<?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 [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" />')
GO
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>
GO
    </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>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;BEGIN&#10;&#09;INSERT INTO VCLib.T_ENTITIES (EntityID, EntName, XML, ServerName, NeedHistory, IsAdditional, [Schema])&#10;&#09;VALUES ((isnull((select top 1 EntityID from VCLib.T_ENTITIES order by EntityID desc), 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;UPDATE dbo.T_ENTITY_IDS SET id = ISNULL((select MAX(e.EntityID) from VCLib.T_ENTITIES e), 0) + 1 WHERE EntName = 'VCLib.ENTITIES' AND FieldName = 'PrimaryKey'&#10;END&#10;GO&#10;&#10;UPDATE VCLib.T_ENTITIES&#10;SET XML='</xsl:text>
    <xsl:value-of select="$xmlForScript"/>
    <xsl:text>',&#10;EntDesc = '</xsl:text>
    <xsl:value-of select="@Description" />
    <xsl:text>',&#10;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;[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;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;BaseEntity = </xsl:text>
    <xsl:choose>
      <xsl:when test="@Inherits">
        <xsl:text>(select top 1 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>')</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;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;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;IsTree = </xsl:text>
    <xsl:choose>
      <xsl:when test="@IsTree">
        <xsl:value-of select="@IsTree"/>
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;UnrestrictedAccess = </xsl:text>
    <xsl:choose>
      <xsl:when test="@UnrestrictedAccess">
        <xsl:value-of select="@UnrestrictedAccess"/>
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;Timestamped = </xsl:text>
    <xsl:choose>
      <xsl:when test="@Timestamped">
        <xsl:value-of select="@Timestamped"/>
      </xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>
    <xsl:text>,&#10;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;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;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;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;GO&#10;&#10;DECLARE @EntId int&#10;SET @EntId = (SELECT TOP 1 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>')&#10;&#10;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;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:value-of select="@IsFlag" />
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsRequired">
        <xsl:choose>
          <xsl:when test="@Required">
            <xsl:value-of select="@Required" />
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="Save">
        <xsl:choose>
          <xsl:when test="@Save">
            <xsl:value-of select="@Save" />
          </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:value-of select="@Cashed" />
          </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:value-of select="@IdentityPrimaryKey" />
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="IsCustom">
        <xsl:choose>
          <xsl:when test="@IsCustom">
            <xsl:value-of select="@IsCustom" />
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="RemoveAction">
        <xsl:choose>
          <xsl:when test="@RemoveAction">
            <xsl:value-of select="@RemoveAction" />
          </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:when test="$map/text()">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:value-of select="$map/@IsStored" />
          </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/text()">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$map/text()" />
            <xsl:text>'</xsl:text>
          </xsl:when>
          <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;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;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;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>SELECT newid(), @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>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>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" />')
                INSERT INTO VCLib.T_COMMAND (CommandID, Name, Enabled, IsDefault, CommandAppearance, Icon, Owner, type_, ClientModules)
                SELECT NEWID(), '<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 TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>'),  @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 TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>')
                WHERE ClientModules = @ID AND Name = '<xsl:value-of select="$command/@Name" />'

            </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" />')
                    BEGIN
                    INSERT INTO VCLib.T_COMMAND (CommandID, Name, Enabled, IsDefault, CommandAppearance, Icon, Owner, type_, ClientModules)
                    SELECT NEWID(), '<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 TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>'), @ID
                    END
                    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 TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>')
                    WHERE ClientModules = @ID AND Name = '<xsl:value-of select="../@Name" />'

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

                    <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">
    
    IF NOT EXISTS(SELECT * FROM [VCLib].[T_SCHEMA] WHERE [SchemaName]='<xsl:value-of select="@Name" />')
    INSERT INTO [VCLib].[T_SCHEMA] ([SchemaName], [Priority]) VALUES ('<xsl:value-of select="@Name"/>', <xsl:value-of select="@Priority"/>)
    GO
    
    <xsl:for-each select ="client_modules/*">
    DECLARE @ownerID  uniqueidentifier
    DECLARE @type INT
    SET @type = (SELECT TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>')
	
    DECLARE @ID uniqueidentifier
    SET @ID = (SELECT TOP 1 ClientModuleID FROM VCLib.T_CLIENT_MODULES WHERE ModuleType = '<xsl:value-of select="@ModuleType" />' and [Schema] = '<xsl:value-of select="../../@Name" />')
	
    IF @ID is NULL
    BEGIN
        SET @ID = NEWID()
        INSERT INTO VCLib.T_CLIENT_MODULES (ClientModuleID, ModuleType, ModuleDescription, [Schema], type_)
	    
        SELECT @ID, '<xsl:value-of select="@ModuleType" />', '<xsl:value-of select="@ModuleDescription" />', '<xsl:value-of select="../../@Name" />', @type
	    
	    --CLIENT_MODULE_FEATURE START
        <xsl:for-each select="client_module_features/client_module_feature">
        INSERT INTO VCLib.T_CLIENT_MODULE_FEATURE (ClientModuleFeatureID, ClientModules, Name, [Description], EntityIndex)
        SELECT NEWID(), @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>
	    --CLIENT_MODULE_FEATURE END
	    
	    --CLIENT_MODULE_FORM START
        <xsl:for-each select ="client_module_forms/client_module_form">
          <xsl:call-template name="ClientModuleForms"/>
        </xsl:for-each>
	    --CLIENT_MODULE_FORM END
          
        --CLIENT_MODULE_COMMAND START
          <xsl:for-each select="./command">
              SET @OwnerID = NULL
              <xsl:call-template name="client_module_commands">

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

              </xsl:call-template>
          </xsl:for-each>
	    --CLIENT_MODULE_COMMAND END
          
	    --CUSTOM_CLIENT_MODULE START
          <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 NEWID(), @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 NEWID(), @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>
	    --CUSTOM_CLIENT_MODULE END
    END
    ELSE
	BEGIN
        UPDATE cm
        SET cm.ModuleDescription = '<xsl:value-of select="@ModuleDescription" />'
        ,cm.[Schema] = '<xsl:value-of select="../../@Name" />'
        ,type_ = @type
        FROM VCLib.T_CLIENT_MODULES cm
        WHERE cm.ClientModuleID = @ID
	    
        --CLIENT_MODULE_FEATURE START
        <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" />')
        INSERT INTO VCLib.T_CLIENT_MODULE_FEATURE (ClientModuleFeatureID, ClientModules, Name, [Description], EntityIndex)
        SELECT NEWID(), @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 cmf
        SET cmf.[Description] = '<xsl:value-of select="@Description" />'
        ,cmf.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>')
        FROM VCLib.T_CLIENT_MODULE_FEATURE cmf
        WHERE cmf.ClientModules = @ID and cmf.Name = '<xsl:value-of select="@Name" />'
        </xsl:for-each>
        --CLIENT_MODULE_FEATURE END
	    
        --CLIENT_MODULE_FORM START
        <xsl:for-each select ="client_module_forms/client_module_form">
          <xsl:call-template name="ClientModuleForms"/>
        </xsl:for-each>
        --CLIENT_MODULE_FORM END
          
        --CLIENT_MODULE_COMMAND START
          <xsl:for-each select="./command">
              SET @OwnerID = NULL
              <xsl:call-template name="client_module_commands">

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

              </xsl:call-template>
          </xsl:for-each>
        --CLIENT_MODULE_COMMAND END
          
        --CUSTOM_CLIENT_MODULE START
          <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" />')
                  INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER ([ClientModuleHandlerID], [CustomClientModule], [HandlerType], [Entities], [ProcessName], [Prepare])
                  SELECT NEWID(), @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
              </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" />')
                  INSERT INTO VCLib.T_CLIENT_MODULE_HANDLER ([ClientModuleHandlerID], [CustomClientModule], [HandlerType], [Entities], [ProcessName], [PrePrepare], [Prepare])
                  SELECT NEWID(), @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
              </xsl:for-each>
          </xsl:if>
        --CUSTOM_CLIENT_MODULE END
    END
    GO
    </xsl:for-each>

    <xsl:for-each select="holders/*">
        DECLARE @type INT
        SET @type = (SELECT TOP 1 EntityID FROM VCLib.T_ENTITIES WHERE EntName = '<xsl:value-of select ="name(.)"/>')
        DECLARE @ID uniqueidentifier
        SET @ID = (SELECT TOP 1 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>)
      IF @ID is NULL
      BEGIN
      SET @ID = NEWID()
      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 NEWID(), @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 NEWID(), @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 NEWID(), @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 NEWID(), @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 NEWID(), @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" />'
      ,type_ = @type
    FROM VCLib.T_HOLDER h
    WHERE h.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" />')
        INSERT INTO VCLib.T_HOLDER_PARAMETER ([HolderParameterID], [Holder], [ParameterName], [Description], [ParameterValue])
        SELECT NEWID(), @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" />'
        <xsl:choose>
          <xsl:when test="@Value">
            ,ParameterValue = '<xsl:value-of select="@Value" />'
          </xsl:when>
        </xsl:choose>
        FROM VCLib.T_HOLDER_PARAMETER hp
        WHERE hp.Holder = @ID and hp.ParameterName = '<xsl:value-of select="@Name" />'
      </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" />')
              INSERT INTO VCLib.T_HOLDER_REFERENCE ([HolderReferenceID], [CustomHolder], [Holder])
              SELECT NEWID(), @ID, '<xsl:value-of select="@RefHolder" />'
        </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" />')
          INSERT INTO VCLib.T_HOLDER_HANDLER ([HolderHandlerID], [CustomHolder], [HandlerType], [Entities], [ProcessName], [Execute], [Postexecute], [Prepare])
          SELECT NEWID(), @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
      BEGIN
          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
      </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" />')
          INSERT INTO VCLib.T_HOLDER_HANDLER ([HolderHandlerID], [CustomHolder], [HandlerType], [Entities], [ProcessName], [Execute], [Postexecute], [PreExecute], [Prepare], [PrePostexecute], [PrePrepare])
          SELECT NEWID(), @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
      BEGIN
          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
      </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" />')
          INSERT INTO VCLib.T_SAFE_LOOP_THREAD ([SafeLoopThreadID], [CustomHolder], [ThreadName], [Interval], [ThreadLexem])
          SELECT NEWID(), @ID, '<xsl:value-of select="@ThreadName" />', '<xsl:value-of select="@Interval" />', '<xsl:value-of select="threadLexem" />'
          ELSE
          BEGIN
          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
      </xsl:for-each>
      </xsl:if>
      END
      GO
    </xsl:for-each>

    <xsl:for-each select ="users/user">
      -- USER
      DECLARE @EntityID INT
      DECLARE @IdentityId uniqueidentifier
      DECLARE @LoginId uniqueidentifier
      DECLARE @userId uniqueidentifier
      DECLARE @type INT
      declare @rc as uniqueidentifier
      DECLARE @contId INT
      declare @DivisionID uniqueidentifier
      declare @salt uniqueidentifier

      if exists(select * from VCLib.T_DIVISIONS)
      set @DivisionID = (select top 1 DivisionID from VCLib.T_DIVISIONS order by TreeLevel)
      else
      begin
      set @DivisionID = newid()
      insert into VCLib.T_DIVISIONS ( DivisionID, Name, FullName )
      values ( @DivisionID, 'Main', 'Main')
      end

      SELECT @type = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'USERS'), @salt = NEWID()

      select top 1 @IdentityId = i.IdentityID, @LoginId = l.LoginID from VCLib.T_IDENTITY i join VCLib.T_LOGIN l on l.[Identity] = i.IdentityID where l.[Login] = '<xsl:value-of select="@Login" />'

      if (@IdentityId is NULL)
      begin

      set @IdentityId = newid()
      set @LoginId = newid()

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

      <!--<xsl:variable name="salt" select="user:createSalt()" />-->
      insert into VCLib.T_LOGIN (LoginID, [Login], AuthenticationType, Password, Salt, Flags, [Identity], Divisions, IsBlocked)
      values (@LoginId, '<xsl:value-of select="@Login" />', 0, (SELECT HASHBYTES('SHA2_512', '<xsl:value-of select="@Password" />'+cast(@salt as varchar(36))) FOR XML PATH(''), BINARY BASE64), @salt, 0, @IdentityId, @DivisionID, 0)

      end
      else
      begin

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

      end

      select top 1 @userId = UserID from VCLib.T_USERS where [Identity] = @IdentityId

      if (@userId is null)
      begin

      set @userId = newid()
      insert into VCLib.T_USERS (UserID, Flags, DivisionID, [Identity], type_)
      values (@userId, 1, @DivisionID, @IdentityId, @type)

      insert into <xsl:value-of select="../../@Name" />.T_USERS (UserID)
      values (@userId)

      end

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

      <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 = @userId
      </xsl:if>

      <xsl:for-each select="UserSearches">
        -- user searches
        <xsl:for-each select="UserSearch">
          set @rc = newid()
          SELECT @EntityID = 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, @userId, '<xsl:value-of select="@SearchName" />', '<xsl:value-of select="." />', <xsl:value-of select="@SimpleFilter" />)
        </xsl:for-each>

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

    <xsl:for-each select ="roles/role">
      -- ROLE
      DECLARE @rc uniqueidentifier

      IF NOT EXISTS (SELECT * from VCLib.T_ROLES WHERE RoleName = '<xsl:value-of select="@RoleName" />' )
      BEGIN
      DECLARE @EnumTypeID int
      DECLARE @EnumID int
      SELECT @EnumTypeID = (SELECT EnumTypeID FROM VCLib.T_ENUM_TYPES WHERE Name = 'Departament')
      SELECT @EnumID = (SELECT min(EnumID) FROM VCLib.T_ENUMS WHERE Name = '<xsl:value-of select="@Departament" />' AND EnumTypeID = @EnumTypeID)
      set @rc = newid()
      INSERT INTO VCLib.T_ROLES (RoleId,RoleName, RoleDesc, Departament, DivisionsLevel)
      VALUES (@rc,'<xsl:value-of select="@RoleName" />','<xsl:value-of select="@RoleDesc" />', @EnumID,
      <xsl:choose>
        <xsl:when test="@DivisionsLevel">
          <xsl:value-of select="@DivisionsLevel" />
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>)
      END
      GO

      <xsl:for-each select="users/user">
        -- USER IN ROLE
        DECLARE @rc uniqueidentifier
        DECLARE @userId uniqueidentifier

        select top 1 @userId = 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" />'

        if (@userId is not null)
        begin

        IF NOT EXISTS ( SELECT * FROM VCLib.T_USERS_ROLES
        WHERE UserId=(SELECT UserId FROM VCLib.T_USERS WHERE UserID = @userId)
        AND RoleId=(SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />') )
        begin
        set @rc = newid()
        INSERT INTO VCLib.T_USERS_ROLES (UserRoleId,UserId,RoleId)
        (
        SELECT
        @rc,
        (SELECT UserID FROM VCLib.T_USERS WHERE UserID = @userId),
        (SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />')
        )
        end
        end
        GO
      </xsl:for-each>

        PRINT 'ВКЛЮЧЕНИЕ КЛИЕНТСКИХ МОДУЛЕЙ И ИХ ФИЧ ДЛЯ РОЛЕЙ. НАЧАЛО.'
		GO
        <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>
        PRINT 'ВКЛЮЧЕНИЕ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. НАЧАЛО.'
		DECLARE @ClientModule uniqueidentifier = (SELECT ClientModuleID FROM VCLib.T_CLIENT_MODULES WHERE ModuleType = '<xsl:value-of select="@ModuleType" />' and [Schema] = '<xsl:value-of select="@Schema" />')
		DECLARE @Role uniqueidentifier = (SELECT RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="$role_name" />')
        DECLARE @RoleClientModule uniqueidentifier = (select RoleClientModuleID from VCLib.T_ROLE_CLIENT_MODULES where ClientModuleID = @ClientModule and RoleID = @Role)
		
        IF (@ClientModule is not null)
        BEGIN --0
            IF (@RoleClientModule is null)
            BEGIN
			    PRINT 'КЛИЕНТСКИЙ МОДУЛЬ <xsl:value-of select="$module_name" /> БУДЕТ ВКЛЮЧЕН ДЛЯ РОЛИ <xsl:value-of select="$role_name" />'
				
                set @RoleClientModule= newid()
				
                INSERT INTO VCLib.T_ROLE_CLIENT_MODULES (RoleClientModuleID, ClientModuleID,RoleId, LoadOrder)
                SELECT
                @RoleClientModule
                , @ClientModule
                , @Role
                , 0
            END
           
		    <xsl:if test="client_module_features/node()">
			PRINT 'ВКЛЮЧАЕМ ФИЧИ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. НАЧАЛО'
			declare @FeatureEntityIndex int
			declare @FeatureId uniqueidentifier
			declare @FeatureEnabled bit = 0
			<xsl:for-each select="client_module_features/client_module_feature">
			--ВКЛЮЧЕНИЕ ФИЧИ. НАЧАЛО
			--ПРОВЕРКА СУЩЕСТВУЕТ ЛИ ФИЧА
			set @FeatureEntityIndex = (select top 1 EntityId from vclib.T_ENTITIES where EntName = '<xsl:value-of select="@EntityIndexName" />' and [Schema] = '<xsl:value-of select="@EntityIndexSchemaName" />')
			set @FeatureId = (select top 1 ClientModuleFeatureID from vclib.T_CLIENT_MODULE_FEATURE where Name = '<xsl:value-of select="@Name" />' and EntityIndex = @FeatureEntityIndex and ClientModules = @ClientModule)
			--ПРОВЕРКА ВКЛЮЧЕНА ЛИ ФИЧА
			set @FeatureEnabled = 0
			if exists(select top 1 1 from vclib.T_BASE_FEATURE_SETTINGS where ClientModuleFeature = @FeatureId and RoleClientModules = @RoleClientModule)
			    set @FeatureEnabled = 1
			IF (@FeatureId is not null and @FeatureEnabled = 0)
			BEGIN
			    PRINT 'ВКЛЮЧАЕМ ФИЧУ <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
			    (newid()
			    ,@FeatureId
			    ,@RoleClientModule
			    ,@FeatureEntityIndex)
			END
			--ВКЛЮЧЕНИЕ ФИЧИ. КОНЕЦ
			</xsl:for-each>
			PRINT 'ВКЛЮЧАЕМ ФИЧИ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="$module_name" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. КОНЕЦ'
            </xsl:if>
        END --0
		PRINT 'ВКЛЮЧЕНИЕ КЛИЕНТСКОГО МОДУЛЯ <xsl:value-of select="@Schema" />.<xsl:value-of select="@ModuleType" /> ДЛЯ РОЛИ <xsl:value-of select="$role_name" />. КОНЕЦ.'
        GO
        </xsl:for-each>
	    print 'ВКЛЮЧЕНИЕ КЛИЕНТСКИХ МОДУЛЕЙ И ИХ ФИЧ ДЛЯ РОЛЕЙ. КОНЕЦ.'

      <xsl:for-each select="access_entries/access_entry">
        -- roles - access_entries
        declare @rc uniqueidentifier
        declare @role uniqueidentifier
        declare @entryid uniqueidentifier

        SELECT @role = RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />'
        select @entryid = '<xsl:value-of select="@EntryID" />'

        IF NOT EXISTS ( SELECT * FROM VCLib.T_BAE_ROLES
        where entryid = @entryid
        AND RoleId=@role )
        begin
        SET @rc= newid()
        INSERT INTO VCLib.T_BAE_ROLES (EntryRoleID, RoleID,EntryID)
        (
        SELECT
        @rc,
        @role,
        @entryid
        )

        end
        GO
      </xsl:for-each>

      <xsl:for-each select="access_reports/access_report">
        IF EXISTS (SELECT ReportId FROM VCLib.T_Report WHERE ReportId='<xsl:value-of select="@ReportID"/>')
        BEGIN
        DECLARE @repRoleID uniqueidentifier
        DECLARE @roleID uniqueidentifier
        DECLARE @rights int
        SET @repRoleID=newid()
        SELECT @roleId=RoleId FROM VCLib.T_ROLES WHERE RoleName='<xsl:value-of select="ancestor::role/@RoleName" />'
        SELECT @rights = 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"/>',@roleId,@rights)
        END
        GO
      </xsl:for-each>

      UPDATE mrs
      SET mrs.LoadOrder = t.LoadOrder
      FROM VCLib.T_ROLE_CLIENT_MODULES mrs
      join (
      SELECT rcm.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 ON t.RoleClientModuleID = mrs.RoleClientModuleID
      GO
    </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 NEWID()
              ,@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/CommonUserColumns">
    <xsl:for-each select="UserColumn">
		-- user columns
		DECLARE @EntityID INT
		DECLARE @rc uniqueidentifier

		DECLARE @ShowGrid bit
		DECLARE @Scale int
		DECLARE @Context varchar(255)
		DECLARE @ViewName varchar(255)
		DECLARE @ActiveContext bit
		DECLARE @contId uniqueidentifier

		DECLARE @ReadOnly bit
		DECLARE @SortingLexem varchar(max)
		DECLARE @CustomSortingEnabled bit
		DECLARE @UserContextType int

		SELECT @EntityID = EntityID from VCLib.T_ENTITIES WHERE [Schema] + '.' + [EntName]='<xsl:value-of select="@Entity" />'

      select @Context =    <xsl:choose>
        <xsl:when test="@UserContext">
          '<xsl:value-of select="@UserContext" />'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      select @ShowGrid = <xsl:choose>
        <xsl:when test="@ShowGrid">
          '<xsl:value-of select="@ShowGrid"/>'
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
      select @ViewName = <xsl:choose>
        <xsl:when test="@ViewName">
          '<xsl:value-of select="@ViewName"/>'
        </xsl:when>
        <xsl:otherwise>'По умолчанию'</xsl:otherwise>
      </xsl:choose>
      select @Scale = <xsl:choose>
        <xsl:when test="@Scale">
          '<xsl:value-of select="@Scale"/>'
        </xsl:when>
        <xsl:otherwise>100</xsl:otherwise>
      </xsl:choose>
      select @ActiveContext = <xsl:choose>
        <xsl:when test="@ActiveContext">
          '<xsl:value-of select="@ActiveContext"/>'
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
      select @ReadOnly = <xsl:choose>
        <xsl:when test="@ReadOnly">
          '<xsl:value-of select="@ReadOnly"/>'
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
      select @SortingLexem = '<xsl:value-of select="@SortingLexem" />'
      select @CustomSortingEnabled = <xsl:choose>
        <xsl:when test="@CustomSortingEnabled">
          <xsl:value-of select="@CustomSortingEnabled"/>
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
      select @UserContextType = <xsl:choose>
        <xsl:when test="@UserContextType">
          <xsl:value-of select="@UserContextType"/>
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose>
		
      if (@Context is NULL)
      begin
      SELECT @contId = (SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE
      (Context is NULL AND EntityID = @EntityID AND UserID is NULL AND ViewName = @ViewName))
      end
      else
      begin
      SELECT @contId = (SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE
      (Context = @Context AND EntityID = @EntityID AND UserID is NULL and ViewName = @ViewName))
      end

      if (@contId is NULL)
      begin
      set @contId = newid()
      INSERT INTO VCLib.T_USER_CONTEXTS ([UserContextID], [EntityID], [UserID], [Context], [ShowGrid], [ScalePercent], [ViewName], [ActiveContext], [ReadOnly], [SortingLexem], [CustomSortingEnabled], [UserContextType])
      VALUES (@contId, @EntityID, null, @Context, @ShowGrid, @Scale, @ViewName, @ActiveContext, @ReadOnly, @SortingLexem, @CustomSortingEnabled, @UserContextType)
      end

      set @rc = newid()

      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)

      GO
      
    </xsl:for-each>
  </xsl:template>
  
  <xsl:template match="root/CommonUserSearches">
    -- user searches
    DECLARE @EntityID INT
    DECLARE @rc uniqueidentifier

    <xsl:for-each select="UserSearch">
      set @rc = newid()
      SELECT @EntityID = 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>
    GO

  </xsl:template>
    
    <xsl:template match="root/UserContexts">
        <xsl:for-each select="UserContext">
            DECLARE @EntityID INT
            DECLARE @CurrentContext varchar(255)
            DECLARE @ViewName varchar(255)
            DECLARE @CurrentUserID uniqueidentifier
            DECLARE @ActiveContext bit
            DECLARE @ShowGrid bit
            DECLARE @Scale int
            DECLARE @contId uniqueidentifier
            DECLARE @ReadOnly bit
            DECLARE @SortingLexem varchar(max)
            DECLARE @ucids table (id uniqueidentifier)
            DECLARE @CustomSortingEnabled bit
            DECLARE @UserContextType int

            SELECT @CurrentContext =<xsl:choose>
                <xsl:when test="@Context">
                    '<xsl:value-of select="@Context" />'
                </xsl:when>
                <xsl:otherwise>NULL</xsl:otherwise>
            </xsl:choose>
            SELECT @CurrentUserID =<xsl:choose>
                <xsl:when test="@UserID">
                    '<xsl:value-of select="@UserID" />'
                </xsl:when>
                <xsl:otherwise>NULL</xsl:otherwise>
            </xsl:choose>
            select @ShowGrid = <xsl:choose>
                <xsl:when test="@ShowGrid">
                    '<xsl:value-of select="@ShowGrid"/>'
                </xsl:when>
                <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
            select @ViewName = <xsl:choose>
                <xsl:when test="@ViewName">
                    '<xsl:value-of select="@ViewName"/>'
                </xsl:when>
                <xsl:otherwise>'По умолчанию'</xsl:otherwise>
            </xsl:choose>
            select @Scale = <xsl:choose>
                <xsl:when test="@Scale">
                    '<xsl:value-of select="@Scale"/>'
                </xsl:when>
                <xsl:otherwise>100</xsl:otherwise>
            </xsl:choose>
            select @ActiveContext = <xsl:choose>
                <xsl:when test="@ActiveContext">
                    '<xsl:value-of select="@ActiveContext"/>'
                </xsl:when>
                <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
            select @ReadOnly = <xsl:choose>
                <xsl:when test="@ReadOnly">
                    '<xsl:value-of select="@ReadOnly"/>'
                </xsl:when>
                <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
            select @SortingLexem = '<xsl:value-of select="@SortingLexem" />'
            select @CustomSortingEnabled = <xsl:choose>
              <xsl:when test="@CustomSortingEnabled">
                <xsl:value-of select="@CustomSortingEnabled"/>
              </xsl:when>
              <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
            select @UserContextType = <xsl:choose>
              <xsl:when test="@UserContextType">
                <xsl:value-of select="@UserContextType"/>
              </xsl:when>
              <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>

            SELECT @EntityID = EntityID from VCLib.T_ENTITIES WHERE [Schema] + '.' + [EntName]='<xsl:value-of select="@Entity" />'

            -- удаляем и сбрасываем ActiveContext
            if(@CurrentUserID is NULL)
            begin
                if (@CurrentContext is null)
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context is null AND ViewName = @ViewName
                else
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context = @CurrentContext AND ViewName = @ViewName

                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)
                begin
                if (@CurrentContext is null)
                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
            end
            else if(@CurrentUserID = '')
            begin
                if (@CurrentContext is null)
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context is null AND ViewName = @ViewName AND UserID is null
                else
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context = @CurrentContext AND ViewName = @ViewName AND UserID is null

                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)
                begin
                if (@CurrentContext is null)
                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
            end
            else
                begin
                if (@CurrentContext is null)
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context is null AND ViewName = @ViewName AND UserID = @CurrentUserID
                else
                insert @ucids SELECT UserContextID FROM VCLib.T_USER_CONTEXTS WHERE EntityID = @EntityID AND Context = @CurrentContext AND ViewName = @ViewName AND UserID = @CurrentUserID

                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)
                begin
                if (@CurrentContext is null)
                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
            end

            -- добавляем новый T_USER_CONTEXTS
            select @contId = newid()
            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(newid(), '<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>
            GO
        </xsl:for-each>
        
    </xsl:template>
  
  <xsl:template match="EnumTypes">
    <xsl:apply-templates select="EnumType" />

    declare @FieldName varchar(256)
    set @FieldName = 'PrimaryKey'
    declare @isexist int

    set @isexist = (select id from T_ENTITY_IDS where EntName = 'ENUM_TYPES' and FieldName = @FieldName)
    if @isexist is null
    begin
    insert T_ENTITY_IDS (id, EntName, FieldName) values(<xsl:value-of select="@next-enum-type-id" />, 'ENUM_TYPES', @FieldName)
    end
    else
    begin
    UPDATE T_ENTITY_IDS SET [id]=<xsl:value-of select="@next-enum-type-id" />
    WHERE [EntName]='ENUM_TYPES'
    end

    set @isexist = (select id from T_ENTITY_IDS where EntName = 'ENUMS' and FieldName = 'PrimaryKey')
    if @isexist is null
    begin
    insert T_ENTITY_IDS (id, EntName, FieldName) values(<xsl:value-of select="@next-enum-id" />, 'ENUMS', @FieldName)
    end
    else
    begin
    UPDATE T_ENTITY_IDS SET [id]=<xsl:value-of select="@next-enum-id" />
    WHERE [EntName]='ENUMS'
    end
    GO

  </xsl:template>
  
  <xsl:template match="EnumType">
    -- EnumType
    --DECLARE @ID INT
    --DECLARE @rc INT
    DECLARE @typeT INT
    DECLARE @typeE INT
    DECLARE @id INT

    --exec @id = sp_GenerateIdForEntity 'ENUM_TYPES'
    <xsl:variable name="EnumTypeId" select="@id" />

    SELECT @id = (SELECT EnumTypeId FROM VCLib.T_ENUM_TYPES WHERE EnumTypeId = <xsl:value-of select="$EnumTypeId" />)
    IF @id IS NOT NULL

    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" />

    ELSE

    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" />');
    --SELECT @ID = IDENT_CURRENT('VCLib.T_ENUM_TYPES')

    <xsl:for-each select="Enum">
      --exec @rc = sp_GenerateIdForEntity 'ENUMS'

      SELECT @id = (SELECT EnumId FROM VCLib.T_ENUMS WHERE EnumId = <xsl:value-of select="@id" />)
      IF @id IS NOT NULL

      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" />');
    </xsl:for-each>
    GO

  </xsl:template>
  
  <xsl:template match="root/process_access_entries/process_access_entry">
    -- PROCESS_ACCESS_ENTRY
    declare @type int
    declare @id uniqueidentifier
    declare @entryid uniqueidentifier
    select @entryid = '<xsl:value-of select="@EntryID" />'

    SELECT @type = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'PROCESS_ACCESS_ENTRY')

    SELECT @id = (SELECT EntryID FROM VCLib.T_BASE_ACCESS_ENTRY WHERE EntryID = @entryid)
    IF @id IS NOT NULL

    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)

    SELECT @id = (SELECT EntryID FROM VCLib.T_PROCESS_ACCESS_ENTRY WHERE EntryID = @entryid)
    IF @id IS NOT NULL

    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" />);

    GO

  </xsl:template>
  
  <xsl:template match="root/entity_access_entries/entity_access_entry">
    -- ENTITY_ACCESS_ENTRY
    DECLARE @type INT
    declare @id uniqueidentifier
    declare @entryid uniqueidentifier
    select @entryid = '<xsl:value-of select="@EntryID" />'

    SELECT @type = (SELECT EntityID FROM VCLib.T_ENTITIES WHERE EntName = 'ENTITY_ACCESS_ENTRY')

    SELECT @id = (SELECT EntryID FROM VCLib.T_BASE_ACCESS_ENTRY WHERE EntryID = @entryid)
    IF @id IS NOT NULL

    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);

    SELECT @id = (SELECT EntryID FROM VCLib.T_ENTITY_ACCESS_ENTRY WHERE EntryID = @entryid)
    IF @id IS NOT NULL

    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" />);

    GO

  </xsl:template>
  
  <xsl:template match="root/entity_attribute_access_entries/entity_attribute_access_entries">
    -- ENTITY_ATTRIBUTE_ACCESS_ENTRY
    DECLARE @type INT
    declare @entryid uniqueidentifier
    select @entryid = '<xsl:value-of select="@EntryID" />'

    SELECT @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" />);
    GO

  </xsl:template>
  
  <xsl:template match="root/enum_access_entries/enum_access_entry">
    -- ENUM_ACCESS_ENTRY
    DECLARE @type INT

    declare @entryid uniqueidentifier
    select @entryid = newid()

    SELECT @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" />);
    GO

  </xsl:template>
  
  <xsl:template match="root/reportformats/reportformat">
    -- ReportFileFormat
    DECLARE @ID uniqueidentifier

    SELECT @ID = RepFmtId FROM VCLib.T_ReportFileFormat WHERE Name = '<xsl:value-of select="@FormatName"/>'
    IF @ID IS NOT NULL
    BEGIN
        UPDATE VCLib.T_ReportFileFormat SET
        Name = '<xsl:value-of select="@FormatName"/>',
        HandlerName = '<xsl:value-of select="@HandlerName"/>'
        WHERE RepFmtId = @ID
    END
    ELSE
    BEGIN
        set @ID = <xsl:choose>
          <xsl:when test="@id != ''">
            <xsl:text>'</xsl:text>
            <xsl:value-of select="@id"/>
            <xsl:text>'</xsl:text>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>newid()</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
    GO
  </xsl:template>

  <xsl:template match="root/Reports">
    <xsl:for-each select="Report">
      -- Report
      DECLARE
      @ID uniqueidentifier,
      @ExistID uniqueidentifier,
      @repName varchar(100),
      @groupID uniqueidentifier,
      @entID int,
      @repLoadDataType int,
      @repFormTypeTable int,
      @repFormatType uniqueidentifier,
      @type int

      BEGIN
      SET @ID = '<xsl:value-of select="@ID"/>'
      SET @repName = '<xsl:value-of select="@Name"/>'
      SELECT @repLoadDataType = EnumID FROM VCLib.T_ENUMS
      WHERE EnumTypeID = (select EnumTypeID from VCLib.T_ENUM_TYPES where Name='ReportLoadDataType')
      AND Alias='<xsl:value-of select="@ReportLoadDataType"/>'
      SELECT @repFormTypeTable = EnumID FROM VCLib.T_ENUMS
      WHERE EnumTypeID = (select EnumTypeID from VCLib.T_ENUM_TYPES where Name='ReportFormType')
      AND Alias='<xsl:value-of select="@ReportFormType"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="@Entity"/>'
      SELECT @type = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='Report'

      SELECT @ExistID = ReportID FROM VCLib.T_BaseReport WHERE ReportID = @ID
      IF @ExistID IS NOT NULL

      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)

      SELECT @ExistID = ReportID FROM VCLib.T_Report WHERE ReportID = @ID
      IF @ExistID IS NOT NULL

      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>)

      --ReportFileGroup
      <xsl:for-each select="ReportFileGroup">
        SET @groupID = newid();

        SELECT @ExistID = 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

        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>)

        --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"/>'

          SELECT @repFormatType = RepFmtID FROM VCLib.T_ReportFileFormat
          WHERE Name = '<xsl:value-of select="@Format"/>'

          SELECT @ExistID = FileID FROM VCLib.T_ReportFile WHERE FileID = '<xsl:value-of select="@ID"/>'
          IF @ExistID IS NOT NULL

          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>)

          --Parameters
          <xsl:for-each select="Parameter">
            SET @tempID = newid();

            SELECT @ExistID = ParamID FROM VCLib.T_ReportFileParameter WHERE FileID = '<xsl:value-of select="@ID"/>' AND Name = '<xsl:value-of select="@Name"/>'
            IF @ExistID IS NOT NULL

            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="."/>')
          </xsl:for-each>
        </xsl:for-each>
      </xsl:for-each>
      END
      GO
    </xsl:for-each>
  </xsl:template>

  <xsl:template match="root/kbks/kbk">
    -- KBK
    DECLARE @id uniqueidentifier

    SELECT @id = ID FROM T_KBK WHERE Code = '<xsl:value-of select="@Code" />'
    IF @id IS NOT NULL

    UPDATE T_KBK SET
    Name = '<xsl:value-of select="@Name" />'
    WHERE ID = @id

    ELSE

    INSERT INTO T_KBK
    (ID, Code, Name)
    VALUES
    (newid(),
    '<xsl:value-of select="@Code" />',
    '<xsl:value-of select="@Name" />')
    GO
  </xsl:template>

  <xsl:template match="root/kbks/childlist/kbk">
    -- KBK childs
    declare @parentid uniqueidentifier
    declare @id uniqueidentifier
    select @parentid = id from t_kbk where code = '<xsl:value-of select="ancestor::childlist/@ParentCode" />'

    SELECT @id = ID FROM T_KBK WHERE ParentID = @parentid AND Code = '<xsl:value-of select="@Code" />'
    IF @id IS NOT NULL

    UPDATE T_KBK SET
    Name = '<xsl:value-of select="@Name" />',
    ParentID = @parentid
    WHERE ID = @id

    ELSE

    INSERT INTO T_KBK
    (ID, Code, Name, ParentID)
    VALUES
    (    newid(),
    '<xsl:value-of select="@Code" />',
    '<xsl:value-of select="@Name" />',
    @parentid
    )
    GO
  </xsl:template>

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

  <!-- GENERIC REPORTS -->
  <xsl:template match="StaticGroups">
    IF EXISTS (select 0 from VCLib.T_STATIC_GROUPS where StaticGroupsID = '<xsl:value-of select="StaticGroupsID"/>')
    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>)

    <xsl:apply-templates select="StaticGroups" />

  </xsl:template>

  <xsl:template match="root/GenericReports">
    -- GenericReportGenerators
    <xsl:for-each select="GenericReportGeneratorStaticGroups">
      DECLARE
      @generatorID uniqueidentifier,
      @genName varchar(255),
      @entID int,
      @generatorType int

      BEGIN
      SET @generatorID = '<xsl:value-of select="ID"/>'
      SET @genName = '<xsl:value-of select="Name"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>'
      SELECT @generatorType = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='GR_STATIC_GROUP_GENERATOR'

      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GENERATOR where GenericReportGeneratorID = @generatorID)
      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)

      <xsl:apply-templates select="StaticGroups" />

      END
      GO
    </xsl:for-each>

    <xsl:for-each select="GenericReportGeneratorGroupByPath">
      DECLARE
      @generatorID uniqueidentifier,
      @genName varchar(255),
      @entID int,
      @generatorType int

      BEGIN
      SET @generatorID = '<xsl:value-of select="ID"/>'
      SET @genName = '<xsl:value-of select="Name"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>'
      SELECT @generatorType = 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)
      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)

      <xsl:for-each select="Groupbys">
        IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GROUPBY where GenericReportGroupbyID = '<xsl:value-of select="GenericReportGroupbyID"/>')
        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"/>')
      </xsl:for-each>

      END
      GO
    </xsl:for-each>

    <xsl:for-each select="GenericReportGeneratorCode">
      DECLARE
      @generatorID uniqueidentifier,
      @genName varchar(255),
      @entID int,
      @generatorType int

      BEGIN
      SET @generatorID = '<xsl:value-of select="ID"/>'
      SET @genName = '<xsl:value-of select="Name"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>'
      SELECT @generatorType = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='GR_CODE_GENERATOR'

      IF EXISTS (select 0 from VCLib.T_GENERIC_REPORT_GENERATOR where GenericReportGeneratorID = @generatorID)
      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)

      IF EXISTS (select 0 from T_GR_CODE_GENERATOR where GenericReportGeneratorID = @generatorID)
      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
      GO
    </xsl:for-each>

    <xsl:for-each select="GrResolver">
      DECLARE
      @ID uniqueidentifier,
      @resolverName varchar(255),
      @entID int,
      @columnDescription varchar(64)

      BEGIN
      SET @ID = '<xsl:value-of select="ID"/>'
      SET @resolverName = '<xsl:value-of select="Name"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>'

      IF EXISTS (select 0 from T_GR_RESOLVER where GrResolverID = @ID)
      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)

      <xsl:for-each select="Column">
        SET @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"/>')
        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>
        )
      </xsl:for-each>

      END
      GO
    </xsl:for-each>

    <xsl:for-each select="GenericReport">
      DECLARE @entID int,
      @ID uniqueidentifier,
      @generatorPath varchar(512),
      @columnsPath varchar(512),
      @roleID uniqueidentifier,
      @fileFrmID uniqueidentifier

      BEGIN
      SET @ID = '<xsl:value-of select="ID"/>'
      SELECT @entID = EntityID FROM VCLib.T_ENTITIES
      WHERE EntName='<xsl:value-of select="Entity"/>'
      SET @generatorPath = <xsl:choose>
        <xsl:when test="GeneratorPath">
          '<xsl:value-of select="GeneratorPath"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      SET @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)
      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)

      <xsl:for-each select="GeneratorParam">
        IF EXISTS (select 0 from VCLib.T_GENERATOR_PARAMS where GeneratorParamsID = '<xsl:value-of select="ID"/>')
        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"/>)
      </xsl:for-each>

      <xsl:for-each select="GrRight">
        SELECT @roleID = 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"/>')
        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"/>)
      </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"/>'

        SELECT @fileFrmID = 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"/>')
        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"/>')
      </xsl:for-each>

      END
      GO
    </xsl:for-each>
  </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" >
    declare @fileFormat varchar(255)
           ,@formatID uniqueidentifier
    select @fileFormat = '<xsl:value-of select="../../../../@format"/>'
    select @fileFormat = SUBSTRING(@fileFormat, CHARINDEX('.', @fileFormat, CHARINDEX('.', @fileFormat, 0) + 1) + 1, CHARINDEX(',', @fileFormat, 0) - CHARINDEX('.', @fileFormat, CHARINDEX('.', @fileFormat, 0) + 1) - 1)

    select @formatID = rff.RepFmtID
    from VCLib.T_ReportFileFormat rff
    where rff.HandlerName like '%' + @fileFormat + '%'

    update rc
    set rc.[TemplatePath] = '<xsl:value-of select="@path"/>'
       ,rc.[FileName] = '<xsl:value-of select="@name"/>'
       ,rc.[OutputName] = '<xsl:value-of select="@outputName"/>'
       ,rc.[ReportFileFormat] = @formatID
       ,rc.[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
       ,rc.[OutputFormat] = case
                               when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1
                               when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2
                               else 0
                            end
    from [VCLib].[T_REPORTS_CONFIG] rc
    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"/>'
    )
    GO
  </xsl:template>
  <xsl:template name="TemplateOverrideFileTemplate" >
    declare @fileFormat varchar(255)
           ,@formatID uniqueidentifier
    select @fileFormat = '<xsl:value-of select="../../../../@format"/>'
    select @fileFormat = SUBSTRING(@fileFormat, CHARINDEX('.', @fileFormat, CHARINDEX('.', @fileFormat, 0) + 1) + 1, CHARINDEX(',', @fileFormat, 0) - CHARINDEX('.', @fileFormat, CHARINDEX('.', @fileFormat, 0) + 1) - 1)

    select @formatID = rff.RepFmtID
    from VCLib.T_ReportFileFormat rff
    where rff.HandlerName like '%' + @fileFormat + '%'

    update tmo
    set tmo.[TemplatePath] = '<xsl:value-of select="@path"/>'
       ,tmo.[Printer] = '<xsl:value-of select="@printer"/>'
       ,tmo.OverrideOrder = ISNULL((select MAX(OverrideOrder) from [VCLib].[T_TEMPLATE_OVERRIDE] where ReportsConfig = '<xsl:value-of select="../../@name"/>'), -1) + 1
       ,tmo.[FileName] = '<xsl:value-of select="@name"/>'
       ,tmo.[OutputName] = '<xsl:value-of select="@outputName"/>'
       ,tmo.[ReportFileFormat] = @formatID
       ,tmo.[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
       ,tmo.[OutputFormat] = case
                                when '<xsl:value-of select="@OutputFormat"/>' = 'Pdf' then 1
                                when '<xsl:value-of select="@OutputFormat"/>' = 'Html' then 2
                                else 0
                             end
    from [VCLib].[T_TEMPLATE_OVERRIDE] tmo
    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
               NEWID()
               ,'<xsl:value-of select="@path"/>'
               ,'<xsl:value-of select="@printer"/>'
               ,ISNULL((select MAX(OverrideOrder) from [VCLib].[T_TEMPLATE_OVERRIDE] where ReportsConfig = '<xsl:value-of select="../../@name"/>'), -1) + 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
    )
    GO
  </xsl:template>
  
  <xsl:template match="RefreshEntities">
    exec sp_UpdateEntities
    GO
  </xsl:template>

  <xsl:template match="StartupProcesses/StartupProcess">
    <xsl:text>INSERT INTO [VCLib].[T_STARTUP_PROCESS] (StartupProcessID, ProcessName, ProcessParameters)&#10;VALUES(newid(), '</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
           NEWID()--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,>)
          ,0
          ,<xsl:choose>
            <xsl:when test="@login!=''">(
                select top 1 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" />'
                    ) )
            </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(.)"/>'
    )
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">
declare  @entity varchar(133)
        ,@alias varchar(100)
        ,@descr varchar(255)
        ,@system bit
        ,@type int
        ,@dtoId uniqueidentifier
        ,@dtoViewId uniqueidentifier
        ,@app varchar(255)

    <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
           NEWID()--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
           NEWID()--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
            NEWID()--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 @dtoViewId = ISNULL((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>), NEWID())
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
           NEWID()--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>

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

    <xsl:for-each select="DTO[@Recreate='1']">
