<?php
/**   ------------------------------------------------------------
		CREA UN TRASPASO DE MATERIAL ENTRE ALMACENES EN NAVISION
	  ------------------------------------------------------------
*/   
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");

if(isset($_REQUEST["storeorigin"])) 
	$origin=$_REQUEST["storeorigin"]; 
else{
	echo json_encode(["result"=>-1 , "text"=>"Se debe proporcionar un codigo de almacen de origen"]);
	die;
}

if(isset($_REQUEST["storedestination"])) 
	$destination=$_REQUEST["storedestination"]; 
else{
	echo json_encode(["result"=>-2 , "text"=>"Se debe proporcionar un codigo de almacen de destino"]);
	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"], 
		"ALM02"=>["name"=>"ALMACEN 02 ROMICA", "name2"=>"", "address"=>"POLIG.IND.DE ROMICA AVENIDA A Nº34", "address2"=>"", "postcode"=>"02007", "city"=>"ALBACETE", "county"=>"Albacete"]
];
//Comprobamos que esten configurados los almacenes implicados
if(!isset($stores[$origin]) || !isset($stores[$destination])){
	echo json_encode(["result"=>-3 , "text"=>"Algun almacen implicado no esta configurado"]);
	die;
}	

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

$transferNo=null;
//Buscamos si hay un traspaso abierto ya creado por el usuario VAIO entre ambos almacenes
$sql='SELECT TOP 1 [No.] FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Header] th where th.[Transfer-from Code]=\''.$origin.'\' AND [Transfer-to Code]=\''.$destination.'\' AND [Status]=0 AND [Last Shipment No.]=\'\' AND [Assigned USER ID]=\'VAIO\' ORDER BY [Fecha emision documento] DESC, [No.] DESC';

