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

//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($estatus !="Todos"){
//	$tira = "AND ac.estatus_ultima_llamada_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('C6:D6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('C6')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('D6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getFill()->getStartColor()->setRGB('00519F');
$objPHPExcel->getActiveSheet()->getStyle('C5:D5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C4:D4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('C4:D4');//Para combinar las celdas
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');//Para combinar las celdas


$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
      ->setCellValue('C4', utf8_encode('Gestión de Llamadas por Estatus'))
      ->setCellValue('C5', utf8_encode('Desde  '.$fechaDesde2. '  Hasta '.$fechaHasta2))
			->setCellValue('C6', utf8_encode('Estado de la Llamada'))
      ->setCellValue('D6', utf8_encode('Número de Llamadas'));

$sql = "SELECT COUNT(c.status) AS Total,
CASE c.status
  WHEN 'Planned' THEN 'Por Llamar'
  WHEN 'Held' THEN 'Contactado'
  WHEN 'Not Held' THEN 'No Localizado'
  WHEN 'NoResp1' THEN 'No Responde 1er Intento'
  WHEN 'NoResp2' THEN 'No Responde 2do Intento'
  WHEN 'NoResp3' THEN 'No Responde 3er Intento'
  WHEN 'Volverallamar' THEN 'Volver a Llamar'
  WHEN 'NumeroEquivocado' THEN 'Número Equivocado'
  WHEN 'ContactoNoInteresado' THEN 'Contacto No Interesado'
END AS Estado


FROM calls AS c
LEFT JOIN accounts AS a ON (a.id = c.parent_id)
LEFT JOIN users AS u ON (u.id=c.assigned_user_id)

WHERE !c.deleted 
AND !a.deleted
AND !u.deleted
AND c.status <> ''
AND (DATE(c.date_entered) BETWEEN '".$fechaDesde."' AND '".$fechaHasta."') ".$tira."
AND c.status <> '' AND c.parent_id <>'' 
GROUP BY c.status;";

$Total = 0;
$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=7;      
$totalRegistros= 0;      
while($fila = mysql_fetch_array($rst))
{
	$totalRegistros++;
	$objPHPExcel->setActiveSheetIndex(0)            
		->setCellValue("C".$i, utf8_encode($fila["Estado"]))
		->setCellValue("D".$i, utf8_encode($fila["Total"]));
		
		$Total = $Total + $fila["Total"];
						            
$i++;
}

$objPHPExcel->setActiveSheetIndex(0) 
    ->setCellValue("C".$i, utf8_encode('Total Llamadas'))
		->setCellValue("D".$i, utf8_encode($Total));

$i--;

if ($totalRegistros > 0){
$objPHPExcel->getActiveSheet()->getStyle("C".$i.":"."D".$i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('C7:C'.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('D7:D'.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

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

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