CREATE PROCEDURE AbsenteeReport(IN customerparam INT, IN groupparam INT, IN personparam INT, IN taskparam INT, IN fromparam DATETIME, IN toparam DATETIME, IN offsetparam INT, IN countparam INT, IN daystartparam INT, IN periodparam 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 @taskfilter=true;
set @taskdefault=false;
if  taskparam >=0 then 
	set @taskfilter = true;
	if taskparam = 0 then set @taskdefault = true;
    end if;
else
	set @taskfilter=false;
end if;

IF periodparam = 1 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( AB.period, @fromparam) DIV 7)*7 DAY)) AS period_start,  DATE(DATE_SUB(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( AB.period, @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 ,AB.period) MONTH )) as period_start, DATE(DATE_SUB(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,AB.period) + 1 MONTH ), INTERVAL 1 SECOND)) as period_end,   ";
ELSEIF periodparam = 3 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( AB.period, @fromparam) DIV @daydifference)*@daydifference DAY)) AS period_start,  DATE(DATE_SUB(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( AB.period, @fromparam) DIV @daydifference)*@daydifference + @daydifference DAY) , INTERVAL 1 SECOND)) AS period_end,  ";
ELSE
	SET @periodqry = " DATE(AB.period) AS period_start, DATE(DATE_SUB(DATE_ADD(AB.period , INTERVAL 1 DAY), INTERVAL 1 SECOND)) AS period_end,";
END IF;

set @grpqry = case when groupparam < 0 then  
	" " 
else 
	" INNER JOIN user_group_membership UGM ON PA.person_id = UGM.member AND UGM.end_date IS NULL AND UGM.assigned_group_id = @groupparam " 
end;

set @psnqry = case when personparam < 0 then
	" "
else
	" AND PA.person_id = @personparam "
end;

