<?php
/**
 * @package linea21.core
 * @subpackage search
 * @author linea21 <info@linea21.com>
 * @version $id SVN
 * @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)
{

  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 . "' IN BOOLEAN 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)
{

  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_description, project_body) AGAINST(\'' . $string . '\') AS pertinence FROM ' . T_PROJECT . ' WHERE MATCH (project_name, project_description, project_body) AGAINST (\'' . $string . '\' IN BOOLEAN 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)
{

	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 . '\' IN BOOLEAN 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)
{

  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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 . '\' IN BOOLEAN 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)
{
  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, 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 . '\' IN BOOLEAN 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)
{
  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, 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 . '\' IN BOOLEAN 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, DATE_FORMAT(user_date_crea, \''.toStringSqlDate().'\') AS date_display, user_validity AS statut FROM ' . T_USER . ' ' . SQL_ConstructorExpression($string, $searchtype, 'user_login') . ' ' . $status_mask . ' ORDER BY user_login ASC;';

  return $q;
}

/**
 * SQL_theme_search()
 *
 * @param string $string
 * @param string $type
 * @param string $searchtype
 * @return string $q
 */
function SQL_theme_search($string, $type, $searchtype)
{
  if ($type == 'PUBLIC') {
    $status_mask = 'AND theme_statut<>\'D\' AND theme_statut<>\'E\'';
  }
  if ($type == 'ADMIN') {
    $status_mask = 'AND theme_statut<>\'E\'';
  }
  $q = 'SELECT theme_id, theme_name, DATE_FORMAT(theme_date_crea, \''.toStringSqlDate().'\') AS date_display, theme_statut AS statut FROM ' . T_THEME . ' ' . SQL_ConstructorExpression($string, $searchtype, 'theme_name') . ' ' . $status_mask . ' ORDER BY theme_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_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_statut<>\'D\' AND level_statut<>\'E\'';
  }
  if ($type == 'ADMIN') {
    $status_mask = 'AND level_statut<>\'E\'';
  }
  $q = 'SELECT level_id, level_name, DATE_FORMAT(level_date_crea, \''.toStringSqlDate().'\') AS date_display, level_statut AS statut FROM ' . T_LEVEL . ' ' . SQL_ConstructorExpression($string, $searchtype, 'level_name') . ' ' . $status_mask . ' ORDER BY level_name 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)
{
  $clause_requete = 'WHERE ';
  $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;
    default:
      $clause_requete .= $field_name . ' LIKE \'%' . $string . '%\'';
      break;
  }
  return $clause_requete;
}

?>
