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