json.php

<?php
/**
 * JSON API
 *
 * PHP version 8
 *
 * Copyright (C) Samu Reinikainen 2004-2008
 * Copyright (C) Ere Maijala 2010-2022
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License version 2,
 * as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category MLInvoice
 * @package  MLInvoice\Base
 * @author   Ere Maijala <ere@labs.fi>
 * @license  http://opensource.org/licenses/gpl-2.0.php GNU General Public License
 * @link     http://labs.fi/mlinvoice.eng.php
 */
$phpErrors = [];
set_error_handler('handleError');

require_once 'vendor/autoload.php';
require_once 'config.php';

if (defined('_PROFILING_') && is_callable('xhprof_enable')) {
    xhprof_enable();
}

require_once 'sqlfuncs.php';
require_once 'miscfuncs.php';
require_once 'sessionfuncs.php';
require_once 'form_funcs.php';
require_once 'translator.php';
require_once 'settings.php';
require_once 'memory.php';
require_once 'form_config.php';
require_once 'list_config.php';
require_once 'list.php';

initDbConnection();
sesVerifySession(false);

$strFunc = getPostOrQuery('func', '');

switch ($strFunc) {
case 'get_company':
case 'get_company_contact':
case 'get_product':
case 'get_invoice':
case 'get_invoice_row':
case 'get_base':
case 'get_print_template':
case 'get_invoice_state':
case 'get_invoice_type':
case 'get_row_type':
case 'get_print_template':
case 'get_company':
case 'get_session_type':
case 'get_delivery_terms':
case 'get_delivery_method':
case 'get_default_value':
case 'get_attachment':
case 'get_send_api_config':
    printJSONRecord(substr($strFunc, 4));
    break;
case 'get_user':
    printJSONRecord('users');
    break;

case 'put_company':
case 'put_product':
case 'put_invoice':
case 'put_base':
case 'put_print_template':
case 'put_invoice_state':
case 'put_invoice_type':
case 'put_row_type':
case 'put_print_template':
case 'put_user':
case 'put_session_type':
case 'put_delivery_terms':
case 'put_delivery_method':
case 'put_default_value':
case 'put_attachment':
case 'put_invoice_attachment':
    saveJSONRecord(substr($strFunc, 4), '');
    break;

case 'delete_invoice_row':
case 'delete_default_value':
case 'delete_send_api_config':
case 'delete_attachment':
case 'delete_invoice_attachment':
    deleteJSONRecord(substr($strFunc, 7));
    break;

case 'put_send_api_config':
    saveJSONRecord(substr($strFunc, 4), 'base_id');
    break;
case 'get_send_api_configs':
    printJSONRecords('send_api_config', 'base_id', 'name');
    break;

case 'session_type':
case 'user':
    if (!sesAdminAccess()) {
        http_response_code(403);
        break;
    }
    saveJSONRecord($strFunc, '');
    break;

case 'get_companies':
    printJSONRecords('company', '', 'company_name');
    break;

case 'get_company_contacts':
    printJSONRecords('company_contact', 'company_id', 'contact_person');
    break;

case 'delete_company_contact':
    deleteJSONRecord('company_contact');
    break;

case 'put_company_contact':
    saveJSONRecord('company_contact', 'company_id');
    break;

case 'get_products':
    printJSONRecords('product', '', 'product_name');
    break;

case 'get_row_types':
    printJSONRecords('row_type', '', 'order_no');
    break;

case 'get_invoice_rows':
    printJSONRecords('invoice_row', 'invoice_id', 'order_no');
    break;

case 'put_invoice_row':
    saveJSONRecord('invoice_row', 'invoice_id');
    break;

case 'get_custom_prices':
    $customPrice = getCustomPriceSettings(
        getPostOrQuery('companyId')
    );
    header('Content-Type: application/json');
    echo createResponse($customPrice);
    break;

case 'put_custom_prices':
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }
    $data = json_decode(file_get_contents('php://input'), true);
    if (!$data) {
        http_response_code(400);
        return;
    }
    setCustomPriceSettings(
        $data['company_id'],
        $data['discount'],
        $data['multiplier'],
        dateConvYmd2DBDate($data['valid_until'])
    );
    header('Content-Type: application/json');
    echo createResponse(['status' => 'ok']);
    break;

