1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
| if(isset($_POST['submit'])) {
try {
$pdo_extraParams = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // rapport d'erreurs sous forme d'exceptions
PDO::ATTR_PERSISTENT => true, // Connexions persistantes
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8" // encodage UTF-8
);
// Instancie la connexion
if(!isset($_FILES['file'])) throw new RuntimeException('Formulaire erronné');
if($_FILES['file']['error'] !== 0) throw new RuntimeException("Erreur à la réception du fichier");
$pdo = new PDO(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx);
$pdo->beginTransaction();
$file = fopen($_FILES["file"]["tmp_name"], "r");
fgetcsv($file, 767, ";");
$stmt_select = $pdo->prepare('SELECT COUNT(*) FROM temporaire WHERE adresse=:adresse and code_postal=:code_postal and commune=:commune and latitude=:latitude and longitude=:longitude');
$stmt_insert = $pdo->prepare('INSERT INTO temporaire(competence_geographique,editeur,type_adresse,
adresse,code_postal,commune,latitude,longitude,id_precision,type_accessibilite,telephone,fax,email) VALUES (:competence_geographique,:editeur,:type_adresse,
:adresse,:code_postal,:commune,:latitude,:longitude,:id_precision,:type_accessibilite,:telephone,:fax,:email);');
$stmt_update = $pdo->prepare('UPDATE temporaire SET
competence_geographique:competence_geographique,
editeur:editeur,
type_adresse:type_adresse,
adresse:adresse,
code_postal:code_postal,
commune:commune,
latitude:latitude,
longitude:longitude,
id_precision:id_precision,
type_accessibilite:type_accessibilite,
telephone:telephone,
fax:fax,
email:email;');
foreach([$stmt_select, $stmt_update, $stmt_insert] as $k => $stmt) {
$stmt->bindParam(':id_service_public', $id_service_public);
$stmt->bindParam(':competence_geographique', $competence_geographique);
$stmt->bindParam(':editeur', $editeur);
$stmt->bindParam(':type_adresse', $type_adresse);
$stmt->bindParam(':adresse', $adresse);
$stmt->bindParam(':code_postal', $code_postal);
$stmt->bindParam(':commune', $commune);
$stmt->bindParam(':latitude', $latitude);
$stmt->bindParam(':longitude', $longitude);
$stmt->bindParam(':id_precision', $id_precision);
$stmt->bindParam(':type_accessibilite', $type_accessibilite);
$stmt->bindParam(':telephone', $telephone);
$stmt->bindParam(':fax', $fax);
$stmt->bindParam(':email', $email);
if($k == 0) continue;
elseif($k == 2)
$stmt->bindParam(':competence_geographique', $competence_geographique);
$stmt->bindParam(':editeur', $editeur);
$stmt->bindParam(':type_adresse', $type_adresse);
$stmt->bindParam(':adresse', $adresse);
$stmt->bindParam(':code_postal', $code_postal);
$stmt->bindParam(':commune', $commune);
$stmt->bindParam(':latitude', $latitude);
$stmt->bindParam(':longitude', $longitude);
$stmt->bindParam(':id_precision', $id_precision);
$stmt->bindParam(':type_accessibilite', $type_accessibilite);
$stmt->bindParam(':telephone', $telephone);
$stmt->bindParam(':fax', $fax);
$stmt->bindParam(':email', $email);
}
while (($getData = fgetcsv($file, 767, ";")) !== FALSE) {
echo '<p>ligne : ' . implode(';', $getData) . '</p>';
list($id_service_public, $competence_geographique,$editeur,$type_adresse,$adresse,$code_postal,$commune,$latitude,$longitude,$id_precision,$type_accessibilite,$telephone,$fax,$email) = $getData;
$stmt_select->execute();
if($stmt_select->fetchColumn())
$stmt_update->execute();
else
$stmt_insert->execute();
}
$pdo->commit();
fclose($file);
$msg = 'Succès';
}
catch(PDOException $ex) {
if(isset($pdo) and ($pdo instanceof PDO) and $pdo->inTransaction())
$pdo->rollBack();
error_log($ex->getMessage());
$msg = "Erreur dans la base de données";
}
catch(RuntimeException $ex) {
$msg = $ex->getMessage();
}
echo $msg;
} |
