<?php
require_once "../clases/config.php";
require_once '../clases/PHPExcel.php';
 
$conexion= mysqli_connect(HOST,USER,PASS, DBNAME) or die ("could not connect to mysql"); 
$conexion->set_charset("utf8");
$sql="SELECT a.id,a.nombre,a.razon_social,a.giro,a.rut,a.email,a.fono1,a.fono2,a.fecha_creacion,CONCAT(b.nombre,' ',b.apepat) as uscreado,a.tipo_proveedor,a.estado,a.fono_emergencia,a.num_f_emergencia,c.Pais,d.ciudad,a.comuna,a.direccion,a.web,a.banco,a.cambiodolar,a.cambiopeso,a.observaciones,a.codpostal FROM proveedores as a LEFT JOIN usuarios as b ON a.us_creador=b.id LEFT JOIN paises as c ON c.Codigo=a.pais LEFT JOIN ciudades as d ON d.idCiudades=a.ciudad ORDER BY a.nombre ASC;";
$resultado=mysqli_query($conexion, $sql) or die(mysql_error());   
$registros = mysqli_num_rows ($resultado);
$fecha=date('d-m-Y'); 
 
 if ($registros > 0) {
   $objPHPExcel = new PHPExcel();
   
   //Informacion del excel
   $objPHPExcel->
    getProperties()
        ->setCreator("I-travel Administrador")
        ->setLastModifiedBy("I-travel Administrador")
        ->setTitle("PROVEEDORES")
        ->setSubject("Hoja 1")
        ->setDescription("Documento generado con PHPExcel")
        ->setKeywords("phpexcel")
        ->setCategory("proveedores");    

   $i = 1;    
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, 'ID');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, 'NOMBRE');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, 'RAZÓN SOCIAL');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, 'GIRO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, 'RUT');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, 'EMAIL');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, 'FONO 1');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, 'FONO 2');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, 'CREADO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, 'CREADO POR');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, 'TIPO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, 'ESTADO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, 'FONO EMERGENCIA');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, 'PAIS');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$i, 'CIUDAD');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$i, 'COMUNA');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q'.$i, 'DIRECCION');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R'.$i, 'WEB');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$i, 'BANCO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T'.$i, 'CAMBIO DOLAR');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$i, 'CAMBIO PESO');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$i, 'OBSERVACIONES');
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W'.$i, 'COD POSTAL');
   
   $i++;
   
   
   
   while ($registro = mysqli_fetch_object ($resultado)) {
      //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, $registro->nombre);
	  
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, strtoupper($registro->id));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, strtoupper($registro->nombre));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, strtoupper($registro->razon_social));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, strtoupper($registro->giro));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, strtoupper($registro->rut));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, strtoupper($registro->email));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, strtoupper($registro->fono1));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, strtoupper($registro->fono2));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, strtoupper($registro->fecha_creacion));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, strtoupper($registro->uscreado));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, strtoupper($registro->tipo_proveedor));
	  $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, strtoupper($registro->estado));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, strtoupper($registro->num_f_emergencia));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, strtoupper($registro->Pais));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$i, strtoupper($registro->ciudad));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$i, strtoupper($registro->comuna));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q'.$i, strtoupper($registro->direccion));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R'.$i, strtoupper($registro->web));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$i, strtoupper($registro->banco));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T'.$i, strtoupper($registro->cambiodolar));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$i, strtoupper($registro->cambiopeso));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$i, strtoupper($registro->observaciones));
	   $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W'.$i, strtoupper($registro->codpostal));
      $i++;
      
   }
}

foreach(range('A','W') as $columnID) { 
	$objPHPExcel->getActiveSheet()->getColumnDimension($columnID) ->setAutoSize(true);
};

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

$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWriter->save('php://output');
exit;
mysqli_close ();
?>