SET @QRY1  = "
    AB.person_id,
    AB.employee_code,
    AB.first_name,
    AB.last_name,
    AB.email,
    AB.primary_telephone,
    AB.address1,
    AB.address2,
    AB.city,
    AB.country,
    AB.state_province_region,
    AB.zip_or_postal_code,
    AB.is_blocked,
    AB.customdata,
    SUM(AB.leave_and_mission) as leave_and_mission,
    SUM(AB.holiday) as holiday,

    SUM(AB.eventlogs) as eventlogs,
    SUM(AB.replacement) as replacement,
    SUM(AB.additional) as additional,
    SUM(AB.roster_item) as roster_item,
    MIN(substring(AB.checkin_timezone,1,19)) AS first_checkin,
    MIN(cast(substring(AB.checkin_timezone,20) as unsigned)) AS first_checkin_timezone,
    SUM(AB.early_arrival) as early_arrival,
    SUM(AB.late_arrival) as late_arrival,
    MAX(substring(AB.checkout_timezone,1,19)) AS last_checkout,
    MAX(cast(substring(AB.checkout_timezone,20) as unsigned)) AS last_checkout_timezone,
    SUM(AB.early_depature) as  early_depature,
    SUM(AB.late_depature) as late_depature

 FROM (
    SELECT
	PER.employee_code,
    PER.first_name,
    PER.last_name,
    PER.email,
    PER.primary_telephone,
    PER.address1,
    PER.address2,
    PER.city,
    PER.country,
    PER.state_province_region,
    PER.zip_or_postal_code,
    PER.is_blocked,
    CD.data_json AS customdata,
    LAM.leave_id IS NOT NULL AS leave_and_mission,
	LAM.leave_policy AS leave_policy,
    H.holiday_id IS NOT NULL AS holiday,
    AD.*
    from
(SELECT
    P.person_id,
    P.cloud_date AS period,
    SUM(P.event_logs) AS eventlogs,
    SUM(R.replacement_id IS NOT NULL) AS replacement,
    SUM(A.additional_employee_id IS NOT NULL) AS additional,
    SUM(P.roster_item) as roster_item,
    SUM(P.roster_item <=0 AND P.event_logs <= 0) AS no_shift_no_work,
    SUM(P.roster_item <=0 AND P.event_logs > 0) AS no_shift_work,
    SUM(P.roster_item > 0 AND P.event_logs <= 0) AS shift_no_work,
    SUM(P.roster_item > 0 AND P.event_logs > 0) AS shift_work,
    MIN(ARRIVAL_DEPATURE.checkin_timezone) as checkin_timezone,
    SUM(ARRIVAL_DEPATURE.early_arrival) as early_arrival,
    SUM(ARRIVAL_DEPATURE.late_arrival) as late_arrival,
    MAX(ARRIVAL_DEPATURE.checkout_timezone) as checkout_timezone,
    SUM(ARRIVAL_DEPATURE.early_depature) as early_depature,
    SUM(ARRIVAL_DEPATURE.late_depature) as late_depature


FROM
    (SELECT 
        R.cloud_date,
            R.person_id,
            R.task_id,
            CAST(AVG(R.roster_item) AS UNSIGNED) AS roster_item,
            SUM(IF(R.task_id != E.task_id, 0, E.event_logs)) AS event_logs
    FROM
        (SELECT 
    P.*,
    ((P_RI.task_id IS NOT NULL) + (G_RI.task_id IS NOT NULL) + (AG_RI.task_id IS NOT NULL)) AS roster_item
    FROM
        (SELECT 
    *
FROM
    (SELECT 
        PC.cloud_date, PA.person_id
    FROM
        person PA ";
        
SET @QRY2 = " CROSS JOIN cloud_date_table PC ON PA.is_deleted = FALSE
        AND PA.customer_id = @customerparam AND (PA.person_type IS NULL OR PA.person_type = 'EMPLOYEE')";



SET @QRY4 =        " AND PC.cloud_date BETWEEN @fromparam AND @toparam  ORDER BY PC.cloud_date, PA.person_id
    LIMIT ? OFFSET ?) PD
        CROSS JOIN
    (SELECT 
        task_id
    FROM
        (SELECT 
        IF(value = 0, 0, task_id) AS task_id
    FROM
        boolean_table B
    LEFT JOIN task T ON T.customer_id = @customerparam
    GROUP BY value , task_id) T
    WHERE
        task_id IS NOT NULL
            AND (NOT @taskfilter
            OR ((NOT @taskdefault
            AND task_id = @taskparam)
            OR (@taskdefault AND task_id = 0)))
    GROUP BY task_id) T) AS P
     LEFT JOIN
    (SELECT
        RI.cloud_date, RI.task_id, RI.ri_person
    FROM
        (SELECT
        CD_RI.cloud_date,
            CD_RI.assigned_user AS ri_person,
            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 @fromparam AND @toparam
            AND RI.assigned_user IS NOT NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATE_ADD(CD.cloud_date, INTERVAL 86399 SECOND))
            ) 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 = DATEDIFF(CD_RI.cloud_date, RO.start_date) % 7 * RO.repetition) AS RI
    WHERE
        RI.available > 0) AS P_RI ON P.cloud_date = P_RI.cloud_date
        AND P.person_id = P_RI.ri_person
        AND P.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 @fromparam AND @toparam
            AND RI.assigned_group IS NOT NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATE_ADD(CD.cloud_date, INTERVAL 86399 SECOND))
            ) 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 = DATEDIFF(CD_RI.cloud_date, RO.start_date) % 7 * RO.repetition) AS RI
    INNER JOIN user_group_membership UGM ON ri_group = UGM.assigned_group_id
        AND UGM.start_date <= RI.cloud_date
        AND (UGM.end_date > RI.cloud_date
        OR UGM.end_date IS NULL)
        AND RI.available > 0) AS G_RI ON P.cloud_date = G_RI.cloud_date
        AND P.person_id = G_RI.ri_group_person
        AND P.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 @fromparam AND @toparam
            AND RI.assigned_group IS NULL
            AND RI.assigned_user IS NULL
            AND (RI.deleted_date IS NULL OR RI.deleted_date >= CD.cloud_date)
            AND (RI.end_date IS NULL OR RI.end_date >= CD.cloud_date)
            AND (RI.start_date IS NULL OR RI.start_date < DATE_ADD(CD.cloud_date, INTERVAL 86399 SECOND))
            ) 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 = DATEDIFF(CD_RI.cloud_date, RO.start_date) % 7 * RO.repetition) AS RI
    CROSS JOIN person P
    WHERE
        P.customer_id = @customerparam
            AND RI.available > 0) AS AG_RI ON P.cloud_date = AG_RI.cloud_date
        AND P.person_id = AG_RI.ri_alluser_person
        AND P.task_id = AG_RI.task_id) R
    CROSS JOIN (SELECT 
        P.*,
            IF(E.event_logs IS NULL, 0, E.event_logs) AS event_logs,
            IF(E.task_id IS NULL, 0, E.task_id) AS task_id
    FROM
        (SELECT 
    PC.cloud_date, PA.person_id
FROM
    person PA " ;        


SET @QRY3 = " CROSS JOIN
    cloud_date_table PC ON PA.is_deleted = FALSE
        AND PA.customer_id = @customerparam
        AND PC.cloud_date BETWEEN @fromparam AND @toparam  ORDER BY PC.cloud_date, PA.person_id
LIMIT ? OFFSET ?) AS P
    LEFT JOIN (SELECT 
        COUNT(E.event_id) AS event_logs,
            P.person_id,
            E.task_id,
            DATE(CASE
                WHEN TIME(E.shift_start) >= @daystartparam THEN E.shift_start
                ELSE DATE_SUB(E.shift_start, INTERVAL 1 DAY)
            END) AS event_date
    FROM
        eventlog E
    INNER JOIN person P ON E.person_id = P.person_id
        AND P.customer_id = @customerparam
        AND E.is_deleted = FALSE
        AND E.shift_start BETWEEN @fromparam AND @toparam
        AND (NOT @taskfilter
        OR ((NOT @taskdefault
        AND E.task_id = @taskparam)
        OR (@taskdefault AND E.task_id IS NULL)))
    GROUP BY E.shift_start , E.person_id , E.task_id) AS E ON P.person_id = E.person_id
        AND P.cloud_date = E.event_date) E ON R.cloud_date = E.cloud_date
        AND R.person_id = E.person_id
    GROUP BY cloud_date , person_id , task_id) AS P

