示例#1
0
        private void editToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            EditLoansBroughtForward editLoansBroughtForward = new EditLoansBroughtForward(userId);

            editLoansBroughtForward.MdiParent = this;
            editLoansBroughtForward.Show();
        }
        private void executeEditLoansBF()
        {
            if (txtAmount.Text != string.Empty && txtInterest.Text != string.Empty && txtTotalRepayment.Text != string.Empty && txtAmountPaid.Text != string.Empty && txtMonthlyRepayment.Text != string.Empty)
            {
                SqlConnection conn = ConnectDB.GetConnection();
                conn.Open();

                try
                {
                    SqlTransaction sqlTrans = conn.BeginTransaction();


                    string QueryLoanApp = "Update LoanApplication set AppMonth=@AppMonth, AppYear=@AppYear,LoanCategoryID=@LoanCategoryID, " +
                                          "LoanTypeID=@LoanTypeID,LoanAmount=@LoanAmount,StartRepaymentMonth=@StartRepaymentMonth,StartRepaymentYear=@StartRepaymentYear," +
                                          "LoanDuration=@LoanDuration,InterestRate=@InterestRate,InterestAmount=@InterestAmount,TotalRepayment=@TotalRepayment, " +
                                          "MonthlyRepayment=@MonthlyRepayment where LoanApplicationID=@LoanApplicationID";

                    SqlCommand cmd = new SqlCommand(QueryLoanApp, conn);
                    cmd.Transaction = sqlTrans;

                    //MessageBox.Show("Application  ID: " + loanApplicationID.ToString());
                    #region parameters
                    cmd.Parameters.Add("@AppMonth", SqlDbType.Int);
                    cmd.Parameters["@AppMonth"].Value = dtAppDate.Value.Month.ToString();

                    cmd.Parameters.Add("@AppYear", SqlDbType.Int);
                    cmd.Parameters["@AppYear"].Value = dtAppDate.Value.Year.ToString();

                    cmd.Parameters.Add("@LoanCategoryID", SqlDbType.Int);
                    cmd.Parameters["@LoanCategoryID"].Value = cboLoanCategory.SelectedValue;

                    cmd.Parameters.Add("@LoanTypeID", SqlDbType.Int);
                    cmd.Parameters["@LoanTypeID"].Value = cboLoanType.SelectedValue;

                    cmd.Parameters.Add("@LoanAmount", SqlDbType.Decimal);
                    cmd.Parameters["@LoanAmount"].Value = txtAmount.Text;

                    cmd.Parameters.Add("@StartRepaymentMonth", SqlDbType.Int);
                    cmd.Parameters["@StartRepaymentMonth"].Value = dtStartRepayment.Value.Month.ToString();

                    cmd.Parameters.Add("@StartRepaymentYear", SqlDbType.Int);
                    cmd.Parameters["@StartRepaymentYear"].Value = dtStartRepayment.Value.Year.ToString();

                    cmd.Parameters.Add("@LoanDuration", SqlDbType.Int);
                    cmd.Parameters["@LoanDuration"].Value = lblDuration.Text;

                    cmd.Parameters.Add("@InterestRate", SqlDbType.Decimal);
                    cmd.Parameters["@InterestRate"].Value = lblInterestRate.Text;

                    cmd.Parameters.Add("@InterestAmount", SqlDbType.Decimal);
                    cmd.Parameters["@InterestAmount"].Value = txtInterest.Text;

                    cmd.Parameters.Add("@TotalRepayment", SqlDbType.Decimal);
                    cmd.Parameters["@TotalRepayment"].Value = txtTotalRepayment.Text;

                    cmd.Parameters.Add("@MonthlyRepayment", SqlDbType.Decimal);
                    cmd.Parameters["@MonthlyRepayment"].Value = txtMonthlyRepayment.Text;

                    cmd.Parameters.Add("@LoanApplicationID", SqlDbType.Int);
                    cmd.Parameters["@LoanApplicationID"].Value = loanApplicationID;
                    #endregion


                    int rowAffected_QueryLoanApp = cmd.ExecuteNonQuery();
                    MessageBox.Show("QueryLoanApp: " + rowAffected_QueryLoanApp.ToString());

                    string QueryLoans;

                    if (dateFinishedPayment != string.Empty)
                    {
                        QueryLoans = "Update Loans set RepaymentAmount=@RepaymentAmount,AmountPaid=@AmountPaid,OutstandingAmount=@OutstandingAmount, " +
                                     "PaymentStatus=@PaymentStatus,PaymentFinished=@PaymentFinished,DateFinishedPayment=@DateFinishedPayment,Remark=@Remark " +
                                     "where LoanApplicationID=@LoanApplicationID";
                    }
                    else
                    {
                        QueryLoans = "Update Loans set RepaymentAmount=@RepaymentAmount,AmountPaid=@AmountPaid,OutstandingAmount=@OutstandingAmount, " +
                                     "PaymentStatus=@PaymentStatus,PaymentFinished=@PaymentFinished,Remark=@Remark " +
                                     "where LoanApplicationID=@LoanApplicationID";
                    }

                    cmd.CommandText = QueryLoans;
                    //cmd.Transaction = sqlTrans;

                    #region parameters
                    cmd.Parameters.Add("@RepaymentAmount", SqlDbType.Decimal);
                    cmd.Parameters["@RepaymentAmount"].Value = txtTotalRepayment.Text;

                    cmd.Parameters.Add("@AmountPaid", SqlDbType.Decimal);
                    cmd.Parameters["@AmountPaid"].Value = txtAmountPaid.Text;

                    cmd.Parameters.Add("@OutstandingAmount", SqlDbType.Decimal);
                    cmd.Parameters["@OutstandingAmount"].Value = txtOutstandingAmt.Text;

                    cmd.Parameters.Add("@PaymentStatus", SqlDbType.NVarChar, 10);
                    cmd.Parameters["@PaymentStatus"].Value = paymentStatus;

                    cmd.Parameters.Add("@PaymentFinished", SqlDbType.NVarChar, 5);
                    cmd.Parameters["@PaymentFinished"].Value = paymentFinished;

                    if (dateFinishedPayment != string.Empty)
                    {
                        cmd.Parameters.Add("@DateFinishedPayment", SqlDbType.Date);
                        cmd.Parameters["@DateFinishedPayment"].Value = DateTime.Parse(dateFinishedPayment);
                    }

                    cmd.Parameters.Add("@Remark", SqlDbType.NVarChar, 1000);
                    cmd.Parameters["@Remark"].Value = txtRemark.Text;

                    cmd.Parameters["@LoanApplicationID"].Value = loanApplicationID;

                    #endregion

                    int rowAffected_QueryLoans = cmd.ExecuteNonQuery();
                    MessageBox.Show("QueryLoans: " + rowAffected_QueryLoans.ToString());

                    if ((rowAffected_QueryLoanApp > 0) && (rowAffected_QueryLoans > 0))
                    {
                        sqlTrans.Commit();
                        MessageBox.Show("Record has been successfully edited.", "Loans Forward", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ActivityLog.logActivity(userId, "LoansForward", "Edit Loans Brought Forward with LoanApplication ID of " + loanApplicationID);

                        EditLoansBroughtForward editLoansBroughtForward = new EditLoansBroughtForward(userId);
                        editLoansBroughtForward.MdiParent = this.ParentForm;
                        editLoansBroughtForward.Show();

                        this.Close();
                    }
                    else
                    {
                        sqlTrans.Rollback();
                        MessageBox.Show("An error occurred editing record.", "Loans Forward", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }//end of if statement check
        }