USE [VIGIL-Audit]
GO


-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_set_details]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_set_details]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_set_criteria]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_set_criteria]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_reports]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_reports]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_reports_ext]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_reports_ext]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_shrink_db]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_shrink_db]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_reindex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_reindex]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_get_db_indexes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_get_db_indexes]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_sync_audit_users]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_sync_audit_users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_table]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_table]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_table_ex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_table_ex]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_table_login_failed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_table_login_failed]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_invalidate_daily_performance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_invalidate_daily_performance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_vpos_activity]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_vpos_activity]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_invalid_records]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_invalid_records]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_update_db_stats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_update_db_stats]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_user_performance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_user_performance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_daily_user_performance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_daily_user_performance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_reports_list]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_reports_list]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_reports_combolist]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_reports_combolist]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_report_criterias]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_report_criterias]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_report_criterias_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_report_criterias_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_report_criterias_all]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_report_criterias_all]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_does_user_or_group_have_criteria]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_does_user_or_group_have_criteria]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_summary_all]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_summary_all]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_summary_all_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_summary_all_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_sum_performance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_sum_performance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_time_logged_in]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_time_logged_in]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_logged_in_failed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_logged_in_failed]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_footage_viewed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_footage_viewed]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_searches_done]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_searches_done]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_pos_query]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_pos_query]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_time_logged_in_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_time_logged_in_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_logged_in_failed_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_logged_in_failed_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_idle_time_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_idle_time_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_idle_time_sum_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_idle_time_sum_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_footage_viewed_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_footage_viewed_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_searches_done_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_searches_done_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_pos_query_by_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_pos_query_by_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_idle_time_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_idle_time_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_time_logged_in_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_time_logged_in_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_active_time_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_active_time_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_footage_viewed_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_footage_viewed_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_frame_viewed_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_frame_viewed_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_searches_done_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_searches_done_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_pos_query_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_pos_query_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_vpos_events_flagged_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_vpos_events_flagged_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_vpos_events_flagged_percent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_vpos_events_flagged_percent]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_audit_get_shift_analysis_tags_viewed_sum]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_audit_get_shift_analysis_tags_viewed_sum]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_check_tables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_check_tables]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_invalidate_daily_performance]
@user as nvarchar(50),
@day as datetime
AS
BEGIN
	DELETE FROM audit_daily_performance_cache WHERE username = @user AND [day] = convert(varchar, @day, 10)

	UPDATE audit_daily_user_summary SET valid = 0 WHERE [user] = @user and [date] = convert(varchar, @day, 10)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_table]
@table nvarchar(30),
@maxcount INT
AS
BEGIN
	DECLARE @max BIGINT
	DECLARE @min BIGINT
	IF @table = 'audit_details'
	BEGIN
		SELECT @min = MIN(idx), @max = MAX(idx) FROM [audit_details]
		IF @max - @min >= @maxcount + 1000
		BEGIN
			DELETE FROM audit_details_info WHERE audit_details_id <= @max - @maxcount
			DELETE FROM audit_details WHERE idx <= @max - @maxcount
		END
	END
	IF @table = 'audit_daily_performance_cache'
	BEGIN
		DELETE FROM audit_daily_performance_cache WHERE [day] < convert(varchar(10), dateadd(day, -30, getdate()), 20)
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_table_ex]
@table nvarchar(30),
@maxcount INT
AS
BEGIN
	DECLARE @count INT
	DECLARE @event_id smallint

	IF @table = 'audit_details'
	BEGIN
		SELECT @event_id = [idx]
		FROM audit_events (NOLOCK)
		WHERE [name] = 'login_failed'

		SELECT @count = COUNT(idx) FROM [audit_details] WHERE [event_id] != @event_id
		IF @count >= @maxcount + 1000
		BEGIN
			SELECT TOP(@count - @maxcount) [idx] INTO #tmp_prune_idx_table FROM [audit_details] WHERE [event_id] != @event_id ORDER BY idx

			DELETE FROM audit_details_info WHERE audit_details_id IN (SELECT idx FROM #tmp_prune_idx_table)
			DELETE FROM audit_details WHERE idx IN (SELECT idx FROM #tmp_prune_idx_table)
		END
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_table_login_failed]
@maxmonth INT,
@maxcount INT
AS
BEGIN
	DECLARE @count INT
	DECLARE @event_id smallint

	SELECT @event_id = [idx]
	FROM audit_events (NOLOCK)
	WHERE [name] = 'login_failed'

	SELECT [idx] INTO #tmp_prune_idx_table_month FROM [audit_details] WHERE [event_id] = @event_id AND [event_time] < DATEADD(month, @maxmonth*(-1), GETDATE())

	DELETE FROM audit_details_info WHERE audit_details_id IN (SELECT idx FROM #tmp_prune_idx_table_month)
	DELETE FROM audit_details WHERE idx IN (SELECT idx FROM #tmp_prune_idx_table_month)

	SELECT @count = COUNT(idx) FROM [audit_details] WHERE [event_id] = @event_id
	IF @count > @maxcount
	BEGIN
		SELECT TOP(@count - @maxcount) [idx] INTO #tmp_prune_idx_table_count FROM [audit_details] WHERE [event_id] = @event_id ORDER BY idx

		DELETE FROM audit_details_info WHERE audit_details_id IN (SELECT idx FROM #tmp_prune_idx_table_count)
		DELETE FROM audit_details WHERE idx IN (SELECT idx FROM #tmp_prune_idx_table_count)
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_sync_audit_users]
AS
BEGIN
	DECLARE @vigil_user nvarchar(50)
	DECLARE @audit_user nvarchar(50)

	DECLARE users_cursor CURSOR FOR SELECT account_username FROM [VIGIL-DVR].[dbo].[users]
	OPEN users_cursor

	FETCH NEXT FROM users_cursor INTO @vigil_user
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @audit_user = NULL
		SELECT @audit_user = [name] FROM audit_users WHERE [name] = @vigil_user

		IF @audit_user IS NULL
			INSERT INTO audit_users ([name], active) VALUES (@vigil_user, 1)
		ELSE
			UPDATE audit_users SET [name] = @vigil_user WHERE [name] = @vigil_user

		FETCH NEXT FROM users_cursor INTO @vigil_user
	END

	CLOSE users_cursor
	DEALLOCATE users_cursor
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_get_db_indexes]
AS
BEGIN
	SET NOCOUNT ON

	create table #results (
		[table] varchar(100),
		[index] varchar(100)
	)

	DECLARE @database varchar(100)
	DECLARE @table varchar(100)
	DECLARE @index varchar(100)

	SELECT @database = DB_NAME()

	DECLARE tableCursor CURSOR FOR
		SELECT name FROM sys.objects
		WHERE [type] = 'U'

	OPEN tableCursor

	FETCH NEXT FROM tableCursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE indexCursor CURSOR FOR
			SELECT name FROM sys.indexes
			WHERE [object_id] = object_id(@table)
			AND (name LIKE 'ix_%' OR name LIKE 'pk_%')

		OPEN indexCursor

		FETCH NEXT FROM indexCursor INTO @index

		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO #results([table], [index]) VALUES(@table, @index)

			FETCH NEXT FROM indexCursor INTO @index
		END

		CLOSE indexCursor
		DEALLOCATE indexCursor

		FETCH NEXT FROM tableCursor INTO @table
	END

	CLOSE tableCursor
	DEALLOCATE tableCursor

	SELECT * FROM #results
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_reindex]
AS
BEGIN
	DECLARE @TableName sysname
	DECLARE @sql nvarchar(1000)
	DECLARE #Table_Name CURSOR FOR SELECT name FROM sys.objects WHERE [type] = 'u' AND OBJECTPROPERTY ([object_id], 'ismsshipped') = 0 AND OBJECTPROPERTY([object_id], 'OwnerID') = USER_ID('dbo')

	OPEN #Table_Name

	FETCH NEXT FROM #Table_Name INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = 'ALTER INDEX ALL ON ' + quotename(@TableName) + ' REBUILD'
		PRINT @sql
		EXEC(@sql)
		FETCH NEXT FROM #Table_Name INTO @TableName
	END

	CLOSE #Table_Name
	DEALLOCATE #Table_Name
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_shrink_db]
AS
BEGIN
	DBCC SHRINKDATABASE ('VIGIL-Audit')
	DBCC SHRINKFILE('VIGIL-Audit', 1)
	DBCC SHRINKFILE('VIGIL-Audit_LOG', 1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_set_criteria]
	@idx smallint= NULL
	, @report_id tinyint = NULL
	, @user_name nvarchar(50) = NULL
	, @group_name nvarchar(50) = NULL
	, @criteria_type nvarchar(30) = NULL
	, @val int = NULL
	, @type_tag nvarchar(64) = NULL
	, @delete bit = 0
AS
set nocount on
declare @user_id smallint
	, @type_id tinyint
if @delete = 1
begin
	if @idx is null
	begin
		raiserror('Record ID is wrong.',16,1)
		return
	end
	delete
	from audit_report_criterias
	where [idx] = @idx
	GOTO END_PROC
