<?php

include("include/data.php");
Init_boutique();

// entete de la page

include 'menu/header.php';
?>
<html>

<script type="text/javascript">
	document.addEventListener('unload', function() {
		console.log('La page est en cours de chargement.....');
	});

	window.addEventListener('load', function() {
		console.log('La page est finie de charger');
	});
	showModal();
</script>


</html>
<?php
/* http://192.168.1.200/horius/import_bl.php
ordre=1
date_bl=2018-07-25
date_bl_livr=2018-07-26
ok=Valider

*/
sleep(3);

if ($_GET['id'] == 1){
	die ('OK');
} else {
	header('500 Internal Server Error', true, 500);
	Die('NoOk');
}

unset($arr);
if (!isset($_GET['ordre'])) {
	// Transmis en POST
	$num_ordre = $_POST['ordre'];
	if (isset($_POST['date_bl'])) {
		$date_bl = $_POST['date_bl'];
	} else {
		$date_bl = '';
	}
	if (isset($_POST['date_bl_livr'])) {
		$date_bl_livr = $_POST['date_bl_livr'];
	} else {
		$date_bl_livr = '';
	}
	if (isset($_POST['ok'])) {
		$ok = $_POST['ok'];
	} else {
		$ok = '';
	}
	$valForm = "POST";
} else {
	// Transmis en GET
	$num_ordre = $_GET['ordre'];
	if (isset($_GET['date_bl'])) {
		$date_bl = $_GET['date_bl'];
	} else {
		$date_bl = '';
	}
	if (isset($_GET['date_bl_livr'])) {
		$date_bl_livr = $_GET['date_bl_livr'];
	} else {
		$date_bl_livr = '';
	}
	if (isset($_GET['ok'])) {
		$ok = $_GET['ok'];
	} else {
		$ok = '';
	}

	$arr = array();
	$i = 0;
	foreach ($_GET['CltSelBL'] as $valeur) {
		$i = $i + 1;
		//echo $valeur.' a &eacute;t&eacute; s&eacute;lectionn&eacute;<br>';
		$arr[$i] = $valeur;
	}
	$valForm = "GET";
}
$b = 0;
$date_bl_sql_base = $date_bl;
$date_livr_sql_base = $date_bl_livr;

$date_bl = date("d/m/Y", strtotime($date_bl));
$date_bl_sql = $date_bl_livr;
$date_bl_livr = date("d/m/Y", strtotime($date_bl_livr));


// TITRE Page //
echo "<HR size='2' width='500'><div class='titre-text'><center>Cr&#233;ation Bon de livraison apr&#232;s imports</div></center><HR size='2' width='500'>";
echo "<div class='t-text12'><center>Date BL : $date_bl - Date Livraison : $date_bl_livr <HR size='2' width='500'";
echo "<br><br>";
//echo '<script type="text/javascript">' . 'alert("Erreur : '.$erreur.'");' . '</script>';

?>

<html>
<div id="fond"></div>
<a href="#" id="show">Afficher la fenetre modale</a>
<script src="js/modal.js" type="text/javascript"></script>
<div id="modal" class="popup"></div>
</html>


<?php


