CREATE PROCEDURE AbsenteeReportCount(IN customerparam INT, IN groupparam INT, IN personparam INT, IN fromparam DATETIME, IN toparam DATETIME, IN daystartparam INT)

BEGIN

set @daystartparam = SEC_TO_TIME(daystartparam-1);
set @fromparam= fromparam;
set @toparam= DATE_ADD(toparam, interval 86399 SECOND);

set @customerparam= customerparam;
set @groupparam = groupparam;


SET @qry1 = "SELECT 
        COUNT(PC.cloud_date)
    FROM
        person PA  ";

set @psnqry = case when personparam < 0 then
	" "
else
	" AND PA.person_id = @personparam "
end;


set @grpqry = case when groupparam < 0 then  
	" " 
else 
	" INNER JOIN user_group_membership UGM ON PA.person_id = UGM.member AND UGM.end_date IS NULL AND UGM.assigned_group_id = @groupparam  " 
end; 

SET @qry2 = "  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 PC.cloud_date BETWEEN @fromparam AND @toparam ";


SET @qry=concat(@qry1, @grpqry, @qry2, @psnqry);

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