#ARRIVAL DEPATURE

    LEFT JOIN
    (SELECT
    PD.*,
    T.*,

	CHECKIN.shift_start AS shift_start,
        CHECKOUT.shift_end AS shift_end,
    CONCAT(CHECKIN.first_checkin, LPAD(CHECKIN.event_time_zone, 8, '0')) as checkin_timezone,
    GREATEST(TIME_TO_SEC(TIMEDIFF(CHECKIN.shift_start,
                            DATE_ADD(CHECKIN.first_checkin,
                                INTERVAL CHECKIN.event_time_zone SECOND))),
            0) AS early_arrival,
    GREATEST(TIME_TO_SEC(TIMEDIFF(DATE_ADD(CHECKIN.first_checkin,
                                INTERVAL CHECKIN.event_time_zone SECOND),
                            CHECKIN.shift_start)),
            0) AS late_arrival,
    CONCAT(CHECKOUT.last_checkout, LPAD(CHECKOUT.event_time_zone, 8, '0')) as checkout_timezone,
    GREATEST(TIME_TO_SEC(TIMEDIFF(CHECKOUT.shift_end,
                            DATE_ADD(CHECKOUT.last_checkout,
                                INTERVAL CHECKOUT.event_time_zone SECOND))),
            0) AS early_depature,
    GREATEST(TIME_TO_SEC(TIMEDIFF(DATE_ADD(CHECKOUT.last_checkout,
                                INTERVAL CHECKOUT.event_time_zone SECOND),
                            CHECKOUT.shift_end)),
            0) AS late_depature
FROM
    (SELECT
        PC.cloud_date, PA.person_id
    FROM
        person PA
    CROSS JOIN cloud_date_table PC ON PA.is_deleted = FALSE
        AND PA.customer_id = @customerparam
        AND PC.cloud_date BETWEEN @fromparam AND @toparam
    ORDER BY PC.cloud_date , PA.person_id
    LIMIT ? OFFSET ?) PD
        CROSS JOIN
    (SELECT
        task_id
    FROM
        (SELECT
        IF(value = 0, 0, task_id) AS task_id
    FROM
        boolean_table B
    LEFT JOIN task T ON T.customer_id = @customerparam
    GROUP BY value , task_id) T
    WHERE
        task_id IS NOT NULL
            AND (NOT @taskfilter
            OR ((NOT @taskdefault
            AND task_id = @taskparam)
            OR (@taskdefault AND task_id = 0)))
    GROUP BY task_id) T
        LEFT JOIN
    (SELECT
        E.person_id,
            IF(E.task_id IS NULL, 0, E.task_id) AS task_id,
            E.shift_start,
            DATE_ADD(E.shift_start, INTERVAL E.shift_span_seconds SECOND) 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 @fromparam AND @toparam
            AND E.event_type = 0
            AND E.is_deleted = FALSE
    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 E.shift_start BETWEEN @fromparam AND @toparam
            AND E.is_deleted = FALSE
            AND CIN.first_checkin IS NOT NULL) AS CHECKIN ON
            CHECKIN.person_id = PD.person_id
        AND CHECKIN.task_id = T.task_id
        AND CHECKIN.event_date = PD.cloud_date
        LEFT JOIN
    (SELECT
        E.person_id,
            IF(E.task_id IS NULL, 0, E.task_id) AS task_id,
            E.shift_start,
            DATE_ADD(E.shift_start, INTERVAL E.shift_span_seconds SECOND) AS shift_end,
            DATE(E.shift_start) AS event_date,
            COUT.last_checkout AS last_checkout,
            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, MAX(E.event_time) last_checkout
    FROM
        eventlog E
    INNER JOIN person P ON E.person_id = P.person_id
    WHERE
        P.customer_id = @customerparam
            AND E.shift_start BETWEEN @fromparam AND @toparam
            AND E.event_type = 1
            AND E.is_deleted = FALSE
    GROUP BY E.person_id , E.task_id,E.shift_start) COUT ON E.person_id = COUT.person_id
        AND ((E.task_id IS NULL
        AND COUT.task_id IS NULL)
        OR E.task_id = COUT.task_id)
        AND E.event_time = COUT.last_checkout
    WHERE
        P.customer_id = @customerparam
            AND E.shift_start BETWEEN @fromparam AND @toparam
            AND E.is_deleted = FALSE
            AND COUT.last_checkout IS NOT NULL) AS CHECKOUT ON
            CHECKOUT.person_id = PD.person_id
        AND CHECKOUT.task_id = T.task_id
        AND CHECKOUT.event_date = PD.cloud_date
