CREATE PROCEDURE AdditionalWorkHourReport(IN customerparam INT, IN groupparam INT, 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)

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;


IF periodparam = 1 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV 7)*7 DAY)) AS period_start,  DATE(DATE_SUB(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV 7)*7 + 7 DAY) , INTERVAL 1 SECOND)) AS period_end,  ";
ELSEIF periodparam = 2 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,cloud_date) MONTH )) as period_start, DATE(DATE_SUB(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,cloud_date) + 1 MONTH ), INTERVAL 1 SECOND)) as period_end,   ";
ELSE
	SET @periodqry = " DATE(cloud_date) AS period_start, DATE(DATE_SUB(DATE_ADD(cloud_date , INTERVAL 1 DAY), INTERVAL 1 SECOND)) AS period_end,";
END IF;

IF taskparam < 0 THEN
	SET @taskfilter = " TRUE ";
ELSEIF taskparam = 0 THEN
	SET @taskfilter = " AA.task_id IS NULL ";
ELSE
	SET @taskfilter = " AA.task_id = @taskparam ";
END IF;

IF personlimitparam THEN
	SET @psnlimit =  " LIMIT ? , ? " ;
    SET @datelimit = "";
ELSE
	SET @psnlimit =  "" ;
    SET @datelimit = " LIMIT ? , ? ";
END IF;

SET @selectqry =
CONCAT("SELECT
		P.person_id,"
		, @periodqry ,
       "SUM(TIME_TO_SEC(TIMEDIFF(shift_end, shift_start))) as shift_hours,
	   SUM(shift_work_hours) AS shift_work_hours,
       SUM(workhours) AS work_hours,
       SUM(TIME_TO_SEC(TIMEDIFF(lastcheckout , firstcheckin))) as in_out_time,
       SUM(productivehours) AS productive_hours,
       SUM(LEAST(shift_work_hours, productivehours)) as productive_work_hours,
       SUM(LEAST(
			IF(max_ot <0 , ~0 , max_ot) ,
			(ot_a + ot_b + LEAST(GREATEST(0, productivehours - shift_work_hours), ot_c ))
       )) AS over_time,

       SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(shift_start, firstcheckin)), 0)) as early_arrival,
       SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(firstcheckin,shift_start)), 0)) as late_arrival,
       SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(shift_end, lastcheckout)), 0)) as early_depature,
	   SUM(GREATEST(TIME_TO_SEC(TIMEDIFF(lastcheckout ,shift_end)), 0)) as late_depature,
       MIN(firstcheckin) as first_checkin,
       MAX(lastcheckout) as last_checkout FROM ");


CASE
WHEN personparam >0 THEN
	SET @grpqry = 	"(SELECT person_id FROM person WHERE customer_id = @customerparam AND is_deleted = FALSE AND person_id = @personparam)" ;
	SET @psnfilterqry =" WHERE  PA.customer_id = @customerparam AND PA.is_deleted = FALSE AND PA.person_id = @personparam ";

WHEN groupparam >0 THEN
   SET @grpqry =  "(SELECT member AS person_id FROM user_group_membership WHERE assigned_group_id = @groupparam AND end_date IS NULL)";
   SET @psnfilterqry = "INNER JOIN user_group_membership PB ON PA.person_id = PB.member WHERE  PA.customer_id = @customerparam AND PA.is_deleted = FALSE AND PB.assigned_group_id = @groupparam AND PB.end_date IS NULL ";

ELSE
	SET @grpqry = "(SELECT person_id FROM person WHERE customer_id = @customerparam AND is_deleted = FALSE)" ;
    SET @psnfilterqry = " WHERE  PA.customer_id = @customerparam AND PA.is_deleted = FALSE ";
END CASE;

SET @psnqry = CONCAT("(SELECT P.person_id, PC.cloud_date FROM (SELECT PA.person_id FROM person PA " , @psnfilterqry, @psnlimit, " ) AS P CROSS JOIN cloud_date_table PC WHERE PC.cloud_date BETWEEN @fromparam AND @toparam " , @datelimit ,")");

