CREATE PROCEDURE ArrivalReport(
    @customer_param INT,
    @group_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, @groupparam INT, @taskparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT, @daystartparam TIME, @startparam DATETIME, @endparam DATETIME;
    DECLARE @workdaygrpqry NVARCHAR(MAX), @taskfilter NVARCHAR(MAX),  @selectqry NVARCHAR(MAX), @grpqry NVARCHAR(MAX), @psnqry NVARCHAR(MAX), @qry NVARCHAR(MAX);

    SET @fromparam = @from_param;
    SET @toparam = DATEADD(SECOND,86399,@to_param);
    SET @daystartparam = CONVERT(TIME, DATEADD(SECOND,@day_start_param,0));
    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 @taskparam = @task_param;

    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 ';
;

    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 = ' SELECT P.*,
       CONVERT(DATE,cloud_date) AS period,
       task_id,
       task_name,
       shift_start,
       firstcheckin,
       firstcheckin_timezone,
       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
FROM ';

    SET @grpqry = CASE WHEN @groupparam < 0 THEN
' (SELECT person_id FROM person WHERE customer_id = @customerparam AND is_deleted = 0) '
ELSE
' (SELECT member AS person_id FROM user_group_membership WHERE assigned_group_id = @groupparam AND end_date IS NULL)'
END;

    SET @psnqry = CASE WHEN @groupparam < 0 THEN
' (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 PA.customer_id = @customerparam AND PA.is_deleted = 0 and PC.cloud_date BETWEEN @fromparam and @toparam
ORDER BY first_name, last_name OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY
) '
    ELSE
    '
(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 CD.custom_data_id = PA.custom_data_id INNER JOIN user_group_membership PB ON PA.person_id=PB.member AND PB.end_date IS NULL CROSS JOIN cloud_date_table PC
WHERE PB.assigned_group_id = @groupparam and PA.is_deleted = 0 and PC.cloud_date BETWEEN @fromparam AND @toparam
ORDER BY PA.first_name, PA.last_name OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY
) '
    END;

    SET @qry = CONCAT(
    @selectqry,
    @psnqry,
    'AS P
LEFT OUTER JOIN
  (SELECT A.person_id,
          ',
    @workdaygrpqry, ' AS workday,
          A.task_id,
          A.shift_start AS shift_start,
          MIN(A.event_time) AS firstcheckin,
          A.event_time_zone AS firstcheckin_timezone,
          AC.name AS task_name
   FROM
     (SELECT AA.event_id,
             AA.person_id,
             DATEADD(SECOND, AA.event_time_zone, AA.event_time) AS event_time,
             AA.shift_start,
             AA.task_id,
             AA.event_time_zone
      FROM eventlog AS AA
      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 @fromparam AND @toparam) AS A
   LEFT OUTER JOIN task AC ON A.task_id= AC.task_id
   GROUP BY ', @workdaygrpqry, ',
            A.task_id,
            shift_start,
            A.event_time_zone,
            person_id,
            AC.name) Q ON P.person_id = Q.person_id
AND P.cloud_date = Q.workday
GROUP BY CONVERT(DATE,cloud_date),
         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,
         P.cloud_date,
         firstcheckin,
         firstcheckin_timezone,
         shift_start,
         task_id,
         task_name
ORDER BY P.first_name,
         P.last_name '
);

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

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

END;

--TestCases
-- EXEC ArrivalReport 773,-1,-1,'2017-05-24', '2018-05-24',0,1000,0