end
if @idx is null
begin
	if @criteria_type is NULL
	begin
		raiserror('Criterion type is missed.',16,1)
		return
	end

	create table #tmp_ids([idx] smallint)
	if @report_id is null
		insert into #tmp_ids([idx])
		select [idx]
		from audit_report_criterias (nolock)
		where report_id is null
	else
		insert into #tmp_ids([idx])
		select [idx]
		from audit_report_criterias (nolock)
		where report_id = @report_id
	if @user_name is null and @group_name is null
		select @idx = a.[idx]
		from #tmp_ids f
		inner join audit_report_criterias a (nolock) on f.[idx] = a.[idx]
		inner join audit_report_criteria_types t (nolock) on t.[idx] = a.[type_id]
		where a.[user_id] is null
		and a.group_name is null
		and t.[name] = @criteria_type
	else
	begin
		if @user_name is null
			select @idx = a.[idx]
			from #tmp_ids f
			inner join audit_report_criterias a (nolock) on f.[idx] = a.[idx]
			inner join audit_report_criteria_types t (nolock) on t.[idx] = a.[type_id]
			where a.[user_id] is null
			and a.group_name = @group_name
			and t.[name] = @criteria_type
		else
			select @idx = a.[idx]
			from #tmp_ids f
			inner join audit_report_criterias a (nolock) on f.[idx] = a.[idx]
			inner join audit_users u (nolock) on u.[idx] = a.[user_id]
			inner join audit_report_criteria_types t (nolock) on t.[idx] = a.[type_id]
			where u.[name] = @user_name
			and a.group_name is null
			and t.[name] = @criteria_type
	end
end
if @criteria_type is null and @idx is not null
	select @type_id = type_id
	from audit_report_criterias (nolock)
	where [idx] = @idx
else
begin
	select @type_id = [idx]
	from audit_report_criteria_types (nolock)
	where [name] = @criteria_type
	if @@rowcount = 0
	begin
		insert into audit_report_criteria_types([name])
		values(@criteria_type)
		select @type_id = scope_identity()
	end
end
if @idx is NULL
begin
	if ((@user_name is not null) and (@group_name is not null))
	begin
		raiserror('Wrong user or group name.',16,1)
		return
	end
	if @user_name is not null
	begin
		select @user_id = [idx]
		from audit_users (nolock)
		where [name] = @user_name

		if @@rowcount = 0
		begin
			insert into audit_users([name])
			values(@user_name)
			select @user_id = scope_identity()
		end
	end
	insert into audit_report_criterias( report_id, [user_id], group_name, type_id, val )
	values(@report_id, @user_id, @group_name, @type_id, @val )
	if @@error = 0
		select @idx = scope_identity()
	else
		select @idx = -1
end
else
begin
	if @val is not null
		update audit_report_criterias set val = @val
		where [idx] = @idx
	if exists(select * from audit_report_criterias (nolock) where [idx] = @idx and type_id != @type_id)
		update audit_report_criterias set type_id = @type_id
		where [idx] = @idx
end
if @type_tag is not null and @idx is not null and @idx > 0
	update a set tag = @type_tag
	from audit_report_criteria_types a
	where a.[idx] = @type_id
END_PROC:
select @idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_set_details]
	@guid as uniqueidentifier,
	@user nvarchar(50) = 'N/A',
	@event_type nvarchar(30) = NULL,
	@event nvarchar(30) = NULL,
	@details_info nvarchar(64) = NULL,
	@details_info_type nvarchar(30) = NULL
