<?php
global $db;
if(!$_REQUEST['year']){
	$_REQUEST['year']=date("Y");
	$_GET['year']=date("Y");
}
//error_reporting(0);
include_once("modules/EcmReports/class.sales.php");

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

$alf1="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for($i=0;$i<strlen($alf1);$i++)$alf[]=$alf1[$i];
for($i=0;$i<strlen($alf1);$i++){
	for($j=0;$j<strlen($alf1);$j++){
		$alf[]=$alf1[$i].$alf1[$j];
	}
}

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(70);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
for($i=3;$i<count($alf);$i++)$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i])->setWidth(15);
$w=$db->query("select id from accounts where deleted='0' and core_client='1'");
while($r=$db->fetchByAssoc($w)){
	$s=new Sales();
	$s->date_from=$_REQUEST['year']."-01-01";
	$s->date_to=$_REQUEST['year']."-12-31";
	$s->account_id=$r['id'];
	$s->product_active="active";
	$sdata[$r['id']]=$s->getSalesByProductForecast();
}
$a=0;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','Index'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','Name'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','Category'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','Inventory'); $a+=2;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','SRP price'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1','Total'); $a+=3;
$w=$db->query("select name from accounts where deleted='0' and core_client='1' order by name asc");
while($r=$db->fetchByAssoc($w)){
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1',$r['name']); $a+=4;
}
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'1',"New clients"); $a+=4;
$a=3;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Qty'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Price'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Value'); $a++;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Sales'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Qty'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Pred. sales'); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2','Pred. qty'); 
$w=$db->query("select name from accounts where deleted='0' and core_client='1' order by name asc");
while($r=$db->fetchByAssoc($w)){
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Avg. price"); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Qty"); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Sales"); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Pred. qty"); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Pred. sales"); 
}
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Avg. price"); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Qty"); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Sales"); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Pred. qty"); 
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].'2',"Pred. sales"); 

function getTotalFromAccounts($d,$id,$type="sales"){
	global $db;
	$ww=$db->query("select name,id from accounts where deleted='0' and core_client='1' order by name asc");
	while($rr=$db->fetchByAssoc($ww)){
		$total+=$d[$rr['id']][$id][$type];
	}
	return $total;
}
if($_REQUEST['category'])$wh_cat=" and product_category_id='".$_REQUEST['category']."'";
else $wh_cat="";
$w=$db->query("select ems_price,ems_qty_in_stock,id,name,code,product_category_name,product_category_id,srp_price from ecmproducts where product_active='1' and production!='1'".$wh_cat." and deleted='0' order by product_category_name asc,code asc");
$m=3;
while($r=$db->fetchByAssoc($w)){
	$a=0;

	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['code']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['name']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['product_category_name']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['ems_qty_in_stock']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['ems_price']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['ems_price']*$r['ems_qty_in_stock']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$r['srp_price']); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,getTotalFromAccounts($sdata,$r['id'])); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,getTotalFromAccounts($sdata,$r['id'],"quantity")); 
	$total_pred_sales=$a;
	$a++;
	$total_pred_qty=$a;
	$a++;

	$total_srp_price+=$r['srp_price'];
	$total_ems_qty+=$r['ems_qty_in_stock'];
	$total_ems_price+=$r['ems_qty_in_stock'];
	$total_ems_value+=$r['ems_qty_in_stock']*$r['ems_price'];
	$total_sales+=getTotalFromAccounts($sdata,$r['id']);
	$total_qty+=getTotalFromAccounts($sdata,$r['id'],"quantity");
	
	$total_pred_sales_v=0;
	$total_pred_qty_v=0;

	$ww=$db->query("select name,id from accounts where deleted='0' and core_client='1' order by name asc");
	while($rr=$db->fetchByAssoc($ww)){
		if($sdata[$rr['id']][$r['id']]['quantity']>0)$aprice=$sdata[$rr['id']][$r['id']]['sales']/$sdata[$rr['id']][$r['id']]['quantity'];
		else $aprice=0;
		if($aprice==0){
			$rpb=$db->fetchByAssoc($db->query("select price from ecmpricebooks_ecmproducts where active=1 and account_id='".$rr['id']."' and ecmproduct_id='".$r['id']."' and deleted='0'"));
			$aprice=$rpb['price'];
		}
		if($aprice==0){
			$rpb=$db->fetchByAssoc($db->query("select price from ecmpricebooks_ecmproducts where ecmpricebook_id='7b840616-a226-4a15-a831-4d11f116995a' and ecmproduct_id='".$r['id']."' and deleted='0'"));
			$aprice=$rpb['price'];
		}
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$aprice); 
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$sdata[$rr['id']][$r['id']]['quantity']);  
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$sdata[$rr['id']][$r['id']]['sales']);  

		$rrr=$db->fetchByAssoc($db->query("select value from ecmforecasts where ecmproduct_id='".$r['id']."' and account_id='".$rr['id']."'"));
		$pred=(int)$rrr['value'];
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$pred);  
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$rrr['value']*$aprice);  

		$total_pred_sales_v+=$rrr['value']*round($aprice,2);
		$total_pred_qty_v+=$rrr['value'];

		$totals_quantity[$rr['id']]+=$sdata[$rr['id']][$r['id']]['quantity'];	
		$totals_sales[$rr['id']]+=$sdata[$rr['id']][$r['id']]['sales'];			
		$totals_pred[$rr['id']]+=$rrr['value']*round($aprice,2);	
		$totals_pred_qty[$rr['id']]+=$rrr['value'];	
	}
	if(getTotalFromAccounts($sdata,$r['id'],"quantity")>0)$avg_price=getTotalFromAccounts($sdata,$r['id'])/getTotalFromAccounts($sdata,$r['id'],"quantity");
	else $avg_price=0;
	if($avg_price==0){
		$rpb=$db->fetchByAssoc($db->query("select price from ecmpricebooks_ecmproducts where ecmpricebook_id='7b840616-a226-4a15-a831-4d11f116995a' and ecmproduct_id='".$r['id']."' and deleted='0'"));
		$avg_price=$rpb['price'];
	}
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$avg_price);  
	$rrr=$db->fetchByAssoc($db->query("select value from ecmforecasts where ecmproduct_id='".$r['id']."' and account_id='new'"));
	$pred=(int)$rrr['value'];
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$pred);  
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$a++].$m,$rrr['value']*$aprice);  
	
	$total_pred_sales_v+=$rrr['value']*round($avg_price,2);
	$total_pred_qty_v+=$rrr['value'];

	$totals_pred["new"]+=$rrr['value']*round($avg_price,2);
	$totals_pred_qty["new"]+=$rrr['value'];
	$t.=$tre;

	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$total_pred_sales].$m,$total_pred_sales_v); 
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$total_pred_qty].$m,$total_pred_qty_v); 

	$m++;
}

$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'F0F0F0')
		),
	),
	"A1:".$alf[$a+1]."2"
);

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

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