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()