USE [VIGIL-POS]
GO


-- DROP

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_parse_tlog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_parse_tlog]
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_posdata]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_sync_posdata]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_alarm_count]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_alarm_count]
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_delete_alarm]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_alarm]
GO

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

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

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

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

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

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_daily_cashier_transactions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_daily_cashier_transactions]
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

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

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

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

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

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

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

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


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_add_alarm_flag]
@idx int,
@name nvarchar(20),
@color int = 0,
@email_enabled bit = 0,
@email_recipients nvarchar(1000) = NULL
AS
BEGIN
	INSERT INTO AlarmFlag (idx, name, color, email_enabled, email_recipients)
	VALUES (@idx, @name, @color, @email_enabled, @email_recipients)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_alarm_flag_usage]
@idx as bigint
AS
BEGIN
	SELECT * from alarm where alarm_flag_idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_clear_alarm_flags]
AS
BEGIN
	DELETE FROM AlarmFlag
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_clear_ignoretext]
AS
	DELETE FROM posignoretext
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_alarm]
@idx bigint
AS
	DELETE FROM alarm where alarm_idx = @idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_shift_analysis_tag_info]
AS
BEGIN
	DELETE s
	FROM shift_analysis_tag_info s LEFT JOIN posdata p ON s.pos_idx = p.idx
	WHERE p.idx IS NULL
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_prune_scheduled_report_log]
@max_delete_count int = 10000
as
begin
	delete top(@max_delete_count)
	from ScheduledReportLog
	where srl_idx not in
	(
		select max(srl_idx) as srl_idx
		from ScheduledReportLog
		where exception_idx in (select exception_idx from Exception where email_enabled = 1)
		group by exception_idx
	)
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_purge_posdata]
@startTime as DATETIME = NULL,
@endTime as DATETIME = NULL
AS
BEGIN
	SET NOCOUNT ON

	if @startTime IS NULL AND @endTime IS NULL
		DELETE FROM POSData
	ELSE IF NOT @startTime IS NULL AND @endTime IS NULL
		DELETE FROM POSData WHERE server_time > @startTime
	ELSE IF @startTime IS NULL AND NOT @endTime IS NULL
		DELETE FROM POSData WHERE server_time < @endTime
	ELSE
		DELETE FROM POSData WHERE server_time >= @startTime AND server_time <= @endTime

	EXEC sp_prune_shift_analysis_tag_info
END
GO

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

	declare @countPos int

	select @countPos = count(*) from posdata where server_time < '1900-01-01'

	if @countPos > 0
		EXECUTE sp_purge_posdata NULL, '1900-01-01'

	select 'posdata' as [table], @countPos as invalid_record_count
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_alarm_count]
@showall int = 0,
@filter as nvarchar(2048) = N''
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @sql AS NVARCHAR(4000)
	DECLARE @ackCondition AS NVARCHAR(50)

	SET @sql  = N'
	SELECT
		COUNT(*) as alarmcount
	FROM
		Alarm a
		JOIN POSData pd on a.pos_idx = pd.idx
		LEFT JOIN AlarmFlag F on a.alarm_flag_idx = F.idx
		LEFT JOIN Exception e on a.exception_idx = e.exception_idx
	WHERE
		a.va_checked = 1
		AND (a.exception_idx = 0 or e.exception_idx is not null)
		AND ACKNOWLEDGE_CONDITION
		AND IDX_CONDITION'

	Set @filter = N'a.alarm_idx > 0 ' + @filter
	SET @sql = REPLACE(@sql, N'IDX_CONDITION', @filter)

	SET @ackCondition = N' (1 <= ' + CAST(@showall as nvarchar(50)) + N' OR a.visible = 1)'
	SET @sql = REPLACE(@sql, N'ACKNOWLEDGE_CONDITION', @ackCondition)

	EXECUTE(@sql)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_alarm_flags]
AS
BEGIN
	SELECT * FROM AlarmFlag ORDER BY IDX
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_alarm_info]
@pos_idx bigint = 0,
@alarm_idx bigint = 0,
@receipt_no nvarchar(20) = N''
AS
BEGIN
	IF @pos_idx > 0
		SELECT a.alarm_idx, a.pos_idx, e.exception_description, f.name as alarm_flag_name, a.notes, a.acknowledged
		FROM Alarm a
		LEFT JOIN Exception e ON a.exception_idx = e.exception_idx
		LEFT JOIN AlarmFlag f ON a.alarm_flag_idx = f.idx
		WHERE pos_idx = @pos_idx
	ELSE
		IF len(@receipt_no) > 0
			IF Exists(SELECT count(*) FROM Alarm a LEFT OUTER JOIN Exception e ON a.exception_idx = e.exception_idx WHERE pos_idx IN (SELECT idx FROM posdata WHERE receipt_no = @receipt_no))
				SELECT a.alarm_idx, a.pos_idx, e.exception_description, f.name as alarm_flag_name, a.notes, a.acknowledged
				FROM Alarm a
				LEFT OUTER JOIN Exception e ON a.exception_idx = e.exception_idx
				LEFT JOIN AlarmFlag f ON a.alarm_flag_idx = f.idx
				WHERE pos_idx IN (SELECT idx FROM posdata WHERE receipt_no = @receipt_no)
			ELSE
				SELECT 0 as alarm_idx, idx as pos_idx, '' as exception_description FROM posdata WHERE receipt_no = @receipt_no
		ELSE
			IF @alarm_idx > 0
				SELECT a.alarm_idx, a.pos_idx, e.exception_description, f.name as alarm_flag_name, a.notes, a.acknowledged
				FROM Alarm a
				LEFT OUTER JOIN Exception e ON a.exception_idx = e.exception_idx
				LEFT JOIN AlarmFlag f ON a.alarm_flag_idx = f.idx
				WHERE alarm_idx = @alarm_idx
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_alarms]
@showall int = 0,
@row_count int = 5000,
@last_idx int = 0,
@filter nvarchar(2048) = N'',
@offset_records int = 0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @sql AS NVARCHAR(4000)
	DECLARE @ackCondition AS NVARCHAR(50)

	SET @sql  = N'
		SELECT  a.alarm_idx, a.exception_idx, a.acknowledged, a.visible, a.pos_idx, a.va_checked,
			ISNULL(f.name + space(20 - len(f.name)), space(20)) as flag,
			a.notes + space(256 - len(a.notes)) as notes,
			e.exception_description,
			pd.idx,
			pd.receipt_no,
			pd.register_time,
			pd.server_time,
			pd.item,
			pd.value_char,
			pd.value_num,
			pd.sequence_num,
			pd.register_num,
			pd.terminal_num,
			pd.void,
			pd.nosale,
			pd.quantity,
			pd.cashier
		FROM
			Alarm a
			JOIN POSData pd on a.pos_idx = pd.idx
			LEFT JOIN AlarmFlag F on a.alarm_flag_idx = F.idx
			LEFT JOIN Exception e on a.exception_idx = e.exception_idx
		WHERE
			a.va_checked = 1
			AND (a.exception_idx = 0 or e.exception_idx is not null)
			AND ACKNOWLEDGE_CONDITION
			AND IDX_CONDITION
		ORDER BY pd.server_time DESC
		OFFSET ' + CAST(@offset_records as nvarchar) + ' ROWS
		FETCH NEXT ' + CAST(@row_count as nvarchar) + ' ROWS ONLY'

	SET @filter = N'a.alarm_idx > ' + CAST(@last_idx as nvarchar(50)) + N' ' + @filter
	SET @sql = REPLACE(@sql, N'IDX_CONDITION', @filter)

	SET @ackCondition = N' (1 <= ' + CAST(@showall as nvarchar(50)) + N' OR a.visible = 1)'
	SET @sql = REPLACE(@sql, N'ACKNOWLEDGE_CONDITION', @ackCondition)

	EXECUTE(@sql)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_cashier_alarm_counts]
