
/****** Object:  StoredProcedure [dbo].[sp_UpdateEntities]    Script Date: 19-May-16 7:04:31 PM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateEntities]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateEntities]
GO

-- =============================================
-- Author:		Vitaly Shatkov, Khuzin Ilnur
-- Create date: 2016-03-30
-- Description:	Распарсивание сущностей
-- =============================================
Create PROCEDURE [dbo].[sp_UpdateEntities]
AS
Begin
SET NOCOUNT ON;

truncate table VCLib.T_ENTITIES_ATTRIBUTES


select EntityID as [id], cast(replace(replace(replace(replace(cast([xml] as nvarchar(max)),N'<?xml version="1.0" encoding="utf-8" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-8"?>',N''),N'<?xml version="1.0" encoding="utf-16"?>',N'') as xml) as [xml]
into #xmls
from VCLib.T_ENTITIES


-- Корректируем BaseEntity
update e
set e.BaseEntity = (select i.EntityID 
					from VCLib.T_ENTITIES i 
					where i.EntName = SUBSTRING(x.[xml].value('/Entity[1]/@Inherits','varchar(255)'), (charindex('.', x.[xml].value('/Entity[1]/@Inherits','varchar(255)')) + 1), 1000)
					and i.[Schema] = SUBSTRING(x.[xml].value('/Entity[1]/@Inherits','varchar(255)'), 1, (charindex('.', x.[xml].value('/Entity[1]/@Inherits','varchar(255)')) - 1)))
from VCLib.T_ENTITIES e
join #xmls x on e.EntityID = x.id


declare @xmlChar nvarchar(max), @xml xml
set @xmlChar = 
(
	select cast(replace(replace(replace(replace(cast([xml] as nvarchar(max)),N'<?xml version="1.0" encoding="utf-8" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-8"?>',N''),N'<?xml version="1.0" encoding="utf-16"?>',N'') as xml)
	from VCLib.T_ENTITIES
	FOR XML PATH(''), ROOT('ENTITIES')
)
set @xml = cast(@xmlChar as xml)
select e.EntityID as [EntId]
		, Tab.n.value('./@Name', 'varchar(255)') [Attr]
		,Tab.n.value('./@Column', 'varchar(255)') [Column]
		,Tab.n.value('./@Function', 'varchar(255)') [Function]	
		,Tab.n.value('./@RefTable', 'varchar(255)') [RefTable]	
		,Tab.n.value('./@IsStored', 'bit') [IsStored]
		,cast(Tab.N.query('./*') as varchar(max)) as [Lexem]
into #Map
from @XML.nodes('/ENTITIES/Entity/DbMapping/*/Map') as Tab(N)
join VCLib.T_ENTITIES e on Tab.n.value('../../../@Name', 'varchar(255)') = e.EntName and Tab.n.value('../../../@Schema', 'varchar(255)') = e.[Schema]
where Tab.N.value('fn:local-name(..)', 'varchar(255)') in ('Table', 'Lexem')

create clustered index ind_#Map_EntId_Attr on #Map(EntId, Attr)

--print 'Map: ' + convert(varchar(50),getdate(),121)