AS
BEGIN
	if @details_info_type is null and @details_info is not null
	begin
		raiserror('Detail_info_type parameter is missed.',16,1)
		return
	end

	DECLARE @user_id smallint
	DECLARE	@audit_detail_id int
	DECLARE	@event_id smallint
	DECLARE	@event_type_id smallint
	DECLARE	@details_info_id int
	DECLARE	@details_info_type_id int
	DECLARE	@err nvarchar(128)

	select @audit_detail_id = [idx] from audit_details (nolock) where [guid] = @guid

	if @audit_detail_id is null
	begin
		if @user is null or len(@user)=0
		begin
			raiserror('User parameter is missed.',16,1)
			return
		end

		if @event is null or len(@event)=0
		begin
			raiserror('Event parameter is missed.',16,1)
			return
		end

		if @event_type is null
		begin
			raiserror('Event parameter is missed.',16,1)
			return
		end

		select @user_id = [idx]	from audit_users (nolock) where [name] = @user

		if @user_id is null
		begin
			insert into audit_users([name]) values(@user)

			if @@error <> 0
				return

			select @user_id = scope_identity()
		end

		select @event_type_id = [idx] from audit_event_types (nolock) where [name] = @event_type

		if @event_type_id is null
		begin
			insert into audit_event_types([name]) values(@event_type)

			if @@error <> 0
				return

			select @event_type_id = scope_identity()
		end

		select @event_id = [idx] from audit_events (nolock) where [name] = @event and  [type_id] = @event_type_id

		if @event_id is null
		begin
			insert into audit_events([name], [type_id]) values(@event, @event_type_id)

			if @@error <> 0
				return

			select @event_id = scope_identity()
		end

		insert audit_details ([guid], [event_id], [user_id]) values(@guid, @event_id, @user_id)

		if @@error <> 0
			return

		select @audit_detail_id = scope_identity()
	end

	if @details_info is not null and len(@details_info) > 0
	begin
		if @details_info_type is null
		begin
			raiserror('Detail_info_type parameter is missed.',16,1)
			return
		end

		select @details_info_type_id = [idx] from audit_details_info_types (nolock) where [name] = @details_info_type

		if @details_info_type_id is null
		begin
			insert into audit_details_info_types([name]) values(@details_info_type)

			if @@error <> 0
				return

			select @details_info_type_id = scope_identity()
		end

		insert into audit_details_info([audit_details_id], [data], [data_type_id]) values(@audit_detail_id, @details_info, @details_info_type_id)
	end
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_vpos_activity]
@start_date datetime,
@end_date datetime,
@offset bigint = 0,
@limit int = 10000
AS
BEGIN
	SELECT DISTINCT idx, event_time, username, [event],
		STUFF((SELECT CHAR(127) + v1.datatype
			FROM vw_vpos_activity v1
			WHERE v1.idx = v2.idx
			ORDER BY v1.idx, v1.datatype
			FOR XML PATH('')), 1, 1, '') AS datatype,
		STUFF((SELECT CHAR(127) + v1.data
			FROM vw_vpos_activity v1
			WHERE v1.idx = v2.idx
			ORDER BY v1.idx, v1.datatype
			FOR XML PATH('')), 1, 1, '') AS data
	FROM vw_vpos_activity v2
	ORDER BY idx DESC
	OFFSET @OFFSET ROWS
	FETCH NEXT @LIMIT ROWS ONLY
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_invalid_records]
AS
BEGIN
	SET NOCOUNT ON

	-- Do nothing for now. Code assumes this procedure exists for all VIGIL databases.
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_update_db_stats]
WITH EXECUTE AS 'dbo'
AS
BEGIN
	DECLARE @dbid AS INTEGER

	SELECT @dbid = DB_ID()

	EXEC sp_updatestats
	DBCC FLUSHPROCINDB(@dbid)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_user_performance]
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @keyword nvarchar(30)
	declare @group_name nvarchar(50)
	declare @user_criterai_count int
	declare @group_criterai_count int
	declare @start_date datetime
	declare @sql nvarchar(1000)
	declare @sql_params nvarchar(200)

	/* the count of criteria records this user has */
	select @user_criterai_count =
		COUNT(*)
		from audit_report_criterias c
		inner join audit_users u on c.[user_id] = u.[idx]
		where u.[name] = @user

	if @user_criterai_count = 0
	begin
		select @group_name = groupname from [VIGIL-DVR].[dbo].[usergroup] where username = @user

		select @group_criterai_count =
			COUNT(*)
			from audit_report_criterias
			where group_name = @group_name
	end

	create table #tmp_user_perf(
		title varchar(30)
		, keyword varchar(30) collate DATABASE_DEFAULT
		, value float)

	declare tmp_reports cursor local for
		select keyword
		from audit_reports (nolock)
		where summary = 1

	open tmp_reports

	fetch next from tmp_reports into @keyword

	while @@fetch_status = 0
	begin
		-- Find this current criterias report span.  Then use it to decrease the start date.
		declare @span int

		if @user_criterai_count > 0
		begin
			select @span = l.idx
			from audit_report_criterias c (nolock)
			inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join audit_users u on c.[user_id] = u.[idx]
			inner join (
				select 1 as idx, 'Daily' as keyword
				union
				select 2, 'Weekly'
				union
				select 3, 'Monthly'
			) l on t.[name] = l.[keyword]
			where u.[name] = @user and @keyword = ar.keyword
		end
		ELSE if @group_criterai_count > 0
		begin
			select @span = l.idx
			from audit_report_criterias c (nolock)
			inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join (
				select 1 as idx, 'Daily' as keyword
				union
				select 2, 'Weekly'
				union
				select 3, 'Monthly'
			) l on t.[name] = l.[keyword]
			where c.[group_name] = @group_name and @keyword = ar.keyword
		end
		else
		begin
			select @span = l.idx
			from audit_report_criterias c (nolock)
			inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join (
				select 1 as idx, 'Daily' as keyword
				union
				select 2, 'Weekly'
				union
				select 3, 'Monthly'
			) l on t.[name] = l.[keyword]
			where c.user_id is null and c.[group_name] = '' and @keyword = ar.keyword
		end

		if @end_date is null
		begin
			select @end_date = getdate()
		end

		IF @span = 1
		begin
			select @start_date = convert(nvarchar(100), @end_date, 106)
		end
		ELSE IF @span = 2
		begin
			select @start_date = dateadd(ww, -1, @end_date)
		end
		ELSE
		begin
			select @start_date = dateadd(m, -1, @end_date)
		end

		if @span is not null
		begin
			-- At this point, we need to add the values for each criteria into the temp table.
			-- For instance, the users value for his current time logged in.
			set @sql = N'exec vigil_audit_get_' + LOWER(@keyword) + N' @start_date, @end_date, @user, @event_type'
			set @sql_params = N'@start_date datetime, @end_date datetime, @user nvarchar(50), @event_type nvarchar(30)'

			insert into #tmp_user_perf
			exec sp_executesql @sql, @sql_params, @start_date, @end_date, @user, @event_type
		end

		fetch next from tmp_reports into @keyword
	end

	close tmp_reports

	deallocate tmp_reports

	-- For each criteria, find its percentage, then take the total for all and divide them by the number
	-- of active criterias for a average total.
	-- if the count not zero, he has criteria, else use the groups.

	if @user_criterai_count > 0
	begin
		select
			convert(money,
				avg(
					-- If the percentage of each report summary is greater than 100%, use 100%, else use the % found.
					CASE WHEN ISNULL(a.value,0.0)*100.0/(convert(money,m.val)) > 100
					THEN 100
					ELSE ISNULL(a.value,0.0)*100.0/(convert(money,m.val))
					END
				    )
				) as perf
		from audit_reports r (nolock)
		left join #tmp_user_perf a on a.keyword = r.keyword
		inner join (
			select c.idx, c.report_id, u.[name] as [user_name], convert(int,c.val) as val, t.[name]
			from audit_report_criterias c (nolock)
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join audit_users u on c.[user_id] = u.[idx]
			where u.[name] = @user
		) m on r.idx = m.report_id
		where m.val != 0
	end
	else if @group_criterai_count > 0
	begin
		select
			convert(money,
				avg(
					-- If the percentage of each report summary is greater than 100%, use 100%, else use the % found.
					CASE WHEN ISNULL(a.value,0.0)*100.0/(convert(money,m.val)) > 100
					THEN 100
					ELSE ISNULL(a.value,0.0)*100.0/(convert(money,m.val))
					END
				    )
				) as perf

		from audit_reports r (nolock)
		left join #tmp_user_perf a on a.keyword = r.keyword
		inner join (
			select c.idx, c.report_id, c.[group_name] as [user_name], convert(int,c.val) as val, t.[name]
			from audit_report_criterias c (nolock)
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			where c.[group_name] = @group_name
		) m on r.idx = m.report_id
		where m.val != 0
	end
	else
	begin
		select
			convert(money,
				avg(
					-- If the percentage of each report summary is greater than 100%, use 100%, else use the % found.
					CASE WHEN ISNULL(a.value,0.0)*100.0/(convert(money,m.val)) > 100
					THEN 100
					ELSE ISNULL(a.value,0.0)*100.0/(convert(money,m.val))
					END
					)
				) as perf
			from audit_reports r (nolock)
			left join #tmp_user_perf a on a.keyword = r.keyword
			inner join (
				select c.idx, c.report_id, c.[group_name] as [user_name], convert(int,c.val) as val, t.[name]
				from audit_report_criterias c (nolock)
				inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
				where c.user_id is null and c.[group_name] = ''
			) m on r.idx = m.report_id
			where m.val != 0
	end
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_daily_user_performance]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @keyword nvarchar(30)
	declare @group_name nvarchar(50)
	declare @user_criterai_count int
	declare @span int

	create table #tmp_daily_perf(
		[day] datetime,
		perf money
	)

	declare @date_part_start varchar(10)
	declare @date_part_end varchar(10)
	declare @day varchar(10)
	declare @today varchar(10)

	set @today = convert(varchar(10), getdate(), 20)
	set @date_part_start = convert(varchar(10), @start_date, 20)
	set @date_part_end = convert(varchar(10), @end_date, 20)
	set @day = @date_part_start

	declare @sql nvarchar(1000)
	declare @sql_params nvarchar(200)

	while @day <= @date_part_end
	begin
		declare @perf money
		select @perf = null

		select @perf = perf from audit_daily_performance_cache where username = @user and [day] = @day

		if @perf is null
		begin
			set @end_date = @day + ' 23:59:59'

			-- the count of criteria records this user has
			select @user_criterai_count =
				COUNT(*)
				from audit_report_criterias c
				inner join audit_users u on c.[user_id] = u.[idx]
				where u.[name] = @user

			create table #tmp_user_perf_value(
				title varchar(30),
				keyword varchar(30) collate DATABASE_DEFAULT,
				value float
			)

			declare tmp_reports cursor local for
				select keyword
				from audit_reports (nolock)
				where summary = 1

			open tmp_reports

			fetch next from tmp_reports into @keyword

			while @@fetch_status = 0
			begin
				-- Find this current criterias report span.  Then use it to decrease the start date.
				if @user_criterai_count > 0
				begin
					select @span = l.idx
					from audit_report_criterias c (nolock)
					inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
					inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
					inner join audit_users u on c.[user_id] = u.[idx]
					inner join (
						select 1 as idx, 'Daily' as keyword
						union
						select 2, 'Weekly'
						union
						select 3, 'Monthly'
					) l on t.[name] = l.[keyword]
					where u.[name] = @user and @keyword = ar.keyword
				end
				ELSE
				begin
					--  get the group this user is in
					select @group_name = groupname from [VIGIL-DVR].[dbo].[usergroup] where username = @user

					select @span = l.idx
					from audit_report_criterias c (nolock)
					inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
					inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
					inner join (
						select 1 as idx, 'Daily' as keyword
						union
						select 2, 'Weekly'
						union
						select 3, 'Monthly'
					) l on t.[name] = l.[keyword]
					where c.[group_name] = @group_name and @keyword = ar.keyword

					if @span is null
					begin
						select @group_name = ''

						select @span = l.idx
						from audit_report_criterias c (nolock)
						inner join audit_reports ar (nolock) on ar.[idx] = c.[report_id]
						inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
						inner join (
							select 1 as idx, 'Daily' as keyword
							union
							select 2, 'Weekly'
							union
							select 3, 'Monthly'
						) l on t.[name] = l.[keyword]
						where c.[group_name] = '' and @keyword = ar.keyword
					end
				end

				if @end_date is null
				begin
					select @end_date = getdate()
				end

				IF @span = 1
				begin
					select @start_date = convert(nvarchar(100), @end_date, 106)
				end
				ELSE IF @span = 2
				begin
					select @start_date = dateadd(ww, -1, @end_date)
				end
				ELSE
				begin
					select @start_date = dateadd(m, -1, @end_date)
				end

				if @span is not null
				begin
					-- At this point, we need to add the values for each criteria into the temp table.
					-- For instance, the users value for his current time logged in.
					set @sql = N'exec vigil_audit_get_' + LOWER(@keyword) + N' @start_date, @end_date, @user, @event_type'
					set @sql_params = N'@start_date datetime, @end_date datetime, @user nvarchar(50), @event_type nvarchar(30)'

					insert into #tmp_user_perf_value
					exec sp_executesql @sql, @sql_params, @start_date, @end_date, @user, @event_type
				end

				fetch next from tmp_reports into @keyword
			end

			close tmp_reports
			deallocate tmp_reports

			-- For each criteria, find its percentage, then take the total for all and divide them by the number
			-- of active criterias for a average total.
			-- if the count not zero, he has criteria, else use the groups.
			if @user_criterai_count > 0
			begin
				select @perf = convert(money,
					avg(
						-- If the percentage of each report summary is greater than 100%, use 100%, else use the % found.
						CASE WHEN ISNULL(a.value,0.0)*100.0/(convert(money,m.val)) > 100
						THEN 100
						ELSE ISNULL(a.value,0.0)*100.0/(convert(money,m.val))
						END
						)
					)
				from audit_reports r (nolock)
				left join #tmp_user_perf_value a on a.keyword = r.keyword
				inner join (
					select c.idx, c.report_id, u.[name] as [user_name], convert(int,c.val) as val, t.[name]
					from audit_report_criterias c (nolock)
					inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
					inner join audit_users u on c.[user_id] = u.[idx]
					where u.[name] = @user
				) m on r.idx = m.report_id
				where m.val != 0
			end
			else
			begin
				select @perf =
					convert(money,
						avg(
							-- If the percentage of each report summary is greater than 100%, use 100%, else use the % found.
							CASE WHEN ISNULL(a.value,0.0)*100.0/(convert(money,m.val)) > 100
							THEN 100
							ELSE ISNULL(a.value,0.0)*100.0/(convert(money,m.val))
							END
						)
					)
				from audit_reports r (nolock)
				left join #tmp_user_perf_value a on a.keyword = r.keyword
				inner join (
					select c.idx, c.report_id, c.[group_name] as [user_name], convert(int,c.val) as val, t.[name]
					from audit_report_criterias c (nolock)
					inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
					where c.[group_name] = @group_name
				) m on r.idx = m.report_id
				where m.val != 0
			end

			insert into #tmp_daily_perf values(@day, @perf)

			if @perf is not null and @day < @today
			begin
				insert into audit_daily_performance_cache values(@user, @day, @perf)
			end

			drop table #tmp_user_perf_value
		end
		else
		begin
			insert into #tmp_daily_perf values(@day, @perf)
		end

		set @day = convert(varchar(10), dateadd(day, 1, @day), 20)
	end

	select * from #tmp_daily_perf
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_reports_list]
AS
begin
	set nocount on

	SELECT a.[idx]
		, a.[name] as report_name
		, a.keyword
		, a.summary
		, ISNULL(u.units,'') AS units
		, ISNULL(u.ids_report, 0) AS ids_report
		, ISNULL(u.ids_units,0) AS ids_units
	FROM audit_reports a (NOLOCK)
	LEFT JOIN (
		SELECT 'TIME_LOGGED_IN' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'FOOTAGE_VIEWED' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'IDLE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14686 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'SEARCHES_DONE' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units
		UNION
		SELECT 'POS_QUERY' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units
		UNION
		SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units
		UNION
		SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units
		UNION
		SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units
		UNION
		SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units
		UNION
		SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' AS units, 19254 AS ids_report, 19244 AS ids_units
		UNION
		SELECT 'LOGGED_IN_FAILED' AS keyword, 'Min(s)' AS units, 19268 AS ids_report, 10305 AS ids_units
	) u ON a.keyword = u.keyword
	ORDER BY a.[name]
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_reports_combolist]
AS
begin
	set nocount on

	SELECT 'TIME_LOGGED_IN' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units, 1 as sortby
	UNION
	SELECT 'FOOTAGE_VIEWED' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units, 2
	UNION
	SELECT 'SEARCHES_DONE' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units, 4
	UNION
	SELECT 'POS_QUERY' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units, 3
	UNION
	SELECT 'LOGGED_IN_FAILED' AS keyword, 'Min(s)' AS units, 19268 AS ids_report, 10305 AS ids_units, 5
	ORDER BY sortby
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_report_criterias]
	@user nvarchar(50) = NULL
