<?php

session_start();

date_default_timezone_set('America/Lima');



if (PHP_SAPI == 'cli')

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



/** Include PHPExcel */

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



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



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

$oCatalogo = new cCatalogo();

require_once ("../almacen/cAlmacen.php");

$oAlmacen = new cAlmacen();

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

$oStock = new cStock();

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

$oPrecio = new cPrecio();



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







//almacenes array

$dts=$oAlmacen->filtrar_x_empresa($_SESSION['empresa_id']);

while($dt = mysql_fetch_array($dts)){

	$alm_id[]=$dt['tb_almacen_id'];

	$alm_nom[]=$dt['tb_almacen_nom'];

}

mysql_free_result($dts);



$codbar =	$_POST['txt_fil_pro_codbar'];

$pronom =	$_POST['txt_fil_pro_nom'];

$cat_id =	$_POST['cmb_fil_cat_id'];

$mar_id =	$_POST['cmb_fil_mar_id'];



$dts1=$oCatalogo->catalogo_stock_general($nom,$codbar,$cat_id,$mar_id);

$num_rows= mysql_num_rows($dts1);



// Create new PHPExcel object

$objPHPExcel = new PHPExcel();



// Set document properties

$objPHPExcel->getProperties()->setCreator("Inticap.com")

->setLastModifiedBy("Inticap.com")

->setTitle("Reporte")

->setSubject("Reporte")

->setDescription("Reporte generado por inticap.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(

	'NOMBRE',

	'MARCA',

	'CATEGORIA',

	'UND',	

	'COSTO',

	'PRECIO VENTA',

	'PRECIO MIN S/.',

	'PRECIO MAY '

	);



foreach ($alm_nom as $value) {

	array_push($titulosColumnas, $value);

} 





$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);

$total_almacen;

if($num_rows>=1){

	$c=$c+1;



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



		//stock

		$rws = $oStock->stock_por_presentacion($dt1['tb_presentacion_id'],$_POST['alm_id']);

		$num_filas=mysql_num_rows($rws);

		$rw = mysql_fetch_array($rws);

		$stock_num=$rw['tb_stock_num'];

		mysql_free_result($rws);							

		$stock_unidad=0;						

		if($num_filas>0){

			$st_uni=floor($stock_num/$dt1['tb_catalogo_fac']);

			$st_res=$stock_num%$dt1['tb_catalogo_fac'];



			if($st_res!=0){

								//$stock_unidad="$st_uni + r$st_res";

				$stock_unidad=$st_uni;

			} else{

				$stock_unidad=$st_uni;

			}							

			$action_stock='editar';

			$stock_id=$rw['tb_stock_id'];

		}

		else

		{

			$action_stock='insertar';

			$stock_unidad='-';

		}

						//PRECIOS

		$precio=1;

						//echo '→'.$precio.' / '.$dt1['tb_catalogo_id']; // 1 / 240

		$rws = $oPrecio->consultar_precio($precio,$dt1['tb_catalogo_id']);

		$rw = mysql_fetch_array($rws);

		$predet_id1=$rw['tb_preciodetalle_id'];

		$predet_val1=$rw['tb_preciodetalle_val'];

		$precio=2;

		$rws = $oPrecio->consultar_precio($precio,$dt1['tb_catalogo_id']);

		$rw = mysql_fetch_array($rws);

		$predet_val2=$rw['tb_preciodetalle_val'];

						//echo '→'.$predet_id1;//''

						//echo '→'.$predet_val1;//''

		mysql_free_result($rws);

		$total_costo += $dt1['tb_catalogo_precos'];

		$total_precio_venta += $dt1['tb_catalogo_preven'];

		$total_precio_min += $predet_val1;

		$total_precio_may += $predet_val2;



		$pred1 = ($predet_val1!=0)?$predet_val1:0;

		$pred2 = ($predet_val2!=0)?$predet_val2:0;



		 

		$array_stock_almacenes = array();





		foreach ($alm_id as $value) {

			$rws = $oStock->stock_por_presentacion($dt1['tb_presentacion_id'],$value);

			$num_filas=mysql_num_rows($rws);

			$rw = mysql_fetch_array($rws);

			$stock_num=$rw['tb_stock_num'];

			mysql_free_result($rws);

			$stock_unidad=0;



			if($num_filas>0){                    		

				if($stock_num>0){

					$st_uni=floor($stock_num/$dt1['tb_catalogo_fac']);

					$st_res=$stock_num%$dt1['tb_catalogo_fac'];	

					if($st_res!=0){

										//$stock_unidad="$st_uni + r$st_res";

						$stock_unidad=$st_uni;

					}else{

						$stock_unidad=$st_uni;

					}

				}else{

					$stock_unidad=$stock_num;

				}

			}

			array_push($array_stock_almacenes, $stock_unidad);

			$total_almacen[$value] += $stock_unidad;

		}







		$objPHPExcel->getActiveSheet(0)

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

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

		->setCellValue('C'.$c, $dt1['tb_categoria_nom'])

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

		->setCellValue('E'.$c, formato_numero($dt1['tb_catalogo_precos'],2,''))		

		->setCellValue('F'.$c, formato_numero($dt1['tb_catalogo_preven'],2,''))

		->setCellValue('G'.$c, formato_numero($pred1,2,''))

		->setCellValue('H'.$c, formato_numero($pred2,2,''))

		->setCellValue('I'.$c, $array_stock_almacenes[0])

		->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.0000');

		//$objPHPExcel->getActiveSheet(0)->getStyle("I$c:K$c")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

		$c++;

	}

	mysql_free_result($dts1);

}

$arrx = array();

foreach ($alm_id as $value){

                  	array_push($arrx, $total_almacen[$value]);

                  	}



$objPHPExcel->getActiveSheet()

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

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

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

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

		->setCellValue('H'.$c, $total_precio_may)		

		->setCellValue('I'.$c, $arrx[0])

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

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

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

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



$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('STOCK_GENERAL');





$nombre_archivo="Informe de Stock General ".date('d-m-Y-h:i');



// 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: '.gmdate('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;



