def get_payment_entry_from_purchase_order(purchase_order): """Returns new Journal Entry document as dict for given Sales Order""" from erpnext.accounts.utils import get_balance_on from erpnext.accounts.party import get_party_account po = frappe.get_doc("Purchase Order", purchase_order) if flt(po.per_billed, 2) != 0.0: frappe.throw(_("Can only make payment against unbilled Sales Order")) jv = get_payment_entry(po) jv.remark = 'Advance payment made against Purchase Order {0}.'.format(po.name) party_account = get_party_account(po.company, po.supplier, "Supplier") amount = flt(po.base_grand_total) - flt(po.advance_paid) # credit customer jv.get("accounts")[0].account = party_account jv.get("accounts")[0].party_type = "Supplier" jv.get("accounts")[0].party = po.supplier jv.get("accounts")[0].balance = get_balance_on(party_account) jv.get("accounts")[0].party_balance = get_balance_on(party=po.supplier, party_type="Supplier") jv.get("accounts")[0].debit = amount jv.get("accounts")[0].reference_type = po.doctype jv.get("accounts")[0].reference_name = po.name jv.get("accounts")[0].is_advance = "Yes" # debit bank jv.get("accounts")[1].credit = amount return jv.as_dict()
def get_type_balance(self, fieldname, account_type, root_type=None): if root_type: accounts = [d.name for d in \ frappe.db.get_all("Account", filters={"account_type": account_type, "company": self.company, "is_group": 0, "root_type": root_type})] else: accounts = [d.name for d in \ frappe.db.get_all("Account", filters={"account_type": account_type, "company": self.company, "is_group": 0})] balance = prev_balance = 0.0 count = 0 for account in accounts: balance += get_balance_on(account, date=self.future_to_date) count += get_count_on(account, fieldname, date=self.future_to_date) prev_balance += get_balance_on(account, date=self.past_to_date) if fieldname in ("bank_balance","credit_balance"): return { 'label': self.meta.get_label(fieldname), 'value': balance, 'last_value': prev_balance } else: return { 'label': self.meta.get_label(fieldname), 'value': balance, 'last_value': prev_balance, 'count': count }
def get_default_bank_cash_account(company, voucher_type, mode_of_payment=None, account=None): from erpnext.accounts.doctype.sales_invoice.sales_invoice import get_bank_cash_account if mode_of_payment: account = get_bank_cash_account(mode_of_payment, company) if account.get("account"): account.update({"balance": get_balance_on(account.get("account"))}) return account if not account: if voucher_type=="Bank Entry": account = frappe.db.get_value("Company", company, "default_bank_account") if not account: account = frappe.db.get_value("Account", {"company": company, "account_type": "Bank", "is_group": 0}) elif voucher_type=="Cash Entry": account = frappe.db.get_value("Company", company, "default_cash_account") if not account: account = frappe.db.get_value("Account", {"company": company, "account_type": "Cash", "is_group": 0}) if account: account_details = frappe.db.get_value("Account", account, ["account_currency", "account_type"], as_dict=1) return { "account": account, "balance": get_balance_on(account), "account_currency": account_details.account_currency, "account_type": account_details.account_type }
def get_gl_entries(filters): gl_entries = frappe.db.sql("""select posting_date, account, sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit, voucher_type, voucher_no,party from `tabGL Entry` where company=%(company)s {conditions} and posting_date <= %(to_date)s or ifnull(is_opening, 'No') = 'Yes' group by account order by posting_date , account"""\ .format(conditions=get_conditions(filters)), filters ,as_dict=1) openind_date= getdate(filters.get("from_date")) date=openind_date-timedelta(days=1) if gl_entries: for i in gl_entries: if i.get("account"): opening_bal=get_balance_on(i.get("account"),date,party=None,party_type=None) i['opening']=opening_bal if i['opening']<0: i['opening']*=(-1) closing_bal=get_balance_on(i.get("account"),filters.get("to_date"),party=None,party_type=None) i['closing']=closing_bal if i['closing']<0: i['closing']*=(-1) return gl_entries
def get_average_exchange_rate(account): exchange_rate = 0 bank_balance_in_account_currency = get_balance_on(account) if bank_balance_in_account_currency: bank_balance_in_company_currency = get_balance_on(account, in_account_currency=False) exchange_rate = bank_balance_in_company_currency / bank_balance_in_account_currency return 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_party_account_and_balance(company, party_type, party): from erpnext.accounts.party import get_party_account account = get_party_account(company, party, party_type) account_balance = get_balance_on(account=account) party_balance = get_balance_on(party_type=party_type, party=party) return {"account": account, "balance": account_balance, "party_balance": party_balance}
def get_party_account_and_balance(company, party_type, party): if not frappe.has_permission("Account"): frappe.msgprint(_("No Permission"), raise_exception=1) account = get_party_account(party_type, party, company) account_balance = get_balance_on(account=account) party_balance = get_balance_on(party_type=party_type, party=party) return {"account": account, "balance": account_balance, "party_balance": party_balance}
def test_delivery_note_gl_entry(self): self.clear_stock_account_balance() set_perpetual_inventory() self.assertEqual(cint(frappe.defaults.get_global_default("auto_accounting_for_stock")), 1) frappe.db.set_value("Item", "_Test Item", "valuation_method", "FIFO") _insert_purchase_receipt() dn = frappe.copy_doc(test_records[0]) dn.get("delivery_note_details")[0].expense_account = "Cost of Goods Sold - _TC" dn.get("delivery_note_details")[0].cost_center = "Main - _TC" stock_in_hand_account = frappe.db.get_value("Account", {"master_name": dn.get("delivery_note_details")[0].warehouse}) from erpnext.accounts.utils import get_balance_on prev_bal = get_balance_on(stock_in_hand_account, dn.posting_date) dn.insert() dn.submit() gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) expected_values = { stock_in_hand_account: [0.0, 375.0], "Cost of Goods Sold - _TC": [375.0, 0.0] } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) # check stock in hand balance bal = get_balance_on(stock_in_hand_account, dn.posting_date) self.assertEquals(bal, prev_bal - 375.0) # back dated purchase receipt pr = frappe.copy_doc(pr_test_records[0]) pr.posting_date = "2013-01-01" pr.get("purchase_receipt_details")[0].rate = 100 pr.get("purchase_receipt_details")[0].base_amount = 100 pr.insert() pr.submit() gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) expected_values = { stock_in_hand_account: [0.0, 666.67], "Cost of Goods Sold - _TC": [666.67, 0.0] } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) dn.cancel() self.assertFalse(get_gl_entries("Delivery Note", dn.name)) set_perpetual_inventory(0)
def get_period_amounts(self, accounts): """Get amounts for current and past periods""" balance = past_balance = 0.0 for account in accounts: balance += (get_balance_on(account, date = self.future_to_date) - get_balance_on(account, date = self.future_from_date)) past_balance += (get_balance_on(account, date = self.past_to_date) - get_balance_on(account, date = self.past_from_date)) return balance, past_balance
def test_delivery_note_gl_entry(self): set_perpetual_inventory() self.assertEqual(cint(frappe.defaults.get_global_default("auto_accounting_for_stock")), 1) set_valuation_method("_Test Item", "FIFO") make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100) stock_in_hand_account = frappe.db.get_value("Account", {"warehouse": "_Test Warehouse - _TC"}) prev_bal = get_balance_on(stock_in_hand_account) dn = create_delivery_note() gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) stock_value_difference = abs( frappe.db.get_value( "Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name}, "stock_value_difference" ) ) expected_values = { stock_in_hand_account: [0.0, stock_value_difference], "Cost of Goods Sold - _TC": [stock_value_difference, 0.0], } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) # check stock in hand balance bal = get_balance_on(stock_in_hand_account) self.assertEquals(bal, prev_bal - stock_value_difference) # back dated incoming entry make_stock_entry(posting_date=add_days(nowdate(), -2), target="_Test Warehouse - _TC", qty=5, basic_rate=100) gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) stock_value_difference = abs( frappe.db.get_value( "Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name}, "stock_value_difference" ) ) expected_values = { stock_in_hand_account: [0.0, stock_value_difference], "Cost of Goods Sold - _TC": [stock_value_difference, 0.0], } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) dn.cancel() self.assertFalse(get_gl_entries("Delivery Note", dn.name)) set_perpetual_inventory(0)
def get_data(filters): data = [ ['Balance as at ' + str(frappe.utils.get_datetime(filters.from_date).strftime('%d %B, %Y')),"","","","","","","","","",""], ['Profit/Loss after Income tax',"","","","","",str(get_balance_on("Retain Earnings - SMCL")),"","","",""], ['Share allotment aganist money recieved from DHI',str(get_balance_on("Paid Up Capital - SMCL")/100)[1:],"100",str(get_balance_on("Paid Up Capital - SMCL"))[1:],"","","","","","",""], ['Dividend for the Financial Year',"","","","","",str(get_balance_on("Proposed Dividend (Current Year) - SMCL")),"","","",""], ['Transfer to Group Investment Reserve',"","","","","","",str(get_balance_on("General Reserves - SMCL")),"","",""], ['Other Comprehensive Income for the Year',"","","","","","","","","",""], ['Balance as at ' + str(frappe.utils.get_datetime(filters.to_date).strftime('%d %B, %Y')),"","","","","","","","","",""] ]; return data
def set_account_and_party_balance(self): account_balance = {} party_balance = {} for d in self.get("accounts"): if d.account not in account_balance: account_balance[d.account] = get_balance_on(account=d.account, date=self.posting_date) if (d.party_type, d.party) not in party_balance: party_balance[(d.party_type, d.party)] = get_balance_on(party_type=d.party_type, party=d.party, date=self.posting_date, company=self.company) d.account_balance = account_balance[d.account] d.party_balance = party_balance[(d.party_type, d.party)]
def get_payment_entry_from_sales_order(sales_order): """Returns new Journal Entry document as dict for given Sales Order""" from erpnext.accounts.utils import get_balance_on from erpnext.accounts.party import get_party_account so = frappe.get_doc("Sales Order", sales_order) if flt(so.per_billed, 2) != 0.0: frappe.throw(_("Can only make payment against unbilled Sales Order")) jv = get_payment_entry(so) jv.remark = 'Advance payment received against Sales Order {0}.'.format(so.name) party_account = get_party_account("Customer", so.customer, so.company) party_account_currency = get_account_currency(party_account) exchange_rate = get_exchange_rate(party_account, party_account_currency, so.company) if party_account_currency == so.company_currency: amount = flt(so.base_grand_total) - flt(so.advance_paid) else: amount = flt(so.grand_total) - flt(so.advance_paid) # credit customer row1 = jv.get("accounts")[0] row1.account = party_account row1.account_currency = party_account_currency row1.party_type = "Customer" row1.party = so.customer row1.balance = get_balance_on(party_account) row1.party_balance = get_balance_on(party=so.customer, party_type="Customer") row1.credit_in_account_currency = amount row1.reference_type = so.doctype row1.reference_name = so.name row1.is_advance = "Yes" row1.exchange_rate = exchange_rate row1.account_type = "Receivable" # debit bank row2 = jv.get("accounts")[1] if row2.account_currency == party_account_currency: row2.debit_in_account_currency = amount else: row2.debit_in_account_currency = amount * exchange_rate # set multi currency check if row1.account_currency != so.company_currency or row2.account_currency != so.company_currency: jv.multi_currency = 1 return jv.as_dict()
def get_type_balance(self, fieldname, account_type): accounts = [ d.name for d in frappe.db.get_all( "Account", filters={"account_type": account_type, "company": self.company, "is_group": 0} ) ] balance = prev_balance = 0.0 for account in accounts: balance += get_balance_on(account, date=self.future_from_date) prev_balance += get_balance_on(account, date=self.past_from_date) return {"label": self.meta.get_label(fieldname), "value": balance, "last_value": prev_balance}
def test_delivery_note_gl_entry_packing_item(self): set_perpetual_inventory() make_stock_entry(item_code="_Test Item", target="_Test Warehouse - _TC", qty=10, basic_rate=100) make_stock_entry( item_code="_Test Item Home Desktop 100", target="_Test Warehouse - _TC", qty=10, basic_rate=100 ) stock_in_hand_account = frappe.db.get_value("Account", {"warehouse": "_Test Warehouse - _TC"}) prev_bal = get_balance_on(stock_in_hand_account) dn = create_delivery_note(item_code="_Test Product Bundle Item") stock_value_diff_rm1 = abs( frappe.db.get_value( "Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name, "item_code": "_Test Item"}, "stock_value_difference", ) ) stock_value_diff_rm2 = abs( frappe.db.get_value( "Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name, "item_code": "_Test Item Home Desktop 100"}, "stock_value_difference", ) ) stock_value_diff = stock_value_diff_rm1 + stock_value_diff_rm2 gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) expected_values = { stock_in_hand_account: [0.0, stock_value_diff], "Cost of Goods Sold - _TC": [stock_value_diff, 0.0], } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) # check stock in hand balance bal = get_balance_on(stock_in_hand_account) self.assertEquals(flt(bal, 2), flt(prev_bal - stock_value_diff, 2)) dn.cancel() self.assertFalse(get_gl_entries("Delivery Note", dn.name)) set_perpetual_inventory(0)
def get_payment_entry_from_purchase_order(purchase_order): """Returns new Journal Entry document as dict for given Sales Order""" from erpnext.accounts.utils import get_balance_on from erpnext.accounts.party import get_party_account po = frappe.get_doc("Purchase Order", purchase_order) if flt(po.per_billed, 2) != 0.0: frappe.throw(_("Can only make payment against unbilled Sales Order")) jv = get_payment_entry(po) jv.remark = "Advance payment made against Purchase Order {0}.".format(po.name) party_account = get_party_account(po.company, po.supplier, "Supplier") party_account_currency = frappe.db.get_value("Account", party_account, "account_currency") exchange_rate = get_exchange_rate(party_account, party_account_currency, po.company) if party_account_currency == po.company_currency: amount = flt(po.base_grand_total) - flt(po.advance_paid) else: amount = flt(po.grand_total) - flt(po.advance_paid) # credit customer row1 = jv.get("accounts")[0] row1.account = party_account row1.party_type = "Supplier" row1.party = po.supplier row1.balance = get_balance_on(party_account) row1.party_balance = get_balance_on(party=po.supplier, party_type="Supplier") row1.debit_in_account_currency = amount row1.reference_type = po.doctype row1.reference_name = po.name row1.is_advance = "Yes" row1.exchange_rate = exchange_rate row1.account_type = "Payable" # debit bank row2 = jv.get("accounts")[1] if row2.account_currency == party_account_currency: row2.credit_in_account_currency = amount else: row2.credit_in_account_currency = amount * exchange_rate # set multi currency check if row1.account_currency != po.company_currency or row2.account_currency != po.company_currency: jv.multi_currency = 1 return jv.as_dict()
def get_party_account_and_balance(company, party_type, party, cost_center=None): if not frappe.has_permission("Account"): frappe.msgprint(_("No Permission"), raise_exception=1) account = get_party_account(party_type, party, company) account_balance = get_balance_on(account=account, cost_center=cost_center) party_balance = get_balance_on(party_type=party_type, party=party, company=company, cost_center=cost_center) return { "account": account, "balance": account_balance, "party_balance": party_balance, "account_currency": frappe.db.get_value("Account", account, "account_currency") }
def get_period_amounts(self, accounts, fieldname): """Get amounts for current and past periods""" balance = past_balance = 0.0 count = 0 for account in accounts: balance += (get_balance_on(account, date = self.future_to_date) - get_balance_on(account, date = self.future_from_date - timedelta(days=1))) count += (get_count_on(account,fieldname, date = self.future_to_date ) - get_count_on(account,fieldname, date = self.future_from_date - timedelta(days=1))) past_balance += (get_balance_on(account, date = self.past_to_date) - get_balance_on(account, date = self.past_from_date - timedelta(days=1))) return balance, past_balance, count
def get_opening_accounts(company): """get all balance sheet accounts for opening entry""" from erpnext.accounts.utils import get_balance_on accounts = frappe.db.sql_list("""select name from tabAccount where group_or_ledger='Ledger' and report_type='Balance Sheet' and company=%s""", company) return [{"account": a, "balance": get_balance_on(a)} for a in accounts]
def make_return_jv(stock_entry): se = frappe.get_doc("Stock Entry", stock_entry) if not se.purpose in ["Sales Return", "Purchase Return"]: return ref = get_return_doc_and_details(se) if ref.doc.doctype == "Delivery Note": result = make_return_jv_from_delivery_note(se, ref) elif ref.doc.doctype == "Sales Invoice": result = make_return_jv_from_sales_invoice(se, ref) elif ref.doc.doctype == "Purchase Receipt": result = make_return_jv_from_purchase_receipt(se, ref) # create jv doc and fetch balance for each unique row item jv = frappe.new_doc("Journal Entry") jv.update({ "posting_date": se.posting_date, "voucher_type": se.purpose == "Sales Return" and "Credit Note" or "Debit Note", "fiscal_year": se.fiscal_year, "company": se.company, "stock_entry": se.name }) from erpnext.accounts.utils import get_balance_on for r in result: jv.append("accounts", { "account": r.get("account"), "party_type": r.get("party_type"), "party": r.get("party"), "balance": get_balance_on(r.get("account"), se.posting_date) if r.get("account") else 0 }) return jv
def execute(filters=None): period_list = get_period_list(filters.get("fiscal_year"), filters.periodicity) account_list=get_cash_and_bank_account() if account_list==None: frappe.throw("Error, There is no Cash or bank Account to be tracked down") selected="" beginning_balance=0 sd=period_list[0]["year_start_date"].strftime("%Y-%m-%d") for ba in account_list: if selected=="": selected=ba[0] else: selected="""{}","{}""".format(selected,ba[0]) beginning_balance+=get_balance_on(ba[0],sd) selected = """ "{}" """.format(selected) asset = get_data(filters.company, "Asset", "Credit", period_list, selected, ignore_closing_entries=True) liability = get_data(filters.company, "Liability", "Credit", period_list, selected, ignore_closing_entries=True) income = get_data(filters.company, "Income", "Credit", period_list, selected, ignore_closing_entries=True) expense = get_data(filters.company, "Expense", "Debit", period_list, selected, ignore_closing_entries=True) net_profit_loss = get_net_profit_loss(income, expense, period_list) data = [] #data.extend(income or []) #data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) data.extend(asset or []) data.extend(liability or []) #data.extend(liability or []) data=get_total_change (beginning_balance,net_profit_loss,asset,liability,period_list,data) columns = get_columns(period_list) return columns, data
def validate_warehouse_account(self): '''If perpetual inventory is set, and warehouse is linked, the account balance and stock balance as of now must always match. ''' from erpnext.accounts.utils import get_balance_on from erpnext.stock.utils import get_stock_value_on if not cint(frappe.defaults.get_global_default("auto_accounting_for_stock")): return if self.account_type == "Stock": if self.is_group == 0 and not self.warehouse: frappe.throw(_("Warehouse is mandatory for non group Accounts of type Stock")) if self.warehouse: # company must be same if frappe.get_value('Warehouse', self.warehouse, 'company') != self.company: frappe.throw(_("Warehouse company must be same as Account company")) # balance must be same stock_balance = get_stock_value_on(self.warehouse) if self.is_new(): account_balance = 0.0 else: account_balance = get_balance_on(self.name) if account_balance != stock_balance: frappe.throw(_('Account balance ({0}) and stock value ({1}) must be same')\ .format(fmt_money(account_balance, currency=self.account_currency), fmt_money(stock_balance, currency=self.account_currency))) elif self.warehouse: self.warehouse = None
def update_accounts(customer): debit_to = get_payable_acc(customer.name) credit_to = get_payable_acc(customer.lead_name) points = get_balance_on(credit_to) details = type('new_dict', (object,), {"name": "Point Adjusment" , "company" : customer.company}) create_jv(details, points, debit_to, credit_to)
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_children(): args = frappe.local.form_dict ctype, company = args['ctype'], args['comp'] # root if args['parent'] in ("Accounts", "Cost Centers"): select_cond = ", root_type, report_type, account_currency" if ctype=="Account" else "" acc = frappe.db.sql(""" select name as value, is_group as expandable %s from `tab%s` where ifnull(parent_%s,'') = '' and `company` = %s and docstatus<2 order by name""" % (select_cond, ctype, ctype.lower().replace(' ','_'), '%s'), company, as_dict=1) if args["parent"]=="Accounts": sort_root_accounts(acc) else: # other select_cond = ", account_currency" if ctype=="Account" else "" acc = frappe.db.sql("""select name as value, is_group as expandable %s from `tab%s` where ifnull(parent_%s,'') = %s and docstatus<2 order by name""" % (select_cond, ctype, ctype.lower().replace(' ','_'), '%s'), args['parent'], as_dict=1) if ctype == 'Account': for each in acc: each["balance"] = flt(get_balance_on(each.get("value"))) return acc
def get_party_details(company, party_type, party, date): if not frappe.db.exists(party_type, party): frappe.throw(_("Invalid {0}: {1}").format(party_type, party)) party_account = get_party_account(party_type, party, company) account_currency = get_account_currency(party_account) account_balance = get_balance_on(party_account, date) party_balance = get_balance_on(party_type=party_type, party=party) return { "party_account": party_account, "party_account_currency": account_currency, "party_balance": party_balance, "account_balance": account_balance }
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 get_account_details(account, date): frappe.has_permission('Payment Entry', throw=True) return frappe._dict({ "account_currency": get_account_currency(account), "account_balance": get_balance_on(account, date), "account_type": frappe.db.get_value("Account", account, "account_type") })
def get_children(): args = frappe.local.form_dict ctype, company = args['ctype'], args['comp'] # root if args['parent'] in ("Accounts", "Cost Centers"): acc = frappe.db.sql(""" select name as value, if(group_or_ledger='Group', 1, 0) as expandable from `tab%s` where ifnull(parent_%s,'') = '' and `company` = %s and docstatus<2 order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'), company, as_dict=1, debug=1) else: # other acc = frappe.db.sql("""select name as value, if(group_or_ledger='Group', 1, 0) as expandable from `tab%s` where ifnull(parent_%s,'') = %s and docstatus<2 order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'), args['parent'], as_dict=1, debug=1) if ctype == 'Account': currency = frappe.db.sql("select default_currency from `tabCompany` where name = %s", company)[0][0] for each in acc: bal = get_balance_on(each.get("value")) each["currency"] = currency each["balance"] = flt(bal) return acc
def get_payment_entry_from_purchase_invoice(purchase_invoice): pi = frappe.get_doc("Purchase Invoice", purchase_invoice) jv = get_payment_entry(pi) jv.remark = 'Payment against Purchase Invoice {0}. {1}'.format( pi.name, pi.remarks) # credit supplier jv.get("accounts")[0].account = pi.credit_to jv.get("accounts")[0].party_type = "Supplier" jv.get("accounts")[0].party = pi.supplier jv.get("accounts")[0].balance = get_balance_on(pi.credit_to) jv.get("accounts")[0].party_balance = get_balance_on(party=pi.supplier, party_type="Supplier") jv.get("accounts")[0].debit = pi.outstanding_amount jv.get("accounts")[0].against_voucher = pi.name # credit bank jv.get("accounts")[1].credit = pi.outstanding_amount return jv.as_dict()
def get_opening_accounts(company): """get all balance sheet accounts for opening entry""" accounts = frappe.db.sql_list("""select name from tabAccount where is_group=0 and report_type='Balance Sheet' and company=%s and name not in(select distinct account from tabWarehouse where account is not null and account != '') order by name asc""", frappe.db.escape(company)) return [{"account": a, "balance": get_balance_on(a)} for a in accounts]
def get_party_details(company, party_type, party, date): if not frappe.db.exists(party_type, party): frappe.throw(_("Invalid {0}: {1}").format(party_type, party)) party_account = get_party_account(party_type, party, company) account_currency = get_account_currency(party_account) account_balance = get_balance_on(party_account, date) _party_name = "title" if party_type == "Student" else party_type.lower( ) + "_name" party_name = frappe.db.get_value(party_type, party, _party_name) party_balance = get_balance_on(party_type=party_type, party=party) return { "party_account": party_account, "party_name": party_name, "party_account_currency": account_currency, "party_balance": party_balance, "account_balance": account_balance }
def test_delivery_note_gl_entry_packing_item(self): company = frappe.db.get_value('Warehouse', '_Test Warehouse - _TC', 'company') set_perpetual_inventory(1, company) make_stock_entry(item_code="_Test Item", target="_Test Warehouse - _TC", qty=10, basic_rate=100) make_stock_entry(item_code="_Test Item Home Desktop 100", target="_Test Warehouse - _TC", qty=10, basic_rate=100) stock_in_hand_account = get_inventory_account('_Test Company') prev_bal = get_balance_on(stock_in_hand_account) dn = create_delivery_note(item_code="_Test Product Bundle Item") stock_value_diff_rm1 = abs(frappe.db.get_value("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name, "item_code": "_Test Item"}, "stock_value_difference")) stock_value_diff_rm2 = abs(frappe.db.get_value("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name, "item_code": "_Test Item Home Desktop 100"}, "stock_value_difference")) stock_value_diff = stock_value_diff_rm1 + stock_value_diff_rm2 gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) expected_values = { stock_in_hand_account: [0.0, stock_value_diff], "Cost of Goods Sold - _TC": [stock_value_diff, 0.0] } for i, gle in enumerate(gl_entries): self.assertEqual([gle.debit, gle.credit], expected_values.get(gle.account)) # check stock in hand balance bal = get_balance_on(stock_in_hand_account) self.assertEqual(flt(bal, 2), flt(prev_bal - stock_value_diff, 2)) dn.cancel() self.assertFalse(get_gl_entries("Delivery Note", dn.name)) set_perpetual_inventory(0, company)
def get_payment_entry_from_sales_invoice(sales_invoice): from erpnext.accounts.utils import get_balance_on si = frappe.get_doc("Sales Invoice", sales_invoice) jv = get_payment_entry(si) jv.remark = 'Payment received against Sales Invoice {0}. {1}'.format( si.name, si.remarks) # credit customer jv.get("accounts")[0].account = si.debit_to jv.get("accounts")[0].party_type = "Customer" jv.get("accounts")[0].party = si.customer jv.get("accounts")[0].balance = get_balance_on(si.debit_to) jv.get("accounts")[0].party_balance = get_balance_on(party=si.customer, party_type="Customer") jv.get("accounts")[0].credit = si.outstanding_amount jv.get("accounts")[0].against_invoice = si.name # debit bank jv.get("accounts")[1].debit = si.outstanding_amount return jv.as_dict()
def set_missing_values_custom(self): from frappe import scrub from erpnext.accounts.utils import get_account_currency, get_balance_on from erpnext.accounts.party import get_party_account from erpnext.accounts.doctype.payment_entry.payment_entry import get_account_details if self.payment_type == "Internal Transfer": for field in ("party", "party_balance", "total_allocated_amount", "base_total_allocated_amount", "unallocated_amount"): self.set(field, None) self.references = [] else: if not self.party_type: frappe.throw(_("Party Type is mandatory")) if not self.party: frappe.throw(_("Party is mandatory")) _party_name = "title" if self.party_type in ( "Student", "Shareholder") else scrub(self.party_type.lower()) + "_name" self.party_name = frappe.db.get_value(self.party_type, self.party, _party_name) if self.party: if not self.party_balance: self.party_balance = get_balance_on(party_type=self.party_type, party=self.party, date=self.posting_date, company=self.company) if not self.party_account: party_account = get_party_account(self.party_type, self.party, self.company) self.set(self.party_account_field, party_account) self.party_account = party_account if self.paid_from and not (self.paid_from_account_currency or self.paid_from_account_balance): acc = get_account_details(self.paid_from, self.posting_date, self.cost_center) self.paid_from_account_currency = acc.account_currency self.paid_from_account_balance = acc.account_balance if self.paid_to and not (self.paid_to_account_currency or self.paid_to_account_balance): acc = get_account_details(self.paid_to, self.posting_date, self.cost_center) self.paid_to_account_currency = acc.account_currency self.paid_to_account_balance = acc.account_balance self.party_account_currency = self.paid_from_account_currency \ if self.payment_type=="Receive" else self.paid_to_account_currency self.set_missing_ref_details()
def get_party_account_and_balance(company, party_type, party): if not frappe.has_permission("Account"): frappe.msgprint(_("No Permission"), raise_exception=1) account = get_party_account(party_type, party, company) account_balance = get_balance_on(account=account) party_balance = get_balance_on(party_type=party_type, party=party, company=company) return { "account": account, "balance": account_balance, "party_balance": party_balance, "account_currency": frappe.db.get_value("Account", account, "account_currency") }
def get_opening_accounts(company): """get all balance sheet accounts for opening entry""" accounts = frappe.db.sql_list("""select name from tabAccount where is_group=0 and report_type='Balance Sheet' and ifnull(warehouse, '') = '' and company=%s order by name asc""", company) return [{"account": a, "balance": get_balance_on(a)} for a in accounts]
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 test_payment_entry_account_and_party_balance_with_cost_center(self): from erpnext.accounts.doctype.cost_center.test_cost_center import create_cost_center from erpnext.accounts.utils import get_balance_on cost_center = "_Test Cost Center for BS Account - _TC" create_cost_center(cost_center_name="_Test Cost Center for BS Account", company="_Test Company") si = create_sales_invoice_against_cost_center(cost_center=cost_center, debit_to="Debtors - _TC") account_balance = get_balance_on(account="_Test Bank - _TC", cost_center=si.cost_center) party_balance = get_balance_on(party_type="Customer", party=si.customer, cost_center=si.cost_center) party_account_balance = get_balance_on(si.debit_to, cost_center=si.cost_center) pe = get_payment_entry("Sales Invoice", si.name, bank_account="_Test Bank - _TC") pe.reference_no = "112211-1" pe.reference_date = nowdate() pe.paid_to = "_Test Bank - _TC" pe.paid_amount = si.grand_total pe.insert() pe.submit() expected_account_balance = account_balance + si.grand_total expected_party_balance = party_balance - si.grand_total expected_party_account_balance = party_account_balance - si.grand_total account_balance = get_balance_on(account=pe.paid_to, cost_center=pe.cost_center) party_balance = get_balance_on(party_type="Customer", party=pe.party, cost_center=pe.cost_center) party_account_balance = get_balance_on(account=pe.paid_from, cost_center=pe.cost_center) self.assertEqual(pe.cost_center, si.cost_center) self.assertEqual(expected_account_balance, account_balance) self.assertEqual(expected_party_balance, party_balance) self.assertEqual(expected_party_account_balance, party_account_balance)
def test_delivery_note_gl_entry_packing_item(self): self.clear_stock_account_balance() set_perpetual_inventory() _insert_purchase_receipt() _insert_purchase_receipt("_Test Item Home Desktop 100") dn = frappe.copy_doc(test_records[0]) dn.get("delivery_note_details")[0].item_code = "_Test Sales BOM Item" dn.get("delivery_note_details")[0].qty = 1 stock_in_hand_account = frappe.db.get_value( "Account", {"master_name": dn.get("delivery_note_details")[0].warehouse}) from erpnext.accounts.utils import get_balance_on prev_bal = get_balance_on(stock_in_hand_account, dn.posting_date) dn.insert() dn.submit() gl_entries = get_gl_entries("Delivery Note", dn.name) self.assertTrue(gl_entries) expected_values = { stock_in_hand_account: [0.0, 525], "Cost of Goods Sold - _TC": [525.0, 0.0] } for i, gle in enumerate(gl_entries): self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account)) # check stock in hand balance bal = get_balance_on(stock_in_hand_account, dn.posting_date) self.assertEquals(bal, prev_bal - 525.0) dn.cancel() self.assertFalse(get_gl_entries("Delivery Note", dn.name)) set_perpetual_inventory(0)
def get_default_bank_cash_account(company, voucher_type, mode_of_payment=None): from erpnext.accounts.doctype.sales_invoice.sales_invoice import get_bank_cash_account if mode_of_payment: account = get_bank_cash_account(mode_of_payment, company) if account.get("account"): account.update({"balance": get_balance_on(account.get("account"))}) return account if voucher_type=="Bank Entry": account = frappe.db.get_value("Company", company, "default_bank_account") if not account: account = frappe.db.get_value("Account", {"company": company, "account_type": "Bank", "is_group": 0}) elif voucher_type=="Cash Entry": account = frappe.db.get_value("Company", company, "default_cash_account") if not account: account = frappe.db.get_value("Account", {"company": company, "account_type": "Cash", "is_group": 0}) if account: return { "account": account, "balance": get_balance_on(account) }
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) jv = get_payment_entry(pi) jv.remark = 'Payment against Purchase Invoice {0}. {1}'.format( pi.name, pi.remarks) # credit supplier jv.get("accounts")[0].account = pi.credit_to jv.get("accounts")[0].party_type = "Supplier" jv.get("accounts")[0].party = pi.supplier jv.get("accounts")[0].balance = get_balance_on(pi.credit_to) jv.get("accounts")[0].party_balance = get_balance_on(party=pi.supplier, party_type="Supplier") jv.get("accounts")[0].debit = pi.outstanding_amount jv.get("accounts")[0].reference_type = pi.doctype jv.get("accounts")[0].reference_name = pi.name # credit bank jv.get("accounts")[1].credit = pi.outstanding_amount return jv.as_dict()
def validate_balance_must_be_debit_or_credit(self): from erpnext.accounts.utils import get_balance_on if not self.get("__islocal") and self.balance_must_be: account_balance = get_balance_on(self.name) if account_balance > 0 and self.balance_must_be == "Credit": frappe.throw( _("Account balance already in Debit, you are not allowed to set 'Balance Must Be' as 'Credit'" )) elif account_balance < 0 and self.balance_must_be == "Debit": frappe.throw( _("Account balance already in Credit, you are not allowed to set 'Balance Must Be' as 'Debit'" ))
def get_account_balance_and_party_type(account, date): """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) account_type = frappe.db.get_value("Account", account, "account_type") return { "balance": get_balance_on(account, date), "party_type": { "Receivable": "Customer", "Payable": "Supplier" }.get(account_type, "") }
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 get_period_amounts(self, accounts, fieldname): """Get amounts for current and past periods""" balance = past_balance = 0.0 count = 0 for account in accounts: balance += ( get_balance_on(account, date=self.future_to_date) - get_balance_on(account, date=self.future_from_date - timedelta(days=1))) count += ( get_count_on(account, fieldname, date=self.future_to_date) - get_count_on(account, fieldname, date=self.future_from_date - timedelta(days=1))) past_balance += ( get_balance_on(account, date=self.past_to_date) - get_balance_on(account, date=self.past_from_date - timedelta(days=1))) return balance, past_balance, count
def execute(filters=None): if not filters: filters = {} columns = get_columns() if not filters.get("account"): return columns, [] account_currency = frappe.db.get_value("Account", filters.account, "account_currency") data = get_entries(filters) from erpnext.accounts.utils import get_balance_on balance_as_per_system = get_balance_on(filters["account"], filters["report_date"]) total_debit, total_credit = 0, 0 for d in data: total_debit += flt(d.debit) total_credit += flt(d.credit) amounts_not_reflected_in_system = frappe.db.sql( """ select sum(jvd.debit_in_account_currency - jvd.credit_in_account_currency) from `tabJournal Entry Account` jvd, `tabJournal Entry` jv where jvd.parent = jv.name and jv.docstatus=1 and jvd.account=%s and jv.posting_date > %s and jv.clearance_date <= %s and ifnull(jv.is_opening, 'No') = 'No' """, (filters["account"], filters["report_date"], filters["report_date"])) amounts_not_reflected_in_system = flt(amounts_not_reflected_in_system[0][0]) \ if amounts_not_reflected_in_system else 0.0 bank_bal = flt(balance_as_per_system) - flt(total_debit) + flt(total_credit) \ + amounts_not_reflected_in_system data += [ get_balance_row(_("Bank Statement balance as per General Ledger"), balance_as_per_system, account_currency), {}, { "journal_entry": _("Outstanding Cheques and Deposits to clear"), "debit": total_debit, "credit": total_credit, "account_currency": account_currency }, get_balance_row(_("Cheques and Deposits incorrectly cleared"), amounts_not_reflected_in_system, account_currency), {}, get_balance_row(_("Calculated Bank Statement balance"), bank_bal, account_currency) ] return columns, data
def get_children(): args = frappe.local.form_dict ctype, company = args['ctype'], args['comp'] # root if args['parent'] in ("Accounts", "Cost Centers"): select_cond = ", root_type, report_type, account_currency" if ctype=="Account" else "" acc = frappe.db.sql(""" select name as value, is_group as expandable %s from `tab%s` where ifnull(`parent_%s`,'') = '' and `company` = %s and docstatus<2 order by name""" % (select_cond, frappe.db.escape(ctype), frappe.db.escape(ctype.lower().replace(' ','_')), '%s'), company, as_dict=1) if args["parent"]=="Accounts": sort_root_accounts(acc) else: # other select_cond = ", account_currency" if ctype=="Account" else "" acc = frappe.db.sql("""select name as value, is_group as expandable %s from `tab%s` where ifnull(`parent_%s`,'') = %s and docstatus<2 order by name""" % (select_cond, frappe.db.escape(ctype), frappe.db.escape(ctype.lower().replace(' ','_')), '%s'), args['parent'], as_dict=1) if ctype == 'Account': company_currency = frappe.db.get_value("Company", company, "default_currency") for each in acc: each["company_currency"] = company_currency each["balance"] = flt(get_balance_on(each.get("value"), in_account_currency=False)) if each.account_currency != company_currency: each["balance_in_account_currency"] = flt(get_balance_on(each.get("value"))) return acc
def test_jv_account_and_party_balance_for_enable_allow_cost_center_in_entry_of_bs_account(self): from erpnext.accounts.doctype.cost_center.test_cost_center import create_cost_center from erpnext.accounts.utils import get_balance_on accounts_settings = frappe.get_doc('Accounts Settings', 'Accounts Settings') accounts_settings.allow_cost_center_in_entry_of_bs_account = 1 accounts_settings.save() cost_center = "_Test Cost Center for BS Account - _TC" create_cost_center(cost_center_name="_Test Cost Center for BS Account", company="_Test Company") jv = make_journal_entry("_Test Cash - _TC", "_Test Bank - _TC", 100, cost_center = cost_center, save=False) account_balance = get_balance_on(account="_Test Bank - _TC", cost_center=cost_center) jv.voucher_type = "Bank Entry" jv.multi_currency = 0 jv.cheque_no = "112233" jv.cheque_date = nowdate() jv.insert() jv.submit() expected_account_balance = account_balance - 100 account_balance = get_balance_on(account="_Test Bank - _TC", cost_center=cost_center) self.assertEqual(expected_account_balance, account_balance) accounts_settings.allow_cost_center_in_entry_of_bs_account = 0 accounts_settings.save()
def get_party_details(company, party_type, party, date, cost_center=None): bank_account = '' if not frappe.db.exists(party_type, party): frappe.throw(_("Invalid {0}: {1}").format(party_type, party)) party_account = get_party_account(party_type, party, company) account_currency = get_account_currency(party_account) account_balance = get_balance_on(party_account, date, cost_center=cost_center) _party_name = "title" if party_type in ("Student", "Shareholder") else party_type.lower() + "_name" party_name = frappe.db.get_value(party_type, party, _party_name) party_balance = get_balance_on(party_type=party_type, party=party, cost_center=cost_center) if party_type in ["Customer", "Supplier"]: bank_account = get_party_bank_account(party_type, party) return { "party_account": party_account, "party_name": party_name, "party_account_currency": account_currency, "party_balance": party_balance, "account_balance": account_balance, "bank_account": bank_account }
def get_default_bank_cash_account(company, account_type=None, mode_of_payment=None, account=None): from erpnext.accounts.doctype.sales_invoice.sales_invoice import get_bank_cash_account if mode_of_payment: account = get_bank_cash_account(mode_of_payment, company).get("account") if not account: if account_type == "Bank": account = frappe.db.get_value("Company", company, "default_bank_account") if not account: account_list = frappe.get_all("Account", filters={ "company": company, "account_type": "Bank", "is_group": 0 }) if len(account_list) > 0: account = account_list[0].name elif account_type == "Cash": account = frappe.db.get_value("Company", company, "default_cash_account") if not account: account_list = frappe.get_all("Account", filters={ "company": company, "account_type": "Cash", "is_group": 0 }) if len(account_list) > 0: account = account_list[0].name if account: account_details = frappe.db.get_value( "Account", account, ["account_currency", "account_type"], as_dict=1) return frappe._dict({ "account": account, "balance": get_balance_on(account), "account_currency": account_details.account_currency, "account_type": account_details.account_type }) else: return frappe._dict()
def get_year_to_date_balance(self, root_type, fieldname): """Get income to date""" balance = 0.0 count = 0 for account in self.get_root_type_accounts(root_type): balance += get_balance_on(account, date=self.future_to_date) count += get_count_on(account, fieldname, date=self.future_to_date) return { "label": self.meta.get_label(root_type + "_year_to_date"), "value": balance, "count": count }
def get_incomes_expenses_for_period(account, from_date, to_date): """Get amounts for current and past periods""" val = 0.0 balance_on_to_date = get_balance_on(account, date=to_date) balance_before_from_date = get_balance_on(account, date=from_date - timedelta(days=1)) fy_start_date = get_fiscal_year(to_date)[1] if from_date == fy_start_date: val = balance_on_to_date elif from_date > fy_start_date: val = balance_on_to_date - balance_before_from_date else: last_year_closing_balance = get_balance_on(account, date=fy_start_date - timedelta(days=1)) val = balance_on_to_date + (last_year_closing_balance - balance_before_from_date) return val
def get_data(filters): data = [[ 'Balance as at ' + str( frappe.utils.get_datetime( filters.from_date).strftime('%d %B, %Y')), "", "", "", "", "", "", "", "", "", "" ], [ 'Profit/Loss after Income tax', "", "", "", "", "", str(get_balance_on("Retain Earnings - SMCL")), "", "", "", "" ], [ 'Share allotment aganist money recieved from DHI', str(get_balance_on("Paid Up Capital - SMCL") / 100)[1:], "100", str(get_balance_on("Paid Up Capital - SMCL"))[1:], "", "", "", "", "", "", "" ], [ 'Dividend for the Financial Year', "", "", "", "", "", str(get_balance_on("Proposed Dividend (Current Year) - SMCL")), "", "", "", "" ], [ 'Transfer to Group Investment Reserve', "", "", "", "", "", "", str(get_balance_on("General Reserves - SMCL")), "", "", "" ], [ 'Other Comprehensive Income for the Year', "", "", "", "", "", "", "", "", "", "" ], [ 'Balance as at ' + str( frappe.utils.get_datetime( filters.to_date).strftime('%d %B, %Y')), "", "", "", "", "", "", "", "", "", "" ]] return data
def make_return_jv(stock_entry): se = frappe.get_doc("Stock Entry", stock_entry) if not se.purpose in ["Sales Return", "Purchase Return"]: return ref = get_return_doc_and_details(se) if ref.doc.doctype == "Delivery Note": result = make_return_jv_from_delivery_note(se, ref) elif ref.doc.doctype == "Sales Invoice": result = make_return_jv_from_sales_invoice(se, ref) elif ref.doc.doctype == "Purchase Receipt": result = make_return_jv_from_purchase_receipt(se, ref) # create jv doc and fetch balance for each unique row item jv = frappe.new_doc("Journal Entry") jv.update({ "posting_date": se.posting_date, "voucher_type": se.purpose == "Sales Return" and "Credit Note" or "Debit Note", "fiscal_year": se.fiscal_year, "company": se.company, "stock_entry": se.name }) from erpnext.accounts.utils import get_balance_on for r in result: jv.append( "accounts", { "account": r.get("account"), "party_type": r.get("party_type"), "party": r.get("party"), "against_invoice": r.get("against_invoice"), "against_voucher": r.get("against_voucher"), "balance": get_balance_on(r.get("account"), se.posting_date) if r.get("account") else 0 }) return jv
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 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, "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_patient_billing_info(patient, ip_billing_info=False): from erpnext.accounts.utils import get_balance_on filters = {'docstatus': 1, 'customer': patient.customer} company = False if ip_billing_info and patient.inpatient_record: filters['inpatient_record'] = patient.inpatient_record company = frappe.db.get_value('Inpatient Record', patient.inpatient_record, 'company') fields = [ 'patient', 'sum(grand_total) as grand_total', 'sum(base_grand_total) as base_grand_total' ] patient_grand_total = frappe.get_all('Sales Invoice', filters=filters, fields=fields) filters['status'] = ['not in', 'Paid'] fields = ['patient', 'sum(outstanding_amount) as outstanding_amount'] patient_total_unpaid = frappe.get_all('Sales Invoice', filters=filters, fields=fields) if not company: company = frappe.defaults.get_user_default('company') if not company: company = frappe.db.get_single_value('Global Defaults', 'default_company') company_default_currency = frappe.db.get_value('Company', company, 'default_currency') from erpnext.accounts.party import get_party_account_currency party_account_currency = get_party_account_currency( 'Customer', patient.customer, company) if party_account_currency == company_default_currency: billing_this_year = flt(patient_grand_total[0]['base_grand_total']) else: billing_this_year = flt(patient_grand_total[0]['grand_total']) total_unpaid = flt(patient_total_unpaid[0]['outstanding_amount']) info = {} info['total_billing'] = flt(billing_this_year) if billing_this_year else 0 info['currency'] = party_account_currency info['total_unpaid'] = flt(total_unpaid) if total_unpaid else 0 info['party_balance'] = get_balance_on(party_type='Customer', party=patient.customer) return info
def test_jv_account_and_party_balance_with_cost_centre(self): from erpnext.accounts.doctype.cost_center.test_cost_center import create_cost_center from erpnext.accounts.utils import get_balance_on cost_center = "_Test Cost Center for BS Account - _TC" create_cost_center(cost_center_name="_Test Cost Center for BS Account", company="_Test Company") jv = make_journal_entry("_Test Cash - _TC", "_Test Bank - _TC", 100, cost_center=cost_center, save=False) account_balance = get_balance_on(account="_Test Bank - _TC", cost_center=cost_center) jv.voucher_type = "Bank Entry" jv.multi_currency = 0 jv.cheque_no = "112233" jv.cheque_date = nowdate() jv.insert() jv.submit() expected_account_balance = account_balance - 100 account_balance = get_balance_on(account="_Test Bank - _TC", cost_center=cost_center) self.assertEqual(expected_account_balance, account_balance)