case 'delete_custom_prices':
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }
    $data = json_decode(file_get_contents('php://input'), true);
    if (!$data) {
        http_response_code(400);
        return;
    }
    deleteCustomPriceSettings($data['company_id']);
    header('Content-Type: application/json');
    echo createResponse(['status' => 'ok']);
    break;

case 'get_custom_price':
    $customPrice = getCustomPrice(
        getPostOrQuery('company_id'),
        getPostOrQuery('product_id')
    );
    header('Content-Type: application/json');
    echo createResponse($customPrice);
    break;

case 'put_custom_price':
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }
    $data = json_decode(file_get_contents('php://input'), true);
    if (!$data) {
        http_response_code(400);
        return;
    }
    $unitPrice = (float)$data['unit_price'];
    setCustomPrice(
        $data['company_id'],
        $data['product_id'],
        $unitPrice
    );
    header('Content-Type: application/json');
    echo createResponse(
        [
            'status' => 'ok',
            'unit_price' => $unitPrice
        ]
    );
    break;

case 'delete_custom_price':
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }
    $data = json_decode(file_get_contents('php://input'), true);
    if (!$data) {
        http_response_code(400);
        return;
    }
    deleteCustomPrice($data['company_id'], $data['product_id']);
    $product = getProduct($data['product_id']);
    $unitPrice = $product['unit_price'];
    if ($unitPrice) {
        $customPrice = getCustomPriceSettings($data['company_id']);
        if ($customPrice && $customPrice['valid']) {
            $unitPrice -= $unitPrice * $customPrice['discount'] / 100;
            $unitPrice *= $customPrice['multiplier'];
        }
    }
    header('Content-Type: application/json');
    echo createResponse(
        [
            'status' => 'ok',
            'unit_price' => $unitPrice
        ]
    );
    break;

case 'add_reminder_fees':
    include 'add_reminder_fees.php';
    $invoiceId = getPostOrQuery('id', 0);
    $errors = addReminderFees($invoiceId);
    if ($errors) {
        $ret = ['status' => 'error', 'errors' => $errors];
    } else {
        $ret = ['status' => 'ok'];
    }
    header('Content-Type: application/json');
    echo createResponse($ret);
    break;

case 'get_invoice_defaults':
    $baseId = getPostOrQuery('base_id', 0);
    $companyId = getPostOrQuery('company_id', 0);
    $invoiceId = getPostOrQuery('id', 0);
    $invoiceDate = getPostOrQuery('invoice_date', date('Y-m-d'));
    $intervalType = getPostOrQuery('interval_type', 0);
    $invoiceNumber = getPostOrQuery('invoice_no', 0);

    $defaults = getInvoiceDefaults(
        $invoiceId, $baseId, $companyId, $invoiceDate, $intervalType, $invoiceNumber
    );

    header('Content-Type: application/json');
    echo createResponse($defaults);
    break;

case 'get_table_columns':
    $table = getPostOrQuery('table', '');
    if (!$table) {
        http_response_code(400);
        break;
    }
    if (!sesAdminAccess() && 'account_statement' !== $table) {
        http_response_code(403);
        break;
    }
    // account_statement is a pseudo table for account statement "import"
    if ($table == 'account_statement') {
        header('Content-Type: application/json');
        echo '{"columns":';
        echo createResponse(
            [
                [
                    'id' => 'date',
                    'name' => Translator::translate('ImportStatementPaymentDate')
                ],
                [
                    'id' => 'amount',
                    'name' => Translator::translate('ImportStatementAmount')
                ],
                [
                    'id' => 'refnr',
                    'name' => Translator::translate('ImportStatementRefNr')
                ],
                [
                    'id' => 'correction',
                    'name' => Translator::translate('ImportStatementCorrectionRow')
                ]
            ]
        );
        echo "\n}";
        break;
    }

    if (!tableNameValid($table)) {
        http_response_code(400);
        die('Invalid table name');
    }

    header('Content-Type: application/json');
    echo '{"columns":[';
    $res = dbQueryCheck("select * from {prefix}$table where 1=2");
    $field_count = mysqli_num_fields($res);
    for ($i = 0; $i < $field_count; $i ++) {
        $field_def = mysqli_fetch_field($res);
        if ($i == 0) {
            echo "\n";
        } else {
            echo ",\n";
        }
        echo createResponse(['name' => $field_def->name]);
    }
    if ('company' === $table || 'company_contact' === $table) {
        echo ",\n";
        echo createResponse(['name' => 'tags']);
    } elseif ('custom_price_map' === $table) {
        echo ",\n";
        echo createResponse(['name' => 'company_id']);
    }
    echo "\n]}";
    break;

