Transaction references extended with parametrized patterns, added check_reference...

[fa-stable.git] / sql / alter2.4.sql

diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql
index 3ae9e0ec53311aff3f57f89d45a257cc6ff29710..2e765bfb9916b8129bd09b33ad8ce9a02b219abb 100644 (file)
--- a/sql/alter2.4.sql
+++ b/sql/alter2.4.sql
@@ -1,5 +1,3 @@
-ALTER TABLE `0_suppliers` ADD COLUMN  `tax_algorithm` tinyint(1) NOT NULL default '1' AFTER `tax_included`;
-ALTER TABLE `0_supp_trans` ADD COLUMN `tax_algorithm` tinyint(1) NULL default '1' AFTER `tax_included`;
 INSERT INTO `0_sys_prefs` VALUES('tax_algorithm','glsetup.customer', 'tinyint', 1, '1');
 INSERT INTO `0_sys_prefs` VALUES('gl_closing_date','setup.closing_date', 'date', 8, '');
 ALTER TABLE `0_audit_trail` CHANGE `fiscal_year` `fiscal_year` int(11) NOT NULL default 0;
@@ -34,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`;
@@ -117,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;
@@ -150,7 +146,7 @@ ALTER TABLE `0_supp_invoice_items` ADD COLUMN `dimension2_id` int(11) NOT NULL D
 UPDATE `0_supp_invoice_items` si
        LEFT JOIN `0_gl_trans` gl ON si.supp_trans_type=gl.`type` AND si.supp_trans_no=gl.type_no AND si.gl_code=gl.account
        SET si.dimension_id=gl.dimension_id, si.dimension2_id=gl.dimension2_id
-WHERE si.grn_item_id=-1 AND (gl.dimension_id OR gl.dimension2_id)
+WHERE si.grn_item_id=-1 AND (gl.dimension_id OR gl.dimension2_id);
 
 ALTER TABLE `0_quick_entries` ADD COLUMN `usage` varchar(120) NULL AFTER `description`;
 ALTER TABLE `0_quick_entry_lines` ADD COLUMN `memo` tinytext NOT NULL AFTER `amount`;
@@ -187,8 +183,8 @@ CREATE TABLE `0_journal` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 
 INSERT INTO `0_journal` (`type`, `trans_no`, `tran_date`, `reference`, `event_date`,`doc_date`,`currency`,`amount`)
- SELECT `gl`.`type`, `gl`.`type_no`, `gl`.`tran_date`, `ref`.`reference`, `gl`.`event_date`,
-               `gl`.`doc_date`, `sys_curr`.`value`, SUM(IF(`gl`.`amount`>0,`gl`.`amount`,0))
+ SELECT `gl`.`type`, `gl`.`type_no`, `gl`.`tran_date`, `ref`.`reference`, `gl`.`tran_date`,
+               `gl`.`tran_date`, `sys_curr`.`value`, SUM(IF(`gl`.`amount`>0,`gl`.`amount`,0))
  FROM `0_gl_trans` gl LEFT JOIN `0_refs` ref ON gl.type = ref.type AND gl.type_no=ref.id
  LEFT JOIN `0_sys_prefs` sys_curr ON `sys_curr`.`name`='curr_default'
  WHERE `gl`.`type` IN(0, 35)
@@ -210,3 +206,54 @@ 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_receival_required', 'glsetup.purchase', 'smallint', 6, '10'),
+       ('default_quote_valid_days', 'glsetup.sales', 'smallint', 6, 30),
+       ('no_zero_lines_amount', 'glsetup.sales', 'tinyint', 1, '1'),
+       ('show_po_item_codes', 'glsetup.purchase', 'tinyint', 1, '0'),
+       ('accounts_alpha', 'glsetup.general', 'tinyint', 1, '0'),
+       ('loc_notification', 'glsetup.inventory', 'tinyint', 1, '0'),
+       ('print_invoice_no', 'glsetup.sales', 'tinyint', 1, '0'),
+       ('allow_negative_prices', 'glsetup.inventory', 'tinyint', 1, '1'),
+       ('print_item_images_on_quote', 'glsetup.inventory', '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`;