<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2015 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 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    ##VERSION##, ##DATE##
 */

include_once("../../../php/sessionStart.php");
include_once("../../../php/db/dbExec.php");
include_once("../../../php/fpdf/functions.php");
include_once('../../../php/timezone.php');
include_once('../../../php/maxExecTime.php');

$empCod=utf8_decode($_SESSION["empCod"]);
$empNom=utf8_decode($_SESSION["empNom"]);
$usuCod = utf8_decode($_SESSION['usuCod']);
$usuNom = utf8_decode($_SESSION['usuNom']);

$dtDel=$_GET["dtDel"];
$dtAl=$_GET["dtAl"];

# QUERYS
$sNomper = "SELECT np.*, TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape,
            TRIM(mae.mae_apec) AS mae_apec, TRIM(mae.mae_ctab) AS mae_ctab, TRIM(mae.mae_nigss) AS mae_nigss, 
            mae.mae_fing, mae.mae_fsal, TRIM(mae.mae_nit)  AS mae_nit, mae.mae_stat,
            SUM(np.np_sord+np.np_sexts+np.np_sextd+np.np_comi+np.np_otrp) AS np_devengado,
            SUM(np.np_igss) AS np_totigss
            FROM nomper np
            LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
            WHERE np_emp='$empCod' AND np_ffin>='$dtDel' AND np_ffin<='$dtAl' AND mae_igsssn='S' GROUP BY np_emp,np_cod,np_ano,np_mes";

# EJECUCION DE QUERYS
$db = new dbExec();
$select["nomper"] = $db->query($sNomper);
$db->close();


/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

require_once dirname(__FILE__) . '/../../../php/PHPExcel/Classes/PHPExcel.php';
require_once dirname(__FILE__) . '/../../../php/PHPExcel/Classes/PHPExcel/IOFactory.php';

// Esto es para abrir y leer el Excel que vamos a trabajar
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('SISTEMAIGSS.xlsx');

// Indicamos que usaremos la primera hoja del excel
$objPHPExcel->setActiveSheetIndexByName('EMPLEADOS'); 

# VALORES PARA COLOCAR EN LA HOJA
//$objPHPExcel->getActiveSheet()->SetCellValue('C2', '21345610');

$j=2;
while ($rec = $db->fetchAssoc($select["nomper"])) {
    // Para separar el primero y segundo nombre y primero y sengudo apellido
    $lnom=strlen($rec["mae_nom"])+1;
    $lape=strlen($rec["mae_ape"])+1;
    $nom1="";
    $nom2="";
    $ape1="";
    $ape2="";
    $t_flag=1;
    $t_col=0;
    for ($i=0; $i < $lnom; $i++) { 
       $tchar=substr($rec["mae_nom"],$t_col,1);
       if ($tchar==" ") { 
           $t_col+=1;
           $t_flag=2;
           continue;
       }
       if ($t_flag==1) {
          $nom1=$nom1.$tchar;
       } else {
          $nom2=$nom2.$tchar;
       }
       $t_col+=1;
    }
    //  Para separar el primero y segundo apellido
    $t_flag=1;
    $t_col=0;
    for ($i=0; $i < $lape; $i++) { 
       $tchar=substr($rec["mae_ape"],$t_col,1);
       if ($tchar==" ") { 
           $t_col+=1;
           $t_flag=2;
           continue;
       }
       if ($t_flag==1) {
          $ape1=$ape1.$tchar;
       } else {
          $ape2=$ape2.$tchar;
       }
       $t_col+=1;
    }


    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$j, $rec["mae_nigss"]);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$j, utf8_encode($nom1));
    $objPHPExcel->getActiveSheet()->SetCellValue('G'.$j, utf8_encode($nom2));
    $objPHPExcel->getActiveSheet()->SetCellValue('I'.$j, utf8_encode($ape1));
    $objPHPExcel->getActiveSheet()->SetCellValue('K'.$j, utf8_encode($ape2));
    $objPHPExcel->getActiveSheet()->SetCellValue('M'.$j, utf8_encode($rec["mae_apec"]));
    $objPHPExcel->getActiveSheet()->SetCellValue('O'.$j, $rec["np_devengado"]);
    if ($rec["mae_fing"]>=$dtDel && $rec["mae_fing"]<=$dtAl) {
      $objPHPExcel->getActiveSheet()->SetCellValue('Q'.$j, $rec["mae_fing"]);
    }
    if ($rec["mae_stat"]=='B') {
      if ($rec["mae_fsal"]>=$dtDel && $rec["mae_fsal"]<=$dtAl) {
        $objPHPExcel->getActiveSheet()->SetCellValue('S'.$j, $rec["mae_fsal"]);
      }
    }
    $objPHPExcel->getActiveSheet()->SetCellValue('U'.$j, 1);
    $objPHPExcel->getActiveSheet()->SetCellValue('W'.$j, $rec["mae_nit"]);
    $objPHPExcel->getActiveSheet()->SetCellValue('Y'.$j, 0);
    $objPHPExcel->getActiveSheet()->SetCellValue('AA'.$j, 'P');
    $j+=1;
} 

//Guardamos los cambios en un nuevo excel y se descarga
$filename = utf8_decode("PlanillaSeguro");
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=\"{$filename}.xlsx\"");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

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

?>