<?php
error_reporting(E_ERROR);
//ini_set('display_errors', 1);
//ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");

$invoices=$_REQUEST["invoices"];
$invoices=json_decode($invoices);
$resultInvoices=[];
function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str= stripslashes($str);
    }
    return str_replace("'", "''", $str);
}

$serverName = "localhost";
//sqlsrv_configure('WarningsReturnAsErrors', 0);
//sqlsrv_configure('LogSubsystems', SQLSRV_LOG_SYSTEM_OFF);
$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));
	}

	foreach($invoices as $key=>$invoice){
		$temp_invoice=[];
		$temp_invoice["number"]=$invoice;
		
		$sql = 'SELECT * FROM [Ind. Campollano Ferreterķa S.L$Sales Comment Line]
			 WHERE [No.]=\''.$invoice.'\' AND [Document Line No.]=0 ORDER BY [Line No.] ASC';
		
		//Invoice Comments
		$comments=[];
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
			  $comments[]=["comment"=>utf8_encode($row["Comment"])];
			};
		}
		
		
		$sql = 'SELECT * FROM [Ind. Campollano Ferreterķa S.L$Cartera Doc.]
			 WHERE [Document No.]=\''.$invoice.'\' ORDER BY [Due Date] ASC';
		
		//Invoice Expirations
		$expirations=[];
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
			  $expirations[]=["date"=>$row["Due Date"], "amount"=>floatval($row["Remaining Amount"])];
			};
		}
		   
		$sql = 'SELECT h.[Bill-to Customer No.] customerId,
		 h.[Bill-to Name] customer,
		 h.[Bill-to Address] address,
		 h.[Bill-to Post Code] postcode,
		 h.[Bill-to City] city,
		 h.[Bill-to County] state,
		 paymethod.[Description] paycode,
		 h.[VAT Registration No.] vat_number,
		 payment.[Due Date Calculation] paydate, 
		 payment.[Description] paydesc,
		 bankacc.[IBAN] IBAN, 
		 h.[Document Date] date,		 
		 h.[VAT Bus. Posting Group] typevat,
		 SUM(l.[Line Amount]) AS linestotal,
		 SUM(l.[Amount Including VAT]-l.[Amount]) AS vattotal,
		 SUM(l.[Inv. Discount Amount]) AS dto,
		 SUM(l.[Line Amount])-SUM(l.[Inv. Discount Amount]) AS base,
		 SUM(l.[Amount Including VAT]) 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$Payment Method] paymethod ON paymethod.[Code]=h.[Payment Method Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Payment Terms] payment ON payment.[Code]=h.[Payment Terms Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Invoice Line] l ON l.[Document No.]=h.[No.]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Customer] c ON c.[No.]=h.[Bill-to Customer No.]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Customer Bank Account] bankacc ON bankacc.[Customer No.]=h.[Bill-to Customer No.] AND bankacc.[Code]=\'BC0001\'
		 WHERE h.[No.]=\''.$invoice.'\'
		 GROUP BY 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], paymethod.[Description],
		 			 h.[VAT Registration No.], payment.[Due Date Calculation], payment.[Description], h.[Document Date], bankacc.[IBAN], h.[VAT Bus. Posting Group];';
		
		
		//Invoice Header 
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			if($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				foreach($row as $key=>$item){
					$temp_invoice[$key]=!is_object($item)?utf8_encode($item):$item;
				}
			}
		}
		   		   
		//Invoice VAT and Surcharge
		$vat=[];
		$sql = 'SELECT DISTINCT(l.[VAT Prod. Posting Group]), ps.[EC %] as surcharge, ps.[VAT %] as vat
				FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Invoice Line] l
				LEFT JOIN [Ind. Campollano Ferreterķa S.L$VAT Posting Setup] ps ON ps.[VAT Prod. Posting Group]=l.[VAT Prod. Posting Group]  AND ps.[VAT Bus. Posting Group]=l.[VAT Bus. Posting Group]
				WHERE l.[Document No.]=\''.$invoice.'\' AND l.[VAT Prod. Posting Group] <> \'\'
				AND l.[VAT Prod. Posting Group] IS NOT null';
		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
					$vat[]=["vat"=>floatval($row["vat"]), "surcharge"=>floatval($row["surcharge"]), "caption"=> floatval($row["vat"]).($row["surcharge"]>0?'+'.floatval($row["surcharge"]):'')];
			}
		}
		
		//Invoice lines
		$lines=[];
		//2024-09-04 Se quitan las lineas con cantidad a 0 por que la facturacion automatica FV mete todas las lineas, incluidas las no servidas y la factura sale fatal
		$sql = 'SELECT l.[No.] AS reference, l.[Cross-Reference No.] AS referencecross, CONCAT(l.[Description], l.[Description 2]) AS description, l.[Quantity] AS quantity,
					l.[Unit price UM precio] AS price, l.[Line Discount %] AS desicount, l.[Line Amount] AS linetotal, l.type, l.[Shipment No.] AS shipment, sh.[Sell-to Contact] AS contact,
					sh.[External Document No.] as customerreference, l.[Variant Code] AS variant_name
				FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Invoice Line] l
				LEFT JOIN [Ind. Campollano Ferreterķa S.L$Sales Shipment Header] sh ON sh.[No.]=l.[Shipment No.]
				WHERE l.[Document No.]=\''.$invoice.'\' AND l.type<>0 AND l.[Quantity] <>0';

		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
            $last_shipment=null;
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
                
				$temp_line=[];
				foreach($row 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["desicount"]=floatval($temp_line["desicount"]);
				$temp_line["linetotal"]=floatval($temp_line["linetotal"]);
				
				if($row["shipment"]!=null && $last_shipment!=$row["shipment"]){
				
					$sqlComments = 'SELECT * FROM [Ind. Campollano Ferreterķa S.L$Sales Comment Line]
					WHERE [No.]=\''.$row["shipment"].'\' AND [Document Line No.]=0 ORDER BY [Line No.] ASC';
					//Line Comments
					$stmtComments = sqlsrv_query( $connNavision, $sqlComments );
					if( $stmtComments !== false) {
						while($rowComments = sqlsrv_fetch_array( $stmtComments, SQLSRV_FETCH_ASSOC)){
						  //$comments[]=["comment"=>utf8_encode($row["Comment"])];
						  $temp_line["comments"][]=["comment"=>utf8_encode($rowComments["Comment"])];
						};
					}//else var_dump(sqlsrv_errors());
					
				}
                if($last_shipment!=$row["shipment"]) $last_shipment=$row["shipment"];
				$lines[]=$temp_line;
				
				
			}
		}
		$temp_invoice["lines"]=$lines;
		
		$temp_invoice["vat"]=$vat;
		$temp_invoice["expirations"]=$expirations;
		$temp_invoice["comments"]=$comments;
		$temp_invoice["linestotal"]=floatval($temp_invoice["linestotal"]);
		$temp_invoice["vattotal"]=floatval($temp_invoice["vattotal"]);
		$temp_invoice["dto"]=floatval($temp_invoice["dto"]);
		$temp_invoice["base"]=floatval($temp_invoice["base"]);
		$temp_invoice["total"]=floatval($temp_invoice["total"]);
		
		//var_dump($temp_invoice);
		$resultInvoices[$invoice]=$temp_invoice;
	}
header('Content-Type: application/json');
echo json_encode($resultInvoices, JSON_PRETTY_PRINT);
?>
