CREATE PROCEDURE AbsenteeReportPerson(
    @customer_param INT,
    @person_param INT,
    @task_param INT,
    @from_param DATETIME,
    @to_param DATETIME,
    @offset_param INT,
    @count_param INT,
    @day_start_param INT)
AS
BEGIN
    DECLARE @customerparam INT, @personparam INT,  @taskparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT;

    DECLARE @qry NVARCHAR(4000), @taskfilter NVARCHAR(4000);

    SET @daystartparam = CONVERT(TIME, DATEADD(SECOND,@day_start_param,0));
    SET @fromparam = @from_param;
    SET @toparam = DATEADD(SECOND,86399,@to_param);

    SET @customerparam= @customer_param;
    SET @offsetparam= @offset_param;
    SET @countparam= @count_param;
    SET @personparam = @person_param;
    SET @taskparam = @task_param;

    IF @taskparam < 0
	SET @taskfilter = ' ';
ELSE IF @taskparam = 0
	SET @taskfilter = 'AND task_id IS NULL ';
ELSE
	SET @taskfilter = 'AND task_id = @taskParam ';
;

SET @qry = 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,
    CD.data_json AS customdata,
    CONVERT(DATE,ABS.cloud_date) AS period,
    ABS.task_id,
    T.name AS task_name,
    T.description AS task_description,
    ABS.eventlogs,
    ABS.replacement,
    ABS.additional,
    ABS.leave_and_mission,
    ABS.holiday,
    ABS.roster_item
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.person_id = @personparam
            AND PA.is_deleted = 0
            AND PC.cloud_date BETWEEN @fromparam AND @toparam
    ORDER BY PC.cloud_date, PA.person_id OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY ) AS P
    LEFT JOIN (SELECT
        COUNT(E.event_id) AS eventlogs,
            P.person_id,
            E.task_id,
            CONVERT(DATE,(CASE WHEN CONVERT(TIME,E.shift_start) >= @daystartparam THEN E.shift_start ELSE DATEADD(DAY,-1,E.shift_start) END)) AS event_date
    FROM
        eventlog E
    INNER JOIN person P ON E.person_id = P.person_id
    WHERE
        E.is_deleted = 0
            AND P.customer_id = @customerparam
            AND E.shift_start BETWEEN @fromparam AND @toparam ',
        @taskfilter,
' 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(DAY,RO.start_date,P.cloud_date) % 7 * RO.repetition
        AND RI.customer_id = @customerparam

    LEFT JOIN replacement R ON R.replaced_by_person_id = P.person_id
        AND CONVERT(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 CONVERT(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 = 0  ORDER BY ABS.cloud_date , ABS.person_id ');

    DECLARE @statement NVARCHAR(4000);
    SET @statement = @qry;
    DECLARE @parameterDefinition NVARCHAR(4000);
    SET @parameterDefinition = N'@customerparam INT, @personparam INT,  @taskparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT,
@countparam INT';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@personparam=@personparam,@taskparam=@taskparam,
@daystartparam=@daystartparam,@fromparam=@fromparam,@toparam=@toparam,@offsetparam=@offsetparam,@countparam=@countparam;

END;

--TestCases
-- EXEC AbsenteeReportPerson 773,2385,-1,'2018-12-01', '2018-12-30',0,10000, 0