<?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()"/>

  <!--<xsl:template match="root/Dictionaries">
    DECLARE @dbid int

    <xsl:for-each select="Dictionary">
      exec @dbid = sp_GenerateIdForEntity 'DICTIONARY_BASE', 'PrimaryKey', 1
      INSERT INTO AKUZ.T_DICTIONARY_BASE (DictionaryID, Code, Name, DateAdd, type_)
      VALUES(@dbid, <xsl:value-of select="@Code" />, '<xsl:value-of select="@Name" />', GETDATE(), <xsl:value-of select="@Type" />)
    </xsl:for-each>
    GO
  </xsl:template>-->

  <xsl:template match="root/InsuranceCompanies">
    <xsl:for-each select="InsuranceCompany">
      -- Страх.организация
      create or replace function TempFunc () returns void as $$
      declare
      _orgid uuid;
      _type integer;
      begin
      _orgid := '<xsl:value-of select="@OrganizationID"/>';
      _type := (select EntityID FROM AKUZ.T_ENTITIES
      WHERE EntName='INSURANCE_COMPANY' and Schema='AKUZ');
      INSERT INTO AKUZ.T_ORGANIZATION
      (OrganizationID, Name, ShortName, type_)
      VALUES (_orgID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="ShortName"/>', _type);

      INSERT INTO AKUZ.T_INSURANCE_COMPANY (OrganizationID, Code, PolicyTemplate)
      VALUES (_orgID, '<xsl:value-of select="@Code"/>', <xsl:choose>
        <xsl:when test="string(@PolicyTemplate)">
          '<xsl:value-of select="@PolicyTemplate"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>);
      end;$$ LANGUAGE plpgsql;
      select TempFunc();
    </xsl:for-each>
  </xsl:template>
  
  <!-- V009_RESULT: V009.xml -->
  <xsl:template match="packet[(zglv[type='Rezult'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="RezultZap" />
    </xsl:for-each>
  </xsl:template>
  <!-- V009 "<zap>" template -->
  <xsl:template name="RezultZap" >
    if not exists(select * from AKUZ.T_V009_RESULT r where r.IDRMP = <xsl:value-of select="IDRMP" />)
    INSERT INTO AKUZ.T_V009_RESULT (IDRMP, RMPNAME, DL_USLOV, DATEBEG, DATEEND)
    SELECT <xsl:value-of select="IDRMP" />
	  , '<xsl:value-of select="RMPNAME" />'
    , <xsl:value-of select="DL_USLOV" />
    , convert_to('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , <xsl:if test="DATEEND != ''">
      convert_to('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    else
    update AKUZ.T_V009_RESULT r
    set RMPNAME = '<xsl:value-of select="RMPNAME" />'
    , DL_USLOV = <xsl:value-of select="DL_USLOV" />
    , DATEBEG = convert_to('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , DATEEND = <xsl:if test="DATEEND != ''">
      convert_to('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where r.IDRMP = <xsl:value-of select="IDRMP" />
    --pgsql:go
  </xsl:template>
  
  <!-- V021_SPECIALITY: V021.xml -->

  <xsl:template match="packet[(zglv[type='MedSpec'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V021_SPECIALITY -->
      <xsl:call-template name="MedSpecZap" />
    </xsl:for-each>
  </xsl:template>
  <!-- V021 "<zap>" template -->

  <xsl:template name="MedSpecZap" >
    do
    $$
    begin
    if not exists(select * from AKUZ.T_V021_SPECIALITY s where s.ID = <xsl:value-of select="IDSPEC" /> and s.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp)
    then
      INSERT INTO AKUZ.T_V021_SPECIALITY
      ("id", "name", DateBegin, DateEnd, V021SpecialityID, POSTNAME, IDPOST_MZ)
      VALUES
      (
      '<xsl:value-of select="IDSPEC" />'
      , '<xsl:value-of select="SPECNAME" />'
      , to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
      , <xsl:choose>
        <xsl:when test="string(DATEEND)">
          to_date('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      , gen_random_uuid()
      , <xsl:choose>
        <xsl:when test="string(POSTNAME)">
          '<xsl:value-of select="POSTNAME"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      , <xsl:choose>
        <xsl:when test="string(IDPOST_MZ)">
          '<xsl:value-of select="IDPOST_MZ"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      );
    else
      update akuz.t_v021_speciality s
      set "name" = '<xsl:value-of select="SPECNAME" />'
      , DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
      , DateEnd = <xsl:choose>
        <xsl:when test="string(DATEEND)">
          to_date('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      , POSTNAME = <xsl:choose>
        <xsl:when test="string(POSTNAME)">
          '<xsl:value-of select="POSTNAME"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      , IDPOST_MZ = <xsl:choose>
        <xsl:when test="string(IDPOST_MZ)">
          '<xsl:value-of select="IDPOST_MZ"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>
      where s.Id = '<xsl:value-of select="IDSPEC" />' and s.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp;
    end if;
    end;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- V015_SPECIALITY: V015.xml -->
  <!--<xsl:template match="packet[(zglv[@type='MedSpec'])]">
    <xsl:for-each select="zap">
      -->
  <!-- insert or update V015_SPECIALITY -->
  <!--
      <xsl:call-template name="MedSpecZap" />
    </xsl:for-each>
  </xsl:template>
  -->
  <!-- V015 "<zap>" template -->
  <!--
  <xsl:template name="MedSpecZap" >
    if not exists(select * from [AKUZ].[T_V015_SPECIALITY] s where s.[RECID] = <xsl:value-of select="@RECID" />)
    INSERT INTO [AKUZ].[T_V015_SPECIALITY] ([RECID] ,[CODE] ,[NAME] ,[HIGH] ,[OKSO] ,[DATEBEG] ,[DATEEND])
    SELECT <xsl:value-of select="@RECID" />
    , <xsl:value-of select="@CODE" />
    , LEFT('<xsl:value-of select="@NAME" />', 256)
    , <xsl:if test="@HIGH != ''">
      '<xsl:value-of select="@HIGH" />'
    </xsl:if><xsl:if test="@HIGH = ''">NULL</xsl:if>
    , <xsl:if test="@OKSO != ''">
      '<xsl:value-of select="@OKSO" />'
    </xsl:if><xsl:if test="@OKSO = ''">NULL</xsl:if>
    , CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    else
    update s
    set s.[CODE] = <xsl:value-of select="@CODE" />
    , s.[NAME] = LEFT('<xsl:value-of select="@NAME" />', 256)
    , s.[HIGH] = <xsl:if test="@HIGH != ''">
      '<xsl:value-of select="@HIGH" />'
    </xsl:if><xsl:if test="@HIGH = ''">NULL</xsl:if>
    , s.[OKSO] = <xsl:if test="@OKSO != ''">
      '<xsl:value-of select="@OKSO" />'
    </xsl:if><xsl:if test="@OKSO = ''">NULL</xsl:if>
    , s.[DATEBEG] = CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , s.[DATEEND] = <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    from [AKUZ].[T_V015_SPECIALITY] s
    where s.[RECID] = <xsl:value-of select="@RECID" />
    GO
  </xsl:template>
  -->
  <!-- VMP_SERVICE: V019.xml elements -->
  <!--
  <xsl:template match="packet[(zglv[type='HMet'])]">
    -->
  <!-- Create MedicalServiceType -->
  <!--
    if not exists(select * from [AKUZ].[T_MEDICAL_SERVICE_TYPE] ms where ms.[MedicalServiceTypeID] = '642809B3-0C46-4673-8E97-76A6D60C14F8')
    INSERT INTO [AKUZ].[T_MEDICAL_SERVICE_TYPE] ([MedicalServiceTypeID], [Name])
    SELECT '642809B3-0C46-4673-8E97-76A6D60C14F8', 'ВМП(V019)'
    GO
    <xsl:for-each select="zap">
      -->
  <!-- insert or update MedicalService -->
  <!--
      <xsl:call-template name="HMetZapElements" />
      -->
  <!-- clear MedicalServiceMkbLinks -->
  <!--
      delete msml
      from [AKUZ].[T_MEDICAL_SERVICE_MKB_LINK] msml
      join [AKUZ].[T_MEDICAL_SERVICE] ms on ms.MedicalServiceID = msml.MedicalService
      where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
      GO
      -->
  <!-- insert new MedicalServiceMkbLinks -->
  <!--
      <xsl:call-template name="HMetDiagnosisElements" />
    </xsl:for-each>
  </xsl:template>
  -->
  <!-- V019 "<zap>" template -->
  <!--
  <xsl:template name="HMetZapElements" >
    if not exists(select * from [AKUZ].[T_MEDICAL_SERVICE] ms where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8')
    INSERT INTO [AKUZ].[T_MEDICAL_SERVICE] ([Code], [Name], [NameFull], [Profile], [MedicalServiceID], [type_], [MedicalServiceType], [ShortName], [EconomicCode], [PayType], [DateBegin], [DateEnd], [IsCommonService])
    SELECT '<xsl:value-of select="IDHM" />'
    , LEFT('<xsl:value-of select="HMNAME" />', 256)
    , LEFT('<xsl:value-of select="HMNAME" />', 256)
    , 3
    , NEWID()
    , 1113
    , '642809B3-0C46-4673-8E97-76A6D60C14F8'
    , LEFT('<xsl:value-of select="HMNAME" />', 24)
    , '<xsl:value-of select="HVID" />'
    , 1
    , CONVERT(datetime, '<xsl:value-of select="DATEBEG" />', 104)
    , <xsl:if test="DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="DATEEND" />', 104)
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, false
    else
    update ms
    set [Name] = LEFT('<xsl:value-of select="HMNAME" />', 256)
    , [NameFull] = LEFT('<xsl:value-of select="HMNAME" />', 256)
    , [ShortName] = LEFT('<xsl:value-of select="HMNAME" />', 24)
    , [EconomicCode] = '<xsl:value-of select="HVID" />'
    , [DateBegin] = CONVERT(datetime, '<xsl:value-of select="DATEBEG" />', 104)
    , [DateEnd] = <xsl:if test="DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="DATEEND" />', 104)
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    from [AKUZ].[T_MEDICAL_SERVICE] ms
    where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    GO
  </xsl:template>
  -->
  <!-- V019 "DIAG" template -->
  <!--
  <xsl:template name="HMetDiagnosisElements">
    <xsl:param name="split" select="concat(replace(replace(DIAG, ' ', ''), ',', ';'), ';')"/>
    <xsl:if test="string-length($split)">
      <xsl:if test="not($split=.)">
        <xsl:variable name="correctMKB"
                      select="translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(substring-before($split,';')
                      , 'А','A')
                      , 'а','a')
                      , 'В','B')
                      , 'с','c')
                      , 'С','C')
                      , 'Е','E')
                      , 'е','e')
                      , 'Н','H')
                      , 'К','K')
                      , 'к','k')
                      , 'М','M')
                      , 'п','n')
                      , 'О','O')
                      , 'о','o')
                      , 'т','m')
                      , 'Р','P')
                      , 'р','p')
                      , 'Т','T')
                      , 'и','u')
                      , 'Х','X')
                      , 'х','x')
                      , 'у','y')"/>
        INSERT INTO [AKUZ].[T_MEDICAL_SERVICE_MKB_LINK] ([MedicalServiceMkbLinkID] ,[MedicalService] ,[MkbCode])
        SELECT NEWID()
        , ms.[MedicalServiceID]
        , '<xsl:value-of select="$correctMKB"/>'
        from [AKUZ].[T_MEDICAL_SERVICE] ms
        where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
        GO
      </xsl:if>
      <xsl:call-template name="HMetDiagnosisElements">
        <xsl:with-param name="split" select="substring-after($split, ';')"/>
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
  -->
  <!-- VMP_SERVICE: V019.xml attributes -->
  <!--
  <xsl:template match="packet[(zglv[@type='HMet'])]">
    -->
  <!-- Create MedicalServiceType -->
  <!--
    if not exists(select * from [AKUZ].[T_MEDICAL_SERVICE_TYPE] ms where ms.[MedicalServiceTypeID] = '642809B3-0C46-4673-8E97-76A6D60C14F8')
    INSERT INTO [AKUZ].[T_MEDICAL_SERVICE_TYPE] ([MedicalServiceTypeID], [Name])
    SELECT '642809B3-0C46-4673-8E97-76A6D60C14F8', 'ВМП(V019)'
    GO
    <xsl:for-each select="zap">
      -->
  <!-- insert or update MedicalService -->
  <!--
      <xsl:call-template name="HMetZapAttributes" />
      -->
  <!-- clear MedicalServiceMkbLinks -->
  <!--
      delete msml
      from [AKUZ].[T_MEDICAL_SERVICE_MKB_LINK] msml
      join [AKUZ].[T_MEDICAL_SERVICE] ms on ms.MedicalServiceID = msml.MedicalService
      where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="@IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
      GO
      -->
  <!-- insert new MedicalServiceMkbLinks -->
  <!--
      <xsl:call-template name="HMetDiagnosisAttributes" />
    </xsl:for-each>
  </xsl:template>
  -->
  <!-- V019 "<zap>" template -->
  <!--
  <xsl:template name="HMetZapAttributes" >
    if not exists(select * from [AKUZ].[T_MEDICAL_SERVICE] ms where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="@IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8')
    INSERT INTO [AKUZ].[T_MEDICAL_SERVICE] ([Code], [Name], [NameFull], [Profile], [MedicalServiceID], [type_], [MedicalServiceType], [ShortName], [EconomicCode], [PayType], [DateBegin], [DateEnd])
    SELECT '<xsl:value-of select="@IDHM" />'
    , LEFT('<xsl:value-of select="@HMNAME" />', 256)
    , LEFT('<xsl:value-of select="@HMNAME" />', 256)
    , 3
    , NEWID()
    , 1113
    , '642809B3-0C46-4673-8E97-76A6D60C14F8'
    , LEFT('<xsl:value-of select="@HMNAME" />', 24)
    , '<xsl:value-of select="@HVID" />'
    , 1
    , CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    else
    update ms
    set [Name] = LEFT('<xsl:value-of select="@HMNAME" />', 256)
    , [NameFull] = LEFT('<xsl:value-of select="@HMNAME" />', 256)
    , [ShortName] = LEFT('<xsl:value-of select="@HMNAME" />', 24)
    , [EconomicCode] = '<xsl:value-of select="@HVID" />'
    , [DateBegin] = CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , [DateEnd] = <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    from [AKUZ].[T_MEDICAL_SERVICE] ms
    where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="@IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    GO
  </xsl:template>
  -->
  <!-- V019 "DIAG" template -->
  <!--
  <xsl:template name="HMetDiagnosisAttributes">
    <xsl:param name="split" select="concat(replace(replace(@DIAG, ' ', ''), ',', ';'), ';')"/>
    <xsl:if test="string-length($split)">
      <xsl:if test="not($split=.)">
        <xsl:variable name="correctMKB"
                      select="translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(
                              translate(substring-before($split,';')
                      , 'А','A')
                      , 'а','a')
                      , 'В','B')
                      , 'с','c')
                      , 'С','C')
                      , 'Е','E')
                      , 'е','e')
                      , 'Н','H')
                      , 'К','K')
                      , 'к','k')
                      , 'М','M')
                      , 'п','n')
                      , 'О','O')
                      , 'о','o')
                      , 'т','m')
                      , 'Р','P')
                      , 'р','p')
                      , 'Т','T')
                      , 'и','u')
                      , 'Х','X')
                      , 'х','x')
                      , 'у','y')"/>
        INSERT INTO [AKUZ].[T_MEDICAL_SERVICE_MKB_LINK] ([MedicalServiceMkbLinkID] ,[MedicalService] ,[MkbCode])
        SELECT NEWID()
        , ms.[MedicalServiceID]
        , '<xsl:value-of select="$correctMKB"/>'
        from [AKUZ].[T_MEDICAL_SERVICE] ms
        where ms.[type_] = 1113 and ms.[Code] = '<xsl:value-of select="@IDHM" />' and ms.[MedicalServiceType] = '642809B3-0C46-4673-8E97-76A6D60C14F8'
        GO
      </xsl:if>
      <xsl:call-template name="HMetDiagnosisAttributes">
        <xsl:with-param name="split" select="substring-after($split, ';')"/>
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
  -->
  <!-- V002_PROFILE: V002.xml -->
  <!--
  <xsl:template match="packet[(zglv[@type='ProfOt'])]">
    <xsl:for-each select="zap">
      -->
  <!-- insert or update MedicalService -->
  <!--
      <xsl:call-template name="ProfOtZap" />      
    </xsl:for-each>
  </xsl:template>
  -->
  <!-- V002 "<zap>" template -->
  <!--
  <xsl:template name="ProfOtZap" >
    if not exists(select * from [AKUZ].[T_V002_PROFILE] p where p.[Id] = '<xsl:value-of select="@IDPR" />')
    INSERT INTO [AKUZ].[T_V002_PROFILE] ([Id], [Name], [DateBegin], [DateEnd])
    SELECT '<xsl:value-of select="@IDPR" />'
    , LEFT('<xsl:value-of select="@PRNAME" />', 256)
    , CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    else
    update p
    set [Name] = LEFT('<xsl:value-of select="@PRNAME" />', 256)    
    , [DateBegin] = CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , [DateEnd] = <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    from [AKUZ].[T_V002_PROFILE] p
    where p.[Id] = '<xsl:value-of select="@IDPR" />'
    GO
  </xsl:template>-->

  <!-- Шаблон загрузки настроек сводной информации ВИМИС регистра -->
  <xsl:template match="VimisConsolidationSettings/Settings">
    <xsl:variable name="priorityPath">
      <xsl:call-template name="replace">
        <xsl:with-param name="text"><xsl:value-of select="@priorityXpath" /></xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="priorityLexem">
      <xsl:call-template name="replace">
        <xsl:with-param name="text"><xsl:value-of select="@priorityLexem" /></xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="xsltTemplate">
      <xsl:call-template name="replace">
		<xsl:with-param name="text"><xsl:value-of select="@template" /></xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>

