Revize 1410
Přidáno uživatelem Ondřej Fibich před více než 12 roky(ů)
| freenetis/branches/network/application/models/vlan.php | ||
|---|---|---|
|
*/
|
||
|
public function get_default_vlan()
|
||
|
{
|
||
|
return $this->where('tag_802_1q', self::DEFAULT_VLAN)->find();
|
||
|
return $this->where('tag_802_1q', self::DEFAULT_VLAN)->find();
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
*/
|
||
|
public function count_all_vlans()
|
||
|
{
|
||
|
return $this->db->count_records('vlans');
|
||
|
return $this->db->count_records('vlans');
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
$limit_from = 0, $limit_results = 50,
|
||
|
$order_by = 'tag_802_1q', $order_by_direction = 'ASC')
|
||
|
{
|
||
|
// order by direction check
|
||
|
$order_by_direction = strtolower($order_by_direction);
|
||
|
|
||
|
if ($order_by_direction != 'desc')
|
||
|
{
|
||
|
$order_by_direction = 'asc';
|
||
|
}
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT v.*, COUNT(DISTINCT d.id) AS devices_count,
|
||
|
GROUP_CONCAT(DISTINCT d.name SEPARATOR ', \n') AS devices
|
||
|
FROM vlans v
|
||
|
LEFT JOIN ifaces_vlans iv ON iv.vlan_id = v.id
|
||
|
LEFT JOIN ifaces i ON iv.iface_id = i.id
|
||
|
LEFT JOIN devices d ON i.device_id = d.id
|
||
|
GROUP BY v.id
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT " . intval($limit_from) . ", " . intval($limit_results) ."
|
||
|
");
|
||
|
// order by direction check
|
||
|
$order_by_direction = strtolower($order_by_direction);
|
||
|
|
||
|
if ($order_by_direction != 'desc')
|
||
|
{
|
||
|
$order_by_direction = 'asc';
|
||
|
}
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT v.*, COUNT(DISTINCT d.id) AS devices_count,
|
||
|
GROUP_CONCAT(DISTINCT d.name SEPARATOR ', \n') AS devices
|
||
|
FROM vlans v
|
||
|
LEFT JOIN ifaces_vlans iv ON iv.vlan_id = v.id
|
||
|
LEFT JOIN ifaces i ON iv.iface_id = i.id
|
||
|
LEFT JOIN devices d ON i.device_id = d.id
|
||
|
GROUP BY v.id
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT " . intval($limit_from) . ", " . intval($limit_results) ."
|
||
|
");
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
*/
|
||
|
public function select_list($key = 'id', $val = NULL, $order_val = 'tag_802_1q')
|
||
|
{
|
||
|
if (empty($val))
|
||
|
{
|
||
|
$val = 'CONCAT(COALESCE(tag_802_1q, ""), " - ", COALESCE(name, ""))';
|
||
|
}
|
||
|
|
||
|
return parent::select_list($key, $val, $order_val);
|
||
|
if (empty($val))
|
||
|
{
|
||
|
$val = 'CONCAT(COALESCE(tag_802_1q, ""), " - ", COALESCE(name, ""))';
|
||
|
}
|
||
|
|
||
|
return parent::select_list($key, $val, $order_val);
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
*/
|
||
|
public function get_devices_of_vlan($vlan_id = NULL)
|
||
|
{
|
||
|
if (!$vlan_id && isset($this))
|
||
|
{
|
||
|
$vlan_id = $this->id;
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT d.id, d.name, IFNULL(p.ports_count, 0) AS ports_count,
|
||
|
p.ports, ip.ip_address AS ip_address, ip.id AS ip_address_id
|
||
|
FROM devices d
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN ifaces_vlans iv ON iv.iface_id = i.id
|
||
|
LEFT JOIN ip_addresses ip ON ip.iface_id = iv.iface_id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT COUNT(*) AS ports_count, i.device_id, GROUP_CONCAT(
|
||
|
IF(i.number IS NOT NULL, CONCAT(?, i.number), i.name)
|
||
|
ORDER BY i.number SEPARATOR ', \n'
|
||
|
) AS ports
|
||
|
FROM ifaces i
|
||
|
JOIN ifaces_vlans iv2 ON iv2.iface_id = i.id
|
||
|
WHERE i.type = ? AND iv2.vlan_id = ?
|
||
|
GROUP BY i.device_id
|
||
|
) p ON p.device_id = d.id
|
||
|
WHERE iv.vlan_id = ?
|
||
|
GROUP BY d.id
|
||
|
ORDER BY INET_ATON(ip.ip_address)
|
||
|
", array( __('Port') . ' ', Iface_Model::TYPE_PORT, $vlan_id, $vlan_id));
|
||
|
if (!$vlan_id && isset($this))
|
||
|
{
|
||
|
$vlan_id = $this->id;
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT d.id, d.name, IFNULL(p.ports_count, 0) AS ports_count,
|
||
|
p.ports, ip.ip_address AS ip_address, ip.id AS ip_address_id
|
||
|
FROM devices d
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN ifaces_vlans iv ON iv.iface_id = i.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT COUNT(*) AS ports_count, i.device_id, GROUP_CONCAT(
|
||
|
IF(i.number IS NOT NULL, CONCAT(?, i.number), i.name)
|
||
|
ORDER BY i.number SEPARATOR ', \n'
|
||
|
) AS ports
|
||
|
FROM ifaces i
|
||
|
JOIN ifaces_vlans iv2 ON iv2.iface_id = i.id
|
||
|
WHERE i.type = ? AND iv2.vlan_id = ?
|
||
|
GROUP BY i.device_id
|
||
|
) p ON p.device_id = d.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT ip.id, ip.ip_address, i.device_id
|
||
|
FROM ip_addresses ip
|
||
|
LEFT JOIN ifaces i ON ip.iface_id = i.id
|
||
|
) ip ON ip.device_id = d.id
|
||
|
WHERE iv.vlan_id = ?
|
||
|
GROUP BY d.id
|
||
|
ORDER BY INET_ATON(ip.ip_address)
|
||
|
", array( __('Port') . ' ', Iface_Model::TYPE_PORT, $vlan_id, $vlan_id));
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
*/
|
||
|
public function get_all_vlans_by_port($port_id)
|
||
|
{
|
||
|
return $this->db->query("
|
||
|
SELECT v.*, iv.tagged
|
||
|
FROM vlans v
|
||
|
JOIN ifaces_vlans iv ON iv.vlan_id = v.id
|
||
|
JOIN ifaces i ON iv.iface_id = i.id
|
||
|
WHERE i.id = ? AND i.type = ?
|
||
|
", $port_id, Iface_Model::TYPE_PORT);
|
||
|
return $this->db->query("
|
||
|
SELECT v.*, iv.tagged
|
||
|
FROM vlans v
|
||
|
JOIN ifaces_vlans iv ON iv.vlan_id = v.id
|
||
|
JOIN ifaces i ON iv.iface_id = i.id
|
||
|
WHERE i.id = ? AND i.type = ?
|
||
|
", $port_id, Iface_Model::TYPE_PORT);
|
||
|
}
|
||
|
}
|
||
Také k dispozici: Unified diff
Opravy:
- opravy dotazu pro VLANy (#185)