<?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 inventario extends fbase_controller /* fs_controller*/
{
public $b_codfamilia;
public $familia;
public $tfamilia;
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 $lineas_stock;
public $b_codfabricante;
public $fabricante;
public $referencia;
public $nfabricante;
public $fecini;
public $fecfin;
public $estado;
public $datagente ;
public $repxls; 
public $preciototal;
//public $precantregul;
	public function __construct()
   {
      parent::__construct('inventario', 'Inventario Ingreso', '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->tfamilia = new familia();

      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();
      }


      if (isset($_POST['repxls'])=='XX'){
        $this->new_message('entra a imprime');
        $entraimpre = $_POST['repxls'];
      }else{
        $entraimpre = 'YY';
      }  

     
          if ($entraimpre == 'XX'){
            $this->repxls = 1;
            $this->imprime_inventario();
          }else{
          }
          if (isset($_POST['estado'])){
            $this->estado = $_POST['estado'];
          }
      


      $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['fechafinC']) )
      {
       
         $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['fechainiC']));
    $fecfin =  date("Y-m-d", strtotime($_POST['fechafinC']));
    $familia =  $_POST['b_codfamilia'];

        $codfamilia = NULL;
        $sql_familia ="";
        if ($_POST['b_codfamilia'] != '') {
            $this->codfamilia = $_POST['b_codfamilia'];
            $sql_familia = " and codfamilia = '".$this->codfamilia."'";
        }
    
    $fec_menor =  date("Y-m-d", strtotime('2021-01-01'));
    $this->new_message("Fecha de Calculo: ".$fecfin );
    $agentecod = $this->agente->codagente;
    set_time_limit(0);
    if (!$fecfin) 
    {
      $this->new_error_msg("Error: Fecha Final no puede ser menor que fecha de Inicio ".$_POST['fechafinC']);  
    }
    else
    {
  // lista de todos los productos 
  // disponible Tiene cantidad disponible de la tabla de stocks  ,'AC-0003','JLI-001'
      $list_productos = $this->db->select("SELECT * from articulos where  stockfis > 0  ".$sql_familia
        ." order by referencia ;");
  // ." where referencia in('AD-0001','AD-0002','AD-0003','AD-0004','TO-0015','MO-0005','AC-0002') order by referencia ;");

      $k = 1;

          $sql_vaciar = "delete from temp_caducados";
          $BORRAR = $this->db->exec( $sql_vaciar );

      $sql_vaciar = "delete from temp_producto";

  $saldo_a = 0;     // saldo para la tabla de caducados

  $BORRAR = $this->db->exec( $sql_vaciar );
  if ($list_productos) {
  	$pvpp = 0;
    foreach ($list_productos as $prod) {
      	/* datos de un producto  */
        if ($prod['preciocoste'] > 0){
          $pvpp = $prod['preciocoste'];  
        }else{
          $pvpp = $prod['pvp']*0.15+$prod['pvp'];  
        }
        //$pvpp = $prod['pvp']*0.10+$prod['pvp'];
        $preciotot = $pvpp * $prod['stockfis'];
          $sql_insert = "insert into temp_caducados (id, tipo, codigo, referencia, descripcion, fecha, hora,"
          ." cantidad, precompra, prectotal, saldo) "
          ." values(".$k
          .", 'COMPRA' "
          .", '0000000'"
          .", '". $prod['referencia']."'"
          .", '". $prod['descripcion']."'"
          .", ''"
          .", ''"
          .", ". $prod['stockfis']
          .", ". bcdiv($pvpp,'1', 2)
          .", ". bcdiv($preciotot,'1', 2)
          .", ". $saldo_a.");";
          //$this->new_message("COMPRA ".$sql_insert);
          $guardar = $this->db->exec($sql_insert);
          $k = $k+1;        
          if ($preciotot==0){
            $this->new_message("sin precio  ".$prod['referencia']);    
          }

		if($pvpp==0)
	  	{
	        $list_compra = $this->db->select("SELECT codigo, lfac.referencia refer, lfac.idfactura, lfac.descripcion, "
	        ." fecha, hora, lfac.cantidad cantcompr, lfac.pvptotal preciototal, "
	        ." (lfac.pvptotal/lfac.cantidad) preciounitario "
	        //." (select sum(cantidad) FROM lineasfacturasprov as lf "
	        //." where lfac.idfactura = lf.idfactura and lf.referencia=lfac.referencia) as cantcompr, "
	        //." (select sum(pvptotal)/sum(cantidad) FROM lineasfacturasprov as lf1 "
	        //." where lfac.idfactura = lf1.idfactura and lf1.referencia=lfac.referencia) as preciounitario "
	        //." (select sum(pvptotal) FROM lineasfacturasprov as lf2 "
	        //." where lfac.idfactura = lf2.idfactura and lf2.referencia=lfac.referencia) as preciototal"
	        ." FROM lineasfacturasprov lfac, facturasprov fprov, articulos ar "
	        ." where ar.referencia=lfac.referencia "
	        ." and lfac.referencia = '". $prod['referencia']
	        ."' and lfac.idfactura = fprov.idfactura "
	        ." and sevende=1 "
	        ." and fecha is not null "
	        ." and lfac.pvptotal > 0.01"
	        //." and fecha > '".$fec_menor
	        ." and anulada = 0 "
	        //." GROUP BY lfac.referencia "
	        ." order by fprov.idfactura desc limit 1;");
	//, lfac.idfactura

	  // cantidad de unidades vendidas del producto
	/*
	      $list_venta = $this->db->select("SELECT codigo, referencia refer, descripcion, cantidad, fecha, hora,"
	        ."(select avg(costemedio) from articulos as a where a.referencia =  '".$prod['lisreferencia']."') costemedio, "
	        ."(select avg(preciocoste) from articulos as a where a.referencia =  '".$prod['lisreferencia']."' ) coste "
	        ." from lineasfacturascli as linfac, "
	        ." facturascli as faccli  where linfac.idfactura = faccli.idfactura and anulada = 0 "
	        ." and  linfac.referencia = '". $prod['lisreferencia']."' and anulada = 0 order by linfac.referencia, fecha;");

	  // CANTIDAD regulacion 

	      $list_regula = $this->db->select("SELECT (cantidadfin - cantidadini) cantregula, "
	        ."referencia, motivo descripcion, fecha, hora "
	        ." FROM stocks s, lineasregstocks l "
	        ." where referencia = '".$prod['lisreferencia']
	        ."' and s.idstock = l.idstock order by referencia, fecha;");
	*/


	  
	      if ($list_compra){
	        $saldo_a = $saldo_a + $lcom['cantcompr'];
	        foreach ($list_compra as $lcom) {
	          $preciotot = $lcom['preciounitario']*$prod['stockfis'];
	          $sql_insert = "insert into temp_caducados (id, tipo, codigo, referencia, descripcion, fecha, hora,"
	          ." cantidad, precompra, prectotal, saldo) "
	          ." values(".$k
	          .", 'COMPRA' "
	          .", '". $lcom['codigo'] ."'"
	          .", '". $lcom['refer']."'"
	          .", '". $lcom['descripcion']."'"
	          .", '". $lcom['fecha']."'"
	          .", '". $lcom['hora']."'"
	          .", ". $prod['stockfis']
	          .", ". bcdiv($lcom['preciounitario'],'1', 4)
	          .", ". bcdiv($preciotot,'1', 2)
	          .", ". $saldo_a.");";
	          //$this->new_message("COMPRA ".$sql_insert);
	          $guardar = $this->db->exec($sql_insert);
	          $k = $k+1;
	        }
	      }else
	      {

	        
	      }
		}
      $precost = 0;
      $cosmed  = 0;
      $costob  = 0;

/*
      if($list_venta){
      $costob = 0;
      foreach ($list_venta as $lcom1) {
      $saldo_a = $saldo_a - $lcom1['cantidad'];
      $precost = $lcom1['costemedio'];
      $cosmed  = $lcom1['coste'];
      $this->new_message($lcom1['refer'].' C medio '.$cosmed.' costo '.$precost);
      if ($precost > 0) $costob =  $precost;
      if ($cosmed > 0) $costob =  $cosmed;
      if ($precost > 0 and $cosmed > 0 )  $costob =  ($precost + $cosmed)/2;

      if ($costob == 0  ){
        $sqlcostcomp = $this->db->select("select avg(pvpunitario) pvpprom FROM lineasfacturasprov as lf1 "
        ." where lf1.referencia ='".$lcom1['refer']."'" );
        if($sqlcostcomp){
          foreach ($sqlcostcomp as $keyc) {
            $costob = $keyc['pvpprom'];
          }
          if($costob==null) $costob=0;
        }
      }

      $sql_insert = "insert into temp_caducados (id, tipo, codigo, referencia, descripcion, fecha, hora,"
      ." precpromed, cantidad, saldo, precompra) "
      ." values(".$k
      .", 'VENTA' "
      .", '". $lcom1['codigo'] ."'"
      .", '". $lcom1['refer']."'"
      .", '". $lcom1['descripcion']."'"
      .", '". $lcom1['fecha']."'"
      .", '". $lcom1['hora']."'"
      .", 0 " 
      .", ".$lcom1['cantidad'] 
      .", ". $saldo_a
      .", ". $costob.");";
      // $this->new_message("VENTA SQL ".$sql_insert);
      $guardar = $this->db->exec($sql_insert);
      $k = $k+1;
      }
      }
      $precost = 0;
      $cosmed  = 0;
      $costoa  = 0;


      /*if ($list_regula){
        $cosmed  =0;
        $saldo_a = $saldo_a - $lcom2['cantregula'];
        $sql_costomed = $this->db->select("select avg(costemedio) costemedio from articulos where referencia =  '".$prod['lisreferencia']."';") ;
        $sql_preciocost =$this->db->select("select avg(preciocoste) costemedio from articulos where referencia =  '".$prod['lisreferencia']."';") ;
        if ($sql_preciocost){
          foreach ($sql_preciocost as $pcost) {
            $precost = $pcost['costemedio'];
        }
        //$this->new_message($prod['lisreferencia']." precio coste ".$precost);
        }              
        if ($sql_costomed){
          foreach ($sql_costomed as $lcosm) {
            $cosmed = $lcosm['costemedio'];
        }
        //$this->new_message($prod['lisreferencia']." precio coste MEDIO ".$cosmed);
        }
        $costoa = 0;
        if ($precost > 0) $costoa =  $precost;
        if ($cosmed > 0) $costoa =  $cosmed;
        if ($precost > 0 and $cosmed > 0 )  $costoa =  ($precost + $cosmed)/2;
        if ($costoa == 0 ){

        //      $this->new_message("sql abs "."select avg(pvpunitario) pvpprom FROM lineasfacturasprov as lf1 "
        //      ." where lf1.referencia ='".$lcom2['referencia']."'");

          $sqlcostcomp = $this->db->select("select avg(pvpunitario) pvpprom FROM lineasfacturasprov as lf1 "
          ." where lf1.referencia ='".$lcom2['referencia']."'" );

          if($sqlcostcomp){
            foreach ($sqlcostcomp as $keyc) {
              $costoa = $keyc['pvpprom'];
            }
            if($costoa==null) $costoa=0;
          }
        }
        foreach ($list_regula as $lcom2) {
        $sql_insert = "insert into temp_caducados (id, tipo,  referencia, descripcion, fecha, hora,"
        ." precompra, prectotal, cantidad, saldo) "
        ." values(".$k
        .", 'REGULA' "
        .", '". $lcom2['referencia']."'"
        .", '". $lcom2['descripcion']."'"
        .", '". $lcom2['fecha']."'"
        .", '". $lcom2['hora']."'"
        .", ".$costoa
        .", ".$costoa * $lcom2['cantregula']
        .", ". $lcom2['cantregula']
        .", ".$saldo_a.");";
        //  $this->new_message("SQLsql ".$sql_insert);
        $guardar = $this->db->exec($sql_insert);
        $k = $k+1;
        }
      }*/
      }
      }
      }
      $k = $k+1;
  

  //} // fin de if
  //}
  /*      $this->lineas_stock = $this->db->select("select * from temp_caducados order by referencia, fecha;");

  $this->valor_stock = $this->db->select("select sum(prectotal) preciot  from temp_caducados;");
  foreach ($this->valor_stock as $valst) {
  $this->preciototal = bcdiv($valst['preciot'],'1', 2);
  //$this->precantregul =bcdiv( $val['preciocant'],'1', 2);
}*/
$k1= 1;
if ($list_productos) {

  //-------------------------------------------------------------------------------------------------------------
  $cont1=0;
  foreach ($list_productos as $tprod) {
    $cantidad  = 0;
    $precompra = 0;  
    $prectotal = 0;
    $saldo     = 0;
    $precpromed= 0;
    $prpromedio= 0;
    $precompunit= 0;
    $cnt_venta  = 0;
    $cnt_comp   = 0;
    $cnt_sale   = 0;
    $cnt_difer  = 0;
    $salida     = 0;
    $saldolinea = 0;
    $prectotlin = 0;
    $guardar    = "";
    $salida     = 0;

    $list_result = $this->db->select("select id, tipo, codigo, referencia,  descripcion, fecha, hora, cantidad,"
      ." precompra, prectotal, saldo, precpromed from temp_caducados where referencia ='".$tprod['referencia'] 
      ."' order by referencia, fecha ;");

  //         $this->new_message("TABLA TEMP_CADUCADOS ");
  // foreach ($list_result as $val) {
  //    $this->new_message($val['id']." Referencia ".$val['referencia']." Cantidad ".$val['cantidad']." Saldo".$val['saldo']);
  // }
  //  $this->new_message("una vez por producto");
     /* if ($list_result){
      foreach ($list_result as $val) {
      if($val['tipo']=='REGULA'){

          $saldo = $saldo + $val['cantidad'];

          $precompunit = $precpromed;
          $cantidad = $val['cantidad'];
          $prectotal = $cantidad * $val['precompra'];
      //$this->new_message('referncia '.$val['referencia']." saldo ".$saldo);
          $sql_insert = "insert into temp_producto (id,  codigo, tipo,  referencia, fecha, hora, salida,precompra,cantidad,prectotal,saldo ) "
          ." values(".$k1
          .", '". $val['codigo']."'"
          .", '". $val['tipo']."'"
          .", '". $val['referencia']."'"
          .", '". $val['fecha']."'"
          .", '". $val['hora']."'"
          .", 0"
          .", ". $val['precompra']
          .", ". $val['cantidad']
          .", ". $prectotal
          .", ".$saldo.");";

          $guardar = $this->db->exec($sql_insert);
          $k1 = $k1 + 1;
        }
        if($val['tipo']=='COMPRA'){
          $acumcompra = $acumcompra + $val['cantidad'];
          $saldo = $saldo + $val['cantidad'];

          $cantidad = $val['cantidad'];
          $precompra =  $val['precompra'];
          $precpromed = $val['precompra'];
          $prectotal = $cantidad * $precompra;
          $salida = 0;

          $sql_insert = "insert into temp_producto (id, codigo, tipo,  referencia, fecha, hora, salida,cantidad, precompra, prectotal, saldo ) "
          ." values(".$k1
          .", '". $val['codigo']."'"
          .", 'COMPRA' "
          .", '". $val['referencia']."'"
          .", '". $val['fecha']."'"
          .", '". $val['hora']."'"
          .", ". $salida
          .", ". $val['cantidad']
          .", ". $val['precompra']
          .", ". $prectotal 
          .", ".$saldo.");";
          $guardar = $this->db->exec($sql_insert);
          $k1 = $k1 + 1;
        }
        if($val['tipo']=='VENTA'){

          $acumcompra = $acumcompra + $val['cantidad'];
          $saldo = $saldo - $val['cantidad'];

     // if($val['referencia'] == 'MO-0005'){
     //   $this->new_message($k1.'VENTA--cantidad : '.$val['cantidad']." Saldo ".$saldo );
     // }                    

          $cantidad = $val['cantidad'];
          $precompra =  $val['precompra'];
          $precpromed = $val['precompra'];
          $prectotal = $cantidad * $precompra;
          $salida = $cantidad ;

          $sql_insert = "insert into temp_producto (id, codigo, tipo,  referencia, fecha, hora, salida,cantidad, precompra, prectotal, saldo ) "
          ." values(".$k1
          .", '". $val['codigo']."'"
          .", '". $val['tipo']."'"
          .", '". $val['referencia']."'"
          .", '". $val['fecha']."'"
          .", '". $val['hora']."'"
          .", ". $salida
          .", ". $val['cantidad']
          .", ". $val['precompra']
          .", ". $prectotal 
          .", ".$saldo.");";
      //$this->new_message('insert '.$sql_insert);
          $guardar = $this->db->exec($sql_insert);
          $k1 = $k1 + 1;



        }
      }
    }*/
  }
  /*$this->new_message("saldo ".$saldo);
  $this->new_message("cantidad ".$cantidad);
  $this->new_message("precompra ".$precompra);
  $this->new_message("prectotal ".$prectotal);
  */
  $this->lineas_stock = $this->db->select("select * from temp_producto order by referencia, fecha;");

  $this->valor_stock = $this->db->select("select sum(prectotal) preciot  from temp_producto;");
  $this->preciototal = 0;
  foreach ($this->valor_stock as $valst) {
    $this->preciototal = bcdiv($valst['preciot'],'1', 2);
  //$this->precantregul =bcdiv( $val['preciocant'],'1', 2);
  }


  }  
}



      
      

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

      }      
      

   }
