CREATE PROCEDURE AbsenteeReport(
    @customerparam INT,
    @groupparam INT,
    @personparam INT,
    @taskparam INT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @offsetparam INT,
    @countparam INT,
    @daystartparam INT)
AS
BEGIN
    DECLARE @startparam DATETIME, @endparam DATETIME;

    DECLARE @grpqry NVARCHAR(MAX),@psnqry NVARCHAR(MAX), @QRY1 NVARCHAR(MAX), @QRY2 NVARCHAR(MAX), @taskfilter NVARCHAR(MAX), @taskfilter1 NVARCHAR(MAX), @QRY3 NVARCHAR(MAX),@qry NVARCHAR(MAX);

    SET @toparam = DATEADD(SECOND,86399,@toparam);
    SET @startparam = DATEADD(SECOND,@daystartparam,@fromparam);
    SET @endparam = DATEADD(SECOND,(86399 + @daystartparam),@toparam);

IF @taskparam < 0
	SET @taskfilter = ' ';
ELSE IF @taskparam = 0
	SET @taskfilter = ' AND task_id IS NULL ';
ELSE
	SET @taskfilter = ' AND task_id = @taskParam ';
;

IF @taskparam < 0
	SET @taskfilter1 = ' ';
ELSE
	SET @taskfilter1 = ' AND task_id = @taskParam ';
;

SET @grpqry = CASE WHEN @groupparam < 0 THEN
	            ''
                ELSE
	            ' INNER JOIN user_group_membership UGM ON PA.person_id = UGM.member AND UGM.end_date IS NULL AND UGM.assigned_group_id = @groupparam '
                END;

SET @psnqry = CASE WHEN @personparam < 0 THEN
	' '
ELSE
	' AND PA.person_id = @personparam '
END;

    SET @QRY1  = ' SELECT
    PER.person_id,
    PER.employee_code,
    PER.first_name,
    PER.last_name,
    PER.email,
    PER.primary_telephone,
    PER.address1,
    PER.address2,
    PER.city,
    PER.country,
    PER.state_province_region,
    PER.zip_or_postal_code,
    PER.is_blocked,
    CD.data_json AS customdata,
    CONVERT(DATE,P.cloud_date) AS period,
    T.task_id,
    T.name AS task_name,
    T.description AS task_description,
    P.event_logs AS eventlogs,
    IIF(R.replacement_id IS NOT NULL,1,0) AS replacement,
    IIF(A.additional_employee_id IS NOT NULL,1,0) AS additional,
    IIF(LAM.leave_id IS NOT NULL,1,0) AS leave_and_mission,
    LAM.leave_policy AS leave_policy,
    IIF(H.holiday_id IS NOT NULL,1,0) AS holiday,
    P.roster_item,
    ARRIVAL_DEPATURE.shift_start,
    DATEADD(SECOND , ARRIVAL_DEPATURE.first_checkin_timezone, ARRIVAL_DEPATURE.first_checkin) AS first_checkin,
    ARRIVAL_DEPATURE.first_checkin_timezone,
    ARRIVAL_DEPATURE.early_arrival,
    ARRIVAL_DEPATURE.late_arrival,
    ARRIVAL_DEPATURE.shift_end,
    DATEADD(SECOND , ARRIVAL_DEPATURE.last_checkout_timezone, ARRIVAL_DEPATURE.last_checkout) AS last_checkout,
    ARRIVAL_DEPATURE.last_checkout_timezone,
    ARRIVAL_DEPATURE.early_depature,
    ARRIVAL_DEPATURE.late_depature
