<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Reporte Contratos de Veh&iacute;culos</title>
<link href="../../maincss.css" rel="stylesheet" type="text/css" />
<link href="../../printcss.css" rel="stylesheet" type="text/css" media="print" />

</head>
<?php
include "../../conn.php";
include "../../general.php";
include "../../token.php";

$rep=$_GET["rep"];
$tipo=$_GET["tipo"];
$tipoc=$_POST["tipoc"];
$por_facturar=$_POST["por_facturar"];
$por_fecha=$_POST["por_fecha"];
$estado=$_POST["estado"];
$almacen=$_POST["almacen"];
$cliente=$_POST["cliente"];
$vehiculo=$_POST["vehiculo"];
$orden=$_POST["orden"];
$categoria=$_POST["categoria"];
$marca=$_POST["marca"];
$modelo=$_POST["modelo"];
$familia=$_POST["familia"];
$subfamilia=$_POST["subfamilia"];
$grupo=$_POST["grupo"];
$subgrupo=$_POST["subgrupo"];
$fechaini=$_POST["fechaini"];
$fechafin=$_POST["fechafin"];
//$usuario=$_POST["usuario"];

$km_ver=$_POST["km_ver"];
$gas_ver=$_POST["gas_ver"];

//$usuario_det=$_GET["usuario_det"];
$cod_contrato_det=$_GET["cod_contrato_det"];

if($familia == '') { $familia = 0;}
if($subfamilia == '') { $subfamilia = 0;}
if($grupo == '') { $grupo = 0;}
if($subgrupo == '') { $subgrupo = 0;}

if($marca == '') { $marca = 0;}
if($modelo == '') { $modelo = 0;}

$n_decimales = 2;

?>
<body>
<?php


//Inicio reporte Contratos de vehiculos

if($rep == 9 && $tipo == 1) { // if 2

 if ($por_fecha == 0) { // if 2.1.0
	$nom_por_fecha = 'Cuya fecha de registro esté en el periodo';
	$valor_por_fecha = 'a.fldDate >= "'.$fechaini.'" and a.fldDate <= "'.$fechafin.'" and ';//para 2 y 3
	$valor_por_fecha2 = 'a.fldDate >= "'.$fechaini.'" and a.fldDate <= "'.$fechafin.'" and ';//para 4
 } //fin if 2.1.0

 if ($por_fecha == 1) { // if 2.1.1
	$nom_por_fecha = 'Cuya fecha de entrega esté en el periodo';
	$valor_por_fecha = 'a.fldDeliveryDate >= "'.$fechaini.'" and a.fldDeliveryDate <= "'.$fechafin.'" and ';//para 2 y 3
	$valor_por_fecha2 = 'a.fldDeliveryDate >= "'.$fechaini.'" and a.fldDeliveryDate <= "'.$fechafin.'" and ';//para 4
 } //fin if 2.1.1

 if ($por_fecha == 2) { // if 2.1.2
	$nom_por_fecha = 'Cuyo rango de fechas de alquiler esté en el periodo';
	$valor_por_fecha = '((a.fldDeliveryDate <= "'.$fechafin.'" and "'.$fechafin.'" <= a.fldReceptionDate) or 
(a.fldDeliveryDate <= "'.$fechaini.'" and "'.$fechaini.'" <= a.fldReceptionDate) or 
(a.fldDeliveryDate <= "'.$fechaini.'" and "'.$fechafin.'" <= a.fldReceptionDate) or 
(a.fldDeliveryDate >= "'.$fechaini.'" and "'.$fechafin.'" >= a.fldReceptionDate)) and ';//para 2 y 3
	$valor_por_fecha2 = '((a.fldDeliveryDate <= "'.$fechafin.'" and "'.$fechafin.'" <= a.fldRealReceptionDate) or 
(a.fldDeliveryDate <= "'.$fechaini.'" and "'.$fechaini.'" <= a.fldRealReceptionDate) or 
(a.fldDeliveryDate <= "'.$fechaini.'" and "'.$fechafin.'" <= a.fldRealReceptionDate) or 
(a.fldDeliveryDate >= "'.$fechaini.'" and "'.$fechafin.'" >= a.fldRealReceptionDate)) and ';//para 4
 } //fin if 2.1.2
	
	if ($estado == 0) { // if 2.2
		$nom_estado = 'Todos';
		$valor_estado = ' ';
	} else {
		//nombre estado
		$result0 = mysqli_query($link, "select pkyVehicleContractStatus as cod_estado, fldName as estado
								from
								vhcVehicleContractStatus
								where
								pkyVehicleContractStatus = '$estado' and
								sysEnabled = 1 AND sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result0)) {
			$nom_estado = $row['estado'];
		}
		mysqli_free_result($result0);
		$valor_estado = ' a.codStatus = ' .$estado. ' and ';
	} //fin if 2.2
	
	if ($tipoc == 0) { // if 2.3
		$nom_tipo = 'Todos';
		$valor_tipo = ' ';
	} else {
		//nombre tipo
		$result1 = mysqli_query($link, "select pkyVehicleContractType as cod_tipo, fldName as tipo
								from
								vhcVehicleContractType
								where
								pkyVehicleContractType = '$tipoc' and
								sysEnabled = 1 AND sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result1)) {
			$nom_tipo = $row['tipo'];
		}
		mysqli_free_result($result1);
		$valor_tipo = ' a.codType = ' .$tipoc. ' and ';
	} //fin if 2.3
	
	if ($almacen == 0) { // if 2.4
		$nom_almacen = 'Todos';
		$valor_almacen = ' ';
	} else {
		//nombre almacen
		$result2 = mysqli_query($link, "select pkyWarehouse as cod_alm, fldName as almacen
								from
								invWarehouse
								where
								pkyWarehouse = '$almacen' and
								sysEnabled = 1 AND sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_almacen = $row['almacen'];
		}
		mysqli_free_result($result2);
		$valor_almacen = ' a.codSourceWarehouse = ' .$almacen. ' and ';
	} //fin if 2.4
	
	if ($cliente == 0) { // if 2.5
		$nom_cliente = 'Todos';
		$valor_cliente = ' ';
	} else {
		//nombre cliente
		$result3 = mysqli_query($link, "select pkyCompany as cod_cli, fldName as cliente
								from
								agnCompany
								where
								pkyCompany = '$cliente' and
								sysEnabled = 1 AND sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result3)) {
			$nom_cliente = $row['cliente'];
		}
		mysqli_free_result($result3);
		$valor_cliente = ' a.codClient = ' .$cliente. ' and ';
	} //fin if 2.5
	
	if ($vehiculo == 0) { // if 2.6
		$nom_vehiculo = 'Todos';
		$valor_vehiculo = ' ';
	} else {
		//nombre vehiculo
		$result4 = mysqli_query($link, "select a.pkyItem as cod_vehi, a.fldName as vehiculo, 
								b.fldCarPlate as placa, b.fldCategory as cat
								from
								invItem a, vhcVehicleDetail b
								where 
								a.pkyItem = '$vehiculo' and
								a.codType = 4 and
								a.pkyItem = b.codItem and
								a.sysEnabled = 1 AND a.sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result4)) {
			$nom_vehiculo = $row['cod_vehi'].' - '.$row['vehiculo'].' ('.$row['placa'].')';
		}
		mysqli_free_result($result4);
		$valor_vehiculo = ' a.codVehicle = ' .$vehiculo. ' and ';
	} //fin if 2.6
	
	if ($orden == 0) { // if 2.7
		$nom_orden = 'Fecha';
		$valor_orden = ' order by fecha ';
	} else {
		$nom_orden = 'Contrato';
		$valor_orden = ' order by cod_contrato ';
	} //fin if 2.7
	
	if ($por_facturar == 0) { // if 2.8
		$nom_por_facturar = 'No';
	} else {
		$nom_por_facturar = 'Si';
	} //fin if 2.8
	
	if (strcmp($categoria,0) == 0) { // if 2.9
		$nom_categoria = 'Todos';
		$valor_categoria = ' ';
	} else {
		$nom_categoria = $categoria;
		$valor_categoria = ' f.fldCategory = "' .$categoria. '" and ';
	} //fin if 2.9
	
	if ($marca == 0) { // if 2.10
		$nom_marca = 'Todos';
		$valor_marca = ' ';
	} else {
		//nombre marca
		$result5 = mysqli_query($link, "select pkyBrand as cod, fldName as marca 
								from invBrand 
								where 
								pkyBrand = '$marca' and 
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result5)) {
			$nom_marca = $row['marca'];
		}
		mysqli_free_result($result5);
		$valor_marca = ' g.codBrand = ' .$marca. ' and ';
	} //fin if 2.10	
	
	if ($modelo == 0) { // if 2.11
		$nom_modelo = 'Todos';
		$valor_modelo = ' ';
	} else {
		//nombre modelo
		$result6 = mysqli_query($link, "select pkyBrandModel as cod, fldName as modelo 
								from invBrandModel 
								where 
								pkyBrandModel = '$modelo' and 
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result6)) {
			$nom_modelo = $row['modelo'];
		}
		mysqli_free_result($result6);
		$valor_modelo = ' g.codBrandModel = ' .$modelo. ' and ';
	} //fin if 2.11	
	
	if ($familia == 0) { // if familia
		$nom_familia = 'Todos';
		$valor_familia = ' ';
	} else {
		//nombre familia
		$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$familia' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultfam)) {
			$nom_familia = $row['nombre'];
		}
		mysqli_free_result($resultfam);
		$valor_familia = ' g.codFamily = ' .$familia. ' and ';
	} //fin if familia