if ($ok == "Valider") {     // Importation Partielle

	$Nb_ValTbl = sizeof($arr);

	for ($f = 1; $f <= $Nb_ValTbl; $f++) {
		$SelClt_a = $arr[$f];
		$NumClient_final_Deb = substr($SelClt_a, 0, 2);
		$NumClient_final_Fin = substr($SelClt_a, -4, -1);
		$SelClt_a = $NumClient_final_Deb . " " . $NumClient_final_Fin;

		if ($f == 1) {
			$SelCltCltTotal .= " AND `num_client`='" . $SelClt_a . "'";
		} else {
			$SelCltCltTotal .= " OR `num_client`='" . $SelClt_a . "'";
		}
	}

	$Nb_etiq_import_Total = 0;
	$sql1 = "SELECT count(num_etiq_ligne) FROM temp_import_csv";
	$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
	$sql1 .= $SelCltCltTotal;
	$sql1 .= " AND `RsltImport`='0'";
	$sql1 .= " AND `RsltLot`='0'";
	$sql1 .= " AND `RsltStock`='0'";
	$req1 = mysqli_query($link, $sql1);
	$row = mysqli_fetch_row($req1);
	$Nb_etiq_import_Total = $row[0];


	$Nb_clients_import_Total = 0;
	$sql1 = "SELECT COUNT(DISTINCT (num_client))  FROM temp_import_csv";
	$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
	$sql1 .= $SelCltCltTotal;
	$sql1 .= " AND `RsltImport`='0'";
	$sql1 .= " AND `RsltLot`='0'";
	$sql1 .= " AND `RsltStock`='0'";
	$req1 = mysqli_query($link, $sql1);
	$row = mysqli_fetch_row($req1);
	$Nb_clients_import_Total = $row[0];

	echo "<center>Importation de  $Nb_etiq_import_Total &#233;tiquette(s) pour $Nb_clients_import_Total client(s) s&eacute;l&eacute;ctionn&eacute;(s)</div><HR size='2' width='500'><br>";
	/*
echo "$SelCltCltTotal <br>";
*/

	for ($tb = 0; $tb <= $Nb_ValTbl; $tb++) {

		$SelClt = $arr[$tb];

		$NumClient_final_Deb = substr($SelClt, 0, 2);
		$NumClient_final_Fin = substr($SelClt, -4, -1);
		$SelClt = $NumClient_final_Deb . " " . $NumClient_final_Fin;
		//
		//echo $SelClt.' | '.$arr[$tb].' a &eacute;t&eacute; s&eacute;lectionn&eacute; '.$Nb_ValTbl.'<br>';
		//

		// Creation BL Haut et detail temps


		$Nb_clients_import = 0;
		$sql1 = "SELECT DISTINCT num_client FROM temp_import_csv";

		$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
		$sql1 .= " AND `num_client`='" . $SelClt . "'";
		$sql1 .= " AND `RsltImport`='0'";
		$sql1 .= " AND `RsltLot`='0'";
		$sql1 .= " AND `RsltStock`='0'";
		$req1 = mysqli_query($link, $sql1);
		while ($row = mysqli_fetch_assoc($req1)) {
			$NumClient = $row["num_client"];

			$sql2 = "SELECT `Code_trsp`,`Code_paiement` FROM `clients_base` WHERE `Num_Client`='" . $NumClient . "'";

			$sql2 .= " AND IdNumClient='" . $IdNumClient . "'";
			$req2 = mysqli_query($link, $sql2);
			$row = mysqli_fetch_row($req2);
			$CodePaiement = $row[0];
			$CodeTrsp = $row[1];

			$sql21 = "SELECT Code_Trsp FROM `categorie_trsp` WHERE `Num_Ligne_Cate_Trsp`='" . $CodeTrsp . "'";

			$sql21 .= " AND IdNumClient='" . $IdNumClient . "'";
			$req21 = mysqli_query($link, $sql21);
			$row = mysqli_fetch_row($req21);
			$Transport = $row[0];

			$sql22 = "SELECT `Code_Paiement` FROM `categorie_paiement` WHERE `Num_Ligne_Cate_Paiement`='" . $CodePaiement . "'";

			$sql22 .= " AND IdNumClient='" . $IdNumClient . "'";
			$req22 = mysqli_query($link, $sql22);
			$row = mysqli_fetch_row($req22);
			$Paiement = $row[0];

			$sql23 = "SELECT Nom_Client FROM clients_base WHERE Num_Client='$NumClient'";

			$req23 = mysqli_query($link, $sql23);
			while ($row = mysqli_fetch_assoc($req23)) {
				$Nom_Client = $row["Nom_Client"];
			}

			$semaine_etq = date("W", strtotime($date_bl_sql));

			echo "<center><table class='t-text' width='750' border='1' cellspacing='2' cellpadding='2'>";
			echo "<tr><th>Num&#233;ro de Client :</th><td>$NumClient | $Nom_Client</td><th>Semaine :</th><td>$date_bl_sql | $semaine_etq</td></tr>";
			echo "<tr><th>Code Trsp :</th><td>$CodeTrsp | $Transport</td><th>Code Payement :</th><td>$CodePaiement | $Paiement</td></tr>";

			$observations = "";
			$observations = htmlspecialchars($observations, ENT_QUOTES);


			$last_num_entre = 0;
			$sql2 = "SELECT max(num_li_bl) FROM gestion_bl_haut";
			$req2 = mysqli_query($link, $sql2);
			$row = mysqli_fetch_row($req2);
			$last_num_entre_0 = $row[0];
			$last_num_entre = $last_num_entre_0 + 1;


			$jourBl = array('01' => 31, '02' => 28, '03' => 31, '04' => 30, '05' => 31, '06' => 30, '07' => 31, '08' => 31, '09' => 30, '10' => 31, '11' => 30, '12' => 31);
			$moisbl = date("m", strtotime($date_bl_sql));
			$SelAnne = date("Y", strtotime($date_bl_sql));
			$semaine_bl = date("W", strtotime($date_bl_sql));
			$jourfin = $jourBl[$moisbl];

			$SelDeb = date("Y-m-d", mktime(0, 0, 0, $moisbl, 01, $SelAnne));
			$SelFin = date("Y-m-d", mktime(0, 0, 0, $moisbl, $jourBl[$moisbl], $SelAnne));

			// chercher le dernier num bl par mois
			$last_bl = 0;
			$sql3 = "SELECT * FROM gestion_bl_haut ";
			$sql3 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
			//$sql3.=" AND `num_li_bl`='".$last_num_entre_0."'";
			$sql3 .= " AND date_bl BETWEEN '" . $SelDeb . "' AND '" . $SelFin . "'";
			$req3 = mysqli_query($link, $sql3);
			while ($row = mysqli_fetch_assoc($req3)) {
				$num_li_bl = $row["num_li_bl"];
				$num_bl_base = $row["num_bl"];
				//
				//echo "$num_li_bl | $num_bl_base <br>" ;
			}
			//echo "$moisbl | $SelAnne | Jours fin : ".$jourBl[$moisbl]." | $jourfin |<br>" ;


			$nbcar = strlen($num_bl_base);
			$nbarrestant = $nbcar - 3;
			$numblfin = substr($num_bl_base, -$nbarrestant);
			$numblfin = $numblfin + 1;
			$numblfin = $moisbl . "-" . $numblfin;
			/* Test ecriture
echo $sql3."<br>";
echo "$num_li_bl | $num_bl_base |nb car : $nbcar|$nbarrestant| numero bl : $numblfin <br>" ;
echo "trsp : $CodeTrsp | Paie : $CodePaiement | obs : $observations <br>";
*/

			$sql_ajout_bl_haut = "INSERT INTO gestion_bl_haut VALUES (
'" . $last_num_entre . "',
'" . $IdNumClient . "',
'" . $numblfin . "',
'" . $date_bl_sql_base . "',
'" . $semaine_bl . "',
'" . $NumClient . "',
'" . $date_livr_sql_base . "',
'0',
'0',
'0',
'" . $CodePaiement . "',
'" . $CodeTrsp . "',
'0',
'0',
'" . $observations . "',
'" . $timestamp . "')";
			//
			$Req_ajout_bl_haut = mysqli_query($link, $sql_ajout_bl_haut);
			//
			$b = $b + 1;

			/*
echo "$sql_ajout_bl_haut | $Req_ajout_bl_haut<br>";
*/
			switch ($Req_ajout_bl_haut) {
				case 0:
					echo "<tr><th>Num&#233;ro Bl :</th><th>$numblfin <img src='images/Erase.png'></th><th></th><th></th></tr>";
					echo "</table></center>";

					break;
				case 1:
					echo "<tr><th>Num&#233;ro Bl :</th><th>$numblfin <img src='images/Apply.png'></th><th></th><th></th></tr>";
					echo "</table></center>";

					break;
			}

			echo "<center><table class='t-text' width='750' border='1' cellspacing='2' cellpadding='2'>";
			$Nb_detail_import = 0;
			$sql31 = "SELECT * FROM temp_import_csv";
			$sql31 .= " WHERE `num_client`='" . $NumClient . "'";
			$sql31 .= " AND `IdNumClient`='" . $IdNumClient . "'";
			$sql31 .= " AND RsltImport ='0'";
			$sql31 .= " AND RsltLot='0'";
			$sql31 .= " AND RsltStock='0'";
			$req31 = mysqli_query($link, $sql31);
			while ($row = mysqli_fetch_assoc($req31)) {
				$num_plu = $row["num_plu"];
				$num_eleveur = $row["Num_Eleveur_Base"];
				$num_lot_entre = $row["num_lot"];
				$num_trace_entre = $row["num_lot_trace"];
				$num_plu_saisie = $row["num_plu"];
				$puht = $row["puht"];
				$poids_saisie = $row["poids"];
				$num_ligne_bltemp = $row["num_ligne_import"];

				$last_num_entre_bl = 0;
				$sql = "SELECT max(num_ligne) FROM temp_detail_bl";
				$req = mysqli_query($link, $sql);
				$row = mysqli_fetch_row($req);
				$last_num_entre_bl = $row[0];
				$last_num_entre_bl = $last_num_entre_bl + 1;

				$sql32 = "select * FROM plu";
				$sql32 .= " WHERE Num_PLU='" . $num_plu_saisie . "'";
				$sql32 .= " AND IdNumClient='" . $IdNumClient . "'";
				$req32 = mysqli_query($link, $sql32);
				while ($row = mysqli_fetch_assoc($req32)) {
					$nom_plu_temp = $row["PLU"];
				}

				$num_lot_entre = trim($num_lot_entre);
				$num_trace_entre = trim($num_trace_entre);
				$montant = $poids_saisie * $puht;
				$montant = number_format($montant, 2, '.', '');

				$sql41 = "INSERT INTO temp_detail_bl VALUES (
			'" . $last_num_entre_bl . "',
			'" . $IdNumClient . "',
			'" . $last_num_entre . "',
			'" . $numblfin . "',
			'" . $num_eleveur . "',
			'" . $num_lot_entre . "',
			'" . $num_trace_entre . "',
			'" . $num_plu_saisie . "',
			'" . $puht . "',
			'" . $poids_saisie . "',
			'1',
			'" . $montant . "')";
				$res41 = mysqli_query($link, $sql41);
				// echo "requet : ".$sql41." | $res41 <br>";

				if ($res41 == 1) {
					$sql51 = "UPDATE temp_import_csv SET
						`RsltImport` = '1'
						WHERE `num_ligne_import` = '$num_ligne_bltemp'";
					$res51 = mysqli_query($link, $sql51);
					//	echo "requet : ".$sql51." | Update $res51 <br>";
				} else {
					$res51 = 0;
				}
				switch ($res51) {
					case 0:
						echo "<tr><th>PLU :</th><td>$last_num_entre_bl | $num_plu_saisie | $nom_plu_temp</td><th>$num_lot_entre-$num_trace_entre</th><th>Poids :</th><th>$poids_saisie Kg | $montant &#172;</th><th><img src='images/Erase.png'></th></tr>";
						break;
					case 1:
						echo "<tr><th>PLU :</th><td>$last_num_entre_bl | $num_plu_saisie | $nom_plu_temp</td><th>$num_lot_entre-$num_trace_entre</th><th>Poids :</th><th>$poids_saisie Kg | $montant &#172;</th><th><img src='images/Apply.png'></th></tr>";
						break;
				}
			}
			echo "<tr><th></th></tr>";
			echo "</table></center>";
		}

		// Import Stock
		echo "<h1> | Tra&ccedil;abilit&eacute;e => Import Stock | Import Lots</h1><br>";
		echo "<center><table class='t-text' width='900' border='1' cellspacing='2' cellpadding='2'>";
		$a = 0;

		$sql61 = "SELECT `Num_Eleveur_Base`, `date_etiq`,`dlc_etiq`, `Num_semaine`, `num_lot`, `num_lot_trace`, num_plu, SUM(poids), COUNT(`num_plu`)";
		$sql61 .= " FROM temp_import_csv";
		$sql61 .= " WHERE IdNumClient='" . $IdNumClient . "'";
		$sql61 .= " AND `RsltLot`='0'";
		$sql61 .= " AND `RsltStock`='0'";
		$sql61 .= " AND `num_client`='" . $SelClt . "'";
		$sql61 .= " GROUP BY `Num_Eleveur_Base`, `date_etiq`, `dlc_etiq`, `Num_semaine`, `num_lot`, `num_lot_trace`, num_plu ASC";
		$req61 = mysqli_query($link, $sql61);
		//echo "<br>$sql61<br>";
		while ($row = mysqli_fetch_assoc($req61)) {
			$num_plu_temp = $row["num_plu"];
			$poids_temp = $row["SUM(poids)"];
			$nb_pi_temp = $row["COUNT(`num_plu`)"];
			$semaine_saisie =  $row["Num_semaine"];
			$num_client_entre =  $row["Num_Eleveur_Base"];
			$num_lot_entre =  $row["num_lot"];
			$num_trace_entre =  $row["num_lot_trace"];
			$date_lot_entre  =  $row["date_etiq"];
			$date_dlc_temp	=  $row["dlc_etiq"];

			$sql661 = "select * FROM plu";
			$sql661 .= " WHERE Num_PLU='" . $num_plu_temp . "'";
			$sql661 .= " AND IdNumClient='" . $IdNumClient . "'";
			$req661 = mysqli_query($link, $sql661);
			while ($row = mysqli_fetch_assoc($req661)) {
				$nom_plu_temp = $row["PLU"];
			}


			$a++;
			$poids_temp = number_format($poids_temp, 3, '.', ' ');
			$nb_pi_temp = number_format($nb_pi_temp, 0, '.', ' ');
			$date = date("Y-m-d");
			//
			$last_num_entre_stock = 0;
			$sql2 = "SELECT max(IdNumStock) FROM stock";
			$req2 = mysqli_query($link, $sql2);
			$row = mysqli_fetch_row($req2);
			$last_num_entre_stock = $row[0];
			$last_num_entre_stock = $last_num_entre_stock + 1;

			$sql_ajout_stock = "INSERT INTO stock VALUES (
'" . $last_num_entre_stock . "',
'" . $IdNumClient . "',
'" . $date . "',
'" . $semaine_saisie . "',
'" . $num_client_entre . "',
'" . $num_lot_entre . "',
'" . $num_trace_entre . "',
'" . $num_plu_temp . "',
'" . $date_lot_entre . "',
'" . $date_dlc_temp . "',
'" . $poids_temp . "',
'" . $nb_pi_temp . "',
'0',
'1',
'',
'" . $timestamp . "')";
			//
			$Req_ajout_stock = mysqli_query($link, $sql_ajout_stock);
			//
			if ($Req_ajout_stock == 1) {
				$sql61 = "UPDATE temp_import_csv SET
						`RsltStock` = '1'
						WHERE `num_plu`='" . $num_plu_temp . "' AND `num_lot`='" . $num_lot_entre . "' AND `num_client`='" . $SelClt . "' AND `num_lot_trace`='" . $num_trace_entre . "'";
				$res61 = mysqli_query($link, $sql61);
				/*
						echo "requet : ".$sql61." | Update $res61 <br>";
						*/
			} else {
				$res61 = 0;
			}


			switch ($res61) {
				case 0:
					echo "<tr><th>Entre Stock $last_num_entre_stock</th><th>$semaine_saisie | $date</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre | $date_dlc_temp</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Erase.png'></th></tr>";
					break;
				case 1:
					echo "<tr><th>Entre Stock $last_num_entre_stock</th><th>$semaine_saisie | $date</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre | $date_dlc_temp</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Apply.png'></th></tr>";
					break;
			}


			// Ajout table lot en position sortie
			$last_num_entre_lot = 0;
			$sql2 = "SELECT max(num_ligne_lot) FROM lot";
			$req2 = mysqli_query($link, $sql2);
			$row = mysqli_fetch_row($req2);
			$last_num_entre_lot = $row[0];
			$last_num_entre_lot = $last_num_entre_lot + 1;

			$nom_plu_temp = htmlspecialchars($nom_plu_temp, ENT_QUOTES);

			$sql_ajout_lot_sortie = "INSERT INTO lot VALUES (
