<?php
	session_start();
	include('conexion.php');
	
	$_SESSION['tipo'] = 6;

	$producto = $_GET['id_producto'];
	$idtipo = $_GET['idtipo'];
	$departamento = $_GET['departamento'];
	$vehiculo = $_GET['id_vehiculo'];
	$limit = $_GET['limit'];
	$fecha1 = $_GET['fecha1'];
	$fecha_inicio1 = $_GET['fecha_inicio'];
	$fecha_fin1 = $_GET['fecha_fin'];
	$fecha2 = $_GET['fecha2'];
	$fecha_inicio2 = $_GET['fecha_inicio2'];
	$fecha_fin2 = $_GET['fecha_fin2'];
	$facturado = $_GET['facturado'];
	$no_facturado = $_GET['no_facturado'];
	$serie = $_GET['serie'];
	$folio = $_GET['folio'];
	$adeudo = $_GET['adeudo'];
	$id_serie = $_GET['id_serie'];
	$id_folio = $_GET['id_folio'];
	$id_adeudo = $_GET['id_adeudo'];
	$t_activa = $_GET['t_activa'];
	$t_cancelada = $_GET['t_cancelada'];
	$ordenamiento = $_GET['ordenamiento'];
	$total_transacciones =0;
	$monto_muestra=0;
	$volumen_muestra=0;