case 'get_import_preview':
    $table = getPostOrQuery('table', '');
    if ($table == 'account_statement') {
        include 'import_statement.php';
        $import = new ImportStatement();
    } else {
        if (!sesAdminAccess()) {
            http_response_code(403);
            break;
        }
        include 'import.php';
        $import = new ImportFile();
    }
    $import->createImportPreview();
    break;

case 'get_list':
    $listFunc = getPostOrQuery('listfunc', '');

    $strList = getPostOrQuery('table', '');
    if (!$strList) {
        http_response_code(400);
        die('Table must be defined');
    }

    $tableId = getPostOrQuery('tableid', '');

    $listConfig = getListConfig($strList);
    if (!$listConfig) {
        http_response_code(400);
        die('Invalid table name');
    }

    $startRow = intval(getPostOrQuery('start', -1));
    $rowCount = intval(getPostOrQuery('length', -1));
    $sort = [];
    $columns = getPostOrQuery('columns', []);
    if ($orderCols = getPostOrQuery('order', [])) {
        foreach ($orderCols as $orderCol) {
            if (!isset($orderCol['column'])) {
                continue;
            }
            $sortColumn = $orderCol['column'];
            $sortDir = $orderCol['dir'];
            $sort[] = [
                'column' => intval($sortColumn),
                'direction' => $sortDir === 'desc' ? 'desc' : 'asc'
            ];
        }
    }
    $search = getPostOrQuery('search');
    $searchId = getPostOrQuery('searchId');
    $filter = empty($search['value']) ? '' : $search['value'];
    $query = json_decode(getPostOrQuery('query', '{}'), true);
    $companyId = 'product' === $strList ? getPostOrQuery('company', null) : null;

    header('Content-Type: application/json');
    echo createJSONList(
        $listFunc, $strList, $startRow, $rowCount, $sort, $filter, $query,
        intval(getPostOrQuery('draw', 1)), $tableId, $companyId,
        $searchId ? intval($searchId) : null
    );
    Memory::set(
        $tableId,
        compact(
            'strList', 'startRow', 'rowCount', 'sort', 'filter', 'where'
        )
    );
    break;

case 'get_invoice_total_sum':
    $search = getPostOrQuery('searchId');
    $query = json_decode(getPostOrQuery('query', '{}'), true);
    header('Content-Type: application/json');
    $totals = getInvoiceListTotal(
        $query,
        $search ? intval($search) : null
    );
    echo createResponse($totals);
    break;

case 'get_selectlist':
    $table = getPostOrQuery('table', '');
    if (!$table) {
        http_response_code(400);
        break;
    }

    if (!tableNameValid($table)) {
        http_response_code(400);
        die('Invalid table name');
    }

    $pageLen = intval(getPostOrQuery('pagelen', 10));
    $page = intval(getPostOrQuery('page', 1)) - 1;
    $q = getPostOrQuery('q', []);
    $filter = $q['term'] ?? '';
    $sort = getPostOrQuery('sort', '');
    $id = getPostOrQuery('id', '');
    $filterType = getPostOrQuery('type', '');

    header('Content-Type: application/json');
    echo json_encode(
        createJSONSelectList(
            $table, $page * $pageLen, $pageLen, $filter, $filterType, $sort, $id
        )
    );
    break;

case 'update_multiple':
    header('Content-Type: application/json');
    echo updateMultipleRows();
    break;

case 'update_row_order':
    header('Content-Type: application/json');
    echo updateRowOrder();
    break;