@startTime as DateTime,
@endTime as DateTime
AS
BEGIN
SELECT a.cashier, a.exception_description, a.exception_idx, coalesce(b.[count], 0) AS [count] FROM
	(SELECT distinct p.cashier, e.exception_description, e.exception_idx, e.[rank] FROM posdata p CROSS JOIN Exception e WHERE e.alert_enabled = 1) a
	LEFT JOIN
	(SELECT p.cashier, e.exception_idx, count(*) AS [count]
		FROM Alarm a join Exception e ON a.exception_idx = e.exception_idx
		JOIN POSData p ON a.pos_idx = p.idx
		WHERE p.server_time BETWEEN @startTime AND @endTime
		GROUP BY p.cashier, e.exception_idx
	) b
ON a.cashier = b.cashier AND a.exception_idx = b.exception_idx
ORDER BY cashier, [rank], exception_description
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_cashier_alarm_report]
@startTime datetime,
@endTime datetime,
@cashier nvarchar(50) = NULL,
@exceptionDescription nvarchar(50) = NULL,
@offset int = 0,
@fetch int = 2147483647,
@countOnly bit = 0,
@listDelimiter nvarchar(10) = ';'
AS
	IF @countOnly = 1
	BEGIN
		SELECT count(*) as [count]
		FROM Alarm a JOIN Exception e ON a.exception_idx = e.exception_idx
		JOIN POSData p ON a.pos_idx = p.idx
		LEFT JOIN AlarmFlag af on a.alarm_flag_idx = af.idx
		WHERE e.alert_enabled = 1
		AND p.server_time BETWEEN @startTime and @endTime
		AND p.cashier IS NOT NULL AND p.cashier  <> ''
		AND (@cashier IS NOT NULL AND p.cashier IN (SELECT * FROM dbo.Split(@cashier, @listDelimiter)) OR @cashier IS NULL)
		AND (@exceptionDescription IS NOT NULL AND e.exception_description IN (SELECT * FROM dbo.Split(@exceptionDescription, @listDelimiter)) OR @exceptionDescription IS NULL)
	END
	ELSE
	BEGIN
		SELECT a.alarm_idx, e.exception_description, p.cashier, p.register_num, p.server_time, af.name AS flag_name, af.color AS flag_color, a.acknowledged, a.pos_idx, a.notes, p.item, p.receipt_no
		FROM Alarm a JOIN Exception e ON a.exception_idx = e.exception_idx
		JOIN POSData p ON a.pos_idx = p.idx
		LEFT JOIN AlarmFlag af on a.alarm_flag_idx = af.idx
		WHERE e.alert_enabled = 1
		AND p.server_time BETWEEN @startTime and @endTime
		AND p.cashier IS NOT NULL AND p.cashier  <> ''
		AND (@cashier IS NOT NULL AND p.cashier IN (SELECT * FROM dbo.Split(@cashier, @listDelimiter)) OR @cashier IS NULL)
		AND (@exceptionDescription IS NOT NULL AND e.exception_description IN (SELECT * FROM dbo.Split(@exceptionDescription, @listDelimiter)) OR @exceptionDescription IS NULL)
		ORDER BY a.alarm_idx
		OFFSET @offset ROWS
		FETCH NEXT @fetch ROWS ONLY
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_data_date_range]
AS
BEGIN
	SELECT min(server_time) AS start_time, max(server_time) AS end_time FROM posdata
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_email_addresses]
@exception_idx int
AS
	SELECT exception_idx, email_address, COALESCE(email_name, '') as email_name, recipient_type, enabled from ExceptionRecipient WHERE exception_idx = @exception_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_exception_count]