AS
begin
	set nocount on

	SELECT
		r.[idx]
		, r.[name] as report_name
		, r.keyword
		, m.val as criteria_val
		, m.[name] as criteria_type
		, m.tag
	FROM audit_reports r (NOLOCK)
	LEFT JOIN (
		select c.idx
			, c.report_id
			, u.[name] as [user_name]
			, convert(int, c.val) as val
			, t.[name]
			, t.tag
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		inner join audit_users u on c.[user_id] = u.[idx]
		where u.[name] = @user
	) m on m.report_id = r.[idx]
	WHERE r.summary = 1
	ORDER BY r.[name]
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_report_criterias_by_group]
	@user nvarchar(50) = NULL
AS
begin
	set nocount on

	SELECT
		r.[idx]
		, r.[name] as report_name
		, r.keyword
		, m.val as criteria_val
		, m.[name] as criteria_type
		, m.tag
	FROM audit_reports r (NOLOCK)
	LEFT JOIN (
		select c.idx
			, c.report_id
			, c.[group_name] as [user_name]
			, convert(int, c.val) as val
			, t.[name]
			, t.tag
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		where c.[group_name] = @user
	) m on m.report_id = r.[idx]
	WHERE r.summary = 1
	ORDER BY r.[name]
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_report_criterias_all]
AS
begin
	set nocount on

	SELECT f.*
		, u.units
		, u.ids_report
		, u.ids_units
	FROM (
		SELECT a.idx AS report_idx
			, b.name as username
			, a.[name] AS report_name
			, m.idx AS criteria_idx
			, m.val AS criteria_val
			, m.[name] AS criteria_type
			, 0 AS group_bit
			, a.keyword
		FROM audit_reports a (NOLOCK)
		CROSS JOIN audit_users b (NOLOCK)
		LEFT JOIN (
			SELECT c.idx
				, c.report_id
				, u.[name] AS [user_name]
				, convert(int, c.val) AS val
				, t.[name]
				, t.tag
			FROM audit_report_criterias c (NOLOCK)
			INNER JOIN audit_report_criteria_types t (NOLOCK) ON t.[idx] = c.[type_id]
			INNER JOIN audit_users u ON c.[user_id] = u.[idx]
		) m ON m.report_id = a.[idx] AND b.name = m.[user_name]
		WHERE a.summary=1
		UNION
		SELECT a.idx AS report_idx
			, b.groupname AS username
			, a.[name] AS report_name
			, m.idx AS criteria_idx
			, m.val AS criteria_val
			, m.[name] AS criteria_type
			, 1 AS group_bit
			, a.keyword
		FROM audit_reports a (NOLOCK)
		CROSS JOIN [VIGIL-DVR].[dbo].[groups] b (NOLOCK)
		LEFT JOIN (
			SELECT c.idx
				, c.report_id
				, c.[group_name] AS [user_name]
				, convert(int, c.val) AS val
				, t.[name]
				, t.tag
			FROM audit_report_criterias c (NOLOCK)
			INNER JOIN audit_report_criteria_types t (NOLOCK) ON t.[idx] = c.[type_id]
		) m ON m.report_id = a.[idx] AND b.groupname = m.[user_name]
		WHERE a.summary=1
		UNION
		SELECT a.idx AS report_idx
			, '' AS username
			, a.[name] AS report_name
			, m.idx AS criteria_idx
			, m.val AS criteria_val
			, m.[name] AS criteria_type
			, 1 AS group_bit
			, a.keyword
		FROM audit_reports a (NOLOCK)
		LEFT JOIN (
			SELECT c.idx
				, c.report_id
				, c.[group_name] AS [user_name]
				, convert(int, c.val) AS val
				, t.[name]
				, t.tag
			FROM audit_report_criterias c (NOLOCK)
			INNER JOIN audit_report_criteria_types t (NOLOCK) ON t.[idx] = c.[type_id]
		) m ON m.report_id = a.[idx] AND m.[user_name] = ''
		WHERE a.summary=1
	) f
	INNER JOIN (
		SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units
		UNION
		SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units
		UNION
		SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units
		UNION
		SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units
		UNION
		SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' AS units, 19254 AS ids_report, 19244 AS ids_units
	) u ON f.keyword = u.keyword
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_does_user_or_group_have_criteria]
	@user nvarchar(50) = NULL
