function addReminderFees($intInvoiceId)
{
$strAlert = '';
$strQuery = 'SELECT inv.due_date, inv.state_id, inv.print_date ' . 'FROM {prefix}invoice inv ' . 'WHERE inv.id = ?';
$intRes = mysqli_param_query($strQuery, [$intInvoiceId]);
if ($row = mysqli_fetch_assoc($intRes)) {
$intStateId = $row['state_id'];
$strDueDate = dateConvDBDate2Date($row['due_date']);
$strPrintDate = $row['print_date'];
} else {
return $GLOBALS['locRecordNotFound'];
}
$intDaysOverdue = floor((time() - strtotime($strDueDate)) / 60 / 60 / 24);
if ($intDaysOverdue <= 0) {
$strAlert = addslashes($GLOBALS['locInvoiceNotOverdue']);
} elseif ($intStateId == 3 || $intStateId == 4) {
$strAlert = addslashes($GLOBALS['locWrongStateForReminderFee']);
} else {
// Update invoice state
if ($intStateId == 1 || $intStateId == 2) {
$intStateId = 5;
} elseif ($intStateId == 5) {
$intStateId = 6;
}
mysqli_param_query('UPDATE {prefix}invoice SET state_id=? where id=?', [$intStateId, $intInvoiceId]);
// Add reminder fee
if (getSetting('invoice_notification_fee')) {
// Remove old fee from same day
mysqli_param_query('UPDATE {prefix}invoice_row SET deleted=1 WHERE invoice_id=? AND reminder_row=2 AND row_date = ?', [$intInvoiceId, date('Ymd')]);
$strQuery = 'INSERT INTO {prefix}invoice_row (invoice_id, description, pcs, price, row_date, vat, vat_included, order_no, reminder_row) ' . 'VALUES (?, ?, 1, ?, ?, 0, 0, -2, 2)';
mysqli_param_query($strQuery, [$intInvoiceId, $GLOBALS['locReminderFeeDesc'], getSetting('invoice_notification_fee'), date('Ymd')]);
}
// Add penalty interest
$penaltyInterest = getSetting('invoice_penalty_interest');
if ($penaltyInterest) {
// Remove old penalty interest
mysqli_param_query('UPDATE {prefix}invoice_row SET deleted=1 WHERE invoice_id=? AND reminder_row=1', [$intInvoiceId]);
// Add new interest
$intTotSumVAT = 0;
$strQuery = 'SELECT ir.pcs, ir.price, ir.discount, ir.vat, ir.vat_included, ir.reminder_row ' . 'FROM {prefix}invoice_row ir ' . 'WHERE ir.deleted=0 AND ir.invoice_id=?';
$intRes = mysqli_param_query($strQuery, [$intInvoiceId]);
while ($row = mysqli_fetch_assoc($intRes)) {
if ($row['reminder_row']) {
continue;
}
list($rowSum, $rowVAT, $rowSumVAT) = calculateRowSum($row['price'], $row['pcs'], $row['vat'], $row['vat_included'], $row['discount']);
$intTotSumVAT += $rowSumVAT;
}
$intPenalty = $intTotSumVAT * $penaltyInterest / 100 * $intDaysOverdue / 360;
$strQuery = 'INSERT INTO {prefix}invoice_row (invoice_id, description, pcs, price, discount, row_date, vat, vat_included, order_no, reminder_row) ' . 'VALUES (?, ?, 1, ?, 0, ?, 0, 0, -1, 1)';
mysqli_param_query($strQuery, [$intInvoiceId, $GLOBALS['locPenaltyInterestDesc'], $intPenalty, date('Ymd')]);
}
}
return $strAlert;
}
private function printRow($format, $printFields, $row, $intRowSum, $intRowVAT, $intRowSumVAT)
{
if ($format == 'pdf' || $format == 'pdfl') {
$pdf = $this->pdf;
$pdf->SetFont('Helvetica', '', 8);
$pdf->setY($pdf->getY() + 1);
if (in_array('invoice_no', $printFields)) {
$pdf->Cell(18, 4, $row['invoice_no'], 0, 0, 'L');
}
if (in_array('invoice_date', $printFields)) {
$pdf->Cell(20, 4, dateConvDBDate2Date($row['invoice_date']), 0, 0, 'L');
}
if (in_array('due_date', $printFields)) {
$pdf->Cell(20, 4, dateConvDBDate2Date($row['due_date']), 0, 0, 'L');
}
if (in_array('payment_date', $printFields)) {
$pdf->Cell(20, 4, dateConvDBDate2Date($row['payment_date']), 0, 0, 'L');
}
if (in_array('company_name', $printFields)) {
$nameX = $pdf->getX();
$pdf->setX($nameX + 45);
}
if (in_array('status', $printFields)) {
$pdf->Cell(20, 4, isset($GLOBALS['loc' . $row['state']]) ? $GLOBALS['loc' . $row['state']] : $row['state'], 0, 0, 'L');
}
if (in_array('ref_number', $printFields)) {
$pdf->Cell(25, 4, formatRefNumber($row['ref_number']), 0, 0, 'L');
}
if (in_array('sums', $printFields)) {
$pdf->Cell(25, 4, miscRound2Decim($intRowSum), 0, 0, 'R');
$pdf->Cell(25, 4, miscRound2Decim($intRowVAT), 0, 0, 'R');
$pdf->Cell(25, 4, miscRound2Decim($intRowSumVAT), 0, 0, 'R');
}
// Print company name last, as it can span multiple lines
if (in_array('company_name', $printFields)) {
$pdf->setX($nameX);
$pdf->MultiCell(45, 4, $row['name'], 0, 'L');
}
return;
}
?>
<tr>
<?php
if (in_array('invoice_no', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars($row['invoice_no']);
?>
</td>
<?php
}
if (in_array('invoice_date', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars(dateConvDBDate2Date($row['invoice_date']));
?>
</td>
<?php
}
if (in_array('due_date', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars(dateConvDBDate2Date($row['due_date']));
?>
</td>
<?php
}
if (in_array('payment_date', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars(dateConvDBDate2Date($row['payment_date']));
?>
</td>
<?php
}
if (in_array('company_name', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars($row['name']);
?>
</td>
<?php
}
if (in_array('status', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars(isset($GLOBALS['loc' . $row['state']]) ? $GLOBALS['loc' . $row['state']] : $row['state']);
?>
</td>
<?php
}
if (in_array('ref_number', $printFields)) {
?>
<td class="input">
<?php
echo htmlspecialchars(formatRefNumber($row['ref_number']));
?>
</td>
<?php
}
if (in_array('sums', $printFields)) {
?>
<td class="input" style="text-align: right">
<?php
echo miscRound2Decim($intRowSum);
?>
</td>
<td class="input" style="text-align: right">
<?php
echo miscRound2Decim($intRowVAT);
?>
</td>
<td class="input" style="text-align: right">
<?php
echo miscRound2Decim($intRowSumVAT);
?>
</td>
<?php
}
?>
</tr>
<?php
}
protected function process_import_row($table, $row, $dupMode, $dupCheckColumns, $mode, &$addedRecordId)
{
if (!isset($row['date']) || !isset($row['amount']) || !isset($row['refnr'])) {
return $GLOBALS['locImportStatementFieldMissing'];
}
$refnr = str_replace(' ', '', $row['refnr']);
$refnr = ltrim($refnr, '0');
$date = date('Ymd', DateTime::createFromFormat(getRequest('date_format', 'd.m.Y'), $row['date'])->getTimestamp());
$amount = trim($row['amount']);
if (substr($amount, 0, 1) == '-') {
return;
}
if (substr($amount, 0, 1) == '+') {
$amount = substr($amount, 1);
}
$sep = getRequest('decimal_separator', ',');
if ($sep == ' ' || $sep == ',') {
$amount = str_replace('.', '', $amount);
$amount = str_replace($sep, '.', $amount);
} elseif ($sep == '.') {
$amount = str_replace(',', '', $amount);
}
$amount = floatval($amount);
if ($row['refnr'] === '') {
return $GLOBALS['locImportStatementFieldMissing'];
}
$sql = 'SELECT i.* FROM {prefix}invoice i' . ' WHERE i.Deleted=0 AND REPLACE(i.ref_number, " ", "") = ?';
$params = [$refnr];
$baseId = getRequest('base_id', '');
if ($baseId) {
$sql .= ' AND i.base_id = ?';
$params[] = $baseId;
}
$intRes = mysqli_param_query($sql, $params);
$count = mysqli_num_rows($intRes);
if ($count == 0) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementInvoiceNotFound']);
}
if ($count > 1) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementMultipleInvoicesFound']);
}
$row = mysqli_fetch_assoc($intRes);
if ($row['state_id'] == 3) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementInvoiceAlreadyPaid']);
}
$res2 = mysqli_param_query('SELECT ir.price, ir.pcs, ir.vat, ir.vat_included, ir.discount, ir.partial_payment from {prefix}invoice_row ir where ir.deleted = 0 AND ir.invoice_id = ?', [$row['id']]);
$rowTotal = 0;
$partialPayments = 0;
while ($invoiceRow = mysqli_fetch_assoc($res2)) {
if ($invoiceRow['partial_payment']) {
$partialPayments += $invoiceRow['price'];
}
list($rowSum, $rowVAT, $rowSumVAT) = calculateRowSum($invoiceRow['price'], $invoiceRow['pcs'], $invoiceRow['vat'], $invoiceRow['vat_included'], $invoiceRow['discount']);
$rowTotal += $rowSumVAT;
}
$totalToPay = $rowTotal + $partialPayments;
if (miscRound2Decim($totalToPay) != miscRound2Decim($amount)) {
if (getRequest('partial_payments', false) && miscRound2Decim($totalToPay) > miscRound2Decim($amount)) {
if ($mode == 'import') {
$sql = <<<EOT
INSERT INTO {prefix}invoice_row
(invoice_id, description, pcs, price, row_date, order_no, partial_payment)
VALUES (?, ?, 0, ?, ?, 100000, 1)
EOT;
mysqli_param_query($sql, [$row['id'], $GLOBALS['locPartialPayment'], -$amount, $date]);
}
$msg = str_replace('{statementAmount}', miscRound2Decim($amount), $GLOBALS['locImportStatementPartialPayment']);
$msg = str_replace('{invoiceAmount}', miscRound2Decim($totalToPay), $msg);
$msg = str_replace('{id}', $row['id'], $msg);
$msg = str_replace('{date}', dateConvDBDate2Date($date), $msg);
$msg = str_replace('{refnr}', $refnr, $msg);
return $msg;
} else {
$msg = str_replace('{statementAmount}', miscRound2Decim($amount), $GLOBALS['locImportStatementAmountMismatch']);
$msg = str_replace('{invoiceAmount}', miscRound2Decim($totalToPay), $msg);
$msg = str_replace('{refnr}', $refnr, $msg);
return $msg;
}
}
$archive = $row['interval_type'] == 0 && getRequest('archive', '');
if ($mode == 'import') {
$sql = 'UPDATE {prefix}invoice SET state_id=3, payment_date=?';
if ($archive) {
$sql .= ', archived=1';
}
$sql .= ' WHERE id = ?';
mysqli_param_query($sql, [$date, $row['id']]);
}
$msg = str_replace('{amount}', miscRound2Decim($amount), $archive ? $GLOBALS['locImportStatementInvoiceMarkedAsPaidAndArchived'] : $GLOBALS['locImportStatementInvoiceMarkedAsPaid']);
$msg = str_replace('{id}', $row['id'], $msg);
$msg = str_replace('{date}', dateConvDBDate2Date($date), $msg);
$msg = str_replace('{refnr}', $refnr, $msg);
return $msg;
}
function fetchRecord($table, $primaryKey, &$formElements, &$values)
{
$result = TRUE;
$strQuery = "SELECT * FROM {$table} WHERE id=?";
$intRes = mysqli_param_query($strQuery, [$primaryKey]);
$row = mysqli_fetch_assoc($intRes);
if (!$row) {
return 'notfound';
}
if ($row['deleted']) {
$result = 'deleted';
}
foreach ($formElements as $elem) {
$type = $elem['type'];
$name = $elem['name'];
if (!$type || $type == 'LABEL' || $type == 'FILLER') {
continue;
}
switch ($type) {
case 'IFORM':
case 'RESULT':
$values[$name] = $primaryKey;
break;
case 'BUTTON':
case 'JSBUTTON':
case 'IMAGE':
if (strstr($elem['listquery'], '=_ID_')) {
$values[$name] = $primaryKey;
} else {
$tmpListQuery = $elem['listquery'];
$strReplName = substr($tmpListQuery, strpos($tmpListQuery, '_'));
$strReplName = strtolower(substr($strReplName, 1, strrpos($strReplName, '_') - 1));
$values[$name] = isset($values[$strReplName]) ? $values[$strReplName] : '';
$elem['listquery'] = str_replace(strtoupper($strReplName), 'ID', $elem['listquery']);
}
break;
case 'INTDATE':
$values[$name] = dateConvDBDate2Date($row[$name]);
break;
case 'INT':
if (isset($elem['decimals'])) {
$values[$name] = miscRound2Decim($row[$name], $elem['decimals']);
} else {
$values[$name] = $row[$name];
}
break;
default:
$values[$name] = $row[$name];
}
}
return $result;
}
private function printHeader($format, $startDate, $endDate)
{
if ($format == 'pdf') {
ob_end_clean();
$pdf = new PDF('P', 'mm', 'A4', _CHARSET_ == 'UTF-8', _CHARSET_, false);
$pdf->setTopMargin(20);
$pdf->headerRight = $GLOBALS['locReportPage'];
$pdf->printHeaderOnFirstPage = true;
$pdf->AddPage();
$pdf->SetAutoPageBreak(TRUE, 15);
$pdf->setY(10);
$pdf->SetFont('Helvetica', 'B', 12);
$pdf->Cell(100, 15, $GLOBALS['locProductReport'], 0, 1, 'L');
if ($startDate || $endDate) {
$pdf->SetFont('Helvetica', '', 8);
$pdf->Cell(25, 15, $GLOBALS['locDateInterval'], 0, 0, 'L');
$pdf->Cell(50, 15, dateConvDBDate2Date($startDate) . ' - ' . dateConvDBDate2Date($endDate), 0, 1, 'L');
}
$pdf->SetFont('Helvetica', 'B', 8);
$pdf->Cell(15, 4, $GLOBALS['locCode'], 0, 0, 'L');
$pdf->Cell(40, 4, $GLOBALS['locProduct'], 0, 0, 'L');
$pdf->Cell(25, 4, $GLOBALS['locPCS'], 0, 0, 'R');
$pdf->Cell(25, 4, $GLOBALS['locUnit'], 0, 0, 'R');
$pdf->Cell(25, 4, $GLOBALS['locVATLess'], 0, 0, 'R');
$pdf->Cell(15, 4, $GLOBALS['locVATPercent'], 0, 0, 'R');
$pdf->Cell(25, 4, $GLOBALS['locVATPart'], 0, 0, 'R');
$pdf->Cell(25, 4, $GLOBALS['locWithVAT'], 0, 1, 'R');
$this->pdf = $pdf;
return;
}
?>
<div class="report">
<table>
<tr>
<th class="label">
<?php
echo $GLOBALS['locCode'];
?>
</th>
<th class="label">
<?php
echo $GLOBALS['locProduct'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locPCS'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locUnit'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locVATLess'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locVATPercent'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locVATPart'];
?>
</th>
<th class="label" style="text-align: right">
<?php
echo $GLOBALS['locWithVAT'];
?>
</th>
</tr>
<?php
}
function createJSONSelectList($strList, $startRow, $rowCount, $filter, $sort, $id = null)
{
global $dblink;
require "list_switch.php";
if (!sesAccessLevel($levelsAllowed) && !sesAdminAccess()) {
?>
<div class="form_container ui-widget-content">
<?php
echo $GLOBALS['locNoAccess'] . "\n";
?>
</div>
<?php
return;
}
if ($sort) {
if (!preg_match('/^[\\w_,]+$/', $sort)) {
header('HTTP/1.1 400 Bad Request');
die('Invalid sort type');
}
$sortValid = 0;
$sortFields = explode(',', $sort);
foreach ($sortFields as $sortField) {
foreach ($astrShowFields as $field) {
if ($sortField === $field['name']) {
++$sortValid;
break;
}
}
}
if ($sortValid != count($sortFields)) {
header('HTTP/1.1 400 Bad Request');
die('Invalid sort type');
}
} else {
foreach ($astrShowFields as $field) {
if ($field['name'] == 'order_no') {
$sort = 'order_no';
}
}
}
$arrQueryParams = array();
$strWhereClause = '';
if (!getSetting('show_deleted_records') && empty($id)) {
$strWhereClause = " WHERE {$strDeletedField}=0";
}
if ($strGroupBy) {
$strGroupBy = " GROUP BY {$strGroupBy}";
}
// Add Filter
if ($filter) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ') . createWhereClause($astrSearchFields, $filter, $arrQueryParams, !getSetting('dynamic_select_search_in_middle'));
}
// Filter out inactive companies
if ($strList == 'company' || $strList == 'companies') {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ') . 'inactive=0';
}
if ($id) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ') . 'id=' . mysqli_real_escape_string($dblink, $id);
}
// Build the final select clause
$strSelectClause = "{$strPrimaryKey}, {$strDeletedField}";
foreach ($astrShowFields as $field) {
$strSelectClause .= ', ' . (isset($field['sql']) ? $field['sql'] : $field['name']);
}
$fullQuery = "SELECT {$strSelectClause} FROM {$strTable} {$strWhereClause}{$strGroupBy}";
if ($sort) {
$fullQuery .= " ORDER BY {$sort}";
}
if ($startRow >= 0 && $rowCount >= 0) {
$fullQuery .= " LIMIT {$startRow}, " . ($rowCount + 1);
}
$res = mysqli_param_query($fullQuery, $arrQueryParams);
$astrListValues = array();
$i = -1;
$moreAvailable = false;
while ($row = mysqli_fetch_prefixed_assoc($res)) {
++$i;
if ($startRow >= 0 && $rowCount >= 0 && $i >= $rowCount) {
$moreAvailable = true;
break;
}
$astrPrimaryKeys[$i] = $row[$strPrimaryKey];
$aboolDeleted[$i] = $row[$strDeletedField];
foreach ($astrShowFields as $field) {
$name = $field['name'];
if ($field['type'] == 'TEXT' || $field['type'] == 'INT') {
$value = $row[$name];
if (isset($field['mappings']) && isset($field['mappings'][$value])) {
$value = $field['mappings'][$value];
}
$astrListValues[$i][$name] = $value;
} elseif ($field['type'] == 'CURRENCY') {
$value = $row[$name];
$value = miscRound2Decim($value, isset($field['decimals']) ? $field['decimals'] : 2);
$astrListValues[$i][$name] = $value;
} elseif ($field['type'] == 'INTDATE') {
$astrListValues[$i][$name] = dateConvDBDate2Date($row[$name]);
}
}
}
$records = array();
for ($i = 0; $i < count($astrListValues); $i++) {
$row = $astrListValues[$i];
$resultValues = array();
foreach ($astrShowFields as $field) {
if (!isset($field['select']) || !$field['select']) {
continue;
}
$name = $field['name'];
if (isset($field['translate']) && $field['translate'] && isset($GLOBALS["loc{$row[$name]}"])) {
$value = $GLOBALS["loc{$row[$name]}"];
} else {
$value = htmlspecialchars($row[$name]);
}
$resultValues[$name] = $value;
}
$records[] = array('id' => $astrPrimaryKeys[$i], 'text' => implode(' ', $resultValues));
}
$results = array('moreAvailable' => $moreAvailable, 'records' => $records, 'filter' => $filter);
return json_encode($results);
}
case 'add_reminder_fees':
require 'add_reminder_fees.php';
$invoiceId = getRequest('id', 0);
$errors = addReminderFees($invoiceId);
if ($errors) {
$ret = ['status' => 'error', 'errors' => $errors];
} else {
$ret = ['status' => 'ok'];
}
echo json_encode($ret);
break;
case 'get_invoice_defaults':
$baseId = getRequest('base_id', 0);
$companyId = getRequest('company_id', 0);
$invoiceId = getRequest('id', 0);
$invoiceDate = getRequest('invoice_date', dateConvDBDate2Date(date('Y') . '0101'));
$intervalType = getRequest('interval_type', 0);
$invNr = getRequest('invoice_no', 0);
$perYear = getSetting('invoice_numbering_per_year');
// If the invoice already has an invoice number, verify that it's not in use in another invoice
if ($invNr) {
$query = 'SELECT ID FROM {prefix}invoice where deleted=0 AND id!=? AND invoice_no=?';
$params = [$invoiceId, $invNr];
if (getSetting('invoice_numbering_per_base') && $baseId) {
$query .= ' AND base_id=?';
$params[] = $baseId;
}
if ($perYear) {
$query .= ' AND invoice_date >= ' . dateConvDate2DBDate($invoiceDate);
}
$res = mysqli_param_query($query, $params);
if (getSetting('invoice_add_number') || getSetting('invoice_add_reference_number')) {
$invoiceNumberUpdatePrefix = "\$.getJSON('json.php?func=get_invoice_defaults', {id: \$('#record_id').val(), invoice_no: \$('#invoice_no').val(), invoice_date: \$('#invoice_date').val(), base_id: \$('#base_id').val(), company_id: \$('#company_id').val(), interval_type: \$('#interval_type').val()}, function(json) { ";
if (getSetting('invoice_add_number')) {
$invoiceNumberUpdatePrefix .= "var invoice_no = document.getElementById('invoice_no'); if (invoice_no.value == '' || invoice_no.value < 100) invoice_no.value = json.invoice_no; ";
}
if (getSetting('invoice_add_reference_number')) {
$invoiceNumberUpdatePrefix .= "var ref_number = document.getElementById('ref_number'); if (ref_number.value == '' || ref_number.value == 0) ref_number.value = json.ref_no; ";
}
$invoiceNumberUpdatePrefix .= "\$('.save_button').addClass('ui-state-highlight'); ";
$invoiceNumberUpdateSuffix = ' });';
}
if (!getSetting('invoice_add_number')) {
$invoiceNumberUpdatePrefix .= "invoice_no = document.getElementById('invoice_no'); if (invoice_no.value == '' || invoice_no.value == 0) { if (!confirm('" . $GLOBALS['locInvoiceNumberNotDefined'] . "')) return false; }";
}
}
$today = dateConvDBDate2Date(date('Ymd'));
$markPaidToday = <<<EOS
\$('#state_id').val(3); if (!\$(this).is('#payment_date')) { \$('#payment_date').val('{$today}'); }
EOS;
if (getSetting('invoice_auto_archive')) {
$markPaidToday .= <<<EOS
\$('#archived').prop('checked', true);
EOS;
}
$markPaidToday .= <<<EOS
\$('.save_button').addClass('ui-state-highlight'); return false;
EOS;
$markPaidTodayButton = '<a class="formbuttonlink" href="#" onclick="' . $markPaidToday . '">' . $GLOBALS['locMarkAsPaidToday'] . '</a>';
if (getSetting('invoice_mark_paid_when_payment_date_set')) {
$markPaidTodayEvent = <<<EOF
if (\$(this).val()) { {$markPaidToday} }
protected function _formatDate($date)
{
return dateConvDBDate2Date($date, $GLOBALS['locPDFDateFormat']);
}
protected function process_import_row($table, $row, $dupMode, $dupCheckColumns, $mode, &$addedRecordId)
{
if (!isset($row['date']) || !isset($row['amount']) || !isset($row['refnr'])) {
return $GLOBALS['locImportStatementFieldMissing'];
}
$refnr = str_replace(' ', '', $row['refnr']);
$refnr = ltrim($refnr, '0');
$date = date('Ymd', DateTime::createFromFormat(getRequest('date_format', 'd.m.Y'), $row['date'])->getTimestamp());
$amount = trim($row['amount']);
if (substr($amount, 0, 1) == '-') {
return;
}
if (substr($amount, 0, 1) == '+') {
$amount = substr($amount, 1);
}
$sep = getRequest('decimal_separator', ',');
if ($sep == ' ' || $sep == ',') {
$amount = str_replace('.', '', $amount);
$amount = str_replace($sep, '.', $amount);
} elseif ($sep == '.') {
$amount = str_replace(',', '', $amount);
}
$amount = floatval($amount);
if ($row['refnr'] === '') {
return $GLOBALS['locImportStatementFieldMissing'];
}
$intRes = mysqli_param_query('SELECT i.* FROM {prefix}invoice i' . ' WHERE i.Deleted=0 AND REPLACE(i.ref_number, " ", "") = ?', array($refnr));
$count = mysqli_num_rows($intRes);
if ($count == 0) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementInvoiceNotFound']);
}
if ($count > 1) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementMultipleInvoicesFound']);
}
$row = mysqli_fetch_assoc($intRes);
if ($row['state_id'] == 3) {
return str_replace('{refnr}', $refnr, $GLOBALS['locImportStatementInvoiceAlreadyPaid']);
}
$res2 = mysqli_param_query('SELECT ir.price, ir.pcs, ir.vat, ir.vat_included, ir.discount from {prefix}invoice_row ir where ir.deleted = 0 AND ir.invoice_id = ?', array($row['id']));
$rowTotal = 0;
while ($invoiceRow = mysqli_fetch_assoc($res2)) {
list($rowSum, $rowVAT, $rowSumVAT) = calculateRowSum($invoiceRow['price'], $invoiceRow['pcs'], $invoiceRow['vat'], $invoiceRow['vat_included'], $invoiceRow['discount']);
$rowTotal += $rowSumVAT;
}
if (miscRound2Decim($rowTotal) != miscRound2Decim($amount)) {
$msg = str_replace('{statementAmount}', miscRound2Decim($amount), $GLOBALS['locImportStatementAmountMismatch']);
$msg = str_replace('{invoiceAmount}', miscRound2Decim($rowTotal), $msg);
$msg = str_replace('{refnr}', $refnr, $msg);
return $msg;
}
if ($mode == 'import') {
$sql = 'UPDATE {prefix}invoice SET state_id=3, payment_date=?';
if (getSetting('invoice_auto_archive')) {
$sql .= ', archived=1';
}
$sql .= ' WHERE id = ?';
mysqli_param_query($sql, array($date, $row['id']));
}
$msg = str_replace('{amount}', miscRound2Decim($amount), $GLOBALS['locImportStatementInvoiceMarkedAsPaid']);
$msg = str_replace('{id}', $row['id'], $msg);
$msg = str_replace('{date}', dateConvDBDate2Date($date), $msg);
$msg = str_replace('{refnr}', $refnr, $msg);
return $msg;
}
