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

  <!-- V020_STRUCTURE_BED: V020.xml -->
  <xsl:template match="packet[(zglv[type='KoPr'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="KoPrZap" />
    </xsl:for-each>
  </xsl:template>

  <xsl:template name="KoPrZap">
    DO $$
    BEGIN
    IF NOT EXISTS(SELECT 1 FROM AKUZ.T_V020_STRUCTURE_BED WHERE IDK_PR = <xsl:value-of select="IDK_PR" />) THEN
    INSERT INTO AKUZ.T_V020_STRUCTURE_BED (IDK_PR, K_PRNAME, DATEBEG, DATEEND)
    VALUES(
    <xsl:value-of select="IDK_PR" />,
    '<xsl:value-of select="K_PRNAME" />',
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    );
    ELSE
    UPDATE AKUZ.T_V020_STRUCTURE_BED SET
    K_PRNAME = '<xsl:value-of select="K_PRNAME" />',
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    WHERE IDK_PR = <xsl:value-of select="IDK_PR" />;
    END IF;
    END;
    $$;
    --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>
  -->

  <!-- V016_DISP_TYPE: V016.xml -->
  <xsl:template match="packet[(zglv[type='DispT'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V016_DISP_TYPE -->
      <xsl:call-template name="DispT" />
    </xsl:for-each>
  </xsl:template>
  <!-- V016 "<zap>" template -->
  <xsl:template name="DispT">
    DO $$
    BEGIN
    IF NOT EXISTS(
    SELECT 1 FROM AKUZ.T_V016_DISP_TYPE
    WHERE IDDT = LEFT('<xsl:value-of select="IDDT" />', 3)
    ) THEN
    INSERT INTO AKUZ.T_V016_DISP_TYPE (IDDT, DTNAME, DATEBEG, DATEEND, DTRULE)
    VALUES(
    LEFT('<xsl:value-of select="IDDT" />', 3),
    LEFT('<xsl:value-of select="DTNAME" />', 256),
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    '<xsl:for-each select="DTRULE/RULE">
      <xsl:value-of select="." />;
    </xsl:for-each>'
    );
    ELSE
    UPDATE AKUZ.T_V016_DISP_TYPE SET
    DTNAME = LEFT('<xsl:value-of select="DTNAME" />', 256),
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    DTRULE = '<xsl:for-each select="DTRULE/RULE">
      <xsl:value-of select="." />;
    </xsl:for-each>'
    WHERE IDDT = LEFT('<xsl:value-of select="IDDT" />', 3)
    AND DATEBEG &lt; to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY');
    END IF;
    END;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- V017_DISP_RESULT: V017.xml -->
  <xsl:template match="packet[(zglv[type='DispR'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V017_DISP_RESULT -->
      <xsl:call-template name="DispR" />
    </xsl:for-each>
  </xsl:template>
  <!-- V017 "<zap>" template -->
  <xsl:template name="DispR">
    DO $$
    BEGIN
    IF NOT EXISTS(SELECT 1 FROM AKUZ.T_V017_DISP_RESULT WHERE IDDR = <xsl:value-of select="IDDR" />) THEN
    INSERT INTO AKUZ.T_V017_DISP_RESULT (IDDR, DRNAME, DATEBEG, DATEEND)
    VALUES(
    <xsl:value-of select="IDDR" />,
    LEFT('<xsl:value-of select="DRNAME" />', 256),
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    );
    ELSE
    UPDATE AKUZ.T_V017_DISP_RESULT SET
    DRNAME = LEFT('<xsl:value-of select="DRNAME" />', 256),
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    WHERE IDDR = <xsl:value-of select="IDDR" />;
    END IF;
    END;
    $$;
    --pgsql: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">
      <xsl:call-template name="ProfOtZap" />
    </xsl:for-each>
  </xsl:template>

  <!-- V002 "<zap>" template -->
  <xsl:template name="ProfOtZap">
    INSERT INTO AKUZ.T_V002_PROFILE (Id, Name, DateBegin, DateEnd)
    SELECT
    '<xsl:value-of select="@IDPR" />',
    SUBSTRING('<xsl:value-of select="@PRNAME" />', 1, 256),
    TO_DATE('<xsl:value-of select="@DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="@DATEEND != ''">
        TO_DATE('<xsl:value-of select="@DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (Id) DO UPDATE SET
    Name = EXCLUDED.Name,
    DateBegin = EXCLUDED.DateBegin,
    DateEnd = EXCLUDED.DateEnd;
  </xsl:template>

  <!-- V011_DRUG: V011.xml -->
  <xsl:template match="book[(entries/zglv[type='V011_Drugs'])]">
    <xsl:for-each select="entry">
      <xsl:call-template name="Drugs" />
    </xsl:for-each>
  </xsl:template>

  <!-- V011 "<zap>" template -->
  <xsl:template name="Drugs">
    INSERT INTO AKUZ.T_V011_DRUG (ID, S_NUM_REG, DATE_REG, DATE_END_REG, S_ORG_NAME, S_ORG_COUNTRY,
    S_TORG_NAME, S_MNN, S_FORMS, S_PROD_PHASE, S_DOCS, S_FARM_GROUP, S_CODES)
    SELECT
    <xsl:value-of select="ID" />,
    SUBSTRING('<xsl:value-of select="S_NUM_REG" />', 1, 50),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="S_ORG_NAME" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_ORG_COUNTRY" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_TORG_NAME" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_MNN" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_FORMS" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_PROD_PHASE" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_DOCS" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_FARM_GROUP" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_CODES" />', 1, 255)
    ON CONFLICT (ID) DO UPDATE SET
    S_NUM_REG = EXCLUDED.S_NUM_REG,
    DATE_REG = EXCLUDED.DATE_REG,
    DATE_END_REG = EXCLUDED.DATE_END_REG,
    S_ORG_NAME = EXCLUDED.S_ORG_NAME,
    S_ORG_COUNTRY = EXCLUDED.S_ORG_COUNTRY,
    S_TORG_NAME = EXCLUDED.S_TORG_NAME,
    S_MNN = EXCLUDED.S_MNN,
    S_FORMS = EXCLUDED.S_FORMS,
    S_PROD_PHASE = EXCLUDED.S_PROD_PHASE,
    S_DOCS = EXCLUDED.S_DOCS,
    S_FARM_GROUP = EXCLUDED.S_FARM_GROUP,
    S_CODES = EXCLUDED.S_CODES;
  </xsl:template>

  <!-- V024_DopKr: V024.xml -->
  <xsl:template match="packet[(zglv[type='DopKr'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="DopKr" />
    </xsl:for-each>
  </xsl:template>

  <!-- V024 "<zap>" template -->
  <xsl:template name="DopKr">
    INSERT INTO AKUZ.T_V024 (V024DoprKrID, IDDKK, DKKNAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="IDDKK" />', 1, 255),
    <xsl:choose>
      <xsl:when test="DKKNAME != ''">
        SUBSTRING('<xsl:value-of select="DKKNAME" />', 1, 500)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDDKK, DATEBEG) DO UPDATE SET
    <xsl:if test="DKKNAME != ''">DKKNAME = EXCLUDED.DKKNAME,</xsl:if>
    DATEBEG = EXCLUDED.DATEBEG,
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V028_RefType: V028.xml -->
  <xsl:template match="packet[(zglv[type='RefType'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="RefType" />
    </xsl:for-each>
  </xsl:template>

  <!-- V028 "<zap>" template -->
  <xsl:template name="RefType">
    INSERT INTO AKUZ.T_V028_REFERRAL_TYPE (IDVN, N_VN, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDVN" />,
    <xsl:choose>
      <xsl:when test="N_VN != ''">
        SUBSTRING('<xsl:value-of select="N_VN" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDVN) DO UPDATE SET
    <xsl:if test="N_VN != ''">N_VN = EXCLUDED.N_VN,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V029_ResMet: V029.xml -->
  <xsl:template match="packet[(zglv[type='ResMet'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="ResMet" />
    </xsl:for-each>
  </xsl:template>

  <!-- V029 "<zap>" template -->
  <xsl:template name="ResMet">
    INSERT INTO AKUZ.T_V029_RESEARCH_METHOD (IDMET, N_MET, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDMET" />,
    <xsl:choose>
      <xsl:when test="N_MET != ''">
        SUBSTRING('<xsl:value-of select="N_MET" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDMET) DO UPDATE SET
    <xsl:if test="N_MET != ''">N_MET = EXCLUDED.N_MET,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V031_GroupDrug: V031.xml -->
  <xsl:template match="packet[(zglv[type='GroupDrug'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="GroupDrug" />
    </xsl:for-each>
  </xsl:template>

  <!-- V031 "<zap>" template -->
  <xsl:template name="GroupDrug">
    INSERT INTO AKUZ.T_V031_GROUP_DRUG (V031GroupDrugID, DrugGroupCode, ManIndMNN, DrugGroup, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="DrugGroupCode" />,
    <xsl:choose>
      <xsl:when test="ManIndMNN != ''">
        <xsl:value-of select="ManIndMNN" />
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="DrugGroup" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (DrugGroupCode, DATEBEG) DO UPDATE SET
    <xsl:if test="ManIndMNN != ''">ManIndMNN = EXCLUDED.ManIndMNN,</xsl:if>
    <xsl:if test="DrugGroup != ''">DrugGroup = EXCLUDED.DrugGroup,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V032_DrugGrReg: V032.xml -->
  <xsl:template match="packet[(zglv[type='DrugGrReg'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="DrugGrReg" />
    </xsl:for-each>
  </xsl:template>

  <!-- V032 "<zap>" template -->
  <xsl:template name="DrugGrReg">
    INSERT INTO AKUZ.T_V032_DRUG_GR_REG (V032DrugGrRegID, ScheDrugGrCd, Name, SchemCode, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ScheDrugGrCd" />', 1, 255),
    <xsl:choose>
      <xsl:when test="Name != ''">
        SUBSTRING('<xsl:value-of select="Name" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    <xsl:choose>
      <xsl:when test="SchemCode != ''">
        SUBSTRING('<xsl:value-of select="SchemCode" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ScheDrugGrCd, DATEBEG) DO UPDATE SET
    <xsl:if test="Name != ''">Name = EXCLUDED.Name,</xsl:if>
    <xsl:if test="SchemCode != ''">SchemCode = EXCLUDED.SchemCode,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V033_SchemCodDrug: V033.xml -->
  <xsl:template match="packet[(zglv[type='SchemCodDrug'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="SchemCodDrug" />
    </xsl:for-each>
  </xsl:template>

  <!-- V033 "<zap>" template -->
  <xsl:template name="SchemCodDrug">
    INSERT INTO AKUZ.T_V033_SCHEM_COD_DRUG (V033SchemCodDrugID, ScheDrugGrCd, DrugCode, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ScheDrugGrCd" />', 1, 255),
    SUBSTRING('<xsl:value-of select="DrugCode" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ScheDrugGrCd, DrugCode, DATEBEG) DO UPDATE SET
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V022_ModPac: V022.xml -->
  <xsl:template match="packet[(zglv[type='ModPac'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="ModPac" />
    </xsl:for-each>
  </xsl:template>

  <!-- V022 "<zap>" template -->
  <xsl:template name="ModPac">
    INSERT INTO AKUZ.T_V022_PATIENT_MODEL (IDMPAC, MPACNAME, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDMPAC" />,
    <xsl:choose>
      <xsl:when test="MPACNAME != ''">
        SUBSTRING('<xsl:value-of select="MPACNAME" />', 1, 2000)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDMPAC) DO UPDATE SET
    <xsl:if test="MPACNAME != ''">MPACNAME = EXCLUDED.MPACNAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- N001_OnkPrOt: N001.xml -->
  <xsl:template match="packet[(zglv[type='OnkPrOt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkPrOt" />
    </xsl:for-each>
  </xsl:template>

  <!-- N001 "<zap>" template -->
  <xsl:template name="OnkPrOt">
    INSERT INTO AKUZ.T_N001_OnkPrOt (N001ID, ID_PrOt, PrOt_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_PrOt" />,
    SUBSTRING('<xsl:value-of select="PrOt_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_PrOt, DATEBEG) DO UPDATE SET
    <xsl:if test="PrOt_NAME != ''">PrOt_NAME = EXCLUDED.PrOt_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N001_OnkPrOt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N001_OnkPrOt tt
    WHERE t.ID_PrOt = tt.ID_PrOt AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N002_OnkStad: N002.xml -->
  <xsl:template match="packet[(zglv[type='OnkStad'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkStad" />
    </xsl:for-each>
  </xsl:template>

  <!-- N002 "<zap>" template -->
  <xsl:template name="OnkStad">
    INSERT INTO AKUZ.T_N002_OnkStad (N002ID, ID_St, DS_St, KOD_St, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_St" />,
    SUBSTRING('<xsl:value-of select="DS_St" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_St" />', 1, 5),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_St, DATEBEG) DO UPDATE SET
    DS_St = EXCLUDED.DS_St,
    KOD_St = EXCLUDED.KOD_St,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N002_OnkStad t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N002_OnkStad tt
    WHERE t.ID_St = tt.ID_St AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N003_OnkT: N003.xml -->
  <xsl:template match="packet[(zglv[type='OnkT'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkT" />
    </xsl:for-each>
  </xsl:template>

  <!-- N003 "<zap>" template -->
  <xsl:template name="OnkT">
    INSERT INTO AKUZ.T_N003_OnkT (N003ID, ID_T, DS_T, KOD_T, T_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_T" />,
    SUBSTRING('<xsl:value-of select="DS_T" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_T" />', 1, 255),
    SUBSTRING('<xsl:value-of select="T_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_T, DATEBEG) DO UPDATE SET
    DS_T = EXCLUDED.DS_T,
    KOD_T = EXCLUDED.KOD_T,
    <xsl:if test="T_NAME != ''">T_NAME = EXCLUDED.T_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N003_OnkT t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N003_OnkT tt
    WHERE t.ID_T = tt.ID_T AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N004_OnkN: N004.xml -->
  <xsl:template match="packet[(zglv[type='OnkN'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkN" />
    </xsl:for-each>
  </xsl:template>

  <!-- N004 "<zap>" template -->
  <xsl:template name="OnkN">
    INSERT INTO AKUZ.T_N004_OnkN (N004ID, ID_N, DS_N, KOD_N, N_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_N" />,
    SUBSTRING('<xsl:value-of select="DS_N" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_N" />', 1, 5),
    SUBSTRING('<xsl:value-of select="N_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_N, DATEBEG) DO UPDATE SET
    DS_N = EXCLUDED.DS_N,
    KOD_N = EXCLUDED.KOD_N,
    <xsl:if test="N_NAME != ''">N_NAME = EXCLUDED.N_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N004_OnkN t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N004_OnkN tt
    WHERE t.ID_N = tt.ID_N AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N005_OnkM: N005.xml -->
  <xsl:template match="packet[(zglv[type='OnkM'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N005 "<zap>" template -->
  <xsl:template name="OnkM">
    INSERT INTO AKUZ.T_N005_OnkM (N005ID, ID_M, DS_M, KOD_M, M_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_M" />,
    SUBSTRING('<xsl:value-of select="DS_M" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_M" />', 1, 5),
    SUBSTRING('<xsl:value-of select="M_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_M, DATEBEG) DO UPDATE SET
    DS_M = EXCLUDED.DS_M,
    KOD_M = EXCLUDED.KOD_M,
    <xsl:if test="M_NAME != ''">M_NAME = EXCLUDED.M_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N005_OnkM t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N005_OnkM tt
    WHERE t.ID_M = tt.ID_M AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N006_OnkTNM: N006.xml -->
  <xsl:template match="packet[(zglv[type='OnkTNM'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkTNM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N006 "<zap>" template -->
  <xsl:template name="OnkTNM">
    INSERT INTO AKUZ.T_N006_OnkTNM (N006ID, ID_gr, DS_gr, ID_St, ID_T, ID_N, ID_M, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_gr" />,
    SUBSTRING('<xsl:value-of select="DS_gr" />', 1, 5),
    <xsl:value-of select="ID_St" />,
    <xsl:value-of select="ID_T" />,
    <xsl:value-of select="ID_N" />,
    <xsl:value-of select="ID_M" />,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_gr, DATEBEG) DO UPDATE SET
    DS_gr = EXCLUDED.DS_gr,
    ID_St = EXCLUDED.ID_St,
    ID_T = EXCLUDED.ID_T,
    ID_N = EXCLUDED.ID_N,
    ID_M = EXCLUDED.ID_M,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N006_OnkTNM t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N006_OnkTNM tt
    WHERE t.ID_gr = tt.ID_gr AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N007_OnkMrf: N007.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrf'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrf" />
    </xsl:for-each>
  </xsl:template>
  <!-- N007 "<zap>" template -->
  <xsl:template name="OnkMrf">
    INSERT INTO AKUZ.T_N007_OnkMrf (N007ID, ID_Mrf, Mrf_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_Mrf" />,
    SUBSTRING('<xsl:value-of select="Mrf_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_Mrf, DATEBEG) DO UPDATE SET
    <xsl:if test="Mrf_NAME != ''">Mrf_NAME = EXCLUDED.Mrf_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N007_OnkMrf t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N007_OnkMrf tt
    WHERE t.ID_Mrf = tt.ID_Mrf AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N008_OnkMrfRt: N008.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrfRt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrfRt" />
    </xsl:for-each>
  </xsl:template>
  <!-- N008 "<zap>" template -->
  <xsl:template name="OnkMrfRt">
    INSERT INTO AKUZ.T_N008_OnkMrfRt (N008ID, ID_R_M, ID_Mrf, R_M_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_R_M" />,
    <xsl:value-of select="ID_Mrf" />,
    SUBSTRING('<xsl:value-of select="R_M_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_R_M, DATEBEG) DO UPDATE SET
    ID_Mrf = EXCLUDED.ID_Mrf,
    <xsl:if test="R_M_NAME != ''">R_M_NAME = EXCLUDED.R_M_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N008_OnkMrfRt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N008_OnkMrfRt tt
    WHERE t.ID_R_M = tt.ID_R_M AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N009_OnkMrtDS: N009.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrtDS'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrtDS" />
    </xsl:for-each>
  </xsl:template>
  <!-- N009 "<zap>" template -->
  <xsl:template name="OnkMrtDS">
    INSERT INTO AKUZ.T_N009_OnkMrtDS (N009ID, ID_M_D, DS_Mrf, ID_Mrf, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_M_D" />,
    SUBSTRING('<xsl:value-of select="DS_Mrf" />', 1, 5),
    <xsl:value-of select="ID_Mrf" />,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_M_D, DATEBEG) DO UPDATE SET
    DS_Mrf = EXCLUDED.DS_Mrf,
    ID_Mrf = EXCLUDED.ID_Mrf,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N009_OnkMrtDS t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N009_OnkMrtDS tt
    WHERE t.ID_M_D = tt.ID_M_D AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N010_OnkIgh: N010.xml -->
  <xsl:template match="packet[(zglv[type='OnkIgh'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIgh" />
    </xsl:for-each>
  </xsl:template>
  <!-- N010 "<zap>" template -->
  <xsl:template name="OnkIgh">
    INSERT INTO AKUZ.T_N010_OnkIgh (N010ID, ID_Igh, KOD_Igh, Igh_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="KOD_Igh" />', 1, 255),
    SUBSTRING('<xsl:value-of select="Igh_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_Igh, DATEBEG) DO UPDATE SET
    KOD_Igh = EXCLUDED.KOD_Igh,
    <xsl:if test="Igh_NAME != ''">Igh_NAME = EXCLUDED.Igh_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N010_OnkIgh t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N010_OnkIgh tt
    WHERE t.ID_Igh = tt.ID_Igh AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N011_OnkIghRt: N011.xml -->
  <xsl:template match="packet[(zglv[type='OnkIghRt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIghRt" />
    </xsl:for-each>
  </xsl:template>
  <!-- N011 "<zap>" template -->
  <xsl:template name="OnkIghRt">
    INSERT INTO AKUZ.T_N011_OnkIghRt (N011ID, ID_R_I, ID_Igh, KOD_R_I, R_I_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_R_I" />,
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="KOD_R_I" />', 1, 255),
    SUBSTRING('<xsl:value-of select="R_I_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_R_I, DATEBEG) DO UPDATE SET
    ID_Igh = EXCLUDED.ID_Igh,
    KOD_R_I = EXCLUDED.KOD_R_I,
    <xsl:if test="R_I_NAME != ''">R_I_NAME = EXCLUDED.R_I_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N011_OnkIghRt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N011_OnkIghRt tt
    WHERE t.ID_R_I = tt.ID_R_I AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N012_OnkIghDS: N012.xml -->
  <xsl:template match="packet[(zglv[type='OnkIghDS'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIghDS" />
    </xsl:for-each>
  </xsl:template>
  <!-- N012 "<zap>" template -->
  <xsl:template name="OnkIghDS">
    INSERT INTO AKUZ.T_N012_OnkIghDS (N012ID, ID_I_D, ID_Igh, DS_Igh, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_I_D" />,
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="DS_Igh" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_I_D, DATEBEG) DO UPDATE SET
    ID_Igh = EXCLUDED.ID_Igh,
    DS_Igh = EXCLUDED.DS_Igh,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N012_OnkIghDS t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N012_OnkIghDS tt
    WHERE t.ID_I_D = tt.ID_I_D AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N013_OnkLech: N013.xml -->
  <xsl:template match="packet[(zglv[type='OnkLech'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLech" />
    </xsl:for-each>
  </xsl:template>
  <!-- N013 "<zap>" template -->
  <xsl:template name="OnkLech">
    INSERT INTO AKUZ.T_N013_OnkLech (N013ID, ID_TLech, TLech_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLech" />,
    SUBSTRING('<xsl:value-of select="TLech_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLech, DATEBEG) DO UPDATE SET
    <xsl:if test="TLech_NAME != ''">TLech_NAME = EXCLUDED.TLech_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N013_OnkLech t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N013_OnkLech tt
    WHERE t.ID_TLech = tt.ID_TLech AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N014_OnkHir: N014.xml -->
  <xsl:template match="packet[(zglv[type='OnkHir'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkHir" />
    </xsl:for-each>
  </xsl:template>
  <!-- N014 "<zap>" template -->
  <xsl:template name="OnkHir">
    INSERT INTO AKUZ.T_N014_OnkHir (N014ID, ID_THir, THir_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_THir" />,
    SUBSTRING('<xsl:value-of select="THir_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_THir, DATEBEG) DO UPDATE SET
    <xsl:if test="THir_NAME != ''">THir_NAME = EXCLUDED.THir_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N014_OnkHir t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N014_OnkHir tt
    WHERE t.ID_THir = tt.ID_THir AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N015_OnkLek_L: N015.xml -->
  <xsl:template match="packet[(zglv[type='OnkLek_L'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLek_L" />
    </xsl:for-each>
  </xsl:template>
  <!-- N015 "<zap>" template -->
  <xsl:template name="OnkLek_L">
    INSERT INTO AKUZ.T_N015_OnkLek_L (N015ID, ID_TLek_L, TLek_NAME_L, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLek_L" />,
    SUBSTRING('<xsl:value-of select="TLek_NAME_L" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLek_L, DATEBEG) DO UPDATE SET
    <xsl:if test="TLek_NAME_L != ''">TLek_NAME_L = EXCLUDED.TLek_NAME_L,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N015_OnkLek_L t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N015_OnkLek_L tt
    WHERE t.ID_TLek_L = tt.ID_TLek_L AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N016_OnkLek_V: N016.xml -->
  <xsl:template match="packet[(zglv[type='OnkLek_V'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLek_V" />
    </xsl:for-each>
  </xsl:template>
  <!-- N016 "<zap>" template -->
  <xsl:template name="OnkLek_V">
    INSERT INTO AKUZ.T_N016_OnkLek_V (N016ID, ID_TLek_V, TLek_NAME_V, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLek_V" />,
    SUBSTRING('<xsl:value-of select="TLek_NAME_V" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLek_V, DATEBEG) DO UPDATE SET
    <xsl:if test="TLek_NAME_V != ''">TLek_NAME_V = EXCLUDED.TLek_NAME_V,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N016_OnkLek_V t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N016_OnkLek_V tt
    WHERE t.ID_TLek_V = tt.ID_TLek_V AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N017_OnkLuch: N017.xml -->
  <xsl:template match="packet[(zglv[type='OnkLuch'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLuch" />
    </xsl:for-each>
  </xsl:template>
  <!-- N017 "<zap>" template -->
  <xsl:template name="OnkLuch">
    INSERT INTO AKUZ.T_N017_OnkLuch (N017ID, ID_TLuch, TLuch_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLuch" />,
    SUBSTRING('<xsl:value-of select="TLuch_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLuch, DATEBEG) DO UPDATE SET
    <xsl:if test="TLuch_NAME != ''">TLuch_NAME = EXCLUDED.TLuch_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N017_OnkLuch t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N017_OnkLuch tt
    WHERE t.ID_TLuch = tt.ID_TLuch AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N018_OnkReas: N018.xml -->
  <xsl:template match="packet[(zglv[type='OnkReas'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkReas" />
    </xsl:for-each>
  </xsl:template>
  <!-- N018 "<zap>" template -->
  <xsl:template name="OnkReas">
    INSERT INTO AKUZ.T_N018_OnkReas (N018ID, ID_REAS, REAS_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_REAS" />,
    SUBSTRING('<xsl:value-of select="REAS_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_REAS, DATEBEG) DO UPDATE SET
    <xsl:if test="REAS_NAME != ''">REAS_NAME = EXCLUDED.REAS_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N018_OnkReas t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N018_OnkReas tt
    WHERE t.ID_REAS = tt.ID_REAS AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N019_OnkCons: N019.xml -->
  <xsl:template match="packet[(zglv[type='OnkCons'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkCons" />
    </xsl:for-each>
  </xsl:template>
  <!-- N019 "<zap>" template -->
  <xsl:template name="OnkCons">
    INSERT INTO AKUZ.T_N019_OnkCons (N019ID, ID_CONS, CONS_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_CONS" />,
    SUBSTRING('<xsl:value-of select="CONS_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_CONS, DATEBEG) DO UPDATE SET
    <xsl:if test="CONS_NAME != ''">CONS_NAME = EXCLUDED.CONS_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N019_OnkCons t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N019_OnkCons tt
    WHERE t.ID_CONS = tt.ID_CONS AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N021_OnkLpsh: N021.xml -->
  <xsl:template match="packet[(zglv[type='OnkLpsh'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLpsh" />
    </xsl:for-each>
  </xsl:template>

  <!-- N021 "<zap>" template -->
  <xsl:template name="OnkLpsh">
    INSERT INTO AKUZ.T_N021_OnkLpsh (N021ID, ID_ZAP, CODE_SH, ID_LEKP, DATEBEG, DATEEND, ID_LEKP_EXT, LEKP_EXT)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ID_ZAP" />', 1, 15),
    SUBSTRING('<xsl:value-of select="CODE_SH" />', 1, 10),
    SUBSTRING('<xsl:value-of select="ID_LEKP" />', 1, 6),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="ID_LEKP_EXT" />', 1, 25),
    SUBSTRING('<xsl:value-of select="LEKP_EXT" />', 1, 256)
    ON CONFLICT (ID_ZAP, DATEBEG) DO UPDATE SET
    CODE_SH = EXCLUDED.CODE_SH,
    ID_LEKP = EXCLUDED.ID_LEKP,
    DATEEND = EXCLUDED.DATEEND,
    ID_LEKP_EXT = EXCLUDED.ID_LEKP_EXT,
    LEKP_EXT = EXCLUDED.LEKP_EXT;

    UPDATE AKUZ.T_N021_OnkLpsh t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N021_OnkLpsh tt
    WHERE t.ID_ZAP = tt.ID_ZAP AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N020_OnkLekp: N020.xml -->
  <xsl:template match="packet[(zglv[type='OnkLekp'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLekp" />
    </xsl:for-each>
  </xsl:template>

  <!-- N020 "<zap>" template -->
  <xsl:template name="OnkLekp">
    INSERT INTO AKUZ.T_N020_OnkLekp (N020ID, ID_LEKP, MNN, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ID_LEKP" />', 1, 6),
    SUBSTRING('<xsl:value-of select="MNN" />', 1, 300),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_LEKP, DATEBEG) DO UPDATE SET
    MNN = EXCLUDED.MNN,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N020_OnkLekp t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N020_OnkLekp tt
    WHERE t.ID_LEKP = tt.ID_LEKP AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- CouncilGoals: N019.xml -->
  <xsl:template match="packet[(zglv[type='N019CouncilGoals'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="N019CouncilGoals" />
    </xsl:for-each>
  </xsl:template>

  <!-- N019 "<zap>" template -->
  <xsl:template name="N019CouncilGoals">
    INSERT INTO AKUZ.T_COUNCIL_GOAL (CouncilGoalID, Code, Name, Type, DateBegin, DateEnd)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_CONS" />,
    SUBSTRING('<xsl:value-of select="CONS_NAME" />', 1, 255),
    1,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (Code, Type) WHERE Type = 1 DO UPDATE SET
    <xsl:if test="CONS_NAME != ''">Name = EXCLUDED.Name,</xsl:if>
    DateEnd = EXCLUDED.DateEnd;
  </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>