PRINT 'POS upgrade script for ''' + DB_NAME() + ''' database'
PRINT 'NOTE that base POS 1.0 database should be installed before this script starts'

IF DB_NAME() = 'master'
BEGIN
	PRINT 'ERROR! current database couldn''t be a ''master'''
	RAISERROR('ERROR! current database couldn''t be a ''master''', 20, 1) WITH LOG
END

GO

IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'POS_LOG_MASTER' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	PRINT 'ERROR! POS 1.0 database is missing'
	PRINT 'NOTE that base POS 1.0 database should be installed before this script starts'
	RAISERROR('ERROR! POS 1.0 database is missing', 20, 1) WITH LOG

END

GO

------SET SNAPSHOT ISOLATION LEVEL -------------
IF EXISTS (SELECT * FROM sys.databases WHERE name = DB_NAME() AND snapshot_isolation_state <> 1)
BEGIN
	DECLARE @request nvarchar(MAX)
	SET @request = N'ALTER DATABASE [@dbname] SET ALLOW_SNAPSHOT_ISOLATION ON';
	SET @request=REPLACE(@request, '@dbname', DB_NAME());
	EXECUTE sp_executesql @request
	PRINT 'SET ALLOW_SNAPSHOT_ISOLATION ON for pos database'
END

GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = DB_NAME() AND is_read_committed_snapshot_on <> 1)
BEGIN
	DECLARE @request nvarchar(MAX)
	SET @request = N'ALTER DATABASE [@dbname] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE';
	SET @request=REPLACE(@request, '@dbname', DB_NAME());
	EXECUTE sp_executesql @request
	PRINT 'SET READ_COMMITTED_SNAPSHOT ON for pos database'
END

GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = N'tempdb' AND snapshot_isolation_state <> 1)
BEGIN
	ALTER DATABASE tempdb SET ALLOW_SNAPSHOT_ISOLATION  ON
	PRINT 'SET ALLOW_SNAPSHOT_ISOLATION ON for tempdb'
END

GO
IF NOT EXISTS (SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID())
BEGIN
	DECLARE @request nvarchar(MAX)
	SET @request = N'ALTER DATABASE [@dbname] SET CHANGE_TRACKING = ON
	(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON)';
	SET @request=REPLACE(@request, '@dbname', DB_NAME());
	EXECUTE sp_executesql @request
END



--------------------------------
BEGIN TRAN UpdatePos

-- MUST SET CURRENT VERSION OF PRODUCTION DATABASE ONLY HERE !!!
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fRequirementVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fRequirementVersion]
GO
CREATE FUNCTION [dbo].[fRequirementVersion] ()
RETURNS bigint
AS
BEGIN
	DECLARE @number int, @major int,  @minor int, @build int
	SELECT @number = 4, @major = 8, @minor = 1, @build = 0
	RETURN [dbo].[fReturnVersion](@number, @major, @minor, @build)
END
GO

-- Detect that database is 4.7.6.0
IF (NOT EXISTS(SELECT syscolumns.name columnname FROM SysObjects 
	JOIN syscolumns ON SysObjects.id = SysColumns.id JOIN systypes ON syscolumns.xtype = sysTypes.xtype 
	WHERE (SysObjects.name = 'POS_LOG_MASTER' OR SysObjects.name = 'POS_LOG_DETAIL')
	AND systypes.name <> 'sysname'
	AND syscolumns.name = '_id'))
	BEGIN
		TRUNCATE TABLE [dbo].[Version]
		INSERT INTO [dbo].[Version] (number, major, minor, build)  VALUES (4, 7, 6, 0);
	END
ELSE
	BEGIN
	IF  ([dbo].[fIsNewDatabase]() > 0) 
		BEGIN
			EXEC spUpdateToRequirementVersion
		END
	END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	returns MD5 hash of the input text
-- =============================================
CREATE FUNCTION [dbo].[fGetHash] ( @value nvarchar(255) )
RETURNS uniqueidentifier
AS
BEGIN

	SET @value = ISNULL(@value, N'-1')
	-- extended up to 64 symbols
	DECLARE @value_ext nvarchar(64)
	SET @value_ext = LEFT( UPPER(@value) + '_012345678901234567890123456789012345678901234567890123456789012', 64)

	-- return hash of the extended string
	RETURN CONVERT(uniqueidentifier, HashBytes('MD5', @value_ext))
END

GO
------------------------------------------------


IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'CashierDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[CashierDic](
		[id] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL, 
		[CashierNumber] nvarchar(50) NOT NULL UNIQUE,
		[CashierName] nvarchar(100) NULL,
	 CONSTRAINT [PK_CashierDic] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	PRINT 'Created dbo.CashierDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.CashierDic is already exists'
END

GO

------------------------------------------------

IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
	WHERE CONSTRAINT_CATALOG = DB_NAME() AND TABLE_NAME = 'StoreDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].StoreDic (
		[id] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL, 
		[StoreGUID] [nvarchar](50) NOT NULL UNIQUE,
		[StoreName] [nvarchar](1024) NOT NULL,
	 CONSTRAINT [PK_StoreDic] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY];

	PRINT 'Created dbo.StoreDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.StoreDic is already exists'
END

GO

------------------------------------------------

IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'PosDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[PosDic](
		[id] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[PosGUID] [nvarchar](50) NOT NULL UNIQUE,
		[PosName] [nvarchar](1024) NOT NULL,
		[Store_id] [uniqueidentifier] NOT NULL,
	CONSTRAINT [FK_StoreID] FOREIGN KEY (Store_id) REFERENCES dbo.StoreDic(id),
	CONSTRAINT [PK_PosDic] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY];

	PRINT 'Created dbo.PosDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.PosDic is already exists'
END

GO

------------------------------------------------

IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'FuncDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[FuncDic](
		[id] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[FunctionNumber] [int] UNIQUE NOT NULL,
		[FunctionName] [nvarchar](100) NOT NULL,
	 CONSTRAINT [PK_FuncDic] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY];

	PRINT 'Created dbo.FuncDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.FuncDic is already exists'
END

GO

---------------------------------------------------
-- existing table changing

IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE   
      TABLE_NAME = 'POS_LOG_DETAIL' AND COLUMN_NAME = 'id' ) = N'int'
  PRINT 'POS_LOG_DETAIL.id is already has INT type'
ELSE
BEGIN
  DROP INDEX [dbo].[POS_LOG_DETAIL].[id_index]
  PRINT 'index [id_index] for [dbo].[POS_LOG_DETAIL] has been dropped'
  DROP INDEX [dbo].[POS_LOG_DETAIL].[FunctionNumber_id_date_index]
  PRINT 'index [FunctionNumber_id_date_index] for [dbo].[POS_LOG_DETAIL] has been dropped'
  DROP INDEX [dbo].[POS_LOG_DETAIL].[pos_id_index]
  PRINT 'index [pos_id_index] for [dbo].[POS_LOG_DETAIL] has been dropped'
  ALTER TABLE [dbo].[POS_LOG_DETAIL] ALTER COLUMN [id] INT
  PRINT 'POS_LOG_DETAIL.id type is now changing to INT type'
END

GO

IF EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = 'POS_LOG_DETAIL' AND COLUMN_NAME = '_id'
)
  PRINT 'POS_LOG_DETAIL._id is already exists'
ELSE
BEGIN
  ALTER TABLE [dbo].[POS_LOG_DETAIL] ADD [_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_POS_LOG_DETAIL__id] DEFAULT (newsequentialid())
  PRINT 'Created POS_LOG_DETAIL._id'
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_DETAIL') AND name = N'PK_POS_LOG_DETAIL')
BEGIN
	ALTER TABLE [dbo].[POS_LOG_DETAIL] ADD  CONSTRAINT [PK_POS_LOG_DETAIL] PRIMARY KEY CLUSTERED 
	(
		[_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'PRIMARY KEY [PK_POS_LOG_DETAIL] for [dbo].[POS_LOG_DETAIL] has been added'
END
ELSE
	PRINT 'PRIMARY KEY [PK_POS_LOG_DETAIL] for [dbo].[POS_LOG_DETAIL] is already exist'

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_DETAIL') AND name = N'id_index')
BEGIN
	CREATE NONCLUSTERED INDEX [id_index] ON [dbo].[POS_LOG_DETAIL] 
	(
		  [id] ASC
	)ON [PRIMARY]
	PRINT 'index [id_index] for [dbo].[POS_LOG_DETAIL] has been added'
END
ELSE
	PRINT 'index [id_index] for [dbo].[POS_LOG_DETAIL] is already exist'

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_DETAIL') AND name = N'FunctionNumber_id_date_index')
BEGIN
	CREATE NONCLUSTERED INDEX [FunctionNumber_id_date_index] ON [dbo].[POS_LOG_DETAIL] 
	(
		  [FunctionNumber] ASC,
		  [id] ASC,
		  [date] ASC
	)ON [PRIMARY]
	PRINT 'index [FunctionNumber_id_date_index] for [dbo].[POS_LOG_DETAIL] has been added'
END
ELSE
	PRINT 'index [FunctionNumber_id_date_index] for [dbo].[POS_LOG_DETAIL] is already exist'

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_DETAIL') AND name = N'pos_id_index')
BEGIN
	CREATE NONCLUSTERED INDEX [pos_id_index] ON [dbo].[POS_LOG_DETAIL] 
	(
		[pos_id] ASC,
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'index [pos_id_index] for [dbo].[POS_LOG_DETAIL] has been added'
END
ELSE
	PRINT 'index [pos_id_index] for [dbo].[POS_LOG_DETAIL] is already exist'

GO

----------------------------------------------------------------

IF EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = 'POS_LOG_MASTER' AND COLUMN_NAME = '_id'
)
  PRINT 'POS_LOG_MASTER._id is already exists'
ELSE
BEGIN
  ALTER TABLE [dbo].[POS_LOG_MASTER] ADD [_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_POS_LOG_MASTER__id] DEFAULT (newsequentialid())
  PRINT 'Created POS_LOG_MASTER._id'
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_MASTER') AND name = N'PK_POS_LOG_MASTER')
BEGIN
	ALTER TABLE [dbo].[POS_LOG_MASTER] ADD  CONSTRAINT [PK_POS_LOG_MASTER] PRIMARY KEY CLUSTERED 
	(
		[_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'PRIMARY KEY [PK_POS_LOG_MASTER] for [dbo].[POS_LOG_MASTER] has been added'
END
ELSE
	PRINT 'PRIMARY KEY [PK_POS_LOG_MASTER] for [dbo].[POS_LOG_MASTER] is already exist'

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.POS_LOG_MASTER') AND name = N'check_id_index')
BEGIN
	CREATE NONCLUSTERED INDEX [check_id_index] ON [dbo].[POS_LOG_MASTER] 
	(
		[check_id] ASC
	) ON [PRIMARY]
	PRINT 'index [check_id_index] for [dbo].[POS_LOG_MASTER] has been added'
END
ELSE
	PRINT 'index [check_id_index] for [dbo].[POS_LOG_MASTER] is already exist'

GO

------------------------------------------------

IF [dbo].[fCurrentVersion]() < [dbo].[fReturnVersion](4, 7, 8, 1) AND EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'Events' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Events_CashierDic]') AND parent_object_id = OBJECT_ID(N'[dbo].[Events]'))
	ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_CashierDic]
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Events_FuncDic]') AND parent_object_id = OBJECT_ID(N'[dbo].[Events]'))
	ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_FuncDic]
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Events_POS_LOG_DETAIL]') AND parent_object_id = OBJECT_ID(N'[dbo].[Events]'))
	ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_POS_LOG_DETAIL]
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Events_POS_LOG_MASTER]') AND parent_object_id = OBJECT_ID(N'[dbo].[Events]'))
	ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_POS_LOG_MASTER]
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Events_PosDic]') AND parent_object_id = OBJECT_ID(N'[dbo].[Events]'))
	ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_PosDic]

	DROP TABLE [dbo].[Events]	PRINT 'Dropped dbo.Events'
END
GO

IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'Events' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[Events](
		[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Events_id]  DEFAULT (newsequentialid()),
		[pos_log_master_id] [uniqueidentifier] NOT NULL,
		[pos_log_detail_id] [uniqueidentifier] NULL,
		[Funct_id] [uniqueidentifier] NOT NULL,
		[Cashier_id] [uniqueidentifier] NOT NULL,
		[Pos_id] [uniqueidentifier] NOT NULL,
		[TimeStamp] [datetime] NOT NULL,
		[Fraction] [int] NOT NULL DEFAULT 0,
		[Message] nvarchar(MAX) NULL,  -- POS check message
		[Cause] nvarchar(8) NOT NULL
	 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_CashierDic] FOREIGN KEY([Cashier_id])
	REFERENCES [dbo].[CashierDic] ([id])

	ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_CashierDic]

	ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_FuncDic] FOREIGN KEY([Funct_id])
	REFERENCES [dbo].[FuncDic] ([id])

	ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_FuncDic]

	ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_POS_LOG_DETAIL] FOREIGN KEY([pos_log_detail_id])
	REFERENCES [dbo].[POS_LOG_DETAIL] ([_id])

	ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_POS_LOG_DETAIL]

	ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_POS_LOG_MASTER] FOREIGN KEY([pos_log_master_id])
	REFERENCES [dbo].[POS_LOG_MASTER] ([_id])

	ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_POS_LOG_MASTER]

	ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_PosDic] FOREIGN KEY([Pos_id])
	REFERENCES [dbo].[PosDic] ([id])

	ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_PosDic]

	PRINT 'Created dbo.Events'
END
GO

IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.Events') AND name = N'K_FunctCashierPosTime')
BEGIN
	DROP INDEX [K_FunctCashierPosTime] ON [dbo].[Events] 
	PRINT 'index [K_FunctCashierPosTime] for [dbo].[Events] has been dropped'
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.Events') AND name = N'posLogMasterId_idx')
BEGIN
	CREATE NONCLUSTERED INDEX [posLogMasterId_idx] ON [dbo].[Events] 
	(
		[pos_log_master_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'index [posLogMasterId_idx] for [dbo].[Events] has been added'
END
ELSE
	PRINT 'index [posLogMasterId_idx] for [dbo].[Events] is already exist'

GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.Events') AND name = N'posLogDetailId_idx')
BEGIN
	CREATE NONCLUSTERED INDEX [posLogDetailId_idx] ON [dbo].[Events] 
	(
		[pos_log_detail_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'index [posLogDetailId_idx] for [dbo].[Events] has been added'
END
ELSE
	PRINT 'index [posLogDetailId_idx] for [dbo].[Events] is already exist'

GO

IF  NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Events]') AND name = N'Funct_idTimeStamp_idx')
BEGIN
	CREATE NONCLUSTERED INDEX [Funct_idTimeStamp_idx] ON [dbo].[Events] 
	(
		[Funct_id] ASC,
		[TimeStamp] ASC
	)
	INCLUDE ( [id],
	[pos_log_master_id],
	[pos_log_detail_id],
	[Cashier_id],
	[Pos_id],
	[Fraction]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'index [Funct_idTimeStamp_idx] for [dbo].[Events] has been added'
END
ELSE
	PRINT 'index [Funct_idTimeStamp_idx] for [dbo].[Events] is already exist'

GO

IF  NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Events]') AND name = N'TimeStamp_idx')
BEGIN
	CREATE NONCLUSTERED INDEX [TimeStamp_idx] ON [dbo].[Events] 
	(
		[TimeStamp] ASC
	)
	INCLUDE ( [id],
	[Funct_id],
	[Cashier_id],
	[Pos_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	PRINT 'index [TimeStamp_idx] for [dbo].[Events] has been added'
END
ELSE
	PRINT 'index [TimeStamp_idx] for [dbo].[Events] is already exist'
GO

------------------------------------------------

-- проверка
UPDATE dbo.POS_LOG_MASTER SET cashier_number='-1' WHERE cashier_number IS NULL

--	первичное заполнение справочников
INSERT INTO dbo.CashierDic
        ( CashierNumber, CashierName )
SELECT DISTINCT cashier_number, cashier_name FROM POS_LOG_MASTER
WHERE cashier_number NOT IN ( SELECT CashierNumber FROM dbo.CashierDic );

PRINT 'Filled dbo.CashierDic'

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fDefaultStatus]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fDefaultStatus]
GO
CREATE FUNCTION [dbo].[fDefaultStatus] ()
RETURNS uniqueidentifier
AS
BEGIN
	RETURN 'd22ed4c5-aedd-df11-bb6a-0017315ee8ce'
END
GO

------------------------------------------------
-- Таблица статусов
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'StatusDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[StatusDic](
		[StatusID] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[Name] [nvarchar](100) NOT NULL UNIQUE,
		[Description] [nvarchar](MAX) NULL DEFAULT NULL,
		[TextColor] [int] NOT NULL DEFAULT 0xFFFFFF,
		[BackColor] [int] NOT NULL DEFAULT 0,
		[IsEnabled] [int] NOT NULL DEFAULT 1,
	CONSTRAINT [PK_StatusDic] PRIMARY KEY CLUSTERED 
	(
		[StatusID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
	-- 'Событие не обработано (по умолчанию)'
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	VALUES    ([dbo].[fDefaultStatus](), '{EventStatus1}','', 0, 0x858585, 1)
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	-- 'Точно не нарушение'
	VALUES    ('366d1c03-afdd-df11-bb6a-0017315ee8ce', '{EventStatus2}','', 0, 0x2d9518, 1)
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	-- 'Возможно нарушение'
	VALUES    ('376d1c03-afdd-df11-bb6a-0017315ee8ce', '{EventStatus3}','', 0, 0xdae042, 1)
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	-- 'Выявлено нарушение лёгкой тяжести'
	VALUES    ('386d1c03-afdd-df11-bb6a-0017315ee8ce', '{EventStatus4}','', 0, 0xe9962b, 1)
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	-- 'Выявлено нарушение средней тяжести'
	VALUES    ('396d1c03-afdd-df11-bb6a-0017315ee8ce', '{EventStatus5}','', 0, 0xf34320, 1)
	-- 'Выявлено грубейшее нарушение'
	INSERT INTO [dbo].[StatusDic]    ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled]) 
	VALUES    ('3a6d1c03-afdd-df11-bb6a-0017315ee8ce', '{EventStatus6}','', 0, 0x6b1e0e, 1)

	PRINT 'Created dbo.StatusDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.StatusDic is already exists'
END

GO

------------------------------------------------
-- Таблица фильтров (объединение статусов)
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'StatusFilter' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[StatusFilter](
		[StatusFilterID] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[Name] [nvarchar](100) NOT NULL UNIQUE,
		[Description] [nvarchar](MAX) NULL DEFAULT NULL,
	CONSTRAINT [PK_StatusFilter] PRIMARY KEY CLUSTERED 
	(
		[StatusFilterID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	-- 'Все события' соответствуют фильтру NULL
	-- 'Точно не нарушения'
	INSERT INTO [dbo].[StatusFilter]    ([StatusFilterID], [Name],[Description]) 
	VALUES    ('A4E40E0E-009E-40B3-80EF-52E44E32C8BD', '{EventFilter2}','')
	-- 'Предупреждения'
	INSERT INTO [dbo].[StatusFilter]    ([StatusFilterID], [Name],[Description]) 
	VALUES    ('9F19FBCF-DD21-4F5E-B703-AB1F97A035FF', '{EventFilter3}','')
	-- 'Точно нарушения'
	INSERT INTO [dbo].[StatusFilter]    ([StatusFilterID], [Name],[Description]) 
	VALUES    ('8F8495B8-09CE-4D19-987A-A14858FF0C97', '{EventFilter4}','')

	PRINT 'Created dbo.StatusFilter'
END
ELSE
BEGIN
	PRINT 'Table dbo.StatusFilter is already exists'
END

GO

------------------------------------------------
-- Таблица связи фильтров статусов и собственно самих статусов
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'StatusFilterStatusDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[StatusFilterStatusDic](
		[StatusFilterID] uniqueidentifier NOT NULL,
		[StatusID] uniqueidentifier NOT NULL,
	CONSTRAINT [PK_StatusFilterStatusDic] PRIMARY KEY CLUSTERED 
	(
		[StatusFilterID] ASC,
		[StatusID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[StatusFilterStatusDic]  WITH CHECK ADD  CONSTRAINT [FK_StatusFilterStatusDic_StatusDic] FOREIGN KEY([StatusID])
	REFERENCES [dbo].[StatusDic] ([StatusID])

	ALTER TABLE [dbo].[StatusFilterStatusDic]  WITH CHECK ADD  CONSTRAINT [FK_StatusFilterStatusDic_StatusFilter] FOREIGN KEY([StatusFilterID])
	REFERENCES [dbo].[StatusFilter] ([StatusFilterID])
	ON DELETE CASCADE

	-- 'Все события' соответствуют фильтру NULL
	-- 'Точно не нарушения'
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('A4E40E0E-009E-40B3-80EF-52E44E32C8BD', '366d1c03-afdd-df11-bb6a-0017315ee8ce')
	-- 'Предупреждения'
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('9F19FBCF-DD21-4F5E-B703-AB1F97A035FF', [dbo].[fDefaultStatus]())
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('9F19FBCF-DD21-4F5E-B703-AB1F97A035FF', '376d1c03-afdd-df11-bb6a-0017315ee8ce')
	-- 'Точно нарушения'
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('8F8495B8-09CE-4D19-987A-A14858FF0C97', '386d1c03-afdd-df11-bb6a-0017315ee8ce')
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('8F8495B8-09CE-4D19-987A-A14858FF0C97', '396d1c03-afdd-df11-bb6a-0017315ee8ce')
	INSERT INTO [dbo].[StatusFilterStatusDic]    ([StatusFilterID], [StatusID]) 
	VALUES    ('8F8495B8-09CE-4D19-987A-A14858FF0C97', '3a6d1c03-afdd-df11-bb6a-0017315ee8ce')

	PRINT 'Created dbo.StatusFilterStatusDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.StatusFilterStatusDic is already exists'
END

GO

------------------------------------------------
-- Таблица фильтров функций
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'FuncFilter' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[FuncFilter](
		[Id] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[Name] [nvarchar](100) NOT NULL UNIQUE,
		[Description] [nvarchar](MAX) NULL DEFAULT NULL,
	CONSTRAINT [PK_FuncFilter] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	PRINT 'Created dbo.StatusFilter'
END
ELSE
BEGIN
	PRINT 'Table dbo.StatusFilter is already exists'
END

GO

------------------------------------------------
-- Таблица связи фильтров функций и собственно самих функций
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'FuncFilterFuncDic' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[FuncFilterFuncDic](
		[FuncFilterId] uniqueidentifier NOT NULL,
		[FuncDicId] uniqueidentifier NOT NULL,
	CONSTRAINT [PK_FuncFilterFuncDic] PRIMARY KEY CLUSTERED 
	(
		[FuncFilterId] ASC,
		[FuncDicId] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[FuncFilterFuncDic]  WITH CHECK ADD  CONSTRAINT [FK_FuncFilterFuncDic_FuncDic] FOREIGN KEY([FuncDicID])
	REFERENCES [dbo].[FuncDic] ([id])

	ALTER TABLE [dbo].[FuncFilterFuncDic]  WITH CHECK ADD  CONSTRAINT [FK_FuncFilterFuncDic_FuncFilter] FOREIGN KEY([FuncFilterId])
	REFERENCES [dbo].[FuncFilter] ([Id])
	ON DELETE CASCADE
	ON UPDATE CASCADE

	PRINT 'Created dbo.FuncFilterFuncDic'
END
ELSE
BEGIN
	PRINT 'Table dbo.FuncFilterFuncDic is already exists'
END

GO
------------------------------------------------
-- Таблица операторов для POS
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'PosOperator' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[PosOperator](
		[PosOperatorID] [uniqueidentifier] DEFAULT NEWSEQUENTIALID() NOT NULL,
		[Login] [nvarchar](100) NOT NULL UNIQUE,
		[Name] [nvarchar](MAX) NOT NULL,
	CONSTRAINT [PK_PosOperator] PRIMARY KEY CLUSTERED 
	(
		[PosOperatorID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	PRINT 'Created dbo.PosOperator'
END
ELSE
BEGIN
	PRINT 'Table dbo.PosOperator is already exists'
END

GO

------------------------------------------------
-- Индекс для логина оператора
------------------------------------------------
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.PosOperator') AND name = N'IX_PosOperator_Login')
BEGIN
	CREATE NONCLUSTERED INDEX [IX_PosOperator_Login] ON [dbo].[PosOperator] 
	(
		  [Login] ASC
	)ON [PRIMARY]
	PRINT 'index [IX_PosOperator_Login] for [dbo].[PosOperator] has been added'
END
ELSE
	PRINT 'index [IX_PosOperator_Login] for [dbo].[PosOperator] is already exist'

GO

------------------------------------------------
-- Таблица истории изменения статусов
------------------------------------------------
IF NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_CATALOG = DB_NAME() AND TABLE_NAME = 'StatusLog' AND TABLE_SCHEMA = 'dbo'
)
BEGIN
	CREATE TABLE [dbo].[StatusLog](
		[StatusLogID] uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
		[EventID] uniqueidentifier NOT NULL,
		[StatusID] uniqueidentifier NOT NULL,
		[PosOperatorID] uniqueidentifier NOT NULL,
		[ChangeDateTime] datetime NOT NULL,
	CONSTRAINT [PK_StatusLog] PRIMARY KEY CLUSTERED 
	(
		[StatusLogID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[StatusLog]  WITH CHECK ADD  CONSTRAINT [FK_StatusLog_StatusDic] FOREIGN KEY([StatusID])
	REFERENCES [dbo].[StatusDic] ([StatusID])

	ALTER TABLE [dbo].[StatusLog]  WITH CHECK ADD  CONSTRAINT [FK_StatusLog_Events] FOREIGN KEY([EventID])
	REFERENCES [dbo].[Events] ([id])

	ALTER TABLE [dbo].[StatusLog]  WITH CHECK ADD  CONSTRAINT [FK_StatusLog_PosOperator] FOREIGN KEY([PosOperatorID])
	REFERENCES [dbo].[PosOperator] ([PosOperatorID])

	PRINT 'Created dbo.StatusLog'
END
ELSE
BEGIN
	PRINT 'Table dbo.StatusLog is already exists'
END

GO

------------------------------------------------
-- Индекс для даты-времени истории изменения
------------------------------------------------
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.StatusLog') AND name = N'IX_StatusLog_ChangeDateTime')
BEGIN
	CREATE NONCLUSTERED INDEX IX_StatusLog_ChangeDateTime ON [dbo].[StatusLog] 
	(
		  [ChangeDateTime] DESC
	)ON [PRIMARY]
	PRINT 'index [IX_StatusLog_ChangeDateTime] for [dbo].[StatusLog] has been added'
END
ELSE
	PRINT 'index [IX_StatusLog_ChangeDateTime] for [dbo].[StatusLog] is already exist'

GO

------------------------------------------------
-- Индекс для связки истории изменения с событием
------------------------------------------------
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.StatusLog') AND name = N'IX_StatusLog_EventID')
BEGIN
	CREATE NONCLUSTERED INDEX IX_StatusLog_EventID ON [dbo].[StatusLog] 
	(
		  [EventID] ASC
	)ON [PRIMARY]
	PRINT 'index [IX_StatusLog_EventID] for [dbo].[StatusLog] has been added'
END
ELSE
	PRINT 'index [IX_StatusLog_EventID] for [dbo].[StatusLog] is already exist'

GO

------------------------------------------------
-- Индекс для идентификатора события+даты-времени истории изменения
------------------------------------------------
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.StatusLog') AND name = N'IX_StatusLog_EventID_ChangeDateTime')
BEGIN
	CREATE NONCLUSTERED INDEX IX_StatusLog_EventID_ChangeDateTime ON [dbo].[StatusLog] 
	(
		  [EventID] ASC,
		  [ChangeDateTime] DESC
	)ON [PRIMARY]
	PRINT 'index [IX_StatusLog_EventID_ChangeDateTime] for [dbo].[StatusLog] has been added'
END
ELSE
	PRINT 'index [IX_StatusLog_EventID_ChangeDateTime] for [dbo].[StatusLog] is already exist'

GO

--------------------------  TRIGGERS -----------------------------

IF OBJECT_ID ('triggerPOS_LOG_MASTER_Inserter', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER triggerPOS_LOG_MASTER_Inserter
   PRINT 'Dropped triggerPOS_LOG_MASTER_Inserter'
   -- this trigger is no more needed !!!
END

GO

IF OBJECT_ID ('triggerPOS_LOG_DETAIL_Inserter', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER triggerPOS_LOG_DETAIL_Inserter
   PRINT 'Dropped triggerPOS_LOG_DETAIL_Inserter'
   -- this trigger is no more needed !!!
END

GO

IF OBJECT_ID ('triggerPOS_LOG_MASTER_Inserter_Updater', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER triggerPOS_LOG_MASTER_Inserter_Updater
   PRINT 'Dropped triggerPOS_LOG_MASTER_Inserter_Updater'
END

GO

CREATE TRIGGER triggerPOS_LOG_MASTER_Inserter_Updater
ON dbo.POS_LOG_MASTER
AFTER INSERT, UPDATE
AS
	-- Cancel trigger for replication context
	IF (SELECT CONTEXT_INFO())=0x77777
		RETURN

	INSERT INTO dbo.CashierDic
			( id, CashierNumber, CashierName )
	SELECT id=[dbo].[fGetHash]( CONVERT(NVARCHAR(255), cashier_number)),
	cashier_number, cashier_name FROM INSERTED
	WHERE cashier_number IS NOT NULL AND
		cashier_name IS NOT NULL AND 
		cashier_number NOT IN ( SELECT CashierNumber FROM dbo.CashierDic )
	GROUP BY cashier_number, cashier_name
		
	INSERT INTO dbo.Events
	        ( pos_log_master_id ,
	          pos_log_detail_id ,
	          Funct_id ,
	          Cashier_id ,
	          Pos_id ,
	          [TimeStamp],
	          Fraction,
	          Cause
	        )
	SELECT 
		i._id,    -- pos_log_master_id 
		NULL,	  -- pos_log_detail_id 
		f.id,	  -- Funct_id 
		c.id,	  -- Cashier_id 
		p.id,	  -- Pos_id
		CASE      -- TimeStamp
          WHEN i.date_end IS NULL THEN i.date_begin
          ELSE i.date_end
        END,
        CASE      -- Fraction
          WHEN i.fraction_end IS NULL THEN i.fraction
          ELSE i.fraction_end
        END,
        CASE      -- Cause
          WHEN EXISTS(select * from deleted) THEN 'UPDATE'
          ELSE 'INSERT'
        END
	FROM INSERTED i
	INNER JOIN dbo.FuncDic f ON f.FunctionNumber = i.FunctionNumber
	INNER JOIN dbo.CashierDic c ON c.CashierNumber = i.cashier_number
	INNER JOIN dbo.PosDic p ON p.PosGUID = i.pos_id
	WHERE 
		i.cashier_number IS NOT NULL AND
		i.FunctionNumber IS NOT NULL AND
		i.pos_id IS NOT NULL
GO

PRINT 'Created triggerPOS_LOG_MASTER_Inserter_Updater'

GO


------------------------------------------------------------------------

IF OBJECT_ID ('triggerPOS_LOG_DETAIL_Inserter_Updater', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER triggerPOS_LOG_DETAIL_Inserter_Updater
   PRINT 'Dropped triggerPOS_LOG_DETAIL_Inserter_Updater'
END

GO

CREATE TRIGGER triggerPOS_LOG_DETAIL_Inserter_Updater
ON dbo.POS_LOG_DETAIL
AFTER INSERT, UPDATE
AS	
	-- Cancel trigger for replication context
	IF (SELECT CONTEXT_INFO())=0x77777
		RETURN

	INSERT INTO dbo.Events
	        ( pos_log_master_id ,
	          pos_log_detail_id ,
	          Funct_id ,
	          Cashier_id ,
	          Pos_id ,
	          [TimeStamp],
	          Fraction,
	          Cause
	        )
	SELECT 
		m._id,      -- pos_log_master_id corresponded to based POS_LOG_MASTER record
		i._id,	    -- pos_log_detail_id 
		f.id,	    -- Funct_id 
		c.id,	    -- Cashier_id 
		p.id,	    -- Pos_id 
		i.date,     -- TimeStamp
		i.fraction, -- Fraction
		CASE        -- Cause
          WHEN EXISTS(select * from deleted) THEN 'UPDATE'
          ELSE 'INSERT'
        END
	FROM INSERTED i
	INNER JOIN dbo.POS_LOG_MASTER m ON m.check_id = i.id AND m.pos_id = i.pos_id 
	INNER JOIN dbo.FuncDic f ON f.FunctionNumber = i.FunctionNumber
	INNER JOIN dbo.CashierDic c ON c.CashierNumber = m.cashier_number
	INNER JOIN dbo.PosDic p ON p.PosGUID = i.pos_id
	WHERE 
		m.cashier_number IS NOT NULL AND
		i.FunctionNumber IS NOT NULL AND
		i.pos_id IS NOT NULL
	
GO

PRINT 'Created triggerPOS_LOG_DETAIL_Inserter_Updater'

GO

----------------------- Service procedure/function ------------------------


--	The following function iterates over the string looking for commas, 
--	and extracts the values one by one. The code is straightforward, 
--	and makes use of some of the string functions in T-SQL. 
--
--	The most complex part is the CASE expression which exists to handle 
--	the last value in the string correctly. 
--
--	This example shows how you could use this function:
--
--	CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
--	   SELECT P.ProductName, P.ProductID
--	   FROM   Northwind..Products P
--	   JOIN   iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
--	GO
--	EXEC get_product_names_iter '9, 12, 27, 37'


IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'iter$simple_intlist_to_tbl' AND type = 'TF')
BEGIN
   DROP FUNCTION iter$simple_intlist_to_tbl
   PRINT 'Dropped iter$simple_intlist_to_tbl'
END
GO

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = CHARINDEX(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (CONVERT(int, SUBSTRING(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END

GO
PRINT 'Created iter$simple_intlist_to_tbl'
GO

------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'iter$simple_nvarcharlist_to_tbl' AND type = 'TF')
BEGIN
   DROP FUNCTION iter$simple_nvarcharlist_to_tbl
   PRINT 'Dropped iter$simple_nvarcharlist_to_tbl'
END
GO

CREATE FUNCTION iter$simple_nvarcharlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number NVARCHAR(50) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = CHARINDEX(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES ( RTRIM( LTRIM( SUBSTRING(@list, @pos + 1, @valuelen ))))
      SELECT @pos = @nextpos
   END
   RETURN
END

GO
PRINT 'Created iter$simple_nvarcharlist_to_tbl'
GO

--------------------------  STORED PROCEDURES -----------------------------
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_test_Events' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_test_Events
   PRINT 'Dropped sp_test_Events'
END

GO

CREATE PROCEDURE [dbo].[sp_test_Events]
AS
BEGIN
   SELECT [FuncDic].[id],[FuncDic].[FunctionNumber],
          [FuncDic].[FunctionName], 
          COUNT([Events].[id]) AS [Count] FROM FuncDic, [Events]
   WHERE [Events].[Funct_id] = [FuncDic].[id]
   GROUP BY [FuncDic].[FunctionNumber],[FuncDic].[FunctionName],[FuncDic].[id]
   ORDER BY [FuncDic].[FunctionNumber]
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_test_ISPP' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_test_ISPP
   PRINT 'Dropped sp_test_ISPP'
END

GO

CREATE PROCEDURE [dbo].[sp_test_ISPP]
AS
BEGIN

    DECLARE @datetime_from DATETIME
    DECLARE @datetime_to DATETIME

    SET @datetime_from = '2001-01-01T01:01:01'
    SET @datetime_to = '2020-01-01T01:01:01'

	SET NOCOUNT ON
	DECLARE @CashierEventList TABLE(CashierNumber int, 
				CashierName nvarchar(255), 
				FunctionNumber int, 
				FunctionName nvarchar(255), 
				Count int)

	INSERT INTO @CashierEventList
	exec [dbo].[sp_getCashierEventList]
			@CashierListNum = NULL,
			@from = @datetime_from,
			@to = @datetime_to

	DECLARE @CashierEventListCount int
	SELECT @CashierEventListCount = sum(Count) from @CashierEventList

	DECLARE @PosEventList TABLE(StoreGUID int, 
				StoreName nvarchar(255), 
				PosGUID int, 
				PosName nvarchar(255), 
				FunctionNumber int, 
				FunctionName nvarchar(255), 
				Count int)

	INSERT INTO @PosEventList
	EXEC	[dbo].[sp_getPosEventList]
			@PosListID = NULL,
			@from = @datetime_from,
			@to = @datetime_to
			
	DECLARE @PosEventListCount int
	SELECT @PosEventListCount = sum(Count) FROM @PosEventList

	DECLARE @EventListCount int
	SELECT @EventListCount = count(*) FROM Events
	SELECT @EventListCount as [Events from table Events],
			@PosEventListCount as [Events from sp_PosEventList], 
			 @CashierEventListCount as [Events sp_CashierEventList]

END

GO
PRINT 'Created sp_test_ISPP'
GO

--	процедура sp_AddStore служит для добавления сущности Store из Интеллекта
--	для дальнейшего связывания с сущностью POS (также из Интеллекта)
--
--	на вход принимает
--	@storeGUID nvarchar(50)   - любой уникальный идентификатор Store
--	@storeName nvarchar(1024) - связанное с этим идентификатором имя
--
--	особенности
--	   может быть вызван любое количество раз. первый раз запись будет вставлена
--	   в последующих разах - произойдет обновление (синхронизация) полей

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AddStore' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AddStore
   PRINT 'Dropped sp_AddStore'
END
GO
CREATE PROCEDURE sp_AddStore( @storeGUID nvarchar(50), @storeName nvarchar(1024) )
AS
IF @storeGUID IS NULL OR @storeName IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

IF NOT EXISTS (SELECT StoreGUID FROM [dbo].[StoreDic] WHERE [StoreGUID] = @storeGUID)
	INSERT INTO [dbo].[StoreDic]
           ([id], [StoreGUID], [StoreName])
    VALUES
           ([dbo].[fGetHash](@storeGUID) , @storeGUID, @storeName)
ELSE
	UPDATE [dbo].[StoreDic] SET [StoreName] = @storeName
	WHERE [StoreGUID] = @storeGUID

GO
PRINT 'Created sp_AddStore'
GO


--									ВАЖНО !!! 
--	перед началом использования системы необходимо заполнить две таблицы
--	[StoreDic] и [PosDic] для этого
--	   1. предварительно вызвать хранимую процедуру sp_AddStore для каждой сущности Store
--	   2. затем вызвать хранимую процедуру sp_AddPOS для каждого терминала указав
--	   StoreGUID в качестве идентификатора принадлежности объекта POS к объекту Store
--     как ссылку на его тип в Интелекте
--	   
--	отметим, что для варианта II обновлены будут только те Store объекты, на которые
--	есть ссылки из объектов POS
--
--	процедура sp_AddPOS служит для добавления сущностей POS из Интеллекта
--
--	на вход принимает
--	@posGUID nvarchar(50)     - уникальный идентификатор из Интеллекта
--	@posName nvarchar(1024)   - имя объекта POS
--	@storeGUID nvarchar(50)   - любой уникальный идентификатор Store
--
--	особенности
--	   может быть вызван любое количество раз. первый раз запись будет вставлена
--	   в последующих разах - произойдет обновление (синхронизация) полей
--

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AddPOS' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AddPOS
   PRINT 'Dropped sp_AddPOS'
END
GO
CREATE PROCEDURE sp_AddPOS( 
	@posGUID nvarchar(50), 
	@posName nvarchar(1024), 
	@storeGUID nvarchar(50) )
AS

IF @posGUID IS NULL OR @posName IS NULL OR @storeGUID IS NULL  
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

IF NOT EXISTS (SELECT [PosGUID] FROM [dbo].[PosDic] WHERE [PosGUID] = @posGUID)
	INSERT INTO [dbo].[PosDic]
           ([id], [PosGUID], [PosName], [Store_id])
     SELECT [dbo].[fGetHash](@posGUID), @posGUID, @posName, s.id
     FROM dbo.StoreDic s 
     WHERE s.StoreGUID = @storeGUID
ELSE
	UPDATE [dbo].[PosDic] SET 
		[PosName] = @posName,
		[Store_id] = s.id
	 FROM dbo.PosDic p
	 INNER JOIN dbo.StoreDic s ON s.[StoreGUID] = @storeGUID
	 WHERE p.[PosGUID] = @posGUID

GO
PRINT 'Created sp_AddPOS'
GO

--	процедура sp_AddFunc служит для добавления всех записей FuncDic
--  представляющей из себя список всех возможных функций POS по
--
--	на вход принимает
--	@functionNumber int           - уникальный идентификатор
--	@functionName nvarchar(100)   - имя функции (операции)
--
--	особенности
--	   может быть вызван любое количество раз. первый раз запись будет вставлена
--	   в последующих разах - произойдет обновление (синхронизация) полей
--

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AddFunc' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AddFunc
   PRINT 'Dropped sp_AddFunc'
END
GO
CREATE PROCEDURE sp_AddFunc( 
	@functionNumber INT,         
	@functionName nvarchar(100) )    
AS

IF @functionNumber IS NULL OR @functionName IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

DECLARE @id uniqueidentifier
SET @id = [dbo].[fGetHash]( CONVERT(NVARCHAR(255), @functionNumber))

IF NOT EXISTS ( SELECT 1 FROM [dbo].[FuncDic] f WHERE f.FunctionNumber = @functionNumber)

	INSERT INTO [dbo].[FuncDic]([id],[FunctionNumber], [FunctionName])
	SELECT @id, @functionNumber, @functionName

ELSE

	UPDATE [dbo].[FuncDic] SET [FunctionName] = @functionName
	FROM dbo.[FuncDic] f
	WHERE f.FunctionNumber = @functionNumber

GO
PRINT 'Created sp_AddFunc'
GO


--	процедура sp_GetStoreList возвращает уникальный список всех
--	зарегестрированных сущностей Store
--	значений на вход не принимает

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_GetStoreList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_GetStoreList
   PRINT 'Dropped sp_GetStoreList'
END
GO
CREATE PROCEDURE sp_GetStoreList
AS
SELECT [StoreGUID]
      ,[StoreName]
  FROM [dbo].[StoreDic]
  
GO
PRINT 'Created sp_GetStoreList'
GO

--	хранимая процедура sp_getCashierList возвращает список зарегестрированных
--	кассиров в системе POS.
--	Отметим, что этот список заполняется автоматически при добавлении записей
--	в таблицы POS_LOG_*
--	первоначальное заполнение списка происходит на этапе исполнения оригинального скрипта
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getCashierList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getCashierList
   PRINT 'Dropped sp_getCashierList'
END
GO
CREATE PROCEDURE sp_getCashierList
AS
SELECT [CashierNumber]
      ,[CashierName]
FROM [dbo].[CashierDic]
ORDER BY [CashierName]

GO
PRINT 'Created sp_getCashierList'
GO


--	хранимая процедура sp_getFuncList возвращает список доступных ф-ций 

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getFuncList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getFuncList
   PRINT 'Dropped sp_getFuncList'
END
GO
CREATE PROCEDURE sp_getFuncList
AS
	SELECT f.FunctionNumber
		,f.FunctionName
	FROM [dbo].FuncDic f
	ORDER BY f.FunctionNumber

GO
PRINT 'Created sp_getPosList'
GO


--	хранимая процедура sp_getPosList возвращает зарегестрированный список касс
--	(объектов POS) с ссылкой на объект Store
--	параметы
--	@StoreGUIDList - разделенный запятой список GUID'ов для объектов Store
--					 параметр не обязательный. если равен NULL вернутся POS
--					 для всех объектов Store

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosList
   PRINT 'Dropped sp_getPosList'
END
GO
CREATE PROCEDURE sp_getPosList(@StoreGUIDList AS nvarchar(1024) )
AS
IF @StoreGUIDList IS NULL
BEGIN
	SELECT p.[PosGUID]
		,p.[PosName]
		,s.[StoreGUID]
		,s.[StoreName]
	FROM [dbo].[PosDic] p
	INNER JOIN dbo.StoreDic s ON s.[id] = p.[Store_id]
	ORDER BY s.[id], p.id
END
ELSE
BEGIN
	SELECT p.[PosGUID]
		,p.[PosName]
		,s.[StoreGUID]
		,s.[StoreName]
	FROM [dbo].[PosDic] p
	INNER JOIN dbo.StoreDic s ON s.[id] = p.[Store_id]
	WHERE s.[StoreGUID] IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@StoreGUIDList))
	ORDER BY s.[id], p.id
END

GO
PRINT 'Created sp_getPosList'
GO

--------------------------------------------------------------------------
--                           Аналитика                                  --
--------------------------------------------------------------------------

-- Функция для получения флага - показывать события с незаданным статусом или нет.
-- На входе идентификатор статусов событий
-- На выходе - показывать события с незаданным статусом или нет
--	@statusFilter - идентификатор статусов событий
--	@showWithNullStatusID - если больше нуля показывать события с незаданным статусом, иначе не показывать
IF  EXISTS (SELECT * FROM sys.objects 
			WHERE object_id = OBJECT_ID(N'[dbo].[fShowWithNullStatusID]') AND 
				  type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
   DROP FUNCTION [dbo].[fShowWithNullStatusID]
   PRINT 'Dropped fShowWithNullStatusID'
END
GO
CREATE FUNCTION [dbo].[fShowWithNullStatusID] (
@statusFilter AS UNIQUEIDENTIFIER)
	RETURNS int
AS
BEGIN
	DECLARE @defaultstatusID AS UNIQUEIDENTIFIER
	SET @defaultstatusID = [dbo].[fDefaultStatus]()

	return (SELECT COUNT(*)FROM dbo.[StatusFilterStatusDic]
			WHERE [StatusFilterID] = @statusFilter AND [StatusID] = @defaultstatusID)
END
GO
PRINT 'Created fShowWithNullStatusID'
GO

-------------------------------
-- Процедура получения данных по кассам
-- На входе список касс+ идентификатор статусов событий + Диапазон времен, 
-- на выходе таблица "касса - событие - количество событий"
--	@PosListID - список касс
--	@StatusFilter - идентификатор статусов событий, если NULL, то возвращаются события
--					 с любым статусом
--	@FunctionFilter - идентификатор фильтра функций POS, если NULL, то возвращаются события
--					 с любого типа
--	@from - начало временного диапазона
--	@to - конец временного диапазона

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosEventList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosEventList
   PRINT 'Dropped sp_getPosEventList'
END
GO
CREATE PROCEDURE [dbo].[sp_getPosEventList](
	@PosListID AS nvarchar(1024),
	@StatusFilter AS UNIQUEIDENTIFIER,
	@FunctionFilter AS UNIQUEIDENTIFIER,
	@from as datetime,
	@to as datetime )
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END

DECLARE @FilteredStatus TABLE (
StatusID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @FilteredStatus (StatusID)
	SELECT [StatusID]
	FROM dbo.[StatusFilterStatusDic] 
	WHERE [StatusFilterID]=@StatusFilter


DECLARE @showWithNullStatusID INT
SET @showWithNullStatusID = dbo.[fShowWithNullStatusID](@StatusFilter)


DECLARE  @PosFunctTable TABLE (
    Cnt  INT NOT NULL,
    Pos_id UNIQUEIDENTIFIER NOT NULL,
    FunctionNumber_id UNIQUEIDENTIFIER NOT NULL
    );

IF @PosListID IS NULL
BEGIN
	INSERT INTO @PosFunctTable (cnt, pos_id, FunctionNumber_id) 
	SELECT COUNT(e.id), Pos_id, Funct_id
	FROM dbo.Events e
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	LEFT JOIN dbo.StatusLog l ON l.EventID = e.id AND (l.[StatusLogID] = (SELECT TOP 1 [StatusLogID]
	     FROM dbo.[StatusLog] 
	     WHERE [EventID] = e.id
	     ORDER BY [ChangeDateTime] DESC))
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
	(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
	(@StatusFilter IS NULL 
		OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) 
		OR l.StatusID in (SELECT * FROM @FilteredStatus))
	GROUP BY Pos_id, Funct_id	
END
ELSE
BEGIN
	DECLARE @PosIds TABLE (Pos_id UNIQUEIDENTIFIER NOT NULL)

	INSERT INTO @PosIds (Pos_id)
	SELECT id FROM dbo.PosDic AS p
	INNER JOIN iter$simple_nvarcharlist_to_tbl(@PosListID) AS n ON p.PosGUID = n.number

	INSERT INTO @PosFunctTable (cnt, pos_id, FunctionNumber_id) 
	SELECT COUNT(*), e.Pos_id, e.Funct_id
	FROM dbo.Events e
	INNER JOIN @PosIds AS p ON e.Pos_id = p.Pos_id
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
	(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
	(@StatusFilter IS NULL OR 
		(SELECT TOP 1 [StatusID]
		 FROM dbo.[StatusLog] 
		 WHERE [EventID] = e.id
		 ORDER BY [ChangeDateTime] DESC) in (SELECT * FROM @FilteredStatus)
		OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) )
	GROUP BY e.Pos_id, e.Funct_id
END

SELECT s.StoreGUID, s.StoreName, 
       p.PosGUID, p.PosName, 
       f.FunctionNumber, f.FunctionName, 
       t.cnt AS [Count]
	FROM @PosFunctTable t
	LEFT JOIN dbo.FuncDic f ON f.id = t.FunctionNumber_id
	LEFT JOIN dbo.PosDic p ON p.id = t.Pos_id
	LEFT JOIN dbo.StoreDic s ON s.id = p.Store_id
ORDER BY f.FunctionNumber, s.StoreGUID, p.PosGUID
	
GO
PRINT 'Created sp_getPosEventList'
GO


-------------------------------
-- Процедура получения данных по кассирам
-- На входе список кассиров + идентификатор статусов событий + Диапазон времен, 
-- на выходе таблица "кассир - событие - количество событий"
--	@CashierListNum - список кассиров
--	@StatusFilter - идентификатор статусов событий, если NULL, то возвращаются события
--					 с любым статусом
--	@FunctionFilter - идентификатор фильтра функций POS, если NULL, то возвращаются события
--					 с любого типа
--	@from - начало временного диапазона
--	@to - конец временного диапазона

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getCashierEventList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getCashierEventList
   PRINT 'Dropped sp_getCashierEventList'
END
GO
CREATE PROCEDURE [dbo].[sp_getCashierEventList](
	@CashierListNum AS nvarchar(1024),
	@StatusFilter AS UNIQUEIDENTIFIER,
	@FunctionFilter AS UNIQUEIDENTIFIER,
	@from as datetime,
	@to as datetime )
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END

DECLARE @FilteredStatus TABLE (
StatusID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @FilteredStatus (StatusID)
	SELECT [StatusID]
	FROM dbo.[StatusFilterStatusDic] 
	WHERE [StatusFilterID]=@StatusFilter
    

DECLARE @showWithNullStatusID INT
SET @showWithNullStatusID = dbo.[fShowWithNullStatusID](@StatusFilter)


DECLARE  @CashierFunctTable TABLE (
    cnt  INT NOT NULL,
    Cashier_id UNIQUEIDENTIFIER NOT NULL,
    FunctionNumber_id UNIQUEIDENTIFIER NOT NULL );


IF @CashierListNum IS NULL
BEGIN
	INSERT INTO @CashierFunctTable (cnt, Cashier_id, FunctionNumber_id)
	SELECT COUNT(*), Cashier_id, Funct_id
	FROM dbo.Events e
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	LEFT JOIN dbo.StatusLog l ON l.EventID = e.id AND (l.[StatusLogID] = (SELECT TOP 1 [StatusLogID]
	     FROM dbo.[StatusLog] 
	     WHERE [EventID] = e.id
	     ORDER BY [ChangeDateTime] DESC))
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
	(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
	(@StatusFilter IS NULL 
		OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) 
		OR l.StatusID in (SELECT * FROM @FilteredStatus))
	GROUP BY Cashier_id, Funct_id	
END
ELSE
BEGIN
	DECLARE @Cashiers TABLE (Cashier_id UNIQUEIDENTIFIER NOT NULL)

	INSERT INTO @Cashiers (Cashier_id)
	SELECT id FROM dbo.CashierDic AS d
	INNER JOIN iter$simple_nvarcharlist_to_tbl(@CashierListNum) AS n ON d.CashierNumber = n.number


	INSERT INTO @CashierFunctTable (cnt, Cashier_id, FunctionNumber_id)
	SELECT COUNT(*), e.Cashier_id, e.Funct_id
	FROM dbo.Events e
	INNER JOIN @Cashiers AS c ON e.Cashier_id = c.Cashier_id
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
	(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
	(@StatusFilter IS NULL OR 
	(SELECT TOP 1 [StatusID]
	 FROM dbo.[StatusLog] 
	 WHERE [EventID] = e.id
	 ORDER BY [ChangeDateTime] DESC) in (SELECT * FROM @FilteredStatus)
	 OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) )
	GROUP BY e.Cashier_id, e.Funct_id	
END

	SELECT c.CashierNumber, c.CashierName, 
	       f.FunctionNumber, f.FunctionName, 
	       t.cnt AS [Count]
		FROM @CashierFunctTable t
		LEFT JOIN dbo.FuncDic f ON f.id = t.FunctionNumber_id
		LEFT JOIN dbo.CashierDic c ON c.id = t.Cashier_id
	ORDER BY f.FunctionNumber, c.CashierNumber

GO
PRINT 'Created sp_getCashierEventList'
GO

-- Процедура получения списка временных меток по событиям
-- На входе 
--     1) Диапазон времен 
--     2) код события
--     3) список Кассиров(cashier_number) или Касс(pos_id) (чистое логическое OR)
-- Если список по Кассирам пустой - (NULL или '') - то по всем кассам(кассирам)
-- если [pos_id] список не задан - (NULL или '') - то по всем кассам
--
-- На выход вернуть список времен
-- 
-- ВАЖНО Процедура возвращяет только первый 1000 записей !!!

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_PosCashierTimeList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_PosCashierTimeList
   PRINT 'Dropped sp_PosCashierTimeList'
END
GO
CREATE PROCEDURE sp_PosCashierTimeList(
   @CashierListGUID AS nvarchar(1024), -- NULL - не задано
   @PosListGUID AS NVARCHAR(1024),     -- NULL - не задано
   @functionNumber AS int,
   @from as datetime, @to as DATETIME)
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END

IF @CashierListGUID IS NULL
SELECT @CashierListGUID = ''
 
IF @PosListGUID IS NULL
SELECT @PosListGUID = ''


SELECT TOP 1000 [TimeStamp] 
FROM dbo.Events
WHERE 
	Funct_id = (SELECT id FROM dbo.FuncDic WHERE FunctionNumber = @functionNumber) AND
	[TimeStamp] >= @from AND [TimeStamp] <= @to AND
	( Cashier_id IN (SELECT id FROM dbo.CashierDic WHERE CashierNumber IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@CashierListGUID) )) OR
	  Pos_id IN ( SELECT id FROM dbo.PosDic WHERE PosGUID IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@PosListGUID) ) ) )
ORDER BY [TimeStamp]
		
GO
PRINT 'Created sp_PosCashierTimeList'
GO


-- В следующей процедуре надо предусмотреть "paging", т.е на входе номер начальной строки и количество записей.
--
-- Процедура получения детальной информации по событиям
-- На входе 
--     1) Диапазон времен 
--     2) код события 
--     3) Кассир(cashier_number) или Касса(pos_id) (чистое логическое OR)
-- Если список по Кассирам пустой - (NULL или '') - то по всем кассам(кассирам)
-- если [pos_id] список не задан - (NULL или '') - то по всем кассам
--
-- На выход вернуть список времен + Кассир(Касса)+ pos_id + (возможно, еще что-то понадобится)
--
-- примечание
-- @just_size - установив в 1 можно вместо отчета получить кол-во строк (всего)
-- в нормальном отчете всегда должен быть равен 0 (не NULL а именно "0")


IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_PosCashierDetailReport' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_PosCashierDetailReport
   PRINT 'Dropped sp_PosCashierDetailReport'
END
GO
CREATE PROCEDURE sp_PosCashierDetailReport(
   @CashierListNum AS nvarchar(1024), 
   @PosListNum AS NVARCHAR(1024),
   @from as datetime, @to as DATETIME,
   @just_size AS INT,
   @page AS INT, @pageSize AS INT )  -- page = 0,1,2...n
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END
IF @just_size IS NULL 
BEGIN
	PRINT '@just_size cannot be equal to NULL'
	RAISERROR('@just_size cannot be equal to NULL', 16, 1)
END
IF @just_size = 0 AND ( @page IS NULL OR @pageSize IS NULL)
BEGIN
	PRINT 'current page or/and page size is missing (equal NULL)'
	RAISERROR('current page or/and page size is missing (equal NULL)', 16, 1)
END

IF @CashierListNum IS NULL
SELECT @CashierListNum = ''
 
IF @PosListNum IS NULL
SELECT @PosListNum = ''


IF @just_size <> 0
BEGIN
	SELECT COUNT(*) AS [count] 
	FROM dbo.Events
	WHERE 
		[TimeStamp] >= @from AND [TimeStamp] <= @to AND
		( Cashier_id IN (SELECT id FROM dbo.CashierDic WHERE CashierNumber IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@CashierListNum) )) OR
		  Pos_id IN ( SELECT id FROM dbo.PosDic WHERE PosGUID IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@PosListNum) ) ) )
END
ELSE
BEGIN
	SELECT t.id, s.StoreGUID, s.StoreName,
	       p.PosGUID, p.PosName, 
	       c.CashierNumber, c.CashierName, 
	       f.FunctionNumber, f.FunctionName, 
	       t.[TimeStamp] 
	FROM dbo.Events t
	LEFT JOIN dbo.FuncDic f ON f.id = t.Funct_id
	LEFT JOIN dbo.PosDic p ON p.id = t.Pos_id
	LEFT JOIN dbo.StoreDic s ON s.id = p.Store_id
	LEFT JOIN dbo.CashierDic c ON c.id = t.Cashier_id
	WHERE 
		[TimeStamp] >= @from AND [TimeStamp] <= @to AND
		( Cashier_id IN (SELECT id FROM dbo.CashierDic WHERE CashierNumber IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@CashierListNum) )) OR
		  Pos_id IN ( SELECT id FROM dbo.PosDic WHERE PosGUID IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@PosListNum) ) ) )
	ORDER BY s.StoreGUID, p.PosGUID, t.[TimeStamp]
	
END
	
GO
PRINT 'Created sp_PosCashierDetailReport'
GO

-- В следующей процедуре надо предусмотреть "paging", т.е на входе номер начальной строки и количество записей.
--
-- Процедура получения детальной информации по событиям 
-- ОТ ВСЕХ КАСС И ВСЕХ КАССИРОВ
--
-- На входе 
--     1) Диапазон времен 
--     2) код события 
--
-- На выход вернуть список времен + Кассир(Касса)+ pos_id + (возможно, еще что-то понадобится)
--
-- примечание
-- @just_size - установив в 1 можно вместо отчета получить кол-во строк (всего)
-- в нормальном отчете всегда должен быть равен 0 (не NULL а именно "0")

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AllPosCashierDetailReport' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AllPosCashierDetailReport
   PRINT 'Dropped sp_AllPosCashierDetailReport'
END
GO
CREATE PROCEDURE sp_AllPosCashierDetailReport(
   @from as datetime, @to as DATETIME,
   @just_size AS INT,
   @page AS INT, @pageSize AS INT )  -- page = 0,1,2...n

AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END
IF @just_size IS NULL 
BEGIN
	PRINT '@just_size cannot be equal to NULL'
	RAISERROR('@just_size cannot be equal to NULL', 16, 1)
END
IF @just_size = 0 AND ( @page IS NULL OR @pageSize IS NULL)
BEGIN
	PRINT 'current page or/and page size is missing (equal NULL)'
	RAISERROR('current page or/and page size is missing (equal NULL)', 16, 1)
END



IF @just_size <> 0
BEGIN
	SELECT COUNT(*) AS [count] 
	FROM dbo.Events
	WHERE 
		[TimeStamp] >= @from AND [TimeStamp] <= @to 
END
ELSE
BEGIN
	SELECT t.id, s.StoreGUID, s.StoreName,
	       p.PosGUID, p.PosName, 
	       c.CashierNumber, c.CashierName, 
	       f.FunctionNumber, f.FunctionName, 
	       t.[TimeStamp] 
	FROM dbo.Events t
	LEFT JOIN dbo.FuncDic f ON f.id = t.Funct_id
	LEFT JOIN dbo.PosDic p ON p.id = t.Pos_id
	LEFT JOIN dbo.StoreDic s ON s.id = p.Store_id
	LEFT JOIN dbo.CashierDic c ON c.id = t.Cashier_id
	WHERE 
		[TimeStamp] >= @from AND [TimeStamp] <= @to
	ORDER BY s.StoreGUID, p.PosGUID, t.[TimeStamp]
END

GO
PRINT 'Created sp_AllPosCashierDetailReport'
GO

---------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_PosCashierTimeListDetail' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_PosCashierTimeListDetail
   PRINT 'Dropped sp_PosCashierTimeListDetail'
END
GO

-- Процедура получения расширенного списка временных меток по событиям
-- На входе 
--     1) список идентификаторов (в формате Intellect) кассиров 
--     2) список идентификаторов (в формате Intellect) касс
--     3) список идентификаторов операторов (см. dbo.PosOperator.PosOperatorID)
--     4) номер функции
--     5) временной интервал за который необходимо получить отчет
--     6) @statusFilter -- uniqueidentifier - ID фильтра, если NULL то выдать все события.
-- Если список по Кассирам пустой - (NULL или '') - то по всем кассам(кассирам)
-- если [pos_id] список не задан - (NULL или '') - то по всем кассам
-- если [@OperatorListGUID] список не задан - (NULL или '') - то по всем операторам
--
-- На выходе возвращаются rowsets со следующими колонками
--		event_type,		-- тип события M - связанный с глобальными операциями; D - с товаром
--		StoreName,		-- имя магазина (из Интеллекта)
--      StoreGUID       -- GUID (Intellect) магазина
--		PosName,		-- имя кассы (из Интеллекта)
--		PosGUID,		-- GUID из Интеллекта для связывания с камерой
--		TimeStamp,		-- время события
--		CashierName,	-- имя кассира
--      CashierNumber,  -- номер кассира
--      POS_LOG_MASTER._id  -- !new! идентификатор чека
--		check_id,	    -- внутренний идентификатор чека
--      check_number,	-- номер чека
--		item_code,		-- код товара
--		item_name,		-- имя товара
--		item_price,		-- цена товара (за единицу)
--		item_count,		-- количество товара
--		item_total,		-- общая цена товара
--		total,			-- общая цена покупки (в рамках всего чека)
--		agree,			-- получено денег
--		surrender,		-- сдача
--		card_number		-- номер дисконтной карты
-- 
-- 
-- ВАЖНО Процедура возвращяет только первый 10000 записей !!!


