def get_chart_data(loan_name):
recovered = get_recovered_principal(loan_name)
outstanding = get_outstanding_principal(loan_name)
undisbursed = get_undisbursed_principal(loan_name)
write_off_account = frappe.get_value('Microfinance Loan Settings', None,
'write_off_account')
conds = [
"account = '{}'".format(write_off_account),
"against_voucher = '{}'".format(loan_name),
]
wrote_off = frappe.db.sql("""
SELECT SUM(debit - credit) FROM `tabGL Entry` WHERE {conds}
""".format(conds=join(" AND ")(conds)))[0][0] or 0
data = {
'labels': ['RC', 'OS', 'UD', 'WO'],
'datasets': [
{
'name': "Total",
'values': [recovered, outstanding, undisbursed, wrote_off]
},
]
}
return data
def get_chart_data(loan_name):
recovered = get_recovered(loan_name)
outstanding = get_unrecovered(loan_name)
undisbursed = get_undisbursed_principal(loan_name)
write_off_account = frappe.get_value(
"Microfinance Loan Settings", None, "write_off_account"
)
conds = [
"account = '{}'".format(write_off_account),
"against_voucher = '{}'".format(loan_name),
]
wrote_off = (
frappe.db.sql(
"""
SELECT SUM(debit - credit) FROM `tabGL Entry` WHERE {conds}
""".format(
conds=join(" AND ")(conds)
)
)[0][0]
or 0
)
data = {
"labels": ["RC", "OS", "UD", "WO"],
"datasets": [
{
"name": "Total",
"values": [recovered, outstanding, undisbursed, wrote_off],
}
],
}
return data
def list(loan, from_date, to_date):
if getdate(to_date) < getdate(from_date):
return frappe.throw('To date cannot be less than From date')
conds = [
"loan = '{}'".format(loan),
"docstatus = 1",
"start_date BETWEEN '{}' AND '{}'".format(from_date, to_date),
]
existing = frappe.db.sql(
"""
SELECT
name, status,
period, posting_date, start_date,
billed_amount, paid_amount, fine_amount
FROM `tabMicrofinance Loan Interest` WHERE {conds}
""".format(conds=join(" AND ")(conds)),
as_dict=True,
)
existing_dict = dict((row.name, row) for row in existing)
get_item = compose(existing_dict.get, partial(make_name, loan))
make_item = compose(_make_list_item, get_item)
loan_end_date, loan_start_date = frappe.get_value(
'Microfinance Loan', loan, ['clear_date', 'posting_date'])
def make_empty(d):
return {
'name': make_name(loan, d),
'period': d.strftime('%b %Y'),
'start_date': max(loan_start_date, d),
'status': 'Unbilled'
}
effective_date = frappe.get_value('Microfinance Loan Settings', None,
'effective_date')
is_not_sys_mgr = 'System Manager' not in frappe.permissions.get_roles()
def change_status(row):
start_date = row.get('start_date')
status = 'Clear' \
if is_not_sys_mgr \
and getdate(effective_date) > getdate(start_date) \
else row.get('status')
return update({
'status': status,
})(row)
fd = compose(partial(max, loan_start_date), getdate)
td = compose(partial(min, loan_end_date), getdate) \
if loan_end_date else getdate
dates = _gen_dates(fd(from_date), td(to_date))
return compose(
partial(map, change_status),
partial(map, lambda x: make_item(x) if get_item(x) else make_empty(x)),
)(dates)
def list(loan, from_date, to_date):
if getdate(to_date) < getdate(from_date):
return frappe.throw("To date cannot be less than From date")
conds = [
"loan = '{}'".format(loan),
"docstatus < 2",
"start_date BETWEEN '{}' AND '{}'".format(from_date, to_date),
]
existing = frappe.db.sql(
"""
SELECT
name, status,
period, posting_date, start_date,
billed_amount, paid_amount, fine_amount
FROM `tabMicrofinance Loan Interest` WHERE {conds}
""".format(conds=join(" AND ")(conds)),
as_dict=True,
)
existing_dict = dict((row.name, row) for row in existing)
get_item = compose(existing_dict.get, partial(make_name, loan))
make_item = compose(_make_list_item, get_item)
loan_end_date, loan_start_date = frappe.get_value(
"Microfinance Loan", loan, ["clear_date", "posting_date"])
def make_empty(d):
return {
"name": make_name(loan, d),
"period": d.strftime("%b %Y"),
"start_date": max(loan_start_date, d),
"status": "Unbilled",
}
effective_date = frappe.get_value("Microfinance Loan Settings", None,
"effective_date")
is_not_sys_mgr = "System Manager" not in frappe.permissions.get_roles()
def change_status(row):
start_date = row.get("start_date")
status = ("Clear" if is_not_sys_mgr
and getdate(effective_date) > getdate(start_date) else
row.get("status"))
return update({"status": status})(row)
fd = compose(partial(max, loan_start_date), getdate)
td = compose(partial(min, loan_end_date),
getdate) if loan_end_date else getdate
dates = _gen_dates(fd(from_date), td(to_date))
return compose(
partial(map, change_status),
partial(map, lambda x: make_item(x) if get_item(x) else make_empty(x)),
)(dates)
def _make_address_text(customer=None):
"""Returns formatted address of Customer"""
if not customer:
return None
address = frappe.get_value(
'Address',
get_default_address('Customer', customer),
['address_line1', 'city'],
as_dict=True
)
if not address:
return None
return join(', ')([address.get('address_line1'), address.get('city')])
def _make_address_text(customer=None):
"""Returns formatted address of Customer"""
if not customer:
return None
address = frappe.get_value(
"Address",
get_default_address("Customer", customer),
["address_line1", "city"],
as_dict=True,
)
if not address:
return None
return join(", ")([address.get("address_line1"), address.get("city")])
def execute(filters=None):
columns = [
_("Customer") + ":Link/Customer:180",
_("Loan No") + ":Link/Microfinance Loan:90",
_("Status") + "::90",
_("Last Payment Date") + ":Date:90",
_("Last Paid Interest") + "::90",
_("Outstanding") + ":Currency/currency:90",
_("Curent Interest") + ":Currency/currency:90",
]
conds = compose(
_set_filter(filters, "name"),
_set_filter(filters, "customer"),
_set_filter(filters, "loan_plan"),
)(["loan.disbursement_status != 'Sanctioned'"])
result = frappe.db.sql("""
SELECT
loan.customer,
loan.name,
loan.recovery_status,
max(recovery.posting_date),
interest.period,
loan.posting_date
FROM `tabMicrofinance Loan` AS loan
LEFT JOIN `tabMicrofinance Recovery` AS recovery
ON recovery.docstatus = 1
AND recovery.loan = loan.name
LEFT JOIN `tabMicrofinance Loan Interest` AS interest
ON interest.docstatus = 1
AND interest.loan = loan.name
AND interest.billed_amount = interest.paid_amount
WHERE loan.docstatus = 1
AND {conds}
GROUP BY loan.name
""".format(conds=join(" AND ")(conds)))
data = compose(
list,
partial(map, _result_to_data),
partial(filter, _display_filter(filters.get("display"))),
)(result)
return columns, data
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
from gwi_customization.microfinance.utils.fp import compose, join
from gwi_customization.microfinance.api.loan import get_outstanding_principal
def _accum_reducer(acc, row):
return acc + [row[:-1] + (acc[-1][5] + row[4], ) + row[-1:]]
def _col_sum(idx):
def fn(rows):
return reduce(lambda a, x: a + x[idx], rows, 0)
return fn
_stringify_accounts = compose(join(", "),
partial(map, lambda x: "'{}'".format(x)))
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",
paid_amount = (doc.paid_amount -
opts.get("paid_amount") if update else doc.paid_amount +
opts.get("paid_amount"))
doc.update({"paid_amount": paid_amount})
doc.save()
if submit and doc.docstatus == 0:
doc.submit()
else:
doc = frappe.new_doc("Microfinance Loan Interest")
doc.update(opts)
doc.insert()
doc.submit()
return name
_stringify_periods = compose(join(", "), partial(map,
lambda x: x.period_label))
class MicrofinanceRecovery(AccountsController):
def validate(self):
if self.total_interests and "NPA" == frappe.db.get_value(
"Microfinance Loan", self.loan, "recovery_status"):
frappe.throw("Cannot recover interests for NPA loans")
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)
from gwi_customization.microfinance.utils.fp import compose, join
from gwi_customization.microfinance.api.loan import get_outstanding_principal
def _accum_reducer(acc, row):
return acc + [row[:-1] + (acc[-1][5] + row[4], ) + row[-1:]]
def _col_sum(idx):
def fn(rows):
return reduce(lambda a, x: a + x[idx], rows, 0)
return fn
_stringify_accounts = compose(join(', '),
partial(map, lambda x: "'{}'".format(x)))
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',