<?php

// ini_set('display_errors', 1);


define("ADMIN", true);
require_once("../../common/lib.php");
require_once("../../common/define.php");
define("TITLE_ELEMENT", 'Inventaire');
if(!isset($_SESSION['user'])){
    header("Location: ../login.php");
    exit();
}
require_once("../includes/fn_module.php"); 
require_once("../includes/functions.php"); ?>
<!DOCTYPE html>
<head>
    <?php include("../includes/inc_header_common.php"); ?>

</head>
<body>
    <div id="wrapper">
        <?php include("../includes/inc_top.php"); ?>
        <div id="page-wrapper">
            <div class="page-header">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-md-6">
                            <h1><i class="fa fa-random"></i> Inventaire <? echo $_GET["cat"]; ?></h1>
                        </div>
						<div class="col-md-6 text-right" style="margin-top:15px;">
							<a href="#" target="_blank"><button class="btn btn-default" type="button" id="print_page"><i class="fa fa-print"></i> Imprimer</button></a>
						</div>
                    </div>
                </div>
            </div>
            <div class="container-fluid">
                <div class="alert-container">
                    <div class="alert alert-success alert-dismissable"></div>
                    <div class="alert alert-warning alert-dismissable"></div>
                    <div class="alert alert-danger alert-dismissable"></div>
                </div>
                <div class="row">				
				
	<form method="GET" action="stock_search.php">				
