set @fromparam= '2018-12-04';
set @toparam= DATE_ADD('2018-12-05', interval 86399 SECOND);
set @customerparam = 773;
set @daystartparam = SEC_TO_TIME(1-1);
set @startparam = DATE_ADD(@fromparam, interval 1 SECOND);
set @endparam = DATE_ADD(@toparam, interval (86399 + 1) SECOND);

set @taskparam = 1;
set @taskfilter=false;
set @taskdefault=false;

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,
    CD.data_json AS customdata,
    ABS.cloud_date AS period,
    ABS.eventlogs,
    ABS.replacement,
    ABS.additional,
    ABS.leave_and_mission,
    ABS.holiday,
    ABS.roster_item,
    ABS.task_id,
    T.name AS task_name, T.description AS task_description
FROM
    person P
    LEFT JOIN
    customdata CD ON CD.custom_data_id = P.custom_data_id 
        INNER JOIN
    (SELECT 
        P.*,E.task_id,
            COUNT(E.eventlogs) AS eventlogs,
            COUNT(R.replacement_id) AS replacement,
            COUNT(A.additional_employee_id) AS additional,
            COUNT(LAM.leave_id) AS leave_and_mission,
            COUNT(H.holiday_id) AS holiday,
            COUNT(RI.roster_item_id)AS roster_item
    FROM
        (SELECT 
        PC.cloud_date, PA.person_id
    FROM
        person PA 
     CROSS JOIN cloud_date_table PC
    WHERE
        PA.customer_id = @customerparam
            AND PA.is_deleted = FALSE
            AND PC.cloud_date BETWEEN @fromparam AND @toparam 
    LIMIT 0 , 10000) AS P
    LEFT JOIN (SELECT 
        COUNT(E.event_id) AS eventlogs,
            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
    WHERE
        E.is_deleted = FALSE
            AND P.customer_id = @customer
            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 , P.person_id , E.task_id) AS E ON E.person_id = P.person_id
        AND E.event_date = P.cloud_date
        

       LEFT JOIN
    user_group_membership UGM ON UGM.start_date <= P.cloud_date
        AND (UGM.end_date > P.cloud_date
        OR UGM.end_date IS NULL)
        AND UGM.member = P.person_id
        LEFT JOIN
    roster_item RI ON (RI.assigned_group = UGM.assigned_group_id
        OR RI.assigned_user = P.person_id) AND RI.task_id = E.task_id
		 LEFT JOIN
    roster RO ON RI.roster_id = RO.roster_id
        AND RO.start_date <= P.cloud_date
        AND (RO.end_date IS NULL
        OR RO.end_date >= P.cloud_date)
        AND RI.day_number = DATEDIFF(P.cloud_date, RO.start_date) % 7 * RO.repetition
        AND RI.customer_id = @customerparam
    
    LEFT JOIN replacement R ON R.replaced_by_person_id = P.person_id
        AND DATE(R.date) = P.cloud_date
        AND E.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 E.task_id = A.task_id
    LEFT JOIN leave_and_mission LAM ON LAM.person_id = P.person_id
        AND (LAM.start_date <= P.cloud_date
        AND LAM.end_date >= P.cloud_date)
    LEFT JOIN holiday H ON H.customer_id = @customerparam
        AND H.date = P.cloud_date
    GROUP BY P.person_id , P.cloud_date, E.task_id
   ) AS ABS ON P.person_id = ABS.person_id LEFT JOIN task T ON ABS.task_id = T.task_id
WHERE
    P.customer_id = @customerparam
        AND P.is_deleted = FALSE  ORDER BY ABS.cloud_date , ABS.person_id