<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.6, 2011-02-27
 */

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

set_time_limit(0);
ini_set("memory_limit","300M");

/** PHPExcel */
require_once 'classes/PHPExcel.php';

$fechaDesde = $_GET["fechaDesde"];	
$fechaHasta = $_GET["fechaHasta"];
$hospedaje = $_GET["hospedaje"];
$hotel = $_GET["hotel"];
$fechaDesde2 = $_GET["fechaDesde2"];
$fechaHasta2 = $_GET["fechaHasta2"];
$usuario = $_GET["usuario"];

$fechaHoy = date("d/m/Y");



//fechaDesde
	$fecha = substr($fechaDesde, 0, 10); 
	$fecha1 = explode("-",$fecha);
	$fecha2 = $fecha1[2]."/".$fecha1[1]."/".$fecha1[0];
	$fechaDesde22 = $fecha2;
	
	//fechaHasta
	$fecha = substr($fechaHasta, 0, 10); 
	$fecha1 = explode("-",$fecha);
	$fecha2 = $fecha1[2]."/".$fecha1[1]."/".$fecha1[0];
	$fechaHasta22 = $fecha2;

	    $cadpasajeros = "";
		$cadAdultos = "";
		$cadNinos = "";
		$cadInfantes = "";

$hostname_strConexion="localhost";
$database_strConexion="dysevent_crmdysmultidestinos";
$username_strConexion="dysevent_crmdysmultidestinos";
$password_strConexion="D]muC?u8]4@J60B[";
$strConexion=mysql_pconnect($hostname_strConexion,$username_strConexion,$password_strConexion) or
trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_strConexion, $strConexion);

$tira = "";
if($hotel !="Todos"){
	$tira.$tira = " AND rc.p08_hoteles_id_c = '".$hotel."' ";
	}
if($hospedaje !="Todos"){
	$tira.$tira = " AND rc.tipo_plan_c = '".$hospedaje."' ";
	}	
if($fechaDesde2 !="" and $fechaHasta2 != ''){
	$tira.$tira = "  AND (DATE(rc.fecha_limite_hotel_c) BETWEEN '".$fechaDesde2."' AND '".$fechaHasta2."') ";
	}
	
if($fechaDesde !="" and $fechaHasta != ''){
	$tira.$tira = "  AND (DATE(rc.check_in_c) BETWEEN '".$fechaDesde."' AND '".$fechaHasta."') ";
	}	

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Tcontacta")
							 ->setLastModifiedBy("Tcontacta")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");

$styleArray = array(
    'font' => array(
        'bold' => true,
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
    ),
    'borders' => array(
        'botton' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        ),
        'top' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        ),
    ),
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startcolor' => array(
            'argb' => 'FFA0A0A0',
        ),
        'endcolor' => array(
            'argb' => '7aa3da',
        ),
    ),
);

