/**
* adv_search_articles, to replace adv_search_articles    in \phpbrain\inc\adv_search_articles\class\class.sokb.inc.php
*/

	/**
	* Returns results of advanced search
	*
	* @author	Alejandro Pedraza / modified by Spirea / may 2015
	* @access	public
	* @param	array	$owners			User ids accessible by current user
	* @param	array	$cats_ids		Categories filtering
	* @param	string	$ocurrences		Field name where to search
	* @param	string	$pub_date		Articles from last 3 or 6 months, or last year
	* @param	int		$start			For pagination
	* @param	int		$num_res		For pagination
	* @param	string	$all_words		'with all the words' filtering
	* @param	string	$phrase			'exact phrase' filtering
	* @param	string	$one_word		'with at least one of the words' filtering
	* @param	string	$without_words	'without the words' filtering
	* @param	int		$cat			Don't know
	* @param	bool	$include_subs	Include subcategories when filtering by categories. Seems to not being working
	* @return	array					Articles
	* @todo		use params $cat and $include_subs
	*/
	function adv_search_articles($owners, $cats_ids, $ocurrences, $pub_date, $start, $num_res, $all_words, $phrase, $one_word, $without_words, $cat, $include_subs)
	{
		$loclike = self::$like;
		$fields= array('egw_kb_articles.art_id', 'title', 'topic', 'views', 'cat_id', 'published', 'user_id', 'created', 'modified', 'votes_1', 'votes_2',  'votes_3', 'votes_4', 'votes_5');
		$fields_str	= implode(' , ', $fields);

		// Remove empty & duplicates
		foreach($owners as $owner){
			if($owner != 0) $temp_owners[$owner] = $owner;
		}
		$owners = $temp_owners;

		// permissions filtering
		$owners	= implode(', ', $owners);
		$sql = "SELECT DISTINCT $fields_str FROM egw_kb_articles LEFT JOIN egw_kb_search ON egw_kb_articles.art_id=egw_kb_search.art_id WHERE user_id IN ($owners)";

		$temp_select = "SELECT DISTINCT $fields_str FROM egw_kb_articles";
		$where = "WHERE user_id IN ($owners)";

		// categories filtering
		$cats_ids	= implode (',', $cats_ids);
		if ($cats_ids){
			$sql .= " AND cat_id IN ($cats_ids)";
			$where .= " AND cat_id IN ($cats_ids)";
		}

		// date filtering
		switch ($pub_date)
		{
			case '3':
				$sql .= " AND created>" . mktime(0, 0, 0, date('n')-3);
				$where .= " AND created>" . mktime(0, 0, 0, date('n')-3);
				break;
			case '6':
				$sql .= " AND created>" . mktime(0, 0, 0, date('n')-6);
				$where .= " AND created>" . mktime(0, 0, 0, date('n')-6);
				break;
			case 'year':
				$sql .= " AND created>" . mktime(0, 0, 0, date('n')-12);
				$where .= " AND created>" . mktime(0, 0, 0, date('n')-12);
				break;
		}

		// ocurrences filtering
		switch ($ocurrences)
		{
			case 'title':
				$target_fields = array('title');
				break;
			case 'topic':
				$target_fields = array('topic');
				break;
			case 'text':
				$target_fields = array('text');
				break;
			default:
				$target_fields = array('title', 'topic', 'keyword', 'text');
				break;
		}

		// "with all the words" filtering
		$all_words = self::$db->db_addslashes($all_words);
		$all_words = strlen($all_words)? explode(' ', $all_words) : False;

		$temp_sql = array();
		if($all_words){
			foreach ($all_words as $word) {
				$sub_sql = array();
				foreach($target_fields as $field){
					$select = $temp_select;
					if($field == 'keyword') $select .= ' LEFT JOIN egw_kb_search ON egw_kb_search.art_id = egw_kb_articles.art_id';
					$sub_sql[] = $select." ".$where." AND ".$field."  {$loclike} '%$word%'";
				}
				$temp_sql[] = "SELECT * FROM (".implode(" UNION ", $sub_sql).") A".count($temp_sql);
			}

			// Article must be in all sub-request
			$sql_temp[] = "SELECT * FROM (".implode(" UNION ALL ", $temp_sql).") B GROUP BY art_id HAVING count(art_id) = ".count($all_words);
		}


		// "with the exact phrase" filtering
		$phrase = self::$db->db_addslashes($phrase);

		if($phrase){
			$sub_sql = array();
			foreach($target_fields as $field){
				$select = $temp_select;
				if($field == 'keyword') $select .= ' LEFT JOIN egw_kb_search ON egw_kb_search.art_id = egw_kb_articles.art_id';
				$sub_sql[] = $select." ".$where." AND ".$field."  {$loclike} '%$phrase%'";
			}
			$sql_temp[] = "SELECT * FROM (".implode(" UNION ", $sub_sql).") C".count($temp_sql);
		}

		// "With at least one of the words" filtering
		$one_word = self::$db->db_addslashes($one_word);
		$one_word = strlen($one_word)? explode(' ', $one_word) : False;

		if ($one_word){
			foreach ($one_word as $word) {
				$sub_sql = array();
				foreach($target_fields as $field){
					$select = $temp_select;
					if($field == 'keyword') $select .= ' LEFT JOIN egw_kb_search ON egw_kb_search.art_id = egw_kb_articles.art_id';
					$sub_sql[] = $select." ".$where." AND ".$field."  {$loclike} '%$word%'";
				}
				$temp_sql[] = "SELECT * FROM (".implode(" UNION ", $sub_sql).") D".count($temp_sql);
			}

			$sql_temp[] = "SELECT * FROM (".implode(" UNION ", $temp_sql).") E GROUP BY art_id";
		}

		// "Without the words" filtering
		$without_words = self::$db->db_addslashes($without_words);
		$without_words = strlen($without_words)? explode(' ', $without_words) : False;
		$each_field = array();
		if ($without_words)
		{
			foreach ($without_words as $word)
			{
				$each_field[] = "(" . implode(" NOT {$loclike} '%$word%' AND ", $target_fields) . " NOT {$loclike} '%$word%')";
			}
			$sql .= " AND " . implode(" AND ", $each_field);

			$sql_temp[] = $sql;
		}

		if(!empty($sql_temp)){
			$sql = "SELECT * FROM (".implode(" UNION ALL ", $sql_temp).") F GROUP BY art_id HAVING count(art_id) = ".count($sql_temp);
		}

		// do the query
		self::$db->query($sql, __LINE__, __FILE__);
		self::$num_rows = self::$db->num_rows();
		self::$db->limit_query($sql, $start, __LINE__, __FILE__, $num_res);
		return $this->results_to_array($fields);
	}