<?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/stockvalorizadoventa/cStockvalorizadoventa.php");
$oStockvalorizadoventa = new cStockvalorizadoventa();

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


$punven_id = $_GET['cmb_fil_puntoventa_id'];
if($punven_id>0)
{

$dts1=$oStockvalorizadoventa->mostrar_filtro(fecha_mysql($_GET['txt_fil_valven_fec1']),fecha_mysql($_GET['txt_fil_valven_fec2']),
	$_GET['txt_fil_pro_dat1'],$_GET['txt_fil_pro_dat2'],$_GET['cmb_fil_cat_id'],$_GET['txt_fil_pro_mar'],$punven_id);
	
	$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(
      'FECHA',
      'DOCUMENTO',
  'CODIGO / CANTIDAD',
  'NOMBRE',
  'MARCA',
  'CATEGORIA', 
  'P. COSTO UNITARIO',
  'P. VENTA UNITARIO',
  'P. COSTO TOTAL',
  'P. VENTA TOTAL',
  'DIFERENCIA V-C'
  );

  /*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>0){
	$c=$c+1;
   
   while($dt1 = mysqli_fetch_array($dts1)){
		$emp_fac = $dt1['tb_empaque_fac'];							
	$preuni = $dt1['tb_ventadetalle_preuni'];
	$canven = $dt1['tb_ventadetalle_can'];
	
	if($canven>=$emp_fac){
		$preuni = $dt1['tb_ventadetalle_preuni']/$emp_fac;
	}
	
	$totpreven = $dt1['tb_ventadetalle_can'] * $preuni;	
				
									$dts2=$oStockvalorizadoventa->mostrarkardex_producto(fecha_mysql($dt1['tb_venta_fec']),$dt1['tb_catalogo_id'],$dt1['tb_marca_id']);
									$numrows = mysqli_num_rows($dts2);
									
									$com_preuni = 0;
									if($numrows>0){
										
										$dt2 = mysqli_fetch_array($dts2);
										$kar_idm=$dt2['tb_kardex_idm'];
										mysqli_free_result($dts2);
										
										$dts3=$oStockvalorizadoventa->mostrarcompra_producto($kar_idm,$dt1['tb_catalogo_id'],$dt1['tb_marca_id']);
										$dt3 = mysqli_fetch_array($dts3);
										$com_igv=$dt3['tb_compra_igvsub'];
										$cancom = $dt3['tb_compradetalle_can'];
										$com_preuni=$dt3['tb_compradetalle_preuni'] * $com_igv;
										mysqli_free_result($dts3);
										
										
										if($cancom>=$emp_fac){
									
											$com_preuni = $com_preuni / $emp_fac;
									
										}else{
											$com_preuni = $com_preuni;
										}
			
			
			
									
									}else{
										
										$com_preuni = 0;
									}
									
									
									
	$totprecos = $com_preuni * $dt1['tb_ventadetalle_can'];
									
	if($totprecos>0){					
		$diff = $totpreven - $totprecos;
	}else{
		$diff = 0;
	}
		
    $objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, mostrarFecha($dt1['tb_venta_fec']))
    ->setCellValue('B'.$c, $dt1['tb_venta_numdoc'])
    ->setCellValue('C'.$c, $dt1['tb_ventadetalle_can'])
    ->setCellValue('D'.$c, $dt1['tb_producto_nom'])
    ->setCellValue('E'.$c, $dt1['tb_marca_nom'])    
    ->setCellValue('F'.$c, $dt1['tb_categoria_nom'])
    ->setCellValue('G'.$c, formato_money($com_preuni))
    ->setCellValue('H'.$c, formato_money($preuni) )
	->setCellValue('I'.$c, formato_money($totprecos))
	->setCellValue('J'.$c, formato_money($totpreven))
	->setCellValue('K'.$c, formato_money($diff));


    $objPHPExcel->getActiveSheet(0)->getStyle("G$c:K$c")->getNumberFormat()->setFormatCode('0.00');
    $c++;
	
	$total_com_preuni += $com_preuni;
	$total_preuni += $preuni;
	$total_com_precos += $totprecos;
	$total_preven += $totpreven;
	$total_diff += $diff;

   }
  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, '')
    ->setCellValue('F'.$c, '')
    ->setCellValue('G'.$c, formato_money($total_com_preuni) )
    ->setCellValue('H'.$c, formato_money($total_preuni) )
    ->setCellValue('I'.$c, formato_money($total_com_precos) )
    ->setCellValue('J'.$c, formato_money($total_preven) )
    ->setCellValue('K'.$c, formato_money($total_diff) );
	
$objPHPExcel->getActiveSheet(0)->getStyle("G$c:K$c")->getNumberFormat()->setFormatCode('0.00');

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


$nombre_archivo="REPORTE VALORIZADO VENTAS ".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;


?>

