From 9db26905f4397053c3ec3362a76add78febb8dac Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 17 Nov 2013 15:41:39 +0100 Subject: [PATCH] Added exchange rate field in grn_batch. --- purchasing/includes/db/grn_db.inc | 9 +++++---- sql/alter2.4.php | 20 +++++++++++++++++++- sql/alter2.4.sql | 5 ++++- sql/en_US-demo.sql | 3 ++- sql/en_US-new.sql | 1 + 5 files changed, 31 insertions(+), 7 deletions(-) diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index 94853b46..7b85ef13 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -101,7 +101,7 @@ function add_grn(&$po) add_new_exchange_rate(get_supplier_currency($po->supplier_id), $date_, $po->ex_rate); - $grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_); + $grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_, $po->ex_rate); $clearing_act = get_company_pref('grn_clearing_act'); if ($clearing_act) { // otherwise GRN clearing account is not used @@ -168,13 +168,13 @@ function add_grn(&$po) //---------------------------------------------------------------------------------------- -function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_) +function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_, $rate) { $date = date2sql($date_); - $sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code) + $sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code, rate) VALUES (".db_escape($po_number).", ".db_escape($date).", " - .db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).")"; + .db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).", ".db_escape($rate).")"; db_query($sql, "A grn batch record could not be inserted."); @@ -368,6 +368,7 @@ function read_grn($grn_batch, &$order) $row = db_fetch($result); $po_number = $row["purch_order_no"]; + $order->ex_rate = $row["rate"]; $result = read_po_header($po_number, $order); diff --git a/sql/alter2.4.php b/sql/alter2.4.php index c065ce05..3f29a739 100644 --- a/sql/alter2.4.php +++ b/sql/alter2.4.php @@ -35,7 +35,8 @@ class fa2_4 { if (get_company_pref('default_receival_required') === null) { // new in 2.4 installations set_company_pref('default_receival_required', 'glsetup.purchase', 'smallint', 6, 10); } - $result = $this->update_workorders() && $this->switch_database_to_utf($pref); + $result = $this->update_workorders() && $this->update_grn_rates() && $this->switch_database_to_utf($pref); + if ($result) $result = $this->do_cleanup(); @@ -178,6 +179,23 @@ class fa2_4 { return true; } + function update_grn_rates() + { + $sql = "SELECT grn.id, grn.delivery_date, supp.curr_code + FROM ".TB_PREF."grn_batch grn, ".TB_PREF."suppliers supp + WHERE supp.supplier_id=grn.supplier_id AND supp.curr_code!='".get_company_pref('curr_default')."'"; + $result = db_query($sql); + + if (!$result) + return false; + + $sql = "UPDATE ".TB_PREF."grn_batch SET rate=%s WHERE id=%d"; + while ($grn = db_fetch($result)) + db_query(sprintf($sql, get_exchange_rate_from_home_currency($grn['curr_code'], sql2date($grn['delivery_date'])), $grn['id'])); + + return true; + } + function do_cleanup() { $dropcol = array( diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index e9d79cbe..14afb1ae 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -79,4 +79,7 @@ UPDATE `0_security_roles` SET `areas`=CONCAT_WS(';', `areas`, '775') ALTER TABLE `0_stock_master` ADD COLUMN `no_purchase` tinyint(1) NOT NULL default '0' AFTER `no_sale`; ALTER TABLE `0_stock_category` ADD COLUMN `dflt_no_purchase` tinyint(1) NOT NULL default '0' AFTER `dflt_no_sale`; -ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10; \ No newline at end of file + +# added exchange rate field in grn_batch +ALTER TABLE `0_grn_batch` ADD COLUMN `rate` double NULL default '1' AFTER `loc_code`; +ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 9d0c0f9f..b3ae1781 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -959,6 +959,7 @@ CREATE TABLE IF NOT EXISTS `0_grn_batch` ( `reference` varchar(60) NOT NULL default '', `delivery_date` date NOT NULL default '0000-00-00', `loc_code` varchar(5) default NULL, + `rate` double NOT NULL default '1', PRIMARY KEY (`id`), KEY `delivery_date` (`delivery_date`), KEY `purch_order_no` (`purch_order_no`) @@ -968,7 +969,7 @@ CREATE TABLE IF NOT EXISTS `0_grn_batch` ( -- Dumping data for table `0_grn_batch` -- -INSERT INTO `0_grn_batch` VALUES(1, 2, 1, '1', '2009-06-21', 'DEF'); +INSERT INTO `0_grn_batch` VALUES(1, 2, 1, '1', '2009-06-21', 'DEF', '1'); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 583cbd61..344d22f9 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -812,6 +812,7 @@ CREATE TABLE IF NOT EXISTS `0_grn_batch` ( `reference` varchar(60) NOT NULL default '', `delivery_date` date NOT NULL default '0000-00-00', `loc_code` varchar(5) default NULL, + `rate` double NOT NULL default '1', PRIMARY KEY (`id`), KEY `delivery_date` (`delivery_date`), KEY `purch_order_no` (`purch_order_no`) -- 2.30.2