CREATE PROCEDURE WorkHourReport(IN customerparam INT, IN groupparam TEXT, IN personparam INT,  IN taskparam INT, IN periodparam INT,IN daystartparam INT, IN fromparam DATETIME, IN toparam DATETIME, IN offsetparam INT, IN countparam INT, IN personlimitparam BOOL, IN minabsentlateparam INT, IN maxabsentlateparam INT, IN deviceinfoparam INT)
BEGIN
    SET @daystartparam = SEC_TO_TIME(daystartparam);
    SET @fromparam= fromparam;
    SET @toparam= DATE_ADD(toparam, INTERVAL 86399 SECOND);
    SET @startparam = DATE_ADD(fromparam, INTERVAL daystartparam SECOND);
    SET @endparam = DATE_ADD(toparam, INTERVAL (86399 + daystartparam) SECOND);

    SET @customerparam= customerparam;
    SET @offsetparam= offsetparam;
    SET @countparam= countparam;
    SET @groupparam = groupparam;
    SET @personparam = personparam;
    SET @taskparam = taskparam;
    SET @minabsentlateparam = minabsentlateparam;
    SET @maxabsentlateparam = maxabsentlateparam;
    SET @deviceinfoparam = deviceinfoparam;


    IF periodparam = 1 THEN
        SET @periodstartCLOUD = "DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV 7)* 7 DAY)) AS period_start " ;
        SET @periodstartWORKHOUR = "DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( WORK_HOUR_DAILY.work_day, @fromparam) DIV 7)* 7 DAY)) AS period_start,";
        SET @periodendQR = "DATE_ADD(DATE_ADD(P.period_start, INTERVAL 7 DAY) , INTERVAL -1 SECOND ) AS period_end, ";
    ELSEIF periodparam = 2 THEN
        SET @periodstartCLOUD = "DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,cloud_date) MONTH )) as period_start ";
        SET @periodstartWORKHOUR = "DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,WORK_HOUR_DAILY.work_day) MONTH )) as period_start, ";
        SET @periodendQR = "DATE_ADD(DATE_ADD(P.period_start, INTERVAL 1 MONTH) , INTERVAL -1 SECOND ) AS period_end, ";
    ELSEIF periodparam = 3 THEN
        SET @periodstartCLOUD = "DATE(@fromparam) AS period_start ";
        SET @periodstartWORKHOUR = "DATE(@fromparam) AS period_start, ";
        SET @periodendQR = "DATE_ADD( DATE_ADD(@fromparam,  INTERVAL (DATEDIFF( @toparam, @fromparam) + 1)  DAY), INTERVAL -1 SECOND ) AS period_end, ";
    ELSE
        SET @periodstartCLOUD = " DATE(cloud_date) AS period_start ";
        SET @periodstartWORKHOUR = " DATE(WORK_HOUR_DAILY.work_day) AS period_start,";
        SET @periodendQR = "DATE_ADD(DATE_ADD(P.period_start, INTERVAL 1 DAY) , INTERVAL -1 SECOND ) AS period_end, ";
    END IF;



    IF taskparam < 0 THEN
        SET @TASK_FILTER = " ";
    ELSEIF taskparam = 0 THEN
        SET @TASK_FILTER = " AND CIN.task_id IS NULL ";
    ELSE
        SET @TASK_FILTER = " AND CIN.task_id = @taskparam  ";
    END IF;




    IF personlimitparam THEN
        SET @DATE_LIMIT_FILTER = " ";
        SET @PERSON_LIMIT_FILTER = " LIMIT ? , ?  ";
    ELSE
        SET @DATE_LIMIT_FILTER = " LIMIT ? , ?  ";
        SET @PERSON_LIMIT_FILTER = " ";
    END IF;


    IF personparam > 0 THEN
        SET @PERSON_FILTER = " P.person_id = @personparam ";
    ELSE
        SET @PERSON_FILTER = " TRUE ";
    END IF;

    IF groupparam IS NULL OR groupparam ='-1' THEN
        SET @GROUP_FILTER = " TRUE ";
    ELSE
        SET @GROUP_FILTER = CONCAT(" P.person_id IN (SELECT UGM.`member` FROM user_group_membership UGM WHERE  UGM.assigned_group_id IN (", @groupparam , ") AND UGM.end_date IS NULL )");
    END IF;




    SET @query = CONCAT("
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,
		CONVERT(P.period_start,
		DATETIME) 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,
                        SUM(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.shift_end, WORK_HOUR_DAILY.shift_start))) AS shift_hours,
                        SUM(WORK_HOUR_DAILY.shift_work_hours) AS shift_work_hours,
                        SUM(WORK_HOUR_DAILY.workhours) AS work_hours,
                        SUM(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.lastcheckout, WORK_HOUR_DAILY.firstcheckin))) AS in_out_time,
                        SUM(WORK_HOUR_DAILY.productivehours) AS productive_hours,
                        SUM(LEAST(WORK_HOUR_DAILY.shift_work_hours, WORK_HOUR_DAILY.productivehours)) AS productive_work_hours,
                        SUM(over_time) AS over_time,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.shift_start, WORK_HOUR_DAILY.firstcheckin)), 0)) AS early_arrival,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.firstcheckin, WORK_HOUR_DAILY.shift_start)), 0)) AS late_arrival,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.shift_end, WORK_HOUR_DAILY.lastcheckout)),
                                                 0)) AS early_depature,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.lastcheckout, WORK_HOUR_DAILY.shift_end)), 0)) AS late_depature,
                        MIN(WORK_HOUR_DAILY.firstcheckin) AS first_checkin,
                        MAX(WORK_HOUR_DAILY.lastcheckout) AS last_checkout,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.firstcheckin, WORK_HOUR_DAILY.shift_start)), 0) >
                                        @minabsentlateparam) AS min_absent_late_count,
                        SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(WORK_HOUR_DAILY.firstcheckin, WORK_HOUR_DAILY.shift_start)), 0) >
                                        @maxabsentlateparam) AS max_absent_late_count,
                        SUM(WORK_HOUR_DAILY.break_time) AS break_time
                    FROM
                        (
                        SELECT
                            WORK_HOUR_DAILY.*,
                            LEAST(WORK_HOUR_DAILY.maxot, (WORK_HOUR_DAILY.ot_a + WORK_HOUR_DAILY.ot_b +
                                                                 LEAST(GREATEST(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,
                                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,
                                    (CASE
                                        WHEN TIME (CIN.shift_start) >= @daystartparam THEN CIN.shift_start
                                        ELSE DATE_SUB(CIN.shift_start, INTERVAL 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,
                                        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 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 (CIN.is_limitot = 0
                                                        OR CIN.maxot < 0
                                                        OR CIN.maxot IS NULL) THEN 86400
                                                    ELSE CIN.maxot
                                                END)
                                                ELSE 0
                                            END) AS maxot,
                                        CIN.ot_start,
                                        DATE_ADD( CIN.ot_start, INTERVAL CIN.ot_span_seconds SECOND) 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 CIN.shift_start BETWEEN @startparam AND @endparam
                                            AND ", @PERSON_FILTER,
                        " AND " , @GROUP_FILTER,
                        @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


");

    PREPARE stmt FROM @query;
    EXECUTE stmt USING @offsetparam, @countparam;
    DEALLOCATE  PREPARE stmt;
END