* @version $id SVN * @access public * @license http://opensource.org/licenses/gpl-3.0.html */ /** * SQL_project_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_project_search($string, $type) { if ($type == 'PUBLIC') { $date = 'project_published_date'; $statut_mask = 'AND project_statut<>\'E\' AND project_statut<>\'AA\' AND project_statut<>\'D\''; } if ($type == 'ADMIN') { $date = 'project_date_crea'; $statut_mask = 'AND project_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_news_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_news_search($string, $type) { if ($type == 'PUBLIC') { $date = 'news_published_date'; $statut_mask = 'AND news_statut<>\'E\' AND news_statut<>\'AA\' AND news_statut<>\'D\''; } if ($type == 'ADMIN') { $date = 'news_date_crea'; $statut_mask = 'AND news_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_newsletter_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_newsletter_search($string, $type) { if ($type == 'PUBLIC') { $date = 'newsletter_published_date'; $statut_mask = 'AND newsletter_statut<>\'E\' AND newsletter_statut<>\'D\''; } if ($type == 'ADMIN') { $date = 'newsletter_date_crea'; $statut_mask = 'AND newsletter_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_publication_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_publication_search($string, $type) { if ($type == 'PUBLIC') { $date = 'publi_published_date'; $statut_mask = 'AND publi_statut<>\'E\' AND publi_statut<>\'AA\' AND publi_statut<>\'D\''; } if ($type == 'ADMIN') { $date = 'publi_date_crea'; $statut_mask = 'AND publi_statut<>\'E\''; } $requete = '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\' ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_publication_content_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_publication_content_search($string, $type) { if ($type == 'PUBLIC') { $date = 'publi_published_date'; $statut_mask = 'AND publi_statut<>\'E\' AND publi_statut<>\'AA\' AND publi_statut<>\'D\' AND publicon_validity=\'Y\''; } if ($type == 'ADMIN') { $date = 'publi_date_crea'; $statut_mask = 'AND publi_statut<>\'E\' AND publicon_validity=\'Y\''; } $requete = '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\' ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_sdi_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_sdi_search($string, $type) { if ($type == 'PUBLIC') { $statut_mask = 'AND sdii_statut<>\'D\' AND sdii_statut<>\'E\''; } if ($type == 'ADMIN') { $statut_mask = 'AND sdii_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_workshop_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_workshop_search($string, $type) { if ($type == 'PUBLIC') { $date = 'workshop_date_crea'; $statut_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\''; } if ($type == 'ADMIN') { $date = 'workshop_date_crea'; $statut_mask = 'AND workshop_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_workshop_content_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_workrep_search($string, $type) { if ($type == 'PUBLIC') { $date = 'workrep_published_date'; $statut_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'; $statut_mask = 'AND workshop_statut<>\'E\' AND workrep_statut<>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_workshop_content_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_workrep_content_search($string, $type) { if ($type == 'PUBLIC') { $date = 'workrep_date_crea'; $statut_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'; $statut_mask = 'AND workshop_statut<>\'E\' AND workrep_statut<>\'E\' AND workrepcon_validity=\'Y\''; } $requete = '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\' ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_workshop_content_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_workshop_calendar_search($string, $type) { if ($type == 'PUBLIC') { $statut_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workcal_validity=\'Y\''; } if ($type == 'ADMIN') { $statut_mask = 'AND workshop_statut<>\'E\' AND workcal_validity=\'Y\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_workshop_content_search() * * @param string $string * @param string $type * @return string $requete */ function SQL_workshop_com_search($string, $type) { if ($type == 'PUBLIC') { $statut_mask = 'AND workshop_statut<>\'E\' AND workshop_statut<>\'AA\' AND workshop_statut<>\'D\' AND workcom_statut <>\'E\''; } if ($type == 'ADMIN') { $statut_mask = 'AND workshop_statut<>\'E\' AND workcom_statut <>\'E\''; } $requete = '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) ' . $statut_mask . ' ORDER BY pertinence DESC;'; return $requete; } /** * SQL_yellowpages_search() * * @param string $string * @param string $type * @param string $searchtype * @return string $requete */ function SQL_yellowpages_search($string, $type, $searchtype) { if ($type == 'PUBLIC') { $statut_mask = 'AND yellowp_statut<>\'E\' AND yellowp_statut<>\'AA\' AND yellowp_statut<>\'D\''; } if ($type == 'ADMIN') { $statut_mask = 'AND yellowp_statut<>\'E\''; } $requete = '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') . ' ' . $statut_mask . ' ORDER BY yellowp_name ASC;'; return $requete; } /** * SQL_user_search() * * @param string $string * @param string $type * @param string $searchtype * @return string $requete */ function SQL_user_search($string, $type, $searchtype) { if ($type == 'PUBLIC') { $statut_mask = 'AND user_validity<>\'D\' AND user_validity<>\'N\''; } if ($type == 'ADMIN') { $statut_mask = 'AND user_validity<>\'N\''; } $requete = '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') . ' ' . $statut_mask . ' ORDER BY user_login ASC;'; return $requete; } /** * SQL_theme_search() * * @param string $string * @param string $type * @param string $searchtype * @return string $requete */ function SQL_theme_search($string, $type, $searchtype) { if ($type == 'PUBLIC') { $statut_mask = 'AND theme_statut<>\'D\' AND theme_statut<>\'E\''; } if ($type == 'ADMIN') { $statut_mask = 'AND theme_statut<>\'E\''; } $requete = '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') . ' ' . $statut_mask . ' ORDER BY theme_name ASC;'; return $requete; } /** * SQL_scale_search() * * @param string $string * @param string $type * @param string $searchtype * @return string $requete */ function SQL_scale_search($string, $type, $searchtype) { if ($type == 'PUBLIC') { $statut_mask = 'AND scale_statut<>\'D\' AND scale_statut<>\'E\' AND scale_statut<>\'I\''; } if ($type == 'ADMIN') { $statut_mask = 'AND scale_statut<>\'I\' AND scale_statut<>\'E\''; } $requete = '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') . ' ' . $statut_mask . ' ORDER BY scale_denomination ASC;'; return $requete; } /** * SQL_level_search() * * @param string $string * @param string $type * @param string $searchtype * @return string $requete */ function SQL_level_search($string, $type, $searchtype) { if ($type == 'PUBLIC') { $statut_mask = 'AND level_statut<>\'D\' AND level_statut<>\'E\''; } if ($type == 'ADMIN') { $statut_mask = 'AND level_statut<>\'E\''; } $requete = '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') . ' ' . $statut_mask . ' ORDER BY level_name ASC;'; return $requete; } /** * SQL_ConstructorExpression() * Construit les requĂȘtes simple * * @param string $string * @param string $searchtype * @param string $field_name * @return string $requete */ 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; } ?>