<?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/maxExecTime.php');
include_once('../../../php/functions.php');
include_once("../../../php/fpdf/functions.php");

$bitForm = "nom1040";
$bitMod = "nom";
$empCod=utf8_decode($_SESSION["empCod"]);
$empNom=utf8_decode($_SESSION["empNom"]);
$usuCod = utf8_decode($_SESSION['usuCod']);
$usuNom = utf8_decode($_SESSION['usuNom']);
$empMoneda=utf8_decode($_SESSION["empMoneda"]);
$empTcam=utf8_decode($_SESSION["pTcam"]);


$numNum=$_GET["numNum"];
$selBodegapro=$_GET['selBodegapro'];
$selDep=$_GET['selDep'];
$selPuesto=$_GET['selPuesto'];
$numCod=(empty($_GET['numCod'])) ? 0 : $_GET['numCod'];
$selBan=$_GET["selBan"];
$Bannom=$_GET["Bannom"];
$Banctab=$_GET["Banctab"];
$Banctac=$_GET["Banctac"];
$dtFec=$_GET["dtFec"];
$crTrepo=$_GET['crTrepo'];
$numAnio=date("Y", strtotime($dtFec));
$numMes=date("n", strtotime($dtFec));
$tipop="004";
$ctasuel="2110202";
$dtGrabado=date("Y-m-d H:i:s", STRTOTIME(date('h:i:sa')));

# QUERYS
if ($numCod!=0) {
    $sNomper = "SELECT np.*, gru.*, dep.*, mae.mae_gru, mae.mae_dep, mae.mae_tpue,  TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape, mae_igsssn, TRIM(mae.mae_nomch) AS mae_nomch, mae_fpago,
                        TRIM(gru_nom) AS gru_nom, TRIM(dep_nom) AS dep_nom, mae.mae_fpago, TRIM(tpu.tpu_nom) AS tpu_nom 
                        FROM nomper np
                        LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
                        LEFT JOIN tpuesto tpu ON mae.mae_tpue = tpu.tpu_cod 
                        LEFT JOIN grupo gru ON np.np_emp=gru.gru_emp AND np.np_gru = gru.gru_cod 
                        LEFT JOIN dep dep ON np.np_emp=dep.dep_emp AND np.np_dep = dep.dep_cod
                WHERE np_emp='$empCod' AND np_numpla='$numNum' AND np_cod='$numCod' ORDER BY np_dep, mae.mae_tpue,mae.mae_nom,mae.mae_ape";
} else {
    if ($selDep=="000") {
        if ($selPuesto=="000") {
            $sNomper = "SELECT np.*, gru.*, dep.*, mae.mae_gru, mae.mae_dep, mae.mae_tpue,  TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape, mae_igsssn, TRIM(mae.mae_nomch) AS mae_nomch, mae_fpago,
                        TRIM(gru_nom) AS gru_nom, TRIM(dep_nom) AS dep_nom, mae.mae_fpago, TRIM(tpu.tpu_nom) AS tpu_nom 
                        FROM nomper np
                        LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
                        LEFT JOIN tpuesto tpu ON mae.mae_tpue = tpu.tpu_cod 
                        LEFT JOIN grupo gru ON np.np_emp=gru.gru_emp AND np.np_gru = gru.gru_cod 
                        LEFT JOIN dep dep ON np.np_emp=dep.dep_emp AND np.np_dep = dep.dep_cod 
                        WHERE np_emp='$empCod' AND np_numpla='$numNum' and mae.mae_bodega='$selBodegapro' ORDER BY np_dep, mae.mae_tpue,mae.mae_nom,mae.mae_ape";
        } else {
            $sNomper = "SELECT np.*, gru.*, dep.*, mae.mae_gru, mae.mae_dep, mae.mae_tpue,  TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape, mae_igsssn, TRIM(mae.mae_nomch) AS mae_nomch, mae_fpago, 
                        TRIM(gru_nom) AS gru_nom, TRIM(dep_nom) AS dep_nom, mae.mae_fpago, TRIM(tpu.tpu_nom) AS tpu_nom 
                        FROM nomper np
                        LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
                        LEFT JOIN tpuesto tpu ON mae.mae_tpue = tpu.tpu_cod 
                        LEFT JOIN grupo gru ON np.np_emp=gru.gru_emp AND np.np_gru = gru.gru_cod 
                        LEFT JOIN dep dep ON np.np_emp=dep.dep_emp AND np.np_dep = dep.dep_cod 
                        WHERE np_emp='$empCod' AND np_numpla='$numNum' and mae.mae_bodega='$selBodegapro' and mae.mae_tpue='$selPuesto' ORDER BY np_dep, mae.mae_tpue,mae.mae_nom,mae.mae_ape";
        }       
    } else {
        if ($selPuesto=="000") {
            $sNomper = "SELECT np.*, gru.*, dep.*, mae.mae_gru, mae.mae_dep, mae.mae_tpue,  TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape, mae_igsssn, TRIM(mae.mae_nomch) AS mae_nomch, mae_fpago, 
                        TRIM(gru_nom) AS gru_nom, TRIM(dep_nom) AS dep_nom, mae.mae_fpago, TRIM(tpu.tpu_nom) AS tpu_nom 
                        FROM nomper np
                        LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
                        LEFT JOIN tpuesto tpu ON mae.mae_tpue = tpu.tpu_cod 
                        LEFT JOIN grupo gru ON np.np_emp=gru.gru_emp AND np.np_gru = gru.gru_cod 
                        LEFT JOIN dep dep ON np.np_emp=dep.dep_emp AND np.np_dep = dep.dep_cod 
                        WHERE np_emp='$empCod' AND np_numpla='$numNum' and mae.mae_bodega='$selBodegapro' and np_dep='$selDep' ORDER BY np_dep, mae.mae_tpue,mae.mae_nom,mae.mae_ape";
        } else {
            $sNomper = "SELECT np.*, gru.*, dep.*, mae.mae_gru, mae.mae_dep, mae.mae_tpue,  TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape, mae_igsssn, TRIM(mae.mae_nomch) AS mae_nomch, mae_fpago, 
                        TRIM(gru_nom) AS gru_nom, TRIM(dep_nom) AS dep_nom, mae.mae_fpago, TRIM(tpu.tpu_nom) AS tpu_nom 
                        FROM nomper np
                        LEFT JOIN maestro mae ON np.np_emp=mae.mae_emp AND np.np_cod = mae.mae_cod 
                        LEFT JOIN tpuesto tpu ON mae.mae_tpue = tpu.tpu_cod 
                        LEFT JOIN grupo gru ON np.np_emp=gru.gru_emp AND np.np_gru = gru.gru_cod 
                        LEFT JOIN dep dep ON np.np_emp=dep.dep_emp AND np.np_dep = dep.dep_cod 
                        WHERE np_emp='$empCod' AND np_numpla='$numNum' and mae.mae_bodega='$selBodegapro' and np_dep='$selDep' and mae.mae_tpue='$selPuesto' ORDER BY np_dep, mae.mae_tpue,mae.mae_nom,mae.mae_ape";
        }       
    }       
}

