create table #lexemTables (pkColumn varchar(255), lexemColumn varchar(255), tableName varchar(255), pkType varchar(1), cond varchar(max))
GO

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('ListsEntityID','PreferedFilter','FIES.T_LISTS_ENTITY','','')

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('ListsEntityID','HiddenFilter','FIES.T_LISTS_ENTITY','','')

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('PayParametrID','RoundSettings','FIES.T_PLAN_PARAMETER','','')

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('PayParametrID','ParameterLexem','FIES.T_PLAN_PARAMETER','','')

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('SubprogrammID','Condition','FIES.T_SUBPROGRAMM','','')

insert into #lexemTables (pkColumn, lexemColumn, tableName,pkType, cond)
values ('CoefficientID','CustomLexem','FIES.T_COEFFICIENT','','')




create table #replaces (name varchar(255), fullname varchar(255), pattern varchar(255))
go
insert into #replaces (name, fullname,pattern)
select '"' + EntName + '"', '"'+ [Schema] + '.' + EntName + '"', '%"' + EntName + '"%'
from VCLib.T_ENTITIES

insert into #replaces (name, fullname,pattern)
select '"' + EntName + '"', '"'+ [Schema] + '.' + EntName + '"', '%"' + EntName + '"%'
from VCLib.T_ADDITIONAL_ENTITIES

create table #lexems (pk uniqueidentifier primary key, lexem xml)
go
create table #lexems2 (pk int primary key, lexem xml)
go


declare @tableCursor cursor, @pkColumn varchar(255), @lexemColumn varchar(255), @tableName varchar(255), @pkType varchar(1), @cond varchar(max), @command nvarchar(max)
set @tableCursor = cursor for select pkColumn, lexemColumn, tableName,pkType,cond from #lexemTables
open @tableCursor
fetch next from @tableCursor into @pkColumn, @lexemColumn, @tableName,@pkType, @cond
while @@FETCH_STATUS = 0
begin

	set @command = '
	
	declare @itemCursor cursor
	declare @replaceName varchar(255), @replaceFullname varchar(255)
	declare @itemPk uniqueIdentifier, @itemPk2 int
	set @itemCursor = cursor local static FORWARD_ONLY READ_ONLY for SELECT i.'+@pkColumn+', r.name, r.fullname FROM '+@tableName+' i join #replaces r on i.'+@lexemColumn+' like  r.pattern 

	open @itemCursor
	fetch next from @itemCursor into @itemPk'+@pkType+', @replaceName, @replaceFullname
	while @@FETCH_STATUS = 0
	begin
		update '+@tableName+'
		set '+@lexemColumn+' = REPLACE(cast('+@lexemColumn+' as varchar(max)), @replaceName, @replaceFullname)
		where '+@pkColumn+' = @itemPk'+@pkType+'
	
		fetch next from @itemCursor into @itemPk'+@pkType+', @replaceName, @replaceFullname
	end
	close @itemcursor'
	exec sp_executesql @command

	
	set @command = '
	delete from #lexems'+@pkType+'

	declare @trycursor cursor, @trypk uniqueidentifier, @trypk2 int, @trylexem varchar(max)
	set @trycursor = cursor for select '+@pkColumn+', '+@lexemColumn+' from '+@tableName+' where '+@lexemColumn+' is not null '+@cond+'
	open @trycursor
	fetch next from @trycursor into @trypk'+@pkType+', @trylexem
	while @@fetch_status =0
	begin
		begin try
			insert into #lexems'+@pkType+'(pk, lexem) select @trypk'+@pkType+', CONVERT(xml, @trylexem)
		end try
		begin catch
		end catch
		
		fetch next from @trycursor into @trypk'+@pkType+', @trylexem
	end

declare @lexemCursor cursor, @pk uniqueidentifier, @pk2 int, @entId int, @entName varchar(255), @nodeType varchar(255), @command2 nvarchar(max)
set @lexemCursor = cursor local static forward_only for 
	SELECT pk, T2.Loc.value(''@EntityIndex'', ''int''), e.[Schema]+''.''+e.EntName, ''ChoiceOperand''
	FROM   #lexems'+@pkType+'
	CROSS APPLY lexem.nodes(''declare namespace lx="lexem";//lx:ChoiceOperand'') as T2(Loc) 
	join (select EntityID, EntName, [Schema] from VCLib.T_ENTITIES union select EntityID, EntName, [Schema] from VCLib.T_ADDITIONAL_ENTITIES) e on T2.Loc.value(''@EntityIndex'', ''int'')=e.EntityID
	union all
	SELECT pk, T2.Loc.value(''@EntityIndex'', ''int''), e.[Schema]+''.''+e.EntName, ''BaseObjectOperand''
	FROM   #lexems'+@pkType+'
	CROSS APPLY lexem.nodes(''declare namespace lx="lexem";//lx:BaseObjectOperand'') as T2(Loc) 
	join (select EntityID, EntName, [Schema] from VCLib.T_ENTITIES union select EntityID, EntName, [Schema] from VCLib.T_ADDITIONAL_ENTITIES) e on T2.Loc.value(''@EntityIndex'', ''int'')=e.EntityID
open @lexemcursor

fetch next from @lexemCursor into @pk'+@pkType+', @entId,@entName, @nodeType
while @@FETCH_STATUS=0
begin
	set @command2=''
	declare @lexem xml
	set @lexem=(select top 1 lexem from #lexems'+@pkType+' where pk = ''''''+cast(@pk'+@pkType+' as varchar(255))+'''''')
	
	set @lexem.modify(''''declare namespace lx="lexem";           
						insert attribute Entity { "''+@entName+''" }             
						into (//lx:''+@nodeType+''[@EntityIndex="''+cast(@entId as varchar(16))+''"])[1]'''');
	set @lexem.modify(''''declare namespace lx="lexem";
						delete (//lx:''+@nodeType+''[@EntityIndex="''+cast(@entId as varchar(16))+''" and @Entity="''+@entName+''"]/@EntityIndex)[1]'''');
						
	update #lexems'+@pkType+' set lexem=@lexem where pk=''''''+cast(@pk'+@pkType+' as varchar(255))+'''''' ''

	exec sp_executesql @command2

	fetch next from @lexemCursor into @pk'+@pkType+', @entId,@entName, @nodeType
end

close @lexemcursor
update t 
set '+@lexemColumn+' = cast(l.lexem as varchar(max)) 
from '+@tableName+' t 
join #lexems'+@pkType+' l on t.'+@pkColumn+'=l.pk '
	exec sp_executesql @command


	fetch next from @tableCursor into @pkColumn, @lexemColumn, @tableName,@pkType,@cond

end
close @tableCursor

drop table #lexemTables
drop table #replaces
drop table #lexems
drop table #lexems2
