def before_save(self):
self.total_amount = \
flt(self.total_interests) + flt(self.principal_amount)
self.total_charges = reduce(lambda a, x: a + x.charge_amount,
self.charges, 0)
self.total_received = self.total_amount + self.total_charges
account_dict = get_bank_cash_account(
mode_of_payment=self.mode_of_payment or 'Cash',
company=self.company,
)
self.payment_account = account_dict.get('account')
self.periods = []
for period in allocate_interests(
self.loan,
self.posting_date,
amount_to_allocate=self.total_interests,
principal=self.principal_amount,
):
self.append('periods', period)
expected_outstanding = self.principal_amount + compose(
sum, partial(map, pick('outstanding_amount')),
partial(filter, lambda x: x.ref_interest is not None))(
self.periods)
if expected_outstanding > get_outstanding_principal(self.loan):
frappe.throw('Cannot receive more than the current outstanding')
def allocate_amount(self):
self.total_amount = (self.paid_amount if self.loan_type == "EMI" else
flt(self.total_interests) +
flt(self.principal_amount))
self.total_charges = reduce(lambda a, x: a + x.charge_amount,
self.get("charges", []), 0)
self.total_received = self.total_amount + self.total_charges
account_dict = get_bank_cash_account(
mode_of_payment=self.mode_of_payment or "Cash",
company=self.company)
self.payment_account = account_dict.get("account")
self.periods = []
if self.loan_type == "EMI":
to_allocate = self.paid_amount
interests = frappe.get_all(
"Microfinance Loan Interest",
filters={
"loan": self.loan,
"status": ("!=", "Clear")
},
fields=[
"period as period_label",
"start_date",
"end_date",
"billed_amount",
"principal_amount",
"fine_amount",
"billed_amount + principal_amount + fine_amount - paid_amount as outstanding_amount", # noqa
"name as ref_interest",
],
order_by="start_date",
)
for interest in interests:
allocated_amount = min(to_allocate,
interest.get("outstanding_amount"))
if allocated_amount == 0:
break
self.append(
"periods",
merge(interest, {"allocated_amount": allocated_amount}))
to_allocate -= allocated_amount
else:
for period in allocate_interests(
self.loan,
self.posting_date,
amount_to_allocate=self.total_interests,
principal=self.principal_amount,
):
self.append("periods", period)
expected_outstanding = self.principal_amount + compose(
sum,
partial(map, pick("outstanding_amount")),
partial(filter, lambda x: x.ref_interest is not None),
)(self.periods)
if expected_outstanding > get_outstanding_principal(self.loan):
frappe.throw(
"Cannot receive more than the current outstanding")
def _make_row(row):
loan, sanctioned = row[1], row[3]
undisbursed = get_undisbursed_principal(loan)
outstanding = get_outstanding_principal(loan)
recovered = get_recovered_principal(loan)
return row + (
sanctioned - undisbursed,
recovered,
outstanding,
)
def get_billed_amount(self):
outstanding_principal = get_outstanding_principal(
self.loan, self.end_date)
calculation_slab, rate_of_interest = frappe.get_value(
'Microfinance Loan',
self.loan,
['calculation_slab', 'rate_of_interest'],
)
return calc_interest(outstanding_principal, rate_of_interest,
calculation_slab)
def onload(self):
self.set_onload(
'address_text', _make_address_text(self.customer)
)
if self.docstatus == 1:
self.set_onload(
'chart_data', get_chart_data(self.name)
)
self.set_onload(
'outstanding_principal', get_outstanding_principal(self.name)
)
def validate_allowable_amount(self, is_update=False):
effective_date = frappe.get_value(
'Loan Plan', self.loan_plan, 'effective_from'
)
if effective_date and \
getdate(effective_date) > getdate(self.posting_date):
return None
date_of_retirement, net_salary_amount = frappe.get_value(
'Microfinance Loanee',
{'customer': self.customer},
['date_of_retirement', 'net_salary_amount']
)
if not date_of_retirement or not net_salary_amount:
return None
allowed = calculate_principal(
income=net_salary_amount,
loan_plan=self.loan_plan,
end_date=date_of_retirement,
execution_date=self.posting_date,
)
loan_principal = flt(self.loan_principal)
recovery_amount = flt(self.recovery_amount)
if loan_principal > allowed.get('principal'):
frappe.throw(
"Requested principal cannot exceed {}".format(
_fmt_money(allowed.get('principal'))
)
)
tentative_outstanding = reduce(
lambda a, x: a + get_outstanding_principal(x),
map(pick('name'), _existing_loans_by(self.customer)),
loan_principal
)
if is_update:
before = self.get_doc_before_save()
tentative_outstanding -= before.loan_principal
if tentative_outstanding > allowed.get('principal'):
frappe.throw(
"Customer already has existing loans. "
"Total principal would exceed allowed {}".format(
_fmt_money(allowed.get('principal')),
)
)
if recovery_amount < tentative_outstanding / allowed.get('duration'):
frappe.throw(
"Recovery Amount cannot be less than {}".format(
_fmt_money(loan_principal / allowed.get('duration'))
)
)
def get_billed_amount(self):
(
loan_type,
calculation_slab,
rate_of_interest,
loan_principal,
) = frappe.get_value(
"Microfinance Loan",
self.loan,
["loan_type", "calculation_slab", "rate_of_interest", "loan_principal"],
)
if loan_type == "EMI":
return calc_interest(loan_principal, rate_of_interest)
outstanding_principal = get_outstanding_principal(self.loan, self.end_date)
return calc_interest(outstanding_principal, rate_of_interest, calculation_slab)
def validate_allowable_amount(self, is_update=False):
loan_type = self.loan_type or frappe.db.get_value(
"Loan Plan", self.loan_plan, "loan_type")
if loan_type == "EMI":
return
effective_date = frappe.get_value("Loan Plan", self.loan_plan,
"effective_from")
if effective_date and getdate(effective_date) > getdate(
self.posting_date):
return None
date_of_retirement, net_salary_amount = frappe.get_value(
"Microfinance Loanee",
{"customer": self.customer},
["date_of_retirement", "net_salary_amount"],
)
if not date_of_retirement or not net_salary_amount:
return None
allowed = calculate_principal(
income=net_salary_amount,
loan_plan=self.loan_plan,
end_date=date_of_retirement,
execution_date=self.posting_date,
)
loan_principal = flt(self.loan_principal)
recovery_amount = flt(self.recovery_amount)
if loan_principal > allowed.get("principal"):
frappe.throw("Requested principal cannot exceed {}".format(
_fmt_money(allowed.get("principal"))))
tentative_outstanding = reduce(
lambda a, x: a + get_outstanding_principal(x),
map(pick("name"), _existing_loans_by(self.customer)),
loan_principal,
)
if is_update:
before = self.get_doc_before_save()
tentative_outstanding -= before.loan_principal
if tentative_outstanding > allowed.get("principal"):
frappe.throw("Customer already has existing loans. "
"Total principal would exceed allowed {}".format(
_fmt_money(allowed.get("principal"))))
if recovery_amount < tentative_outstanding / allowed.get("duration"):
frappe.throw("Recovery Amount cannot be less than {}".format(
_fmt_money(loan_principal / allowed.get("duration"))))
def get_current_interest(loan, posting_date):
calculation_slab, rate_of_interest, recovery_status = frappe.get_value(
"Microfinance Loan",
loan,
["calculation_slab", "rate_of_interest", "recovery_status"],
)
if recovery_status == "NPA":
return 0
prev_billed_amount = compose(
partial(frappe.get_value,
"Microfinance Loan Interest",
fieldname="billed_amount"),
partial(make_name, loan),
getdate,
partial(add_months, months=-1),
)(posting_date)
if prev_billed_amount:
return prev_billed_amount
outstanding = get_outstanding_principal(loan, posting_date)
return calc_interest(outstanding, rate_of_interest, calculation_slab)
def get_current_interest(loan, posting_date):
calculation_slab, rate_of_interest, recovery_status = frappe.get_value(
'Microfinance Loan',
loan,
['calculation_slab', 'rate_of_interest', 'recovery_status'],
)
if recovery_status == 'NPA':
return 0
prev_billed_amount = compose(
partial(frappe.get_value,
'Microfinance Loan Interest',
fieldname='billed_amount'),
partial(make_name, loan),
getdate,
partial(add_months, months=-1),
)(posting_date)
if prev_billed_amount:
return prev_billed_amount
outstanding = get_outstanding_principal(loan, posting_date)
return calc_interest(outstanding, rate_of_interest, calculation_slab)
def allocate_interests(loan, posting_date, amount_to_allocate=0, principal=0):
periods = []
to_allocate = amount_to_allocate
existing_unpaid_interests = get_unpaid(loan)
for period in map(_interest_to_period, existing_unpaid_interests):
p = _allocate(period, to_allocate)
periods.append(p)
to_allocate -= p.get('allocated_amount')
calculation_slab, loan_date, rate_of_interest = frappe.get_value(
'Microfinance Loan',
loan,
['calculation_slab', 'posting_date', 'rate_of_interest'],
)
outstanding_amount = get_outstanding_principal(loan, posting_date)
interest_amount = calc_interest(outstanding_amount, rate_of_interest,
calculation_slab)
adv_interest_amount = calc_interest(outstanding_amount - principal,
rate_of_interest, calculation_slab)
last = get_last(loan)
effective_date = frappe.get_value('Microfinance Loan Settings', None,
'effective_date')
init_date = add_days(last.get('end_date'), 1) if last \
else max(loan_date, getdate(effective_date))
gen_per = _generate_periods(init_date)
while to_allocate > 0:
per_ = gen_per.next()
# for advance payments consider outstanding_amount to be minus
# the current principal to be paid
amount = adv_interest_amount \
if _is_advance(per_, posting_date) else interest_amount
per_.update({
'billed_amount': amount,
'outstanding_amount': amount,
})
per = _allocate(per_, to_allocate)
periods.append(per)
to_allocate -= per.get('allocated_amount')
return filter(lambda x: x.get('allocated_amount') > 0, periods)
def allocate_interests(loan, posting_date, amount_to_allocate=0, principal=0):
periods = []
to_allocate = amount_to_allocate
existing_unpaid_interests = get_unpaid(loan)
for period in map(_interest_to_period, existing_unpaid_interests):
p = _allocate(period, to_allocate)
periods.append(p)
to_allocate -= p.get("allocated_amount")
calculation_slab, loan_date, rate_of_interest = frappe.get_value(
"Microfinance Loan",
loan,
["calculation_slab", "posting_date", "rate_of_interest"],
)
outstanding_amount = get_outstanding_principal(loan, posting_date)
interest_amount = calc_interest(outstanding_amount, rate_of_interest,
calculation_slab)
adv_interest_amount = calc_interest(outstanding_amount - principal,
rate_of_interest, calculation_slab)
last = get_last(loan)
effective_date = frappe.get_value("Microfinance Loan Settings", None,
"effective_date")
init_date = (add_days(last.get("end_date"), 1) if last else max(
loan_date, getdate(effective_date)))
gen_per = _generate_periods(init_date)
while to_allocate > 0:
per_ = next(gen_per)
# for advance payments consider outstanding_amount to be minus
# the current principal to be paid
amount = (adv_interest_amount
if _is_advance(per_, posting_date) else interest_amount)
per_.update({"billed_amount": amount, "outstanding_amount": amount})
per = _allocate(per_, to_allocate)
periods.append(per)
to_allocate -= per.get("allocated_amount")
return filter(lambda x: x.get("allocated_amount") > 0, periods)
def execute(filters={}):
columns = [
_("Posting Date") + ":Date:90",
_("Account") + ":Link/Account:150",
_("Credit") + ":Currency/currency:90",
_("Debit") + ":Currency/currency:90",
_("Amount") + ":Currency/currency:90",
_("Cummulative") + ":Currency/currency:90",
_("Remarks") + "::240",
]
company, loan_account = frappe.get_value("Microfinance Loan",
filters.get("loan"),
["company", "loan_account"])
accounts_to_exclude = [
loan_account,
"Temporary Opening - {}".format(
frappe.db.get_value("Company", company, "abbr")),
]
conds = [
"against_voucher_type = 'Microfinance Loan'",
"against_voucher = '{}'".format(filters.get("loan")),
"account NOT IN ({})".format(_stringify_accounts(accounts_to_exclude)),
]
opening_entries = frappe.db.sql(
"""
SELECT
sum(credit) AS credit,
sum(debit) AS debit,
sum(credit - debit) as amount
FROM `tabGL Entry`
WHERE {conds} AND posting_date <'{from_date}'
""".format(conds=join(" AND ")(conds),
from_date=filters.get("from_date")),
as_dict=True,
)[0]
results = frappe.db.sql("""
SELECT
posting_date,
account,
sum(credit) as credit,
sum(debit) as debit,
sum(credit - debit) as amount,
remarks
FROM `tabGL Entry`
WHERE {conds}
AND posting_date BETWEEN '{from_date}' AND '{to_date}'
GROUP BY posting_date, account, voucher_no, remarks
ORDER BY posting_date ASC, name ASC
""".format(
conds=join(" AND ")(conds),
from_date=filters.get("from_date"),
to_date=filters.get("to_date"),
))
opening_credit = opening_entries.get("credit") or 0
opening_debit = opening_entries.get("debit") or 0
opening_amount = opening_entries.get("amount") or 0
total_credit = _col_sum(2)(results)
total_debit = _col_sum(3)(results)
total_amount = _col_sum(4)(results)
opening = (
None,
_("Opening"),
opening_credit,
opening_debit,
opening_amount,
opening_amount,
None,
)
total = (None, _("Total"), total_credit, total_debit, total_amount, None,
None)
closing = (
None,
_("Closing"),
opening_credit + total_credit,
opening_debit + total_debit,
opening_amount + total_amount,
get_outstanding_principal(filters.get("loan"), filters.get("to_date")),
None,
)
data = reduce(_accum_reducer, results, [opening]) + [total, closing]
return columns, data
def _result_to_data(row):
return row[:-1] + (
get_outstanding_principal(row[1]),
get_current_interest(row[1], today()),
)
def on_save(self):
self.current_outstanding = get_outstanding_principal(
self.loan, self.posting_date)
self.next_outstanding = self.current_outstanding - self.amount
def onload(self):
if self.docstatus == 1:
self.set_onload("chart_data", get_chart_data(self.name))
self.set_onload("outstanding_principal",
get_outstanding_principal(self.name))