USE [VIGIL-Audit]
GO

-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_time_str]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[get_time_str]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_is_report_in_ext_sp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_is_report_in_ext_sp]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_audit_session_info]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_audit_session_info]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_audit_perf_dates]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_audit_perf_dates]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_audit_login_sessions]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_audit_login_sessions]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_max_int]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_max_int]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_get_audit_login_sessions](
	@start_date datetime,
	@end_date datetime,
	@user nvarchar(50),
	@event_type nvarchar(30)
)
RETURNS @tmp_return table(
	[user_id] int,
	start_time datetime,
	end_time datetime,
	[key] nvarchar(64) COLLATE database_default
)
AS
BEGIN
	INSERT INTO @tmp_return([user_id], start_time, end_time, [key])
		SELECT
			[user_id],
			start_time,
			CASE WHEN actual_logoff_time IS NOT NULL AND end_time > actual_logoff_time THEN actual_logoff_time ELSE end_time END AS end_time,
			details.data
		FROM
			(
				(
					SELECT
						d.user_id,
						CASE WHEN min(d.event_time) < @start_date THEN @start_date ELSE min(d.event_time) END AS start_time,
						CASE WHEN max(d.event_time) > @end_date THEN @end_date ELSE max(d.event_time) END AS end_time,
						di.data
					FROM
						audit_details d
						JOIN audit_details_info di ON d.idx = di.audit_details_id
						JOIN audit_details_info_types dit ON di.data_type_id = dit.idx
						JOIN audit_users u ON d.user_id = u.idx
						JOIN audit_events e ON d.event_id = e.idx
						JOIN audit_event_types et ON e.type_id = et.idx
					WHERE
						u.name = @user
						AND et.name = @event_type
						AND dit.name = 'LoginSession'
					GROUP BY
						d.user_id,
						di.data
					HAVING
						min(d.event_time) <= @end_date
						AND max(d.event_time) >= @start_date
				) details

				LEFT JOIN

				(
					SELECT
						d.event_time AS actual_logoff_time,
						di.data
					FROM
						audit_details d
						JOIN audit_details_info di ON d.idx = di.audit_details_id
						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 et.idx = e.type_id
						JOIN audit_users u ON u.idx = d.user_id
					WHERE
						u.name = @user
						AND et.name = @event_type
						AND e.name = 'logoff'
						AND dit.name = 'LoginSession'
				) actual_logoffs
			
				ON actual_logoffs.data = details.data
			)

		DELETE FROM @tmp_return WHERE start_time > end_time

	RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_get_audit_perf_dates] (
	@curr_date datetime
)
RETURNS @tmp_return TABLE (
		[idx] int 
		, [name] nvarchar(30)
		, start_date datetime
		, end_date datetime )
