<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Europe/Madrid');
// Parámetros
  $sServerName 	= "localhost";
  $aConnectionInfo = array( "Database"=>"ICF", "UID"=>"VAIO", "PWD"=>"VAIO");
  $aResultData = [];
// Conexión con Navision
  $bConnNavision = false;
  $iRetries = 0;
  while(!$bConnNavision && $iRetries<3){
    $bConnNavision = sqlsrv_connect($sServerName, $aConnectionInfo);
    $iRetries++;
  }
  if(!$bConnNavision){
    echo json_encode(["result"=>-1 , "text"=>"No se pudo conectar con Navision."]);
    die;
  }
// Obtención de los datos
  // Código del Pedido a obtener
    $sBuyOrder_code = null;
    if(isset($_REQUEST["code"])) 
      $sBuyOrder_code = $_REQUEST["code"]; 
  //
  if($sBuyOrder_code!=null || $sBuyOrder_code!=''){
    $sBuyOrdersQuery = 'SELECT
			CAST(ph.TIMESTAMP AS BIGINT) as ts,
			FORMAT(ph.[Order Date], \'yyyy-MM-dd HH:mm:ss\') as odate,
			ph.[No.] as code,
			ph.[Historic] as ended,
			ph.[Buy-from Vendor No.] as supplier_code,
			ph.[Buy-from Contact No.] as supplier_contact_code,
			ph.[Buy-from Contact] as supplier_contact_name,			
			ph.[Pay-to Vendor No.] as supplier2_code,
			ph.[Pay-to Contact No.] as supplier2_contact_code,
			ph.[Pay-to Contact] as supplier2_contact_name,		
			ph.[Vendor Shipment No.] as their_delivery,
			ph.[Es Devolucion] as refund,
			us.[E-mail] as agent_email,
			ph.[Sell-to Customer No.] as customer_code,
			CONCAT(ph.[Ship-to Name], ph.[Ship-to Name 2]) as customer_name,
			ph.[VAT Registration No.] as customer_vat,
			ph.[Payment Method Code] as payment_method_code,
			pt.[Code] as payment_term_code,
			pt.[Description] as payment_term_name,
			pt.[Due Date Calculation] as payment_term_days,
			ph.[Location Code] as store_code,
			ph.[Shipment Method Code] as ship_condition_code,
			ph.[Email Documento] as email,
			ph.[Ship-to Country_Region Code] as country_code,
			ph.[Ship-to City] as town,
			CONCAT(ph.[Ship-to address], ph.[Ship-to Address 2]) as address,
			ph.[Ship-to Post Code] as cp,
			ph.[Telefono] as phone,
			FORMAT(ph.[Payment Discount %], \'0.0000\', \'en-EN\') as discount
			FROM dbo.[Ind. Campollano Ferretería S.L$Purchase Header] ph
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$User Setup] us ON ph.[Assigned User Id] = us.[User ID]
			LEFT JOIN dbo.[Ind. Campollano Ferretería S.L$Payment Terms] pt ON ph.[Payment Terms Code] = pt.[Code]
			WHERE ph.[No.] = \''.$sBuyOrder_code.'\'
			GROUP BY 
			ph.[No.],
			ph.[Historic],
			ph.TIMESTAMP,
			ph.[Order Date],    
			ph.[Buy-from Vendor No.],
			ph.[Buy-from Contact No.],
			ph.[Buy-from Contact],
			ph.[Pay-to Vendor No.],
			ph.[Pay-to Contact No.],
			ph.[Pay-to Contact],
			ph.[Vendor Shipment No.],
			ph.[Es Devolucion],
			us.[E-mail],
			ph.[Sell-to Customer No.],
			ph.[VAT Registration No.],
			ph.[Payment Method Code],
			pt.[Code],
			pt.[Description],
			pt.[Due Date Calculation],
			ph.[Location Code],
			ph.[Shipment Method Code],
			ph.[Email Documento],
			ph.[Ship-to Country_Region Code],
			ph.[Ship-to City],
			ph.[Ship-to Address],
			ph.[Ship-to Address 2],
			ph.[Ship-to Post Code],
			ph.[Telefono],
			ph.[Payment Discount %],
			ph.[Ship-to Name],
			ph.[Ship-to Name 2]';
    $stmtBuyOrders = sqlsrv_query($bConnNavision, $sBuyOrdersQuery);
    if(sqlsrv_errors(SQLSRV_ERR_ERRORS) !== NULL) {
      print_r(sqlsrv_errors(SQLSRV_ERR_ERRORS));
      echo json_encode(["result"=>-2, "text"=>"Ocurrio un error al obtener los datos del Pedido de Compra cuyo código es: {$sBuyOrder_code}"]);
      die;
    }
    if($stmtBuyOrders !== false) {
      while($row = sqlsrv_fetch_array($stmtBuyOrders, SQLSRV_FETCH_ASSOC)){			
        $aBuyOrders = [];
        foreach($row as $key=>$object){
          $aBuyOrders[$key] = !is_object($object)?utf8_encode($object):$object;
        }
        $aResultData['header'] = $aBuyOrders;
        $aResultData['lines']  = [];
        // Obtención de las Líneas del Pedido
          $sBuyOrdersLinesQuery = 'SELECT
							CAST(pl.TIMESTAMP AS BIGINT) as ts,
							pl.[Line No.] as linenum_navision,
							pl.[Type] as type,
							pl.[Location Code] as store_code,
							pl.[No.] as product_code,
							pl.[Variant Code] as variant_code,						
							FORMAT(pl.[Quantity], \'0.0000\', \'en-EN\') as quantity,
							FORMAT(pl.[Gross Weight], \'0.0000\', \'en-EN\') as weight,
							FORMAT(pl.[Qty. per Unit of Measure], \'0.0000\', \'en-EN\') as buyunit,
							pl.[Unit of Measure Code] as measure_code,
							FORMAT(pl.[Line Discount %], \'0.0000\', \'en-EN\') as discountperc,
							FORMAT(pl.[% Descuento 1], \'0.0000\', \'en-EN\') as disc1,
							FORMAT(pl.[% Descuento 2], \'0.0000\', \'en-EN\') as disc2,
							FORMAT(pl.[% Descuento 3], \'0.0000\', \'en-EN\') as disc3,
							FORMAT(pl.[% Descuento 4], \'0.0000\', \'en-EN\') as disc4,
							FORMAT((pl.[Line Discount Amount]/NULLIF(pl.[Quantity], 0)), \'0.0000\', \'en-EN\') as discountunit,
							FORMAT(pl.[Line Discount Amount], \'0.0000\', \'en-EN\') as discounttotal,
							FORMAT((pl.[Direct Unit Cost]), \'0.0000\', \'en-EN\') as pricebase,
							FORMAT(((pl.[Amount]/NULLIF(pl.[Quantity], 0))), \'0.0000\', \'en-EN\') as priceunit,
							FORMAT(pl.[Amount], \'0.0000\', \'en-EN\') as pricetotal,
							FORMAT(pl.[VAT %], \'0.0000\', \'en-EN\') as taxperc,
							FORMAT(((pl.[Amount]/NULLIF(pl.[Quantity], 0))*pl.[VAT %])/100, \'0.0000\', \'en-EN\') as taxunit,
							FORMAT((pl.[Amount]*pl.[VAT %])/100, \'0.0000\', \'en-EN\') as taxtotal,
							FORMAT(pl.[Amount]+((pl.[Amount]*pl.[VAT %])/100), \'0.0000\', \'en-EN\') as total,
							FORMAT(um.[Qty. per Unit of Measure], \'0.0000\', \'en-EN\') as qtymeasure
						FROM  dbo.[Ind. Campollano Ferretería S.L$Purchase Line] pl
						LEFT JOIN dbo.[Item Unit of Measure] um ON pl.[No.] = um.[Item No.] AND pl.[Unit of Measure Code] = um.[Code]
						WHERE pl.[Document No.] = \''.$sBuyOrder_code.'\' AND (pl.[Type]=0 or pl.[Type]=2) 
						ORDER BY pl.[Line No.]';
					$stmtBuyOrderLines = sqlsrv_query($bConnNavision, $sBuyOrdersLinesQuery);
					if(sqlsrv_errors(SQLSRV_ERR_ERRORS)!==NULL) {
						echo json_encode(["result"=>-3, "text"=>"Ocurrio un error al obtener los datos de las Líneas del Pedido de Compra cuyo código es: {$sBuyOrder_code}"]);
						die;
					}
					if($stmtBuyOrderLines !== false) {
						while($rowLine = sqlsrv_fetch_array($stmtBuyOrderLines, SQLSRV_FETCH_ASSOC)){
							$aBuyOrderLines = [];
							foreach($rowLine as $key=>$object){
								$aBuyOrderLines[$key] = !is_object($object)?utf8_encode($object):$object;
							}
							if((isset($aBuyOrderLines['product_code']) && $aBuyOrderLines['product_code']!='' && $aBuyOrderLines['type']=='2') || (isset($aBuyOrderLines['type']) && $aBuyOrderLines['type']=='0'))
								$aResultData['lines'][] = $aBuyOrderLines;
						}
					}
        //
        
      }
    }

  }
// Resultado
  header('Content-Type: application/json');
  echo json_encode($aResultData, JSON_UNESCAPED_UNICODE);
//
?>