public function GetNode($id)
{
if ($result = $this->db->GetRow('SELECT n.*, rs.name AS linkradiosectorname,
inet_ntoa(n.ipaddr) AS ip, inet_ntoa(n.ipaddr_pub) AS ip_pub,
lc.name AS city_name,
(CASE WHEN ls.name2 IS NOT NULL THEN ' . $this->db->Concat('ls.name2', "' '", 'ls.name') . ' ELSE ls.name END) AS street_name,
lt.name AS street_type,
lb.name AS borough_name, lb.type AS borough_type,
ld.name AS district_name, lst.name AS state_name
FROM vnodes n
LEFT JOIN netradiosectors rs ON rs.id = n.linkradiosector
LEFT JOIN location_cities lc ON (lc.id = n.location_city)
LEFT JOIN location_streets ls ON (ls.id = n.location_street)
LEFT JOIN location_street_types lt ON (lt.id = ls.typeid)
LEFT JOIN location_boroughs lb ON (lb.id = lc.boroughid)
LEFT JOIN location_districts ld ON (ld.id = lb.districtid)
LEFT JOIN location_states lst ON (lst.id = ld.stateid)
WHERE n.id = ?', array($id))) {
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$user_manager = new LMSUserManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['radiosectors'] = $this->db->GetAll('SELECT * FROM netradiosectors WHERE netdev = ?', array($result['netdev']));
$result['owner'] = $customer_manager->GetCustomerName($result['ownerid']);
$result['createdby'] = $user_manager->GetUserName($result['creatorid']);
$result['modifiedby'] = $user_manager->GetUserName($result['modid']);
$result['creationdateh'] = date('Y/m/d, H:i', $result['creationdate']);
$result['moddateh'] = date('Y/m/d, H:i', $result['moddate']);
$result['lastonlinedate'] = lastonline_date($result['lastonline']);
$result['mac'] = preg_split('/,/', $result['mac']);
foreach ($result['mac'] as $mac) {
$result['macs'][] = array('mac' => $mac, 'producer' => get_producer($mac));
}
unset($result['mac']);
if ($netname = $this->db->GetOne('SELECT name FROM networks
WHERE id = ?', array($result['netid']))) {
$result['netname'] = $netname;
}
if ($result['ip_pub'] != '0.0.0.0') {
$network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['netpubid'] = $network_manager->GetNetIDByIP($result['ip_pub']);
$result['netpubname'] = $this->db->GetOne('SELECT name FROM networks
WHERE id = ?', array($result['netpubid']));
}
return $result;
} else {
return FALSE;
}
}
/**
* Returns customer data
*
* @global array $CONTACTTYPES
* @param int $id Customer id
* @param boolean $short Basic or expanded data
* @return array|boolean Customer data or false on failure
*/
public function GetCustomer($id, $short = false)
{
global $CONTACTTYPES;
if ($result = $this->db->GetRow('SELECT c.*, ' . $this->db->Concat('UPPER(c.lastname)', "' '", 'c.name') . ' AS customername,
d.shortname AS division, d.account
FROM customers' . (defined('LMS-UI') ? 'view' : '') . ' c
LEFT JOIN divisions d ON (d.id = c.divisionid)
WHERE c.id = ?', array($id))) {
if (!$short) {
$user_manager = new LMSUserManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['createdby'] = $user_manager->getUserName($result['creatorid']);
$result['modifiedby'] = $user_manager->getUserName($result['modid']);
$result['creationdateh'] = date('Y/m/d, H:i', $result['creationdate']);
$result['moddateh'] = date('Y/m/d, H:i', $result['moddate']);
$result['consentdate'] = $result['consentdate'] ? date('Y/m/d', $result['consentdate']) : '';
$result['up_logins'] = $this->db->GetRow('SELECT lastlogindate, lastloginip,
failedlogindate, failedloginip
FROM up_customers WHERE customerid = ?', array($result['id']));
// Get country name
if ($result['countryid']) {
$result['country'] = $this->db->GetOne('SELECT name FROM countries WHERE id = ?', array($result['countryid']));
if ($result['countryid'] == $result['post_countryid']) {
$result['post_country'] = $result['country'];
} else {
if ($result['post_countryid']) {
$result['country'] = $this->db->GetOne('SELECT name FROM countries WHERE id = ?', array($result['post_countryid']));
}
}
}
// Get state name
if ($cstate = $this->db->GetRow('SELECT s.id, s.name
FROM states s, zipcodes
WHERE zip = ? AND stateid = s.id', array($result['zip']))) {
$result['stateid'] = $cstate['id'];
$result['cstate'] = $cstate['name'];
}
if ($result['zip'] == $result['post_zip']) {
$result['post_stateid'] = $result['stateid'];
$result['post_cstate'] = $result['cstate'];
} else {
if ($result['post_zip'] && ($cstate = $this->db->GetRow('SELECT s.id, s.name
FROM states s, zipcodes
WHERE zip = ? AND stateid = s.id', array($result['post_zip'])))) {
$result['post_stateid'] = $cstate['id'];
$result['post_cstate'] = $cstate['name'];
}
}
}
$result['balance'] = $this->getCustomerBalance($result['id']);
$result['bankaccount'] = bankaccount($result['id'], $result['account']);
$result['messengers'] = $this->db->GetAllByKey('SELECT uid, type
FROM imessengers WHERE customerid = ? ORDER BY type', 'type', array($result['id']));
$result['contacts'] = $this->db->GetAll('SELECT contact AS phone, name, type
FROM customercontacts
WHERE customerid = ? AND type < ? ORDER BY id', array($result['id'], CONTACT_EMAIL));
$result['emails'] = $this->db->GetAll('SELECT contact AS email, name
FROM customercontacts
WHERE customerid = ? AND type = ? ORDER BY id', array($result['id'], CONTACT_EMAIL));
if (is_array($result['contacts'])) {
foreach ($result['contacts'] as $idx => $row) {
$types = array();
foreach ($CONTACTTYPES as $tidx => $tname) {
if ($row['type'] & $tidx) {
$types[] = $tname;
}
}
if ($types) {
$result['contacts'][$idx]['typestr'] = implode('/', $types);
}
}
}
return $result;
} else {
return false;
}
}
/**
* Returns customer data
*
* @global array $CONTACTTYPES
* @param int $id Customer id
* @param boolean $short Basic or expanded data
* @return array|boolean Customer data or false on failure
*/
public function GetCustomer($id, $short = false)
{
global $CONTACTTYPES, $CUSTOMERCONTACTTYPES;
require_once LIB_DIR . DIRECTORY_SEPARATOR . 'customercontacttypes.php';
if ($result = $this->db->GetRow('SELECT c.*, ' . $this->db->Concat('UPPER(c.lastname)', "' '", 'c.name') . ' AS customername,
d.shortname AS division, d.account
FROM customer' . (defined('LMS-UI') ? '' : 'address') . 'view c
LEFT JOIN divisions d ON (d.id = c.divisionid)
WHERE c.id = ?', array($id))) {
if (!$short) {
$user_manager = new LMSUserManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['createdby'] = $user_manager->getUserName($result['creatorid']);
$result['modifiedby'] = $user_manager->getUserName($result['modid']);
$result['creationdateh'] = date('Y/m/d, H:i', $result['creationdate']);
$result['moddateh'] = date('Y/m/d, H:i', $result['moddate']);
$result['consentdate'] = $result['consentdate'] ? date('Y/m/d', $result['consentdate']) : '';
$result['up_logins'] = $this->db->GetRow('SELECT lastlogindate, lastloginip,
failedlogindate, failedloginip
FROM up_customers WHERE customerid = ?', array($result['id']));
// Get country name
if ($result['countryid']) {
$result['country'] = $this->db->GetOne('SELECT name FROM countries WHERE id = ?', array($result['countryid']));
if ($result['countryid'] == $result['post_countryid']) {
$result['post_country'] = $result['country'];
} else {
if ($result['post_countryid']) {
$result['country'] = $this->db->GetOne('SELECT name FROM countries WHERE id = ?', array($result['post_countryid']));
}
}
}
// Get state name
if ($cstate = $this->db->GetRow('SELECT s.id, s.name
FROM states s, zipcodes
WHERE zip = ? AND stateid = s.id', array($result['zip']))) {
$result['stateid'] = $cstate['id'];
$result['cstate'] = $cstate['name'];
}
if ($result['zip'] == $result['post_zip']) {
$result['post_stateid'] = $result['stateid'];
$result['post_cstate'] = $result['cstate'];
} else {
if ($result['post_zip'] && ($cstate = $this->db->GetRow('SELECT s.id, s.name
FROM states s, zipcodes
WHERE zip = ? AND stateid = s.id', array($result['post_zip'])))) {
$result['post_stateid'] = $cstate['id'];
$result['post_cstate'] = $cstate['name'];
}
}
}
$result['balance'] = $this->getCustomerBalance($result['id']);
$result['bankaccount'] = bankaccount($result['id'], $result['account']);
foreach ($CUSTOMERCONTACTTYPES as $contacttype => $properties) {
$result[$contacttype . 's'] = $this->db->GetAll('SELECT contact AS ' . $contacttype . ',
contact, name, type
FROM customercontacts
WHERE customerid = ? AND type & ? > 0 ORDER BY id', array($result['id'], $properties['flagmask']));
}
$result['sendinvoices'] = false;
foreach (array_keys($CUSTOMERCONTACTTYPES) as $ctype) {
$customercontacttype = $CUSTOMERCONTACTTYPES[$ctype];
$ctype .= 's';
if (is_array($result[$ctype])) {
foreach ($result[$ctype] as $idx => $row) {
$types = array();
foreach ($CONTACTTYPES as $tidx => $tname) {
if ($row['type'] & $tidx && isset($customercontacttype['ui']['flags'][$row['type'] & $tidx])) {
$types[] = $tname;
}
}
if (isset($customercontacttype['ui']['typeselectors'])) {
$result[$ctype][$idx]['typeselector'] = $tidx;
}
if ($ctype == 'emails' && ($row['type'] & (CONTACT_INVOICES | CONTACT_DISABLED)) == CONTACT_INVOICES) {
$result['sendinvoices'] = true;
}
if ($types) {
$result[$ctype][$idx]['typestr'] = implode('/', $types);
}
}
}
}
$result['contacts'] = $result['phones'];
if (empty($result['invoicenotice'])) {
$result['sendinvoices'] = false;
}
return $result;
} else {
return false;
}
}
/**
* Returns VoIP account data
*
* @param int $id VoIP account id
* @return array|false VoIP account data on success, false on failure
*/
public function getVoipAccount($id)
{
$result = $this->db->GetRow('
SELECT v.id, ownerid, login, passwd, phone, creationdate, moddate, creatorid, modid, access,
location, location_city, location_street, location_house, location_flat,
lb.name AS borough_name, ld.name AS district_name, ls.name AS state_name
FROM voipaccounts v
LEFT JOIN location_cities lc ON lc.id = v.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
WHERE v.id = ?', array($id));
if ($result) {
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$user_manager = new LMSUserManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['createdby'] = $user_manager->getUserName($result['creatorid']);
$result['modifiedby'] = $user_manager->getUserName($result['modid']);
$result['creationdateh'] = date('Y/m/d, H:i', $result['creationdate']);
$result['moddateh'] = date('Y/m/d, H:i', $result['moddate']);
$result['owner'] = $customer_manager->getCustomerName($result['ownerid']);
return $result;
} else {
return FALSE;
}
}
/**
* Returns VoIP account data
*
* @param int $id VoIP account id
* @return array|false VoIP account data on success, false on failure
*/
public function getVoipAccount($id)
{
$result = $this->db->GetRow('
SELECT v.id, ownerid, login, passwd, creationdate, moddate, creatorid, modid, access, balance,
location, location_city, location_street, location_house, location_flat,
lb.name AS borough_name, ld.name AS district_name, lst.name AS state_name,
lc.name AS city_name,
(CASE WHEN ls.name2 IS NOT NULL THEN ' . $this->db->Concat('ls.name2', "' '", 'ls.name') . ' ELSE ls.name END) AS street_name,
lt.name AS street_type,
v.flags, v.balance, v.cost_limit
FROM voipaccounts v
LEFT JOIN location_cities lc ON lc.id = v.location_city
LEFT JOIN location_streets ls ON (ls.id = v.location_street)
LEFT JOIN location_street_types lt ON (lt.id = ls.typeid)
LEFT JOIN location_boroughs lb ON lb.id = lc.boroughid
LEFT JOIN location_districts ld ON ld.id = lb.districtid
LEFT JOIN location_states lst ON lst.id = ld.stateid
WHERE v.id = ?', array($id));
if ($result) {
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$user_manager = new LMSUserManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['createdby'] = $user_manager->getUserName($result['creatorid']);
$result['modifiedby'] = $user_manager->getUserName($result['modid']);
$result['creationdateh'] = date('Y/m/d, H:i', $result['creationdate']);
$result['moddateh'] = date('Y/m/d, H:i', $result['moddate']);
$result['phones'] = $this->db->GetAll('SELECT phone, number_index FROM voip_numbers WHERE voip_account_id = ?;', array($id));
$result['owner'] = $customer_manager->getCustomerName($result['ownerid']);
return $result;
}
return FALSE;
}