<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");
header('Content-Type: application/json; charset=utf-8');

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

$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 json_encode(["result"=>-11 , "text"=>"No se pudo conectar con Navision"]);
		 die;
	}
	
	$changedStocks=[];
	$sql='SELECT codigo_antiguo FROM dbo.[NavisionChanges] nc WHERE nc.[entidad] = \'STOCK\' ORDER BY fecha ASC';
	$stmt = sqlsrv_query( $connNavision, $sql );
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		echo json_encode(["result"=>-12, "text"=>"1 Ocurrio un error en la consulta SQL de Navision"]);
		die;
	}
	if( $stmt !== false) {
		while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
			$changedStocks[]=utf8_encode($row["codigo_antiguo"]);
			$sql='DELETE FROM dbo.[NavisionChanges] WHERE [entidad] = \'STOCK\' AND [codigo_antiguo]=\''.$row["codigo_antiguo"].'\'';
			$stmtDelete = sqlsrv_query( $connNavision, $sql );
		}
	}
	
	$sql='SELECT
		itl.[Item No.] code,
		itl.[Variant Code] variant,
		ISNULL(SUM(CASE WHEN itl.[Location Code] = \'ALM01\' THEN itl.Quantity ELSE 0 END), 0) AS ALM01,
		ISNULL(SUM(CASE WHEN itl.[Location Code] = \'ALM02\' THEN itl.Quantity ELSE 0 END), 0) AS ALM02
			FROM
			dbo.[Item Ledger Entry] itl
			WHERE
			itl.[Item No.] IN (\'' . implode("','", $changedStocks) . '\')
			AND itl.[Location Code] IN (\'ALM01\', \'ALM02\')
			GROUP BY itl.[Item No.], itl.[Variant Code];';
			
	
	$stmt = sqlsrv_query( $connNavision, $sql );
	$quantitys = [];
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
            echo json_encode(["result"=>-12, "text"=>"1 Ocurrio un error en la consulta SQL de Navision"]);
			die;
		}
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				$item["code"]=utf8_encode($row["code"]);
				$item["variant"]=utf8_encode($row["variant"]);
				$item["ALM01"]=($row["ALM01"])*1;
				$item["ALM02"]=($row["ALM02"])*1;
				$quantitys[]=$item;
			}
		}
	if(count($quantitys)){
		echo json_encode(["result"=>1, "data"=>$quantitys]);
	}else echo json_encode(["result"=>-2, "text"=>"No se encontro la referencia"]);
?>

