Improved journal trans: added currency support, tax and source document date. Allowed...

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

diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql
index 8b74324fa5c6f09a5d3e30709aae40b2205ad6c3..eedb056622033b40b2fbe8e8cb0c5ed20183ddc3 100644 (file)
--- a/sql/alter2.4.sql
+++ b/sql/alter2.4.sql
@@ -154,3 +154,49 @@ 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`;
+
+# multiply allocations to single jiurnal transaction
+ALTER TABLE `0_cust_allocations` ADD COLUMN `person_id` int(11) DEFAULT NULL AFTER `id`;
+UPDATE `0_cust_allocations` alloc LEFT JOIN `0_debtor_trans` trans ON alloc.trans_no_to=trans.trans_no AND alloc.trans_type_to=trans.type
+       SET alloc.person_id = trans.debtor_no;
+
+ALTER TABLE `0_supp_allocations` ADD COLUMN `person_id` int(11) DEFAULT NULL AFTER `id`;
+UPDATE `0_supp_allocations` alloc LEFT JOIN `0_supp_trans` trans ON alloc.trans_no_to=trans.trans_no AND alloc.trans_type_to=trans.type
+       SET alloc.person_id = trans.supplier_id;
+
+ALTER TABLE `0_cust_allocations` DROP KEY `trans_type_from`;
+ALTER TABLE `0_cust_allocations` ADD  UNIQUE KEY(`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
+ALTER TABLE `0_supp_allocations` DROP KEY `trans_type_from`;
+ALTER TABLE `0_supp_allocations` ADD  UNIQUE KEY(`person_id`,`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
+
+# full support for any journal transaction
+DROP TABLE IF EXISTS `0_journal`;
+CREATE TABLE `0_journal` (
+  `type` smallint(6) NOT NULL DEFAULT '0',
+  `trans_no` int(11) NOT NULL DEFAULT '0',
+  `tran_date` date DEFAULT '0000-00-00',
+  `reference` varchar(60) NOT NULL DEFAULT '',
+  `source_ref` varchar(60) NOT NULL DEFAULT '',
+  `event_date` date DEFAULT '0000-00-00',
+  `doc_date` date NOT NULL DEFAULT '0000-00-00',
+  `currency` char(3) NOT NULL DEFAULT '',
+  `amount` double NOT NULL DEFAULT '0',
+  `rate` double NOT NULL DEFAULT '1',
+  PRIMARY KEY `Type_and_Number` (`type`,`trans_no`),
+  KEY `tran_date` (`tran_date`)
+) 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))
+ 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)
+ GROUP BY `type`,`type_no`;
+
+# allow multiply customers.suppliers in single journal transaction
+ALTER TABLE `0_debtor_trans` DROP PRIMARY KEY;
+ALTER TABLE `0_debtor_trans` ADD  PRIMARY KEY (`type`,`trans_no`, `debtor_no`);
+ALTER TABLE `0_supp_trans` DROP PRIMARY KEY;
+ALTER TABLE `0_supp_trans` ADD  PRIMARY KEY (`type`,`trans_no`, `supplier_id`);
+