<?php
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."CRM/class/class.Language-Pack.php");
include_once (PATH_TO_ROOT."CRM/class/class.checkString.php");

//ini_set("display_errors", 1);

$LangPack = new LanguagePack($db, $aLanguage);
if($_POST['action'] == "getLocation"){
	$sql = "SELECT DISTINCT(presentation_location) FROM ".WARPIT_CRM."._PresentatorPermissions";
	$res = $db->SQLexecute($sql);
	$i = 1;
	while($row = $db->fetchAssoc($res)){
		$data[] = array("id" => $i++, "value" => $row['presentation_location']);
	}
	$r['success'] = true;
	$r['results'] = sizeof($data);
	$r['rows'] = $data;
	echo json_encode($r);
}elseif($_POST['action'] == "getTeam"){
	$sql = "SELECT DISTINCT(presentation_team) FROM ".WARPIT_CRM."._PresentatorPermissions";
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$sql = "SELECT id,idLab FROM ".DB_WARPIT_WEBCATI_BASE."._groups WHERE id = '".$row['presentation_team']."'";		
		$rez = $db->SQLexecute($sql);
		$dat = $db->fetchAssoc($rez);
		$data[] = array("id" => $dat['id'], "value" => $dat['idLab']);
	}
	
	$r['success'] = true;
	$r['results'] = sizeof($data);
	$r['rows'] = $data;
	echo json_encode($r);
}elseif($_POST['action'] == "get"){
	$basedOn = StringChecker::Check($_POST['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;
	}
    $dateField = "p.presentation_date";
    if(isset($_POST['datum_dostave'])){
        if($_POST['datum_dostave'] == 1){
            $dateField = "v.datum_dostave";
        }
    }
	
	if($_POST['fromDate'] != null) $filter[] = " $dateField >= STR_TO_DATE('".StringChecker::Check($_POST['fromDate'])."', '%d.%m.%Y')";
	if($_POST['toDate'] != null) $filter[] = " $dateField <= STR_TO_DATE('".StringChecker::Check($_POST['toDate'])."', '%d.%m.%Y')";
	if($_POST['teamF'] != null) $filter[] = " presentation_team = '".StringChecker::Check($_POST['teamF'])."'";
	if($_POST['locationF'] != null) $filter[] = " presentation_location = '".StringChecker::Check($_POST['locationF'])."'";
	
//	$filter[] = " presentation_location = 'HIT HOTEL PERLA'";
	$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 
                   AND v.dan = p.cati_day 
                   AND v.ura = p.cati_hour 
                   AND v.cati_location = p.cati_location
           ";
	if(count($filter) > 0){
	    $sql .= " WHERE ";
		$sql .= implode(" AND ", $filter);
	} 
	$result['sql'] = $sql;
	$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."._visitStatistic AS v 
                        RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p 
                        ON v.id_project = p.id_project  
                        AND v.cati_location = p.cati_location
                        AND v.dan = p.cati_day
                        AND v.ura = p.cati_hour
                        WHERE ".$f." AND ".implode(" AND ", $filter);
			else
				$sql = "SELECT COUNT(*) as c 
				        FROM ".WARPIT_CRM."._visitStatistic AS v 
                        RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p 
                        ON v.id_project = p.id_project
                        AND v.cati_location = p.cati_location
                        AND v.dan = p.cati_day
                        AND v.ura = p.cati_hour  
				        WHERE ".$f;
			
            //echo "/*$sql*/";
			//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(p.id_project) as ProjectId, 
    				        cati_hour, 
    				        cati_day, 
    				        p.cati_location 
                        FROM    ".WARPIT_CRM."._visitStatistic AS v 
                            RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p 
                        ON v.id_project = p.id_project  
				        WHERE ".$f." AND ".implode(" AND ", $filter);
			else
				$sql = "SELECT DISTINCT(p.id_project) as ProjectId, 
				            cati_hour, 
				            cati_day, 
				            p.cati_location 
			            FROM ".WARPIT_CRM."._visitStatistic AS v 
                        RIGHT JOIN ".WARPIT_CRM."._PresentatorPermissions AS p 
                        ON v.id_project = p.id_project  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_tel'] > 0 && $row['id_sub_tel'] == 0 && ($row['changedPresentation'] == '' || $row['changedPresentation'] == 0)){
                if(is_array($basedOn))
                    $data[$row[$basedOn[0]]."[x]".$row[$basedOn[1]]]['attendance']++;
                else
                    $data[$row[$basedOn]]['attendance']++;
            }
			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['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_reason'] == 5 && $row['product_amount'] > 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['order_reason'] != 5){
				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'] = true;
		
	}

	//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_total[] = $data[$i]['total'];
		$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_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' => false
	 );
	 $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)."%",
					'appeared_average' => round(((array_sum($stat_attendance) + array_sum($stat_appeared)) / array_sum($stat_invited) * 100), 2)."%", 
					//'customer_avarage' => array_sum($stat_customer)."%",
					'customer_avarage' => round((array_sum($stat_order) / (array_sum($stat_attendance) + array_sum($stat_appeared)) * 100), 2)."%",
					'traffic_avarage' => array_sum($stat_traffic),
					//'realization' => round(array_sum($stat_realization)/$sum, 2)."%", //povprečje procentov: realization/sum
					'realization' => round(array_sum($stat_delivery)/array_sum($stat_order)*100, 2)."%", //procenti izračunani z zadnje vrstice: (delivery/orders)*100
					//'storno_percent' => round(array_sum($stat_storno_percent)/$sum, 2)."%", //povprečje procentov: stornoPercent/sum
					'storno_percent' => round(array_sum($stat_storno)/array_sum($stat_order)*100, 2)."%",//procenti izračunani z zadnje vrstice: (storno/orders)*100
					'order_amount' => array_sum($stat_amount),
					'recognized_income' => array_sum($stat_recognized),
					'color' => false
	 );
	//FB::info($data);
	//FB::data($data);
	
	$result['success'] = TRUE;
    $result['results'] = sizeof($data);
    $result['rows'] = $data != null ? $data : "";
    echo json_encode($result);
}