/DEV/LIB/search.class.php

Large files files are truncated, but you can click here to view the full file

<?php

/*
  20091007-1 : using new table T_AGENCY_DATA_PONDERING_ADP
  20091202-1 (3 sections)	: not using T_LOCALITY_LCT_FR table when select town name
  20091202-2 (4 sections)	: not using SELECT when use of DATEDIFF
  20100216-1 (2 sections)	: not using T_LOCALITY_LCT_FR table when select town name for AGC


 */

// search class
// 97 -> lgm_id
// 98 -> lgc_id
// 99 -> lgn_id
class search_object {

    public $table_name;
    public $table_prfx;
    public $table_locality_name;
    public $table_locality_lang;
    public $has_premium;
    public $has_exclusive;
    public $is_agency_search;
    public $query;
    public $query_agency;
    public $query_premium;
    public $query_count;
    public $select;
    public $select_count;
    public $from;
    public $where;
    public $global_where;
    public $order;
    public $search_request;
    public $search_count;
    public $search_count_premium;
    public $search_results;
    public $search_results_agency;
    public $search_results_premium;
    public $search_results_exclusive;
    public $search_hierarchy_agency;
    public $search_parameters;
    public $loc_keys;
    public $link_trad = true;
    public $PerimProximit = 0;
    public $FirstLocalityProximity = "";
    public $list_lock_proximite;
    public $Proximity_level;
    public $Proximity_lockey;
    public $lat1;
    public $long1;
    public $loc_nameGrp;
    public $isAdvancedSearch = false;
    // vrai si tle type de transaction a été setté dans la requête sql
    // ex. xxx_type_transaction = 1 => pas de besoin de faire ensuite
    // un xxx_type_transaction <> 4 pour exclure les viagers
    public $isTtypeTransactionAlreadySetInSql = false;

    /* Parametres */
    /*
      Ce parametre est d�fini dans /LIB/common.function.php -> $cumul_ad_for_agency_search
      1= cumul sur Ventes et Locations par d�fault
      2= Ventes
      3= Locations
     */
    public $cumul_ad_for_agency_search = 1;

    // *** constructor ***
    function setPerimProximit($val) {
        $this->PerimProximit = $val;
    }

    public function isAdvancedSearch() {
        return $this->isAdvancedSearch;
    }

    // ***
    function getCoord($lockey) {

        global $_global;

        $loctab = explode("_", $lockey);
        $res = array();

        $sql = "SELECT lct_lat,lct_long, lct_name_" . $_global["lang"] . " FROM T_LOCALITY_LCT_" . strtoupper($_global["site"]);

        $this->Proximity_level = $loctab[1];
        $this->Proximity_lockey = $loctab[0];

        switch ($loctab[1]) {
            case 1:
                $sql .=" WHERE lct_id=" . $loctab[0];
                break;
            case 2:
                $sql .=" WHERE lct_id=" . $loctab[0];
                break;
            case 98:
                $sql .=" WHERE lgc_id=" . $loctab[0];
                break;
            case 99:
                $sql .=" WHERE lgn_id=" . $loctab[0];
                break;

            default:
                echo "ERREUR DANS LA CREATION DE LA REQUETE :) (" . $loctab[1] . ")";
                return $res;
                break;
        }



        if ($_global["debug"] == 3 || $_global["debug"] == 1) {
            echo "getCoord()<br />";
            echo $sql . "<br />";
            echo "<hr />";
        }

        $res = pdoquery($sql);

        $this->lat1 = $this->long1 = 0;
        if ($res) {
            $this->lat1 = $res[0]['lct_lat'];
            $this->long1 = $res[0]['lct_long'];
        }

        $this->getLocalityProximit($res[0]['lct_lat'], $res[0]['lct_long']);
    }

    // ***
    function getLocalityProximit($lat, $long) {
        global $_global;


        $this->list_lock_proximite = "";

        //if ($this->Proximity_level <> 1)
        //{
        $sql = "SELECT lct_id FROM (SELECT lct_id,
				(6366*acos(cos(radians($this->lat1))*cos(radians(lct_lat))*cos(radians(lct_long)-radians($this->long1))+sin(radians($this->lat1))*sin(radians(lct_lat))))
			as Proximite from T_LOCALITY_LCT_FR ) AS GLO where Proximite<='$this->PerimProximit'";
        //LOC_LOOKUP_FR

        if ($this->Proximity_lockey == "100") {

            $sql .= " AND lct_id <> 1";
        }

        $sql .= " order by Proximite";

        if ($_global["debug"] == 1 || $_global["debug"] == 3) {
            echo "getLocalityProximit()<br />";
            echo $sql . "<br />";
            echo "<hr />";
        }
        echo $sql . "<br />";

        $res = pdoquery($sql);
        /*
          }
          else
          {
          $res[0]['lct_id'] = $this->Proximity_lockey;
          }
         */