$stmt_searchTransfer = sqlsrv_query( $connNavision, $sql);	
if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
	echo json_encode(["result"=>-7, "text"=>"Ocurrio un error en la consulta SQL de Navision: Buscando traspaso abierto."]);
	die;
}
if( $stmt_searchTransfer !== false) {
	if ($row_searchTransfer = sqlsrv_fetch_array( $stmt_searchTransfer, SQLSRV_FETCH_ASSOC)){
		$transferNo = $row_searchTransfer['No.'];
	}
}
//Si no hay traspaso abierto lo creamos
$formattedDate=null;
if(!$transferNo){
	//Obtenemos el No. del ultimo traspaso generado para incrementarlo
	$sql = 'SELECT MAX([No.]) as [No.] FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Header] th WHERE [No.] LIKE CAST(YEAR(GETDATE()) % 100 AS NVARCHAR(2)) + \'TR%\'';
	$stmt_searchMaxNo = sqlsrv_query( $connNavision, $sql);
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		echo json_encode(["result"=>-8, "text"=>"Ocurrio un error en la consulta SQL de Navision: Buscando ultimo traspaso."]);
		die;
	}
	if( $stmt_searchMaxNo !== false) {
		if ($row_searchMaxNo = sqlsrv_fetch_array( $stmt_searchMaxNo, SQLSRV_FETCH_ASSOC)){
			$transferNo = $row_searchMaxNo['No.'];
		}
	}
		
	//Incrementamos el numero de traspaso
	$nextNo = intval(substr($transferNo, 4))+1;
	$nextFormatedNo = str_pad($nextNo, 5, '0', STR_PAD_LEFT);
	$transferNo = date('y').'TR'.$nextFormatedNo;
	//$formattedDate = (new DateTime())->setTime(0, 0)->format('Y-m-d H:i:s.000');
	$formattedDate = date("Y-d-m");
	$sql='INSERT INTO ICF.dbo.[Ind. Campollano Ferretería S.L$Transfer Header](
			[No.], 
			[Transfer-from Code], 
			[Transfer-from Name], 
			[Transfer-from Name 2], 
			[Transfer-from Address], 
			[Transfer-from Address 2], 
			[Transfer-from Post Code], 
			[Transfer-from City], 
			[Transfer-from County], 
			[Trsf.-from Country_Region Code], 
			[Transfer-to Code], 
			[Transfer-to Name], 
			[Transfer-to Name 2], 
			[Transfer-to Address], 
			[Transfer-to Address 2], 
			[Transfer-to Post Code], 
			[Transfer-to City], 
			[Transfer-to County], 
			[Trsf.-to Country_Region Code],
			[Posting Date], 
			[Shipment Date], 
			[Receipt Date], 
			[Status], 
			[Shortcut Dimension 1 Code],
			[Shortcut Dimension 2 Code], 
			[In-Transit Code],
			[No. Series],
			[Last Shipment No.],
			[Last Receipt No.],
			[Transfer-from Contact],
			[Transfer-to Contact],
			[External Document No.],
			[Shipping Agent Code],			
			[Shipping Agent Service Code],
			[Shipping Time],
			[Shipment Method Code],
			[Transaction Type],
			[Transport Method],
			[Entry_Exit Point],
			[Area],
			[Transaction Specification],
			[Shipping Advice],
			[Posting from Whse. Ref.],
			[Outbound Whse. Handling Time],
			[Inbound Whse. Handling Time],
			[Assigned User ID],
			[Fecha emision documento],
			[Estado pedido], 
			[Fecha cierre],
			[Salesperson Code],
			[Source type])
		VALUES(
			\''.$transferNo.'\', 
			\''.$origin.'\', 
			\''.$stores[$origin]["name"].'\', 
			\''.$stores[$origin]["name2"].'\',  
			\''.$stores[$origin]["address"].'\',  
			\''.$stores[$origin]["address2"].'\',  
			\''.$stores[$origin]["postcode"].'\',  
			\''.$stores[$origin]["city"].'\',  
			\''.$stores[$origin]["county"].'\', 
			\'ES\', 
			\''.$destination.'\', 
			\''.$stores[$destination]["name"].'\', 
			\''.$stores[$destination]["name2"].'\',  
			\''.$stores[$destination]["address"].'\',  
			\''.$stores[$destination]["address2"].'\',  
			\''.$stores[$destination]["postcode"].'\',  
			\''.$stores[$destination]["city"].'\',  
			\''.$stores[$destination]["county"].'\',
			\'ES\',
			CAST(\''.$formattedDate.'\' AS DATETIME),
			CAST(\''.$formattedDate.'\' AS DATETIME),
			CAST(\''.$formattedDate.'\' AS DATETIME),
			0,
			\'\',
			\'\',
			\'TRANSITO\',
			\'T-PED\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			\'\',
			0,
			0,
			\'\',
			\'\',
			\'VAIO\',
			CAST(\''.$formattedDate.'\' AS DATETIME),
			0,
			CAST(\'1753-01-01\' AS DATETIME),
			\'\',
			0
		);';
		$stmt_createTransfer = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			var_dump($sql);
			var_dump(sqlsrv_errors(SQLSRV_ERR_ERRORS));
			echo json_encode(["result"=>-9, "text"=>"Ocurrio un error al crear el traspaso"]);
			die;
		}
		//Actualizamos el maximo numero de la serie
		$startingNo = date('y').'TR00001';
		$sql='UPDATE ICF.dbo.[Ind. Campollano Ferretería S.L$No. Series Line] SET 
				[Last No. Used]=\''.$transferNo.'\',
				[Last Date Used]=\''.$formattedDate.'\'		
			WHERE [Series Code]=\'T-PED\' AND [Starting No.]=\''.$startingNo.'\' AND [Open]=1';
		$stmt_updateSerie = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			echo json_encode(["result"=>-10, "text"=>"Ocurrio un error al actualizar la serie de traspasos."]);
			die;
		}
}

foreach($lines as $line){
	if(!isset($line["quantity"]) || !$line["quantity"]>0) continue;
	//Buscamos si la referencia y la variante esta ya en el traspaso
	$lineNo=null;
	$sql='SELECT TOP 1 [Line No.] FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Line] tl where 
			tl.[Item No.]=\''.$line["code"].'\' AND 
			tl.[Variant Code]=\''.$line["variant"].'\' AND [Status]=0 ORDER BY [Line No.] DESC';
			
	$stmt_searchLine = sqlsrv_query( $connNavision, $sql);	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		echo json_encode(["result"=>-11, "text"=>"Ocurrio un error en la consulta SQL de Navision: Buscando traspaso abierto."]);
		die;
	}
	if( $stmt_searchLine !== false) {
		if ($row_searchLine = sqlsrv_fetch_array( $stmt_searchLine, SQLSRV_FETCH_ASSOC)){
			$lineNo = $row_searchLine['Line No.'];
		}
	}
	if($lineNo){
		//El producto ya esta en el traspaso, incrementamos la cantidad
		$sql='UPDATE ICF.dbo.[Ind. Campollano Ferretería S.L$Transfer Line] SET 
			[Quantity]=[Quantity]+'.$line["quantity"].',
			[Qty. to Ship]=[Qty. to Ship]+'.$line["quantity"].',
			[Quantity (Base)]=[Quantity (Base)]+'.$line["quantity"].',
			[Outstanding Qty. (Base)]=[Outstanding Qty. (Base)]+'.$line["quantity"].',
			[Qty. to Ship (Base)]=[Qty. to Ship (Base)]+'.$line["quantity"].',
			[Outstanding Quantity]=[Outstanding Quantity]+'.$line["quantity"].',
			[Cantidad por recibir]=[Cantidad por recibir]+'.$line["quantity"].',
			[Cantidad por recibir (base)]=[Cantidad por recibir (base)]+'.$line["quantity"].'			
		WHERE [Document No.]=\''.$transferNo.'\' AND [Line No.]=\''.$lineNo.'\' AND [Item No.]=\''.$line["code"].'\' AND [Variant Code]=\''.$line["variant"].'\' AND [Status]=0';
		$stmt_updateLine = sqlsrv_query( $connNavision, $sql);	
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {

			echo json_encode(["result"=>-12, "text"=>"Ocurrio un error al actualizar la cantidad de la linea ".$lineNo." del traspaso."]);
			die;
		}
	}else{	
		//Si el producto no esta ya en el traspaso creamos una nueva linea 		
		//primero calculamos el numero de linea que corresponde
		$sql = 'SELECT MAX([Line No.]) as [Line No.] FROM dbo.[Ind. Campollano Ferretería S.L$Transfer Line] tl WHERE tl.[Document No.]=\''.$transferNo.'\'';
		$stmt_searchMaxLineNo = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			echo json_encode(["result"=>-13, "text"=>"Ocurrio un error en la consulta SQL de Navision: Buscando numero de ultima linea del traspaso."]);
			die;
		}
		if( $stmt_searchMaxLineNo !== false) {
			if ($row_searchMaxLineNo = sqlsrv_fetch_array( $stmt_searchMaxLineNo, SQLSRV_FETCH_ASSOC)){
				$lineNo = $row_searchMaxLineNo["Line No."];
			}
		}
		//Buscamos los datos del producto de Navision
		$sql = 'SELECT [Description],[Description 2],[Gen. Prod. Posting Group],[Inventory Posting Group],[Item Category Code],[Product Group Code],[Item Category Code],[Cod. Marca],[Gross Weight] FROM dbo.[Item] WHERE [No.]=\''.$line["code"].'\'';
		$stmt_searchItem = sqlsrv_query( $connNavision, $sql);
		if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
			echo json_encode(["result"=>-14, "text"=>"Ocurrio un error en la busqueda del codigo ".$line["code"]]);
			die;
		}
		if( $stmt_searchItem !== false) {
			if (!$row_searchItem = sqlsrv_fetch_array( $stmt_searchItem, SQLSRV_FETCH_ASSOC)){
				echo json_encode(["result"=>-15, "text"=>"Ocurrio un error en la busqueda del codigo ".$line["code"]]);
				die;
			}
		}		
		
		//Incrementamos la linea
		$lineNo+=10000;
		//Insertamos la línea
		$sql='INSERT INTO ICF.dbo.[Ind. Campollano Ferretería S.L$Transfer Line](
				[Document No.], 
				[Line No.], 
				[Item No.], 
				[Quantity], 
				[Unit of Measure], 
				[Qty. to Ship], 
				[Qty. to Receive], 
				[Quantity Shipped], 
				[Quantity Received], 
				[Status], 
				[Shortcut Dimension 1 Code], 
				[Shortcut Dimension 2 Code], 
				[Description], 
				[Gen. Prod. Posting Group], 
				[Inventory Posting Group], 
				[Quantity (Base)], 
				[Outstanding Qty. (Base)], 
				[Qty. to Ship (Base)], 
				[Qty. Shipped (Base)], 
				[Qty. to Receive (Base)], 
				[Qty. Received (Base)], 
				[Qty. per Unit of Measure], 
				[Unit of Measure Code], 
				[Outstanding Quantity], 
				[Gross Weight], 
				[Net Weight], 
				[Unit Volume], 
				[Variant Code], 
				[Units per Parcel], 
				[Description 2], 
				[In-Transit Code], 
				[Qty. in Transit], 
				[Qty. in Transit (Base)], 
				[Transfer-from Code], 
				[Transfer-to Code], 
				[Shipment Date], 
				[Receipt Date], 
				[Derived From Line No.], 
				[Shipping Agent Code], 
				[Shipping Agent Service Code], 
				[Shipping Time], 
				[Item Category Code], 
				[Product Group Code], 
				[Completely Shipped], 
				[Completely Received], 
				[Outbound Whse. Handling Time], 
				[Inbound Whse. Handling Time], 
				[Transfer-from Bin Code], 
				[Transfer-To Bin Code],
				[Cod. Marca], 
				[Fecha cierre línea],
				[Observaciones],
				[Marca], 
				[Estado línea pedido], 
				[Cantidad inicial], 
				[Cantidad inicial base], 
				[Cantidad por recibir], 
				[Cantidad por recibir (base)], 
				[Planning Flexibility])
			VALUES(
				\''.$transferNo.'\',
				'.$lineNo.', 
				\''.$line["code"].'\',
				'.$line["quantity"].',
				\'Unidades\',
				'.$line["quantity"].',
				0.00000000000000000000,
				0.00000000000000000000,
				0.00000000000000000000,
				0,
				\'\',
				\'\',
				\''.$row_searchItem["Description"].'\',
				\''.$row_searchItem["Gen. Prod. Posting Group"].'\',
				\''.$row_searchItem["Inventory Posting Group"].'\',
				'.$line["quantity"].',
				'.$line["quantity"].',
				'.$line["quantity"].',
				0.00000000000000000000, 
				0.00000000000000000000,
				0.00000000000000000000,
				1.00000000000000000000,
				\'UD\',
				'.$line["quantity"].',
				'.$row_searchItem["Gross Weight"].',
				0.00000000000000000000,
				0.00000000000000000000,
				\''.$line["variant"].'\',
				0.00000000000000000000,
				\''.$row_searchItem["Description 2"].'\',
				\'TRANSITO\',
				0.00000000000000000000,
				0.00000000000000000000,
				\''.$origin.'\', 
				\''.$destination.'\', 
				CAST(\''.date('Y-d-m').'\' AS DATETIME),
				CAST(\''.date('Y-d-m').'\' AS DATETIME),
				0,
				\'\',
				\'\',
				\'\',
				\''.$row_searchItem["Item Category Code"].'\',
				\''.$row_searchItem["Product Group Code"].'\',
				0,
				0, 
				\'\',
				\'\',
				\'\',
				\'\',
				\'\', 
				CAST(\'1753-01-01\' AS DATETIME),
				\'\',
				\''.$row_searchItem["Cod. Marca"].'\',
				0,
				0.00000000000000000000,
				0.00000000000000000000,
				'.$line["quantity"].',
				'.$line["quantity"].',
				0
			);';
			$stmt_insertLine = sqlsrv_query( $connNavision, $sql);	
			if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
							var_dump(utf8_encode($sql));
			var_dump(sqlsrv_errors(SQLSRV_ERR_ERRORS));
				echo json_encode(["result"=>-16, "text"=>"Ocurrio un error al insertar la linea ".$lineNo." del traspaso."]);
				die;
			}
	}
}
echo json_encode(["result"=>1, "data"=>["transfer"=>$transferNo], "text"=>"Traspaso creado correctamente"]);
?>
