<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Europe/Madrid');

// Parámetros
$serverName 	= "localhost";
$connectionInfo = array( "Database"=>"ICF", "UID"=>"VAIO", "PWD"=>"VAIO");

// Conexión con Navision
$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 json_encode(["result"=>-1 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

$result		= [];


// Obtención de la orden de venta
$code = null;
if(isset($_REQUEST["code"])) 
	$code=$_REQUEST["code"]; 



if($code != null) {
	$sql = 'SELECT 
			CAST(sh.TIMESTAMP AS BIGINT) as ts,
			format(sh.[Order Date], \'yyyy-MM-dd HH:mm:ss\') as odate,
			sh.[No.] as code,
			sh.[Es Devolucion] as refund,
			sh.[Shipping Advice] as complete,
			sh.[Entrega completa del articulo] as completeline,
			sh.[Historic] as historic,
			sh.[Estado pedido] as state,
			us.[E-mail] as agent_email,
			sh.[Sell-to Customer No.] as customer_code,
			CONCAT(sh.[Ship-to Name], sh.[Ship-to Name 2]) as customer_name,
			sh.[VAT Registration No.] as customer_vat,
			sh.[Payment Method Code] as payment_method_code,
			pt.[Code] as payment_term_code,
			pt.[Description] as payment_term_name,
			pt.[Due Date Calculation] as payment_term_days,
			cu.[Sales type] as sales_type,
			sh.[Location Code] as store_code,
			sh.[External Document No.] as their_code,
			CASE WHEN sh.[Ship-to Contact] IS NULL OR sh.[Ship-to Contact]=\'\' THEN sh.[Sell-to Contact] ELSE sh.[Ship-to Contact] END as contact,
			sh.[Shipment Method Code] ship_condition_code,
			sh.[Shipping Agent Code] ship_agent_code,
			sh.[Email Documento] as email,
			sh.[Ship-to Country_Region Code] as country_code,
			sh.[Ship-to City] as town,
			CONCAT(sh.[Ship-to address], sh.[Ship-to Address 2]) as address,
			sh.[Ship-to Post Code] as cp,
			CASE WHEN sa.[Phone No.] IS NULL OR sa.[Phone No.]=\'\' THEN sh.[Telefono] ELSE sa.[Phone No.] END as phone,
			FORMAT(sh.[Invoice Discount Value], \'0.0000\', \'en-EN\') as discount,
			sh.[Pedido WEB] as web,
			sh.[External Document No.] as code2,
			sh.[Package Tracking No.] as related_order,
			STUFF((
				SELECT \'|\' + CAST(co.[Line No.] AS NVARCHAR(8)) + \'~\' + co.[Comment]
				FROM dbo.[Ind. Campollano Ferretería S.L$Sales Comment Line] co
				WHERE co.[No.] = sh.[No.] AND (co.[Document Line No.]=0 or co.[Document Line No.] not in (SELECT sl.[Line No.] from dbo.[Ind. Campollano Ferretería S.L$Sales Line] sl where sl.[Document No.]=\''.$code.'\' AND (sl.[Type]=0 or sl.[Type]=2)))
				FOR XML PATH(\'\'), TYPE
			).value(\'.\', \'NVARCHAR(MAX)\'), 1, 1, \'\') as comments,
			STUFF((
				SELECT \'|\' + CAST(cop.[Line No.] AS NVARCHAR(8)) + \'~\' + cop.[Comment]
				FROM dbo.[Ind. Campollano Ferretería S.L$Comment Line] cop
				WHERE cop.[No.] = sh.[Sell-to Customer No.] AND cop.[Code]=\'VENTAS\'
				FOR XML PATH(\'\'), TYPE
			).value(\'.\', \'NVARCHAR(MAX)\'), 1, 1, \'\') as commentsprivate
			FROM dbo.[Ind. Campollano Ferretería S.L$Sales Header] sh
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$User Setup] us on sh.[Salesperson Code]=us.[Salespers._Purch. Code]
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Payment Terms] pt on sh.[Payment Terms Code]=pt.[Code]
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Customer] cu on cu.[No.]=sh.[Bill-to Customer No.]
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Ship-to Address] sa on sa.[Code]=sh.[Ship-to Code] AND sa.[Customer No.]=sh.[Sell-to Customer No.]
			WHERE sh.[No.]=\''.$code.'\' group by sh.[No.],
			sh.TIMESTAMP,
			sh.[Order Date],    
			sh.[Es Devolucion],
			sh.[Shipping Advice],
			sh.[Entrega completa del articulo],
			sh.[Historic],
			sh.[Estado pedido],
			us.[E-mail],
			sh.[Sell-to Customer No.],
			sh.[Ship-to Name],
			sh.[Ship-to Name 2],
			sh.[VAT Registration No.],
			sh.[Payment Method Code],
			pt.[Code],
			pt.[Description],
			pt.[Due Date Calculation],
			cu.[Sales type],
			sh.[Location Code],
			sh.[Your Reference],
			sh.[Ship-to Contact],
			sh.[Sell-to Contact],
			sh.[Shipment Method Code],
			sh.[Shipping Agent Code],
			sh.[Email Documento],
			sh.[Ship-to Country_Region Code],
			sh.[Ship-to City],
			sh.[Ship-to Address],
			sh.[Ship-to Address 2],
			sh.[Ship-to Post Code],
			sa.[Phone No.],
			sh.[Telefono],
			sh.[Invoice Discount Value],
			sh.[Pedido WEB],
			sh.[External Document No.],
			sh.[Package Tracking No.]';
	$stmt_sale = sqlsrv_query( $connNavision, $sql);	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
		echo json_encode(["result"=>-2, "text"=>"1 Ocurrio un error en la consulta SQL de Navision: Ventas"]);
		die;
	}
	if( $stmt_sale !== false) {
		while($row2 = sqlsrv_fetch_array( $stmt_sale, SQLSRV_FETCH_ASSOC)){			
			$sale=[];
			foreach($row2 as $key=>$object){
				$sale[$key]=!is_object($object)?utf8_encode($object):$object;
			}			
			$result['header']=$sale;
			$result['lines']=[];
			$sql = 'SELECT
					CAST(sl.TIMESTAMP AS BIGINT) as ts,
					sl.[Line No.] as linenum_navision,
					sl.[Type] as type,
					sl.[Location Code] as store_code,
					REPLACE(sl.[No.], CHAR(13) + CHAR(10), \'\') as product_code,
					sl.[Variant Code] as variant_code,
					sl.[Cross-Reference No.] as customer_reference,
					FORMAT(sl.[Quantity], \'0.0000\', \'en-EN\') as quantity,
					FORMAT(sl.[Gross Weight], \'0.0000\', \'en-EN\') as weight,
					FORMAT(sl.[Unit Cost (LCY)], \'0.0000\', \'en-EN\') as shoppingprice,
					FORMAT(sl.[Qty. per Unit of Measure], \'0.0000\', \'en-EN\') as saleunit,
					sl.[Unit of Measure Code] as measure_code,
					FORMAT(sl.[Unit Price], \'0.0000\', \'en-EN\') as pricebase,
					FORMAT(sl.[Line Discount %], \'0.0000\', \'en-EN\') as discountperc,
					FORMAT((sl.[Line Discount Amount]/NULLIF(sl.[Quantity], 0)), \'0.0000\', \'en-EN\') as discountunit,
					FORMAT(sl.[Line Discount Amount], \'0.0000\', \'en-EN\') as discounttotal,
					FORMAT((sl.[Amount]/NULLIF(sl.[Quantity], 0)), \'0.0000\', \'en-EN\') as priceunit,
					FORMAT(sl.[Amount], \'0.0000\', \'en-EN\') as pricetotal,
					FORMAT(sl.[VAT %], \'0.0000\', \'en-EN\') as taxperc,
					FORMAT(((sl.[Amount]/NULLIF(sl.[Quantity], 0))*sl.[VAT %])/100, \'0.0000\', \'en-EN\') as taxunit,
					FORMAT((sl.[Amount]*sl.[VAT %])/100, \'0.0000\', \'en-EN\') as taxtotal,
					FORMAT(sl.[Amount]+((sl.[Amount]*sl.[VAT %])/100), \'0.0000\', \'en-EN\') as total,
							STUFF((
						SELECT \'|\' + CAST(co.[Line No.] AS NVARCHAR(8)) + \'~\' + co.[Comment]
						FROM dbo.[Ind. Campollano Ferretería S.L$Sales Comment Line] co
						WHERE co.[No.] = sl.[Document No.] AND co.[Document Line No.]=sl.[Line No.]
						FOR XML PATH(\'\'), TYPE
					).value(\'.\', \'NVARCHAR(MAX)\'), 1, 1, \'\') as comments
					FROM  dbo.[Ind. Campollano Ferretería S.L$Sales Line] sl
					WHERE sl.[Document No.]=\''.$code.'\' AND (sl.[Type]=0 or sl.[Type]=2) ORDER BY sl.[Line No.]';
			$stmt_saleline = sqlsrv_query( $connNavision, $sql);
			if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
				echo json_encode(["result"=>-3, "text"=>"1 Ocurrio un error en la consulta SQL de Navision: Lineas de Ventas"]);
				die;
			}
			if( $stmt_saleline !== false) {
				while($row3 = sqlsrv_fetch_array( $stmt_saleline, SQLSRV_FETCH_ASSOC)){			
					$saleline=[];
					foreach($row3 as $key=>$object){
						$saleline[$key]=!is_object($object)?utf8_encode($object):$object;
					}
					if ((isset($saleline['product_code']) && $saleline['product_code']!='' && $saleline['type']=='2') || (isset($saleline['type']) && $saleline['type']=='0'))
						$result['lines'][] = $saleline;
				}
			}				
		}
	}				

}
header('Content-Type: application/json');
echo json_encode($result, JSON_UNESCAPED_UNICODE);
?>
