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,
    D.description AS device_description,
    D.assigned_user_id AS device_user,
    D.assigned_group_id AS device_group,
    CD.data_json AS customdata,
    WORK_HOUR.*
FROM
    (

SELECT
    P.person_id,
    DATETIME(P.period_start) AS period_start,
    :periodendQR
    WORK_HOUR_DAILY.device_id,
    SUM(WORK_HOUR_DAILY.shift_hours) AS shift_hours,
    SUM(WORK_HOUR_DAILY.shift_work_hours) AS shift_work_hours,
    SUM(WORK_HOUR_DAILY.work_hours) AS work_hours,
    SUM(WORK_HOUR_DAILY.in_out_time) AS in_out_time,
    SUM(WORK_HOUR_DAILY.productive_hours) AS productive_hours,
    (CASE WHEN :deviceinfoparam = 0 THEN SUM(WORK_HOUR_DAILY.productive_work_hours)  ELSE NULL END) AS productive_work_hours,
    (CASE WHEN :deviceinfoparam = 0 THEN SUM(WORK_HOUR_DAILY.over_time) ELSE NULL END) AS over_time,
    SUM(WORK_HOUR_DAILY.early_arrival) AS early_arrival,
    SUM(WORK_HOUR_DAILY.late_arrival) AS late_arrival,
    SUM(WORK_HOUR_DAILY.early_depature) AS early_depature,
    SUM(WORK_HOUR_DAILY.late_depature) AS late_depature,
    MIN(WORK_HOUR_DAILY.first_checkin) AS first_checkin,
    MAX(WORK_HOUR_DAILY.last_checkout) AS last_checkout,
    SUM(WORK_HOUR_DAILY.min_absent_late_count) AS min_absent_late_count,
    SUM(WORK_HOUR_DAILY.max_absent_late_count) AS max_absent_late_count,
    P.holiday AS holiday,
    P.leave_and_mission AS leave_and_mission,
    SUM(WORK_HOUR_DAILY.event_count) AS total_attendance_count,
    SUM(CASE WHEN (WORK_HOUR_DAILY.holiday != 1 AND WORK_HOUR_DAILY.leave_and_mission != 1 ) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS total_valid_attendance_count,
    SUM(CASE WHEN (WORK_HOUR_DAILY.holiday = 1 ) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS attendance_on_holiday,
    SUM(CASE WHEN (WORK_HOUR_DAILY.leave_and_mission = 1 AND WORK_HOUR_DAILY.holiday = 0) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS attendance_on_leave,
    SUM(WORK_HOUR_DAILY.break_time) AS break_time
FROM
    (
        SELECT
            P.person_id,
            P.period_start,
            COUNT(H.date) AS holiday,
            COUNT(LAM.person_id) AS leave_and_mission
        FROM
            (
                SELECT
                    P.person_id,
                    CDT.cloud_date,
                    :periodstartCLOUD
            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 (
			SELECT
				*
			FROM
				cloud_date_table CDT
			WHERE
				CDT.cloud_date BETWEEN :fromparam AND :toparam :DATE_LIMIT_FILTER) AS CDT) AS P
        LEFT JOIN (
        SELECT
            H.date
        FROM
            holiday H
        WHERE
                H.customer_id = :customerparam
          AND H.is_deleted = 0
          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.is_deleted = 0
          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.period_start) AS P
LEFT JOIN


			(
	SELECT
			WORK_HOUR_DAILY.*,
			(CASE
				WHEN H.date IS NULL THEN 0
			ELSE 1
		END) AS holiday,
			(CASE
				WHEN LAM.person_id IS NULL THEN 0
			ELSE 1
		END) AS leave_and_mission
	FROM
		(
		SELECT
			WORK_HOUR_DAILY.work_day,
            :periodstartWORKHOUR
			WORK_HOUR_DAILY.person_id,
			WORK_HOUR_DAILY.device_id,
			1 AS event_count,
			CAST(SUM((julianday(WORK_HOUR_DAILY.shift_end) * 86400 - julianday(WORK_HOUR_DAILY.shift_start) * 86400)) AS INTEGER) AS shift_hours,
			SUM(WORK_HOUR_DAILY.shift_work_hours) AS shift_work_hours,
			SUM(WORK_HOUR_DAILY.workhours) AS work_hours,
			CAST(SUM((julianday(WORK_HOUR_DAILY.lastcheckout) * 86400 - julianday(WORK_HOUR_DAILY.firstcheckin) * 86400)) AS INTEGER) AS in_out_time,
			SUM(WORK_HOUR_DAILY.productivehours) AS productive_hours,
			SUM(MIN(WORK_HOUR_DAILY.shift_work_hours, WORK_HOUR_DAILY.productivehours)) AS productive_work_hours,
			SUM(WORK_HOUR_DAILY.over_time) AS over_time,
				CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.shift_start) * 86400 - julianday(WORK_HOUR_DAILY.firstcheckin) * 86400),
                             0)) AS INTEGER) AS early_arrival,
			CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.firstcheckin) * 86400 - julianday(WORK_HOUR_DAILY.shift_start) * 86400),
                             0)) AS INTEGER) AS late_arrival,
			CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.shift_end) * 86400 - julianday(WORK_HOUR_DAILY.lastcheckout) * 86400),
                             0)) AS INTEGER) AS early_depature,
			CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.lastcheckout) * 86400 - julianday(WORK_HOUR_DAILY.shift_end) * 86400),
                             0)) AS INTEGER) AS late_depature,
			MIN(WORK_HOUR_DAILY.firstcheckin) AS first_checkin,
			MAX(WORK_HOUR_DAILY.lastcheckout) AS last_checkout,
			CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.firstcheckin) * 86400 - julianday(WORK_HOUR_DAILY.shift_start) * 86400), 0) >
                         :minabsentlateparam) AS INTEGER) AS min_absent_late_count,
			CAST(SUM(MAX((julianday(WORK_HOUR_DAILY.firstcheckin) * 86400 - julianday(WORK_HOUR_DAILY.shift_start) * 86400), 0) >
                         :maxabsentlateparam) AS INTEGER) AS max_absent_late_count,
            SUM(WORK_HOUR_DAILY.break_time) AS break_time
		FROM
			(
			SELECT
				WORK_HOUR_DAILY.*,
				MIN((
                                 CASE
                                     WHEN
                                             WORK_HOUR_DAILY.maxot < 0
                                         THEN
                                         2147483647
                                     ELSE
                                         WORK_HOUR_DAILY.maxot
                                     END),
                             (WORK_HOUR_DAILY.ot_a + WORK_HOUR_DAILY.ot_b + MIN(MAX(0, WORK_HOUR_DAILY.productivehours - WORK_HOUR_DAILY.shift_work_hours), WORK_HOUR_DAILY.ot_c))) AS over_time
			FROM
				(
				SELECT
					EVENT_CORE.work_day,
					EVENT_CORE.person_id,
					EVENT_CORE.device_id,
					EVENT_CORE.task_id,
					EVENT_CORE.shift_work_hours,
					EVENT_CORE.maxot,
					EVENT_CORE.shift_start,
					EVENT_CORE.shift_end,
					SUM(EVENT_CORE.break_time) AS break_time,
					CAST (SUM((julianday(EVENT_CORE.cout_event_time)* 86400 - julianday(EVENT_CORE.cin_event_time)* 86400)) AS INTEGER) AS workhours,
					CAST (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
                                         (julianday(
                                                  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
                                              )* 86400 - julianday(
                                                                 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
                                                             )* 86400)
                                     END
                             )AS INTEGER) AS productivehours,
					MIN(EVENT_CORE.cin_event_time) as firstcheckin,
					MAX(EVENT_CORE.cout_event_time) as lastcheckout,
					CAST (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
                                          (MAX(0,
                                               (
                                                           julianday(MIN(EVENT_CORE.ot_end, EVENT_CORE.shift_start, EVENT_CORE.cout_event_time))* 86400 - julianday(MAX(EVENT_CORE.ot_start, EVENT_CORE.cin_event_time ))* 86400
                                                   )
                                              ))
                                      END
                              END
                      ) AS INTEGER) AS ot_a,
					CAST (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
                                          (MAX(0,
                                               (
                                                           julianday(MIN(EVENT_CORE.ot_end, EVENT_CORE.cout_event_time))* 86400 - julianday(MAX(EVENT_CORE.ot_start, EVENT_CORE.shift_end, EVENT_CORE.cin_event_time ))* 86400
                                                   )
                                              ))
                                      END
                              END
                      ) AS INTEGER) AS ot_b,
					CAST (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
                                  (julianday(MIN(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time))* 86400 - julianday(MAX(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time ))* 86400)
                              ELSE
                                  MAX(0, ( julianday(MIN(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time, EVENT_CORE.ot_end))* 86400 - julianday(MAX(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time, EVENT_CORE.ot_start))* 86400 ))
                              END
                      ) AS INTEGER) AS ot_c
				FROM
					(
					SELECT
						CIN.person_id ,
						(CASE
							WHEN TIME (CIN.shift_start) >= TIME (:daystartparam,
							'unixepoch') THEN CIN.shift_start
							ELSE DATETIME(CIN.shift_start, '-1 day')
						END) AS work_day,
						CASE
							WHEN :deviceinfoparam = 0
								OR CIN.device_id IS NULL THEN NULL
								ELSE CIN.device_id
							END AS device_id,
							CASE
								WHEN CIN.task_id IS NULL THEN 0
								ELSE CIN.task_id
							END AS task_id,
							DATETIME(CIN.event_time, '' || CIN.event_time_zone || ' SECONDS') AS cin_event_time,
							DATETIME(COUT.event_time, '' || COUT.event_time_zone || ' SECONDS') AS cout_event_time,
							CIN.shift_start,
							DATETIME(CIN.shift_start, '' || CIN.shift_span_seconds || ' SECONDS') AS shift_end,
							CIN.work_hours AS shift_work_hours,
							IFNULL(COUT.total_breaktime, 0) AS break_time,
							(
                                  CASE
								WHEN
                                              CIN.maxotstatus IS NULL
									OR CIN.maxotstatus != 'REJECTED'
             THEN
                                          CASE
										WHEN
                                                  (
                                                          (NOT CIN.is_limitot)
											OR
                                                          CIN.maxot < 0
											OR CIN.maxot IS NULL
                                                      )
                                                  THEN
                                                  86400
										ELSE
                                                  CIN.maxot
									END
									ELSE
                                          0
								END
                                  )
                                  AS maxot,
							CIN.ot_start,
							DATETIME(CIN.ot_start, '' || CIN.ot_span_seconds || ' SECONDS') AS ot_end
						FROM
							(
							SELECT
								CIN.*
							FROM
								eventlog CIN
							INNER JOIN person P ON
								CIN.person_id = P.person_id
							WHERE
								P.customer_id = :customerparam
								AND P.is_deleted = 0
                                AND (P.person_type IS NULL OR P.person_type = 'EMPLOYEE')
								AND CIN.event_type = 0
								AND CIN.is_deleted = 0
								AND DATETIME(CIN.shift_start, '' || ( - 1 *:daystartparam) || ' SECONDS') BETWEEN :fromparam AND :toparam
							    AND :PERSON_FILTER AND :GROUP_FILTER AND :TASK_FILTER) AS CIN
						INNER JOIN
	 eventlog COUT ON
								CIN.next_event = COUT.event_id


								) AS EVENT_CORE
				GROUP BY
						EVENT_CORE.work_day,
						EVENT_CORE.person_id,
						EVENT_CORE.device_id,
						EVENT_CORE.task_id,
						EVENT_CORE.shift_work_hours,
						EVENT_CORE.maxot,
						EVENT_CORE.shift_start,
						EVENT_CORE.shift_end) AS WORK_HOUR_DAILY) AS WORK_HOUR_DAILY
		GROUP BY
				WORK_HOUR_DAILY.work_day,
				WORK_HOUR_DAILY.person_id,
				WORK_HOUR_DAILY.device_id

				) AS WORK_HOUR_DAILY
	LEFT JOIN (
		SELECT
				H.date
		FROM
				holiday H
		WHERE
				H.customer_id = :customerparam
			AND H.is_deleted = 0
			AND H.date BETWEEN :fromparam AND :toparam) AS H
                                     ON
			WORK_HOUR_DAILY.work_day = 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.is_deleted = 0
			AND LAM.approval_status = 'APPROVED') AS LAM
                                     ON
			WORK_HOUR_DAILY.person_id = LAM.person_id
		AND WORK_HOUR_DAILY.work_day BETWEEN LAM.start_date AND LAM.end_date
	) AS WORK_HOUR_DAILY

ON
    P.person_id = WORK_HOUR_DAILY.person_id
    AND
    P.period_start = WORK_HOUR_DAILY.period_start
GROUP BY
    P.person_id,
    P.period_start,
    P.leave_and_mission,
    P.holiday,
    WORK_HOUR_DAILY.device_id) AS WORK_HOUR
        LEFT JOIN person P ON
            WORK_HOUR.person_id = P.person_id
        LEFT JOIN customdata CD ON
            P.custom_data_id = CD.custom_data_id
        LEFT JOIN device D ON
            WORK_HOUR.device_id = D.device_id