Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 1411

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

Upravy:
- uprava IP adres pro #185

Zobrazit rozdíly:

freenetis/branches/network/application/i18n/cs_CZ/texts.php
'ip address detail' => 'Detail IP adresy',
'ip address does not match the subnet/mask' => 'IP adresa neodpovídá podsíti/masce.',
'ip address has been successfully deleted' => 'IP adresa byla úspěšně smazána.',
'ip address has been successfully updated' => 'IP adresa byla úspěšně upravena.',
'ip address is already monitored' => 'IP adresa je už monitorována',
'ip address is required' => 'IP adresa je vyžadována.',
'ip address is successfully saved' => 'IP adresa byla úspěšně uložena.',
freenetis/branches/network/application/models/ip_address.php
* @property bool $service
* @property integer $whitelisted
* @property bool $redirect
* @property integer $member_id
*/
class Ip_address_Model extends ORM
{
......
const TEMPORARY_WHITELIST = 2;
/**
* White list type names
*
* @var array
*/
public static $whitelist_types = array
(
self::NO_WHITELIST => 'No whitelist',
self::PERNAMENT_WHITELIST => 'Permanent whitelist',
self::TEMPORARY_WHITELIST => 'Temporary whitelist'
);
/**
* Gets translated whitelist types
*
* @return array
*/
public static function get_whitelist_types()
{
return array_map('__', self::$whitelist_types);
}
/**
* Gets translated whitelist types
*
* @param integer $white_list_type
* @return string
*/
public function get_whitelist_type($white_list_type = NULL)
{
if (empty($white_list_type) && $this->id)
{
$white_list_type = $this->whitelisted;
}
if (array_key_exists($white_list_type, self::$whitelist_types))
{
return __(self::$whitelist_types[$white_list_type]);
}
return __(self::$whitelist_types[self::NO_WHITELIST]);
}
/**
* Gives IP address is string is writted
*
* @return string
......
SELECT * FROM
(
SELECT ip.*, ip.id AS ip_address_id,
IFNULL(i1.name, i2.name) AS iface_name,
vi.name AS vlan_iface_name,
s.name as subnet_name,
i.name AS iface_name, s.name as subnet_name,
d.id AS device_id, d.name AS device_name
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON i1.id = ip.iface_id
LEFT JOIN vlan_ifaces vi ON vi.id = ip.VLAN_iface_id
LEFT JOIN ifaces i2 ON i2.id = vi.iface_id
LEFT JOIN devices d ON d.id = IFNULL(i1.device_id, i2.device_id)
LEFT JOIN ifaces i ON i.id = ip.iface_id
LEFT JOIN ifaces_vlans iv ON iv.iface_id = i.id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN subnets s ON s.id = ip.subnet_id
WHERE ip.member_id IS NULL
) ip
......
* Function counts all ip addresses.
*
* @param array $filter_values
* @return unknown_type
* @return integer
*/
public function count_all_ip_addresses($filter_sql = '')
{
......
FROM
(
SELECT ip.*, ip.id AS ip_address_id,
IFNULL(i1.name, i2.name) AS iface_name,
vi.name AS vlan_iface_name,
s.name as subnet_name,
i.name AS iface_name, s.name as subnet_name,
d.id AS device_id, d.name AS device_name
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON i1.id = ip.iface_id
LEFT JOIN vlan_ifaces vi ON vi.id = ip.VLAN_iface_id
LEFT JOIN ifaces i2 ON i2.id = vi.iface_id
LEFT JOIN devices d ON d.id = IFNULL(i1.device_id, i2.device_id)
LEFT JOIN ifaces i ON i.id = ip.iface_id
LEFT JOIN ifaces_vlans iv ON iv.iface_id = i.id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN subnets s ON s.id = ip.subnet_id
WHERE ip.member_id IS NULL
) ip
$where
$where
")->current()->total;
}
......
* Function gets all ip address of interfaces of devices of users of given member.
*
* @param integer $member_id
* @param integer $subnet_id
* @param integer $cloud_id
* @return Mysql_Result
*/
public function get_ip_addresses_of_member($member_id, $subnet_id = NULL, $cloud_id = NULL)
{
$subnet_where = "";
$where = "";
if ($subnet_id)
$subnet_where = "AND ip.subnet_id = ".intval($subnet_id);
{
$where = "AND ip.subnet_id = ".intval($subnet_id);
}
$cloud_where = "";
if ($cloud_id)
$cloud_where = "WHERE cs.cloud_id = ".intval($cloud_id);
{
$where .= " AND cs.cloud_id = ".intval($cloud_id);
}
return $this->db->query("
SELECT ip.*
FROM
(
SELECT ip.*,
IFNULL(i1.device_id, i2.device_id) AS device_id
FROM ip_addresses ip
LEFT JOIN subnets s ON ip.subnet_id = s.id
LEFT JOIN clouds_subnets cs ON cs.subnet_id = s.id
LEFT JOIN ifaces i1 ON i1.id = ip.iface_id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON i2.id = vi.iface_id
$cloud_where
GROUP BY ip.id
) ip
LEFT JOIN devices d ON d.id = ip.device_id
SELECT ip.*, i.device_id
FROM ip_addresses ip
LEFT JOIN subnets s ON ip.subnet_id = s.id
LEFT JOIN clouds_subnets cs ON cs.subnet_id = s.id
LEFT JOIN ifaces i ON i.id = ip.iface_id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN users u ON u.id = d.user_id
WHERE IFNULL(u.member_id, ip.member_id) = ?
$subnet_where
WHERE IFNULL(u.member_id, ip.member_id) = ? $where
", $member_id);
}
public function get_ip_addresses_of_members($member_ids = array(), $message_id = NULL)
{
if (!count($member_ids))
return false;
return $this->db->query("
SELECT
ip.*, m.id AS member_id, m.name AS member_name,
IF(mip.ip_address_id IS NOT NULL, 1, 0) AS active,
1 AS redirection
FROM
(
SELECT
ip.id, ip.ip_address, ip.whitelisted, subnet_id,
IFNULL(u.member_id, ip.member_id) AS member_id
FROM
(
SELECT ip.*,
IFNULL(i1.device_id, i2.device_id) AS device_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON i1.id = ip.iface_id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON i2.id = vi.iface_id
) ip
LEFT JOIN devices d ON d.id = ip.device_id
LEFT JOIN users u ON u.id = d.user_id
) ip
JOIN members m ON ip.member_id = m.id
LEFT JOIN messages_ip_addresses mip
ON mip.ip_address_id = ip.id AND mip.message_id = ?
WHERE member_id = " .
implode(" OR member_id = ", $member_ids) . "
ORDER BY active DESC, m.id ASC, INET_ATON(ip_address) ASC"
, array($message_id));
}
/**
* Gets all ip addresses of device.
*
......
/**
* Function gets ip addresses of subnet.
*
* @param $subnet_id
* @param integer $subnet_id
* @return Mysql_Result
*/
public function get_ip_addresses_of_subnet($subnet_id)
{
return $this->db->query("
SELECT q.ip_address_id AS id, q.ip_address_id, q.ip_address,
q.gateway, d.name AS device_name, d.id AS device_id,
SELECT ip.id, ip.id AS ip_address_id, ip.ip_address,
ip.gateway, d.name AS device_name, d.id AS device_id,
m.name AS member_name, m.id AS member_id, a.balance,
u.name AS user_name, u.surname AS user_surname, mac
FROM
(
SELECT ip.id AS ip_address_id, ip.ip_address, ip.gateway,
IFNULL(ic1.device_id, ic2.device_id) as device_id,
IFNULL(ic1.mac,ic2.mac) AS mac
FROM ip_addresses ip
LEFT JOIN ifaces ic1 ON ip.iface_id = ic1.id
LEFT JOIN vlan_ifaces vic ON ip.vlan_iface_id = vic.id
LEFT JOIN ifaces ic2 ON vic.iface_id = ic2.id
WHERE ip.subnet_id = ? AND ip.member_id IS NULL
) AS q
LEFT JOIN devices d ON q.device_id = d.id
u.name AS user_name, u.surname AS user_surname, i.mac
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
LEFT JOIN devices d ON i.device_id = d.id
LEFT JOIN users u ON d.user_id = u.id
LEFT JOIN members m ON u.member_id = m.id
LEFT JOIN accounts a ON a.member_id = m.id AND account_attribute_id = ?
ORDER BY inet_aton(q.ip_address)
", $subnet_id, Account_attribute_Model::CREDIT);
WHERE ip.subnet_id = ? AND ip.member_id IS NULL
ORDER BY inet_aton(ip.ip_address)
", Account_attribute_Model::CREDIT, $subnet_id);
}
/**
* Gets all IP addresses of member who have currently interrupted membership.
* These IP addresses are redirected.
* Gets all IDs of IP addresses of member who have currently interrupted
* membership. These IP addresses are redirected.
*
* @author Jiri Svitak
* @return Mysql_Result
......
FROM
(
SELECT ip.id, ip.ip_address, ip.whitelisted,
s.name AS subnet_name,
IFNULL(u.member_id, ip.member_id) AS member_id
FROM
(
SELECT ip.id, ip.id AS ip_address_id, ip.ip_address,
ip.whitelisted, IFNULL(i1.device_id,i2.device_id) AS device_id,
ip.member_id, ip.subnet_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) ip
JOIN subnets s ON s.id = ip.subnet_id
LEFT JOIN devices d ON d.id = ip.device_id
s.name AS subnet_name,
IFNULL(u.member_id, ip.member_id) AS member_id
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
LEFT JOIN subnets s ON s.id = ip.subnet_id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN users u ON u.id = d.user_id
) ip
JOIN members m ON m.id = ip.member_id
......
JOIN members_fees mf ON mi.members_fee_id = mf.id
JOIN fees f ON f.id = mf.fee_id
JOIN accounts a ON a.member_id = m.id
WHERE mf.activation_date <= CURDATE() AND
CURDATE() <= mf.deactivation_date AND
f.special_type_id = ".Fee_Model::MEMBERSHIP_INTERRUPT."
");
WHERE mf.activation_date <= CURDATE() AND f.special_type_id = ? AND
CURDATE() <= mf.deactivation_date
", Fee_Model::MEMBERSHIP_INTERRUPT);
}
/**
* Returns all ip addresses with unallowed connecting place
* Returns all IDs of IP addresses with unallowed connecting place
*
* @author Michal Kliment
* @return Mysql_Result
......
return $this->db->query("
SELECT q.id, ip_address FROM
(
SELECT subnet_id, q.id, ip_address, IFNULL(q.member_id, u.member_id) AS member_id FROM
(
SELECT ip.ip_address, ip.id, ip.subnet_id,
IFNULL(i1.device_id, i2.device_id) AS device_id, ip.member_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.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
LEFT JOIN devices d ON q.device_id = d.id
SELECT ip.subnet_id, ip.id, ip.ip_address,
IFNULL(ip.member_id, u.member_id) AS member_id
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
LEFT JOIN devices d ON i.device_id = d.id
LEFT JOIN users u ON d.user_id = u.id
) q
WHERE q.member_id <> 1 AND q.subnet_id NOT IN
(
SELECT a.subnet_id
FROM allowed_subnets a
WHERE a.subnet_id = q.subnet_id AND a.member_id = q.member_id AND enabled = 1
WHERE a.subnet_id = q.subnet_id AND
a.member_id = q.member_id AND enabled = 1
)
");
}
/**
* Gets all IP addresses of members who have credit negative credit status.
*
* @author Jiri Svitak
* @param duble $debtor_boundary
* @return Mysql_Result
......
public function get_ip_addresses_of_debtors($debtor_boundary)
{
return $this->db->query("
SELECT ip.id, ip.ip_address, ip.whitelisted,
subnet_name, m.name AS member_name,
SELECT ip.id, ip.ip_address, ip.whitelisted, subnet_name,
m.name AS member_name, a.balance,
(
SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol
FROM variable_symbols vs
LEFT JOIN accounts a ON a.id = vs.account_id
WHERE a.member_id = m.id
) AS variable_symbol,
a.balance
) AS variable_symbol
FROM
(
SELECT ip.id, ip.ip_address, ip.whitelisted,
s.name AS subnet_name,
(
SELECT ip.id, ip.ip_address, ip.whitelisted, s.name AS subnet_name,
IFNULL(u.member_id, ip.member_id) AS member_id
FROM
(
SELECT ip.id, ip.id AS ip_address_id, ip.ip_address,
ip.whitelisted, IFNULL(i1.device_id,i2.device_id) AS device_id,
ip.member_id, ip.subnet_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) ip
JOIN subnets s ON s.id = ip.subnet_id
LEFT JOIN devices d ON d.id = ip.device_id
LEFT JOIN users u ON u.id = d.user_id
) ip
JOIN members m ON m.id = ip.member_id
JOIN accounts a ON a.member_id = m.id AND m.id <> ?
WHERE a.balance < ?
AND DATEDIFF(CURDATE(), m.entrance_date) >= ?
AND (ip.whitelisted IS NULL OR ip.whitelisted = 0)
",
array
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
JOIN subnets s ON s.id = ip.subnet_id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN users u ON u.id = d.user_id
) ip
JOIN members m ON m.id = ip.member_id
JOIN accounts a ON a.member_id = m.id AND m.id <> ?
WHERE a.balance < ?
AND DATEDIFF(CURDATE(), m.entrance_date) >= ?
AND (ip.whitelisted IS NULL OR ip.whitelisted = 0)
", array
(
Member_Model::ASSOCIATION,
$debtor_boundary,
Member_Model::ASSOCIATION, $debtor_boundary,
Settings::get('initial_debtor_immunity')
));
}
......
/**
* Gets all IP addresses of members who have low credit and should pay
* in short time.
*
* @author Jiri Svitak
* @param double $payment_notice_boundary
* @param double $debtor_boundary
......
public function get_ip_addresses_of_almostdebtors($payment_notice_boundary, $debtor_boundary)
{
return $this->db->query("
SELECT ip.id, ip.ip_address, ip.whitelisted,
subnet_name, m.name AS member_name,
SELECT ip.id, ip.ip_address, ip.whitelisted, subnet_name,
m.name AS member_name, a.balance,
(
SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol
FROM variable_symbols vs
LEFT JOIN accounts a ON a.id = vs.account_id
WHERE a.member_id = m.id
) AS variable_symbol,
a.balance
) AS variable_symbol
FROM
(
SELECT ip.id, ip.ip_address, ip.whitelisted,
s.name AS subnet_name,
IFNULL(u.member_id, ip.member_id) AS member_id
FROM
(
SELECT ip.id, ip.id AS ip_address_id, ip.ip_address,
ip.whitelisted, IFNULL(i1.device_id,i2.device_id) AS device_id,
ip.member_id, ip.subnet_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) ip
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
JOIN subnets s ON s.id = ip.subnet_id
LEFT JOIN devices d ON d.id = ip.device_id
LEFT JOIN devices d ON d.id = i.device_id
LEFT JOIN users u ON u.id = d.user_id
) ip
JOIN members m ON m.id = ip.member_id
JOIN accounts a ON a.member_id = m.id AND m.id <> ?
WHERE
(
DATEDIFF(CURDATE(), m.entrance_date) >= ? AND a.balance >= ?
OR DATEDIFF(CURDATE(), m.entrance_date) < ? AND DATEDIFF(CURDATE(), m.entrance_date) >= ?
WHERE (
DATEDIFF(CURDATE(), m.entrance_date) >= ? AND
a.balance >= ? OR DATEDIFF(CURDATE(), m.entrance_date) < ? AND
DATEDIFF(CURDATE(), m.entrance_date) >= ?
)
AND a.balance < ?
AND (ip.whitelisted IS NULL OR ip.whitelisted = 0)
",
array
", array
(
Member_Model::ASSOCIATION,
Settings::get('initial_debtor_immunity'),
$debtor_boundary,
Settings::get('initial_debtor_immunity'),
Settings::get('initial_immunity'),
$payment_notice_boundary
Member_Model::ASSOCIATION, Settings::get('initial_debtor_immunity'),
$debtor_boundary, Settings::get('initial_debtor_immunity'),
Settings::get('initial_immunity'), $payment_notice_boundary
));
}
......
* @author Michal Kliment
* @return Mysql_Result
*/
public function get_ip_addresses_with_self_cancel ()
public function get_ip_addresses_with_self_cancel()
{
return $this->db->query("
SELECT * FROM
(
SELECT * FROM
(
SELECT ip.*, IFNULL(m.self_cancel,0) AS self_cancel
SELECT ip.*, IFNULL(m.self_cancel, 0) AS self_cancel
FROM ip_addresses ip
JOIN messages_ip_addresses mip ON mip.ip_address_id = ip.id
JOIN messages m ON mip.message_id = m.id
......
/**
* Gets all ip addresses including their redirections. Used in member's profile screen.
* Gets all ip addresses including their redirections.
* Used in member's profile screen.
*
* @author Jiri Svitak
* @param integer $member_id
* @return Mysql_Result
......
public function get_ips_and_redirections_of_member($member_id)
{
return $this->db->query("
SELECT ip.ip_address_id, ip.ip_address, ip.whitelisted,
SELECT ip.id AS ip_address_id, ip.ip_address, ip.whitelisted,
m.id AS message_id, m.name AS message, m.type, ? AS member_id
FROM
(
SELECT ip.id, ip.id AS ip_address_id, ip.ip_address,
ip.whitelisted, IFNULL(i1.device_id,i2.device_id) AS device_id,
ip.member_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) ip
LEFT JOIN devices d ON ip.device_id = d.id
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
LEFT JOIN devices d ON i.device_id = d.id
LEFT JOIN users u ON d.user_id = u.id
LEFT JOIN messages_ip_addresses mip ON mip.ip_address_id = ip.id
LEFT JOIN messages m ON m.id = mip.message_id
WHERE u.member_id = ? OR ip.member_id = ?
ORDER BY inet_aton(ip.ip_address) ASC, m.self_cancel DESC, mip.datetime ASC
ORDER BY inet_aton(ip.ip_address) ASC,
m.self_cancel DESC, mip.datetime ASC
", $member_id, $member_id, $member_id);
}
/**
* Returns all ip addresses from subnet with set owner and without ip addresses from ip_addresses table
*
* @author Michal Kliment
* @param array $ip_array
* @return Mysql_Result object
*/
public function get_all_ip_addresses_with_owner($ip_array)
{
if (!is_array($ip_array) || !count($ip_array))
{
return FALSE;
}
$subq = array();
foreach ($ip_array as $ip)
{
$subq[] = "SELECT " . $this->db->escape($ip) . " AS ip_address\n";
}
return $this->db->query("
SELECT ip_address FROM
(" . implode("UNION\n",$subq) . ") AS q
WHERE q.ip_address NOT IN (
SELECT ip.ip_address
FROM ip_addresses ip
WHERE ip.ip_address = q.ip_address
)
");
}
/**
* Clear redirect state of ip addresses
*
* @author Michal Kliment
......
}
/**
* Updates redirect state of ip_addresses
*
* @author Michal Kliment
*/
public function update_allowed_ips()
{
$this->clear_disable_ips();
$this->db->query("
UPDATE ip_addresses ip,
(
SELECT ip_address_id FROM
(
SELECT ip.id AS ip_address_id, ip.subnet_id,
IFNULL(i1.device_id, i2.device_id) AS device_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.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
WHERE u.member_id <> 1 AND q.subnet_id NOT IN (
SELECT a.subnet_id
FROM allowed_subnets a
WHERE a.subnet_id = q.subnet_id AND a.member_id = u.member_id AND enabled = 1
)
) q
SET ip.redirect = ip.redirect | 1
WHERE ip.id = ip_address_id
");
}
/**
* Counts all ip addresses by member and subnet
*
* @author Michal Kliment
......
public function count_all_ip_addresses_by_member_and_subnet($member_id, $subnet_id)
{
return $this->db->query("
SELECT COUNT(*) AS count FROM
(
SELECT ip.subnet_id, IFNULL(i1.device_id, i2.device_id) AS device_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) AS ip
JOIN devices d ON ip.device_id = d.id
JOIN users u ON d.user_id = u.id
WHERE u.member_id = ? AND ip.subnet_id = ?
SELECT COUNT(*) AS count
FROM ip_addresses ip
JOIN ifaces i ON ip.iface_id = i.id
JOIN devices d ON i.device_id = d.id
JOIN users u ON d.user_id = u.id
WHERE u.member_id = ? AND ip.subnet_id = ?
", array($member_id, $subnet_id))->current()->count;
}
......
* @param integer $iface_id
* @return Mysql_Result
*/
public function get_all_ip_addresses_of_iface ($iface_id)
public function get_all_ip_addresses_of_iface($iface_id)
{
return $this->db->query("
SELECT ip.id, ip.ip_address, s.name AS subnet_name, s.id AS subnet_id
FROM ip_addresses ip
LEFT JOIN subnets s ON s.id = ip.subnet_id
WHERE iface_id = ?
WHERE ip.iface_id = ?
ORDER BY id ASC
", array($iface_id));
}
......
* Removes all IP addresses from temporary whitelist. Used when bank statement
* is imported, then all whitelisted whould have payed their fees, so they are
* no longer protected from redirection.
*
* @author Jiri Svitak
*/
public function clean_temporary_whitelist()
......
* @param integer $subnet_id
* @param integer $member_id
*/
public function delete_ip_addresses_by_subnet_member ($subnet_id, $member_id)
public function delete_ip_addresses_by_subnet_member($subnet_id, $member_id)
{
$this->db->query("
DELETE FROM ip_addresses
......
* @param integer $subnet_id
* @return integer
*/
public function count_all_ip_addresses_without_member_by_subnet ($subnet_id)
public function count_all_ip_addresses_without_member_by_subnet($subnet_id)
{
return $this->db->query("
SELECT COUNT(*) AS total
......
* @author Michal Kliment
* @param string $ip_address
*/
public function delete_ip_address_with_member ($ip_address)
public function delete_ip_address_with_member($ip_address)
{
$this->db->query("
DELETE FROM ip_addresses
......
* @param integer $ip_address_id
* @return boolean
*/
public function set_whitelist ($whitelist, $ip_address_id = NULL)
public function set_whitelist($whitelist, $ip_address_id = NULL)
{
if (!$ip_address_id && isset($this))
$ip_address_id = $this->id;
......
* @author Michal Kliment
* @return MySQL Result
*/
public function get_ip_addresses_qos_ceil_rate ()
public function get_ip_addresses_qos_ceil_rate()
{
return $this->db->query("
SELECT m.id AS member_id, ip_address
FROM
(
SELECT ip_address, IFNULL(u.member_id,ip.member_id) AS member_id
FROM
(
SELECT ip_address, IFNULL(i1.device_id,i2.device_id) AS device_id, member_id
FROM ip_addresses ip
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
) ip
LEFT JOIN devices d ON ip.device_id = d.id
SELECT ip.ip_address, IFNULL(u.member_id, ip.member_id) AS member_id
FROM ip_addresses ip
LEFT JOIN ifaces i ON ip.iface_id = i.id
LEFT JOIN devices d ON i.device_id = d.id
LEFT JOIN users u ON d.user_id = u.id
) ip
JOIN members m ON ip.member_id = m.id AND (m.qos_ceil OR m.qos_rate)
freenetis/branches/network/application/controllers/ip_addresses.php
$limit_results = 500, $order_by = 'ip_address',
$order_by_direction = 'asc', $page_word = null, $page = 1)
{
if (!$this->acl_check_view('Devices_Controller','ip_address'))
if (!$this->acl_check_view('Devices_Controller', 'ip_address'))
Controller::error(ACCESS);
$ip_model = new Ip_address_Model();
......
$sql_offset = 0;
$query = $ip_model->get_all_ip_addresses(
$sql_offset, (int)$limit_results, $order_by,
$sql_offset, $limit_results, $order_by,
$order_by_direction, $filter_form->as_sql()
);
......
}
$grid->order_callback_field('ip_address')
->label(__('IP address'))
->label('IP address')
->callback('callback::ip_address_field');
$grid->order_link_field('subnet_id')
......
->label('Subnet');
$grid->order_callback_field('device_name')
->label(__('Device name'))
->label('Device name')
->callback('callback::device_field');
$actions = $grid->grouped_action_field();
......
if (!$ip_address->id)
Controller::error(RECORD);
if ($ip_address->iface_id)
$member = $ip_address->iface->device->user->member;
$member_id = $member->id;
if (!$this->acl_check_view('Devices_Controller', 'ip_address', $member_id))
Controller::error(ACCESS);
$device = $ip_address->iface->device;
$iface = $ip_address->iface;
$iface_name = $iface->name;
if (empty($iface_name))
{
$member_id = $ip_address->iface->device->user->member_id;
$device_id = $ip_address->iface->device_id;
$iface_id = $ip_address->iface_id;
$vlan_iface = null;
$iface_name = $iface->mac;
}
else
else if (!empty($iface->mac))
{
$member_id = $ip_address->vlan_iface->iface->device->user->member_id;
$device_id = $ip_address->vlan_iface->iface->device_id;
$iface_id = $ip_address->vlan_iface->iface_id;
$vlan_iface = new Vlan_iface_Model($ip_address->vlan_iface_id);
$iface_name .= " (".$iface->mac.")";
}
if (!$this->acl_check_view('Devices_Controller','ip_address',$member_id))
Controller::error(ACCESS);
$member = new Member_Model($member_id);
$device = new Device_Model($device_id);
$iface = new Iface_Model($iface_id);
// whitelist types
$whitelist_types = array
(
Ip_address_Model::NO_WHITELIST => __('No whitelist'),
Ip_address_Model::PERNAMENT_WHITELIST => __('Permanent whitelist'),
Ip_address_Model::TEMPORARY_WHITELIST => __('Temporary whitelist')
);
if ($ip_address->member_id)
{
// breadcrumbs menu
......
->link('members/show/'.$ip_address->member_id,
"ID ".$ip_address->member->id." - ".$ip_address->member->name,
$this->acl_check_view(
'Members_Controller', 'members', $ip_address->member_id))
->text($ip_address->ip_address . ' (' . $ip_address->id . ')');
'Members_Controller', 'members', $ip_address->member_id));
}
else
{
if (url_lang::current(TRUE) == 'devices')
{
if ($ip_address->iface_id)
$iface = $ip_address->iface;
else
$iface = $ip_address->vlan_iface->iface;
$iface = $ip_address->iface;
$device_name = $iface->device->name;
if ($device_name == '')
{
$device_name = ORM::factory ('enum_type')
$device_name = ORM::factory('enum_type')
->get_value($device->type);
}
if ($iface->name != '')
$iface_name = $iface->name." (".$iface->mac.")";
else
$iface_name = $iface->mac;
// breadcrumbs menu
$breadcrumbs = breadcrumbs::add()
->link('members/show_all', 'Members',
$this->acl_check_view('Members_Controller','members'))
->disable_translation()
->link('members/show/' .
$iface->device->user->member->id,
'ID ' . $iface->device->user->member->id .
' - ' . $iface->device->user->member->name,
$this->acl_check_view(
'Members_Controller',
'members',
$iface->device->user->member->id)
)
->enable_translation()
->link('users/show_by_member/' .
$iface->device->user->member->id, 'Users',
$this->acl_check_view(
'Users_Controller', 'users',
$iface->device->user->member->id)
)
->disable_translation()
->link('users/show/' . $iface->device->user->id,
$iface->device->user->name .
' ' . $iface->device->user->surname .
' (' . $iface->device->user->login . ')',
$this->acl_check_view(
'Users_Controller', 'users',
$iface->device->user->member_id)
)
->enable_translation()
->link(
'devices/show_by_user/'.$iface->device->user_id,
'Devices',
$this->acl_check_view(
'Devices_Controller',
'devices',
$iface->device->user->member_id
)
// breadcrumbs menu
$breadcrumbs = breadcrumbs::add()
->link('members/show_all', 'Members',
$this->acl_check_view('Members_Controller','members'))
->disable_translation()
->link('members/show/' .
$iface->device->user->member->id,
'ID ' . $iface->device->user->member->id .
' - ' . $iface->device->user->member->name,
$this->acl_check_view(
'Members_Controller',
'members',
$iface->device->user->member->id)
)
->link(
'devices/show/'.$iface->device_id,
$device_name,
$this->acl_check_view(
'Devices_Controller',
'devices',
$iface->device->user->member_id
)
->enable_translation()
->link('users/show_by_member/' .
$iface->device->user->member->id, 'Users',
$this->acl_check_view(
'Users_Controller', 'users',
$iface->device->user->member->id)
)
->link(
'devices/show_iface/'.$iface_id,
$iface_name,
$this->acl_check_view(
'Devices_Controller',
'iface',
$iface->device->user->member_id
)
);
if ($ip_address->vlan_iface_id)
{
$breadcrumbs->link(
'devices/show_vlan_iface/'.$ip_address->vlan_iface->id,
$ip_address->vlan_iface->name,
$this->acl_check_view(
'Devices_Controller',
'vlan_iface',
$iface->device->user->member_id
)
);
}
$breadcrumbs->disable_translation()
->text($ip_address->ip_address);
->disable_translation()
->link('users/show/' . $iface->device->user->id,
$iface->device->user->name .
' ' . $iface->device->user->surname .
' (' . $iface->device->user->login . ')',
$this->acl_check_view(
'Users_Controller', 'users',
$iface->device->user->member_id)
)
->enable_translation()
->link(
'devices/show_by_user/'.$iface->device->user_id,
'Devices', $this->acl_check_view(
'Devices_Controller', 'devices',
$iface->device->user->member_id
)
)
->link(
'devices/show/'.$iface->device_id,
$device_name, $this->acl_check_view(
'Devices_Controller', 'devices',
$iface->device->user->member_id
)
)
->link(
'devices/show_iface/'.$iface->id,
$iface_name, $this->acl_check_view(
'Devices_Controller', 'iface',
$iface->device->user->member_id
)
);
}
else
{
// breadcrumbs menu
$breadcrumbs = breadcrumbs::add()
->link('ip_addresses/show_all', 'IP addresses',
$this->acl_check_view('Devices_Controller', 'ip_address'))
->disable_translation()
->text($ip_address->ip_address . ' (' . $ip_address->id . ')');
$this->acl_check_view('Devices_Controller', 'ip_address'));
}
}
$breadcrumbs->disable_translation()
->text($ip_address->ip_address . ' (' . $ip_address->id . ')');
$view = new View('main');
$view->title = __('IP address detail').
' - '.$ip_address->ip_address;
$view->title = __('IP address detail') . ' - ' . $ip_address->ip_address;
$view->breadcrumbs = $breadcrumbs->html();
$view->content = new View('ip_addresses/show');
$view->content->ip_address = $ip_address;
$view->content->member = $member;
$view->content->device = $device;
$view->content->device = $device;
$view->content->iface = $iface;
$view->content->vlan_iface = $vlan_iface;
$view->content->whitelist_types = $whitelist_types;
$view->content->iface_name = $iface_name;
$view->content->whitelist_types = Ip_address_Model::get_whitelist_types();
$view->content->grid = '';
$view->content->headline = __('IP address detail') . ' - '.$ip_address->ip_address;
$view->render(TRUE);
......
if (!$this->acl_check_new('Devices_Controller', 'ip_address'))
Controller::error(ACCESS);
$vlan_iface_model = new Vlan_iface_Model();
if ($device_id)
{
if (!is_numeric($device_id))
......
if (!$device->id)
Controller::error(RECORD);
$arr_ifaces = array();
$arr_vlan_ifaces = array();
$arr_ifaces = array
(
NULL => '----- '.__('select interface').' -----'
) + $device->ifaces->select_list();
$arr_ifaces[0] = '----- '.__('select interface').' -----';
$arr_vlan_ifaces[0] = '----- '.__('select vlan interface').' -----';
$ifaces = $device->ifaces;
foreach ($ifaces as $iface)
{
$arr_ifaces[$iface->id] = $iface->name;
}
$vlan_ifaces = $vlan_iface_model->get_all_vlan_ifaces_by_device_id(
$device->id
);
foreach ($vlan_ifaces as $vlan_iface)
{
$arr_vlan_ifaces[$vlan_iface->id] = $vlan_iface->name;
}
$title = __('Add new IP address to device').' '.$device->name;
$link_back_url = 'devices/show/'.$device->id;
$link_back = html::anchor($link_back_url, __('Back to the device'));
}
else
{
......
(
NULL => '----- '.__('select interface').' -----'
) + ORM::factory('iface')->select_list('id', $concat, 'id');
$arr_vlan_ifaces = array
(
NULL => '----- '.__('select vlan interface').' -----'
) + ORM::factory('vlan_iface')->select_list('id', 'name');
$title = __('Add new IP address');
$link_back_url = 'ip_addresses/show_all';
$link_back = html::anchor($link_back_url,__('Back to IP addresses list'));
}
$this->form = new Forge(url::base(TRUE).url::current(TRUE));
$this->form = new Forge();
$arr_subnets = array
(
......
);
$this->form->dropdown('iface_id')
->label(__('Interface name').':')
->label('Interface name')
->rules('required')
->options($arr_ifaces);
$this->form->dropdown('vlan_iface_id')
->label(__('VLAN interface name').':')
->options($arr_vlan_ifaces)
->callback(array($this, 'check_ifaces'));
$this->form->input('ip_address')
->label(__('IP address').':')
->label('IP address')
->rules('required|valid_ip_address')
->callback(array($this, 'valid_ip'));
$this->form->dropdown('subnet_id')
->label(__('Select subnet name').':')
->label('Select subnet name')
->options($arr_subnets)
->rules('required')->add_button('subnets');
->rules('required')
->add_button('subnets');
$this->form->dropdown('gateway')
->label(__('Gateway').':&nbsp;'.help::hint('gateway'))
......
{
$form_data = $this->form->as_array();
$ip = new ip_address_Model();
$ip = new Ip_address_Model();
$ip->delete_ip_address_with_member($form_data['ip_address']);
if ($form_data['iface_id'])
{
$iface = new Iface_Model($form_data['iface_id']);
$member_id = $iface->device->user->member->id;
$ip->iface_id = $form_data['iface_id'];
}
else
{
$vlan_iface = new Vlan_iface_Model($form_data['vlan_iface_id']);
$member_id = $vlan_iface->iface->device->user->member->id;
$ip->vlan_iface_id = $form_data['vlan_iface_id'];
}
$ip->iface_id = $form_data['iface_id'];
$ip->ip_address = $form_data['ip_address'];
$ip->subnet_id = $form_data['subnet_id'];
$ip->gateway = $form_data['gateway'];
$ip->service = $form_data['service'];
$ip->whitelisted = Ip_address_Model::NO_WHITELIST;
$ip->member_id = NULL;
unset($form_data);
if ($ip->save())
{
status::success('IP address is successfully saved.');
Allowed_subnets_Controller::update_enabled(
$member_id, array($ip->subnet_id)
$ip->iface->device->user->member->id, array($ip->subnet_id)
);
}
......
$view->content->headline = $title;
$view->render(TRUE);
} // end of add function
/**
* Adds new IP address to some iface
*
* @author Michal Kliment
* @param integer $iface_id id of iface to add new IP address
*/
public function add_to_iface($iface_id = NULL)
{
if (!$iface_id)
Controller::warning(PARAMETER);
$iface = new Iface_Model($iface_id);
if (!$iface->id)
Controller::error(RECORD);
$this->form = new Forge('ip_addresses/add_to_iface/'.$iface_id);
$arr_ifaces = array();
$member_id = $iface->device->user->member_id;
if (!$this->acl_check_new('Devices_Controller', 'ip_address', $member_id))
Controller::error(ACCESS);
$arr_ifaces[$iface->id] = $iface->name;
$subnet_model = new Subnet_Model();
$arr_subnets = array(
NULL => '----- '.__('select subnet').' -----'
) + $subnet_model->select_list_by_net();
$arr_bool = array
(
'0'=> __('No'),
'1'=> __('Yes')
);
$this->form->dropdown('iface_id')
->label(__('Interface name').':')
->options($arr_ifaces);
$this->form->input('ip_address')
->label(__('IP address').':')
->rules('required|valid_ip_address')
->callback(array($this, 'valid_ip'));
$this->form->dropdown('subnet_id')
->label(__('Select subnet name').':')
->options($arr_subnets)
->rules('required')
->add_button('subnets');
$this->form->dropdown('gateway')
->label(__('Gateway'))
->options($arr_bool)
->selected('0');
$this->form->dropdown('service')
->label(__('Service'))
->options($arr_bool)
->selected('0');
$this->form->submit('Save');
// validate form and save data
if ($this->form->validate())
{
$form_data = $this->form->as_array();
$ip = new ip_address_Model();
$ip->delete_ip_address_with_member($form_data['ip_address']);
$ip->iface_id = $form_data['iface_id'];
$ip->ip_address = $form_data['ip_address'];
$ip->subnet_id = $form_data['subnet_id'];
$ip->gateway = $form_data['gateway'];
$ip->service = $form_data['service'];
$ip->whitelisted = Ip_address_Model::NO_WHITELIST;
unset($form_data);
if ($ip->save())
{
status::success('IP address is successfully saved.');
Allowed_subnets_Controller::update_enabled(
$iface->device->user->member_id, array($ip->subnet_id)
);
}
if (Path::instance()->previous()->uri()->current(0,1) == 'devices')
url::redirect('devices/show_ip_address/' . $ip->id);
else
url::redirect('ip_addresses/show/' . $ip->id);
}
if (Path::instance()->previous()->uri()->current(0,1) == 'devices')
{
if ($iface->name != '')
$iface_name = $iface->name." (".$iface->mac.")";
else
$iface_name = $iface->mac;
$breadcrumbs = breadcrumbs::add()
->link(
'members/show_all',
'Members',
$this->acl_check_view('Members_Controller','members')
)
->link(
'members/show/'.$iface->device->user->member_id,
'ID '.$iface->device->user->member->id.' - '.$iface->device->user->member->name,
$this->acl_check_view(
'Members_Controller',
'members',
$iface->device->user->member_id
)
)
->link(
'users/show_by_member/'.$iface->device->user->member_id,
"Users",
$this->acl_check_view(
'Users_Controller',
'users',$iface->device->user->member_id
)
)
->link(
'users/show/'.$iface->device->user_id,
$iface->device->user->name.' '.$iface->device->user->surname.' ('.$iface->device->user->login.')',
$this->acl_check_view(
'Users_Controller',
'users',
$iface->device->user->member_id
)
)
->link(
'devices/show_by_user/'.$iface->device->user_id,
'Devices',
$this->acl_check_view(
'Devices_Controller',
'devices',
$iface->device->user->member_id
)
)
->link(
'devices/show/'.$iface->device_id,
$iface->device->name,
$this->acl_check_view(
'Devices_Controller',
'devices',
$iface->device->user->member_id
)
)
->link(
'devices/show_iface/'.$iface->id,
$iface_name,
$this->acl_check_view(
'Devices_Controller',
'iface',
$iface->device->user->member_id
)
);
}
else
{
// breadcrumbs menu
if ($iface->name != '')
$name = "$iface->name ($iface->mac)";
else
$name = $iface->mac;
$breadcrumbs = breadcrumbs::add()
->link('ifaces/show_all', 'Interfaces',
$this->acl_check_view('Devices_Controller','iface'))
->link('ifaces/show/' . $iface_id, $name,
$this->acl_check_view(
'Devices_Controller', 'iface',
$iface->device->user->member_id
));
}
$breadcrumbs->text('Add new IP address');
// view
$view = new View('main');
$view->title = __('Add new IP address');
$view->breadcrumbs = $breadcrumbs->html();
$view->content = new View('form');
$view->content->form = $this->form->html();
$view->content->headline = __('Add new IP address');
$view->render(TRUE);
} // end of add function
/**
* Adds ...
*
* @param integer $vlan_iface_id
*/
public function add_to_vlan_iface($vlan_iface_id = NULL)
{
if (!$vlan_iface_id)
Controller::warning(PARAMETER);
$vlan_iface = new Vlan_iface_Model($vlan_iface_id);
if (!$vlan_iface->id)
Controller::error(RECORD);
$this->form = new Forge('ip_addresses/add_to_vlan_iface/'.$vlan_iface_id);
$arr_vlan_ifaces = array();
$member_id = $vlan_iface->iface->device->user->member_id;
if (!$this->acl_check_new('Devices_Controller', 'ip_address', $member_id))
Controller::error(ACCESS);
$arr_vlan_ifaces[$vlan_iface->id] = $vlan_iface->name;
$subnet_model = new Subnet_Model();
$arr_subnets = array(
NULL => '----- '.__('select subnet').' -----'
) + $subnet_model->select_list_by_net();
$arr_bool = array
(
'0'=> __('No'),
'1'=> __('Yes')
);
$this->form->dropdown('vlan_iface_id')
->label(__('VLAN interface name').':')
->options($arr_vlan_ifaces);
$this->form->input('ip_address')
->label(__('IP address').':')
->rules('required|valid_ip_address')
->callback(array($this, 'valid_ip'));
$this->form->dropdown('subnet_id')
->label(__('Select subnet name').':')
->options($arr_subnets)
->rules('required')
->add_button('subnets');
$this->form->dropdown('gateway')
->options($arr_bool)
->selected('0');
$this->form->dropdown('service')
->options($arr_bool)
->selected('0');
$this->form->submit('Save');
// validate form and save data
if ($this->form->validate())
{
$form_data = $this->form->as_array();
$ip = new ip_address_Model();
$ip->delete_ip_address_with_member($form_data['ip_address']);
$ip->vlan_iface_id = $form_data['vlan_iface_id'];
$ip->ip_address = $form_data['ip_address'];
$ip->subnet_id = $form_data['subnet_id'];
$ip->gateway = $form_data['gateway'];
$ip->service = $form_data['service'];
$ip->whitelisted = Ip_address_Model::NO_WHITELIST;
unset($form_data);
if ($ip->save())
{
status::success('IP address is successfully saved.');
Allowed_subnets_Controller::update_enabled(
$vlan_iface->iface->device->user->member_id,
array($ip->subnet_id)
);
}
if (Path::instance()->previous()->uri()->current(0,1) == 'devices')
url::redirect('devices/show_ip_address/' . $ip->id);
else
url::redirect('ip_addresses/show/' . $ip->id);
}
if (Path::instance()->previous()->uri()->current(0,1) == 'devices')
{
if ($vlan_iface->iface->name != '')
$iface_name = $vlan_iface->iface->name." (".$vlan_iface->iface->mac.")";
else
$iface_name = $vlan_iface->iface->mac;
$breadcrumbs = breadcrumbs::add()
->link(
'members/show_all',
'Members',
$this->acl_check_view('Members_Controller','members')
)
->link(
'members/show/'.$vlan_iface->iface->device->user->member_id,
'ID '.$vlan_iface->iface->device->user->member->id.' - '.$vlan_iface->iface->device->user->member->name,
$this->acl_check_view(
'Members_Controller',
'members',
$vlan_iface->iface->device->user->member_id
)
)
->link(
'users/show_by_member/'.$vlan_iface->iface->device->user->member_id,
"Users",
$this->acl_check_view(
'Users_Controller',
'users',$vlan_iface->iface->device->user->member_id
)
)
->link(
'users/show/'.$vlan_iface->iface->device->user_id,
$vlan_iface->iface->device->user->name.' '.$vlan_iface->iface->device->user->surname.' ('.$vlan_iface->iface->device->user->login.')',
$this->acl_check_view(
'Users_Controller',
'users',
... Rozdílový soubor je zkrácen, protože jeho délka přesahuje max. limit.

Také k dispozici: Unified diff