CREATE PROCEDURE ReplacedWorkHourReport(
    @customer_param INT,
    @group_param INT,
    @person_param INT,
    @task_param INT,
    @period_param INT,
    @day_start_param INT,
    @from_param DATETIME,
    @to_param DATETIME,
    @offset_param INT,
    @count_param INT,
    @person_limit_param BIT)
AS
BEGIN
    DECLARE @customerparam INT, @groupparam INT, @personparam INT,  @taskparam INT, @periodparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT, @personlimitparam BIT, @startparam DATETIME, @endparam DATETIME;
    DECLARE @periodqry NVARCHAR(MAX),@periodgrpqry NVARCHAR(MAX), @workdaygrpqry NVARCHAR(MAX), @taskfilter NVARCHAR(MAX), @psnlimit NVARCHAR(MAX), @datelimit NVARCHAR(MAX), @selectqry NVARCHAR(MAX), @grpqry NVARCHAR(MAX), @psnfilterqry NVARCHAR(MAX), @psnqry NVARCHAR(MAX), @workhourqry NVARCHAR(MAX), @qry NVARCHAR(MAX), @personworkhourqry NVARCHAR(MAX);

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

    SET @customerparam= @customer_param;
    SET @offsetparam= @offset_param;
    SET @countparam= @count_param;
    SET @groupparam = @group_param;
    SET @personparam = @person_param;
    SET @taskparam = @task_param;
    SET @personlimitparam = @person_limit_param;
    SET @periodparam = @period_param;

    IF @periodparam = 1
    BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)), CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7 + 6,@fromparam)) ';
        SET @periodqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)) AS period_start, CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7 + 6,@fromparam)) AS period_end, ';
    END
ELSE IF @periodparam = 2
	BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)), CONVERT(DATE,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)))) ';
        SET @periodqry = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) as period_start, CONVERT(DATE,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)))) as period_end, ';
    END
ELSE
	BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,cloud_date), CONVERT(DATE,cloud_date) ';
        SET @periodqry = ' CONVERT(DATE,cloud_date) AS period_start, CONVERT(DATE,cloud_date) AS period_end, ';
    END
;

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

    IF @personlimitparam = 1
    BEGIN
        SET @psnlimit = ' ORDER BY person_id OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY ';
        SET @datelimit = '';
    END
    ELSE
    BEGIN
        SET @psnlimit =  '';
        SET @datelimit = ' ORDER BY person_id OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY ';
    END
    ;

    SET @workdaygrpqry = ' CONVERT(DATE,(CASE WHEN CONVERT(TIME,A.shift_start) >= @daystartparam THEN A.shift_start ELSE DATEADD(DAY,-1,A.shift_start) END)) ';

    SET @selectqry =
CONCAT(' SELECT
		P.person_id, '
		, @periodqry ,
       ' SUM(DATEDIFF(SECOND,shift_start, shift_end)) as shift_hours,
SUM(shift_work_hours) AS shift_work_hours,
SUM(workhours) AS work_hours,
SUM(DATEDIFF(SECOND,firstcheckin, lastcheckout)) as in_out_time,
SUM(productivehours) AS productive_hours,
SUM(dbo.INTEGER_LEAST_2PARAM(shift_work_hours, productivehours)) as productive_work_hours,
SUM(dbo.INTEGER_LEAST_2PARAM(IIF(max_ot < 0 , 9223372036854775807 , max_ot) ,(ot_a + ot_b + dbo.INTEGER_LEAST_2PARAM(dbo.INTEGER_GREATEST_2PARAM(0, productivehours - shift_work_hours), ot_c )))) AS over_time,

SUM(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND,firstcheckin, shift_start), 0)) as early_arrival,
SUM(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND,shift_start,firstcheckin), 0)) as late_arrival,
SUM(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND,lastcheckout, shift_end), 0)) as early_depature,
SUM(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND,shift_end ,lastcheckout), 0)) as late_depature,
MIN(firstcheckin) as first_checkin,
MAX(lastcheckout) as last_checkout FROM ');

    IF(@personparam > 0)
	BEGIN
        SET @grpqry = 	' (SELECT person_id FROM person WHERE customer_id = @customerparam AND is_deleted = 0 AND person_id = @personparam) '
        ;
        SET @psnfilterqry =' WHERE  PA.customer_id = @customerparam AND PA.is_deleted = 0 AND PA.person_id = @personparam ';
    END

