<?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/'
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsl:output method="text" />

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

  <!-- Cascade delete settings -->
  <xsl:template match="CascadeDeleteSettings">
    <xsl:for-each select="CascadeDeleteSetting">
delete from VCLib.T_CASCADE_DELETE_SETTING where Entities = (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@Entity" />');
INSERT INTO VCLib.T_CASCADE_DELETE_SETTING
    (Entities
    ,ValidationLexem
    <xsl:choose><xsl:when test="Dependencies">,Dependencies</xsl:when></xsl:choose>
    <xsl:text>)</xsl:text>
SELECT
      (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@Entity" />')
      <xsl:text>,'</xsl:text>
      <xsl:call-template name="serializeNodeToString">
        <xsl:with-param name="node" select="ValidationLexem/*"/>
      </xsl:call-template>'
      <xsl:choose><xsl:when test="Dependencies">,'<xsl:value-of select="Dependencies" />'</xsl:when></xsl:choose>;
    </xsl:for-each>
  </xsl:template>

  <!-- Load entity xml -->
  <xsl:template match="Entity">
    <xsl:variable name="xmlString">
      <xsl:text>&lt;?xml version="1.0" encoding="utf-8" standalone="yes"?&gt;</xsl:text>
      <xsl:call-template name="serializeNodeToString">
        <xsl:with-param name="node" select="."/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="xmlForScript">
      <xsl:call-template name="replaceApostrs">
        <xsl:with-param name="text" select="$xmlString"/>
      </xsl:call-template>
    </xsl:variable>

    <xsl:text>DO&#10;$$&#10;DECLARE _EntId int;&#10;BEGIN&#10;&#09;IF NOT EXISTS(SELECT * FROM VCLib.T_ENTITIES WHERE Schema='</xsl:text>
    <xsl:value-of select="@Schema" />
    <xsl:text>' and EntName='</xsl:text>
    <xsl:value-of select="@Name" />
    <xsl:text>')&#10;&#09;THEN&#10;&#09;&#09;INSERT INTO VCLib.T_ENTITIES (EntityID, EntName, XML, ServerName, NeedHistory, IsAdditional, Schema)&#10;&#09;&#09;VALUES ((coalesce((select EntityID from VCLib.T_ENTITIES order by EntityID desc limit 1), 0) + 1), '</xsl:text>
    <xsl:value-of select="@Name" />
    <xsl:text>', NULL, 'Primary SQL Server', '0', </xsl:text>
    <xsl:choose>
      <xsl:when test="@Settings='1'">'1', '</xsl:when>
      <xsl:otherwise>'0', '</xsl:otherwise>
    </xsl:choose>
    <xsl:value-of select="@Schema" />
    <xsl:text>');&#10;&#09;&#09;UPDATE T_ENTITY_IDS SET id = COALESCE((select MAX(e.EntityID) from VCLib.T_ENTITIES e), 0) + 1 WHERE EntName = 'VCLib.ENTITIES' AND FieldName = 'PrimaryKey';&#10;&#09;END IF;&#10;&#10;&#09;UPDATE VCLib.T_ENTITIES&#10;&#09;SET XML='</xsl:text>
    <xsl:value-of select="$xmlForScript"/>
    <xsl:text>',&#10;&#09;EntDesc = '</xsl:text>
    <xsl:value-of select="@Description" />
    <xsl:text>',&#10;&#09;PrimaryKeyName = </xsl:text>
    <xsl:choose>
      <xsl:when test="@PrimaryKey">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@PrimaryKey"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;Version = </xsl:text>
    <xsl:choose>
      <xsl:when test="@version">
        <xsl:value-of select="@version" />
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;BaseEntityName = </xsl:text>
    <xsl:choose>
      <xsl:when test="@Inherits">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@Inherits" />
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;BaseEntity = </xsl:text>
    <xsl:choose>
      <xsl:when test="@Inherits">
        <xsl:text>(select EntityID from VCLib.T_ENTITIES where Schema='</xsl:text>
        <xsl:value-of select="substring-before(@Inherits, '.')" />
        <xsl:text>' and EntName = '</xsl:text>
        <xsl:value-of select="substring-after(@Inherits, '.')" />
        <xsl:text>' limit 1)</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;DisplayName = </xsl:text>
    <xsl:choose>
      <xsl:when test="@DisplayName">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@DisplayName"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;DNAttributes = </xsl:text>
    <xsl:choose>
      <xsl:when test="@DNAttributes">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@DNAttributes"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;IsTree = </xsl:text>
    <xsl:choose>
      <xsl:when test="@IsTree">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@IsTree"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>'0'</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;UnrestrictedAccess = </xsl:text>
    <xsl:choose>
      <xsl:when test="@UnrestrictedAccess">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@UnrestrictedAccess"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>'0'</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;Timestamped = </xsl:text>
    <xsl:choose>
      <xsl:when test="@Timestamped">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@Timestamped"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;Customs = </xsl:text>
    <xsl:choose>
      <xsl:when test="./Customs/Custom">
        <xsl:text>'&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;Customs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;</xsl:text>
        <xsl:for-each select="./Customs/Custom">
          <xsl:call-template name="serializeNodeToString">
            <xsl:with-param name="node" select="."/>
          </xsl:call-template>
        </xsl:for-each>
        <xsl:text>&lt;/Customs&gt;'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;Sortings = </xsl:text>
    <xsl:choose>
      <xsl:when test="./Sortings/Sorting">
        <xsl:text>'&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;Sortings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;</xsl:text>
        <xsl:for-each select="./Sortings/Sorting">
          <xsl:call-template name="serializeNodeToString">
            <xsl:with-param name="node" select="."/>
          </xsl:call-template>
        </xsl:for-each>
        <xsl:text>&lt;/Sortings&gt;'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;&#09;CanGroupInTreeListBy = </xsl:text>
    <xsl:choose>
      <xsl:when test="./CanGroupInTreeListBy/@Value">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="./CanGroupInTreeListBy/@Value"/>
        <xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>&#10;&#09;WHERE Schema='</xsl:text>
    <xsl:value-of select="@Schema" />
    <xsl:text>' and EntName='</xsl:text>
    <xsl:value-of select="@Name" />
    <xsl:choose>
      <xsl:when test="@Settings='1'">
        <xsl:text>' and IsAdditional='1'</xsl:text>
      </xsl:when>
      <xsl:otherwise>'</xsl:otherwise>
    </xsl:choose>
    <xsl:text>;&#10;&#10;&#09;_EntId := (SELECT EntityId FROM VCLib.T_ENTITIES WHERE Schema='</xsl:text>
    <xsl:value-of select="@Schema" />
    <xsl:text>' and EntName='</xsl:text>
    <xsl:value-of select="@Name" />
    <xsl:text>' LIMIT 1);&#10;&#10;&#09;UPDATE VCLib.T_ENTITIES SET BaseEntity = _EntId WHERE BaseEntityName = '</xsl:text>
    <xsl:value-of select="@Schema" />
    <xsl:text>.</xsl:text>
    <xsl:value-of select="@Name" />
    <xsl:text>';&#10;</xsl:text>
    <xsl:choose>
      <xsl:when test="@Settings='1'" />
      <xsl:otherwise>
        <xsl:text>&#10;&#09;DELETE FROM VCLib.T_ENTITIES_ATTRIBUTES WHERE IsAdditional = '0' AND EntityId = _EntId AND Name NOT IN ('</xsl:text>
        <xsl:for-each select="Fields/*|CalculatingAttributes/*">
           <xsl:value-of select="@Name"/>
           <xsl:if test="position() != last()">
              <xsl:text>', '</xsl:text>
           </xsl:if>
        </xsl:for-each>
        <xsl:text>');&#10;</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:for-each select="Fields/*|CalculatingAttributes/*">
      <xsl:variable name="field" select="."/>
      <xsl:variable name="map" select="../../DbMapping/node()[not(self::Filter)]/Map[@Name=$field/@Name]" />
      <xsl:variable name="Index">
        <xsl:choose>
          <xsl:when test="@Index">
            <xsl:value-of select="@Index" />
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="AttributeType">
        <xsl:choose>
          <xsl:when test="name(.)='Attribute'">0</xsl:when>
          <xsl:when test="name(.)='Parent'">1</xsl:when>
          <xsl:when test="name(.)='Child'">2</xsl:when>
          <xsl:when test="name(.)='Link'">3</xsl:when>
          <xsl:when test="name(.)='Choice'">4</xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Description">
        <xsl:call-template name="replaceApostrs">
          <xsl:with-param name="text">
            <xsl:value-of select="@Description" />
          </xsl:with-param>
        </xsl:call-template>
      </xsl:variable>
      <xsl:variable name="MaxLength">
        <xsl:choose>
          <xsl:when test="@CharacterMaximumLength">
            <xsl:value-of select="@CharacterMaximumLength" />
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Precision">
        <xsl:choose>
          <xsl:when test="@NumericPrecision">
            <xsl:value-of select="@NumericPrecision" />
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Scale">
        <xsl:choose>
          <xsl:when test="@NumericScale">
            <xsl:value-of select="@NumericScale" />
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="DefaultFormat">
        <xsl:choose>
          <xsl:when test="@DefaultFormat">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@DefaultFormat" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsCalculated">
        <xsl:choose>
          <xsl:when test="name(..)='CalculatingAttributes'">'1'</xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsFlag">
        <xsl:choose>
          <xsl:when test="@IsFlag">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@IsFlag" />
            <xsl:text>'</xsl:text>
          </xsl:when>
        <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsRequired">
        <xsl:choose>
          <xsl:when test="@Required">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@Required" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Save">
        <xsl:choose>
          <xsl:when test="@Save">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@Save" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'1'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="ContentType">
        <xsl:choose>
          <xsl:when test="@ContentType">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@ContentType" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="LinkAttributeName">
        <xsl:choose>
          <xsl:when test="@LinkAttribute">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@LinkAttribute" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:when test="@ChildAttribute">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@ChildAttribute" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="PathsAttributes">
        <xsl:choose>
          <xsl:when test="@PathAttributes">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@PathAttributes" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:when test="../../DbMapping/Lexem/Map[@Name=$field/@Name]/@PathAttributes">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="../../DbMapping/Lexem/Map[@Name=$field/@Name]/@PathAttributes" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Filter">
        <xsl:choose>
          <xsl:when test="../../DbMapping/Filter/Map[@Name=$field/@Name]/text()">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="../../DbMapping/Filter/Map[@Name=$field/@Name]/text()" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:when test="../../DbMapping/Filter/Map[@Name=$field/@Name]/*">
            <xsl:text>'</xsl:text>
            <xsl:for-each select="../../DbMapping/Filter/Map[@Name=$field/@Name]/*">
              <xsl:call-template name="serializeNodeToString">
                <xsl:with-param name="node" select="."/>
              </xsl:call-template>
            </xsl:for-each>
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsCashed">
        <xsl:choose>
          <xsl:when test="@Cashed">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@Cashed" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="RequiredAttributes">
        <xsl:choose>
          <xsl:when test="@RequiredAttributes">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@RequiredAttributes" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="SortingAttribute">
        <xsl:choose>
          <xsl:when test="@SortingAttr">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@SortingAttr" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="EnumType">
        <xsl:choose>
          <xsl:when test="@EnumType">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@EnumType" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsIdentity">
        <xsl:choose>
          <xsl:when test="@IdentityPrimaryKey">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@IdentityPrimaryKey" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsCustom">
        <xsl:choose>
          <xsl:when test="@IsCustom">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@IsCustom" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="RemoveAction">
        <xsl:choose>
          <xsl:when test="@RemoveAction">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@RemoveAction" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'1'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsPrimaryKey">
        <xsl:choose>
          <xsl:when test="@Name = ../../@PrimaryKey">'1'</xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="ChoiceAttributeData">
        <xsl:choose>
          <xsl:when test="./Item">
            <xsl:text>'&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;ChoiceItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;</xsl:text>
            <xsl:for-each select="./Item">
              <xsl:call-template name ="replace">
                <xsl:with-param name="text">
                  <xsl:call-template name ="replace">
                    <xsl:with-param name="text">
                      <xsl:call-template name="serializeNodeToString">
                        <xsl:with-param name="node" select="."/>
                      </xsl:call-template>
                    </xsl:with-param>
                    <xsl:with-param name="replace">Item Name</xsl:with-param>
                    <xsl:with-param name="by">ChoiceItem Name</xsl:with-param>
                  </xsl:call-template>
                </xsl:with-param>
                <xsl:with-param name="replace">/Item</xsl:with-param>
                <xsl:with-param name="by">/ChoiceItem</xsl:with-param>
              </xsl:call-template>
            </xsl:for-each>
            <xsl:text>&lt;/ChoiceItems&gt;'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="ParametersData">
        <xsl:choose>
          <xsl:when test="./Parameter">
            <xsl:text>'&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;</xsl:text>
            <xsl:for-each select="./Parameter">
              <xsl:call-template name="serializeNodeToString">
                <xsl:with-param name="node" select="."/>
              </xsl:call-template>
            </xsl:for-each>
            <xsl:text>&lt;/Parameters&gt;'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="CustomsData">
        <xsl:choose>
          <xsl:when test="./Custom">
            <xsl:text>'&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;Customs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;</xsl:text>
            <xsl:for-each select="./Custom">
              <xsl:call-template name="serializeNodeToString">
                <xsl:with-param name="node" select="."/>
              </xsl:call-template>
            </xsl:for-each>
            <xsl:text>&lt;/Customs&gt;'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="MappingType">
        <xsl:choose>
          <xsl:when test="name($map/..)='Spread'">5</xsl:when>
          <xsl:when test="$map/@Column and name($map/..)='Table'">1</xsl:when>
          <xsl:when test="$map/@Column and name($map/..)='View'">6</xsl:when>
          <xsl:when test="$map/@Function">2</xsl:when>
          <xsl:when test="$map/*">4</xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="MappingName">
        <xsl:choose>
          <xsl:when test="$map/@Function">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@Function" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:when test="$map/@Column">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@Column" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsStored">
        <xsl:choose>
          <xsl:when test="$map/@IsStored">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@IsStored" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>'1'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="MappingTable">
        <xsl:choose>
          <xsl:when test="$map/../@name">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/../@name" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="MappingRefTable">
        <xsl:choose>
          <xsl:when test="$map/@RefTable">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@RefTable" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="RefTableSearchMode">
        <xsl:choose>
          <xsl:when test="$map/@SearchMode">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@SearchMode" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="RefTableHashFunc">
        <xsl:choose>
          <xsl:when test="$map/@HashFunc">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/@HashFunc" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="AttrLexem">
        <xsl:choose>
          <xsl:when test="$map/*">
            <xsl:text>'</xsl:text>
            <xsl:for-each select="$map/*">
              <xsl:call-template name="serializeNodeToString">
                <xsl:with-param name="node" select="."/>
              </xsl:call-template>
            </xsl:for-each>
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsAdditional">
        <xsl:choose>
          <xsl:when test="../../@Settings='1'">'1'</xsl:when>
          <xsl:otherwise>'0'</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:text>&#10;&#09;UPDATE VCLib.T_ENTITIES_ATTRIBUTES SET Index = </xsl:text>
      <xsl:value-of select="$Index" />
      <xsl:text>, AttributeType = </xsl:text>
      <xsl:value-of select="$AttributeType" />
      <xsl:text>, Name = '</xsl:text>
      <xsl:value-of select="@Name" />
      <xsl:text>', Description = '</xsl:text>
      <xsl:value-of select="$Description" />
      <xsl:text>', SystemType = '</xsl:text>
      <xsl:value-of select="@Type" />
      <xsl:text>', MaxLength = </xsl:text>
      <xsl:value-of select="$MaxLength" />
      <xsl:text>, Precision = </xsl:text>
      <xsl:value-of select="$Precision" />
      <xsl:text>, Scale = </xsl:text>
      <xsl:value-of select="$Scale" />
      <xsl:text>, DefaultFormat = </xsl:text>
      <xsl:value-of select="$DefaultFormat" />
      <xsl:text>, IsCalculated = </xsl:text>
      <xsl:value-of select="$IsCalculated" />
      <xsl:text>, IsFlag = </xsl:text>
      <xsl:value-of select="$IsFlag" />
      <xsl:text>, IsRequired = </xsl:text>
      <xsl:value-of select="$IsRequired" />
      <xsl:text>, Save = </xsl:text>
      <xsl:value-of select="$Save" />
      <xsl:text>, LinkAttributeName = </xsl:text>
      <xsl:value-of select="$LinkAttributeName" />
      <xsl:text>, PathsAttributes = </xsl:text>
      <xsl:value-of select="$PathsAttributes" />
      <xsl:text>, Filter = </xsl:text>
      <xsl:value-of select="$Filter" />
      <xsl:text>, IsCashed = </xsl:text>
      <xsl:value-of select="$IsCashed" />
      <xsl:text>, RequiredAttributes = </xsl:text>
      <xsl:value-of select="$RequiredAttributes" />
      <xsl:text>, SortingAttribute = </xsl:text>
      <xsl:value-of select="$SortingAttribute" />
      <xsl:text>, EnumType = </xsl:text>
      <xsl:value-of select="$EnumType" />
      <xsl:text>, IsIdentity = </xsl:text>
      <xsl:value-of select="$IsIdentity" />
      <xsl:text>, IsCustom = </xsl:text>
      <xsl:value-of select="$IsCustom" />
      <xsl:text>, RemoveAction = </xsl:text>
      <xsl:value-of select="$RemoveAction" />
      <xsl:text>, IsPrimaryKey = </xsl:text>
      <xsl:value-of select="$IsPrimaryKey" />
      <xsl:text>, ChoiceAttributeData = </xsl:text>
      <xsl:value-of select="$ChoiceAttributeData" />
      <xsl:text>, ParametersData = </xsl:text>
      <xsl:value-of select="$ParametersData" />
      <xsl:text>, CustomsData = </xsl:text>
      <xsl:value-of select="$CustomsData" />
      <xsl:text>, MappingType = </xsl:text>
      <xsl:value-of select="$MappingType" />
      <xsl:text>, MappingName = </xsl:text>
      <xsl:value-of select="$MappingName" />
      <xsl:text>, IsStored = </xsl:text>
      <xsl:value-of select="$IsStored" />
      <xsl:text>, MappingTable = </xsl:text>
      <xsl:value-of select="$MappingTable" />
      <xsl:text>, MappingRefTable = </xsl:text>
      <xsl:value-of select="$MappingRefTable" />
      <xsl:text>, RefTableSearchMode = </xsl:text>
      <xsl:value-of select="$RefTableSearchMode" />
      <xsl:text>, RefTableHashFunc = </xsl:text>
      <xsl:value-of select="$RefTableHashFunc" />
      <xsl:text>, AttrLexem = </xsl:text>
      <xsl:value-of select="$AttrLexem" />
      <xsl:text>, ContentType = </xsl:text>
      <xsl:value-of select="$ContentType" />
      <xsl:text>&#10;&#09;WHERE EntityID = _EntId AND Name = '</xsl:text>
      <xsl:value-of select="@Name" />
      <xsl:text>' AND IsAdditional = </xsl:text>
      <xsl:value-of select="$IsAdditional" />
      <xsl:text>;&#10;&#09;INSERT INTO VCLib.T_ENTITIES_ATTRIBUTES (EntitiesAttributesId, EntityID, Index, AttributeType, Name, Description, SystemType, MaxLength, Precision, Scale, DefaultFormat, IsCalculated, IsFlag, IsRequired, Save, LinkAttributeName, PathsAttributes, Filter, IsCashed, RequiredAttributes, SortingAttribute, EnumType, IsIdentity, IsCustom, RemoveAction, IsPrimaryKey, ChoiceAttributeData, ParametersData, CustomsData, MappingType, MappingName, IsStored, MappingTable, MappingRefTable, RefTableSearchMode, RefTableHashFunc, AttrLexem, IsAdditional, ContentType)&#10;</xsl:text>
      <xsl:text>&#09;SELECT gen_random_uuid(), _EntId, </xsl:text>
      <xsl:value-of select="$Index" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$AttributeType" />
      <xsl:text>, '</xsl:text>
      <xsl:value-of select="@Name" />
      <xsl:text>', '</xsl:text>
      <xsl:value-of select="$Description" />
      <xsl:text>', '</xsl:text>
      <xsl:value-of select="@Type" />
      <xsl:text>', </xsl:text>
      <xsl:value-of select="$MaxLength" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$Precision" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$Scale" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$DefaultFormat" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsCalculated" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsFlag" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsRequired" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$Save" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$LinkAttributeName" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$PathsAttributes" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$Filter" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsCashed" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$RequiredAttributes" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$SortingAttribute" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$EnumType" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsIdentity" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsCustom" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$RemoveAction" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsPrimaryKey" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$ChoiceAttributeData" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$ParametersData" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$CustomsData" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$MappingType" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$MappingName" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsStored" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$MappingTable" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$MappingRefTable" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$RefTableSearchMode" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$RefTableHashFunc" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$AttrLexem" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$IsAdditional" />
      <xsl:text>, </xsl:text>
      <xsl:value-of select="$ContentType" />
      <xsl:text>&#10;</xsl:text>
      <xsl:text>&#09;WHERE NOT EXISTS(SELECT * FROM VCLib.T_ENTITIES_ATTRIBUTES where EntityID = _EntId AND Name = '</xsl:text>
      <xsl:value-of select="@Name" />
      <xsl:text>' AND IsAdditional = </xsl:text>
      <xsl:value-of select="$IsAdditional" />
      <xsl:text>);&#10;</xsl:text>
    </xsl:for-each>
    <xsl:text>END;&#10;$$;&#10;--PGSQL:GO&#10;&#10;</xsl:text>
  </xsl:template>
	
	<xsl:template name="client_module_commands">

		<xsl:param name="command"/>

		<xsl:choose>

			<xsl:when test="count($command/*)=0">

				IF NOT EXISTS(select * from VCLib.T_COMMAND cm where cm.ClientModules =  ID and cm.Name = '<xsl:value-of select="$command/@Name" />')
				then
				INSERT INTO VCLib.T_COMMAND (CommandID, Name, Enabled, IsDefault, CommandAppearance, Icon, Owner, type_, ClientModules)
				SELECT gen_random_uuid(), '<xsl:value-of select="$command/@Name" />', '<xsl:value-of select="@Enabled" />', '<xsl:value-of select="@IsDefault" />', '<xsl:value-of select="@CommandAppearance" />', '<xsl:value-of select="@Icon" />', ownerID , (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1),  ID;
				else
				UPDATE  VCLib.T_COMMAND
				SET Enabled = '<xsl:value-of select="@Enabled" />',
				IsDefault = '<xsl:value-of select="@IsDefault" />',
				CommandAppearance = '<xsl:value-of select="@CommandAppearance" />',
				Icon =  '<xsl:value-of select="@Icon" />',
				type_ = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1)
				WHERE ClientModules = ID AND Name = '<xsl:value-of select="$command/@Name" />';
				end if;

			</xsl:when>

			<xsl:otherwise>

				<xsl:for-each select="$command/command">
					
					IF NOT EXISTS(select * from VCLib.T_COMMAND cm WHERE cm.ClientModules =  ID and cm.Name = '<xsl:value-of select="../@Name" />')
					then
					INSERT INTO VCLib.T_COMMAND (CommandID, Name, Enabled, IsDefault, CommandAppearance, Icon, Owner, type_, ClientModules)
					SELECT gen_random_uuid(), '<xsl:value-of select="../@Name" />', '<xsl:value-of select="@Enabled" />', '<xsl:value-of select="@IsDefault" />', '<xsl:value-of select="@CommandAppearance" />', '<xsl:value-of select="@Icon" />', ownerID , (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1), ID;
					else
					UPDATE  VCLib.T_COMMAND
					SET Enabled = '<xsl:value-of select="@Enabled" />',
					IsDefault = '<xsl:value-of select="@IsDefault" />',
					CommandAppearance = '<xsl:value-of select="@CommandAppearance" />',
					Icon =  '<xsl:value-of select="@Icon" />',
					type_ = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1)
					WHERE ClientModules = ID AND Name = '<xsl:value-of select="../@Name" />';
					end if;

					ownerID := (SELECT CommandID FROM VCLib.T_COMMAND cm WHERE cm.ClientModules =  ID and cm.Name = '<xsl:value-of select="../@Name" />' limit 1);

					<xsl:call-template name="client_module_commands">

						<xsl:with-param name="command" select="."/>

					</xsl:call-template>
						
				</xsl:for-each> 

			</xsl:otherwise>

		</xsl:choose>

	</xsl:template>

  <xsl:template match="root/Schemas/Schema">
    do
    $$
    begin
    IF NOT EXISTS(SELECT * FROM VCLib.T_SCHEMA WHERE SchemaName='<xsl:value-of select="@Name" />')
    then
    INSERT INTO VCLib.T_SCHEMA (SchemaName, Priority) VALUES ('<xsl:value-of select="@Name"/>', <xsl:value-of select="@Priority"/>);
    end if;
    end;
    $$;

    <xsl:for-each select="client_modules/*">
	  do $$
	  declare 
	  ownerID uuid;
	  declare
	  _type int;
	  declare
	  id uuid;
	  begin
	  _type := (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1);
	  ID := (SELECT ClientModuleID FROM VCLib.T_CLIENT_MODULES WHERE ModuleType = '<xsl:value-of select="@ModuleType" />' and Schema = '<xsl:value-of select="../../@Name" />' LIMIT 1);
      if (id is null)
      then
      begin
      id = gen_random_uuid();
      INSERT INTO VCLib.T_CLIENT_MODULES (ClientModuleID, ModuleType, ModuleDescription, Schema, type_)
      values (id, '<xsl:value-of select="@ModuleType" />', '<xsl:value-of select="@ModuleDescription" />', '<xsl:value-of select="../../@Name" />', _type);
      <xsl:for-each select="client_module_features/client_module_feature">
        INSERT INTO VCLib.T_CLIENT_MODULE_FEATURE (ClientModuleFeatureID, ClientModules, Name, Description, EntityIndex)
        values (gen_random_uuid(), ID, '<xsl:value-of select="@Name" />', '<xsl:value-of select="@Description" />', (SELECT EntityID FROM VCLib.T_ENTITIES WHERE Schema || '.' || EntName = '<xsl:choose>
          <xsl:when test="@EntityName">
            <xsl:value-of select="@EntityName" />
          </xsl:when>
          <xsl:otherwise>VCLib.ACTIVE_FEATURE</xsl:otherwise>
        </xsl:choose>'));
      </xsl:for-each>
      <xsl:for-each select ="client_module_forms/client_module_form">
        <xsl:call-template name="ClientModuleForms"/>
      </xsl:for-each>
		
		<xsl:for-each select="./command">
			ownerID := NULL;
			<xsl:call-template name="client_module_commands">

				<xsl:with-param name="command" select="."/>

			</xsl:call-template>
		</xsl:for-each>

		<xsl:if test="name(.) = 'custom_client_module'">

			<xsl:for-each select="client_module_handlers/client_module_handlers[@Type = 0]">
				INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER (ClientModuleHandlerID, CustomClientModule, HandlerType, Entities, ProcessName, Prepare)
				SELECT gen_random_uuid(), id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="prepare" />';
			</xsl:for-each>

			<xsl:for-each select="client_module_handlers/client_module_handlers[@Type = 1]">
				INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER (ClientModuleHandlerID, CustomClientModule, HandlerType, Entities, ProcessName, Prepare, PrePrepare)
				SELECT gen_random_uuid(), id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="prepare" />', '<xsl:value-of select="preprepare" />';
			</xsl:for-each>

		</xsl:if>
		
      end;
      else
      begin
      UPDATE VCLib.T_CLIENT_MODULES
      SET ModuleDescription = '<xsl:value-of select="@ModuleDescription" />'
      ,Schema = '<xsl:value-of select="../../@Name" />'
	  ,type_ = _type
      WHERE ClientModuleID = ID;
      end;
      end if;
      <xsl:for-each select="client_module_features/client_module_feature">
        IF NOT EXISTS(select * from VCLib.T_CLIENT_MODULE_FEATURE cmf where cmf.ClientModules = ID and cmf.Name = '<xsl:value-of select="@Name" />')
        then
        INSERT INTO VCLib.T_CLIENT_MODULE_FEATURE (ClientModuleFeatureID, ClientModules, Name, Description, EntityIndex)
        values (gen_random_uuid(), ID, '<xsl:value-of select="@Name" />', '<xsl:value-of select="@Description" />', (SELECT EntityID FROM VCLib.T_ENTITIES WHERE Schema || '.' || EntName = '<xsl:choose>
          <xsl:when test="@EntityName">
            <xsl:value-of select="@EntityName" />
          </xsl:when>
          <xsl:otherwise>VCLib.ACTIVE_FEATURE</xsl:otherwise>
        </xsl:choose>'));
        else
        UPDATE VCLib.T_CLIENT_MODULE_FEATURE
        SET Description = '<xsl:value-of select="@Description" />'
        , EntityIndex = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE Schema || '.' || EntName = '<xsl:choose>
          <xsl:when test="@EntityName">
            <xsl:value-of select="@EntityName" />
          </xsl:when>
          <xsl:otherwise>VCLib.ACTIVE_FEATURE</xsl:otherwise>
        </xsl:choose>')
        WHERE ClientModules = ID and Name = '<xsl:value-of select="@Name" />';
        end if;
      </xsl:for-each>
      <xsl:for-each select ="client_module_forms/client_module_form">
        <xsl:call-template name="ClientModuleForms"/>
      </xsl:for-each>
		
		<xsl:for-each select="./command">
			ownerID := NULL;
			<xsl:call-template name="client_module_commands">

				<xsl:with-param name="command" select="."/>

			</xsl:call-template>
		</xsl:for-each>

		<xsl:if test="name(.) = 'custom_client_module'">

			<xsl:for-each select="client_module_handlers/client_module_handler[@Type = 0]">
				IF NOT EXISTS(SELECT * from VCLib.T_CLIENT_MODULE_HANDLER mh where mh.CustomClientModule = ID and mh.HandlerType = '<xsl:value-of select="@Type" />' and mh.ProcessName = '<xsl:value-of select="@Name" />')
				then
				INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER (ClientModuleHandlerID, CustomClientModule, HandlerType, Entities, ProcessName, Prepare)
				SELECT gen_random_uuid(), ID, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="prepare" />';
				ELSE
				BEGIN
				UPDATE VCLib.T_CLIENT_MODULE_HANDLER
				SET HandlerType = '<xsl:value-of select="@Type" />'
				,ProcessName = '<xsl:value-of select="@Name" />'
				,Entities = '<xsl:value-of select="@ObjectType" />'
				,Prepare = '<xsl:value-of select="prepare" />'
				FROM VCLib.T_CLIENT_MODULE_HANDLER mh
				WHERE mh.CustomClientModule = ID and  mh.HandlerType = '<xsl:value-of select="@Type" />' and mh.ProcessName = '<xsl:value-of select="@Name" />';
				END;
				end if;
			</xsl:for-each>

			<xsl:for-each select="client_module_handlers/client_module_handler[@Type = 1]">
				IF NOT EXISTS(SELECT * from VCLib.T_CLIENT_MODULE_HANDLER mh where mh.CustomClientModule = ID and mh.HandlerType = '<xsl:value-of select="@Type" />' and mh.ProcessName = '<xsl:value-of select="@Name" />')
				then
				INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER (ClientModuleHandlerID, CustomClientModule, HandlerType, Entities, ProcessName, PrePrepare, Prepare)
				SELECT gen_random_uuid(), ID, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="preprepare" />', '<xsl:value-of select="prepare" />';
				ELSE
				BEGIN
				UPDATE VCLib.T_CLIENT_MODULE_HANDLER
				SET HandlerType = '<xsl:value-of select="@Type" />'
				,ProcessName = '<xsl:value-of select="@Name" />'
				,Entities = '<xsl:value-of select="@ObjectType" />'
				,PrePrepare = '<xsl:value-of select="preprepare" />'
				,Prepare = '<xsl:value-of select="prepare" />'
				FROM VCLib.T_CLIENT_MODULE_HANDLER mh
				WHERE mh.CustomClientModule = ID and  mh.HandlerType = '<xsl:value-of select="@Type" />' and mh.ProcessName = '<xsl:value-of select="@Name" />';
				END;
				end if;
			</xsl:for-each>
		</xsl:if>
		
      end;$$;
    </xsl:for-each>

    <xsl:for-each select="holders/*">
		do $$
		declare
		_type int;
		declare
		_id uuid;
		begin
		_type := (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = upper('<xsl:value-of select ="name(.)"/>') limit 1);
		_id := (SELECT HolderID FROM VCLib.T_HOLDER WHERE Name = '<xsl:value-of select="@HolderName" />' and Schema = '<xsl:value-of select="../../@Name" />' and IsClient=<xsl:choose>
        <xsl:when test="@IsClient">
          '<xsl:value-of select="@IsClient" />'
        </xsl:when>
        <xsl:otherwise>'0'</xsl:otherwise>
      </xsl:choose> limit 1);
      if _id is NULL
      then
      BEGIN
      _id := gen_random_uuid();
      INSERT INTO VCLib.T_HOLDER (HolderID, Schema, Name, Description, IsClient, type_)
      SELECT _id, '<xsl:value-of select="../../@Name" />', '<xsl:value-of select="@HolderName" />', '<xsl:value-of select="@Description" />', <xsl:choose>
        <xsl:when test="@IsClient">
          '<xsl:value-of select="@IsClient" />'
        </xsl:when>
        <xsl:otherwise>'0'</xsl:otherwise>
      </xsl:choose>, _type;

      <xsl:for-each select="holder_parameters/holder_parameter">
        INSERT INTO VCLib.T_HOLDER_PARAMETER (HolderParameterID, Holder, ParameterName, Description, ParameterValue)
        SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Name" />', '<xsl:value-of select="@Description" />', <xsl:choose>
          <xsl:when test="@Value">
            '<xsl:value-of select="@Value" />'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>;
      </xsl:for-each>

		<xsl:if test="name(.) = 'custom_holder'">
			INSERT INTO VCLib.T_CUSTOM_HOLDER (HolderID, On_Load, On_Start, On_Stop, On_Unload, IsCachedDataHolder)
			SELECT _id, '<xsl:value-of select="on_load" />', '<xsl:value-of select="on_start" />', '<xsl:value-of select="on_stop" />', '<xsl:value-of select="on_unload" />', '<xsl:value-of select="@IsCachedDataHolder" />';


			<xsl:for-each select="holder_references/holder_reference">
				INSERT INTO VCLib.T_HOLDER_REFERENCE (HolderReferenceID, CustomHolder, Holder)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@RefHolder" />';
			</xsl:for-each>


			<xsl:for-each select="holder_handlers/handler[@Type = 0]">
				INSERT INTO VCLib.T_HOLDER_HANDLER (HolderHandlerID, CustomHolder, HandlerType, Entities, ProcessName, Execute, Postexecute, Prepare)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="execute" />', '<xsl:value-of select="postexecute" />', '<xsl:value-of select="prepare" />';
			</xsl:for-each>

			<xsl:for-each select="holder_handlers/handler[@Type = 1]">
				INSERT INTO VCLib.T_HOLDER_HANDLER (HolderHandlerID, CustomHolder, HandlerType, Entities, ProcessName, Execute, Postexecute, PreExecute, Prepare, PrePostexecute, PrePrepare)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="execute" />', '<xsl:value-of select="postexecute" />', '<xsl:value-of select="preexecute" />','<xsl:value-of select="prepare" />','<xsl:value-of select="prepostexecute" />','<xsl:value-of select="preprepare" />';
			</xsl:for-each>

			<xsl:for-each select="safe_loop_threads/safe_loop_thread">
				INSERT INTO VCLib.T_SAFE_LOOP_THREAD (SafeLoopThreadID, CustomHolder, ThreadName, Interval, ThreadLexem)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@ThreadName" />', '<xsl:value-of select="@Interval" />', '<xsl:value-of select="threadLexem" />';
			</xsl:for-each>

		</xsl:if>
		END;
		ELSE
		BEGIN
		UPDATE VCLib.T_HOLDER
		SET Description = '<xsl:value-of select="@Description" />'
      WHERE HolderID = _id;

      <xsl:for-each select="holder_parameters/holder_parameter">
        IF NOT EXISTS(select * from VCLib.T_HOLDER_PARAMETER hp where hp.Holder = _id and hp.ParameterName = '<xsl:value-of select="@Name" />')
        then
        INSERT INTO VCLib.T_HOLDER_PARAMETER (HolderParameterID, Holder, ParameterName, Description, ParameterValue)
        SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Name" />', '<xsl:value-of select="@Description" />', <xsl:choose>
          <xsl:when test="@Value">
            '<xsl:value-of select="@Value" />'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>;
        ELSE
        UPDATE VCLib.T_HOLDER_PARAMETER
        SET Description = '<xsl:value-of select="@Description" />'
        ,ParameterValue = <xsl:choose>
          <xsl:when test="@Value">
            '<xsl:value-of select="@Value" />'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
        WHERE Holder = _id and ParameterName = '<xsl:value-of select="@Name" />';
        end if;
      </xsl:for-each>

		<xsl:if test="name(.) = 'custom_holder'">
			UPDATE VCLib.T_CUSTOM_HOLDER
			SET On_Load = '<xsl:value-of select="on_load" />'
			,On_Start = '<xsl:value-of select="on_start" />'
			,On_Stop = '<xsl:value-of select="on_stop" />'
			,On_Unload = '<xsl:value-of select="on_unload" />'
			,IsCachedDataHolder = '<xsl:value-of select="@IsCachedDataHolder" />'
			FROM VCLib.T_CUSTOM_HOLDER ch
			WHERE ch.HolderID = _id;


			<xsl:for-each select="holder_references/holder_reference">
				IF NOT EXISTS(SELECT * from VCLib.T_HOLDER_REFERENCE hr WHERE hr.CustomHolder = _id and hr.Holder = '<xsl:value-of select="@RefHolder" />')
				then
				INSERT INTO VCLib.T_HOLDER_REFERENCE (HolderReferenceID, CustomHolder, Holder)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@RefHolder" />';
				end if;
			</xsl:for-each>


			<xsl:for-each select="holder_handlers/handler[@Type = 0]">
				IF NOT EXISTS(SELECT * from VCLib.T_HOLDER_HANDLER hh where hh.CustomHolder = _id and hh.HandlerType = '<xsl:value-of select="@Type" />' and hh.ProcessName = '<xsl:value-of select="@Name" />')
				then
				INSERT INTO VCLib.T_HOLDER_HANDLER (HolderHandlerID, CustomHolder, HandlerType, Entities, ProcessName, Execute, Postexecute, Prepare)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="execute" />', '<xsl:value-of select="postexecute" />', '<xsl:value-of select="prepare" />';
				ELSE
				UPDATE VCLib.T_HOLDER_HANDLER
				SET HandlerType = '<xsl:value-of select="@Type" />'
				,ProcessName = '<xsl:value-of select="@Name" />'
				,Entities = '<xsl:value-of select="@ObjectType" />'
				,Prepare = '<xsl:value-of select="prepare" />'
				,Execute = '<xsl:value-of select="execute" />'
				,Postexecute = '<xsl:value-of select="postexecute" />'
				FROM VCLib.T_HOLDER_HANDLER hh
				WHERE hh.CustomHolder = _id and  hh.HandlerType = '<xsl:value-of select="@Type" />' and hh.ProcessName = '<xsl:value-of select="@Name" />';
				end if;
			</xsl:for-each>

			<xsl:for-each select="holder_handlers/handler[@Type = 1]">
				IF NOT EXISTS(SELECT * from VCLib.T_HOLDER_HANDLER hh where hh.CustomHolder = _id and hh.HandlerType = '<xsl:value-of select="@Type" />' and hh.ProcessName = '<xsl:value-of select="@Name" />')
				then
				INSERT INTO VCLib.T_HOLDER_HANDLER (HolderHandlerID, CustomHolder, HandlerType, Entities, ProcessName, Execute, Postexecute, PreExecute, Prepare, PrePostexecute, PrePrepare)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@Type" />', '<xsl:value-of select="@ObjectType" />', '<xsl:value-of select="@Name" />', '<xsl:value-of select="execute" />', '<xsl:value-of select="postexecute" />', '<xsl:value-of select="preexecute" />','<xsl:value-of select="prepare" />','<xsl:value-of select="prepostexecute" />','<xsl:value-of select="preprepare" />';
				ELSE
				UPDATE VCLib.T_HOLDER_HANDLER
				SET HandlerType = '<xsl:value-of select="@Type" />'
				,ProcessName = '<xsl:value-of select="@Name" />'
				,Entities = '<xsl:value-of select="@ObjectType" />'
				,PrePrepare = '<xsl:value-of select="preprepare" />'
				,Prepare = '<xsl:value-of select="prepare" />'
				,PreExecute = '<xsl:value-of select="preexecute" />'
				,Execute = '<xsl:value-of select="execute" />'
				,PrePostexecute = '<xsl:value-of select="prepostexecute" />'
				,Postexecute = '<xsl:value-of select="postexecute" />'
				FROM VCLib.T_HOLDER_HANDLER hh
				WHERE hh.CustomHolder = _id and  hh.HandlerType = '<xsl:value-of select="@Type" />' and hh.ProcessName = '<xsl:value-of select="@Name" />';
				end if;
			</xsl:for-each>

			<xsl:for-each select="safe_loop_threads/safe_loop_thread">
				IF NOT EXISTS(SELECT * from VCLib.T_SAFE_LOOP_THREAD slt where slt.CustomHolder = _id and slt.ThreadName = '<xsl:value-of select="@ThreadName" />')
				then
				INSERT INTO VCLib.T_SAFE_LOOP_THREAD (SafeLoopThreadID, CustomHolder, ThreadName, Interval, ThreadLexem)
				SELECT gen_random_uuid(), _id, '<xsl:value-of select="@ThreadName" />', '<xsl:value-of select="@Interval" />', '<xsl:value-of select="threadLexem" />';
				ELSE
				UPDATE VCLib.T_SAFE_LOOP_THREAD
				SET Interval = '<xsl:value-of select="@Interval" />'
				,ThreadLexem = '<xsl:value-of select="threadLexem" />'
				FROM VCLib.T_SAFE_LOOP_THREAD slt
				WHERE slt.CustomHolder = _id and slt.ThreadName = '<xsl:value-of select="@ThreadName" />';
				end if;
			</xsl:for-each>
		</xsl:if>
      END;
      end if;

      end;$$;
    </xsl:for-each>

    <xsl:for-each select ="users/user">
      -- USER

      do
      $$
      declare
      entid integer;
      usrid uuid;
      identId uuid;
      lognId uuid;
      type integer;
      _salt uuid;
      rc uuid;
      contid integer;
      divid uuid;
      begin
      if exists(select * from VCLib.T_DIVISIONS)
      then
      divid := (select DivisionID from VCLib.T_DIVISIONS order by TreeLevel limit 1);
      else
      begin
      divid := gen_random_uuid();
      insert into VCLib.T_DIVISIONS ( DivisionID, Name, FullName )
      values ( divid, 'Main', 'Main');
      end;
      end if;

      type := (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'USERS');
      _salt := gen_random_uuid();

      identId := (select i.IdentityID from VCLib.T_IDENTITY i join VCLib.T_LOGIN l on l.Identity = i.IdentityID where l.Login = '<xsl:value-of select="@Login" />' limit 1);

      if (identId is null)
      then
      begin
      identId := gen_random_uuid();
      lognId := gen_random_uuid();

      insert into VCLib.T_IDENTITY (IdentityID, IdentityName)
      values (identId, '<xsl:value-of select="@UserName" />');

      insert into VCLib.T_LOGIN (LoginID, Login, AuthenticationType, Password, Salt, Flags, Identity, Divisions, IsBlocked)
      values (lognId, '<xsl:value-of select="@Login" />', 0, encode(digest('<xsl:value-of select="@Password" />' || _salt::text, 'sha512'), 'base64'), _salt::text, 0, identId, divid, false);
      end;
      else
      begin

      update VCLib.T_IDENTITY
      set IdentityName = '<xsl:value-of select="@UserName" />'
      where IdentityID = identId;

      end;
      end if;

      usrid := (select UserID from VCLib.T_USERS where Identity = identId limit 1);

      if (usrid is null)
      then
      begin
      usrid := gen_random_uuid();
      insert into VCLib.T_USERS (UserID, Flags, DivisionID, Identity, type_)
      values (usrid, 1, divid, identId, @type);
      insert into <xsl:value-of select="../../@Name" />.T_USERS (UserID)
      values (usrid);
      end;
      end if;

      update VCLib.T_users
      set Flags = case when <xsl:value-of select="@Active" /> = 1 then Flags | 1 else Flags &amp; ~1 end
      where userId = usrid;

      <xsl:if test="@Remote">
      update VCLib.T_users
      set Flags = case when <xsl:value-of select="@Remote" /> = 1 then Flags | 4 else Flags &amp; ~4 end
      where userId = usrid;
      </xsl:if>

      <xsl:for-each select="UserSearches">
        -- user searches
        <xsl:for-each select="UserSearch">
          set rc = gen_random_uuid();
          EntID := (select EntityID from VCLib.T_ENTITIES WHERE EntName='<xsl:value-of select="@Entity" />');
          INSERT INTO VCLib.T_USER_SEARCHES(SearchID,EntityID, UserID, SearchName, FilterValue, SimpleFilter)
          VALUES(rc, EntID, usrId, '<xsl:value-of select="@SearchName" />', '<xsl:value-of select="." />', <xsl:value-of select="@SimpleFilter" />)
        </xsl:for-each>
      </xsl:for-each>
      end;
      $$;
    </xsl:for-each>

    <xsl:for-each select ="roles/role">
		-- ROLE
		create or replace function getLoadOrder (rcmid uuid) returns integer as $$
		declare
		lord integer;
		begin
		lord :=(select t.loadorder from
		(select roleclientmoduleid, ROW_NUMBER() OVER (PARTITION BY rcm.ClientModuleID ORDER BY rcm.LoadOrder, r.RoleDesc)-1 as loadorder
		FROM VCLib.T_ROLE_CLIENT_MODULES rcm
		join VCLib.T_ROLES r on r.RoleID = rcm.RoleID)t
		where t.roleclientmoduleid = rcmid);
		return lord;
		end;$$ LANGUAGE plpgsql;

		do $$
		declare
		rc uuid;
		enmtypeid integer;
		enmid integer;
		role uuid;
		entrid uuid;
		repRoleId uuid;
		rolId uuid;
		rights integer;
		usrId uuid;
		v_client_module uuid;
		v_role_client_module uuid;
		v_role uuid;
		v_feature_entity_index integer;
		v_feature_id uuid;
		v_feature_enabled bool = false;
		begin
		IF NOT EXISTS (SELECT * from VCLib.T_ROLES WHERE RoleName = '<xsl:value-of select="@RoleName" />' )
      then
      begin
      enmtypeid := (SELECT EnumTypeID FROM VCLib.T_ENUM_TYPES WHERE Name = 'Departament');
      enmid := (SELECT min(EnumID) FROM VCLib.T_ENUMS WHERE Name = '<xsl:value-of select="@Departament" />' AND EnumTypeID = EnmTypeID);
      rc := gen_random_uuid();
      INSERT INTO VCLib.T_ROLES (RoleId,RoleName, RoleDesc, Departament, DivisionsLevel)
      VALUES (rc,'<xsl:value-of select="@RoleName" />','<xsl:value-of select="@RoleDesc" />', EnmID,
      <xsl:choose>
        <xsl:when test="@DivisionsLevel">
          <xsl:value-of select="@DivisionsLevel" />
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>);
      end;
      end if;

      <xsl:for-each select="users/user">
        --USER IN ROLE
        rc :=null;
        usrId := (select u.UserID from VCLib.T_USERS u join VCLib.T_IDENTITY i on u.Identity = i.IdentityID join VCLib.T_LOGIN l on l.Identity = i.IdentityID where l.Login = '<xsl:value-of select="@Login" />' limit 1);

        if (usrId is not null)
        then
        begin
        IF NOT EXISTS ( SELECT * FROM VCLib.T_USERS_ROLES
        WHERE UserId=(SELECT UserId FROM VCLib.T_USERS WHERE UserID = usrId)
        AND RoleId=(SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />') )
        then
        begin
        rc := gen_random_uuid();
        INSERT INTO VCLib.T_USERS_ROLES (UserRoleId,UserId,RoleId)
        (
        SELECT
        rc,
        (SELECT UserID FROM VCLib.T_USERS WHERE UserID = usrId),
        (SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />')
        );
        end;
        end if;
        end;
        end if;
      </xsl:for-each>

	    RAISE NOTICE 'ВКЛЮЧЕНИЕ КЛИЕНТСКИХ МОДУЛЕЙ И ИХ ФИЧ ДЛЯ РОЛЕЙ. НАЧАЛО.';
        <xsl:for-each select="client_modules/client_module">
            <xsl:variable name="module_name">
	            <xsl:value-of select="@Schema" />.<xsl:value-of select="@ModuleType" />
            </xsl:variable>
            <xsl:variable name="role_name">
	            <xsl:value-of select="ancestor::role/@RoleName" />
            </xsl:variable>
        RAISE NOTICE 'ВКЛЮЧЕНИЕ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. НАЧАЛО.';
        SELECT ClientModuleID FROM VCLib.T_CLIENT_MODULES WHERE ModuleType = '<xsl:value-of select="@ModuleType" />' and schema = '<xsl:value-of select="@Schema" />' INTO v_client_module;
		SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="$role_name" />' INTO v_role;
		SELECT RoleClientModuleID from VCLib.T_ROLE_CLIENT_MODULES where ClientModuleID = v_client_module and RoleID = v_role INTO v_role_client_module;
		
		IF (v_client_module is not null) THEN
        BEGIN
            IF (v_role_client_module is null) THEN
                BEGIN
					RAISE NOTICE 'КЛИЕНТСКИЙ МОДУЛЬ <xsl:value-of select="$module_name" /> БУДЕТ ВКЛЮЧЕН ДЛЯ РОЛИ <xsl:value-of select="$role_name" />';
						
					v_role_client_module := gen_random_uuid();
						
                    INSERT INTO VCLib.T_ROLE_CLIENT_MODULES (RoleClientModuleID, ClientModuleID,RoleId, loadorder)
                    SELECT
                     v_role_client_module
                    , v_client_module
                    , v_role
					, 0;
                END;
            END IF;
			
            <xsl:if test="client_module_features/node()">
			RAISE NOTICE 'ВКЛЮЧАЕМ ФИЧИ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. НАЧАЛО';
			<xsl:for-each select="client_module_features/client_module_feature">
			--ВКЛЮЧЕНИЕ ФИЧИ. НАЧАЛО
			--ПРОВЕРКА СУЩЕСТВУЕТ ЛИ ФИЧА
			select EntityId from vclib.T_ENTITIES where EntName = '<xsl:value-of select="@EntityIndexName" />' and schema = '<xsl:value-of select="@EntityIndexSchemaName" />' limit 1 INTO v_feature_entity_index;
			select ClientModuleFeatureID from vclib.T_CLIENT_MODULE_FEATURE where Name = '<xsl:value-of select="@Name" />' and EntityIndex = v_feature_entity_index and ClientModules = v_client_module limit 1 INTO v_feature_id; 
			--ПРОВЕРКА ВКЛЮЧЕНА ЛИ ФИЧА
			v_feature_enabled := false;
			IF EXISTS(select 1 from vclib.T_BASE_FEATURE_SETTINGS where ClientModuleFeature = v_feature_id and RoleClientModules = v_role_client_module) THEN
			    v_feature_enabled := true;
			END IF;
			
			IF (v_feature_id is not null and v_feature_enabled = false) THEN
			BEGIN
			    RAISE NOTICE 'ВКЛЮЧАЕМ ФИЧУ <xsl:value-of select="@Name" /> В КЛИЕНТСКОМ МОДУЛЕ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />';
			    INSERT INTO VCLib.T_BASE_FEATURE_SETTINGS
			    (BaseFeatureSettingsID
			    ,ClientModuleFeature
			    ,RoleClientModules
			    ,type_)
			    VALUES
			    (gen_random_uuid()
			    ,v_feature_id
			    ,v_role_client_module
			    ,v_feature_entity_index);
			END;
			END IF;
			--ВКЛЮЧЕНИЕ ФИЧИ. КОНЕЦ
			</xsl:for-each>
			RAISE NOTICE 'ВКЛЮЧАЕМ ФИЧИ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. КОНЕЦ';
            </xsl:if>
        END;
		END IF;
		RAISE NOTICE 'ВКЛЮЧЕНИЕ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. КОНЕЦ.';
        </xsl:for-each>
		RAISE NOTICE 'ВКЛЮЧЕНИЕ КЛИЕНТСКИХ МОДУЛЕЙ И ИХ ФИЧ ДЛЯ РОЛЕЙ. КОНЕЦ.';

		<xsl:for-each select="access_entries/access_entry">
        --roles - access_entries
        rc := null;
        role:= (select RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />');
        entrid := '<xsl:value-of select="@EntryID" />';
        IF NOT EXISTS ( SELECT * FROM VCLib.T_BAE_ROLES where entryid = entrid AND RoleId=role )
        then
        begin
        rc := gen_random_uuid();
        INSERT INTO VCLib.T_BAE_ROLES (EntryRoleID, RoleID,EntryID) values (rc, role, entrid);
        end;
        end if;
      </xsl:for-each>

      <xsl:for-each select="access_reports/access_report">
        --roles - access reports
        IF EXISTS (SELECT ReportId FROM VCLib.T_Report WHERE ReportId='<xsl:value-of select="@ReportID"/>')
        then
        begin
        reproleid := gen_random_uuid();
        rolid := (select RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />');
        rights := (select EnumID FROM VCLib.T_ENUMS WHERE Alias='<xsl:value-of select="@Rights" />' and EnumTypeID = 7);
        INSERT INTO VCLib.T_ReportRole (RepRoleID,ReportID,RoleID,ReportRights)
        VALUES (repRoleID,'<xsl:value-of select="@ReportID"/>', rolId, rights);
        end;
        end if;
      </xsl:for-each>

      update VCLib.T_role_client_modules
      set loadorder = getloadorder(roleclientmoduleid);


      end;$$;
    </xsl:for-each>

  </xsl:template>

  <!-- ClientModuleForms -->
  <xsl:template name="ClientModuleForms">
        INSERT INTO VCLib.T_CLIENT_MODULE_FORM (ClientModuleFormID, ClientModules, Entities, Context, InitializationLexem, Name, Schema)
        SELECT gen_random_uuid()
              ,id
              ,(select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@Entities" />')
              ,<xsl:choose><xsl:when test="@Context != ''">'<xsl:value-of select="@Context" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
              ,<xsl:choose><xsl:when test="@InitializationLexem != ''">'<xsl:value-of select="@InitializationLexem" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
              ,'<xsl:value-of select="@Name" />'
              ,'<xsl:value-of select="@Schema" />'
        where not exists
          (
              select *
              from VCLib.T_CLIENT_MODULE_FORM cmf
              where cmf.ClientModules = id and cmf.Entities = (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@Entities" />')
              <xsl:choose><xsl:when test="@Context != ''"> and cmf.Context = '<xsl:value-of select="@Context" />'</xsl:when></xsl:choose>
          );
  </xsl:template>

	<xsl:template match="root/UserContexts">
		<xsl:for-each select="UserContext">
			do $$
			DECLARE _EntityID INT;
			_CurrentContext varchar(255);
			_ViewName varchar(255);
			_CurrentUserID uuid;
			_ActiveContext boolean;
			_ShowGrid boolean;
			_Scale int;
			_contId uuid;
			_ReadOnly boolean;
			_SortingLexem text;
			_CustomSortingEnabled boolean;
			_UserContextType int;

			begin
			create temp table _ucids(id uuid);
			_CurrentContext :=<xsl:choose>
				<xsl:when test="@Context">
					'<xsl:value-of select="@Context" />'
				</xsl:when>
				<xsl:otherwise>NULL</xsl:otherwise>
			</xsl:choose>;
			_CurrentUserID :=<xsl:choose>
				<xsl:when test="@UserID">
					'<xsl:value-of select="@UserID" />'
				</xsl:when>
				<xsl:otherwise>NULL</xsl:otherwise>
			</xsl:choose>;
			_ShowGrid := <xsl:choose>
				<xsl:when test="@ShowGrid">
					'<xsl:value-of select="@ShowGrid"/>'
				</xsl:when>
				<xsl:otherwise>0</xsl:otherwise>
			</xsl:choose>;
			_ViewName := <xsl:choose>
				<xsl:when test="@ViewName">
					'<xsl:value-of select="@ViewName"/>'
				</xsl:when>
				<xsl:otherwise>'По умолчанию'</xsl:otherwise>
			</xsl:choose>;
			_Scale := <xsl:choose>
				<xsl:when test="@Scale">
					'<xsl:value-of select="@Scale"/>'
				</xsl:when>
				<xsl:otherwise>100</xsl:otherwise>
			</xsl:choose>;
			_ActiveContext := <xsl:choose>
				<xsl:when test="@ActiveContext">
					'<xsl:value-of select="@ActiveContext"/>'
				</xsl:when>
				<xsl:otherwise>0</xsl:otherwise>
			</xsl:choose>;
			_ReadOnly := <xsl:choose>
				<xsl:when test="@ReadOnly">
					'<xsl:value-of select="@ReadOnly"/>'
				</xsl:when>
				<xsl:otherwise>0</xsl:otherwise>
			</xsl:choose>;
			_SortingLexem := '<xsl:value-of select="@SortingLexem" />';
			_CustomSortingEnabled := <xsl:value-of select="@CustomSortingEnabled" />;
			_UserContextType := <xsl:choose>
				<xsl:when test="@UserContextType">
					<xsl:value-of select="@UserContextType"/>
				</xsl:when>
				<xsl:otherwise>0</xsl:otherwise>
			</xsl:choose>;

			_EntityID := (SELECT e.EntityID from VCLib.T_ENTITIES e WHERE e."schema" || '.' || e.EntName ='<xsl:value-of select="@Entity" />');

			-- удаляем и сбрасываем ActiveContext
			if _CurrentUserID is NULL
			then
			if _CurrentContext is null
			then
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context is null AND ViewName = _ViewName;
			else
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context = _CurrentContext AND ViewName = _ViewName;
			end if;

			DELETE FROM VCLib.T_USER_COLUMNS WHERE UserContextID in (select id from _ucids);
			DELETE FROM VCLib.T_USER_CONTEXTS WHERE UserContextID in (select id from _ucids);

			if _ActiveContext = '1'
			then
			if _CurrentContext is null
			then
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context is null;
			else
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context = _CurrentContext;
			end if;
			end if;
			elsif _CurrentUserID = ''
			then
			if _CurrentContext is null
			then
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context is null AND ViewName = _ViewName AND UserID is null;
			else
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context = _CurrentContext AND ViewName = _ViewName AND UserID is null;
			end if;

			DELETE FROM VCLib.T_USER_COLUMNS WHERE UserContextID in (select id from _ucids);
			DELETE FROM VCLib.T_USER_CONTEXTS WHERE UserContextID in (select id from _ucids);

			if _ActiveContext = '1'
			then
			if _CurrentContext is null
			then
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context is null;
			else
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context = _CurrentContext;
			end if;
			end if;
			else
			if _CurrentContext is null
			then
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context is null AND ViewName = _ViewName AND UserID = _CurrentUserID;
			else
			insert into _ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = _EntityID AND Context = _CurrentContext AND ViewName = _ViewName AND UserID = _CurrentUserID;
			end if;

			DELETE FROM VCLib.T_USER_COLUMNS WHERE UserContextID in (select id from _ucids);
			DELETE FROM VCLib.T_USER_CONTEXTS WHERE UserContextID in (select id from _ucids);

			if _ActiveContext = '1'
			then
			if _CurrentContext is null
			then
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context is null AND UserID = _CurrentUserID;
			else
			UPDATE VCLib.T_USER_CONTEXTS SET ActiveContext = '0' WHERE EntityID = _EntityID AND Context = _CurrentContext AND UserID = _CurrentUserID;
			end if;
			end if;
			end if;

			-- добавляем новый T_USER_CONTEXTS
			_contId := uuid_generate_v4();
			INSERT INTO VCLib.T_USER_CONTEXTS(UserContextID, EntityID, UserID, Context, ShowGrid, ScalePercent, ViewName, ActiveContext, ReadOnly, SortingLexem, CustomSortingEnabled, UserContextType)
			VALUES (_contId, _EntityID, _CurrentUserID, _CurrentContext, _ShowGrid, _Scale, _ViewName, _ActiveContext, _ReadOnly, _SortingLexem, _CustomSortingEnabled, _UserContextType);

			-- добавляем новые T_USER_COLUMNS

			<xsl:for-each select="UserColumns/UserColumn">

				INSERT INTO VCLib.T_USER_COLUMNS(UserColsId, AttributePath, DisplayName, ColumnIndex, ColumnWidth, Visible, SortingIndex, Sorting, UserContextID)
				VALUES(uuid_generate_v4(), '<xsl:value-of select="@AttributePath" />',
				'<xsl:value-of select="@DisplayName" />', <xsl:value-of select="@ColumnIndex" />,
				<xsl:value-of select="@ColumnWidth" />, '1',
				<xsl:choose>
					<xsl:when test="@SortingIndex">
						'<xsl:value-of select="@SortingIndex"/>'
					</xsl:when>
					<xsl:otherwise>0</xsl:otherwise>
				</xsl:choose>, <xsl:value-of select="@Sorting" />, _contId);
			</xsl:for-each>
			end;
			$$;
		</xsl:for-each>

	</xsl:template>
  <xsl:template match="root/CommonUserColumns">
    <xsl:for-each select="UserColumn">
      -- user columns
      do $$
      declare
      entid integer;
      rc uuid;
      _context varchar(255);
      contid uuid;
      _showgrid boolean = <xsl:choose>
        <xsl:when test="@ShowGrid">
          '<xsl:value-of select="@ShowGrid"/>'
        </xsl:when>
        <xsl:otherwise>'0'</xsl:otherwise>
      </xsl:choose>;
      _scale integer = <xsl:choose>
        <xsl:when test="@Scale">
          '<xsl:value-of select="@Scale"/>'
        </xsl:when>
        <xsl:otherwise>'100'</xsl:otherwise>
      </xsl:choose>;
      _viewname varchar(255) = <xsl:choose>
        <xsl:when test="@ViewName">
          '<xsl:value-of select="@ViewName"/>'
        </xsl:when>
        <xsl:otherwise>'По умолчанию'</xsl:otherwise>
      </xsl:choose>;
      _activecontext boolean = <xsl:choose>
        <xsl:when test="@ActiveContext">
          '<xsl:value-of select="@ActiveContext"/>'
        </xsl:when>
        <xsl:otherwise>'0'</xsl:otherwise>
      </xsl:choose>;

      _readonly boolean = <xsl:choose>
        <xsl:when test="@ReadOnly">
          '<xsl:value-of select="@ReadOnly"/>'
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
      _sortinglexem varchar(max) = '<xsl:value-of select="@SortingLexem" />'
      _customsortingenabled boolean = <xsl:value-of select="@CustomSortingEnabled" />
      _usercontexttype := <xsl:choose>
              <xsl:when test="@UserContextType">
                      <xsl:value-of select="@UserContextType"/>
              </xsl:when>
              <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>;

      begin
      entid := (select EntityID from VCLib.T_ENTITIES WHERE Schema || '.' || EntName='<xsl:value-of select="@Entity" />');
      _context := <xsl:choose>
        <xsl:when test="@UserContext">
          '<xsl:value-of select="@UserContext" />'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      if(_context is null)
      then
      contid :=(SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE (Context is NULL AND EntityID = EntID AND UserID is NULL AND ViewName = _viewname));
      else
      contid :=(SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE (Context = _context AND EntityID = EntID AND UserID is NULL AND ViewName = _viewname));
      end if;
      if(contid is null)
      then
      begin
      contid := gen_random_uuid();
      INSERT INTO VCLib.T_USER_CONTEXTS (UserContextID, EntityID, UserID, Context, showgrid, ScalePercent, ViewName, ActiveContext, ReadOnly, SortingLexem, CustomSortingEnabled, UserContextType)
      VALUES (contId, EntID, null, _context, _showgrid, _scale, _viewname, _activecontext, _readonly, _sortinglexem, _customsortingenabled, _usercontexttype);
      end;
      end if;
      rc := gen_random_uuid();
      INSERT INTO VCLib.T_USER_COLUMNS(UserColsId, AttributePath, DisplayName, ColumnIndex, ColumnWidth, Visible, SortingIndex, Sorting, UserContextID)
      VALUES(rc, '<xsl:value-of select="@AttributePath" />', '<xsl:value-of select="@DisplayName" />', <xsl:value-of select="@ColumnIndex" />, <xsl:value-of select="@ColumnWidth" />, '1',
      <xsl:choose>
        <xsl:when test="@SortingIndex">
          <xsl:value-of select="@SortingIndex"/>
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>, <xsl:value-of select="@Sorting" />, contId);
      end;$$;
    </xsl:for-each>
  </xsl:template>

  <xsl:template match="root/CommonUserSearches">
    -- user searches
    do $$
    declare
    __entityid integer;
    __rc uuid;
    begin
    <xsl:for-each select="UserSearch">
      __rc = gen_random_uuid();
      SELECT __EntityID := (select EntityID from VCLib.T_ENTITIES WHERE EntName='<xsl:value-of select="@Entity" />');
      INSERT INTO VCLib.T_USER_SEARCHES(SearchId,EntityID, UserID, SearchName, FilterValue, SimpleFilter)
      VALUES(__rc, __EntityID, null, '<xsl:value-of select="@SearchName" />', '<xsl:value-of select="." />', <xsl:value-of select="@SimpleFilter" />);
    </xsl:for-each>
    end;$$;
  </xsl:template>

  <xsl:template match="EnumTypes">
    <xsl:apply-templates select="EnumType" />
    do $$
    declare
    fname varchar(255) = 'PrimaryKey';
    isexist integer;
    begin
    isexist := (select id from T_ENTITY_IDS where EntName = 'ENUM_TYPES' and FieldName = FName);
    if(isexist is null)
    then
    insert into T_ENTITY_IDS (id, EntName, FieldName) values(<xsl:value-of select="@next-enum-type-id" />, 'ENUM_TYPES', FName);
    else
    UPDATE T_ENTITY_IDS SET id=<xsl:value-of select="@next-enum-type-id" /> WHERE EntName='ENUM_TYPES';
    end if;
    isexist := (select id from T_ENTITY_IDS where EntName = 'ENUMS' and FieldName = 'PrimaryKey');
    if(isexist is null)
    then
    insert into T_ENTITY_IDS (id, EntName, FieldName) values(<xsl:value-of select="@next-enum-id" />, 'ENUMS', FName);
    else
    UPDATE T_ENTITY_IDS SET id=<xsl:value-of select="@next-enum-id" /> WHERE EntName='ENUMS';
    end if;
    end;$$;
  </xsl:template>

  <xsl:template match="EnumType">
    -- EnumType
    do $$
    declare
    _id integer;
    begin
    <xsl:variable name="EnumTypeId" select="@id" />
    _id:=(SELECT EnumTypeId FROM VCLib.T_ENUM_TYPES WHERE EnumTypeId = <xsl:value-of select="$EnumTypeId" />);
    if(_id is not null)
    then
    begin
    UPDATE VCLib.T_ENUM_TYPES SET
    Name = '<xsl:value-of select="@Name" />', Description = '<xsl:value-of select="@Description" />'
    WHERE EnumTypeId = <xsl:value-of select="$EnumTypeId" />;
    end;
    else
    begin
    INSERT INTO VCLib.T_ENUM_TYPES (EnumTypeId,Name, Description)
    VALUES(<xsl:value-of select="$EnumTypeId" />,'<xsl:value-of select="@Name" />', '<xsl:value-of select="@Description" />');
    end;
    end if;
    <xsl:for-each select="Enum">
      _id := (SELECT EnumId FROM VCLib.T_ENUMS WHERE EnumId = <xsl:value-of select="@id" />);
      if (_id is not null)
      then
      UPDATE VCLib.T_ENUMS SET
      EnumTypeID = <xsl:value-of select="$EnumTypeId" />,
      Code = <xsl:value-of select="@Code" />,
      Name = '<xsl:value-of select="@Name" />',
      ShortName = '<xsl:value-of select="@ShortName" />',
      Active = '<xsl:value-of select="@Active" />',
      Alias = '<xsl:value-of select="@Alias" />'
      WHERE EnumId = <xsl:value-of select="@id" />;
      else
      INSERT INTO VCLib.T_ENUMS (EnumId,EnumTypeID, Code, Name, ShortName, Active, Alias)
      VALUES(
      <xsl:value-of select="@id" />,
      <xsl:value-of select="$EnumTypeId" />,
      <xsl:value-of select="@Code" />,
      '<xsl:value-of select="@Name" />',
      '<xsl:value-of select="@ShortName" />',
      '<xsl:value-of select="@Active" />',
      '<xsl:value-of select="@Alias" />');
      end if;
    </xsl:for-each>
    end;$$;
  </xsl:template>

  <xsl:template match="root/process_access_entries/process_access_entry">
    -- PROCESS_ACCESS_ENTRY
    do $$
    declare
    _type integer;
    _id uuid;
    _entryid uuid = '<xsl:value-of select="@EntryID" />';
    begin
    _type :=(SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'PROCESS_ACCESS_ENTRY');
    _id:=(SELECT EntryID FROM VCLib.T_BASE_ACCESS_ENTRY WHERE EntryID = _entryid);
    if(_id is not null)
    then
    UPDATE VCLib.T_BASE_ACCESS_ENTRY SET
    EntryDescription = '<xsl:value-of select="@EntryDescription" />',
    type_ = _type WHERE EntryID = _entryid;
    else
    INSERT INTO VCLib.T_BASE_ACCESS_ENTRY (EntryID, EntryDescription, type_)
    VALUES (_entryid, '<xsl:value-of select="@EntryDescription" />', _type);
    end if;

    _id := (SELECT EntryID FROM VCLib.T_PROCESS_ACCESS_ENTRY WHERE EntryID = _entryid);
    if(_id is not null)
    then
    UPDATE VCLib.T_PROCESS_ACCESS_ENTRY SET
    LexemContent = '<xsl:value-of select="@LexemContent" />',
    ProcessName = '<xsl:value-of select="@ProcessName" />',
    Priority = <xsl:value-of select="@Priority" />,
    Flags = <xsl:value-of select="@Flags" />,
    EntityID = <xsl:value-of select="@EntityID" />,
    CheckInputObject = '<xsl:value-of select="@CheckInputObject" />',
    CheckDBObject = '<xsl:value-of select="@CheckDBObject" />'
    WHERE EntryID = _entryid;
    else
    INSERT INTO VCLib.T_PROCESS_ACCESS_ENTRY (EntryID, LexemContent, ProcessName, Priority, Flags, EntityID, CheckInputObject, CheckDBObject)
    VALUES (_entryid, '<xsl:value-of select="@LexemContent" />', '<xsl:value-of select="@ProcessName" />', <xsl:value-of select="@Priority" />,
    <xsl:value-of select="@Flags" />, <xsl:value-of select="@EntityID" />, '<xsl:value-of select="@CheckInputObject" />', '<xsl:value-of select="@CheckDBObject" />');
    end if;
    end;$$;
  </xsl:template>

  <xsl:template match="root/entity_access_entries/entity_access_entry">
    -- ENTITY_ACCESS_ENTRY
    do $$
    declare
    _type integer;
    _id uuid;
    _entryid uuid = '<xsl:value-of select="@EntryID" />';
    begin
    _type:=(SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'ENTITY_ACCESS_ENTRY');
    _id:=(SELECT EntryID FROM VCLib.T_BASE_ACCESS_ENTRY WHERE EntryID = _entryid);
    if(_id is not null)
    then
    UPDATE VCLib.T_BASE_ACCESS_ENTRY SET
    EntryDescription = '<xsl:value-of select="@EntryDescription" />',
    type_ = _type WHERE EntryID = _entryid;
    else
    INSERT INTO VCLib.T_BASE_ACCESS_ENTRY (EntryID, EntryDescription, type_)
    VALUES (_entryid, '<xsl:value-of select="@EntryDescription" />', _type);
    end if;

    _id:=(SELECT EntryID FROM VCLib.T_ENTITY_ACCESS_ENTRY WHERE EntryID = _entryid);
    if(_id is not null)
    then
    UPDATE VCLib.T_ENTITY_ACCESS_ENTRY SET
    AccessType = <xsl:value-of select="@Access" />,
    LexemContent = '<xsl:value-of select="@LexemContent" />',
    EntityID = <xsl:value-of select="@EntityID" />
    WHERE EntryID = _entryid;
    else
    INSERT INTO VCLib.T_ENTITY_ACCESS_ENTRY (EntryID, AccessType, LexemContent, EntityID)
    VALUES (_entryid, <xsl:value-of select="@Access" />, '<xsl:value-of select="@LexemContent" />', <xsl:value-of select="@EntityID" />);
    end if;
    end;$$;
  </xsl:template>

  <xsl:template match="root/entity_attribute_access_entries/entity_attribute_access_entries">
    -- ENTITY_ATTRIBUTE_ACCESS_ENTRY
    do $$
    declare
    __type integer;
    __entryid uuid;
    begin
    __entryid := '<xsl:value-of select="@EntryID" />';
    __type := (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'ENTITY_ATTRIBUTE_ACCESS_ENTRY');
    INSERT INTO VCLib.T_BASE_ACCESS_ENTRY (EntryID, EntryDescription, type_)
    VALUES (__entryid, '<xsl:value-of select="@EntryDescription" />', __type);
    INSERT INTO VCLib.T_ENT_ATTR_ACCESS_ENTRY (EntryID, Attributes, AccessType, LexemContent, EntityID)
    VALUES (__entryid, '<xsl:value-of select="@Attributes" />', <xsl:value-of select="@Access" />, '<xsl:value-of select="@LexemContent" />', <xsl:value-of select="@EntityID" />);
    end;$$;
  </xsl:template>

  <xsl:template match="root/enum_access_entries/enum_access_entry">
    -- ENUM_ACCESS_ENTRY
    do $$
    declare
    __type integer;
    __entryid uuid;
    begin
    entryid := gen_random_uuid();
    type := (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'ENUM_ACCESS_ENTRY');
    INSERT INTO VCLib.T_BASE_ACCESS_ENTRY (EntryID, EntryDescription, type_)
    VALUES (__entryid, '<xsl:value-of select="@EntryDescription" />', __type);
    INSERT INTO VCLib.T_ENUM_ACCESS_ENTRY (EntryID, Enabled, EnumTypeID)
    VALUES (__entryid, <xsl:value-of select="@Enabled" />, <xsl:value-of select="@EnumTypeID" />);
    end;$$;
  </xsl:template>

  <xsl:template match="root/reportformats/reportformat">
    -- ReportFileFormat
    do $$
      declare
      _id uuid;
    begin
      _id:=(select RepFmtId FROM VCLib.T_ReportFileFormat WHERE Name = '<xsl:value-of select="@FormatName"/>');
      if(_id is not null)
      then
        UPDATE VCLib.T_ReportFileFormat SET
        Name = '<xsl:value-of select="@FormatName"/>',
        HandlerName = '<xsl:value-of select="@HandlerName"/>'
        WHERE RepFmtId = _ID;
      else
      begin
        _id = <xsl:choose>
      <xsl:when test="@id != ''">
        <xsl:text>'</xsl:text>
        <xsl:value-of select="@id"/>
        <xsl:text>'::uuid;</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>gen_random_uuid();</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
        INSERT INTO VCLib.T_ReportFileFormat (RepFmtId, Name, HandlerName)
        VALUES(_ID, '<xsl:value-of select="@FormatName"/>', '<xsl:value-of select="@HandlerName"/>');
      end;
      end if;
    end;$$;
  </xsl:template>

  <xsl:template match="root/Reports">
    <xsl:for-each select="Report">
      -- Report
      do $$
      declare
      _id uuid;
      _repName varchar(100);
      _groupID uuid;
      _entid integer;
      _reploaddatatype integer;
      _repformtypetable integer;
      _repformattype uuid;
      _type integer;
      _existid uuid;
      begin
      _id := '<xsl:value-of select="@ID"/>';
      _repname := '<xsl:value-of select="@Name"/>';
      _reploaddatatype := (select EnumID FROM VCLib.T_ENUMS
      WHERE EnumTypeID = (select EnumTypeID from VCLib.T_ENUM_TYPES where Name='ReportLoadDataType')
      AND Alias='<xsl:value-of select="@ReportLoadDataType"/>');
      _repformtypetable := (select EnumID FROM VCLib.T_ENUMS
      WHERE EnumTypeID = (select EnumTypeID from VCLib.T_ENUM_TYPES where Name='ReportFormType')
      AND Alias='<xsl:value-of select="@ReportFormType"/>');
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="@Entity"/>');
      _type := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='Report');

      _existid := (select ReportID FROM VCLib.T_BaseReport WHERE ReportID = _ID);
      if(_existid is not null)
      then
      UPDATE VCLib.T_BaseReport SET
      RepName = _repName,
      RepDesc= <xsl:choose>
        <xsl:when test="@Desc">
          '<xsl:value-of select="@Desc"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      Active = <xsl:choose>
        <xsl:when test="@Active">
          '<xsl:value-of select="@Active"/>'
        </xsl:when>
        <xsl:otherwise>'1'</xsl:otherwise>
      </xsl:choose>,
      type_ = _type
      WHERE ReportID = _ExistID;
      else
      INSERT INTO VCLib.T_BaseReport (ReportID, RepName, RepDesc, Active, type_)
      VALUES (_ID, _repName, <xsl:choose>
        <xsl:when test="@Desc">
          '<xsl:value-of select="@Desc"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>, <xsl:choose>
        <xsl:when test="@Active">
          '<xsl:value-of select="@Active"/>'
        </xsl:when>
        <xsl:otherwise>'1'</xsl:otherwise>
      </xsl:choose>, _type);
      end if;


      _existid := (select ReportID FROM VCLib.T_Report WHERE ReportID = _ID);
      if(existid is not null)
      then

      UPDATE VCLib.T_Report SET
      ForcedFetchAttrs = <xsl:choose>
        <xsl:when test="string(ForcedFetchAttrs)">
          '<xsl:value-of select="ForcedFetchAttrs"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      FilterValue = <xsl:choose>
        <xsl:when test="string(FilterValue)">
          '<xsl:value-of select="FilterValue"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      ReportFormType = _repFormTypeTable,
      ReportLoadDataType = _repLoadDataType,
      EntityID = _entID,
      Sorting = <xsl:choose>
        <xsl:when test="string(Sorting)">
          '<xsl:value-of select="Sorting"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      Descending = <xsl:choose>
        <xsl:when test="string(Descending)">
          '<xsl:value-of select="Descending"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      ScriptName = <xsl:choose>
        <xsl:when test="string(ScriptName)">
          '<xsl:value-of select="ScriptName"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      WHERE ReportID = _ExistID;
      else
      INSERT INTO VCLib.T_Report (ReportID,ForcedFetchAttrs,FilterValue,ReportFormType,ReportLoadDataType,EntityID,Sorting,Descending,ScriptName)
      VALUES (_ID, <xsl:choose>
        <xsl:when test="string(ForcedFetchAttrs)">
          '<xsl:value-of select="ForcedFetchAttrs"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>, <xsl:choose>
        <xsl:when test="string(FilterValue)">
          '<xsl:value-of select="FilterValue"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>, _repFormTypeTable, _repLoadDataType, _entID,<xsl:choose>
        <xsl:when test="string(Sorting)">
          '<xsl:value-of select="Sorting"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>, <xsl:choose>
        <xsl:when test="string(Descending)">
          '<xsl:value-of select="Descending"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>, <xsl:choose>
        <xsl:when test="string(ScriptName)">
          '<xsl:value-of select="ScriptName"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>);
      end if;

      --ReportFileGroup
      <xsl:for-each select="ReportFileGroup">
        _groupid := gen_random_uuid();
        _existid := (select GroupID FROM VCLib.T_ReportFileGroup WHERE ReportID = @ID AND Name = <xsl:choose>
          <xsl:when test="@Name">
            '<xsl:value-of select="@Name"/>'
          </xsl:when>
          <xsl:otherwise>@repName</xsl:otherwise>
        </xsl:choose>);

        if(_existid is not null)
        then
        UPDATE VCLib.T_ReportFileGroup SET
        IsDefault = <xsl:choose>
          <xsl:when test="@IsDefault">
            '<xsl:value-of select="@IsDefault"/>'
          </xsl:when>
          <xsl:otherwise>'1'</xsl:otherwise>
        </xsl:choose>
        WHERE GroupID = _ExistID;
        else
        INSERT INTO VCLib.T_ReportFileGroup(GroupID,ReportID,Name,IsDefault)
        VALUES (_groupID, _ID, <xsl:choose>
          <xsl:when test="@Name">
            '<xsl:value-of select="@Name"/>'
          </xsl:when>
          <xsl:otherwise>_repName</xsl:otherwise>
        </xsl:choose>, <xsl:choose>
          <xsl:when test="@IsDefault">
            '<xsl:value-of select="@IsDefault"/>'
          </xsl:when>
          <xsl:otherwise>1</xsl:otherwise>
        </xsl:choose>);
        end if;

        --Files
        <xsl:for-each select="File">
          UPDATE VCLib.T_FILES SET
          type_ = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName='ReportFile')
          WHERE FileID = '<xsl:value-of select="@ID"/>';
          _repformattype := (select RepFmtID FROM VCLib.T_ReportFileFormat
          WHERE Name = '<xsl:value-of select="@Format"/>');
          _existid := (select FileID FROM VCLib.T_ReportFile WHERE FileID = '<xsl:value-of select="@ID"/>');
          if(_existid is not null)
          then
          UPDATE VCLib.T_ReportFile SET
          RepFmtID = _repFormatType,
          IsSecondary = <xsl:choose>
            <xsl:when test="@IsSecondary">
              '<xsl:value-of select="@IsSecondary"/>'
            </xsl:when>
            <xsl:otherwise>'0'</xsl:otherwise>
          </xsl:choose>
          WHERE FileID = _ExistID;
          else
          INSERT INTO VCLib.T_ReportFile (FileID, GroupID, RepFmtID, IsSecondary)
          VALUES ('<xsl:value-of select="@ID"/>', _groupID, _repFormatType, <xsl:choose>
            <xsl:when test="@IsSecondary">
              '<xsl:value-of select="@IsSecondary"/>'
            </xsl:when>
            <xsl:otherwise>0</xsl:otherwise>
          </xsl:choose>);
          end if;
          --Parameters
          <xsl:for-each select="Parameter">
            _tempid := gen_random_uuid();
            _existid := (select ParamID FROM VCLib.T_ReportFileParameter WHERE FileID = '<xsl:value-of select="@ID"/>' AND Name = '<xsl:value-of select="@Name"/>');
            if(_existid is not null)
            then
            UPDATE VCLib.T_ReportFileParameter SET
            Content = '<xsl:value-of select="."/>'
            WHERE ParamID = _ExistID;
            else
            INSERT INTO VCLib.T_ReportFileParameter(ParamID,FileID,Name,Content)
            VALUES (_tempID, '<xsl:value-of select="@ID"/>', '<xsl:value-of select="@Name"/>', '<xsl:value-of select="."/>');
            end if;
          </xsl:for-each>
        </xsl:for-each>
      </xsl:for-each>

      end;$$;
    </xsl:for-each>
  </xsl:template>

  <xsl:template match="root/sqllist/sql">
    <xsl:value-of select="@Code" />
    ;
  </xsl:template>

  <!-- GENERIC REPORTS -->
  <xsl:template match="StaticGroups">
    do $$
    declare
    begin
    IF EXISTS (select 0 from VCLib.T_STATIC_GROUPS where StaticGroupsID = '<xsl:value-of select="StaticGroupsID"/>')
    then
    UPDATE VCLib.T_STATIC_GROUPS SET
    GrStaticGroupGenerator = <xsl:choose>
      <xsl:when test="GeneratorID">
        '<xsl:value-of select="GeneratorID"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    Condition = <xsl:choose>
      <xsl:when test="Condition">
        '<xsl:value-of select="Condition"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>, Name = '<xsl:value-of select="Name"/>', OrderNum = '<xsl:value-of select="OrderNum"/>',
    ParentGroup = <xsl:choose>
      <xsl:when test="ParentGroup">
        '<xsl:value-of select="ParentGroup"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    WHERE StaticGroupsID = '<xsl:value-of select="StaticGroupsID"/>';
    else
    INSERT INTO VCLib.T_STATIC_GROUPS (StaticGroupsID, GrStaticGroupGenerator, Condition, Name, OrderNum, ParentGroup)
    VALUES ('<xsl:value-of select="StaticGroupsID"/>',
    <xsl:choose>
      <xsl:when test="GeneratorID">
        '<xsl:value-of select="GeneratorID"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    <xsl:choose>
      <xsl:when test="Condition">
        '<xsl:value-of select="Condition"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    '<xsl:value-of select="Name"/>',
    <xsl:value-of select="OrderNum"/>,
    <xsl:choose>
      <xsl:when test="ParentGroup">
        '<xsl:value-of select="ParentGroup"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>);
    end if;
    <xsl:apply-templates select="StaticGroups" />;
    end;$$;
  </xsl:template>

  <xsl:template match="root/GenericReports">
    -- GenericReportGenerators
    do $$
    declare
    _generatorid uuid;
    _genname varchar(255);
    _entid integer;
    _generatortype integer;
    _id uuid;
    _resolvername varchar(255);
    _entid integer;
    _columndescription varchar(64);
    _generatorpath varchar(512);
    _columnspath varchar(512);
    _roleid uuid;
    _filefrmid uuid;
    begin
    <xsl:for-each select="GenericReportGeneratorStaticGroups">
      _generatorid := '<xsl:value-of select="ID"/>';
      _genname := '<xsl:value-of select="Name"/>';
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>');
      _generatortype := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='GR_STATIC_GROUP_GENERATOR');
      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GENERATOR where GenericReportGeneratorID = _generatorID)
      then
      UPDATE VCLib.T_GENERIC_REPORT_GENERATOR SET Name = _genName, Entities = _entID, type_ = _generatorType
      WHERE GenericReportGeneratorID = _generatorID;
      else
      INSERT INTO VCLib.T_GENERIC_REPORT_GENERATOR (GenericReportGeneratorID, Name, Entities, type_)
      VALUES (_generatorID, _genName, _entID, _generatorType);
      end if;
      <xsl:apply-templates select="StaticGroups" />;
    </xsl:for-each>

    <xsl:for-each select="GenericReportGeneratorGroupByPath">
      _generatorid := '<xsl:value-of select="ID"/>';
      _genname := '<xsl:value-of select="Name"/>';
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>');
      _generatortype := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='GR_GROP_BY_PATH_GENERATOR');

      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GENERATOR where GenericReportGeneratorID = _generatorID)
      then
      UPDATE VCLib.T_GENERIC_REPORT_GENERATOR SET Name = _genName, Entities = _entID, type_ = _generatorType
      WHERE GenericReportGeneratorID = _generatorID;
      else
      INSERT INTO VCLib.T_GENERIC_REPORT_GENERATOR (GenericReportGeneratorID, Name, Entities, type_)
      VALUES (_generatorID, _genName, _entID, _generatorType);
      end if;
      <xsl:for-each select="Groupbys">
        IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GROUPBY where GenericReportGroupbyID = '<xsl:value-of select="GenericReportGroupbyID"/>')
        then
        UPDATE VCLib.T_GENERIC_REPORT_GROUPBY SET OrderBy = '<xsl:value-of select="OrderBy"/>', OrderNum = '<xsl:value-of select="OrderNum"/>',
        GrGropByPathGenerator = _generatorID, GroupByPaths = '<xsl:value-of select="GroupByPaths"/>',
        DisplayFormat = '<xsl:value-of select="DisplayFormat"/>'
        WHERE GenericReportGroupbyID = '<xsl:value-of select="GenericReportGroupbyID"/>';
        else
        INSERT INTO VCLib.T_GENERIC_REPORT_GROUPBY (GenericReportGroupbyID, OrderBy, OrderNum, GrGropByPathGenerator, GroupByPaths, DisplayFormat)
        VALUES ('<xsl:value-of select="GenericReportGroupbyID"/>',
        '<xsl:value-of select="OrderBy"/>',
        '<xsl:value-of select="OrderNum"/>',
        _generatorID,
        '<xsl:value-of select="GroupByPaths"/>',
        '<xsl:value-of select="DisplayFormat"/>');
        end if;
      </xsl:for-each>
    </xsl:for-each>

    <xsl:for-each select="GenericReportGeneratorCode">
      _generatorid := '<xsl:value-of select="ID"/>';
      _genname := '<xsl:value-of select="Name"/>';
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>');
      _generatortype := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='GR_CODE_GENERATOR');
      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GENERATOR where GenericReportGeneratorID = _generatorID)
      then
      UPDATE VCLib.T_GENERIC_REPORT_GENERATOR SET Name = _genName, Entities = _entID, type_ = _generatorType
      WHERE GenericReportGeneratorID = _generatorID;
      ELSE
      INSERT INTO VCLib.T_GENERIC_REPORT_GENERATOR (GenericReportGeneratorID, Name, Entities, type_)
      VALUES (_generatorID, _genName, _entID, _generatorType);
      end if;
      IF EXISTS (select 0 from T_GR_CODE_GENERATOR where GenericReportGeneratorID = _generatorID)
      then
      UPDATE T_GR_CODE_GENERATOR SET NetClass = '<xsl:value-of select="NetClass"/>'
      WHERE GenericReportGeneratorID = _generatorID;
      ELSE
      INSERT INTO T_GR_CODE_GENERATOR (GenericReportGeneratorID, NetClass)
      VALUES (_generatorID, '<xsl:value-of select="NetClass"/>');
      end if;
    </xsl:for-each>

    <xsl:for-each select="GrResolver">
      _id:= '<xsl:value-of select="ID"/>';
      _resolvername := '<xsl:value-of select="Name"/>';
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>');

      IF EXISTS (select 0 from T_GR_RESOLVER where GrResolverID = _ID)
      then
      UPDATE T_GR_RESOLVER SET Entities = _entID, Name = _resolverName WHERE GrResolverID = _ID;
      else
      INSERT INTO T_GR_RESOLVER (GrResolverID, Name, Entities) VALUES (_ID, _resolverName, _entID);
      end if;

      <xsl:for-each select="Column">
        _columndescription := <xsl:choose>
          <xsl:when test="Description">
            '<xsl:value-of select="Description"/>'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>;

        IF EXISTS (select 0 from T_GENERIC_REPORT_COLUMN where GenericReportColumnID = '<xsl:value-of select="GenericReportColumnID"/>')
        then
        UPDATE T_GENERIC_REPORT_COLUMN SET Description = _columnDescription, Condition = <xsl:choose>
          <xsl:when test="Condition">
            '<xsl:value-of select="Condition"/>'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>,
        Name = '<xsl:value-of select="Name"/>',
        GrResolver = _ID, Calculated = <xsl:value-of select="Calculated"/>,
        LexemFormula = <xsl:choose>
          <xsl:when test="LexemFormula">
            '<xsl:value-of select="LexemFormula"/>'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
        WHERE GenericReportColumnID = '<xsl:value-of select="GenericReportColumnID"/>';
        else
        INSERT T_GENERIC_REPORT_COLUMN (GenericReportColumnID, Description, Condition, Name, GrResolver, Calculated, LexemFormula)
        VALUES ('<xsl:value-of select="GenericReportColumnID"/>',
        _columnDescription, <xsl:choose>
          <xsl:when test="Condition">
            '<xsl:value-of select="Condition"/>'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>,
        '<xsl:value-of select="Name"/>', _ID,
        <xsl:value-of select="Calculated"/>,
        <xsl:choose>
          <xsl:when test="LexemFormula">
            '<xsl:value-of select="LexemFormula"/>'
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>);
        end if;

      </xsl:for-each>
    </xsl:for-each>

    <xsl:for-each select="GenericReport">
      _id := '<xsl:value-of select="ID"/>';
      _entid := (select EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>');
      _generatorpath := <xsl:choose>
        <xsl:when test="GeneratorPath">
          '<xsl:value-of select="GeneratorPath"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      _columnspath := <xsl:choose>
        <xsl:when test="ColumnsPath">
          '<xsl:value-of select="ColumnsPath"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT where GenericReportID = _ID)
      then
      UPDATE VCLib.T_GENERIC_REPORT SET Name = '<xsl:value-of select="Name"/>',
      Entities = _entID,
      GenericReportGenerator = '<xsl:value-of select="Generator"/>',
      UseCustomGenerator = '<xsl:value-of select="UseCustomGenerator"/>',
      GrResolver = '<xsl:value-of select="GrResolver"/>',
      Filter = <xsl:choose>
        <xsl:when test="Filter">
          '<xsl:value-of select="Filter"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      GeneratorPath = _generatorPath, ColumnsPath = _columnsPath
      WHERE GenericReportID = _ID;
      else
      INSERT INTO VCLib.T_GENERIC_REPORT (GenericReportID, Entities, GenericReportGenerator,
      Filter, Name, GeneratorPath, UseCustomGenerator, GrResolver, ColumnsPath)
      VALUES (_ID, _entID,
      '<xsl:value-of select="Generator"/>',
      <xsl:choose>
        <xsl:when test="Filter">
          '<xsl:value-of select="Filter"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>,
      '<xsl:value-of select="Name"/>',
      _generatorPath,
      <xsl:value-of select="UseCustomGenerator"/>,
      '<xsl:value-of select="GrResolver"/>',
      _columnsPath);
      end if;

      <xsl:for-each select="GeneratorParam">
        IF EXISTS (select 0 from VCLib.T_GENERATOR_PARAMS where GeneratorParamsID = '<xsl:value-of select="ID"/>')
        then
        UPDATE VCLib.T_GENERATOR_PARAMS SET GenericReport = _ID,
        Name = '<xsl:value-of select="Name"/>',
        Value = '<xsl:value-of select="Value"/>',
        MakeUser = <xsl:value-of select="MakeUser"/>
        WHERE GeneratorParamsID = '<xsl:value-of select="ID"/>';
        else
        INSERT VCLib.T_GENERATOR_PARAMS (GeneratorParamsID, Name, Value, GenericReport, MakeUser)
        VALUES ('<xsl:value-of select="ID"/>',
        '<xsl:value-of select="Name"/>',
        '<xsl:value-of select="Value"/>',
        _ID,
        <xsl:value-of select="MakeUser"/>);
        end if;
      </xsl:for-each>

      <xsl:for-each select="GrRight">
        _roleid := (select RoleID from VCLib.T_ROLES WHERE RoleName = '<xsl:value-of select="Roles"/>');
        IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_RIGHTS where GenericReportRightsID = '<xsl:value-of select="ID"/>')
        then
        UPDATE VCLib.T_GENERIC_REPORT_RIGHTS SET GenericReport = _ID,
        Roles = _roleID,
        ReportRights = '<xsl:value-of select="ReportRights"/>'
        WHERE GenericReportRightsID = '<xsl:value-of select="ID"/>';
        else
        INSERT VCLib.T_GENERIC_REPORT_RIGHTS (GenericReportRightsID, GenericReport, Roles, ReportRights)
        VALUES ('<xsl:value-of select="ID"/>', _ID, _roleID,
        <xsl:value-of select="ReportRights"/>);
        end if;
      </xsl:for-each>

      <xsl:for-each select="GenericReportTemplate">
        UPDATE VCLib.T_FILES SET
        type_ = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName='GENERIC_REPORT_TEMPLATE')
        WHERE FileID = '<xsl:value-of select="FileID"/>';
        _filefrmid := (select RepFmtID from VCLib.T_ReportFileFormat WHERE Name = '<xsl:value-of select="ReportFileFormat"/>');

        IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_TEMPLATE where FileID = '<xsl:value-of select="FileID"/>')
        then
        UPDATE VCLib.T_GENERIC_REPORT_TEMPLATE SET GenericReport = _ID,
        ReportFileFormat = _fileFrmID
        WHERE FileID = '<xsl:value-of select="FileID"/>';
        else
        INSERT VCLib.T_GENERIC_REPORT_TEMPLATE (ReportFileFormat, GenericReport, FileID)
        VALUES (_fileFrmID, _ID, '<xsl:value-of select="FileID"/>');
        end if;
      </xsl:for-each>
    </xsl:for-each>
    end;$$;
  </xsl:template>

  <!-- Reports.config -->
  <xsl:template match="Root/Reports/Report/Templates/Template">
    <xsl:variable name="tPath" select="Files[1]/File[1]/@path" />

    <xsl:for-each select="Files/File">
      <xsl:if test="@path=$tPath" >
        <xsl:call-template name="ReportConfigFileTemplate" />
      </xsl:if>
      <xsl:if test="@path!=$tPath" >
        <xsl:call-template name="TemplateOverrideFileTemplate" />
      </xsl:if>
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="ReportConfigFileTemplate" >
    do $$
        declare
        _fileFormat varchar(255);
        _formatID uuid;
    begin
        _fileFormat:='<xsl:value-of select="../../../../@format"/>';
        _fileFormat:=(select replace(replace(replace(_fileFormat, ' ', ''), 'GEst.Report.', ''), ',GEst.Report', ''));

        _formatID:=(select rff.RepFmtID from VCLib.T_ReportFileFormat rff where rff.HandlerName like '%' || _fileFormat || '%');

        update VCLib.T_REPORTS_CONFIG rc
        set TemplatePath = '<xsl:value-of select="@path"/>'
           ,FileName = '<xsl:value-of select="@name"/>'
           ,OutputName = '<xsl:value-of select="@outputName"/>'
           ,ReportFileFormat = _formatID
           ,ReportProcessingWay = case
                                      when '<xsl:value-of select="@EnableAspose"/>' = 'false' then 1
                                      when '<xsl:value-of select="@EnableAspose"/>' = 'true' then 2
                                      when '<xsl:value-of select="@ExcelFormat"/>' = 'MsOle' then 1
                                      when '<xsl:value-of select="@ExcelFormat"/>' = 'Fast' then 2
                                      else 0
                                   end
           ,OutputFormat = case
                               when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1
                               when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2
                               else 0
                            end
        where rc.TemplateName = '<xsl:value-of select="../../@name"/>';

        INSERT INTO VCLib.T_REPORTS_CONFIG (TemplateName, TemplatePath, FileName, OutputName, ReportFileFormat, ReportProcessingWay, OutputFormat)
        SELECT
                   '<xsl:value-of select="../../@name"/>'
                   ,'<xsl:value-of select="@path"/>'
                   ,'<xsl:value-of select="@name"/>'
                   ,'<xsl:value-of select="@outputName"/>'
                   ,_formatID
                   ,case
                        when '<xsl:value-of select="@EnableAspose"/>' = 'false' then 1
                        when '<xsl:value-of select="@EnableAspose"/>' = 'true' then 2
                        when '<xsl:value-of select="@ExcelFormat"/>' = 'MsOle' then 1
                        when '<xsl:value-of select="@ExcelFormat"/>' = 'Fast' then 2
                        else 0
                    end
                   ,case                                                             
                       when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1  
                       when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2 
                       else 0                                                        
                    end                                                              
        where not exists
        (
            select *
            from VCLib.T_REPORTS_CONFIG rc
            where rc.TemplateName = '<xsl:value-of select="../../@name"/>'
        );
    end;$$;
  </xsl:template>
  <xsl:template name="TemplateOverrideFileTemplate" >
    do $$
        declare
        _fileFormat varchar(255);
        _formatID uuid;
    begin
        _fileFormat:='<xsl:value-of select="../../../../@format"/>';
        _fileFormat:=(select replace(replace(replace(_fileFormat, ' ', ''), 'GEst.Report.', ''), ',GEst.Report', ''));

        _formatID:=(select rff.RepFmtID from VCLib.T_ReportFileFormat rff where rff.HandlerName like '%' || _fileFormat || '%');

        update VCLib.T_TEMPLATE_OVERRIDE tmo
        set TemplatePath = '<xsl:value-of select="@path"/>'
           ,Printer = '<xsl:value-of select="@printer"/>'
           ,OverrideOrder = case
                                when (select MAX(OverrideOrder) from VCLib.T_TEMPLATE_OVERRIDE where ReportsConfig = '<xsl:value-of select="../../@name"/>') is null then -1
                                else (select MAX(OverrideOrder) from VCLib.T_TEMPLATE_OVERRIDE where ReportsConfig = '<xsl:value-of select="../../@name"/>')
                            end + 1
           ,FileName = '<xsl:value-of select="@name"/>'
           ,OutputName = '<xsl:value-of select="@outputName"/>'
           ,ReportFileFormat = _formatID
           ,ReportProcessingWay = case
                                      when '<xsl:value-of select="@EnableAspose"/>' = 'false' then 1
                                      when '<xsl:value-of select="@EnableAspose"/>' = 'true' then 2
                                      when '<xsl:value-of select="@ExcelFormat"/>' = 'MsOle' then 1
                                      when '<xsl:value-of select="@ExcelFormat"/>' = 'Fast' then 2
                                      else 0
                                   end
           ,OutputFormat = case
                                when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1
                                when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2
                                else 0
                             end
        where tmo.TemplatePath = '<xsl:value-of select="@path"/>'
                and
            tmo.UsersGroup is null;

        INSERT INTO VCLib.T_TEMPLATE_OVERRIDE (TemplateOverrideID, TemplatePath, Printer, OverrideOrder, FileName, OutputName, ReportsConfig, ReportFileFormat, ReportProcessingWay, ConfigType, OutputFormat)
        SELECT
                    uuid_generate_v4()	
                   ,'<xsl:value-of select="@path"/>'
                   ,'<xsl:value-of select="@printer"/>'
                   ,case                                                                                                                                             
                        when (select MAX(OverrideOrder) from VCLib.T_TEMPLATE_OVERRIDE where ReportsConfig = '<xsl:value-of select="../../@name"/>') is null then -1 
                        else (select MAX(OverrideOrder) from VCLib.T_TEMPLATE_OVERRIDE where ReportsConfig = '<xsl:value-of select="../../@name"/>')                 
                    end + 1                                                                                                                                          
                   ,'<xsl:value-of select="@name"/>'
                   ,'<xsl:value-of select="@outputName"/>'
                   ,'<xsl:value-of select="../../@name"/>'
                   ,_formatID
                   ,case
                        when '<xsl:value-of select="@EnableAspose"/>' = 'false' then 1
                        when '<xsl:value-of select="@EnableAspose"/>' = 'true' then 2
                        when '<xsl:value-of select="@ExcelFormat"/>' = 'MsOle' then 1
                        when '<xsl:value-of select="@ExcelFormat"/>' = 'Fast' then 2
                        else 0
                    end
                   ,1
                   ,case                                                             
                       when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1  
                       when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2 
                       else 0                                                        
                    end                                                              
        where not exists
        (
            select *
            from VCLib.T_TEMPLATE_OVERRIDE tmo
            where tmo.TemplatePath = '<xsl:value-of select="@path"/>'
                    and
                tmo.UsersGroup is null
        );
    end;$$;
  </xsl:template>

  <xsl:template match="RefreshEntities">
    select sp_UpdateEntities();
  </xsl:template>

  <xsl:template match="StartupProcesses/StartupProcess">
    <xsl:text>INSERT INTO VCLib.T_STARTUP_PROCESS (StartupProcessID, ProcessName, ProcessParameters)&#10;VALUES(gen_random_uuid(), '</xsl:text>
    <xsl:value-of select="@ProcessName"/>
    <xsl:text>', </xsl:text>
    <xsl:choose>
      <xsl:when test="ProcessParameters/ProcessParameter">
        <xsl:text>'<![CDATA[<ProcessParameters>]]></xsl:text>
        <xsl:apply-templates select="ProcessParameters/ProcessParameter"/>
        <xsl:text><![CDATA[</ProcessParameters>]]>'</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>NULL</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:text>);&#10;&#10;</xsl:text>
  </xsl:template>

  <xsl:template match="ProcessParameter">
    <xsl:text><![CDATA[<ProcessParameter Name="]]></xsl:text>
    <xsl:value-of select="@Name"/>
    <xsl:text><![CDATA["><Value>]]></xsl:text>
    <xsl:value-of select="normalize-space(.)"/>
    <xsl:text><![CDATA[</Value></ProcessParameter>]]></xsl:text>
  </xsl:template>

  <xsl:template name="serializeNodeToString">
    <xsl:param name="node" />
    <xsl:variable name="name" select="name($node)" />

    <xsl:text>&lt;</xsl:text>
    <xsl:value-of select="$name" />
    <xsl:for-each select="$node/@*">
      <xsl:text> </xsl:text>
      <xsl:value-of select="name()" />
      <xsl:text>=&quot;</xsl:text>
      <xsl:call-template name ="replaceSpecCharacters">
        <xsl:with-param name="text" select="."/>
      </xsl:call-template>
      <xsl:text>&quot;</xsl:text>
    </xsl:for-each>
    <xsl:if test="namespace-uri($node)">
      <xsl:text> xmlns:</xsl:text>
      <xsl:value-of select="substring-before(name($node),':')"/>
      <xsl:text>="</xsl:text>
      <xsl:value-of select="namespace-uri($node)"/>
      <xsl:text>"</xsl:text>
    </xsl:if>
    <xsl:text>&gt;</xsl:text>
    <xsl:if test="$node/text()">
      <xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
      <xsl:value-of select="$node/text()" />
      <xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
    </xsl:if>
    <xsl:for-each select="$node/*">
      <xsl:call-template name="serializeNodeToString">
        <xsl:with-param name="node" select="."/>
      </xsl:call-template>
    </xsl:for-each>

    <xsl:text>&lt;/</xsl:text>
    <xsl:value-of select="$name" />
    <xsl:text>&gt;</xsl:text>
  </xsl:template>

  <xsl:template name="replaceApostrs">
    <xsl:param name="text" />
    <xsl:variable name="apos">'</xsl:variable>
    <xsl:variable name="apos2">''</xsl:variable>

    <xsl:call-template name ="replace">
      <xsl:with-param name="text" select="$text"/>
      <xsl:with-param name="replace" select="$apos"/>
      <xsl:with-param name="by" select="$apos2"/>
    </xsl:call-template>
  </xsl:template>

  <xsl:template name="replaceSpecCharacters">
    <xsl:param name="text" />

    <xsl:call-template name ="replace">
      <xsl:with-param name="text">
        <xsl:call-template name ="replace">
          <xsl:with-param name="text">
            <xsl:call-template name ="replace">
              <xsl:with-param name="text">
                <xsl:call-template name ="replace">
                  <xsl:with-param name="text">
                    <xsl:call-template name ="replace">
                      <xsl:with-param name="text" select="$text"/>
                      <xsl:with-param name="replace">&amp;</xsl:with-param>
                      <xsl:with-param name="by">&amp;amp;</xsl:with-param>
                    </xsl:call-template>
                  </xsl:with-param>
                  <xsl:with-param name="replace">&lt;</xsl:with-param>
                  <xsl:with-param name="by">&amp;lt;</xsl:with-param>
                </xsl:call-template>
              </xsl:with-param>
              <xsl:with-param name="replace">&gt;</xsl:with-param>
              <xsl:with-param name="by">&amp;gt;</xsl:with-param>
            </xsl:call-template>
          </xsl:with-param>
          <xsl:with-param name="replace">&apos;</xsl:with-param>
          <xsl:with-param name="by">&amp;apos;</xsl:with-param>
        </xsl:call-template>
      </xsl:with-param>
      <xsl:with-param name="replace">&quot;</xsl:with-param>
      <xsl:with-param name="by">&amp;quot;</xsl:with-param>
    </xsl:call-template>
  </xsl:template>

  <xsl:template name ="replace">
    <xsl:param name="text"/>
    <xsl:param name="replace"/>
    <xsl:param name="by"/>
    <xsl:choose>
      <xsl:when test="contains($text, $replace)">
        <xsl:value-of select="substring-before($text,$replace)" />
        <xsl:value-of select="$by" />
        <xsl:call-template name="replace">
          <xsl:with-param name="text" select="substring-after($text,$replace)" />
          <xsl:with-param name ="replace" select="$replace"/>
          <xsl:with-param name ="by" select="$by"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$text" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  
  <!--Загрузка web приложений-->
  <xsl:template match="webapp/*">
INSERT INTO VCLib.T_WEB_APP
           (WebAppID
           ,Name
           ,HostedAddress
           ,IsActive
           ,IsSwaggerActive
           ,AnonymousAccessUser)
SELECT
           gen_random_uuid()--WebAppID, uniqueidentifier,>
          ,'<xsl:value-of select ="name(.)"/>'--Name, varchar(255),>
          ,'<xsl:value-of select ="@hosted"/>'--HostedAddress, varchar(255),>
          ,'<xsl:choose>
            <xsl:when test="@active!=''"><xsl:value-of select="@active" /></xsl:when>
            <xsl:otherwise>0</xsl:otherwise>
           </xsl:choose>'--IsActive, bit,>
          ,false
          ,<xsl:choose>
            <xsl:when test="@login!=''">(
                select u.UserID
                from VCLib.T_USERS u
                where exists
                    (
                        select *
                        from VCLib.T_LOGIN l
                        where l.Identity = u.Identity
                                and
                            l.Login = '<xsl:value-of select="@login" />'
                    ) 
                limit 1)
            </xsl:when>
            <xsl:otherwise>null</xsl:otherwise>
           </xsl:choose>--AnonymousAccessUser, uniqueidentifier,>)
where not exists
    (
        select *
        from VCLib.T_WEB_APP wa
        where wa.Name = '<xsl:value-of select ="name(.)"/>'
    );
--PGSQL:GO

    <xsl:for-each select="DTOs[(headers)]">
      <xsl:call-template name="loadAppDtos" />
    </xsl:for-each>

    <xsl:for-each select="webAliases[(alias)]">
      <xsl:call-template name="loadAppAliases" />
    </xsl:for-each>

  </xsl:template>

  <!--Загрузка шаблонов DTO-->
  <xsl:template match="root/DTOs[(headers)]" name="loadAppDtos">
DO 
$$ 
DECLARE
         _entity varchar(133);
         _alias varchar(100);
         _descr varchar(255);
         _system boolean;
         _type int;
         _dtoId uuid;
         _dtoViewId uuid;
         _app varchar(255);
BEGIN 

    <xsl:for-each select="headers/model[@Recreate='1']">
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DropDTO" />
    </xsl:for-each>

    <xsl:for-each select="headers/model">
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
INSERT INTO VCLib.T_DTO_MODEL
           (DtoModelID ,Entities ,Name ,Descr ,MaxNestingLevel ,IsSystem ,WebApp)
SELECT
           gen_random_uuid()--DtoModelID, uniqueidentifier,
          ,e.EntityID--Entities, int,
          ,_alias--Name, nvarchar(100),
          ,_descr--Descr, nvarchar(255),
          ,<xsl:value-of select="@nesting" />--MaxNestingLevel, int,
          ,_system--IsSystem, bit,
          ,(select a.WebAppID from VCLib.T_WEB_APP a where a.Name = _app)
from VCLib.T_ENTITIES e
where not exists(select * from VCLib.T_DTO_MODEL m where m.DtoModelID = _dtoId)
        and
    e.Schema || '.' || e.EntName = _entity;
    </xsl:for-each>

    <xsl:for-each select="headers/model/items/item">
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
INSERT INTO VCLib.T_DTO_ITEM
           (DtoItemID ,Name ,ItemLexem ,DataType ,Multiplicity ,DtoModel ,ItemDtoModel ,StringFormat ,IsInvariantCulture ,Postion ,PathsAttributes ,BindingType ,Descr ,InputValueLexem ,ChoiceData)
SELECT
           gen_random_uuid()--DtoItemID, uniqueidentifier,
          ,'<xsl:value-of select="@itemName" />'--Name, nvarchar(255),
          ,'<xsl:value-of select="@lexem" />'--ItemLexem, nvarchar(max),
          ,_type--DataType, int,
          ,<xsl:value-of select="@multiplicity" />--Multiplicity, int,
          ,_dtoId--DtoModel, uniqueidentifier,
          ,(select m.DtoModelID
            from VCLib.T_DTO_MODEL m
                join VCLib.T_ENTITIES e on e.EntityID = m.Entities
                join VCLib.T_WEB_APP a on a.WebAppID = m.WebApp
            where e.Schema || '.' || e.EntName = '<xsl:value-of select="@itemEntity" />'
                    and
                (m.Name is null and '<xsl:value-of select="@itemAlias" />' = '' or m.Name = '<xsl:value-of select="@itemAlias" />')
                    and
                a.Name = _app)--ItemDtoModel, uniqueidentifier,
          ,'<xsl:call-template name="replaceApostrs">
              <xsl:with-param name="text" select="@format"/>
            </xsl:call-template>'--StringFormat, nvarchar(255),
          ,'<xsl:value-of select="@invariant" />'--IsInvariantCulture, bit,
          ,<xsl:value-of select="@position" />--Postion, int,
          ,'<xsl:value-of select="@pathAttributes" />'--PathsAttributes, nvarchar(max),
          ,<xsl:value-of select="@bindingType" />--BindingType, int,
          ,_descr--Descr, nvarchar(1000),
          ,'<xsl:value-of select="@inputLexem" />'--InputValueLexem, nvarchar(max),
          ,'<xsl:value-of select="@choiceData" />'--ChoiceData, varchar(2000),
where exists(select * from VCLib.T_DTO_MODEL m where m.DtoModelID = _dtoId)
        and
      not exists(select * from VCLib.T_DTO_ITEM i where i.DtoModel = _dtoId and i.Name = '<xsl:value-of select="@itemName" />');
    </xsl:for-each>

    <xsl:for-each select="headers/model/filters/filter">
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
INSERT INTO VCLib.T_DTO_MODEL_FILTER
           (DtoModelFilterID ,DtoModel ,Name ,DtoItem ,CriterionType ,Condition)
SELECT
            gen_random_uuid()--DtoModelFilterID, uniqueidentifier
           ,_dtoId--DtoModel, uniqueidentifier
           ,'<xsl:value-of select="@filterName" />'--Name, varchar(255)
           ,(
                select di.DtoItemID
                from VCLib.T_DTO_ITEM di
                where (di.Name is null and '<xsl:value-of select="@itemName" />' = '' or di.Name = '<xsl:value-of select="@itemName" />')
                        and
                    di.DtoModel = _dtoId
            )--DtoItem, uniqueidentifier
           ,<xsl:value-of select="@criterionType" />--CriterionType, int
           ,'<xsl:value-of select="@condition" />'--Condition, varchar(max))
where not exists
    (
        select *
        from VCLib.T_DTO_MODEL_FILTER df
        where df.DtoModel = _dtoId
                and
            df.Name = '<xsl:value-of select="@filterName" />'
    );
    </xsl:for-each>

    <xsl:for-each select="headers/model/views/view">
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
select COALESCE((select v.DtoModelViewID
from VCLib.T_DTO_MODEL_VIEW v
where v.DtoModel = _dtoId and <xsl:choose>
          <xsl:when test="@Context!=''">v.Context = '<xsl:value-of select="@Context" />'</xsl:when>
          <xsl:otherwise>v.Context is null</xsl:otherwise>
        </xsl:choose>), gen_random_uuid())
      into _dtoViewId;
INSERT INTO VCLib.T_DTO_MODEL_VIEW
           (DtoModelViewID ,Context ,DtoModel)
SELECT
           _dtoViewId--DtoModelViewID, uniqueidentifier,
          ,<xsl:choose>
        <xsl:when test="@Context!=''">'<xsl:value-of select="@Context" />'</xsl:when>
        <xsl:otherwise>null</xsl:otherwise>
      </xsl:choose>--Context, varchar(255),
          ,_dtoId--DtoModel, uniqueidentifier,
where not exists(select * from VCLib.T_DTO_MODEL_VIEW v where v.DtoModelViewID = _dtoViewId)
        and
    exists(select * from VCLib.T_DTO_MODEL m where m.DtoModelID = _dtoId);
      <xsl:for-each select="columns/column" >
INSERT INTO VCLib.T_DTO_MODEL_COLUMN
           (DtoModelColumnID ,DtoModelView ,ColPath ,EditPath ,ColDescr ,ColOrder ,IsVisible ,SortOrder ,SortDirection ,FormatType ,Aggregation)
SELECT
           gen_random_uuid()--DtoModelColumnID, uniqueidentifier,
          ,_dtoViewId--DtoModelView, uniqueidentifier,
          ,'<xsl:value-of select="@ColPath" />'--ColPath, varchar(500),
          ,<xsl:choose>
          <xsl:when test="@EditPath!=''">'<xsl:value-of select="@EditPath" />'</xsl:when>
          <xsl:otherwise>null</xsl:otherwise>
        </xsl:choose>--EditPath, varchar(500),
          ,<xsl:choose>
          <xsl:when test="@ColDescr!=''">'<xsl:value-of select="@ColDescr" />'</xsl:when>
          <xsl:otherwise>null</xsl:otherwise>
        </xsl:choose>--ColDescr, varchar(1000),
          ,<xsl:value-of select="@ColOrder" />--ColOrder, int,
          ,'<xsl:value-of select="@IsVisible" />'--IsVisible, bit,
          ,<xsl:choose>
          <xsl:when test="@SortOrder!=''"><xsl:value-of select="@SortOrder" /></xsl:when>
          <xsl:otherwise>null</xsl:otherwise>
        </xsl:choose>--SortOrder, int,
          ,<xsl:choose>
          <xsl:when test="@SortDirection!=''"><xsl:value-of select="@SortDirection" /></xsl:when>
          <xsl:otherwise>null</xsl:otherwise>
        </xsl:choose>--SortDirection, int,
          ,<xsl:choose>
          <xsl:when test="@FormatType!=''"><xsl:value-of select="@FormatType" /></xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>--FormatType, int,
          ,<xsl:choose>
          <xsl:when test="@Aggregation!=''"><xsl:value-of select="@Aggregation" /></xsl:when>
          <xsl:otherwise>null</xsl:otherwise>
        </xsl:choose>--Aggregation, int,
where not exists(select * from VCLib.T_DTO_MODEL_COLUMN c where c.DtoModelView = _dtoViewId and c.ColPath = '<xsl:value-of select="@ColPath" />')
        and
    exists(select * from VCLib.T_DTO_MODEL m where m.DtoModelID = _dtoId);
      </xsl:for-each>
    </xsl:for-each>

END
$$ LANGUAGE  plpgsql;
  </xsl:template>
  <!--Генерация шаблонов DTO-->
  <xsl:template match="root/DTOs[(DTO)]">

    <xsl:for-each select="DTO[@Recreate='1']">
DO 
$$ 
DECLARE
         _entity varchar(133);
         _alias varchar(100);
         _descr varchar(255);
         _system boolean;
         _type int;
         _dtoId uuid;
         _app varchar(255);
BEGIN
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DropDTO" />
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

    <xsl:for-each select="DTO">
DO 
$$ 
DECLARE
         _entity varchar(133);
         _alias varchar(100);
         _descr varchar(255);
         _system boolean;
         _type int;
         _dtoId uuid;
         _app varchar(255);
BEGIN
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DTO" />
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

update VCLib.T_DTO_ITEM i
set Postion = o.position
from (
        select ROW_NUMBER() over (partition by i.DtoModel order by i.Postion, ae.depth desc, ea.AttributeId) "position", i.DtoItemID
        from VCLib.T_DTO_ITEM i
            join VCLib.T_DTO_MODEL m on m.DtoModelID = i.DtoModel
            join 
            (
                select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,IsAdditional ,EntitiesAttributesId ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
                from VCLib.T_ENTITIES_ATTRIBUTES
                union all
                select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,false ,EntityAttributesID ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
                from V_ENTITY_ATTRIBUTES
            ) ea on (lower(ea.Name) = lower(i.Name) or ea.IsPrimaryKey = true and i.Name =  'id')
            join
            ( select ae.BaseEntity, ae.EntityID, ae.depth from V_ENTITY_HIERARCHY ae) ae on ae.EntityID = m.Entities 
                    and
                ea.EntityID = ae.BaseEntity
    ) o
where o.DtoItemID = i.DtoItemID;
--PGSQL:GO

    <xsl:for-each select="DTO[@WithView = '1']">
DO 
$$ 
DECLARE
         _entity varchar(133);
         _alias varchar(100);
         _descr varchar(255);
         _system boolean;
         _type int;
         _dtoId uuid;
         _app varchar(255);
BEGIN
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DtoModelView" />
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

  </xsl:template>
  <!--Шаблон получения параметров для формирования DTO-->
  <xsl:template name="FillDtoParams">
select '<xsl:value-of select="@Entity"/>', <xsl:choose>
      <xsl:when test="@Name!=''">'<xsl:value-of select="@Name" />'</xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>, <xsl:choose>
      <xsl:when test="@Descr!=''">'<xsl:value-of select="@Descr" />'</xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>, <xsl:choose>
      <xsl:when test="@IsSystem">'<xsl:value-of select="@IsSystem" />'</xsl:when>
      <xsl:otherwise>'0'</xsl:otherwise>
    </xsl:choose>, <xsl:choose>
      <xsl:when test="@type">'<xsl:value-of select="@type" />'</xsl:when>
      <xsl:otherwise>1</xsl:otherwise>
    </xsl:choose>, <xsl:choose>
      <xsl:when test="@App">'<xsl:value-of select="@App" />'</xsl:when>
      <xsl:when test="../../@App">'<xsl:value-of select="../../@App" />'</xsl:when>
      <xsl:otherwise>'api'</xsl:otherwise>
    </xsl:choose>
    into _entity, _alias, _descr, _system, _type, _app;
  </xsl:template>
  <!--Шаблон для получения идентификатора DTO-->
  <xsl:template name="GetDtoId">
select COALESCE((select m.DtoModelID
from VCLib.T_DTO_MODEL m
    join VCLib.T_ENTITIES e on e.EntityID = m.Entities
    join VCLib.T_WEB_APP a on a.WebAppID = m.WebApp
where e.Schema || '.' || e.EntName = _entity
        and
    (m.Name is null and _alias is null or m.Name = _alias)
        and
    a.Name = _app), gen_random_uuid())
    into _dtoId;
  </xsl:template>
  <!--Шаблон для удаления загруженных DTO-->
  <xsl:template name="DropDTO">
delete from VCLib.T_DTO_MODEL_COLUMN mc
using VCLib.T_DTO_MODEL_VIEW mv 
where mv.DtoModelViewID = mc.DtoModelView
        and
    mv.DtoModel = _dtoId;

delete from VCLib.T_DTO_MODEL_VIEW mv
where mv.DtoModel = _dtoId;

delete from VCLib.T_DTO_MODEL_FILTER mf
where mf.DtoModel = _dtoId;

update VCLib.T_DTO_ITEM mi
set ItemDtoModel = null
where mi.ItemDtoModel = _dtoId;

delete from VCLib.T_DTO_ITEM mi
where mi.DtoModel = _dtoId;
  </xsl:template>
  <!--Шаблон для генерации атрибутов в DTO-->
  <xsl:template name="DTO">
update VCLib.T_DTO_MODEL m
set Descr = COALESCE(_descr, e.EntDesc),
    MaxNestingLevel = 2,
    IsSystem = _system
from VCLib.T_ENTITIES e, VCLib.T_WEB_APP a
where e.EntityID = m.Entities
        and
    a.WebAppID = m.WebApp
        and
    e.Schema || '.' || e.EntName = _entity
        and
    (m.Name is null and _alias is null or m.Name = _alias)
        and
    a.Name = _app;

INSERT INTO VCLib.T_DTO_MODEL
           (DtoModelID
           ,Entities
           ,Name
           ,Descr
           ,MaxNestingLevel
           ,IsSystem
           ,WebApp)
SELECT
            _dtoId--DtoModelID, uniqueidentifier,
           ,(select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = _entity)--Entities, int,
           ,_alias--Name, varchar(100),
           ,COALESCE(_descr, (select e.EntDesc from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = _entity limit 1))--Descr, varchar(255),
           ,2
           ,_system
           ,(select a.WebAppID from VCLib.T_WEB_APP a where a.Name = _app)
where not exists
    (
        select * 
        from VCLib.T_DTO_MODEL m
            join VCLib.T_ENTITIES e on e.EntityID = m.Entities
            join VCLib.T_WEB_APP a on a.WebAppID = m.WebApp
        where e.Schema || '.' || e.EntName = _entity
                and
            (m.Name is null and _alias is null or m.Name = _alias)
                and
            a.Name = _app
    );

update VCLib.T_DTO_ITEM i
set 
    ItemLexem = '&lt;lx:Path OwnerQueryLevel=&quot;0&quot; xmlns:lx=&quot;lexem&quot;&gt; &lt;lx:StepString name=&quot;' || ea.Name || '&quot; /&gt; &lt;/lx:Path&gt;',
    DataType = case
                  when ea.AttributeType = 4 then 13
                  when ea.SystemType = 'int' then 2
                  when ea.SystemType in ('string', 'text') then 1
                  when ea.SystemType = 'bigint' then 8
                  when ea.SystemType = 'binary' then 9
                  when ea.SystemType in ('date', 'datetime') then 6
                  when ea.SystemType = 'float' then 5
                  when ea.SystemType in ('boolean', 'bool') then 4
                  when ea.SystemType = 'byte' then 10
                  when ea.SystemType in ('GUID', 'guid') then 3
                  else 99
               end,
    Multiplicity = case
                        when ea.AttributeType = 2 and ea.IsRequired = true then 3
                        when ea.AttributeType = 2 and ea.IsRequired = false then 2
                        when ea.IsRequired = true then 1
                        else 0
                    end,
    DtoModel = _dtoId,
    StringFormat = case 
                      when ea.DefaultFormat is null and ea.SystemType in ('date', 'datetime') then 'yyyy-MM-dd'
                      else REPLACE(REPLACE(ea.DefaultFormat, '{0:', ''), '}', '')
                   end,
    ItemDtoModel = (
                      select m.DtoModelID 
                      from VCLib.T_DTO_MODEL m
                         join V_ENTITY_HIERARCHY ae on m.Entities = ae.BaseEntity
                         join VCLib.T_WEB_APP a on a.WebAppID = m.WebApp
                      where (m.Name is null or m.Name = '')
                            and 
                        ae.FullName = ea.SystemType
                            and
                        a.Name = _app
                      order by ae.Depth desc
                      limit 1
                   ),
    Descr = ea.Description
from VCLib.T_DTO_MODEL m
    join 
    (
        select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,IsAdditional ,EntitiesAttributesId ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
        from VCLib.T_ENTITIES_ATTRIBUTES
        union all
        select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,false ,EntityAttributesID ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
        from V_ENTITY_ATTRIBUTES
    ) ea on ea.EntityID in (select ae.BaseEntity from V_ENTITY_HIERARCHY ae where ae.EntityID = m.Entities) 
where (ea.Name = i.Name or ea.IsPrimaryKey = true and i.Name =  'id')
        and
    m.DtoModelID = i.DtoModel
        and
    m.DtoModelID = _dtoId;

INSERT INTO VCLib.T_DTO_ITEM
           (DtoItemID
           ,Name
           ,ItemLexem
           ,DataType
           ,Multiplicity
           ,DtoModel
           ,BindingType
           ,StringFormat
           ,ItemDtoModel
           ,IsInvariantCulture
           ,Postion
           ,Descr
           ,ChoiceData)
SELECT
            gen_random_uuid()--DtoItemID, uniqueidentifier,
           ,case
              when ea.IsPrimaryKey = true then 'id'
              else LOWER(SUBSTRING(ea.Name, 1, 1)) || SUBSTRING(ea.Name, 2, LENGTH(ea.Name) - 1)--Name, varchar(255),
            end
           ,'&lt;lx:Path OwnerQueryLevel=&quot;0&quot; xmlns:lx=&quot;lexem&quot;&gt; &lt;lx:StepString name=&quot;' || ea.Name || '&quot; /&gt; &lt;/lx:Path&gt;'
           ,case
              when ea.AttributeType = 4 then 13
              when ea.SystemType = 'int' then 2
              when ea.SystemType in ('string', 'text') then 1
              when ea.SystemType = 'bigint' then 8
              when ea.SystemType = 'binary' then 9
              when ea.SystemType in ('date', 'datetime') then 6
              when ea.SystemType = 'float' then 5
              when ea.SystemType in ('boolean', 'bool') then 4
              when ea.SystemType = 'byte' then 10
              when ea.SystemType in ('GUID', 'guid') then 3
              else 99
            end--DataType, int,
           ,case
                when ea.AttributeType = 2 and ea.IsRequired = true then 3
                when ea.AttributeType = 2 and ea.IsRequired = false then 2
                when ea.IsRequired = true then 1
                else 0
            end--Multiplicity, int,
           ,_dtoId--DtoModel, uniqueidentifier,
           ,case
              when ea.IsCalculated = true or ea.MappingType in (4, 2) then 1
              else (1 | 2)
            end--TwoWayBinding, bit,
           ,case 
                when ea.DefaultFormat is null and ea.SystemType in ('date', 'datetime') then 'yyyy-MM-dd'
                else REPLACE(REPLACE(ea.DefaultFormat, '{0:', ''), '}', '')
            end--StringFormat, varchar(255)
           ,(
                select m.DtoModelID 
                from VCLib.T_DTO_MODEL m
                   join V_ENTITY_HIERARCHY ae on m.Entities = ae.BaseEntity
                   join VCLib.T_WEB_APP a on a.WebAppID = m.WebApp
                where (m.Name is null or m.Name = '')
                          and 
                      ae.FullName = ea.SystemType
                          and
                      a.Name = _app
                order by ae.Depth desc
                limit 1
            )--ItemDtoModel, uniqueidentifier,
           ,true--IsInvariantCulture, bit,
           ,0--Postion, int,
           ,ea.Description
           ,ea.ChoiceAttributeData--ChoiceData, varchar(2000),
from (
        select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,IsAdditional ,EntitiesAttributesId ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
        from VCLib.T_ENTITIES_ATTRIBUTES
        union all
        select EntityID ,Index ,AttributeType ,Name ,Description ,SystemType ,MaxLength ,Precision ,Scale ,DefaultFormat ,IsCalculated ,IsFlag ,IsRequired ,MappingType ,MappingName ,IsStored ,Save ,LinkAttributeName ,ChoiceAttributeData ,AttrLexem ,PathsAttributes ,IsCashed ,false ,EntityAttributesID ,MappingTable ,MappingRefTable ,ParametersData ,CustomsData ,RequiredAttributes ,SortingAttribute ,EnumType ,IsIdentity ,IsCustom ,RemoveAction ,IsPrimaryKey ,RefTableSearchMode ,RefTableHashFunc ,AttributeId
        from V_ENTITY_ATTRIBUTES
    ) ea 
where ea.EntityID in (select ae.BaseEntity from V_ENTITY_HIERARCHY ae where ae.FullName = _entity)
        and
    <xsl:choose>
      <xsl:when test="Ignore">
    (ea.Name not in (''<xsl:for-each select="Ignore">,'<xsl:value-of select="@Name"/>'</xsl:for-each>))
        and
      </xsl:when>
    </xsl:choose>
    not exists
    (
        select *
        from VCLib.T_DTO_ITEM di
        where di.DtoModel = _dtoId
                and
            (ea.Name = di.Name or ea.IsPrimaryKey = true and di.Name =  'id')
    );

  </xsl:template>
  <!--Шаблон для генерации отображения модели DTO-->
  <xsl:template name="DtoModelView">
INSERT INTO VCLib.T_DTO_MODEL_VIEW
           (DtoModelViewID ,DtoModel)
SELECT
           gen_random_uuid(), _dtoId;
          
INSERT INTO VCLib.T_DTO_MODEL_COLUMN
           (DtoModelColumnID ,DtoModelView ,ColPath ,ColDescr ,ColOrder ,IsVisible ,FormatType)
SELECT
           gen_random_uuid()
           ,v.DtoModelViewID
           ,case
                when di.DataType = 99 then di.Name || '.displayName'
                else di.Name
            end
           ,di.Descr
           ,di.Postion
           ,true
           ,0
from VCLib.T_DTO_ITEM di
    join VCLib.T_DTO_MODEL_VIEW v on v.DtoModel = di.DtoModel and (v.Context is null or v.Context = '')
where di.DtoModel = _dtoId
        and
    di.Name != 'id'
        and
  (di.DataType != 99 or di.ItemDtoModel is not null)
        and
    di.Multiplicity &lt; 2;
  </xsl:template>

  <!--Загрузка Web алиасов сущностей-->
  <xsl:template match="root/webAliases[(alias)]" name="loadAppAliases">

    <xsl:for-each select="alias">
DO 
$$ 
DECLARE
         _aliasId uuid;
BEGIN 
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="loadWebAlias"/>
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

    <xsl:for-each select="alias[@default='1']">
DO 
$$ 
DECLARE
         _aliasId uuid;
BEGIN 
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="setDefaultWebAlias"/>
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

    <xsl:for-each select="alias[@inherits!='']">
DO 
$$ 
DECLARE
         _aliasId uuid;
BEGIN 
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="setInheritedAlias"/>
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
    </xsl:for-each>

  </xsl:template>
  <!--Шаблон для получения идентификатора alias-а-->
  <xsl:template name="getWebAliasID">
select COALESCE((select wa.EntitiesWebAliasID
from VCLib.T_ENTITIES_WEB_ALIAS wa
    join VCLib.T_WEB_APP a on a.WebAppID = wa.WebApp
where wa.Alias = '<xsl:value-of select="@name" />'
        and
    a.Name = '<xsl:choose>
                <xsl:when test="@App"><xsl:value-of select="@App" /></xsl:when>
                <xsl:when test="../../@hosted"><xsl:value-of select="name(../..)" /></xsl:when>
                <xsl:otherwise>api</xsl:otherwise>
              </xsl:choose>'), gen_random_uuid())
into _aliasId;
  </xsl:template>
  <!--Шаблон загрузки основной информации по alias-у-->
  <xsl:template name="loadWebAlias">
    INSERT INTO VCLib.T_ENTITIES_WEB_ALIAS (EntitiesWebAliasID, Alias ,Entities ,Filter, Descr, IsDefault, WebApp, DefaultTemplateName, PageRowCountMax)
    SELECT
    _aliasId
    ,'<xsl:value-of select="@name" />'
          ,e.EntityID
          ,<xsl:choose>
            <xsl:when test="./filter[(*)]" >'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="filter/*"/></xsl:call-template>'</xsl:when>
            <xsl:otherwise>null</xsl:otherwise>
          </xsl:choose>
          ,<xsl:choose><xsl:when test="@description != ''">'<xsl:value-of select="@description" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,'0'
          ,(select WebAppID from VCLib.T_WEB_APP a where a.Name = '<xsl:choose>
                                                                  <xsl:when test="@App"><xsl:value-of select="@App" /></xsl:when>
                                                                  <xsl:when test="../../@hosted"><xsl:value-of select="name(../..)" /></xsl:when>
                                                                  <xsl:otherwise>api</xsl:otherwise>
                                                                </xsl:choose>')
          ,<xsl:choose><xsl:when test="@template != ''">'<xsl:value-of select="@template" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,1000
from VCLib.T_ENTITIES e
where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity" />'
        and
    not exists ( select * from VCLib.T_ENTITIES_WEB_ALIAS wa where wa.EntitiesWebAliasID = _aliasId);
update VCLib.T_ENTITIES_WEB_ALIAS wa
set Entities = e.EntityID
   ,Filter = <xsl:choose>
               <xsl:when test="./filter[(*)]" >'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="filter/*"/></xsl:call-template>'</xsl:when>
               <xsl:otherwise>null</xsl:otherwise>
             </xsl:choose>
   ,Descr = <xsl:choose><xsl:when test="@description != ''">'<xsl:value-of select="@description" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,DefaultTemplateName = <xsl:choose><xsl:when test="@template != ''">'<xsl:value-of select="@template" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
from VCLib.T_ENTITIES e
where wa.EntitiesWebAliasID = _aliasId
        and
    e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity" />';
    <xsl:if test="@recreateAccess='1'">
delete from VCLib.T_ENTITIES_WEB_ALIAS_ACCESS ewaa
where ewaa.EntitiesWebAlias = _aliasId;
    </xsl:if>
    <xsl:for-each select="access/role">
INSERT INTO VCLib.T_ENTITIES_WEB_ALIAS_ACCESS (EntitiesWebAliasAccessID ,EntitiesWebAlias ,Role ,Access)
SELECT
           gen_random_uuid()--EntitiesWebAliasAccessID, uniqueidentifier,
          ,_aliasId--EntitiesWebAlias, varchar(255),
          ,(select r.RoleID from VCLib.T_ROLES r where r.RoleName = '<xsl:value-of select="@name" />' limit 1)--Role, uniqueidentifier,
          ,<xsl:value-of select="@access" />--Access, int,
where not exists(select * from VCLib.T_ENTITIES_WEB_ALIAS_ACCESS ewaa 
                 join VCLib.T_ROLES r on r.RoleID = ewaa.Role
                 where ewaa.EntitiesWebAlias = _aliasId and r.RoleName = '<xsl:value-of select="@name" />')
        and
    exists(select * from VCLib.T_ROLES r where r.RoleName = '<xsl:value-of select="@name" />')
        and
    exists(select * from VCLib.T_ENTITIES_WEB_ALIAS wa where wa.EntitiesWebAliasID = _aliasId);
    </xsl:for-each>
  </xsl:template>
  <!--Шаблон установки default флага-->
  <xsl:template name="setDefaultWebAlias">
update VCLib.T_ENTITIES_WEB_ALIAS wa
set IsDefault = case when wa.EntitiesWebAliasID = _aliasId then '1' else '0' end::boolean
from VCLib.T_WEB_APP a
where a.WebAppID = wa.WebApp
        and
    wa.Entities = (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity" />')
        and
    a.Name = '<xsl:choose>
                <xsl:when test="@App"><xsl:value-of select="@App" /></xsl:when>
                <xsl:when test="../../@hosted"><xsl:value-of select="name(../..)" /></xsl:when>
                <xsl:otherwise>api</xsl:otherwise>
              </xsl:choose>';
  </xsl:template>
  <!--Шаблон установки базового alias-а-->
  <xsl:template name="setInheritedAlias">
update VCLib.T_ENTITIES_WEB_ALIAS wa
set InheritsAlias = 
    (
        select EntitiesWebAliasID 
        from VCLib.T_ENTITIES_WEB_ALIAS 
            join VCLib.T_WEB_APP a on a.WebAppID = WebApp
        where Alias = '<xsl:value-of select="@inherits" />'
                and
            a.Name = '<xsl:choose>
                        <xsl:when test="@App"><xsl:value-of select="@App" /></xsl:when>
                        <xsl:when test="../../@hosted"><xsl:value-of select="name(../..)" /></xsl:when>
                        <xsl:otherwise>api</xsl:otherwise>
                      </xsl:choose>'
    ) 
where wa.EntitiesWebAliasID = _aliasId;
  </xsl:template>

  <!--Загрузка настраиваемых Web сервисов-->
  <xsl:template match="root/webServices[(service)]">

    <xsl:for-each select="service">
      <xsl:call-template name="loadWebService"/>
delete from VCLib.T_CUSTOM_WEBSERVICE_METHOD_PARAMETER wsmp
using VCLib.T_CUSTOM_WEBSERVICE_METHOD wsm
where wsm.CustomWebserviceMethodID = wsmp.CustomWebserviceMethod and CustomWebservice = '<xsl:value-of select="@id" />';
delete from VCLib.T_CUSTOM_WEBSERVICE_METHOD where CustomWebservice = '<xsl:value-of select="@id" />';
delete from VCLib.T_CUSTOM_WEBSERVICE_ENTITY where CustomWebservice = '<xsl:value-of select="@id" />';
    </xsl:for-each>

    <xsl:for-each select="service/entities/entity">
      <xsl:call-template name="loadWebServiceEntity"/>
    </xsl:for-each>

    <xsl:for-each select="service/methods/method">
      <xsl:call-template name="loadWebServiceMethod"/>
    </xsl:for-each>

    <xsl:for-each select="service/parameters/parameter">
      <xsl:call-template name="loadWebServiceMethodParams"/>
    </xsl:for-each>

  </xsl:template>
  <!--Шаблон загрузки основной информации по сервису-->
  <xsl:template name="loadWebService">
INSERT INTO VCLib.T_CUSTOM_WEBSERVICE
           (CustomWebserviceID
           ,Name
           ,Descr
           ,BaseAddress
           ,Soap11Supported
           ,Soap12Supported
           ,RestSupported
           ,SoapEndpoint
           ,Soap11BindingName
           ,Soap12BindingName
           ,PortTypeName)
SELECT
           '<xsl:value-of select="@id" />'
          ,'<xsl:value-of select="@name" />'
          ,<xsl:choose><xsl:when test="@descr != ''">'<xsl:value-of select="@descr" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,'<xsl:value-of select="@address" />'
          ,'<xsl:value-of select="@soap11" />'
          ,'<xsl:value-of select="@soap12" />'
          ,'<xsl:value-of select="@rest" />'
          ,<xsl:choose><xsl:when test="@endpoint != ''">'<xsl:value-of select="@endpoint" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="@soap11binding != ''">'<xsl:value-of select="@soap11binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="@soap12binding != ''">'<xsl:value-of select="@soap12binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="@portType != ''">'<xsl:value-of select="@portType" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
where not exists ( select * from VCLib.T_CUSTOM_WEBSERVICE ws where ws.CustomWebserviceID = '<xsl:value-of select="@id" />' );
update VCLib.T_CUSTOM_WEBSERVICE ws
set Name = '<xsl:value-of select="@name" />'
   ,Descr = <xsl:choose><xsl:when test="@descr != ''">'<xsl:value-of select="@descr" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,BaseAddress = '<xsl:value-of select="@address" />'
   ,Soap11Supported = '<xsl:value-of select="@soap11" />'
   ,Soap12Supported = '<xsl:value-of select="@soap12" />'
   ,RestSupported = '<xsl:value-of select="@rest" />'
   ,SoapEndpoint = <xsl:choose><xsl:when test="@endpoint != ''">'<xsl:value-of select="@endpoint" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,Soap11BindingName = <xsl:choose><xsl:when test="@soap11binding != ''">'<xsl:value-of select="@soap11binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,Soap12BindingName = <xsl:choose><xsl:when test="@soap12binding != ''">'<xsl:value-of select="@soap12binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,PortTypeName = <xsl:choose><xsl:when test="@portType != ''">'<xsl:value-of select="@portType" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
where ws.CustomWebserviceID = '<xsl:value-of select="@id" />';
  </xsl:template>
  <!--Шаблон загрузки сущностей сервиса-->
  <xsl:template name="loadWebServiceEntity">
INSERT INTO VCLib.T_CUSTOM_WEBSERVICE_ENTITY
           (CustomWebserviceEntityID
           ,Descr
           ,CustomWebservice
           ,EntityName
           ,TextDescription)
SELECT
           '<xsl:value-of select="@id" />'--CustomWebserviceEntityID, uniqueidentifier,
          ,<xsl:choose><xsl:when test="@descr != ''">'<xsl:value-of select="@descr" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>--Descr, varchar(max),
          ,'<xsl:value-of select="../../@id" />'--CustomWebservice, uniqueidentifier,
          ,'<xsl:value-of select="@name" />'--EntityName, varchar(255),
          ,<xsl:choose><xsl:when test="@text != ''">'<xsl:value-of select="@text" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>;--TextDescription, varchar(max),
  </xsl:template>
  <!--Шаблон загрузки методов сервиса-->
  <xsl:template name="loadWebServiceMethod">
INSERT INTO VCLib.T_CUSTOM_WEBSERVICE_METHOD
           (CustomWebserviceMethodID
           ,Descr
           ,MethodLexem
           ,NeedAuth
           ,CustomWebservice
           ,MethodType
           ,Path
           ,ContentType
           ,ContentEncoding
           ,BodyFormat
           ,InputWebServiceEntity
           ,InputAttributePaths
           ,OutputWebServiceEntity
           ,OutputAttributePahs)
SELECT
           '<xsl:value-of select="@id" />'
          ,<xsl:choose><xsl:when test="@descr != ''">'<xsl:value-of select="@descr" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,'<xsl:value-of select="@lexem" />'
          ,'<xsl:value-of select="@auth" />'
          ,'<xsl:value-of select="../../@id" />'
          ,<xsl:value-of select="@method" />
          ,'<xsl:value-of select="@path" />'
          ,<xsl:choose><xsl:when test="@content != ''">'<xsl:value-of select="@content" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="@encoding != ''">'<xsl:value-of select="@encoding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="@bodyFormat != ''"><xsl:value-of select="@bodyFormat" /></xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="input/@entity != ''">(select wse.CustomWebserviceEntityID from VCLib.T_CUSTOM_WEBSERVICE_ENTITY wse where wse.EntityName = '<xsl:value-of select="input/@entity" />' and wse.CustomWebservice = '<xsl:value-of select="../../@id" />') </xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="input/@paths != ''">'<xsl:value-of select="input/@paths" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="output/@entity != ''">(select wse.CustomWebserviceEntityID from VCLib.T_CUSTOM_WEBSERVICE_ENTITY wse where wse.EntityName = '<xsl:value-of select="output/@entity" />' and wse.CustomWebservice = '<xsl:value-of select="../../@id" />') </xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
          ,<xsl:choose><xsl:when test="output/@paths != ''">'<xsl:value-of select="output/@paths" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>;
  </xsl:template>
  <!--Шаблон загрузки параметров методов сервиса-->
  <xsl:template name="loadWebServiceMethodParams">
INSERT INTO VCLib.T_CUSTOM_WEBSERVICE_METHOD_PARAMETER
           (CustomWebserviceMethodParameterID
           ,MethodParameterName
           ,"order"
           ,CustomWebserviceMethod
           ,BaseObjectType
           ,"datatype"
           ,Lexem
           ,ParameterType)
SELECT
           '<xsl:value-of select="@id" />'--CustomWebserviceMethodParameterID, uniqueidentifier,
          ,'<xsl:value-of select="@name" />'--MethodParameterName, varchar(255),
          ,<xsl:value-of select="@order" />--Order, int,
          ,'<xsl:value-of select="@method" />'--CustomWebserviceMethod, uniqueidentifier,
          ,<xsl:choose><xsl:when test="@entity != ''">'<xsl:value-of select="@entity" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>--BaseObjectType, varchar(255),
          ,<xsl:value-of select="@dataType" />--DataType, int,
          ,'<xsl:value-of select="@lexem" />'--Lexem, varchar(255),
          ,<xsl:value-of select="@paramType" />;--ParameterType, int,
  </xsl:template>

  <!--Загрузка настроек валидации-->
  <xsl:template match="root/validation[(condition)]">
    <xsl:for-each select="condition">
INSERT INTO VCLib.T_DATAFORM_VALIDATION (Entities)
SELECT (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity"/>')
where not exists
	(
		select *
		from VCLib.T_DATAFORM_VALIDATION dv
			join VCLib.T_ENTITIES e on e.EntityID = dv.Entities
		where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity"/>'
	);
--PGSQL:GO
INSERT INTO VCLib.T_VALIDATION_CONDITION (ValidationConditionID, ValidationGroup, Condition, Context, ValidationDescription, IsHierarchical, DataformValidation, ValidationType, ServerSide)
SELECT
           '<xsl:value-of select="@id"/>'::uuid--ValidationConditionID, uniqueidentifier
          ,'<xsl:value-of select="@group"/>'--ValidationGroup, varchar(255)
          ,'<xsl:value-of select="@condition"/>'--Condition, varchar(max)
          ,'<xsl:value-of select="@context"/>'--Context, varchar(255)
          ,'<xsl:value-of select="@description"/>'--ValidationDescription, varchar(max)
          ,'<xsl:value-of select="@hierarchical"/>'--IsHierarchical, bit
          ,(select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity"/>')--DataformValidation, int
          ,'<xsl:value-of select="@type"/>'--ValidationType, int
          ,'<xsl:value-of select="@server"/>'--ServerSide, bit,
where not exists
	(
		select *
		from VCLib.T_VALIDATION_CONDITION vc
		where vc.ValidationConditionID = '<xsl:value-of select="@id"/>'::uuid
	);
--PGSQL:GO
update VCLib.T_VALIDATION_CONDITION vc
set ValidationGroup = '<xsl:value-of select="@group"/>'
   ,Condition = '<xsl:value-of select="@condition"/>'
   ,Context = '<xsl:value-of select="@context"/>'
   ,ValidationDescription = '<xsl:value-of select="@description"/>'
   ,IsHierarchical = '<xsl:value-of select="@hierarchical"/>'
   ,DataformValidation = (select e.EntityID from VCLib.T_ENTITIES e where e.Schema || '.' || e.EntName = '<xsl:value-of select="@entity"/>')
   ,ValidationType = '<xsl:value-of select="@type"/>'
   ,ServerSide = '<xsl:value-of select="@server"/>'
where vc.ValidationConditionID = '<xsl:value-of select="@id"/>'::uuid;
--PGSQL:GO
    </xsl:for-each>
  </xsl:template>

  <!--Загрузка действий при изменении объекта-->
  <xsl:template match="ObjectModificationActions/SingleModificationAction|ObjectModificationActions/DependencyModificationAction">
DO 
$$ 
DECLARE
         _ObjectModificationActionID uuid;
BEGIN 
    <xsl:call-template name="getObjectModificationActionID"/>
    -- Обновление существующего действия
    update VCLib.T_OBJECT_MODIFICATION_ACTION oma
    set Position = <xsl:value-of select="@Position" />,
        type_ = (select e.EntityID from VCLib.T_ENTITIES e where e."schema" = 'VCLib' and e.EntName = '<xsl:choose><xsl:when test="name(.) = 'SingleModificationAction'">SINGLE_MODIFICATION_ACTION</xsl:when><xsl:otherwise>DEPENDENCY_MODIFICATION_ACTION</xsl:otherwise></xsl:choose>' limit 1),
        Active = '<xsl:value-of select="@Active" />',
        Instruction = '<xsl:value-of select="Instruction" />',
        InstructionType = <xsl:value-of select="@InstructionType" />
    where oma.ObjectModificationActionID = _ObjectModificationActionID;

    -- Создание нового, если не существует
    INSERT INTO VCLib.T_OBJECT_MODIFICATION_ACTION
               (ObjectModificationActionID
               ,Entities
               ,Position
               ,type_
               ,Active
               ,Name
               ,Instruction
               ,InstructionType)
    SELECT
                _ObjectModificationActionID--ObjectModificationActionID, uniqueidentifier
               ,(select e.EntityID from VCLib.T_ENTITIES e where e."schema" || '.' || e.EntName = '<xsl:value-of select="@Entities" />' limit 1)--Entities, int
               ,<xsl:value-of select="@Position" />--Position, int
               ,(select e.EntityID from VCLib.T_ENTITIES e where e."schema" = 'VCLib' and e.EntName = '<xsl:choose><xsl:when test="name(.) = 'SingleModificationAction'">SINGLE_MODIFICATION_ACTION</xsl:when><xsl:otherwise>DEPENDENCY_MODIFICATION_ACTION</xsl:otherwise></xsl:choose>')--type_, int
               ,'<xsl:value-of select="@Active" />'--Active, bit
               ,'<xsl:value-of select="@Name" />'--Name, varchar(255)
               ,'<xsl:value-of select="Instruction" />'--Instruction, varchar(max)
               ,<xsl:value-of select="@InstructionType" />--InstructionType, int
    where not exists
        (
            select *
            from VCLib.T_OBJECT_MODIFICATION_ACTION oma
            where oma.ObjectModificationActionID = _ObjectModificationActionID
        );

    -- Перезагружаем информацию о параметрах и дополнительных настройках действия
    delete from VCLib.T_DEPENDENCY_MODIFICATION_ACTION
    where ObjectModificationActionID = _ObjectModificationActionID;
    delete from VCLib.T_SINGLE_MODIFICATION_ACTION
    where ObjectModificationActionID = _ObjectModificationActionID;
    INSERT INTO VCLib.T_<xsl:choose><xsl:when test="name(.) = 'SingleModificationAction'">SINGLE_MODIFICATION_ACTION</xsl:when><xsl:otherwise>DEPENDENCY_MODIFICATION_ACTION</xsl:otherwise></xsl:choose>
               (ObjectModificationActionID
                <xsl:choose>
                  <xsl:when test="name(.) = 'SingleModificationAction'">
                    <xsl:text>,InsertFilter</xsl:text>
                    <xsl:text>,UpdateFilter</xsl:text>
                    <xsl:text>,DeleteFilter</xsl:text>
                    <xsl:text>,EditType)</xsl:text>
                  </xsl:when>
                  <xsl:otherwise>
                    <xsl:text>,Dependencies)</xsl:text>
                  </xsl:otherwise>
                </xsl:choose>
    SELECT
                _ObjectModificationActionID--ObjectModificationActionID, uniqueidentifier
                <xsl:choose>
                  <xsl:when test="name(.) = 'SingleModificationAction'">
                    ,<xsl:choose><xsl:when test="InsertFilter">'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="InsertFilter/*"/></xsl:call-template>'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>--InsertFilter, varchar(max)
                    ,<xsl:choose><xsl:when test="UpdateFilter">'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="UpdateFilter/*"/></xsl:call-template>'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>--UpdateFilter, varchar(max)
                    ,<xsl:choose><xsl:when test="DeleteFilter">'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="DeleteFilter/*"/></xsl:call-template>'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>--DeleteFilter, varchar(max)
                    ,<xsl:value-of select="@EditType" />--EditType, int
                  </xsl:when>
                  <xsl:otherwise>
                    ,'<xsl:value-of select="@Dependencies" />'
                  </xsl:otherwise>
                </xsl:choose>;
    delete from VCLib.T_OBJECT_MODIFICATION_ACTION_PARAM
    where ObjectModificationAction = _ObjectModificationActionID;

    <xsl:for-each select="ObjectModificationActionParams/*">
    INSERT INTO VCLib.T_OBJECT_MODIFICATION_ACTION_PARAM
               (ObjectModificationActionParamID
               ,ObjectModificationAction
               ,ParamValue
               ,Name)
    SELECT
                gen_random_uuid()--ObjectModificationActionParamID, uniqueidentifier
               ,_ObjectModificationActionID--ObjectModificationAction, uniqueidentifier
               ,'<xsl:call-template name="serializeNodeToString">
                  <xsl:with-param name="node" select="./*"/>
                </xsl:call-template>'--ParamValue, varchar(max)
               ,'<xsl:value-of select="name()" />';--Name, varchar(255)
    </xsl:for-each>
END
$$ LANGUAGE  plpgsql;
--PGSQL:GO
  </xsl:template>
  <!--Шаблон для получения идентификатора действия-->
  <xsl:template name="getObjectModificationActionID">
select COALESCE((
        select oma.ObjectModificationActionID 
        from VCLib.T_OBJECT_MODIFICATION_ACTION oma
            join VCLib.T_ENTITIES e on e.EntityID = oma.Entities
        where e."schema" || '.' || e.EntName = '<xsl:value-of select="@Entities" />'
                and
            oma.Name = '<xsl:value-of select="@Name" />'
    limit 1), gen_random_uuid())
    into _ObjectModificationActionID;
  </xsl:template>

  <!-- Execute SQL Script -->
  <xsl:template match="executesql|PgScript">
    <xsl:value-of select="text()"/>
  </xsl:template>

  <xsl:template match="Jobs/Job">
    <xsl:variable name="apos">'</xsl:variable>
    <xsl:variable name="quot">"</xsl:variable>
    -- обработка Job с Name = <xsl:value-of select="@Name" />
    do $$

    declare
      _JobID uuid;
      _UserID uuid;
      _JobStepID uuid;
      _OperationID uuid;
      _DataIndex int;
      
    begin

    select tu.UserID into _UserID
    from VCLib.T_USERS as tu
    where exists
    (
      select *
      from VCLib.T_LOGIN as tl
      where tl.Identity = tu.Identity and tl.Login = <xsl:value-of select="@Login" />
    ) limit 1;

    select tj.JobID into _JobID from VCLib.T_JOB as tj where tj.Name = <xsl:value-of select="@Name" /> limit 1;

    if _JobID is null then
      select gen_random_uuid() into _JobID;
      insert into VCLib.T_JOB (
        JobID,
        Name,
        ConcurrentExecute,
        JobUser)
      values (
        _JobID,
        <xsl:value-of select="@Name" />,
        <xsl:value-of select="@ConcurrentExecute" />,
        _UserID);
    else
      update VCLib.T_JOB
      set
        ConcurrentExecute = <xsl:value-of select="@ConcurrentExecute" />,
        JobUser = _UserID,
        Name = <xsl:value-of select="@Name" />
      where JobID = _JobID;
    end if;

    -- обработка JobStep-ов для Job с Name = <xsl:value-of select="@Name" />
    <xsl:for-each select="Steps/Step">
      -- обработка JobStep со Step = <xsl:value-of select="@Step" />

      select tjs.JobStepID into _JobStepID from VCLib.T_JOB_STEP as tjs
        where tjs.Job = _JobID
        and tjs.Step = <xsl:value-of select="@Step" /> limit 1;

      select tso.OperationID into _OperationID from VCLib.T_SCHEDULER_OPERATION as tso
        where tso.Name = <xsl:value-of select="Operation/@Name" />;
      
      select te.entityid into _DataIndex from VCLib.T_ENTITIES as te
        where te.entname = <xsl:value-of select="Operation/@EntityName" />
          and te."schema" = <xsl:value-of select="Operation/@EntitySchema" /> limit 1;

      if _OperationID is null then
        select gen_random_uuid() into _OperationID;
        insert into VCLib.T_SCHEDULER_OPERATION (
          OperationID,
          Name,
          type_,
          ProcessName)
        values (
          _OperationID,
          <xsl:value-of select="Operation/@Name" />,
          _DataIndex,
          <xsl:choose>
            <xsl:when test="Operation/@ProcessName">
              <xsl:value-of select="Operation/@ProcessName"/>
            </xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
          </xsl:choose>);
      else
      update VCLib.T_SCHEDULER_OPERATION
      set
          Name = <xsl:value-of select="Operation/@Name" />,
          type_ = _DataIndex,
          ProcessName = <xsl:choose>
                          <xsl:when test="Operation/@ProcessName">
                            <xsl:value-of select="Operation/@ProcessName"/>
                          </xsl:when>
                          <xsl:otherwise>NULL</xsl:otherwise>
                      </xsl:choose>
      where OperationID = _OperationID;
      end if;

      <xsl:for-each select="Operation/RunLexemOperation">
        <xsl:variable name="newLexem">
          <xsl:call-template name="replace">
            <xsl:with-param name="text" select="Lexem" />
            <xsl:with-param name="replace" select="$apos" />
            <xsl:with-param name="by" select="concat($apos, $apos)" />
          </xsl:call-template>
        </xsl:variable>
        if (select trlo.OperationID from VCLib.T_RUN_LEXEM_OPERATION as trlo
          where trlo.OperationID = _OperationID limit 1) is null then
          insert into VCLib.T_RUN_LEXEM_OPERATION (
            OperationID,
            Lexem)
          values (
            _OperationID,
            '<xsl:value-of select="$newLexem" />');
        else
          update VCLib.T_RUN_LEXEM_OPERATION
          set
            Lexem = '<xsl:value-of select="$newLexem" />'
          where OperationID = _OperationID;
        end if;
      </xsl:for-each>

      if _JobStepID is null then
        select gen_random_uuid() into _JobStepID;
        insert into VCLib.T_JOB_STEP (
          JobStepID,
          Step,
          ContinueOnError,
          Job,
          Operation)
        values (
          _JobStepID,
          <xsl:value-of select="@Step" />,
          <xsl:value-of select="@ContinueOnError" />,
          _JobID,
          _OperationID);
      else
        update VCLib.T_JOB_STEP
        set
          Step = <xsl:value-of select="@Step" />,
          ContinueOnError = <xsl:value-of select="@ContinueOnError" />,
          Job = _JobID,
          Operation = _OperationID
        where JobStepID = _JobStepID;
      end if;
    </xsl:for-each>
    end $$ language plpgsql;
    --PGSQL:GO
  </xsl:template>
  
  <xsl:template match="ArrayOfExtraUICommand">
do $$
declare
  _ExtraUICommandId uuid;
  _Entities int;
  _CommandEntity int;
begin
    <xsl:for-each select="ExtraUICommand">
      <xsl:variable name="index" select="position()"/>
  --record <xsl:value-of select="$index" />
  select e.entityid into _Entities from vclib.t_entities e where e.schema || '.' || e.entname = '<xsl:value-of select="Entities" />' limit 1;
  select e.entityid into _CommandEntity from vclib.t_entities e where e.schema || '.' || e.entname = '<xsl:value-of select="CommandEntity" />' limit 1;
  select e.extrauicommandid into _ExtraUICommandId from vclib.t_extra_ui_command e where e.name  = '<xsl:value-of select="Name" />' and Entities = _Entities and CommandEntity = _CommandEntity limit 1;

  IF _ExtraUICommandId IS NULL THEN
    _ExtraUICommandId = gen_random_uuid();

    INSERT INTO vclib.t_extra_ui_command (
    activationfilter
    , commandentity
    , commandtype
    , context
    , credentials
    , displayattributes
    , entities
    , executiontype
    , extrauicommandid
    , filtervalue
    , instruction
    , instructiontype
    , name
    , opencontext
    , priority
    , queryprocessparams
    , remotecommandid
    , sameobject
    , styletype
    , waytransferobjects
    )
    SELECT
    <xsl:choose><xsl:when test="ActivationFilter/@xsi:nil='true'">NULL</xsl:when><xsl:when test="ActivationFilter">'<xsl:value-of select="ActivationFilter"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as activationfilter
    ,_CommandEntity
    , <xsl:choose><xsl:when test="CommandType"><xsl:value-of select="CommandType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as commandtype
    , <xsl:choose><xsl:when test="Context/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Context">'<xsl:value-of select="Context"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as context
    , <xsl:choose><xsl:when test="Credentials/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Credentials"><xsl:value-of select="Credentials"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as credentials
    , <xsl:choose><xsl:when test="DisplayAttributes/@xsi:nil='true'">NULL</xsl:when><xsl:when test="DisplayAttributes">'<xsl:value-of select="DisplayAttributes"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as displayattributes
    ,_Entities
    , <xsl:choose><xsl:when test="ExecutionType"><xsl:value-of select="ExecutionType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as executiontype
    ,_ExtraUICommandId
    , <xsl:choose><xsl:when test="FilterValue/@xsi:nil='true'">NULL</xsl:when><xsl:when test="FilterValue">'<xsl:value-of select="FilterValue"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as filtervalue
    , <xsl:choose><xsl:when test="Instruction/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Instruction">'<xsl:value-of select="Instruction"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as instruction
    , <xsl:choose><xsl:when test="InstructionType/@xsi:nil='true'">NULL</xsl:when><xsl:when test="InstructionType"><xsl:value-of select="InstructionType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as instructiontype
    , <xsl:choose><xsl:when test="Name">'<xsl:value-of select="Name"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as name
    , <xsl:choose><xsl:when test="OpenContext/@xsi:nil='true'">NULL</xsl:when><xsl:when test="OpenContext">'<xsl:value-of select="OpenContext"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as opencontext
    , <xsl:choose><xsl:when test="Priority"><xsl:value-of select="Priority"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as priority
    , <xsl:choose><xsl:when test="QueryProcessParams/@xsi:nil='true'">NULL</xsl:when><xsl:when test="QueryProcessParams">'<xsl:value-of select="QueryProcessParams"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as queryprocessparams
    , <xsl:choose><xsl:when test="RemoteCommandID/@xsi:nil='true'">NULL</xsl:when><xsl:when test="RemoteCommandID">'<xsl:value-of select="RemoteCommandID"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as remotecommandid
    , <xsl:choose><xsl:when test="SameObject"><xsl:value-of select="SameObject"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as sameobject
    , <xsl:choose><xsl:when test="StyleType/@xsi:nil='true'">NULL</xsl:when><xsl:when test="StyleType">'<xsl:value-of select="StyleType"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as styletype
    , <xsl:choose><xsl:when test="WayTransferObjects/@xsi:nil='true'">NULL</xsl:when><xsl:when test="WayTransferObjects"><xsl:value-of select="WayTransferObjects"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as waytransferobjects;
  ELSE
    UPDATE vclib.t_extra_ui_command set
    activationfilter=<xsl:choose><xsl:when test="ActivationFilter/@xsi:nil='true'">NULL</xsl:when><xsl:when test="ActivationFilter">'<xsl:value-of select="ActivationFilter"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,commandtype=<xsl:choose><xsl:when test="CommandType"><xsl:value-of select="CommandType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,context=<xsl:choose><xsl:when test="Context/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Context">'<xsl:value-of select="Context"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,credentials=<xsl:choose><xsl:when test="Credentials/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Credentials"><xsl:value-of select="Credentials"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,displayattributes=<xsl:choose><xsl:when test="DisplayAttributes/@xsi:nil='true'">NULL</xsl:when><xsl:when test="DisplayAttributes">'<xsl:value-of select="DisplayAttributes"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,executiontype=<xsl:choose><xsl:when test="ExecutionType"><xsl:value-of select="ExecutionType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,filtervalue=<xsl:choose><xsl:when test="FilterValue/@xsi:nil='true'">NULL</xsl:when><xsl:when test="FilterValue">'<xsl:value-of select="FilterValue"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,instruction=<xsl:choose><xsl:when test="Instruction/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Instruction">'<xsl:value-of select="Instruction"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,instructiontype=<xsl:choose><xsl:when test="InstructionType/@xsi:nil='true'">NULL</xsl:when><xsl:when test="InstructionType"><xsl:value-of select="InstructionType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,name=<xsl:choose><xsl:when test="Name">'<xsl:value-of select="Name"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,opencontext=<xsl:choose><xsl:when test="OpenContext/@xsi:nil='true'">NULL</xsl:when><xsl:when test="OpenContext">'<xsl:value-of select="OpenContext"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,priority=<xsl:choose><xsl:when test="Priority"><xsl:value-of select="Priority"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,queryprocessparams=<xsl:choose><xsl:when test="QueryProcessParams/@xsi:nil='true'">NULL</xsl:when><xsl:when test="QueryProcessParams">'<xsl:value-of select="QueryProcessParams"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,remotecommandid=<xsl:choose><xsl:when test="RemoteCommandID/@xsi:nil='true'">NULL</xsl:when><xsl:when test="RemoteCommandID">'<xsl:value-of select="RemoteCommandID"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,sameobject=<xsl:choose><xsl:when test="SameObject"><xsl:value-of select="SameObject"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,styletype=<xsl:choose><xsl:when test="StyleType/@xsi:nil='true'">NULL</xsl:when><xsl:when test="StyleType">'<xsl:value-of select="StyleType"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    ,waytransferobjects=<xsl:choose><xsl:when test="WayTransferObjects/@xsi:nil='true'">NULL</xsl:when><xsl:when test="WayTransferObjects"><xsl:value-of select="WayTransferObjects"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>
    WHERE extrauicommandid = _ExtraUICommandId;

    DELETE FROM vclib.t_extra_ui_action_params WHERE extrauicommand = _ExtraUICommandId;
  END IF;
  
  --record <xsl:value-of select="$index" /> params
  <xsl:for-each select="ExtraUIActionParams/ExtraUIActionParam">
  INSERT INTO vclib.t_extra_ui_action_params
  (
  extrauiactionparamsid
  , extrauicommand
  , filename
  , isoutput
  , name
  , outputorder
  , paramdescription
  , paramvalue
  , resulttype
  , verb
  )
  SELECT
  gen_random_uuid() as extrauiactionparamsid
  , _ExtraUICommandId as extrauicommand
  , <xsl:choose><xsl:when test="FileName/@xsi:nil='true'">NULL</xsl:when><xsl:when test="FileName">'<xsl:value-of select="FileName"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as filename
  , <xsl:choose><xsl:when test="IsOutput"><xsl:value-of select="IsOutput"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as isoutput
  , <xsl:choose><xsl:when test="Name">'<xsl:value-of select="Name"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as name
  , <xsl:choose><xsl:when test="OutputOrder/@xsi:nil='true'">NULL</xsl:when><xsl:when test="OutputOrder"><xsl:value-of select="OutputOrder"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as outputorder
  , <xsl:choose><xsl:when test="ParamDescription">'<xsl:value-of select="ParamDescription"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as paramdescription
  , <xsl:choose><xsl:when test="ParamValue/@xsi:nil='true'">NULL</xsl:when><xsl:when test="ParamValue">'<xsl:value-of select="ParamValue"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as paramvalue
  , <xsl:choose><xsl:when test="ResultType/@xsi:nil='true'">NULL</xsl:when><xsl:when test="ResultType"><xsl:value-of select="ResultType"/></xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as resulttype
  , <xsl:choose><xsl:when test="Verb/@xsi:nil='true'">NULL</xsl:when><xsl:when test="Verb">'<xsl:value-of select="Verb"/>'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose> as verb;
  </xsl:for-each> 
  </xsl:for-each>    
end
$$ language plpgsql;
  </xsl:template>
</xsl:stylesheet>
