| |
<%M_Year = Request("T_Year")
Response.Write M_Year & "
"
'FilePath = Server.MapPath(".") & "\import\Excel_File.xls"
'Fields("File1").Value.SaveAs FilePath
M_idPays = Request.QueryString("idPays")
M_TitreCell = "B29:J2600"
M_Feuille = "Training"
Nb_ligne = 0
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;" &_
"DBQ=d:\maptracksolutions\clp\import\Excel_File.xls;" &_
"DefaultDir = d:\maptracksolutions\clp\import\"
Set RS=Server.CreateObject("ADODB.recordset")
RS.open "SELECT * from ["&M_feuille&"$"&M_TitreCell&"]", oConn
' Verification des erreur
response.write "Liste des village introuvable " & ("
")
Find_erreur = 0
List_non_trouve = ""
do until RS.EOF
Mid_village = 0
Mid_pays = 0
Mid_organisation = 0
M_nom_village= Trim(rs("VILLAGE/COMMUNITY"))
If M_nom_village <> "" Then
M_nom_village = Replace(M_nom_village, "'" , "''" )
Sql1 = "SELECT N4.idNiv4,P.ID_PAYS "
SQL1 = SQL1 & "FROM (((PAYS P INNER JOIN Niveau2 N2 ON (N2.idPays = P.ID_PAYS) ) "
SQL1 = SQL1 & "INNER JOIN Niveau3 N3 ON (N3.idNiveau2 = N2.idNiv2) ) "
SQL1 = SQL1 & "INNER JOIN Niveau4 N4 ON (N4.idNiveau3 = N3.idNiv3) ) "
SQL1 = SQL1 & "WHERE (UPPER(N4.Name) = '"& Ucase(M_nom_village) &"') "
set rs3 = conn.Execute(SQL1)
if rs3.eof Then
If InStr(List_non_trouve,rs("VILLAGE/COMMUNITY")) = 0 Then
response.write rs("VILLAGE/COMMUNITY") & ("
")
List_non_trouve = List_non_trouve + "/" & rs("VILLAGE/COMMUNITY")
End if
Find_erreur = 1
else
'response.write rs("VILLAGE/COMMUNITY") & ("
")
end if
rs3.close
Set rs3 = Nothing
If rs("FARMER_ORGANIZATION") <> "" Then
Sql1 = "SELECT Id FROM Entreprise WHERE Nom = '"& rs("FARMER_ORGANIZATION") &"'"
set rs3 = conn.Execute(SQL1)
if rs3.eof Then
If rs("FARMER_ORGANIZATION") <> "" Then
'response.write rs("FARMER_ORGANIZATION") & ("
")
End if
end if
rs3.close
Set rs3 = Nothing
End if
End if
RS.movenext
loop
Find_erreur = 1
If Find_erreur = 0 Then
sqlQuery1="INSERT INTO Formation(id_typeformation,DateFormation) "
sqlQuery1=sqlQuery1&"VALUES('"& "1" &"','2009-01-01'); "
conn.Execute(sqlQuery1)
set rs = conn.execute("select MAX(IdFormation) from Formation")
M_idFormation=rs(0)
RS.Close
RS.open "SELECT * from ["&M_feuille&"$"&M_TitreCell&"]", oConn
do until RS.EOF
Mid_village = 0
Mid_pays = 0
Mid_organisation = 0
M_nom_village= rs("VILLAGE/COMMUNITY")
If M_nom_village <> "" Then
M_nom_village = Replace(M_nom_village, "'" , "''" )
Sql1 = "SELECT N4.idNiv4,P.ID_PAYS "
SQL1 = SQL1 & "FROM (((PAYS P INNER JOIN Niveau2 N2 ON (N2.idPays = P.ID_PAYS) ) "
SQL1 = SQL1 & "INNER JOIN Niveau3 N3 ON (N3.idNiveau2 = N2.idNiv2) ) "
SQL1 = SQL1 & "INNER JOIN Niveau4 N4 ON (N4.idNiveau3 = N3.idNiv3) ) "
SQL1 = SQL1 & "WHERE (UPPER(N4.Name) = '"& Ucase(M_nom_village) &"') "
set rs3 = conn.Execute(SQL1)
if not rs3.eof Then
Mid_village = rs3(0)
Mid_pays = rs3(1)
end if
rs3.close
Set rs3 = Nothing
If Mid_village <> 0 Then
If rs("FARMER_ORGANIZATION") <> "" Then
M_organisation = Replace(rs("FARMER_ORGANIZATION"),"'"," ")
else
M_organisation = ""
End if
Sql1 = "SELECT Id FROM Entreprise WHERE Nom = '"& M_organisation &"'"
set rs3 = conn.Execute(SQL1)
if not rs3.eof Then
Mid_organisation = rs3(0)
end if
rs3.close
Set rs3 = Nothing
If rs("CLP_ID") <> "" Then
M_clp_id = rs("CLP_ID")
else
M_clp_id = "X"
End if
if rs("FARMER_NAME") <> "" Then
M_nom = rs("FARMER_NAME")
M_nom = Replace(M_nom , "'" , "''" )
else
M_nom = "X"
End if
M_sexe = rs("GENDER")
If M_sexe = "0" Then
M_sexe = "M"
End if
If M_sexe = "1" Then
M_sexe = "F"
End if
M_mobile = LEFT(rs("MOBILE"),11)
M_next_id = 0
M_new_id = ""
M_id_producteur = 0
Sql1 = "SELECT Idproducteur FROM Producteur WHERE CodeProd = '"& M_clp_id &"'"
set rs1 = conn.Execute(SQL1)
if rs1.eof Then
M_id_producteur = 0
else
M_id_producteur = rs1(0)
End if
rs1.close
Set rs1 = Nothing
If M_id_producteur = 0 Then
Sql2 = "SELECT Idproducteur FROM Producteur WHERE Nom = '"& M_nom &"'"
set rs2 = conn.Execute(SQL2)
if rs2.eof Then
M_id_producteur = 0
else
M_id_producteur = rs2(0)
End if
rs2.close
Set rs2 = Nothing
End if
If M_id_producteur = 0 Then
Sql1 = "SELECT Next_id,Code_pays FROM Pays WHERE Id_pays = "& Mid_pays
set rs3 = conn.Execute(SQL1)
M_next_id = rs3(0)
M_next_id = M_next_id + 1
M_new_id = rs3(1) & Right("0000000"&M_next_id,7)
rs3.close
Set rs3 = Nothing
sqlQuery="UPDATE Pays SET Next_id="& M_next_id
sqlQuery=sqlQuery&" WHERE (Id_pays= "& Mid_pays &") "
conn.Execute(sqlQuery)
Nb_ligne = Nb_ligne + 1
sqlQuery1="INSERT INTO Producteur(CodeProd,Nom,Sexe,DateNaissance,Tel,IdNiveau4,Identreprise) "
sqlQuery1=sqlQuery1&"VALUES('"& M_new_id &"','"& M_nom&"','"&M_sexe&"','"&rs("DATE_OF_BIRTH")&"','"& M_mobile & "',"& Mid_village&","&Mid_organisation&"); "
conn.Execute(sqlQuery1)
set rs3 = conn.execute("select MAX(IdProducteur) from Producteur")
M_id_producteur =rs3(0)
Set rs3 = Nothing
End if
sqlQuery1="INSERT INTO Participant(idFormation,id_producteur) "
sqlQuery1=sqlQuery1&"VALUES("& M_idFormation &","& M_id_producteur&"); "
conn.Execute(sqlQuery1)
End if
End if
RS.movenext
Loop
End if
RS.Close
oConn.Close
Set RS = Nothing%>
|
|
|
|
