X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=sql%2Falter2.4.sql;h=f89f15c96e289a8955581f0c2600bdb796e7f067;hb=96f82e64b766fa59a3ebc3176e4efd17736f6878;hp=d944173746f9c8100c03c49e3a4559c91b95534c;hpb=7732e889ec468099f6b66173b8dd6363e0a30ed8;p=fa-stable.git

diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql
index d9441737..f89f15c9 100644
--- a/sql/alter2.4.sql
+++ b/sql/alter2.4.sql
@@ -33,3 +33,46 @@ UPDATE `0_tax_group_items` tgi
 	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 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`;
+UPDATE `0_debtor_trans` tr, `0_trans_tax_details` td SET tr.tax_included=td.included_in_price
+	WHERE tr.`type`=td.trans_type AND tr.trans_no=td.trans_no AND td.included_in_price;
+
+ALTER TABLE `0_bank_accounts` ADD COLUMN `bank_charge_act` varchar(15) NOT NULL DEFAULT '' AFTER `id`;
+UPDATE `0_bank_accounts` SET `bank_charge_act`=(SELECT `value` FROM 0_sys_prefs WHERE name='bank_charge_act');
+
+ALTER TABLE `0_users` ADD `transaction_days` INT( 6 ) NOT NULL default '30' COMMENT 'Transaction days' AFTER `startup_tab`;
+
+ALTER TABLE `0_purch_orders` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `total`;
+ALTER TABLE `0_purch_orders` ADD COLUMN `alloc` double NOT NULL DEFAULT 0 AFTER `prep_amount`;
+
+ALTER TABLE `0_sales_orders` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `total`;
+ALTER TABLE `0_sales_orders` ADD COLUMN `alloc` double NOT NULL DEFAULT 0 AFTER `prep_amount`;
+
+ALTER TABLE `0_cust_allocations` ADD  UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
+ALTER TABLE `0_supp_allocations` ADD  UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`);
+
+ALTER TABLE `0_sales_order_details` ADD COLUMN `invoiced` double NOT NULL DEFAULT 0 AFTER `quantity`;
+
+# update sales_order_details.invoiced with sum of invoiced quantities on all related SI
+UPDATE `0_sales_order_details` so
+	LEFT JOIN `0_debtor_trans_details` delivery ON delivery.`debtor_trans_type`=13 AND src_id=so.id
+	LEFT JOIN (SELECT src_id, sum(quantity) as qty FROM `0_debtor_trans_details` WHERE `debtor_trans_type`=10 GROUP BY src_id) inv
+		ON inv.src_id=delivery.id
+	SET `invoiced` = `invoiced`+inv.qty;
+
+ALTER TABLE `0_debtor_trans` ADD COLUMN `prep_amount` double NOT NULL DEFAULT 0 AFTER `alloc`;
+
+INSERT INTO `0_sys_prefs` VALUES ('deferred_income_act', 'glsetup.sales', 'varchar', '15', '');
+
+# set others transactions edition for all roles for backward  compatibility
+UPDATE `0_security_roles` SET `sections`=CONCAT_WS(';', `sections`, '768'), `areas`='775'
+	WHERE NOT `sections` REGEXP '[^0-9]?768[^0-9]?';
+
+UPDATE `0_security_roles` SET `areas`=CONCAT_WS(';', `areas`, '775')
+	WHERE NOT `areas` REGEXP '[^0-9]?775[^0-9]?';