SCRIPT
BEGIN

   DECLARE FullReport BOOLEAN DEFAULT FALSE; --Pass this in
   DECLARE AllRooms BOOLEAN DEFAULT TRUE;  --Pass this in
   DECLARE RoomsFilter VARCHAR(255) DEFAULT '12';  --Pass this in
   DECLARE ReportDate DATE DEFAULT DATE '2015-10-01'; --Pass this in
   DECLARE REPORT_CURSOR CURSOR WITH RETURN FOR REPORT_STMT;
   DECLARE ReportsToRun Varchar; -- Will want to pass this in from Delphi side
   DECLARE StartDate Date;
   DECLARE EndDate Date;
   
   DECLARE InsertString VARCHAR DEFAULT
          'INSERT INTO
            tmpReportResults(
             ReportName, 
             ResNumber, 
             GuestName,
             DateOfFinancial,
             Cancelled,
             Arrival,
             Departure,
             Amount)';


   DECLARE DepositsTransList VARCHAR DEFAULT ' (100,101,102,122,130)';
   DECLARE PaymentsTransList VARCHAR DEFAULT ' (150,155,160,165,180,185,200,210,201,212,230,121,170,175)';
   DECLARE WriteOffsTransList VARCHAR DEFAULT '(254,222,221,220)';
   DECLARE ReturnsTransList VARCHAR DEFAULT '(120, 121, 122, 135, 145, 155, 165, 175, 185, 210, 212, 250)';
   SET StartDate = fnFirstOfTheMonth(ReportDate);
   SET EndDate = fnFirstOfNextMonth(ReportDate);
  
   SET ReportsToRun = 'DepositsMadeInMonthCancelledInMonth,';
   SET ReportsToRun = ReportsToRun + 'DepositsMadeInMonthForDeparturesInMonth,';
   SET ReportsToRun = ReportsToRun + 'DepositsMadeInMonthForFutureAndCancelledInFutureMonths,';
   SET ReportsToRun = ReportsToRun + 'DepositsMadePreviousMonthsForDeparturesThisMonth,';
   SET ReportsToRun = ReportsToRun + 'DepositsMadeInPreviousMonthsThatCancelledThisMonth,';
   SET ReportsToRun = ReportsToRun + 'DepositsReturnedInMonthForDepositsTakenInAnotherMonth,';
   SET ReportsToRun = ReportsToRun + 'PaymentsMadeInMonthForCancellationsThisMonth,';
   SET ReportsToRun = ReportsToRun + 'PaymentsMadeInMonthForDeparturesThisMonth,';
   SET ReportsToRun = ReportsToRun + 'PaymentsMadeInMonthForFutureReservations,';

   --Clean tmpTables
      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE tmpReportResults';
      EXCEPTION
         SET LOG MESSAGE TO ERRORMSG();
      END;
     

   --Create tmpReportResults Temp Table
      BEGIN
         EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE 
                              tmpReportResults(
                                 ReportName varchar(255),
                                 ResNumber Int,
                                 GuestName varchar(255),
                                 DateOfFinancial TIMESTAMP,
                                 Arrival Date,
                                 Cancelled Date,
                                 Departure Date,
                                 Amount Decimal(19,2)
                           )';
      EXCEPTION
         SET LOG MESSAGE TO ERRORMSG();
      END;
   
   IF Occurs('DepositsMadeInMonthCancelledInMonth' IN ReportsToRun)  THEN
   --DepositsMadeInMonthCancelledInMonth
      EXECUTE IMMEDIATE
         InsertString + '         
          CALL spDepositsMadeInMonthCancelledInMonth ? ? ? ?
         ' USING ReportDate, RoomsFilter, AllRooms, FullReport;
   END IF;

  IF Occurs('DepositsMadeInMonthForDeparturesInMonth' IN ReportsToRun)  THEN