select 		e.EntityID as EntityId
		,   Attr.N.value('./@Index', 'Int') as [Index]
		,	Attr.N.value('./@Name', 'varchar(255)') as Name
		,	Attr.N.value('./@Description', 'varchar(255)') as [Description]
		,	Attr.N.value('./@Type', 'varchar(255)') as [SystemType]
		,	Attr.N.value('./@CharacterMaximumLength', 'int') as [MaxLength]
		,	Attr.N.value('./@NumericPrecision', 'int') as [Precision]
		,	Attr.N.value('./@NumericScale', 'int') as [Scale]
		,	Attr.N.value('./@DefaultFormat', 'varchar(max)') as [DefaultFormat]
		,	case when Attr.N.value('./@Name', 'varchar(255)') = Attr.N.value('../../@PrimaryKey', 'varchar(255)') then -1
				 when Attr.N.value('fn:local-name(.)', 'varchar(255)') = 'Attribute' then 0
				 when Attr.N.value('fn:local-name(.)', 'varchar(255)') = 'Parent' then 1
				 when Attr.N.value('fn:local-name(.)', 'varchar(255)') = 'Child' then 2
                 when Attr.N.value('fn:local-name(.)', 'varchar(255)') = 'Link'  then 3
				 when Attr.N.value('fn:local-name(.)', 'varchar(255)') = 'Choice' then 4 else null end as AttributeType -- Атрибут
		,	case when Attr.N.value('fn:local-name(..)', 'varchar(255)') = 'CalculatingAttributes' then 1 else 0 end as IsCalculated
		,	isnull(Attr.N.value('./@IsFlag', 'bit'),0) as [IsFlag]
		,	isnull(Attr.N.value('./@Required', 'bit'),0) as IsRequired
				,	isnull(Attr.N.value('./@Save', 'bit'),0) as [Save]	
		,	Attr.N.value('./@LinkAttribute', 'varchar(max)') as LinkAttributeName	 
		, case when Len(cast(Attr.N.query('./Item') as varchar(max)))>0 then '<?xml version="1.0" encoding="utf-16"?>
<ChoiceItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">' 
	+ 
	(	
		select replace(cast(Attr.N.query('./Item') as varchar(max)), 'Item Name', 'ChoiceItem')
	) 
	+ '
</ChoiceItems>' else null end	  as ChoiceAttributeData
		--,	Map.Lexem as AttrLexem	
		,	Attr.N.value('./@PathAttributes', 'varchar(max)') as PathsAttributes
		,	isnull(Attr.N.value('./@IsCashed', 'bit'),0) as [IsCashed]
		,	Attr.N.value('../../@Inherits', 'varchar(255)') as Inherits
into #tmp
from @XML.nodes('/ENTITIES/Entity/*/*') as Attr(N)
join VCLib.T_ENTITIES e on Attr.N.value('../../@Name', 'varchar(255)') = e.EntName and Attr.N.value('../../@Schema', 'varchar(255)') = e.[Schema]
where Attr.N.value('fn:local-name(..)', 'varchar(255)') in ('Fields', 'CalculatingAttributes')

create clustered index ind_#tmp_EntityId on #tmp(EntityId)

--print 'Attributes: ' + convert(varchar(50),getdate(),121)


-- Строим табличку связей
---------------------------------------------------------------------
declare @tsql varchar(max)
if object_id('tempdb..#EntitiesHierarchy') is not null
	drop table #EntitiesHierarchy
create table #EntitiesHierarchy(father int, son int)
create clustered index ind_#EntitiesHierarchy_Father on #EntitiesHierarchy(father)
;with CTE as 
	(
		SELECT EntityId, BaseEntity, 0 as Lvl
		FROM VCLib.T_ENTITIES
		WHERE BaseEntity is null
		UNION All
		SELECT e.EntityId, e.BaseEntity, CTE.Lvl + 1
		FROM VCLib.T_ENTITIES as e
		JOIN CTE
		ON e.BaseEntity = CTE.EntityId
	)
select* into #preTree from CTE

create table #Num(c1 int)
declare @lvl int, @tsqlFrom varchar(max), @tsqlLJoin varchar(max), @tsqlSelect varchar(max), @tsqlSelect1 varchar(max), @tsqlOrder varchar(max), @tsqlInto varchar(30)
declare @from varchar(max), @tsql2 varchar(max), @num1 varchar(max), @num2 varchar(max)
select @from = '', @tsql2 = '', @num1 = '', @num2 = ''
set @tsqlInto = '
into #tree'
set @tsqlSelect = 'select distinct
	t0.EntityId as l0'
set @tsql = ''
set @tsqlLJoin = ''
set @tsqlFrom = '
from #preTree t0'
set @tsqlOrder = ''
set @tsqlSelect1 = ''
DECLARE cTree CURSOR FOR 
SELECT Lvl FROM #preTree
group by Lvl
order by Lvl;

OPEN cTree

