<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Reporte Tasa de Uso</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"];
$familia=$_POST["familia"];
$subfamilia=$_POST["subfamilia"];
$grupo=$_POST["grupo"];
$subgrupo=$_POST["subgrupo"];
$vehiculo=$_POST["vehiculo"];
$almacen=$_POST["almacen"];
$agrupar=$_POST["agrupar"];
$fechaini=$_POST["fechaini"];
$fechafin=$_POST["fechafin"];
//$usuario=$_POST["usuario"];

$n_decimales = 2;

// Calcula el numero de dias entre dos fechas.
// Da igual el formato de las fechas (dd-mm-aaaa o aaaa-mm-dd),
// pero el caracter separador debe ser un guión.
function diasEntreFechas($fechainicio, $fechafinal){
    return round((strtotime($fechafinal)-strtotime($fechainicio))/86400);
}

?>
<body>
<?php
if($familia == '') { $familia = 0;}
if($subfamilia == '') { $subfamilia = 0;}
if($grupo == '') { $grupo = 0;}
if($subgrupo == '') { $subgrupo = 0;}

$dias_periodo = diasEntreFechas($fechaini, $fechafin);

//Inicio reporte tasa de uso

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

	if ($agrupar == 0) { // if 2.0
		$familia = 0;
		$subfamilia = 0;
		$grupo = 0;
		$subgrupo = 0;
		$valor_agrupar = ' group by t.cod_vehi ';
		$nom_agrupar = 'Veh&iacute;culo';
	} //fin if 2.0
	if ($agrupar == 1) { // if 2.1
		$vehiculo = 0;
		$valor_agrupar = ' group by t.cod_familia ';
		$nom_agrupar = 'Familia';
	} //fin if 2.1
	if ($agrupar == 2) { // if 2.2
		$vehiculo = 0;
		$valor_agrupar = ' group by t.cod_familia, t.cod_subfamilia ';
		$nom_agrupar = 'Sub Familia';
	} //fin if 2.2
	if ($agrupar == 3) { // if 2.3
		$vehiculo = 0;
		$valor_agrupar = ' group by t.cod_familia, t.cod_subfamilia, t.cod_grupo ';
		$nom_agrupar = 'Grupo';
	} //fin if 2.3
	if ($agrupar == 4) { // if 2.4
		$vehiculo = 0;
		$valor_agrupar = ' group by t.cod_familia, t.cod_subfamilia, t.cod_grupo, t.cod_subgrupo ';
		$nom_agrupar = 'Sub Grupo';
	} //fin if 2.4
	
	
	if ($almacen == 0) { // if 2.1
		$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 = ' d.codWarehouse = ' .$almacen. ' and ';
	} //fin if 2.1
	
	
	if ($vehiculo == 0) { // if 2.2
		$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.2
	
	
	if ($familia == 0) { // if 2.3
		$nom_familia = 'Todos';
		$valor_familia = ' ';
	} else {
		//nombre familia
		$result3 = 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($result3)) {
			$nom_familia = $row['nombre'];
		}
		mysqli_free_result($result3);
		$valor_familia = ' b.codFamily = ' .$familia. ' and ';
	} //fin if 2.3
	
	
	if ($subfamilia == 0) { // if 2.4
		$nom_subfamilia = 'Todos';
		$valor_subfamilia = ' ';
	} else {
		//nombre sub familia
		$result5 = 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($result5)) {
			$nom_subfamilia = $row['nombre'];
		}
		mysqli_free_result($result5);
		$valor_subfamilia = ' b.codSubFamily = ' .$subfamilia. ' and ';
	} //fin if 2.4
	
	
	if ($grupo == 0) { // if 2.5
		$nom_grupo = 'Todos';
		$valor_grupo = ' ';
	} else {
		//nombre grupo
		$result6 = 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($result6)) {
			$nom_grupo = $row['nombre'];
		}
		mysqli_free_result($result6);
		$valor_grupo = ' b.codGroup = ' .$grupo. ' and ';
	} //fin if 2.5
	
	
	if ($subgrupo == 0) { // if 2.6
		$nom_subgrupo = 'Todos';
		$valor_subgrupo = ' ';
	} else {
		//nombre sub grupo
		$result7 = 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($result7)) {
			$nom_subgrupo = $row['nombre'];
		}
		mysqli_free_result($result7);
		$valor_subgrupo = ' b.codSubGroup = ' .$subgrupo. ' and ';
	} //fin if 2.6
		



		//listado vehiculos con los dias de uso
		$result = mysqli_query($link, "select t.cod_vehi as cod_vehi, t.vehiculo as vehiculo,
t.cod_familia as cod_familia, t.cod_subfamilia as cod_subfamilia,
t.cod_grupo as cod_grupo, t.cod_subgrupo, t.placa as placa,
t.categoria as categoria, t.almacen as almacen,
sum(t.dias) as dias
from (
select a.pkyVehicleContract as cod, a.codType as cod_tipo,
a.codClient as cod_cli, a.codVehicle as cod_vehi,
a.fldDeliveryDate as fecha_ent, a.fldReceptionDate as fecha_rec,
a.codStatus as cod_estado, a.fldRealReceptionDate as fecha_real_rec,
b.fldName as vehiculo, b.codFamily as cod_familia,
b.codSubFamily as cod_subfamilia, b.codGroup as cod_grupo,
b.codSubGroup as cod_subgrupo, c.fldCarPlate as placa,
c.fldCategory as categoria, e.fldName as almacen, 
case a.codStatus when 2 then 
case when a.fldDeliveryDate > '$fechaini' and a.fldDeliveryDate < '$fechafin' and a.fldReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert(a.fldDeliveryDate,date)) 
when a.fldReceptionDate > '$fechaini' and a.fldReceptionDate < '$fechafin' and a.fldDeliveryDate < '$fechaini' then 
datediff(convert(a.fldReceptionDate,date), convert('$fechaini',date)) 
when a.fldDeliveryDate <= '$fechaini' and a.fldReceptionDate = '$fechaini' then 1
when a.fldDeliveryDate = '$fechafin' and a.fldReceptionDate >= '$fechafin' then 1 
when a.fldDeliveryDate >= '$fechaini' and a.fldReceptionDate <= '$fechafin' then 
datediff(convert(a.fldReceptionDate,date), convert(a.fldDeliveryDate,date)) 
when a.fldDeliveryDate < '$fechaini' and a.fldReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert('$fechaini',date))
else 0 end 

