<?php

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



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



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

$oProducto = new cProducto();



require_once ("../producto/cCatalogo.php");

$oCatalogo = new cCatalogo();



require_once ("../compra/cCompra.php");

$oCompra = new cCompra();



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

$oStock = new cStock();



require_once("../producto/cProductomarcacodigo.php");

$oProductomarcacodigo = new cProductomarcacodigo();



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

$oProductomarca = new cProductomarca();



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



$dts1=$oCatalogo->mostrarReporte();



/*while ($dts11 = mysql_fetch_array($dts1)) {

  $nom = $dts11['tb_venta_nom'];

}*/

$num_rows= mysql_num_rows($dts1);



// Create new PHPExcel object

$objPHPExcel = new PHPExcel();



// Set document properties

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

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

->setTitle("Reporte consolidado general")

->setSubject("Reporte")

->setDescription("Reporte generado por https://vyfrepuestos.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(

  'FAMILIAS',

  'CODIGO GENERAL',

  'CODIGOS MARCA',

  'MARCA', 

  'DESCRIPCION',

  'PROVEEDOR',

  'ULTIMA COMPRA',

  'PRECIO MAXIMO S/',

  'PRECIO MINIMO S/',

  'PRECIO MAXIMO US$',

  'PRECIO MINIMO US$',

  'STOCK TOTAL'

  );



  /*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 = mysql_fetch_array($dts1))

    {

	

	

	

	$dtsmar=$oProductomarcacodigo->mostrar_filtro($dt1['tb_productomarca_id']);

	$num_rowsmar= mysql_num_rows($dtsmar);



	$codmar = '';

	

	if($num_rowsmar!=0){

	while($dtmar = mysql_fetch_array($dtsmar)){

		$codmar.= $dtmar['tb_productomarcacodigo_cod'].' | ';

	}

	}else{

		$codmar = 'SIN CODIGOS';	

	}

	

	

	$dtscomsol = $oCompra->mostrar_compra_precio($dt1['tb_catalogo_id'],$dt1['tb_marca_id'],'1');

	$dtcomsol = mysql_fetch_array($dtscomsol);

	$premay_soligv = $dtcomsol['premay'] * 0.18;

	$premin_soligv	= $dtcomsol['premin'] * 0.18;

	

	$premay_sol = $dtcomsol['premay'] + $premay_soligv;

	$premin_sol	= $dtcomsol['premin'] + $premin_soligv;

	

	$dtscomdol = $oCompra->mostrar_compra_precio($dt1['tb_catalogo_id'],$dt1['tb_marca_id'],'2');

	$dtcomdol = mysql_fetch_array($dtscomdol);

	$premay_doligv = $dtcomdol['premay'] * 0.18;

	$premin_doligv	= $dtcomdol['premin'] * 0.18;

	

	$premay_dol = $dtcomdol['premay'] + $premay_doligv;

	$premin_dol	= $dtcomdol['premin'] + $premin_doligv;

	

	$precad = '';

	$prov = '';

	$lbl= '';

	

	$dtscompro = $oCompra->mostrar_todosprovedores($dt1['tb_catalogo_id'],$dt1['tb_marca_id']);

	while($dtcompro = mysql_fetch_array($dtscompro)){

		

		

		$dtscom = $oCompra->mostrar_ultimacompra($dt1['tb_catalogo_id'],$dt1['tb_marca_id'],$dtcompro['tb_proveedor_id']);

		$dtcom = mysql_fetch_array($dtscom);	

		$igv = $dtcom['tb_compradetalle_preuni'] * 0.18;

		$preuni = $dtcom['tb_compradetalle_preuni'] + $igv;

		$mon = $dtcom['tb_moneda_id'];

		

		if($mon == '1'){

			$lbl = ' S/';

		}else if($mon == '2'){

			$lbl = ' US$';

		}

		

		$precad .= $preuni.$lbl.' | ';

		$prov .= $dtcompro['tb_proveedor_alias'].' | ';

	};

	

	

	$dtsfam=$oProducto->mostrarFamilia($dt1['tb_catalogo_id']);

	$num_rowsfam= mysql_num_rows($dtsfam);	

	

	$familias ='';

	if($num_rowsfam!=0){

	while($dtfam = mysql_fetch_array($dtsfam)){

		$familias.= $dtfam['tb_familia_nom'].' | ';

	}

	}else{

		$familias = 'SIN FAMILIAS';	

	}

	

	$rscod = $oProductomarcacodigo->mostrarUno($dt1['tb_catalogo_id'],'1');

	$dtcod = mysql_fetch_array($rscod);

			

	$codpri = $dtcod['tb_productomarcacodigo_cod'];

	

	$rsstock = $oStock->stock_por_presentacion_todos($dt1['tb_presentacion_id'],$dt1['tb_marca_id']);

	$dtstock = mysql_fetch_array($rsstock);

			

	$tot_stock = $dtstock['tot_stock'];

	

	

		

    $objPHPExcel->getActiveSheet(0)

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

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

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

    ->setCellValue('D'.$c, $dt1['tb_marca_nom'])

    ->setCellValue('E'.$c, $dt1['tb_producto_des'])   

    ->setCellValue('F'.$c, $prov)

    ->setCellValue('G'.$c, $precad)

    ->setCellValue('H'.$c, formato_money($premay_sol))

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

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

    ->setCellValue('K'.$c, formato_money($premin_dol))

    ->setCellValue('L'.$c, $tot_stock);

    //->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++;



  }

  mysql_free_result($dts1);

  

}



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

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

}





/*

 if($_SESSION['usuariogrupo_id']==2){



   $objPHPExcel->getActiveSheet()

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

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

      ->setCellValue('F'.$c, formato_money($total_abono))

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

      ->setCellValue('H'.$c, &nbsp;)   

      ->setCellValue('I'.$c, &nbsp;)

      ->setCellValue('J'.$c, &nbsp;)



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



}

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

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

}



$objPHPExcel->getActiveSheet()->freezePane('A2');*/

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





$nombre_archivo="REPORTE CONSOLIDADO 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;





?>