SET @workhourqry = CONCAT("(SELECT A.person_id,
          DATE(CASE WHEN TIME(A.shift_start) >= @daystartparam THEN A.shift_start ELSE DATE_SUB(A.shift_start, interval 1 day) END) AS workday,
          A.task_id,
          A.maxot as max_ot,
          A.shift_start as shift_start,
          A.shift_end as shift_end,
          A.work_hours AS shift_work_hours,
		  SUM(TIME_TO_SEC(TIMEDIFF( B.event_time ,  A.event_time))) AS workhours,

		SUM(CASE
				WHEN (A.event_time > A.shift_end OR  B.event_time < A.shift_start) then 0
                ELSE TIME_TO_SEC(TIMEDIFF(CASE
                                       WHEN B.event_time < A.shift_end THEN CASE
                                                                                WHEN B.event_time < A.shift_start THEN A.shift_start
                                                                                ELSE B.event_time
                                                                            END
                                       ELSE A.shift_end
                                   END, CASE
                                            WHEN A.event_time> A.shift_start THEN CASE
                                                                                      WHEN A.event_time > A.shift_end THEN A.shift_end
                                                                                      ELSE A.event_time
                                                                                  END
                                            ELSE A.shift_start
                                        END))
			END) as productivehours,

		MIN(A.event_time) as firstcheckin,

        MAX(B.event_time) as lastcheckout,

        SUM(CASE WHEN (A.event_time > A.shift_start) THEN
				0
			ELSE CASE WHEN (A.ot_start IS NULL) THEN
					0
				ELSE TIME_TO_SEC(GREATEST(0,
                    TIMEDIFF(LEAST(A.ot_end, A.shift_start, B.event_time),
                            GREATEST(A.ot_start, A.event_time))))
				END
			END
        ) AS ot_a,

        SUM(CASE WHEN (B.event_time < A.shift_end) THEN
				0
			ELSE CASE WHEN (A.ot_start IS NULL) THEN
					0
				ELSE TIME_TO_SEC(GREATEST(0,
                    TIMEDIFF(LEAST(A.ot_end, B.event_time),
                            GREATEST(A.ot_start, A.shift_end, A.event_time))))
				END
			END
        ) AS ot_b,

        SUM(CASE
			WHEN (A.shift_start >= B.event_time OR A.shift_end <= A.event_time) THEN
				0
			WHEN (A.ot_start IS NULL) THEN
				TIME_TO_SEC(TIMEDIFF(LEAST(A.shift_end, B.event_time), GREATEST(A.shift_start, A.event_time)))
			ELSE
				GREATEST(0 , TIME_TO_SEC(TIMEDIFF(LEAST(A.shift_end, B.event_time, A.ot_end), GREATEST(A.shift_start, A.event_time, A.ot_start))))
			END
        ) AS ot_c

   FROM
     (SELECT AA.event_id, AA.person_id, DATE_ADD(AA.event_time, interval AA.event_time_zone SECOND) AS event_time, AA.shift_start, DATE_ADD(AA.shift_start, interval AA.shift_span_seconds SECOND) AS shift_end,AA.work_hours,AA.task_id,AA.next_event,AA.maxot,AA.ot_start,DATE_ADD(AA.ot_start, interval AA.ot_span_seconds SECOND) AS ot_end
      FROM eventlog AS AA INNER JOIN additional_employee ae on AA.person_id = ae.additional_person_id and AA.task_id = ae.task_id and date(AA.shift_start) = date(ae.date) INNER JOIN "
		, @grpqry
		, "  AB ON AA.person_id = AB.person_id AND ",  @taskfilter ,  " AND AA.event_type=0 AND AA.is_deleted = false AND AA.shift_start BETWEEN @startparam AND @endparam ) AS A INNER JOIN
                (SELECT event_id, person_id,  DATE_ADD(event_time, INTERVAL event_time_zone SECOND) AS event_time  FROM eventlog) AS B ON A.next_event=B.event_id
		GROUP BY workday,task_id,work_hours,maxot,shift_start,shift_end,person_id) " );


SET @qry=concat(@selectqry, @psnqry , "AS P LEFT OUTER JOIN ", @workhourqry, "AS Q ON P.person_id = Q.person_id AND P.cloud_date = Q.workday GROUP BY period_start, period_end, P.person_id");

SET @personworkhourqry = CONCAT("SELECT
	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 (",@qry, ") 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");

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