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

// Código de pedido de Navision a introducir en ICF_FrasCab pero ya procesado (para poder ser albaranado)
$code = null;
if(isset($_REQUEST["code"]) && $_REQUEST["code"]!=''){ 
	$code=$_REQUEST["code"]; 
	// Verifica si la primera letra es 'N' y si es así la elimina
	if (strtoupper($code[0]) === 'N') {
		// Elimina la primera letra
		$code = substr($code, 1);
	}else{
		echo json_encode(["result"=>-1, "text"=>"El codigo de pedido es de AXIOM"]);
		die;
	}	
}else{
	echo json_encode(["result"=>-1, "text"=>"No se ha indicado codigo de pedido"]);
	die;
}


function acentos($str) {
  $a = array('À', 'Á', 'Â', 'Ã', 'Ä', 'Å', 'Æ', 'Ç', 'È', 'É', 'Ê', 'Ë', 'Ì', 'Í', 'Î', 'Ï', 'Ð', 'Ñ', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö', 'Ø', 'Ù', 'Ú', 'Û', 'Ü', 'Ý', 'ß', 'à', 'á', 'â', 'ã', 'ä', 'å', 'æ', 'ç', 'è', 'é', 'ê', 'ë', 'ì', 'í', 'î', 'ï', 'ñ', 'ò', 'ó', 'ô', 'õ', 'ö', 'ø', 'ù', 'ú', 'û', 'ü', 'ý', 'ÿ', 'A', 'a', 'A', 'a', 'A', 'a', 'C', 'c', 'C', 'c', 'C', 'c', 'C', 'c', 'D', 'd', 'Ð', 'd', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'G', 'g', 'G', 'g', 'G', 'g', 'G', 'g', 'H', 'h', 'H', 'h', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', '?', '?', 'J', 'j', 'K', 'k', 'L', 'l', 'L', 'l', 'L', 'l', '?', '?', 'L', 'l', 'N', 'n', 'N', 'n', 'N', 'n', '?', 'O', 'o', 'O', 'o', 'O', 'o', 'Œ', 'œ', 'R', 'r', 'R', 'r', 'R', 'r', 'S', 's', 'S', 's', 'S', 's', 'Š', 'š', 'T', 't', 'T', 't', 'T', 't', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'W', 'w', 'Y', 'y', 'Ÿ', 'Z', 'z', 'Z', 'z', 'Ž', 'ž', '?', 'ƒ', 'O', 'o', 'U', 'u', 'A', 'a', 'I', 'i', 'O', 'o', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?');
  $b = array('A', 'A', 'A', 'A', 'A', 'A', 'AE', 'C', 'E', 'E', 'E', 'E', 'I', 'I', 'I', 'I', 'D', 'N', 'O', 'O', 'O', 'O', 'O', 'O', 'U', 'U', 'U', 'U', 'Y', 's', 'a', 'a', 'a', 'a', 'a', 'a', 'ae', 'c', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'n', 'o', 'o', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'y', 'y', 'A', 'a', 'A', 'a', 'A', 'a', 'C', 'c', 'C', 'c', 'C', 'c', 'C', 'c', 'D', 'd', 'D', 'd', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'G', 'g', 'G', 'g', 'G', 'g', 'G', 'g', 'H', 'h', 'H', 'h', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'IJ', 'ij', 'J', 'j', 'K', 'k', 'L', 'l', 'L', 'l', 'L', 'l', 'L', 'l', 'l', 'l', 'N', 'n', 'N', 'n', 'N', 'n', 'n', 'O', 'o', 'O', 'o', 'O', 'o', 'OE', 'oe', 'R', 'r', 'R', 'r', 'R', 'r', 'S', 's', 'S', 's', 'S', 's', 'S', 's', 'T', 't', 'T', 't', 'T', 't', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'W', 'w', 'Y', 'y', 'Y', 'Z', 'z', 'Z', 'z', 'Z', 'z', 's', 'f', 'O', 'o', 'U', 'u', 'A', 'a', 'I', 'i', 'O', 'o', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'A', 'a', 'AE', 'ae', 'O', 'o', '?', 'a', '?', 'e', '?', '?', 'O', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?');
  return str_replace($a, $b, $str);
}


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

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

$connNavision=false;
$retries=0;
while(!$connNavision && $retries<3){
	$connNavision = sqlsrv_connect( $serverName, $connectionInfo);
	$retries++;
}


if(!$connNavision ) {
	 echo "Conexion no se pudo establecer con Navision.\r\n";
	 die( print_r( sqlsrv_errors(), true));
}

// Comprobar que no existe en ICF_FrasCab
$sql='SELECT TOP 1 Cod_FrasCab FROM dbo.[Ind. Campollano Ferretería S.L$ICF_FrasCab] fc where fc.[CodFacturaTemp]=\''.$code.'\'';

$stmt_sale = 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( $stmt_sale !== false) {
	$salefrascab=[];
	while($row2 = sqlsrv_fetch_array( $stmt_sale, SQLSRV_FETCH_ASSOC)){			
		foreach($row2 as $key=>$object){
			$salefrascab[$key]=!is_object($object)?utf8_encode($object):$object;
		}			
	}
	
	// Si no existe se busca en Sales Header para ver si existe 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.'\'';
	$stmt_sale_header = sqlsrv_query( $connNavision, $sql);	
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
		echo json_encode(["result"=>-3, "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){
			//echo print_r($saleheader,true);
			//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='';
			if (!isset($salefrascab['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']))."',
					'0000',
					'".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']))."',
					'',
					'',
					'".strtoupper(mssql_escape($saleheader['code']))."',
					1,
					'',
					'".mssql_escape($saleheader['address1'])."',
					'".mssql_escape($saleheader['address2'])."',
					'".mssql_escape($saleheader['cp'])."',
					'".mssql_escape($saleheader['town'])."',
					'".mssql_escape($saleheader['province'])."',
					'".strtoupper(mssql_escape($saleheader['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) {
					die( print_r( sqlsrv_errors(), true) );
				}
				//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"];
			}else{
				// Borrar lineas para volver a ser importadas
				$Cod_FrasCab=$salefrascab['Cod_FrasCab'];
				$sql = 'DELETE FROM dbo.[Ind. Campollano Ferretería S.L$ICF_FrasLinea] WHERE Cod_FrasCab=\''.$Cod_FrasCab.'\'';		
				$stmt_delete = 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: ICF_FrasLinea delete"]);
					die;
				}
			}
					
			//Creamos las líneas del pedido
			$sql = 'SELECT
				    sl.[Line No.] as linenum_navision,
					sl.[Location Code] as store_code,
					sl.[No.] as product_code,
					sl.[Description] as product_name,
					sl.[Variant Code] as variant_code,
					FORMAT(sl.[Quantity], \'0.0000\', \'en-EN\') as quantity,
					FORMAT(sl.[Unit Price], \'0.0000\', \'en-EN\') as pricebase,
					FORMAT(sl.[Line Discount %], \'0.0000\', \'en-EN\') as discountperc,
					FORMAT(
						CASE 
							WHEN sl.[Quantity] IS NOT NULL AND FLOOR(sl.[Quantity]) <> 0 
							THEN (sl.[Line Discount Amount]/sl.[Quantity]) 
							ELSE 0 
						END, \'0.0000\', \'en-EN\'
					) as discountunit,
					FORMAT(sl.[Line Discount Amount], \'0.0000\', \'en-EN\') as discounttotal,
					FORMAT(
						CASE 
							WHEN sl.[Quantity] IS NOT NULL AND FLOOR(sl.[Quantity]) <> 0 
							THEN (sl.[Amount]/sl.[Quantity]) 
							ELSE 0 
						END, \'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(
						CASE 
							WHEN sl.[Quantity] IS NOT NULL AND FLOOR(sl.[Quantity]) <> 0 
							THEN ((sl.[Amount]/sl.[Quantity])*sl.[VAT %])/100 
							ELSE 0 
						END, \'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
				FROM  dbo.[Ind. Campollano Ferretería S.L$Sales Line] sl
				WHERE [Document No.]=\''.$code.'\' AND [Type]=2 ORDER BY [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"]);
				print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
				die;
			}
			$salelines=[];
			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;
						$saleline[$key]=!is_object($object)?$object:$object;
					}
					if (isset($saleline['product_code']) && $saleline['product_code']!='')
						$salelines[] = $saleline;
				}
			}				
			
			//print_r($salelines);
			$line = 0;
			foreach($salelines 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']."',
					1,
					'".mssql_escape($saleline['variant_code'])."',
					0,
					0,
					0,
					'".mssql_escape($saleline['store_code'])."',
					0,
					0,
					0,
					'".$saleline['linenum_navision']."'
				);";
				
				$stmt = sqlsrv_query( $connNavision, $sql );
				//echo $sql."<br>";
				if( $stmt === false) {
					die( print_r( sqlsrv_errors(), true) );
				}
			}				
		}else{
			echo json_encode(["result"=>-2, "text"=>"Codigo de pedido no existe"]);
			die;
		}
	}else{
		echo json_encode(["result"=>-2, "text"=>"Codigo de pedido no existe"]);
		die;
	}
	echo json_encode(["result"=>1, "text"=>"Pedido insertado correctamente"]);
	
}
?>