$sBancos = "SELECT ban_ctac,ban_corr FROM bancos WHERE ban_emp='$empCod' AND ban_num='$selBan'";

$sSaldob = "SELECT * FROM saldob WHERE salb_emp='$empCod' AND salb_num='$selBan'";


function uNomper($cod,$numch) { 
    global $empCod, $usuNom, $selBan, $numNum;
    return "UPDATE nomper SET np_chafe=1, np_ban='$selBan', np_cheque='$numch' WHERE np_emp='$empCod' AND np_numpla='$numNum' AND np_cod='$cod'";
}

function uBancos($nuevocorr) { 
    global $empCod, $usuNom, $selBan;
    return "UPDATE bancos SET ban_corr=$nuevocorr WHERE ban_emp='$empCod' AND ban_num='$selBan'";
}

function sCheques($numch) { 
    global $empCod, $usuNom, $selBan, $tipop, $dtFec;
    return "SELECT * FROM cheques WHERE ch_emp='$empCod' AND ch_tipo='$tipop' AND ch_ban='$selBan' AND ch_num='$numch'";
}

function iCheques($numch,$tot,$bene,$concepto) { 
    global $empCod, $usuNom, $selBan, $tipop, $dtFec, $numNum;
    return "INSERT INTO cheques(ch_emp, ch_tipo, ch_ban, ch_num, ch_fec, ch_tot, ch_tcam, ch_bene, ch_con, ch_nego, ch_ulcam) VALUES ('$empCod','$tipop','$selBan','$numch','$dtFec','$tot',1.00,'$bene','$concepto','N','$usuNom')";
}

