Location: PHPKode > projects > StreamOnTheFly > node/code/classes/sotf_AdvSearch.class.php
<?php
/*
 * This class represents a query for the advanced search
 *
 * @author Mate Pataki MTA SZTAKI DSD
 *
 */
class sotf_AdvSearch
{
	var $SQLquery, $sort1, $sort2, $allid;

  /** Used by deserialize only */
  var $SQLFields = array('station','production_date','language','person','title','seriestitle','topic','length','rating','genre_id','keywords','abstract','seriesdescription','entry_date','expiry_date','modify_date','broadcast_date','spatial_coverage','temporal_coverage');
	
	function sotf_AdvSearch($array = "")			//constuctor with a starting query
	{
		if ($array != "") $this->SQLquery = $array;
	}

	function GetQuery()					//gives back the current query
	{
		return $this->SQLquery;
	}

	function GetSort1()					//gives back the current sort order 1
	{
		$pos = strpos($this->sort1, " DESC");
		if (!$pos) return $this->sort1;
		return substr($this->sort1, 0, $pos);
	}

	function GetSort2()					//gives back the current sort order 2
	{
		$pos = strpos($this->sort2, " DESC");
		if (!$pos) return $this->sort2;
		return substr($this->sort2, 0, $pos);
	}

	function getDir1()					//gives back if the current sort order direction 1 is DESC or not
	{
		$pos = strpos($this->sort1, " DESC");
		if (!$pos) return false;
		return "DESC";
	}

	function getDir2()					//gives back if the current sort order direction 2 is DESC or not
	{
		$pos = strpos($this->sort2, " DESC");
		if (!$pos) return false;
		return "DESC";
	}

	function DeleteQuery()					//start a new query
	{
		$this->SQLquery = NULL;
		return $this->SQLquery;
	}

	function Serialize()					//make a string from array
	{
		$serial= $this->sort1."|B".$this->sort2;
		$max = count($this->SQLquery);
		for($i=0; $i < $max; $i++)
		{			//Need | char as a sepecial char so replace it
			$serial = $serial."|A".strtr($this->SQLquery[$i][0], "|", "I")."|B".strtr($this->SQLquery[$i][1], "|", "I")."|B".strtr($this->SQLquery[$i][2], "|", "I")."|B".strtr($this->SQLquery[$i][3], "|", "I")."|B".strtr($this->SQLquery[$i][4], "|", "I");
		}
		return $serial;
	}

	function Deserialize($serial)					//make an array from string
	{
		$this->SQLquery = array();
		$terms = explode("|A", $serial);
		$max = count($terms);
		$term = explode("|B", $terms[0]);		//sort order is the first array
		$pos1 = strpos($term[0], " DESC");			//SORT 1
		$pos2 = strpos($term[1], " DESC");			//SORT 2
		if ($pos1) $term[0] = substr($term[0], 0, $pos1);		//remove DESC
		if ($pos2) $term[1] = substr($term[1], 0, $pos2);		//remove DESC
		if (array_key_exists($term[0], $this->getOrderFields()) AND array_key_exists($term[1], $this->getOrderFields())) $this->SetSortOrder($term[0], $term[1]);	//SetSortOrder
		else $this->SetSortOrder();		//set default
		$this->setDir($pos1, $pos2);		//set direction after field is set

		if ($max < 1) return $this->SQLquery;		//at least sort order nedded
		if (count($term) != 2) return $this->SQLquery;	//two sort orders needed

		for($i=1; $i < $max; $i++)
		{			//TODO: | char as a sepecial char so replace it
			$term = explode("|B", $terms[$i]);
			if (count($term == 5) AND in_array($term[1], $this->SQLFields))			//to be sure :-)
				$this->SQLquery[]=$term;
		}
		return $this->SQLquery;
	}

