<?php

include ('../inc/includes.php');
include ('../inc/utils.class.php');

function NonImmodepot() {

	global $DB;
	
	$query = "SELECT DEP_CODE from `Depot` where immobilisation = 0";
	$result = $DB->query($query);
	$count = $result->num_rows;
	$depots = "(";
	$i = 0;
	if ($count > 0) {	 
		while($row = $result->fetch_assoc()) {
			$depots = $depots."'".$row['DEP_CODE']."'";
			if($i < $count-1)
				$depots = $depots.",";
			$i++;
		}
	}
	$depots = $depots.")";
	return $depots;
}

function getArticles($all){
		
	global $DB;	
	   
	$query = "SELECT p.`ART_CODE`, `ART_LIB`, `ART_LIBC`, `ART_UB_STK`, `SFA_LIB`, `FAR_LIB`, `imgpath`, stk.`DEP_CODE`,
	stk.`STK_NUMLOT`, stk.`STK_REEL` 
	FROM `Produit` p 
			JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE`
			JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = p.`SFA_CODE`
			JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE`
			WHERE stk.`DEP_CODE` in ".NonImmodepot();
	if($all != 1)
		$query = $query." and STK_REEL > 0 ";

	$query = $query."GROUP by p.`ART_CODE`";
//echo $query; die;

	$result = $DB->query($query);
	
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}

function getArticlesWithStock() {
		
	global $DB;	
	   
	$query = "SELECT p.`ART_CODE`, `ART_LIB`, `ART_LIBC`, `ART_UB_STK`, `SFA_LIB`, `FAR_LIB`, stk.`STK_NUMLOT`, stk.`STK_INV`, stk.`DEP_CODE` FROM `Produit` p 
			JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE`
			JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = p.`SFA_CODE`
			JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE`
			Where stk.`STK_REEL` > 0
			GROUP by p.`ART_CODE`";
	$result = $DB->query($query);
	
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			if(getArticlInInventaire($row['DEP_CODE'], $row['ART_CODE'], $row['STK_NUMLOT']) == 0)
				$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}

function getArticlInInventaire($depcode, $artcode, $stklot) {

	global $DB;

	$query = "SELECT `DEP_CODE`, `ART_CODE`, `STK_NUMLOT`
			FROM `Inventaire` Where `DEP_CODE` ='".$depcode."' and `ART_CODE` ='".$artcode."' and `STK_NUMLOT` ='".$stklot."'";

//echo $query;
	$result = $DB->query($query);
	/*$rows = array();
	if ($result->num_rows > 0) {	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
	}
	return $rows;*/
	if ($result->num_rows > 0)
		return 1;
	return 0;
}


function getStocks($all) {
		
	global $DB;	
	   
	$query = "SELECT D.`DEP_NOM`, stk.`ART_CODE`, `ART_LIB`, `STK_NUMLOT`, `STK_INV`, `STK_DTINV`, `STK_ENTREE`, `STK_SORTIE`,
	`STK_REEL`, stk.`DEP_CODE`, FP.`FAR_LIB`, stk.`STK_RESPONSABLE` 
	        FROM `Stock2` stk 
			JOIN `Produit` P on P.ART_CODE = stk.`ART_CODE`
			JOIN `Fam_Produit` FP on FP.FAR_CODE = P.`FAR_CODE`
			JOIN `Depot` D on D.DEP_CODE = stk.`DEP_CODE`";
	if($all != 1)
		$query = $query." where  STK_REEL > 0 ";
//echo $query; 
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		} 
		//print_r($rows) ;die; 
		return $rows;
	}
		  
	return null;	
}

function getDepots() {
		
	global $DB;	
	   
	$query = "SELECT * FROM `Depot`";
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}


function getInventaire() {

	global $DB;

	$query = "SELECT inv.`id`, inv.`DEP_CODE`, inv.`ART_CODE`, P.`ART_LIB`, dep.`DEP_NOM`, FP.`FAR_LIB`, `SFA_LIB`, inv.`STK_NUMLOT`, `STK_REEL` as Qt_reel, `Qt_compt1`, `usercomp1`, `datecomp1`, `Emplacement`, `Qt_compt2`, `usercomp2`, `datecomp2`, `Qt_controle`, `usercontrole`,`datecontrole` 
	    FROM `Inventaire` inv
		JOIN `Produit` P on P.ART_CODE = inv.`ART_CODE`
		LEFT JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = P.`SFA_CODE`
		LEFT JOIN `Fam_Produit` FP on FP.FAR_CODE = P.`FAR_CODE`
		LEFT JOIN `Depot` dep on dep.DEP_CODE = inv.`DEP_CODE`
		LEFT JOIN `Stock2` stk on inv.`ART_CODE` = stk.`ART_CODE` and inv.`DEP_CODE` = stk.`DEP_CODE` and inv.`STK_NUMLOT` = stk.`STK_NUMLOT`
		";

	$result = $DB->query($query);
	$rows = array();
	if ($result->num_rows > 0) {	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
	}
	return $rows;
}

