def test_loan_repayment_salary_slip(self):
from erpnext.loan_management.doctype.loan.test_loan import create_loan_type, create_loan, make_loan_disbursement_entry, create_loan_accounts
from erpnext.loan_management.doctype.process_loan_interest_accrual.process_loan_interest_accrual import process_loan_interest_accrual_for_term_loans
applicant = make_employee("*****@*****.**", company="_Test Company")
create_loan_accounts()
create_loan_type("Car Loan", 500000, 8.4,
is_term_loan=1,
mode_of_payment='Cash',
payment_account='Payment Account - _TC',
loan_account='Loan Account - _TC',
interest_income_account='Interest Income Account - _TC',
penalty_income_account='Penalty Income Account - _TC')
loan = create_loan(applicant, "Car Loan", 11000, "Repay Over Number of Periods", 20, posting_date=add_months(nowdate(), -1))
loan.repay_from_salary = 1
loan.submit()
make_loan_disbursement_entry(loan.name, loan.loan_amount, disbursement_date=add_months(nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
ss = make_employee_salary_slip("*****@*****.**", "Monthly")
ss.submit()
self.assertEqual(ss.total_loan_repayment, 592)
self.assertEqual(ss.net_pay, (flt(ss.gross_pay) - (flt(ss.total_deduction) + flt(ss.total_loan_repayment))))
def test_loan_repayment_for_term_loan(self):
pledges = [{
"loan_security": "Test Security 2",
"qty": 4000.00
},
{
"loan_security": "Test Security 1",
"qty": 2000.00
}]
loan_application = create_loan_application('_Test Company', self.applicant2, 'Stock Loan', pledges,
"Repay Over Number of Periods", 12)
create_pledge(loan_application)
loan = create_loan_with_security(self.applicant2, "Stock Loan", "Repay Over Number of Periods", 12, loan_application,
posting_date=add_months(nowdate(), -1))
loan.submit()
make_loan_disbursement_entry(loan.name, loan.loan_amount, disbursement_date=add_months(nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
repayment_entry = create_repayment_entry(loan.name, self.applicant2, add_days(nowdate(), 5), 89768.75)
repayment_entry.submit()
amounts = frappe.db.get_value('Loan Interest Accrual', {'loan': loan.name}, ['paid_interest_amount',
'paid_principal_amount'])
self.assertEquals(amounts[0], 11250.00)
self.assertEquals(amounts[1], 78303.00)
def test_loan_repayment_salary_slip(self):
from erpnext.loan_management.doctype.loan.test_loan import create_loan_type, create_loan, make_loan_disbursement_entry, create_loan_accounts
from erpnext.loan_management.doctype.process_loan_interest_accrual.process_loan_interest_accrual import process_loan_interest_accrual_for_term_loans
from erpnext.payroll.doctype.salary_structure.test_salary_structure import make_salary_structure
applicant = make_employee("*****@*****.**",
company="_Test Company")
create_loan_accounts()
create_loan_type(
"Car Loan",
500000,
8.4,
is_term_loan=1,
mode_of_payment='Cash',
payment_account='Payment Account - _TC',
loan_account='Loan Account - _TC',
interest_income_account='Interest Income Account - _TC',
penalty_income_account='Penalty Income Account - _TC')
payroll_period = create_payroll_period(name="_Test Payroll Period 1",
company="_Test Company")
make_salary_structure("Test Loan Repayment Salary Structure",
"Monthly",
employee=applicant,
currency='INR',
payroll_period=payroll_period)
frappe.db.sql("""delete from `tabLoan""")
loan = create_loan(applicant,
"Car Loan",
11000,
"Repay Over Number of Periods",
20,
posting_date=add_months(nowdate(), -1))
loan.repay_from_salary = 1
loan.submit()
make_loan_disbursement_entry(loan.name,
loan.loan_amount,
disbursement_date=add_months(
nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
ss = make_employee_salary_slip(
"*****@*****.**", "Monthly",
"Test Loan Repayment Salary Structure")
ss.submit()
self.assertEqual(ss.total_loan_repayment, 592)
self.assertEqual(
ss.net_pay,
(flt(ss.gross_pay) -
(flt(ss.total_deduction) + flt(ss.total_loan_repayment))))
def test_loan(self):
branch = "Test Employee Branch"
applicant = make_employee(
"*****@*****.**", company="_Test Company")
company = "_Test Company"
holiday_list = make_holiday("test holiday for loan")
company_doc = frappe.get_doc('Company', company)
if not company_doc.default_payroll_payable_account:
company_doc.default_payroll_payable_account = frappe.db.get_value('Account',
{'company': company, 'root_type': 'Liability', 'account_type': ''}, 'name')
company_doc.save()
if not frappe.db.exists('Branch', branch):
frappe.get_doc({
'doctype': 'Branch',
'branch': branch
}).insert()
employee_doc = frappe.get_doc('Employee', applicant)
employee_doc.branch = branch
employee_doc.holiday_list = holiday_list
employee_doc.save()
salary_structure = "Test Salary Structure for Loan"
make_salary_structure(salary_structure, "Monthly", employee=employee_doc.name,
company="_Test Company", currency=company_doc.default_currency)
loan = create_loan(applicant, "Car Loan", 280000, "Repay Over Number of Periods",
20, posting_date=add_months(nowdate(), -1))
loan.repay_from_salary = 1
loan.submit()
make_loan_disbursement_entry(
loan.name, loan.loan_amount, disbursement_date=add_months(nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
dates = get_start_end_dates('Monthly', nowdate())
make_payroll_entry(company="_Test Company", start_date=dates.start_date, payable_account=company_doc.default_payroll_payable_account,
currency=company_doc.default_currency, end_date=dates.end_date, branch=branch, cost_center="Main - _TC", payment_account="Cash - _TC")
name = frappe.db.get_value('Salary Slip',
{'posting_date': nowdate(), 'employee': applicant}, 'name')
salary_slip = frappe.get_doc('Salary Slip', name)
for row in salary_slip.loans:
if row.loan == loan.name:
interest_amount = (280000 * 8.4)/(12*100)
principal_amount = loan.monthly_repayment_amount - interest_amount
self.assertEqual(row.interest_amount, interest_amount)
self.assertEqual(row.principal_amount, principal_amount)
self.assertEqual(row.total_payment,
interest_amount + principal_amount)
if salary_slip.docstatus == 0:
frappe.delete_doc('Salary Slip', name)
def accrue_loan_interest(self):
from erpnext.loan_management.doctype.process_loan_interest_accrual.process_loan_interest_accrual import (
process_loan_interest_accrual_for_term_loans, )
if getdate(
self.repayment_start_date) < getdate() and self.is_term_loan:
process_loan_interest_accrual_for_term_loans(
posting_date=getdate(),
loan_type=self.loan_type,
loan=self.name)
def test_repayment_schedule_update(self):
loan = create_loan(
self.applicant2,
"Personal Loan",
200000,
"Repay Over Number of Periods",
4,
applicant_type="Customer",
repayment_start_date="2021-04-30",
posting_date="2021-04-01",
)
loan.submit()
make_loan_disbursement_entry(loan.name,
loan.loan_amount,
disbursement_date="2021-04-01")
process_loan_interest_accrual_for_term_loans(posting_date="2021-05-01")
process_loan_interest_accrual_for_term_loans(posting_date="2021-06-01")
repayment_entry = create_repayment_entry(loan.name, self.applicant2,
"2021-06-05", 120000)
repayment_entry.submit()
loan.load_from_db()
self.assertEqual(
flt(loan.get("repayment_schedule")[3].principal_amount, 2),
41369.83)
self.assertEqual(
flt(loan.get("repayment_schedule")[3].interest_amount, 2), 289.59)
self.assertEqual(
flt(loan.get("repayment_schedule")[3].total_payment, 2), 41659.41)
self.assertEqual(
flt(loan.get("repayment_schedule")[3].balance_loan_amount, 2), 0)
def execute():
# Create a penalty account for loan types
frappe.reload_doc('loan_management', 'doctype', 'loan_type')
frappe.reload_doc('loan_management', 'doctype', 'loan')
frappe.reload_doc('loan_management', 'doctype', 'repayment_schedule')
frappe.reload_doc('loan_management', 'doctype', 'process_loan_interest_accrual')
frappe.reload_doc('loan_management', 'doctype', 'loan_repayment')
frappe.reload_doc('loan_management', 'doctype', 'loan_repayment_detail')
frappe.reload_doc('loan_management', 'doctype', 'loan_interest_accrual')
frappe.reload_doc('accounts', 'doctype', 'gl_entry')
frappe.reload_doc('accounts', 'doctype', 'journal_entry_account')
updated_loan_types = []
loans_to_close = []
# Update old loan status as closed
if frappe.db.has_column('Repayment Schedule', 'paid'):
loans_list = frappe.db.sql("""SELECT distinct parent from `tabRepayment Schedule`
where paid = 0 and docstatus = 1""", as_dict=1)
loans_to_close = [d.parent for d in loans_list]
if loans_to_close:
frappe.db.sql("UPDATE `tabLoan` set status = 'Closed' where name not in (%s)" % (', '.join(['%s'] * len(loans_to_close))), tuple(loans_to_close))
loans = frappe.get_all('Loan', fields=['name', 'loan_type', 'company', 'status', 'mode_of_payment',
'applicant_type', 'applicant', 'loan_account', 'payment_account', 'interest_income_account'],
filters={'docstatus': 1, 'status': ('!=', 'Closed')})
for loan in loans:
# Update details in Loan Types and Loan
loan_type_company = frappe.db.get_value('Loan Type', loan.loan_type, 'company')
loan_type = loan.loan_type
group_income_account = frappe.get_value('Account', {'company': loan.company,
'is_group': 1, 'root_type': 'Income', 'account_name': _('Indirect Income')})
if not group_income_account:
group_income_account = frappe.get_value('Account', {'company': loan.company,
'is_group': 1, 'root_type': 'Income'})
penalty_account = create_account(company=loan.company, account_type='Income Account',
account_name='Penalty Account', parent_account=group_income_account)
# Same loan type used for multiple companies
if loan_type_company and loan_type_company != loan.company:
# get loan type for appropriate company
loan_type_name = frappe.get_value('Loan Type', {'company': loan.company,
'mode_of_payment': loan.mode_of_payment, 'loan_account': loan.loan_account,
'payment_account': loan.payment_account, 'interest_income_account': loan.interest_income_account,
'penalty_income_account': loan.penalty_income_account}, 'name')
if not loan_type_name:
loan_type_name = create_loan_type(loan, loan_type_name, penalty_account)
# update loan type in loan
frappe.db.sql("UPDATE `tabLoan` set loan_type = %s where name = %s", (loan_type_name,
loan.name))
loan_type = loan_type_name
if loan_type_name not in updated_loan_types:
updated_loan_types.append(loan_type_name)
elif not loan_type_company:
loan_type_doc = frappe.get_doc('Loan Type', loan.loan_type)
loan_type_doc.is_term_loan = 1
loan_type_doc.company = loan.company
loan_type_doc.mode_of_payment = loan.mode_of_payment
loan_type_doc.payment_account = loan.payment_account
loan_type_doc.loan_account = loan.loan_account
loan_type_doc.interest_income_account = loan.interest_income_account
loan_type_doc.penalty_income_account = penalty_account
loan_type_doc.submit()
updated_loan_types.append(loan.loan_type)
loan_type = loan.loan_type
if loan_type in updated_loan_types:
if loan.status == 'Fully Disbursed':
status = 'Disbursed'
elif loan.status == 'Repaid/Closed':
status = 'Closed'
else:
status = loan.status
frappe.db.set_value('Loan', loan.name, {
'is_term_loan': 1,
'penalty_income_account': penalty_account,
'status': status
})
process_loan_interest_accrual_for_term_loans(posting_date=nowdate(), loan_type=loan_type,
loan=loan.name)
if frappe.db.has_column('Repayment Schedule', 'paid'):
total_principal, total_interest = frappe.db.get_value('Repayment Schedule', {'paid': 1, 'parent': loan.name},
['sum(principal_amount) as total_principal', 'sum(interest_amount) as total_interest'])
accrued_entries = get_accrued_interest_entries(loan.name)
for entry in accrued_entries:
interest_paid = 0
principal_paid = 0
if flt(total_interest) > flt(entry.interest_amount):
interest_paid = flt(entry.interest_amount)
else:
interest_paid = flt(total_interest)
if flt(total_principal) > flt(entry.payable_principal_amount):
principal_paid = flt(entry.payable_principal_amount)
else:
principal_paid = flt(total_principal)
frappe.db.sql(""" UPDATE `tabLoan Interest Accrual`
SET paid_principal_amount = `paid_principal_amount` + %s,
paid_interest_amount = `paid_interest_amount` + %s
WHERE name = %s""",
(principal_paid, interest_paid, entry.name))
total_principal = flt(total_principal) - principal_paid
total_interest = flt(total_interest) - interest_paid
def test_loan(self):
branch = "Test Employee Branch"
applicant = make_employee("*****@*****.**",
company="_Test Company")
company = "_Test Company"
holiday_list = make_holiday("test holiday for loan")
company_doc = frappe.get_doc("Company", company)
if not company_doc.default_payroll_payable_account:
company_doc.default_payroll_payable_account = frappe.db.get_value(
"Account", {
"company": company,
"root_type": "Liability",
"account_type": ""
}, "name")
company_doc.save()
if not frappe.db.exists("Branch", branch):
frappe.get_doc({"doctype": "Branch", "branch": branch}).insert()
employee_doc = frappe.get_doc("Employee", applicant)
employee_doc.branch = branch
employee_doc.holiday_list = holiday_list
employee_doc.save()
salary_structure = "Test Salary Structure for Loan"
make_salary_structure(
salary_structure,
"Monthly",
employee=employee_doc.name,
company="_Test Company",
currency=company_doc.default_currency,
)
if not frappe.db.exists("Loan Type", "Car Loan"):
create_loan_accounts()
create_loan_type(
"Car Loan",
500000,
8.4,
is_term_loan=1,
mode_of_payment="Cash",
disbursement_account="Disbursement Account - _TC",
payment_account="Payment Account - _TC",
loan_account="Loan Account - _TC",
interest_income_account="Interest Income Account - _TC",
penalty_income_account="Penalty Income Account - _TC",
)
loan = create_loan(
applicant,
"Car Loan",
280000,
"Repay Over Number of Periods",
20,
posting_date=add_months(nowdate(), -1),
)
loan.repay_from_salary = 1
loan.submit()
make_loan_disbursement_entry(loan.name,
loan.loan_amount,
disbursement_date=add_months(
nowdate(), -1))
process_loan_interest_accrual_for_term_loans(posting_date=nowdate())
dates = get_start_end_dates("Monthly", nowdate())
make_payroll_entry(
company="_Test Company",
start_date=dates.start_date,
payable_account=company_doc.default_payroll_payable_account,
currency=company_doc.default_currency,
end_date=dates.end_date,
branch=branch,
cost_center="Main - _TC",
payment_account="Cash - _TC",
)
name = frappe.db.get_value("Salary Slip", {
"posting_date": nowdate(),
"employee": applicant
}, "name")
salary_slip = frappe.get_doc("Salary Slip", name)
for row in salary_slip.loans:
if row.loan == loan.name:
interest_amount = (280000 * 8.4) / (12 * 100)
principal_amount = loan.monthly_repayment_amount - interest_amount
self.assertEqual(row.interest_amount, interest_amount)
self.assertEqual(row.principal_amount, principal_amount)
self.assertEqual(row.total_payment,
interest_amount + principal_amount)
if salary_slip.docstatus == 0:
frappe.delete_doc("Salary Slip", name)
def execute():
# Create a penalty account for loan types
frappe.reload_doc('loan_management', 'doctype', 'loan_type')
frappe.reload_doc('loan_management', 'doctype', 'loan')
frappe.reload_doc('loan_management', 'doctype', 'repayment_schedule')
frappe.reload_doc('loan_management', 'doctype',
'process_loan_interest_accrual')
frappe.reload_doc('loan_management', 'doctype', 'loan_repayment')
frappe.reload_doc('loan_management', 'doctype', 'loan_repayment_detail')
frappe.reload_doc('loan_management', 'doctype', 'loan_interest_accrual')
frappe.reload_doc('accounts', 'doctype', 'gl_entry')
updated_loan_types = []
loans = frappe.get_all('Loan',
fields=[
'name', 'loan_type', 'company', 'status',
'mode_of_payment', 'applicant_type',
'applicant', 'loan_account', 'payment_account',
'interest_income_account'
])
for loan in loans:
# Update details in Loan Types and Loan
loan_type_company = frappe.db.get_value('Loan Type', loan.loan_type,
'company')
group_income_account = frappe.get_value(
'Account', {
'company': loan.company,
'is_group': 1,
'root_type': 'Income',
'account_name': _('Indirect Income')
})
if not group_income_account:
group_income_account = frappe.get_value('Account', {
'company': loan.company,
'is_group': 1,
'root_type': 'Income'
})
penalty_account = create_account(company=loan.company,
account_type='Income Account',
account_name='Penalty Account',
parent_account=group_income_account)
if not loan_type_company:
loan_type_doc = frappe.get_doc('Loan Type', loan.loan_type)
loan_type_doc.is_term_loan = 1
loan_type_doc.company = loan.company
loan_type_doc.mode_of_payment = loan.mode_of_payment
loan_type_doc.payment_account = loan.payment_account
loan_type_doc.loan_account = loan.loan_account
loan_type_doc.interest_income_account = loan.interest_income_account
loan_type_doc.penalty_income_account = penalty_account
loan_type_doc.submit()
updated_loan_types.append(loan.loan_type)
if loan.loan_type in updated_loan_types:
if loan.status == 'Fully Disbursed':
status = 'Disbursed'
elif loan.status == 'Repaid/Closed':
status = 'Closed'
else:
status = loan.status
frappe.db.set_value(
'Loan', loan.name, {
'is_term_loan': 1,
'penalty_income_account': penalty_account,
'status': status
})
process_loan_interest_accrual_for_term_loans(
posting_date=nowdate(),
loan_type=loan.loan_type,
loan=loan.name)
payments = frappe.db.sql(
''' SELECT j.name, a.debit, a.debit_in_account_currency, j.posting_date
FROM `tabJournal Entry` j, `tabJournal Entry Account` a
WHERE a.parent = j.name and a.reference_type='Loan' and a.reference_name = %s
and account = %s
''', (loan.name, loan.loan_account),
as_dict=1)
for payment in payments:
repayment_entry = make_repayment_entry(
loan.name, loan.loan_applicant_type, loan.applicant,
loan.loan_type, loan.company)
repayment_entry.amount_paid = payment.debit_in_account_currency
repayment_entry.posting_date = payment.posting_date
repayment_entry.save()
repayment_entry.submit()
jv = frappe.get_doc('Journal Entry', payment.name)
jv.flags.ignore_links = True
jv.cancel()
def execute():
# Create a penalty account for loan types
frappe.reload_doc("loan_management", "doctype", "loan_type")
frappe.reload_doc("loan_management", "doctype", "loan")
frappe.reload_doc("loan_management", "doctype", "repayment_schedule")
frappe.reload_doc("loan_management", "doctype", "process_loan_interest_accrual")
frappe.reload_doc("loan_management", "doctype", "loan_repayment")
frappe.reload_doc("loan_management", "doctype", "loan_repayment_detail")
frappe.reload_doc("loan_management", "doctype", "loan_interest_accrual")
frappe.reload_doc("accounts", "doctype", "gl_entry")
frappe.reload_doc("accounts", "doctype", "journal_entry_account")
updated_loan_types = []
loans_to_close = []
# Update old loan status as closed
if frappe.db.has_column("Repayment Schedule", "paid"):
loans_list = frappe.db.sql(
"""SELECT distinct parent from `tabRepayment Schedule`
where paid = 0 and docstatus = 1""",
as_dict=1,
)
loans_to_close = [d.parent for d in loans_list]
if loans_to_close:
frappe.db.sql(
"UPDATE `tabLoan` set status = 'Closed' where name not in (%s)"
% (", ".join(["%s"] * len(loans_to_close))),
tuple(loans_to_close),
)
loans = frappe.get_all(
"Loan",
fields=[
"name",
"loan_type",
"company",
"status",
"mode_of_payment",
"applicant_type",
"applicant",
"loan_account",
"payment_account",
"interest_income_account",
],
filters={"docstatus": 1, "status": ("!=", "Closed")},
)
for loan in loans:
# Update details in Loan Types and Loan
loan_type_company = frappe.db.get_value("Loan Type", loan.loan_type, "company")
loan_type = loan.loan_type
group_income_account = frappe.get_value(
"Account",
{
"company": loan.company,
"is_group": 1,
"root_type": "Income",
"account_name": _("Indirect Income"),
},
)
if not group_income_account:
group_income_account = frappe.get_value(
"Account", {"company": loan.company, "is_group": 1, "root_type": "Income"}
)
penalty_account = create_account(
company=loan.company,
account_type="Income Account",
account_name="Penalty Account",
parent_account=group_income_account,
)
# Same loan type used for multiple companies
if loan_type_company and loan_type_company != loan.company:
# get loan type for appropriate company
loan_type_name = frappe.get_value(
"Loan Type",
{
"company": loan.company,
"mode_of_payment": loan.mode_of_payment,
"loan_account": loan.loan_account,
"payment_account": loan.payment_account,
"interest_income_account": loan.interest_income_account,
"penalty_income_account": loan.penalty_income_account,
},
"name",
)
if not loan_type_name:
loan_type_name = create_loan_type(loan, loan_type_name, penalty_account)
# update loan type in loan
frappe.db.sql(
"UPDATE `tabLoan` set loan_type = %s where name = %s", (loan_type_name, loan.name)
)
loan_type = loan_type_name
if loan_type_name not in updated_loan_types:
updated_loan_types.append(loan_type_name)
elif not loan_type_company:
loan_type_doc = frappe.get_doc("Loan Type", loan.loan_type)
loan_type_doc.is_term_loan = 1
loan_type_doc.company = loan.company
loan_type_doc.mode_of_payment = loan.mode_of_payment
loan_type_doc.payment_account = loan.payment_account
loan_type_doc.loan_account = loan.loan_account
loan_type_doc.interest_income_account = loan.interest_income_account
loan_type_doc.penalty_income_account = penalty_account
loan_type_doc.submit()
updated_loan_types.append(loan.loan_type)
loan_type = loan.loan_type
if loan_type in updated_loan_types:
if loan.status == "Fully Disbursed":
status = "Disbursed"
elif loan.status == "Repaid/Closed":
status = "Closed"
else:
status = loan.status
frappe.db.set_value(
"Loan",
loan.name,
{"is_term_loan": 1, "penalty_income_account": penalty_account, "status": status},
)
process_loan_interest_accrual_for_term_loans(
posting_date=nowdate(), loan_type=loan_type, loan=loan.name
)
if frappe.db.has_column("Repayment Schedule", "paid"):
total_principal, total_interest = frappe.db.get_value(
"Repayment Schedule",
{"paid": 1, "parent": loan.name},
["sum(principal_amount) as total_principal", "sum(interest_amount) as total_interest"],
)
accrued_entries = get_accrued_interest_entries(loan.name)
for entry in accrued_entries:
interest_paid = 0
principal_paid = 0
if flt(total_interest) > flt(entry.interest_amount):
interest_paid = flt(entry.interest_amount)
else:
interest_paid = flt(total_interest)
if flt(total_principal) > flt(entry.payable_principal_amount):
principal_paid = flt(entry.payable_principal_amount)
else:
principal_paid = flt(total_principal)
frappe.db.sql(
""" UPDATE `tabLoan Interest Accrual`
SET paid_principal_amount = `paid_principal_amount` + %s,
paid_interest_amount = `paid_interest_amount` + %s
WHERE name = %s""",
(principal_paid, interest_paid, entry.name),
)
total_principal = flt(total_principal) - principal_paid
total_interest = flt(total_interest) - interest_paid
