CREATE PROCEDURE WorkHourReportCount(
    @customerparam INT,
    @groupparam varchar(200),
    @personparam INT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @periodparam INT)
AS
BEGIN
    DECLARE @daydifference INT;
    DECLARE @periodqry NVARCHAR(MAX),@periodgrpqry NVARCHAR(MAX),  @psnqry NVARCHAR(MAX), @qry NVARCHAR(MAX);

    SET @toparam = DATEADD(SECOND,86399,@toparam);
    SET @daydifference = DATEDIFF(DAY,@fromparam,@toparam) + 1;

    IF @periodparam = 1
    BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)) ';
        SET @periodqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / 7)*7,@fromparam)) AS period ';
    END
ELSE IF @periodparam = 2
	BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) ';
        SET @periodqry = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) as period ';
    END
ELSE IF @periodparam = 3
	BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / @daydifference)*@daydifference,@fromparam)) ';
        SET @periodqry = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam,cloud_date) / @daydifference)*@daydifference,@fromparam)) AS period ';
    END
ELSE
	BEGIN
        SET @periodgrpqry = ' CONVERT(DATE,cloud_date) ';
        SET @periodqry = ' CONVERT(DATE,cloud_date) AS period ';
    END
;

    IF(@personparam > 0)
	SET @psnqry = CONCAT(' SELECT PA.person_id, ' , @periodqry,
	' FROM
		person PA
			CROSS JOIN
		cloud_date_table PC
	WHERE
		PA.customer_id = @customerparam
			AND PA.is_deleted = 0
			AND PA.person_id = @personparam
			AND PC.cloud_date BETWEEN @fromparam AND @toparam GROUP BY PA.person_id, ',  @periodgrpqry);

ELSE IF (CHARINDEX('-1',@groupparam) = 0 )
SET @psnqry =  CONCAT('SELECT PA.person_id, ' , @periodqry,
	' FROM
		person PA
			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
		PA.customer_id = @customerparam
			AND PA.is_deleted = 0
			AND (PA.person_type IS NULL OR PA.person_type = ''EMPLOYEE'')
			AND PB.assigned_group_id in ( ' , @groupparam , ' )
			AND PB.end_date IS NULL
			AND PC.cloud_date BETWEEN @fromparam AND @toparam  GROUP BY PA.person_id, ', @periodgrpqry);

ELSE
SET @psnqry =  CONCAT(' SELECT PA.person_id, ' , @periodqry,
	' FROM
		person PA
			CROSS JOIN
		cloud_date_table PC
	WHERE
		PA.customer_id = @customerparam
			AND PA.is_deleted = 0
			AND PC.cloud_date BETWEEN @fromparam AND @toparam GROUP BY PA.person_id, ', @periodgrpqry);
;

    SET @qry = CONCAT(' SELECT COUNT(*) FROM (',@psnqry , ' ) AS P ');

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


    SET @parameterDefinition = N'@customerparam INT, @groupparam varchar(200), @personparam INT,  @fromparam DATETIME, @toparam DATETIME, @periodparam INT, @daydifference INT';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@groupparam=@groupparam,@personparam=@personparam,@periodparam=@periodparam,
    @fromparam=@fromparam,@toparam=@toparam,@daydifference=@daydifference;

END;

--TestCases
-- EXEC WorkHourReportCount 773,-1,-1,'2019-01-01', '2019-12-01', 1