CREATE PROCEDURE EventLogReportDailyCount(
    @customerparam INT,
    @groupparam INT,
    @personparam INT,
    @taskparam INT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @daystartparam INT,
    @shift_start_grouping BIT,
    @persontypeparam VARCHAR(100)
)
AS
BEGIN
    DECLARE @startparam DATETIME, @endparam DATETIME;
    DECLARE @qry1 NVARCHAR(4000),
    @qry2 NVARCHAR(4000),
    @qry3 NVARCHAR(4000),
    @qry4 NVARCHAR(4000),
    @qry5 NVARCHAR(4000),
    @qry6 NVARCHAR(4000),
    @qry7 NVARCHAR(4000),
    @qryPersonType NVARCHAR(4000),
    @qry NVARCHAR(4000);

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

SET @qry1 = ' SELECT   CONVERT(DATE,CD.cloud_date) AS event_day,
          ISNULL(e.event_count, 0 ) AS event_count,
          ISNULL(error_count, 0) AS error_count
FROM      cloud_date_table CD
LEFT JOIN
          (SELECT
		evt.event_date ,
		COUNT(evt.event_id) AS event_count,
		SUM(evt.error_event) AS error_count
	FROM
		(
                     SELECT
                                IIF(@shift_start_grouping = 1,CONVERT(DATE,(DATEADD(SECOND,(- @daystartparam),CO.shift_start))),CONVERT(DATE,(DATEADD(SECOND,(CO.event_time_zone - @daystartparam),CO.event_time)))) AS event_date,
                                CO.event_id,
                                CASE WHEN (CO.event_type < 2 AND CI.event_id IS NULL AND COUT.event_id IS NULL) THEN 1 ELSE 0 END error_event
                     FROM       eventlog CO
                     INNER JOIN person P
                     ON         CO.person_id = P.person_id ';



SET @qry2 = CASE WHEN (@personparam < 0 AND @groupparam > 0) THEN
' INNER JOIN user_group_membership UGM ON P.person_id=UGM.member INNER JOIN usergroup UG ON UGM.assigned_group_id=UG.group_id '
ELSE ' ' END;

SET @qry3 = ' LEFT JOIN task T ON CO.task_id = T.task_id
    LEFT JOIN visitor_appointments VA ON CO.visit_appointment  = VA.id
	LEFT JOIN roster_item VA_RI ON VA.attendant_roster_item_id  = VA_RI.roster_item_id
	LEFT JOIN roster VA_R ON VA_RI.roster_id = VA_R.roster_id
    LEFT JOIN eventlog CI ON CO.event_id = CI.next_event
    LEFT JOIN eventlog COUT ON CO.next_event = COUT.event_id WHERE CO.is_deleted = 0
AND
P.is_deleted = 0
AND
VA_R.deleted_date IS NULL
AND
P.customer_id= @customerParam ';

SET @qry4 = CASE WHEN @personparam > 0 THEN ' AND P.person_id = @personparam ' ELSE ' ' END;

SET @qryPersonType = ' AND ((@persontypeparam = ''EMPLOYEE'' AND P.person_type IS NULL ) OR (P.person_type = @persontypeparam)) ';

SET @qry5 = CASE WHEN (@personparam <= 0 AND @groupparam > 0) THEN ' AND UGM.assigned_group_id= @groupparam AND UGM.end_date IS NULL AND UG.deleted_date IS NULL ' ELSE '  ' END;

SET @qry6 = CASE wHEN @taskparam = 0 THEN ' AND T.task_id IS NULL '
ELSE
CASE WHEN @taskparam > 0 THEN ' AND T.task_id = @taskparam AND T.is_deleted = 0 ' ELSE '  ' END
END;

SET @qry7 = ' AND
IIF( @shift_start_grouping= 1, CO.shift_start, CONVERT(DATE,(DATEADD(SECOND,CO.event_time_zone,CO.event_time))))
BETWEEN @startparam
AND
@endparam   )
AS evt
	GROUP BY
		evt.event_date) e ON CD.cloud_date = e.event_date WHERE CD.cloud_date BETWEEN @fromparam
AND @toparam ORDER BY CD.cloud_date ';

SET @qry = CONCAT(@qry1, @qry2, @qry3, @qry4, @qryPersonType, @qry5, @qry6, @qry7);




    DECLARE @statement NVARCHAR(4000);
    SET @statement = @qry;
    DECLARE @parameterDefinition NVARCHAR(4000);
    SET @parameterDefinition = N'@customerparam INT, @groupparam INT, @personparam INT,  @taskparam INT, @fromparam DATETIME, @toparam DATETIME,@daystartparam INT,@shift_start_grouping BIT, @startparam DATETIME, @endparam DATETIME, @persontypeparam VARCHAR(100)';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@groupparam=@groupparam,@personparam=@personparam,@taskparam=@taskparam,
    @fromparam=@fromparam,@toparam=@toparam,@daystartparam=@daystartparam,@shift_start_grouping=@shift_start_grouping,@startparam=@startparam,@endparam=@endparam, @persontypeparam=@persontypeparam;

END;

--TestCases
-- exec EventLogReportDailyCount 773, -1,-1, -1,'2018-12-04', '2018-12-04', 180 , FALSE