<?php
$finish_timeb = time();		
$texto_finishb= date("d/m/y", $finish_timeb);
require_once 'PHPExcel/Classes/PHPExcel.php';
$filename = 'Reporte_Pedidos_'.$texto_finishb; //your file name
				
$servername = "localhost";
$username = "bn_wordpress";
$password = "5e601fd753";
$dbname = "bd_vips";
// Create connection
$mysqli = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8");


$q = $_GET['q'];
$fecha_inicial = urldecode($_GET['f1']);
$fecha_final = urldecode ($_GET['f2']);
$por = $_GET['por'];
$orden = $_GET['orden'];
if(strlen($por)<2 || $por =='fecha_reg'){$por = 'id'; }
if(strlen($orden)<2){$orden = 'DESC'; }

if(strlen($fecha_inicial)>5 && strlen($fecha_final)>5){
 $rango = "DATE(fecha_pedido) between '$fecha_inicial' AND '$fecha_final' AND"; 
}
else{
  $rango = "";
}

$consulta = "SELECT * FROM tapedidos_navidad WHERE $rango source!='Web' AND activo = 1 AND codigo_pedido IS NOT NULL ORDER BY $por $orden";
$porby = $por;
                 
if(strlen($q)>2){
    $sql1 = "SELECT * FROM tapedidos_navidad WHERE $rango (id_user LIKE '%$q%' OR nombre LIKE '%$q%' OR apellido LIKE '%$q%' OR correo LIKE '%$q%' OR  telefono LIKE '%$q%' OR paquetes LIKE '%$q%' OR sucursal LIKE '%$q%' OR  fecha_entrega LIKE '%$q%'  OR hora_entrega  LIKE '%$q%' OR descuento  LIKE '%$q%' OR total  LIKE '%$q%' OR codigo_pedido  LIKE '%$q%') AND activo = 1 AND codigo_pedido IS NOT NULL AND source!='Web' ORDER BY $porby $orden";
    
}
else{
    $sql1 = $consulta;
}
$porby = $por;
$result1 = $mysqli->query($sql1)  or die("Error en".$sql1);
$numfilas1 = $result1->num_rows;

$objPHPExcel = new PHPExcel();
/*********************Add column headings START**********************/
		$objPHPExcel->setActiveSheetIndex(0) 
                    ->setCellValue('A1', 'id')
                    ->setCellValue('B1', 'DIA (Fecha pedido)')
                    ->setCellValue('C1', 'MES (Fecha pedido)')
                    ->setCellValue('D1', 'AÑO (Fecha pedido)')
                    ->setCellValue('E1', 'NOMBRE')
                    ->setCellValue('F1', 'APELLIDO')
                    ->setCellValue('G1', 'CORREO')
                    ->setCellValue('H1', 'TELÉFONO')
                    ->setCellValue('I1', 'ORDEN')
                    ->setCellValue('J1', '% DESC')
                    ->setCellValue('K1', 'SUBTOTAL')
                    ->setCellValue('L1', 'DESCUENTO')
                    ->setCellValue('M1', 'TOTAL')
                    ->setCellValue('N1', 'DIA (Fecha entrega)')
                    ->setCellValue('O1', 'MES (Fecha entrega)')
                    ->setCellValue('P1', 'AÑO (Fecha entrega)')
                    ->setCellValue('Q1', 'HORA ENTREGA')
                    ->setCellValue('R1', 'SUCURSAL')
                    ->setCellValue('S1', 'MONEDERO')
                    ->setCellValue('T1', '$ ABONADO')
                    ->setCellValue('U1', 'FUENTE');
		/*********************Add column headings END**********************/
		
		
$row = 2;
while($subrow = mysqli_fetch_array($result1))
{

    $someJSON = '['.$subrow['payload'].']';
          
    // Convert JSON string to Array
    $someArray = json_decode($someJSON, true);
      //print_r($someArray);        // Dump all data of the Array
      //echo $someArray[0]["clave"]; // Access Array data
    $items ="";
    $more = "";
    $lfcr = "\n";
    foreach ($someArray as $key => $value) {

      if(strlen($value["guarnicion"])>5){$more= '+';}
      else{$more = "";}
      $items.= $value["cantidad"] . " ". $value["nombre"] .$more." ".$value["guarnicion"]." $" . $value["total"].$lfcr;
    }
    
    $abonado = $subrow['abonado'];

    if(strlen($subrow['abonado']) <2 || $subrow['abonado'] == 0 ){
      $abonado = '0.00';
    }
    $monedero = "'".$subrow['codigo_pedido'];
    $date_pedido = DateTime::createFromFormat("d/m/y h:i:s a", $subrow['fecha_pedido']);
    $date_entrega = DateTime::createFromFormat("d-m-Y", $subrow['fecha_entrega']);

    $dia_pedido = $date_pedido->format("d");
    $mes_pedido = $date_pedido->format("m");
    $anio_pedido = $date_pedido->format("Y");

    $dia_entrega =  $date_entrega->format("d");
    $mes_entrega =  $date_entrega->format("m");
    $anio_entrega = $date_entrega->format("Y");
	
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dia_pedido);
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $mes_pedido);
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $anio_pedido);
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, ucwords($subrow["nombre"]));
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, ucwords($subrow['apellido']));    $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, ucwords($subrow["correo"]));
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, ucwords($subrow['telefono']));
    $objPHPExcel->getActiveSheet()->setCellValue('I'.$row, trim($items));
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$row, $subrow['porcentaje']);
    $objPHPExcel->getActiveSheet()->setCellValue('K'.$row, "$".$subrow["subtotal"]);
    $objPHPExcel->getActiveSheet()->setCellValue('L'.$row, "$".$subrow["descuento"]);
    $objPHPExcel->getActiveSheet()->setCellValue('M'.$row, "$".$subrow["total"]);
    $objPHPExcel->getActiveSheet()->setCellValue('N'.$row, $dia_entrega);
    $objPHPExcel->getActiveSheet()->setCellValue('O'.$row, $mes_entrega);
    $objPHPExcel->getActiveSheet()->setCellValue('P'.$row, $anio_entrega);
    $objPHPExcel->getActiveSheet()->setCellValue('Q'.$row, $subrow["hora_entrega"]);
    $objPHPExcel->getActiveSheet()->setCellValue('R'.$row, $subrow['sucursal']);
    $objPHPExcel->getActiveSheet()->setCellValue('S'.$row, $monedero);
    $objPHPExcel->getActiveSheet()->setCellValue('T'.$row, $abonado);
    $objPHPExcel->getActiveSheet()->setCellValue('U'.$row, $subrow['source']);
    $row++;
}
/*********************Autoresize column width depending upon contents START**********************/
        foreach(range('A','S') as $columnID) {
			$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
		}
		/*********************Autoresize column width depending upon contents END***********************/
		
        $objPHPExcel->getActiveSheet()->getStyle('A1:U1')->getFont()->setBold(true); //Make heading font bold
        $objPHPExcel->getActiveSheet()->getStyle('I1:I'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
		
		/*********************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' => '4c7307')
        ),
        'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        
        );
		$objPHPExcel->getActiveSheet()
					->getStyle('A1:U1')->applyFromArray($styleArray);
		/*********************Add color to heading END***********************/
		
		$objPHPExcel->getActiveSheet()->setTitle('Pedidos Openpay'); //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;
?>