function iMovAcu($numch,$tot,$bene,$concepto) { 
    global $empCod, $usuNom, $selBan, $tipop, $dtFec;
    return "INSERT INTO mov_acu(ma_emp, ma_tipo, ma_ban, ma_num, ma_fec, ma_haber, ma_bene, ma_con, ma_stat) VALUES ('$empCod','$tipop','$selBan','$numch','$dtFec','$tot','$bene','$concepto','C')";
}

function iPolizas($numch,$tot,$concepto) { 
    global $empCod, $usuNom, $selBan, $tipop, $dtFec, $dtGrabado;
    return "INSERT INTO polizas(pol_emp, pol_fec, pol_ban, pol_tipo, pol_num, pol_con, pol_ulcam, pol_grabado,pol_stat,pol_tipoi,pol_nit) VALUES ('$empCod','$dtFec','$selBan','$tipop','$numch','$concepto','$usuNom','$dtGrabado','','','')";
}

function iPoli_det($numch,$cta,$ctanom,$debe,$haber) { 
    global $empCod, $usuNom, $selBan, $tipop, $dtFec, $dtGrabado;
    return "INSERT INTO poli_det (pold_emp, pold_fec, pold_ban, pold_tipo, pold_num, pold_cta, pold_ctan, pold_debe, pold_haber,pold_stat,pold_tipoi,pold_nit) VALUES ('$empCod','$dtFec','$selBan','$tipop','$numch','$cta','$ctanom','$debe','$haber','','','')";
}

function uSaldob($nvalor) { 
    global $empCod, $usuNom, $selBan;
    return "UPDATE saldob SET salb_saldo=$nvalor WHERE salb_emp='$empCod' AND salb_num='$selBan'";
}

function iSaldob($nvalor) { 
    global $empCod, $usuNom, $selBan;
    return "INSERT INTO saldob(salb_emp, salb_num, salb_saldo) VALUES('$empCod','$selBan',$nvalor)";
}

function sCuentas($cta) { 
    global $empCod;
    return "SELECT * FROM cuentas WHERE cta_emp='$empCod' AND cta='$cta'";
}

function sCierres($cta, $numAnio, $numMes) { 
    global $empCod;
    return "SELECT * FROM cierres WHERE cie_emp='$empCod' AND cie_ano=$numAnio AND cie_mes=$numMes AND cie_cta='$cta'";
}

function uCierres($cta, $numAnio, $numMes, $mdebe, $mhaber) { 
    global $empCod, $usuNom;
    return "UPDATE cierres SET cie_mdebe=$mdebe, cie_mhaber=$mhaber, cie_ulcam='$usuNom' WHERE cie_emp='$empCod' AND cie_ano=$numAnio AND cie_mes=$numMes AND cie_cta='$cta'";
}

function iCierres($cta, $numAnio, $numMes, $mdebe, $mhaber) { 
    global $empCod, $usuNom;
    return "INSERT INTO cierres(cie_emp, cie_ano, cie_mes, cie_cta, cie_mdebe, cie_mhaber, cie_ulcam) VALUES('$empCod',$numAnio,$numMes,'$cta',$mdebe,$mhaber,'$usuNom')";      
}

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

$select["bancos"] = $db->query($sBancos);
$fetchAssoc["bancos"] = $db->fetchAssoc($select["bancos"]);     
$ctaBanco = trim($fetchAssoc["bancos"]["ban_ctac"]);
$numch = $fetchAssoc["bancos"]["ban_corr"];
$numchini=$numch;
$todossn='S';
$nogenerados="";
$totpag=0;

$func = new functions();