case 'update_stock_balance':
    if (!sesWriteAccess()) {
        http_response_code(403);
        break;
    }
    $productId = getPostOrQuery('product_id', 0);
    $change = getPostOrQuery('stock_balance_change', 0);
    $desc = getPostOrQuery('stock_balance_change_desc', '');
    header('Content-Type: application/json');
    echo updateStockBalance($productId, $change, $desc);
    break;

case 'get_stock_balance_rows':
    $productId = getPostOrQuery('product_id', 0);
    if (!$productId) {
        break;
    }
    $rows = dbParamQuery(
        <<<EOT
SELECT l.time, u.name, l.stock_change, l.description FROM {prefix}stock_balance_log l
INNER JOIN {prefix}users u ON l.user_id=u.id WHERE product_id=? ORDER BY time DESC
EOT
        ,
        [$productId]
    );
    $html = '';
    foreach ($rows as $row) {
        ?>
<tr>
    <td><?php echo dateConvDBTimestamp2DateTime($row['time'])?></td>
    <td><?php echo $row['name']?></td>
    <td><?php echo miscRound2Decim($row['stock_change'])?></td>
    <td><?php echo $row['description']?></td>
</tr>
        <?php
    }
    break;

case 'get_send_api_services':
    header('Content-Type: application/json');
    echo getSendApiServices(getPostOrQuery('invoice_id'), getPostOrQuery('base_id'));
    break;

case 'add_invoice_attachment':
    if (!sesWriteAccess()) {
        http_response_code(403);
        break;
    }
    addInvoiceAttachment();
    break;

case 'get_invoice_attachments':
    printJSONRecords('invoice_attachment', 'invoice_id', 'order_no');
    break;

case 'get_update_info':
    include 'updater.php';
    $updater = new Updater();
    $res = $updater->checkForUpdates();
    echo json_encode($res);
    break;

case 'save_search':
    include_once 'search.php';
    $search = new Search();
    $res = $search->saveSearch(getQuery('name'), $search->getSearchGroups($_GET));
    echo json_encode($res);
    break;

case 'noop':
    // Session keep-alive
    http_response_code(204);
    break;

default:
    http_response_code(404);
}

if (defined('_PROFILING_') && is_callable('xhprof_disable')) {
    $data = xhprof_disable();
    file_put_contents(
        sys_get_temp_dir() . '/' . uniqid() . '.mlinvoice-json.xhprof',
        serialize($data)
    );
}

/**
 * Output a JSON record
 *
 * @param string $table    Table name
 * @param int    $id       Record ID
 * @param array  $warnings Warnings to include in the output
 *
 * @return void
 */
function printJSONRecord($table, $id = false, $warnings = null)
{
    if ($id === false) {
        $id = getPostOrQuery('id', '');
    }
    if ($id) {
        if (substr($table, 0, 8) === '{prefix}') {
            $table = substr($table, 8);
        }
        $select = 'SELECT t.*';
        $from = "FROM {prefix}$table t";
        $where = 'WHERE t.id=?';

        if ($table === 'invoice_row') {
            // Include product name and code
            $select .= ", CASE WHEN LENGTH(p.product_code) = 0 THEN IFNULL(p.product_name, '') ELSE CONCAT_WS(' ', p.product_code, IFNULL(p.product_name, '')) END as product_id_text";
            $from .= ' LEFT OUTER JOIN {prefix}product p on (p.id = t.product_id)';
        }

        $query = "$select $from $where";
        $rows = dbParamQuery($query, [$id]);
        if (!$rows) {
            http_response_code(404);
            return;
        }
        $row = $rows[0];
        $row = convertToApi($row, $table);

        // Include any custom price for a product
        if ($table === 'product' && ($companyId = getPostOrQuery('company_id'))) {
            $customPriceSettings = getCustomPriceSettings($companyId);
            if (empty($customPriceSettings['valid'])) {
                $customPriceSettings = null;
            }
            $customPrice = null;
            if ($customPriceSettings) {
                $customPrice = getCustomPrice($companyId, $id);
                if (!$customPrice) {
                    $unitPrice = $row['unit_price'];
                    $unitPrice -= $unitPrice * $customPriceSettings['discount']
                        / 100;
                    $unitPrice *= $customPriceSettings['multiplier'];
                    $customPrice = [
                        'unit_price' => $unitPrice
                    ];
                }
            }
            $row['custom_price'] = $customPrice ? $customPrice : null;
        }

        header('Content-Type: application/json');
        $row['warnings'] = $warnings;
        echo createResponse($row);
    }
}