	function getEQSign($sign, $value)
	{
		switch ($sign) {			//= < > != ...
		    case "bigger":
			return " >= ".$value;
		    case "smaller":
			return " <= ".$value;
		    case "is":
			return " = ".$value;
		    case "is_equal":
			return " ~* '^".substr($value, 1, -1)."$'";
		    case "is_not_equal":
			return " != ".$value;
		    case "is_not":
			return " != ".$value;
		    case "contains":
			return " ~* '.*".substr($value, 1, -1).".*'";
		    case "begins_with":
			return " ~* '^".substr($value, 1, -1)."'";
		    case "does_not_contain":
			return " !~* '.*".substr($value, 1, -1).".*'";
		}
		return false;
	}

	function GetSQLCommand()			//gives back the SQL command for the search
	{
		global $lang;
		$topic = false;		//stores whether there was a search for topic;
		$max = count($this->SQLquery);					//all rows of the advsearch
		for($i = 0; $i < $max; $i++)
			if ($this->SQLquery[$i][1] == "topic")	{$topic = true;	break;}

		$query="SELECT distinct programmes.* FROM (";
		$query.=" SELECT sotf_programmes.*, sotf_stations.name as station, sotf_series.name as seriestitle, sotf_series.description as seriesdescription, sotf_prog_rating.rating_value as rating";
		if ($topic) $query.=", sotf_topics.topic_name as topic_name";
		$query.=" FROM sotf_programmes";
		$query.=" LEFT JOIN sotf_stations ON sotf_programmes.station_id = sotf_stations.id";
		$query.=" LEFT JOIN sotf_series ON sotf_programmes.series_id = sotf_series.id";
		$query.=" LEFT JOIN sotf_prog_rating ON sotf_programmes.id = sotf_prog_rating.id";
		
		if ($topic)		// added the topics as left join for performance reasons
		{
			$query .= " LEFT JOIN sotf_prog_topics ON sotf_programmes.id = sotf_prog_topics.prog_id";
			$query .= " LEFT JOIN sotf_topics ON sotf_prog_topics.topic_id = sotf_topics.topic_id";
		}


		$query.=") as programmes WHERE published = 't'";
		
		for($i = 0; $i < $max ;$i++)		//go through all terms
		{
			//AND or OR words
			if ($i != 0) $query .= " ".$this->SQLquery[$i][0];
			else $query .= " AND";
			
			//set begining of round bracket
			if ( (($this->SQLquery[$i][0] == "AND") || ($i == 0)) && ($this->SQLquery[$i+1][0] == "OR") ) $query = $query." (";

			//field name eq sign and value
			if ($this->SQLquery[$i][4] == "date")
			{
				$query .= " ".$this->SQLquery[$i][1];
				$date = getdate($this->SQLquery[$i][3]);
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$date["year"]."-".$date["mon"]."-".$date["mday"]."'");
			}
			elseif ($this->SQLquery[$i][1] == "topic")
			{
				$query .= " (".
					" programmes.topic_name";
//					" sotf_topics.topic_name";
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$this->SQLquery[$i][3]."'");
				$query .= ")";
			}
			elseif ($this->SQLquery[$i][1] == "title")
			{
				if (strpos($this->SQLquery[$i][2], "not") == false) $andor = "OR";
				else $andor = "AND";	//if does not contain or not equal then NONE should contain it
				$query .= " (coalesce(title,'')";
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$this->SQLquery[$i][3]."'");
				$query .= " $andor coalesce(alternative_title,'')";
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$this->SQLquery[$i][3]."'");
				$query .= " $andor coalesce(episode_title,'')";
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$this->SQLquery[$i][3]."'").")";
			}
			elseif ($this->SQLquery[$i][1] == "person")
			{
				if ($this->SQLquery[$i][2] == "does_not_contain")
				{
					$qi2 = "contains";
					$not = "not";
				}
				elseif  ($this->SQLquery[$i][2] == "is_not_equal")
				{
					$qi2 = "is_equal";
					$not = "not";
				}
				else
				{
					$qi2 = $this->SQLquery[$i][2];
					$not = "";
				}

				$query .= " id $not in (SELECT sotf_object_roles.object_id as id FROM sotf_object_roles WHERE sotf_object_roles.contact_id = sotf_contacts.id AND";
				$query .= " ( coalesce(sotf_contacts.name,'')";
				$query .= $this->getEQSign($qi2, "'".$this->SQLquery[$i][3]."'");
				$query .= " OR coalesce(sotf_contacts.alias,'')";
				$query .= $this->getEQSign($qi2, "'".$this->SQLquery[$i][3]."'");
				$query .= " OR coalesce(sotf_contacts.acronym,'')";
				$query .= $this->getEQSign($qi2, "'".$this->SQLquery[$i][3]."'")."))";
			}
			elseif (($this->SQLquery[$i][4] == "number") or ($this->SQLquery[$i][4] == "genre"))
			{
				$query .= " ".$this->SQLquery[$i][1]." ";
				$query .= $this->getEQSign($this->SQLquery[$i][2], $this->SQLquery[$i][3]);
			}
			else
			{
				$query .= " ".$this->SQLquery[$i][1]." ";
				$query .= $this->getEQSign($this->SQLquery[$i][2], "'".$this->SQLquery[$i][3]."'");
			}

			//set end of round bracket
			if (($this->SQLquery[$i][0] == "OR") && ($this->SQLquery[$i+1][0] != "OR")) $query = $query." )";
		}
		$query = $query." ORDER BY ".$this->sort1.", ".$this->sort2;			//ISBN, TITLE 
		//print($query);
		//die();
		return $query;
	}


	function getPersons($program_id)			//gives back the persons that have to do with the program
	{
		global $db, $lang;
		$query="SELECT sotf_contacts.name, sotf_contacts.alias, sotf_contacts.acronym, sotf_role_names.name as role FROM sotf_contacts, sotf_role_names WHERE sotf_contacts.id = sotf_object_roles.contact_id AND sotf_object_roles.object_id = '$program_id' AND sotf_object_roles.role_id = sotf_role_names.role_id AND sotf_role_names.language='$lang'";
		return $db->getAll($query);
	}


	function getTopics($program_id)			//gives back the topics for the program
	{
		global $db, $lang;
		$query="SELECT sotf_topics.topic_name as name FROM sotf_topics WHERE sotf_topics.language = '$lang' AND sotf_prog_topics.topic_id = sotf_topics.topic_id AND sotf_prog_topics.prog_id = '$program_id'";
		return $db->getAll($query);
	}


	function AddRow($SQLlink, $SQLfield, $where = -1)			//add a row to the query
	{
		if ($SQLlink == "AND") $new[0] = "AND";
		else $new[0] = "OR";
		$new[1] = $SQLfield;		//name of the field
		$new[2] = "";			//=, !=, <, >
		$new[3] = "";			//text or date

		switch ($SQLfield) {		//set type of the field
		    case "station":
			$new[4] = "station";
		        break;
		    case "production_date":
			$new[4] = "date";
		        break;
		    case "language":
			$new[4] = "lang";
		        break;
		    case "series":
			$new[4] = "string";
		        break;
		    case "track":
			$new[4] = "string";
		        break;
		    case "topic":
			$new[4] = "topic";
		        break;
		    case "entry_date":
			$new[4] = "date";
		        break;
		    case "expiry_date":
			$new[4] = "date";
		        break;
		    case "modify_date":
			$new[4] = "date";
		        break;
		    case "broadcast_date":
			$new[4] = "date";
		        break;
		    case "owner":
			$new[4] = "string";
		        break;
		    case "person":
			$new[4] = "string";
		        break;
		    case "title":
			$new[4] = "string";
		        break;
		    case "seriestitle":
			$new[4] = "string";
		        break;
		    case "seriesdescription":
			$new[4] = "string";
		        break;
		    case "keywords":
			$new[4] = "string";
		        break;
		    case "genre_id":
			$new[4] = "genre";
		        break;
		    case "abstract":
			$new[4] = "string";
		        break;
		    case "length":
			$new[4] = "length";
		        break;
		    case "contact_email":
			$new[4] = "string";
		        break;
		    case "contact_phone":
			$new[4] = "string";
		        break;
		    case "spatial_coverage":
			$new[4] = "string";
		        break;
		    case "temporal_coverage":
			$new[4] = "date";
		        break;
		    case "rating":
			$new[4] = "rating";
		        break;
		}

		if ($where == -1)
		{
			$this->SQLquery[] = $new;
		}
		else
		{
			$max = count($this->SQLquery);
			$output1 = array_slice($this->SQLquery, 0, $where+1);   
			if ($where < $max) $output2 = array_slice($this->SQLquery, $where+1);
			//var_dump($output);
			$output1[]=$new;
			$this->SQLquery = array_merge($output1, $output2);
		}
		return $this->SQLquery;
	}
	
	function DelRow($where)		//set the sort order
	{
		$max = count($this->SQLquery);
		if ($where > 0) $output1 = array_slice($this->SQLquery, 0, $where);
		if ($where < $max) $output2 = array_slice($this->SQLquery, $where+1);
		$this->SQLquery = array_merge($output1, $output2);
		return $this->SQLquery;
	}

	function SetSortOrder($sort1 = "entry_date DESC", $sort2 = "station")		//set the sort order
	{
		$pos = strpos($this->sort1, " DESC");			//SORT 1
		if ($pos) $this->sort1 = $sort1." DESC";
		else  $this->sort1 = $sort1;

		$pos = strpos($this->sort2, " DESC");			//SORT 2
		if ($pos) $this->sort2 = $sort2." DESC";
		else  $this->sort2 = $sort2;
	}
	
	function setDir($dir1 = false, $dir2 = false)					//gives back if the current sort order direction 1 is DESC or not
	{
		$pos = strpos($this->sort1, " DESC");			//SORT 1
		if (!$pos AND $dir1) $this->sort1 .= " DESC";		//add DESC
		elseif ($pos AND !$dir1) $this->sort1 = substr($this->sort1, 0, $pos);		//remove DESC

		$pos = strpos($this->sort2, " DESC");			//SORT 2
		if (!$pos AND $dir2) $this->sort2 .= " DESC";		//add DESC
		elseif ($pos AND !$dir2) $this->sort2 = substr($this->sort2, 0, $pos);		//remove DESC
	}

	function GetHumanReadable()			//translates fieldnames for all rows of the query
	{
		global $page;
		$SQLfields = "";
		$max = count($this->SQLquery);
		for($i=0; $i < $max; $i++)
			{
			$SQLfield[0] = $page->getlocalized($this->SQLquery[$i][0]);
			$SQLfield[1] = $page->getlocalized($this->SQLquery[$i][1]);
			$SQLfield[2] = $page->getlocalized($this->SQLquery[$i][2]);
			if ($this->SQLquery[$i][4] == "date") $SQLfield[3] = date("Y-m-d", $this->SQLquery[$i][3]);
				elseif ($this->SQLquery[$i][4] == "lang") $SQLfield[3] = $page->getlocalized($this->SQLquery[$i][3]);
				else $SQLfield[3] = $this->SQLquery[$i][3];
			$SQLfield[4] = $this->SQLquery[$i][4];
			$SQLfields[] = $SQLfield;
			}
		return $SQLfields;
	}

	function GetSQLqueryfields()			//translates fieldnames for all rows of the query
	{
		global $page;
		$max = count($this->SQLquery);
		for($i=0; $i < $max; $i++)
			$SQLfield[] = $page->getlocalized($this->SQLquery[$i][1]);
		return $SQLfield;
	}

