CREATE PROCEDURE DepatureReportPerson(
    @person_param INT,
    @task_param INT,
    @from_param DATETIME,
    @to_param DATETIME,
    @day_start_param INT)
AS
BEGIN
    DECLARE @personparam INT, @taskparam INT, @fromparam DATETIME, @toparam DATETIME, @daystartparam TIME;
    DECLARE @workdaygrpqry NVARCHAR(MAX), @taskfilter NVARCHAR(MAX),  @selectqry 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 @personparam = @person_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,
       shift_end,
       lastcheckout,
       lastcheckout_timezone,
       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
FROM ';

    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 PA.person_id = @personparam AND PA.is_deleted = 0 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.shift_start AS shift_start,
          A.shift_end as shift_end,
          MAX(A.event_time) AS lastcheckout,
          A.event_time_zone AS lastcheckout_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,
             DATEADD(SECOND,AA.shift_span_seconds,AA.shift_start) AS shift_end,
             AA.task_id,
             AA.event_time_zone
      FROM eventlog AS AA
      WHERE AA.person_id = @personparam ',
      @taskfilter,
      '
      AND AA.event_type = 1
      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,
            shift_end,
            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,
         lastcheckout,
         lastcheckout_timezone,
         shift_start,
         shift_end,
         task_id,
         task_name '
);

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

    SET @parameterDefinition = N'@personparam INT, @taskparam INT, @daystartparam TIME, @fromparam DATETIME, @toparam DATETIME';
    EXEC sp_executesql @statement,@parameterDefinition,@personparam=@personparam,@taskparam=@taskparam,
@fromparam=@fromparam,@toparam=@toparam,@daystartparam=@daystartparam;
select @qry;
END;

--TestCases
--EXEC DepatureReportPerson 1,-1,'2018-05-20', '2018-05-25', 28801