<?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');
if(isset($_REQUEST["code"])) 
	$code=$_REQUEST["code"]; 
else{
	echo json_encode(["result"=>-1 , "text"=>"Se debe proporcionar un codigo de producto"]);
	die;
}

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;
	}
	$quantitys=[];
	$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)+
			ISNULL(SUM(CASE WHEN itl.[Location Code] = \'TRANSITO\' AND tsl.[Transfer-from Code]=\'ALM01\' AND tsl.[Transfer-to Code]=\'ALM02\'  THEN itl.Quantity ELSE 0 END), 0) AS ALM01,			
			ISNULL(SUM(CASE WHEN itl.[Location Code] = \'ALM02\' THEN itl.Quantity ELSE 0 END), 0)+
			ISNULL(SUM(CASE WHEN itl.[Location Code] = \'TRANSITO\' AND tsl.[Transfer-from Code]=\'ALM02\' AND tsl.[Transfer-to Code]=\'ALM01\' THEN itl.Quantity ELSE 0 END), 0) AS ALM02
			FROM
			dbo.[Item Ledger Entry] itl	
			LEFT JOIN dbo.[Ind. Campollano Ferreterķa S.L$Transfer Shipment Line] tsl on tsl.[Transfer Order No.]=itl.[Transfer Order No.] and tsl.[Item No.]=itl.[Item No.] and tsl.[Variant Code]=itl.[Variant Code] 				
			WHERE
			itl.[Item No.] = \''.$code.'\'
			AND itl.[Location Code] IN (\'ALM01\', \'ALM02\',\'TRANSITO\')
			GROUP BY itl.[Item No.], itl.[Variant Code];';

	$stmt = sqlsrv_query( $connNavision, $sql );
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
		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)){
			$temp=[];
			foreach($row as $key=>$object){
				$temp_object[$key]=!is_object($object)?utf8_encode($object):$object;		
				
			}
			$quantitys[]=$temp_object;
		}
	}
		
	if(!count($quantitys)){
		$quantitys[]=["code"=>$code, "variant"=>"", "ALM01"=>0, "ALM02"=>0];
	}
	echo json_encode(["result"=>1, "data"=>$quantitys]);
?>