AS
begin
	set nocount on

	declare @group_name nvarchar(50)
	declare @user_criteria_count int

	select @user_criteria_count = COUNT(*)
		from audit_report_criterias c
		inner join audit_users u on c.[user_id] = u.[idx]
		where u.[name] = @user

	if @user_criteria_count = 0
	begin
		/* get the group name from the user */
		select @group_name = groupname from [VIGIL-DVR].[dbo].[usergroup] where username = @user

		select @user_criteria_count = COUNT(*)
			from audit_report_criterias
			where [group_name] = @group_name
	end

	if @user_criteria_count = 0
	begin
		select @user_criteria_count = COUNT(*)
			from audit_report_criterias
			where [group_name] = ''
	end

	select @user_criteria_count as user_criteria_count
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_summary_all]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)
	declare @keyword nvarchar(30)
	declare @sql nvarchar(1000)
	declare @sql_params nvarchar(200)

	select @group = groupname
		from [VIGIL-DVR].[dbo].[usergroup] (nolock)
		where username = @user

	create table #tmp_sum_all(
		title nvarchar(30)
		, keyword nvarchar(30) collate DATABASE_DEFAULT
		, value float)

	declare tmp_reports cursor local for
		select keyword
		from audit_reports (nolock)
		where summary = 1

	open tmp_reports

	fetch next from tmp_reports into @keyword

	while @@fetch_status = 0
	begin
		set @sql = N'exec vigil_audit_get_' + LOWER(@keyword) + N' @start_date, @end_date, @user, @event_type'
		set @sql_params = N'@start_date datetime, @end_date datetime, @user nvarchar(50), @event_type nvarchar(30)'

		insert into #tmp_sum_all
		exec sp_executesql @sql, @sql_params, @start_date, @end_date, @user, @event_type

		fetch next from tmp_reports into @keyword
	end

	close tmp_reports
	deallocate tmp_reports

	select
		r.[name] as title
		, a.value
		, m.val as [Criteria Value]
		, m.[name] as [Criteria Type]
		, g.val as [Group Criteria Value]
		, g.[name] as [Group Criteria Type]
		, ISNULL(u.units,'') AS units
		, ISNULL(u.ids_report, 0) AS ids_report
		, ISNULL(u.ids_units,0) AS ids_units
		, ISNULL(u.sort_order,0) AS sort_order
	from audit_reports r (nolock)
	left join #tmp_sum_all a on a.keyword = r.keyword
	left join (
		select c.idx, c.report_id, u.[name] as [user_name], c.val, t.[name]
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		inner join audit_users u on c.[user_id] = u.[idx]
		where u.[name] = @user
	) m on r.idx = m.report_id
	left join (
		select
			c.idx
			, c.report_id
			, c.[group_name] as [user_name]
			, c.val
			, t.[name]
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		where c.[group_name] = @group
	) g on r.idx = g.report_id
	LEFT JOIN (
		SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units, 3 as sort_order
		UNION
		SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units, 0 as sort_order
		UNION
		SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units, 0 as sort_order
		UNION
		SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units, 0 as sort_order
		UNION
		SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units, 0 as sort_order
		UNION
		SELECT 'IDLE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14686 AS ids_report, 10305 AS ids_units, 2 as sort_order
		UNION
		SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units, 4 as sort_order
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units, 0 as sort_order
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units, 0 as sort_order
		UNION
		SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' as units, 19254 as ids_report, 19244 AS ids_units, 0 as sort_order
	) u ON r.keyword = u.keyword
	where r.summary = 1
	ORDER BY 10, 1
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_summary_all_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @max_idx int
	declare @group nvarchar(50)
	declare @keyword nvarchar(30)
	declare @idx int
	declare @sql nvarchar(1000)
	declare @sql_params nvarchar(200)

	select @group = @user

	create table #tmp_userlist(
		idx int identity(1, 1),
		name nvarchar(50)
	)

	if len(@group) > 0
	begin
		insert into #tmp_userlist
		select f.[name]
		from (
			select distinct b.idx
				, b.[name]
			from [VIGIL-DVR].[dbo].[usergroup] a (nolock)
			inner join audit_users b (nolock) on a.username = b.[name]
			where a.groupname = @group
				and b.active = 1
		) f
	end
	else
	begin
		insert into #tmp_userlist
		select f.[name]
		from (
			select distinct b.idx
				, b.[name]
			from [VIGIL-DVR].[dbo].[usergroup] a (nolock)
			inner join audit_users b (nolock) on a.username = b.[name]
			where b.active = 1
		) f
	end

	select @max_idx = @@rowcount

	create table #tmp_sum_all_by_group(title nvarchar(30), keyword nvarchar(30) collate DATABASE_DEFAULT, value float)

	if @max_idx > 0
	begin
		declare tmp_reports cursor local for
		select keyword
		from audit_reports (nolock)
		where summary = 1

		open tmp_reports
		fetch next from tmp_reports into @keyword
		while @@fetch_status = 0
		begin
			select @idx = 1
			while @idx <= @max_idx
			begin
				set @sql = N'exec vigil_audit_get_' + LOWER(@keyword) + N' @start_date, @end_date, @user, @event_type'
				set @sql_params = N'@start_date datetime, @end_date datetime, @user nvarchar(50), @event_type nvarchar(30)'

				select @user = [name] from #tmp_userlist where [idx] = @idx

				insert into #tmp_sum_all_by_group
				exec sp_executesql @sql, @sql_params, @start_date, @end_date, @user, @event_type

				select @idx = @idx + 1
			end

			fetch next from tmp_reports into @keyword
		end
		close tmp_reports
		deallocate tmp_reports
	end

	select
		r.[name] as title
		, a.value
		, m.val as [Criteria Value]
		, m.[name] as [Criteria Type]
		, ISNULL(u.units,'') AS units
		, ISNULL(u.ids_report, 0) AS ids_report
		, ISNULL(u.ids_units,0) AS ids_units
		, ISNULL(u.sort_order,0) AS sort_order
	from audit_reports r (nolock)
	left join (
		select
			keyword
			, sum(isnull(value,0)) as value
		from #tmp_sum_all_by_group
		group by keyword
	) a on a.keyword = r.keyword
	left join (
		select
			c.idx
			, c.report_id
			, c.[group_name] as [user_name]
			, c.val
			, t.[name]
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		where c.[group_name] = @group
	) m on r.idx = m.report_id
	LEFT JOIN (
		SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units, 3 as sort_order
		UNION
		SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units, 0 as sort_order
		UNION
		SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units, 0 as sort_order
		UNION
		SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units, 0 as sort_order
		UNION
		SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units, 0 as sort_order
		UNION
		SELECT 'IDLE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14686 AS ids_report, 10305 AS ids_units, 2 as sort_order
		UNION
		SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units, 4 as sort_order
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units, 0 as sort_order
		UNION
		SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units, 0 as sort_order
		UNION
		SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' AS units, 19254 AS ids_report, 19244 AS ids_units, 0 as sort_order
	) u ON r.keyword = u.keyword
	where r.summary = 1
	ORDER BY 8, 1
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_sum_performance]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @keyword nvarchar(30)
	declare @curr_date datetime
	declare @group_name nvarchar(50)
	declare @user_criterai_count int
	declare @sql nvarchar(1000)
	declare @sql_params nvarchar(200)

	select @group_name = groupname from [VIGIL-DVR].[dbo].[usergroup] where username = @user

	set @curr_date = @start_date

	if @curr_date is null
		set @curr_date = getdate()

	create table #tmp_user_perf_sum(
		title nvarchar(30)
		, keyword nvarchar(30) collate DATABASE_DEFAULT
		, value float)

	/* the count of criteria records this user has */
	select @user_criterai_count = COUNT(*)
		from audit_report_criterias c
		inner join audit_users u on c.[user_id] = u.[idx]
		where u.[name] = @user

	/* if the count not zero, he has criteria, else use the groups. */
	if @user_criterai_count > 0
	begin
		declare tmp_reports cursor local for
			select r.keyword
				, l.start_date
				, l.end_date
			from audit_report_criterias c (nolock)
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join audit_users u (nolock) on c.[user_id] = u.[idx]
			inner join dbo.fn_get_audit_perf_dates(@curr_date) l on t.[name] = l.[name] COLLATE DATABASE_DEFAULT
			inner join audit_reports r (nolock) on c.report_id = r.[idx]
			where u.[name] = @user
	end
	else
	begin
		/* get the group name from the user */
		select @group_name = groupname from [VIGIL-DVR].[dbo].[usergroup] where username = @user

		select @user_criterai_count = COUNT(*)
			from audit_report_criterias
			where [group_name] = @group_name

		if @user_criterai_count = 0
		begin
			select @group_name = ''
		end

		select @user_criterai_count = 0

		declare tmp_reports cursor local for
			select r.keyword
				, l.start_date
				, l.end_date
			from audit_report_criterias c (nolock)
				inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
				inner join dbo.fn_get_audit_perf_dates(@curr_date) l on t.[name] = l.[name] COLLATE DATABASE_DEFAULT
				inner join audit_reports r (nolock) on c.report_id = r.[idx]
			where c.[group_name] = @group_name
	end

	open tmp_reports

	fetch next from tmp_reports into @keyword, @start_date, @end_date

	while @@fetch_status = 0
	begin
		set @sql = N'exec vigil_audit_get_' + LOWER(@keyword) + N' @start_date, @end_date, @user, @event_type'
		set @sql_params = N'@start_date datetime, @end_date datetime, @user nvarchar(50), @event_type nvarchar(30)'

		insert into #tmp_user_perf_sum
		exec sp_executesql @sql, @sql_params, @start_date, @end_date, @user, @event_type

		fetch next from tmp_reports into @keyword, @start_date, @end_date
	end

	close tmp_reports

	deallocate tmp_reports

	/* if the count not zero, he has criteria, else use the groups. */

	if @user_criterai_count > 0
	begin
		select
			r.[name] as title
			, a.value
			, m.val as [Criteria Value]
			, m.[name] as [Criteria Type]
			, ISNULL(u.units,'') AS units
			, ISNULL(u.ids_report, 0) AS ids_report
			, ISNULL(u.ids_units,0) AS ids_units
			, convert(money,a.value*100.0/m.val) as perf
		from audit_reports r (nolock)
		inner join #tmp_user_perf_sum a on a.keyword = r.keyword
		left join (
			select c.idx, c.report_id, u.[name] as [user_name], convert(int,c.val) as val, t.[name]
			from audit_report_criterias c (nolock)
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			inner join audit_users u on c.[user_id] = u.[idx]
			where u.[name] = @user
		) m on r.idx = m.report_id
		LEFT JOIN (
			SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units
			UNION
			SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units
			UNION
			SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units
			UNION
			SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units
			UNION
			SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units
			UNION
			SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' AS units, 19254 AS ids_report, 19244 AS ids_units
		) u ON r.keyword = u.keyword
		where r.summary = 1 and m.val != 0
		ORDER BY 1
	end
	else
	begin
		select
			r.[name] as title
			, a.value
			, m.val as [Criteria Value]
			, m.[name] as [Criteria Type]
			, ISNULL(u.units,'') AS units
			, ISNULL(u.ids_report, 0) AS ids_report
			, ISNULL(u.ids_units, 0) AS ids_units
			, convert(money,a.value*100.0/m.val) as perf
		from audit_reports r (nolock)
		inner join #tmp_user_perf_sum a on a.keyword = r.keyword
		left join (
			select c.idx, c.report_id, c.[group_name] as [user_name], convert(int,c.val) as val, t.[name]
			from audit_report_criterias c (nolock)
			inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
			where c.[group_name] = @group_name
		) m on r.idx = m.report_id
		LEFT JOIN (
			SELECT 'TIME_LOGGED_IN_SUM' AS keyword, 'Min(s)' AS units, 14684 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'FOOTAGE_VIEWED_SUM' AS keyword, 'Min(s)' AS units, 14678 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'FRAME_VIEWED_SUM' AS keyword, 'Frames' AS units, 14685 AS ids_report, 10115 AS ids_units
			UNION
			SELECT 'SEARCHES_DONE_SUM' AS keyword, 'Searches' AS units, 14680 AS ids_report, 14681 AS ids_units
			UNION
			SELECT 'POS_QUERY_SUM' AS keyword, 'Queries' AS units, 14679 AS ids_report, 14682 AS ids_units
			UNION
			SELECT 'ACTIVE_TIME_SUM' AS keyword, 'Min(s)' AS units, 14728 AS ids_report, 10305 AS ids_units
			UNION
			SELECT 'VPOS_EVENTS_FLAGGED_SUM' AS keyword, 'Events' AS units, 18284 AS ids_report, 15267 AS ids_units
			UNION
			SELECT 'VPOS_EVENTS_FLAGGED_PERCENT' AS keyword, 'Percent' AS units, 18288 AS ids_report, 18289 AS ids_units
			UNION
			SELECT 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' AS keyword, 'Tags' AS units, 19254 AS ids_report, 19244 AS ids_units
		) u ON r.keyword = u.keyword
		where r.summary = 1 and m.val != 0
		ORDER BY 1
	end
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_time_logged_in]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		u.name as [User],
		start_time as [Login Time],
		end_time as [Logoff Time],
		dbo.get_time_str(active_time) as [Active Time],
		dbo.get_time_str(idle_time) as [Time Idle],
		dbo.get_time_str(total_time) as [Total Time],
		idle_time as [_TimeIdle],
		total_time as [_TotalTime]
	from
		(select
			start_time,
			end_time,
			datediff(s, start_time, end_time) - coalesce(idle_time, 0) as active_time,
			login_info.[key],
			user_id,
			coalesce(idle_time, 0) as idle_time,
			datediff(s, start_time, end_time) as total_time
		from
			fn_get_audit_login_sessions(@start_date, @end_date, @user, @event_type) login_info
			left join
				(select
					sum(datediff(s, start_time, end_time)) as idle_time,
					login_session_id
				from
					fn_get_audit_session_info(@start_date, @end_date, @user, 'IdleSession', @event_type)
				group by
					login_session_id
			) idle_info on login_info.[key] = idle_info.login_session_id
		) time_logged_in
		inner join audit_users u on time_logged_in.user_id = u.idx
	order by start_time
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_logged_in_failed]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @event_id smallint

	select @event_id = [idx]
	from audit_events (NOLOCK)
	where [name] = 'login_failed'

	select u.name as [User], 
		   d.event_time as [Login Time]
	from audit_details d (NOLOCK) left join audit_users u on d.user_id = u.idx
	where d.event_id = @event_id and u.name = @user and d.event_time between @start_date and @end_date
	order by event_time
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_footage_viewed]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		@user as [User],
		start_info.start_time as [Playback Start],
		end_info.end_time as [Playback End],
		start_info.cam_number as [Camera Numbers],
		start_info.playtime_start as [Footage Start],
		end_info.playtime_stop as [Footage End],
		abs(end_info.frame_stop - start_info.frame_start) + 1 as [Frames Watched],
		dbo.get_time_str(datediff(s, start_info.start_time, end_info.end_time)) as [Total Time Watched]
	from
		(
			select session_id, user_id, start_time, cam_number, playtime_start, frame_start from fn_get_audit_session_info(@start_date, @end_date, @user, 'PlaybackSession', @event_type) f
				inner join (select audit_details_id, data as cam_number from audit_details_info i join audit_details_info_types t on i.data_type_id = t.idx where t.name = 'CameraNumber') cam on f.detail_id_start = cam.audit_details_id
				inner join (select audit_details_id, try_convert(int, data) as frame_start from audit_details_info i join audit_details_info_types t on i.data_type_id = t.idx where t.name = 'CurrentFrame') frame on f.detail_id_start = frame.audit_details_id
				inner join (select audit_details_id, try_convert(datetime, data) as playtime_start from audit_details_info i join audit_details_info_types t on i.data_type_id = t.idx where t.name = 'CurrentPlayTime') play_time on f.detail_id_start = play_time.audit_details_id
		) start_info
		inner join
		(
			select session_id, user_id, end_time, playtime_stop, frame_stop from fn_get_audit_session_info(@start_date, @end_date, @user, 'PlaybackSession', @event_type) f
				inner join (select audit_details_id, try_convert(int, data) as frame_stop from audit_details_info i join audit_details_info_types t on i.data_type_id = t.idx where t.name = 'CurrentFrame') frame on f.detail_id_end = frame.audit_details_id
				inner join (select audit_details_id, try_convert(datetime, data) as playtime_stop from audit_details_info i join audit_details_info_types t on i.data_type_id = t.idx where t.name = 'CurrentPlayTime') play_time on f.detail_id_end = play_time.audit_details_id
		) end_info
		on start_info.session_id = end_info.session_id
	order by start_info.start_time
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_searches_done]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		u.[name] as [User]
		, d.event_time as [Search Date]
		, r1.data as [Camera Numbers]
		, convert(datetime, r3.data) as [Date From]
		, convert(datetime, r4.data) as [Date To]
		, r2.data as [Footage Type]
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	left join (
		select i1.data, i1.[audit_details_id]
		from audit_details_info i1 (nolock)
		inner join audit_details_info_types it1 (nolock) on  it1.[idx] = i1.[data_type_id] and it1.[name]='CameraNumbers'
	) r1 on r1.[audit_details_id] = d.[idx]
	left join (
		select i2.data, i2.[audit_details_id]
		from audit_details_info i2 (nolock)
		inner join audit_details_info_types it2 (nolock) on  it2.[idx] = i2.[data_type_id] and it2.[name]='FootageType'
	) r2 on r2.[audit_details_id] = d.[idx]
	left join (
		select i3.data, i3.[audit_details_id]
		from audit_details_info i3 (nolock)
		inner join audit_details_info_types it3 (nolock) on  it3.[idx] = i3.[data_type_id] and it3.[name]='DateFrom'
	) r3 on r3.[audit_details_id] = d.[idx]
	left join (
		select i4.data, i4.[audit_details_id]
		from audit_details_info i4 (nolock)
		inner join audit_details_info_types it4 (nolock) on  it4.[idx] = i4.[data_type_id] and it4.[name]='DateTo'
	) r4 on r4.[audit_details_id] = d.[idx]
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'search'
	order by d.event_time
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_pos_query]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		u.[name] as [User]
		, d.event_time as [Search Date]
		, r1.data as [Date From]
		, r2.data as [Date To]
		, r6.data as [Item]
		, r7.data as [Code]
		, r3.data as [RegisterNo]
		, r9.data as [Cashier]
		, r4.data as [MinValue]
		, r5.data as [MaxValue]
		, r11.data as [Quantity]
		, r0.data as [Idx]
		, r8.data as [ReceiptNo]
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	left join ( select i1.data, audit_details_id
			from audit_details_info i1 (nolock)
			inner join audit_details_info_types it1 (nolock) on  it1.[idx] = i1.[data_type_id] and it1.[name]='DateFrom'
	) r1 on r1.[audit_details_id] = d.[idx]
	left join (
		select i2.data, i2.audit_details_id
		from audit_details_info i2 (nolock)
		inner join audit_details_info_types it2 (nolock) on  it2.[idx] = i2.[data_type_id] and it2.[name]='DateTo'
	) r2 on r2.[audit_details_id] = d.[idx]
	left join (
		select i3.data, i3.audit_details_id
		from audit_details_info i3 (nolock)
		inner join audit_details_info_types it3 (nolock) on  it3.[idx] = i3.[data_type_id] and it3.[name]='RegisterNo'
	) r3 on r3.[audit_details_id] = d.[idx]
	left join (
		select i4.data, i4.audit_details_id
		from audit_details_info i4 (nolock)
		inner join audit_details_info_types it4 (nolock) on  it4.[idx] = i4.[data_type_id] and it4.[name]='MinValue'
	) r4 on r4.[audit_details_id] = d.[idx]
	left join (
		select i5.data, i5.audit_details_id
		from audit_details_info i5
		inner join audit_details_info_types it5 (nolock) on  it5.[idx] = i5.[data_type_id] and it5.[name]='MaxValue'
	) r5  on r5.[audit_details_id] = d.[idx]
	left join (
		select i6.data, i6.audit_details_id
		from audit_details_info i6 (nolock)
		inner join audit_details_info_types it6 (nolock) on  it6.[idx] = i6.[data_type_id] and it6.[name]='Item'
	) r6 on r6.[audit_details_id] = d.[idx]
	left join (
		select i7.data, i7.audit_details_id
		from audit_details_info i7
		inner join audit_details_info_types it7 (nolock) on  it7.[idx] = i7.[data_type_id] and it7.[name]='Action'
	) r7 on r7.[audit_details_id] = d.[idx]
	left join (
		select i8.data, i8.audit_details_id
		from audit_details_info i8 (nolock)
		inner join audit_details_info_types it8 (nolock) on  it8.[idx] = i8.[data_type_id] and it8.[name]='ReceiptNo'
	) r8 on r8.[audit_details_id] = d.[idx]
	left join (
		select i9.data, i9.audit_details_id
		from audit_details_info i9 (nolock)
		inner join audit_details_info_types it9 (nolock) on  it9.[idx] = i9.[data_type_id] and it9.[name]='Cashier'
	) r9 on r9.[audit_details_id] = d.[idx]
	left join (
		select i0.data, i0.audit_details_id
		from audit_details_info i0 (nolock)
		inner join audit_details_info_types it0 (nolock) on  it0.[idx] = i0.[data_type_id] and it0.[name]='Idx'
	) r0 on r0.[audit_details_id] = d.[idx]
	left join (
		select i11.data, i11.audit_details_id
		from audit_details_info i11 (nolock)
		inner join audit_details_info_types it11 (nolock) on  it11.[idx] = i11.[data_type_id] and it11.[name]='Quantity'
	) r11 on r11.[audit_details_id] = d.[idx]
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'pos_query'
	order by d.event_time
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_time_logged_in_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_group_logged (
		[User] nvarchar(50)
		, [Login Time] datetime NULL
		, [Logoff Time] datetime NULL
		, [Active Time] nvarchar(10) NULL
		, [Time Idle] nvarchar(10) NULL
		, [Total Time] nvarchar(10) NULL
		, _TimeIdle int NULL
		, _TotalTime int NULL
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_group_logged([User], [Login Time], [Logoff Time], [Active Time], [Time Idle], [Total Time], _TimeIdle, _TotalTime)
		exec vigil_audit_get_time_logged_in @start_date, @end_date, @user, @event_type

		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_group_logged order by 2, 3
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_logged_in_failed_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_failed_group_logged (
		[User] nvarchar(50)
		, [Login Time] datetime NULL
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_failed_group_logged([User], [Login Time])
		exec vigil_audit_get_logged_in_failed @start_date, @end_date, @user, @event_type

		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_failed_group_logged order by 2
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_idle_time_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_group_idle (
		[User] nvarchar(50)
		, [Idle Start Time] datetime NULL
		, [Idle End Time] datetime NULL
		, [Total Idle Time] nvarchar(10) NULL
		, _TotalIdleTime int NULL
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_group_idle(
			[User]
			, [Idle Start Time]
			, [Idle End Time]
			, [Total Idle Time]
			, _TotalIdleTime
		)
		select
			@user as [User]
			, start_time
			, end_time
			, dbo.get_time_str(
				ISNULL(datediff(s, start_time, end_time),0)
			) as Total
			, ISNULL(datediff(s, start_time, end_time),0) as _Total
		from dbo.fn_get_audit_session_info(
					@start_date
					, @end_date
					, @user
					, 'IdleSession'
					, @event_type)
		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_group_idle order by 2, 3
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_idle_time_sum_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @max_idx int
	declare @group nvarchar(50)
	declare @idx int

	select @group = @user

	select idx = identity(int,1,1)
		, f.[name]
	into #tmp_userlist_idle
	from (
		select distinct b.idx
			, b.[name]
		from [VIGIL-DVR].[dbo].[usergroup] a (nolock)
		inner join audit_users b (nolock) on a.username = b.[name]
		where a.groupname = @group
			and b.active = 1
	) f
	select @max_idx = @@rowcount
	create table #tmp_sum_idle_time_by_group(title nvarchar(30), keyword nvarchar(30) collate DATABASE_DEFAULT, value float)
	if @max_idx>0
	begin
		select @idx = 1
		while @idx <= @max_idx
		begin
			select @user = [name] from #tmp_userlist_idle where [idx] = @idx
			insert into #tmp_sum_idle_time_by_group
			exec vigil_audit_get_idle_time_sum @start_date, @end_date, @user, @event_type
			select @idx = @idx + 1
		end
	end
	select
		r.[name] as title
		, a.value as Total
		, m.val as [Criteria Value]
		, m.[name] as [Criteria Type]
		, r.units
	from audit_reports r (nolock)
	inner join (
		select
			keyword
			, sum(isnull(value,0)) as value
		from #tmp_sum_idle_time_by_group
		group by keyword
	) a on a.keyword = r.keyword
	left join (
		select
			c.idx
			, c.report_id
			, c.[group_name] as [user_name]
			, c.val
			, t.[name]
		from audit_report_criterias c (nolock)
		inner join audit_report_criteria_types t (nolock) on t.[idx] = c.[type_id]
		where c.[group_name] = @group
	) m on r.idx = m.report_id
	where r.summary = 1
	ORDER BY 1
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_footage_viewed_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_group_footage (
		[User] nvarchar(50)
		, [Playback Start] datetime NULL
		, [Playback End] datetime NULL
		, [Camera Numbers] nvarchar(64) NULL
		, [Footage Start] datetime NULL
		, [Footage End] datetime NULL
		, [Frames Watched] int NULL
		, [Total Time Watched] nvarchar(10)
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_group_footage
		exec vigil_audit_get_footage_viewed @start_date, @end_date, @user, @event_type

		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_group_footage
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_searches_done_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_group_searches (
		[User] nvarchar(50)
		, [Search Date] datetime NULL
		, [Camera Numbers] nvarchar(64) NULL
		, [Date From] datetime NULL
		, [Date To] datetime NULL
		, [Footage Type] nvarchar(64) NULL
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_group_searches
		exec vigil_audit_get_searches_done @start_date, @end_date, @user, @event_type

		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_group_searches order by 2, 1
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_pos_query_by_group]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @group nvarchar(50)

	select @group = @user

	create table #tmp_group_pos_query (
		[User] nvarchar(50)
		, [Search Date] datetime NULL
		, [Date From] datetime NULL
		, [Date To] datetime NULL
		, [Item] nvarchar(64) NULL
		, [Code] nvarchar(64) NULL
		, [RegisterNo] nvarchar(64) NULL
		, [Cashier] nvarchar(64) NULL
		, [MinValue] nvarchar(64) NULL
		, [MaxValue] nvarchar(64) NULL
		, [Quantity] nvarchar(64) NULL
		, [Idx] nvarchar(64) NULL
		, [ReceiptNo] nvarchar(64) NULL
	)

	if len(@group) = 0
	begin
		declare tmp_user_cursor cursor local for
			select [name]
			from audit_users (nolock)
	end
	else
	begin
		declare tmp_user_cursor cursor local for
			select username
			from [VIGIL-DVR].[dbo].[usergroup] (nolock)
			where groupname = @group
	end

	open tmp_user_cursor

	fetch next from tmp_user_cursor into @user

	while @@fetch_status = 0
	begin
		insert into #tmp_group_pos_query
		exec vigil_audit_get_pos_query @start_date, @end_date, @user, @event_type

		fetch next from tmp_user_cursor into @user
	end

	close tmp_user_cursor
	deallocate tmp_user_cursor

	select * from #tmp_group_pos_query order by 2, 1
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_idle_time_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		'Idle Time' as title
		, 'IDLE_TIME_SUM 'as keyword
		, SUM(ISNULL(datediff(s, start_time, end_time),0)) / 60.0 as Total
	from dbo.fn_get_audit_session_info(
				@start_date
				, @end_date
				, @user
				, 'IdleSession'
				, @event_type)
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_time_logged_in_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @curr_date datetime

	select @curr_date = getdate()

	select
		'Time Logged In' as title
		, 'TIME_LOGGED_IN_SUM' as keyword
		, (COALESCE(SUM(ISNULL((case when 0 = datediff(s, start_time, end_time) then 1 else datediff(s, start_time, end_time) end), ISNULL(datediff(s, start_time, @curr_date), 0) )), 0)) / 60.0 as Total
	from dbo.fn_get_audit_login_sessions(
				@start_date
				, @end_date
				, @user
				, @event_type)
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_active_time_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @curr_date datetime

	SELECT @curr_date = getdate()

	SELECT 'Active Time' AS title
	, 'ACTIVE_TIME_SUM' AS keyword
	, dbo.fn_max_int((SELECT
			(
				SELECT COALESCE(SUM(ISNULL((case when 0 = datediff(s, start_time, end_time) then 1 else datediff(s, start_time, end_time) end) ,ISNULL(datediff(s, start_time, @curr_date), 0))), 0)
					FROM dbo.fn_get_audit_login_sessions(
						@start_date
						, @end_date
						, @user
						, @event_type)
			)
			-
			(
				SELECT COALESCE(SUM(datediff(s, start_time, end_time)), 0)
					FROM dbo.fn_get_audit_session_info(
						@start_date
						, @end_date
						, @user
						, 'IdleSession'
						, @event_type)
			)
		), 0) / 60.0  AS Total
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_footage_viewed_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select *
	into #tmp_footage_sum
	from dbo.fn_get_audit_session_info(
				@start_date
				, @end_date
				, @user
				, 'PlaybackSession'
				, @event_type)
	select
		convert(datetime, r3.data) as [CurrentPlayTimeStart]
		, convert(datetime, r6.data) as [CurrentPlayTimeStop]
		, a.start_time
		, a.end_time
	into #tmp_footage_par_sum
	from #tmp_footage_sum a
	left join (
		select i3.data, i3.[audit_details_id]
		from audit_details_info i3 (nolock)
		inner join audit_details_info_types it3 (nolock) on  it3.[idx] = i3.[data_type_id] and it3.[name]='CurrentPlayTime'
	) r3 on r3.[audit_details_id] = a.[detail_id_start]
	left join (
		select i6.data, i6.[audit_details_id]
		from audit_details_info i6 (nolock)
		inner join audit_details_info_types it6 (nolock) on  it6.[idx] = i6.[data_type_id] and it6.[name]='CurrentPlayTime'
	) r6 on r6.[audit_details_id] = a.[detail_id_end]
	select
		'Footage Viewed' as title
		, 'FOOTAGE_VIEWED_SUM' as keyword
		, SUM(ISNULL(datediff(s, start_time, end_time),0)) / 60.0 as Total_viewed_footage_min
	from #tmp_footage_par_sum
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_frame_viewed_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select *
	into #tmp_frame_sum
	from dbo.fn_get_audit_session_info(
				@start_date
				, @end_date
				, @user
				, 'PlaybackSession'
				, @event_type)
	select
		convert(int, r2.data) as [CurrentFrameStart]
		, convert(int,r5.data) as [CurrentFrameStop]
	into #tmp_frame_par_sum
	from #tmp_frame_sum a
	left join (
		select i2.data, i2.[audit_details_id]
		from audit_details_info i2 (nolock)
		inner join audit_details_info_types it2 (nolock) on  it2.[idx] = i2.[data_type_id] and it2.[name]='CurrentFrame'
	) r2 on r2.[audit_details_id] = a.[detail_id_start]
	left join (
		select i5.data, i5.[audit_details_id]
		from audit_details_info i5 (nolock)
		inner join audit_details_info_types it5 (nolock) on  it5.[idx] = i5.[data_type_id] and it5.[name]='CurrentFrame'
	) r5 on r5.[audit_details_id] = a.[detail_id_end]
	select
		'Frame Viewed' as title
		, 'FRAME_VIEWED_SUM' as keyword
		, SUM(ISNULL(abs([CurrentFrameStop] - [CurrentFrameStart]) + 1,0)) as Total_frames
	from #tmp_frame_par_sum
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_searches_done_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		'Searches Done' as title
		, 'SEARCHES_DONE_SUM' as keyword
		, count(*) as Total_searches
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'search'
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_pos_query_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		'POS Queries' as title
		, 'POS_QUERY_SUM' as keyword
		, COUNT(*) as Total_pos_queries
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'pos_query'
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_vpos_events_flagged_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	select
		'Events Flagged' as title
		, 'VPOS_EVENTS_FLAGGED_SUM' as keyword
		, count(*) as events_flagged
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'vpos_flag_event'
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_vpos_events_flagged_percent]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
begin
	set nocount on

	declare @vpos_alarm_start_date datetime
	declare @vpos_alarm_end_date datetime
	declare @vpos_alarm_count int
	declare @vpos_alarms_flagged int
	declare @vpos_alarms_flagged_percent float

	set @vpos_alarm_start_date = dateadd(day, -1, convert(datetime, convert(date, @start_date)))
	set @vpos_alarm_end_date = dateadd(second, 86399, @vpos_alarm_start_date)

	select @vpos_alarm_count = count(*) from [vigil-pos].dbo.alarm a
		join [vigil-pos].dbo.posdata p on a.pos_idx = p.idx
	where p.server_time between @vpos_alarm_start_date and @vpos_alarm_end_date

	set @vpos_alarms_flagged_percent = 0

	if @vpos_alarm_count > 0
	begin
		select @vpos_alarms_flagged = count(data) from
			(select data from audit_details_info di
				join audit_details d on di.audit_details_id = d.idx
				join audit_details_info_types dit on di.data_type_id = dit.idx
				join audit_events e on d.event_id = e.idx
				join audit_event_types et on e.type_id = et.idx
				join audit_users u on d.user_id = u.idx
			 where d.event_time >= @vpos_alarm_start_date
				and et.name = @event_type
				and e.name = 'vpos_flag_event'
				and u.name = @user
				and dit.name = 'VPOSAlarmIdx'
			) d
		where data in
			(select convert(nvarchar, a.alarm_idx) from [vigil-pos].dbo.alarm a
				join [vigil-pos].dbo.posdata p on a.pos_idx = p.idx
				where p.server_time between @vpos_alarm_start_date and @vpos_alarm_end_date
			)

		set @vpos_alarms_flagged_percent = convert(float, @vpos_alarms_flagged * 100) / @vpos_alarm_count
	end

	select 'Events Flagged Percent' as title
		, 'VPOS_EVENTS_FLAGGED_PERCENT' as keyword
		, @vpos_alarms_flagged_percent as events_flagged_percent
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_get_shift_analysis_tags_viewed_sum]
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
BEGIN
	set nocount on

	select
		'Tags Viewed' as title
		, 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM' as keyword
		, count(*) as tags_viewed
	from audit_details d (nolock)
	inner join audit_users u (nolock) on u.[idx] = d.[user_id]
	inner join audit_events e (nolock) on e.[idx] = d.event_id
	inner join audit_event_types et (nolock) on et.[idx] = e.type_id
	where d.event_time between @start_date and @end_date
		and u.[name] = @user
		and et.[name] = @event_type
		and e.[name] = 'sa_tag_view'
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_reports]
	@report nvarchar(50),
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = 'VIGIL'
AS
set nocount on

