Ejemplo n.º 1
0
 /**
  * @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;
 }
Ejemplo n.º 2
0
 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;
 }
Ejemplo n.º 3
0
 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;
     }
 }
Ejemplo n.º 4
0
 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();
 }
Ejemplo n.º 5
0
    /**
     * Выборка всех профессий и групп к которым они относятся.
     *
     * @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;
             }
         }
     }
     /**/
 }
Ejemplo n.º 7
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));
 }
Ejemplo n.º 8
0
$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 
Ejemplo n.º 9
0
 /**
  * получаем идентификаторы контактов пользователя.
  *
  * @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;
 }
Ejemplo n.º 10
0
 /**
  * @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;
 }
Ejemplo n.º 11
0
 /**
  * Выборка всех профессий и групп к которым они относятся.
  * @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;
 }
Ejemplo n.º 12
0
 /**
  * Возвращает страны упорядоченные по количеству зарегистрированных пользователей из этих стран 
  * */
 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;
 }
Ejemplo n.º 13
0
 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;
 }
Ejemplo n.º 14
0
    /**
     * Возвращает страны упорядоченные по количеству зарегистрированных пользователей из этих стран.
     * */
    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;
    }
Ejemplo n.º 15
0
 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;
 }