Recurrent Invoices: fixed buggy call to non existing function and payment terms type...

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

diff --git a/sql/alter2.5.sql b/sql/alter2.5.sql
index 1511ff1123b49565094629456dde9a352aba4aff..0c316f1672b5f7108991879ad0cedd90d9a4ec2a 100644 (file)
--- a/sql/alter2.5.sql
+++ b/sql/alter2.5.sql
@@ -59,3 +59,63 @@ UPDATE `0_cust_branch` branch
        SET branch.default_ship_via=stock.stock_id;
 
 ALTER TABLE `0_tax_group_items` DROP COLUMN `tax_shipping`;
+
+# new debug trail
+DROP TABLE `0_sql_trail`;
+CREATE TABLE `0_db_trail` (
+               `id` int(11) NOT NULL AUTO_INCREMENT,
+               `stamp` timestamp DEFAULT CURRENT_TIMESTAMP,
+               `user` tinyint(3) unsigned NOT NULL DEFAULT '0',
+               `msg`  varchar(255) DEFAULT '',
+               `entry`  varchar(255) DEFAULT '',
+               `data` text DEFAULT NULL,
+       PRIMARY KEY (`id`)
+       ) ENGINE=MyISAM;
+
+# payment terms normalization, early payment support
+ALTER TABLE `0_payment_terms` CHANGE COLUMN `terms_indicator` `id` tinyint(1) NOT NULL DEFAULT '0';
+ALTER TABLE `0_payment_terms` ADD COLUMN `type` tinyint(1) NOT NULL DEFAULT '0' AFTER `terms`;
+UPDATE `0_payment_terms` SET `type`=IF(days_before_due < 0, 1, IF(day_in_following_month>0, 4, IF(days_before_due=0, 2, 3)));
+UPDATE `0_payment_terms` SET days_before_due=day_in_following_month WHERE days_before_due<=0;
+ALTER TABLE `0_payment_terms` CHANGE COLUMN `days_before_due` `days` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `0_payment_terms` DROP COLUMN `day_in_following_month`;
+ALTER TABLE `0_payment_terms` ADD COLUMN `early_discount` double NOT NULL DEFAULT '0' AFTER `days`;
+ALTER TABLE `0_payment_terms` ADD COLUMN `early_days` int(11) NOT NULL DEFAULT '0' AFTER `early_discount`;
+
+ALTER TABLE `0_cust_allocations` ADD COLUMN `discount` double unsigned DEFAULT '0' AFTER `amt`;
+ALTER TABLE `0_supp_allocations` ADD COLUMN `discount` double unsigned DEFAULT '0' AFTER `amt`;
+
+# please define and set payment terms with discount manually per customer when needed
+ALTER TABLE `0_debtors_master` DROP COLUMN `pymt_discount`;
+
+# this update works only for single pay for invoice discounts, so may need additional manual fixes in more complex cases 
+UPDATE `0_cust_allocations` ca
+       LEFT JOIN `0_debtor_trans` pay ON pay.`type`=ca.`trans_type_from` AND pay.`trans_no`=ca.`trans_no_from`
+       LEFT JOIN `0_debtor_trans` trans ON trans.`type`=ca.`trans_type_to` AND trans.`trans_no`=ca.`trans_no_to`
+SET ca.discount=pay.ov_discount 
+       WHERE pay.ov_discount != 0 AND pay.ov_amount+pay.ov_discount = trans.ov_amount+trans.ov_gst+trans.ov_freight+trans.ov_freight_tax;
+
+UPDATE `0_supp_allocations` sa
+       LEFT JOIN `0_supp_trans` pay ON pay.`type`=sa.`trans_type_from` AND pay.`trans_no`=sa.`trans_no_from`
+       LEFT JOIN `0_supp_trans` trans ON trans.`type`=sa.`trans_type_to` AND trans.`trans_no`=sa.`trans_no_to`
+SET sa.discount=pay.ov_discount 
+       WHERE pay.ov_discount != 0 AND pay.ov_amount+pay.ov_discount = trans.ov_amount+trans.ov_gst;
+
+# bank charge stored in bank_trans
+ALTER TABLE `0_bank_trans` ADD COLUMN `charge` double DEFAULT 0 AFTER `amount`;
+
+UPDATE `0_bank_trans` bt
+       LEFT JOIN (SELECT trans.type, trans.trans_no, IF(act.bank_curr_code=home_curr.value, charge.amount,
+               IF(act.bank_curr_code=debtor.curr_code, -(trans.amount-ar.ov_amount+ar.ov_discount),
+               IFNULL(charge.amount*trans.amount/pmt.amount, 0))) amount
+               FROM 0_bank_trans trans
+                       LEFT JOIN `0_bank_accounts` act ON trans.bank_act=act.id
+                       LEFT JOIN `0_sys_prefs` charge_act ON charge_act.name='bank_charge_act'
+                       LEFT JOIN `0_sys_prefs` home_curr ON home_curr.name='curr_default'
+                       LEFT JOIN `0_gl_trans` charge ON charge.type=trans.`type` AND charge.type_no=trans.trans_no AND charge.account=charge_act.`value`
+                       LEFT JOIN `0_gl_trans` pmt ON pmt.type=trans.type AND pmt.type_no=trans.trans_no AND pmt.account=act.account_code
+                       LEFT JOIN `0_debtors_master` debtor ON trans.person_id=debtor.debtor_no AND trans.person_type_id=2
+                       LEFT JOIN `0_debtor_trans` ar ON trans.type=ar.`type` AND trans.trans_no=ar.trans_no
+               WHERE pmt.amount!=0 AND charge.amount!=0 AND trans.amount!=0) charges ON bt.`type`=charges.`type` AND bt.`trans_no`=charges.`trans_no`
+       SET bt.charge=IFNULL(-charges.amount,0),
+               bt.amount=bt.amount+IFNULL(charges.amount,0);