Advertisement
Guest User

Untitled

a guest
Apr 27th, 2017
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.38 KB | None | 0 0
  1. DELIMITER //
  2. create procedure ImageTotalInLibrary
  3. (IN `InClinicId` INT, IN `InIsBilled` CHAR)
  4. BEGIN
  5. DECLARE `DefaultInIsBilled` CHAR DEFAULT '';
  6. if (`InIsBilled` IS NULL) THEN
  7. SET `DefaultInIsBilled` = '_';
  8. ELSE
  9. SET `DefaultInIsBilled` = `InIsBilled`;
  10. END IF;
  11.  
  12. select
  13. sum(Count) as `PeriodTotal`,
  14. GROUP_CONCAT(`Id`) as `ImageUploadIds`,
  15. SUBSTRING_INDEX( GROUP_CONCAT(CAST(`Id` AS CHAR) ORDER BY `CreateDate`), ',', 1 ) AS `BeginningId`,
  16. SUBSTRING_INDEX( GROUP_CONCAT(CAST(`CreateDate` AS CHAR) ORDER BY `CreateDate`), ',', 1 ) AS `BeginningDate`,
  17. SUBSTRING_INDEX( GROUP_CONCAT(CAST(`CreateDate` AS CHAR) ORDER BY `CreateDate` DESC), ',', 1 ) AS `EndingDate`,
  18. DATE_FORMAT(CreateDate, '%Y-%m') as `UploadPeriod`,
  19. `IsBilled`,
  20. YEAR(CreateDate) as `Year`,
  21. MONTH(CreateDate) as `Month`
  22. from ImageUploads iu
  23. where ClinicId = `InClinicId`
  24. and iu.DoctorPracticeId is not NULL
  25. and iu.`IsBilled` LIKE `DefaultInIsBilled`
  26. group by `UploadPeriod`, `IsBilled`
  27. order by `UploadPeriod`, `Year`, `Month` desc ;
  28. END //
  29. DELIMITER ;
  30.  
  31. DELIMITER //
  32. create procedure ImageTotalGetInLibrary
  33. (IN `year` INT, IN `month` INT, IN `InAccountId` INT, IN `InIsBilled` CHAR)
  34. BEGIN
  35. DECLARE `DefaultInIsBilled` CHAR DEFAULT '';
  36. if (`InIsBilled` IS NULL) THEN
  37. SET `DefaultInIsBilled` = '_';
  38. ELSE
  39. SET `DefaultInIsBilled` = `InIsBilled`;
  40. END IF;
  41.  
  42. if (`InAccountId` IS NULL) THEN
  43. select
  44. `iu`.`ClinicId`,
  45. sum(`iu`.`Count`) as `Total`,
  46. `c`.`AccountId`
  47. from
  48. ImageUploads `iu`
  49. inner join `Clinics` `c`
  50. on `c`.clinicid = `iu`.ClinicId
  51. where
  52. `iu`.`IsBilled` LIKE `DefaultInIsBilled`
  53. and iu.DoctorPracticeId is not NULL
  54. and YEAR(`iu`.`CreateDate`) = `year`
  55. and MONTH(`iu`.`CreateDate`) = `month`
  56. GROUP BY `iu`.`ClinicId`;
  57. ELSE
  58. select
  59. `iu`.`ClinicId`,
  60. sum(`iu`.`Count`) as `Total`,
  61. `c`.`AccountId`
  62. from
  63. ImageUploads `iu`
  64. inner join `Clinics` `c`
  65. on `c`.clinicid = `iu`.ClinicId
  66. where
  67. `iu`.`IsBilled` LIKE `DefaultInIsBilled`
  68. and iu.DoctorPracticeId is not NULL
  69. and YEAR(`iu`.`CreateDate`) = `year`
  70. and MONTH(`iu`.`CreateDate`) = `month`
  71. and `c`.`AccountId` = `InAccountId`
  72. GROUP BY `iu`.`ClinicId`;
  73. END IF;
  74. END //
  75. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement