private void LoanApplication_Load(object sender, EventArgs e)
{
DateTime appDate = DateTime.Now;
lblAppMonthYear.Text = "Loan Application for " + appDate.ToString("MMMM, yyyy");
//MessageBox.Show(DateFunction.getMonthByName(appDate.Month));
cboMonth.Text = DateFunction.getMonthByName(appDate.Month);
cboYear.Text = appDate.Year.ToString();
loadMemberFileNoIntoCboFileNo();
loadLoanCategoryIntoCboCategory();
txtLoanDuration.TextAlign = HorizontalAlignment.Right;
txtLoanInterestRate.TextAlign = HorizontalAlignment.Right;
txtAmount.TextAlign = HorizontalAlignment.Right;
txtInterestAmount.TextAlign = HorizontalAlignment.Right;
txtTotalRepaymentAmount.TextAlign = HorizontalAlignment.Right;
txtMonthRepayment.TextAlign = HorizontalAlignment.Right;
lblSuretyMemberID_1.Text = string.Empty;
lblSuretyMemberID_2.Text = string.Empty;
lblSuretyMemberID_3.Text = string.Empty;
lblSurety1.Text = string.Empty;
lblSurety2.Text = string.Empty;
lblWitness.Text = string.Empty;
}
private void getSelectedLoanDetail(string transactionID)
{
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Select l.LoansID as [Id], l.TransactionID as [Transact. ID], " +
"a.TotalRepayment, l.AmountPaid, l.OutstandingAmount, a.ApprovalStatus, " +
"a.StartRepaymentMonth, a.StartRepaymentYear, t.Type, t.Duration, t.InterestRate, a.LoanAmount, a.InterestAmount, a.MonthlyRepayment " +
"from Loans l inner join LoanApplication a on l.TransactionID=a.TransactionID " +
"left join LoanType t on a.LoanTypeID=t.LoanTypeID " +
"where a.ApprovalStatus='Yes' " +
"and a.TransactionID='" + transactionID + "' order by l.LoansID desc";
SqlCommand cmd = new SqlCommand(strQuery, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
//MessageBox.Show(reader["StartRepaymentMonth"].ToString());
txtStartRepayment.Text = DateFunction.getMonthByName(Convert.ToInt32(reader["StartRepaymentMonth"])) + " " + reader["StartRepaymentYear"].ToString();
txtLoanType.Text = reader["Type"].ToString();
txtDuration.Text = reader["Duration"].ToString();
txtInterestRate.Text = reader["InterestRate"].ToString();
txtLoanAmount.Text = CheckForNumber.formatCurrency(reader["LoanAmount"].ToString());
txtRepaymentAmount.Text = CheckForNumber.formatCurrency(reader["TotalRepayment"].ToString());
txtInterestAmount.Text = CheckForNumber.formatCurrency(reader["InterestAmount"].ToString());
txtMonthlyRepayment.Text = CheckForNumber.formatCurrency(reader["MonthlyRepayment"].ToString());
txtAmountPaid.Text = CheckForNumber.formatCurrency(reader["AmountPaid"].ToString());
txtOutstanding.Text = CheckForNumber.formatCurrency(reader["OutstandingAmount"].ToString());
//alignment of controls
txtLoanAmount.TextAlign = HorizontalAlignment.Right;
txtRepaymentAmount.TextAlign = HorizontalAlignment.Right;
txtInterestAmount.TextAlign = HorizontalAlignment.Right;
txtMonthlyRepayment.TextAlign = HorizontalAlignment.Right;
txtAmountPaid.TextAlign = HorizontalAlignment.Right;
txtOutstanding.TextAlign = HorizontalAlignment.Right;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void getLoanApplicationDetails(string transactionID, string loanApplicationID)
{
txtApplicationID.Text = loanApplicationID;
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Select mn.Month as [AppMonth],a.AppYear as [AppYear],m.FileNo,m.LastName as [LastName],m.FirstName as [FirstName],m.MiddleName as [MiddleName], m.Photo," +
"c.Name as [LoanCategory],t.Type as [LoanType], a.LoanAmount as [Amount],a.TransactionID as [TransactionID],a.DatePosted as [Date], " +
"a.StartRepaymentMonth, a.StartRepaymentYear,a.SuretyMemberID1,a.SuretyMemberID2," +
"a.WitnessMemberID, a.NonMemberSurety1, a.NonMemberSurety2, a.NonMemberWitness, " +
"a.LoanDuration, a.InterestRate, a.InterestAmount, a.TotalRepayment, a.MonthlyRepayment, a.ApprovalStatus from LoanApplication a " +
"left join Members m on a.MemberID=m.MemberID " +
"left join MonthByName mn on a.AppMonth = mn.MonthID " +
"left join LoanCategory c on a.LoanCategoryID = c.LoanCategoryID " +
"left join LoanType t on a.LoanTypeID=t.LoanTypeID " +
"where TransactionID='" + transactionID + "' order by LoanApplicationID desc";
SqlCommand cmd = new SqlCommand(strQuery, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
txtApplicationDate.Text = reader["AppMonth"].ToString() + " " + reader["AppYear"].ToString();
txtCategory.Text = reader["LoanCategory"].ToString();
txtType.Text = reader["LoanType"].ToString();
txtAmount.Text = CheckForNumber.formatCurrency(reader["Amount"].ToString());
txtRepaymentDate.Text = DateFunction.getMonthByName(Convert.ToInt16(reader["StartRepaymentMonth"].ToString())) + " " + reader["StartRepaymentYear"].ToString();
txtTransactionID.Text = reader["TransactionID"].ToString();
txtDuration.Text = reader["LoanDuration"].ToString();
txtInterestRate.Text = CheckForNumber.formatCurrency(reader["InterestRate"].ToString());
txtInterestAmount.Text = CheckForNumber.formatCurrency(reader["InterestAmount"].ToString());
txtTotalRepayment.Text = CheckForNumber.formatCurrency(reader["TotalRepayment"].ToString());
txtMonthRepayment.Text = CheckForNumber.formatCurrency(reader["MonthlyRepayment"].ToString());
txtMemberProfile.Text = reader["LastName"] + " " + reader["FirstName"] + " " + reader["MiddleName"];
lblMemberFileNo.Text = reader["FileNo"].ToString();
if (reader["Photo"].ToString() == null || reader["Photo"].ToString() == string.Empty)
{
picMember.Image = Image.FromFile(paths + "//photos//" + reader["Photo"].ToString());
}
if ((reader["SuretyMemberID1"].ToString() != string.Empty))
{
getSuretyDetails(reader["SuretyMemberID1"].ToString(), "1");
}
else
{
lblSurety1.Text = "Surety 1\n" + reader["NonMemberSurety1"].ToString();
picSurety1.Image = Image.FromFile(paths + "//photos//profile_img.png");
}
if ((reader["SuretyMemberID2"].ToString() != string.Empty))
{
getSuretyDetails(reader["SuretyMemberID2"].ToString(), "2");
}
else
{
lblSurety2.Text = "Surety 2\n" + reader["NonMemberSurety2"].ToString();
picSurety2.Image = Image.FromFile(paths + "//photos//profile_img.png");
}
if ((reader["WitnessMemberID"].ToString() != string.Empty))
{
getSuretyDetails(reader["WitnessMemberID"].ToString(), "3");
}
else
{
lblWitness.Text = "Witness\n" + reader["NonMemberWitness"].ToString();
picWitness.Image = Image.FromFile(paths + "//photos//profile_img.png");
}
//MessageBox.Show(reader["ApprovalStatus"].ToString());
switch (reader["ApprovalStatus"].ToString().Trim())
{
case "":
lblApprovalStatus.Text = "Pending";
lblApprovalStatus.ForeColor = System.Drawing.Color.Black;
radYes.Checked = false;
radNo.Checked = false;
break;
case "Yes":
lblApprovalStatus.Text = "Loan Approved";
lblApprovalStatus.ForeColor = System.Drawing.Color.Green;
radYes.Checked = true;
break;
case "No":
lblApprovalStatus.Text = "Loan Not Approved";
lblApprovalStatus.ForeColor = System.Drawing.Color.Red;
radNo.Checked = true;
break;
}
//check/effect whether to enable or disable approval/disapproval box
DateTime today = DateTime.Now;
DateTime repaymentDate = DateTime.Parse(txtRepaymentDate.Text);
TimeSpan diffBeforeRepayment = today.Subtract(repaymentDate);
int daysBeforeRepayment = (int)diffBeforeRepayment.TotalDays;
if (daysBeforeRepayment >= 0)
{
grpApproval.Enabled = false;
}
else
{
grpApproval.Enabled = true;
}
//check the Application Details Section
this.Height = 591;
}
catch (Exception ex)
{
MessageBox.Show("getLoanApplicationDetails " + ex.Message);
}
finally
{
conn.Close();
}
}
private void clearAllFields()
{
DateTime appDate = DateTime.Now;
lblAppMonthYear.Text = "Loan Application for " + appDate.ToString("MMMM, yyyy");
//MessageBox.Show(DateFunction.getMonthByName(appDate.Month));
cboMonth.Text = DateFunction.getMonthByName(appDate.Month);
cboYear.Text = appDate.Year.ToString();
cboFileNo.SelectedIndex = 0;
cboCategory.SelectedIndex = 0;
cboType.Text = string.Empty;
txtAmount.Text = string.Empty;
chkStartRepayment.Checked = false;
cboStartRepaymentMonth.Text = string.Empty;
cboStartRepaymentYear.Text = string.Empty;
txtSuretyFileNo1.Text = string.Empty;
txtSuretyFileNo2.Text = string.Empty;
txtWitnessFileNo.Text = string.Empty;
chkSurety1.Checked = false;
txtSuretyName1.Text = string.Empty;
chkSurety2.Checked = false;
txtSuretyName2.Text = string.Empty;
chkWitness.Checked = false;
txtWitnessName.Text = string.Empty;
picMember.Image = Image.FromFile(paths + "//photos//profile_img.png");
lblMemberProfile.Text = string.Empty;
lblMemberProfile.Visible = false;
txtLoanDuration.Text = string.Empty;
txtLoanInterestRate.Text = string.Empty;
txtInterestAmount.Text = string.Empty;
txtTotalRepaymentAmount.Text = string.Empty;
txtMonthRepayment.Text = string.Empty;
txtEndRepaymentDate.Text = string.Empty;
picSurety1.Image = Image.FromFile(paths + "//photos/profile_img.png");
picSurety1.Visible = false;
lblSurety1.Text = string.Empty;
lblSurety1.Visible = false;
lblSuretyMemberID_1.Text = string.Empty;
lblSuretyMemberID_1.Visible = false;
picSurety2.Image = Image.FromFile(paths + "//photos/profile_img.png");
picSurety2.Visible = false;
lblSurety2.Text = string.Empty;
lblSurety2.Visible = false;
lblSuretyMemberID_2.Text = string.Empty;
lblSuretyMemberID_2.Visible = false;
picWitness.Image = Image.FromFile(paths + "//photos/profile_img.png");
picWitness.Visible = false;
lblWitness.Text = string.Empty;
lblWitness.Visible = false;
lblSuretyMemberID_3.Text = string.Empty;
lblSuretyMemberID_3.Visible = false;
}