CREATE PROCEDURE dbo.sp_PosCashierTimeListDetail(
   @CashierListGUID AS nvarchar(1024), -- NULL - не задано
   @PosListGUID AS NVARCHAR(1024),     -- NULL - не задано
   @OperatorListGUID AS NVARCHAR(MAX), -- NULL - не задано
   @functionNumber AS int,
   @from as datetime, @to as datetime,
   @statusFilter AS uniqueidentifier)
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END

IF @CashierListGUID IS NULL
SELECT @CashierListGUID = ''
 
IF @PosListGUID IS NULL
SELECT @PosListGUID = ''

IF @OperatorListGUID IS NULL
SELECT @OperatorListGUID = ''

DECLARE @FilteredStatus TABLE (
StatusID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @FilteredStatus (StatusID)
	SELECT [StatusID]
	FROM dbo.[StatusFilterStatusDic] 
	WHERE [StatusFilterID] = @statusFilter

DECLARE @showWithNullStatusID INT
SET @showWithNullStatusID = dbo.[fShowWithNullStatusID](@StatusFilter)

DECLARE @Operators TABLE (
PosOperatorID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @Operators (PosOperatorID)
SELECT number FROM iter$simple_nvarcharlist_to_tbl(@OperatorListGUID) WHERE LEN(number) >= 36

SELECT TOP 10000 
    e.id AS EventID,
	CASE
      WHEN e.pos_log_detail_id IS NULL     THEN N'M'
      WHEN e.pos_log_detail_id IS NOT NULL THEN N'D'
      ELSE N'?'
    END AS event_type,
	s.StoreName,
	s.StoreGUID,
	p.PosName,
	p.PosGUID,
	e.[TimeStamp],
	e.Fraction,
	c.CashierName,
	c.CashierNumber,
    m._id,
    m.check_id,
    m.check_number,
    d.item_code,
    d.item_name,
    d.item_price,
    d.item_count,
    d.item_total,
    m.total,
    m.agree,
    m.surrender,
    m.card_number
FROM dbo.Events e
LEFT JOIN dbo.PosDic p ON p.id = e.Pos_id
LEFT JOIN dbo.StoreDic s ON s.id = p.Store_id
LEFT JOIN dbo.CashierDic c ON c.id = e.Cashier_id
LEFT JOIN dbo.POS_LOG_MASTER m ON m.[_id] = e.pos_log_master_id
LEFT JOIN dbo.POS_LOG_DETAIL d ON d.[_id] = e.pos_log_detail_id 
LEFT JOIN dbo.StatusLog l ON l.EventID = e.id AND (l.[StatusLogID] = (SELECT TOP 1 [StatusLogID]
	     FROM dbo.[StatusLog] 
	     WHERE [EventID] = e.id
	     ORDER BY [ChangeDateTime] DESC))
WHERE 
	e.Funct_id = (SELECT id FROM dbo.FuncDic WHERE FunctionNumber = @functionNumber) AND
	e.[TimeStamp] >= @from AND [TimeStamp] <= @to AND
	( e.Cashier_id IN (SELECT id FROM dbo.CashierDic WHERE CashierNumber IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@CashierListGUID) )) OR
	  e.Pos_id IN ( SELECT id FROM dbo.PosDic WHERE PosGUID IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@PosListGUID) ) ) 
	  OR (l.PosOperatorID) in (SELECT * FROM @Operators) ) AND 
	  (@statusFilter IS NULL 
		OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) 
		OR l.StatusID in (SELECT * FROM @FilteredStatus) )
