<?php
if (PHP_SAPI == 'cli')
  die('Se visualiza solamente desde un navegador.');
require_once ('../../libreriasphp/phpexcel/Classes/PHPExcel.php');
date_default_timezone_set('America/Lima');
session_start();
require_once ("../../config/Cado.php");
require_once ("../../app/productobonificacion_cst/cProductobonificacion_cst.php");
$oProductobonificacion_cst = new cProductobonificacion_cst();
$fec = date('Y-m-d');
require_once ("../../app/formatos/formato.php");
require_once ("../../app/formatos/fechas.php");
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("https://infosisperu.com")
->setLastModifiedBy("https://infosisperu.com")
->setTitle("REPORTE DE BONIFICACIONES")
->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
    )
  );
  
  $estiloTitulos = array(
  'font' => array(
    'name'  => 'Arial',
    'bold'  => true,
    'size'  =>8,
    'color' => array(
      'rgb' => 'FFFFFF'
      )
    ),
  'fill' => array(
    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
      'rgb' => 'FF8800')
    ),
  '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
    )
  );
  
  $estiloTotales = array(
  'font' => array(
    'name'  => 'Arial',
    'bold'  => true,
    'size'  =>8,
    'color' => array(
      'rgb' => 'FFFFFF'
      )
    ),
  'fill' => array(
    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
      'rgb' => '0d47a1')
    ),
  '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
    )
  );
  
  $titulos = array(
  'FECHA',
  'VENDEDOR',
  'COD. INT.',
  'PRODUCTO',
  'LABORATORIO',
  'CAN', 
  'PRE. VEN. UNI.',
  'PRE. BON. UNI.',
  'PRE. VEN. TOT.',
  'PRE. BON. TOT.'
  );
  
  $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');
  
	$objPHPExcel->getActiveSheet()->setCellValue("A$c", 'REPORTE DE BONIFICACIONES');
	//$objPHPExcel->getActiveSheet()->margeCells("A1:G1");
	$objPHPExcel->getActiveSheet()->getStyle("A$c:I$c")->applyFromArray($estiloTitulos);
	
	$c = $c+1;
  
  $letracolumn = 0;
  
  for ($i=0; $i < count($titulos) ; $i++) { 
    $letracolumn = $i;
    $objPHPExcel->getActiveSheet()->setCellValue($letras[$i].$c, $titulos[$i]);
	}
	
	$objPHPExcel->getActiveSheet()->getStyle("A$c:".$letras[$letracolumn]."$c")->applyFromArray($estiloTituloColumnas);
		$c = $c+1;
 
 
 
 
$dtsa=$oProductobonificacion_cst->mostrar_filtrocondetalles($_GET['usu_id'],$_GET['punven_id'],
fecha_mysql($_GET['fec1']),fecha_mysql($_GET['fec2']),$_GET['mon_id']);

$num_rows= mysqli_num_rows($dtsa);

	if($num_rows>0){
	
	while($dt1 = mysqli_fetch_array($dtsa))
	{ 			

		$can = $dt1['tb_ventadetalle_can'] * 1;
		$prebon = formato_money4($dt1['tb_producto_bonpre']*1); 
		$preven = formato_money4($dt1['tb_ventadetalle_preuni']*1); 
		$preventot = formato_money4($dt1['tb_ventadetalle_preuni']) * $can; 
		$prebontot = formato_money4($dt1['tb_producto_bonpre']) * $can; 
		
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, mostrarFecha($dt1['tb_venta_fec']))
    ->setCellValue('B'.$c, $dt1['tb_usuario_nom'])
     ->setCellValue('C'.$c, $dt1['tb_producto_codint'])
    ->setCellValue('D'.$c, $dt1['tb_producto_nom'])
    ->setCellValue('E'.$c, $dt1['tb_marca_nom'])
    ->setCellValue('F'.$c, $dt1['tb_ventadetalle_can'] * 1)
    ->setCellValue('G'.$c, formato_money($preven))
    ->setCellValue('H'.$c, formato_money($prebon))   
    ->setCellValue('I'.$c, formato_money($preventot))   
    ->setCellValue('J'.$c, formato_money($prebontot));
	
    $objPHPExcel->getActiveSheet(0)->getStyle("G$c:J$c")->getNumberFormat()->setFormatCode('0.00');
    $c++;
	
	
	$totcan += $can;
		$sumpreven += $preven;
		$sumprebon += $prebon;
		$sumpreventot += $preventot;
		$sumprebontot += $prebontot;
		
	}
	mysqli_free_result($dtsa);
	
	}else{
		$objPHPExcel->getActiveSheet()->setCellValue("A$c", 'SIN DATOS');
	}
		
		    $objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, '')
    ->setCellValue('B'.$c, '')    
    ->setCellValue('C'.$c, '')
    ->setCellValue('D'.$c, '')
    ->setCellValue('E'.$c, '')
    ->setCellValue('F'.$c, '')
    ->setCellValue('G'.$c, '')   
    ->setCellValue('H'.$c, '')   
    ->setCellValue('I'.$c, '')
    ->setCellValue('J'.$c, '');
	
	   $c++;
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, '')
    ->setCellValue('B'.$c, '')    
    ->setCellValue('C'.$c, '')
    ->setCellValue('D'.$c, '')
    ->setCellValue('E'.$c, '')
    ->setCellValue('F'.$c, formato_money($totcan))
    ->setCellValue('G'.$c, formato_money($sumpreven))
    ->setCellValue('H'.$c, formato_money($sumprebon))   
    ->setCellValue('I'.$c, formato_money($sumpreventot))   
    ->setCellValue('J'.$c, formato_money($sumprebontot));
	
     $c++;
    
    
    

    
    
    
	
	
	
for($z = 'A'; $z <= 'L'; $z++){
  $objPHPExcel->getActiveSheet()->getColumnDimension($z)->setAutoSize(TRUE);
}
	
	$objPHPExcel->getActiveSheet()->setTitle('REPORTE DE BONIFICACIONES');
	
	$nombre_archivo="REPORTE DE BONIFICACIONES ".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;
?>