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