<?php



// ini_set('display_errors',1);



session_start();



date_default_timezone_set('America/Lima');



if (PHP_SAPI == 'cli')

  die('Se visualiza solamente desde un navegador.');



require_once dirname(__FILE__) . '/../../libreriasphp/phpexcel/Classes/PHPExcel.php';



//ini_set('display_errors',1);

require_once ("../../config/Cado.php");

require_once ("../../app/producto/cProducto.php");

$oProducto = new cProducto();

require_once ("../../app/stock/cStock.php");

$oStock = new cStock();

require_once("../../app/producto/cProductomarca.php");

$oProductomarca = new cProductomarca();

require_once ("../../app/formatos/formato.php"); 



$alm_id = $_GET['cmb_fil_alm_id'];

if($alm_id>0)

{

$dts1=$oProducto->mostrar_filtro($_GET['txt_fil_pro_dat1'],$_GET['txt_fil_pro_dat2'],$_GET['txt_fil_pro_dat4'],$_GET['dat4'],

'','',$_GET['txt_fil_pro_mar'],$_GET['codmar'],'');

$num_rows= mysqli_num_rows($dts1);

}

else

{

	$num_rows=0;

}





// Create new PHPExcel object

$objPHPExcel = new PHPExcel();



// Set document properties

$objPHPExcel->getProperties()->setCreator("https://infosisperu.com")

->setLastModifiedBy("https://infosisperu.com")

->setTitle("Reporte stock valorizado")

->setSubject("Reporte")

->setDescription("Reporte generado por https://infosisperu.com")

->setKeywords("")

->setCategory("reporte excel");



$objPHPExcel->setActiveSheetIndex(0);

$c=1;

$estiloTituloColumnas = array(

  'font' => array(

    'name'  => 'Arial',

    'bold'  => true,

    'size'  =>8,

    'color' => array(

      'rgb' => '000000'

      )

    ),

  'fill' => array(

    'type'  => PHPExcel_Style_Fill::FILL_SOLID,

    'color' => array(

      'rgb' => 'FAFAFA')

    ),

  'borders' => array(

    'outline' => array(

      'style' => PHPExcel_Style_Border::BORDER_THIN ,

      'color' => array(

        'rgb' => '143860'

        )

      )

    ),

  'alignment' =>  array(

    'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

    'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,

    'wrap'      => TRUE

    )

  );

  $titulosColumnas = array(

  'CODIGO',

  'NOMBRE',

  'MARCA',

  'CATEGORIA', 

  'P. COSTO',

  'UTILIDAD',

  'P. VENTA',

  'STOCK',

  'VALORIZADO COSTO',

  'VALORIZADO VENTA',

  'TOTAL UTILIDAD (P. VENTA)'

  );



  /*foreach ($nom as $val) {

      array_push($titulosColumnas, $val);

  }*/



  $letras = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');



  $letracolumn = 0;

  

  for ($i=0; $i < count($titulosColumnas) ; $i++) { 

    $letracolumn = $i;

    $objPHPExcel->getActiveSheet()->setCellValue($letras[$i].$c, $titulosColumnas[$i]);

  }

  $objPHPExcel->getActiveSheet()->getStyle("A$c:".$letras[$letracolumn]."$c")->applyFromArray($estiloTituloColumnas);

	

   if($num_rows>=1){

	$c=$c+1;

   while($dt1 = mysqli_fetch_array($dts1)){

									

							$rs = $oProductomarca->mostrar_filtro($dt1['tb_catalogo_id']);



							$row= mysqli_num_rows($rs);



							while($dt = mysqli_fetch_array($rs)) {



							$stock = mysqli_fetch_array($oStock->stock_por_presentacion($dt['tb_presentacion_id'],$dt['tb_marca_id'],$alm_id))['tb_stock_num'];



									$precio_min = 0; $precio_may = 0;

									$stock_unidad=$stock;

									$valorizado_cos_dol=0;

									$valorizado_cos = 0; $valorizado_ven = 0; $valorizado_min = 0; $valorizado_may = 0; $valorizado_uti = 0;

									$total_costo += $dt['tb_productomarca_precos'];

									$total_utilidad_porcen += utilidad($dt['tb_productomarca_precos'],$dt['tb_productomarca_preven']);

									$total_precio_ven += $dt['tb_productomarca_preven'];

									$total_stock += $stock_unidad;

									$valorizado_cos = $stock_unidad*$dt['tb_productomarca_precos'];

									$valorizado_ven = $stock_unidad * $dt['tb_productomarca_preven'];

									$valorizado_uti = $valorizado_ven - $valorizado_cos;

									$total_valorizado_cos += $valorizado_cos;

									$total_valorizado_ven += $valorizado_ven;

									$total_utilidad += $valorizado_uti; 

		

    $objPHPExcel->getActiveSheet(0)

    ->setCellValue('A'.$c, $dt['tb_producto_id'])

    ->setCellValue('B'.$c, trim($dt1['tb_producto_nom']))    

    ->setCellValue('C'.$c, $dt['tb_marca_nom'])

    ->setCellValue('D'.$c, $dt['tb_categoria_nom']) 

    ->setCellValue('E'.$c, formato_money($dt['tb_productomarca_precos']))

    ->setCellValue('F'.$c, utilidad($dt['tb_productomarca_precos'],$dt['tb_productomarca_preven']))

    ->setCellValue('G'.$c, formato_money($dt['tb_productomarca_preven']))

    ->setCellValue('H'.$c, $stock_unidad * 1)

    ->setCellValue('I'.$c, formato_money($valorizado_cos))

    ->setCellValue('J'.$c, formato_money($valorizado_ven))

    ->setCellValue('K'.$c, $valorizado_uti);

    //->setCellValue('J'.$c, $array_stock_almacenes[1])

    //->setCellValue('K'.$c, $array_stock_almacenes[2])

    //->setCellValue('L'.$c, $array_stock_almacenes[3])

    //->setCellValue('M'.$c, $array_stock_almacenes[4]);



    $objPHPExcel->getActiveSheet(0)->getStyle("E$c:I$c")->getNumberFormat()->setFormatCode('0.00');

    $c++;



   }

   }

  mysqli_free_result($dts1);

  

}



