/****** Object:  Table [dbo].[tb_reportslist]    Script Date: 11/06/2019 16:48:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_reportslist]') AND type in (N'U'))
DROP TABLE [dbo].[tb_reportslist]
GO
/****** Object:  Table [dbo].[tb_reportslist]    Script Date: 11/06/2019 16:48:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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] [varchar] (50)NOT 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateFlagInDeviceDatabase]    Script Date: 11/06/2019 16:48:24 ******/
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: 11/06/2019 16:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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
GO
/****** Object:  StoredProcedure [dbo].[Pro_CreateAutoTrigger]    Script Date: 11/06/2019 16:48:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pro_CreateAutoTrigger]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Pro_CreateAutoTrigger]
GO
/****** Object:  StoredProcedure [dbo].[Pro_CreateAutoTrigger]    Script Date: 11/06/2019 16:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Pro_CreateAutoTrigger]	
AS
BEGIN	
	SET NOCOUNT ON;
	Declare @GetCurrentYear int
	Declare @GetCurrentMonth int
	Declare @finalMonth Varchar(10) 
	Declare @GetTableName Varchar(50) 
	Declare @CreateTriggerName Varchar(50) 
	Select @GetCurrentYear = DATEPART(YEAR,DATEADD(month,-5,GETDATE()))
	Select @GetCurrentMonth = DATEPART(MONTH,DATEADD(month,-5,GETDATE()))
	--select @GetCurrentYear ,@GetCurrentMonth 
	Declare @i int
	set @i=0
	while @i<=5
	begin									
		set @finalMonth=case when LEN(@GetCurrentMonth)=1 then '0'+Convert(varchar(10),@GetCurrentMonth) else Convert(varchar(2),@GetCurrentMonth) end 
		Set @GetTableName ='T_LG'+Convert(varchar,@GetCurrentYear)+ Convert(varchar,@finalMonth) 
		--select @GetTableName 
		IF EXISTS (select table_name from information_schema.tables where table_name = @GetTableName)
		BEGIN
			DECLARE @SQL1 nvarchar(max)='
			IF EXISTS (select dtDateTime from '+ @GetTableName +' t1 left join tb_reportslist t2 
			on dateadd(SS,t1.DEVDT+14400,''01/01/1970'') = t2.dtDateTime and t1.USRID = t2.nUserID where t2.dtDateTime is null
			and TNAKEY in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''10'',''11'',''12'',''13'',''14'',''15'',''16'',''255'')) 
			Begin
				insert into tb_reportslist(dtDateTime,nDeviceID,nUserID,nEvent,nTNAEvent,strUniqueKey) 
				select dateadd(SS,t1.DEVDT+14400,''01/01/1970''),DEVUID,USRID,EVT,TNAKEY,EVTLGUID from '+ @GetTableName +' t1 
				left join tb_reportslist t2 on dateadd(SS,t1.DEVDT+14400,''01/01/1970'')= t2.dtDateTime and t1.USRID = t2.nUserID where t2.dtDateTime is null
				and TNAKEY in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''10'',''11'',''12'',''13'',''14'',''15'',''16'',''255'')
				and t1.USRID is not null and t1.USRID<>''''
			End'
			--print(@SQL1)
			Execute(@SQL1)
		END
		if(@GetCurrentMonth=12)
		BEGIN
			set	@GetCurrentYear=@GetCurrentYear+1
			set @GetCurrentMonth=1
		END
		ELSE
		BEGIN
			SET @GetCurrentMonth=@GetCurrentMonth+1
		END
		SET @i=@i+1
	end
END
GO
/****** Object:  StoredProcedure [dbo].[Proc_Ta_UpdateProcessedDataForService]    Script Date: 11/25/2019 11:03:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Ta_UpdateProcessedDataForService]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Proc_Ta_UpdateProcessedDataForService]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Ta_UpdateProcessedDataForService]    Script Date: 11/25/2019 11:03:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- [dbo].[Proc_Ta_UpdateProcessedDataForService]'SELECT Top 1000 nIndex,ndeviceid,nuserid,dtDateTime,nTNAEvent,susername FROM tb_reportslist WHERE susername IS NULL AND nEvent in( 55)'
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Ta_UpdateProcessedDataForService]
	-- Add the parameters for the stored procedure here
	@UpdateQuery varchar(max)
AS
BEGIN
	SET NOCOUNT ON;
	Execute(@updateQuery)
END
GO