<div class="table-responsive">
                                <table class="table table-bordered table-hover table-striped" id="listing_base">
                                    <thead>

                                    </thead>
                                    <tbody>    
									
									    <?php
										if($db !== false){
											
											$query = "select b.id, b.nom, a.type from pm_category as a ,pm_famille_classe as b where a.stockable = 1 AND a.id = b.famille_cat ";
											if($_GET["cat"] <> ""){
												$query .= " AND a.type = '".$_GET["cat"]."' ";
											}
											// echo $query."<br>";
											$result = @$db->query($query);
											if($result !== false){
												foreach($result as $row){		
													echo '<tr><td class="text-center" colspan="10" style="background-color: #ffc000;color:#000"><b>'.$row['nom'].'</b></td></tr>';												
													$query_2 = "SELECT * FROM pm_famille WHERE famille_classe = ".$row['id'];
													// echo $query_2."<br>";
													$result_2 = @$db->query($query_2);
													if($result_2 !== false){
														$qte_all_total = 0;
														$qte_emballe_total = 0;
														$qte_palette_total = 0;
														foreach($result_2 as $row_2){	
															echo '<tr><td class="text-center" colspan="10" style="background-color: #dadada;color:#000"><b>'.$row_2['nom'].'</b></td></tr>';
															
															if($row['type'] == "vente"){
																$query_3 = "SELECT * FROM pm_produits WHERE famille = ".$row_2['id'];
																$query_3 .= " AND status = 1 ";
															}else{
																$query_3 = "SELECT * FROM pm_produitsachat WHERE famille = ".$row_2['id'];
																// $query_3 .= " AND status = 1 ";
															}
															
															//================= Permission =============
															// if($_SESSION['user']['type'] == "editor"){
																// $query_3 .= " AND status = 1 ";
															// }
															//==========================================
															
															
															
															$result_3 = @$db->query($query_3);
															if($result_3 !== false){
																echo '<tr class="nodrop nodrag">
																	 <th width="20">                                                
																			Code article                                              
																	</th>
																	 <th width="130">                                                
																			Désignation Article                                              
																	</th>
																	 <th width="50">                                                
																			Quantité                                            
																	</th>
																	<th width="150">                                                
																			BE                                            
																	</th>
																	<th width="20">                                                
																			Unité                                              
																	</th>
																	<th width="20">                                                
																			Colisage                                               
																	</th>
																	<th width="70">                                                
																			Qte emballée                                               
																	</th>
																	<th width="20">                                                
																			Colisage palette                                               
																	</th>
																	<th width="70">                                                
																			Qte palette                                               
																	</th>
																	<th width="70">                                                
																			Qte CMD en cours                                               
																	</th>
																</tr>';
																
																$qte_total = 0;
																$qte_emballe = 0;
																$qte_palette = 0;
																
																foreach($result_3 as $row_3){	
																
																	$qte_be = "";
																	//====== unite ================
																	$query_unt = "select * from pm_unite where id =".$row_3["unite"] ;
																	$result_unt = @$db->query($query_unt);
																	if($result_unt !== false){
																		$row_unt = $result_unt->fetch();
																		
																		$unt = $row_unt["name"];
																		
																	}
																	//=============================
																	if($row['type'] == "vente"){
																		// $query_4 = "SELECT *,SUM(qte) as cnt,SUM(a.coulissage) as coul FROM pm_produits as a,pm_be_product as b where a.id = b.id_be AND a.id = ".$row_3["id"] ;
																	}else{
																		// $query_4 = "SELECT *,SUM(qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_be_product as b where a.id = b.name AND a.id = ".$row_3["id"] ;
																		$query_4 = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_be_product as b,pm_be as c where a.id = b.name AND b.id_be = c.id AND a.id = ".$row_3["id"] ;
																		if($_GET["date"]){
																			$date_enter = strtotime($_GET["date"]);
																			$query_4 .= " AND date_entre <=".$date_enter;
																		}
																		$query_4 .= " ORDER BY desingnation ASC";
																		// $query_4_retour = "SELECT *,SUM(qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_br_product as b where a.id = b.name AND a.id = ".$row_3["id"] ;
																		// $query_4_retour = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_br_product as b, pm_br as c where a.id = b.name AND b.id_br = c.id = b.name AND a.id = ".$row_3["id"] ; // 05/06/2018
																		$query_4_retour = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_br_product as b, pm_br as c where a.id = b.name AND b.id_br = c.id AND a.id = ".$row_3["id"] ;
																		if($_GET["date"]){
																			$query_4_retour .= " AND date_entre <=".$date_enter;
																		}
																		$query_4_retour .= " ORDER BY desingnation ASC";
																		// $query_4_fabri = "SELECT *,SUM(qte) as cnt,SUM(a.coulissage) as coul FROM pm_produitsachat as a,pm_bf_product as b where a.id = b.id_produits AND a.id = ".$row_3["id"] ;
																		$query_4_fabri = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul, c.id as id_bf FROM pm_produitsachat as a,pm_bf_product as b, pm_bf as c where a.id = b.id_produits AND b.id_bf = c.id AND a.id = ".$row_3["id"] ;
																		if($_GET["date"]){
																			$query_4_fabri .= " AND date_bf <=".$date_enter;
																		}
																		$query_4_fabri .= " ORDER BY desingnation ASC";
																		// echo $query_4_fabri."<br>" ;
																		// $query_4_cmd = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul, c.id as id_bc FROM pm_produitsachat as a,pm_bc_product as b , pm_bc as c where a.id = b.name AND b.id_bc = c.id AND c.valider <> 1 AND a.id = ".$row_3["id"] ;
																		$query_4_cmd = "SELECT *,SUM(b.qte) as cnt,SUM(a.coulissage) as coul, c.id as id_bc FROM pm_produitsachat as a,pm_bc_product as b , pm_bc as c where a.id = b.name AND b.id_bc = c.id AND c.valider <> 1 AND a.id = ".$row_3["id"] ;
																		if($_GET["date"]){
																			// $query_4_cmd .= " AND date_livraison <=".$date_enter;
																		}
																		$query_4_cmd .= " ORDER BY desingnation ASC";
																	}
																	
																	// echo $query_4_cmd ."<br>"; 
																	// echo $query_4_cmd ."<br>";
																	// echo $query_4_retour ."<br>";
																	$qte = $coulissage = $emballe = $qte_all = $coulissage_all = 0;
																	//========== query_4 =============
																	$result_4 = @$db->query($query_4);
																	if($result_4 !== false){
																		$row_4 = $result_4->fetch();
																		
																		$qte = $row_4["cnt"];
																		$coulissage = $row_4["coul"];
																		
																	}
																	//================================
																	//========== query_4_retour ======		
																	$result_4_retour = @$db->query($query_4_retour);
																	if($result_4_retour !== false){
																		$row_4_retour = $result_4_retour->fetch();
																		
																		$qte_retour = $row_4_retour["cnt"];
																		$coulissage_retour = $row_4_retour["coul"];
																		
																	}
																	//================================
																	//========== query_4_fabri ======

																	$result_4_fabri = @$db->query($query_4_fabri);
																	if($result_4_fabri !== false){
																		// $row_4_fabri = $result_4_fabri->fetch();
																		foreach($result_4_fabri as $row_4_fabri){
																			
																		$qte_fabri = $row_4_fabri["cnt"];
																		$coulissage_fabri = $row_4_fabri["coul"];
																		
																		//============= GET qte BE ============================
																		// $qu_fabri = "SELECT `id_be`,`qte` FROM `pm_be_ecart` WHERE id_produit = ".$row_3["id"] ;
																		// $result_4_fab = @$db->query($qu_fabri);
																		// if($result_4_fab !== false){
																			// foreach($result_4_fab as $row_4_fab){
																				// $qte_be .= "<div><span style='color:#000 !important;'>BE_".$row_4_fab["id_be"]."</span> : ".$row_4_fab["qte"]."</div>";	
																			// }																				
																		// }
																		//=================================
																		$qte_be .= get_be($row_3["id"]);
																		
																		
		
																		}
																	}
																	//================================
																	//========== query_4_cmd ======
																	$qte_en_cour = 0;
																	$result_4_cmd = @$db->query($query_4_cmd);
																	if($result_4_cmd !== false){
																		$row_4_cmd = $result_4_cmd->fetch();
																		
																		$qte_cmd = $row_4_cmd["cnt"];
																		$coulissage_cmd = $row_4_cmd["coul"];
																		
																		//============ QTE en cour ==========
																		$query_encour = "SELECT *,SUM(b.qte) as cnt FROM pm_be as a , pm_be_product as b WHERE a.id = b.id_be AND b.name= ".$row_4_cmd["name"]." AND ref_bc = ".$row_4_cmd["id_bc"];
																		// echo $query_encour ."<br>";
																		$result_encour = @$db->query($query_encour);
																		if($result_encour !== false){
																			$row_encour = $result_encour->fetch();
																			$qte_encour_be  = $row_encour["cnt"];
																			$qte_en_cour = $qte_cmd - $qte_encour_be; 
																			// echo $query_encour." ==>".$qte_encour_be."<br>";
																		}	
																	}
																	
																	//================================
																	//========== Caclcule ============
																	$qte_all = $qte - ($qte_retour + $qte_fabri) ;
																	$coulissage_all = $coulissage - ($coulissage_retour + $coulissage_fabri) ;
																	if($coulissage <> ""){
																		$emballe = floatval($qte_all/$row_3["coulissage"]) ;
																	}
																	//================================
																	if($qte_all <= 0){
																		$color = 'style="color:red;"';
																	}else{
																		$color = 'style="color:green;"';
																	}
																	
																	//================================
																	// if($qte_cmd == 0){
																		// $cmd_encour = 0;
																	// }else{
																		// $cmd_encour = $qte_cmd-$qte;
																	// }
																	if($qte_en_cour <= 0){
																		$color_cmd = 'style="color:red;"';
																	}else{
																		$color_cmd = 'style="color:green;"';
																	}
																	//================================
																	// if($qte_all == "") $qte = "-" ;
																	// if($coulissage == "") $coulissage = "-" ;
																	
																	if((intval($qte_all) <> 0)||(intval($qte_en_cour) > 0)){
																	// if((intval($qte_all) == 0)||(intval($qte_en_cour) == 0)){
																	if(((intval($qte_all) > 0)&&($row_3["status"] == 0))|| $row_3["status"] == 1){
																		
																		$sql = $row_3["id"].",";
																		
																		echo '<tr>
																				<td class="text-center">'.$row_3["code_produit"].'</td>
																				<td class="text-center">'.$row_3["desingnation"].'</td>
																				<td class="text-center"><b '.$color.'> '.number_format($qte_all,1, ',', ' ').'</b></td>
																				<td class="text-left"><b '.$color.'>'.$qte_be.'</b></td>
																				<td class="text-center">'.mb_strtoupper($unt).'</td>
																				<td class="text-center">'.$row_3["coulissage"].'</td>
																				<td class="text-center">'.number_format($qte_all/$row_3["coulissage"],1, ',', ' ').'</td>
																				<td class="text-center">'.$row_3["coulissage_palette"].'</td>
																				<td class="text-center">'.number_format($qte_all/$row_3["coulissage_palette"],1, ',', ' ').'</td>
																				
																				<td class="text-center"><b '.$color_cmd.'>'.number_format($qte_en_cour,1, ',', ' ').'</b></td>
																			</tr>';
																	}
																	}
																		$qte_en_cour = 0;															
																		$qte_total = $qte_total + $qte_all;
																		$qte_emballe += number_format($qte_all/$row_3["coulissage"],1, '.', '') ;
																		$qte_palette += $qte_pal ;
																}
																$qte_all_total = $qte_all_total + $qte_total;
															
																$qte_emballe_total += $qte_emballe;
																$qte_palette_total += $qte_palette;
																
																
																if($qte_total > 0){
																echo '<tr style="background-color: #ffbebe;color:#000">
																			<td class="text-center" style="background-color: #ffbebe;"colspan="2"><b>Total sous-famille</b></td>
																			<td class="text-center" style="background-color: #ffbebe;"><b>'.number_format($qte_total,1, ',', ' ').' '.mb_strtoupper($unt).'</b></td>
																			<td class="text-center" style="background-color: #ffbebe;" colspan="3"></td>
																			<td class="text-center" style="background-color: #ffbebe;"><b>'.$qte_emballe.'</b></td>
																			<td class="text-center" style="background-color: #ffbebe;"></td>
																			<td class="text-center" style="background-color: #ffbebe;"><b>'.$qte_palette.'</b></td>
																			<td class="text-center" style="background-color: #ffbebe;"></td>
																			
																		</tr>';															
																}
															
															
															// echo '<tr style="background-color: #ffbebe;color:#000">
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;">Total sous-famille : '.number_format($qte_total,1, ',', ' ').' '.mb_strtoupper($unt).'</td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></td>
																			// <td class="text-center" style="background-color: #ffbebe;"></b></td>
																		// </tr>';
																		
																		
																		
																		
																		
																
															}	
															
														}
														
																if($qte_all_total > 0){
																	echo '<tr style="background-color: #bec0ff;color:#000">
																			<td class="text-center" style="background-color: #bec0ff;"colspan="2"><b>Total Famille</b></td>
																			<td class="text-center" style="background-color: #bec0ff;"><b>'.number_format($qte_all_total,1, ',', ' ').' '.mb_strtoupper($unt).'</b></td>
																			<td class="text-center" style="background-color: #bec0ff;" colspan="3"></td>
																			<td class="text-center" style="background-color: #bec0ff;"><b>'.$qte_emballe_total.'</b></td>
																			<td class="text-center" style="background-color: #bec0ff;"></td>
																			<td class="text-center" style="background-color: #bec0ff;"><b>'.$qte_palette_total.'</b></td>
																			<td class="text-center" style="background-color: #bec0ff;"></td>
																			
																			
																		</tr>';
																}
																$qte_emballe_total = 0;
																$qte_palette_total = 0;

													// echo '<tr style="background-color: #bec0ff;color:#000">
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;">Total famille : '.number_format($qte_all_total,1, ',', ' ').' '.mb_strtoupper($unt).'</td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></td>
																			// <td class="text-center" style="background-color: #bec0ff;"></b></td>
																		// </tr>';		

																		
													}
												}	
											}
											
											
											
											// $srt = '<tr>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                    // <td class="text-center">x</td>
                                                // </tr>';
											
										} ?>
									
												                                                



                                    </tbody>
                                </table>
                            </div>
									

					
                </div>
            </div>
        </div>
    </div>
