<?php
/**   ------------------------------------------------------------
		CREA UN PEDIDO DE COMPRA A PROVEEDOR EN NAVISION
	  ------------------------------------------------------------
*/ 
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Europe/Madrid');

$discord_channel = "1281539966144483338";

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

function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str= stripslashes($str);
    }
    return str_replace("'", "''", $str);
}

if(isset($_REQUEST["store"])) 
	$store=$_REQUEST["store"]; 
else{
	echo json_encode(["result"=>-1 , "text"=>"Se debe proporcionar un codigo de almacen de donde se enviara el pedido"]);
	die;
}

//Precargamos los datos de los almacenes para no tener que buscarlos en base de datos
$stores=[
		"ALM01"=>["name"=>"ALMACEN 01 CAMPOLLANO", "name2"=>"", "address"=>"Parque empresarial Campollano c/G, 35", "address2"=>"C/G Nº 35", "postcode"=>"02007", "city"=>"ALBACETE", "county"=>"ALBACETE", "country"=>"ES"], 
		"ALM02"=>["name"=>"ALMACEN 02 ROMICA", "name2"=>"", "address"=>"POLIG.IND.DE ROMICA AVENIDA A Nº34", "address2"=>"", "postcode"=>"02007", "city"=>"ALBACETE", "county"=>"Albacete", "country"=>"ES"]
];
//Comprobamos que esten configurados los almacenes implicados
if(!isset($stores[$store])){
	echo json_encode(["result"=>-3 , "text"=>"El almacn no esta configurado"]);
	die;
}	

$response=[];
$responseErrors=[];
// 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"=>-4 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

$lines 		= [];
$json = file_get_contents('php://input');
$data = json_decode($json, true);

if (json_last_error() !== JSON_ERROR_NONE) {
    // JSON no es válido
	    echo json_encode(["result"=>-5 , "text"=>"Los datos enviados no son validos"]);
}

if (isset($data['lines']) && count($data['lines'])>0)
	$lines = $data['lines'];
