<?php

// ini_set('display_errors', 1);


define("ADMIN", true);
require_once("../../../common/lib.php");
require_once("../../../common/define.php");
define("TITLE_ELEMENT", 'etat');
if(!isset($_SESSION['user'])){
    header("Location: ../../login.php");
    exit();
}
require_once("../../includes/fn_module.php"); 

header('Content-Type: text/html; charset=utf-8');
ini_set('default_charset', 'utf-8');
setlocale(LC_ALL, 'fr_FR');
setlocale(LC_TIME, "fr_FR");

$avoir_array = array();
$avoir_vente_array = array();
?>
<!DOCTYPE html>
<head>
    <?php include("../../includes/inc_header_common.php"); ?>
    <?php include("../../includes/functions.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-registered"></i> État Financier détaillé du <? echo $_GET["date_from"]; ?> au <? echo $_GET["date_to"]; ?></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="row">	
<table cellspacing="20" cellpadding="20">	
				<td>* Année exercice : </td><td><b><? if($_GET["annee"] == "") echo "Tous"; else echo $_GET["annee"]; ?></b></td></tr>			
				<tr><td>* Type d’opération : </td><td><b><? echo $_GET["type"];?></b></td>

				<td>* Etat du règlement : </td><td><b><? if($_GET["payer"] == 1) echo "Payer"; else echo "Non payer"; ?></b></td></tr>
				<tr><td>* Mode de réglement : </td><td><b><? 
				if($_GET["mode"] <> "" ) {
					echo get_mode_reglement($_GET["mode"]);
				}else{
					echo "Tous";
				}
				?></b></td>
				<td>* Journal : </td><td><b><? 
				if($_GET["journal"] <> "" ) {
					echo get_journal($_GET["journal"]);
				}else{
					echo "Tous";
				}
				?></b></td></tr>
				<tr><td>* Compte financer : </td><td><b><? 
				if($_GET["compte"] <> "" ) {
					echo compte($_GET["compte"]);
				}else{
					echo "Tous";
				}
				?></b></td>
				<td>* Fournisseur : </td><td><b><? 
				if($_GET["fournisseur"] <> "" ) {
					echo fournisseur($_GET["fournisseur"]);
				}else{
					echo "Tous";
				}
				?></b></td></tr>
				<tr><td>* Client : </td><td><b><? 
				if($_GET["client"] <> "" ) {
					echo client($_GET["client"]);
				}else{
					echo "Tous";
				}
				?></b></td>
				<td>* Référence facture Achat : </td><td><b><? 
				if($_GET["ref_fac_achat"] <> "" ) {
					echo ref_facture($_GET["ref_fac_achat"]);
				}else{
					echo "Tous";
				}
				?></b></td></tr>
				<tr><td>* Référence facture Vente : </td><td><b><? 
				if($_GET["ref_fac_vente"] <> "" ) {
					echo ref_facture_vente($_GET["ref_fac_vente"]);
				}else{
					echo "Tous";
				}
				?></b></td>
				<td>* Retenue à la source : </td><td><b><? 
				if($_GET["retenue"] == 1 ) {
					echo "OUI";
				}else{
					echo "NON";
				}
				?></b></td>
				</tr>
				<tr><td>* Montant de : </td><td><b><? 
					echo $_GET["montant_from"];
				?></b></td>
				<td>* Montant à : </td><td><b><? 
					echo $_GET["montant_to"];
				?></b></td>
				</tr>				
</table>				
				<br><br>
				<div class="table-responsive">
				
				<?
				if($_GET["payer"] == 0){
					$date_name = "d'echeance";
				}else{
					$date_name = "de paiement";
				}
				?>
				
                                <table class="table table-bordered table-hover table-striped" id="listing_base">
                                    <thead>
										<th><b>Date <? echo $date_name; ?></b></th>
										<th><b>ID</b></th>
										<th><b>Année exercice</b></th>
										<th><b>Journal</b></th>
										<th><b>Mode de<br>réglement</b></th>
										<th><b>Référence</b></th>
										<th><b>Compte financer</b></th>
										<th><b>Libellé</b></th>
										<th><b>Ref Fac<br>Achat/vente</b></th>
										<th><b>Fournisseur/Client</b></th>
										<th><b>Montant IN</b></th>
										<th><b>Montant OUT</b></th>
                                    </thead>
                                    <tbody> 
									
									<?
									$count = 0;
									$total = 0;
									$total_in = 0;
									$total_out = 0;
									$weekand = "";
									$mois = array(1 => 0,2 => 0,3 => 0,4 => 0,5 => 0,6 => 0,7 => 0,8 => 0,9 => 0,10 => 0,11 => 0,12 => 0);
									$date_trans = strtotime($_GET["date_from"]) ;
									$date_transa = strtotime($_GET["date_to"]. '+1 days') ;
									if($_GET["payer"] == 0){
										$query = "select * from pm_transaction where date_echeance >= ".$date_trans." AND date_echeance < ".$date_transa  ;
									}else{
										$query = "select * from pm_transaction where date_paiement >= ".$date_trans." AND date_paiement < ".$date_transa ;
									}
									if($_GET["montant_from"] > 0){
										$query .= " AND montant >= ".$_GET["montant_from"]."";
									}
									if($_GET["montant_to"] > 0){
										$query .= " AND montant <= ".$_GET["montant_to"]."";
									}
									
									if($_GET["ref"] <> ""){
										$query .= " AND ref LIKE '%".$_GET["ref"]."%'";
									}
									
									if($_GET["type"] <> ""){
										$query .= " AND type = '".$_GET["type"]."'";
									}
																		
									if($_GET["mode"] <> ""){
										$query .= " AND mode_reglement = ".$_GET["mode"];
									}
									if($_GET["journal"] <> ""){
										$query .= " AND journal = ".$_GET["journal"];
									}
									if($_GET["compte"] <> ""){
										$query .= " AND compte = ".$_GET["compte"];
									}
									if($_GET["fournisseur"] <> ""){
										$query .= " AND fournisseur = ".$_GET["fournisseur"];
									}
									if($_GET["client"] <> ""){
										$query .= " AND client = ".$_GET["client"];
									}
									// if($_GET["ref_fac_achat"] <> ""){
										// $query .= " AND ref_facture_achat = '".$_GET["ref_fac_achat"]."'";
									// }
									// if($_GET["ref_fac_vente"] <> ""){
										// $query .= " AND ref_facture_vente = '".$_GET["ref_fac_vente"]."'";
									// }
									
									
									if($_GET["annee"] <> ""){
										$query .= " AND annee = '".$_GET["annee"]."'";
									}
									
									
									if($_GET["payer"] == 0){
										$query .= " AND ( ISNULL(status_paiement) OR status_paiement = 0 )";										
										$query .= " order by date_echeance ASC";
									}else{
										$query .= " AND status_paiement = 1 ";										
										$query .= " order by date_paiement ASC";
									}
									// echo $query;
									$result = @$db->query($query);
									if($result !== false){
										foreach($result as $row){
										$count++;
										$weekand = "";
										$ref_facture_vente = "";
										$ref_facture_achat = "";
										
										//============= achat ================================
										$id_facture_achat = array();
										if($row["ref_facture_achat"] <> ""){
											$ref_facture_achat = ref_facture($row["ref_facture_achat"]) ;
										}else{
											$cnt = 0;
											$query_fact = "select ref_facture_achat2 from pm_transaction_product where id_transaction =".$row["id"];
											// echo "<br>".$query_fact;
											$result_mnt = @$db->query($query_fact);
											if($result_mnt !== false){
												// $row_mnt = $result_mnt->fetch();
												foreach($result_mnt as $row_mnt){
													$ref_facture_achat .=  ref_facture($row_mnt["ref_facture_achat2"])." - ";
													$id_facture_achat[$cnt] =  $row_mnt["ref_facture_achat2"];
													$cnt++;
												}
												$ref_facture_achat = substr($ref_facture_achat, 0, -3);	
											}
												
										}//============= Vente ======
										$id_facture_vente = array();
										if($row["ref_facture_vente"] <> ""){
											$ref_facture_vente = ref_facture_vente($row["ref_facture_vente"]) ;
										}else{
											
											$cnt = 0;
											$query_fact = "select id,ref_facture_vente2 from pm_transaction_product where id_transaction =".$row["id"];
											// echo $query_fact;
											$result_mnt = @$db->query($query_fact);
											if($result_mnt !== false){
												// $row_mnt = $result_mnt->fetch();
												foreach($result_mnt as $row_mnt){
													$ref_facture_vente .=  ref_facture_vente($row_mnt["ref_facture_vente2"])." - ";
													$id_facture_vente[$cnt] =  $row_mnt["ref_facture_vente2"];
													$cnt++;
												}
												$ref_facture_vente = substr($ref_facture_vente, 0, -3);	
											}
												
										}
										//=========================================
										// echo $ref_facture_achat;
										// exit();
										
		
												

										
										
										$total += $row["montant"];

										
										if($_GET["payer"] == 0){
											$date = date( "d-m-Y", $row["date_echeance"]) ; 
											$date_name = "d'echeance";
											// $date = date( "d-m-Y", $row["date_trans"]) ; 
										}else{
											$date = date( "d-m-Y", $row["date_paiement"]) ; 
											$date_name = "de paiement";
										}
										
										$month = date("n",strtotime($date));
										$jour  = date("w",strtotime($date));
										
										if($mois[$month] == 0){
											echo "<tr><td colspan='12' align='center' style='background-color: #FFF;'><b style='text-transform: capitalize;'>".utf8_encode(strftime("%B",strtotime($date)))." ".date("Y",strtotime($date))."</b></td></tr>";
										}
										if(($jour == 0)||($jour == 6)){ //==== Si samdi ou dimanche  ====
											$weekand = "style='background-color: #ccc;'";
										}	
										
										
										
									if($_GET["ref_fac_vente"] <> "" && ( in_array($_GET["ref_fac_vente"], $id_facture_vente)) ){
										$weekand .= "";
										
												//====================================
											// if($row["type"] == "in"){
												// $total_in += $row["montant"];
											// }else{
												// $total_out += $row["montant"];							
											// }
											//====================================									

									}else if($_GET["ref_fac_vente"] <> "" ){
										$weekand .= " class='hidden'";
									}
									
									if($_GET["ref_fac_achat"] <> "" && ( in_array($_GET["ref_fac_achat"], $id_facture_achat)) ){
										$weekand .= "";
										
									
									}else if($_GET["ref_fac_achat"] <> "" ){
										$weekand .= " class='hidden'";
										
										
									}
									
										//====================================
										if($row["type"] == "in"){
											$total_in += $row["montant"];
										}elseif($row["type"] == "out"){
											$total_out += $row["montant"];							
										}else{
											
										}
										//====================================
									
									
									if($weekand == ""){
										
									}
									?>
									<tr>	
									<td <? echo $weekand ; ?> style='text-transform: capitalize;'><table><tr><td width='50%' align='right'><b><? echo strftime("%A",strtotime($date))."&nbsp;</b><br><b>".$date; ?></b></td></tr></table></td>		
									<td <? echo $weekand ; ?>><a href="/admin/modules/transaction/index.php?view=form&id=<? echo $row["id"]; ?>" target="_blank"><? echo $row["id"]; ?></a></td>		
									<td <? echo $weekand ; ?>><? echo $row["annee"]; ?></td>		
									<td <? echo $weekand ; ?>><? echo get_journal($row["journal"]); ?></td>		
									<td <? echo $weekand ; ?>><? echo get_mode_reglement($row["mode_reglement"]); ?></td>			
									<td <? echo $weekand ; ?>><? echo $row["ref"]; ?></td>		
									<td <? echo $weekand ; ?>><? echo compte($row["compte"]); ?></td>		
									<td <? echo $weekand ; ?>><? echo $row["libelle"]; ?></td>		
										
									<td <? echo $weekand ; ?>><? echo $ref_facture_achat." / ".$ref_facture_vente; ?></td>		
									<td <? echo $weekand ; ?>><? echo fournisseur($row["fournisseur"])." / ".client($row["client"]); ?></td>	
									<td <? echo $weekand ; ?> align="right"><b><? if($row["type"] == "in"){ echo number_format($row["montant"],3, '.',','); } ?></b></td>		
									<td <? echo $weekand ; ?> align="right"><b><? if($row["type"] == "out"){ echo '-'.number_format($row["montant"],3, '.',','); } ?></b></td>		
									</tr>
									
									<?
									
									
										// ============ Facture d'avoir =====
										if (empty($avoir_array[$row["ref_facture_achat"]])) {
											$avoir_array[$row["ref_facture_achat"]] = 0;
										}										
										
										if(($avoir_array[$row["ref_facture_achat"]] == 0)) {
											$query_avoir = "SELECT * FROM `pm_facture_avoir` where ref_bl =".$row["ref_facture_achat"];
											// echo " avoir ==>".$query_avoir."<br>";
											$result_avoir = @$db->query($query_avoir);
											if($result_avoir !== false){
												foreach($result_avoir as $row_avoir){
													$total += $row_avoir["total_ttc"];	
													if($row["type"] == "in"){
														$total_in += $row_avoir["total_ttc"];
													}else{
														$total_out += $row_avoir["total_ttc"];	
													}
													// $date_avoir = $avoir_array[$row["ref_facture_achat"]] ; 		
													$date_avoir = date( "d-m-Y", $row_avoir["date_avoir"]) ; 		
													echo "<tr style='background-color: #b4b7ff;'><td style='background-color: #b4b7ff;' align='center'><b>".$date_avoir."</b></td><td colspan='6' style='background-color: #b4b7ff;' align='center'><b>Facture d'avoir (Achat)</b> - ".$row_avoir["remarque"]."</td><td style='background-color: #b4b7ff;' colspan='2'>".$row_avoir["ref_avoir"]."</td><td style='background-color: #b4b7ff;' align='right'></td><td style='background-color: #b4b7ff;' align='right'>- ".number_format($row_avoir["total_ttc"],3, '.',' ')."</td></tr>";
											
												}
											}
											$avoir_array[$row["ref_facture_achat"]] ++; 
										}

//===================== New DEV
if($_GET["ref_fac_achat"] <> ""){
			$queryt = "SELECT * FROM `pm_transaction_product` WHERE id_transaction = ".$row["id"];
			// echo "<br>".$queryt;
			$resultt = @$db->query($queryt);
			if($resultt !== false){
				foreach($resultt as $row_avoiro){
											$query_avoir = "SELECT * FROM `pm_facture_avoir` where ref_bl =".$row_avoiro["ref_facture_achat2"];
											if($_GET["ref_fac_achat"] <> ""){
												$query_avoir .= " AND ref_bl = '".$_GET["ref_fac_achat"]."'";
											}
											// echo "<br>".$query_avoir;
											$result_avoir = @$db->query($query_avoir);
											if($result_avoir !== false){
												foreach($result_avoir as $row_avoir){
													$total += $row_avoir["total_ttc"];	
													if($row["type"] == "in"){
														$total_out += $row_avoir["total_ttc"];	
													}else{														
														// $total_in += $row_avoir["total_ttc"]*(-1);
													}
													// $date_avoir = $avoir_array[$row["ref_facture_achat"]] ; 		
													$date_avoir = date( "d-m-Y", $row_avoir["date_avoir"]) ; 		
													echo "<tr style='background-color: #b4b7ff;'><td style='background-color: #b4b7ff;' align='center'><b>".$date_avoir."</b></td><td colspan='6' style='background-color: #b4b7ff;' align='center'><b>Facture d'avoir (Achat)</b> - ".$row_avoir["remarque"]."</td><td style='background-color: #b4b7ff;' colspan='2'>".$row_avoir["ref_avoir"]."</td><td style='background-color: #b4b7ff;' align='right'></td><td style='background-color: #b4b7ff;' align='right'>- ".number_format($row_avoir["total_ttc"],3, '.',' ')."</td></tr>";
											
												}
											}
											$avoir_array[$row["ref_facture_achat"]] ++; 
										}
			}	
}			
//===============================



										
									?>
									<?
										// ============ Facture d'avoir vente =====
										if (empty($avoir_vente_array[$row["ref_facture_vente"]])) {
											$avoir_vente_array[$row["ref_facture_vente"]] = 0;
										}
										
										if($avoir_vente_array[$row["ref_facture_vente"]] == 0){
											$query_avoir2 = "SELECT * FROM `pm_facture_avoir_vente` where ref_facture =".$row["ref_facture_vente"];
											$result_avoir2 = @$db->query($query_avoir2);
											if($result_avoir2 !== false){
												foreach($result_avoir2 as $row_avoir2){
													$total += $row_avoir2["total_ttc"];	
													if($row["type"] == "in"){
														$total_in += $row_avoir2["total_ttc"];	
													}else{
														$total_out += $row_avoir["total_ttc"];	
													}
													$type = $row_avoir2["type"];
													switch ($type) {
														case "avoir":
															$type = "Facture d’avoir";
															break;
														case "retour":
															$type = "Facture de retour";
															break;
													}													
													$date_avoir2 = date( "d-m-Y", $row_avoir2["date_facture"]) ; 		
													echo "<tr style='background-color: #85dde4;'><td style='background-color: #85dde4;' align='center'><b>".$date_avoir2."</b></td><td colspan='6' style='background-color: #85dde4;' align='center'><b>".$type." (Vente)</b> - ".$row_avoir2["remarque"]."</td><td style='background-color: #85dde4;' colspan='2'>".$row_avoir2["idd"]."</td><td style='background-color: #85dde4;' align='right'>".number_format($row_avoir2["total_ttc"],3, '.',' ')."</td><td style='background-color: #85dde4;' align='right'></td></tr>";
											
												}
											}
											$avoir_vente_array[$row["ref_facture_vente"]] ++; 
										}										
									?>
									<?	
										if(($_GET["retenue"] == 1)&&($row["retenue_montant"] <> 0)){
											$total += $row["retenue_montant"];
											if($row["type"] == "in"){
												$total_in += $row["retenue_montant"];	
											}else{
												$total_out += $row["retenue_montant"];
											}
											$date_retenue = date( "d-m-Y", $row["add_date"]) ; 
											echo "<tr style='background-color: #fbe5e5;'><td style='background-color: #fbe5e5;' align='center'><b>".$date_retenue."</b></td><td colspan='6' style='background-color: #fbe5e5;' align='center'><b>Retenue à la source</b></td><td style='background-color: #fbe5e5;'>".ref_facture($row["ref_facture_achat"])."</td><td style='background-color: #fbe5e5;'>".fournisseur($row["fournisseur"])."</td><td style='background-color: #fbe5e5;' align='right'></td><td style='background-color: #fbe5e5;' align='right'>- ".$row["retenue_montant"]."</td></tr>";
										}
									
									$mois[$month] = 1;
										}
									}
									if($count == 0){
										echo "<tr><td colspan='11' align='center'>No data !</td></tr>";
									}else{
										echo "<tr><td colspan='10' align='right'><b>TOTAL</b></td><td align='right'><b>".number_format($total_in,3, '.',',')."</b></td><td align='right'><b>- ".number_format($total_out,3, '.',',')."</b></td></tr>";
										echo "<tr><td colspan='11' align='right' style='background-color: #feffc3;'><b>SOLDE</b></td><td align='center' colspan='2' style='background-color: #feffc3;'><b>".number_format($total_in-$total_out,3, '.',',')."</b></td></tr>";
									}	
									?>

										
                                    </tbody>
                                </table>
							
								
								
                </div>
					
                </div>
            </div>
        </div>
    </div>
</body>
</html>

<style>
@media print {
    #page-wrapper{
		padding : 10px;
	}
	.table-striped{
		font-size: 12px;
	}
	#wrapper {
		margin-left: 0px !important;
	}
	
}

</style>

<?php
$_SESSION['msg_error'] = "";
$_SESSION['msg_success'] = "";
$_SESSION['msg_notice'] = ""; ?>