if ($subfamilia == 0) { // if subfamilia
		$nom_subfamilia = 'Todos';
		$valor_subfamilia = ' ';
	} else {
		//nombre sub familia
		$resultsubf = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subfamilia' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubf)) {
			$nom_subfamilia = $row['nombre'];
		}
		mysqli_free_result($resultsubf);
		$valor_subfamilia = ' g.codSubFamily = ' .$subfamilia. ' and ';
	} //fin if subfamilia
	
if ($grupo == 0) { // if grupo
		$nom_grupo = 'Todos';
		$valor_grupo = ' ';
	} else {
		//nombre grupo
		$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$grupo' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgru)) {
			$nom_grupo = $row['nombre'];
		}
		mysqli_free_result($resultgru);
		$valor_grupo = ' g.codGroup = ' .$grupo. ' and ';
	} //fin if grupo
	
if ($subgrupo == 0) { // if subgrupo
		$nom_subgrupo = 'Todos';
		$valor_subgrupo = ' ';
	} else {
		//nombre sub grupo
		$resultsubg = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subgrupo' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubg)) {
			$nom_subgrupo = $row['nombre'];
		}
		mysqli_free_result($resultsubg);
		$valor_subgrupo = ' g.codSubGroup = ' .$subgrupo. ' and ';
	} //fin if subgrupo

	//listado de contratos
	$result = mysqli_query($link, "select t.cod_contrato as cod_contrato, t.nro_doc as nro_doc, t.fecha as fecha, t.estado as estado, t.tipo as tipo, t.almacen as almacen, t.cliente as cliente, t.cat as cat, t.placa as placa, t.vehiculo as vehiculo, t.fec_entrega as fec_entrega, t.fec_recepcion as fec_recepcion, t.fec_real_rec as fec_real_rec, t.total_parcial as total_parcial, t.lts_gas_falta as lts_gas_falta, t.costo_gas as costo_gas, t.total as total, t.km_recorridos as km_recorridos, t.km_extra as km_extra, t.tiempo_extra as tiempo_extra, t.alquiler as alquiler, t.cod_estado as cod_estado, t.facturado as facturado, t.desc_extra as desc_extra, 
t.km_entrega_cli as km_entrega_cli, t.gas_entrega_cli as gas_entrega_cli, t.km_recepcion_cli as km_recepcion_cli, t.gas_recepcion_cli as gas_recepcion_cli, 
t.km_entrega_par as km_entrega_par, t.km_recepcion_par as km_recepcion_par, t.gas_entrega_par as gas_entrega_par, t.gas_recepcion_par as gas_recepcion_par, t.usuario_reg as usuario_reg
from
(select a.pkyVehicleContract as cod_contrato, a.fldNumber as nro_doc, a.fldDate as fecha, b.fldName as estado, c.fldName as tipo, d.fldName as almacen, e.fldName as cliente, f.fldCategory as cat, f.fldCarPlate as placa, g.fldName as vehiculo, a.fldDeliveryDate as fec_entrega, a.fldReceptionDate as fec_recepcion, a.fldRealReceptionDate as fec_real_rec, a.fldTotal as total_parcial, 0 as lts_gas_falta, a.fldExtraFuelTotal as costo_gas, a.fldRealTotal as total, fldMileageRented as km_recorridos, a.fldExtraMileageTotal as km_extra, a.fldExtraTimeTotal as tiempo_extra, a.fldSemiTotal as alquiler, a.codStatus as cod_estado, a.fldTotalBilled as facturado, 
a.fldMileage as km_entrega_cli, a.fldFuel as gas_entrega_cli, a.fldArrivalMileage as km_recepcion_cli, a.fldArrivalFuel as gas_recepcion_cli, a.fldDiscount as desc_extra, 
a.fldDeliveredMileage as km_entrega_par, a.fldReceivedMileage as km_recepcion_par, a.fldDeliveredFuel as gas_entrega_par, a.fldReceivedFuel as gas_recepcion_par, h.fldName as usuario_reg
from
vhcVehicleContract a, vhcVehicleContractStatus b, vhcVehicleContractType c, invWarehouse d, agnCompany e, vhcVehicleDetail f, invItem g, accGroup h
where 
$valor_estado 
$valor_tipo 
$valor_almacen 
$valor_cliente 
$valor_vehiculo 
$valor_por_fecha 
$valor_categoria 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_marca 
$valor_modelo 
(a.codStatus = 2 or a.codStatus = 3) and
a.codStatus = b.pkyVehicleContractStatus and
a.codType = c.pkyVehicleContractType and
a.codSourceWarehouse = d.pkyWarehouse and
a.codClient = e.pkyCompany and
a.codVehicle = f.codItem and 
a.codVehicle = g.pkyItem and 
a.sysAuthorizationGroup = h.pkyGroup and 
a.sysEnabled = 1 AND a.sysAuthorized = 1

union all

select a.pkyVehicleContract as cod_contrato, a.fldNumber as nro_doc, a.fldDate as fecha, b.fldName as estado, c.fldName as tipo, d.fldName as almacen, e.fldName as cliente, f.fldCategory as cat, f.fldCarPlate as placa, g.fldName as vehiculo, a.fldDeliveryDate as fec_entrega, a.fldReceptionDate as fec_recepcion, a.fldRealReceptionDate as fec_real_rec, a.fldTotal as total_parcial, convert(a.fldFuel, signed integer) - convert(a.fldArrivalFuel, signed integer) as lts_gas_falta, a.fldExtraFuelTotal as costo_gas, a.fldRealTotal as total, convert(a.fldArrivalMileage, signed integer) - convert(a.fldMileage, signed integer) as km_recorridos, a.fldExtraMileageTotal as km_extra, a.fldExtraTimeTotal as tiempo_extra, a.fldSemiTotal as alquiler, a.codStatus as cod_estado, a.fldTotalBilled as facturado, a.fldMileage as km_entrega_cli, a.fldFuel as gas_entrega_cli, a.fldArrivalMileage as km_recepcion_cli, a.fldArrivalFuel as gas_recepcion_cli, a.fldDiscount as desc_extra, 
a.fldDeliveredMileage as km_entrega_par, a.fldReceivedMileage as km_recepcion_par, a.fldDeliveredFuel as gas_entrega_par, a.fldReceivedFuel as gas_recepcion_par, h.fldName as usuario_reg
from
vhcVehicleContract a, vhcVehicleContractStatus b, vhcVehicleContractType c, invWarehouse d, agnCompany e, vhcVehicleDetail f, invItem g, accGroup h
where 
$valor_estado 
$valor_tipo 
$valor_almacen 
$valor_cliente 
$valor_vehiculo 
$valor_por_fecha2 
$valor_categoria 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_marca 
$valor_modelo 
a.codStatus = 4 and
a.codStatus = b.pkyVehicleContractStatus and
a.codType = c.pkyVehicleContractType and
a.codSourceWarehouse = d.pkyWarehouse and
a.codClient = e.pkyCompany and
a.codVehicle = f.codItem and 
a.codVehicle = g.pkyItem and 
a.sysAuthorizationGroup = h.pkyGroup and 
a.sysEnabled = 1 AND a.sysAuthorized = 1) t 
$valor_orden;");
		

$matriz_contratos = array();
$i = 1;
while ($row = mysqli_fetch_array($result)) {
	$matriz_contratos [$i][1] = $row['cod_contrato'];
	$matriz_contratos [$i][2] = $row['nro_doc'];
	$matriz_contratos [$i][3] = $row['fecha'];
	$matriz_contratos [$i][4] = $row['estado'];
	$matriz_contratos [$i][5] = $row['tipo'];
	$matriz_contratos [$i][6] = $row['almacen'];
	$matriz_contratos [$i][7] = $row['cliente'];
	$matriz_contratos [$i][8] = $row['cat'];
	$matriz_contratos [$i][9] = $row['placa'];
	$matriz_contratos [$i][10] = $row['vehiculo'];
	$matriz_contratos [$i][11] = $row['fec_entrega'];
	$matriz_contratos [$i][12] = $row['fec_recepcion'];
	$matriz_contratos [$i][13] = $row['fec_real_rec'];
	$matriz_contratos [$i][14] = $row['alquiler'];
	$matriz_contratos [$i][15] = $row['lts_gas_falta'];
	$matriz_contratos [$i][16] = $row['costo_gas'];
	$matriz_contratos [$i][17] = $row['total'];
	$matriz_contratos [$i][22] = $row['km_extra'];
	$matriz_contratos [$i][23] = $row['tiempo_extra'];
	$matriz_contratos [$i][24] = $row['total_parcial'];
	$matriz_contratos [$i][25] = $row['cod_estado'];
	$matriz_contratos [$i][26] = $row['facturado'];
	$matriz_contratos [$i][29] = $row['km_recorridos'];
	$matriz_contratos [$i][30] = $row['usuario_reg'];
	$matriz_contratos [$i][31] = $row['km_entrega_cli'];
	$matriz_contratos [$i][32] = $row['gas_entrega_cli'];
	$matriz_contratos [$i][33] = $row['km_recepcion_cli'];
	$matriz_contratos [$i][34] = $row['gas_recepcion_cli'];
	$matriz_contratos [$i][35] = $row['km_entrega_par'];
	$matriz_contratos [$i][36] = $row['gas_entrega_par'];
	$matriz_contratos [$i][37] = $row['km_recepcion_par'];
	$matriz_contratos [$i][38] = $row['gas_recepcion_par'];
	$matriz_contratos [$i][39] = $row['desc_extra'];
	 
	$i++;
}
$fin = $i - 1;
mysqli_free_result($result);


//calculo de extras, seguros y tc a la fecha de cierre
$i = 1;
while ($i <= $fin) {//while calculo 1
	$cod_contrato = $matriz_contratos [$i][1];
	$fecha_entrega = $matriz_contratos [$i][12];
	$fecha_real_entrega = $matriz_contratos [$i][13];
	$cod_estado = $matriz_contratos [$i][25];
	//suma de equipamiento extra
	$result1 = mysqli_query($link, "select sum(fldTotal) as precio_extras
	from vhcVehicleContractExtra 
	where
	codContract = '$cod_contrato' and
	sysEnabled = 1 and sysAuthorized = 1 ;");
	while ($row = mysqli_fetch_array($result1)) {
		$matriz_contratos [$i][18] = $row['precio_extras'];
	}
	mysqli_free_result($result1);
	
	//suma de seguros tomados
	$result2 = mysqli_query($link, "select sum(fldTotal) as precio_seguros
	from vhcContractVehicleInsurance 
	where 
	codContract = '$cod_contrato' and
	sysEnabled = 1 and sysAuthorized = 1 ;");
	while ($row = mysqli_fetch_array($result2)) {
		$matriz_contratos [$i][19] = $row['precio_seguros'];
	}
	mysqli_free_result($result2);
	
	//verifica que fecha tomar si el contrato es abierto o cerrado
	if($cod_estado == 4) {//if cod_estado
		$fecha_a_tomar = $fecha_real_entrega;
	} else {
		$fecha_a_tomar = $fecha_entrega;
	}//fin if cod_estado
	
	//valor del codigo del ultimo tipo de cambio
	$resultom = mysqli_query($link, "SELECT T2.pkyExchangeRate as tc FROM 
	(SELECT fldDate, MAX(fldTime) AS fldTime FROM actExchangeRate WHERE fldExclusive = '0' GROUP BY fldDate) as T1,
	actExchangeRate T2 
	WHERE 
	T1.fldDate = (SELECT MAX(fldDate) FROM actExchangeRate WHERE fldDate <= '$fecha_a_tomar' AND fldExclusive = '0') AND 
	T2.fldDate = T1.fldDate AND 
	T2.fldTime = T1.fldTime AND 
	sysEnabled = 1 AND sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resultom)) {
		$ultimo_tc = $row['tc'];
		}
		mysqli_free_result($resultom);
	
		//valores de conversion tipo de cambio
		$resulttc = mysqli_query($link, "SELECT pkyExchangeRateDetail as codigotc, codBaseCurrency as monedabase, codTargetCurrency as monedacambio, fldRate as tc
	from
	actExchangeRateDetail
	where
	codExchangeRate = '$ultimo_tc' and
	codBaseCurrency = 2 and
	codTargetCurrency = 1 and
	sysEnabled = 1 AND sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resulttc)) {
		$codigo_tc = $row['codigotc'];
		$moneda_base = $row['monedabase'];
		$moneda_cambio = $row['monedacambio'];
		$tasa_tc = $row['tc'];
		}
		mysqli_free_result($resulttc);
	
	$matriz_contratos [$i][20] = $tasa_tc;
	$matriz_contratos [$i][21] = $matriz_contratos [$i][17] * $tasa_tc;
	
	//suma de cobro y debe (tambien saca el facturado pero ya hay en el query principal)
	$result3 = mysqli_query($link, "select sum(fin.facturado) as facturado, sum(fin.cobrado) as cobrado, sum(fin.debe) as debe 
	from (
	select a.pkySalesNote as cod_venta, a.fldDate as fecha, a.fldNumber as nro_doc, (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato) as facturado, d.fldAmount as cobrado, 0 as debe, a.codAccountReceivable as cod_cxc, a.codInvoice as cod_fac, c.fldNumber as nro_fac 
	from slsSalesNote a, txsSalesLedger c, slsSalesNoteVehicleContract d  
	where 
	d.codVehicleContract = '$cod_contrato' and 
	a.codInvoice = c.pkySalesLedger and 
	a.pkySalesNote = d.codSalesNote and 
	a.codAccountReceivable is NULL and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	union all
	select a.pkySalesNote as cod_venta, a.fldDate as fecha, a.fldNumber as nro_doc, (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato) as facturado, 
	case when b.fldBalance = 0 then (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato) 
	ELSE case when (b.fldTotal - b.fldBalance) != 0 then 
				case when (select COUNT(1) from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote) != 1 then (b.fldBalance / b.fldTotal) * (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato) ELSE (b.fldTotal - b.fldBalance) END 
	ELSE 0 END END as cobrado, 
	b.fldBalance as debe, a.codAccountReceivable as cod_cxc, a.codInvoice as cod_fac, c.fldNumber as nro_fac 
	from slsSalesNote a, colAccountsReceivable b, txsSalesLedger c, slsSalesNoteVehicleContract d  
	where 
	d.codVehicleContract = '$cod_contrato' and 
	a.codInvoice = c.pkySalesLedger and 
	a.pkySalesNote = d.codSalesNote and 
	a.codAccountReceivable = b.pkyAccountsReceivable and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) fin ;");
	while ($row = mysqli_fetch_array($result3)) {
		$matriz_contratos [$i][27] = $row['cobrado'];
		//$matriz_contratos [$i][28] = $row['debe'];
		$matriz_contratos [$i][28] = $matriz_contratos [$i][26] - $matriz_contratos [$i][27]; 
	}
	mysqli_free_result($result3);
	$i++;
}//fin while calculo 1


?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</td><td align="right"><b>Fecha: <?php echo date("Y-m-d"); ?><br/>Hora: <?php echo date("H:i:s"); ?><br/>Usuario: <?php echo $usuario; ?></b>
</td></tr></table>
<p><br /><center><font color="#000099" size="+1"><b>Contratos</b></font></center></p>
<table border="0" cellpadding="2" align="center" width="100%">
<tr>
<td>	
    <table border="1" align="center" cellpadding="2" width="100%">
        <TR> 
        <TD width="25%"><b>Veh&iacute;culo: </b><?php echo $nom_vehiculo; ?></TD>
        <TD width="25%"><b>Almacen: </b><?php echo $nom_almacen; ?></TD>
        <TD width="25%"><b>Estado: </b><?php echo $nom_estado; ?></TD>
        <TD width="25%"><b>Tipo: </b><?php echo $nom_tipo; ?></TD>
        </TR>
        <TR>
        <TD><b>Familia: </b><?php echo $nom_familia; ?></TD>
        <TD><b>Sub Familia: </b><?php echo $nom_subfamilia; ?></TD>
        <TD><b>Grupo: </b><?php echo $nom_grupo; ?></TD>
        <TD><b>Sub Grupo: </b><?php echo $nom_subgrupo; ?></TD>
        </TR>
        <TR>
        <TD><b>Categor&iacute;a: </b><?php echo $nom_categoria; ?></TD>
        <TD><b>Marca: </b><?php echo $nom_marca; ?></TD>
        <TD><b>Modelo: </b><?php echo $nom_modelo; ?></TD>
        <TD><b>Ordenado por: </b><?php echo $nom_orden; ?></TD>
        </TR>
        <TR>
        <TD><b>Cliente: </b><?php echo $nom_cliente; ?></TD>
        <TD><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
        <TD colspan="2"><b>Mostrar contratos: </b><?php echo $nom_por_fecha; ?></TD>
        </TR>
    </table>

</td>
<!--
<td valign="top" width="150">

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>&token=<?php echo $token; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br />
<table border="1" align="center" cellpadding="2" width="100%">
    <thead>
    <TR> 
    <TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Nro. Doc.</b></TD>
    <TD align="center"><b>Fecha</b></TD>
    <TD align="center"><b>Estado</b></TD>
    <TD align="center"><b>Tipo</b></TD>
    <TD align="center"><b>Almac&eacute;n</b></TD>
    <TD align="center"><b>Cliente</b></TD>
    <TD align="center"><b>Cat.</b></TD>
    <TD align="center"><b>Placa</b></TD>
    <TD align="center"><b>Veh&iacute;culo</b></TD>
    <TD align="center"><b>Fecha Entrega</b></TD>
    <TD align="center"><b>Fecha Recepci&oacute;n</b></TD>
    <TD align="center"><b>Extra Bs.</b></TD>
    <TD align="center"><b>Seguros Bs.</b></TD>
    <TD align="center"><b>Alquiler Bs.</b></TD>
    <TD align="center"><b>Total Parcial Bs.</b></TD>
    <TD align="center"><b>Lts. Gas. Faltante</b></TD>
    <TD align="center"><b>Gas. Faltante Bs.</b></TD>
    <TD align="center"><b>Km. Recorridos</b></TD>
    <TD align="center"><b>Km. Excedente Bs.</b></TD>
    <TD align="center"><b>Tiempo Excedente Bs.</b></TD>
    <TD align="center"><b>Dcto.(-)/Extra(+) Bs.</b></TD>
    <TD align="center"><b>Total Bs.</b></TD>
    <TD align="center"><b>Total $us.</b></TD>
    <TD align="center"><b>Total Facturado Bs.</b></TD>
    <TD align="center"><b>Cobro Facturado Bs.</b></TD>
    <TD align="center"><b>Deuda Facturado Bs.</b></TD>
    <TD align="center"><b>Por Facturar Bs.</b></TD>
    <?php if($km_ver == 1) { ?>
    <TD align="center"><b>Km. Entrega Parcial</b></TD>
    <TD align="center"><b>Km. Entrega Cliente</b></TD>
    <TD align="center"><b>Km. Entrega Diferencia</b></TD>
    <TD align="center"><b>Km. Recepci&oacute;n Cliente</b></TD>
    <TD align="center"><b>Km. Recepci&oacute;n Parcial</b></TD>
    <TD align="center"><b>Km. Recepci&oacute;n Diferencia</b></TD>
    <?php } ?>
    <?php if($gas_ver == 1) { ?>
    <TD align="center"><b>Gas. Entrega Parcial</b></TD>
    <TD align="center"><b>Gas. Entrega Cliente</b></TD>
    <TD align="center"><b>Gas. Entrega Diferencia</b></TD>
    <TD align="center"><b>Gas. Recepci&oacute;n Cliente</b></TD>
    <TD align="center"><b>Gas. Recepci&oacute;n Parcial</b></TD>
    <TD align="center"><b>Gas. Recepci&oacute;n Diferencia</b></TD>
    <?php } ?>
    <TD align="center"><b>Usuario</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

  <?php
  $color = 2;
  $suma_alquiler = 0;
  $suma_lts_gas = 0;
  $suma_cobro_gas = 0;
  $suma_km_extra = 0;
  $suma_km_recorridos = 0;
  $suma_tiempo_extra = 0;
  $suma_extras = 0;
  $suma_seguros = 0;
  $suma_total = 0;
  $suma_total_dolar = 0;
  $suma_total_parcial = 0;
  $suma_fac = 0;
  $cobro_fac = 0;
  $debe_fac = 0;
  $por_fac = 0;
  $dcto_extra = 0;
  $suma_km_entrega_parcial = 0;
  $suma_km_entrega_cliente = 0;
  $suma_km_entrega_dif = 0;
  $suma_km_recepcion_parcial = 0;
  $suma_km_recepcion_cliente = 0;
  $suma_km_recepcion_dif = 0;
  $suma_gas_entrega_parcial = 0;
  $suma_gas_entrega_cliente = 0;
  $suma_gas_entrega_dif = 0;
  $suma_gas_recepcion_parcial = 0;
  $suma_gas_recepcion_cliente = 0;
  $suma_gas_recepcion_dif = 0;
   
  $j = 1;

while ($j <= $fin) { //while
    $cod_estado = $matriz_contratos [$j][25];
	$cod_contrato = $matriz_contratos [$j][1];
	if($por_facturar == 0) { //if mostrar 0
		$mostrar_por_facturar = 1; //obligo a que muestre
	} else {
		$mostrar_por_facturar = $matriz_contratos [$j][17] - $matriz_contratos [$j][26];
	} //fin if mostrar 0
	
	if($mostrar_por_facturar != 0) { //if mostrar principal
	
	//verificar si es cerrado, para mostrar todo colocar 5
	if($cod_estado == 4) {
		$cod_estado_muestra = 5;
	} else {
		$cod_estado_muestra = $cod_estado;
	}
	if($color % 2 == 0) { //if color
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../VoucherVehicleContract/index.php?pkyVehicleContract=$cod_contrato&codStatus=$cod_estado_muestra&usuario_det=$usuario&token=$token' title='Contrato'>%s</a></td>", $matriz_contratos [$j][1]);//cod
	} else {
	printf("<tr><td align='center'><a href='../VoucherVehicleContract/index.php?pkyVehicleContract=$cod_contrato&codStatus=$cod_estado_muestra&usuario_det=$usuario&token=$token' title='Contrato'>%s</a></td>", $matriz_contratos [$j][1]);//cod
	}//fin if color
	printf("<td align='center'>%s</td>", $matriz_contratos [$j][2]);//nro doc
	printf("<td align='right'>%s</td>", $matriz_contratos [$j][3]);//fecha contrato
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][4]);//estado
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][5]);//tipo
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][6]);//almacen
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][7]);//cliente
	printf("<td align='center'>%s</td>", $matriz_contratos [$j][8]);//categoria
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][9]);//placa
	printf("<td align='left'>%s</td>", $matriz_contratos [$j][10]);//vehiculo
	printf("<td align='right'>%s</td>", $matriz_contratos [$j][11]);//fecha entrega
	if($cod_estado == 4) {
		printf("<td align='right'>%s</td>", $matriz_contratos [$j][13]);//fecha recepcion
	} else {
		printf("<td align='right'>%s</td>", $matriz_contratos [$j][12]);//fecha recepcion
	}
	printf("<td align='right'><a href='../../ModuleVehicles/ReportVehicleContract/report.php?rep=9&tipo=3&cod_contrato_det=$cod_contrato&usuario_det=$usuario&token=$token' title='Detalle Extras'>%s</a></td>", number_format($matriz_contratos [$j][18],$n_decimales));//costo equipo extra
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][19],$n_decimales));//costo seguros
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][14],$n_decimales));//alquiler
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][24],$n_decimales));//total parcial
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][15],0));//lts. gas. faltante
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][16],$n_decimales));//cobro gasolina
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][29],0));// km recorridos
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][22],$n_decimales));//cobro km excedente
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][23],$n_decimales));//cobro tiempo excedente
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][39],$n_decimales));//descuento / extra
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][17],$n_decimales));//total
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][21],$n_decimales));//total en $us
	printf("<td align='right'><a href='../../ModuleVehicles/ReportVehicleContract/report.php?rep=9&tipo=2&cod_contrato_det=$cod_contrato&usuario_det=$usuario&token=$token' title='Detalle Nota de Venta'>%s</a></td>", number_format($matriz_contratos [$j][26],$n_decimales));//total facturado
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][27],$n_decimales));//total cobro facturado
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][28],$n_decimales));//total debe facturado
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][17] - $matriz_contratos [$j][26],$n_decimales));//total por facturar
	if($km_ver == 1) {
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][35],0));// km entrega parcial
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][31],0));// km entrega cliente
	$dif_km_entrega = $matriz_contratos [$j][31] - $matriz_contratos [$j][35];
	printf("<td align='right'>%s</td>", number_format($dif_km_entrega,0));// km entrega diferencia
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][33],0));// km recepcion cliente
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][37],0));// km recepcion parcial
	$dif_km_recepcion = $matriz_contratos [$j][37] - $matriz_contratos [$j][33];
	printf("<td align='right'>%s</td>", number_format($dif_km_entrega,0));// km recepcion diferencia
	}
	if($gas_ver == 1) {
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][36],0));// gas entrega parcial
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][32],0));// gas entrega cliente
	$dif_gas_entrega = $matriz_contratos [$j][36] - $matriz_contratos [$j][32];
	printf("<td align='right'>%s</td>", number_format($dif_gas_entrega,0));// gas entrega diferencia
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][34],0));// gas recepcion cliente
	printf("<td align='right'>%s</td>", number_format($matriz_contratos [$j][38],0));// gas recepcion parcial
	$dif_gas_recepcion = $matriz_contratos [$j][34] - $matriz_contratos [$j][38];
	printf("<td align='right'>%s</td>", number_format($dif_gas_entrega,0));// gas recepcion diferencia
	}
	
	printf("<td align='right'>%s</td></tr>", $matriz_contratos [$j][30]);//usuario
	
	$suma_alquiler = $suma_alquiler + $matriz_contratos [$j][14];
	$suma_lts_gas = $suma_lts_gas + $matriz_contratos [$j][15];
    $suma_cobro_gas = $suma_cobro_gas + $matriz_contratos [$j][16];
	$suma_km_extra = $suma_km_extra + $matriz_contratos [$j][22];
	$suma_km_recorridos = $suma_km_recorridos + $matriz_contratos [$j][29];
	$suma_tiempo_extra = $suma_tiempo_extra + $matriz_contratos [$j][23];
    $suma_extras = $suma_extras + $matriz_contratos [$j][18];
    $suma_seguros = $suma_seguros + $matriz_contratos [$j][19];
    $suma_total = $suma_total + $matriz_contratos [$j][17];
    $suma_total_dolar = $suma_total_dolar + $matriz_contratos [$j][21];
	$suma_total_parcial = $suma_total_parcial + $matriz_contratos [$j][24];
	$suma_fac = $suma_fac + $matriz_contratos [$j][26];
	$cobro_fac = $cobro_fac + $matriz_contratos [$j][27];
	$debe_fac = $debe_fac + $matriz_contratos [$j][28];
	$por_fac = $por_fac + $matriz_contratos [$j][17] - $matriz_contratos [$j][26];
	$dcto_extra = $dcto_extra + $matriz_contratos [$j][39];
	
	$suma_km_entrega_parcial = $suma_km_entrega_parcial + $matriz_contratos [$j][35];
  	$suma_km_entrega_cliente = $suma_km_entrega_cliente + $matriz_contratos [$j][31];
	$suma_km_entrega_dif = $suma_km_entrega_dif + $dif_km_entrega;
	$suma_km_recepcion_cliente = $suma_km_recepcion_cliente + $matriz_contratos [$j][33];
	$suma_km_recepcion_parcial = $suma_km_recepcion_parcial + $matriz_contratos [$j][37];
  	$suma_km_recepcion_dif = $suma_km_recepcion_dif + $dif_km_recepcion;
	$suma_gas_entrega_parcial = $suma_gas_entrega_parcial + $matriz_contratos [$j][36];
  	$suma_gas_entrega_cliente = $suma_gas_entrega_cliente + $matriz_contratos [$j][32];
	$suma_gas_entrega_dif = $suma_gas_entrega_dif + $dif_gas_entrega;
	$suma_gas_recepcion_cliente = $suma_gas_recepcion_cliente + $matriz_contratos [$j][34];
	$suma_gas_recepcion_parcial = $suma_gas_recepcion_parcial + $matriz_contratos [$j][38];
  	$suma_gas_recepcion_dif = $suma_gas_recepcion_dif + $dif_gas_recepcion;
	$color++;
	} //fin if mostrar principal
	$j++;
}//fin while
	