//Celdas
$objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('K4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getFill()->getStartColor()->setRGB('FFFFFF');

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:D3');
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
      ->setCellValue('A1', utf8_encode('DYS Multidestinos C.A Reservaciones'))
	  ->setCellValue('A2', utf8_encode('Check IN: '.$fechaDesde22.' Check OUT: '.$fechaHasta22.' Emisión Reporte: '.$fechaHoy))
	  ->setCellValue('A3', utf8_encode('Usuario: '.$usuario))
	  ->setCellValue('A4', utf8_encode('Cédula'))
	  ->setCellValue('B4', utf8_encode('Nombre Cliente'))
      ->setCellValue('C4', utf8_encode('Check In'))
	  ->setCellValue('D4', utf8_encode('Check Out'))
	  ->setCellValue('E4', utf8_encode('Hotel'))
      ->setCellValue('F4', utf8_encode('Sistema de Hospedaje'))
      ->setCellValue('G4', utf8_encode('Nro de Pasajeros'))
      //->setCellValue('H4', utf8_encode('Pago Cliente'))
	  ->setCellValue('H4', utf8_encode('Venta Asociada'))
      ->setCellValue('I4', utf8_encode('Pago Adicional Cliente'))
      ->setCellValue('J4', utf8_encode('Pago Proveedor'))
      ->setCellValue('K4', utf8_encode('Fecha Límite de Pago'));
      

$sql = "SELECT a.name AS cedula, rc.nombre_cliente_c AS cliente, DATE_FORMAT(rc.check_in_c, '%d/%m/%Y')  AS checkIn, 
DATE_FORMAT(rc.check_out_c, '%d/%m/%Y')  AS checkOut, h.name AS hotel, rc.tipo_plan_c AS tipoPlan,
rc.nro_adultos_c AS adultos, rc.nro_ninos_c AS ninos, rc.nro_infantes_c AS infantes, v.name AS venta,
rc.pago_adicional_c AS pagoAdicional, rc.monto_hotel_c AS pagoProveedor, DATE_FORMAT(rc.fecha_limite_hotel_c, '%d/%m/%Y') AS fechaLimite 
FROM p07_reservacion AS r
LEFT JOIN p07_reservacion_cstm AS rc ON (rc.id_c = r.id)
LEFT JOIN accounts_p07_reservacion_1_c AS ar ON (ar.accounts_p07_reservacion_1p07_reservacion_idb = r.id)
LEFT JOIN accounts_cstm AS ac ON (ac.id_c = ar.accounts_p07_reservacion_1accounts_ida)
LEFT JOIN accounts AS a ON (a.id = ac.id_c)
LEFT JOIN p08_hoteles AS h ON (h.id = rc.p08_hoteles_id_c)
LEFT JOIN p02_ventas AS v ON (v.id = rc.p02_ventas_id_c)
WHERE !ar.deleted AND !r.deleted AND !a.deleted  ".$tira."
ORDER BY a.name ASC;";

//echo $sql;
$rst=mysql_query($sql,$strConexion); //Ejecutamos la SQL
if(!$rst) //Comprobamos si hay errores
 	die("Error MySQL de Extracción de Datos...".mysql_error());
$i=5;      
$totalRegistros= 0;      
while($fila = mysql_fetch_array($rst))
{
	      if ($fila["adultos"] != '0') $cadAdultos = $fila["adultos"]." Pax;";
		  if ($fila["ninos"]   != '0') { $cadNinos   = $fila["ninos"].' Chd;';}
		  if ($fila["infantes"]!= '0') { $cadInfantes= $fila["infantes"].' Inf;';}
		 $cadpasajeros = $cadAdultos.$cadNinos.$cadInfantes;
		 
		 $pagoAdicional = str_replace(",",".",$fila["pagoAdicional"]);
	     $pagoProveedor   = str_replace(",",".",$fila["pagoProveedor"]);

		 
		 
	$totalRegistros++;
	$objPHPExcel->setActiveSheetIndex(0) 
		
			  
		  

		->setCellValue("A".$i, utf8_encode($fila["cedula"]))
		->setCellValue("B".$i, utf8_encode($fila["cliente"]))
		->setCellValue("C".$i, utf8_encode($fila["checkIn"]))
		->setCellValue("D".$i, utf8_encode($fila["checkOut"]))
		->setCellValue("E".$i, utf8_encode($fila["hotel"]))
		->setCellValue("F".$i, utf8_encode($fila["tipoPlan"]))
		->setCellValue("G".$i, utf8_encode($cadpasajeros))
		->setCellValue("H".$i, utf8_encode($fila["venta"]))
		->setCellValue("I".$i, utf8_encode(number_format($pagoAdicional,2,",",".") ))
		->setCellValue("J".$i, utf8_encode(number_format($pagoProveedor,2,",",".") ))
		->setCellValue("K".$i, utf8_encode($fila["fechaLimite"]));
$i++;
}
$i--;

if ($totalRegistros > 0){
$objPHPExcel->getActiveSheet()->getStyle("A".$i.":"."K".$i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A5:A'.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('J5:K'.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('ListadoReservaciones');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$nombreArchivo="ListadoReservaciones.xls";
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$nombreArchivo.'"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
else
{
	echo "No se encontraron registros con las Fechas de busqueda proporcionadas"."<br/>";
}
?>