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


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

  <!-- Postponed Delete Entity Settings -->
  <xsl:template match="IES.POSTPONED_DELETE_ENTITY_group">
    <xsl:for-each select="IES.POSTPONED_DELETE_ENTITY">
DELETE FROM [IES].[T_POSTPONED_DELETE_ENTITY] WHERE [Name] = '<xsl:value-of select="Name" />'
GO
INSERT INTO [IES].[T_POSTPONED_DELETE_ENTITY]
      ([PostponedDeleteEntityID]
      ,[Name]
      ,[ParentTriggerPaths]
      ,[IsSystem]
      ,[Order]
      ,[IsActive]
      ,[DependentPaths])
VALUES (NEWID(),
      '<xsl:value-of select="Name"/>',
      '<xsl:value-of select="ParentTriggerPaths"/>',
      '<xsl:value-of select="IsSystem"/>',
      '<xsl:value-of select="Order"/>',
      '<xsl:value-of select="IsActive"/>',
      '<xsl:value-of select="DependentPaths"/>')
GO
    </xsl:for-each>
  </xsl:template>
  <!--  criteria group Entity Import Filters -->
	<xsl:template match="IES.CRITERIA_GROUP_group">
		<xsl:for-each select="IES.CRITERIA_GROUP">
			BEGIN
			DECLARE @CriteriaGroupId uniqueidentifier
			SET @CriteriaGroupId = NEWID()
			DECLARE @CriteriaItemId uniqueidentifier
			INSERT INTO [IES].[T_CRITERIA_GROUP]
			([CriteriaGroupID]
			,[Name]
			,[ExpertiseType]
			,[Condition]
			,[Priority]
			,[IsActive]
			,[Entities]
			,[ItemEntity]
			<xsl:if test="F006ControlType/ArrayOfUniquenessField/UniquenessField/@Value != ''">
				,[F006ControlType]
			</xsl:if>)
			VALUES (@CriteriaGroupId,
			'<xsl:value-of select="Name"/>',
			'<xsl:value-of select="ExpertiseType"/>',
			'<xsl:call-template name="serializeNodeToString">
			    <xsl:with-param name="node" select="Condition/*"/>
		    </xsl:call-template>',
			'<xsl:value-of select="Priority"/>',
			'<xsl:value-of select="IsActive"/>',
			(select e1.[EntityID] from [VCLib].[T_ENTITIES] e1 where
			e1.[Schema] = '<xsl:value-of select="substring-before(Entities, '.')"/>' and
			e1.[EntName] = '<xsl:value-of select="substring-after(Entities, '.')"/>'),
			(select e1.[EntityID] from [VCLib].[T_ENTITIES] e1 where
			e1.[Schema] = '<xsl:value-of select="substring-before(ItemEntity, '.')"/>' and
			e1.[EntName] = '<xsl:value-of select="substring-after(ItemEntity, '.')"/>')
			<xsl:if test="F006ControlType/ArrayOfUniquenessField/UniquenessField/@Value != ''">
				,(select top 1 [F006ControlTypeID] from [IES].[T_F006_CONTROL_TYPE]
				    where 1=1 <xsl:for-each select="F006ControlType/ArrayOfUniquenessField/UniquenessField">
						<xsl:if test="@Value != ''">
							and <xsl:value-of select="@StepString"/> = '<xsl:value-of select="@Value"/>'
						</xsl:if>
					</xsl:for-each>)
			</xsl:if>)
		    <xsl:for-each select="CRITERIA_ITEM">
			    <xsl:for-each select="IES.CRITERIA_ITEM_CODE">
					BEGIN
					SET @CriteriaItemId = NEWID()
					INSERT INTO [IES].[T_CRITERIA_ITEM]
					([CriteriaItemID]
					,[CriteriaGroup]
					,[DateBegin]
					<xsl:if test="DateEnd != ''">
					    ,[DateEnd]
				    </xsl:if>
					,[Description]
					,[type_]
					,[Priority])
					VALUES(@CriteriaItemId
					,@CriteriaGroupId
					,'<xsl:value-of select="DateBegin"/>'
					<xsl:if test="DateEnd != ''">
						,'<xsl:value-of select="DateEnd"/>'
					</xsl:if>
					,'<xsl:value-of select="Description"/>'
					,(select e1.[EntityID] from [VCLib].[T_ENTITIES] e1 where
					e1.[Schema] + '.' + e1.[EntName] = '<xsl:value-of select="local-name()"/>')
					,'<xsl:value-of select="Priority"/>')
					INSERT INTO [IES].[T_CRITERIA_ITEM_CODE]
					([Source]
					,[CriteriaItemID])
					VALUES('<xsl:value-of select="Source"/>'
					,@CriteriaItemId)
					END
				</xsl:for-each>
				<xsl:for-each select="IES.CRITERIA_ITEM_LEXEM">
					BEGIN
					SET @CriteriaItemId = NEWID()
					INSERT INTO [IES].[T_CRITERIA_ITEM]
					([CriteriaItemID]
					,[CriteriaGroup]
					,[DateBegin]
					<xsl:if test="DateEnd != ''">
						,[DateEnd]
					</xsl:if>
					,[Description]
					,[type_]
					,[Priority])
					VALUES(@CriteriaItemId
					,@CriteriaGroupId
					,'<xsl:value-of select="DateBegin"/>'
					<xsl:if test="DateEnd != ''">
						,'<xsl:value-of select="DateEnd"/>'
					</xsl:if>
					,'<xsl:value-of select="Description"/>'
					,(select e1.[EntityID] from [VCLib].[T_ENTITIES] e1 where
					e1.[Schema] + '.' + e1.[EntName] = '<xsl:value-of select="local-name()"/>')
					,'<xsl:value-of select="Priority"/>')
					INSERT INTO [IES].[T_CRITERIA_ITEM_LEXEM]
					([Condition]
					,[ExpertiseError]
					<xsl:if test="MakeCode != ''">
						,[MakeCode]
					</xsl:if>
					,[CriteriaItemID]
					<xsl:if test="DescriptionCondition != ''">
					    ,[DescriptionCondition]
					</xsl:if>
					<xsl:if test="LinksCondition != ''">
					    ,[LinksCondition]
				    </xsl:if>
					<xsl:if test="Test != ''">
						,[Test]
					</xsl:if>)
					VALUES('<xsl:call-template name="serializeNodeToString">
					            <xsl:with-param name="node" select="Condition/*"/>
				            </xsl:call-template>'
					,'<xsl:value-of select="ExpertiseError"/>'
					<xsl:if test="MakeCode != ''">
					    ,'<xsl:value-of select="MakeCode"/>'
					</xsl:if>
					,@CriteriaItemId
					<xsl:if test="DescriptionCondition != ''">
						,'<xsl:value-of select="DescriptionCondition"/>'
					</xsl:if>
					<xsl:if test="LinksCondition != ''">
						,'<xsl:value-of select="LinksCondition"/>'
					</xsl:if>
					<xsl:if test="Test != ''">
						,'<xsl:value-of select="Test"/>'
					</xsl:if>)
					END
				</xsl:for-each>
		    </xsl:for-each>
			END
		</xsl:for-each>
	</xsl:template>
  <!-- Load Settigs-->
  <xsl:template match="IES.LOAD_SETTINGS_group">
    <xsl:for-each select="IES.LOAD_SETTINGS">
        INSERT INTO [IES].[T_LOAD_SETTINGS]
        ([LoadSettingsID]
        ,[Name]
        ,[CreateData])
        VALUES (NEWID(),
        '<xsl:value-of select="Name"/>',
        '<xsl:value-of select="CreateData"/>'),
        GO
    </xsl:for-each>
  </xsl:template>


  <!-- Verification Pattern -->
    <xsl:template match="IES.VERIFICATION_PATTERN_group">        
        <xsl:for-each select="IES.VERIFICATION_PATTERN">
            BEGIN
            DECLARE @id uniqueidentifier
            SET @id = NEWID()
            INSERT INTO [IES].[T_VERIFICATION_PATTERN]
            ([VerificationPatternID]
            ,[Name]
            ,[Descr]
            ,[Version]
            ,[IgnoreFileCount]
            ,[Procedure]
            ,[IsBulk]
            ,[IsAdditional])
            VALUES
            (@id
            ,'<xsl:value-of select="Name"/>'
           ,'<xsl:value-of select="Descr"/>'
           ,'<xsl:value-of select="Version"/>'
           ,'<xsl:value-of select="IgnoreFileCount"/>'
           ,'<xsl:value-of select="Procedure"/>'
           ,'<xsl:value-of select="IsBulk"/>'
           ,'<xsl:value-of select="IsAdditional"/>')
            <xsl:for-each select="FILE_PATTERN">
               <xsl:for-each select="IES.FILE_PATTERN">
                   INSERT INTO [IES].[T_FILE_PATTERN]
                   ([FilePatternID]
                   ,[VerificationPattern]
                   ,[Pattern]
                   ,[XsdSchema]
                   ,[LoadSchema]
                   ,[Procedure]
                   ,[Version]
                   ,[IsRequired])
                   VALUES
                   (NEWID()
                   ,@id
                   ,'<xsl:value-of select="Pattern"/>'
                   ,'<xsl:value-of select="XsdSchema"/>'
                   ,'<xsl:value-of select="LoadSchema"/>'
                   ,'<xsl:value-of select="Procedure"/>'
                   ,'<xsl:value-of select="Version"/>'
                   ,'<xsl:value-of select="IsRequired"/>')
                </xsl:for-each> 
           </xsl:for-each>
            END
        </xsl:for-each>
    </xsl:template>

    <!-- Load Settings Pattern-->
    <xsl:template match="IES.LOAD_SETTINGS_PATTERNS_group">
        <xsl:for-each select="IES.LOAD_SETTINGS_PATTERNS">
            BEGIN
            INSERT INTO [IES].[T_LOAD_SETTINGS_PATTERNS]
            ([LoadSettingsPatternsID]
            ,[LoadSettings]
            ,[VerificationPattern])
            VALUES
            (NEWID()
            ,'<xsl:value-of select="LoadSettings"/>'
            ,'<xsl:value-of select="VerificationPattern"/>')
            END            
        </xsl:for-each>
    </xsl:template>
    
    
  <xsl:template match="root/V008">
    <xsl:for-each select="*">
        
        INSERT INTO [dbo].[T_V008_MEDICAL_TYPE]
        ([IDVMP],[VMPNAME],[DATEBEG],[DATEEND])
        VALUES(<xsl:value-of select="@IDVMP"/>, 
        '<xsl:value-of select="@VMPNAME"/>', 
        case when LEN('<xsl:value-of select="@DATEBEG"/>') &lt; 8  then null else CONVERT(datetime,'<xsl:value-of select="@DATEBEG"/>',103) end, 
        case when LEN('<xsl:value-of select="@DATEEND"/>') &lt; 8  then null else CONVERT(datetime,'<xsl:value-of select="@DATEEND"/>',103) end)
        
      </xsl:for-each>
  </xsl:template>

  <xsl:template match="root/client_modules/client_module">
    DECLARE @ID uniqueidentifier
    SET @ID = (SELECT TOP 1 ClientModuleID FROM VCLib.T_CLIENT_MODULES WHERE ModuleType = '<xsl:value-of select="@ModuleType" />')
    IF @ID is NULL
    BEGIN
    SET @ID = NEWID()
    INSERT INTO VCLib.T_CLIENT_MODULES (ClientModuleID, ModuleType, ModuleDescription, Priority)
    SELECT @ID, '<xsl:value-of select="@ModuleType" />', '<xsl:value-of select="@ModuleDescription" />', <xsl:choose>
      <xsl:when test="@Priority">
        <xsl:value-of select="@Priority" />
      </xsl:when>
      <xsl:otherwise>10</xsl:otherwise>
    </xsl:choose>
    <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>
    END
    ELSE
    UPDATE cm
    SET cm.[Priority] = <xsl:choose>
      <xsl:when test="@Priority">
        <xsl:value-of select="@Priority" />
      </xsl:when>
      <xsl:otherwise>10</xsl:otherwise>
    </xsl:choose>
    ,cm.ModuleDescription = '<xsl:value-of select="@ModuleDescription" />'
    FROM VCLib.T_CLIENT_MODULES cm
    WHERE cm.ClientModuleID = @ID
    <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>
    GO
  </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="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>
</xsl:stylesheet>
