Beispiel #1
0
 /**
  * Формирует SQL-условия по заданному фильтру.
  *
  * @param array $filter    фильтр, см. функцию.
  * @param $prof_id   ид. специализации (если находимся в конкретном разделе каталога).
  * @return array   [where-условие, join-вставки].
  */
 function createCatalogFilterSql($filter, $prof_id = 0)
 {
     if (!$filter) {
         return NULL;
     }
     require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/projects.php";
     require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/teams.php";
     require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/professions.php";
     require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/project_exrates.php";
     global $project_exRates, $DB;
     $filter_join = array();
     $filter_where = array();
     if ($filter['prof'] && !$prof_id) {
         if (count($filter['prof'][0]) > 0) {
             $p1 = professions::getProfIdForGroups(array_keys($filter['prof'][0]), true);
         }
         if (count($filter['prof'][1]) > 0) {
             $p2 = professions::GetProfessionOrigin(implode(",", array_keys($filter['prof'][1])));
         }
         $in_sql = ($p2 ? $p2 : "") . ($p1 && $p2 ? ", " : "") . ($p1 ? $p1 : "");
         if ($in_sql) {
             $filter_where[] = "s.spec_orig IN ({$in_sql})";
             $has_prof_filter = true;
         }
     }
     if ($filter['orig_kwords']) {
         if (!$has_prof_filter && !$prof_id) {
             if ($kprofs = professions::getProfsByKeywords($filter['orig_kwords'])) {
                 $kprofs = professions::GetProfessionOrigin($kprofs);
                 $kf[] = "s.spec_orig IN ({$kprofs})";
             }
             $m = array();
             $e = explode(',', $filter['orig_kwords']);
             foreach ($e as $k => $v) {
                 if ($v = trim($v)) {
                     $m[] = strtolower($v);
                 }
             }
             if ($m) {
                 $kf[] = $DB->parse('s.uid IN (SELECT portf_word.uid FROM words JOIN portf_word ON portf_word.wid=words.id JOIN users ON users.uid=portf_word.uid where lower(name) IN (?l))', $m);
             }
         }
         if (!empty($filter['kword']) && is_array($filter['kword'])) {
             foreach ($filter['kword'] as $k => $v) {
                 $g[$v['group_id']] = $v['group_id'];
                 $w[$v['id']] = $v['id'];
             }
             $kf[] = 's.uid IN (SELECT DISTINCT pw.uid FROM words w JOIN portf_word pw ON pw.wid = w.id WHERE w.group_id IN (' . implode(',', $g) . '))';
         }
         if ($kf) {
             $filter_where[] = '(' . implode(' OR ', $kf) . ') ';
         } else {
             return -1;
         }
     }
     if ($filter['in_fav']) {
         $teams = new teams();
         if ($tt = $teams->teamsFavorites($uid, $error)) {
             foreach ($tt as $t) {
                 $favs[] = $t['uid'];
             }
             if ($favs) {
                 $filter_where[] = $DB->parse('s.uid IN (?l)', $favs);
             }
         }
     }
     $jstr_rating = 'INNER JOIN rating r ON r.user_id = s.uid';
     if ($filter['success_sbr'][0] == 1) {
         $filter_join['rating'] = $jstr_rating;
         for ($i = 1; $i < 4; $i++) {
             if ($filter['success_sbr'][$i] == 1) {
                 $s[] = $i;
             }
         }
         if ($s) {
             $filter_where[] = $DB->parse('r.rank IN (?l)', $s);
         }
         $filter_where[] = 'r.sbr_sum > 0';
     }
     if ($filter['is_preview']) {
         $filter_join['rating'] = $jstr_rating;
         $filter_where[] = 'r.o_wrk_factor_a > 0';
     }
     if ($filter['is_pro']) {
         $filter_where[] = 's.is_pro = true';
     }
     if ($filter['is_verify']) {
         $filter_where[] = 's.is_verify = true';
     }
     if ($filter['only_tu']) {
         $filter_where[] = ' s.uid IN (SELECT tservices.user_id FROM tservices WHERE tservices.active=true AND tservices.deleted=false) ';
     }
     if ($filter['sbr_is_positive']) {
         $filter_where[] = '(uc.paid_advices_cnt + uc.sbr_opi_plus + uc.tu_orders_plus + uc.projects_fb_plus) > 0';
     }
     if ($filter['sbr_not_negative']) {
         $filter_where[] = '(uc.sbr_opi_minus + uc.tu_orders_minus + uc.projects_fb_minus) = 0';
     }
     if ($filter['opi_is_positive']) {
         $filter_where[] = '(uc.ops_emp_plus + uc.ops_frl_plus) > 0';
     }
     if ($filter['opi_not_negative']) {
         $filter_where[] = '(uc.ops_emp_minus + uc.ops_frl_minus) = 0';
     }
     if ($filter['only_free']) {
         $filter_where[] = 's.status_type = 0';
     }
     if ($filter['in_office']) {
         $filter_where[] = 's.in_office = true';
     }
     if ($filter['wo_cost'] == 'f') {
         // hh
         $filter_where[] = 's.cost_month <> 0';
     }
     if ($filter['cost']) {
         foreach ($filter['cost'] as $k => $val) {
             if ($val['cost_from'] || $val['cost_to']) {
                 switch ($val['type_date']) {
                     default:
                     case 4:
                         if ($prof_id) {
                             $cf = 'pc.cost_hour';
                             $ct = 'pc.cost_type_hour';
                         } else {
                             $cf = 's.cost_hour';
                             $ct = 's.cost_type_hour';
                         }
                         break;
                     case 3:
                         $cf = 'pc.cost_from';
                         $ct = 'pc.cost_type';
                         break;
                     case 1:
                         $cf = 's.cost_month';
                         $ct = 's.cost_type_month';
                         break;
                     case 2:
                         $cf = 'pc.cost_1000';
                         $ct = 'pc.cost_type';
                         break;
                 }
                 $cr = (int) $val['cost_type'];
                 $cex = array(freelancer::USD => project_exrates::USD, freelancer::EUR => project_exrates::EUR, freelancer::RUR => project_exrates::RUR, freelancer::FM => project_exrates::FM);
                 if (($cost_from = (double) $val['cost_from']) < 0) {
                     $cost_from = 0;
                 }
                 if (($cost_to = (double) $val['cost_to']) < 0) {
                     $cost_to = 0;
                 }
                 if ($cost_to < $cost_from && $cost_to != 0) {
                     $cost_to = $cost_from;
                 }
                 if ($cost_to || $cost_from) {
                     $cost_sql = "";
                     for ($i = 0; $i < 4; $i++) {
                         $exfr = round($cost_from * $project_exRates[$cex[$cr] . $cex[$i]], 4);
                         $exto = round($cost_to * $project_exRates[$cex[$cr] . $cex[$i]], 4);
                         $cost_sql .= ($i ? ' OR ' : '') . "(COALESCE({$ct},0) = {$i} AND {$cf} >= {$exfr}" . ($cost_to ? " AND {$cf} <= {$exto}" : '') . ')';
                     }
                     if ($filter['wo_cost'] == 't') {
                         // hh
                         $cost_sql .= " OR {$cf} = 0";
                     }
                     $a_cost_sql[] = $cost_sql;
                 }
             }
         }
         if ($a_cost_sql) {
             $filter_where[] = '(' . implode(" OR ", $a_cost_sql) . ')';
         }
     }
     if ($filter['exp'][0] > 0 || $filter['exp'][1] > 0) {
         if ($filter['exp'][1] == 0 && $filter['exp'][0] > 0) {
             $filter_where[] = $DB->parse("s.exp >= ?", $filter['exp'][0]);
         } else {
             if ($filter['exp'][1] > 0 && $filter['exp'][0] == 0) {
                 $filter_where[] = $DB->parse("s.exp <= ?", $filter['exp'][1]);
             } else {
                 $filter_where[] = $DB->parse("s.exp >= ? AND s.exp <= ?", $filter['exp'][0], $filter['exp'][1]);
             }
         }
     }
     /* Не используется?
        if($filter['login']) {
            $filter_where[] = "(s.uname ILIKE '" . trim($filter['login']) . "' OR s.usurname ILIKE '" . trim($filter['login']) . "' OR s.login ILIKE '" . trim($filter['login']) . "')";
        }*/
     if ($filter['wo_age'] == 'f') {
         // hh
         $filter_where[] = "COALESCE(s.birthday, 'epoch') > '1910-01-01'";
     }
     if ($filter['age'][1] > 0 || $filter['age'][0] > 0) {
         $age_from = $filter['age'][0];
         $age_to = $filter['age'][1];
         $ccond = "AND s.birthday > '1910-01-01'";
         if ($filter['wo_age'] == 't') {
             // hh
             $ccond = "OR COALESCE(s.birthday, 'epoch') <= '1910-01-01'";
         }
         if ($age_to == 0 && $age_from > 0) {
             $filter_where[] = $DB->parse("extract('year' from age(s.birthday)) >= ?i {$ccond}", $age_from);
         } elseif ($age_to > 0 && $age_from == 0) {
             $filter_where[] = $DB->parse("extract('year' from age(s.birthday)) <= ?i {$ccond}", $age_to);
         } else {
             $filter_where[] = $DB->parse("(extract('year' from age(s.birthday)) BETWEEN ?i AND ?i) {$ccond}", $age_from, $age_to);
         }
     }
     $userIsAuth = $_SESSION["uid"];
     if ($cc = $filter['country']) {
         if (is_array($cc)) {
             $cc = implode(',', $cc);
         }
         $filter_where[] = "s.country IN ({$cc})";
         if (!$userIsAuth) {
             $filter_where[] = "position ('\"country\"' IN s.info_for_reg) = 0";
             /*смотрим в сериализованном массиве полей, которые не надо показывать неавторизованым, есть ли ключ country*/
         }
     }
     if ($cc = $filter['city']) {
         if (is_array($cc)) {
             $cc = implode(',', $cc);
         }
         $filter_where[] = "s.city IN ({$cc})";
         if (!$userIsAuth) {
             $filter_where[] = "position ('\"city\"' IN s.info_for_reg) = 0";
         }
     }
     if ($filter['sex']) {
         $fsex = $filter['sex'] == 2 ? 'TRUE' : 'FALSE';
         $filter_where[] = "s.sex = {$fsex}";
     }
     $filter_where = implode(' AND ', $filter_where);
     $filter_join = implode(' ', $filter_join);
     return array($filter_where, $filter_join);
 }