<?php
/*
 * This file is part of FacturaSctipts
 * Copyright (C) 2014-2015  Carlos Garcia Gomez  neorazorx@gmail.com
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

require_model('almacen.php');
require_model('agentes.php');
require_model('fabricante.php');
//require_once 'extras/excel/PHPExcel.php';
require_once 'plugins/liquifactura/extras/excel/PHPExcel.php';
require_once 'plugins/facturacion_base/controller/informe_albaranes.php';
require_once 'plugins/facturacion_base/extras/fbase_controller.php';

class resumenfac extends fbase_controller /* fs_controller*/
{
public $var1 = "xxx" ;
public $data;
public $data2;
public $order;
public $arreglodata = array();    
public $almacenes;
public $periodos;
public $b_codalmacen;
public $b_periodo;   
public $agente;
public $agentev;
public $agentenom;
public $resultadoven;
public $resultado;
public $b_codfabricante;
public $fabricante;
public $referencia;
public $nfabricante;
public $fecini;
public $fecfin;
public $estado;
public $datagente ;

	public function __construct()
   {
      parent::__construct('resumenfac', 'Rasumen Ventas', 'Formularios EC');
   }
   
   protected function private_core()
   {
      $almacen = new almacen();
      $this->almacenes = $almacen->all();
      $this->articulo = new articulo();
      $this->stock = new stock();
      $this->url_recarga = FALSE;
      $this->agentecod ='';
      $this->estado = '';
      $this->almacenes = new almacen();
      $this->tfamilia = new familia();
      $this->b_subfamilias = FALSE;

      $this->b_codfamilia = '';

        if (isset($_REQUEST['b_codfamilia'])) {
            $this->b_codfamilia = $_REQUEST['b_codfamilia'];
            if ($_REQUEST['b_codfamilia']) {
                $this->b_subfamilias = isset($_REQUEST['b_subfamilias']);
            }
        } 


      if (isset($_POST['estado'])){
          $this->estado = $_POST['estado'];
      }
          if (isset($_REQUEST['codagente2'])) {
             if (($_POST['codagente2']) ) {
             }
             $this->agentecod = $_POST['codagentev']; 
          }else{
                $this->agente = $this->user->get_agente();
          }

          if (isset($_REQUEST['codagente4'])) {
             if (($_POST['codagente4']) ) {
  
             }
             //$this->agentecod = $_POST['codagentev']; 
          }else{
                $this->agente = $this->user->get_agente();
          }     

          if (isset($_POST['codagente'])) {
             $datagente = new agente();
             $this->agente = $datagente->get($_POST['codagente']);
             $this->agentecod = $_POST['codagente']; //$this->agente->codagente;
             if ($this->agentecod == ''){
                 $this->agentecod = 14;
             }
             //$this->new_error_msg("Agente zz ".$this->agentecod."  codagente->".$_POST['codagente']);
          } else {
             $this->agente = $this->user->get_agente();
             $this->agentecod = 14;
            // $this->new_error_msg("Agente aa ".$agentecod);
          }

      	$this->referencia = '';
      	if (isset($_POST['referencia'])) {
         	$this->referencia = $_POST['referencia'];
      	} else if (isset($_GET['ref'])) {
         	$this->referencia = $_GET['ref'];
      	}            
      
                  
            
        $this->fabricante = new fabricante();	
        
        if (isset($_REQUEST['buscar_referencia'])) {
            $this->buscar_referencia();
        }
/*      	$this->b_codfabricante = '';
      	if( isset($_REQUEST['b_codfabricante']) ){
      		$this->b_codfabricante = $_REQUEST['b_codfabricante'];
         	$codfab = $this->b_codfabricante;
         	if (($_POST['b_codfabricante']) ) {
         		$nombrefabric = $this->db->select("select nombre from fabricantes where codfabricante=".$this->b_codfabricante.";");
         
         		if ($nombrefabric){
            		foreach($nombrefabric as $row){
                	$this->nfabricante = ($row['nombre']);
            	}
            	}
        	}
      	}*/
      	if( isset($_GET['table']) )
      	{
         	$this->export_structure_xml($_GET['table']);
      	}
      	else if( isset($_POST['fechaini2']) )
      		{
                $this->genera_rep();
      		}
      		else if( isset($_POST['fechaini1']) )
      			{
                	$this->import_xml();
      			}
      			else if( isset($_POST['fechaini4']) )
      				{
         				$this->beneficio_xml();
      				}

      				else if( isset($_POST['fechafin6']) )
      				{
         				$this->genera_regularizacion();
      				}

 	}

    
   private function genera_rep()
 { 


      $fecini =  date("y-m-d", strtotime($_POST['fechaini2']));
      $fecfin =  date("y-m-d", strtotime($_POST['fechafin2']));
      $periodo = $_POST['fechaini2']."*".$_POST['fechafin2'];

      if ($fecini > $fecfin) 
	  {
        //$this->new_error_msg("Error: Fecha Final no puede ser menor que fecha de Inicio ".$_POST['fechafin1']);  
      }else {
	      	//$this->new_message("Fecha inicio ".$fecini. "  fecha fin ".$fecfin);
	           
	      	// seleccion de todos los registros que entran en el periodo 
	      	// Con detalle de facturas de venta
	      	$wherevend = '';  
	      	if ($this->agentecod <> 0){
	      	    $wherevend = " and c.codagente = ". $this->agentecod;
	      	}else{
	      	    //$agentecod = $this->agente->codagente;    
	      	}
		  //  $this->new_message("SQL ".$wherevend);


         	$resultado = $this->db->select("SELECT codigo, nombrecliente, total, anulada, pagada, codpago, "
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura and iva=15 group by idfactura) neto15,"
			."(select sum(totaliva) from lineasivafactcli where idfactura=c.idfactura and iva=15  group by idfactura) iva15," 
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura  and iva=0  group by idfactura) neto0, "
			
                 . "FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . " order by codpago, nombrecliente, total ;");
                 
                 var_dump($resultado);

            // RETENCIONES        	
         	$result_reten = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, tiporeten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			$resRegReten = $this->db->select("SELECT count(*) contadoreten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			/// PAGOS NO EN EFECTIVO
         	$res_pagnoefec = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, nota "
         		. " from facturascli c, pagos p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			$resRegNoefe = $this->db->select("SELECT count(*) contadorpagos "
         		. " from facturascli c, retenciones p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total;");

         	$resRegFac = $this->db->select("SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . " order by codpago, nombrecliente, total ;");

          	$resRegFac1 ="SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . ";";


         	$resRegRbos = $this->db->select("SELECT count(*) contador1 FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."'".  " ;");     

         	$resultado_rbos = $this->db->select("SELECT nombrecliente, fechap, importe, codpago, "
         		 . "(select total from facturascli as fc where  fc.idfactura = c.idfactura) totalfac, " 
                 . "(select sum(importe) from reciboscli as rc where  rc.idfactura = c.idfactura and estado = 'Pagado'"
                 . " group by idfactura)  totalabono, "
                 . "(select codigo from facturascli as fc1 where  fc1.idfactura = c.idfactura) codigof "
                 . "FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."' "
                 . " order by codpago, nombrecliente, importe ;");     


                 
        	$resultado1 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM presupuestoscli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " ;");
             
                
       		$resultado2 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM albaranescli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " ;");                 
    
 

			$fila = 4; //Establecemos en que fila inciara a imprimir los datos    
			$gdImage = imagecreatefrompng('images/logo.png');//Logotipo
///*
			//Objeto de PHPExcel
			$objPHPExcel  = new PHPExcel();
			
			//Propiedades de Documento
			$objPHPExcel->getProperties()->setCreator("John Lima")->setDescription("Liquidacion de Facturas");
			
			//Establecemos la pesta?a activa y nombre a la pesta?a
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setTitle("facturas");
			
			$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
			$objDrawing->setName('Logotipo');
			$objDrawing->setDescription('Logotipo');
			$objDrawing->setHeight(100);
			$objDrawing->setCoordinates('A1');
			$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
			
			
			$estiloTituloReporte = array(
		    'font' => array(
			'name'      => 'Arial',
			'bold'      => true,
			'italic'    => false,
			'strike'    => false,
			'size' =>12
		    ),
		    'fill' => array(
			'type'  => PHPExcel_Style_Fill::FILL_SOLID
			),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
			
			$estiloTituloColumnas = array(
		    'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => 'FFFFFF'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => '538DD5')
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
    		),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT ,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_TOP
		    )
			);