when 3 then 
case when a.fldDeliveryDate > '$fechaini' and a.fldDeliveryDate < '$fechafin' and a.fldReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert(a.fldDeliveryDate,date)) 
when a.fldReceptionDate > '$fechaini' and a.fldReceptionDate < '$fechafin' and a.fldDeliveryDate < '$fechaini' then 
datediff(convert(a.fldReceptionDate,date), convert('$fechaini',date)) 
when a.fldDeliveryDate <= '$fechaini' and a.fldReceptionDate = '$fechaini' then 1
when a.fldDeliveryDate = '$fechafin' and a.fldReceptionDate >= '$fechafin' then 1 
when a.fldDeliveryDate >= '$fechaini' and a.fldReceptionDate <= '$fechafin' then 
datediff(convert(a.fldReceptionDate,date), convert(a.fldDeliveryDate,date)) 
when a.fldDeliveryDate < '$fechaini' and a.fldReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert('$fechaini',date))
else 0 end  

else 0 end as dias
from
vhcVehicleContract a, invItem b, vhcVehicleDetail c,
invItemMovementConsolidated d, invWarehouse e
where
$valor_almacen 
$valor_vehiculo 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo   
((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
(a.codStatus = 2 or a.codStatus = 3) and 
a.codVehicle = b.pkyItem and
a.codVehicle = c.codItem and
a.codVehicle = d.codItem and
d.fldQuantity > 0 and
a.codType <> 3 and
d.codWarehouse = e.pkyWarehouse and 
a.codStatus <> 1 and
a.sysEnabled = 1 and a.sysAuthorized = 1 

union all 

select a.pkyVehicleContract as cod, a.codType as cod_tipo,
a.codClient as cod_cli, a.codVehicle as cod_vehi,
a.fldDeliveryDate as fecha_ent, a.fldReceptionDate as fecha_rec,
a.codStatus as cod_estado, a.fldRealReceptionDate as fecha_real_rec,
b.fldName as vehiculo, b.codFamily as cod_familia,
b.codSubFamily as cod_subfamilia, b.codGroup as cod_grupo,
b.codSubGroup as cod_subgrupo, c.fldCarPlate as placa,
c.fldCategory as categoria, e.fldName as almacen, 
case a.codStatus when 4 then
case when a.fldDeliveryDate > '$fechaini' and a.fldDeliveryDate < '$fechafin' and a.fldRealReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert(a.fldDeliveryDate,date)) 
when a.fldRealReceptionDate > '$fechaini' and a.fldRealReceptionDate < '$fechafin' and a.fldDeliveryDate < '$fechaini' then 
datediff(convert(a.fldRealReceptionDate,date), convert('$fechaini',date)) 
when a.fldDeliveryDate <= '$fechaini' and a.fldRealReceptionDate = '$fechaini' then 1
when a.fldDeliveryDate = '$fechafin' and a.fldRealReceptionDate >= '$fechafin' then 1 
when a.fldDeliveryDate >= '$fechaini' and a.fldRealReceptionDate <= '$fechafin' then 
datediff(convert(a.fldRealReceptionDate,date), convert(a.fldDeliveryDate,date)) 
when a.fldDeliveryDate < '$fechaini' and a.fldRealReceptionDate > '$fechafin' then 
datediff(convert('$fechafin',date), convert('$fechaini',date))
else 0 end   

else 0 end as dias
from
vhcVehicleContract a, invItem b, vhcVehicleDetail c,
invItemMovementConsolidated d, invWarehouse e
where
$valor_almacen 
$valor_vehiculo 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo  
((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 
a.codStatus = 4 and 
a.codVehicle = b.pkyItem and
a.codVehicle = c.codItem and
a.codVehicle = d.codItem and
d.fldQuantity > 0 and
a.codType <> 3 and
d.codWarehouse = e.pkyWarehouse and 
a.codStatus <> 1 and
a.sysEnabled = 1 and a.sysAuthorized = 1) t
$valor_agrupar;");
		
$matriz_uso = array();
$i = 1;
while ($row = mysqli_fetch_array($result)) {
	$matriz_uso [$i][1] = $row['cod_vehi'];
	$matriz_uso [$i][2] = $row['vehiculo'];
	$matriz_uso [$i][3] = $row['placa'];
	$matriz_uso [$i][4] = $row['categoria'];
	$matriz_uso [$i][5] = $row['almacen'];
	$matriz_uso [$i][6] = $row['cod_familia'];
	$matriz_uso [$i][7] = $row['cod_subfamilia'];
	$matriz_uso [$i][8] = $row['cod_grupo'];
	$matriz_uso [$i][9] = $row['cod_subgrupo'];
	$matriz_uso [$i][10] = $row['dias'];
	
	$i++;
}
$fin = $i - 1;
mysqli_free_result($result);


?>
<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>Tasa de Uso</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>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?> (<?php echo $dias_periodo; ?> d&iacute;as)</TD>
        <TD width="25%"><b>Almacen: </b><?php echo $nom_almacen; ?></TD>
        <TD width="25%"><b>Agrupado por: </b><?php echo $nom_agrupar; ?></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>
    </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> 
    <?php if($agrupar == 0) { ?>
    <TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Veh&iacute;culo</b></TD>
    <TD align="center"><b>Placa</b></TD>
    <TD align="center"><b>Cat.</b></TD>
    <?php } ?>
    <TD align="center"><b>Almac&eacute;n</b></TD>
    <TD align="center"><b>Familia</b></TD>
    <TD align="center"><b>Sub Familia</b></TD>
    <TD align="center"><b>Grupo</b></TD>
    <TD align="center"><b>Sub Grupo</b></TD>
    <TD align="center"><b>D&iacute;as de Uso</b></TD>
    <TD align="center"><b>Tasa de Uso</b></TD>
    <TD align="center"><b>Tasa de Uso %</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_tiempo_extra = 0;
  $suma_extras = 0;
  $suma_seguros = 0;
  $suma_total = 0;
  $suma_total_dolar = 0;
  $suma_total_parcial = 0;
   
  $j = 1;
  while ($j <= $fin) { //while
  if($agrupar == 0) { //if agrupar 0
    if($color % 2 == 0) { //if color
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>%s</td>", $matriz_uso [$j][1]);//cod vehi
	} else {
	printf("<tr><td align='center'>%s</td>", $matriz_uso [$j][1]);//cod vehi
	}//fin if color
	printf("<td align='left'>%s</td>", $matriz_uso [$j][2]);//vehiculo
	printf("<td align='left'>%s</td>", $matriz_uso [$j][3]);//placa
	printf("<td align='center'>%s</td>", $matriz_uso [$j][4]);//categoria
	printf("<td align='left'>%s</td>", $matriz_uso [$j][5]);//almacen
  } //fin if agrupar 0
  if($agrupar != 0) { //if agrupar 1
  	if($color % 2 == 0) { //if color
	printf("<tr BGCOLOR='#e2e7fa'><td align='left'>%s</td>", $matriz_uso [$j][5]);//almacen
	} else {
	printf("<tr><td align='left'>%s</td>", $matriz_uso [$j][5]);//almacen
	}//fin if color
  } //fin if agrupar 1
	
	$cod_familia2 = $matriz_uso [$j][6];
	$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$cod_familia2' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultfam)) {
			$nom_familia2 = $row['nombre'];
		}
		mysqli_free_result($resultfam);
	printf("<td align='left'>%s</td>", $nom_familia2);//familia
	$cod_subfamilia2 = $matriz_uso [$j][7];
	$resultsfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$cod_subfamilia2' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsfam)) {
			$nom_subfamilia2 = $row['nombre'];
		}
		mysqli_free_result($resultsfam);
	printf("<td align='left'>%s</td>", $nom_subfamilia2);//sub familia
	$cod_grupo2 = $matriz_uso [$j][8];
	$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$cod_grupo2' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgru)) {
			$nom_grupo2 = $row['nombre'];
		}
		mysqli_free_result($resultgru);
	printf("<td align='left'>%s</td>", $nom_grupo2);//grupo
	$cod_subgrupo2 = $matriz_uso [$j][9];
	$resultsgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$cod_subgrupo2' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsgru)) {
			$nom_subgrupo2 = $row['nombre'];
		}
		mysqli_free_result($resultsgru);
	printf("<td align='left'>%s</td>", $nom_subgrupo2);//sub grupo
	
	
	if($dias_periodo == 0) {
		$tasa_de_uso = 0;
		$tasa_de_uso2 = 0;
	} else {
		$tasa_de_uso = $matriz_uso [$j][10] / $dias_periodo;
		$tasa_de_uso2 = $matriz_uso [$j][10] / $dias_periodo * 100;
	}
	
	//controlar si es negativo o si es mayor para imprimir en rojo
	if($matriz_uso [$j][10] < 0 || $matriz_uso [$j][10] > $dias_periodo) {
	printf("<td align='right'><font color='#ff0000'><b>%s</b></font></td>", number_format($matriz_uso [$j][10],0));//dias de uso
	printf("<td align='right'><font color='#ff0000'><b>%s</b></font></td>", number_format($tasa_de_uso,$n_decimales));//tasa de uso
	printf("<td align='right'><font color='#ff0000'><b>%s</b></font></td></tr>", number_format($tasa_de_uso2,$n_decimales));//tasa de uso %
	} else {
	printf("<td align='right'>%s</td>", number_format($matriz_uso [$j][10],0));//dias de uso
	printf("<td align='right'>%s</td>", number_format($tasa_de_uso,$n_decimales));//tasa de uso
	printf("<td align='right'>%s</td></tr>", number_format($tasa_de_uso2,$n_decimales));//tasa de uso %
	}
	
	$nom_familia2 = NULL;
	$nom_subfamilia2 = NULL;
	$nom_grupo2 = NULL;
	$nom_subgrupo2 = NULL;
	$color++;
	$j++;
  }//fin while
?>	
</tbody>
</table>
<?php
} //fin if 2
?>

</body>
</html>
