def test_exchange_rate_strict_switched(self):
# Start with allow_stale is True
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15",
"for_buying")
self.assertEqual(exchange_rate, 65.1)
frappe.db.set_value("Accounts Settings", None, "allow_stale", 0)
frappe.db.set_value("Accounts Settings", None, "stale_days", 1)
# Will fetch from fixer.io
self.clear_cache()
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15",
"for_buying")
self.assertEqual(flt(exchange_rate, 3), 67.79)
def set_missing_values(source, target):
from erpbee.controllers.accounts_controller import get_default_taxes_and_charges
quotation = frappe.get_doc(target)
company_currency = frappe.get_cached_value('Company', quotation.company, "default_currency")
if quotation.quotation_to == 'Customer' and quotation.party_name:
party_account_currency = get_party_account_currency("Customer", quotation.party_name, quotation.company)
else:
party_account_currency = company_currency
quotation.currency = party_account_currency or company_currency
if company_currency == quotation.currency:
exchange_rate = 1
else:
exchange_rate = get_exchange_rate(quotation.currency, company_currency,
quotation.transaction_date, args="for_selling")
quotation.conversion_rate = exchange_rate
# get default taxes
taxes = get_default_taxes_and_charges("Sales Taxes and Charges Template", company=quotation.company)
if taxes.get('taxes'):
quotation.update(taxes)
quotation.run_method("set_missing_values")
quotation.run_method("calculate_taxes_and_totals")
if not source.with_items:
quotation.opportunity = source.name
def execute():
frappe.reload_doc("manufacturing", "doctype", "bom")
frappe.reload_doc("manufacturing", "doctype", "bom_item")
frappe.db.sql(""" UPDATE `tabBOM`, `tabPrice List`
SET
`tabBOM`.price_list_currency = `tabPrice List`.currency,
`tabBOM`.plc_conversion_rate = 1.0
WHERE
`tabBOM`.buying_price_list = `tabPrice List`.name AND `tabBOM`.docstatus < 2
AND `tabBOM`.rm_cost_as_per = 'Price List'
""")
for d in frappe.db.sql("""
SELECT
bom.creation, bom.name, bom.price_list_currency as currency,
company.default_currency as company_currency
FROM
`tabBOM` as bom, `tabCompany` as company
WHERE
bom.company = company.name AND bom.rm_cost_as_per = 'Price List' AND
bom.price_list_currency != company.default_currency AND bom.docstatus < 2""",
as_dict=1):
plc_conversion_rate = get_exchange_rate(d.currency, d.company_currency,
getdate(d.creation),
"for_buying")
frappe.db.set_value("BOM", d.name, "plc_conversion_rate",
plc_conversion_rate)
def make_quotation(domain):
# get open opportunites
opportunity = get_random("Opportunity", {
"status": "Open",
"with_items": 1
})
if opportunity:
from erpbee.crm.doctype.opportunity.opportunity import make_quotation
qtn = frappe.get_doc(make_quotation(opportunity))
qtn.insert()
frappe.db.commit()
qtn.submit()
frappe.db.commit()
else:
# make new directly
# get customer, currency and exchange_rate
customer = get_random("Customer")
company_currency = frappe.get_cached_value(
'Company', erpbee.get_default_company(), "default_currency")
party_account_currency = get_party_account_currency(
"Customer", customer, erpbee.get_default_company())
if company_currency == party_account_currency:
exchange_rate = 1
else:
exchange_rate = get_exchange_rate(party_account_currency,
company_currency,
args="for_selling")
qtn = frappe.get_doc({
"creation": frappe.flags.current_date,
"doctype": "Quotation",
"quotation_to": "Customer",
"party_name": customer,
"currency": party_account_currency or company_currency,
"conversion_rate": exchange_rate,
"order_type": "Sales",
"transaction_date": frappe.flags.current_date,
})
add_random_children(qtn,
"items",
rows=3,
randomize={
"qty": (1, 5),
"item_code": ("Item", {
"has_variants": "0",
"is_fixed_asset": 0,
"domain": domain
})
},
unique="item_code")
qtn.insert()
frappe.db.commit()
qtn.submit()
frappe.db.commit()
def test_exchange_rate(self):
save_new_records(test_records)
frappe.db.set_value("Accounts Settings", None, "allow_stale", 1)
# Start with allow_stale is True
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-01",
"for_buying")
self.assertEqual(flt(exchange_rate, 3), 60.0)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15",
"for_buying")
self.assertEqual(exchange_rate, 65.1)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-30",
"for_selling")
self.assertEqual(exchange_rate, 62.9)
# Exchange rate as on 15th Dec, 2015, should be fetched from fixer.io
self.clear_cache()
exchange_rate = get_exchange_rate("USD", "INR", "2015-12-15",
"for_selling")
self.assertFalse(exchange_rate == 60)
self.assertEqual(flt(exchange_rate, 3), 66.894)
def get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = erpbee.get_company_currency(self.company)
precision = get_field_precision(
frappe.get_meta("Exchange Rate Revaluation Account").get_field(
"new_balance_in_base_currency"), company_currency)
account_details = self.get_accounts_from_gle()
for d in account_details:
current_exchange_rate = d.balance / d.balance_in_account_currency \
if d.balance_in_account_currency else 0
new_exchange_rate = get_exchange_rate(d.account_currency,
company_currency,
self.posting_date)
new_balance_in_base_currency = flt(d.balance_in_account_currency *
new_exchange_rate)
gain_loss = flt(new_balance_in_base_currency, precision) - flt(
d.balance, precision)
if gain_loss:
accounts.append({
"account":
d.account,
"party_type":
d.party_type,
"party":
d.party,
"account_currency":
d.account_currency,
"balance_in_base_currency":
d.balance,
"balance_in_account_currency":
d.balance_in_account_currency,
"current_exchange_rate":
current_exchange_rate,
"new_exchange_rate":
new_exchange_rate,
"new_balance_in_base_currency":
new_balance_in_base_currency
})
if not accounts:
self.throw_invalid_response_message(account_details)
return accounts
def get_rate_as_at(date, from_currency, to_currency):
"""
Gets exchange rate as at `date` for `from_currency` - `to_currency` exchange rate.
This calls `get_exchange_rate` so that we can get the correct exchange rate as per
the user's Accounts Settings.
It is made efficient by memoising results to `__exchange_rates`
:param date: exchange rate as at this date
:param from_currency: Base currency
:param to_currency: Quote currency
:return: Retrieved exchange rate
"""
rate = __exchange_rates.get('{0}-{1}@{2}'.format(from_currency,
to_currency, date))
if not rate:
rate = get_exchange_rate(from_currency, to_currency, date) or 1
__exchange_rates['{0}-{1}@{2}'.format(from_currency, to_currency,
date)] = rate
return rate
def test_payment_request_linkings(self):
so_inr = make_sales_order(currency="INR")
pr = make_payment_request(dt="Sales Order",
dn=so_inr.name,
recipient_id="*****@*****.**")
self.assertEqual(pr.reference_doctype, "Sales Order")
self.assertEqual(pr.reference_name, so_inr.name)
self.assertEqual(pr.currency, "INR")
conversion_rate = get_exchange_rate("USD", "INR")
si_usd = create_sales_invoice(currency="USD",
conversion_rate=conversion_rate)
pr = make_payment_request(dt="Sales Invoice",
dn=si_usd.name,
recipient_id="*****@*****.**")
self.assertEqual(pr.reference_doctype, "Sales Invoice")
self.assertEqual(pr.reference_name, si_usd.name)
self.assertEqual(pr.currency, "USD")
def get_account_details(account,
company,
posting_date,
party_type=None,
party=None):
account_currency, account_type = frappe.db.get_value(
"Account", account, ["account_currency", "account_type"])
if account_type in ["Receivable", "Payable"
] and not (party_type and party):
frappe.throw(
_("Party Type and Party is mandatory for {0} account").format(
account_type))
account_details = {}
company_currency = erpbee.get_company_currency(company)
balance = get_balance_on(account,
party_type=party_type,
party=party,
in_account_currency=False)
if balance:
balance_in_account_currency = get_balance_on(account,
party_type=party_type,
party=party)
current_exchange_rate = balance / balance_in_account_currency if balance_in_account_currency else 0
new_exchange_rate = get_exchange_rate(account_currency,
company_currency, posting_date)
new_balance_in_base_currency = balance_in_account_currency * new_exchange_rate
account_details = {
"account_currency": account_currency,
"balance_in_base_currency": balance,
"balance_in_account_currency": balance_in_account_currency,
"current_exchange_rate": current_exchange_rate,
"new_exchange_rate": new_exchange_rate,
"new_balance_in_base_currency": new_balance_in_base_currency
}
return account_details
def test_exchange_rate_strict(self):
# strict currency settings
frappe.db.set_value("Accounts Settings", None, "allow_stale", 0)
frappe.db.set_value("Accounts Settings", None, "stale_days", 1)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-01",
"for_buying")
self.assertEqual(exchange_rate, 60.0)
# Will fetch from fixer.io
self.clear_cache()
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15",
"for_buying")
self.assertEqual(flt(exchange_rate, 3), 67.79)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-30",
"for_selling")
self.assertEqual(exchange_rate, 62.9)
# Exchange rate as on 15th Dec, 2015, should be fetched from fixer.io
self.clear_cache()
exchange_rate = get_exchange_rate("USD", "INR", "2015-12-15",
"for_buying")
self.assertEqual(flt(exchange_rate, 3), 66.894)
exchange_rate = get_exchange_rate("INR", "NGN", "2016-01-10",
"for_selling")
self.assertEqual(exchange_rate, 65.1)
# NGN is not available on fixer.io so these should return 0
exchange_rate = get_exchange_rate("INR", "NGN", "2016-01-09",
"for_selling")
self.assertEqual(exchange_rate, 0)
exchange_rate = get_exchange_rate("INR", "NGN", "2016-01-11",
"for_selling")
self.assertEqual(exchange_rate, 0)
def prepare_data(supplier_quotation_data, filters):
out, groups, qty_list, suppliers, chart_data = [], [], [], [], []
group_wise_map = defaultdict(list)
supplier_qty_price_map = {}
group_by_field = "supplier_name" if filters.get(
"group_by") == "Group by Supplier" else "item_code"
company_currency = frappe.db.get_default("currency")
float_precision = cint(frappe.db.get_default("float_precision")) or 2
for data in supplier_quotation_data:
group = data.get(
group_by_field) # get item or supplier value for this row
supplier_currency = frappe.db.get_value("Supplier",
data.get("supplier_name"),
"default_currency")
if supplier_currency:
exchange_rate = get_exchange_rate(supplier_currency,
company_currency)
else:
exchange_rate = 1
row = {
"item_code":
"" if group_by_field == "item_code" else
data.get("item_code"), # leave blank if group by field
"supplier_name":
"" if group_by_field == "supplier_name" else
data.get("supplier_name"),
"quotation":
data.get("parent"),
"qty":
data.get("qty"),
"price":
flt(data.get("amount") * exchange_rate, float_precision),
"uom":
data.get("uom"),
"stock_uom":
data.get('stock_uom'),
"request_for_quotation":
data.get("request_for_quotation"),
"valid_till":
data.get('valid_till'),
"lead_time_days":
data.get('lead_time_days')
}
row["price_per_unit"] = flt(row["price"]) / (flt(data.get("stock_qty"))
or 1)
# map for report view of form {'supplier1'/'item1':[{},{},...]}
group_wise_map[group].append(row)
# map for chart preparation of the form {'supplier1': {'qty': 'price'}}
supplier = data.get("supplier_name")
if filters.get("item_code"):
if not supplier in supplier_qty_price_map:
supplier_qty_price_map[supplier] = {}
supplier_qty_price_map[supplier][row["qty"]] = row["price"]
groups.append(group)
suppliers.append(supplier)
qty_list.append(data.get("qty"))
groups = list(set(groups))
suppliers = list(set(suppliers))
qty_list = list(set(qty_list))
highlight_min_price = group_by_field == "item_code" or filters.get(
"item_code")
# final data format for report view
for group in groups:
group_entries = group_wise_map[
group] # all entries pertaining to item/supplier
group_entries[0].update(
{group_by_field:
group}) # Add item/supplier name in first group row
if highlight_min_price:
prices = [
group_entry["price_per_unit"] for group_entry in group_entries
]
min_price = min(prices)
for entry in group_entries:
if highlight_min_price and entry["price_per_unit"] == min_price:
entry["min"] = 1
out.append(entry)
if filters.get("item_code"):
# render chart only for one item comparison
chart_data = prepare_chart_data(suppliers, qty_list,
supplier_qty_price_map)
return out, chart_data
def set_plc_conversion_rate(self): if self.rm_cost_as_per in ["Valuation Rate", "Last Purchase Rate"]: self.plc_conversion_rate = 1 elif not self.plc_conversion_rate and self.price_list_currency: self.plc_conversion_rate = get_exchange_rate(self.price_list_currency, self.company_currency(), args="for_buying")
def set_conversion_rate(self): if self.currency == self.company_currency(): self.conversion_rate = 1 elif self.conversion_rate == 1 or flt(self.conversion_rate) <= 0: self.conversion_rate = get_exchange_rate(self.currency, self.company_currency(), args="for_buying")
def work():
frappe.set_user(frappe.db.get_global('demo_purchase_user'))
if random.random() < 0.6:
report = "Items To Be Requested"
for row in query_report.run(report)["result"][:random.randint(1, 5)]:
item_code, qty = row[0], abs(row[-1])
mr = make_material_request(item_code, qty)
if random.random() < 0.6:
for mr in frappe.get_all('Material Request',
filters={
'material_request_type': 'Purchase',
'status': 'Open'
},
limit=random.randint(1, 6)):
if not frappe.get_all('Request for Quotation',
filters={'material_request': mr.name},
limit=1):
rfq = make_request_for_quotation(mr.name)
rfq.transaction_date = frappe.flags.current_date
add_suppliers(rfq)
rfq.save()
rfq.submit()
# Make suppier quotation from RFQ against each supplier.
if random.random() < 0.6:
for rfq in frappe.get_all('Request for Quotation',
filters={'status': 'Open'},
limit=random.randint(1, 6)):
if not frappe.get_all('Supplier Quotation',
filters={'request_for_quotation': rfq.name},
limit=1):
rfq = frappe.get_doc('Request for Quotation', rfq.name)
for supplier in rfq.suppliers:
supplier_quotation = make_supplier_quotation_from_rfq(
rfq.name, for_supplier=supplier.supplier)
supplier_quotation.save()
supplier_quotation.submit()
# get supplier details
supplier = get_random("Supplier")
company_currency = frappe.get_cached_value('Company',
erpbee.get_default_company(),
"default_currency")
party_account_currency = get_party_account_currency(
"Supplier", supplier, erpbee.get_default_company())
if company_currency == party_account_currency:
exchange_rate = 1
else:
exchange_rate = get_exchange_rate(party_account_currency,
company_currency,
args="for_buying")
# make supplier quotations
if random.random() < 0.5:
from erpbee.stock.doctype.material_request.material_request import make_supplier_quotation
report = "Material Requests for which Supplier Quotations are not created"
for row in query_report.run(report)["result"][:random.randint(1, 3)]:
if row[0] != "Total":
sq = frappe.get_doc(make_supplier_quotation(row[0]))
sq.transaction_date = frappe.flags.current_date
sq.supplier = supplier
sq.currency = party_account_currency or company_currency
sq.conversion_rate = exchange_rate
sq.insert()
sq.submit()
frappe.db.commit()
# make purchase orders
if random.random() < 0.5:
from erpbee.stock.doctype.material_request.material_request import make_purchase_order
report = "Requested Items To Be Ordered"
for row in query_report.run(
report)["result"][:how_many("Purchase Order")]:
if row[0] != "Total":
try:
po = frappe.get_doc(make_purchase_order(row[0]))
po.supplier = supplier
po.currency = party_account_currency or company_currency
po.conversion_rate = exchange_rate
po.transaction_date = frappe.flags.current_date
po.insert()
po.submit()
except Exception:
pass
else:
frappe.db.commit()
if random.random() < 0.5:
make_subcontract()