Revize 1636
Přidáno uživatelem Ondřej Fibich před asi 12 roky(ů)
freenetis/branches/testing/application/models/members_traffic.php | ||
---|---|---|
protected $belongs_to = array('member');
|
||
|
||
/**
|
||
* Returns all daily traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @param date $date_from
|
||
* @param date $date_to
|
||
* @param integer $sql_offset
|
||
* @param integer $limit_results
|
||
* @param string $order_by
|
||
* @param string $order_by_direction
|
||
* @return MySQL_Result object
|
||
*/
|
||
public function get_all_members_traffics_by_dates(
|
||
$date_from, $date_to, $sql_offset = 0,
|
||
$limit_results = 100, $order_by = 'total',
|
||
$order_by_direction = 'DESC')
|
||
{
|
||
// order by direction check
|
||
if (strtolower($order_by_direction) != 'desc')
|
||
{
|
||
$order_by_direction = 'asc';
|
||
}
|
||
// query
|
||
return $this->db->query("
|
||
SELECT q.*, m.name AS member_name FROM
|
||
(
|
||
SELECT member_id, SUM(upload) AS upload, SUM(download) AS download,
|
||
SUM(upload) + SUM(download) AS total
|
||
FROM members_traffics d
|
||
WHERE day BETWEEN ? AND ?
|
||
GROUP BY member_id
|
||
) q
|
||
LEFT JOIN members m ON q.member_id = m.id
|
||
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
|
||
LIMIT " . intval($sql_offset) . ", " . intval($limit_results) . "
|
||
", $date_from, $date_to);
|
||
}
|
||
|
||
/**
|
||
* Counts all daily traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @param date $date_from
|
||
* @param date $date_to
|
||
* @return integer
|
||
*/
|
||
public function count_all_members_traffics_by_dates($date_from, $date_to)
|
||
{
|
||
// validates dates
|
||
if (!valid::date_string($date_from) || !valid::date_string($date_to))
|
||
return FALSE;
|
||
|
||
return $this->db->query("
|
||
SELECT COUNT(DISTINCT member_id) AS count
|
||
FROM members_traffics
|
||
WHERE day BETWEEN ? AND ?
|
||
", array($date_from, $date_to))->current()->count;
|
||
}
|
||
|
||
/**
|
||
* Returns total traffics of member
|
||
*
|
||
* @author Michal Kliment
|
||
... | ... | |
{
|
||
$result = $this->db->query("
|
||
SELECT
|
||
IFNULL(SUM(upload),0) AS upload,
|
||
IFNULL(SUM(download),0) AS download
|
||
IFNULL(SUM(upload+local_upload),0) AS upload,
|
||
IFNULL(SUM(download+local_download),0) AS download
|
||
FROM members_traffics d WHERE d.member_id = ?
|
||
", $member_id);
|
||
|
||
... | ... | |
{
|
||
return $this->db->query("
|
||
SELECT
|
||
IFNULL(SUM(upload),0) AS upload,
|
||
IFNULL(SUM(download),0) AS download
|
||
IFNULL(SUM(upload+local_upload),0) AS upload,
|
||
IFNULL(SUM(download+local_download),0) AS download
|
||
FROM members_traffics d WHERE d.member_id = ? AND day = CURDATE()
|
||
", array($member_id))->current();
|
||
}
|
||
... | ... | |
{
|
||
return $this->db->query("
|
||
SELECT
|
||
IFNULL(SUM(upload),0) AS upload,
|
||
IFNULL(SUM(download),0) AS download
|
||
IFNULL(SUM(upload+local_upload),0) AS upload,
|
||
IFNULL(SUM(download+local_download),0) AS download
|
||
FROM members_traffics d WHERE d.member_id = ? AND day BETWEEN ? AND CURDATE()
|
||
", array($member_id, date::start_of_week (date("W"))))->current();
|
||
}
|
||
... | ... | |
{
|
||
return $this->db->query("
|
||
SELECT
|
||
IFNULL(SUM(upload),0) AS upload,
|
||
IFNULL(SUM(download),0) AS download
|
||
IFNULL(SUM(upload+local_upload),0) AS upload,
|
||
IFNULL(SUM(download+local_download),0) AS download
|
||
FROM members_traffics d WHERE d.member_id = '?' AND day BETWEEN ? AND CURDATE()
|
||
", array($member_id, date("Y-m").'-01'))->current();
|
||
}
|
||
... | ... | |
|
||
$where = '';
|
||
if ($filter_sql != '')
|
||
$where .= 'WHERE '.$filter_sql;
|
||
$where .= 'HAVING '.$filter_sql;
|
||
|
||
$limit = '';
|
||
if ($limit_results > 0)
|
||
... | ... | |
YEAR(day) AS year
|
||
FROM members_traffics d
|
||
$group_by
|
||
$where
|
||
) d
|
||
$where
|
||
$order
|
||
$limit
|
||
");
|
||
... | ... | |
|
||
$where = '';
|
||
if ($filter_sql != '')
|
||
$where .= 'WHERE '.$filter_sql;
|
||
$where .= 'HAVING '.$filter_sql;
|
||
|
||
$limit = '';
|
||
if ($limit_results > 0)
|
||
... | ... | |
active
|
||
FROM members_traffics d
|
||
$group_by
|
||
$where
|
||
) d
|
||
JOIN members m ON d.member_id = m.id
|
||
$where
|
||
$order
|
||
$limit
|
||
");
|
||
... | ... | |
|
||
$where = '';
|
||
if ($filter_sql != '')
|
||
$where .= 'WHERE '.$filter_sql;
|
||
$where .= 'HAVING '.$filter_sql;
|
||
|
||
$order = '';
|
||
if ($order_by != '')
|
||
... | ... | |
FROM members_traffics d
|
||
WHERE d.member_id = ?
|
||
$group_by
|
||
$where
|
||
) d
|
||
$where
|
||
", array($member_id));
|
||
|
||
$arr_traffics = array();
|
||
... | ... | |
|
||
if ($filter_sql != '')
|
||
{
|
||
$where .= 'WHERE '.$filter_sql;
|
||
$where .= 'HAVING '.$filter_sql;
|
||
}
|
||
|
||
return $this->db->query("
|
||
... | ... | |
YEAR(day) AS year
|
||
FROM members_traffics d
|
||
$group_by
|
||
$where
|
||
) d
|
||
$where
|
||
")->current()->count;
|
||
}
|
||
|
||
... | ... | |
|
||
$where = '';
|
||
if ($filter_sql != '')
|
||
$where .= 'WHERE '.$filter_sql;
|
||
$where .= 'HAVING '.$filter_sql;
|
||
|
||
return $this->db->query("
|
||
SELECT COUNT(*) AS total
|
||
... | ... | |
YEAR(day) AS year
|
||
FROM members_traffics d
|
||
$group_by
|
||
$where
|
||
) d
|
||
JOIN members m ON d.member_id = m.id
|
||
$where
|
||
")->current()->total;
|
||
}
|
||
|
||
/**
|
||
* Counts all traffics of member
|
||
*
|
||
* !!!!!! SECURITY WARNING !!!!!!
|
||
* Be careful when you using this method, param $filter_sql is unprotected
|
||
* for SQL injections, security should be made at controller site using
|
||
* Filter_form class.
|
||
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $member_id
|
||
* @param string $type
|
||
* @param string $filter_sql
|
||
* @return integer
|
||
*/
|
||
public function count_member_traffics ($member_id, $type, $filter_sql = '')
|
||
{
|
||
switch ($type)
|
||
{
|
||
case 'daily':
|
||
$group_by = 'GROUP BY TO_DAYS(day)';
|
||
break;
|
||
case 'weekly':
|
||
$group_by = 'GROUP BY WEEK(day)';
|
||
break;
|
||
case 'monthly':
|
||
$group_by = 'GROUP BY MONTH(day)';
|
||
break;
|
||
case 'yearly':
|
||
$group_by = 'GROUP BY YEAR(day)';
|
||
break;
|
||
default:
|
||
$group_by = '';
|
||
break;
|
||
}
|
||
|
||
$where = '';
|
||
if ($filter_sql != '')
|
||
$where .= 'WHERE '.$filter_sql;
|
||
|
||
return $this->db->query("
|
||
SELECT COUNT(*) AS total
|
||
FROM
|
||
(
|
||
SELECT
|
||
IFNULL(SUM(upload),0) AS upload,
|
||
IFNULL(SUM(download),0) AS download,
|
||
IFNULL(SUM(upload),0) + IFNULL(SUM(download),0) AS total,
|
||
IFNULL(AVG(upload),0) AS avg_upload,
|
||
IFNULL(AVG(download),0) AS avg_download,
|
||
IFNULL(SUM(local_upload),0) AS local_upload,
|
||
IFNULL(SUM(local_download),0) AS local_download,
|
||
SUM(upload - local_upload) AS foreign_upload,
|
||
SUM(download - local_download) AS foreign_download,
|
||
day,
|
||
WEEK(day) AS week,
|
||
MONTH(day) AS month,
|
||
YEAR(day) AS year
|
||
FROM members_traffics d
|
||
WHERE d.member_id = ?
|
||
$group_by
|
||
) d
|
||
$where
|
||
", array($member_id))->current()->total;
|
||
}
|
||
|
||
/**
|
||
* Averages total traffics
|
||
*
|
||
* @author Michal Kliment
|
freenetis/branches/testing/application/controllers/traffic.php | ||
---|---|---|
|
||
// filter
|
||
|
||
$filter_form = new Filter_form('d');
|
||
$filter_form = new Filter_form('');
|
||
|
||
if ($type_number == 0)
|
||
{
|
||
$month_before = time() - 60 * 60 * 24 * 30;
|
||
|
||
$filter_form->add('day')
|
||
->type('date');
|
||
->type('date')
|
||
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y-m-d', $month_before));
|
||
}
|
||
|
||
if ($type_number == 1)
|
||
{
|
||
$month3_before = time() - 60 * 60 * 24 * 30 * 3;
|
||
|
||
$filter_form->add('week')
|
||
->type('number');
|
||
->type('number')
|
||
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('W', $month3_before));
|
||
}
|
||
|
||
if ($type_number == 2)
|
||
{
|
||
$month6_before = time() - 60 * 60 * 24 * 30 * 6;
|
||
|
||
$filter_form->add('month')
|
||
->type('select_number')
|
||
->values(array
|
||
... | ... | |
10 => __('October'),
|
||
11 => __('November'),
|
||
12 => __('December')
|
||
));
|
||
))
|
||
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('W', $month6_before));
|
||
}
|
||
|
||
if ($type_number == 3)
|
||
{
|
||
$filter_form->add('year')
|
||
->type('number')
|
||
->values(date::years());
|
||
->values(date::years())
|
||
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y') - 5);
|
||
}
|
||
|
||
$filter_form->add('upload')
|
||
... | ... | |
.$order_by.'/'.$order_by_direction,
|
||
'uri_segment' => 'page',
|
||
'style' => 'classic',
|
||
'total_items' => $total_traffics,
|
||
'items_per_page' => $limit_results,
|
||
'filter' => $filter_form
|
||
));
|
||
... | ... | |
$order_by_direction = 'DESC';
|
||
|
||
// filter
|
||
$filter_form = new Filter_form('d');
|
||
$filter_form = new Filter_form('');
|
||
|
||
if ($type_number == 0)
|
||
{
|
||
$filter_form->add('day')
|
||
->type('date');
|
||
->type('date')
|
||
->default(Filter_form::OPER_IS, date('Y-m-d'));
|
||
}
|
||
|
||
if ($type_number == 1)
|
||
{
|
||
$filter_form->add('week')
|
||
->type('number');
|
||
->type('number')
|
||
->default(Filter_form::OPER_IS, intval(date('W')));
|
||
}
|
||
|
||
if ($type_number == 2)
|
||
... | ... | |
10 => __('October'),
|
||
11 => __('November'),
|
||
12 => __('December')
|
||
));
|
||
))
|
||
->default(Filter_form::OPER_IS, intval(date('m')));
|
||
}
|
||
|
||
if ($type_number != 0)
|
||
{
|
||
$filter_form->add('year')
|
||
->type('number')
|
||
->values(date::years());
|
||
->values(date::years())
|
||
->default(Filter_form::OPER_IS, intval(date('Y')));
|
||
}
|
||
|
||
$filter_form->add('upload')
|
||
... | ... | |
|
||
// filter
|
||
|
||
$filter_form = new Filter_form('d');
|
||
$filter_form = new Filter_form('');
|
||
|
||
//if ($type_number == 0)
|
||
//{
|
||
... | ... | |
if ($type_number > 0)
|
||
{
|
||
$filter_form->add('year')
|
||
->type('number')->values(date::years());
|
||
->type('number')
|
||
->values(date::years());
|
||
}
|
||
|
||
$filter_form->add('upload')
|
||
... | ... | |
{
|
||
if ($filter['key'] == 'day')
|
||
{
|
||
if (is_array($filter['value']))
|
||
{
|
||
$filter['value'] = $filter['value'][0];
|
||
}
|
||
|
||
switch ($filter['op'])
|
||
{
|
||
{
|
||
case Filter_form::OPER_EQUAL:
|
||
$time = strtotime($filter['value']);
|
||
$to_time = strtotime($filter['value']);
|
Také k dispozici: Unified diff
Upravy:
- optimalizace v provozu