<?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++;
}

$product_code 	= '';
$variant_code 	= '';
$customer_code	= '';
$quantity		= 1;

if (isset($_REQUEST["product_code"]))
	$product_code = $_REQUEST["product_code"];
if (isset($_REQUEST["variant_code"]))
	$variant_code = $_REQUEST["variant_code"];
if (isset($_REQUEST["customer_code"]))
	$customer_code = $_REQUEST["customer_code"];
if (isset($_REQUEST["quantity"]) && $_REQUEST["quantity"]!='' && is_numeric($_REQUEST["quantity"]))
	$quantity = floatval($_REQUEST["quantity"]);

if ($product_code==''){
	file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode("No se ha indicado codigo de producto para obtener su precio."));
	echo json_encode(["result"=>-2 , "text"=>"No se ha indicado codigo de producto para obtener su precio."]);
	die;
}

if( $connNavision ) {
	 //echo "Conexion establecida con Navision.\r\n";
}else{
	  file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode("No se pudo establecer la conexion con Navision"));
	  echo json_encode(["result"=>-3 , "text"=>"No se pudo conectar con Navision"]);
	  die;
}

$price 		= null; // Precio final
$price_base = null; // Precio tarifa
$discount	= null; // Descuentos sobre precio tarifa
$discount_code = null; // Código del descuento
$discount_group = null; // Grupo del descuento

// Precio especifico de un producto/variante para un cliente 
// Unit Price ya esta dividido en caso de ser cientos (C) o miles (M) la unidad de medida de precio
$sql='
SELECT TOP 1 
	s.[Unit Price] price
FROM [Ind. Campollano Ferretería S.L$Sales Price] s
WHERE
	s.[Item No.]=\''.$product_code.'\'
	AND (s.[Variant Code]=\''.$variant_code.'\' OR s.[Variant Code]=\'\')
	AND (s.[Sales Type]=0 OR s.[Sales Type]=2) 
	AND (s.[Sales Code]=\''.$customer_code.'\' OR s.[Sales Code]=\'\')
	AND s.[Minimum Quantity] <= '.$quantity.' 
	AND s.[Starting Date] <= GETDATE()
	AND (s.[Ending Date]=\'1753-01-01 00:00:00.000\' OR s.[Ending Date]>=GETDATE())
ORDER BY s.[Variant Code] DESC, s.[Sales Code] DESC';

$stmt = sqlsrv_query( $connNavision, $sql );
if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
	file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode("No se pudo obtener el precio especifico de un producto"));
	echo json_encode(["result"=>-4, "text"=>"No se pudo obtener el precio especifico de un producto"]);
	die;
}
if( $stmt !== false) {
	if ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		$price = floatval($row['price']);
	}
}

// Precio tarifa menos los descuentos por producto o grupo de descuento
if ($price==null){
	$sql='
	SELECT 
		i.[Unit Price] as price
	FROM [Item] i
	WHERE
		i.[No.]=\''.$product_code.'\' AND i.[Blocked]=0 AND i.[Discontinuado]=0';
		
	$stmt = sqlsrv_query( $connNavision, $sql );
	if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
        file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode("No se pudo obtener el precio de tarifa de un producto"));
        echo json_encode(["result"=>-5, "text"=>"No se pudo obtener el precio de tarifa de un producto"]);
		die;
	}
	if( $stmt !== false) {
		if ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
			$price_base	= floatval($row['price']);
			if ($price_base > 0){
				// Una vez que ya se tiene un precio de tarifa se aplican los descuentos
				$sql='
				SELECT TOP 1 
					d.[Line Discount %] as discount, d.[Code] as discount_code, d.[Sales Code] as discount_group
				FROM [Ind. Campollano Ferretería S.L$Sales Line Discount] d
				WHERE
					(d.[Code]=\''.$product_code.'\' OR d.[Code]=(SELECT i.[Item Disc. Group] FROM [Item] i WHERE i.[No.]=\''.$product_code.'\'))
					AND (d.[Variant Code]=\''.$variant_code.'\' OR d.[Variant Code]=\'\')
					AND (d.[Sales Type]=0 OR d.[Sales Type]=1) 
					AND (d.[Sales Code]=\''.$customer_code.'\' OR d.[Sales Code]=(SELECT c.[Customer Disc. Group] FROM [Ind. Campollano Ferretería S.L$Customer] c WHERE c.[No.]=\''.$customer_code.'\'))
					AND d.[Minimum Quantity] <= '.$quantity.' 
					AND d.[Starting Date] <= GETDATE()
					AND (d.[Ending Date]=\'1753-01-01 00:00:00.000\' OR d.[Ending Date]>=GETDATE())
				ORDER BY d.[Variant Code] DESC, d.[Sales Code] ASC';	
			
				$stmt = sqlsrv_query( $connNavision, $sql );
				if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
					file_get_contents("https://icfbot.ferreteriacampollano.com/message.php?channel=".$discordchannel."&msg=".urlencode("No se pudo obtener los descuentos de un producto"));
					echo json_encode(["result"=>-6, "text"=>"No se pudo obtener los descuentos de un producto"]);
					die;
				}
				if( $stmt !== false) {
					if ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
						$discount = floatval($row['discount']);
						$discount_code = $row['discount_code'];
						$discount_group = $row['discount_group'];
						if ($discount>0)
							$price = $price_base - ($price_base * $discount /100);						
					}
				}				
			}
			if ($price==null)
				$price = $price_base;
		}
	}	
}	


$result			= [];
$result['data'] = [];
$result['data']['product_code'] = $product_code;
$result['data']['variant_code'] = $variant_code;
$result['data']['customer_code'] = $customer_code;
$result['data']['quantity'] = $quantity;
$result['data']['price_base'] = $price_base;
$result['data']['discount_code'] = $discount_code;
$result['data']['discount_group'] = $discount_group;
$result['data']['discount'] = $discount;
$result['data']['price'] = $price;
if ($price==null){
	$result['result']=-1;
	$result['text']='No se ha encontrado precio para el producto';
}else
	$result['result']=1;

header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);
?>