/**
 * Output multiple records
 *
 * @param string $table       Table name
 * @param string $parentIdCol Parent ID column name
 * @param string $sort        Sort rules
 *
 * @return void
 */
function printJSONRecords($table, $parentIdCol, $sort)
{
    $select = 'SELECT t.*';
    $from = "FROM {prefix}$table t";

    if ($table == 'invoice_row') {
        // Include product name, product code, product weight and row type name
        $select .= <<<EOT
, CASE WHEN LENGTH(p.product_code) = 0 THEN IFNULL(p.product_name, '')
  ELSE CONCAT_WS(' ', p.product_code, IFNULL(p.product_name, ''))
  END as product_id_text, p.weight as product_weight
EOT;
        $from .= ' LEFT OUTER JOIN {prefix}product p on (p.id = t.product_id)';
        $select .= ', rt.name as type_id_text';
        $from .= ' LEFT OUTER JOIN {prefix}row_type rt on (rt.id = t.type_id)';
    }

    $where = '';
    $params = [];
    $id = getPostOrQuery('parent_id', '');
    if ($id && $parentIdCol) {
        $where .= " WHERE t.$parentIdCol=?";
        $params[] = $id;
    }
    if (!getSetting('show_deleted_records') && 'send_api_config' !== $table
        && 'attachment' !== $table && 'invoice_attachment' !== $table
    ) {
        if ($where) {
            $where .= ' AND t.deleted=0';
        } else {
            $where = ' WHERE t.deleted=0';
        }
    }

    $query = "$select $from $where";
    if ($sort) {
        $query .= " order by $sort";
    }
    $rows = dbParamQuery($query, $params);
    header('Content-Type: application/json');
    echo '{"records":[';
    $first = true;
    foreach ($rows as $row) {
        if ($first) {
            echo "\n";
            $first = false;
        } else {
            echo ",\n";
        }
        $row = convertToApi($row, $table);

        echo createResponse($row);
    }
    echo "\n]}";
}

/**
 * Convert a record to API format
 *
 * @param array  $row   Record row
 * @param string $table Table name
 *
 * @return array
 */
function convertToApi($row, $table)
{
    $form = $table;
    switch ($table) {
    case 'base':
        $row['logo_filedata'] = base64_encode($row['logo_filedata']);
        break;
    case 'attachment':
    case 'invoice_attachment':
        unset($row['filedata']);
        $row['filesize_readable'] = fileSizeToHumanReadable($row['filesize']);
        break;
    case 'company':
        $row['tags'] = getTagsArray('company', $row['id']);
        break;
    case 'company_contact':
        $row['tags'] = getTagsArray('contact', $row['id']);
        break;
    case 'invoice_row':
        $row['type_id_text'] = Translator::translate($row['type_id_text']);
        break;
    case 'users':
        unset($row['password']);
        $form = 'user';
        break;
    }

    $formConfig = getFormConfig($form, '');
    foreach ($formConfig['fields'] as $field) {
        $name = $field['name'];
        if ('INTDATE' === $field['type'] && isset($row[$name])) {
            $row[$name] = dateConvDBDate2Ymd($row[$name]);
        }
    }

    return $row;
}

/**
 * Convert a record from API format
 *
 * @param array  $row   Record row
 * @param string $table Table name
 *
 * @return array
 */
function convertFromApi($row, $table)
{
    return $row;
}

/**
 * Save a record
 *
 * @param string $table         Table name
 * @param string $parentKeyName Parent ID column name
 *
 * @return void
 */
