def get_type_balance(self, fieldname, account_type, root_type=None):
if root_type:
accounts = [d.name for d in \
dataent.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 \
dataent.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,
in_account_currency=False)
count += get_count_on(account, fieldname, date=self.future_to_date)
prev_balance += get_balance_on(account,
date=self.past_to_date,
in_account_currency=False)
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 test_jv_account_and_party_balance_for_enable_allow_cost_center_in_entry_of_bs_account(
self):
from epaas.accounts.doctype.cost_center.test_cost_center import create_cost_center
from epaas.accounts.utils import get_balance_on
accounts_settings = dataent.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 test_delivery_note_gl_entry(self):
company = dataent.db.get_value('Warehouse', '_Test Warehouse - _TC', 'company')
set_perpetual_inventory(1, company)
set_valuation_method("_Test Item", "FIFO")
make_stock_entry(target="_Test Warehouse - _TC", qty=5, 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()
gl_entries = get_gl_entries("Delivery Note", dn.name)
self.assertTrue(gl_entries)
stock_value_difference = abs(dataent.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.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(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(dataent.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.assertEqual([gle.debit, gle.credit], expected_values.get(gle.account))
dn.cancel()
self.assertFalse(get_gl_entries("Delivery Note", dn.name))
set_perpetual_inventory(0, company)
def test_delivery_note_gl_entry_packing_item(self):
company = dataent.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(dataent.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(dataent.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 validate_balance_must_be_debit_or_credit(self):
from epaas.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":
dataent.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":
dataent.throw(
_("Account balance already in Credit, you are not allowed to set 'Balance Must Be' as 'Debit'"
))
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))
print(fy_start_date - timedelta(days=1), last_year_closing_balance)
val = balance_on_to_date + (last_year_closing_balance -
balance_before_from_date)
return val
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
if not filters.get("account"): return columns, []
account_currency = dataent.db.get_value("Account", filters.account, "account_currency")
data = get_entries(filters)
from epaas.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 = get_amounts_not_reflected_in_system(filters)
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),
{},
{
"payment_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 test_payment_entry_account_and_party_balance_for_enable_allow_cost_center_in_entry_of_bs_account(self):
from epaas.accounts.doctype.cost_center.test_cost_center import create_cost_center
from epaas.accounts.utils import get_balance_on
accounts_settings = dataent.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")
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)
accounts_settings.allow_cost_center_in_entry_of_bs_account = 0
accounts_settings.save()