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