IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_reportslist]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_reportslist](
	[nIndex] [int] IDENTITY(1,1) NOT NULL,
	[dtDateTime] [datetime] NULL,
	[m_Date] [int] NULL,
	[m_Time] [int] NULL,
	[nDeviceID] [int] NULL,
	[nEvent] [int] NULL,
	[nUserID] [int] NULL,
	[sUserName] [varchar](255) NULL,
	[nSource] [int] NULL,
	[bAdminCheck] [int] NULL,
	[nTNAEvent] [int] NULL,
	[strUniqueKey] [char](38) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Trigger [RawDataTableTrigger]    Script Date: 10/10/2017 16:00:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[RawDataTableTrigger]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
		-- Author:		Karthikeyan.R
		-- Create date: 
		-- Description:	
		-- =============================================
		CREATE TRIGGER [dbo].[RawDataTableTrigger] on [dbo].[T_LGTBIDX]
		   AFTER INSERT
				AS 
				BEGIN
					-- SET NOCOUNT ON added to prevent extra result sets from
					-- interfering with SELECT statements.
					SET NOCOUNT ON;
					-- Insert statements for trigger here
					declare @TableName nvarchar(Max)
					select @TableName=LGTABLENM FROM inserted
					declare @SQLquery nvarchar(Max)
					declare @USTDif nvarchar(10)
					set @USTDif=right(CAST(SYSDATETIMEOFFSET() as nvarchar(100)),5)
					set @SQLquery =''''
					set @SQLquery=N''Create  TRIGGER [dbo].[RawDataTrigger''+ @TableName +''] ON [dbo].[''+@TableName+''] 
		AFTER INSERT
			AS
				BEGIN
					DECLARE @CLKDATE NVARCHAR(25)
					DECLARE @CLKTIME NVARCHAR(25)
					DECLARE @ORGDATE DATETIME 
					DECLARE @PUNCH AS VARCHAR(5)
					DECLARE @CLK_ID AS VARCHAR(25)
					DECLARE @IO AS VARCHAR(5)
					DECLARE @LOG AS VARCHAR(1)
					DECLARE @cDate AS numeric(18,0)
					DECLARE @strUniqueKey NVARCHAR(255)
					Declare @nReaderIdn int
					Declare @nEventIdn int
					DECLARE punches CURSOR FOR   
					select  DEVDT,USRID,TNAKEY,DEVUID,EVT  from inserted where 
					USRID<>'''''''' and
					--nEventIdn in (''''32'''',''''43'''',''''44'''',''''45'''',''''46'''',''''47'''',''''48'''',''''49'''',''''55'''',''''58'''',''''59'''',''''99'''',''''151'''') and   Karthick Check this
					TNAKEY in (''''0'''',''''1'''',''''2'''',''''3'''',''''4'''',''''5'''',''''6'''',''''7'''',''''8'''',''''9'''',''''10'''',''''11'''',''''12'''',''''13'''',''''14'''',''''15'''',''''16'''',''''255'''')
					OPEN punches  
					FETCH NEXT FROM punches   
					INTO @cDate,@CLK_ID,@IO,@nReaderIdn,@nEventIdn
						WHILE @@FETCH_STATUS = 0  
							BEGIN
								set @ORGDATE=dateadd(SS,@cDate,''''01/01/1970 ''+cast(@USTDif as nvarchar(10))+'''''')
								set @strUniqueKey=@CLK_ID+@IO
								INSERT INTO dbo.tb_reportslist(dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent,strUniqueKey) 
								(
								select @ORGDATE,@nReaderIdn,@nEventIdn,@CLK_ID,@IO, @strUniqueKey 					
								)
								FETCH NEXT FROM punches   
								INTO @cDate,@CLK_ID,@IO,@nReaderIdn,@nEventIdn
							END		
					CLOSE punches;  
					DEALLOCATE punches;  
		end''
												
					IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].''+@TableName+'''') AND type in (N''U''))
						BEGIN
							IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[RawDataTrigger''+@TableName+'']''))
								BEGIN
									EXEC dbo.sp_executesql @statement = @SQLquery
								end
						End
					else
						BEGIN	
							declare @SQLtable nvarchar(Max)
							set @SQLtable=''
		SET ANSI_NULLS ON
		SET QUOTED_IDENTIFIER ON
		SET ANSI_PADDING ON
		CREATE TABLE [dbo].[''+@TableName+''](
			[EVTLGUID] [int] IDENTITY(1,1) NOT NULL,
			[SRVDT] [datetime2](7) NULL,
			[DEVDT] [int] NULL,
			[DEVUID] [int] NOT NULL,
			[PKTDEVID] [int] NOT NULL,
			[DEVLGIDX] [bigint] NOT NULL,
			[IMGLGUID] [varchar](32) NULL,
			[USRID] [varchar](32) NULL,
			[USRGRUID] [int] NULL,
			[EVT] [int] NULL,
			[CRDSL] [int] NULL,
			[TNAKEY] [int] NULL,
			[DRUID] [int] NULL,
			[ZNUID] [int] NULL,
			[HINT] [varchar](50) NULL,
		PRIMARY KEY CLUSTERED 
		(
			[EVTLGUID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
		 CONSTRAINT [UNIQUE_DEVLOG_''+@TableName+''] UNIQUE NONCLUSTERED 
		(
			[PKTDEVID] ASC,
			[DEVLGIDX] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]

		SET ANSI_PADDING OFF
		ALTER TABLE [dbo].[''+@TableName+''] ADD  DEFAULT ((1)) FOR [USRGRUID]
		''
						EXEC dbo.sp_executesql @statement = @SQLtable
						EXEC dbo.sp_executesql @statement = @SQLquery
						END
						
			
			
				END

'
GO
	/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateFlagInDeviceDatabase]    Script Date: 03/08/2017 14:17:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_TA_UpdateFlagInDeviceDatabase]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Proc_TA_UpdateFlagInDeviceDatabase]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateFlagInDeviceDatabase]    Script Date: 03/08/2017 14:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_TA_UpdateFlagInDeviceDatabase]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<R.Manikandan>
