|
<?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|
* This file is part of open source system FreeNetIS
|
|
* and it is release under GPLv3 licence.
|
|
*
|
|
* More info about licence can be found:
|
|
* http://www.gnu.org/licenses/gpl-3.0.html
|
|
*
|
|
* More info about project can be found:
|
|
* http://www.freenetis.org/
|
|
*
|
|
*/
|
|
|
|
/**
|
|
* @package Model
|
|
*/
|
|
class Vlan_Model extends ORM
|
|
{
|
|
protected $has_many = array('vlan_ifaces');
|
|
protected $has_and_belongs_to_many = array('ports');
|
|
|
|
/**
|
|
* Get count of vlans
|
|
*
|
|
* @return integer
|
|
*/
|
|
public function count_all_vlans()
|
|
{
|
|
return $this->db->count_records('vlans');
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @param integer $limit_from
|
|
* @param integer $limit_results
|
|
* @param string $order_by
|
|
* @param integer $order_by_direction
|
|
* @return unknown_type
|
|
*/
|
|
public function get_all_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(d.id) AS devices_count,
|
|
GROUP_CONCAT(d.name SEPARATOR ', \n') AS devices
|
|
FROM vlans v
|
|
LEFT JOIN
|
|
(
|
|
SELECT i.device_id, vi.vlan_id
|
|
FROM vlan_ifaces vi
|
|
JOIN ifaces i ON vi.iface_id = i.id
|
|
UNION
|
|
SELECT p.device_id, pv.vlan_id
|
|
FROM ports_vlans pv
|
|
JOIN ports p ON pv.port_id = p.id
|
|
) i ON i.vlan_id = v.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) ."
|
|
");
|
|
}
|
|
|
|
/**
|
|
* Override default ORM function select_list due to
|
|
*need of ordering by another column than are key and value
|
|
*
|
|
* @author Michal Kliment, Ondrej Fibich
|
|
* @return array
|
|
*/
|
|
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);
|
|
}
|
|
|
|
/**
|
|
* Returns all devices which belong to VLAN
|
|
* @param type $vlan_id
|
|
* @return type
|
|
*/
|
|
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,
|
|
IFNULL(d.ip_address, IFNULL(ip1.ip_address,ip2.ip_address)) AS ip_address,
|
|
IFNULL(d.ip_address_id, IFNULL(ip1.id,ip2.id)) AS ip_address_id
|
|
FROM
|
|
(
|
|
SELECT d.*,
|
|
IFNULL(ip1.ip_address,ip2.ip_address) AS ip_address,
|
|
IFNULL(ip1.id,ip2.id) AS ip_address_id
|
|
FROM devices d
|
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
|
LEFT JOIN vlan_ifaces vi ON vi.iface_id = i.id
|
|
LEFT JOIN ip_addresses ip1 ON ip1.vlan_iface_id = vi.id
|
|
LEFT JOIN ip_addresses ip2 ON ip2.iface_id = i.id
|
|
LEFT JOIN ports p ON p.device_id = d.id
|
|
LEFT JOIN ports_vlans pv ON pv.port_id = p.id
|
|
WHERE vi.vlan_id = ? OR pv.vlan_id = ?
|
|
GROUP BY d.id
|
|
) d
|
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
|
LEFT JOIN vlan_ifaces vi ON vi.iface_id = i.id
|
|
LEFT JOIN ip_addresses ip1 ON ip1.iface_id = i.id
|
|
LEFT JOIN ip_addresses ip2 ON ip2.vlan_iface_id = vi.id
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
p.device_id, COUNT(*) AS ports_count,
|
|
GROUP_CONCAT(IF(p.port_nr IS NOT NULL, CONCAT(?, p.port_nr), p.name) ORDER BY p.port_nr SEPARATOR ', \n') AS ports
|
|
FROM ports p
|
|
JOIN ports_vlans pv ON pv.port_id = p.id
|
|
WHERE pv.vlan_id = ?
|
|
GROUP BY p.device_id
|
|
) p ON p.device_id = d.id
|
|
GROUP BY d.id
|
|
ORDER BY INET_ATON(IFNULL(d.ip_address, IFNULL(ip1.ip_address,ip2.ip_address)))
|
|
", array($vlan_id, $vlan_id, __('Port').' ', $vlan_id));
|
|
}
|
|
}
|