<?php

include_once("_dirinfo.php");
include_once (PATH_TO_ROOT."init.php");
include_once(PATH_TO_ROOT."config.php");
class Pivot {
	
	public function getCallStatusAction($db)
	{
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$result =	$db->SQLexecute("SELECT status FROM _telephoneLog GROUP BY status");
		
		while ($row = $db->fetchAssoc($result)) {
			$rows[]=$row['status'];
		}
		
		$sql="SELECT id, opis FROM _TelephoneStatus WHERE id IN (";
		for ($i=0;$i<count($rows)-1;$i++)
		{
		$sql .=$rows[$i].",";
		}
		$sql .=$rows[count($rows)-1].");";
		$result =	$db->SQLexecute($sql);
		
		while ($row = $db->fetchAssoc($result)) {
			$proj['id']=$row['id'];
			$proj['value']=$row['opis'];
			$ar[]=$proj;
		}
		
		echo '{success:true, rows:'.json_encode($ar).'}';
	}	
	
	public function getProjectsAction($db)
	{
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$result =	$db->SQLexecute("SELECT idProject FROM _telephoneLog GROUP BY idProject");
		
		while ($row = $db->fetchAssoc($result)) {
			$rows[]=$row['idProject'];
		}
		
		$sql="SELECT id, proj_name FROM _Projects WHERE id IN (";
		for ($i=0;$i<count($rows)-1;$i++)
		{
			$sql .=$rows[$i].",";
		}
		$sql .=$rows[count($rows)-1].");";		
		$result =	$db->SQLexecute($sql);
		
		while ($row = $db->fetchAssoc($result)) {
			$proj['id']=$row['id'];
			$proj['value']=$row['proj_name'];
			$aa[]=$proj;
		}
		echo '{success:true, rows:'.json_encode($aa).'}';
	}
	
	public function getInterwieversAction($db)
	{

		$result =	$db->SQLexecute("SELECT id_user FROM ".DB_WARPIT_WEBCATI_BASE."._telephoneLog GROUP BY id_user");
		
		//posebaj shranim id-je za admine in interwievere
		while ($row = $db->fetchAssoc($result)) {	
			if ($row['id_user']>0)
				$interwiever[]=$row['id_user'];
			else
				$admin[]= -$row['id_user']; 
		}
		
		if (count($interwiever)>0)
		{
			$sql="SELECT id, CONCAT (Firstname,' ', Surname ) AS user FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer  WHERE id IN (";
			for ($i=0;$i<count($interwiever)-1;$i++)
			{
			$sql .=$interwiever[$i].",";
			}
			$sql .=$interwiever[count($interwiever)-1].");";
			$result =	$db->SQLexecute($sql);
			
			while ($row = $db->fetchAssoc($result)) {
			$proj['id']=$row['id'];
			$proj['value']=$row['user'];
			$ar[]=$proj;
			}
		} 
	
		
		if (count($admin)>0)
		{
			$sql="SELECT id, CONCAT (Firstname,' ', Surname ) AS user FROM ".DB_WARPIT_MAIN."._user  WHERE id IN (";
			for ($i=0;$i<count($admin)-1;$i++)
			{
			$sql .= $admin[$i].",";
			}
			$sql .=$admin[count($admin)-1].");";
			$result =	$db->SQLexecute($sql);
				
			while ($row = $db->fetchAssoc($result)) {
			$proj['id']= -$row['id'];
			$proj['value']=$row['user'];
			$ar[]=$proj;
			}
		}
		echo '{success:true, rows:'.json_encode($ar).'}';
	}
	
	public function getProjects($db)
	{
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$result =	$db->SQLexecute("SELECT idProject FROM _telephoneLog GROUP BY idProject");
	
		while ($row = $db->fetchAssoc($result)) {
			$rows[]=$row['idProject'];
		}
		$sql="SELECT id, proj_name FROM _Projects WHERE id IN (";
		for ($i=0;$i<count($rows)-1;$i++)
		{
		$sql .=$rows[$i].",";
		}
		$sql .=$rows[count($rows)-1].");";
		$result =	$db->SQLexecute($sql);
	
		while ($row = $db->fetchAssoc($result)) {
		$proj[$row['id']]=$row['proj_name'];
		}
		return $proj;
	}
	