function getInventaireId($idinv) {

	if(isInjection($idinv)) {
		echo "Not Allowed";
		return null;
	}

	global $DB;

	$query = "SELECT * FROM `Inventaire` inv
		Where id = ".$idinv;
	$result = $DB->query($query);
	$rows = array();
	if ($result->num_rows > 0) {	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
	}
	return $rows[0];
}

function updateInventaire($invid, $qtcompt1, $qtcompt2, $qtcontrole) {

	global $DB;

	$query = "UPDATE `Inventaire` SET `Qt_compt1`='".$qtcompt1."', `Qt_compt2`='".$qtcompt2."', `Qt_controle`='".$qtcontrole."' where `id`='".$invid."'";
	$result = $DB->query($query);
}

/*
function saveInventaire($codedepot, $codearticle, $numlot, $emplacement, $qtreel, $qtcompt1, $qtcompt2, 
$qtcontrole, $usercomp1, $datecomp1, $usercomp2, $datecomp2, $usercontrole, $datecontrole) {

	global $DB;

	$query = "SELECT `id` FROM `Inventaire` WHERE `DEP_CODE` = '".$codedepot."' 
	and `ART_CODE` = '".$codearticle."' and `STK_NUMLOT` = '".$numlot."'  and (annee  > 2021 or annee is null)" ;// and FORMAT(`Qt_reel`,2) = FORMAT('".$qtreel."',2)
	$result = $DB->query($query);
	$rows = array();
	if ($result->num_rows > 0) {	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
	}


    if(count($rows) > 0){
		$inventaire = $rows[0];
	}else{
		$inventaire = null;
		};
	 
	if($qtcompt1 != '' && count($rows) < 1)
		$query = "INSERT INTO `Inventaire`(`DEP_CODE`, `ART_CODE`, `STK_NUMLOT`, `Qt_reel`, `Qt_compt1`, 
	`usercomp1`, `datecomp1`, `Emplacement`) VALUES ('".$codedepot."','".$codearticle."','".$numlot."','".$qtreel."',
	'".$qtcompt1."','".$usercomp1."','".$datecomp1."','')";
	else {
		if(count($rows) > 0 && $inventaire['id'] != "") {
		$query = "";
		if($qtcompt2 != '')
			$query = "UPDATE `Inventaire` SET `Qt_compt2`='".$qtcompt2."',`usercomp2`='".$usercomp2."',`datecomp2`='".$datecomp2."' where `id`=".$inventaire['id'];
		else if ($qtcontrole != '')
			$query = "UPDATE `Inventaire` SET `Qt_controle`='".$qtcontrole."',`usercontrole`='".$usercontrole."',`datecontrole`='".$datecontrole."' where `id`=".$inventaire['id'];
		else if($qtcompt1 != '')
			$query = "UPDATE `Inventaire` SET `Qt_compt1`='".$qtcompt1."',usercomp1='".$usercomp1."',`datecomp1`='".$datecomp1."' where `id`=".$inventaire['id'];
		}
	}
	//echo $query; die;
	$result = $DB->query($query);
}

function getFamille(){
	global $DB;	
	   
	$query = "SELECT FAR_LIB FROM `Produit` p JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE` JOIN `Sous_Fam_Produit` SFP on SFP.SFA_CODE = p.`SFA_CODE` JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE` GROUP BY FAR_LIB";
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}
*/

function saveInventaire($codedepot, $codearticle, $numlot, $emplacement, $qtreel, $qtcompt1, $qtcompt2, 
$qtcontrole, $usercomp1, $datecomp1, $usercomp2, $datecomp2, $usercontrole, $datecontrole) {

	global $DB;

	$query = "SELECT `id` FROM `Inventaire` WHERE `DEP_CODE` = '".$codedepot."' 
	and `ART_CODE` = '".$codearticle."' and `STK_NUMLOT` = '".$numlot."'" ;// and FORMAT(`Qt_reel`,2) = FORMAT('".$qtreel."',2)
	$result = $DB->query($query);
	$rows = array();
	if ($result->num_rows > 0) {	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
	}


    if(count($rows) > 0){
		$inventaire = $rows[0];
	}else{
		$inventaire = null;
		};
	 
	if(count($rows) < 1){
		if($qtcompt2 != ''){
		$query = "INSERT INTO `Inventaire`(`DEP_CODE`, `ART_CODE`, `STK_NUMLOT`, `Qt_reel`, `Qt_compt2`, 
		`usercomp2`, `datecomp2`, `Emplacement`) VALUES ('".$codedepot."','".$codearticle."','".$numlot."','".$qtreel."',
		'".$qtcompt2."','".$usercomp2."','".$datecomp2."','')";
		}
		if($qtcompt1 != ''){
		$query = "INSERT INTO `Inventaire`(`DEP_CODE`, `ART_CODE`, `STK_NUMLOT`, `Qt_reel`, `Qt_compt1`, 
		`usercomp1`, `datecomp1`, `Emplacement`) VALUES ('".$codedepot."','".$codearticle."','".$numlot."','".$qtreel."',
		'".$qtcompt1."','".$usercomp1."','".$datecomp1."','')";
		}
	}else {
		if(count($rows) > 0 && $inventaire['id'] != "") {
		$query = "";
		if($qtcompt2 != '')
			$query = "UPDATE `Inventaire` SET `Qt_compt2`= `Qt_compt2` + '".$qtcompt2."',`usercomp2`='".$usercomp2."',`datecomp2`='".$datecomp2."' where `id`=".$inventaire['id'];
		else if ($qtcontrole != '')
			$query = "UPDATE `Inventaire` SET `Qt_controle`= `Qt_controle` + '".$qtcontrole."',`usercontrole`='".$usercontrole."',`datecontrole`='".$datecontrole."' where `id`=".$inventaire['id'];
		else if($qtcompt1 != '')
			$query = "UPDATE `Inventaire` SET `Qt_compt1`= `Qt_compt1` + '".$qtcompt1."',usercomp1='".$usercomp1."',`datecomp1`='".$datecomp1."' where `id`=".$inventaire['id'];
		}
		
	}
	$result = $DB->query($query);
}

