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

$empCod=utf8_decode($_SESSION["empCod"]);
$empNom=utf8_decode($_SESSION["empNom"]);
$empNomc=utf8_decode($_SESSION["empNomc"]);
$pTcam=$_SESSION["pTcam"];
$empNit=utf8_decode($_SESSION['empNit']);

$selMes=$_GET["selMes"];
$selMesNom=$_GET["selMesNom"];
$numAno=$_GET["numAno"];


# QUERYS
$sIva_comp = "SELECT *,
                TRIM(ic.iva_serie) AS iva_serie,
                TRIM(ic.iva_nit) AS iva_nit,
                TRIM(ic.iva_prov) AS iva_prov
            FROM iva_comp ic LEFT JOIN tipoc tc ON ic.iva_tipo=tc.tc_cod 
            WHERE ic.iva_emp='$empCod' AND year(ic.iva_rfec)=$numAno AND month(ic.iva_rfec)=$selMes AND ic.iva_afe='S' 
            ORDER BY ic.iva_emp,ic.iva_fec";

$sIva_ven = "SELECT *,
                TRIM(iv.ivav_serie) AS ivav_serie,
                TRIM(iv.ivav_nit) AS ivav_nit,
                TRIM(iv.ivav_clin) AS ivav_clin
            FROM iva_ven iv LEFT JOIN tipov tv ON iv.ivav_tipo=tv.tv_cod 
            WHERE iv.ivav_emp='$empCod' AND year(iv.ivav_fec)=$numAno AND month(iv.ivav_fec)=$selMes AND iv.ivav_afe='S' 
            ORDER BY iv.ivav_emp,iv.ivav_fec";

# EJECUCION DE QUERYS
$db = new dbExec();