		  $estiloTituloColumnas2 = array(
		  'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
                
		$estiloTituloColumnas3 = array(
		    'font' => array(
			'name'  => 'Calibri',
			'bold'  => false,
			'size' =>9,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);                
		                
		$estiloNumerosCol = array(
		    'font' => array(
		  'name'  => 'Calibri',
		  'bold'  => false,
		  'size' =>9,
		  'color' => array(
		  'rgb' => '000000'
		  )
		    ),
		    'fill' => array(
		  'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
		  'allborders' => array(
		  'style' => PHPExcel_Style_Border::BORDER_THIN
		  )
		    ),
		    'alignment' =>  array(
		  'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
		  'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    ),
		    'numberformat' => array(
		    'setFormatCode' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE
		    )

		  );                  
   
		/// contador de registros      
	 	if($resRegFac){
		    foreach ($resRegFac as $rows) {
		      $nregfac = $rows['contador'];
		    }
		}else{
		    $menRegistro1 = 'No hay Facturas emitidas en esta fecha ';
		}


		if($resRegReten){
		    foreach ($resRegReten as $rows) {
		      $nregReten = $rows['contadoreten'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}


		if($resRegNoefe){
		    foreach ($resRegNoefe as $rows) {
		      $nregPagos = $rows['contadorpagos'];
		    }
		}else{
		    $menRegistroP = 'No hay Pagos no Efectivo cobrados en esta fecha';
		}

		if($resRegRbos){
		    foreach ($resRegRbos as $rows) {
		      $nregrecib = $rows['contador1'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}

        
		$estiloInformacion = new PHPExcel_Style();
		$estiloInformacion->applyFromArray( array(
	    'font' => array(
		'name'  => 'Arial',
		'color' => array(
		'rgb' => '000000'
		)
	    ),
	    'fill' => array(
		'type'  => PHPExcel_Style_Fill::FILL_SOLID
		),
	    'borders' => array(
		'allborders' => array(
		'style' => PHPExcel_Style_Border::BORDER_THIN
		)
	    ),
		'alignment' =>  array(
		'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
	    )
		));
	    for ($i = 1; $i <= 51; $i++) {
	            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(14);
	    }
		$linaux = $fila + $nregfac + 5;
		$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->applyFromArray($estiloTituloReporte);
	  	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'V E N T A S');
	  	$objPHPExcel->getActiveSheet()->setCellValue('b3', "Fecha desde: 20".$fecini." hasta: 20".$fecfin);


		$objPHPExcel->getActiveSheet()->mergeCells('b1:E1');
	  	$objPHPExcel->getActiveSheet()->mergeCells('B3:I3');
	  	$objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
	        

        
        // COLUMNAS
		$objPHPExcel->getActiveSheet()->setCellValue('G1',"Fecha Reporte: ".date("Y/m/d") );

	    $nomagente = "TODOS  LOS VENDEDORES"    ;

		$objPHPExcel->getActiveSheet()->setCellValue('b1', "Vendedor:   ".$nomagente );
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(14);      
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(14);  
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14); 


		if ($resultado){
			
		     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->applyFromArray($estiloTituloColumnas2);
		     $objPHPExcel->getActiveSheet()->getStyle('A5:'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		     $objPHPExcel->getActiveSheet()->getStyle('D5:'.'H'.$linaux)->applyFromArray($estiloNumerosCol);
		     $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Nombre');
		     $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Codigo Fac');
		     $objPHPExcel->getActiveSheet()->setCellValue('D4', 'Val Fac Pag');
		     $objPHPExcel->getActiveSheet()->setCellValue('E4', 'Val Fac Impag');
		     $objPHPExcel->getActiveSheet()->setCellValue('F4', 'Neto 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('G4', 'Iva 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('H4', 'Neto 0');
		     //$objPHPExcel->getActiveSheet()->setCellValue('I4', 'Bemeficio Fac');
		     $nregis=1;
		     $codpago = '';
		     $subtotp    = 0;
		     $subtotimp  = 0;
		     $totalre = 0;
		     $totalP  = 0;
			 $totalImp = 0;


		//if ($resultado){
			foreach($resultado as $rows)	{
			    if ($rows['anulada'] == 0){
		          	if ( $nregis == 1) {
		            	$codpago = $rows['codpago'];
		          	}
		          	if ( $codpago != $rows['codpago']) {
			            $codimpre = $codpago;
			            $codpago = $rows['codpago'];
			            $fila++;
			            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
			            $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
			            $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
			            $subtotp   = 0;
			            $subtotimp = 0;
		          	}
		          	$fila++; //Sumamos 1 para pasar a la siguiente fila
			        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
				    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		          	if ($rows['pagada'] == 1){
		          		$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['total']);
		          		$subtotp  = $subtotp  + $rows['total'];
		          		$totalP   = $totalP + $rows['total'];
		          	}else{
		          		$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		          		$subtotimp  = $subtotimp  + $rows['total'];
		          		$totalImp   = $totalImp + $rows['total'];
		          	}

		          	$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['iva15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, $rows['neto0']);
		          	//$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));

		          	/*$sqlbenefic = $this->db->select('select sum(totalutilidad) totutil from lineasfacturascli where idfactura= '.$rows['idfactura']);

		          	if($sqlbenefic){
		          		foreach ($sqlbenefic as $k) {
		          			$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $k['totutil']);
		          		}
		          	}*/

		          	$codimpre1 = $rows['codpago'];
		          	$totalre = $totalre + $rows['total'];

		          	$neto15 = $neto15 + $rows['neto15'];
		          	$iva15  =$iva15  + $rows['iva15'];
		          	$neto0  = $neto0  + $rows['neto0'];
		          	

		          	$nregis++;
	           		//$this->new_message( " cliente procesado: ".$rows['nombrecliente']."   total: ".$rows['total']);
		    	}
			}
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, number_format($neto15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($iva15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, number_format($neto0,2,'.',','));
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalP,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalImp,2,'.',','));
            $fila++;	
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   TOTAL FACTURAS ");        
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalre,2,'.',','));
		}else{
		    $fila = $fila + 1 ;
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han emitido facturas en este lapaso de tiempo' );
		    $fila ++;
		}
		/////////
		
	  	if ($resultado_rbos){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->mergeCells('A'.$fila.':'.'G'.$fila);
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Abono Act');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'T. Abonos.');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Saldo');
		    $fila ++;
  			$linaux = $fila + $nregrecib + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalre = 0;
  			//if ($resultado_rbos){
  			foreach($resultado_rbos as $rows)  {
      
	          	if ( $nregis == 1) {
	            	$codpago = $rows['codpago'];
	          	}
	          	if ( $codpago != $rows['codpago']) {
		            $codimpre = $codpago;
		            $codpago = $rows['codpago'];
		            $fila++;
		            $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
		            $objPHPExcel->getActiveSheet()->setCellValue('J'.$fila, number_format($subtot,2,',','.'));
		            $subtot = 0;
	          	}  //nombrecliente, fechap, importe, codpago, totalfac totalabono
			        $fila++; //Sumamos 1 para pasar a la siguiente fila
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigof']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['totalfac']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['importe']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['totalabono']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['totalfac'] - $rows['totalabono']);
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codpago'];
		        $subtot  = $subtot  + $rows['importe'];
		        $totalre = $totalre + $rows['importe'];
		        $nregis++;
  			}
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalre,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay recibos');
    		$fila = $fila + 2 ;
    		//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han cobrado recibos en este lapaso de tiempo');
    		$fila ++;
  		}//

		////////  
        ///////    RETENCIONES   
	  	if ($result_reten){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'R E T E N C I O N E S');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Tipo Reten');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregReten + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($result_reten as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['tiporeten']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
			//$this->new_message('no hay retenciones');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado retenciones sobre las facturas del período');
    		$fila ++;
  		}

        /////    FIN DE RETENCIONES     ////////////


        /// pagos no efectivo
        if ($res_pagnoefec){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'PAGOS NO EFECTIVO');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Nota');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregPagos + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($res_pagnoefec as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['nota']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay Pagos');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado pagos NO EFECTIVO sobre facturas del período');
    		$fila ++;
  		}

