if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildGKL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuildGKL]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE  PROCEDURE [dbo].[BuildGKL];1 (@DT char(10),@FullRebuild bit = 1,@FullInf bit = 1) AS
begin
	declare @UserName varchar(50)
	declare @S nvarchar(2048)

	select @UserName = current_user
	-- обновление по необходимости
	if (@FullRebuild = 0)
	begin
	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_01]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	' begin '+
		' if not exists (select * from ['+@UserName+'].[GKL_01] where DT = '+''''+@DT+''''+') '+
		' begin '+
			' drop table ['+@UserName+'].[GKL_01] '+
			' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_01 from [dbo].[GetKartotekaList] (1 ,'+''''+@DT+''''+') as GKL'+
		' end '+
	' end '+
	' else '+
	' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_01 from [dbo].[GetKartotekaList] (1 ,'+''''+@DT+''''+') as GKL '
	exec (@S)
	
	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_02]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	' begin '+
		' if not exists (select * from ['+@UserName+'].[GKL_02] where DT = '+''''+@DT+''''+') '+
		' begin '+
			' drop table ['+@UserName+'].[GKL_02] '+
			' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_02 from [dbo].[GetKartotekaList] (2 ,'+''''+@DT+''''+') as GKL'+
		' end '+
	' end '+
	' else '+
	' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_02 from [dbo].[GetKartotekaList] (2 ,'+''''+@DT+''''+') as GKL '
	exec (@S)

	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_03]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	' begin '+
		' if not exists (select * from ['+@UserName+'].[GKL_03] where DT = '+''''+@DT+''''+') '+
		' begin '+
			' drop table ['+@UserName+'].[GKL_03] '+
			' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_03 from [dbo].[GetKartotekaList] (3 ,'+''''+@DT+''''+') as GKL'+
		' end '+
	' end '+
	' else '+
	' select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_03 from [dbo].[GetKartotekaList] (3 ,'+''''+@DT+''''+') as GKL '
	exec (@S)
	end	

	-- полное обновление
	if (@FullRebuild = 1)
	begin
	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_01]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	'drop table ['+@UserName+'].[GKL_01]'
	exec (@S)
	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_02]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	'drop table ['+@UserName+'].[GKL_02]'
	exec (@S)
	set @S = 
	'if exists (select * from dbo.sysobjects '+
	'where id = object_id(N''['+@UserName+'].[GKL_03]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '+
	'drop table ['+@UserName+'].[GKL_03]'
	exec (@S)
	set @S =
	'select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_01 from [dbo].[GetKartotekaList] (1 ,'+''''+@DT+''''+') as GKL'
	exec (@S)
	set @S =
	'select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_02 from [dbo].[GetKartotekaList] (2 ,'+''''+@DT+''''+') as GKL'
	exec (@S)
	set @S =
	'select GKL.*,'+''''+@DT+''''+' as DT, getdate() as DATE_INS '+' into ['+@UserName+'].GKL_03 from [dbo].[GetKartotekaList] (3 ,'+''''+@DT+''''+') as GKL'
	exec (@S)
	end
end
GO