FROM
    (SELECT
        R.cloud_date,
            R.person_id,
            R.task_id,
            CAST(AVG(R.roster_item) AS INT) AS roster_item,
            SUM(IIF(R.task_id != E.task_id, 0, E.event_logs)) AS event_logs
    FROM
        (SELECT
        P.*, (IIF(P_RI.task_id IS NOT NULL,1,0) + IIF(G_RI.task_id IS NOT NULL,1,0) + IIF(AG_RI.task_id IS NOT NULL,1,0)) AS roster_item
    FROM
        (SELECT
    *
    FROM
    (SELECT
        PC.cloud_date, PA.person_id
    FROM
        person PA ';

    SET @QRY2 = CONCAT('
    CROSS JOIN cloud_date_table PC WHERE PA.is_deleted = 0
        AND PA.customer_id = @customerparam AND (PA.person_type IS NULL OR PA.person_type = ''EMPLOYEE'')',
        @psnqry,
        ' AND PC.cloud_date BETWEEN @fromparam AND @toparam  ORDER BY PC.cloud_date, PA.person_id OFFSET @offsetparam ROWS
        FETCH NEXT @countparam ROWS ONLY ) PD
        CROSS JOIN
(SELECT
    task_id
FROM
    (SELECT IIF(value = 0, 0, task_id) AS task_id
    FROM
        boolean_table B
        LEFT JOIN task T ON T.customer_id = @customerparam
    GROUP BY value , task_id) T
WHERE
        task_id IS NOT NULL ',
        @taskfilter1,
        'GROUP BY task_id)
T) AS P
    LEFT JOIN
    (SELECT
        RI.cloud_date, RI.task_id, RI.ri_person
    FROM
        (SELECT
        CD_RI.cloud_date,
            CD_RI.assigned_user AS ri_person,
            CD_RI.task_id,
            IIF(RO.roster_id IS NOT NULL,1,0) AS available
    FROM
        (SELECT
        *
    FROM
        cloud_date_table CD
    CROSS JOIN (SELECT
        RI.*, RIPG.assigned_user_id as assigned_user, RIPG.assigned_group_id as assigned_group
    FROM
        roster_item RI LEFT JOIN rosteritem_person_group RIPG on RI.roster_item_id=RIPG.roster_item_id
    WHERE
        RI.customer_id = @customerparam) AS RI
    WHERE
        CD.cloud_date BETWEEN @fromparam AND @toparam
            AND RI.assigned_user IS NOT NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATEADD(SECOND,86399,CD.cloud_date)) ) AS CD_RI
    LEFT JOIN roster RO ON CD_RI.roster_id = RO.roster_id
        AND RO.start_date <= CD_RI.cloud_date
        AND (RO.end_date IS NULL
        OR RO.end_date >= CD_RI.cloud_date)
        AND CD_RI.day_number = DATEDIFF(DAY,RO.start_date,CD_RI.cloud_date) % 7 * RO.repetition) AS RI
    WHERE
        RI.available > 0) AS P_RI ON P.cloud_date = P_RI.cloud_date
        AND P.person_id = P_RI.ri_person
        AND P.task_id = P_RI.task_id

        LEFT JOIN
    (SELECT
        RI.cloud_date, RI.task_id, UGM.member AS ri_group_person
    FROM
        (SELECT
        CD_RI.cloud_date,
            CD_RI.assigned_group AS ri_group,
            CD_RI.task_id,
            IIF(RO.roster_id IS NOT NULL,1,0) AS available
    FROM
        (SELECT
        *
    FROM
        cloud_date_table CD
    CROSS JOIN (SELECT
        RI.*, RIPG.assigned_user_id as assigned_user, RIPG.assigned_group_id as assigned_group
    FROM
        roster_item RI LEFT JOIN rosteritem_person_group RIPG on RI.roster_item_id=RIPG.roster_item_id
    WHERE
        RI.customer_id = @customerparam) AS RI
    WHERE
        CD.cloud_date BETWEEN @fromparam AND @toparam
            AND RI.assigned_group IS NOT NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATEADD(SECOND,86399,CD.cloud_date)) ) AS CD_RI
    LEFT JOIN roster RO ON CD_RI.roster_id = RO.roster_id
        AND RO.start_date <= CD_RI.cloud_date
        AND (RO.end_date IS NULL
        OR RO.end_date >= CD_RI.cloud_date)
        AND CD_RI.day_number = DATEDIFF(DAY,CD_RI.cloud_date, RO.start_date) % 7 * RO.repetition) AS RI
    INNER JOIN user_group_membership UGM ON ri_group = UGM.assigned_group_id
        AND UGM.start_date <= RI.cloud_date
        AND (UGM.end_date > RI.cloud_date
        OR UGM.end_date IS NULL)
        AND RI.available > 0) AS G_RI ON P.cloud_date = G_RI.cloud_date
        AND P.person_id = G_RI.ri_group_person
        AND P.task_id = G_RI.task_id

        LEFT JOIN
    (SELECT
        RI.cloud_date, RI.task_id, P.person_id AS ri_alluser_person
    FROM
        (SELECT
        CD_RI.cloud_date,
            CD_RI.assigned_group AS ri_group,
            CD_RI.task_id,
            IIF(RO.roster_id IS NOT NULL,1,0) AS available
    FROM
        (SELECT
        *
    FROM
        cloud_date_table CD
    CROSS JOIN (SELECT
        RI.*, RIPG.assigned_user_id as assigned_user, RIPG.assigned_group_id as assigned_group
    FROM
        roster_item RI LEFT JOIN rosteritem_person_group RIPG on RI.roster_item_id=RIPG.roster_item_id
    WHERE
        RI.customer_id = @customerparam) AS RI
    WHERE
        CD.cloud_date BETWEEN @fromparam AND @toparam
            AND RI.assigned_group IS NULL
            AND RI.assigned_user IS NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATEADD(SECOND,86399,CD.cloud_date)) ) AS CD_RI
    LEFT JOIN roster RO ON CD_RI.roster_id = RO.roster_id
        AND RO.start_date <= CD_RI.cloud_date
        AND (RO.end_date IS NULL
        OR RO.end_date >= CD_RI.cloud_date)
        AND CD_RI.day_number = DATEDIFF(DAY, RO.start_date,CD_RI.cloud_date) % 7 * RO.repetition) AS RI
    CROSS JOIN person P
    WHERE
        P.customer_id = @customerparam
            AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'') AND RI.available > 0) AS AG_RI ON P.cloud_date = AG_RI.cloud_date
        AND P.person_id = AG_RI.ri_alluser_person
        AND P.task_id = AG_RI.task_id) R
    CROSS JOIN
