<?php
require 'database.inc.php';
set_time_limit(600);
?>
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>

<head>
	<title>Title here!</title>
</head>

<body>
	<?php
	function Trouve_No_Client_Libre2()
	{
		return "999999";
		/*$requette = "SELECT * FROM config2 WHERE champ='NO_CLIENT'";
    if ($resultat = mysql_query($requette))
    {
      if($ligne = mysql_fetch_array( $resultat ))
      {
        $last=$ligne['valeur'];
        if($last>0)
        {
          $last++;
          $requette="UPDATE config2 SET valeur='$last' WHERE champ='NO_CLIENT'";
          Connection::executeRawQuery($requette);
          return $last;
        }
      }
    }
	return 0;*/
	}

	$file = fopen("Lignon.csv", "r");
	$line = fgets($file);
	$table_ch = "<table border='1'><tr><th>No Client</th><th>Prenom</th><th>Nom</th></tr>";
	$table_li = "<table border='1'><tr><th>No Client</th><th>Prenom</th><th>Nom</th></tr>";
	$table2_ch = "<table border='1'><tr><th>No Chassis</th></tr>";
	$table2_li = "<table border='1'><tr><th>No Chassis</th></tr>";
	while ($line = fgets($file)) {
		$arr = explode(";", $line);
		if (trim($arr[14]) != "" && trim($arr[23])) {
			$requette = "UPDATE vehicules SET Matr = '" . str_replace(".", "", $arr[23]) . "' WHERE NoChasis = '" . $arr[14] . "'";
			echo $requette . "<br>\n";
			Connection::executeRawQuery($requette);
		}
		if (trim($arr[4] . $arr[3]) != "" && false) {
			$requette = "SELECT * FROM clients WHERE Nom LIKE '" . Connection::getConnection()->quote($arr[4]) . "' AND Prenom LIKE '" . Connection::getConnection()->quote($arr[3]) . "'";
			$resultat = Connection::executeRawQueryWithfetchAll($requette);
			if (count($resultat) == 0) {
				$NoClient = Trouve_No_Client_Libre2();
				$request1 = "INSERT INTO clients (NoClient, Allocution, Prenom, Nom, DateNaissance, Adresse, NPA, Localite, TelProf, TelPriv, Natel) VALUES (";
				$request1 .= "$NoClient, ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[2]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[3]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[4]) . "', ";
				if ($arr[5] == "") {
					$request1 .= "'0000-00-00', ";
				} else {
					$request1 .= "'" . substr($arr[5], 6, 4) . "-" . substr($arr[5], 3, 2) . "-" . substr($arr[5], 0, 2) . "', ";
				}
				$request1 .= "'" . Connection::getConnection()->quote($arr[6]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[7]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[8]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[9]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[10]) . "', ";
				$request1 .= "'" . Connection::getConnection()->quote($arr[11]) . "')";
				echo $request1 . "<br/>\n";
				//mysql_query($request1);
			} else {
				$ligne = $resultat[0];
				$NoClient = $ligne["NoClient"];
			}
			echo $NoClient . "<br/>\n";
			$resultat = Vehicule::getVehiculeByNoChassis($arr[14]);
			if (!$resultat) {
				$request2 = "INSERT INTO vehicules (NoChasis, Client, NoMoteur, MarqueModel, Model, Couleur, NoPlaque, NoCle, Homolog, Matr, PrixVente, DateVente, DateCircul, Km) VALUES (";
				$request2 .= "'" . Connection::getConnection()->quote($arr[14]) . "', ";
				$request2 .= "$NoClient, ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[15]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[16]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[17]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[18]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote(str_replace(" ", "", $arr[19])) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[20]) . "', ";
				//$request2 .= "'".Connection::getConnection()->quote($arr[21])."', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[22]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[23]) . "', ";
				$request2 .= "'" . Connection::getConnection()->quote($arr[24]) . "', ";
				//$request2 .= "'".Connection::getConnection()->quote($arr[25])."', ";
				if ($arr[26] == "") {
					$request2 .= "'0000-00-00', ";
				} else {
					$request2 .= "'" . substr($arr[26], 6, 4) . "-" . substr($arr[26], 3, 2) . "-" . substr($arr[26], 0, 2) . "', ";
				}
				//$request2 .= "'".Connection::getConnection()->quote($arr[27])."', ";
				if ($arr[28] == "") {
					$request2 .= "'0000-00-00', ";
				} else {
					$request2 .= "'" . substr($arr[28], 6, 4) . "-" . substr($arr[28], 3, 2) . "-" . substr($arr[28], 0, 2) . "', ";
				}
				$request2 .= "'" . Connection::getConnection()->quote($arr[29]) . "')";
				echo $request2 . "<br/>\n";
				//mysql_query($request2);
			} else {
				echo "Existe deja...<br>\n";
			}
			//echo mysql_num_rows($resultat)." ".$arr[3]." ".$arr[4]."<br />\n";
			// while ($ligne = mysql_fetch_array($resultat))
			// {
			// $requette2 = "SELECT * FROM commande1 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat2 = mysql_query($requette2);
			// $requette3 = "SELECT * FROM commande2 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat3 = mysql_query($requette3);
			// $requette4 = "SELECT * FROM location1 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat4 = mysql_query($requette4);
			// $requette5 = "SELECT * FROM location2 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat5 = mysql_query($requette5);
			// $requette6 = "SELECT * FROM atelier1 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat6 = mysql_query($requette6);
			// $requette7 = "SELECT * FROM atelier2 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat7 = mysql_query($requette7);
			// $requette8 = "SELECT * FROM employes1 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat8 = mysql_query($requette8);
			// $requette9 = "SELECT * FROM employes2 WHERE NoClient = ".$ligne["NoClient"];
			// $resultat9 = mysql_query($requette9);
			// if (mysql_num_rows($resultat2)+mysql_num_rows($resultat4)+mysql_num_rows($resultat6)+mysql_num_rows($resultat8)!=0)
			// {
			// $table_ch .= "<tr><td>".$ligne["NoClient"]."</td><td>".$arr[3]."</td><td>".$arr[4]."</td></tr>";
			// echo "CH ".mysql_num_rows($resultat)." ".$ligne["NoClient"]." ".$arr[3]." ".$arr[4]."<br />\n";
			// }
			// if (mysql_num_rows($resultat3)+mysql_num_rows($resultat5)+mysql_num_rows($resultat7)+mysql_num_rows($resultat9)!=0)
			// {
			// $table_li .= "<tr><td>".$ligne["NoClient"]."</td><td>".$arr[3]."</td><td>".$arr[4]."</td></tr>";
			// echo "LI ".mysql_num_rows($resultat)." ".$ligne["NoClient"]." ".$arr[3]." ".$arr[4]."<br />\n";
			// }
			// if (mysql_num_rows($resultat2)+mysql_num_rows($resultat3)+mysql_num_rows($resultat4)+mysql_num_rows($resultat5)+mysql_num_rows($resultat6)+mysql_num_rows($resultat7)+mysql_num_rows($resultat8)+mysql_num_rows($resultat9)==0)
			// {
			// $requette_de = "DELETE FROM clients WHERE NoClient = ".$ligne["NoClient"];
			// mysql_query($requette_de);
			// echo $requette_de."<br/>\n";
			// echo "DE ".mysql_num_rows($resultat)." ".$ligne["NoClient"]." ".$arr[3]." ".$arr[4]."<br />\n";
			// }
			// }
			//$no_client = Trouve_No_Client_Libre2();
			//$request1 = "INSERT INTO clients (NoClient, Nom, Allocution, Prenom, DateNaissance, Adresse, NPA, Localite, Commantaire, TelPriv, TelProf, Natel) VALUES (";
			//$request1 .= "$no_client, ";
			//$request1 .= "'".$arr[1]."', ";
			// $request1 .= "'".$arr[2]."', ";
			// $request1 .= "'".$arr[3]."', ";
			// if ($arr[4]=="")
			// {
			// $request1 .= "'0000-00-00', ";
			// }
			// else
			// {
			// $request1 .= "'".substr($arr[4], 6, 4)."-".substr($arr[4], 3, 2)."-".substr($arr[4], 0, 2)."', ";
			// }
			// $request1 .= "'".$arr[6]."', ";
			// $request1 .= "'".$arr[7]."', ";
			// $request1 .= "'".$arr[8]."', ";
			// $request1 .= "'".$arr[13]."', ";
			// $request1 .= "'".$arr[10]."', ";
			// $request1 .= "'".$arr[11]."', ";
			// $request1 .= "'".$arr[12]."')";
			// echo $request1."<br/>\n";
			// mysql_query($request1);
			// if($arr[25]!="")
			// {
			// $request2 = "INSERT INTO vehicules (NoChasis, Client, NoPlaque, MarqueModel, Model, Couleur, NoCle, Matr, Cylindree, DateVente, DateCircul) VALUES (";
			// $request2 .= "'".$arr[25]."', ";
			// $request2 .= "$no_client, ";
			// $request2 .= "'".$arr[24]."', ";
			// $request2 .= "'".$arr[27]."', ";
			// $request2 .= "'".$arr[28]."', ";
			// $request2 .= "'".$arr[29]."', ";
			// $request2 .= "'".$arr[31]."', ";
			// $request2 .= "'".str_replace(".","",$arr[35])."', ";
			// $request2 .= "'".$arr[40]."', ";
			// if ($arr[45]=="")
			// {
			// $request2 .= "'0000-00-00', ";
			// }
			// else
			// {
			// $request2 .= "'".substr($arr[45], 6, 4)."-".substr($arr[45], 3, 2)."-".substr($arr[45], 0, 2)."', ";
			// }
			// if ($arr[47]=="")
			// {
			// $request2 .= "'0000-00-00')";
			// }
			// else
			// {
			// $request2 .= "'".substr($arr[47], 6, 4)."-".substr($arr[47], 3, 2)."-".substr($arr[47], 0, 2)."')";
			// }
			// echo $request2."<br/>\n";
			// mysql_query($request2);
			// }
		}
		// if (trim($arr[14])!="")
		// {
		// $requette2 = "SELECT * FROM commande1 WHERE Vehicule = '".$arr[14]."'";
		// $resultat2 = mysql_query($requette2);
		// $requette3 = "SELECT * FROM commande2 WHERE Vehicule = '".$arr[14]."'";
		// $resultat3 = mysql_query($requette3);
		// $requette4 = "SELECT * FROM atelier1 WHERE NoChasis = '".$arr[14]."'";
		// $resultat4 = mysql_query($requette4);
		// $requette5 = "SELECT * FROM atelier2 WHERE NoChasis = '".$arr[14]."'";
		// $resultat5 = mysql_query($requette5);
		// if (mysql_num_rows($resultat2)+mysql_num_rows($resultat4)!=0)
		// {
		// $table2_ch .= "<tr><td>".$arr[14]."</td></tr>";
		// echo "CH ".$arr[14]."<br />\n";
		// }
		// if (mysql_num_rows($resultat3)+mysql_num_rows($resultat5)!=0)
		// {
		// $table2_li .= "<tr><td>".$arr[14]."</td></tr>";
		// echo "LI ".$arr[14]."<br />\n";
		// }
		// if (mysql_num_rows($resultat2)+mysql_num_rows($resultat3)+mysql_num_rows($resultat4)+mysql_num_rows($resultat5)==0)
		// {
		// echo "DE ".$arr[14]."<br />\n";
		// $requette_de = "DELETE FROM vehicules WHERE NoChasis = '".$arr[14]."'";
		// mysql_query($requette_de);
		// echo $requette_de."<br/>\n";
		// }
		// }
	}

	//echo "CH CLIENT".$table_ch."</table>";
	//echo "LI CLIENT".$table_li."</table>";
	//echo "CH VHC".$table2_ch."</table>";
	//echo "LI VHC".$table2_li."</table>";

	?>
</body>

</html>