<?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 liqfactura 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('liqfactura', 'Liq. de facturas', '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 = '';
          if (isset($_POST['estado'])){
          $this->estado = $_POST['estado'];
          }
      
            if (isset($_REQUEST['codagentev'])) {
                $datagente = new agente();
                $this->agentev = $datagente->get($_POST['codagentev']);
                if (($_POST['codagentev']) ) {
                        $this->agentenom = $this->agentev->get_fullname();
                }
                    $this->agentecod = $_POST['codagentev']; //$this->agente->codagente;
                //$this->new_error_msg("Agente zz ".$agentecod);
            } 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;
                //$this->new_error_msg("Agente zz ".$agentecod);
            } else {
                $this->agente = $this->user->get_agente();
            }

      $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['fechaini3']) )
      {
         $this->genera_ventxprod();
      }
 }

  

    
    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'];
      $agentecod = $this->agente->codagente;
      if ($fecini > $fecfin) 
	  {
        $this->new_error_msg("Error: Fecha Final no puede ser menor que fecha de Inicio ".$_POST['fechafin2']);  
      }
       else
      {
          // se cambia conagente x codvendedor  11/01/2018 joly
      $this->resultado = $this->db->select("select fac.codigo codigo, pre.idpresupuesto presup, pre.idpedido pedido, ped.idalbaran albaran, fac.idfactura factura, fac.nombrecliente nombre, fac.total total , 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from presupuestoscli pre, pedidoscli ped,  facturascli fac, albaranescli alb 
where pre.idpedido = ped.idpedido 
and ped.idalbaran = alb.idalbaran 
and alb.idfactura = fac.idfactura
and pre.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0
and pre.codagente = ". $agentecod."
union
select fac.codigo codigo, null presup, ped.idpedido pedido, ped.idalbaran albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from  pedidoscli ped, facturascli fac, albaranescli alb 
where (ped.idalbaran = alb.idalbaran 
and alb.idfactura = fac.idfactura) 
and ped.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0
and ped.codagente = ". $agentecod."
and not exists (select * from presupuestoscli as p where p.idpedido = ped.idpedido )
union
select  fac.codigo codigo, null presup, null pedido, alb.idalbaran albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from   facturascli fac, albaranescli alb 
where  alb.idfactura = fac.idfactura  
and alb.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0 
and alb.codagente = ". $agentecod."
and not exists (select * from pedidoscli as ped where ped.idalbaran = alb.idalbaran )
union
select  fac.codigo codigo, null presup, null pedido, null albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from   facturascli fac
where pagada  = 0
and fac.fecha between '".$fecini."' and '".$fecfin."' 
and anulada = 0 
and fac.codvendedor = ". $agentecod."
and not exists (select * from albaranescli as alb where alb.idfactura = fac.idfactura  )
; " );

$sql1000 = "select fac.codigo codigo, pre.idpresupuesto presup, pre.idpedido pedido, ped.idalbaran albaran, fac.idfactura factura, fac.nombrecliente nombre, fac.total total , 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from presupuestoscli pre, pedidoscli ped,  facturascli fac, albaranescli alb 
where pre.idpedido = ped.idpedido 
and ped.idalbaran = alb.idalbaran 
and alb.idfactura = fac.idfactura
and pre.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0
and pre.codvendedor = ". $agentecod."
union
select fac.codigo codigo, null presup, ped.idpedido pedido, ped.idalbaran albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from  pedidoscli ped, facturascli fac, albaranescli alb 
where (ped.idalbaran = alb.idalbaran 
and alb.idfactura = fac.idfactura) 
and ped.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0
and ped.codagente = ". $agentecod."
and not exists (select * from presupuestoscli as p where p.idpedido = ped.idpedido )
union
select  fac.codigo codigo, null presup, null pedido, alb.idalbaran albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from   facturascli fac, albaranescli alb 
where  alb.idfactura = fac.idfactura  
and alb.fecha between '".$fecini."' and '".$fecfin."' 
and pagada  = 0
and anulada = 0 
and alb.codagente = ". $agentecod."
and not exists (select * from pedidoscli as ped where ped.idalbaran = alb.idalbaran )
union
select  fac.codigo codigo, null presup, null pedido, null albaran, fac.idfactura factura , fac.nombrecliente nombre, fac.total total, 
(select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) abono, fac.total - (select sum(importe) pg from pagos where fac.idfactura = pagos.idfactura) saldo, fac.coddivisa coddivisa1
from   facturascli fac
where pagada  = 0
and fac.fecha between '".$fecini."' and '".$fecfin."' 
and anulada = 0 
and fac.codvendedor = ". $agentecod."
and not exists (select * from albaranescli as alb where alb.idfactura = fac.idfactura  )
; " ;

$this->new_message("sql:> ",$sql1000);
      
      }
   }
   
   
   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
	  
      $agentecod = $this->agente->codagente;
         $resultado = $this->db->select("SELECT nombrecliente, total, anulada "
                 . "FROM facturascli "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . "and codvendedor = ". $agentecod." ;");
                 
                 //. "and codigorect is null and anulada = 0 and codagente = ". $agentecod." ;");
                 
        $resultado1 = $this->db->select("SELECT nombrecliente, total, 'anulada' "
                 . "FROM presupuestoscli "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " and codagente = ". $agentecod." ;");
                 
       $resultado2 = $this->db->select("SELECT nombrecliente, total, 'anulada' "
                 . "FROM albaranescli "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . " and codagente = ". $agentecod." ;");                 

	$fila = 5; //Establecemos en que fila inciara a imprimir los datos 
         
	$fila = 5; //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->setImageResource($gdImage);
	//$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
	//$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
	$objDrawing->setHeight(100);
	$objDrawing->setCoordinates('A1');
	$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
	
	
	$estiloTituloReporte = array(
    'font' => array(
	'name'      => 'Arial',
	'bold'      => true,
	'italic'    => false,
	'strike'    => false,
	'size' =>13
    ),
    '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' =>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
    )
	);
                
