private void btnPostRepayment_Click(object sender, EventArgs e) { DialogResult res = MessageBox.Show("Do you wish to Post this Loan Repayment?", "Loans", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (res == DialogResult.Yes) { #region Execute_Loan_Repayment //declare and initialize variables //MessageBox.Show("Member ID: " + memberID); int loanSize = loanServicing.Count - 1; #region declaration and initialisation decimal amountPaying = 0; decimal savedAmountPaying = 0; decimal rePayment = 0; decimal amountPaid = 0; decimal newAmountPaid = 0; decimal outstanding = 0; decimal newOutstanding = 0; decimal excess = 0; string loanId; string loanToService; string transactionID; string paymentStatus; string paymentFinished; DateTime dateFinishedPayment; bool disContinueLoop = false; string strQuery; string strInsertRepayment; string strUpdateLoan; int rowsAffected; bool dbOperationStatus = true; #endregion end of declaration and initialisation SqlConnection conn = ConnectDB.GetConnection(); SqlCommand cmdSelectLoans; SqlCommand cmdInsertRepayment; SqlCommand cmdUpdateLoan; SqlDataReader reader; SqlTransaction sqlTrans = null; amountPaying = Convert.ToDecimal(txtPayAmount.Text); string rePayTransactID = "PAY" + DateTime.Now.ToString("ddMMyyhhmmss"); //MessageBox.Show("RePayTransactID: " + rePayTransactID); //Execute Loans Servicing #region while section while (loanSize >= 0) { //MessageBox.Show(); //MessageBox.Show("Loan item: " + loanSize.ToString() + " - " + loanServicing[loanSize]); loanToService = loanServicing[loanSize]; loanId = loanToService.Substring(0, loanToService.IndexOf("-")); transactionID = loanToService.Substring(loanToService.IndexOf("-") + 1).Trim(); //MessageBox.Show("Loan ID: " + loanId + " TransactionID: " + transactionID); //Setup command to retrieve Loan Record strQuery = "Select RepaymentAmount, AmountPaid, OutstandingAmount from Loans where LoansID='" + loanId + "' and TransactionID='" + transactionID + "'"; cmdSelectLoans = new SqlCommand(strQuery, conn); #region try section try { //MessageBox.Show("Connection State: " + conn.State); if (conn.State != ConnectionState.Open) { //MessageBox.Show("Db wasn't opened, it is now.."); conn.Open(); sqlTrans = conn.BeginTransaction(); } //Execute cmdSelectLoans Command cmdSelectLoans.Transaction = sqlTrans; reader = cmdSelectLoans.ExecuteReader(); reader.Read(); rePayment = Convert.ToDecimal(reader["RepaymentAmount"]); outstanding = Convert.ToDecimal(reader["OutstandingAmount"]); amountPaid = Convert.ToDecimal(reader["AmountPaid"]); //MessageBox.Show("Outstanding Loan: " + outstanding); savedAmountPaying = amountPaying; if (amountPaying <= outstanding) { newOutstanding = outstanding - amountPaying; newAmountPaid = amountPaying + amountPaid; excess = 0; disContinueLoop = true; } else { excess = amountPaying - outstanding; newOutstanding = 0; newAmountPaid = rePayment; amountPaying = excess; } reader.Close(); //close reader for cmdSelectLoans //MessageBox.Show("New Outstanding: " + newOutstanding + "\nNew Amount Paid: " + newAmountPaid + "\nExcess: " + excess + "\nSaved Amount: " + savedAmountPaying); //Begin Repayment subroutine strInsertRepayment = "Insert into LoanRepayment(MemberID,LoanID,TransactionID,RepaymentAmount,PaidAmount,Outstanding,PaidCumulative,Excess,Remark,RepayTransactID)" + "values(@MemberID,@LoanID,@TransactionID,@RepaymentAmount,@NewAmountPaid,@NewOutstanding,@PaidCumulative,@Excess,@Remark,@RepayTransactID)"; cmdInsertRepayment = new SqlCommand(strInsertRepayment, conn); #region cmdInsertRepayment parameters cmdInsertRepayment.Parameters.Add("@MemberID", SqlDbType.Int); cmdInsertRepayment.Parameters["@MemberID"].Value = memberID; cmdInsertRepayment.Parameters.Add("@LoanID", SqlDbType.Int); cmdInsertRepayment.Parameters["@LoanID"].Value = Convert.ToInt32(loanId); cmdInsertRepayment.Parameters.Add("@TransactionID", SqlDbType.VarChar, 50); cmdInsertRepayment.Parameters["@TransactionID"].Value = transactionID; cmdInsertRepayment.Parameters.Add("@RepaymentAmount", SqlDbType.Decimal); cmdInsertRepayment.Parameters["@RepaymentAmount"].Value = rePayment; cmdInsertRepayment.Parameters.Add("@NewAmountPaid", SqlDbType.Decimal); cmdInsertRepayment.Parameters["@NewAmountPaid"].Value = savedAmountPaying; cmdInsertRepayment.Parameters.Add("@NewOutstanding", SqlDbType.Decimal); cmdInsertRepayment.Parameters["@NewOutstanding"].Value = newOutstanding; cmdInsertRepayment.Parameters.Add("@PaidCumulative", SqlDbType.Decimal); cmdInsertRepayment.Parameters["@PaidCumulative"].Value = newAmountPaid; cmdInsertRepayment.Parameters.Add("@Excess", SqlDbType.Decimal); cmdInsertRepayment.Parameters["@Excess"].Value = excess; cmdInsertRepayment.Parameters.Add("@Remark", SqlDbType.NVarChar, 100); cmdInsertRepayment.Parameters["@Remark"].Value = txtRemark.Text.Trim(); cmdInsertRepayment.Parameters.Add("@RepayTransactID", SqlDbType.NVarChar, 50); cmdInsertRepayment.Parameters["@RepayTransactID"].Value = rePayTransactID; #endregion cmdInsertRepayment.Transaction = sqlTrans; rowsAffected = cmdInsertRepayment.ExecuteNonQuery(); if (rowsAffected < 1) { dbOperationStatus = false; } if (dbOperationStatus == false) { MessageBox.Show("An error has occurred and Operation has been terminated!", "Loan Repayment", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } else { //Begin Loan Update subroutine strUpdateLoan = "Update Loans set AmountPaid=@NewAmountPaid, OutstandingAmount=@NewOutstandingAmount," + "PaymentStatus=@PaymentStatus,PaymentFinished=@PaymentFinished,DateFinishedPayment=@DateFinishedPayment " + "where LoansID=@LoanID and TransactionID=@TransactionID"; if (newOutstanding > 0) { //MessageBox.Show("PayStatus: Paying | PaymentFinished: No"); paymentStatus = "Paying"; paymentFinished = "No"; } else { //MessageBox.Show("PayStatus: PAID | PaymentFinished: Yes"); paymentStatus = "PAID"; paymentFinished = "Yes"; } cmdUpdateLoan = new SqlCommand(strUpdateLoan, conn); #region cmdUpdateLoan parameters cmdUpdateLoan.Parameters.Add("@NewAmountPaid", SqlDbType.Decimal); cmdUpdateLoan.Parameters["@NewAmountPaid"].Value = newAmountPaid; cmdUpdateLoan.Parameters.Add("@NewOutstandingAmount", SqlDbType.Decimal); cmdUpdateLoan.Parameters["@NewOutstandingAmount"].Value = newOutstanding; cmdUpdateLoan.Parameters.Add("@PaymentStatus", SqlDbType.NVarChar, 10); cmdUpdateLoan.Parameters["@PaymentStatus"].Value = paymentStatus; cmdUpdateLoan.Parameters.Add("@PaymentFinished", SqlDbType.NVarChar, 5); cmdUpdateLoan.Parameters["@PaymentFinished"].Value = paymentFinished; dateFinishedPayment = DateTime.Now; cmdUpdateLoan.Parameters.Add("@DateFinishedPayment", SqlDbType.Date); cmdUpdateLoan.Parameters["@DateFinishedPayment"].Value = dateFinishedPayment; cmdUpdateLoan.Parameters.Add("@LoanID", SqlDbType.Int); cmdUpdateLoan.Parameters["@LoanID"].Value = Convert.ToInt32(loanId); cmdUpdateLoan.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 50); cmdUpdateLoan.Parameters["@TransactionID"].Value = transactionID; #endregion cmdUpdateLoan.Transaction = sqlTrans; rowsAffected = cmdUpdateLoan.ExecuteNonQuery(); if (rowsAffected != 1) { dbOperationStatus = false; } }//end of try snippet } catch (Exception ex) { MessageBox.Show(ex.Message); } #endregion end of try loanSize--; if (disContinueLoop == true && excess == 0) { break; } else if (loanSize < 0 && excess > 0) { //MessageBox.Show("Savings deposit: " + excess); dbOperationStatus = depositExcessInSavings(excess, transactionID, conn, sqlTrans); } } #endregion end of while section //Close up database connection if (dbOperationStatus == true) { sqlTrans.Commit(); txtServicingAmtPaid.Text = txtCalAmountPaid.Text; txtServicingOutstanding.Text = txtCalOutstanding.Text; MessageBox.Show("Repayment Operation is Successful", "Loan Repayment", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { sqlTrans.Rollback(); MessageBox.Show("An error has occurred!", "Loan Repayment", MessageBoxButtons.OK, MessageBoxIcon.Error); } conn.Close(); //MessageBox.Show("After Database Close, Db State: " + conn.State); #endregion clearFields(); bool isRecordFound = false; isRecordFound = MemberAllApprovedLoans(); strFilter = ""; MemberRepaymentRecords(strFilter); } }
private void txtPayAmount_Leave(object sender, EventArgs e) { if (CheckForNumber.isNumeric(txtPayAmount.Text)) { txtPayAmount.Text = CheckForNumber.formatCurrency(txtPayAmount.Text); btnPostRepayment.Enabled = true; int loanSize = loanServicing.Count - 1; int loopTimes = 0; decimal amountToPay; decimal totalRepaymentAmount; decimal amountPaidAlready; decimal outstanding; decimal newOutstanding; decimal newAmountPaid; decimal excess = 0; string servicingTransactionID = string.Empty; totalRepaymentAmount = Convert.ToDecimal(txtServicingRepaymentAmt.Text); amountPaidAlready = Convert.ToDecimal(txtServicingAmtPaid.Text); amountToPay = Convert.ToDecimal(txtPayAmount.Text); outstanding = totalRepaymentAmount - amountPaidAlready; txtExtraFeedback.Text = string.Empty; txtPaymentStatus.Text = string.Empty; txtCalAmountPaid.Text = string.Empty; while (loanSize >= 0) { loopTimes++; if (amountToPay <= outstanding) { newAmountPaid = amountToPay + amountPaidAlready; newOutstanding = totalRepaymentAmount - (newAmountPaid); if (excess == 0) { txtCalOutstanding.Text = CheckForNumber.formatCurrency(newOutstanding.ToString()); txtCalAmountPaid.Text = CheckForNumber.formatCurrency(newAmountPaid.ToString()); } txtExtraFeedback.Text += "\n\n------------------------"; txtExtraFeedback.Text += servicingTransactionID + Environment.NewLine + "Repayment: " + CheckForNumber.formatCurrency(totalRepaymentAmount.ToString()) + Environment.NewLine; txtExtraFeedback.Text += "Paying: " + CheckForNumber.formatCurrency(amountToPay.ToString()) + Environment.NewLine; txtExtraFeedback.Text += "Outstanding: " + CheckForNumber.formatCurrency(newOutstanding.ToString()) + Environment.NewLine; if (newOutstanding == 0) { txtPaymentStatus.Text = "Paid"; } else { txtPaymentStatus.Text = "Paying"; } break; } else { //newAmountPaid = amountToPay + totalRepaymentAmount; decimal noteAmountPaid; noteAmountPaid = amountToPay; excess = amountToPay - outstanding; amountToPay = excess; //MessageBox.Show("Amount Paid: " + totalRepaymentAmount.ToString()); //MessageBox.Show("Excess: " + excess); txtCalOutstanding.Text = "0.00"; txtCalAmountPaid.Text += totalRepaymentAmount + ", "; txtPaymentStatus.Text = "Paid"; txtExtraFeedback.Text += "\n\n------------------------"; txtExtraFeedback.Text += servicingTransactionID + Environment.NewLine + "Repayment: " + CheckForNumber.formatCurrency(totalRepaymentAmount.ToString()) + Environment.NewLine; txtExtraFeedback.Text += "Paying: " + CheckForNumber.formatCurrency(noteAmountPaid.ToString()) + Environment.NewLine; txtExtraFeedback.Text += "Excess: " + CheckForNumber.formatCurrency(excess.ToString()) + Environment.NewLine; } loanSize--; if (loanSize < 0) { if (excess > 0) { txtExtraFeedback.Text += "\n\n------------------------"; txtExtraFeedback.Text += "Savings Deposit: " + CheckForNumber.formatCurrency(excess.ToString()); } break; } SqlConnection conn = ConnectDB.GetConnection(); servicingTransactionID = loanServicing[loanSize]; servicingTransactionID = servicingTransactionID.Substring(servicingTransactionID.IndexOf("-") + 1).Trim(); string strQuery2 = "Select RepaymentAmount,OutstandingAmount,AmountPaid from Loans where TransactionID='" + servicingTransactionID + "'"; SqlCommand cmdQuery = new SqlCommand(strQuery2, conn); conn.Open(); SqlDataReader reader = cmdQuery.ExecuteReader(); reader.Read(); outstanding = Convert.ToDecimal(reader["OutstandingAmount"]); totalRepaymentAmount = Convert.ToDecimal(reader["RepaymentAmount"]); amountPaidAlready = Convert.ToDecimal(reader["AmountPaid"]); } } else { txtCalAmountPaid.Clear(); txtCalOutstanding.Clear(); } }
private bool MemberAllApprovedLoans() { bool isRecordFound = false; SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select l.LoansID as [ID], l.TransactionID as [Transact. ID],m.Title + ' ' + m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as FullName, " + "a.MemberID,m.photo,a.TotalRepayment as Repayment,l.AmountPaid as [Amt. Paid], l.OutstandingAmount as [Outstanding],a.ApprovalStatus,l.PaymentFinished " + "from Loans l inner join LoanApplication a on l.TransactionID=a.TransactionID " + "inner join Members m on a.MemberID=m.MemberID " + "and a.ApprovalStatus='Yes' " + "and m.FileNo='" + txtFileNo.Text + "' order by l.LoansID desc"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Loans"); DataTable dt = ds.Tables["Loans"]; if (dt.Rows.Count > 0) { datGrdLoans.DataSource = null; datGrdLoans.Columns.Clear(); datGrdLoans.Rows.Clear(); datGrdLoans.Refresh(); datGrdLoans.DataSource = dt; DataGridViewButtonColumn btn = new DataGridViewButtonColumn(); datGrdLoans.Columns.Add(btn); btn.HeaderText = "Details"; btn.Text = "View"; btn.Name = "btn"; btn.UseColumnTextForButtonValue = true; datGrdLoans.Columns["FullName"].Visible = false; datGrdLoans.Columns["MemberID"].Visible = false; datGrdLoans.Columns["Photo"].Visible = false; datGrdLoans.Columns["ApprovalStatus"].Visible = false; datGrdLoans.Columns["PaymentFinished"].Visible = false; datGrdLoans.Columns["Id"].Width = 40; datGrdLoans.Columns["Repayment"].DefaultCellStyle.Format = "N2"; datGrdLoans.Columns["Amt. Paid"].DefaultCellStyle.Format = "N2"; datGrdLoans.Columns["Outstanding"].DefaultCellStyle.Format = "N2"; datGrdLoans.Columns["Repayment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; datGrdLoans.Columns["Amt. Paid"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; datGrdLoans.Columns["Outstanding"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; //retrieve Member Photo string memberPic = string.Empty; string fullName = string.Empty; //MessageBox.Show(datGrdLoans.Rows.Count.ToString()); countLoanServicing = datGrdLoans.Rows.Count; loanServicing = new List <string>(); //clear List before beginning adding items. loanServicing.Clear(); foreach (DataRow row in dt.Rows) { memberPic = row["Photo"].ToString(); fullName = row["FullName"].ToString(); memberID = row["MemberID"].ToString(); string paymentFinished = row["PaymentFinished"].ToString(); if (paymentFinished == string.Empty || paymentFinished != "Yes") { //MessageBox.Show(row["PaymentFinished"].ToString()); loanServicing.Add(row["Id"].ToString() + " - " + row["Transact. ID"].ToString()); } } if (memberPic != string.Empty) { picMember.Image = Image.FromFile(paths + "//photos//" + memberPic); } MemberProfileInfo.Text = fullName + "\n" + txtFileNo.Text.ToUpper(); MemberProfileInfo.Visible = true; //check if member has any loan to service if (loanServicing.Count == 0) { MessageBox.Show("Currently no loan to service", "Loan Repayment", MessageBoxButtons.OK, MessageBoxIcon.Information); grpPostRepayment.Enabled = false; gBoxServicingLoan.Visible = false; } else { //Read Out List of Loans Member is to Service //MessageBox.Show(countLoanServicing.ToString()); grpPostRepayment.Enabled = true; gBoxServicingLoan.Visible = true; currentServicingLoan = loanServicing.Count - 1; txtServicingLoan.Text = loanServicing[currentServicingLoan]; //MessageBox.Show(loanServicing[currentServicingLoan].ToString()); string servicingTransactionID = loanServicing[currentServicingLoan]; servicingTransactionID = servicingTransactionID.Substring(servicingTransactionID.IndexOf("-") + 1).Trim(); //MessageBox.Show(servicingTransactionID.ToString()); string strResult = "Select RepaymentAmount, AmountPaid, OutstandingAmount from Loans where TransactionID='" + servicingTransactionID + "'"; SqlCommand cmdResult = new SqlCommand(strResult, conn); SqlDataReader reader = cmdResult.ExecuteReader(); if (reader.HasRows) { if (reader.Read()) { txtServicingRepaymentAmt.Text = CheckForNumber.formatCurrency(reader["RepaymentAmount"].ToString()); txtServicingAmtPaid.Text = CheckForNumber.formatCurrency(reader["AmountPaid"].ToString()); txtServicingOutstanding.Text = CheckForNumber.formatCurrency(reader["OutstandingAmount"].ToString()); } } } //Record true if the searched Member exist isRecordFound = true; } else { MessageBox.Show("Sorry, there is either no member with that File No. or\nThe member does not have Loan Information at the moment.", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error); //Record false if Member record does not exist isRecordFound = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(isRecordFound); }
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 }
private void loadWithdrawals(string fileNo_Surname, string param_savingsType) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = null; if (fileNo_Surname == string.Empty && param_savingsType == string.Empty) { strQuery = "Select sw.SavingsWithdrawalID 'ID',sw.SavingsID, m.FileNo 'File No.', m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as 'Full Name', " + "st.SavingsName 'Account Type', sw.Amount, sw.WithdrawAmount 'Withdrawal Amt.', sw.Balance, s.TransactionID 'Transaction ID', s.Date " + "from SavingsWithdrawal sw left join Savings s on s.SavingsID=sw.SavingsID " + "left join Members m on m.MemberID=s.MemberID " + "left join TempSavingsAcctType st on st.SavingsTypeID =sw.SavingsTypeID " + "order by sw.SavingsWithdrawalID desc"; } else if (fileNo_Surname != string.Empty && param_savingsType == string.Empty) { strQuery = "Select sw.SavingsWithdrawalID 'ID',sw.SavingsID, m.FileNo 'File No.', m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as 'Full Name', " + "st.SavingsName 'Account Type', sw.Amount, sw.WithdrawAmount 'Withdrawal Amt.', sw.Balance, s.TransactionID 'Transaction ID', s.Date " + "from SavingsWithdrawal sw left join Savings s on s.SavingsID=sw.SavingsID " + "left join Members m on m.MemberID=s.MemberID " + "left join TempSavingsAcctType st on st.SavingsTypeID =sw.SavingsTypeID " + "where m.FileNo LIKE '%" + fileNo_Surname + "%' OR m.LastName LIKE '%" + fileNo_Surname + "%' " + "order by sw.SavingsWithdrawalID desc"; } else if (fileNo_Surname == string.Empty && param_savingsType != string.Empty) { strQuery = "Select sw.SavingsWithdrawalID 'ID',sw.SavingsID, m.FileNo 'File No.', m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as 'Full Name', " + "st.SavingsName 'Account Type', sw.Amount, sw.WithdrawAmount 'Withdrawal Amt.', sw.Balance, s.TransactionID 'Transaction ID', s.Date " + "from SavingsWithdrawal sw left join Savings s on s.SavingsID=sw.SavingsID " + "left join Members m on m.MemberID=s.MemberID " + "left join TempSavingsAcctType st on st.SavingsTypeID =sw.SavingsTypeID " + "where sw.SavingsTypeID=" + param_savingsType + " " + "order by sw.SavingsWithdrawalID desc"; } else if (fileNo_Surname != string.Empty && param_savingsType != string.Empty) { strQuery = "Select sw.SavingsWithdrawalID 'ID',sw.SavingsID, m.FileNo 'File No.', m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as 'Full Name', " + "st.SavingsName 'Account Type', sw.Amount, sw.WithdrawAmount 'Withdrawal Amt.', sw.Balance, s.TransactionID 'Transaction ID', s.Date " + "from SavingsWithdrawal sw left join Savings s on s.SavingsID=sw.SavingsID " + "left join Members m on m.MemberID=s.MemberID " + "left join TempSavingsAcctType st on st.SavingsTypeID =sw.SavingsTypeID " + "where sw.SavingsTypeID=" + param_savingsType + " " + "And m.FileNo LIKE '%" + fileNo_Surname + "%' OR m.LastName LIKE '%" + fileNo_Surname + "%' " + "order by sw.SavingsWithdrawalID desc"; } //MessageBox.Show(strQuery); SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "SavingsWithdrawal"); DataTable dt = ds.Tables["SavingsWithdrawal"]; dtGrdVwWithdrawal.DataSource = dt; dtGrdVwWithdrawal.Columns["ID"].Width = 60; dtGrdVwWithdrawal.Columns["Full Name"].Width = 200; dtGrdVwWithdrawal.Columns["Account Type"].Width = 120; dtGrdVwWithdrawal.Columns["Transaction ID"].Width = 120; dtGrdVwWithdrawal.Columns["Withdrawal Amt."].Width = 120; dtGrdVwWithdrawal.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdVwWithdrawal.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdVwWithdrawal.Columns["Withdrawal Amt."].DefaultCellStyle.Format = "N2"; dtGrdVwWithdrawal.Columns["Withdrawal Amt."].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdVwWithdrawal.Columns["Balance"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdVwWithdrawal.Columns["Balance"].DefaultCellStyle.Format = "N2"; dtGrdVwWithdrawal.Columns["SavingsID"].Visible = false; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getSavingDetailsInfo(string tableName, string selectedTransactionID) { string strQuery = string.Empty; //MessageBox.Show("TableName: " + tableName + "\nTransactionID: " + selectedTransactionID); switch (tableName) { case "Deductions": strQuery = "Select d.DeductionID 'Deduction ID',m.Title + ' ' + m.LastName + ' ' + m.FirstName + ' ' + m.MiddleName as 'Full Name', " + "mon.Month,d.Year,d.Savings,d.Loans,d.Total,d.TransactionID,d.DatePosted as 'Date Posted' " + "from Deductions d left join Members m on d.MemberID = m.MemberID " + "left join MonthByName mon on mon.MonthID=d.Month " + "where d.TransactionID='" + selectedTransactionID + "'"; //MessageBox.Show(strQuery); break; case "SavingsForward": BuildTempSavingsAcctType.Create(); strQuery = "Select SavingsForwardID 'SF ID',mon.Month,sf.Year,t.SavingsName 'Saving Type',sf.Amount,sf.Comment, " + "sf.TransactionID,sf.DatePosted from SavingsForward sf left join MonthByName mon on sf.Month=mon.MonthID " + "left join TempSavingsAcctType t on sf.SavingsTypeID=t.SavingsTypeID " + "where sf.SavingsID=" + selectedSavingsID; break; case "Contributions": BuildTempSavingsAcctType.Create(); strQuery = "Select c.ContributionID 'Contribution ID',p.PaymentMode 'Payment Mode',c.OtherPayment 'Other Payment',b.BankName 'Bank Name',t.SavingsName 'Savings Type',c.TellerNo 'Teller No',c.ReceiptNo 'ReceiptNo', " + "c.Comment,c.TransactionID,c.Date from Contributions c " + "left join PaymentMode p on c.PaymentModeID=p.PaymentModeID " + "left join Banks b on b.BankID=c.BankID " + "left join TempSavingsAcctType t on c.SavingsAcctID=t.SavingsTypeID " + "where c.SavingsID=" + selectedSavingsID; break; case "Loans": strQuery = "Select l.LoansID 'Loan ID',l.LoanApplicationID 'App. ID', m.Title + ' ' + m.LastName + ' ' + m.FirstName + ' ' + m.MiddleName as 'Full Name', " + "a.LoanAmount 'Loan Amount',a.InterestRate 'Interest Rate',a.interestAmount 'Interest Amt.',a.MonthlyRepayment 'Monthly Repayment', " + "l.RepaymentAmount 'Total Repayment',l.AmountPaid 'Amt. Paid', l.OutstandingAmount 'Outstanding Amt.',l.PaymentStatus 'Payment Status', " + "mon.Month 'App. Month',a.AppYear 'App. Year',mth.Month 'Start Repayment Mth.',a.StartRepaymentYear 'Start Repayment Yr.', lc.Name 'Loan Category',lt.Type 'Loan Type', l.DateFinishedPayment 'Finished Repayment', l.TransactionID,l.DateCreated 'Date Created'" + "from Loans l inner join LoanApplication a on a.LoanApplicationID=l.LoanApplicationID " + "left join Members m on m.MemberID=a.MemberID " + "left join LoanCategory lc on a.LoanCategoryID=lc.LoanCategoryID " + "left join LoanType lt on a.LoanTypeID=lt.LoanTypeID " + "left join MonthByName mon on mon.MonthID=a.AppMonth " + "left join MonthByName mth on mth.MonthID=a.StartRepaymentMonth " + "where l.TransactionID='" + selectedTransactionID + "'"; break; case "SavingsWithdrawal": strQuery = "Select w.SavingsWithdrawalID 'ID',w.SavingsID 'Savings ID', st.SavingsName 'Savings Type', w.Amount,w.WithdrawAmount 'Withdrawal',w.Balance,w.Date from SavingsWithdrawal w " + "inner join SavingsType st on w.SavingsTypeID=st.SavingsTypeID " + "where w.SavingsID = " + selectedSavingsID + " order by w.SavingsWithdrawalID desc"; break; } SqlConnection conn = ConnectDB.GetConnection(); SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "SavingDetails"); DataTable dt = ds.Tables["SavingDetails"]; dtGrdSavingDetails.DataSource = dt; if (tableName == "Deductions") { dtGrdSavingDetails.Columns["Savings"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Loans"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Total"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Full Name"].Width = 250; } else if (tableName == "SavingsForward") { dtGrdSavingDetails.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } else if (tableName == "Loans") { dtGrdSavingDetails.Columns["Loan Amount"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Loan Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Interest Rate"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Interest Rate"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Interest Amt."].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Interest Amt."].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Monthly Repayment"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Monthly Repayment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Total Repayment"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Total Repayment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Amt. Paid"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Amt. Paid"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Outstanding Amt."].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Outstanding Amt."].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Payment Status"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Payment Status"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } else if (tableName == "SavingsWithdrawal") { dtGrdSavingDetails.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Withdrawal"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Withdrawal"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdSavingDetails.Columns["Balance"].DefaultCellStyle.Format = "N2"; dtGrdSavingDetails.Columns["Balance"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void datGrdVwLoansForward_CellClick(object sender, DataGridViewCellEventArgs e) { SqlConnection conn = ConnectDB.GetConnection(); if (e.ColumnIndex == 0) { loanApplicationID = datGrdVwLoansForward.Rows[e.RowIndex].Cells[2].Value.ToString(); loanID = datGrdVwLoansForward.Rows[e.RowIndex].Cells[1].Value.ToString(); btnEnableEdit.Enabled = shouldEnableEdit(); string strQuery = "Select a.AppMonth,a.AppYear,a.StartRepaymentMonth,a.StartRepaymentYear,a.LoanCategoryID," + "a.LoanTypeID,a.LoanAmount,a.InterestAmount,a.TotalRepayment,l.AmountPaid,a.MonthlyRepayment,a.LoanDuration, " + "a.InterestRate,l.OutstandingAmount,l.PaymentStatus,l.PaymentFinished,l.DateFinishedPayment,l.Remark from LoanApplication a " + "inner join Loans l on a.LoanApplicationID=l.LoanApplicationID " + "where a.LoanApplicationID='" + loanApplicationID + "'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { txtAmount.Text = CheckForNumber.formatCurrency2(reader["LoanAmount"].ToString()); txtInterest.Text = CheckForNumber.formatCurrency2(reader["InterestAmount"].ToString()); txtTotalRepayment.Text = CheckForNumber.formatCurrency2(reader["TotalRepayment"].ToString()); txtMonthlyRepayment.Text = CheckForNumber.formatCurrency2(reader["MonthlyRepayment"].ToString()); lblDuration.Text = CheckForNumber.formatCurrency2(reader["LoanDuration"].ToString()); lblInterestRate.Text = CheckForNumber.formatCurrency2(reader["InterestRate"].ToString()); txtAmountPaid.Text = CheckForNumber.formatCurrency2(reader["AmountPaid"].ToString()); txtOutstandingAmt.Text = CheckForNumber.formatCurrency2(reader["OutstandingAmount"].ToString()); txtRemark.Text = reader["Remark"].ToString(); cboLoanCategory.SelectedValue = reader["LoanCategoryID"].ToString(); loadLoanType(); cboLoanType.SelectedValue = reader["LoanTypeID"].ToString(); DateTime loanApplicationDate = new DateTime(Convert.ToInt16(reader["AppYear"].ToString()), Convert.ToInt16(reader["AppMonth"].ToString()), 1); dtAppDate.Value = loanApplicationDate; DateTime startRepayment = new DateTime(Convert.ToInt16(reader["StartRepaymentYear"].ToString()), Convert.ToInt16(reader["StartRepaymentMonth"].ToString()), 1); dtStartRepayment.Value = startRepayment; if (reader["PaymentFinished"].ToString() == "Yes") { dateFinishedPayment = reader["DateFinishedPayment"].ToString(); dtPickerPaymentFinished.Visible = true; DateTime theFinishedPaymentDate = DateTime.Parse(dateFinishedPayment); dtPickerPaymentFinished.Value = theFinishedPaymentDate; } else { dateFinishedPayment = string.Empty; } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } } }
private SqlCommand selectQueryMethod(string searchCriteria, string sender) { //MessageBox.Show(searchCriteria.ToString()); string searchFilter = null; switch (sender) { case "FileNoAndName": searchFilter = "m.FileNo LIKE '%" + searchCriteria + "%' OR " + "m.LastName LIKE '%" + searchCriteria + "%' OR " + "m.MiddleName LIKE '%" + searchCriteria + "%' OR " + "m.FirstName LIKE '%" + searchCriteria + "%'"; break; case "Month": searchFilter = "s.Month='" + searchCriteria + "'"; break; case "Year": searchFilter = "s.Year='" + searchCriteria + "'"; break; case "PaymentMode": searchFilter = "c.PaymentModeID=" + searchCriteria; break; case "OtherPaymentMode": searchFilter = "c.OtherPayment LIKE '%" + searchCriteria + "%'"; break; case "Banks": searchFilter = "c.BankID='" + searchCriteria + "'"; break; case "TellerNo": searchFilter = "c.TellerNo LIKE '%" + searchCriteria + "%'"; break; case "DateInterval": searchFilter = searchCriteria; break; case "Filter": searchFilter = searchCriteria; break; } //Configure search string for Total of DataSet totalContributionOfDataSet = "Select SUM(s.Amount) as TotalAmount " + "from Savings s left join Contributions c on s.SavingsID=c.SavingsID " + "left join Members m on s.MemberID = m.MemberID " + "left join MonthByName d on s.Month=d.MonthID " + "left join PaymentMode p on c.PaymentModeID=p.PaymentModeID " + "left join Banks b on c.BankID=b.BankID where s.SavingSource='Contribution' "; totalContributionOfDataSet = totalContributionOfDataSet + " AND " + searchFilter; //MessageBox.Show(totalContributionOfDataSet.ToString()); conn = ConnectDB.GetConnection(); string strQuery = "Select m.FileNo, m.Title + ' ' + m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as [Full Name], " + "mst.Remark [Account],s.Amount, d.Month, s.Year, p.PaymentMode [Payment Mode], c.OtherPayment [Other Payment], b.BankName [Bank], c.TellerNo [Teller No.], c.Comment, c.TransactionID, s.Date " + "from Savings s left join Contributions c on s.SavingsID=c.SavingsID " + "left join Members m on s.MemberID = m.MemberID " + "left join MonthByName d on s.Month=d.MonthID " + "left join PaymentMode p on c.PaymentModeID=p.PaymentModeID " + "left join Banks b on c.BankID=b.BankID " + "left join MemberSavingsTypeAcct mst on c.SavingsAcctID=mst.SavingsTypeID and m.MemberID=mst.MemberID " + "where s.SavingSource='Contribution' and " + searchFilter + " order by s.SavingsID desc"; cmd = new SqlCommand(strQuery, conn); return(cmd); }
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(); } }
private void UpdateSavings() { int rowAffected = 0; bool errorflag = false; int savingsTypeID = 0; int savingsForwardID = 0; string temp = string.Empty; decimal amount = 0; string comment = string.Empty; string strQuery = string.Empty; SqlConnection conn = ConnectDB.GetConnection(); strQuery = "Update Savings set Amount=@Amount where TransactionID=@TransactionID"; conn.Open(); SqlTransaction sqlTrans = conn.BeginTransaction(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; cmd.Parameters.Add("@Amount", SqlDbType.Decimal); cmd.Parameters["@Amount"].Value = Convert.ToDecimal(lblSavingsTotal.Text); cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 100); cmd.Parameters["@TransactionID"].Value = lblSavingsID.Text; cmd.Transaction = sqlTrans; try { rowAffected = cmd.ExecuteNonQuery(); if (rowAffected > 0) { for (int i = 0; i < lstVwSavingDetails.Items.Count; i++) { strQuery = "Update SavingsForward set SavingsTypeID=@SavingsTypeID,Amount=@Amount,Comment=@Comment where SavingsForwardID=@SavingsForwardID" + " and TransactionID=@TransactionID"; cmd.CommandText = strQuery; #region cmd parameters savingsForwardID = Convert.ToInt16(lstVwSavingDetails.Items[i].SubItems[0].Text.ToString()); savingsTypeID = Convert.ToInt16(lstVwSavingDetails.Items[i].SubItems[1].Text.ToString()); temp = lstVwSavingDetails.Items[i].SubItems[3].Text.ToString(); amount = Convert.ToDecimal(temp); comment = lstVwSavingDetails.Items[i].SubItems[4].Text.ToString(); cmd.Parameters.Clear(); //MessageBox.Show("Savings TypeID - " + savingsTypeID.ToString() + " - " + amount.ToString()); cmd.Parameters.Add("@SavingsTypeID", SqlDbType.Int); cmd.Parameters["@SavingsTypeID"].Value = savingsTypeID; cmd.Parameters.Add("@Amount", SqlDbType.Decimal); cmd.Parameters["@Amount"].Value = amount; cmd.Parameters.Add("@Comment", SqlDbType.NVarChar, 400); cmd.Parameters["@Comment"].Value = comment; cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 400); cmd.Parameters["@TransactionID"].Value = lblSavingsID.Text; cmd.Parameters.Add("@SavingsForwardID", SqlDbType.Int); cmd.Parameters["@SavingsForwardID"].Value = savingsForwardID; #endregion rowAffected = cmd.ExecuteNonQuery(); if (rowAffected == 0) { errorflag = true; } } } else { errorflag = true; } //check if there was no error and commit, if there is error rollback if (errorflag == false) { sqlTrans.Commit(); ActivityLog.logActivity(userId, "SavingsForward", "Update Savings Record " + lblSavingsID.Text); MessageBox.Show("Savings has been successfully Updated", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { sqlTrans.Rollback(); MessageBox.Show("An error occurred! Update has been cancelled.", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } //reset view loadDataSetSavingsForward(); lstVwSavingDetails.Items.Clear(); cboSavingsType.SelectedIndex = 0; txtAmount.Text = string.Empty; txtComment.Text = string.Empty; lblSavingsTotal.Text = "0.00"; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void dtGrdVwSavings_CellContentClick(object sender, DataGridViewCellEventArgs e) { string savingsAcct; decimal savingsTotal = 0; //MessageBox.Show(e.ColumnIndex.ToString()); #region View Record if (e.ColumnIndex == 5) { string transactionID = dtGrdVwSavings.Rows[e.RowIndex].Cells[3].Value.ToString(); grpBoxDetails.Text = "Savings Details [" + transactionID + "]"; SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SavingsForwardID, SavingsTypeID, Amount, Comment from SavingsForward where TransactionID='" + transactionID + "'"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; lstVwSavingDetails.Items.Clear(); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { if ((reader["SavingsTypeID"].ToString()) == "99") { savingsAcct = "Shares Savings"; } else { savingsAcct = getSavingsAcctName(reader["SavingsTypeID"].ToString()); } string[] row = { reader["SavingsForwardID"].ToString(), reader["SavingsTypeID"].ToString(), savingsAcct, CheckForNumber.formatCurrency2(reader["Amount"].ToString()), reader["Comment"].ToString() }; ListViewItem item = new ListViewItem(row); lstVwSavingDetails.Items.Add(item); savingsTotal = savingsTotal + Convert.ToDecimal(reader["Amount"].ToString()); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } } #endregion end View Record #region Delete Record if (e.ColumnIndex == 6) { DialogResult res = MessageBox.Show("Do you wish to Delete the Selected Record?", "Savings", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (res == DialogResult.Yes) { SqlConnection conn = ConnectDB.GetConnection(); string transactionID = dtGrdVwSavings.Rows[e.RowIndex].Cells[3].Value.ToString(); string strQuery = "Delete from Savings where transactionID='" + transactionID + "'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try{ conn.Open(); int rowAffected = cmd.ExecuteNonQuery(); if (rowAffected > 0) { MessageBox.Show("Record has been deleted", "Savings", MessageBoxButtons.OK); loadDataSetSavingsForward(); //clear data items in listView lstVwSavingDetails.Items.Clear(); } else { MessageBox.Show("An error occurred. Record not deletee", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } }catch (Exception ex) { MessageBox.Show(ex.Message); }finally{ conn.Close(); } } } #endregion end Delete Record }
private void ExecuteSave() { SqlConnection conn = null; SqlCommand cmd = null; bool errorFlag = false; int rowAffected; string strQuery; if ((lstVwSavings.Items.Count != 0) && (totalAmount > 0) && (cboMonth.Text != string.Empty) && (cboYear.Text != string.Empty)) { conn = ConnectDB.GetConnection(); conn.Open(); SqlTransaction sqlTrans = conn.BeginTransaction(); cmd = conn.CreateCommand(); cmd.Transaction = sqlTrans; string transactionID = "SAV" + DateTime.Now.ToString("ddMMyyhhmmss"); strQuery = "Insert into Savings(MemberID,SavingSource,Amount,Month,Year,TransactionID)" + "values(@MemberID,@SavingSource,@Amount,@Month,@Year,@TransactionID)"; #region cmd parameters cmd.Parameters.Add("@MemberID", SqlDbType.Int); cmd.Parameters["@MemberID"].Value = memberID; cmd.Parameters.Add("@SavingSource", SqlDbType.NVarChar, 40); cmd.Parameters["@SavingSource"].Value = "SavingsForward"; cmd.Parameters.Add("@Amount", SqlDbType.Decimal); cmd.Parameters["@Amount"].Value = totalAmount; cmd.Parameters.Add("@Month", SqlDbType.Int); cmd.Parameters["@Month"].Value = Convert.ToInt16(cboMonth.SelectedIndex); cmd.Parameters.Add("@Year", SqlDbType.Int); cmd.Parameters["@Year"].Value = Convert.ToInt16(cboYear.Text); cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar, 50); cmd.Parameters["@TransactionID"].Value = transactionID; #endregion cmd.CommandText = strQuery; try { rowAffected = cmd.ExecuteNonQuery(); #region if Statement of rowAffected if (rowAffected > 0) { //Get SavingsID from the savings Table strQuery = "Select SavingsID from Savings where TransactionID='" + transactionID + "'"; cmd.CommandText = strQuery; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); string SavingsID = reader["SavingsID"].ToString(); reader.Close(); cmd.Parameters.Add("@SavingsID", SqlDbType.Int); cmd.Parameters.Add("@SavingsTypeID", SqlDbType.Int); cmd.Parameters.Add("@Comment", SqlDbType.NVarChar, 400); for (int i = 0; i < lstVwSavings.Items.Count; i++) { strQuery = "Insert into SavingsForward(SavingsID,Month,Year,SavingsTypeID,Amount,Comment,TransactionID)" + "values(@SavingsID,@Month,@Year,@SavingsTypeID,@Amount,@Comment,@TransactionID)"; #region cmd parameters cmd.Parameters["@SavingsID"].Value = SavingsID; cmd.Parameters["@Month"].Value = Convert.ToInt16(cboMonth.SelectedIndex); cmd.Parameters["@Year"].Value = Convert.ToInt16(cboYear.Text); cmd.Parameters["@SavingsTypeID"].Value = Convert.ToInt16(lstVwSavings.Items[i].SubItems[0].Text.ToString()); cmd.Parameters["@Amount"].Value = Convert.ToDecimal(lstVwSavings.Items[i].SubItems[2].Text.ToString()); cmd.Parameters["@Comment"].Value = txtComment.Text.Trim(); cmd.Parameters["@TransactionID"].Value = transactionID; #endregion cmd.CommandText = strQuery; rowAffected = cmd.ExecuteNonQuery(); if (rowAffected == 0) { MessageBox.Show("An error has occurred", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); errorFlag = true; break; } } } else { errorFlag = true; } #endregion end of if statement //Check if Transaction has been successful without error and commit or rollback if (errorFlag == false) { sqlTrans.Commit(); ActivityLog.logActivity(userId, "SavingsForward", "Added a new Savings record with TransactionID of " + transactionID); MessageBox.Show("Savings has been successfully posted", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Information); loadDataSetSavingsForward(); loadDataSetMemberSavings(); } else { sqlTrans.Rollback(); MessageBox.Show("An error has occurred posting savings", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } clearFields(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } } else { MessageBox.Show("Month, Year and Amount are required", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void loadDataSetSavingsForward() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select sf.SavingsID, m.Month, sf.Year, sf.Amount, sf.TransactionID, sf.Date from Savings sf " + "left join MonthByName m on sf.Month=m.MonthID " + "where sf.MemberID=" + memberID + " and sf.SavingSource='SavingsForward' order by sf.savingsID desc"; string strQuery2 = "Select Sum(Amount) as TotalSavingsForward from Savings where MemberID=" + memberID + " and SavingSource='SavingsForward'"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); dtGrdVwSavingsForward.DataSource = null; dtGrdVwSavingsForward.Columns.Clear(); dtGrdVwSavingsForward.Rows.Clear(); dtGrdVwSavingsForward.Refresh(); try { conn.Open(); da.Fill(ds, "Savings"); DataTable dt = ds.Tables["Savings"]; dtGrdVwSavingsForward.DataSource = dt; int rowFound = dtGrdVwSavingsForward.Rows.Count; dtGrdVwSavingsForward.Columns["SavingsID"].Visible = false; dtGrdVwSavingsForward.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdVwSavingsForward.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdVwSavingsForward.Columns["Year"].Width = 60; DataGridViewButtonColumn btn = new DataGridViewButtonColumn(); dtGrdVwSavingsForward.Columns.Add(btn); btn.HeaderText = "Action"; btn.Text = "Details"; btn.Name = "btn"; btn.UseColumnTextForButtonValue = true; if (rowFound > 0) { cmd.CommandText = strQuery2; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); string totalSavingsForward = CheckForNumber.formatCurrency(reader["TotalSavingsForward"].ToString()); lblTotalSavingsForward.Text = "Total: " + totalSavingsForward; reader.Close(); } else { lblTotalSavingsForward.Text = "Total: 0.00"; } } else { lblTotalSavingsForward.Text = "Total: 0.00"; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void executeDeduction() { int numOfRecords = lstMonthlyDeductions.Items.Count; int currentRecord; SqlConnection conn = ConnectDB.GetConnection(); SqlTransaction sqlTrans = null; SqlCommand cmd; try { conn.Open(); sqlTrans = conn.BeginTransaction(); cmd = conn.CreateCommand(); cmd.Transaction = sqlTrans; int selectedMonth = Convert.ToInt16(cboMonth.SelectedValue); int selectedYear = Convert.ToInt16(cboYear.Text); #region loop through records //loop through records for (currentRecord = 0; currentRecord < numOfRecords; currentRecord++) { cmd.Parameters.Clear(); memberID = Convert.ToInt16(lstMonthlyDeductions.Items[currentRecord].SubItems[1].Text); decimal monthlyLoanRepayment = Convert.ToDecimal(lstMonthlyDeductions.Items[currentRecord].SubItems[5].Text); decimal lstAllSavingsDeduction = Convert.ToDecimal(lstMonthlyDeductions.Items[currentRecord].SubItems[4].Text); decimal lstAllLoansDeduction = Convert.ToDecimal(lstMonthlyDeductions.Items[currentRecord].SubItems[5].Text); decimal lstTotalDeductions = Convert.ToDecimal(lstMonthlyDeductions.Items[currentRecord].SubItems[6].Text); string transactionID = "DED" + DateTime.Now.ToString("ddMMyyhhmmss"); totalSavings = Convert.ToDecimal(lblTotalSavings.Text); Deduction newPosting = new Deduction(); string postSavingsStatus = newPosting.postSavings(conn, cmd, sqlTrans, memberID, transactionID, lstAllSavingsDeduction, numOfRows, errorflag, selectedMonth, selectedYear); //MessageBox.Show("Post Savings Status: " + postSavingsStatus); string postLoansStatus = newPosting.postLoans(conn, cmd, sqlTrans, memberID, transactionID, currentRecord, selectedMonth, selectedYear, numOfRows, errorflag, monthlyLoanRepayment); //MessageBox.Show("PostLoan Status: " + postLoansStatus); string recordDeductionStatus = newPosting.recordDeduction(cmd, memberID, transactionID, currentRecord, selectedMonth, selectedYear, lstAllSavingsDeduction, lstAllLoansDeduction, lstTotalDeductions, numOfRows, errorflag); //MessageBox.Show("Record Deductions status: " + recordDeductionStatus); //string recordDeductionDetailsStatus = newPosting.recordDeductionDetails(cmd, memberID, transactionID, currentRecord, numOfRows, errorflag, lstAllSavingsDeduction, lstAllLoansDeduction, repaymentLoanType, servicingLoan, loanMoreThanSavings); //MessageBox.Show("Record Deduction Detail Status: " + recordDeductionDetailsStatus); int recordDeductionDetailStatus = recordDeductionDetails(cmd, memberID, transactionID, errorflag, numOfRows); //MessageBox.Show(recordDeductionDetailStatus.ToString()); } //end of for loop #endregion end of loop if (errorflag == 0) { sqlTrans.Commit(); MessageBox.Show("Member Deduction has been successfully posted.", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Information); getMonthlyDeductionsInfo(memberID); getMemberPreviousDeductions(memberID); getPreviousDeductionDate(memberID); //Display Savings and Loan information //lstMonthlyDeductions_SelectedIndexChanged(sender, e); string selectedMemberID = lstMonthlyDeductions.Items[0].SubItems[1].Text; getSavingsInfo(selectedMemberID); getLoansInfo(selectedMemberID); txtChangeSavingsAmt.Text = string.Empty; txtChangeLoanAmount.Text = string.Empty; ActivityLog.logActivity(UserId, "Posting Deduction - Individual", "Deduction Posting for MemberID:" + memberID + " for Month:" + selectedMonth + " " + selectedYear); } else { sqlTrans.Rollback(); MessageBox.Show("Deduction Transaction failed and is aborted!", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }