Merge pull request #7162 from nabinhait/manqala-exchange_rate_modifications1
Exchange rate base on date
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.js b/erpnext/accounts/doctype/journal_entry/journal_entry.js
index 06ba5df..c98e77f 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.js
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.js
@@ -35,6 +35,14 @@
multi_currency: function(frm) {
erpnext.journal_entry.toggle_fields_based_on_currency(frm);
+ },
+
+ posting_date: function(frm) {
+ if(!frm.doc.multi_currency) return;
+
+ $.each(frm.doc.accounts || [], function(i, row) {
+ erpnext.journal_entry.set_exchange_rate(frm, row.doctype, row.name);
+ })
}
})
@@ -345,7 +353,7 @@
});
}
},
-
+
debit_in_account_currency: function(frm, cdt, cdn) {
erpnext.journal_entry.set_exchange_rate(frm, cdt, cdn);
},
@@ -420,6 +428,7 @@
frappe.call({
method: "erpnext.accounts.doctype.journal_entry.journal_entry.get_exchange_rate",
args: {
+ posting_date: frm.doc.posting_date,
account: row.account,
account_currency: row.account_currency,
company: frm.doc.company,
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 2f3b101..cd79363 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -326,8 +326,9 @@
if d.account_currency == self.company_currency:
d.exchange_rate = 1
elif not d.exchange_rate or d.exchange_rate == 1 or \
- (d.reference_type in ("Sales Invoice", "Purchase Invoice") and d.reference_name):
- d.exchange_rate = get_exchange_rate(d.account, d.account_currency, self.company,
+ (d.reference_type in ("Sales Invoice", "Purchase Invoice") and d.reference_name and d.posting_date):
+ # Modified to include the posting date for which to retreive the exchange rate
+ d.exchange_rate = get_exchange_rate(self.posting_date, d.account, d.account_currency, self.company,
d.reference_type, d.reference_name, d.debit, d.credit, d.exchange_rate)
if not d.exchange_rate:
@@ -648,7 +649,9 @@
cost_center = frappe.db.get_value("Company", ref_doc.company, "cost_center")
exchange_rate = 1
if args.get("party_account"):
- exchange_rate = get_exchange_rate(args.get("party_account"), args.get("party_account_currency"),
+ # Modified to include the posting date for which the exchange rate is required.
+ # Assumed to be the posting date in the reference document
+ exchange_rate = get_exchange_rate(ref_doc.posting_date, args.get("party_account"), args.get("party_account_currency"),
ref_doc.company, ref_doc.doctype, ref_doc.name)
je = frappe.new_doc("Journal Entry")
@@ -681,7 +684,9 @@
bank_account = get_default_bank_cash_account(ref_doc.company, "Bank", account=args.get("bank_account"))
if bank_account:
bank_row.update(bank_account)
- bank_row.exchange_rate = get_exchange_rate(bank_account["account"],
+ # Modified to include the posting date for which the exchange rate is required.
+ # Assumed to be the posting date of the reference date
+ bank_row.exchange_rate = get_exchange_rate(ref_doc.posting_date, bank_account["account"],
bank_account["account_currency"], ref_doc.company)
bank_row.cost_center = cost_center
@@ -805,7 +810,10 @@
"party_type": party_type,
"account_type": account_details.account_type,
"account_currency": account_details.account_currency or company_currency,
- "exchange_rate": get_exchange_rate(account, account_details.account_currency,
+
+ # The date used to retreive the exchange rate here is the date passed in
+ # as an argument to this function. It is assumed to be the date on which the balance is sought
+ "exchange_rate": get_exchange_rate(date, account, account_details.account_currency,
company, debit=debit, credit=credit, exchange_rate=exchange_rate)
}
@@ -815,8 +823,9 @@
return grid_values
+# Added posting_date as one of the parameters of get_exchange_rate
@frappe.whitelist()
-def get_exchange_rate(account, account_currency=None, company=None,
+def get_exchange_rate(posting_date, account, account_currency=None, company=None,
reference_type=None, reference_name=None, debit=None, credit=None, exchange_rate=None):
from erpnext.setup.utils import get_exchange_rate
account_details = frappe.db.get_value("Account", account,
@@ -842,9 +851,10 @@
(account_details.root_type == "Liability" and debit)):
exchange_rate = get_average_exchange_rate(account)
- if not exchange_rate and account_currency:
- exchange_rate = get_exchange_rate(account_currency, company_currency)
-
+ # The date used to retreive the exchange rate here is the date passed
+ # in as an argument to this function.
+ if not exchange_rate and account_currency and posting_date:
+ exchange_rate = get_exchange_rate(account_currency, company_currency, posting_date)
else:
exchange_rate = 1
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 1f6199e..d3dbd31 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -327,6 +327,7 @@
frappe.call({
method: "erpnext.setup.utils.get_exchange_rate",
args: {
+ transaction_date: frm.doc.posting_date,
from_currency: from_currency,
to_currency: to_currency
},
@@ -335,6 +336,10 @@
}
})
},
+
+ posting_date: function(frm) {
+ frm.events.paid_from_account_currency(frm);
+ },
source_exchange_rate: function(frm) {
if (frm.doc.paid_amount) {
@@ -425,6 +430,7 @@
method: 'erpnext.accounts.doctype.payment_entry.payment_entry.get_outstanding_reference_documents',
args: {
args: {
+ "posting_date": frm.doc.posting_date,
"company": frm.doc.company,
"party_type": frm.doc.party_type,
"payment_type": frm.doc.payment_type,
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index d4f8edb..3e10b51 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -153,11 +153,11 @@
self.source_exchange_rate = get_average_exchange_rate(self.paid_from)
else:
self.source_exchange_rate = get_exchange_rate(self.paid_from_account_currency,
- self.company_currency)
+ self.company_currency, self.posting_date)
if self.paid_to and not self.target_exchange_rate:
self.target_exchange_rate = get_exchange_rate(self.paid_to_account_currency,
- self.company_currency)
+ self.company_currency, self.posting_date)
def validate_mandatory(self):
for field in ("paid_amount", "received_amount", "source_exchange_rate", "target_exchange_rate"):
@@ -482,12 +482,12 @@
d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
- orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"),
+ orders_to_be_billed = get_orders_to_be_billed(args.get("posting_date"),args.get("party_type"), args.get("party"),
party_account_currency, company_currency)
return negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
-def get_orders_to_be_billed(party_type, party, party_account_currency, company_currency):
+def get_orders_to_be_billed(posting_date, party_type, party, party_account_currency, company_currency):
voucher_type = 'Sales Order' if party_type == "Customer" else 'Purchase Order'
ref_field = "base_grand_total" if party_account_currency == company_currency else "grand_total"
@@ -517,7 +517,9 @@
order_list = []
for d in orders:
d["voucher_type"] = voucher_type
- d["exchange_rate"] = get_exchange_rate(party_account_currency, company_currency)
+ # This assumes that the exchange rate required is the one in the SO
+ d["exchange_rate"] = get_exchange_rate(party_account_currency,
+ company_currency, posting_date)
order_list.append(d)
return order_list
@@ -592,14 +594,19 @@
exchange_rate = 1
else:
total_amount = ref_doc.grand_total
+
+ # Get the exchange rate from the original ref doc
+ # or get it based on the posting date of the ref doc
exchange_rate = ref_doc.get("conversion_rate") or \
- get_exchange_rate(party_account_currency, ref_doc.company_currency)
+ get_exchange_rate(party_account_currency, ref_doc.company_currency, ref_doc.posting_date)
outstanding_amount = ref_doc.get("outstanding_amount") \
if reference_doctype in ("Sales Invoice", "Purchase Invoice") \
else flt(total_amount) - flt(ref_doc.advance_paid)
else:
- exchange_rate = get_exchange_rate(party_account_currency, ref_doc.company_currency)
+ # Get the exchange rate based on the posting date of the ref doc
+ exchange_rate = get_exchange_rate(party_account_currency,
+ ref_doc.company_currency, ref_doc.posting_date)
return frappe._dict({
"due_date": ref_doc.get("due_date"),
diff --git a/erpnext/accounts/doctype/payment_request/test_payment_request.py b/erpnext/accounts/doctype/payment_request/test_payment_request.py
index 73c412f..bf3e24f 100644
--- a/erpnext/accounts/doctype/payment_request/test_payment_request.py
+++ b/erpnext/accounts/doctype/payment_request/test_payment_request.py
@@ -9,7 +9,6 @@
from erpnext.accounts.doctype.payment_request.payment_request import make_payment_request
from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
from erpnext.setup.utils import get_exchange_rate
-# test_records = frappe.get_test_records('Payment Request')
test_dependencies = ["Currency Exchange", "Journal Entry", "Contact", "Address"]
diff --git a/erpnext/accounts/doctype/sales_invoice/pos.py b/erpnext/accounts/doctype/sales_invoice/pos.py
index 4e4ad78..9fb11f2 100644
--- a/erpnext/accounts/doctype/sales_invoice/pos.py
+++ b/erpnext/accounts/doctype/sales_invoice/pos.py
@@ -3,7 +3,6 @@
from __future__ import unicode_literals
import frappe, json
-from frappe import _
from frappe.utils import nowdate
from erpnext.setup.utils import get_exchange_rate
from erpnext.stock.get_item_details import get_pos_profile
@@ -63,8 +62,10 @@
doc.currency = pos_profile.get('currency') or company_data.default_currency
doc.conversion_rate = 1.0
+
if doc.currency != company_data.default_currency:
- doc.conversion_rate = get_exchange_rate(doc.currency, company_data.default_currency)
+ doc.conversion_rate = get_exchange_rate(doc.currency, company_data.default_currency, doc.posting_date)
+
doc.selling_price_list = pos_profile.get('selling_price_list') or \
frappe.db.get_value('Selling Settings', None, 'selling_price_list')
doc.naming_series = pos_profile.get('naming_series') or 'SINV-'
diff --git a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
index 1793fc3..03f7a34 100644
--- a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
+++ b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
@@ -39,7 +39,7 @@
#Add a row for each supplier
for root in set(suppliers):
supplier_currency = frappe.db.get_value("Supplier",root,"default_currency")
- exg = get_exchange_rate(supplier_currency,company_currency)
+ exg = get_exchange_rate(supplier_currency, company_currency)
row = frappe._dict({
"supplier_name": root
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index 5796a4d..554529c 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -127,6 +127,11 @@
validate_due_date(self.posting_date, self.due_date, "Supplier", self.supplier, self.company)
def set_price_list_currency(self, buying_or_selling):
+ if self.meta.get_field("posting_date"):
+ transaction_date = self.posting_date
+ else:
+ transaction_date = self.transaction_date
+
if self.meta.get_field("currency"):
# price list part
fieldname = "selling_price_list" if buying_or_selling.lower() == "selling" \
@@ -139,8 +144,8 @@
self.plc_conversion_rate = 1.0
elif not self.plc_conversion_rate:
- self.plc_conversion_rate = get_exchange_rate(
- self.price_list_currency, self.company_currency)
+ self.plc_conversion_rate = get_exchange_rate(self.price_list_currency,
+ self.company_currency, transaction_date)
# currency
if not self.currency:
@@ -150,7 +155,7 @@
self.conversion_rate = 1.0
elif not self.conversion_rate:
self.conversion_rate = get_exchange_rate(self.currency,
- self.company_currency)
+ self.company_currency, transaction_date)
def set_missing_item_details(self, for_validate=False):
"""set missing item values"""
@@ -602,7 +607,6 @@
for item in duplicate_list:
self.remove(item)
-
@frappe.whitelist()
def get_tax_rate(account_head):
return frappe.db.get_value("Account", account_head, ["tax_rate", "account_name"], as_dict=True)
diff --git a/erpnext/crm/doctype/opportunity/opportunity.py b/erpnext/crm/doctype/opportunity/opportunity.py
index 6be0768..6ee9003 100644
--- a/erpnext/crm/doctype/opportunity/opportunity.py
+++ b/erpnext/crm/doctype/opportunity/opportunity.py
@@ -205,7 +205,8 @@
if company_currency == quotation.currency:
exchange_rate = 1
else:
- exchange_rate = get_exchange_rate(quotation.currency, company_currency)
+ exchange_rate = get_exchange_rate(quotation.currency, company_currency,
+ quotation.transaction_date)
quotation.conversion_rate = exchange_rate
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index c4b66e7..94212ec 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -354,4 +354,5 @@
erpnext.patches.v7_0.update_autoname_field
erpnext.patches.v7_1.update_bom_base_currency
erpnext.patches.v7_0.update_status_of_po_so
-erpnext.patches.v7_1.set_budget_against_as_cost_center
\ No newline at end of file
+erpnext.patches.v7_1.set_budget_against_as_cost_center
+erpnext.patches.v7_1.set_currency_exchange_date
\ No newline at end of file
diff --git a/erpnext/patches/v7_1/set_currency_exchange_date.py b/erpnext/patches/v7_1/set_currency_exchange_date.py
new file mode 100644
index 0000000..7d8e4f0
--- /dev/null
+++ b/erpnext/patches/v7_1/set_currency_exchange_date.py
@@ -0,0 +1,9 @@
+import frappe
+
+def execute():
+ frappe.reload_doctype("Currency Exchange")
+ frappe.db.sql("""
+ update `tabCurrency Exchange`
+ set `date` = '2010-01-01'
+ where date is null or date = '' or date = '0000-00-00'
+ """)
\ No newline at end of file
diff --git a/erpnext/public/js/controllers/transaction.js b/erpnext/public/js/controllers/transaction.js
index 954b03b..c31b0c8 100644
--- a/erpnext/public/js/controllers/transaction.js
+++ b/erpnext/public/js/controllers/transaction.js
@@ -413,6 +413,7 @@
transaction_date: function() {
if (this.frm.doc.transaction_date) {
this.frm.transaction_date = this.frm.doc.transaction_date;
+ frappe.ui.form.trigger(this.frm.doc.doctype, "currency");
}
},
@@ -434,9 +435,12 @@
callback: function(r, rt) {
if(r.message) {
me.frm.set_value("due_date", r.message);
+ frappe.ui.form.trigger(me.frm.doc.doctype, "currency");
}
}
})
+ } else {
+ frappe.ui.form.trigger(me.frm.doc.doctype, "currency");
}
}
},
@@ -450,6 +454,10 @@
},
currency: function() {
+ /* manqala 19/09/2016: let the translation date be whichever of the transaction_date or posting_date is available */
+ var transaction_date = this.frm.doc.transaction_date || this.frm.doc.posting_date;
+ /* end manqala */
+
var me = this;
this.set_dynamic_labels();
@@ -457,7 +465,7 @@
// Added `ignore_pricing_rule` to determine if document is loading after mapping from another doc
if(this.frm.doc.currency && this.frm.doc.currency !== company_currency
&& !this.frm.doc.ignore_pricing_rule) {
- this.get_exchange_rate(this.frm.doc.currency, company_currency,
+ this.get_exchange_rate(transaction_date, this.frm.doc.currency, company_currency,
function(exchange_rate) {
me.frm.set_value("conversion_rate", exchange_rate);
});
@@ -485,10 +493,11 @@
}
},
- get_exchange_rate: function(from_currency, to_currency, callback) {
+ get_exchange_rate: function(transaction_date, from_currency, to_currency, callback) {
return frappe.call({
method: "erpnext.setup.utils.get_exchange_rate",
args: {
+ transaction_date: transaction_date,
from_currency: from_currency,
to_currency: to_currency
},
@@ -505,7 +514,7 @@
var company_currency = this.get_company_currency();
// Added `ignore_pricing_rule` to determine if document is loading after mapping from another doc
if(this.frm.doc.price_list_currency !== company_currency && !this.frm.doc.ignore_pricing_rule) {
- this.get_exchange_rate(this.frm.doc.price_list_currency, company_currency,
+ this.get_exchange_rate(this.frm.doc.posting_date, this.frm.doc.price_list_currency, company_currency,
function(exchange_rate) {
me.frm.set_value("plc_conversion_rate", exchange_rate);
});
diff --git a/erpnext/setup/doctype/currency_exchange/currency_exchange.json b/erpnext/setup/doctype/currency_exchange/currency_exchange.json
index 44cea20..76e1a6b 100644
--- a/erpnext/setup/doctype/currency_exchange/currency_exchange.json
+++ b/erpnext/setup/doctype/currency_exchange/currency_exchange.json
@@ -17,6 +17,33 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "date",
+ "fieldtype": "Date",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Date",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 1,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0,
+ "width": "5"
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "from_currency",
"fieldtype": "Link",
"hidden": 0,
@@ -38,7 +65,8 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
- "unique": 0
+ "unique": 0,
+ "width": "3"
},
{
"allow_on_submit": 0,
@@ -66,7 +94,8 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
- "unique": 0
+ "unique": 0,
+ "width": "3"
},
{
"allow_on_submit": 0,
@@ -94,7 +123,8 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
- "unique": 0
+ "unique": 0,
+ "width": "3"
}
],
"hide_heading": 0,
@@ -108,7 +138,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2016-11-07 05:28:09.772560",
+ "modified": "2016-11-08 05:28:09.772560",
"modified_by": "Administrator",
"module": "Setup",
"name": "Currency Exchange",
@@ -202,5 +232,8 @@
"quick_entry": 1,
"read_only": 0,
"read_only_onload": 0,
+ "sort_field": "name",
+ "sort_order": "DESC",
+ "title_field": "",
"track_seen": 0
}
\ No newline at end of file
diff --git a/erpnext/setup/doctype/currency_exchange/currency_exchange.py b/erpnext/setup/doctype/currency_exchange/currency_exchange.py
index 6022812..7f1a43c 100644
--- a/erpnext/setup/doctype/currency_exchange/currency_exchange.py
+++ b/erpnext/setup/doctype/currency_exchange/currency_exchange.py
@@ -7,13 +7,15 @@
import frappe
from frappe import _
from frappe.model.document import Document
+from frappe.utils import get_datetime, get_datetime_str, formatdate
class CurrencyExchange(Document):
- def autoname(self):
- self.name = self.from_currency + "-" + self.to_currency
+ def autoname(self):
+ self.name = formatdate(get_datetime_str(self.date),"yyyy-MM-dd") + "-" + self.from_currency + "-" + self.to_currency
+ #self.name = self.date + "-" + self.from_currency + "-" + self.to_currency
- def validate(self):
- self.validate_value("exchange_rate", ">", 0)
+ def validate(self):
+ self.validate_value("exchange_rate", ">", 0)
- if self.from_currency == self.to_currency:
- frappe.throw(_("From Currency and To Currency cannot be same"))
+ if self.from_currency == self.to_currency:
+ frappe.throw(_("From Currency and To Currency cannot be same"))
diff --git a/erpnext/setup/doctype/currency_exchange/test_currency_exchange.py b/erpnext/setup/doctype/currency_exchange/test_currency_exchange.py
index 0077630..181f072 100644
--- a/erpnext/setup/doctype/currency_exchange/test_currency_exchange.py
+++ b/erpnext/setup/doctype/currency_exchange/test_currency_exchange.py
@@ -3,5 +3,18 @@
from __future__ import unicode_literals
-import frappe
-test_records = frappe.get_test_records('Currency Exchange')
\ No newline at end of file
+import frappe, unittest
+test_records = frappe.get_test_records('Currency Exchange')
+
+class TestCurrencyExchange(unittest.TestCase):
+ def test_exchnage_rate(self):
+ from erpnext.setup.utils import get_exchange_rate
+
+ # Exchange rate as on 15th Jan, 2016, should be fetched from Currency Exchange record
+ exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15")
+ self.assertEqual(exchange_rate, 60.0)
+
+ # Exchange rate as on 15th Dec, 2015, should be fetched from fixer.io
+ exchange_rate = get_exchange_rate("USD", "INR", "2015-12-15")
+ self.assertFalse(exchange_rate==60)
+
\ No newline at end of file
diff --git a/erpnext/setup/doctype/currency_exchange/test_records.json b/erpnext/setup/doctype/currency_exchange/test_records.json
index 784bf26..23edd8a 100644
--- a/erpnext/setup/doctype/currency_exchange/test_records.json
+++ b/erpnext/setup/doctype/currency_exchange/test_records.json
@@ -1,18 +1,21 @@
[
{
"doctype": "Currency Exchange",
+ "date": "2016-01-01",
"exchange_rate": 60.0,
"from_currency": "USD",
"to_currency": "INR"
},
{
"doctype": "Currency Exchange",
+ "date": "2016-01-01",
"exchange_rate": 0.773,
"from_currency": "USD",
"to_currency": "EUR"
},
{
"doctype": "Currency Exchange",
+ "date": "2016-01-01",
"exchange_rate": 0.0167,
"from_currency": "INR",
"to_currency": "USD"
diff --git a/erpnext/setup/utils.py b/erpnext/setup/utils.py
index eda2042..0c214e4 100644
--- a/erpnext/setup/utils.py
+++ b/erpnext/setup/utils.py
@@ -5,7 +5,8 @@
import frappe
from frappe import _, throw
from frappe.utils import flt
-
+from frappe.utils import get_datetime_str, nowdate
+
def get_company_currency(company):
currency = frappe.db.get_value("Company", company, "default_currency", cache=True)
if not currency:
@@ -64,36 +65,44 @@
frappe.db.commit()
@frappe.whitelist()
-def get_exchange_rate(from_currency, to_currency):
+def get_exchange_rate(from_currency, to_currency, transaction_date=None):
+ if not transaction_date:
+ transaction_date = nowdate()
if not (from_currency and to_currency):
+ # manqala 19/09/2016: Should this be an empty return or should it throw and exception?
return
if from_currency == to_currency:
return 1
- exchange = "%s-%s" % (from_currency, to_currency)
- value = flt(frappe.db.get_value("Currency Exchange", exchange, "exchange_rate"))
+ # cksgb 19/09/2016: get last entry in Currency Exchange with from_currency and to_currency.
+ entries = frappe.get_all("Currency Exchange", fields = ["exchange_rate"],
+ filters=[
+ ["date", "<=", get_datetime_str(transaction_date)],
+ ["from_currency", "=", from_currency],
+ ["to_currency", "=", to_currency]
+ ], order_by="date desc", limit=1)
+
+ if entries:
+ return flt(entries[0].exchange_rate)
- if not value:
- try:
- cache = frappe.cache()
- key = "currency_exchange_rate:{0}:{1}".format(from_currency, to_currency)
- value = cache.get(key)
+ try:
+ cache = frappe.cache()
+ key = "currency_exchange_rate:{0}:{1}".format(from_currency, to_currency)
+ value = cache.get(key)
- if not value:
- import requests
- response = requests.get("http://api.fixer.io/latest", params={
- "base": from_currency,
- "symbols": to_currency
- })
- # expire in 6 hours
- response.raise_for_status()
- value = response.json()["rates"][to_currency]
- cache.setex(key, value, 6 * 60 * 60)
+ if not value:
+ import requests
+ response = requests.get("http://api.fixer.io/latest", params={
+ "base": from_currency,
+ "symbols": to_currency
+ })
+ # expire in 6 hours
+ response.raise_for_status()
+ value = response.json()["rates"][to_currency]
+ cache.setex(key, value, 6 * 60 * 60)
- return flt(value)
- except:
- frappe.msgprint(_("Unable to find exchange rate for {0} to {1}").format(from_currency, to_currency))
- return 0.0
- else:
- return value
+ return flt(value)
+ except:
+ frappe.msgprint(_("Unable to find exchange rate for {0} to {1} for key date {2}").format(from_currency, to_currency, transaction_date))
+ return 0.0
\ No newline at end of file
diff --git a/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py b/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
index d8d00ef..6915ef5 100644
--- a/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
+++ b/erpnext/shopping_cart/doctype/shopping_cart_settings/shopping_cart_settings.py
@@ -8,6 +8,7 @@
from frappe import _, msgprint
from frappe.utils import comma_and
from frappe.model.document import Document
+from frappe.utils import get_datetime, get_datetime_str, now_datetime
class ShoppingCartSetupError(frappe.ValidationError): pass
@@ -38,11 +39,18 @@
expected_to_exist = [currency + "-" + company_currency
for currency in price_list_currency_map.values()
if currency != company_currency]
+
+ # manqala 20/09/2016: set up selection parameters for query from tabCurrency Exchange
+ from_currency = [currency for currency in price_list_currency_map.values() if currency != company_currency]
+ to_currency = company_currency
+ # manqala end
if expected_to_exist:
- exists = frappe.db.sql_list("""select name from `tabCurrency Exchange`
- where name in (%s)""" % (", ".join(["%s"]*len(expected_to_exist)),),
- tuple(expected_to_exist))
+ # manqala 20/09/2016: modify query so that it uses date in the selection from Currency Exchange.
+ # exchange rates defined with date less than the date on which this document is being saved will be selected
+ exists = frappe.db.sql_list("""select CONCAT(from_currency,'-',to_currency) from `tabCurrency Exchange`
+ where from_currency in (%s) and to_currency = "%s" and date <= curdate()""" % (", ".join(["%s"]*len(from_currency)), to_currency), tuple(from_currency))
+ # manqala end
missing = list(set(expected_to_exist).difference(exists))
diff --git a/erpnext/stock/doctype/material_request_item/material_request_item.json b/erpnext/stock/doctype/material_request_item/material_request_item.json
index f7ca7d9..b1d6ec0 100644
--- a/erpnext/stock/doctype/material_request_item/material_request_item.json
+++ b/erpnext/stock/doctype/material_request_item/material_request_item.json
@@ -23,6 +23,7 @@
"ignore_xss_filter": 0,
"in_filter": 1,
"in_list_view": 1,
+ "in_standard_filter": 0,
"label": "Item Code",
"length": 0,
"no_copy": 0,
@@ -34,6 +35,7 @@
"print_hide_if_no_value": 0,
"print_width": "100px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 1,
"search_index": 1,
@@ -53,12 +55,14 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -77,6 +81,7 @@
"ignore_xss_filter": 0,
"in_filter": 1,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Item Name",
"length": 0,
"no_copy": 0,
@@ -87,6 +92,7 @@
"print_hide_if_no_value": 0,
"print_width": "100px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 1,
@@ -106,6 +112,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Description",
"length": 0,
"no_copy": 0,
@@ -114,6 +121,7 @@
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -132,6 +140,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Description",
"length": 0,
"no_copy": 0,
@@ -142,6 +151,7 @@
"print_hide_if_no_value": 0,
"print_width": "250px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 1,
"search_index": 0,
@@ -161,6 +171,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -168,6 +179,7 @@
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -186,6 +198,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Image",
"length": 0,
"no_copy": 0,
@@ -194,6 +207,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -212,6 +226,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Quantity and Warehouse",
"length": 0,
"no_copy": 0,
@@ -219,6 +234,7 @@
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -237,6 +253,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
+ "in_standard_filter": 0,
"label": "Quantity",
"length": 0,
"no_copy": 0,
@@ -247,6 +264,7 @@
"print_hide_if_no_value": 0,
"print_width": "80px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 1,
"search_index": 0,
@@ -266,6 +284,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Stock UOM",
"length": 0,
"no_copy": 0,
@@ -277,6 +296,7 @@
"print_hide_if_no_value": 0,
"print_width": "70px",
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 1,
"search_index": 0,
@@ -296,6 +316,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
+ "in_standard_filter": 0,
"label": "For Warehouse",
"length": 0,
"no_copy": 0,
@@ -307,6 +328,7 @@
"print_hide_if_no_value": 0,
"print_width": "100px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -326,12 +348,14 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -351,6 +375,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
+ "in_standard_filter": 0,
"label": "Required Date",
"length": 0,
"no_copy": 0,
@@ -361,6 +386,7 @@
"print_hide_if_no_value": 0,
"print_width": "100px",
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 1,
"search_index": 0,
@@ -380,6 +406,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "More Information",
"length": 0,
"no_copy": 0,
@@ -387,6 +414,7 @@
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -406,6 +434,7 @@
"ignore_xss_filter": 0,
"in_filter": 1,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Item Group",
"length": 0,
"no_copy": 0,
@@ -416,6 +445,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 1,
@@ -434,6 +464,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Brand",
"length": 0,
"no_copy": 0,
@@ -445,6 +476,7 @@
"print_hide_if_no_value": 0,
"print_width": "100px",
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -464,6 +496,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Lead Time Date",
"length": 0,
"no_copy": 1,
@@ -473,6 +506,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -491,6 +525,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Sales Order",
"length": 0,
"no_copy": 0,
@@ -499,6 +534,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -517,6 +553,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Project",
"length": 0,
"no_copy": 0,
@@ -526,6 +563,7 @@
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -544,12 +582,14 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -568,6 +608,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Min Order Qty",
"length": 0,
"no_copy": 1,
@@ -578,6 +619,7 @@
"print_hide_if_no_value": 0,
"print_width": "70px",
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -597,6 +639,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Projected Qty",
"length": 0,
"no_copy": 1,
@@ -607,6 +650,7 @@
"print_hide_if_no_value": 0,
"print_width": "70px",
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -626,6 +670,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Actual Qty",
"length": 0,
"no_copy": 1,
@@ -634,6 +679,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 1,
"reqd": 0,
"search_index": 0,
@@ -652,6 +698,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Completed Qty",
"length": 0,
"no_copy": 1,
@@ -661,6 +708,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 1,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -679,6 +727,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Page Break",
"length": 0,
"no_copy": 1,
@@ -688,6 +737,7 @@
"print_hide": 1,
"print_hide_if_no_value": 0,
"read_only": 0,
+ "remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
@@ -705,7 +755,7 @@
"issingle": 0,
"istable": 1,
"max_attachments": 0,
- "modified": "2016-10-17 04:58:33.317145",
+ "modified": "2016-12-08 14:49:48.397015",
"modified_by": "Administrator",
"module": "Stock",
"name": "Material Request Item",
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 0caf1cd..16ea58e 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -481,7 +481,9 @@
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
- plc_conversion_rate = get_exchange_rate(price_list_currency, args.currency) or plc_conversion_rate
+ # cksgb 19/09/2016: added args.transaction_date as posting_date argument for get_exchange_rate
+ plc_conversion_rate = get_exchange_rate(price_list_currency, args.currency,
+ args.transaction_date) or plc_conversion_rate
return frappe._dict({
"price_list_currency": price_list_currency,