CREATE PROCEDURE WorkHourReportCount(IN customerparam INT, IN groupparam TEXT, IN personparam INT,  IN fromparam DATETIME, IN toparam DATETIME, IN periodparam INT)

BEGIN
SET @fromparam= fromparam;
SET @toparam= DATE_ADD(toparam, INTERVAL 86399 SECOND);

SET @customerparam= customerparam;
SET @groupparam = groupparam;
SET @personparam = personparam;
SET @periodparam = periodparam;
SET @daydifference = DATEDIFF( @toparam, @fromparam) + 1;

CASE
WHEN @periodparam = 2 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,cloud_date) MONTH )) as period  ";
WHEN @periodparam = 1 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV 7)*7 DAY)) AS period ";
WHEN @periodparam = 3 THEN
	SET @periodqry = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV @daydifference)*@daydifference DAY)) AS period ";
ELSE
	SET @periodqry = " DATE(cloud_date) AS period ";
END CASE;

CASE
WHEN @personparam >0 THEN
	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 = FALSE
			AND PA.person_id = @personparam
			AND PC.cloud_date BETWEEN @fromparam AND @toparam GROUP BY PA.person_id, period");
WHEN @groupparam >0 THEN
	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 = FALSE
			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, period");
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 = FALSE
			AND PC.cloud_date BETWEEN @fromparam AND @toparam GROUP BY PA.person_id, period");
END CASE;

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

PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE  PREPARE stmt;
END