for($z = 'A'; $z <= 'L'; $z++){

  $objPHPExcel->getActiveSheet()->getColumnDimension($z)->setAutoSize(TRUE);

}



 $objPHPExcel->getActiveSheet(0)

    ->setCellValue('A'.$c, '')

    ->setCellValue('B'.$c, '')    

    ->setCellValue('C'.$c, '')

    ->setCellValue('D'.$c, '') 

    ->setCellValue('E'.$c, formato_money($total_costo))

    ->setCellValue('F'.$c, $total_utilidad_porcen.' %')

    ->setCellValue('G'.$c, formato_money($total_precio_ven))

    ->setCellValue('H'.$c, $total_stock * 1)

    ->setCellValue('I'.$c, formato_money($total_valorizado_cos))

    ->setCellValue('J'.$c, formato_money($total_valorizado_ven))

    ->setCellValue('K'.$c, $total_utilidad);

    //->setCellValue('J'.$c, $array_stock_almacenes[1])

    //->setCellValue('K'.$c, $array_stock_almacenes[2])

    //->setCellValue('L'.$c, $array_stock_almacenes[3])

    //->setCellValue('M'.$c, $array_stock_almacenes[4]);



$objPHPExcel->getActiveSheet(0)->getStyle("E$c:I$c")->getNumberFormat()->setFormatCode('0.00');



$objPHPExcel->getActiveSheet()->setTitle('STOCK VALORIZADO VENTAS');





$nombre_archivo="REPORTE STOCK VALORIZADO GENERAL ".date('d-m-Y H-i:s');



// Redirect output to a client’s web browser (Excel5)

//header('Content-Type: application/vnd.ms-excel');

//header('Content-Disposition: attachment;filename="'.$nombre_archivo.'.xls"');



// Redirect output to a client’s web browser (Excel2007)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

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



header('Cache-Control: max-age=0');

// If you're serving to IE 9, then the following may be needed

header('Cache-Control: max-age=1');



// If you're serving to IE over SSL, then the following may be needed

header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

header ('Last-Modified: '.date('D, d M Y H:i:s').' GMT'); // always modified

header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1

header ('Pragma: public'); // HTTP/1.0



//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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



$objWriter->save('php://output');

exit;





?>



