Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DELIMITER //
- create procedure ImageTotalInLibrary
- (IN `InClinicId` INT, IN `InIsBilled` CHAR)
- BEGIN
- DECLARE `DefaultInIsBilled` CHAR DEFAULT '';
- if (`InIsBilled` IS NULL) THEN
- SET `DefaultInIsBilled` = '_';
- ELSE
- SET `DefaultInIsBilled` = `InIsBilled`;
- END IF;
- select
- sum(Count) as `PeriodTotal`,
- GROUP_CONCAT(`Id`) as `ImageUploadIds`,
- SUBSTRING_INDEX( GROUP_CONCAT(CAST(`Id` AS CHAR) ORDER BY `CreateDate`), ',', 1 ) AS `BeginningId`,
- SUBSTRING_INDEX( GROUP_CONCAT(CAST(`CreateDate` AS CHAR) ORDER BY `CreateDate`), ',', 1 ) AS `BeginningDate`,
- SUBSTRING_INDEX( GROUP_CONCAT(CAST(`CreateDate` AS CHAR) ORDER BY `CreateDate` DESC), ',', 1 ) AS `EndingDate`,
- DATE_FORMAT(CreateDate, '%Y-%m') as `UploadPeriod`,
- `IsBilled`,
- YEAR(CreateDate) as `Year`,
- MONTH(CreateDate) as `Month`
- from ImageUploads iu
- where ClinicId = `InClinicId`
- and iu.DoctorPracticeId is not NULL
- and iu.`IsBilled` LIKE `DefaultInIsBilled`
- group by `UploadPeriod`, `IsBilled`
- order by `UploadPeriod`, `Year`, `Month` desc ;
- END //
- DELIMITER ;
- DELIMITER //
- create procedure ImageTotalGetInLibrary
- (IN `year` INT, IN `month` INT, IN `InAccountId` INT, IN `InIsBilled` CHAR)
- BEGIN
- DECLARE `DefaultInIsBilled` CHAR DEFAULT '';
- if (`InIsBilled` IS NULL) THEN
- SET `DefaultInIsBilled` = '_';
- ELSE
- SET `DefaultInIsBilled` = `InIsBilled`;
- END IF;
- if (`InAccountId` IS NULL) THEN
- select
- `iu`.`ClinicId`,
- sum(`iu`.`Count`) as `Total`,
- `c`.`AccountId`
- from
- ImageUploads `iu`
- inner join `Clinics` `c`
- on `c`.clinicid = `iu`.ClinicId
- where
- `iu`.`IsBilled` LIKE `DefaultInIsBilled`
- and iu.DoctorPracticeId is not NULL
- and YEAR(`iu`.`CreateDate`) = `year`
- and MONTH(`iu`.`CreateDate`) = `month`
- GROUP BY `iu`.`ClinicId`;
- ELSE
- select
- `iu`.`ClinicId`,
- sum(`iu`.`Count`) as `Total`,
- `c`.`AccountId`
- from
- ImageUploads `iu`
- inner join `Clinics` `c`
- on `c`.clinicid = `iu`.ClinicId
- where
- `iu`.`IsBilled` LIKE `DefaultInIsBilled`
- and iu.DoctorPracticeId is not NULL
- and YEAR(`iu`.`CreateDate`) = `year`
- and MONTH(`iu`.`CreateDate`) = `month`
- and `c`.`AccountId` = `InAccountId`
- GROUP BY `iu`.`ClinicId`;
- END IF;
- END //
- DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement