<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Europe/Madrid');

// Parámetros
$serverName 	= "localhost";
$connectionInfo = array( "Database"=>"ICF", "UID"=>"VAIO", "PWD"=>"VAIO");

// Conexión con Navision
$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"=>-1 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

$result		= [];

// Obtención de los traspasos de ALM01 Y ALM02 a EXPEND ALM
$sql = "SELECT 
		codigo_nuevo as code
		FROM [navisionChanges]
		WHERE entidad='TRANSFERBASE' 
		ORDER BY fecha asc";

$stmt_transfers = 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_transfers !== false) {
	while($row = sqlsrv_fetch_array($stmt_transfers, SQLSRV_FETCH_ASSOC)){		
		$transfer_base=[];
		foreach($row as $key=>$object){
			$transfer_base[$key]=!is_object($object)?utf8_encode($object):$object;
		}		
		$transfer_base['code'] = trim($transfer_base['code']);
		$result['N'.$transfer_base['code']]=[];
		$sql = 'SELECT 
				[Transfer-from code] as origin,
				[Transfer-to code] as destination
				FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Shipment Header]
				WHERE [Transfer Order No.]=\''.$transfer_base['code'].'\'';
		$stmt_transfer = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			echo json_encode(["result"=>-2, "text"=>"1 Ocurrio un error en la consulta SQL de Navision: Traspasos"]);
			die;
		}
		if( $stmt_transfer !== false) {
			while($row2 = sqlsrv_fetch_array( $stmt_transfer, SQLSRV_FETCH_ASSOC)){			
				$transfer=[];
				foreach($row2 as $key=>$object){
					$transfer[$key]=!is_object($object)?utf8_encode($object):$object;
				}			
				$result['N'.$transfer_base['code']]['header']=$transfer;
				$result['N'.$transfer_base['code']]['lines']=[];
				$sql = 'SELECT 
						[Item No.] as product,
						[Variant Code] as variant,
						CAST([Quantity (Base)] AS INT) as quantity
						FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Shipment Line]
						WHERE [Transfer Order No.]=\''.$transfer_base['code'].'\'';
				$stmt_transferline = sqlsrv_query( $connNavision, $sql);
				if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
					echo json_encode(["result"=>-3, "text"=>"1 Ocurrio un error en la consulta SQL de Navision: Lineas de Traspasos"]);
					die;
				}
				if( $stmt_transferline !== false) {
					while($row3 = sqlsrv_fetch_array( $stmt_transferline, SQLSRV_FETCH_ASSOC)){			
						$transferline=[];
						foreach($row3 as $key=>$object){
							$transferline[$key]=!is_object($object)?utf8_encode($object):$object;
						}
						$result['N'.$transfer_base['code']]['lines'][] = $transferline;
					}
				}					
			}
		}				
	}
}

header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);
?>
