<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Reporte Mantenimientos 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"];
$sucursal=$_POST["sucursal"];
$responsable=$_POST["responsable"];
$vehiculo=$_POST["vehiculo"];
$orden=$_POST["orden"];
$marca=$_POST["marca"];
$modelo=$_POST["modelo"];
$familia=$_POST["familia"];
$subfamilia=$_POST["subfamilia"];
$grupo=$_POST["grupo"];
$subgrupo=$_POST["subgrupo"];
$fechaini=$_POST["fechaini"];
$fechafin=$_POST["fechafin"];
$contexto=$_POST["contexto"];
//$usuario=$_POST["usuario"];

$familia_ver=$_POST["familia_ver"];
$subfamilia_ver=$_POST["subfamilia_ver"];
$grupo_ver=$_POST["grupo_ver"];
$subgrupo_ver=$_POST["subgrupo_ver"];


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;
$nro_col = 13; //numero de columnas por defecto 

//para que muestre el logo en lugar del texto empresa y NIT
$mostrar_logo_reportes = '<img src="../../Images/logo.jpg" alt="Logo" />';

?>
<body>
<?php


//Inicio reporte Mantenimiento de vehiculos

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

 	if ($sucursal == 0) { // if 2.2
		$nom_sucursal = 'Todos';
		$valor_sucursal = ' ';
	} else {
		//nombre sucursal
		$result1 = mysqli_query($link, "select a.pkyCompanyBranch as cod_suc, a.codCompany as cod_emp, a.fldName as sucursal, a.fldID as nro_suc, a.codCity as cod_ciu, b.fldName as ciudad, a.fldPhone as tel, a.fldAddress as direccion
								from agnCompanyBranch a, regCity b 
								where 
								a.codCompany = 2 and 
								a.codCity = b.pkyCity and 
								a.pkyCompanyBranch = '$sucursal';");
		while ($row = mysqli_fetch_array($result1)) {
			$nom_sucursal = $row['nro_suc'].' - '.$row['sucursal'].' ('.$row['ciudad'].')';
		}
		mysqli_free_result($result1);
		$valor_sucursal = ' a.codCompanyBranch = ' .$sucursal. ' and ';
	} //fin if 2.2
	
	if ($responsable == 0) { // if 2.3
		$nom_responsable = 'Todos';
		$valor_responsable = ' ';
	} else {
		//nombre responsable
		$result2 = mysqli_query($link, "select pkyPerson as codigo, case when fldMiddleName is NULL then concat(fldLastName,', ',fldFirstName) else concat(fldLastName,' ',fldMiddleName,', ',fldFirstName) end as persona
								from
								agnPerson 
								where
								pkyPerson = '$responsable' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_responsable = $row['persona'];
		}
		mysqli_free_result($result2);
		$valor_responsable = ' a.codResponsible = ' .$responsable. ' and ';
	} //fin if 2.3
	
	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 ($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 = ' c.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 = ' c.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 = ' c.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 = ' c.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 = ' c.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 = ' c.codSubGroup = ' .$subgrupo. ' and ';
	} //fin if subgrupo

	//contexto
	if($contexto == -1){ 
		$nom_contexto = 'Todos';
		$valor_contexto = ' ';
	} else {
		$resultcontx = mysqli_query($link, "select pkyContext as cod_contexto, fldName as contexto, codCostCenter as cod_cc 
		from srvContext
		where 
		pkyContext = '$contexto';");
			while ($row = mysqli_fetch_array($resultcontx)) {   
				$nom_contexto = $row['cod_contexto'].' - '.$row['contexto'];
			}
			mysqli_free_result($resultcontx);
		$valor_contexto = ' a.sysContext = '.$contexto.' and ';	
	}

	if ($orden == 0) { // if 2.7
		$nom_orden = 'Fecha';
		$valor_orden = ' order by fecha ';
	} else {
		$nom_orden = 'Vehículo';
		$valor_orden = ' order by vehiculo ';
	} //fin if 2.7
	

	//listado de mantenimientos
	$result = mysqli_query($link, "SELECT a.pkyMaintenanceOrder AS cod_mant, a.fldDocumentNumber AS nro_doc, a.fldDate AS fecha, a.fldTime AS hora, a.codResponsible AS cod_resp, case when b.fldMiddleName is NULL then CONCAT(b.fldLastName,', ',b.fldFirstName) else CONCAT(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end AS responsable, a.codVehicle AS cod_vehi, c.fldName as vehiculo, d.fldCarPlate as placa, a.fldFuel AS gasolina, a.fldMileage AS km, a.codCompanyBranch AS cod_sucursal, case when a.codCompanyBranch IS NULL then NULL ELSE (SELECT CONCAT (a1.fldID,' - ',a1.fldName) FROM agnCompanyBranch a1 WHERE a1.pkyCompanyBranch = a.codCompanyBranch) END AS sucursal, a.txtExtraData AS glosa, h.fldName as usuario_reg, 
	case when c.codBrand IS NULL then NULL ELSE (SELECT b1.fldName FROM invBrand b1 WHERE b1.pkyBrand = c.codBrand) END AS marca, 
	case when c.codBrandModel IS NULL then NULL ELSE (SELECT c1.fldName FROM invBrandModel c1 WHERE c1.pkyBrandModel = c.codBrandModel) END AS modelo, 
	e.fldName AS familia,
	case when c.codSubFamily IS NULL then NULL ELSE (SELECT d1.fldName FROM invItemCategory d1 WHERE d1.pkyItemCategory = c.codSubFamily) END AS subfamilia,
	case when c.codGroup IS NULL then NULL ELSE (SELECT e1.fldName FROM invItemCategory e1 WHERE e1.pkyItemCategory = c.codGroup) END AS grupo,
	case when c.codSubGroup IS NULL then NULL ELSE (SELECT f1.fldName FROM invItemCategory f1 WHERE f1.pkyItemCategory = c.codSubGroup) END AS subgrupo
	FROM
	vhcMaintenanceOrder a, agnPerson b, invItem c, vhcVehicleDetail d, invItemCategory e, accGroup h
	where 
	$valor_sucursal 
	$valor_responsable 
	$valor_marca 
	$valor_modelo 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_vehiculo 
	$valor_contexto  
	a.fldDate >= '$fechaini' and
	a.fldDate <= '$fechafin' and 
	a.codResponsible = b.pkyPerson and 
	a.codVehicle = c.pkyItem and 
	a.codVehicle = d.codItem and 
	c.codFamily = e.pkyItemCategory and 
	a.sysAuthorizationGroup = h.pkyGroup and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	$valor_orden;");
		

	$mat_mant = array();
	$i = 1;
	while ($row = mysqli_fetch_array($result)) {
		$mat_mant [$i][1] = $row['cod_mant'];
		$mat_mant [$i][2] = $row['nro_doc'];
		$mat_mant [$i][3] = $row['fecha'];
		$mat_mant [$i][4] = $row['hora'];
		$mat_mant [$i][5] = $row['cod_resp'];
		$mat_mant [$i][6] = $row['responsable'];
		$mat_mant [$i][7] = $row['cod_vehi'];
		$mat_mant [$i][8] = $row['vehiculo'];
		$mat_mant [$i][9] = $row['placa'];
		$mat_mant [$i][10] = $row['gasolina'];
		$mat_mant [$i][11] = $row['km'];
		$mat_mant [$i][12] = $row['cod_sucursal'];
		$mat_mant [$i][13] = $row['sucursal'];
		$mat_mant [$i][14] = $row['glosa'];
		$mat_mant [$i][15] = $row['usuario_reg'];
		$mat_mant [$i][16] = $row['marca'];
		$mat_mant [$i][17] = $row['modelo'];
		$mat_mant [$i][18] = $row['familia'];
		$mat_mant [$i][19] = $row['subfamilia'];
		$mat_mant [$i][20] = $row['grupo'];
		$mat_mant [$i][21] = $row['subgrupo'];
		
		$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>Mantenimientos</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>Sucursal: </b><?php echo $nom_sucursal; ?></TD>
			<TD width="25%"><b>Responsable: </b><?php echo $nom_responsable; ?></TD>
			<TD width="25%"><b>Ordenado por: </b><?php echo $nom_orden; ?></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>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
			<TD><b>Marca: </b><?php echo $nom_marca; ?></TD>
			<TD><b>Modelo: </b><?php echo $nom_modelo; ?></TD>
			<TD><b>Contexto: </b><?php echo $nom_contexto; ?></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>Hora</b></TD>
    <TD align="center"><b>Sucursal</b></TD>
	<TD align="center"><b>Responsable</b></TD>
	<TD align="center"><b>Marca</b></TD>
	<TD align="center"><b>Modelo</b></TD>
	<?php if($familia_ver == 1) { $nro_col ++;?>
    <TD align="center"><b>Familia</b></TD>
    <?php } ?>
	<?php if($subfamilia_ver == 1) { $nro_col ++;?>
    <TD align="center"><b>Sub Familia</b></TD>
    <?php } ?>
	<?php if($grupo_ver == 1) { $nro_col ++;?>
    <TD align="center"><b>Grupo</b></TD>
    <?php } ?>
	<?php if($subgrupo_ver == 1) { $nro_col ++;?>
    <TD align="center"><b>Sub Grupo</b></TD>
    <?php } ?>
    <TD align="center"><b>Veh&iacute;culo</b></TD>
	<TD align="center"><b>Placa</b></TD>
    <TD align="center"><b>Gasolina</b></TD>
    <TD align="center"><b>Km.</b></TD>
    <TD align="center"><b>Datos Extra</b></TD>
    <TD align="center"><b>Usuario</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

	<?php
	$color = 2;
	
	$j = 1;

	while ($j <= $fin) { //while
		$cod_manto = $mat_mant [$j][1];
		if($color % 2 == 0) { //if color
			printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../VoucherMaintenanceOrder/index.php?pkyMaintenanceOrder=$cod_manto&user=$usuario&token=$token' title='Orden de Mantenimiento'>%s</a></td>", $cod_manto);//cod
		} else {
			printf("<tr><td align='center'><a href='../VoucherMaintenanceOrder/index.php?pkyMaintenanceOrder=$cod_manto&user=$usuario&token=$token' title='Orden de Mantenimiento'>%s</a></td>", $cod_manto);//cod
		}//fin if color
		printf("<td align='center'>%s</td>", $mat_mant [$j][2]);//nro doc
		printf("<td align='center'>%s</td>", $mat_mant [$j][3]);//fecha orden mantenimiento
		printf("<td align='center'>%s</td>", $mat_mant [$j][4]);//hora orden mantenimiento
		printf("<td align='left'>%s</td>", $mat_mant [$j][13]);//sucursal
		printf("<td align='left'>%s</td>", $mat_mant [$j][6]);//responsable
		printf("<td align='left'>%s</td>", $mat_mant [$j][16]);//marca
		printf("<td align='left'>%s</td>", $mat_mant [$j][17]);//modelo
		if($familia_ver == 1) {
			printf("<td align='left'>%s</td>", $mat_mant [$j][18]);//familia
		}
		if($subfamilia_ver == 1) {
			printf("<td align='left'>%s</td>", $mat_mant [$j][19]);//sub familia
		}
		if($grupo_ver == 1) {
			printf("<td align='left'>%s</td>", $mat_mant [$j][20]);//grupo
		}
		if($subgrupo_ver == 1) {
			printf("<td align='left'>%s</td>", $mat_mant [$j][21]);//sub grupo
		}
		printf("<td align='left'>%s</td>", $mat_mant [$j][8]);//vehiculo
		printf("<td align='center'>%s</td>", $mat_mant [$j][9]);//placa
		printf("<td align='right'>%s</td>", $mat_mant [$j][10]);//gasolina
		printf("<td align='right'>%s</td>", $mat_mant [$j][11]);//km
		printf("<td align='left'>%s</td>", $mat_mant [$j][14]);//datos extra
		printf("<td align='left'>%s</td></tr>", $mat_mant [$j][15]);//usuario
		
		$color++;
		$j++;

	}//fin while
		
	?>

	<tr BGCOLOR='#ffcccc'><td colspan="<?php echo $nro_col; ?>"><b>Total Orden(es) de Mantenimiento</b>
	</td><td align="right"><?php printf("<b>%s</b>", number_format($j - 1,0)); ?>
	</td></tr>
	</tbody>
	</table>
	<?php

} //fin if 2

?>

</body>
</html>
