<?

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

class CoursesDatabaseTable extends DatabaseTable {
	function getRowById($ID)
	{
		return $this->getRow("SELECT * FROM courses WHERE id=".getCorrectInteger($ID));
	}
	function getDateFilter($datetype){
		if($datetype=='recent'){
			return 'str_to_date( startdate, \'%Y-%m-%d\' ) < UTC_TIMESTAMP() AND str_to_date( startdate, \'%Y-%m-%d\' )>DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)';
		}elseif($datetype=='added'){
			return ' foundat>DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 DAY)';
		}elseif($datetype=='upcoming'){
			return 'str_to_date( startdate, \'%Y-%m-%d\' ) > UTC_TIMESTAMP()';
		}elseif($datetype=='ongoing'){
			return $this->getDateFilter('recent');
		}elseif($datetype=='finished'){
			
		}elseif($datetype=='alwaysopen'){
			return '(startdate=\'\' OR startdate=\'undef\')';
		}
		return '';
	}
	function getPriceFilter($pricetype){
		if($pricetype=='free'){
			return ' (price=\'\' OR LOWER(price)=\'free\' or price=\'undef\')';
		}else{
			return ' (price<>\'\' AND LOWER(price)<>\'free\' AND price<>\'undef\')';
		}
		return '';
	}
	function getCourses($vendor='',$limit=100,$offset=0,$userid=0,$onlyvisible=true,$textfilter='',$addwh='',$sortby='',$datetype='',$pricetype='',$categoryid='',$degreeid=''){
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate,v.priority,str_to_date( startdate, '%Y-%m-%d' ) AS coursedate ".
			" FROM courses as c LEFT JOIN eduvendors as v ON (c.vendorid=v.id) ";
		$wh=array();
		if($vendor>0){
			$wh[]=' vendorid='.$vendor;
		}
		if($textfilter!=''){
			$wh[]=' c.name LIKE \'%'.$textfilter.'%\'';
		}
		if($onlyvisible){
			$wh[]=' visible=\'y\' AND v.isactive=\'y\' ';
		}
		if($categoryid!='' && $categoryid>0){
			$wh[]=' c.id IN (SELECT courseid FROM course_categories WHERE catid='.$categoryid.') ';
		}elseif($categoryid==-1){//get uncategorised 
			$wh[]=' c.id NOT IN (SELECT courseid FROM course_categories) AND c.vendorid<>12 ';
		}elseif($degreeid!='' && $degreeid>0){
			$wh[]=' c.id IN (SELECT courseid FROM course_degrees WHERE degreeid='.$degreeid.') ';
		}
		if($addwh!=''){
			$wh[]=$addwh;
		}
		if($datetype!=''){
			$wh[]=$this->getDateFilter($datetype);
			if($datetype=='upcoming') $sortby='date';
		}
		if($pricetype!=''){
			$wh[]=$this->getPriceFilter($pricetype);
		}
		
		$wh[]=' c.vendorid<1000000 ';
		
		if(count($wh)>0) $sql.=' WHERE '.join(' AND ',$wh);
		
		$sql.=" ORDER BY v.priority DESC, ";
		if( $sortby == 'rating') {
			$sql.="c.countvotes DESC, c.rating DESC";
		} elseif($sortby=='date') {
			$sql.="coursedate";
		} elseif($sortby=='price') {
			$sql.="price";
		} elseif($sortby=='visible') {
			$sql.="visible DESC, c.name";
		} else {
			if($datetype=='added'){
				$sql.="foundat DESC";
			}else{
				$sql.="v.priority DESC,";
				$sql.="c.name";
			}
		}
		
		if($limit>0){
			$sql.=" LIMIT $offset,$limit";
		}
		$list=$this->getRows($sql);
		
		logIt($sql);
		
		$this->patchWithTags($list);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function getCoursesCount($vendor=0,$onlyvisible=true,$textfilter='',$addwh='',$datetype='',$pricetype='',$categoryid='',$degreeid=''){
		$sql="SELECT count(id) as c FROM courses ";
		$wh=array();
		if($vendor>0){
			$wh[]=' vendorid='.$vendor;
		}
		if($textfilter!=''){
			$wh[]=' name LIKE \'%'.$textfilter.'%\'';
		}
		if($onlyvisible){
			$wh[]=' visible=\'y\' ';
		}
		if($datetype!=''){
			$wh[]=$this->getDateFilter($datetype);
		}
		if($pricetype!=''){
			$wh[]=$this->getPriceFilter($pricetype);
		}
		if($categoryid!='' && $categoryid>0){
			$wh[]=' id IN (SELECT courseid FROM course_categories WHERE catid='.$categoryid.') ';
		}elseif($categoryid==-1){//get uncategorised 
			$wh[]=' id NOT IN (SELECT courseid FROM course_categories) AND vendorid<>12 ';
		}elseif($degreeid!='' && $degreeid>0){
			$wh[]=' id IN (SELECT courseid FROM course_degrees WHERE degreeid='.$degreeid.') ';
		}
		if($addwh!=''){
			$wh[]=$addwh;
		}
		
		if(count($wh)>0) $sql.=' WHERE '.join(' AND ',$wh);
		$c=$this->getRow($sql);
		return $c['c'];
	}
	function searchCoursesCount($q,$tag,$vendorid='',$onlyvisible=true,$datetype='',$pricetype='',$categoryid='',$degreeid='',$morewh=''){
		$addwh='';
		$wh=array();
		if($morewh!='') $wh[]=$morewh;
		if($tag!=''){
			//get courses IDs for tags and then filter with IDs
			$ids=$this->getCoursesIDsByTag($tag);
			if(count($ids)==0) return 0;
			$wh[]=' id IN ('.join(',',$ids).')';
		}elseif($q!=''){
			$words=split(' ',$q);
			foreach($words as $w){
				$wh[]=' ( name LIKE \'%'.$this->quote($w).'%\' OR description LIKE \'%'.$this->quote($w).'%\')';
			}
			//$wh[]=' ( name LIKE \'%'.$this->quote($q).'%\' OR description LIKE \'%'.$this->quote($q).'%\')';
		}else{
			return 0;
		}
		if(count($wh)>0) $addwh=join(' AND ',$wh);
		return $this->getCoursesCount($vendorid,$onlyvisible,'',$addwh,$datetype,$pricetype,$categoryid,$degreeid);
	}
	function searchCourses($q,$tag,$vendor='',$limit=100,$offset=0,$userid=0,$onlyvisible=true,$sortby='',$datetype='',$pricetype='',$categoryid='',$degreeid='',$morewh=''){
		$addwh='';
		$wh=array();
		if($morewh!='') $wh[]=$morewh;
		if($tag!=''){
			//get courses IDs for tags and then filter with IDs
			$ids=$this->getCoursesIDsByTag($tag);
			if(count($ids)==0) return array();
			$wh[]=' c.id IN ('.join(',',$ids).')';
		}elseif($q!=''){
			$words=split(' ',$q);
			foreach($words as $w){
				$wh[]=' ( c.name LIKE \'%'.$this->quote($w).'%\' OR c.description LIKE \'%'.$this->quote($w).'%\')';
			}
			//$wh[]=' ( c.name LIKE \'%'.$this->quote($q).'%\' OR c.description LIKE \'%'.$this->quote($q).'%\')';
		}else{
			return array();
		}
		if(count($wh)>0) $addwh=join(' AND ',$wh);
		return $this->getCourses($vendor,$limit,$offset,$userid,$onlyvisible,'',$addwh,$sortby,$datetype,$pricetype,$categoryid,$degreeid);
	}
	function getCoursesIDsByTag($tag){
		$tagid=$this->getTagId($tag);
		if($tagid==0) return array();
		$ids=array();
		$sql="SELECT * FROM course_tags   WHERE tagid=$tagid";
		$list=$this->getRows($sql);
		foreach($list as $l){
			$ids[]=$l['courseid'];
		}
		return $ids;
	}
	function getCourseByRemoteID($remoteid,$vendorid){
		$sql="SELECT * FROM courses WHERE importid='".$this->quote($remoteid)."' AND vendorid=$vendorid";
		$course=$this->getRow($sql);
		if(!$course) return null;
		return $this->getCourse($course['id']);
	}
	function getCourse($id,$userid=0,$local=true){
		$vtable='eduvendors';
		if(!$local) $vtable='eduvendors_temp';
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate,0 as priority,str_to_date( startdate, '%Y-%m-%d' ) AS coursedate ".
			" FROM courses as c LEFT JOIN ".$vtable." as v ON (c.vendorid=v.id) ";
		$sql.=' WHERE c.id='.$id;
		
		$list=$this->getRows($sql);
		$this->patchWithTags($list);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		if(count($list)==1) return $list[0];
		return null;
	}
	function patchWithTags(&$courses){
		for($i=0;$i<count($courses);$i++){
			$courses[$i]['tagslist']=$this->getTagsForCourse($courses[$i]['id']);
			$courses[$i]['tags']=join(', ',$courses[$i]['tagslist']);
		}
	}
	function patchWithVote(&$courses,$userid){
		$feedbackdb=F::getDBObject('feedback');
		return $feedbackdb->patchWithVote($courses,'c',$userid);
	}
	function patchCustom(&$courses,$userid){
		require_once(SITE_DIR.'include/class.languages.php');
		$landobj = new Languages();
		
		for($i=0;$i<count($courses);$i++){
			$courses[$i]['alreadystarted']='n';
			$courses[$i]['startdateformated']='';
			$courses[$i]['startdate']=preg_replace('![\\n\\r<>]!','',$courses[$i]['startdate']);
			$starttime=0;
			if($courses[$i]['startdate']=='undef') $courses[$i]['startdate']='';
			if(strtotime($courses[$i]['startdate'])>0){
				$courses[$i]['startdateformated']=date('Ymd',strtotime($courses[$i]['startdate']));
				$starttime=strtotime($courses[$i]['startdate']);
				$courses[$i]['startdateunix']=$starttime;
			}
			if($starttime>0)  $courses[$i]['alreadystarted']=($starttime<time())?'y':'n';
			$courses[$i]['categorieslist']=$this->getCategories($courses[$i]['id']);
			
			$courses[$i]['categories']=array();
			foreach($courses[$i]['categorieslist'] as $j) $courses[$i]['categories'][]=$j['id'];
			
			$courses[$i]['degrees']=array();//list of ids
			if($courses[$i]['enroltype']=='') $courses[$i]['enroltype']='alwaysopen';
			$courses[$i]['currentstatus']='open';
			$courses[$i]['durationdays']=0;
			if($courses[$i]['duration']!=''){
				$courses[$i]['durationdays']=$this->getDurationInDays($courses[$i]['duration']);
			}
			if($courses[$i]['enroltype']=='enrolbeforestart' && $starttime>0 && $courses[$i]['durationdays']>0){
				$endtime=$starttime+($courses[$i]['durationdays']*24*3600);
				$time=time();
				if($starttime<=$time && $time<$endtime){
					$courses[$i]['currentstatus']='inprogress';
				}elseif($time>=$endtime){
					$courses[$i]['currentstatus']='completed';
				}
			}
			if($courses[$i]['language']!=''){
				$courses[$i]['clangtitle']=$landobj->getLanguageTitle($courses[$i]['language']);
			}
		}
		return true;
	}
	function getDurationInDays($duration){
		$days=0;
		if(preg_match('!(\\d+)\\s+week!',$duration,$m)){
			$days=$m[1]*7;
		}elseif(preg_match('!^(\\d+)$!',$duration,$m)){
			$days=strval(intval($duration));
		}
		return $days;
	}
	function getCoursesForVendor($vendor,$onlyimported=false){
		$addwh='';
		if($onlyimported) $addwh=' AND ismanaged<>\'y\' ';
		$sql='SELECT * FROM courses WHERE vendorid='.$vendor.$addwh;	
		return $this->getRows($sql);
	}
	/*
	//this is dublication
	function getCourseByRemoteID($vendorid,$remoteid){
		$sql="SELECT  * FROM courses WHERE vendorid=$vendorid AND importid='".$this->quote($remoteid)."' ";
		return $this->getRow($sql);
	}
	*/
	function insertNewCourse($vendorid,$c,$visible='y',$ismanaged='n'){
		if(!isset($c['language'])) $c['language']='';
		$sql="INSERT INTO courses SET ".
			"vendorid=$vendorid,".
			"title='".$this->quote($c['title'])."',".
			"name='".$this->quote($c['name'])."',".
			"description='".$this->quote($c['description'])."',".
			"rewards='".$this->quote($c['rewards'])."',".
			"importid='".$this->quote($c['importid'])."',".
			"weburl='".$this->quote($c['weburl'])."',".
			"imageurl='".$this->quote($c['imageurl'])."',".
			"parentcourse='".$this->quote($c['parentcourse'])."',".
			"foundat=UTC_TIMESTAMP(),".
			"startdate='".$this->quote($c['startdate'])."',".
			"duration='".$this->quote($c['duration'])."',".
			"enroltype='".$this->quote($c['enroltype'])."',".
			"price='".$this->quote($c['price'])."',".
			"language='".$this->quote($c['language'])."',".
			"ismanaged='$ismanaged',".
			"visible='$visible'";
		$this->dbExecQuery($sql);
		$courseid=$this->getLastInsertID();
		
		if(!is_array($c['tags'])) $c['tags']=array();
		$this->updateTagsForCourse($courseid,$c['tags']);
		return $courseid;
	}
	function setCouseManaged($id){
		$sql="UPDATE courses SET ismanaged='y' WHERE id=$id";
		$this->dbExecQuery($sql);
	}
	function insertnewCourseDirect($vendorid,$name,$description,$weburl,$imageurl,$price,$duration,$startdate,$importid='',$language=''){
		return $this->insertNewCourse($vendorid,array(
				'name'=>$name,
				'title'=>$name,
				'description'=>$description,
				'rewards'=>'',
				'importid'=>$importid,
				'weburl'=>$weburl,
				'imageurl'=>$imageurl,
				'parentcourse'=>'',
				'startdate'=>$startdate,
				'duration'=>$duration,
				'language'=>$language,
				'price'=>$price,
				),'n'/*NOT VISIBLE*/,'y'/*IS managed*/);
	}
	function updateCourse($vendorid,$courseid,$name,$description,$weburl,$imageurl,$price,$duration,$startdate,$importid='',$language=''){
		$sql="UPDATE courses SET ".
			"title='".$this->quote($name)."',".
			"name='".$this->quote($name)."',".
			"description='".$this->quote($description)."',".
			"weburl='".$this->quote($weburl)."',".
			"imageurl='".$this->quote($imageurl)."',".
			"startdate='".$this->quote($startdate)."',".
			"duration='".$this->quote($duration)."',".
			"importid='".$this->quote($importid)."',".
			"language='".$this->quote($c['language'])."',".
			"price='".$this->quote($price)."' ".
		" WHERE vendorid=$vendorid AND id=$courseid";
		$this->dbExecQuery($sql);
	}
	function deleteCourse($vendorid,$courseid){
		//move this course to deleted courses table
		$sql="INSERT INTO courses_trash SELECT * FROM courses ".
		" WHERE vendorid=$vendorid AND id=$courseid";
		$this->dbExecQuery($sql);
		
		$sql="DELETE FROM courses ".
		" WHERE vendorid=$vendorid AND id=$courseid";
		$this->dbExecQuery($sql);
	}
	function closeCourse($vendorid,$courseid,$closestatus='y'){
		if($closestatus!='n'){
			//if this vendor has other course with such course importID then this should not be closing. this should be deleting
			$course=$this-> getCourse($courseid);		
			$sql="SELECT * FROM courses WHERE vendorid=$vendorid AND id<>$courseid AND importid='".$this->quote($course['importid'])."'";
			$r=$this->getRow($sql);

			if($r){
				$this->deleteCourse($vendorid,$courseid);
				return 1;
			}
		}
		
		$sql="UPDATE courses SET ".
			"closed='$closestatus' WHERE vendorid=$vendorid AND id=$courseid";
		$this->dbExecQuery($sql);
		
		return 2;
	}
	function checkAndUpdate($vendorid,$courseid,$c,$courserec=null){
		$updated=false;
		if(!$courserec) $courserec=$this->getRowById($courseid);
		if(!isset($c['language'])) $c['language']='';
		//check all fields
		if($courserec['visible']=='n'){
			$this->dbExecQuery("UPDATE courses SET visible='y' WHERE id = ".$courseid.' AND vendorid='.$vendorid);
			$updated=true;
		}
		//check tags
		if(!is_array($c['tags'])) $c['tags']=array();
		$this->updateTagsForCourse($courseid,$c['tags']);
		//check simple values
		$updatefields=array();
		foreach(array('name','title','description','rewards','weburl','imageurl','parentcourse','startdate','duration','enroltype','price','language') as $field){
			if(!isset($c[$field])) $c[$field]='';
			if($c[$field]!=$courserec[$field]){
				$updatefields[]=$field.'=\''.$this->quote($c[$field]).'\'';
			}
		}
		if(count($updatefields)>0){
			$sql="UPDATE courses SET ".join(',',$updatefields).' WHERE id = '.$courseid.' AND vendorid='.$vendorid;
			$this->dbExecQuery($sql);
			$updated=true;
		}
		if($courserec['visible']=='y') $this->activateCourse($vendorid,$courseid);
		return $updated;
	}
	function deactivateCourse($vendorid,$courseid){
		$this->dbExecQuery("UPDATE courses SET visible='n' WHERE id = ".$courseid.' AND vendorid='.$vendorid);
		return true;
	}
	function activateCourse($vendorid,$courseid){
		$this->dbExecQuery("UPDATE courses SET visible='y' WHERE id = ".$courseid.' AND vendorid='.$vendorid);
		return true;
	}
	function getPopularCourses($vendorid=0,$limit=10,$userid=0){
		$sql="SELECT c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate FROM courses 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->patchWithTags($list);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function updateTagsForCourse($cid,$tags){
		$this->dbExecQuery("DELETE FROM course_tags WHERE courseid=$cid");
		
		$correctlist=array();//to split tags with commas
		foreach($tags as $tag){
			if(preg_match('!,!',$tag)){
				$at=preg_split('!,\\s*?!',$tag);
				$correctlist=array_merge($correctlist,$at);
			}else{
				$correctlist[]=$tag;
			}
		}
		foreach($correctlist as $tag){
			$tag=SITEHelper::normaliseTag($tag);
			if(trim($tag)=='') continue;
			
			// check tag is not in block list
			$sql="SELECT  * FROM skills_tags_block WHERE tag='".$this->quote($tag)."' ";
			$tmp_tag = $this->getRow($sql);
			
			if ($tmp_tag) {
				continue;
			}
			
			$tagid = $this->getTagId($tag);
			
			if ($tagid > 0) {
				$this->dbExecQuery("INSERT INTO course_tags SET courseid=$cid,tagid=$tagid");
			}
		}
		return true;
	}
	function addTagForCourse($courseid,$tag){
		$tag=SITEHelper::normaliseTag($tag);
		if(trim($tag)=='') return true;
		$tagid=$this->getTagId($tag);
		
		if ($tagid == 0) {
			return false;
		}
		
		$this->dbExecQuery("DELETE FROM course_tags WHERE courseid=$courseid AND tagid=$tagid");
		
		$sql="SELECT  * FROM skills_tags_block WHERE tag='".$this->quote($tag)."' ";
		
		$this->dbExecQuery("INSERT INTO course_tags SET courseid=$courseid,tagid=".$tagid."");
		
		return true;
	}
	function getTagId($tag){
		$sql="SELECT  * FROM skills_tags_block WHERE tag='".$this->quote($tag)."' ";
		$tmp_tag = $this->getRow($sql);
		
		if ($tmp_tag) {
			return 0;// can not be added
		}
		// check aliaces first
		$sql="SELECT  * FROM skills_tags_aliaces WHERE tag='".$this->quote($tag)."' ";
		$r = $this->getRow($sql);
		
		if ($r) {
			return $r['aliacetagid'];
		}
		
		$sql="SELECT  * FROM skills_tags WHERE tag='".$this->quote($tag)."' ";
		$r=$this->getRow($sql);
		if(!$r){
			$this->dbExecQuery("INSERT INTO skills_tags SET tag='".$this->quote($tag)."'");
			return $this->getLastInsertID();
		}
		return $r['tagid'];
	}
	function getActiveVendors(){
		$sql="SELECT * FROM eduvendors WHERE isactive='y' ORDER BY name";
		return $this->getRows($sql);
	}
	function getTopTags($limit,$provider=0,$skiptags=array()){
		if($limit<1 || $limit>1000) $limit=100;
		if($provider>0){
			$sql="SELECT ct.tagid,st.tag,count(ct.courseid) as count FROM course_tags as ct LEFT JOIN skills_tags as st ON (ct.tagid=st.tagid) ".
			"LEFT JOIN courses as c ON (ct.courseid=c.id) WHERE c.vendorid=$provider ";
		}else{
			$sql="SELECT ct.tagid,st.tag,count(ct.courseid) as count FROM course_tags as ct LEFT JOIN skills_tags as st ON (ct.tagid=st.tagid) ";
			if(count($skiptags)>0){
				$sql.=' WHERE st.tag NOT IN (\''.join('\',\'',$skiptags).'\') ';
			}
		}
		$sql.="GROUP BY ct.tagid ORDER BY count DESC LIMIT $limit";
		$tags=$this->getRows($sql);
		usort($tags, array("sortFunctions", "sortTags"));
		return $tags;
	}
	function getTagsForCourses($ids){
		if($ids=='') return array();
		$sql="SELECT ct.tagid,st.tag,count(ct.courseid) as count FROM course_tags as ct LEFT JOIN skills_tags as st ON (ct.tagid=st.tagid) ".
			" WHERE ct.courseid IN ($ids) GROUP BY ct.tagid ORDER BY count DESC ";
		$tags=$this->getRows($sql);
		usort($tags, array("sortFunctions", "sortTags"));
		return $tags;
	}
	function getTagsForCourse($courseid){
		$sql="SELECT st.tag FROM course_tags as ct LEFT JOIN skills_tags as st ON (ct.tagid=st.tagid) WHERE ct.courseid=$courseid ORDER BY st.tag";
		$tagslist=array();
		$tags=$this->getRows($sql);
		foreach($tags as $t){
			$tagslist[]=$t['tag'];
		}
		return $tagslist;
	}
	function getReviews($id,$userid=0,$allreviews=false){
		$addwh='';
		if(!$allreviews){
			if($userid>0){
				//get also unapproved of this user
				$addwh=' AND (approved=\'y\' OR userid='.$userid.')';
			}else{
				$addwh=' AND approved=\'y\'';
			}
		}
		$sql="SELECT r.*,u.name as usernameorig FROM course_reviews as r LEFT JOIN users as u ON (r.userid=u.id)  WHERE r.courseid=$id $addwh ORDER BY whenposted DESC";
		$l=$this->getRows($sql);
		for($i=0;$i<count($l);$i++){
			if($l[$i]['username']=='') $l[$i]['username']=$l[$i]['usernameorig'];
		}
		return $l;
	}
	function getAlternatives($id,$userid=0,$allreviews=false){
		$addwh='';
		if(!$allreviews){
			if($userid>0){
				//get also unapproved of this user
				$addwh=' AND (approved=\'y\' OR userid='.$userid.')';
			}else{
				$addwh=' AND approved=\'y\'';
			}
		}
		
		//load alternatives using subjects connections
		$sql="SELECT c.*,'' as username,1 as userid,'y' as approved, v.name as vname, v.website as vwebsite,date(c.foundat) as lastupdate,".
			" cm.itemid as altid,v.altpriority FROM courses as c ".
			" LEFT JOIN courses_subjects_conn as cm ON (c.id=cm.itemid) ".
			" LEFT JOIN eduvendors as v ON (c.vendorid=v.id) ".
			" WHERE cm.itemtype='c' AND c.id<>$id and cm.covering='exact' ".
			" AND cm.subjectid IN (SELECT subjectid FROM courses_subjects_conn WHERE itemid=$id AND itemtype='c' AND covering='exact') ".
			" ORDER BY v.altpriority DESC, c.name";
		$list=$this->getRows($sql);
		
		$existent=array();
		foreach($list as $c){
			$existent[]=$c['id'];
		}
		if(count($existent)>0){
			$addwh.=' AND c.id NOT IN ('.join(',',$existent).')';
		}

		$sql_t="";
			
		$sql="(SELECT a.*,u.name as username, c.*,IF(a.type<>'l',vt.name,v.name) as vname, IF(a.type<>'l',vt.website,v.website) as vwebsite,date(foundat) as lastupdate,".
			"c.id as altid,v.altpriority FROM course_alternatives as a LEFT JOIN users as u ON (a.userid=u.id)".
			" LEFT JOIN courses as c ON (a.alternativeid=c.id) LEFT JOIN eduvendors as v ON (c.vendorid=v.id) LEFT JOIN eduvendors_temp as vt ON (c.vendorid=vt.id)".
			" WHERE a.courseid=$id $addwh) ".
		" UNION ".
			"(SELECT a.*,u.name as username, c.*,IF(a.type<>'l',vt.name,v.name) as vname, IF(a.type<>'l',vt.website,v.website) as vwebsite,date(foundat) as lastupdate,".
			"c.id as altid,v.altpriority  FROM course_alternatives as a LEFT JOIN users as u ON (a.userid=u.id)".
			" LEFT JOIN courses as c ON (a.courseid=c.id) LEFT JOIN eduvendors as v ON (c.vendorid=v.id) LEFT JOIN eduvendors_temp as vt ON (c.vendorid=vt.id)".
			" WHERE a.alternativeid=$id $addwh )".
			" ORDER BY name";
		$list=array_merge($list,$this->getRows($sql));
		$this->patchWithTags($list);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function getPrerequisites($id,$userid=0,$alls=false){
		$addwh='';
		if(!$alls){
			if($userid>0){
				//get also unapproved of this user
				$addwh=' AND (approved=\'y\' OR userid='.$userid.')';
			}else{
				$addwh=' AND approved=\'y\'';
			}
		}

			
		$sql="SELECT a.*,u.name as username, c.*,IF(a.type<>'l',vt.name,v.name) as vname, IF(a.type<>'l',vt.website,v.website) as vwebsite,date(foundat) as lastupdate,".
			"c.id as altid FROM course_prerequisites as a LEFT JOIN users as u ON (a.userid=u.id)".
			" LEFT JOIN courses as c ON (a.prerequisiteid=c.id) LEFT JOIN eduvendors as v ON (c.vendorid=v.id) LEFT JOIN eduvendors_temp as vt ON (c.vendorid=vt.id)".
			" WHERE a.courseid=$id $addwh ORDER BY name";
		$list=$this->getRows($sql);
		$this->patchWithTags($list);
		$this->patchWithVote($list,$userid);
		$this->patchCustom($list,$userid);
		return $list;
	}
	function getDegree($id){
		$sql="SELECT * FROM degrees WHERE id=$id";
		return $this->getRow($sql);
	}
	function getDegrees($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.*,IF(a.userid>0,u.name,'') as username,a.degreeid as id,d.groupid,d.title ".
			" FROM course_degrees as a LEFT JOIN degrees as d ON (a.degreeid=d.id) LEFT JOIN degrees_groups as g ON (d.groupid=g.id) LEFT JOIN users as u ON (a.userid=u.id)".
			" WHERE a.courseid=$id $addwh ORDER BY d.title";
		$list=$this->getRows($sql);
		return $list;
	}
	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 course_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.courseid=$id $addwh ORDER BY g.title";
		$list=$this->getRows($sql);
		return $list;
	}
	function suggestTags($text){
		$sql="SELECT  * FROM skills_tags WHERE tag LIKE '%".$this->quote($text)."%'";	
		$list=$this->getRows($sql);
		$tags=array();
		foreach($list as $t){
			$tags[]=$t['tag'];
		}
		return $tags;
	}
	function insertReview($courseid,$review,$title,$userid,$ip,$approved='n',$username){
		$sql="INSERT INTO course_reviews SET ".
			"courseid=$courseid,".
			"review='".$this->quote($review)."',".
			"title='".$this->quote($title)."',".
			"username='".$this->quote($username)."',".
			"userid=$userid,".
			"approved='n',".
			"ipaddress='".$this->quote($ip)."',".
			"whenposted=UTC_TIMESTAMP()";
		$this->dbExecQuery($sql);
		
		$id=$this->getLastInsertID();
		
		if($approved=='y'){
			$this->approveReview($id);
		}
		
		return $id;
	}
	function approveReview($reviewid){
		$sql="UPDATE course_reviews SET approved='y' WHERE id=$reviewid";
		$this->dbExecQuery($sql);
		
		$sql="SELECT * FROM course_reviews WHERE id=$reviewid";
		$r=$this->getRow($sql);
		
		if(isset($r['courseid'])){
			$sql="UPDATE courses SET reviews=reviews+1 WHERE id='".$r['courseid']."'";
			$this->dbExecQuery($sql);
		}
		
		return true;
	}
	function disableReview($reviewid){
		$sql="UPDATE course_reviews SET approved='n' WHERE id=$reviewid";
		$this->dbExecQuery($sql);
		
		$sql="SELECT * FROM course_reviews WHERE id=$reviewid";
		$r=$this->getRow($sql);
		
		if(isset($r['courseid'])){
			$sql="UPDATE courses SET reviews=reviews-1 WHERE id='".$r['courseid']."'";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function updateReview($reviewid,$review,$title,$ip,$username){
		$sql="UPDATE course_reviews SET ".
			"review='".$this->quote($review)."',".
			"title='".$this->quote($title)."',".
			"username='".$this->quote($username)."',".
			"ipaddress='".$this->quote($ip)."'".
		"WHERE id=$reviewid";
		$this->dbExecQuery($sql);
		return $reviewid;
	}
	function deleteReview($reviewid){
		$sql="SELECT * FROM course_reviews WHERE id=$reviewid";
		$r=$this->getRow($sql);
		
		if(isset($r['courseid']) && $r['approved']=='y'){
			$sql="UPDATE courses SET reviews=reviews-1 WHERE id='".$r['courseid']."'";
			$this->dbExecQuery($sql);
		}
		
		$sql="DELETE FROM course_reviews WHERE id=$reviewid ";
		$this->dbExecQuery($sql);
		
		return true;
	}
	function getReview($reviewid){
		$sql="SELECT r.*,u.name as usernameorig FROM course_reviews as r LEFT JOIN users as u ON (r.userid=u.id) WHERE r.id=$reviewid";
		$r=$this->getRow($sql);
		if($r['username']=='') $r['username']=$r['usernameorig'];
		return $r;
	}
	function getVendorReviewsCount($vendorid){
		$sql="SELECT count(re.id) as c FROM course_reviews as re LEFT JOIN courses as c ON (re.courseid=c.id)  WHERE c.vendorid=$vendorid";
		$c=$this->getRow($sql);
		return $c['c'];
	}
	function getVendorReviews($vendorid,$limit,$offset){
		if($limit<1 || $limit>100) $limit=20;
		$sql="SELECT re.*,u.name as username,u.email as uemail,c.name as cname FROM course_reviews as re LEFT JOIN courses as c ON (re.courseid=c.id) LEFT JOIN users as u ON (re.userid=u.id)";
		$sql.=" WHERE c.vendorid=$vendorid ORDER BY re.whenposted DESC";
		$sql.=" LIMIT $offset,$limit";
		return $this->getRows($sql);
	}
	function getAlternative($courseid,$alternativeid){
		$sql="SELECT a.*,u.name as username, c1.name as coursename, c2.name as altname FROM course_alternatives as a LEFT JOIN users as u ON (a.userid=u.id) ";
		$sql.=" LEFT JOIN courses as c1 ON (a.courseid=c1.id) LEFT JOIN courses as c2 ON (a.alternativeid=c2.id)";
		$sql.=" WHERE a.courseid=$courseid AND alternativeid=$alternativeid";
		return $this->getRow($sql);
	}
	function addAlternative($courseid,$alternativeid,$userid,$type='l',$approved='n',$ip=''){
		$sql="INSERT INTO course_alternatives SET ".
			"courseid=$courseid,".
			"alternativeid=".$alternativeid.",".
			"type='".$type."',".
			"userid=$userid,".
			"approved='n',".
			"ipaddress='".$this->quote($ip)."',".
			"whenposted=UTC_TIMESTAMP()";
		$this->dbExecQuery($sql);
		
		if($approved=='y'){
			$this->approveAlternative($courseid,$alternativeid);
		}
		return true;
	}
	function deleteAlternative($courseid,$alternativeid){
		
		$alt=$this->getAlternative($courseid,$alternativeid);
		if($alt['type']=='r'){
			//delete also connected course and probably the vendor
			$alt=$this->getAlternative($courseid,$alternativeid);
			if($alt){
				$altcourse=$this->getRowById($alternativeid);
				$this->deleteCourse($altcourse['vendorid'],$alternativeid);
				$vendn=F::getDBObject('eduvendors');
				$vendn->deleteTempVendorIfNoCourses($altcourse['vendorid']);
			}
		}
		$sql="DELETE FROM course_alternatives WHERE courseid=$courseid AND alternativeid=$alternativeid ";
		$this->dbExecQuery($sql);
		return true;
	}
	function changeAlternativeStatus($courseid,$alternativeid,$status){
		$alt=$this->getAlternative($courseid,$alternativeid);
		$sql="UPDATE course_alternatives SET approved='$status' WHERE courseid=$courseid AND alternativeid=$alternativeid ";
		$this->dbExecQuery($sql);
		if($alt['type']=='r'){
			//change the status for alt course also
			$sql="UPDATE courses SET visible='$status' WHERE id=$alternativeid ";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function getPrerequisite($courseid,$prerequisiteid){
		$sql="SELECT a.*,u.name as username, c1.name as coursename, c2.name as prename FROM course_prerequisites as a LEFT JOIN users as u ON (a.userid=u.id) ";
		$sql.=" LEFT JOIN courses as c1 ON (a.courseid=c1.id) LEFT JOIN courses as c2 ON (a.prerequisiteid=c2.id)";
		$sql.=" WHERE a.courseid=$courseid AND prerequisiteid=$prerequisiteid";
		return $this->getRow($sql);
	}
	function addPrerequisite($courseid,$prerequisiteid,$userid,$type='l',$approved='n',$ip=''){
		$sql="INSERT INTO course_prerequisites SET ".
			"courseid=$courseid,".
			"prerequisiteid=".$prerequisiteid.",".
			"type='".$type."',".
			"userid=$userid,".
			"approved='n',".
			"ipaddress='".$this->quote($ip)."',".
			"whenposted=UTC_TIMESTAMP()";
		$this->dbExecQuery($sql);
		
		if($approved=='y'){
			$this->approvePrerequisite($courseid,$prerequisiteid);
		}
		return true;
	}
	function deletePrerequisite($courseid,$prerequisiteid){
		
		$alt=$this->getPrerequisite($courseid,$prerequisiteid);
		if($alt['type']=='r'){
			//delete also connected course and probably the vendor
			$precourse=$this->getRowById($prerequisiteid);
			$this->deleteCourse($precourse['vendorid'],$prerequisiteid);
			$vendn=F::getDBObject('eduvendors');
			$vendn->deleteTempVendorIfNoCourses($precourse['vendorid']);
		}
		$sql="DELETE FROM course_prerequisites WHERE courseid=$courseid AND prerequisiteid=$prerequisiteid ";
		$this->dbExecQuery($sql);
		return true;
	}
	function changePrerequisiteStatus($courseid,$prerequisiteid,$status){
		$alt=$this->getPrerequisite($courseid,$prerequisiteid);
		$sql="UPDATE course_prerequisites SET approved='$status' WHERE courseid=$courseid AND prerequisiteid=$prerequisiteid ";
		$this->dbExecQuery($sql);
		if($alt['type']=='r'){
			//change the status for alt course also
			$sql="UPDATE courses SET visible='$status' WHERE id=$prerequisiteid ";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function getAllCategories(){
		$sql="SELECT * FROM degrees_groups ORDER BY title";
		return $this->getRows($sql);
	}
	function getAllDegrees($catid=0){
		$wh='';
		if($catid>0) $wh=' WHERE d.groupid='.$catid.' ';
		$sql="SELECT d.*,g.title ascategory FROM degrees as d LEFT JOIN degrees_groups as g ON (d.groupid=g.id) $wh ORDER BY d.title";
		return $this->getRows($sql);
	}
	function getDegreesSpecialities($degreeid){
		$sql="SELECT * FROM degrees_specialities WHERE degreeid=$degreeid ORDER BY title";
		return $this->getRows($sql);
	}
	function setCategories($courseid,$categories,$userid,$ip=''){
		//remove all categories suggested by this user and not yet approved
		$sql="DELETE FROM course_categories WHERE userid=$userid AND courseid=$courseid";
		$this->dbExecQuery($sql);
		
		//insert new categories
		foreach($categories as $category){
			//check if course already has this category
			$ext=$this->getRow("SELECT * FROM course_categories WHERE courseid=$courseid AND catid=".$category."");
			if($ext) continue;
			
			$sql="INSERT INTO course_categories SET ".
				"courseid=$courseid,".
				"catid=".$category.",".
				"userid=$userid,".
				"ipaddress='".$this->quote($ip)."',".
				"whenposted=UTC_TIMESTAMP()";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function setCategory($courseid,$categoryid){
		$ext=$this->getRow("SELECT * FROM course_categories WHERE courseid=$courseid AND catid=".$categoryid."");
		if($ext) return true;
			
		$sql="INSERT INTO course_categories SET courseid=$courseid, catid=".$categoryid.",userid=0,".
			"ipaddress='1.1.1.1',whenposted=UTC_TIMESTAMP()";
		$this->dbExecQuery($sql);

		return true;
	}
	function approveCategory($courseid,$categoryid){
		$sql="UPDATE course_categories SET userid=0 WHERE courseid=$courseid and catid=$categoryid";
		$this->dbExecQuery($sql);
		return true;
	}
	function deleteCategory($courseid,$categoryid){
		$sql="DELETE FROM course_categories WHERE courseid=$courseid and catid=$categoryid";
		$this->dbExecQuery($sql);
		return true;
	}
	function setDegrees($courseid,$degrees,$userid,$ip=''){
		//remove all categories suggested by this user and not yet approved
		$sql="DELETE FROM course_degrees WHERE userid=$userid AND courseid=$courseid";
		$this->dbExecQuery($sql);

		//insert new categories
		foreach($degrees as $degree){
			if(trim($degree)=='') continue;
			//check if course already has this category
			$ext=$this->getRow("SELECT * FROM course_degrees WHERE courseid=$courseid AND degreeid=$degree");
			if($ext && $ext['userid']==0){ continue;}
			
			$sql="INSERT INTO course_degrees SET ".
				"courseid=$courseid,".
				"degreeid=".$degree.",".
				"userid=$userid,".
				"ipaddress='".$this->quote($ip)."',".
				"whenposted=UTC_TIMESTAMP()";
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function approveDegree($courseid,$degreeid){
		$sql="UPDATE course_degrees SET userid=0 WHERE courseid=$courseid and degreeid=$degreeid";
		$this->dbExecQuery($sql);
		$ext=$this->getRow("SELECT count(*) as c FROM course_degrees WHERE courseid=$courseid AND degreeid=$degreeid");
		if($ext['c']>1){
			$sql="DELETE FROM course_degrees WHERE courseid=$courseid and degreeid=$degreeid LIMIT ".($ext['c']-1);
			$this->dbExecQuery($sql);
		}
		return true;
	}
	function deleteDegree($courseid,$degreeid){
		$sql="DELETE FROM course_degrees WHERE courseid=$courseid and degreeid=$degreeid";
		$this->dbExecQuery($sql);
		return true;
	}
	function getRandomCourse($vendorid,$fromlist=true,$onlyvisible=true){
		$sql="SELECT * FROM courses 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 courses 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 getAllTags(){
		$sql="SELECT * FROM skills_tags ORDER BY tag";
		return $this->getRows($sql);
	}
	function assignTagToCategory($tagid,$category){
		$sql="UPDATE skills_tags SET category=$category WHERE tagid=".getCorrectInteger($tagid);
		$this->dbExecQuery($sql);
	}
	function unAssignTagToCategory($tagid){
		$sql="UPDATE skills_tags SET category=0 WHERE tagid=".getCorrectInteger($tagid);
		$this->dbExecQuery($sql);
	}
	function getCoursesByTagsCategory($catid){
		$sql="SELECT c.* FROM courses as c WHERE c.id IN (SELECT ct.courseid FROM course_tags as ct LEFT JOIN skills_tags as t ON (ct.tagid=t.tagid) WHERE t.category=$catid)";
		return $this->getRows($sql);
	}
	function getAllCategoriesWithCounts(){
		$sql="SELECT c.*,count(cc.courseid) as countcourses FROM degrees_groups as c JOIN course_categories as cc ON (c.id=cc.catid) GROUP BY c.id ORDER BY c.title";
		return $this->getRows($sql);
	}
	function getCourseIDByImportID($importid,$vendorid){
		$sql="SELECT * FROM courses WHERE vendorid=".$vendorid." AND importid='".$this->quote($importid)."'";
		$c=$this->getRow($sql);
		if(isset($c['id'])) return $c['id'];
		return 0;
	}
	function getCoursePaths($id){
		$pathsdb=F::getDBObject('paths');
		return $pathsdb->getPathsWhereItemIncluded('c',$id);
	}
	function getCourseStudentsInPassport($id){
		$pathsdb=F::getDBObject('paths');
		return $pathsdb->getStudentsWhereItemInPassport('c',$id);
	}
	function getCourseStudentsInPath($id){
		$pathsdb=F::getDBObject('paths');
		return $pathsdb->getStudentsWhereItemInPath('c',$id);
	}
	function getCourseStudents($id){
		$pathsdb=F::getDBObject('paths');
		return $pathsdb->getStudentsWhereItemIncluded('c',$id);
	}
	
	function mergeCourses($vendorid,$oldremid,$newremid){
		$oldcourse=$this->getCourseByRemoteID($oldremid,$vendorid);
		$newcourse=$this->getCourseByRemoteID($newremid,$vendorid);
		
		if(!$oldcourse || !$newcourse) return false;
		$oldid=$oldcourse['id'];
		$newid=$newcourse['id'];
		//update all mappings from old course to new course
		$sqls=array(
			"UPDATE comments SET objid=$newid WHERE objid=$oldid and objtype='c'",
			"UPDATE course_alternatives SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE course_alternatives SET alternativeid=$newid WHERE alternativeid=$oldid",
			"UPDATE course_categories SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE course_degrees SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE course_prerequisites SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE course_prerequisites SET prerequisiteid=$newid WHERE prerequisiteid=$oldid",
			"UPDATE course_tags SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE exam_connections SET courseid=$newid WHERE courseid=$oldid",
			"UPDATE pathitems SET itemid=$newid WHERE itemid=$oldid and itemtype='c'",
			"UPDATE paths SET itemid=$newid WHERE itemid=$oldid and itemtype='c'",
			"UPDATE pathtemplate_items SET itemid=$newid WHERE itemid=$oldid and itemtype='c'",
			"UPDATE reviews SET objid=$newid WHERE objid=$oldid and objtype='c'",
			"UPDATE votes SET objid=$newid WHERE objid=$oldid and objtype='c'",
			);
		foreach($sqls as $sql){
			$this->dbExecQuery($sql);
		}
		//recalculate comments,reviews,votes
		$feedbackdb=F::getDBObject('feedback');
		$feedbackdb->recalculateRating('c',$newid);
		$feedbackdb->recalculateComments('c',$newid);
		$feedbackdb->recalculateReviews('c',$newid);
		
		//delete old course
		$this->deleteCourse($vendorid,$oldid);
		return true;
	}
	function getCoursesAddedRecently($fromdate,$todate,$excludevendors=array(),$excludecourses=array()){
		$addwh=' c.foundat >\''.$fromdate.'\' AND c.foundat<=\''.$todate.'\'';
		if(count($excludecourse)>0)
			$addwh.=' AND c.id NOT IN ('.join(',',$excludecourse).')';
		if(count($excludevendors)>0)
			$addwh.=' AND c.vendorid NOT IN ('.join(',',$excludevendors).')';
		return $this->getCourses('',100,0,0,true,'',$addwh);
	}
	function groupCoursesByVendor($list){
		$courses=array();
		foreach($list as $i){
			if(!isset($courses[$i['vendorid']]))
				$courses[$i['vendorid']]=array($i['vname'],array());
			$courses[$i['vendorid']][1][]=$i;
		}
		return $courses;
	}
	function getCoursesAddedRecentlyGrouped($fromdate,$todate,$excludevendors=array(),$excludecourses=array()){
		$list=$this->getCoursesAddedRecently($fromdate,$todate,$excludevendors,$excludecourses);
		return $this->groupCoursesByVendor($list);
	}
	function getCoursesStartedGrouped($fromdate,$todate,$excludecvendors=array()){
		$addwh=' str_to_date( startdate, \'%Y-%m-%d\' ) <= \''.$todate.'\' AND str_to_date( startdate, \'%Y-%m-%d\' )>\''.$fromdate.'\' ';
		if(count($excludevendors)>0)
			$addwh.=' AND c.vendorid NOT IN ('.join(',',$excludevendors).')';
		$list=$this->getCourses('',100,0,0,true,'',$addwh);
		return $this->groupCoursesByVendor($list);
	}
	function getCoursesUpcomingGrouped($fromdate,$todate,$excludecvendors=array()){
		$addwh=' str_to_date( startdate, \'%Y-%m-%d\' ) > \''.$todate.'\' '.
			' AND str_to_date( startdate, \'%Y-%m-%d\' )<DATE_ADD(\''.$todate.'\', INTERVAL 7 DAY) ';
		if(count($excludevendors)>0)
			$addwh.=' AND c.vendorid NOT IN ('.join(',',$excludevendors).')';
		$list=$this->getCourses('',100,0,0,true,'',$addwh);
		return $this->groupCoursesByVendor($list);
	}
	function fixTagsTable(){
		$alltags=$this->getRows("SELECT * FROM skills_tags");
		$i=0;
		foreach($alltags as $t){
			$tag=SITEHelper::normaliseTag($t['tag']);
			if($tag!=$t['tag']){
				if($tag==''){
					$this->dbExecQuery("DELETE FROM skills_tags WHERE tagid=".$t['tagid']);
					$this->dbExecQuery("DELETE FROM course_tags WHERE tagid=".$t['tagid']);
					$this->dbExecQuery("DELETE FROM courses_subjects_tags WHERE tagid=".$t['tagid']);
				}else{	
					$this->dbExecQuery("UPDATE skills_tags SET tag='".$this->quote($tag)."' WHERE tagid=".$t['tagid']);
				}
				$i++;
			}
		}
		$brokenconn=$this->getRows("SELECT ct.* FROM courses_subjects_tags as ct LEFT JOIN skills_tags as t ON (ct.tagid=t.tagid) WHERE t.tag IS NULL");
		foreach($brokenconn as $c){
			$i++;
			$this->dbExecQuery("DELETE FROM courses_subjects_tags WHERE tagid=".$c['tagid']);
		}
		$brokenconn=$this->getRows("SELECT ct.* FROM course_tags as ct LEFT JOIN skills_tags as t ON (ct.tagid=t.tagid) WHERE t.tag IS NULL");
		foreach($brokenconn as $c){
			$i++;
			$this->dbExecQuery("DELETE FROM course_tags WHERE tagid=".$c['tagid']);
		}
		
		//duplicates
		$alltags=$this->getRows("SELECT tag, count(tagid) as c FROM skills_tags GROUP BY tag ORDER BY c DESC");
		foreach($alltags as $t){
			if($t['c']<2) break;
			//echo $t['tag'].' '.$t['c'].'<br>';
			$rtags=$this->getRows("SELECT * FROM skills_tags WHERE tag='".$this->quote($t['tag'])."'");
			$maintag=array_shift($rtags);
			foreach($rtags as $tag){
				$i++;
				//reconnect to main tag
				$this->dbExecQuery("UPDATE course_tags SET tagid=".$maintag['tagid']." WHERE tagid=".$tag['tagid']);
				$this->dbExecQuery("UPDATE courses_subjects_tags SET tagid=".$maintag['tagid']." WHERE tagid=".$tag['tagid']);
				$this->dbExecQuery("DELETE FROM skills_tags WHERE tagid=".$tag['tagid']);
			}
		}
		
		// aliaces
		$alltags = $this->getRows("SELECT a.*,t.tagid FROM skills_tags_aliaces as a LEFT JOIN skills_tags as t ON (a.tag=t.tag)WHERE t.tag IS NOT NULL");
		
		foreach($alltags as $t){
			// replace all connection with aliace and delete from tags table
			$this->dbExecQuery("UPDATE course_tags SET tagid=".$t['aliacetagid']." WHERE tagid=".$t['tagid']);
			$this->dbExecQuery("UPDATE courses_subjects_tags SET tagid=".$t['aliacetagid']." WHERE tagid=".$t['tagid']);
			$this->dbExecQuery("DELETE FROM skills_tags WHERE tagid=".$t['tagid']);
		}
		
		return $i;
	}
	function getCourseKeywords($id){
		$sql="SELECT k.keyphrase FROM keyphrases as k LEFT JOIN keyphrases_conn as kc ON (k.id=kc.keyphraseid) WHERE kc.itemid=$id AND  kc.conntype='c' ORDER BY k.keyphrase";
		$keywords=array();
		$list=$this->getRows($sql);
		foreach($list as $k){
			$keywords[]=$k['keyphrase'];
		}
		return $keywords;
	}
	function getGroupCourses($groupid,$limit=100,$userid=0, $addwh = '', $justlist = false, $nopatch = false){
		$sql = "SELECT a.*,c.*,v.name as vname, v.website as vwebsite,date(foundat) as lastupdate".
			" FROM groups_courses as a LEFT JOIN courses as c ON (a.courseid=c.id) ".
			" LEFT JOIN eduvendors as v ON (c.vendorid=v.id) ".
			" WHERE a.groupid=$groupid ";
		if ($addwh!='') {
			$sql .= $addwh;
		}
		$sql .= "ORDER BY name LIMIT $limit";
		
		$list = $this->getRows($sql);
		
		if (!$nopatch) {
			$this->patchWithTags($list);
			$this->patchCustom($list,$userid);
		}
		
		if ($justlist) {
			return $list;
		}
		
		$sql = "SELECT count(*) FROM groups_courses WHERE groupid=$groupid";
		$total = $this->getValue($sql);
		
		return array('list'=>$list,'total'=>$total);
	}
	function getGroupCoursesAdded($groupid,$sincedate, $limit=100,$userid=0) {
		return $this->getGroupCourses($groupid,$limit,$userid, ' AND a.whenadded>\''.$sincedate.'\' ',true,true);
	}
}

?><br />
<b>Fatal error</b>:  Class 'CoursesDatabaseTable' 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 'CoursesDatabaseTable' 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>