public function GetInvoiceContent($invoiceid)
{
global $PAYTYPES, $LMS;
if ($result = $this->db->GetRow('SELECT d.id, d.number, d.name, d.customerid,
d.userid, d.address, d.zip, d.city, d.countryid, cn.name AS country,
d.ten, d.ssn, d.cdate, d.sdate, d.paytime, d.paytype, d.numberplanid,
d.closed, d.reference, d.reason, d.divisionid,
(SELECT name FROM users WHERE id = d.userid) AS user, n.template,
d.div_name AS division_name, d.div_shortname AS division_shortname,
d.div_address AS division_address, d.div_zip AS division_zip,
d.div_city AS division_city, d.div_countryid AS division_countryid,
d.div_ten AS division_ten, d.div_regon AS division_regon, d.div_account AS account,
d.div_inv_header AS division_header, d.div_inv_footer AS division_footer,
d.div_inv_author AS division_author, d.div_inv_cplace AS division_cplace,
c.pin AS customerpin, c.divisionid AS current_divisionid,
c.street, c.building, c.apartment,
c.post_street, c.post_building, c.post_apartment,
c.post_name, c.post_address, c.post_zip, c.post_city, c.post_countryid
FROM documents d
JOIN customeraddressview c ON (c.id = d.customerid)
LEFT JOIN countries cn ON (cn.id = d.countryid)
LEFT JOIN numberplans n ON (d.numberplanid = n.id)
WHERE d.id = ? AND (d.type = ? OR d.type = ?)', array($invoiceid, DOC_INVOICE, DOC_CNOTE))) {
$result['bankaccounts'] = $this->db->GetCol('SELECT contact FROM customercontacts
WHERE customerid = ? AND (type & ?) = ?', array($result['customerid'], CONTACT_BANKACCOUNT | CONTACT_INVOICES | CONTACT_DISABLED, CONTACT_BANKACCOUNT | CONTACT_INVOICES));
if (empty($result['bankaccounts'])) {
$result['bankaccounts'] = array();
}
$result['pdiscount'] = 0;
$result['vdiscount'] = 0;
$result['totalbase'] = 0;
$result['totaltax'] = 0;
$result['total'] = 0;
if ($result['reference']) {
$result['invoice'] = $this->GetInvoiceContent($result['reference']);
}
if (!$result['division_header']) {
$location_manager = new LMSLocationManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['division_header'] = $result['division_name'] . "\n" . $result['division_address'] . "\n" . $result['division_zip'] . ' ' . $result['division_city'] . ($result['division_countryid'] && $result['countryid'] && $result['division_countryid'] != $result['countryid'] ? "\n" . trans($location_manager->GetCountryName($result['division_countryid'])) : '') . ($result['division_ten'] != '' ? "\n" . trans('TEN') . ' ' . $result['division_ten'] : '');
}
if ($result['content'] = $this->db->GetAll('SELECT invoicecontents.value AS value,
itemid, taxid, taxes.value AS taxvalue, taxes.label AS taxlabel,
prodid, content, count, invoicecontents.description AS description,
tariffid, itemid, pdiscount, vdiscount
FROM invoicecontents
LEFT JOIN taxes ON taxid = taxes.id
WHERE docid=?
ORDER BY itemid', array($invoiceid))) {
foreach ($result['content'] as $idx => $row) {
if (isset($result['invoice'])) {
$row['value'] += $result['invoice']['content'][$idx]['value'];
$row['count'] += $result['invoice']['content'][$idx]['count'];
}
$result['content'][$idx]['basevalue'] = round($row['value'] / (100 + $row['taxvalue']) * 100, 2);
$result['content'][$idx]['total'] = round($row['value'] * $row['count'], 2);
$result['content'][$idx]['totalbase'] = round($result['content'][$idx]['total'] / (100 + $row['taxvalue']) * 100, 2);
$result['content'][$idx]['totaltax'] = round($result['content'][$idx]['total'] - $result['content'][$idx]['totalbase'], 2);
$result['content'][$idx]['value'] = $row['value'];
$result['content'][$idx]['count'] = $row['count'];
if (isset($result['taxest'][$row['taxvalue']])) {
$result['taxest'][$row['taxvalue']]['base'] += $result['content'][$idx]['totalbase'];
$result['taxest'][$row['taxvalue']]['total'] += $result['content'][$idx]['total'];
$result['taxest'][$row['taxvalue']]['tax'] += $result['content'][$idx]['totaltax'];
} else {
$result['taxest'][$row['taxvalue']]['base'] = $result['content'][$idx]['totalbase'];
$result['taxest'][$row['taxvalue']]['total'] = $result['content'][$idx]['total'];
$result['taxest'][$row['taxvalue']]['tax'] = $result['content'][$idx]['totaltax'];
$result['taxest'][$row['taxvalue']]['taxlabel'] = $row['taxlabel'];
}
$result['totalbase'] += $result['content'][$idx]['totalbase'];
$result['totaltax'] += $result['content'][$idx]['totaltax'];
$result['total'] += $result['content'][$idx]['total'];
// for backward compatybility
$result['taxest'][$row['taxvalue']]['taxvalue'] = $row['taxvalue'];
$result['content'][$idx]['pkwiu'] = $row['prodid'];
$result['pdiscount'] += $row['pdiscount'];
$result['vdiscount'] += $row['vdiscount'];
}
}
$result['pdate'] = $result['cdate'] + $result['paytime'] * 86400;
$result['value'] = $result['total'] - (isset($result['invoice']) ? $result['invoice']['value'] : 0);
if ($result['value'] < 0) {
$result['value'] = abs($result['value']);
$result['rebate'] = true;
}
$result['valuep'] = round(($result['value'] - floor($result['value'])) * 100);
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$result['customerbalance'] = $customer_manager->GetCustomerBalance($result['customerid'], $result['cdate'] + 1);
// NOTE: don't waste CPU/mem when printing history is not set:
if (ConfigHelper::checkValue(ConfigHelper::getConfig('invoices.print_balance_history', false))) {
if (ConfigHelper::checkValue(ConfigHelper::getConfig('invoices.print_balance_history_save', false))) {
$result['customerbalancelist'] = $customer_manager->GetCustomerBalanceList($result['customerid'], $result['cdate']);
} else {
$result['customerbalancelist'] = $customer_manager->GetCustomerBalanceList($result['customerid']);
}
$result['customerbalancelistlimit'] = ConfigHelper::getConfig('invoices.print_balance_history_limit');
}
$result['paytypename'] = $PAYTYPES[$result['paytype']];
// for backward compat.
$result['totalg'] = round(($result['value'] - floor($result['value'])) * 100);
$result['year'] = date('Y', $result['cdate']);
$result['month'] = date('m', $result['cdate']);
$result['pesel'] = $result['ssn'];
$result['nip'] = $result['ten'];
if ($result['post_name'] || $result['post_address']) {
$result['serviceaddr'] = $result['post_name'];
if ($result['post_address']) {
$result['serviceaddr'] .= "\n" . $result['post_address'];
}
if ($result['post_zip'] && $result['post_city']) {
$result['serviceaddr'] .= "\n" . $result['post_zip'] . ' ' . $result['post_city'];
}
}
return $result;
} else {
return FALSE;
}
}
/**
* Commits cash imports located in database
*/
public function CashImportCommit()
{
global $LMS;
$imports = $this->db->GetAll('SELECT i.*, f.idate
FROM cashimport i
LEFT JOIN sourcefiles f ON (f.id = i.sourcefileid)
WHERE i.closed = 0 AND i.customerid <> 0');
if (!empty($imports)) {
$idate = ConfigHelper::checkConfig('finances.cashimport_use_idate');
$icheck = ConfigHelper::checkConfig('finances.cashimport_checkinvoices');
$finance_manager = new LMSFinanceManager($this->db, $this->auth, $this->cache, $this->syslog);
$customer_manager = new LMSCustomerManager($this->db, $this->auth, $this->cache, $this->syslog);
$cashimports = array();
foreach ($imports as $import) {
$this->db->BeginTrans();
$balance['time'] = $idate ? $import['idate'] : $import['date'];
$balance['type'] = 1;
$balance['value'] = $import['value'];
$balance['customerid'] = $import['customerid'];
$balance['comment'] = $import['description'];
$balance['importid'] = $import['id'];
$balance['sourceid'] = $import['sourceid'];
$balance['userid'] = 0;
if ($import['value'] > 0 && $icheck) {
if ($invoices = $this->db->GetAll('SELECT x.id, x.value FROM (
SELECT d.id,
(SELECT SUM(value*count) FROM invoicecontents WHERE docid = d.id) +
COALESCE((
SELECT SUM((a.value+b.value)*(a.count+b.count)) - SUM(b.value*b.count)
FROM documents dd
JOIN invoicecontents a ON (a.docid = dd.id)
JOIN invoicecontents b ON (dd.reference = b.docid AND a.itemid = b.itemid)
WHERE dd.reference = d.id
GROUP BY dd.reference), 0) AS value,
d.cdate
FROM documents d
WHERE d.customerid = ? AND d.type = ? AND d.closed = 0
GROUP BY d.id, d.cdate
UNION
SELECT d.id, dn.value, d.cdate
FROM documents d
JOIN debitnotecontents dn ON dn.docid = d.id
WHERE d.customerid = ?
) x ORDER BY x.cdate', array($balance['customerid'], DOC_INVOICE, $balance['customerid']))) {
foreach ($invoices as $inv) {
$sum += $inv['value'];
}
$bval = $customer_manager->GetCustomerBalance($balance['customerid']);
$value = f_round($bval + $import['value'] + $sum);
foreach ($invoices as $inv) {
$inv['value'] = f_round($inv['value']);
if ($inv['value'] > $value) {
break;
} else {
// close invoice and assigned credit notes
$this->db->Execute('UPDATE documents SET closed = 1
WHERE id = ? OR reference = ?', array($inv['id'], $inv['id']));
if ($this->syslog) {
foreach (array('id', 'reference') as $key) {
$docids = $this->db->GetCol('SELECT id FROM documents WHERE ' . $key . ' = ?', array($inv['id']));
if (!empty($docids)) {
foreach ($docids as $docid) {
$args = array(SYSLOG::RES_DOC, SYSLOG::RES_CUST, 'closed' => 1);
$this->syslog->AddMessage(SYSLOG::RES_DOC, SYSLOG::OPER_UPDATE, $args);
}
}
}
}
$value -= $inv['value'];
}
}
}
}
$this->db->Execute('UPDATE cashimport SET closed = 1 WHERE id = ?', array($import['id']));
if ($this->syslog) {
$args = array(SYSLOG::RES_CASHIMPORT => $import['id'], SYSLOG::RES_CASHSOURCE => $import['sourceid'], SYSLOG::RES_SOURCEFILE => $import['sourcefileid'], SYSLOG::RES_CUST => $import['customerid'], 'closed' => 1);
$this->syslog->AddMessage(SYSLOG::RES_CASHIMPORT, SYSLOG::OPER_UPDATE, $args);
}
$finance_manager->AddBalance($balance);
$this->db->CommitTrans();
if ($this->db->GetOne('SELECT closed FROM cashimport WHERE id = ?', array($import['id']))) {
$cashimports[] = $import;
}
}
$LMS->executeHook('cashimport_after_commit', array('cashimports' => $cashimports));
}
}