-- Create date: <08-11-2016>
-- Description:	<Updating flag in device database>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_TA_UpdateFlagInDeviceDatabase]
	-- Add the parameters for the stored procedure here
	@TableName varchar(max),
	@FlagName varchar(50),
	@nIndex bigint
AS
BEGIN
	SET NOCOUNT ON;
	Declare @DynamicQuery varchar(max)
	set @DynamicQuery =''''
	set @DynamicQuery= ''UPDATE ''+convert(varchar(max),@TableName) + '' SET '' + convert(varchar(50),@FlagName) + '' = 1 WHERE nIndex='' + convert(varchar(max),@nindex)
	Execute(@DynamicQuery)
END
' 
END
GO

			
EXEC dbo.sp_executesql @statement = N'Declare @TotalCount int
			Declare @Count int
			set @Count=1
			select @TotalCount=count(*) from T_LGTBIDX
			declare @TableName nvarchar(500)
			declare @SQLquery nvarchar(MAX)
			declare @USTDif nvarchar(10)
			set @USTDif=right(CAST(SYSDATETIMEOFFSET() as nvarchar(100)),5)
			while(@TotalCount>=@Count)
			begin
				select @TableName=LGTABLENM from T_LGTBIDX where TABLEIDXUID=@Count
				if @TableName<>''''
					begin
						set @SQLquery =''''
						set @SQLquery=N''Create  TRIGGER [dbo].[RawDataTrigger''+ @TableName +''] ON [dbo].[''+@TableName+''] 
					AFTER INSERT
						AS
							BEGIN
								DECLARE @CLKDATE NVARCHAR(25)
								DECLARE @CLKTIME NVARCHAR(25)
								DECLARE @ORGDATE DATETIME 
								DECLARE @PUNCH AS VARCHAR(5)
								DECLARE @CLK_ID AS VARCHAR(25)
								DECLARE @IO AS VARCHAR(5)
								DECLARE @LOG AS VARCHAR(1)
								DECLARE @cDate AS numeric(18,0)
								DECLARE @strUniqueKey NVARCHAR(255)
								Declare @nReaderIdn int
								Declare @nEventIdn int
								DECLARE punches CURSOR FOR   
								select  DEVDT,USRID,TNAKEY,DEVUID,EVT  from inserted where 
								USRID<>'''''''' and
								--nEventIdn in (''''32'''',''''43'''',''''44'''',''''45'''',''''46'''',''''47'''',''''48'''',''''49'''',''''55'''',''''58'''',''''59'''',''''99'''',''''151'''') and   Karthick Check this
								TNAKEY in (''''0'''',''''1'''',''''2'''',''''3'''',''''4'''',''''5'''',''''6'''',''''7'''',''''8'''',''''9'''',''''10'''',''''11'''',''''12'''',''''13'''',''''14'''',''''15'''',''''16'''',''''255'''')
								OPEN punches  
								FETCH NEXT FROM punches   
								INTO @cDate,@CLK_ID,@IO,@nReaderIdn,@nEventIdn
									WHILE @@FETCH_STATUS = 0  
										BEGIN
											set @ORGDATE=dateadd(SS,@cDate,''''01/01/1970 ''+cast(@USTDif as nvarchar(10))+'''''')
											set @strUniqueKey=@CLK_ID+@IO
											INSERT INTO dbo.tb_reportslist(dtDateTime,nDeviceID,nEvent,nUserID,nTNAEvent,strUniqueKey) 
											(
											select @ORGDATE,@nReaderIdn,@nEventIdn,@CLK_ID,@IO, @strUniqueKey 					
											)
											FETCH NEXT FROM punches   
											INTO @cDate,@CLK_ID,@IO,@nReaderIdn,@nEventIdn
										END		
								CLOSE punches;  
								DEALLOCATE punches;  
					end''
						IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].''+@TableName+'''') AND type in (N''U''))
							BEGIN
								IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[RawDataTrigger''+@TableName+'']''))
									BEGIN
										EXEC dbo.sp_executesql @statement = @SQLquery
									end
							End
					END
					set @Count=@Count+1
			END'