2 /**********************************************************************
3 Copyright (C) FrontAccounting, LLC.
4 Released under the terms of the GNU General Public License, GPL,
5 as published by the Free Software Foundation, either version 3
6 of the License, or (at your option) any later version.
7 This program is distributed in the hope that it will be useful,
8 but WITHOUT ANY WARRANTY; without even the implied warranty of
9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
11 ***********************************************************************/
13 class fa2_4 extends fa_patch {
14 var $previous = '2.3rc'; // applicable database version
15 var $version = '2.4.1'; // version installed
17 var $sql = 'alter2.4.sql';
19 var $max_upgrade_time = 900; // table recoding is really long process
21 function __construct() {
22 parent::__construct();
23 $this->description = _('Upgrade from version 2.3 to 2.4');
27 Shows parameters to be selected before upgrade (if any)
29 function show_params($comp)
31 display_note(_('Set optimal parameters and start upgrade:'));
32 start_table(TABLESTYLE);
34 collations_list_row(_('Text collation optimization:'), 'collation', substr($_SESSION['language']->code, 0, 2));
41 Fetches selected upgrade parameters.
45 $this->collation = get_post('collation');
50 // Install procedure. All additional changes
51 // not included in sql file should go here.
53 function install($company, $force=false)
55 global $db_version, $db_connections;
57 $pref = $db_connections[$company]['tbpref'];
59 if (get_company_pref('grn_clearing_act') === null) { // available form 2.3.1, can be not defined on pre-2.4 installations
60 set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0);
62 if (get_company_pref('default_quote_valid_days') === null) { // new in 2.3.23 installations
63 set_company_pref('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, 30);
65 if (get_company_pref('bcc_email') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
66 set_company_pref('bcc_email', 'setup.company', 'varchar', 100, '');
68 if (get_company_pref('alternative_tax_include_on_docs') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
69 set_company_pref('alternative_tax_include_on_docs', 'setup.company', 'tinyint', 1, '0');
71 if (get_company_pref('suppress_tax_rates') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
72 set_company_pref('suppress_tax_rates', 'setup.company', 'tinyint', 1, '0');
74 if (get_company_pref('company_logo_report') === null) { // available from 2.4.2, during updates
75 set_company_pref('company_logo_report', 'setup.company', 'tinyint', 1, '0');
77 if (get_company_pref('print_dialog_direct') === null) { // available from 2.4.5, during updates
78 set_company_pref('print_dialog_direct', 'setup.company', 'tinyint', 1, '0');
80 if (get_company_pref('barcodes_on_stock') === null) { // available from 2.4.3, during updates
81 set_company_pref('barcodes_on_stock', 'setup.company', 'tinyint', 1, '0');
83 if (get_company_pref('ref_no_auto_increase') === null) { // available from 2.4.4, during updates
84 set_company_pref('ref_no_auto_increase', 'setup.company', 'tinyint', 1, '0');
86 if (get_company_pref('shortname_name_in_list') === null) { // available from 2.4.2, during updates
87 set_company_pref('shortname_name_in_list', 'setup.company', 'tinyint', 1, '0');
90 $result = $this->update_workorders() && $this->update_grn_rates() && $this->switch_database_to_utf($pref);
93 $result = $this->do_cleanup();
96 $db_connections[$company]['collation'] = $this->collation;
97 if (write_config_db())
98 return $this->log_error(_("Cannot update config_db.php file."));
101 $sec_updates = array(
102 'SA_SETUPCOMPANY' => array(
103 'SA_ASSET', 'SA_ASSETCATEGORY', 'SA_ASSETCLASS',
104 'SA_ASSETSTRANSVIEW','SA_ASSETTRANSFER', 'SA_ASSETDISPOSAL',
105 'SA_DEPRECIATION', 'SA_ASSETSANALYTIC'),
107 $result = $this->update_security_roles($sec_updates);
113 // optional procedure done after upgrade fail, before backup is restored
115 function post_fail($company)
117 $pref = $this->companies[$company]['tbpref'];
118 db_query("DROP TABLE IF EXISTS " . $pref . 'wo_costing');
119 db_query("DROP TABLE IF EXISTS " . $pref . 'stock_fa_class');
120 db_query("DELETE FROM ".$pref."sys_prefs
122 'gl_closing_date', 'deferred_income_act', 'no_zero_lines_amount', 'accounts_alpha',
123 'tax_algorithm', 'grn_clearing_act', 'default_receival_required',
124 'default_quote_valid_days', 'no_zero_lines_amount', 'show_po_item_codes', 'accounts_alpha',
125 'loc_notification', 'print_invoice_no', 'allow_negative_prices', 'print_item_images_on_quote',
126 'bcc_email', 'alternative_tax_include_on_docs', 'suppress_tax_rates', 'company_logo_report',
127 'barcodes_on_stock', print_dialog_direct', 'ref_no_auto_increase')");
130 function update_workorders()
134 $sql = "SELECT DISTINCT type, type_no, tran_date, person_id FROM ".TB_PREF."gl_trans WHERE `type`=".ST_WORKORDER
135 ." AND person_type_id=1";
136 $res = db_query($sql);
138 return $this->log_error(sprintf(_("Cannot update work orders costs:\n%s"), db_error_msg($db)));
140 while ($row = db_fetch($res))
142 $journal_id = get_next_trans_no(ST_JOURNAL);
144 $sql1 = "UPDATE ".TB_PREF."gl_trans SET `type`=".ST_JOURNAL.", type_no={$journal_id},
145 person_type_id=NULL, person_id=0
146 WHERE `type`=".ST_WORKORDER." AND type_no={$row['type_no']} AND tran_date='{$row['tran_date']}'
147 AND person_id='{$row['person_id']}'";
148 if (!db_query($sql1)) return false;
150 $sql2 = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_no)
151 VALUES ({$row['type_no']}, {$row['person_id']}, {$journal_id})";
152 if (!db_query($sql2)) return false;
158 In previous versions FA ignored encoding settings on database/tables, so it depended on server settings,
159 but data stored is encoded in user language encoding. Now we switch to utf8 internal database encoding, while
160 user encoding can be selected independently.
162 To perform safe FA database switch to utf-8 encoding we have to first ensure that all text/char columns
163 have properly set encoding (the same as its content), so the algorithm performed on every table is as follows:
164 . set default table encoding for the table to currently used on client side;
165 . for all text/char column:
166 - suppress autorecoding by change of the type to related binary/blob type
167 - change column to utf8 encodding and selected collation.
168 . change default table encoding to utf8 and selected collation
170 function switch_database_to_utf($pref, $dbg = false) {
172 global $installed_languages, $dflt_lang;
174 $old_encoding = 'latin1'; // default client encoding
176 // uncomment in case of 1071 errors (requires SUPER privileges)
177 // db_query("SET @@global.innodb_large_prefix=1", "Cannot set large prefix");
179 // site default encoding is presumed as encoding for all databases!
180 $lang = array_search_value($dflt_lang, $installed_languages, 'code');
181 $new_encoding = get_mysql_encoding_name(strtoupper($lang['encoding']));
183 $this->log_error(sprintf('Switching database to utf8 encoding from %s', $old_encoding), 'Info');
184 $collation = get_mysql_collation($this->collation);
185 $tsql = "SHOW TABLES LIKE '".($pref=='' ? '' : substr($pref, 0, -1).'\\_')."%'";
186 $tresult = db_query($tsql, "Cannot select all tables with prefix '$pref'");
187 while($tbl = db_fetch($tresult)) {
190 db_query("ALTER TABLE `$table` CONVERT TO CHARACTER SET $old_encoding"); // convert encoding on utf-8 tables
192 // set proper default table encoding for current user language (used on binary->text conversion)
193 db_query("ALTER TABLE `$table` CHARSET $new_encoding");
194 $csql = "SHOW COLUMNS FROM $table";
195 $cresult = db_query($csql, "Cannot select column names for table '$table'");
198 $to_binary = $to_default = $to_utf = array();
199 while($col = db_fetch($cresult)) {
201 $bintype = strtr($col['Type'], array('varchar' => 'varbinary', 'char'=>'varbinary', 'text'=>'blob', 'tinytext'=>'tinyblob'));
203 if ($bintype != $col['Type'])
204 { // this is char/text column, so change encoding to proper encoding
206 $this->log_error(sprintf('%s switched to uft8.', $table.'.'.$col['Field']), 'Debug');
208 $null = $col['Null'] === 'YES' ? ' NULL ' : ' NOT NULL ';
209 $default = $col['Null'] !== 'YES' && isset($col['Default']) ? ' DEFAULT '.db_escape($col['Default']) : '';
211 // to avoid column width multiplication x3 we old->binary->ui->utf column type change instead of column CONVERT
213 $to_binary[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$bintype;
214 $to_default[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$col['Type'].$null.$default;
215 $to_utf[] = "MODIFY COLUMN `".$col['Field']."` ".$col['Type']." COLLATE ".$collation.$null.$default;
219 if(count($to_binary))
221 $sql = "ALTER TABLE `$table` ".implode(',',$to_binary);
223 $sql = "ALTER TABLE `$table` ".implode(',',$to_default);
225 $sql = "ALTER TABLE `$table` ".implode(',',$to_utf);
228 db_query("ALTER TABLE `$table` COLLATE $collation");
230 db_query("ALTER DATABASE COLLATE $collation");
231 $this->log_error(_('Convertion to utf8 done.'), 'Info');
236 function update_grn_rates()
238 $sql = "SELECT grn.id, grn.delivery_date, supp.curr_code
239 FROM ".TB_PREF."grn_batch grn, ".TB_PREF."suppliers supp
240 WHERE supp.supplier_id=grn.supplier_id AND supp.curr_code!='".get_company_pref('curr_default')."'";
241 $result = db_query($sql);
246 $sql = "UPDATE ".TB_PREF."grn_batch SET rate=%s WHERE id=%d";
247 while ($grn = db_fetch($result))
248 db_query(sprintf($sql, get_exchange_rate_from_home_currency($grn['curr_code'], sql2date($grn['delivery_date'])), $grn['id']));
253 function do_cleanup()
257 //remove obsolete and temporary columns.
258 // this have to be done here as db_import rearranges alter query order
260 'tax_groups' => array('tax_shipping'),
261 'tax_group_items' => array('rate'),
262 'budget_trans' => array('type', 'type_no', 'person_id', 'person_type_id', 'memo_'),
263 'cust_branch' => array('contact_name', 'disable_trans'),
264 'stock_moves' => array('discount_percent', 'visible', 'person_id'),
267 foreach($dropcol as $table => $columns)
268 foreach($columns as $col) {
269 if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`") == false) {
270 return $this->log_error(sprintf(_("Cannot drop column in %s table: %s"), $table, db_error_msg($db)));
277 $install = new fa2_4;