<?php
$date_from=$_GET['date_from'];
$date_to=$_GET['date_to'];
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 RPH");
$objPHPExcel->getProperties()->setSubject("Office 2007 RPH");
$objPHPExcel->getProperties()->setDescription("RPH");

$alf1="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for($i=0;$i<strlen($alf1);$i++)$alf[$i]=$alf1[$i];
$alf[]="AA";
$alf[]="AB";
$alf[]="AC";
$alf[]="AD";
$alf[]="AE";
$alf[]="AF";
$alf[]="AG";
$alf[]="AH";
$alf[]="AI";
$alf[]="AJ";
$alf[]="AK";
$alf[]="AL";
$alf[]="AM";
$alf[]="AN";
$alf[]="AO";
$alf[]="AP";
$alf[]="AQ";
$alf[]="AR";
$alf[]="AS";
$alf[]="AT";
$alf[]="AU";
$alf[]="AV";
$alf[]="AW";
$alf[]="AX";
$alf[]="AY";
$alf[]="AZ";
$alf[]="BA";
$alf[]="BB";
$alf[]="BC";
$alf[]="BD";
$alf[]="BE";
$alf[]="BF";
$alf[]="BG";
$alf[]="BH";
$alf[]="BI";

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AY')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('AZ')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BA')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BB')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BC')->setWidth(30);


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

$objPHPExcel->getActiveSheet()->SetCellValue("A1","Lp.");
$objPHPExcel->getActiveSheet()->SetCellValue("B1","Name");
$objPHPExcel->getActiveSheet()->SetCellValue("C1","January");
$objPHPExcel->getActiveSheet()->SetCellValue("G1","February");
$objPHPExcel->getActiveSheet()->SetCellValue("K1","March");
$objPHPExcel->getActiveSheet()->SetCellValue("O1","April");
$objPHPExcel->getActiveSheet()->SetCellValue("S1","May");
$objPHPExcel->getActiveSheet()->SetCellValue("W1","June");
$objPHPExcel->getActiveSheet()->SetCellValue("AA1","July");
$objPHPExcel->getActiveSheet()->SetCellValue("AE1","August");
$objPHPExcel->getActiveSheet()->SetCellValue("AI1","September");
$objPHPExcel->getActiveSheet()->SetCellValue("AM1","October");
$objPHPExcel->getActiveSheet()->SetCellValue("AQ1","November");
$objPHPExcel->getActiveSheet()->SetCellValue("AU1","Dezember");
$objPHPExcel->getActiveSheet()->SetCellValue("AY1",$_REQUEST['year']." total");
$objPHPExcel->getActiveSheet()->SetCellValue("AZ1",$_REQUEST['year']." avg");
$objPHPExcel->getActiveSheet()->SetCellValue("BA1",($_REQUEST['year']-1)." total");
$objPHPExcel->getActiveSheet()->SetCellValue("BB1",($_REQUEST['year']-1)." avg");
$objPHPExcel->getActiveSheet()->SetCellValue("BC1","User");
$i=2;
$wh[]="deleted='0'";
if($_REQUEST['user_id'])$wh[]="assigned_user_id='".$_REQUEST['user_id']."'";
if($_REQUEST['account_id']){
	$wh[]="parent_id='".$_REQUEST['account_id']."'";
	$wh[]="parent_id is not null";
}
if(count($wh)>0)$where=" and ".implode(" and ",$wh);
else $where="";
$z="select * from accounts where 1=1".$where." order by name asc";
$w=mysql_query($z);
function getSumWz($year){
	$w=$GLOBALS['db']->query("select id,type,register_date,currency_value,wz_id,parent_id,ecminvoiceout_id from ecminvoiceouts where register_date like '".$year."%' and deleted='0'");
	while($r=$GLOBALS['db']->fetchByAssoc($w)){
		if(!$r['currency_value'])
		$currency_value=1;
		else $currency_value=$r['currency_value'];
		$d=explode("-",$r['register_date']);
		$dat=$d[0]."-".$d[1];
		if($r['type']!="correct"){
			$rr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select sum(price*quantity) as s from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'"));
			$total_netto=$currency_value*$rr['s'];
			
		}
		else{
			$ww=$GLOBALS['db']->query("select price,quantity,ecminvoiceoutitem_id from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'");
			$total_netto=0;
			while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
				$rrrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select price,quantity from ecminvoiceoutitems where id='".$rr['ecminvoiceoutitem_id']."'"));
				$total_netto+=$currency_value*$rr['price']*$rr['quantity']-$currency_value*$rrrr['price']*$rrrr['quantity'];
			}
		}
		if($r['wz_id']){
			$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$r['wz_id']."'"));
			$parent_id=$rrr['parent_id'];
		}
		else {
			if($r['type']!="correct")$parent_id=$r['parent_id'];	
			else{
				$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select wz_id from ecminvoiceouts where id='".$r['ecminvoiceout_id']."'"));
				$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$rrr['wz_id']."'"));
				$parent_id=$rrr['parent_id'];
			}
			$parent_id=$r['parent_id'];
		}
		$sum[$dat][$parent_id]['tn']+=$total_netto;
	}
	return $sum;
}

