<p:config xmlns:p="http://www.orbeon.com/oxf/pipeline"
          xmlns:oxf="http://www.orbeon.com/oxf/processors"
          xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
          xmlns:sql="http://orbeon.org/oxf/xml/sql">
    <p:param name="instance" type="input"/>

    <p:processor name="oxf:xslt">
          <p:input name="data" href="#instance" />
          <p:input name="config">
            <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
             <xsl:template match="/">
              <sql:config>
                  <sql:connection>
                      <!-- <sql:datasource>Rachid</sql:datasource> si on veut faire le datasource dans le contexte au niveau du serveur -->
                      <!-- requete pour inserer les informations de la personne : nom, prenom .. -->
                      <sql:execute>
                          <sql:update>
                             insert into informations(nom,prenom,cin,cne,date_naissance,email,site_web,telephone,adresse,situation_familiale,fonction_actuelle) values(
                              '<xsl:value-of  select="/form/info-general/nom" />',
                              '<xsl:value-of select="/form/info-general/prenom" />',
                              '<xsl:value-of select="/form/info-general/cin" />',
                              '<xsl:value-of select="/form/info-general/cne" />',
                              '<xsl:value-of select="/form/info-general/date-naissance" />',
                              '<xsl:value-of select="/form/info-general/email" />',
                              '<xsl:value-of select="/form/info-general/site-web" />',
                              '<xsl:value-of select="/form/info-general/telephone" />',
                              '<xsl:value-of select="/form/info-general/adresse" />',
                              '<xsl:value-of select="/form/info-general/situation-familiale" />',
                              '<xsl:value-of select="/form/formations/fonction-actuelle" />'
                              )
                          </sql:update>
                      </sql:execute>
                      </sql:connection>
                      <sql:connection>
                      <!-- on insere les infos du compte: login et password en recuperant l'id de la personne -->
                      <sql:execute>
                          <sql:update>
                             insert into user(login,password,level,id_personne) values(
                              '<xsl:value-of select="/form/authentification/login" />',
                              '<xsl:value-of select="/form/authentification/password" />',
                              '<xsl:value-of select="/form/authentification/level" />',
                              (select id_informations from informations where cin='<xsl:value-of select="/form/info-general/cin" />'))
                          </sql:update>
                      </sql:execute>
                      
                      <!-- insertion des diplomes -->
                      
                      
                          <xsl:for-each select="/form/formations/diplomes/diplome[position()!=last()]">
                           <sql:execute>
                            <sql:update>
                               insert into diplomes(id_user,intitule,annee,mention) values(
                              (select id_informations from informations where cin='<xsl:value-of select="/form/info-general/cin" />'),
                                         '<xsl:value-of select="intitule" />',
                                         '<xsl:value-of select="annee" />',
                                          '<xsl:value-of select="mention" />'
                                           )
                              </sql:update>
                             </sql:execute>
                              </xsl:for-each>
                      
                  </sql:connection>
              </sql:config>
                 </xsl:template>
                 </xsl:stylesheet>
          </p:input>
        <p:output name="data" id="diplome-inserer"/>
         </p:processor>
    
    <!-- execution de la requete sql -->
                     <p:processor name="oxf:sql">
                         <p:input name="data"><rien/></p:input>
                         <p:input name="datasource" href="oxf:/FormationOrbeon/TestMigration/Datasource/datasource.xml" />
                         <p:input name="config" href="#diplome-inserer"/>
                      
                  </p:processor>
      
    </p:config>
