PHP mysqli_param_query函数代码示例

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]);
            }
        }
    }
//.........这里部分代码省略.........

 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'];

//.........这里部分代码省略.........
         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') {

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']) {
//.........这里部分代码省略.........

				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;