Beispiel #1
0
 /**
  * Выбрать ТУ для списка пользователей 
  * по указанному количеству на каждого
  * 
  * @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;
 }
Beispiel #2
0
 /**
  * Взять последнюю новость
  *
  * @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;
 }
Beispiel #3
0
 /**
  * Подсчитывает и возвращает количество проектов по закладкам для постранички в ленте,
  * записыавет в 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;
 }
Beispiel #4
0
 /**
  * Статистика по забаненым пользователям.
  *
  * @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;
 }
Beispiel #6
0
 /**
  * Выборка записи из таблицы 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;
 }
Beispiel #7
0
 /**
  * Возвращает кол-во заблокированных проектов
  *
  * @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'];
 }
Beispiel #8
0
 /**
  * Возвращает информацию по заданной 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;
 }
Beispiel #9
0
 /**
  * Возвращает имя актуальной таблицы с позициями фрилансеров
  * в общем каталоге
  *
  * @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;
 }
Beispiel #10
0
 /**
  * Получаем блоки (элементы) ленты по пользователю
  * Получаем единый массив данных, необходимых для вывода элементов ленты. Разделением между топиком сообщества и работой портфолио
  * может служить, например, член массива .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;
 }
Beispiel #11
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;
 }
Beispiel #12
0
 /**
  * Возвращает реквизиты физ. и юр. лица
  * 
  * @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;
 }
Beispiel #13
0
 /**
  * Метод может использоваться в двух случаях:
  * 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;
 }
Beispiel #14
0
 /**
  * Считаем количество фрилансеров различных разрядов СБР
  *
  * @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];
 }
Beispiel #15
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;
 }
Beispiel #16
0
 /**
  * Список 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;
 }
Beispiel #17
0
 /**
  * Возвращает кол-во заблокированных Тем(групп)
  *
  * @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'];
 }
Beispiel #18
0
 /**
  * Статистика по проектам за последний месяц и активным пользователям
  * @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' => 'средний бюджет проектов'));
 }
Beispiel #19
0
 /**
  * Позиция фрилансера относительно других исходя из его рейтинга.
  *
  * @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;
 }
Beispiel #20
0
 /**
  * Возвращает страницу псевдо-каталога фрилансеров.
  * 
  * @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;
 }
Beispiel #21
0
 /**
  * Список 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;
 }