<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
//ini_set('memory_limit', '2048M');
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");
if(isset($_REQUEST["code"])) $code=$_REQUEST["code"]; else $code='';
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');


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

		
		$resultInvoices=[];		
		$sql = 'SELECT
			h.[No.] id,
			h.[Bill-to Customer No.] code,
			CONVERT(varchar, h.[Document Date], 103) date,
			CONVERT(varchar, h.[Due Date], 103) due_date,
			cast(SUM(l.[Amount Including VAT])as decimal(10,2)) AS total
			FROM dbo.[Ind. Campollano Ferreterķa S.L$Sales Invoice Header] h
			LEFT JOIN dbo.[Ind. Campollano Ferreterķa S.L$Sales Invoice Line] l
			ON l.[Document No.]=h.[No.]
			WHERE h.[Bill-to Customer No.]=\''.$code.'\' AND h.[Payment Method Code]!=\'EFECTIVO\' AND h.[Payment Method Code]!=\'CCM\' AND h.[Payment Method Code]!=\'SABADELL\' AND h.[Document date]>=\''.$start->format('d/m/Y').'\' AND h.[Document date]<=\''.$end->format('d/m/Y').'\'
			GROUP BY h.[No.], h.[Bill-to Customer No.], h.[Document date], h.[Due date]
			ORDER BY [Document Date] DESC';
	/*	
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
					$invoices[]=["id"=>$row["id"], "date"=>$row["date"], "due_date"=> $row["due_date"], "total"=> $row["total"]];
			}
		}
		*/
		
		$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;
			}
			
		}
header('Content-Type: application/json');
echo json_encode($resultInvoices, JSON_PRETTY_PRINT);

?>
