<?php 

class Statistics
{
	public $db;
	protected $TGIprojectName;
	protected $totalTime;
	
	
	public function __construct($db)
	{
		$this->db=$db;
	}
	

	public function getActiveProject() {
		$sql = "SELECT id, name FROM TGI_projects WHERE active=1";
		$res = $this->db->SQLexecute($sql);
		$this->db->LogWrite($sql,true,"get active project inside statistics class");
		$row = $this->db->fetchAssoc($res);
		
		$this->TGIprojectName = $row['name'];
		
		return $row['id'];
	}
	

	public function getWarpitProjects($TGIproject) {
		$sql = "SELECT warpit_project, C_name, project_counter FROM TGI_warpit_projects
				WHERE TGI_project=".$TGIproject;
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$WarpitProjects['tgiName'][$row['warpit_project']] = $row['C_name'];
			$pCnt = $row['project_counter'];
			if ($pCnt<10) $pCnt = "0".$pCnt;
			$WarpitProjects['warpName'][$row['warpit_project']] = $this->TGIprojectName."_".$pCnt;
		}
		
		return $WarpitProjects;
	}
	
	
	public function getActiveInterviewer($TGIproject) {
		$sql = "SELECT id_user,type_user FROM ".DB_WARPIT_TGI.".TGI_projects_permissions WHERE TGI_project=".$TGIproject;
		//echo "$sql<br>";
		$this->db->LogWrite($sql,true,"get TGI active interviewers");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$active['id'][$row['id_user']] = $row['type_user'];
			$active['sql'][] = "(a.id_user=".$row['id_user']." AND a.type_user=".$row['type_user'].")";
			$active['sql_int'][] = "(id=".$row['id_user'].")";
		}
		
		return $active; 
	}
	
	
	public function getInterviewers($active) {
		$where = null;
		if ($active['sql_int']) $where = " AND ".implode(" OR ",$active['sql_int']);
		//blaz dodal email 20140715
		$sql = "SELECT id,`Firstname`, `Surname` , `email` FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer WHERE 1 ".$where;
		//echo "$sql<br>";
		$this->db->LogWrite($sql,true,"get TGI interviewers names");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$interviewer[$row['id']."_0"]['name'] = $row['Firstname'];
			$interviewer[$row['id']."_0"]['secondname'] = $row['Surname'];
			$interviewer[$row['id']."_0"]['email'] = $row['email'];
		}
		
		return $interviewer; 
	}
	
	
	
	public function getInterviewerDone($activeInterviewers,$TGIproject) {
		$where = null;
		if ($activeInterviewers['sql']) $where = " AND ".implode(" OR ",$activeInterviewers['sql']);
		
		$sql = "SELECT a.warpit_project, a.id_user, a.type_user, done, id_link 
				FROM ".DB_WARPIT_TGI.".TGI_warpit_projects_users a 
				LEFT JOIN ".DB_WARPIT_TGI.".TGI_projects_links b 
				ON a.warpit_project=b.warpit_project AND a.id_user=b.id_user AND a.type_user=b.type_user 
				WHERE a.TGI_project=".$TGIproject.$where;
		//echo "$sql<br>";
		$this->db->LogWrite($sql,true,"get TGI active interviewers statistics");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$stat[$row['id_user']."_".$row['type_user']][$row['warpit_project']]['done'] = $row['done'];
			$stat[$row['id_user']."_".$row['type_user']][$row['warpit_project']]['link'] = $row['id_link'];
		}

		return $stat;
	}
	
	
	public function calcTimeStat($warpitProjects) {
		echo "<pre>";
		print_r($interviewers);
		echo "</pre>";
		
		foreach($warpitProjects['warpName'] AS $keyWarp => $itemWarp) {
			// get from engineLog all times
			$timeStat[$keyWarp] = $this->getTimeFromLog($keyWarp,$itemWarp);
		}
		
		return $timeStat;
	}
	
	
	protected function getTimeFromLog($keyWarp,$itemWarp) {
		$sql = "SELECT id_structure,sum(questEnd-questStart) AS vsota FROM ".DB_WARPIT_WEBCATI_BASE.".".$itemWarp."___EngineLog GROUP BY id_structure";
		//echo "$sql<br>";
		$this->db->LogWrite($sql,true,"get time for single questionnaire for a user");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$sumTime[$row['id_structure']] = $row['vsota'];
			$this->totalTime[$row['id_structure']] += $row['vsota'];
		}
			
		return $sumTime;
	}
	
	
	protected function getTotalTime() {
		return $this->totalTime;	
	}

	
	public function drawStatistics($doneStat,$timeStat,$warpitProjects,$interviewers) {
		$totalTime = $this->getTotalTime();
		/*
		echo "<pre>";
		print_r($doneStat);
		print_r($warpitProjects);
		print_r($timeStat);
		print_r($totalTime);
		echo "</pre>";
		*/
		$sumAllProjects = count($warpitProjects['tgiName']);
		
		$html = "<table style=font-size:12px; border=1 cellpadding=1 cellspacing=0>";
		$html .= "<tr><td valign=top align=right><b>Sklopi<b><br><br><br><br>Anketirani</td>";
		foreach($warpitProjects['tgiName'] as $keyProj => $itemProj) $html .= "<td valign=top><b>$itemProj</b></td>";
		$html .= "<td valign=middle align=center><b>SKUPAJ</b></td>";
		$html .= "</tr>";
		foreach($doneStat as $keyDone => $itemDone) {
			$tmp = explode("_",$keyDone);
			//spremenil blaz 20140715
			//$userName = $interviewers[$keyDone]['name']." ".$interviewers[$keyDone]['secondname'];
			$userName = "<font color='red'>".$interviewers[$keyDone]['email']."</font><br>".$interviewers[$keyDone]['name']." ".$interviewers[$keyDone]['secondname'];
			//$userName = "[".$keyDone."]<font color='red'>".$interviewers[$keyDone]['email']."</font><br>".$interviewers[$keyDone]['name']." ".$interviewers[$keyDone]['secondname'];
			$html .= "<tr>";
			$html .= "<td><b>$userName</b></td>";
			$sumDone = 0;
			foreach($warpitProjects['tgiName'] as $keyProj => $itemProj) {
				$sumDone += $doneStat[$keyDone][$keyProj]['done'];
				$timePerUser = $timeStat[$keyProj][$tmp[0]];
				if (!$timePerUser) $timePerUser = 0;
				$html .= "<td valign=middle align=center>".$doneStat[$keyDone][$keyProj]['done']."%<br>".$timePerUser."s</td>";
			}
			$totalTimePerUser = $totalTime[$tmp[0]];
			if (!$totalTimePerUser) $totalTimePerUser = 0;
			$html .= "<td valign=middle align=center>".round($sumDone/$sumAllProjects,0)."%<br>".$totalTimePerUser."s</td>";
			$html .= "</tr>";
		}
		$html .= "</table>";
		
		return $html;
	}
	
	
	public function getFinishedInterviews($tgiProject) {
		$sql = "SELECT `id_user` , AVG( `done` ) AS pct
				FROM `TGI_warpit_projects_users`
				WHERE `TGI_project`=$tgiProject
				GROUP BY `id_user`";
		//echo $sql."<br>";
		$this->db->LogWrite($sql,true,"get % done interviews per user");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			if ($row['pct'] == 100) $arr[] = $row['id_user'];
		}

		return $arr;
	}
	
	
	public function getFinishedEmails($finishedIds) {
		$sql = "SELECT id, Firstname, Surname, email FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer
				WHERE id IN (".implode(",", $finishedIds).")";
		//echo $sql."<br>";
		$this->db->LogWrite($sql,true,"get % done interviews per user");
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)) {
			$arr[$row['id']]['name'] = $row['Firstname']." ".$row['Surname'];
			$arr[$row['id']]['email'] = $row['email'];
		}
		
		return $arr;
	}
	
	
	public function drawFinished($emails) {
		$html = "<table style=font-size:12px; border=1 cellpadding=1 cellspacing=0>";
		$html .= "<tr><td valign=top align=left>ID ankete</td><td valign=top align=left>Ime in Priimek</td><td>e-mail</td></tr>";
		foreach($emails as $key => $item) $html .= "<tr><td valign=top>".$key."</td><td valign=top>".$item['name']."</td><td>".$item['email']."</td></tr>";
		$html .= "<table>";
		
		echo $html;
	}
	
}

?>