/** * Возвращает страницу каталога фрилансеров со всеми необходимыми причиндалами. ВЕДУТСЯ РАБОТЫ! * * @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; }