INSERT INTO AKUZ.S_VIMIS_CONSOLIDATION_GroupType(value) select '<xsl:value-of select="@group" />' where not exists (select * from AKUZ.S_VIMIS_CONSOLIDATION_GroupType where value = '<xsl:value-of select="@group" />');

INSERT INTO AKUZ.T_VIMIS_CONSOLIDATION_SETTING
           (VimisConsolidationSettingID
           ,Entities
           ,GroupType
           ,PriorityXpath
           ,PriorityLexem
           ,XsltTemplate)
SELECT
            gen_random_uuid()--VimisConsolidationSettingID, uniqueidentifier,
           ,(select e.EntityID from VCLib.T_ENTITIES e where e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />' limit 1)--Entities, int,
           ,(select v.id from AKUZ.S_VIMIS_CONSOLIDATION_GroupType v where v.value = '<xsl:value-of select="@group" />' limit 1)--GroupType, int,
           ,<xsl:choose><xsl:when test="@priorityXpath!=''">'<xsl:value-of select="$priorityPath" />'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>--PriorityXpath, varchar(1000),
           ,<xsl:choose><xsl:when test="@priorityLexem!=''">'<xsl:value-of select="$priorityLexem" />'</xsl:when><xsl:otherwise>NULL</xsl:otherwise></xsl:choose>--PriorityLexem, varchar(1000),
           ,'<xsl:value-of select="$xsltTemplate" />'--XsltTemplate, varchar(max),)
