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 }