<?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"];
//$depart = $_GET["depart"];
$estatus = $_GET["estatus"];
$usuario = $_GET["usuario"];
$fechaHoy = date("d/m/Y");

		

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


$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($depart !="Todos"){
	$tira = "AND vc.departamento_c = '".$depart."' ";
	}*/
if($estatus !="Todos"){
	$tira = "AND  pc.estatus_pago_c = '".$estatus."' ";
	}	

// 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:N4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A4:N4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4:N4')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('N4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A4:N4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A4:N4')->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()->getColumnDimension('N')->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 Cargos Automáticos'))
	  ->setCellValue('A2', utf8_encode('Desde '.$fechaDesde2.' Hasta '.$fechaHasta2.' Emisión Reporte: '.$fechaHoy))
	  ->setCellValue('A3', utf8_encode('Usuario: '.$usuario))
	  ->setCellValue('A4', utf8_encode('Fecha Transacción'))
	  ->setCellValue('B4', utf8_encode('Estatus'))
      ->setCellValue('C4', utf8_encode('Producto'))
      ->setCellValue('D4', utf8_encode('Tarjetahabiente'))
	  ->setCellValue('E4', utf8_encode('Cédula Tarjetahabiente'))
      ->setCellValue('F4', utf8_encode('Banco'))
      ->setCellValue('G4', utf8_encode('Tipo Tarjeta'))
	  ->setCellValue('H4', utf8_encode('Nro Tarjeta'))
	  ->setCellValue('I4', utf8_encode('Vencimiento'))
      ->setCellValue('J4', utf8_encode('Código de Segurtidad'))
      ->setCellValue('K4', utf8_encode('Código Alfa'))
      ->setCellValue('L4', utf8_encode('Monto'))
	  ->setCellValue('M4', utf8_encode('Punto Bancario'))
      ->setCellValue('N4', utf8_encode('Venta'));

$sql = "SELECT pc.fecha_transaccion_tarjeta_c AS fecha, pc.producto_c AS producto,
pc.nombre_tarjetahabiente_c AS cliente, pc.cedula_tarjetahabiente_c AS cedula,
FROM_BASE64(p.nro_tarjeta) AS tarjeta, FROM_BASE64(p.codigo_validador) AS codigovalidador, pc.alfa_c AS alfa,
p.banco_transaccion AS banco, p.tipo_tarjeta AS tipoTarjeta,
p.mes_vencimiento AS mes, p.anio_vencimiento AS anno,
p.monto_transaccion AS monto, p.punto_bancario AS punto,
v.name AS venta, pc.estatus_pago_c AS estatus,
vc.nro_factura_c AS nroFactura, p.nro_aprobacion AS nroAprobacion,
e.name AS empleado

FROM p05_pagos AS p
LEFT JOIN p05_pagos_cstm AS pc ON (pc.id_c = p.id)
LEFT JOIN p02_ventas_p05_pagos_1_c AS vp ON (vp.p02_ventas_p05_pagos_1p05_pagos_idb = p.id)
LEFT JOIN p02_ventas AS v ON (vp.p02_ventas_p05_pagos_1p02_ventas_ida = v.id)
LEFT JOIN p02_ventas_cstm AS vc ON (vc.id_c = v.id)
LEFT JOIN p10_empleados AS e ON (e.id = vc.p10_empleados_id_c)
WHERE !v.deleted AND !p.deleted
 AND (DATE(pc.fecha_transaccion_tarjeta_c) BETWEEN '".$fechaDesde."' AND '".$fechaHasta."') ".$tira."
ORDER BY pc.fecha_transaccion_tarjeta_c ASC;";


$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))
{
	$totalRegistros++;
	$vencimiento = $fila["mes"].'/'.$fila["anno"];
	
	$fecha = substr($fila["fecha"], 0, 10); 
	$fecha1 = explode("-",$fecha);
	$fecha2 = $fecha1[2]."/".$fecha1[1]."/".$fecha1[0];
	$fechaTransac = $fecha2;
	
	
	$objPHPExcel->setActiveSheetIndex(0)            
		->setCellValue("A".$i, utf8_encode($fechaTransac))
		->setCellValue("B".$i, utf8_encode($fila["estatus"]))
		->setCellValue("C".$i, utf8_encode($fila["producto"]))
		->setCellValue("D".$i, utf8_encode($fila["cliente"]))
		->setCellValue("E".$i, utf8_encode($fila["cedula"]))
		->setCellValue("F".$i, utf8_encode($fila["banco"]))
		->setCellValue("G".$i, utf8_encode($fila["tipoTarjeta"]))
		->setCellValue("H".$i, $fila["tarjeta"])
		->setCellValue("I".$i, utf8_encode($vencimiento))
		->setCellValue("J".$i, utf8_encode($fila["codigovalidador"]))
		->setCellValue("K".$i, utf8_encode($fila["alfa"]))
		->setCellValue("L".$i, utf8_encode($fila["monto"]))
		->setCellValue("M".$i, utf8_encode($fila["punto"]))
		->setCellValue("N".$i, utf8_encode($fila["venta"]));
						            
$i++;
}
$i--;

if ($totalRegistros > 0){
$objPHPExcel->getActiveSheet()->getStyle("A".$i.":"."N".$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:N'.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

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

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$nombreArchivo="ListadoCargosAutomaticoTDC.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/>";
}
?>