where not exists
    (
        select *
        from AKUZ.T_VIMIS_CONSOLIDATION_SETTING cs
            join VCLib.T_ENTITIES e on e.EntityID = cs.Entities
            join AKUZ.S_VIMIS_CONSOLIDATION_GroupType v on v.id = cs.GroupType
        where e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />'
                and
            v.value = '<xsl:value-of select="@group" />'
                and
            cs.PriorityXpath <xsl:choose><xsl:when test="@priorityXpath!=''"> = '<xsl:value-of select="$priorityPath" />'</xsl:when><xsl:otherwise>is NULL</xsl:otherwise></xsl:choose>
                and
            cs.PriorityLexem <xsl:choose><xsl:when test="@priorityLexem!=''"> = '<xsl:value-of select="$priorityLexem" />'</xsl:when><xsl:otherwise>is NULL</xsl:otherwise></xsl:choose>
    );
update AKUZ.T_VIMIS_CONSOLIDATION_SETTING cs
set XsltTemplate = '<xsl:value-of select="$xsltTemplate" />'
from VCLib.T_ENTITIES e, AKUZ.S_VIMIS_CONSOLIDATION_GroupType v 
where e.EntityID = cs.Entities
        and
    v.id = cs.GroupType
        and
    e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />'
        and
    v.value = '<xsl:value-of select="@group" />'
        and
    PriorityXpath <xsl:choose><xsl:when test="@priorityXpath!=''"> = '<xsl:value-of select="$priorityPath" />'</xsl:when><xsl:otherwise>is NULL</xsl:otherwise></xsl:choose>
        and
    PriorityLexem <xsl:choose><xsl:when test="@priorityLexem!=''"> = '<xsl:value-of select="$priorityLexem" />'</xsl:when><xsl:otherwise>is NULL</xsl:otherwise></xsl:choose>;
  </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>