function saveJSONRecord($table, $parentKeyName)
{
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }

    [$contentType] = explode(';', $_SERVER['CONTENT_TYPE']);
    if ($contentType === 'application/json') {
        $data = json_decode(file_get_contents('php://input'), true);
    } else {
        // If we don't have a JSON request, assume we have POST data
        $data = $_POST;
    }
    if (!$data) {
        http_response_code(400);
        return;
    }
    $id = !empty($data['id']) ? (int)$data['id'] : null;
    $new = $id ? false : true;
    unset($data['id']);
    $formConfig = getFormConfig($table, 'json');

    $onPrint = false;
    if (isset($data['onPrint'])) {
        $onPrint = $data['onPrint'];
        unset($data['onPrint']);
    }

    // Allow partial update for invoice attachments. This is a safety check since the
    // partial update mechanism might hide issues with other record types.
    $partial = !$new && 'invoice_attachment' === $table;

    $data = convertFromApi($data, $table);

    $warnings = '';
    try {
        $res = saveFormData(
            $formConfig['table'], $id, $formConfig, $data, $warnings, $parentKeyName,
            $parentKeyName ? $data[$parentKeyName] : false, $onPrint, $partial
        );
    } catch (Exception $e) {
        http_response_code(500);
        header('Content-Type: application/json');
        echo createResponse(['error' => $e->getMessage()]);
        return;
    }
    if ($res !== true) {
        if ($warnings) {
            http_response_code(409);
        }
        header('Content-Type: application/json');
        echo createResponse(['missing_fields' => $res, 'warnings' => $warnings]);
        return;
    }

    if ($new) {
        http_response_code(201);
    }
    printJSONRecord($formConfig['table'], $id, $warnings);
}

/**
 * Delete a record
 *
 * @param string $table Table name
 *
 * @return void
 */
function deleteJSONRecord($table)
{
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }

    $ids = getPostOrQuery('id', '');
    if ($ids) {
        foreach ((array)$ids as $id) {
            deleteRecord("{prefix}$table", $id);
        }
        header('Content-Type: application/json');
        echo createResponse(['status' => 'ok']);
    }
}

/**
 * Update multiple rows
 *
 * @return void
 */
function updateMultipleRows()
{
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }

    $request = json_decode(file_get_contents('php://input'), true);
    if (!$request) {
        http_response_code(400);
        return;
    }

    $strForm = $request['table'];
    $strFunc = '';
    $strList = '';
    $formConfig = getFormConfig($strForm, 'json');

    $warnings = '';
    foreach ($request['ids'] as $id) {
        $id = (int)$id;
        // Set fields anew for every row since saveFormData returns the whole record
        $data = convertFromApi($request['changes'], $request['table']);

        $res = saveFormData(
            '{prefix}' . $request['table'], $id, $formConfig, $data, $warnings,
            false, false, false, true
        );
        if ($res !== true) {
            if ($warnings) {
                http_response_code(409);
            }
            header('Content-Type: application/json');
            return createResponse(['missing_fields' => $res, 'warnings' => $warnings]);
        }
    }

    return createResponse(['status' => 'ok']);
}

/**
 * Update row order based on POST data
 *
 * @return void
 */
function updateRowOrder()
{
    if (!sesWriteAccess()) {
        http_response_code(403);
        return;
    }

    $request = json_decode(file_get_contents('php://input'), true);
    if (!$request) {
        http_response_code(400);
        return;
    }

    foreach ($request['order'] as $id => $orderNo) {
        dbParamQuery(
            "UPDATE {prefix}{$request['table']} SET order_no=? WHERE id=?",
            [$orderNo, $id]
        );
    }

    return createResponse(['status' => 'ok']);
}

/**
 * Get total sums for invoice list
 *
 * @param array $query    Query
 * @param int   $searchId Search ID
 *
 * @return array
 */
function getInvoiceListTotal(array $query, int $searchId = null): array
{
    $listConfig = getListConfig('invoice');
    $queries = createListQuery('invoice', 'invoice', 0, 0, [], '', $query, $searchId);
    $query = $queries['fullQuery'];
    $query
        ->select('sum(it.row_total)')
        ->from(_DB_PREFIX_ . '_' . $listConfig['table'], $listConfig['alias']);
    // Reset grouping and order to get just a single line:
    $query->add('groupBy', [], false);
    $query->add('orderBy', [], false);
    $sum = $query->executeQuery()->fetchOne();
    return [
        'sum' => null !== $sum ? $sum : 0,
        'sum_rounded' => miscRound2Decim($sum, 2, '.', '')
    ];
}

