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"):
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_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_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 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_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_default_bank_cash_account(company,
account_type=None,
mode_of_payment=None,
account=None):
from condos.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:
'''
Set the default account first. If the user hasn't set any default account then, he doesn't
want us to set any random account. In this case set the account only if there is single
account (of that type), otherwise return empty dict.
'''
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) == 1:
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) == 1:
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_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 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 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_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 = condos.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 validate_balance_must_be_debit_or_credit(self):
from condos.financials.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_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 set_missing_values(self):
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 == "Student" else 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.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.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 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 condos.financials.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 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"):
# Modified to include the posting date for which the exchange rate is required.
# Assumed to be the posting date in the reference document
exchange_rate = get_exchange_rate(
ref_doc.get("posting_date") or ref_doc.get("transaction_date"),
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)
# Modified to include the posting date for which the exchange rate is required.
# Assumed to be the posting date of the reference date
bank_row.exchange_rate = get_exchange_rate(
ref_doc.get("posting_date") or ref_doc.get("transaction_date"),
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)
# Multi currency check again
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()