<?php
/**
 * Search module
 *
 * MySQL queries related to search module
 *
 * @package linea21\core\search
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

/**
 * SQL_comment_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_comment_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$status_mask = " AND comment_status = 'P' ";
	}
	if ($type == 'ADMIN') {
		$status_mask = '';
	}
	
	$q =	"SELECT C.*, U.user_id, U.user_login, ".
			"DATE_FORMAT(C.comment_date_crea, '".toStringSqlDate('long')."') AS date_display, ".
			"MATCH(comment_name, comment_email, comment_body) AGAINST('" . $string . "') AS pertinence FROM " . T_COMM . " AS C ".
			"LEFT OUTER JOIN " . T_USER . " AS U ON C.comment_user_id = U.user_id ".
			"LEFT OUTER JOIN " . T_PROFILE . " AS P ON P.profile_id = U.user_profile ".
			"WHERE MATCH (comment_name, comment_email, comment_body) AGAINST ('" . $string . "' ".$search_mode.")".
			$status_mask . "ORDER BY pertinence DESC;";
			
			return $q;
}

/**
 * SQL_project_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_project_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'project_published_date';
		$status_mask = 'AND project_statut<>\'E\' AND project_statut<>\'AA\' AND project_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'project_date_crea';
		$status_mask = 'AND project_statut<>\'E\'';
	}
	$q = 'SELECT project_id, project_name, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, project_statut AS statut, MATCH(project_name,project_goal,project_description,project_body,project_comment,project_budget_comment, project_elected, project_team) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_PROJECT . ' WHERE MATCH (project_name,project_goal,project_description,project_body,project_comment,project_budget_comment, project_elected, project_team) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_page_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_page_search($string, $type, $searchtype)
{
	
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'page_published_date';
		$status_mask = 'AND page_status <> \'E\' AND page_status <> \'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'page_date_crea';
		$status_mask = 'AND page_status <>\'E\'';
	}
	$q = 'SELECT page_id, page_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, page_status AS statut, MATCH(page_title, page_header, page_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_PAGE . ' WHERE MATCH (page_title, page_header, page_body) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_news_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_news_search($string, $type, $searchtype)
{
	
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'news_published_date';
		$status_mask = 'AND news_statut<>\'E\' AND news_statut<>\'AA\' AND news_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'news_date_crea';
		$status_mask = 'AND news_statut<>\'E\'';
	}
	$q = 'SELECT news_id, news_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, news_statut AS statut, MATCH(news_title, news_header, news_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_NEWS . ' WHERE MATCH (news_title, news_header, news_body) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_newsletter_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_newsletter_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'newsletter_published_date';
		$status_mask = 'AND newsletter_statut<>\'E\' AND newsletter_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'newsletter_date_crea';
		$status_mask = 'AND newsletter_statut<>\'E\'';
	}
	$q = 'SELECT newsletter_id, newsletter_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, newsletter_statut AS statut, MATCH(newsletter_title, newsletter_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_NEWSLETTER . ' WHERE MATCH (newsletter_title, newsletter_body) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_publication_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_publication_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'publi_published_date';
		$status_mask = 'AND publi_statut<>\'E\' AND publi_statut<>\'AA\' AND publi_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'publi_date_crea';
		$status_mask = 'AND publi_statut<>\'E\'';
	}
	$q = 'SELECT publi_id, publi_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, publi_statut AS statut, MATCH(publi_title, publi_resume) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_PUBLI . ' WHERE MATCH (publi_title, publi_resume) AGAINST (\'' . $string . '\' '.$search_mode.') AND publi_statut<>\'E\' ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_publication_content_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_publication_content_search($string, $type, $searchtype)
{
	
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'publi_published_date';
		$status_mask = 'AND publi_statut<>\'E\' AND publi_statut<>\'AA\' AND publi_statut<>\'D\' AND publicon_validity=\'Y\'';
	}
	if ($type == 'ADMIN') {
		$date = 'publi_date_crea';
		$status_mask = 'AND publi_statut<>\'E\' AND publicon_validity=\'Y\'';
	}
	$q = 'SELECT publi_id, publi_title, publicon_id, publicon_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, publi_statut AS statut, MATCH(publicon_title, publicon_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . J_PARTS . ' LEFT OUTER JOIN ' . T_PUBLI_CONT . ' ON j_parts_id  =  publicon_id LEFT OUTER JOIN  ' . T_PUBLI . ' ON  j_root_id= publi_id WHERE MATCH (publicon_title, publicon_body) AGAINST (\'' . $string . '\' '.$search_mode.') AND j_type=\'P\' ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_sdi_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_sdi_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$status_mask = 'AND sdii_statut<>\'D\' AND sdii_statut<>\'E\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND sdii_statut<>\'E\'';
	}
	$q = 'SELECT sdii_id, sdii_name, DATE_FORMAT(sdii_date_crea, \''.toStringSqlDate().'\') AS date_display, sdii_statut AS statut, MATCH(sdii_name, sdii_description, sdii_goal, sdii_consulting) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_SDI_INFO . ' WHERE MATCH ( sdii_name, sdii_description, sdii_goal, sdii_consulting) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_workshop_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_workshop_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'workshop_date_crea';
		$status_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'workshop_date_crea';
		$status_mask = 'AND workshop_statut<>\'E\'';
	}
	$q = 'SELECT workshop_id, workshop_denomination, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workshop_statut AS statut, MATCH(workshop_denomination, workshop_resume) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_WORK . ' WHERE MATCH ( workshop_denomination, workshop_resume) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_workshop_content_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_workrep_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'workrep_published_date';
		$status_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workrep_statut<>\'E\' AND workrep_statut<>\'AA\' AND workrep_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$date = 'workrep_date_crea';
		$status_mask = 'AND workshop_statut<>\'E\' AND workrep_statut<>\'E\'';
	}
	$q = 'SELECT workrep_id, workrep_title, workshop_id, workshop_denomination, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workrep_statut AS statut, MATCH(workrep_title, workrep_resume) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_WORK_REP . ' LEFT OUTER JOIN ' . T_WORK . ' ON workrep_workshop_id  =  workshop_id WHERE MATCH (workrep_title, workrep_resume) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_workshop_content_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_workrep_content_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$date = 'workrep_date_crea';
		$status_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workrep_statut<>\'E\' AND workrep_statut<>\'AA\' AND workrep_statut<>\'D\' AND workrepcon_validity=\'Y\'';
	}
	if ($type == 'ADMIN') {
		$date = 'workrep_date_crea';
		$status_mask = 'AND workshop_statut<>\'E\' AND workrep_statut<>\'E\' AND workrepcon_validity=\'Y\'';
	}
	$q = 'SELECT workrep_id, workrep_title, workrepcon_id, workrepcon_title, DATE_FORMAT(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workrep_statut AS statut, MATCH(workrepcon_title, workrepcon_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . J_PARTS . ' LEFT OUTER JOIN ' . T_WORK_REP . ' ON j_root_id  =  workrep_id LEFT OUTER JOIN ' . T_WORK_REP_CONT . ' ON  j_parts_id= workrepcon_id LEFT OUTER JOIN ' . T_WORK . ' ON workrep_workshop_id  =  workshop_id WHERE MATCH (workrepcon_title, workrepcon_body) AGAINST (\'' . $string . '\' '.$search_mode.') AND j_type=\'W\' ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_workshop_content_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_workshop_calendar_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$status_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workcal_validity=\'Y\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND workshop_statut<>\'E\' AND workcal_validity=\'Y\'';
	}
	
	$q = 'SELECT workcal_id, workcal_task, workcal_workshop_id, workshop_denomination, workshop_statut AS statut, DATE_FORMAT(workcal_date_crea, \''.toStringSqlDate().'\') AS date_display, MATCH(workcal_task, workcal_task_details) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_WORK_CAL . ' LEFT OUTER JOIN ' . T_WORK . ' ON  workcal_workshop_id  = workshop_id WHERE MATCH (workcal_task, workcal_task_details) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_workshop_content_search()
 *
 * @param string $string
 * @param string $type
 * @return string $q
 */
