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