Exemplo n.º 1
0
        private void newLoanBFToolStripMenuItem_Click(object sender, EventArgs e)
        {
            NewLoanBroughtForward newLoanBroughtForward = new NewLoanBroughtForward(userId);

            newLoanBroughtForward.MdiParent = this;
            newLoanBroughtForward.Show();
        }
Exemplo n.º 2
0
        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();
            }
        }