/*	$consulta =  validaPermiso('catalogo_cargos','catalogo_cargos_consultar',$_SESSION['userid']);
	$eliminacion = validaPermiso('catalogo_cargos','catalogo_cargos_eliminar',$_SESSION['userid']);
	$adeudoPermiso = validaPermiso('catalogo_cargos','catalogo_cargos_adeudo',$_SESSION['userid']);	
	$permiso_odometro = validaPermiso('catalogo_cargos','catalogo_cargos_editar_odometro',$_SESSION['userid']);*/

	$filtros="";

	$sql = "Select ";
	$sql .= "t.id_transaccion, ";
	$sql .= "t.id_turno, ";
	$sql .= "ifnull(t.monto,0) as monto, ";
	$sql .= "ifnull(t.saldo,0) as saldo, ";
	$sql .= "date_format(t.fecha,'%Y/%m/%d %H:%m:%s') as fecha, ";
	$sql .= "t.id_vehiculo, ";
	$sql .= "ifnull(t.odometro,0) as odometro, ";
	$sql .= "ifnull(t.odometro_diferencia,0) as odometro_diferencia, ";
	$sql .= "if(t.factura=0,'No','Si') as factura, ";
	$sql .= "t.serie, ";
	$sql .= "t.folio, ";
	$sql .= "t.id_factura, ";
	$sql .= "ifnull(tac.id_adeudo,0) as id_adeudo, ";
	$sql .= "ifnull(t.aux_1,'') as aux_1, ";
	$sql .= "td.volumen, ";
	$sql .= "p.nombre, ";
	$sql .= "t.id_turno, ";
	$sql .= "td.id_producto, ";
	$sql .= "v.departamento, ";
	$sql .= "v.numero_economico, ";
	$sql .= "v.placa, ";
	$sql .= "b.id_estacion, ";
	$sql .= "b.razon_social";
	$sql .= "from transacciones_detalle td, productos p, estacion e,transacciones t  ";
	$sql .= "left join vehiculos v on(v.id_vehiculo=t.id_vehiculo) ";
	$sql .= "left join (select ta.id_adeudo, ta.id_transaccion from transacciones_adeudos_cobro ta, adeudos_cobro ac ";
	$sql .= "where ta.id_adeudo= ac.id_adeudo and ac.estatus!=3 and ac.tipo_entidad=0) tac on (t.id_transaccion = tac.id_transaccion) ";	
	$sql .= "where ifnull(estatus,1) !=0 and id_tipo = 1 ";

	if ($facturado == "1" && $no_facturado=="0")
	{
		$sql .= " and t.factura =1 ";
	}
	if ($facturado == "0" && $no_facturado == "1")
	{
		$sql .= " and t.factura =0 ";
	}
	
	if($fecha2 == 1)
	{
		$sql .= "and fecha >='" . $fecha_inicio2 . "' and fecha <='".$fecha_fin2."' ";
	}
	
	if ($_SESSION['id_cliente'] != "")
	{
		$sql .= " and t.id_cliente = " . $_SESSION['id_cliente'] ;
	}
	
	if ($vehiculo !="" )
	{
		$sql .= " and t.id_vehiculo = " . $vehiculo;
	}
	
	if($fecha1=="1")
	{
		$fecha_inicio1 =str_replace ( "-", '', $fecha_inicio1);
		$turnoini = $fecha_inicio1."00";

		$fecha_fin1 =str_replace ( "-", '', $fecha_fin1);
		$turnofin = $fecha_fin1."99";

		if ($turnoini !="")
		{
			$sql .= " and t.id_turno >= " . $turnoini;
		}
		if ($turnofin != "")
		{
			$sql .= " and t.id_turno <= " . $turnofin;
		}
	}
	if ($adeudo == "1" && $id_adeudo != "")
	{
		$sql .= " and tac.id_adeudo =" . $id_adeudo ." ";
	}

	if ($serie == true && $id_serie != "")
	{
		$sql .= " and t.serie ='" . $id_serie . "' ";
	}

	if ($folio == true && $id_folio != "")
	{
		$sql .= " and t.folio =" . $id_folio . " ";
	}
	
	$sql .= " and td.id_producto= p.id_producto and t.id_transaccion = td.id_transaccion ";
	if($producto != 0){
	     $sql .= "and p.id_producto = " . $producto." ";		
	}							


	
	//rmn para que solo muestre 1 detalle
	$sql .= " and td.id_detalle=1 ";
	
	//fin filtro productos
	/*if ($orid=="1")
		$sql .= " order by t.id_transaccion ";
	else
		$sql .= " order by t.id_turno,t.id_transaccion ";*/
	
	if ($limit=="1")
	{
		$sql .= "limit 1000 ";
	}



			$sqlc = "select ";
            $sqlc .= "'" . $_SESSION['id_cliente']." ".$_SESSION['razon_social']."' as cliente, ";
            $sqlc .= "t.id_transaccion, ";
            $sqlc .= "t.id_turno, ";
            $sqlc .= "t.monto, ";
            $sqlc .= "t.saldo, ";
            $sqlc .= "t.fecha, ";
            $sqlc .= "t.id_vehiculo, ";
            $sqlc .= "t.placa, ";
            $sqlc .= "t.odometro, ";
            $sqlc .= "t.odometro_diferencia, ";
            $sqlc .= "b.id_estacion, ";
            $sqlc .= "b.razon_social ";
           	$sqlc .= "from estacion b, transacciones t left join ";
            $sqlc .= "(select ta.id_adeudo, ta.id_transaccion from transacciones_adeudos_cobro ta, adeudos_cobro ac ";
            $sqlc .= "where ta.id_adeudo= ac.id_adeudo and ac.estatus!=3) tac on (t.id_transaccion = tac.id_transaccion) ";
            
            //$sqlc .= "from transacciones t left join transacciones_adeudos_cobro ta on(t.id_transaccion= ta.id_transaccion) ";
            $sqlc .= "where ifnull(estatus,1) !=0 and id_tipo = 1 ";

        	if ($facturado == "1" && $no_facturado=="0")
			{
				$sqlc .= " and t.factura =1 ";
			}
			if ($facturado == "0" && $no_facturado == "1")
			{
				$sqlc .= " and t.factura =0 ";
			}
			
			if($fecha2 == 1)
			{
				$sqlc .= "and fecha >='" . $fecha_inicio2 . "' and fecha <='".$fecha_fin2."' ";
			}
			
			if ($_SESSION['id_cliente'] != "")
			{
				$sqlc .= " and t.id_cliente = " . $_SESSION['id_cliente'] ;
			}
			
			if ($vehiculo !="" )
			{
				$sqlc .= " and t.id_vehiculo = " . $vehiculo;
			}
			
			if($fecha1=="1")
			{
				$fecha_inicio1 =str_replace ( "-", '', $fecha_inicio1);
				$turnoini = $fecha_inicio1."00";

				$fecha_fin1 =str_replace ( "-", '', $fecha_fin1);
				$turnofin = $fecha_fin1."99";

				if ($turnoini !="")
				{
					$sqlc .= " and t.id_turno >= " . $turnoini;
				}
				if ($turnofin != "")
				{
					$sqlc .= " and t.id_turno <= " . $turnofin;
				}
			}
			if ($adeudo == "1" && $id_adeudo != "")
			{
				$sqlc .= " and tac.id_adeudo =" . $id_adeudo ." ";
			}

			if ($serie == true && $id_serie != "")
			{
				$sqlc .= " and t.serie ='" . $id_serie . "' ";
			}

			if ($folio == true && $id_folio != "")
			{
				$sqlc .= " and t.folio =" . $id_folio . " ";
			}
	
          
				$sqlc .= " order by t.id_transaccion ";
			
            

            if ($limit=="1")
			{
				$sqlc .= "limit 1000 ";
			}
	if($idtipo=="1")
	{
		
		require('./fpdf/fpdf.php');

	//die("Error: no se pudo realizar la consulta===".$sqlc);
	$resultadoE = $conexion->query($sqlc); 
	$estacion = $resultadoE->fetch_assoc();
	
	//$_SESSION['estacion']=$estacion['id_estacion'];
	//$_SESSION['razon_s']=$estacion['razon_social'];	
	$_SESSION['fech']=$chfecha;	
	$_SESSION['fech1']=substr($fecha, 0,2)."-".substr($fecha,3 ,2)."-".substr($fecha, 6,4);	
	$_SESSION['fech2']=substr($fecha, 11,2)."-".substr($fecha,14 ,2)."-".substr($fecha, 17,4);	
	$_SESSION['chturno']=$checkturno;
	$_SESSION['turno1']=$turnoini;	
	$_SESSION['turno2']=$turnofin;	
	$_SESSION['perifericos']=$perifericos;	
	$produc="";
	
	$sqlpr= "SELECT nombre from productos where id_producto  <11";
	$resultadopr= $conexion->query($sqlpr);
	while($prodr=$resultadopr->fetch_assoc())
	{
		$produc.=$prodr['nombre'].", ";
	}
	
	
		$_SESSION['productos']=$produc;
	class PDF extends FPDF
	{
		function Header()
		{
			$this->SetFont('Arial','B',14);
			$this->Cell(212,10,'REPORTE DE TRANSACCIONES');
			$this->SetFont('Arial','',10);
			date_default_timezone_set('America/Mexico_City');
			$time = time();	
			$this->Cell(50,10,'Fecha: ' . date("Y/m/d  H:i:s", $time) );
			$this->Ln();
			//$this->Cell(25,5,$_SESSION['estacion']." ".$_SESSION['razon_s']);
			$this->Ln();
			$this->Cell(25,5,"Transacciones");
			$this->Ln();	
			if($_SESSION['fech']=="1")
			{
				$this->Cell(25,5,"Fecha Inicio  ");
				$this->Cell(25,5,$_SESSION['fech1']);
				$this->Ln();	
				$this->Cell(25,5,"Fecha Fin  ");
				$this->Cell(25,5,$_SESSION['fech2']);
				$this->Ln();	
			}
			if($_SESSION['chturno']=="1")
			{
				$this->Cell(25,5,"Turno Inicio");
				$this->Cell(25,5,$_SESSION['turno1']);
				$this->Ln();	
				$this->Cell(25,5,"Turno Fin");
				$this->Cell(25,5,$_SESSION['turno2']);
				$this->Ln();	
			}
			if($_SESSION['perifericos']=="1" || $_SESSION['productos']!="")
			{
				$this->Cell(25,5,"Productos");
				$this->Ln();
				if($_SESSION['perifericos']=="1")
				{
					$this->Cell(19,5,"Perifericos,");
					
				}
				if($_SESSION['productos']!="")
				{

					
					

					$this->Cell(25,5,substr($_SESSION['productos'],0,-2));
					$this->Ln();
					
				}
			}
			$this->Ln();		
			$this->Cell(50,5,$_SESSION['id_cliente']." ".$_SESSION['razon_social']);
			$this->Ln();		
			$this->Cell(15,5,"Id Vehi",'B',0,'C');
			
			$this->Cell(26,5,"Departamento",'B',0,'C');
			$this->Cell(21,5,"N Eco",'B',0,'C');
			$this->Cell(15,5,"Placa",'B',0,'C');
			$this->Cell(23,5,"Transaccion",'B',0,'C');
			$this->Cell(30,5,"Fecha Registro",'B',0,'C');
			$this->Cell(23,5,"Producto",'B',0,'C');
			$this->Cell(21,5,"Volumen",'B',0,'C');
			$this->Cell(23,5,"Monto",'B',0,'C');
			$this->Cell(16,5,"Odometro",'B',0,'C');
			$this->Cell(19,5,"Diferencia",'B',0,'C');
			$this->Cell(10,5,"Serie",'B',0,'C');
			$this->Cell(10,5,"Folio",'B',0,'C');
			
			
				
		}
	}
	$id_vehiculo="";
	$cont=0;
	$totalmonto=0;
	$montototal=0;
	$totalvol=0;
	$montovol=0;
		
	$pdf=new PDF('L', 'mm', 'Letter');
	$pdf->SetAutoPageBreak(true,25); 
	$pdf->AliasNbPages();
	$pdf->SetTitle('Transacciones');
	$pdf->AddPage();
	$pdf->SetFont('Arial','',10);

	$resultado = $conexion->query($sql); 
	while($fila = $resultado->fetch_assoc())
	{
		$cont++;
		if($id_vehiculo=="")
		{
			
		}
		else
		{
			
		
			if($id_vehiculo!=$fila['id_vehiculo'])
			{
				$pdf->Cell(15,5,"");
				$pdf->Cell(20,5,"");
				$pdf->Cell(20,5,"");
				$pdf->Cell(36,5,"");
				$pdf->SetFont('Arial','B',9);
				$pdf->Cell(56,5,"Total Vehiculo ",0,0,'R');
				$pdf->Cell(23,5,number_format($totalvol,2,'.',','),0,0,'R');
				$pdf->Cell(24,5,number_format($totalmonto,2,'.',','),0,0,'R');
				$pdf->Cell(15,5,"");
				$pdf->Cell(35,5,"");
				$pdf->Cell(25,5,"");
				$pdf->Cell(25,5,"");
				$pdf->Ln();	
				$totalmonto=0;
				$totalvol=0;
			}
		}
		
	$sqlp= "SELECT nombre from productos where id_producto=".$fila['id_producto'];
	$resultadop= $conexion->query($sqlp);
	$prod=$resultadop->fetch_assoc();
		$id_vehiculo=$fila['id_vehiculo'];
		
		$pdf->SetFont('Arial','',9);
		$pdf->Cell(16,5,$fila['id_vehiculo'],0,0,'C');
		$pdf->Cell(22,5,$fila['departamento'],0,0,'L');
		$pdf->Cell(20,5,$fila['numero_economico'],0,0,'R');
		$pdf->Cell(23,5,$fila['placa'],0,0,'C');
		$pdf->Cell(18,5,$fila['id_transaccion'],0,0,'R');
		$pdf->Cell(33,5,$fila['fecha'],0,0,'C');
		$pdf->Cell(18,5,$fila['nombre'],0,0,'C');
		$pdf->Cell(20,5,number_format($fila['volumen'],2,'.',','),0,0,'R');
		$pdf->Cell(24,5,number_format($fila['monto'],2,'.',','),0,0,'R');
		$pdf->Cell(20,5,$fila['odometro'],0,0,'R');
		$pdf->Cell(18,5,$fila['odometro_diferencia'],0,0,'R');
		$pdf->Cell(10,5,$fila['serie'],0,0,'L');
		$pdf->Cell(10,5,$fila['folio'],0,0,'R');
		$pdf->Ln();
		$totalmonto+=$fila['monto'];
		$totalvol+=$fila['volumen'];
		
		if($cuenta==$cont)
		{
			$pdf->SetFont('Arial','B',9);
			$pdf->Cell(15,5,"");
			$pdf->Cell(20,5,"");
			$pdf->Cell(20,5,"");
			$pdf->Cell(36,5,"");
			$pdf->Cell(56,5,"Total Vehiculo",0,0,'R');
			$pdf->Cell(23,5,number_format($totalvol,2,'.',','),0,0,'R');
			$pdf->Cell(24,5,number_format($totalmonto,2,'.',','),0,0,'R');
			$pdf->Cell(15,5,"");
			$pdf->Cell(35,5,"");
			$pdf->Cell(25,5,"");
			$pdf->Cell(25,5,"");
			$pdf->Ln();	
			$totalmonto=0;
			$totalvol=0;
		}
		$montototal+=$fila['monto'];
		$montovol+=$fila['volumen'];
			
	}
	$pdf->SetFont('Arial','B',9);
			$pdf->Cell(15,5,"");
			$pdf->Cell(20,5,"");
			$pdf->Cell(20,5,"");
			$pdf->Cell(36,5,"");
			$pdf->Cell(56,5,"Total",0,0,'R');
			$pdf->Cell(23,5,number_format($montovol,2,'.',','),0,0,'R');
			$pdf->Cell(24,5,number_format($montototal,2,'.',','),0,0,'R');
			$pdf->Cell(15,5,"");
			$pdf->Cell(35,5,"");
			$pdf->Cell(25,5,"");
			$pdf->Cell(25,5,"");
			$pdf->Ln();	
			$totalmonto=0;
			$totalvol=0;

$pdf->Output();
	}