  		////   fin de pagos no efectivo
/*--------------
		if ($resultado1){
	
			foreach($resultado1 as $rows)	{
		    	$objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				$fila++; 
		   	 	$nregis++;
			}
		}
			
		if($resultado2){
			foreach($resultado2 as $rows)	{
			    $objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				//$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['total']);
				$fila++;
			    $nregis++;
			}
		}
---------*/
		$fila = $fila-1;
		
		$filaGrafica = $fila+2;

		$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		ob_end_clean();
	
		$nomarch = $nomagente."_".$fecini.".xlsx";

		// We'll be outputting an excel file
		header('Content-type: application/vnd.ms-excel');
		// It will be called file.xls
		header('Content-Disposition: attachment; filename="'.$nomarch.'"');

		header('Cache-Control: max-age=0');
		ob_start();
		$writer->save('php://output');
		
		
		//$objWriter->save('wwwroot/some-file.xlsx'); 
		//header("Location: /some-file.xlsx"); exit(); 
		
//	*/	
   }
   }   
   
   private function import_xml()
   { 


      $fecini =  date("y-m-d", strtotime($_POST['fechaini1']));
      $fecfin =  date("y-m-d", strtotime($_POST['fechafin1']));
      $periodo = $_POST['fechaini1']."*".$_POST['fechafin1'];

      if ($fecini > $fecfin) 
	  {
        //$this->new_error_msg("Error: Fecha Final no puede ser menor que fecha de Inicio ".$_POST['fechafin1']);  
      }else {
	      	//$this->new_message("Fecha inicio ".$fecini. "  fecha fin ".$fecfin);
	           
	      	// seleccion de todos los registros que entran en el periodo 
	      	// Con detalle de facturas de venta
	      	$wherevend = '';  
	      	if ($this->agentecod <> 0){
	      	    $wherevend = " and c.codagente = ". $this->agentecod;
	      	}else{
	      	    //$agentecod = $this->agente->codagente;    
	      	}
		  //  $this->new_message("SQL ".$wherevend);


         	$resultado = $this->db->select("SELECT codigo, nombrecliente, total, anulada, pagada, codpago, "
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura and iva=15 group by idfactura) neto15,"
			."(select sum(totaliva) from lineasivafactcli where idfactura=c.idfactura and iva=15  group by idfactura) iva15," 
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura  and iva=0  group by idfactura) neto0 "
                 . "FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . $wherevend ." order by codpago, nombrecliente, total ;");
                 
                 

            // RETENCIONES        	
         	$result_reten = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, tiporeten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. $wherevend ." order by nombrecliente, total ;");

			$resRegReten = $this->db->select("SELECT count(*) contadoreten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. $wherevend ." order by nombrecliente, total ;");

			/// PAGOS NO EN EFECTIVO
         	$res_pagnoefec = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, nota "
         		. " from facturascli c, pagos p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. $wherevend ." order by nombrecliente, total ;");

			$resRegNoefe = $this->db->select("SELECT count(*) contadorpagos "
         		. " from facturascli c, retenciones p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. $wherevend ." order by nombrecliente, total;");

         	$resRegFac = $this->db->select("SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . $wherevend ." order by codpago, nombrecliente, total ;");

          	$resRegFac1 ="SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . $wherevend .";";


         	$resRegRbos = $this->db->select("SELECT count(*) contador1 FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."'".  " ;");     

         	$resultado_rbos = $this->db->select("SELECT nombrecliente, fechap, importe, codpago, "
         		 . "(select total from facturascli as fc where  fc.idfactura = c.idfactura) totalfac, " 
                 . "(select sum(importe) from reciboscli as rc where  rc.idfactura = c.idfactura and estado = 'Pagado'"
                 . " group by idfactura)  totalabono, "
                 . "(select codigo from facturascli as fc1 where  fc1.idfactura = c.idfactura) codigof "
                 . "FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."' "
                 . " order by codpago, nombrecliente, importe ;");     


                 
        	$resultado1 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM presupuestoscli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . $wherevend ." ;");
             
                
       		$resultado2 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM albaranescli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . $wherevend ." ;");                 
    
 

			$fila = 4; //Establecemos en que fila inciara a imprimir los datos    
			$gdImage = imagecreatefrompng('images/logo.png');//Logotipo

			//Objeto de PHPExcel
			$objPHPExcel  = new PHPExcel();
			
			//Propiedades de Documento
			$objPHPExcel->getProperties()->setCreator("John Lima")->setDescription("Liquidacion de Facturas");
			
			//Establecemos la pesta?a activa y nombre a la pesta?a
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setTitle("facturas");
			
			$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
			$objDrawing->setName('Logotipo');
			$objDrawing->setDescription('Logotipo');
			$objDrawing->setHeight(100);
			$objDrawing->setCoordinates('A1');
			$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
			
			
			$estiloTituloReporte = array(
		    'font' => array(
			'name'      => 'Arial',
			'bold'      => true,
			'italic'    => false,
			'strike'    => false,
			'size' =>12
		    ),
		    'fill' => array(
			'type'  => PHPExcel_Style_Fill::FILL_SOLID
			),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
			
			$estiloTituloColumnas = array(
		    'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => 'FFFFFF'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => '538DD5')
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
    		),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT ,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_TOP
		    )
			);

		  $estiloTituloColumnas2 = array(
		  'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
                
		$estiloTituloColumnas3 = array(
		    'font' => array(
			'name'  => 'Calibri',
			'bold'  => false,
			'size' =>9,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);                
		                
		$estiloNumerosCol = array(
		    'font' => array(
		  'name'  => 'Calibri',
		  'bold'  => false,
		  'size' =>9,
		  'color' => array(
		  'rgb' => '000000'
		  )
		    ),
		    'fill' => array(
		  'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
		  'allborders' => array(
		  'style' => PHPExcel_Style_Border::BORDER_THIN
		  )
		    ),
		    'alignment' =>  array(
		  'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
		  'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    ),
		    'numberformat' => array(
		    'setFormatCode' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE
		    )

		  );                  
   
		/// contador de registros      
	 	if($resRegFac){
		    foreach ($resRegFac as $rows) {
		      $nregfac = $rows['contador'];
		    }
		}else{
		    $menRegistro1 = 'No hay Facturas emitidas en esta fecha ';
		}


		if($resRegReten){
		    foreach ($resRegReten as $rows) {
		      $nregReten = $rows['contadoreten'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}


		if($resRegNoefe){
		    foreach ($resRegNoefe as $rows) {
		      $nregPagos = $rows['contadorpagos'];
		    }
		}else{
		    $menRegistroP = 'No hay Pagos no Efectivo cobrados en esta fecha';
		}

		if($resRegRbos){
		    foreach ($resRegRbos as $rows) {
		      $nregrecib = $rows['contador1'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}

        
		$estiloInformacion = new PHPExcel_Style();
		$estiloInformacion->applyFromArray( array(
	    'font' => array(
		'name'  => 'Arial',
		'color' => array(
		'rgb' => '000000'
		)
	    ),
	    'fill' => array(
		'type'  => PHPExcel_Style_Fill::FILL_SOLID
		),
	    'borders' => array(
		'allborders' => array(
		'style' => PHPExcel_Style_Border::BORDER_THIN
		)
	    ),
		'alignment' =>  array(
		'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
	    )
		));
	    for ($i = 1; $i <= 51; $i++) {
	            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(14);
	    }
		$linaux = $fila + $nregfac + 5;
		$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->applyFromArray($estiloTituloReporte);
	  	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'V E N T A S');
	  	$objPHPExcel->getActiveSheet()->setCellValue('b3', "Fecha desde: 20".$fecini." hasta: 20".$fecfin);


		$objPHPExcel->getActiveSheet()->mergeCells('b1:E1');
	  	$objPHPExcel->getActiveSheet()->mergeCells('B3:I3');
	  	$objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
	        

        
        // COLUMNAS
		$objPHPExcel->getActiveSheet()->setCellValue('G1',"Fecha Reporte: ".date("Y/m/d") );
		if($wherevend == ''){
		    $nomagente = "TODOS  LOS VENDEDORES"    ;
		}else{
    		$agentecod = $this->agente->codagente;
    		$nomagente =  $this->agente->nombre." ".$this->agente->apellidos;     
		}
		$objPHPExcel->getActiveSheet()->setCellValue('b1', "Vendedor:   ".$nomagente );
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(14);      
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(14);  
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14); 


		if ($resultado){
			
		     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->applyFromArray($estiloTituloColumnas2);
		     $objPHPExcel->getActiveSheet()->getStyle('A5:'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		     $objPHPExcel->getActiveSheet()->getStyle('D5:'.'H'.$linaux)->applyFromArray($estiloNumerosCol);
		     $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Nombre');
		     $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Codigo Fac');
		     $objPHPExcel->getActiveSheet()->setCellValue('D4', 'Val Fac Pag');
		     $objPHPExcel->getActiveSheet()->setCellValue('E4', 'Val Fac Impag');
		     $objPHPExcel->getActiveSheet()->setCellValue('F4', 'Neto 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('G4', 'Iva 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('H4', 'Neto 0');
		     $objPHPExcel->getActiveSheet()->setCellValue('I4', 'Bemeficio Fac');
		     $nregis=1;
		     $codpago = '';
		     $subtotp    = 0;
		     $subtotimp  = 0;
		     $totalre = 0;
		     $totalImp = 0;
		     $totalP = 0;


		//if ($resultado){
			foreach($resultado as $rows)	{
			    if ($rows['anulada'] == 0){
		          	if ( $nregis == 1) {
		            	$codpago = $rows['codpago'];
		          	}
		          	if ( $codpago != $rows['codpago']) {
			            $codimpre = $codpago;
			            $codpago = $rows['codpago'];
			            $fila++;
			            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
			            $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
			            $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
			            $subtotp   = 0;
			            $subtotimp = 0;
		          	}
		          	$fila++; //Sumamos 1 para pasar a la siguiente fila
			        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
				    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		          	if ($rows['pagada'] == 1){
		          		$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['total']);
		          		$subtotp  = $subtotp  + $rows['total'];
		          		$totalP   = $totalP + $rows['total'];
		          	}else{
		          		$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		          		$subtotimp  = $subtotimp + $rows['total'];
		          		$totalImp   = $totalImp + $rows['total'];
		          	}

		          	$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['iva15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, $rows['neto0']);
		          	$sqlbenefic = $this->db->select('select sum(totalutilidad) totutil from lineasfacturascli where idfactura= '.$rows['idfactura']);

		          	if($sqlbenefic){
		          		foreach ($sqlbenefic as $k) {
		          			$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $k['totutil']);
		          		}
		          	}
		          	
		          	$codimpre1 = $rows['codpago'];
			       // $subtot  = $subtot  + $rows['total'];
		          	$totalre = $totalre + $rows['total'];

		          	$neto15 = $neto15 + $rows['neto15'];
		          	$iva15  = $iva15  + $rows['iva15'];
		          	$neto0  = $neto0  + $rows['neto0'];
		          	

		          	$nregis++;
	           		//$this->new_message( " cliente procesado: ".$rows['nombrecliente']."   total: ".$rows['total']);
		    	}
			}
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, number_format($neto15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($iva15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, number_format($neto0,2,'.',','));
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalP,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalImp,2,'.',','));
            $fila++;
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   TOTAL FACTURAS ");
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalre,2,'.',','));
	        
	        
			
		}else{
		    $fila = $fila + 1 ;
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han emitido facturas en este lapaso de tiempo' );
		    $fila ++;
		}
		/////////
		///*
	  	if ($resultado_rbos){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->mergeCells('A'.$fila.':'.'G'.$fila);
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Abono Act');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'T. Abonos.');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Saldo');
		    $fila ++;
  			$linaux = $fila + $nregrecib + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalre = 0;
  			//if ($resultado_rbos){
  			foreach($resultado_rbos as $rows)  {
      
	          	if ( $nregis == 1) {
	            	$codpago = $rows['codpago'];
	          	}
	          	if ( $codpago != $rows['codpago']) {
		            $codimpre = $codpago;
		            $codpago = $rows['codpago'];
		            $fila++;
		            $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
		            $objPHPExcel->getActiveSheet()->setCellValue('J'.$fila, number_format($subtot,2,',','.'));
		            $subtot = 0;
	          	}  //nombrecliente, fechap, importe, codpago, totalfac totalabono
			        $fila++; //Sumamos 1 para pasar a la siguiente fila
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigof']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['totalfac']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['importe']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['totalabono']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['totalfac'] - $rows['totalabono']);
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codpago'];
		        $subtot  = $subtot  + $rows['importe'];
		        $totalre = $totalre + $rows['importe'];
		        $nregis++;
  			}
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalre,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay recibos');
    		$fila = $fila + 2 ;
    		//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han cobrado recibos en este lapaso de tiempo');
    		$fila ++;
  		}//*/

		////////  
        ///////    RETENCIONES   
	  	if ($result_reten){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'R E T E N C I O N E S');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Tipo Reten');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregReten + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($result_reten as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['tiporeten']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");

          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
			//$this->new_message('no hay retenciones');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado retenciones sobre las facturas del período');
    		$fila ++;
  		}

        /////    FIN DE RETENCIONES     ////////////


        /// pagos no efectivo
        if ($res_pagnoefec){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'PAGOS NO EFECTIVO');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Nota');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregPagos + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($res_pagnoefec as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['nota']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay Pagos');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado pagos NO EFECTIVO sobre facturas del período');
    		$fila ++;
  		}

  		////   fin de pagos no efectivo
