/**
* 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);
}
}