--Deposits Made in Month for Departures in Month
      EXECUTE IMMEDIATE
        InsertString + '
           SELECT
             ''*Deposits'' as "Account",
              gd.ResNumber,
              GuestName(n.FirstName,n.LastName, n.Company) as "Name",
              f.DateOfFinancial  as "Paid" ,
              gd.Cancelled,
              gd.Arrival,
              gd.Departure,
              sum( f.Amount) "Amount"
          FROM
             tblguestdates gd
          inner JOIN
             tblnames n on n.ResNumber = gd.ResNumber
          inner JOIN
             tblfinancial_l f on f.ResNumber = gd.ResNumber

          WHERE

              n.NameType=''GST'' AND
              gd.Cancelled IS  NULL AND
            
              --Deposit Trans Types
              f.TransType in (100,101,102,122,130) AND
              --Deposit in month
              EXTRACT(MONTH FROM f.DateOfFinancial)  =   EXTRACT(MONTH FROM ?)  AND
              EXTRACT(YEAR FROM f.DateOfFinancial)  =   EXTRACT(YEAR FROM ?)  AND
            
              --Departure in month
              EXTRACT(MONTH FROM gd.Departure)  =   EXTRACT(MONTH FROM ?)  AND
              EXTRACT(YEAR FROM gd.Departure)  =   EXTRACT(YEAR FROM ?) AND
              CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END

          GROUP BY
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +'  THEN f.DateOfFinancial ELSE current_date() END
          ORDER BY        
             gd.Departure,
             gd.ResNumber,
             f.Amount,
             f.DateOfFinancial' USING ReportDate,ReportDate,ReportDate, ReportDate, RoomsFilter;
   END IF;

  IF Occurs('DepositsMadeInMonthForFutureAndCancelledInFutureMonths' IN ReportsToRun)  THEN
--Deposits Made in Month for Future and Cancelled in Future Months
      EXECUTE IMMEDIATE
        InsertString + '
        SELECT
           ''*Advanced Deposits taken in month but cancelled in future months'' as "Account",
           gd.ResNumber,
           GuestName(n.FirstName,n.LastName, n.Company) as "Name",
           f.DateOfFinancial  as "Paid" ,
           gd.Cancelled,
           gd.Arrival,
           gd.Departure,
           sum( f.Amount) "Amount"
       FROM
          tblguestdates gd
       INNER JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       INNER JOIN
          tblfinancial_l f on f.ResNumber = gd.ResNumber

       WHERE
         n.NameType=''GST'' AND
         --Deposit Trans Types
         f.TransType in (100,101,102,122,130) AND
         gd.Arrival > ? AND

         EXTRACT(MONTH FROM f.DateOfFinancial)  =   EXTRACT(MONTH FROM ?)  AND
         EXTRACT(YEAR FROM f.DateOfFinancial)  =   EXTRACT(YEAR FROM ?)  AND
         (EXTRACT(MONTH FROM gd.Cancelled)  > EXTRACT(MONTH FROM ?) AND
         EXTRACT(YEAR FROM  gd.Cancelled)  =   EXTRACT(YEAR FROM ?)) OR
         (EXTRACT(YEAR FROM  gd.Cancelled)  >   EXTRACT(YEAR FROM ?)) AND
          CASE WHEN  '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END

          GROUP BY
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +' THEN f.DateOfFinancial ELSE current_date() END' 
            USING ReportDate,ReportDate,ReportDate, ReportDate, ReportDate, ReportDate, RoomsFilter;
   END IF;


 


  IF Occurs('DepositsMadePreviousMonthsForDeparturesThisMonth' IN ReportsToRun)  THEN
--Deposits Made Previous Months For Departures This Month
      EXECUTE IMMEDIATE
        InsertString + '
       SELECT
        ''Deposits from previous months'' as "Account",
         gd.ResNumber,
         GuestName(n.FirstName,n.LastName, n.Company) as "Name",
         gd.Deposit as "Date Paid",
         gd.Cancelled,
         gd.Arrival,
         gd.Departure,
         sum(f.Amount) as "Amount"
      FROM
         tblguestdates gd
      LEFT JOIN
         tblnames n on n.ResNumber = gd.ResNumber
      LEFT JOIN
         tblfinancial_l f on f.ResNumber = gd.ResNumber
      WHERE
        
         n.NameType=''GST'' AND
         EXTRACT(MONTH FROM gd.Departure)  = EXTRACT(MONTH FROM ?)  AND
         EXTRACT(YEAR FROM gd.Departure) =  EXTRACT(YEAR FROM ?) AND
         (EXTRACT(MONTH FROM f.DateOfFinancial)  <   EXTRACT(MONTH FROM ?)  OR
         (EXTRACT(YEAR FROM f.DateOfFinancial)  <   EXTRACT(YEAR FROM ?) ) )

         AND
         f.DateOfFinancial < ? AND
         gd.Cancelled IS NULL AND
         f.TransType IN (100, 120, 101, 121, 102, 122, 130, 135) AND
         CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
        GROUP BY 
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +' THEN f.DateOfFinancial ELSE current_date() END
      ORDER BY
         gd.Departure,
         gd.ResNumber,
         f.Amount,
         f.DateOfFinancial'
            USING ReportDate,ReportDate,ReportDate, ReportDate, ReportDate,  RoomsFilter;
   END IF;


  IF Occurs('DepositsMadeInPreviousMonthsThatCancelledThisMonth' IN ReportsToRun)  THEN