if @report = N'GET_USER_PERFORMANCE'
begin
	exec vigil_audit_get_user_performance @end_date, @user, @event_type
	return
end

if @report = N'GET_DAILY_USER_PERFORMANCE'
begin
	exec vigil_audit_get_daily_user_performance @start_date, @end_date, @user, @event_type
	return
end

if @report = N'GET_REPORTS_LIST'
begin
	exec vigil_audit_get_reports_list
	return
end

if @report = N'GET_REPORTS_COMBOLIST'
begin
	exec vigil_audit_get_reports_combolist
	return
end

if @report = N'GET_REPORT_CRITERIAS_ALL'
begin
	exec vigil_audit_get_report_criterias_all
	return
end

if @report = N'GET_REPORT_CRITERIAS'
begin
	exec vigil_audit_get_report_criterias @user
	return
end

if @report = N'GET_REPORT_CRITERIAS_BY_GROUP'
begin
	exec vigil_audit_get_report_criterias_by_group @user
	return
end

if @report = N'TIME_LOGGED_IN'
begin
	exec vigil_audit_get_time_logged_in @start_date, @end_date, @user, @event_type
	return
end

if @report = N'LOGGED_IN_FAILED'
begin
	exec vigil_audit_get_logged_in_failed @start_date, @end_date, @user, @event_type
	return