ORDER BY 
	e.[TimeStamp], e.[Fraction]

GO
PRINT 'Created sp_PosCashierTimeListDetail'
GO

---------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_StoreSubtitle' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_StoreSubtitle
   PRINT 'Dropped sp_StoreSubtitle'
END
GO

-- Процедура сохранения сообщения от чека
-- На входе 
--     1) тип таблицы POS_LOG_MASTER (M) или POS_LOG_DETAIL (D)
--     2) _id из таблицы POS_LOG_MASTER или POS_LOG_DETAIL 
--     3) Сообщение (текст, который будет сохранен в качестве строки титров)

CREATE PROCEDURE dbo.sp_StoreSubtitle(
   @MasterDetail AS NVARCHAR(1),  -- expecting 'M' or 'D'
   @PosLogID AS UNIQUEIDENTIFIER,
   @Message AS NVARCHAR(MAX))
AS
IF @PosLogID IS NULL OR @Message IS NULL
BEGIN
	PRINT '@PosLogID or @Message could not be a NULL'
	RAISERROR('@PosLogID or @Message could not be a NULL', 16, 1)
END

IF @MasterDetail = N'M'
BEGIN
	UPDATE dbo.Events
	SET [Message] = @Message
	WHERE pos_log_master_id = @PosLogID AND pos_log_detail_id IS NULL AND [Message] IS NULL