(SELECT
    P.*,
    IIF(E.event_logs IS NULL, 0, E.event_logs) AS event_logs,
    IIF(E.task_id IS NULL, 0, E.task_id) AS task_id
FROM
    (SELECT
        PC.cloud_date, PA.person_id
    FROM
        person PA '
    );

    SET @QRY3 = CONCAT('CROSS JOIN
    cloud_date_table PC WHERE PA.is_deleted = 0
        AND PA.customer_id = @customerparam AND (PA.person_type IS NULL OR PA.person_type = ''EMPLOYEE'')
        AND PC.cloud_date BETWEEN @fromparam AND @toparam  ORDER BY PC.cloud_date, PA.person_id OFFSET @offsetparam ROWS
FETCH NEXT @countparam ROWS ONLY) AS P
    LEFT JOIN
(SELECT
    COUNT(E.event_id) AS event_logs,
    P.person_id,
    E.task_id,
    CONVERT(DATE,CASE WHEN CONVERT(TIME,E.shift_start) >= CONVERT(TIME, DATEADD(SECOND,@daystartparam,0)) THEN E.shift_start ELSE DATEADD(DAY,-1,E.shift_start) END) AS event_date
FROM
    eventlog E
    INNER JOIN person P ON E.person_id = P.person_id
        AND P.customer_id = @customerparam
        AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
        AND E.is_deleted = 0
        AND E.shift_start BETWEEN @fromparam AND @toparam ',
        @taskfilter,
'
GROUP BY E.shift_start , P.person_id , E.task_id)
AS E ON P.person_id = E.person_id
        AND P.cloud_date = E.event_date) E WHERE R.cloud_date = E.cloud_date
        AND R.person_id = E.person_id
    GROUP BY R.cloud_date , R.person_id , R.task_id) AS P ',