declare  @entity varchar(133)
        ,@alias varchar(100)
        ,@descr varchar(255)
        ,@system bit
        ,@type int
        ,@dtoId uniqueidentifier
        ,@app varchar(255)
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DropDTO" />
GO
    </xsl:for-each>

    <xsl:for-each select="DTO">
declare  @entity varchar(133)
        ,@alias varchar(100)
        ,@descr varchar(255)
        ,@system bit
        ,@type int
        ,@dtoId uniqueidentifier
        ,@app varchar(255)
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DTO" />
GO
    </xsl:for-each>

update i
set i.Postion = o.Position
from VCLib.T_DTO_ITEM i
    join (
        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] ,0 ,[EntityAttributesID] ,[MappingTable] ,[MappingRefTable] ,[ParametersData] ,[CustomsData] ,[RequiredAttributes] ,[SortingAttribute] ,[EnumType] ,[IsIdentity] ,[IsCustom] ,[RemoveAction] ,[IsPrimaryKey] ,[RefTableSearchMode] ,[RefTableHashFunc] ,[AttributeId]
                from V_ENTITY_ATTRIBUTES
            ) ea on (ea.Name = i.Name or ea.IsPrimaryKey = 1 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 on o.DtoItemID = i.DtoItemID
GO

    <xsl:for-each select="DTO[@WithView='1']">
declare  @entity varchar(133)
        ,@alias varchar(100)
        ,@descr varchar(255)
        ,@system bit
        ,@type int
        ,@dtoId uniqueidentifier
        ,@app varchar(255)
      <xsl:call-template name="FillDtoParams" />
      <xsl:call-template name="GetDtoId" />
      <xsl:call-template name="DtoModelView" />
GO
    </xsl:for-each>

  </xsl:template>
  <!--Шаблон получения параметров для формирования DTO-->
  <xsl:template name="FillDtoParams">
select   @entity = '<xsl:value-of select="@Entity"/>'
        ,@alias = <xsl:choose>
      <xsl:when test="@Name!=''">'<xsl:value-of select="@Name" />'</xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>
        ,@descr = <xsl:choose>
      <xsl:when test="@Descr!=''">'<xsl:value-of select="@Descr" />'</xsl:when>
      <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>
        ,@system = <xsl:choose>
      <xsl:when test="@IsSystem">'<xsl:value-of select="@IsSystem" />'</xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
        ,@type = <xsl:choose>
      <xsl:when test="@type">'<xsl:value-of select="@type" />'</xsl:when>
      <xsl:otherwise>1</xsl:otherwise>
    </xsl:choose>
        ,@app = <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>
  </xsl:template>
  <!--Шаблон для получения идентификатора DTO-->
  <xsl:template name="GetDtoId">
select @dtoId = ISNULL((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), NEWID())
  </xsl:template>
  <!--Шаблон для удаления загруженных DTO-->
  <xsl:template name="DropDTO">
delete mc
from VCLib.T_DTO_MODEL_COLUMN mc
    join VCLib.T_DTO_MODEL_VIEW mv on mv.DtoModelViewID = mc.DtoModelView
where mv.DtoModel = @dtoId

delete mv
from VCLib.T_DTO_MODEL_VIEW mv
where mv.DtoModel = @dtoId

delete mf
from VCLib.T_DTO_MODEL_FILTER mf
where mf.DtoModel = @dtoId

update mi
set mi.ItemDtoModel = null
from VCLib.T_DTO_ITEM mi
where mi.ItemDtoModel = @dtoId

delete mi
from VCLib.T_DTO_ITEM mi
where mi.DtoModel = @dtoId
  </xsl:template>
  <!--Шаблон для генерации атрибутов в DTO-->
  <xsl:template name="DTO">
update m
set m.Descr = ISNULL(@descr, e.EntDesc),
    m.MaxNestingLevel = 2,
    m.IsSystem = @system
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

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),
           ,ISNULL(@descr, (select top 1 e.EntDesc from VCLib.T_ENTITIES e where e.[Schema] + '.' + e.EntName = @entity))--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 i
