def test_delivery_note_gl_entry_packing_item(self):
company = frappe.db.get_value('Warehouse', 'Stores - TCP1', 'company')
make_stock_entry(item_code="_Test Item",
target="Stores - TCP1",
qty=10,
basic_rate=100)
make_stock_entry(item_code="_Test Item Home Desktop 100",
target="Stores - TCP1",
qty=10,
basic_rate=100)
stock_in_hand_account = get_inventory_account(
'_Test Company with perpetual inventory')
prev_bal = get_balance_on(stock_in_hand_account)
dn = create_delivery_note(
item_code="_Test Product Bundle Item",
company='_Test Company with perpetual inventory',
warehouse='Stores - TCP1',
cost_center='Main - TCP1',
expense_account="Cost of Goods Sold - TCP1")
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 - TCP1": [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()
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)
if fieldname == 'income':
filters = {
"currency": self.currency
}
label = get_link_to_report('Profit and Loss Statement', label=self.meta.get_label(root_type + "_year_to_date"), filters=filters)
elif fieldname == 'expenses_booked':
filters = {
"currency": self.currency
}
label = get_link_to_report('Profit and Loss Statement', label=self.meta.get_label(root_type + "_year_to_date"), filters=filters)
return {
"label": label,
"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 validate_balance_must_be_debit_or_credit(self):
from erpbee.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 test_jv_account_and_party_balance_with_cost_centre(self):
from erpbee.accounts.doctype.cost_center.test_cost_center import create_cost_center
from erpbee.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)
def get_data(filters):
data = []
conditions = get_conditions(filters)
accounts = frappe.db.get_all("Account", fields=["name", "account_currency"],
filters=conditions)
for d in accounts:
balance = get_balance_on(d.name, date=filters.report_date)
row = {"account": d.name, "balance": balance, "currency": d.account_currency}
data.append(row)
return data
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 erpbee.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_with_cost_center(self):
from erpbee.accounts.doctype.cost_center.test_cost_center import create_cost_center
from erpbee.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 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, 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"):
label = ""
if fieldname == "bank_balance":
filters = {
"root_type": "Asset",
"account_type": "Bank",
"report_date": self.future_to_date,
"company": self.company
}
label = get_link_to_report('Account Balance', label=self.meta.get_label(fieldname), filters=filters)
else:
filters = {
"root_type": "Liability",
"account_type": "Bank",
"report_date": self.future_to_date,
"company": self.company
}
label = get_link_to_report('Account Balance', label=self.meta.get_label(fieldname), filters=filters)
return {
'label': label,
'value': balance,
'last_value': prev_balance
}
else:
if account_type == 'Payable':
label = get_link_to_report('Accounts Payable', label=self.meta.get_label(fieldname),
filters={
"report_date": self.future_to_date,
"company": self.company
} )
elif account_type == 'Receivable':
label = get_link_to_report('Accounts Receivable', label=self.meta.get_label(fieldname),
filters={
"report_date": self.future_to_date,
"company": self.company
})
else:
label = self.meta.get_label(fieldname)
return {
'label': label,
'value': balance,
'last_value': prev_balance,
'count': count
}