X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sql%2Falter2.4.php;h=e6e8b619d8024b8c26b8949d356d149d5abbfdd8;hb=da7df35c61205d0b1af47d286be591b8a3194b0c;hp=898c566ac36c68e76e7f9f34e73d0e3019858f38;hpb=f79fb55ef2dc9405300fe3941941b41667165223;p=fa-stable.git diff --git a/sql/alter2.4.php b/sql/alter2.4.php index 898c566a..e6e8b619 100644 --- a/sql/alter2.4.php +++ b/sql/alter2.4.php @@ -9,49 +9,119 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -class fa2_4 { - var $version = '2.4'; // version installed + +class fa2_4 extends fa_patch { + var $previous = '2.3rc'; // applicable database version + var $version = '2.4.1'; // version installed var $description; var $sql = 'alter2.4.sql'; var $preconf = true; + var $max_upgrade_time = 900; // table recoding is really long process - function fa2_4() { + function __construct() { + parent::__construct(); $this->description = _('Upgrade from version 2.3 to 2.4'); } + /* + Shows parameters to be selected before upgrade (if any) + */ + function show_params($comp) + { + display_note(_('Set optimal parameters and start upgrade:')); + start_table(TABLESTYLE); + start_row(); + collations_list_row(_('Text collation optimization:'), 'collation', substr($_SESSION['language']->code, 0, 2)); + end_row(); + end_table(); + br(); + } + + /* + Fetches selected upgrade parameters. + */ + function prepare() + { + $this->collation = get_post('collation'); + return true; + } + // // Install procedure. All additional changes // not included in sql file should go here. // - function install($company, $force) + function install($company, $force=false) { - global $db_version; - + global $db_version, $db_connections; + + $pref = $db_connections[$company]['tbpref']; + if (get_company_pref('grn_clearing_act') === null) { // available form 2.3.1, can be not defined on pre-2.4 installations set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0); } - if ($this->update_workorders()) -// return update_company_prefs(array('version_id'=>$db_version)); - return true; - } - // - // Checking before install - // - function pre_check($pref, $force) - { - return true; + if (get_company_pref('default_quote_valid_days') === null) { // new in 2.3.23 installations + set_company_pref('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, 30); + } + if (get_company_pref('bcc_email') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations + set_company_pref('bcc_email', 'setup.company', 'varchar', 100, ''); + } + if (get_company_pref('alternative_tax_include_on_docs') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations + set_company_pref('alternative_tax_include_on_docs', 'setup.company', 'tinyint', 1, '0'); + } + if (get_company_pref('suppress_tax_rates') === null) { // available from 2.3.14, can be not defined on pre-2.4 installations + set_company_pref('suppress_tax_rates', 'setup.company', 'tinyint', 1, '0'); + } + if (get_company_pref('company_logo_report') === null) { // available from 2.4.2, during updates + set_company_pref('company_logo_report', 'setup.company', 'tinyint', 1, '0'); + } + if (get_company_pref('barcodes_on_stock') === null) { // available from 2.4.3, during updates + set_company_pref('barcodes_on_stock', 'setup.company', 'tinyint', 1, '0'); + } + if (get_company_pref('ref_no_auto_increase') === null) { // available from 2.4.4, during updates + set_company_pref('ref_no_auto_increase', 'setup.company', 'tinyint', 1, '0'); + } + if (get_company_pref('shortname_name_in_list') === null) { // available from 2.4.2, during updates + set_company_pref('shortname_name_in_list', 'setup.company', 'tinyint', 1, '0'); + } + + $result = $this->update_workorders() && $this->update_grn_rates() && $this->switch_database_to_utf($pref); + + if ($result) + $result = $this->do_cleanup(); + if ($result) + { + $db_connections[$company]['collation'] = $this->collation; + if (write_config_db()) + return $this->log_error(_("Cannot update config_db.php file.")); + } + + $sec_updates = array( + 'SA_SETUPCOMPANY' => array( + 'SA_ASSET', 'SA_ASSETCATEGORY', 'SA_ASSETCLASS', + 'SA_ASSETSTRANSVIEW','SA_ASSETTRANSFER', 'SA_ASSETDISPOSAL', + 'SA_DEPRECIATION', 'SA_ASSETSANALYTIC'), + ); + $result = $this->update_security_roles($sec_updates); + + return $result; } + // - // Test if patch was applied before. + // optional procedure done after upgrade fail, before backup is restored // - function installed($pref) + function post_fail($company) { - $n = 2; // number of patches to be installed - $patchcnt = 0; - - if (!check_table($pref, 'suppliers', 'tax_algorithm')) $patchcnt++; - if (!check_table($pref, 'wo_costing')) $patchcnt++; - return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0); + $pref = $this->companies[$company]['tbpref']; + db_query("DROP TABLE IF EXISTS " . $pref . 'wo_costing'); + db_query("DROP TABLE IF EXISTS " . $pref . 'stock_fa_class'); + db_query("DELETE FROM ".$pref."sys_prefs + WHERE `name` in ( + 'gl_closing_date', 'deferred_income_act', 'no_zero_lines_amount', 'accounts_alpha', + 'tax_algorithm', 'grn_clearing_act', 'default_receival_required', + 'default_quote_valid_days', 'no_zero_lines_amount', 'show_po_item_codes', 'accounts_alpha', + 'loc_notification', 'print_invoice_no', 'allow_negative_prices', 'print_item_images_on_quote', + 'bcc_email', 'alternative_tax_include_on_docs', 'suppress_tax_rates', 'company_logo_report', + 'barcodes_on_stock', 'ref_no_auto_increase')"); } function update_workorders() @@ -59,14 +129,11 @@ class fa2_4 { global $db; $sql = "SELECT DISTINCT type, type_no, tran_date, person_id FROM ".TB_PREF."gl_trans WHERE `type`=".ST_WORKORDER - ." AND person_type_id=1"; + ." AND person_type_id=1"; $res = db_query($sql); if (!$res) - { - display_error("Cannot update work orders costs" - .':
'. db_error_msg($db)); - return false; - } + return $this->log_error(sprintf(_("Cannot update work orders costs:\n%s"), db_error_msg($db))); + while ($row = db_fetch($res)) { $journal_id = get_next_trans_no(ST_JOURNAL); @@ -76,13 +143,132 @@ class fa2_4 { WHERE `type`=".ST_WORKORDER." AND type_no={$row['type_no']} AND tran_date='{$row['tran_date']}' AND person_id='{$row['person_id']}'"; if (!db_query($sql1)) return false; - + $sql2 = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_no) VALUES ({$row['type_no']}, {$row['person_id']}, {$journal_id})"; if (!db_query($sql2)) return false; } return true; } + +/* + In previous versions FA ignored encoding settings on database/tables, so it depended on server settings, + but data stored is encoded in user language encoding. Now we switch to utf8 internal database encoding, while + user encoding can be selected independently. + + To perform safe FA database switch to utf-8 encoding we have to first ensure that all text/char columns + have properly set encoding (the same as its content), so the algorithm performed on every table is as follows: + . set default table encoding for the table to currently used on client side; + . for all text/char column: + - suppress autorecoding by change of the type to related binary/blob type + - change column to utf8 encodding and selected collation. + . change default table encoding to utf8 and selected collation +*/ + function switch_database_to_utf($pref, $dbg = false) { + + global $installed_languages, $dflt_lang; + + $old_encoding = 'latin1'; // default client encoding + + // uncomment in case of 1071 errors (requires SUPER privileges) + // db_query("SET @@global.innodb_large_prefix=1", "Cannot set large prefix"); + + // site default encoding is presumed as encoding for all databases! + $lang = array_search_value($dflt_lang, $installed_languages, 'code'); + $new_encoding = get_mysql_encoding_name(strtoupper($lang['encoding'])); + + $this->log_error(sprintf('Switching database to utf8 encoding from %s', $old_encoding), 'Info'); + $collation = get_mysql_collation($this->collation); + $tsql = "SHOW TABLES LIKE '".($pref=='' ? '' : substr($pref, 0, -1).'\\_')."%'"; + $tresult = db_query($tsql, "Cannot select all tables with prefix '$pref'"); + while($tbl = db_fetch($tresult)) { + $table = $tbl[0]; + + db_query("ALTER TABLE `$table` CONVERT TO CHARACTER SET $old_encoding"); // convert encoding on utf-8 tables + + // set proper default table encoding for current user language (used on binary->text conversion) + db_query("ALTER TABLE `$table` CHARSET $new_encoding"); + $csql = "SHOW COLUMNS FROM $table"; + $cresult = db_query($csql, "Cannot select column names for table '$table'"); + $convert = false; + + $to_binary = $to_default = $to_utf = array(); + while($col = db_fetch($cresult)) { + + $bintype = strtr($col['Type'], array('varchar' => 'varbinary', 'char'=>'varbinary', 'text'=>'blob', 'tinytext'=>'tinyblob')); + + if ($bintype != $col['Type']) + { // this is char/text column, so change encoding to proper encoding + if ($dbg) + $this->log_error(sprintf('%s switched to uft8.', $table.'.'.$col['Field']), 'Debug'); + + $null = $col['Null'] === 'YES' ? ' NULL ' : ' NOT NULL '; + $default = $col['Null'] !== 'YES' && isset($col['Default']) ? ' DEFAULT '.db_escape($col['Default']) : ''; + + // to avoid column width multiplication x3 we old->binary->ui->utf column type change instead of column CONVERT + + $to_binary[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$bintype; + $to_default[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$col['Type'].$null.$default; + $to_utf[] = "MODIFY COLUMN `".$col['Field']."` ".$col['Type']." COLLATE ".$collation.$null.$default; + $convert = true; + } + } + if(count($to_binary)) + { + $sql = "ALTER TABLE `$table` ".implode(',',$to_binary); + db_query($sql); + $sql = "ALTER TABLE `$table` ".implode(',',$to_default); + db_query($sql); + $sql = "ALTER TABLE `$table` ".implode(',',$to_utf); + db_query($sql); + } + db_query("ALTER TABLE `$table` COLLATE $collation"); + } + db_query("ALTER DATABASE COLLATE $collation"); + $this->log_error(_('Convertion to utf8 done.'), 'Info'); + + return true; + } + + function update_grn_rates() + { + $sql = "SELECT grn.id, grn.delivery_date, supp.curr_code + FROM ".TB_PREF."grn_batch grn, ".TB_PREF."suppliers supp + WHERE supp.supplier_id=grn.supplier_id AND supp.curr_code!='".get_company_pref('curr_default')."'"; + $result = db_query($sql); + + if (!$result) + return false; + + $sql = "UPDATE ".TB_PREF."grn_batch SET rate=%s WHERE id=%d"; + while ($grn = db_fetch($result)) + db_query(sprintf($sql, get_exchange_rate_from_home_currency($grn['curr_code'], sql2date($grn['delivery_date'])), $grn['id'])); + + return true; + } + + function do_cleanup() + { + global $db; + + //remove obsolete and temporary columns. + // this have to be done here as db_import rearranges alter query order + $dropcol = array( + 'tax_groups' => array('tax_shipping'), + 'tax_group_items' => array('rate'), + 'budget_trans' => array('type', 'type_no', 'person_id', 'person_type_id', 'memo_'), + 'cust_branch' => array('contact_name', 'disable_trans'), + 'stock_moves' => array('discount_percent', 'visible', 'person_id'), + ); + + foreach($dropcol as $table => $columns) + foreach($columns as $col) { + if (db_query("ALTER TABLE `".TB_PREF."{$table}` DROP `$col`") == false) { + return $this->log_error(sprintf(_("Cannot drop column in %s table: %s"), $table, db_error_msg($db))); + } + } + return true; + } } $install = new fa2_4;