set 
    i.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;',
    i.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 = 'GUID' then 3
                  else 99
               end,
    i.Multiplicity = case
                        when ea.AttributeType = 2 and ea.IsRequired = 1 then 3
                        when ea.AttributeType = 2 and ea.IsRequired = 0 then 2
                        when ea.IsRequired = 1 then 1
                        else 0
                    end,
    i.DtoModel = @dtoId,
    i.StringFormat = case 
                        when ea.DefaultFormat is null and ea.SystemType in ('date', 'datetime') then 'yyyy-MM-dd'
                        else REPLACE(REPLACE(ea.DefaultFormat, '{0:', ''), '}', '')
                     end,
    i.ItemDtoModel = (
                      select top 1 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
                   ),
    i.Descr = ea.[Description]
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] ,0 ,[EntityAttributesID] ,[MappingTable] ,[MappingRefTable] ,[ParametersData] ,[CustomsData] ,[RequiredAttributes] ,[SortingAttribute] ,[EnumType] ,[IsIdentity] ,[IsCustom] ,[RemoveAction] ,[IsPrimaryKey] ,[RefTableSearchMode] ,[RefTableHashFunc] ,[AttributeId]
        from V_ENTITY_ATTRIBUTES
    ) ea on (ea.Name = i.Name or ea.IsPrimaryKey = 1 and i.Name =  'id')
            and
        ea.EntityID in (select ae.BaseEntity from V_ENTITY_HIERARCHY ae where ae.EntityID = m.Entities) 