@starttime nvarchar(100),
@endtime nvarchar(100),
@exception_idx int
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @SQLString nvarchar(4000)

	SELECT @where_clause = (SELECT where_clause from Exception WHERE exception_idx = @exception_idx)

	SELECT @where_clause = replace(@where_clause, '@start_time', '''' + convert(nvarchar(30),@starttime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@end_time', '''' + convert(nvarchar(30), @endtime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@idx', '0')

	SELECT @SQLString = N'SELECT COUNT(*) AS report_count FROM POSData WITH (INDEX(ix_posdata_server_time)) WHERE ' + CAST(@where_clause AS nvarchar(1000)) + N' AND server_time >= ''' + @starttime + N''' AND server_time < ''' + @endtime + N''''

	EXEC sp_executesql @SQLString

	RETURN @@ROWCOUNT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_exception_report]
@starttime nvarchar(100),
@endtime nvarchar(100),
@exception_idx int,
@max_records int = NULL,
@orderAscending bit = 0
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @SQLString nvarchar(4000)
	DECLARE @order nvarchar(4)

	IF @orderAscending = 1
		SELECT @order = 'ASC'
	ELSE
		SELECT @order = 'DESC'

	SELECT @where_clause = (SELECT where_clause FROM Exception WHERE exception_idx = @exception_idx)

	SELECT @where_clause = replace(@where_clause, '@start_time', '''' + convert(nvarchar(30),@starttime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@end_time', '''' + convert(nvarchar(30), @endtime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@idx', '0')

	SELECT @SQLString = N'SELECT ' + COALESCE(N'TOP ' + CAST(@max_records AS nvarchar(20)), N'') + ' * FROM POSData WITH (INDEX(ix_posdata_server_time)) WHERE ' + CAST(@where_clause AS nvarchar(1000)) + N' AND server_time >= ''' + @starttime + N''' AND server_time < ''' + @endtime + N''' ORDER BY server_time ' + @order + ', idx ' + @order

	EXEC sp_executesql @SQLString

	RETURN @@ROWCOUNT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_exception_report_sql]
@starttime nvarchar(100),
@endtime nvarchar(100),
@exception_idx int,
@max_records int = NULL,
@orderAscending bit = 0
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @SQLString nvarchar(4000)
	DECLARE @order nvarchar(4)

	IF @orderAscending = 1
		SELECT @order = 'ASC'
	ELSE
		SELECT @order = 'DESC'

	SELECT @where_clause = (SELECT where_clause FROM Exception WHERE exception_idx = @exception_idx)

	SELECT @where_clause = replace(@where_clause, '@start_time', '''' + convert(nvarchar(30),@starttime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@end_time', '''' + convert(nvarchar(30), @endtime, 23) + '''')
	SELECT @where_clause = replace(@where_clause, '@idx', '0')

	SELECT @SQLString = N'SELECT ' + COALESCE(N'TOP ' + CAST(@max_records AS nvarchar(20)), N'') + ' * FROM POSData WITH (INDEX(ix_posdata_server_time)) WHERE ' + CAST(@where_clause AS nvarchar(1000)) + N' AND server_time >= ''' + @starttime + N''' AND server_time < ''' + @endtime + N''' ORDER BY server_time ' + @order + ', idx ' + @order

	SELECT @SQLString AS Insert_clause

	RETURN @@ROWCOUNT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_exception_va_filter]
@exception_idx int
AS
BEGIN
	SELECT va_enabled, va_camera, va_rulename, va_compare, va_value FROM Exception WHERE exception_idx = @exception_idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_export_pos_data_scan_index]
@starttime datetime,
@endtime datetime,
@pos_reg_term_map nvarchar(200), --in format: "reg:terminal,reg:termimal" such as "1:1,1:2", "1:1"
@scan_interval_min int, -- The Scan duration in minutes
@delimeter_map nvarchar(1) = ',',
@delimeter_reg_term nvarchar(1) = ':'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @delimeter_pos int,
            @pos_reg int,
	        @pos_term int,
		    @pos_reg_term_pair_item nvarchar(16),
	        @tmp_reg_txt nvarchar(8),
		    @tmp_term_txt nvarchar(8),
		    @reg_term_delimeter_pos int,
		    @current_scan_start_time datetime,
		    @current_scan_end_time datetime

	CREATE TABLE #pos_reg_term_table(register_num int, terminal_num int)

	SET @delimeter_pos = CHARINDEX(@delimeter_map, @pos_reg_term_map, 0)
	WHILE @delimeter_pos > 0
	BEGIN
		SET @pos_reg_term_pair_item = LTRIM(RTRIM(SUBSTRING(@pos_reg_term_map, 1, @delimeter_pos - 1)))
		SET @reg_term_delimeter_pos = CHARINDEX(@delimeter_reg_term, @pos_reg_term_pair_item, 0)
		IF @reg_term_delimeter_pos > 0
		BEGIN
			SET @tmp_reg_txt = LTRIM(RTRIM(SUBSTRING(@pos_reg_term_pair_item, 1, @reg_term_delimeter_pos - 1)))
			SET @tmp_term_txt = LTRIM(RTRIM(SUBSTRING(@pos_reg_term_pair_item, @reg_term_delimeter_pos + 1, LEN(@pos_reg_term_pair_item) - @reg_term_delimeter_pos)))

			IF ISNUMERIC(@tmp_reg_txt) = 1 AND ISNUMERIC(@tmp_term_txt) = 1
			BEGIN
				SET @pos_reg = cast( cast(@tmp_reg_txt as numeric) as int)
				SET @pos_term = cast( cast(@tmp_term_txt as numeric) as int)
				INSERT INTO #pos_reg_term_table VALUES(@pos_reg, @pos_term)
			END
		END

		--Prepare for next loop
		SET @pos_reg_term_map = SUBSTRING(@pos_reg_term_map, @delimeter_pos + 1, LEN(@pos_reg_term_map) - @delimeter_pos)
		SET @delimeter_pos = CHARINDEX(@delimeter_map, @pos_reg_term_map, 0)
	END

	IF @delimeter_pos = 0 -- only one reg:term pair item, example: 1:1
	BEGIN
        SET @reg_term_delimeter_pos = CHARINDEX(@delimeter_reg_term, @pos_reg_term_map, 0) --pos is 1 based
		SET @tmp_reg_txt = LTRIM(RTRIM(SUBSTRING(@pos_reg_term_map, 1, @reg_term_delimeter_pos - 1)))
		SET @tmp_term_txt = LTRIM(RTRIM(SUBSTRING(@pos_reg_term_map, @reg_term_delimeter_pos + 1, LEN(@pos_reg_term_map) - @reg_term_delimeter_pos)))

		IF ISNUMERIC(@tmp_reg_txt) = 1 AND ISNUMERIC(@tmp_term_txt) = 1
		BEGIN
			SET @pos_reg = cast( cast(@tmp_reg_txt as numeric) as int)
			SET @pos_term = cast( cast(@tmp_term_txt as numeric) as int)
			INSERT INTO #pos_reg_term_table VALUES(@pos_reg, @pos_term)
		END
	END

	SET @current_scan_start_time = @starttime
	SET @current_scan_end_time = DATEADD(minute, @scan_interval_min, @current_scan_start_time)

	create table #pos_scan_tmp(start_time datetime,
			                   end_time datetime,
				               register_num int,
			                   terminal_num int,
                               interval_count int)

	WHILE (@current_scan_start_time <= @endtime AND @current_scan_end_time >= @current_scan_start_time)
	BEGIN
		INSERT INTO #pos_scan_tmp(start_time, end_time, register_num, terminal_num, interval_count)
        SELECT @current_scan_start_time AS start_time, @current_scan_end_time AS end_time, v.register_num as register_num, v.terminal_num as terminal_num, COUNT(*) as interval_count
		FROM vw_posdata v inner join #pos_reg_term_table p on (v.register_num = p.register_num and v.terminal_num = p.terminal_num)
		WHERE v.server_time >= @current_scan_start_time
		AND v.server_time < @current_scan_end_time
		GROUP BY v.register_num, v.terminal_num

		-- prepare for next loop
		SET @current_scan_start_time = @current_scan_end_time
		SET @current_scan_end_time = DATEADD(minute, @scan_interval_min, @current_scan_start_time)
	END

	SELECT * from #pos_scan_tmp
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_global_setting]
@fieldName as NVARCHAR(50)
AS
	SELECT field_value
	FROM [GlobalSettings]
	WHERE field_name = @fieldName
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_ignoretext]
AS
	SELECT (SELECT COUNT(idx) FROM posignoretext) AS record_count, ignore_field, ignore_text
	FROM posignoretext
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_last_inserted_posdata_idx]
@NewVal bigint,
@OldVal bigint OUTPUT
AS
BEGIN TRANSACTION
	SELECT @OldVal = MAX(idx) FROM last_inserted_posdata_idx WITH (TABLOCKX)

	UPDATE last_inserted_posdata_idx
	SET idx = @NewVal
COMMIT TRANSACTION
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_latest_posdata]
@LastIdx bigint = 0
AS
	IF @LastIdx = 0
		BEGIN
			Declare @default_latest_dt Datetime
			set @default_latest_dt = DATEADD(minute, -2, GETDATE())
			DECLARE @max_idx bigint
			SELECT @max_idx = max(idx) FROM vw_posdata WITH (NOLOCK)
			SELECT *  FROM vw_posdata WITH (NOLOCK)
			WHERE idx > @max_idx - 10 and server_time >= @default_latest_dt
			ORDER BY idx
		END
	ELSE
		BEGIN
			SELECT * FROM vw_posdata WITH (NOLOCK) WHERE idx > @LastIdx ORDER BY idx
		END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_posdata_from_idx]
@idx as BIGINT,
@register_num as INT,
@terminal_num as INT,
@receipt_no as nvarchar(20),
@server_time as DATETIME
AS
BEGIN
	SELECT * FROM posdata WHERE receipt_no = @receipt_no
		AND register_num = @register_num
		AND terminal_num = @terminal_num
		AND idx > @idx - 200 AND idx < @idx + 200
		AND ABS(DATEDIFF(minute, server_time, @server_time)) < 60
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_report]
@sql_query nvarchar(3072),
@row_count int = 5000,
@offset_records bigint = 0
AS
BEGIN
	-- This proc originally contained extensive code to support paginated results.
	-- We now use pagination features native to SQL Server.
	-- This proc still exists to serve older VIGIL Client queries, but has been rewritten to use SQL Server pagination.

	SET NOCOUNT ON

	DECLARE @sql AS NVARCHAR(4000)

	SET @sql = REPLACE(@sql_query, ' TOP 50001', '')
	SET @sql = @sql + ' OFFSET ' + CONVERT(nvarchar, @offset_records) + ' ROWS FETCH NEXT ' + CONVERT(nvarchar, @row_count) + ' ROWS ONLY'

	EXEC sp_executesql @sql

	SET ROWCOUNT 0
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_scheduled_exceptions]
AS
	SELECT e1.*,
	(SELECT max(srl.report_timestamp)
		FROM Exception e2 LEFT JOIN ScheduledReportLog srl
		ON (e2.exception_idx = srl.exception_idx)
		WHERE e1.exception_idx = e2.exception_idx
	) AS report_timestamp
	FROM Exception e1
	WHERE e1.email_enabled = 1

	RETURN @@ROWCOUNT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_tmp_posdata_size]
AS
	SELECT COUNT(idx) AS record_count
	FROM tmp_posdata WITH (NOLOCK)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_un_checked_alarms]
AS
BEGIN
	DECLARE @dtCutOff as DATETIME
	SET @dtCutOff = DATEADD(second, -90, getdate())
	SELECT  a.alarm_idx, a.exception_idx, pd.server_time
	FROM
		POSData pd,
		Alarm a
	WHERE
	 	a.pos_idx = pd.idx AND
		a.va_checked = 0 AND
		pd.server_time <= @dtCutoff
	ORDER BY a.alarm_idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_email_recipients]
@exception_idx int,
@email_recipients nvarchar(4000),
@recipient_type int,
@enabled bit
AS
	DECLARE @recipient_delim INT
	DECLARE @name_delim INT
	DECLARE @start_pos INT
	DECLARE @current_address NVARCHAR(MAX)
	DECLARE @current_name NVARCHAR(MAX)

	IF @email_recipients IS NOT NULL AND @email_recipients != '' AND @exception_idx > 0
	BEGIN
		SET @start_pos = 0

		WHILE (@start_pos < LEN(@email_recipients))
		BEGIN
			SET @name_delim = CHARINDEX('#', @email_recipients, @start_pos)
			SET @recipient_delim = CHARINDEX('|', @email_recipients, @start_pos)

			IF @recipient_delim = 0 SET @recipient_delim = LEN(@email_recipients) + 1

			SET @current_address = SUBSTRING(@email_recipients, @start_pos, @name_delim - @start_pos)
			SET @current_name = SUBSTRING(@email_recipients, @name_delim + 1, @recipient_delim - @name_delim - 1)

			INSERT INTO ExceptionRecipient (exception_idx, email_address, email_name, recipient_type, [enabled]) VALUES (@exception_idx, @current_address, @current_name, @recipient_type, @enabled)

			EXEC [Vigil-DVR].[dbo].[sp_insert_email_notification] @current_address, @recipient_type, 5, @enabled, -1, -1, @exception_idx, -1
			EXEC [Vigil-DVR].[dbo].[sp_insert_email] @current_address

			SET @start_pos = @recipient_delim + 1
		END
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_exceptions]
@exception_idx int,
@exception_guid nvarchar(50),
@exception_description nvarchar(50),
@item nvarchar(50) = NULL,
@value_num money,
@value_num_max money,
@compare_type int,
@quantity smallmoney = NULL,
@quantity_compare_type int,
@register_num int = NULL,
@cashier nvarchar(50) = NULL,
@value_char nvarchar(20) = NULL,
@whole_dollar_amounts bit,
@void_nosale_transactions bit,
@start_hour int = NULL,
@end_hour int = NULL,
@alert_enabled bit = 1,
@email_enabled bit,
@email_is_html bit,
@email_period int,
@email_period_type int,
@email_specific_day_enabled bit,
@email_specific_day int,
@email_specific_hour_enabled bit,
@email_specific_hour int,
@email_exception_threshold int,
@filter_conjunction_AND as bit,
@show_exception_in_list as bit,
@va_enabled bit,
@va_camera int,
@va_rulename nvarchar(30),
@va_compare int,
@va_value int,
@item_case_sensitive int,
@email_recipients nvarchar(4000) = NULL,
@terminal_num int = NULL,
@realtional_operator_item nvarchar(2000) = NULL,
@email_recipients_cc nvarchar(4000) = NULL,
@email_recipients_bcc nvarchar(4000) = NULL,
@email_recipients_disabled nvarchar(4000) = NULL,
@email_recipients_cc_disabled nvarchar(4000) = NULL,
@email_recipients_bcc_disabled nvarchar(4000) = NULL,
@item2 nvarchar(50) = NULL,
@item2_in_next_receipt bit = NULL,
@rank int = 0,
@trigger_server_alarm bit = 0,
@start_minute int = 0,
@end_minute int = 0,
@start_second int = 0,
@end_second int = 0,
@alarm_priority int = 1
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @where_item nvarchar(1000)
	DECLARE @field_name nvarchar(100)
	DECLARE @conjunction nvarchar(5)
	DECLARE @start_time time
	DECLARE @end_time time

	SET NOCOUNT ON

	IF ISNULL(@exception_guid, '') = ''
		SET @exception_guid = newid()

	IF @filter_conjunction_AND= 1
		SET @conjunction = ' AND '
	ELSE
		SET @conjunction = ' OR '

	SET @where_clause = ''

	IF @item IS NOT NULL AND @item != ''
	BEGIN
		SELECT @where_item = ''

		IF @item_case_sensitive = 1
			SELECT @field_name='item COLLATE Latin1_General_CS_AS '
		ELSE
			SELECT @field_name='item '

		IF @realtional_operator_item IS NOT NULL AND @realtional_operator_item != ''
			SET @where_clause = @where_clause + @realtional_operator_item + ' ' + @conjunction
		ELSE
		BEGIN
			SELECT @where_item = @where_item + ' ' + @field_name +
				case
					when left(value,5) = '[NOT]' then ' NOT Like N''%' + ltrim(substring(value,6,len(value)))+'%'''
					else ' Like N''%' + value + '%'''
				end
				+ ' ' + @conjunction
			from dbo.Split(@item, ',')
			SET @where_clause = @where_clause + @where_item
		END
	END

	IF @register_num IS NOT NULL AND @register_num != 0
		SET @where_clause = @where_clause + 'register_num = ' + CAST(@register_num as nvarchar) + @conjunction

	IF @terminal_num IS NOT NULL AND @terminal_num != 0
		SET @where_clause = @where_clause + 'terminal_num = ' + CAST(@terminal_num as nvarchar) + @conjunction

	IF @cashier IS NOT NULL AND @cashier != ''
		SET @where_clause = @where_clause + 'cashier LIKE N''%' + replace(replace(@cashier, '''', ''''''), '[', '[[]') + '%''' + @conjunction

	IF @value_char IS NOT NULL AND @value_char != ''
		SET @where_clause = @where_clause + 'value_char = N''' + replace(replace(@value_char, '''', ''''''), '[', '[[]') + '''' + @conjunction

	IF @compare_type = 1
		SET @where_clause = @where_clause + 'value_num >= ' + CAST(@value_num as nvarchar) + @conjunction
	Else IF @compare_type = 2
		SET @where_clause = @where_clause + 'value_num = ' + CAST(@value_num as nvarchar) + @conjunction
	Else IF @compare_type = 3
		SET @where_clause = @where_clause + 'value_num <= ' + CAST(@value_num as nvarchar) + @conjunction
	Else IF @compare_type = 4
		SET @where_clause = @where_clause + 'value_num > ' + CAST(@value_num as nvarchar) + @conjunction
	Else IF @compare_type = 5
		SET @where_clause = @where_clause + 'value_num < ' + CAST(@value_num as nvarchar) + @conjunction
	Else IF @compare_type = 6
		SET @where_clause = @where_clause + '(value_num >= ' + CAST(@value_num as nvarchar) + ' AND value_num <= ' + CAST(@value_num_max as nvarchar) + ') ' + @conjunction

	IF @quantity_compare_type = 1
		SET @where_clause = @where_clause + 'quantity >= ' + CAST(@quantity as nvarchar) + @conjunction
	Else IF @quantity_compare_type = 2
		SET @where_clause = @where_clause + 'quantity = ' + CAST(@quantity as nvarchar) + @conjunction
	Else IF @quantity_compare_type = 3
		SET @where_clause = @where_clause + 'quantity <= ' + CAST(@quantity as nvarchar) + @conjunction
	Else IF @quantity_compare_type = 4
		SET @where_clause = @where_clause + 'quantity > ' + CAST(@quantity as nvarchar) + @conjunction
	Else IF @quantity_compare_type = 5
		SET @where_clause = @where_clause + 'quantity < ' + CAST(@quantity as nvarchar) + @conjunction

	IF @whole_dollar_amounts = 1
		SET @where_clause = @where_clause + 'value_num =  FLOOR(value_num)' + @conjunction

	IF @void_nosale_transactions = 1
		SET @where_clause = @where_clause + 'receipt_no IN (SELECT receipt_no FROM fn_nosale_after_void(@start_time, @end_time, ' + CAST(@register_num as nvarchar) + ', @idx) GROUP BY receipt_no)' + @conjunction

	IF LEN(@where_clause) > 0
		SET @where_clause = '(' + LEFT(@where_clause, LEN(@where_clause) - LEN(@conjunction)) + ')'
	ELSE
		SET @where_clause = '1 = 1'


	IF @start_hour IS NOT NULL AND @end_hour IS NOT NULL
	BEGIN
		IF LEN(@where_clause) > 0
		BEGIN
			SET @where_clause = @where_clause + ' AND '
		END

		SET @start_time = TIMEFROMPARTS(@start_hour, @start_minute, @start_second, 0, 0)
		SET @end_time = TIMEFROMPARTS(@end_hour, @end_minute, @end_second, 0, 0)

		IF @start_time > @end_time
		BEGIN
			SET @where_clause = @where_clause + '(CAST(server_time as time) BETWEEN ''' + CAST(@start_time as nvarchar) + ''' AND ''23:59:59.9999999'' OR CAST(server_time as time) BETWEEN ''00:00:00.0000000'' AND ''' + CAST(@end_time as nvarchar) + ''')'
		END
		ELSE
		BEGIN
			SET @where_clause = @where_clause + '(CAST(server_time as time) BETWEEN ''' + CAST(@start_time as nvarchar) + ''' AND ''' + CAST(@end_time as nvarchar) + ''')'
		END
	END

	IF @item2 IS NOT NULL AND @item2 != ''
	BEGIN
		IF @item_case_sensitive = 1
			SELECT @field_name='p1.item COLLATE Latin1_General_CS_AS'
		ELSE
			SELECT @field_name='p1.item'

		IF @item2_in_next_receipt = 1
		BEGIN
			SET @where_clause = '(idx IN (SELECT p1.idx FROM posdata p1 JOIN (SELECT * FROM posdata WHERE ' + @where_clause + ') p2'
				+ ' ON p1.terminal_num = p2.terminal_num  AND p1.register_num = p2.register_num'
				+ ' AND p2.receipt_no = dbo.fn_get_previous_receipt_no(p1.receipt_no, p1.terminal_num, p1.register_num, p1.server_time)'
				+ ' AND abs(datediff(minute, p1.server_time, p2.server_time)) <= 60'
				+ ' WHERE ' + @field_name + ' LIKE ''%' + @item2 + '%''))'
		END
		ELSE
		BEGIN
			SET @where_clause = '(idx IN (SELECT MAX(idx) FROM posdata, (SELECT p1.idx AS idx1, p2.idx AS idx2 FROM'
				+ ' (SELECT * FROM posdata WHERE '+ @where_clause + ') p1 JOIN (SELECT * FROM posdata p1 WHERE ' + @field_name + ' LIKE ''%' + @item2 + '%'') p2'
				+ ' ON p1.terminal_num = p2.terminal_num AND p1.register_num = p2.register_num AND p1.receipt_no = p2.receipt_no AND abs(datediff(minute, p1.server_time, p2.server_time)) <= 60) p1jp2'
				+ ' WHERE idx1 <> idx2 AND idx IN (idx1, idx2) GROUP BY receipt_no))'
		END
	END

	SET @exception_idx = 0
	SELECT @exception_idx = exception_idx FROM Exception WHERE exception_guid = @exception_guid

	IF @exception_idx = 0
	BEGIN
		INSERT INTO Exception
		(exception_guid, exception_description, item, value_num, value_num_max, compare_type, quantity, quantity_compare_type, register_num,
cashier, value_char, whole_dollar_amounts, void_nosale_transactions, start_hour, end_hour, alert_enabled, email_enabled, email_is_html,
email_period, email_period_type, email_specific_day_enabled, email_specific_day, email_specific_hour_enabled, email_specific_hour,
email_exception_threshold, where_clause, filter_conjunction_AND, show_exception_in_list, va_enabled, va_camera, va_rulename, va_compare,
va_value, item_case_sensitive, terminal_num, item2, item2_in_next_receipt, [rank], trigger_server_alarm, start_minute, end_minute, start_second, end_second, alarm_priority)
		VALUES
		(@exception_guid, @exception_description, @item, @value_num, @value_num_max, @compare_type, @quantity, @quantity_compare_type, @register_num,
@cashier, @value_char, @whole_dollar_amounts, @void_nosale_transactions, @start_hour, @end_hour, @alert_enabled, @email_enabled,
@email_is_html, @email_period, @email_period_type, @email_specific_day_enabled, @email_specific_day, @email_specific_hour_enabled,
@email_specific_hour, @email_exception_threshold, @where_clause, @filter_conjunction_AND, @show_exception_in_list, @va_enabled, @va_camera,
@va_rulename, @va_compare, @va_value, @item_case_sensitive, @terminal_num, @item2, @item2_in_next_receipt, @rank, @trigger_server_alarm, @start_minute, @end_minute, @start_second, @end_second, @alarm_priority)

		SET @exception_idx = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		UPDATE Exception SET
		exception_description = @exception_description,
		item = @item,
		value_num = @value_num,
		value_num_max = @value_num_max,
		compare_type = @compare_type,
		quantity = @quantity,
		quantity_compare_type = @quantity_compare_type,
		register_num = @register_num,
		cashier = @cashier,
		value_char = @value_char,
		whole_dollar_amounts = @whole_dollar_amounts,
		void_nosale_transactions = @void_nosale_transactions,
		start_hour = @start_hour,
		end_hour = @end_hour,
		start_minute  = @start_minute,
		end_minute = @end_minute,
		start_second  = @start_second,
		end_second = @end_second,
		alert_enabled = @alert_enabled,
		email_enabled = @email_enabled,
		email_is_html = @email_is_html,
		email_period = @email_period,
		email_period_type = @email_period_type,
		email_specific_day_enabled = @email_specific_day_enabled,
		email_specific_day = @email_specific_day,
		email_specific_hour_enabled = @email_specific_hour_enabled,
		email_specific_hour = @email_specific_hour,
		email_exception_threshold = @email_exception_threshold,
		where_clause = @where_clause,
		filter_conjunction_AND = @filter_conjunction_AND,
		show_exception_in_list = @show_exception_in_list,
		va_enabled = @va_enabled,
		va_camera = @va_camera,
		va_rulename = @va_rulename,
		va_compare = @va_compare,
		va_value = @va_value,
		item_case_sensitive = @item_case_sensitive,
		terminal_num = @terminal_num,
		item2 = @item2,
		item2_in_next_receipt = @item2_in_next_receipt,
		[rank] = @rank,
		trigger_server_alarm = @trigger_server_alarm,
		alarm_priority = @alarm_priority
		WHERE exception_guid = @exception_guid
	END

	--Insert related recipients
	EXEC [Vigil-DVR].[dbo].[sp_delete_vpos_email_notification] @exception_idx
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients, 0, 1
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients_cc, 1, 1
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients_bcc, 2, 1
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients_disabled, 0, 0
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients_cc_disabled, 1, 0
	EXEC sp_insert_email_recipients @exception_idx, @email_recipients_bcc_disabled, 2, 0

	--Insert cutoff record to stop old exceptions from being emailed
	IF @email_enabled = 1 AND @exception_idx > 0
	BEGIN
		INSERT INTO ScheduledReportLog (exception_idx, report_timestamp) VALUES (@exception_idx, CURRENT_TIMESTAMP)
	END

	SELECT @exception_idx AS 'idx', @exception_guid AS 'exception_guid'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_ignoretext]
@ignore_field integer,
@ignore_text nvarchar(50)
AS
	INSERT INTO posignoretext ( pos_type, ignore_field, ignore_text)
		VALUES (0, @ignore_field, @ignore_text)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_posdata]
@Receipt_No nvarchar(20),
@Register_Time DateTime,
@Server_Time DateTime,
@Item nvarchar(50),
@value_Char nvarchar(20),
@value_Num money,
@Sequence_Num int,
@Register_Num int,
@terminal_num int,
@void bit,
@nosale bit,
@quantity smallmoney,
@cashier nvarchar(50) = N'',
@action_code_num smallint = null,
@parser_type smallint = null,
@is_standardized bit = 0
AS
  SET NOCOUNT ON
	INSERT INTO tmp_posdata
	(receipt_no, register_time, server_time, item, value_char, value_num, sequence_num, register_num, terminal_num, void, nosale, quantity, cashier, action_code_num, parser_type, is_standardized)
	VALUES
	(@Receipt_No, @Register_Time, @Server_Time, @Item, @Value_Char, @Value_Num, @Sequence_Num, @Register_Num, @terminal_num, @void, @nosale, @quantity, @cashier, @action_code_num, @parser_type, @is_standardized)

	SELECT @@IDENTITY AS new_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_report_log]
@exception_idx int,
@report_timestamp datetime,
@last_report_time datetime output
AS
	DECLARE @max_server_time datetime

	SELECT @max_server_time = MAX(server_time) FROM POSData

	IF NOT @max_server_time IS NULL
	BEGIN
		IF @max_server_time < @report_timestamp
		BEGIN
			SELECT @report_timestamp = @max_server_time
		END

		SELECT @last_report_time = MAX(report_timestamp) FROM ScheduledReportLog WHERE exception_idx = @exception_idx

		IF COALESCE(@last_report_time, DATEADD(s, -1, @report_timestamp)) < @report_timestamp
		BEGIN
			INSERT INTO ScheduledReportLog (exception_idx, report_timestamp) VALUES (@exception_idx, @report_timestamp)
			SELECT @last_report_time = @report_timestamp
		END
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_posdata]
@server_time DATETIME = NULL,
@chunk_size INT = 1000,
@max_record_count INT = 5000000
AS
BEGIN
	-- DELETE oldest data from POSData table either by @server_time cutoff or by @max_record_count to preserve.
	-- Data is deleted up to @chunk_size records per call.
	-- Data coninciding with locked footage will be preserved.
	-- Returns the number of records deleted.
	
	SET NOCOUNT ON

	DECLARE @totalDeleteCount INT

	SET @totalDeleteCount = 0

	IF @server_time IS NULL
	BEGIN
		DECLARE @deleteCount INT
		
		SELECT @deleteCount = COUNT(*) - @max_record_count FROM POSData

		IF @deleteCount > @chunk_size
			SET @deleteCount = @chunk_size

		IF @deleteCount > 0
		BEGIN
			DELETE FROM POSData WHERE idx IN
				(SELECT DISTINCT TOP(@deleteCount) p.idx
				FROM POSData p LEFT JOIN [VIGIL-DVR].dbo.marked_footage f ON f.locked = 1 AND p.server_time BETWEEN f.start_date AND f.end_date
				WHERE f.idx IS NULL
				ORDER BY p.idx)

			SELECT @totalDeleteCount = @@ROWCOUNT
		END
	END
	ELSE
	BEGIN
		DELETE FROM POSData WHERE idx IN
			(SELECT DISTINCT TOP(@chunk_size) p.idx
			FROM POSData p LEFT JOIN [VIGIL-DVR].dbo.marked_footage f ON f.locked = 1 AND p.server_time BETWEEN f.start_date AND f.end_date
			WHERE f.idx IS NULL
			AND p.server_time < @server_time
			ORDER BY p.idx)

		SELECT @totalDeleteCount = @@ROWCOUNT
	END

	EXEC sp_prune_shift_analysis_tag_info
	EXEC sp_prune_scheduled_report_log

	SELECT @totalDeleteCount AS DeleteCount
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_alarm_acknowledged]
@alarm_idx int = 0
AS
	UPDATE alarm SET acknowledged = 1 WHERE alarm_idx = @alarm_idx OR @alarm_idx = 0
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_alarm_checked]
@idx bigint
AS
	UPDATE alarm SET va_checked = 1 WHERE alarm_idx = @idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_alarm_flag]
@idx bigint,
@flag nvarchar(256)
AS
BEGIN
	DECLARE @flag_idx AS bigint
	SELECT @flag_idx = idx FROM AlarmFlag WHERE name = @flag

	IF @flag_idx is NULL or @flag_idx < 0
		Set @flag_idx = -1

	UPDATE Alarm SET alarm_flag_idx = @flag_idx WHERE alarm_idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_alarm_notes]
@idx bigint,
@notes nvarchar(256)
AS
BEGIN
	UPDATE Alarm SET notes = @notes WHERE alarm_idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_alarm_visibility]
@idx bigint = -1,
@state int = 0
AS
BEGIN
	UPDATE Alarm SET visible = @state
	 WHERE acknowledged = 1 AND (alarm_idx = @idx OR @idx = 0)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_global_setting]
@fieldName as NVARCHAR(50),
@fieldValue as NVARCHAR(260)
AS
	SET NOCOUNT ON

	DELETE FROM [GlobalSettings]
	WHERE field_name = @fieldName

	INSERT INTO [GlobalSettings]
		(field_name, field_value)
	VALUES
		(@fieldName, @fieldValue)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_sync_posdata]
AS
	DECLARE @MinIdx bigint
	DECLARE @MaxIdx bigint
	DECLARE @LastError int
	DECLARE @sql nvarchar(1024)
	SELECT @MaxIdx = MAX(idx) FROM tmp_posdata
	IF @MaxIdx IS NOT NULL
	BEGIN
		SET @MaxIdx = @MaxIdx - 1

		BEGIN TRANSACTION

		EXEC sp_get_last_inserted_posdata_idx @MaxIdx, @MinIdx OUTPUT

		SET @sql = 'INSERT INTO posdata
		(idx, receipt_no, register_time, server_time, item, value_char, value_num, sequence_num, register_num, terminal_num, void, nosale, quantity, cashier, action_code_num, parser_type, is_standardized)
		SELECT idx, receipt_no, register_time, server_time, item, value_char, value_num, sequence_num, register_num, terminal_num, void, nosale, quantity, cashier, action_code_num, parser_type, is_standardized
		FROM tmp_posdata
		WHERE idx > ' + CAST(@MinIdx as nvarchar(30)) + '
		AND idx <= ' + CAST (@MaxIdx as nvarchar(30))

		EXECUTE(@sql)

		SELECT @LastError = @@ERROR

		IF @LastError = 0
		BEGIN
			DELETE FROM tmp_posdata
			WHERE idx > @MinIdx
			AND idx <= @MaxIdx
			COMMIT TRANSACTION
		END
		ELSE
		BEGIN
			ROLLBACK TRANSACTION
		END
	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-POS')
	DBCC SHRINKFILE('VIGIL-POS', 1)
	DBCC SHRINKFILE('VIGIL-POS_LOG', 1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_parse_tlog]
	@type nvarchar(5)
	, @transaction nvarchar(4000)
as
declare @i int
	, @rec_id int
	, @period nvarchar(32)
	, @terminal_num nvarchar(32)
	, @transaction_num nvarchar(32)
	, @operator_code nvarchar(32)
	, @trx_amt money
	, @trx_ret money
	, @server_time datetime
	, @cash money
	, @change money
	, @tax money
select @server_time = convert(datetime, convert(nvarchar(32),getdate(),120), 120)
if @type = '01'
begin
	select [name]
	from pos_items (nolock)
	where item_code = right(N'000000000000' + @transaction,12)
	return
end
if @type = '02'
begin
	declare @len int, @rec_len int

	select @len = len(@transaction)/30
		, @rec_len = 30
	insert into pos_items(item_code, [name])
	select f.item_code, f.item_name
	from (
		select
			item_name = substring(d.data,13,18)
			, item_code = left(d.data,12)
		from(
			select
				substring(@transaction, (([index]-1)*@rec_len)+1,@rec_len) as data
			from seq_data (nolock)
			where [index] <= @len + 1
		) d
		where ISNULL(d.data,'') != ''
	) f
	left join pos_items it (nolock) on it.item_code = f.item_code
	where it.item_code is null
	return
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_set_acknowledged_expired_alarms]
@ack int
AS
BEGIN
	DECLARE @date datetime
	SELECT @date = CONVERT(datetime,convert(varchar(10),getdate(),101),101)

	UPDATE b SET acknowledged = 1
	FROM POSData a (nolock)
	INNER JOIN Alarm b (nolock) on a.idx = b.pos_idx
	WHERE a.server_time BETWEEN dateadd(dd, -2*@ack, @date) AND dateadd(dd, -@ack, @date)
		AND b.acknowledged = 0
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_vpos_posdata]
@exception_guid nvarchar(50),
@start_time datetime,
@end_time datetime
AS
BEGIN
	DECLARE @sql nvarchar(4000)
	DECLARE @where_clause nvarchar(1000)
	DECLARE @param_def nvarchar(1000)

	SELECT @where_clause = where_clause FROM exception WHERE exception_guid = @exception_guid

	IF @where_clause IS NOT NULL
	BEGIN
		SET @sql = N'SELECT * FROM vw_posdata WHERE (' + @where_clause + N') AND server_time BETWEEN @start AND @end ORDER BY idx'
		SET @param_def = '@start datetime, @end datetime'
		EXEC sp_executesql @sql, @param_def, @start = @start_time, @end = @end_time
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_generate_alarms]
@minInsertedIdx bigint = 0
AS
BEGIN
	DECLARE @where_clause nvarchar(1000)
	DECLARE @exception_idx int
	DECLARE @insertSQL nvarchar(4000)
	DECLARE @va_enabled bit
	DECLARE @va_checked nvarchar(10)

	DECLARE WhereClause_Cursor CURSOR FOR
	SELECT where_clause, exception_idx, va_enabled
	FROM dbo.Exception
	WHERE alert_enabled = 1
	AND ISNULL(where_clause, '') <> ''

	OPEN WhereClause_Cursor

	FETCH NEXT FROM WhereClause_Cursor
	INTO @where_clause, @exception_idx, @va_enabled

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @where_clause = replace(@where_clause, '@start_time', '''1980-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@end_time', '''2099-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@idx', CAST(@minInsertedIdx - 100 AS nvarchar(21)))

		IF @va_enabled = 1
			SET @va_checked = '0'
		ELSE
			SET @va_checked = '1'

		SELECT @insertSQL = N'INSERT INTO Alarm (pos_idx, exception_idx, va_checked) SELECT idx, ' + CAST(@exception_idx AS nvarchar(21)) + N', ' + @va_checked + N' FROM POSData WHERE idx >= ' + CAST(@minInsertedIdx AS nvarchar(21)) + N' AND ' + CAST(@where_clause AS nvarchar(1000))

		EXEC sp_executesql @insertSQL

		FETCH NEXT FROM WhereClause_Cursor
		INTO @where_clause, @exception_idx, @va_enabled
	END

	CLOSE WhereClause_Cursor

	DEALLOCATE WhereClause_Cursor
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_get_daily_cashier_transactions]
@start_time as datetime,
@end_time as datetime
as
begin
	set nocount on

	select [date], cashier, count(*) as transactions from
	(
		select distinct convert(date, server_time) as [date], cashier, receipt_no
		from posdata
		where server_time >= @start_time
		and server_time <= @end_time
		and receipt_no is not null and receipt_no <> ''
		and cashier is not null and cashier <> ''
	) t
	group by [date], cashier
	order by date, cashier
end
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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_alarms_for_server_alarm_exceptions]
@last_alarm_idx bigint = 0,
@min_server_time datetime = null
AS
BEGIN
	select a.alarm_idx, e.exception_idx, e.exception_description, p.terminal_num, p.register_num, e.alarm_priority
	from alarm a
	join exception e on a.exception_idx = e.exception_idx
	join posdata p on a.pos_idx = p.idx
	where e.trigger_server_alarm = 1
	and a.alarm_idx > @last_alarm_idx
	and (@min_server_time is null or @min_server_time <= p.server_time)
	order by alarm_idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_set_shift_analysis_tag_info]
@rule_id nvarchar(50),
@cashier nvarchar(50),
@tag_date date,
@acknowledged bit,
@pos_idx bigint,
@notes nvarchar(1000) = null,
@event_flag_id nvarchar(50) = null
as
begin
	merge shift_analysis_tag_info with (serializable) as t
	using (values (@rule_id, @pos_idx)) as u (rule_id, pos_idx)
		on t.rule_id = u.rule_id and t.pos_idx = u.pos_idx
	when matched then
		update set acknowledged = @acknowledged, notes = @notes, event_flag_id = @event_flag_id, last_update_utc = getutcdate()
	when not matched then
		insert (rule_id, cashier, tag_date, acknowledged, pos_idx, notes, event_flag_id)
		values (@rule_id, @cashier, @tag_date, @acknowledged, @pos_idx, @notes, @event_flag_id);
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_get_shift_analysis_tag_info]
@rule_id nvarchar(50) = null,
@cashier nvarchar(50) = null,
@tag_date_start date = null,
@tag_date_end date = null
as
begin
	select * from shift_analysis_tag_info
	where
		(@rule_id is null or rule_id = @rule_id)
		and (@cashier is null or cashier = @cashier)
		and (
			@tag_date_start is null and @tag_date_end is null
			or @tag_date_end is null and tag_date = @tag_date_start
			or @tag_date_start is not null and @tag_date_end is not null and tag_date between @tag_date_start and @tag_date_end
		)
