function createOpenInvoiceList()
{
$currentDate = date('Ymd');
$res = mysqli_query_check("select count(*) as cnt from {prefix}invoice i where i.deleted = 0 AND i.interval_type > 0 AND i.next_interval_date <= {$currentDate} AND i.archived = 0");
$row = mysqli_fetch_assoc($res);
if ($row['cnt'] > 0) {
createList('open_invoices', 'invoice', 'resultlist_repeating_invoices', $GLOBALS['locLabelInvoicesWithIntervalDue'], "i.interval_type > 0 AND i.next_interval_date <= {$currentDate} AND i.archived = 0", true);
}
$open = '';
$res = mysqli_query_check('SELECT id FROM {prefix}invoice_state WHERE invoice_open=1');
while ($id = mysqli_fetch_value($res)) {
if ($open) {
$open .= ', ';
}
$open .= $id;
}
$unpaid = '';
$res = mysqli_query_check('SELECT id FROM {prefix}invoice_state WHERE invoice_unpaid=1');
while ($id = mysqli_fetch_value($res)) {
if ($unpaid) {
$unpaid .= ', ';
}
$unpaid .= $id;
}
if ($open) {
createList('open_invoices', 'invoice', 'resultlist_open_invoices', $GLOBALS['locLabelOpenInvoices'], "i.state_id IN ({$open}) AND i.archived=0", true);
}
if ($unpaid) {
createList('open_invoices', 'invoice', 'resultlist_unpaid_invoices', $GLOBALS['locLabelUnpaidInvoices'], "i.state_id IN ({$unpaid}) AND i.archived=0", true, true);
}
}
?>
</div>
</div>
</body>
</html>
<?php
return;
}
$paymentDays = getPaymentDays($invoiceData['company_id']);
unset($invoiceData['id']);
unset($invoiceData['invoice_no']);
if (!$boolRefund) {
unset($invoiceData['ref_number']);
if (!empty($invoiceData['company_id'])) {
$res = mysqli_param_query('SELECT default_ref_number FROM {prefix}company WHERE id=?', [$invoiceData['company_id']]);
$invoiceData['ref_number'] = mysqli_fetch_value($res);
}
}
$invoiceData['invoice_date'] = date('Ymd');
$invoiceData['due_date'] = date('Ymd', mktime(0, 0, 0, date('m'), date('d') + $paymentDays, date('Y')));
$invoiceData['payment_date'] = null;
$invoiceData['state_id'] = 1;
$invoiceData['archived'] = false;
$invoiceData['refunded_invoice_id'] = $boolRefund ? $intInvoiceId : null;
if ($boolRefund) {
$invoiceData['interval_type'] = 0;
}
switch ($invoiceData['interval_type']) {
// Month
case 2:
$invoiceData['next_interval_date'] = date('Ymd', mktime(0, 0, 0, date('m') + 1, date('d'), date('Y')));
function htmlFormElement($strName, $strType, $strValue, $strStyle, $strListQuery, $strMode = 'MODIFY', $strParentKey = NULL, $strTitle = "", $astrDefaults = array(), $astrAdditionalAttributes = '', $options = NULL)
{
if ($astrAdditionalAttributes) {
$astrAdditionalAttributes = " {$astrAdditionalAttributes}";
}
$strFormElement = '';
$readOnly = $strMode == 'MODIFY' ? '' : ' readonly="readonly"';
$disabled = $strMode == 'MODIFY' ? '' : ' disabled="disabled"';
switch ($strType) {
case 'TEXT':
if (strstr($strStyle, 'hasDateRangePicker')) {
$autocomplete = ' autocomplete="off"';
} else {
$autocomplete = '';
}
$strFormElement = "<input type=\"text\" class=\"{$strStyle}\"{$autocomplete} " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars($strValue) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
break;
case 'PASSWD':
$strFormElement = "<input type=\"password\" class=\"{$strStyle}\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"\"{$astrAdditionalAttributes}{$readOnly}>\n";
break;
case 'CHECK':
$strValue = $strValue ? 'checked' : '';
$strFormElement = "<input type=\"checkbox\" id=\"{$strName}\" name=\"{$strName}\" value=\"1\" " . htmlspecialchars($strValue) . "{$astrAdditionalAttributes}{$disabled}>\n";
break;
case 'RADIO':
$strChecked = $strValue ? 'checked' : '';
$strFormElement = "<input type=\"radio\" id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars($strValue) . "\"{$astrAdditionalAttributes}{$disabled}>\n";
break;
case 'INT':
$hideZero = FALSE;
if (strstr($strStyle, ' hidezerovalue')) {
$strStyle = str_replace(' hidezerovalue', '', $strStyle);
$hideZero = TRUE;
}
if ($hideZero && $strValue == 0) {
$strValue = '';
}
$strFormElement = "<input type=\"text\" class=\"{$strStyle}\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars($strValue) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
break;
case 'INTDATE':
$strFormElement = "<input type=\"text\" class=\"{$strStyle} hasCalendar\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars($strValue) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
break;
case 'HID_INT':
$strFormElement = "<input type=\"hidden\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars($strValue) . "\">\n";
break;
case 'AREA':
$strFormElement = "<textarea rows=\"24\" cols=\"80\" class=\"" . $strStyle . "\" " . "id=\"" . $strName . "\" name=\"" . $strName . "\"{$astrAdditionalAttributes}{$readOnly}>" . $strValue . "</textarea>\n";
break;
case 'RESULT':
$strListQuery = str_replace("_ID_", $strValue, $strListQuery);
$strFormElement = htmlspecialchars(mysqli_fetch_value(mysqli_query_check($strListQuery))) . "\n";
break;
case 'LIST':
$translate = false;
if (strstr($strStyle, ' translated')) {
$translate = true;
$strStyle = str_replace(' translated', '', $strStyle);
}
if ($strMode == "MODIFY") {
$strFormElement = htmlSQLListBox($strName, $strListQuery, $strValue, $strStyle, false, $astrAdditionalAttributes, $translate);
} else {
$strFormElement = "<input type=\"text\" class=\"{$strStyle}\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars(getSQLListBoxSelectedValue($strListQuery, $strValue, $translate)) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
}
break;
case 'SEARCHLIST':
if ($strMode == "MODIFY") {
$showEmpty = <<<EOT
if (page == 1 && data.filter == '') {
records.unshift({id: '', text: '-'});
}
EOT;
if (strstr($strStyle, ' noemptyvalue')) {
$strStyle = str_replace(' noemptyvalue', '', $strStyle);
$showEmpty = '';
}
$strValue = htmlspecialchars($strValue);
$onchange = $astrAdditionalAttributes ? ".on(\"change\", {$astrAdditionalAttributes})" : '';
$strFormElement = <<<EOT
<input type="hidden" class="{$strStyle}" id="{$strName}" name="{$strName}" value="{$strValue}"/>
<script type="text/javascript">
\$(document).ready(function() {
\$("#{$strName}").select2({
placeholder: "",
ajax: {
url: "json.php?func=get_selectlist&{$strListQuery}",
dataType: 'json',
quietMillis: 200,
data: function (term, page) { // page is the one-based page number tracked by Select2
return {
q: term, //search term
pagelen: 50, // page size
page: page, // page number
};
},
results: function (data, page) {
var records = data.records;
{$showEmpty}
return {results: records, more: data.moreAvailable};
}
},
initSelection: function(element, callback) {
var id = \$(element).val();
if (id !== "") {
\$.ajax("json.php?func=get_selectlist&{$strListQuery}&id=" + id, {
dataType: "json"
}).done(function(data) { callback(data.records[0]); });
}
},
dropdownCssClass: "bigdrop",
dropdownAutoWidth: true,
escapeMarkup: function (m) { return m; },
width: "element"
}){$onchange}
});
</script>
EOT;
} else {
$strFormElement = "<input type=\"text\" class=\"{$strStyle}\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars(getSQLListBoxSelectedValue($strListQuery, $strValue, $translate)) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
}
break;
case 'SELECT':
$translate = false;
if (strstr($strStyle, ' translated')) {
$translate = true;
$strStyle = str_replace(' translated', '', $strStyle);
}
if ($strMode == "MODIFY") {
$strFormElement = htmlListBox($strName, $options, $strValue, $strStyle, false, $astrAdditionalAttributes, $translate);
} else {
$strFormElement = "<input type=\"text\" class=\"{$strStyle}\" " . "id=\"{$strName}\" name=\"{$strName}\" value=\"" . htmlspecialchars(getListBoxSelectedValue($options, $strValue, $translate)) . "\"{$astrAdditionalAttributes}{$readOnly}>\n";
}
break;
case 'BUTTON':
$strListQuery = str_replace("_ID_", $strValue, $strListQuery);
switch ($strStyle) {
case 'custom':
$strListQuery = str_replace("'", "", $strListQuery);
$strHref = $strListQuery;
$strOnClick = "";
break;
case 'redirect':
$strHref = "#";
$strOnClick = "onclick=\"save_record('{$strListQuery}', 'redirect'); return false;\"";
break;
case 'openwindow':
$strHref = "#";
$strOnClick = "onclick=\"save_record('{$strListQuery}', 'openwindow'); return false;\"";
break;
default:
switch ($strStyle) {
case 'tiny':
$strHW = "height=1,width=1,";
break;
case 'small':
$strHW = "height=200,width=200,";
break;
case 'medium':
$strHW = "height=400,width=400,";
break;
case 'large':
$strHW = "height=600,width=600,";
break;
case 'xlarge':
$strHW = "height=800,width=650,";
break;
case 'full':
$strHW = "";
break;
default:
$strHW = "";
break;
}
$strHref = "#";
$strOnClick = "onclick=\"window.open(" . $strListQuery . ",'" . $strHW . "menubar=no,scrollbars=no," . "status=no,toolbar=no'); return false;\"";
break;
}
$strFormElement = "<a class=\"formbuttonlink\" href=\"{$strHref}\" {$strOnClick}{$astrAdditionalAttributes}>" . htmlspecialchars($strTitle) . "</a>\n";
break;
case 'JSBUTTON':
if (strstr($strListQuery, '_ID_') && !$strValue) {
$strFormElement = $GLOBALS['locSaveFirst'];
} else {
if ($strValue) {
$strListQuery = str_replace('_ID_', $strValue, $strListQuery);
}
$strOnClick = "onClick=\"{$strListQuery}\"";
$strFormElement = "<a class=\"formbuttonlink\" href=\"#\" {$strOnClick}{$astrAdditionalAttributes}>" . htmlspecialchars($strTitle) . "</a>\n";
}
break;
case 'IMAGE':
$strListQuery = str_replace("_ID_", $strValue, $strListQuery);
$strFormElement = "<img class=\"{$strStyle}\" src=\"{$strListQuery}\" title=\"" . htmlspecialchars($strTitle) . "\"></div>\n";
break;
default:
$strFormElement = " \n";
}
return $strFormElement;
}
function saveFormData($table, &$primaryKey, &$formElements, &$values, &$warnings, $parentKeyName = '', $parentKey = FALSE)
{
global $dblink;
$missingValues = '';
$strFields = '';
$strInsert = '';
$strUpdateFields = '';
$arrValues = [];
if (!isset($primaryKey) || !$primaryKey) {
unset($values['id']);
}
foreach ($formElements as $elem) {
$type = $elem['type'];
if (in_array($type, ['', 'IFORM', 'RESULT', 'BUTTON', 'JSBUTTON', 'IMAGE', 'ROWSUM', 'NEWLINE', 'LABEL']) || isset($elem['read_only']) && $elem['read_only']) {
continue;
}
$name = $elem['name'];
if (!$elem['allow_null'] && (!isset($values[$name]) || $values[$name] === '')) {
if ($missingValues) {
$missingValues .= ', ';
}
$missingValues .= $elem['label'];
continue;
}
$value = isset($values[$name]) ? $values[$name] : getFormDefaultValue($elem, $parentKey);
if ($type == 'PASSWD' && !$value) {
continue;
}
// Don't save empty password
if (isset($elem['unique']) && $elem['unique']) {
$query = "SELECT * FROM {$table} WHERE deleted=0 AND {$name}=?";
$params = [$value];
if (isset($primaryKey) && $primaryKey) {
$query .= ' AND id!=?';
$params[] = $primaryKey;
}
$res = mysqli_param_query($query, $params);
if (mysqli_fetch_array($res)) {
$warnings = sprintf($GLOBALS['locDuplicateValue'], $elem['label']);
return false;
}
}
if ($strFields) {
$strFields .= ', ';
$strInsert .= ', ';
$strUpdateFields .= ', ';
}
$strFields .= $name;
$fieldPlaceholder = '?';
switch ($type) {
case 'PASSWD':
$fieldPlaceholder = 'md5(?)';
$arrValues[] = $values[$name];
break;
case 'INT':
case 'HID_INT':
case 'SECHID_INT':
$arrValues[] = $value !== '' && $value !== false ? str_replace(',', '.', $value) : ($elem['allow_null'] ? NULL : 0);
break;
case 'LIST':
case 'SEARCHLIST':
$arrValues[] = isset($values[$name]) ? $value !== '' ? str_replace(',', '.', $value) : NULL : NULL;
break;
case 'CHECK':
$arrValues[] = $value ? 1 : 0;
break;
case 'INTDATE':
$arrValues[] = $value ? dateConvDate2DBDate($value) : NULL;
break;
default:
$arrValues[] = $value;
}
$strInsert .= $fieldPlaceholder;
$strUpdateFields .= "{$name}={$fieldPlaceholder}";
}
if ($missingValues) {
return $missingValues;
}
mysqli_query_check('SET AUTOCOMMIT = 0');
mysqli_query_check('BEGIN');
try {
// Special case for invoice rows - update product stock balance
if ($table == '{prefix}invoice_row') {
updateProductStockBalance(isset($primaryKey) ? $primaryKey : null, isset($values['product_id']) ? $values['product_id'] : null, $values['pcs']);
}
if (!isset($primaryKey) || !$primaryKey) {
if ($parentKeyName) {
$strFields .= ", {$parentKeyName}";
$strInsert .= ', ?';
$arrValues[] = $parentKey;
}
$strQuery = "INSERT INTO {$table} ({$strFields}) VALUES ({$strInsert})";
mysqli_param_query($strQuery, $arrValues, 'exception');
$primaryKey = mysqli_insert_id($dblink);
} else {
// Special case for invoice - update product stock balance for all
// invoice rows if the invoice was previously deleted
if ($table == '{prefix}invoice') {
$res = mysqli_param_query('SELECT deleted FROM {prefix}invoice WHERE id=?', [$primaryKey]);
if (mysqli_fetch_value($res)) {
$res = mysqli_param_query('SELECT product_id, pcs FROM {prefix}invoice_row WHERE invoice_id=? AND deleted=0', [$primaryKey]);
while ($row = mysqli_fetch_assoc($res)) {
updateProductStockBalance(null, $row['product_id'], $row['pcs']);
}
}
}
$strQuery = "UPDATE {$table} SET {$strUpdateFields}, deleted=0 WHERE id=?";
$arrValues[] = $primaryKey;
mysqli_param_query($strQuery, $arrValues, 'exception');
}
} catch (Exception $e) {
mysqli_query_check('ROLLBACK');
mysqli_query_check('SET AUTOCOMMIT = 1');
die($e->getMessage());
}
mysqli_query_check('COMMIT');
mysqli_query_check('SET AUTOCOMMIT = 1');
// Special case for invoices - check for duplicate invoice numbers
if ($table == '{prefix}invoice' && isset($values['invoice_no']) && $values['invoice_no']) {
$query = 'SELECT ID FROM {prefix}invoice where deleted=0 AND id!=? AND invoice_no=?';
$params = [$primaryKey, $values['invoice_no']];
if (getSetting('invoice_numbering_per_base')) {
$query .= ' AND base_id=?';
$params[] = $values['base_id'];
}
if (getSetting('invoice_numbering_per_year')) {
$query .= ' AND invoice_date >= ' . date('Y') . '0101';
}
$res = mysqli_param_query($query, $params);
if (mysqli_fetch_assoc($res)) {
$warnings = $GLOBALS['locInvoiceNumberAlreadyInUse'];
}
}
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';
}
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);
}
$printFunc = "window.open('invoice.php?id=_ID_&template={$templateId}&func={$strFunc}'); return false;";
} else {
$printFunc = "window.location = 'invoice.php?id=_ID_&template={$templateId}&func={$strFunc}'; return false;";
}
}
$arr = array('name' => "print{$templateId}", 'label' => isset($GLOBALS["loc{$row['name']}"]) ? $GLOBALS["loc{$row['name']}"] : $row['name'], 'type' => 'JSBUTTON', 'style' => $printStyle, 'listquery' => $printFunc, 'position' => 3, 'allow_null' => true);
if (++$rowNum > $templateFirstCol) {
$arr['position'] = 4;
$printButtons2[] = $arr;
} else {
$printButtons[] = $arr;
}
}
$intRes = mysqli_query_check('SELECT ID from {prefix}base WHERE deleted=0');
if (mysqli_num_rows($intRes) == 1) {
$defaultBase = mysqli_fetch_value($intRes);
} else {
$defaultBase = FALSE;
}
$copyLinkOverride = "copy_invoice.php?func={$strFunc}&list={$strList}&id={$intInvoiceId}";
$updateInvoiceNr = null;
if (sesWriteAccess()) {
if (!getSetting('invoice_add_number') || !getSetting('invoice_add_reference_number')) {
$updateInvoiceNr = '<a class="formbuttonlink" href="#" onclick="' . $getInvoiceNr . '">' . $GLOBALS['locGetInvoiceNr'] . '</a>';
}
}
$addReminderFees = "\$.getJSON('json.php?func=add_reminder_fees&id=' + document.getElementById('record_id').value, function(json) { if (json.errors) { \$('#errormsg').text(json.errors).show() } else { showmsg('{$GLOBALS['locReminderFeesAdded']}'); } init_rows(); }); return false;";
$intervalOptions = array('0' => $GLOBALS['locInvoiceIntervalNone'], '2' => $GLOBALS['locInvoiceIntervalMonth'], '3' => $GLOBALS['locInvoiceIntervalYear']);
$astrFormElements = array(array('name' => 'base_id', 'label' => $GLOBALS['locBiller'], 'type' => 'LIST', 'style' => 'medium linked', 'listquery' => 'SELECT id, name FROM {prefix}base WHERE deleted=0', 'position' => 1, 'default' => $defaultBase), array('name' => 'name', 'label' => $GLOBALS['locInvName'], 'type' => 'TEXT', 'style' => 'medium', 'position' => 2, 'allow_null' => true), array('name' => 'company_id', 'label' => $GLOBALS['locPayer'], 'type' => 'SEARCHLIST', 'style' => 'medium linked', 'listquery' => "table=company&sort=company_name,company_id", 'position' => 1, 'allow_null' => true, 'attached_elem' => $addCompanyCode, 'elem_attributes' => $companyOnChange), array('name' => 'reference', 'label' => $GLOBALS['locClientsReference'], 'type' => 'TEXT', 'style' => 'medium', 'position' => 2, 'allow_null' => true), array('name' => 'invoice_no', 'label' => $GLOBALS['locInvoiceNumber'], 'type' => 'INT', 'style' => 'medium hidezerovalue', 'position' => 1, 'default' => null, 'allow_null' => true), array('name' => 'ref_number', 'label' => $GLOBALS['locReferenceNumber'], 'type' => 'TEXT', 'style' => 'medium hidezerovalue', 'position' => 2, 'default' => null, 'attached_elem' => $updateInvoiceNr, 'allow_null' => true), array('name' => 'invoice_date', 'label' => $GLOBALS['locInvDate'], 'type' => 'INTDATE', 'style' => 'date', 'position' => 1, 'default' => 'DATE_NOW'), array('name' => 'due_date', 'label' => $GLOBALS['locDueDate'], 'type' => 'INTDATE', 'style' => 'date', 'position' => 2, 'default' => 'DATE_NOW+' . getSetting('invoice_payment_days'), 'attached_elem' => $updateDates), array('name' => 'interval_type', 'label' => $GLOBALS['locInvoiceIntervalType'], 'type' => 'SELECT', 'style' => 'medium', 'position' => 1, 'options' => $intervalOptions, 'default' => '0', 'allow_null' => true), array('name' => 'next_interval_date', 'label' => $GLOBALS['locInvoiceNextIntervalDate'], 'type' => 'INTDATE', 'style' => 'date', 'position' => 2, 'default' => '', 'allow_null' => true), array('name' => 'state_id', 'label' => $GLOBALS['locStatus'], 'type' => 'LIST', 'style' => 'medium translated', 'listquery' => 'SELECT id, name FROM {prefix}invoice_state WHERE deleted=0 ORDER BY order_no', 'position' => 1, 'default' => 1), array('name' => 'payment_date', 'label' => $GLOBALS['locPayDate'], 'type' => 'INTDATE', 'style' => 'date', 'position' => 2, 'allow_null' => true, 'attached_elem' => $markPaidTodayButton, 'elem_attributes' => 'onchange="' . $markPaidTodayEvent . '"'), array('name' => 'delivery_terms_id', 'label' => $GLOBALS['locDeliveryTerms'], 'type' => 'LIST', 'style' => 'medium', 'listquery' => 'SELECT id, name FROM {prefix}delivery_terms WHERE deleted=0 ORDER BY order_no;', 'position' => 1, 'default' => null, 'allow_null' => true), array('name' => 'delivery_method_id', 'label' => $GLOBALS['locDeliveryMethod'], 'type' => 'LIST', 'style' => 'medium', 'listquery' => 'SELECT id, name FROM {prefix}delivery_method WHERE deleted=0 ORDER BY order_no;', 'position' => 2, 'default' => null, 'allow_null' => true), array('name' => 'archived', 'label' => $GLOBALS['locArchived'], 'type' => 'CHECK', 'style' => 'medium', 'position' => 1, 'default' => 0, 'allow_null' => true), array('name' => 'info', 'label' => $GLOBALS['locVisibleInfo'], 'type' => 'AREA', 'style' => 'medium', 'position' => 1, 'allow_null' => true), array('name' => 'internal_info', 'label' => $GLOBALS['locInternalInfo'], 'type' => 'AREA', 'style' => 'medium', 'position' => 2, 'allow_null' => true), !sesWriteAccess() ? array('name' => 'refundinvoice', 'label' => '', 'type' => 'FILLER', 'position' => 1) : array('name' => 'refundinvoice', 'label' => $GLOBALS['locRefundInvoice'], 'type' => 'BUTTON', 'style' => 'redirect', 'listquery' => "copy_invoice.php?func={$strFunc}&list={$strList}&id=_ID_&refund=1", 'position' => 1, 'default' => FALSE, 'allow_null' => true), $arrRefundedInvoice, isset($printButtons[0]) ? $printButtons[0] : array(), isset($printButtons2[0]) ? $printButtons2[0] : array(), !sesWriteAccess() ? array('name' => 'addreminderfees', 'label' => '', 'type' => 'FILLER', 'position' => 1) : array('name' => 'addreminderfees', 'label' => $GLOBALS['locAddReminderFees'], 'type' => 'JSBUTTON', 'style' => 'redirect', 'listquery' => $addReminderFees, 'position' => 1, 'default' => FALSE, 'allow_null' => true), $arrRefundingInvoice, isset($printButtons[1]) ? $printButtons[1] : array(), isset($printButtons2[1]) ? $printButtons2[1] : array());
for ($i = 2; $i < count($printButtons); $i++) {
$astrFormElements[] = $printButtons[$i];
