<?php
include_once ("_dirinfo.php");

require_once (PATH_TO_ROOT."CRM/_lib/excel/PHPExcel.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT."CRM/class/class.checkString.php");

include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."CRM/class/class.Language-Pack.php");

$LangPack = new LanguagePack($db, $aLanguage);
//ini_set("display_errors", 1);

function cellColor($cells,$color){
    global $objPHPExcel;
    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
	    ->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
	    'startcolor' => array('rgb' => $color)
    ));
}
if($_GET['action'] == 'export'){
	$basedOn = StringChecker::Check($_GET['basedOn']);
	switch($basedOn){
		case '1':
			$basedOn = "presentation_location";
			break;
		case '2':
			$basedOn = array("presentation_location", "presentation_team");
			break;
		case '3':
			$basedOn = "presentation_team";
			break;
		default:
			$basedOn = "presentation_location";
			break;
	}
	
	if($_GET['fromDate'] != null) $filter[] = " presentation_date >= STR_TO_DATE('".StringChecker::Check($_GET['fromDate'])."', '%d.%m.%Y')";
	if($_GET['toDate'] != null) $filter[] = " presentation_date <= STR_TO_DATE('".StringChecker::Check($_GET['toDate'])."', '%d.%m.%Y')";
	if($_GET['teamF'] != null) $filter[] = " presentation_team = '".StringChecker::Check($_GET['teamF'])."'";
	if($_GET['locationF'] != null) $filter[] = " presentation_location = '".StringChecker::Check($_GET['locationF'])."'";
	
	if(count($filter) > 0){
		$filter[] = " presentation_date <> '0000-00-00'";
		$sql = "SELECT p.id_project AS projectId, p.presentation_location AS pLocation, p.presentation_team AS pTeam, p.*, v.* FROM ".WARPIT_CRM."._visitStatistic AS v RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p ON v.id_project = p.id_project WHERE v.dan = p.cati_day AND v.ura = p.cati_hour AND v.cati_location = p.cati_location AND ".implode("AND ", $filter);
	} else {
		$sql = "SELECT p.id_project AS projectId, p.presentation_location AS pLocation, p.presentation_team AS pTeam, p.*, v.* FROM ".WARPIT_CRM."._visitStatistic AS v RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p ON v.id_project = p.id_project WHERE v.dan = p.cati_day AND v.ura = p.cati_hour AND v.cati_location = p.cati_location";
	}

	
	$res = $db->SQLexecute($sql);
	while($row=$db->fetchAssoc($res)){
		$sql = "SELECT idLab FROM ".DB_WARPIT_WEBCATI_BASE."._groups WHERE id = '".$row['pTeam']."'";		
		$rez = $db->SQLexecute($sql);
		$dat = $db->fetchAssoc($rez);
		if(is_array($basedOn)){
			$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['teamId'] = $row['presentation_team'];
			$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['team'] = $dat['idLab'];
		}else{
			$data[$row[$basedOn]]['teamId'] = $row['presentation_team'];
			$data[$row[$basedOn]]['team'] = $dat['idLab'];
		}
		//team name, city, location
		$sql = "SELECT * FROM ".DB_WARPIT_WEBCATI_BASE."._group_link as g LEFT JOIN ".DB_WARPIT_WEBCATI_BASE."._Interviewer as i ON g.id = i.id WHERE g.id_group = ".$row['pTeam'];
		
		$rez = $db->SQLexecute($sql);
		$dat = $db->fetchAssoc($rez); 
		if(is_array($basedOn)){
			$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['team_name'] = $data[$row[$basedOn]]['team']." ".$dat['Firstname']." ".$dat['Surname'];
			$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['city'] = $row['presentation_city'];
			$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['location'] = $row['pLocation'];
		}else{
			$data[$row[$basedOn]]['team_name'] = $data[$row[$basedOn]]['team']." ".$dat['Firstname']." ".$dat['Surname'];
			$data[$row[$basedOn]]['city'] = $row['presentation_city'];
			$data[$row[$basedOn]]['location'] = $row['pLocation'];
		}
		if(!isset($data[$row[$basedOn]]['presentation'])){
			$f = "";
			if(is_array($basedOn)){
				$f .= $basedOn[0]. " = '" .$row[$basedOn[0]]."'";
				$f .= " AND " . $basedOn[1]. " = '" .$row[$basedOn[1]]."'";
			}else{
				$f = $basedOn. " = '" .$row[$basedOn]."'";
			}
			if(count($filter) > 0)
				$sql = "SELECT COUNT(*) as c FROM ".WARPIT_CRM."._PresentatorPermissions WHERE ".$f." AND ".implode(" AND ", $filter);
			else
				$sql = "SELECT COUNT(*) as c FROM ".WARPIT_CRM."._PresentatorPermissions WHERE ".$f;
			
			//FB::info($sql);
			$rez = $db->SQLexecute($sql);
			$zad = $db->fetchAssoc($rez);
			if(is_array($basedOn))
				$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['presentation'] = $zad['c'];
			else
				$data[$row[$basedOn]]['presentation'] = $zad['c']; 
		}
		if(!isset($data[$row[$basedOn]]['invited'])){
			$f = "";
			if(is_array($basedOn)){
				$f .= $basedOn[0]. " = '" .$row[$basedOn[0]]."'";
				$f .= " AND ". $basedOn[1]. " = '" .$row[$basedOn[1]]."'";
			}else{
				$f = $basedOn. " = '" .$row[$basedOn]."'";
			}
			if(count($filter) > 0)				
				$sql = "SELECT DISTINCT(id_project) as ProjectId, cati_hour, cati_day, cati_location FROM ".WARPIT_CRM."._PresentatorPermissions WHERE ".$f." AND ".implode(" AND ", $filter);
			else
				$sql = "SELECT DISTINCT(id_project) as ProjectId, cati_hour, cati_day, cati_location FROM ".WARPIT_CRM."._PresentatorPermissions WHERE ".$f;
			
			$rez = $db->SQLexecute($sql);
			$invited = 0;
			while($rowP = $db->fetchAssoc($rez)){
				$sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE id=".$rowP['ProjectId'];
				$rex = $db->SQLexecute($sql);
				$datx = $db->fetchAssoc($rex);
				$projectName = $datx['proj_name'];
				
				$sql= "SELECT * FROM ".DB_WARPIT_WEBCATI_BASE.".".$projectName."_structure WHERE status = 1 AND status_rec = 1 AND smpl_var = ".$rowP['cati_location']." AND ura = ".$rowP['cati_hour']." AND dan = ".$rowP['cati_day'];
				$rex = $db->SQLexecute($sql);
				$invited += $db->NumRows($rex);
			}
			if(is_array($basedOn))
				$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['invited'] = $invited;
			else
				$data[$row[$basedOn]]['invited'] = $invited;
		}
		
		if($row['tip_narocila'] > 0){
			if(is_array($basedOn)){
				$price[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['product_amount'][] = $row['product_amount'];
				$price[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['product_price'][] = $row['product_price'];
			}else{
				$price[$row[$basedOn]]['product_amount'][] = $row['product_amount'];
				$price[$row[$basedOn]]['product_price'][] = $row['product_price'];
			}
		}
		if($row['order_status'] == 2){
			if(is_array($basedOn)){
				$realizationPrice[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['product_amount'][] = $row['product_amount'];
				$realizationPrice[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['product_price'][] = $row['product_price'];
			}else{
				$realizationPrice[$row[$basedOn]]['product_amount'][] = $row['product_amount'];
				$realizationPrice[$row[$basedOn]]['product_price'][] = $row['product_price'];
			}
		}
		if($row['nacin_pristopa'] > 0){
			if($row['id_sub_tel'] != 0){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['companions']++;
				else
					$data[$row[$basedOn]]['companions']++;
			}
			if($row['id_sub_tel'] == 0){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['attendance']++;
				else
					$data[$row[$basedOn]]['attendance']++;
			}
			if($row['gender'] == 1){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['male']++;
				else
					$data[$row[$basedOn]]['male']++;
			}
			if($row['gender'] == 2){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['female']++;
				else
					$data[$row[$basedOn]]['female']++;
			}
			if($row['nacin_pristopa'] == 1){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['alone']++;
				else
					$data[$row[$basedOn]]['alone']++;
			}
			if($row['nacin_pristopa'] == 2){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['spouse']++;
				else
					$data[$row[$basedOn]]['spouse']++;	
			}
			if($row['nacin_pristopa'] == 3 || $row['nacin_pristopa'] == 4 || $row['nacin_pristopa'] == 5 || $row['nacin_pristopa'] == 6){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['other']++;
				else
					$data[$row[$basedOn]]['other']++;
			}
			if($row['order_status'] > 0){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['order']+= $row['product_amount'];
				else
					$data[$row[$basedOn]]['order'] += $row['product_amount'];
			}
			if($row['order_status'] == 2){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['delivery']++;
				else
					$data[$row[$basedOn]]['delivery']++;
			}
			if($row['order_status'] == 4 || $row['order_status'] == 5){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['storno']++;
				else
					$data[$row[$basedOn]]['storno']++;
			}
			if($row['order_status'] == 3){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['delayed']++;
				else
					$data[$row[$basedOn]]['delayed']++;
			}
			if($row['tip_narocila'] == 5){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['corporate_card']++;
				else
					$data[$row[$basedOn]]['corporate_card']++;
			}
			if($row['tip_narocila'] == 5 && $row['order_status'] == 2){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['loan_credit']++;
				else
					$data[$row[$basedOn]]['loan_credit']++;
			}
			if($row['tip_narocila'] == 0){
				if(is_array($basedOn))
					$data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['undecidedly']++;
				else
					$data[$row[$basedOn]]['undecidedly']++;
			}
		}
	}
	foreach($price as $key => $value){
		$sum = 0;
		for ($i=0; $i < count($value['product_amount']); $i++) { 
			$sum += ($value['product_amount'][$i] * $value['product_price'][$i]);
		}
		$data[$key]['order_amount'] = $sum; 
	}
	foreach($realizationPrice as $key => $value){
		$sum = 0;
		for ($i=0; $i < sizeof($value['product_amount']); $i++) { 
			$sum += ($value['product_amount'][$i] * $value['product_price'][$i]);
		}
		$data[$key]['recognized_income'] = $sum; 
	}
	
	foreach ($data as $key => $value) {
		if(!isset($data[$key]['companions'])) $data[$key]['companions'] = 0;
		if(!isset($data[$key]['attendance'])) $data[$key]['attendance'] = 0;
		if(!isset($data[$key]['male'])) $data[$key]['male'] = 0;
		if(!isset($data[$key]['female'])) $data[$key]['female'] = 0;
		if(!isset($data[$key]['alone'])) $data[$key]['alone'] = 0;
		if(!isset($data[$key]['spouse'])) $data[$key]['spouse'] = 0;
		if(!isset($data[$key]['other'])) $data[$key]['other'] = 0;
		if(!isset($data[$key]['order'])) $data[$key]['order'] = 0;
		if(!isset($data[$key]['delivery'])) $data[$key]['delivery'] = 0;
		if(!isset($data[$key]['storno'])) $data[$key]['storno'] = 0;
		if(!isset($data[$key]['delayed'])) $data[$key]['delayed'] = 0;
		if(!isset($data[$key]['corporate_card'])) $data[$key]['corporate_card'] = 0;
		if(!isset($data[$key]['undecidedly'])) $data[$key]['undecidedly'] = 0;
		if(!isset($data[$key]['order_amount'])) $data[$key]['order_amount'] = 0;
		if(!isset($data[$key]['recognized_income'])) $data[$key]['recognized_income'] = 0;
		if(!isset($data[$key]['product_amount'])) $data[$key]['product_amount'] = 0;
		if(!isset($data[$key]['loan_credit'])) $data[$key]['loan_credit'] = 0;
		
		$data[$key]['total'] = $value['attendance'] + $value['companions'];
		$data[$key]['not_present'] = $value['invited'] - $value['attendance'];
		$data[$key]['appeared_average'] = round(($value['attendance'] + $value['companions']) / $value['invited'] * 100,2)."%";
		$data[$key]['customer_avarage'] = round($value['order'] / ($value['attendance'] + $value['companions']) * 100 ,2)."%";
		$data[$key]['traffic_avarage'] = round($value['recognized_income'] / $value['presentation'],2);
		$data[$key]['realization'] = round($value['delivery'] / $value['order'] * 100,2)."%";
		$data[$key]['storno_percent'] = round($value['storno'] / $value['order'] * 100,2)."%";
		$data[$key]['color'] = "F0E68C";
		
	}

	//FB::info($data);
	$data = array_values($data);
	$sum = count($data);
	for($i = 0; $i < count($data); $i++){
		$stat_presentation[] = $data[$i]['presentation'];
		$stat_invited[] = $data[$i]['invited'];
		$stat_not_present[] = $data[$i]['not_present'];
		$stat_attendance[] = $data[$i]['attendance'];
		$stat_undecidedly[] = $data[$i]['undecidedly'];
		$stat_appeared[] = $data[$i]['companions'];
		$stat_male[] = $data[$i]['male'];
		$stat_female[] = $data[$i]['female'];
		$stat_alone[] = $data[$i]['alone'];
		$stat_loan_credit[] = $data[$i]['loan_credit'];
		$stat_spouse[] = $data[$i]['spouse'];
		$stat_other[] = $data[$i]['other'];
		$stat_order[] = $data[$i]['order'];
		$stat_total[] = $data[$i]['total'];
		$stat_delivery[] = $data[$i]['delivery'];
		$stat_storno[] = $data[$i]['storno'];
		$stat_delayed[] = $data[$i]['delayed'];
		$stat_corporate[] = $data[$i]['corporate_card'];
		$stat_appeared_average[] = $data[$i]['appeared_average'];
		$stat_customer[] = $data[$i]['customer_avarage'];
		$stat_traffic[] = $data[$i]['traffic_avarage'];
		$stat_realization[] = $data[$i]['realization'];
		$stat_storno_percent[] = $data[$i]['storno_percent'];
		$stat_amount[] = $data[$i]['order_amount'];
		$stat_recognized[] = $data[$i]['recognized_income'];
 	}
	
	
		$data[] = array('team' => $LangPack->GetLabel("CRM_statistic_grid", "ststistic_avg_row_label","Povprečje"),
					'team_name' => '',
					'city' => '',
					'location' => '',
					'presentation' => round(array_sum($stat_presentation)/$sum, 2),
					'not_present' => round(array_sum($stat_not_present) / $sum, 2),
					'invited' => round(array_sum($stat_invited)/$sum, 2),
					'attendance' => round(array_sum($stat_attendance)/$sum, 2),
					'undecidedly' => round(array_sum($stat_undecidedly)/$sum, 2),
					'companions' => round(array_sum($stat_appeared)/$sum, 2),
					'total' => round(array_sum($stat_total)/$sum, 2),
					'male' => round(array_sum($stat_male)/$sum, 2),
					'female' => round(array_sum($stat_female)/$sum, 2),
					'alone' => round(array_sum($stat_alone)/$sum, 2),
					'spouse' => round(array_sum($stat_spouse)/$sum, 2),
					'other' => round(array_sum($stat_other)/$sum, 2),
					'order' => round(array_sum($stat_order)/$sum, 2),
					'delivery' => round(array_sum($stat_delivery)/$sum, 2),
					'storno' => round(array_sum($stat_storno)/$sum, 2),
					'delayed' => round(array_sum($stat_delayed)/$sum, 2),
					'corporate_card' => round(array_sum($stat_corporate)/$sum, 2),
					'loan_credit' => round(array_sum($stat_loan_credit)/$sum, 2),
					'appeared_average' => round(array_sum($stat_appeared_average)/$sum, 2)."%",
					'customer_avarage' => round(array_sum($stat_customer)/$sum, 2)."%",
					'traffic_avarage' => round(array_sum($stat_traffic)/$sum, 2),
					'realization' => '-',
					'storno_percent' => '-',
					'order_amount' => round(array_sum($stat_amount)/$sum, 2),
					'recognized_income' => round(array_sum($stat_recognized)/$sum, 2),
					'color' => "FFFFFF"
	 );
	 $data[] = array('team' => $LangPack->GetLabel("CRM_statistic_grid", "ststistic_sum_row_label","Skupno"),
					'team_name' => '',
					'city' => '',
					'location' => '',
					'presentation' => array_sum($stat_presentation),
					'not_present' => array_sum($stat_not_present),
					'invited' => array_sum($stat_invited),
					'attendance' => array_sum($stat_attendance),
					'undecidedly' => array_sum($stat_undecidedly),
					'companions' => array_sum($stat_appeared),
					'total' => array_sum($stat_total),
					'male' => array_sum($stat_male),
					'female' => array_sum($stat_female),
					'alone' => array_sum($stat_alone),
					'spouse' => array_sum($stat_spouse),
					'other' => array_sum($stat_other),
					'order' => array_sum($stat_order),
					'delivery' => array_sum($stat_delivery),
					'storno' => array_sum($stat_storno),
					'delayed' => array_sum($stat_delayed),
					'corporate_card' => array_sum($stat_corporate),
					'loan_credit' => array_sum($stat_loan_credit),
					'appeared_average' => array_sum($stat_appeared_average)."%",
					'customer_avarage' => array_sum($stat_customer)."%",
					'traffic_avarage' => array_sum($stat_traffic),
					'realization' => round(array_sum($stat_realization)/$sum, 2)."%",
					'storno_percent' => round(array_sum($stat_storno_percent)/$sum, 2)."%",
					'order_amount' => array_sum($stat_amount),
					'recognized_income' => array_sum($stat_recognized),
					'color' => "E0E0E0"
	);
	$abc = range("A","Z");
	
	$orderBy['team'] = 0;
	$orderBy['team_name'] = 1;
	$orderBy['city'] = 2;
	$orderBy['location'] = 3;
	$orderBy['presentation'] = 5;
	$orderBy['invited'] = 6;
	$orderBy['not_present'] = 7;
	$orderBy['attendance'] = 8;
	$orderBy['companions'] = 9;
	$orderBy['total'] = 10;
	$orderBy['male'] = 11;
	$orderBy['female'] = 12;
	$orderBy['undecidedly'] = 13;
	$orderBy['alone'] = 14;
	$orderBy['spouse'] = 15;
	$orderBy['other'] = 16;
	$orderBy['order'] = 17;
	$orderBy['delivery'] = 18;
	$orderBy['storno'] = 19;
	$orderBy['delayed'] = 20;
	$orderBy['corporate_card'] = 21;
	$orderBy['loan_credit'] = 22;
	$orderBy['appeared_average'] = 23;
	$orderBy['customer_avarage'] = 24;
	$orderBy['traffic_avarage'] = 25;
	$orderBy['realization'] = 26;
	$orderBy['storno_percent'] = 27;
	$orderBy['order_amount'] = 28;
	$orderBy['recognized_income'] = 29;
	
	$fields = json_decode($_GET['status']);
	$orderedStatus = array();
	foreach ($fields as $key => $value) {
		$orderedStatus[$orderBy[$value]] = $value;
	}
	ksort($orderedStatus);
	$orderedStatus = array_values($orderedStatus);
	
	$config['team'] = array("width" => "15", "title" => $LangPack->GetLabel("statistic_export", "team_title","Ekipa"));
	$config['team_name'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "team_name_title","Ime ekipa"));
	$config['city'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "city_title","Kraj"));
	$config['location'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "location_title","Lokacija"));
	$config['qestionnaire'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "questionnaire_title","Vpršalnik"));
	$config['presentation'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "presentation_title","Predstavitev"));
	$config['invited'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "invited_title","Povabljeni"));
	$config['attendance'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "attendance_register_title","Udeleženi"));
	$config['total'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "total_title","Vsi skupaj"));
	$config['not_present'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "not_present_title","Niso prisotni"));
	$config['undecidedly'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "undecideldy_title","Neodločni"));
	$config['companions'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "companions_title","Spremjevalci"));
	$config['male'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "male_title","Moški"));
	$config['female'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "female_title","Žensk"));
	$config['alone'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "alone_title","Sami"));
	$config['spouse'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "spouse_title","Zakonec"));
	$config['other'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "other_title","Ostalo"));
	$config['order'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "order_title","Naročila"));
	$config['delivery'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "delivery_title","Dostava"));
	$config['storno'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "storno_title","Storno"));
	$config['delayed'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "delayed_title","Zamuda"));
	$config['corporate_card'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "corporate_card_title","Podjetna kartica"));
	$config['loan_credit'] = array("width" => "20", "title" => $LangPack->GetLabel("statistic_export", "loan_credit_title","Lon kredit"));
	$config['appeared_average'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "appeared_avarage_title","Povprečna pojava"));
	$config['customer_avarage'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "customer_avarage_title","Povprečnih kupcov"));
	$config['traffic_avarage'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "traffic_avarage_title","Povprečni promet"));
	$config['realization'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "realization_title","Realizacija (%)")."%");
	$config['storno_percent'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "storno_percent_title","Storno (%)")."%");
	$config['order_amount'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "order_amount_title","Znesek naročila"));
	$config['recognized_income'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "recognized_income_title","Priznani prihodek"));
	
	$objPHPExcel = new PHPExcel();
	
	foreach ($orderedStatus as $key => $value) {
		if($key > 25){
			$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
		}
		else
			$letter = $abc[$key];
		
		$objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($config[$value]['width']);
		$objPHPExcel->getActiveSheet()->setCellValue($letter."1", $config[$value]['title']);	
		$objPHPExcel->getActiveSheet()->getStyle($letter."1")->getFont()->setBold(true);
	}
	
	$i = 2;
 	
	foreach( $data as $key1 => $value1 )
	{
		foreach ($orderedStatus as $key => $value) {
			if($key > 25){
				$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
			}
			else
				$letter = $abc[$key];
			
			$objPHPExcel->getActiveSheet()->setCellValue($letter.$i, $value1[$value]);
			if(isset($value1['color']))
				cellColor($letter.$i, $value1['color']);
		}
	    $i++;
	}
	$fileName = $LangPack->GetLabel("statistic_export", "file_name","Statistika");
	$fileName .= ".xls";
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment; filename="' . $fileName . '"');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	
	
}
elseif($_GET['action'] == 'exportRow'){
	$basedOn = StringChecker::Check($_GET['basedOn']);
	$location = StringChecker::Check($_GET['location']);
	$team = StringChecker::Check($_GET['team']);
	if($basedOn == 1) $filter[] = " presentation_location='".$location."'";
	if($basedOn == 2) $filter[] = " presentation_location='".$location."' AND presentation_team = '".$team."'";
	if($basedOn == 3) $filter[] = " presentation_team = '".$team."'";
	if($_GET['fromDate'] != null) $filter[] = " presentation_date >= STR_TO_DATE('".StringChecker::Check($_GET['fromDate'])."', '%d.%m.%Y')";
	if($_GET['toDate'] != null) $filter[] = " presentation_date <= STR_TO_DATE('".StringChecker::Check($_GET['toDate'])."', '%d.%m.%Y')";
	
	$sql = "SELECT DISTINCT(id_project) FROM ".WARPIT_CRM."._PresentatorPermissions WHERE ".implode(" AND ", $filter);
	$rez = $db->SQLexecute($sql);
	while($dat = $db->fetchAssoc($rez)){
		$projectId = $dat['id_project'];
		
		$sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE id=".$projectId;
		$res = $db->SQLexecute($sql);
		$dat = $db->fetchAssoc($res);
		$projectName = $dat['proj_name'];
		$sql= 	"SELECT s.oseba_1, s.oseba_2, s.oseba_3, s.dogodek_2, v.order_status, v.product_amount, v.product_price 
				FROM ".DB_WARPIT_WEBCATI_BASE.".".$projectName."_structure s RIGHT JOIN ".WARPIT_CRM."._visitStatistic v ON s.id_tel = v.id_tel 
				AND s.smpl_var = v.cati_location AND s.ura = v.ura AND s.dan = v.dan
				WHERE s.status = 1 AND s.status_rec = 1";
		$res = $db->SQLexecute($sql);
		while($row = $db->fetchAssoc($res)){
			$data[] = $row;
		}
	}
	$objPHPExcel = new PHPExcel();
	
	$abc = range("A", "Z");

	$config['oseba_1'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "oseba_1_title","Ime in Priimek"));
	$config['oseba_2'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "oseba_2_title","Naslov"));
	$config['oseba_3'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "oseba_3_title","Pošta"));
	$config['dogodek_2'] = array("width" => "65", "title" => $LangPack->GetLabel("statistic_export", "dogodek_2_title","Dogodek"));
	$config['order_status'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "order_status_title","Status nakupa"));
	$config['product_amount'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "product_amount_title","Količina produkta"));
	$config['product_price'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "product_price_title","Cena produkta"));

	$fields = array('oseba_1', 
					'oseba_2', 
					'oseba_3', 
					'dogodek_2', 
					'order_status', 
					'product_amount', 
					'product_price');
	$orderBy = array();
	foreach ($fields as $key => $value) {
		$orderBy[$value] = $key; 
	}
					
	$orderedStatus = array();
	foreach ($fields as $key => $value) {
		$orderedStatus[$orderBy[$value]] = $value;
	}
	ksort($orderedStatus);
	$orderedStatus = array_values($orderedStatus);
	
	
	$objPHPExcel = new PHPExcel();
	foreach ($orderedStatus as $key => $value) {
		if($key > 25){
			$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
		}
		else
			$letter = $abc[$key];
		
		$objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($config[$value]['width']);
		$objPHPExcel->getActiveSheet()->setCellValue($letter."1", $config[$value]['title']);	
		$objPHPExcel->getActiveSheet()->getStyle($letter."1")->getFont()->setBold(true);
	}
	$i=2;
 	
	foreach( $data as $key1 => $value1 )
	{
		foreach ($orderedStatus as $key => $value) {
			if($key > 25){
				$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
			}
			else
				$letter = $abc[$key];
			
			$objPHPExcel->getActiveSheet()->setCellValue($letter.$i, $value1[$value]);
			if(isset($value1['color']))
				cellColor($letter.$i, $value1['color']);
			
		}
	    $i++;
	}
	$fileName = $LangPack->GetLabel("statistic_export", "file_name","Statistic_Export");
	$fileName .= ".xls";
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment; filename="' . $fileName . '"');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
}
?>