<?php
    session_start();

	include('conexion.php');
	include 'mysql_Agregar_auditoria.php';
	ini_set('max_execution_time', '300');
	set_time_limit(300);

	if (PHP_SAPI == 'cli')
    {
        die('This report should only be run from a Web Browser');
    }

	require('./PHPExcel/Classes/PHPExcel.php');

    /////////////////////////////////////////////////////////////////////////////////////
    /////////////////////////////////////////////////////////////////////////////////////
    /////////////////////////////////////////////////////////////////////////////////////

  	$cliente = $_SESSION['id_cliente'];
	$id_vehiculo = $_GET['id_vehiculo'];
	$fecha_inicio = $_GET['fecha_inicio'];
	$fecha_fin = $_GET['fecha_fin'];
	$fini = date_create($fecha_inicio);
	$finiMuestra =  date_format($fini, 'Ymd');	
	$ffin = date_create($fecha_fin);
	$ffinMuestra =  date_format($ffin, 'Ymd');	

    $sql = "SELECT ";
	$sql .= "id_cliente, ";
	$sql .= "razon_social, ";
	$sql .= "nombre_comercial, ";
	$sql .= "saldo ";
	$sql .= "FROM ";
	$sql .= "clientes ";
	$sql .= "WHERE ";
	$sql .= "id_cliente = " . $cliente;
	
	$resultado = $conexion->query($sql); 
								
	if (!$resultado) 
	{
		die("Error: no se pudo realizar la consulta cliente");
	}
		
	while ($fila = $resultado->fetch_assoc()) 
	{
		$_SESSION['id_cliente'] = $fila['id_cliente'];
		$_SESSION['razon_cliente'] = $fila['razon_social'];
		$_SESSION['nombre_cliente'] = $fila['nombre_comercial'];
		$_SESSION['saldo_muestra'] = $fila['saldo'];
	}

	$resultado->free();

	number_format($_SESSION['saldo_muestra'], 2, '.', ',');
	$_SESSION['fech1'] = $fecha_inicio;	
	$_SESSION['fech2'] = $fecha_fin;


    /////////////////////////////////////////////////////////////////////////////////////

    date_default_timezone_set('America/Mazatlan');
	$time = time();

	// Creación de objeto Excel
	$objPHPExcel = new PHPExcel();

	$cel = 8;

    $objPHPExcel->getProperties()
	->setCreator("Gaslight")
	->setLastModifiedBy("Gaslight System")
	->setTitle("Reporte Consumos ")
	->setSubject("Reporte Consumos ")
	->setDescription("Reporte Consumos")
	->setKeywords("Excel Office 2007 openxml php")
	->setCategory("Consumos");
                
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:D1');
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H1:I1');

    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);

    $boldArray = array('font' => array('bold' => true,),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
	$bold2Array = array('font' => array('bold' => true,),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));

    $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->applyFromArray($boldArray);
	$objPHPExcel->getActiveSheet()->getStyle('A7:I7')->applyFromArray($bold2Array);

    $objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1', 'REPORTE CONSUMOS')
				->setCellValue('E1', 'Fecha: ' . date("Y/m/d  H:i:s", $time))
				->setCellValue('A2', 'Cliente: '.$_SESSION['id_cliente'].'-'.$_SESSION['razon_cliente'])
				->setCellValue('A3', 'Saldo: '.$_SESSION['saldo_muestra'])
				->setCellValue('A4', 'Fecha Inicio: '.$_SESSION['fech1'])
				->setCellValue('A5', 'Fecha Fin: '.$_SESSION['fech2'])
				->setCellValue('A7', 'Vehiculo')
				->setCellValue('B7', 'Descripcion')
				->setCellValue('C7', 'Consumos')
				->setCellValue('D7', 'Litros')
				->setCellValue('E7', 'Volumen');

    /////////////////////////////////////////////////////////////////////////////////////

    $sql = "select ";
	$sql .= "c.id_tag as tag, ";
	$sql .= "c.descripcion as descripcion, ";
	$sql .= "count(*) as consumos, ";
	$sql .= "round(sum(b.volumen),3) as volumen, ";
	$sql .= "round(sum(b.monto),2) as monto ";
	$sql .= "from ";
	$sql .= "transacciones a, ";
	$sql .= "transacciones_detalle b, ";
	$sql .= "vehiculos c ";
	$sql .= "where ";
	$sql .= "a.id_transaccion = b.id_transaccion and ";
	$sql .= "a.id_vehiculo = c.id_vehiculo and ";
	$sql .= "a.id_cliente = " . $_SESSION['id_cliente'] . " and ";
	$sql .= "a.id_turno >= '" . $finiMuestra . "00" . "' and a.id_turno <= '" . $ffinMuestra . "06" . "' ";
	
	if ($id_vehiculo != 0)
	{
		$sql .= "and c.id_vehiculo = " . $id_vehiculo . " ";
	}
	
	$sql .= "group by c.id_tag, c.descripcion ";
	$sql .= "order by c.id_tag ";
	
	$resultado = $conexion->query($sql); 

	$numero_filas = $resultado -> num_rows;
	$num_consumos = 0;	
	
	if (!$resultado) 
	{
		die("Error: no se pudo realizar la consulta".$sql);
	}

	while ($fila = $resultado->fetch_assoc()) 
	{	
		$a = "A" . $cel;
		$b = "B" . $cel;
		$c = "C" . $cel;
		$d = "D" . $cel;
		$e = "E" . $cel;
		$f = "F" . $cel;
		$g = "G" . $cel;
		$h = "H" . $cel;
		$i = "I" . $cel;
		$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue($a, utf8_encode(" ".$fila["tag"]))
					->setCellValue($b, $fila["descripcion"])
					->setCellValue($c, $fila["consumos"])
					->setCellValue($d, $fila["volumen"])
					->setCellValue($e, $fila["monto"]);

		//$objPHPExcel->getActiveSheet()->getStyle("A".($cel))->getNumberFormat()->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);;

		$num_consumos = $num_consumos + $fila['consumos'];
		$total_volumen = $total_volumen + $fila['volumen'];
		$total_monto = $total_monto + $fila['monto'];

		$cel += 1;
	}

		$resultado->free();	
	
		$cel += 1;
		$a = "A" . $cel;
		$b = "B" . $cel;
		$c = "C" . $cel;
		$d = "D" . $cel;
		$e = "E" . $cel;
		$f = "F" . $cel;
		$g = "G" . $cel;

		$objPHPExcel->setActiveSheetIndex(0)	
					->setCellValue($a, 'Total vehiculos: '.$numero_filas)
					->setCellValue($b, 'Total: ')
					->setCellValue($c, $num_consumos)
					->setCellValue($d, $total_volumen)
					->setCellValue($e, $total_monto);	
		$cel += 1;

		
	$cel += 1;

	$a = "A" . ($cel + 4);

	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue($a, 'Gaslight System Flotillas Locales');

	$informacion = 'Genero Reporte Consumos formato EXCEL ';
	$datos = "Con filtros: ";
	if ($id_vehiculo != 0)
	{
		$datos=$datos." Vehiculo=".$id_vehiculo;
	}
	$datos=$datos." Del id_turno=". $finiMuestra . "00" . " al id_turno =" . $ffinMuestra . "06";	
	insertAuditoria($informacion,$datos,$_SESSION['id_usuario'],$_SESSION['direccion_ip'],$_SESSION['direccion_mac']);

    mysqli_close($conexion);

	$objPHPExcel->getActiveSheet()->setTitle('REPORTE');
	$objPHPExcel->setActiveSheetIndex(0);

	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

	ob_end_clean();
	
	header('Content-type: application/vnd.ms-excel');

	$nombre_archivo = "Reporte_Consumos_".date("Y_m_d_H_i_s", $time);
	$nombre_archivo = strtoupper($nombre_archivo);

	header('Content-Disposition: attachment; filename="'.$nombre_archivo.'.xlsx"');

	$objWriter->save('php://output');

	exit;
?>