/**
 * Update product stock balance
 *
 * @param int    $productId Product ID
 * @param int    $change    Change in balance
 * @param string $desc      Change description
 *
 * @return void
 */
function updateStockBalance($productId, $change, $desc)
{
    $missing = [];
    if (!$change) {
        $missing[] = Translator::translate('StockBalanceChange');
    }
    if (!$desc) {
        $missing[] = Translator::translate('StockBalanceChangeDescription');
    }

    if ($missing) {
        return createResponse(['missing_fields' => $missing]);
    }

    $rows = dbParamQuery(
        'SELECT stock_balance FROM {prefix}product WHERE id=?',
        [$productId]
    );
    if (!$rows) {
        return createResponse(
            ['status' => 'error', 'errors' => Translator::translate('ErrInvalidValue')]
        );
    }
    $row = $rows[0];
    $balance = $row['stock_balance'];
    $balance += $change;
    dbParamQuery(
        'UPDATE {prefix}product SET stock_balance=? where id=?',
        [$balance, $productId]
    );
    dbParamQuery(
        <<<EOT
INSERT INTO {prefix}stock_balance_log
(user_id, product_id, stock_change, description) VALUES (?, ?, ?, ?)
EOT
        ,
        [
            $_SESSION['sesUSERID'],
            $productId,
            $change,
            $desc
        ]
    );
    return createResponse(['status' => 'ok', 'new_stock_balance' => $balance]);
}

/**
 * Get send API services for the given invoice and base
 *
 * @param int $invoiceId Invoice ID
 * @param int $baseId    Base ID
 *
 * @return string
 */
function getSendApiServices($invoiceId, $baseId)
{
    $templateCandidates = dbParamQuery(
        'SELECT * FROM {prefix}print_template WHERE deleted=0 and type=? and inactive=0 ORDER BY order_no',
        [isOffer($invoiceId) ? 'offer' : 'invoice']
    );
    $templates = [];
    foreach ($templateCandidates as $candidate) {
        $printer = getInvoicePrinter($candidate['filename']);
        if (null === $printer) {
            continue;
        }
        $uses = class_uses($printer);
        if (in_array('InvoicePrinterEmailTrait', $uses)
            || $printer instanceof InvoicePrinterFinvoiceSOAP
            || $printer instanceof InvoicePrinterBlank
        ) {
            continue;
        }
        $templates[] = $candidate;
    }

    $services = [];
    foreach (getSendApiConfigs($baseId) as $config) {
        $urlBase = [
            'func' => 'send_api',
            'invoice_id' => $invoiceId,
            'api_id' => $config['id']
        ];
        $items = [];
        foreach ($templates as $template) {
            $item = $urlBase;
            $item['template_id'] = $template['id'];
            $items[] = [
                'href' => http_build_query($item),
                'name' => Translator::translate($template['name'])
            ];
        }
        $services[] = [
            'name' => $config['name'] ? $config['name'] : Translator::translate($config['method']),
            'items' => $items
        ];
    }

    return createResponse(['services' => $services]);
}

/**
 * Add an attachment to an invoice and return the new record
 *
 * @return string
 */
function addInvoiceAttachment()
{
    $newId = addAttachmentToInvoice(getPostOrQuery('id'), getPostOrQuery('invoice_id'));
    printJSONRecord('invoice_attachment', $newId);

}

/**
 * Handle error without disturbing actual output
 *
 * @param string $errno   Error code number
 * @param string $errstr  Error message
 * @param string $errfile File where error occurred
 * @param string $errline Line number of error
 *
 * @return bool           Always true to cancel default error handling
 */
function handleError($errno, $errstr, $errfile, $errline)
{
    global $phpErrors;
    $phpErrors[] = "[$errno] $errstr at $errfile:$errline";
    return true;
}

/**
 * Format a response as a JSON array
 *
 * @param array $response Response
 *
 * @return string
 */
function createResponse($response)
{
    global $phpErrors;
    if ($phpErrors) {
        $response['php_errors'] = $phpErrors;
    }
    return json_encode($response);
}