'" . $last_num_entre_lot . "',
'" . $IdNumClient . "',
'" . $num_client_entre . "',
'" . $date_lot_entre . "',
'" . $num_lot_entre . "',
'" . $num_trace_entre . "',
'6',
'" . $poids_temp . "',
'1',
'0',
'" . $last_num_entre_stock . "',
'" . $nom_plu_temp . " | Nb Pi: " . $nb_pi_temp . "')";
			//
			$Req_ajout_lot = mysqli_query($link, $sql_ajout_lot_sortie);
			/*
echo "$a | $sql_ajout_lot_sortie | $Req_ajout_lot<br><br>";
echo "$a | $sql_ajout_stock | $Req_ajout_stock <br>";
*/

			if ($Req_ajout_lot == 1) {
				$sql71 = "UPDATE temp_import_csv SET
						`RsltLot` = '1'
						WHERE `num_plu`='" . $num_plu_temp . "' AND `num_lot`='" . $num_lot_entre . "' AND `num_client`='" . $SelClt . "' AND `num_lot_trace`='" . $num_trace_entre . "'";
				$res71 = mysqli_query($link, $sql71);
				/*
						echo "requet : ".$sql71." | Update $res71 <br>";
						*/
			} else {
				$res71 = 0;
			}


			switch ($res71) {
				case 0:
					echo "<tr><th>Entre Lot $last_num_entre_lot</th><th>$date_lot_entre</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Erase.png'></th></tr>";
					break;
				case 1:
					echo "<tr><th>Entre Lot $last_num_entre_lot</th><th>$date_lot_entre</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Apply.png'></th></tr>";
					break;
			}
		}
		echo "</table></center>";

		if ($res51 and $res61 and $res71) {
			echo "<h1>La cr&eacute;ation c'est bien d&eacute;roul&eacute;e !</h1><br>";
		} else {
			echo "<h1>Pas de cr&#233;ation de bl et pas de creation de Tra&#231;abilit&#233;e</h1><br>";
		}

		echo "<form id='form1' name='form1' method='GET' action='edition_recap_etiq.php' target='_blank'>";
		echo "<input type='hidden' name='ordre' value='$num_ordre'/>";
		echo "<input type='hidden' name='date_bl' value='$date_bl_sql_base'/>";
		echo "<input type='hidden' name='date_bl_livr' value='$date_livr_sql_base'/>";
		echo "<input type='submit' value='* edition R&#233;cap ? *'>
      </div></td>";
		echo "<tr>";
		echo "</table></form><br>";
	}
	//
} else { // Importation Totale
	//


	$Nb_etiq_import = 0;
	$sql1 = "SELECT count(num_etiq_ligne) FROM temp_import_csv";
	$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
	$sql1 .= " AND `RsltImport`='0'";
	$sql1 .= " AND `RsltLot`='0'";
	$sql1 .= " AND `RsltStock`='0'";
	$req1 = mysqli_query($link, $sql1);
	$row = mysqli_fetch_row($req1);
	$Nb_etiq_import = $row[0];

	$Nb_clients_import = 0;
	$sql1 = "SELECT COUNT(DISTINCT num_client) FROM temp_import_csv";
	$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
	$sql1 .= " AND `RsltImport`='0'";
	$sql1 .= " AND `RsltLot`='0'";
	$sql1 .= " AND `RsltStock`='0'";
	$req1 = mysqli_query($link, $sql1);
	$row = mysqli_fetch_row($req1);
	$Nb_clients_import = $row[0];

	echo "<center>Importation de toutes les $Nb_etiq_import &#233;tiquette(s) pour $Nb_clients_import client(s)</div><HR size='2' width='500'><br>";


	// Creation BL Haut et detail temps


	$Nb_clients_import = 0;
	$sql1 = "SELECT DISTINCT num_client FROM temp_import_csv";

	$sql1 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
	$sql1 .= " AND `RsltImport`='0'";
	$sql1 .= " AND `RsltLot`='0'";
	$sql1 .= " AND `RsltStock`='0'";
	$req1 = mysqli_query($link, $sql1);
	while ($row = mysqli_fetch_assoc($req1)) {
		$NumClient = $row["num_client"];

		$sql2 = "SELECT `Code_trsp`,`Code_paiement` FROM `clients_base` WHERE `Num_Client`='" . $NumClient . "'";

		$sql2 .= " AND IdNumClient='" . $IdNumClient . "'";
		$req2 = mysqli_query($link, $sql2);
		$row = mysqli_fetch_row($req2);
		$CodePaiement = $row[0];
		$CodeTrsp = $row[1];

		$sql21 = "SELECT Code_Trsp FROM `categorie_trsp` WHERE `Num_Ligne_Cate_Trsp`='" . $CodeTrsp . "'";

		$sql21 .= " AND IdNumClient='" . $IdNumClient . "'";
		$req21 = mysqli_query($link, $sql21);
		$row = mysqli_fetch_row($req21);
		$Transport = $row[0];

		$sql22 = "SELECT `Code_Paiement` FROM `categorie_paiement` WHERE `Num_Ligne_Cate_Paiement`='" . $CodePaiement . "'";

		$sql22 .= " AND IdNumClient='" . $IdNumClient . "'";
		$req22 = mysqli_query($link, $sql22);
		$row = mysqli_fetch_row($req22);
		$Paiement = $row[0];

		$sql23 = "SELECT Nom_Client FROM clients_base WHERE Num_Client='$NumClient'";

		$req23 = mysqli_query($link, $sql23);
		while ($row = mysqli_fetch_assoc($req23)) {
			$Nom_Client = $row["Nom_Client"];
		}

		$semaine_etq = date("W", strtotime($date_bl_sql));

		echo "<center><table class='t-text' width='750' border='1' cellspacing='2' cellpadding='2'>";
		echo "<tr><th>Num&#233;ro de Client :</th><td>$NumClient | $Nom_Client</td><th>Semaine :</th><td>$date_bl_sql | $semaine_etq</td></tr>";
		echo "<tr><th>Code Trsp :</th><td>$CodeTrsp | $Transport</td><th>Code Payement :</th><td>$CodePaiement | $Paiement</td></tr>";

		$observations = "";
		$observations = htmlspecialchars($observations, ENT_QUOTES);


		$last_num_entre = 0;
		$sql2 = "SELECT max(num_li_bl) FROM gestion_bl_haut";
		$req2 = mysqli_query($link, $sql2);
		$row = mysqli_fetch_row($req2);
		$last_num_entre_0 = $row[0];
		$last_num_entre = $last_num_entre_0 + 1;


		$jourBl = array('01' => 31, '02' => 28, '03' => 31, '04' => 30, '05' => 31, '06' => 30, '07' => 31, '08' => 31, '09' => 30, '10' => 31, '11' => 30, '12' => 31);
		$moisbl = date("m", strtotime($date_bl_sql));
		$SelAnne = date("Y", strtotime($date_bl_sql));
		$semaine_bl = date("W", strtotime($date_bl_sql));
		$jourfin = $jourBl[$moisbl];

		$SelDeb = date("Y-m-d", mktime(0, 0, 0, $moisbl, 01, $SelAnne));
		$SelFin = date("Y-m-d", mktime(0, 0, 0, $moisbl, $jourBl[$moisbl], $SelAnne));

		// chercher le dernier num bl par mois
		$last_bl = 0;
		$sql3 = "SELECT * FROM gestion_bl_haut ";
		$sql3 .= " WHERE `IdNumClient`='" . $IdNumClient . "'";
		//$sql3.=" AND `num_li_bl`='".$last_num_entre_0."'";
		$sql3 .= " AND date_bl BETWEEN '" . $SelDeb . "' AND '" . $SelFin . "'";
		$req3 = mysqli_query($link, $sql3);
		while ($row = mysqli_fetch_assoc($req3)) {
			$num_li_bl = $row["num_li_bl"];
			$num_bl_base = $row["num_bl"];
			//echo "$num_li_bl | $num_bl_base <br>" ;
		}
		// echo "$moisbl | $SelAnne | Jours fin : ".$jourBl[$moisbl]." | $jourfin |<br>" ;


		$nbcar = strlen($num_bl_base);
		$nbarrestant = $nbcar - 3;
		$numblfin = substr($num_bl_base, -$nbarrestant);
		$numblfin = $numblfin + 1;
		$numblfin = $moisbl . "-" . $numblfin;
		/* Test ecriture
echo $sql3."<br>";
echo "$num_li_bl | $num_bl_base |nb car : $nbcar|$nbarrestant| numero bl : $numblfin <br>" ;
echo "trsp : $CodeTrsp | Paie : $CodePaiement | obs : $observations <br>";
*/

		$sql_ajout_bl_haut = "INSERT INTO gestion_bl_haut VALUES (
'" . $last_num_entre . "',
'" . $IdNumClient . "',
'" . $numblfin . "',
'" . $date_bl_sql_base . "',
'" . $semaine_bl . "',
'" . $NumClient . "',
'" . $date_livr_sql_base . "',
'0',
'0',
'0',
'" . $CodePaiement . "',
'" . $CodeTrsp . "',
'0',
'0',
'" . $observations . "',
'" . $timestamp . "')";
		//
		$Req_ajout_bl_haut = mysqli_query($link, $sql_ajout_bl_haut);
		//
		$b = $b + 1;

		//echo "$sql_ajout_bl_haut | $Req_ajout_bl_haut<br>";
		switch ($Req_ajout_bl_haut) {
			case 0:
				echo "<tr><th>Num&#233;ro Bl :</th><th>$numblfin <img src='images/Erase.png'></th><th></th><th></th></tr>";
				echo "</table></center>";

				break;
			case 1:
				echo "<tr><th>Num&#233;ro Bl :</th><th>$numblfin <img src='images/Apply.png'></th><th></th><th></th></tr>";
				echo "</table></center>";

				break;
		}

		echo "<center><table class='t-text' width='750' border='1' cellspacing='2' cellpadding='2'>";
		$Nb_detail_import = 0;
		$sql31 = "SELECT * FROM temp_import_csv ";
		$sql31 .= "WHERE `num_client`='" . $NumClient . "'";
		$sql31 .= " AND `IdNumClient`='" . $IdNumClient . "'";
		$sql31 .= " AND `RsltImport`='0'";
		$sql31 .= " AND `RsltLot`='0'";
		$sql31 .= " AND `RsltStock`='0'";
		//echo "$sql31<br>";
		$req31 = mysqli_query($link, $sql31);
		while ($row = mysqli_fetch_assoc($req31)) {
			$num_plu = $row["num_plu"];
			$num_eleveur = $row["Num_Eleveur_Base"];
			$num_lot_entre = $row["num_lot"];
			$num_trace_entre = $row["num_lot_trace"];
			$num_plu_saisie = $row["num_plu"];
			$puht = $row["puht"];
			$poids_saisie = $row["poids"];
			$num_ligne_bltemp = $row["num_ligne_import"];

			$last_num_entre_bl = 0;
			$sql = "SELECT max(num_ligne) FROM temp_detail_bl";
			$req = mysqli_query($link, $sql);
			$row = mysqli_fetch_row($req);
			$last_num_entre_bl = $row[0];
			$last_num_entre_bl = $last_num_entre_bl + 1;

			$sql32 = "select * FROM plu";
			$sql32 .= " WHERE Num_PLU='" . $num_plu_saisie . "'";
			$sql32 .= " AND IdNumClient='" . $IdNumClient . "'";
			$req32 = mysqli_query($link, $sql32);
			while ($row = mysqli_fetch_assoc($req32)) {
				$nom_plu_temp = $row["PLU"];
			}

			$num_lot_entre = trim($num_lot_entre);
			$num_trace_entre = trim($num_trace_entre);
			$montant = $poids_saisie * $puht;
			$montant = number_format($montant, 2, '.', '');

			$sql41 = "INSERT INTO temp_detail_bl VALUES (
			'" . $last_num_entre_bl . "',
			'" . $IdNumClient . "',
			'" . $last_num_entre . "',
			'" . $numblfin . "',
			'" . $num_eleveur . "',
			'" . $num_lot_entre . "',
			'" . $num_trace_entre . "',
			'" . $num_plu_saisie . "',
			'" . $puht . "',
			'" . $poids_saisie . "',
			'1',
			'" . $montant . "')";
			//
			$res41 = mysqli_query($link, $sql41);
			//echo "requet : ".$sql41." | $res41 <br>";
			//
			if ($res41 == 1) {
				$sql51 = "UPDATE temp_import_csv SET
						`RsltImport` = '1'
						WHERE `num_ligne_import` = '$num_ligne_bltemp'";
				$res51 = mysqli_query($link, $sql51);
				//echo "requet : ".$sql51." | Update $res51 <br>";
			} else {
				$res51 = 0;
			}
			switch ($res51) {
				case 0:
					echo "<tr><th>PLU :</th><td>$last_num_entre_bl | $num_plu_saisie | $nom_plu_temp</td><th>$num_lot_entre-$num_trace_entre</th><th>Poids :</th><th>$poids_saisie Kg | $montant &#172;</th><th><img src='images/Erase.png'></th></tr>";
					break;
				case 1:
					echo "<tr><th>PLU :</th><td>$last_num_entre_bl | $num_plu_saisie | $nom_plu_temp</td><th>$num_lot_entre-$num_trace_entre</th><th>Poids :</th><th>$poids_saisie Kg | $montant &#172;</th><th><img src='images/Apply.png'></th></tr>";
					break;
			}
		}
		echo "<tr><th></th></tr>";
		echo "</table></center>";
	}

	// Import Stock
	echo "<h1> | Tra&ccedil;abilit&eacute;e => Import Stock | Import Lots</h1><br>";
	echo "<center><table class='t-text' width='900' border='1' cellspacing='2' cellpadding='2'>";
	$a = 0;

	$sql61 = "SELECT `Num_Eleveur_Base`, `date_etiq`,`dlc_etiq`, `Num_semaine`, `num_lot`, `num_lot_trace`, num_plu, SUM(poids), COUNT(`num_plu`)";
	$sql61 .= " FROM temp_import_csv";
	$sql61 .= " WHERE IdNumClient='" . $IdNumClient . "'";
	$sql61 .= " AND `RsltLot`='0'";
	$sql61 .= " AND `RsltStock`='0'";
	$sql61 .= " GROUP BY `Num_Eleveur_Base`, `date_etiq`, `dlc_etiq`, `Num_semaine`, `num_lot`, `num_lot_trace`, num_plu ASC";
	$req61 = mysqli_query($link, $sql61);
	//	echo "<br>$sql61<br>";
	while ($row = mysqli_fetch_assoc($req61)) {
		$num_plu_temp = $row["num_plu"];
		$poids_temp = $row["SUM(poids)"];
		$nb_pi_temp = $row["COUNT(`num_plu`)"];
		$semaine_saisie =  $row["Num_semaine"];
		$num_client_entre =  $row["Num_Eleveur_Base"];
		$num_lot_entre =  $row["num_lot"];
		$num_trace_entre =  $row["num_lot_trace"];
		$date_lot_entre  =  $row["date_etiq"];
		$date_dlc_temp	=  $row["dlc_etiq"];

		$sql661 = "select * FROM plu";
		$sql661 .= " WHERE Num_PLU='" . $num_plu_temp . "'";
		$sql661 .= " AND IdNumClient='" . $IdNumClient . "'";
		$req661 = mysqli_query($link, $sql661);
		while ($row = mysqli_fetch_assoc($req661)) {
			$nom_plu_temp = $row["PLU"];
		}


		$a++;
		$poids_temp = number_format($poids_temp, 3, '.', ' ');
		$nb_pi_temp = number_format($nb_pi_temp, 0, '.', ' ');
		$date = date("Y-m-d");
		//
		$last_num_entre_stock = 0;
		$sql2 = "SELECT max(IdNumStock) FROM stock";
		$req2 = mysqli_query($link, $sql2);
		$row = mysqli_fetch_row($req2);
		$last_num_entre_stock = $row[0];
		$last_num_entre_stock = $last_num_entre_stock + 1;

		$sql_ajout_stock = "INSERT INTO stock VALUES (
'" . $last_num_entre_stock . "',
'" . $IdNumClient . "',
'" . $date . "',
'" . $semaine_saisie . "',
'" . $num_client_entre . "',
'" . $num_lot_entre . "',
'" . $num_trace_entre . "',
'" . $num_plu_temp . "',
'" . $date_lot_entre . "',
'" . $date_dlc_temp . "',
'" . $poids_temp . "',
'" . $nb_pi_temp . "',
'0',
'1',
'',
'" . $timestamp . "')";
		//
		$Req_ajout_stock = mysqli_query($link, $sql_ajout_stock);
		//
		if ($Req_ajout_stock == 1) {
			$sql61 = "UPDATE temp_import_csv SET
						`RsltStock` = '1'
						WHERE `num_plu`='" . $num_plu_temp . "' AND `num_lot`='" . $num_lot_entre . "' AND `num_lot_trace`='" . $num_trace_entre . "'";
			$res61 = mysqli_query($link, $sql61);
			//echo "requet : ".$sql61." | Update $res61 <br>";
		} else {
			$res61 = 0;
		}


		switch ($res61) {
			case 0:
				echo "<tr><th>Entre Stock $last_num_entre_stock</th><th>$semaine_saisie | $date</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre | $date_dlc_temp</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Erase.png'></th></tr>";
				break;
			case 1:
				echo "<tr><th>Entre Stock $last_num_entre_stock</th><th>$semaine_saisie | $date</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre | $date_dlc_temp</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Apply.png'></th></tr>";
				break;
		}


		// Ajout table lot en position sortie
		$last_num_entre_lot = 0;
		$sql2 = "SELECT max(num_ligne_lot) FROM lot";
		$req2 = mysqli_query($link, $sql2);
		$row = mysqli_fetch_row($req2);
		$last_num_entre_lot = $row[0];
		$last_num_entre_lot = $last_num_entre_lot + 1;

		$nom_plu_temp = htmlspecialchars($nom_plu_temp, ENT_QUOTES);

		$sql_ajout_lot_sortie = "INSERT INTO lot VALUES (
