<?php

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

/*
$bdd_usuario="icf";
$bdd_contrasena="6k4mkb9LWT3Zr5A";
$bdd_servidor="212.129.50.120";
$bdd_base_datos="pruebas";
*/

function Slug($string)
{
	return strtolower(trim(preg_replace('~[^0-9a-z]+~i', '-', html_entity_decode(preg_replace('~&([a-z]{1,2})(?:acute|cedil|circ|grave|lig|orn|ring|slash|th|tilde|uml);~i', '$1', htmlentities($string, ENT_QUOTES, 'UTF-8')), ENT_QUOTES, 'UTF-8')), '-'));
}



function obtenerIDProducto($sku, $conn){
	$stmt = $conn->prepare("SELECT id_product FROM fcproduct WHERE reference='$sku'");
	$stmt->execute();
	$result = $stmt->get_result();
	if($row_m = $result->fetch_assoc()) return $row_m['id_product'];
		else return -1;
}


function existeProducto($sku, $conn){
	$stmt = $conn->prepare("SELECT * FROM fccatalog_product_entity WHERE sku='$sku'");
	$stmt->execute();
	$result = $stmt->get_result();
	if($row_m = $result->fetch_assoc()) return true;
		else return false;
}


function round_up ( $value, $precision ) { 
    $pow = pow ( 10, $precision ); 
    return ( ceil ( $pow * $value ) + ceil ( $pow * $value - ceil ( $pow * $value ) ) ) / $pow; 
} 