?>

<tr BGCOLOR='#ffcccc'><td colspan="12"><b>Totales</b>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_extras,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_seguros,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_alquiler,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_total_parcial,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_lts_gas,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_cobro_gas,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_recorridos,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_extra,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_tiempo_extra,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($dcto_extra,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_total,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_total_dolar,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_fac,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($cobro_fac,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($debe_fac,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($por_fac,$n_decimales)); ?>
<?php if($km_ver == 1) { ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_entrega_parcial,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_entrega_cliente,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_entrega_dif,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_recepcion_cliente,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_recepcion_parcial,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_km_recepcion_dif,0)); ?> 
<?php } ?>
<?php if($gas_ver == 1) { ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_entrega_parcial,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_entrega_cliente,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_entrega_dif,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_recepcion_cliente,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_recepcion_parcial,0)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_gas_recepcion_dif,0)); ?> 
<?php } ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($j - 1,0)); ?>
</td></tr>
</tbody>
</table>
<?php

} //fin if 2



//Inicio detalle reporte Contratos de vehiculos

if($rep == 9 && $tipo == 2) { // if 3

//detalle de las notas de venta
$result3 = mysqli_query($link, "select a.pkySalesNote as cod_venta, a.fldDate as fecha, a.fldNumber as nro_doc, d.fldName as cliente, e.fldName as almacen, g.fldName as tipo, f.fldName as moneda, (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato_det) as facturado, a.fldNetTotal as cobrado, 0 as debe, a.codAccountReceivable as cod_cxc, a.codInvoice as cod_fac, c.fldNumber as nro_fac 
from slsSalesNote a, txsSalesLedger c, agnCompany d, invWarehouse e, actCurrency f, slsSalesNoteType g, slsSalesNoteVehicleContract h
where 
h.codVehicleContract = '$cod_contrato_det' and 
a.pkySalesNote = h.codSalesNote and 
a.codInvoice = c.pkySalesLedger and 
a.codAccountReceivable is NULL and 
a.codClient = d.pkyCompany and 
a.codWarehouse = e.pkyWarehouse and 
a.codCurrency = f.pkyCurrency and 
a.codType = g.pkySalesNoteType and 
a.sysEnabled = 1 and a.sysAuthorized = 1
union all
select a.pkySalesNote as cod_venta, a.fldDate as fecha, a.fldNumber as nro_doc, d.fldName as cliente, e.fldName as almacen, g.fldName as tipo, f.fldName as moneda, (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato_det) as facturado, 
case when b.fldBalance = 0 then (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato_det) 
	ELSE case when (b.fldTotal - b.fldBalance) != 0 then 
				case when (select COUNT(1) from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote) != 1 then (b.fldBalance / b.fldTotal) * (SELECT d1.fldAmount from slsSalesNoteVehicleContract d1 where d1.codSalesNote = a.pkySalesNote AND d1.codVehicleContract = $cod_contrato_det) ELSE (b.fldTotal - b.fldBalance) END 
	ELSE 0 END END as cobrado,  
b.fldBalance as debe, a.codAccountReceivable as cod_cxc, a.codInvoice as cod_fac, c.fldNumber as nro_fac 
from slsSalesNote a, colAccountsReceivable b, txsSalesLedger c, agnCompany d, invWarehouse e, actCurrency f, slsSalesNoteType g, slsSalesNoteVehicleContract h  
where 
h.codVehicleContract = '$cod_contrato_det' and 
a.pkySalesNote = h.codSalesNote and 
a.codInvoice = c.pkySalesLedger and 
a.codAccountReceivable = b.pkyAccountsReceivable and 
a.codClient = d.pkyCompany and 
a.codWarehouse = e.pkyWarehouse and 
a.codCurrency = f.pkyCurrency and 
a.codType = g.pkySalesNoteType and 
a.sysEnabled = 1 and a.sysAuthorized = 1 
order by fecha, cod_venta;");

?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</td><td align="right"><b>Fecha: <?php echo date("Y-m-d"); ?><br/>Hora: <?php echo date("H:i:s"); ?><br/>Usuario: <?php echo $usuario_det; ?></b>
</td></tr></table>
<p><br /><center><font color="#000099" size="+1"><b>Detalle Nota de Venta</b></font></center></p>
<table border="0" cellpadding="2" align="center" width="100%">
<tr>
<td>	
    <table border="1" align="center" cellpadding="2" width="100%">
        <TR> 
        <TD width="50%"><b>Contrato: </b><?php echo $cod_contrato_det; ?></TD>
        <TD width="50%"><b>Ordenado por: </b>Fecha</TD>
        </TR>
    </table>

</td>
<!--
<td valign="top" width="150">

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>&token=<?php echo $token; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br />
<table border="1" align="center" cellpadding="2" width="100%">
    <thead>
    <TR> 
    <TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Nro. Doc.</b></TD>
    <TD align="center"><b>Fecha</b></TD>
    <TD align="center"><b>Almac&eacute;n</b></TD>
    <TD align="center"><b>Cliente</b></TD>
    <TD align="center"><b>Tipo</b></TD>
    <TD align="center"><b>CxC</b></TD>
    <TD align="center"><b>Factura</b></TD>
    <TD align="center"><b>Mon.</b></TD>
    <TD align="center"><b>Monto Facturado</b></TD>
    <TD align="center"><b>Monto Cobrado</b></TD>
    <TD align="center"><b>Monto por Cobrar</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

<?php
$color = 2;
$suma_fac = 0;
$cobro_fac = 0;
$debe_fac = 0;
     
while ($row = mysqli_fetch_array($result3)) { //while
    $cod_venta = $row['cod_venta'];
	$cod_cxc = $row['cod_cxc'];
	$cod_fac = $row['cod_fac'];
	
	if($color % 2 == 0) { //if color
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModuleSales/VoucherSalesNote/index.php?pkySalesNote=$cod_venta&user=$usuario_det&token=$token' title='Nota de Venta'>%s</a></td>", $row['cod_venta']);//cod
	} else {
	printf("<tr><td align='center'><a href='../../ModuleSales/VoucherSalesNote/index.php?pkySalesNote=$cod_venta&user=$usuario_det&token=$token' title='Nota de Venta'>%s</a></td>", $row['cod_venta']);//cod
	}//fin if color
	printf("<td align='center'>%s</td>", $row['nro_doc']);//nro doc
	printf("<td align='right'>%s</td>", $row['fecha']);//fecha
	printf("<td align='left'>%s</td>", $row['almacen']);//almacen
	printf("<td align='left'>%s</td>", $row['cliente']);//cliente
	printf("<td align='left'>%s</td>", $row['tipo']);//tipo
	printf("<td align='center'><a href='../../ModuleCollections/VoucherAccountsReceivable/index.php?pkyAccountsReceivable=$cod_cxc&user=$usuario_det&token=$token' title='Nota de Cuenta por Cobrar'>%s</a></td>", $row['cod_cxc']);//codigo cxc
	printf("<td align='right'><a href='../../ModuleTaxes/VoucherSalesLedger/index.php?pkySalesLedger=$cod_fac&sheetSize=$formato_factura&head=$muestra_cabecera&copy=0&user=$usuario_det&token=$token' title='Factura'>%s</a> (%s)</td>", $row['cod_fac'], $row['nro_fac']);//factura
	printf("<td align='left'>%s</td>", $row['moneda']);//moneda
	printf("<td align='right'>%s</td>", number_format($row['facturado'],$n_decimales));//monto facturado
	
	if($cod_cxc != NULL) {
	printf("<td align='right'><a href='../../ModuleCollections/ReportAccountsReceivable/report.php?rep=6&tipo=2&cod_cxc=$cod_cxc&empresa=1&otra_moneda_det=$cod_moneda_principal&contexto_det=0&usuario_det=$usuario_det&token=$token' title='Detalle Cuenta por Cobrar'>%s</a></td>", number_format($row['cobrado'],$n_decimales));//monto cobrado (ojo no hay contexto en el contrato por eso &contexto_det=0 hardcode)
	} else {
	printf("<td align='right'>%s</td>", number_format($row['cobrado'],$n_decimales));//monto cobrado	
	}
	
	printf("<td align='right'>%s</td></tr>", number_format($row['facturado'] - $row['cobrado'],$n_decimales));//monto por cobrar
	
	$suma_fac = $suma_fac + $row['facturado'];
	$cobro_fac = $cobro_fac + $row['cobrado'];
	$debe_fac = $debe_fac + $row['facturado'] - $row['cobrado'];
	$color++;
}//fin while
mysqli_free_result($result3);
	
?>

<tr BGCOLOR='#ffcccc'><td colspan="9"><b>Totales</b>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_fac,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($cobro_fac,$n_decimales)); ?>
</td><td align="right"><?php printf("<b>%s</b>", number_format($debe_fac,$n_decimales)); ?>
</td></tr>
</tbody>
</table>
<?php

} //fin if 3