CREATE  PROCEDURE [dbo].[BuildGKL];2 (@DT char(10),@FullRebuild bit = 1,@FullInf bit = 1) AS
begin
	declare @UserName nvarchar(50),@FullInfStr char(1)
	declare @S nvarchar(2048),@Prm nvarchar(512)
	declare @CurrDate char(10),@d datetime,@CalcDate datetime
		
	--Создаем набор списков картотеки на дату.
	select @d = getdate(), @UserName = current_user 
	if (@FullInf = 0) set @FullInfStr = '0' else set @FullInfStr = '1'
	set @currdate = 	convert(varchar(4),datepart(dd,@d),104)+'.'+
				convert(varchar(4),datepart(mm,@d),104)+'.'+
				convert(varchar(4),datepart(yy,@d),104)
	--if (@currdate = @DT) select @UserName = 'dbo' 
	
	
	--GKL01
	set @S = N'
	if not exists (select * from dbo.sysobjects 
	where id = object_id(N''['+@UserName+'].[GKL_01]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
	begin'+
	'	CREATE TABLE ['+@UserName+'].[GKL_01] (
			[ID] [int] IDENTITY (1, 1) NOT NULL ,
			[O_HMEN01_ID] [int] NOT NULL ,
			[REGION_COD] [int] NOT NULL ,
			[O_HMEN01_ID_REGION_COD] [int] ,
			[FIO] [nvarchar] (152) COLLATE Cyrillic_General_CI_AS NULL,
			[FAMILY] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[FSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[LSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[BIRTHDATE] [datetime] NULL ,
			[AGE] [int] NULL ,
			[DolgName] [nvarchar] (250) COLLATE Cyrillic_General_CI_AS NULL ,
			[DB_WORK] [datetime] NULL ,
			[DF_WORK] [datetime] NULL ,
			[WORKPLACE] [nvarchar] (2000) COLLATE Cyrillic_General_CI_AS NULL ,
			[LastClassName] [nvarchar] (150) COLLATE Cyrillic_General_CI_AS NULL ,
			[DT] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
			[DATE_INS] [datetime] NOT NULL ,
			[Hesh_FAMILY] AS (checksum([FAMILY])) 
			) ON [PRIMARY] '+
		'ALTER TABLE ['+@UserName+'].[GKL_01] WITH NOCHECK ADD 
			CONSTRAINT [PK_GKL_01] PRIMARY KEY  CLUSTERED ([ID])  ON [PRIMARY] '+ 
		'ALTER TABLE ['+@UserName+'].[GKL_01] ADD 
			CONSTRAINT [DF_GKL_01_DATE_INS] DEFAULT (getdate()) FOR [DATE_INS]'+
		'CREATE  INDEX [IX_GKL_01_2] ON ['+@UserName+'].[GKL_01]([O_HMEN01_ID_REGION_COD]) ON [PRIMARY]'+
	'end'
	exec (@S)
	
	--GKL02
	set @S = N'
	if not exists (select * from dbo.sysobjects 
	where id = object_id(N''['+@UserName+'].[GKL_02]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
	begin'+
	'	CREATE TABLE ['+@UserName+'].[GKL_02] (
			[ID] [int] IDENTITY (1, 1) NOT NULL ,
			[O_HMEN01_ID] [int] NOT NULL ,
			[REGION_COD] [int] NOT NULL ,
			[O_HMEN01_ID_REGION_COD] [int] ,
			[FIO] [nvarchar] (152) COLLATE Cyrillic_General_CI_AS NULL,
			[FAMILY] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[FSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[LSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[BIRTHDATE] [datetime] NULL ,
			[AGE] [int] NULL ,
			[DolgName] [nvarchar] (250) COLLATE Cyrillic_General_CI_AS NULL ,
			[DB_WORK] [datetime] NULL ,
			[DF_WORK] [datetime] NULL ,
			[WORKPLACE] [nvarchar] (2000) COLLATE Cyrillic_General_CI_AS NULL ,
			[LastClassName] [nvarchar] (150) COLLATE Cyrillic_General_CI_AS NULL ,
			[DT] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
			[DATE_INS] [datetime] NOT NULL ,
			[Hesh_FAMILY] AS (checksum([FAMILY])) 
			) ON [PRIMARY] '+
		'ALTER TABLE ['+@UserName+'].[GKL_02] WITH NOCHECK ADD 
			CONSTRAINT [PK_GKL_02] PRIMARY KEY  CLUSTERED ([ID])  ON [PRIMARY] '+ 
		'ALTER TABLE ['+@UserName+'].[GKL_02] ADD 
			CONSTRAINT [DF_GKL_02_DATE_INS] DEFAULT (getdate()) FOR [DATE_INS]'+
		'CREATE  INDEX [IX_GKL_02_2] ON ['+@UserName+'].[GKL_02]([O_HMEN01_ID_REGION_COD]) ON [PRIMARY]'+
	'end'
	exec (@S)

	--GKL03
	set @S = N'
	if not exists (select * from dbo.sysobjects 
	where id = object_id(N''['+@UserName+'].[GKL_03]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
	begin'+
	'	CREATE TABLE ['+@UserName+'].[GKL_03] (
			[ID] [int] IDENTITY (1, 1) NOT NULL ,
			[O_HMEN01_ID] [int] NOT NULL ,
			[REGION_COD] [int] NOT NULL ,
			[O_HMEN01_ID_REGION_COD] [int] ,
			[FIO] [nvarchar] (152) COLLATE Cyrillic_General_CI_AS NULL,
			[FAMILY] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[FSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[LSTNAME] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,
			[BIRTHDATE] [datetime] NULL ,
			[AGE] [int] NULL ,
			[DolgName] [nvarchar] (250) COLLATE Cyrillic_General_CI_AS NULL ,
			[DB_WORK] [datetime] NULL ,
			[DF_WORK] [datetime] NULL ,
			[WORKPLACE] [nvarchar] (2000) COLLATE Cyrillic_General_CI_AS NULL ,
			[LastClassName] [nvarchar] (150) COLLATE Cyrillic_General_CI_AS NULL ,
			[DT] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
			[DATE_INS] [datetime] NOT NULL ,
			[Hesh_FAMILY] AS (checksum([FAMILY])) 
			) ON [PRIMARY] '+
		'ALTER TABLE ['+@UserName+'].[GKL_03] WITH NOCHECK ADD 
			CONSTRAINT [PK_GKL_03] PRIMARY KEY  CLUSTERED ([ID])  ON [PRIMARY] '+ 
		'ALTER TABLE ['+@UserName+'].[GKL_03] ADD 
			CONSTRAINT [DF_GKL_03_DATE_INS] DEFAULT (getdate()) FOR [DATE_INS]'+
		'CREATE  INDEX [IX_GKL_03_2] ON ['+@UserName+'].[GKL_03]([O_HMEN01_ID_REGION_COD]) ON [PRIMARY]'+
	'end'
	exec (@S)
	-- обновление по необходимости
	if (@FullRebuild = 0)
	begin
		--GKL01
		set @S = N'
		if exists(select  * from ['+@UserName+'].[GKL_01])
			select  distinct top 1 @CalcDate =DT from ['+@UserName+'].[GKL_01] order by DT desc
		else	
			select @CalcDate = ''01.01.1900''
		'
		set @Prm = N'
		@CalcDate datetime OUTPUT'
		exec sp_executesql @S,@Prm,@CalcDate OUTPUT
		if (@CalcDate <> @DT) or (not @currdate between @DT and DATEADD(day,1,@DT)) 
		begin
			set @S = 'DELETE FROM ['+@UserName+'].[GKL_01]'
			exec (@S)
			set @S =			
			'INSERT INTO ['+@UserName+'].[GKL_01]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
			' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (1 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
			exec (@S)
		end
		--GKL02
		set @S = N'
		if exists(select  * from ['+@UserName+'].[GKL_02])
			select  distinct top 1 @CalcDate =DT from ['+@UserName+'].[GKL_02] order by DT desc
		else	
			select @CalcDate = ''01.01.1900''
		'
		set @Prm = N'
		@CalcDate datetime OUTPUT'
		exec sp_executesql @S,@Prm,@CalcDate OUTPUT
		if (@CalcDate <> @DT) or (not @currdate between @DT and DATEADD(day,1,@DT)) 
		begin
			set @S = 'DELETE FROM ['+@UserName+'].[GKL_02]'
			exec (@S)
			set @S =			
			'INSERT INTO ['+@UserName+'].[GKL_02]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
			' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (2 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
			exec (@S)
		end
		--GKL03
		set @S = N'
		if exists(select  * from ['+@UserName+'].[GKL_03]) 
			select  distinct top 1 @CalcDate =DT from ['+@UserName+'].[GKL_03] order by DT desc
		else	
			select @CalcDate = ''01.01.1900''
		'
		set @Prm = N'
		@CalcDate datetime OUTPUT'
		exec sp_executesql @S,@Prm,@CalcDate OUTPUT
		if (@CalcDate <> @DT) or (not @currdate between @DT and DATEADD(day,1,@DT)) 
		begin
			set @S = 'DELETE FROM ['+@UserName+'].[GKL_03]'
			exec (@S)
			set @S =			
			'INSERT INTO ['+@UserName+'].[GKL_03]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
			' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (3 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
			exec (@S)
		end
	end	
	-- обновление по требованию
	if (@FullRebuild = 1)
	begin
		--GKL01
		set @S = 'DELETE FROM ['+@UserName+'].[GKL_01]'
		exec (@S)
		set @S =			
		'INSERT INTO ['+@UserName+'].[GKL_01]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
		' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (1 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
		exec (@S)
		--GKL02
		set @S = 'DELETE FROM ['+@UserName+'].[GKL_02]'
		exec (@S)
		set @S =			
		'INSERT INTO ['+@UserName+'].[GKL_02]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
		' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (2 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
		exec (@S)
		--GKL03
		set @S = 'DELETE FROM ['+@UserName+'].[GKL_03]'
		exec (@S)
		set @S =			
		'INSERT INTO ['+@UserName+'].[GKL_03]	([O_HMEN01_ID], [REGION_COD], [O_HMEN01_ID_REGION_COD], 
				[FIO], [FAMILY], [FSTNAME], [LSTNAME], [BIRTHDATE],[AGE], 
				[DolgName], [DB_WORK], [DF_WORK],[WORKPLACE], [LastClassName],[DT]) '+
		' SELECT GKL.[O_HMEN01_ID], GKL.[REGION_COD], GKL.[O_HMEN01_ID_REGION_COD], 
				GKL.[FIO], GKL.[FAMILY], GKL.[FSTNAME], GKL.[LSTNAME], GKL.[BIRTHDATE], GKL.[AGE],
				GKL.[DolgName], GKL.[DB_WORK], GKL.[DF_WORK],
				GKL.[WORKPLACE], GKL.[LastClassName],'+
			''''+@DT+''''+' as DT from [dbo].[GetKartotekaList] (3 ,'+''''+@DT+''''+','+@FullInfStr+') as GKL'
		exec (@S)
	end	
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[BuildGKL]  TO [public]
GO

