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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
function importTac() {
//Affichage formulaire pour les import et export
$out ="<table><tr><th>Import Tac list</th></tr>";
$out.="<tr><td>";
$out.="<form name='uploadTac' method='post' action='#' enctype='multipart/form-data'>
File <input type='file' name='tacCSV'><input type='submit' value='upload'>
</form>";
$out.="</td></tr>";
$out.="<tr><th>Export tac list</th></tr>";
$out.="<tr><td><div class='validate'><a href='incs/tools/exportTac.php' >Export</a></div></td></tr>";
$out.="</table>";
//Si fichier spécifié dans le formulaire
//Si fichier de type CSV
if(file_exists($_FILES['tacCSV']['tmp_name']) && $_FILES['tacCSV']['type']=="text/comma-separated-values") {
//Copie du CSV dans table temporaire : taclist_tmp et sauvegarde du fichier dans docs/imports/tacs
//on vide la table dans un 1er tps
mysql_query("TRUNCATE taclist_tmp")or die(mysql_error());
move_uploaded_file($_FILES['tacCSV']['tmp_name'], 'docs/imports/tacs/'.date('Ymd').'.csv');
$sql="LOAD DATA LOCAL INFILE 'C:/wamp/www/REGENERSIS/docs/imports/tacs/".date('Ymd').".csv' INTO TABLE taclist_tmp FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' (`tac`,`manufacturer`,`model`,`grade`)";
mysql_query($sql) or die(mysql_error());
//Récupération nombre d'enregistrements
$q=mysql_query("SELECT COUNT(*) FROM taclist_tmp") or die(mysql_error());
$r=mysql_fetch_row($q);
$nbLine=($r[0]-1);
//INSERT des nouveaux tac
/*echo $sql = 'SELECT LPAD(taclist_tmp.tac, 8, 0) AS TAC, taclist_tmp.manufacturer AS MANUFACTURER, taclist_tmp.model AS MODEL, taclist_tmp.grade AS GRADE, taclist.tac AS "TAC from taclist"'
. ' FROM taclist_tmp'
. ' LEFT JOIN taclist ON taclist.tac=LPAD(taclist_tmp.tac, 8, 0)'
. ' WHERE taclist.tac IS NULL'
. ' ORDER BY taclist_tmp.tac ASC';*/
$sql = 'SELECT LPAD(taclist_tmp.tac, 8, 0) AS TAC, taclist_tmp.manufacturer AS MANUFACTURER, taclist_tmp.model AS MODEL, taclist_tmp.grade AS GRADE, taclist.tac AS "TAC from taclist"'
. ' FROM taclist_tmp '
. ' LEFT JOIN taclist ON taclist.tac = LPAD( taclist_tmp.tac , 8 , 0 ) '
. ' AND taclist.model = taclist_tmp.model '
. ' AND taclist.manufacturer = taclist_tmp.manufacturer '
. ' WHERE taclist.tac IS NULL LIMIT 0, 1000 ';
$q = mysql_query($sql) or die(mysql_error());
$actions = "<br /><u>Work :</u><br />";
//init des compteurs
$newTac=0;
$newManufacturer=0;
$newModel=0;
$updateGrade=0;
while($tac = mysql_fetch_array($q)){
$newTac++;
//Recherche si le manufacturer existe, sinon création du model
$sqlManufact='SELECT COUNT(*)'
. ' FROM manufacturers'
. ' WHERE name=UPPER("'.$tac['MANUFACTURER'].'")';
$qManufact=mysql_query($sqlManufact) or die(mysql_error());
$manufacturer=mysql_fetch_row($qManufact);
if($manufacturer[0]==0){
$newManufacturer++;
mysql_query('INSERT INTO manufacturers VALUES (NULL, UPPER("'.$tac['MANUFACTURER'].'"))') or die(mysql_error());
$actions.="<font color=blue>Add new manufacturer : <b>".$tac['MANUFACTURER']."</b></font><br />";
}
//Recherche si le model du manufacturer existe, sinon création du model
$sqlModel='SELECT COUNT(*)'
. ' FROM models'
. ' WHERE model=UPPER("'.$tac['MODEL'].'") AND manufacturer=UPPER("'.$tac['MANUFACTURER'].'")';
$qModel=mysql_query($sqlModel) or die(mysql_error());
$model=mysql_fetch_row($qModel);
if($model[0]==0){
$newModel++;
$sqlManufactId='SELECT manufacturerId FROM manufacturers WHERE name=UPPER("'.$tac['MANUFACTURER'].'")';
$qManufactId=mysql_query($sqlManufactId);
$manufacturerId=mysql_fetch_row($qManufactId);
$sqlGradeId='SELECT gradeId FROM grades WHERE grade=UPPER("'.$tac['GRADE'].'")';
$qGradeId=mysql_query($sqlGradeId);
$gradeId=mysql_fetch_row($qGradeId);
mysql_query('INSERT INTO models VALUES(NULL, UPPER("'.$tac['MODEL'].'"), "'.$manufacturerId[0].'", UPPER("'.$tac['MANUFACTURER'].'"), 1, '.$gradeId[0].', 0, 0, 0, 0)') or die(mysql_error());
$actions.="<font color=green>Add new model <b>".$tac['MODEL']."</b> of manufacturer <b>".$tac['MANUFACTURER']."</b></font><br />";
}
//Ajout du tac dans taclist et models2tac
mysql_query('INSERT INTO taclist (tac, manufacturer, model, grade, displayName) VALUES("'.$tac['TAC'].'", "'.$tac['MANUFACTURER'].'", "'.$tac['MODEL'].'", "'.$tac['GRADE'].'", "'.$tac['MANUFACTURER'].' - '.$tac['MODEL'].' - '.$tac['TAC'].'")') or die(mysql_error());
$qModelId=mysql_query('SELECT modelId FROM models WHERE manufacturer=UPPER("'.$tac['MANUFACTURER'].'") AND model=UPPER("'.$tac['MODEL'].'")');
$modelId=mysql_fetch_row($qModelId);
$qTacId=mysql_query('SELECT MAX(tacId) FROM taclist');
$tacId=mysql_fetch_row($qTacId);
mysql_query('INSERT INTO models2tac VALUES(NULL, '.$modelId[0].', '.($tacId[0]).')') or die(mysql_error());
$actions.="Add new tac : <b>".$tac['TAC']."</b> -> modelId n°".$modelId[0].", tacId n°".$tacId[0]."<br />";
}
//UPDATE des grades si besoin
$sql = 'SELECT taclist.tac, taclist.manufacturer, taclist.model, taclist_tmp.grade'
. ' FROM taclist_tmp'
. ' JOIN taclist ON taclist.tac = LPAD( taclist_tmp.tac , 8 , 0 ) AND taclist.model = taclist_tmp.model AND taclist.manufacturer = taclist_tmp.manufacturer AND taclist.grade != taclist_tmp.grade ORDER BY `taclist_tmp` . `tac`';
$q=mysql_query($sql);
while($data=mysql_fetch_array($q)){
$updateGrade++;
$actions.="<font color=grey><i>update TAC n°<b>".$data['tac']."</b>, manufacturer <b>".$data['manufacturer']."</b>, model <b>".$data['model']."</b>, grade <b>".$data['grade']."</b></i></font><br />";
$sqlUpdate='UPDATE taclist SET grade="'.$data['grade'].'" WHERE manufacturer="'.$data['manufacturer'].'" AND model="'.$data['model'].'" AND tac="'.$data['tac'].'"';
mysql_query($sqlUpdate) or die(mysql_error());
}
//Affichage du travail effectué
$out.= "<b>".$newTac." new tacs.</b><br />";
$out.= "<b>".$newManufacturer." new manufacturers.</b><br />";
$out.= "<b>".$newModel." new models.</b><br />";
$out.= "<b>".$updateGrade." update grades.</b><br />";
$out.= $actions;
}
return $out;
} |
