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

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

		global   $current_user;
		$user = $current_user->user_name;

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

if($producto !="Todos"){
	$tira.$tira = "  AND vc.p01_productos_id_c = '".$producto."' ";
	}	
// 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('K4')->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 Cuadro de Producción'))
	  ->setCellValue('A2', utf8_encode('Desde '.$fechaDesde2.' Hasta '.$fechaHasta2.' Emisión Reporte: '.$fechaHoy))
	  ->setCellValue('A3', utf8_encode('Usuario: '.$usuario))
	  ->setCellValue('A4', utf8_encode('Fecha de la Venta'))
	  ->setCellValue('B4', utf8_encode('Cédula/RIF'))
      ->setCellValue('C4', utf8_encode('Nombres y Apellidos'))
	  ->setCellValue('D4', utf8_encode('Teléfono'))
	  ->setCellValue('E4', utf8_encode('Correo Electrónico'))
      ->setCellValue('F4', utf8_encode('Código Producto'))
      ->setCellValue('G4', utf8_encode('Nombre del Producto'))
      ->setCellValue('H4', utf8_encode('Monto'))
	  ->setCellValue('I4', utf8_encode('Comision'))
      ->setCellValue('J4', utf8_encode('Código de la Venta'))
      ->setCellValue('K4', utf8_encode('Estatus de la Venta'))
	  ->setCellValue('L4', utf8_encode('Código de Verificación'))
      ->setCellValue('M4', utf8_encode('Empleado'))
      ->setCellValue('N4', utf8_encode('División'));
      

$sql = "SELECT a.name AS cedula, ac.nombres_apellidos_c AS Nombres, p.name AS Producto, p.codigo AS NombreProducto,
v.monto AS monto, v.name AS codigoVenta, v.estatus AS estatusVenta,
CONCAT( u.first_name, ' ', u.last_name) AS Agente, vc.comision_venta_c AS comision, e.name AS empleado,
vc.departamento_c AS departamento, DATE_FORMAT(v.fecha_venta, '%d/%m/%Y') AS FechaVenta,
ed.email_address AS email, ac.phone_movil_c AS telefono, vec.name as verificacion
FROM accounts AS a
LEFT JOIN accounts_cstm AS ac ON (ac.id_c = a.id)
LEFT JOIN accounts_p02_ventas_1_c AS av ON (av.accounts_p02_ventas_1accounts_ida = a.id)
LEFT JOIN p02_ventas AS v ON (v.id = av.accounts_p02_ventas_1p02_ventas_idb)
LEFT JOIN p02_ventas_cstm AS vc ON (v.id = vc.id_c)
LEFT JOIN p06_verificacion AS vec ON (vc.p06_verificacion_id_c = vec.id)
LEFT JOIN p01_productos AS p ON (vc.p01_productos_id_c = p.id)
LEFT JOIN users AS u ON (u.id = v.created_by)
LEFT JOIN p10_empleados AS e ON (e.id = vc.p10_empleados_id_c)
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 ed ON (ed.id = eb.email_address_id)
WHERE
!a.deleted AND !v.deleted AND !p.deleted  AND v.estatus IN ('PorVerificar','Completada')
 AND (DATE(v.date_entered) BETWEEN '".$fechaDesde."' AND '".$fechaHasta."') ".$tira."
ORDER BY v.fecha_venta 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))
{
	$totalRegistros++;
	$objPHPExcel->setActiveSheetIndex(0)            
		->setCellValue("A".$i, utf8_encode($fila["FechaVenta"]))
		->setCellValue("B".$i, utf8_encode($fila["cedula"]))
		->setCellValue("C".$i, utf8_encode($fila["Nombres"]))
		->setCellValue("D".$i, utf8_encode($fila["telefono"]))
		->setCellValue("E".$i, utf8_encode($fila["email"]))
		->setCellValue("F".$i, utf8_encode($fila["Producto"]))
		->setCellValue("G".$i, utf8_encode($fila["NombreProducto"]))
		->setCellValue("H".$i, utf8_encode($fila["monto"]))
		->setCellValue("I".$i, utf8_encode($fila["comision"]))
		->setCellValue("J".$i, utf8_encode($fila["codigoVenta"]))
		->setCellValue("K".$i, utf8_encode($fila["estatusVenta"]))
		->setCellValue("L".$i, $fila["verificacion"])
		->setCellValue("M".$i, utf8_encode($fila["empleado"]))
		->setCellValue("N".$i, utf8_encode($fila["departamento"]));
$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('ListadoVentasRealizadas');

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