function getValue($cid, $year, $month)
{
global $DB;
$start = mktime(0, 0, 0, $month, 1, $year);
$end = mktime(23, 59, 59, $month, date("t", mktime(0, 0, 0, $month, 1, $year)), $year);
$suspension_percentage = f_round(get_conf('finances.suspension_percentage', 0));
$sql = '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 <= ' . $start . ' OR a.datefrom = 0) AND (a.dateto >= ' . $end . ' OR a.dateto = 0)
AND a.customerid = ' . $cid . ';';
$tmp = $DB->GetRow($sql);
$val = str_replace(',', '.', sprintf('%01.2f', $tmp['value']));
return $val;
}
WHERE id IN (SELECT in_numberplanid FROM cashregs WHERE id = ?)', array($regid)), '%I') !== false) {
$receipt['in_extended'] = true;
}
$receipt['id'] = $id;
$receipt['regid'] = $regid;
}
$titlenumber = docnumber($record['number'], $record['template'], $record['cdate'], $record['extnumber']);
$layout['pagetitle'] = trans('Advance settlement: $a', $titlenumber);
if (isset($_POST['receipt'])) {
$out_extended = isset($receipt['out_extended']) ? $receipt['out_extended'] : NULL;
$in_extended = isset($receipt['in_extended']) ? $receipt['in_extended'] : NULL;
$receipt = $_POST['receipt'];
$receipt['out_extended'] = $out_extended;
$receipt['in_extended'] = $in_extended;
$receipt['regid'] = $regid;
$value = f_round($receipt['value']);
if ($receipt['type'] == 'return') {
$receipt['cdate'] = $_POST['receiptr']['cdate'];
}
if ($receipt['cdate']) {
list($year, $month, $day) = explode('/', $receipt['cdate']);
if (checkdate($month, $day, $year)) {
$receipt['cdate'] = mktime(date('G', time()), date('i', time()), date('s', time()), $month, $day, $year);
} else {
$error['cdate'] = trans('Incorrect date format!');
$receipt['cdate'] = time();
}
} else {
$receipt['cdate'] = time();
}
$in_plan = $DB->GetOne('SELECT in_numberplanid FROM cashregs WHERE id = ?', array($regid));
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
FROM documents d
WHERE d.customerid = ? AND d.type = ? AND d.closed = 0
GROUP BY d.id, d.cdate ORDER BY d.cdate', array($balance['customerid'], DOC_INVOICE))) {
foreach ($invoices as $inv) {
$sum += $inv['value'];
}
$bval = $LMS->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
$DB->Execute('UPDATE documents SET closed = 1
WHERE id = ? OR reference = ?', array($inv['id'], $inv['id']));
if ($SYSLOG) {
$docid = $DB->GetOne('SELECT id FROM documents
WHERE id = ? OR reference = ?', array($inv['id'], $inv['id']));
$args = array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_DOC] => $docid, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST] => $balance['customerid'], 'closed' => 1);
$SYSLOG->AddMessage(SYSLOG_RES_DOC, SYSLOG_OPER_UPDATE, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_DOC], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST]));
}
$value -= $inv['value'];
}
}
$invoicelist[$idx]['tax'] += $tax;
$invoicelist[$idx]['brutto'] += $sum;
if (!isset($listdata[$taxid])) {
$listdata[$taxid]['tax'] = 0;
$listdata[$taxid]['val'] = 0;
}
$listdata[$taxid]['tax'] += $tax;
$listdata[$taxid]['val'] += $val;
$listdata['tax'] += $tax;
$listdata['brutto'] += $sum;
}
// get used tax rates for building report table
foreach ($listdata as $idx => $val) {
if (is_int($idx)) {
$tax = $taxes[$idx];
$tax['value'] = f_round($tax['value']);
$taxeslist[$idx] = $tax;
$taxescount += $tax['value'] ? 2 : 1;
}
}
}
$SMARTY->assign('listdata', $listdata);
$SMARTY->assign('taxes', $taxeslist);
$SMARTY->assign('taxescount', $taxescount);
$SMARTY->assign('layout', $layout);
$SMARTY->assign('invoicelist', $invoicelist);
if (isset($_POST['extended'])) {
$pages = array();
$totals = array();
$reccount = sizeof($invoicelist);
// hidden option: records count for one page of printout
function AddAssignment($data)
{
$result = array();
// Create assignments according to promotion schema
if (!empty($data['promotiontariffid']) && !empty($data['schemaid'])) {
$data['tariffid'] = $data['promotiontariffid'];
$tariff = $this->DB->GetRow('SELECT a.data, s.data AS sdata,
t.name, t.value, t.period, t.id, t.prodid, t.taxid,
s.continuation, s.ctariffid
FROM promotionassignments a
JOIN promotionschemas s ON (s.id = a.promotionschemaid)
JOIN tariffs t ON (t.id = a.tariffid)
WHERE a.promotionschemaid = ? AND a.tariffid = ?', array($data['schemaid'], $data['promotiontariffid']));
$data_schema = explode(';', $tariff['sdata']);
$data_tariff = explode(';', $tariff['data']);
$datefrom = $data['datefrom'];
$cday = date('d', $datefrom);
foreach ($data_tariff as $idx => $dt) {
list($value, $period) = explode(':', $dt);
// Activation
if (!$idx) {
// if activation value specified, create disposable liability
if (f_round($value)) {
$start_day = date('d', $data['datefrom']);
$start_month = date('n', $data['datefrom']);
$start_year = date('Y', $data['datefrom']);
// payday is before the start of the period
// set activation payday to next month's payday
if ($start_day > $data['at']) {
$_datefrom = $data['datefrom'];
$datefrom = mktime(0, 0, 0, $start_month + 1, $data['at'], $start_year);
}
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array(trans('Activation payment'), str_replace(',', '.', $value), intval($tariff['taxid']), $tariff['prodid']));
$lid = $this->DB->GetLastInsertID('liabilities');
$tariffid = 0;
$period = DISPOSABLE;
$at = $datefrom;
} else {
continue;
}
} else {
$lid = 0;
if (!$period) {
$period = $data['period'];
}
$datefrom = $_datefrom ? $_datefrom : $datefrom;
$_datefrom = 0;
$at = $this->CalcAt($period, $datefrom);
$length = $data_schema[$idx - 1];
$month = date('n', $datefrom);
$year = date('Y', $datefrom);
// assume $data['at'] == 1, set last day of the specified month
$dateto = mktime(23, 59, 59, $month + $length + ($cday && $cday != 1 ? 1 : 0), 0, $year);
$cday = 0;
// Find tariff with specified name+value+period...
$tariffid = $this->DB->GetOne('SELECT id FROM tariffs
WHERE name = ? AND value = ? AND period = ?
LIMIT 1', array($tariff['name'], str_replace(',', '.', $value), $tariff['period']));
// ... if not found clone tariff
if (!$tariffid) {
$this->DB->Execute('INSERT INTO tariffs (name, value, period,
taxid, type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit)
SELECT ?, ?, ?, taxid, type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit
FROM tariffs WHERE id = ?', array($tariff['name'], str_replace(',', '.', $value), $tariff['period'], $tariff['id']));
$tariffid = $this->DB->GetLastInsertId('tariffs');
}
}
// Create assignment
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array($tariffid, $data['customerid'], $period, $at, !empty($data['invoice']) ? 1 : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $idx ? $datefrom : 0, $idx ? $dateto : 0, 0, 0, $lid));
$result[] = $this->DB->GetLastInsertID('assignments');
if ($idx) {
$datefrom = $dateto + 1;
}
}
// add "after promotion" tariff(s)
if ($tariff['continuation'] || !$data_schema[0]) {
$tariffs[] = $tariff['id'];
if ($tariff['ctariffid'] && $data_schema[0] != 0) {
$tariffs[] = $tariff['ctariffid'];
}
// Create assignments
foreach ($tariffs as $t) {
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array($t, $data['customerid'], $data['period'], $this->CalcAt($data['period'], $datefrom), !empty($data['invoice']) ? 1 : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $datefrom, 0, 0, 0, 0));
$result[] = $this->DB->GetLastInsertID('assignments');
}
}
} else {
if (!empty($data['value'])) {
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array($data['name'], str_replace(',', '.', $data['value']), intval($data['taxid']), $data['prodid']));
$lid = $this->DB->GetLastInsertID('liabilities');
}
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array(intval($data['tariffid']), $data['customerid'], $data['period'], $data['at'], !empty($data['invoice']) ? 1 : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $data['datefrom'], $data['dateto'], str_replace(',', '.', $data['pdiscount']), str_replace(',', '.', $data['vdiscount']), isset($lid) ? $lid : 0));
$result[] = $this->DB->GetLastInsertID('assignments');
}
if (!empty($result) && count($result = array_filter($result))) {
if (!empty($data['nodes'])) {
// Use multi-value INSERT query
$values = array();
foreach ((array) $data['nodes'] as $nodeid) {
foreach ($result as $aid) {
$values[] = sprintf('(%d, %d)', $nodeid, $aid);
}
}
$this->DB->Execute('INSERT INTO nodeassignments (nodeid, assignmentid)
VALUES ' . implode(', ', $values));
}
if (!empty($data['locks'])) {
// Use multi-value INSERT query
$values = array();
foreach ($data['locks'] as $lock) {
foreach ($result as $aid) {
$values[] = sprintf('(%d, %d, %d, %d)', $aid, $lock['days'], $lock['from'], $lock['to']);
}
}
$this->DB->Execute('INSERT INTO assignmentlocks (assignmentid, days, fromsec, tosec)
VALUES ' . implode(', ', $values));
}
}
return $result;
}
function commit_cashimport()
{
global $LMS;
$DB = LMSDB::getInstance();
$imports = $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::checkValue(ConfigHelper::getConfig('finances.cashimport_use_idate', false));
$icheck = ConfigHelper::checkValue(ConfigHelper::getConfig('finances.cashimport_checkinvoices', false));
foreach ($imports as $import) {
$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 = $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 = $LMS->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
$DB->Execute('UPDATE documents SET closed = 1
WHERE id = ? OR reference = ?', array($inv['id'], $inv['id']));
$value -= $inv['value'];
}
}
}
}
$DB->Execute('UPDATE cashimport SET closed = 1 WHERE id = ?', array($import['id']));
$LMS->AddBalance($balance);
$DB->CommitTrans();
}
}
}
if (preg_match('/^[0-9]+(\\.[0-9]+)*$/', $contents[$idx]['discount'])) {
$contents[$idx]['pdiscount'] = $contents[$idx]['discount_type'] == DISCOUNT_PERCENTAGE ? floatval($contents[$idx]['discount']) : 0;
$contents[$idx]['vdiscount'] = $contents[$idx]['discount_type'] == DISCOUNT_AMOUNT ? floatval($contents[$idx]['discount']) : 0;
}
if ($contents[$idx]['pdiscount'] < 0 || $contents[$idx]['pdiscount'] > 99.90000000000001 || $contents[$idx]['vdiscount'] < 0) {
$error['discount'] = trans('Wrong discount value!');
}
$contents[$idx]['name'] = isset($newcontents['name'][$idx]) ? $newcontents['name'][$idx] : $item['name'];
$contents[$idx]['tariffid'] = isset($newcontents['tariffid'][$idx]) ? $newcontents['tariffid'][$idx] : $item['tariffid'];
$contents[$idx]['valuebrutto'] = $newcontents['valuebrutto'][$idx] != '' ? $newcontents['valuebrutto'][$idx] : $item['valuebrutto'];
$contents[$idx]['valuenetto'] = $newcontents['valuenetto'][$idx] != '' ? $newcontents['valuenetto'][$idx] : $item['valuenetto'];
$contents[$idx]['valuebrutto'] = f_round($contents[$idx]['valuebrutto']);
$contents[$idx]['valuenetto'] = f_round($contents[$idx]['valuenetto']);
$contents[$idx]['count'] = f_round($contents[$idx]['count']);
$contents[$idx]['pdiscount'] = f_round($contents[$idx]['pdiscount']);
$contents[$idx]['vdiscount'] = f_round($contents[$idx]['vdiscount']);
$taxvalue = $taxeslist[$contents[$idx]['taxid']]['value'];
if ($contents[$idx]['valuenetto'] != $item['valuenetto']) {
$contents[$idx]['valuebrutto'] = round($contents[$idx]['valuenetto'] * ($taxvalue / 100 + 1), 2);
}
if (isset($item['deleted']) && $item['deleted']) {
$contents[$idx]['valuebrutto'] = 0;
$contents[$idx]['cash'] = round($item['valuebrutto'] * $item['count'], 2);
$contents[$idx]['count'] = 0;
} elseif ($contents[$idx]['count'] != $item['count'] || $contents[$idx]['valuebrutto'] != $item['valuebrutto']) {
$contents[$idx]['cash'] = round($item['valuebrutto'] * $item['count'], 2) - round($contents[$idx]['valuebrutto'] * $contents[$idx]['count'], 2);
}
$contents[$idx]['valuebrutto'] = $contents[$idx]['valuebrutto'] - $item['valuebrutto'];
$contents[$idx]['count'] = $contents[$idx]['count'] - $item['count'];
}
$DB->BeginTrans();
break;
case 'additemlist':
if ($marks = $_POST['marks']) {
foreach ($marks as $id) {
$cash = $DB->GetRow('SELECT value, comment, taxid
FROM cash WHERE id = ?', array($id));
$itemdata['cashid'] = $id;
$itemdata['name'] = $cash['comment'];
$itemdata['taxid'] = $cash['taxid'];
$itemdata['tax'] = isset($taxeslist[$itemdata['taxid']]) ? $taxeslist[$itemdata['taxid']]['label'] : '';
$itemdata['discount'] = 0;
$itemdata['pdiscount'] = 0;
$itemdata['vdiscount'] = 0;
$itemdata['count'] = f_round($_POST['l_count'][$id]);
$itemdata['valuebrutto'] = f_round(-$cash['value'] / $itemdata['count']);
$itemdata['s_valuebrutto'] = f_round(-$cash['value']);
$itemdata['valuenetto'] = round($itemdata['valuebrutto'] / ((isset($taxeslist[$itemdata['taxid']]) ? $taxeslist[$itemdata['taxid']]['value'] : 0) / 100 + 1), 2);
$itemdata['s_valuenetto'] = round($itemdata['s_valuebrutto'] / ((isset($taxeslist[$itemdata['taxid']]) ? $taxeslist[$itemdata['taxid']]['value'] : 0) / 100 + 1), 2);
$itemdata['prodid'] = $_POST['l_prodid'][$id];
$itemdata['jm'] = $_POST['l_jm'][$id];
$itemdata['posuid'] = (string) (getmicrotime() + $id);
$itemdata['tariffid'] = 0;
$contents[] = $itemdata;
}
}
break;
case 'deletepos':
if (sizeof($contents)) {
foreach ($contents as $idx => $row) {
if ($row['posuid'] == $_GET['posuid']) {
unset($contents[$idx]);
if ($itemdata['valuenetto'] != 0) {
$itemdata['valuenetto'] = f_round($itemdata['valuenetto'] - $itemdata['valuenetto'] * f_round($itemdata['pdiscount']) / 100 - $itemdata['vdiscount']);
$itemdata['valuebrutto'] = $itemdata['valuenetto'] * ($taxvalue / 100 + 1);
$itemdata['s_valuebrutto'] = f_round($itemdata['valuenetto'] * $itemdata['count'] * ($taxvalue / 100 + 1));
} elseif ($itemdata['valuebrutto'] != 0) {
$itemdata['valuebrutto'] = f_round($itemdata['valuebrutto'] - $itemdata['valuebrutto'] * $itemdata['pdiscount'] / 100 - $itemdata['vdiscount']);
$itemdata['valuenetto'] = round($itemdata['valuebrutto'] / ($taxvalue / 100 + 1), 2);
$itemdata['s_valuebrutto'] = f_round($itemdata['valuebrutto'] * $itemdata['count']);
}
// str_replace here is needed because of bug in some PHP versions (4.3.10)
$itemdata['s_valuenetto'] = f_round($itemdata['s_valuebrutto'] / ($taxvalue / 100 + 1));
$itemdata['valuenetto'] = f_round($itemdata['valuenetto']);
$itemdata['count'] = f_round($itemdata['count']);
$itemdata['discount'] = f_round($itemdata['discount']);
$itemdata['pdiscount'] = f_round($itemdata['pdiscount']);
$itemdata['vdiscount'] = f_round($itemdata['vdiscount']);
$itemdata['tax'] = $taxeslist[$itemdata['taxid']]['label'];
$itemdata['posuid'] = (string) getmicrotime();
$contents[] = $itemdata;
}
break;
case 'deletepos':
if ($invoice['closed']) {
break;
}
if (sizeof($contents)) {
foreach ($contents as $idx => $row) {
if ($row['posuid'] == $_GET['posuid']) {
unset($contents[$idx]);
}
}
public function AddAssignment($data)
{
global $SYSLOG_RESOURCE_KEYS;
$result = array();
// Create assignments according to promotion schema
if (!empty($data['promotiontariffid']) && !empty($data['schemaid'])) {
$data['tariffid'] = $data['promotiontariffid'];
$tariff = $this->DB->GetRow('SELECT a.data, s.data AS sdata,
t.name, t.value, t.period, t.id, t.prodid, t.taxid,
s.continuation, s.ctariffid
FROM promotionassignments a
JOIN promotionschemas s ON (s.id = a.promotionschemaid)
JOIN tariffs t ON (t.id = a.tariffid)
WHERE a.promotionschemaid = ? AND a.tariffid = ?', array($data['schemaid'], $data['promotiontariffid']));
$data_schema = explode(';', $tariff['sdata']);
$data_tariff = explode(';', $tariff['data']);
$datefrom = $data['datefrom'];
$cday = date('d', $datefrom);
foreach ($data_tariff as $idx => $dt) {
list($value, $period) = explode(':', $dt);
// Activation
if (!$idx) {
// if activation value specified, create disposable liability
if (f_round($value)) {
$start_day = date('d', $data['datefrom']);
$start_month = date('n', $data['datefrom']);
$start_year = date('Y', $data['datefrom']);
// payday is before the start of the period
// set activation payday to next month's payday
if ($start_day > $data['at']) {
$_datefrom = $data['datefrom'];
$datefrom = mktime(0, 0, 0, $start_month + 1, $data['at'], $start_year);
}
$args = array('name' => trans('Activation payment'), 'value' => str_replace(',', '.', $value), $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX] => intval($tariff['taxid']), 'prodid' => $tariff['prodid']);
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array_values($args));
$lid = $this->DB->GetLastInsertID('liabilities');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB]] = $lid;
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST]] = $data['customerid'];
$this->SYSLOG->AddMessage(SYSLOG_RES_LIAB, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX]));
}
$tariffid = 0;
$period = DISPOSABLE;
$at = $datefrom;
} else {
continue;
}
} else {
$lid = 0;
if (!$period) {
$period = $data['period'];
}
$datefrom = $_datefrom ? $_datefrom : $datefrom;
$_datefrom = 0;
$at = $this->CalcAt($period, $datefrom);
$length = $data_schema[$idx - 1];
$month = date('n', $datefrom);
$year = date('Y', $datefrom);
// assume $data['at'] == 1, set last day of the specified month
$dateto = mktime(23, 59, 59, $month + $length + ($cday && $cday != 1 ? 1 : 0), 0, $year);
$cday = 0;
// Find tariff with specified name+value+period...
$tariffid = $this->DB->GetOne('SELECT id FROM tariffs
WHERE name = ? AND value = ? AND period = ?
LIMIT 1', array($tariff['name'], str_replace(',', '.', $value), $tariff['period']));
// ... if not found clone tariff
if (!$tariffid) {
$args = $this->DB->GetRow('SELECT name, value, period,
taxid AS ' . $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX] . ', type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit
FROM tariffs WHERE id = ?', array($tariff['id']));
$args = array_merge($args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX] => $args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX]], 'name' => $tariff['name'], 'value' => str_replace(',', '.', $value), 'period' => $tariff['period']));
unset($args['taxid']);
$this->DB->Execute('INSERT INTO tariffs (name, value, period,
taxid, type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array_values($args));
$tariffid = $this->DB->GetLastInsertId('tariffs');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF]] = $tariffid;
$this->SYSLOG->AddMessage(SYSLOG_RES_TARIFF, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX]));
}
}
}
// Create assignment
$args = array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF] => $tariffid, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST] => $data['customerid'], 'period' => $period, 'at' => $at, 'invoice' => !empty($data['invoice']) ? 1 : 0, 'settlement' => !empty($data['settlement']) ? 1 : 0, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN] => !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, 'paytype' => !empty($data['paytype']) ? $data['paytype'] : NULL, 'datefrom' => $idx ? $datefrom : 0, 'dateto' => $idx ? $dateto : 0, 'pdiscount' => 0, 'vdiscount' => 0, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB] => $lid);
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array_values($args));
$id = $this->DB->GetLastInsertID('assignments');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN]] = $id;
$this->SYSLOG->AddMessage(SYSLOG_RES_ASSIGN, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN]));
}
$result[] = $id;
if ($idx) {
$datefrom = $dateto + 1;
}
}
// add "after promotion" tariff(s)
if ($tariff['continuation'] || !$data_schema[0]) {
$tariffs[] = $tariff['id'];
if ($tariff['ctariffid'] && $data_schema[0] != 0) {
$tariffs[] = $tariff['ctariffid'];
}
// Create assignments
foreach ($tariffs as $t) {
$args = array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF] => $t, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST] => $data['customerid'], 'period' => $data['period'], 'at' => $this->CalcAt($data['period'], $datefrom), 'invoice' => !empty($data['invoice']) ? 1 : 0, 'settlement' => !empty($data['settlement']) ? 1 : 0, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN] => !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, 'paytype' => !empty($data['paytype']) ? $data['paytype'] : NULL, 'datefrom' => $datefrom, 'dateto' => 0, 'pdiscount' => 0, 'vdiscount' => 0, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB] => 0);
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array_values($args));
$id = $this->DB->GetLastInsertID('assignments');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN]] = $id;
$this->SYSLOG->AddMessage(SYSLOG_RES_ASSIGN, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN]));
}
$result[] = $id;
}
}
} else {
if (!empty($data['value'])) {
$args = array('name' => $data['name'], 'value' => str_replace(',', '.', $data['value']), $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX] => intval($data['taxid']), 'prodid' => $data['prodid']);
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array_values($args));
$lid = $this->DB->GetLastInsertID('liabilities');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB]] = $lid;
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST]] = $data['customerid'];
$this->SYSLOG->AddMessage(SYSLOG_RES_LIAB, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TAX]));
}
}
$args = array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF] => intval($data['tariffid']), $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST] => $data['customerid'], 'period' => $data['period'], 'at' => $data['at'], 'invoice' => !empty($data['invoice']) ? 1 : 0, 'settlement' => !empty($data['settlement']) ? 1 : 0, $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN] => !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, 'paytype' => !empty($data['paytype']) ? $data['paytype'] : NULL, 'datefrom' => $data['datefrom'], 'dateto' => $data['dateto'], 'pdiscount' => str_replace(',', '.', $data['pdiscount']), 'vdiscount' => str_replace(',', '.', $data['vdiscount']), $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB] => isset($lid) ? $lid : 0);
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array_values($args));
$id = $this->DB->GetLastInsertID('assignments');
if ($this->SYSLOG) {
$args[$SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN]] = $id;
$this->SYSLOG->AddMessage(SYSLOG_RES_ASSIGN, SYSLOG_OPER_ADD, $args, array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_TARIFF], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_LIAB], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NUMPLAN]));
}
$result[] = $id;
}
if (!empty($result) && count($result = array_filter($result))) {
if (!empty($data['nodes'])) {
// Use multi-value INSERT query
$values = array();
foreach ((array) $data['nodes'] as $nodeid) {
foreach ($result as $aid) {
$values[] = sprintf('(%d, %d)', $nodeid, $aid);
}
}
$this->DB->Execute('INSERT INTO nodeassignments (nodeid, assignmentid)
VALUES ' . implode(', ', $values));
if ($this->SYSLOG) {
$nodeassigns = $this->DB->GetAll('SELECT id, nodeid FROM nodeassignments WHERE assignmentid = ?', array($aid));
foreach ($nodeassigns as $nodeassign) {
$args = array($SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NODEASSIGN] => $nodeassign['id'], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_CUST] => $data['customerid'], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_NODE] => $nodeassign['nodeid'], $SYSLOG_RESOURCE_KEYS[SYSLOG_RES_ASSIGN] => $aid);
$this->SYSLOG->AddMessage(SYSLOG_RES_NODEASSIGN, SYSLOG_OPER_ADD, $args, array_keys($args));
}
}
}
}
return $result;
}
$error['count'] = 'Incorrect ammount!';
}
$itemdata['unitname'] = $LMSST->QuantityGetNameById($itemdata['unit']);
if (!preg_match('/^\\d+[,.]{0,1}\\d{0,2}$/i', $itemdata['price']['net']) && !preg_match('/^\\d+[,.]{0,1}\\d{0,2}$/i', $itemdata['price']['gross'])) {
$error['price'] = 'Wrong or missing price!';
}
$itemdata['price']['tax'] = isset($itemdata['price']['taxid']) ? $taxeslist[$itemdata['price']['taxid']]['label'] : '';
if (!$error) {
$taxvalue = isset($itemdata['price']['taxid']) ? $taxeslist[$itemdata['price']['taxid']]['value'] : 0;
if ($itemdata['price']['net'] != 0) {
$itemdata['price']['net'] = f_round($itemdata['price']['net']);
$itemdata['price']['gross'] = f_round($itemdata['price']['net'] * ($taxvalue / 100 + 1), 2);
$itemdata['price']['net'] = f_round($itemdata['price']['gross'] / ($taxvalue / 100 + 1), 2);
} elseif ($itemdata['price']['gross'] != 0) {
$itemdata['price']['gross'] = f_round($itemdata['price']['gross'], 2);
$itemdata['price']['net'] = f_round($itemdata['price']['gross'] / ($taxvalue / 100 + 1), 2);
}
if ($itemdata['count'] > 1) {
$serials = array();
$rnepl['doc']['net'] += $itemdata['count'] * $itemdata['price']['net'];
$rnepl['doc']['gross'] += $itemdata['count'] * $itemdata['price']['gross'];
for ($i = 1; $i < $itemdata['count']; ++$i) {
$serials[] = strtoupper($itemdata['serial'][$i]);
unset($itemdata['serial'][$i]);
}
$itemdata['count'] = 1;
$itemdata['serial'] = strtoupper($itemdata['serial'][0]);
$rnepl['product'][] = $itemdata;
foreach ($serials as $serial) {
$itemdata['serial'] = $serial;
$rnepl['product'][] = $itemdata;
} else {
unset($productedit['leavedate']);
unset($productedit['pricesell']);
}
if (!$error) {
if ($productedit['leavedate']) {
$productedit['leavedate'] = DateChange($productedit['leavedate']);
}
$taxvalue = isset($productedit['taxid']) ? $taxeslist[$productedit['taxid']]['value'] : 0;
if ($productedit['pricebuynet'] != 0) {
$productedit['pricebuynet'] = f_round($productedit['pricebuynet']);
$productedit['pricebuygross'] = f_round($productedit['pricebuynet'] * ($taxvalue / 100 + 1), 2);
$productedit['pricebuynet'] = f_round($productedit['pricebuygross'] / ($taxvalue / 100 + 1), 2);
} elseif ($productedit['pricebuygross'] != 0) {
$productedit['pricebuygross'] = f_round($productedit['pricebuygross'], 2);
$productedit['pricebuynet'] = f_round($productedit['pricebuygross'] / ($taxvalue / 100 + 1), 2);
}
if ($LMSST->StockPositionEdit($productedit)) {
$SMARTY->assign('success', 1);
$SMARTY->assign('reload', 1);
} else {
$error['general'] = trans('Unknown error!');
}
}
} else {
$productedit = $LMSST->StockPositionGetById($_GET['id']);
}
$wlist = $LMSST->WarehouseGetList();
unset($wlist['total']);
unset($wlist['order']);
unset($wlist['direction']);
function GetInvoicesList($search = NULL, $cat = NULL, $group = NULL, $hideclosed = NULL, $order, $pagelimit = 100, $page = NULL, $proforma = 0)
{
global $DB;
if ($order == '') {
$order = 'id,asc';
}
list($order, $direction) = sscanf($order, '%[^,],%s');
$direction == 'desc' ? $direction = 'desc' : ($direction = 'asc');
switch ($order) {
case 'id':
$sqlord = ' ORDER BY d.id';
break;
case 'cdate':
$sqlord = ' ORDER BY d.cdate';
break;
case 'number':
$sqlord = ' ORDER BY number';
break;
case 'value':
$sqlord = ' ORDER BY value';
break;
case 'count':
$sqlord = ' ORDER BY count';
break;
case 'name':
$sqlord = ' ORDER BY name';
break;
}
$where = '';
if ($search != '' && $cat) {
switch ($cat) {
case 'number':
$where = ' AND number = ' . intval($search);
break;
case 'cdate':
$where = ' AND cdate >= ' . intval($search) . ' AND cdate < ' . (intval($search) + 86400);
break;
case 'month':
$last = mktime(23, 59, 59, date('n', $search) + 1, 0, date('Y', $search));
$where = ' AND cdate >= ' . intval($search) . ' AND cdate <= ' . $last;
break;
case 'ten':
$where = ' AND ten = ' . $DB->Escape($search);
break;
case 'customerid':
$where = ' AND d.customerid = ' . intval($search);
break;
case 'name':
$where = ' AND UPPER(d.name) ?LIKE? UPPER(' . $DB->Escape('%' . $search . '%') . ')';
break;
case 'address':
$where = ' AND UPPER(address) ?LIKE? UPPER(' . $DB->Escape('%' . $search . '%') . ')';
break;
case 'value':
$having = ' HAVING CASE reference WHEN 0 THEN
SUM(a.value*a.count)
ELSE
SUM((a.value+b.value)*(a.count+b.count)) - SUM(b.value*b.count)
END = ' . str_replace(',', '.', f_round($search)) . ' ';
break;
}
}
if ($hideclosed) {
$where .= ' AND closed = 0';
}
if ($res = $DB->Exec('SELECT d.id AS id, number, cdate, type,
d.customerid, d.name, address, zip, city, countries.name AS country, template, closed, cancelled, published,
CASE reference WHEN 0 THEN
SUM(a.value*a.count)
ELSE
SUM((a.value+b.value)*(a.count+b.count)) - SUM(b.value*b.count)
END AS value,
COUNT(a.docid) AS count,
i.sendinvoices
FROM documents d
JOIN invoicecontents a ON (a.docid = d.id)
LEFT JOIN invoicecontents b ON (d.reference = b.docid AND a.itemid = b.itemid)
LEFT JOIN countries ON (countries.id = d.countryid)
LEFT JOIN numberplans ON (d.numberplanid = numberplans.id)
LEFT JOIN (
SELECT DISTINCT c.id AS customerid, 1 AS sendinvoices FROM customers c
JOIN customercontacts cc ON cc.customerid = c.id
WHERE invoicenotice = 1 AND cc.type & ' . (CONTACT_INVOICES | CONTACT_DISABLED) . ' = ' . CONTACT_INVOICES . '
) i ON i.customerid = d.customerid
LEFT JOIN (
SELECT DISTINCT a.customerid FROM customerassignments a
JOIN excludedgroups e ON (a.customergroupid = e.customergroupid)
WHERE e.userid = lms_current_user()
) e ON (e.customerid = d.customerid)
WHERE e.customerid IS NULL AND ' . ($proforma ? 'type = ' . DOC_INVOICE_PRO : '(type = ' . DOC_CNOTE . ($cat != 'cnotes' ? ' OR type = ' . DOC_INVOICE : '') . ')') . $where . (!empty($group['group']) ? ' AND ' . (!empty($group['exclude']) ? 'NOT' : '') . ' EXISTS (
SELECT 1 FROM customerassignments WHERE customergroupid = ' . intval($group['group']) . '
AND customerid = d.customerid)' : '') . ' GROUP BY d.id, number, cdate, d.customerid,
d.name, address, zip, city, template, closed, type, reference, countries.name, cancelled, published, sendinvoices ' . (isset($having) ? $having : '') . $sqlord . ' ' . $direction)) {
if ($page > 0) {
$start = ($page - 1) * $pagelimit;
$stop = $start + $pagelimit;
}
$id = 0;
while ($row = $DB->FetchRow($res)) {
$row['customlinks'] = array();
$result[$id] = $row;
// free memory for rows which will not be displayed
if ($page > 0) {
if (($id < $start || $id > $stop) && isset($result[$id])) {
$result[$id] = NULL;
}
} elseif (isset($result[$id - $pagelimit])) {
$result[$id - $pagelimit] = NULL;
}
$id++;
}
$result['page'] = $page > 0 ? $page : ceil($id / $pagelimit);
}
$result['order'] = $order;
$result['direction'] = $direction;
return $result;
}
function GetCustomerList($order = 'customername,asc', $state = NULL, $network = NULL, $customergroup = NULL, $search = NULL, $time = NULL, $sqlskey = 'AND', $nodegroup = NULL, $division = NULL, $firstletter = NULL, $status = NULL, $contractend = NULL, $odlaczeni = NULL, $warn = NULL, $origin = NULL, $osobowosc = NULL, $nodeblock = NULL, $pagestart)
{
global $DB, $LMS;
if ($status && in_array($status, array(16, 17, 6, 11, 12))) {
$_sum = $DB->getone('SELECT SUM(value) FROM cash;');
}
$_cuscount = $DB->getOne('SELECT COUNT(id) FROM customers WHERE type=0 OR type=1;');
$_node = array();
$_order = $order;
if ($odlaczeni || $warn || $nodeblock) {
$_node = $DB->GetRow('SELECT
COUNT(CASE WHEN access=1 THEN 1 END) AS connected,
COUNT(CASE WHEN warning=1 THEN 1 END) AS warning,
COUNT(CASE WHEN blockade=1 THEN 1 END) AS blockade
FROM nodes WHERE ownerid > 0;');
}
list($order, $direction) = sscanf($order, '%[^,],%s');
$direction != 'desc' ? $direction = 'asc' : ($direction = 'desc');
if ($origin) {
$origin = intval($origin);
}
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:
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;
case 15:
$tying = 1;
break;
}
switch ($status) {
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;
case 15:
$tying = 1;
break;
case 16:
$balanceok = 1;
break;
case 17:
$balanceok2 = 1;
break;
}
switch ($odlaczeni) {
case 1:
$disabled = 1;
break;
case 2:
$disabled = 2;
break;
case 3:
$disabled = 3;
break;
case 4:
$disabled = 4;
break;
}
switch ($warn) {
case 1:
$warning = 1;
break;
case 2:
$warning = 2;
break;
case 3:
$warning = 3;
break;
default:
$warning = NULL;
break;
}
switch ($nodeblock) {
case 1:
$blockade = 1;
break;
case 2:
$blockade = 2;
break;
case 3:
$blockade = 3;
break;
case 4:
$blockade = 4;
break;
default:
$blockade = NULL;
break;
}
if ($network) {
$net = $LMS->GetNetworkParams($network);
}
$over = $below = 0;
// pierwsza litera nazwiska
$sqlfl = NULL;
if (!empty($firstletter)) {
$firstletter = strtoupper($firstletter);
switch ($firstletter) {
case 'A':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("A%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ą%") . ' ';
break;
case 'C':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("C%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ć%") . ' ';
break;
case 'E':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("E%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ę%") . ' ';
break;
case 'L':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("L%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ł%") . ' ';
break;
case 'N':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("N%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ń%") . ' ';
break;
case 'O':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("O%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ó%") . ' ';
break;
case 'S':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("S%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ś%") . ' ';
break;
case 'Z':
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("Z%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ź%") . ' OR UPPER(lastname) ?LIKE? ' . $DB->Escape("Ż%") . ' ';
break;
default:
$sqlfl = ' UPPER(lastname) ?LIKE? ' . $DB->Escape("{$firstletter}%");
}
}
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 phone ?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}%") . ") OR 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 '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;
default:
$searchargs[] = "{$key} ?LIKE? " . $this->DB->Escape("%{$value}%");
}
}
}
}
if (isset($searchargs)) {
$sqlsarg = implode(' ' . $sqlskey . ' ', $searchargs);
}
$suspension_percentage = f_round(get_conf('finances.suspension_percentage'));
$md5 = md5(($_order ? $_order : '') . ($state ? $state : '') . ($network ? $network : '') . ($customergroup ? $customergroup : '') . ($sqlskey ? $sqlskey : '') . ($nodegroup ? $nodegroup : '') . ($division ? $division : '') . ($firstletter ? $firstletter : '') . ($status ? $status : '') . ($contractend ? $contractend : '') . ($odlaczeni ? $odlaczeni : '') . ($warn ? $warn : '') . ($origin ? $origin : '') . ($osobowosc ? $osobowosc : '') . ($nodeblock ? $nodeblock : '') . ($sqlsarg ? $sqlsarg : '') . ($time ? $time : '') . ($_sum ? $_sum : '0') . ($_node['connected'] ? $_node['connected'] : '0') . ($_node['warning'] ? $_node['warning'] : '0') . ($_node['blockade'] ? $_node['blockade'] : '0') . ($_cuscount ? $_cuscount : '0'));
$_cache = $LMS->loadCache('customerlist', $md5);
if (!$_cache) {
$preload = $DB->GetAll('SELECT c.id AS id, ' . $DB->Concat('UPPER(lastname)', "' '", 'c.name') . ' AS customername , COALESCE(b.value, 0) AS balance
FROM customersview c
LEFT JOIN countries ON (c.countryid = countries.id) ' . ($customergroup ? 'LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) ' : '') . (in_array(get_conf('database.type'), array('mysql', 'mysqli')) ? ' JOIN customercash b ON (b.customerid = c.id) ' : ' LEFT JOIN (SELECT
SUM(value) AS value, customerid
FROM cash
GROUP BY customerid
) b ON (b.customerid = c.id) ') . ($indebted3 || $indebted2 ? ' 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)' : '') . ($online || !$odlaczeni && $disabled || $odlaczeni && $disabled == 1 || $odlaczeni && $disabled == 2 || $odlaczeni && $disabled == 3 || $warn && $warning == 1 || $warn && $warning == 2 || $warn && $warning == 3 || $nodeblock && $blockade == 1 || $nodeblock && $blockade == 2 || $nodeblock && $blockade == 3 || $nodeblock && $blockade == 4 || $odlaczeni && $disabled == 4 ? ' LEFT JOIN (SELECT ownerid,
SUM(access) AS acsum, COUNT(access) AS account,
SUM(warning) AS warnsum, COUNT(warning) AS warncount,
SUM(blockade) AS blocksum, COUNT(blockade) AS blockcount,
(CASE WHEN MAX(lastonline) > ?NOW? - ' . intval(get_conf('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) . ($tying ? ' AND c.status=4 ' : '') . ($contractend ? ' AND c.id IN (' . $contractend . ')' : '') . ($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' : '') . ($balanceok ? ' AND (b.value = 0 OR b.value IS NULL) ' : '') . ($balanceok2 ? ' AND b.value > 0' : '') . ($origin ? ' AND c.origin = ' . $origin : '') . (!$odlaczeni && $disabled ? ' AND s.ownerid IS NOT NULL AND s.account > s.acsum' : '') . ($odlaczeni && $disabled == 1 ? ' AND s.ownerid IS NOT NULL AND s.acsum = 0 ' : '') . ($odlaczeni && $disabled == 2 ? ' AND s.ownerid IS NOT NULL AND s.account = s.acsum' : '') . ($odlaczeni && $disabled == 3 ? ' AND s.ownerid IS NOT NULL AND s.account > s.acsum AND s.acsum != 0' : '') . ($warn && $warning == 1 ? ' AND s.ownerid IS NOT NULL AND s.warnsum = 0 ' : '') . ($warn && $warning == 2 ? ' AND s.ownerid IS NOT NULL AND s.warncount = s.warnsum' : '') . ($warn && $warning == 3 ? ' AND s.ownerid IS NOT NULL AND s.warncount > s.warnsum AND s.warnsum != 0' : '') . ($nodeblock && $blockade == 1 ? ' AND s.ownerid IS NOT NULL AND s.blockcount = s.blocksum ' : '') . ($nodeblock && $blockade == 2 ? ' AND s.ownerid IS NOT NULL AND s.blocksum = 0 ' : '') . ($nodeblock && $blockade == 3 ? ' AND s.ownerid IS NOT NULL AND s.blockcount > s.blocksum AND s.blocksum != 0 ' : '') . ($nodeblock && $blockade == 4 ? ' AND s.ownerid IS NOT NULL AND c.cutoffstop >= ?NOW? ' : '') . ($osobowosc && $osobowosc == 1 ? ' AND c.type=0 ' : '') . ($osobowosc && $osobowosc == 2 ? ' AND c.type=1 ' : '') . ($odlaczeni && $disabled == 4 ? ' AND s.ownerid IS NULL' : '') . ($network ? ' AND EXISTS (SELECT 1 FROM nodes WHERE ownerid = c.id AND
((ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ')
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 . ')' : '') . (isset($sqlfl) ? ' AND (' . $sqlfl . ') ' : '') . ($sqlord != '' ? $sqlord . ' ' . $direction : ''));
// end preload
$_tmp = array();
for ($i = 0; $i < sizeof($preload); $i++) {
$_tmp[$i]['id'] = $preload[$i]['id'];
$_tmp[$i]['balance'] = $preload[$i]['balance'];
}
$LMS->saveCache('customerlist', $md5, $_tmp);
} else {
$preload = $_cache;
}
$idlist = array();
if ($preload) {
$pageend = $pagestart + get_conf('phpui.customerlist_pagelimit', '50');
for ($pl = $pagestart; $pl < $pageend; $pl++) {
if ($preload[$pl]['id']) {
$idlist[] = $preload[$pl]['id'];
}
}
} else {
$idlist[0] = '0';
}
$_idlist = implode(',', $idlist);
$customerlist = $DB->GetAll('SELECT c.id AS id, ' . $DB->Concat('UPPER(lastname)', "' '", 'c.name') . ' AS customername,
status, address, zip, city, countryid, countries.name AS country, 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, s.blockcount,
c.type AS customertype, cutoffstop,
(SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id) AS lastcash,
(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,
(CASE WHEN s.blockcount = s.blocksum THEN 1 WHEN s.blocksum > 0 THEN 2 ELSE 0 END) as nodeblock
FROM customersview c
LEFT JOIN countries ON (c.countryid = countries.id) ' . ($customergroup ? 'LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) ' : '') . (in_array(get_conf('database.type'), array('mysql', 'mysqli')) ? ' JOIN customercash b ON (b.customerid = c.id) ' : ' LEFT JOIN (SELECT
SUM(value) AS value, customerid
FROM cash
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,
SUM(blockade) AS blocksum, COUNT(blockade) AS blockcount,
(CASE WHEN MAX(lastonline) > ?NOW? - ' . intval(get_conf('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 1=1 ' . ' AND c.id in (' . $_idlist . ') ' . ($sqlord != '' ? $sqlord . ' ' . $direction : ''));
if ($preload) {
foreach ($preload as $idx => $row) {
// summary
if ($row['balance'] > 0) {
$over += $row['balance'];
} elseif ($row['balance'] < 0) {
$below += $row['balance'];
}
}
}
$customerlist['total'] = sizeof($preload);
$customerlist['state'] = $state;
$customerlist['order'] = $order;
$customerlist['direction'] = $direction;
$customerlist['below'] = $below;
$customerlist['over'] = $over;
return $customerlist;
}
if ($itemdata['pdiscount'] < 0 || $itemdata['pdiscount'] > 99.90000000000001 || $itemdata['vdiscount'] < 0) {
$error['discount'] = trans('Wrong discount value!');
}
if ($error) {
break;
}
foreach (array('count', 'discount', 'pdiscount', 'vdiscount', 'valuenetto', 'valuebrutto') as $key) {
$itemdata[$key] = round((double) str_replace(',', '.', $itemdata[$key]), 2);
}
if ($itemdata['count'] > 0 && $itemdata['name'] != '') {
$taxvalue = $taxeslist[$itemdata['taxid']]['value'];
if ($itemdata['valuenetto'] != 0) {
$itemdata['valuenetto'] = f_round($itemdata['valuenetto'] - $itemdata['valuenetto'] * f_round($itemdata['pdiscount']) / 100 - $itemdata['vdiscount']);
$itemdata['valuebrutto'] = round($itemdata['valuenetto'] * ($taxvalue / 100 + 1), 2);
} elseif ($itemdata['valuebrutto'] != 0) {
$itemdata['valuebrutto'] = f_round($itemdata['valuebrutto'] - $itemdata['valuebrutto'] * $itemdata['pdiscount'] / 100 - $itemdata['vdiscount']);
$itemdata['valuenetto'] = round($itemdata['valuebrutto'] / ($taxvalue / 100 + 1), 2);
}
// str_replace here is needed because of bug in some PHP versions (4.3.10)
$itemdata['s_valuebrutto'] = str_replace(',', '.', $itemdata['valuebrutto'] * $itemdata['count']);
$itemdata['s_valuenetto'] = str_replace(',', '.', $itemdata['s_valuebrutto'] / ($taxvalue / 100 + 1));
$itemdata['valuenetto'] = str_replace(',', '.', $itemdata['valuenetto']);
$itemdata['valuebrutto'] = str_replace(',', '.', $itemdata['valuebrutto']);
$itemdata['count'] = str_replace(',', '.', $itemdata['count']);
$itemdata['discount'] = str_replace(',', '.', $itemdata['discount']);
$itemdata['pdiscount'] = str_replace(',', '.', $itemdata['pdiscount']);
$itemdata['vdiscount'] = str_replace(',', '.', $itemdata['vdiscount']);
$itemdata['tax'] = $taxeslist[$itemdata['taxid']]['label'];
$itemdata['posuid'] = (string) getmicrotime();
$contents[] = $itemdata;
}
function form_num($num)
{
return str_replace(',', '.', sprintf('%.2f', f_round($num)));
}
function GetRecipients($filter, $type = MSG_MAIL)
{
global $LMS;
$group = intval($filter['group']);
$network = intval($filter['network']);
if (is_array($filter['customergroup'])) {
$customergroup = array_map('intval', $filter['customergroup']);
$customergroup = implode(',', $customergroup);
} else {
$customergroup = intval($filter['customergroup']);
}
$nodegroup = intval($filter['nodegroup']);
$linktype = intval($filter['linktype']);
$tarifftype = intval($filter['tarifftype']);
$consent = isset($filter['consent']);
if ($group == 50) {
$deleted = 1;
$network = NULL;
$customergroup = NULL;
} else {
$deleted = 0;
}
$disabled = $group == 51 ? 1 : 0;
$indebted = $group == 52 ? 1 : 0;
$notindebted = $group == 53 ? 1 : 0;
$indebted2 = $group == 57 ? 1 : 0;
$indebted3 = $group == 58 ? 1 : 0;
if ($group >= 50) {
$group = 0;
}
if ($network) {
$net = $LMS->GetNetworkParams($network);
}
if ($type == MSG_SMS) {
$smstable = 'JOIN (SELECT ' . $LMS->DB->GroupConcat('contact') . ' AS phone, customerid
FROM customercontacts
WHERE ((type & ' . (CONTACT_MOBILE | CONTACT_DISABLED) . ') = ' . CONTACT_MOBILE . ' )
GROUP BY customerid
) x ON (x.customerid = c.id) ';
} elseif ($type == MSG_MAIL) {
$mailtable = 'JOIN (SELECT ' . $LMS->DB->GroupConcat('contact') . ' AS email, customerid
FROM customercontacts
WHERE ((type & ' . (CONTACT_EMAIL | CONTACT_DISABLED) . ') = ' . CONTACT_EMAIL . ')
GROUP BY customerid
) cc ON (cc.customerid = c.id) ';
}
if ($tarifftype) {
$tarifftable = 'JOIN (
SELECT DISTINCT a.customerid FROM assignments a
JOIN tariffs t ON t.id = a.tariffid
WHERE a.suspended = 0
AND (a.datefrom = 0 OR a.datefrom < ?NOW?)
AND (a.dateto = 0 OR a.dateto > ?NOW?)
AND t.type = ' . $tarifftype . '
) a ON a.customerid = c.id ';
}
$suspension_percentage = f_round(ConfigHelper::getConfig('finances.suspension_percentage'));
$recipients = $LMS->DB->GetAll('SELECT c.id, pin, ' . ($type == MSG_MAIL ? 'cc.email, ' : '') . ($type == MSG_SMS ? 'x.phone, ' : '') . $LMS->DB->Concat('c.lastname', "' '", 'c.name') . ' AS customername,
COALESCE(b.value, 0) AS balance
FROM customerview c
LEFT JOIN (
SELECT SUM(value) AS value, customerid
FROM cash 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) ' . (isset($mailtable) ? $mailtable : '') . (isset($smstable) ? $smstable : '') . ($tarifftype ? $tarifftable : '') . 'WHERE deleted = ' . $deleted . ($consent ? ' AND c.mailingnotice = 1' : '') . ($type == MSG_WWW ? ' AND c.id IN (SELECT DISTINCT ownerid FROM nodes)' : '') . ($group != 0 ? ' AND status = ' . $group : '') . ($network ? ' AND c.id IN (SELECT ownerid FROM vnodes WHERE
(netid = ' . $net['id'] . ' AND ipaddr > ' . $net['address'] . ' AND ipaddr < ' . $net['broadcast'] . ')
OR (ipaddr_pub > ' . $net['address'] . ' AND ipaddr_pub < ' . $net['broadcast'] . '))' : '') . ($customergroup ? ' AND c.id IN (SELECT customerid FROM customerassignments
WHERE customergroupid IN (' . $customergroup . '))' : '') . ($nodegroup ? ' AND c.id IN (SELECT ownerid FROM vnodes
JOIN nodegroupassignments ON (nodeid = vnodes.id)
WHERE nodegroupid = ' . $nodegroup . ')' : '') . ($linktype != '' ? ' AND c.id IN (SELECT ownerid FROM vnodes
WHERE linktype = ' . $linktype . ')' : '') . ($disabled ? ' AND EXISTS (SELECT 1 FROM vnodes WHERE ownerid = c.id
GROUP BY ownerid HAVING (SUM(access) != COUNT(access)))' : '') . ($indebted ? ' AND COALESCE(b.value, 0) < 0' : '') . ($indebted2 ? ' AND COALESCE(b.value, 0) < -t.value' : '') . ($indebted3 ? ' AND COALESCE(b.value, 0) < -t.value * 2' : '') . ($notindebted ? ' AND COALESCE(b.value, 0) >= 0' : '') . ($tarifftype ? ' AND NOT EXISTS (SELECT id FROM assignments
WHERE customerid = c.id AND tariffid = 0 AND liabilityid = 0
AND (datefrom = 0 OR datefrom < ?NOW?)
AND (dateto = 0 OR dateto > ?NOW?))' : '') . ' ORDER BY customername');
return $recipients;
}
/**
* 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);
}
}
function GetNotesList($search = NULL, $cat = NULL, $group = NULL, $hideclosed = NULL, $order, $pagelimit = 100, $page = NULL)
{
global $DB;
if ($order == '') {
$order = 'id,asc';
}
list($order, $direction) = sscanf($order, '%[^,],%s');
$direction == 'desc' ? $direction = 'desc' : ($direction = 'asc');
switch ($order) {
case 'id':
$sqlord = ' ORDER BY d.id';
break;
case 'cdate':
$sqlord = ' ORDER BY d.cdate';
break;
case 'number':
$sqlord = ' ORDER BY number';
break;
case 'value':
$sqlord = ' ORDER BY value';
break;
case 'count':
$sqlord = ' ORDER BY count';
break;
case 'name':
$sqlord = ' ORDER BY name';
break;
}
$where = '';
if ($search != '' && $cat) {
switch ($cat) {
case 'number':
$where = ' AND number = ' . intval($search);
break;
case 'cdate':
$where = ' AND cdate >= ' . intval($search) . ' AND cdate < ' . (intval($search) + 86400);
break;
case 'month':
$last = mktime(23, 59, 59, date('n', $search) + 1, 0, date('Y', $search));
$where = ' AND cdate >= ' . intval($search) . ' AND cdate <= ' . $last;
break;
case 'ten':
$where = ' AND ten = ' . $DB->Escape($search);
break;
case 'customerid':
$where = ' AND d.customerid = ' . intval($search);
break;
case 'name':
$where = ' AND UPPER(d.name) ?LIKE? UPPER(' . $DB->Escape('%' . $search . '%') . ')';
break;
case 'address':
$where = ' AND UPPER(address) ?LIKE? UPPER(' . $DB->Escape('%' . $search . '%') . ')';
break;
case 'value':
$having = ' HAVING SUM(n.value) = ' . str_replace(',', '.', f_round($search)) . ' ';
break;
}
}
$result['page'] = null;
if ($hideclosed) {
$where .= ' AND closed = 0';
}
if ($res = $DB->Exec('SELECT d.id AS id, number, cdate, template, closed,
d.customerid, d.name, address, zip, city, c.name AS country,
SUM(n.value) AS value, COUNT(n.docid) AS count
FROM documents d
JOIN debitnotecontents n ON (n.docid = d.id)
LEFT JOIN countries c ON (c.id = d.countryid)
LEFT JOIN numberplans ON (d.numberplanid = numberplans.id)
LEFT JOIN (
SELECT DISTINCT a.customerid FROM customerassignments a
JOIN excludedgroups e ON (a.customergroupid = e.customergroupid)
WHERE e.userid = lms_current_user()
) e ON (e.customerid = d.customerid)
WHERE e.customerid IS NULL AND type = ' . DOC_DNOTE . $where . (!empty($group['group']) ? ' AND ' . (!empty($group['exclude']) ? 'NOT' : '') . ' EXISTS (
SELECT 1 FROM customerassignments WHERE customergroupid = ' . intval($group['group']) . '
AND customerid = d.customerid)' : '') . ' GROUP BY d.id, number, cdate, d.customerid,
d.name, address, zip, city, template, closed, c.name ' . (isset($having) ? $having : '') . $sqlord . ' ' . $direction)) {
if ($page > 0) {
$start = ($page - 1) * $pagelimit;
$stop = $start + $pagelimit;
}
$id = 0;
while ($row = $DB->FetchRow($res)) {
$result[$id] = $row;
// free memory for rows which will not be displayed
if ($page > 0) {
if (($id < $start || $id > $stop) && isset($result[$id])) {
$result[$id] = NULL;
}
} elseif (isset($result[$id - $pagelimit])) {
$result[$id - $pagelimit] = NULL;
}
$id++;
}
$result['page'] = $page > 0 ? $page : ceil($id / $pagelimit);
}
$result['order'] = $order;
$result['direction'] = $direction;
return $result;
}
function AddAssignment($data)
{
$result = array();
// Create assignments according to promotion schema
if (!empty($data['promotiontariffid']) && !empty($data['schemaid'])) {
$data['tariffid'] = $data['promotiontariffid'];
$tariff = $this->DB->GetRow('SELECT a.data, s.data AS sdata,
t.name, t.value, t.period, t.id, t.prodid, t.taxid,
s.continuation, s.ctariffid
FROM promotionassignments a
JOIN promotionschemas s ON (s.id = a.promotionschemaid)
JOIN tariffs t ON (t.id = a.tariffid)
WHERE a.promotionschemaid = ? AND a.tariffid = ?', array($data['schemaid'], $data['promotiontariffid']));
$data_schema = explode(';', $tariff['sdata']);
$data_tariff = explode(';', $tariff['data']);
$datefrom = $data['datefrom'];
$cday = date('d', $datefrom);
foreach ($data_tariff as $idx => $dt) {
list($value, $period) = explode(':', $dt);
// Activation
if (!$idx) {
// if activation value specified, create disposable liability
if (f_round($value)) {
$start_day = date('d', $data['datefrom']);
$start_month = date('n', $data['datefrom']);
$start_year = date('Y', $data['datefrom']);
// payday is before the start of the period
// set activation payday to next month's payday
if ($start_day > $data['at']) {
$_datefrom = $data['datefrom'];
$datefrom = mktime(0, 0, 0, $start_month + 1, $data['at'], $start_year);
}
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array(trans('Activation payment'), str_replace(',', '.', $value), intval($tariff['taxid']), $tariff['prodid']));
$lid = $this->DB->GetLastInsertID('liabilities');
$tariffid = 0;
$period = DISPOSABLE;
$at = $datefrom;
$promo_discount = 0;
} else {
continue;
}
} else {
$lid = 0;
if (!$period) {
$period = $data['period'];
}
$datefrom = $_datefrom ? $_datefrom : $datefrom;
$_datefrom = 0;
$at = $this->CalcAt($period, $datefrom);
$length = $data_schema[$idx - 1];
$month = date('n', $datefrom);
$year = date('Y', $datefrom);
// assume $data['at'] == 1, set last day of the specified month
$dateto = mktime(23, 59, 59, $month + $length + ($cday && $cday != 1 ? 1 : 0), 0, $year);
$cday = 0;
// Find tariff with specified name+value+period...
$tariffid = $this->DB->GetOne('SELECT id, value FROM tariffs
WHERE name = ? AND period = ?
LIMIT 1', array($tariff['name'], $tariff['period']));
// ... if not found clone tariff
/* if (!$tariffid) {
$this->DB->Execute('INSERT INTO tariffs (name, value, period,
taxid, type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit)
SELECT ?, ?, ?, taxid, type, upceil, downceil, uprate, downrate,
prodid, plimit, climit, dlimit, upceil_n, downceil_n, uprate_n, downrate_n,
domain_limit, alias_limit, sh_limit, www_limit, ftp_limit, mail_limit, sql_limit,
quota_sh_limit, quota_www_limit, quota_ftp_limit, quota_mail_limit, quota_sql_limit
FROM tariffs WHERE id = ?', array(
$tariff['name'],
str_replace(',', '.', $value),
$tariff['period'],
$tariff['id'],
));
$tariffid = $this->DB->GetLastInsertId('tariffs');
}
*/
$basic_value = $tariff['value'];
$promo_value = str_replace(',', '.', $value);
$promo_discount = $basic_value - $promo_value;
}
// Create assignment
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array($tariffid, $data['customerid'], $period, $at, !empty($data['invoice']) ? $data['invoice'] : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $idx ? $datefrom : 0, $idx ? $dateto : 0, 0, $promo_discount, $lid));
$result[] = $this->DB->GetLastInsertID('assignments');
if ($idx) {
$datefrom = $dateto + 1;
}
}
// add "after promotion" tariff(s)
if ($tariff['continuation'] || !$data_schema[0]) {
if ($tariff['ctariffid'] && $data_schema[0] != 0) {
$tariffs[] = $tariff['ctariffid'];
} else {
$tariffs[] = $tariff['id'];
}
// Create assignments
foreach ($tariffs as $t) {
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array($t, $data['customerid'], $data['period'], $this->CalcAt($data['period'], $datefrom), !empty($data['invoice']) ? $data['invoice'] : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $datefrom, 0, 0, 0, 0));
$result[] = $this->DB->GetLastInsertID('assignments');
if (SYSLOG && !empty($result) && !empty($data['customerid'])) {
if (!empty($data['liabilityid'])) {
$nazwa = $this->DB->GetOne('SELECT name FROM liabilites WHERE id=? LIMIT 1;', array($data['liabilityid']));
} else {
$nazwa = $this->DB->GetOne('SELECT name FROM tariffs WHERE id=? LIMIT 1;', array($data['tariffid']));
}
addlogs('dodano zobowiązanie: ' . $nazwa . ', klient: ' . $this->getcustomername($data['customerid']), 'e=add;m=fin;c=' . $data['customerid']);
}
}
}
} else {
if (!empty($data['value'])) {
$this->DB->Execute('INSERT INTO liabilities (name, value, taxid, prodid)
VALUES (?, ?, ?, ?)', array($data['name'], str_replace(',', '.', $data['value']), intval($data['taxid']), $data['prodid']));
$lid = $this->DB->GetLastInsertID('liabilities');
}
$this->DB->Execute('INSERT INTO assignments (tariffid, customerid, period, at, invoice,
settlement, numberplanid, paytype, datefrom, dateto, pdiscount, vdiscount, liabilityid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', array(intval($data['tariffid']), $data['customerid'], $data['period'], $data['at'], !empty($data['invoice']) ? $data['invoice'] : 0, !empty($data['settlement']) ? 1 : 0, !empty($data['numberplanid']) ? $data['numberplanid'] : NULL, !empty($data['paytype']) ? $data['paytype'] : NULL, $data['datefrom'], $data['dateto'], str_replace(',', '.', $data['pdiscount']), str_replace(',', '.', $data['vdiscount']), isset($lid) ? $lid : 0));
$result[] = $this->DB->GetLastInsertID('assignments');
if (SYSLOG && !empty($result) && !empty($data['customerid'])) {
if ($data['datefrom'] != 0) {
$data_od = ' okres od: ' . date('Y/m/d', $data['datefrom']);
} else {
$data_od = 'od: "nie ustawiono" ';
}
if ($data['dateto'] != 0) {
$data_do = 'do: ' . date('Y/m/d', $data['dateto']);
} else {
$data_do = 'do: "nie ustawiono" ';
}
if ($data['period'] == 5) {
$nalicz = ', nalicza rocznie, ';
} elseif ($data['period'] == 7) {
$nalicz = ', nalicza półrocznie, ';
} elseif ($data['period'] == 4) {
$nalicz = ', nalicza kwartalnie, ';
} elseif ($data['period'] == 3) {
$nalicz = ', nalicza miesięcznie, ';
} elseif ($data['period'] == 0) {
$nalicz = ', nalicza jednorazowo, ';
}
if ($data['pdiscount'] != 0 || $data['vdiscount'] != 0) {
$rabat = ' z rabatem';
} else {
$rabat = ' bez rabatu';
}
if ($data['invoice'] == 1) {
$opcje = ', z fakturą VAT';
} elseif ($data['invoice'] == 6) {
$opcje = ', z fakturą proforma ';
} else {
$opcje = ', tylko naliczanie ';
}
if ($data['settlement'] != 0) {
$opcje_ = ' z wyrównaniem';
}
if ($data['tariffid'] == 0) {
$zap = $this->DB->GetRow('SELECT name, value FROM liabilities WHERE id=? LIMIT 1;', array($lid));
$nazwa = ' beztaryfowe <b> ' . $zap['name'] . '</b> na kwotę: <b>' . moneyf($zap['value']) . '</b> ';
} else {
$zap = $this->DB->GetRow('SELECT name, value FROM tariffs WHERE id=? LIMIT 1;', array($data['tariffid']));
$nazwa = ' taryfa <b>' . $zap['name'] . '</b> na kwotę: <b>' . moneyf($zap['value']) . '</b> ';
}
addlogs('dodano zobowiązanie: ' . $nazwa . ' ' . $rabat . ' ' . $nalicz . ' ' . $data_od . ' ' . $data_do . ' ' . $opcje . ' ' . $opcje_ . ', klient: ' . $this->getcustomername($data['customerid']), 'e=add;m=fin;c=' . $data['customerid']);
}
}
if (!empty($result) && count($result = array_filter($result))) {
if (!empty($data['nodes'])) {
// Use multi-value INSERT query
$values = array();
foreach ((array) $data['nodes'] as $nodeid) {
foreach ($result as $aid) {
$values[] = sprintf('(%d, %d)', $nodeid, $aid);
}
}
$this->DB->Execute('INSERT INTO nodeassignments (nodeid, assignmentid)
VALUES ' . implode(', ', $values));
}
}
return $result;
}
$note['cdate'] = time();
$note['paytime'] = $CONFIG['notes']['paytime'];
if (isset($_GET['customerid']) && $_GET['customerid'] != '' && $LMS->CustomerExists($_GET['customerid'])) {
$customer = $LMS->GetCustomer($_GET['customerid'], true);
$note['numberplanid'] = $DB->GetOne('SELECT n.id FROM numberplans n
JOIN numberplanassignments a ON (n.id = a.planid)
WHERE n.doctype = ? AND n.isdefault = 1 AND a.divisionid = ?', array(DOC_DNOTE, $customer['divisionid']));
}
if (empty($note['numberplanid'])) {
$note['numberplanid'] = $DB->GetOne('SELECT id FROM numberplans
WHERE doctype = ? AND isdefault = 1', array(DOC_DNOTE));
}
break;
case 'additem':
$itemdata = r_trim($_POST);
$itemdata['value'] = f_round($itemdata['value']);
$itemdata['description'] = $itemdata['description'];
if ($itemdata['value'] > 0 && $itemdata['description'] != '') {
$itemdata['posuid'] = (string) getmicrotime();
$contents[] = $itemdata;
}
break;
case 'deletepos':
if (sizeof($contents)) {
foreach ($contents as $idx => $row) {
if ($row['posuid'] == $_GET['posuid']) {
unset($contents[$idx]);
}
}
}
break;
/**
* 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));
}
}
