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


  
  <!-- INSURER: F002.xml attributes -->
  <xsl:template match="packet[(insCompany)]">
    <xsl:for-each select="insCompany">
      declare @insDates table
        (
            dBegin datetime,
            dEnd datetime,
            nameE int,
            nalP int
        )
      declare @insLicenses table
        (
            n_doc varchar(40),
            dStart datetime,
            dEnd datetime
        )
      declare @insAdvices table
        (
            duved datetime,
            kolZl int
        )
      <xsl:call-template name="F002insCompanys" />
    </xsl:for-each>
  </xsl:template>
  <!-- F002 insCompany template -->
  <xsl:template name="F002insCompanys" >
    update i
    set i.[Name] = LEFT('<xsl:value-of select="nam_smop" />', 250)
        ,i.[IndexLegal] = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
        ,i.[AddressLegal] = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 250)
        ,i.[IndexReal] = LEFT('<xsl:value-of select="pstAddress/index_f" />', 6)
        ,i.[Address] = LEFT('<xsl:value-of select="pstAddress/addr_f" />', 250)
        ,i.[DirectorLastName] = LEFT('<xsl:value-of select="fam_ruk" />', 40)
        ,i.[DirectorFirstName] = LEFT('<xsl:value-of select="im_ruk" />', 40)
        ,i.[DirectorFatherName] = LEFT('<xsl:value-of select="ot_ruk" />', 40)
        ,i.[Phone] = LEFT('<xsl:value-of select="phone" />', 40)
        ,i.[Fax] = LEFT('<xsl:value-of select="fax" />', 40)
        ,i.[EMail] = LEFT('<xsl:value-of select="e_mail" />', 30)
        ,i.[Date_red] = CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
        ,i.[INN] = LEFT('<xsl:value-of select="inn" />', 12)
        ,i.[KPP] = LEFT('<xsl:value-of select="KPP" />', 9)
        ,i.[OGRN] = LEFT('<xsl:value-of select="Ogrn" />', 15)
        ,i.[ShortName] = LEFT('<xsl:value-of select="nam_smok" />', 250)
        ,i.[OKOPF] = LEFT('<xsl:value-of select="okopf" />', 8)
        ,i.[SAIT] = LEFT('<xsl:value-of select="www" />', 80)
        ,i.[ORG] = <xsl:value-of select="org" />
    from [MRDS].[T_INSURER] i
    where i.SmoCod = LEFT('<xsl:value-of select="smocod" />', 5)

    INSERT INTO [MRDS].[T_INSURER]
               ([Name] ,[IndexLegal] ,[AddressLegal] ,[IndexReal] ,[Address] ,[DirectorLastName] ,[DirectorFirstName] ,[DirectorFatherName] ,[Phone] ,[Fax] ,[EMail]
               ,[Date_red] ,[INN] ,[KPP] ,[OGRN] ,[ShortName] ,[SmoCod] ,[OKOPF] ,[SAIT] ,[ORG])
    SELECT
               LEFT('<xsl:value-of select="nam_smop" />', 250)
             , LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
             , LEFT('<xsl:value-of select="jurAddress/addr_j" />', 250)
             , LEFT('<xsl:value-of select="pstAddress/index_f" />', 6)
             , LEFT('<xsl:value-of select="pstAddress/addr_f" />', 250)
             , 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" />', 30)
             , CONVERT(datetime, '<xsl:value-of select="d_edit" />', 104)
             , LEFT('<xsl:value-of select="inn" />', 12)
             , LEFT('<xsl:value-of select="KPP" />', 9)
             , LEFT('<xsl:value-of select="Ogrn" />', 15)
             , LEFT('<xsl:value-of select="nam_smok" />', 250)
             , LEFT('<xsl:value-of select="smocod" />', 5)
             , LEFT('<xsl:value-of select="okopf" />', 8)
             , LEFT('<xsl:value-of select="www" />', 80)
             , <xsl:value-of select="org" />
    where not exists
      (
          select *
          from [MRDS].[T_INSURER] i
          where i.[SmoCod] = LEFT('<xsl:value-of select="smocod" />', 5)
      )

    <xsl:for-each select="insInclude">
      INSERT INTO @insDates (dBegin, dEnd, nameE, nalP)
      SELECT CONVERT(datetime, '<xsl:value-of select="d_begin" />', 104)
             , <xsl:if test="d_end != ''">CONVERT(datetime, '<xsl:value-of select="d_end" />', 104)</xsl:if><xsl:if test="d_end = ''">NULL</xsl:if>
             , <xsl:if test="name_e != ''"><xsl:value-of select="name_e" /></xsl:if><xsl:if test="name_e = ''">NULL</xsl:if>
             , <xsl:if test="Nal_p != ''"><xsl:value-of select="Nal_p" /></xsl:if><xsl:if test="Nal_p = ''">NULL</xsl:if>
    </xsl:for-each>

    <xsl:for-each select="licenziy">
      INSERT INTO @insLicenses (n_doc, dStart, dEnd)
      SELECT LEFT('<xsl:value-of select="n_doc" />', 40)
             , CONVERT(datetime, '<xsl:value-of select="d_start" />', 104)
             , <xsl:if test="date_e != ''">CONVERT(datetime, '<xsl:value-of select="date_e" />', 104)</xsl:if><xsl:if test="date_e = ''">NULL</xsl:if>
    </xsl:for-each>

    <xsl:for-each select="insAdvice">
      INSERT INTO @insAdvices (duved, kolZl)
      SELECT CONVERT(datetime, '<xsl:value-of select="DUVED" />', 104)
             , <xsl:if test="kol_zl != ''"><xsl:value-of select="kol_zl" /></xsl:if><xsl:if test="kol_zl = ''">NULL</xsl:if>
    </xsl:for-each>

    update i
    set i.[Date_b] = (select top 1 dBegin from @insDates order by dBegin desc)
        ,i.[Date_e] = (select top 1 dEnd from @insDates order by dBegin desc)
        ,i.[NAME_E] = (select top 1 nameE from @insDates order by dBegin desc)
        ,i.[NAL_P] = (select top 1 nalP from @insDates order by dBegin desc)
        ,i.[Gk_start] = (select top 1 dStart from @insLicenses order by dStart desc)
        ,i.[Gk_end] = (select top 1 dEnd from @insLicenses order by dStart desc)
        ,i.[N_DOC] = (select top 1 n_doc from @insLicenses order by dStart desc)
        ,i.[DUVED] = (select top 1 duved from @insAdvices order by duved desc)
        ,i.[KOL_ZL] = (select top 1 kolZl from @insAdvices order by duved desc)
    from [MRDS].[T_INSURER] i
    where i.SmoCod = LEFT('<xsl:value-of select="smocod" />', 5)
    GO
  </xsl:template>

</xsl:stylesheet>