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 var $version = '2.4.0'; // version installed
15 var $sql = 'alter2.4.sql';
19 $this->description = _('Upgrade from version 2.3 to 2.4');
23 // Install procedure. All additional changes
24 // not included in sql file should go here.
26 function install($company, $force=false)
28 global $db_version, $db_connections;
30 $pref = $db_connections[$company]['tbpref'];
32 if (get_company_pref('grn_clearing_act') === null) { // available form 2.3.1, can be not defined on pre-2.4 installations
33 set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0);
35 if (get_company_pref('default_receival_required') === null) { // new in 2.4 installations
36 set_company_pref('default_receival_required', 'glsetup.purchase', 'smallint', 6, 10);
38 if (get_company_pref('default_quote_valid_days') === null) { // new in 2.3.23 installations
39 set_company_pref('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, 30);
41 if (get_company_pref('no_zero_lines_amount') === null) { // new in 2.4 installations
42 set_company_pref('no_zero_lines_amount', 'glsetup.sales', 'tinyint', 1, '1');
45 if (get_company_pref('show_po_item_codes') === null) { // new in 2.4 installations
46 set_company_pref('show_po_item_codes', 'glsetup.purchase', 'tinyint', 1, '0');
49 if (get_company_pref('accounts_alpha') === null) { // new in 2.4 installations
50 set_company_pref('accounts_alpha', 'glsetup.general', 'tinyint', 1, '0');
53 if (get_company_pref('loc_notification') === null) { // new in 2.4 installations
54 set_company_pref('loc_notification', 'glsetup.inventory', 'tinyint', 1, '0');
57 if (get_company_pref('print_invoice_no') === null) { // new in 2.4 installations
58 set_company_pref('print_invoice_no', 'glsetup.sales', 'tinyint', 1, '0');
61 if (get_company_pref('allow_negative_prices') === null) { // new in 2.4 installations
62 set_company_pref('allow_negative_prices', 'glsetup.inventory', 'tinyint', 1, '1');
65 if (get_company_pref('print_item_images_on_quote') === null) { // new in 2.4 installations
66 set_company_pref('print_item_images_on_quote', 'glsetup.inventory', 'tinyint', 1, '0');
69 if (get_company_pref('bcc_email') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
70 set_company_pref('bcc_email', 'setup.company', 'varchar', 100, '');
73 if (get_company_pref('alternative_tax_include_on_docs') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
74 set_company_pref('alternative_tax_include_on_docs', 'setup.company', 'tinyint', 1, '0');
77 if (get_company_pref('suppress_tax_rates') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations
78 set_company_pref('suppress_tax_rates', 'setup.company', 'tinyint', 1, '0');
82 $result = $this->update_workorders() && $this->update_grn_rates() && $this->switch_database_to_utf($pref);
85 $result = $this->do_cleanup();
87 return update_company_prefs(array('version_id'=>$db_version));
90 // Checking before install
92 function pre_check($pref, $force)
98 // optional procedure done after upgrade fail, before backup is restored
100 function post_fail($pref)
102 db_query("DROP TABLE IF EXISTS " . $pref . 'wo_costing');
105 // Test if patch was applied before.
107 function installed($pref)
109 $n = 2; // number of patches to be installed
112 if (!check_table($pref, 'suppliers', 'tax_algorithm')) $patchcnt++;
113 if (!check_table($pref, 'wo_costing')) $patchcnt++;
114 return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0);
117 function update_workorders()
121 $sql = "SELECT DISTINCT type, type_no, tran_date, person_id FROM ".TB_PREF."gl_trans WHERE `type`=".ST_WORKORDER
122 ." AND person_type_id=1";
123 $res = db_query($sql);
126 display_error("Cannot update work orders costs"
127 .':<br>'. db_error_msg($db));
130 while ($row = db_fetch($res))
132 $journal_id = get_next_trans_no(ST_JOURNAL);
134 $sql1 = "UPDATE ".TB_PREF."gl_trans SET `type`=".ST_JOURNAL.", type_no={$journal_id},
135 person_type_id=NULL, person_id=0
136 WHERE `type`=".ST_WORKORDER." AND type_no={$row['type_no']} AND tran_date='{$row['tran_date']}'
137 AND person_id='{$row['person_id']}'";
138 if (!db_query($sql1)) return false;
140 $sql2 = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_no)
141 VALUES ({$row['type_no']}, {$row['person_id']}, {$journal_id})";
142 if (!db_query($sql2)) return false;
148 In previous versions FA ignored encoding settings on database/tables, so it depended on server settings,
149 but data stored is encoded in user language encoding. Now we switch to utf8 internal database encoding, while
150 user encoding can be selected independently.
152 To perform safe FA database switch to utf-8 encoding we have to first ensure that all text/char columns
153 have properly set encoding (the same as its content), so the algorithm performed on every table is as follows:
154 . set default table encoding for the table to currently used on client side;
155 . for all text/char column:
156 - suppress autorecoding by change of the type to related binary/blob type
157 - change column to utf8 encodding and selected collation.
158 . change default table encoding to utf8
160 function switch_database_to_utf($pref, $test = false) {
162 global $installed_languages, $dflt_lang;
164 $old_encoding = 'latin1'; // default client encoding
166 // site default encoding is presumed as encoding for all databases!
167 $lang = array_search_value($dflt_lang, $installed_languages, 'code');
168 $new_encoding = get_mysql_encoding_name(strtoupper($lang['encoding']));
171 error_log('Switching database to utf8 encoding from '.$old_encoding);
172 $collation = get_mysql_collation();
173 $tsql = "SHOW TABLES LIKE '".($pref=='' ? '' : substr($pref, 0, -1).'\\_')."%'";
174 $tresult = db_query($tsql, "Cannot select all tables with prefix '$pref'");
175 while($tbl = db_fetch($tresult)) {
177 // if ($table != '1_chart_master') continue; _vd($table); get_usec(); // fast debug on single table
179 db_query("ALTER TABLE `$table` CONVERT TO CHARACTER SET $old_encoding"); // convert encoding on utf-8 tables
181 // set proper default table encoding for current user language (used on binary->text conversion)
182 db_query("ALTER TABLE `$table` CHARSET $new_encoding");
183 $csql = "SHOW COLUMNS FROM $table";
184 $cresult = db_query($csql, "Cannot select column names for table '$table'");
187 $to_binary = $to_default = $to_utf = array();
188 while($col = db_fetch($cresult)) {
190 $bintype = strtr($col['Type'], array('varchar' => 'varbinary', 'char'=>'varbinary', 'text'=>'blob', 'tinytext'=>'tinyblob'));
192 if ($bintype != $col['Type'])
193 { // this is char/text column, so change encoding to proper encoding
195 error_log($table.'.'.$col['Field']);
197 $null = $col['Null'] === 'YES' ? ' NULL ' : ' NOT NULL ';
198 $default = $col['Null'] !== 'YES' && isset($col['Default']) ? ' DEFAULT '.db_escape($col['Default']) : '';
200 // to avoid column width multiplication x3 we old->binary->ui->utf column type change instead of column CONVERT
202 $to_binary[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$bintype;
203 $to_default[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$col['Type'].$null.$default;
204 $to_utf[] = "MODIFY COLUMN `".$col['Field']."` ".$col['Type']." COLLATE ".$collation.$null.$default;
208 if(count($to_binary))
210 $sql = "ALTER TABLE `$table` ".implode(',',$to_binary);
212 $sql = "ALTER TABLE `$table` ".implode(',',$to_default);
214 $sql = "ALTER TABLE `$table` ".implode(',',$to_utf);
217 db_query("ALTER TABLE `$table` COLLATE $collation");
219 db_query("ALTER DATABASE COLLATE $collation");
221 error_log('Convertion to utf8 done.');
226 function update_grn_rates()
228 $sql = "SELECT grn.id, grn.delivery_date, supp.curr_code
229 FROM ".TB_PREF."grn_batch grn, ".TB_PREF."suppliers supp
230 WHERE supp.supplier_id=grn.supplier_id AND supp.curr_code!='".get_company_pref('curr_default')."'";
231 $result = db_query($sql);
236 $sql = "UPDATE ".TB_PREF."grn_batch SET rate=%s WHERE id=%d";
237 while ($grn = db_fetch($result))
238 db_query(sprintf($sql, get_exchange_rate_from_home_currency($grn['curr_code'], sql2date($grn['delivery_date'])), $grn['id']));
243 function do_cleanup()
246 'tax_group_items' => array('rate'),
247 'budget_trans' => array('type', 'type_no', 'person_id', 'person_type_id', 'memo_'),
250 foreach($dropcol as $table => $columns)
251 foreach($columns as $col) {
252 if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`") == false) {
253 display_error("Cannot drop {$table}.{$col} column:<br>".db_error_msg($db));
260 $install = new fa2_4;