<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");
if(isset($_REQUEST["from"])) 
	$from=$_REQUEST["from"]; 
else $from=0;
if(isset($_REQUEST["to"])) 
	$to=$_REQUEST["to"]; 
else $to=0;
if(isset($_REQUEST["salesperson"])) 
	$salesperson=$_REQUEST["salesperson"]; 
else $salesperson=0;

$quantitys=[];

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 establecida con Navision.\r\n";
	}else{
		 //echo "Conexion no se pudo establecer con Navision.\r\n";
		 die( print_r( sqlsrv_errors(), true));
	}

	/*$sql='select l.[Document No.] as deliverynote, s.[Sell-to Customer No.] as customercode, s.[Document Date] as date, l.[Line No.] as line, l.[No.] as code, CONCAT(l.Description, l.[Description 2]) as description, l.[Variant Code] as variant, l.Quantity as qty, 
			l.[Item Charge Base Amount] as price, l.[Unit Cost (LCY)]*l.Quantity as cost, l.[Unit Cost (LCY)] as ponderatedCost, i.[Coste de Tarifa Compra] as feeCost, i.[Last Direct Cost] todayLastBuy, i.[Last Direct Cost] *l.Quantity as costlastbuy,
				(100-((100*l.[Unit Cost (LCY)]*l.Quantity/l.[Cdad por UM Precio])/l.[Item Charge Base Amount])) as margincost,
				(100-((100*i.[Last Direct Cost]*l.Quantity)/l.[Item Charge Base Amount])) as marginlastbuy,			
				IIF(c.[Abierto por el comercial]=1, \'Directa\', \'Indirecta\') as type

		from [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Line] l 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Header] s ON l.[Document No.] = s.[No.]
		LEFT JOIN [ICF].[dbo].[Item] i ON l.[No.] = i.[No.] 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Customer] c ON s.[Sell-to Customer No.] = c.[No.]
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Salesperson_Purchaser] p ON s.[Salesperson Code] = p.Code 
	   WHERE (s.[Document Date] BETWEEN \''.$from.'\' and \''.$to.'\') and p.[E-Mail] LIKE \''.$salesperson.'\' 
			and i.[Classification Excluded]=0
			and s.[Order No. Series] IN (\'V-PED\', \'V-DEV\', \'TICKET\');';*/
		
