<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
	xmlns:user="http://mycompany.com/mynamespace" xmlns:js="urn:the-xml-files:xslt-csharp" xmlns:ntd='http://www.ntd.ru/ntd/'>
  <xsl:output method="text" />

  <xsl:template match="text()"/>

  <!--Загрузка данных EntityDataLoad-->
  <xsl:template match="EntityDataLoad">
declare @sql nvarchar(max),
        @pkTable varchar(max),
        @pkColName varchar(max),
        @type_ varchar(11),
        @id uniqueidentifier

    <xsl:for-each select="./*">
      <xsl:call-template name="EntityDataRowLoad" />
    </xsl:for-each>
  </xsl:template>
  <!--Обработка записей EntityDataLoad-->
  <xsl:template name="EntityDataRowLoad">
select @pkColName = ea.MappingName
      ,@pkTable = ea.MappingTable
      ,@type_ = case when exists(select * from V_ENTITY_HIERARCHY where EntityID = e.EntityID and BaseEntity != e.EntityID) then cast(e.EntityID as varchar(11)) else '' end
      ,@id = ISNULL(cast(vs.Val as uniqueidentifier), NEWID())
from (
        select [EntityID] ,[Name] ,[SystemType] ,[MaxLength] ,[Precision] ,[Scale] ,[IsRequired] ,[MappingType] ,[MappingName] ,[IsStored] ,[MappingTable] ,[MappingRefTable] ,[IsPrimaryKey]
        from VCLib.T_ENTITIES_ATTRIBUTES
        union all
        select [EntityID] ,[Name] ,[SystemType] ,[MaxLength] ,[Precision] ,[Scale] ,[IsRequired] ,[MappingType] ,[MappingName] ,[IsStored] ,[MappingTable] ,[MappingRefTable] ,[IsPrimaryKey]
        from V_ENTITY_ATTRIBUTES
    ) ea
    join ( select ae.BaseEntity, ae.EntityID, ae.depth from V_ENTITY_HIERARCHY ae ) ae on ea.EntityID = ae.BaseEntity
    join VCLib.T_ENTITIES e on e.EntityID = ae.EntityID
    left join (
            select '' [Name], '' Val
            <xsl:for-each select="./@*">
              <xsl:text>union select '</xsl:text>
              <xsl:value-of select="name()"/>
              <xsl:text>', '</xsl:text>
              <xsl:value-of select="."/>'
            </xsl:for-each>) vs on vs.[Name] = ea.[Name]
where e.[Schema] + '.' + e.EntName = '<xsl:value-of select ="name(.)"/>'
        and
    ea.IsPrimaryKey = 1
select @sql = STRING_AGG(t.SqlText, '; ') WITHIN GROUP ( ORDER BY t.depth desc )
from
(
    select 'INSERT INTO ' + eaEnt.[Schema] + '.' + ea.MappingTable + '(' + @pkColName + ', ' + STRING_AGG(ea.MappingName, ', ') WITHIN GROUP ( ORDER BY ea.MappingName ) + case when @type_ != '' and @pkTable = ea.MappingTable then ', type_' else '' end
         + ') SELECT ''' + cast(@id as varchar(36)) + ''', '
         + STRING_AGG(ISNULL(cast(vsEnt.EntityID as varchar(max)), ISNULL('''' + vs.Val + '''', 'NULL')), ', ') WITHIN GROUP ( ORDER BY ea.MappingName )
         + case when @type_ != '' and @pkTable = ea.MappingTable then ', ' + @type_ else '' end SqlText, ae.depth
    from (
            select [EntityID] ,[Name] ,[SystemType] ,[MaxLength] ,[Precision] ,[Scale] ,[IsRequired] ,[MappingType] ,[MappingName] ,[IsStored] ,[MappingTable] ,[MappingRefTable] ,[IsPrimaryKey]
            from VCLib.T_ENTITIES_ATTRIBUTES
            union all
            select [EntityID] ,[Name] ,[SystemType] ,[MaxLength] ,[Precision] ,[Scale] ,[IsRequired] ,[MappingType] ,[MappingName] ,[IsStored] ,[MappingTable] ,[MappingRefTable] ,[IsPrimaryKey]
            from V_ENTITY_ATTRIBUTES
        ) ea
        join VCLib.T_ENTITIES eaEnt on eaEnt.EntityID = ea.EntityID
        join ( select ae.BaseEntity, ae.EntityID, ae.depth from V_ENTITY_HIERARCHY ae ) ae on ea.EntityID = ae.BaseEntity
        join VCLib.T_ENTITIES e on e.EntityID = ae.EntityID
        left join (
                select '' [Name], '' Val
                <xsl:for-each select="./@*">
                  <xsl:text>union select '</xsl:text>
                  <xsl:value-of select="name()"/>
                  <xsl:text>', '</xsl:text>
                  <xsl:value-of select="."/>'
                </xsl:for-each>) vs on vs.[Name] = ea.[Name]
        left join VCLib.T_ENTITIES vsEnt on ea.SystemType = 'VCLib.ENTITIES' 
                                                and 
                                            CHARINDEX('.', vs.Val, 1) > 0
                                                and
                                            vsEnt.[Schema] + '.' + vsEnt.EntName = vs.Val
    where e.[Schema] + '.' + e.EntName = '<xsl:value-of select ="name(.)"/>'
            and
        ea.MappingType = 1 -- Таблица
            and
        ea.MappingTable is not null
            and
        ea.MappingName is not null
            and
        ea.IsStored = 1
            and
        ea.IsPrimaryKey = 0
    group by eaEnt.[Schema], e.EntName, ea.MappingTable, ae.depth
) t
exec sp_executesql @sql
  </xsl:template>

</xsl:stylesheet>
