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
}