$estiloTituloColumnas3 = array(
    'font' => array(
	'name'  => 'Calibri',
	'bold'  => false,
	'size' =>13,
	'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
    )
	);                
                
                
   
        
        
	$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(20);
        }
        
	    $objPHPExcel->getActiveSheet()->getStyle('A3:C3')->applyFromArray($estiloTituloColumnas);
        $objPHPExcel->getActiveSheet()->getStyle('A24:C24')->applyFromArray($estiloTituloColumnas);
        $objPHPExcel->getActiveSheet()->getStyle('A34:C34')->applyFromArray($estiloTituloColumnas);

        
	$objPHPExcel->getActiveSheet()->getStyle('a4:i23')->applyFromArray($estiloTituloColumnas2);
	$objPHPExcel->getActiveSheet()->getStyle('a25:i33')->applyFromArray($estiloTituloColumnas2);
	$objPHPExcel->getActiveSheet()->getStyle('a35:i51')->applyFromArray($estiloTituloColumnas2);
	
	
    $objPHPExcel->getActiveSheet()->getStyle('E5:i51')->applyFromArray($estiloTituloColumnas3);
	$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->applyFromArray($estiloTituloReporte);
	//$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->applyFromArray($estiloTituloColumnas);


	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'INGRESO DIARIO DE VENTAS');
        
        $objPHPExcel->getActiveSheet()->setCellValue('D3', 'Registro de creditos y pagos');
        
       	
        
	    $objPHPExcel->getActiveSheet()->mergeCells('b1:E1');
        $objPHPExcel->getActiveSheet()->mergeCells('D3:I3');
	    $objPHPExcel->getActiveSheet()->mergeCells('A3:C3');
        $objPHPExcel->getActiveSheet()->mergeCells('A22:b22');
        $objPHPExcel->getActiveSheet()->mergeCells('A24:C24');
        $objPHPExcel->getActiveSheet()->mergeCells('A34:C34');
        $objPHPExcel->getActiveSheet()->mergeCells('A42:B42');
        $objPHPExcel->getActiveSheet()->mergeCells('A43:B43');
        $objPHPExcel->getActiveSheet()->mergeCells('A44:B44');
        $objPHPExcel->getActiveSheet()->mergeCells('A45:B45');
        $objPHPExcel->getActiveSheet()->mergeCells('A46:B46');
        $objPHPExcel->getActiveSheet()->mergeCells('A47:B47');
        $objPHPExcel->getActiveSheet()->mergeCells('A48:B48');
        $objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
        

        
        // COLUMNAS
        $objPHPExcel->getActiveSheet()->setCellValue('A3', 'EFECTIVO');
        $objPHPExcel->getActiveSheet()->setCellValue('A22', 'Efectivo + Niquel');
        $objPHPExcel->getActiveSheet()->setCellValue('A24', 'DETALLE CHEQUES');
        $objPHPExcel->getActiveSheet()->setCellValue('A34', 'Facturas Anuladas');
        
        //$objPHPExcel->getActiveSheet()->setCellValue('B1', 'VENDEDOR:');
        $objPHPExcel->getActiveSheet()->setCellValue('G1', 'FECHA:');
        $objPHPExcel->getActiveSheet()->setCellValue('h1', date("Y/m/d") ); //$fecini);
        $objPHPExcel->getActiveSheet()->setCellValue('E4', 'Nombre');
        $objPHPExcel->getActiveSheet()->setCellValue('F4', 'Factura');
        $objPHPExcel->getActiveSheet()->setCellValue('G4', 'Contado');
        $objPHPExcel->getActiveSheet()->setCellValue('H4', 'Credito');
        $objPHPExcel->getActiveSheet()->setCellValue('I4', 'Abono');
        
        $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Billetes');
        $objPHPExcel->getActiveSheet()->setCellValue('b4', 'Cantidad');
        $objPHPExcel->getActiveSheet()->setCellValue('c4', 'Total');
        $objPHPExcel->getActiveSheet()->setCellValue('A5', '100');
        $objPHPExcel->getActiveSheet()->setCellValue('A6', '50');
        $objPHPExcel->getActiveSheet()->setCellValue('A7', '20');
        $objPHPExcel->getActiveSheet()->setCellValue('A8', '10');
        $objPHPExcel->getActiveSheet()->setCellValue('A9', '5');
        $objPHPExcel->getActiveSheet()->setCellValue('A10', '1');
        $objPHPExcel->getActiveSheet()->setCellValue('a11', 'Total');        
        
       
        $objPHPExcel->getActiveSheet()->setCellValue('a13', 'Niquel');
        $objPHPExcel->getActiveSheet()->setCellValue('a14', '1');       
        $objPHPExcel->getActiveSheet()->setCellValue('a15', '0.50');
        $objPHPExcel->getActiveSheet()->setCellValue('a16', '0.25');
        $objPHPExcel->getActiveSheet()->setCellValue('a17', '0.10');
        $objPHPExcel->getActiveSheet()->setCellValue('a18', '0.05');
        $objPHPExcel->getActiveSheet()->setCellValue('a19', '0.01');
        $objPHPExcel->getActiveSheet()->setCellValue('a20', 'Total');
        
        $objPHPExcel->getActiveSheet()->setCellValue('a25', 'Nombre');
        $objPHPExcel->getActiveSheet()->setCellValue('b25', 'Banco');
        $objPHPExcel->getActiveSheet()->setCellValue('c25', 'Valor');
        
        $objPHPExcel->getActiveSheet()->setCellValue('a32', 'Total');
        $objPHPExcel->getActiveSheet()->setCellValue('a40', 'Total');
        
        $objPHPExcel->getActiveSheet()->setCellValue('a42', 'Liquidacion');
        $objPHPExcel->getActiveSheet()->setCellValue('a43', 'Efectivo');
        $objPHPExcel->getActiveSheet()->setCellValue('a44', 'Cheques');
        $objPHPExcel->getActiveSheet()->setCellValue('a45', 'Gastos');
        $objPHPExcel->getActiveSheet()->setCellValue('a46', 'Retencion');
        $objPHPExcel->getActiveSheet()->setCellValue('a47', 'Notas Credito');
        $objPHPExcel->getActiveSheet()->setCellValue('a48', 'Total Ingresos');
        
        $objPHPExcel->getActiveSheet()->setCellValue('a50', 'Ventas');
        $objPHPExcel->getActiveSheet()->setCellValue('a51', 'Pagos');
        
        
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(3);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(13);
        

        
        
	//$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
	//$objPHPExcel->getActiveSheet()->setCellValue('B6', 'NOMBRE CLIENTE');
	//$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
	//$objPHPExcel->getActiveSheet()->setCellValue('C6', 'VALOR');
	
	//Recorremos los resultados de la consulta y los imprimimos
	//while($rows = $resultado->fetch_assoc()){
        //$this->new_message( "prueba de mensaje");
      $agentecod = $this->agente->codagente;
      $nomagente =  $this->agente->nombre." ".$this->agente->apellidos;     
      $objPHPExcel->getActiveSheet()->setCellValue('b1', "Vendedor:   ".$nomagente );
        $nregis=1;
	if ($resultado){
	foreach($resultado as $rows)	{
	    if ($rows['anulada'] == 0){
	        $objPHPExcel->getActiveSheet()->setCellValue('d'.$fila, $nregis);
		    $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['nombrecliente']);
		    //$objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['anulada']);
	    
		    //$objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['total']);		
		    $fila++; //Sumamos 1 para pasar a la siguiente fila
            $nregis++;
            //$this->new_message( " cliente procesado: ".$rows['nombrecliente']."   total: ".$rows['total']);
	    }
	}
	}
	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;
	
	$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A7:C".$fila);
	
	$filaGrafica = $fila+2;
	
	// definir origen de los valores
	$values = new PHPExcel_Chart_DataSeriesValues('Number', 'Productos!$D$7:$D$'.$fila);
	
	// definir origen de los rotulos
	$categories = new PHPExcel_Chart_DataSeriesValues('String', 'Productos!$B$7:$B$'.$fila);
	
	// definir  gr?fico
	$series = new PHPExcel_Chart_DataSeries(
	PHPExcel_Chart_DataSeries::TYPE_BARCHART, // tipo de gr?fico
	PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,
	array(0),
	array(),
	array($categories), // r?tulos das columnas
	array($values) // valores
	);
	$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
	
	// inicializar gr?fico
	$layout = new PHPExcel_Chart_Layout();
	$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
	
	// inicializar o gr?fico
	$chart = new PHPExcel_Chart('exemplo', null, null, $plotarea);
	
	// definir t?tulo do gr?fico
	$title = new PHPExcel_Chart_Title(null, $layout);
	$title->setCaption('Gr?fico PHPExcel Chart Class');
	
	// definir posiciondo gr?fico y t?tulo
	$chart->setTopLeftPosition('B'.$filaGrafica);
	$filaFinal = $filaGrafica + 10;
	$chart->setBottomRightPosition('E'.$filaFinal);
	$chart->setTitle($title);
	
	// adicionar o gr?fico ? folha
	//$objPHPExcel->getActiveSheet()->addChart($chart);
	
	$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	ob_end_clean();
	
	// incluir gr?fico
	$writer->setIncludeCharts(TRUE);
	$nomarch = $nomagente.$fecini.".xlsx";