/*
		if ($resultado1){
	
			foreach($resultado1 as $rows)	{
		    	$objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				$fila++; 
		   	 	$nregis++;
			}
		}
			
		if($resultado2){
			foreach($resultado2 as $rows)	{
			    $objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				//$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['total']);
				$fila++;
			    $nregis++;
			}
		}
*/
		$fila = $fila-1;
		
		$filaGrafica = $fila+2;

		$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		ob_end_clean();
	
		$nomarch = $nomagente."_".$fecini.".xlsx";

		// We'll be outputting an excel file
		header('Content-type: application/vnd.ms-excel');
		// It will be called file.xls
		header('Content-Disposition: attachment; filename="'.$nomarch.'"');

		header('Cache-Control: max-age=0');
		ob_start();
		$writer->save('php://output');
		
		
		//$objWriter->save('wwwroot/some-file.xlsx'); 
		//header("Location: /some-file.xlsx"); exit(); 
		
//	*/	
   }
   }


   private function beneficio_xml()
 	{ 

$this->template = FALSE;
      $fecini  =  date("y-m-d", strtotime($_POST['fechaini4']));
      $fecfin  =  date("y-m-d", strtotime($_POST['fechafin4']));
      $periodo = $_POST['fechaini4']."*".$_POST['fechafin4'];
      $almacen = $_POST['codalmacen'];
      if($almacen){
      	$sqlalmacen = " and codalmacen ='".$almacen."'";
      }else{
      	$almacen = "todos";
      	$sqlalmacen = "";
      }
      
      if ($fecini > $fecfin ) 
	  {

      }else {
	      	// seleccion de todos los registros que entran en el periodo 
	      	// Con detalle de facturas de venta
	      	$wherevend = '';  
	      	if ($this->agentecod <> 0){
	      	    $wherevend = " and c.codagente = ". $this->agentecod;
	      	}else{
	      	       
	      	}
		


         	$resultado = $this->db->select("SELECT codigo, nombrecliente, total, anulada, pagada, codpago, c.idfactura, "
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura and iva=15 ) neto15,"
			."(select sum(totaliva) from lineasivafactcli where idfactura=c.idfactura and iva=15  group by idfactura) iva15," 
			."(select sum(pvptotal) from lineasfacturascli where idfactura=c.idfactura and (iva=0 or iva='' or iva=null)  group by idfactura) neto0, "
			."(select sum(totalutilidad) from lineasfacturascli where idfactura=c.idfactura   group by idfactura) totalutil "
                 . " FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 ".$sqlalmacen
                 . " order by codpago, nombrecliente, total ;");

$this->new_error_msg("SELECT codigo, nombrecliente, total, anulada, pagada, codpago, c.idfactura, "
			."(select sum(neto) from lineasivafactcli where idfactura=c.idfactura and iva=15 ) neto15,"
			."(select sum(totaliva) from lineasivafactcli where idfactura=c.idfactura and iva=15  group by idfactura) iva15," 
			."(select sum(pvptotal) from lineasfacturascli where idfactura=c.idfactura and (iva=0 or iva='' or iva=null)  group by idfactura) neto0, "
			."(select sum(totalutilidad) from lineasfacturascli where idfactura=c.idfactura   group by idfactura) totalutil "
                 . " FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 ".$sqlalmacen
                 . " order by codpago, nombrecliente, total ;");

        	//var_dump($resultado);                       

            // RETENCIONES        	
         	$result_reten = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, tiporeten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			$resRegReten = $this->db->select("SELECT count(*) contadoreten "
         		. " from facturascli c, retenciones r "
         		. " where c.idfactura = r.idfactura "
         		. " and r.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			/// PAGOS NO EN EFECTIVO
         	$res_pagnoefec = $this->db->select("SELECT codigo, nombrecliente, total, neto, importe, nota "
         		. " from facturascli c, pagos p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total ;");

			$resRegNoefe = $this->db->select("SELECT count(*) contadorpagos "
         		. " from facturascli c, retenciones p "
         		. " where c.idfactura = p.idfactura "
         		. " and p.fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
         		. " order by nombrecliente, total;");

         	$resRegFac = $this->db->select("SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . " order by codpago, nombrecliente, total ;");

          	$resRegFac1 ="SELECT count(*) contador FROM facturascli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' and anulada = 0 "
                 . ";";


         	$resRegRbos = $this->db->select("SELECT count(*) contador1 FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."'".  " ;");     

         	$resultado_rbos = $this->db->select("SELECT nombrecliente, fechap, importe, codpago, "
         		 . "(select total from facturascli as fc where  fc.idfactura = c.idfactura) totalfac, " 
                 . "(select sum(importe) from reciboscli as rc where  rc.idfactura = c.idfactura and estado = 'Pagado'"
                 . " group by idfactura)  totalabono, "
                 . "(select codigo from facturascli as fc1 where  fc1.idfactura = c.idfactura) codigof "
                 . "FROM reciboscli c "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."' "
                 . " order by codpago, nombrecliente, importe ;");     


                 
        	$resultado1 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM presupuestoscli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " ;");
             
                
       		$resultado2 = $this->db->select("SELECT nombrecliente, total "
                 . "FROM albaranescli c "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " ;");                 
    
 

			$fila = 4; //Establecemos en que fila inciara a imprimir los datos    
			$gdImage = imagecreatefrompng('images/logo.png');//Logotipo
///*
			//Objeto de PHPExcel
			$objPHPExcel  = new PHPExcel();
			
			//Propiedades de Documento
			$objPHPExcel->getProperties()->setCreator("John Lima")->setDescription("Liquidacion de Facturas");
			
			//Establecemos la pesta?a activa y nombre a la pesta?a
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setTitle("facturas");
			
			$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
			$objDrawing->setName('Logotipo');
			$objDrawing->setDescription('Logotipo');
			$objDrawing->setHeight(100);
			$objDrawing->setCoordinates('A1');
			$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
			
			
			$estiloTituloReporte = array(
		    'font' => array(
			'name'      => 'Arial',
			'bold'      => true,
			'italic'    => false,
			'strike'    => false,
			'size' =>12
		    ),
		    'fill' => array(
			'type'  => PHPExcel_Style_Fill::FILL_SOLID
			),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
			
			$estiloTituloColumnas = array(
		    'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => 'FFFFFF'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => '538DD5')
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
    		),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT ,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_TOP
		    )
			);

		  $estiloTituloColumnas2 = array(
		  'font' => array(
			'name'  => 'Arial',
			'bold'  => true,
			'size' =>10,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);
                
		$estiloTituloColumnas3 = array(
		    'font' => array(
			'name'  => 'Calibri',
			'bold'  => false,
			'size' =>9,
			'color' => array(
			'rgb' => '000000'
			)
		    ),
		    'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		    ),
		    'alignment' =>  array(
			'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    )
			);                
		                
		$estiloNumerosCol = array(
		    'font' => array(
		  'name'  => 'Calibri',
		  'bold'  => false,
		  'size' =>9,
		  'color' => array(
		  'rgb' => '000000'
		  )
		    ),
		    'fill' => array(
		  'type' => PHPExcel_Style_Fill::FILL_SOLID
		    ),
		    'borders' => array(
		  'allborders' => array(
		  'style' => PHPExcel_Style_Border::BORDER_THIN
		  )
		    ),
		    'alignment' =>  array(
		  'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
		  'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
		    ),
		    'numberformat' => array(
		    'setFormatCode' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE
		    )

		  );                  
   
		/// contador de registros      
	 	if($resRegFac){
		    foreach ($resRegFac as $rows) {
		      $nregfac = $rows['contador'];
		    }
		}else{
		    $menRegistro1 = 'No hay Facturas emitidas en esta fecha ';
		}


		if($resRegReten){
		    foreach ($resRegReten as $rows) {
		      $nregReten = $rows['contadoreten'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}


		if($resRegNoefe){
		    foreach ($resRegNoefe as $rows) {
		      $nregPagos = $rows['contadorpagos'];
		    }
		}else{
		    $menRegistroP = 'No hay Pagos no Efectivo cobrados en esta fecha';
		}

		if($resRegRbos){
		    foreach ($resRegRbos as $rows) {
		      $nregrecib = $rows['contador1'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}

        
		$estiloInformacion = new PHPExcel_Style();
		$estiloInformacion->applyFromArray( array(
	    'font' => array(
		'name'  => 'Arial',
		'color' => array(
		'rgb' => '000000'
		)
	    ),
	    'fill' => array(
		'type'  => PHPExcel_Style_Fill::FILL_SOLID
		),
	    'borders' => array(
		'allborders' => array(
		'style' => PHPExcel_Style_Border::BORDER_THIN
		)
	    ),
		'alignment' =>  array(
		'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
	    )
		));
	    for ($i = 1; $i <= 51; $i++) {
	            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(14);
	    }
		$linaux = $fila + $nregfac + 5;
		$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->applyFromArray($estiloTituloReporte);
	  	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'V E N T A S');
	  	$objPHPExcel->getActiveSheet()->setCellValue('b3', "Fecha desde: 20".$fecini." hasta: 20".$fecfin);


		$objPHPExcel->getActiveSheet()->mergeCells('b1:E1');
	  	$objPHPExcel->getActiveSheet()->mergeCells('B3:I3');
	  	$objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
	        

        
        // COLUMNAS
		$objPHPExcel->getActiveSheet()->setCellValue('G1',"Fecha Reporte: ".date("Y/m/d") );

	    $nomagente = "TODOS  LOS VENDEDORES"    ;

		$objPHPExcel->getActiveSheet()->setCellValue('b1', "Vendedor:   ".$nomagente );
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(14);      
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(14);  
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14);  


		if ($resultado){
			
		     $objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray($estiloTituloColumnas2);
		     $objPHPExcel->getActiveSheet()->getStyle('A5:'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		     $objPHPExcel->getActiveSheet()->getStyle('D5:'.'I'.$linaux)->applyFromArray($estiloNumerosCol);
		     $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Nombre');
		     $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Codigo Fac');
		     $objPHPExcel->getActiveSheet()->setCellValue('D4', 'Val Fac Pag');
		     $objPHPExcel->getActiveSheet()->setCellValue('E4', 'Val Fac Impag');
		     $objPHPExcel->getActiveSheet()->setCellValue('F4', 'Neto 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('G4', 'Iva 15');
		     $objPHPExcel->getActiveSheet()->setCellValue('H4', 'Neto 0');
		     $objPHPExcel->getActiveSheet()->setCellValue('I4', 'Bemeficio Fac');
		     $nregis=1;
		     $codpago = '';
		     $subtotp    = 0;
		     $subtotimp  = 0;
		     $totalre = 0;
		     $totalP  = 0;
			 $totalImp = 0;
			 $totalben = 0;
			 $subtotben = 0;
			 $neto15 = 0;
				$iva15  = 0;
				$neto0  = 0;

		//if ($resultado){
			foreach($resultado as $rows)	{
			    if ($rows['anulada'] == 0){
		          	if ( $nregis == 1) {
		            	$codpago = $rows['codpago'];
		          	}
		          	if ( $codpago != $rows['codpago']) {
			            $codimpre = $codpago;
			            $codpago = $rows['codpago'];
			            $fila++;
			            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
			            $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
			            $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
			            $objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, number_format($subtotben,2,'.',','));
			            $subtotben = 0;
			            $subtotp   = 0;
			            $subtotimp = 0;
		          	}
		          	$fila++; //Sumamos 1 para pasar a la siguiente fila
			        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
				    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		          	if ($rows['pagada'] == 1){
		          		$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['total']);
		          		$subtotp  = $subtotp  + $rows['total'];
		          		$totalP   = $totalP + $rows['total'];
		          	}else{
		          		$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		          		$subtotimp  = $subtotimp  + $rows['total'];
		          		$totalImp   = $totalImp + $rows['total'];
		          	}

		          	$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['iva15']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, $rows['neto0']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $rows['totalutil']);
		          	//$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
						
						//$sqlbenefic = $this->db->select('select sum(totalutilidad) totutil from lineasfacturascli where idfactura= '.$rows['idfactura']);

		          	if($rows['totalutil'] > 0){
		          		//foreach ($sqlbenefic as $k) {
		          		//	if ($k['totutil'] > 0) {
		          				$n0 = 0;
		          				$rows['neto0'] ? $n0 = $rows['neto0'] : $n0 = $rows['neto0'] ;  
		          				$rows['neto15'] ? $n15 = $rows['neto15'] : $n15 = $rows['neto15'] ;  
		          				$utilxfactura = $n15 + $n0 - $rows['totalutil'];
			          			$subtotben = $subtotben + $utilxfactura ;
			          			$totalben = $totalben + $utilxfactura ;
			          			$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $utilxfactura);
		          		//}
		          		//}
		          	}


		          	$codimpre1 = $rows['codpago'];
			       // $subtot  = $subtot  + $rows['total'];
		          	$totalre = $totalre + $rows['total'];

		          	$neto15 = $neto15 + $rows['neto15'];
		          	$iva15  =$iva15  + $rows['iva15'];
		          	$neto0  = $neto0  + $rows['neto0'];
		          	

		          	$nregis++;
	           		//$this->new_message( " cliente procesado: ".$rows['nombrecliente']."   total: ".$rows['total']);
		    	}
			}
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($subtotp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtotimp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, number_format($neto15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($iva15,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, number_format($neto0,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, number_format($subtotben,2,'.',','));
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalP,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalImp,2,'.',','));
	        $objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, number_format($totalben,2,'.',','));
            $fila++;	
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   TOTAL FACTURAS ");        
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($totalre,2,'.',','));
		}else{
		    $fila = $fila + 1 ;
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han emitido facturas en este lapaso de tiempo' );
		    $fila ++;
		}
		/////////
		
	  	if ($resultado_rbos){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->mergeCells('A'.$fila.':'.'G'.$fila);
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Abono Act');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'T. Abonos.');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Saldo');
		    $fila ++;
  			$linaux = $fila + $nregrecib + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalre = 0;
  			//if ($resultado_rbos){
  			foreach($resultado_rbos as $rows)  {
      
	          	if ( $nregis == 1) {
	            	$codpago = $rows['codpago'];
	          	}
	          	if ( $codpago != $rows['codpago']) {
		            $codimpre = $codpago;
		            $codpago = $rows['codpago'];
		            $fila++;
		            $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre);
		            $objPHPExcel->getActiveSheet()->setCellValue('J'.$fila, number_format($subtot,2,',','.'));
		            $subtot = 0;
	          	}  //nombrecliente, fechap, importe, codpago, totalfac totalabono
			        $fila++; //Sumamos 1 para pasar a la siguiente fila
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigof']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['totalfac']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['importe']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['totalabono']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['totalfac'] - $rows['totalabono']);
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codpago'];
		        $subtot  = $subtot  + $rows['importe'];
		        $totalre = $totalre + $rows['importe'];
		        $nregis++;
  			}
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($totalre,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay recibos');
    		$fila = $fila + 2 ;
    		//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han cobrado recibos en este lapaso de tiempo');
    		$fila ++;
  		}//

		////////  
        ///////    RETENCIONES   
	  	if ($result_reten){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'R E T E N C I O N E S');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Tipo Reten');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregReten + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($result_reten as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['tiporeten']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        
		        //$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
			//$this->new_message('no hay retenciones');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado retenciones sobre las facturas del período');
    		$fila ++;
  		}

        /////    FIN DE RETENCIONES     ////////////


        /// pagos no efectivo
        if ($res_pagnoefec){
		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);
			
		  	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila,'PAGOS NO EFECTIVO');

		    $fila ++;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'G'.$fila)->applyFromArray($estiloTituloColumnas2);

		    $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'Nombre');
		    $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'Codigo Fac');
		    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, 'Nota');
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, 'Valor Fac.');
		    $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, 'Neto');
		    $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, 'Importe');
		    $fila ++;
  			$linaux = $fila + $nregPagos + 3;
		    $objPHPExcel->getActiveSheet()->getStyle('A'.$fila.':'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		    $objPHPExcel->getActiveSheet()->getStyle('D'.$fila.':'.'G'.$linaux)->applyFromArray($estiloNumerosCol);

		    $nregis=1;
		    $codpago = '';
		    $subtot  = 0;
		    $totalret = 0;
  			//if ($resultado_rbos){
  			foreach($res_pagnoefec as $rows)  {
      
			    $fila++; //Sumamos 1 para pasar a la siguiente fila    
		        $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregReten);
		        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['nombrecliente']);
		        $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['codigo']);
		        $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['nota']);
		        $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['total']);
		        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['neto']);
		        $objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, $rows['importe']);
		        
		        $codimpre1 = $rows['codigo'];
		        //$subtot    = $subtot + $rows['importe'];
		        $totalret  = $totalret + $rows['importe'];
		        $nregis++;
  			}
          	//$fila++;
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   SUB TOTAL Tipo Pago: ".$codimpre1);
          	//$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, number_format($subtot,2,',','.'));
          	$fila++;
          	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
          	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fila, number_format($totalret,2,',','.'));
          	$fila++;  
          	$f = $fila+1;
          	//$objPHPExcel->getActiveSheet()->mergeCells('b'.$fila.':'.'G'.$f);
          	//$objPHPExcel->getActiveSheet()->getStyle('b'.$fila.':'.'G'.$f)->applyFromArray($estiloTituloColumnas);
          	//$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "Cada registro corresponde a un RECIBO cobrado: asi que, si se pagaron 3 recibos de una factura apareceran 3 resgistros");

  		}else{
		//	$this->new_message('no hay Pagos');
    		$fila = $fila + 2 ;
    	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, 'No se han realizado pagos NO EFECTIVO sobre facturas del período');
    		$fila ++;
  		}

  		////   fin de pagos no efectivo