where m.DtoModelID = @dtoId

INSERT INTO [VCLib].[T_DTO_ITEM]
           ([DtoItemID]
           ,[Name]
           ,[ItemLexem]
           ,[DataType]
           ,[Multiplicity]
           ,[DtoModel]
           ,[BindingType]
           ,[StringFormat]
           ,[ItemDtoModel]
           ,[IsInvariantCulture]
           ,[Postion]
           ,[Descr]
           ,[ChoiceData])
SELECT
            NEWID()--DtoItemID, uniqueidentifier,
           ,case
              when ea.IsPrimaryKey = 1 then 'id'
              else LOWER(SUBSTRING(ea.Name, 1, 1)) + SUBSTRING(ea.Name, 2, LEN(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 = 'GUID' then 3
              else 99
            end--DataType, int,
           ,case
                when ea.AttributeType = 2 and ea.IsRequired = 1 then 3
                when ea.AttributeType = 2 and ea.IsRequired = 0 then 2
                else ea.IsRequired
            end--Multiplicity, int,
           ,@dtoId--DtoModel, uniqueidentifier,
           ,case
              when ea.IsCalculated = 1 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 top 1 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
            )--ItemDtoModel, uniqueidentifier,
           ,1--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] ,0 ,[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 = 1 and di.Name =  'id')
    )

  </xsl:template>
  <!--Шаблон для генерации отображения модели DTO-->
  <xsl:template name="DtoModelView">
