CREATE PROCEDURE WorkHourReportPerson(
    @person_param INT,
    @task_param INT,
    @from_param DATETIME,
    @to_param DATETIME,
    @period_param INT,
    @day_start_param INT)
AS
BEGIN
    DECLARE @personparam INT,  @taskparam INT, @fromparam DATETIME, @toparam DATETIME, @periodparam INT, @daystartparam TIME, @startparam DATETIME, @endparam DATETIME;
    DECLARE @periodgrpqry NVARCHAR(MAX), @workdaygrpqry NVARCHAR(MAX), @taskfilter NVARCHAR(MAX), @selectqry NVARCHAR(MAX), @psnqry NVARCHAR(MAX), @qry 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 @personparam = @person_param;
    SET @taskparam = @task_param;
    SET @periodparam = @period_param;

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

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

    SET @periodgrpqry = ' CONVERT(DATE,cloud_date) ';
    set @selectqry = '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,
    P.customdata,
    DATE(cloud_date) AS period,
    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(CASE
			WHEN (productivehours < shift_work_hours) then 0
            ELSE
				CASE
					WHEN max_ot > 0 THEN dbo.INTEGER_LEAST_2PARAM(max_ot, (productivehours - shift_work_hours))
					ELSE (productivehours - shift_work_hours)
				END
	   END) 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 @periodparam = 1
    BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)) ';
        set @selectqry = '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,
    P.customdata,
    CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)) AS period,
    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(CASE
			WHEN (productivehours < shift_work_hours) then 0
            ELSE
				CASE
					WHEN max_ot > 0 THEN dbo.INTEGER_LEAST_2PARAM(max_ot, (productivehours - shift_work_hours))
					ELSE (productivehours - shift_work_hours)
				END
	   END) 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 ';
    END

ELSE IF @periodparam = 2
BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) ';
        set @selectqry = '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,
    P.customdata,
    CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) as period,
    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(CASE
			WHEN (productivehours < shift_work_hours) then 0
            ELSE
				CASE
					WHEN max_ot > 0 THEN dbo.INTEGER_LEAST_2PARAM(max_ot, (productivehours - shift_work_hours))
					ELSE (productivehours - shift_work_hours)
				END
	   END) 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 ';
    END
;

    SET @psnqry = ' (SELECT
    PA.person_id,
    PA.employee_code,
    PA.first_name,
    PA.last_name,
    PA.email,
    PA.primary_telephone,
    PA.address1,
    PA.address2,
    PA.city,
    PA.country,
    PA.state_province_region,
    PA.zip_or_postal_code,
    PA.is_blocked,
    CD.data_json AS customdata,
    PC.cloud_date FROM person PA
				LEFT JOIN customdata CD ON PA.custom_data_id = CD.custom_data_id
                CROSS JOIN cloud_date_table PC
                WHERE  person_id = @personparam AND PC.cloud_date BETWEEN @fromparam AND @toparam) ';

    SET @qry= CONCAT(@selectqry,
    @psnqry,
    ' AS P LEFT OUTER JOIN
			(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
		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
				FROM eventlog AS AA WHERE ',@taskfilter,' 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) Q
                ON P.person_id = Q.person_id  AND P.cloud_date = Q.workday
				GROUP BY ',  @periodgrpqry, ' , 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, P.customdata ORDER BY ',@periodgrpqry,' ');



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

    SET @parameterDefinition = N'@personparam INT,  @taskparam INT, @periodparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME, @startparam DATETIME, @endparam DATETIME';
    EXEC sp_executesql @statement,@parameterDefinition,@personparam=@personparam,@taskparam=@taskparam,@periodparam=@periodparam,
@daystartparam=@daystartparam,@fromparam=@fromparam,@toparam=@toparam,@startparam=@startparam,@endparam=@endparam;

END;

--TestCases
-- EXEC WorkHourReportPerson 1,-1,'2018-05-30', '2018-05-31',2, 0