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

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"=>'1');

$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));
	}

		$objects=[];
		$collection=[];
		$maxtimestamp=0;
		//GET MAX TIMESTAMP
		/*$maxtimestamp=0;
		$sql = 'SELECT CAST(MAX(TIMESTAMP) AS BIGINT) timestamp FROM [Ind. Campollano Ferreterķa S.L$Purchase Header]';
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			if($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				$maxtimestamp=$row['timestamp'];
			}
		}*/
		
		$sql = 'SELECT CAST(h.TIMESTAMP AS BIGINT) timestamp, h.[No.] code, h.[Buy-from Vendor No.] supplier,
		 h.[Vendor Shipment No.] supplierdeliverycode,
		 userauthor.[E-Mail] author,
		 usersaleperson.[E-Mail] agent,
		 CONCAT(h.[Buy-from Vendor Name],h.[Buy-from Vendor Name 2]) suppliername,
		 CONCAT(h.[Buy-from Address], h.[Buy-from Address 2]) supplieraddress,
		 h.[Buy-from Post Code] supplierpostcode,
		 h.[Buy-from City] suppliercity,
		 h.[Buy-from County] supplierstate,
         h.[Location Code] warehouse,
		 CONCAT(h.[Ship-to Name],h.[Ship-to Name 2]) shiptoname,
		 CONCAT(h.[Ship-to Address], h.[Ship-to Address 2]) shiptoaddress,
		 h.[Ship-to Post Code] shiptopostcode,
		 h.[Ship-to City] shiptocity,
		 h.[Ship-to County] shiptostate,
		 h.[VAT Registration No.] vat,
		 FORMAT(h.[Document Date],\'dd/MM/yyyy\') date,
		 COUNT(l.[Line No.]) AS lines
		 FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Rcpt. Header] h
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$User Setup] userauthor ON userauthor.[User ID]=h.[User ID]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$User Setup] usersaleperson ON usersaleperson.[Salespers._Purch. Code]=h.[Purchaser Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Rcpt. Line] l ON l.[Document No.]=h.[No.]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Vendor] c ON c.[No.]=h.[Buy-from Vendor No.]
		 WHERE (h.[No.] LIKE \'%'.$search.'%\') AND
		 CAST(h.TIMESTAMP AS BIGINT)>0 
		 GROUP BY h.TIMESTAMP, h.[No.], h.[Buy-from Vendor No.], h.[Buy-from Vendor Name], h.[Buy-from Vendor Name 2], h.[Buy-from Address], h.[Buy-from Address 2], h.[Buy-from Post Code], h.[Buy-from City], h.[Buy-from County],
					 h.[Ship-to Name], h.[Ship-to Address], h.[Ship-to Name 2], h.[Ship-to Address 2], h.[Ship-to Post Code], h.[Ship-to City], h.[Ship-to County], h.[Location Code], h.[Vendor Shipment No.],
		 			 h.[VAT Registration No.], h.[Document Date], userauthor.[E-Mail], 
					 usersaleperson.[E-Mail]
		ORDER BY h.[No.] DESC;';
		
		
		//echo utf8_encode($sql);
		
		
		$stmt = sqlsrv_query( $connNavision, $sql);
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				
				$temp=[];
				foreach($row as $key=>$object){
					$temp_object[$key]=!is_object($object)?utf8_encode($object):$object;
				}
				
		if($row['timestamp']>$maxtimestamp) $maxtimestamp=$row['timestamp'];
				//Invoice lines
		$lines=[];
		$sql = 'SELECT l.[No.] AS reference, l.[Cross-Reference No.] AS referencecross, CONCAT(l.[Description], l.[Description 2]) AS description, l.[Quantity] AS quantity
				FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Rcpt. Line] l
				WHERE l.[Document No.]=\''.$row['code'].'\'';

		

		$stmt_lines = sqlsrv_query( $connNavision, $sql );
		if( $stmt_lines !== false) {
			while($row_line = sqlsrv_fetch_array( $stmt_lines, SQLSRV_FETCH_ASSOC)){
				$temp_line=[];
				foreach($row_line as $key=>$item){
					$temp_line[$key]=!is_object($item)?utf8_encode($item):$item;
				}
								
				/*$temp_line["quantity"]=floatval($temp_line["quantity"]);
				$temp_line["price"]=floatval($temp_line["price"]);
				$temp_line["discountperc"]=floatval($temp_line["discountperc"]);
				$temp_line["discounttotal"]=floatval($temp_line["discounttotal"]);
				$temp_line["surchargeperc"]=floatval($temp_line["surchargeperc"]);
				$temp_line["taxperc"]=floatval($temp_line["taxperc"]);
				$temp_line["taxtotal"]=floatval($temp_line["taxtotal"]);
				$temp_line["linetotal"]=floatval($temp_line["linetotal"]);
				$temp_line["cost"]=floatval($temp_line["cost"]);
				$temp_line["total"]=floatval($temp_line["total"]);*/
				
					
				$lines[]=$temp_line;
				
				
			}
		}
				$temp_object["lines"]=$lines;	
				/*$temp_object["linestotal"]=floatval($temp_object["linestotal"]);
				$temp_object["vattotal"]=floatval($temp_object["vattotal"]);
				$temp_object["dto"]=floatval($temp_object["dto"]);
				$temp_object["base"]=floatval($temp_object["base"]);
				$temp_object["cost"]=floatval($temp_object["cost"]);
				$temp_object["total"]=floatval($temp_object["total"]);*/
				$objects[]=$temp_object;
			}
		}
		//var_dump($objects);
		$collection["maxtimestamp"]=$maxtimestamp;
		$collection["class"]=$objects;
		$result[]=$collection;
		//var_dump($result);
		
header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);

?>