--Deposits Made In Previous Months That Cancelled This Month
      EXECUTE IMMEDIATE
        InsertString + '

        SELECT
           ''Deposits made previous Months'' as "Account",
           gd.ResNumber,
           GuestName(n.FirstName,n.LastName, n.Company) as "Name",
           f.DateOfFinancial as "Date Paid",
           gd.Cancelled,
           gd.Arrival,           
           gd.Departure,
           sum(f.Amount) as "Amount"

        FROM
           tblguestdates gd
        left JOIN
           tblnames n on n.ResNumber = gd.ResNumber
        left JOIN
           tblfinancial_l f on f.ResNumber = gd.ResNumber
        WHERE
           
           n.NameType=''GST'' AND
          
            ((EXTRACT(MONTH FROM f.DateOfFinancial) < EXTRACT(MONTH FROM ?) AND
              EXTRACT(YEAR FROM f.DateOfFinancial) = EXTRACT(YEAR FROM ?)) OR
              EXTRACT(YEAR FROM f.DateOfFinancial) < EXTRACT(YEAR FROM?)) AND

        
           EXTRACT(MONTH FROM gd.Cancelled) = EXTRACT(MONTH FROM ?) AND
           EXTRACT(YEAR FROM gd.Cancelled) = EXTRACT(YEAR FROM ?) AND
           f.TransType IN (100, 120, 101, 121, 102, 122, 130, 135) AND

        CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
        GROUP BY 
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +' THEN f.DateOfFinancial ELSE current_date() END
        ORDER BY        
         
           gd.Departure,
           gd.ResNumber,
           f.Amount,
           f.DateOfFinancial'
            USING ReportDate,ReportDate,ReportDate, ReportDate, ReportDate,  RoomsFilter;
   END IF;


  IF Occurs('DepositsReturnedInMonthForDepositsTakenInAnotherMonth' IN ReportsToRun)  THEN
