<?php    
    $tabla="resultados";
    include '../comunes_php/cors.php';
    include '../rdb/conn.php';

    $clientes= isset($_REQUEST['clientes']) ? $_REQUEST['clientes'] : '';
    $estados= isset($_REQUEST['estados']) ? $_REQUEST['estados'] : '';
    $repartidores= isset($_REQUEST['repartidor']) ? $_REQUEST['repartidor'] : '';
    $tipoenvio= isset($_REQUEST['tipoenvio']) ? $_REQUEST['tipoenvio'] : '';
    
    $zonas= isset($_REQUEST['zonas']) ? $_REQUEST['zonas'] : '';
    $empresas= isset($_REQUEST['empresas']) ? $_REQUEST['empresas'] : '';
    $aliasml= isset($_REQUEST['aliasml']) ? $_REQUEST['aliasml'] : '';
    

    $idesde= isset($_REQUEST['idesde']) ? $_REQUEST['idesde'] : '';
    $ihasta= isset($_REQUEST['ihasta']) ? $_REQUEST['ihasta'] : '';

    $adesde= isset($_REQUEST['adesde']) ? $_REQUEST['adesde'] : '';
    $ahasta= isset($_REQUEST['ahasta']) ? $_REQUEST['ahasta'] : '';


    
    $ddesde= isset($_REQUEST['desde']) ?(is_null($_REQUEST['desde'])?  '-1':$_REQUEST['desde']) : '-1';
    $dhasta= isset($_REQUEST['hasta']) ?(is_null($_REQUEST['hasta'])?  '-1':$_REQUEST['hasta']) : '-1';
    $dporcentual= isset($_REQUEST['porcentual']) ?(is_null($_REQUEST['porcentual'])?  '-1':$_REQUEST['porcentual']) : '-1';
    

    $res_id= isset($_REQUEST['res_id']) ? $_REQUEST['res_id'] : '';

    $where ="";
   
    if ($clientes <>'') {$where .= " and cliente_id in ($clientes) "; }
    if ($estados <>'') {$where .= " and estado_id in ($estados) "; }
    if ($repartidores <>'') {$where .= " and repartidor_id in ($repartidores) "; }
    if ($tipoenvio <>'') {$where .= " and tipo_envio_id in ($tipoenvio) "; }

    if ($empresas <>'') {$where .= " and empresa_id in ($empresas) "; }
    if ($zonas <>'') {$where .= " and zona_id in ($zonas) "; }
    if ($aliasml <>'') {$where .= " and alias_ml like '%$aliasml%' "; }
    
    if ($idesde<>'') {$where .= " and fecha_iniciado >= '$idesde' "; }
    if ($ihasta<>'') {$where .= " and fecha_iniciado <= '$ihasta' "; }   
    if ($adesde<>'') {$where .= " and fecha_aprobado >= '$adesde' "; }
    if ($ahasta<>'') {$where .= " and fecha_aprobado <= '$ahasta' "; }   
    
    if ($dhasta<>'-1') {$where .= " and saldo <= $dhasta "; }  
    if ($ddesde<>'-1') {$where .= " and saldo >= $ddesde "; }  
    if ($dporcentual<>'-1') {$where .= " and saldo/total >= $dporcentual/100 "; }  
    
    if ($res_id <>'') {$where .= " and id in ($res_id) "; }
    
    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $offset = ($page-1)*$rows;
    $result = array();


$sql = "select "
    . "count(*) "
    . "from"
    . " vw_contratos where 1 ";
$sql.= $where ;
    

    
    $rs = mysqli_query( $conn,$sql);
    $row = mysqli_fetch_row($rs);
    $result["total"] = $row[0];
    		    $myfile = fopen("logFileGet.txt", "a") or die("Unable to open file!");
      		    fwrite($myfile, "\n");  
      		    fwrite($myfile, date("Y-m-d H:i:s")."*****************MODIFICACION*****************");
		        fwrite($myfile, "\n");      		    
        	    fwrite($myfile, $sql);
        	    fwrite($myfile, "\n");
        	    fclose($myfile);	    
    if ($result["total"] === 0) {exit;}  
    
