Projekt

Obecné

Profil

Stáhnout (18.2 KB) Statistiky
| Větev: | Tag: | Revize:

freenetis-github/application/models/members_traffic.php @ 53cf4ce8

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("
c1bdc1c4 Michal Kliment
SELECT DISTINCT PARTITION_NAME AS partition_name
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'members_traffics_daily' AND
TABLE_SCHEMA = ? AND
PARTITION_NAME <> 'p_first' AND
STR_TO_DATE(PARTITION_NAME, 'p_%Y_%m_%d') <
DATE_SUB(NOW(), INTERVAL 2 MONTH)
ORDER BY PARTITION_NAME
", Config::get('db_name'));
8baed187 Michal Kliment
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("
c1bdc1c4 Michal Kliment
SELECT DISTINCT PARTITION_NAME AS partition_name
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'members_traffics_monthly' AND
TABLE_SCHEMA = ? AND
PARTITION_NAME <> 'p_first' AND
STR_TO_DATE(PARTITION_NAME, 'p_%Y_%m_%d') <
DATE_SUB(NOW(), INTERVAL 2 YEAR)
ORDER BY PARTITION_NAME
", Config::get('db_name'));
8baed187 Michal Kliment
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
c1bdc1c4 Michal Kliment
WHERE speed_class_id IS NULL
8baed187 Michal Kliment
)
ORDER BY $type DESC
LIMIT ?
", array($avg, $day, $limit));
return true;
}
}