</body>
</html>
<?php
$_SESSION['msg_error'] = "";
$_SESSION['msg_success'] = "";
$_SESSION['msg_notice'] = ""; ?>


<?


																//=================================
																		
function get_be($id_product)
{
	global $db;
	$ids_be = "";
	$test_qte_be = 0 ;
	$query_be = "SELECT id_be FROM pm_be_product WHERE name = ".$id_product;
	
	$result_be = @$db->query($query_be);
	if($result_be !== false){
		foreach($result_be as $row_be){
			$test_qte_be = get_qte_be($id_product,$row_be["id_be"]) ;
			// echo  "test_qte_be ==> ".$test_qte_be."<br>";
			
			//===================================
			$date_be = get_info_table("pm_be","date_entre",$row_be["id_be"]);
			$date_enter = strtotime($_GET["date"]);
			// $query_4 .= " AND date_entre <=".$date_enter;
			//===================================
			if($date_be <= $date_enter){			
				if($test_qte_be > 0){
					$ids_be .= "<span style='color:#000;'>BE_".$row_be["id_be"]."</span> : ".$test_qte_be."<br>";
				}
			}
		}
	}
	if($ids_be <> ""  ){
		return substr($ids_be, 0, -1);	
	}else{
		return "";
	}
}



function get_qte_be($id_product,$id_be)
{
	global $db;
	
	
	$id = $id_product;
	// $id_be = $id_be;

	$qte_sortie = 0 ;
	$qte_valide = 0 ;

	//====================== QTE utiliser
	$query_sorti = "SELECT SUM(qte) as all_qte FROM pm_bf_product WHERE id_be = ".$id_be." AND id_produits = ".$id;
	// echo $query_sorti;
	$result_sorti = @$db->query($query_sorti);
	if($result_sorti !== false){
	$outp_sorti = $result_sorti->fetch();
		$qte_sortie += $outp_sorti["all_qte"];
	}	
	//====================== BS
	
	$query_sorti = "SELECT SUM(qte) as all_qte FROM pm_br_product WHERE id_be = ".$id_be." AND name = ".$id;
	// echo $query_sorti;
	$result_sorti = @$db->query($query_sorti);
	if($result_sorti !== false){
	$outp_sorti = $result_sorti->fetch();
		$qte_sortie += $outp_sorti["all_qte"];
	}	
	//======================
	
	
	
	

		$cont = 0 ;
		$outp = array();



	if($id_be > 276 ){
		
		$query = "SELECT qte FROM pm_be_product WHERE id_be = ".$id_be." AND name = ".$id;
		$result = @$db->query($query);
		if($result !== false){
			// $outp = $result->fetch();
			foreach($result as $row){
				
				$qte_valide = $row["qte"] - $qte_sortie;
		
			} 	
		}
		
	}else{
		
	//====================== QTE ecart

	$query_e = "SELECT SUM(qte) as all_qte FROM pm_be_ecart WHERE id_be = ".$id_be." AND id_produit = ".$id;
	// echo $query_e;
	$result_e = @$db->query($query_e);
	if($result_e !== false){
	$outp_e = $result_e->fetch();
		// $qte_sortie += $outp_sorti["all_qte"];
		$qte_valide = $outp_e["all_qte"] - $qte_sortie;
	}	
	//======================	
		
	}



	return $qte_valide;
	// return "<span style='color:#000;'>".$id_be."</span> : ".$qte_valide;

}	
																		//=================================

?>