--Deposits Returned in Month for Deposits Taken in Another Month
      EXECUTE IMMEDIATE
        InsertString + '
         SELECT
       ''*Deposits returned on previous Months'' as "Account",
         gd.ResNumber,
         GuestName(n.FirstName,n.LastName, n.Company) as "Name",
         gd.Deposit as "Date Paid",
         gd.Cancelled,
         gd.Arrival,
         gd.Departure,
         sum(f.Amount) as "Amount"

        FROM
           tblguestdates gd
        inner JOIN
           tblnames n on n.ResNumber = gd.ResNumber
        inner JOIN
           tblfinancial_l f on f.ResNumber = gd.ResNumber
        inner JOIN
            tblfinancial_l pd on pd.ResNumber = f.ResNumber
         
        WHERE
            pd.Amount >0 and 
            (EXTRACT(MONTH FROM pd.DateOfFinancial)  <   EXTRACT(MONTH FROM f.DateOfFinancial) OR
            EXTRACT(YEAR FROM pd.DateOfFinancial)  <   EXTRACT(YEAR FROM f.DateOfFinancial)) AND
            n.NameType=''GST'' AND
            EXTRACT(MONTH FROM f.DateOfFinancial)  =   EXTRACT(MONTH FROM ?)  AND
            EXTRACT(YEAR FROM f.DateOfFinancial)  =   EXTRACT(YEAR FROM ?)  AND
            gd.Cancelled IS NOT NULL AND
              f.Amount<0 AND
           f.TransType IN (100, 120, 101, 121, 102, 122, 130, 135) AND

        CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
        GROUP BY 
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +' THEN f.DateOfFinancial ELSE current_date() END
        ORDER BY        
         
           gd.Departure,
           gd.ResNumber,
           f.Amount,
           f.DateOfFinancial'
            USING  ReportDate, ReportDate,  RoomsFilter;
   END IF;

 IF Occurs('PaymentsMadeInMonthForCancellationsThisMonth' IN ReportsToRun)  THEN
 --Payments Made In Month for Cancellations This Month
      EXECUTE IMMEDIATE
        InsertString + '
        SELECT
           ''*Payments Made this Month'' as "Account",
           gd.ResNumber,
           GuestName(n.FirstName,n.LastName, n.Company) as "Name",
           CAST(f.DateOfFinancial AS DATE) as "Date Paid" ,
           gd.Arrival,
           gd.Departure,
           gd.Cancelled,
           coalesce(sum(f.Amount), 0.0) as "Amount"
       FROM
          tblguestdates gd
       LEFT JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       LEFT JOIN
          tblfinancial_l f on f.ResNumber = gd.ResNumber
       WHERE
         n.NameType=''GST'' AND
         
         --Payment this month
         EXTRACT(MONTH FROM f.DateOfFinancial) =   EXTRACT(MONTH FROM ?)  AND
         EXTRACT(YEAR FROM f.DateOfFinancial)  =   EXTRACT(YEAR FROM ?)  AND

         --Cancelled this Month
         EXTRACT(MONTH FROM gd.Cancelled) =   EXTRACT(MONTH FROM ?)  AND
         EXTRACT(YEAR FROM gd.Cancelled)  =   EXTRACT(YEAR FROM ?)  AND

         f.TransType IN (200,210) AND
    
        CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
        GROUP BY 
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +' THEN f.DateOfFinancial ELSE current_date() END
        '  USING  ReportDate, ReportDate, ReportDate, ReportDate,  RoomsFilter;
   END IF;



 IF Occurs('PaymentsMadeInMonthForDeparturesThisMonth' IN ReportsToRun)  THEN
 --Payments Made In Month for Departures This Month
      EXECUTE IMMEDIATE
        InsertString + '

        SELECT
          ''Payments Made In Month for Departures This Month'' as "Account",
         gd.ResNumber,
         GuestName(n.FirstName,n.LastName, n.Company) as "Name",
         CAST(f.DateOfFinancial AS DATE) as "Date Paid" ,
         gd.Cancelled,
         gd.Arrival,
         gd.Departure,
         sum(f.Amount) as "Amount"
       FROM
          tblguestdates gd
       LEFT JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       LEFT JOIN
          tblfinancial_l f on f.ResNumber = gd.ResNumber
       WHERE
         n.NameType=''GST'' AND
         
         f.DateOfFinancial >= ?  AND
         f.DateOfFinancial < ?  AND

         gd.Departure >= ?  AND
         gd.Departure <  ?  AND
         gd.Cancelled is Null and
         f.TransType IN(150,155,160,165,180,185,200,210,201,212,230,121,170,175) AND
         CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
         GROUP BY
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +'  THEN f.DateOfFinancial ELSE current_date() END
        '  USING  StartDate, EndDate, StartDate, EndDate,  RoomsFilter;
   END IF;




 IF Occurs('PaymentsMadeInMonthForFutureReservations' IN ReportsToRun)  THEN
 --Payments Made In Month for Future Reservations
      EXECUTE IMMEDIATE
        InsertString + '

        SELECT
          ''Payments Made in previous month'' as "Account",
         gd.ResNumber,
         GuestName(n.FirstName,n.LastName, n.Company) as "Name",
         CAST(f.DateOfFinancial AS DATE) as "Date Paid" ,
         gd.Cancelled,
         gd.Arrival,
         gd.Departure,
         sum(f.Amount) as "Amount"
       FROM
          tblguestdates gd
       LEFT JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       LEFT JOIN
          tblfinancial_l f on f.ResNumber = gd.ResNumber
       WHERE
         n.NameType=''GST'' AND

         f.DateOfFinancial >= ?  AND
         f.DateOfFinancial <  ?  AND

         gd.Departure >= ?  AND

         f.TransType IN (200) AND
         CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
         GROUP BY
           CASE WHEN '+ Cast(FullReport as Varchar(9)) +'  THEN f.DateOfFinancial ELSE current_date() END
        '  USING  StartDate, EndDate, ReportDate,  RoomsFilter;
   END IF;






      PREPARE REPORT_STMT FROM 'Select * From tmpReportResults';
      OPEN REPORT_CURSOR;

   
   
END