Revize 874
Přidáno uživatelem Michal Kliment před více než 13 roky(ů)
freenetis/branches/ulogd/media/css/style.css | ||
---|---|---|
#operation {
|
||
margin-right: 20px;
|
||
width: 190px;
|
||
}
|
||
|
||
.right {
|
||
text-align: right;
|
||
}
|
freenetis/branches/ulogd/media/js/jquery.timer.js | ||
---|---|---|
/*
|
||
*
|
||
* jQuery Timer plugin v0.1
|
||
* Matt Schmidt [http://www.mattptr.net]
|
||
*
|
||
* Licensed under the BSD License:
|
||
* http://mattptr.net/license/license.txt
|
||
*
|
||
*/
|
||
|
||
jQuery.timer = function (interval, callback)
|
||
{
|
||
/**
|
||
*
|
||
* timer() provides a cleaner way to handle intervals
|
||
*
|
||
* @usage
|
||
* $.timer(interval, callback);
|
||
*
|
||
*
|
||
* @example
|
||
* $.timer(1000, function (timer) {
|
||
* alert("hello");
|
||
* timer.stop();
|
||
* });
|
||
* @desc Show an alert box after 1 second and stop
|
||
*
|
||
* @example
|
||
* var second = false;
|
||
* $.timer(1000, function (timer) {
|
||
* if (!second) {
|
||
* alert('First time!');
|
||
* second = true;
|
||
* timer.reset(3000);
|
||
* }
|
||
* else {
|
||
* alert('Second time');
|
||
* timer.stop();
|
||
* }
|
||
* });
|
||
* @desc Show an alert box after 1 second and show another after 3 seconds
|
||
*
|
||
*
|
||
*/
|
||
|
||
var interval = interval || 100;
|
||
|
||
if (!callback)
|
||
return false;
|
||
|
||
_timer = function (interval, callback) {
|
||
this.stop = function () {
|
||
clearInterval(self.id);
|
||
};
|
||
|
||
this.internalCallback = function () {
|
||
callback(self);
|
||
};
|
||
|
||
this.reset = function (val) {
|
||
if (self.id)
|
||
clearInterval(self.id);
|
||
|
||
var val = val || 100;
|
||
this.id = setInterval(this.internalCallback, val);
|
||
};
|
||
|
||
this.interval = interval;
|
||
this.id = setInterval(this.internalCallback, this.interval);
|
||
|
||
var self = this;
|
||
};
|
||
|
||
return new _timer(interval, callback);
|
||
};
|
freenetis/branches/ulogd/application/i18n/cs_CZ/texts.php | ||
---|---|---|
'active message' => 'Aktivní zpráva',
|
||
'active redirections' => 'Aktivní přesměrování',
|
||
'actual' => 'Aktuální',
|
||
'actual traffic of ip addresses' => 'Aktuální provoz IP adres',
|
||
'actual traffic of members' => 'Aktuální provoz členů',
|
||
'add' => 'Přidat',
|
||
'add admin to redirection module' => 'Přidat správce do modulu přesměrování',
|
||
'add address point' => 'Přidat adresní bod',
|
||
... | ... | |
'change voicemail password' => 'Změnit heslo hlasové schránky',
|
||
'channel' => 'Kanál',
|
||
'check again' => 'Znovu zkontrolovat',
|
||
'choose date' => 'Vyberte datum',
|
||
'choose language' => 'Vyberte jazyk',
|
||
'choose format of export' => 'Vyberte formát exportu',
|
||
'clean urls' => 'Čistá URL',
|
||
... | ... | |
'optional message text' => 'Text pro volitelná zpráva',
|
||
'or' => 'nebo',
|
||
'or enter correct variable symbol' => 'Nebo vložte správný variabilní symbol',
|
||
'order' => 'Pořadí',
|
||
'order number' => 'Číslo objednávky',
|
||
'organization identifier' => 'IČ',
|
||
'organization_identifier' => 'IČ',
|
||
... | ... | |
'range of ip addresses - first host' => 'Rozsah IP adres - první host',
|
||
'range of ip addresses - last host' => 'Rozsah IP adres - poslední host',
|
||
'rating' => 'Ohodnocení',
|
||
'remaining time' => 'Zbývající čas',
|
||
'read' => 'Přečtená',
|
||
'read only' => 'Pouze pro čtení',
|
||
'read-only tariff cannot be deleted' => 'Tarif pouze pro čtení nelze mazat.',
|
||
... | ... | |
'to' => 'Komu',
|
||
'too short' => 'Příliš krátké',
|
||
'tools' => 'Nástroje',
|
||
'total' => 'Celkem',
|
||
'total inbound' => 'Celkem příchozí',
|
||
'total items' => 'Celkem položek',
|
||
'total outbound' => 'Celkem odchozí',
|
||
... | ... | |
'town has been successfully updated' => 'Město bylo úspěšně aktualizováno.',
|
||
'towns' => 'Města',
|
||
'trade name' => 'Výrobní název',
|
||
'traffic for the period' => 'Provoz za období',
|
||
'traffic of members in time' => 'Provoz členů v čase',
|
||
'transfer' => 'Převod',
|
||
'transfer details' => 'Detaily převodu',
|
||
'transfer fee' => 'Transakční poplatek',
|
freenetis/branches/ulogd/application/helpers/date.php | ||
---|---|---|
return ($interval['days']*24 + $interval['h'])*60 + $interval['i'];
|
||
}
|
||
|
||
/**
|
||
* Returns counter for timestamp
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $timestamp
|
||
* @return string
|
||
*/
|
||
public static function counter($timestamp)
|
||
{
|
||
$seconds = num::negation($timestamp % 60, 0, 0, -1);
|
||
$timestamp = ($timestamp - $seconds) / 60;
|
||
|
||
$minutes = num::negation($timestamp % 60, 0, 0, -1);
|
||
$timestamp = ($timestamp - $minutes) / 60;
|
||
|
||
$hours = num::negation($timestamp % 24, 0, 0, -1);
|
||
$timestamp = ($timestamp - $hours) / 24;
|
||
|
||
return num::null_fill($hours,2).':'.num::null_fill($minutes,2).':'.num::null_fill($seconds,2);
|
||
}
|
||
|
||
} // End date
|
freenetis/branches/ulogd/application/helpers/callback.php | ||
---|---|---|
*/
|
||
class callback_Core
|
||
{
|
||
|
||
private static $order_number;
|
||
|
||
/**
|
||
* Callback for balance field in accounts.
|
||
* @author Jiri Svitak
|
||
... | ... | |
echo '<span style="color: red;">' . url_lang::lang('texts.Locked') . '</span>';
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Callback for order number field in grid
|
||
*
|
||
* @author Michal Kliment
|
||
* @param <type> $item
|
||
* @param <type> $name
|
||
* @param <type> $args
|
||
*/
|
||
public static function order_number_field($item, $name, $args = array())
|
||
{
|
||
// first time set default number
|
||
if (!isset(self::$order_number))
|
||
self::$order_number = $args[0];
|
||
|
||
// direction
|
||
switch ($args[1])
|
||
{
|
||
// increases
|
||
case 1:
|
||
self::$order_number++;
|
||
break;
|
||
// decreases
|
||
case -1:
|
||
self::$order_number--;
|
||
break;
|
||
default:
|
||
// do nothing
|
||
break;
|
||
}
|
||
|
||
echo self::$order_number.'.';
|
||
}
|
||
|
||
/**
|
||
* Callback for traffic field
|
||
*
|
||
* @author Michal Kliment
|
||
* @param <type> $item
|
||
* @param <type> $name
|
||
*/
|
||
public static function traffic_field($item, $name)
|
||
{
|
||
// default unit is kiloByte
|
||
$unit = 'kB';
|
||
|
||
$value = $item->$name;
|
||
|
||
// size is too big
|
||
if ($value > 1024)
|
||
{
|
||
// transforms to MegaByte
|
||
$unit = 'MB';
|
||
$value = round($value/1024,2);
|
||
|
||
// size is still too big
|
||
if ($value > 1024)
|
||
{
|
||
// transforms to GigaByte
|
||
$unit = 'GB';
|
||
$value = round($value/1024,2);
|
||
}
|
||
}
|
||
|
||
echo $value.' '.$unit;
|
||
}
|
||
|
||
/**
|
||
* Callback to draw active/inactive image in grid field
|
||
*
|
||
* @author Michal Kliment
|
||
* @param <type> $item
|
||
* @param <type> $name
|
||
*/
|
||
public function active_field($item, $name)
|
||
{
|
||
// 1 => active, 0 => inactive
|
||
switch ($item->$name)
|
||
{
|
||
case 1:
|
||
$state = 'active';
|
||
break;
|
||
case 0:
|
||
$state = 'inactive';
|
||
break;
|
||
default:
|
||
$state = 'inactive';
|
||
break;
|
||
}
|
||
|
||
echo html::image(array('src' => url::base().'media/images/'.$state.'.png', 'title' => url_lang::lang('texts.'.$state)));
|
||
}
|
||
}
|
freenetis/branches/ulogd/application/helpers/num.php | ||
---|---|---|
}
|
||
|
||
/**
|
||
* Negates number to opposite number / Negates number to given number
|
||
* Negates all number / Negates only numbers for given level and operand
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $number
|
||
* @param integer $to_number
|
||
* @param integer $level
|
||
* @param integer $op
|
||
* @return integer
|
||
*/
|
||
public static function negation($number, $to_number = NULL, $level = NULL, $op = 1)
|
||
{
|
||
// number in which it will negate
|
||
$to_number = ($to_number) ? $to_number : $number*(-1);
|
||
|
||
// level is given
|
||
if ($level !== NULL)
|
||
{
|
||
switch ($op)
|
||
{
|
||
// >
|
||
case 1:
|
||
return ($number > $level) ? $to_number : $number;
|
||
break;
|
||
// <
|
||
case -1:
|
||
return ($number < $level) ? $to_number : $number;
|
||
break;
|
||
// =
|
||
case 0:
|
||
return ($number == $level) ? $to_number : $number;
|
||
break;
|
||
// for all other
|
||
default:
|
||
return $to_number;
|
||
break;
|
||
}
|
||
}
|
||
else
|
||
// level is not given
|
||
return $to_number;
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Replace decimal comma with decimal point
|
||
* @param int $number
|
||
* @return string
|
||
... | ... | |
return str_replace(',', '.', $number);
|
||
}
|
||
|
||
|
||
}
|
freenetis/branches/ulogd/application/models/ip_addresses_traffic.php | ||
---|---|---|
<?php
|
||
/**
|
||
*
|
||
* @author Michal Kliment
|
||
* @copyright (c) 2010 Kliment Michal
|
||
* @license GNU GPLv3
|
||
* @license http://www.gnu.org/licenses/gpl.txt
|
||
*/
|
||
|
||
class Ip_addresses_traffic_Model extends ORM
|
||
{
|
||
|
||
/**
|
||
* Returns all traffics of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
* @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_ip_addresses_traffics($sql_offset = 0, $limit_results = 100, $order_by = 'total', $order_by_direction = 'DESC')
|
||
{
|
||
return $this->db->query("
|
||
SELECT t.ip_address, t.upload, t.download, (t.upload+t.download) AS total, m.id AS member_id, m.name AS member_name
|
||
FROM ip_addresses_traffics t
|
||
LEFT JOIN members m ON t.member_id = m.id
|
||
ORDER BY $order_by $order_by_direction
|
||
LIMIT $sql_offset,$limit_results
|
||
");
|
||
}
|
||
|
||
/**
|
||
* Counts all traffics of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
* @return integer
|
||
*/
|
||
public function count_all_ip_addresses_traffics()
|
||
{
|
||
return $this->db->query("SELECT COUNT(*) AS count FROM ip_addresses_traffics")->current()->count;
|
||
}
|
||
|
||
/**
|
||
* Cleans (truncate table) traffics of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function clean_ip_addresses_traffics()
|
||
{
|
||
$this->db->query("TRUNCATE ip_addresses_traffics;");
|
||
}
|
||
|
||
/**
|
||
* Inserts new traffics of ip addresses, from ulogd table
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function insert_ip_addresses_traffics()
|
||
{
|
||
$this->db->query("
|
||
INSERT INTO ip_addresses_traffics
|
||
SELECT ip2str(orig_ip_saddr) AS ip_address, SUM(orig_raw_pktlen/1024) AS upload, SUM(reply_raw_pktlen/1024) AS download, NULL AS member_id
|
||
FROM ulog2_ct
|
||
GROUP BY orig_ip_saddr;
|
||
");
|
||
}
|
||
|
||
/**
|
||
* Updates member's ids of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function update_member_ids()
|
||
{
|
||
// first for ip addresses of ifaces
|
||
|
||
$this->db->query("
|
||
UPDATE ip_addresses_traffics t, ip_addresses ip, ifaces i, devices d, users u
|
||
SET t.member_id = u.member_id
|
||
WHERE t.ip_address = ip.ip_address AND ip.iface_id = i.id AND i.device_id = d.id AND d.user_id = u.id
|
||
");
|
||
|
||
// now for ip addresses of vlan ifaces
|
||
$this->db->query("
|
||
UPDATE ip_addresses_traffics t, ip_addresses ip, vlan_ifaces vi, ifaces i, devices d, users u
|
||
SET t.member_id = u.member_id
|
||
WHERE t.ip_address = ip.ip_address AND ip.vlan_iface_id = vi.id AND vi.iface_id = i.id AND i.device_id = d.id AND d.user_id = u.id
|
||
");
|
||
}
|
||
}
|
||
|
||
?>
|
freenetis/branches/ulogd/application/models/members_traffic.php | ||
---|---|---|
<?php
|
||
/**
|
||
*
|
||
* @author Michal Kliment
|
||
* @copyright (c) 2010 Kliment Michal
|
||
* @license GNU GPLv3
|
||
* @license http://www.gnu.org/licenses/gpl.txt
|
||
*/
|
||
|
||
class Members_traffic_Model extends Model
|
||
{
|
||
|
||
/**
|
||
* Returns all traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @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($sql_offset = 0, $limit_results = 100, $order_by = 'total', $order_by_direction = 'DESC')
|
||
{
|
||
return $this->db->query("
|
||
SELECT t.upload, t.download, (t.upload+t.download) AS total, t.active, m.id AS member_id, m.name AS member_name
|
||
FROM members_traffics t
|
||
LEFT JOIN members m ON t.member_id = m.id
|
||
ORDER BY `$order_by` $order_by_direction
|
||
LIMIT ?,?", array(
|
||
$sql_offset,
|
||
$limit_results
|
||
));
|
||
}
|
||
|
||
/**
|
||
* Counts all traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @return integer
|
||
*/
|
||
public function count_all_members_traffics()
|
||
{
|
||
return $this->db->query("SELECT COUNT(*) AS count FROM members_traffics")->current()->count;
|
||
}
|
||
|
||
/**
|
||
* Cleans (truncate table) traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function clean_members_traffics()
|
||
{
|
||
$this->db->query("TRUNCATE members_traffics");
|
||
}
|
||
|
||
/**
|
||
* Inserts new traffics of members, from traffics of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function insert_members_traffics()
|
||
{
|
||
$this->db->query("INSERT INTO members_traffics
|
||
SELECT t.member_id, SUM(t.upload) AS upload, SUM(t.download) AS download, 0 AS active FROM
|
||
ip_addresses_traffics t
|
||
WHERE t.member_id IS NOT NULL
|
||
GROUP BY t.member_id");
|
||
}
|
||
|
||
/**
|
||
* Updates active members (e.g. for QoS)
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $ulogd_active_count
|
||
*/
|
||
public function update_active_members($ulogd_active_count)
|
||
{
|
||
$this->db->query("
|
||
UPDATE members_traffics
|
||
SET active = 1
|
||
WHERE member_id IN
|
||
(
|
||
SELECT alias.member_id FROM
|
||
(
|
||
SELECT t.member_id, (download+upload) AS total FROM members_traffics t
|
||
JOIN members m ON t.member_id = m.id AND (m.qos_rate IS NULL OR m.qos_rate = 0)
|
||
ORDER BY ".Settings::get('ulogd_active_type')." DESC LIMIT 0,$ulogd_active_count
|
||
) alias
|
||
)
|
||
");
|
||
}
|
||
}
|
||
|
||
?>
|
freenetis/branches/ulogd/application/models/members_traffics_daily.php | ||
---|---|---|
<?php
|
||
/**
|
||
*
|
||
* @author Michal Kliment
|
||
* @copyright (c) 2010 Kliment Michal
|
||
* @license GNU GPLv3
|
||
* @license http://www.gnu.org/licenses/gpl.txt
|
||
*/
|
||
|
||
class Members_traffics_daily_Model extends Model
|
||
{
|
||
|
||
/**
|
||
* 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')
|
||
{
|
||
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_daily d
|
||
WHERE day BETWEEN ? AND ?
|
||
GROUP BY member_id
|
||
) q
|
||
LEFT JOIN members m ON q.member_id = m.id
|
||
ORDER BY `$order_by` $order_by_direction
|
||
LIMIT ?, ?", array(
|
||
$date_from,
|
||
$date_to,
|
||
$sql_offset,
|
||
$limit_results
|
||
));
|
||
}
|
||
|
||
/**
|
||
* 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)
|
||
{
|
||
return $this->db->query("SELECT COUNT(*) AS count FROM (SELECT * FROM members_traffics_daily WHERE day BETWEEN ? AND ? GROUP BY member_id) AS q", array($date_from, $date_to))->current()->count;
|
||
}
|
||
|
||
/**
|
||
* Updates daily traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @param date $date
|
||
*/
|
||
public function updates_members_traffics_by_date($date)
|
||
{
|
||
$this->db->query("
|
||
UPDATE members_traffics t, members_traffics_daily d
|
||
SET d.upload = d.upload + t.upload, d.download = d.download + t.download
|
||
WHERE t.member_id = d.member_id AND d.day = ?;", array($date)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Inserts new daily traffics of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @param date $date
|
||
*/
|
||
public function insert_members_traffics_by_date($date)
|
||
{
|
||
$this->db->query("
|
||
INSERT INTO members_traffics_daily
|
||
SELECT t.member_id, t.upload, t.download, ? AS day FROM members_traffics t
|
||
LEFT JOIN members_traffics_daily d ON t.member_id = d.member_id AND d.day = ?
|
||
WHERE d.member_id IS NULL;", array($date,$date)
|
||
);
|
||
}
|
||
}
|
||
|
||
?>
|
freenetis/branches/ulogd/application/controllers/scheduler.php | ||
---|---|---|
// it's time to update
|
||
if (Settings::get('ulogd_update_last') + Settings::get('ulogd_update_interval') < time())
|
||
{
|
||
$db = new Database();
|
||
// traffics of ip addresses
|
||
$ip_addresses_traffic_model = new Ip_addresses_traffic_Model();
|
||
|
||
$db->query("TRUNCATE ip_addresses_traffics;");
|
||
// cleans traffics of ip addresses
|
||
$ip_addresses_traffic_model->clean_ip_addresses_traffics();
|
||
|
||
$db->query("
|
||
INSERT INTO ip_addresses_traffics
|
||
SELECT ip2str(orig_ip_saddr) AS ip_address, SUM(orig_raw_pktlen/1024) AS upload, SUM(reply_raw_pktlen/1024) AS download
|
||
FROM ulog2_ct
|
||
GROUP BY orig_ip_saddr;
|
||
");
|
||
// inserts new traffics of ip addresses
|
||
$ip_addresses_traffic_model->insert_ip_addresses_traffics();
|
||
|
||
// updates member's ids of ip addresses
|
||
$ip_addresses_traffic_model->update_member_ids();
|
||
|
||
// cleans ulogd table
|
||
$db = new Database();
|
||
$db->query("TRUNCATE ulog2_ct;");
|
||
|
||
$db->query("TRUNCATE members_traffics;");
|
||
// traffics of members
|
||
$members_traffic_model = new Members_traffic_Model();
|
||
|
||
$db->query("
|
||
INSERT INTO members_traffics
|
||
SELECT u.member_id, SUM(q.upload) AS upload, SUM(q.download) AS download, 0 AS active FROM
|
||
(
|
||
SELECT t.*, IFNULL(i.device_id,i2.device_id) AS device_id FROM ip_addresses_traffics t
|
||
JOIN ip_addresses ip ON t.ip_address = ip.ip_address
|
||
LEFT JOIN ifaces i ON ip.iface_id = i.id
|
||
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
||
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
||
) AS q
|
||
JOIN devices d ON q.device_id = d.id
|
||
JOIN users u ON d.user_id = u.id
|
||
GROUP BY u.member_id;
|
||
");
|
||
// cleans traffics of members
|
||
$members_traffic_model->clean_members_traffics();
|
||
|
||
// inserts new traffics of members
|
||
$members_traffic_model->insert_members_traffics();
|
||
|
||
// finding of count of active members
|
||
$ulogd_active_count = Settings::get('ulogd_active_count');
|
||
|
||
// count is in percents
|
||
if (substr($ulogd_active_count,-1) == "%")
|
||
{
|
||
$member_model = new Member_Model();
|
||
$total = $member_model->count_all_members_to_ulogd();
|
||
// finds total members to ulogd
|
||
$total = ORM::factory('member')->count_all_members_to_ulogd();
|
||
|
||
// calculates count of active members from percents
|
||
$ulogd_active_count = round(((int) substr($ulogd_active_count,0,-1)) / 100 * $total);
|
||
}
|
||
|
||
$db->query("
|
||
UPDATE members_traffics
|
||
SET active = 1
|
||
WHERE member_id IN
|
||
(
|
||
SELECT alias.member_id FROM
|
||
(
|
||
SELECT t.member_id, (download+upload) AS total FROM members_traffics t
|
||
JOIN members m ON t.member_id = m.id AND (m.qos_rate IS NULL OR m.qos_rate = 0)
|
||
ORDER BY ".Settings::get('ulogd_active_type')." DESC LIMIT 0,$ulogd_active_count
|
||
) alias
|
||
)
|
||
");
|
||
// updates active members
|
||
$members_traffic_model->update_active_members($ulogd_active_count);
|
||
|
||
$db->query("
|
||
UPDATE members_traffics t, members_traffics_daily d
|
||
SET d.upload = d.upload + t.upload, d.download = d.download + t.download
|
||
WHERE t.member_id = d.member_id AND d.day = '".date('Y-m-d')."';
|
||
");
|
||
// daily traffics of members
|
||
$members_traffics_daily_model = new Members_traffics_daily_Model();
|
||
|
||
$db->query("
|
||
INSERT INTO members_traffics_daily
|
||
SELECT t.member_id, t.upload, t.download, '".date('Y-m-d')."' AS day FROM members_traffics t
|
||
LEFT JOIN members_traffics_daily d ON t.member_id = d.member_id AND d.day = '".date('Y-m-d')."'
|
||
WHERE d.member_id IS NULL;
|
||
");
|
||
// updates daily traffics of members
|
||
$members_traffics_daily_model->updates_members_traffics_by_date(date('Y-m-d'));
|
||
|
||
// inserts new daily traffics of members
|
||
$members_traffics_daily_model->insert_members_traffics_by_date(date('Y-m-d'));
|
||
|
||
// updates variable
|
||
Settings::set('ulogd_update_last', time());
|
||
}
|
freenetis/branches/ulogd/application/controllers/settings.php | ||
---|---|---|
$view->render(TRUE);
|
||
}
|
||
|
||
/**
|
||
* Settings for logging
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function logging()
|
||
{
|
||
// access control
|
||
if (!$this->acl_check_edit(get_class($this),'system'))
|
||
Controller::error(ACCESS);
|
||
|
||
$additional_info = 'ulogd - '.url_lang::lang('texts.last update').': '.strftime("%Y/%m/%d %T", Settings::get('ulogd_update_last')).', <span';
|
||
// time of next update of ulogd
|
||
$ulogd_update_next = Settings::get('ulogd_update_last')+Settings::get('ulogd_update_interval');
|
||
|
||
if (Settings::get('ulogd_update_last')+Settings::get('ulogd_update_interval') < time())
|
||
// remaining time to next update of ulogd
|
||
$remaining_time = $ulogd_update_next - time();
|
||
|
||
$additional_info = '<b>ulogd</b> - '.url_lang::lang('texts.info').':<br />';
|
||
$additional_info .= url_lang::lang('texts.last update').': <b>'.date('Y-m-d H:i:s',Settings::get('ulogd_update_last')).'</b>,<br />';
|
||
$additional_info .= '<span';
|
||
|
||
// remaining time is in past, print it red
|
||
if ($remaining_time < 0)
|
||
$additional_info .= ' style="color:red"';
|
||
|
||
$additional_info .= '>'.url_lang::lang('texts.next update').': '.strftime('%Y/%m/%d %T', Settings::get('ulogd_update_last')+Settings::get('ulogd_update_interval')).'</span>';
|
||
$additional_info .= '>'.url_lang::lang('texts.next update').': <b>'.date('Y-m-d H:i:s',$ulogd_update_next).'</b> ('.url_lang::lang('texts.Remaining time').': <b><span id="rest">'.date::counter($remaining_time).'</span></b>)</span>';
|
||
|
||
// creating of new forge
|
||
$this->form = new Forge(NULL, '', 'POST', array('id' => 'article_form'));
|
||
... | ... | |
$this->form->dropdown('ulogd_active_type')->label(url_lang::lang('texts.Type of traffic').':')->rules('required')->options(array(0 => '----- '.url_lang::lang('texts.Select type').' -----', 'upload' => url_lang::lang('texts.upload'), 'download' => url_lang::lang('texts.download'),'total' => url_lang::lang('texts.both').' ('.url_lang::lang('texts.upload').' + '.url_lang::lang('texts.download').')'))->selected($this->settings->get('ulogd_active_type'))->help(help::hint('ulogd_active_type'));
|
||
$this->form->submit('submit')->value(url_lang::lang('texts.Save'));
|
||
special::required_forge_style($this->form, ' *', 'required');
|
||
|
||
// form validate
|
||
if ($this->form->validate())
|
||
{
|
||
... | ... | |
|
||
$view->extra_scripts = '$(document).ready(function(){
|
||
|
||
function update_inputs()
|
||
{
|
||
var driver = $("#clog_driver option:selected").val();
|
||
if (driver == "")
|
||
$("[id^=clog_]").not("[id=clog_driver]").attr("disabled",1).removeClass("required");
|
||
$.timer(1000, function (timer) {
|
||
rest = $("#rest").html();
|
||
|
||
arr = explode(":",rest);
|
||
|
||
t = parseInt(arr[2],10) + parseInt(arr[1],10)*60 + parseInt(arr[0],10)*3600;
|
||
|
||
if (t>0)
|
||
{
|
||
t = t-1;
|
||
|
||
s = t%60;
|
||
t = (t-s)/60;
|
||
|
||
m = t%60;
|
||
t = (t-m)/60;
|
||
|
||
h = t%24;
|
||
|
||
if (s<10)
|
||
s = "0"+s;
|
||
|
||
if (m<10)
|
||
m = "0"+m;
|
||
|
||
if (h<10)
|
||
h = "0"+h;
|
||
}
|
||
else
|
||
$("[id^=clog_]").not("[id=clog_driver]").removeAttr("disabled").addClass("required");
|
||
}
|
||
timer.stop();
|
||
|
||
update_inputs();
|
||
$("#rest").html(h+":"+m+":"+s);
|
||
});
|
||
|
||
$("#clog_driver").change(update_inputs);
|
||
});';
|
||
|
||
$view->title = url_lang::lang('texts.Settings').' - '.url_lang::lang('texts.logging');
|
freenetis/branches/ulogd/application/controllers/ulogd.php | ||
---|---|---|
<?php
|
||
/**
|
||
* Ulogd controller.
|
||
*
|
||
* @author Kliment Michal
|
||
* @copyright (c) 2011 Kliment Michal
|
||
* @license GNU GPLv3
|
||
* @license http://www.gnu.org/licenses/gpl.txt
|
||
*/
|
||
|
||
class Ulogd_Controller extends Controller
|
||
{
|
||
|
||
/**
|
||
* Contructs controller, only to setting of menu
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function __construct()
|
||
{
|
||
parent::__construct();
|
||
|
||
// contains items of menu
|
||
$this->sections = array(
|
||
'actual_ip_addresses_traffic' => url_lang::lang('texts.Actual traffic of IP addresses'),
|
||
'actual_members_traffic' => url_lang::lang('texts.Actual traffic of members'),
|
||
'time_members_traffic' => url_lang::lang('texts.Traffic of members in time')
|
||
);
|
||
|
||
// time of next update of ulogd
|
||
$this->ulogd_update_next = Settings::get('ulogd_update_last') + Settings::get('ulogd_update_interval');
|
||
|
||
// time of previous update ulogd
|
||
$this->ulogd_update_prev = Settings::get('ulogd_update_last') - Settings::get('ulogd_update_interval');
|
||
}
|
||
|
||
/**
|
||
* Redirects to actual traffic of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
*/
|
||
public function index()
|
||
{
|
||
url::redirect(url_lang::base().'ulogd/actual_ip_addresses_traffic');
|
||
}
|
||
|
||
/**
|
||
* Shows actual traffic of ip addresses
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $limit_results
|
||
* @param string $order_by
|
||
* @param string $order_by_direction
|
||
* @param string $page_word
|
||
* @param integer $page
|
||
*/
|
||
public function actual_ip_addresses_traffic($limit_results = 100, $order_by = '', $order_by_direction = 'DESC', $page_word = null, $page = 1)
|
||
{
|
||
// access control
|
||
if (!$this->acl_check_view(get_class($this),'ip_address'))
|
||
Controller::error(ACCESS);
|
||
|
||
// gets new selector
|
||
if (is_numeric($this->input->get('record_per_page')))
|
||
$limit_results = (int) $this->input->get('record_per_page');
|
||
|
||
// by default orders by ulogd's type of traffic of active members
|
||
if ($order_by == '')
|
||
$order_by = Settings::get('ulogd_active_type');
|
||
|
||
$ip_addresses_traffic_model = new Ip_addresses_traffic_Model();
|
||
|
||
// counts all traffics of ip addresses
|
||
$total_ip_addresses_traffics = $ip_addresses_traffic_model->count_all_ip_addresses_traffics();
|
||
|
||
if (($sql_offset = ($page - 1) * $limit_results) > $total_ip_addresses_traffics)
|
||
$sql_offset = 0;
|
||
|
||
// returns all traffics of ip addresses
|
||
$ip_addresses_traffics = $ip_addresses_traffic_model->get_all_ip_addresses_traffics($sql_offset, (int)$limit_results, $order_by, $order_by_direction);
|
||
|
||
// create grid
|
||
$grid = new Grid(url_lang::base().'actual_ip_addresses_traffic', '', array(
|
||
//'separator' => '',
|
||
'use_paginator' => true,
|
||
'use_selector' => true,
|
||
'current' => $limit_results, //current selected 'records_per_page' value
|
||
'selector_increace' => 100, // increace
|
||
'selector_min' => 100, // minimum where selector start
|
||
'selector_max_multiplier' => 20,
|
||
'base_url' => Config::get('lang').'/ulogd/actual_ip_addresses_traffic/'.$limit_results.'/'.$order_by.'/'.$order_by_direction ,
|
||
'uri_segment' => 'page', // pass a string as uri_segment to trigger former 'label' functionality
|
||
'total_items' => $total_ip_addresses_traffics, // use db count query here of course
|
||
'items_per_page' => $limit_results, // it may be handy to set defaults for stuff like this in config/pagination.php
|
||
'style' => 'classic',
|
||
'order_by' => $order_by,
|
||
'order_by_direction' => $order_by_direction,
|
||
'limit_results' => $limit_results,
|
||
));
|
||
|
||
// first number for order number field
|
||
$first_number = ($order_by_direction == 'DESC') ? $sql_offset : $total_ip_addresses_traffics-($page-1)*$limit_results+1;
|
||
|
||
// direction for order number field
|
||
$direction = ($order_by_direction == 'DESC') ? 1 : -1;
|
||
|
||
$grid->order_callback_field(Settings::get('ulogd_active_type'))->label(url_lang::lang('texts.Order'))->callback('callback::order_number_field',$first_number,$direction)->help(help::hint('ulogd_ip_address_order'))->class('right');
|
||
$grid->order_field('ip_address')->label(url_lang::lang('texts.Ip address'))->class('right');
|
||
$grid->order_callback_field('upload')->label(url_lang::lang('texts.Upload'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('download')->label(url_lang::lang('texts.Download'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('total')->label(url_lang::lang('texts.Total'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('member_id')->label(url_lang::lang('texts.Member'))->callback('callback::member_field');
|
||
|
||
$grid->datasource($ip_addresses_traffics);
|
||
|
||
$view = new View('main');
|
||
$view->title = url_lang::lang('texts.Actual traffic of ip addresses');
|
||
$view->content = new View('ulogd');
|
||
$view->content->headline = url_lang::lang('texts.Actual traffic of ip addresses');
|
||
$view->content->grid = $grid;
|
||
$view->content->current = 'actual_ip_addresses_traffic';
|
||
$view->content->text = url_lang::lang('texts.Traffic for the period').': '.date('Y/m/d H:i:s', $this->ulogd_update_prev) . ' - '.date('Y/m/d H:i:s', Settings::get('ulogd_update_last')).', '.url_lang::lang('texts.Next update').': '.date('Y/m/d H:i:s',$this->ulogd_update_next);
|
||
$view->render(TRUE);
|
||
}
|
||
|
||
/**
|
||
* Shows actual traffic of members
|
||
*
|
||
* @author Michal Kliment
|
||
* @param integer $limit_results
|
||
* @param string $order_by
|
||
* @param string $order_by_direction
|
||
* @param string $page_word
|
||
* @param integer $page
|
||
*/
|
||
public function actual_members_traffic($limit_results = 100, $order_by = '', $order_by_direction = 'DESC', $page_word = null, $page = 1)
|
||
{
|
||
// access control
|
||
if (!$this->acl_check_view(get_class($this),'member'))
|
||
Controller::error(ACCESS);
|
||
|
||
// gets new selector
|
||
if (is_numeric($this->input->get('record_per_page')))
|
||
$limit_results = (int) $this->input->get('record_per_page');
|
||
|
||
// by default orders by ulogd's type of traffic of active members
|
||
if ($order_by == '')
|
||
$order_by = Settings::get('ulogd_active_type');
|
||
|
||
$members_traffic_model = new Members_traffic_Model();
|
||
|
||
// counts all traffics of members
|
||
$total_members_traffics = $members_traffic_model->count_all_members_traffics();
|
||
|
||
if (($sql_offset = ($page - 1) * $limit_results) > $total_members_traffics)
|
||
$sql_offset = 0;
|
||
|
||
// returns all traffics of members
|
||
$members_traffics = $members_traffic_model->get_all_members_traffics($sql_offset, (int)$limit_results, $order_by, $order_by_direction);
|
||
|
||
// create grid
|
||
$grid = new Grid(url_lang::base().'actual_members_traffic', '', array(
|
||
//'separator' => '',
|
||
'use_paginator' => true,
|
||
'use_selector' => true,
|
||
'current' => $limit_results, //current selected 'records_per_page' value
|
||
'selector_increace' => 100, // increace
|
||
'selector_min' => 100, // minimum where selector start
|
||
'selector_max_multiplier' => 20,
|
||
'base_url' => Config::get('lang').'/ulogd/actual_members_traffic/'.$limit_results.'/'.$order_by.'/'.$order_by_direction ,
|
||
'uri_segment' => 'page', // pass a string as uri_segment to trigger former 'label' functionality
|
||
'total_items' => $total_members_traffics, // use db count query here of course
|
||
'items_per_page' => $limit_results, // it may be handy to set defaults for stuff like this in config/pagination.php
|
||
'style' => 'classic',
|
||
'order_by' => $order_by,
|
||
'order_by_direction' => $order_by_direction,
|
||
'limit_results' => $limit_results,
|
||
));
|
||
|
||
// first number for order number field
|
||
$first_number = ($order_by_direction == 'DESC') ? $sql_offset : $total_members_traffics-($page-1)*$limit_results+1;
|
||
|
||
// direction for order number field
|
||
$direction = ($order_by_direction == 'DESC') ? 1 : -1;
|
||
|
||
$grid->order_callback_field(Settings::get('ulogd_active_type'))->label(url_lang::lang('texts.Order'))->callback('callback::order_number_field',$first_number,$direction)->help(help::hint('ulogd_ip_address_order'))->class('right');
|
||
$grid->order_callback_field('member_id')->label(url_lang::lang('texts.Member'))->callback('callback::member_field');
|
||
$grid->order_callback_field('upload')->label(url_lang::lang('texts.Upload'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('download')->label(url_lang::lang('texts.Download'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('total')->label(url_lang::lang('texts.Total'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('active')->label(url_lang::lang('texts.Active'))->callback('callback::active_field')->class('center');
|
||
|
||
$grid->datasource($members_traffics);
|
||
|
||
$view = new View('main');
|
||
$view->title = url_lang::lang('texts.Actual traffic of members');
|
||
$view->content = new View('ulogd');
|
||
$view->content->headline = url_lang::lang('texts.Actual traffic of members');
|
||
$view->content->grid = $grid;
|
||
$view->content->current = 'actual_members_traffic';
|
||
$view->content->text = url_lang::lang('texts.Traffic for the period').': '.date('Y/m/d H:i:s', $this->ulogd_update_prev) . ' - '.date('Y/m/d H:i:s', Settings::get('ulogd_update_last')).', '.url_lang::lang('texts.Next update').': '.date('Y/m/d H:i:s',$this->ulogd_update_next);
|
||
$view->render(TRUE);
|
||
}
|
||
|
||
/**
|
||
* Shows traffic of members in time
|
||
*
|
||
* @author Michal Kliment
|
||
* @param date $date_from
|
||
* @param date $date_to
|
||
* @param integer $limit_results
|
||
* @param string $order_by
|
||
* @param string $order_by_direction
|
||
* @param string $page_word
|
||
* @param integer $page
|
||
*/
|
||
public function time_members_traffic($date_from = NULL, $date_to = NULL, $limit_results = 100, $order_by = '', $order_by_direction = 'DESC', $page_word = null, $page = 1)
|
||
{
|
||
// access control
|
||
if (!$this->acl_check_view(get_class($this),'member'))
|
||
Controller::error(ACCESS);
|
||
|
||
// gets new selector
|
||
if (is_numeric($this->input->get('record_per_page')))
|
||
$limit_results = (int) $this->input->get('record_per_page');
|
||
|
||
// date from is not valid, uses actual date
|
||
if (!$date_from || !valid::date_string($date_from))
|
||
$date_from = date('Y-m-d');
|
||
|
||
// date to is not valid, uses actual date
|
||
if (!$date_to || !valid::date_string($date_to) || $date_to < $date_from)
|
||
$date_to = $date_from;
|
||
|
||
// by default orders by ulogd's type of traffic of active members
|
||
if ($order_by == '')
|
||
$order_by = Settings::get('ulogd_active_type');
|
||
|
||
$members_traffics_daily_model = new Members_traffics_daily_Model();
|
||
|
||
// count all daily traffics of members
|
||
$total_members_traffics = $members_traffics_daily_model->count_all_members_traffics_by_dates($date_from, $date_to);
|
||
|
||
if (($sql_offset = ($page - 1) * $limit_results) > $total_members_traffics)
|
||
$sql_offset = 0;
|
||
|
||
// returns all daily traffics of members
|
||
$members_traffics = $members_traffics_daily_model->get_all_members_traffics_by_dates($date_from, $date_to, $sql_offset, (int)$limit_results, $order_by, $order_by_direction);
|
||
|
||
// create grid
|
||
$grid = new Grid(url_lang::base().'time_members_traffic', '', array(
|
||
//'separator' => '',
|
||
'use_paginator' => true,
|
||
'use_selector' => true,
|
||
'current' => $limit_results, //current selected 'records_per_page' value
|
||
'selector_increace' => 100, // increace
|
||
'selector_min' => 100, // minimum where selector start
|
||
'selector_max_multiplier' => 20,
|
||
'base_url' => Config::get('lang').'/ulogd/time_members_traffic/'.$date_from.'/'.$date_to.'/'.$limit_results.'/'.$order_by.'/'.$order_by_direction ,
|
||
'uri_segment' => 'page', // pass a string as uri_segment to trigger former 'label' functionality
|
||
'total_items' => $total_members_traffics, // use db count query here of course
|
||
'items_per_page' => $limit_results, // it may be handy to set defaults for stuff like this in config/pagination.php
|
||
'style' => 'classic',
|
||
'order_by' => $order_by,
|
||
'order_by_direction' => $order_by_direction,
|
||
'limit_results' => $limit_results,
|
||
'variables' => $date_from.'/'.$date_to.'/',
|
||
'url_array_ofset' => 2
|
||
));
|
||
|
||
// first number for order number field
|
||
$first_number = ($order_by_direction == 'DESC') ? $sql_offset : $total_members_traffics-($page-1)*$limit_results+1;
|
||
|
||
// direction for order number field
|
||
$direction = ($order_by_direction == 'DESC') ? 1 : -1;
|
||
|
||
$grid->order_callback_field(Settings::get('ulogd_active_type'))->label(url_lang::lang('texts.Order'))->callback('callback::order_number_field',$first_number,$direction)->help(help::hint('ulogd_ip_address_order'))->class('right');
|
||
$grid->order_callback_field('member_id')->label(url_lang::lang('texts.Member'))->callback('callback::member_field');
|
||
$grid->order_callback_field('upload')->label(url_lang::lang('texts.Upload'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('download')->label(url_lang::lang('texts.Download'))->callback('callback::traffic_field')->class('right');
|
||
$grid->order_callback_field('total')->label(url_lang::lang('texts.Total'))->callback('callback::traffic_field')->class('right');
|
||
|
||
$grid->datasource($members_traffics);
|
||
|
||
// form to select date
|
||
$form = new Forge(url::base().url::current(TRUE),'', 'POST', array('id' => 'article_form'));
|
||
$form->set_attr('class', 'form_class')->set_attr('method', 'post');
|
||
$form->group('')->label(url_lang::lang('texts.Choose date'));
|
||
$form->input('date_from')->label(url_lang::lang('texts.Date from').':')->rules('required|valid_date_string')->value($date_from);
|
||
$form->input('date_to')->label(url_lang::lang('texts.Date to').':')->rules('required|valid_date_string')->value($date_to);
|
||
$form->submit('submit')->value(url_lang::lang('texts.Save'));
|
||
special::required_forge_style($form, ' *', 'required');
|
||
|
||
// form is validate
|
||
if ($form->validate())
|
||
{
|
||
$form_data = $form->as_array();
|
||
|
||
// redirect to this page with new dates
|
||
url::redirect(url_lang::base().'ulogd/time_members_traffic/'.$form_data['date_from'].'/'.$form_data['date_to']);
|
||
}
|
||
|
||
$view = new View('main');
|
||
|
||
$view->extra_scripts .= '
|
||
$(document).ready(function(){
|
||
$(function() {
|
||
var dates = $( "#date_from, #date_to" ).datepicker({
|
||
dateFormat: "yy-mm-dd",
|
||
changeMonth: true,
|
||
changeYear: true,
|
||
onSelect: function( selectedDate ) {
|
||
var option = this.id == "date_from" ? "minDate" : "maxDate",
|
||
instance = $( this ).data( "datepicker" );
|
||
date = $.datepicker.parseDate(
|
||
instance.settings.dateFormat ||
|
||
$.datepicker._defaults.dateFormat,
|
||
selectedDate, instance.settings );
|
||
dates.not( this ).datepicker( "option", option, date );
|
||
}
|
||
});
|
||
$.datepicker.regional["' . Config::get('lang') . '"];
|
||
})
|
||
});';
|
||
|
||
$view->title = url_lang::lang('texts.Traffic of members in time');
|
||
$view->content = new View('ulogd');
|
||
$view->content->headline = url_lang::lang('texts.Traffic of members in time');
|
||
$view->content->grid = $form->html().'<br /><br />'.$grid;
|
||
$view->content->current = 'time_members_traffic';
|
||
$view->render(TRUE);
|
||
}
|
||
}
|
||
|
||
?>
|
freenetis/branches/ulogd/application/upgrade_sql/upgrade_sql.php | ||
---|---|---|
return $svnid;
|
||
}
|
||
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// Remove phone, email columns in users table with transformation of data
|
||
// to the table contacts and its relations tables
|
||
// @author Ondřej Fibich
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// array of sql queries that upgrade database
|
||
$upgrade_sql[get_SVN_rev()] = array(
|
||
|
||
function upgrade_sql_before()
|
||
{
|
||
$contact = new Contact_Model();
|
||
$default_country = new Country_Model(Settings::get('default_country'));
|
||
"INSERT INTO `axo_sections` (`id`, `value`, `order_value`, `name`, `hidden`) VALUES ('26', 'Ulogd_Controller', '1', 'Network traffics of ip addresses and members', '0');",
|
||
|
||
if (!$default_country || !$default_country->id)
|
||
{
|
||
return false;
|
||
}
|
||
"INSERT INTO `axo` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES ('172', 'Ulogd_Controller', 'ip_address', '0', 'IP address', '0'), ('173', 'Ulogd_Controller', 'member', '0', 'Member', '0');",
|
||
|
||
try
|
||
{
|
||
// transaction
|
||
$contact->transaction_start();
|
||
"INSERT INTO `axo_map` (`acl_id`,`section_value`,`value`) VALUES ('38', 'Ulogd_Controller', 'ip_address'), ('38', 'Ulogd_Controller', 'member');",
|
||
|
||
$users = ORM::factory('user')->find_all();
|
||
"INSERT INTO `axo_map` (`acl_id`,`section_value`,`value`) VALUES ('49', 'Ulogd_Controller', 'ip_address'), ('49', 'Ulogd_Controller', 'member');",
|
||
|
||
foreach ($users as $user)
|
||
{
|
||
$email = trim($user->email);
|
||
$phone = trim($user->phone);
|
||
"CREATE TABLE IF NOT EXISTS `ip_addresses_traffics` (
|
||
`ip_address` varchar(15) COLLATE utf8_czech_ci NOT NULL,
|
||
`upload` int(11) unsigned NOT NULL,
|
||
`download` int(11) unsigned NOT NULL,
|
||
`member_id` int(11) DEFAULT NULL,
|
||
PRIMARY KEY (`ip_address`),
|
||
KEY `member_id` (`member_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",
|
||
|
||
////////////////////////////////////////////////////////
|
||
// EMAIL
|
||
// is there something to add?
|
||
if (!empty($email))
|
||
{
|
||
$contact_id = $contact->find_contact_id(
|
||
Contact_Model::TYPE_EMAIL, $email
|
||
);
|
||
"CREATE TABLE IF NOT EXISTS `members_traffics` (
|
||
`member_id` int(11) NOT NULL,
|
||
`upload` int(11) unsigned NOT NULL,
|
||
`download` int(11) unsigned NOT NULL,
|
||
`active` tinyint(1) NOT NULL DEFAULT '0',
|
||
PRIMARY KEY (`member_id`),
|
||
KEY `active` (`active`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",
|
||
|
||
// contact is not in database?
|
||
if (!$contact_id)
|
||
{
|
||
// clear contact
|
||
$contact->clear();
|
||
// add contact
|
||
$contact->type = Contact_Model::TYPE_EMAIL;
|
||
$contact->value = $email;
|
||
$contact->save_throwable();
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
else
|
||
{
|
||
$contact = $contact->find($contact_id);
|
||
// is already users contact?
|
||
if (!$contact->is_users_contact($user->id))
|
||
{
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
}
|
||
"CREATE TABLE IF NOT EXISTS `members_traffics_daily` (
|
||
`member_id` int(11) NOT NULL,
|
||
`upload` int(11) unsigned NOT NULL,
|
||
`download` int(11) unsigned NOT NULL,
|
||
`day` date NOT NULL,
|
||
PRIMARY KEY (`member_id`,`day`),
|
||
KEY `member_id` (`member_id`),
|
||
KEY `day` (`day`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",
|
||
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
"CREATE TABLE IF NOT EXISTS `ulog2_ct` (
|
||
`_ct_id` tinyint(4) DEFAULT NULL,
|
||
`orig_ip_saddr` int(10) unsigned DEFAULT NULL,
|
||
`orig_ip_daddr` int(10) unsigned DEFAULT NULL,
|
||
`orig_ip_protocol` tinyint(3) unsigned DEFAULT NULL,
|
||
`orig_l4_sport` smallint(5) unsigned DEFAULT NULL,
|
||
`orig_l4_dport` smallint(5) unsigned DEFAULT NULL,
|
||
`orig_raw_pktlen` bigint(20) DEFAULT '0',
|
||
`orig_raw_pktcount` int(10) unsigned DEFAULT '0',
|
||
`reply_ip_daddr` int(10) unsigned DEFAULT NULL,
|
||
`reply_l4_dport` smallint(5) unsigned DEFAULT NULL,
|
||
`reply_raw_pktlen` bigint(20) DEFAULT '0',
|
||
`reply_raw_pktcount` int(10) unsigned DEFAULT '0',
|
||
`icmp_code` tinyint(3) DEFAULT NULL,
|
||
`icmp_type` tinyint(3) DEFAULT NULL,
|
||
`flow_start_sec` int(10) DEFAULT '0',
|
||
`flow_end_sec` int(10) DEFAULT '0',
|
||
KEY `ct_tuple` (`flow_start_sec`,`orig_ip_daddr`,`orig_l4_dport`,`reply_l4_dport`)
|
||
) ENGINE=MyISAM DEFAULT CHARSET=latin1;"
|
||
|
||
////////////////////////////////////////////////////////
|
||
// PHONE
|
||
// is there something to add?
|
||
if (!empty($phone))
|
||
{
|
||
$contact_id = $contact->find_contact_id(
|
||
Contact_Model::TYPE_PHONE, $phone
|
||
);
|
||
|
||
// contact is not in database?
|
||
if (!$contact_id)
|
||
{
|
||
$prefix = false;
|
||
// clear contact
|
||
$contact->clear();
|
||
// has prefix?
|
||
if (mb_strlen($phone) > 9)
|
||
{
|
||
$prefix = $contact->find_phone_country_code($phone);
|
||
// remove prefix
|
||
$phone = mb_substr($phone, mb_strlen($prefix));
|
||
}
|
||
// add contact
|
||
$contact->type = Contact_Model::TYPE_PHONE;
|
||
$contact->value = $phone;
|
||
$contact->save_throwable();
|
||
// add country
|
||
if ($prefix)
|
||
{ // not default country
|
||
$country_id = $contact->find_phone_country($prefix.$phone);
|
||
$country = new Country_Model($country_id);
|
||
$contact->add($country);
|
||
}
|
||
else
|
||
{
|
||
$contact->add($default_country);
|
||
}
|
||
$contact->save_throwable();
|
||
// add relation between user and phone
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
else
|
||
{
|
||
$contact = $contact->find($contact_id);
|
||
// is already users contact?
|
||
if (!$contact->is_users_contact($user->id))
|
||
{
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// commit
|
||
$contact->transaction_commit();
|
||
}
|
||
catch (Exception $e)
|
||
{
|
||
$contact->transaction_rollback();
|
||
return false;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
// array of sql queries that upgrade database
|
||
$upgrade_sql[get_SVN_rev()] = array(
|
||
|
||
// delete phone column
|
||
"ALTER TABLE users DROP phone",
|
||
|
||
// delete emial column
|
||
"ALTER TABLE users DROP email"
|
||
|
||
);
|
||
?>
|
freenetis/branches/ulogd/application/upgrade_sql/upgrade_sql_842.php | ||
---|---|---|
<?php
|
||
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// Remove phone, email columns in users table with transformation of data
|
||
// to the table contacts and its relations tables
|
||
// @author Ondřej Fibich
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
|
||
function upgrade_sql_842_before()
|
||
{
|
||
$contact = new Contact_Model();
|
||
$default_country = new Country_Model(Settings::get('default_country'));
|
||
|
||
if (!$default_country || !$default_country->id)
|
||
{
|
||
return false;
|
||
}
|
||
|
||
try
|
||
{
|
||
// transaction
|
||
$contact->transaction_start();
|
||
|
||
$users = ORM::factory('user')->find_all();
|
||
|
||
foreach ($users as $user)
|
||
{
|
||
$email = trim($user->email);
|
||
$phone = trim($user->phone);
|
||
|
||
////////////////////////////////////////////////////////
|
||
// EMAIL
|
||
// is there something to add?
|
||
if (!empty($email))
|
||
{
|
||
$contact_id = $contact->find_contact_id(
|
||
Contact_Model::TYPE_EMAIL, $email
|
||
);
|
||
|
||
// contact is not in database?
|
||
if (!$contact_id)
|
||
{
|
||
// clear contact
|
||
$contact->clear();
|
||
// add contact
|
||
$contact->type = Contact_Model::TYPE_EMAIL;
|
||
$contact->value = $email;
|
||
$contact->save_throwable();
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
else
|
||
{
|
||
$contact = $contact->find($contact_id);
|
||
// is already users contact?
|
||
if (!$contact->is_users_contact($user->id))
|
||
{
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
}
|
||
|
||
// add relation between user and email
|
||
$user->add($contact);
|
||
$user->save_throwable();
|
||
}
|
||
|
||
////////////////////////////////////////////////////////
|
||
// PHONE
|
||
// is there something to add?
|
||
if (!empty($phone))
|
||
{
|
||
$contact_id = $contact->find_contact_id(
|
||
Contact_Model::TYPE_PHONE, $phone
|
||
);
|
||
|
||
// contact is not in database?
|
||
if (!$contact_id)
|
||
{
|
||
$prefix = false;
|
||
// clear contact
|
||
$contact->clear();
|
||
// has prefix?
|
||
if (mb_strlen($phone) > 9)
|
||
{
|
||
$prefix = $contact->find_phone_country_code($phone);
|
||
// remove prefix
|
||
$phone = mb_substr($phone, mb_strlen($prefix));
|
||
}
|
||
// add contact
|
||
$contact->type = Contact_Model::TYPE_PHONE;
|
||
$contact->value = $phone;
|
||
$contact->save_throwable();
|
Také k dispozici: Unified diff
Pridan novy kontroler ulogd pro zobrazeni dat ziskanych pomoci ulogd. Pridany databazovych tabulek pro ulogd.