	public function getCallStatus($db)
	{
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$result =	$db->SQLexecute("SELECT status FROM _telephoneLog GROUP BY status");
	
		while ($row = $db->fetchAssoc($result)) {
			$rows[]=$row['status'];
		}
	
		$sql="SELECT id, opis FROM _TelephoneStatus WHERE id IN (";
		for ($i=0;$i<count($rows)-1;$i++)
		{
		$sql .=$rows[$i].",";
		}
		$sql .=$rows[count($rows)-1].");";
		$result =	$db->SQLexecute($sql);
	
		while ($row = $db->fetchAssoc($result)) {
		$proj[$row['id']]=$row['opis'];
		}
	
		return $proj;
	}	
		
		public function getInterwievers($db)
		{
		
			$result =	$db->SQLexecute("SELECT id_user FROM ".DB_WARPIT_WEBCATI_BASE."._telephoneLog GROUP BY id_user");
		
			//posebaj shranim id-je za admine in interwievere
			while ($row = $db->fetchAssoc($result)) {
				if ($row['id_user']>0)
					$interwiever[]=$row['id_user'];
				else
					$admin[]= -$row['id_user'];
			}
		  
			if (count($interwiever)>0)
			{
				$sql="SELECT id, CONCAT (Firstname,' ', Surname ) AS user FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer  WHERE id IN (";
				for ($i=0;$i<count($interwiever)-1;$i++)
				{
				$sql .=$interwiever[$i].",";
				}
				$sql .=$interwiever[count($interwiever)-1].");";
				$result =	$db->SQLexecute($sql);
		
				while ($row = $db->fetchAssoc($result)) {
				$proj[$row['id']]=$row['user'];
				}
				}
		
		
				if (count($admin)>0)
				{
					$sql="SELECT id, CONCAT (Firstname,' ', Surname ) AS user FROM ".DB_WARPIT_MAIN."._user  WHERE id IN (";
					for ($i=0;$i<count($admin)-1;$i++)
					{
					$sql .= $admin[$i].",";
					}
					$sql .=$admin[count($admin)-1].");";
					$result =	$db->SQLexecute($sql);
		
					while ($row = $db->fetchAssoc($result)) {
					$proj[-$row['id']]= $row['user'];
					}
					}
					return $proj;
		}

	public function fillPivotAction($db)
	{
		$row=$_GET['row'];
		$column=$_GET['column'];
		$stat=$_GET['stat'];
		
		$sql="SELECT id, id_user, idProject, status, call_time, end_time, COUNT( * ) AS sestevek FROM _telephoneLog ".$stat." GROUP BY id_user, idProject, status_rec";
		$left1=$this->setXYValue($row);
		$top1=$this->setXYValue($column);
		$valueL1=$this->setValueArray($db,$row);
		$valueT1=$this->setValueArray($db,$column);
		
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$result =	$db->SQLexecute($sql);
		
		while ($row = $db->fetchAssoc($result)) {
			$rows['l1']=$valueL1[$row[$left1]];
			$rows['t1']=$valueT1[$row[$top1]];
			$rows['st']=$row['sestevek'];
			$ar[]=$rows;
		}
		
		echo '{success:true, rows:'.json_encode($ar).'}';
	}
	
	
	
	private function setXYValue($par)
	{
		if ($par=="Project") return "idProject";
		else if ($par=="Time") return "call_time";
		else if ($par=="Interviewers") return "id_user";
		else if ($par=="Call status") return "status";
		
		return 0;
	}
	
	private function setValueArray($db,$par)
	{
		if ($par=="Project") $ar= $this->getProjects($db);
		else if ($par=="Time") $ar= "call_time";
		else if ($par=="Interviewers") $ar=  $this->getInterwievers($db);
		else if ($par=="Call status") $ar=  $this->getCallStatus($db);
		return $ar;

	}
		
		
	}
$manipulator = new Pivot();
$comm = $_REQUEST['action'].'Action';
if(method_exists($manipulator,$comm)) {
	echo $manipulator->$comm($db);
} else {
	echo '{success:false,error:"Unknown action!'.$comm.'}';
}


?>