/*
	function GetSQLqueryEQs()			//translates EQs (=, <, >...) for all rows of the query
	{
		global $page;
		$max = count($this->SQLquery);
		for($i=0; $i < $max; $i++)
			$SQLEQfiels[] = $page->getlocalized($this->SQLquery[$i][2]);
		return $SQLEQfiels;
	}
*/

	function GetSQLfields()		//translates fieldnames for dropdown box
	{
		global $page;
		$SQLfiels[station] = $page->getlocalized("station");
		$SQLfiels[production_date] = $page->getlocalized("production_date");
		$SQLfiels[language] = $page->getlocalized("language");
		$SQLfiels[person] = $page->getlocalized("person");
		$SQLfiels[title] = $page->getlocalized("title");
		$SQLfiels[seriestitle] = $page->getlocalized("seriestitle");
		$SQLfiels[topic] = $page->getlocalized("topic");
		$SQLfiels[length] = $page->getlocalized("length");
		$SQLfiels[rating] = $page->getlocalized("rating");
		//$SQLfiels[track] = $page->getlocalized("track");
		$SQLfiels[genre_id] = $page->getlocalized("genre_id");
		$SQLfiels[keywords] = $page->getlocalized("keywords");
		$SQLfiels[abstract] = $page->getlocalized("abstract");
		$SQLfiels[seriesdescription] = $page->getlocalized("seriesdescription");
		$SQLfiels[entry_date] = $page->getlocalized("entry_date");
		$SQLfiels[expiry_date] = $page->getlocalized("expiry_date");
		$SQLfiels[modify_date] = $page->getlocalized("modify_date");
		$SQLfiels[broadcast_date] = $page->getlocalized("broadcast_date");
		$SQLfiels[spatial_coverage] = $page->getlocalized("spatial_coverage");
		$SQLfiels[temporal_coverage] = $page->getlocalized("temporal_coverage");
		
		asort($SQLfiels);
		return $SQLfiels;
	}

	function getOrderFields()		//translates fieldnames for dropdown box
	{
	  global $page;
		$SQLfiels = $this->GetSQLfields();
		foreach($SQLfiels as $key => $value) if ($key != "person" AND $key != "topic") $OrderFields[$key] = $value;
		$OrderFields['episode_sequence'] = $page->getlocalized("episode_sequence");
		asort($OrderFields);
		return $OrderFields;
	}

	function GetLanguages()		//returns all the languages
	{
		global $page, $config;
		$max = count($config['languages']);
		for($i=0; $i<$max;$i++) $Languages[$config['languages'][$i]] = $page->getlocalized($config['languages'][$i]);
		return $Languages;
	}

	function GetStations()		//returns all the stations
	{
		$stationsarray = sotf_Station::listStationNames();
		$max = count($stationsarray);
		for($i=0; $i<$max;$i++) $Stations[$stationsarray[$i][name]] = $stationsarray[$i][name];
		return $Stations;
	}

	function GetGenres()		//returns all the genres
	{
		global $vocabularies;
		$genresarray = $vocabularies->getGenres();
		$max = count($genresarray);
		for($i=0; $i<$max;$i++) $Genres[$genresarray[$i][id]] = $genresarray[$i][name];
		return $Genres;
	}

	function getRatings()		//returns all the genres
	{
		$rating = new sotf_Rating();
		for($i=$rating->minValue; $i<=$rating->maxValue; $i+=0.5) $ratings["$i"] = "$i";
		return $ratings;
	}

	function GetEQdate()		//returns EQ options for dates
	{
		global $page;
		$EQdate[bigger] = $page->getlocalized("after");
		$EQdate[smaller] = $page->getlocalized("before");
		$EQdate[is] = $page->getlocalized("is");
		$EQdate[is_not] = $page->getlocalized("is_not");
		return $EQdate;
	}

	function GetEQstring()		//returns EQ options for strings
	{
		global $page;
		$EQstring[contains] = $page->getlocalized("contains");
		$EQstring[begins_with] = $page->getlocalized("begins_with");
		$EQstring[is_equal] = $page->getlocalized("is");
		$EQstring[does_not_contain] = $page->getlocalized("does_not_contain");
		$EQstring[is_not_equal] = $page->getlocalized("is_not_equal");
		return $EQstring;
	}

	function GetEQtopic()		//returns EQ options for topics
	{
		global $page;
		$EQstring[contains] = $page->getlocalized("contains");
		$EQstring[begins_with] = $page->getlocalized("begins_with");
		$EQstring[is_equal] = $page->getlocalized("is");
		//$EQstring[does_not_contain] = $page->getlocalized("does_not_contain");
		//$EQstring[is_not_equal] = $page->getlocalized("is_not_equal");
		return $EQstring;
	}


	function GetEQlang()		//returns EQ options for languages and station
	{
		global $page;
		$EQlang[is] = $page->getlocalized("is");
		$EQlang[contains] = $page->getlocalized("contains");
		$EQlang[does_not_contain] = $page->getlocalized("does_not_contain");
		$EQlang[is_not] = $page->getlocalized("is_not");
		return $EQlang;
	}

	function GetEQlength()		//returns EQ options for length
	{
		global $page;
		$EQlength[bigger] = $page->getlocalized("longer");
		$EQlength[smaller] = $page->getlocalized("shorter");
		$EQlength[is] = $page->getlocalized("is");
		$EQlength[is_not] = $page->getlocalized("is_not");
		return $EQlength;
	}

	function GetEQnumber()		//returns EQ options for numbers
	{
		global $page;
		$EQnumber[bigger] = $page->getlocalized("bigger");
		$EQnumber[smaller] = $page->getlocalized("smaller");
		$EQnumber[is] = $page->getlocalized("is");
		$EQnumber[is_not] = $page->getlocalized("is_not");
		return $EQnumber;
	}

	function simpleSearch($words, $language = false, $stationId = '')		//searches the words in the most popular fields
	{
		global $db;
		$this->allid = array();
		$words = sotf_Utils::magicQuotes(strip_tags($words));		//remove special chars
		$word = split(" ", $words);			//split into separate words
		$max = count($word);				//count words
		for ($i=0; $i<$max; $i++)			//go through all words
		{
			$word[$i] = trim($word[$i]);			//trim word
			if ($word[$i] == "") continue;			//in empty get next
			//find word at the most common places
			$serial = str_replace("XXX", $word[$i], "production_date|Bstation|AAND|Bperson|Bcontains|BXXX|Bstring|AOR|Btitle|Bcontains|BXXX|Bstring|AOR|Bkeywords|Bcontains|BXXX|Bstring|AOR|Babstract|Bcontains|BXXX|Bstring|AOR|Bspatial_coverage|Bcontains|BXXX|Bstring");
			if ($language) $serial .= "|AAND|Blanguage|Bis|B".$language."|Blang";		//if language given add to search options
      if ($stationId) $serial .= "|AAND|Bstation|Bis|B".$stationId."|Bstation";
			$this->Deserialize($serial);		//deserialize query
			$query = $this->GetSQLCommand();	//get desrialized query
			$query = "SELECT id FROM (".$query.") as a";
			$result = $db->getAll($query);
			$maxk = count($result);				//count words
			for ($k=0; $k<$maxk; $k++)			//go through all results
			{
				if (array_key_exists($result[$k]["id"], $this->allid)) $this->allid[$result[$k]["id"]] += 1;
				else $this->allid[$result[$k]["id"]] = 1;
			}
		}
		return count($this->allid);
	}

	function getSimpleSearchResults($from, $to)		//gives back the search results from the simpleSearch
	{
		global $db;
		arsort($this->allid, SORT_NUMERIC);		//sorts the array, bigger numbers first
		$result = array();
		$i = -1;
		foreach($this->allid as $id => $value)
		{
			$i++;
			if ($i < $from) continue;
			if ($i > $to) break;
			$query = "SELECT * FROM sotf_programmes WHERE id='$id'";
			$result = array_merge($result, $db->getAll($query));
		}
//		print("<pre>");
//		var_dump($result);
//		print("</pre>");
    return($result);
	}

}
?>
Return current item: StreamOnTheFly