private void newLoanBFToolStripMenuItem_Click(object sender, EventArgs e)
{
NewLoanBroughtForward newLoanBroughtForward = new NewLoanBroughtForward(userId);
newLoanBroughtForward.MdiParent = this;
newLoanBroughtForward.Show();
}
private void executePosting()
{
btnPost.Enabled = false;
string appMonth = dtAppDate.Value.Month.ToString();
string appYear = dtAppDate.Value.Year.ToString();
string rePay_StartMonth = dtStartRepayment.Value.Month.ToString();
string rePay_StartYear = dtStartRepayment.Value.Year.ToString();
string transactionID = "LBF" + DateTime.Now.ToString("ddMMyyhhmmss");
SqlConnection conn = ConnectDB.GetConnection();
conn.Open();
try
{
SqlTransaction sqlTrans = conn.BeginTransaction();
string strQuery = "Insert into LoanApplication(AppMonth,AppYear,MemberID,LoanCategoryID," +
"LoanTypeID,LoanAmount,StartRepaymentMonth,StartRepaymentYear,LoanDuration,InterestRate," +
"InterestAmount,TotalRepayment,MonthlyRepayment,ApprovalStatus,TransactionID)" +
"values(@AppMonth,@AppYear,@MemberID,@LoanCategoryID,@LoanTypeID,@LoanAmount,@StartRepaymentMonth,@StartRepaymentYear," +
"@LoanDuration,@InterestRate,@InterestAmount,@TotalRepayment,@MonthlyRepayment,@ApprovalStatus,@TransactionID)";
SqlCommand cmd = new SqlCommand(strQuery, conn);
cmd.Transaction = sqlTrans;
#region Parameters
cmd.Parameters.Add("@AppMonth", SqlDbType.Int);
cmd.Parameters["@AppMonth"].Value = appMonth;
cmd.Parameters.Add("@AppYear", SqlDbType.Int);
cmd.Parameters["@AppYear"].Value = appYear;
cmd.Parameters.Add("@MemberID", SqlDbType.Int);
cmd.Parameters["@MemberID"].Value = memberID;
cmd.Parameters.Add("@LoanCategoryID", SqlDbType.Int);
cmd.Parameters["@LoanCategoryID"].Value = cboLoanCategory.SelectedValue.ToString();
cmd.Parameters.Add("@LoanTypeID", SqlDbType.Int);
cmd.Parameters["@LoanTypeID"].Value = cboLoanType.SelectedValue.ToString();
cmd.Parameters.Add("@LoanAmount", SqlDbType.Decimal);
cmd.Parameters["@LoanAmount"].Value = txtAmount.Text;
cmd.Parameters.Add("@StartRepaymentMonth", SqlDbType.Decimal);
cmd.Parameters["@StartRepaymentMonth"].Value = rePay_StartMonth;
cmd.Parameters.Add("@StartRepaymentYear", SqlDbType.Decimal);
cmd.Parameters["@StartRepaymentYear"].Value = rePay_StartYear;
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("@ApprovalStatus", SqlDbType.NVarChar, 3);
cmd.Parameters["@ApprovalStatus"].Value = "Yes";
cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 50);
cmd.Parameters["@TransactionID"].Value = transactionID;
#endregion
string strQuery1;
if (dateFinishedPayment != string.Empty)
{
strQuery1 = "Insert into Loans(LoanApplicationID,RepaymentAmount,AmountPaid,OutstandingAmount," +
"PaymentStatus,PaymentFinished,DateFinishedPayment,TransactionID,Remark)values(@LoanApplicationID," +
"@RepaymentAmount,@AmountPaid,@OutstandingAmount,@PaymentStatus,@PaymentFinished,@DateFinishedPayment," +
"@TransactionID,@Remark)";
}
else
{
strQuery1 = "Insert into Loans(LoanApplicationID,RepaymentAmount,AmountPaid,OutstandingAmount," +
"PaymentStatus,PaymentFinished,TransactionID,Remark)values(@LoanApplicationID," +
"@RepaymentAmount,@AmountPaid,@OutstandingAmount,@PaymentStatus,@PaymentFinished," +
"@TransactionID,@Remark)";
}
string strQuery3 = "Select LoanApplicationID from LoanApplication where TransactionID='" + transactionID + "'";
int rowAffected = cmd.ExecuteNonQuery();
//MessageBox.Show(rowAffected.ToString());
if (rowAffected > 0)
{
cmd = new SqlCommand(strQuery3, conn);
cmd.Transaction = sqlTrans;
int loanApplicationID = Convert.ToInt16(cmd.ExecuteScalar());
//MessageBox.Show(loanApplicationID.ToString());
cmd = new SqlCommand(strQuery1, conn);
cmd.Transaction = sqlTrans;
#region parameters strQuery1
cmd.Parameters.Add("@LoanApplicationID", SqlDbType.Int);
cmd.Parameters["@LoanApplicationID"].Value = loanApplicationID;
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, 3);
cmd.Parameters["@PaymentFinished"].Value = paymentFinished;
if (dateFinishedPayment != string.Empty)
{
cmd.Parameters.Add("@DateFinishedPayment", SqlDbType.NVarChar, 40);
cmd.Parameters["@DateFinishedPayment"].Value = dateFinishedPayment;
}
cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 50);
cmd.Parameters["@TransactionID"].Value = transactionID;
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar, 1000);
cmd.Parameters["@Remark"].Value = txtRemark.Text;
#endregion
//Execute strQuery1
rowAffected = cmd.ExecuteNonQuery();
if (rowAffected > 0)
{
sqlTrans.Commit();
MessageBox.Show("Transaction has been successfully posted.", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Information);
ActivityLog.logActivity(userId, "LoansForward", "Added a new Loans Brought Forward record with TransactionID of " + transactionID);
}
else
{
sqlTrans.Rollback();
MessageBox.Show("An error has occurred! Operation has been aborted.", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Close existing form and reopen active form
NewLoanBroughtForward newLoanBroughtForward = new NewLoanBroughtForward(userId);
newLoanBroughtForward.MdiParent = this.ParentForm;
newLoanBroughtForward.Show();
this.Close();
}
else
{
MessageBox.Show("An error has occurred!", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}