END
ELSE IF @MasterDetail =  N'D'
BEGIN
	UPDATE dbo.Events
	SET [Message] = @Message
	WHERE pos_log_detail_id = @PosLogID AND [Message] IS NULL
END
ELSE
BEGIN
	PRINT ('Unexpecting switch letter ' + @MasterDetail)
	RAISERROR ('Unexpecting switch letter', 16, 1)
END

GO
PRINT 'Created sp_StoreSubtitle'
GO

---------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosSubtitlesByCheckID' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosSubtitlesByCheckID
   PRINT 'Dropped sp_getPosSubtitlesByCheckID'
END
GO

-- Процедура выборки сообщений принадлежащих одному чеку
-- по ключу (_id) POS_LOG_MASTER
-- На входе 
--     1) PK (_id ) из LOG_POS_MASTER 
-- на выходе 
--      @cashier_name    -- имя кассира  	
--      @pos_name        -- название кассы
--		
--		(rowset)
--		[TimeStamp],     -- время собфтия
--		[Fraction],      -- дробная составляющая времени
--		FunctionNumber,  -- номер функции
--		FunctionName,    -- имя функции
--		[Message]        -- сообщение subtitle

CREATE PROCEDURE dbo.sp_getPosSubtitlesByCheckID(
   @pos_log_master_id UNIQUEIDENTIFIER,
   @cashier_name AS NVARCHAR(MAX) OUTPUT,
   @pos_name AS NVARCHAR(MAX) OUTPUT )
AS
IF @pos_log_master_id IS NULL
BEGIN
	PRINT '@pos_log_master_id could not be a NULL'
	RAISERROR('@pos_log_master_id could not be a NULL', 16, 1)
END

SELECT 
  @cashier_name = m.cashier_name,
  @pos_name = p.PosName
FROM dbo.POS_LOG_MASTER m
INNER JOIN dbo.PosDic p ON m.pos_id = p.PosGUID
WHERE m._id = @pos_log_master_id

DECLARE @second_check_number NVARCHAR(255)
SELECT TOP 1 @second_check_number = second_check_number FROM POS_LOG_MASTER
WHERE _id = @pos_log_master_id

DECLARE @ids TABLE ( id UNIQUEIDENTIFIER )

IF (@second_check_number IS NOT NULL AND @second_check_number <> '' )
	BEGIN
		INSERT INTO @ids (id) 
		SELECT _id from POS_LOG_MASTER
		WHERE POS_LOG_MASTER.second_check_number = @second_check_number
	END
	ELSE
	BEGIN
		INSERT INTO @ids (id) VALUES (@pos_log_master_id)
	END

SELECT 
	e.[TimeStamp],
	e.[Fraction],
	f.FunctionNumber,
	f.FunctionName,
	e.[Message]
FROM dbo.[Events] e
INNER JOIN dbo.FuncDic f ON e.Funct_id = f.id
INNER JOIN @ids ids ON e.pos_log_master_id = ids.id
ORDER BY 
	[TimeStamp], [Fraction]

GO
PRINT 'Created sp_getPosSubtitlesByCheckID'
GO


---------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosSubtitles' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosSubtitles
   PRINT 'Dropped sp_getPosSubtitles'
END
GO

-- Процедура выборки сообщений принадлежащих одному чеку
-- по номеру кассы и по времени
-- На входе 
--     1) ID (Интеллект) объекта POS 
--     2) временная метка
-- Параметры на выход
--     1) текущий номер чека
--     2) текущие имя и номер кассира
--
-- Выходные параметры (rowset)
--     @check_id        - идентификатор найденого чека
--     @check_number - номер найденого чека
--     @cashier_number  - кассир
--     @cashier_name    - имя кассира

CREATE PROCEDURE dbo.sp_getPosSubtitles(
   @PosGUID AS NVARCHAR(50),	-- Intellect GUID for POS object
   @Time AS DATETIME,			-- Current time position
   @check_id AS INT OUTPUT,
   @check_number AS NVARCHAR(MAX) OUTPUT,
   @cashier_number AS NVARCHAR(255) OUTPUT,
   @cashier_name AS NVARCHAR(120) OUTPUT )
AS
IF @PosGUID IS NULL OR @Time IS NULL
BEGIN
	PRINT '@PosGUID or @Time could not be a NULL'
	RAISERROR('@PosGUID or @Time could not be a NULL', 16, 1)
END

DECLARE @minId UNIQUEIDENTIFIER
DECLARE @maxId UNIQUEIDENTIFIER
SELECT TOP 1
	@minId = m.[_id],
	@check_id = m.check_id,
	@check_number = m.check_number,
	@cashier_number = m.cashier_number,
	@cashier_name = m.cashier_name
FROM dbo.Events e
INNER JOIN dbo.POS_LOG_MASTER m ON m.[_id] = e.pos_log_master_id
INNER JOIN dbo.PosDic p ON p.id = e.Pos_id
WHERE 
	p.PosGUID = @PosGUID AND
	e.TimeStamp <= @Time
ORDER BY e.TimeStamp DESC

SELECT TOP 1
	@maxId = m.[_id],
	@check_id = m.check_id,
	@check_number = m.check_number,
	@cashier_number = m.cashier_number,
	@cashier_name = m.cashier_name
FROM dbo.Events e
INNER JOIN dbo.POS_LOG_MASTER m ON m.[_id] = e.pos_log_master_id
INNER JOIN dbo.PosDic p ON p.id = e.Pos_id
WHERE 
	p.PosGUID = @PosGUID AND
	e.TimeStamp >= @Time
ORDER BY e.TimeStamp 

-- Если искомое время @Time попадает внутрь чека
IF @minId = @maxId
BEGIN
	DECLARE @m_unused1 UNIQUEIDENTIFIER
	DECLARE @m_unused2 UNIQUEIDENTIFIER
	EXEC dbo.sp_getPosSubtitlesByCheckID @minId, @m_unused1, @m_unused2
END
		
GO

PRINT 'Created sp_getPosSubtitles'
GO


------------------------------------------------
-- CRU для StatusDic
------------------------------------------------

--	процедура sp_AddStatus служит для добавления варианта статуса для событий
--	для дальнейшего связывания с сущностью события
--
--	на вход принимает
--	@name nvarchar(MAX)   - имя статуса
--	@description nvarchar(MAX) - описание для статуса (может быть NULL)
--  @textColor int - цвет текста
--  @backColor int - цвет фона
--  @isEnabled int - доступен ли статус для установки оператором
--  @statusID uniqueidentifier OUTPUT - возвращаем guid добавленного статуса
--	особенности

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AddStatus' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AddStatus
   PRINT 'Dropped sp_AddStatus'
END
GO
CREATE PROCEDURE sp_AddStatus( 
	@name nvarchar(MAX),
	@description nvarchar(MAX),
	@textColor int,
	@backColor int,
	@isEnabled int,
	@statusID uniqueidentifier OUTPUT)
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

SET @statusID = NEWID()

INSERT INTO [dbo].[StatusDic]
        ([StatusID], [Name],[Description], [TextColor], [BackColor], [IsEnabled])
    VALUES
        (@statusID, @name, @description, @textColor, @backColor, @isEnabled)


GO
PRINT 'Created sp_AddStatus'
GO


--	процедура sp_getStatusList служит для получения списка всех возможных статусов для событий
--
--	на вход принимает

--	особенности

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getStatusList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getStatusList
   PRINT 'Dropped sp_getStatusList'
END
GO
CREATE PROCEDURE sp_getStatusList
AS

SELECT 
    [StatusID], [Name], [Description], [TextColor], [BackColor], [IsEnabled] 
FROM 
    [dbo].[StatusDic]

GO
PRINT 'Created sp_getStatusList'
GO

--	процедура sp_UpdateStatus служит для добавления варианта статуса для событий
--	для дальнейшего связывания с сущностью события
--
--	на вход принимает
--  @id uniqueidentifier - идентификатор статуса
--	@name nvarchar(MAX)   - имя статуса
--	@description nvarchar(MAX) - описание для статуса (может быть NULL)
--  @textColor int - цвет текста
--  @backColor int - цвет фона
--  @isEnabled int - разрешён или запрещён
--	особенности

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_UpdateStatus' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_UpdateStatus
   PRINT 'Dropped sp_UpdateStatus'
END
GO
CREATE PROCEDURE sp_UpdateStatus( @id uniqueidentifier, @name nvarchar(MAX), @description nvarchar(MAX), @textColor int, @backColor int, @isEnabled int )
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

BEGIN
	UPDATE [dbo].[StatusDic] SET 
	    [Name] = @name,
		[Description] = @description,
		[TextColor] = @textColor,
		[BackColor] = @backColor,
		[IsEnabled] = @isEnabled
	WHERE [StatusID] = @id
END

GO
PRINT 'Created sp_UpdateStatus'
GO

------------------------------------------------
-- CRUD для StatusFilter
------------------------------------------------

--	процедура sp_CreateStatusFilter служит для изменения фильтра статусов 
--
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра статусов
--	@name nvarchar(MAX)   - имя фильтра
--	@description nvarchar(MAX) - описание для фильтра (может быть NULL)
--	@statusList nvarchar(MAX) - список статусов, входящих в фильтр

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_CreateStatusFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_CreateStatusFilter
   PRINT 'Dropped sp_CreateStatusFilter'
END
GO
CREATE PROCEDURE sp_CreateStatusFilter( 
	@name nvarchar(MAX),
	@description nvarchar(MAX),
	@statusList nvarchar(MAX),
	@id uniqueidentifier OUTPUT)
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

BEGIN TRANSACTION CreateStatusFilter

DECLARE @MyTableVar table( [StatusFilterID] uniqueidentifier);
                           
INSERT INTO [dbo].[StatusFilter]
        ([Name],[Description])
        OUTPUT INSERTED.[StatusFilterID] INTO @MyTableVar
    VALUES
        (@name, @description)

SELECT TOP 1 @id=[StatusFilterID] FROM @MyTableVar

-- добавляем статусы связанные с фильтром
INSERT INTO [dbo].[StatusFilterStatusDic] ([StatusFilterID], [StatusID])
	SELECT f.[StatusFilterID], s.[StatusID]
	FROM dbo.[StatusDic] s, dbo.[StatusFilter] f
	WHERE	f.[StatusFilterID] = @id AND 
			s.[StatusID] in (SELECT * 
							 FROM iter$simple_nvarcharlist_to_tbl(@statusList))

COMMIT TRANSACTION CreateStatusFilter

GO
PRINT 'Created sp_CreateStatusFilter'
GO

--	процедура sp_getStatusFilterList служит для получения списка всех возможных фильтров статусов
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getStatusFilterList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getStatusFilterList
   PRINT 'Dropped sp_getStatusFilterList'
END
GO
CREATE PROCEDURE sp_getStatusFilterList
AS

SELECT 
   [StatusFilterID], [Name], [Description]
FROM 
    [dbo].[StatusFilter]

GO
PRINT 'Created sp_getStatusFilterList'
GO

--	процедура sp_getStatusListForStatusFilter служит для получения списка всех статусов 
--		заданного фильтра.
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра статусов
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getStatusListForStatusFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getStatusListForStatusFilter
   PRINT 'Dropped sp_getStatusListForStatusFilter'
END
GO
CREATE PROCEDURE sp_getStatusListForStatusFilter(@id uniqueidentifier)
AS

SELECT 
	s.[StatusID],
	s.[Name],
	s.[Description],
	s.[TextColor],
	s.[BackColor],
	s.[IsEnabled]
FROM 
	[dbo].[StatusDic] s, 
	[dbo].[StatusFilterStatusDic] sd
WHERE 
	sd.[StatusID] = s.[StatusID] AND sd.[StatusFilterID] = @id

GO
PRINT 'Created sp_getStatusFilterList'
GO

--	процедура sp_UpdateStatusFilter служит для изменения фильтра статусов 
--
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра статусов
--	@name nvarchar(MAX)   - имя фильтра
--	@description nvarchar(MAX) - описание для фильтра (может быть NULL)
--	@statusList nvarchar(MAX) - список статусов, входящих в фильтр

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_UpdateStatusFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_UpdateStatusFilter
   PRINT 'Dropped sp_UpdateStatusFilter'
END
GO
CREATE PROCEDURE sp_UpdateStatusFilter( 
	@id uniqueidentifier,
	@name nvarchar(MAX),
	@description nvarchar(MAX),
	@statusList nvarchar(MAX))
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

BEGIN TRANSACTION UpdateStatusFilter

-- удаляем все статусы связанные с фильтром
DELETE FROM [dbo].[StatusFilterStatusDic] 
WHERE [StatusFilterID] = @id 

-- добавляем все статусы связанные с фильтром
INSERT INTO [dbo].[StatusFilterStatusDic] ([StatusFilterID], [StatusID])
	SELECT f.[StatusFilterID], s.[StatusID]
	FROM dbo.[StatusDic] s, dbo.[StatusFilter] f
	WHERE	f.[StatusFilterID] = @id AND 
			s.[StatusID] in (SELECT * 
							 FROM iter$simple_nvarcharlist_to_tbl(@statusList))

BEGIN
	UPDATE [dbo].[StatusFilter] SET 
	    [Name] = @name,
		[Description] = @description
	WHERE [StatusFilterID] = @id