' LEFT JOIN (
SELECT PD.*,
       T.*,
       CHECKIN.shift_start AS shift_start,
       CHECKOUT.shift_end AS shift_end,
       CHECKIN.first_checkin,
       CHECKIN.event_time_zone AS first_checkin_timezone,
       dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, DATEADD(SECOND, CHECKIN.event_time_zone, CHECKIN.first_checkin), CHECKIN.shift_start), 0) AS early_arrival,
       dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, CHECKIN.shift_start, DATEADD(SECOND, CHECKIN.event_time_zone, CHECKIN.first_checkin)), 0) AS late_arrival,
       CHECKOUT.last_checkout,
       CHECKOUT.event_time_zone AS last_checkout_timezone,
       dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, DATEADD(SECOND, CHECKOUT.event_time_zone, CHECKOUT.last_checkout), CHECKOUT.shift_end), 0) AS early_depature,
       dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, CHECKOUT.shift_end, DATEADD(SECOND, CHECKOUT.event_time_zone, CHECKOUT.last_checkout)), 0) AS late_depature
FROM
  (SELECT PC.cloud_date,
          PA.person_id
   FROM person PA
   CROSS JOIN cloud_date_table PC WHERE PA.is_deleted = 0
   AND PA.customer_id = @customerparam
   AND (PA.person_type IS NULL OR PA.person_type = ''EMPLOYEE'')
   AND PC.cloud_date BETWEEN @fromparam AND @toparam
   ORDER BY PC.cloud_date,
            PA.person_id
   OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY) PD
CROSS JOIN (
SELECT task_id
FROM
  (SELECT (CASE
               WHEN value = 0 THEN 0
               ELSE task_id
           END) AS task_id
   FROM boolean_table B
   LEFT JOIN task T ON T.customer_id = @customerparam
   GROUP BY value,
            task_id) T
WHERE task_id IS NOT NULL ',
@taskfilter1,
'
GROUP BY task_id) T
LEFT JOIN
  (SELECT E.person_id,
          (CASE
               WHEN E.task_id IS NULL THEN 0
               ELSE E.task_id
           END) AS task_id,
          E.shift_start,
          DATEADD(SECOND, E.shift_span_seconds, E.shift_start) AS shift_end,
          CONVERT(DATE,E.shift_start) AS event_date,
          CIN.first_checkin AS first_checkin,
          E.event_time_zone
   FROM eventlog E
   INNER JOIN person P ON E.person_id = P.person_id
   LEFT JOIN
     (SELECT E.person_id,
             E.task_id,
             MIN(E.event_time) first_checkin
      FROM eventlog E
      INNER JOIN person P ON E.person_id = P.person_id
      WHERE P.customer_id = @customerparam
        AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
        AND E.shift_start BETWEEN @fromparam AND @toparam
        AND E.event_type = 0
        AND E.is_deleted = 0
      GROUP BY E.person_id,
               E.task_id,
               E.shift_start) CIN ON E.person_id = CIN.person_id
   AND ((E.task_id IS NULL AND CIN.task_id IS NULL)
        OR E.task_id = CIN.task_id)
   AND E.event_time = CIN.first_checkin
   WHERE P.customer_id = @customerparam
     AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
     AND E.shift_start BETWEEN @fromparam AND @toparam
     AND E.is_deleted = 0
     AND CIN.first_checkin IS NOT NULL) AS CHECKIN ON CHECKIN.person_id = PD.person_id
