<?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", "ConnectionPooling" => "1" ,"MultipleActiveResultSets"=>'1', "TraceOn"=>"0");
$scriptPath = 'do-NAVISION-sendDiscordMessage.php';
//$sDiscordChannel= "1075385202303586324"; // Antonio, pruebas
$sDiscordChannel= "806434577437556776"; // Dpto. Compras

// 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;
}

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


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

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

if (!isset($data['code']) || $data['code']=='') {
	echo json_encode(["result" => -3, "text" => "No se ha indicado pedido para crear pedido de venta en ICF_FrasCab"]);
	die;
}

if (!isset($data['lines']) || !is_array($data['lines']) || count($data['lines']) == 0) {
	echo json_encode(["result" => -4, "text" => "No se ha indicado líneas para crear pedido de venta en ICF_FrasLinea"]);
	die;
}

$code = $data['code'];
if (strtoupper($code[0]) === 'N') 
	// Elimina la primera letra
	$code = substr($code, 1);

// Comprobar que no existe ya el pedido de venta en ICF_FrasCab para evitar duplicidades
$newCodFrasCab=null;
$saleOrderCode='';
$sql='SELECT Cod_FrasCab, CodFacturaTemp FROM dbo.[Ind. Campollano Ferretería S.L$ICF_FrasCab] fc where fc.[CodClientePS]=\'0001\' and fc.[CodPedidoPS]=\''.$code.'\'';