/*$sql='select l.[Document No.] as deliverynote, s.[Sell-to Customer No.] as customercode, s.[Document Date] as date, l.[Line No.] as line, l.[No.] as code, CONCAT(l.Description, l.[Description 2]) as description, l.[Variant Code] as variant, l.Quantity as qty, 
			l.[Item Charge Base Amount] as price, l.[Unit Cost (LCY)]*l.Quantity as cost, l.[Unit Cost (LCY)] as ponderatedCost, i.[Coste de Tarifa Compra] as feeCost, i.[Last Direct Cost] todayLastBuy, i.[Last Direct Cost] *l.Quantity as costlastbuy,

				IIF(c.[Abierto por el comercial]=1, \'Directa\', \'Indirecta\') as type

		from [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Line] l 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Header] s ON l.[Document No.] = s.[No.]
		LEFT JOIN [ICF].[dbo].[Item] i ON l.[No.] = i.[No.] 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Customer] c ON s.[Sell-to Customer No.] = c.[No.]
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Salesperson_Purchaser] p ON s.[Salesperson Code] = p.Code 
	   WHERE (s.[Document Date] BETWEEN \''.$from.'\' and \''.$to.'\') and p.[E-Mail] LIKE \''.$salesperson.'\' 
			and s.[Order No. Series] IN (\'V-PED\', \'V-DEV\', \'TICKET\');';*/
			
			
	$sql='select l.[Document No.] as deliverynote, s.[Sell-to Customer No.] as customercode, s.[Document Date] as date, l.[Line No.] as line, l.[No.] as code, CONCAT(l.Description, l.[Description 2]) as description, l.[Variant Code] as variant, l.Quantity as qty, 
			l.[Item Charge Base Amount] as price, l.[Unit Cost (LCY)]*l.Quantity as cost, l.[Unit Cost (LCY)] as ponderatedCost, i.[Coste de Tarifa Compra] as feeCost, i.[Last Direct Cost] todayLastBuy, i.[Last Direct Cost] *l.Quantity as costlastbuy,
			IIF(c.[Abierto por el comercial]=1, \'Directa\', \'Indirecta\') as type
		from [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Line] l 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Sales Shipment Header] s ON l.[Document No.] = s.[No.]
		LEFT JOIN [ICF].[dbo].[Item] i ON l.[No.] = i.[No.] 
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Customer] c ON s.[Sell-to Customer No.] = c.[No.]
		LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Salesperson_Purchaser] p ON s.[Salesperson Code] = p.Code 
	   WHERE (s.[Document Date] BETWEEN \''.$from.'\' and \''.$to.'\') and p.[E-Mail] LIKE \''.$salesperson.'\' 
			and s.[Order No. Series] IN (\'V-PED\', \'V-DEV\', \'TICKET\') ORDER BY s.[Document Date] ASC;';	
	
	$item=[];
	$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				$temp=[];
				foreach($row as $key=>$object){
					$temp_object[$key]=!is_object($object)?utf8_encode($object):$object;						
				}
				
				/*$sql='select TOP 1 pl.[Document No.] as purchaseorder, pl.[Order Date] as date, pl.Quantity as quantity, pl.[Coste unit. directo UM precio] as cost, pl.[Cod. unidad medida precio] as unit FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purchase Line] pl
  					LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purchase Header] ph on ph.[No.] = pl.[Document No.]
					WHERE ph.[Document Type] = 1 and pl.[No.]=\''.$temp_object['code'].'\' and pl.Quantity >0 and pl.[Coste unit. directo UM precio]>0 and pl.[Order Date] < \''.$temp_object['date']->format('d/m/Y').'\' 
					ORDER BY pl.[Order Date] DESC';*/
					
					$sql='select TOP 1 pl.[Document No.] as purchaseorder, pl.[Order Date] as date, pl.Quantity as quantity, pl.[Unit Cost UM Precio] as cost, pl.[Cod. unidad medida precio] as unit FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Rcpt. Line] pl
  					LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Rcpt. Header] ph on ph.[No.] = pl.[Document No.]
					WHERE pl.[No.]=\''.$temp_object['code'].'\' and pl.Quantity >='.$temp_object['qty'].' and pl.[Coste unit. directo UM precio]>0 and pl.[Order Date] < \''.$temp_object['date']->format('d/m/Y').'\'  
					ORDER BY pl.[Order Date] DESC';

				$stmtPurchase = sqlsrv_query( $connNavision, $sql );

				if( $stmtPurchase !== false) {
					if($rowPurchase = sqlsrv_fetch_array( $stmtPurchase, SQLSRV_FETCH_ASSOC)){
						$temp_object['purchase_order']=$rowPurchase['purchaseorder'];
						$temp_object['purchase_date']=$rowPurchase['date'];
						if($rowPurchase['unit'] == 'C')	$temp_object['purchase_cost']=$rowPurchase['cost']/100;
						else if($rowPurchase['unit'] == 'M') $temp_object['purchase_cost']=$rowPurchase['cost']/1000;
							else $temp_object['purchase_cost']=$rowPurchase['cost'];
						$temp_object['purchase_unit']=$rowPurchase['unit'];
						
					}else{
						$temp_object['purchase_order']='';
						$temp_object['purchase_date']='';
						
						$cost=$temp_object['ponderatedCost'];
						if($cost==0) $cost=$temp_object['todayLastBuy'];
						if($cost==0) $cost=$temp_object['feeCost'];
						
						if($rowPurchase['unit'] == 'C')	$temp_object['purchase_cost']=$cost/100;
						else if($rowPurchase['unit'] == 'M') $temp_object['purchase_cost']=$cost/1000;
							else $temp_object['purchase_cost']=$cost;
						$temp_object['purchase_unit']='';
					}
				}else  die( print_r( sqlsrv_errors(), true));
					
					
				$item[]=$temp_object;
			}
		}

	$prueba["class"]=$item;
	
	
	$sql='select TOP 10 s.[Sell-to Customer No.] as customercode, CONCAT(s.[Sell-to Customer Name], s.[Sell-to Customer Name 2]) as customername, sum(l.[Item Charge Base Amount]) as price, sum(l.[Unit Cost (LCY)]*l.Quantity) as Cost, sum(i.[Last Direct Cost] *l.Quantity) as CostItem,
	  avg((100-((100*l.[Unit Cost (LCY)]*l.Quantity/l.[Cdad por UM Precio])/l.[Item Charge Base Amount]))) as margincost,
	  avg((100-((100*i.[Last Direct Cost]*l.Quantity)/l.[Item Charge Base Amount]))) as marginlastbuy

		from [Ind. Campollano Ferreterķa S.L$Sales Shipment Line] l 
		LEFT JOIN [Ind. Campollano Ferreterķa S.L$Sales Shipment Header] s ON l.[Document No.] = s.[No.]
		LEFT JOIN [Item] i ON l.[No.] = i.[No.]
		LEFT JOIN [Ind. Campollano Ferreterķa S.L$Salesperson_Purchaser] p ON s.[Salesperson Code] = p.Code 
		WHERE (s.[Document Date] BETWEEN \''.$from.'\' and \''.$to.'\') and p.[E-Mail] LIKE \''.$salesperson.'\' 
			and i.[Classification excluded] = 0
			and s.[Order No. Series] IN (\'V-PED\', \'V-DEV\')
			and l.[Item Charge Base Amount]>0
		
		GROUP BY s.[Sell-to Customer No.], CONCAT(s.[Sell-to Customer Name], s.[Sell-to Customer Name 2])
		ORDER BY price DESC	
			
			;';
	
	$temp_object=null;
	$item=[];
	$stmt = sqlsrv_query( $connNavision, $sql );
		if( $stmt !== false) {
			while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
				$temp=[];
				foreach($row as $key=>$object){
					$temp_object[$key]=!is_object($object)?utf8_encode($object):$object;						
				}
				$item[]=$temp_object;
			}
		}

	$prueba["topcustomers"]=$item;
	
	
	$result[]=$prueba;
echo json_encode($result, JSON_PRETTY_PRINT);
?>

