CREATE PROCEDURE WorkHourReport(
    @customerparam INT,
    @groupparam varchar(200),
    @personparam INT,
    @taskparam INT,
    @periodparam INT,
    @daystartparam INT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @offsetparam INT,
    @countparam INT,
    @personlimitparam BIT,
    @minabsentlateparam INT,
    @maxabsentlateparam INT,
    @deviceinfoparam BIT
)
    AS
BEGIN
    DECLARE @startparam DATETIME, @endparam DATETIME;
    DECLARE @periodstartCLOUD NVARCHAR(MAX), @periodstartWORKHOUR NVARCHAR(MAX), @periodendQR NVARCHAR(MAX) ;
    DECLARE  @taskfilter NVARCHAR(MAX), @psnlimit NVARCHAR(MAX), @datelimit NVARCHAR(MAX), @PERSON_FILTER NVARCHAR(MAX), @GROUP_FILTER NVARCHAR(MAX), @workquery NVARCHAR(MAX);

    SET @toparam = DATEADD(SECOND,86399,@toparam);
    SET @startparam = DATEADD(SECOND,@daystartparam,@fromparam);
    SET @endparam = DATEADD(SECOND,(86399 + @daystartparam),@toparam);

    IF @periodparam = 1
BEGIN
	        SET @periodstartCLOUD = 'CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam, cloud_date) / 7)* 7,@fromparam)) AS period_start ';
	        SET @periodstartWORKHOUR = 'CONVERT(DATE,(DATEADD(DAY,( DATEDIFF(DAY, @fromparam, CONVERT(DATE, WORK_HOUR_DAILY.work_day )) / 7)* 7 , @fromparam ))) AS period_start,';
	        SET @periodendQR = 'DATEADD(SECOND, -1, DATEADD(DAY, 7, CONVERT(DATETIME, P.period_start))) AS period_end, ';
END
ELSE IF @periodparam = 2
BEGIN
            SET @periodstartCLOUD = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) AS period_start ';
	        SET @periodstartWORKHOUR = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,WORK_HOUR_DAILY.work_day)),@fromparam))  AS period_start,';
	        SET @periodendQR = 'DATEADD(SECOND, -1, DATEADD(MONTH, 1, CONVERT(DATETIME, P.period_start)))  AS period_end, ';
END
ELSE IF @periodparam = 3
BEGIN
      	    SET @periodstartCLOUD = ' CONVERT(DATE, @fromparam) AS period_start ';
	        SET @periodstartWORKHOUR = ' CONVERT(DATE, @fromparam) AS period_start, ';
	        SET @periodendQR = 'DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY,@fromparam,@toparam) + 1, CONVERT(DATETIME, P.period_start))) AS period_end, ';
END
ELSE
BEGIN
      	    SET @periodstartCLOUD = ' CONVERT(DATE,cloud_date) AS period_start ';
	        SET @periodstartWORKHOUR = ' CONVERT(DATE,WORK_HOUR_DAILY.work_day) AS period_start,';
	        SET @periodendQR = 'DATEADD(SECOND, -1, DATEADD(DAY, 1, CONVERT(DATETIME, P.period_start))) AS period_end, ';
END;

    IF @taskparam < 0
		SET @taskfilter = ' ';
ELSE IF @taskparam = 0
		SET @taskfilter = ' AND CIN.task_id IS NULL ';
ELSE
		SET @taskfilter = ' AND CIN.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 cloud_date OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY ';
END;




	IF (@personparam > 0)
BEGIN
        SET @PERSON_FILTER = ' P.person_id = @personparam ';
END
ELSE
BEGIN
        SET @PERSON_FILTER = ' P.person_id >0 ';
END;

   	IF (@groupparam = '-1' )
BEGIN
        SET @GROUP_FILTER = '  P.person_id >0  ';