end

if @report = N'FOOTAGE_VIEWED'
begin
	exec vigil_audit_get_footage_viewed @start_date, @end_date, @user, @event_type
	return
end

if @report = N'SEARCHES_DONE'
begin
	exec vigil_audit_get_searches_done @start_date, @end_date, @user, @event_type
	return
end

if @report = N'POS_QUERY'
begin
	exec vigil_audit_get_pos_query @start_date, @end_date, @user, @event_type
	return
end

if @report = N'GET_SUMMARY_ALL'
begin
	exec vigil_audit_get_summary_all @start_date, @end_date, @user, @event_type
	return
end

if @report = N'TIME_LOGGED_IN_BY_GROUP'
begin
	exec vigil_audit_get_time_logged_in_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'LOGGED_IN_FAILED_BY_GROUP'
begin
	exec vigil_audit_get_logged_in_failed_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'IDLE_TIME_BY_GROUP'
begin
	exec vigil_audit_get_idle_time_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'IDLE_TIME_SUM_BY_GROUP'
begin
	exec vigil_audit_get_idle_time_sum_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'FOOTAGE_VIEWED_BY_GROUP'
begin
	exec vigil_audit_get_footage_viewed_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'SEARCHES_DONE_BY_GROUP'
begin
	exec vigil_audit_get_searches_done_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'POS_QUERY_BY_GROUP'
