<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");
if(isset($_REQUEST["from"])) //Formato linux timestamp
	$from=$_REQUEST["from"]; else $from=0;
//$from=date('d/m/Y', $from);	

$discordchannelerror="883046233017552956"; // Errores	

$aBuyOrders=[];


$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{
	  file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannelerror."&msg=".urlencode(":sos:"."SCRIPT ".basename(__FILE__, '.php').": No se pudo establecer la conexion con Navision"));
	  echo json_encode(["result"=>-11 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

		
		
		
$sql = 'SELECT 
		ph.[No.] ph_code,
		SUM(pl.[Amount]) as pl_importe,
		ph.[Buy-from Vendor No.] as v_code,
		CONCAT(ph.[Buy-from Vendor Name], ph.[Buy-from Vendor Name 2]) as v_name,
		v.[Pedido minimo] as v_minimo,
		v.[Portes pagados desde] as v_portes
				
		FROM [Ind. Campollano Ferretería S.L$Purchase Header] ph 
		LEFT JOIN [Ind. Campollano Ferretería S.L$Purchase Line] pl ON pl.[Document No.]=ph.[No.]
		LEFT JOIN [Ind. Campollano Ferretería S.L$Vendor] v ON v.[No.]=ph.[Buy-from Vendor No.]
		WHERE 
		(v.[Pedido minimo] > 0 OR v.[Portes pagados desde] >0) AND
		SUBSTRING(ph.[No.], 3, 2) = \'PC\' AND 
		ph.[Status]=0 AND 
		ph.[Historic]=0 AND 
		ph.[Es Devolucion]=0 AND 
		ph.[Document Date] >= DATEADD(MONTH, -2, GETDATE())
		GROUP BY 
		ph.[No.],
		ph.[Buy-from Vendor No.],
		CONCAT(ph.[Buy-from Vendor Name], ph.[Buy-from Vendor Name 2]),
		v.[Pedido minimo],
		v.[Portes pagados desde]
		ORDER BY ph.[No.] ASC';

$stmt = sqlsrv_query( $connNavision, $sql );
if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
	echo json_encode(["result"=>-1, "text"=>"1 Ocurrio un error en la consulta SQL de Navision"]);
	file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannelerror."&msg=".urlencode(":sos:"."SCRIPT ".basename(__FILE__, '.php').": No se pudo obtener la información de los clientes"));
	die;
}
if( $stmt !== false) {
	while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		$aBuyOrder=[];
		foreach($row as $key=>$object){
			$aBuyOrder[$key]=!is_object($object)?utf8_encode($object):$object;					
		}
		$aBuyOrders[]=$aBuyOrder;
	}
}
		

header('Content-Type: application/json');
$result = [];
$result['result'] = 1;
$result['text'] = 'Pedidos de compra, para lanzar, obtenidos correctamente';
$result['data'] = $aBuyOrders;
echo json_encode($result, JSON_UNESCAPED_UNICODE);
?>
