drop procedure if exists `ImageTotalInLibraryWithDates`;

DELIMITER ;;
CREATE PROCEDURE `ImageTotalInLibraryWithDates`(IN `year_start` INT, IN `month_start` INT, IN `day_start` INT, IN `year_end` INT, IN `month_end` INT, IN `day_end` INT, IN `InAccountId` INT, IN `InClinicId` INT, IN `InIsBilled` CHAR)
BEGIN
    DECLARE `DefaultInIsBilled` CHAR DEFAULT '';

    DECLARE `PastDate` DATETIME;
    DECLARE `CurrentDate` DATETIME;
    DECLARE `TempDate` VARCHAR(20);


    SET `TempDate` = (SELECT CONCAT(`year_start`,'-',`month_start`,'-',`day_start`, ' ', ' 00:00:00'));
    SET `PastDate` = (SELECT CAST(`TempDate` AS DATETIME));

    SET `TempDate` = (SELECT CONCAT(`year_end`,'-',`month_end`,'-',`day_end`, ' ', ' 23:59:59'));
    SET `CurrentDate` = (SELECT CAST(`TempDate` AS DATETIME));

    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
        inner join `Clinics` `c`
        on `c`.`clinicid` = `iu`.`ClinicId`
    where `iu`.`ClinicId` = `InClinicId`
        and `iu`.`IsBilled` LIKE `DefaultInIsBilled`
        and iu.DoctorPracticeId is not NULL
        and (`iu`.`CreateDate` between `PastDate` and  `CurrentDate`)
        and `c`.`AccountId` = `InAccountId`
    group by `UploadPeriod`,`CreateDate`, `IsBilled`
    order by `UploadPeriod`, `Year`, `Month` desc ;
  END;;
DELIMITER ;

drop procedure if exists `ImageTotalGetInLibraryWithDates`;

DELIMITER ;;
CREATE PROCEDURE `ImageTotalGetInLibraryWithDates`(IN `year_start` INT, IN `month_start` INT, IN `day_start` INT, IN `year_end` INT, IN `month_end` INT, IN `day_end` INT, IN `InAccountId` INT, IN `InIsBilled` CHAR)
BEGIN
    DECLARE `DefaultInIsBilled` CHAR DEFAULT '';

    DECLARE `PastDate` DATETIME;
    DECLARE `CurrentDate` DATETIME;
    DECLARE `TempDate` VARCHAR(20);


    SET `TempDate` = (SELECT CONCAT(`year_start`,'-',`month_start`,'-',`day_start`, ' ', ' 00:00:00'));
    SET `PastDate` = (SELECT CAST(`TempDate` AS DATETIME));

    SET `TempDate` = (SELECT CONCAT(`year_end`,'-',`month_end`,'-',`day_end`, ' ', ' 23:59:59'));
    SET `CurrentDate` = (SELECT CAST(`TempDate` AS DATETIME));


    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 (`iu`.`CreateDate` between `PastDate` and  `CurrentDate`)
      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 (`iu`.`CreateDate` between `PastDate` and  `CurrentDate`)
        and `c`.`AccountId` = `InAccountId`
      GROUP BY `iu`.`ClinicId`;
    END IF;
  END;;
DELIMITER ;

drop procedure if exists `BillingHistory`;

DELIMITER ;;
CREATE PROCEDURE `BillingHistory`(IN `InAccountId` INT, IN `InYear` INT, IN `InMonth` INT, IN `InIsBilled` CHAR)
BEGIN

 DECLARE `DefaultInIsBilled` CHAR DEFAULT '';
  if (`InIsBilled` IS NULL) THEN
    SET `DefaultInIsBilled` = '_';
  ELSE
    SET `DefaultInIsBilled` = `InIsBilled`;
  END IF;


if (`InYear` IS NULL AND `InMonth` IS NULL) THEN
  select
    se.*
    ,SUM(iu.Count) as PeriodTotal
    ,DATE_FORMAT(iu.CreateDate,'%Y-%m') as UploadPeriod
    ,YEAR(iu.CreateDate) as `Year`, MONTH(iu.CreateDate) as `Month`
    ,a.AccountId
   from ImageUploads iu
   inner join Clinics c
    on c.ClinicId = iu.ClinicId
   inner join Accounts a
      on c.AccountId = a.AccountId
   inner join StripeCharges sc
    on (
    sc.AccountId = a.AccountId
    -- to be removed and DATE_FORMAT(DATE_SUB(sc.CreateDate, INTERVAL 1 MONTH),'%Y-%m') = DATE_FORMAT(iu.CreateDate,'%Y-%m')
    -- Stripe Charge (less on month, and only use the year and month) compared to image upload period
  )
  inner join StripeEvents se
    on (
    se.Id = sc.StripeEventId
  )
  where a.AccountId = `InAccountId`
  and `iu`.IsBilled LIKE `DefaultInIsBilled`
   group by se.Id
   order by se.`CreateDate`, `Year`, `Month` desc
  ;
ELSE
    select
    se.*
    ,SUM(`iu`.`Count`) as `PeriodTotal`
    ,DATE_FORMAT(`iu`.`CreateDate`,'%Y-%m') as `UploadPeriod`
    ,YEAR(`iu`.`CreateDate`) as `Year`,
    MONTH(`iu`.`CreateDate`) as `Month`
    , a.`AccountId`
   from `ImageUploads` `iu`
   inner join `Clinics` c
    on c.`ClinicId` = iu.`ClinicId`
   inner join Accounts a
      on c.`AccountId` = a.`AccountId`
   inner join `StripeCharges` sc
    on (
    sc.`AccountId` = a.`AccountId`
    -- to be removed    and DATE_FORMAT(DATE_SUB(sc.`CreateDate`, INTERVAL 1 MONTH),'%Y-%m') = DATE_FORMAT(iu.`CreateDate`,'%Y-%m')
    -- Stripe Charge (less on month, and only use the year and month) compared to image upload period
  )
  inner join StripeEvents se
    on (
    se.`Id` = sc.`StripeEventId`
  )
  where a.`AccountId` = `InAccountId`
  and `iu`.`IsBilled` LIKE `DefaultInIsBilled`
   group by se.`Id`
    having `Year`= `InYear` and `Month` = `InMonth`
   order by se.`CreateDate`, `Year`, `Month` desc
;

END IF;
END;;
DELIMITER ;

drop procedure if exists `ImageTotalGet`;

DELIMITER ;;
CREATE PROCEDURE `ImageTotalGet`(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 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 YEAR(`iu`.`CreateDate`) = `year`
      and MONTH(`iu`.`CreateDate`) = `month`
      and `c`.`AccountId` = `InAccountId`
    GROUP BY `iu`.`ClinicId`;
  END IF;
END;;
DELIMITER ;

drop procedure if exists `ImageTotalGetInLibrary`;

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 ;

drop procedure if exists `ImageTotalInLibrary`;

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`,`CreateDate`, `IsBilled`
    order by `UploadPeriod`, `Year`, `Month` desc ;
  END;;
DELIMITER ;

drop procedure if exists `ImageTotals`;

DELIMITER ;;
CREATE PROCEDURE `ImageTotals`(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.`IsBilled` LIKE `DefaultInIsBilled`
    group by `UploadPeriod`, `IsBilled`
    order by `UploadPeriod`, `Year`, `Month` desc ;
END;;
DELIMITER ;