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;
}
function getSetting($name)
{
// The cache only lives for a single request to speed up repeated requests for a setting
static $settingsCache = [];
if (isset($settingsCache[$name])) {
return $settingsCache[$name];
}
require 'settings_def.php';
if (isset($arrSettings[$name]) && isset($arrSettings[$name]['session']) && $arrSettings[$name]['session']) {
if (isset($_SESSION[$name])) {
return $_SESSION[$name];
}
} else {
$res = mysqli_param_query('SELECT value from {prefix}settings WHERE name=?', [$name]);
if ($row = mysqli_fetch_assoc($res)) {
$settingsCache[$name] = $row['value'];
return $settingsCache[$name];
}
}
$settingsCache[$name] = isset($arrSettings[$name]) && isset($arrSettings[$name]['default']) ? cond_utf8_decode($arrSettings[$name]['default']) : '';
return $settingsCache[$name];
}
protected function process_import_row($table, $row, $dupMode, $dupCheckColumns, $mode, &$addedRecordId)
{
global $dblink;
$result = '';
$recordId = null;
if ($dupMode != '' && count($dupCheckColumns) > 0) {
$query = "select id from {prefix}{$table} where Deleted=0";
$where = '';
$params = array();
foreach ($dupCheckColumns as $dupCol) {
$where .= " AND {$dupCol}=?";
$params[] = $row[$dupCol];
}
$res = mysqli_param_query($query . $where, $params);
if ($dupRow = mysqli_fetch_row($res)) {
$id = $dupRow[0];
$found_dup = true;
if ($dupMode == 'update') {
$result = "Update existing row id {$id} in table {$table}";
} else {
$result = "Not updating existing row id {$id} in table {$table}";
}
if ($mode == 'import' && $dupMode == 'update') {
// Update existing row
$query = "UPDATE {prefix}{$table} SET ";
$columns = '';
$params = array();
foreach ($row as $key => $value) {
if ($key == 'id') {
continue;
}
if ($columns) {
$columns .= ', ';
}
$columns .= "{$key}=?";
$params[] = $value;
}
$query .= "{$columns} WHERE id=?";
$params[] = $id;
mysqli_param_query($query, $params);
}
return $result;
}
}
// Add new row
$query = "INSERT INTO {prefix}{$table} ";
$columns = '';
$values = '';
$params = array();
foreach ($row as $key => $value) {
if ($key == 'id') {
continue;
}
if ($columns) {
$columns .= ', ';
}
if ($values) {
$values .= ', ';
}
$columns .= $key;
$values .= '?';
$params[] = $value;
}
$query .= "({$columns}) VALUES ({$values})";
if ($mode == 'import') {
mysqli_param_query($query, $params);
$addedRecordId = mysqli_insert_id($dblink);
} else {
$addedRecordId = 'x';
}
$result = "Add as new (ID {$addedRecordId}) into table {$table}";
return $result;
}
function get_max_invoice_number($invoiceId, $baseId, $perYear)
{
if ($baseId !== null) {
$sql = 'SELECT max(cast(invoice_no as unsigned integer)) FROM {prefix}invoice WHERE deleted=0 AND id!=? AND base_id=?';
$params = [$invoiceId, $baseId];
} else {
$sql = 'SELECT max(cast(invoice_no as unsigned integer)) FROM {prefix}invoice WHERE deleted=0 AND id!=?';
$params = [$invoiceId];
}
if ($perYear) {
$sql .= ' AND invoice_date >= ' . date('Y') . '0101';
}
$res = mysqli_param_query($sql, $params);
return mysqli_fetch_value($res);
}
$arrRefundingInvoice = ['allow_null' => true];
$intInvoiceId = getRequest('id', 0);
if ($intInvoiceId) {
$strQuery = 'SELECT refunded_invoice_id ' . 'FROM {prefix}invoice ' . 'WHERE id=?';
// ok to maintain links to deleted invoices too
$intRes = mysqli_param_query($strQuery, [$intInvoiceId]);
$strBaseLink = '?' . preg_replace('/&id=\\d*/', '', $_SERVER['QUERY_STRING']);
$strBaseLink = preg_replace('/&/', '&', $strBaseLink);
if ($intRes) {
$intRefundedInvoiceId = mysqli_fetch_value($intRes);
if ($intRefundedInvoiceId) {
$arrRefundedInvoice = ['name' => 'get', 'label' => $GLOBALS['locShowRefundedInvoice'], 'type' => 'BUTTON', 'style' => 'custom', 'listquery' => "{$strBaseLink}&id={$intRefundedInvoiceId}", 'position' => 2, 'allow_null' => true];
}
}
$strQuery = 'SELECT id ' . 'FROM {prefix}invoice ' . 'WHERE deleted=0 AND refunded_invoice_id=?';
$intRes = mysqli_param_query($strQuery, [$intInvoiceId]);
if ($intRes && ($row = mysqli_fetch_assoc($intRes))) {
$intRefundingInvoiceId = $row['id'];
if ($intRefundingInvoiceId) {
$arrRefundingInvoice = ['name' => 'get', 'label' => $GLOBALS['locShowRefundingInvoice'], 'type' => 'BUTTON', 'style' => 'custom', 'listquery' => "'{$strBaseLink}&id={$intRefundingInvoiceId}", 'position' => 2, 'allow_null' => true];
}
}
}
$invoicePrintChecks = '';
$invoiceNumberUpdatePrefix = '';
$invoiceNumberUpdateSuffix = '';
$companyOnChange = '';
$getInvoiceNr = '';
$updateDates = '';
$addCompanyCode = '';
if (sesWriteAccess()) {
protected function printReport()
{
$intProductId = getRequest('product', FALSE);
$format = getRequest('format', 'html');
$purchasePrice = getRequest('purchase_price', false);
$arrParams = [];
$strQuery = 'SELECT * ' . 'FROM {prefix}product ' . 'WHERE deleted=0';
if ($intProductId) {
$strQuery .= ' AND id = ? ';
$arrParams[] = $intProductId;
}
if ($purchasePrice) {
$strQuery .= ' AND NOT (purchase_price IS NULL or purchase_price = 0)';
}
$this->printHeader($format);
$stockValue = 0;
$intRes = mysqli_param_query($strQuery, $arrParams);
while ($row = mysqli_fetch_assoc($intRes)) {
$this->printRow($format, $row['product_code'], $row['product_name'], $row['purchase_price'], $row['unit_price'], $row['stock_balance']);
$stockValue += $row['stock_balance'] * $row['purchase_price'];
}
$this->printTotals($format, $stockValue);
$this->printFooter($format);
}
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;
}
private function printReport()
{
$intStateID = getRequest('stateid', FALSE);
$intBaseId = getRequest('base', FALSE);
$intCompanyId = getRequest('company', FALSE);
$intProductId = getRequest('product', FALSE);
$format = getRequest('format', 'html');
$dateRange = explode(' - ', getRequest('date', ''));
$startDate = $dateRange[0];
$endDate = isset($dateRange[1]) ? $dateRange[1] : $startDate;
if ($startDate) {
$startDate = dateConvDate2DBDate($startDate);
}
if ($endDate) {
$endDate = dateConvDate2DBDate($endDate);
}
$arrParams = [];
$strQuery = 'SELECT i.id ' . 'FROM {prefix}invoice i ' . 'WHERE i.deleted=0';
if ($startDate) {
$strQuery .= ' AND i.invoice_date >= ?';
$arrParams[] = $startDate;
}
if ($endDate) {
$strQuery .= ' AND i.invoice_date <= ?';
$arrParams[] = $endDate;
}
if ($intBaseId) {
$strQuery .= ' AND i.base_id = ?';
$arrParams[] = $intBaseId;
}
if ($intCompanyId) {
$strQuery .= ' AND i.company_id = ?';
$arrParams[] = $intCompanyId;
}
$strQuery2 = '';
$strQuery3 = 'SELECT id, name ' . 'FROM {prefix}invoice_state WHERE deleted=0 ' . 'ORDER BY order_no';
$intRes = mysqli_query_check($strQuery3);
while ($row = mysqli_fetch_assoc($intRes)) {
$intStateId = $row['id'];
$strStateName = $row['name'];
$strTemp = "stateid_{$intStateId}";
$tmpSelected = getRequest($strTemp, FALSE) ? TRUE : FALSE;
if ($tmpSelected) {
$strQuery2 .= ' i.state_id = ? OR ';
$arrParams[] = $intStateId;
}
}
if ($strQuery2) {
$strQuery2 = ' AND (' . substr($strQuery2, 0, -3) . ')';
}
$strQuery .= "{$strQuery2} ORDER BY invoice_no";
if ($intProductId) {
$strProductWhere = 'AND ir.product_id = ? ';
$arrParams[] = $intProductId;
} else {
$strProductWhere = '';
}
$strProductQuery = 'SELECT p.id, p.product_code, p.product_name, ir.description, ' . 'ir.vat, ir.pcs, t.name as unit, ir.price, ir.vat_included, ir.discount ' . 'FROM {prefix}invoice_row ir ' . 'LEFT OUTER JOIN {prefix}product p ON p.id = ir.product_id ' . 'LEFT OUTER JOIN {prefix}row_type t ON t.id = ir.type_id ' . "WHERE ir.deleted = 0 AND ir.partial_payment = 0 AND ir.invoice_id IN ({$strQuery}) {$strProductWhere}" . 'ORDER BY p.id, ir.description, t.name, ir.vat';
$this->printHeader($format, $startDate, $endDate);
$totalSum = 0;
$totalVAT = 0;
$totalSumVAT = 0;
$prevRow = false;
$productCount = 0;
$productSum = 0;
$productVAT = 0;
$productSumVAT = 0;
$intRes = mysqli_param_query($strProductQuery, $arrParams);
while ($row = mysqli_fetch_assoc($intRes)) {
if ($prevRow !== false && ($prevRow['id'] != $row['id'] || $prevRow['description'] != $row['description'] || $prevRow['unit'] != $row['unit'] || $prevRow['vat'] != $row['vat'])) {
$this->printRow($format, $prevRow['product_code'], $prevRow['product_name'], $prevRow['description'], $productCount, $prevRow['unit'], $productSum, $prevRow['vat'], $productVAT, $productSumVAT);
$productCount = 0;
$productSum = 0;
$productVAT = 0;
$productSumVAT = 0;
}
$prevRow = $row;
$productCount += $row['pcs'];
list($rowSum, $rowVAT, $rowSumVAT) = calculateRowSum($row['price'], $row['pcs'], $row['vat'], $row['vat_included'], $row['discount']);
$productSum += $rowSum;
$productVAT += $rowVAT;
$productSumVAT += $rowSumVAT;
$totalSum += $rowSum;
$totalVAT += $rowVAT;
$totalSumVAT += $rowSumVAT;
}
if ($prevRow !== false) {
$this->printRow($format, $prevRow['product_code'], $prevRow['product_name'], $prevRow['description'], $productCount, $prevRow['unit'], $productSum, $prevRow['vat'], $productVAT, $productSumVAT);
}
$this->printTotals($format, $totalSum, $totalVAT, $totalSumVAT);
$this->printFooter($format);
}
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);
}
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;
}
} elseif ($astrFormElements[$j]['type'] == 'INTDATE') {
$strSearchValue = dateConvDate2DBDate($astrValues[$name]);
}
if ($strSearchValue) {
$strWhereClause .= "{$strSearchOperator}{$strListTableAlias}{$name} {$strSearchMatch} {$strSearchValue}";
}
}
}
$strWhereClause = urlencode($strWhereClause);
if ($blnSearch) {
$strLink = "index.php?func={$strFunc}&where={$strWhereClause}";
$strOnLoad = "opener.location.href='{$strLink}'";
}
if ($blnSave && $strSearchName) {
$strQuery = 'INSERT INTO {prefix}quicksearch(user_id, name, func, whereclause) ' . 'VALUES (?, ?, ?, ?)';
$intRes = mysqli_param_query($strQuery, [$_SESSION['sesUSERID'], $strSearchName, $strFunc, $strWhereClause]);
} elseif ($blnSave && !$strSearchName) {
$strOnLoad = "alert('" . $GLOBALS['locErrorNoSearchName'] . "')";
}
}
echo htmlPageStart(_PAGE_TITLE_);
?>
<body onload="<?php
echo $strOnLoad;
?>
">
<script type="text/javascript">
<!--
$(function() {
$('input[class~="hasCalendar"]').datepicker();
});
if (!$imageInfo || !in_array($imageInfo['mime'], ['image/jpeg', 'image/png'])) {
$messages .= $GLOBALS['locErrFileTypeInvalid'] . "<br>\n";
} else {
$file = fopen($_FILES['logo']['tmp_name'], 'rb');
if ($file === FALSE) {
die('Could not process file upload - temp file missing');
}
$fsize = filesize($_FILES['logo']['tmp_name']);
$data = fread($file, $fsize);
fclose($file);
mysqli_param_query('UPDATE {prefix}base set logo_filename=?, logo_filesize=?, logo_filetype=?, logo_filedata=? WHERE id=?', [$_FILES['logo']['name'], $fsize, $imageInfo['mime'], $data, $baseId]);
$messages .= $GLOBALS['locBaseLogoSaved'] . ' (' . fileSizeToHumanReadable($fsize) . ")<br>\n";
}
}
} elseif ($func == 'view') {
$res = mysqli_param_query('SELECT logo_filename, logo_filesize, logo_filetype, logo_filedata FROM {prefix}base WHERE id=?', [$baseId]);
if ($row = mysqli_fetch_assoc($res)) {
if (isset($row['logo_filename']) && isset($row['logo_filesize']) && isset($row['logo_filetype']) && isset($row['logo_filedata'])) {
header('Content-length: ' . $row['logo_filesize']);
header('Content-type: ' . $row['logo_filetype']);
header('Content-Disposition: inline; filename=' . $row['logo_filename']);
echo $row['logo_filedata'];
}
}
exit;
}
$maxUploadSize = getMaxUploadSize();
$row = mysqli_fetch_array(mysqli_query_check('SELECT @@max_allowed_packet'));
$maxPacket = $row[0];
if ($maxPacket < $maxUploadSize) {
$maxFileSize = fileSizeToHumanReadable($maxPacket) . ' ' . $GLOBALS['locBaseLogoSizeDBLimited'];
private function printReport()
{
$intBaseId = getRequest('base', false);
$intCompanyId = getRequest('company', false);
$grouping = getRequest('grouping', '');
$format = getRequest('format', 'html');
$printFields = getRequest('fields', []);
$rowTypes = getRequest('row_types', 'all');
$dateRange = explode(' - ', getRequest('date', ''));
$startDate = $dateRange[0];
$endDate = isset($dateRange[1]) ? $dateRange[1] : $startDate;
if ($startDate) {
$startDate = dateConvDate2DBDate($startDate);
}
if ($endDate) {
$endDate = dateConvDate2DBDate($endDate);
}
$rowDateRange = explode(' - ', getRequest('row_date', ''));
$rowStartDate = $rowDateRange[0];
$rowEndDate = isset($rowDateRange[1]) ? $rowDateRange[1] : $rowStartDate;
if ($rowStartDate) {
$rowStartDate = dateConvDate2DBDate($rowStartDate);
}
if ($rowEndDate) {
$rowEndDate = dateConvDate2DBDate($rowEndDate);
}
$paymentDateRange = explode(' - ', getRequest('payment_date', ''));
$paymentStartDate = $paymentDateRange[0];
$paymentEndDate = isset($paymentDateRange[1]) ? $paymentDateRange[1] : '';
if ($paymentStartDate) {
$paymentStartDate = dateConvDate2DBDate($paymentStartDate);
}
if ($paymentEndDate) {
$paymentEndDate = dateConvDate2DBDate($paymentEndDate);
}
$arrParams = [];
$strQuery = 'SELECT i.id, i.invoice_no, i.invoice_date, i.due_date, i.payment_date, i.ref_number, i.ref_number, c.company_name AS name, c.billing_address, ist.name as state, ist.invoice_unpaid as unpaid' . ($grouping == 'vat' ? ', ir.vat' : '') . ' FROM {prefix}invoice i' . ($grouping == 'vat' ? ' INNER JOIN {prefix}invoice_row ir ON ir.invoice_id = i.id' : '') . ' LEFT OUTER JOIN {prefix}company c ON c.id = i.company_id' . ' LEFT OUTER JOIN {prefix}invoice_state ist ON i.state_id = ist.id' . ' WHERE i.deleted=0';
if ($startDate) {
$strQuery .= ' AND i.invoice_date >= ?';
$arrParams[] = $startDate;
}
if ($endDate) {
$strQuery .= ' AND i.invoice_date <= ?';
$arrParams[] = $endDate;
}
if ($paymentStartDate) {
$strQuery .= ' AND i.payment_date >= ?';
$arrParams[] = $paymentStartDate;
}
if ($paymentEndDate) {
$strQuery .= ' AND i.payment_date <= ?';
$arrParams[] = $paymentEndDate;
}
if ($intBaseId) {
$strQuery .= ' AND i.base_id = ?';
$arrParams[] = $intBaseId;
}
if ($intCompanyId) {
$strQuery .= ' AND i.company_id = ?';
$arrParams[] = $intCompanyId;
}
$strQuery2 = '';
$strQuery3 = 'SELECT id, name ' . 'FROM {prefix}invoice_state WHERE deleted=0 ORDER BY order_no';
$intRes = mysqli_query_check($strQuery3);
while ($row = mysqli_fetch_assoc($intRes)) {
$intStateId = $row['id'];
$strStateName = $row['name'];
$strTemp = "stateid_{$intStateId}";
$tmpSelected = getRequest($strTemp, false);
if ($tmpSelected) {
$strQuery2 .= 'i.state_id = ? OR ';
$arrParams[] = $intStateId;
}
}
if ($strQuery2) {
$strQuery2 = ' AND (' . substr($strQuery2, 0, -4) . ')';
}
$strQuery .= $strQuery2;
switch ($grouping) {
case 'state':
$strQuery .= ' ORDER BY state_id, invoice_date, invoice_no';
break;
case 'client':
$strQuery .= ' ORDER BY name, invoice_date, invoice_no';
break;
case 'vat':
$strQuery .= ' GROUP BY i.id, ir.vat ORDER BY vat, invoice_date, invoice_no';
break;
default:
$strQuery .= ' ORDER BY invoice_date, invoice_no';
}
$this->printHeader($format, $printFields, $startDate, $endDate);
$intTotSum = 0;
$intTotVAT = 0;
$intTotSumVAT = 0;
$intTotalToPay = 0;
$currentGroup = false;
$groupTotSum = 0;
$groupTotVAT = 0;
$groupTotSumVAT = 0;
$groupTotalToPay = 0;
$totalsPerVAT = [];
$intRes = mysqli_param_query($strQuery, $arrParams);
while ($row = mysqli_fetch_assoc($intRes)) {
switch ($grouping) {
case 'state':
$invoiceGroup = $row['state'];
break;
case 'month':
$invoiceGroup = substr($row['invoice_date'], 4, 2);
break;
case 'client':
$invoiceGroup = $row['name'];
break;
case 'vat':
$invoiceGroup = $row['vat'];
break;
default:
$invoiceGroup = false;
}
$rowParams = [$row['id']];
$strQuery = 'SELECT ir.description, ir.pcs, ir.price, ir.discount, ir.row_date, ir.vat, ir.vat_included, ir.partial_payment ' . 'FROM {prefix}invoice_row ir ' . 'WHERE ir.invoice_id=? AND ir.deleted=0';
if ($rowStartDate) {
$strQuery .= ' AND ir.row_date >= ?';
$rowParams[] = $rowStartDate;
}
if ($rowEndDate) {
$strQuery .= ' AND ir.row_date <= ?';
$rowParams[] = $rowEndDate;
}
if ($rowTypes != 'all') {
if ($rowTypes == 'normal') {
$strQuery .= ' AND ir.reminder_row = 0';
} else {
if ($rowTypes == 'reminder') {
$strQuery .= ' AND ir.reminder_row in (1, 2)';
}
}
}
if ($grouping == 'vat') {
if ($row['vat'] === null) {
$strQuery .= ' AND ir.vat IS NULL';
} else {
$strQuery .= ' AND ir.vat = ?';
$rowParams[] = $row['vat'];
}
}
$intRes2 = mysqli_param_query($strQuery, $rowParams);
$intRowSum = 0;
$intRowVAT = 0;
$intRowSumVAT = 0;
$rowPayments = 0;
$rows = false;
while ($row2 = mysqli_fetch_assoc($intRes2)) {
$rows = true;
if ($row2['partial_payment']) {
$rowPayments -= $row2['price'];
continue;
}
list($intSum, $intVAT, $intSumVAT) = calculateRowSum($row2['price'], $row2['pcs'], $row2['vat'], $row2['vat_included'], $row2['discount']);
$intRowSum += $intSum;
$intRowVAT += $intVAT;
$intRowSumVAT += $intSumVAT;
if (!isset($totalsPerVAT[$row2['vat']])) {
$totalsPerVAT[$row2['vat']] = ['sum' => $intSum, 'VAT' => $intVAT, 'sumVAT' => $intSumVAT];
} else {
$totalsPerVAT[$row2['vat']]['sum'] += $intSum;
$totalsPerVAT[$row2['vat']]['VAT'] += $intVAT;
$totalsPerVAT[$row2['vat']]['sumVAT'] += $intSumVAT;
}
}
if (!$rows) {
continue;
}
$intTotSum += $intRowSum;
$intTotVAT += $intRowVAT;
$intTotSumVAT += $intRowSumVAT;
if ($row['unpaid']) {
$intTotalToPay += $intRowSumVAT - $rowPayments;
} else {
$rowPayments = $intRowSumVAT;
}
if ($grouping && $currentGroup !== false && $currentGroup != $invoiceGroup) {
$this->printGroupSums($format, $printFields, $row, $groupTotSum, $groupTotVAT, $groupTotSumVAT, $groupTotalToPay, $grouping == 'vat' ? $GLOBALS['locVAT'] . ' ' . miscRound2Decim($currentGroup) : '');
$groupTotSum = 0;
$groupTotVAT = 0;
$groupTotSumVAT = 0;
$groupTotalToPay = 0;
}
$currentGroup = $invoiceGroup;
$groupTotSum += $intRowSum;
$groupTotVAT += $intRowVAT;
$groupTotSumVAT += $intRowSumVAT;
$groupTotalToPay += $intRowSumVAT - $rowPayments;
$this->printRow($format, $printFields, $row, $intRowSum, $intRowVAT, $intRowSumVAT, $intRowSumVAT - $rowPayments);
}
if ($grouping) {
$this->printGroupSums($format, $printFields, $row, $groupTotSum, $groupTotVAT, $groupTotSumVAT, $groupTotalToPay, $grouping == 'vat' ? $GLOBALS['locVAT'] . ' ' . miscRound2Decim($currentGroup) : '');
}
ksort($totalsPerVAT, SORT_NUMERIC);
$this->printTotals($format, $printFields, $intTotSum, $intTotVAT, $intTotSumVAT, $intTotalToPay, $totalsPerVAT);
$this->printFooter($format, $printFields);
}
function createSettingsList()
{
if (!sesAdminAccess()) {
?>
<div class="form_container ui-widget-content">
<?php
echo $GLOBALS['locNoAccess'] . "\n";
?>
</div>
<?php
return;
}
require 'settings_def.php';
$messages = '';
$blnSave = getPostRequest('saveact', FALSE) ? TRUE : FALSE;
if ($blnSave) {
foreach ($arrSettings as $name => $elem) {
$type = $elem['type'];
$label = $elem['label'];
if ($type == 'LABEL') {
continue;
}
$newValue = getPost($name, NULL);
if (!isset($newValue) || $newValue === '') {
if (!$elem['allow_null']) {
$messages .= $GLOBALS['locErrValueMissing'] . ": '{$label}'<br>\n";
continue;
} else {
$newValue = '';
}
}
if (in_array($type, array('CURRENCY', 'PERCENT'))) {
$newValue = str_replace($GLOBALS['locDecimalSeparator'], '.', $newValue);
}
if (in_array($type, array('CURRENCY', 'PERCENT', 'INT'))) {
$newValue = trim($newValue);
if (!is_numeric($newValue)) {
$messages .= $GLOBALS['locErrInvalidValue'] . " '{$label}'<br>\n";
continue;
}
}
if (isset($elem['session']) && $elem['session']) {
$_SESSION[$name] = $newValue;
}
mysqli_param_query('DELETE from {prefix}settings WHERE name=?', array($name));
mysqli_param_query('INSERT INTO {prefix}settings (name, value) VALUES (?, ?)', array($name, $newValue));
}
}
?>
<div class="form_container ui-widget-content">
<?php
if ($messages) {
?>
<div class="ui-widget ui-state-error"><?php
echo $messages;
?>
</div>
<?php
}
?>
<script type="text/javascript">
<!--
$(document).ready(function() {
$('input[class~="hasCalendar"]').datepicker();
$('iframe[class~="resizable"]').load(function() {
var iframe = $(this);
var body = iframe.contents().find("body");
var newHeight = body.outerHeight(true) + 10;
// Leave room for calendar popup
if (newHeight < 250)
newHeight = 250;
iframe.css("height", newHeight + 'px');
body.css("overflow", "hidden");
});
$('#admin_form').find('input[type="text"],input[type="checkbox"],select,textarea').change(function() { $('.save_button').addClass('unsaved'); });
});
-->
</script>
<?php
createSettingsListButtons();
?>
<div class="form">
<form method="post" name="admin_form" id="admin_form">
<?php
foreach ($arrSettings as $name => $elem) {
$elemType = $elem['type'];
if ($elemType == 'LABEL') {
?>
<div class="sublabel ui-widget-header ui-state-default"><?php
echo $elem['label'];
?>
</div>
<?php
continue;
}
$value = getPost($name, NULL);
if (!isset($value)) {
if (isset($elem['session']) && $elem['session']) {
$value = isset($_SESSION[$name]) ? $_SESSION[$name] : (isset($elem['default']) ? cond_utf8_decode($elem['default']) : '');
} else {
$res = mysqli_param_query('SELECT value from {prefix}settings WHERE name=?', array($name));
if ($row = mysqli_fetch_assoc($res)) {
$value = $row['value'];
} else {
$value = isset($elem['default']) ? cond_utf8_decode($elem['default']) : '';
}
}
if ($elemType == 'CURRENCY') {
$value = miscRound2Decim($value);
} elseif ($elemType == 'PERCENT') {
$value = miscRound2Decim($value, 1);
}
}
if ($elemType == 'CURRENCY' || $elemType == 'PERCENT') {
$elemType = 'INT';
}
if ($elemType == 'CHECK') {
?>
<div class="field" style="clear: both">
<?php
echo htmlFormElement($name, $elemType, $value, $elem['style'], '', "MODIFY", '', '', array(), isset($elem['elem_attributes']) ? $elem['elem_attributes'] : '', isset($elem['options']) ? $elem['options'] : null);
?>
<label for="<?php
echo $name;
?>
"><?php
echo $elem['label'];
?>
</label>
</div>
<?php
} else {
?>
<div class="label" style="clear: both"><label for="<?php
echo $name;
?>
"><?php
echo $elem['label'];
?>
</label></div>
<div class="field" style="clear: both">
<?php
echo htmlFormElement($name, $elemType, $value, $elem['style'], '', "MODIFY", '', '', array(), isset($elem['elem_attributes']) ? $elem['elem_attributes'] : '', isset($elem['options']) ? $elem['options'] : null);
?>
</div>
<?php
}
}
?>
<input type="hidden" name="saveact" value="0">
<?php
createSettingsListButtons();
?>
</form>
</div>
</div>
<?php
}
action="quick_search.php?func=<?php
echo $strFunc;
?>
" target="_self"
name="search_form">
<table style="width: 100%">
<tr>
<td class="sublabel" colspan="4">
<?php
echo $GLOBALS['locLabelQuickSearch'];
?>
<br> <br>
</td>
</tr>
<?php
$intRes = mysqli_param_query($strQuery, [$strFunc, $_SESSION['sesUSERID']]);
while ($row = mysqli_fetch_assoc($intRes)) {
$intID = $row['id'];
$strName = $row['name'];
$strFunc = $row['func'];
$strWhereClause = $row['whereclause'];
$strLink = "index.php?func={$strFunc}&where={$strWhereClause}";
$strOnClick = "opener.location.href='{$strLink}'";
?>
<tr class="search_row">
<td class="label"><a href="quick_search.php"
onClick="<?php
echo $strOnClick;
?>
; return false;"><?php
echo $strName;
protected function printOut()
{
$pdf = $this->pdf;
$senderData = $this->senderData;
$invoiceData = $this->invoiceData;
mb_internal_encoding('UTF-8');
$boundary = '-----' . md5(uniqid(time())) . '-----';
// Note: According to https://bugs.php.net/bug.php?id=15841 the PHP documentation is wrong,
// and CRLF should not be used except on Windows. PHP_EOL should work.
$headers = ['Date' => date('r'), 'From' => $this->emailFrom, 'Cc' => $this->emailCC, 'Bcc' => $this->emailBCC, 'Mime-Version' => '1.0', 'Content-Type' => "multipart/mixed; boundary=\"{$boundary}\"", 'X-Mailer' => 'MLInvoice'];
$filename = $this->outputFileName ? $this->outputFileName : getSetting('invoice_pdf_filename');
$filename = $this->getPrintOutFileName($filename);
$data = $pdf->Output($filename, 'E');
$messageBody = 'This is a multipart message in mime format.' . PHP_EOL . PHP_EOL;
$messageBody .= "--{$boundary}" . PHP_EOL;
$messageBody .= 'Content-Type: text/plain; charset=UTF-8; format=flowed' . PHP_EOL;
$messageBody .= 'Content-Transfer-Encoding: 8bit' . PHP_EOL;
$messageBody .= 'Content-Disposition: inline' . PHP_EOL . PHP_EOL;
$messageBody .= $this->getFlowedBody() . PHP_EOL;
$messageBody .= "--{$boundary}" . PHP_EOL;
$messageBody .= str_replace("\r\n", PHP_EOL, $data);
$messageBody .= PHP_EOL . "--{$boundary}--";
$result = mail($this->mimeEncodeAddress($this->emailTo), $this->mimeEncodeHeaderValue($this->emailSubject), $messageBody, $this->headersToStr($headers), '-f ' . $this->extractAddress($this->emailFrom));
if ($result && $invoiceData['state_id'] == 1) {
// Mark invoice sent
mysqli_param_query('UPDATE {prefix}invoice SET state_id=2 WHERE id=?', [$this->invoiceId]);
}
if ($result) {
$_SESSION['formMessage'] = 'EmailSent';
} else {
$_SESSION['formErrorMessage'] = 'EmailFailed';
}
echo header('Location: ' . _PROTOCOL_ . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . '/index.php?func=' . sanitize(getRequest('func', 'open_invoices')) . "&list=invoices&form=invoice&id={$this->invoiceId}");
}
function db_session_gc($sessionMaxAge)
{
if (!$sessionMaxAge) {
$sessionMaxAge = 900;
}
mysqli_param_query('DELETE FROM {prefix}session WHERE session_timestamp<?', [date('Y-m-d H:i:s', time() - $sessionMaxAge)]);
return true;
}
/**
* Verify database status and upgrade as necessary.
* Expects all pre-1.6.0 changes to have been already made.
*
* @return string status (OK|UPGRADED|FAILED)
*/
function verifyDatabase()
{
$res = mysqli_query_check("SHOW TABLES LIKE '{prefix}state'");
if (mysqli_num_rows($res) == 0) {
$res = mysqli_query_check(<<<EOT
CREATE TABLE {prefix}state (
id char(32) NOT NULL,
data varchar(100) NULL,
PRIMARY KEY (id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
EOT
, true);
if ($res === false) {
return 'FAILED';
}
mysqli_query_check("REPLACE INTO {prefix}state (id, data) VALUES ('version', '15')");
}
// Convert any MyISAM tables to InnoDB
$res = mysqli_param_query('SELECT data FROM {prefix}state WHERE id=?', ['tableconversiondone']);
if (mysqli_num_rows($res) == 0) {
mysqli_query_check('SET AUTOCOMMIT = 0');
mysqli_query_check('BEGIN');
mysqli_query_check('SET FOREIGN_KEY_CHECKS = 0');
$res = mysqli_query_check("SHOW TABLE STATUS WHERE ENGINE='MyISAM'");
while ($row = mysqli_fetch_array($res)) {
$res2 = mysqli_query_check('ALTER TABLE `' . $row['Name'] . '` ENGINE=INNODB', true);
if ($res2 === false) {
mysqli_query_check('ROLLBACK');
mysqli_query_check('SET FOREIGN_KEY_CHECKS = 1');
error_log('Database upgrade query failed. Please convert the tables using MyISAM engine to InnoDB engine manually');
return 'FAILED';
}
}
mysqli_query_check("INSERT INTO {prefix}state (id, data) VALUES ('tableconversiondone', '1')");
mysqli_query_check('COMMIT');
mysqli_query_check('SET AUTOCOMMIT = 1');
mysqli_query_check('SET FOREIGN_KEY_CHECKS = 1');
}
$res = mysqli_param_query('SELECT data FROM {prefix}state WHERE id=?', ['version']);
$version = mysqli_fetch_value($res);
$updates = [];
if ($version < 16) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}invoice ADD CONSTRAINT FOREIGN KEY (base_id) REFERENCES {prefix}base(id)', 'ALTER TABLE {prefix}invoice ADD COLUMN interval_type int(11) NOT NULL default 0', 'ALTER TABLE {prefix}invoice ADD COLUMN next_interval_date int(11) default NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '16')"]);
}
if ($version < 17) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}invoice_state CHANGE COLUMN name name varchar(255)', "UPDATE {prefix}invoice_state set name='StateOpen' where id=1", "UPDATE {prefix}invoice_state set name='StateSent' where id=2", "UPDATE {prefix}invoice_state set name='StatePaid' where id=3", "UPDATE {prefix}invoice_state set name='StateAnnulled' where id=4", "UPDATE {prefix}invoice_state set name='StateFirstReminder' where id=5", "UPDATE {prefix}invoice_state set name='StateSecondReminder' where id=6", "UPDATE {prefix}invoice_state set name='StateDebtCollection' where id=7", "UPDATE {prefix}print_template set name='PrintInvoiceFinnish' where name='Lasku'", "UPDATE {prefix}print_template set name='PrintDispatchNoteFinnish' where name='Lähetysluettelo'", "UPDATE {prefix}print_template set name='PrintReceiptFinnish' where name='Kuitti'", "UPDATE {prefix}print_template set name='PrintEmailFinnish' where name='Email'", "UPDATE {prefix}print_template set name='PrintInvoiceEnglish' where name='Invoice'", "UPDATE {prefix}print_template set name='PrintReceiptEnglish' where name='Receipt'", "UPDATE {prefix}print_template set name='PrintFinvoice' where name='Finvoice'", "UPDATE {prefix}print_template set name='PrintFinvoiceStyled' where name='Finvoice Styled'", "UPDATE {prefix}print_template set name='PrintInvoiceFinnishWithVirtualBarcode' where name='Lasku virtuaaliviivakoodilla'", "UPDATE {prefix}print_template set name='PrintInvoiceFinnishFormless' where name='Lomakkeeton lasku'", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintInvoiceEnglishWithVirtualBarcode', 'invoice_printer.php', 'invoice,en,Y', 'invoice_%d.pdf', 'invoice', 70, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintInvoiceEnglishFormless', 'invoice_printer_formless.php', 'invoice,en,N', 'invoice_%d.pdf', 'invoice', 80, 1)", 'ALTER TABLE {prefix}row_type CHANGE COLUMN name name varchar(255)', "UPDATE {prefix}row_type set name='TypeHour' where name='h'", "UPDATE {prefix}row_type set name='TypeDay' where name='pv'", "UPDATE {prefix}row_type set name='TypeMonth' where name='kk'", "UPDATE {prefix}row_type set name='TypePieces' where name='kpl'", "UPDATE {prefix}row_type set name='TypeYear' where name='vuosi'", "UPDATE {prefix}row_type set name='TypeLot' where name='erä'", "UPDATE {prefix}row_type set name='TypeKilometer' where name='km'", "UPDATE {prefix}row_type set name='TypeKilogram' where name='kg'", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '17')"]);
}
if ($version < 18) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}base ADD COLUMN country varchar(255) default NULL', 'ALTER TABLE {prefix}company ADD COLUMN country varchar(255) default NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '18')"]);
}
if ($version < 19) {
$updates = array_merge($updates, ["UPDATE {prefix}session_type set name='SessionTypeUser' where name='Käyttäjä'", "UPDATE {prefix}session_type set name='SessionTypeAdmin' where name='Ylläpitäjä'", "UPDATE {prefix}session_type set name='SessionTypeBackupUser' where name='Käyttäjä - varmuuskopioija'", "UPDATE {prefix}session_type set name='SessionTypeReadOnly' where name='Vain laskujen ja raporttien tarkastelu'", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '19')"]);
}
if ($version < 20) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product CHANGE COLUMN unit_price unit_price decimal(15,5)', 'ALTER TABLE {prefix}invoice_row CHANGE COLUMN price price decimal(15,5)', 'ALTER TABLE {prefix}product CHANGE COLUMN discount discount decimal(4,1) NULL', 'ALTER TABLE {prefix}invoice_row CHANGE COLUMN discount discount decimal(4,1) NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '20')"]);
}
if ($version < 21) {
$updates = array_merge($updates, ["INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintInvoiceSwedish', 'invoice_printer.php', 'invoice,sv-FI,Y', 'faktura_%d.pdf', 'invoice', 90, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintInvoiceSwedishFormless', 'invoice_printer_formless.php', 'invoice,sv-FI,N', 'faktura_%d.pdf', 'invoice', 100, 1)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '21')"]);
}
if ($version < 22) {
$updates = array_merge($updates, ["INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintEmailReceiptFinnish', 'invoice_printer_email.php', 'receipt', 'kuitti_%d.pdf', 'invoice', 110, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintEmailReceiptSwedish', 'invoice_printer_email.php', 'receipt,sv-FI', 'kvitto_%d.pdf', 'invoice', 120, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintEmailReceiptEnglish', 'invoice_printer_email.php', 'receipt,en', 'receipt_%d.pdf', 'invoice', 130, 1)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '22')"]);
}
if ($version < 23) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product ADD COLUMN order_no int(11) default NULL', 'ALTER TABLE {prefix}users CHANGE COLUMN name name varchar(255)', 'ALTER TABLE {prefix}users CHANGE COLUMN login login varchar(255)', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '23')"]);
}
if ($version < 24) {
$updates = array_merge($updates, ["INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationFinnish', 'invoice_printer_order_confirmation.php', 'receipt', 'tilausvahvistus_%d.pdf', 'invoice', 140, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationSwedish', 'invoice_printer_order_confirmation.php', 'receipt,sv-FI', 'orderbekraftelse_%d.pdf', 'invoice', 150, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationEnglish', 'invoice_printer_order_confirmation.php', 'receipt,en', 'order_confirmation_%d.pdf', 'invoice', 160, 1)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '24')"]);
}
if ($version < 25) {
$updates = array_merge($updates, [<<<EOT
CREATE TABLE {prefix}delivery_terms (
id int(11) NOT NULL auto_increment,
deleted tinyint NOT NULL default 0,
name varchar(255) default NULL,
order_no int(11) default NULL,
PRIMARY KEY (id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci
EOT
, <<<EOT
CREATE TABLE {prefix}delivery_method (
id int(11) NOT NULL auto_increment,
deleted tinyint NOT NULL default 0,
name varchar(255) default NULL,
order_no int(11) default NULL,
PRIMARY KEY (id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci
EOT
, 'ALTER TABLE {prefix}invoice ADD COLUMN delivery_terms_id int(11) default NULL', 'ALTER TABLE {prefix}invoice ADD CONSTRAINT FOREIGN KEY (delivery_terms_id) REFERENCES {prefix}delivery_terms(id)', 'ALTER TABLE {prefix}invoice ADD COLUMN delivery_method_id int(11) default NULL', 'ALTER TABLE {prefix}invoice ADD CONSTRAINT FOREIGN KEY (delivery_method_id) REFERENCES {prefix}delivery_method(id)', 'ALTER TABLE {prefix}company ADD COLUMN delivery_terms_id int(11) default NULL', 'ALTER TABLE {prefix}company ADD CONSTRAINT FOREIGN KEY (delivery_terms_id) REFERENCES {prefix}delivery_terms(id)', 'ALTER TABLE {prefix}company ADD COLUMN delivery_method_id int(11) default NULL', 'ALTER TABLE {prefix}company ADD CONSTRAINT FOREIGN KEY (delivery_method_id) REFERENCES {prefix}delivery_method(id)', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '25')"]);
}
if ($version < 26) {
$updates = array_merge($updates, ['CREATE INDEX {prefix}company_name on {prefix}company(company_name)', 'CREATE INDEX {prefix}company_id on {prefix}company(company_id)', 'CREATE INDEX {prefix}company_deleted on {prefix}company(deleted)', 'CREATE INDEX {prefix}invoice_no on {prefix}invoice(invoice_no)', 'CREATE INDEX {prefix}invoice_ref_number on {prefix}invoice(ref_number)', 'CREATE INDEX {prefix}invoice_name on {prefix}invoice(name)', 'CREATE INDEX {prefix}invoice_deleted on {prefix}invoice(deleted)', 'CREATE INDEX {prefix}base_name on {prefix}base(name)', 'CREATE INDEX {prefix}base_deleted on {prefix}base(deleted)', 'CREATE INDEX {prefix}product_name on {prefix}product(product_name)', 'CREATE INDEX {prefix}product_code on {prefix}product(product_code)', 'CREATE INDEX {prefix}product_deleted on {prefix}product(deleted)', 'CREATE INDEX {prefix}product_order_no_deleted on {prefix}product(order_no, deleted)', 'CREATE INDEX {prefix}users_name on {prefix}users(name)', 'CREATE INDEX {prefix}users_deleted on {prefix}users(deleted)', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '26')"]);
}
if ($version < 27) {
$updates = array_merge($updates, ["INSERT INTO {prefix}invoice_state (name, order_no) VALUES ('StatePaidInCash', 17)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '27')"]);
}
if ($version < 28) {
$updates = array_merge($updates, ["INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationEmailFinnish', 'invoice_printer_order_confirmation_email.php', 'receipt', 'tilausvahvistus_%d.pdf', 'invoice', 170, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationEmailSwedish', 'invoice_printer_order_confirmation_email.php', 'receipt,sv-FI', 'orderbekraftelse_%d.pdf', 'invoice', 180, 1)", "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintOrderConfirmationEmailEnglish', 'invoice_printer_order_confirmation_email.php', 'receipt,en', 'order_confirmation_%d.pdf', 'invoice', 190, 1)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '28')"]);
}
if ($version < 29) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}session CHANGE COLUMN id id varchar(255)', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '29')"]);
}
if ($version < 30) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}base ADD COLUMN payment_intermediator varchar(100) default NULL', 'ALTER TABLE {prefix}company ADD COLUMN payment_intermediator varchar(100) default NULL', "INSERT INTO {prefix}print_template (name, filename, parameters, output_filename, type, order_no, inactive) VALUES ('PrintFinvoiceSOAP', 'invoice_printer_finvoice_soap.php', '', 'finvoice_%d.xml', 'invoice', 55, 1)", "REPLACE INTO {prefix}state (id, data) VALUES ('version', '30')"]);
}
if ($version < 31) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product ADD COLUMN ean_code1 varchar(13) default NULL', 'ALTER TABLE {prefix}product ADD COLUMN ean_code2 varchar(13) default NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '31')"]);
}
if ($version < 32) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product ADD COLUMN purchase_price decimal(15,5) NULL', 'ALTER TABLE {prefix}product ADD COLUMN stock_balance int(11) default NULL', <<<EOT
CREATE TABLE {prefix}stock_balance_log (
id int(11) NOT NULL auto_increment,
time timestamp NOT NULL default CURRENT_TIMESTAMP,
user_id int(11) NOT NULL,
product_id int(11) NOT NULL,
stock_change int(11) NOT NULL,
description varchar(255) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES {prefix}users(id),
FOREIGN KEY (product_id) REFERENCES {prefix}product(id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci
EOT
, "REPLACE INTO {prefix}state (id, data) VALUES ('version', '32')"]);
}
if ($version < 33) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}base ADD COLUMN receipt_email_subject varchar(255) NULL', 'ALTER TABLE {prefix}base ADD COLUMN receipt_email_body text NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '33')"]);
}
if ($version < 34) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product CHANGE COLUMN stock_balance stock_balance decimal(11,2) default NULL', 'ALTER TABLE {prefix}stock_balance_log CHANGE COLUMN stock_change stock_change decimal(11,2) default NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '34')"]);
}
if ($version < 35) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}invoice_state ADD COLUMN invoice_open tinyint NOT NULL default 0', 'ALTER TABLE {prefix}invoice_state ADD COLUMN invoice_unpaid tinyint NOT NULL default 0', 'UPDATE {prefix}invoice_state SET invoice_open=1 WHERE id IN (1)', 'UPDATE {prefix}invoice_state SET invoice_unpaid=1 WHERE id IN (2, 5, 6, 7)', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '35')"]);
}
if ($version < 36) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}product CHANGE COLUMN ean_code1 barcode1 varchar(255) default NULL', 'ALTER TABLE {prefix}product CHANGE COLUMN ean_code2 barcode2 varchar(255) default NULL', 'ALTER TABLE {prefix}product ADD COLUMN barcode1_type varchar(20) default NULL', 'ALTER TABLE {prefix}product ADD COLUMN barcode2_type varchar(20) default NULL', "UPDATE {prefix}product SET barcode1_type='EAN13' WHERE barcode1 IS NOT NULL", "UPDATE {prefix}product SET barcode2_type='EAN13' WHERE barcode2 IS NOT NULL", 'ALTER TABLE {prefix}base ADD COLUMN order_confirmation_email_subject varchar(255) NULL', 'ALTER TABLE {prefix}base ADD COLUMN order_confirmation_email_body text NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '36')"]);
}
if ($version < 37) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}company ADD COLUMN payment_days int(11) default NULL', 'ALTER TABLE {prefix}company ADD COLUMN terms_of_payment varchar(255) NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '37')"]);
}
if ($version < 38) {
$updates = array_merge($updates, ['UPDATE {prefix}invoice_row ir SET ir.row_date=(SELECT i.invoice_date FROM {prefix}invoice i where i.id=ir.invoice_id) WHERE ir.row_date IS NULL', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '38')"]);
}
if ($version < 39) {
// Check for a bug in database creation script in v1.12.0 and v1.12.1
$res = mysqli_param_query("SELECT count(*) FROM information_schema.columns WHERE table_schema = '" . _DB_NAME_ . "' AND table_name = '{prefix}invoice_row' AND column_name = 'partial_payment'");
$count = mysqli_fetch_value($res);
if ($count == 0) {
$updates = array_merge($updates, ['ALTER TABLE {prefix}invoice_row ADD COLUMN partial_payment tinyint NOT NULL default 0', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '39')"]);
}
}
if ($version < 40) {
$updates = array_merge($updates, ['UPDATE {prefix}invoice_state SET invoice_unpaid=1 WHERE id=1', "REPLACE INTO {prefix}state (id, data) VALUES ('version', '40')"]);
}
if (!empty($updates)) {
mysqli_query_check('SET AUTOCOMMIT = 0');
mysqli_query_check('BEGIN');
foreach ($updates as $update) {
$res = mysqli_query_check($update, true);
if ($res === false) {
mysqli_query_check('ROLLBACK');
mysqli_query_check('SET AUTOCOMMIT = 1');
error_log('Database upgrade query failed. Please execute the following queries manually:');
foreach ($updates as $s) {
error_log(str_replace('{prefix}', _DB_PREFIX_ . '_', $s) . ';');
}
return 'FAILED';
}
}
mysqli_query_check('COMMIT');
mysqli_query_check('SET AUTOCOMMIT = 1');
return 'UPGRADED';
}
return 'OK';
}
protected function process_import_row($table, $row, $dupMode, $dupCheckColumns, $mode, &$addedRecordId)
{
global $dblink;
$sep = getRequest('decimal_separator', ',');
if ($sep != '.') {
$fieldDefs = getFormElements($table);
foreach ($row as $key => &$value) {
foreach ($fieldDefs as $fieldDef) {
if ($fieldDef['name'] === $key) {
if ($fieldDef['type'] == 'INT' && in_array($fieldDef['style'], ['percent', 'currency'])) {
$value = str_replace($sep, '.', $value);
}
break;
}
}
}
}
$result = '';
$recordId = null;
if ($dupMode != '' && count($dupCheckColumns) > 0) {
$query = "select id from {prefix}{$table} where Deleted=0";
$where = '';
$params = [];
foreach ($dupCheckColumns as $dupCol) {
$where .= " AND {$dupCol}=?";
$params[] = $row[$dupCol];
}
$res = mysqli_param_query($query . $where, $params);
if ($dupRow = mysqli_fetch_row($res)) {
$id = $dupRow[0];
$found_dup = true;
if ($dupMode == 'update') {
$result = "Update existing row id {$id} in table {$table}";
} else {
$result = "Not updating existing row id {$id} in table {$table}";
}
if ($mode == 'import' && $dupMode == 'update') {
// Update existing row
$query = "UPDATE {prefix}{$table} SET ";
$columns = '';
$params = [];
foreach ($row as $key => $value) {
if ($key == 'id') {
continue;
}
if ($columns) {
$columns .= ', ';
}
$columns .= "{$key}=?";
$params[] = $value;
}
$query .= "{$columns} WHERE id=?";
$params[] = $id;
mysqli_param_query($query, $params);
}
return $result;
}
}
// Add new row
$query = "INSERT INTO {prefix}{$table} ";
$columns = '';
$values = '';
$params = [];
foreach ($row as $key => $value) {
if ($key == 'id') {
continue;
}
if ($columns) {
$columns .= ', ';
}
if ($values) {
$values .= ', ';
}
$columns .= $key;
$values .= '?';
$params[] = $value;
}
$query .= "({$columns}) VALUES ({$values})";
if ($mode == 'import') {
mysqli_param_query($query, $params);
$addedRecordId = mysqli_insert_id($dblink);
} else {
$addedRecordId = 'x';
}
$result = "Add as new (ID {$addedRecordId}) into table {$table}";
return $result;
}
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;
}
$arrRefundingInvoice = array('allow_null' => true);
$intInvoiceId = getRequest('id', 0);
if ($intInvoiceId) {
$strQuery = 'SELECT refunded_invoice_id ' . 'FROM {prefix}invoice ' . 'WHERE id=?';
// ok to maintain links to deleted invoices too
$intRes = mysqli_param_query($strQuery, array($intInvoiceId));
$strBaseLink = '?' . preg_replace('/&id=\\d*/', '', $_SERVER['QUERY_STRING']);
$strBaseLink = preg_replace('/&/', '&', $strBaseLink);
if ($intRes) {
$intRefundedInvoiceId = mysqli_fetch_value($intRes);
if ($intRefundedInvoiceId) {
$arrRefundedInvoice = array('name' => 'get', 'label' => $GLOBALS['locShowRefundedInvoice'], 'type' => 'BUTTON', 'style' => 'custom', 'listquery' => "{$strBaseLink}&id={$intRefundedInvoiceId}", 'position' => 2, 'allow_null' => true);
}
}
$strQuery = 'SELECT id ' . 'FROM {prefix}invoice ' . 'WHERE deleted=0 AND refunded_invoice_id=?';
$intRes = mysqli_param_query($strQuery, array($intInvoiceId));
if ($intRes && ($row = mysqli_fetch_assoc($intRes))) {
$intRefundingInvoiceId = $row['id'];
if ($intRefundingInvoiceId) {
$arrRefundingInvoice = array('name' => 'get', 'label' => $GLOBALS['locShowRefundingInvoice'], 'type' => 'BUTTON', 'style' => 'custom', 'listquery' => "'{$strBaseLink}&id={$intRefundingInvoiceId}", 'position' => 2, 'allow_null' => true);
}
}
}
$invoicePrintChecks = '';
$invoiceNumberUpdatePrefix = '';
$invoiceNumberUpdateSuffix = '';
$companyOnChange = '';
$getInvoiceNr = '';
$updateDates = '';
$addCompanyCode = '';
if (sesWriteAccess()) {
$strQuery = 'SELECT * ' . 'FROM {prefix}invoice_row ' . 'WHERE deleted=0 AND invoice_id=?';
$intRes = mysqli_param_query($strQuery, [$intInvoiceId], 'exception');
while ($row = mysqli_fetch_assoc($intRes)) {
if ($boolRefund) {
$row['pcs'] = -$row['pcs'];
} else {
if ($row['reminder_row']) {
continue;
}
}
unset($row['id']);
$row['invoice_id'] = $intNewId;
if (getSetting('invoice_update_row_dates_on_copy')) {
$row['row_date'] = $newRowDate;
}
// Update product stock balance
if ($row['product_id'] !== null) {
updateProductStockBalance(null, $row['product_id'], $row['pcs']);
}
$strQuery = 'INSERT INTO {prefix}invoice_row(' . implode(', ', array_keys($row)) . ') ' . 'VALUES (' . str_repeat('?, ', count($row) - 1) . '?)';
mysqli_param_query($strQuery, $row, 'exception');
}
} catch (Exception $e) {
mysqli_query_check('ROLLBACK');
mysqli_query_check('SET AUTOCOMMIT = 1');
die($e->message);
}
mysqli_query_check('COMMIT');
mysqli_query_check('SET AUTOCOMMIT = 1');
}
header('Location: ' . _PROTOCOL_ . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php?func={$strFunc}&list={$strList}&form=invoice&id={$intNewId}");
$invoiceData = mysqli_fetch_assoc($intRes);
if (!$invoiceData) {
die('Could not find invoice data');
}
$strQuery = 'SELECT * FROM {prefix}company WHERE id=?';
$intRes = mysqli_param_query($strQuery, [$invoiceData['company_id']]);
$recipientData = mysqli_fetch_assoc($intRes);
if (!empty($recipientData['company_id'])) {
$recipientData['vat_id'] = createVATID($recipientData['company_id']);
} else {
$recipientData['vat_id'] = '';
}
$strQuery = 'SELECT * FROM {prefix}base WHERE id=?';
$intRes = mysqli_param_query($strQuery, [$invoiceData['base_id']]);
$senderData = mysqli_fetch_assoc($intRes);
if (!$senderData) {
die('Could not find invoice sender data');
}
$senderData['vat_id'] = createVATID($senderData['company_id']);
$strQuery = 'SELECT pr.product_name, pr.product_code, pr.price_decimals, pr.barcode1, pr.barcode1_type, pr.barcode2, pr.barcode2_type, ir.description, ir.pcs, ir.price, IFNULL(ir.discount, 0) as discount, ir.row_date, ir.vat, ir.vat_included, ir.reminder_row, rt.name type ' . 'FROM {prefix}invoice_row ir ' . 'LEFT OUTER JOIN {prefix}row_type rt ON rt.id = ir.type_id ' . 'LEFT OUTER JOIN {prefix}product pr ON ir.product_id = pr.id ' . 'WHERE ir.invoice_id=? AND ir.deleted=0 ORDER BY ir.order_no, row_date, pr.product_name DESC, ir.description DESC';
$intRes = mysqli_param_query($strQuery, [$intInvoiceId]);
$invoiceRowData = [];
while ($row = mysqli_fetch_assoc($intRes)) {
$invoiceRowData[] = $row;
}
if (sesWriteAccess()) {
mysqli_param_query('UPDATE {prefix}invoice SET print_date=? where id=?', [date('Ymd'), $intInvoiceId]);
}
$printer = instantiateInvoicePrinter(trim($printTemplateFile));
$printer->init($intInvoiceId, $printParameters, $printOutputFileName, $senderData, $recipientData, $invoiceData, $invoiceRowData);
$printer->printInvoice();
private function printReport()
{
$intBaseId = getRequest('base', false);
$intCompanyId = getRequest('company', false);
$grouping = getRequest('grouping', '');
$format = getRequest('format', 'html');
$printFields = getRequest('fields', array());
$rowTypes = getRequest('row_types', 'all');
$dateRange = explode(' - ', getRequest('date', ''));
$startDate = $dateRange[0];
$endDate = isset($dateRange[1]) ? $dateRange[1] : $startDate;
if ($startDate) {
$startDate = dateConvDate2DBDate($startDate);
}
if ($endDate) {
$endDate = dateConvDate2DBDate($endDate);
}
$rowDateRange = explode(' - ', getRequest('row_date', ''));
$rowStartDate = $rowDateRange[0];
$rowEndDate = isset($rowDateRange[1]) ? $rowDateRange[1] : $rowStartDate;
if ($rowStartDate) {
$rowStartDate = dateConvDate2DBDate($rowStartDate);
}
if ($rowEndDate) {
$rowEndDate = dateConvDate2DBDate($rowEndDate);
}
$paymentDateRange = explode(' - ', getRequest('payment_date', ''));
$paymentStartDate = $paymentDateRange[0];
$paymentEndDate = isset($paymentDateRange[1]) ? $paymentDateRange[1] : '';
if ($paymentStartDate) {
$paymentStartDate = dateConvDate2DBDate($paymentStartDate);
}
if ($paymentEndDate) {
$paymentEndDate = dateConvDate2DBDate($paymentEndDate);
}
$arrParams = array();
$strQuery = "SELECT i.id, i.invoice_no, i.invoice_date, i.due_date, i.payment_date, i.ref_number, i.ref_number, c.company_name AS name, c.billing_address, ist.name as state " . "FROM {prefix}invoice i " . "LEFT OUTER JOIN {prefix}company c ON c.id = i.company_id " . "LEFT OUTER JOIN {prefix}invoice_state ist ON i.state_id = ist.id " . "WHERE i.deleted=0";
if ($startDate) {
$strQuery .= ' AND i.invoice_date >= ?';
$arrParams[] = $startDate;
}
if ($endDate) {
$strQuery .= ' AND i.invoice_date <= ?';
$arrParams[] = $endDate;
}
if ($paymentStartDate) {
$strQuery .= ' AND i.payment_date >= ?';
$arrParams[] = $paymentStartDate;
}
if ($paymentEndDate) {
$strQuery .= ' AND i.payment_date <= ?';
$arrParams[] = $paymentEndDate;
}
if ($intBaseId) {
$strQuery .= ' AND i.base_id = ?';
$arrParams[] = $intBaseId;
}
if ($intCompanyId) {
$strQuery .= ' AND i.company_id = ?';
$arrParams[] = $intCompanyId;
}
$strQuery2 = '';
$strQuery3 = "SELECT id, name " . "FROM {prefix}invoice_state WHERE deleted=0 " . "ORDER BY order_no";
$intRes = mysqli_query_check($strQuery3);
while ($row = mysqli_fetch_assoc($intRes)) {
$intStateId = $row['id'];
$strStateName = $row['name'];
$strTemp = "stateid_{$intStateId}";
$tmpSelected = getRequest($strTemp, false);
if ($tmpSelected) {
$strQuery2 .= 'i.state_id = ? OR ';
$arrParams[] = $intStateId;
}
}
if ($strQuery2) {
$strQuery2 = ' AND (' . substr($strQuery2, 0, -4) . ')';
}
$strQuery .= "{$strQuery2} ORDER BY ";
switch ($grouping) {
case 'state':
$strQuery .= "state_id, invoice_date, invoice_no";
break;
case 'client':
$strQuery .= "name, invoice_date, invoice_no";
break;
default:
$strQuery .= "invoice_date, invoice_no";
}
$this->printHeader($format, $printFields, $startDate, $endDate);
$intTotSum = 0;
$intTotVAT = 0;
$intTotSumVAT = 0;
$currentGroup = false;
$groupTotSum = 0;
$groupTotVAT = 0;
$groupTotSumVAT = 0;
$intRes = mysqli_param_query($strQuery, $arrParams);
while ($row = mysqli_fetch_assoc($intRes)) {
switch ($grouping) {
case 'state':
$invoiceGroup = $row['state'];
break;
case 'month':
$invoiceGroup = substr($row['invoice_date'], 4, 2);
break;
case 'client':
$invoiceGroup = $row['name'];
break;
default:
$invoiceGroup = false;
}
$rowParams = array($row['id']);
$strQuery = "SELECT ir.description, ir.pcs, ir.price, ir.discount, ir.row_date, ir.vat, ir.vat_included " . "FROM {prefix}invoice_row ir " . "WHERE ir.invoice_id=? AND ir.deleted=0";
if ($rowStartDate) {
$strQuery .= ' AND ir.row_date >= ?';
$rowParams[] = $rowStartDate;
}
if ($rowEndDate) {
$strQuery .= ' AND ir.row_date <= ?';
$rowParams[] = $rowEndDate;
}
if ($rowTypes != 'all') {
if ($rowTypes == 'normal') {
$strQuery .= ' AND ir.reminder_row = 0';
} else {
if ($rowTypes == 'reminder') {
$strQuery .= ' AND ir.reminder_row in (1, 2)';
}
}
}
$intRes2 = mysqli_param_query($strQuery, $rowParams);
$intRowSum = 0;
$intRowVAT = 0;
$intRowSumVAT = 0;
$rows = false;
while ($row2 = mysqli_fetch_assoc($intRes2)) {
$rows = true;
list($intSum, $intVAT, $intSumVAT) = calculateRowSum($row2['price'], $row2['pcs'], $row2['vat'], $row2['vat_included'], $row2['discount']);
$intRowSum += $intSum;
$intRowVAT += $intVAT;
$intRowSumVAT += $intSumVAT;
$intTotSum += $intSum;
$intTotVAT += $intVAT;
$intTotSumVAT += $intSumVAT;
}
if (!$rows) {
continue;
}
if ($grouping && $currentGroup !== false && $currentGroup != $invoiceGroup) {
$this->printGroupSums($format, $printFields, $row, $groupTotSum, $groupTotVAT, $groupTotSumVAT);
$groupTotSum = 0;
$groupTotVAT = 0;
$groupTotSumVAT = 0;
}
$currentGroup = $invoiceGroup;
$groupTotSum += $intRowSum;
$groupTotVAT += $intRowVAT;
$groupTotSumVAT += $intRowSumVAT;
$this->printRow($format, $printFields, $row, $intRowSum, $intRowVAT, $intRowSumVAT);
}
if ($grouping) {
$this->printGroupSums($format, $printFields, $row, $groupTotSum, $groupTotVAT, $groupTotSumVAT);
}
$this->printTotals($format, $printFields, $intTotSum, $intTotVAT, $intTotSumVAT);
$this->printFooter($format, $printFields);
}
public function launch()
{
$charset = getRequest('charset', 'UTF-8');
$table = getRequest('table', '');
$format = getRequest('format', '');
$fieldDelimiter = getRequest('field_delim', ',');
$enclosureChar = getRequest('enclosure_char', '"');
$rowDelimiter = getRequest('row_delim', "\n");
$columns = getRequest('column', '');
$childRows = getRequest('child_rows', '');
$deletedRecords = getRequest('deleted', false);
if ($table && $format && $columns) {
if (!table_valid($table)) {
die('Invalid table name');
}
$res = mysqli_query_check("show fields from {prefix}{$table}");
$field_count = mysqli_num_rows($res);
$field_defs = array();
while ($row = mysqli_fetch_assoc($res)) {
$field_defs[$row['Field']] = $row;
}
foreach ($columns as $key => $column) {
if (!$column) {
unset($columns[$key]);
} elseif (!isset($field_defs[$column])) {
die('Invalid column name');
}
}
ob_clean();
$filename = isset($GLOBALS["locTable_{$table}"]) ? $GLOBALS["locTable_{$table}"] : $table;
switch ($format) {
case 'csv':
$field_delims = $this->importer->get_field_delims();
$enclosure_chars = $this->importer->get_enclosure_chars();
$row_delims = $this->importer->get_row_delims();
if (!isset($field_delims[$fieldDelimiter])) {
die('Invalid field delimiter');
}
$fieldDelimiter = $field_delims[$fieldDelimiter]['char'];
if (!isset($enclosure_chars[$enclosureChar])) {
die('Invalid enclosure character');
}
$enclosureChar = $enclosure_chars[$enclosureChar]['char'];
if (!isset($row_delims[$rowDelimiter])) {
die('Invalid field delimiter');
}
$rowDelimiter = $row_delims[$rowDelimiter]['char'];
header('Content-type: text/csv');
header("Content-Disposition: attachment; filename=\"{$filename}.csv\"");
if ($charset == 'UTF-16') {
echo iconv($charset, 'UTF-16', '');
}
// output BOM
$this->output_str($this->str_putcsv($columns, $fieldDelimiter, $enclosureChar) . $rowDelimiter, $charset);
break;
case 'xml':
header('Content-type: text/xml');
header("Content-Disposition: attachment; filename=\"{$filename}.xml\"");
if ($charset == 'UTF-16') {
echo iconv($charset, 'UTF-16', '');
}
// output BOM
$this->output_str("<?xml version=\"1.0\"?>\n<records>\n", $charset);
break;
case 'json':
header('Content-type: application/json');
header("Content-Disposition: attachment; filename=\"{$filename}.json\"");
if ($charset == 'UTF-16') {
echo iconv($charset, 'UTF-16', '');
}
// output BOM
echo "{\"{$table}\":[\n";
break;
}
$query = "select * from {prefix}{$table}";
if (!$deletedRecords) {
$query .= ' where deleted=0';
if ($table == 'company_contact') {
$query .= ' and company_id not in (select id from {prefix}company where deleted=1)';
} elseif ($table == 'invoice_row') {
$query .= ' and invoice_id not in (select id from {prefix}invoice where deleted=1)';
}
}
$res = mysqli_query_check($query);
$first = true;
while ($row = mysqli_fetch_assoc($res)) {
$data = array();
foreach ($columns as $column) {
$value = $row[$column];
if (is_null($value)) {
$data[$column] = '';
}
if ($value && substr($field_defs[$column]['Type'], 0, 8) == 'longblob') {
$data[$column] = '0x' . bin2hex($value);
} else {
$data[$column] = $value;
}
}
switch ($format) {
case 'csv':
$this->output_str($this->str_putcsv($data, $fieldDelimiter, $enclosureChar) . $rowDelimiter, $charset);
break;
case 'xml':
$str = " <{$table}>\n";
foreach ($columns as $column) {
$str .= " <{$column}>" . xml_encode($data[$column]) . "</{$column}>\n";
}
if ($childRows && ($table == 'invoice' || $table == 'company')) {
if ($table == 'invoice') {
$cres = mysqli_param_query('select * from {prefix}invoice_row where invoice_id=?', array($row['id']));
} else {
$cres = mysqli_param_query('select * from {prefix}company_contact where company_id=?', array($row['id']));
}
while ($crow = mysqli_fetch_assoc($cres)) {
$str .= " <invoice_row>\n";
foreach ($crow as $column => $value) {
$str .= " <{$column}>" . xml_encode($value) . "</{$column}>\n";
}
$str .= " </invoice_row>\n";
}
}
$str .= " </{$table}>\n";
$this->output_str($str, $charset);
break;
case 'json':
if ($childRows && ($table == 'invoice' || $table == 'company')) {
if ($table == 'invoice') {
$childTable = 'invoice_row';
} else {
$childTable = 'company_contact';
}
$data[$childTable] = array();
if ($table == 'invoice') {
$cres = mysqli_param_query('select * from {prefix}invoice_row where invoice_id=?', array($row['id']));
} else {
$cres = mysqli_param_query('select * from {prefix}company_contact where company_id=?', array($row['id']));
}
while ($crow = mysqli_fetch_assoc($cres)) {
$data[$childTable][] = $crow;
}
}
if ($first) {
$first = false;
} else {
echo ",\n";
}
$this->output_str(json_encode($data), $charset);
break;
}
}
switch ($format) {
case 'xml':
$this->output_str("</records>\n");
break;
case 'json':
echo "\n]}\n";
break;
}
exit;
}
?>
<script type="text/javascript">
$(document).ready(function() {
$('#imessage').ajaxStart(function() {
$('#spinner').css('visibility', 'visible');
});
$('#imessage').ajaxStop(function() {
$('#spinner').css('visibility', 'hidden');
});
$('#imessage').ajaxError(function(event, request, settings) {
alert('Server request failed: ' + request.status + ' - ' + request.statusText);
$('#spinner').css('visibility', 'hidden');
});
update_field_states();
reset_columns();
});
var g_column_id = 0;
function reset_columns()
{
$("#columns > select").remove();
g_column_id = 0;
add_column();
}
function add_column()
{
var table = document.getElementById("sel_table").value;
$.getJSON("json.php?func=get_table_columns&table=" + table, function(json) {
var index = ++g_column_id;
var columns = document.getElementById("columns");
var select = document.createElement("select");
select.id = "column" + index;
select.name = "column[]";
select.onchange = update_columns;
var option = document.createElement("option");
option.value = "";
option.text = "<?php
echo $GLOBALS['locImportExportColumnNone'];
?>
";
select.options.add(option);
for (var i = 0; i < json.columns.length; i++)
{
var option = document.createElement("option");
option.value = json.columns[i].name;
option.text = json.columns[i].name;
select.options.add(option);
}
columns.appendChild(document.createTextNode(' '));
columns.appendChild(select);
});
}
function update_columns()
{
if (this.value == "" && $("#columns > select").size() > 1)
$(this).remove();
else if (this.id == "column" + g_column_id)
add_column();
}
function update_field_states()
{
var type = document.getElementById('format').value;
document.getElementById('field_delim').disabled = type != 'csv';
document.getElementById('enclosure_char').disabled = type != 'csv';
document.getElementById('row_delim').disabled = type != 'csv';
document.getElementById('child_rows').disabled = type == 'csv';
}
function add_all_columns()
{
var options = document.getElementById("column" + g_column_id).options;
$("#columns > select").remove();
g_column_id = 0;
var columns = document.getElementById("columns");
for (var i = 1; i < options.length; i++)
{
var index = ++g_column_id;
var select = document.createElement("select");
select.id = "column" + index;
select.name = "column[]";
select.onchange = update_columns;
var option = document.createElement("option");
for (var opt = 0; opt < options.length; opt++)
select.options.add(options[opt].cloneNode(true));
select.selectedIndex = i;
columns.appendChild(document.createTextNode(' '));
columns.appendChild(select);
}
}
</script>
<div class="form_container">
<h1><?php
echo $GLOBALS['locExport'];
?>
</h1>
<span id="imessage" style="display: none"></span>
<span id="spinner" style="visibility: hidden"><img src="images/spinner.gif" alt=""></span>
<form id="export_form" name="export_form" method="GET">
<input type="hidden" name="func" value="system">
<input type="hidden" name="operation" value="export">
<div class="medium_label"><?php
echo $GLOBALS['locImportExportCharacterSet'];
?>
</div>
<div class="field">
<select id="charset" name="charset">
<option value="UTF-8">UTF-8</option>
<option value="ISO-8859-1">ISO-8859-1</option>
<option value="ISO-8859-15">ISO-8859-15</option>
<option value="Windows-1251">Windows-1251</option>
<option value="UTF-16">UTF-16</option>
<option value="UTF-16LE">UTF-16 LE</option>
<option value="UTF-16BE">UTF-16 BE</option>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locImportExportTable'];
?>
</div>
<div class="field">
<select id="sel_table" name="table" onchange="reset_columns()">
<option value="company"><?php
echo $GLOBALS['locImportExportTableCompanies'];
?>
</option>
<option value="company_contact"><?php
echo $GLOBALS['locImportExportTableCompanyContacts'];
?>
</option>
<option value="base"><?php
echo $GLOBALS['locImportExportTableBases'];
?>
</option>
<option value="invoice"><?php
echo $GLOBALS['locImportExportTableInvoices'];
?>
</option>
<option value="invoice_row"><?php
echo $GLOBALS['locImportExportTableInvoiceRows'];
?>
</option>
<option value="product"><?php
echo $GLOBALS['locImportExportTableProducts'];
?>
</option>
<option value="row_type"><?php
echo $GLOBALS['locImportExportTableRowTypes'];
?>
</option>
<option value="invoice_state"><?php
echo $GLOBALS['locImportExportTableInvoiceStates'];
?>
</option>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locImportExportFormat'];
?>
</div>
<div class="field">
<select id="format" name="format" onchange="update_field_states()">
<option value="csv">CSV</option>
<option value="xml">XML</option>
<option value="json">JSON</option>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locImportExportFieldDelimiter'];
?>
</div>
<div class="field">
<select id="field_delim" name="field_delim">
<?php
$field_delims = $this->importer->get_field_delims();
foreach ($field_delims as $key => $delim) {
echo "<option value=\"{$key}\">" . $delim['name'] . "</option>\n";
}
?>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locImportExportEnclosureCharacter'];
?>
</div>
<div class="field">
<select id="enclosure_char" name="enclosure_char">
<?php
$enclosure_chars = $this->importer->get_enclosure_chars();
foreach ($enclosure_chars as $key => $delim) {
echo "<option value=\"{$key}\">" . $delim['name'] . "</option>\n";
}
?>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locImportExportRowDelimiter'];
?>
</div>
<div class="field">
<select id="row_delim" name="row_delim">
<?php
$row_delims = $this->importer->get_row_delims();
foreach ($row_delims as $key => $delim) {
echo "<option value=\"{$key}\">" . $delim['name'] . "</option>\n";
}
?>
</select>
</div>
<div class="medium_label"><?php
echo $GLOBALS['locExportIncludeChildRows'];
?>
</div>
<div class="field">
<input id="child_rows" name="child_rows" type="checkbox" checked="checked">
</div>
<div class="medium_label"><?php
echo $GLOBALS['locExportIncludeDeletedRecords'];
?>
</div>
<div class="field">
<input id="deleted" name="deleted" type="checkbox">
</div>
<div class="medium_label"><?php
echo $GLOBALS['locExportColumns'];
?>
<input type="button" value="<?php
echo $GLOBALS['locExportAddAllColumns'];
?>
" onclick="add_all_columns()"></div>
<div id="columns" class="field">
</div>
<div class="form_buttons" style="clear: both">
<input type="submit" value="<?php
echo $GLOBALS['locExportDo'];
?>
">
</div>
</form>
</div>
<?php
}
