<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '-1');
set_time_limit(0);
date_default_timezone_set('Europe/Madrid');
//include("conexion.php");
if(isset($_REQUEST["from"])) //Formato linux timestamp
	$from=$_REQUEST["from"]; else $from=0;
if(isset($_REQUEST["type"])) //Formato linux timestamp
	$type=$_REQUEST["type"]; else $type='PC';
//$from=date('d/m/Y', $from);

$date_start='2021-01-01';
$date_end='2021-12-31';

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

function searchCSV($handle, $col, $value){
    rewind($handle);
    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        if($data[$col]==$value){
            return $data;
        }
    } 
    return false;
}

$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));
	}

	
	
	/*
	if (($handle_new = fopen("stock_rectificado.csv", "w")) !== FALSE) {      

        fclose($handle_new);
    }*/
	
	if (($handle = fopen("stock.csv", "r")) !== FALSE) {
        if (($handle_new = fopen("stock_rectificado.csv", "w")) !== FALSE) {
            $sql = 'SELECT l.[No.] as code, CONCAT(l.[Description],\' \',l.[Description 2]) as descripcion, SUM(l.[Quantity]) as quantity, l.[Unit Cost (LCY)] as cost, l.[Quantity]*l.[Unit Cost (LCY)] as total From [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Inv. Line] l
                    LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purch. Inv. Header] h ON h.[No.] = l.[Document No.] 
                    WHERE CAST(h.[Document Date] AS Date) >= \''.$date_start.'\' AND CAST(h.[Document Date] AS Date) <= \''.$date_end.'\'
                    AND l.[Type] = 2
                    GROUP BY l.[No.], l.[Description], l.[Description 2],l.[Unit Cost (LCY)],l.[Quantity]';
            $stmt = sqlsrv_query( $connNavision, $sql);
            if( $stmt !== false) {
                while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
                    if(searchCSV($handle,1,$row['code'])===false){
                        $item[]=$row['code'];
                        $item[]=$row['descripcion'];
                        $item[]=$row['quantity'];
                        $item[]=$row['cost'];
                        $item[]=$row['total'];
                        fputcsv($handle_new, $item);
                    }else{
                        
                    }
                }
            }      
            fclose($handle_new);
        }
    fclose($handle);
    }
	
	
	
		/*$objects=[];
		$collection=[];
		$maxtimestamp=0;

		$sql = 'SELECT TOP 5000 CAST(h.TIMESTAMP AS BIGINT) timestamp, h.[No.] code, h.[Buy-from Vendor No.] supplier,
		 userauthor.[E-Mail] author,
		 usersaleperson.[E-Mail] agent,
		 CONCAT(h.[Buy-from Vendor Name],h.[Buy-from Vendor Name 2]) suppliername,
		 CONCAT(h.[Buy-from Address], h.[Buy-from Address 2]) supplieraddress,
		 h.[Buy-from Post Code] supplierpostcode,
		 h.[Buy-from City] suppliercity,
		 h.[Buy-from County] supplierstate,
		 h.[No oferta relacionada] purchasesbudget,
		 CONCAT(h.[Ship-to Name],h.[Ship-to Name 2]) shiptoname,
		 CONCAT(h.[Ship-to Address], h.[Ship-to Address 2]) shiptoaddress,
		 h.[Ship-to Post Code] shiptopostcode,
		 h.[Ship-to City] shiptocity,
		 h.[Ship-to County] shiptostate,
		 h.[Status] status,
		 paymethod.[Description] paycode,
		 h.[VAT Registration No.] vat,
		 payment.[Due Date Calculation] paydate, 
		 payment.[Description] paydesc,
		 bankacc.[IBAN] IBAN, 
		 h.[Document Date] date,
		 h.[Fecha 1.lanzamiento] launchdate,
		 SUM(l.[Line Amount]) AS linestotal,
		 SUM(l.[Amount Including VAT]-l.[Amount]) AS vattotal,
		 SUM(l.[Line Discount Amount]) AS dto,
		 SUM(l.[Line Amount])-SUM(l.[Inv. Discount Amount]) AS base,
		 SUM(l.[Amount Including VAT]) AS total,
		 SUM(l.[Unit Cost]*l.[Quantity]) AS cost
		 FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purchase Header] h
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$User Setup] userauthor ON userauthor.[User ID]=h.[Assigned User ID]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$User Setup] usersaleperson ON usersaleperson.[Salespers._Purch. Code]=h.[Purchaser Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Payment Method] paymethod ON paymethod.[Code]=h.[Payment Method Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Payment Terms] payment ON payment.[Code]=h.[Payment Terms Code]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purchase Line] l ON l.[Document No.]=h.[No.]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Vendor] c ON c.[No.]=h.[Buy-from Vendor No.]
		 LEFT JOIN [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Vendor Bank Account] bankacc ON bankacc.[Vendor No.]=h.[Buy-from Vendor No.] AND bankacc.[Code]=\'BC0001\'
		 WHERE (h.[No.] LIKE \'__PC%\' OR h.[No.] LIKE \'__DEVC%\') AND
		 CAST(h.TIMESTAMP AS BIGINT)>'.$from.' 
		 GROUP BY h.TIMESTAMP, h.[No.], h.[Buy-from Vendor No.], h.[Buy-from Vendor Name], h.[Buy-from Vendor Name 2], h.[Buy-from Address], h.[Buy-from Address 2], h.[Buy-from Post Code], h.[Buy-from City], h.[Buy-from County],
					 h.[Ship-to Name], h.[Ship-to Address], h.[Ship-to Name 2], h.[Ship-to Address 2], h.[Ship-to Post Code], h.[Ship-to City], h.[Ship-to County], paymethod.[Description], h.[Status],
		 			 h.[VAT Registration No.], payment.[Due Date Calculation], payment.[Description], h.[Document Date], h.[Fecha 1.lanzamiento], bankacc.[IBAN], h.[No oferta relacionada], userauthor.[E-Mail], 
					 usersaleperson.[E-Mail];';
		
	
		
		$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;
				}
				
		if($row['timestamp']>$maxtimestamp) $maxtimestamp=$row['timestamp'];
				//Invoice lines
		$lines=[];
		$sql = 'SELECT l.[No.] AS reference, l.[Cross-Reference No.] AS referencecross, CONCAT(l.[Description], l.[Description 2]) AS description, l.[Quantity] AS quantity,
					l.[Unit price (LCY)] AS price, l.[Line Discount %] AS discountperc, l.[Line Discount Amount] AS discounttotal, l.[Unit Cost]*l.[Quantity] AS cost, l.[Amount] AS linetotal, l.[Amount including VAT]-l.[Amount] AS taxtotal, l.[Amount including VAT] as total, l.type, 
					l.[Line No.]/10000 as linenum, vat.[EC %] surchargeperc, vat.[VAT %] taxperc
				FROM [ICF].[dbo].[Ind. Campollano Ferreterķa S.L$Purchase Line] l
				LEFT JOIN [Ind. Campollano Ferreterķa S.L$VAT Posting Setup] vat ON vat.[VAT Prod. Posting Group]=l.[VAT Prod. Posting Group] AND vat.[VAT Bus. Posting Group]=l.[VAT Bus. Posting Group]
				WHERE l.[Document No.]=\''.$row['code'].'\' AND l.type<>0';

		

		$stmt_lines = sqlsrv_query( $connNavision, $sql );
		if( $stmt_lines !== false) {
			while($row_line = sqlsrv_fetch_array( $stmt_lines, SQLSRV_FETCH_ASSOC)){
				$temp_line=[];
				foreach($row_line as $key=>$item){
					$temp_line[$key]=!is_object($item)?utf8_encode($item):$item;
				}
								
				$temp_line["quantity"]=floatval($temp_line["quantity"]);
				$temp_line["price"]=floatval($temp_line["price"]);
				$temp_line["discountperc"]=floatval($temp_line["discountperc"]);
				$temp_line["discounttotal"]=floatval($temp_line["discounttotal"]);
				$temp_line["surchargeperc"]=floatval($temp_line["surchargeperc"]);
				$temp_line["taxperc"]=floatval($temp_line["taxperc"]);
				$temp_line["taxtotal"]=floatval($temp_line["taxtotal"]);
				$temp_line["linetotal"]=floatval($temp_line["linetotal"]);
				$temp_line["cost"]=floatval($temp_line["cost"]);
				$temp_line["total"]=floatval($temp_line["total"]);
				
					
				$lines[]=$temp_line;
				
				
			}
		}
				$temp_object["lines"]=$lines;	
				$temp_object["linestotal"]=floatval($temp_object["linestotal"]);
				$temp_object["vattotal"]=floatval($temp_object["vattotal"]);
				$temp_object["dto"]=floatval($temp_object["dto"]);
				$temp_object["base"]=floatval($temp_object["base"]);
				$temp_object["cost"]=floatval($temp_object["cost"]);
				$temp_object["total"]=floatval($temp_object["total"]);
				$objects[]=$temp_object;
			}
		}
		$collection["maxtimestamp"]=$maxtimestamp;
		$collection["class"]=$objects;
		$result[]=$collection;
		
header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);*/

?>