/*--------------
		if ($resultado1){
	
			foreach($resultado1 as $rows)	{
		    	$objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				$fila++; 
		   	 	$nregis++;
			}
		}
			
		if($resultado2){
			foreach($resultado2 as $rows)	{
			    $objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
				//$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['total']);
				$fila++;
			    $nregis++;
			}
		}
---------//*/
		$fila = $fila-1;
		
		$filaGrafica = $fila+2;

		$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		ob_end_clean();
	
		$nomarch = "Almacen_".$almacen."_".$fecini.".xlsx";

		// We'll be outputting an excel file
		header('Content-type: application/vnd.ms-excel');
		// It will be called file.xls
		header('Content-Disposition: attachment; filename="'.$nomarch.'"');

		header('Cache-Control: max-age=0');
		ob_start();
		$writer->save('php://output');
		
		
		//$objWriter->save('wwwroot/some-file.xlsx'); 
		//header("Location: /some-file.xlsx"); exit(); 
		
//	*/	
   }
   } 
    private function buscar_referencia()
    {
        /// desactivamos la plantilla HTML
        $this->template = FALSE;

        $articulo = new articulo();
        $json = array();
        foreach ($articulo->search($_REQUEST['buscar_referencia']) as $art) {
            $json[] = array('value' => $art->referencia . ' ' . $art->descripcion(60), 'data' => $art->referencia);
        }

        header('Content-Type: application/json');
        echo json_encode(array('query' => $_REQUEST['buscar_referencia'], 'suggestions' => $json));
    }
    
    
    private function genera_regularizacion()
   {
      $this->opcionImp=5;
      $fecini =  date("Y-m-d", strtotime($_POST['fechaini6']));
      $fecfin =  date("Y-m-d", strtotime($_POST['fechafin6']));
      
      //$this->fecini =  date("y-m-d", strtotime($_POST['fechaini6']));
      //$this->fecfin =  date("y-m-d", strtotime($_POST['fechafin6']));
      $sqlwhere = true;
      $codfamilia = NULL;
      $sql_familia ="";
      $sql_and = '';

      if (isset($_POST['referencia']) && $_POST['referencia'] !== '') {
    		// Evitar inyección SQL usando consultas preparadas
    		$list_productos = $this->db->select("SELECT * FROM articulos WHERE referencia = '". $_POST['referencia']."';");
      }

      // Cuando se escogio familias 
      else{
      	if ($this->b_codfamilia) {
            if ($this->b_subfamilias) {
                $sql_familia .= $sql_and." codfamilia IN (";
                $coma = '';
                foreach ($this->get_subfamilias($this->b_codfamilia) as $fam) {
                    $sql_familia .= $coma . $this->empresa->var2str($fam);
                    $coma = ',';
                }
                $sql_familia .= ")";
            } else {
                $sql_familia .= $sql_and." codfamilia = " . $this->empresa->var2str($this->b_codfamilia);
            }
            $sqlwhere = true;
          	$sql_and = ' and ';
      	}
      	$sqlw = '';
      	if($sqlwhere) $sqlw = ' where ';	
      		$list_productos = $this->db->select("SELECT * from articulos ".$sqlw.$sql_familia." order by referencia ;");
  	 }
      //
        

      /*$sql_tot1		=	"";
      $valfacmin 	= 	0;
      $valfacmax 	= 	0;
      $coma = '';*/
      $prod = '';
      if($list_productos)  {
      	foreach ($list_productos as $key => $producto) {
		    $coma = ($key < count($list_productos) - 1) ? ',' : '';
		    $prod .= "'".$producto['referencia']."'".$coma;       		
		}

      		//$this->new_error_msg('Productos:'.$prod);
      		$list_reg = $this->db->select("SELECT s.referencia, lr.fecha,
										    CASE
										        WHEN lr.cantidadfin > lr.cantidadini THEN lr.cantidadfin - lr.cantidadini
										        ELSE 0
										    END AS entrada,
										    CASE
										        WHEN lr.cantidadfin <= lr.cantidadini THEN lr.cantidadini - lr.cantidadfin
										        ELSE 0
										    END AS salida,
										    motivo
										FROM 
										    stocks s
										JOIN 
										     lineasregstocks lr ON s.idstock = lr.idstock
										where 
											s.referencia IN ($prod)     
											AND lr.fecha >= '$fecini'
        									AND lr.fecha <= '$fecfin'	
										order by s.idstock, referencia, fecha;");

      		if($list_reg){
      			/*foreach ($list_reg as $k) {
      				$this->new_error_msg('r:'.$k['referencia']." - ".$k['fecha']." - ".$k['entrada']." - ".$k['salida']." - ".$k['motivo']);
      			}*/

  				$gdImage = imagecreatefrompng('images/logo.png');//Logotipo
				//*  
				//Objeto de PHPExcel
				$objPHPExcel  = new PHPExcel();
				
				//Propiedades de Documento
				$objPHPExcel->getProperties()->setCreator("John Lima")->setDescription("Inventario");
				
				//Establecemos la pesta?a activa y nombre a la pesta?a
				$objPHPExcel->setActiveSheetIndex(0);
				$objPHPExcel->getActiveSheet()->setTitle("Inventario");
				  
				$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
				$objDrawing->setName('Logotipo');
				$objDrawing->setDescription('Logotipo');
				$objDrawing->setHeight(100);
				$objDrawing->setCoordinates('A1');
				$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				  
  
				  $estiloTituloColumnas = array(
				    'font' => array(
				  'name'  => 'Arial',
				  'bold'  => true,
				  'size' =>11,
				  'color' => array(
				  'rgb' => 'FFFFFF'
				  )
				    ),
				    'fill' => array(
				  'type' => PHPExcel_Style_Fill::FILL_SOLID,
				  'color' => array('rgb' => '538DD5')
				    ),
				    'borders' => array(
				  'allborders' => array(
				  'style' => PHPExcel_Style_Border::BORDER_THIN
				  )
				    ),
				    'alignment' =>  array(
				  'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
				  'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
				    )
				  );

				$estiloTituloColumnas2 = array(
					  'font' => array(
					  'name'  => 'Arial',
					  'bold'  => true,
					  'size' =>9,
					  'color' => array(
					  'rgb' => '000000'
					  )
					    ),
					    'fill' => array(
					  'type' => PHPExcel_Style_Fill::FILL_SOLID
					    ),
					    'borders' => array(
					  'allborders' => array(
					  'style' => PHPExcel_Style_Border::BORDER_THIN
					  )
					    ),
					    'alignment' =>  array(
					  'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
					  'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER
					    )
					  );
                

			  for ($i = 1; $i <= 51; $i++) {
			    $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(20);
			  }
			        
			$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($estiloTituloColumnas);
			$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->applyFromArray($estiloTituloColumnas2);
			$objPHPExcel->getActiveSheet()->getStyle('A4:F4')->applyFromArray($estiloTituloColumnas2);


			$objPHPExcel->getActiveSheet()->mergeCells('D3:F3');
			$objPHPExcel->getActiveSheet()->setCellValue('A1', 'LISTADO DE REGULARIZACIONES');
			$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Fecha:');
			$objPHPExcel->getActiveSheet()->setCellValue('B3', date("j/n/Y") );
			$objPHPExcel->getActiveSheet()->setCellValue('D3', 'POR RANGO DE FECHAS ');
			$objPHPExcel->getActiveSheet()->setCellValue('A4','Ord.');        
			$objPHPExcel->getActiveSheet()->setCellValue('B4','Referencia');
			$objPHPExcel->getActiveSheet()->setCellValue('C4','Fecha');
			$objPHPExcel->getActiveSheet()->setCellValue('D4','Entrada');
			$objPHPExcel->getActiveSheet()->setCellValue('E4','Salida');
			$objPHPExcel->getActiveSheet()->setCellValue('F4','Motivo');

			$objPHPExcel->getActiveSheet()->mergeCells('A1:F2');
        
        // COLUMNAS        

	        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
	        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
	        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
	        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(60);
      		$agentecod = $this->agente->codagente;
      		$nomagente = $this->agente->nombre." ".$this->agente->apellidos;     
      
        	$nregis=1;
  			if ($list_reg){
			    $s_total = 0;
			    $ws=0;
			    $f=1;
			    $filr = 0;
			    $fila = 6;
			    $saldo_t = 0;
				foreach($list_reg as $rows)  {
	      			$objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);   
	      			$objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['referencia']);
	      			$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['fecha']);
				    $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['entrada']);
				    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['salida']);
			        $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['motivo']);
				    $fila++; 
				    $nregis++;
				}

  			}
			  $fila = $fila-1;
			  $filaGrafica = $fila+2;
			  
				// Definir los valores para el gráfico
				$values = new PHPExcel_Chart_DataSeriesValues('Number', 'Inventario!$D$7:$D$' . $fila);

				// Definir el tipo de gráfico y agrupación
				$series = new PHPExcel_Chart_DataSeries(
				    PHPExcel_Chart_DataSeries::TYPE_BARCHART,
				    PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,
				    array(0),
				    array(),
				    array(), // No se necesitan etiquetas de columnas
				    array($values) // Valores
				);

				// Configurar la dirección del gráfico
				$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);

				// Inicializar el gráfico con el área de trazado
				$layout = new PHPExcel_Chart_Layout();
				$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));

				// Crear el objeto de gráfico
				$chart = new PHPExcel_Chart('exemplo', null, null, $plotarea);

				// Definir el título del gráfico
				$title = new PHPExcel_Chart_Title(null, $layout);
				$title->setCaption('Gráfico PHPExcel Chart Class');

				// Definir la posición del gráfico y el título
				$chart->setTopLeftPosition('B' . $filaGrafica);
				$filaFinal = $filaGrafica + 10;
				$chart->setBottomRightPosition('E' . $filaFinal);
				$chart->setTitle($title);

				// Crear el escritor de Excel
				$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

				// Limpiar el búfer de salida
				ob_end_clean();

				// Configurar la inclusión de gráficos
				$writer->setIncludeCharts(TRUE);

				// Definir el nombre del archivo
				$nomarch = $nomagente . $fecini . ".xlsx";


				// Indicar que se va a enviar un archivo Excel
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

				// Definir el nombre del archivo
				$nomarch = $nomagente . $fecini . ".xlsx";
				header('Content-Disposition: attachment; filename="' . $nomarch . '"');

				// Evitar el almacenamiento en caché
				header('Cache-Control: max-age=0');
				ob_start();
				$writer->save('php://output');

    }  ///fin del if   		
      		
      }else{
      	$this->new_error_msg('No No:'. "SELECT * from articulos ".$sqlw.$sql_familia." order by referencia ;");
      }

   } // FIN FUNCION

    private function get_subfamilias($cod)
    {
        $familias = array($cod);

        $data = $this->db->select("SELECT codfamilia,madre FROM familias WHERE madre = " . $this->empresa->var2str($cod) . ";");
        if ($data) {
            foreach ($data as $d) {
                foreach ($this->get_subfamilias($d['codfamilia']) as $subf) {
                    $familias[] = $subf;
                }
            }
        }

        return $familias;
    }
}