<?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"];
$estado = $_GET["estado"];


$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($estado !="Todos"){
	$tira = "AND ac.dir_residencia_estado_c = '".$estado."' ";
	}
// 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('A1:H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('H1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->getStartColor()->setRGB('00519F');

$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);
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', utf8_encode('Código'))
      ->setCellValue('B1', utf8_encode('Cédula/RIF'))
      ->setCellValue('C1', utf8_encode('Nombres y Apellidos'))
      ->setCellValue('D1', utf8_encode('Teléfono Móvil 1'))
      ->setCellValue('E1', utf8_encode('Teléfono Móvil 2'))
	  ->setCellValue('F1', utf8_encode('Estado'))
      ->setCellValue('G1', utf8_encode('Clasificación'))
      ->setCellValue('H1', utf8_encode('Correo'));

$sql = "SELECT DISTINCT a.sic_code AS codigo, a.name AS Ced_Rif, ac.nombres_apellidos_c AS Nombres, ac.phone_movil_c AS movil1,
ac.phone_movil2_c AS movil2,ac.clasificacion_c AS clasificacion, e.email_address AS email, ac.dir_residencia_estado_c as estado
FROM accounts AS a
LEFT JOIN accounts_cstm AS ac ON (ac.id_c = a.id)
LEFT JOIN email_addr_bean_rel AS eb ON (eb.bean_id = a.id AND eb.primary_address = '1' AND !eb.deleted)
LEFT JOIN email_addresses AS e ON (e.id = eb.email_address_id)
WHERE 
!a.deleted AND (DATE(a.date_entered) BETWEEN '".$fechaDesde."' AND '".$fechaHasta."') ".$tira."
ORDER BY a.sic_code 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=2;      
$totalRegistros= 0;      
while($fila = mysql_fetch_array($rst))
{
	$totalRegistros++;
	$objPHPExcel->setActiveSheetIndex(0)            
		->setCellValue("A".$i, utf8_encode($fila["codigo"]))
		->setCellValue("B".$i, utf8_encode($fila["Ced_Rif"]))
		->setCellValue("C".$i, utf8_encode($fila["Nombres"]))
		->setCellValue("D".$i, utf8_encode($fila["movil1"]))
		->setCellValue("E".$i, utf8_encode($fila["movil2"]))
		->setCellValue("F".$i, utf8_encode($fila["estado"]))
		->setCellValue("G".$i, utf8_encode($fila["clasificacion"]))
		->setCellValue("H".$i, utf8_encode($fila["email"]));
						            
$i++;
}
$i--;

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

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

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$nombreArchivo="ListadoClientes.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 Clientes con las Fechas de busqueda proporcionadas"."<br/>";
}
?>