<?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">
      DROP TABLE IF EXISTS insDates;
      CREATE TEMP TABLE insDates
          (
              dBegin timestamp NULL,
              dEnd timestamp NULL,
              nameE integer NULL,
              nalP integer NULL
          );
      DROP TABLE IF EXISTS insLicenses;
      CREATE TEMP TABLE insLicenses
          (
              n_doc varchar(40) NULL,
              dStart timestamp NULL,
              dEnd timestamp NULL
          );
      DROP TABLE IF EXISTS insAdvices;
      CREATE TEMP TABLE insAdvices
          (
              duved timestamp NULL,
              kolZl integer NULL
          );
      <xsl:call-template name="F002insCompanys" />
    </xsl:for-each>
  </xsl:template>
  <!-- F002 insCompany template -->
  <xsl:template name="F002insCompanys" >
    update MRDS.T_INSURER i
    set Name = LEFT('<xsl:value-of select="nam_smop" />', 250)
        ,IndexLegal = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
        ,AddressLegal = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 250)
        ,IndexReal = LEFT('<xsl:value-of select="pstAddress/index_f" />', 6)
        ,Address = LEFT('<xsl:value-of select="pstAddress/addr_f" />', 250)
        ,DirectorLastName = LEFT('<xsl:value-of select="fam_ruk" />', 40)
        ,DirectorFirstName = LEFT('<xsl:value-of select="im_ruk" />', 40)
        ,DirectorFatherName = LEFT('<xsl:value-of select="ot_ruk" />', 40)
        ,Phone = LEFT('<xsl:value-of select="phone" />', 40)
        ,Fax = LEFT('<xsl:value-of select="fax" />', 40)
        ,EMail = LEFT('<xsl:value-of select="e_mail" />', 30)
        ,Date_red = to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
        ,INN = LEFT('<xsl:value-of select="inn" />', 12)
        ,KPP = LEFT('<xsl:value-of select="KPP" />', 9)
        ,OGRN = LEFT('<xsl:value-of select="Ogrn" />', 15)
        ,ShortName = LEFT('<xsl:value-of select="nam_smok" />', 250)
        ,OKOPF = LEFT('<xsl:value-of select="okopf" />', 8)
        ,SAIT = LEFT('<xsl:value-of select="www" />', 80)
        ,ORG = <xsl:value-of select="org" />
    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)
             , to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
             , 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 to_timestamp('<xsl:value-of select="d_begin" />', 'DD.MM.YYYY')
             , <xsl:if test="d_end != ''">to_timestamp('<xsl:value-of select="d_end" />', 'DD.MM.YYYY')</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)
             , to_timestamp('<xsl:value-of select="d_start" />', 'DD.MM.YYYY')
             , <xsl:if test="date_e != ''">to_timestamp('<xsl:value-of select="date_e" />', 'DD.MM.YYYY')</xsl:if><xsl:if test="date_e = ''">NULL</xsl:if>;
    </xsl:for-each>

    <xsl:for-each select="insAdvice">
      INSERT INTO insAdvices (duved, kolZl)
      SELECT to_timestamp('<xsl:value-of select="DUVED" />', 'DD.MM.YYYY')
             , <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 MRDS.T_INSURER i
    set Date_b = (select dBegin from insDates order by dBegin desc limit 1)
       ,Date_e = (select dEnd from insDates order by dBegin desc limit 1)
       ,NAME_E = (select nameE from insDates order by dBegin desc limit 1)
       ,NAL_P = (select nalP from insDates order by dBegin desc limit 1)
       ,Gk_start = (select dStart from insLicenses order by dStart desc limit 1)
       ,Gk_end = (select dEnd from insLicenses order by dStart desc limit 1)
       ,N_DOC = (select n_doc from insLicenses order by dStart desc limit 1)
       ,DUVED = (select duved from insAdvices order by duved desc limit 1)
       ,KOL_ZL = (select kolZl from insAdvices order by duved desc limit 1)
    where i.SmoCod = LEFT('<xsl:value-of select="smocod" />', 5);
  </xsl:template>

</xsl:stylesheet>
