/** * Returns customer list * * @param string $order Order * @param int $state State * @param boolean $network With or without network params * @param int $customergroup Customer group * @param array $search Search parameters * @param int $time Timestamp * @param string $sqlskey Logical conjunction * @param int $nodegroup Node group * @param int $division Division id * @return array Customer list */ public function getCustomerList($order = 'customername,asc', $state = null, $network = null, $customergroup = null, $search = null, $time = null, $sqlskey = 'AND', $nodegroup = null, $division = null) { list($order, $direction) = sscanf($order, '%[^,],%s'); $direction != 'desc' ? $direction = 'asc' : ($direction = 'desc'); switch ($order) { case 'id': $sqlord = ' ORDER BY c.id'; break; case 'address': $sqlord = ' ORDER BY address'; break; case 'balance': $sqlord = ' ORDER BY balance'; break; case 'tariff': $sqlord = ' ORDER BY tariffvalue'; break; default: $sqlord = ' ORDER BY customername'; break; } switch ($state) { case 4: // When customer is deleted we have no assigned groups or nodes, see DeleteCustomer(). // Return empty list in this case if (!empty($network) || !empty($customergroup) || !empty($nodegroup)) { $customerlist['total'] = 0; $customerlist['state'] = 0; $customerlist['order'] = $order; $customerlist['direction'] = $direction; return $customerlist; } $deleted = 1; break; case 5: $disabled = 1; break; case 6: $indebted = 1; break; case 7: $online = 1; break; case 8: $groupless = 1; break; case 9: $tariffless = 1; break; case 10: $suspended = 1; break; case 11: $indebted2 = 1; break; case 12: $indebted3 = 1; break; } if ($network) { $network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog); $net = $network_manager->getNetworkParams($network); } $over = 0; $below = 0; if (sizeof($search)) { foreach ($search as $key => $value) { if ($value != '') { switch ($key) { case 'phone': $searchargs[] = 'EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type < ' . CONTACT_EMAIL . ' AND REPLACE(contact, \'-\', \'\') ?LIKE? ' . $this->db->Escape("%{$value}%") . ')'; break; case 'email': $searchargs[] = 'EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type = ' . CONTACT_EMAIL . ' AND contact ?LIKE? ' . $this->db->Escape("%{$value}%") . ')'; break; case 'zip': case 'city': case 'address': // UPPER here is a workaround for postgresql ILIKE bug $searchargs[] = "(UPPER({$key}) ?LIKE? UPPER(" . $this->db->Escape("%{$value}%") . ")\n\t\t\t\t\t\t\t\tOR UPPER(post_{$key}) ?LIKE? UPPER(" . $this->db->Escape("%{$value}%") . '))'; break; case 'customername': // UPPER here is a workaround for postgresql ILIKE bug $searchargs[] = $this->db->Concat('UPPER(c.lastname)', "' '", 'UPPER(c.name)') . ' ?LIKE? UPPER(' . $this->db->Escape("%{$value}%") . ')'; break; case 'createdfrom': if ($search['createdto']) { $searchargs['createdfrom'] = '(creationdate >= ' . intval($value) . ' AND creationdate <= ' . intval($search['createdto']) . ')'; unset($search['createdto']); } else { $searchargs[] = 'creationdate >= ' . intval($value); } break; case 'createdto': if (!isset($searchargs['createdfrom'])) { $searchargs[] = 'creationdate <= ' . intval($value); } break; case 'deletedfrom': if ($search['deletedto']) { $searchargs['deletedfrom'] = '(moddate >= ' . intval($value) . ' AND moddate <= ' . intval($search['deletedto']) . ')'; unset($search['deletedto']); } else { $searchargs[] = 'moddate >= ' . intval($value); } $deleted = 1; break; case 'deletedto': if (!isset($searchargs['deletedfrom'])) { $searchargs[] = 'moddate <= ' . intval($value); } $deleted = 1; break; case 'type': $searchargs[] = 'type = ' . intval($value); break; case 'linktype': $searchargs[] = 'EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND linktype = ' . intval($value) . ')'; break; case 'linktechnology': $searchargs[] = 'EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND linktechnology = ' . intval($value) . ')'; break; case 'linkspeed': $searchargs[] = 'EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND linkspeed = ' . intval($value) . ')'; break; case 'doctype': $val = explode(':', $value); // <doctype>:<fromdate>:<todate> $searchargs[] = 'EXISTS (SELECT 1 FROM documents WHERE customerid = c.id' . (!empty($val[0]) ? ' AND type = ' . intval($val[0]) : '') . (!empty($val[1]) ? ' AND cdate >= ' . intval($val[1]) : '') . (!empty($val[2]) ? ' AND cdate <= ' . intval($val[2]) : '') . ')'; break; case 'stateid': $searchargs[] = 'EXISTS (SELECT 1 FROM zipcodes z WHERE z.zip = c.zip AND z.stateid = ' . intval($value) . ')'; break; case 'tariffs': $searchargs[] = 'EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND (datefrom <= ?NOW? OR datefrom = 0) AND (dateto >= ?NOW? OR dateto = 0) AND (tariffid IN (' . $value . ')))'; break; case 'tarifftype': $searchargs[] = 'EXISTS (SELECT 1 FROM assignments a JOIN tariffs t ON t.id = a.tariffid WHERE a.customerid = c.id AND (datefrom <= ?NOW? OR datefrom = 0) AND (dateto >= ?NOW? OR dateto = 0) AND (t.type = ' . intval($value) . '))'; break; default: $searchargs[] = "{$key} ?LIKE? " . $this->db->Escape("%{$value}%"); } } } } if (isset($searchargs)) { $sqlsarg = implode(' ' . $sqlskey . ' ', $searchargs); } $suspension_percentage = f_round(ConfigHelper::getConfig('finances.suspension_percentage')); if ($customerlist = $this->db->GetAll('SELECT c.id AS id, ' . $this->db->Concat('UPPER(lastname)', "' '", 'c.name') . ' AS customername, status, address, zip, city, countryid, countries.name AS country, cc.email, ten, ssn, c.info AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN (SELECT customerid, (' . $this->db->GroupConcat('contact') . ') AS email FROM customercontacts WHERE type = ' . CONTACT_EMAIL . ' GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN countries ON (c.countryid = countries.id) ' . ($customergroup ? 'LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) ' : '') . 'LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash' . ($time ? ' WHERE time < ' . $time : '') . ' GROUP BY customerid ) b ON (b.customerid = c.id) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS null THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS null THEN l.value ELSE t.value END) / 100) - a.vdiscount) * ' . $suspension_percentage . ' / 100) END) * (CASE t.period WHEN ' . MONTHLY . ' THEN 1 WHEN ' . YEARLY . ' THEN 1/12.0 WHEN ' . HALFYEARLY . ' THEN 1/6.0 WHEN ' . QUARTERLY . ' THEN 1/3.0 ELSE (CASE a.period WHEN ' . MONTHLY . ' THEN 1 WHEN ' . YEARLY . ' THEN 1/12.0 WHEN ' . HALFYEARLY . ' THEN 1/6.0 WHEN ' . QUARTERLY . ' THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id = a.tariffid) LEFT JOIN liabilities l ON (l.id = a.liabilityid AND a.period != ' . DISPOSABLE . ') WHERE (a.datefrom <= ?NOW? OR a.datefrom = 0) AND (a.dateto > ?NOW? OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > ?NOW? - ' . intval(ConfigHelper::getConfig('phpui.lastonline_limit')) . ' THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE c.deleted = ' . intval($deleted) . ($state <= 3 && $state > 0 ? ' AND c.status = ' . intval($state) : '') . ($division ? ' AND c.divisionid = ' . intval($division) : '') . ($online ? ' AND s.online = 1' : '') . ($indebted ? ' AND b.value < 0' : '') . ($indebted2 ? ' AND b.value < -t.value' : '') . ($indebted3 ? ' AND b.value < -t.value * 2' : '') . ($disabled ? ' AND s.ownerid IS NOT null AND s.account > s.acsum' : '') . ($network ? ' AND EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND (netid = ' . $network . ' OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')))' : '') . ($customergroup ? ' AND customergroupid=' . intval($customergroup) : '') . ($nodegroup ? ' AND EXISTS (SELECT 1 FROM nodegroupassignments na JOIN nodes n ON (n.id = na.nodeid) WHERE n.ownerid = c.id AND na.nodegroupid = ' . intval($nodegroup) . ')' : '') . ($groupless ? ' AND NOT EXISTS (SELECT 1 FROM customerassignments a WHERE c.id = a.customerid)' : '') . ($tariffless ? ' AND NOT EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND (datefrom <= ?NOW? OR datefrom = 0) AND (dateto >= ?NOW? OR dateto = 0) AND (tariffid != 0 OR liabilityid != 0))' : '') . ($suspended ? ' AND EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND ( (tariffid = 0 AND liabilityid = 0 AND (datefrom <= ?NOW? OR datefrom = 0) AND (dateto >= ?NOW? OR dateto = 0)) OR ((datefrom <= ?NOW? OR datefrom = 0) AND (dateto >= ?NOW? OR dateto = 0) AND suspended = 1) ))' : '') . (isset($sqlsarg) ? ' AND (' . $sqlsarg . ')' : '') . ($sqlord != '' ? $sqlord . ' ' . $direction : ''))) { foreach ($customerlist as $idx => $row) { // summary if ($row['balance'] > 0) { $over += $row['balance']; } elseif ($row['balance'] < 0) { $below += $row['balance']; } } } $customerlist['total'] = sizeof($customerlist); $customerlist['state'] = $state; $customerlist['order'] = $order; $customerlist['direction'] = $direction; $customerlist['below'] = $below; $customerlist['over'] = $over; return $customerlist; }
/** * Returns customer list * * @param string $order Order * @param int $state State * @param boolean $network With or without network params * @param int $customergroup Customer group * @param array $search Search parameters * @param int $time Timestamp * @param string $sqlskey Logical conjunction * @param int $nodegroup Node group * @param int $division Division id * @param int $limit Limit * @param int $offset Offset * @param boolean $count Count flag * @return array Customer list */ public function getCustomerList($params = array()) { extract($params); if (is_null($order)) { $order = 'customername,asc'; } if (is_null($sqlskey)) { $sqlskey = 'AND'; } if (is_null($count)) { $count = FALSE; } list($order, $direction) = sscanf($order, '%[^,],%s'); $direction != 'desc' ? $direction = 'asc' : ($direction = 'desc'); switch ($order) { case 'id': $sqlord = ' ORDER BY c.id'; break; case 'address': $sqlord = ' ORDER BY address'; break; case 'balance': $sqlord = ' ORDER BY balance'; break; case 'tariff': $sqlord = ' ORDER BY tariffvalue'; break; default: $sqlord = ' ORDER BY customername'; break; } switch ($state) { case 50: // When customer is deleted we have no assigned groups or nodes, see DeleteCustomer(). // Return empty list in this case if (!empty($network) || !empty($customergroup) || !empty($nodegroup)) { $customerlist['total'] = 0; $customerlist['state'] = 0; $customerlist['order'] = $order; $customerlist['direction'] = $direction; return $customerlist; } $deleted = 1; break; case 51: $disabled = 1; break; case 52: $indebted = 1; break; case 53: $online = 1; break; case 54: $groupless = 1; break; case 55: $tariffless = 1; break; case 56: $suspended = 1; break; case 57: $indebted2 = 1; break; case 58: $indebted3 = 1; break; case 59: case 60: case 61: $contracts = $state - 58; $contracts_days = intval(ConfigHelper::getConfig('contracts.contracts_days')); break; case 62: $einvoice = 1; break; case 63: $withactivenodes = 1; break; case 64: $withnodes = 1; break; case 65: $withoutnodes = 1; break; } switch ($as) { case 7: case 14: case 30: $assigment = 'SELECT DISTINCT(a.customerid) FROM assignments a WHERE ' . 'a.suspended = 0 AND a.dateto > ' . time() . ' AND a.dateto <= ' . (time() + $as * 86400) . ' AND NOT EXISTS (SELECT 1 FROM assignments aa WHERE aa.customerid = a.customerid AND aa.datefrom > a.dateto LIMIT 1)'; break; case -1: $assigment = 'SELECT DISTINCT(a.customerid) FROM assignments a WHERE a.suspended = 0 AND a.dateto = 0'; break; case -2: $assigment = 'SELECT DISTINCT(a.customerid) FROM assignments a WHERE a.suspended = 0 AND (a.dateto = 0 OR a.dateto > ' . time() . ')'; break; case -3: $assigment = 'SELECT DISTINCT(a.customerid) FROM assignments a WHERE a.invoice = 1 AND a.suspended = 0 AND (a.dateto = 0 OR a.dateto > ' . time() . ')'; break; case -4: $assigment = 'SELECT DISTINCT(a.customerid) FROM assignments a WHERE a.suspended != 0'; break; default: $assigment = NULL; break; } if ($network) { $network_manager = new LMSNetworkManager($this->db, $this->auth, $this->cache, $this->syslog); $net = $network_manager->getNetworkParams($network); } $over = 0; $below = 0; if (sizeof($search)) { foreach ($search as $key => $value) { if ($value != '') { switch ($key) { case 'phone': $searchargs[] = 'EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND (customercontacts.type & ' . (CONTACT_MOBILE | CONTACT_LANDLINE) . ') > 0 AND REPLACE(contact, \'-\', \'\') ?LIKE? ' . $this->db->Escape("%{$value}%") . ')'; break; case 'email': $searchargs[] = 'EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type & ' . CONTACT_EMAIL . ' = ' . CONTACT_EMAIL . ' AND contact ?LIKE? ' . $this->db->Escape("%{$value}%") . ')'; break; case 'zip': case 'city': case 'address': // UPPER here is a workaround for postgresql ILIKE bug $searchargs[] = "(UPPER({$key}) ?LIKE? UPPER(" . $this->db->Escape("%{$value}%") . ")\n\t\t\t\t\t\t\t\tOR UPPER(post_{$key}) ?LIKE? UPPER(" . $this->db->Escape("%{$value}%") . '))'; break; case 'customername': // UPPER here is a workaround for postgresql ILIKE bug $searchargs[] = $this->db->Concat('UPPER(c.lastname)', "' '", 'UPPER(c.name)') . ' ?LIKE? UPPER(' . $this->db->Escape("%{$value}%") . ')'; break; case 'createdfrom': if ($search['createdto']) { $searchargs['createdfrom'] = '(creationdate >= ' . intval($value) . ' AND creationdate <= ' . intval($search['createdto']) . ')'; unset($search['createdto']); } else { $searchargs[] = 'creationdate >= ' . intval($value); } break; case 'createdto': if (!isset($searchargs['createdfrom'])) { $searchargs[] = 'creationdate <= ' . intval($value); } break; case 'deletedfrom': if ($search['deletedto']) { $searchargs['deletedfrom'] = '(moddate >= ' . intval($value) . ' AND moddate <= ' . intval($search['deletedto']) . ')'; unset($search['deletedto']); } else { $searchargs[] = 'moddate >= ' . intval($value); } $deleted = 1; break; case 'deletedto': if (!isset($searchargs['deletedfrom'])) { $searchargs[] = 'moddate <= ' . intval($value); } $deleted = 1; break; case 'type': $searchargs[] = 'type = ' . intval($value); break; case 'linktype': $searchargs[] = 'EXISTS (SELECT 1 FROM vnodes WHERE ownerid = c.id AND linktype = ' . intval($value) . ')'; break; case 'linktechnology': $searchargs[] = 'EXISTS (SELECT 1 FROM vnodes WHERE ownerid = c.id AND linktechnology = ' . intval($value) . ')'; break; case 'linkspeed': $searchargs[] = 'EXISTS (SELECT 1 FROM vnodes WHERE ownerid = c.id AND linkspeed = ' . intval($value) . ')'; break; case 'doctype': $val = explode(':', $value); // <doctype>:<fromdate>:<todate> $searchargs[] = 'EXISTS (SELECT 1 FROM documents WHERE customerid = c.id' . (!empty($val[0]) ? ' AND type = ' . intval($val[0]) : '') . (!empty($val[1]) ? ' AND cdate >= ' . intval($val[1]) : '') . (!empty($val[2]) ? ' AND cdate <= ' . intval($val[2]) : '') . ')'; break; case 'stateid': $searchargs[] = 'EXISTS (SELECT 1 FROM zipcodes z WHERE z.zip = c.zip AND z.stateid = ' . intval($value) . ')'; break; case 'tariffs': $searchargs[] = 'EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND datefrom <= ?NOW? AND (dateto >= ?NOW? OR dateto = 0) AND (tariffid IN (' . $value . ')))'; break; case 'tarifftype': $searchargs[] = 'EXISTS (SELECT 1 FROM assignments a JOIN tariffs t ON t.id = a.tariffid WHERE a.customerid = c.id AND datefrom <= ?NOW? AND (dateto >= ?NOW? OR dateto = 0) AND (t.type = ' . intval($value) . '))'; break; default: $searchargs[] = "{$key} ?LIKE? " . $this->db->Escape("%{$value}%"); } } } } if (isset($searchargs)) { $sqlsarg = implode(' ' . $sqlskey . ' ', $searchargs); } $suspension_percentage = f_round(ConfigHelper::getConfig('finances.suspension_percentage')); $sql = ''; if ($count) { $sql .= 'SELECT COUNT(*) AS total, SUM(CASE WHEN b.value > 0 THEN b.value ELSE 0 END) AS over, SUM(CASE WHEN b.value < 0 THEN b.value ELSE 0 END) AS below '; } else { $sql .= 'SELECT c.id AS id, ' . $this->db->Concat('UPPER(lastname)', "' '", 'c.name') . ' AS customername, status, address, zip, city, countryid, countries.name AS country, cc.email, ten, ssn, c.info AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn '; } $sql .= 'FROM customerview c LEFT JOIN (SELECT customerid, (' . $this->db->GroupConcat('contact') . ') AS email FROM customercontacts WHERE (type & ' . CONTACT_EMAIL . ' = ' . CONTACT_EMAIL . ') GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN countries ON (c.countryid = countries.id) ' . ($customergroup ? 'LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) ' : '') . 'LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash' . ($time ? ' WHERE time < ' . $time : '') . ' GROUP BY customerid ) b ON (b.customerid = c.id) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS null THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS null THEN l.value ELSE t.value END) / 100) - a.vdiscount) * ' . $suspension_percentage . ' / 100) END) * (CASE t.period WHEN ' . MONTHLY . ' THEN 1 WHEN ' . YEARLY . ' THEN 1/12.0 WHEN ' . HALFYEARLY . ' THEN 1/6.0 WHEN ' . QUARTERLY . ' THEN 1/3.0 ELSE (CASE a.period WHEN ' . MONTHLY . ' THEN 1 WHEN ' . YEARLY . ' THEN 1/12.0 WHEN ' . HALFYEARLY . ' THEN 1/6.0 WHEN ' . QUARTERLY . ' THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id = a.tariffid) LEFT JOIN liabilities l ON (l.id = a.liabilityid AND a.period != ' . DISPOSABLE . ') WHERE a.datefrom <= ?NOW? AND (a.dateto > ?NOW? OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > ?NOW? - ' . intval(ConfigHelper::getConfig('phpui.lastonline_limit')) . ' THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) ' . ($contracts == 1 ? ' LEFT JOIN ( SELECT COUNT(*), d.customerid FROM documents d JOIN documentcontents dc ON dc.docid = d.id WHERE d.type IN (' . DOC_CONTRACT . ',' . DOC_ANNEX . ') GROUP BY d.customerid ) d ON d.customerid = c.id' : '') . ($contracts == 2 ? ' JOIN ( SELECT SUM(CASE WHEN dc.todate < ?NOW? THEN 1 ELSE 0 END), SUM(CASE WHEN dc.todate > ?NOW? THEN 1 ELSE 0 END), d.customerid FROM documents d JOIN documentcontents dc ON dc.docid = d.id WHERE d.type IN (' . DOC_CONTRACT . ',' . DOC_ANNEX . ') GROUP BY d.customerid HAVING SUM(CASE WHEN dc.todate < ?NOW? THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN dc.todate >= ?NOW? THEN 1 ELSE 0 END) = 0 ) d ON d.customerid = c.id' : '') . ($contracts == 3 ? ' JOIN ( SELECT DISTINCT d.customerid FROM documents d JOIN documentcontents dc ON dc.docid = d.id WHERE dc.todate >= ?NOW? AND dc.todate <= ?NOW? + 86400 * ' . $contracts_days . ' AND type IN (' . DOC_CONTRACT . ',' . DOC_ANNEX . ') ) d ON d.customerid = c.id' : '') . ' WHERE c.deleted = ' . intval($deleted) . ($state < 50 && $state > 0 ? ' AND c.status = ' . intval($state) : '') . ($division ? ' AND c.divisionid = ' . intval($division) : '') . ($online ? ' AND s.online = 1' : '') . ($indebted ? ' AND b.value < 0' : '') . ($indebted2 ? ' AND b.value < -t.value' : '') . ($indebted3 ? ' AND b.value < -t.value * 2' : '') . ($einvoice ? ' AND c.einvoice = 1' : '') . ($withactivenodes ? ' AND EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND access = 1)' : '') . ($withnodes ? ' AND EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id)' : '') . ($withoutnodes ? ' AND NOT EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id)' : '') . ($contracts == 1 ? ' AND d.customerid IS NULL' : '') . ($assigment ? ' AND c.id IN (' . $assigment . ')' : '') . ($disabled ? ' AND s.ownerid IS NOT null AND s.account > s.acsum' : '') . ($network ? ' AND EXISTS (SELECT 1 FROM vnodes WHERE ownerid = c.id AND (netid = ' . $network . ' OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . ')))' : '') . ($customergroup ? ' AND customergroupid=' . intval($customergroup) : '') . ($nodegroup ? ' AND EXISTS (SELECT 1 FROM nodegroupassignments na JOIN vnodes n ON (n.id = na.nodeid) WHERE n.ownerid = c.id AND na.nodegroupid = ' . intval($nodegroup) . ')' : '') . ($groupless ? ' AND NOT EXISTS (SELECT 1 FROM customerassignments a WHERE c.id = a.customerid)' : '') . ($tariffless ? ' AND NOT EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND datefrom <= ?NOW? AND (dateto >= ?NOW? OR dateto = 0) AND (tariffid != 0 OR liabilityid != 0))' : '') . ($suspended ? ' AND EXISTS (SELECT 1 FROM assignments a WHERE a.customerid = c.id AND ( (tariffid = 0 AND liabilityid = 0 AND datefrom <= ?NOW? AND (dateto >= ?NOW? OR dateto = 0)) OR (datefrom <= ?NOW? AND (dateto >= ?NOW? OR dateto = 0) AND suspended = 1) ))' : '') . (isset($sqlsarg) ? ' AND (' . $sqlsarg . ')' : '') . ($sqlord != '' && !$count ? $sqlord . ' ' . $direction : '') . ($limit !== null && !$count ? ' LIMIT ' . $limit : '') . ($offset !== null && !$count ? ' OFFSET ' . $offset : ''); if (!$count) { $customerlist = $this->db->GetAll($sql); if (!empty($customerlist)) { foreach ($customerlist as $idx => $row) { // summary if ($row['balance'] > 0) { $over += $row['balance']; } elseif ($row['balance'] < 0) { $below += $row['balance']; } } } $customerlist['total'] = sizeof($customerlist); $customerlist['state'] = $state; $customerlist['order'] = $order; $customerlist['direction'] = $direction; $customerlist['below'] = $below; $customerlist['over'] = $over; return $customerlist; } else { return $this->db->getRow($sql); } }