INSERT INTO VCLib.T_DTO_MODEL_VIEW
           (DtoModelViewID ,DtoModel)
SELECT
           NEWID(), @dtoId;
          
INSERT INTO VCLib.T_DTO_MODEL_COLUMN
           (DtoModelColumnID ,DtoModelView ,ColPath ,ColDescr ,ColOrder ,IsVisible ,FormatType)
SELECT
           NEWID()
           ,v.DtoModelViewID
           ,case
                when di.DataType = 99 then di.Name + '.displayName'
                else di.Name
            end
           ,di.Descr
           ,di.Postion
           ,1
           ,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">
declare  @aliasId uniqueidentifier
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="loadWebAlias"/>
GO
    </xsl:for-each>

    <xsl:for-each select="alias[@default='1']">
declare  @aliasId uniqueidentifier
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="setDefaultWebAlias"/>
GO
    </xsl:for-each>

    <xsl:for-each select="alias[@inherits!='']">
declare  @aliasId uniqueidentifier
      <xsl:call-template name="getWebAliasID"/>
      <xsl:call-template name="setInheritedAlias"/>
GO
    </xsl:for-each>

  </xsl:template>
  <!--Шаблон для получения идентификатора alias-а-->
  <xsl:template name="getWebAliasID">
select @aliasId = ISNULL((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>'), NEWID())
  </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 wa
set wa.Entities = e.EntityID
   ,wa.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>
   ,wa.Descr = <xsl:choose><xsl:when test="@description != ''">'<xsl:value-of select="@description" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,wa.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_WEB_ALIAS] wa
    join VCLib.T_ENTITIES e on e.[Schema] + '.' + e.EntName = '<xsl:value-of select="@entity" />'
where wa.EntitiesWebAliasID = @aliasId
    <xsl:if test="@recreateAccess='1'">
delete ewaa
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
           NEWID()--EntitiesWebAliasAccessID, uniqueidentifier,
          ,@aliasId--EntitiesWebAlias, varchar(255),
          ,(select top 1 r.RoleID from [VCLib].[T_ROLES] r where r.RoleName = '<xsl:value-of select="@name" />')--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 wa
set wa.IsDefault = case when wa.EntitiesWebAliasID = @aliasId then 1 else 0 end
from [VCLib].[T_ENTITIES_WEB_ALIAS] wa
    join VCLib.T_WEB_APP a on a.WebAppID = wa.WebApp
where 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 wa
set wa.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>'
    )
from [VCLib].[T_ENTITIES_WEB_ALIAS] wa
where wa.EntitiesWebAliasID = @aliasId
  </xsl:template>

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

    <xsl:for-each select="service">
      <xsl:call-template name="loadWebService"/>
delete wsmp
from [VCLib].[T_CUSTOM_WEBSERVICE_METHOD_PARAMETER] wsmp
    join [VCLib].[T_CUSTOM_WEBSERVICE_METHOD] wsm on wsm.CustomWebserviceMethodID = wsmp.CustomWebserviceMethod 
where 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" />' )
GO
update ws
set ws.Name = '<xsl:value-of select="@name" />'
   ,ws.Descr = <xsl:choose><xsl:when test="@descr != ''">'<xsl:value-of select="@descr" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,ws.BaseAddress = '<xsl:value-of select="@address" />'
   ,ws.Soap11Supported = <xsl:value-of select="@soap11" />
   ,ws.Soap12Supported = <xsl:value-of select="@soap12" />
   ,ws.RestSupported = <xsl:value-of select="@rest" />
   ,ws.SoapEndpoint = <xsl:choose><xsl:when test="@endpoint != ''">'<xsl:value-of select="@endpoint" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,ws.Soap11BindingName = <xsl:choose><xsl:when test="@soap11binding != ''">'<xsl:value-of select="@soap11binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,ws.Soap12BindingName = <xsl:choose><xsl:when test="@soap12binding != ''">'<xsl:value-of select="@soap12binding" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
   ,ws.PortTypeName = <xsl:choose><xsl:when test="@portType != ''">'<xsl:value-of select="@portType" />'</xsl:when><xsl:otherwise>null</xsl:otherwise></xsl:choose>
from [VCLib].[T_CUSTOM_WEBSERVICE] ws
where ws.CustomWebserviceID = '<xsl:value-of select="@id" />'
GO
  </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),