WHERE
    (first_checkin IS NOT NULL
        OR last_checkout IS NOT NULL)) AS ARRIVAL_DEPATURE ON
            ARRIVAL_DEPATURE.person_id = P.person_id
        AND ARRIVAL_DEPATURE.task_id = P.task_id
        AND ARRIVAL_DEPATURE.cloud_date = P.cloud_date




        LEFT JOIN
    replacement R ON R.replaced_by_person_id = P.person_id
        AND DATE(R.date) = P.cloud_date
        AND P.task_id = R.task_id
        LEFT JOIN
    additional_employee A ON A.additional_person_id = P.person_id
        AND DATE(A.date) = P.cloud_date
        AND P.task_id = A.task_id

        #LEFT JOIN task T ON T.task_id = P.task_id
 GROUP BY P.cloud_date , P.person_id ORDER BY P.cloud_date, P.person_id) as AD

 LEFT JOIN
    leave_and_mission LAM ON LAM.person_id = AD.person_id
        AND (LAM.start_date <= AD.period
        AND LAM.end_date >= AD.period AND LAM.approval_status != 'REJECTED')
        LEFT JOIN
    holiday H ON H.customer_id = @customerparam
        AND H.date = AD.period
        LEFT JOIN
    person PER ON AD.person_id = PER.person_id
        LEFT JOIN
    customdata CD ON PER.custom_data_id = CD.custom_data_id) AS AB GROUP BY period_start, period_end, person_id";


SET @qry=concat(@QRY1, @grpqry,  @QRY2 , @psnqry, @QRY4, @grpqry, @QRY3);
SET @SELECTQRY = concat("SELECT ", @periodqry, @qry);

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