AS
BEGIN
	declare @start_date datetime
		, @end_date datetime

	select @start_date = convert(datetime, convert(nvarchar(8), @curr_date,112) ,112)
	select @end_date = @start_date + 1
	insert into @tmp_return([idx],[name],start_date,end_date)
	values(1,'Daily',@start_date,@end_date)

	select @start_date = convert(datetime, convert(nvarchar(8),dateadd(d, -datepart(dw,@curr_date),@curr_date)+1,112) ,112)
	select @end_date = @start_date + 7
	insert into @tmp_return([idx],[name],start_date,end_date)
	values(2,'Weekly',@start_date,@end_date)

	select @start_date = convert(datetime, convert(nvarchar(6), @curr_date,112)+'01', 112)
	select @end_date = dateadd(m, 1, @start_date)
	insert into @tmp_return([idx],[name],start_date,end_date)
	values(3,'Monthly',@start_date,@end_date)
	
	return
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_get_audit_session_info](
	@start_date datetime,
	@end_date datetime,
	@user nvarchar(50),
	@session_type nvarchar(30),
	@event_type nvarchar(30)
)
RETURNS @tmp_return table(
	detail_id_start int NULL,
	detail_id_end int NULL,
	start_time datetime NULL,
	end_time datetime NULL,
	[user_id] int,
	session_id nvarchar(64) COLLATE DATABASE_DEFAULT,
	login_session_id nvarchar(64) COLLATE DATABASE_DEFAULT
)
AS
BEGIN
	INSERT INTO @tmp_return (detail_id_start, detail_id_end, start_time, end_time, [user_id], session_id, login_session_id)
		SELECT
			detail_id_start,
			detail_id_end,
			CASE WHEN start_time < @start_date THEN @start_date ELSE start_time END as start_time,
			CASE WHEN actual_logoff_time IS NOT NULL AND end_time > actual_logoff_time THEN actual_logoff_time ELSE end_time END AS end_time,
			[user_id],
			session_id,
			login_session_id
		FROM
			(
				SELECT
					detail_id_start,
					detail_id_end,
					CASE WHEN start_time < @start_date THEN @start_date ELSE start_time END as start_time,
					CASE WHEN end_time > @end_date THEN @end_date ELSE end_time END as end_time,
					[user_id],
					session_id,
					login_sessions.login_session_id,
					actual_logoff_time
				FROM
				(
					(
						SELECT
							min(d.idx) AS detail_id_start,
							max(d.idx) AS detail_id_end,
							min(d.event_time) AS start_time,
							max(d.event_time) AS end_time,
							u.idx as [user_id],
							di.data AS session_id
						FROM
							audit_details d
							JOIN audit_details_info di ON d.idx = di.audit_details_id
							JOIN audit_details_info_types dit ON dit.idx = di.data_type_id
							JOIN audit_users u on d.user_id = u.idx
							JOIN audit_events e on e.idx = d.event_id
							JOIN audit_event_types et on et.idx = e.type_id
						WHERE
							u.name = @user
							and et.name = @event_type
							and dit.name = @session_type
						GROUP BY
							u.idx,
							di.data
						HAVING
							min(d.event_time) <= @end_date
							AND max(d.event_time) >= @start_date
					) details

					JOIN

					(
						SELECT
							d.idx,
							di.data as login_session_id
						FROM
							audit_details d
							JOIN audit_details_info di ON d.idx = di.audit_details_id
							JOIN audit_details_info_types dit ON dit.idx = di.data_type_id
							JOIN audit_users u on d.user_id = u.idx
							JOIN audit_events e on e.idx = d.event_id
							JOIN audit_event_types et on et.idx = e.type_id
						WHERE
							u.name = @user
							and et.name = @event_type
							and dit.name = 'LoginSession'
					) login_sessions

					ON details.detail_id_start = login_sessions.idx

					LEFT JOIN

					(
						SELECT
							d.event_time AS actual_logoff_time,
							di.data as login_session_id
						FROM
							audit_details d
							JOIN audit_details_info di ON d.idx = di.audit_details_id
							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 et.idx = e.type_id
							JOIN audit_users u ON u.idx = d.user_id
						WHERE
							u.name = @user
							AND et.name = @event_type
							AND e.name = 'logoff'
							AND dit.name = 'LoginSession'
					) actual_logoffs

					ON login_sessions.login_session_id = actual_logoffs.login_session_id
				)
			) session_data


		DELETE FROM @tmp_return WHERE start_time > end_time

	RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_is_report_in_ext_sp]
	(@report nvarchar(30))
RETURNS bit
AS
BEGIN
	declare @return bit
	
	/* assume false */
	select @return =

		case @report
		when 'TIME_LOGGED_IN_SUM' then 1
		when 'FOOTAGE_VIEWED_SUM' then 1
		when 'FRAME_VIEWED_SUM' then 1
		when 'SEARCHES_DONE_SUM' then 1
		when 'POS_QUERY_SUM' then 1
		when 'IDLE_TIME_SUM' then 1
		when 'ACTIVE_TIME_SUM' then 1
		when 'VPOS_EVENTS_FLAGGED_SUM' then 1
		when 'VPOS_EVENTS_FLAGGED_PERCENT' then 1
		--when '' then 1
		
		else 0
	end

return @return
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[get_time_str]( @time int )
returns nvarchar(10)
as
begin
	declare @ret nvarchar(10)
	select @ret = convert(nvarchar(10),@time/3600), @time = @time%3600
	if len(@ret)=1
		set @ret='0'+@ret
	set @ret = @ret + ':' + right('0'+convert(nvarchar(10),@time/60),2) + ':' + right('0' + convert(nvarchar(10),@time%60),2)

	return(@ret)
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_max_int] (
@a bigint,
@b bigint
)
RETURNS bigint
AS
BEGIN
	IF @a >= @b RETURN @a
	RETURN @b
END
GO