AND CHECKIN.task_id = T.task_id
 AND CHECKIN.event_date = PD.cloud_date
 LEFT JOIN
  (SELECT E.person_id,
          (CASE
               WHEN E.task_id IS NULL THEN 0
               ELSE E.task_id
           END) AS task_id,
          E.shift_start,
          DATEADD(SECOND, E.shift_span_seconds, E.shift_start) AS shift_end,
          CONVERT(DATE,E.shift_start) AS event_date,
          COUT.last_checkout AS last_checkout,
          E.event_time_zone
   FROM eventlog E
   INNER JOIN person P ON E.person_id = P.person_id
   LEFT JOIN
     (SELECT E.person_id,
             E.task_id,
             MAX(E.event_time) last_checkout
      FROM eventlog E
      INNER JOIN person P ON E.person_id = P.person_id
      WHERE P.customer_id = @customerparam
        AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
        AND E.shift_start BETWEEN @fromparam AND @toparam
        AND E.event_type = 1
        AND E.is_deleted = 0
      GROUP BY E.person_id,
               E.task_id,
               E.shift_start) COUT ON E.person_id = COUT.person_id
   AND ((E.task_id IS NULL AND COUT.task_id IS NULL)
        OR E.task_id = COUT.task_id)
   AND E.event_time = COUT.last_checkout
   WHERE P.customer_id = @customerparam
     AND E.shift_start BETWEEN @fromparam AND @toparam
     AND E.is_deleted = 0
     AND COUT.last_checkout IS NOT NULL) AS CHECKOUT ON CHECKOUT.person_id = PD.person_id
 AND CHECKOUT.task_id = T.task_id
 AND CHECKOUT.event_date = PD.cloud_date
 WHERE (first_checkin IS NOT NULL
       OR last_checkout IS NOT NULL)) AS ARRIVAL_DEPATURE ON ARRIVAL_DEPATURE.person_id = P.person_id
  AND ARRIVAL_DEPATURE.task_id = P.task_id
  AND ARRIVAL_DEPATURE.cloud_date = P.cloud_date ',

        ' LEFT JOIN
    replacement R ON R.replaced_by_person_id = P.person_id
        AND CONVERT
(DATE,R.date) = P.cloud_date
        AND P.task_id = R.task_id
        LEFT JOIN
        additional_employee A ON A.additional_person_id = P.person_id
        AND CONVERT
(DATE,A.date) = P.cloud_date
        AND P.task_id = A.task_id
        LEFT JOIN
    leave_and_mission LAM ON LAM.person_id = P.person_id
        AND
(LAM.start_date <= P.cloud_date
        AND LAM.end_date >= P.cloud_date AND LAM.approval_status != ''REJECTED'')
        LEFT JOIN
    holiday H ON H.customer_id = @customerparam
        AND H.date = P.cloud_date
        LEFT JOIN
    person PER ON P.person_id = PER.person_id
        LEFT JOIN
    customdata CD ON PER.custom_data_id = CD.custom_data_id
        LEFT JOIN
    task T ON T.task_id = P.task_id
 ORDER BY P.cloud_date , P.person_id , P.task_id ');

SET @qry=concat(@QRY1, @grpqry,  @QRY2 , @grpqry, @QRY3);

    DECLARE @statement NVARCHAR(MAX);
    SET @statement = @qry;
    DECLARE @parameterDefinition NVARCHAR(MAX);
    SET @parameterDefinition = N'@customerparam INT, @groupparam INT, @personparam INT,  @taskparam INT, @daystartparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT,
@countparam INT, @startparam DATETIME, @endparam DATETIME';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@groupparam=@groupparam,@personparam=@personparam,@taskparam=@taskparam,
@daystartparam=@daystartparam,@fromparam=@fromparam,@toparam=@toparam,@offsetparam=@offsetparam,@countparam=@countparam,@startparam=@startparam,@endparam=@endparam;

END;

--TestCases
-- EXEC AbsenteeReport 666,-1,-1,'2018-12-19'  ,'2018-12-19',1,3, 0