if  exists (select * from sys.objects where object_id = object_id(N'dbo.upd_unique_hash') and type in (N'P', N'PC'))
	drop procedure dbo.upd_unique_hash
GO

create procedure dbo.upd_unique_hash
	@Uniques int,
	@Guid uniqueidentifier
as
begin
	set nocount on;

	if not exists (select * from #uniques_check) return

	update #uniques_check set value = replace(value, 'null|', cast(id1 as varchar(20)) + '|') where guid = @Guid and uniques = @Uniques

	if exists(select top 1 * from #uniques_check where guid = @Guid and uniques = @Uniques and patindex('0|%', value) > 0)
		begin
			update #uniques_check set value = replace(value, '0|', cast(id1 as varchar(20)) + '|') where guid = @Guid and uniques = @Uniques
		end

	update #uniques_check set hashvalue = hashbytes('MD5', lower(value)) where guid = @Guid and uniques = @Uniques

end
GO


exec dbo.gp
GO


if exists (select * from sys.objects where object_id = object_id(N'dbo.package_clone') and type in (N'P', N'PC'))
	drop procedure dbo.package_clone
GO

/*
if object_id('tempdb..#objects') is null create table #objects (entityid int, entityobjectid int)
if object_id('tempdb..#inputelids') is null create table #inputelids (elid int)
if object_id('tempdb..#replaces') is null create table #replaces (attrId int, replace varchar(MAX), outvalue varchar(MAX), replacemode varchar(MAX), outemode varchar(MAX))
*/
create procedure dbo.package_clone
	@gUnit uniqueidentifier
as
begin
	set nocount on

	declare @ErrorMessage varchar(4000), @ErrorSeverity int, @ErrorState int, @cStr varchar(4000),
		@hdid int, @nAttributeId int, @nEntityObject int, @cAlias varchar(128),	@cAttr varchar(100),
		@cAliasTblLvl2 varchar(40), @nEntityId int, @datatype varchar(50),	@cValues varchar(4000), @nId int,
		@ukEntityObjectId int, @cFields varchar(4000), @cClone varchar(20), @nEntityType int, @cTblClone varchar(128)

	create table #entitytbl (levels int, tbl varchar(100))
	create table #entitystru (levels int not null, tbl varchar(100) not null, id int not null, attr varchar(30) not null, datatype varchar(50) not null, hdid int not null, f int)
	create table #unique (id int, name varchar(50), matchcode varchar(250), minlev int, entityobjectid int, shareobjects int, fields varchar(max), fields_prot varchar(max), f int)
	create table #attr (uniques int, levels int, clmn varchar(128), clmn_type varchar(100), attrid int, refid int, alias varchar(100), f int)
	create table #uniques_check (uniques int, entityobjectid int, id1 int, hashvalue varbinary(100), value varchar(8000), guid uniqueidentifier)

	if object_id('tempdb..#unit_elements') is null
		create table #unit_elements(unit_id uniqueidentifier, id int, timestamp bigint)

--#region контроли
	if object_id('tempdb..#objects') is null
		begin
			raiserror ('Не определена таблица #objects для пакетного клонирования.', 16, 1)
			return
		end

	if not exists (select * from #objects)
		begin
			raiserror ('Не определен макет и объект для пакетного клонирования.', 16, 1)
			return
		end

	if object_id('tempdb..#inputelids') is null
		begin
			raiserror ('Не определена таблица #inputelids для пакетного клонирования.', 16, 1)
			return
		end

	if not exists (select * from #inputelids)
		begin
			raiserror ('Не определены элементы документа для пакетного клонирования.', 16, 1)
			return
		end

	if object_id('tempdb..#replaces') is null
		begin
			raiserror ('Не определена таблица #replaces для пакетного клонирования.', 16, 1)
			return
		end
--#endregion контроли

	select top (1) @nEntityId = entityid from #objects
	select @cAliasTblLvl2 = 't' + cast(hdid as varchar(20)) from dbo.dynamic_structure where entityid = @nEntityId and levels = 1 and parentid is null
	select @cClone = 'batchcopy_'
	select @nEntityType = dbo.EntityTypeId(@nEntityId)

--select @nEntityId , @nEntityTypeId
	-- уникальности
	exec dbo.get_unique @nEntityId = @nEntityId, @nMode = 1

	-- удаляем лишние уникальности
	delete uk from #unique uk where uk.entityobjectid > 0
		and not exists (select * from #objects obj where uk.entityobjectid = obj.entityobjectid)

	delete a from #attr a where not exists (select * from #unique uk where a.uniques = uk.id)

--select * from #unique
--select * from #attr

	if exists(select * from #unique) and not exists (select * from #attr a, #replaces r where a.attrid = r.attrId)
		begin
			raiserror ('В макете/документе определена уникальность, которая при клонировании будет нарушена.', 16, 1)
			return
		end

	insert #entitytbl (levels, tbl)
	select distinct levels, tbl from dbo.entitystru(@nEntityId)
	update #entitytbl set tbl = @cClone + tbl

	insert #entitystru (levels, tbl, id, attr, datatype, hdid)
	select distinct levels, tbl, id, attr, datatype, hdid
	from dbo.entitystru(@nEntityId) es, #replaces r
	where es.id = r.attrid

	update #entitystru set datatype = 'int' where datatype = 'bit'
	select top (1) @cTblClone = tbl from #entitytbl where levels = 2

	begin try

		-- создаем клон макета
		exec dbo.ds_clone @nEntityId = @nEntityId, @cPrefixClone = @cClone

		while exists (select * from #objects)
			begin
				select top (1) @nEntityObject = coalesce(entityobjectid, 0) from #objects order by entityobjectid

				-- скидываем id элементов в клонирование
				delete #unit_elements where [unit_id] = @gUnit

				if @nEntityType = 1
					insert into #unit_elements ([unit_id], [id]) select @gUnit, elid from #inputelids

				if @nEntityType = 2
					insert into #unit_elements ([unit_id], [id]) select @gUnit, elid from #inputelids where entityobjectid = @nEntityObject

				-- клонирование
				execute dbo.cl_clone_elements @nentityid = @nEntityId, @nentobjid=@nEntityObject, @gunit=@gUnit, @cPrefixClone = @cClone

				select @cStr = 'update KS_DDLControl.' + @cTblClone + ' set guid = newid()'
				execute (@cStr)

				update #entitystru set f = 0

--#region замена
				while exists (select * from #entitystru where f = 0)
					begin
						select @hdid = hdid, @cAlias = 't' + cast(hdid as varchar(20)), @nAttributeId = id, @cAttr = attr, @datatype = datatype
						from #entitystru
						where f = 0
						order by levels, hdid, id

						select @cValues = stuff((select distinct ',cast(''' + replacevalue + ''' as ' + @datatype + ')' from #replaces where attrid = @nAttributeId and replacemode = 32 for xml path('')),1,1,'')
						select @cStr = 'update ' + @cAlias + ' set ' + @cAlias + '.' + @cAttr + ' = '
							+ (select top (1)
											case
												when outvalue = 'null' then 'null '
												when outvalue = '' and patindex('%int', @datatype) > 0 then 'null '
												when outvalue = '' and patindex('%int', @datatype) = 0 then ''''
												else 'cast(''' + outvalue + ''' as ' + @datatype + ') '
											end
								from #replaces where attrid = @nAttributeId)
							+ replace(dbo.GetEntitySelect(@nEntityId, @hdid, 1), 'select <fields> ', '')
							+ ' where ' + (select top 1
																			case
																				when replacemode = 1 then @cAlias + '.' + @cAttr + ' is null '
																				when replacemode = 2 then @cAlias + '.' + @cAttr + ' is not null '
																				when replacemode = 3 then '1 = 1'
																				when replacemode = 32 then @cAlias + '.' + @cAttr + ' in (' + @cValues + ')'
																			end
																	from #replaces where attrid = @nAttributeId)

						select @cStr = replace(@cStr, 'KS_DDLControl.', 'KS_DDLControl.' + @cClone)
--print @cStr
						execute (@cStr)

						update #entitystru set f = 1 where @hdid = hdid and @nAttributeId = id
					end
--#endregion замена

				delete #unit_elements where [unit_id] = @gUnit

--#region уникальность
				while exists(select * from #unique where f = 0)
					begin
						if @nEntityType = 1
							select top (1) @nId = id, @ukEntityObjectId = entityobjectid, @cFields = fields from #unique where f = 0 and entityobjectid = 0 order by id

						if @nEntityType = 2
							select top (1) @nId = id, @ukEntityObjectId = entityobjectid, @cFields = fields from #unique where f = 0 and (entityobjectid = 0 or entityobjectid = @nEntityObject) order by id

--select @nId, @ukEntityObjectId,@cFields
						-- при условии что атрибуты уникальности в одной ветке
						select @hdid = cast(replace(alias, 't', '') as  int) from #attr where uniques = @nId and levels = (select max(levels) from #attr where uniques = @nId and levels < 2000)
						-- заполняем таблицу для проверки
						select @cStr = 'insert into #uniques_check (uniques, id1, entityobjectid, value, guid) '
						+ replace(dbo.getentityselect(@nEntityId, @hdid, 2), '<fields>', cast(@nId as varchar(20)) + ', 0, ' + cast(@ukEntityObjectId as varchar(20)) + ', ' + @cFields + ', ' + @cAliasTblLvl2 + '.guid')

						select @cStr = replace(@cStr, 'KS_DDLControl.', 'KS_DDLControl.' + @cClone)
--print @cStr
						execute (@cStr)

						update #uniques_check set hashvalue = hashbytes('MD5', value) where value is not null
--select * from #uniques_check
						-- выполняем проверку
						select @cStr = 'execute KS_DDLControl.uk_check_' + cast(@nEntityId as varchar(20)) + ' @ProtRid=''' + cast(@gUnit as varchar(40)) + ''''
						execute(@cStr)

						-- если есть протокол, то выходим
						if exists (select * from dbo.sys_protocols where rid = @gUnit)
							begin
								-- удаляем клон макета
								exec dbo.ds_clone_del @nEntityId = @nEntityId, @cPrefixClone = @cClone
								return
							end

						update #unique set f = 1 where id = @nId
					end
--#endregion уникальность

				-- сохранение
				exec dbo.ds_save @nentityid = @nEntityId, @cListAttr = '', @cPrefixClone = @cClone

				if exists (select * from #uniques_check)
					begin
						select @cStr = 'update #uniques_check set id1 = cl.id_sync from #uniques_check uc, KS_DDLControl.' + @cTblClone + ' cl	where uc.guid = cl.guid'
						execute(@cStr)
						select @cStr = 'execute KS_DDLControl.uk_check_' + cast(@nEntityId as varchar(20)) + ' @ProtRid=''' + cast(@gUnit as varchar(40)) + ''''
						execute(@cStr)
					end

				select @cStr = (select '; delete KS_DDLControl.' + tbl from #entitytbl order by levels desc for xml path(''))
				execute(@cStr)

				delete #objects where @nEntityObject = coalesce(entityobjectid, 0)
			end

		-- удаляем клон макета
		exec dbo.ds_clone_del @nEntityId = @nEntityId, @cPrefixClone = @cClone

	end try
	begin catch
		select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)

		-- удаляем клон макета
		exec dbo.ds_clone_del @nEntityId = @nEntityId, @cPrefixClone = @cClone
	end catch

	return	1
end
GO


exec dbo.gp
GO


if  exists (select * from sys.objects where object_id = object_id(N'dbo.cl_drop') and type in (N'P', N'PC'))
	drop procedure dbo.cl_drop
GO


/*
Назначение: Процедура удаления динамических таблиц сущности
Входные параметры:
	@nEntityId int, - ID сущности

Код возврата: нет
Пример вызова:
  exec dbo.cl_drop @nEntityId = 371
  exec dbo.cl_drop @nEntityId = 371, @nEntityTypeId = 1

Автор: Кузнецов А.П. 24.05.2013
*/

create procedure dbo.cl_drop
	@nEntityId int,
	@nDeployType int,
	@nEntityTypeId int = null
as
begin
	set nocount on

	declare
		@cEntityId varchar(20), @bTransCreated bit, @nId int,
		@cTbl varchar(100), @cTblClone varchar(100), @cStr varchar(4000), @ErrMsg varchar(4000), @cPrefixTable varchar(20)

	create table #hier_structure (levels int not null, tbl varchar(100) not null, tbl_clone varchar(100) null)
	create table #detailidlist (id int not null)

	if @nEntityTypeId is null
		select @nEntityTypeId = dbo.entitytypeid(@nEntityId)

	if @nEntityTypeId is null
		begin
			select @ErrMsg = 'Не определен тип сущности (EntityType)'
			raiserror (@ErrMsg, 16, 0)
			return
		end

	-- обрабатывается только тип 0 и 2
	if @nDeployType = 1
		begin
			return
		end

	if not @nEntityTypeId in (1,2)
		begin
			return
		end

	if @@trancount = 0
	begin
		begin transaction
		select @bTransCreated = 1
	end

	begin try
		select @cEntityId = convert(varchar(20), @nEntityId), @cPrefixTable = 'KS_DDLControl.'

		insert #hier_structure (levels, tbl)
		select distinct levels, tbl from dbo.entitystru(@nEntityId)

		if @nEntityTypeId = 1
			begin
				-- права
				select @cStr = dbo.object_drop_script (('cla_' + @cEntityId), 'U', null, @cPrefixTable)
				execute (@cStr)

				-- иерархии
				select @cStr = dbo.object_drop_script (('clh_' + @cEntityId), 'U', null, @cPrefixTable)
				execute (@cStr)
			end

		if @nEntityTypeId = 2
			begin
				-- кубы
				select @cStr = dbo.object_drop_script (('cube_' + @cEntityId), 'U', null, @cPrefixTable)
				execute (@cStr)

				select @cStr = dbo.object_drop_script (('cubet_' + @cEntityId), 'U', null, @cPrefixTable)
				execute (@cStr)

				select @cStr = dbo.object_drop_script (('clt_' + @cEntityId), 'U', null, @cPrefixTable)
				execute (@cStr)
			end

		-- уникальности
		select @cStr = dbo.object_drop_script (('uk_' + @cEntityId), 'U', null, @cPrefixTable)
		execute (@cStr)
		select @cStr = dbo.object_drop_script (('ukt_' + @cEntityId), 'U', null, @cPrefixTable)
		execute (@cStr)

		while exists (select * from #hier_structure)
			begin
				select top (1) @cTbl = tbl, @cTblClone = 'clone_' + tbl from #hier_structure order by levels desc, tbl desc

				-- основная таблица
				select @cStr = dbo.object_drop_script (@cTbl, 'U', null, @cPrefixTable)
				execute (@cStr)

				-- клон по умолчанию
				select @cStr = dbo.object_drop_script (@cTblClone, 'U', null, @cPrefixTable)
				execute (@cStr)

				-- клон ЦКИ
				select @cTblClone = 'ccd_' + @cTbl
				select @cStr = dbo.object_drop_script (@cTblClone, 'U', null, @cPrefixTable)
				execute (@cStr)

				delete #hier_structure where @cTbl = tbl
			end

		insert into #detailidlist (id)
		select evd.id
		from dbo.entityviewdetail evd, dbo.dynamic_structure ds
		where ds.entityid = @nEntityId and ds.attributeid is not null and
			coalesce(evd.attributeid, 0) = ds.attributeid

		while exists(select * from #detailidlist)
			begin
				select top (1) @nId = id from #detailidlist order by id
				execute dbo.delete_viewdetails @nDetailId = @nId
				delete #detailidlist where id = @nId
			end

		execute dbo.deletefuncprotocol @nEntityId = @nEntityId

		delete dbo.dynamic_uniques where entityid = @nEntityId
		delete dbo.dynamic_structure where entityid = @nEntityId
    	execute dbo.admin_create_v_prim_for @nMode = 0

		if @bTransCreated = 1
			commit transaction
	end try
	begin catch
		if @bTransCreated = 1
			rollback transaction

		select @ErrMsg = error_message();
		raiserror (@ErrMsg, 16,0, @ErrMsg)
		return
	end catch
end
GO


exec dbo.gp
GO

update op
 set cloneparam = 1
from dbo.ObjectParams op inner join dbo.attribute a on op.attributeid = a.id and a.systemtype in (1,2,25)
where op.cloneparam = 0

if  exists (select * from sys.objects where object_id = object_id(N'dbo.clh_build_hier') and type in (N'P', N'PC'))
	drop procedure dbo.clh_build_hier
GO


/*
Назначение: Процедура заполнения динамических таблиц иерархии
Входные параметры:
	@nEntityId int, - ID сущности

Код возврата: нет
Пример вызова:
  exec [dbo].[CLH_BUILD_HIER] @nEntityHierarchyId = 371

Автор: festdeim 16.10.2017
*/

create proc dbo.clh_build_hier
	@nEntityHierarchyId int
as
begin
	set nocount on
	begin try

		declare @cTableH varchar(30), @cStr varchar(max), @cStr1 varchar(max), @cStr1_1 varchar(max), 
				@cStr2 varchar(max), @cStr3 varchar(max),
				@cStrUpdate varchar(max), @cAT_COL varchar(128),@cAT_COL_ADD varchar(128),
				@cTABLE_CL_ATTR varchar(128),@cTABLE_CL_ADDATTR varchar(128),
			    @nH_AttrId int,@nH_AttrAddId int,@nH_Type int, @nSequence int,
			    @nEntityId int, @attrParentId int,@isNewSchema int

		select
		       @nEntityId = entityid,
			   @nH_AttrId = attributeid,
			   @nH_AttrAddId = isnull(attributeadditionalid,0),
			   @nH_Type = hierarchytype,
			   @cAT_COL = (select ds.tablecolumn from dbo.dynamic_structure ds where ds.attributeid = deh.attributeid and ds.entityid = deh.entityid),
			   @isNewSchema = isnull(isnewschema,0)
		from dbo.dynamic_entity_hierarchy deh
		where id = @nEntityHierarchyId

		declare @max_ind_length int
		if ((select lenght from dynamic_structure ds inner join attributetype at on ds.attributetypeid = at.id where ds.entityid = @nEntityId and attributeid = @nH_AttrId)>896 )
			set @max_ind_length = 1

		if (@nH_AttrAddId > 0)
			set @isNewSchema = 1

		select @cTableH = 'ks_ddlcontrol.clh_' + convert(varchar(20),@nEntityId)
		if (@isNewSchema > 0)
			set @cTableH = @cTableH + '_' + convert(varchar(20),@nEntityHierarchyId)

		if object_id(@cTableH) is null
			return

		create table #h_tmp (id int, elemid int, isredefined int, isignore int)
		create table #h_mask (id int, mask varchar(1000), sequence int, maskquery varchar(2000), lenmask int)

		if (@nH_Type = 0)
			select @attrParentId = parent.hdid, @cTABLE_CL_ATTR = 'ks_ddlcontrol.' + parent.tablecolumn from dbo.dynamic_structure ds
													inner join dbo.dynamic_structure parent on parent.hdid = ds.parentid
													where ds.AttributeId = @nH_AttrId
		else
			select top 1 @attrParentId = ds.hdId, @cTABLE_CL_ATTR = 'ks_ddlcontrol.' + ds.tablecolumn from dbo.dynamic_structure ds
													where ds.levels = 1 and ds.entityid = @nEntityId

		set @attrParentId = (select top 1 ds.parentId from dynamic_structure ds where ds.AttributeId = @nH_AttrAddId)
		set @cTABLE_CL_ADDATTR = 'ks_ddlcontrol.'+(select ds.tablecolumn from dbo.dynamic_structure ds where ds.hdid = @attrParentId)

		-- Иерархия
		insert #H_MASK (id, mask, maskquery, sequence)
		select deh.id, isnull(deh.mask,'#'), deh.maskquery, deh.sequence
		from dbo.dynamic_entity_hierarchydetail deh
		where deh.dehierarchyid = @nEntityHierarchyId
		order by deh.dehierarchyid, deh.id

		update #h_mask set lenmask = len(mask) + 1 - charindex('#',reverse(mask)) where maskquery is null and mask is not null  and charindex('#',mask) > -1
		update #h_mask set lenmask = len(mask) where lenmask is null
		update #h_mask set maskquery = 'substring(#field,1,' + convert(varchar(20),lenmask) + ') + ' +
										case when len(mask) - lenmask < 1 then '''''' else 'right(#field,' + convert(varchar(20),len(mask) - lenmask) + ')' end
						  where maskquery is null and mask is not null

		if (@nH_Type = 0 and not exists (select * from #h_mask ))
		begin
			set @cStr = 'Одна из иерархий справочника c ИД=' + convert(varchar(20),@nEntityId) + ' не содержит уровни'
			raiserror(@cStr,16,1)
			return
		end


		if (@nH_AttrAddId > 0)
		begin
			select @cat_col_add = ds.tablecolumn from dbo.dynamic_structure ds where ds.attributeid = @nh_attraddid

			select @cStr = 'delete from ' + @cTableH
			set @cStr = @cStr + char(10) + 'insert ' + char(10) + @cTableH + ' (elemid, coladd)' + char(10)
						+ 'select p.id' + ',' + @cAT_COL_ADD
						+ char(10) + 'from ' + @cTABLE_CL_ATTR + ' p'
			if (@cTABLE_CL_ATTR <> @cTABLE_CL_ADDATTR)
				set @cStr = @cStr + char(10) + 'left join ' + @cTABLE_CL_ADDATTR + ' c on c.id_up = p.id '  + char(10)
						+ 'order by p.id,' + @cAT_COL_ADD
			select @cStr1 = 'insert #h_tmp (id, elemid'
			set @cStr2 = 'select row_number() over(order by p.id, ' + @cAT_COL + ', ' + @cAT_COL_ADD + '), p.id'
		end
		else
		begin
			select @cStr = 'delete h from ' + @cTableH  + ' h where not exists(select * from ' + @cTABLE_CL_ATTR + ' s where h.id = s.id)'
			select @cStr = @cStr + char(10) + 'insert ' + @cTableH + ' (id) select id from '
						   + @cTABLE_CL_ATTR + ' s where not exists (select * from ' + @cTableH + ' h where h.id = s.id)'
			select @cStr1 = 'insert #h_tmp (id'
			set @cStr2 = 'select p.id'
		end

		exec (@cStr)

		select @cStr = 'alter table #h_tmp add ' + stuff((select ', ln_' + convert(varchar(20), id) + ' varchar(500)' from #h_mask
		                                                  order by sequence for xml path('')),1,1,'')
		exec (@cStr)

		if (@nH_Type = 0)
		begin
			select @cStr = 'alter table #h_tmp add ' + @cAT_COL + ' varchar(1000)'
			if (@nH_AttrAddId > 0)
				select @cStr = @cStr + char(10) + 'alter table #h_tmp add ' + @cAT_COL_ADD + ' varchar(1000)'
			exec (@cStr)
			select @cStr1 = @cStr1 + ', ' + @cAT_COL
			select @cStr2 = @cStr2 + ', ' + @cAT_COL
		end

		if (@nH_Type = 0  and @max_ind_length = 0)
			select @cStr = 'create nonclustered index ind_' + substring(@cAT_COL,2,len(@cAT_COL) - 2) + ' on #h_tmp (id,' + @cAT_COL + ')'
		else
			select @cStr = 'create nonclustered index ind_' + substring(@cAT_COL,2,len(@cAT_COL) - 2) + ' on #h_tmp (id)'
		exec (@cStr)

		set @cStr = ' from ' + @cTABLE_CL_ATTR + ' p '

		if (@nH_AttrAddId > 0)
		begin

			if (@cTABLE_CL_ATTR <> @cTABLE_CL_ADDATTR)
				set @cStr =  @cStr + char(10) + 'left join ' + @cTABLE_CL_ADDATTR + ' c on c.id_up = p.id'
			select @cStr1 = @cStr1 + ',' + @cAT_COL_ADD
			select @cStr2 = @cStr2 + ',' + @cAT_COL_ADD
		end

		if (@nH_Type = 0)
		begin
			select @cStr1 = @cStr1 + (select ', ln_' + convert(varchar(20), ID) from #H_MASK order by [SEQUENCE] for xml path(''))
			select @cStr2 = @cStr2 + (select ',' + replace(MASKQUERY,'#field',@cAT_COL) from #H_MASK order by [SEQUENCE] for xml path(''))
		end
		select @cStr3 = @cStr1 + ') ' + char(10) + @cStr2 + @cStr

		exec (@cStr3)

		--акутализируем основную таблицу только для доп атрибутов
		if (@nH_AttrAddId > 0)
		begin
			set @cStr = 'update h set h.Id = s.id from ' + @cTableH + ' h' + char(10) + 'inner join #h_tmp s on h.elemid = s.elemid'
						+ ' and isnull(h.coladd,0) = isnull(s.' + @cAT_COL_ADD + ',0)'
			exec (@cStr)
		end

		declare @nID int,@nID_NEXT int,
				@cH_COL varchar(128), @cH_COL_NEXT varchar(128),
				@lvl int,@cUpdateFields	varchar(1000),@cWhere varchar(1000),
				@d_beg datetime ,@useOper varchar(20),
				@i int

		set @lvl = 1
		set @cUpdateFields = ''
		set @cWhere = ''
		update #h_tmp set isredefined = null, isignore = null

		set @cStrUpdate = ''


-- обнулить все дерево
		set @cStr = 'update h set '
		select @cStr = @cStr + 'h_' + convert(varchar(20), @nEntityHierarchyId) + '_' + convert(varchar(20), id) + ' = null,' from #h_mask
		set @cStr = substring(@cStr,1,len(@cStr) - 1) + ' from ' + @cTableH + '  h'
		exec (@cStr)

--set @i=1
		while exists (select * from #h_mask)
			begin

				set @cStr = ''

				select top 1 @nID = id, @nSequence = sequence, @useOper = case when charindex('_', mask) > 0 then ' like ' else ' = ' end from #h_mask order by sequence
				select @cH_COL = 'h_' + convert(varchar(20), @nEntityHierarchyId) + '_' + convert(varchar(20), @nID)

				set @nID_NEXT = (select top 1 id from #H_MASK where sequence >= @nSequence + 1 order by sequence)
				if (@nID_NEXT is not null)
					select @cH_COL_NEXT  = 'h_' + convert(varchar(20), @nEntityHierarchyId) + '_' + convert(varchar(20), @nID_NEXT)
				else
					select @cH_COL_NEXT = null;

set @d_beg = getdate()

				if (@nH_Type = 0)
				begin
					-- переопределенные значения
					if (@nH_AttrAddId = 0)
					begin
						set @cStr = @cStr + char(10) + '
							 update h set ' + @cH_COL + ' = isnull(deh.parentId,deh.elementId)'

							 if (@cH_COL_NEXT is not null and @lvl > 1)
								set @cStr = @cStr + ',' + @cH_COL_NEXT + ' = deh.elementId '

							 set @cStr = @cStr +
							 'from ' + @cTableH  + ' h
							 inner join dbo.dynamic_entity_hierarchyelement deh on deh.dehierarchyId =  ' + convert(varchar(20), @nEntityHierarchyId)
										+ ' and deh.elementId = H.id
							 and deh.lvl = ' + convert(varchar(20),@lvl)
					end
					else begin
						set @cStr = @cStr  + char(10) + '
							 update h set ' + @cH_COL + ' = isnull(h_p.id,h.id)'

							 if (@cH_COL_NEXT is not null)
								set @cStr = @cStr + ',' + @cH_COL_NEXT + ' = h.Id '

							 set @cStr = @cStr +
							 'from ' + @cTableH  + ' h
							 inner join dbo.dynamic_entity_hierarchyelement deh on deh.dehierarchyId =  ' + convert(varchar(20), @nEntityHierarchyId)
										+ ' and deh.elementid = h.elemid and isnull(deh.elemaddvalue,0) = isnull(h.coladd,0)
							 inner join ' + @cTableH  + ' h_p on h_p.elemid = deh.parentid and isnull(deh.parentelemaddvalue,0) = isnull(h_p.coladd,0)'
							 + ' where deh.lvl = ' + convert(varchar(20),@lvl)
					end


					set @cStr = @cStr  + char(10) + '
							 update #h_tmp set isignore = 1
							 from #h_tmp h
							 where id in (select id from ' + @cTableH + ' where ' + @cH_COL + ' is not null)'

					if (@lvl > 1)
							begin

								set @cStr = @cStr + char(10) + '
									 update h set ' + substring(@cUpdateFields,1,len(@cUpdateFields)-1) + '
									 from  #h_tmp a
									 inner join ' + @cTableH + ' h on a.id = h.id
									 inner join ' + @cTableH + ' h1 on h1.Id = h.' + @cH_COL+ '
									 where a.isignore = 1 and a.isredefined is null'
							end

					set @cStr1_1 = '
							 update h set ' + @cH_COL + ' = (select max(s2.id) from #h_tmp s2 where s2.' + @cAT_COL + @useOper + 's1.ln_' + convert(varchar(20), @nID)
					
					set  @cStr1 = ' )
							 from ' + @cTableH  + ' h
							 inner join #h_tmp s1 on s1.id = h.id'

					set @cStr3 = '
							 where ' + @cH_COL + ' is null and s1.isredefined is null and s1.isignore is null'

					if (@nH_AttrAddId = 0)
						set @cStr2 = ''
					else
						set @cStr2 = ' and isnull(s2.'  + @cAT_COL_ADD + ',0) = isnull(s1.' + @cAT_COL_ADD + ',0)'
					
					set @cStr = @cStr + char(10) +  @cStr1_1 + @cStr2  + @cStr1 + @cStr3
										
					if (@nH_AttrAddId > 0)
					begin
						set @cStr2 = ' and isnull(s2.' + @cAT_COL_ADD + ',0) = 0'
						set @cStr = @cStr + char(10) + @cStr1_1 + @cStr2  + @cStr1 + @cStr3
					end

					set @cStr = @cStr + char(10) +  '
							 update #h_tmp set isredefined = 1
							 from ' + @cTableH  + ' h
							 inner join #h_tmp on #h_tmp.id = h.id
							 where h.' + @cH_COL + ' = h.id'

					set @cUpdateFields = @cUpdateFields + (@cH_COL + ' = h1.' + @cH_COL + ',')

				end
--пользовательская
				else begin

					if (@nSequence = 1)

						select @cStr = @cStr + '
							update h set ' + @cH_COL + ' = h.id
							from ' + @cTableH  + ' h
							where not exists (select * from dbo.dynamic_entity_hierarchyelement deh
										where deh.elementid = h.id and deh.lvl > 1 and deh.dehierarchyid = ' + convert(varchar(20), @nEntityHierarchyId) + ' )'

					else
					begin

						set @cStr = @cStr + '
							update h set ' + @cH_COL + ' = h.id
							from ' + @cTableH  + ' h
							where exists (select * from dbo.dynamic_entity_hierarchyelement deh where deh.elementid = h.id
									and deh.lvl = ' + convert(varchar(20), @nSequence ) + ' and deh.dehierarchyid = ' + convert(varchar(20), @nEntityHierarchyId) + ')
									and ' + @cH_COL + ' is null'

					end

					if (@nSequence <> (select max(sequence) from #h_mask))
					begin

						set @cStrUpdate = '
							update h set ' + @cH_COL + ' = h_p.' +  @cH_COL + '
							from ' + @cTableH  + ' h
							inner join dbo.dynamic_entity_hierarchyelement deh on deh.elementid = h.' + @cH_COL_NEXT + ' and deh.dehierarchyid = ' + convert(varchar(20), @nEntityHierarchyId) + '
							inner join ' + @cTableH  + ' h_p on h_p.id = deh.parentid
							where h.' + @cH_COL + ' is null
							' + @cStrUpdate
					end
				end


--print @cStr
				exec (@cStr)

--print 'время выполнения шага: '
--print (datediff (second,@d_beg,getdate()))
--set @i = @i + 1

--if (@i=1)
--return

				delete #h_mask where @nID = id
				set @lvl = @lvl + 1
			end

		--	delete #H_DESCR where @nEntityHierarchyId = H_ID
			--print @cStrUpdate
			exec (@cStrUpdate)

		--end

	end try
	begin catch
		declare @ErrorMessage nvarchar(4000)
		select @ErrorMessage = ERROR_MESSAGE()
		raiserror(@ErrorMessage,16,1)
	end catch
end


GO

if exists (select * from sys.objects where object_id = object_id(N'dbo.cl_create') and type in (N'P', N'PC'))
	drop procedure dbo.cl_create
GO


-- execute dbo.CL_CREATE @nEntityId = 957
create procedure dbo.cl_create
	@nEntityId int
as
begin
	set nocount on
	declare @cStr varchar(max), @cFields varchar(max), @cEntity varchar(20), @error int, @message varchar(4000),
		@nLevels int, @nId int, @nParentId int,
		@cEntityPrefix varchar(10),		--Префикс таблиц данных
		@cAccessPrefix varchar(10),		--Префикс таблиц доступа
		@cTablePostfix varchar(100),
		@cTableName varchar(128),
		@cUserTableName varchar(128),
		@nEntityType int,
		@cSchemeName varchar(30),
		@nEtalon int

	create table #cldpl (id int, reference int, rhdid int, entitytype int)
	create table #cldpl_tree (id int, levels int, sequence int, hdtype int, attributeid int, parentid int)
	create table #cldpl_lev (id int, levels int, sequence int, hdtype int, attributeid int, parentid int, tablename varchar(128))

	begin try
		select @cEntity = cast(@nEntityId as varchar(20)), @nEntityType = dbo.EntityTypeId(@nEntityId), @cEntityPrefix = dbo.EntityPrefix(@nEntityId)
		select @cAccessPrefix = @cEntityPrefix + 'a_', @cSchemeName = 'ks_ddlcontrol'

		select @nEtalon = dbo.AvailableRegList()

		insert into #cldpl (id, reference, rhdid, entitytype)
		select a.id, a.reference,
		(select top (1) et.id from dbo.entity_tree(a.reference) et order by et.id) as rhdid,--HIERARCHYDETAILID ССЫЛКИ НА СПРАВОЧНИК
		(select e1.entitytype from dbo.entity e1 where a.reference = e1.id) as entitytype
		from dbo.entity e, dbo.attribute a
		where e.id = @nEntityId and e.id = a.entityid and e.entitytype in (1,2)

		-- Логическая структура Entity
		insert into #cldpl_tree (id, levels, sequence, hdtype, attributeid, parentid)
		select id, levels, sequence, hdtype, attributeid, parentid from dbo.entity_tree(@nEntityId)

--update #cldpl set isunique = 1
--select * from #cldpl
--select * from #cldpl_tree
--return

		insert into #cldpl_lev (id, levels, sequence, hdtype, attributeid, parentid, tablename)
		select t.id, t.levels, t.sequence, t.hdtype, t.attributeid, t.parentid, hd.stable from #cldpl_tree t
		left join dbo.hierarchydetail hd on hd.id = t.id
		where t.attributeid is null --order by Levels desc, Sequence desc

--#region Удаляем "старые" таблицы
		if @nEntityType = 1--справочник
			begin
				-- Удаляем "старые" таблицы
				select @cStr = dbo.object_drop_script((@cAccessPrefix + @cEntity), 'U',null,null)

				execute (@cStr)
			end

		select @cStr = stuff((
		select ';' + dbo.object_drop_script((@cEntityPrefix + @cEntity + '_' + cast(a.id as varchar(20))), 'U',null, null) as [text()]
		from #cldpl_lev a
		order by levels desc
		for xml path('')),1,1,'')
		execute (@cStr)

--#endregion Удаляем "старые" таблицы

		-- Построение структуры справочника/источника
		while exists(select * from #cldpl_lev)
			begin
				select top (1) @nId = id, @cUserTableName = tablename from #cldpl_lev order by id
				select @nLevels = levels from #cldpl_lev where id = @nId
				select @nParentId = parentid from #cldpl_lev where id = @nId
				select @cTablePostfix = @cEntity + '_' + cast(@nId as varchar(20))
				select @cTableName = @cEntityPrefix + @cTablePostfix
--select @cTableName

				-- Формируем список ВСЕХ полей для create
				select @cFields = stuff((select ',' + dbo.atributestru(ct.attributeid, 3) + ' null'
				from #cldpl_tree ct
				where not ct.attributeid is null and ct.parentid = @nId
				for xml path('')),1,1,'')
--select @cFields

--#region Верхний уровень
				if @nLevels = 1
					begin
						-- Создаем структуру головной таблицы справочника/источника
						select @cStr = 'create table ' + @cSchemeName  + '.' + @cUserTableName + ' (id int identity(1,1) not null, ' +
							case when coalesce(@cFields,'')<>'' then @cFields + ', ' else '' end +
							case @nEntityType when 2 then 'documentid int,' else '' end +
							case @nEtalon when 1 then '' else 'sync_stmp bigint,' end +
							'guid uniqueidentifier not null default (newid()), unit uniqueidentifier, timestamp timestamp, eodguid uniqueidentifier, constraint [pk_' + @cTableName + '] primary key clustered (id asc) with (fillfactor = 90))'

						execute (@cStr)

						--index UNIT, DOCUMENTDID в головной таблице, пока только для источников
						if @nEntityType = 2
							begin
								select @cStr = 'create index ix_' + @cTableName + '_unit on ' + @cSchemeName  + '.' + @cUserTableName + ' (unit)'
								execute (@cStr)
								select @cStr = 'create index ix_' + @cTableName + '_01 on ' + @cSchemeName  + '.' + @cUserTableName + ' (documentid) include (id, timestamp)'
								execute (@cStr)
							end
					end
--#endregion Верхний уровень

--#region Нижние уровни
				if @nLevels > 1
					begin
						-- Создаем таблицу нижнего уровня
						-- Создаем структуру дочерней таблицы справочника/источника
						select @cStr = 'create table ' + @cSchemeName  + '.' + @cUserTableName + ' (id int identity(1,1) not null, id_up int, ' +
							case when coalesce(@cFields,'')<>'' then @cFields + ', ' else '' end +
							 'guid uniqueidentifier not null default (newid()), unit uniqueidentifier, timestamp timestamp, eodguid uniqueidentifier, constraint [pk_' + @cTableName + '] primary key clustered (id asc) with (fillfactor = 90))'
						--select @cStr, @cTableName, @cFields
						execute (@cStr)

						-- Создаем связи на головную таблицу
						select @cStr = 'alter table ' + @cSchemeName  + '.' + @cUserTableName + ' with check add constraint FK_' + @cTableName + '_' + @cEntityPrefix + @cEntity + '_' + cast(@nParentId as varchar(20)) + ' foreign key (id_up)
						references ' + @cSchemeName  + '.' +(select stable from dbo.hierarchydetail where id = @nParentId) + ' (id) on update no action on delete no action'
						--select @cStr
						execute (@cStr)
						--index ID_UP include (ID, TIMESTAMP)
						select @cStr = 'create index ix_' + @cTableName + '_id_up on ' + @cSchemeName  + '.' + @cUserTableName + ' (id_up) include (id,timestamp)'
						execute (@cStr)

						--index UNIT в нижнем уровне, пока только для источников
						if @nEntityType = 2
							begin
								select @cStr = 'create index ix_' + @cTableName + '_unit on ' + @cSchemeName  + '.' + @cUserTableName + ' (unit)'
								execute (@cStr)
							end

					end
--#endregion Нижние уровни

--#region Создаем связи на макеты справочников
				if exists(select * from #cldpl a, #cldpl_tree ct
					where not a.reference is null and ct.parentid = @nId and a.id = ct.attributeid and not ct.attributeid is null and a.entitytype = 1)
				begin

					select @cStr = stuff((
					select ';alter table ' + @cSchemeName  + '.' + @cUserTableName + ' with check add constraint' +
						' fk_cl_' + cast(a.id as varchar(20)) + '_cl_' + cast(a.reference as varchar(20)) + '_' + cast(a.rhdid as varchar(20)) +
						' foreign key (' + atr.scolumn + ')' +
						' references ' + @cSchemeName  + '.' + (select replace(tablecolumn, 'ks_ddlcontrol.', '') from dbo.dynamic_structure ds where ds.hdid = a.rhdid) + ' (id)'
						as [text()]
					from #cldpl a inner join #cldpl_tree ct on a.id = ct.attributeid
						inner join dbo.attribute atr on atr.id = ct.attributeid
					where not a.reference is null
						and ct.parentid = @nId
						and not ct.attributeid is null
						and a.entitytype = 1
					for xml path('')),1,1,'')
--print @cstr
	--select 22, @cStr
					execute (@cStr)
				end
--#endregion Создаем связи на макеты справочников

--#region Создаем связи на макеты источников
				if exists(select * from #cldpl a, #cldpl_tree ct
					where not a.reference is null and ct.parentid = @nId and a.id = ct.attributeid and not ct.attributeid is null and a.entitytype = 2)
				begin
					select @cStr = stuff((
					select ';alter table ' + @cSchemeName  + '.' + @cUserTableName + ' with check add constraint' +
						' fk_ds_' + cast(a.id as varchar(20)) + '_ds_' + cast(a.reference as varchar(20)) + '_' + cast(a.rhdid as varchar(20)) +
						' foreign key (' + atr.scolumn + ')' +
						' references ' + @cSchemeName  + '.'+ (select tablecolumn from dbo.dynamic_structure ds where ds.hdid = a.rhdid) + ' (id)'
						as [text()]
					from #cldpl a inner join #cldpl_tree ct on a.id = ct.attributeid
						inner join dbo.attribute atr on atr.id = ct.attributeid
					where not a.reference is null
						and ct.parentid = @nId
						and not ct.attributeid is null
						and a.entitytype = 2
					for xml path('')),1,1,'')
	--print @cstr
					execute (@cStr)
				end
--#endregion Создаем связи на макеты источников

				delete #cldpl_lev where id = @nId
			end

		--создание кода уникальных комбинаций
		delete dbo.entity_uniques where coalesce(isdeleted,0) > 0

		insert into dbo.dynamic_uniques(id, entityid, name, isenabled, matchcode, shareobjects)
		select eu.id, @nEntityId, eu.name, eu.isenabled,
		stuff((select ',' + cast(eui.attributeid as varchar(20)) from dbo.entityuniqueitem eui where eui.uniqueid = eu.id	for xml path('')),1,1,''),
		eu.shareobjects
		from dbo.entity_uniques eu
		where eu.entityid = @nEntityId
			and exists (select * from dbo.entityuniqueitem eui where eui.uniqueid = eu.id)

--#region Динамические структуры
		--добавляем описание динамической структуры в таблицу
		delete dbo.dynamic_structure where entityid = @nEntityId
		insert into dbo.dynamic_structure(entityid, name, hdid, levels, sequence, hdtype, attributeid, isrequired, isparentref, display, systemtype, attributetypeid, referenceid, parentid, comment, attrprotocolorder, saveeventjournal, deploytype, tablecolumn)
		select @nEntityId, at.name, tree.id, tree.levels, tree.sequence, tree.hdtype, tree.attributeid, at.isrequired, at.isparentref, at.display, at.systemtype, at.attributetype, ref.id, tree.parentid, at.comment, at.protocolorder, at.saveeventjournal, e.deploytype, (case when tree.attributeid is null then hd.stable else at.scolumn end)
		from #cldpl_tree tree left join dbo.attribute at on (tree.attributeid = at.id)
			left join dbo.entity ref on (at.reference = ref.id)
			left join dbo.entity e on e.id = @nEntityId
			left join dbo.hierarchydetail hd on hd.id = tree.id
		--Проставляем имена уровней
		update dbo.dynamic_structure set name = hd.name
		from dbo.hierarchydetail hd, dbo.dynamic_structure ds where ds.entityid = @nEntityId and hd.id = ds.hdid and ds.name is null

		--Проставляем типы ссылок-показателей
		update dbo.dynamic_structure set attributetypeid = e.attributetype
		from dbo.entity e, dbo.dynamic_structure ds where ds.entityid = @nEntityId and ds.referenceid = e.id and e.entitytype = 5
--#endregion Динамические структуры

	end try
	begin catch
		select @error = error_number(), @message = error_message()
		raiserror ('CL_CREATE: %d: %s', 16, 1, @error, @message)
		return
	end catch
end
GO

exec dbo.gp
GO

if exists (select * from sys.objects where object_id = object_id(N'dbo.cl_deploy') and type in (N'P', N'PC'))
	drop procedure dbo.cl_deploy
GO


-- execute dbo.CL_DEPLOY @nEntityId = 957
create procedure dbo.cl_deploy
	@nEntityId int,
	@ProtRID uniqueidentifier = null
as
begin
	set nocount on
	declare @cRepHeader varchar(1500), @DataXml varchar(max), @trancount int, @nId int, @nPermissioned int, @cPrefixClone varchar(10)

	create table #message (resolution varchar(100), error_msg varchar(4000))
	create table #entityobjects(entityid int)
	create table #cntrl (refid int, reference varchar(100), exist int, modystru int, name varchar(1000), tblreference varchar(128))

--#region Контроли
	if exists(select * from dbo.entityindicator ei where ei.id = @nEntityId and coalesce(ei.chdstruct, 0) > 0 and ei.chstruct = ei.chdstruct)
		begin
			return
		end

	if exists(select * from dbo.entity where id = @nEntityId and deploytype = 1)
	begin
		execute dbo.cl_simple_deploy @nEntityId = @nEntityId
		execute dbo.create_sys_envents_triggers @nEntityId = @nEntityId

		update ei set chdstruct = ei.chstruct, chgmstructstr = null, allowpermission = e.allowpermission, statused = e.statused, name = e.name
		from dbo.entityindicator ei inner join dbo.entity e on ei.id = e.id
		where ei.id = @nEntityId
		return
	end

	if not exists(select * from dbo.entity_tree(@nEntityId))
		begin
--#region протокол ошибок и контролей
			if @ProtRID is not null
				begin
					select @cRepHeader = 'Протокол генерации макета ' + dbo.EntityTypeName(@nEntityId, 2) + ' "' + (select name from dbo.entity where id = @nEntityId) + '"'
					execute dbo.XMLHeaders @Header = @cRepHeader, @ProtRID = @ProtRID
					insert into #message (resolution, error_msg) values('Обратитесь к администратору комплекса.', 'Не создана логическая структура справочника/источника.')
					execute dbo.XMLDescColumns @Header = '<SPAN ID=WARN>Ошибка генерации CL_DEPLOY</SPAN>', @Columns = 'resolution,error_msg', @ColumnHeaders = 'Решение,Ошибка', @ProtRID = @ProtRID
					select @DataXml = (select resolution, error_msg from #message for xml raw, root('data'))
					execute dbo.XMLValColumns @DataXml = @DataXml, @ProtRID = @ProtRID
				end
--#endregion протокол ошибок и контролей
			return
		end

	insert into #cntrl (refid, reference, exist, modystru, tblreference)
	select distinct stru.refid, stru.reference, 0, 0, stru.tbl_reference
	from dbo.EntityStru(@nEntityId) stru
	where stru.reference is not null and stru.refid <> @nEntityId
		and not exists (select * from dbo.dynamic_structure ds where ds.entityid = stru.refid and ds.attributeid is null and ds.parentid is null and ds.deploytype = 1)

	if exists (select * from #cntrl)
		begin
			update cn set modystru = 1
			from #cntrl cn inner join dbo.entityindicator ei on ei.id = cn.refid
			where coalesce(ei.chdstruct,0) = 0 or coalesce(ei.chstruct,0) <> coalesce(ei.chdstruct,0)

			update cn set exist = 1	from #cntrl cn where dbo.GetRowCount('ks_ddlcontrol', cn.tblreference) is not null

			delete cn from #cntrl cn where exist = 1 and modystru = 0
		end

	if exists (select * from #cntrl)
		begin
--#region протокол ошибок и контролей
			if @ProtRID is not null
				begin
					update cn set name = (select e.name from dbo.entity e where e.id = cn.refid) from #cntrl cn

					select @cRepHeader = 'Протокол генерации макета ' + dbo.EntityTypeName(@nEntityId, 2) + ' "' + (select name from dbo.entity where id = @nEntityId) + '"'
					execute dbo.XMLHeaders @Header = @cRepHeader, @ProtRID = @ProtRID
					insert into #message (resolution, error_msg) select 'Обратитесь к администратору комплекса.', name from #cntrl where refid = (select min(refid) from #cntrl)
					insert into #message (resolution, error_msg) select '', name from #cntrl where refid > (select min(refid) from #cntrl)
					execute dbo.XMLDescColumns @Header = '<SPAN ID=WARN>Структура следующих макетов справочников/источников не сгенерирована или устарела</SPAN>', @Columns = 'resolution,error_msg', @ColumnHeaders = 'Решение,Ошибка', @ProtRID = @ProtRID
					select @DataXml = (select resolution, error_msg from #message for xml raw, root('data'))
					execute dbo.XMLValColumns @DataXml = @DataXml, @ProtRID = @ProtRID
				end
--#endregion протокол ошибок и контролей
			return
		end
--#endregion Контроли

	select @cPrefixClone = 'ccd_'
	set @trancount = @@trancount

	if @trancount = 0
		begin transaction

	begin try

		if exists (select * from dbo.entityindicator ei where ei.id = @nEntityId and coalesce(ei.chdstruct, 0) > 0 and ei.chstruct <> ei.chdstruct)
			begin

--#region удаление таблиц клонов для контроля
			if exists(
								select *
								from dbo.entityobjectcontrols eoc, dbo.entityobjects eo, dbo.entity e
								where eoc.entityobjectid = eo.id and eo.entityid = e.id and e.id = @nEntityId
								)
				begin
					execute dbo.ds_clone_del @nEntityId = @nEntityId, @cPrefixClone = @cPrefixClone
				end
--#endregion удаление таблиц клонов для контроля

				execute dbo.cl_modify @nEntityId = @nEntityId
			end
		else
			begin
				execute dbo.cl_create @nEntityId = @nEntityId
				update ei set chstruct = coalesce(ei.chstruct, 0) + 1 from dbo.entityindicator ei where ei.id = @nEntityId
			end

		update ei set chdstruct = ei.chstruct, chgmstructstr = null, allowpermission = e.allowpermission, statused = e.statused, name = e.name, eds = e.eds
		from dbo.entityindicator ei inner join dbo.entity e on ei.id = e.id
		where ei.id = @nEntityId

		-- Добавление констрэйнтов на атрибуты с типом дата. Выполнять после изменения dbo.DYNAMIC_STRUCTURE
		execute dbo.dt_constraints @nMode = 1, @nEntityId = @nEntityId

--#region заполнение иерархий

		if dbo.EntityTypeId(@nEntityId) = 1
			begin
				execute dbo.cl_dynamichierarchy_fill @nEntityId = @nEntityId
				execute dbo.clh_build @nEntityId = @nEntityId
			end
--#endregion заполнение иерархий

--#region Построение функций протокола
		execute dbo.entityfuncprotocol @nEntityId = @nEntityId

		insert into #entityobjects (entityid)
		select eo.id
		from dbo.entity e inner join dbo.entityobjects eo on e.id = eo.entityid
		where e.id = @nEntityId and e.entitytype in (1,2)

		while exists (select * from #entityobjects)
		 begin
			select top (1) @nId = entityid from #entityobjects order by entityid

			execute dbo.EntityObjectFuncProtocol @nEntityObjectId = @nId

			delete #entityobjects where @nId = entityid
		 end
--#endregion Построение функций протокола

--#region Построение таблиц для хранения прав
		select @nPermissioned = coalesce(allowpermission, 0) from dbo.entity where id = @nEntityId
		execute dbo.cl_set_permissioned @nEntityId = @nEntityId, @nPermissioned = @nPermissioned
--#endregion Построение таблиц для хранения прав

		-- Построение триггеров уникальности, логирования, иерархий, статусов, процедур удаления
		execute dbo.cl_unique_trg @nEntityId = @nEntityId, @ProtRID = @ProtRID
		execute dbo.create_sys_envents_triggers @nEntityId = @nEntityId
		execute dbo.cl_status_trg @nEntityId = @nEntityId
		execute dbo.admin_check_stamp @cMode = 'upd_stru'
		execute dbo.admin_create_v_prim_for @nMode = 0

--#region создание таблиц клонов для контроля
		if exists(
							select *
							from dbo.entityobjectcontrols eoc, dbo.entityobjects eo, dbo.entity e
							where eoc.entityobjectid = eo.id and eo.entityid = e.id and e.id = @nEntityId
							)
			begin
				execute dbo.ds_clone @nEntityId = @nEntityId, @cPrefixClone = @cPrefixClone
			end
--#endregion создание таблиц клонов для контроля

		-- триггера для эталонной базы
		if dbo.AvailableRegList() = 1
			execute dbo.cl_reglist_trg @nEntityId = @nEntityId

		if @@trancount > 0 and @trancount = 0
			commit transaction
	end try
	begin catch
		if @@trancount > 0 and @trancount = 0
			rollback transaction

--#region протокол ошибок и контролей
		if @ProtRID is not null
			begin
				select @cRepHeader = 'Протокол генерации макета ' + dbo.EntityTypeName(@nEntityId, 2) + ' "' + (select name from dbo.entity where id = @nEntityId) + '"'
				execute dbo.XMLHeaders @Header = @cRepHeader, @ProtRID = @ProtRID
				insert into #message (resolution, error_msg) values('Обратитесь к администратору комплекса.', coalesce(error_procedure() + ' ', '') + error_message())
				execute dbo.XMLDescColumns @Header = '<SPAN ID=WARN>Ошибка в процедуре генерации</SPAN>', @Columns = 'resolution,error_msg', @ColumnHeaders = 'Решение,Ошибка', @ProtRID = @ProtRID
				select @DataXml = (select resolution, error_msg from #message for xml raw, root('data'))
				execute dbo.XMLValColumns @DataXml = @DataXml, @ProtRID = @ProtRID
			end
--#endregion протокол ошибок и контролей

		return
	end catch
end
GO


exec dbo.gp
GO

if exists (select * from sys.objects where object_id = object_id(N'dbo.cl_modify') and type in (N'P', N'PC'))
	drop procedure dbo.cl_modify
GO

create procedure dbo.cl_modify
	@nEntityId int,
	@nPrint int = 0
as
begin
	set nocount on
	declare @cStr varchar(max), @Id int, @error int, @message varchar(4000), @nDeployType int, @cc varchar(10),
		@tablename_d varchar(128), @tablename_c varchar(128)

	begin try

	create table #tab_designer (hdid int, levels int, hdtype int, attributeid int, parentid int, tree varchar(1000), tablename varchar(128), columname varchar(128), columntype varchar(30), columntypecode varchar(30), refid int, ref_table varchar(128))
	create table #tab_current (hdid int, levels int, hdtype int, attributeid int, parentid int, tree varchar(1000), tablename varchar(128), columname varchar(128), columntype varchar(30), columntypecode varchar(30), refid int, ref_table varchar(128))

	create table #tree_designer (id int, table_path varchar(1000), table_comp varchar(1000))
	create table #tree_current (id int identity(1,1), table_path varchar(1000), table_comp varchar(1000))

	create table #entitypach (ord int identity(1,1), id int, parent int)
	create table #table_param (id int not null)

	create table #tab_query (orders int identity(1,1), tablename_d varchar(128), columname_d varchar(128), tablename_c varchar(128), columname_c varchar(128))

	select @nDeployType = deploytype from dbo.entity where id = @nEntityId
	select @cc = char(13)+char(10)

--#region Динамические структуры
	insert into #tab_current (hdid, levels, hdtype, attributeid, parentid, tablename, columname, columntype, columntypecode, refid, ref_table)
	select ds.hdid, ds.levels, ds.hdtype, ds.attributeid, ds.parentid,
		case when hdtype > 0 then ds.tablecolumn else null end,
		case when hdtype = 0 then ds.tablecolumn else null end,
		dbo.atributetype(ds.attributetypeid),
		(select dt.code from dbo.attributetype att, dbo.datatype dt where att.id = ds.attributetypeid and dt.id = att.datatype),
		ds.referenceid, dbo.entitytbltop(ds.referenceid)
	from dbo.dynamic_structure ds
	where ds.entityid = @nEntityId

	update tс set tablename = (select tс1.tablename from #tab_current tс1 where tс1.hdid = tс.parentid)
	from #tab_current tс
	where tс.tablename is null and tс.attributeid is not null

	insert into #table_param (id)
	select tc.hdid from #tab_current tc where tc.hdtype > 0 and not exists (select * from #tab_current tc1 where tc1.parentid = tc.hdid and tc1.hdtype > 0)

	while exists (select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			insert #tree_current (table_path, table_comp)
			select
				(select ',' + cast(hdid as varchar(20)) from dbo.GetEntityTblTree(@nEntityId, @Id) order by levels for xml path('')),
				(select ',' + cast(hdid as varchar(20)) from dbo.GetEntityTblTree(@nEntityId, @Id) order by hdid for xml path(''))

			delete #table_param where @Id = id
		end

	update tc set tree = (select ',' + cast(trc.id as varchar(20)) from #tree_current trc where patindex('%,' + cast(tc.hdid as varchar(20)) + '%', trc.table_path) > 0 order by trc.id for xml path(''))
	from #tab_current tc
	where tc.hdtype > 0

	update tc set tree = (select ',' + cast(trc.id as varchar(20)) from #tree_current trc where patindex('%,' + cast(tc.parentid as varchar(20)) + '%', trc.table_path) > 0 order by trc.id for xml path(''))
	from #tab_current tc
	where tc.hdtype = 0
--#endregion Динамические структуры

--#region Конструктор
	insert into #tab_designer (hdid, levels, hdtype, attributeid, parentid)
	select id, levels, hdtype, attributeid, parentid from dbo.entity_tree(@nEntityId)

	update td set columname = at.scolumn, columntype = dbo.atributestru(at.id, 2)
		from #tab_designer td inner join dbo.attribute at on td.attributeid = at.id
	where td.attributeid is not null

	update td set columntypecode = dbo.regexreplace(td.columntype,'[0-9,()]','')
	from #tab_designer td
	where td.attributeid is not null and td.columntypecode is null

	update td set columntypecode = dtp.code
	from #tab_designer td, dbo.attribute at, dbo.attributetype atp, dbo.datatype dtp
	where at.id = td.attributeid and at.attributetype = atp.id and atp.datatype = dtp.id and td.attributeid is not null and td.columntypecode = 'int';

	update td set tablename = (select hd.stable from dbo.hierarchydetail hd where hd.id = td.parentid)
	from #tab_designer td
	where td.attributeid is not null and td.hdtype = 0

	update td set tablename = (select hd.stable from dbo.hierarchydetail hd where hd.id = td.hdid)
	from #tab_designer td
	where td.hdtype > 0

	update td set refid = at.reference, ref_table = dbo.entitytbltop(at.reference)
	from #tab_designer td, dbo.attribute at
	where td.attributeid = at.id and td.hdtype = 0 and at.reference is not null

	-- находим листья в дереве
	insert into #table_param (id)
	select td.hdid from #tab_designer td where td.hdtype > 0 and not exists (select * from #tab_designer td1 where td1.parentid = td.hdid and td1.hdtype > 0)

	while exists (select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			insert into #entitypach (id, parent) select td.hdid, td.parentid from #tab_designer td where td.hdid = @Id
			while @@rowcount > 0
				begin
					insert into #entitypach (id, parent)
					select td.hdid, td.parentid
					from #tab_designer td inner join #entitypach ep on ep.parent = td.hdid
					where not exists (select * from #entitypach a where a.id = td.hdid)
				end

			insert #tree_designer (table_path, table_comp)
			select
				(select ',' + cast(id as varchar(20)) from #entitypach order by ord desc for xml path('')),
				(select ',' + cast(id as varchar(20)) from #entitypach order by id for xml path(''))

			update trd set id = trc.id
			from #tree_designer trd, #tree_current trc
			where trd.table_path = trc.table_path or trd.table_comp = trc.table_comp

			delete #entitypach
			delete #table_param where @Id = id
		end

	select @Id = max(id) from #tree_current trc
	update trd set @Id = id = @Id + 1 from #tree_designer trd where trd.id is null

	update td set tree = (select ',' + cast(trd.id as varchar(20)) from #tree_designer trd where patindex('%,' + cast(td.hdid as varchar(20)) + '%', trd.table_path) > 0 order by trd.id for xml path(''))
	from #tab_designer td
	where td.hdtype > 0

	update td set tree = (select ',' + cast(trd.id as varchar(20)) from #tree_designer trd where patindex('%,' + cast(td.parentid as varchar(20)) + '%', trd.table_path) > 0 order by trd.id for xml path(''))
	from #tab_designer td
	where td.hdtype = 0

	update #tab_designer set ref_table = replace(ref_table, 'ks_ddlcontrol.', '')
--#endregion Конструктор

--#region Селекты
--select * from #tree_current
--select * from #tree_designer
--select * from #tab_current order by levels, hdtype desc, attributeid, tablename
--select * from #tab_designer order by levels, hdtype desc, attributeid, tablename
--select tc.*, td.* from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid
--select tc.*, td.* from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid where td.hdid = tc.hdid and td.hdtype = 0 and td.parentid <> tc.parentid
--return
--#endregion Селекты

-- Удаление констрэйнтов на атрибуты с типом дата. Выполнять до изменения dbo.DYNAMIC_STRUCTURE
	if exists (select *
							from dbo.dynamic_structure ds
										inner join dbo.attributetype at on at.id = ds.attributetypeid
										inner join dbo.datatype dt on dt.id = at.datatype
							where dt.code = 'date' and ds.entityid = @nEntityId)
		begin
			execute dbo.dt_constraints @nMode = 0, @nEntityId = @nEntityId
		end

--#region Отключаем триггера
	select @cStr = (select distinct '
	if object_id(''ks_ddlcontrol.' + tc.tablename + ''') is not null alter table ks_ddlcontrol.' + tc.tablename + ' disable trigger all'
	from #tab_current tc
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion Отключаем триггера

--#region Удаление атрибутов
	select @cStr = (select ';' + dbo.object_drop_script(tc.tablename, 'column', tc.columname, null)
	from #tab_current tc
	where tc.hdtype = 0 and not exists (select * from #tab_designer td where td.hdid = tc.hdid and td.hdtype = 0)
	for xml path(''))
if @nPrint = 1 print @cStr
	execute (@cStr)

	insert into #table_param (id)
	select tc.attributeid
	from #tab_current tc
	where tc.hdtype = 0 and not exists (select * from #tab_designer td where td.hdid = tc.hdid and td.hdtype = 0)

	while(exists(select * from #table_param))
		begin
			select top (1) @Id = id from #table_param order by id

			--Удаление ссылок ENTITYVIEWDETAIL.ATTRIBUTEID
			execute dbo.delete_viewdetails_attribute @nAttributeId = @Id
			execute dbo.delete_oparams_attribute @nAttributeId = @Id

			-- Удаление атрибута из комбинаций уникальностей
			execute dbo.delete_dynamic_uniques @nEntityId = @nEntityId, @nAttributeId = @Id
			-- Удаление уникальности содержащей удаляемый атрибут
			execute dbo.cl_unique_del @nEntityId = @nEntityId, @nAttributeId = @Id

--select * from filters where attributeid = 38970

			delete #table_param where id = @Id
		end
--#endregion Удаление атрибутов

--#region Удаление уровней
	insert into #table_param (id)
	select tc.hdid
	from #tab_current tc
	where tc.hdtype > 0 and not exists (select * from #tab_designer td where td.hdid = tc.hdid and td.hdtype > 0)

	while exists(select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			select @cStr = (select '
			create table #tmp_del (id int); insert #tmp_del (id) select id from ks_ddlcontrol.' + tc.tablename + '; exec dbo.del_step ''' + tc.tablename + ''', ''id'', ''#tmp_del'''
			from #tab_current tc
			where tc.hdid = @Id)
if @nPrint = 1 print @cStr
			execute (@cStr)

			-- Удаляем связь на таблицу нижнего уровня
			select @cStr = @cc + (select '
			if exists (select * from sys.foreign_keys where object_id = object_id(N''KS_DDLControl.' + sfk.name + ''') and parent_object_id = object_id(N''KS_DDLControl.' + vpf.f_tab + '''))
			alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
			from #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
			where tc.hdid = @Id and tc.tablename = vpf.r_tab and vpf.constid = sfk.object_id
			for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
			execute (@cStr)

			-- Удаляем связь на таблицу верхнего уровня
			select @cStr = @cc + (select '
			if exists (select * from sys.foreign_keys where object_id = object_id(N''KS_DDLControl.' + sfk.name + ''') and parent_object_id = object_id(N''KS_DDLControl.' + vpf.f_tab + '''))
			alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
			from #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
			where tc.hdid = @Id and tc.tablename = vpf.f_tab and vpf.constid = sfk.object_id
			for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = @cc + (select ';' + dbo.object_drop_script(tc.tablename, 'u', null, null)
			from #tab_current tc
			where tc.hdtype > 0 and tc.hdid = @Id
			for xml path(''))
if @nPrint = 1 print @cStr
			execute (@cStr)

			--Удаление ссылок ENTITYVIEWDETAIL.ATTRIBUTEID
			execute dbo.delete_viewdetails_level @nLevelId = @Id
			execute dbo.delete_oparams_level @nLevelId = @Id

			delete #table_param where id = @Id
		end
--#endregion Удаление уровней

--#region Переименование уровней
	select @cStr = (select '
	exec sp_rename @objname = ''ks_ddlcontrol.' + tc.tablename + ''', @newname = ''' + td.tablename + ''''
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.tablename <> tc.tablename and td.hdtype > 0
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion Переименование уровней

--#region Создание новых уровней
	select @cStr = (select '; create table ks_ddlcontrol.' + td.tablename + ' (id int identity(1,1) not null, id_up int, guid uniqueidentifier not null default (newid()), unit uniqueidentifier, timestamp timestamp, eodguid uniqueidentifier, constraint [pk_' + dbo.EntityPrefix(@nEntityId) + cast(@nEntityId as varchar(20)) + '_' + cast(td.hdid as varchar(20)) + '] primary key clustered (id asc))'
	from #tab_designer td
	where td.hdtype > 0 and not exists (select * from #tab_current tc where td.hdid = tc.hdid and tc.hdtype > 0)
	for xml path(''))
if @nPrint = 1 print @cStr
	execute (@cStr)

	-- Создаем связь на таблицу верхнего уровня
	select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' with check add constraint fk_' + dbo.EntityPrefix(@nEntityId) + cast(@nEntityId as varchar(20)) + '_' + cast(td.hdid as varchar(20)) + '_' + dbo.EntityPrefix(@nEntityId) + cast(@nEntityId as varchar(20)) + '_' + cast(td.parentid as varchar(20)) + ' foreign key (id_up) references ks_ddlcontrol.' + (select td1.tablename from #tab_designer td1 where td1.hdid = td.parentid) + ' (id) on update no action on delete no action'
	from #tab_designer td
	where td.hdtype > 0 and not exists (select * from #tab_current tc where td.hdid = tc.hdid and tc.hdtype > 0)
	for xml path(''))
if @nPrint = 1 print @cStr
	execute (@cStr)

----index ID_UP include (ID, TIMESTAMP)
	select @cStr = (select '; create index ix_' + td.tablename + '_id_up on ks_ddlcontrol.' + td.tablename + ' (id_up) include (id, timestamp)'
	from #tab_designer td
	where td.hdtype > 0 and not exists (select * from #tab_current tc where td.hdid = tc.hdid and tc.hdtype > 0)
	for xml path(''))
if @nPrint = 1 print @cStr
	execute (@cStr)

----#endregion Создание новых уровней

--#region Создание новых атрибутов
	select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' add ' + dbo.atributestru(td.attributeid, 3) + ' null'
	from #tab_designer td
	where td.hdtype = 0 and not exists (select * from #tab_current tc where td.hdid = tc.hdid and tc.hdtype = 0)
	for xml path(''))
if @nPrint = 1 print @cStr
	execute (@cStr)


	-- у аттрибута ссылка на родителя
	select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' with check add constraint fk_' + dbo.entityprefix(td.refid) + cast(td.attributeid as varchar(20)) + '_' + td.ref_table + ' foreign key (' + td.columname + ') references ks_ddlcontrol.' + td.ref_table + ' (id)'
	from #tab_designer td
	where td.hdtype = 0 and not exists (select * from #tab_current tc where td.hdid = tc.hdid and tc.hdtype = 0)
		and td.refid is not null
	for xml path(''))

if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion Создание новых атрибутов

--#region Переименование атрибутов
	select @cStr = (select '
	exec sp_rename @objname = ''ks_ddlcontrol.' + td.tablename + '.' + tc.columname + ''', @newname = ''' + td.columname + ''', @objtype = ''column'''
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname <> tc.columname and td.hdtype = 0
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion Переименование атрибутов

--#region Перенос уровней
	-- Удаляем связь на таблицу верхнего уровня
	select @cStr = @cc + (select '
	if exists (select * from sys.foreign_keys where object_id = object_id(N''KS_DDLControl.' + sfk.name + ''') and parent_object_id = object_id(N''KS_DDLControl.' + vpf.f_tab + '''))
	alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
	from #tab_designer td, #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
	where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid
		and tc.tablename = vpf.f_tab and vpf.constid = sfk.object_id
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	-- Удаляем связь на таблицу нижнего уровня
	select @cStr = @cc + (select '
	if exists (select * from sys.foreign_keys where object_id = object_id(N''KS_DDLControl.' + sfk.name + ''') and parent_object_id = object_id(N''KS_DDLControl.' + vpf.f_tab + '''))
	alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
	from #tab_designer td, #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
	where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid
		and tc.tablename = vpf.r_tab and vpf.constid = sfk.object_id
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

--#region перемещение в другую ветку
	delete #table_param
	insert into #table_param (id)
	select td.hdid
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid and td.tree <> tc.tree
--select 111111, * from #table_param
	while exists(select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id
			select @cStr = (select 'create table #tmp_del (id int); insert #tmp_del (id) select id from ks_ddlcontrol.' + td.tablename + '; exec dbo.del_step ''' + td.tablename + ''', ''id'', ''#tmp_del''' from #tab_designer td where td.hdid = @Id)
if @nPrint = 1 print @cStr
			execute (@cStr)

			-- Удаляем связь на таблицу нижнего уровня
			select @cStr = (select '; alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
			from #tab_designer td, #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
			where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid and td.tree <> tc.tree
				and tc.tablename = vpf.r_tab and vpf.constid = sfk.object_id
			for xml path(''))
if @nPrint = 1 print @cStr
			execute (@cStr)

			delete #table_param where id = @Id
		end
--#endregion перемещение в другую ветку

	-- Создаем связь на таблицу верхнего уровня
	select @cStr = @cc + (select '
	delete A from ks_ddlcontrol.' + td.tablename + ' A where not exists (select * from ks_ddlcontrol.' + (select td1.tablename from #tab_designer td1 where td1.hdid = td.parentid) + ' B where A.id_up = B.id)
	alter table ks_ddlcontrol.' + td.tablename + ' with check add constraint fk_' + dbo.EntityPrefix(@nEntityId) + cast(@nEntityId as varchar(20)) + '_' + cast(td.hdid as varchar(20)) + '_' + dbo.EntityPrefix(@nEntityId) + cast(@nEntityId as varchar(20)) + '_' + cast(td.parentid as varchar(20)) + ' foreign key (id_up) references ks_ddlcontrol.' + (select td1.tablename from #tab_designer td1 where td1.hdid = td.parentid) + ' (id) on update no action on delete no action'
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.hdid = tc.hdid and td.hdtype > 0 and td.parentid <> tc.parentid
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

--#endregion Перенос уровней

--#region Перенос колонок между уровнями

--#region перенос колонок в одной ветке
	insert into #table_param (id)
	select td.attributeid
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree = tc.tree

	while exists(select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			select @cStr = (select 'alter table ks_ddlcontrol.' + td.tablename + ' add ' + td.columname + ' ' + td.columntype + ' null'
			from #tab_designer td
			where td.attributeid = @Id and td.hdtype = 0)
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = @cc + (select 'alter table ks_ddlcontrol.' + vpf.f_tab + ' drop constraint ' + sfk.name
			from #tab_current tc, dbo.v_prim_for vpf, sys.foreign_keys sfk
			where tc.attributeid = @Id and tc.tablename = vpf.f_tab and tc.columname = vpf.f_col and vpf.constid = sfk.object_id)
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' with check add constraint fk_' + dbo.entityprefix(td.refid) + cast(td.attributeid as varchar(20)) + '_' + td.ref_table + ' foreign key (' + td.columname + ') references ks_ddlcontrol.' + td.ref_table + ' (id)'
			from #tab_designer td
			where td.attributeid = @Id and td.hdtype = 0 and td.refid is not null)
if @nPrint = 1 print @cStr
			execute (@cStr)

			if (select td.levels from #tab_designer td where td.attributeid = @Id and td.hdtype = 0) > (select tc.levels from #tab_current tc where tc.attributeid = @Id and tc.hdtype = 0)
				select @cStr = (select dbo.getentityselect_designer(@nEntityId, td.parentid, 1) from #tab_designer td where td.attributeid = @Id and td.hdtype = 0)
			else
				select @cStr = (select dbo.getentityselect_designer(@nEntityId, tc.parentid, 1) from #tab_current tc where tc.attributeid = @Id and tc.hdtype = 0)

	--select td.attributeid, td.ref_table, tc.ref_table
	--from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	--where td.attributeid = @Id and td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree = tc.tree

			select @cStr = (select replace(@cStr, 'select', 'update t' + cast(td.parentid as varchar(20)) + ' set ' + td.columname + ' =') from #tab_designer td where td.attributeid = @Id and td.hdtype = 0)
			select @cStr = (select replace(@cStr, '<fields>', 't' + cast(tc.parentid as varchar(20)) + '.' + tc.columname) from #tab_current tc	where tc.attributeid = @Id and tc.hdtype = 0)
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = (select 'alter table ks_ddlcontrol.' + tc.tablename + ' drop column ' + tc.columname
			from #tab_current tc
			where tc.attributeid = @Id and tc.hdtype = 0)
if @nPrint = 1 print @cStr
			execute (@cStr)

			delete #table_param where id = @Id
		end
--#endregion перенос колонок в одной ветке

--#region перенос колонок между ветками
	insert into #table_param (id)
	select td.attributeid
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree <> tc.tree

	while exists(select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			select @cStr = (select '
			if exists (select * from sys.foreign_keys where object_id = object_id(N''KS_DDLControl.fk_' + dbo.entityprefix(tс.refid) + cast(tс.attributeid as varchar(20)) + '_' + tс.ref_table + ''') and parent_object_id = object_id(N''KS_DDLControl.' + tс.tablename + '''))
			alter table ks_ddlcontrol.' + tс.tablename + ' drop constraint fk_' + dbo.entityprefix(tс.refid) + cast(tс.attributeid as varchar(20)) + '_' + tс.ref_table
			from #tab_current tс
			where tс.attributeid = @Id and tс.hdtype = 0 and tс.refid is not null)
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' add ' + td.columname + ' ' + td.columntype + ' null'
			from #tab_designer td
			where td.attributeid = @Id and td.hdtype = 0)
if @nPrint = 1 print @cStr
			execute (@cStr)

--#region проброс вниз по ветке
			select @cStr = (select ';update t2 set ' + td.columname + ' = t1.' + tc.columname + ' from ks_ddlcontrol.' + tc.tablename + ' t1, ks_ddlcontrol.' + td.tablename + ' t2 where t1.id = t2.id_up'
			from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
			where td.attributeid = @Id and td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree <> tc.tree
				and exists (select * from #tab_designer td1 where td.parentid = td1.hdid and td1.hdtype > 0 and td1.parentid = tc.parentid)
				and exists (select * from #tab_current tc where td.parentid = tc.hdid and tc.hdtype > 0))
if @nPrint = 1 print @cStr
			execute (@cStr)

		select @cStr = (select ';insert into ks_ddlcontrol.' + td.tablename + '(id_up, ' + td.columname + ')
															select t1.id, t1.' + tc.columname + ' from ks_ddlcontrol.' + tc.tablename + ' t1
															where t1.' + tc.columname + ' is not null and not exists (select * from ks_ddlcontrol.' + td.tablename + ' t2 where t1.id = t2.id_up)'
			from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
			where td.attributeid = @Id and td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree <> tc.tree
				and exists (select * from #tab_designer td1 where td.parentid = td1.hdid and td1.hdtype > 0 and td1.parentid = tc.parentid)
				and exists (select * from #tab_current tc where td.parentid = tc.hdid and tc.hdtype > 0))
if @nPrint = 1 print @cStr
			execute (@cStr)
--#endregion проброс вниз по ветке

			delete #table_param where id = @Id
		end

--#region Объединяем поля из одинаковых таблиц получателей и отправителей в один запрос
	insert into #tab_query (tablename_d, columname_d, tablename_c, columname_c)
	select td.tablename, td.columname, tc.tablename, tc.columname
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree <> tc.tree
		and exists (select * from #tab_designer td1 where td.parentid = td1.hdid and td1.hdtype > 0 and td1.parentid = tc.parentid)
		and not exists (select * from #tab_current tc1 where td.parentid = tc1.hdid and tc1.hdtype > 0)

	select @cStr = ''
	while exists (select * from #tab_query)
		begin
			select top 1 @tablename_d = tablename_d, @tablename_c = tablename_c from #tab_query

			select @cStr =  @cStr + ';insert into ks_ddlcontrol.' + @tablename_d + ' (id_up' + (select ', ' + columname_d from #tab_query where @tablename_d = tablename_d and @tablename_c = tablename_c order by orders for xml path('')) + ')
			select t1.id' + (select ', t1.' + columname_c from #tab_query where @tablename_d = tablename_d and @tablename_c = tablename_c order by orders for xml path('')) + ' from ks_ddlcontrol.' + @tablename_c + ' t1'

			delete #tab_query where @tablename_d = tablename_d and @tablename_c = tablename_c
		end
if @nPrint = 1 print @cStr
			execute (@cStr)
--#endregion Объединяем поля из одинаковых таблиц получателей и отправителей в один запрос

	insert into #table_param (id)
	select td.attributeid
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.attributeid = tc.attributeid and td.hdtype = 0 and td.parentid <> tc.parentid and td.tree <> tc.tree

	while exists(select * from #table_param)
		begin
			select top (1) @Id = id from #table_param order by id

			select @cStr = (select ';if object_id(''ks_ddlcontrol.' + tc.tablename + ''') is not null and col_length(''ks_ddlcontrol.' + tc.tablename + ''',''' + tc.columname + ''') is not null alter table ks_ddlcontrol.' + tc.tablename + ' drop column ' + tc.columname
			from #tab_current tc
			where tc.attributeid = @Id and tc.hdtype = 0)
if @nPrint = 1 print @cStr
			execute (@cStr)

			select @cStr = (select '; alter table ks_ddlcontrol.' + td.tablename + ' with check add constraint fk_' + dbo.entityprefix(td.refid) + cast(td.attributeid as varchar(20)) + '_' + td.ref_table + ' foreign key (' + td.columname + ') references ks_ddlcontrol.' + td.ref_table + ' (id)'
			from #tab_designer td
			where td.attributeid = @Id and td.hdtype = 0 and td.refid is not null)
if @nPrint = 1 print @cStr
			execute (@cStr)

			delete #table_param where id = @Id
		end

--#endregion перенос колонок между ветками

--#endregion Перенос колонок между уровнями

--#region Конвертация типа колонки
	-- только преобразование типов данных,
	-- т.к нельзя поменять ранее установленный тип связи(справочник, иcточник, глоб. показатель или обычный атрибут)
	select @cStr = (select '
	alter table ks_ddlcontrol.' + td.tablename + ' add ' + td.columname + '_temp ' + td.columntype + ' null'
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	select @cStr = (select '
	update ks_ddlcontrol.' + td.tablename + ' set ' + td.columname + '_temp = ' + dbo.convert_type(td.columname, tc.columntype, td.columntype, tc.columntypecode, td.columntypecode)
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	select @cStr = (select '
	alter table ks_ddlcontrol.' + td.tablename + ' drop column ' + td.columname
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	select @cStr = (select '
	exec sp_rename @objname = ''ks_ddlcontrol.' + td.tablename + '.' + td.columname + '_temp'', @newname = ''' + td.columname + ''', @objtype = ''column'''
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	-- Удаление уникальности содержащей изменяемый атрибут
	insert into #table_param (id)
	select td.attributeid
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype

	while(exists(select * from #table_param))
		begin
			select top (1) @Id = id from #table_param order by id
			execute dbo.cl_unique_del @nEntityId = @nEntityId, @nAttributeId = @Id
			delete #table_param where id = @Id

--#region фильтры
			select @cStr = stuff((select distinct ',' + convert(varchar(30), op.filterid)
														from dbo.dynamic_structure ds
															left join dbo.entityobjects eo on ds.entityid = eo.entityid
															left join dbo.objectparams op on op.entityobjectid = eo.id
														where ds.entityid = @nEntityId and op.attributeid = @Id
														for xml path('')),1,1,'')

				if coalesce(@cStr, '') <> ''
					begin
						set @cStr = 'update op set filterid = null
							from dbo.objectparams op, dbo.dynamic_structure ds
									left join dbo.entityobjects eo on ds.entityid = eo.entityid
							where ds.entityid = ' + cast(@nEntityId as varchar(20)) + ' and op.attributeid = ' + cast(@Id as varchar(20)) + ' and eo.id = op.entityobjectid and op.filterid is not null

							delete dbo.filtervalues where filterid in (' + @cStr + ')
							delete dbo.filters where id in (' + @cStr + ')'
if @nPrint = 1 print @cStr
							execute (@cStr)
					end
--#endregion фильтры
		end

--#region objectparams
	select @cStr = (select '
	update op set ' + dbo.convert_objectparams (1, tc.columntypecode, td.columntypecode, dbo.regexreplace(td.columntype,'[a-z;()]','')) + '
	from dbo.objectparams op, dbo.dynamic_structure ds left join dbo.entityobjects eo on ds.entityid = eo.entityid
	where ds.entityid = ' + cast(@nEntityId as varchar(20)) + ' and op.attributeid = ' + cast(td.attributeid as varchar(20)) + ' and eo.id = op.entityobjectid'
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)

	-- связанные параметры
	select @cStr = (select '
	update op set ' + dbo.convert_objectparams (2, tc.columntypecode, td.columntypecode, dbo.regexreplace(td.columntype,'[a-z;()]','')) + '
	from dbo.objectparams op, dbo.dynamic_structure ds left join dbo.entityobjects eo on ds.entityid = eo.entityid
	where ds.entityid <> ' + cast(@nEntityId as varchar(20)) + ' and op.attributeid = ' + cast(td.attributeid as varchar(20)) + ' and eo.id = op.entityobjectid'
	from #tab_designer td inner join #tab_current tc on td.hdid = tc.hdid
	where td.columname = tc.columname and td.hdtype = 0 and td.columntype <> tc.columntype
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion objectparams

--#endregion Конвертация типа колонки

--#region Обработка комбинаций
	delete dbo.entity_uniques where entityid = @nEntityId and coalesce(isdeleted, 0) > 0

	--удаление комбинаций без атрибутов
	delete euniq from dbo.entity_uniques euniq
	where euniq.entityid = @nEntityId and exists (select eu.id
								from dbo.entity_uniques eu left join dbo.entityuniqueitem eue on eu.id = eue.uniqueid
								where eu.entityid = @nEntityId and eue.id is null and eu.id = euniq.id)

	delete du
	from dbo.dynamic_uniques du
	where du.entityid = @nEntityId and not exists(select * from dbo.entity_uniques eu where eu.entityid = @nEntityId and eu.id = du.id)

	create table #e_u (id int, name varchar(50), isenabled int, matchcode	varchar(255), shareobjects int)

	insert into #e_u (id, name, isenabled, matchcode, shareobjects)
	select eu.id, eu.name, eu.isenabled,
			stuff((select ',' + cast(eui.attributeid as varchar(20)) from dbo.entityuniqueitem eui	where eui.uniqueid = eu.id order by attributeid	for xml path('')),1,1,'') as matchcode,
			eu.shareobjects
	from dbo.entity_uniques eu
	where eu.entityid = @nEntityId

	--удаляем повторяющиеся комбинации из справочника
	delete ou
	from dbo.object_uniques ou inner join dbo.entityobjects eo on ou.entityobjectid = eo.id
	where eo.entityid = @nEntityId
		and exists(select * from #e_u where matchcode = (stuff((select ',' + value as [text()] from dbo.StrSplit2(ou.matchcode,',') order by value for xml path('')),1,1,'')))

	--обновление кода уникальных комбинаций
	update du set name = eu.name, isenabled = eu.isenabled, matchcode = eu.matchcode, shareobjects = eu.shareobjects
	from dbo.dynamic_uniques du, #e_u eu
	where du.entityid = @nEntityId and du.id = eu.id

	insert into dbo.dynamic_uniques(id, entityid, name, isenabled, matchcode, shareobjects)
	select eu.id, @nEntityId, eu.name, eu.isenabled, eu.matchcode, eu.shareobjects
	from #e_u eu
	where not exists(select * from dbo.dynamic_uniques du where du.entityid = @nEntityId and du.id = eu.id) and coalesce(matchcode,'') <> ''

--#endregion Обработка комбинаций

	--важно! до изменения динамической структуры
	execute dbo.transference_oparams_attribute @nEntityId = @nEntityId
	execute dbo.transference_viewdetails_attribute @nEntityId = @nEntityId

--#region Изменение динамической структуры
		delete dbo.dynamic_structure where entityid = @nEntityId

		-- уровни
		insert into dbo.dynamic_structure (entityid, hdid, name, levels, sequence, hdtype, deploytype, tablecolumn, parentid)
		select @nEntityId, td.hdid, hd.name, td.levels, hd.sequence, td.hdtype, @nDeployType, td.tablename, td.parentid
		from #tab_designer td inner join dbo.hierarchydetail hd on td.hdid = hd.id
		where td.hdtype > 0

		-- обычные атрибуты
		insert into dbo.dynamic_structure (entityid, hdid, name, levels, sequence, hdtype, attributeid, isrequired, isparentref, display, systemtype, attributetypeid, referenceid, parentid, comment, attrprotocolorder, saveeventjournal, deploytype, tablecolumn)
		select @nEntityId, td.hdid, at.name, td.levels, hd.sequence, td.hdtype, td.attributeid, at.isrequired, at.isparentref, at.display, at.systemtype, at.attributetype, at.reference, td.parentid, at.comment, at.protocolorder, at.saveeventjournal, @nDeployType, td.columname
		from #tab_designer td
			inner join dbo.attribute at on td.attributeid = at.id
			inner join dbo.hierarchydetail hd on td.attributeid = hd.attributeid
		where at.entityid = @nEntityId and td.refid is null

		-- показатели
		insert into dbo.dynamic_structure (entityid, hdid, name, levels, sequence, hdtype, attributeid, isrequired, isparentref, display, systemtype, attributetypeid, referenceid, parentid, comment, attrprotocolorder, saveeventjournal, deploytype, tablecolumn)
		select @nEntityId, td.hdid, at.name, td.levels, hd.sequence, td.hdtype, td.attributeid, at.isrequired, at.isparentref, at.display, at.systemtype, (select e.attributetype from dbo.entity e where e.id = td.refid), at.reference, td.parentid, at.comment, at.protocolorder, at.saveeventjournal, @nDeployType, td.columname
		from #tab_designer td
			inner join dbo.attribute at on td.attributeid = at.id
			inner join dbo.hierarchydetail hd on td.attributeid = hd.attributeid
		where at.entityid = @nEntityId and td.refid is not null and td.ref_table is null

		-- ссылки
		insert into dbo.dynamic_structure (entityid, hdid, name, levels, sequence, hdtype, attributeid, isrequired, isparentref, display, systemtype, attributetypeid, referenceid, parentid, comment, attrprotocolorder, saveeventjournal, deploytype, tablecolumn)
		select @nEntityId, td.hdid, at.name, td.levels, hd.sequence, td.hdtype, td.attributeid, at.isrequired, at.isparentref, at.display, at.systemtype, (select top (1) at.id from dbo.attributetype at, dbo.datatype dt where dt.id = at.datatype and dt.code = 'int'), at.reference, td.parentid, at.comment, at.protocolorder, at.saveeventjournal, @nDeployType, td.columname
		from #tab_designer td
			inner join dbo.attribute at on td.attributeid = at.id
			inner join dbo.hierarchydetail hd on td.attributeid = hd.attributeid
		where at.entityid = @nEntityId and td.refid is not null and td.ref_table is not null
--#endregion Изменение динамической структуры

	-- Добавление констрэйнтов на атрибуты с типом дата. Выполнять до изменения dbo.DYNAMIC_STRUCTURE
	if exists (select *
							from dbo.dynamic_structure ds
										inner join dbo.attributetype at on at.id = ds.attributetypeid
										inner join dbo.datatype dt on dt.id = at.datatype
							where dt.code = 'date' and ds.entityid = @nEntityId)
		begin
			execute dbo.dt_constraints @nMode = 1, @nEntityId = @nEntityId
		end

--#region Влючаем триггера
	select @cStr = (select distinct '
	if object_id(''ks_ddlcontrol.' + tc.tablename + ''') is not null alter table ks_ddlcontrol.' + tc.tablename + ' enable trigger all'
	from #tab_current tc
	for xml path(''),type).value('.','varchar(max)')
if @nPrint = 1 print @cStr
	execute (@cStr)
--#endregion Влючаем триггера

	end try
	begin catch
		select @error = error_number(), @message = error_message()
		raiserror ('cl_modify1: %d: %s', 16, 1, @error, @message)
		return
	end catch
end
GO


exec dbo.gp
GO

--2019-05-30 10:42:25
-- Установка значения
exec dbo.neo_set_val @CodeObject='SUBBUTTON_STATUS_NSI_LOG', @CodeProperty='t_Title', @Value='История статусов синхронизации'
GO

if  exists (select * from sys.objects where object_id = object_id(N'dbo.events_read_dwh_status_detail') and type in (N'P', N'PC'))
	drop procedure dbo.events_read_dwh_status_detail
GO

create procedure dbo.events_read_dwh_status_detail
		@RID uniqueidentifier = null
as
begin
	set nocount on

	execute dbo.XMLHeader @Header = 'Детализация', @Result = 0

	execute dbo.XMLColumns
		@Header = 'Информация о событиях',
		@Columns = 'date,loginame,host,caction,entity_name,eo_name,id_el,status,note,content',
		@ColumnHeaders = 'Дата,Пользователь,Клиент,Событие,Макет,Документ,Идентификатор документа,Статус,Примечание,Файл'

	select
		res.[date],
		sa.name as caction,
		res.loginame,
		res.host,
		e.name as entity_name,
		res.[note] as [note],
		eo.name as eo_name,
		res.id_row as id_el,
		res.status_name as [status],
		replace(res.content, char(13)+char(10), '<br>') as content
	from dbo.sys_events_status res, dbo.s_action sa, dbo.entity e, dbo.entityobjects eo, dbo.syslink(@RID) sl
	where sa.link = res.action and res.entityid = e.id and res.entityobjectid = eo.id and sl.link = res.link
end
GO

exec dbo.gp
GO

exec dbo.xp_rts
GO


if  exists (select * from sys.objects where object_id = object_id(N'dbo.cl_dynamichierarchy_fill') and type in (N'P', N'PC'))
	drop procedure dbo.cl_dynamichierarchy_fill
GO

/*
Ќазначение: ѕроцедура перестроени¤ таблиц иерахий справочника по иерархии макета
¬ходные параметры:
	@nEntityId int, - ID сущности

 од возврата: нет
ѕример вызова:
  execute dbo.dbo.cl_dynamichierarchy_fill @nEntityId = 371

јвтор: festdeim 02.06.2015
*/
create procedure dbo.cl_dynamichierarchy_fill
	@nEntityId int = 0
as
begin
	set nocount on

	begin transaction
	begin try

		create table #extHier(id int)
	
		if object_id('tempdb..#tabledelete') is not null
			drop table #tabledelete
		create table #tabledelete(id int)
		
		insert into #tabledelete(id) 
		select dehd.Id
		from dbo.dynamic_entity_hierarchy deh inner join dbo.dynamic_entity_hierarchydetail dehd on deh.id = dehd.dehierarchyid
		where deh.entityid = @nEntityId and deh.entityobjectid is null
			and not exists (select * from dbo.entity_hierarchy eh where eh.entityid = @nEntityId and eh.id = deh.ehierarchyid)

		exec dbo.del_step @prim_table = 'dbo.dynamic_entity_hierarchydetail', @prim_col = 'id', @prim_val_tab = '#tabledelete'

--#region выравниваем иерархии
		-- удаляем мертвые иерархии
		delete from #tabledelete
		
		insert into #tabledelete(id) 
		select deh.Id
		from dbo.dynamic_entity_hierarchy deh
		where deh.entityid = @nEntityId and deh.entityobjectid is null
			and not exists (select * from dbo.entity_hierarchy eh where eh.entityid = @nEntityId and eh.id = deh.ehierarchyid)
				
		exec dbo.del_step @prim_table = 'dbo.dynamic_entity_hierarchy', @prim_col = 'id', @prim_val_tab = '#tabledelete'							
						
		-- обновляем иерархии
		update deh set name = eh.name, hierarchytype = eh.hierarchytype, attributeid = eh.attributeid,
					   attributeadditionalid = eh.attributeadditionalid,
					   buildafteredit_element = eh.buildafteredit_element,
					   isnewschema = case when eh.attributeadditionalid > 0  then 1 else  eh.isnewschema end
		from dbo.dynamic_entity_hierarchy deh
			 inner join dbo.entity_hierarchy eh on eh.entityid = deh.entityid and eh.id = deh.ehierarchyid
		where deh.entityid = @nEntityId and deh.entityobjectid is null

		-- добавл¤ем новые иерархии
		insert into dbo.dynamic_entity_hierarchy (name, entityid, hierarchytype, attributeid, attributeadditionalid, isnewschema,
												  ehierarchyid, buildafteredit_element)
		select eh.name, eh.entityid, eh.hierarchytype, eh.attributeid,eh.attributeadditionalid, 1,
												   eh.id, eh.buildafteredit_element
		from dbo.entity_hierarchy eh
		where eh.entityid = @nEntityId
			and not exists (select * from dbo.dynamic_entity_hierarchy deh where deh.entityid = @nEntityId and deh.entityobjectid is null and eh.id = deh.ehierarchyid)

--#endregion выравниваем иерархии

--#region выравниваем уровни
		-- удаляем мертвые уровни иерархии
		delete from #tabledelete
		
		insert into #tabledelete(id) 		
		select dehd.Id
		from dbo.dynamic_entity_hierarchy deh inner join dbo.dynamic_entity_hierarchydetail dehd on deh.id = dehd.dehierarchyid
		where deh.entityid = @nEntityId and deh.entityobjectid is null and
			not exists (select *
						from dbo.entity_hierarchy eh
							inner join dbo.entity_hierarchydetail ehd on eh.id = ehd.ehierarchyid
						where eh.entityid = @nEntityId and eh.id = deh.ehierarchyid and dehd.ehdetailid = ehd.id)
						
						
		exec dbo.del_step @prim_table = 'dbo.dynamic_entity_hierarchydetail', @prim_col = 'id', @prim_val_tab = '#tabledelete'

		-- обновляем уровни иерархии
		update dehd set name = ehd.name, mask = ehd.mask, maskquery = ehd.maskquery, sequence = ehd.sequence
		from dbo.dynamic_entity_hierarchy deh
			inner join dbo.dynamic_entity_hierarchydetail dehd on deh.id = dehd.dehierarchyid
			inner join dbo.entity_hierarchy EH on eh.entityid = deh.entityid and eh.id = deh.ehierarchyid
			inner join dbo.entity_hierarchydetail ehd on eh.id = ehd.ehierarchyid and dehd.ehdetailid = ehd.id
		where deh.entityid = @nEntityId and deh.entityobjectid is null

		-- добавл¤ем новые уровни иерархии
		insert into dbo.dynamic_entity_hierarchydetail (dehierarchyid, name, mask, maskquery, sequence, ehdetailid)
		select deh.id, ehd.name, ehd.mask, ehd.maskquery, ehd.sequence, ehd.id
		from dbo.dynamic_entity_hierarchy deh
			inner join dbo.entity_hierarchy eh on eh.entityid = deh.entityid and eh.id = deh.ehierarchyid
			inner join dbo.entity_hierarchydetail ehd on eh.id = ehd.ehierarchyid
		where deh.entityid = @nEntityId and deh.entityobjectid is null
			and not exists (select * from dbo.dynamic_entity_hierarchydetail dehd where deh.id = dehd.dehierarchyid and dehd.ehdetailid = ehd.id)

--#endregion выравниваем уровни

		execute dbo.cl_hierarchy_deploy @nEntityId = @nEntityId

		drop table #extHier

		if @@trancount > 0
			commit transaction
	end try
	begin catch
		if @@trancount > 0
			rollback transaction

		declare @cMsg varchar(4000)
		select @cMsg = error_message()
		raiserror(@cMsg, 16, 1)
		return
	end catch
end
GO


-- Создание нового объекта
exec dbo.stru_edit_objects @cObjCode='REPORT_CATALOG_APP', @cObjName='ОТЧЕТЫ', @nMode=3
exec dbo.stru_add_property @cParentObjCode='REPORT_CATALOG_APP', @cObjCode='CONTAINER_CLASS', @nContainer=0
exec dbo.stru_add_property @cParentObjCode='ROOT_CATALOG_APP', @cObjCode='REPORT_CATALOG_APP', @nContainer=1
-- Окончание создания нового объекта
GO

-- Установка привязки к проектам
exec dbo.neo_set_object_proj @cMode='set_proj', @cObjCode='REPORT_CATALOG_APP', @nProjMask=0, @nWorkID=1333087947698112770, @bFullMask=1, @nWorkID2=0
GO

-- Изменение реквизитов класса
exec dbo.stru_edit_objects @nMode=5, @cCurrentObjCode='DOCUMENTS_DWH_CLASS', @cObjCode='DOCUMENTS_DWH_CLASS', @cObjName='Базовый класс документа "Проект-СМАРТ Про"', @cObjType='CLASS'
exec dbo.stru_edit_objects @nMode=5, @cCurrentObjCode='DICTIONARY_DWH_CLASS', @cObjCode='DICTIONARY_DWH_CLASS', @cObjName='Базовый класс справочника "Проект-СМАРТ Про"', @cObjType='CLASS'
GO

-- start
-- ks-377\oleg
-- Сборка: 19.5.34135, Дата выгрузки: 19.06.2019
-- Выгружаемый объект: SQL_DESC (Метод получения описания для удаляемых строк)
if object_id( 'tempdb..#OP') is not null drop table #OP if object_id( 'tempdb..#OC') is not null drop table #OC if object_id( 'tempdb..#OV') is not null drop table #OV if object_id( 'tempdb..#SO') is not null drop table #SO if object_id( 'tempdb..#TO') is not null drop table #TO if object_id( 'tempdb..#PR') is not null drop table #PR
GO

begin
	create table #PR (CODE varchar(30) not null, VALUE int null)
	create table #TO (OBJ varchar (30) not null, PAR varchar(30) null, ALLOW int null, TYPE int null)
	create table #SO (OBJ varchar(30) not null, NAME varchar(254) not null, TYPE varchar(20) null, ACCESS int null, proj bigint null, work_id bigint null, work_id2 bigint null )
	create table #OP (PROP varchar(30) not null, OBJ varchar(30) not null, ALLOW int not null, TYPE int null)
	create table #OC (PROP varchar(30) not null, OBJ varchar(30) not null, VALUE int null, DEF int null, ALLOW int null)
	create table #OV (LINK int not null, VALUE varchar(max) null, OBJ varchar(30) null)

	insert into #PR values('bWOChild', 0)
	insert into #TO(obj, par, allow, type) values('SQL_DESC', null, null, null)

	insert into #SO(obj, name, type, access, proj, work_id, work_id2) values('SQL_DESC', 'Метод получения описания для удаляемых строк', 'VARCHAR', null, 0, 0, null)

	exec script_load_object
	if object_id( 'tempdb..#OP') is not null drop table #OP if object_id( 'tempdb..#OC') is not null drop table #OC if object_id( 'tempdb..#OV') is not null drop table #OV if object_id( 'tempdb..#SO') is not null drop table #SO if object_id( 'tempdb..#TO') is not null drop table #TO if object_id( 'tempdb..#PR') is not null drop table #PR
end
GO


-- Добавление свойства
exec dbo.stru_add_property @cParentObjCode='OBJECT_CLASS', @cObjCode='SQL_DESC', @nContainer=0
GO


-- start
-- ks-377\oleg
-- Сборка: 19.2.32930, Дата выгрузки: 24.04.2019
-- Выгружаемый объект: REPORTS_DWH_CLASS (Базовый класс отчета "Проект-СМАРТ Про")
if object_id( 'tempdb..#OP') is not null drop table #OP if object_id( 'tempdb..#CL') is not null drop table #CL if object_id( 'tempdb..#OV') is not null drop table #OV if object_id( 'tempdb..#SO') is not null drop table #SO if object_id( 'tempdb..#TO') is not null drop table #TO if object_id( 'tempdb..#PR') is not null drop table #PR
GO

begin
	create table #PR (CODE varchar(30) not null, VALUE int null)
	create table #TO (OBJ varchar (30) not null, PAR varchar(30) null, ALLOW int null, TYPE int null)
	create table #SO (OBJ varchar(30) not null, NAME varchar(254) not null, TYPE varchar(20) null, ACCESS int null, proj bigint null, work_id bigint null, work_id2 bigint null )
	create table #OP (PROP varchar(30) not null, OBJ varchar(30) not null, ALLOW int not null, TYPE int null)
	create table #CL (PROP varchar(30) not null, CLASS varchar(30) not null, VALUE int null, DEF int null, STAT int null)
	create table #OV (LINK int not null, VALUE varchar(max) null, OBJ varchar(30) null)

	insert into #TO(obj, par, allow, type) values('REPORTS_DWH_CLASS', null, null, null)

	insert into #SO(obj, name, type, access, proj, work_id, work_id2) values('REPORTS_DWH_CLASS', 'Базовый класс отчета "Проект-СМАРТ Про"', 'CLASS', 0, 64, -6086773317906194000, 0)
		insert into #OP(prop, obj, allow, type) values('OBJECT_CLASS', 'REPORTS_DWH_CLASS', 0, 0)
			insert into #CL(prop, class, value, def, stat) values('ATTRIBUTES', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('CHECK_UNIQUE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('CHILD_OBJECTS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('CLOSE_CODE', 'REPORTS_DWH_CLASS', 29642, 0, 7)
							insert into #OV(link, value, obj) values(29642, 'ID', null)
			insert into #CL(prop, class, value, def, stat) values('CLOSE_CODE_UNIQUE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('DOCUMENT_TYPE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('HELP_TOPIC', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('LIC_MSG', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('LIC_PARAM', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('LIC_PARAM_KEY', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('MAXROWCOUNT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('MSG_SEL_ALL', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('MSG_SEL_NONE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('MSG_SEL_ONE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('MSG_SEL_SOME', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_GROUPS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_FOOTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_SELECT_FIELDS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_SELECT_ORDER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_SHABLON', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_SPISOK_FOOTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NASTR_XL_SPISOK_HEADER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('NODE_TYPE', 'REPORTS_DWH_CLASS', 29640, 0, 7)
							insert into #OV(link, value, obj) values(29640, 'NODE_REP_RS', null)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_ALIASES', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_DELETE_COMPLETE_EVENT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_INIT_EDIT_METHOD', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_INIT_METHOD', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_LOAD_BEFORE_EVENT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_LOAD_COMPLETE_EVENT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_LOAD_LIST_EVENT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('OBJECT_SAVE_COMPLETE_EVENT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('ORDERS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SEARCH_CODE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SEARCH_FILTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SEARCH_MAKET', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SNAME', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_CHECK', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_CLOSE_CODE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DELETE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DELETE_ACCESS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DELETE_AFTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DELETE_BEFORE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DELETE_PREPARE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_DESC', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_EDIT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_EDIT_ACCESS', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_EODCONV', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_EXPORT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_EXPORT_AFTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_FIND', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_IMPORT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_LIST', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_PRINT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SAVE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SAVE_AFTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SAVE_PREPARE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SCHEME', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SEARCH', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SETTING_CHECK', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SETTING_SAVE_AFTER', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_SIGN', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('SQL_TABLE_KEY', 'REPORTS_DWH_CLASS', 29641, 0, 7)
							insert into #OV(link, value, obj) values(29641, 'ID', null)
			insert into #CL(prop, class, value, def, stat) values('SQL_TABLE_NAME', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('TITLE', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('TOOLBAR_OBJECT', 'REPORTS_DWH_CLASS', null, 1, 7)
			insert into #CL(prop, class, value, def, stat) values('USE_SEARCH_FILTER', 'REPORTS_DWH_CLASS', null, 1, 7)

	exec script_load_class
	if object_id( 'tempdb..#OP') is not null drop table #OP if object_id( 'tempdb..#CL') is not null drop table #CL if object_id( 'tempdb..#OV') is not null drop table #OV if object_id( 'tempdb..#SO') is not null drop table #SO if object_id( 'tempdb..#TO') is not null drop table #TO if object_id( 'tempdb..#PR') is not null drop table #PR
end
GO


if  exists (select * from sys.objects where object_id = object_id(N'dbo.create_dynamic_objects') and type in (N'P', N'PC'))
	drop procedure dbo.create_dynamic_objects
GO

create proc dbo.create_dynamic_objects
	@nEntityObject int,
	@RunRTS int = 1
as
begin
	set nocount on
	declare @cEntityCode varchar(30), @cEntityName varchar(254), @cEntityTable varchar(254), @nEntityId int

	if @nEntityObject is null
		return

	if not exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject)
		return

	-- для расчетных книг не выполняем
	if exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject and eo.objecttype = 4)
		return

	if exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject and objecttype in (1,2))
		begin
			select @nEntityId = entityid, @cEntityName = left(name,254) from dbo.entityobjects where id = @nEntityObject

			select @cEntityTable = replace(ds.tablecolumn, 'ks_ddlcontrol.', '') from dbo.dynamic_structure ds where ds.entityid = @nEntityId and ds.levels = 1 and ds.hdtype = 1 and ds.parentid is null
			if exists(select * from dbo.entity where id = @nEntityId)
				select @cEntityTable = 'ks_ddlcontrol.' + @cEntityTable

			if @cEntityTable is null or coalesce(object_id(@cEntityTable), 0) = 0
				begin
					print ('отсутствует таблица, макет = ' + str(@nEntityId))
					return
				end

--#region Справочники
			if exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject and objecttype = 1)
				begin
					select @cEntityCode = 'DICTIONARY_APP_' + cast(@nEntityObject as varchar(20))

					if dbo.obj_id(@cEntityCode) is null
						begin
							execute dbo.stru_edit_objects @cObjCode=@cEntityCode, @cObjName=@cEntityName, @nMode=3
							execute dbo.stru_add_property @cParentObjCode=@cEntityCode, @cObjCode='DICTIONARY_DWH_CLASS', @nContainer=0
							execute dbo.stru_add_property @cParentObjCode='DICTIONARY_CATALOG_APP', @cObjCode=@cEntityCode, @nContainer=1
							execute dbo.neo_set_object_proj @cMode='set_proj', @cObjCode=@cEntityCode, @nProjMask=64, @nWorkID=-7159227864106861111, @bFullMask=1, @nWorkID2=0
						end
				end
--#endregion Справочники

--#region Документы
			if exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject and objecttype = 2)
				begin
					select @cEntityCode = 'DOCUMENTS_APP_' + cast(@nEntityObject as varchar(20))

					if dbo.obj_id(@cEntityCode) is null
						begin
							execute dbo.stru_edit_objects @cObjCode=@cEntityCode, @cObjName=@cEntityName, @nMode=3
							execute dbo.stru_add_property @cParentObjCode=@cEntityCode, @cObjCode='DOCUMENTS_DWH_CLASS', @nContainer=0
							execute dbo.stru_add_property @cParentObjCode='DOCUMENT_CATALOG_APP', @cObjCode=@cEntityCode, @nContainer=1
							execute dbo.neo_set_object_proj @cMode='set_proj', @cObjCode=@cEntityCode, @nProjMask=64, @nWorkID=-7159227864106861111, @bFullMask=1, @nWorkID2=0
						end

					if left(dbo.get_val('PROJECT','PLATFORM_VERSION',null),2) = '19'
						begin
							delete dbo.bpms_doc_req_assist where tab_id = dbo.tab_id(@cEntityTable) and tab_condition =  'MT.DOCUMENTID = ' + cast(@nEntityObject as varchar(20))

							insert into dbo.bpms_doc_req_assist (tab_id, tab_name, numberfield,
								datefield, prefnum, keyfield, guidfield, doc_info,
								tab_condition, object, eds_query)
							values (dbo.tab_id(@cEntityTable), @cEntityTable, 'coalesce(left(KS_DDLControl.PROTOCOL_EO_' + cast(@nEntityObject as varchar(20)) + '(MT.ID),50), ''<...>'')',
								'getdate()', '№', 'MT.ID', 'MT.GUID', 'coalesce(KS_DDLControl.PROTOCOL_EO_' + cast(@nEntityObject as varchar(20)) + '(MT.ID), ''<...>'')',
								'MT.DOCUMENTID = ' + cast(@nEntityObject as varchar(20)), dbo.obj_id(@cEntityCode), 'select ee.doc_id as link, ee.eds as eds from dbo.entity_eds ee where ee.object_id = ' + cast(@nEntityObject as varchar(20)))
						end
					else
						begin
							delete dbo.bpms_doc_req_assist where tab_id = object_id(@cEntityTable) and tab_condition =  'MT.DOCUMENTID = ' + cast(@nEntityObject as varchar(20))

							insert into dbo.bpms_doc_req_assist (tab_id, tab_name, numberfield,
								datefield, prefnum, keyfield, guidfield, doc_info,
								tab_condition, object, eds_query)
							values (object_id(@cEntityTable), @cEntityTable, 'coalesce(left(KS_DDLControl.PROTOCOL_EO_' + cast(@nEntityObject as varchar(20)) + '(MT.ID),50), ''<...>'')',
								'getdate()', '№', 'MT.ID', 'MT.GUID', 'coalesce(KS_DDLControl.PROTOCOL_EO_' + cast(@nEntityObject as varchar(20)) + '(MT.ID), ''<...>'')',
								'MT.DOCUMENTID = ' + cast(@nEntityObject as varchar(20)), dbo.obj_id(@cEntityCode), 'select ee.doc_id as link, ee.eds as eds from dbo.entity_eds ee where ee.object_id = ' + cast(@nEntityObject as varchar(20)))
						end
				end
--#endregion Документы

			execute dbo.stru_edit_objects @nMode=5, @cCurrentObjCode=@cEntityCode, @cObjCode=@cEntityCode, @cObjName=@cEntityName
			execute dbo.neo_set_val @CodeObject=@cEntityCode, @CodeProperty='SNAME', @Value=@cEntityName
			execute dbo.neo_set_val @CodeObject=@cEntityCode, @CodeProperty='TITLE', @Value=@cEntityName
			execute dbo.neo_set_val @CodeObject=@cEntityCode, @CodeProperty='SQL_TABLE_NAME', @Value=@cEntityTable

			if @RunRTS = 1
				begin
					execute dbo.xp_rts @ObjCode = @cEntityCode
					exec dbo.SetupSrvMsg @cMode = 'drop'
				end
		end

--#region Отчеты
	if exists (select * from dbo.entityobjects eo where eo.id = @nEntityObject and objecttype in (5,6))
		begin
			select @nEntityId = eo.entityid, @cEntityName = left(e.name,254) from dbo.entityobjects eo, dbo.entity e where eo.id = @nEntityObject and e.id = eo.entityid

			select @cEntityCode = 'DICTIONARY_RS_TEMPLATE_' + cast(@nEntityObject as varchar(20))
--select @nEntityId, @cEntityName, @nEntityObject, @cEntityCode
			if dbo.obj_id(@cEntityCode) is null
				begin
					execute dbo.stru_edit_objects @cObjCode=@cEntityCode, @cObjName=@cEntityName, @nMode=3
					execute dbo.stru_add_property @cParentObjCode=@cEntityCode, @cObjCode='REPORTS_DWH_CLASS', @nContainer=0
					execute dbo.stru_add_property @cParentObjCode='REPORT_CATALOG_APP', @cObjCode=@cEntityCode, @nContainer=1
					execute dbo.neo_set_object_proj @cMode='set_proj', @cObjCode=@cEntityCode, @nProjMask=64, @nWorkID=-7159227864106861111, @bFullMask=1, @nWorkID2=0
				end

			exec dbo.SetupSrvMsg @cMode = 'drop'
		end
--#endregion Отчеты

end
GO


--#region Удаление мертвых документов
declare @cExecSmt varchar(max)

select @cExecSmt = (select '; if not exists(select * from ks_ddlcontrol.' + object_name(tab_id) + ' where id = ' + cast(link_doc as varchar(20)) + ') exec dbo.bpms_state_del @LinkDrs = ' + cast(link as varchar(20))
from dbo.bpms_document_router_state bt
left join dbo.dynamic_structure ds on ds.tablecolumn = object_name(bt.tab_id)
left join dbo.entity e on e.id = ds.entityid
where e.entitytype = 2
order by tab_id
for xml path(''))

exec (@cExecSmt)
--#endregion Удаление мертвых документов
GO

--#region Удаление мертвых объектов
set nocount on
declare @cStr varchar(max)

declare @DeadEO table (code varchar(30), eo int, OBJECT int)

insert into @DeadEO (code)
select code from dbo.s_objects where code like 'DOCUMENTS[_]APP[_]%'
union
select code from dbo.s_objects where code like 'DICTIONARY[_]APP[_]%'
union
select code from dbo.s_objects where code like 'DICTIONARY[_]RS[_]TEMPLATE[_]%' and code <> 'DICTIONARY_RS_TEMPLATE_ITEM'

update deo set eo = dbo.j_sub_str(code,'_',3) from @DeadEO deo where not code like 'DICTIONARY[_]RS[_]TEMPLATE[_]%'
update deo set eo = dbo.j_sub_str(code,'_',4) from @DeadEO deo where code like 'DICTIONARY[_]RS[_]TEMPLATE[_]%'
delete deo from @DeadEO deo where exists (select * from dbo.entityobjects e where e.id = deo.eo)
update deo set object = dbo.obj_id(code) from @DeadEO deo
delete deo from @DeadEO deo where coalesce(object,0) = 0

delete bdru from dbo.bpms_documents_routers_users bdru, dbo.bpms_documents_routers bdr where bdr.link = bdru.documents_routers and exists (select * from @DeadEO where object = bdr.object)
delete bdrd from dbo.bpms_documents_routers_divs bdrd, dbo.bpms_documents_routers bdr where bdr.link = bdrd.documents_routers and exists (select * from @DeadEO where object = bdr.object)
delete bdr from dbo.bpms_documents_routers bdr where exists (select * from @DeadEO where object = bdr.object)
delete bdrse from dbo.bpms_document_router_state_end bdrse where exists (select * from @DeadEO where object = bdrse.object)
delete bdrs from dbo.bpms_document_router_state bdrs where exists (select * from @DeadEO where object = bdrs.object)

if exists (select * from @DeadEO)
	begin
		select @cStr = stuff((select ',' + code as [text()] from @DeadEO for xml path('')),1,1,'')
		exec dbo.neo_do @cCodeDoc = @cStr
	end
--#endregion Удаление мертвых объектов
GO

--#region Перестраиваем объекты после изменения процедуры
set nocount on
declare @nId int
create table #EntityObjects (entityid int)

delete bt
from dbo.bpms_doc_req_assist bt
inner join dbo.dynamic_structure ds on ds.tablecolumn = object_name(bt.tab_id)
inner join dbo.entity e on e.id = ds.entityid and e.entitytype = 2

insert into #EntityObjects (entityid)
select eo.id
from dbo.entity e
	inner join dbo.entityobjects eo on e.id = eo.entityid
	inner join dbo.entityindicator ei on e.id = ei.id
where e.entitytype in (1,2) and eo.objecttype in (1,2) and ei.chdstruct > 0

insert into #EntityObjects (entityid)
select eo.id
from dbo.entity e
	inner join dbo.entityobjects eo on e.id = eo.entityid
where e.entitytype in (3,6) and eo.objecttype in (5,6)

while exists (select * from #EntityObjects)
	begin
		select top 1 @nId = entityid from #EntityObjects
		execute dbo.create_dynamic_objects @nEntityObject = @nId, @RunRTS = 0
print @nId
		delete #EntityObjects where @nId = entityid
	end
--#endregion Перестраиваем объекты после изменения процедуры
GO


update dbo.gate set code = 'DICTIONARY_RS_TEMPLATE_' + cast(out_link as varchar(20)) where NODE_TYPE = 'NODE_REP_RS'
update dbo.gate set object = dbo.obj_id(code) where NODE_TYPE = 'NODE_REP_RS'
GO


if exists (select * from sys.triggers where object_id = object_id(N'dbo.entity_ei'))
	drop trigger dbo.entity_ei
GO

create trigger dbo.entity_ei on dbo.entity
for insert, update, delete
as
begin
	set nocount on
	declare @id int, @cCodeDoc varchar(200)

--#region delete
	if not exists(select * from inserted)
		begin
			-- Удаление фиктивной строки, связанной с отчетом RDL и сводным отчетом
			delete eo
			from dbo.entityobjects eo inner join deleted d on eo.entityid = d.id
			where d.entitytype in (3,6)

			select @cCodeDoc = 'DICTIONARY_RS_TEMPLATE_' + cast(d.id as varchar(20)) from deleted d where d.entitytype in (3,6)
			if @cCodeDoc is not null
				exec dbo.neo_do @cCodeDoc = @cCodeDoc

			return
		end
--#endregion delete

--#region insert
	if not exists(select * from deleted)
		begin
			-- Добавление записи(индикатора) для текущей сущности
			insert into dbo.entityindicator(id, chstruct)
			select i.id, 1 from inserted i where i.entitytype in (1,2)

			-- Добавление фиктивной строки в dbo.EntityObjects для отчетов RDL из dbo.Entity
			insert into dbo.entityobjects (code, name, entityid, objecttype, objectversion)
			select i.code, i.name, i.id, case i.entitytype when 3 then 5 when 6 then 6 else 0 end, 1
			from inserted i
			where i.entitytype in (3,6) and not exists(select * from dbo.entityobjects eo where eo.entityid = i.id)

			select @id = scope_identity()
			if @id is not null
				exec dbo.create_dynamic_objects @nEntityObject = @id

			return
		end
--#endregion insert

--#region update
	-- Анализ измененных полей записи(сущности)
	update eo set code = i.code, name = i.name
	from dbo.entityobjects eo
		inner join inserted i on eo.entityid = i.id
		inner join deleted d on i.id = d.id
	where i.entitytype in (3,6) and coalesce(i.name,'') <> coalesce(d.name,'')

	update ei set chstruct = coalesce(ei.chstruct,0) + 1
	from dbo.entityindicator ei
			inner join inserted i on ei.id = i.id
			inner join deleted d on i.id = d.id
	where (coalesce(i.entitytype, 0) <> coalesce(d.entitytype, 0) or coalesce(i.isversioned, 0) <> coalesce(d.isversioned, 0)
		or coalesce(i.allowpermission, 0) <> coalesce(d.allowpermission, 0)) and i.entitytype in (1,2)

	--данное поле только для показателей!
	update ei set chstruct = coalesce(ei.chstruct,0) + 1,
		chgmstructstr = case when ei.chgmstructstr is null then cast(cast(coalesce(ei.chstruct,0) + 1 as int) as varchar(50)) + ',' +  cast(ei.chdstruct as varchar(20)) else ei.chgmstructstr end
	from inserted i
			inner join deleted d on i.id = d.id
			inner join dbo.attribute a on i.id = a.reference
			inner join dbo.entityindicator ei on a.entityid = ei.id
	where i.entitytype = 5 and coalesce(i.attributetype, 0) <> coalesce(d.attributetype, 0)
--#endregion update

	update ei set chstruct =  coalesce(ei.chstruct,0) + 1
	from inserted i, deleted d, dbo.entityindicator ei
	where ei.id = i.id and ei.id = d.id
		and (
			coalesce(i.protocoltext,'') <> coalesce(d.protocoltext,''))

end

GO


exec dbo.gp
GO


exec dbo.xp_rts
GO




exec dbo.dropdeftable @ctable = 'dbo.calc_measure_history'
GO

if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[calc_measure_history]') and type in (N'u'))
begin
create table [dbo].[calc_measure_history](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[calcmeasureid] int, 
	[guid] [uniqueidentifier] NOT NULL,
	[date] datetime,
	[userid] int,
 constraint [pk_calc_measure_history] primary key clustered
(
	[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary]
) on [primary]
end
GO

alter table [dbo].[calc_measure_history] add constraint [df_calc_measure_history_date] default (getdate()) for [date]
GO

alter table [dbo].[calc_measure_history] add constraint [df_calc_measure_history_guid] default (user_id()) for [userid]
GO




if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[fk_calc_measure_row_fid]') and parent_object_id = object_id(N'[dbo].[calc_measure_row_info]'))
alter table [dbo].[calc_measure_row_info] drop constraint [fk_calc_measure_row_fid]
GO

exec dbo.dropdeftable @ctable = 'dbo.calc_measure_row_info'
GO

if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[calc_measure_row_info]') and type in (N'u'))
begin
create table [dbo].[calc_measure_row_info](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[idHistory] int, 
	[tableName] varchar(300) NOT NULL,
	[guid] [uniqueidentifier] NOT NULL,
	[idRow] int NOT NULL, 
	[timestampInfo] int NOT NULL,
 constraint [pk_calc_measure_row_info] primary key clustered
(
	[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary]
) on [primary]
end
GO

alter table [dbo].[calc_measure_row_info] with check add constraint [fk_calc_measure_row_fid] foreign key([idHistory]) references [calc_measure_history] ([id])
GO

alter table [dbo].[calc_measure_row_info] check constraint [fk_calc_measure_row_fid]
GO

alter table [dbo].[calc_measure_row_info] add constraint [df_measure_row_info_guid] default (newid()) for [guid]
GO




-- Изменение: KS-227(gubka) (22.05.2019 09:40:54) Метка: KS-227 (22.05.2019 09:41:36)
-- включение структуры в структуру
-- Bugzilla #110742
-- Комментарий к изменению:
--    Кнопки Импорта пользователей с СА
exec stru_add_stru_object @cParentStruCode = 'TOOLBAR_USERS_GROUPS_PLT', @cObjCode = 'BUTTON_IN_EOD_PLT'
GO


-- Изменение: KS-227(gubka) (22.05.2019 09:40:54) Метка: KS-227 (22.05.2019 09:41:54)
-- установка, сброс значений свойств связок объектов, классов и структур
-- Bugzilla #110742
-- Комментарий к изменению:
--    Кнопки Импорта пользователей с СА
exec stru_set_val @cObjCode = 'DICTIONARY_USERS_ADM', @cPropCode = 't_Execute_Plugin', @cStruPath = 'TOOLBAR_USERS_GROUPS_PLT.BUTTON_IN_EOD_PLT', @cValue = '<Function Name="Meta.Users.Import" />'
GO


-- Изменение: KS-227(gubka) (22.05.2019 09:40:54) Метка: KS-227 (22.05.2019 09:41:54)
-- установка, сброс значений свойств связок объектов, классов и структур
-- Bugzilla #110742
-- Комментарий к изменению:
--    Кнопки Импорта пользователей с СА
exec stru_set_val @cObjCode = 'DICTIONARY_USERS_TEMPLATE_ADM', @cPropCode = 't_Visible', @cStruPath = 'TOOLBAR_USERS_GROUPS_PLT.BUTTON_IN_EOD_PLT', @cValue = '0'
GO


if  exists (select * from sys.triggers where object_id = object_id(N'dbo.dehierarchy_deploy'))
	drop trigger dbo.dehierarchy_deploy
GO


create trigger dbo.dehierarchy_deploy on dbo.dynamic_entity_hierarchydetail
for insert, update, delete
as
begin
	set nocount on
	declare @idHierarchy int

	if object_id('tempdb..#exthier') is null
	begin

--#region delete
		if not exists(select * from inserted)
			begin
				if exists(select * from dbo.dynamic_entity_hierarchy deh, deleted d where deh.id = d.dehierarchyid and deh.entityobjectid is not null)
						select @idHierarchy = dehierarchyid from deleted
			end
--#endregion delete

--#region insert/update
		else									
			begin
				if exists(select * from dbo.dynamic_entity_hierarchy deh, inserted i where deh.id = i.dehierarchyid and deh.entityobjectid is not null)
						select @idHierarchy = dehierarchyid from inserted
			end
--#endregion insert/update

		if (@idHierarchy is not null)
			execute dbo.cl_hierarchy_deploy_item @nDEHierarchyId = @idHierarchy	

	end
end
GO