END

COMMIT TRANSACTION UpdateStatusFilter

GO
PRINT 'Created sp_UpdateStatusFilter'
GO

--	процедура sp_deleteStatusFilter удаляет заданный фильтр статусов
--		заданного фильтра.
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра статусов
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_deleteStatusFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_deleteStatusFilter
   PRINT 'Dropped sp_deleteStatusFilter'
END
GO
CREATE PROCEDURE sp_deleteStatusFilter(@id uniqueidentifier)
AS

DELETE FROM [dbo].[StatusFilter] 
WHERE [StatusFilterID] = @id 

GO
PRINT 'Created sp_deleteStatusFilter'
GO

------------------------------------------------
-- CRUD для FuncFilter
------------------------------------------------

--	процедура sp_CreateFuncFilter служит для изменения фильтра функций
--
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра
--	@name nvarchar(MAX)   - имя фильтра
--	@description nvarchar(MAX) - описание для фильтра (может быть NULL)
--	@statusList nvarchar(MAX) - список функций, входящих в фильтр

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_CreateFuncFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_CreateFuncFilter
   PRINT 'Dropped sp_CreateFuncFilter'
END
GO
CREATE PROCEDURE sp_CreateFuncFilter( 
	@name nvarchar(MAX),
	@description nvarchar(MAX),
	@funcList nvarchar(MAX),
	@id uniqueidentifier OUTPUT)
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

BEGIN TRANSACTION CreateFuncFilter
set @id = NEWID();
                           
INSERT INTO [dbo].[FuncFilter]
        (Id,[Name],[Description])
    VALUES
        (@id, @name, @description)

-- добавляем функции связанные с фильтром
INSERT INTO [dbo].[FuncFilterFuncDic] ([FuncFilterId], [FuncDicId])
	SELECT @id, d.id
	FROM dbo.[FuncDic] AS d
	INNER JOIN iter$simple_nvarcharlist_to_tbl(@funcList) AS t ON t.number=d.FunctionNumber

COMMIT TRANSACTION CreateFuncFilter

GO
PRINT 'Created sp_CreateFuncFilter'
GO

--	процедура sp_getFuncFilterList служит для получения списка всех возможных фильтров функций
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getFuncFilterList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getFuncFilterList
   PRINT 'Dropped sp_getFuncFilterList'
END
GO
CREATE PROCEDURE sp_getFuncFilterList
AS

SELECT 
   [Id], [Name], [Description]
FROM 
    [dbo].[FuncFilter]

GO
PRINT 'Created sp_getFuncFilterList'
GO

--	процедура sp_getFuncListForFuncFilter служит для получения списка всех функций 
--		заданного фильтра.
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра функций
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getFuncListForFuncFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getFuncListForFuncFilter
   PRINT 'Dropped sp_getFuncListForFuncFilter'
END
GO
CREATE PROCEDURE sp_getFuncListForFuncFilter(@id uniqueidentifier)
AS

SELECT 
	d.[id],
	d.[FunctionNumber],
	d.[FunctionName]
FROM 
	[dbo].[FuncFilterFuncDic] AS fd
	INNER JOIN [dbo].[FuncDic] AS d ON d.id=fd.FuncDicId AND fd.FuncFilterId=@id

GO
PRINT 'Created sp_getFuncListForFuncFilter'
GO

--	процедура sp_UpdateFuncFilter служит для изменения фильтра функций 
--
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра функций
--	@name nvarchar(MAX)   - имя фильтра
--	@description nvarchar(MAX) - описание для фильтра (может быть NULL)
--	@statusList nvarchar(MAX) - список функций, входящих в фильтр

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_UpdateFuncFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_UpdateFuncFilter
   PRINT 'Dropped sp_UpdateFuncFilter'
END
GO
CREATE PROCEDURE sp_UpdateFuncFilter( 
	@id uniqueidentifier,
	@name nvarchar(MAX),
	@description nvarchar(MAX),
	@funcList nvarchar(MAX))
AS
IF @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

BEGIN TRANSACTION UpdateFuncFilter

-- удаляем все статусы связанные с фильтром
DELETE FROM [dbo].[FuncFilterFuncDic] 
WHERE [FuncFilterID] = @id 

-- добавляем функции связанные с фильтром
INSERT INTO [dbo].[FuncFilterFuncDic] ([FuncFilterId], [FuncDicId])
	SELECT @id, d.id
	FROM dbo.[FuncDic] AS d
	INNER JOIN iter$simple_nvarcharlist_to_tbl(@funcList) AS t ON t.number=d.FunctionNumber

UPDATE [dbo].[FuncFilter] SET 
    [Name] = @name,
	[Description] = @description
WHERE [Id] = @id

COMMIT TRANSACTION UpdateFuncFilter

GO
PRINT 'Created sp_UpdateFuncFilter'
GO

--	процедура sp_DeleteFuncFilter удаляет заданный фильтр функций
--		заданного фильтра.
--	на вход принимает
--  @id uniqueidentifier - идентификатор фильтра
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_DeleteFuncFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_DeleteFuncFilter
   PRINT 'Dropped sp_DeleteFuncFilter'
END
GO
CREATE PROCEDURE sp_DeleteFuncFilter(@id uniqueidentifier)
AS

DELETE FROM [dbo].[FuncFilter] 
WHERE [Id] = @id 

GO
PRINT 'Created sp_DeleteFuncFilter'
GO

------------------------------------------------
-- CRU для PosOperator
------------------------------------------------
--	процедура sp_addPosOperator служит для добавления нового оператора
--
--	на вход принимает
--	@login nvarchar(100)  - логин
--	@name nvarchar(MAX)   - имя оператора
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_addPosOperator' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_addPosOperator
   PRINT 'Dropped sp_addPosOperator'
END
GO
CREATE PROCEDURE sp_addPosOperator( @login nvarchar(100), @name nvarchar(MAX))
AS
IF @login IS NULL OR @name IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

IF NOT EXISTS (SELECT [Login] FROM [dbo].[PosOperator] WHERE [Login]=@login)
	INSERT INTO [dbo].[PosOperator]
			([PosOperatorID], [Login], [Name])
		VALUES
			([dbo].[fGetHash]( CONVERT(NVARCHAR(255), @login)), @login, @name)
ELSE
    UPDATE [dbo].[PosOperator] SET [Name]=@name
	WHERE [Login]=@login

GO
PRINT 'Created sp_addPosOperator'
GO

-- процедура sp_getPosOperatorList служит для получения списка всех операторов
--
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosOperatorList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosOperatorList
   PRINT 'Dropped sp_getPosOperatorList'
END
GO
CREATE PROCEDURE sp_getPosOperatorList
AS

SELECT * FROM [dbo].[PosOperator]

GO
PRINT 'Created sp_getPosOperatorList'
GO

-- процедура sp_deletePosOperatorByLogin служит для удаления оператора по логину
--
--	на вход принимает
--	@login nvarchar(100)  - логин
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_deletePosOperatorByLogin' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_deletePosOperatorByLogin
   PRINT 'Dropped sp_deletePosOperatorByLogin'
END
GO
CREATE PROCEDURE sp_deletePosOperatorByLogin( @login nvarchar(100))
AS
IF @login IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

DELETE FROM [dbo].[PosOperator] WHERE [Login] = @login

GO
PRINT 'Created sp_deletePosOperatorByLogin'
GO

-- процедура sp_deletePosOperatorByID служит для удаления оператора по ID
--
--	на вход принимает
--	@id nvarchar(MAX)  - ID
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_deletePosOperatorByID' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_deletePosOperatorByID
   PRINT 'Dropped sp_deletePosOperatorByID'
END
GO
CREATE PROCEDURE sp_deletePosOperatorByID( @id nvarchar(MAX))
AS
IF @id IS NULL
BEGIN
	PRINT 'store proc parameters are missing / NULL value occured'
	RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
END

DELETE FROM [dbo].[PosOperator] WHERE [PosOperatorID] = @id

GO
PRINT 'Created sp_deletePosOperatorByID'
GO

------------------------------------------------
-- Работа со StatusLog
------------------------------------------------

--	процедура sp_AddStatusLog служит для установки оператором статуса для события
--
--	на вход принимает
--  @eventId uniqueidentifier - идентификатор события для которого устанавливается статус
--  @posOperatorId uniqueidentifier - идентификатор оператора POS 
--  @changeDateTime uniqueidentifier - время изменения статуса
--  @newStatusId uniqueidentifier - идентификатор статуса

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_AddStatusLog' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_AddStatusLog
   PRINT 'Dropped sp_AddStatusLog'
END
GO
CREATE PROCEDURE sp_AddStatusLog( @eventId uniqueidentifier, @posOperatorId uniqueidentifier, @changeDateTime datetime, @newStatusId uniqueidentifier)
AS

DECLARE @oldStatusId uniqueidentifier

SELECT TOP 1
	@oldStatusId = [dbo].[StatusLog].[StatusID]
FROM [dbo].[StatusLog] 
WHERE [dbo].[StatusLog].[EventID] = @eventId 
ORDER BY [dbo].[StatusLog].[ChangeDateTime] DESC

IF (@oldStatusId IS NULL) OR (@oldStatusId <> @newStatusId)
    INSERT INTO [dbo].[StatusLog] ([EventID], [StatusID], [PosOperatorID], [ChangeDateTime])
    VALUES (@eventId, @newStatusId, @posOperatorId, @changeDateTime)
ELSE
    PRINT 'sp_AddStatusLog [StatusLog] is already up to date'

GO
PRINT 'Created sp_AddStatusLog'
GO

--	процедура sp_getStatusLogList служит для получения истории изменения статусов для события
--
--	на вход принимает
--  @eventId uniqueidentifier - идентификатор события для которого устанавливается статус
--  возвращает таблицу с полями
--  [StatusName] статус
--  [PosOperatorName] имя оператора
--  [ChangeDateTime] дата изменения
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getStatusLogList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getStatusLogList
   PRINT 'Dropped sp_getStatusLogList'
END
GO
CREATE PROCEDURE sp_getStatusLogList( @eventId uniqueidentifier)
AS
IF @eventId IS NULL
BEGIN
	PRINT '@eventId could not be a NULL'
	RAISERROR('@eventId could not be a NULL', 16, 1)
END
SELECT
    [dbo].[StatusDic].[Name] as [StatusName], 
	[dbo].[PosOperator].[Name] as [PosOperatorName], 
	[dbo].[StatusLog].[ChangeDateTime]
FROM 
    [dbo].[StatusDic] 
	INNER JOIN [dbo].[StatusLog] ON [dbo].[StatusDic].[StatusID] = [dbo].[StatusLog].[StatusID]
	INNER JOIN [dbo].[PosOperator] ON [dbo].[StatusLog].[PosOperatorID] = [dbo].[PosOperator].[PosOperatorID]
WHERE [dbo].[StatusLog].[EventID] = @eventId
ORDER BY [dbo].[StatusLog].[ChangeDateTime] DESC

GO
PRINT 'Created sp_getStatusLogList'
GO

-- Процедура получения расширенного списка временных меток по событиям
-- На входе 
--     1) список идентификаторов (в формате Intellect) кассиров 
--     2) список идентификаторов (в формате Intellect) касс
--     3) список идентификаторов операторов (см. dbo.PosOperator.PosOperatorID)
--     4) номер функции
--     5) временной интервал за который необходимо получить отчет
--     6) @statusFilter -- uniqueidentifier - ID фильтра, если NULL то выдать все события.
-- Если список по Кассирам пустой - (NULL или '') - то по всем кассам(кассирам)
-- если [pos_id] список не задан - (NULL или '') - то по всем кассам
-- если [@OperatorListGUID] список не задан - (NULL или '') - то по всем операторам
--
-- На выходе возвращаются rowsets со следующими колонками
--		event_type,		-- тип события M - связанный с глобальными операциями; D - с товаром
--		StoreName,		-- имя магазина (из Интеллекта)
--      StoreGUID       -- GUID (Intellect) магазина
--		PosName,		-- имя кассы (из Интеллекта)
--		PosGUID,		-- GUID из Интеллекта для связывания с камерой
--		TimeStamp,		-- время события
--		CashierName,	-- имя кассира
--      CashierNumber,  -- номер кассира
--      POS_LOG_MASTER._id  -- !new! идентификатор чека
--		check_id,	    -- внутренний идентификатор чека
--      check_number,	-- номер чека
--		item_code,		-- код товара
--		item_name,		-- имя товара
--		item_price,		-- цена товара (за единицу)
--		item_count,		-- количество товара
--		item_total,		-- общая цена товара
--		total,			-- общая цена покупки (в рамках всего чека)
--		agree,			-- получено денег
--		surrender,		-- сдача
--		card_number		-- номер дисконтной карты
--      [StatusIDOld]   -- предыдущий статус события 
--      [StatusID]      -- текущий статус события
--      [PosOperatorName] -- оператор сменивший статус последним
--      [ChangeDateTime]  -- время последнего изменения статуса
-- 
-- ВАЖНО Процедура возвращяет только первый 10000 записей !!!
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_PosCashierTimeListDetailEx' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_PosCashierTimeListDetailEx
   PRINT 'Dropped sp_PosCashierTimeListDetailEx'
END
GO
CREATE PROCEDURE sp_PosCashierTimeListDetailEx(
   @CashierListGUID AS nvarchar(1024), -- NULL - не задано
   @PosListGUID AS NVARCHAR(1024),     -- NULL - не задано
   @OperatorListGUID AS NVARCHAR(MAX),     -- NULL - не задано
   @functionNumber AS int,
   @from as datetime, @to as datetime,
   @statusFilter AS uniqueidentifier)
AS

