SET LANGUAGE russian
SET IMPLICIT_TRANSACTIONS OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET NOCOUNT OFF
SET LOCK_TIMEOUT -1
SET XACT_ABORT ON
SET ARITHABORT ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET DATEFIRST 1
SET DATEFORMAT DMY
SET QUOTED_IDENTIFIER ON
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[VX_UPGRADE_CLEAR]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE dbo.[VX_UPGRADE_CLEAR]

GO

CREATE       PROCEDURE [dbo].VX_UPGRADE_CLEAR
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[object_values_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table object_values_u
if exists (select * from dbo.sysobjects where id = object_id(N'[object_values]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('object_values')

if exists (select * from dbo.sysobjects where id = object_id(N'[object_var_values_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table object_var_values_u
if exists (select * from dbo.sysobjects where id = object_id(N'[object_var_values]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('object_var_values')

if exists (select * from dbo.sysobjects where id = object_id(N'[object_property_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table object_property_u
if exists (select * from dbo.sysobjects where id = object_id(N'[object_property]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('object_property')

if exists (select * from dbo.sysobjects where id = object_id(N'[s_navigator_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_navigator_u

if exists(select * from syscolumns sc where name = 'old_link' and id = OBJECT_ID('s_navigator'))
begin
	IF EXISTS (SELECT * FROM dbo.sysindexes WHERE [name] = 'tmp_index_for_old_link' and id = object_id(N'[dbo].[S_NAVIGATOR]'))
		drop index s_navigator.tmp_index_for_old_link
	alter table s_navigator
		drop column old_link
end

if exists (select * from dbo.sysindexes where name = N'IX_S_NAVIGATOR' and id = object_id(N'[dbo].[S_NAVIGATOR]'))
	drop index [dbo].[S_NAVIGATOR].[IX_S_NAVIGATOR]

if exists (select * from dbo.sysobjects where id = object_id(N'[s_navigator]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_navigator')

if exists (select * from dbo.sysobjects where id = object_id(N'[object_var_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table object_var_u
if exists (select * from dbo.sysobjects where id = object_id(N'[object_var]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('object_var')

if exists (select * from dbo.sysobjects where id = object_id(N'[s_property_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_property_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_property]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_property')

if exists (select * from sysobjects where id = OBJECT_ID(N's_permissions_u') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_permissions_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_permissions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_permissions')

if exists (select * from sysobjects where id = OBJECT_ID(N's_dbobjects_u') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_dbobjects_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_dbobjects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_dbobjects')

if exists (select * from sysobjects where id = OBJECT_ID(N's_tables_u') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_tables_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_tables')

if exists (select * from sysobjects where id = OBJECT_ID(N's_release_u') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_release_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_release]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_release')

if exists (select * from dbo.sysobjects where id = object_id(N'[s_objects_u]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table s_objects_u
if exists (select * from dbo.sysobjects where id = object_id(N'[s_objects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DBCC DBREINDEX ('s_objects')

if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_FORMS_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table REPORT_FORMS_U

if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_TABLES_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table REPORT_TABLES_U

if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_ROWS_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table REPORT_ROWS_U

if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_COLOUMNS_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table REPORT_COLOUMNS_U
if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_FORMULA_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table REPORT_FORMULA_U
--if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_CONDITIONS_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--	drop table REPORT_CONDITIONS_U
--if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_COND_VALUE_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--	drop table REPORT_COND_VALUE_U
--if exists (select * from sysobjects where id = OBJECT_ID(N'REPORT_CONTROL_PARITIES_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--	drop table REPORT_CONTROL_PARITIES_U
if exists (select * from sysobjects where id = OBJECT_ID(N'S_FILES_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table S_FILES_U
if exists (select * from sysobjects where id = OBJECT_ID(N'S_HISTORY_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table S_HISTORY_U
if exists (select * from sysobjects where id = OBJECT_ID(N'S_FILEPARTS_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table S_FILEPARTS_U
if exists (select * from sysobjects where id = OBJECT_ID(N'DICTION_U') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table DICTION_U
if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_OPTIONS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [REPORT_OPTIONS_U]
if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_CONTROL_ROWS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [REPORT_CONTROL_ROWS_U]
if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_CONTROL_RULES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [REPORT_CONTROL_RULES_U]

if exists (select * from dbo.sysobjects where id = object_id(N'u_obj') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [u_obj]

if exists (select * from dbo.sysobjects where id = object_id(N'u_prop') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [u_prop]


if exists (select * from dbo.sysobjects where id = object_id(N'[EXPORT_FIELDS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [EXPORT_FIELDS_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[EXPORT_BLOCKS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [EXPORT_BLOCKS_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[EXPORT_MODELS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [EXPORT_MODELS_U]


if exists (select * from dbo.sysobjects where id = object_id(N'[S_BANK_FORMAT_BASE_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [S_BANK_FORMAT_BASE_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[S_GROUP_SID_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [S_GROUP_SID_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[DICTION_APP_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)   
	drop table [dbo].[DICTION_APP_U]  

if exists (select * from dbo.sysobjects where id = object_id(N'[OBJECT_VAR_U_DELETE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OBJECT_VAR_U_DELETE]

if exists (select * from dbo.sysobjects where id = object_id(N'[ECONOMY_OPER_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ECONOMY_OPER_U]

/*if exists (select * from dbo.sysobjects where id = object_id(N'[XOZ_OPER_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [XOZ_OPER_U]*/

if exists (select * from dbo.sysobjects where id = object_id(N'[JOURNAL_OPERATION_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [JOURNAL_OPERATION_U]


if exists (select * from dbo.sysobjects where id = object_id(N'[HISTORY_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [HISTORY_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[FAQ_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [FAQ_U]

if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_GROUP_KS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_GROUP_KS_U] 

if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_CONTROL_GROUP_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_CONTROL_GROUP_U 

if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_FORM_GROUPS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORM_GROUPS_U 

if exists (select * from dbo.sysobjects where id = object_id(N'[REPORT_FORM_GROUPS_REF_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORM_GROUPS_REF_U 

if exists (select * from dbo.sysobjects where id = object_id(N'[R_FILES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].R_FILES_U

if exists (select * from dbo.sysobjects where id = object_id(N'[R_FILEPARTS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].R_FILEPARTS_U

if exists (select * from dbo.sysobjects where id = object_id(N'[REMIND_QUERY_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REMIND_QUERY_U

----Удалить все временные талицы, заканчивающиеся на _U
--DECLARE @TABLE_NAME VARCHAR(256), @SQL_SCRIPT VARCHAR(4000)
--WHILE EXISTS (SELECT NAME FROM SYS.TABLES WHERE RIGHT(NAME,2) = '_U')
--BEGIN

--	SELECT @TABLE_NAME = NAME FROM SYS.TABLES WHERE RIGHT(NAME,2) = '_U'

--	SET @SQL_SCRIPT = '
--	IF exists(select * from sysobjects where id = object_id(''[dbo].[' + @TABLE_NAME + ']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
--		drop table [dbo].[' + @TABLE_NAME + ']'

--	PRINT @SQL_SCRIPT
--	EXEC (@SQL_SCRIPT)

--	SET @TABLE_NAME = LEFT(@TABLE_NAME,LEN(@TABLE_NAME)-2)

--	SET @SQL_SCRIPT = '
--	if exists (select * from dbo.sysobjects where id = object_id(N'''+@TABLE_NAME+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
--		DBCC DBREINDEX (''' + @TABLE_NAME + ''')'
		
--	PRINT @SQL_SCRIPT
--	EXEC (@SQL_SCRIPT)
--END

IF exists(select * from sysobjects where id = object_id('[dbo].[REPORT_KBK_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_KBK_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[REPORT_CALC_FORMS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_CALC_FORMS_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[REPORT_CALC_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_CALC_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[REPORT_CALC_ROWS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_CALC_ROWS_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[REPORT_CALC_ATRIBUTE_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[REPORT_CALC_ATRIBUTE_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[S_ACCOUNTS_EQUIVALENT_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[S_ACCOUNTS_EQUIVALENT_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[S_ACCOUNTS_NASTR_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[S_ACCOUNTS_NASTR_U]

IF exists (select * from sysobjects where id = object_id('[dbo].[S_ACCOUNTS_NASTR_KOSGU_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[S_ACCOUNTS_NASTR_KOSGU_U]

IF exists(select * from sysobjects where id = object_id('[dbo].[S_ACCOUNTS_NEW_FOR_HO_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[S_ACCOUNTS_NEW_FOR_HO_U]

GO


GRANT EXECUTE ON dbo.[VX_UPGRADE_CLEAR] TO PUBLIC

GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[VX_UPGRADE_S_RELEASE]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE dbo.[VX_UPGRADE_S_RELEASE]

GO

/*
© Кейсистемс (Keysystems), 2005
*/

CREATE  PROCEDURE VX_UPGRADE_S_RELEASE
AS

SELECT SO.LINK as Project, SR.Release, SR.Fix
INTO #tmpRelease
FROM S_RELEASE_U SR, S_OBJECTS_U SOU, S_OBJECTS SO
WHERE Project = SOU.LINK AND RTRIM(LTRIM(SO.CODE))=RTRIM(LTRIM(SOU.CODE))
	and not exists(Select Release From S_Release Where Release = SR.Release and Project = SO.LINK AND Fix = SR.Fix)

BEGIN TRAN
	INSERT INTO S_RELEASE(Project, Release, Fix)
	SELECT Project, Release, Fix
	FROM #tmpRelease
	
	
	Select SFU.Name, SR.ID as Release, SFU.Path, SFU.CreateDate, SFU.FileSize, SFU.ID as oldID
	INTO #tmpFiles
	From S_FILES_U SFU INNER JOIN S_RELEASE_U SUR ON SFU.Release = SUR.ID
		INNER JOIN S_RELEASE SR ON SUR.Release = SR.Release AND SUR.Fix = SR.Fix
		INNER JOIN #tmpRelease TR ON SR.Release = TR.Release
			AND SR.Project = TR.Project AND SR.Fix = TR.Fix
	
	
	DELETE FROM S_FILES
	FROM #tmpFiles T WHERE T.NAME = S_FILES.NAME
	
	
	INSERT INTO S_FILES(Name, Release, Path, CreateDate, FileSize)
	SELECT Name, Release, Path, CreateDate, FileSize
	From #tmpFiles
	
	
	INSERT INTO S_FILEPARTS(File_ID, ORDERS, BODY)
	Select SF.ID, SPU.Orders, convert(varchar(8000), SPU.Body)
	From S_FILES SF, S_FILEPARTS_U SPU, #tmpFiles TF
	Where SF.Release = TF.Release
		AND SF.Name = TF.Name
		AND SPU.File_ID = TF.oldID

	/* HISTORY */
	DELETE FROM dbo.HISTORY
	
	INSERT INTO dbo.HISTORY(LINK_UP, TASK_CODE, INFO)
	SELECT NEW.ID, HU.TASK_CODE, HU.INFO FROM dbo.HISTORY_U HU INNER JOIN dbo.S_RELEASE_U OLD ON HU.LINK_UP = OLD.ID
	INNER JOIN dbo.S_RELEASE NEW ON OLD.RELEASE = NEW.RELEASE AND OLD.FIX = NEW.FIX
	--- HISTORY


	/* FAQ */
	DELETE FROM dbo.FAQ
	
	INSERT INTO dbo.FAQ (TASK,GRP,ORDERS,QUEST,ANSWER)
	SELECT TASK,GRP,ORDERS,QUEST,ANSWER FROM dbo.FAQ_U
	-- FAQ

	IF EXISTS(select * from s_files F
		where F.Filesize <> (select sum(datalength(body)) from s_fileparts where file_id = F.id))
	BEGIN 
		ROLLBACK
		raiserror('Файлы клиента не могут быть обновлены! Возможно дистрибутив поврежден!',11,1)
		RETURN
	END
WHILE @@TRANCOUNT > 0
	COMMIT TRAN

Drop Table #tmpFiles
Drop Table #tmpRelease




GO


GRANT EXECUTE ON dbo.[VX_UPGRADE_S_RELEASE] TO PUBLIC

GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[ro_import_form_spr]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE dbo.[ro_import_form_spr]

GO

/*******************************************************************************

	© Кейсистемс (Keysystems), 2011

*******************************************************************************/
-- Процедура импорта справочников
CREATE PROCEDURE dbo.[ro_import_form_spr]
AS

   CREATE TABLE #t_CodeRow (Code VARCHAR(254), Code_Form VARCHAR(254), begin_date DATETIME, end_date DATETIME,
                           Code_s VARCHAR(254), Code_Form_s VARCHAR(254), begin_date_s DATETIME, end_date_s DATETIME)
   
   INSERT #t_CodeRow ([Code], [Code_Form], [begin_date], [end_date], [Code_s], [Code_Form_s], [begin_date_s], [end_date_s])
   SELECT K.Code, K.[Code_Form], K.[begin_date], K.[end_date], K1.[Code], K1.[Code_Form], K1.[begin_date], K1.[end_date]
   FROM dbo.report_kbk k
         JOIN dbo.report_kbk K1 ON K.[Link_self] = K.[link] AND K1.code_form <> K.code_form
         JOIN dbo.report_kbk_U U ON K1.[Code_Form] = U.[Code_Form] AND K1.[Code] = U.[Code]
        
   DELETE dbo.report_kbk
   WHERE code_form IN (SELECT distinct K.code_form FROM dbo.report_kbk_U K)
   
     
	insert dbo.report_kbk(Code, link_self, Code_form, childrens, Name, ImageIndex, Code_Str, property, add_info, orders, long_name, begin_date, end_date)
	select Code, 0, Code_Form, childrens, Name, ImageIndex, Code_Str, property, add_info, orders, long_name, begin_date, end_date
	from dbo.report_kbk_U K

	update K
	set link_self = isnull((select top 1 K2.link from dbo.report_kbk K2 where K2.code_form = U1.code_form and K2.code = U1.code and isnull(K2.begin_date, '19000101') = isnull(U1.begin_date, '19000101') and isnull(K2.end_date, '30000101') = isnull(U1.end_date, '30000101')),0)
	from dbo.report_kbk K join dbo.report_kbk_U U on K.code_form = U.code_form and K.code = U.code and isnull(K.begin_date, '19000101') = isnull(U.begin_date, '19000101') and isnull(K.end_date, '30000101') = isnull(U.end_date, '30000101')
		left join dbo.report_kbk_U U1 on U.link_self = U1.link


   UPDATE K
   SET link_self = K1.[link]
   FROM dbo.report_kbk K
         JOIN #t_CodeRow C ON K.[Code_Form] = C.[Code_Form] AND K.[Code] = C.[Code] AND ISNULL(K.begin_date, '19000101') = ISNULL(C.begin_date, '19000101') AND ISNULL(K.end_date, '30000101') = ISNULL(C.end_date, '30000101')
         JOIN dbo.report_kbk K1 ON K1.[Code_Form] = C.[Code_Form_S] AND K1.[Code] = C.[Code_S] AND ISNULL(K1.begin_date, '19000101') = ISNULL(C.begin_date_S, '19000101') AND ISNULL(K1.end_date, '30000101') = ISNULL(C.end_date_S, '30000101')
         
   DELETE #t_CodeRow
   WHERE EXISTS(SELECT * 
                FROM dbo.report_kbk K JOIN dbo.report_kbk K1 ON K.[Link_self] = K1.[link] 
                WHERE K.[Code_Form] = #t_CodeRow.[Code_Form] AND K.[Code] = #t_CodeRow.[Code] AND ISNULL(K.begin_date, '19000101') = ISNULL(#t_CodeRow.begin_date, '19000101') AND ISNULL(K.end_date, '30000101') = ISNULL(#t_CodeRow.end_date, '30000101')
                      AND K1.[Code_Form] = #t_CodeRow.[Code_Form_S] AND K1.[Code] = #t_CodeRow.[Code_S] AND ISNULL(K1.begin_date, '19000101') = ISNULL(#t_CodeRow.begin_date_S, '19000101') AND ISNULL(K1.end_date, '30000101') = ISNULL(#t_CodeRow.end_date_S, '30000101'))

   UPDATE K
   SET link_self = K1.[link]
   FROM dbo.report_kbk K
         JOIN #t_CodeRow C ON K.[Code_Form] = C.[Code_Form] AND K.[Code] = C.[Code] AND ISNULL(K.begin_date, '19000101') = ISNULL(C.begin_date, '19000101') AND ISNULL(K.end_date, '30000101') = ISNULL(C.end_date, '30000101')
         JOIN dbo.report_kbk K1 ON K1.[Code_Form] = C.[Code_Form_S] AND K1.[Code] = C.[Code_S] AND ISNULL(K1.begin_date, '19000101') = ISNULL(C.begin_date_S, '19000101')
         
   DELETE #t_CodeRow
   WHERE EXISTS(SELECT * 
                FROM dbo.report_kbk K JOIN dbo.report_kbk K1 ON K.[Link_self] = K1.[link] 
                WHERE K.[Code_Form] = #t_CodeRow.[Code_Form] AND K.[Code] = #t_CodeRow.[Code] AND ISNULL(K.begin_date, '19000101') = ISNULL(#t_CodeRow.begin_date, '19000101') AND ISNULL(K.end_date, '30000101') = ISNULL(#t_CodeRow.end_date, '30000101')
                      AND K1.[Code_Form] = #t_CodeRow.[Code_Form_S] AND K1.[Code] = #t_CodeRow.[Code_S] AND ISNULL(K1.begin_date, '19000101') = ISNULL(#t_CodeRow.begin_date_S, '19000101') )


   UPDATE K
   SET link_self = K1.[link]
   FROM dbo.report_kbk K
         JOIN #t_CodeRow C ON K.[Code_Form] = C.[Code_Form] AND K.[Code] = C.[Code] AND ISNULL(K.begin_date, '19000101') = ISNULL(C.begin_date, '19000101') AND ISNULL(K.end_date, '30000101') = ISNULL(C.end_date, '30000101')
         JOIN dbo.report_kbk K1 ON K1.[Code_Form] = C.[Code_Form_S] AND K1.[Code] = C.[Code_S] 
         

  DROP TABLE #t_CodeRow        
GO


GRANT EXECUTE ON dbo.[ro_import_form_spr] TO PUBLIC

GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[ro_import_form_struct]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE dbo.[ro_import_form_struct]

GO

/*******************************************************************************

	© Кейсистемс (Keysystems), 2010

*******************************************************************************/
-- Процедура импорта структур форм из временных таблиц.
CREATE PROCEDURE dbo.[ro_import_form_struct]
AS
--SET NOCOUNT ON
declare @bd DATETIME
DECLARE @only_add TINYINT 
SET @only_add = 0

IF ISNULL((SELECT COUNT(*) FROM S_OPTIONS WHERE OBJECT = (SELECT LINK FROM S_OBJECTS WHERE CODE = 'OBJ_UPDATE_SVOD') AND RTRIM(SUBSTRING(VALUE,22,8))='12.11.00'),0) > 1000
BEGIN
   SET @only_add = 1 -- чтобы ничего не затереть у пользователей
END 

set @bd = getdate() 

DECLARE @t_Forms TABLE (Link_Form INT, Link_Form_imp INT, maxOrderRule int)
DECLARE @t_Forms_New TABLE (Link_Form INT)
create table #t_Tables (Link_Table INT, Link_Table_imp INT, cnt_Cols TINYINT, cnt_Cols_imp TINYINT)
declare @t_Tables_New table (Link_Table INT)
DECLARE @t_Tables_Del table (Link_Table INT)
create table #t_Cols (Link_Col INT, Link_Col_imp INT)
DECLARE @t_Cols_new TABLE (Link_Col INT, Link_Table INT)
DECLARE @t_Cols_del TABLE (Link_Col INT, Link_Table INT)
create table #t_Rows (Link_Row INT, Link_Row_imp INT)
create table #t_Rows_del (Link_Row INT)
DECLARE @t_Rows_new TABLE (Link_Row INT)

Create index #IX_Tables on #t_Tables (link_table)
Create index #IX_Tables_imp on #t_Tables (link_table_imp)
Create index #IX_Cols on #t_Cols (link_col)
Create index #IX_Cols_imp on #t_Cols (link_col_imp)
Create index #IX_Rows on #t_Rows (link_row)
Create index #IX_Rows_imp on #t_Rows (link_row_imp)
Create index #IX_Rows_del on #t_Rows_del (link_row)

DECLARE @LinkTable INT, @LinkCodeCol INT, @LinkCodeColNew INT

INSERT @t_Forms (Link_Form, Link_Form_imp)
SELECT F.Link_Form, I.Link_Form
FROM report_forms F JOIN report_forms_U I ON F.code = I.Code AND F.Start_Date = I.Start_Date
where isnull(I.update_date, '21000101') > isnull(F.update_date, '19000101')

INSERT @t_Forms_New (Link_Form)
SELECT Link_Form
FROM report_forms_U U
WHERE not exists(select top 1 * from report_forms F where U.Code = F.Code and U.Start_Date = F.Start_Date)

print 'form'
print convert(varchar(30), getdate() - @BD, 114)

INSERT #t_Tables (Link_Table,Link_Table_imp, cnt_Cols, cnt_Cols_imp) 
SELECT T.Link_Table, I.Link_Table, (SELECT COUNT(*) FROM dbo.REPORT_COLOUMNS RC WHERE RC.Link_Table = T.Link_Table), (SELECT COUNT(*) FROM dbo.REPORT_COLOUMNS_U RC WHERE RC.Link_Table = I.Link_Table)
FROM report_tables T
	JOIN @t_Forms F ON T.Link_Form = F.Link_Form
	JOIN dbo.REPORT_TABLES_U I ON I.Link_Form = F.Link_Form_imp AND I.orders = T.orders


INSERT @t_Tables_New (Link_Table)
SELECT Link_Table
FROM report_tables_U U
WHERE not exists (SELECT top 1 * FROM #t_Tables T where T.Link_Table_imp = U.Link_Table)

INSERT @t_Tables_Del (Link_Table)
SELECT Link_Table
FROM report_tables T JOIN @t_Forms F ON T.Link_Form = F.Link_Form
WHERE not exists(SELECT top 1 * FROM #t_Tables T1 where T1.Link_Table = T.Link_Table)

print 'tables'
print convert(varchar(30), getdate() - @BD, 114)

INSERT #t_Cols (Link_Col, Link_Col_imp)
SELECT C.Link_Col, I.Link_Col
FROM report_coloumns C
	JOIN #t_Tables T ON C.Link_Table = T.Link_Table
	JOIN dbo.REPORT_COLOUMNS_U I ON T.Link_Table_imp = I.Link_Table
WHERE (T.cnt_Cols = T.cnt_Cols_imp AND C.orders = I.orders)
		OR
		(T.cnt_Cols <> T.cnt_Cols_imp AND C.Name = I.Name)
		
INSERT @t_Cols_New (Link_Col, Link_Table)
SELECT Link_Col, Link_Table
FROM dbo.REPORT_COLOUMNS_U U
WHERE not exists(SELECT top 1 * FROM #t_Cols C where C.Link_Col_imp = U.Link_Col)


INSERT @t_Cols_Del (Link_Col, Link_Table)
SELECT Link_Col, C.Link_Table
FROM dbo.REPORT_COLOUMNS C JOIN #t_Tables T ON C.link_Table = T.Link_Table
WHERE not exists(SELECT top 1 * FROM #t_Cols C1 where C1.Link_Col = C.Link_Col)

print 'col'
print convert(varchar(30), getdate() - @BD, 114)

insert #t_Rows (Link_Row, Link_Row_imp)
select R.Link_Row, U.Link_Row
from i_report_rows R 
	join #t_Tables T on R.Link_Table = T.Link_Table
	join (select  RFU.Formula as Code, RTU.Link_Table, RFU.Link_Row from report_tables_U RTU join report_formula_U RFU on RFU.Link_Col = RTU.Link_Code_Col ) U
			on U.Link_Table = T.Link_Table_imp and convert(varchar(50), U.Code) = convert(varchar(50), R.Code)

/*
insert #t_Rows (Link_Row, Link_Row_imp)
select R.Link_Row, U.Link_Row
from report_rows R 
	join #t_Tables T on R.Link_Table = T.Link_Table
	join report_rows_U U on U.Link_Table = T.Link_Table_imp and U.orders = R.orders
*/

insert @t_Rows_New (Link_Row)
select Link_Row
from report_rows_U U
where not exists (select top 1 * from #t_Rows R where R.Link_Row_imp = U.Link_Row)

insert #t_Rows_del (Link_Row)
select Link_Row
from report_rows RR join #t_Tables T on RR.Link_Table = T.Link_Table
where not exists (select top 1 * from #t_Rows R1 where R1.Link_Row = RR.Link_Row) 

print 'row'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- Обновление таблицы REPORT_FORMS.
-------------------------------------------------------------------------------
IF @only_add = 0
BEGIN 
   UPDATE [A]
   SET [NAME]                    = [B].[NAME],
       [CODE]                    = [B].[CODE],
       [START_DATE]              = [B].[START_DATE],
       [PERIODIC]                = [B].[PERIODIC],
       [IsSvod]                  = [B].[IsSvod],
       [IsExported]              = [B].[IsExported],
       [IsUserForm]              = [B].[IsUserForm],
       [PRINT_OBJECT_METHOD]     = [B].[PRINT_OBJECT_METHOD],
       [QUERY_ADD_MODULE]        = [B].[QUERY_ADD_MODULE],
       [QUERY_BEFORE]            = [B].[QUERY_BEFORE],
       [QUERY_REPMAKER]          = [B].[QUERY_REPMAKER],
       [QUERY_REPORT]            = [B].[QUERY_REPORT],
       [QUERY_REPORT_PATH]       = [B].[QUERY_REPORT_PATH],
       [FORM_TYPE]               = [C].[LINK],
       [EXPORT_PROC]             = [B].[EXPORT_PROC],
       [DECRIPT_PROC]            = [B].[DECRIPT_PROC],
       [IsKonsolid]              = [B].[IsKonsolid],
       [PARAM_EXPORT]            = [B].[PARAM_EXPORT],
       [PARAM_IMPORT]            = [B].[PARAM_IMPORT],
       [OBJECT]                  = [B].[OBJECT],
       [ExportCode]              = [B].[ExportCode],
       [IS_AUTOFILL]             = [B].[IS_AUTOFILL],
       [ATRIBUTS]                = [B].[ATRIBUTS],
       [UPDATE_DATE]             = [B].[UPDATE_DATE],
       [SvodProc]                = [B].[SvodProc],
       [END_DATE]                = [B].[END_DATE],
       [COMMENT]                 = [B].[COMMENT],             
       [PRINT_OBJECT_METHOD_NEW] = [B].[PRINT_OBJECT_METHOD_NEW],
       [QUERY_BEFORE_PRINT_NEW]  = [B].[QUERY_BEFORE_PRINT_NEW],
       [QUERY_REPORT_NEW]        = [B].[QUERY_REPORT_NEW],
       [IS_CALC]                 = [B].[IS_CALC],
       [HINT_FILE_ID]            = NULL,
       [GUID]					  = [B].[GUID]
   FROM dbo.[REPORT_FORMS] [A]
   JOIN @t_Forms F ON A.link_form = F.Link_Form
   JOIN dbo.[REPORT_FORMS_U] [B] ON F.Link_Form_imp = B.Link_Form
   JOIN dbo.[S_OBJECTS_U]	[S]		ON [S].[Link] = [B].[Form_Type]
   JOIN dbo.[S_OBJECTS]    [C] ON [S].[CODE] = [C].[CODE]
END 
ELSE
BEGIN     
   UPDATE [A]
   SET [NAME]                    = [B].[NAME],
       [CODE]                    = [B].[CODE],
       [START_DATE]              = [B].[START_DATE],
       [IsSvod]                  = [B].[IsSvod],
       [IsExported]              = [B].[IsExported],
       [IsUserForm]              = [B].[IsUserForm],
       [PRINT_OBJECT_METHOD]     = [B].[PRINT_OBJECT_METHOD],
       [QUERY_ADD_MODULE]        = [B].[QUERY_ADD_MODULE],
       [QUERY_BEFORE]            = [B].[QUERY_BEFORE],
       [QUERY_REPMAKER]          = [B].[QUERY_REPMAKER],
       [QUERY_REPORT]            = [B].[QUERY_REPORT],
       [QUERY_REPORT_PATH]       = [B].[QUERY_REPORT_PATH],
       [FORM_TYPE]               = [C].[LINK],
       [EXPORT_PROC]             = [B].[EXPORT_PROC],
       [DECRIPT_PROC]            = [B].[DECRIPT_PROC],
       [PARAM_EXPORT]            = [B].[PARAM_EXPORT],
       [PARAM_IMPORT]            = [B].[PARAM_IMPORT],
       [OBJECT]                  = [B].[OBJECT],
       [ExportCode]              = [B].[ExportCode],
       [IS_AUTOFILL]             = [B].[IS_AUTOFILL],
       [ATRIBUTS]                = [B].[ATRIBUTS],
       [UPDATE_DATE]             = [B].[UPDATE_DATE],
       [SvodProc]                = [B].[SvodProc],
       [END_DATE]                = [B].[END_DATE],
       [COMMENT]                 = [B].[COMMENT],             
       [PRINT_OBJECT_METHOD_NEW] = [B].[PRINT_OBJECT_METHOD_NEW],
       [QUERY_BEFORE_PRINT_NEW]  = [B].[QUERY_BEFORE_PRINT_NEW],
       [QUERY_REPORT_NEW]        = [B].[QUERY_REPORT_NEW],
       [IS_CALC]                 = [B].[IS_CALC],
       [GUID]					  = [B].[GUID]
   FROM dbo.[REPORT_FORMS] [A]
   JOIN @t_Forms F ON A.link_form = F.Link_Form
   JOIN dbo.[REPORT_FORMS_U] [B] ON F.Link_Form_imp = B.Link_Form
   JOIN dbo.[S_OBJECTS_U]	[S]		ON [S].[Link] = [B].[Form_Type]
   JOIN dbo.[S_OBJECTS]    [C] ON [S].[CODE] = [C].[CODE]
END

INSERT dbo.[REPORT_FORMS]([NAME],[CODE],[START_DATE],[PERIODIC],[IsSvod],[IsExported],[IsUserForm],[IsUsedRazdel], [IsUsedCelSt], [IsUsedRashod],[PRINT_OBJECT_METHOD],[QUERY_ADD_MODULE],[QUERY_BEFORE],[QUERY_REPMAKER],[QUERY_REPORT],[QUERY_REPORT_PATH],[FORM_TYPE],[EXPORT_PROC],[DECRIPT_PROC],[IsKonsolid],[PARAM_EXPORT],[PARAM_IMPORT],[OBJECT],[ExportCode],[IS_AUTOFILL],[ATRIBUTS],[UPDATE_DATE],[SvodProc],[END_DATE],[COMMENT],[PRINT_OBJECT_METHOD_NEW],[QUERY_BEFORE_PRINT_NEW],[QUERY_REPORT_NEW],[IS_CALC],[HINT_FILE_ID],[GUID],[IsRemoteAccess])
SELECT [B].[NAME],[B].[CODE],[B].[START_DATE],[B].[PERIODIC],[B].[IsSvod],[B].[IsExported],[B].[IsUserForm],0,0,0,[B].[PRINT_OBJECT_METHOD],[B].[QUERY_ADD_MODULE],[B].[QUERY_BEFORE],[B].[QUERY_REPMAKER],[B].[QUERY_REPORT],[B].[QUERY_REPORT_PATH],[C].[LINK],[B].[EXPORT_PROC],[B].[DECRIPT_PROC],[B].[IsKonsolid],[B].[PARAM_EXPORT],[B].[PARAM_IMPORT],[B].[OBJECT],[B].[ExportCode],[B].[IS_AUTOFILL],[B].[ATRIBUTS],[B].[UPDATE_DATE],[B].[SvodProc],[B].[END_DATE],[B].[COMMENT],[B].[PRINT_OBJECT_METHOD_NEW],[B].[QUERY_BEFORE_PRINT_NEW],[B].[QUERY_REPORT_NEW],[B].[IS_CALC],NULL,[B].[GUID],0
FROM @t_Forms_New  [A]
JOIN report_forms_U   [B] ON [A].[Link_Form] = [B].[Link_Form]
JOIN dbo.[S_OBJECTS_U] [S] ON [B].[FORM_TYPE] = [S].[Link]
JOIN dbo.[S_OBJECTS] [C] ON [S].[CODE] = [C].[CODE]

INSERT @t_Forms (Link_Form, Link_Form_imp) -- теперь все соответствия форм
SELECT F.Link_Form, I.Link_Form
FROM report_forms F JOIN report_forms_U I ON F.code = I.Code AND F.Start_Date = I.Start_Date
	JOIN @t_Forms_New N ON N.Link_Form = I.Link_Form

print 'form'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- удаление REPORT_TABLES, REPORT_COLOUMNS, REPORT_ROWS
-------------------------------------------------------------------------------
-- сначала удаляем все системные правила
DELETE report_control_rules
where Link_Rule in (select Link_Rule FROM report_control_rows R join @t_Tables_del T ON  T.Link_Table = R.Link_Table)

DELETE report_control_rules
where Link_Rule in (select Link_Rule FROM report_control_rows R join @t_Cols_del C ON  C.Link_Col = R.Link_Col)

DELETE report_control_rules
where Link_Rule in (select Link_Rule FROM report_control_rows R join #t_Rows_del RD ON  RD.Link_Row = R.Link_Row)


DELETE A
FROM dbo.REPORT_Tables A join @t_Tables_Del C on A.Link_Table = C.Link_Table

DELETE A
FROM dbo.REPORT_Coloumns A join @t_Cols_Del C on A.Link_Col = C.Link_Col

DELETE A
FROM dbo.REPORT_Rows A join #t_Rows_del R on A.Link_Row = R.Link_Row

print 'del'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- REPORT_TABLES
-------------------------------------------------------------------------------
UPDATE [A]
SET [Name] = I.[Name],
   Val_Type = I.Val_Type,
   Script_Str = I.Script_Str,
   order_rule = I.order_rule,
   IS_DINAMIC = I.IS_DINAMIC,
   Doschet_Tree = I.Doschet_Tree,
   GUID = I.GUID
FROM dbo.REPORT_TABLES [A] JOIN #t_Tables T ON A.link_table = T.Link_Table
   JOIN dbo.REPORT_TABLES_U I ON T.Link_Table_imp = I.Link_Table

INSERT dbo.REPORT_TABLES ([Name],Link_Form,Orders,Link_Code_Col,Val_Type,Script_Str,order_rule,IS_DINAMIC,GUID, Doschet_Tree) 
SELECT I.[Name],F.Link_Form,I.Orders,NULL,I.Val_Type,I.Script_Str,I.order_rule,I.IS_DINAMIC,I.GUID, I.Doschet_Tree
FROM dbo.REPORT_TABLES_U I JOIN @t_Tables_New T ON I.Link_Table = T.Link_Table
	JOIN @t_Forms F ON I.Link_Form = F.Link_Form_imp
	
INSERT #t_Tables (Link_Table,Link_Table_imp, cnt_Cols, cnt_Cols_imp) -- теперь все соответствия таблиц
SELECT T.Link_Table, I.Link_Table, (SELECT COUNT(*) FROM dbo.REPORT_COLOUMNS RC WHERE RC.Link_Table = T.Link_Table), (SELECT COUNT(*) FROM dbo.REPORT_COLOUMNS_U RC WHERE RC.Link_Table = I.Link_Table)
FROM report_tables T
	JOIN @t_Forms F ON T.Link_Form = F.Link_Form
	JOIN report_tables_U I ON I.Link_Form = F.Link_Form_imp AND I.orders = T.orders
	JOIN @t_Tables_New N on N.Link_Table = I.Link_Table

print 'tables'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- REPORT_COLOUMNS
-------------------------------------------------------------------------------
IF @only_add = 0
BEGIN 
   UPDATE [A]
   SET Orders = I.orders,
	   [Name] = I.[Name],
	   IsExported = I.IsExported,
	   Col_Type = I.Col_Type,
	   Export_Order = I.Export_Order,
	   Calc_Orders = I.Calc_Orders,
	   IsEdited = I.IsEdited,
	   Val_Len = I.Val_Len,
	   Dictionary = I.Dictionary,
	   Dictionary_Columns_List = I.Dictionary_Columns_List,
	   Dictionary_Fields_List = I.Dictionary_Fields_List,
	   input_mask = I.input_mask,
	   [property] = I.[property],
	   STR_PROPERTY = I.STR_PROPERTY,
	   [GUID] = I.[GUID], 
      [Dictionary_Where] = I.[Dictionary_Where]   
   FROM dbo.REPORT_COLOUMNS A JOIN #t_Cols C ON A.Link_Col = C.Link_Col
	   JOIN dbo.REPORT_COLOUMNS_U I ON C.Link_col_imp = I.Link_Col
END 
ELSE BEGIN
   UPDATE [A]
   SET Orders = I.orders,
	   IsExported = I.IsExported,
	   Col_Type = I.Col_Type,
	   Export_Order = I.Export_Order,
	   Calc_Orders = I.Calc_Orders,
	   IsEdited = I.IsEdited,
	   [GUID] = I.[GUID]
   FROM dbo.REPORT_COLOUMNS A JOIN #t_Cols C ON A.Link_Col = C.Link_Col
	   JOIN dbo.REPORT_COLOUMNS_U I ON C.Link_col_imp = I.Link_Col
END   
   

INSERT dbo.REPORT_COLOUMNS (Orders,[Name],Link_Table,IsExported,Col_Type,Export_Order,Calc_Orders,IsEdited,Val_Len,Dictionary,Dictionary_Columns_List,Dictionary_Fields_List,input_mask,[property],STR_PROPERTY,[GUID], [Dictionary_Where])
SELECT I.Orders,I.[Name],T.Link_Table,I.IsExported,I.Col_Type,I.Export_Order,I.Calc_Orders,I.IsEdited,I.Val_Len,I.Dictionary,I.Dictionary_Columns_List,I.Dictionary_Fields_List,I.input_mask,I.[property],I.STR_PROPERTY,I.[GUID], I.[Dictionary_Where]
FROM dbo.REPORT_COLOUMNS_U I JOIN @t_Cols_New N ON I.Link_Col = N.Link_Col
	JOIN #t_Tables T on I.Link_Table = T.Link_Table_imp


INSERT #t_Cols (Link_Col, Link_Col_imp)  -- теперь все соответствия колонок
SELECT C.Link_Col, I.Link_Col
FROM report_coloumns C
	JOIN #t_Tables T ON C.Link_Table = T.Link_Table
	JOIN dbo.REPORT_COLOUMNS_U I ON T.Link_Table_imp = I.Link_Table
	JOIN @t_Cols_New N ON N.Link_Col = I.Link_Col
WHERE C.orders = I.orders

print 'col'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- REPORT_CODE_COL
-------------------------------------------------------------------------------
-- для существующих таблиц возможно нужно будет перенести данные
DECLARE cur_Tables CURSOR LOCAL STATIC FOR
SELECT RT.Link_Table, Link_Code_Col
FROM report_tables RT JOIN #t_Tables T ON RT.Link_Table = T.Link_Table
WHERE Link_Code_Col IS NOT NULL 

OPEN cur_Tables

FETCH NEXT FROM cur_Tables
INTO @LinkTable, @LinkCodeCol

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @LinkCodeColNew = (SELECT top 1 C.Link_Col FROM dbo.REPORT_TABLES_U I JOIN #t_Tables T ON I.Link_Table = T.Link_Table_imp and T.Link_Table =  @LinkTable JOIN #t_Cols C ON C.Link_Col_imp = I.Link_Code_Col)

	IF @LinkCodeColNew IS NOT NULL
	BEGIN 
		EXEC dbo.[RA_MOVE_CODE_COL] @LinkCodeColNew
		UPDATE report_tables SET Link_Code_Col = @LinkCodeColNew WHERE Link_Table = @LinkTable
	END

--	exec dbo.[I_REPORT_CREATE_PROC] @LinkTable
	
	FETCH NEXT FROM cur_Tables
	INTO @LinkTable, @LinkCodeCol
END 

CLOSE cur_Tables

DEALLOCATE cur_Tables

-- для новых таблиц
UPDATE [A]
SET Link_Code_Col = C.Link_Col
FROM dbo.REPORT_TABLES [A] JOIN #t_Tables T ON A.Link_Table = T.Link_Table
	JOIN dbo.REPORT_TABLES_U I ON T.Link_Table_imp = I.Link_Table
	JOIN #t_Cols C ON C.Link_Col_imp = I.Link_Code_Col
WHERE A.Link_Code_Col IS NULL

print 'report_code_col'
print convert(varchar(30), getdate() - @BD, 114)
-----------------------------------------------------------------------------
-- REPORT_ROWS
-------------------------------------------------------------------------------
UPDATE A
set	Calc_Orders = I.Calc_Orders,
	Print_Null = I.Print_Null,
	Orders = I.Orders,
	GUID = I.GUID
FROM dbo.REPORT_ROWS A JOIN #t_Rows T ON A.Link_Row = T.Link_Row
	JOIN dbo.REPORT_ROWS_U I ON T.Link_Row_imp = I.Link_Row 

INSERT dbo.REPORT_ROWS (Orders,Link_Table,Calc_Orders,Print_Null,GUID)
SELECT RRU.Orders, T.Link_Table, RRU.Calc_Orders, RRU.Print_Null, RRU.GUID
FROM report_rows_U RRU join #t_Tables T on RRU.Link_Table = T.Link_Table_imp
	join @t_Rows_New N on N.Link_Row = RRU.Link_Row

insert #t_Rows (Link_Row, Link_Row_imp) -- теперь все соответствия
select R.Link_Row, RU.Link_Row
from report_rows R 
	join #t_Tables T on R.Link_Table = T.Link_Table
	join report_rows_U RU on R.orders = RU.orders and Ru.Link_Table = T.Link_Table_imp
where RU.Link_Row in (select Link_Row from @t_Rows_New)

print 'row'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- REPORT_FORMULA
-------------------------------------------------------------------------------
IF @only_add = 0
BEGIN 
   UPDATE A
   set Formula = U.Formula
   from report_formula A join #t_Cols C on A.Link_Col = C.Link_Col
		   join #t_Rows R on A.Link_Row = R.Link_Row
		   join report_formula_U U on U.Link_Row = R.Link_Row_imp and U.Link_Col = C.Link_Col_imp
END 

delete A
from report_formula A
where not exists(select top 1 * from report_formula_U RFU join #t_Cols C on C.Link_Col_imp = RFU.Link_Col 
					 join #t_Rows R on R.Link_Row_imp = RFU.Link_Row where A.Link_Row = R.Link_Row and A.Link_Col = C.Link_Col)
	and exists(select top 1 * from #t_Rows R where R.Link_Row = A.Link_Row)

insert report_formula (Link_Row, Link_Col, Formula)
select R.Link_Row, C.Link_Col, RFU.Formula
from report_formula_U RFU  join #t_Cols C on RFU.Link_Col = C.Link_Col_imp
		join #t_Rows R on RFU.Link_Row = R.Link_Row_imp
where not exists(select top 1 * from report_formula RF where RF.Link_Row = R.Link_Row and RF.Link_Col = C.Link_Col)

print 'formula'
print convert(varchar(30), getdate() - @BD, 114)

-------------------------------------------------------------------------------
-- ГРУППЫ ФОРМ
-------------------------------------------------------------------------------
update A
set Name = U.Name,
	System = U.System
from report_form_groups A, report_form_groups_U U
where A.Code = U.Code

insert report_form_groups (Code, Name, System)
select U.Code, U.Name, U.System
from report_form_groups_U U
where not exists(select top 1 * from report_form_groups G where G.Code = U.Code)

insert report_form_groups_ref (link_form, Link_Form_Groups)
select distinct F.Link_Form, G.Link
from report_form_groups_ref_U U join @t_Forms F on F.Link_Form_imp = U.Link_Form
	join report_form_groups_U UG on UG.Link = U.Link_Form_Groups
	join report_form_groups G on G.Code = UG.Code
where not exists(select top 1 * from report_form_groups_ref R where R.Link_Form = F.Link_Form and R.Link_Form_Groups = G.Link)

print 'group'
print convert(varchar(30), getdate() - @BD, 114)
-------------------------------------------------------------------------------
-- ПРАВА
-------------------------------------------------------------------------------
declare @LinkForm int
declare cur_New_Form cursor local static for
select F.Link_Form
from @t_Forms_New N join @t_Forms F on F.Link_Form_imp = N.Link_Form
	join report_forms RF on F.Link_Form = RF.Link_Form
order by RF.Start_Date

open cur_New_Form

fetch next from cur_New_Form
into @LinkForm

while @@fetch_status = 0
begin
	exec dbo.[RA_UNION_FORM_SET_PERSMISSIONS] @LinkForm

	fetch next from cur_New_Form
	into @LinkForm
end

close cur_New_Form
deallocate cur_New_Form
print 'perm'
print convert(varchar(30), getdate() - @BD, 114)
-------------------------------------------------------------------------------
-- ПРАВИЛА
-------------------------------------------------------------------------------
---- группы междокументных правил
update A
set Code = U.Code,
	Name = U.Name,
	Type = U.Type,
	System = U.System,
	dt_begin = U.dt_begin,
	dt_end = U.dt_end
from report_group_ks A join report_group_KS_U U on A.Code = U.Code

DELETE a
FROM [REPORT_CONTROL_RULES] A
WHERE ([IsSystemsRule] = 1 OR ([IsSystemsRule] = 0 AND ([REGION] IS NULL OR [REGION] IN ('')))) AND ISOUTSIDE = 1
	AND Link_Rule in (select GR.Link_Rule from report_control_group GR join report_group_ks G on GR.link_group_ks = G.link_group_ks join report_group_ks_U GU on G.Code = GU.Code where exists(select top 1 * from report_control_group_U CGU where GU.link_group_ks = CGU.link_group_ks))


insert report_group_ks (code, Name, Type, System, dt_begin, dt_end)
select Code, Name, Type, System, dt_begin, dt_end
from report_group_ks_U U
where not exists(select top 1 * from report_group_ks G where G.Code = U.Code)

declare @maxLink int, @minLink int, @NowDate datetime, @maxOrderRule int

set @NowDate = getdate()

select @maxLink=isnull(max(link_rule),1) from REPORT_CONTROL_RULES   
select @minLink=isnull(min(link_rule),1) from REPORT_CONTROL_RULES_U
set @maxLink = @maxLink - @minLink + 1

INSERT @t_Forms (Link_Form, Link_Form_imp) -- те формы, которые исправляли пользователи, для них нужны КС
SELECT F.Link_Form, I.Link_Form
FROM report_forms F JOIN report_forms_U I ON F.code = I.Code AND F.Start_Date = I.Start_Date
where not exists(select top 1 * from  @t_Forms F1 where F1.Link_Form =  F.Link_Form and F1.Link_Form_imp =  I.Link_Form) 

DELETE A
FROM dbo.[REPORT_CONTROL_RULES] A JOIN @t_Forms F on A.Link_Form = F.Link_Form and A.IsOutside = 0
WHERE ([IsSystemsRule] = 1 OR ([IsSystemsRule] = 0 AND ([REGION] IS NULL OR [REGION] IN (''))))

insert #t_Tables (Link_Table, Link_Table_imp, cnt_Cols , cnt_Cols_imp)
select RT.Link_Table, RTU.Link_Table, 0, 0
from report_tables RT join @t_Forms F on F.link_Form = RT.Link_Form
		join report_tables_U RTU on RTU.Link_Form = F.Link_Form_imp and RT.orders = RTU.orders
where not exists(select * from #t_Tables T where T.link_table = RT.Link_Table and T.Link_Table_imp = RTU.link_table)

insert  #t_Cols (Link_Col, Link_Col_imp)
select RT.Link_Col, RTU.Link_Col
from report_coloumns RT join #t_Tables F on F.link_table = RT.Link_table
		join report_coloumns_U RTU on RTU.link_table = F.link_table_imp and RT.orders = RTU.orders
where not exists(select * from #t_Cols T where T.Link_Col = RT.Link_Col and T.Link_Col_imp = RTU.Link_Col)

insert  #t_Rows (Link_Row, Link_Row_imp)
select RT.Link_Row, RTU.Link_Row
from report_rows RT join #t_Tables F on F.link_table = RT.Link_table
		join report_rows_U RTU on RTU.link_table = F.link_table_imp and RT.orders = RTU.orders
where not exists(select * from #t_Rows T where T.Link_Row = RT.Link_Row and T.Link_Row_imp = RTU.Link_Row)

update A
set maxOrderRule = isnull(Ru.maxOrderRule, 0)
from @t_Forms A join (select Link_Form, max(OrderRule) as maxOrderRule from report_control_rules where IsOutside = 0 group by Link_Form) Ru on A.Link_Form = Ru.Link_Form

update A
set maxOrderRule = isnull(maxOrderRule, 0)
from @t_Forms A

select @maxOrderRule = max(OrderRule)
from report_control_rules where IsOutside = 1

set @maxOrderRule = isnull(@maxOrderRule, 0)

	SET IDENTITY_INSERT  REPORT_CONTROL_RULES ON 

	insert into  REPORT_CONTROL_RULES ([link_rule],[Link_Task], [IsOutside], [Link_Form], [OrderRule], [Period], [Period_nom], [Org_Level], [IsSvod], [Str_Rule], [IsDoschet], [Calc_Orders],[isSystemsRule],[Modify_Date],[Cols],[Rows],[Rows_desc],REGION,COMMENT,SIGN_SR,ALGORITHM,ATR_CODE_SR)
	select U.link_rule+@maxLink, S.link,U.isOutside,F.link_form, 
case when U.IsOutside = 0 then U.OrderRule + F.maxOrderRule else U.OrderRule + @maxOrderRule end,U.Period,U.Period_nom, U.Org_Level, U.IsSvod, U.Str_Rule, U.IsDoschet, U.Calc_Orders,U.isSystemsRule,@NowDate,U.Cols,U.Rows,U.Rows_desc,U.REGION,U.COMMENT,U.SIGN_SR,U.ALGORITHM,U.ATR_CODE_SR 
	from report_control_rules_U U
		left join s_objects_U SU on U.link_task = SU.link
		left join s_objects S on S.Code = SU.Code
		left join @t_Forms F on U.Link_Form = F.Link_Form_imp

	insert into report_control_rows(Link_Rule, OrderRow, Val_Type, Sign, Link_Form, Link_Table, Link_Col, Link_Row, Period_Form, Factor, Code_Row, Exec_Proc, Exec_Proc_Desc,Filter,Filter_Desc,Maska,Maska_desc,period_type,budg_code,PERIOD_YEAR,ATR_CODE,PART,BEGIN_DATE,PX, PX_COMMENT)
	select  U.link_rule+@maxLink, U.OrderRow, U.Val_Type, U.Sign, F.Link_Form, T.Link_Table, C.Link_Col, R.Link_Row, U.Period_Form, U.Factor, U.Code_Row, U.Exec_Proc, U.Exec_Proc_Desc,U.Filter,U.Filter_Desc,U.Maska,U.Maska_desc,U.period_type,U.budg_code,U.PERIOD_YEAR,U.ATR_CODE,U.PART,U.BEGIN_DATE,U.PX, U.PX_COMMENT 
	from report_control_rows_U U
		left join @t_Forms F on U.Link_Form = F.Link_Form_imp
		left join #t_Tables T on U.Link_Table = T.Link_Table_imp
		left join #t_Cols C on U.Link_Col = C.Link_Col_imp
		left join #t_Rows R on U.Link_Row = R.Link_Row_imp
	where U.link_rule+@maxLink in (select R1.Link_rule from report_control_rules R1)


	insert report_control_group (Link_Rule, Link_Group_KS)
	select U.link_rule+@maxLink, G.Link_Group_KS
	from report_control_rules_U U join report_control_group_U GU on U.Link_rule = GU.Link_Rule
		join report_group_ks_U GU1 on GU1.Link_Group_KS = GU.Link_Group_KS
		join report_group_ks G on GU1.Code = G.Code
	where U.link_rule+@maxLink in (select R1.Link_rule from report_control_rules R1)
  
	SET IDENTITY_INSERT  REPORT_CONTROL_RULES OFF 

	drop table #t_Tables
	drop table #t_Cols
	drop table #t_Rows
	drop table #t_Rows_Del

-------------------------------------------------------------------------------
-- ПОДСКАЗКИ ДЛЯ ФОРМ
-------------------------------------------------------------------------------

	set @maxLink = isnull((select max(ID) from dbo.r_files),0)
	set @minLink = isnull((select min(ID) from dbo.r_files_U),0)

	SET IDENTITY_INSERT r_files ON 

	insert r_files (ID, Name, Path, CreateDate, FileSize)
	select ID+@maxLink - @minLink + 1, Name, Path, CreateDate, FileSize
	from dbo.r_files_U

	insert r_fileparts (File_ID, Orders, Body)
	select File_ID+@maxLink - @minLink + 1, Orders, Body
	from dbo.r_fileparts_U

	SET IDENTITY_INSERT r_files OFF

	update report_forms
	set hint_file_id = U.hint_file_id +@maxLink - @minLink+1
	from dbo.report_forms_U U
		join @t_Forms F on F.Link_Form_imp = U.Link_Form
	where report_forms.Link_Form = F.Link_Form


GO


GRANT EXECUTE ON dbo.[ro_import_form_struct] TO PUBLIC

GO


--Обновить системные таблицы
exec VX_UPGRADE_S_RELEASE
GO



--Обновить таблицы свода
EXECUTE dbo.ro_import_form_struct
GO


EXECUTE dbo.i_report_create_proc_on_setup
GO

EXECUTE dbo.ro_import_form_spr
GO




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

/*
© Кейсистемс (Keysystems), 2005
*/
CREATE   PROCEDURE VB_UPGRADE_REPORT_CALC
AS

declare @cScript varchar(8000) 

UPDATE RCF 
set [ORDER] = 1
from dbo.REPORT_CALC_FORMS RCF
inner join dbo.REPORT_CALC RC on RC.LINK_UP = RCF.LINK and RC.[isUSER] = 1
inner join dbo.REPORT_FORMS RF on RF.LINK_FORM = RCF.LINK_FORM 
inner join dbo.REPORT_FORMS_U RFU on RFU.CODE = RFU.CODE and RFU.[START_DATE] = RF.[START_DATE]
inner join dbo.REPORT_CALC_FORMS_U RCFU on RFU.LINK_FORM = RCFU.LINK_FORM 
where	RCF.[ORDER] = 0 and   
		dbo.BB_CALC_GET_ATR_VAL(RCF.LINK,RCF.LINK_FORM,2) = dbo.BB_CALC_U_GET_ATR_VAL(RCFU.LINK,RCFU.LINK_FORM,2)
		
DELETE RCF
from dbo.REPORT_CALC_FORMS RCF
inner join dbo.REPORT_FORMS RF on RF.LINK_FORM = RCF.LINK_FORM 
inner join dbo.REPORT_FORMS_U RFU on RFU.CODE = RF.CODE and RFU.[START_DATE] = RF.[START_DATE] 
inner join dbo.REPORT_CALC_FORMS_U RCFU on RFU.LINK_FORM = RCFU.LINK_FORM 
left join dbo.REPORT_CALC RC on RC.LINK_UP = RCF.LINK and RC.[isUSER] = 1
where	RCF.[ORDER] = 0 and RC.LINK is null and 
		dbo.BB_CALC_GET_ATR_VAL(RCF.LINK,RCF.LINK_FORM,2) = dbo.BB_CALC_U_GET_ATR_VAL(RCFU.LINK,RCFU.LINK_FORM,2)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC_FORMS]''), ''LINK_U'', ''AllowsNull'') is NULL
	alter table dbo.REPORT_CALC_FORMS add LINK_U int'
execute(@cScript)
	
set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC_FORMS]''), ''LINK_FORM_U'', ''AllowsNull'') is NULL
	alter table dbo.REPORT_CALC_FORMS add LINK_FORM_U int'
execute(@cScript)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC]''), ''LINK_U'', ''AllowsNull'') is NULL
	alter table dbo.REPORT_CALC add LINK_U int'
execute(@cScript)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC]''), ''LINK_UP_U'', ''AllowsNull'') is NULL
	alter table dbo.REPORT_CALC add LINK_UP_U int'
execute(@cScript)


set @cScript = 
'insert into dbo.REPORT_CALC_FORMS (LINK_FORM, [ORDER], USED, COMMENT, LINK_U, LINK_FORM_U)
select RF.LINK_FORM, RCFU.[ORDER], RCFU.USED, RCFU.COMMENT, RCFU.LINK, RCFU.LINK_FORM  
from dbo.REPORT_CALC_FORMS_U RCFU
inner join dbo.REPORT_FORMS_U RFU on RFU.LINK_FORM = RCFU.LINK_FORM
inner join dbo.REPORT_FORMS RF on RFU.CODE = RF.CODE and RFU.[START_DATE] = RF.[START_DATE]'
execute(@cScript)


set @cScript = 
'insert into dbo.REPORT_CALC (LINK_UP, Link_Col, Code_Row, Calc_Order, Comment, LINK_U)
select RCF.LINK, RCol.LINK_COL, RCU.Code_Row, RCU.Calc_Order, RCU.Comment, RCU.LINK 
from dbo.REPORT_CALC_FORMS RCF
inner join dbo.REPORT_CALC_U RCU on RCF.LINK_U = RCU.LINK_UP 
inner join dbo.REPORT_COLOUMNS_U RColU on RCU.Link_Col = RColU.Link_Col  
inner join dbo.REPORT_TABLES_U RTU on RColU.Link_TABLE = RTU.Link_TABLE  
inner join dbo.REPORT_TABLES RT on RT.ORDERS = RTU.ORDERS and RT.LINK_FORM = RCF.LINK_FORM
inner join dbo.REPORT_COLOUMNS RCol on RT.Link_TABLE = RCol.Link_TABLE and RColU.[ORDERS] = RCol.[ORDERS]'
execute(@cScript)


set @cScript = 
'insert into dbo.REPORT_CALC_ROWS (Link_Calc, Orders, [Sign], DT_Mask, KT_Mask, DT_EKR, KT_EKR, Type_KBK, [Type], Mask_KBK, DT_KT, OPER)
select RC.Link, RCRU.Orders, RCRU.[Sign], RCRU.DT_Mask, RCRU.KT_Mask, RCRU.DT_EKR, RCRU.KT_EKR, RCRU.Type_KBK, RCRU.[Type], RCRU.Mask_KBK, RCRU.DT_KT, RCRU.OPER 
from dbo.REPORT_CALC RC
inner join dbo.REPORT_CALC_ROWS_U RCRU ON RCRU.LINK_CALC = RC.LINK_U'
execute(@cScript)

set @cScript = 
'insert into dbo.REPORT_CALC_ATRIBUTE (LINK_CALC_FORM, CODE_ATR, STR_VAL, INT_VAL)
select RCF.LINK, RCAU.CODE_ATR, RCAU.STR_VAL, RCAU.INT_VAL 
from dbo.REPORT_CALC_FORMS RCF
inner join dbo.REPORT_CALC_ATRIBUTE_U RCAU on RCF.LINK_U = RCAU.LINK_CALC_FORM' 
execute(@cScript)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC_FORMS]''), ''LINK_U'', ''AllowsNull'') is not NULL
	alter table dbo.REPORT_CALC_FORMS drop column LINK_U'
execute(@cScript)
	
set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC_FORMS]''), ''LINK_FORM_U'', ''AllowsNull'') is not NULL
	alter table dbo.REPORT_CALC_FORMS drop column LINK_FORM_U'
execute(@cScript)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC]''), ''LINK_U'', ''AllowsNull'') is not NULL
	alter table dbo.REPORT_CALC drop column LINK_U'
execute(@cScript)

set @cScript = 
'IF COLUMNPROPERTY(object_id(''[dbo].[REPORT_CALC]''), ''LINK_UP_U'', ''AllowsNull'') is not NULL
	alter table dbo.REPORT_CALC drop column LINK_UP_U'
execute(@cScript)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

EXEC dbo.VB_UPGRADE_REPORT_CALC
GO


WHILE @@TRANCOUNT > 0
	raiserror('@@TRANCOUNT>0!!!',11,1)
GO