END
ELSE
BEGIN
        SET @GROUP_FILTER = CONCAT(' P.person_id IN (SELECT UGM.member FROM user_group_membership UGM WHERE  UGM.assigned_group_id IN (', @groupparam , ') AND UGM.end_date IS NULL )');
END;

    SET @workquery = 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,
	d.description AS device_description,
	d.assigned_user_id AS device_user,
	d.assigned_group_id AS device_group,
	CD.data_json AS customdata,
	WORK_HOUR.*
FROM
	(
	SELECT
		P.person_id,
		CONVERT(DATETIME,
		P.period_start) AS period_start,',
		@periodendQR,
		'WORK_HOUR_DAILY.device_id,
		SUM(WORK_HOUR_DAILY.shift_hours) AS shift_hours,
		SUM(WORK_HOUR_DAILY.shift_work_hours) AS shift_work_hours,
		SUM(WORK_HOUR_DAILY.work_hours) AS work_hours,
		SUM(WORK_HOUR_DAILY.in_out_time) AS in_out_time,
		SUM(WORK_HOUR_DAILY.productive_hours) AS productive_hours,
		(CASE WHEN @deviceinfoparam = 0 THEN  SUM(WORK_HOUR_DAILY.productive_work_hours) ELSE NULL END) AS productive_work_hours,
		(CASE WHEN @deviceinfoparam = 0 THEN  SUM(WORK_HOUR_DAILY.over_time) ELSE NULL END) AS over_time,
		SUM(WORK_HOUR_DAILY.early_arrival) AS early_arrival,
		SUM(WORK_HOUR_DAILY.late_arrival) AS late_arrival,
		SUM(WORK_HOUR_DAILY.early_depature) AS early_depature,
		SUM(WORK_HOUR_DAILY.late_depature) AS late_depature,
		MIN(WORK_HOUR_DAILY.first_checkin) AS first_checkin,
		MAX(WORK_HOUR_DAILY.last_checkout) AS last_checkout,
		SUM(WORK_HOUR_DAILY.min_absent_late_count) AS min_absent_late_count,
		SUM(WORK_HOUR_DAILY.max_absent_late_count) AS max_absent_late_count,
		P.holiday AS holiday,
		P.leave_and_mission AS leave_and_mission,
		SUM(WORK_HOUR_DAILY.event_count) AS total_attendance_count,
		SUM(CASE WHEN (WORK_HOUR_DAILY.holiday != 1 AND WORK_HOUR_DAILY.leave_and_mission != 1 ) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS total_valid_attendance_count,
		SUM(CASE WHEN (WORK_HOUR_DAILY.holiday = 1 ) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS attendance_on_holiday,
		SUM(CASE WHEN (WORK_HOUR_DAILY.leave_and_mission = 1 AND WORK_HOUR_DAILY.holiday = 0) THEN COALESCE(WORK_HOUR_DAILY.event_count, 0) ELSE 0 END) AS attendance_on_leave,
                        SUM(WORK_HOUR_DAILY.break_time) AS break_time
	FROM
		(
		SELECT
			P.person_id,
			P.period_start,
			COUNT(H.date) AS holiday,
			COUNT(LAM.person_id) AS leave_and_mission
		FROM
			(
			SELECT
				P.person_id,
				CDT.cloud_date,',
				@periodstartCLOUD,
			'FROM
				(
				SELECT
					P.person_id
				FROM
					person P WHERE P.customer_id = @customerparam
                                                  AND P.is_deleted = 0
											      AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
                                                  AND ',
					 @PERSON_FILTER,
					 ' AND ', @GROUP_FILTER,
					@psnlimit,

           ') AS P
			CROSS JOIN (SELECT * FROM cloud_date_table CDT WHERE CDT.cloud_date BETWEEN @fromparam AND @toparam ', @datelimit ,') AS CDT) AS P
		LEFT JOIN (
			SELECT
				H.date
			FROM
				holiday H
			WHERE
				H.customer_id = @customerparam
				AND H.is_deleted = 0
				AND H.date BETWEEN @fromparam AND @toparam) AS H
                                     ON
			P.cloud_date = H.date
		LEFT JOIN (
			SELECT
				person_id,
				start_date,
				end_date
			FROM
				leave_and_mission LAM
			WHERE
				LAM.customer_id = @customerparam
				AND NOT (LAM.end_date < @fromparam
					OR LAM.start_date > @toparam)
				AND LAM.is_deleted = 0
				AND LAM.approval_status = ''APPROVED'') AS LAM
                                     ON
			P.person_id = LAM.person_id
			AND P.cloud_date BETWEEN LAM.start_date AND LAM.end_date
		GROUP BY
			P.person_id,
			P.period_start

) AS P
	LEFT JOIN

		(
		SELECT
			WORK_HOUR_DAILY.*,
			(CASE
				WHEN H.date IS NULL THEN 0
				ELSE 1
			END) AS holiday,
			(CASE
				WHEN LAM.person_id IS NULL THEN 0
				ELSE 1
			END) AS leave_and_mission
		FROM
			(
			SELECT
				WORK_HOUR_DAILY.work_day,',
				@periodstartWORKHOUR,
				'WORK_HOUR_DAILY.person_id,
				WORK_HOUR_DAILY.device_id,
				1 AS event_count,
				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(over_time) 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,
				SUM(IIF(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, shift_start, firstcheckin), 0)>@minabsentlateparam, 1, 0)) as min_absent_late_count,
				SUM(IIF(dbo.INTEGER_GREATEST_2PARAM(DATEDIFF(SECOND, shift_start, firstcheckin), 0)>@maxabsentlateparam, 1, 0)) as max_absent_late_count,
				SUM(WORK_HOUR_DAILY.break_time) AS break_time
			FROM
				(
				SELECT
					WORK_HOUR_DAILY.*,
					dbo.INTEGER_LEAST_2PARAM(WORK_HOUR_DAILY.maxot,
					(WORK_HOUR_DAILY.ot_a + WORK_HOUR_DAILY.ot_b +
                                             dbo.INTEGER_LEAST_2PARAM(dbo.INTEGER_GREATEST_2PARAM(0,
					WORK_HOUR_DAILY.productivehours - WORK_HOUR_DAILY.shift_work_hours),
					WORK_HOUR_DAILY.ot_c))) AS over_time
				FROM
					(
					SELECT
						EVENT_CORE.work_day,
						EVENT_CORE.person_id,
						EVENT_CORE.device_id,
						EVENT_CORE.task_id,
						EVENT_CORE.shift_work_hours,
						EVENT_CORE.maxot,
						EVENT_CORE.shift_start,
						EVENT_CORE.shift_end,
                        SUM(EVENT_CORE.break_time) AS break_time,
						SUM(DATEDIFF(SECOND, EVENT_CORE.cin_event_time , EVENT_CORE.cout_event_time)) AS workhours,
						SUM(CASE
				WHEN (EVENT_CORE.cin_event_time > EVENT_CORE.shift_end OR EVENT_CORE.cout_event_time < EVENT_CORE.shift_start) then 0
                ELSE DATEDIFF(SECOND, (CASE
                                            WHEN EVENT_CORE.cin_event_time> EVENT_CORE.shift_start THEN CASE
                                                                                      WHEN EVENT_CORE.cin_event_time > EVENT_CORE.shift_end THEN EVENT_CORE.shift_end
                                                                                      ELSE EVENT_CORE.cin_event_time
                                                                                  END
                                            ELSE EVENT_CORE.shift_start
                                        END),
                                        (CASE
                                       WHEN EVENT_CORE.cout_event_time < EVENT_CORE.shift_end THEN CASE
                                                                                WHEN EVENT_CORE.cout_event_time < EVENT_CORE.shift_start THEN EVENT_CORE.shift_start
                                                                                ELSE EVENT_CORE.cout_event_time
                                                                            END
                                       ELSE EVENT_CORE.shift_end
                                   END))
			END) as productivehours,
						MIN(EVENT_CORE.cin_event_time) as firstcheckin,
						MAX(EVENT_CORE.cout_event_time) as lastcheckout,
						SUM(CASE
              WHEN (EVENT_CORE.cin_event_time > EVENT_CORE.shift_start) THEN 0
			        ELSE CASE
			          WHEN (EVENT_CORE.ot_start IS NULL) THEN 0
				        ELSE dbo.INTEGER_GREATEST_2PARAM(0, DATEDIFF(SECOND, dbo.DATETIME_GREATEST_2PARAM(EVENT_CORE.ot_start, EVENT_CORE.cin_event_time), dbo.DATETIME_LEAST_3PARAM(EVENT_CORE.ot_end, EVENT_CORE.shift_start, EVENT_CORE.cout_event_time)))
				        END
			      END) AS ot_a,
						SUM(CASE
              WHEN (EVENT_CORE.cout_event_time < EVENT_CORE.shift_end) THEN 0
			        ELSE CASE
			        WHEN (EVENT_CORE.ot_start IS NULL) THEN 0
				      ELSE dbo.INTEGER_GREATEST_2PARAM(0, DATEDIFF(SECOND, dbo.DATETIME_GREATEST_3PARAM(EVENT_CORE.ot_start, EVENT_CORE.shift_end, EVENT_CORE.cin_event_time), dbo.DATETIME_LEAST_2PARAM(EVENT_CORE.ot_end, EVENT_CORE.cout_event_time)))
				      END
			      END) AS ot_b,
						SUM(CASE WHEN (EVENT_CORE.shift_start >= EVENT_CORE.cout_event_time OR EVENT_CORE.shift_end <= EVENT_CORE.cin_event_time) THEN 0
        WHEN (EVENT_CORE.ot_start IS NULL) THEN DATEDIFF(SECOND, dbo.DATETIME_GREATEST_2PARAM(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time), dbo.DATETIME_LEAST_2PARAM(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time))
			  ELSE
				dbo.INTEGER_GREATEST_2PARAM(0, DATEDIFF(SECOND, dbo.DATETIME_GREATEST_3PARAM(EVENT_CORE.shift_start, EVENT_CORE.cin_event_time, EVENT_CORE.ot_start), dbo.DATETIME_LEAST_3PARAM(EVENT_CORE.shift_end, EVENT_CORE.cout_event_time, EVENT_CORE.ot_end)))
			  END) AS ot_c
					FROM
						(
						SELECT
							CIN.person_id,
							CONVERT(DATE,
							(CASE
								WHEN CONVERT(TIME,
								CIN.shift_start)>= CONVERT(TIME,
								DATEADD(SECOND,@daystartparam, 0)) THEN CIN.shift_start
								ELSE DATEADD(DAY, -1, CIN.shift_start)
							END)) AS work_day,
							CASE
								WHEN @deviceinfoparam = 0
									OR CIN.device_id IS NULL THEN 0
									ELSE CIN.device_id
								END AS device_id,
								CASE
									WHEN CIN.task_id IS NULL THEN 0
									ELSE CIN.task_id
								END AS task_id,
								DATEADD(SECOND , CIN.event_time_zone, CIN.event_time) AS cin_event_time,
								DATEADD(SECOND , CIN.event_time_zone, COUT.event_time) AS cout_event_time,
								CIN.shift_start,
								DATEADD(SECOND, CIN.shift_span_seconds, CIN.shift_start) AS shift_end,
								CIN.work_hours AS shift_work_hours,
								ISNULL(COUT.total_breaktime, 0) AS break_time,
								(CASE
									WHEN
                    CIN.maxotstatus IS NULL
										OR CIN.maxotstatus != ''REJECTED''
                THEN (CASE
											WHEN (CIN.is_limitot = 0
												OR CIN.maxot < 0
												OR CIN.maxot IS NULL) THEN 86400
											ELSE CIN.maxot
										END)
										ELSE 0
									END) AS maxot,
								CIN.ot_start,
								DATEADD(SECOND, CIN.ot_span_seconds, CIN.ot_start) AS ot_end
							FROM
								(
								SELECT
									CIN.*
								FROM
									eventlog CIN
								INNER JOIN person P ON
									CIN.person_id = P.person_id
									WHERE P.customer_id = @customerparam
											AND P.is_deleted = 0
											AND (P.person_type IS NULL OR P.person_type = ''EMPLOYEE'')
											AND CIN.event_type = 0
									AND CIN.is_deleted = 0
									AND CIN.shift_start BETWEEN @startparam AND @endparam AND
									',@PERSON_FILTER,
									@taskfilter,
		') AS CIN
							INNER JOIN
	 eventlog COUT ON
								CIN.next_event = COUT.event_id
	) AS EVENT_CORE
					GROUP BY
						EVENT_CORE.work_day,
						EVENT_CORE.person_id,
						EVENT_CORE.device_id,
						EVENT_CORE.task_id,
						EVENT_CORE.shift_work_hours,
						EVENT_CORE.maxot,
						EVENT_CORE.shift_start,
						EVENT_CORE.shift_end) AS WORK_HOUR_DAILY ) AS WORK_HOUR_DAILY
			GROUP BY
				WORK_HOUR_DAILY.work_day,
				WORK_HOUR_DAILY.person_id,
				WORK_HOUR_DAILY.device_id) AS WORK_HOUR_DAILY
		LEFT JOIN (
			SELECT
				H.date
			FROM
				holiday H
			WHERE
				H.customer_id = @customerparam
				AND H.is_deleted = 0
				AND H.date BETWEEN @fromparam AND @toparam) AS H
                                     ON
			WORK_HOUR_DAILY.work_day = H.date
		LEFT JOIN (
			SELECT
				person_id,
				start_date,
				end_date
			FROM
				leave_and_mission LAM
			WHERE
				LAM.customer_id = @customerparam
				AND NOT (LAM.end_date < @fromparam
					OR LAM.start_date > @toparam)
				AND LAM.is_deleted = 0
				AND LAM.approval_status = ''APPROVED'') AS LAM
                                     ON
			WORK_HOUR_DAILY.person_id = LAM.person_id
			AND WORK_HOUR_DAILY.work_day BETWEEN LAM.start_date AND LAM.end_date



		) AS WORK_HOUR_DAILY

		ON
		P.person_id = WORK_HOUR_DAILY.person_id
		AND
		P.period_start = WORK_HOUR_DAILY.period_start
	GROUP BY
		P.person_id,
		P.period_start,
		P.leave_and_mission,
		P.holiday,
		WORK_HOUR_DAILY.device_id




		) AS WORK_HOUR
LEFT JOIN person P ON
	WORK_HOUR.person_id = P.person_id
LEFT JOIN customdata CD ON
	P.custom_data_id = CD.custom_data_id
LEFT JOIN device D ON
	WORK_HOUR.device_id = D.device_id








');

    DECLARE @statement NVARCHAR(MAX);
    SET @statement = @workquery;
    DECLARE @parameterDefinition NVARCHAR(MAX);

    SET @parameterDefinition = N'@customerparam INT, @groupparam varchar(200), @personparam INT,  @taskparam INT, @periodparam INT, @daystartparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT,
@countparam INT, @personlimitparam BIT, @startparam DATETIME, @endparam DATETIME, @minabsentlateparam INT, @maxabsentlateparam INT, @deviceinfoparam BIT';
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,
@minabsentlateparam=@minabsentlateparam,@maxabsentlateparam=@maxabsentlateparam, @deviceinfoparam=@deviceinfoparam;

END;