From afe67b4c42b0020be85e3698bc8c864c889f98b2 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 19 Nov 2008 14:17:43 +0000 Subject: [PATCH] Additions for foreign item codes support. --- sql/alter2.1.php | 30 +++++++++++++++++++++++++++++- sql/alter2.1.sql | 47 +++++++++++++++++++++++++++++++++++++---------- 2 files changed, 66 insertions(+), 11 deletions(-) diff --git a/sql/alter2.1.php b/sql/alter2.1.php index 8e3a0d54..17fda983 100644 --- a/sql/alter2.1.php +++ b/sql/alter2.1.php @@ -31,6 +31,32 @@ class fa2_1 { return false; } } + // copy all item codes from stock_master into item_codes + $sql = "SELECT `stock_id`,`description` FROM `".$pref."stock_master`"; + $result = db_query($sql); + if (!$result) { + display_error(_("Cannot select stock identificators") + .':
'. db_error_msg($db)); + return false; + } else { + while ($row = db_fetch_assoc($result)) { + $sql = "INSERT IGNORE " + .$pref."item_codes (`item_code`,`stock_id`,`description`) + VALUES('".$row['stock_id']."','".$row['stock_id']."','" + .$row['description']."')"; + $res2 = db_query($sql); + if (!$res2) { + display_error(_("Cannot insert stock id into item_codes") + .':
'. db_error_msg($db)); + return false; + } + } + } + // remove obsolete bank_trans_types table + // (DROP queries are skipped during non-forced upgrade) + $sql = "DROP TABLE IF EXISTS `0_bank_trans_types`"; + db_query($sql); + return true; } // @@ -44,7 +70,9 @@ class fa2_1 { // Test if patch was applied before. // function installed($pref) { - return !check_table($pref, 'item_codes'); + if (check_table($pref, 'item_codes')) return false; + if (check_table($pref, 'company', 'foreign_codes')) return false; + return true; } }; diff --git a/sql/alter2.1.sql b/sql/alter2.1.sql index 7560011f..d2f8d5c7 100644 --- a/sql/alter2.1.sql +++ b/sql/alter2.1.sql @@ -1,3 +1,16 @@ +# +# Database upgrade script Front Accounting +# Source version: 2.0.x +# Target version: 2.1.0 +# +# To make upgrades clean and failsafe: +# * Precede all CREATE TABLE statment with DROP TABLE IF EXISTS +# * Precede all ALTER TABLE statements using ADD column with respective +# ALTER TABLE with DROP column +# * Move all other DROP queries (e.g. removing obsolete tables) to installer +# - they are not executed during non-forced upgrade. +# + DROP TABLE IF EXISTS `0_attachments`; CREATE TABLE `0_attachments` ( @@ -44,24 +57,19 @@ CREATE TABLE `0_recurrent_invoices` ( UNIQUE KEY `description` (`description`) ) TYPE=InnoDB AUTO_INCREMENT=1 ; +ALTER TABLE `0_cust_branch` DROP COLUMN `group_no`; ALTER TABLE `0_cust_branch` ADD `group_no` int(11) NOT NULL default '0'; +ALTER TABLE `0_debtor_trans` DROP COLUMN `dimension_id`; ALTER TABLE `0_debtor_trans` ADD `dimension_id` int(11) NOT NULL default '0'; +ALTER TABLE `0_debtor_trans` DROP COLUMN `dimension2_id`; ALTER TABLE `0_debtor_trans` ADD `dimension2_id` int(11) NOT NULL default '0'; -DROP TABLE IF EXISTS `0_bank_trans_types`; - +ALTER TABLE `0_bank_accounts` DROP COLUMN `id`; ALTER TABLE `0_bank_accounts` DROP PRIMARY KEY; ALTER TABLE `0_bank_accounts` ADD `id` SMALLINT(6) AUTO_INCREMENT PRIMARY KEY; -ALTER TABLE `0_bank_accounts` ADD KEY (`account_code`); - -# Version for any MySQL but usable with digital only gl account codes: -# UPDATE 0_bank_accounts SET id = account_code; -# For any Applicable only to MySQL >=4.0.4 : -UPDATE `0_bank_trans`, `0_bank_accounts` SET 0_bank_trans.bank_act=0_bank_accounts.id - WHERE 0_bank_trans.bank_act=0_bank_accounts.account_code; - +ALTER TABLE `0_users` DROP COLUMN `query_size`; ALTER TABLE `0_users` ADD `query_size` TINYINT(1) DEFAULT '10'; DROP TABLE IF EXISTS `0_sales_pos`; @@ -79,6 +87,7 @@ CREATE TABLE `0_sales_pos` ( INSERT INTO `0_sales_pos` VALUES ('1', 'Default', '1', '1', 'DEF', '1'); +ALTER TABLE `0_users` DROP COLUMN `pos`; ALTER TABLE `0_users` ADD `pos` SMALLINT(6) DEFAULT '1'; DROP TABLE IF EXISTS `0_quick_entries`; @@ -115,7 +124,9 @@ INSERT INTO `0_quick_entry_lines` VALUES ('1', '1', '6600', '1', '0', 0, '0', '0 INSERT INTO `0_quick_entry_lines` VALUES ('2', '2', '6730', '1', '0', 0, '0', '0'); INSERT INTO `0_quick_entry_lines` VALUES ('3', '3', '3000', '1', '0', 0, '0', '0'); +ALTER TABLE `0_users` DROP COLUMN `print_profile`; ALTER TABLE `0_users` ADD `print_profile` VARCHAR(30) DEFAULT '' AFTER `show_hints` ; +ALTER TABLE `0_users` DROP COLUMN `rep_popup`; ALTER TABLE `0_users` ADD `rep_popup` TINYINT(1) DEFAULT '1' AFTER `print_profile` ; DROP TABLE IF EXISTS `0_print_profiles`; @@ -155,3 +166,19 @@ CREATE TABLE `0_printers` ( INSERT INTO `0_printers` VALUES ('1', 'QL500', 'Label printer', 'QL500', 'server', '127', '20'); INSERT INTO `0_printers` VALUES ('2', 'Samsung', 'Main network printer', 'scx4521F', 'server', '515', '5'); INSERT INTO `0_printers` VALUES ('3', 'Local', 'Local print server at user IP', 'lp', '', '515', '10'); + +DROP TABLE IF EXISTS `0_item_codes`; + +CREATE TABLE `0_item_codes` ( + `id` int(11) NOT NULL auto_increment, + `item_code` varchar(20) NOT NULL, + `stock_id` varchar(20) NOT NULL, + `description` varchar(200) NOT NULL default '', + `quantity` double NOT NULL default '1', + `foreign` tinyint(1) NOT NULL default 0, + PRIMARY KEY (`id`), + UNIQUE KEY(`stock_id`, `item_code`) +) TYPE=MyISAM AUTO_INCREMENT=1; + +ALTER TABLE `0_company` DROP COLUMN `foreign_codes`; +ALTER TABLE `0_company` ADD `foreign_codes` TINYINT(1) NOT NULL DEFAULT '0'; -- 2.30.2