// method for sending new loan details to databse and returning result
public bool NewLoanDetails(Loan loan, string cr)
{
//decimal atb, int repaymentPeriod, string creditRating
decimal atb = loan.ATB;
int rp = loan.RepaymentPeriod;
decimal loanBalance = loan.ATB;
bool result = false;
loan.LoanApplicationDate = DateTime.Today;
loan.MonthlyRepayment = GetMontlyRepayment(atb, rp, cr);
loan.PaymentsMade = 0;
loan.LoanBalance = loanBalance;
loan.LoanStatus = "Pending";
loan.OutstandingLoans = GetOutstandingLoan(loan);
if (loan.OutstandingLoans == true)
{
loan.OutstandingLoansValue += GetOutstandingLoanValue(loan);
}
//string result = String.Empty;
try
{
DALLoanManager DALMngr = new DALLoanManager();
result = DALMngr.PassNewLoanDetails(loan);
return result;
}
catch (Exception ex)
{
throw;
}
}
public ApproveLoan(Loan loan)
{
InitializeComponent();
LoanToBeApproved = loan;
txtLRN.Text = loan.LRN.ToString();
txtATB.Text = loan.ATB.ToString();
dtpLoanApplicationDate.Value = loan.LoanApplicationDate;
dtpLoanCommencementDate.Value = DateTime.Today;
txtRepaymentPeriod.Text = loan.RepaymentPeriod.ToString();
txtComments.Text = loan.Comments.ToString();
txtLoanType.Text = loan.LoanType.ToString();
txtMonthlyRepayment.Text = loan.MonthlyRepayment.ToString();
if (loan.OutstandingLoans == true)
{
rBtnYes.Checked = true;
}
else
{
rBtnNo.Checked = true;
}
txtOutstandingLoansValue.Text = loan.OutstandingLoansValue.ToString();
}
public MakePayment(Loan loan)
{
InitializeComponent();
txtLoanBalance.Text = loan.LoanBalance.ToString();
txtLRN.Text = loan.LRN.ToString();
txtPaymentsMade.Text = loan.PaymentsMade.ToString();
txtMonthlyPayment.Text = loan.MonthlyRepayment.ToString();
txtRepaymentPeriod.Text = loan.RepaymentPeriod.ToString();
LoanBeingPay = loan;
rBtnRegularPayment.Checked = true;
}
//public method to pass over details
public bool PassNewLoanDetails(Loan loan)
{
bool result = false;
try
{
result = NewLoanDetailsToDB(loan);
}
catch (SqlException ex)
{
throw;
}
return result;
}
public EditLoanDetails(Applicant applicant, Loan loan)
{
InitializeComponent();
LoanToUpdate = loan;
AppSeekingLoanUpdate = applicant;
txtFirstName.Text = applicant.FirstName;
txtSurname.Text = applicant.Surname;
txtEmail.Text = applicant.Email;
txtPhone.Text = applicant.Phone;
txtCRN.Text = applicant.CRN.ToString();
txtEmploymentStatus.Text = applicant.EmploymentStatus.ToString();
txtWeeklyNetPay.Text = applicant.WeeklyNetPay.ToString();
txtWeeklyOutgoings.Text = applicant.WeeklyOutgoings.ToString();
txtCreditRating.Text = applicant.CreditRating.ToString();
txtLRN.Text = loan.LRN.ToString();
dtpLoanApplicationDate.Value = loan.LoanApplicationDate;
dtpLoanCommencementDate.Value = loan.LoanCommencementDate;
txtATB.Text = loan.ATB.ToString();
//txtRepaymentPeriod.Text = loan.RepaymentPeriod.ToString();
cboRepaymentPeriod.Text = loan.RepaymentPeriod.ToString();
txtPaymentsMade.Text = loan.PaymentsMade.ToString();
txtMonthlyRepayment.Text = loan.MonthlyRepayment.ToString();
txtLoanBalance.Text = loan.LoanBalance.ToString();
txtLoanType.Text = loan.LoanType.ToString();
txtComments.Text = loan.Comments;
if (loan.OutstandingLoans == true)
{
rBtnYes.Checked = true;
}
else
{
rBtnNo.Checked = true;
}
txtOutstandingLoansValue.Text = loan.OutstandingLoans.ToString();
}
// method for sending loan payment details to database
private bool LoanPaymentDetailsToDB(Loan loan)
{
bool result = false;
try
{
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spMakeLoanPayment", Cxn))
{
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter UpdateLRNParam = new SqlParameter("@lrn", SqlDbType.Int, 4);
SqlParameter UpdateRepaymentPeriodParam = new SqlParameter("@repayperiod", SqlDbType.Int, 4);
SqlParameter UpdateMonthlyPaymentParam = new SqlParameter("@monthlypayment", SqlDbType.Decimal);
SqlParameter UpdatePaymentsMadeParam = new SqlParameter("@paymentsmade", SqlDbType.Decimal);
SqlParameter UpdateLoanBalanceParam = new SqlParameter("@loanbalance", SqlDbType.Decimal);
SqlParameter UpdateOutstandingLoansParam = new SqlParameter("@outloans", SqlDbType.Bit);
SqlParameter UpdateOutstandingLoansValueParam = new SqlParameter("@outloansvalue", SqlDbType.Decimal);
SqlParameter UpdateLoanStatusParam = new SqlParameter("@loanstatus", SqlDbType.NVarChar, 10);
UpdateLRNParam.Value = loan.LRN;
UpdateRepaymentPeriodParam.Value = loan.RepaymentPeriod;
UpdateMonthlyPaymentParam.Value = loan.MonthlyRepayment;
UpdatePaymentsMadeParam.Value = loan.PaymentsMade;
UpdateLoanBalanceParam.Value = loan.LoanBalance;
UpdateOutstandingLoansParam.Value = loan.OutstandingLoans;
UpdateOutstandingLoansValueParam.Value = loan.OutstandingLoansValue;
UpdateLoanStatusParam.Value = loan.LoanStatus;
Cmd.Parameters.Add(UpdateLRNParam);
Cmd.Parameters.Add(UpdateRepaymentPeriodParam);
Cmd.Parameters.Add(UpdateMonthlyPaymentParam);
Cmd.Parameters.Add(UpdatePaymentsMadeParam);
Cmd.Parameters.Add(UpdateLoanBalanceParam);
Cmd.Parameters.Add(UpdateOutstandingLoansParam);
Cmd.Parameters.Add(UpdateOutstandingLoansValueParam);
Cmd.Parameters.Add(UpdateLoanStatusParam);
Cxn.Open();
int i = Cmd.ExecuteNonQuery();
if (i > 0)
{
result = true;
}
else
{
result = false;
}
Cxn.Close();
}
}
}
catch (SqlException ex)
{
throw;
}
return result;
}
// method which inserts new loan details into database
private bool NewLoanDetailsToDB(Loan loan)
{
bool result = false;
//string result = String.Empty;
try
{
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spInsertLoanDetails", Cxn))
{
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter InsertCRNParam = new SqlParameter("@crn", SqlDbType.Int, 4);
SqlParameter InsertAppDateParam = new SqlParameter("@loanappdate", SqlDbType.DateTime);
SqlParameter InsertComDateParam = new SqlParameter("@loancomdate", SqlDbType.DateTime);
SqlParameter InsertATBParam = new SqlParameter("@atb", SqlDbType.Decimal);
SqlParameter InsertRepaymentPeriodParam = new SqlParameter("@repayperiod", SqlDbType.Int, 4);
SqlParameter InsertMonthlyPaymentParam = new SqlParameter("@monthly", SqlDbType.Decimal);
SqlParameter InsertPaymentsMadeParam = new SqlParameter("@paymentsmade", SqlDbType.Decimal);
SqlParameter InsertLoanBalanceParam = new SqlParameter("@loanbalance", SqlDbType.Decimal);
SqlParameter InsertLoanTypeParam = new SqlParameter("@loantype", SqlDbType.Int, 4);
SqlParameter InsertCommentsParam = new SqlParameter("@comments", SqlDbType.NVarChar, 256);
SqlParameter InsertOutstandingLoansParam = new SqlParameter("@outloans", SqlDbType.Bit);
SqlParameter InsertOutstandingLoansValueParam = new SqlParameter("@outloanvalue", SqlDbType.Decimal);
SqlParameter InsertLoanStatusParam = new SqlParameter("@status", SqlDbType.NVarChar, 10);
InsertCRNParam.Value = loan.CRN;
InsertAppDateParam.Value = loan.LoanApplicationDate;
InsertComDateParam.Value = loan.LoanCommencementDate;
InsertATBParam.Value = loan.ATB;
InsertRepaymentPeriodParam.Value = loan.RepaymentPeriod;
InsertMonthlyPaymentParam.Value = loan.MonthlyRepayment;
InsertPaymentsMadeParam.Value = loan.PaymentsMade;
InsertLoanBalanceParam.Value = loan.LoanBalance;
InsertLoanTypeParam.Value = loan.LoanType;
InsertCommentsParam.Value = loan.Comments;
InsertOutstandingLoansParam.Value = loan.OutstandingLoans;
InsertOutstandingLoansValueParam.Value = loan.OutstandingLoansValue;
InsertLoanStatusParam.Value = loan.LoanStatus;
Cmd.Parameters.Add(InsertCRNParam);
Cmd.Parameters.Add(InsertAppDateParam);
Cmd.Parameters.Add(InsertComDateParam);
Cmd.Parameters.Add(InsertATBParam);
Cmd.Parameters.Add(InsertRepaymentPeriodParam);
Cmd.Parameters.Add(InsertMonthlyPaymentParam);
Cmd.Parameters.Add(InsertPaymentsMadeParam);
Cmd.Parameters.Add(InsertLoanBalanceParam);
Cmd.Parameters.Add(InsertLoanTypeParam);
Cmd.Parameters.Add(InsertCommentsParam);
Cmd.Parameters.Add(InsertOutstandingLoansParam);
Cmd.Parameters.Add(InsertOutstandingLoansValueParam);
Cmd.Parameters.Add(InsertLoanStatusParam);
Cxn.Open();
int i = Cmd.ExecuteNonQuery();
if (i > 0)
{
result = true;
}
else
{
result = false;
}
Cxn.Close();
}
}
return result;
}
catch (SqlException ex)
{
throw;
}
}
// method for sending updated loan to database
private bool UpdatedLoanDetailsToDB(Loan loan)
{
bool result = false;
try
{
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spUpdateLoanDetails", Cxn))
{
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter UpdateLRNParam = new SqlParameter("@lrn", SqlDbType.Int, 4);
SqlParameter UpdateLoanComDateParam = new SqlParameter("@comdate", SqlDbType.DateTime);
SqlParameter UpdateATBParam = new SqlParameter("@atb", SqlDbType.Decimal);
SqlParameter UpdateRepaymentPeriodParam = new SqlParameter("@repayperiod", SqlDbType.Int, 4);
SqlParameter UpdateCommentsParam = new SqlParameter("@comments", SqlDbType.NVarChar, 256);
UpdateLRNParam.Value = loan.LRN;
UpdateLoanComDateParam.Value = loan.LoanCommencementDate;
UpdateATBParam.Value = loan.ATB;
UpdateRepaymentPeriodParam.Value = loan.RepaymentPeriod;
UpdateCommentsParam.Value = loan.Comments;
Cmd.Parameters.Add(UpdateLRNParam);
Cmd.Parameters.Add(UpdateLoanComDateParam);
Cmd.Parameters.Add(UpdateATBParam);
Cmd.Parameters.Add(UpdateRepaymentPeriodParam);
Cmd.Parameters.Add(UpdateCommentsParam);
Cxn.Open();
int i = Cmd.ExecuteNonQuery();
if (i > 0)
{
result = true;
}
else
{
result = false;
}
Cxn.Close();
}
}
}
catch (SqlException ex)
{
throw;
}
return result;
}
// public method calls private
public bool PassLoanPaymentDetailsToDB(Loan loan)
{
bool result = false;
try
{
result = LoanPaymentDetailsToDB(loan);
}
catch (SqlException ex)
{
throw;
}
return result;
}
// method which returns list of loans from database
private List<Loan> GetLoanList()
{
try
{
List<Loan> ListOfLoans = new List<Loan>(12);
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spGetAllLoans", Cxn))
{
Cmd.CommandType = CommandType.StoredProcedure;
Cxn.Open();
using (SqlDataReader dr = Cmd.ExecuteReader())
{
while (dr.Read())
{
//SPGetLoanDetails_PaymentsMade,
//SPGetLoanDetails_LoanBalance,
int crn = Convert.ToInt32(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_CRN)));
DateTime loanAppDate = Convert.ToDateTime(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LoanApplicationDate)));
DateTime loanComDate = Convert.ToDateTime(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LoanCommencementDate)));
decimal atb = Convert.ToDecimal(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_ATB)));
int repayPeriod = Convert.ToInt32(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_RepaymentPeriod)));
decimal monthlyPayment = Convert.ToDecimal(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_MonthlyRepayment)));
decimal paymentsMade = Convert.ToDecimal(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_PaymentsMade)));
decimal loanBalance = Convert.ToDecimal(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LoanBalance)));
LoanType loanType = (LoanType)Convert.ToInt32(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LoanType)));
string comments = dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_Comments)).ToString();
//bool outstandingLoans = Convert.ToBoolean(dr.GetValue(Convert.ToInt32(LOAN_SPGetLoanDetails.SPGetLoanDetails_OutstandingLoans)));
bool outstandingLoans = (dr.GetBoolean(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_OutstandingLoans)));
decimal outstandingLoansValue = Convert.ToDecimal(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_OutstandingLoansValue)));
string loanStatus = dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LoanStatus)).ToString();
int lrn = Convert.ToInt32(dr.GetValue(Convert.ToInt32(LOAN_SPGetNewLoanDetails.SPGetLoanDetails_LRN)));
//outstandingLoans, outstandingLoansValue
Loan loan = new Loan(crn, loanComDate, atb, repayPeriod, loanType, comments);
loan.LoanApplicationDate = loanAppDate;
loan.MonthlyRepayment = monthlyPayment;
loan.OutstandingLoans = outstandingLoans;
loan.OutstandingLoansValue = outstandingLoansValue;
loan.PaymentsMade = paymentsMade;
loan.LoanBalance = loanBalance;
loan.LoanStatus = loanStatus;
loan.LRN = lrn;
ListOfLoans.Add(loan);
}
dr.Close();
Cxn.Close();
}
}
}
return ListOfLoans;
}
catch (SqlException ex)
{
throw;
}
}
// public method calls private
public bool PassUpdatedLoanDetails(Loan loan)
{
bool result = false;
try
{
result = UpdatedLoanDetails(loan);
}
catch (Exception ex)
{
throw;
}
return result;
}
// send updated loan details to data access layer
private bool UpdatedLoanDetails(Loan loan)
{
bool result = false;
try
{
DALLoanManager DALMngr = new DALLoanManager();
result = DALMngr.PassUpdatedLoanDetailsToDB(loan);
}
catch (Exception ex)
{
throw;
}
return result;
}
// public method which calls redemption charge private method
public decimal RedemptionCharge(Loan loan, decimal payment)
{
decimal redemptionCharge = 0;
try
{
redemptionCharge = GetRedemptionCharge(loan, payment);
}
catch (Exception ex)
{
throw;
}
return redemptionCharge;
}
// method to return the value of an applicant's outstanding loan
private decimal GetOutstandingLoanValue(Loan loan)
{
List<Loan> LoanList = null;
decimal loansValue = 0;
try
{
DALLoanManager DALMngr = new DALLoanManager();
LoanList = DALMngr.CallGetLoanList();
foreach (Loan l in LoanList)
{
if (l.CRN == loan.CRN)
{
loansValue = l.OutstandingLoansValue;
}
}
}
catch (Exception ex)
{
throw;
}
return loansValue;
}
// public method passes info to private
public bool PassMakeLoanPayment(Loan loan)
{
bool result = false;
try
{
result = MakeLoanPayment(loan);
}
catch (Exception ex)
{
throw;
}
return result;
}
// method to return whether an applicant has an outstanding loan
private bool GetOutstandingLoan(Loan loan)
{
List<Loan> LoanList = null;
bool outLoans = false;
try
{
DALLoanManager DALMngr = new DALLoanManager();
LoanList = DALMngr.CallGetLoanList();
foreach (Loan l in LoanList)
{
if (l.CRN == loan.CRN)
{
if (l.OutstandingLoans == true)
{
outLoans = true;
}
}
}
}
catch (Exception ex)
{
throw;
}
return outLoans;
}
// method which sends the updated details of an approved loan to the DAL
public bool ApproveLoanDetails(Loan loan)
{
bool result = false;
loan.LoanCommencementDate = DateTime.Today;
loan.OutstandingLoans = true;
loan.OutstandingLoansValue += loan.ATB;
loan.LoanStatus = "Live";
try
{
DALLoanManager DALMngr = new DALLoanManager();
result = DALMngr.CallApproveLoanToDB(loan);
}
catch (Exception ex)
{
throw;
}
return result;
}
// method for calculating redemption charge
private decimal GetRedemptionCharge(Loan loan, decimal payment)
{
decimal redemptionCharge = 0;
decimal percentage = 0;
decimal balance = loan.LoanBalance;
decimal paymentsMade = loan.PaymentsMade;
int paymentPeriod = loan.RepaymentPeriod;
if (payment >= (balance / 2))
{
if (paymentPeriod <= 3)
{
redemptionCharge = 0;
}
else if (paymentPeriod >= 4 && paymentPeriod <= 6)
{
percentage = 0.025M;
redemptionCharge = payment * percentage;
}
else if (paymentPeriod >= 7 && paymentPeriod <= 12)
{
percentage = 0.05M;
redemptionCharge = payment * percentage;
}
else if (paymentPeriod >= 13 && paymentPeriod <= 18)
{
percentage = 0.12M;
redemptionCharge = payment * percentage;
}
else if (paymentPeriod >= 19)
{
percentage = 0.15M;
redemptionCharge = payment * percentage;
}
}
return redemptionCharge;
}
// public method calls private
public bool CallApproveLoanToDB(Loan loan)
{
bool result = false;
try
{
result = ApproveLoanToDB(loan);
}
catch (SqlException ex)
{
throw;
}
return result;
}
// method to get loan details from the form
private Loan GetLoanDetailsFromForm()
{
int crn = int.Parse(txtCRN.Text.ToString());
//DateTime loanAppDate = DateTime.Today;
DateTime loanComDate = DateTime.MaxValue;
decimal atb = 0;
int repayPeriod = 0;
LoanType loanType = 0;
string comments = String.Empty;
validations = true;
Validations validator = new Validations();
Loan loan = null;
try
{
// loan application date and loan application commencement date
if (dtpLoanCommencementDate.Value > DateTime.Today)
{
loanComDate = dtpLoanCommencementDate.Value;
}
else
{
MessageBox.Show("Error: check loan application date and loan application commencement values");
}
// amount to be paid back
if (txtATB.Text != null)
{
//atb = decimal.Parse(txtATB.Text);
validations = decimal.TryParse(txtATB.Text, out atb);
if (!validations)
{
txtATB.Text = "Invalid Entry";
validations = false;
}
}
else
{
MessageBox.Show("Error: must specify amount to be borrowed");
}
// repayment period
if (cboRepaymentPeriod.SelectedItem != null)
{
repayPeriod = int.Parse(cboRepaymentPeriod.SelectedItem.ToString());
}
else
{
MessageBox.Show("Error: must specify repayment period");
}
// loan type
if (cboLoanType.SelectedItem != null)
{
string loanT = cboLoanType.SelectedItem.ToString();
loanType = (LoanType)Enum.Parse(typeof(LoanType), loanT);
}
else
{
MessageBox.Show("Error: must specify loan type");
}
// comments
comments = txtComments.Text.ToString();
loan = new Loan(crn, loanComDate, atb, repayPeriod, loanType, comments);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw;
}
return loan;
}
// method for sending loan payment details to database
private bool ApproveLoanToDB(Loan loan)
{
bool result = false;
try
{
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spApproveLoan", Cxn))
{
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter UpdateLRNParam = new SqlParameter("@lrn", SqlDbType.Int, 4);
SqlParameter UpdateComDateParam = new SqlParameter("@loancomdate", SqlDbType.DateTime);
SqlParameter UpdateOutstandingLoansParam = new SqlParameter("@outloans", SqlDbType.Bit);
SqlParameter UpdateOutstandingLoansValueParam = new SqlParameter("@outloansvalue", SqlDbType.Decimal);
SqlParameter UpdateLoanStatusParam = new SqlParameter("@loanstatus", SqlDbType.NVarChar, 10);
UpdateLRNParam.Value = loan.LRN;
UpdateComDateParam.Value = loan.LoanCommencementDate;
UpdateOutstandingLoansParam.Value = loan.OutstandingLoans;
UpdateOutstandingLoansValueParam.Value = loan.OutstandingLoansValue;
UpdateLoanStatusParam.Value = loan.LoanStatus;
Cmd.Parameters.Add(UpdateLRNParam);
Cmd.Parameters.Add(UpdateComDateParam);
Cmd.Parameters.Add(UpdateOutstandingLoansParam);
Cmd.Parameters.Add(UpdateOutstandingLoansValueParam);
Cmd.Parameters.Add(UpdateLoanStatusParam);
Cxn.Open();
int i = Cmd.ExecuteNonQuery();
if (i > 0)
{
result = true;
}
else
{
result = false;
}
Cxn.Close();
}
}
}
catch (SqlException ex)
{
throw;
}
return result;
}
// send loan payment details to DAL
private bool MakeLoanPayment(Loan loan)
{
bool result = false;
try
{
DALLoanManager DALMngr = new DALLoanManager();
result = DALMngr.PassLoanPaymentDetailsToDB(loan);
}
catch (Exception ex)
{
throw;
}
return result;
}