if($rep == 9 && $tipo == 3) { // if 4

//detalle de extras
$result3 = mysqli_query($link, "select a.codVehicleExtra as cod_extra, b.fldName as extra, a.fldTotal as precio_extras
	from vhcVehicleContractExtra a, vhcVehicleExtra b
	where
	a.codVehicleExtra = b.pkyVehicleExtra and 
	a.codContract = '$cod_contrato_det' and
	a.sysEnabled = 1 and a.sysAuthorized = 1
	order by extra;");

?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</td><td align="right"><b>Fecha: <?php echo date("Y-m-d"); ?><br/>Hora: <?php echo date("H:i:s"); ?><br/>Usuario: <?php echo $usuario_det; ?></b>
</td></tr></table>
<p><br /><center><font color="#000099" size="+1"><b>Detalle Extras</b></font></center></p>
<table border="0" cellpadding="2" align="center" width="100%">
<tr>
<td>	
    <table border="1" align="center" cellpadding="2" width="100%">
        <TR> 
        <TD width="50%"><b>Contrato: </b><?php echo $cod_contrato_det; ?></TD>
        <TD width="50%"><b>Ordenado por: </b>Nombre</TD>
        </TR>
    </table>

</td>
<!--
<td valign="top" width="150">

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>&token=<?php echo $token; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br />
<table border="1" align="center" cellpadding="2" width="100%">
    <thead>
    <TR> 
    <TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Extra</b></TD>
    <TD align="center"><b>Monto en Bs.</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

<?php
$color = 2;
$suma_extra = 0;
     
while ($row = mysqli_fetch_array($result3)) { //while
	
	if($color % 2 == 0) { //if color
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>%s</td>", $row['cod_extra']);//cod
	} else {
	printf("<tr><td align='center'>%s</td>", $row['cod_extra']);//cod
	}//fin if color
	printf("<td align='left'>%s</td>", $row['extra']);//extra
	printf("<td align='right'>%s</td></tr>", number_format($row['precio_extras'],$n_decimales));//monto extras
	
	$suma_extra = $suma_extra + $row['precio_extras'];
	$color++;
}//fin while
mysqli_free_result($result3);
	
?>

<tr BGCOLOR='#ffcccc'><td colspan="2"><b>Total</b>
</td><td align="right"><?php printf("<b>%s</b>", number_format($suma_extra,$n_decimales)); ?>
</td></tr>
</tbody>
</table>
<?php

} //fin if 4

?>

</body>
</html>