function SQL_workshop_com_search($string, $type, $searchtype)
{
	// do we run query expansion query or not ?
	$searchtype === 'expanded' ? $search_mode = 'WITH QUERY EXPANSION' : $search_mode = SEARCH_MODE;
	
	if ($type == 'PUBLIC') {
		$status_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workcom_statut <>\'E\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND workshop_statut<>\'E\' AND workcom_statut <>\'E\'';
	}
	
	$q = 'SELECT workcom_id, workcom_parent, workcom_subject, workcom_workshop_id, workshop_denomination, workshop_statut AS statut, DATE_FORMAT(workcom_date_crea, \''.toStringSqlDate().'\') AS date_display, MATCH(workcom_subject, workcom_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_WORK_COM . ' LEFT OUTER JOIN ' . T_WORK . ' ON  workcom_workshop_id  = workshop_id WHERE MATCH (workcom_subject, workcom_body) AGAINST (\'' . $string . '\' '.$search_mode.') ' . $status_mask . ' ORDER BY pertinence DESC;';
	
	return $q;
}

/**
 * SQL_yellowpages_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_yellowpages_search($string, $type, $searchtype)
{
	if ($type == 'PUBLIC') {
		$status_mask = 'AND yellowp_statut<>\'E\' AND yellowp_statut<>\'AA\' AND yellowp_statut<>\'D\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND yellowp_statut<>\'E\'';
	}
	$q = 'SELECT yellowp_id, yellowp_name, DATE_FORMAT(yellowp_date_crea, \''.toStringSqlDate().'\') AS date_display, yellowp_statut AS statut FROM ' . T_YELLOWPAGES . ' ' . SQL_ConstructorExpression($string, $searchtype, 'yellowp_name') . ' ' . $status_mask . ' ORDER BY yellowp_name ASC;';
	
	return $q;
}

/**
 * SQL_user_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_user_search($string, $type, $searchtype)
{
	if ($type == 'PUBLIC') {
		$status_mask = 'AND user_validity<>\'D\' AND user_validity<>\'N\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND user_validity<>\'N\'';
	}
	$q = 'SELECT user_id, user_login, profile_firstname, profile_lastname, profile_email, DATE_FORMAT(user_date_crea, \''.toStringSqlDate().'\') AS date_display, user_validity AS statut FROM ' . T_USER . ' LEFT OUTER JOIN '. T_PROFILE . ' on user_id = profile_id '  . SQL_ConstructorExpression($string, $searchtype, 'user_login') . SQL_ConstructorExpression($string, $searchtype, 'profile_firstname', ' OR ') . SQL_ConstructorExpression($string, $searchtype, 'profile_lastname',' OR ') . SQL_ConstructorExpression($string, $searchtype, 'profile_email', ' OR ') . ' ' . $status_mask . ' ORDER BY user_login ASC;';
	
	return $q;
}


/**
 * SQL_tag_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_tag_search($string, $type, $searchtype)
{
	
	$q = 'SELECT tag_id, tag_name, DATE_FORMAT(tag_date_crea, \''.toStringSqlDate().'\') AS date_display FROM ' . T_TAG . ' ' . SQL_ConstructorExpression($string, $searchtype, 'tag_name') . ' ORDER BY tag_name ASC;';
	
	return $q;
}

/**
 * SQL_level_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_level_search($string, $type, $searchtype)
{
	if ($type == 'PUBLIC') {
		$status_mask = 'AND level_status<>\'D\' AND level_status<>\'E\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND level_status<>\'E\'';
	}
	$q = 'SELECT level_id, level_name, DATE_FORMAT(level_date_crea, \''.toStringSqlDate().'\') AS date_display, level_status AS statut FROM ' . T_LEVEL . ' ' . SQL_ConstructorExpression($string, $searchtype, 'level_name') . ' ' . $status_mask . ' ORDER BY level_name ASC;';
	
	return $q;
}

/**
 * SQL_scale_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_scale_search($string, $type, $searchtype)
{
	if ($type == 'PUBLIC') {
		$status_mask = 'AND scale_statut<>\'D\' AND scale_statut<>\'E\' AND scale_statut<>\'I\'';
	}
	if ($type == 'ADMIN') {
		$status_mask = 'AND scale_statut<>\'I\' AND scale_statut<>\'E\'';
	}
	$q = 'SELECT scale_id, scale_denomination, DATE_FORMAT(scale_date_crea, \''.toStringSqlDate().'\') AS date_display, scale_statut AS statut FROM ' . T_SCALE . ' ' . SQL_ConstructorExpression($string, $searchtype, 'scale_denomination') . ' ' . $status_mask . ' ORDER BY scale_denomination ASC;';
	
	return $q;
}

/**
 * SQL_ConstructorExpression()
 * Construit les requêtes simple
 *
 * @param string $string
 * @param string $searchtype
 * @param string $field_name
 * @return string $q
 */
function SQL_ConstructorExpression($string, $searchtype, $field_name, $prefix = 'WHERE ')
{
	$clause_requete = ''. $prefix;
	
	$sep = '';
	$string = trim($string);
	$strings = @explode(' ', $string);
	switch ($searchtype) {
		case 'one':
			for($i = 0; $i < count($strings); $i++) {
				if(strlen($strings[$i])<=3)	$clause_requete .= $sep . $field_name . ' LIKE \'' . $strings[$i] . '%\'';
				else $clause_requete .= $sep . $field_name . ' LIKE \'%' . $strings[$i] . '%\'';
				$sep = ' OR ';
			}
			break;
		case 'all':
			for($i = 0; $i < count($strings); $i++) {
				if(strlen($strings[$i])<=3) $clause_requete .= $sep . $field_name . ' LIKE \'' . $strings[$i] . '%\'';
				else $clause_requete .= $sep . $field_name . ' LIKE \'%' . $strings[$i] . '%\'';
				$sep = ' AND ';
			}
			break;
		case 'exp':
			$clause_requete .= $field_name . '=\'' . $string . '\'';
			break;
		case 'expanded':
			for($i = 0; $i < count($strings); $i++) {
				if(strlen($strings[$i])<=3)	$clause_requete .= $sep . $field_name . ' LIKE \'' . $strings[$i] . '%\'';
				else $clause_requete .= $sep . $field_name . ' LIKE \'%' . $strings[$i] . '%\'';
				$sep = ' OR ';
			}
			break;
		default:
			$clause_requete .= $field_name . ' LIKE \'%' . $string . '%\'';
			break;
	}
	return $clause_requete;
}

?>