end
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_alarm]
@pos_idx bigint,
@exception_idx int = 0,
@alarm_flag_idx int = -1,
@notes nvarchar(256) = '',
@acknowledged bit = 0,
@visible bit = 1,
@va_checked bit = 0
AS
	SET NOCOUNT ON
	
	INSERT INTO Alarm (pos_idx, exception_idx, alarm_flag_idx, notes, acknowledged, visible, va_checked)
	VALUES (@pos_idx, @exception_idx, @alarm_flag_idx, @notes, @acknowledged, @visible, @va_checked)

	SELECT @@IDENTITY as alarm_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_get_alarm_details]
@alarm_idx bigint
as
	select
		af.name as flag_name,
		af.email_enabled,
		af.email_recipients,
		e.exception_description,
		p.idx as pos_idx,
		p.receipt_no,
		p.register_time,
		p.item,
		p.value_char,
		p.value_num,
		p.register_num,
		p.terminal_num,
		p.quantity,
		p.cashier
	from
		alarm a
		join posdata p on a.pos_idx = p.idx
		join alarmflag af on a.alarm_flag_idx = af.idx
		left join Exception e on a.exception_idx = e.exception_idx
	where
		a.alarm_idx = @alarm_idx
GO


-- Update real time alarms database trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[REAL_TIME_ALARMS] ON [dbo].[POSData]
FOR INSERT
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @exception_idx int
	DECLARE @insertSQL nvarchar(4000)
	DECLARE @minInsertedIdx bigint
	DECLARE @va_enabled bit
	DECLARE @va_checked nvarchar(10)

	DECLARE WhereClause_Cursor CURSOR FOR
	SELECT where_clause, exception_idx, va_enabled
	FROM dbo.Exception
	WHERE alert_enabled = 1
	AND ISNULL(where_clause, '') <> ''

	OPEN WhereClause_Cursor

	SELECT @minInsertedIdx = MIN(idx) FROM inserted

	FETCH NEXT FROM WhereClause_Cursor
	INTO @where_clause, @exception_idx, @va_enabled

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @where_clause = replace(@where_clause, '@start_time', '''1980-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@end_time', '''2099-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@idx', CAST(@minInsertedIdx - 100 AS nvarchar(21)))

		IF @va_enabled = 1
			SET @va_checked = '0'
		ELSE
			SET @va_checked = '1'

		SELECT @insertSQL = N'INSERT INTO Alarm (pos_idx, exception_idx, va_checked) SELECT idx, ' + CAST(@exception_idx AS nvarchar(21)) + N', ' + @va_checked + N' FROM POSData WHERE idx >= ' + CAST(@minInsertedIdx AS nvarchar(21)) + N' AND ' + CAST(@where_clause AS nvarchar(1000))

		EXEC sp_executesql @insertSQL

		FETCH NEXT FROM WhereClause_Cursor
		INTO @where_clause, @exception_idx, @va_enabled
	END

	CLOSE WhereClause_Cursor

	DEALLOCATE WhereClause_Cursor
GO
