public function GetNodesWithGroup($groupid, $network = NULL)
{
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
return $this->db->GetAll('SELECT n.id AS id, n.name AS nodename, a.nodeid
FROM nodes n
JOIN customerview c ON (n.ownerid = c.id)
JOIN nodegroupassignments a ON (n.id = a.nodeid)
WHERE a.nodegroupid = ?' . ($network ? ' AND ((ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ')
OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')) ' : '') . ' ORDER BY nodename', array($groupid));
}
public function GetNodeList($order = 'name,asc', $search = NULL, $sqlskey = 'AND', $network = NULL, $status = NULL, $customergroup = NULL, $nodegroup = NULL)
{
if ($order == '') {
$order = 'name,asc';
}
list($order, $direction) = sscanf($order, '%[^,],%s');
$direction == 'desc' ? $direction = 'desc' : ($direction = 'asc');
switch ($order) {
case 'name':
$sqlord = ' ORDER BY n.name';
break;
case 'id':
$sqlord = ' ORDER BY n.id';
break;
case 'mac':
$sqlord = ' ORDER BY n.mac';
break;
case 'ip':
$sqlord = ' ORDER BY n.ipaddr';
break;
case 'ip_pub':
$sqlord = ' ORDER BY n.ipaddr_pub';
break;
case 'ownerid':
$sqlord = ' ORDER BY n.ownerid';
break;
case 'owner':
$sqlord = ' ORDER BY owner';
break;
case 'location':
$sqlord = ' ORDER BY location';
break;
}
if (sizeof($search)) {
foreach ($search as $idx => $value) {
if ($value != '') {
switch ($idx) {
case 'ipaddr':
$searchargs[] = '(inet_ntoa(n.ipaddr) ?LIKE? ' . $this->db->Escape('%' . trim($value) . '%') . ' OR inet_ntoa(n.ipaddr_pub) ?LIKE? ' . $this->db->Escape('%' . trim($value) . '%') . ')';
break;
case 'state':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc
JOIN location_boroughs lb ON lb.id = lc.boroughid
JOIN location_districts ld ON ld.id = lb.districtid
JOIN location_states ls ON ls.id = ld.stateid WHERE ls.id = ' . $this->db->Escape($value) . ')';
}
break;
case 'district':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc
JOIN location_boroughs lb ON lb.id = lc.boroughid
JOIN location_districts ld ON ld.id = lb.districtid WHERE ld.id = ' . $this->db->Escape($value) . ')';
}
break;
case 'borough':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc WHERE lc.boroughid = ' . $this->db->Escape($value) . ')';
}
break;
default:
$searchargs[] = 'n.' . $idx . ' ?LIKE? ' . $this->db->Escape("%{$value}%");
}
}
}
}
if (isset($searchargs)) {
$searchargs = ' AND (' . implode(' ' . $sqlskey . ' ', $searchargs) . ')';
}
$totalon = 0;
$totaloff = 0;
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
if ($nodelist = $this->db->GetAll('SELECT n.id AS id, n.ipaddr, inet_ntoa(n.ipaddr) AS ip, ipaddr_pub,
inet_ntoa(n.ipaddr_pub) AS ip_pub, n.mac, n.name, n.ownerid, n.access, n.warning,
n.netdev, n.lastonline, n.info, ' . $this->db->Concat('c.lastname', "' '", 'c.name') . ' AS owner, net.name AS netname, n.location,
lb.name AS borough_name, lb.type AS borough_type,
ld.name AS district_name, ls.name AS state_name
FROM vnodes n
JOIN customersview c ON (n.ownerid = c.id)
JOIN networks net ON net.id = n.netid
LEFT JOIN location_cities lc ON lc.id = n.location_city
LEFT JOIN location_boroughs lb ON lb.id = lc.boroughid
LEFT JOIN location_districts ld ON ld.id = lb.districtid
LEFT JOIN location_states ls ON ls.id = ld.stateid ' . ($customergroup ? 'JOIN customerassignments ON (customerid = c.id) ' : '') . ($nodegroup ? 'JOIN nodegroupassignments ON (nodeid = n.id) ' : '') . ' WHERE 1=1 ' . ($network ? ' AND (n.netid = ' . $network . '
OR (n.ipaddr_pub > ' . $net['address'] . ' AND n.ipaddr_pub < ' . $net['broadcast'] . '))' : '') . ($status == 1 ? ' AND n.access = 1' : '') . ($status == 2 ? ' AND n.access = 0' : '') . ($status == 3 ? ' AND n.lastonline > ?NOW? - ' . intval(ConfigHelper::getConfig('phpui.lastonline_limit')) : '') . ($customergroup ? ' AND customergroupid = ' . intval($customergroup) : '') . ($nodegroup ? ' AND nodegroupid = ' . intval($nodegroup) : '') . (isset($searchargs) ? $searchargs : '') . ($sqlord != '' ? $sqlord . ' ' . $direction : ''))) {
foreach ($nodelist as $idx => $row) {
$row['access'] ? $totalon++ : $totaloff++;
}
}
$nodelist['total'] = sizeof($nodelist);
$nodelist['order'] = $order;
$nodelist['direction'] = $direction;
$nodelist['totalon'] = $totalon;
$nodelist['totaloff'] = $totaloff;
return $nodelist;
}
/**
* Returns customer nodes
*
* @param int $id Customer id
* @param int $count Limit
* @return array Nodes
*/
public function getCustomerNodes($id, $count = null)
{
if ($result = $this->db->GetAll('SELECT n.id, n.name, mac, ipaddr,
inet_ntoa(ipaddr) AS ip, ipaddr_pub,
inet_ntoa(ipaddr_pub) AS ip_pub, passwd, access,
warning, info, ownerid, lastonline, location,
(SELECT COUNT(*) FROM nodegroupassignments
WHERE nodeid = n.id) AS gcount,
n.netid, net.name AS netname
FROM vnodes n
JOIN networks net ON net.id = n.netid
WHERE ownerid = ?
ORDER BY n.name ASC ' . ($count ? 'LIMIT ' . $count : ''), array($id))) {
// assign network(s) to node record
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache);
$networks = (array) $network_manager->GetNetworks();
foreach ($result as $idx => $node) {
$ids[$node['id']] = $idx;
$result[$idx]['lastonlinedate'] = lastonline_date($node['lastonline']);
//foreach ($networks as $net)
// if (isipin($node['ip'], $net['address'], $net['mask'])) {
// $result[$idx]['network'] = $net;
// break;
// }
if ($node['ipaddr_pub']) {
foreach ($networks as $net) {
if (isipin($node['ip_pub'], $net['address'], $net['mask'])) {
$result[$idx]['network_pub'] = $net;
break;
}
}
}
}
// get EtherWerX channels
if (ConfigHelper::checkValue(ConfigHelper::getConfig('phpui.ewx_support', false))) {
$channels = $this->db->GetAllByKey('SELECT nodeid, channelid, c.name, c.id, cid,
nc.upceil, nc.downceil
FROM ewx_stm_nodes
JOIN ewx_stm_channels nc ON (channelid = nc.id)
LEFT JOIN ewx_channels c ON (c.id = nc.cid)
WHERE nodeid IN (' . implode(',', array_keys($ids)) . ')', 'nodeid');
if ($channels) {
foreach ($channels as $channel) {
$idx = $ids[$channel['nodeid']];
$result[$idx]['channelid'] = $channel['id'] ? $channel['id'] : $channel['channelid'];
$result[$idx]['channelname'] = $channel['name'];
$result[$idx]['cid'] = $channel['cid'];
$result[$idx]['downceil'] = $channel['downceil'];
$result[$idx]['upceil'] = $channel['upceil'];
}
}
}
}
return $result;
}
public function GetNodeList($order = 'name,asc', $search = NULL, $sqlskey = 'AND', $network = NULL, $status = NULL, $customergroup = NULL, $nodegroup = NULL, $limit = null, $offset = null, $count = false)
{
if ($order == '') {
$order = 'name,asc';
}
list($order, $direction) = sscanf($order, '%[^,],%s');
$direction == 'desc' ? $direction = 'desc' : ($direction = 'asc');
switch ($order) {
case 'name':
$sqlord = ' ORDER BY n.name';
break;
case 'id':
$sqlord = ' ORDER BY n.id';
break;
case 'mac':
$sqlord = ' ORDER BY n.mac';
break;
case 'ip':
$sqlord = ' ORDER BY n.ipaddr';
break;
case 'ip_pub':
$sqlord = ' ORDER BY n.ipaddr_pub';
break;
case 'ownerid':
$sqlord = ' ORDER BY n.ownerid';
break;
case 'owner':
$sqlord = ' ORDER BY owner';
break;
case 'location':
$sqlord = ' ORDER BY location';
break;
}
if (sizeof($search)) {
foreach ($search as $idx => $value) {
if ($value != '') {
switch ($idx) {
case 'ipaddr':
$searchargs[] = '(inet_ntoa(n.ipaddr) ?LIKE? ' . $this->db->Escape('%' . trim($value) . '%') . ' OR inet_ntoa(n.ipaddr_pub) ?LIKE? ' . $this->db->Escape('%' . trim($value) . '%') . ')';
break;
case 'state':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc
JOIN location_boroughs lb ON lb.id = lc.boroughid
JOIN location_districts ld ON ld.id = lb.districtid
JOIN location_states ls ON ls.id = ld.stateid WHERE ls.id = ' . $this->db->Escape($value) . ')';
}
break;
case 'district':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc
JOIN location_boroughs lb ON lb.id = lc.boroughid
JOIN location_districts ld ON ld.id = lb.districtid WHERE ld.id = ' . $this->db->Escape($value) . ')';
}
break;
case 'borough':
if ($value != '0') {
$searchargs[] = 'n.location_city IN (SELECT lc.id FROM location_cities lc WHERE lc.boroughid = ' . $this->db->Escape($value) . ')';
}
break;
case 'project':
$projectid = intval($value);
if ($projectid) {
switch ($projectid) {
case -2:
$searchargs[] = 'n.invprojectid IS NULL';
case -1:
break;
default:
$searchargs[] = 'n.invprojectid = ' . $projectid;
break;
}
}
break;
default:
$searchargs[] = 'n.' . $idx . ' ?LIKE? ' . $this->db->Escape("%{$value}%");
}
}
}
}
if (isset($searchargs)) {
$searchargs = ' AND (' . implode(' ' . $sqlskey . ' ', $searchargs) . ')';
}
$totalon = 0;
$totaloff = 0;
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
$sql = '';
if ($count) {
$sql .= 'SELECT COUNT(n.id) ';
} else {
$sql .= 'SELECT n.id AS id, n.ipaddr, inet_ntoa(n.ipaddr) AS ip, ipaddr_pub,
inet_ntoa(n.ipaddr_pub) AS ip_pub, n.mac, n.name, n.ownerid, n.access, n.warning,
n.netdev, n.lastonline, n.info, ' . $this->db->Concat('c.lastname', "' '", 'c.name') . ' AS owner, net.name AS netname, n.location,
lb.name AS borough_name, lb.type AS borough_type,
ld.name AS district_name, ls.name AS state_name ';
}
$sql .= 'FROM vnodes n
JOIN customerview c ON (n.ownerid = c.id)
JOIN networks net ON net.id = n.netid
LEFT JOIN location_cities lc ON lc.id = n.location_city
LEFT JOIN location_boroughs lb ON lb.id = lc.boroughid
LEFT JOIN location_districts ld ON ld.id = lb.districtid
LEFT JOIN location_states ls ON ls.id = ld.stateid ' . ($customergroup ? 'JOIN customerassignments ON (customerid = c.id) ' : '') . ($nodegroup ? 'JOIN nodegroupassignments ON (nodeid = n.id) ' : '') . ' WHERE 1=1 ' . ($network ? ' AND (n.netid = ' . $network . ' OR (n.ipaddr_pub > ' . $net['address'] . ' AND n.ipaddr_pub < ' . $net['broadcast'] . '))' : '') . ($status == 1 ? ' AND n.access = 1' : '') . ($status == 2 ? ' AND n.access = 0' : '') . ($status == 3 ? ' AND n.lastonline > ?NOW? - ' . intval(ConfigHelper::getConfig('phpui.lastonline_limit')) : '') . ($status == 4 ? ' AND n.id NOT IN (
SELECT DISTINCT nodeid FROM nodeassignments na
JOIN assignments a ON a.id = na.assignmentid
WHERE a.suspended = 0 AND a.period IN (' . implode(',', array(YEARLY, HALFYEARLY, QUARTERLY, MONTHLY, DISPOSABLE)) . ')
AND a.datefrom <= ?NOW? AND (a.dateto = 0 OR a.dateto >= ?NOW?)
)' : '') . ($status == 5 ? ' AND n.location_city IS NULL' : '') . ($status == 6 ? ' AND n.netdev = 0' : '') . ($status == 7 ? ' AND n.warning = 1' : '') . ($status == 8 ? ' AND (n.latitude IS NULL OR n.longitude IS NULL)' : '') . ($customergroup ? ' AND customergroupid = ' . intval($customergroup) : '') . ($nodegroup ? ' AND nodegroupid = ' . intval($nodegroup) : '') . (isset($searchargs) ? $searchargs : '') . ($sqlord != '' && !$count ? $sqlord . ' ' . $direction : '') . ($limit !== null && !$count ? ' LIMIT ' . $limit : '') . ($offset !== null && !$count ? ' OFFSET ' . $offset : '');
if (!$count) {
$nodelist = $this->db->GetAll($sql);
if (!empty($nodelist)) {
foreach ($nodelist as $idx => $row) {
$row['access'] ? $totalon++ : $totaloff++;
}
$nodelist['total'] = sizeof($nodelist);
$nodelist['order'] = $order;
$nodelist['direction'] = $direction;
$nodelist['totalon'] = $totalon;
$nodelist['totaloff'] = $totaloff;
return $nodelist;
}
} else {
return $this->db->getOne($sql);
}
}
/**
* Returns customer group
*
* @param int $id Customer group id
* @param int $network Network id
* @return array Customer group
*/
public function CustomergroupGet($id, $network = NULL)
{
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
$result = $this->db->GetRow('SELECT id, name, description
FROM customergroups WHERE id=?', array($id));
$result['customers'] = $this->db->GetAll('SELECT c.id AS id,' . $this->db->Concat('c.lastname', "' '", 'c.name') . ' AS customername
FROM customerassignments, customers c ' . ($network ? 'LEFT JOIN nodes ON c.id = nodes.ownerid ' : '') . 'WHERE c.id = customerid AND customergroupid = ? ' . ($network ? 'AND ((ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ') OR
(ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')) ' : '') . ' GROUP BY c.id, c.lastname, c.name ORDER BY c.lastname, c.name', array($id));
$result['customerscount'] = sizeof($result['customers']);
$result['count'] = $network ? $this->CustomergroupWithCustomerGet($id) : $result['customerscount'];
return $result;
}
/**
* Returns customers without groups
*
* @param int $groupid Customer group id
* @param int $network Network id
* @return array Customers
*/
public function GetCustomerWithoutGroupNames($groupid, $network = NULL)
{
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
return $this->db->GetAll('SELECT c.id AS id, ' . $this->db->Concat('c.lastname', "' '", 'c.name') . ' AS customername
FROM customerview c ' . ($network ? 'LEFT JOIN nodes ON (c.id = nodes.ownerid) ' : '') . 'WHERE c.deleted = 0 AND c.id NOT IN (
SELECT customerid FROM customerassignments WHERE customergroupid = ?) ' . ($network ? 'AND ((ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ') OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')) ' : '') . 'GROUP BY c.id, c.lastname, c.name
ORDER BY c.lastname, c.name', array($groupid));
}
public function GetTariff($id, $network = NULL)
{
if ($network) {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$net = $network_manager->GetNetworkParams($network);
}
$result = $this->db->GetRow('SELECT t.*, taxes.label AS tax, taxes.value AS taxvalue
FROM tariffs t
LEFT JOIN taxes ON (t.taxid = taxes.id)
WHERE t.id=?', array($id));
$result['customers'] = $this->db->GetAll('SELECT c.id AS id, COUNT(c.id) AS cnt, ' . $this->db->Concat('c.lastname', "' '", 'c.name') . ' AS customername ' . ($network ? ', COUNT(vnodes.id) AS nodescount ' : '') . 'FROM assignments, customerview c ' . ($network ? 'LEFT JOIN vnodes ON (c.id = vnodes.ownerid) ' : '') . 'WHERE c.id = customerid AND deleted = 0 AND tariffid = ? ' . ($network ? 'AND ((ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ') OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')) ' : '') . 'GROUP BY c.id, c.lastname, c.name ORDER BY c.lastname, c.name', array($id));
$unactive = $this->db->GetRow('SELECT COUNT(*) AS count,
SUM(CASE t.period
WHEN ' . MONTHLY . ' THEN t.value
WHEN ' . QUARTERLY . ' THEN t.value/3
WHEN ' . HALFYEARLY . ' THEN t.value/6
WHEN ' . YEARLY . ' THEN t.value/12
ELSE (CASE a.period
WHEN ' . MONTHLY . ' THEN t.value
WHEN ' . QUARTERLY . ' THEN t.value/3
WHEN ' . HALFYEARLY . ' THEN t.value/6
WHEN ' . YEARLY . ' THEN t.value/12
ELSE 0
END)
END) AS value
FROM assignments a
JOIN tariffs t ON (t.id = a.tariffid)
WHERE t.id = ? AND (
a.suspended = 1
OR a.datefrom > ?NOW?
OR (a.dateto <= ?NOW? AND a.dateto != 0)
OR EXISTS (
SELECT 1 FROM assignments b
WHERE b.customerid = a.customerid
AND liabilityid = 0 AND tariffid = 0
AND (b.datefrom <= ?NOW? OR b.datefrom = 0)
AND (b.dateto > ?NOW? OR b.dateto = 0)
)
)', array($id));
$all = $this->db->GetRow('SELECT COUNT(*) AS count,
SUM(CASE t.period
WHEN ' . MONTHLY . ' THEN t.value
WHEN ' . QUARTERLY . ' THEN t.value/3
WHEN ' . HALFYEARLY . ' THEN t.value/6
WHEN ' . YEARLY . ' THEN t.value/12
ELSE (CASE a.period
WHEN ' . MONTHLY . ' THEN t.value
WHEN ' . QUARTERLY . ' THEN t.value/3
WHEN ' . HALFYEARLY . ' THEN t.value/6
WHEN ' . YEARLY . ' THEN t.value/12
ELSE 0
END)
END) AS value
FROM assignments a
JOIN tariffs t ON (t.id = a.tariffid)
WHERE tariffid = ?', array($id));
// count of all customers with that tariff
$result['customerscount'] = sizeof($result['customers']);
// count of all assignments
$result['count'] = $all['count'];
// count of 'active' assignments
$result['activecount'] = $all['count'] - $unactive['count'];
// avg monthly income (without unactive assignments)
$result['totalval'] = $all['value'] - $unactive['value'];
$result['rows'] = ceil($result['customerscount'] / 2);
return $result;
}
protected function customerNodesProvider($customer_id, $type = '', $count = null)
{
$type = strtolower($type);
switch ($type) {
case 'netdev':
$type = 'nd.ownerid = ?';
break;
default:
$type = 'n.ownerid = ?';
}
$result = $this->db->GetAll("SELECT\n n.id, n.name, mac, ipaddr, inet_ntoa(ipaddr) AS ip, nd.name as netdev_name,\n ipaddr_pub, n.authtype, inet_ntoa(ipaddr_pub) AS ip_pub,\n passwd, access, warning, info, n.ownerid, lastonline, n.location,\n (SELECT COUNT(*)\n FROM nodegroupassignments\n WHERE nodeid = n.id) AS gcount,\n n.netid, net.name AS netname\n FROM\n vnodes n\n JOIN networks net ON net.id = n.netid\n LEFT JOIN netdevices nd ON n.netdev = nd.id\n WHERE\n " . $type . "\n ORDER BY\n n.name ASC " . ($count ? 'LIMIT ' . $count : ''), array($customer_id));
if ($result) {
// assign network(s) to node record
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache);
$networks = (array) $network_manager->GetNetworks();
foreach ($result as $idx => $node) {
$ids[$node['id']] = $idx;
$result[$idx]['lastonlinedate'] = lastonline_date($node['lastonline']);
if ($node['ipaddr_pub']) {
foreach ($networks as $net) {
if (isipin($node['ip_pub'], $net['address'], $net['mask'])) {
$result[$idx]['network_pub'] = $net;
break;
}
}
}
}
// get EtherWerX channels
if (ConfigHelper::checkValue(ConfigHelper::getConfig('phpui.ewx_support', false))) {
$channels = $this->db->GetAllByKey('SELECT nodeid, channelid, c.name, c.id, cid,
nc.upceil, nc.downceil
FROM ewx_stm_nodes
JOIN ewx_stm_channels nc ON (channelid = nc.id)
LEFT JOIN ewx_channels c ON (c.id = nc.cid)
WHERE nodeid IN (' . implode(',', array_keys($ids)) . ')', 'nodeid');
if ($channels) {
foreach ($channels as $channel) {
$idx = $ids[$channel['nodeid']];
$result[$idx]['channelid'] = $channel['id'] ? $channel['id'] : $channel['channelid'];
$result[$idx]['channelname'] = $channel['name'];
$result[$idx]['cid'] = $channel['cid'];
$result[$idx]['downceil'] = $channel['downceil'];
$result[$idx]['upceil'] = $channel['upceil'];
}
}
}
}
return $result;
}
