<?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["start"])) $start=date_create_from_format('Y-m-d',$_REQUEST["start"]); else $start=date_create_from_format('Y-m-d','1900-01-01');
if(isset($_REQUEST["end"])) $end=date_create_from_format('Y-m-d',$_REQUEST["end"]); else $end=date_create_from_format('Y-m-d','2999-12-31');
if(isset($_REQUEST["numfact"])) $numfact=$_REQUEST["numfact"]; else $numfact='';
if(isset($_REQUEST["cif"])) $cif=$_REQUEST["cif"]; else $cif='';
if(isset($_REQUEST["customer"])) $customer=$_REQUEST["customer"]; else $customer='';
	
$resultInvoices=[];
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", "ConnectionPooling" => "1", "MultipleActiveResultSets"=>'0');


$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 "Conexion no se pudo establecer con Navision.\r\n";
		 die( print_r( sqlsrv_errors(), true));
	}

		$temp_invoice=[];
		
		$resultInvoices=[];
		
		   
		$sql = 'SELECT 
		 h.[No.] id,
		 h.[Bill-to Customer No.] Code,
		 
		 h.[Bill-to Name] customer,
		 CONVERT(varchar, [Document Date], 103) date,
		 cast(SUM(l.[Amount Including VAT])as decimal(10,2)) AS Total
		 FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Invoice Header] h
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Invoice Line] l ON l.[Document No.]=h.[No.]		
		 WHERE [Document Date]>=\''.$start->format('d/m/Y').'\' AND [Document Date]<= \''.$end->format('d/m/Y').'\' 
			AND h.[No.] LIKE \'%'.$numfact.'%\' AND h.[VAT Registration No.] LIKE \'%'.$cif.'%\' AND h.[Bill-to Name] LIKE \'%'.$customer.'%\'
		 GROUP BY  h.[No.], h.[Bill-to Customer No.], h.[Bill-to Name], h.[Bill-to Address], h.[Bill-to Post Code], h.[Bill-to City], h.[Bill-to County],
		 			 h.[VAT Registration No.], h.[Document Date] ORDER BY [Document Date] DESC';
		
		//echo $sql;
		//Invoice Header 
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				foreach($row as $key=>$item){
					$temp_invoice[$key]=!is_object($item)?utf8_encode($item):$item;
				}
				$resultInvoices[]=$temp_invoice;
			}
			
		}
		   
		   
		
		
		//var_dump($resultInvoices);
		//$resultInvoices[$invoice]=$temp_invoice;
	
header('Content-Type: application/json');
echo json_encode($resultInvoices, JSON_PRETTY_PRINT);
?>