GO
  </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>
GO
  </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,
GO
  </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"/>'
    )
GO
INSERT INTO VCLib.T_VALIDATION_CONDITION (ValidationConditionID, ValidationGroup, Condition, Context, ValidationDescription, IsHierarchical, DataformValidation, ValidationType, ServerSide)
SELECT
           '<xsl:value-of select="@id"/>'--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"/>'
    )
GO
update VCLib.T_VALIDATION_CONDITION
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 ValidationConditionID = '<xsl:value-of select="@id"/>'
GO
    </xsl:for-each>
  </xsl:template>

  <!--Загрузка действий при изменении объекта-->
  <xsl:template match="ObjectModificationActions/SingleModificationAction|ObjectModificationActions/DependencyModificationAction">
declare @ObjectModificationActionID uniqueidentifier
    <xsl:call-template name="getObjectModificationActionID"/>
-- Обновление существующего действия
update oma
set oma.Position = <xsl:value-of select="@Position" />,
    oma.type_ = (select top 1 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>'),
    oma.Active = <xsl:value-of select="@Active" />,
    oma.Instruction = '<xsl:value-of select="Instruction" />',
    oma.InstructionType = <xsl:value-of select="@InstructionType" />
from [VCLib].[T_OBJECT_MODIFICATION_ACTION] oma
where oma.ObjectModificationActionID = @ObjectModificationActionID

-- Создание нового, если не существует
INSERT INTO [VCLib].[T_OBJECT_MODIFICATION_ACTION]
           ([ObjectModificationActionID]
           ,[Entities]
           ,[Position]
           ,[type_]
           ,[Active]
           ,[Name]
           ,[Instruction]
           ,[InstructionType])
SELECT
            @ObjectModificationActionID--ObjectModificationActionID, uniqueidentifier
           ,(select top 1 e.EntityID from VCLib.T_ENTITIES e where e.[Schema] + '.' + e.EntName = '<xsl:value-of select="@Entities" />')--Entities, int
           ,<xsl:value-of select="@Position" />--Position, int
           ,(select top 1 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_SINGLE_MODIFICATION_ACTION]
where ObjectModificationActionID = @ObjectModificationActionID
delete from [VCLib].[T_DEPENDENCY_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
            NEWID()--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>
GO
  </xsl:template>
  <!--Шаблон для получения идентификатора действия-->
  <xsl:template name="getObjectModificationActionID">
select @ObjectModificationActionID = 
    ISNULL((
        select top 1 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" />'
    ), NEWID())
  </xsl:template>

  <!-- Execute SQL Script -->
  <xsl:template match="executesql|MsScript">
    <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" />

    declare @JobID uniqueidentifier
    declare @UserID uniqueidentifier

    select @UserID = (select top 1 tu.[UserID]
      from VCLib.T_USERS tu
      where exists
      (
        select *
        from VCLib.T_LOGIN tl
        where tl.[Identity] = tu.[Identity] and tl.[Login] = <xsl:value-of select="@Login" />
      )
    )

    select @JobID = (select top 1 tj.[JobID] from [VCLib].[T_JOB] tj where tj.[Name] = <xsl:value-of select="@Name" />)

    if @JobID is NULL
    begin
      select @JobID = NEWID()
      insert into [VCLib].[T_JOB] (
        [JobID],
        [Name],
        [ConcurrentExecute],
        [JobUser])
      values (
        @JobID,
        <xsl:value-of select="@Name" />,
        <xsl:value-of select="@ConcurrentExecute" />,
        @UserID)
    end
    else
    begin
      update [VCLib].[T_JOB]
      set
        [ConcurrentExecute] = <xsl:value-of select="@ConcurrentExecute" />,
        [JobUser] = @UserID,
        [Name] = <xsl:value-of select="@Name" />
      where [JobID] = @JobID
    end

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

    declare @JobStepID uniqueidentifier
    declare @OperationID uniqueidentifier
    declare @DataIndex int
    <xsl:for-each select="Steps/Step">
      -- обработка JobStep со Step = <xsl:value-of select="@Step" />

      select @JobStepID = (select top 1 tjs.[JobStepID] from [VCLib].[T_JOB_STEP] tjs
        where tjs.[Job] = @JobID
        and tjs.[Step] = <xsl:value-of select="@Step" />)

      select @OperationID = (select top 1 tso.[OperationID] from [VCLib].[T_SCHEDULER_OPERATION] tso
        where tso.[Name] = <xsl:value-of select="Operation/@Name" />)
      
      select @DataIndex = (select top 1 te.[EntityId] from [VCLib].[T_ENTITIES] te
        where te.[EntName] = <xsl:value-of select="Operation/@EntityName" />
          and te.[Schema] = <xsl:value-of select="Operation/@EntitySchema" />)

      if @OperationID is NULL
      begin
        select @OperationID = NEWID()
        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>)
      end
      else
      begin
      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

      <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 top 1 trlo.[OperationID] from [VCLib].[T_RUN_LEXEM_OPERATION] trlo
          where trlo.[OperationID] = @OperationID) is NULL
        begin
          insert into [VCLib].[T_RUN_LEXEM_OPERATION] (
            [OperationID],
            [Lexem])
          values (
            @OperationID,
            '<xsl:value-of select="$newLexem" />')
        end
        else
        begin
          update [VCLib].[T_RUN_LEXEM_OPERATION]
          set
            [Lexem] = '<xsl:value-of select="$newLexem" />'
          where [OperationID] = @OperationID
        end
      </xsl:for-each>

      if @JobStepID is NULL
      begin
        select @JobStepID = NEWID()
        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)
      end
      else
      begin
        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
    </xsl:for-each>
    go
  </xsl:template>

  <xsl:template match="ArrayOfExtraUICommand">
