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

  <!-- F003_MO: F003.xml -->
  <xsl:template match="packet[(medCompany)]">
    <xsl:for-each select="medCompany">
      <!-- insert or update LPU_MAIN -->
      <xsl:call-template name="medCompany" />
    </xsl:for-each>
  </xsl:template>
  
  <!-- F003 "<medCompany>" template -->
  <xsl:template name="medCompany" >
    if not exists(select * from [APS].[T_LPU_MAIN] s where s.[MCOD] = '<xsl:value-of select="mcod" />')
    INSERT INTO [APS].[T_LPU_MAIN] ([TF_OKATO] ,[MCOD] ,[NAM_MOP] ,[NAM_MOK] ,[INN] ,[OGRN] ,[KPP] ,[INDEX_J] ,[ADDR_J] ,[OKOPF] ,[VEDPRI] 
    ,[ORG] ,[FAM_RUK] ,[IM_RUK] ,[OT_RUK] ,[PHONE] ,[FAX] ,[E_MAIL] ,[WWW] ,[D_BEGIN] ,[D_END] ,[D_EDIT] ,[Holidays] ,[IsActive])
    SELECT
    LEFT('<xsl:value-of select="tf_okato" />', 5)
    , '<xsl:value-of select="mcod" />'
    , LEFT('<xsl:value-of select="nam_mop" />', 250)
    , LEFT('<xsl:value-of select="nam_mok" />', 250)
    , LEFT('<xsl:value-of select="inn" />', 12)
    , LEFT('<xsl:value-of select="Ogrn" />', 15)
    , LEFT('<xsl:value-of select="KPP" />', 9)
    , LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , (select top 1 o005.O005OkopfID from APS.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />')
    , (select top 1 dep.IDVED from APS.F007_DEPARTMENT dep where IDVED = '<xsl:value-of select="vedpri" />')
    , <xsl:value-of select="org" />
    , LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , LEFT('<xsl:value-of select="im_ruk" />', 40)
    , LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , LEFT('<xsl:value-of select="phone" />', 40)
    , LEFT('<xsl:value-of select="fax" />', 40)
    , LEFT('<xsl:value-of select="e_mail" />', 50)
    , LEFT('<xsl:value-of select="www" />', 50)
    , CONVERT(datetime, '<xsl:value-of select="medInclude/d_begin" />', 104)
    , <xsl:choose>
      <xsl:when test="d_end != ''">
        CONVERT(datetime, '<xsl:value-of select="d_end" />', 104)
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
    , 64
    , 1
    else
    update l
    set l.TF_OKATO = LEFT('<xsl:value-of select="tf_okato" />', 5)
    , l.NAM_MOP = LEFT('<xsl:value-of select="nam_mop" />', 250)
    , l.NAM_MOK = LEFT('<xsl:value-of select="nam_mok" />', 250)
    , l.INN = LEFT('<xsl:value-of select="inn" />', 12)
    , l.OGRN = LEFT('<xsl:value-of select="Ogrn" />', 15)
    , l.KPP = LEFT('<xsl:value-of select="KPP" />', 9)
    , l.INDEX_J = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , l.ADDR_J = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , l.OKOPF = (select top 1 o005.O005OkopfID from APS.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />')
    , l.VEDPRI = <xsl:value-of select="vedpri" />
    , l.ORG = <xsl:value-of select="org" />
    , l.FAM_RUK = LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , l.IM_RUK = LEFT('<xsl:value-of select="im_ruk" />', 40)
    , l.OT_RUK = LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , l.PHONE = LEFT('<xsl:value-of select="phone" />', 40)
    , l.FAX = LEFT('<xsl:value-of select="fax" />', 40)
    , l.E_MAIL = LEFT('<xsl:value-of select="e_mail" />', 50)
    , l.WWW = LEFT('<xsl:value-of select="www" />', 50)
    , l.D_BEGIN = CONVERT(datetime, '<xsl:value-of select="medInclude/d_begin" />', 104)
    , l.D_END = <xsl:choose>
      <xsl:when test="d_end != ''">
        CONVERT(datetime, '<xsl:value-of select="d_end" />', 104)
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , l.D_EDIT = CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
    , l.Holidays = 64
    , l.IsActive = 1
    from APS.T_LPU_MAIN l
    where l.MCOD = '<xsl:value-of select="mcod" />'

    update l
    set l.IsActive = case when l.D_END is NULL or l.D_END &gt; GETDATE() then 1 else 0 end
    from APS.T_LPU_MAIN l
    where l.MCOD = '<xsl:value-of select="mcod" />'
    GO
  </xsl:template>


  <!-- F002_SMO: F002.xml -->
  <xsl:template match="packet[(insCompany)]">
    <xsl:for-each select="insCompany">
      <!-- insert or update F002_SMO -->
      <xsl:call-template name="insCompany" />
    </xsl:for-each>
  </xsl:template>
  <!-- F002 "<insCompany>" template -->
  <xsl:template name="insCompany" >
    <xsl:variable name="d_start">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_start"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="n_doc">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="n_doc"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="date_e">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="date_e"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="d_begin">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_begin"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="d_end">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_end"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="name_e">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="name_e"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="DUVED">
      <xsl:for-each select="insAdvice">
        <xsl:sort select="DUVED" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="DUVED"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="Nal_p">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="Nal_p"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="kol_zl">
      <xsl:for-each select="insAdvice">
        <xsl:sort select="DUVED" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="kol_zl"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    if not exists(select * from [APS].[T_F002_SMO] s where s.[SMOCOD] = '<xsl:value-of select="smocod" />')
    INSERT INTO [APS].[T_F002_SMO]
               ([TF_OKATO] ,[SMOCOD] ,[NAM_SMOP] ,[NAM_SMOK] ,[INN] ,[OGRN] ,[KPP] ,[INDEX_J] ,[ADDR_J] ,[OKOPF] ,[ORG] ,[FAM_RUK] ,[IM_RUK] ,[OT_RUK] ,[PHONE] ,[FAX] ,[E_MAIL] ,[N_DOC] ,[D_START] ,[DATA_E] ,[WWW] ,[D_BEGIN] ,[D_END] ,[NAME_E] ,[DUVED] ,[D_EDIT] ,[INDEX_F] ,[ADDR_F] ,[NAL_P] ,[KOL_ZL] ,[IsActive] ,[Local])
    SELECT
               LEFT('<xsl:value-of select="tf_okato" />', 5)
               , LEFT('<xsl:value-of select="smocod" />', 5)
               , LEFT('<xsl:value-of select="nam_smop" />', 254)
               , LEFT('<xsl:value-of select="nam_smok" />', 250)
               , LEFT('<xsl:value-of select="inn" />', 12)
               , LEFT('<xsl:value-of select="Ogrn" />', 15)
               , LEFT('<xsl:value-of select="KPP" />', 9)
               , LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
               , LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
               , (select top 1 o005.O005OkopfID from APS.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />')
               , <xsl:value-of select="org" />
               , LEFT('<xsl:value-of select="fam_ruk" />', 40)
               , LEFT('<xsl:value-of select="im_ruk" />', 40)
               , LEFT('<xsl:value-of select="ot_ruk" />', 40)
               , LEFT('<xsl:value-of select="phone" />', 40)
               , LEFT('<xsl:value-of select="fax" />', 40)
               , LEFT('<xsl:value-of select="e_mail" />', 50)
               , LEFT('<xsl:value-of select="$n_doc" />', 20)
               , CONVERT(datetime, '<xsl:value-of select="$d_start" />', 104)
               , <xsl:choose>
                   <xsl:when test="$date_e != ''">
                     CONVERT(datetime, '<xsl:value-of select="$date_e" />', 104)
                   </xsl:when>
                   <xsl:otherwise>
                     NULL
                   </xsl:otherwise>
                 </xsl:choose>
               , LEFT('<xsl:value-of select="www" />', 100)
               , CONVERT(datetime, '<xsl:value-of select="$d_begin" />', 104)
               , <xsl:choose>
                   <xsl:when test="$d_end != ''">
                     CONVERT(datetime, '<xsl:value-of select="$d_end" />', 104)
                   </xsl:when>
                   <xsl:otherwise>
                     NULL
                   </xsl:otherwise>
                 </xsl:choose>
               , LEFT('<xsl:value-of select="$name_e" />', 10)
               , <xsl:choose>
                   <xsl:when test="$DUVED != ''">
                     CONVERT(datetime, '<xsl:value-of select="$DUVED" />', 104)
                   </xsl:when>
                   <xsl:otherwise>
                     NULL
                   </xsl:otherwise>
                 </xsl:choose>
               , <xsl:choose>
                   <xsl:when test="d_edit != ''">
                     CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
                   </xsl:when>
                   <xsl:otherwise>
                     NULL
                   </xsl:otherwise>
                 </xsl:choose>
               , LEFT('<xsl:value-of select="pstAddress/index_f" />', 254)
               , LEFT('<xsl:value-of select="pstAddress/addr_f" />', 254)
               , LEFT('<xsl:value-of select="$Nal_p" />', 1)
               , <xsl:choose>
                   <xsl:when test="$kol_zl != ''">
                     <xsl:value-of select="$kol_zl" />
                   </xsl:when>
                   <xsl:otherwise>
                     NULL
                   </xsl:otherwise>
                 </xsl:choose>
               , 1
               , 0
    else
    UPDATE s
    set
       s.TF_OKATO = LEFT('<xsl:value-of select="tf_okato" />', 5)
       , s.NAM_SMOP = LEFT('<xsl:value-of select="nam_smop" />', 254)
       , s.NAM_SMOK = LEFT('<xsl:value-of select="nam_smok" />', 250)
       , s.INN = LEFT('<xsl:value-of select="inn" />', 12)
       , s.OGRN = LEFT('<xsl:value-of select="Ogrn" />', 15)
       , s.KPP = LEFT('<xsl:value-of select="KPP" />', 9)
       , s.INDEX_J = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
       , s.ADDR_J = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
       , s.OKOPF = (select top 1 o005.O005OkopfID from APS.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />')
       , s.ORG = <xsl:value-of select="org" />
       , s.FAM_RUK = LEFT('<xsl:value-of select="fam_ruk" />', 40)
       , s.IM_RUK = LEFT('<xsl:value-of select="im_ruk" />', 40)
       , s.OT_RUK = LEFT('<xsl:value-of select="ot_ruk" />', 40)
       , s.PHONE = LEFT('<xsl:value-of select="phone" />', 40)
       , s.FAX = LEFT('<xsl:value-of select="fax" />', 40)
       , s.E_MAIL = LEFT('<xsl:value-of select="e_mail" />', 50)
       , s.N_DOC = LEFT('<xsl:value-of select="$n_doc" />', 20)
       , s.D_START = CONVERT(datetime, '<xsl:value-of select="$d_start" />', 104)
       , s.DATA_E = <xsl:choose>
           <xsl:when test="$date_e != ''">
             CONVERT(datetime, '<xsl:value-of select="$date_e" />', 104)
           </xsl:when>
           <xsl:otherwise>
             NULL
           </xsl:otherwise>
         </xsl:choose>
       , s.WWW = LEFT('<xsl:value-of select="www" />', 100)
       , s.D_BEGIN = CONVERT(datetime, '<xsl:value-of select="$d_begin" />', 104)
       , s.D_END = <xsl:choose>
           <xsl:when test="$d_end != ''">
             CONVERT(datetime, '<xsl:value-of select="$d_end" />', 104)
           </xsl:when>
           <xsl:otherwise>
             NULL
           </xsl:otherwise>
         </xsl:choose>
       , s.NAME_E = LEFT('<xsl:value-of select="$name_e" />', 10)
       , s.DUVED = <xsl:choose>
           <xsl:when test="$DUVED != ''">
             CONVERT(datetime, '<xsl:value-of select="$DUVED" />', 104)
           </xsl:when>
           <xsl:otherwise>
             NULL
           </xsl:otherwise>
         </xsl:choose>
       , s.D_EDIT = <xsl:choose>
           <xsl:when test="d_edit != ''">
             CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
           </xsl:when>
           <xsl:otherwise>
             NULL
           </xsl:otherwise>
         </xsl:choose>
       , s.INDEX_F = LEFT('<xsl:value-of select="pstAddress/index_f" />', 254)
       , s.ADDR_F = LEFT('<xsl:value-of select="pstAddress/addr_f" />', 254)
       , s.NAL_P = LEFT('<xsl:value-of select="$Nal_p" />', 1)
       , s.KOL_ZL = <xsl:choose>
           <xsl:when test="$kol_zl != ''">
             <xsl:value-of select="$kol_zl" />
           </xsl:when>
           <xsl:otherwise>
             NULL
           </xsl:otherwise>
         </xsl:choose>
    FROM [APS].[T_F002_SMO] s
    where s.SMOCOD = LEFT('<xsl:value-of select="smocod" />', 5)
  </xsl:template>

</xsl:stylesheet>
