Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 1649

Přidáno uživatelem Ondřej Fibich před asi 12 roky(ů)

Novinky:

- komplet predelani provozu (tri tabulky pro denni, mesicni a rocni) - jede jak pila

Upravy:

- podpora pro TB u network::size

Zobrazit rozdíly:

freenetis/branches/testing/db_upgrades/upgrade_1.0.0~rc9.php
<?php defined('SYSPATH') or die('No direct script access.');
/*
* This file is part of open source system FreenetIS
* and it is released 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/
*
*/
/**
* Creates new structure for traffic logging and
* transform data from precious structure.
*
* @author Ondřej Fibich <ondrej.fibich@google.com>
* @return boolean
*/
function upgrade_1_0_0_rc9_after()
{
$db = Database::instance();
// this should not happend, but...
if (!$db->table_exists('members_traffics'))
{
Settings::set('ulogd_enabled', 0);
return true;
}
// pre delete (if update went wrong previously)
try
{
Members_traffic_Model::destroy_tables();
}
catch (Exception $e)
{
Log::add_exception($e);
}
// create tables and basic partitions
try
{
Members_traffic_Model::create_tables();
// days
$prev = '';
$current = time();
$last = strtotime($db->query("
SELECT DATE_SUB('" . date('Y-m-d', $current) . "', INTERVAL 2 MONTH) AS t
")->current()->t);
for ($i = $last; $i <= $current; $i += 86400)
{
if (date('Y_m_d', $i) != $prev)
{
$db->query("
ALTER TABLE members_traffics_daily
ADD PARTITION (
PARTITION p_" . date('Y_m_d', $i) . "
VALUES LESS THAN (TO_DAYS('" . date('Y-m-d', $i + 86400) . "')
) ENGINE = InnoDB)
");
}
$prev = date('Y_m_d', $i);
}
// monthts
$prev = '';
$i = strtotime($db->query("
SELECT DATE_SUB('" . date('Y-m-d', $current) . "', INTERVAL 2 YEAR) AS t
")->current()->t);
while ($i <= $current)
{
$prev = date('Y_m_01', $i);
// next month
$i = strtotime($db->query("
SELECT DATE_ADD('" . date('Y-m-d', $i) . "', INTERVAL 1 MONTH) AS t
")->current()->t);
if (date('Y_m_01', $i) != $prev)
{
$db->query("
ALTER TABLE members_traffics_monthly
ADD PARTITION (
PARTITION p_$prev
VALUES LESS THAN (TO_DAYS('" . date('Y-m-01', $i) . "')
) ENGINE = InnoDB)
");
}
}
}
catch (Exception $e)
{
Settings::set('ulogd_enabled', 0);
Log::add_exception($e);
try
{
Members_traffic_Model::destroy_tables();
}
catch (Exception $e)
{
Log::add_exception($e);
}
return true;
}
// fill old data
$db->query("
INSERT INTO members_traffics_daily
(member_id, upload, download, local_upload, local_download, active, date)
SELECT member_id,
IFNULL(SUM(upload), 0),
IFNULL(SUM(download), 0),
IFNULL(SUM(local_upload), 0),
IFNULL(SUM(local_download), 0),
active, day
FROM members_traffics
WHERE DATE_SUB(NOW(), INTERVAL 2 MONTH) <= day
GROUP BY member_id, TO_DAYS(day)
");
$db->query("
INSERT INTO members_traffics_monthly
(member_id, upload, download, local_upload, local_download, date)
SELECT member_id,
IFNULL(SUM(upload), 0),
IFNULL(SUM(download), 0),
IFNULL(SUM(local_upload), 0),
IFNULL(SUM(local_download), 0),
CONCAT(YEAR(day), '-', MONTH(day), '-00')
FROM members_traffics
WHERE DATE_SUB(NOW(), INTERVAL 2 YEAR) <= day
GROUP BY member_id, YEAR(day), MONTH(day)
");
$db->query("
INSERT INTO members_traffics_yearly
(member_id, upload, download, local_upload, local_download, date)
SELECT member_id,
IFNULL(SUM(upload), 0),
IFNULL(SUM(download), 0),
IFNULL(SUM(local_upload), 0),
IFNULL(SUM(local_download), 0),
CONCAT(YEAR(day), '-00-00')
FROM members_traffics
GROUP BY member_id, YEAR(day)
");
// drop old table
$db->query("DROP TABLE IF EXISTS members_traffics");
// diasable ulogd
try
{
Ulog2_ct_Model::destroy_functions();
}
catch (Exception $e)
{
Log::add_exception($e);
}
// re-enable ulogd if enabled
if (Settings::get('ulogd_enabled'))
{
try
{
Ulog2_ct_Model::create_functions();
}
catch (Exception $e)
{
Settings::set('ulogd_enabled', 0);
Log::add_exception($e);
}
}
return true;
}
/**
* Creates new structure for traffic logging.
*
* @author Ondřej Fibich <ondrej.fibich@gmail.com>
*/
$upgrade_sql['1.0.0~rc9'] = array
(
);
freenetis/branches/testing/application/helpers/network.php
// transforms to Giga
$unit = 'G';
$size = round($size / 1024, 2);
// size is still too big
if ($size >= 1024)
{
// transforms to Giga
$unit = 'T';
$size = round($size / 1024, 2);
}
}
}
freenetis/branches/testing/application/models/message.php
$mm->transaction_start();
// preparation
$message = $mm->where(array
$message = ORM::factory('message')->where(array
(
'type' => self::UNALLOWED_CONNECTING_PLACE_MESSAGE
))->find();
......
// message do not exists
if (!$message || !$message->id)
{
throw new Exception('Debtor message not founded');
throw new Exception('Unallowed connecting place message not founded');
}
// deletes old redirections
freenetis/branches/testing/application/models/members_traffic.php
*/
/**
* Member's traffic daily
* Member's traffic (daily, monthly, yearly)
*
* @author Michal Kliment
* @author Ondřej Fibich
* @package Model
*
* @property integer $member_id
* @property Member_Model $member
* @property integer $upload
* @property integer $download
* @property date $day
*/
class Members_traffic_Model extends ORM
class Members_traffic_Model extends Model
{
protected $belongs_to = array('member');
/**
* Creates tables (daily, monthly, yearly)
*/
public static function create_tables()
{
$db = Database::instance();
$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);
");
$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);
");
$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());
$partition_date = date('Y-m-d', time() + 86400);
$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 year 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 year 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)
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 d
FROM members_traffics_yearly d
WHERE d.member_id = ?
", $member_id);
......
* @param integer $member_id
* @return MySQL_Result
*/
public function get_today_member_traffic ($member_id)
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 d
WHERE d.member_id = ? AND day = CURDATE()
FROM members_traffics_daily d
WHERE d.member_id = ? AND date = CURDATE()
", array($member_id))->current();
}
/**
* Returns week traffics of member
*
* @param integer $member_id
* @return MySQL_Result
*/
public function get_week_member_traffic ($member_id)
{
return $this->db->query("
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(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();
}
/**
* Returns month traffics of member
*
* @param integer $member_id
* @return MySQL_Result
*/
public function get_month_member_traffic ($member_id)
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 d
WHERE d.member_id = '?' AND day BETWEEN ? AND CURDATE()
", array($member_id, date("Y-m").'-01'))->current();
FROM members_traffics_monthly d
WHERE d.member_id = ? AND YEAR(date) = ? AND MONTH(date) = ?
", array($member_id, date('Y'), date('m')))->current();
}
/**
......
* @param string $filter_sql
* @return MySQL_Iterator object
*/
public function get_total_traffics (
public function get_total_traffics(
$type, $limit_from = 0, $limit_results = 50,
$order_by = NULL, $order_by_direction = 'ASC', $filter_sql = '')
{
switch ($type)
// 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))
{
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;
return NULL;
}
// order by direction check
......
$order_by_direction = 'asc';
}
// having
$where = '';
if ($filter_sql != '')
$where .= 'HAVING '.$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,
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
$group_by
$where
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->day] = $row;
{
$result[$row->date] = $row;
}
return $result;
}
......
* @param string $filter_sql
* @return MySQL_Iterator object
*/
public function get_all_members_traffics (
public function get_all_members_traffics(
$type, $limit_from = 0, $limit_results = 50,
$order_by = NULL, $order_by_direction = 'ASC', $filter_sql = '')
{
switch ($type)
// check type
$types = array('daily', 'monthly', 'yearly');
if (!in_array($type, $types))
{
case 'daily':
$group_by = 'GROUP BY member_id, TO_DAYS(day)';
break;
case 'weekly':
$group_by = 'GROUP BY member_id, WEEK(day), YEAR(day)';
break;
case 'monthly':
$group_by = 'GROUP BY member_id, MONTH(day), YEAR(day)';
break;
case 'yearly':
$group_by = 'GROUP BY member_id, YEAR(day)';
break;
default:
$group_by = '';
break;
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 .= 'HAVING '.$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";
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 d.*, m.name AS member_name
FROM
SELECT *, m.id AS member_id, m.name AS member_name 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,
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,
active
FROM members_traffics d
$group_by
$where
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
");
......
* @param type $filter_sql
* @return type
*/
public function get_member_traffics (
public function get_member_traffics(
$member_id, $type, $order_by = NULL, $filter_sql = '')
{
switch ($type)
// 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))
{
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;
return NULL;
}
// active?
$sel_active = ($type == $types[0]) ? ',active' : '';
// having
$where = '';
if ($filter_sql != '')
$where .= 'HAVING '.$filter_sql;
{
$where .= 'WHERE '.$filter_sql;
}
// order
$order = '';
if ($order_by != '')
$order = "ORDER BY " . $this->db->escape_column($order_by) . " DESC";
{
$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(upload),0) + IFNULL(SUM(download),0) AS total,
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,
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
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
$where
GROUP BY $group_by[$type]
) d
$where
", array($member_id));
$arr_traffics = array();
foreach ($traffics as $traffic)
{
switch ($type)
{
case 'daily':
$arr_traffics[$traffic->day] = $traffic;
$arr_traffics[$traffic->date] = $traffic;
break;
case 'weekly':
$arr_traffics[$traffic->year.'-'.num::null_fill($traffic->week, 2)] = $traffic;
break;
case 'monthly':
$arr_traffics[substr($traffic->day,0,7)] = $traffic;
$arr_traffics[substr($traffic->date, 0, 7)] = $traffic;
break;
case 'yearly':
$arr_traffics[$traffic->year] = $traffic;
break;
......
* @param string $filter_sql
* @return integer
*/
public function count_total_traffics ($type, $filter_sql = '')
public function count_total_traffics($type, $filter_sql = '')
{
switch ($type)
// 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))
{
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 = 'GROUP BY TO_DAYS(day)';
break;
return NULL;
}
// having
$where = '';
if ($filter_sql != '')
{
$where .= 'HAVING '.$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(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
$group_by
$where
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;
}
......
* @param string $filter_sql
* @return integer
*/
public function count_all_members_traffics ($type, $filter_sql = '')
public function count_all_members_traffics($type, $filter_sql = '')
{
switch ($type)
// 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))
{
case 'daily':
$group_by = 'GROUP BY member_id, TO_DAYS(day)';
break;
case 'weekly':
$group_by = 'GROUP BY member_id, WEEK(day), YEAR(day)';
break;
case 'monthly':
$group_by = 'GROUP BY member_id, MONTH(day), YEAR(day)';
break;
case 'yearly':
$group_by = 'GROUP BY member_id, YEAR(day)';
break;
default:
$group_by = '';
break;
return NULL;
}
$where = '';
if ($filter_sql != '')
$where .= 'HAVING '.$filter_sql;
{
$where .= 'WHERE '.$filter_sql;
}
return $this->db->query("
SELECT COUNT(*) AS total
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,
day,
WEEK(day) AS week,
MONTH(day) AS month,
YEAR(day) AS year
FROM members_traffics d
$group_by
$where
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;
}
......
* @param string $type
* @return MySQL_Result object
*/
public function avg_total_traffics ($type)
public function avg_total_traffics($type)
{
switch ($type)
// check type
$types = array('daily', 'monthly', 'yearly');
if (!in_array($type, $types))
{
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 = 'GROUP BY TO_DAYS(day)';
break;
$type = 'daily';
}
$result = $this->db->query("
SELECT
AVG(upload) as upload,
AVG(download) AS download
FROM
(
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
FROM members_traffics d
$group_by
) d
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;
......
* @param string $type
* @return Database_Result
*/
public function avg_member_traffics ($member_id, $type)
public function avg_member_traffics($member_id, $type)
{
switch ($type)
// check type
$types = array('daily', 'monthly', 'yearly');
if (!in_array($type, $types))
{
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 = 'GROUP BY TO_DAYS(day)';
break;
$type = 'daily';
}
$result = $this->db->query("
SELECT
AVG(upload) as upload,
AVG(download) AS download
FROM
(
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
FROM members_traffics d
WHERE d.member_id = ?
$group_by
) d
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;
......
IFNULL(AVG(upload), 0) AS upload,
IFNULL(AVG(download), 0) AS download,
IFNULL(AVG(upload+download), 0) AS total
FROM members_traffics
WHERE day = ?
FROM members_traffics_daily
WHERE `date` = ?
", $day);
if ($result && ($result = $result->current()))
......
$type = 'download';
}
try
{
$this->transaction_start();
$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));
$this->db->query("
UPDATE members_traffics
SET active = 0
WHERE day = ?
", $day);
$this->db->query("
UPDATE members_traffics
SET active = 1
WHERE day = ? AND $type >= ? 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($day, $avg, $limit));
$this->transaction_commit();
return true;
}
catch (Exception $e)
{
$this->transaction_rollback();
Log::add_exception($e);
return false;
}
return true;
}
}
freenetis/branches/testing/application/models/member.php
{
return $this->db->query("
SELECT DISTINCT ip.ip_address
FROM members_traffics mt
FROM members_traffics_daily mt
JOIN users u ON u.member_id = mt.member_id
JOIN devices d ON d.user_id = u.id
LEFT JOIN ifaces i ON i.device_id = d.id
LEFT JOIN ip_addresses ip ON ip.iface_id = i.id
WHERE mt.active = 1 AND mt.day = ? AND ip.ip_address IS NOT NULL
WHERE mt.active = 1 AND mt.date = ? AND ip.ip_address IS NOT NULL
", $day);
}
freenetis/branches/testing/application/models/log.php
*
* @author Ondřej Fibich
* @see Scheduler_Controller::logs_partitions_daily()
* @todo delete all old partitions
*/
public function remove_old_partitions()
{
freenetis/branches/testing/application/models/ulog2_ct.php
SET _upload = _orig_raw_pktlen/1024;
SET _download = _reply_raw_pktlen/1024;
SELECT IF(COUNT(*) > 0, 1, 0) INTO _is_local
FROM local_subnets ls
WHERE (_orig_ip_daddr >> 24 & 255 | _orig_ip_daddr >> 8 & 65280 | _orig_ip_daddr << 8 & 16711680 | _orig_ip_daddr << 24 & 4278190080) & INET_ATON(netmask) = INET_ATON(network_address);
......
VALUES(_ip_address, _download, _upload, _local_download, _local_upload, _member_id)
ON DUPLICATE KEY
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
INSERT INTO members_traffics (member_id, download, upload, local_download, local_upload, day)
INSERT INTO members_traffics_daily (member_id, download, upload, local_download, local_upload, date)
VALUES(_member_id, _download, _upload, _local_download, _local_upload, CURDATE())
ON DUPLICATE KEY
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
INSERT INTO members_traffics_monthly (member_id, download, upload, local_download, local_upload, date)
VALUES(_member_id, _download, _upload, _local_download, _local_upload, DATE_FORMAT(CURDATE(), '%Y-%m-00'))
ON DUPLICATE KEY
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
INSERT INTO members_traffics_yearly (member_id, download, upload, local_download, local_upload, date)
VALUES(_member_id, _download, _upload, _local_download, _local_upload, DATE_FORMAT(CURDATE(), '%Y-00-00'))
ON DUPLICATE KEY
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
REPLACE `config` (`name`, `value`) VALUES ('logging_state', NOW());
RETURN _member_id;
freenetis/branches/testing/application/controllers/web_interface.php
$data = array();
$ips = ORM::factory('member')->get_active_traffic_members_ip_addresses(date('Y-m-d'));
if (Settings::get('ulogd_enabled'))
{
$ips = ORM::factory('member')->get_active_traffic_members_ip_addresses(date('Y-m-d'));
foreach ($ips as $ip)
{
$data[] = array
(
"id" => self::MEMBERS_QOS_ACTIVE,
"ip_address" => $ip->ip_address
);
foreach ($ips as $ip)
{
$data[] = array
(
"id" => self::MEMBERS_QOS_ACTIVE,
"ip_address" => $ip->ip_address
);
}
}
foreach (explode(",", Settings::get('qos_high_priority_ip_addresses'))
freenetis/branches/testing/application/controllers/traffic.php
$order_by = 'day', $order_by_direction = 'DESC', $page_word = null,
$page = 1)
{
$allowed_types = array('daily', 'weekly', 'monthly', 'yearly');
$default_order_by = array('day', 'week', 'month', 'year');
$allowed_types = array('daily', 'monthly', 'yearly');
$default_order_by = array('day', 'month', 'year');
// access control
if (!$this->acl_check_view('Ulogd_Controller','member'))
......
// filter
$filter_form = new Filter_form('');
$filter_form = new Filter_form('d');
if ($type_number == 0)
{
$month_before = time() - 60 * 60 * 24 * 30;
$filter_form->add('day')
->type('date')
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y-m-d', $month_before));
$filter_form->add('date')
->type('date');
}
if ($type_number == 1)
{
$month3_before = time() - 60 * 60 * 24 * 30 * 3;
$filter_form->add('week')
->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')
......
10 => __('October'),
11 => __('November'),
12 => __('December')
))
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('W', $month6_before));
));
}
if ($type_number == 3)
if ($type_number == 2)
{
$filter_form->add('year')
->type('number')
->values(date::years())
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y') - 5);
->values(date::years());
}
$filter_form->add('upload')
......
if ($type_number == 0)
{
$grid->order_field('day')
->label(__('Day'))
->class('center');
}
if ($type_number == 1)
if ($type_number == 2 || $type_number == 1)
{
$grid->order_callback_field('week')
->label(__('Week'))
->callback('callback::week_field')
$grid->order_field('year')
->class('center');
}
if ($type_number == 2)
if ($type_number == 1)
{
$grid->order_callback_field('month')
->label(__('Month'))
->callback('callback::month_field')
->class('center');
}
if ($type_number == 3)
{
$grid->order_field('year')
->label(__('Year'))
->class('center');
}
$grid->order_callback_field('local_upload')
->label(__('Local upload'))
->callback('callback::traffic_upload_field');
$grid->order_callback_field('local_download')
->label(__('Local download'))
->callback('callback::traffic_download_field')
->class('right');
$grid->order_callback_field('foreign_upload')
->label(__('Foreign upload'))
->callback('callback::traffic_upload_field');
$grid->order_callback_field('foreign_download')
->label(__('Foreign download'))
->callback('callback::traffic_download_field')
->class('right');
$grid->order_callback_field('upload')
->label(__('Total upload'))
->callback('callback::traffic_upload_field');
$grid->order_callback_field('download')
->label(__('Total download'))
->callback('callback::traffic_download_field')
->class('right');
if ($type_number > 0)
{
$grid->callback_field('upload')
->label(__('Avarage day upload'))
->label('Avarage day upload')
->callback('callback::members_traffic_avg_field', $type)
->class('right');
$grid->callback_field('download')
->label(__('Avarage day download'))
->label('Avarage day download')
->callback('callback::members_traffic_avg_field', $type)
->class('right');
}
......
$form = new Forge(url::base(TRUE).url::current(TRUE));
$form->dropdown('type')
->label(__('Group by').':')
->label('Group by')
->options($arr_types)
->selected($type_number);
......
$text = $traffic->day;
$title = __('Day');
break;
case 'weekly':
$text = $traffic->week.'/'.$traffic->year;
$title = __('Week');
break;
case 'monthly':
$text = $traffic->month.'/'.$traffic->year;
$title = __('Month');
......
*/
public function members(
$type = NULL, $limit_results = 20,
$order_by = 'download', $order_by_direction = 'DESC',
$page_word = null, $page = 1)
$order_by = NULL, $order_by_direction = 'DESC',
$page_word = NULL, $page = 1)
{
// type cannot be empty
if (!$type)
$this->redirect($this->url().'/daily');
$allowed_types = array('daily', 'weekly', 'monthly', 'yearly');
$default_order_by = array('day', 'week', 'month', 'year');
$allowed_types = array('daily', 'monthly', 'yearly');
$default_order_by = array('day', 'month', 'year');
// access control
if (!$this->acl_check_view('Ulogd_Controller','member'))
......
$order_by_direction = 'DESC';
// filter
$filter_form = new Filter_form('');
$filter_form = new Filter_form('d');
if ($type_number == 0)
{
$filter_form->add('day')
->type('date')
->default(Filter_form::OPER_IS, date('Y-m-d'));
->type('date');
}
if ($type_number == 1)
{
$filter_form->add('week')
->type('number')
->default(Filter_form::OPER_IS, intval(date('W')));
}
... Rozdílový soubor je zkrácen, protože jeho délka přesahuje max. limit.

Také k dispozici: Unified diff