<?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/controller/ventas_articulo.php';
require_once 'plugins/facturacion_base/extras/fbase_controller.php';

class varias_t extends fbase_controller /* fs_controller*/
{
public $b_codfamilia;
PUBLIC $f_idtrans;
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('varias_t', 'Varias tareas', 'Formularios EC');
   }
   
   protected function private_core()
   {


      $this->stock = new stock();
      $almacen = new almacen();
      $this->almacenes = $almacen->all();
      $this->articulo = new articulo();
      $this->stock = new stock();
      $this->transferencias = $this->db->select("select * from transstock;");
      // print_r($this->transferencias);
      // var_dump($this->transferencias);
      $this->tfamilia = new familia();

      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($_GET['table']) )
      {
         $this->export_structure_xml($_GET['table']);
      }
      else if( isset($_POST['fechafinC']) )
      {
         $this->trans_familia();
      }
 }

   private function trans_familia()
   { 
  //$fecini =  date("y-m-d", strtotime($_POST['fechainiC']));
    $fecfin =  date("Y-m-d", strtotime($_POST['fechafinC']));
   // $familia =  $_POST['f_idtrans'];

        $codfamilia = NULL;
        $sql_familia ="1=2";
        if ($_POST['b_codfamilia'] != '') {
            $this->codfamilia = $_POST['b_codfamilia'];
            $sql_familia = " codfamilia = '".$this->codfamilia."' and stockfis > 0";
        }
    
$this->new_message('FAMILIA '.$sql_familia);
  if ($sql_familia) {
    $this->linea_transf = $this->db->select("select * from articulos where $sql_familia;");
$this->new_message('sql '."select * from articulos where $sql_familia;");

  	$pvpp = 0;
    $idtrans = 1;
    foreach ($this->linea_transf as $prod) {
         $sql_insert = "insert into lineastransstock (idtrans, referencia,descripcion,cantidad) values("
         . $idtrans . ",'". $prod['referencia']."','". $prod['descripcion']."',".$prod['stockfis'].");";
         $guardar = $this->db->exec($sql_insert);
        // $x = $this->calcular_stock_real($prod['referencia']);
    }

      }
      


}

    // private function calcular_stock_real(&$articulo)
    // {
    //     //if ($articulo->nostock === FALSE) {
    //         foreach ($this->almacenes as $alm) {
    //             $total = 0;
    //             foreach ($this->recalcular_stock->get_movimientos($articulo->referencia, $alm->codalmacen) as $mov) {
    //                 $total = $mov['final'];
    //             }

    //             if (!$articulo->set_stock($alm->codalmacen, $total)) {
    //                 $this->new_error_msg('Error al recarcular el stock del artículo ' . $articulo->referencia
    //                     . ' en almacén ' . $alm->codalmacen . '.');
    //             }
    //         }
    //     //}
    // }

      
      

    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');
header('Content-Type: application/json');
// It will be called file.xls
header('Content-Disposition: attachment; filename="'.$nomarch.'"');

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

//*/     

        

   }
   }   


}







