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