/** 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';

$iIni=0;
$objPHPExcel = new PHPExcel();

# CREAR HOJAS
for ($i=0; $i < 1; $i++) { 
    $objPHPExcel->createSheet();
}

# HOJAS
$sheet['planilla']=0;

# ESTABLECER PROPIEDADES DEL DOCUMENTO
$objPHPExcel->getProperties()->setCreator("Asesoría en Sistemas, S.A.")
                        ->setLastModifiedBy("Asesoría en Sistemas, S.A.")
                        ->setTitle("Office 2007 XLSX Test Document")
                        ->setSubject("Office 2007 XLSX Test Document")
                        ->setDescription("")
                        ->setKeywords("office 2007 openxml php")
                        ->setCategory("Contabilidad General");

# ENCABEZADO PRIMER HOJA
$i=$iIni;
$j=7;
$objPHPExcel->setActiveSheetIndex($sheet['planilla'])
            ->setCellValueByColumnAndRow($i, 2, TRIM($empNom))
            ->setCellValueByColumnAndRow($i, 3, "LISTADO DE CHEQUES EMITIDOS CON FECHA: ".strftime("%d/%m/%Y", strtotime($dtFec)))

            ->setCellValueByColumnAndRow($i, 4, "PLANILLA No. : ".$numNum)
            ->setCellValueByColumnAndRow($i, 5, "CUENTA BANCARIA: ".$Banctab)

            ->setCellValueByColumnAndRow($i++, $j, 'CODIGO')
            ->setCellValueByColumnAndRow($i++, $j, 'NOMBRE PARA EL CHEQUE')
            ->setCellValueByColumnAndRow($i++, $j, 'NUMERO/CHEQUE')
            ->setCellValueByColumnAndRow($i++, $j, 'MONTO');

# ESTILO ENCABEZADO PRIMERA HOJA
$styEmp = array(
    'font' => array(
        'name' => 'Arial',
        'bold' => true,
        'size' => 14,
        //'color' => array('rgb' => 'FFFFFF')
        'color' => array('rgb' => '16365C')

    )
);
$styEmp1 = array(
    'font' => array(
        'name' => 'Arial',
        'bold' => true,
        'size' => 14,
        //'color' => array('rgb' => 'FFFFFF')
        'color' => array('rgb' => '16365C')

    )    
);


$styFec = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    )
);

$styEncabezado = array(
    'font' => array(
        'name' => 'Arial',
        'bold' => true,
        'size' => 12,
        'name' => 'Arial',
        //'color' => array('rgb' => 'FFFFFF')
        'color' => array('rgb' => '16365C')

    ),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        )
    ),
    'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E6B8B7') ),
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'c3c3c3') )
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'wrap' => true,
        )    
);

$styTotales = array(
    'font' => array(
        'bold' => true,
        'size' => 12,
        'color' => array('rgb' => 'FFFFFF')
        //'color' => array('rgb' => '001a57')

    ),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        )
    ),
    'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '354D73') )
    //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'c3c3c3') )
);

$styLineas = array(
    'font' => array(
        'name' => 'Arial',
        'bold' => false,
        'size' => 12,
    ),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
        )
    )
);
//foreach(range('A','I') as $colID) { 
//    $objPHPExcel->getActiveSheet()->getColumnDimension($colID)->setAutoSize(true); 
//}

$objPHPExcel->getActiveSheet()->getStyle('A2:D5')->applyFromArray($styEmp1);
$objPHPExcel->getActiveSheet()->getStyle('A7:D7')->applyFromArray($styEncabezado);

$select["nomper"] = $db->query($sNomper);

$select["bancos"] = $db->query($sBancos);
$fetchAssoc["bancos"] = $db->fetchAssoc($select["bancos"]);     
$ctaBanco = trim($fetchAssoc["bancos"]["ban_ctac"]);
$numch = $fetchAssoc["bancos"]["ban_corr"];
$numchini=$numch;
$todossn='S';
$nogenerados="";
$totpag=0;

$t_lini=1;
$j=8;
$inidep=8;
$nocorr=1;
$func = new functions();
while ($rec = $db->fetchAssoc($select["nomper"])) {
    if ($crTrepo=='S' && $rec["mae_igsssn"] =='N') {
        continue;
    }
    if ($crTrepo=='N' && $rec["mae_igsssn"] =='S') {
        continue;
    }
    if ($rec["mae_fpago"]<3 || $rec["mae_fpago"]>4) {
        continue;
    }
    $otrosing=($rec["np_bonii"]+$rec["np_bonoe"]+$rec["np_depre"]+$rec["np_viati"]+$rec["np_comi"]+$rec["np_otrp"]);
    $devengado=($rec["np_sord"]+$rec["np_boni"]+$rec["np_sferi"]+$rec["np_sexts"]+$rec["np_sextd"]+$rec["np_ssept"]+$rec["np_bonii"]+$rec["np_bonoe"]+$rec["np_depre"]+$rec["np_viati"]+$rec["np_comi"]+$rec["np_otrp"]);
    $otrosdesc=($rec["np_isr"]+$rec["np_anti"]+$rec["np_unif"]+$rec["np_lent"]+$rec["np_preb"]+$rec["np_pree"]+$rec["np_desj"]+$rec["np_otrd"]);
    $totdesc=($rec["np_igss"]+$rec["np_isr"]+$rec["np_anti"]+$rec["np_unif"]+$rec["np_lent"]+$rec["np_preb"]+$rec["np_pree"]+$rec["np_desj"]+$rec["np_otrd"]);
    $liquido=($devengado-$totdesc);

    $cod=$rec["np_cod"];
    $nomch=$rec["mae_nomch"];
    if ($nomch=="") {
        $nomch=$rec["mae_nom"]." ".$rec["mae_ape"];
    }
    $tot=$liquido;
    if ($tot == 0){
        continue;
    }
    $concepto="Pago de salarios correspondiente del ".strftime("%d/%m/%Y", strtotime($rec["np_fini"]))." al ".strftime("%d/%m/%Y", strtotime($rec["np_ffin"]))."  Planilla No. ".$numNum."  Empleado: ".$nomch;
    // Aqui hace la revision para generar cheques y ver si procede
    $select["cheques"] = $db->query(sCheques($numch));
    $fetchAssoc["cheques"] = $db->fetchAssoc($select["cheques"]);     
    if ($db->numRows($select["cheques"]) == 0) {
        $select["saldob"] = $db->query($sSaldob);
        if ($db->numRows($select["saldob"]) == 0) {
            $vSaldo = 0;
            $nvalor = ($vSaldo - $tot);
            $db->query(iSaldob($nvalor));
        } else {
            $fetchAssoc["saldob"] = $db->fetchAssoc($select["saldob"]);     
            $vSaldo = $fetchAssoc["saldob"]["salb_saldo"];
            $nvalor = ($vSaldo) - $tot;
            $db->query(uSaldob($nvalor));
        }       
        $db->query(iCheques($numch,$tot,$nomch,$concepto));
        $db->query(iMovAcu($numch,$tot,$nomch,$concepto));

        $db->query(iPolizas($numch,$tot,$concepto));
        $db->query(uNomper($cod,$numch));

        // Para el detalle de la cuenta de sueldos
        $select["cuentas"] = $db->query(sCuentas($ctasuel));
        $fetchAssoc["cuentas"] = $db->fetchAssoc($select["cuentas"]);     
        if ($db->numRows($select["cuentas"]) == 0) {
            $ctanom="Cuenta No definida";
        } else {
            $ctanom=$fetchAssoc["cuentas"]["cta_nom"];
        }
        $debe=$tot;
        $haber=0;

        $db->query(iPoli_det($numch,$ctasuel,$ctanom,$debe,$haber));
        $cta = $ctasuel;
        while (!empty($cta)) {
            $select["cuentas"] = $db->query(sCuentas($cta));
            $select["cierres"] = $db->query(sCierres($cta, $numAnio, $numMes));

            $fetchAssoc["cuentas"] = $db->fetchAssoc($select["cuentas"]);
            $ctaActa = trim($fetchAssoc["cuentas"]["cta_acta"]);

            if ($db->numRows($select["cierres"]) > 0) {
                $fetchAssoc["cierres"] = $db->fetchAssoc($select["cierres"]);
                $mdebe=$fetchAssoc["cierres"]["cie_mdebe"]+$debe;
                $mhaber=$fetchAssoc["cierres"]["cie_mhaber"]+$haber;
                $db->query(uCierres($cta, $numAnio, $numMes, $mdebe, $mhaber));
            } else {
                $mdebe=$debe;
                $mhaber=$haber;
                $db->query(iCierres($cta, $numAnio, $numMes, $mdebe, $mhaber));
            }
            $cta = $ctaActa;
        }
        // Aqui termina el proceso para actualizar la cuenta de sueldos


        // Para el detalle de la cuenta del banco
        $select["cuentas"] = $db->query(sCuentas($ctaBanco));
        $fetchAssoc["cuentas"] = $db->fetchAssoc($select["cuentas"]);     
        if ($db->numRows($select["cuentas"]) == 0) {
            $ctanom="Cuenta No definida";
        } else {
            $ctanom=$fetchAssoc["cuentas"]["cta_nom"];;
        }
        $debe=0;
        $haber=$tot;
        $db->query(iPoli_det($numch,$ctaBanco,$ctanom,$debe,$haber));
        $cta = $ctaBanco;
        while (!empty($cta)) {
            $select["cuentas"] = $db->query(sCuentas($cta));
            $select["cierres"] = $db->query(sCierres($cta, $numAnio, $numMes));

            $fetchAssoc["cuentas"] = $db->fetchAssoc($select["cuentas"]);
            $ctaActa = trim($fetchAssoc["cuentas"]["cta_acta"]);

            if ($db->numRows($select["cierres"]) > 0) {
                $fetchAssoc["cierres"] = $db->fetchAssoc($select["cierres"]);
                $mdebe=$fetchAssoc["cierres"]["cie_mdebe"]+$debe;
                $mhaber=$fetchAssoc["cierres"]["cie_mhaber"]+$haber;
                $db->query(uCierres($cta, $numAnio, $numMes, $mdebe, $mhaber));
            } else {
                $mdebe=$debe;
                $mhaber=$haber;
                $db->query(iCierres($cta, $numAnio, $numMes, $mdebe, $mhaber));
            }
            $cta = $ctaActa;
        }
        // Aqui termina el proceso para actualizar la cuenta de bancos

        $i=$iIni;
        $objPHPExcel->getActiveSheet()
                    ->setCellValueByColumnAndRow($i++, $j, $rec["np_cod"])
                    ->setCellValueByColumnAndRow($i++, $j, trim(utf8_decode($rec["mae_nomch"])))
                    ->setCellValueByColumnAndRow($i++, $j, $numch)
                    ->setCellValueByColumnAndRow($i++, $j, $rec["np_liqrec"]); 
        $objPHPExcel->getActiveSheet()->getStyle('A'.$j.':D'.$j)->applyFromArray($styLineas);
        $j+=1;

        $db->iEx($func->iBitacora(utf8_decode("Se creó Cheque No.: $numch  Banco: $selBan Fecha: $dtFec Usuario: ").$usuNom, $bitMod, $bitForm));

        $totpag+=$tot;
        $numch+=1;
    } else {
        $todossn='N';
        $nogenerados=$nogenerados.$rec["np_cod"].", ";
    }
}
$i=$iIni;

$i=$iIni;
if ($numch>$numchini) {
    $numchfin=$numch-1;
    $objPHPExcel->getActiveSheet()
        ->setCellValueByColumnAndRow($i++, $j, "")
        ->setCellValueByColumnAndRow($i++, $j, "                          TOTAL ")
        ->setCellValueByColumnAndRow($i++, $j, "") 
        ->setCellValueByColumnAndRow($i++, $j, '=SUM(D8:D'.($j-1).')'); 
    $objPHPExcel->getActiveSheet()->getStyle('A'.$j.':D'.$j)->applyFromArray($styTotales);
} else {
    $numchfin=$numch;
    $objPHPExcel->getActiveSheet()
                ->setCellValueByColumnAndRow($i++, $j, "")
                ->setCellValueByColumnAndRow($i++, $j, "**** NO SE ENCONTRARON CHEQUES PARA IMPRIMIR EN ESTA PLANILLA ****")
                ->setCellValueByColumnAndRow($i++, $j, "")
                ->setCellValueByColumnAndRow($i++, $j, ""); 
    $objPHPExcel->getActiveSheet()->getStyle('A'.$j.':D'.$j)->applyFromArray($styLineas);
}
if ($todossn=='N') {
    $objPHPExcel->getActiveSheet()
                ->setCellValueByColumnAndRow($i++, $j, "")
                ->setCellValueByColumnAndRow($i++, $j, "CHEQUES NO GENERADOS, CODIGO(S): ".$nogenerados)
                ->setCellValueByColumnAndRow($i++, $j, "")
                ->setCellValueByColumnAndRow($i++, $j, ""); 
    $objPHPExcel->getActiveSheet()->getStyle('A'.$j.':D'.$j)->applyFromArray($styLineas);
}
// Para cambiar el correlativo de cheques
$db->query(uBancos($numch));

$db->close();

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(80);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);

$objPHPExcel->getActiveSheet()->getStyle('C8'.':C'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('D8'.':D'.$j)->getNumberFormat()->setFormatCode('###,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('A8'.':B'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

# ESTABLECER TITULOS
$objPHPExcel->setActiveSheetIndex($sheet['planilla'])->setTitle('Listado Cheques');

# AL ABRIR EL DOCUMENTO ABRIR EN PRIMERA HOJA
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Listado Cheques.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;
?>