/**
* Выбрать ТУ для списка пользователей
* по указанному количеству на каждого
*
* @param type $uids
* @param type $limit
* @param type $expire
* @param type $group
* @return type
*/
public function getListByUids($uids, $limit = 3, $expire = 0, $group = false)
{
$sql = $this->db()->parse("\n SELECT \n DISTINCT ON (q.id) \n q.*,\n f.fname AS file\n FROM (\n SELECT \n s.id AS id, \n s.user_id,\n s.title AS title, \n s.price AS price,\n s.videos AS videos,\n s.total_feedbacks AS total_feedbacks,\n row_number() OVER(PARTITION BY s.user_id ORDER BY s.id DESC) AS rownum\n FROM {$this->TABLE} AS s \n LEFT JOIN {$this->TABLE_DEBT} AS od ON od.user_id = s.user_id \n LEFT JOIN {$this->TABLE_BLOCKED} AS sb ON sb.src_id = s.id \n WHERE \n s.user_id IN(?l) \n AND s.deleted = FALSE \n AND s.active = TRUE \n AND sb.src_id IS NULL\n AND (od.id IS NULL OR od.date >= NOW())\n ) AS q\n LEFT JOIN {$this->TABLE_FILES} AS f ON f.src_id = q.id AND f.small = 4\n WHERE q.rownum <= ?i\n ORDER BY q.id DESC, f.preview DESC, f.id \n ", $uids, $limit);
$memBuff = new memBuff();
$result = $memBuff->getSql($error, $sql, $expire, true, $group);
return $result;
}
/**
* Взять последнюю новость
*
* @return array Новость
*/
function GetLastNews()
{
$sql = "SELECT post_date, header FROM news ORDER BY post_date DESC, id DESC LIMIT 1";
$memBuff = new memBuff();
$headers = $memBuff->getSql($error, $sql, 1800);
if ($error) {
$error = parse_db_error($error);
} else {
$ret = $headers[0];
}
return $ret;
}
/**
* Подсчитывает и возвращает количество проектов по закладкам для постранички в ленте,
* записыавет в memcache на 30 минут.
* Если $uid != null также на последних страницах проверяется количество
* забаненных проектов пользователя и суммируется с общим.
*
* @param integer $kind Тип проектов (-1=5=Все проекты; 2=Конкурсы; 4=В офис; 6=Только для про)
* @param integer $page Номер текущей страницы
* @param integer $uid ИД работодателя, если есть.
* @return integer
*/
function getProjectsCount($kind = 0, $page = 1, $uid = null, $is_moder = null)
{
//$_uid = intval($uid);
$_where = '';
//$_where = !$is_moder?" AND (p.status = ".self::STATE_PUBLIC." OR e.uid = {$_uid})":'';
//$_where = !$is_moder?"AND NOT(p.payed = 0 AND p.kind = ".self::KIND_VACANCY." AND p.state = ".self::STATE_MOVED_TO_VACANCY.")":'';
$sql = "SELECT\n SUM(pro_only::int) as pro_only,\n SUM((kind = 1)::int) as prj_fl,\n SUM((kind IN (2,7))::int) as prj_kon,\n SUM((kind = 4)::int) as prj_off\n FROM projects p\n LEFT JOIN projects_blocked pb ON pb.project_id = p.id\n INNER JOIN employer e ON e.uid = p.user_id AND e.is_banned = '0'\n WHERE \n " . (get_uid(false) ? '' : 'COALESCE(p.hide, false) = false AND ') . "p.closed = false AND pb.project_id IS NULL AND p.kind <> 9 {$_where}";
$memBuff = new memBuff();
$a_num_prjs = $memBuff->getSql($error, $sql, 1800);
if (!$a_num_prjs) {
return 0;
}
$res = $a_num_prjs[0];
$cnt = 0;
switch ($kind) {
case 6:
//только про
$cnt = $res['pro_only'];
$where_kind = ' AND pro_only = true AND kind <> 9';
break;
case 1:
//фриланс
$cnt = $res['prj_fl'];
$where_kind = ' AND kind = 1 ';
break;
case 2:
//конкурсы
$cnt = $res['prj_kon'];
$where_kind = ' AND kind IN (2,7) ';
break;
case 4:
//офис
$cnt = $res['prj_off'];
$where_kind = ' AND kind = 4 ';
break;
default:
//все
$cnt = $res['prj_fl'] + $res['prj_kon'] + $res['prj_off'];
$where_kind = ' AND kind <> 9';
}
//нужно уточнить счетчик на последних страницах, и добавить
//к общему кол-во забаненных проектов работодателя
if ($uid && $page + 10 >= ceil($cnt / projects::PAGE_SIZE)) {
$sql = "SELECT COUNT(*) as cnt FROM projects_blocked pb\n INNER JOIN projects p ON p.id = pb.project_id\n WHERE p.user_id = {$uid} {$where_kind}";
$res_cnt = $memBuff->getSql($error, $sql, 300);
if ($res_cnt) {
$ban = $res_cnt[0];
$cnt += (int) $ban['cnt'];
}
}
return $cnt;
}
/**
* Статистика по забаненым пользователям.
*
* @return array статистика
*/
public function GetBannedStat()
{
$sql = "SELECT COUNT(*) AS cnt FROM users WHERE is_banned = B'1' AND ban_where = 0";
$memBuff = new memBuff();
$row = $memBuff->getSql($error, $sql, 180);
$site = $row[0]['cnt'];
$sql = 'SELECT COUNT(*) AS cnt FROM users WHERE ban_where = 1';
$memBuff = new memBuff();
$row = $memBuff->getSql($error, $sql, 180);
$blogs = $row[0]['cnt'];
$sql = "SELECT COUNT(*) AS cnt FROM users WHERE warn > 0 AND is_banned = B'0' AND ban_where = 0";
$memBuff = new memBuff();
$row = $memBuff->getSql($error, $sql, 180);
$warns = $row[0]['cnt'];
return array('all' => $site + $blogs + $warns, 'site' => $site, 'blogs' => $blogs, 'warns' => $warns);
}
/**
* Получает обменные курсы
*
* @param boolean $cache получить из базы или из кэша
* @return array массив курсов валют
*/
function GetAll($cache = true)
{
$sql = "SELECT * FROM project_exrates";
if ($cache) {
$memBuff = new memBuff();
$ret = $memBuff->getSql($error, $sql, 600);
} else {
global $DB;
$res = $DB->squery($sql);
$ret = pg_fetch_all($res);
}
if ($ret) {
foreach ($ret as $ikey => $val) {
$out[$val['id']] = $val['val'];
}
}
return $out;
}
/**
* Выборка записи из таблицы ban_promo_types по типу страницы
* setTypeByPage.
*
* @param type $target
*/
public function setTypeByPage($target = '0|0')
{
global $DB;
$sql = "SELECT * FROM ban_promo_types\n WHERE now()::date BETWEEN from_date AND to_date AND deleted = 'f'\n AND (is_pro & B'{$this->is_pro}' = '{$this->is_pro}') AND (is_role & B'{$this->is_role}' = '{$this->is_role}')\n ORDER BY (page_target = ?) DESC, is_activity DESC, advertising DESC, from_date ASC \n LIMIT 1\n ";
$query = $DB->parse($sql, $target);
$memBuff = new memBuff();
$result = $memBuff->getSql($error, $query, 600, true, 'banner_promo');
if ($result) {
$this->info = $result[0];
if (strpos($this->info['name_img'], '/users') === 0) {
$this->info['name_img'] = WDCPREFIX . $this->info['name_img'];
} else {
$this->info['name_img'] = $this->info['name_img'];
}
$this->type_banner = $this->info['id'];
}
return $this->type_banner;
}
/**
* Возвращает кол-во заблокированных проектов
*
* @return integer
*/
function NumsBlockedCommunes()
{
$sql = "SELECT COUNT(*) AS cnt FROM commune_blocked JOIN commune ON commune.id = commune_blocked.commune_id";
$memBuff = new memBuff();
$row = $memBuff->getSql($error, $sql, 180);
return (int) $row[0]['cnt'];
}
/**
* Возвращает информацию по заданной HH-валюте
*
* @param string $code код валюты (USD, UAH и т.д.).
* @return array
*/
function getHHCurrency($code)
{
global $DB;
$sql = "SELECT * FROM hh_currency WHERE code ILIKE '{$code}'";
$memBuff = new memBuff();
if ($rows = $memBuff->getSql($error, $sql, self::MEM_LIFE)) {
$ret = $rows[0];
}
return $ret;
}
/**
* Возвращает имя актуальной таблицы с позициями фрилансеров
* в общем каталоге
*
* @param boolean $clear_cache Удалить или нет запись из кеша
* @return string Имя актуальной таблицы
*/
function GetCatalogPositionsTable($clear_cache = false)
{
$memBuff = new memBuff();
$sql = "SELECT value FROM settings WHERE module = 'professions' AND variable = 'pos_table' LIMIT 1";
if ($clear_cache) {
$memBuff->delete(md5($sql));
}
if (!($pos_table = $memBuff->getSql($error, $sql, 3600))) {
return NULL;
}
$pos_table = $pos_table[0]['value'];
return $pos_table;
}
/**
* Получаем блоки (элементы) ленты по пользователю
* Получаем единый массив данных, необходимых для вывода элементов ленты. Разделением между топиком сообщества и работой портфолио
* может служить, например, член массива .portfolio_id, у сообществ он NULL. В запросе pf.post_date может быть NULL, так как
* поле portfolio.post_date заведено совсем недавно.
*
* @param integer $user_id id пользователя
* @param integer $my_team_checked истина, если стоит галка "Моя команда" или "Рекоммендованые мной".
* @param integer $all_profs_checked истина, если стоит галка "Все разделы".
* @param mixed $prof_groups строка идентификаторов групп профессий, разделенных запятыми.
* @param mixed $communes строка идентификаторов сообществ, разделенных запятыми.
* @param integer $offset SQL OFFSET
* @param string $limit SQL LIMIT
* @param integer &$count=-1 количество работ, если пользователь определил в настройках разделы или стоит галка "Все разделы". Количество тем сообществ, считается отдельно
* @param mixed $blog_groups строка идентификаторов разделов блогов, разделенных запятыми.
*
* @return array массив тем в случае успеха, 0 в случае неудачи
*/
function GetLentaItems($user_id, $my_team_checked = 0, $all_profs_checked = 0, $prof_groups = NULL, $communes = NULL, $offset = 0, $limit = 'ALL', &$count = -1, $blog_groups = NULL)
{
global $DB;
if ($my_team_checked) {
$DBProxy = new DB('plproxy');
$sql = "SELECT uid FROM teams_get(?i);";
$quids_team = $DBProxy->rows($sql, $user_id);
$uids_team = array();
$uids_team[] = 0;
if ($quids_team) {
foreach ($quids_team as $uid_team) {
$uids_team[] = $uid_team['uid'];
}
}
}
$sql = "\n SELECT \n li.*,\n u.is_banned::int as user_is_banned,\n u.is_pro as user_is_pro,\n u.is_profi AS user_is_profi,\n u.is_team as user_is_team,\n u.is_pro_test as user_is_pro_test,\n u.role as user_role,\n u.login as user_login,\n u.photo as user_photo,\n u.usurname as user_usurname,\n u.uname as user_uname,\n u.reg_date, u.is_chuck, u.is_verify\n FROM\n (" . (!$all_profs_checked && !$prof_groups ? '' : "\n SELECT\n 1 as item_type,\n pf.user_id as user_id, \n pf.post_date as post_time,\n NULL as id,\n 'PF-' || pf.id as key,\n NULL::integer as parent_id,\n NULL::integer as theme_id,\n NULL as msgtext,\n NULL as title,\n NULL::integer as deleted_id,\n NULL::integer as modified_id,\n NULL as created_time,\n NULL as deleted_time,\n NULL as modified_time,\n NULL as file_exists,\n NULL::integer as commune_id,\n NULL as a_count,\n NULL as is_blocked,\n NULL as last_activity,\n NULL::integer as commune_group_id,\n NULL as commune_group_name,\n NULL as commune_name,\n NULL::integer as commune_author_id,\n NULL as member_warn_count,\n NULL::integer as member_id,\n NULL as member_is_banned,\n NULL as member_is_admin,\n NULL as last_viewed_time,\n NULL as modified_login,\n NULL as modified_usurname,\n NULL as modified_uname,\n NULL as modified_by_commune_admin,\n pf.id as portfolio_id,\n pf.name as name,\n pf.link as link,\n pf.descr as descr,\n pf.pict as pict,\n pf.prev_pict as prev_pict,\n pf.prof_id as prof_id,\n p.name as prof_name,\n p.id as prof_id, \n NULL as question,\n NULL::boolean as poll_closed,\n\t\t\t\t\t NULL::boolean as poll_multiple,\n NULL::bigint as poll_votes,\n NULL::boolean as close_comments,\n NULL as is_private,\n NULL::smallint as current_count,\nNULL as dfl_title,\nNULL as dfl_description,\nNULL as dfl_type,\n0 as dfl_jury_id,\nNULL as dfl_image,\n0 as dfl_type_id,\nNULL as yt_link,\n0 as count_comments,\n0 as status_comments,\n/*pf.moderator_status,*/\npfb.admin AS work_is_blocked\n FROM\n (\n SELECT DISTINCT COALESCE(m.main_prof, p.id) as id\n FROM prof_group pg\n INNER JOIN\n professions p\n ON p.prof_group = pg.id\n LEFT JOIN\n mirrored_professions m\n ON m.mirror_prof = p.id\n " . (!$prof_groups ? '' : " WHERE pg.id IN ({$prof_groups})") . "\n ) as px\n INNER JOIN\n portf_choise pc\n ON pc.prof_id = px.id\n AND pc.user_id <> {$user_id}\n INNER JOIN\n professions p\n ON p.id = COALESCE(pc.prof_origin, pc.prof_id)\n INNER JOIN\n portfolio pf\n ON pf.prof_id = pc.prof_id\n AND pf.user_id = pc.user_id\n AND pf.post_date > now() - '1 month'::interval\n /*AND (pf.moderator_status != 0 OR pf.moderator_status IS NULL)*/\n " . (!$my_team_checked ? '' : " \n AND pf.user_id IN (?l)\n ") . "LEFT JOIN\n portfolio_blocked AS pfb\n ON pfb.src_id = pf.id" . ($communes || $blog_groups ? " UNION ALL" : '') . "\n ") . (!$communes ? '' : "\n SELECT \n 2 as item_type,\n ms.user_id as user_id,\n ms.created_time as post_time,\n ms.id as id,\n 'CM-' || ms.id as key,\n ms.parent_id as parent_id,\n ms.theme_id as theme_id,\n ms.msgtext as msgtext,\n ms.title as title,\n ms.deleted_id as deleted_id,\n ms.modified_id as modified_id,\n ms.created_time as created_time,\n ms.deleted_time as deleted_time,\n ms.modified_time as modified_time,\n ms.cnt_files as file_exists,\n t.commune_id as commune_id,\n t.a_count as a_count,\n (CASE WHEN ctb.blocked_time IS NOT NULL THEN ctb.blocked_time\n WHEN t.blocked_time IS NOT NULL THEN t.blocked_time\n WHEN ms.deleted_time IS NOT NULL THEN ms.deleted_time\n ELSE NULL END) as is_blocked,\n t.last_activity as last_activity,\n cg.id as commune_group_id,\n cg.name as commune_group_name,\n cm.name as commune_name,\n cm.author_id as commune_author_id,\n m.warn_count as member_warn_count,\n m.id as member_id,\n m.is_banned::int as member_is_banned,\n m.is_admin::int as member_is_admin,\n um.last_viewed_time as last_viewed_time,\n umm.login as modified_login,\n umm.usurname as modified_usurname,\n umm.uname as modified_uname,\n (am.user_id IS NOT NULL)::int as modified_by_commune_admin,\n NULL as portfolio_id,\n NULL as name,\n NULL as link,\n NULL as descr,\n NULL as pict,\n NULL as prev_pict,\n NULL as prof_id,\n NULL as prof_name,\n NULL as prof_id, \n \t\t\t\t cp.question as question,\n \t\t\t\t cp.closed as poll_closed,\n\t\t\t\t\t cp.multiple as poll_multiple,\n \t\t\t\t cv._cnt as poll_votes,\n t.close_comments as closed_comments,\n t.is_private as is_private,\n um.current_count as current_count,\nNULL as dfl_title,\nNULL as dfl_description,\nNULL as dfl_type,\n0 as dfl_jury_id,\nNULL as dfl_image,\n0 as dfl_type_id,\nms.youtube_link as yt_link,\nt.a_count-1 as count_comments,\num.current_count as status_comments,\n/*ms.moderator_status, */\nNULL AS work_is_blocked\n FROM commune_themes t\n INNER JOIN\n commune cm\n ON cm.id = t.commune_id\n INNER JOIN\n commune_groups cg\n ON cg.id = cm.group_id\n INNER JOIN\n commune_messages ms\n ON ms.theme_id = t.id\n AND ms.parent_id IS NULL\n AND ms.created_time > now() - '1 month'::interval\n /*AND (ms.moderator_status != 0 OR ms.moderator_status IS NULL)*/\n LEFT JOIN\n commune_members m\n ON m.user_id = ms.user_id\n AND m.commune_id = t.commune_id\n LEFT JOIN\n users umm\n ON umm.uid = ms.modified_id\n LEFT JOIN\n commune_members am\n ON am.user_id = umm.uid\n AND am.commune_id = cm.id\n AND am.is_admin = true\n\t\t\tLEFT JOIN\n\t\t\t commune_poll cp\n\t\t\t ON cp.theme_id = ms.theme_id\n LEFT JOIN\n commune_theme_blocked ctb\n ON ctb.theme_id = t.id\n\t\t\tLEFT JOIN\n\t\t\t (SELECT theme_id, COUNT(answer_id) AS _cnt FROM commune_poll_votes WHERE user_id = {$user_id} GROUP BY theme_id) cv\n\t\t\t ON cv.theme_id = ms.theme_id\n LEFT JOIN\n commune_users_messages um\n ON um.message_id = ms.id\n AND um.user_id = {$user_id}\n WHERE t.commune_id IN ({$communes})\n " . ($blog_groups ? " UNION ALL" : '')) . (!$blog_groups ? '' : "\nSELECT \n 4 as item_type,\n bm.fromuser_id as user_id,\n bm.post_time as post_time,\n bm.id as id,\n 'BL-' || bm.id as key,\n NULL as parent_id,\n b.thread_id as theme_id,\n bm.msgtext as msgtext,\n bm.title as title,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bg.id as commune_group_id,\n bg.t_name as commune_group_name,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bp.question as question,\n bp.closed as poll_closed,\n\t\t\t\t\tbp.multiple as poll_multiple,\n bv._cnt as poll_votes,\n b.close_comments as closed_comments,\n NULL,\n w.status AS current_count,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nbm.yt_link as yt_link,\n(b.messages_cnt-1) as count_comments ,\nw.status as status_comments,\n/*bm.moderator_status, */\nNULL AS work_is_blocked \nFROM blogs_themes b \nINNER JOIN blogs_msgs_" . date('Y') . " bm\n ON bm.thread_id = b.thread_id\n AND bm.reply_to IS NULL\n AND (b.is_private='f' OR bm.fromuser_id={$user_id})\n AND bm.post_time > now() - '1 month'::interval\n /*AND (bm.moderator_status != 0 OR bm.moderator_status IS NULL)*/\nLEFT JOIN blogs_blocked ON blogs_blocked.thread_id = b.thread_id\nINNER JOIN blogs_groups bg\n ON bg.id = b.id_gr\nLEFT JOIN blogs_poll bp\n ON bp.thread_id = bm.thread_id\nLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = {$user_id} GROUP BY thread_id) bv\n ON bv.thread_id = bm.thread_id\nLEFT JOIN (SELECT * FROM blogs_themes_watch WHERE user_id = '{$user_id}') AS w ON (theme_id=b.thread_id)\n\n \nWHERE b.id_gr IN ({$blog_groups}) AND bm.deleted IS NULL AND blogs_blocked.thread_id IS NULL\n\n " . (date('n') < 2 ? " \n \n UNION ALL\n \n SELECT \n 4 as item_type,\n bm.fromuser_id as user_id,\n bm.post_time as post_time,\n bm.id as id,\n 'BL-' || bm.id as key,\n NULL as parent_id,\n b.thread_id as theme_id,\n bm.msgtext as msgtext,\n bm.title as title,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bg.id as commune_group_id,\n bg.t_name as commune_group_name,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bp.question as question,\n bp.closed as poll_closed,\n\t\t\t\t\tbp.multiple as poll_multiple,\n bv._cnt as poll_votes,\n b.close_comments as closed_comments,\n NULL,\n w.status AS current_count,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nbm.yt_link as yt_link,\n(b.messages_cnt-1) as count_comments ,\nw.status as status_comments,\n/*bm.moderator_status, */\nNULL AS work_is_blocked \nFROM blogs_themes b \nINNER JOIN blogs_msgs_" . (date('Y') - 1) . " bm\n ON bm.thread_id = b.thread_id\n AND bm.reply_to IS NULL\n AND (b.is_private='f' OR bm.fromuser_id={$user_id})\n AND bm.post_time > now() - '1 month'::interval\n /*AND (bm.moderator_status != 0 OR bm.moderator_status IS NULL)*/\nLEFT JOIN blogs_blocked ON blogs_blocked.thread_id = b.thread_id\nINNER JOIN blogs_groups bg\n ON bg.id = b.id_gr\nLEFT JOIN blogs_poll bp\n ON bp.thread_id = bm.thread_id\nLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = {$user_id} GROUP BY thread_id) bv\n ON bv.thread_id = bm.thread_id\nLEFT JOIN (SELECT * FROM blogs_themes_watch WHERE user_id = '{$user_id}') AS w ON (theme_id=b.thread_id)\n\n \nWHERE b.id_gr IN ({$blog_groups}) AND bm.deleted IS NULL AND blogs_blocked.thread_id IS NULL\n\n " : '')) . "\n\n ) AS li\n INNER JOIN\n users u\n ON u.uid = li.user_id\n AND u.is_banned = '0'\n \n\n ORDER BY li.post_time DESC" . ($all_profs_checked || $prof_groups ? ', li.portfolio_id DESC' : '') . "\n LIMIT {$limit} OFFSET {$offset}\n ";
$res = $DB->rows($sql, $uids_team);
if ($res) {
foreach ($res as $row) {
$ret[$row['key']] = $row;
if ($row['item_type'] == 2) {
$ids2[] = $row['id'];
}
if ($row['item_type'] == 4) {
$ids4[] = $row['id'];
}
// if($row['id']) $ids[] = $row['id'];
}
if ($ids2) {
//$sql = "SELECT file.*, commune_attach.cid, commune_attach.small FROM commune_attach JOIN file_commune as file ON file.id = commune_attach.fid WHERE commune_attach.cid IN (".implode(", ", $ids2).")";
//$res2 = $DB->rows($sql);
$res2 = CFile::selectFilesBySrc(commune::FILE_TABLE, $ids2);
foreach ($res2 as $row) {
$ret['CM-' . $row['src_id']]['attach'][] = $row;
}
}
if ($ids4) {
$sql = 'SELECT * FROM file_blogs WHERE src_id IN (?l)';
$res2 = $DB->rows($sql, $ids4);
foreach ($res2 as $row) {
$ret['BL-' . $row['src_id']]['attach'][] = $row;
}
}
$count = 0;
if ($all_profs_checked || $prof_groups || $communes || $blog_groups) {
$sql = "SELECT SUM(items.count) as count FROM (" . (!$all_profs_checked && !$prof_groups ? '' : "\n SELECT\n COUNT(pf.id) as count\n FROM (\n SELECT DISTINCT COALESCE(m.main_prof, p.id) as id\n FROM prof_group pg\n INNER JOIN professions p ON p.prof_group = pg.id\n LEFT JOIN mirrored_professions m ON m.mirror_prof = p.id\n " . (!$prof_groups ? '' : " WHERE pg.id IN ({$prof_groups})") . "\n ) as px\n INNER JOIN portf_choise pc\n ON pc.prof_id = px.id\n AND pc.user_id <> {$user_id}\n INNER JOIN professions p\n ON p.id = COALESCE(pc.prof_origin, pc.prof_id)\n INNER JOIN portfolio pf\n ON pf.prof_id = pc.prof_id\n AND pf.user_id = pc.user_id\n AND pf.post_date > now() - '1 month'::interval" . (!$my_team_checked ? '' : " \n AND pf.user_id IN (" . implode(',', $uids_team) . ")\n ") . ($communes || $blog_groups ? " UNION ALL" : '')) . (!$communes ? '' : "\n SELECT \n COUNT(ms.id) as count\n FROM commune_themes t\n INNER JOIN commune_messages ms \n ON ms.theme_id = t.id\n AND ms.parent_id IS NULL\n AND ms.created_time > now() - '1 month'::interval\n INNER JOIN users u\n ON u.uid = ms.user_id\n AND u.is_banned = '0'\n WHERE t.commune_id IN ({$communes})\n " . ($blog_groups ? " UNION ALL" : '')) . (!$blog_groups ? '' : "\n SELECT COUNT(1) as count\n FROM blogs_themes b \n WHERE b.id_gr IN ({$blog_groups}) AND b.deleted IS NULL AND b.is_blocked = false\n AND (b.is_private='f' OR b.fromuser_id={$user_id})\n AND b.post_time > now() - '1 month'::interval\n ") . ") as items";
/*
$sql =
"
SELECT COUNT(pf.id) as count
FROM prof_group pg
INNER JOIN
professions p
ON p.prof_group = pg.id
INNER JOIN
portf_choise pc
ON pc.prof_id = p.id
AND pc.user_id <> {$user_id}
INNER JOIN
portfolio pf
ON pf.prof_id = pc.prof_id
AND pf.user_id = pc.user_id
INNER JOIN
freelancer f
ON f.uid = pf.user_id
AND f.is_banned = '0'".
(
!$my_team_checked
? ''
: " INNER JOIN
teams tm
ON tm.target_id = f.uid
AND tm.user_id = {$user_id}"
).
" WHERE pf.post_date > now() - '1 month'::interval "
.
( !$prof_groups ? '' : " AND pg.id IN ({$prof_groups})" );
*/
$memBuff = new memBuff();
$count_arr = $memBuff->getSql($error, $sql, 120);
if (!$error) {
$count = $count_arr[0]['count'];
}
}
return $ret;
}
return 0;
}
/**
* Загрузить весь список опкодов.
*
* @return bool
*/
public static function getAllOpCodes($refresh = false)
{
if (!empty(self::$_cache_data) && !$refresh) {
return self::$_cache_data;
}
$error = null;
$memBuff = new memBuff();
$data = $memBuff->getSql($error, '
SELECT * FROM ' . get_class($this) . '
', self::OP_CODES_MEMCACHE_LIFE, true, self::OP_CODES_MEMCACHE_TAG);
if ($data && !$error) {
foreach ($data as $el) {
self::$_cache_data[$el['id']] = $el;
}
return self::$_cache_data;
}
return false;
}
/**
* Возвращает реквизиты физ. и юр. лица
*
* @return array
*/
function getReqvFields()
{
if (!sbr_meta::$reqv_fields) {
$memBuff = new memBuff();
$mlife = 1800;
$rows = $memBuff->getSql($err, 'SELECT * FROM sbr_reqv_fields ORDER BY pos, name ', $mlife);
$ro = $memBuff->getSql($err, 'SELECT * FROM reqv_ordered LIMIT 1', $mlife);
$bp = $memBuff->getSql($err, 'SELECT * FROM bank_payments LIMIT 1', $mlife);
$fre = '/^_(\\d)_(.*)$/';
// $1:тип лица (физ. или юр.), $2:имя поля
if ($rows) {
foreach ($rows as $row) {
if (!preg_match($fre, $row['idname'], $m)) {
continue;
}
$ft = $m[1];
$nm = $m[2];
$row['bill_bound'] = $ft == sbr::FT_JURI && @array_key_exists($nm, $ro[0]) || $ft == sbr::FT_PHYS && @array_key_exists($nm, $bp[0]);
//Переводим битарный тип в массив форм резиденства
//которые поддреживает данное поле
$rez_type_new = $row['rez_type_new'];
$row['rez_type_new'] = array();
if (bindec($rez_type_new) > 0) {
for ($idx = 0; $idx < strlen($rez_type_new); $idx++) {
if (substr($rez_type_new, $idx, 1) == 1) {
$row['rez_type_new'][] = $idx + 1;
//сопостовление sbr::RT_ ...
}
}
}
sbr_meta::$reqv_fields[$ft][$nm] = $row;
}
}
}
return sbr_meta::$reqv_fields;
}
/**
* Метод может использоваться в двух случаях:
* 1. Для подсчета количества пользователей и стомости рассылки исходя из заданного фильтра.
* 2. Расчитать (без дополнительно расчета каталога и городов) количество пользователей и стоимость + сохранить список пользователей в mass_sending_users
* @param integer $uid uid пользователя совершаемого рассылку
* @param array $params массив с данными фильтра фрилансеров
* @param commit integer если не 0, то сохранит всех найденых пользователей для рассылки $commit иначе просто расчет
* @return array результат расчета в виде
* array('count', 'cost', 'pro'=>array('count', 'cost'), locations=>array(array('city', 'country', 'count', 'cost')), professions=>array(array('group', 'profession', 'count', 'cost')))
*
*/
public function Calculate($uid, array $params, $commit = 0)
{
global $DB;
$result = array('count' => 0, 'cost' => 0, 'pro' => array('count' => 0, 'cost' => 0), 'professions' => array(), 'locations' => array());
$cost = $this->GetTariff($this->tariff_id);
$memBuff = new memBuff();
$memBuffGroup = 'massending_calc';
$ow = $cw = "";
$op = $cp = array();
$tmp = array();
$profs = $profsgr = array();
$jn = $wh = $whc = $whl = "";
$wh .= " AND u.subscr & B'0000000000001000' = B'0000000000001000'";
//----------------------------------------------------------------------
// у меня в избранных
if (!empty($params['favorites'])) {
$dbProxy = new DB('plproxy');
$targets = $dbProxy->col("SELECT target_id FROM teams(?)", $uid);
if ($targets) {
$wh .= $dbProxy->parse(" AND u.uid IN (?l)", $targets);
}
}
//----------------------------------------------------------------------
// только свободные
if (!empty($params['free'])) {
$wh .= " AND u.status_type = 0";
}
//----------------------------------------------------------------------
// с верифицированым аккаунтом
if (!empty($params['opi_is_verify'])) {
$wh .= ' AND (u.is_verify = true)';
}
//----------------------------------------------------------------------
// с примерами работ
//if ($params['portfolio']) $wh .= " AND EXISTS(SELECT 1 FROM portfolio WHERE user_id = u.uid)";
if (!empty($params['portfolio'])) {
$jn = " INNER JOIN rating r ON r.user_id = u.uid AND r.o_wrk_factor_a > 0";
}
//----------------------------------------------------------------------
// с успешными сбр и фрилансреами 1/2/3 разрядов
if (!empty($params['sbr'])) {
if (!$jn) {
$jn = " INNER JOIN rating r ON u.uid = r.user_id";
}
if (!empty($params['discharge3'])) {
$discharge = 3;
} else {
if (!empty($params['discharge2'])) {
$discharge = 2;
} else {
if (!empty($params['discharge1'])) {
$discharge = 1;
} else {
$discharge = 0;
}
}
}
if ($discharge) {
$jn .= " AND r.rank >= {$discharge}";
} else {
$jn .= " AND r.sbr_count > 0 ";
}
}
//----------------------------------------------------------------------
$uc_where = '';
// с положительными рекомендациями
//if (!empty($params['sbr_is_positive'])) $uc_where .= " AND uc.sbr_opi_plus > 0";
// без негативных рекомендация
//if (!empty($params['sbr_not_negative'])) $uc_where .= " AND uc.sbr_opi_minus = 0";
// с положительными отзывами
if (!empty($params['opi_is_positive'])) {
$uc_where .= ' AND ((uc.ops_emp_plus + uc.ops_frl_plus + uc.sbr_opi_plus) > 0)';
}
// без негативных отзывами
if (!empty($params['opi_not_negative'])) {
$uc_where .= ' AND ((uc.ops_emp_minus + uc.ops_frl_minus + uc.sbr_opi_minus) = 0 OR uc.user_id IS NULL)';
}
if ($uc_where) {
$wh .= $uc_where;
$jn .= "LEFT JOIN users_counters uc ON uc.user_id = u.uid";
}
//----------------------------------------------------------------------
// ищет работу в офисе
if ($params['inoffice']) {
$wh .= " AND u.in_office = 't'";
}
//----------------------------------------------------------------------
// стоимость
if (!empty($params['cost_from']) && is_array($params['cost_from']) || !empty($params['cost_to']) && is_array($params['cost_to'])) {
$exrates = project_exrates::GetAll();
$cex = array(2, 3, 4, 1);
$tmp = '';
foreach ($params['cost_from'] as $i => $val) {
if (!$params['cost_from'][$i] && !$params['cost_to'][$i]) {
continue;
}
$type = isset($params['cost_type'][$i]) && in_array($params['cost_type'][$i], array(0, 1, 2, 3)) ? $params['cost_type'][$i] : 0;
if (isset($params['cost_period'][$i]) && $params['cost_period'][$i] == 'month') {
$ct = 'u.cost_type_month';
$cc = 'u.cost_month';
} else {
$ct = 'u.cost_type_hour';
$cc = 'u.cost_hour';
}
$cost_from = floatval(str_replace(array(' ', ','), array('', '.'), $params['cost_from'][$i])) * $exrates[$cex[$type] . '1'];
$cost_to = floatval(str_replace(array(' ', ','), array('', '.'), $params['cost_to'][$i])) * $exrates[$cex[$type] . '1'];
$s = "(CASE WHEN {$ct} = 0 THEN {$exrates[$cex[0] . '1']} WHEN {$ct} = 1 THEN {$exrates[$cex[1] . '1']} WHEN {$ct} = 2 THEN {$exrates[$cex[2] . '1']} WHEN {$ct} = 3 THEN {$exrates[$cex[3] . '1']} END)";
if ($cost_to > $cost_from || !$cost_to || !$cost_from) {
$s = ($cost_from ? " AND ({$cc} * {$s}) >= {$cost_from} " : "") . ($cost_to ? " AND ({$cc} * {$s}) <= {$cost_to}" : "");
} else {
$s = ($cost_from ? " AND ({$cc} * {$s}) <= {$cost_from} " : "") . ($cost_to ? " AND ({$cc} * {$s}) >= {$cost_to}" : "");
}
$tmp .= ' OR (' . substr($s, 5) . ')';
}
if ($tmp) {
$wh .= ' AND (' . substr($tmp, 4) . ')';
}
}
//----------------------------------------------------------------------
// опыт в годах
if (intval($params['expire_from']) || intval($params['expire_to'])) {
$f = intval($params['expire_from']);
$t = intval($params['expire_to']);
if ($f && $t && $f > $t) {
list($f, $t) = array($t, $f);
}
//if ($f) $wh .= " AND ((regexp_replace(u.exp, '^([0-9]+)?.*', E'\\\\1')) <> '' AND (regexp_replace(u.exp, '^([0-9]+)?.*', E'\\\\1'))::int >= $f)";
//if ($t) $wh .= " AND ((regexp_replace(u.exp, '^([0-9]+)?.*', E'\\\\1')) <> '' AND (regexp_replace(u.exp, '^([0-9]+)?.*', E'\\\\1'))::int <= $t)";
if ($f) {
$wh .= " AND u.exp >= {$f}";
}
if ($t) {
$wh .= " AND u.exp <= {$t}";
}
}
//----------------------------------------------------------------------
// только pro
if (!empty($params['is_pro'])) {
$wh .= " AND u.is_pro = 't'";
}
//----------------------------------------------------------------------
// меторасположение
if (!empty($params['locations']) && is_array($params['locations'])) {
$tmp = '';
$tmpc = array();
foreach ($params['locations'] as $location) {
if (preg_match("/^([0-9]{1,10})\\:([0-9]{1,10})\$/", $location, $o)) {
if ($o[2]) {
if (empty($tmpc["{$o[1]}:{$o[2]}"])) {
$tmpc["{$o[1]}:{$o[2]}"] = 1;
} else {
continue;
}
$cw .= " OR (u.country = {$o[1]} AND u.city = {$o[2]})";
$tmp .= " OR (u.country = {$o[1]} AND u.city = {$o[2]})";
} else {
if (empty($tmpc["{$o[1]}:0"])) {
$tmpc["{$o[1]}:0"] = 1;
} else {
continue;
}
$ow .= " OR (u.country = {$o[1]})";
$tmp .= " OR (u.country = {$o[1]})";
}
}
}
if ($tmp) {
$whl = " AND (" . substr($tmp, 4) . ")";
}
}
//----------------------------------------------------------------------
// разделы в каталоге
if (!empty($params['professions']) && is_array($params['professions'])) {
$tmpc = array();
foreach ($params['professions'] as $profession) {
if (preg_match("/^([0-9]{1,10})\\:([0-9]{1,10})\$/", $profession, $o)) {
if ($o[2]) {
if (empty($tmpc["{$o[1]}:{$o[2]}"])) {
$tmpc["{$o[1]}:{$o[2]}"] = 1;
} else {
continue;
}
$cp[$o[2]] = array($o[1], $o[2]);
} else {
if (empty($tmpc["{$o[1]}:0"])) {
$tmpc["{$o[1]}:0"] = 1;
} else {
continue;
}
$op[] = $o[1];
}
}
}
}
// подготовка данных, если указаны разделы каталога
if ($op || $cp) {
// если группа и раздел
if ($cp) {
$tmp = array();
foreach ($cp as $k => $v) {
if (in_array($v[0], $op)) {
unset($cp[$k]);
} else {
$tmp[] = $v[1];
}
}
if (!empty($cp)) {
$res = $DB->query('SELECT main_prof, mirror_prof FROM mirrored_professions WHERE mirror_prof IN (?l)', $tmp);
while ($row = pg_fetch_assoc($res)) {
$profs[] = $row['main_prof'];
$cp[$row['mirror_prof']][] = $row['main_prof'];
}
foreach ($cp as $v) {
if (empty($v[2])) {
$profs[] = $v[1];
}
}
}
}
// если указаны только группы разделов
if ($op) {
$res = $DB->query('SELECT prof_group, id, main_prof FROM professions LEFT JOIN mirrored_professions ON mirror_prof = id WHERE prof_group IN (?l)', $op);
$tmp = array();
while ($row = pg_fetch_assoc($res)) {
$profsgr[] = $row['id'];
$tmp[$row['prof_group']] = 1;
if ($row['main_prof']) {
$profsgr[] = $row['main_prof'];
}
}
$op = array_keys($tmp);
}
$in_ids = implode(array_unique(array_merge($profs, $profsgr)), ',');
//@todo: здесь лучше бы избавится от подзапроса, но походу никак
//JOIN не быстрее да и другие результаты выдает
$whc = " AND (u.spec_orig IN ({$in_ids})\n OR (u.is_pro = TRUE AND EXISTS(\n SELECT 1 FROM\n spec_add_choise\n WHERE user_id = u.uid AND prof_id IN ({$in_ids})\n\t\t\t)))";
} else {
$whc = " AND u.spec_orig IS NOT NULL AND u.spec_orig > 0";
}
//----------------------------------------------------------------------
// если указано меторасположение, то оно обробатывается своими запросами
if (($cw || $ow) && !$commit && $this->isCalcMethond('locations')) {
$locations = array();
$haveTheir = array();
// страны без городов
if ($ow) {
$sql = "SELECT country, is_pro, COUNT(*) AS cnt FROM freelancer u {$jn} WHERE is_banned = '0' {$whc}{$wh} AND (" . substr($ow, 4) . ") GROUP BY country, is_pro";
if (!($rows = $memBuff->getSql($error, $sql, 600, false, $memBuffGroup))) {
$rows = array();
}
foreach ($rows as $row) {
$c = "{$row['country']}:0";
if (empty($locations[$c])) {
$locations[$c] = array('country' => $row['country'], 'city' => 0, 'cost' => 0, 'cost' => 0, 'pro' => array('cost' => 0, 'count' => 0));
}
$locations[$c]['count'] += $row['cnt'];
if ($row['is_pro'] == 't') {
$locations[$c]['cost'] += $row['cnt'] * $cost['pro'];
$locations[$c]['pro']['count'] += $row['cnt'];
$locations[$c]['pro']['cost'] += $row['cnt'] * $cost['pro'];
} else {
$locations[$c]['cost'] += $row['cnt'] * $cost['no_pro'];
}
$haveTheir[$row['country']] = TRUE;
}
}
// страны с городами
if ($cw) {
$sql = "SELECT country, city, is_pro, COUNT(*) AS cnt FROM freelancer u {$jn} WHERE is_banned = '0' {$whc}{$wh} AND (" . substr($cw, 4) . ") GROUP BY country, city, is_pro";
if (!($rows = $memBuff->getSql($error, $sql, 600, false, $memBuffGroup))) {
$rows = array();
}
foreach ($rows as $row) {
$c = "{$row['country']}:{$row['city']}";
if (empty($locations[$c])) {
$locations[$c] = array('country' => $row['country'], 'city' => $row['city'], 'cost' => 0, 'cost' => 0, 'pro' => array('cost' => 0, 'count' => 0));
}
if (!empty($haveTheir[$row['country']])) {
$locations[$c]['no'] = 1;
}
$locations[$c]['count'] += $row['cnt'];
if ($row['is_pro'] == 't') {
$locations[$c]['cost'] += $row['cnt'] * $cost['pro'];
$locations[$c]['pro']['count'] += $row['cnt'];
$locations[$c]['pro']['cost'] += $row['cnt'] * $cost['pro'];
} else {
$locations[$c]['cost'] += $row['cnt'] * $cost['no_pro'];
}
}
}
foreach ($locations as $k => $v) {
$v['cost'] = $v['cost'];
if (empty($v['no'])) {
$result['count'] += $v['count'];
$result['cost'] += $v['cost'];
$result['pro']['count'] += $v['pro']['count'];
$result['pro']['cost'] += $v['pro']['cost'];
}
$result['locations'][] = $v;
}
}
//----------------------------------------------------------------------
// если указаны разделы каталога, то для них дополнительные запросы
if (($op || $cp) && !$commit && $this->isCalcMethond('professions')) {
$professions = array();
if ($op) {
$profsgr = array_unique($profsgr);
if ($profsgr) {
$in_profsgr = implode($profsgr, ',');
$sql = "SELECT prof_group, is_pro, COUNT(uid) AS cnt\n FROM (\n SELECT s.prof_group, is_pro, uid\n FROM freelancer u\n INNER JOIN professions s ON s.id = u.spec_orig\n {$jn}\n WHERE \n u.is_banned = '0' \n AND u.spec_orig IN({$in_profsgr}) \n {$wh}\n {$whl}\n\n UNION\n\n SELECT s.prof_group, is_pro, uid\n FROM spec_add_choise sp\n INNER JOIN freelancer u ON sp.user_id = u.uid\n INNER JOIN professions s ON s.id = sp.prof_id\n {$jn}\n WHERE \n u.is_banned = '0' \n AND u.is_pro = TRUE \n AND sp.prof_id IN({$in_profsgr}) \n {$wh}\n {$whl}\n ) s\n GROUP BY prof_group, is_pro";
if (!($rows = $memBuff->getSql($error, $sql, 600, false, $memBuffGroup))) {
$rows = array();
}
foreach ($rows as $row) {
if (empty($professions["{$row['prof_group']}:0"])) {
$professions["{$row['prof_group']}:0"] = array('id' => 0, 'group' => $row['prof_group'], 'count' => $row['cnt'], 'cost' => $row['cnt'] * ($row['is_pro'] == 't' ? $cost['pro'] : $cost['no_pro']));
} else {
$professions["{$row['prof_group']}:0"]['count'] += $row['cnt'];
$professions["{$row['prof_group']}:0"]['cost'] += $row['cnt'] * ($row['is_pro'] == 't' ? $cost['pro'] : $cost['no_pro']);
}
}
}
}
if ($cp) {
$in_profs = implode($profs, ',');
$sql = "SELECT spec, is_pro, SUM(cnt) AS cnt\n\t\t\t\t\tFROM (\n\t\t\t\t\t\tSELECT spec_orig AS spec, is_pro, COUNT(uid) AS cnt\n\t\t\t\t\t\tFROM freelancer u\n\t\t\t\t\t\t{$jn}\n\t\t\t\t\t\tWHERE spec_orig IN ({$in_profs}) AND u.is_banned = '0' {$wh}{$whl}\n\t\t\t\t\t\tGROUP BY spec_orig, is_pro\n \n\t\t\t\t\t\tUNION ALL\n \n\t\t\t\t\t\tSELECT prof_id AS spec, is_pro, COUNT(uid) AS cnt\n\t\t\t\t\t\tFROM spec_add_choise sp\n\t\t\t\t\t\tINNER JOIN freelancer u ON sp.user_id = u.uid AND u.is_banned = '0' AND u.is_pro = TRUE\n\t\t\t\t\t\t{$jn}\n\t\t\t\t\t\tWHERE prof_id IN ({$in_profs}) {$wh}{$whl}\n\t\t\t\t\t\tGROUP BY prof_id, is_pro\n\t\t\t\t\t) s\n\t\t\t\t\tGROUP BY spec, is_pro\n\t\t\t\t";
if (!($rows = $memBuff->getSql($error, $sql, 600, false, $memBuffGroup))) {
$rows = array();
}
foreach ($rows as $row) {
foreach ($cp as $k => $v) {
if ($row['spec'] == $v[1] || !empty($v[2]) && $row['spec'] == $v[2]) {
if (empty($professions["{$v[0]}:{$v[1]}"])) {
$professions["{$v[0]}:{$v[1]}"] = array('id' => $v[1], 'group' => $v[0], 'count' => $row['cnt'], 'cost' => $row['cnt'] * ($row['is_pro'] == 't' ? $cost['pro'] : $cost['no_pro']));
} else {
$professions["{$v[0]}:{$v[1]}"]['count'] += $row['cnt'];
$professions["{$v[0]}:{$v[1]}"]['cost'] += $row['cnt'] * ($row['is_pro'] == 't' ? $cost['pro'] : $cost['no_pro']);
}
}
}
}
}
foreach ($professions as $k => $v) {
$v['cost'] = $v['cost'];
$result['professions'][] = $v;
}
}
//----------------------------------------------------------------------
// подсчет общего количества, если еще не было подсета при обратки месторасположения
if (!($cw || $ow) || $commit || !$this->isCalcMethond('locations')) {
$sql = "SELECT is_pro, COUNT(*) AS cnt FROM freelancer AS u {$jn} WHERE is_banned = '0' {$whc}{$whl}{$wh} GROUP BY is_pro";
if (!($rows = $memBuff->getSql($error, $sql, 600, false, $memBuffGroup))) {
$rows = array();
}
$result['count'] = 0;
$result['cost'] = 0;
$result['pro'] = array('count' => 0, 'cost' => 0);
foreach ($rows as $row) {
$result['count'] += $row['cnt'];
if ($row['is_pro'] == 't') {
$result['pro']['count'] += $row['cnt'];
$result['pro']['cost'] += $row['cnt'] * $cost['pro'];
$result['cost'] += $row['cnt'] * $cost['pro'];
} else {
$result['cost'] += $row['cnt'] * $cost['no_pro'];
}
}
}
//----------------------------------------------------------------------
if ($commit) {
$sql = "\n\t\t\t\tINSERT INTO mass_sending_users\n\t\t\t\tSELECT {$commit}, uid FROM freelancer u {$jn} WHERE is_banned = '0' {$whc}{$whl}{$wh} " . ($params['max_users'] > 0 && $params['max_cost'] > 0 ? 'ORDER BY u.rating DESC LIMIT ' . $params['max_users'] : '') . "\n\t\t\t";
$DB->squery($sql);
}
return $result;
}
/**
* Считаем количество фрилансеров различных разрядов СБР
*
* @return array Данные подсчета
*/
function getRankCount($prof_id = 0)
{
$memBuff = new memBuff();
if ($prof_id) {
$or_prof = professions::GetProfessionOrigin($prof_id);
$tbl_s = "\n ( SELECT * FROM fu WHERE spec_orig = '{$or_prof}' UNION ALL\n SELECT fu.* FROM fu INNER JOIN spec_add_choise sp ON sp.user_id = fu.uid AND sp.prof_id = '{$or_prof}' WHERE fu.is_pro = true\n UNION ALL\n SELECT fu.* FROM fu INNER JOIN spec_paid_choise spc ON spc.user_id = fu.uid AND spc.prof_id = '{$or_prof}' AND spc.paid_to > now()\n ) as s\n ";
} else {
$tbl_s = "fu s";
$join_pc = "\n INNER JOIN\n portf_choise pc\n ON pc.prof_id = s.spec_orig\n AND pc.user_id = s.uid\n ";
}
$sql = "\n SELECT SUM((r.rank=3)::int) as rank3, SUM((r.rank=2)::int) as rank2, SUM((r.rank=1)::int) as rank1\n FROM rating r\n INNER JOIN\n {$tbl_s}\n ON s.uid = r.user_id\n AND s.is_banned = '0'\n {$join_pc}\n WHERE r.rank > 0\n ";
$rank = $memBuff->getSql($error, $sql, 3600);
if ($error || !$rank) {
return NULL;
}
return $rank[0];
}
/**
* Информацию по документообороту СБР для админки.
*
* @param int $scheme тип схем сделок.
* @param array $filter фильтр
* @param int $page номер страницы
* @param string $dir сортировка ASC|DESC
* @param int $dir_col поле сортировки.
* @param int $page_count вернет всего кол-во строк.
*
* @return array
*/
public function getDocsFlow($scheme = sbr::SCHEME_AGNT, $filter = null, $page = 1, $dir = 'DESC', $dir_col = 0, &$page_count = null)
{
$dir = $dir == 'DESC' ? 'DESC' : 'ASC';
$limit = self::PAGE_SA_SIZE;
$offset = ($page - 1) * $limit;
$where = $this->_buildFilterPeriod('ss.arch_closed_time', $filter);
$page_count = 1;
$emp_upload_docs_cond = sbr::DOCS_TYPE_ACT | sbr::DOCS_TYPE_ARB_REP;
// документы, после загрузки которых выводим работодателя в док-те.
$frl_upload_docs_cond = sbr::DOCS_TYPE_ACT;
if ($scheme) {
if ($scheme != -1) {
$where[] = "ss.arch_closed_time > NOW()::date - interval '6 months'";
$scheme_cond = "AND s.scheme_type = {$scheme}";
if ($scheme == sbr::SCHEME_PDRD || $scheme == sbr::SCHEME_PDRD2) {
$scheme_cond = "AND ( s.scheme_type = {$scheme} OR s.scheme_type = " . sbr::SCHEME_PDRD2 . ')';
}
}
$docs_cond = 'AND su.docs_received = false AND su.is_removed = false';
} else {
if ($filter['archive'] == 't') {
$where[] = "ss.arch_closed_time < NOW()::date - interval '6 months'";
}
if ($filter['archive'] == 'f') {
$where[] = "ss.arch_closed_time >= NOW()::date - interval '6 months'";
}
}
if ($fv = pg_escape_string(trim($filter['contract_num']))) {
$where[] = "'СБР-'||ss.sbr_id||'-'||ss.num ILIKE '%{$fv}%'";
}
if ($fv = pg_escape_string(trim($filter['user']))) {
$where[] = "(u.login ILIKE '%{$fv}%' OR u.uname ILIKE '%{$fv}%' OR u.usurname ILIKE '%{$fv}%')";
}
if ($fv = pg_escape_string(trim($filter['name']))) {
$where[] = "ss.name ILIKE '%{$fv}%'";
}
if ($fv = round((double) str_replace(array(' ', ','), array('', '.'), $filter['act_sum']), 2)) {
$where[] = "round(su.act_lcomm+su.act_lintr, 2) = {$fv}";
}
if ($fv = (int) $filter['act_sys']) {
$where[] = "ss.act_sys = {$fv}";
}
if ($fv = $filter['has_docs']) {
$where[] = "su.docs_received = '{$fv}'";
}
if ($fv = $filter['has_act']) {
$where[] = '((su.uploaded_docs & ' . sbr::DOCS_TYPE_COPY_ACT . ") <> 0) = '{$fv}'";
}
if ($fv = $filter['has_fct']) {
$where[] = '((su.uploaded_docs & ' . sbr::DOCS_TYPE_COPY_FACTURA . ") <> 0) = '{$fv}'";
}
if ($fv = $filter['has_reqv']) {
$where[] = "COALESCE(sr.is_filled[sr.form_type], false) = '{$fv}'";
}
if ($fv = $filter['is_removed']) {
$where[] = "su.is_removed = '{$fv}'";
}
if ($scheme == 0) {
$where[] = ' ( ss.scheme_type <> ' . sbr::SCHEME_LC . ' ) ';
// исключаем Аккредитив
}
if ($where) {
$where = 'WHERE ' . implode(' AND ', $where);
}
$leftPdrd = 'WHERE sp.completed IS NOT NULL OR su.user_id IS NOT NULL';
if ($scheme == SBR::SCHEME_PDRD || $scheme == sbr::SCHEME_PDRD2) {
$leftPdrd = "LEFT JOIN \n sbr_stages_users su1 \n ON su1.stage_id = wss.id \n AND su1.user_id = wss.frl_id \n AND (su1.uploaded_docs & {$frl_upload_docs_cond}) <> 0 \n WHERE su1.user_id IS NOT NULL OR su.user_id IS NOT NULL\n ";
}
$from = "\n FROM (\n WITH w_sbr_stages AS (\n SELECT ss.*, s.emp_id, s.frl_id, s.scheme_id, s.scheme_type, s.cost_sys, \n arb.resolved, arb.frl_percent\n FROM sbr s\n INNER JOIN\n sbr_stages ss\n ON ss.sbr_id = s.id\n LEFT JOIN sbr_stages_arbitrage arb\n ON arb.stage_id = ss.id\n WHERE s.reserved_id IS NOT NULL\n AND s.norisk_id IS NULL\n {$scheme_cond}\n )\n SELECT wss.*, wss.emp_id as user_id, wss.cost_sys as act_sys,\n wss.closed_time as arch_closed_time\n FROM w_sbr_stages wss \n LEFT JOIN\n sbr_stages_payouts sp\n ON sp.stage_id = wss.id\n AND sp.user_id = wss.frl_id\n LEFT JOIN\n sbr_stages_users su\n ON su.stage_id = wss.id\n AND su.user_id = wss.emp_id\n AND (su.uploaded_docs & {$emp_upload_docs_cond}) <> 0\n {$leftPdrd} \n UNION ALL\n SELECT wss.*, wss.frl_id, sp.credit_sys,\n sp.requested as arch_closed_time\n FROM w_sbr_stages wss\n INNER JOIN\n sbr_stages_payouts sp\n ON sp.stage_id = wss.id\n AND sp.user_id = wss.frl_id\n ) as ss\n INNER JOIN\n sbr_stages_users su\n ON su.stage_id = ss.id\n AND su.user_id = ss.user_id\n {$docs_cond}\n INNER JOIN\n users u\n ON u.uid = ss.user_id\n LEFT JOIN\n sbr_reqv sr\n ON sr.user_id = ss.user_id\n ";
$sql = "\n SELECT ss.*, sr.*, ss.id as stage_id,\n su.uploaded_docs, su.docs_received, su.act_lcomm, su.act_lintr, su.act_lndfl, su.act_lnp, su.act_lcomm + su.act_lintr as act_sum, su.act_notnp, su.is_removed,\n sp.credit_sys, sp.credit_sum,\n u.login, u.uname, u.usurname, u.role,\n COALESCE(docs.publ_time, ss.arch_closed_time) as act_upload_time\n {$from}\n LEFT JOIN\n sbr_stages_payouts sp\n\n ON sp.stage_id = ss.id\n AND sp.user_id = ss.user_id\n \n --LEFT JOIN sbr_docs docs ON docs.stage_id = ss.id AND docs.access_role IN (1,2) AND docs.type IN (1,8) AND docs.is_deleted = false AND docs.owner_role = 0\n LEFT JOIN (\n SELECT DISTINCT ON (stage_id, access_role) stage_id, d.publ_time, f.modified, d.access_role FROM sbr_docs d\n INNER JOIN file_sbr f ON f.id = d.file_id\n WHERE \n is_deleted = false AND access_role IN (1,2)\n AND owner_role = 0\n ORDER BY stage_id, access_role, publ_time DESC\n ) docs ON docs.stage_id = ss.id AND (docs.access_role = substring(u.role, 1, 1)::integer + 1)\n \n {$where}\n ORDER BY {$this->form_cols['docsflow'][$dir_col][1][$dir]}\n LIMIT {$limit} OFFSET {$offset}\n ";
/*
LEFT JOIN (
SELECT DISTINCT ON (stage_id) stage_id, d.publ_time, f.modified FROM sbr_docs d
INNER JOIN sbr s ON s.id = d.sbr_id
INNER JOIN file_sbr f ON f.id = d.file_id
WHERE type IN (1,8)
--AND status = 4
AND is_deleted = false AND access_role IN (1,2)
AND owner_role = 0
ORDER BY stage_id, publ_time DESC
) docs ON docs.stage_id = ss.id
*/
if ($res = pg_query(self::connect(), $sql)) {
if ($ret = pg_fetch_all($res)) {
$account = new account();
foreach ($ret as &$row) {
if ($row['uploaded_docs']) {
$access_role = is_emp($row['role']) ? self::DOCS_ACCESS_EMP : self::DOCS_ACCESS_FRL;
if ($docs = sbr_meta::getDocs("WHERE sd.stage_id = '{$row['id']}' AND (sd.access_role & {$access_role}) = {$access_role} AND sd.is_deleted = false", null, true)) {
$row['uploaded_docs_a'] = array();
foreach ($docs as $doc) {
$row['uploaded_docs_a'][$doc['type']] = $doc;
}
}
}
// это потом переделать
$account->GetInfo($row['user_id']);
$row['attaches'] = $account->getAllAttach();
}
$sql = "SELECT COUNT(1) as cnt {$from} {$where}";
$mem = new memBuff();
if ($rows = $mem->getSql($err, $sql, 60)) {
$page_count = $rows[0]['cnt'];
}
}
}
return $ret;
}
/**
* Список PROFI пользлвателей для лендинга
*
* @param type $limit
* @return null
*/
function getProfiLanding($limit = 39)
{
$fu_table = self::$fu_table;
$error = null;
$sql = "\n SELECT \n p.name as profname,\n s.*\n FROM {$fu_table} AS s \n INNER JOIN orders AS o ON o.from_id = s.uid AND o.from_date < NOW() AND (o.from_date + o.to_date) > NOW()\n LEFT JOIN professions AS p ON p.id = s.spec\n WHERE \n s.is_profi = 't' AND s.is_banned = '0'\n ORDER BY o.from_date DESC\n LIMIT {$limit}\n ";
$memBuff = new memBuff();
$frls = $memBuff->getSql($error, $sql, self::CATALOG_PROFI_MEM_LIFE, true, self::CATALOG_PROFI_MEM_TAG);
if ($error || !$frls) {
return null;
}
return $frls;
}
/**
* Возвращает кол-во заблокированных Тем(групп)
*
* @return integer Количество заблокированных
*/
function NumsBlockedThreads()
{
global $DB;
$sql = "SELECT COUNT(*) AS cnt FROM blogs_blocked JOIN blogs_msgs ON blogs_msgs.thread_id = blogs_blocked.thread_id AND blogs_msgs.reply_to IS NULL";
$memBuff = new memBuff();
$row = $memBuff->getSql($error, $sql, 180);
return (int) $row[0]['cnt'];
}
/**
* Статистика по проектам за последний месяц и активным пользователям
* @return array элемент u - данные по активным пользователям, p - данные по проектам.
* Каждый содрежит массив с элементами:count - количество, phrase - обозначение единицы в нужном числе
*/
function ShowStats()
{
require_once ABS_PATH . '/classes/project_exrates.php';
$sql = "SELECT count(uid) as cnt FROM users WHERE active = true";
$memBuff = new memBuff();
$tmp = $memBuff->getSql($error, $sql, 600);
$users = $tmp[0]['cnt'];
$sql = "SELECT count(id) as cnt FROM projects WHERE post_date >= '" . date("Y-m-d", time() - 3600 * 24 * 31) . "'";
$tmp = $memBuff->getSql($error, $sql, 600);
$projects = $tmp[0]['cnt'];
$projects = $tmp[0]['cnt'];
$project_exRates = project_exrates::GetAll();
$costProjectWithoutCost = 21000;
$sql = "SELECT \n count(t.id) as cnt, SUM(t.cost_rub) as sum \n FROM (SELECT \n CASE WHEN currency = 0 THEN ( CASE WHEN cost = 0 THEN {$costProjectWithoutCost} ELSE cost * {$project_exRates[24]} END )\n WHEN currency = 1 THEN ( CASE WHEN cost=0 THEN {$costProjectWithoutCost} ELSE cost * {$project_exRates[34]} END )\n WHEN currency = 3 THEN ( CASE WHEN cost=0 THEN {$costProjectWithoutCost} ELSE cost * {$project_exRates[14]} END )\n ELSE ( CASE WHEN cost=0 THEN {$costProjectWithoutCost} ELSE cost END ) END as cost_rub, id\n FROM projects WHERE post_date >= NOW() - interval '1 month'\n ) as t";
$tmp = $memBuff->getSql($error, $sql, 600);
if ($tmp[0]['cnt'] > 0) {
$projects_budget = round($tmp[0]['sum'] / $tmp[0]['cnt'], 0);
} else {
$projects_budget = 0;
}
$users_str = ending($users, 'пользователь', 'пользователя', 'пользователей');
$projects_str = ending($projects, 'проект', 'проекта', 'проектов');
if ($projects >= 10000) {
$projects = number_format($projects, 0, '', ' ');
}
if ($users >= 10000) {
$users = number_format($users, 0, '', ' ');
}
if ($projects_budget >= 10000) {
$projects_budget = number_format($projects_budget, 0, '', ' ');
}
//$str = "<span>".$projects."</span> ".$projects_str." в месяц, <span>".$users."</span> ".$users_str."";
return array('u' => array('count' => $users, 'phrase' => $users_str), 'p' => array('count' => $projects, 'phrase' => $projects_str . ' в месяц'), 's' => array('count' => $projects_budget, 'phrase' => 'средний бюджет проектов'));
}
/**
* Позиция фрилансера относительно других исходя из его рейтинга.
*
* @param string $prm колонка в таблице rating по рейтингу которой будет высчитываться позиция фрилансера
*
* @return integer позиция
*/
public function get_pos_by($prm = 'total')
{
if ($prm == 'total') {
$sql = "SELECT COUNT(*) as cnt FROM freelancer WHERE rating_get(rating, is_pro, is_verify, is_profi) > {$this->data[$prm]}";
} else {
$sql = "SELECT COUNT(*) as cnt FROM rating WHERE {$prm} > {$this->data[$prm]}";
}
require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/memBuff.php';
$memBuff = new memBuff();
$pos = $memBuff->getSql($error, $sql, 1800);
if (!$pos) {
return NULL;
}
return $pos[0]['cnt'] + 1;
}
/**
* Возвращает страницу псевдо-каталога фрилансеров.
*
* @param int count количество всего фрилансеров в данном разделе каталога.
* @param int size количество фрилансеров на данной странице каталога.
* @param array works массив, индексированный ид. фрилансеров, содержащий массив из трех первых работ данного фрилансера в данном разделе.
* @param int limit сколько фрилансеров на одной странице.
* @param int offset OFFSET.
* @param string order тип сортировки
* @param int direction порядок сортировки. 0 -- по убывающей, не 0 -- по возрастающей.
*
* @return array
*/
public function fseoGetCatalog(&$count, &$size, &$works, $limit, $offset, $order = 'general', $direction = 0)
{
global $DB;
// строим запрос
$this->fseoSetSelect();
$this->fseoSetFrom();
$this->fseoSetJoin();
$this->fseoSetWhere();
$this->fseoSetOrderBy($order, $direction);
// получаем список фрилансеров
$sQuery = 'SELECT ' . implode(', ', $this->aSQL['select']) . ' FROM ' . $this->aSQL['from'] . ($this->aSQL['join'] ? ' ' . implode(' ', $this->aSQL['join']) : '') . ' WHERE ' . implode(' AND ', $this->aSQL['where']) . ' ORDER BY ' . implode(', ', $this->aSQL['order_by']) . ' LIMIT ' . $limit . ' OFFSET ' . $offset;
$memBuff = new memBuff();
$frls = $memBuff->getSql($error, $sQuery, self::CATALOG_MEM_LIFE);
if ($error || !$frls) {
return;
}
// получаем общее количество фрилансеров
// для подсчета количества фрилансеров не нужны
unset($this->aSQL['join']['portf_choise']);
unset($this->aSQL['join']['portf_professions']);
$sQuery = 'SELECT COUNT(s.uid) AS count FROM ' . $this->aSQL['from'] . ($this->aSQL['join'] ? ' ' . implode(' ', $this->aSQL['join']) : '') . ' WHERE ' . implode(' AND ', $this->aSQL['where']);
$aCount = $memBuff->getSql($error, $sQuery, self::CATALOG_MEM_LIFE);
$count = $aCount[0]['count'];
$size = sizeof($frls);
// получаем работы фрилансеров
foreach ($frls as $row) {
$frl_ids[] = $row['uid'];
}
$sQuery = '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
FROM portfolio p
INNER JOIN portf_choise pc ON pc.user_id = p.user_id AND pc.prof_id = p.prof_id
' . ($this->sSQLProfId ? '' : 'INNER JOIN freelancer f ON f.uid = p.user_id') . '
WHERE p.user_id IN (' . implode(', ', $frl_ids) . ')
AND p.prof_id = ' . ($this->sSQLProfId ? $this->sSQLProfId : 'f.spec_orig') . '
AND p.first3 = true
ORDER BY p.user_id, p.norder';
$ret = $memBuff->getSql($error, $sQuery, self::CATALOG_MEM_LIFE);
if ($ret) {
foreach ($ret as $row) {
$works[$row['user_id']][] = $row;
}
}
return $frls;
}
/**
* Список PROFI пользователей
*
* @param type $limit
* @return null
*/
function getProfiCatalog($limit = 40)
{
$fu_table = self::$fu_table;
$error = null;
$sql = "\n SELECT \n p.name as profname,\n (COALESCE(sm.completed_cnt,0) + COALESCE(rm.completed_cnt,0)) AS completed_cnt,\n rating_get(s.rating, s.is_pro, s.is_verify, s.is_profi) as t_rating,\n (uc.paid_advices_cnt + uc.ops_frl_plus + uc.ops_emp_plus + uc.sbr_opi_plus + uc.tu_orders_plus + uc.projects_fb_plus) AS total_opi_plus,\n (uc.ops_frl_minus + uc.ops_emp_minus + uc.sbr_opi_minus + uc.tu_orders_minus + uc.projects_fb_minus) AS total_opi_minus,\n s.*\n FROM {$fu_table} AS s \n INNER JOIN orders AS o ON o.from_id = s.uid AND o.from_date < NOW() AND (o.from_date + o.to_date) > NOW()\n LEFT JOIN users_counters AS uc ON uc.user_id = s.uid\n LEFT JOIN sbr_meta AS sm ON sm.user_id = s.uid\n LEFT JOIN reserves_meta AS rm ON rm.user_id = s.uid\n LEFT JOIN professions AS p ON p.id = s.spec\n WHERE \n s.is_profi = 't' AND s.is_banned = '0'\n ORDER BY o.from_date DESC\n LIMIT {$limit};\n ";
$memBuff = new memBuff();
$frls = $memBuff->getSql($error, $sql, self::CATALOG_PROFI_MEM_LIFE, true, self::CATALOG_PROFI_MEM_TAG);
if ($error || !$frls) {
return null;
}
return $frls;
}