DECLARE @FilteredStatus TABLE (
StatusID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @FilteredStatus (StatusID)
	SELECT [StatusID]
	FROM dbo.[StatusFilterStatusDic] 
	WHERE [StatusFilterID] = @statusFilter

DECLARE @showWithNullStatusID INT
SET @showWithNullStatusID = dbo.[fShowWithNullStatusID](@StatusFilter)

DECLARE @Operators TABLE (
	ID UNIQUEIDENTIFIER NOT NULL)
IF @OperatorListGUID IS NOT NULL
BEGIN
	INSERT INTO @Operators (ID)
		SELECT number 
		FROM iter$simple_nvarcharlist_to_tbl(@OperatorListGUID) WHERE LEN(number) >= 36
END

DECLARE @PosList TABLE (
	ID UNIQUEIDENTIFIER NOT NULL)
IF @PosListGUID IS NOT NULL
BEGIN
	INSERT INTO @PosList (ID)
		SELECT id FROM dbo.PosDic AS d
		INNER JOIN iter$simple_nvarcharlist_to_tbl(@PosListGUID) AS n ON d.PosGUID = n.number
END
DECLARE @Cashiers TABLE (
	ID UNIQUEIDENTIFIER NOT NULL)
IF @CashierListGUID IS NOT NULL
BEGIN
	INSERT INTO @Cashiers (ID)
		SELECT id 
		FROM dbo.CashierDic AS c
		INNER JOIN iter$simple_nvarcharlist_to_tbl(@CashierListGUID) AS n ON c.CashierNumber = n.number
END

DECLARE @defaultstatusID AS UNIQUEIDENTIFIER
SET @defaultstatusID = [dbo].[fDefaultStatus]()

DECLARE @functionID AS UNIQUEIDENTIFIER
SELECT @functionID = id FROM dbo.FuncDic WHERE FunctionNumber = @functionNumber

SELECT TOP 10000 
	e.id AS EventID,
	CASE
      WHEN e.pos_log_detail_id IS NULL     THEN N'M'
      WHEN e.pos_log_detail_id IS NOT NULL THEN N'D'
      ELSE N'?'
    END AS event_type,
	s.StoreName,
	s.StoreGUID,
	p.PosName,
	p.PosGUID,
	e.[TimeStamp],
	e.Fraction,
	c.CashierName,
	c.CashierNumber,
    m._id,
    m.check_id,
    m.check_number,
    d.item_code,
    d.item_name,
    d.item_price,
    d.item_count,
    d.item_total,
    m.total,
    m.agree,
    m.surrender,
    m.card_number,
	CASE
      WHEN sd.Name IS NOT NULL THEN sd.StatusID
      ELSE @defaultstatusID
    END AS StatusID,
	lprev.StatusID AS StatusIDOld,
	l.ChangeDateTime AS ChangeDateTime,
	o.Login PosOperatorName
FROM dbo.Events e
LEFT JOIN dbo.PosDic p ON p.id = e.Pos_id
LEFT JOIN dbo.StoreDic s ON s.id = p.Store_id
LEFT JOIN dbo.CashierDic c ON c.id = e.Cashier_id
LEFT JOIN dbo.POS_LOG_MASTER m ON m.[_id] = e.pos_log_master_id
LEFT JOIN dbo.POS_LOG_DETAIL d ON d.[_id] = e.pos_log_detail_id
LEFT JOIN dbo.StatusLog l ON l.StatusLogID = (SELECT TOP 1 l1.StatusLogID FROM dbo.StatusLog l1 WHERE l1.EventID =e.id ORDER BY l1.ChangeDateTime DESC)
LEFT JOIN dbo.StatusDic sd ON sd.StatusID = l.StatusID AND l.EventID = e.id
LEFT JOIN dbo.StatusLog lprev ON lprev.StatusLogID = (SELECT TOP 1 l3.StatusLogID
					     FROM dbo.StatusLog l3 
					     WHERE l3.ChangeDateTime<l.ChangeDateTime AND l3.EventID=e.id ORDER BY l3.ChangeDateTime DESC)
LEFT JOIN dbo.PosOperator o ON o.PosOperatorID= l.PosOperatorID
WHERE 
	e.Funct_id = @functionID AND
	e.[TimeStamp] >= @from AND [TimeStamp] <= @to AND
	( e.Cashier_id IN (SELECT * FROM @Cashiers) OR
	  e.Pos_id IN ( SELECT * FROM @PosList )  OR
	  (l.PosOperatorID IN (SELECT * FROM @Operators)) ) AND 
    (@statusFilter IS NULL OR 
     (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) OR l.StatusID in (SELECT * FROM @FilteredStatus))
	  
ORDER BY 
	e.[TimeStamp], e.[Fraction]	

GO
PRINT 'Created sp_PosCashierTimeListDetailEx'
GO

-- Процедура получения данных по операторам
--
-- На вход принимает
-- @OperatorListNum nvarchar(2048) -- список операторов
-- @StatusFilter - идентификатор статусов событий, если NULL, то возвращаются события
--					 с любым статусом
--@FunctionFilter - идентификатор фильтра функций POS, если NULL, то возвращаются события
--					 с любого типа
-- @from datetime -- начальный момент времени
-- @to datetime -- конечный момент времени
--
-- На выходе возвращается таблица с полями
-- PosOperatorID  - идентификатор оператора
-- PosOperatorName - имя оператора
-- FunctionNumber - номер функции
-- FunctionName - имя функции
-- Count - количество событий
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_getPosOperatorEventList' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_getPosOperatorEventList
   PRINT 'Dropped sp_getPosOperatorEventList'
END
GO
CREATE PROCEDURE sp_getPosOperatorEventList(
     @OperatorListNum AS nvarchar(2048), 
	 @StatusFilter AS UNIQUEIDENTIFIER,
	 @FunctionFilter AS UNIQUEIDENTIFIER,
	 @from as datetime, 
	 @to as datetime )
AS
IF @from IS NULL OR @to IS NULL
BEGIN
	PRINT '@from..@to time period is missing / NULL value occured'
	RAISERROR('@from..@to time period is missing / NULL value occured', 16, 1)
END

DECLARE @FilteredStatus TABLE (
StatusID UNIQUEIDENTIFIER NOT NULL)

INSERT INTO @FilteredStatus (StatusID)
	SELECT [StatusID]
	FROM dbo.[StatusFilterStatusDic] 
	WHERE [StatusFilterID] = @StatusFilter

	

DECLARE @showWithNullStatusID INT
SET @showWithNullStatusID = dbo.[fShowWithNullStatusID](@StatusFilter)

	    
DECLARE  @OperatorFunctTable TABLE (
    cnt  INT NOT NULL,
    PosOperatorID UNIQUEIDENTIFIER NOT NULL,
    FunctionNumber_id UNIQUEIDENTIFIER NOT NULL );


IF @OperatorListNum IS NULL
BEGIN
	INSERT INTO @OperatorFunctTable (cnt, PosOperatorID, FunctionNumber_id)
	SELECT COUNT(e.id), l.PosOperatorID, e.Funct_id
	FROM dbo.Events e
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	LEFT JOIN dbo.StatusLog l ON l.EventID = e.id AND (l.[StatusLogID] = (SELECT TOP 1 [StatusLogID]
		FROM dbo.[StatusLog] 
		WHERE [EventID] = e.id
		ORDER BY [ChangeDateTime] DESC))
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
		(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
		(@StatusFilter IS NULL 
			OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) 
			OR l.StatusID in (SELECT * FROM @FilteredStatus))
	GROUP BY l.PosOperatorID, e.Funct_id	
END
ELSE
BEGIN
	INSERT INTO @OperatorFunctTable (cnt, PosOperatorID, FunctionNumber_id)
	SELECT COUNT(*), l.PosOperatorID, e.Funct_id
	FROM dbo.Events e
	LEFT JOIN dbo.FuncFilterFuncDic AS f ON (f.FuncFilterId = @FunctionFilter AND f.FuncDicId = Funct_id)
	LEFT JOIN dbo.StatusLog l ON l.EventID = e.id AND (l.[StatusLogID] = (SELECT TOP 1 [StatusLogID]
	    FROM dbo.[StatusLog] 
	    WHERE [EventID] = e.id
	    ORDER BY [ChangeDateTime] DESC))
	WHERE [TimeStamp] >= @from AND [TimeStamp] <= @to AND
		(@FunctionFilter IS NULL OR f.FuncFilterId IS NOT NULL) AND
		l.PosOperatorID IN (SELECT * FROM iter$simple_nvarcharlist_to_tbl(@OperatorListNum))
		AND
		(@StatusFilter IS NULL 
			OR (@showWithNullStatusID > 0 AND NOT EXISTS (SELECT TOP 1 * FROM dbo.[StatusLog] WHERE [EventID] = e.id)) 
			OR l.StatusID in (SELECT * FROM @FilteredStatus))
	GROUP BY l.PosOperatorID, e.Funct_id	
END

SELECT c.PosOperatorID, c.Name AS [PosOperatorName], 
	    f.FunctionNumber, f.FunctionName, 
	    t.cnt AS [Count]
	FROM @OperatorFunctTable t 
	LEFT JOIN dbo.FuncDic f ON f.id = t.FunctionNumber_id
	LEFT JOIN dbo.PosOperator c ON c.PosOperatorID = t.PosOperatorID
ORDER BY f.FunctionNumber, c.PosOperatorID 

GO
PRINT 'Created sp_getPosOperatorEventList'
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Replication]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Replication](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[Server] [nvarchar](255) NOT NULL,
		[Source] [nvarchar](255) NOT NULL,
		[User] [nvarchar](255) NOT NULL,
		[StartTime] [datetime] NOT NULL,
		[FinishTime] [datetime] NULL,
		[LastTime] [datetime] NULL,
		[EventCount] [int] NULL,
		[MasterCount] [int] NULL,
		[StatusLogCount] [int] NULL,
		[DetailCount] [int] NULL,
		[Message] [nvarchar](255) NULL,
		[Error] [int] NULL,
	 CONSTRAINT [PK_Replication] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
		PRINT 'Created dbo.Replication'
END
ELSE
BEGIN
	PRINT 'Table dbo.Replication is already exists'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE   
      TABLE_NAME = 'Replication' AND COLUMN_NAME = 'LastSyncVersion' )
  PRINT 'Replication.LastSyncVersion is already exists'
ELSE
BEGIN
	ALTER TABLE [dbo].[Replication] ADD LastSyncVersion bigint NULL
	PRINT 'Replication.LastSyncVersion column added.'

	--UPDATE CashierDic SET id=dbo.fGetHash(NULL)
	--WHERE CashierNumber=N'-1'
	--PRINT 'CashierDic.id updated for unknown cashiers with CashierNumber= -1.'
	
END

IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.FuncDic')))
BEGIN
	ALTER TABLE dbo.FuncDic ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR FuncDic ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.StoreDic')))
BEGIN
	ALTER TABLE dbo.StoreDic ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR StoreDic ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.PosDic')))
BEGIN
	ALTER TABLE dbo.PosDic ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR PosDic ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.CashierDic')))
BEGIN
	ALTER TABLE dbo.CashierDic ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR CashierDic ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.StatusDic')))
BEGIN
	ALTER TABLE dbo.StatusDic ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR StatusDic ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.StatusFilter')))
BEGIN
	ALTER TABLE dbo.StatusFilter ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR StatusFilter ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.PosOperator')))
BEGIN
	ALTER TABLE dbo.PosOperator ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR PosOperator ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.POS_LOG_MASTER')))
BEGIN
	ALTER TABLE dbo.POS_LOG_MASTER ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR POS_LOG_MASTER ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.POS_LOG_DETAIL')))
BEGIN
	ALTER TABLE dbo.POS_LOG_DETAIL ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR POS_LOG_DETAIL ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.Events')))
BEGIN
	ALTER TABLE dbo.[Events] ENABLE CHANGE_TRACKING
	PRINT 'CHANGE_TRACKING FOR Events ON.'
END	
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID((N'dbo.StatusLog')))
BEGIN
	ALTER TABLE dbo.StatusLog ENABLE CHANGE_TRACKING  
	PRINT 'CHANGE_TRACKING FOR StatusLog ON.'
END	

--	процедура sp_Replicate служит для загрузки данных pos с удаленного сервера
--	на текущий

--	на вход принимает
--	@server nvarchar(255)   - имя интеллек сервера осуществляющего сбор POS данных
--	@datasrc nvarchar(4000) - экземпляр SQL сервера с реплицируемыми данными данные.
--							 Например 10.0.0.134\SQLEXPRESS
--	@catalog nvarchar(255)	- Имя базы данных на сервере. Например POS
--	@user nvarchar(255)		- Имя пользователя которому доступно удаленно чтение
--							 данных из БД 10.0.0.134\SQLEXPRESS.POS.
--	@password nvarchar(255)	- Пароль на подключения пользователя.
--
--	особенности
--	   На момент работы создает подключение к удаленному серверу при помощи
-- механизма Linked Server. Данные полученные за последнюю минуту не реплицируются,
-- т.к. в них могут появиться события с одинаковым временем и они будут пропущены 
-- при следующей репликации.

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_Replicate' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_Replicate
   PRINT 'Dropped sp_Replicate'
END

GO

CREATE PROCEDURE [dbo].[sp_Replicate]
	@server nvarchar(255),
	@datasrc nvarchar(4000),
	@catalog nvarchar(255),
	@user nvarchar(255),
	@password nvarchar(255)