'" . $last_num_entre_lot . "',
'" . $IdNumClient . "',
'" . $num_client_entre . "',
'" . $date_lot_entre . "',
'" . $num_lot_entre . "',
'" . $num_trace_entre . "',
'6',
'" . $poids_temp . "',
'1',
'0',
'" . $last_num_entre_stock . "',
'" . $nom_plu_temp . " | Nb Pi: " . $nb_pi_temp . "')";
		//
		$Req_ajout_lot = mysqli_query($link, $sql_ajout_lot_sortie);
		//
		/*
echo "$a | $sql_ajout_lot_sortie | $Req_ajout_lot<br><br>";
echo "$a | $sql_ajout_stock | $Req_ajout_stock <br>";
*/

		if ($Req_ajout_lot == 1) {
			$sql71 = "UPDATE temp_import_csv SET
						`RsltLot` = '1'
						WHERE `num_plu`='" . $num_plu_temp . "' AND `num_lot`='" . $num_lot_entre . "' AND `num_lot_trace`='" . $num_trace_entre . "'";
			$res71 = mysqli_query($link, $sql71);
			//echo "requet : ".$sql71." | Update $res71 <br>";
		} else {
			$res71 = 0;
		}


		switch ($res71) {
			case 0:
				echo "<tr><th>Entre Lot $last_num_entre_lot</th><th>$date_lot_entre</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Erase.png'></th></tr>";
				break;
			case 1:
				echo "<tr><th>Entre Lot $last_num_entre_lot</th><th>$date_lot_entre</th><th>$num_lot_entre | $num_trace_entre</th><th>$date_lot_entre</th><th>$num_plu_temp | $nom_plu_temp</th><th>$poids_temp Kg | $nb_pi_temp Pi</th><th><img src='images/Apply.png'></th></tr>";
				break;
		}
	}
	echo "</table></center>";

	if ($res51 and $res61 and $res71) {
		echo "<h1>La cr&eacute;ation c'est bien d&eacute;roul&eacute;e !</h1><br>";
	} else {
		echo "<h1>Pas de cr&#233;ation de bl et pas de creation de Tra&#231;abilit&#233;e</h1><br>";
	}
	/*
echo "<form id='form1' name='form1' method='GET' action='edition_recap_etiq.php' TARGET='_BLANK'>";
echo "<input type='hidden' name='ordre' value='$num_ordre'/>";
echo "<input type='hidden' name='date_bl' value='$date_bl_sql_base'/>";
echo "<input type='hidden' name='date_bl_livr' value='$date_livr_sql_base'/>";
echo "<input type='submit' value='* edition R&#233;cap ? *'>
      </div></td>";
echo "</tr>";
echo "</table>";
echo "</form><br>";
*/
	echo "<a href='edition_recap_etiq.php?ordre=$num_ordre&date_bl=$date_bl_sql_base&date_bl_livr=date_livr_sql_base' target='_blank'><input type='button' value='* edition R&#233;cap ? *'></a>";
}

echo "<a href='bon_de_livraison.php?limite=0' target='_self'><input type='button' value='* OK *'></a>";

include 'menu/footer.php';
?>
