<?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_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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, project_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_PROJECT . ', to_tsquery(\'default\', \''.$string.'\') as q WHERE idxfti @@ q '. $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, news_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_NEWS . ', to_tsquery(\'default\', \''.$string.'\') as q WHERE idxfti @@ q '. $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, newsletter_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_NEWSLETTER . ', to_tsquery(\'default\', \''.$string.'\') as q WHERE idxfti @@ q '. $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, publi_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_PUBLI . ', to_tsquery(\'default\', \''.$string.'\') as q WHERE idxfti @@ q AND publi_statut<>\'E\' ' . $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, publi_statut AS statut, to_char(rank(pc.idxfti, q)*100, \'99.99\') AS pertinence FROM to_tsquery(\'default\', \''.$string.'\') as q, ' . J_PARTS . ' LEFT OUTER JOIN ' . T_PUBLI_CONT . ' AS pc ON j_parts_id  =  publicon_id LEFT OUTER JOIN  ' . T_PUBLI . ' AS p ON  j_root_id= publi_id WHERE pc.idxfti @@ q AND j_type=\'P\' ' . $statut_mask . ' ORDER BY rank(pc.idxfti, q) 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, to_char(sdii_date_crea, \''.toStringSqlDate().'\') AS date_display, sdii_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_SDI_INFO . ', to_tsquery(\'default\', \''.$string.'\') as q  WHERE idxfti @@ q ' . $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workshop_statut AS statut, to_char(rank(idxfti, q)*100, \'99.99\') AS pertinence FROM ' . T_WORK . ', to_tsquery(\'default\', \''.$string.'\') as q WHERE idxfti @@ q ' . $statut_mask . ' ORDER BY rank(idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workrep_statut AS statut, to_char(rank(wr.idxfti, q)*100, \'99.99\') AS pertinence FROM to_tsquery(\'default\', \''.$string.'\') as q, ' . T_WORK_REP . ' AS wr LEFT OUTER JOIN ' . T_WORK . ' AS w ON workrep_workshop_id  =  workshop_id WHERE wr.idxfti @@ q ' . $statut_mask . ' ORDER BY rank(wr.idxfti, q) 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, to_char(' . $date . ', \''.toStringSqlDate().'\') AS date_display, workrep_statut AS statut, to_char(rank(wrc.idxfti, q)*100, \'99.99\') AS pertinence FROM to_tsquery(\'default\', \''.$string.'\') as q, ' . J_PARTS . ' LEFT OUTER JOIN ' . T_WORK_REP . '  AS wr ON j_root_id  =  workrep_id LEFT OUTER JOIN ' . T_WORK_REP_CONT . ' AS wrc ON  j_parts_id= workrepcon_id LEFT OUTER JOIN ' . T_WORK . ' ON workrep_workshop_id  =  workshop_id WHERE wrc.idxfti @@ q  AND j_type=\'W\' ' . $statut_mask . ' ORDER BY rank(wrc.idxfti, q) 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, to_char(rank(wc.idxfti, q)*100, \'99.99\') AS pertinence FROM to_tsquery(\'default\', \''.$string.'\') as q, ' . T_WORK_CAL . ' AS wc LEFT OUTER JOIN ' . T_WORK . ' AS w ON  workcal_workshop_id  = workshop_id WHERE wc.idxfti @@ q ' . $statut_mask . ' ORDER BY rank(wc.idxfti, q) 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, to_char(rank(wc.idxfti, q)*100, \'99.99\') AS pertinence FROM to_tsquery(\'default\', \''.$string.'\') as q, ' . T_WORK_COM . '  AS wc LEFT OUTER JOIN ' . T_WORK . ' AS w ON  workcom_workshop_id  = workshop_id WHERE wc.idxfti @@ q ' . $statut_mask . ' ORDER BY rank(wc.idxfti, q) 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, to_char(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, to_char(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, to_char(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, to_char(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, to_char(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;
} 

?>