AS
BEGIN
	DECLARE @sql nvarchar(MAX);
	DECLARE @source nvarchar(MAX);
	DECLARE @masterInserted int;
	DECLARE @detailInserted int;
	DECLARE @eventsInserted int;
	DECLARE @statusLogInserted int;
	DECLARE @lastError int;
	DECLARE @step [nvarchar](50);
	DECLARE @startTime DateTime;
	DECLARE @replicationId int;
	
	------------------Prepare data--------------------
	IF @server IS NULL OR @datasrc IS NULL OR @user IS NULL OR @password IS NULL
	BEGIN
		PRINT 'store proc parameters are missing / NULL value occured'
		RAISERROR('store proc parameters are missing  / NULL value occured', 16, 1)
	END
	
	SET @catalog = ISNULL(@catalog, N'pos')
	
	SET @source=N'['+@datasrc+N'].'+N'['+@catalog+N']';
	
	INSERT INTO dbo.[Replication]
		(Server, Source, [User], StartTime)
	VALUES
		(@server, @source, @user, GETDATE())
	
	SET @replicationId=@@identity;
	------------------Reconnect to server--------------------
	--EXEC master.dbo.sp_dropserver 
	--  @server = @datasrc
	--, @droplogins = N'droplogins'
	
	BEGIN TRY 
		EXEC master.dbo.sp_dropserver 
		  @server = @datasrc
		, @droplogins = N'droplogins'
	END TRY
	BEGIN CATCH
	END CATCH
	
	BEGIN TRY
		SET @step=N'Connecting...'
		EXEC master.dbo.sp_addlinkedserver @server = @datasrc;
		SELECT @lastError = @@ERROR
		IF (@lastError <> 0) GOTO LINK_PROBLEM
		SET @step=N'Login setting...'
		EXEC sp_addlinkedsrvlogin
		 @datasrc
		 , N'FALSE'
		 , NULL
		 , @user
		 , @password;
		SELECT @lastError = @@ERROR
		IF (@lastError <> 0) GOTO LINK_PROBLEM
    END TRY
    BEGIN CATCH
		SELECT @lastError = @@ERROR
		IF (@lastError <> 0) GOTO LINK_PROBLEM
    END CATCH
	 
	SET REMOTE_PROC_TRANSACTIONS OFF
	 
	-----------------Getting start time of replication------------
	SET @step=N'Getting begin time and fraction...'
	DECLARE @beginTime DateTime;
	DECLARE @lastSyncVersion bigint;
	DECLARE @currentVersion bigint;
	
	--Looking for record about last successful replication from @server.
	SELECT TOP 1 @beginTime = LastTime, @lastSyncVersion = LastSyncVersion
	FROM dbo.[Replication]
	WHERE Server = @server AND error=0
	ORDER BY id DESC
	
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM

	-----------------Update unreplicated data------------
    IF @lastSyncVersion IS NULL
    BEGIN
		SET @step=N'Update unreplicated data...'
    
		SET @beginTime = ISNULL(@beginTime, N'1753-01-01')
		SET @lastSyncVersion = 0

		SET @sql = N' @source.dbo.[sp_repUpdateUnreplicatedData] @lastTime';
			
		SET @sql=REPLACE(@sql, '@source', @source);
		
		EXECUTE sp_executesql 
			  @sql
			, N'@lastTime DateTime'
			, @beginTime 

		SELECT @lastError = @@ERROR
		SET @lastError = @@ERROR
		IF (@lastError <> 0) GOTO LINK_PROBLEM
    END
	
	-----------------Getting current version of changes------------
	SET @step=N'Getting current version of changes...'
	SET @sql = N'@source.dbo.sp_repGetChangeTrackingCurrentVersion @currentVersion OUT';
		
	SET @sql=REPLACE(@sql, '@source', @source);
	
	EXECUTE sp_executesql 
		  @sql
		, N'@currentVersion bigint OUTPUT'
		, @currentVersion OUTPUT
		
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	
	PRINT N'Replication ' + @server	+N' from version ' 
	+ CAST(@lastSyncVersion as varchar(max)) 
	+ N' till '+ CAST(@currentVersion as varchar(max)) + N'...'
	
	IF (@currentVersion = @lastSyncVersion)
	BEGIN
		PRINT N'No changes till last replication.'
		SET @step=N'Replication skipped! (No changes)';
		GOTO LINK_PROBLEM
	END
	
	DECLARE @getChangesSql nvarchar(MAX)
	DECLARE @getChangesSqlParamDefinition nvarchar(MAX)
	SET @getChangesSql = 'INSERT INTO #@table SELECT * from openquery([@datasrc], N''[@catalog].dbo.sp_repGetChangesOf@table @lastSyncVersion, @currentVersion'')'
	SET @getChangesSql=REPLACE(@getChangesSql, '@datasrc', @datasrc);
	SET @getChangesSql=REPLACE(@getChangesSql, '@catalog', @catalog);
	SET @getChangesSql=REPLACE(@getChangesSql, '@lastSyncVersion', @lastSyncVersion);
	SET @getChangesSql=REPLACE(@getChangesSql, '@currentVersion', @currentVersion);

	-------------COPY TO #FuncDic--------------------	
	SET @step=N'Copy data to #FuncDic'
	SELECT TOP 0 * INTO #FuncDic FROM FuncDic
	SET @sql=REPLACE(@getChangesSql, '@table', N'FuncDic');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	
	---------------COPY TO #StoreDic-------------------	
	SET @step=N'Copy data to #StoreDic'
	
	SELECT TOP 0 * INTO #StoreDic FROM StoreDic
	SET @sql=REPLACE(@getChangesSql, '@table', N'StoreDic');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	
	-------------COPY TO #PosDic-------------------	
	SET @step=N'Copy data to #PosDic'
	
	SELECT TOP 0 * INTO #PosDic FROM PosDic
	SET @sql=REPLACE(@getChangesSql, '@table', N'PosDic');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM

	-------------COPY TO #CashierDic-------------------	
	SET @step=N'Copy data to #CashierDic'
	
	SELECT TOP 0 * INTO #CashierDic FROM CashierDic
	SET @sql=REPLACE(@getChangesSql, '@table', N'CashierDic');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	
	-------------COPY TO #StatusDic-------------------	
	SET @step=N'Copy data to #StatusDic'
	
	SELECT TOP 0 * INTO #StatusDic FROM StatusDic
	SET @sql=REPLACE(@getChangesSql, '@table', N'StatusDic');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	
	-------------COPY TO #PosOperator-------------------	
	SET @step=N'Copy data to #PosOperator'
	
	SELECT TOP 0 * INTO #PosOperator FROM PosOperator
	SET @sql=REPLACE(@getChangesSql, '@table', N'PosOperator');
	EXECUTE sp_executesql @sql
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
	---------------------------------------------------	

	-------------COPY TO #POS_LOG_MASTER-------------------	
	SET @step=N'Copy data to #POS_LOG_MASTER'

	SELECT TOP 0 * INTO #POS_LOG_MASTER FROM POS_LOG_MASTER
	SET @sql=REPLACE(@getChangesSql, '@table', N'POS_LOG_MASTER');
	EXECUTE sp_executesql @sql
	
	SELECT @masterInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
    
	-------------COPY TO #POS_LOG_DETAIL-------------------	
	SET @step=N'Copy data to #POS_LOG_DETAIL'
	
	SELECT TOP 0 * INTO #POS_LOG_DETAIL FROM POS_LOG_DETAIL
	SET @sql=REPLACE(@getChangesSql, '@table', N'POS_LOG_DETAIL');
	EXECUTE sp_executesql @sql
		
	SELECT @detailInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
    
	-------------COPY TO #Events-------------------	
	SET @step=N'Copy data to #Events'

	SELECT TOP 0 * INTO #Events FROM [Events]
	SET @sql=REPLACE(@getChangesSql, '@table', N'Events');
	EXECUTE sp_executesql @sql

	SELECT @eventsInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM

	-------------COPY TO #StatusLog-------------------	
	SET @step=N'Copy data to #StatusLog'
	
	SELECT TOP 0 * INTO #StatusLog FROM StatusLog
	SET @sql=REPLACE(@getChangesSql, '@table', N'StatusLog');
	EXECUTE sp_executesql @sql
		
	SELECT @statusLogInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO LINK_PROBLEM
        
    ----------------
	SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
	
	BEGIN TRANSACTION
	SET CONTEXT_INFO 0x77777
	---------------------------------------------------	
	SET @step=N'UPDATE dbo.FuncDic'
	UPDATE dbo.FuncDic
	SET FunctionNumber=src.FunctionNumber, FunctionName = src.FunctionName
	FROM dbo.FuncDic AS dst
	JOIN #FuncDic AS src ON dst.id=src.id

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.FuncDic'
	
	INSERT INTO dbo.FuncDic
	SELECT * FROM #FuncDic AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.FuncDic as dst WHERE dst.id = src.id)
	
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
    	
	---------------------------------------------------	
	SET @step=N'UPDATE dbo.StoreDic'
	UPDATE dbo.StoreDic
	SET StoreGUID=src.StoreGUID, StoreName = src.StoreName
	FROM dbo.StoreDic AS dst JOIN 
		#StoreDic AS src ON dst.id=src.id

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.StoreDic'

	INSERT INTO dbo.StoreDic
	SELECT * FROM #StoreDic AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.StoreDic AS dst WHERE src.id = dst.id)
	
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'UPDATE dbo.PosDic'

	UPDATE dbo.PosDic
	SET PosGUID=src.PosGUID, PosName = src.PosName, Store_id = src.Store_id
	FROM dbo.PosDic AS dst JOIN 
	#PosDic AS src ON dst.id=src.id

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
    ---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.PosDic'

	INSERT INTO dbo.PosDic
	SELECT * FROM #PosDic AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.PosDic AS dst WHERE src.id = dst.id)

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'UPDATE dbo.CashierDic'

	UPDATE dbo.CashierDic
	SET CashierNumber=src.CashierNumber, CashierName = src.CashierName
	FROM dbo.CashierDic AS dst JOIN 
		#CashierDic AS src ON dst.id=src.id

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.CashierDic'

	INSERT INTO dbo.CashierDic
	SELECT * FROM #CashierDic AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.CashierDic AS dst WHERE src.id = dst.id)

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'UPDATE dbo.StatusDic'

	UPDATE dbo.StatusDic
	SET Name=src.Name, Description = src.Description, TextColor = src.TextColor, BackColor = src.BackColor, IsEnabled = src.IsEnabled
	FROM dbo.StatusDic AS dst JOIN 
		#StatusDic AS src ON dst.StatusId=src.StatusId
		
	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.StatusDic'
	
	INSERT INTO dbo.StatusDic
	SELECT * FROM #StatusDic AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.StatusDic AS dst WHERE src.StatusId = dst.StatusId)

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	
	SET @step=N'UPDATE dbo.PosOperator'

	UPDATE dbo.PosOperator
	SET Name=src.Name, [Login] = src.[Login]
	FROM dbo.PosOperator AS dst JOIN 
		#PosOperator AS src ON dst.PosOperatorID=src.PosOperatorID

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	---------------------------------------------------	
	SET @step=N'INSERT INTO dbo.PosOperator'

	INSERT INTO dbo.PosOperator
	SELECT * FROM #PosOperator AS src
	WHERE NOT EXISTS 
	(SELECT * FROM dbo.PosOperator AS dst WHERE src.PosOperatorID = dst.PosOperatorID)

	SELECT @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
     
	-----------------Replicating POS_LOG_MASTER------------------	
	SET @step=N'Replicating POS_LOG_MASTER.'

	INSERT INTO dbo.POS_LOG_MASTER
	SELECT * FROM #POS_LOG_MASTER AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.POS_LOG_MASTER AS dst WHERE src._id = dst._id)
	
	SELECT @masterInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	-----------------Replicating POS_LOG_DETAIL.------------------	
	SET @step=N'Replicating POS_LOG_DETAIL.'
	
	INSERT INTO dbo.POS_LOG_DETAIL
	SELECT * FROM #POS_LOG_DETAIL AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.POS_LOG_DETAIL AS dst WHERE src._id = dst._id)
			  
	SELECT @detailInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	-----------------Replicating Events.------------------	
	SET @step=N'Replicating Events...'
	
	INSERT INTO dbo.Events
	SELECT * FROM #Events AS src
	WHERE NOT EXISTS (SELECT * FROM dbo.Events AS dst WHERE src.id = dst.id)

	SELECT @eventsInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
	-----------------Replicating Events.------------------	
	SET @step=N'Replicating StatusLog...'
	INSERT INTO dbo.StatusLog
	SELECT s.StatusLogID, s.EventID, s.StatusID, s.PosOperatorID, s.ChangeDateTime
	FROM #StatusLog as s 
	INNER JOIN dbo.[Events] as e ON e.id=s.EventID
	WHERE NOT EXISTS (SELECT * FROM dbo.StatusLog AS dst WHERE s.StatusLogID = dst.StatusLogID)

	SELECT @statusLogInserted = @@RowCount, @lastError = @@ERROR
    IF (@lastError <> 0) GOTO PROBLEM
    ------------------------------------------------------
	SET Context_Info 0;
	
	COMMIT
-- This is the code for reseting replication results. It is saved for future debuging.
IF 2*2<>4
BEGIN
	DELETE FROM dbo.StatusLog
	DELETE FROM dbo.[Events]
	DELETE FROM dbo.POS_LOG_DETAIL
	DELETE FROM dbo.POS_LOG_MASTER
	DELETE FROM dbo.PosOperator
	DELETE FROM dbo.StatusFilter
	DELETE FROM dbo.StatusDic
	DELETE FROM dbo.CashierDic
	DELETE FROM dbo.PosDic
	DELETE FROM dbo.StoreDic
	DELETE FROM dbo.FuncDic
	DELETE FROM dbo.[Replication]
END
	SET @step=N'Replication succeeded!';
PROBLEM:
IF (@lastError <> 0)
    ROLLBACK TRAN
LINK_PROBLEM:
	IF OBJECT_ID('tempdb..#FuncDic') IS NOT NULL
		DROP TABLE #FuncDic
	IF OBJECT_ID('tempdb..#StoreDic') IS NOT NULL
		DROP TABLE #StoreDic
	IF OBJECT_ID('tempdb..#PosDic') IS NOT NULL
		DROP TABLE #PosDic
	IF OBJECT_ID('tempdb..#CashierDic') IS NOT NULL
		DROP TABLE #CashierDic
	IF OBJECT_ID('tempdb..#StatusDic') IS NOT NULL
		DROP TABLE #StatusDic
	IF OBJECT_ID('tempdb..#PosOperator') IS NOT NULL
		DROP TABLE #PosOperator
	IF OBJECT_ID('tempdb..#POS_LOG_MASTER') IS NOT NULL
		DROP TABLE #POS_LOG_MASTER
	IF OBJECT_ID('tempdb..#POS_LOG_DETAIL') IS NOT NULL
		DROP TABLE #POS_LOG_DETAIL
	IF OBJECT_ID('tempdb..#Events') IS NOT NULL
		DROP TABLE #Events
	IF OBJECT_ID('tempdb..#StatusLog') IS NOT NULL
		DROP TABLE #StatusLog

IF (@lastError <> 0) 
	PRINT N'Replication error='+STR(@lastError)+N'. Step:'+@step
	EXEC master.dbo.sp_dropserver 
	  @server = @datasrc
	, @droplogins = N'droplogins'
	
	UPDATE dbo.[Replication]
	SET 
		  FinishTime=GETDATE()
		, EventCount = @eventsInserted
		, MasterCount = @masterInserted
		, DetailCount = @detailInserted
		, StatusLogCount = @statusLogInserted
		, [Message] = @step
		, Error = @lastError
		, LastSyncVersion = @currentVersion
	WHERE id=@replicationId
	
	SELECT * FROM dbo.[Replication]
	WHERE id=@replicationId
END
GO
PRINT 'Created sp_Replicate'


-- Set current version of database
EXEC spUpdateToRequirementVersion

COMMIT TRAN UpdatePos

------------------------------------ THE END ------------------------------------------

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangeTrackingCurrentVersion' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangeTrackingCurrentVersion
   PRINT 'Dropped sp_repGetChangeTrackingCurrentVersion'
END

GO

CREATE PROCEDURE [dbo].[sp_repGetChangeTrackingCurrentVersion]
	@currentVersion bigint OUT
AS
BEGIN
	SELECT @currentVersion = CHANGE_TRACKING_CURRENT_VERSION()
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repUpdateUnreplicatedData' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repUpdateUnreplicatedData
   PRINT 'Dropped sp_repUpdateUnreplicatedData'
END

GO

CREATE PROCEDURE [dbo].[sp_repUpdateUnreplicatedData]
	@lastTime datetime
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
	
	BEGIN TRANSACTION
	
		SET CONTEXT_INFO 0x77777

		UPDATE dbo.FuncDic SET FunctionName = FunctionName
		
		UPDATE dbo.StoreDic SET StoreName = StoreName
		
		UPDATE dbo.PosDic SET PosName = PosName
		
		UPDATE dbo.CashierDic SET CashierName = CashierName
		
		UPDATE dbo.StatusDic SET Name = Name
		
		UPDATE dbo.StatusFilter SET Name = Name
		
		UPDATE dbo.PosOperator SET Name = Name
		
		-- Т.к. изменения POS_LOG_MASTER и POS_LOG_DETAIL забираются через join с [Events]
		-- Обновляем только [Events].
		--UPDATE dbo.POS_LOG_MASTER SET pos_id = pos_id
		--	WHERE DATEADD(ms, fraction, date_begin) > @lastTime
			
		--UPDATE dbo.POS_LOG_DETAIL SET pos_id=pos_id
		--	WHERE DATEADD(ms, fraction, [date]) > @lastTime
			
		UPDATE dbo.[Events] SET Fraction=Fraction
			WHERE DATEADD(ms, fraction, [TimeStamp]) > @lastTime
		
		UPDATE dbo.StatusLog SET ChangeDateTime=ChangeDateTime
			WHERE ChangeDateTime > @lastTime

		SET Context_Info 0;

	COMMIT

END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfFuncDic' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfFuncDic
   PRINT 'Dropped sp_repGetChangesOfFuncDic'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfFuncDic]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES FuncDic, @lastSyncVersion) AS c
	LEFT OUTER JOIN FuncDic AS d ON d.id = c.id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfStoreDic' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfStoreDic
   PRINT 'Dropped sp_repGetChangesOfStoreDic'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfStoreDic]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES StoreDic, @lastSyncVersion) AS c
	LEFT OUTER JOIN StoreDic AS d ON d.id = c.id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfPosDic' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfPosDic
   PRINT 'Dropped sp_repGetChangesOfPosDic'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfPosDic]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES PosDic, @lastSyncVersion) AS c
	LEFT OUTER JOIN PosDic AS d ON d.id = c.id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfCashierDic' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfCashierDic
   PRINT 'Dropped sp_repGetChangesOfCashierDic'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfCashierDic]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES CashierDic, @lastSyncVersion) AS c
	LEFT OUTER JOIN CashierDic AS d ON d.id = c.id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfStatusDic' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfStatusDic
   PRINT 'Dropped sp_repGetChangesOfStatusDic'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfStatusDic]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES StatusDic, @lastSyncVersion) AS c
	LEFT OUTER JOIN StatusDic AS d ON d.StatusID = c.StatusID
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfStatusFilter' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfStatusFilter
   PRINT 'Dropped sp_repGetChangesOfStatusFilter'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfStatusFilter]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES StatusFilter, @lastSyncVersion) AS c
	LEFT OUTER JOIN StatusFilter AS d ON d.StatusFilterID = c.StatusFilterID
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfPosOperator' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfPosOperator
   PRINT 'Dropped sp_repGetChangesOfPosOperator'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfPosOperator]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES PosOperator, @lastSyncVersion) AS c
	LEFT OUTER JOIN PosOperator AS d ON d.PosOperatorID = c.PosOperatorID
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] in (N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfPOS_LOG_MASTER' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfPOS_LOG_MASTER
   PRINT 'Dropped sp_repGetChangesOfPOS_LOG_MASTER'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfPOS_LOG_MASTER]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT DISTINCT d.*
	FROM CHANGETABLE (CHANGES [Events], @lastSyncVersion) AS c
	INNER JOIN [Events] AS e ON e.id = c.id
	INNER JOIN POS_LOG_MASTER AS d ON e.pos_log_master_id = d._id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] IN(N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfPOS_LOG_DETAIL' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfPOS_LOG_DETAIL
   PRINT 'Dropped sp_repGetChangesOfPOS_LOG_DETAIL'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfPOS_LOG_DETAIL]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT DISTINCT d.*
	FROM CHANGETABLE (CHANGES [Events], @lastSyncVersion) AS c
	INNER JOIN [Events] AS e ON e.id = c.id
	INNER JOIN POS_LOG_DETAIL AS d ON e.pos_log_detail_id = d._id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] IN(N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfEvents' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfEvents
   PRINT 'Dropped sp_repGetChangesOfEvents'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfEvents]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES [Events], @lastSyncVersion) AS c
	LEFT OUTER JOIN [Events] AS d ON d.id = c.id
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] IN(N'I', N'U')
END

GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'sp_repGetChangesOfStatusLog' AND type = 'P')
BEGIN
   DROP PROCEDURE sp_repGetChangesOfStatusLog
   PRINT 'Dropped sp_repGetChangesOfStatusLog'
END

GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_repGetChangesOfStatusLog]
	@lastSyncVersion bigint,
	@currentVersion bigint
AS
BEGIN
	SELECT d.*
	FROM CHANGETABLE (CHANGES StatusLog, @lastSyncVersion) AS c
	LEFT OUTER JOIN StatusLog AS d ON d.StatusLogID = c.StatusLogID
	WHERE c.SYS_CHANGE_VERSION <= @CurrentVersion AND c.[SYS_CHANGE_OPERATION] IN(N'I', N'U')
END
	
GO