        if ($res) {

            for ($cnt = 0; $cnt < count($res); $cnt++) {
                $this->list_lock_proximite .= $res[$cnt]['lct_id'];
                if ($cnt < count($res) - 1) {
                    $this->list_lock_proximite .= ", ";
                }
            }
        } else {
            $this->list_lock_proximite = 100;
        }
    }

    /* Retrouve le nombre d'annonce par ville par univers pour une agence */

    function get_NbAds_Agency($id_agency = "") {

        GLOBAL $_global;
        // sets from
        $this->setFrom();

        $sql = "SELECT count(*) as count, " . $this->table_prfx . "_default_town, " . $this->table_prfx . "_locality_2 ";
        $sql .= ", l.lct_name_fr ";
        $sql .= "FROM " . $this->from . " , T_LOCALITY_LCT_FR l ";
        $sql .= "WHERE id_agence='$id_agency' AND (" . $this->table_prfx . "_ad_status >= 0) ";
        $sql .= "AND t." . $this->table_prfx . "_locality_2 = l.lct_id ";
        $sql .= "AND (" . $this->table_prfx . "_country = 'FR') ";

        if ($_global['univers'] == 1) {
            $sql .= "AND (" . $this->table_prfx . "_price > 10000) ";
        }
        $sql .= "AND (" . $this->table_prfx . "_property_type_id IN (1, 2, 3, 4, 5, 6, 7, 8)) ";
        //$sql .= "AND ".$this->table_prfx."_default_town <> '' "; /// Attention il arrive qu'il soit vide
        //$sql .= "group by ".$this->table_prfx."_default_town ";
        $sql .= "group by lct_name_fr ";
        $sql .= "order by lct_name_fr, count ";

        //$new_sql = "CALL GET_NB_ADS_PER_TOWN_PER_AGENCY(?)";
        //$sql = "CALL GET_NB_ADS_PER_TOWN_PER_AGENCY('2337D4CF-6368-5E2B-F5FB-D2513A7169B1');";
        //$new_sql = "CALL GET_NB_ADS_PER_TOWN_PER_AGENCY('$id_agency')";

        if ($_global["debug"] == 3) {
            echo "get_NbAds_Agency()<br />";
            echo $sql . "<br />";
            echo "<hr />";
        }

        $res = pdoquery($sql);
        //$res = pdoprocstock($new_sql, $id_agency);

        return $res;
    }

    /* Retrouve les 3 annonces pour une agence avec priorité sur les exclusivité */

    function get_Exclusive_Ads_Agency($id_agency = "") {

        GLOBAL $_global;
        // sets from
        $this->setFrom();

        $sql = "SELECT * ";
        $sql .= "FROM " . $this->from . " ";
        $sql .= "WHERE id_agence='$id_agency' AND (" . $this->table_prfx . "_ad_status >= 0) ";
        //$sql .= "AND (".$this->table_prfx."_country = 'FR') ";
        if ($_global['univers'] == 1) {
            $sql .= "AND (" . $this->table_prfx . "_price > 10000) ";
        }
        $sql .= "AND (" . $this->table_prfx . "_property_type_id IN (1, 2)) ";
        $sql .= "AND " . $this->table_prfx . "_default_town <> '' ";
        $sql .= "AND " . $this->table_prfx . "_photo <> '' ";
        $sql .= "ORDER BY " . $this->table_prfx . "_is_exclusive desc, " . $this->table_prfx . "_update_date ";
        $sql .= " LIMIT 0, 3";

        if ($_global["debug"] == 3) {
            echo "get_Exclusive_Ads_Agency()<br />";
            echo $sql . "<br />";
            echo "<hr />";
        }

        $res = pdoquery($sql);

        return $res;
    }

    /////////////////////////////////////////////////////////////////////////////
    // ***
    function search_object($table_name = TABLE_S_NAME, $table_prefix = TABLE_S_PRFX) {
        $this->setTable($table_name, $table_prefix);

        /* Attention peut générer des requête sur la HomePAge */
        //$this->relief();
    }

    // *** set main properties ***
    function setTable($table_name, $table_prefix) {

        global $_global;

        // sets table data
        $this->table_name = $table_name;
        $this->table_prfx = $table_prefix;

        // sets table locality
        $this->table_locality_name = "T_LOCALITY_LCT_FR";
        $this->table_locality_lang = ""; //"_".$_global["lang"]; // forcage pour FR pas de suffixe
        //$this->table_locality_name = "T_LOCALITY_LCT_FR";
        /*
          if (checkLockFile("memorytable_maintenance") == true)
          {
          $this->table_locality_name = "T_LOCALITY_LCT_FR";
          }
          else
          {
          $this->table_locality_name = (memoryTableCheck() == true ? "LOC_LOOKUP" : "T_LOCALITY_LCT_FR");
          }
         */
    }

    function isGrpAgency($liste) {

        $this->isGrpAgency = true;
        $this->list_agency = $liste;
    }

    function get_Where() {
        $sWhere = implode(" AND ", $this->global_where);
        return $sWhere;
    }

    function getAdsCount($request, $advanced = false) {
        global $_global;

        // sets request
        $this->search_request = $this->setRequest($request);

        $where = array();

        // Ajouter le pays
        $this->setWhere($where, "country");
        // Filtrer le status de l'annonce
        $this->setWhere($where, "ad_status");

        // Ajouter la localité
        if ($this->PerimProximit > 0) {
            $this->setWhere($where, "perimproximit");
        } else {
            $this->setWhere($where, "locality");
        }

        $this->setWhere($where, "price");
        $this->setWhere($where, "area");

        $this->setWhere($where, "ck_nb_rooms");
        $this->setWhere($where, "ck_nb_bedrooms");

        if ($_global['univers'] == 1 || $_global['univers'] == 2 AND $advanced) {
            // Champs de recherche avancée par encore en base !
            $this->setWhere($where, "surf");  // Surface du terrain
            $this->setWhere($where, "dpe");   // Recherche par dpe
            $this->setWhere($where, "fresh"); // Recherche par fraicheur d'annonces
            $this->setWhere($where, "ck_advanced");
        }

        // vacances
        if ($_global["univers"] == 13) {
            $this->setWhere($where, "nb_bedrooms");
            $this->setWhere($where, "nb_people");
            $this->setWhere($where, "type_transaction", 3);
        }
        // location
        elseif ($_global["univers"] == 2) {
            $this->setWhere($where, "type_transaction", 2);
        }
        // colocation
        elseif ($_global["univers"] == 8) {
            $this->setWhere($where, "type_transaction", 8);
        }

        // doit être placé après l'appel à ceci :
        // $this->setWhere($where, "type_transaction", ...);
        $this->setWhere($where, "ck_property_type");

        if ($_REQUEST['an_visuel'] == 1)
            $this->setWhere($where, "photo");

        $this->where = $where;

        //////////////////////////////////////////////////////////

        $this->setFrom(false);

        // builds main query
        $this->query = 'SELECT COUNT(*) AS NUM FROM ' . $this->from;
        $this->query .= ' WHERE ' . implode(' AND ', $this->where);

        if ($_global["debug"] == 3) {
            echo 'getAdsCount()<br />';
            echo $this->query . "<br /><hr />";
        }

        $this->result = pdoquery($this->query);
        return $this->result[0]['NUM'];
    }

    /**
     * Function getAgencyAds : uniquement pour l'univers 9 (liste annonces agence vente + location)
     * 
     * @global type $_global
     * @param type $request
     * @param type $offset
     */
    function getAgencyAds($request, $offset) {

        global $_global;


        //*** TABLE TO USE !***//
        $sSAL_table_name = '';
        $sRNT_table_name = '';

        $sSql = " SELECT ttu_sal_fr, ttu_rnt_fr FROM TP_TABLE_TO_USE_TTU";
        $r = pdoquery($sSql);
        //var_dump($r);die;
        if (sizeof($r) > 0) {
            $sSAL_table_name = $r[0]["ttu_sal_fr"];
            $sRNT_table_name = $r[0]["ttu_rnt_fr"];
        }

        // Si on trouve une des 2 tables en TM on force à T ! Sinon ça plante !
        if (('TM' == substr($sSAL_table_name, 0, 2) && 'TM' != substr($sRNT_table_name, 0, 2)) ||
                ('TM' == substr($sRNT_table_name, 0, 2) && 'TM' != substr($sSAL_table_name, 0, 2))
        ) {
            $sSAL_table_name = 'T_SALESAD_SAL';
            $sRNT_table_name = 'T_RENTAD_RNT';
        }




        $this->search_parameters["univers"] = $_global["univers"]; // = 9        
        $this->is_agency_search = true;
        $this->search_request = $this->setRequest($request);
        $this->setDate();
        $this->search_parameters["offset"] = "" . $offset . "";

        $date_in_where = '';
        if ($_REQUEST['seven_days'] == 1) {
            (!empty($date)) ? "DATEDIFF('$date', sal_first_online_date) AS days_old, " : '' .
                            $date_in_where = "DATEDIFF('" . date("Y-m-d", time() - 7 * 24 * 60 * 60) . "', sal_first_online_date) AS days_old, ";
        }

        $this->query = '';
        //pdoquery('(select id_annonce AS id from T_SALESAD_SAL) union all (select id_annonce AS id from T_RENTAD_RNT) ORDER BY id limit 1');die;
        //*** CONSTRUCTION REQUETE VENTE ***//
        $sReq_SAL = ''; //SQL_CALC_FOUND_ROWS
        $sReq_SAL .= "(SELECT id_annonce, 
                            SAL.sal_ai_id AS ai_id,
                            sal_reference AS reference, 
                            sal_num_mandat AS num_mandat, 
                            sal_is_premium AS is_premium, 
                            sal_is_logo_agency AS is_logo_agency, 
                            sal_is_directory_agency AS is_directory_agency, 
                            sal_is_website_agency AS is_website_agency, 
                            sal_type_transaction AS type_transaction, 
                            id_bien, 
                            '' AS prg_title, 
                            '' AS id_bien_programme, 
                            sal_property_type_id AS property_type_id, 
                            sal_property_type_id_orig AS property_type_id_orig, 
                            sal_property_type AS property_type, 
                            sal_area AS area, 
                            sal_price AS price, 
                            sal_nb_rooms AS nb_rooms, 
                            sal_default_text AS default_text, 
                            sal_is_exclusive AS is_excusive, 
                            sal_currency AS currency, 
                            sal_photo AS photo, 
                            sal_nb_photo AS nb_photo, 
                            sal_logicode AS logicode, 
                            sal_logicode_old AS logicode_old, 
                            sal_post_code AS post_code, 
                            sal_default_town AS default_town, 
                            sal_default_town_2 AS default_town_2, 
                            sal_default_town_1 AS default_town_1, 
                            sal_default_town_0 AS default_town_0, 
                            sal_district_id1 AS district_id1, 
                            sal_district_name1 AS district_name1, 
                            sal_district_id2 AS district_id2, 
                            sal_district_name2 AS district_name2, 
                            sal_district_id3 AS district_id3, 
                            sal_district_name3 AS district_name3, 
                            lgm_id, lgc_id, lgn_id, 
                            sal_country AS country, 
                            sal_default_proximity AS default_proximity, 
                            sal_default_sector AS default_sector, 
                            sal_locality_0 AS locality_0, 
                            sal_locality_1 AS locality_1, 
                            sal_locality_2 AS locality_2, 
                            sal_first_online_date AS first_online_date, 
                            sal_update_date AS update_date, "
                . $date_in_where .
                "sal_pub_code AS pub_code, 
                            id_agence, 
                            id_presto, 
                            sal_name_agency AS name_agency, 
                            sal_post_code_agency AS post_code_agency, 
                            sal_town_agency AS town_agency, 
                            sal_tel_agency AS tel_agency, 
                            sal_fax_agency AS fax_agency, 
                            sal_email_agency AS email_agency, 
                            sal_site_agency AS site_agency, 
                            sal_rcs_agency AS rcs_agency, 
                            sal_logo_agency AS logo_agency, 
                            sal_franchising_agency AS franchising_agency, 
                            sal_is_powersite_agency AS is_powersite_agency,  
                            sal_powersite_url_agency AS powersite_url_agency,  
                            sal_powersite_calltracking_agency AS powersite_calltracking_agency,  
                            sal_advanced_set AS advanced_set, 
                            sal_personnalisable AS personnalisable,
                            1 as univ
                        FROM " . $sSAL_table_name . " SAL 
                        WHERE id_agence = '" . $this->search_request["id_agence"] . "'
                        AND sal_ad_status >= 0
                        AND sal_country = '" . strtoupper($_global["site"]) . "') ";




        //*** CONSTRUCTION REQUETE LOCATION ***//
        $sReq_RNT = ''; //SQL_CALC_FOUND_ROWS
        $sReq_RNT .= "(SELECT id_annonce, 
                            RNT.rnt_ai_id AS ai_id,
                            rnt_reference AS reference, 
                            rnt_num_mandat AS num_mandat, 
                            rnt_is_premium AS is_premium, 
                            rnt_is_logo_agency AS is_logo_agency, 
                            rnt_is_directory_agency AS is_directory_agency, 
                            rnt_is_website_agency AS is_website_agency, 
                            rnt_type_transaction AS type_transaction, 
                            id_bien, 
                            '' AS prg_title, 
                            '' AS id_bien_programme, 
                            rnt_property_type_id AS property_type_id, 
                            rnt_property_type_id_orig AS property_type_id_orig, 
                            rnt_property_type AS property_type, 
                            rnt_area AS area, 
                            rnt_price AS price, 
                            rnt_nb_rooms AS nb_rooms, 
                            rnt_default_text AS default_text, 
                            rnt_is_exclusive AS is_excusive, 
                            rnt_currency AS currency, 
                            rnt_photo AS photo, 
                            rnt_nb_photo AS nb_photo, 
                            rnt_logicode AS logicode, 
                            rnt_logicode_old AS logicode_old, 
                            rnt_post_code AS post_code, 
                            rnt_default_town AS default_town, 
                            rnt_default_town_2 AS default_town_2, 
                            rnt_default_town_1 AS default_town_1, 
                            rnt_default_town_0 AS default_town_0, 
                            rnt_district_id1 AS district_id1, 
                            rnt_district_name1 AS district_name1, 
                            rnt_district_id2 AS district_id2, 
                            rnt_district_name2 AS district_name2, 
                            rnt_district_id3 AS district_id3, 
                            rnt_district_name3 AS district_name3, 
                            lgm_id, lgc_id, lgn_id, 
                            rnt_country AS country, 
                            rnt_default_proximity AS default_proximity, 
                            rnt_default_sector AS default_sector, 
                            rnt_locality_0 AS locality_0, 
                            rnt_locality_1 AS locality_1, 
                            rnt_locality_2 AS locality_2, 
                            rnt_first_online_date AS first_online_date, 
                            rnt_update_date AS update_date, "
                . $date_in_where .
                "rnt_pub_code AS pub_code, 
                            id_agence, 
                            id_presto, 
                            rnt_name_agency AS name_agency, 
                            rnt_post_code_agency AS post_code_agency, 
                            rnt_town_agency AS town_agency, 
                            rnt_tel_agency AS tel_agency, 
                            rnt_fax_agency AS fax_agency, 
                            rnt_email_agency AS email_agency, 
                            rnt_site_agency AS site_agency, 
                            rnt_rcs_agency AS rcs_agency, 
                            rnt_logo_agency AS logo_agency, 
                            rnt_franchising_agency AS franchising_agency, 
                            rnt_is_powersite_agency AS is_powersite_agency,  
                            rnt_powersite_url_agency AS powersite_url_agency,  
                            rnt_powersite_calltracking_agency AS powersite_calltracking_agency,  
                            rnt_advanced_set AS advanced_set, 
                            rnt_personnalisable AS personnalisable,
                            2 as univ
                        FROM " . $sRNT_table_name . " RNT 
                        WHERE id_agence = '" . $this->search_request["id_agence"] . "'
                        AND rnt_ad_status >= 0
                        AND rnt_type_transaction = 2
                        AND rnt_country = '" . strtoupper($_global["site"]) . "') ";


        $this->query = 'SELECT SQL_CALC_FOUND_ROWS * FROM ( ' . $sReq_SAL . 'UNION ALL ' . $sReq_RNT;



        // sets index
        $force_index = array();

        $this->setIndex($force_index, "locality");

        // common index clause
        // force index when we are doing a search on more than one locality
        if (count($force_index) > 1) {
            $this->force_index = $force_index;
        }

        //*** CONSTRUCTION ORDER ***//

        if (isset($this->search_request["order_ads_agency"])) {
            $this->setOrderAgencyAds($this->search_request["order"]);
            if ($this->search_request["order_ads_agency"] == "") {
                $this->search_request["order_ads_agency"] = "date_asc";
            }
            $this->setOrderAgencyAds($this->search_request["order_ads_agency"]);
        } else {
            $this->setOrderAgencyAds($this->search_request["order"]);
        }

        //*** CONSTRUCTION LIMIT ***//

        $this->setLimit("ad", $offset);

        $this->query .= ") AS RESULTVIEW ORDER BY " . implode(", ", $this->order);

        //-- VARIABLE USED IN TEST FILE DISPLAY/resultats_testpoi.php
        $_global["debug_testpoi"] = $this->query;

        $this->query .= " LIMIT " . $this->limit;



        // adds post code
        $_start = microtime(true);

        $this->setPostCodes();
        $this->showTime("set post codes", $_start);

        // executes the query
        $_start = microtime(true);

        if ($_global["debug"] == 3) {
            echo 'getAgencyAds() - 2<br />';
            echo $this->query . "<br />";
            echo "<hr />";
        }

        $this->search_results = pdoquery($this->query);

        $this->showTime("main search", $_start);

        // builds the count query
        $this->query_count = "SELECT FOUND_ROWS() AS NUM";

        // executes the count query
        $_start = microtime(true);

        if ($_global["debug"] == 3) {
            echo 'getAds() - 3<br />';
            echo $this->query_count . "<br />";
            echo "<hr />";
        }

        $this->search_count = pdoquery($this->query_count);
        $this->search_count = $this->search_count[0]["NUM"];
        $this->showTime("main count", $_start);

        // save
        $this->search_parameters["count"] = $this->search_count;

        $this->search_request["count"] = $this->search_count;

        // calculates number of pages
        $this->setPages("ad");

        // sets antenna
        $this->setAntenna();

        // saves search
        $this->saveSearch();
    }

    // *** search ads ***
    /**
     * 
     * @global type $_global
     * @param string $request
     * @param string $offset
     * @param bool $skip_premium NE SERT A RIEN DANS LES PARAMETRES !!!
     * @param bool $simulate
     */
    function getAds($request, $offset, $skip_premium = false, $simulate = false) {

        global $_global;

        // adds universe
        $this->search_parameters["univers"] = $_global["univers"];

        // is agency search ?
        $this->is_agency_search = (isset($request["id_agence"]) ? true : false);



        // sets request
        //echo "<br><b>search.class.php:424</b> : request : "; var_dump($request); echo "<br>\n";
        $this->search_request = $this->setRequest($request);
        //echo "<br><b>search.class.php:426</b> : getRequest : "; var_dump($this->search_request); echo "<br>\n";
        // adds date
        $this->setDate();

        // sets offset
        $this->search_parameters["offset"] = "" . $offset . "";

        // sets select
        $this->setSelect("ad", false);

        // sets where
        $where = array();

        if ($this->isGrpAgency == true) {
            $this->setWhere($where, "grp_id_agence", $this->list_agency);
        }

        $this->setWhere($where, "id_agence", $this->search_request["id_agence"]);
        $this->setWhere($where, "ad_status");

        if (!$this->is_agency_search) {
            if ($this->PerimProximit > 0) {
                $this->setWhere($where, "perimproximit");
            } elseif (!isset($this->search_request['loc_key2'])) {
                $this->setWhere($where, "locality");
            }
        }


        $this->setWhere($where, "country");

        $this->setWhere($where, "price");
        $this->setWhere($where, "area");

        $this->setWhere($where, "ck_nb_rooms");

        if ($_global['univers'] == 1 || $_global['univers'] == 2) {
            // Champs de recherche avancée par encore en base !
            //$this->setWhere($where, "surf");  // Surface du terrain
            $this->setWhere($where, "dpe");   // Recherche par dpe
            $this->setWhere($where, "fresh"); // Recherche par fraicheur d'annonces
            $this->setWhere($where, "ck_nb_bedrooms");
            $this->setWhere($where, "ck_advanced");
        }

        // vacances
        if ($_global["univers"] == 13) {
            $this->setWhere($where, "nb_bedrooms");
            $this->setWhere($where, "nb_people");
            $this->setWhere($where, "type_transaction", 3);
        }
        // location
        elseif ($_global["univers"] == 2) {
            $this->setWhere($where, "type_transaction", 2);
        }
        // colocation
        elseif ($_global["univers"] == 8) {
            $this->setWhere($where, "type_transaction", 8);
        }

        // doit être placé après l'appel à ceci :
        // $this->setWhere($where, "type_transaction", ...);
        $this->setWhere($where, "ck_property_type");

        if ($_REQUEST['an_visuel'] == 1) {
            $this->setWhere($where, "photo");
        }

        if ($_REQUEST['only_old'] == 1) {
            $this->setWhere($where, "only_old");
        }

        if ($_REQUEST['seven_days'] == 1) {
            $date = date("Y-m-d", time() - 7 * 24 * 60 * 60);
            $this->setWhere($where, 'date', $date);
        }

        if ($_global["debug"] == 1) {
            echo "Dump  '_REQUEST'<br />";
            var_dump($_REQUEST);
            echo "<hr />";
        }

        $this->setWhere($where, "with_mmf");

        // common where clause
        $this->where = $where;

        // sets index
        $force_index = array();

        $this->setIndex($force_index, "locality");

        // common index clause
        // force index when we are doing a search on more than one locality
        if (count($force_index) > 1) {
            $this->force_index = $force_index;
        }

        // sets order
        /*
          if (isset($this->search_request["order_price"])){

          $this->setOrder("ad", $this->search_request["order"]);

          if ($this->search_request["order_price"]==""){$this->search_request["order_price"]=1;}
          $this->setOrder("filter_ad", $this->search_request["order_price"], "price");

          }
         */
        if (isset($this->search_request["order_ads_agency"])) {
            $this->setOrder("ad", $this->search_request["order"]);
            if ($this->search_request["order_ads_agency"] == "") {
                $this->search_request["order_ads_agency"] = "date_asc";
            }
            $this->setOrder("filter_ad", $this->search_request["order_ads_agency"]);
        } else {
            $this->setOrder("ad", $this->search_request["order"]);
        }

        // sets from
        // SARA A. 04/09/09 Modified to correct the problem of number of agencies displayed on estate agency directory
        $this->setFrom(false);

        // builds main query
        $this->query = "SELECT SQL_CALC_FOUND_ROWS " . $this->select . " "; //SQL_CALC_FOUND_ROWS
        $this->query .= "FROM " . $this->from . " ";

        $this->query .= "WHERE " . implode(" AND ", $this->where) . " ";

        // W.asbaiti 24/01/2013
        // Dans le cas de recherche multi localité, on remplace la clause or multi loc par union avec requete mono loc

        if (isset($this->search_request['loc_key2'])) {
            // Ajout de la premiere localite
            $valLoc = explode("_", $this->search_request['loc_key1']);
            $this->query .= " AND " . $this->setLocWhere($valLoc, 1);

            // Ajout de la 2eme localite
            $this->query .= ' UNION ';

            $this->query .= "SELECT " . $this->select . " "; //SQL_CALC_FOUND_ROWS
            $this->query .= "FROM " . $this->from . " ";

            $this->query .= "WHERE " . implode(" AND ", $this->where) . " ";

            // Ajout requete pour 2eme localite
            $valLoc = explode("_", $this->search_request['loc_key2']);

            $this->query .= " AND " . $this->setLocWhere($valLoc, 2);

            // Traiteent 3eme localite
            if (isset($this->search_request['loc_key3'])) {
                $this->query .= ' UNION ';

                $this->query .= "SELECT " . $this->select . " "; //SQL_CALC_FOUND_ROWS
                $this->query .= "FROM " . $this->from . " ";

                $this->query .= "WHERE " . implode(" AND ", $this->where) . " ";

                $valLoc = explode("_", $this->search_request['loc_key3']);

                $this->query .= " AND " . $this->setLocWhere($valLoc, 3);
            }

            $this->query = str_replace('SQL_CALC_FOUND_ROWS', '', $this->query);
            $this->query = "SELECT SQL_CALC_FOUND_ROWS * FROM (" . $this->query . ") as search_result ";
        }

        //-- VARIABLE SET BY XAVIER INNAVONG, USED IN TEST FILE DISPLAY/resultats_testpoi.php
        $_global["debug_testpoi_where"] = $this->where;

        // sur l'univers vente 
        // si on est pas sur le tri par défaut (du moins cher au plus cher)
        // on ne met pas en avant les annonces exclu orpi et à la une    

        if ($_COOKIE['fromSort'] == "true") {
            $skip_premium = true;
        } else if ($_global['page'] == "resultats_agence_annonces") {
            $skip_premium = true;
        } else if ($_global['page'] == "resultats_coloc") {
            $skip_premium = true;
        } 
        // Ne pas afficher les annonces ORPI sur page > 1 de PR
        // Garder les annonce premium (annonce a la une)
        if ($offset > 1) {
            $skip_orpi = true;
        } else {
            $skip_orpi = false;
        }


        if ($skip_premium == false) {
            if (isset($this->search_request['loc_key2'])) {
                foreach ($this->search_parameters["loc_keys"] as $sLoc) {
                    $aLocInfo = explode('_', $sLoc);

                    switch ($aLocInfo[1]) {
                        // map group
                        case 97 :
                            $aComplement[] = "lgm_id = " . $aLocInfo[0];
                            break;

                        // code group
                        case 98 :
                            $aComplement[] = "lgc_id = " . $aLocInfo[0];
                            break;

                        // name group
                        case 99 :
                            $aComplement[] = "lgn_id = " . $aLocInfo[0];
                            break;

                        // standard
                        default :
                            $aComplement[] = $this->table_prfx . "_locality_" . $aLocInfo[1] . " = " . $aLocInfo[0];
                            break;
                    }
                }
                $sComplement = ' AND (' . implode(' OR ', $aComplement) . ')';
            }

            // Construction de la requete Premium + Exclusive
            if (CURRENT_EXCLUSIVE_NUM > 0 && !$skip_orpi) {
                $where_exclusive = $where;

                // builds premium query
                $this->setWhere($where, "premium");
                // builds premium query
                $this->setWhere($where_exclusive, "exclusive", CURRENT_EXCLUSIVE_NUM);

                $this->query_premium = '(  SELECT ' . $this->select . '
                                                            FROM ' . $this->from . '
                                                            WHERE ' . implode(' AND ', $where_exclusive) . '
                                                            ' . $sComplement . '
                                                            LIMIT 0,30
                                                            ) UNION (
                                                            SELECT ' . $this->select . '
                                                            FROM ' . $this->from . '
                                                            WHERE ' . implode(' AND ', $where) . '
                                                            ' . $sComplement . '
                                                            LIMIT 0,30
                                                             )';
                // Construction de la requete Premium
            } else {
                // builds premium query
                $this->setWhere($where, "premium");
                $this->query_premium = 'SELECT ' . $this->select . '
                                                        FROM ' . $this->from . '
                                                        WHERE ' . implode(' AND ', $where) . '
                                                        ' . $sComplement . '
                                                        LIMIT 0,30';
            }


            if ($simulate == false) {
                // executes the premium query
                $_delta = microtime(true);

                if ($_global["debug"] == 3) {
                    echo 'getAds() - 1<br />';
                    echo $this->query_premium . "<br />";
                    echo "<hr />";
                }

                $this->search_results_premium = pdoquery($this->query_premium);


                $nb_total = count($this->search_results_premium);


                // Doit-on traiter les annonces exclusives ?
                if (CURRENT_EXCLUSIVE_NUM > 0) {
                    $nb_exclusive = 0;
                    // Trouver le nombre d'annonces exclusives
                    if (sizeof($this->search_results_premium) > 0) {
                        foreach ($this->search_results_premium as $annonce) {
                            if ($annonce[$this->table_prfx . '_is_exclusive'] == CURRENT_EXCLUSIVE_NUM)
                                ++$nb_exclusive;
                            else
                                $break;
                        }
                    }


                    // Calculer le nombre d'annonces premium
                    $nb_premium = $nb_total - $nb_exclusive;

                    if ($nb_exclusive > 0) {
                        $randexclu = rand(0, $nb_exclusive - 1);
                        $this->search_results_exclusive = $this->search_results_premium[$randexclu];

                        //Yo - 2011/08/05 : Exclure cet annonce Orpi dans la liste des résultats pour éviter le doublon
                        //Probleme: Si on fait pas: on a une Orpi(aléatoire) à la première position et une autre orpi dans le résultat
                        //Et si jamais elles sont les mêmes, on a doublon  
                        // W.ASBAITI : inclure ORPI dans la liste de recherche pour gérer la navigation sur la page detail (risque de doublons accepte par market)
                        // $this->query .= "AND id_annonce != '".$this->search_results_exclusive['id_annonce']."'"." ";
                        $this->has_exclusive = true;
                    } else {
                        $this->has_exclusive = false;
                    }




                    if ($_global["debug"] == 3) {
                        echo 'TOTAL: ' . $nb_total . ' / PREMIUM: ' . $nb_premium . ' / EXCLU: ' . $nb_exclusive . '<hr />';
                    }
                } else {
                    $nb_premium = $nb_total;
                    if ($_global["debug"] == 3) {
                        echo 'TOTAL: ' . $nb_total . ' / PREMIUM: ' . $nb_premium . '<hr />';
                    }
                }

                //parametres supplémentaires pour corriger la pagination 
                //Ajout de Mathieu pour ne pas tout détruire...07/12/2012 
                if ($nb_premium > 0) {
                    $this->search_parameters["premiumPresente"] = true;
                } else {
                    $this->search_parameters["premiumPresente"] = false;
                }
                if ($nb_exclusive > 0) {
                    $this->search_parameters["exclusivePresente"] = true;
                } else {
                    $this->search_parameters["exclusivePresente"] = false;
                }


                if ($nb_premium > 0) {
                    $randpremium = rand($nb_exclusive, $nb_total - 1);
                    $this->search_results_premium = array($this->search_results_premium[$randpremium]);
                    $this->search_count_premium = $nb_premium;
                    $this->has_premium = true;
                    $this->search_parameters["premium"] = 1;
                } else {
                    $this->search_count_premium = 0;
                    $this->has_premium = false;
                    $this->search_parameters["premium"] = 0;
                }

                $this->showTime("premium search", $_delta);
            }
        }

        // sets limit
        $this->setLimit("ad", $offset);

        // main query additional clause
        //var_dump($this->order);

        $this->query .= "ORDER BY " . implode(", ", $this->order) . " ";

        //-- VARIABLE SET BY XAVIER INNAVONG, USED IN TEST FILE DISPLAY/resultats_testpoi.php
        $_global["debug_testpoi"] = $this->query;

        // Stockage de la requete pour pagination page detail
        // $this->save_query($this->query);

        $this->query .= "LIMIT " . $this->limit;

        // adds post code
        $_start = microtime(true);

        $this->setPostCodes();
        $this->showTime("set post codes", $_start);

        // executes the query
        $_start = microtime(true);

        if ($simulate == false) {
            if ($_global["debug"] == 3) {
                echo 'getAds() - 2<br />';
                echo $this->query . "<br />";
                echo "<hr />";
            }

            $this->search_results = pdoquery($this->query);

            $this->showTime("main search", $_start);

            // builds the count query
            $this->query_count = "SELECT FOUND_ROWS() AS NUM";

            // executes the count query
            $_start = microtime(true);

            if ($_global["debug"] == 3) {
                echo 'getAds() - 3<br />';
                echo $this->query_count . "<br />";
                echo "<hr />";
            }

            $this->search_count = pdoquery($this->query_count);

            $this->search_count = $this->search_count[0]["NUM"];
            $this->showTime("main count", $_start);

            // save
            $this->search_parameters["count"] = $this->search_count;

            $this->search_request["count"] = $this->search_count;

            // calculates number of pages
            $this->setPages("ad");

            // sets antenna
            $this->setAntenna();

            // saves search
            $this->saveSearch();
        }
    }

    /*
      Apparamment, cette fonction est
      - appelée dans bloc_principal.php (inclus dans V5_home_fr.php)
      - commentée dans google_gadget/gadget_search.php
     */

    // *** search ads by date ***
    function getAdsByDate($request) {
        global $_global;

        // adds universe
        $this->search_parameters["univers"] = $_global["univers"];

        // sets search parameters
        $this->search_request = $this->setRequest($request);

        // adds date
        $this->setDate(true);

        // sets select
        $this->setSelect("ad", false);

        // sets from
        $this->setFrom();

        // sets where
        $where = array();

        $this->setWhere($where, "ad_status");
        $this->setWhere($where, "locality");
        $this->setWhere($where, "country");
        $this->setWhere($where, "ck_property_type");
        $this->setWhere($where, "price");
        $this->setWhere($where, "area");
        $this->setWhere($where, "ck_nb_rooms");
        $this->setWhere($where, "date", $this->search_request["date"]);

        $this->setWhere($where, "with_mmf");

        /* START:RECHERCHE AVANCEE */
        /*
          //$this->setWhere($where, "with_mmf");
          $this->setWhere($where, "recent_ads");

          //if($request["property_type"][0] == 3)
          $this->setWhere($where, "surf");

          $this->setWhere($where, "floor");
          $this->setWhere($where, "elevator");
          $this->setWhere($where, "caretaker");
          $this->setWhere($where, "interphone");
          $this->setWhere($where, "balcony");
          $this->setWhere($where, "terrace");
          $this->setWhere($where, "parquet");
          $this->setWhere($where, "fireside");
          $this->setWhere($where, "closets");
          $this->setWhere($where, "storage");
          $this->setWhere($where, "parking");
          $this->setWhere($where, "garden");
          $this->setWhere($where, "pool");
         */
        /* STOP:RECHERCHE AVANCEE */

        // common where clause
        $this->where = $where;

        // sets order
        $this->setOrder("ad", $this->search_request["order"]);

        // sets limit
        $this->setLimit("ad", 0);

        // sets index
        $force_index = array();
        $this->setIndex($force_index, "locality");

        // common index clause
        // force index when we are doing a search on more than one locality
        if (count($force_index) > 1) {
            $this->force_index = $force_index;
        }

        // builds main query
        $this->query = "SELECT SQL_CALC_FOUND_ROWS 1 AS revisite, " . $this->select . " ";
        $this->query .= "FROM " . $this->from . " ";

        if (isset($this->force_index)) {
            //$this->query .= "FORCE INDEX (".implode(", ", $this->force_index).") ";
        }

        $this->query .= "WHERE " . implode(" AND ", $this->where) . " ";
        $this->query .= "ORDER BY " . implode(", ", $this->order) . " ";
        $this->query .= "LIMIT " . $this->limit;

        // adds post code
        $this->setPostCodes();

        // executes the query
        if ($_global["debug"] == 3) {
            echo "getAdsByDate() - 1<br />";
            echo $this->query . "<br />";
            echo "<hr />";
        }
        $this->search_results = pdoquery($this->query);

        // builds the count query
        $this->query_count = "SELECT FOUND_ROWS() AS NUM";

        // executes the count query
        if ($_global["debug"] == 3) {
            echo "getAdsByDate() - 2<br />";
            echo $this->query_count . "<br />";
            echo "<hr />";
        }

        $this->search_count = pdoquery($this->query_count);
        $this->search_count = $this->search_count[0]["NUM"];

        // save
        $this->search_parameters["count"] = $this->search_count;
        $this->search_request["count"] = $this->search_count;
    }

    /*
      Apparamment, cette fonction est
      - appelée dans ws/soap/server/classes/search.class_from50.php,
      - commentée dans ajax/prod.count.localities.maps.php
     */

    // *** search ads by coordinates ***
    function getAdsByMap($request) {
        global $_global;

        // adds universe
        $this->search_parameters["univers"] = $_global["univers"];

        // is agency search ?
        $this->is_agency_search = (isset($request["id_agence"]) ? true : false);

        // checks if the search form has been activated or if the query has to be pulled from a cookie:
        $this->search_request = $this->setRequest($request);

        // adds date
        $this->setDate();

        // sets select
        $this->select = "COUNT(" . $this->table_prfx . "_ai_id) AS num, lct_id, lct_level, lct_name, lct_lat, lct_long";

        // sets from
        $this->from = $this->table_name . ", " . $this->table_locality_name;

        // sets where
        $where = array();

        $this->setWhere($where, "ad_status");
        $this->setWhere($where, "country");
        $this->setWhere($where, "ck_property_type");
        $this->setWhere($where, "price");
        $this->setWhere($where, "area");
        $this->setWhere($where, "ck_nb_rooms");
        $this->setWhere($where, "id_agence", $this->search_request["id_agence"]);

        $this->setWhere($where, "with_mmf");

        /* START:RECHERCHE AVANCEE */
        /*

          //$this->setWhere($where, "with_mmf");
          $this->setWhere($where, "recent_ads");

          //if($request["property_type"][0] == 3)
          $this->setWhere($where, "surf");

          $this->setWhere($where, "floor");
          $this->setWhere($where, "elevator");
          $this->setWhere($where, "caretaker");
          $this->setWhere($where, "interphone");
          $this->setWhere($where, "balcony");
          $this->setWhere($where, "terrace");
          $this->setWhere($where, "parquet");
          $this->setWhere($where, "fireside");
          $this->setWhere($where, "closets");
          $this->setWhere($where, "storage");
          $this->setWhere($where, "parking");
          $this->setWhere($where, "garden");
          $this->setWhere($where, "pool");

          /* STOP:RECHERCHE AVANCEE */

        // common where clause
        $this->where = $where;

        // builds main query
        $this->query = "SELECT " . $this->select . " ";
        $this->query .= "FROM " . $this->from . " ";
        $this->query .= "WHERE " . implode(" AND ", $this->where) . " ";
        $this->query .= "GROUP BY lct_name, lct_extra_detail, lct_lat, lct_long ";

        // adds post code
        $_start = microtime(true);

        $this->setPostCodes();
        $this->showTime("set post codes", $_start);

        // executes the query
        $_start = microtime(true);

        if ($_global["debug"] == 3) {
            echo "getAdsByMap() - 1<br />";
            echo $this->query . "<br />";
            echo "<hr />";
        }

        $this->search_results = pdoquery($this->query);
        $this->showTime("main search", $_start);

        // count
        if ($this->search_request["offset"] == "") {
            // builds the count query
            $this->query_count = "SELECT FOUND_ROWS() AS NUM";

            // executes the count query
            $_start = microtime(true);

            if ($_global["debug"] == 3) {
                echo "getAdsByMap() - 2<br />";
                echo $this->query_count . "<br />";
                echo "<hr />";
            }

            $this->search_count = pdoquery($this->query_count);
            $this->search_count = $this->search_count[0]["NUM"];
            $this->showTime("main count", $_start);

            // save
            $this->search_parameters["count"] = $this->search_count;
            $this->search_request["count"];

            // calculates number of pages
            $this->setPages("ad");
        }

        // saves search
        $this->saveSearch();
    }

    /**
     * Recuperer la position de l annonce EXCLU parmis toutes les annonces de la recherche
     *
     * @param string $id : l'ID de l'annonce à positionner
     * @return array : Tableau contenant l'annonce suivante et precedente de l'id envoye en param
     */
    function getExcluNav($id) {
        global $_global;

        // Reconstruire les parametres de recherche depuis le cookie LISEARCHHISTORY

        $request = $this->loadSearch();

        if (empty($request))
            return null;
        else {
            unset($request['date']);
            unset($request['timestamp']);
        }
        if ($_global['debug'] == 1) {
            echo 'Requ…