freenetis-github/application/models/members_traffic.php @ c2e44ab0
| 8baed187 | Michal Kliment | <?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|||
* This file is part of open source system FreenetIS
|
|||
* and it is release under GPLv3 licence.
|
|||
*
|
|||
* More info about licence can be found:
|
|||
* http://www.gnu.org/licenses/gpl-3.0.html
|
|||
*
|
|||
* More info about project can be found:
|
|||
* http://www.freenetis.org/
|
|||
*
|
|||
*/
|
|||
/**
|
|||
* Member's traffic (daily, monthly, yearly)
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
* @package Model
|
|||
*/
|
|||
class Members_traffic_Model extends Model
|
|||
{
|
|||
/**
|
|||
* Creates tables (daily, monthly, yearly) with parttitons for current date/month
|
|||
*
|
|||
* @param boolean $create_first_partition enable creating of first partitions?
|
|||
*/
|
|||
public static function create_tables($create_first_partition = FALSE)
|
|||
{
|
|||
$db = Database::instance();
|
|||
$first_partition_daily = '';
|
|||
$first_partition_monthly = '';
|
|||
if ($create_first_partition === TRUE)
|
|||
{
|
|||
// next month
|
|||
$i = strtotime($db->query("
|
|||
SELECT DATE_ADD('" . date('Y-m-d', time()) . "', INTERVAL 1 MONTH) AS t
|
|||
")->current()->t);
|
|||
// sql
|
|||
$first_partition_daily = ", PARTITION p_" . date('Y_m_d')
|
|||
. " VALUES LESS THAN (TO_DAYS('"
|
|||
. date('Y-m-d', time() + 86400) . "')) ENGINE = InnoDB";
|
|||
$first_partition_monthly = ", PARTITION p_" . date('Y_m_01')
|
|||
. " VALUES LESS THAN (TO_DAYS('"
|
|||
. date('Y-m-01', $i) . "')) ENGINE = InnoDB";
|
|||
}
|
|||
// daily
|
|||
$db->query("CREATE TABLE IF NOT EXISTS `members_traffics_daily` (
|
|||
`member_id` int(11) NOT NULL,
|
|||
`upload` bigint unsigned NOT NULL,
|
|||
`download` bigint unsigned NOT NULL,
|
|||
`local_upload` bigint unsigned NOT NULL,
|
|||
`local_download` bigint unsigned NOT NULL,
|
|||
`active` tinyint(1) NOT NULL DEFAULT '0',
|
|||
`date` date NOT NULL,
|
|||
PRIMARY KEY (`member_id`,`date`),
|
|||
KEY `member_id` (`member_id`),
|
|||
KEY `date` (`date`)
|
|||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci
|
|||
PARTITION BY RANGE (TO_DAYS(`date`))
|
|||
(PARTITION p_first VALUES LESS THAN (TO_DAYS('1970-01-01')) ENGINE = InnoDB
|
|||
$first_partition_daily);
|
|||
");
|
|||
// monthly
|
|||
$db->query("CREATE TABLE IF NOT EXISTS `members_traffics_monthly` (
|
|||
`member_id` int(11) NOT NULL,
|
|||
`upload` bigint unsigned NOT NULL,
|
|||
`download` bigint unsigned NOT NULL,
|
|||
`local_upload` bigint unsigned NOT NULL,
|
|||
`local_download` bigint unsigned NOT NULL,
|
|||
`date` date NOT NULL,
|
|||
PRIMARY KEY (`member_id`,`date`),
|
|||
KEY `member_id` (`member_id`),
|
|||
KEY `date` (`date`)
|
|||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci
|
|||
PARTITION BY RANGE (TO_DAYS(`date`))
|
|||
(PARTITION p_first VALUES LESS THAN (TO_DAYS('1970-01-01')) ENGINE = InnoDB
|
|||
$first_partition_monthly);
|
|||
");
|
|||
// yearly
|
|||
$db->query("CREATE TABLE IF NOT EXISTS `members_traffics_yearly` (
|
|||
`member_id` int(11) NOT NULL,
|
|||
`upload` bigint unsigned NOT NULL,
|
|||
`download` bigint unsigned NOT NULL,
|
|||
`local_upload` bigint unsigned NOT NULL,
|
|||
`local_download` bigint unsigned NOT NULL,
|
|||
`date` date NOT NULL,
|
|||
PRIMARY KEY (`member_id`,`date`),
|
|||
KEY `member_id` (`member_id`),
|
|||
KEY `date` (`date`)
|
|||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Destroys tables
|
|||
*/
|
|||
public static function destroy_tables()
|
|||
{
|
|||
$db = Database::instance();
|
|||
$db->query("DROP TABLE IF EXISTS `members_traffics_daily`");
|
|||
$db->query("DROP TABLE IF EXISTS `members_traffics_monthly`");
|
|||
$db->query("DROP TABLE IF EXISTS `members_traffics_yearly`");
|
|||
}
|
|||
/**
|
|||
* Add new partition for members_traffics_daily
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
*/
|
|||
public function add_daily_partition()
|
|||
{
|
|||
$partition_name = date('Y_m_d', time());
|
|||
| c2e44ab0 | Michal Kliment | $partition_date = date('Y-m-d', strtotime('+1 day', time()));
|
|
| 8baed187 | Michal Kliment | ||
$this->db->query("
|
|||
ALTER TABLE members_traffics_daily
|
|||
ADD PARTITION (
|
|||
PARTITION p_$partition_name
|
|||
VALUES LESS THAN (TO_DAYS('$partition_date')
|
|||
) ENGINE = InnoDB)
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Add new partition for members_traffics_monthly
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
*/
|
|||
public function add_monthly_partition()
|
|||
{
|
|||
$partition_name = date('Y_m_01', time());
|
|||
$next_month = ((date('m') == 12) ? 1 : date('m') + 1);
|
|||
$partition_date = date('Y-') . ($next_month < 10 ? '0' : '') . $next_month . '-01';
|
|||
$this->db->query("
|
|||
ALTER TABLE members_traffics_monthly
|
|||
ADD PARTITION (
|
|||
PARTITION p_$partition_name
|
|||
VALUES LESS THAN (TO_DAYS('$partition_date')
|
|||
) ENGINE = InnoDB)
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Remove partitions for members_traffics_daily which are more than 2 month old
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
*/
|
|||
public function remove_daily_old_partitions()
|
|||
{
|
|||
// get all old partitions
|
|||
$partitions = $this->db->query("
|
|||
SELECT partition_name FROM
|
|||
(
|
|||
SELECT DISTINCT CONCAT('p_', DATE_FORMAT(date, '%Y_%m_%d')) AS partition_name
|
|||
FROM members_traffics_daily
|
|||
WHERE DATE_SUB(NOW(), INTERVAL 2 MONTH) > date
|
|||
) p GROUP BY partition_name
|
|||
");
|
|||
foreach ($partitions as $partition)
|
|||
{
|
|||
$this->db->query("
|
|||
ALTER TABLE members_traffics_daily
|
|||
DROP PARTITION " . $partition->partition_name
|
|||
);
|
|||
}
|
|||
}
|
|||
/**
|
|||
* Remove partitions for members_traffics_monthly which are more than 2 years old
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
*/
|
|||
public function remove_monthly_old_partitions()
|
|||
{
|
|||
// get all old partitions
|
|||
$partitions = $this->db->query("
|
|||
SELECT partition_name FROM
|
|||
(
|
|||
SELECT DISTINCT CONCAT('p_', DATE_FORMAT(date, '%Y_%m_01')) AS partition_name
|
|||
FROM members_traffics_monthly
|
|||
WHERE DATE_SUB(NOW(), INTERVAL 2 YEAR) > date
|
|||
) p GROUP BY partition_name
|
|||
");
|
|||
foreach ($partitions as $partition)
|
|||
{
|
|||
$this->db->query("
|
|||
ALTER TABLE members_traffics_monthly
|
|||
DROP PARTITION " . $partition->partition_name
|
|||
);
|
|||
}
|
|||
}
|
|||
/**
|
|||
* Returns total traffics of member
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param int $member_id
|
|||
* @return MySQL_Result object
|
|||
*/
|
|||
public function get_total_member_traffic($member_id)
|
|||
{
|
|||
$result = $this->db->query("
|
|||
SELECT
|
|||
IFNULL(SUM(upload),0) AS upload,
|
|||
IFNULL(SUM(download),0) AS download
|
|||
FROM members_traffics_yearly d
|
|||
WHERE d.member_id = ?
|
|||
", $member_id);
|
|||
return ($result && $result->count()) ? $result->current() : null;
|
|||
}
|
|||
/**
|
|||
* Returns today traffics of member
|
|||
*
|
|||
* @param integer $member_id
|
|||
* @return MySQL_Result
|
|||
*/
|
|||
public function get_today_member_traffic($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT
|
|||
IFNULL(SUM(upload),0) AS upload,
|
|||
IFNULL(SUM(download),0) AS download
|
|||
FROM members_traffics_daily d
|
|||
WHERE d.member_id = ? AND date = CURDATE()
|
|||
", array($member_id))->current();
|
|||
}
|
|||
/**
|
|||
* Returns month traffics of member
|
|||
*
|
|||
* @param integer $member_id
|
|||
* @return MySQL_Result
|
|||
*/
|
|||
public function get_month_member_traffic($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT
|
|||
IFNULL(SUM(upload),0) AS upload,
|
|||
IFNULL(SUM(download),0) AS download
|
|||
FROM members_traffics_monthly d
|
|||
WHERE d.member_id = ? AND YEAR(date) = ? AND MONTH(date) = ?
|
|||
", array($member_id, date('Y'), date('m')))->current();
|
|||
}
|
|||
/**
|
|||
* Returns total traffics
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $type
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @param string $filter_sql
|
|||
* @return MySQL_Iterator object
|
|||
*/
|
|||
public function get_total_traffics(
|
|||
$type, $limit_from = 0, $limit_results = 50,
|
|||
$order_by = NULL, $order_by_direction = 'ASC', $filter_sql = '')
|
|||
{
|
|||
// check type and select group by
|
|||
$types = array
|
|||
(
|
|||
'daily', 'monthly', 'yearly'
|
|||
);
|
|||
$group_by = array
|
|||
(
|
|||
'daily' => 'TO_DAYS(date)',
|
|||
'monthly' => 'YEAR(date), MONTH(date)',
|
|||
'yearly' => 'YEAR(date)'
|
|||
);
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
// order by direction check
|
|||
if (strtolower($order_by_direction) != 'desc')
|
|||
{
|
|||
$order_by_direction = 'asc';
|
|||
}
|
|||
// having
|
|||
$where = '';
|
|||
if ($filter_sql != '')
|
|||
{
|
|||
$where .= 'WHERE '.$filter_sql;
|
|||
}
|
|||
// limit
|
|||
$limit = '';
|
|||
if ($limit_results > 0)
|
|||
{
|
|||
$limit = "LIMIT " . intval($limit_from) . ", " . intval($limit_results);
|
|||
}
|
|||
// order
|
|||
$order = '';
|
|||
if ($order_by != '')
|
|||
{
|
|||
$order = "ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction";
|
|||
}
|
|||
$data = $this->db->query("
|
|||
SELECT * FROM
|
|||
(
|
|||
SELECT
|
|||
member_id,
|
|||
IFNULL(SUM(upload),0) AS upload,
|
|||
IFNULL(SUM(download),0) AS download,
|
|||
IFNULL(SUM(local_upload),0) AS local_upload,
|
|||
IFNULL(SUM(local_download),0) AS local_download,
|
|||
IFNULL(SUM(upload - local_upload), 0) AS foreign_upload,
|
|||
IFNULL(SUM(download - local_download), 0) AS foreign_download,
|
|||
IFNULL(AVG(upload),0) AS avg_upload,
|
|||
IFNULL(AVG(download),0) AS avg_download,
|
|||
date,
|
|||
date AS day,
|
|||
WEEK(date) AS week,
|
|||
MONTH(date) AS month,
|
|||
YEAR(date) AS year
|
|||
FROM members_traffics_$type d
|
|||
GROUP BY $group_by[$type]
|
|||
) d
|
|||
$where
|
|||
$order
|
|||
$limit
|
|||
");
|
|||
$result = array();
|
|||
foreach ($data as $row)
|
|||
{
|
|||
$result[$row->date] = $row;
|
|||
}
|
|||
return $result;
|
|||
}
|
|||
/**
|
|||
* Returns traffics of all members
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $type
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @param string $filter_sql
|
|||
* @return MySQL_Iterator object
|
|||
*/
|
|||
public function get_all_members_traffics(
|
|||
$type, $limit_from = 0, $limit_results = 50,
|
|||
$order_by = NULL, $order_by_direction = 'ASC', $filter_sql = '')
|
|||
{
|
|||
// check type
|
|||
$types = array('daily', 'monthly', 'yearly');
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
// active?
|
|||
$sel_active = ($type == $types[0]) ? ',active' : '';
|
|||
// order by direction check
|
|||
if (strtolower($order_by_direction) != 'desc')
|
|||
{
|
|||
$order_by_direction = 'asc';
|
|||
}
|
|||
// having
|
|||
$where = '';
|
|||
if ($filter_sql != '')
|
|||
{
|
|||
$where .= 'WHERE '.$filter_sql;
|
|||
}
|
|||
// limit
|
|||
$limit = '';
|
|||
if ($limit_results > 0)
|
|||
{
|
|||
$limit = "LIMIT " . intval($limit_from) . ", " . intval($limit_results);
|
|||
}
|
|||
// order
|
|||
$order = '';
|
|||
if (!empty($order_by))
|
|||
{
|
|||
$active = Settings::get('ulogd_active_type');
|
|||
if ($order_by == 'month')
|
|||
{
|
|||
$date_fields = "year $order_by_direction, month $order_by_direction";
|
|||
}
|
|||
else
|
|||
{
|
|||
$date_fields = $this->db->escape_column($order_by) . ' ' . $order_by_direction;
|
|||
}
|
|||
$order = "ORDER BY $date_fields" . (($active) ? ", $active DESC" : "");
|
|||
}
|
|||
return $this->db->query("
|
|||
SELECT *, m.id AS member_id, m.name AS member_name FROM
|
|||
(
|
|||
SELECT
|
|||
member_id, upload, download, local_upload, local_download,
|
|||
(upload - local_upload) AS foreign_upload,
|
|||
(download - local_download) AS foreign_download,
|
|||
(upload + download) AS total,
|
|||
date,
|
|||
date AS day,
|
|||
WEEK(date) AS week,
|
|||
MONTH(date) AS month,
|
|||
YEAR(date) AS year
|
|||
$sel_active
|
|||
FROM members_traffics_$type d
|
|||
) d
|
|||
JOIN members m ON d.member_id = m.id
|
|||
$where
|
|||
$order
|
|||
$limit
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Returns 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 type $member_id
|
|||
* @param type $type
|
|||
* @param type $limit_from
|
|||
* @param type $limit_results
|
|||
* @param type $order_by
|
|||
* @param type $order_by_direction
|
|||
* @param type $filter_sql
|
|||
* @return type
|
|||
*/
|
|||
public function get_member_traffics(
|
|||
$member_id, $type, $order_by = NULL, $filter_sql = '')
|
|||
{
|
|||
// check type and select group by
|
|||
$types = array
|
|||
(
|
|||
'daily', 'monthly', 'yearly'
|
|||
);
|
|||
$group_by = array
|
|||
(
|
|||
'daily' => 'TO_DAYS(date)',
|
|||
'monthly' => 'YEAR(date), MONTH(date)',
|
|||
'yearly' => 'YEAR(date)'
|
|||
);
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
// active?
|
|||
$sel_active = ($type == $types[0]) ? ',active' : '';
|
|||
// having
|
|||
$where = '';
|
|||
if ($filter_sql != '')
|
|||
{
|
|||
$where .= 'WHERE '.$filter_sql;
|
|||
}
|
|||
// order
|
|||
$order = '';
|
|||
if ($order_by != '')
|
|||
{
|
|||
$order = "ORDER BY " . $this->db->escape_column($order_by);
|
|||
}
|
|||
$traffics = $this->db->query("
|
|||
SELECT * FROM
|
|||
(
|
|||
SELECT
|
|||
member_id,
|
|||
IFNULL(SUM(upload),0) AS upload,
|
|||
IFNULL(SUM(download),0) AS download,
|
|||
IFNULL(SUM(local_upload),0) AS local_upload,
|
|||
IFNULL(SUM(local_download),0) AS local_download,
|
|||
IFNULL(SUM(upload - local_upload), 0) AS foreign_upload,
|
|||
IFNULL(SUM(download - local_download), 0) AS foreign_download,
|
|||
IFNULL(AVG(upload),0) AS avg_upload,
|
|||
IFNULL(AVG(download),0) AS avg_download,
|
|||
date,
|
|||
date AS day,
|
|||
WEEK(date) AS week,
|
|||
MONTH(date) AS month,
|
|||
YEAR(date) AS year
|
|||
$sel_active
|
|||
FROM members_traffics_$type d
|
|||
WHERE d.member_id = ?
|
|||
GROUP BY $group_by[$type]
|
|||
) d
|
|||
$where
|
|||
", array($member_id));
|
|||
$arr_traffics = array();
|
|||
foreach ($traffics as $traffic)
|
|||
{
|
|||
switch ($type)
|
|||
{
|
|||
case 'daily':
|
|||
$arr_traffics[$traffic->date] = $traffic;
|
|||
break;
|
|||
case 'monthly':
|
|||
$arr_traffics[substr($traffic->date, 0, 7)] = $traffic;
|
|||
break;
|
|||
case 'yearly':
|
|||
$arr_traffics[$traffic->year] = $traffic;
|
|||
break;
|
|||
}
|
|||
}
|
|||
return $arr_traffics;
|
|||
}
|
|||
/**
|
|||
* Count total traffics
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $type
|
|||
* @param string $filter_sql
|
|||
* @return integer
|
|||
*/
|
|||
public function count_total_traffics($type, $filter_sql = '')
|
|||
{
|
|||
// check type and select group by
|
|||
$types = array
|
|||
(
|
|||
'daily', 'monthly', 'yearly'
|
|||
);
|
|||
$group_by = array
|
|||
(
|
|||
'daily' => 'TO_DAYS(date)',
|
|||
'monthly' => 'YEAR(date), MONTH(date)',
|
|||
'yearly' => 'YEAR(date)'
|
|||
);
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
// having
|
|||
$where = '';
|
|||
if ($filter_sql != '')
|
|||
{
|
|||
$where .= 'WHERE '.$filter_sql;
|
|||
}
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS count FROM
|
|||
(
|
|||
SELECT
|
|||
member_id,
|
|||
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,
|
|||
date,
|
|||
date AS day,
|
|||
WEEK(date) AS week,
|
|||
MONTH(date) AS month,
|
|||
YEAR(date) AS year
|
|||
FROM members_traffics_$type d
|
|||
GROUP BY $group_by[$type]
|
|||
) d
|
|||
$where
|
|||
")->current()->count;
|
|||
}
|
|||
/**
|
|||
* Counts traffics of all members
|
|||
*
|
|||
* @author Michal kliment
|
|||
* @param string $type
|
|||
* @param string $filter_sql
|
|||
* @return integer
|
|||
*/
|
|||
public function count_all_members_traffics($type, $filter_sql = '')
|
|||
{
|
|||
// check type
|
|||
$types = array
|
|||
(
|
|||
'daily', 'monthly', 'yearly'
|
|||
);
|
|||
$group_by = array
|
|||
(
|
|||
'daily' => 'TO_DAYS(date)',
|
|||
'monthly' => 'YEAR(date), MONTH(date)',
|
|||
'yearly' => 'YEAR(date)'
|
|||
);
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
$where = '';
|
|||
if ($filter_sql != '')
|
|||
{
|
|||
$where .= 'WHERE '.$filter_sql;
|
|||
}
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS total
|
|||
FROM
|
|||
(
|
|||
SELECT
|
|||
member_id, upload, download, local_upload, local_download,
|
|||
(upload - local_upload) AS foreign_upload,
|
|||
(download - local_download) AS foreign_download,
|
|||
date,
|
|||
date AS day,
|
|||
WEEK(date) AS week,
|
|||
MONTH(date) AS month,
|
|||
YEAR(date) AS year
|
|||
FROM members_traffics_$type d
|
|||
) d
|
|||
$where
|
|||
")->current()->total;
|
|||
}
|
|||
/**
|
|||
* Averages total traffics
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $type
|
|||
* @return MySQL_Result object
|
|||
*/
|
|||
public function avg_total_traffics($type)
|
|||
{
|
|||
// check type
|
|||
$types = array('daily', 'monthly', 'yearly');
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
$type = 'daily';
|
|||
}
|
|||
$result = $this->db->query("
|
|||
SELECT
|
|||
IFNULL(AVG(upload), 0) as upload,
|
|||
IFNULL(AVG(download), 0) AS download,
|
|||
IFNULL(AVG(upload+download), 0) AS total
|
|||
FROM members_traffics_$type d
|
|||
");
|
|||
return ($result && $result->current()) ? $result->current() : NULL;
|
|||
}
|
|||
/**
|
|||
* Counts avarage of member's traffics
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param integer $member_id
|
|||
* @param string $type
|
|||
* @return Database_Result
|
|||
*/
|
|||
public function avg_member_traffics($member_id, $type)
|
|||
{
|
|||
// check type
|
|||
$types = array('daily', 'monthly', 'yearly');
|
|||
if (!in_array($type, $types))
|
|||
{
|
|||
$type = 'daily';
|
|||
}
|
|||
$result = $this->db->query("
|
|||
SELECT
|
|||
IFNULL(AVG(upload), 0) as upload,
|
|||
IFNULL(AVG(download), 0) AS download,
|
|||
IFNULL(AVG(upload+download), 0) AS total
|
|||
FROM members_traffics_$type d
|
|||
WHERE d.member_id = ?
|
|||
", array($member_id));
|
|||
return ($result && $result->current()) ? $result->current() : NULL;
|
|||
}
|
|||
/**
|
|||
* Average daily traffic of all members
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param type $day
|
|||
* @param type $type
|
|||
* @return null
|
|||
*/
|
|||
public function avg_daily_traffics($day = NULL, $type = NULL)
|
|||
{
|
|||
$result = $this->db->query("
|
|||
SELECT
|
|||
IFNULL(AVG(upload), 0) AS upload,
|
|||
IFNULL(AVG(download), 0) AS download,
|
|||
IFNULL(AVG(upload+download), 0) AS total
|
|||
FROM members_traffics_daily
|
|||
WHERE `date` = ?
|
|||
", $day);
|
|||
if ($result && ($result = $result->current()))
|
|||
{
|
|||
return ($type && property_exists($result, $type)) ? $result->$type : $result;
|
|||
}
|
|||
else
|
|||
{
|
|||
return NULL;
|
|||
}
|
|||
}
|
|||
/**
|
|||
* Update active members
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param double $avg
|
|||
* @param integer $limit
|
|||
* @param string $type
|
|||
* @param string $day
|
|||
* @return boolean
|
|||
*/
|
|||
public function update_active_members($avg, $limit, $type, $day)
|
|||
{
|
|||
if ($type == 'total')
|
|||
{
|
|||
$type = '(upload+download)';
|
|||
}
|
|||
else if ($type == 'upload')
|
|||
{
|
|||
$type = 'upload';
|
|||
}
|
|||
else
|
|||
{
|
|||
$type = 'download';
|
|||
}
|
|||
$this->db->query("
|
|||
UPDATE members_traffics_daily
|
|||
SET active = IF($type >= ?, 1, 0)
|
|||
WHERE date = ? AND member_id IN
|
|||
(
|
|||
SELECT id
|
|||
FROM members
|
|||
WHERE
|
|||
(qos_rate IS NULL OR qos_rate = '') AND
|
|||
(qos_ceil IS NULL OR qos_ceil = '')
|
|||
)
|
|||
ORDER BY $type DESC
|
|||
LIMIT ?
|
|||
", array($avg, $day, $limit));
|
|||
return true;
|
|||
}
|
|||
}
|