/*	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

	header("Content-Disposition: attachment;filename=".$nomarch.".xlsx");

	header('Cache-Control: max-age=0');
	
	$writer->save('php://output');      */
        

//$Writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

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

        
        
        

   }
   }
   
    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_ventxprod()
   {

      $fecini =  date("y-m-d", strtotime($_POST['fechaini3']));
      $fecfin =  date("y-m-d", strtotime($_POST['fechafin3']));
      $periodo = $_POST['fechaini3']."*".$_POST['fechafin3'];
      $this->fecini =  date("y-m-d", strtotime($_POST['fechaini3']));
      $this->fecfin =  date("y-m-d", strtotime($_POST['fechafin3']));
      

      $sql_tot="";
      $sql_tot1="";
      $sql_repit="";
      if($this->agentecod) //( isset($_REQUEST['codagente']) )
      {
          $sql_tot= " and f.codvendedor = ".$this->agentecod  ;
          $sql_tot1= " and f1.codagente = ".$this->agentecod  ;
          $sql_repit= " and f1.codagente != ".$this->agentecod  ;
      }
      if($this->b_codfabricante) //( isset($_REQUEST['b_codfabricante']) ) 
      {
          $sql_tot .= " and a.codfabricante = ".  $this->b_codfabricante;
          $sql_tot1 .= " and a1.codfabricante = ".$this->b_codfabricante;
      }
      if($this->referencia)  //if( isset($_REQUEST['referencia']) ) 
      {
          $sql_tot .= " and a.referencia = '".  $this->referencia."'";
          $sql_tot1 .= " and a1.referencia = '".$this->referencia."'";

      }      
      
      if ($fecini > $fecfin) 
	  {
        $this->new_error_msg("Error: Fecha Final no puede ser menor que fecha de Inicio ".$_POST['fechafin2']);  
      }
       else
      {
           
			if($this->estado ==""  )
			{
                            $creavista = $this->db->exec("CREATE TEMPORARY TABLE IF NOT EXISTS tempVentas 
                            select codcliente, l.descripcion nombre, sum(l.cantidad) cantid1 , l.referencia referen, fecha, sum(l.pvptotal) pvptota1 
                            from lineasfacturascli as l, facturascli as f, articulos as a
                            where f.idfactura = l.idfactura
                            and a.referencia =l.referencia
                            and f.fecha between '".$fecini."' and '".$fecfin."'". 
                            $sql_tot." and codigorect is null and anulada = 0 " .	
                            " group by  l.referencia
                            union
                            select codcliente, l1.descripcion nombre, sum(l1.cantidad) cantid1 , l1.referencia referen, fecha,  sum(l1.pvptotal) pvptota1
                            from lineasalbaranescli as l1, albaranescli as f1, articulos as a1
                            where f1.idalbaran = l1.idalbaran
                            and  f1.idfactura not in (select f2.idfactura from facturascli as f2 where f2.idfactura = f1.idfactura
                            and f2.fecha between '".$fecini."' and '".$fecfin."' and codigorect is null and anulada = 0".$sql_repit." )
                            and a1.referencia =l1.referencia
                            and f1.fecha between '".$fecini."' and '".$fecfin."'".
                            $sql_tot1." group by  l1.referencia
                             " );
                            $this->resultadoven = $this->db->select("select  nombre, referen, sum(cantid1) cantid, sum(pvptota1) pvptota  from tempVentas group by nombre, referen;");
                            //$this->new_error_msg("Consulta por Vendedor ".$this->referencia."----".$this->estado);
                        }
                        else{    
                            if (1) { //($this->referencia <> NULL && $this->estado =="xclientes") {
                            $creavista = $this->db->exec("CREATE TEMPORARY TABLE IF NOT EXISTS tempVentas 
                            select l.descripcion nombre, sum(l.cantidad) cantid1 , l.referencia referen,  f.codcliente codcli, f.nombrecliente nombcli, sum(l.pvptotal) pvptota1 
                            from lineasfacturascli as l, facturascli as f, articulos as a
                            where f.idfactura = l.idfactura
                            and a.referencia =l.referencia
                            and f.fecha between '".$fecini."' and '".$fecfin."'". 
                            $sql_tot." and codigorect is null and anulada = 0 " .	
                            " group by  f.codcliente,l.referencia
                            union
                            select l1.descripcion nombre, sum(l1.cantidad) cantid1 , l1.referencia referen,  f1.codcliente codcli, f1.nombrecliente nombcli,  sum(l1.pvptotal) pvptota1
                            from lineasalbaranescli as l1, albaranescli as f1, articulos as a1
                            where f1.idalbaran = l1.idalbaran
                            and  f1.idfactura not in (select f2.idfactura from facturascli as f2 where f2.idfactura = f1.idfactura
                            and f2.fecha between '".$fecini."' and '".$fecfin."' and codigorect is null and anulada = 0".$sql_repit." )
                            and a1.referencia =l1.referencia
                            and f1.fecha between '".$fecini."' and '".$fecfin."'".
                            $sql_tot1." group by  f1.codcliente, l1.referencia " );
                            $this->resultadoven = $this->db->select("select  nombcli, referen, sum(cantid1) cantid, sum(pvptota1) pvptota  from tempVentas group by codcli, referen order by codcli, cantid DESC;");
                            //$this->new_error_msg("Consulta por Cliente ".$this->referencia."----".$this->estado);
                        }else{
                            $this->new_error_msg("Debe elegir por lo menos un producto");
                        }
                        }
                        $sql_tot="";
                        $sql_tot1="";
      }
   }
}