if($idtipo=="2")
{
	header("Content-Type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename=ReporteTransaccionesCliente".$_SESSION['id_cliente'].".xls");
	
	$resultadoE = $conexion->query($sqlc); 
	$estacion = $resultadoE->fetch_assoc();
	//$cuenta=$estacion['cuenta'];										
			
		echo "<table>";
		echo "<tr>";
		echo "<td>".$estacion['id_estacion']." ".$estacion['razon_social']."</td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>Reporte Transacciones</td>";
		echo "</tr>";
	if($chfecha=="1")
		{
		echo "<tr>";
		echo "<td>Fecha Inicio  ".$fecha[0]."</td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>Fecha Fin  ".$fecha[1]."</td>";
		echo "</tr>";
		}
	if($checkturno=="1")
	{
		echo "<tr>";
		echo "<td>Turno Inicio  ".$turnoini."</td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>Turno Fin  ".$turnofin."</td>";
		echo "</tr>";
	}	
		echo "</table>";
	
	echo "<table  border='1'>";
		echo "<tr>";
		echo "<td></td>";
		echo "<td>Id Vehi</td>";
		echo "<td>Departamento</td>";
		echo "<td>N Eco</td>";
		echo "<td>Placa</td>";
		echo "<td>Transaccion</td>";
		echo "<td>Fecha Registro</td>";
		echo "<td>Producto</td>";
		echo "<td>Volumen</td>";
		echo "<td>Monto</td>";
		echo "<td>Odometro</td>";
		echo "<td>Diferencia</td>";
		echo "<td>Facturado</td>";
		echo "<td>Serie</td>";
		echo "<td>Folio</td>";
		echo "<td>Id Factura</td>";
		echo "<td>Id Adeudo</td>";
		echo "<td>Referencia</td>";
		echo "</tr>";
	
	$id_vehiculo="";
	$cont=0;
	$totalmonto=0;
	$totalvol=0;
	$montototal=0;
	$montovol=0;
	
	$resultado = $conexion->query($sql); 
	while($fila = $resultado->fetch_assoc())
	{
		$cont++;
		if($id_vehiculo=="")
		{
			
		}
		else
		{
			
		
			if($id_vehiculo!=$fila['id_vehiculo'])
			{
				echo "<tr>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td>Total Vehiculo</td>";
				echo "<td>".number_format($totalvol,2,'.',',')."</td>";
				echo "<td>".number_format($totalmonto,2,'.',',')."</td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "<td></td>";
				echo "</tr>";
				$totalmonto=0;
				$totalvol=0;
			}
		}
		$id_vehiculo=$fila['id_vehiculo'];
		$sqlp= "SELECT nombre from productos where id_producto=".$fila['id_producto'];
	$resultadop= $conexion->query($sqlp);
	$prod=$resultadop->fetch_assoc();
		
		$sqlv="select departamento, numero_economico from vehiculos where departamento like '%".$departamento."%' and id_vehiculo=". $fila['id_vehiculo'];
			mysqli_query($conexion,'SET NAMES utf8');
			$resultadove = $conexion->query($sqlv); 
			$prov=$resultadove->fetch_assoc();					
			if(mysqli_num_rows($resultadove)==0)
			{
										
			}
			else
			{
				echo "<tr>";
		echo "<td></td>";
		echo "<td>$fila[id_vehiculo]</td>";
		echo "<td>$prov[departamento]</td>";
		echo "<td>$prov[numero_economico]</td>";
		echo "<td>$fila[placa]</td>";
		echo "<td>$fila[id_transaccion]</td>";
		echo "<td>$fila[fecha]</td>";
		echo "<td>$prod[nombre]</td>";
		echo "<td>".number_format($fila['volumen'],2,'.',',')."</td>";
		echo "<td>".number_format($fila['monto'],2,'.',',')."</td>";
		echo "<td>$fila[odometro]</td>";
		echo "<td>$fila[odometro_diferencia]</td>";
		echo "<td>$fila[factura]</td>";
		echo "<td>$fila[serie]</td>";
		echo "<td>$fila[folio]</td>";
		echo "<td>$fila[id_factura]</td>";
		echo "<td>$fila[id_adeudo]</td>";
		echo "<td>$fila[aux_1]</td>";
		echo "</tr>";
		$totalmonto+=$fila['monto'];
		$totalvol+=$fila['volumen'];
		if($cuenta==$cont)
		{
			echo "<tr>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td>Total Vehiculo</td>";
			echo "<td>".number_format($totalvol,2,'.',',')."</td>";
			echo "<td>".number_format($totalmonto,2,'.',',')."</td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "</tr>";
			$totalmonto=0;
			$totalvol=0;
		}
		$montototal+=$fila['monto'];
		$montovol+=$fila['volumen'];
			}
	}
	echo "<tr>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td>Total</td>";
			echo "<td>".number_format($montovol,2,'.',',')."</td>";
			echo "<td>".number_format($montototal,2,'.',',')."</td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "<td></td>";
			echo "</tr>";
	
		echo "</table>";
}
?>