//// ****************************************************************************************************************

       private function imprime_inventario()
    {
      set_time_limit(0);

      $fecini =  date("y-m-d", strtotime($_POST['fechafin3']));

      if (1==2) 
    {
        
      }
       else
      {
      
           
      // seleccion de todos los registros que entran en el periodo 
      // Con detalle de facturas de venta
      $num_res =100;
      $agentecod = $this->agente->codagente;
         $resultado = $this->db->select("SELECT * FROM temp_caducados ORDER BY referencia ;");
         $num_resul = $this->db->select("SELECT count(*) nresul FROM temp_caducados   ;");
         if($num_resul){
            foreach ($num_resul as $key) {
              $num_res = $key['nresul'] + 4;  
            }

         }                 
         $this->new_message("registros ".$num_res);
         //. "and codigorect is null and anulada = 0 and codagente = ". $agentecod." ;");
  $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("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());
  
  
  $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('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', 'INVENTARIO ALMACENES VIP');
  $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Fecha:');
  $objPHPExcel->getActiveSheet()->setCellValue('B3', date("j/n/Y") );
  $objPHPExcel->getActiveSheet()->setCellValue('D3', 'Inventario Compra sin IVA');
  $objPHPExcel->getActiveSheet()->setCellValue('A4','Ord.');        
  $objPHPExcel->getActiveSheet()->setCellValue('B4','Referencia');
  $objPHPExcel->getActiveSheet()->setCellValue('C4','Descripción');
  $objPHPExcel->getActiveSheet()->setCellValue('D4','Cantidad');
  $objPHPExcel->getActiveSheet()->setCellValue('E4','Prec.Unitario');
  $objPHPExcel->getActiveSheet()->setCellValue('F4','Prec.Total');
  //$objPHPExcel->getActiveSheet()->setCellValue('H4','Salida');
  //$objPHPExcel->getActiveSheet()->setCellValue('I4','Saldo');
  //$objPHPExcel->getActiveSheet()->setCellValue('J4','Saldo Usd');
  

  //$objPHPExcel->getActiveSheet()->mergeCells('b1:E1');
 
  //$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('A1:F2');
  //$objPHPExcel->getActiveSheet()->mergeCells('A3:F3');

        

        
        // COLUMNAS        

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
      $agentecod = $this->agente->codagente;
      $nomagente = $this->agente->nombre." ".$this->agente->apellidos;     
      //$objPHPExcel->getActiveSheet()->setCellValue('b1', "Vendedor:   ".$nomagente );
        $nregis=1;
  if ($resultado){
    $s_total = 0;
    $ws=0;
    $f=1;
    $filr = 0;
    $saldo_t = 0;
  foreach($resultado as $rows)  {
    //$f=$f+1;
    if($ref !== $rows['referencia']){
      $ref = $rows['referencia'];
      $ws = 0;
      
      $filref = $this->db->select("SELECT count(*) filref1 FROM temp_caducados WHERE referencia ='".$ref."';");      
      if ($filref){
        foreach ($filref as $keyf) {
         $filr =  $keyf['filref1']; 
        }
      }
    }

      $s_total = $s_total + $rows['prectotal'];

    //$this->new_message('refer '.$ref.' Cpr Unit '.$rows['precompra'].' CANTID '.$rows['cantidad'].' Tot usd'.$rows['prectotal'].' SALDO '. $rows['saldo'].' SALDO TOTAL '. $s_total);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A'.$fila, $nregis);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$fila, $rows['referencia']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, $rows['descripcion']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$fila, $rows['cantidad']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$fila, $rows['precompra']);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $rows['prectotal']);
      //$objPHPExcel->getActiveSheet()->setCellValue('H'.$fila, $rows['salida']);
      //$objPHPExcel->getActiveSheet()->setCellValue('I'.$fila, $rows['saldo']);

      //$objPHPExcel->getActiveSheet()->setCellValue('k'.$fila, $s_total);
      //$objPHPExcel->getActiveSheet()->setCellValue('L'.$fila, $saldo_t);
      $f = 0 ;    
 
    //$this->new_message(' saldo usd tot  '.$s_total);
    $f += 1 ;  
    $fila++; //Sumamos 1 para pasar a la siguiente fila
    $nregis++;

  }
  $objPHPExcel->getActiveSheet()->setCellValue('C'.$fila, 'TOTAL INVENTARIO');
  $objPHPExcel->getActiveSheet()->setCellValue('F'.$fila, $s_total);

  }


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

//*/     

        

   }
   }   


}