/** 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;
$jIni=0;
$objPHPExcel = new PHPExcel();

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

$bordersThin = array(
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
        )
    )
);

$borderBottomDouble = array(
    'borders' => array(
        'bottom' => array(
            'style' => PHPExcel_Style_Border::BORDER_DOUBLE
        )
    )
);

$borderTopThin = array(
    'borders' => array(
        'top' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
        )
    )
);

# HOJAS
$sheet['asiste']=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("Iva");

# ENCABEZADO PRIMER HOJA
$i=$iIni;
$j=$jIni+1;
$objPHPExcel->setActiveSheetIndex($sheet['asiste']);

# CREA EL CICLO PARA EL LISTADO DE MOVIMIENTOS DE COMPRAS
$item=1;
$select["iva_comp"] = $db->query($sIva_comp);
while ($rec = $db->fetchAssoc($select['iva_comp'])) {
    $i=$iIni;

    $t1 = $item;
    $t2 = 'C';
    $t3 = trim($rec["tc_abrev"]);
    $t4 = trim($rec["iva_serie"]);
    $t5 = $rec["iva_num"];
    $t6 = $rec["iva_fec"];
    $t7 = str_replace("-", "", trim($rec["iva_nit"]));
    $t7 = str_replace(".", "", trim($t7));
    $t7 = str_replace("/", "", trim($t7));
    if ($t7 == '') {
        $t7 = 'CF';
    }        
    $t8 = trim($rec["iva_prov"]);
    if ($rec["iva_imp"] == 0.00 && $rec["iva_impre"] == 0.00) {
        $t9 = 'L';
    } else {
        $t9 = 'I';
    }
    $t10 = '';
    if ($rec["iva_tot"] == 0.00 && $rec["iva_iva"] == 0.00 && $rec["iva_sub"] == 0.00) {
        $t11 = 'A';
    } else {
        $t11 = ' ';
    }
    $t12 = '';
    $t13 = 0;
    $t14 = '';
    $t15 = '';
    if (trim($rec["tc_abrev"]) != 'FPC') {
      if (($rec["iva_bien"]+$rec["iva_medi"]+$rec["iva_vehi"]+$rec["iva_acti"]+$rec["iva_comb"]) != 0.00) {
        $t16 = ($rec["iva_bien"]+$rec["iva_medi"]+$rec["iva_vehi"]+$rec["iva_acti"]+$rec["iva_comb"]);
        } else {
        $t16 = 0.00;
      }
      } else {
      $t16 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      if (($rec["iva_imp"]+$rec["iva_impre"]) != 0.00) {
        $t17 = ($rec["iva_tot"]-($rec["iva_serv"]+$rec["iva_bien"]));
        } else {
        $t17 = 0.00;
      }
      } else {
      $t17 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      if ($rec["iva_serv"] != 0.00) {
        $t18 = $rec["iva_serv"];
        } else {
        $t18 = 0.00;
      }
      } else {
      $t18 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      $t19 = '0';
      } else {
      $t19 = '';
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      if ($rec["iva_noafe"] != 0.00) {
        $t20 = $rec["iva_noafe"];
        } else {
        $t20 = 0.00;
      }
      } else {
      $t20 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      $t21 = 0.00;
      } else {
      $t21 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      $t22 = 0.00;
      } else {
      $t22 = 0.00;
    }
    if (trim($rec["tc_abrev"]) != 'FPC') {
      $t23 = 0.00;
      } else {
      $t23 = 0.00;
    }
    $t24 = '';
    $t25 = 0;
    $t26 = 0.00;
    $t27 = 0.00;
    if (trim($rec["iva_peque"]) != 0.00) {
      $t28 = $rec["iva_peque"];
      } else {
      $t28 = 0.00;
    }
    $t29 = 0.00;
    $t30 = 0.00;
    if ($rec["iva_iva"] != 0.00) {
      if (trim($rec["tc_abrev"]) == 'NC') {
        $t31 = $rec["iva_iva"];
        } else {
        $t31 = $rec["iva_iva"];
      }
      } else {
      $t31 = 0.00;
    }
    if ($rec["iva_tot"] != 0.00) {
      if (trim($rec["tc_abrev"]) == 'NC') {
        $t32 = $rec["iva_tot"];
        } else {
        $t32 = $rec["iva_tot"];
      }
      } else {
      $t32 = '';
    }


    $objPHPExcel->getActiveSheet()
    ->setCellValueByColumnAndRow($i++,  $j, $t1)    
    ->setCellValueByColumnAndRow($i++,  $j, $t2)
    ->setCellValueByColumnAndRow($i++,  $j, $t3)
    ->setCellValueByColumnAndRow($i++,  $j, $t4)
    ->setCellValueByColumnAndRow($i++,  $j, $t5)
    ->setCellValueByColumnAndRow($i++,  $j, strftime("%d/%m/%Y", strtotime($t6)))
    ->setCellValueByColumnAndRow($i++,  $j, trim($t7))
    ->setCellValueByColumnAndRow($i++,  $j, trim(utf8_encode($t8)))
    ->setCellValueByColumnAndRow($i++,  $j, $t9)
    ->setCellValueByColumnAndRow($i++,  $j, $t10)
    ->setCellValueByColumnAndRow($i++,  $j, $t11)
    ->setCellValueByColumnAndRow($i++,  $j, $t12)
    ->setCellValueByColumnAndRow($i++,  $j, $t13)
    ->setCellValueByColumnAndRow($i++,  $j, $t14)
    ->setCellValueByColumnAndRow($i++,  $j, $t15)
    ->setCellValueByColumnAndRow($i++,  $j, $t16)
    ->setCellValueByColumnAndRow($i++,  $j, $t17)
    ->setCellValueByColumnAndRow($i++,  $j, $t18)
    ->setCellValueByColumnAndRow($i++,  $j, $t19)
    ->setCellValueByColumnAndRow($i++,  $j, $t20)
    ->setCellValueByColumnAndRow($i++,  $j, $t21)
    ->setCellValueByColumnAndRow($i++,  $j, $t22)
    ->setCellValueByColumnAndRow($i++,  $j, $t23)
    ->setCellValueByColumnAndRow($i++,  $j, $t24)
    ->setCellValueByColumnAndRow($i++,  $j, $t25)
    ->setCellValueByColumnAndRow($i++,  $j, $t26)
    ->setCellValueByColumnAndRow($i++,  $j, $t27)
    ->setCellValueByColumnAndRow($i++,  $j, $t28)
    ->setCellValueByColumnAndRow($i++,  $j, $t29)
    ->setCellValueByColumnAndRow($i++,  $j, $t30)
    ->setCellValueByColumnAndRow($i++,  $j, $t31)
    ->setCellValueByColumnAndRow($i++,  $j, $t32);
    $j++;
    $item += 1;
}


# CREA EL CICLO PARA EL LISTADO DE MOVIMIENTOS DE VENTAS
$select["iva_ven"] = $db->query($sIva_ven);
while ($rec = $db->fetchAssoc($select['iva_ven'])) {
    $i=$iIni;

    $t1 = $item;
    $t2 = 'V';
    $t3 = trim($rec["tv_codasiste"]);
    $t4 = trim($rec["ivav_serie"]);
    $t5 = $rec["ivav_num"];
    $t6 = $rec["ivav_fec"];
    $t7 = str_replace("-", "", trim($rec["ivav_nit"]));
    $t7 = str_replace(".", "", trim($t7));
    $t7 = str_replace("/", "", trim($t7));
    if ($t7 == '') {
        $t7 = 'CF';
    }        
    $t8 = trim($rec["ivav_clin"]);
    if ($rec["ivav_exp"] == 0.00 && $rec["ivav_expre"] == 0.00) {
        $t9 = 'L';
    } else {
        $t9 = 'E';
    }
    if ($rec["ivav_exp"] != 0.00 || $rec["ivav_expre"] != 0.00) {
        $t10 = 'BIEN';
    } else {
        $t10 = '';
    }
    if ($rec["ivav_tot"] == 0.00 && $rec["ivav_iva"] == 0.00 && $rec["ivav_sub"] == 0.00) {
        $t11 = 'A';
    } else {
        $t11 = 'E';
    }
    if ($t11 == 'A') {
       $t7 = 'CF';
    }   
    $t12 = '';
    $t13 = 0;
    $t14 = '';
    $t15 = '';
    if (($rec["ivav_bien"]+$rec["ivav_medi"]+$rec["ivav_vehi"]+$rec["ivav_comb"]) != 0.00) {
        $t16 = ($rec["ivav_bien"]+$rec["ivav_medi"]+$rec["ivav_vehi"]+$rec["ivav_comb"]);
        } else {
        $t16 = 0.00;
    }
    if ($t11 == 'A') {
        $t17 = 0.00;
    } else {
        $t17 = 0.00;
    }
    if ($rec["ivav_serv"] != 0.00) {
        $t18 = $rec["ivav_serv"];
    } else {
        $t18 = 0.00;
    }
    $t19 = '0';
    if ($rec["ivav_noafe"]+$rec["ivav_exen"] != 0.00) {
        $t20 = $rec["ivav_noafe"]+$rec["ivav_exen"];
    } else {
        $t20 = 0.00;
    }
    if ($rec["ivav_exp"]+$rec["ivav_expre"] != 0.00) {
        $t21 = $rec["ivav_exp"]+$rec["ivav_expre"];
    } else {
        $t21 = 0.00;
    }
    $t22 = 0.00;
    $t23 = 0.00;
    $t24 = '';
    $t25 = 0;
    $t26 = 0.00;
    $t27 = 0.00;
    $t28 = 0.00;
    $t29 = 0.00;
    $t30 = 0.00;
    if ($rec["ivav_iva"] != 0.00) {
      if (trim($rec["tv_codasiste"]) == 'NC') {
        $t31 = $rec["ivav_iva"];
        } else {
        $t31 = $rec["ivav_iva"];
      }
      } else {
      $t31 = 0.00;
    }
    if ($rec["ivav_tot"] != 0.00) {
      if (trim($rec["tv_codasiste"]) == 'NC') {
        $t32 = $rec["ivav_tot"];
        } else {
        $t32 = $rec["ivav_tot"];
      }
      } else {
      $t32 = '';
    }


    $objPHPExcel->getActiveSheet()
    ->setCellValueByColumnAndRow($i++,  $j, $t1)    
    ->setCellValueByColumnAndRow($i++,  $j, $t2)
    ->setCellValueByColumnAndRow($i++,  $j, $t3)
    ->setCellValueByColumnAndRow($i++,  $j, $t4)
    ->setCellValueByColumnAndRow($i++,  $j, $t5)
    ->setCellValueByColumnAndRow($i++,  $j, strftime("%d/%m/%Y", strtotime($t6)))
    ->setCellValueByColumnAndRow($i++,  $j, trim($t7))
    ->setCellValueByColumnAndRow($i++,  $j, trim(utf8_encode($t8)))
    ->setCellValueByColumnAndRow($i++,  $j, $t9)
    ->setCellValueByColumnAndRow($i++,  $j, $t10)
    ->setCellValueByColumnAndRow($i++,  $j, $t11)
    ->setCellValueByColumnAndRow($i++,  $j, $t12)
    ->setCellValueByColumnAndRow($i++,  $j, $t13)
    ->setCellValueByColumnAndRow($i++,  $j, $t14)
    ->setCellValueByColumnAndRow($i++,  $j, $t15)
    ->setCellValueByColumnAndRow($i++,  $j, $t16)
    ->setCellValueByColumnAndRow($i++,  $j, $t17)
    ->setCellValueByColumnAndRow($i++,  $j, $t18)
    ->setCellValueByColumnAndRow($i++,  $j, $t19)
    ->setCellValueByColumnAndRow($i++,  $j, $t20)
    ->setCellValueByColumnAndRow($i++,  $j, $t21)
    ->setCellValueByColumnAndRow($i++,  $j, $t22)
    ->setCellValueByColumnAndRow($i++,  $j, $t23)
    ->setCellValueByColumnAndRow($i++,  $j, $t24)
    ->setCellValueByColumnAndRow($i++,  $j, $t25)
    ->setCellValueByColumnAndRow($i++,  $j, $t26)
    ->setCellValueByColumnAndRow($i++,  $j, $t27)
    ->setCellValueByColumnAndRow($i++,  $j, $t28)
    ->setCellValueByColumnAndRow($i++,  $j, $t29)
    ->setCellValueByColumnAndRow($i++,  $j, $t30)
    ->setCellValueByColumnAndRow($i++,  $j, $t31)
    ->setCellValueByColumnAndRow($i++,  $j, $t32);
    $j++;
    $item += 1;
}


# ESTILO HOJA
$xCenter = array(
    'font' => array(
        'bold' => true,
    ),
	'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    )
);


$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(14);

# FORMATOS
$objPHPExcel->getActiveSheet()->getStyle('A5'.':A'.$j)->getNumberFormat()->setFormatCode('##0');
$objPHPExcel->getActiveSheet()->getStyle('B5'.':D'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('E5'.':E'.$j)->getNumberFormat()->setFormatCode('##0');
$objPHPExcel->getActiveSheet()->getStyle('F5'.':L'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('M5'.':M'.$j)->getNumberFormat()->setFormatCode('##0');
$objPHPExcel->getActiveSheet()->getStyle('N5'.':O'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('P5'.':W'.$j)->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('X5'.':X'.$j)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('Y5'.':AF'.$j)->getNumberFormat()->setFormatCode('#,##0.00');

# ESTABLECER TITULOS
$objPHPExcel->setActiveSheetIndex($sheet['asiste'])->setTitle('Asiste Libros');

# 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="Asiste Libros.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;
?>