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;
} |
Partager