CREATE PROCEDURE EventLogReportCount(
    @customerparam INT,
    @groupparam INT,
    @personparam INT,
    @taskparam INT,
    @errorparam BIT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @daystartparam INT,
    @shift_start_grouping BIT,
    @rosterparam INT,
    @appointmentparam INT,
    @eventtypesparam VARCHAR(100),
    @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),
    @qry8 NVARCHAR(4000),
    @qry9 NVARCHAR(4000),
    @qryAppointment NVARCHAR(4000),
    @qry10 NVARCHAR(4000),
    @qryPersonType NVARCHAR(4000),
    @qry NVARCHAR(4000);

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

SET @qry1 = 'SELECT count(*)
 FROM
  (SELECT CO.event_id,
          CO.event_time_zone,
          CO.event_time AS event_time,
          CO.shift_start,
          CO.event_type,
            IIF(CO.event_type=1, NULL, CO.next_event) next_event,
            IIF(CO.event_type=0, NULL, CI.event_id) previous_event,
   		CO.latitude,
           CO.longitude,
           CO.address,
           CO.description,
           CO.event_origin,
           CDE.data_json AS event_customdata ,
           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,
           CDU.data_json AS customdata,
           CO.task_id,
           T.name AS task_name,
           CO.device_mode ,
           DG.group_id AS device_group_id,
           DG.group_code AS device_group_code,
           DG.description AS device_group_description,
           IIF((DG.group_id IS NOT NULL AND DG.deleted_date IS NOT NULL),1,0) device_group_deleted ,
           RG.group_id AS restriction_group_id,
           RG.group_code AS restriction_group_code,
           RG.description AS restriction_group_description,
           IIF((RG.group_id IS NOT NULL AND RG.deleted_date IS NOT NULL),1,0) restriction_group_deleted ,
            IIF(CO.event_type=1, CI.event_id, CO.event_id) CheckinIndex,
                IIF(CO.event_type=0, CO.next_event, CO.event_id) CheckoutIndex   FROM eventlog CO
   INNER JOIN person P ON CO.person_id=P.person_id
   LEFT JOIN customdata CDU ON P.custom_data_id = CDU.custom_data_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 @qry9 = CASE WHEN (@rosterparam > 0) THEN ' AND VA_RI.roster_id = @rosterparam ' ELSE ' ' END;
SET @qryAppointment = CASE WHEN (@appointmentparam > 0) THEN ' AND VA.id = @appointmentparam 'ELSE ' ' END;
SET @qry10 = CONCAT(' AND CO.event_type in (', @eventtypesparam, ') ');

SET @qry3 = CONCAT('
     LEFT JOIN task T ON CO.task_id=T.task_id
    LEFT JOIN usergroup DG ON CO.device_group_id = DG.group_id
    LEFT JOIN usergroup RG ON CO.restriction_group_id = RG.group_id
    LEFT JOIN customdata CDE ON CO.custom_data_id = CDE.custom_data_id
    LEFT JOIN eventlog CI ON CO.event_id=CI.next_event
    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
    WHERE CO.is_deleted=0 ', @qry9, @qryAppointment, @qry10,
' AND
P.is_deleted =0
AND
VA_R.deleted_date IS NULL
AND
P.customer_id= @customerparam
        AND
 CO.event_time
  BETWEEN DATEADD(DAY, -2, @startparam)
AND
 DATEADD(DAY, 2, @endparam)

AND
IIF((@shift_start_grouping=1),CO.shift_start, DATEADD(SECOND,CO.event_time_zone,CO.event_time)) BETWEEN @startparam
AND
@endparam ');

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 = ' ) A ';

SET @qry8 = CASE WHEN (@errorparam=1) THEN ' WHERE CheckinIndex IS NULL OR CheckoutIndex IS NULL ' ELSE ' ' END;

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

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

END;

--TestCases
-- exec EventLogReportCount 773, -1,-1, -1, false,'2018-12-04', '2018-12-04', 0, FALSE, -1, '1,2,3'