begin
	exec vigil_audit_get_pos_query_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'GET_SUMMARY_ALL_BY_GROUP'
begin
	exec vigil_audit_get_summary_all_by_group @start_date, @end_date, @user, @event_type
	return
end

if @report = N'GET_SUM_PERFORMANCE'
begin
	exec vigil_audit_get_sum_performance @start_date, @end_date, @user, @event_type
	return
end

if @report = N'DOES_USER_OR_GROUP_HAVE_CRITERIA'
begin
	exec vigil_audit_does_user_or_group_have_criteria @user
	return
end

declare @err_msg nvarchar(100)
select @err_msg = N'There is no report with keyword=''' + @report + N''''
raiserror(@err_msg,16,1)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_audit_reports_ext]
	@report nvarchar(30),
	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@user nvarchar(50) = NULL,
	@event_type nvarchar(30) = N'VIGIL'
AS
set nocount on

if @report = N'IDLE_TIME_SUM'
begin
	exec vigil_audit_get_idle_time_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'TIME_LOGGED_IN_SUM'
begin
	exec vigil_audit_get_time_logged_in_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'ACTIVE_TIME_SUM'
begin
	exec vigil_audit_get_active_time_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'FOOTAGE_VIEWED_SUM'
begin
	exec vigil_audit_get_footage_viewed_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'FRAME_VIEWED_SUM'
begin
	exec vigil_audit_get_frame_viewed_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'SEARCHES_DONE_SUM'
begin
	exec vigil_audit_get_searches_done_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'POS_QUERY_SUM'
begin
	exec vigil_audit_get_pos_query_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'VPOS_EVENTS_FLAGGED_SUM'
begin
	exec vigil_audit_get_vpos_events_flagged_sum @start_date, @end_date, @user, @event_type
	return
end

if @report = N'VPOS_EVENTS_FLAGGED_PERCENT'
begin
	exec vigil_audit_get_vpos_events_flagged_percent @start_date, @end_date, @user, @event_type
	return
end

if @report = N'SHIFT_ANALYSIS_TAGS_VIEWED_SUM'
begin
	exec vigil_audit_get_shift_analysis_tags_viewed_sum @start_date, @end_date, @user, @event_type
	return
end

declare @err_msg nvarchar(100)
select @err_msg = N'There is no report with keyword=''' + @report + N''''
raiserror(@err_msg,16,1)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_check_tables]
AS
BEGIN
	-- Perform simple query on each table in database.
	-- Output a list of tables for which queries failed.

	SET NOCOUNT ON 

	CREATE TABLE #suspect
	(
		suspect_table sysname
	)

	DECLARE @table sysname

	DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables
	OPEN table_cursor
	FETCH NEXT FROM table_cursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC('SELECT TOP 1 * INTO #tmp FROM [' + @table + ']')
		END TRY
		BEGIN CATCH
			INSERT INTO #suspect VALUES (@table)
		END CATCH

		FETCH NEXT FROM table_cursor INTO @table
	END

	CLOSE table_cursor
	DEALLOCATE table_cursor

	SELECT * FROM #suspect
END
GO
