def get_reference_details(reference_doctype, reference_name, party_account_currency):
total_amount = outstanding_amount = exchange_rate = None
ref_doc = frappe.get_doc(reference_doctype, reference_name)
if reference_doctype != "Journal Entry":
if party_account_currency == ref_doc.company_currency:
total_amount = ref_doc.base_grand_total
exchange_rate = 1
else:
total_amount = ref_doc.grand_total
exchange_rate = ref_doc.get("conversion_rate") or \
get_exchange_rate(party_account_currency, ref_doc.company_currency)
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)
return frappe._dict({
"due_date": ref_doc.get("due_date"),
"total_amount": total_amount,
"outstanding_amount": outstanding_amount,
"exchange_rate": exchange_rate
})
def set_price_list_currency(self, buying_or_selling):
if self.meta.get_field("currency"):
company_currency = get_company_currency(self.company)
# price list part
fieldname = "selling_price_list" if buying_or_selling.lower() == "selling" \
else "buying_price_list"
if self.meta.get_field(fieldname) and self.get(fieldname):
self.price_list_currency = frappe.db.get_value("Price List",
self.get(fieldname), "currency")
if self.price_list_currency == company_currency:
self.plc_conversion_rate = 1.0
elif not self.plc_conversion_rate:
self.plc_conversion_rate = get_exchange_rate(
self.price_list_currency, company_currency)
# currency
if not self.currency:
self.currency = self.price_list_currency
self.conversion_rate = self.plc_conversion_rate
elif self.currency == company_currency:
self.conversion_rate = 1.0
elif not self.conversion_rate:
self.conversion_rate = get_exchange_rate(self.currency,
company_currency)
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
if buying_or_selling.lower() == "selling":
fieldname = "selling_price_list"
args = "for_selling"
else:
fieldname = "buying_price_list"
args = "for_buying"
if self.meta.get_field(fieldname) and self.get(fieldname):
self.price_list_currency = frappe.db.get_value("Price List",
self.get(fieldname), "currency")
if self.price_list_currency == self.company_currency:
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, transaction_date, args)
# currency
if not self.currency:
self.currency = self.price_list_currency
self.conversion_rate = self.plc_conversion_rate
elif self.currency == self.company_currency:
self.conversion_rate = 1.0
elif not self.conversion_rate:
self.conversion_rate = get_exchange_rate(self.currency,
self.company_currency, transaction_date, args)
def get_reference_details(reference_doctype, reference_name, party_account_currency):
total_amount = outstanding_amount = exchange_rate = None
ref_doc = frappe.get_doc(reference_doctype, reference_name)
if reference_doctype != "Journal Entry":
if party_account_currency == ref_doc.company_currency:
if ref_doc.doctype == "Expense Claim":
total_amount = ref_doc.total_sanctioned_amount
else:
total_amount = ref_doc.base_grand_total
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, ref_doc.posting_date)
outstanding_amount = ref_doc.get("outstanding_amount") \
if reference_doctype in ("Sales Invoice", "Purchase Invoice", "Expense Claim") \
else flt(total_amount) - flt(ref_doc.advance_paid)
else:
# 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"),
"total_amount": total_amount,
"outstanding_amount": outstanding_amount,
"exchange_rate": exchange_rate
})
def get_payment_entry(ref_doc, args):
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"),
ref_doc.company, ref_doc.doctype, ref_doc.name)
je = frappe.new_doc("Journal Entry")
je.update({
"voucher_type": "Bank Entry",
"company": ref_doc.company,
"remark": args.get("remarks")
})
party_row = je.append("accounts", {
"account": args.get("party_account"),
"party_type": args.get("party_type"),
"party": ref_doc.get(args.get("party_type").lower()),
"cost_center": cost_center,
"account_type": frappe.db.get_value("Account", args.get("party_account"), "account_type"),
"account_currency": args.get("party_account_currency") or \
get_account_currency(args.get("party_account")),
"balance": get_balance_on(args.get("party_account")),
"party_balance": get_balance_on(party=args.get("party"), party_type=args.get("party_type")),
"exchange_rate": exchange_rate,
args.get("amount_field_party"): args.get("amount"),
"is_advance": args.get("is_advance"),
"reference_type": ref_doc.doctype,
"reference_name": ref_doc.name
})
bank_row = je.append("accounts")
#make it bank_details
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"],
bank_account["account_currency"], ref_doc.company)
bank_row.cost_center = cost_center
amount = args.get("debit_in_account_currency") or args.get("amount")
if bank_row.account_currency == args.get("party_account_currency"):
bank_row.set(args.get("amount_field_bank"), amount)
else:
bank_row.set(args.get("amount_field_bank"), amount * exchange_rate)
# set multi currency check
if party_row.account_currency != ref_doc.company_currency \
or (bank_row.account_currency and bank_row.account_currency != ref_doc.company_currency):
je.multi_currency = 1
je.set_amounts_in_company_currency()
je.set_total_debit_credit()
return je if args.get("journal_entry") else je.as_dict()
def get_reference_details(reference_doctype, reference_name, party_account_currency):
total_amount = outstanding_amount = exchange_rate = bill_no = None
ref_doc = frappe.get_doc(reference_doctype, reference_name)
company_currency = ref_doc.get("company_currency") or erpnext.get_company_currency(ref_doc.company)
if reference_doctype == "Fees":
total_amount = ref_doc.get("grand_total")
exchange_rate = 1
outstanding_amount = ref_doc.get("outstanding_amount")
elif reference_doctype == "Journal Entry" and ref_doc.docstatus == 1:
total_amount = ref_doc.get("total_amount")
if ref_doc.multi_currency:
exchange_rate = get_exchange_rate(party_account_currency, company_currency, ref_doc.posting_date)
else:
exchange_rate = 1
outstanding_amount = get_outstanding_on_journal_entry(reference_name)
elif reference_doctype != "Journal Entry":
if party_account_currency == company_currency:
if ref_doc.doctype == "Expense Claim":
total_amount = ref_doc.total_sanctioned_amount
elif ref_doc.doctype == "Employee Advance":
total_amount = ref_doc.advance_amount
else:
total_amount = ref_doc.base_grand_total
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, company_currency, ref_doc.posting_date)
if reference_doctype in ("Sales Invoice", "Purchase Invoice"):
outstanding_amount = ref_doc.get("outstanding_amount")
bill_no = ref_doc.get("bill_no")
elif reference_doctype == "Expense Claim":
outstanding_amount = flt(ref_doc.get("total_sanctioned_amount")) \
- flt(ref_doc.get("total_amount+reimbursed")) - flt(ref_doc.get("total_advance_amount"))
elif reference_doctype == "Employee Advance":
outstanding_amount = ref_doc.advance_amount - flt(ref_doc.paid_amount)
else:
outstanding_amount = flt(total_amount) - flt(ref_doc.advance_paid)
else:
# Get the exchange rate based on the posting date of the ref doc
exchange_rate = get_exchange_rate(party_account_currency,
company_currency, ref_doc.posting_date)
return frappe._dict({
"due_date": ref_doc.get("due_date"),
"total_amount": total_amount,
"outstanding_amount": outstanding_amount,
"exchange_rate": exchange_rate,
"bill_no": bill_no
})
def set_exchange_rate(self): if self.paid_from and not self.source_exchange_rate: if self.paid_from_account_currency == self.company_currency: self.source_exchange_rate = 1 else: self.source_exchange_rate = get_exchange_rate(self.paid_from_account_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.posting_date)
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)
def test_exchange_rate_strict_switched(self):
# Start with allow_stale is True
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15")
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")
self.assertEqual(exchange_rate, 67.79)
def set_exchange_rate(self):
if self.paid_from and not self.source_exchange_rate:
if self.paid_from_account_currency == self.company_currency:
self.source_exchange_rate = 1
elif self.payment_type in ("Pay", "Internal Transfer"):
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)
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)
def get_exchange_rate(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,
["account_type", "root_type", "account_currency", "company"], as_dict=1)
if not account_details:
frappe.throw(_("Please select correct account"))
if not company:
company = account_details.company
if not account_currency:
account_currency = account_details.account_currency
company_currency = get_company_currency(company)
if account_currency != company_currency:
if reference_type in ("Sales Invoice", "Purchase Invoice") and reference_name:
exchange_rate = frappe.db.get_value(reference_type, reference_name, "conversion_rate")
elif account_details and account_details.account_type == "Bank" and \
((account_details.root_type == "Asset" and flt(credit) > 0) or
(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)
else:
exchange_rate = 1
# don't return None or 0 as it is multipled with a value and that value could be lost
return exchange_rate or 1
def get_exchange_rate(
account,
account_currency,
company,
reference_type=None,
reference_name=None,
debit=None,
credit=None,
exchange_rate=None,
):
from erpnext.setup.utils import get_exchange_rate
company_currency = get_company_currency(company)
account_details = frappe.db.get_value("Account", account, ["account_type", "root_type"], as_dict=1)
if account_currency != company_currency:
if reference_type in ("Sales Invoice", "Purchase Invoice") and reference_name:
exchange_rate = frappe.db.get_value(reference_type, reference_name, "conversion_rate")
elif account_details.account_type == "Bank" and (
(account_details.root_type == "Asset" and flt(credit) > 0)
or (account_details.root_type == "Liability" and debit)
):
exchange_rate = get_average_exchange_rate(account)
if not exchange_rate:
exchange_rate = get_exchange_rate(account_currency, company_currency)
else:
exchange_rate = 1
return exchange_rate
def get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = erpnext.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)
for d in self.get_accounts_from_gle():
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
})
return accounts
def set_missing_values(source, target):
from erpnext.controllers.accounts_controller import get_default_taxes_and_charges
quotation = frappe.get_doc(target)
company_currency = frappe.db.get_value("Company", quotation.company, "default_currency")
party_account_currency = get_party_account_currency("Customer", quotation.customer,
quotation.company) if quotation.customer else 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)
quotation.conversion_rate = exchange_rate
# get default taxes
taxes = get_default_taxes_and_charges("Sales Taxes and Charges Template")
if taxes:
quotation.extend("taxes", taxes)
quotation.run_method("set_missing_values")
quotation.run_method("calculate_taxes_and_totals")
if not source.with_items:
quotation.opportunity = source.name
def set_exchange_rate(self):
for d in self.get("accounts"):
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 and self.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:
frappe.throw(_("Row {0}: Exchange Rate is mandatory").format(d.idx))
def get_itemquantity_list(item, supplier_currency):
out = []
if item:
qty_list = frappe.db.sql(
"""select item_code, qty, rate from `tabSupplier Quotation Item` as item where parent =%s and docstatus = 1""",
item,
as_dict=1,
)
qty_list.sort(reverse=False)
company_currency = frappe.db.get_default("currency")
exg = get_exchange_rate(supplier_currency, company_currency) or 1
for qt in qty_list:
col = frappe._dict(
{
"key": str(qt.item_code) + " x" + str(qt.qty),
"item_code": qt.item_code,
"qty": qt.qty,
"rate": qt.rate * exg,
"label": str(qt.item_code) + " x" + str(qt.qty),
}
)
out.append(col)
return out
def get_account_balance_and_party_type(account, date, company, debit=None, credit=None, exchange_rate=None):
"""Returns dict of account balance and party type to be set in Journal Entry on selection of account."""
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
company_currency = erpnext.get_company_currency(company)
account_details = frappe.db.get_value("Account", account, ["account_type", "account_currency"], as_dict=1)
if not account_details:
return
if account_details.account_type == "Receivable":
party_type = "Customer"
elif account_details.account_type == "Payable":
party_type = "Supplier"
else:
party_type = ""
grid_values = {
"balance": get_balance_on(account, date),
"party_type": party_type,
"account_type": account_details.account_type,
"account_currency": account_details.account_currency or company_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)
}
# un-set party if not party type
if not party_type:
grid_values["party"] = ""
return grid_values
def test_payment_entry(self):
so_inr = make_sales_order(currency="INR")
pr = make_payment_request(dt="Sales Order", dn=so_inr.name, recipient_id="*****@*****.**",
mute_email=1, submit_doc=1)
jv = pr.set_as_paid()
so_inr = frappe.get_doc("Sales Order", so_inr.name)
self.assertEquals(so_inr.advance_paid, jv.total_debit)
conversion_rate = get_exchange_rate("USD", "INR")
si_usd = create_sales_invoice(customer="_Test Customer USD", debit_to="_Test Receivable USD - _TC",
currency="USD", conversion_rate=conversion_rate)
pr = make_payment_request(dt="Sales Invoice", dn=si_usd.name, recipient_id="*****@*****.**",
mute_email=1, return_doc=1, payment_gateway="_Test Gateway - USD")
jv = pr.set_as_paid()
payment_gateway_details = get_gateway_details({"payment_gateway": "_Test Gateway - USD"})
self.assertEquals(jv.accounts[0].account, "_Test Receivable USD - _TC")
self.assertEquals(jv.accounts[1].account, payment_gateway_details.payment_account)
def get_account_balance_and_party_type(account, date, company, debit=None, credit=None, exchange_rate=None):
"""Returns dict of account balance and party type to be set in Journal Entry on selection of account."""
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
company_currency = get_company_currency(company)
account_details = frappe.db.get_value("Account", account, ["account_type", "account_currency"], as_dict=1)
if account_details.account_type == "Receivable":
party_type = "Customer"
elif account_details.account_type == "Payable":
party_type = "Supplier"
else:
party_type = ""
grid_values = {
"balance": get_balance_on(account, date),
"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,
company, debit=debit, credit=credit, exchange_rate=exchange_rate)
}
# un-set party if not party type
if not party_type:
grid_values["party"] = ""
return grid_values
def get_price_list_currency_and_exchange_rate(args):
if not args.price_list:
return {}
if args.doctype in ['Quotation', 'Sales Order', 'Delivery Note', 'Sales Invoice']:
args.update({"exchange_rate": "for_selling"})
elif args.doctype in ['Purchase Order', 'Purchase Receipt', 'Purchase Invoice']:
args.update({"exchange_rate": "for_buying"})
price_list_currency = get_price_list_currency(args.price_list)
price_list_uom_dependant = get_price_list_uom_dependant(args.price_list)
plc_conversion_rate = args.plc_conversion_rate
company_currency = get_company_currency(args.company)
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
# 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, company_currency,
args.transaction_date, args.exchange_rate) or plc_conversion_rate
return frappe._dict({
"price_list_currency": price_list_currency,
"price_list_uom_dependant": price_list_uom_dependant,
"plc_conversion_rate": plc_conversion_rate
})
def update_pos_profile_data(doc, pos_profile, company_data):
doc.campaign = pos_profile.get('campaign')
if pos_profile and not pos_profile.get('country'):
pos_profile.country = company_data.country
doc.write_off_account = pos_profile.get('write_off_account') or \
company_data.write_off_account
doc.change_amount_account = pos_profile.get('change_amount_account') or \
company_data.default_cash_account
doc.taxes_and_charges = pos_profile.get('taxes_and_charges')
if doc.taxes_and_charges:
update_tax_table(doc)
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.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-'
doc.letter_head = pos_profile.get('letter_head') or company_data.default_letter_head
doc.ignore_pricing_rule = pos_profile.get('ignore_pricing_rule') or 0
doc.apply_discount_on = pos_profile.get('apply_discount_on') if pos_profile.get('apply_discount') else ''
doc.customer_group = pos_profile.get('customer_group') or get_root('Customer Group')
doc.territory = pos_profile.get('territory') or get_root('Territory')
doc.terms = frappe.db.get_value('Terms and Conditions', pos_profile.get('tc_name'), 'terms') or doc.terms or ''
def set_missing_values(source, target):
from erpnext.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 get_exchange_rate(posting_date, account=None, 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,
["account_type", "root_type", "account_currency", "company"], as_dict=1)
if not account_details:
frappe.throw(_("Please select correct account"))
if not company:
company = account_details.company
if not account_currency:
account_currency = account_details.account_currency
company_currency = erpnext.get_company_currency(company)
if account_currency != company_currency:
if reference_type in ("Sales Invoice", "Purchase Invoice") and reference_name:
exchange_rate = frappe.db.get_value(reference_type, reference_name, "conversion_rate")
# The date used to retreive the exchange rate here is the date passed
# in as an argument to this function.
elif (not exchange_rate or exchange_rate==1) and account_currency and posting_date:
exchange_rate = get_exchange_rate(account_currency, company_currency, posting_date)
else:
exchange_rate = 1
# don't return None or 0 as it is multipled with a value and that value could be lost
return exchange_rate or 1
def get_outstanding_reference_documents(args):
args = json.loads(args)
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency")
# Get negative outstanding sales /purchase invoices
total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total"
negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"),
args.get("party"), args.get("party_account"), total_field)
# Get positive outstanding sales /purchase invoices
outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"),
args.get("party_account"))
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_exchange_rate(
party_account_currency, company_currency, d.posting_date
)
# 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("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):
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"
orders = frappe.db.sql("""
select
name as voucher_no,
{ref_field} as invoice_amount,
({ref_field} - advance_paid) as outstanding_amount,
transaction_date as posting_date
from
`tab{voucher_type}`
where
{party_type} = %s
and docstatus = 1
and ifnull(status, "") != "Closed"
and {ref_field} > advance_paid
and abs(100 - per_billed) > 0.01
order by
transaction_date, name
""".format(**{
"ref_field": ref_field,
"voucher_type": voucher_type,
"party_type": scrub(party_type)
}), party, as_dict = True)
order_list = []
for d in orders:
d["voucher_type"] = voucher_type
d["exchange_rate"] = get_exchange_rate(party_account_currency, company_currency)
order_list.append(d)
return order_list
def get_outstanding_reference_documents(args):
if isinstance(args, string_types):
args = json.loads(args)
if args.get('party_type') == 'Member':
return
# confirm that Supplier is not blocked
if args.get('party_type') == 'Supplier':
supplier_status = get_supplier_block_status(args['party'])
if supplier_status['on_hold']:
if supplier_status['hold_type'] == 'All':
return []
elif supplier_status['hold_type'] == 'Payments':
if not supplier_status['release_date'] or getdate(nowdate()) <= supplier_status['release_date']:
return []
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.get_cached_value('Company', args.get("company"), "default_currency")
# Get negative outstanding sales /purchase invoices
negative_outstanding_invoices = []
if args.get("party_type") not in ["Student", "Employee"] and not args.get("voucher_no"):
negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"),
args.get("party"), args.get("party_account"), party_account_currency, company_currency)
# Get positive outstanding sales /purchase invoices/ Fees
condition = ""
if args.get("voucher_type") and args.get("voucher_no"):
condition = " and voucher_type='{0}' and voucher_no='{1}'"\
.format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"]))
# Add cost center condition
if args.get("cost_center") and get_allow_cost_center_in_entry_of_bs_account():
condition += " and cost_center='%s'" % args.get("cost_center")
outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"),
args.get("party_account"), condition=condition)
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_exchange_rate(
party_account_currency, company_currency, d.posting_date
)
if d.voucher_type in ("Purchase Invoice"):
d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = []
if (args.get("party_type") != "Student"):
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 test_exchnage_rate(self):
from erpnext.setup.utils import get_exchange_rate
save_new_records(test_records)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-01")
self.assertEqual(exchange_rate, 60.0)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15")
self.assertEqual(exchange_rate, 65.1)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-30")
self.assertEqual(exchange_rate, 62.9)
# 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)
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")
self.assertEqual(exchange_rate, 60.0)
# Will fetch from fixer.io
self.clear_cache()
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15")
self.assertEqual(exchange_rate, 67.79)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-30")
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")
self.assertEqual(exchange_rate, 66.894)
exchange_rate = get_exchange_rate("INR", "NGN", "2016-01-10")
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")
self.assertEqual(exchange_rate, 0)
exchange_rate = get_exchange_rate("INR", "NGN", "2016-01-11")
self.assertEqual(exchange_rate, 0)
def set_exchange_rate(self):
for d in self.get("accounts"):
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, d.reference_name, d.debit, d.credit, d.exchange_rate)
if not d.exchange_rate:
frappe.throw(_("Row {0}: Exchange Rate is mandatory").format(d.idx))
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")
self.assertEqual(exchange_rate, 60.0)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-15")
self.assertEqual(exchange_rate, 65.1)
exchange_rate = get_exchange_rate("USD", "INR", "2016-01-30")
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")
self.assertFalse(exchange_rate == 60)
self.assertEqual(exchange_rate, 66.894)
def get_outstanding_reference_documents(args):
if isinstance(args, basestring):
args = json.loads(args)
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency")
# Get negative outstanding sales /purchase invoices
negative_outstanding_invoices = []
if args.get("party_type") not in ["Student", "Employee"] and not args.get("voucher_no"):
negative_outstanding_invoices = get_negative_outstanding_invoices(args.get("party_type"),
args.get("party"), args.get("party_account"), party_account_currency, company_currency)
# Get positive outstanding sales /purchase invoices/ Fees
condition = ""
if args.get("voucher_type") and args.get("voucher_no"):
condition = " and voucher_type='{0}' and voucher_no='{1}'"\
.format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"]))
outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"),
args.get("party_account"), condition=condition)
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_exchange_rate(
party_account_currency, company_currency, d.posting_date
)
if d.voucher_type in ("Purchase Invoice"):
d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = []
if (args.get("party_type") != "Student"):
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_account_balance_and_party_type(account,
date,
company,
debit=None,
credit=None,
exchange_rate=None):
"""Returns dict of account balance and party type to be set in Journal Entry on selection of account."""
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
company_currency = get_company_currency(company)
account_details = frappe.db.get_value("Account",
account,
["account_type", "account_currency"],
as_dict=1)
if account_details.account_type == "Receivable":
party_type = "Customer"
elif account_details.account_type == "Payable":
party_type = "Supplier"
else:
party_type = ""
grid_values = {
"balance":
get_balance_on(account, date),
"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,
company,
debit=debit,
credit=credit,
exchange_rate=exchange_rate)
}
return grid_values
def set_missing_values(source, target):
quotation = frappe.get_doc(target)
company_currency = frappe.db.get_value("Company", quotation.company,
"default_currency")
party_account_currency = get_party_account_currency(
"Customer", quotation.customer,
quotation.company) if quotation.customer else 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.conversion_rate = exchange_rate
quotation.run_method("set_missing_values")
quotation.run_method("calculate_taxes_and_totals")
def update_pos_profile_data(doc, pos_profile, company_data):
doc.campaign = pos_profile.get('campaign')
print()
doc.write_off_account = pos_profile.get('write_off_account') or \
company_data.write_off_account
doc.change_amount_account = pos_profile.get('change_amount_account') or \
company_data.default_cash_account
doc.taxes_and_charges = pos_profile.get('taxes_and_charges')
doc.currency = pos_profile.get('currency') or company_data.default_currency
doc.conversion_rate = 1.0
doc.company_address = company_data.address
doc.company_email = company_data.email
doc.company_phone_no = company_data.phone_no
doc.company_website = company_data.website
if doc.currency != company_data.default_currency:
doc.conversion_rate = get_exchange_rate(doc.currency,
company_data.default_currency,
doc.posting_date)
doc.territory = pos_profile.get('territory') or get_root('Territory')
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.assertEquals(pr.reference_doctype, "Sales Order")
self.assertEquals(pr.reference_name, so_inr.name)
self.assertEquals(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.assertEquals(pr.reference_doctype, "Sales Invoice")
self.assertEquals(pr.reference_name, si_usd.name)
self.assertEquals(pr.currency, "USD")
def get_payment_entry(doc):
bank_account = get_default_bank_cash_account(doc.company, "Bank Entry")
jv = frappe.new_doc('Journal Entry')
jv.voucher_type = 'Bank Entry'
jv.company = doc.company
jv.fiscal_year = doc.fiscal_year
jv.append("accounts")
d2 = jv.append("accounts")
if bank_account:
d2.account = bank_account["account"]
d2.balance = bank_account["balance"]
d2.account_currency = bank_account["account_currency"]
d2.account_type = bank_account["account_type"]
d2.exchange_rate = get_exchange_rate(bank_account["account"],
bank_account["account_currency"],
doc.company)
return jv
def get_price_list_currency_and_exchange_rate(args):
if not args.price_list:
return {}
price_list_currency = get_price_list_currency(args.price_list)
price_list_uom_dependant = get_price_list_uom_dependant(args.price_list)
plc_conversion_rate = args.plc_conversion_rate
company_currency = get_company_currency(args.company)
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
# 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, company_currency,
args.transaction_date) or plc_conversion_rate
return frappe._dict({
"price_list_currency": price_list_currency,
"price_list_uom_dependant": price_list_uom_dependant,
"plc_conversion_rate": plc_conversion_rate
})
def get_activity_cost(employee=None, activity_type=None, currency=None):
base_currency = frappe.defaults.get_global_default("currency")
rate = frappe.db.get_values(
"Activity Cost",
{"employee": employee, "activity_type": activity_type},
["costing_rate", "billing_rate"],
as_dict=True,
)
if not rate:
rate = frappe.db.get_values(
"Activity Type",
{"activity_type": activity_type},
["costing_rate", "billing_rate"],
as_dict=True,
)
if rate and currency and currency != base_currency:
exchange_rate = get_exchange_rate(base_currency, currency)
rate[0]["costing_rate"] = rate[0]["costing_rate"] * exchange_rate
rate[0]["billing_rate"] = rate[0]["billing_rate"] * exchange_rate
return rate[0] if rate else {}
def get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = erpnext.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)
for d in self.get_accounts_from_gle():
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
})
return accounts
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"
orders = frappe.db.sql("""
select
name as voucher_no,
{ref_field} as invoice_amount,
({ref_field} - advance_paid) as outstanding_amount,
transaction_date as posting_date
from
`tab{voucher_type}`
where
{party_type} = %s
and docstatus = 1
and ifnull(status, "") != "Closed"
and {ref_field} > advance_paid
and abs(100 - per_billed) > 0.01
order by
transaction_date, name
""".format(
**{
"ref_field": ref_field,
"voucher_type": voucher_type,
"party_type": scrub(party_type)
}),
party,
as_dict=True)
order_list = []
for d in orders:
d["voucher_type"] = voucher_type
# 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
def update_item_prices(self):
unit_pcost = 0
total_time = 0
unit_acost = 0
for purchased in self.items:
purchased.qty = purchased.qty_per_asm * self.quantity
purchased.purchase_rate = get_item_price(purchased, self.company)
if not purchased.taxes:
purchased.taxes = 0
if not purchased.freight:
purchased.freight = 0
fields = ["purchase_rate", "taxes", "freight"]
from_currency = purchased.currency
to_currency = self.currency
conversion_rate = get_exchange_rate(from_currency, to_currency)
if not conversion_rate:
conversion_rate = 0
#frappe.msgprint(conversion_rate)
for f in fields:
val = flt(
flt(purchased.get(f), purchased.precision(f)) *
conversion_rate, purchased.precision("base_" + f))
purchased.set("base_" + f, val)
purchased.unit_price = purchased.purchase_rate + purchased.taxes + purchased.freight
purchased.base_unit_price = purchased.base_purchase_rate + purchased.base_taxes + purchased.base_freight
purchased.total_price = purchased.base_unit_price * purchased.qty_per_asm
unit_pcost = unit_pcost + (purchased.base_unit_price *
purchased.qty_per_asm)
for operations in self.operations:
total_time += operations.minutes
unit_acost += operations.total_cost
self.assembly_time = total_time
self.assembly_cost = unit_acost
self.purchased_cost = unit_pcost
self.unit_cost = self.purchased_cost + self.assembly_cost
self.total_cost = self.quantity * self.unit_cost
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 = erpnext.get_company_currency(company)
balance = get_balance_on(account,
date=posting_date,
party_type=party_type,
party=party,
in_account_currency=False)
if balance:
balance_in_account_currency = get_balance_on(account,
date=posting_date,
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 get_payment_entry_from_sales_invoice(sales_invoice):
"""Returns new Journal Entry document as dict for given Sales Invoice"""
from erpnext.accounts.utils import get_balance_on
si = frappe.get_doc("Sales Invoice", sales_invoice)
# exchange rate
exchange_rate = get_exchange_rate(si.debit_to, si.party_account_currency,
si.company, si.doctype, si.name)
jv = get_payment_entry(si)
jv.remark = 'Payment received against Sales Invoice {0}. {1}'.format(
si.name, si.remarks)
# credit customer
row1 = jv.get("accounts")[0]
row1.account = si.debit_to
row1.account_currency = si.party_account_currency
row1.party_type = "Customer"
row1.party = si.customer
row1.balance = get_balance_on(si.debit_to)
row1.party_balance = get_balance_on(party=si.customer,
party_type="Customer")
row1.credit_in_account_currency = si.outstanding_amount
row1.reference_type = si.doctype
row1.reference_name = si.name
row1.exchange_rate = exchange_rate
row1.account_type = "Receivable" if si.customer else ""
# debit bank
row2 = jv.get("accounts")[1]
if row2.account_currency == si.party_account_currency:
row2.debit_in_account_currency = si.outstanding_amount
else:
row2.debit_in_account_currency = si.outstanding_amount * exchange_rate
# set multi currency check
if row1.account_currency != si.company_currency or row2.account_currency != si.company_currency:
jv.multi_currency = 1
return jv.as_dict()
def get_outstanding_reference_documents(args):
args = json.loads(args)
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.db.get_value("Company", args.get("company"),
"default_currency")
# Get negative outstanding sales /purchase invoices
total_field = "base_grand_total" if party_account_currency == company_currency else "grand_total"
negative_outstanding_invoices = get_negative_outstanding_invoices(
args.get("party_type"), args.get("party"), args.get("party_account"),
total_field)
# Get positive outstanding sales /purchase invoices
outstanding_invoices = get_outstanding_invoices(args.get("party_type"),
args.get("party"),
args.get("party_account"))
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice",
"Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(
d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_exchange_rate(party_account_currency,
company_currency,
d.posting_date)
# 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("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_account_balance_and_party_type(account, date, company, debit=None, credit=None, exchange_rate=None, cost_center=None):
"""Returns dict of account balance and party type to be set in Journal Entry on selection of account."""
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
company_currency = erpnext.get_company_currency(company)
account_details = frappe.db.get_value("Account", account, ["account_type", "account_currency"], as_dict=1)
if not account_details:
return
if account_details.account_type == "Receivable":
party_type = "Customer"
elif account_details.account_type == "Payable":
default_letter_of_credit_account = frappe.get_cached_value('Company', {"company_name": company}, "default_letter_of_credit_account")
if account == default_letter_of_credit_account:
party_type = "Letter of Credit"
else:
party_type = "Supplier"
else:
party_type = ""
grid_values = {
"balance": get_balance_on(account, date, cost_center=cost_center),
"party_type": party_type,
"account_type": account_details.account_type,
"account_currency": account_details.account_currency or company_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)
}
# un-set party if not party type
if not party_type:
grid_values["party"] = ""
return grid_values
def get_exchange_rates():
from erpnext.setup.utils import get_exchange_rate
mops = frappe.db.sql("""
SELECT
name AS mode_of_payment,
alt_currency AS currency
FROM `tabMode of Payment`
WHERE in_alt_currency=1
""",
as_dict=1)
return {
mop.mode_of_payment: mop
for mop in map(
lambda x: _merge_dicts(
x, {
'conversion_rate':
get_exchange_rate(
x.currency,
frappe.defaults.get_user_default('currency'),
),
}), mops)
}
def get_payment_entry_from_purchase_invoice(purchase_invoice):
"""Returns new Journal Entry document as dict for given Purchase Invoice"""
pi = frappe.get_doc("Purchase Invoice", purchase_invoice)
exchange_rate = get_exchange_rate(pi.credit_to, pi.party_account_currency,
pi.company, pi.doctype, pi.name)
jv = get_payment_entry(pi)
jv.remark = 'Payment against Purchase Invoice {0}. {1}'.format(
pi.name, pi.remarks)
jv.exchange_rate = exchange_rate
# credit supplier
row1 = jv.get("accounts")[0]
row1.account = pi.credit_to
row1.account_currency = pi.party_account_currency
row1.party_type = "Supplier"
row1.party = pi.supplier
row1.balance = get_balance_on(pi.credit_to)
row1.party_balance = get_balance_on(party=pi.supplier,
party_type="Supplier")
row1.debit_in_account_currency = pi.outstanding_amount
row1.reference_type = pi.doctype
row1.reference_name = pi.name
row1.exchange_rate = exchange_rate
row1.account_type = "Payable" if pi.supplier else ""
# credit bank
row2 = jv.get("accounts")[1]
if row2.account_currency == pi.party_account_currency:
row2.credit_in_account_currency = pi.outstanding_amount
else:
row2.credit_in_account_currency = pi.outstanding_amount * exchange_rate
# set multi currency check
if row1.account_currency != pi.company_currency or row2.account_currency != pi.company_currency:
jv.multi_currency = 1
return jv.as_dict()
def update_pos_profile_data(doc, pos_profile):
company_data = frappe.db.get_value('Company', doc.company, '*', as_dict=1)
doc.taxes_and_charges = pos_profile.get('taxes_and_charges')
if doc.taxes_and_charges:
update_tax_table(doc)
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.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-'
doc.letter_head = pos_profile.get(
'letter_head') or company_data.default_letter_head
doc.ignore_pricing_rule = pos_profile.get('ignore_pricing_rule') or 0
doc.apply_discount_on = pos_profile.get('apply_discount_on') or ''
doc.customer_group = pos_profile.get('customer_group') or get_root(
'Customer Group')
doc.territory = pos_profile.get('territory') or get_root('Territory')
def update_pos_profile_data(doc, pos_profile, company_data):
doc.campaign = pos_profile.get('campaign')
doc.write_off_account = pos_profile.get('write_off_account') or \
company_data.write_off_account
doc.change_amount_account = pos_profile.get('change_amount_account') or \
company_data.default_cash_account
doc.taxes_and_charges = pos_profile.get('taxes_and_charges')
if doc.taxes_and_charges:
update_tax_table(doc)
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.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-'
doc.letter_head = pos_profile.get('letter_head') or company_data.default_letter_head
doc.ignore_pricing_rule = pos_profile.get('ignore_pricing_rule') or 0
doc.apply_discount_on = pos_profile.get('apply_discount_on') if pos_profile.get('apply_discount') else ''
doc.customer_group = pos_profile.get('customer_group') or get_root('Customer Group')
doc.territory = pos_profile.get('territory') or get_root('Territory')
def update_pos_profile_data(doc, pos_profile, company_data):
doc.campaign = pos_profile.get('campaign')
if pos_profile and not pos_profile.get('country'):
pos_profile.country = company_data.country
doc.write_off_account = pos_profile.get('write_off_account') or \
company_data.write_off_account
doc.change_amount_account = pos_profile.get('change_amount_account') or \
company_data.default_cash_account
doc.taxes_and_charges = pos_profile.get('taxes_and_charges')
if doc.taxes_and_charges:
update_tax_table(doc)
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.posting_date,
args="for_selling")
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-'
doc.letter_head = pos_profile.get(
'letter_head') or company_data.default_letter_head
doc.ignore_pricing_rule = pos_profile.get('ignore_pricing_rule') or 0
doc.apply_discount_on = pos_profile.get(
'apply_discount_on') or 'Grand Total'
doc.customer_group = pos_profile.get('customer_group') or get_root(
'Customer Group')
doc.territory = pos_profile.get('territory') or get_root('Territory')
doc.terms = frappe.db.get_value('Terms and Conditions',
pos_profile.get('tc_name'),
'terms') or doc.terms or ''
doc.offline_pos_name = ''
def set_missing_values(source, target):
from erpnext.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 validate_price_list_exchange_rate(self):
"Check if exchange rate exists for Price List currency (to Company's currency)."
from erpnext.setup.utils import get_exchange_rate
if not self.enabled or not self.company or not self.price_list:
return # this function is also called from hooks, check values again
company_currency = frappe.get_cached_value("Company", self.company,
"default_currency")
price_list_currency = frappe.db.get_value("Price List",
self.price_list, "currency")
if not company_currency:
msg = f"Please specify currency in Company {self.company}"
frappe.throw(_(msg),
title=_("Missing Currency"),
exc=ShoppingCartSetupError)
if not price_list_currency:
msg = f"Please specify currency in Price List {frappe.bold(self.price_list)}"
frappe.throw(_(msg),
title=_("Missing Currency"),
exc=ShoppingCartSetupError)
if price_list_currency != company_currency:
from_currency, to_currency = price_list_currency, company_currency
# Get exchange rate checks Currency Exchange Records too
exchange_rate = get_exchange_rate(from_currency,
to_currency,
args="for_selling")
if not flt(exchange_rate):
msg = f"Missing Currency Exchange Rates for {from_currency}-{to_currency}"
frappe.throw(_(msg),
title=_("Missing"),
exc=ShoppingCartSetupError)
def get_itemquantity_list(item, supplier_currency):
out = []
if item:
qty_list = frappe.db.sql("""select item_code, qty, rate from `tabSupplier Quotation Item` as item where parent =%s and docstatus = 1""", item, as_dict=1)
qty_list.sort(reverse=False)
company_currency = frappe.db.get_default("currency")
exg = get_exchange_rate(supplier_currency,company_currency) or 1
for qt in qty_list:
col = frappe._dict({
"key": str(qt.item_code) + " x" + str(qt.qty),
"item_code": qt.item_code,
"qty": qt.qty,
"rate": qt.rate * exg,
"label": str(qt.item_code) + " x" + str(qt.qty)
})
out.append(col)
return out
def get_item_list(quote, qty_list, mask):
out = []
if quote:
low_price_data = []
low_supplier = []
company = frappe.db.get_default("company")
company_currency = frappe.db.get_default("currency")
# Get the default supplier of suppliers
#Add a row for each item/qty
for root in qty_list:
root.supplier = frappe.db.get_value("Item", root.item_code,"default_supplier")
root.price_list = frappe.db.get_value("Supplier", root.supplier,"default_price_list")
supplier_currency = frappe.db.get_value("Supplier", root.supplier,"default_currency")
price = get_item_price(root,company)
exg = get_exchange_rate(supplier_currency,company_currency) or 1
if price:
percent = flt(100*(root.rate - price * exg) / price)
else:
percent = 0
row = frappe._dict({
"line_item": root.label,
"supplier_price": root.rate,
"lowest_price": price * exg,
"lowest_supplier": root.supplier,
"percent_diff": percent
})
if mask == 1:
row["lowest_supplier"] = "Redacted"
out.append(row)
return out
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 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 get_payment_entry(ref_doc, args):
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"),
ref_doc.company, ref_doc.doctype,
ref_doc.name)
je = frappe.new_doc("Journal Entry")
je.update({
"voucher_type": "Bank Entry",
"company": ref_doc.company,
"remark": args.get("remarks")
})
party_row = je.append("accounts", {
"account": args.get("party_account"),
"party_type": args.get("party_type"),
"party": ref_doc.get(args.get("party_type").lower()),
"cost_center": cost_center,
"account_type": frappe.db.get_value("Account", args.get("party_account"), "account_type"),
"account_currency": args.get("party_account_currency") or \
get_account_currency(args.get("party_account")),
"balance": get_balance_on(args.get("party_account")),
"party_balance": get_balance_on(party=args.get("party"), party_type=args.get("party_type")),
"exchange_rate": exchange_rate,
args.get("amount_field_party"): args.get("amount"),
"is_advance": args.get("is_advance"),
"reference_type": ref_doc.doctype,
"reference_name": ref_doc.name
})
bank_row = je.append("accounts")
#make it bank_details
bank_account = get_default_bank_cash_account(
ref_doc.company, "Bank Entry", account=args.get("bank_account"))
if bank_account:
bank_row.update(bank_account)
bank_row.exchange_rate = get_exchange_rate(
bank_account["account"], bank_account["account_currency"],
ref_doc.company)
bank_row.cost_center = cost_center
amount = args.get("debit_in_account_currency") or args.get("amount")
if bank_row.account_currency == args.get("party_account_currency"):
bank_row.set(args.get("amount_field_bank"), amount)
else:
bank_row.set(args.get("amount_field_bank"), amount * exchange_rate)
# set multi currency check
if party_row.account_currency != ref_doc.company_currency \
or (bank_row.account_currency and bank_row.account_currency != ref_doc.company_currency):
je.multi_currency = 1
je.set_amounts_in_company_currency()
je.set_total_debit_credit()
return je if args.get("journal_entry") else je.as_dict()
def get_outstanding_reference_documents(args):
if isinstance(args, string_types):
args = json.loads(args)
# confirm that Supplier is not blocked
if args.get('party_type') == 'Supplier':
supplier_status = get_supplier_block_status(args['party'])
if supplier_status['on_hold']:
if supplier_status['hold_type'] == 'All':
return []
elif supplier_status['hold_type'] == 'Payments':
if not supplier_status['release_date'] or getdate(
nowdate()) <= supplier_status['release_date']:
return []
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.db.get_value("Company", args.get("company"),
"default_currency")
# Get negative outstanding sales /purchase invoices
negative_outstanding_invoices = []
if args.get("party_type") not in ["Student", "Employee"
] and not args.get("voucher_no"):
negative_outstanding_invoices = get_negative_outstanding_invoices(
args.get("party_type"), args.get("party"),
args.get("party_account"), party_account_currency,
company_currency)
# Get positive outstanding sales /purchase invoices/ Fees
condition = ""
if args.get("voucher_type") and args.get("voucher_no"):
condition = " and voucher_type='{0}' and voucher_no='{1}'"\
.format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"]))
outstanding_invoices = get_outstanding_invoices(args.get("party_type"),
args.get("party"),
args.get("party_account"),
condition=condition)
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice",
"Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(
d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_exchange_rate(party_account_currency,
company_currency,
d.posting_date)
if d.voucher_type in ("Purchase Invoice"):
d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no,
"bill_no")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = []
if (args.get("party_type") != "Student"):
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 run():
frappe.set_user("*****@*****.**")
frappe.set_user_lang("fr")
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_quotation_from_rfq(rfq.name, supplier.supplier)
supplier_quotation.save()
supplier_quotation.submit()
# get supplier details
supplier = get_random("Supplier")
company_currency = frappe.get_cached_value('Company', erpnext.get_default_company(), "default_currency")
party_account_currency = get_party_account_currency("Supplier", supplier, erpnext.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 erpnext.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 erpnext.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()