SELECT   BASE.period_start,
          BASE.period_end,
          BASE.ri_count,
          BASE.event_count,
          BASE.min_late_count,
          BASE.max_late_count,
          BASE.not_attended_roster_count,
          BASE.attended_roster_count,
          BASE.not_attended_no_roster_count,
          BASE.attended_no_roster_count ,
          P.person_id,
          P.employee_code,
          P.first_name,
          P.last_name,
          CDP.data_json AS customdata,
          T.task_id,
          T.name AS task_name,
          CDT.data_json AS customdata_task
FROM      (
                   SELECT
                        :GROUP_TYPE_FILTER


                            ABSENT.person_id,
                            ABSENT.task_id,
                            SUM(ABSENT.ri_available)    AS ri_count,
                            SUM(ABSENT.event_available) AS event_count,
                            SUM(min_late)               AS min_late_count,
                            SUM(max_late)               AS max_late_count,
                            SUM(not_attended_roster)    AS not_attended_roster_count,
                            SUM(attended_roster)        AS attended_roster_count,
                            SUM(not_attended_no_roster) AS not_attended_no_roster_count,
                            SUM(attended_no_roster)     AS attended_no_roster_count
                   FROM     (
                                      SELECT    DPTR.*,
                                                (CIN.first_checkin IS NOT NULL)                                                                          AS event_available,
                                                CAST((JULIANDAY(DATETIME(CIN.first_checkin, '' ||CIN.event_time_zone ||' SECONDS')) - JULIANDAY(CIN.shift_start))*24*60*60 AS INTEGER) AS late_seconds,
                                                DPTR.ri_available
                                      AND       CIN.first_checkin IS NOT NULL
                                      AND       (
                                                          CAST((JULIANDAY(DATETIME(CIN.first_checkin, ''||CIN.event_time_zone ||' SECONDS')) - JULIANDAY(CIN.shift_start))*24*60*60 AS INTEGER) > :minlate
                                                AND       CAST((JULIANDAY(DATETIME(CIN.first_checkin, ''||CIN.event_time_zone ||' SECONDS')) - JULIANDAY(CIN.shift_start))*24*60*60 AS INTEGER) <:maxlate
                                                ) AS min_late,
                                                DPTR.ri_available
                                      AND       CIN.first_checkin IS NOT NULL
                                      AND       CAST((JULIANDAY(DATETIME(CIN.first_checkin, ''||CIN.event_time_zone ||' SECONDS')) - JULIANDAY(CIN.shift_start))*24*60*60 AS INTEGER) >= :maxlate AS max_late,
                                                (DPTR.ri_available AND LANDM.leave_id IS NULL  AND H_DAY.holiday_id IS NULL)
                                      AND       CIN.first_checkin IS NULL AS not_attended_roster,
                                                DPTR.ri_available
                                      AND       CIN.first_checkin IS NOT NULL AS attended_roster,
                                                NOT DPTR.ri_available
                                      AND       CIN.first_checkin IS NOT NULL AS attended_no_roster,
                                                NOT DPTR.ri_available
                                      AND       CIN.first_checkin IS NULL AS not_attended_no_roster
                                      FROM      (
                                                          SELECT    DPT.*,
                                                                    ((P_RI.ri_available IS NOT NULL) + (G_RI.task_id IS NOT NULL) + (AG_RI.task_id IS NOT NULL)) >0 AS ri_available
                                                          FROM      (
                                                                               SELECT     *
                                                                               FROM       (
                                                                                                     SELECT     PC.cloud_date,
                                                                                                                PA.person_id
                                                                                                     FROM       (
                                                                                                                           SELECT     P.person_id
                                                                                                                           FROM       person P


                                                                                                                           :GROUP_FILTER



                                                                                                                           WHERE      P.customer_id = :customerparam
                                                                                                                           AND        NOT P.is_deleted
                                                                                                                           AND        (
                                                                                                                                                 NOT (:personparam > 0)
                                                                                                                                      OR         P.person_id = :personparam)) AS PA
                                                                                                     CROSS JOIN cloud_date_table PC
                                                                                                     ON         PC.cloud_date BETWEEN DATETIME(:fromParam) AND        DATETIME(:toParam) LIMIT :limitparam OFFSET :offsetparam) PD
                                                                               CROSS JOIN
                                                                                          (
                                                                                                   SELECT   T.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
                                                                                                                      AND       T.is_deleted = 0
                                                                                                                      GROUP BY  value ,
                                                                                                                                T.task_id) AS T
                                                                                                   WHERE    (
                                                                                                                     :taskparam < 0
                                                                                                            OR       :taskparam = T.task_id)
                                                                                                   GROUP BY T.task_id) AS T
                                                                               ORDER BY   PD.cloud_date ,
                                                                                          PD.person_id ,
                                                                                          T.task_id) AS DPT
                                                          LEFT JOIN
                                                                    (
                                                                              SELECT    CD_RI.cloud_date,
                                                                                        CD_RI.assigned_user AS person_id,
                                                                                        CD_RI.task_id,
                                                                                        RO.roster_id AS ri_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 DATETIME(:fromParam) AND        DATETIME(:toParam)
                                                                                                   AND        RI.assigned_user IS NOT NULL
                                                                                                   AND        RI.deleted_date IS NULL
                                                                                                   AND        (RI.start_date IS NULL OR RI.start_date < DATETIME(CD.cloud_date, '86399 SECONDS'))
                                                                                                   ) 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 = (CAST((JULIANDAY(CD_RI.cloud_date)-JULIANDAY(RO.start_date)) AS INTEGER)) % 7 * RO.repetition) AS P_RI
                                                          ON        DPT.cloud_date = P_RI.cloud_date
                                                          AND       DPT.person_id = P_RI.person_id
                                                          AND       DPT.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,
                                                                                                              RO.roster_id IS NOT NULL 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 DATETIME(:fromParam) AND        DATETIME(:toParam)
                                                                                                                         AND        RI.assigned_group IS NOT NULL
                                                                                                                         AND        RI.deleted_date IS NULL
                                                                                                                         AND        (RI.start_date IS NULL OR RI.start_date < DATETIME(CD.cloud_date, '86399 SECONDS'))) 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 = (CAST((JULIANDAY(CD_RI.cloud_date)-JULIANDAY(RO.start_date)) AS INTEGER)) % 7 * RO.repetition) AS RI
                                                                               INNER JOIN user_group_membership UGM
                                                                               ON         ri_group = UGM.assigned_group_id
                                                                               AND        (
                                                                                                     UGM.end_date > RI.cloud_date
                                                                                          OR         UGM.end_date IS NULL)
                                                                               AND        RI.available > 0) AS G_RI
                                                          ON        DPT.cloud_date = G_RI.cloud_date
                                                          AND       DPT.person_id = G_RI.ri_group_person
                                                          AND       DPT.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,
                                                                                                              RO.roster_id IS NOT NULL 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 DATETIME(:fromParam) AND        DATETIME(:toParam)
                                                                                                                         AND        RI.assigned_group IS NULL
                                                                                                                         AND        RI.assigned_user IS NULL
                                                                                                                         AND        RI.deleted_date IS NULL
                                                                                                                         AND        (RI.start_date IS NULL OR RI.start_date < DATETIME(CD.cloud_date, '86399 SECONDS'))) 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 = (CAST((JULIANDAY(CD_RI.cloud_date)-JULIANDAY(RO.start_date)) AS INTEGER)) % 7 * RO.repetition) AS RI
                                                                               CROSS JOIN person P
                                                                               WHERE      P.customer_id = :customerparam
                                                                               AND        RI.available > 0) AS AG_RI
                                                          ON        DPT.cloud_date = AG_RI.cloud_date
                                                          AND       DPT.person_id = AG_RI.ri_alluser_person
                                                          AND       DPT.task_id = AG_RI.task_id ) AS DPTR
                                      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,
                                                                      DATETIME(E.shift_start, ''||E.shift_span_seconds ||' SECONDS') AS shift_end,
                                                                      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        E.shift_start BETWEEN DATETIME(:fromParam) AND        DATETIME(DATETIME(:toParam), '86399 SECONDS')
                                                                                 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.is_deleted = 0
                                                           AND        E.shift_start BETWEEN DATETIME(:fromParam) AND        DATETIME(DATETIME(:toParam), '86399 SECONDS')
                                                           AND        E.is_deleted = 0
                                                           AND        CIN.first_checkin IS NOT NULL ) AS CIN
                                      ON        CIN.person_id = DPTR.person_id
                                      AND       CIN.task_id = DPTR.task_id
                                      AND       DATE(CIN.event_date) = DATE(DPTR.cloud_date)

                                      LEFT JOIN (
						SELECT * FROM leave_and_mission
                                        ) AS LANDM
                                      ON 	DPTR.cloud_date BETWEEN LANDM.start_date AND LANDM.end_date
                                      AND	DPTR.person_id = LANDM.person_id
                                      AND	LANDM.approval_status = 'APPROVED'
                                      AND	LANDM.is_deleted = 0


                                         LEFT JOIN (
						SELECT * FROM holiday
                                        ) AS H_DAY
                                      ON 	DPTR.cloud_date = H_DAY.date
                                      AND	H_DAY.is_deleted = 0
                                      AND	H_DAY.customer_id = :customerparam

                                      ) AS ABSENT
                   WHERE ABSENT.ri_available >0

                   GROUP BY period_start,
                            period_end,
                            ABSENT.person_id,
                            ABSENT.task_id) AS BASE
LEFT JOIN person P
ON        BASE.person_id = P.person_id
LEFT JOIN customdata CDP
ON        CDP.custom_data_id = P.custom_data_id
LEFT JOIN task T
ON        BASE.task_id = T.task_id
LEFT JOIN customdata CDT
ON 		  CDT.custom_data_id = T.custom_data_id