SELECT P.person_id,
       P.employee_code,
       P.first_name,
       P.last_name,
       P.email,
       P.primary_telephone,
       P.address1,
       P.address2,
       P.city,
       P.country,
       P.state_province_region,
       P.zip_or_postal_code,
       P.is_blocked,
       CD.data_json AS customdata,
       WH.*
FROM (
         SELECT P.person_id,
                :DATE_SUMMARY,
                SUM(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.shift_end, WORK_HOUR.shift_start)))                 AS shift_hours,
                SUM(WORK_HOUR.shift_work_hours)                                                        AS shift_work_hours,
                SUM(WORK_HOUR.workhours)                                                               AS work_hours,
                SUM(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.lastcheckout, WORK_HOUR.firstcheckin)))             AS in_out_time,
                SUM(WORK_HOUR.productivehours)                                                         AS productive_hours,
                SUM(LEAST(WORK_HOUR.shift_work_hours, WORK_HOUR.productivehours))                      AS productive_work_hours,
                SUM(LEAST(WORK_HOUR.max_ot, (WORK_HOUR.ot_a + WORK_HOUR.ot_b +
                                             LEAST(GREATEST(0, WORK_HOUR.productivehours - WORK_HOUR.shift_work_hours),
                                                   WORK_HOUR.ot_c))))                                  AS over_time,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.shift_start, WORK_HOUR.firstcheckin)), 0)) AS early_arrival,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.firstcheckin, WORK_HOUR.shift_start)), 0)) AS late_arrival,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.shift_end, WORK_HOUR.lastcheckout)),
                             0))                                                                       AS early_depature,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.lastcheckout, WORK_HOUR.shift_end)), 0))   AS late_depature,
                MIN(WORK_HOUR.firstcheckin)                                                            AS first_checkin,
                MAX(WORK_HOUR.lastcheckout)                                                            AS last_checkout,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.firstcheckin, WORK_HOUR.shift_start)), 0) >
                    :minabsentlateparam)                                                               AS min_absent_late_count,
                SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR.firstcheckin, WORK_HOUR.shift_start)), 0) >
                    :maxabsentlateparam)                                                               AS max_absent_late_count,
                SUM(P.holiday)                                                                         AS holiday,
                SUM(P.leave_and_mission)                                                               AS leave_and_mission,
                SUM(COALESCE(WORK_HOUR.event_count, 0))                                                AS total_attendance_count,
                SUM(IF((NOT P.holiday AND NOT P.leave_and_mission), COALESCE(WORK_HOUR.event_count, 0),
                       0))                                                                             AS total_valid_attendance_count,
                SUM(IF(P.holiday, COALESCE(WORK_HOUR.event_count, 0), 0))                              AS attendance_on_holiday,
                SUM(IF(P.leave_and_mission, COALESCE(WORK_HOUR.event_count, 0), 0))                    AS attendance_on_leave
         FROM (
                  SELECT P.person_id,
                         P.cloud_date,
                         COUNT(H.`date`)      AS holiday,
                         COUNT(LAM.person_id) AS leave_and_mission
                  FROM (
                           SELECT P.person_id,
                                  CDT.cloud_date
                           FROM (
                                    SELECT P.person_id
                                    FROM person P
                                    WHERE P.customer_id = :customerparam
                                      AND P.is_deleted = FALSE
                                      AND (P.person_type IS NULL OR P.person_type = 'EMPLOYEE')
                                      AND :PERSON_FILTER
                                      AND :GROUP_FILTER
                                        :PERSON_LIMIT_FILTER
                                ) AS P
                                    CROSS JOIN cloud_date_table CDT
                           WHERE CDT.cloud_date BETWEEN :fromparam AND :toparam :DATE_LIMIT_FILTER) P
                           LEFT JOIN (
                      SELECT H.`date`
                      FROM holiday H
                      WHERE H.customer_id = :customerparam
                        AND H.is_deleted = FALSE
                        AND H.`date` BETWEEN :fromparam AND :toparam) AS H
                                     ON
                                         P.cloud_date = H.`date`
                           LEFT JOIN (
                      SELECT person_id,
                             start_date,
                             end_date
                      FROM leave_and_mission LAM
                      WHERE LAM.customer_id = :customerparam
                        AND NOT (LAM.end_date < :fromparam
                          OR LAM.start_date > :toparam)
                        AND LAM.approval_status = 'APPROVED') AS LAM
                                     ON
                                                 P.person_id = LAM.person_id
                                             AND P.cloud_date BETWEEN LAM.start_date AND LAM.end_date
                  GROUP BY P.person_id,
                           P.cloud_date) P
                  LEFT JOIN
              (
                  SELECT EVENT_CORE.person_id,
                      DATE
                      (CASE
                      WHEN TIME (EVENT_CORE.shift_start) >= :daystartparam THEN EVENT_CORE.shift_start
                      ELSE DATE_SUB(EVENT_CORE.shift_start, interval 1 DAY)
                      END) AS workday,
              EVENT_CORE.task_id,
             1 AS event_count,
             EVENT_CORE.maxot AS max_ot,
             EVENT_CORE.shift_start AS shift_start,
             EVENT_CORE.shift_end AS shift_end,
             EVENT_CORE.work_hours AS shift_work_hours,
             SUM(TIME_TO_SEC(TIMEDIFF(EVENT_CORE.cout_event_time, EVENT_CORE.cin_event_time))) AS workhours,
             SUM(CASE
             WHEN (EVENT_CORE.cin_event_time > EVENT_CORE.shift_end
             OR EVENT_CORE.cout_event_time < EVENT_CORE.shift_start) THEN 0
             ELSE TIME_TO_SEC(TIMEDIFF(CASE
             WHEN EVENT_CORE.cout_event_time < EVENT_CORE.shift_end THEN CASE
             WHEN EVENT_CORE.cout_event_time < EVENT_CORE.shift_start THEN EVENT_CORE.shift_start
             ELSE EVENT_CORE.cout_event_time
             END
             ELSE EVENT_CORE.shift_end
             END, CASE
             WHEN EVENT_CORE.cin_event_time > EVENT_CORE.shift_start THEN CASE
             WHEN EVENT_CORE.cin_event_time > EVENT_CORE.shift_end THEN EVENT_CORE.shift_end
             ELSE EVENT_CORE.cin_event_time
             END
             ELSE EVENT_CORE.shift_start
             END))
             END) AS productivehours,
             MIN(EVENT_CORE.cin_event_time) AS firstcheckin,
             MAX(EVENT_CORE.cout_event_time) AS lastcheckout,
             SUM(CASE
             WHEN (EVENT_CORE.cin_event_time > EVENT_CORE.shift_start) THEN 0
             ELSE CASE
             WHEN (EVENT_CORE.ot_start IS NULL) THEN 0
             ELSE TIME_TO_SEC(GREATEST(0, TIMEDIFF(LEAST(EVENT_CORE.ot_end, EVENT_CORE.shift_start, EVENT_CORE.cout_event_time), GREATEST(EVENT_CORE.ot_start, EVENT_CORE.cin_event_time))))
             END
             END) AS ot_a,
             SUM(CASE
             WHEN (EVENT_CORE.cout_event_time < EVENT_CORE.shift_end) THEN 0
             ELSE CASE
             WHEN (EVENT_CORE.ot_start IS NULL) THEN 0
             ELSE TIME_TO_SEC(GREATEST(0, TIMEDIFF(LEAST(EVENT_CORE.ot_end, EVENT_CORE.cout_event_time), GREATEST(EVENT_CORE.ot_start, EVENT_CORE.shift_end, EVENT_CORE.cin_event_time))))
             END
             END) AS ot_b,
             SUM(CASE
             WHEN (EVENT_CORE.shift_start >= EVENT_CORE.cout_event_time
             OR EVENT_CORE.shift_end <= EVENT_CORE.cin_event_time) THEN 0
             WHEN (EVENT_CORE.ot_start IS NULL) THEN TIME_TO_SEC(TIMEDIFF(LEAST(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time), GREATEST(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time)))
             ELSE GREATEST(0, TIME_TO_SEC(TIMEDIFF(LEAST(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time, EVENT_CORE.ot_end), GREATEST(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time, EVENT_CORE.ot_start))))
             END) AS ot_c
         FROM
             (
             SELECT
             CIN.person_id,
             DATE_ADD(CIN.event_time, interval CIN.event_time_zone SECOND) AS cin_event_time,
             DATE_ADD(COUT.event_time, interval COUT.event_time_zone SECOND) AS cout_event_time,
             CIN.shift_start,
             DATE_ADD(CIN.shift_start, interval CIN.shift_span_seconds SECOND) AS shift_end,
             CIN.work_hours,
             CIN.task AS task_id,
             (CASE
             WHEN CIN.maxotstatus IS NULL
             OR CIN.maxotstatus != 'REJECTED' THEN IF(CIN.maxot < 0
             OR CIN.maxot IS NULL,
             86400,
             CIN.maxot)
             ELSE 0
             END) AS maxot,
             CIN.ot_start,
             DATE_ADD(CIN.ot_start, interval CIN.ot_span_seconds SECOND) AS ot_end
             FROM
             (
             SELECT
             *,
             IF(CIN.task_id IS NULL,
             0,
             CIN.task_id) AS task
             FROM
             eventlog CIN) AS CIN
             INNER JOIN person P
             INNER JOIN eventlog COUT ON
             CIN.person_id = P.person_id
             AND CIN.next_event = COUT.event_id
             WHERE
             P.is_deleted = FALSE
             AND (P.person_type IS NULL OR P.person_type = 'EMPLOYEE')
             AND P.customer_id = :customerparam
             AND CIN.event_type = 0
             AND CIN.is_deleted = FALSE
             AND DATE_SUB(CIN.shift_start, interval :daystartparam SECOND) BETWEEN :fromparam AND :toparam
             AND :PERSON_FILTER
             AND :GROUP_FILTER
             AND :TASK_FILTER ) AS EVENT_CORE
         GROUP BY
             workday,
             task_id,
             work_hours,
             maxot,
             shift_start,
             shift_end,
             person_id) AS WORK_HOUR ON
             P.person_id = WORK_HOUR.person_id
             AND P.cloud_date = WORK_HOUR.workday
GROUP BY
    period_start,
    period_end,
    P.person_id) AS WH
    LEFT JOIN person P
ON
    WH.person_id = P.person_id
    LEFT JOIN customdata CD ON
    CD.custom_data_id = P.custom_data_id