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; }