X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sql%2Falter2.4.sql;h=f51e1d2cfc59114d1201c17cf6d7a7bcbfc4e961;hb=7e6e0807990447d2977b970c3a0fd28dc9250194;hp=e2bf6278ca48e068b41b182828db9c393f0bee2c;hpb=29a1353791d3b622af39431bb89a42392cc66666;p=fa-stable.git diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index e2bf6278..f51e1d2c 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -32,11 +32,10 @@ UPDATE `0_tax_group_items` tgi SET tgi.tax_shipping=1 WHERE tgi.rate=(SELECT 0_tax_types.rate FROM 0_tax_types, 0_tax_groups WHERE tax_shipping=1 AND tgi.tax_group_id=0_tax_groups.id AND tgi.tax_type_id=0_tax_types.id); -ALTER TABLE `0_tax_groups` DROP COLUMN `tax_shipping`; ALTER TABLE `0_sales_order_details` ADD KEY `stkcode` (`stk_code`); ALTER TABLE `0_purch_order_details` ADD KEY `itemcode` (`item_code`); -ALTER TABLE `0_sys_prefs` CHANGE `value` `value` TEXT NOT NULL; +ALTER TABLE `0_sys_prefs` CHANGE `value` `value` TEXT NOT NULL DEFAULT ''; ALTER TABLE `0_cust_branch` ADD COLUMN `bank_account` varchar(60) DEFAULT NULL AFTER `notes`; ALTER TABLE `0_debtor_trans` ADD COLUMN `tax_included` tinyint(1) unsigned NOT NULL default '0' AFTER `payment_terms`; @@ -115,7 +114,6 @@ ALTER TABLE `0_groups` ENGINE=InnoDB; ALTER TABLE `0_item_codes` ENGINE=InnoDB; ALTER TABLE `0_item_units` ENGINE=InnoDB; ALTER TABLE `0_locations` ENGINE=InnoDB; -ALTER TABLE `0_movement_types` ENGINE=InnoDB; ALTER TABLE `0_payment_terms` ENGINE=InnoDB; ALTER TABLE `0_prices` ENGINE=InnoDB; ALTER TABLE `0_printers` ENGINE=InnoDB; @@ -208,3 +206,114 @@ UPDATE `0_trans_tax_details` reg SET reg.reg_type=0 WHERE reg.trans_type IN(10, 11); +INSERT IGNORE INTO `0_sys_prefs` VALUES + ('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0), + ('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, 30), + ('no_zero_lines_amount', 'glsetup.sales', 'tinyint', 1, '1'), + ('accounts_alpha', 'glsetup.general', 'tinyint', 1, '0'), + ('bcc_email', 'setup.company', 'varchar', 100, ''), + ('alternative_tax_include_on_docs', 'setup.company', 'tinyint', 1, '0'), + ('suppress_tax_rates', 'setup.company', 'tinyint', 1, '0'); + +# stock_moves.visible field is obsolete +# removing obsolete moves for writeoffs +DELETE moves + FROM `0_stock_moves` moves + INNER JOIN (SELECT * FROM `0_stock_moves` WHERE `type`=11 AND `qty`<0) writeoffs ON writeoffs.`trans_no`=moves.`trans_no` AND writeoffs.`type`=11 + WHERE moves.`type`=11; + +# stock_moves.discount_percent field are obsolete + +UPDATE `0_stock_moves` SET + price = price*(1-discount_percent); + +DROP TABLE IF EXISTS `0_movement_types`; + +# change salesman breakpoint meaning to turnover level +UPDATE `0_salesman` + SET `break_pt` = `break_pt`*100.0/`provision` +WHERE `provision` != 0; + +# reference lines +DROP TABLE IF EXISTS `0_reflines`; +CREATE TABLE `0_reflines` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `trans_type` int(11) NOT NULL, + `prefix` char(5) NOT NULL DEFAULT '', + `pattern` varchar(35) NOT NULL DEFAULT '1', + `description` varchar(60) NOT NULL DEFAULT '', + `default` tinyint(1) NOT NULL DEFAULT '0', + `inactive` tinyint(1) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + UNIQUE KEY `prefix` (`trans_type`, `prefix`) +) ENGINE=InnoDB; + +INSERT INTO `0_reflines` (`trans_type`, `pattern`, `default`) SELECT `type_id`, `next_reference`, 1 FROM `0_sys_types`; + +DROP TABLE `0_sys_types`; + +ALTER TABLE `0_cust_branch` DROP KEY `branch_code`; +ALTER TABLE `0_supp_trans` DROP KEY `SupplierID_2`; +ALTER TABLE `0_supp_trans` DROP KEY `type`; + +# new fixed assets module +ALTER TABLE `0_locations` ADD COLUMN `fixed_asset` tinyint(1) NOT NULL DEFAULT '0' after `contact`; + +DROP TABLE IF EXISTS `0_stock_fa_class`; +CREATE TABLE `0_stock_fa_class` ( + `fa_class_id` varchar(20) NOT NULL DEFAULT '', + `parent_id` varchar(20) NOT NULL DEFAULT '', + `description` varchar(200) NOT NULL DEFAULT '', + `long_description` tinytext NOT NULL, + `depreciation_rate` double NOT NULL DEFAULT '0', + `inactive` tinyint(1) NOT NULL DEFAULT '0', + PRIMARY KEY (`fa_class_id`) +) ENGINE=InnoDB; + +ALTER TABLE `0_stock_master` ADD COLUMN `depreciation_method` char(1) NOT NULL DEFAULT 'S' AFTER `editable`; +ALTER TABLE `0_stock_master` ADD COLUMN `depreciation_rate` double NOT NULL DEFAULT '0' AFTER `depreciation_method`; +ALTER TABLE `0_stock_master` ADD COLUMN `depreciation_factor` double NOT NULL DEFAULT '0' AFTER `depreciation_rate`; +ALTER TABLE `0_stock_master` ADD COLUMN `depreciation_start` date NOT NULL DEFAULT '0000-00-00' AFTER `depreciation_factor`; +ALTER TABLE `0_stock_master` ADD COLUMN `depreciation_date` date NOT NULL DEFAULT '0000-00-00' AFTER `depreciation_start`; +ALTER TABLE `0_stock_master` ADD COLUMN `fa_class_id` varchar(20) NOT NULL DEFAULT '' AFTER `depreciation_date`; +ALTER TABLE `0_stock_master` CHANGE `actual_cost` `purchase_cost` double NOT NULL default 0; + +INSERT IGNORE INTO `0_sys_prefs` VALUES + ('default_loss_on_asset_disposal_act', 'glsetup.items', 'varchar', '15', '5660'), + ('depreciation_period', 'glsetup.company', 'tinyint', '1', '1'), + ('use_manufacturing','setup.company', 'tinyint', 1, '1'), + ('use_fixed_assets','setup.company', 'tinyint', 1, '1'); + +# manufacturing rewrite +ALTER TABLE `0_wo_issue_items` ADD COLUMN `unit_cost` double NOT NULL default '0' AFTER `qty_issued`; +ALTER TABLE `0_wo_requirements` CHANGE COLUMN `std_cost` `unit_cost` double NOT NULL default '0'; + +ALTER TABLE `0_stock_master` DROP COLUMN `last_cost`; +UPDATE `0_stock_master` SET `material_cost`=`material_cost`+`labour_cost`+`overhead_cost`; + +ALTER TABLE `0_stock_master` CHANGE COLUMN `assembly_account` `wip_account` VARCHAR(15) NOT NULL default ''; +ALTER TABLE `0_stock_category` CHANGE COLUMN `dflt_assembly_act` `dflt_wip_act` VARCHAR(15) NOT NULL default ''; +UPDATE `0_sys_prefs` SET `name`='default_wip_act' WHERE `name`='default_assembly_act'; + +UPDATE `0_wo_issue_items` i, `0_stock_moves` m + SET i.unit_cost=m.standard_cost + WHERE i.unit_cost=0 AND i.stock_id=m.stock_id AND m.trans_no=i.issue_id AND m.`type`=28 AND m.qty=-i.qty_issued; + +UPDATE `0_wo_requirements` r, `0_stock_moves` m + SET r.unit_cost=m.standard_cost + WHERE r.unit_cost=0 AND r.stock_id=m.stock_id AND m.trans_no=r.workorder_id AND m.`type`=26 AND m.qty=-r.units_issued; + +UPDATE `0_bank_trans` SET person_id=trans_no WHERE person_type_id=26; + +ALTER TABLE `0_budget_trans` CHANGE `counter` `id` int(11) NOT NULL AUTO_INCREMENT; +ALTER TABLE `0_sys_prefs` CHANGE `value` `value` text NOT NULL default ''; + +ALTER TABLE `0_debtor_trans` + CHANGE `debtor_no` `debtor_no` int(11) unsigned NOT NULL, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`type`,`trans_no`,`debtor_no`); + +ALTER TABLE `0_supp_trans` + CHANGE `supplier_id` `supplier_id` int(11) unsigned NOT NULL, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`type`,`trans_no`,`supplier_id`);