<?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 las ordenes de ventas
$from = 999999999999;
if(isset($_REQUEST["from"])) 
	$from=$_REQUEST["from"]; 

$sql = 'UPDATE dbo.[Ind. Campollano Ferretería S.L$Sales Header] SET [Status]=1, [Estado Oferta]=0 WHERE CAST(TIMESTAMP AS BIGINT) > '.$from.' AND [Status]=0 AND [Pedido WEB]=1 AND [Document Type]=1 AND [Sales type]=0 AND [Sales document type]=0 AND [Es Devolucion]=0 AND [Estado pedido]<>3 AND [BloqueadoWeb]=0';
$stmt_launched_order = sqlsrv_query( $connNavision, $sql);
if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
	echo json_encode(["result"=>-13, "text"=>"Error al lanzar los pedidos web"]);
	die;
}

// Fecha último registro
$maxtimestamp=0;
$sql = 'SELECT CAST(MAX(TIMESTAMP) AS BIGINT) timestamp FROM dbo.[Ind. Campollano Ferretería S.L$Sales Header]';
$stmt = sqlsrv_query( $connNavision, $sql );
if( $stmt !== false) {
	if($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		$maxtimestamp=$row['timestamp']*1;
	}
}
$sql = 'SELECT CAST(MAX(TIMESTAMP) AS BIGINT) timestamp FROM dbo.[Ind. Campollano Ferretería S.L$Sales Line]';
$stmt = sqlsrv_query( $connNavision, $sql );
if( $stmt !== false) {
	if($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		if ($maxtimestamp<($row['timestamp']*1))		
			$maxtimestamp=$row['timestamp']*1;
	}
}

$sql = 'SELECT DISTINCT p.code AS code from	
		(SELECT sh.[No.] as code
		FROM dbo.[Ind. Campollano Ferretería S.L$Sales Header] sh
		WHERE ISNULL(sh.[No.], \'\') NOT LIKE \'%PVM%\' AND CAST(sh.TIMESTAMP AS BIGINT) > '.$from.' AND sh.[Order Date] >= DATEADD(MONTH, -3, GETDATE()) AND sh.[Status]=1 AND sh.[Document Type]=1 AND sh.[Sales type]=0 AND sh.[Sales document type]=0 AND sh.[Es Devolucion]=0 AND (sh.[BloqueadoWeb]=0 or sh.[Historic]=1)
		UNION
		SELECT sh2.[No.] as code
		FROM dbo.[Ind. Campollano Ferretería S.L$Sales Line] sl
		LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Sales Header] sh2 on sh2.[No.]=sl.[Document No.]
		WHERE ISNULL(sh2.[No.], \'\') NOT LIKE \'%PVM%\' AND CAST(sl.TIMESTAMP AS BIGINT) > '.$from.' AND sh2.[Order Date] >= DATEADD(MONTH, -3, GETDATE()) AND sh2.[Status]=1 AND sh2.[Document Type]=1 AND sh2.[Sales type]=0 AND sh2.[Sales document type]=0 AND sh2.[Es Devolucion]=0 AND (sh2.[BloqueadoWeb]=0 or sh2.[Historic]=1)) p		
		ORDER BY p.code asc';

$stmt_sales = 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_sales !== false) {
	while($row = sqlsrv_fetch_array($stmt_sales, SQLSRV_FETCH_ASSOC)){		
		foreach($row as $key=>$object){
			$result[]=!is_object($object)?utf8_encode($object):$object;
		}						
	}
}

$results =[];
$results['maxtimestamp'] = $maxtimestamp;
$results['class'] = $result;
header('Content-Type: application/json');
echo json_encode($results, JSON_UNESCAPED_UNICODE);
?>