--Insert or Update ExtraUICommand Records
declare @ExtraUICommandId uniqueidentifier
declare @Entities int
declare @CommandEntity int    
  <xsl:for-each select="ExtraUICommand">
    <xsl:variable name="index" select="position()"/>
--record <xsl:value-of select="$index" />
SET @Entities = (SELECT TOP 1 EntityID from VCLIB.T_ENTITIES e where e.[Schema] + '.' + e.EntName = '<xsl:value-of select="Entities" />')
SET @CommandEntity = (SELECT TOP 1 EntityID from VCLIB.T_ENTITIES e where e.[Schema] + '.' + e.EntName = '<xsl:value-of select="CommandEntity" />')
SET @ExtraUICommandId = (SELECT TOP 1 [ExtraUiCommandID] FROM [VCLib].[T_EXTRA_UI_COMMAND] WHERE [Name] = '<xsl:value-of select="Name" />' and Entities = @Entities and CommandEntity = @CommandEntity)

IF @ExtraUICommandId is NULL
BEGIN
  SET @ExtraUICommandId = NEWID()

  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 as [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 as [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='true'">1</xsl:when><xsl:when test="SameObject='false'">0</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]
END
ELSE
BEGIN
  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>
  ,[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='true'">1</xsl:when><xsl:when test="SameObject='false'">0</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

--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
newid() 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='true'">1</xsl:when><xsl:when test="IsOutput='false'">0</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>
  </xsl:template>
</xsl:stylesheet>
