<?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");

$dateOriginale = $_GET["date"];
$dateTransformee = DateTime::createFromFormat("Y-m-d", $dateOriginale)->format("d-m-Y");


 ?>
<!DOCTYPE html>
<head>
    <?php include("../includes/inc_header_common.php"); ?>

    <style>
        .hide_be {
            display: none;
			font-size: 12px;
        }
		
        .fixTableHead {
            overflow-y: auto;
            height: 700px;
        }
        
        .fixTableHead thead th {
            position: sticky;
            top: 0;
        }
        
        table {
            border-collapse: collapse;
            width: 100%;
			font-size: 14px;
        }
        
        th {
            background-color: #e5efff;
        }
        
        th,
        td {
            padding: 8px 15px;
            border: 2px solid #529432;
        }
		.table > thead > tr > th, .table-bordered > thead > tr > th{
			background-color: #e5efff !important;
		}
    </style>



<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
    
    <script>
        $(document).ready(function() {
            $('#toggleVisibility').on('change', function() {
                $('.hide_be').toggle();
				// alert('');
            });
        });
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <script>
        // Fonction pour imprimer le tableau en excluant les colonnes/lignes "hide_be"
        // Fonction pour imprimer le tableau
        function printTable() {
            const table = document.getElementById('listing_base');
            const newWindow = window.open('', '', 'width=800,height=600');
            newWindow.document.write('<html><head><title>Imprimer le tableau</title></head><body>');
            newWindow.document.write(table.outerHTML);
            newWindow.document.write('</body></html>');
            newWindow.document.close();
            newWindow.print();
        }

        // Fonction pour exporter le tableau en Excel (.xlsx) sans "hide_be"
        function exportTableToExcelXLSX(tableID, filename = 'table.xlsx') {
            const table = document.getElementById(tableID);
            const clonedTable = table.cloneNode(true);

            // Supprimer les colonnes ou lignes avec la classe "hide_be"
            clonedTable.querySelectorAll('.hide_be').forEach(el => el.remove());

            const workbook = XLSX.utils.table_to_book(clonedTable, { sheet: "Sheet1" });
            XLSX.writeFile(workbook, filename);
        }

        // Fonction pour exporter le tableau en CSV sans "hide_be"
        function exportTableToCSV(tableID, filename = 'table.csv') {
            const table = document.getElementById(tableID);
            const clonedTable = table.cloneNode(true);

            // Supprimer les colonnes ou lignes avec la classe "hide_be"
            clonedTable.querySelectorAll('.hide_be').forEach(el => el.remove());

            let csv = [];
            const rows = clonedTable.querySelectorAll('tr');

            rows.forEach(row => {
                let rowData = [];
                const cells = row.querySelectorAll('th, td');
                cells.forEach(cell => {
                    rowData.push(cell.textContent.replace(/"/g, '""')); // Échapper les guillemets
                });
                csv.push('"' + rowData.join('","') + '"');
            });

            const csvData = new Blob([csv.join('\n')], { type: 'text/csv' });
            const a = document.createElement('a');
            a.href = URL.createObjectURL(csvData);
            a.download = filename;
            a.click();
        }
    </script>
	
</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 MP  <? echo $dateTransformee; ?></h1>
                        </div>
						
	<div class="col-md-2 text-right" style="margin-top:15px;">					
						        <!-- Checkbox toggle -->
        <div class="form-check form-switch">
            <input 
                class="form-check-input" 
                type="checkbox" 
                id="toggleVisibility">
            <label class="form-check-label" for="toggleVisibility">
                Afficher/Cacher les BE
            </label>
        </div>
    </div>

    <!-- Boutons -->
 <!-- Boutons -->
 <div class="col-md-4 text-right" style="margin-top:15px;">
        <a href="#" onclick="printTable()" target="_blank">
            <button class="btn btn-default" type="button" id="print_page">
                <i class="fa fa-print"></i> Imprimer
            </button>
        </a>

        <a href="#" onclick="exportTableToExcelXLSX('listing_base')" target="_blank">
            <button class="btn btn-default" type="button" id="export_excel">
                <i class="fa fa-file-excel"></i> Exporter en Excel
            </button>
        </a>

        <a href="#" onclick="exportTableToCSV('listing_base')" target="_blank" style="margin-right:5px;">
            <button class="btn btn-default" type="button" id="export_csv">
                <i class="fa fa-file-csv"></i> Exporter en CSV
            </button>
        </a>
	</div>	
						<div class="top_table"></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="fixTableHead">




                                <table class="table table-bordered table-hover table-striped" id="listing_base">
                                    <thead>
            <tr class="sticky-header">
                <th>Code article</th>
                <th>Désignation Article</th>
                <? if($_GET["depot"] == 0 || $_GET["depot"] == 1 ){?><th>Qte<br>Dépôt Principal</th><? } ?>
                <? if($_GET["depot"] == 0 || $_GET["depot"] == 2 ){?><th>Qte<br>Dépôt Usine</th><? } ?>
                <th>Quantité Total</th>
                <th>Qte CMD en cours</th>
            </tr>
                                    </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 sticky-header">
																	 // <th>                                                
																			// Code article                                              
																	// </th>
																	 // <th>                                                
																			// Désignation Article                                              
																	// </th>
																	 // <th >                                                
																			// Quantité Dépôt.P                                           
																	// </th>
																	// <th >                                                
																			// Quantité Dépôt.U                                            
																	// </th>
																	// <th >                                                
																			// Quantité Total                                                 
																	// </th>
																	
																// </tr>';
																
																$qte_total = 0;
																$qte_emballe = 0;
																$qte_palette = 0;
																
																foreach($result_3 as $row_3){	
																	$qte_all = array(0,0,0);
																	
																	$qte_be = "";
																	//====== unite ================
																	$query_unt = "SELECT id_depot,SUM(qte) qte_all FROM `pm_depot_stock` WHERE id_produits = ".$row_3["id"]." GROUP BY id_depot" ;
																	// echo $query_unt;
																	$result_unt = @$db->query($query_unt);
																	if($result_unt !== false){
																		// $row_unt = $result_unt->fetch();
																		foreach($result_unt as $row_unt){																		
																			$qte_all[$row_unt["id_depot"]] = $row_unt["qte_all"];
																			// echo $row_unt["id_depot"]." >> ".$row_unt["qte_all"];
																		}
																	}
																	// exit();
																	//================================
																	// if($qte_all <= 0){
																		// $color = 'style="color:red;"';
																	// }else{
																		// $color = 'style="color:green;"';
																	// }
																	//========== query_4_cmd ======
																	$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 .= " ORDER BY desingnation ASC";
																	
																	
																	$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>";
																		}	
																	}
																	
																	//================================																	
																	//================================
																	// 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)){

																		echo '<tr>
																				<td class="text-center">'.$row_3["code_produit"].'</td>
																				<td class="text-center">'.$row_3["desingnation"].'</td>';
																		if($_GET["depot"] == 0 || $_GET["depot"] == 1 ){ 
																			echo '<td class="text-center"><b> '.number_format($qte_all[1],0, ',', ' ').'</b><div class="hide_be">'.get_be_qtee($row_3["id"],1).'</div></td>';
																		}
																		if($_GET["depot"] == 0 || $_GET["depot"] == 2 ){ 
																			echo '<td class="text-center"><b> '.number_format($qte_all[2],0, ',', ' ').'</b><div class="hide_be">'.get_be_qtee($row_3["id"],2).'</div></td>';
																		}
																		echo '<td class="text-center"><b> '.number_format($qte_all[1]+$qte_all[2],0, ',', ' ').'</b><div class="hide_be">'.get_be_qtee($row_3["id"],'1,2').'</div></td>';
																		
																		echo '<td class="text-center"><b '.$color_cmd.'>'.number_format($qte_en_cour,1, ',', ' ').'</b></td>';
																		
																				
																		echo '</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_qtee($id_product,$id_depot)
{
	global $db;
	$message = "";
	$query_unt = "SELECT ref_id,SUM(qte) qte_all FROM `pm_depot_stock` WHERE id_produits = ".$id_product." AND id_depot IN(".$id_depot.") GROUP BY ref_id " ;
	$result_unt = @$db->query($query_unt);
	if($result_unt !== false){
		foreach($result_unt as $row_unt){																		
			$message .= "BE_".$row_unt["ref_id"].": ".$row_unt["qte_all"]."<br>";
		}
	}
	
	return $message;

}

																//=================================
																		
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;

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

?>
