<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$discordchannel="883046233017552956";

$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 establecida con Navision.\r\n";
}else{
	  file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode(":sos:"."SCRIPT ".basename(__FILE__, '.php').": No se pudo establecer la conexion con Navision"));
	  echo json_encode(["result"=>-11 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

$result 					= [];
$result['products_prices'] 	= [];
$result['increments'] 		= [];
$calcule 					= [];
// Productos de la categorķa
$products = [];
if (isset($_REQUEST["products"])) 
	$products=json_decode($_REQUEST["products"],true); 
if (is_array($products) && count($products)){
	for($i=0; $i<count($products); $i++){
		$product = $products[$i]['code'];
		$result['products_prices'][$product] = [];
		// Por cada producto se saca el incremento por grupo de clientes
		for($j=1; $j<4; $j++){
			$increment 	= null;
			$prices		= [];
			$sql='SELECT i.[Precio Neto Compra UM Precio] AS price, d.[Line Discount %] AS discount, (case when i.[Unidad medida precio]=\'C\' then i.[Unit Price]*100  when i.[Unidad medida precio]=\'M\' then i.[Unit Price]*1000 else i.[Unit Price] end) as pvpr, i.[Precio venta UM Precio] as pvp, i.[Vendor No.] as vendor FROM dbo.[Item] i
					LEFT JOIN [Ind. Campollano Ferreterķa S.L$Sales Line Discount] d ON d.[Code]=i.[Item Disc. Group]
						WHERE i.[No.]=\''.$product.'\' and d.[Sales Code]=\'GDTO'.$j.'\' and i.[Blocked]=0 and i.[Discontinuado]=0';
				  
			$stmt = sqlsrv_query( $connNavision, $sql );
			if( $stmt !== false) {				
				while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
					foreach($row as $key=>$object)
						$prices[$key]=!is_object($object)?utf8_encode($object):$object;													
				}				
			}	  
			if (count($prices)==5){			
				$price 		= $prices['price'];
				$discount 	= $prices['discount'];
				$pvpr 		= $prices['pvpr'];
				$pvp 		= $prices['pvp'];
				$vendor 	= $prices['vendor'];
				
				if ($price!=0){
					if ($pvp!=$pvpr)
						$increment = ((($pvpr-(($discount*$pvpr)/100))/$price)-1)*100;					
					else	
						$increment = ((($pvp-(($discount*$pvp)/100))/$price)-1)*100;										
				}else{
					$increment = 0;
				}
				if (!isset($result['products_prices'][$product][$j]))
					$result['products_prices'][$product][$j] = [];
				if (!isset($result['products_prices'][$product][$j][$vendor]))
					$result['products_prices'][$product][$j][$vendor] = [];
				$result['products_prices'][$product][$j][$vendor]=$increment;				
				if (!isset($calcule[$j][$vendor]))
					$calcule[$j][$vendor] = [];
				$calcule[$j][$vendor][] = $increment;	
			}else{
				// No tiene grupos de descuento
				$prices		= [];
				$sql='SELECT i.[Precio Neto Compra UM Precio] AS price, (case when i.[Unidad medida precio]=\'C\' then i.[Unit Price]*100  when i.[Unidad medida precio]=\'M\' then i.[Unit Price]*1000 else i.[Unit Price] end) as pvpr, i.[Precio venta UM Precio] as pvp, i.[Vendor No.] as vendor FROM dbo.[Item] i					
						WHERE i.[No.]=\''.$product.'\' and i.[Blocked]=0 and i.[Discontinuado]=0';
				$stmt = sqlsrv_query( $connNavision, $sql );
				if( $stmt !== false) {				
					while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
						foreach($row as $key=>$object)
							$prices[$key]=!is_object($object)?utf8_encode($object):$object;													
					}				
				}	  		
				if (count($prices)==4){
					$price 		= $prices['price'];
					$pvpr 		= $prices['pvpr'];
					$pvp 		= $prices['pvp'];
					$vendor 	= $prices['vendor'];
					if ($price!=0){
						if ($pvp!=$pvpr)
							$increment = (($pvpr/$price)-1)*100;					
						else	
							$increment = (($pvp/$price)-1)*100;										
					}else{
						$increment = 0;
					}
				
					if (!isset($result['products_prices'][$product][$j]))
						$result['products_prices'][$product][$j] = [];
					if (!isset($result['products_prices'][$product][$j][$vendor]))
						$result['products_prices'][$product][$j][$vendor] = [];
					$result['products_prices'][$product][$j][$vendor]=$increment;
					if (!isset($calcule[$j][$vendor]))
						$calcule[$j][$vendor] = [];
					$calcule[$j][$vendor][] = $increment;					
				}else{
					if (!isset($result['products_prices'][$product][$j]))
						$result['products_prices'][$product][$j] = [];
					if (!isset($result['products_prices'][$product][$j][$vendor]))
						$result['products_prices'][$product][$j][$vendor] = [];
					$result['products_prices'][$product][$j][$vendor]=0;
					if (!isset($calcule[$j][$vendor]))
						$calcule[$j][$vendor] = [];
					$calcule[$j][$vendor][] = 0;	
				}
			}
		}
	}
	// Media acotada de la categorķa
	$percentage = '0.1'; // Entre 0 y 1
	for($j=1; $j<4; $j++){
		foreach ($calcule[$j] as $vendor=>$value){
			$count 		= count($value);
			$increment	= 0;
			// Elementos a eliminar
			$remove		= round($count*$percentage);
			if (($count-($remove*2))>=3){
				// Ordenar grupo
				sort($value, SORT_NUMERIC);
				// Eliminar elementos finales 
				for($i=0; $i<$remove; $i++)
					array_pop($value);
				// Eliminar elementos al principio
				for($i=0; $i<$remove; $i++)
					array_shift($value);
				$increment = round(array_sum($value)/($count-($remove*2)));
			}else
			if ($count>0)
				$increment = round(array_sum($value)/$count);
			if (!isset($result['increments'][$vendor]))
				$result['increments'][$vendor] = [];
			$result['increments'][$vendor][$j] = $increment;			
		}
	}
}
unset($result['products_prices']);
header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);
?>
