Ejemplo n.º 1
0
 /**
  * Возвращает страницу каталога фрилансеров со всеми необходимыми причиндалами. ВЕДУТСЯ РАБОТЫ!
  *
  * @param integer prof_id     ид. профессии (раздела каталога). Если 0, то выводим фрилансеров из общего каталога.
  * @param integer uid         ид. юзера, просматривающего каталог.
  * @param integer count       количество всего фрилансеров в данном разделе каталога.
  * @param integer size        количество фрилансеров на данной странице каталога.
  * @param array   works       массив, индексированный ид. фрилансеров, содержащий массив из трех первых работ данного фрилансера в данном разделе.
  * @param integer limit       сколько фрилансеров на одной странице.
  * @param integer offset      OFFSET.
  * @param string  order       тип сортировки (см. использование).
  * @param int     direction   порядок сортировки. 0 -- по убывающей, не 0 -- по возрастающей.
  * @param int     favorite    флаг -- показывать ли только избранных.
  * @param array   filter      массив с параметрами фильтра или NULL -- фильтр не применен.
  *
  * @return array  массив с фрилансерами.
  */
 function old_getCatalog($prof_id, $uid, &$count, &$size, &$works, $limit, $offset, $order = "general", $direction = 0, $favorite = 0, $filter = NULL)
 {
     require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/projects.php";
     global $DB;
     $project_exRates = project_exrates::GetAll();
     $dir_sql = !$direction ? 'DESC' : 'ASC';
     $cost_fm = '';
     $fu_table = self::$fu_table;
     $fprms = self::createCatalogFilterSql($filter, $prof_id);
     if ($fprms == -1) {
         return null;
     }
     list($filter_where, $filter_join) = $fprms;
     if ($filter_where) {
         $filter_where = ' AND ' . $filter_where;
     }
     if (!$filter_where && intval($_SESSION['region_filter_country'])) {
         $filter_where = 'AND s.country IN (' . intval($_SESSION['region_filter_country']) . ')' . (intval($_SESSION['region_filter_city']) != 0 ? 'AND s.city IN (' . intval($_SESSION['region_filter_city']) . ') ' : '');
     }
     $size = 0;
     if ($prof_id) {
         $or_prof = professions::GetProfessionOrigin($prof_id);
         $ord_spec = ', s.its_his_main_spec DESC';
     }
     // Список специализаций для фильтрации в портфолио
     $specs_list = '';
     switch ($order) {
         case "opinions":
             $uc_side = 'INNER';
             $order = ", osum {$dir_sql}, s.rating {$dir_sql}";
             break;
         case "sbr":
             $uc_side = 'INNER';
             $order = ", ssum {$dir_sql}, s.rating {$dir_sql}";
             break;
         case "cost_hour":
             $order = "{$ord_spec}, cost_hour_is_0, cost_fm {$dir_sql}, s.rating {$dir_sql}";
             $orderCf = 'pc.cost_hour';
             $orderCt = 'pc.cost_type_hour';
             break;
         case "cost_month":
             $order = "{$ord_spec}, COALESCE(s.cost_month,0)=0, cost_fm {$dir_sql}, s.rating {$dir_sql}";
             $orderCf = 's.cost_month';
             $orderCt = 's.cost_type_month';
             break;
         case "cost_proj":
             $order = "{$ord_spec}, COALESCE(cost_from,0)=0, cost_fm {$dir_sql}, s.rating {$dir_sql}";
             $orderCf = 'pc.cost_from';
             $orderCt = 'pc.cost_type';
             break;
         case "cost_1000":
             $order = "{$ord_spec}, COALESCE(cost_1000,0)=0, cost_fm {$dir_sql}, s.rating {$dir_sql}";
             $orderCf = 'pc.cost_1000';
             $orderCt = 'pc.cost_type';
             break;
         case "general":
         default:
             $order = "{$ord_spec}, rating_get(s.rating, s.is_pro, s.is_verify, s.is_profi) {$dir_sql}";
             break;
     }
     if ($orderCf && $orderCt) {
         $cost_fm = ",\n            CASE WHEN COALESCE({$orderCt},0) = 0 THEN {$orderCf} * {$project_exRates[24]}\n                 WHEN {$orderCt} = 1 THEN {$orderCf} * {$project_exRates[34]}\n                 WHEN {$orderCt} = 2 THEN {$orderCf} * {$project_exRates[44]}\n                 WHEN {$orderCt} = 3 THEN {$orderCf} * {$project_exRates[14]}\n                 ELSE {$orderCf}\n             END as cost_fm ";
     }
     if (!$uc_side) {
         $uc_side = strpos($filter_where, 'uc.') !== false ? 'INNER' : 'OUTER';
     }
     $uc_cols[$uc_side] = "\n          , uc.ops_emp_plus + uc.ops_frl_plus as sg, uc.ops_emp_minus + uc.ops_frl_minus as sl, uc.ops_emp_null + uc.ops_frl_null as se,\n          zin(uc.paid_advices_cnt + uc.sbr_opi_plus + uc.tu_orders_plus + uc.projects_fb_plus)-zin(uc.sbr_opi_minus + uc.tu_orders_minus + uc.projects_fb_minus) as ssum, \n          zin(uc.ops_emp_plus)-zin(uc.ops_emp_minus) as osum,\n          (uc.paid_advices_cnt + uc.sbr_opi_plus + uc.tu_orders_plus + uc.projects_fb_plus) AS total_opi_plus,\n          (uc.sbr_opi_null) AS total_opi_null,\n          (uc.sbr_opi_minus + uc.tu_orders_minus + uc.projects_fb_minus) AS total_opi_minus,\n          uc.*\n        ";
     $uc_join[$uc_side] = "LEFT JOIN users_counters uc ON uc.user_id = s.uid";
     $fb_cols = "\n        ,   (fb.id > 0)::boolean as is_binded\n        ";
     if ($prof_id) {
         $fb_join = "LEFT JOIN freelancer_binds fb ON fb.user_id = s.uid AND fb.prof_id = '{$or_prof}' AND fb.status = TRUE AND fb.date_stop > NOW()";
         $pc_side = strpos($filter_where, 'pc.') !== false || strpos($cost_fm, 'pc.') !== false ? 'INNER' : 'OUTER';
         $pc_cols[$pc_side] = "\n              , (COALESCE(pc.cost_hour, 0) = 0) as cost_hour_is_0, pc.cost_hour, pc.cost_type_hour, pc.cost_from, pc.cost_type, pc.cost_1000\n              \n            ";
         $pc_join[$pc_side] = "LEFT JOIN portf_choise pc ON pc.prof_id = '{$or_prof}' AND pc.user_id = s.uid";
         $fu = "(\n              SELECT *, true as its_his_main_spec FROM {$fu_table} WHERE spec_orig = '{$or_prof}'\n              UNION ALL\n              SELECT {$fu_table}.*, false FROM {$fu_table} INNER JOIN spec_add_choise sp ON sp.user_id = {$fu_table}.uid AND sp.prof_id = '{$or_prof}' WHERE {$fu_table}.is_pro = true\n              UNION ALL\n              SELECT {$fu_table}.*, false FROM {$fu_table} INNER JOIN spec_paid_choise pc ON pc.user_id = {$fu_table}.uid AND pc.prof_id = '{$or_prof}' AND pc.paid_to > NOW()\n            )";
         $sql = "\n              SELECT s.*,\n                     city.city_name as str_city, country.country_name as str_country, sbr_meta.completed_cnt,\n                     rating_get(s.rating, s.is_pro, s.is_verify, s.is_profi) as t_rating\n                     {$uc_cols['OUTER']}\n                     {$pc_cols['OUTER']}\n                     {$fb_cols}\n                FROM (\n                  SELECT s.* {$cost_fm}, s.cost_hour as frl_cost_hour, s.cost_type_hour as frl_cost_type_hour\n                        {$uc_cols['INNER']}\n                        {$pc_cols['INNER']}\n                    FROM {$fu} as s\n                  {$uc_join['INNER']}\n                  {$pc_join['INNER']}\n                  {$filter_join}\n                   {$fb_join}\n                   WHERE s.is_banned = '0' {$filter_where} AND ( s.cat_show = 't' OR s.is_pro = 'f' ) \n                   ORDER BY (fb.id IS NULL)::boolean ASC, fb.date_start DESC, s.is_pro DESC {$order}, s.uid\n                   LIMIT {$limit} OFFSET {$offset}                 \n                ) as s\n              {$uc_join['OUTER']}\n              {$pc_join['OUTER']}\n              {$fb_join}\n              LEFT JOIN\n              \tcountry \n                  ON country.id = s.country\n              LEFT JOIN\n              \tcity \n                  ON city.id = s.city\n              LEFT JOIN\n              \tsbr_meta \n                  ON sbr_meta.user_id = s.uid\n              ORDER BY (fb.id IS NULL)::boolean ASC, fb.date_start DESC, s.is_pro DESC {$order}, s.uid\n            ";
         $countSql = "SELECT COUNT(s.uid) as count, SUM(s.is_pro::int) as payed FROM {$fu} as s {$filter_join} " . ($filter_where ? $uc_join['INNER'] . ' ' . $pc_join['INNER'] : '') . " WHERE s.is_banned = '0' {$filter_where}";
     } else {
         // Общий каталог.
         // В отличие от разделов тут жесткая связка с posrtf_choise -- это одно из условий нахождения в общем каталоге.
         //переменные для добавления проверки по дополнительным специальностям
         $join_add_spec = '';
         $prof_choise_condition = "pc.prof_id = s.spec_orig";
         $pattern = "#(s.spec_orig\\s+(IN\\s+\\([\\d,]+\\)))#";
         $order_add_spec = "";
         $order_add_spec_field = "";
         if (preg_match($pattern, $filter_where, $m)) {
             $filter_where = preg_replace($pattern, "(\n                \$1  \n                OR (sa.prof_id \$2 AND s.is_pro = 't' ) -- Только у ПРО учитываем ДОП специализацию, тк отменили платные специализации\n                OR (sp.prof_id \$2  )\n                    )", $filter_where);
             $join_add_spec = "LEFT JOIN \n                   spec_add_choise sa\n                     ON  sa.user_id = s.uid\n                     \n                  LEFT JOIN \n                    spec_paid_choise sp\n                     ON  sp.user_id = s.uid";
             $prof_choise_condition = "(\n                    pc.prof_id = s.spec_orig\n                      OR pc.prof_id = sa.prof_id\n                      OR pc.prof_id = sp.prof_id\n                      )";
             //$order_add_spec = "aso DESC, ";
             $order_add_spec_field = "CAST(s.spec_orig {$m[2]} AS integer) AS aso, ";
         }
         $distinct = array_map('trim', array_filter(explode(',', str_replace(array('DESC', 'ASC', 'descr', 'asc'), '', $order))));
         $distinct = $distinct ? implode(", ", $distinct) . "," : "";
         // Сортировка фрилансеров внутри специализаии верхнего уровня по подуровням
         $spec_case_order = '';
         if (isset($m[2]) && $m[2]) {
             $spec_case_order = ", CASE WHEN s.spec_orig {$m[2]} THEN 1 ELSE 2 END";
             $specs_list = "p.prof_id {$m[2]}";
         }
         $fb_on_prof = 0;
         if ($filter['prof']) {
             if (count($filter['prof'][0]) > 0) {
                 $group_ids = array_keys($filter['prof'][0]);
                 $fb_on_prof = $group_ids[0];
             }
         }
         $fb_join = "LEFT JOIN freelancer_binds fb ON fb.user_id = s.uid AND fb.prof_id = {$fb_on_prof} AND fb.is_spec = FALSE AND fb.status = TRUE AND fb.date_stop > NOW()";
         $sql = "\n                    SELECT s.*,\n                     city.city_name as str_city, country.country_name as str_country, sbr_meta.completed_cnt,\n                     p.name as profname, p.is_text,\n                     rating_get(s.rating, s.is_pro, s.is_verify, s.is_profi) as t_rating\n                     {$uc_cols['OUTER']}\n                     {$fb_cols}\n                FROM (\n                \tSELECT s.* FROM (\n                          SELECT  DISTINCT ON (s.is_pro, {$distinct} s.uid) s.*, s.cost_hour as frl_cost_hour, s.cost_type_hour as frl_cost_type_hour, {$order_add_spec_field}\n                                (COALESCE(pc.cost_hour, 0) = 0) as cost_hour_is_0, pc.cost_hour, pc.cost_from, pc.cost_type, pc.cost_1000, pc.cost_type_hour as pc_cost_type_hour\n                                {$cost_fm}\n                                {$uc_cols['INNER']}\n                            FROM {$fu_table} s\n                          {$join_add_spec}\n                          {$uc_join['INNER']}\n                          INNER JOIN\n                            portf_choise pc\n                              ON {$prof_choise_condition}\n                             AND pc.user_id = s.uid\n                          {$filter_join}\n                           WHERE s.is_banned = '0' {$filter_where}  AND ( s.cat_show = 't' OR s.is_pro = 'f' )\n                        ) as s\n                    ) as s \n              {$uc_join['OUTER']}\n              {$fb_join}\n              LEFT JOIN\n                professions p\n                  ON p.id = s.spec\n              LEFT JOIN\n              \tcountry \n                  ON country.id = s.country\n              LEFT JOIN\n              \tsbr_meta \n                  ON sbr_meta.user_id = s.uid\n              LEFT JOIN\n              \tcity \n                  ON city.id = s.city\n               ORDER BY (fb.id IS NULL)::boolean ASC, fb.date_start DESC, s.is_pro DESC {$spec_case_order} {$order}, {$order_add_spec} s.uid\n               LIMIT {$limit} OFFSET {$offset}\n            ";
         $countSql = self::_createMainCountSql($filter_where, $filter_join, $join_add_spec);
     }
     if (!$filter_where) {
         $memBuff = new memBuff();
         $DB->setTimeout(90);
         $frls = $memBuff->getSql($error, $sql, self::CATALOG_MEM_LIFE);
         $DB->setTimeout();
         if ($error || !$frls) {
             return NULL;
         }
         if (!$prof_id && !$offset && !$memBuff->getBWasMqUsed()) {
             professions::RecalcCatalogPositions();
         }
         $DB->setTimeout(90);
         $count_arr = $memBuff->getSql($error, $countSql, self::CATALOG_MEM_LIFE);
         $DB->setTimeout();
         $count = $count_arr[0]['count'];
         $size = sizeof($frls);
         if ($prof_id && !$memBuff->getSqlBWasMqUsed && !$filter_where) {
             professions::UpdateProfessionCount($or_prof, $count, $count_arr[0]['payed']);
         }
     } else {
         $DB->setTimeout(90);
         $frls = $DB->rows($sql);
         $DB->setTimeout();
         $error = $DB->error;
         if ($error || !$frls) {
             return NULL;
         }
         $DB->setTimeout(90);
         $count_arr = $DB->rows($countSql);
         //$memBuff->getSql($error, $countSql, 1800);
         $DB->setTimeout();
         $count = $count_arr[0]['count'];
         $size = sizeof($frls);
         //@todo: это никогда не срабатывает чтоли?
         if ($prof_id && !$filter_where) {
             professions::UpdateProfessionCount($or_prof, $count, $count_arr[0]['payed']);
         }
     }
     foreach ($frls as $row) {
         $frl_ids[] = $row['uid'];
     }
     $join_blocked = ' LEFT JOIN portfolio_blocked pb ON p.id = pb.src_id ';
     $where_blocked = ' AND pb.src_id IS NULL ';
     $sql = "SELECT p.id, p.user_id, p.name, p.descr, p.pict, p.prev_pict, p.show_preview, p.norder, p.prev_type, p.is_video\n               FROM portfolio p\n             INNER JOIN\n               portf_choise pc\n                 ON pc.user_id = p.user_id\n                AND pc.prof_id = p.prof_id \n             " . ($prof_id ? '' : 'INNER JOIN freelancer f ON f.uid = p.user_id') . "\n             {$join_blocked} \n              WHERE p.user_id IN (" . implode(',', $frl_ids) . ")\n        ";
     if ($specs_list) {
         $sql .= " AND {$specs_list}";
     } else {
         $sql .= " AND p.prof_id = " . ($prof_id ? "'{$or_prof}'" : 'f.spec_orig');
     }
     $sql .= " AND p.first3 = true  {$where_blocked} ORDER BY p.user_id, p.norder";
     if (!$filter_where) {
         if ($ret = $memBuff->getSql($error, $sql, 600, true)) {
             foreach ($ret as $row) {
                 $works[$row['user_id']][] = $row;
             }
         }
     } else {
         $ret = $DB->rows($sql);
         if ($ret) {
             foreach ($ret as $row) {
                 $works[$row['user_id']][] = $row;
             }
         }
     }
     return $frls;
 }