$stmNewExist = sqlsrv_query( $connNavision, $sql);	
if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
	echo json_encode(["result"=>-2, "text"=>"1 Ocurrio un error en la consulta SQL de Navision: ICF_FrasCab"]);
	die;
}
if( $stmNewExist !== false) {
	if($rowNewExist = sqlsrv_fetch_array( $stmNewExist, SQLSRV_FETCH_ASSOC)){			
		$newCodFrasCab=$rowNewExist["Cod_FrasCab"];
		$saleOrderCode=$rowNewExist["CodFacturaTemp"];
	}
}
$code_parent = substr($code,0,strpos($code,'-'));
//Si no hay ningun pedido de venta generado lo creamos
if(!$newCodFrasCab){
			
	// Si no existe se busca en Sales Header para ver si existe su padre y así poderlo copiar a ICF_FrasCab		
	$sql='SELECT 
	sh.[No.] as code,
	sh.[External Document No.] as code2,
	format(sh.[Order Date], \'yyyy-MM-dd HH:mm:ss\') as odate,
	CONCAT(sh.[Ship-to Name], sh.[Ship-to Name 2]) as customer_name,
	sh.[Ship-to Address] as address1,
	sh.[Ship-to Address 2] as address2,
	sh.[Ship-to Post Code] as cp,
	sh.[Ship-to City] as town,
	sh.[Ship-to County] as province,
	sh.[Payment Terms Code] as payment_term_code,
	sh.[Payment Method Code] as payment_method_code,
	sh.[VAT Registration No.] as customer_vat,
	sh.[Telefono] as phone,		
	sh.[Sell-to Customer No.] as customer_code,		
	sh.[Email Documento] as email,
	sh.[Sell-to Contact] as contact		
	FROM dbo.[Ind. Campollano Ferretería S.L$Sales Header] sh
	WHERE sh.[No.]=\''.$code_parent.'\'';
	$stmt_sale_header = sqlsrv_query( $connNavision, $sql);	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		echo json_encode(["result"=>-5, "text"=>"Ocurrio un error en la consulta SQL de Navision: Sales Header"]);
		print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
		die;
	}
	if( $stmt_sale_header !== false) {		
		$saleheader=[];
		while($row3 = sqlsrv_fetch_array( $stmt_sale_header, SQLSRV_FETCH_ASSOC)){			
			foreach($row3 as $key=>$object){
				//$saleheader[$key]=!is_object($object)?utf8_encode($object):$object;
				$saleheader[$key]=!is_object($object)?$object:$object;
			}			
		}
		
		if (count($saleheader)!=0){
			// Insertamos cabecera del pedido en ICF_FrasCab	si no existia
			// Si existe se borra del FrasCabLineas las lineas y la cabecera se queda igual
			$Cod_FrasCab='';
			$sql = "INSERT INTO ".'[Ind. Campollano Ferretería S.L$ICF_FrasCab]'."(
			FechaModificacion,
			HoraModificacion,
			CodClientePS,
			Nombre,
			Apellido1,
			Apellido2,
			Direccion1,
			Direccion2,
			CP,
			Poblacion,
			Provincia,
			Contacto,
			TerminosPago,
			FormaPago,
			FechaRegistro,
			FechaVencimiento,
			TextoRegistro,
			DNI,
			Telefono,
			CodClientePagadorPS,
			NombrePagador,
			Apellido1Pagagor,
			Apellido2Pagagor,
			CodFacturaTemp,
			Procesado,
			ConceptoGastoEnvio,
			Direccion1Envio,
			Direccion2Envio,
			CPEnvio,
			PoblacionEnvio,
			ProvinciaEnvio,
			CodPedidoPS,
			Observaciones,
			CodCliente,
			RefPedidoPS,
			EmpresaPagador,
			EmpresaEnvios,
			Email,
			[Texto Error],
			Solucionado,
			Error,
			ProcesarAlbaran,
			NoAlbaran,
			codPreparacion,
			ProcesarFactura,
			CodFacturaReg,
			AnularRestos
			) VALUES (
			'".date("d-m-Y", strtotime($saleheader['odate']))."',
			'".date("1754-01-01 H:i:s.000", strtotime($saleheader['odate']))."',
			'0001',
			'".strtoupper(mssql_escape($saleheader['customer_name']))."',
			'',
			'',
			'".mssql_escape($saleheader['address1'])."',
			'".mssql_escape($saleheader['address2'])."',
			'".mssql_escape($saleheader['cp'])."',
			'".mssql_escape($saleheader['town'])."',
			'".mssql_escape($saleheader['province'])."',
			'".mssql_escape($saleheader['contact'])."',
			'".mssql_escape($saleheader['payment_term_code'])."',
			'".mssql_escape($saleheader['payment_method_code'])."',
			'".date("d-m-Y", strtotime($saleheader['odate']))."',
			'".date("d-m-Y", strtotime($saleheader['odate']))."',
			'',
			'".mssql_escape($saleheader['customer_vat'])."',
			'".mssql_escape($saleheader['phone'])."',
			'',
			'".strtoupper(mssql_escape($saleheader['customer_name']))."',
			'',
			'',
			'',
			0,
			'',
			'".mssql_escape($saleheader['address1'])."',
			'".mssql_escape($saleheader['address2'])."',
			'".mssql_escape($saleheader['cp'])."',
			'".mssql_escape($saleheader['town'])."',
			'".mssql_escape($saleheader['province'])."',
			'".$code."',
			'',
			'".strtoupper(mssql_escape($saleheader['customer_code']))."',
			'".strtoupper(mssql_escape($saleheader['code2']))."',
			'',
			'',
			'".strtoupper(mssql_escape($saleheader['email']))."',
			'',
			0,
			0,
			0,
			0,
			'',
			0,
			'',
			0);";
			
			//echo $sql;
			$stmt = sqlsrv_query( $connNavision, $sql );
			if( $stmt === false) {
				echo json_encode(["result"=>-6, "text"=>"Ocurrio un error al insertar el pedido en ICF_FrasCab"]);
				print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
				die;
			}
			// Obtener el id que se acaba de insertar
			$sql="SELECT MAX(Cod_FrasCab) Cod_FrasCab FROM ".'[Ind. Campollano Ferretería S.L$ICF_FrasCab]';
			$stmt = sqlsrv_query( $connNavision, $sql );
			if( $stmt === false) {
				die( print_r( sqlsrv_errors(), true) );
			}
			$row_max = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
			$Cod_FrasCab=$row_max["Cod_FrasCab"];
			
			// ----------------- Líneas -----------------------
			if ($Cod_FrasCab){
				$line = 0;
				foreach($data['lines'] as $saleline){
					$line++;
					$sql = "INSERT INTO ".'[Ind. Campollano Ferretería S.L$ICF_FrasLinea]'."(
					Cod_FrasCab,
					NLinea,
					Cod_NAV,
					Cantidad,
					Descripcion,
					PrecioVenta,
					GrupoIva,
					Descuento,
					ImporteLinea,
					Procesado,
					Variante,
					ProcesaAlbaran,
					CantidadAProcesar,
					CantidadProcesada,
					CodAlmacen,
					ProcesarFactura,
					CantidadAFacturar,
					CantidadFacturada,
					NlineaPedido					
					) VALUES (
						".$Cod_FrasCab.",
						".$line.",
						'".mssql_escape($saleline['product_code'])."',
						'".$saleline['quantity']."',
						'".mssql_escape($saleline['product_name'])."',
						'".$saleline['pricebase']."',
						'".$saleline['taxperc']."',
						'".$saleline['discountperc']."',
						'".$saleline['pricetotal']."',
						0,
						'".mssql_escape($saleline['variant_code'])."',
						0,
						0,
						0,
						'".mssql_escape($saleline['store_code'])."',
						0,
						0,
						0,
						'".$saleline['linenum_navision']."'
					);";
					
					$stmt = sqlsrv_query( $connNavision, $sql );
					if( $stmt === false) {
						echo json_encode(["result"=>-7, "text"=>"Ocurrio un error al insertar las lineas de pedido en ICF_FrasLinea"]);
						print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
						die;
					}
				}				
				
				
				// -------------- Lanzar CODE UNIT --------------------
				$sql = "INSERT INTO ".'dbo.[Ind. Campollano Ferretería S.L$Job Queue Entry]'." ([ID],[User ID], [Last Modified Date_Time], [Expiration Date_Time], [Earliest Start Date_Time], [Object Type to Run],[Object ID to Run],[Response Required],[Maximum No. of Attempts to Run],[No. of Attempts to Run],[Status],[Priority],[Record ID to Process],[Parameter String],[Recurring Job],[No. of Minutes between Runs],[Run on Mondays],[Run on Tuesdays],[Run on Wednesdays],[Run on Thursdays],[Run on Fridays],[Run on Saturdays],[Run on Sundays],[Starting Time],[Ending Time])
					   VALUES(NEWID(),'jag',GETDATE(),'1753-01-01 00:00:00.000',DATEADD(HOUR, -2, GETDATE()),5,50003,0,3,3,0,0,CONVERT(varbinary(max), ''),'',0,0,0,0,0,0,0,1,1,'1754-01-01 21:00:00.000','1753-01-01 00:00:00.000')";
				$stmt = sqlsrv_query( $connNavision, $sql );
				if( $stmt === false) {
					echo json_encode(["result"=>-8,"text"=>"Error al solicitar ejecucion de CodeUnit"]);
					die( print_r( sqlsrv_errors(), true) );
				}
				
				// -------------- Obtener número código de pedido -----
				//Esperar numeración de factura creado
				$retries=0;
				$found=false;
				$error=false;
				while($retries<120 && $found==false){
					$sql = 'SELECT fc.[CodFacturaTemp] as CodFacturaTemp, [Texto Error] as error
							FROM [dbo].[Ind. Campollano Ferretería S.L$ICF_FrasCab] fc
							WHERE fc.[CodClientePS]=\'0001\' and fc.[CodPedidoPS]=\''.$code.'\'';
					$smtp=sqlsrv_query( $connNavision, $sql );
					if( $stmt !== false) {
						if($row = sqlsrv_fetch_array( $smtp, SQLSRV_FETCH_ASSOC)){
							if($row['CodFacturaTemp']!=null && $row['CodFacturaTemp']!=0 && $row['CodFacturaTemp']!=''){
								$saleOrderCode=$row['CodFacturaTemp'];
								$found=true;
																	
								//Cabecera
								//Modificamos el pedido generado de sibling para ajustar los datos de cabecera con el pedido padre
								$sql = 'SELECT * FROM [dbo].[Ind. Campollano Ferretería S.L$Sales Header] WHERE [No.]=\''.$code_parent.'\'';
								$smtpOriginalOrder=sqlsrv_query( $connNavision, $sql );
								if($rowOriginalOrder = sqlsrv_fetch_array( $smtpOriginalOrder, SQLSRV_FETCH_ASSOC)){
									$sql = "UPDATE ".'dbo.[Ind. Campollano Ferretería S.L$Sales Header]'." SET 
									[Estado Oferta]=0, 
									[Bill-to Customer No.]='".mssql_escape($rowOriginalOrder["Bill-to Customer No."])."',
									[Bill-to Name]='".mssql_escape($rowOriginalOrder["Bill-to Name"])."',
									[Bill-to Name 2]='".mssql_escape($rowOriginalOrder["Bill-to Name 2"])."',
									[Bill-to Address]='".mssql_escape($rowOriginalOrder["Bill-to Address"])."',
									[Bill-to Address 2]='".mssql_escape($rowOriginalOrder["Bill-to Address 2"])."',
									[Bill-to City]='".mssql_escape($rowOriginalOrder["Bill-to City"])."',
									[Bill-to Contact]='".mssql_escape($rowOriginalOrder["Bill-to Contact"])."',
									[Ship-to Code]='".mssql_escape($rowOriginalOrder["Ship-to Code"])."',
									[Ship-to Name]='".mssql_escape($rowOriginalOrder["Ship-to Name"])."',
									[Ship-to Name 2]='".mssql_escape($rowOriginalOrder["Ship-to Name 2"])."',
									[Ship-to Address]='".mssql_escape($rowOriginalOrder["Ship-to Address"])."',
									[Ship-to Address 2]='".mssql_escape($rowOriginalOrder["Ship-to Address 2"])."',
									[Ship-to City]='".mssql_escape($rowOriginalOrder["Ship-to City"])."',
									[Ship-to Contact]='".mssql_escape($rowOriginalOrder["Ship-to Contact"])."',
									[Sell-to Customer Name]='".mssql_escape($rowOriginalOrder["Sell-to Customer Name"])."',
									[Sell-to Customer Name 2]='".mssql_escape($rowOriginalOrder["Sell-to Customer Name 2"])."',
									[Sell-to Address]='".mssql_escape($rowOriginalOrder["Sell-to Address"])."',
									[Sell-to Address 2]='".mssql_escape($rowOriginalOrder["Sell-to Address 2"])."',
									[Sell-to City]='".mssql_escape($rowOriginalOrder["Sell-to City"])."',
									[Sell-to Contact]='".mssql_escape($rowOriginalOrder["Sell-to Contact"])."',
									[Payment Terms Code]='".mssql_escape($rowOriginalOrder["Payment Terms Code"])."',
									[Shipment Method Code]='".mssql_escape($rowOriginalOrder["Shipment Method Code"])."',
									[Ship]='".mssql_escape($rowOriginalOrder["Ship"])."',
									[Invoice]='".mssql_escape($rowOriginalOrder["Invoice"])."',
									[Salesperson Code]='".mssql_escape($rowOriginalOrder["Salesperson Code"])."',
									[Payment Method Code]='".mssql_escape($rowOriginalOrder["Payment Method Code"])."',
									[Shipping Agent Code]='".mssql_escape($rowOriginalOrder["Shipping Agent Code"])."',
									[Invoice Discount Calculation]='".mssql_escape($rowOriginalOrder["Invoice Discount Calculation"])."',
									[Assigned User ID]='".mssql_escape($rowOriginalOrder["Assigned User ID"])."',
									[Creation user]='".mssql_escape($rowOriginalOrder["Creation user"])."',
									[Pedido WEB]='".mssql_escape($rowOriginalOrder["Pedido WEB"])."',
									[External Document No.]='".mssql_escape($rowOriginalOrder["External Document No."])."',		
									[Package Tracking No.]='".$code_parent."'		
									where [No.]='".$saleOrderCode."' and [Document Type]=1";
									$smtpSaveOrder=sqlsrv_query( $connNavision, $sql );
									if( $smtpSaveOrder === false) {
										echo json_encode(["result"=>-9,"text"=>"Error al actualizar la cabecera del pedido sibling"]);
										die;
									}
									//Actualizamos el campo de descuento de las lineas para que se visualice bien en Navision
									$sql = "UPDATE ".'dbo.[Ind. Campollano Ferretería S.L$Sales Line]'."
												SET [% Descuento 1] = (
													SELECT TOP 1 fl.[Descuento]
													FROM ".'[Ind. Campollano Ferretería S.L$ICF_FrasLinea]'." fl
													LEFT JOIN ".'[Ind. Campollano Ferretería S.L$ICF_FrasCab]'." fc ON fc.Cod_FrasCab = fl.Cod_FrasCab
													WHERE fc.CodFacturaTemp = ".'[Ind. Campollano Ferretería S.L$Sales Line]'.".[Document No.] 
													AND fl.NlineaPedido = ".'[Ind. Campollano Ferretería S.L$Sales Line]'.".[Line No.]
												)
											WHERE [Document No.] = '".$saleOrderCode."'";
									$smtpRemainsOrderLines=sqlsrv_query( $connNavision, $sql );
									if( $smtpRemainsOrderLines === false) {
										echo json_encode(["result"=>-10,"text"=>"Error al actualizar el campo descuento de las lineas"]);
										die;
									}

									// Se reemplaza en Navision el código de pedido generado por el original y se recupera ese código para ser usado por otro pedido
									// Tablas afectadas: ICF_FrasCab, Sales Header, Sales Line
									$sql = "UPDATE ".'[dbo].[Ind. Campollano Ferretería S.L$ICF_FrasCab]'."
												SET [CodFacturaTemp] = '$code'
											WHERE [CodFacturaTemp] = '$saleOrderCode'";
									$smtpOriginalCode=sqlsrv_query( $connNavision, $sql );
									if( $smtpOriginalCode === false) {
										echo json_encode(["result"=>-11,"text"=>"Error al actualizar el campo CodFacturaTemp con el codigo original en la tabla ICF_FrasCab"]);
										die;
									}
									$sql = "UPDATE ".'[dbo].[Ind. Campollano Ferretería S.L$Sales Header]'."
												SET [No.] = '$code'
											WHERE [No.] = '$saleOrderCode'";
									$smtpOriginalCode=sqlsrv_query( $connNavision, $sql );
									if( $smtpOriginalCode === false) {
										echo json_encode(["result"=>-12,"text"=>"Error al actualizar el campo [No.] con el codigo original en la tabla Sales Header"]);
										die;
									}		
									$sql = "UPDATE ".'[dbo].[Ind. Campollano Ferretería S.L$Sales Line]'."
												SET [Document No.] = '$code'
											WHERE [Document No.] = '$saleOrderCode'";
									$smtpOriginalCode=sqlsrv_query( $connNavision, $sql );
									if( $smtpOriginalCode === false) {
										echo json_encode(["result"=>-13,"text"=>"Error al actualizar el campo [Document No.] con el codigo original en la tabla Sales Line"]);
										die;
									}
									// Reutilizar el código para otro pedido
									// Tabla No. Series Line
									$sql='SELECT [Last No. Used] FROM dbo.[Ind. Campollano Ferretería S.L$No. Series Line] sl where sl.[Last No. Used]=\''.$saleOrderCode.'\' AND [Open]=1';
									$stmt_searchMaxNo=sqlsrv_query( $connNavision, $sql );										
									if( $stmt_searchMaxNo !== false) {
										if ($row_searchMaxNo = sqlsrv_fetch_array( $stmt_searchMaxNo, SQLSRV_FETCH_ASSOC)){
											$orderNo = $row_searchMaxNo["Last No. Used"];
											$prefix = substr($orderNo,0,4);
											$suffix = intval(substr($orderNo,4));
											$suffix--;
											$suffix = str_pad($suffix,strlen(substr($orderNo,4)),'0',STR_PAD_LEFT);
											$orderNew = $prefix.$suffix;
											$sql = "UPDATE ".'[dbo].[Ind. Campollano Ferretería S.L$No. Series Line]'."
														SET [Last No. Used] = '$orderNew'
													WHERE [Last No. Used] = '$saleOrderCode' AND [Open]=1";
											$smtpCode=sqlsrv_query( $connNavision, $sql );
											if( $smtpCode === false) {
												echo json_encode(["result"=>-14,"text"=>"Error al actualizar el campo [Last No. Used] con el codigo original en la tabla No. Series Line"]);
												die;
											}
										}
									}
									

									// Se devuelve el código de pedido original ya que se sustituido en Navision
									$saleOrderCode = $code;
								}
							}	
							if($row['error']!=null && $row['error']!=''){
								$error=$row['error'];
							}							
						}
					}
					usleep(500000);
					$retries++;
				}
				if($error!==false){
					$error = iconv("UTF-8", "Windows-1252",mb_strtoupper($error, "UTF-8"));		
					echo json_encode(["result"=>-240,"text"=>"Error pedido restos FrasCab: ".$error]);
					die;
				}else
				if(!$found){
					echo json_encode(["result"=>-15,"text"=>"Error, no se pudo obtener la numeracion de pedido de venta"]);
					die;
				}
				
				
				
			}							
		}
	}
		
}


