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(); } }