/** * @desc получить выборку данных для страницы user_visits/?mode=user_visits * @param string $from дата в формате Y-m-d * @param string $to дата в формате Y-m-d **/ public static function GetStatistics($from, $to) { $pattern = "#^[0-9]{4}\\-[0-9]{2}\\-[0-9]{2}\$#"; if (!preg_match($pattern, $from, $m) || !preg_match($pattern, $to, $m)) { return false; } $DB = new DB("stat"); $row = $DB->cache(300)->row("SELECT\n SUM((n = 1 AND NOT is_emp)::int) AS f1,\n SUM((n BETWEEN 2 AND 5 AND NOT is_emp)::int) AS f2_5,\n SUM((n BETWEEN 6 AND 10 AND NOT is_emp)::int) AS f6_10,\n SUM((n > 10 AND NOT is_emp)::int) AS f11,\n SUM((n = 1 AND is_emp)::int) AS e1,\n SUM((n BETWEEN 2 AND 5 AND is_emp)::int) AS e2_5,\n SUM((n BETWEEN 6 AND 10 AND is_emp)::int) AS e6_10,\n SUM((n > 10 and is_emp)::int) AS e11\n FROM\n(\nSELECT user_id,\n COUNT(*) AS n, is_emp\n FROM users_visits_daily \n WHERE visit_date >= '{$from}' AND visit_date <= '{$to}' GROUP BY user_id, is_emp\n) AS s"); $o = new StdClass(); $o->f1 = $row["f1"]; //фриленсеры авторизовавшиеся 1 раз $o->e1 = $row["e1"]; //работодатели авторизовавшиеся 1 раз $o->f2_5 = $row["f2_5"]; //фриленсеры авторизовавшиеся от 2 до 5 раз $o->e2_5 = $row["e2_5"]; //работодатели авторизовавшиеся от 2 до 5 раз $o->f6_10 = $row["f6_10"]; //фриленсеры авторизовавшиеся от 6 до 10 раз $o->e6_10 = $row["e6_10"]; //работодатели авторизовавшиеся от 6 до 10 раз $o->f11 = $row["f11"]; //фриленсеры авторизовавшиеся более 10 раз $o->e11 = $row["e11"]; //работодатели авторизовавшиеся более 10 раз $o->total_emp = $o->e1 + $o->e2_5 + $o->e6_10 + $o->e11; $o->total_frl = $o->f1 + $o->f2_5 + $o->f6_10 + $o->f11; return $o; }
public function getStatAdvices($filter = false) { // оптимизирован временно (год-два, пока рекомендаций не очень много; основная проблема в джойнах с users): 0018602 $sql = "SET join_collapse_limit = 1;\n SELECT COUNT(*) as cnt, status, mod_status \n FROM paid_advices\n INNER JOIN users f ON f.uid = paid_advices.user_from AND f.is_banned = B'0'\n INNER JOIN users t ON t.uid = paid_advices.user_to AND t.is_banned = B'0'\n WHERE mod_status <> 0 GROUP by status, mod_status"; $res = $this->_db->cache(60)->rows($sql); $counter = array('all' => 0, 'new' => 0, 'accepted' => 0, 'declined' => 0, 'deleted' => 0, 'filter' => 0); foreach ($res as $k => $val) { $counter['all'] += $val['cnt']; if ($val['status'] == self::STATUS_ACCEPTED && $val['mod_status'] == self::MOD_STATUS_PENDING) { $counter['new'] += $val['cnt']; } if ($val['mod_status'] == self::MOD_STATUS_ACCEPTED) { $counter['accepted'] += $val['cnt']; } if ($val['mod_status'] == self::MOD_STATUS_DECLINED) { $counter['declined'] += $val['cnt']; } if ($val['mod_status'] == self::MOD_STATUS_USER_DECLINED) { $counter['deleted'] += $val['cnt']; } } if ($filter !== false) { list($type_sql, $order) = $this->getTypeSQL($filter['type']); $filter_sql = ' AND ' . $this->getFilterSQL($filter); $sql = "SELECT COUNT(pa.*) as cnt, SUM(pa.cost_sum) as cost_sum_filter, SUM(pa.comm_sum) as comm_sum_filter FROM paid_advices as pa \n INNER JOIN users e ON e.uid = pa.user_from AND e.is_banned = B'0'\n INNER JOIN users f ON f.uid = pa.user_to AND f.is_banned = B'0'\n WHERE {$type_sql} {$filter_sql}"; $res = $this->_db->row($sql); $counter['filter'] = (int) $res['cnt']; $counter['cost_sum_filter'] = round($res['cost_sum_filter'], 2); $counter['comm_sum_filter'] = round($res['comm_sum_filter'], 2); } return $counter; }
protected function _loadHelperInfo() { $DB = new DB($this->master_alias); $rows = $DB->cache(600)->rows('SELECT * FROM londiste_helper__inkeys'); foreach ($rows as $r) { $this->_helper[$r['t_name']][$r['f_name']] = $r; } }
public function run() { $xml = \DB::cache()->fetch(self::cacheXmlKey); if (empty($xml)) { $xml = $this->getAnalyticsXml(); DB::cache()->save(self::cacheXmlKey, $xml, self::cacheXmlTime); } $xml = simplexml_load_string($xml); if (!empty($this->state['drop'])) { $this->dropState(); } $this->aggregate($xml); $this->analytics(); }
/** * Выборка всех профессий и групп к которым они относятся. * * @return array $rows * */ public function GetCountriesAndCities() { $DB = new DB('master'); $cmd = 'SELECT country.id AS country_id, country.country_name, city.id, city.city_name AS name FROM country LEFT JOIN city ON city.country_id = country.id ORDER BY country.pos, city.id '; $rows = $DB->cache(1200)->rows($cmd); return $rows; }
/** * @param $matchIds - массив в котором все ключи - идентификаторы найденных пользователей (по идее это массив выдачи сфинкса $result['matches'] но сейчас пока буду использовать вместо сфинкса работу с базой напрямую) * @param $limit - сколько записей показывать * @param &$rows - массив с результатами поиска по вхождению подстрок * Array * @param &$moreContacts -hfpyjcnm количествf найденных контактов и limit * */ private function getLastContacts($matchIds, $limit, &$rows, &$moreContacts) { //получаем роль пользователя session_start(); $role = $_SESSION['role'][0]; if ($role !== null) { //если роль пользователя известна //получаем партнеров по СБР пользователя $partner = 'emp_id'; $entity = 'frl_id'; if ($role == 1) { $partner = 'frl_id'; $entity = 'emp_id'; } $cmd = "SELECT {$partner} FROM sbr WHERE {$entity} = " . $_SESSION['uid'] . ' ORDER BY reserved_time DESC'; $DB = new DB('master'); $rawsbr = $DB->cache(600)->rows($cmd); $partners = array(); $j = 0; //счетчик СБР пользователя $complete = 0; foreach ($rawsbr as $i) { if ($matchIds[$i[$partner]] !== null) { if (!$complete) { $partners[] = $i[$partner]; } ++$j; if ($j > $limit) { $complete = 1; } } } //здесь надо обработать случай, когда СБР у пользователя нет (получить из контактов) if ($j) { $ids = implode(',', array_reverse($partners)); $cmd = "SELECT u.uid, u.uname, u.usurname, u.login, u.photo, u.role, f.path FROM users AS u\n\t\t\t\t\t\tLEFT JOIN file AS f\n\t\t\t\t\t\t\tON f.fname = u.photo\n\t\t\t\t\t\tWHERE uid IN ({$ids});\n\t\t\t\t\t"; $partners = $DB->cache(600)->rows($cmd); foreach ($partners as $k => $i) { $i['uname'] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['uname']); $i['usurname'] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['usurname']); $i['isContacts'] = 1; if ($rows[$k] !== null) { $more--; } $rows[$k] = $i; } $moreContacts = $j - $limit; if ($moreContacts <= 0) { $moreContacts = 0; } } } /**/ }
/** * Возвращает статистику по проекту из таблицы projects_stat (БД stat) * * @param integer $prj_id id проекта. * @param integer $cache время жизни кэша информации. Меньше 0 -- не кэшировать. * @return array строка. */ function getProjectWatch($prj_id, $cache = 180) { $db = new DB('stat'); if ($cache >= 0) { $db->cache($cache); } return $db->row(self::getViewsCountSql($prj_id)); }
$aMonthes[12] = 'декабрь'; $aData = array(); for ($i = 2006; $i <= date('Y'); ++$i) { for ($j = 1; $j <= 12; ++$j) { $aData[$i][$j]['data'] = 0; $aData[$i][$j]['date_m'] = 0; $aData[$i][$j]['date_y'] = 0; } } $DB = new DB('master'); for ($i = 2006, $Y = date('Y'); $i <= $Y; ++$i) { $date_from = $i . '-01-01'; $date_to = $i + 1 . '-01-01'; $sql = "SELECT SUM(trs_sum) as ammount, to_char(op_date,'MM') as _day FROM\n\t\t\taccount_operations WHERE op_date >= ? AND op_date < ? GROUP BY to_char(op_date,'MM') ORDER BY to_char(op_date,'MM')"; if ($i < $Y) { $aTemp = $DB->cache(0)->rows($sql, $date_from, $date_to); } else { $aTemp = $DB->rows($sql, $date_from, $date_to); } $aTemp = $DB->rows($sql, $date_from, $date_to); for ($j = 0; $j < count($aTemp); ++$j) { $iMonth = intval($aTemp[$j]['_day']); $aData[$i][$iMonth]['data'] = true; $aData[$i][$iMonth]['date_m'] = $aTemp[$j]['_day']; $aData[$i][$iMonth]['date_y'] = $i; } } ?> <script type="text/javascript"> var cur_y = '<?php
/** * получаем идентификаторы контактов пользователя. * * @param $uid - идентификатор активного пользователя * */ public static function GetMsgContact($uid) { if (!$uid) { return false; } $cmd = "SELECT messages_contacts({$uid})"; $DB = new DB('plproxy'); $rawdata = $DB->cache(600)->rows($cmd); $data = array(); foreach ($rawdata as $i) { $f = preg_match("#^\\((\\d+),#", $i['messages_contacts'], $matches); if ($matches[1] !== null) { $data[] = $matches[1]; } } if (count($data)) { return implode(',', $data); } return false; }
/** * @param array $ids - массив идентификаторов профессий * Возвращает названия профессий по их id * */ function GetProfessionsTitles($ids) { $ids = join(",", $ids); $DB = new DB('master'); $cmd = "SELECT id, name FROM professions WHERE id IN ({$ids})"; $rows = $DB->cache(3600)->rows($cmd); return $rows; }
/** * Выборка всех профессий и групп к которым они относятся. * @return array $rows * */ function GetCountriesAndCities() { $DB = new DB('master'); $cmd = "SELECT country.id AS country_id, country.country_name, city.id, city.city_name AS name\n FROM country \n LEFT JOIN city\n ON city.country_id = country.id\t\t\t\t\t\n\t\t\t\tORDER BY country.pos, city.id\n\t "; $rows = $DB->cache(1200)->rows($cmd); return $rows; }
/** * Возвращает страны упорядоченные по количеству зарегистрированных пользователей из этих стран * */ function GetCountriesByCountUser() { $cmd = "SELECT c.id AS id , count(uid) as nn, c.country_name AS name\n\t\t\t\t\t\t\tFROM country AS c\n\t\t\t\t\t\t\tLEFT JOIN users as u\n\t\t\t\t\t\t\t ON c.id = u.country \t\t\t\t\t\t\t\n\t\t\t\t\t\t\tGROUP BY c.id, c.country_name \n\t\t\t\t\t\t\tORDER BY nn desc"; $DB = new DB('master'); $rows = $DB->cache(1200)->rows($cmd); return $rows; }
public function searchLC($page, $request, &$page_count) { $limit = self::ADM_PAGE_SIZE; $offset = ($page - 1) * $limit; $where = array(); $orWhere = array(); $params = array(); $sql[] = 'SELECT lc.* FROM pskb_lc lc'; $sql_cnt[] = 'SELECT COUNT(*) cnt FROM pskb_lc lc'; $sql[] = $sql_cnt[] = 'INNER JOIN sbr s ON s.id = lc.sbr_id AND s.is_draft = FALSE'; foreach ($request as $k => $v) { if ($v == 'null' || !$v) { continue; } switch ($k) { case 'search': $orWhere[] = 'lc.lc_id = ?'; $orWhereParams[] = intval($v); $orWhere[] = 'lc.sbr_id = ?'; $orWhereParams[] = intval($v); break; case 'state': $where[] = 'lc.state = ?'; $whereParams[] = $v; break; case 'ps_emp': $where[] = 'lc.ps_emp = ?'; $whereParams[] = $v; break; case 'ps_frl': $where[] = 'lc.ps_frl = ?'; $whereParams[] = $v; break; case 'date_cover': if ($v['from']['year'] > 0) { $date_cover_from = mktime(0, 0, 0, $v['from']['month'] ? (int) $v['from']['month'] : (int) $v['from']['month'] + 1, $v['from']['day'] ? (int) $v['from']['day'] : (int) $v['from']['day'] + 1, (int) $v['from']['year']); $where[] = "to_char(lc.covered, 'YYYY-MM-DD') >= ?"; $whereParams[] = date('Y-m-d', $date_cover_from); } if ($v['to']['year'] > 0) { $date_cover_to = mktime(23, 59, 59, $v['to']['month'] ? (int) $v['to']['month'] : 12, $v['to']['day'] ? (int) $v['to']['day'] : 30, (int) $v['to']['year']); $where[] = "to_char(lc.covered, 'YYYY-MM-DD') <= ?"; $whereParams[] = date('Y-m-d', $date_cover_to); } break; case 'date_end': if ($v['from']['year'] > 0) { $date_end_from = mktime(0, 0, 0, $v['from']['month'] ? (int) $v['from']['month'] : (int) $v['from']['month'] + 1, $v['from']['day'] ? (int) $v['from']['day'] : (int) $v['from']['day'] + 1, (int) $v['from']['year']); $where[] = "to_char(lc.ended, 'YYYY-MM-DD') >= ?"; $whereParams[] = date('Y-m-d', $date_end_from); } if ($v['to']['year'] > 0) { $date_end_to = mktime(23, 59, 59, $v['to']['month'] ? (int) $v['to']['month'] : 12, $v['to']['day'] ? (int) $v['to']['day'] : 30, (int) $v['to']['year']); $where[] = "to_char(lc.ended, 'YYYY-MM-DD') <= ?"; $whereParams[] = date('Y-m-d', $date_end_to); } break; } } if (count($orWhere) || count($where)) { $sql_where[] = 'WHERE'; if (count($where)) { $sql_where[] = implode(' AND ', $where); $params = array_merge($params, $whereParams); } if (count($orWhere)) { if (count($where)) { $sql_where[] = 'AND'; } $sql_where[] = '(' . implode(' OR ', $orWhere) . ')'; $params = array_merge($params, $orWhereParams); } $sql[] = implode(' ', $sql_where); $sql_cnt[] = implode(' ', $sql_where); } $sql[] = 'ORDER BY lc.id DESC'; $sql[] = 'LIMIT ? OFFSET ?'; $params[] = $limit; $params[] = $offset; $sql_str = implode(' ', $sql); $sql_cnt_str = implode(' ', $sql_cnt); $sql_p = $params; array_unshift($sql_p, $sql_cnt_str); $page_count = call_user_method_array('row', $this->_db->cache(300), $sql_p); $page_count = $page_count['cnt']; array_unshift($params, $sql_str); $res = call_user_method_array('rows', $this->_db, $params); return $res; }
/** * Возвращает страны упорядоченные по количеству зарегистрированных пользователей из этих стран. * */ public function GetCountriesByCountUser() { $cmd = 'SELECT c.id AS id , count(uid) as nn, c.country_name AS name FROM country AS c LEFT JOIN users as u ON c.id = u.country GROUP BY c.id, c.country_name ORDER BY nn desc'; $DB = new DB('master'); $rows = $DB->cache(1200)->rows($cmd); return $rows; }
protected function gatherResponse(&$tmp, $platform, $cacheResponseKey) { $response = array(); foreach ($tmp as $startTime => $item) { switch ($platform) { case 'all': $value = (isset($item['Yes']) ? $item['Yes']['value'] : 0) + (isset($item['No']) ? $item['No']['value'] : 0); break; case 'mobile': $value = isset($item['Yes']) ? $item['Yes']['value'] : 0; break; case 'desktop': $value = isset($item['No']) ? $item['No']['value'] : 0; break; } $response[] = array('startTime' => $startTime, 'value' => $value); } // if (!empty($response)) \DB::cache()->save($cacheResponseKey, $response, gaExport::cacheTime); return $response; }