<?php
set_time_limit(999999);
$mn=explode(",",$_REQUEST['uid']);
//$mn=$_REQUEST['mass'];
if(!$_REQUEST['uid'])$mn=$_REQUEST['mass'];
set_include_path('include/PHPExcel/');

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("E5 CRM");
$objPHPExcel->getProperties()->setLastModifiedBy("E5 CRM");
$objPHPExcel->getProperties()->setTitle("Office 2007 PRICEBOOK");
$objPHPExcel->getProperties()->setSubject("Office 2007 PRICEBOOK");
$objPHPExcel->getProperties()->setDescription("PRICEBOOK");

$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'FFCCFFCC')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A1:AG1"
);

$alf="ABCDEFGHIJKLMNOPQRSTUVWXYZ";

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(20);

$objPHPExcel->getActiveSheet()->SetCellValue('A1','Product');
$objPHPExcel->getActiveSheet()->SetCellValue('B1','Code');
$objPHPExcel->getActiveSheet()->SetCellValue('C1','Category');
$objPHPExcel->getActiveSheet()->SetCellValue('D1','Inventory');
$objPHPExcel->getActiveSheet()->SetCellValue('E1','Ordered');
$objPHPExcel->getActiveSheet()->SetCellValue('F1','EMS price');
$objPHPExcel->getActiveSheet()->SetCellValue('G1','Unit price');
$objPHPExcel->getActiveSheet()->SetCellValue('H1','Inv value');
$objPHPExcel->getActiveSheet()->SetCellValue('I1','Ppc');
$objPHPExcel->getActiveSheet()->SetCellValue('J1','CBM stock');
$objPHPExcel->getActiveSheet()->SetCellValue('K1','CBM ordered');
$objPHPExcel->getActiveSheet()->SetCellValue('L1','Qty 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('M1','Avg price 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('N1','Qty this m');
$objPHPExcel->getActiveSheet()->SetCellValue('O1','Avg price this m');
$objPHPExcel->getActiveSheet()->SetCellValue('P1','Stock m');
$objPHPExcel->getActiveSheet()->SetCellValue('Q1','Pricebook');
$objPHPExcel->getActiveSheet()->SetCellValue('R1','MOQ');
$objPHPExcel->getActiveSheet()->SetCellValue('S1','Pieces per carton');
$objPHPExcel->getActiveSheet()->SetCellValue('T1','Carton dimensions (m)');
$objPHPExcel->getActiveSheet()->SetCellValue('U1','Carton netto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('V1','Carton brutto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('W1','Product netto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('X1','Product brutto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('Y1','Packing dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('Z1','Carton volume');
$objPHPExcel->getActiveSheet()->SetCellValue('AA1','EAN');
$objPHPExcel->getActiveSheet()->SetCellValue('AB1','Short description');
$objPHPExcel->getActiveSheet()->SetCellValue('AC1','Long description');
$objPHPExcel->getActiveSheet()->SetCellValue('AD1','FOB price');
$objPHPExcel->getActiveSheet()->SetCellValue('AE1','SRP price');
$objPHPExcel->getActiveSheet()->SetCellValue('AF1','Custom duty rate');
$objPHPExcel->getActiveSheet()->SetCellValue('AG1','Commission rate');

/*
$objPHPExcel->getActiveSheet()->SetCellValue('O1','Pieces per carton');
$objPHPExcel->getActiveSheet()->SetCellValue('P1','Carton dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('Q1','Packing dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('R1','Carton volume');*/
$j=0;

if(count($mn)>0){
	foreach($mn as $mm){
		$mmm[]="id='".$mm."'";
	}
}
if(count($mmm)<=0){
	if($_SESSION['EcmProductsForXLSwhere']=="")$_SESSION['EcmProductsForXLSwhere']="1=1";
	$where="where (".$_SESSION['EcmProductsForXLSwhere'].") and deleted='0'";
	//$_SESSION['EcmProductsForXLSwhere']="";
}
else $where="where (".implode(" or ",$mmm).") and deleted='0'";


$ww=$GLOBALS['db']->query("select * from ecmproducts ".$where);
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
	$objPHPExcel->getActiveSheet()->SetCellValue('A'.($j+2),$rr['name']);
	$objPHPExcel->getActiveSheet()->SetCellValue('B'.($j+2),$rr['code']);
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.($j+2),$rr['product_category_name']);
	$objPHPExcel->getActiveSheet()->SetCellValue('D'.($j+2),$rr['ems_qty_in_stock']);
	$ems_qty_in_stock_sum+=$rr['ems_qty_in_stock'];
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.($j+2),$rr['ordered']);
	$ordered_sum+=$rr['ordered'];
	$objPHPExcel->getActiveSheet()->SetCellValue('F'.($j+2),$rr['ems_price']);
	$ems_price_sum+=$rr['ems_price'];
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.($j+2),$rr['purchase_price']);
	$purchase_price_sum+=$rr['purchase_price'];
	
	$SALE_AVG_PRICE90=0;
	$sale0=0;
	$sale30=0;
	$sale90=0;
	$sale180=0;
	$qty0=0;
	$qty30=0;
	$qty90=0;
	$qty180=0;
	
	$ddate=date("Y-m-d",mktime()-200*24*3600);

	$w=$GLOBALS['db']->query("select ecminvoiceoutitems.price as price,ecminvoiceoutitems.quantity as quantity,ecminvoiceouts.register_date as date from ecminvoiceoutitems inner join ecminvoiceouts on ecminvoiceoutitems.ecminvoiceout_id=ecminvoiceouts.id where ecminvoiceoutitems.ecmproduct_id='".$rr['id']."' and ecminvoiceouts.type!='correct' and ecminvoiceouts.register_date>'".$ddate."'");
	  while($r=$GLOBALS['db']->fetchByAssoc($w)){
	   		$sale+=$r['price']*$r['quantity'];
			$qty+=$r['quantity'];
			$aq[$r['no']]+=$r['quantity'];
					$date=date("Y-m");
					$m=(int)date("m"); 
					$date1=date("Y-m",mktime(0,0,0,($m-1),1,date("Y")));
					$date2=date("Y-m",mktime(0,0,0,($m-2),1,date("Y")));
					$date3=date("Y-m",mktime(0,0,0,($m-3),1,date("Y")));
					$date4=date("Y-m",mktime(0,0,0,($m-4),1,date("Y")));
					$date5=date("Y-m",mktime(0,0,0,($m-5),1,date("Y")));
					$date6=date("Y-m",mktime(0,0,0,($m-6),1,date("Y")));

					$d=explode("-",$r['date']);
			
					$dd=$d[0]."-".$d[1];

					if($dd==$date){
	  					$sale0+=$r['price']*$r['quantity'];
						$qty0+=$r['quantity'];
						$aq0[$r['no']]+=$r['quantity'];
					}
					if($dd==$date1){
	  					$sale30+=$r['price']*$r['quantity'];
						$qty30+=$r['quantity'];
						$aq30[$r['no']]+=$r['quantity'];
					}
					if($dd==$date1 || $dd==$date2 || $dd==$date3){
	  					$sale90+=$r['price']*$r['quantity'];
						$qty90+=$r['quantity'];
						$aq90[$r['no']]+=$r['quantity'];
					}
					if($dd==$date1 || $dd==$date2 || $dd==$date3 || $dd==$date4 || $dd==$date5 || $dd==$date6){
	  					$sale180+=$r['price']*$r['quantity'];
						$qty180+=$r['quantity'];
						$aq180[$r['no']]+=$r['quantity'];
					}
	  }
	  $SALE_QTY=$qty;
	  $SALE_QTY0=$qty0;
	  $SALE_QTY30=$qty30;
	  $SALE_QTY90=($qty90/3);
	  $SALE_QTY180=$qty180;
	  
	  $SALE_SALE=$sale;
	  $SALE_SALE0=$sale0;
	  $SALE_SALE30=$sale30;
	  $SALE_SALE90=$sale90;
	  $SALE_SALE80=$sale180;
	  
	  if($qty>0)$SALE_AVG_PRICE=round($sale/$qty,2);
	  if($qty0>0)$SALE_AVG_PRICE0=round($sale0/$qty0,2);
	  if($qty30>0)$SALE_AVG_PRICE30=round($sale30/$qty30,2);
	  if($qty90>0)$SALE_AVG_PRICE90=round($sale90/$qty90,2);
	  if($qty180>0)$SALE_AVG_PRICE180=round($sale180/$qty180,2);


	  if($rr['pieces_per_carton'])$cbm=(($rr['ems_qty_in_stock']*$rr['carton_volume_meter'])/$rr['pieces_per_carton']);
	  if($rr['pieces_per_carton'])$cbm_ordered=(($rr['ordered']*$rr['carton_volume_meter'])/$rr['pieces_per_carton']);
	
	$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),($rr['ems_price']*$rr['ems_qty_in_stock']));
	$stock_value_sum+=($rr['ems_price']*$rr['ems_qty_in_stock']);
	$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),$rr['pieces_per_carton']);
	$pieces_per_carton_sum+=$rr['pieces_per_carton'];
	$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),$cbm);
	$cbm_sum+=$cbm;
	$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),$cbm_ordered);
	$cbm_ordered_sum+=$cbm_ordered;
	$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),$SALE_QTY90);
	$sale_qty90_sum+=$SALE_QTY90;
	$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),$SALE_AVG_PRICE90);
	$sale_avg_price90_sum+=$SALE_AVG_PRICE90;
	$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),$SALE_QTY0);
	$sale_qty0_sum+=$SALE_QTY0;
	$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),$SALE_AVG_PRICE0);
	$sale_avg_price0_sum+=$SALE_AVG_PRICE0;
	$objPHPExcel->getActiveSheet()->SetCellValue('P'.($j+2),($rr['ems_qty_in_stock']/($SALE_QTY90)));
	//pricebook
	$accounts=array("Auchan","Carrefour","Euro","Real");
	$dig=array();
	foreach($accounts as $account){
		$www=$GLOBALS['db']->query("select p.id from ecmpricebooks_ecmproducts as p inner join ecmpricebooks as e on e.id=p.ecmpricebook_id where p.ecmproduct_id='".$rr['id']."' and e.name='".$account."' and e.deleted='0' and p.deleted='0'");
		if(mysql_num_rows($www)>0)$dig[]=$account[0];
	}
	$objPHPExcel->getActiveSheet()->SetCellValue('Q'.($j+2),implode(",",$dig));
	$objPHPExcel->getActiveSheet()->SetCellValue('R'.($j+2),$rr['moq']);
	$objPHPExcel->getActiveSheet()->SetCellValue('S'.($j+2),$rr['pieces_per_carton']);
	$objPHPExcel->getActiveSheet()->SetCellValue('T'.($j+2),$rr['carton_dimensions_1']." x ".$rr['carton_dimensions_2']." x ".$rr['carton_dimensions_3']);
	$objPHPExcel->getActiveSheet()->SetCellValue('U'.($j+2),$rr['carton_netto_weight']);
	$objPHPExcel->getActiveSheet()->SetCellValue('V'.($j+2),$rr['carton_brutto_weight']);
	$objPHPExcel->getActiveSheet()->SetCellValue('W'.($j+2),$rr['product_netto_weight']);
	$objPHPExcel->getActiveSheet()->SetCellValue('X'.($j+2),$rr['product_brutto_weight']);
	$objPHPExcel->getActiveSheet()->SetCellValue('Y'.($j+2),$rr['packing_dimensions_1']." x ".$rr['packing_dimensions_2']." x ".$rr['packing_dimensions_3']);
	$objPHPExcel->getActiveSheet()->SetCellValue('Z'.($j+2),$rr['carton_volume_meter']);
	$rl=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select * from ecmproduct_language where ecmproduct_id='".$rr['id']."' and language='pl'"));
	$objPHPExcel->getActiveSheet()->SetCellValue('AA'.($j+2),$rl['ean']);
	if($rl['short_description'])$sd="yes";
	else $sd="no";
	if($rl['long_description'])$ld="yes";
	else $ld="no";
	$objPHPExcel->getActiveSheet()->SetCellValue('AB'.($j+2),$sd);
	$objPHPExcel->getActiveSheet()->SetCellValue('AC'.($j+2),$ld);
	$objPHPExcel->getActiveSheet()->SetCellValue('AD'.($j+2),$rr['fob_price']);
	$objPHPExcel->getActiveSheet()->SetCellValue('AE'.($j+2),$rr['srp_price']);
	$objPHPExcel->getActiveSheet()->SetCellValue('AF'.($j+2),$rr['custom_duty_rate']);
	$objPHPExcel->getActiveSheet()->SetCellValue('AG'.($j+2),$rr['commission_rate']);
	$j++;
}
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'FFCCFFCC')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A".($j+2).":AG".($j+2)
);

	$objPHPExcel->getActiveSheet()->SetCellValue('D'.($j+2),$ems_qty_in_stock_sum);
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.($j+2),$ordered_sum);
	$objPHPExcel->getActiveSheet()->SetCellValue('F'.($j+2),($ems_price_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.($j+2),($purchase_price_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),($stock_value_sum));
	$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),($pieces_per_carton_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),$cbm_sum);
	$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),$cbm_ordered_sum);
	$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),($sale_qty90_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),($sale_avg_price90_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),($sale_qty0_sum/$j));
	$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),($sale_avg_price0_sum/$j));

$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
chmod("cache/upload",0777);
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/Products".$microtime.".xlsx";
$objWriter->save($name);
chmod($name,0777);

header("Location: ".$name);
?>