$sql = "select "
    . "*  "
    . "from "
    . "vw_contratos where 1  ";
    
	$sql.=  $where;
	$sql.= " order by id desc"; //limit $offset,$rows ";	
    $rs = mysqli_query( $conn,$sql);

    $items = array();
	error_reporting(E_ALL);
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
	date_default_timezone_set('Europe/London');
	/** Include PHPExcel */
	require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
	$objPHPExcel = new PHPExcel();
	// Set document properties
	$objPHPExcel->getProperties()->setCreator("DanyPlacard")
								 ->setLastModifiedBy("DanyPlacard")
								 ->setTitle("Contratos")
								 ->setSubject("Reporte de Contratos")
								 ->setDescription("Reporte de Contratos")
								 ->setKeywords("Reporte de Contratos")
								 ->setCategory("Reporte de Contratos");


	// Create a first sheet
	$objPHPExcel->setActiveSheetIndex(0);
    


	
	$objPHPExcel->getActiveSheet()->setCellValue('A1', "ID");
	$objPHPExcel->getActiveSheet()->setCellValue('B1', "ESTADO");
	$objPHPExcel->getActiveSheet()->setCellValue('C1', "CLIENTE ");
	$objPHPExcel->getActiveSheet()->setCellValue('D1', "REPARTIDOR");
	$objPHPExcel->getActiveSheet()->setCellValue('E1', "TIPO ENVIO");
	$objPHPExcel->getActiveSheet()->setCellValue('F1', "VENDEDOR");
	$objPHPExcel->getActiveSheet()->setCellValue('G1', "TELEFONO 1");	
	$objPHPExcel->getActiveSheet()->setCellValue('H1', "TELEFONO 2");	
	$objPHPExcel->getActiveSheet()->setCellValue('I1', "EMAIL");	
	$objPHPExcel->getActiveSheet()->setCellValue('J1', "H_SEMANA");	
	$objPHPExcel->getActiveSheet()->setCellValue('K1', "H_SABADO");	
	$objPHPExcel->getActiveSheet()->setCellValue('L1', "TOTAL");	
	$objPHPExcel->getActiveSheet()->setCellValue('M1', "ZONA");	
	$objPHPExcel->getActiveSheet()->setCellValue('N1', "DIRECCION");	
	$objPHPExcel->getActiveSheet()->setCellValue('O1', "CONTACTO");	
	$objPHPExcel->getActiveSheet()->setCellValue('P1', "#PROD");		
	$objPHPExcel->getActiveSheet()->setCellValue('Q1', "DEMORA");	
	$objPHPExcel->getActiveSheet()->setCellValue('R1', "ESCALERA");
	$objPHPExcel->getActiveSheet()->setCellValue('S1', "TARJETA");	
	$objPHPExcel->getActiveSheet()->setCellValue('T1', "ENVIO");
	$objPHPExcel->getActiveSheet()->setCellValue('U1', "ARMADO");
	$objPHPExcel->getActiveSheet()->setCellValue('V1', "SUBTOTAL");
	$objPHPExcel->getActiveSheet()->setCellValue('W1', "COMENTARIOS");	
	$objPHPExcel->getActiveSheet()->setCellValue('X1', "FECHA_INICIADO");
	$objPHPExcel->getActiveSheet()->setCellValue('Y1', "FECHA_APROBADO");
	$objPHPExcel->getActiveSheet()->setCellValue('Z1', "FECHA_FABRICADO");		
	$objPHPExcel->getActiveSheet()->setCellValue('AA1', "EMPRESA");
	$objPHPExcel->getActiveSheet()->setCellValue('AB1', "ALIAS_ML");
	$objPHPExcel->getActiveSheet()->setCellValue('AC1', "#PAGOS");	
	$objPHPExcel->getActiveSheet()->setCellValue('AD1', "SALDO");
	$objPHPExcel->getActiveSheet()->setCellValue('AE1', "RECLAMOS");	
	$objPHPExcel->getActiveSheet()->setCellValue('AF1', "RECL.PEND");	
	$objPHPExcel->getActiveSheet()->setCellValue('AG1', "USER");	
				
				
				
	

	$objPHPExcel->getActiveSheet()->getStyle("A1:AG1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC0C0C0');
	$objPHPExcel->getActiveSheet()->getStyle("A1:AG1")->getFont()->setBold(true);
	
    $objPHPExcel->getActiveSheet()->getColumnDimension('A'	)->setWidth(5);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('M'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('N'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('O'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('P'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('Q'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('R'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('S'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('T'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('U'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('V'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('W'	)->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('X'	)->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('Y'	)->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('Z'	)->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AA'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AB'	)->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AC'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AD'	)->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AE'	)->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AF'	)->setWidth(12);
	$objPHPExcel->getActiveSheet()->getColumnDimension('AG'	)->setWidth(10);

        


	// Freeze panes
	$objPHPExcel->getActiveSheet()->freezePane('A2');


	// Rows to repeat at top
	$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
    $i=2;	
    
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&CContratos');
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('Pagina &P / &N');
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setEvenFooter('Pagina &P / &N');  
	
	
	$styleArray = array(
	    'font'  => array(
	        'bold'  => true,
	        'color' => array('rgb' => '000000'),
	        'size'  => 15,
	        'name'  => 'Verdana'
	    ));

    $i=1;			
    while($row = mysqli_fetch_object($rs)){
    		$i++;
	$objPHPExcel->getActiveSheet()->setCellValue('A'. $i, $row->id);
	$objPHPExcel->getActiveSheet()->setCellValue('B'. $i, $row->estado);
	$objPHPExcel->getActiveSheet()->setCellValue('C'. $i, $row->cliente);
	$objPHPExcel->getActiveSheet()->setCellValue('D'. $i, $row->repartidor);
	$objPHPExcel->getActiveSheet()->setCellValue('E'. $i, $row->tipo_envio);
	$objPHPExcel->getActiveSheet()->setCellValue('F'. $i, $row->vendedor);
	$objPHPExcel->getActiveSheet()->setCellValue('G'. $i, $row->telefono_1);	
	$objPHPExcel->getActiveSheet()->setCellValue('H'. $i, $row->telefono_2);	
	$objPHPExcel->getActiveSheet()->setCellValue('I'. $i, $row->email);	
	$objPHPExcel->getActiveSheet()->setCellValue('J'. $i, $row->horarios_en_semana);	
	$objPHPExcel->getActiveSheet()->setCellValue('K'. $i, $row->horarios_en_sabado);	
	$objPHPExcel->getActiveSheet()->setCellValue('L'. $i, $row->total);	
	$objPHPExcel->getActiveSheet()->setCellValue('M'. $i , $row->zona);	
	$objPHPExcel->getActiveSheet()->setCellValue('N'. $i , $row->direccion);	
	$objPHPExcel->getActiveSheet()->setCellValue('O'. $i , $row->contacto);	
	$objPHPExcel->getActiveSheet()->setCellValue('P'. $i , $row->cant_productos);		
	$objPHPExcel->getActiveSheet()->setCellValue('Q'. $i , $row->demora);	
	$objPHPExcel->getActiveSheet()->setCellValue('R'. $i , $row->escalera);
	
	
	$objPHPExcel->getActiveSheet()->setCellValue('S'. $i ,($row->tarjeta=='on')?'si':'no');	
	$objPHPExcel->getActiveSheet()->setCellValue('T'. $i , $row->envio);
	$objPHPExcel->getActiveSheet()->setCellValue('U'. $i , $row->armado);
	$objPHPExcel->getActiveSheet()->setCellValue('V'. $i , $row->subtotal);
	$objPHPExcel->getActiveSheet()->setCellValue('W'. $i , $row->comentarios);	
	$objPHPExcel->getActiveSheet()->setCellValue('X'. $i , $row->fecha_iniciado);
	$objPHPExcel->getActiveSheet()->setCellValue('Y'. $i , $row->fecha_aprobado);
	$objPHPExcel->getActiveSheet()->setCellValue('Z'. $i , $row->fecha_fabricado);		
	$objPHPExcel->getActiveSheet()->setCellValue('AA'. $i, $row->empresa);
	$objPHPExcel->getActiveSheet()->setCellValue('AB'. $i, $row->alias_ml);
	$objPHPExcel->getActiveSheet()->setCellValue('AC'. $i, $row->total_pagos);	
	$objPHPExcel->getActiveSheet()->setCellValue('AD'. $i, $row->saldo);
	$objPHPExcel->getActiveSheet()->setCellValue('AE'. $i, $row->reclamos);	
	$objPHPExcel->getActiveSheet()->setCellValue('AF'. $i, $row->reclamos_pendientes);
	$objPHPExcel->getActiveSheet()->setCellValue('AG'. $i, $row->usuario_id);	


	
        $objPHPExcel->getActiveSheet()->getStyle("A".$i.":AG".$i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );			
    }

//	
	$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);	
	$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
       
	$objPHPExcel->getActiveSheet()->getStyle('A1:AG1'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
	    
        $objPHPExcel->setActiveSheetIndex(0);
	
    
    // Save Excel 2007 file
    $callStartTime = microtime(true);

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="contratos.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); 
    

    
?>