// ANULACIÓN DE RESTOS DEL PEDIDO ORIGINAL					
$sql = 'UPDATE dbo.[Ind. Campollano Ferretería S.L$Sales Line]'." SET
	[Quantity]= 0,
	[Outstanding Quantity]=0,
	[Qty. to Invoice]=0,
	[Qty. to Ship]=0,
	[Line Discount Amount]= 0,
	[Amount]=0,
	[Amount Including VAT]=0,
	[Qty. Shipped Not Invoiced]=0,
	[Shipped Not Invoiced]=0,
	[Quantity Shipped]=0,
	[Quantity Invoiced]=0,
	[Outstanding Amount (LCY)]=0,
	[Shipped Not Invoiced (LCY)]=0,
	[VAT Base Amount]=0,
	[Line Amount]=0,
	[Inv. Disc. Amount to Invoice]=0,
	[Quantity (Base)]= 0,
	[Outstanding Qty. (Base)]=0,
	[Qty. to Invoice (Base)]=0,
	[Qty. to Ship (Base)]=0,
	[Qty. Shipped Not Invd. (Base)]=0,
	[Qty. Shipped (Base)]=0,
	[Qty. Invoiced (Base)]=0,
	[Completely Shipped]=1
WHERE [Document No.] = '$code_parent'";
$stmt=sqlsrv_query( $connNavision, $sql );
if( $stmt === false) {
	echo json_encode(["result"=>-16,"text"=>"Error al actualizar las lineas originales"]);
	die;
}


// Mandar al histórico pedido padre para que no lo tenga 
$sql = "UPDATE ".'dbo.[Ind. Campollano Ferretería S.L$Sales Header]'." SET [Historic]=1 WHERE [No.] = '".$code_parent."';";
$smtp=sqlsrv_query( $connNavision, $sql );
if( $smtp === false) {
	echo json_encode(["result"=>-17,"text"=>"Error al actualizar cabecera pedido para pasar a historico"]);
	die;
}

echo json_encode(["result"=>1, "data"=>["saleOrderCode"=>$saleOrderCode, "text"=>"Pedido de venta creado correctamente"]]);
?>