else{
	echo json_encode(["result"=>-6 , "text"=>"No se ha indicado lineas para traspasar"]);
	die;
}
$formattedDate=date("Y-d-m");
foreach($lines as $line){
	$resultItem=[];
	$orderNo=null;
	//Buscamos los datos del producto de Navision
	$sql = 'SELECT i.*, v.[VAT %], ium.[Qty. per Unit of Measure], um.[Description] as [Unit Measure Desc]  FROM dbo.[Item] i
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$VAT Posting Setup] v ON i.[VAT Prod. Posting Group]=v.[VAT Prod. Posting Group]
			LEFT JOIN dbo.[Item Unit of Measure] ium ON i.[Unidad medida precio] = ium.[Code] AND i.[No.]=ium.[Item No.]
			LEFT JOIN dbo.[Unit of Measure] um ON ium.[Code] = um.[Code]
	WHERE [No.]=\''.$line["code"].'\'';
	$stmt_searchItem = sqlsrv_query( $connNavision, $sql);
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -1 Error en la consulta de busqueda de producto en Navision";
		continue;
	}
	if( $stmt_searchItem !== false) {
		if (!$row_searchItem = sqlsrv_fetch_array( $stmt_searchItem, SQLSRV_FETCH_ASSOC)){
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -2 Error no se ha encontrado el producto en Navision";
			continue;
		}
	}		
	//Buscamos los datos del proveedor de pedido de Navision
	$sql = 'SELECT v.*, pt.[Due Date Calculation] FROM dbo.[Ind. Campollano Ferretería S.L$Vendor] v
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Payment Terms] pt ON v.[Payment Terms Code] = pt.[Code] 
			WHERE [No.]=\''.$row_searchItem["Vendor No."].'\'';
	$stmt_searchSupplier = sqlsrv_query( $connNavision, $sql);
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -3 Error en la consulta de busqueda de proveedor en Navision";
		continue;
	}
	if( $stmt_searchSupplier !== false) {
		if (!$row_searchSupplier = sqlsrv_fetch_array( $stmt_searchSupplier, SQLSRV_FETCH_ASSOC)){
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -4 Error no se ha encontrado el proveedor en Navision";
			continue;
		}
	}
	
	//Buscamos los datos del proveedor de pago de Navision
	if($row_searchSupplier['Pay-to Vendor No.']!=""){
		$sql = 'SELECT v.*, pt.[Due Date Calculation] FROM dbo.[Ind. Campollano Ferretería S.L$Vendor] v 
				LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Payment Terms] pt ON v.[Payment Terms Code] = pt.[Code] 
				WHERE [No.]=\''.$row_searchItem["Vendor No."].'\'';
		$stmt_searchPaySupplier = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -5 Error en la consulta de busqueda de proveedor de pago en Navision";
			continue;
		}
		if( $stmt_searchPaySupplier !== false) {
			if (!$row_searchPaySupplier = sqlsrv_fetch_array( $stmt_searchPaySupplier, SQLSRV_FETCH_ASSOC)){
				$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -6 Error no se ha encontrado el proveedor de pago en Navision";
				continue;
			}
		}
	}else $row_searchPaySupplier = $row_searchSupplier;
	
	//Buscamos si hay un pedido de compra abierto ya creado del proveedor indicado no mas antiguo a dos meses
	$sql='SELECT TOP 1 [No.] FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Header] th where th.[No.] LIKE CONCAT(RIGHT(YEAR(GETDATE()), 2), \'PC%\') and th.[Buy-from Vendor No.]=\''.$row_searchSupplier['No.'].'\' AND [Status]=0 AND [Historic]=0 AND [Es Devolucion]=0 AND [Document Date] >= DATEADD(MONTH, -2, GETDATE()) ORDER BY [Document Date] DESC';
	$stmt_searchOrder = sqlsrv_query( $connNavision, $sql);	
	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -7 Error en la consulta de busqueda de pedido abierto en Navision";
		continue;
	}
	if( $stmt_searchOrder !== false) {
		if ($row_searchOrder = sqlsrv_fetch_array( $stmt_searchOrder, SQLSRV_FETCH_ASSOC)){
			$orderNo = $row_searchOrder['No.'];
		}
	}

	//Si no hay pedido abierto lo creamos
	if(!$orderNo){
		//Obtenemos el No. del ultimo pedido de compra generado para incrementarlo
		$initDate = date('Y-m-d', strtotime('first day of January ' . date('Y')));
		$sql='SELECT [Last No. Used] FROM dbo.[Ind. Campollano Ferretería S.L$No. Series Line] sl where sl.[Series Code]=\'C-PED\' AND [Starting Date]=\''.$initDate.'\' AND [Open]=1';
		
		$stmt_searchMaxNo = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -8 Error en la consulta de busqueda de ultimo pedido de compra generado en Navision";
			continue;
		}
		if( $stmt_searchMaxNo !== false) {
			if ($row_searchMaxNo = sqlsrv_fetch_array( $stmt_searchMaxNo, SQLSRV_FETCH_ASSOC)){
				$orderNo = $row_searchMaxNo["Last No. Used"];
			}else{
				$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -9 Error no se ha podido encontrar el ultimo pedido de compra generado en Navision";
			}
		}
		//Incrementamos el numero de pedido
		$previousNo=$orderNo;
		$nextNo = intval(substr($orderNo, 4))+1;
		$nextFormatedNo = str_pad($nextNo, 5, '0', STR_PAD_LEFT);
		$orderNo = date('y').'PC'.$nextFormatedNo;
			
		$sql='
			INSERT INTO ICF.dbo.[Ind. Campollano Ferretería S.L$Purchase Header](
				[Document Type], 
				[No.], 
				[Buy-from Vendor No.], 
				[Pay-to Vendor No.], 
				[Pay-to Name], 
				[Pay-to Name 2], 
				[Pay-to Address], 
				[Pay-to Address 2],
				[Pay-to City], 
				[Pay-to Contact], 
				[Your Reference], 
				[Ship-to Code],
				[Ship-to Name], 
				[Ship-to Name 2],
				[Ship-to Address],
				[Ship-to Address 2],
				[Ship-to City], 
				[Ship-to Contact], 
				[Order Date],
				[Posting Date],
				[Expected Receipt Date],
				[Posting Description],
				[Payment Terms Code],
				[Due Date], 
				[Payment Discount %],
				[Pmt. Discount Date],
				[Shipment Method Code],
				[Location Code],
				[Shortcut Dimension 1 Code],
				[Shortcut Dimension 2 Code],
				[Vendor Posting Group],
				[Currency Code], 
				[Currency Factor],
				[Prices Including VAT], 
				[Invoice Disc. Code], 
				[Language Code], 
				[Purchaser Code], 
				[Order Class], 
				[No. Printed],
				[On Hold], 
				[Applies-to Doc. Type],
				[Applies-to Doc. No.],
				[Bal. Account No.],
				[Receive], 
				[Invoice], 
				[Receiving No.],
				[Posting No.],
				[Last Receiving No.],
				[Last Posting No.],
				[Vendor Order No.], 
				[Vendor Shipment No.],
				[Vendor Invoice No.],
				[Vendor Cr. Memo No.],
				[VAT Registration No.],
				[Sell-to Customer No.],
				[Reason Code],
				[Gen. Bus. Posting Group], 
				[Transaction Type], 
				[Transport Method],
				[VAT Country_Region Code], 
				[Buy-from Vendor Name],
				[Buy-from Vendor Name 2],
				[Buy-from Address],
				[Buy-from Address 2],
				[Buy-from City], 
				[Buy-from Contact], 
				[Pay-to Post Code], 
				[Pay-to County], 
				[Pay-to Country_Region Code], 
				[Buy-from Post Code],
				[Buy-from County], 
				[Buy-from Country_Region Code],
				[Ship-to Post Code], 
				[Ship-to County],
				[Ship-to Country_Region Code],
				[Bal. Account Type], 
				[Order Address Code], 
				[Entry Point],
				Correction, 
				[Document Date],
				[Area], 
				[Transaction Specification],
				[Payment Method Code], 
				[No. Series], 
				[Posting No. Series],
				[Receiving No. Series],
				[Tax Area Code],
				[Tax Liable],
				[VAT Bus. Posting Group],
				[Applies-to ID], 
				[VAT Base Discount %],
				[Status],
				[Invoice Discount Calculation],
				[Invoice Discount Value],
				[Send IC Document], 
				[IC Status], 
				[Buy-from IC Partner Code],
				[Pay-to IC Partner Code], 
				[IC Direction], 
				[Prepayment No.],
				[Last Prepayment No.],
				[Prepmt. Cr. Memo No.],
				[Last Prepmt. Cr. Memo No.],
				[Prepayment %],
				[Prepayment No. Series],
				[Compress Prepayment],
				[Prepayment Due Date],
				[Prepmt. Cr. Memo No. Series],
				[Prepmt. Posting Description],
				[Prepmt. Pmt. Discount Date],
				[Prepmt. Payment Terms Code], 
				[Prepmt. Payment Discount %],
				[Doc. No. Occurrence], 
				[Campaign No.], 
				[Buy-from Contact No.], 
				[Pay-to Contact No.], 
				[Responsibility Center],
				[Posting from Whse. Ref.],
				[Requested Receipt Date], 
				[Promised Receipt Date], 
				[Lead Time Calculation], 
				[Inbound Whse. Handling Time],
				[Vendor Authorization No.],
				[Return Shipment No.], 
				[Return Shipment No. Series], 
				[Ship], 
				[Last Return Shipment No.], 
				[Assigned User ID],
				[Generate Autoinvoices], 
				[Generate Autocredit Memo],
				[Corrected Invoice No.],
				[Estado pedido],
				[Cod. Banco domic.],
				[Fecha cierre], 
				[Regimen fiscal IRPF],
				[Tipo base IRPF],
				[% IRPF], 
				[Doc. Rectificado],
				[Tipo Doc. Rectificado],
				[Tipo Facturas (349)],
				[Applies-to Bill No.],
				[Vendor Bank Acc. Code],
				[Pay-at Code], 
				[Historic],
				[Fecha 1.lanzamiento], 
				[Contestado por Proveedor],
				[incluir recl. cant. pendientes],
				[Importe portes],
				[Peso total], 
				[Dto por pesos], 
				[% seguro],
				[No oferta patron],
				[No oferta relacionada],
				[Fax Documento], 
				[Email Documento], 
				[Cod. Transportista],
				[No Transporte], 
				[Plazo medio entrega (días) Ped],
				[Plazo total entrega (días) Ped], 
				[Es Devolucion], 
				[Factura rappel],
				[Date Received], 
				[Time Received], 
				[BizTalk Purchase Quote], 
				[BizTalk Purch. Order Cnfmn.], 
				[BizTalk Purchase Invoice],
				[BizTalk Purchase Receipt],
				[BizTalk Purchase Credit Memo],
				[Date Sent], 
				[Time Sent], 
				[BizTalk Request for Purch. Qte], 
				[BizTalk Purchase Order], 
				[Vendor Quote No.], 
				[BizTalk Document Sent], 
				Packages, 
				FechaReclamacion, 
				PorcentajeRecibido, 
				Telefono, 
				[Pedido Axiom]
				)VALUES(
				1, 
				\''.$orderNo.'\', 
				\''.$row_searchSupplier['No.'].'\',
				\''.$row_searchPaySupplier['No.'].'\', 
				\''.mssql_escape($row_searchPaySupplier['Name']).'\', 
				\''.mssql_escape($row_searchPaySupplier['Name 2']).'\', 
				\''.mssql_escape($row_searchPaySupplier['Address']).'\', 
				\''.mssql_escape($row_searchPaySupplier['Address 2']).'\', 
				\''.mssql_escape($row_searchPaySupplier['City']).'\', 
				\''.mssql_escape($row_searchPaySupplier['Contact']).'\', 
				\'\',
				\'\',
				\''.mssql_escape($stores[$store]["name"]).'\',
				\''.mssql_escape($stores[$store]["name2"]).'\',
				\''.mssql_escape($stores[$store]["address"]).'\',
				\''.mssql_escape($stores[$store]["address2"]).'\',
				\''.mssql_escape($stores[$store]["city"]).'\',
				\'\',
				\''.$formattedDate.'\',
				\''.$formattedDate.'\',
				CAST(\''.date('Y-d-m', strtotime("+".intval($row_searchSupplier['Plazo total entrega (dias)'])." days")).'\' AS DATETIME),
				\'Pedido '.$orderNo.'\',
				\''.$row_searchPaySupplier['Payment Terms Code'].'\', 
				CAST(\''.date('Y-d-m', strtotime("+".intval($row_searchPaySupplier['Due Date Calculation'])." days")).'\' AS DATETIME),
				0,
				\''.$formattedDate.'\',
				\''.$row_searchSupplier['Shipment Method Code'].'\', 
				\''.$store.'\',
				\'\',
				\'\',
				\''.$row_searchSupplier['Vendor Posting Group'].'\', 
				\''.$row_searchSupplier['Currency Code'].'\', 
				0, 
				\''.$row_searchSupplier['Prices Including VAT'].'\', 
				\''.$row_searchSupplier['Invoice Disc. Code'].'\', 
				\''.$row_searchSupplier['Language Code'].'\', 
				99,
				\'\',
				0,
				\'\',
				0,
				\'\',
				\'\',
				0,
				0,
				\'\',
				\'\',
				\'\',
				\'\',
				\'\',
				\'\',
				\'\',
				\'\',
				\''.$row_searchSupplier['VAT Registration No.'].'\', 
				\'\',
				\'\',
				\''.$row_searchSupplier['Gen. Bus. Posting Group'].'\', 
				\'\',
				\'\',
				\''.$row_searchPaySupplier['Country_Region Code'].'\',
				\''.mssql_escape($row_searchSupplier['Name']).'\',
				\''.mssql_escape($row_searchSupplier['Name 2']).'\',
				\''.mssql_escape($row_searchSupplier['Address']).'\',
				\''.mssql_escape($row_searchSupplier['Address 2']).'\',
				\''.mssql_escape($row_searchSupplier['City']).'\',
				\''.mssql_escape($row_searchSupplier['Contact']).'\',
				\''.$row_searchPaySupplier['Post Code'].'\',
				\''.$row_searchPaySupplier['County'].'\',
				\''.$row_searchPaySupplier['Country_Region Code'].'\',				
				\''.$row_searchSupplier['Post Code'].'\',
				\''.$row_searchSupplier['County'].'\',
				\''.$row_searchSupplier['Country_Region Code'].'\',
				\''.$stores[$store]["postcode"].'\',
				\''.$stores[$store]["county"].'\',
				\''.$stores[$store]["country"].'\',
				0,
				\'\',
				\'\',
				0,
				\''.$formattedDate.'\',
				\'\',
				\'\',
				\''.$row_searchPaySupplier['Payment Method Code'].'\',
				\'C-PED\',
				\'C-FAC+\',
				\'C-ALB\',
				\'\',
				0,
				\''.$row_searchPaySupplier['VAT Bus. Posting Group'].'\',
				\'\',
				0,
				0,
				0,
				0,
				0,
				0,
				\'\',
				\'\',
				0,
				\'\',
				\'\',
				\'\',
				\'\',
				0,
				\'PREP-INT+\',
				1,
				CAST(\''.date('Y-d-m', strtotime("+".(intval($row_searchPaySupplier['Due Date Calculation'])-1)." days")).'\' AS DATETIME),
				\'PREP-AB+\',
				\'\',
				\''.$formattedDate.'\',
				\''.$row_searchPaySupplier['Payment Terms Code'].'\',
				0,
				1,
				\'\',
				\''.$row_searchSupplier['Primary Contact No.'].'\',
				\''.$row_searchPaySupplier['Primary Contact No.'].'\',
				\'\',
				0,
				\'1753-01-01\',
				\'1753-01-01\',
				\''.$row_searchSupplier['Lead Time Calculation'].'\',
				\'\',
				\'\',
				\'\',
				\'\',
				0,
				\'\',
				\'\',
				0,
				0,
				\'\',
				0,
				\''.$row_searchPaySupplier['Cod. Banco domic.'].'\',
				\'1753-01-01\',
				\'\',
				0,
				0,
				\'\',
				0,
				0,
				\'\',
				\'\',
				\'\',
				0,
				\'1753-01-01\',
				0,
				0,
				0,
				0,
				0,
				0,
				\'\',
				\'\',
				\''.$row_searchSupplier['Fax No.'].'\',
				\''.$row_searchSupplier['E-Mail'].'\',
				\'\',
				0,
				0,
				0,
				0,
				0,
				CAST(\''.date('Y-d-m', strtotime("+".intval($row_searchSupplier['Plazo total entrega (dias)'])." days")).'\' AS DATETIME),
				\'1753-01-01\',
				0,
				0,
				0,
				0,
				0,
				\'1753-01-01\',
				\'1753-01-01\',
				0,
				0,
				\'\',
				0,
				0,
				\'1753-01-01\',
				0,
				\''.$row_searchSupplier['Phone No.'].'\',
				0
				
			);';
			
			
			$stmt_createOrder = sqlsrv_query( $connNavision, $sql);
			if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
				$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -10 Error al crear la cabecera del pedido de compra generado en Navision";
				continue;
			}
			//Notificamos por Discord
			file_get_contents('https://icfbot.ferreteriacampollano.com/message.php?channel='.$discord_channel.'&msg='.urlencode($orderNo." pedido generado del proveedor (".$row_searchSupplier['No.'].") ".$row_searchSupplier['Name'].$row_searchSupplier['Name 2']));
			
			
			//Actualizamos el maximo numero de la serie
			$startingNo = date('y').'PC00001';
			$sql='UPDATE ICF.dbo.[Ind. Campollano Ferretería S.L$No. Series Line] SET 
					[Last No. Used]=\''.$orderNo.'\',
					[Last Date Used]=\''.$formattedDate.'\'		
				WHERE [Series Code]=\'C-PED\' AND [Last No. Used]=\''.$previousNo.'\' AND [Open]=1';
			$stmt_updateSerie = sqlsrv_query( $connNavision, $sql);	
			if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
				$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -11 Ocurrio un error al actualizar la serie de pedidos de compra.";
				continue;
			}	
	}
	//Insertamos la línea o modificamos la línea
	//Buscamos si la referencia y la variante esta ya en el pedido
	$lineNo=null;
	$sql='SELECT TOP 1 * FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Line] tl where 
			tl.[Document No.]=\''.$orderNo.'\' AND
			tl.[No.]=\''.$line["code"].'\' AND 
			tl.[Variant Code]=\''.$line["variant"].'\' AND
			tl.[Buy-from Vendor No.]=\''.$row_searchSupplier['No.'].'\'
			ORDER BY [Line No.] DESC';
	
	
	$stmt_searchLine = sqlsrv_query( $connNavision, $sql);	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -12 Error en la consulta de busqueda de linea abierta en pedido de Navision";
		continue;
	}
	if( $stmt_searchLine !== false) {
		if ($row_searchLine = sqlsrv_fetch_array( $stmt_searchLine, SQLSRV_FETCH_ASSOC)){
			$lineNo = $row_searchLine['Line No.'];
			$qtyLine=$row_searchLine['Quantity'];
		}
	}
	if($lineNo){
		//Existe la línea actualizamos la cantidad
		//El producto ya esta en el traspaso, incrementamos la cantidad
		$newQtyLine=$qtyLine+$line["quantity"];
		$sql='UPDATE ICF.dbo.[Ind. Campollano Ferretería S.L$Purchase Line] SET 
			[Quantity]='.$newQtyLine.',
			[Outstanding Quantity]='.$newQtyLine.',
			[Qty. to Invoice]='.$newQtyLine.',
			[Qty. to Receive]='.$newQtyLine.',
			[Line Discount Amount]=('.$newQtyLine.' * [Direct Unit Cost]) * ([Line Discount %] / 100),
			[Amount]=('.$newQtyLine.' * [Unit Cost (LCY)]),
			[Amount Including VAT]=ROUND(('.$newQtyLine.' * [Unit Cost (LCY)])+(([VAT %]/100)*('.$newQtyLine.' * [Unit Cost (LCY)])),2),
			[Unit Price (LCY)]=ROUND('.$row_searchItem['Unit Price'].',3),
			[Outstanding Amount]=ROUND(('.$newQtyLine.' * [Unit Cost (LCY)])+(([VAT %]/100)*('.$newQtyLine.' * [Unit Cost (LCY)])),2),
			[Outstanding Amount (LCY)]=ROUND(('.$newQtyLine.' * [Unit Cost (LCY)])+(([VAT %]/100)*('.$newQtyLine.' * [Unit Cost (LCY)])),2),
			[VAT Base Amount]=('.$newQtyLine.' * [Unit Cost (LCY)]),
			[Unit Cost]=ROUND([Direct Unit Cost]+(([Direct Unit Cost]) * ([Line Discount %] / 100)),4),
			[Line Amount]=('.$newQtyLine.' * [Unit Cost (LCY)]),
			[Quantity (Base)]='.$newQtyLine.',
			[Outstanding Qty. (Base)]='.$newQtyLine.',
			[Qty. to Invoice (Base)]='.$newQtyLine.',
			[Qty. to Receive (Base)]='.$newQtyLine.',
			[Importe pendiente base]=('.$newQtyLine.' * [Unit Cost (LCY)]),
			[Importe pendiente base (DL)]=('.$newQtyLine.' * [Unit Cost (LCY)]),
			[Peso]=[Gross Weight]*'.$newQtyLine.'
		WHERE [Document No.]=\''.$orderNo.'\' AND [Line No.]=\''.$lineNo.'\' AND [No.]=\''.$line["code"].'\' AND [Variant Code]=\''.$line["variant"].'\'';
		
		$stmt_updateLine = sqlsrv_query( $connNavision, $sql);	

		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -13 Error actualizando la linea del pedido de Navision";
			continue;
		}	
	}else{
	//No existe la línea, la insertamos
	//primero calculamos el numero de linea que corresponde
		$sql = 'SELECT MAX([Line No.]) as [Line No.] FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Line] tl WHERE tl.[Document No.]=\''.$orderNo.'\'';
		$stmt_searchMaxLineNo = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -14 Error en la consulta de busqueda de la ultima linea del pedido de Navision";
			continue;
		}
		if( $stmt_searchMaxLineNo !== false) {
			if ($row_searchMaxLineNo = sqlsrv_fetch_array( $stmt_searchMaxLineNo, SQLSRV_FETCH_ASSOC)){
				$lineNo = $row_searchMaxLineNo["Line No."];
			}else{
				$lineNo = 0;
			}
		}
		//Buscamos los descuentos de compra del grupo de descuento o del articulo
		$sql='SELECT TOP 1 * FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Line Discount] tl where 
			(tl.[Item No.]=\''.$row_searchItem['Item Disc. Group'].'\' OR tl.[Item No.]=\''.$row_searchItem['No.'].'\') AND 
			[Vendor No.]=\''.$row_searchSupplier['No.'].'\' AND
			[Starting Date] <= GETDATE() AND ([Ending Date]>GETDATE() OR [Ending Date]=CAST(\'1753-01-01\' AS DATETIME))';
		
	
		$stmt_searchDiscount = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -15 Error en la consulta de busqueda de descuento de compra";
			continue;
		}
		if( $stmt_searchDiscount !== false) {
			if ($row_searchDiscount = sqlsrv_fetch_array( $stmt_searchDiscount, SQLSRV_FETCH_ASSOC)){
				$lineDiscount = $row_searchDiscount['Line Discount %'];
				$lineDiscount1 = $row_searchDiscount['% Descuento 1'];
				$lineDiscount2 = $row_searchDiscount['% Descuento 2'];
				$lineDiscount3 = $row_searchDiscount['% Descuento 3'];
				$lineDiscount4 = $row_searchDiscount['% Descuento 4'];
			}else{
				$lineDiscount = 0;
				$lineDiscount1 = 0;
				$lineDiscount2 = 0;
				$lineDiscount3 = 0;
				$lineDiscount4 = 0;
			}
		}
		
		//Buscamos el precio de compra del articulo
		$sql='SELECT TOP 1 * FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Price] tl where 
			tl.[Item No.]=\''.$row_searchItem['No.'].'\' AND tl.[Vendor No.]=\''.$row_searchSupplier['No.'].'\' AND 
			([Variant Code]=\''.$line['variant'].'\' OR [Variant Code]=\'\') AND [Starting Date] <= GETDATE() AND ([Ending Date]>GETDATE() OR [Ending Date]=CAST(\'1753-01-01\' AS DATETIME))';
	
		$stmt_searchPrice = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -16 Error en la consulta de busqueda de precio de compra";
			continue;
		}
		if( $stmt_searchPrice !== false) {
			if (!$row_searchPrice = sqlsrv_fetch_array( $stmt_searchPrice, SQLSRV_FETCH_ASSOC)){
				$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -17 Error el articulo no tiene precio de compra";
				continue;
			}
		}
		
		//Incrementamos la linea
		$lineNo+=10000;
		
		$directUnitCost=round($row_searchPrice["Direct Unit Cost"],4);
		$costWithDiscount=round($directUnitCost-($directUnitCost*($lineDiscount/100)),4);
		$vatPercent=$row_searchItem["VAT %"];
		$unitDiscount=($directUnitCost*($lineDiscount/100));
		$amount=$line["quantity"]*$costWithDiscount;
		$totalWithVat=round($amount+($amount*($vatPercent/100)),2);
		
		$sql='INSERT INTO ICF.dbo.[Ind. Campollano Ferretería S.L$Purchase Line](
			[Document Type],
			[Document No.], 
			[Line No.], 
			[Buy-from Vendor No.], 
			[Type],
			[No.],
			[Location Code],
			[Posting Group],
			[Expected Receipt Date],
			Description,
			[Description 2],
			[Unit of Measure],
			Quantity,
			[Outstanding Quantity],
			[Qty. to Invoice],
			[Qty. to Receive],
			[Direct Unit Cost],
			[Unit Cost (LCY)],
			[VAT %],
			[Line Discount %], 
			[Line Discount Amount],
			Amount, 
			[Amount Including VAT],
			[Unit Price (LCY)],
			[Allow Invoice Disc.], 
			[Gross Weight],
			[Net Weight],
			[Units per Parcel],
			[Unit Volume],
			[Appl.-to Item Entry],
			[Shortcut Dimension 1 Code],
			[Shortcut Dimension 2 Code],
			[Job No.], [Indirect Cost %],
			[Outstanding Amount],
			[Qty. Rcd. Not Invoiced], 
			[Amt. Rcd. Not Invoiced], 
			[Quantity Received], 
			[Quantity Invoiced], 
			[Receipt No.], 
			[Receipt Line No.],
			[Profit %], 
			[Pay-to Vendor No.], 
			[Inv. Discount Amount],
			[Vendor Item No.],
			[Sales Order No.],
			[Sales Order Line No.],
			[Drop Shipment], 
			[Gen. Bus. Posting Group],
			[Gen. Prod. Posting Group],
			[VAT Calculation Type],
			[Transaction Type],
			[Transport Method], 
			[Attached to Line No.],
			[Entry Point], 
			Area,
			[Transaction Specification], 
			[Tax Area Code], 
			[Tax Liable], 
			[Tax Group Code], 
			[Use Tax],
			[VAT Bus. Posting Group],
			[VAT Prod. Posting Group],
			[Currency Code],
			[Outstanding Amount (LCY)], 
			[Amt. Rcd. Not Invoiced (LCY)],
			[Blanket Order No.], 
			[Blanket Order Line No.], 
			[VAT Base Amount],
			[Unit Cost],
			[System-Created Entry],
			[Line Amount], 
			[VAT Difference],
			[Inv. Disc. Amount to Invoice],
			[VAT Identifier],
			[IC Partner Ref. Type],
			[IC Partner Reference],
			[Prepayment %], 
			[Prepmt. Line Amount],
			[Prepmt. Amt. Inv.], 
			[Prepmt. Amt. Incl. VAT],
			[Prepayment Amount], 
			[Prepmt. VAT Base Amt.], 
			[Prepayment VAT %],
			[Prepmt. VAT Calc. Type],
			[Prepayment VAT Identifier],
			[Prepayment Tax Area Code], 
			[Prepayment Tax Liable], 
			[Prepayment Tax Group Code],
			[Prepmt Amt to Deduct], 
			[Prepmt Amt Deducted], 
			[Prepayment Line],
			[Prepmt. Amount Inv. Incl. VAT],
			[Prepmt. Amount Inv. (LCY)], 
			[IC Partner Code], 
			[Prepayment VAT Difference],
			[Prepmt VAT Diff. to Deduct], 
			[Prepmt VAT Diff. Deducted],
			[Job Task No.], 
			[Job Line Type], 
			[Job Unit Price], 
			[Job Total Price], 
			[Job Line Amount],
			[Job Line Discount Amount],
			[Job Line Discount %],
			[Job Unit Price (LCY)], 
			[Job Total Price (LCY)],
			[Job Line Amount (LCY)],
			[Job Line Disc. Amount (LCY)],
			[Job Currency Factor],
			[Job Currency Code], 
			[Prod. Order No.], 
			[Variant Code], 
			[Bin Code], 
			[Qty. per Unit of Measure], 
			[Unit of Measure Code],
			[Quantity (Base)], 
			[Outstanding Qty. (Base)],
			[Qty. to Invoice (Base)],
			[Qty. to Receive (Base)],
			[Qty. Rcd. Not Invoiced (Base)],
			[Qty. Received (Base)],
			[Qty. Invoiced (Base)], 
			[FA Posting Date],
			[FA Posting Type],
			[Depreciation Book Code], 
			[Salvage Value],
			[Depr. until FA Posting Date],
			[Depr. Acquisition Cost],
			[Maintenance Code], 
			[Insurance No.], 
			[Budgeted FA No.],
			[Duplicate in Depreciation Book],
			[Use Duplication List],
			[Responsibility Center],
			[Cross-Reference No.], 
			[Unit of Measure (Cross Ref.)],
			[Cross-Reference Type],
			[Cross-Reference Type No.], 
			[Item Category Code], 
			Nonstock,
			[Purchasing Code],
			[Product Group Code],
			[Special Order],
			[Special Order Sales No.], 
			[Special Order Sales Line No.], 
			[Completely Received],
			[Requested Receipt Date],
			[Promised Receipt Date],
			[Lead Time Calculation], 
			[Inbound Whse. Handling Time], 
			[Planned Receipt Date],
			[Order Date],
			[Allow Item Charge Assignment],
			[Return Qty. to Ship], 
			[Return Qty. to Ship (Base)],
			[Return Qty. Shipped Not Invd.],
			[Ret. Qty. Shpd Not Invd.(Base)],
			[Return Shpd. Not Invd.],
			[Return Shpd. Not Invd. (LCY)], 
			[Return Qty. Shipped], 
			[Return Qty. Shipped (Base)],
			[Return Shipment No.], 
			[Return Shipment Line No.], 
			[Return Reason Code], 
			[Pmt. Disc. Rcd. Amount], 
			[EC %], 
			[EC Difference], 
			[Prepayment EC %],
			[% Descuento 1], 
			[% Descuento 2], 
			[% Descuento 3], 
			[% Descuento 4],
			[ViejoPrepmt. Amount Inv. (LCY)], 
			[Estado línea pedido], 
			[Cantidad inicial], 
			[Cantidad inicial base],
			[Sujeto IRPF], 
			[% IRPF], 
			[Base IRPF],
			[Base IRPF (DL)],
			[Importe IRPF],
			[Importe IRPF (DL)],
			[Proveedor extranjero], 
			[Base imponible (DUA)], 
			[Fecha admisión (DUA)], 
			[Identificación DUA],
			[Cod. Marca], 
			Historic, 
			[Importe pendiente base],
			[Importe pendiente base (DL)], 
			[Estado documento],
			[Cod. unidad medida precio],
			[Coste unit. directo UM precio], 
			[Unit Cost UM Precio],
			[Cdad por UM Precio], 
			[Descripcion UM precio],
			[Pasar a pedido],
			[Procede de No oferta],
			[Fecha cierre linea], 
			[Es devolucion documento],
			[Dto por pesos], 
			[Drop Shipment simul], 
			[Sales Order No. simul], 
			[Sales Order Line No. simul],
			[Cantidad modif.],
			[No albaran dev],
			[No linea alb. Dev.],
			[No linea alb], 
			[Routing No.],
			[Operation No.],
			[Work Center No.],
			Finished, 
			[Prod. Order Line No.],
			[Overhead Rate],
			[MPS Order], 
			[Planning Flexibility],
			[Safety Lead Time], 
			[Routing Reference No.],
			[Es Devolucion], 
			[Nº línea lista materiales],
			[BOM Item No.],
			[Skip purchase discount],
			Peso,
			[Pedido WEB]
		)VALUES(
			1,
			\''.$orderNo.'\', 
			'.$lineNo.',
			\''.$row_searchSupplier['No.'].'\',
			2,
			\''.$row_searchItem['No.'].'\',
			\''.$store.'\',
			\''.$row_searchItem['Inventory Posting Group'].'\',
			CAST(\''.date('Y-d-m', strtotime("+".intval($row_searchSupplier['Plazo total entrega (dias)'])." days")).'\' AS DATETIME),
			\''.mssql_escape($row_searchItem['Description']).'\',
			\''.mssql_escape($row_searchItem['Description 2']).'\',
			\'Unidades\',
			'.$line["quantity"].',
			'.$line["quantity"].',
			'.$line["quantity"].',
			'.$line["quantity"].',
			'.floatval($directUnitCost).',
			'.floatval($costWithDiscount).',
			'.$vatPercent.',
			'.$lineDiscount.',
			'.floatval($line["quantity"]*$unitDiscount).',
			'.floatval($amount).',
			'.floatval($totalWithVat).',
			'.floatval($row_searchItem["Unit Price"]).',
			1,
			'.($row_searchItem["Gross Weight"]!=""?$row_searchItem["Gross Weight"]:'0').',
			0,
			0,
			0,
			0,
			\'\',
			\'\',
			\'\',
			0,
			'.floatval($totalWithVat).',
			0,
			0,
			0,
			0,
			\'\',
			0,
			0,
			\''.$row_searchPaySupplier['No.'].'\',
			0,
			\''.$row_searchItem['Vendor Item No.'].'\',
			\'\',
			0,
			0,
			\''.$row_searchPaySupplier['Gen. Bus. Posting Group'].'\',
			\''.$row_searchItem['Gen. Prod. Posting Group'].'\',
			0,
			\'\',
			\'\',
			0,
			\'\',
			\'\',
			\'\',
			\'\',
			0,
			\'\',
			0,
			\''.$row_searchPaySupplier['VAT Bus. Posting Group'].'\',
			\''.$row_searchItem['VAT Prod. Posting Group'].'\',
			\'\',
			'.floatval($totalWithVat).',
			0,
			\'\',
			0,
			'.floatval($amount).',
			'.floatval($costWithDiscount).',
			0,
			'.floatval($line["quantity"]*$costWithDiscount).',
			0,
			0,
			\'VAT21\',
			0,
			\'\',
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			\'\',
			\'\',
			0,
			\'\',
			0,
			0,
			0,
			0,
			0,
			\'\',
			0,
			0,
			0,
			\'\',
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			\'\',
			\'\',
			\''.$line["variant"].'\',
			\'\',
			1,
			\'UD\',
			'.$line["quantity"].',
			'.$line["quantity"].',
			'.$line["quantity"].',
			'.$line["quantity"].',
			0,
			0,
			0,
			\'1753-01-01\',
			0,
			\'\',
			0,
			0,
			0,
			\'\',
			\'\',
			\'\',
			\'\',
			0,
			\'\',
			\''.$row_searchItem['Vendor Item No.'].'\',
			\'UD\',
			2,
			\''.$row_searchSupplier['No.'].'\',
			\''.$row_searchItem['Item Category Code'].'\',
			0,
			\'\',
			\''.$row_searchItem['Product Group Code'].'\',
			0,
			\'\',
			0,
			0,
			\'1753-01-01\',
			\'1753-01-01\',
			\''.($row_searchItem['Lead Time Calculation']!=''?$row_searchItem['Lead Time Calculation']:$row_searchSupplier['Lead Time Calculation']).'\',
			\'\',
			CAST(\''.date('Y-d-m', strtotime("+".intval($row_searchSupplier['Plazo total entrega (dias)'])." days")).'\' AS DATETIME),
			\''.$formattedDate.'\',
			1,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			\'\',
			0,
			\'\',
			0,
			0,
			0,
			0,
			'.floatval($lineDiscount1).',
			'.floatval($lineDiscount2).',
			'.floatval($lineDiscount3).',
			'.floatval($lineDiscount4).',
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			\'\',
			0,
			\'1753-01-01\',
			\'\',
			\''.$row_searchItem['Cod. Marca'].'\',
			0,
			'.floatval($line["quantity"]*$costWithDiscount).',
			'.floatval($line["quantity"]*$costWithDiscount).',
			0,
			\''.$row_searchItem['Unidad medida precio'].'\',
			'.floatval($row_searchPrice["Direct Unit Cost"]*$row_searchItem["Qty. per Unit of Measure"]).',
			'.floatval($costWithDiscount*$row_searchItem["Qty. per Unit of Measure"]).',
			'.$row_searchItem["Qty. per Unit of Measure"].',
			\''.$row_searchItem["Unit Measure Desc"].'\',
			0,
			\'\',
			\'1753-01-01\',
			0,
			0,
			0,
			\'\',
			0,
			0,
			\'\',
			0,
			0,
			\'\',
			\'\',
			\'\',
			0,
			0,
			0,
			0,
			0,
			\'0\',
			0,
			0,
			0,
			\'\',
			'.$row_searchPrice["Skip purchase discount"].',
			'.($row_searchItem["Gross Weight"]!=""?$row_searchItem["Gross Weight"]*$line["quantity"]:'0').',
			0
		);';
		$stmt_insertLine = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			echo json_encode(["result"=>-8, "text"=>"Ocurrio un error al insertar la linea ".$lineNo." del traspaso."]);
			$responseErrors[]="Error saleorderlineid: ".$line["saleorderlineid"]." -18 Error al insertar la linea en el pedido de compra";
			continue;
		}
	}
	
	//Agregamos la linea al resultado
	$response[]=["saleorderlineid"=>$line["saleorderlineid"], "code"=>$line["code"], "variant"=>$line["variant"], "quantity"=>$line["quantity"], "order"=>$orderNo];
	
	
	
}
echo json_encode(["result"=>1, "data"=>$response, "errors"=>$responseErrors], JSON_UNESCAPED_UNICODE);
?>
