/** * Возвращает номер позиции юзера в каталоге * * @param integer $user_id ИД Пользователя * @param integer $user_spec специализация юзера. * @param integer $rating рейтинг юзера. * @param integer $prof_id рейтинг юзера. * @param integer $is_pro Пользователь ПРО или не ПРО. * @return NULL|multitype */ function GetCatalogPosition($user_id, $user_spec, $rating, $prof_id, $is_pro, $not_exact = false) { global $DB; $R = 'rating_get(fu.rating, fu.is_pro, fu.is_verify, fu.is_profi)'; $pos = NULL; $memBuff = new memBuff(); if (!$prof_id) { if (!$not_exact) { $pos_table = professions::GetCatalogPositionsTable(); //такого не должно быть, но на всякий if (!$pos_table) { return NULL; } $out = $DB->row("SELECT 0 as prof_id, 'В общем каталоге' as prof_name, pos FROM {$pos_table} WHERE uid = ?", $user_id); if ($out) { return $out; } } $sql = "SELECT 0 as prof_id, 'В общем каталоге' as prof_name, COUNT(fu.uid) + 1 as pos\n FROM fu\n INNER JOIN\n portf_choise pc\n ON pc.user_id = fu.uid\n\n AND pc.prof_id = fu.spec_orig\n WHERE fu.is_banned = '0'\n AND (fu.is_pro = true " . ($is_pro ? 'AND' : 'OR') . " ({$R} > {$rating} OR ({$R} = {$rating} AND fu.uid < {$user_id})))"; if (!($out = $memBuff->getSql($error, $sql, 300))) { return NULL; } return $out[0]; } $or_prof = $prof_id; $or_prof = professions::GetProfessionOrigin($prof_id); $freelancer_binds = new freelancer_binds(); $date_binded = $freelancer_binds->getBindDate($user_id, $or_prof); $bind_select = $bind_join = $spec_where = ''; if ($date_binded) { $bind_join = "INNER JOIN freelancer_binds ON freelancer_binds.user_id = fu.uid AND freelancer_binds.prof_id = {$or_prof} AND freelancer_binds.is_spec = TRUE AND freelancer_binds.date_stop > NOW() AND freelancer_binds.date_start > '{$date_binded}'"; } else { //Ищем всех закрепленных $bind_select = "SELECT fu.uid, fb.prof_id as spec FROM fu \n LEFT JOIN freelancer_binds fb ON fb.user_id = fu.uid AND fb.date_stop > NOW()\n LEFT JOIN spec_add_choise bsa ON bsa.user_id = fu.uid\n WHERE fu.is_banned = '0' \n AND fb.prof_id={$or_prof} AND (fu.spec_orig = {$or_prof} OR bsa.prof_id = {$or_prof})\n AND fu.uid<>{$user_id} AND fu.cat_show = TRUE\n UNION ALL "; $spec_where = "AND (fu.is_pro = true " . ($is_pro ? 'AND' : 'OR') . " ({$R} > {$rating} OR ({$R} = {$rating} AND fu.uid <> {$user_id})" . ($user_spec == $or_prof ? ") AND fu.spec_orig={$or_prof}" : " OR fu.spec_orig={$or_prof})") . ")"; } $user_where = "\n fu.is_banned = '0' \n AND fu.uid <> {$user_id} \n AND fu.cat_show = TRUE \n AND fu.last_time > now() - '6 months'::interval\n " . $spec_where; $sql = "SELECT \n p.id as prof_id,\n p.name as prof_name,\n COUNT(DISTINCT s.uid) + 1 as pos,\n link\n \n FROM professions p\n LEFT JOIN\n (\n {$bind_select}\n \n SELECT uid, spec_orig as spec\n FROM fu\n {$bind_join}\n WHERE " . $user_where . "\n \n UNION ALL\n \n SELECT fu.uid, sa.prof_id FROM fu\n INNER JOIN spec_add_choise sa ON sa.user_id = fu.uid\n {$bind_join}\n WHERE fu.is_pro = true AND " . $user_where . "\n \n ) AS s ON s.spec = p.id\n \n WHERE p.id = {$or_prof}\n GROUP BY p.id, p.name, link"; $out = $memBuff->getSql($error, $sql, 300, true, freelancer::getCatalogMemTags($or_prof, true)); return $out ? $out[0] : null; }