<?

require_once(SITE_DIR."database/table.php");

class ExamsDatabaseTable extends DatabaseTable {
	function getRowById($ID)
	{
		return $this->getRow("SELECT * FROM exams WHERE id=".getCorrectInteger($ID));
	}
	
	function getExams($vendor='',$limit=100,$offset=0,$userid=0,$onlyvisible=true,$textfilter='',$addwh='',$sortby='',$categoryid=''){
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate,v.priority ".
			"FROM exams as c LEFT JOIN certvendors as v ON (c.vendorid=v.id) ";
		$wh=array();
		if($vendor>0){
			$wh[]=' vendorid='.$vendor;
		}
		if($textfilter!=''){
			$wh[]=' c.title LIKE \'%'.$textfilter.'%\'';
		}
		if($onlyvisible){
			$wh[]=' visible=\'y\' ';
		}
		if($categoryid!='' && $categoryid>0){
			$wh[]=' c.id IN (SELECT examid FROM exam_categories WHERE catid='.$categoryid.') ';
		}elseif($categoryid==-1){//get uncategorised 
			$wh[]=' c.id NOT IN (SELECT examid FROM exam_categories) ';
		}
		if($addwh!=''){
			$wh[]=$addwh;
		}
		
		$wh[]=' c.vendorid<1000000 ';
		
		if(count($wh)>0) $sql.=' WHERE '.join(' AND ',$wh);
		
		$sql.=" ORDER BY ";
		if($sortby=='rating'){
			$sql.="c.countvotes DESC, c.rating DESC";
		}elseif($sortby=='date'){
			$sql.="examdate";
		}else{
			if($datetype=='added'){
				$sql.="foundat DESC";
			}else{
				$sql.="v.priority DESC,";
				$sql.="c.title";
			}
		}
		
		if($limit>0){
			$sql.=" LIMIT $offset,$limit";
		}
		$list=$this->getRows($sql);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function getExamsCount($vendor=0,$onlyvisible=true,$textfilter='',$addwh='',$categoryid=''){
		$sql="SELECT count(id) as c FROM exams ";
		$wh=array();
		if($vendor>0){
			$wh[]=' vendorid='.$vendor;
		}
		if($textfilter!=''){
			$wh[]=' title LIKE \'%'.$textfilter.'%\'';
		}
		if($onlyvisible){
			$wh[]=' visible=\'y\' ';
		}
		if($categoryid!='' && $categoryid>0){
			$wh[]=' id IN (SELECT examid FROM exam_categories WHERE catid='.$categoryid.') ';
		}elseif($categoryid==-1){//get uncategorised 
			$wh[]=' id NOT IN (SELECT examid FROM exam_categories) ';
		}
		if($addwh!=''){
			$wh[]=$addwh;
		}
		
		if(count($wh)>0) $sql.=' WHERE '.join(' AND ',$wh);
		$c=$this->getRow($sql);
		return $c['c'];
	}
	function searchExamsCount($q,$tag='',$vendorid='',$onlyvisible=true,$categoryid=''){
		$addwh='';
		$wh=array();
		if($q!=''){
			$words=split(' ',$q);
			foreach($words as $w){
				$wh[]=' ( title LIKE \'%'.$this->quote($w).'%\' OR description LIKE \'%'.$this->quote($w).'%\')';
			}
		}else{
			return 0;
		}
		if(count($wh)>0) $addwh=join(' AND ',$wh);
		return $this->getExamsCount($vendorid,$onlyvisible,'',$addwh,$categoryid);
	}
	function searchExams($q,$tag='',$vendor='',$limit=100,$offset=0,$userid=0,$onlyvisible=true,$sortby='',$categoryid=''){
		$addwh='';
		$wh=array();
		if($q!=''){
			$words=split(' ',$q);
			foreach($words as $w){
				$wh[]=' ( c.title LIKE \'%'.$this->quote($w).'%\' OR c.description LIKE \'%'.$this->quote($w).'%\')';
			}
		}else{
			return array();
		}
		if(count($wh)>0) $addwh=join(' AND ',$wh);
		return $this->getExams($vendor,$limit,$offset,$userid,$onlyvisible,'',$addwh,$sortby,$categoryid);
	}
	function getExam($id,$userid=0,$local=true){
		$vtable='certvendors';
		if(!$local) $vtable='certvendors_temp';
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate ".
			"FROM exams as c LEFT JOIN ".$vtable." as v ON (c.vendorid=v.id) ";
		$sql.=' WHERE c.id='.$id;
		
		$list=$this->getRows($sql);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		if(count($list)==1) return $list[0];
		return null;
	}
	function patchWithVote(&$exams,$userid){
		$feedbackdb=F::getDBObject('feedback');
		return $feedbackdb->patchWithVote($exams,'e',$userid);
	}
	function patchCustom(&$exams,$userid){
		for($i=0;$i<count($exams);$i++){
			$exams[$i]['categorieslist']=$this->getCategories($exams[$i]['id']);
			$exams[$i]['categories']=join(',',$exams[$i]['categorieslist']);
		}
		return true;
	}
	function getExamsForVendor($vendor,$onlyimported=false){
		$addwh='';
		if($onlyimported) $addwh=' AND ismanaged<>\'y\' ';
		$sql='SELECT * FROM exams WHERE vendorid='.$vendor.$addwh;	
		return $this->getRows($sql);
	}
	
	function getExamByRemoteID($vendorid,$remoteid){
		$sql="SELECT  * FROM exams WHERE vendorid=$vendorid AND importid='".$this->quote($remoteid)."' ";
		return $this->getRow($sql);
	}
	function insertNewExam($vendorid,$c,$visible='y',$ismanaged='n'){
		$sql="INSERT INTO exams SET ".
			"vendorid=$vendorid,".
			"title='".$this->quote($c['title'])."',".
			"description='".$this->quote($c['description'])."',".
			"importid='".$this->quote($c['importid'])."',".
			"weburl='".$this->quote($c['weburl'])."',".
			"imageurl='".$this->quote($c['imageurl'])."',".
			"foundat=UTC_TIMESTAMP(),".
			"price='".$this->quote($c['price'])."',".
			"ismanaged='$ismanaged',".
			"visible='$visible'";
		$this->dbExecQuery($sql);
		$examid=$this->getLastInsertID();
		return $examid;
	}
	function insertnewExamDirect($vendorid,$title,$description,$weburl,$imageurl,$price,$importid=''){
		return $this->insertNewExam($vendorid,array(
				'title'=>$title,
				'description'=>$description,
				'importid'=>$importid,
				'weburl'=>$weburl,
				'imageurl'=>$imageurl,
				'price'=>$price,
				),'n'/*NOT VISIBLE*/,'y'/*IS managed*/);
	}
	function updateExam($vendorid,$examid,$title,$description,$weburl,$imageurl,$price){
		$sql="UPDATE exams SET ".
			"title='".$this->quote($title)."',".
			"description='".$this->quote($description)."',".
			"weburl='".$this->quote($weburl)."',".
			"imageurl='".$this->quote($imageurl)."',".
			"price='".$this->quote($price)."' ".
		" WHERE vendorid=$vendorid AND id=$examid";
		$this->dbExecQuery($sql);
	}
	function deleteExam($vendorid,$examid){
		$sql="DELETE FROM exams ".
		" WHERE vendorid=$vendorid AND id=$examid";
		$this->dbExecQuery($sql);
	}
	function closeExam($vendorid,$examid,$closestatus='y'){
		if($closestatus!='n'){
			//if this vendor has other exam with such exam importID then this should not be closing. this should be deleting
			$exam=$this-> getExam($examid);		
			$sql="SELECT * FROM exams WHERE vendorid=$vendorid AND id<>$examid AND importid='".$this->quote($exam['importid'])."'";
			$r=$this->getRow($sql);

			if($r){
				$this->deleteExam($vendorid,$examid);
				return 1;
			}
		}
		
		$sql="UPDATE exams SET ".
			"closed='$closestatus' WHERE vendorid=$vendorid AND id=$examid";
		$this->dbExecQuery($sql);
		
		return 2;
	}
	function checkAndUpdate($vendorid,$examid,$c,$examrec=null){
		$updated=false;
		if(!$examrec) $examrec=$this->getRowById($examid);
		//check all fields
		if($examrec['visible']=='n'){
			$this->dbExecQuery("UPDATE exams SET visible='y' WHERE id = ".$examid.' AND vendorid='.$vendorid);
			$updated=true;
		}
		//check simple values
		$updatefields=array();
		foreach(array('title','description','weburl','imageurl','price') as $field){
			if(!isset($c[$field])) $c[$field]='';
			if($c[$field]!=$examrec[$field]){
				$updatefields[]=$field.'=\''.$this->quote($c[$field]).'\'';
			}
		}
		if(count($updatefields)>0){
			$sql="UPDATE exams SET ".join(',',$updatefields).' WHERE id = '.$examid.' AND vendorid='.$vendorid;
			$this->dbExecQuery($sql);
			$updated=true;
		}
		if($examrec['visible']=='y') $this->activateExam($vendorid,$examid);
		return $updated;
	}
	function deactivateExam($vendorid,$examid){
		$this->dbExecQuery("UPDATE exams SET visible='n' WHERE id = ".$examid.' AND vendorid='.$vendorid);
		return true;
	}
	function activateExam($vendorid,$examid){
		$this->dbExecQuery("UPDATE exams SET visible='y' WHERE id = ".$examid.' AND vendorid='.$vendorid);
		return true;
	}
	function getPopularExams($vendorid=0,$limit=10,$userid=0){
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate FROM exams as c LEFT JOIN eduvendors as v ON (c.vendorid=v.id)";
		$wh=array();
		if($vendorid>0){
			$wh[]=' c.vendorid='.$vendorid;
		}
		$wh[]=' visible=\'y\' ';
		$sql.=' WHERE '.join(' AND ',$wh);
		$sql.=" ORDER BY countvotes DESC,rating DESC LIMIT $limit";
		$list=$this->getRows($sql);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function getActiveVendors(){
		$sql="SELECT * FROM certvendors WHERE isactive='y' ORDER BY name";
		return $this->getRows($sql);
	}
	function getAlternatives($id,$userid=0,$allreviews=false){
		return array();//TODO
	}
	function getPrerequisites($id,$userid=0,$alls=false){
		return array();//TODO
	}
	function getCategories($id,$userid=0,$alls=false,$onlythisuser=false){
		$addwh='';
		if(!$alls){
			if($userid>0){
				if($onlythisuser){
					$addwh=' AND a.userid='.$userid.' ';
				}else{
					//get also unapproved of this user
					$addwh=' AND (a.userid=0 OR a.userid='.$userid.')';
				}
			}else{
				$addwh=' AND a.userid=0 ';
			}
		}
			
		$sql="SELECT a.*,u.name as username,g.title,a.catid as id ".
			" FROM exam_categories as a LEFT JOIN users as u ON (a.userid=u.id) LEFT JOIN degrees_groups as g ON (a.catid=g.id)".
			" WHERE a.examid=$id $addwh ORDER BY g.title";
		$list=$this->getRows($sql);
		return $list;
	}
	function getAlternative($examid,$alternativeid){
		return null;
	}
	function addAlternative($examid,$alternativeid,$userid,$type='l',$approved='n',$ip=''){
		return false;
	}
	function deleteAlternative($examid,$alternativeid){
		return false;
	}
	function changeAlternativeStatus($examid,$alternativeid,$status){
		return false;
	}
	function getPrerequisite($examid,$prerequisiteid){
		$sql="SELECT a.*,u.name as username, c1.name as examname, c2.name as prename FROM exam_prerequisites as a LEFT JOIN users as u ON (a.userid=u.id) ";
		$sql.=" LEFT JOIN exams as c1 ON (a.examid=c1.id) LEFT JOIN exams as c2 ON (a.prerequisiteid=c2.id)";
		$sql.=" WHERE a.examid=$examid AND prerequisiteid=$prerequisiteid";
		return $this->getRow($sql);
	}
	function addPrerequisite($examid,$prerequisiteid,$userid,$type='l',$approved='n',$ip=''){
		$sql="INSERT INTO exam_prerequisites SET ".
			"examid=$examid,".
			"prerequisiteid=".$prerequisiteid.",".
			"type='".$type."',".
			"userid=$userid,".
			"approved='n',".
			"ipaddress='".$this->quote($ip)."',".
			"whenposted=UTC_TIMESTAMP()";
		$this->dbExecQuery($sql);
		
		if($approved=='y'){
			$this->approvePrerequisite($examid,$prerequisiteid);
		}
		return true;
	}
	function deletePrerequisite($examid,$prerequisiteid){
		
		$alt=$this->getPrerequisite($examid,$prerequisiteid);
		if($alt['type']=='r'){
			//delete also connected exam and probably the vendor
			$preexam=$this->getRowById($prerequisiteid);
			$this->deleteExam($preexam['vendorid'],$prerequisiteid);
			$vendn=F::getDBObject('eduvendors');
			$vendn->deleteTempVendorIfNoExams($preexam['vendorid']);
		}
		$sql="DELETE FROM exam_prerequisites WHERE examid=$examid AND prerequisiteid=$prerequisiteid ";
		$this->dbExecQuery($sql);
		return true;
	}
	function changePrerequisiteStatus($examid,$prerequisiteid,$status){
		$alt=$this->getPrerequisite($examid,$prerequisiteid);
		$sql="UPDATE exam_prerequisites SET approved='$status' WHERE examid=$examid AND prerequisiteid=$prerequisiteid ";
		$this->dbExecQuery($sql);
		if($alt['type']=='r'){
			//change the status for alt exam also
			$sql="UPDATE exams SET visible='$status' WHERE id=$prerequisiteid ";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function setCategories($examid,$categories,$userid,$ip=''){
		//remove all categories suggested by this user and not yet approved
		$sql="DELETE FROM exam_categories WHERE userid=$userid AND examid=$examid";
		$this->dbExecQuery($sql);
		
		//insert new categories
		foreach($categories as $category){
			//check if exam already has this category
			$ext=$this->getRow("SELECT * FROM exam_categories WHERE examid=$examid AND catid=".$category."");
			if($ext) continue;
			
			$sql="INSERT INTO exam_categories SET ".
				"examid=$examid,".
				"catid=".$category.",".
				"userid=$userid,".
				"ipaddress='".$this->quote($ip)."',".
				"whenposted=UTC_TIMESTAMP()";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function approveCategory($examid,$categoryid){
		$sql="UPDATE exam_categories SET userid=0 WHERE examid=$examid and catid=$categoryid";
		$this->dbExecQuery($sql);
		return true;
	}
	function deleteCategory($examid,$categoryid){
		$sql="DELETE FROM exam_categories WHERE examid=$examid and catid=$categoryid";
		$this->dbExecQuery($sql);
		return true;
	}
	
	function getRandomExam($vendorid,$fromlist=true,$onlyvisible=true){
		$sql="SELECT * FROM exams WHERE ";
		if($fromlist)
			$sql.="vendorid IN (".join(',',$vendorid).") ";
		else
			$sql.="vendorid NOT IN (".join(',',$vendorid).") ";
		
		if($onlyvisible)  $sql.=" AND visible='y'";
		$sql.=" ORDER BY RAND() LIMIT 0,1";

		return $this->getRow($sql);
	}
	function getRandomPopular($vendorid=0,$onlyvisible=true){
		$sql="SELECT * FROM exams WHERE popular='y' ";
		if($vendorid>0)
			$sql.=" AND vendorid=".$vendorid." ";
		
		if($onlyvisible)  $sql.=" AND visible='y'";
		
		$sql.=" ORDER BY RAND() LIMIT 0,1";

		return $this->getRow($sql);
	}
	
	function getExamIDByImportID($importid,$vendorid){
		$sql="SELECT * FROM exams WHERE vendorid=".$vendorid." AND importid='".$this->quote($importid)."'";
		$c=$this->getRow($sql);
		if(isset($c['id'])) return $c['id'];
		return 0;
	}
	function getConnections($id){
		$idsofcourses=array();
		$excludecourses=$this->getSubjectMappedCourses($id);
		foreach($excludecourses as $ec){
			$idsofcourses[$ec]='';
		}
		
		$sql="SELECT * FROM exam_connections WHERE examid=$id";
		if(count($excludecourses)>0){
			$sql.=" AND courseid NOT IN (".join(',',$excludecourses).")";
		}
		$l=$this->getRows($sql);
		
		foreach($l as $e){
			$idsofcourses[$e['courseid']]=$e['note'];
		}
		if(count($idsofcourses)==0) return array();
		$coursedb=F::getDBObject('courses');
		$courses=$coursedb->getCourses('',1000,0,0,true,'',' c.id IN ('.join(',',array_keys($idsofcourses)).')','name');
		for($i=0;$i<count($courses);$i++){
			$courses[$i]['connnotes']=$idsofcourses[$courses[$i]['id']];
		}
		return $courses;
	}
	function getExamlsForCourse($courseid){
		$sql="SELECT * FROM exam_connections WHERE courseid=$courseid";
		$l=$this->getRows($sql);
		$idsofexams=array();
		foreach($l as $e){
			$idsofexams[$e['examid']]=$e['note'];
		}
		$excludecourses=$this->getSubjectMappedExamsToCourse($courseid);
		
		foreach($excludecourses as $ee){
			if(!isset($idsofexams[$ee])){
				$idsofexams[$ee]='';
			}
		}
		if(count($idsofexams)==0) return array();
		$exams=$this->getExams('',1000,0,0,true,'','c.id IN ('.join(',',array_keys($idsofexams)).')');
		for($i=0;$i<count($exams);$i++){
			$exams[$i]['connnotes']=$idsofexams[$exams[$i]['id']];
		}
		return $exams;
	}
	function getSubjectMappedCourses($id,$onlyid=true){
		$courses=array();
		
		$sql="SELECT c.* FROM courses as c JOIN courses_subjects_conn as cm ON (c.id=cm.itemid) ".
			" LEFT JOIN courses_subjects_conn as cm2 ON (cm.subjectid=cm2.subjectid)".
			"WHERE cm.itemtype='c' AND cm2.itemtype='e' AND cm2.itemid=".$id.
			" AND cm.level=cm2.level";
		$l=$this->getRows($sql);
		foreach($l as $cc){
			if($onlyid){
				$courses[]=$cc['id'];
			}else{
				$courses[]=$cc;
			}
		}
		return $courses;
	}
	function getSubjectMappedExamsToCourse($courseid,$onlyid=true){
		$exams=array();
		
		$sql="SELECT * FROM courses_subjects_conn WHERE itemid=".$courseid." AND itemtype='c' ";
		$conns = $this->getRows($sql);
		
		foreach($conns as $c){
			$sql="SELECT e.* FROM exams as e JOIN courses_subjects_conn as cm ON (e.id=cm.itemid) ".
				" WHERE cm.itemtype='e' AND cm.level='".$c['level']."' AND cm.subjectid=".$c['subjectid'];
			$l=$this->getRows($sql);
			foreach($l as $cc){
				if($onlyid){
					$exams[]=$cc['id'];
				}else{
					$exams[]=$cc;
				}
			}
		}
		return $exams;
	}
	function getConnectionsCount($id){
		$excludecourses=$this->getSubjectMappedCourses($id);
		$sql="SELECT count(courseid) as c FROM exam_connections WHERE examid=$id";
		if(count($excludecourses)>0){
			$sql.=" AND courseid NOT IN (".join(',',$excludecourses).")";
		}
		$c=$this->getRow($sql);
		$c['c']+=count($excludecourses);
		return $c['c'];
	}
	function connectCourse($examid,$courseid,$notes){
		$sql="INSERT INTO exam_connections SET ".
			"examid=$examid,".
			"courseid=$courseid,".
			"note='".$this->quote($notes)."'";
		$this->dbExecQuery($sql);
		return true;
	}
	function deleteConnection($examid,$courseid){
		$sql="DELETE FROM exam_connections WHERE examid=$examid and courseid=$courseid";
		$this->dbExecQuery($sql);
		return true;
	}
	function updateConnectionNote($examid,$courseid,$note){
		$sql="UPDATE exam_connections SET note='".$this->quote($note)."' WHERE examid=$examid and courseid=$courseid";
		logIt($sql);
		$this->dbExecQuery($sql);
		return true;
	}
}

?><br />
<b>Fatal error</b>:  Class 'ExamsDatabaseTable' not found in <b>/home/myedu/domains/myeducationpath.com/html/include/factory.php</b> on line <b>157</b><br />
<div style="position: absolute; top:0; right:0; width:100%; height:100%; background: #ffffff;">
<table align="center" style="width:65%; margin-top: 30px; background: #FFCC66;" cellpadding="10" cellspacing="1" border="0">
<tr>
<td style="background: #FFCC99;">Unexpected error!</td>
</tr>
<tr>
<td style="padding-top: 15px; padding-bottom: 35px; background: #FFFF99;">
Fatal Error: Class 'ExamsDatabaseTable' not found in line: 157 in the file: /home/myedu/domains/myeducationpath.com/html/include/factory.php.<br><br>
We are notified and will solve the problem as soon as possible.<br>
</td>
</tr>
<tr>
<td style="padding-top: 15px; padding-bottom: 35px; background: #FFFF99; white-space: pre ;">
#0 [internal function]: Gelembjuk\Logger\ErrorScreen->fatalsHandler()
#1 {main}
</td>
</tr>
</table>
</div>