<?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');


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

$fechaDesde = $_GET["fechaDesde"];	
$fechaHasta = $_GET["fechaHasta"];

$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);

// 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:E1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setRGB('6F83F7');


$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);

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', 'Nombre Empresa')
            ->setCellValue('B1', 'Persona Contacto')
            ->setCellValue('C1', 'Cargo')
            ->setCellValue('D1', utf8_encode('Teléfono Móvil'))
            ->setCellValue('E1', utf8_encode('Correo'));



$sql = "SELECT 
	a.name                               AS 'nombre_empresa',
	CONCAT(c.first_name,' ',c.last_name) AS 'persona_contacto',
	c.title                              AS 'cargo',
	c.phone_mobile                       AS 'telefono_movil',
	ea.email_address                     AS 'correo' 
FROM 
	accounts AS a
	INNER JOIN accounts_cstm       AS ac   ON a.id                  = ac.id_c
	INNER JOIN accounts_contacts   AS act  ON a.id                  = act.account_id
	INNER JOIN contacts            AS c    ON c.id                  = act.contact_id
	INNER JOIN email_addr_bean_rel AS eabr ON eabr.bean_id          = c.id
	INNER JOIN email_addresses     AS ea   ON eabr.email_address_id = ea.id
WHERE 
	a.deleted = '0'
	AND act.deleted = '0'
	AND c.deleted = '0'
	AND ea.deleted = '0'
	AND eabr.deleted = '0'
	AND ac.ciudad_c LIKE '%Barquisimeto%'
	AND DATE(a.date_entered) BETWEEN '$fechaDesde' AND '$fechaHasta'

GROUP BY ea.email_address
ORDER BY a.name ASC";	
	

$rst=mysql_query($sql,$strConexion); //Ejecutamos la SQL
if(!$rst) //Comprobamos si hay errores
die("Error MySQL de Extracción de Datos");
$i=2;      
$hayRegistros= false;      
while($fila = mysql_fetch_array($rst)){
	$hayRegistros = true;

			$objPHPExcel->setActiveSheetIndex(0)            
			->setCellValue("A".$i, $fila["nombre_empresa"])
			->setCellValue("B".$i, $fila["persona_contacto"])
			->setCellValue("C".$i, $fila["cargo"])
			->setCellValue("D".$i, $fila["telefono_movil"])
			->setCellValue("E".$i, $fila["correo"]);
			
		$objPHPExcel->getActiveSheet()->getStyle("A".$i.":E".$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
		$objPHPExcel->getActiveSheet()->getStyle("A".$i.":E".$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
			$cedulaOld = $fila["Cedula"];
			$nombreOld = utf8_encode($fila["Nombre"]);
			$telefCOld = $fila["TelefonoC"];
			$telefAOld = $fila["TelefonoA"];
			$twitterOld = utf8_encode($fila["Twitter"]);

		$i++;
}
$i--;

			/*$objPHPExcel->setActiveSheetIndex(0)            
			->setCellValue("A".$i, $cedulaOld)
			->setCellValue("B".$i, $nombreOld)
			->setCellValue("C".$i, $telefCOld)
			->setCellValue("D".$i, $telefAOld)
			->setCellValue("E".$i, $twitterOld)
			->setCellValue("F".$i, $emailOld)
			->setCellValue("G".$i, $placaOld)
			->setCellValue("H".$i, $modeloOld)
			->setCellValue("I".$i, $annoOld);
		$objPHPExcel->getActiveSheet()->getStyle("A".$i.":I".$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
		$objPHPExcel->getActiveSheet()->getStyle("A".$i.":I".$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);*/

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



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

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