<?php

//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);

if(isset($_COOKIE['persistCupones']))
{
    $userid =   $_COOKIE['persistCupones'];
    session_start();
   $_SESSION['userCupones'] = $userid;
}
if( strlen($_SESSION['userCupones'])>0 ){
	$uid=$_SESSION['userCupones'];
}

if(isset($_COOKIE['rolCupones']))
{
    $rol =   $_COOKIE['rolCupones'];
}

else{
	header('Location: /pedidos-especiales-tienda/cupones-medallia');
}

$filter = $_GET['f'];

$orden =$_GET['orden'];
if(strlen($orden)<1){
	$orden = 'id';

}


$finish_timeb = time();
$texto_finishb= date("d/m/y", $finish_timeb);
require_once 'PHPExcel/Classes/PHPExcel.php';
$filename = 'Reporte_Cupones_Medallia_'.$texto_finishb; //your file name


include_once '../../dbconnect.php';
$dhost = '127.0.0.1';
$duser = 'bn_wordpress';
$dpw = '5e601fd753';
$dname = 'vips18';

$connection = mysqli_connect($dhost, $duser, $dpw, $dname);
//print first employee table


if (strlen($filter)<1){

	$sql = "SELECT * FROM tacupones_medallia WHERE generado_por = '$uid'  ORDER BY $orden ASC";

}

else{

if($filter == 'a'){

if($rol == 'super' ){
	$sql = "SELECT * FROM tacupones_medallia ORDER BY $orden ASC";
}

if($rol == 'gerente' ){
		$sql = "SELECT * FROM tacupones_medallia WHERE gerente = $uid  ORDER BY $orden ASC";
}

if($rol == 'distrital' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE distrital = $uid ORDER BY $orden ASC";
}

if($rol == 'regional' ){
			$sql = "SELECT * FROM tacupones_medallia WHERE regional = $uid ORDER BY $orden ASC";
}

}

if($filter == 'r'){

if($rol == 'super' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'regional' ORDER BY $orden ASC";
}

}

if($filter == 'd'){

if($rol == 'super' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'distrital' ORDER BY $orden ASC";
}

if($rol == 'regional' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'distrital' AND distrital = $uid ORDER BY $orden ASC";
}

}

if($filter == 'g'){

if($rol == 'super' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'gerente' ORDER BY $orden ASC";
}

if($rol == 'regional' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'gerente' AND regional = $uid ORDER BY $orden ASC";
}

if($rol == 'distrital' ){
	$sql = "SELECT * FROM tacupones_medallia WHERE rol = 'gerente' AND distrital = $uid ORDER BY $orden ASC";
}

}




}


$rs_result = $mysqli->query($sql)  or die("Error en".$sql);



$objPHPExcel = new PHPExcel();
/*********************Add column headings START**********************/
		$objPHPExcel->setActiveSheetIndex(0)

					->setCellValue('A1', '#Registro')
					->setCellValue('B1', 'Fecha')
					->setCellValue('C1', 'Generado por')
					->setCellValue('D1', 'Cupón para')
					->setCellValue('E1', 'Motivo')
					->setCellValue('F1', 'Solución')
					->setCellValue('G1', 'Código cupón')
					->setCellValue('H1', 'Cupón')
					->setCellValue('I1', 'ID Medallia')
					->setCellValue('J1', 'Medio de contacto')
					->setCellValue('K1', 'Sucursal');
		/*********************Add column headings END**********************/


$row = 2;
while($subrow = mysqli_fetch_assoc($rs_result))
{

	$id = $subrow['id'];
	$nombre = $subrow['nombre_cliente'];
	$nombre = strtolower($nombre);
	$nombrecorreo = ucwords($nombre)." (".$subrow['correo_cliente'].")";
	$autor = $subrow['generado_por'];

	if(strlen($autor) > 3){

		$sqlop = "SELECT * FROM stores WHERE storeNumber = $autor LIMIT 1";
		$resultop = $mysqli->query($sqlop)  or die("Error en".$sqlop);
		$rowop= mysqli_fetch_array($resultop);

		$generado = $rowop['gerente'];
	}
	else{

		$sqlop = "SELECT * FROM tausuarios_ad WHERE id = $autor LIMIT 1";
		$resultop = $mysqli->query($sqlop)  or die("Error en".$sqlop);
		$rowop= mysqli_fetch_array($resultop);

		$generado = $rowop['nombre'];

	}

 		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $id);
 		$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $subrow['fecha_generacion']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $generado);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $nombrecorreo);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $subrow['motivo_generacion']);
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $subrow['acuerdo_solucion']);
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $subrow['codigo_cupon']);
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $subrow['tipo_cupon']);
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$row, $subrow['id_encuesta']);
		$objPHPExcel->getActiveSheet()->setCellValue('J'.$row, $subrow['medio_contacto']);
		$objPHPExcel->getActiveSheet()->setCellValue('K'.$row, $subrow['sucursal_visitada']);

    $row++;
}
/*********************Autoresize column width depending upon contents START**********************/
        foreach(range('A','K') as $columnID) {
			$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
		}
		/*********************Autoresize column width depending upon contents END***********************/

		$objPHPExcel->getActiveSheet()->getStyle('A1:K1')->getFont()->setBold(true); //Make heading font bold

		/*********************Add color to heading START**********************/
		$styleArray = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => 'F0F0F0'),
        'size'  => 10,
        'name'  => 'Century Gothic',

    ),
	'fill'  => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array('rgb' => 'DD1B2B')
	),
	'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )

	);
		$objPHPExcel->getActiveSheet()
					->getStyle('A1:K1')->applyFromArray($styleArray);
		/*********************Add color to heading END***********************/

		$objPHPExcel->getActiveSheet()->setTitle('Mensajes de Atención al cliente'); //give title to sheet
		$objPHPExcel->setActiveSheetIndex(0);
		header('Content-Type: application/vnd.ms-excel');
		header("Content-Disposition: attachment;Filename=$filename.xls");
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
?>
