<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');
include("../conexion.php");
$discordchannel="935430617842196601";

function obtenerIDProducto($sku, $conn){
	$stmt = $conn->prepare("SELECT id_product FROM fcproduct WHERE reference='$sku'");
	$stmt->execute();
	$result = $stmt->get_result();
	if($row_m = $result->fetch_assoc()) return $row_m['id_product'];
		else return -1;
}


function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str= stripslashes($str);
    }
    return str_replace("'", "''", $str);
}


$connPrestashop = new mysqli($bdd_servidor, $bdd_usuario, $bdd_contrasena, $bdd_base_datos);
	if( $connPrestashop ) {
			 echo "Conexion establecida con Prestashop <br>";
	}else{
			 echo "Conexion no se pudo establecer con Prestashop.<br>";
			 die( print_r( sqlsrv_errors(), true));
}
$serverName = "localhost";
$connectionInfo = array( "Database"=>"ICF", "UID"=>"VAIO", "PWD"=>"VAIO");

$connNavision=false;
$retries=0;
while(!$connNavision && $retries<3){
	$connNavision = sqlsrv_connect( $serverName, $connectionInfo);
	$retries++;
}


	if( $connNavision ) {
		 //echo "Conexion establecida con Navision.\r\n";
	}else{
		 //echo "Conexion no se pudo establecer con Navision.\r\n";
		 die( print_r( sqlsrv_errors(), true));
	}

		$arrayProductosPrestashop=array();
		
	
		$sql = "SELECT p.id_product, p.reference
				FROM fcproduct p
				LEFT JOIN fcstock_available s ON (s.id_product=p.id_product)
				WHERE p.outlet=1 AND s.quantity<=0 AND p.active=1";
	  
		$stmtPrestashop = $connPrestashop->prepare($sql);
		$stmtPrestashop->execute();
		$resultPrestashop = $stmtPrestashop->get_result();
		while($rowPrestashop = $resultPrestashop->fetch_assoc()){
			$arrayProductosPrestashop[$rowPrestashop["reference"]]=$rowPrestashop;
		}
		
		$cont=0;
		foreach($arrayProductosPrestashop as $productoPrestashop) {
			
			/*if($productoPrestashop['reference']=="1362973")
			{*/
				$sku=($productoPrestashop['reference']);
				//echo $sku."<br>";
				
				$sql = "SELECT * FROM fcproduct_attribute WHERE id_product='".$productoPrestashop['id_product']."'";
	  
				$stmt = $connPrestashop->prepare($sql);
				$stmt->execute();
				$result = $stmt->get_result();
							
				//ARTICULO CON VARIANTES
				if($result->fetch_assoc())
				{
				//	echo "- ".$sku."articulo con variante<br>";				
					$sql2 = "SELECT * FROM fcstock_available WHERE id_product='".$productoPrestashop['id_product']."' AND id_product_attribute!=0 AND quantity>0";
	  
					$stmt2 = $connPrestashop->prepare($sql2);
					$stmt2->execute();
					$result2 = $stmt2->get_result();
								
					//NO QUEDA STOCK EN NINGUNA VARIANTE
					if($result2->fetch_assoc()==false){
						
						//echo "no queda stock en ninguna variante<br><br>";
						
						$sql = "UPDATE `fcproduct_shop` SET active=0 WHERE id_product=".$productoPrestashop['id_product'].";";
						$stmtPrestashop = $connPrestashop->prepare($sql);
						$stmtPrestashop->execute();		

										
						$sql = "UPDATE `fcproduct` SET active=0 WHERE id_product=".$productoPrestashop['id_product'].";";
						$stmtPrestashop = $connPrestashop->prepare($sql);
						$stmtPrestashop->execute();	
						
						$sql="UPDATE dbo.[Item] SET [ProductoWEB]=0,[Desactivado WEB]=1 WHERE [No.]='".$sku."'";
					
							

						$sql="UPDATE dbo.[Item] SET [ProductoWEB]=0,[IncidenciaWEB]=1 WHERE [No.]='".$sku."'";	
						$stmt = sqlsrv_query( $connNavision, $sql );
						if( $stmt === false) {
								die( print_r( sqlsrv_errors(), true) );
						}
						
						
					
					}
					/*
					else{
						
						echo "todavia queda stock en alguna variante<br><br>";
					
					}*/
					
				
				}
				
				//ARTICULO SIN VARIANTES
				else
				{
					//echo "- ".$sku."articulo sin variante<br>";			
						
					$sql = "UPDATE `fcproduct_shop` SET active=0 WHERE id_product=".$productoPrestashop['id_product'].";";
					$stmtPrestashop = $connPrestashop->prepare($sql);
					$stmtPrestashop->execute();		

									
					$sql = "UPDATE `fcproduct` SET active=0 WHERE id_product=".$productoPrestashop['id_product'].";";
					$stmtPrestashop = $connPrestashop->prepare($sql);
					$stmtPrestashop->execute();	
					
					$sql="UPDATE dbo.[Item] SET [ProductoWEB]=0,[Desactivado WEB]=1 WHERE [No.]='".$sku."'";
				
						

					$sql="UPDATE dbo.[Item] SET [ProductoWEB]=0,[IncidenciaWEB]=1 WHERE [No.]='".$sku."'";	
					$stmt = sqlsrv_query( $connNavision, $sql );
					if( $stmt === false) {
							die( print_r( sqlsrv_errors(), true) );
					}
					
					
				}
				
			/*}*/
			$cont++;
		
		}

		if($cont>0) file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode(":warning:"."SCRIPT ".basename(__FILE__, '.php').": Se han desactivado ".$cont." productos en PS que estaban descatalogados y sin stock"));

		$connPrestashop->close();
		echo " \r\n \r\Finalizado \r\n"; 
?>