FETCH NEXT FROM cTree 
INTO @lvl

WHILE @@FETCH_STATUS = 0
BEGIN
	if @lvl = 0
	begin		
		set @tsqlSelect1 = 't0.EntityId'
	end
	else
	begin
		if @lvl = 1
			set @tsqlOrder = '
order by 1'
		set @tsqlLJoin = @tsqlLJoin + '
left join #preTree t' + cast(@lvl as varchar(5)) + ' on t' + cast(@lvl -1 as varchar(5)) + '.EntityId = t' + cast(@lvl as varchar(5)) + '.BaseEntity and t' + cast(@lvl - 1 as varchar(5)) + '.lvl = ' + cast(@lvl -1 as varchar(5))
		set @tsqlSelect1 = 't' + cast(@lvl as varchar(5)) + '.EntityId,' + @tsqlSelect1;
		set @tsqlSelect = @tsqlSelect + '
,	coalesce(' + @tsqlSelect1 + ') as l' + cast(@lvl as varchar(5))
		if @lvl > 1
			set @tsqlOrder = @tsqlOrder + ',' + cast(@lvl as varchar(5))
	end
	insert into #Num(c1)
	Values(@lvl)
	FETCH NEXT FROM cTree 
    INTO @lvl
END 
CLOSE cTree;
DEALLOCATE cTree;

select @from = @from + 
'
select distinct l' + cast(n1.c1 as varchar(5)) + ' as father, l' + cast(n2.c1 as varchar(5)) + ' as son
from #tree
union'
from #Num n1
cross join
	#Num n2
where n1.c1 <= n2.c1
order by n1.c1, n2.c1 desc

set @from = left(@from,len(@from)-6)

set @tsql2 = '
select distinct father, son
from
(' + @from + '
) Tab
'
set @tsql = @tsqlSelect + @tsqlInto + @tsqlFrom + @tsqlLJoin + @tsqlOrder + @tsql2 
--print @tsql
insert into #EntitiesHierarchy(father, son)
exec(@tsql)
---------------------------------------------------------------------
--print 'BaseEnt: ' + convert(varchar(50),getdate(),121)

insert into VCLib.T_ENTITIES_ATTRIBUTES(EntitiesAttributesId,EntityID,[Index],AttributeType,Name,[Description],SystemType,[MaxLength],[Precision],Scale,DefaultFormat,IsCalculated,IsFlag,IsRequired,MappingType,MappingName,IsStored,[Save],LinkAttributeName,ChoiceAttributeData,AttrLexem,PathsAttributes,IsCashed)
select
	cast(eh.son as varchar(11)) + '|' + cast(t.[Index] as varchar(11))
,	eh.son
,	t.[Index]
,	t.AttributeType
,	t.Name
,	t.[Description]
,	t.SystemType
,	t.[MaxLength]
,	t.[Precision]
,	t.Scale
,	t.DefaultFormat
,	t.IsCalculated
,	t.IsFlag
,	t.IsRequired
,	case	when Map.[Column] is not null and Map.RefTable is null
			then 1 -- Колонка
			when Map.[Function] is not null
			then 2 -- Функция
			when Map.[Column] is not null and Map.RefTable is not null
			then 3 -- Таблица
			when Map.Lexem is not null
			then 4 -- Лексема
			else 0 -- Не маппировано
	end as MappingType
,	case	when Map.RefTable is not null then Map.RefTable
			when Map.[Function] is not null then  Map.[Function] else Map.[Column] 
	end as MappingName
,	IsNull(Map.IsStored, 0) as IsStored
,	t.[Save]
,	t.LinkAttributeName
,	t.ChoiceAttributeData
,	Map.Lexem as AttrLexem
,	t.PathsAttributes
,	t.IsCashed
from #tmp t
left join
	#Map map
	on t.EntityId = map.EntId
	and t.Name = Map.Attr
inner join
	#EntitiesHierarchy eh
	on eh.father = t.EntityID

--print 'Insert: ' + convert(varchar(50),getdate(),121)
		
Return

End
GO

exec sp_UpdateEntities