function getMinSec($v){
	$min=floor($v/60);
	$sec=$v-$min*60;
	if($min>0 || $sec>0)$str=$min."m ".$sec."s";
	return $str;
}
$swz1=getSumWz($_REQUEST['year']);
$swz1m=getSumWz($_REQUEST['year']-1);
if($_REQUEST['account_id'] && $_REQUEST['user_id']){
	$time=$_SESSION['EcmSalesReport_time'];
	$ll=4;
}
else $ll=4;

while($r=mysql_fetch_array($w)){	
	$objPHPExcel->getActiveSheet()->SetCellValue("A".$i,($i-1));
	$objPHPExcel->getActiveSheet()->SetCellValue("B".$i,$r['name']);
	
	for($k=1;$k<=12;$k++){
		if($k<10)$n="0".$k;
		else $n=$k;
		$swz=$swz1[$_REQUEST['year']."-".$n][$r['id']];
		$total_netto=$swz['tn'];
		$rval=mysql_fetch_array(mysql_query("select value from ecmsalesreports_predictions where account_id='".$r['id']."' and year='".$_REQUEST['year']."' and month='".$n."' and deleted='0'"));
		$value=$rval['value'];
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+2].$i,$total_netto);
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+3].$i,$value);
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+4].$i,getMinSec($time[$r['id']][$_REQUEST['year']."-".$n]['duration']));
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+5].$i,$time[$r['id']][$_REQUEST['year']."-".$n]['l']);
		$sum_total_netto[$_REQUEST['year']."-".$n]+=$total_netto;
		$sum_total_netto[$_REQUEST['year']]+=$total_netto;
		
		$account_total[$r['id']]+=$total_netto;
	}
	for($k=1;$k<=12;$k++){
		if($k<10)$n="0".$k;
		else $n=$k;
		$swzm=$swz1m[($_REQUEST['year']-1)."-".$n][$r['id']];
		$total_netto_m=$swzm['tn'];
		$sum_total_netto_m[($_REQUEST['year']-1)."-".$n]+=$total_netto_m;
		$sum_total_netto_m[($_REQUEST['year']-1)]+=$total_netto_m;
		
		$account_total_m[$r['id']]+=$total_netto_m;
	}
	$objPHPExcel->getActiveSheet()->SetCellValue("AY".$i,$account_total[$r['id']]);
	$objPHPExcel->getActiveSheet()->SetCellValue("AZ".$i,($account_total[$r['id']]/((int)date("m"))));
	$objPHPExcel->getActiveSheet()->SetCellValue("BA".$i,$account_total_m[$r['id']]);
	$objPHPExcel->getActiveSheet()->SetCellValue("BB".$i,($account_total_m[$r['id']]/12));
	$ruser=mysql_fetch_array(mysql_query("select * from users where id='".$r['assigned_user_id']."'"));
	$objPHPExcel->getActiveSheet()->SetCellValue("BC".$i,$ruser['first_name'].' '.$ruser['last_name']);
	$i++;
}
for($k=1;$k<=12;$k++){
	if($k<10)$n="0".$k;
	else $n=$k;
		$allvalue=0;
		$wacc=mysql_query("select * from accounts where 1=1".$where." order by name asc");
		while($racc=mysql_fetch_array($wacc)){
			$wval=mysql_query("select value from ecmsalesreports_predictions where account_id='".$racc['id']."' and year='".$_REQUEST['year']."' and month='".$n."' and deleted='0'");
			while($rval=mysql_fetch_array($wval)){
				$allvalue+=$rval['value'];
			}
		}
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+2].$i,($sum_total_netto[$_REQUEST['year'].'-'.$n]));
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+3].$i,$allvalue);
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+4].$i,getMinSec($time[$_REQUEST['year']."-".$n]['duration']));
	$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+5].$i,$time[$_REQUEST['year']."-".$n]['l']);
}
$objPHPExcel->getActiveSheet()->SetCellValue("AY".$i,$sum_total_netto[$_REQUEST['year']]);
$objPHPExcel->getActiveSheet()->SetCellValue("AZ".$i,($sum_total_netto[$_REQUEST['year']]/((int)date("m"))));
$objPHPExcel->getActiveSheet()->SetCellValue("BA".$i,$sum_total_netto_m[$_REQUEST['year']-1]);
$objPHPExcel->getActiveSheet()->SetCellValue("BB".$i,($sum_total_netto_m[$_REQUEST['year']-1]/12));

$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'F0F0F0')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A1:BA1"
);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'F0F0F0')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A".($i).":BA".($i)
);


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

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