while(true){
		$connPrestashop = new mysqli($bdd_servidor, $bdd_usuario, $bdd_contrasena, $bdd_base_datos);
		if( $connPrestashop ) {
			 echo "Conexion establecida con Prestashop.\r\n";
		}else{
			 echo "Conexion no se pudo establecer con Prestashop.\r\n";
			 die( print_r( sqlsrv_errors(), true));
		}

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

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

		echo "\r\n\r\nSincronizando productos:\r\n\r\n";


		$sql = ' 
					SELECT I.[No.], CONCAT(I.[Description], I.[Description 2]) Description, [DescripcionCortaWEB], CONCAT([DescripcionWEB],[Descripcion2WEB], [FamiliaWEB], [AgrupacionWEB]) desclarga, [MetadatosWEB], [MetadatosDescripcionWEB], [Precio Neto Compra UM Precio], [Last Direct Cost UM Precio], [EnvaseCompletoWEB] GastosVoluminoso, [ProductoNuevo2017] ProductoNuevo, [Unidad de embalaje de compra] unidad_embalaje, [Minimum Order Quantity] unidad_embalaje_secundaria, [CantidadEnDecimalesWEB] liquidacion,
						 [Item Disc. Group], [NovedadWEB], [OutlerWEB], [DestacadoWEB], [NoRotacion], [Cod. Marca], [Gross Weight], [CantidadPedidoWeb], [ArticuloVoluminosoWeb], [SoloTiendaFisica], [Unidad medida precio], TipoAgrupWEB, [Proveedor Tarifa-Dto Compra], [IncidenciaWEB] incidenciaweb, [ProductoWEB] productoweb,[EtiquetaAtribWEB] equivalenciaUM, [VAT Prod. Posting Group] iva,
						 I.[Precio venta UM precio] precio, I.[PrecioWEB], I.[precioDestacadoWEB], I.[discontinuado] as discontinuado, I.[Sales Unit of Measure] unidad_minima_venta, I.[Purch. Unit of Measure] unidad_minima_compra, I.[Blocked] bloqueado, I.[Item Category Code] familia_nav, I.[Product Group Code] subfamilia,
						(I.[Precio venta UM precio]-(I.[Precio venta UM precio]*(ISNULL(
								(SELECT TOP 1 [Line Discount %] descuento FROM
								[Ind. Campollano Ferretería S.L$Sales Line Discount]
								WHERE [Sales Code] = \'GDTO1\' AND Code LIKE I.[Item Disc. Group] AND [Starting Date]<=SYSDATETIME()
								AND ([Ending Date]>=SYSDATETIME() or [Ending Date] = \'1753-01-01 00:00:00.000\'))
						,0)/100))) GDTO1,
							(I.[Precio venta UM precio]-(I.[Precio venta UM precio]*(ISNULL(
								(SELECT TOP 1 [Line Discount %] descuento FROM
								[Ind. Campollano Ferretería S.L$Sales Line Discount]
								WHERE [Sales Code] = \'GDTO2\' AND Code LIKE I.[Item Disc. Group] AND [Starting Date]<=SYSDATETIME()
								AND ([Ending Date]>=SYSDATETIME() or [Ending Date] = \'1753-01-01 00:00:00.000\'))
						,0)/100))) GDTO2,
							(I.[Precio venta UM precio]-(I.[Precio venta UM precio]*(ISNULL(
								(SELECT TOP 1 [Line Discount %] descuento FROM
								[Ind. Campollano Ferretería S.L$Sales Line Discount]
								WHERE [Sales Code] = \'GDTO3\' AND Code LIKE I.[Item Disc. Group] AND [Starting Date]<=SYSDATETIME()
								AND ([Ending Date]>=SYSDATETIME() or [Ending Date] = \'1753-01-01 00:00:00.000\'))
						,0)/100))) GDTO3,					
						(SELECT ISNULL(MIN([Unit price UM precio]),0) From [Ind. Campollano Ferretería S.L$Sales Price] p WHERE [Item No.]=I.[No.]
						AND [Sales Type]=\'2\'
						AND [Minimum Quantity]<=1 
						AND [Starting Date]<SYSDATETIME()
						AND ([Ending Date]=\'1753-01-01 00:00:00.000\' OR [Ending Date]>SYSDATETIME())) precioTarifa,
						(SELECT SUM(a.[Outstanding Quantity]) FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Line] a, dbo.[Ind. Campollano Ferretería S.L$Purchase Header] b WHERE a.[No.]=I.[No.] AND a.[Document Type]=b.[Document Type] AND a.[Document Type]=1 AND a.[Document No.]=b.[No.] and b.[status]=1) pendiente_recibir,
						(SELECT [Lead Time Calculation] plazo_entrega_proveedor from dbo.[Ind. Campollano Ferretería S.L$Vendor] WHERE [No.]=I.[Proveedor Tarifa-Dto Compra]) plazo_entrega_proveedor,
						(SELECT ISNULL(Sum(Quantity),0) stock FROM dbo.[Item Ledger Entry] WHERE [Item No.]=I.[No.] AND ([Location Code]=\'ALM01\' OR [Location Code]=\'ALM02\' OR [Location Code]=\'TRANSITO\' OR [Location Code]=\'EXPEND ALM\')) stock,
						(SELECT ISNULL(Sum([Outstanding Quantity]),0) pendiente FROM dbo.[Ind. Campollano Ferretería S.L$Sales Line] sl WHERE [Document Type]=1 AND [Type]=2 AND [No.]=I.[No.] AND ([Location Code]=\'ALM01\' OR [Location Code]=\'ALM02\' OR [Location Code]=\'SERITEC\' OR [Location Code]=\'EXPEND ALM\')) pendiente,
						(SELECT [Qty. per Unit of Measure] cantidad_minima_venta FROM dbo.[Item Unit of Measure]  WHERE [Item No.]=I.[No.] AND [Code]=I.[Sales Unit of Measure] AND [Qty. per Unit of Measure]!=1 AND  [Qty. per Unit of Measure]!=100  AND [Qty. per Unit of Measure]!=1000) cantidad_minima_venta,
						(SELECT MAX([Posting Date]) fecha_ultimo_inventario FROM dbo.[Ind. Campollano Ferretería S.L$Phys. Inventory Ledger Entry] inv WHERE [Item No.]=I.[No.] AND ([Location Code]=\'ALM01\' OR [Location Code]=\'ALM02\') AND [Phys Invt Counting Period Type]=0) fecha_ultimo_inventario, 
						STUFF((SELECT \'|\' + Description FROM [Item Charge] WHERE Description <>\'\' AND Tipo=0 AND [No.]=I.[No.] FOR XML PATH(\'\'), TYPE).value(\'.\', \'nvarchar(max)\'), 1, 1, \'\') familiaWeb
					 FROM Item I Where ProductoWEB=1 AND ActualizarWEB=1
		';		




		$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt === false) {
			die( print_r( sqlsrv_errors(), true) );
		}
			 
		//Preparamos Array de productos en Navision
		$arrayProductosNavision=array();
		while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
			$arrayProductosNavision[$row['No.']]=$row;
		}

		//Preparamos Array de productos
		/*Añado la tabla fcproduct_shop porque es la que gestiona las cantidades mínimas de los pedidos*/
		/* Añado fcstock_available para gestionar que se denieguen los pedidos al llegar a cero cuando el producto esté marcado como outlet */
		
		$arrayProductosPrestashop=array();

		$sql = "SELECT p.id_product, l.name, l.description descripcion, l.description_short short_descripcion, l.meta_title meta_titulo, l.meta_description meta_descripcion, p.unidad_medida unidad_medida,
		l.available_later stock_label, p.price, p.wholesale_price, p.reference, p.ean13 ean, q.id_supplier proveedor, m.name marca, p.weight peso, p.voluminoso voluminoso, p.tienda_fisica tienda_fisica, p.outlet outlet, p.equivalencia, p.unidad_medida_equivalencia, 
		s.on_sale oferta, s.cantidad_pedido_minimo cantidad__minima_real_PS, p.id_tax_rules_group id_tax_rules_group,
		s.minimal_quantity cantida_minima, a.out_of_stock fuera_stock, p.active activo,
				(SELECT reduction FROM fcspecific_price WHERE id_group = 0 AND id_product=p.id_product AND reduction_type='percentage' AND from_quantity=1 LIMIT 1) reduction_general,
				(SELECT reduction FROM fcspecific_price WHERE id_group = 7 AND id_product=p.id_product AND reduction_type='percentage' AND from_quantity=1 LIMIT 1) reduction_gdto1,
				(SELECT reduction FROM fcspecific_price WHERE id_group = 8 AND id_product=p.id_product AND reduction_type='percentage' AND from_quantity=1 LIMIT 1) reduction_gdto2,
				(SELECT reduction FROM fcspecific_price WHERE id_group = 6 AND id_product=p.id_product AND reduction_type='percentage' AND from_quantity=1 LIMIT 1) reduction_gdto3,
				(SELECT price FROM fcspecific_price WHERE id_group = 10 AND id_product=p.id_product LIMIT 1) amazon,		
				(SELECT quantity FROM fcstock_available WHERE id_product=p.id_product LIMIT 1) stock,
				(SELECT GROUP_CONCAT(id_category SEPARATOR ',') FROM fccategory_product WHERE id_product=p.id_product) familias		
				FROM fcproduct p
				left join fcmanufacturer m on m.id_manufacturer=p.id_manufacturer
				left join fcproduct_lang l on l.id_product=p.id_product
				left join fcfeature_product f_color on f_color.id_product=p.id_product AND f_color.id_feature=11
				left join fcfeature_product f_long on f_long.id_product=p.id_product AND f_long.id_feature=12
				left join fcproduct_shop s on s.id_product=p.id_product
				left join fcstock_available a on a.id_product=p.id_product
				left join fcfeature_product x on x.id_product=p.id_product
				left join fcproduct_supplier q on q.id_product=p.id_product
				left join fcfeature_lang y on y.id_feature=x.id_feature
				left join fcfeature_value_lang z on z.id_feature_value=x.id_feature_value
				WHERE p.id_product=l.id_product ORDER BY p.reference";
	  
		$stmtPrestashop = $connPrestashop->prepare($sql);
		$stmtPrestashop->execute();
		$resultPrestashop = $stmtPrestashop->get_result();
		while($rowPrestashop = $resultPrestashop->fetch_assoc()){
			$arrayProductosPrestashop[$rowPrestashop["reference"]]=$rowPrestashop;
		}

	echo "yaq...\n";
//sqlsrv_close(connNavision);
		$connPrestashop->close();
		echo " \r\n \r\nDurmiendo \r\n"; 
		sleep(600); //10 minutos
		
}