ELSE IF (@groupparam > 0)
BEGIN
        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 = 0 AND PB.assigned_group_id = @groupparam AND PB.end_date IS NULL ';
    END

ELSE
BEGIN
        SET @grpqry = ' (SELECT person_id FROM person WHERE customer_id = @customerparam AND is_deleted = 0) '
        ;
        SET @psnfilterqry = ' WHERE  PA.customer_id = @customerparam AND PA.is_deleted = 0 ';
    END
;

    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, ',
    @workdaygrpqry,
    ' 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(DATEDIFF(SECOND, A.event_time ,  B.event_time)) AS workhours,

    SUM(CASE
				WHEN (A.event_time > A.shift_end OR B.event_time < A.shift_start) then 0
                ELSE DATEDIFF(SECOND, (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),
                                        (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))
			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 dbo.INTEGER_GREATEST_2PARAM(0,DATEDIFF(SECOND,dbo.DATETIME_GREATEST_2PARAM(A.ot_start, A.event_time),dbo.DATETIME_LEAST_3PARAM(A.ot_end, A.shift_start, B.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 dbo.INTEGER_GREATEST_2PARAM(0,DATEDIFF(SECOND,dbo.DATETIME_GREATEST_3PARAM(A.ot_start, A.shift_end, A.event_time),dbo.DATETIME_LEAST_2PARAM(A.ot_end, B.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 DATEDIFF(SECOND,dbo.DATETIME_GREATEST_2PARAM(A.shift_start, A.event_time),dbo.DATETIME_LEAST_2PARAM(A.shift_end, B.event_time))
			  ELSE
				dbo.INTEGER_GREATEST_2PARAM(0,DATEDIFF(SECOND,dbo.DATETIME_GREATEST_3PARAM(A.shift_start, A.event_time, A.ot_start),dbo.DATETIME_LEAST_3PARAM(A.shift_end, B.event_time, A.ot_end)))
			  END) AS ot_c

FROM
    (SELECT AA.event_id, AA.person_id, DATEADD(SECOND,AA.event_time_zone,AA.event_time) AS event_time, AA.shift_start, DATEADD(SECOND,AA.shift_span_seconds,AA.shift_start) AS shift_end,AA.work_hours,AA.task_id,AA.next_event,AA.maxot,AA.ot_start,DATEADD(SECOND,AA.ot_span_seconds,AA.ot_start) AS ot_end
      FROM eventlog AS AA INNER JOIN replacement r on AA.person_id = r.replaced_by_person_id AND AA.task_id = r.task_id AND CONVERT(DATE,AA.shift_start) = CONVERT(DATE,r.date) INNER JOIN ',
      @grpqry,
    ' AB ON AA.person_id = AB.person_id ',
    @taskfilter,
    ' AND AA.event_type = 0 AND AA.is_deleted = 0 AND AA.shift_start BETWEEN @startparam AND @endparam ) AS A INNER JOIN
                (SELECT event_id, person_id,  DATEADD(SECOND,event_time_zone,event_time) AS event_time FROM eventlog) AS B ON A.next_event=B.event_id
		GROUP BY ', @workdaygrpqry,' ,task_id,work_hours,maxot,shift_start,shift_end,A.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 ', @periodgrpqry, ', 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 '
    );

    DECLARE @statement NVARCHAR(MAX);
    SET @statement = @personworkhourqry;
    DECLARE @parameterDefinition NVARCHAR(4000);

    SET @parameterDefinition = N'@customerparam INT, @groupparam INT, @personparam INT,  @taskparam INT, @periodparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT,
@countparam INT, @personlimitparam BIT, @startparam DATETIME, @endparam DATETIME';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@groupparam=@groupparam,@personparam=@personparam,@taskparam=@taskparam,@periodparam=@periodparam,
@daystartparam=@daystartparam,@fromparam=@fromparam,@toparam=@toparam,@offsetparam=@offsetparam,@countparam=@countparam,@personlimitparam=@personlimitparam,@startparam=@startparam,@endparam=@endparam;

END;

--TestCases
-- EXEC ReplacedWorkHourReport 1,-1,-1,-1,1,0,'2019-02-04 00:00:00', '2019-02-04 00:00:00',0,100, 1