Example #1
0
        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;
        }
Example #2
0
        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();
            }
        }
Example #3
0
        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();
            }
        }
Example #4
0
        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;
        }
Example #5
0
        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();
            }
        }