<?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">
DO 
$$ 
DECLARE _sql varchar;
        _pkTable varchar;
        _pkColName varchar;
        _type varchar(11);
        _id uuid;
BEGIN
    <xsl:for-each select="./*">
      <xsl:call-template name="EntityDataRowLoad" />
    </xsl:for-each>
END
$$ LANGUAGE  plpgsql;
  </xsl:template>
  <!--Обработка записей EntityDataLoad-->
  <xsl:template name="EntityDataRowLoad">
select ea.MappingName, ea.MappingTable, case when exists(select * from V_ENTITY_HIERARCHY where EntityID = e.EntityID and BaseEntity != e.EntityID) then cast(e.EntityID as varchar) else '' end, coalesce(vs.Val::uuid, gen_random_uuid())
      into _pkColName, _pkTable, _type, _id
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 '' as Name, '' as 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 = true;
select STRING_AGG(t.SqlText, '; ' ORDER BY t.depth desc)
      into _sql
from
(
    select 'INSERT INTO ' || eaEnt.Schema || '.' || ea.MappingTable || '(' || _pkColName || ', ' || STRING_AGG(ea.MappingName, ', ' ORDER BY ea.MappingName) || case when _type != '' and _pkTable = ea.MappingTable then ', type_' else '' end
        || ') SELECT ''' || cast(_id as varchar(36)) || ''', '
        || STRING_AGG(COALESCE(vsEnt.EntityID::varchar, '''' || vs.Val || '''', 'NULL'), ', ' 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 '' as Name, '' as 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 = true
            and
        ea.IsPrimaryKey = false
    group by eaEnt.Schema, e.EntName, ea.MappingTable, ae.depth
) t;
EXECUTE _sql;
  </xsl:template>

</xsl:stylesheet>
