<?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 = '';
      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()
   	{

      
   	}
   
   
   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 codigo, nombrecliente, total, anulada, pagada, codpago "
                 . "FROM facturascli "
                 . "WHERE fecha between '".$fecini."' and '".$fecfin."' "
                 . "and codvendedor = ". $agentecod." order by codpago, nombrecliente, total ;");

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

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


         	$resRegRbos = $this->db->select("SELECT count(*) contador1 FROM reciboscli "
                 . "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 = reciboscli.idfactura) totalfac, " 
                 . "(select sum(importe) from reciboscli as rc where  rc.idfactura = reciboscli.idfactura and estado = 'Pagado'"
                 . " group by idfactura)  totalabono, "
                 . "(select codigo from facturascli as fc1 where  fc1.idfactura = reciboscli.idfactura) codigof "
                 . "FROM reciboscli "
                 . "WHERE fechap between '".$fecini."' and '".$fecfin."' "
                 . " order by codpago, nombrecliente, importe ;");     

                 //. "and codvendedor = ". $agentecod." order by codpago, nombrecliente, importe ;");                 
                 //. "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." ;");                 


$this->new_message('paso 1');         
			$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($resRegRbos){
		    foreach ($resRegRbos as $rows) {
		      $nregrecib = $rows['contador1'];
		    }
		}else{
		    $menRegistro2 = 'No hay Recibos cobrados en esta fecha';
		}
$this->new_message('paso recibos '.$nregrecib."  factura ".$nregfac);
        
		$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', 'INGRESO DIARIO DE VENTAS');
	  	$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") );
		$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(10);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);      


		if ($resultado){
$this->new_message('SI hay facturas');			
		     $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->applyFromArray($estiloTituloColumnas2);
		     $objPHPExcel->getActiveSheet()->getStyle('A5:'.'C'.$linaux)->applyFromArray($estiloTituloColumnas3);
		     $objPHPExcel->getActiveSheet()->getStyle('D5:'.'D'.$linaux)->applyFromArray($estiloNumerosCol);
		     $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Nombre');
		     $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Codigo Fac');
		     $objPHPExcel->getActiveSheet()->setCellValue('D4', 'Valor');
		     $nregis=1;
		     $codpago = '';
		     $subtot  = 0;
		     $totalre = 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($subtot,2,',','.'));
			            $subtot = 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']);
		          	$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['total']);
		          	//$objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, number_format($rows['total'],2,',','.'));
		          	$codimpre1 = $rows['codpago'];
			        $subtot  = $subtot  + $rows['total'];
		          	$totalre = $totalre + $rows['total'];
		          	$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($subtot,2,',','.'));
	        $fila++;
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, "   T O T A L  ");
	        $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 ++;
  		}

		////////  

		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";
	       

		//$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()
   {

      
   }
}