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