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;
}
private void saveLoanApplication()
{
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Insert into LoanApplication(AppMonth,AppYear,MemberID,LoanCategoryID,LoanTypeID," +
"LoanAmount,FormFee,StartRepaymentMonth,StartRepaymentYear,SuretyMemberID1,SuretyMemberID2,WitnessMemberID," +
"NonMemberSurety1,NonMemberSurety2,NonMemberWitness,LoanDuration,InterestRate,InterestAmount," +
"TotalRepayment,MonthlyRepayment,TransactionID)values(@AppMonth,@AppYear,@MemberID,@LoanCategoryID,@LoanTypeID," +
"@LoanAmount,@FormFee,@StartRepaymentMonth,@StartRepaymentYear,@SuretyMemberID1,@SuretyMemberID2,@WitnessMemberID," +
"@NonMemberSurety1,@NonMemberSurety2,@NonMemberWitness,@LoanDuration,@InterestRate,@InterestAmount," +
"@TotalRepayment,@MonthlyRepayment,@TransactionID)";
string transactionID = "LOA" + DateTime.Now.ToString("ddMMyyhhmmss");
SqlCommand cmd = new SqlCommand(strQuery, conn);
#region parameters
cmd.Parameters.Add("@AppMonth", SqlDbType.Int);
cmd.Parameters["@AppMonth"].Value = DateFunction.getMonthByDate(cboMonth.Text);
cmd.Parameters.Add("@AppYear", SqlDbType.Int);
cmd.Parameters["@AppYear"].Value = Convert.ToInt32(cboYear.Text);
cmd.Parameters.Add("@MemberID", SqlDbType.Int);
cmd.Parameters["@MemberID"].Value = memberID;
cmd.Parameters.Add("@LoanCategoryID", SqlDbType.Int);
cmd.Parameters["@LoanCategoryID"].Value = Convert.ToInt32(cboCategory.SelectedValue.ToString());
cmd.Parameters.Add("@LoanTypeID", SqlDbType.Int);
cmd.Parameters["@LoanTypeID"].Value = Convert.ToInt32(cboType.SelectedValue.ToString());
cmd.Parameters.Add("@LoanAmount", SqlDbType.Decimal);
cmd.Parameters["@LoanAmount"].Value = Convert.ToDecimal(txtAmount.Text);
cmd.Parameters.Add("@FormFee", SqlDbType.Decimal);
cmd.Parameters["@FormFee"].Value = Convert.ToDecimal(txtFormFee.Text);
int startRepaymentMonth = 0;
int startRepaymentYear = 0;
if ((chkStartRepayment.Checked == true) && (cboStartRepaymentMonth.Text != string.Empty) && (cboStartRepaymentYear.Text != string.Empty))
{
startRepaymentMonth = DateFunction.getMonthByDate(cboStartRepaymentMonth.Text);
startRepaymentYear = Convert.ToInt32(cboStartRepaymentYear.Text);
}
else
{
//DateTime currentDate = DateTime.Now;
//int currentMonth = currentDate.Month;
string strAppDate = cboMonth.Text + " " + cboYear.Text;
DateTime appDate = DateTime.Parse(strAppDate);
DateTime nextMonth = appDate.AddMonths(1);
startRepaymentMonth = nextMonth.Month;
startRepaymentYear = nextMonth.Year;
}
cmd.Parameters.Add("@StartRepaymentMonth", SqlDbType.Int);
cmd.Parameters["@StartRepaymentMonth"].Value = startRepaymentMonth;
cmd.Parameters.Add("@StartRepaymentYear", SqlDbType.Int);
cmd.Parameters["@StartRepaymentYear"].Value = startRepaymentYear;
cmd.Parameters.Add("@SuretyMemberID1", SqlDbType.NVarChar, 10);
cmd.Parameters["@SuretyMemberID1"].Value = lblSuretyMemberID_1.Text;
cmd.Parameters.Add("@SuretyMemberID2", SqlDbType.NVarChar, 10);
cmd.Parameters["@SuretyMemberID2"].Value = lblSuretyMemberID_2.Text;
cmd.Parameters.Add("@WitnessMemberID", SqlDbType.NVarChar, 10);
cmd.Parameters["@WitnessMemberID"].Value = lblSuretyMemberID_3.Text;
cmd.Parameters.Add("@NonMemberSurety1", SqlDbType.NVarChar, 100);
cmd.Parameters["@NonMemberSurety1"].Value = txtSuretyName1.Text;
cmd.Parameters.Add("@NonMemberSurety2", SqlDbType.NVarChar, 100);
cmd.Parameters["@NonMemberSurety2"].Value = txtSuretyName2.Text;
cmd.Parameters.Add("@NonMemberWitness", SqlDbType.NVarChar, 100);
cmd.Parameters["@NonMemberWitness"].Value = txtWitnessName.Text;
cmd.Parameters.Add("@LoanDuration", SqlDbType.Int);
cmd.Parameters["@LoanDuration"].Value = Convert.ToInt32(txtLoanDuration.Text);
cmd.Parameters.Add("@InterestRate", SqlDbType.Decimal);
cmd.Parameters["@InterestRate"].Value = Convert.ToDecimal(txtLoanInterestRate.Text);
cmd.Parameters.Add("@InterestAmount", SqlDbType.Decimal);
cmd.Parameters["@InterestAmount"].Value = Convert.ToDecimal(txtInterestAmount.Text);
cmd.Parameters.Add("@TotalRepayment", SqlDbType.Decimal);
cmd.Parameters["@TotalRepayment"].Value = Convert.ToDecimal(txtTotalRepaymentAmount.Text);
cmd.Parameters.Add("@MonthlyRepayment", SqlDbType.Decimal);
cmd.Parameters["@MonthlyRepayment"].Value = Convert.ToDecimal(txtMonthRepayment.Text);
cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 50);
cmd.Parameters["@TransactionID"].Value = transactionID;
#endregion
try
{
conn.Open();
int rowAffected = cmd.ExecuteNonQuery();
if (rowAffected > 0)
{
MessageBox.Show("Loan Application has been Successfully saved.", "Loan Application", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Hide();
clearAllFields();
string ActivityType = "Loan Application";
string Description = "Loan Application of " + cboType.Text + ". Amount: " + txtAmount.Text + " for " + cboFileNo.Text + " with TransactionID: " + transactionID;
ActivityLog.logActivity(userId, ActivityType, Description);
}
else
{
MessageBox.Show("An error has occurred!", "Loan Application", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}catch (Exception ex)
{
MessageBox.Show(ex.Message);
}finally
{
conn.Close();
}
}