public function GetNetworkRecord($id, $page = 0, $plimit = 4294967296, $firstfree = false)
{
$network = $this->db->GetRow('SELECT no.ownerid, ne.id, ne.name, inet_ntoa(ne.address) AS address,
ne.address AS addresslong, ne.mask, ne.interface, ne.gateway, ne.dns, ne.dns2,
ne.domain, ne.wins, ne.dhcpstart, ne.dhcpend, ne.hostid,
mask2prefix(inet_aton(ne.mask)) AS prefix, ne.notes,
inet_ntoa(broadcast(ne.address, inet_aton(ne.mask))) AS broadcast
FROM networks ne
LEFT JOIN nodes no ON (no.netid = ne.id AND no.ipaddr = 0 AND no.ipaddr_pub = 0)
WHERE ne.id = ?', array($id));
if ($network['ownerid']) {
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$network['customername'] = $customer_manager->GetCustomerName($network['ownerid']);
}
$nodes = $this->db->GetAllByKey('
SELECT id, name, ipaddr, ownerid, netdev
FROM vnodes WHERE netid = ? AND ipaddr > ? AND ipaddr < ?
UNION ALL
SELECT id, name, ipaddr_pub AS ipaddr, ownerid, netdev
FROM vnodes WHERE ipaddr_pub > ? AND ipaddr_pub < ?', 'ipaddr', array($id, $network['addresslong'], ip_long($network['broadcast']), $network['addresslong'], ip_long($network['broadcast'])));
if ($network['hostid']) {
$network['hostname'] = $this->db->GetOne('SELECT name FROM hosts WHERE id=?', array($network['hostid']));
}
$network['size'] = pow(2, 32 - $network['prefix']);
$network['assigned'] = sizeof($nodes);
$network['free'] = $network['size'] - $network['assigned'] - 2;
if ($network['dhcpstart']) {
$network['free'] = $network['free'] - (ip_long($network['dhcpend']) - ip_long($network['dhcpstart']) + 1);
}
if (!$plimit) {
$plimit = 256;
}
$network['pages'] = ceil($network['size'] / $plimit);
if ($page > $network['pages']) {
$page = $network['pages'];
}
if ($page < 1) {
$page = 1;
}
$page--;
while (1) {
$start = $page * $plimit;
$end = $network['size'] > $plimit ? $start + $plimit : $network['size'];
$network['pageassigned'] = 0;
unset($network['nodes']);
for ($i = 0; $i < $end - $start; $i++) {
$longip = (string) ($network['addresslong'] + $i + $start);
$network['nodes']['addresslong'][$i] = $longip;
$network['nodes']['address'][$i] = long2ip($longip);
if (isset($nodes[$longip])) {
$network['nodes']['id'][$i] = $nodes[$longip]['id'];
$network['nodes']['netdev'][$i] = $nodes[$longip]['netdev'];
$network['nodes']['ownerid'][$i] = $nodes[$longip]['ownerid'];
$network['nodes']['name'][$i] = $nodes[$longip]['name'];
$network['pageassigned']++;
} else {
$network['nodes']['id'][$i] = 0;
if ($longip == $network['addresslong']) {
$network['nodes']['name'][$i] = '<b>NETWORK</b>';
} elseif ($network['nodes']['address'][$i] == $network['broadcast']) {
$network['nodes']['name'][$i] = '<b>BROADCAST</b>';
} elseif ($network['nodes']['address'][$i] == $network['gateway']) {
$network['nodes']['name'][$i] = '<b>GATEWAY</b>';
} elseif ($longip >= ip_long($network['dhcpstart']) && $longip <= ip_long($network['dhcpend'])) {
$network['nodes']['name'][$i] = '<b>DHCP</b>';
} else {
$freenode = true;
}
}
}
if ($firstfree && !isset($freenode)) {
if ($page + 1 >= $network['pages']) {
break;
}
$page++;
} else {
break;
}
}
$network['rows'] = ceil(sizeof($network['nodes']['address']) / 4);
$network['page'] = $page + 1;
return $network;
}
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 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;
}
}
public function GetInvoiceContent($invoiceid)
{
global $PAYTYPES, $LMS;
if ($result = $this->db->GetRow('SELECT d.id, d.number, d.name, d.customerid,
d.userid, d.address, d.zip, d.city, d.countryid, cn.name AS country,
d.ten, d.ssn, d.cdate, d.sdate, d.paytime, d.paytype, d.numberplanid,
d.closed, d.reference, d.reason, d.divisionid,
(SELECT name FROM users WHERE id = d.userid) AS user, n.template,
d.div_name AS division_name, d.div_shortname AS division_shortname,
d.div_address AS division_address, d.div_zip AS division_zip,
d.div_city AS division_city, d.div_countryid AS division_countryid,
d.div_ten AS division_ten, d.div_regon AS division_regon, d.div_account AS account,
d.div_inv_header AS division_header, d.div_inv_footer AS division_footer,
d.div_inv_author AS division_author, d.div_inv_cplace AS division_cplace,
c.pin AS customerpin, c.divisionid AS current_divisionid,
c.street, c.building, c.apartment,
c.post_street, c.post_building, c.post_apartment,
c.post_name, c.post_address, c.post_zip, c.post_city, c.post_countryid
FROM documents d
JOIN customeraddressview c ON (c.id = d.customerid)
LEFT JOIN countries cn ON (cn.id = d.countryid)
LEFT JOIN numberplans n ON (d.numberplanid = n.id)
WHERE d.id = ? AND (d.type = ? OR d.type = ?)', array($invoiceid, DOC_INVOICE, DOC_CNOTE))) {
$result['bankaccounts'] = $this->db->GetCol('SELECT contact FROM customercontacts
WHERE customerid = ? AND (type & ?) = ?', array($result['customerid'], CONTACT_BANKACCOUNT | CONTACT_INVOICES | CONTACT_DISABLED, CONTACT_BANKACCOUNT | CONTACT_INVOICES));
if (empty($result['bankaccounts'])) {
$result['bankaccounts'] = array();
}
$result['pdiscount'] = 0;
$result['vdiscount'] = 0;
$result['totalbase'] = 0;
$result['totaltax'] = 0;
$result['total'] = 0;
if ($result['reference']) {
$result['invoice'] = $this->GetInvoiceContent($result['reference']);
}
if (!$result['division_header']) {
$location_manager = new LMSLocationManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['division_header'] = $result['division_name'] . "\n" . $result['division_address'] . "\n" . $result['division_zip'] . ' ' . $result['division_city'] . ($result['division_countryid'] && $result['countryid'] && $result['division_countryid'] != $result['countryid'] ? "\n" . trans($location_manager->GetCountryName($result['division_countryid'])) : '') . ($result['division_ten'] != '' ? "\n" . trans('TEN') . ' ' . $result['division_ten'] : '');
}
if ($result['content'] = $this->db->GetAll('SELECT invoicecontents.value AS value,
itemid, taxid, taxes.value AS taxvalue, taxes.label AS taxlabel,
prodid, content, count, invoicecontents.description AS description,
tariffid, itemid, pdiscount, vdiscount
FROM invoicecontents
LEFT JOIN taxes ON taxid = taxes.id
WHERE docid=?
ORDER BY itemid', array($invoiceid))) {
foreach ($result['content'] as $idx => $row) {
if (isset($result['invoice'])) {
$row['value'] += $result['invoice']['content'][$idx]['value'];
$row['count'] += $result['invoice']['content'][$idx]['count'];
}
$result['content'][$idx]['basevalue'] = round($row['value'] / (100 + $row['taxvalue']) * 100, 2);
$result['content'][$idx]['total'] = round($row['value'] * $row['count'], 2);
$result['content'][$idx]['totalbase'] = round($result['content'][$idx]['total'] / (100 + $row['taxvalue']) * 100, 2);
$result['content'][$idx]['totaltax'] = round($result['content'][$idx]['total'] - $result['content'][$idx]['totalbase'], 2);
$result['content'][$idx]['value'] = $row['value'];
$result['content'][$idx]['count'] = $row['count'];
if (isset($result['taxest'][$row['taxvalue']])) {
$result['taxest'][$row['taxvalue']]['base'] += $result['content'][$idx]['totalbase'];
$result['taxest'][$row['taxvalue']]['total'] += $result['content'][$idx]['total'];
$result['taxest'][$row['taxvalue']]['tax'] += $result['content'][$idx]['totaltax'];
} else {
$result['taxest'][$row['taxvalue']]['base'] = $result['content'][$idx]['totalbase'];
$result['taxest'][$row['taxvalue']]['total'] = $result['content'][$idx]['total'];
$result['taxest'][$row['taxvalue']]['tax'] = $result['content'][$idx]['totaltax'];
$result['taxest'][$row['taxvalue']]['taxlabel'] = $row['taxlabel'];
}
$result['totalbase'] += $result['content'][$idx]['totalbase'];
$result['totaltax'] += $result['content'][$idx]['totaltax'];
$result['total'] += $result['content'][$idx]['total'];
// for backward compatybility
$result['taxest'][$row['taxvalue']]['taxvalue'] = $row['taxvalue'];
$result['content'][$idx]['pkwiu'] = $row['prodid'];
$result['pdiscount'] += $row['pdiscount'];
$result['vdiscount'] += $row['vdiscount'];
}
}
$result['pdate'] = $result['cdate'] + $result['paytime'] * 86400;
$result['value'] = $result['total'] - (isset($result['invoice']) ? $result['invoice']['value'] : 0);
if ($result['value'] < 0) {
$result['value'] = abs($result['value']);
$result['rebate'] = true;
}
$result['valuep'] = round(($result['value'] - floor($result['value'])) * 100);
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['customerbalance'] = $customer_manager->GetCustomerBalance($result['customerid'], $result['cdate'] + 1);
// NOTE: don't waste CPU/mem when printing history is not set:
if (ConfigHelper::checkValue(ConfigHelper::getConfig('invoices.print_balance_history', false))) {
if (ConfigHelper::checkValue(ConfigHelper::getConfig('invoices.print_balance_history_save', false))) {
$result['customerbalancelist'] = $customer_manager->GetCustomerBalanceList($result['customerid'], $result['cdate']);
} else {
$result['customerbalancelist'] = $customer_manager->GetCustomerBalanceList($result['customerid']);
}
$result['customerbalancelistlimit'] = ConfigHelper::getConfig('invoices.print_balance_history_limit');
}
$result['paytypename'] = $PAYTYPES[$result['paytype']];
// for backward compat.
$result['totalg'] = round(($result['value'] - floor($result['value'])) * 100);
$result['year'] = date('Y', $result['cdate']);
$result['month'] = date('m', $result['cdate']);
$result['pesel'] = $result['ssn'];
$result['nip'] = $result['ten'];
if ($result['post_name'] || $result['post_address']) {
$result['serviceaddr'] = $result['post_name'];
if ($result['post_address']) {
$result['serviceaddr'] .= "\n" . $result['post_address'];
}
if ($result['post_zip'] && $result['post_city']) {
$result['serviceaddr'] .= "\n" . $result['post_zip'] . ' ' . $result['post_city'];
}
}
return $result;
} else {
return FALSE;
}
}
/**
* Commits cash imports located in database
*/
public function CashImportCommit()
{
global $LMS;
$imports = $this->db->GetAll('SELECT i.*, f.idate
FROM cashimport i
LEFT JOIN sourcefiles f ON (f.id = i.sourcefileid)
WHERE i.closed = 0 AND i.customerid <> 0');
if (!empty($imports)) {
$idate = ConfigHelper::checkConfig('finances.cashimport_use_idate');
$icheck = ConfigHelper::checkConfig('finances.cashimport_checkinvoices');
$finance_manager = new LMSFinanceManager($this->db, $this->auth, $this->cache, $this->syslog);
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$cashimports = array();
foreach ($imports as $import) {
$this->db->BeginTrans();
$balance['time'] = $idate ? $import['idate'] : $import['date'];
$balance['type'] = 1;
$balance['value'] = $import['value'];
$balance['customerid'] = $import['customerid'];
$balance['comment'] = $import['description'];
$balance['importid'] = $import['id'];
$balance['sourceid'] = $import['sourceid'];
$balance['userid'] = 0;
if ($import['value'] > 0 && $icheck) {
if ($invoices = $this->db->GetAll('SELECT x.id, x.value FROM (
SELECT d.id,
(SELECT SUM(value*count) FROM invoicecontents WHERE docid = d.id) +
COALESCE((
SELECT SUM((a.value+b.value)*(a.count+b.count)) - SUM(b.value*b.count)
FROM documents dd
JOIN invoicecontents a ON (a.docid = dd.id)
JOIN invoicecontents b ON (dd.reference = b.docid AND a.itemid = b.itemid)
WHERE dd.reference = d.id
GROUP BY dd.reference), 0) AS value,
d.cdate
FROM documents d
WHERE d.customerid = ? AND d.type = ? AND d.closed = 0
GROUP BY d.id, d.cdate
UNION
SELECT d.id, dn.value, d.cdate
FROM documents d
JOIN debitnotecontents dn ON dn.docid = d.id
WHERE d.customerid = ?
) x ORDER BY x.cdate', array($balance['customerid'], DOC_INVOICE, $balance['customerid']))) {
foreach ($invoices as $inv) {
$sum += $inv['value'];
}
$bval = $customer_manager->GetCustomerBalance($balance['customerid']);
$value = f_round($bval + $import['value'] + $sum);
foreach ($invoices as $inv) {
$inv['value'] = f_round($inv['value']);
if ($inv['value'] > $value) {
break;
} else {
// close invoice and assigned credit notes
$this->db->Execute('UPDATE documents SET closed = 1
WHERE id = ? OR reference = ?', array($inv['id'], $inv['id']));
if ($this->syslog) {
foreach (array('id', 'reference') as $key) {
$docids = $this->db->GetCol('SELECT id FROM documents WHERE ' . $key . ' = ?', array($inv['id']));
if (!empty($docids)) {
foreach ($docids as $docid) {
$args = array(SYSLOG::RES_DOC, SYSLOG::RES_CUST, 'closed' => 1);
$this->syslog->AddMessage(SYSLOG::RES_DOC, SYSLOG::OPER_UPDATE, $args);
}
}
}
}
$value -= $inv['value'];
}
}
}
}
$this->db->Execute('UPDATE cashimport SET closed = 1 WHERE id = ?', array($import['id']));
if ($this->syslog) {
$args = array(SYSLOG::RES_CASHIMPORT => $import['id'], SYSLOG::RES_CASHSOURCE => $import['sourceid'], SYSLOG::RES_SOURCEFILE => $import['sourcefileid'], SYSLOG::RES_CUST => $import['customerid'], 'closed' => 1);
$this->syslog->AddMessage(SYSLOG::RES_CASHIMPORT, SYSLOG::OPER_UPDATE, $args);
}
$finance_manager->AddBalance($balance);
$this->db->CommitTrans();
if ($this->db->GetOne('SELECT closed FROM cashimport WHERE id = ?', array($import['id']))) {
$cashimports[] = $import;
}
}
$LMS->executeHook('cashimport_after_commit', array('cashimports' => $cashimports));
}
}
/**
* 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;
}
