From e4c7f0dbe9fa83ed6382713b32a4586ce3e60d81 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Mon, 21 Jun 2010 17:01:42 +0000 Subject: [PATCH] Added sales/purchase order totals --- sql/alter2.3.php | 37 +++++++++++++++++++++++++++++++++++-- sql/alter2.3.sql | 2 ++ sql/en_US-demo.sql | 22 ++++++++++++---------- sql/en_US-new.sql | 2 ++ 4 files changed, 51 insertions(+), 12 deletions(-) diff --git a/sql/alter2.3.php b/sql/alter2.3.php index c1d57492..a429430b 100644 --- a/sql/alter2.3.php +++ b/sql/alter2.3.php @@ -61,6 +61,7 @@ class fa2_3 { 'cust_branch' => array('phone', 'phone2', 'fax', 'email'), 'suppliers' => array('phone', 'phone2', 'fax', 'email') ); + foreach($dropcol as $table => $columns) foreach($columns as $col) { if (db_query("ALTER TABLE `{$pref}{$table}` DROP `$col`")==false) { @@ -68,7 +69,10 @@ class fa2_3 { return false; } } - + if (!update_totals_2_3($pref)) { + display_error("Cannot update order totals"); + return false; + } // remove old preferences table after upgrade script has been executed $sql = "DROP TABLE IF EXISTS `{$pref}company`"; @@ -95,6 +99,35 @@ class fa2_3 { $n -= $patchcnt; return $n == 0 ? true : $patchcnt; } -} +} +/* + Update order totals +*/ +function update_totals_2_3($pref) +{ + global $path_to_root; + + include_once("$path_to_root/sales/includes/cart_class.inc"); + include_once("$path_to_root/purchasing/includes/po_class.inc"); + $cart = new cart(ST_SALESORDER); + $sql = "SELECT order_no FROM {$pref}sales_orders"; + $orders = db_query($sql); + while ($order_no = db_fetch($orders)) { + read_sales_order($order_no[0], $cart, ST_SALESORDER); + update_sales_order($cart); + unset($cart->line_items); + } + unset($cart); + $cart = new purch_order(); + $sql = "SELECT order_no FROM {$pref}purch_orders"; + $orders = db_query($sql); + while ($order_no = db_fetch($orders)) { + read_po($order_no[0], $cart); + update_po($cart); + unset($cart->line_items); + } +} + $install = new fa2_3; + ?> \ No newline at end of file diff --git a/sql/alter2.3.sql b/sql/alter2.3.sql index e6d3da0c..4d45ed16 100644 --- a/sql/alter2.3.sql +++ b/sql/alter2.3.sql @@ -115,6 +115,8 @@ INSERT INTO `0_sys_prefs` SELECT 'version_id', 'system', 'varchar', '11', c.vers ALTER TABLE `0_stock_master` ADD COLUMN `editable` TINYINT(1) NOT NULL default '0'; ALTER TABLE `0_debtor_trans` ADD COLUMN `payment_terms` int(11) default NULL; ALTER TABLE `0_sales_orders` ADD COLUMN `payment_terms` int(11) default NULL; +ALTER TABLE `0_sales_orders` ADD COLUMN `total` double NOT NULL default '0'; +ALTER TABLE `0_purch_orders` ADD COLUMN `total` double NOT NULL default '0'; # change account, groups and classes id's ALTER TABLE `0_bank_accounts` CHANGE `account_code` `account_code` VARCHAR(15) NOT NULL DEFAULT ''; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 0236cea6..a6d08314 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -1330,6 +1330,7 @@ CREATE TABLE IF NOT EXISTS `0_purch_orders` ( `requisition_no` tinytext, `into_stock_location` varchar(5) NOT NULL default '', `delivery_address` tinytext NOT NULL, + `total` double NOT NULL default '0', PRIMARY KEY (`order_no`), KEY `ord_date` (`ord_date`) ) TYPE=InnoDB AUTO_INCREMENT=3 ; @@ -1338,8 +1339,8 @@ CREATE TABLE IF NOT EXISTS `0_purch_orders` ( -- Dumping data for table `0_purch_orders` -- -INSERT INTO `0_purch_orders` VALUES(1, 2, '', '2009-06-01', '1', '', 'DEF', 'Delivery 1\nDelivery 2\nDelivery 3'); -INSERT INTO `0_purch_orders` VALUES(2, 3, '', '2009-06-21', '2', '', 'DEF', 'Delivery 1\nDelivery 2\nDelivery 3'); +INSERT INTO `0_purch_orders` VALUES(1, 2, '', '2009-06-01', '1', '', 'DEF', 'Delivery 1\nDelivery 2\nDelivery 3', 0); +INSERT INTO `0_purch_orders` VALUES(2, 3, '', '2009-06-21', '2', '', 'DEF', 'Delivery 1\nDelivery 2\nDelivery 3', 0); -- -------------------------------------------------------- @@ -1534,6 +1535,7 @@ CREATE TABLE IF NOT EXISTS `0_sales_orders` ( `from_stk_loc` varchar(5) NOT NULL default '', `delivery_date` date NOT NULL default '0000-00-00', `payment_terms` int(11) default NULL, + `total` double NOT NULL default '0', PRIMARY KEY (`trans_type`,`order_no`) ) TYPE=InnoDB; @@ -1541,11 +1543,11 @@ CREATE TABLE IF NOT EXISTS `0_sales_orders` ( -- Dumping data for table `0_sales_orders` -- -INSERT INTO `0_sales_orders` VALUES(1, 30, 1, 0, 1, 1, '1', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Beefeater Ltd.', 10, 'DEF', '2009-06-22', 3); -INSERT INTO `0_sales_orders` VALUES(2, 30, 1, 0, 2, 2, '2', '', '', '2009-06-21', 1, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Ghostbusters Corp.', 0, 'DEF', '2009-06-22', 4); -INSERT INTO `0_sales_orders` VALUES(3, 30, 1, 0, 3, 3, '3', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Brezan', 0, 'DEF', '2009-07-01', 3); -INSERT INTO `0_sales_orders` VALUES(4, 30, 0, 0, 1, 1, '4', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Beefeater Ltd.', 0, 'DEF', '2009-06-22', 3); -INSERT INTO `0_sales_orders` VALUES(5, 30, 1, 0, 2, 2, '5', '', '', '2009-06-21', 1, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Ghostbusters Corp.', 5, 'DEF', '2009-06-22', 4); +INSERT INTO `0_sales_orders` VALUES(1, 30, 1, 0, 1, 1, '1', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Beefeater Ltd.', 10, 'DEF', '2009-06-22', 3, 0); +INSERT INTO `0_sales_orders` VALUES(2, 30, 1, 0, 2, 2, '2', '', '', '2009-06-21', 1, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Ghostbusters Corp.', 0, 'DEF', '2009-06-22', 4, 0); +INSERT INTO `0_sales_orders` VALUES(3, 30, 1, 0, 3, 3, '3', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Brezan', 0, 'DEF', '2009-07-01', 3, 0); +INSERT INTO `0_sales_orders` VALUES(4, 30, 0, 0, 1, 1, '4', '', '', '2009-06-21', 2, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Beefeater Ltd.', 0, 'DEF', '2009-06-22', 3, 0); +INSERT INTO `0_sales_orders` VALUES(5, 30, 1, 0, 2, 2, '5', '', '', '2009-06-21', 1, 1, 'Address 1\nAddress 2\nAddress 3', '', '', 'Ghostbusters Corp.', 5, 'DEF', '2009-06-22', 4, 0); -- -------------------------------------------------------- @@ -1887,9 +1889,9 @@ CREATE TABLE IF NOT EXISTS `0_suppliers` ( -- Dumping data for table `0_suppliers` -- -INSERT INTO `0_suppliers` VALUES(1, 'Junk Beer ApS', 'Junk Beer', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '123456', 'Contact', '111', '', '', 'DKK', 3, 1, 0, 2, 1, '5010', '2100', '5060', 'A supplier with junk beers.', 0); -INSERT INTO `0_suppliers` VALUES(2, 'Lucky Luke Inc.', 'Lucky Luke', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '654321', 'Luke', '333', '', '', 'USD', 3, 0, 0, 1, 1, '5010', '2100', '5060', '', 0); -INSERT INTO `0_suppliers` VALUES(3, 'Money Makers Ltd.', 'Money Makers', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '987654', 'Makers', '222', '', '', 'GBP', 3, 0, 0, 2, 1, '5010', '2100', '5060', '', 0); +INSERT INTO `0_suppliers` VALUES(1, 'Junk Beer ApS', 'Junk Beer', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '123456', 'Contact', '111', '', '', 'DKK', 3, 1, 0, 2, 1000, '5010', '2100', '5060', 'A supplier with junk beers.', 0); +INSERT INTO `0_suppliers` VALUES(2, 'Lucky Luke Inc.', 'Lucky Luke', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '654321', 'Luke', '333', '', '', 'USD', 3, 0, 0, 1, 500, '5010', '2100', '5060', '', 0); +INSERT INTO `0_suppliers` VALUES(3, 'Money Makers Ltd.', 'Money Makers', 'Mailing 1\nMailing 2\nMailing 3', 'Address 1\nAddress 2\nAddress 3', '987654', 'Makers', '222', '', '', 'GBP', 3, 0, 0, 2, 300, '5010', '2100', '5060', '', 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 730b1e1d..75903fc8 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -1163,6 +1163,7 @@ CREATE TABLE IF NOT EXISTS `0_purch_orders` ( `requisition_no` tinytext, `into_stock_location` varchar(5) NOT NULL default '', `delivery_address` tinytext NOT NULL, + `total` double NOT NULL default '0', PRIMARY KEY (`order_no`), KEY `ord_date` (`ord_date`) ) TYPE=InnoDB AUTO_INCREMENT=1 ; @@ -1358,6 +1359,7 @@ CREATE TABLE IF NOT EXISTS `0_sales_orders` ( `from_stk_loc` varchar(5) NOT NULL default '', `delivery_date` date NOT NULL default '0000-00-00', `payment_terms` int(11) default NULL, + `total` double NOT NULL default '0', PRIMARY KEY (`trans_type`,`order_no`) ) TYPE=InnoDB; -- 2.30.2