function getSFamille(){
	global $DB;	
	   
	$query = "SELECT SFA_LIB FROM `Produit` p JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE` JOIN `Sous_Fam_Produit` SFP on SFP.SFA_CODE = p.`SFA_CODE` JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE` GROUP BY SFA_LIB";
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}

function getZONES(){
	global $DB;	
	   
	$query = "SELECT * FROM `Zones`";
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}

function getArticlesOf($fam){

	global $DB;	
	  
	if($fam == "all"){
		$query = "SELECT p.`ART_CODE`, `ART_LIB` FROM `Produit` p 
		JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE`
		JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = p.`SFA_CODE`
		JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE`";
	}else{
		$query = "SELECT p.`ART_CODE`, `ART_LIB` FROM `Produit` p 
		JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE`
		JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = p.`SFA_CODE`
		JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE`
		WHERE SFP.`SFA_CODE` = \"$fam\" GROUP by p.`ART_CODE`";
	}
	
	$result = $DB->query($query);

	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}

		return $rows;
	}
		   
	return null;	
}

function getDepotsMap() {
		
	global $DB;	
	   
	$query = "SELECT * FROM `Depot`";
	$result = $DB->query($query);
		  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[$row['DEP_CODE']] = $row['DEP_NOM'];
		}
		return $rows;
	}
		   
	return null;	
}

function getTypeArt(){
	
	global $DB;

	$query = "SELECT `TYPE_CODE`, `TYPE_NOM` FROM `typearticle`";
	$result = $DB->query($query);

	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return null;	
}

function getWUnit(){

	global $DB;

	$query = "SELECT `ART_UB_STK` UN FROM `Produit` WHERE ART_UB_STK != 'NULL' GROUP BY ART_UB_STK";
	$result = $DB->query($query);

	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
	return null;	
}

function saveProduct($codedepot,$codearticle,$fam,$sfam,$zone,$lib,$responsable,$unite) {

	global $DB;

	$query = "INSERT INTO `stock2` (`DEP_CODE`, `ART_CODE`, `STK_NUMLOT`, `STK_INV`, `STK_DTINV`, `STK_ENTREE`, `STK_SORTIE`, `STK_REEL`) VALUES ('".$codedepot."', '".$codearticle."', '', '0', '".date("Y-m-d")."', '0', '0', '0') ";
	
	$DB->query($query);

	$query = "INSERT INTO `Produit`(`DEPOT`, `ART_CODE`, `FAR_CODE`, `SFA_CODE`, `ZONE`, `ART_LIB`, `Responsable`, `ART_UB_STK`) VALUES ('".$codedepot."','".$codearticle."','".$fam."','".$sfam."','".$zone."','".$lib."','".$responsable."','".$unite."')";
	
	$DB->query($query);
	
}

function getCount($term){
	global $DB;

	$query = "SELECT count(*) c FROM `Produit` WHERE `ART_CODE` LIKE '%".$term."%'";
	$result = $DB->query($query);

	if ($result->num_rows > 0) {
		return $result->fetch_assoc();
	}
	return null;	
}

function getArticlesByStock($Stock){
		
	global $DB;	
	$all = 1; 
	$query = "SELECT p.`ART_CODE`, `ART_LIB`
	FROM `Produit` p 
			JOIN `Stock2` stk on p.`ART_CODE` = stk.`ART_CODE`
			JOIN `Sous_Fam_Produit` SFP on SFP.`SFA_CODE` = p.`SFA_CODE`
			JOIN `Fam_Produit` FP on FP.FAR_CODE = p.`FAR_CODE`
			WHERE stk.`DEP_CODE`  = '".$Stock."' 
			and (stk.annee  > 2021 or stk.annee is null)";
	if($all != 1)
		$query = $query." and STK_REEL > 0 ";

	$query = $query."GROUP by p.`ART_CODE`";

	$result = $DB->query($query);
	
	$rows = array();	  
	if ($result->num_rows > 0) {
			 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows;
	}
		   
	return $rows;	
}