private void DeleteWithdrawal_Load(object sender, EventArgs e) { fileNo_Surname = string.Empty; param_savingsType = string.Empty; loadWithdrawals(fileNo_Surname, param_savingsType); BuildTempSavingsAcctType.Create(); loadSavingsType(); }
private void btnFindMember_Click(object sender, EventArgs e) { //Build Savings Table with Personal Savings Account Type and other Savings Account Type using the BuildTempSavingsAcctType Class BuildTempSavingsAcctType.Create(); SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select MemberID, FileNo, LastName + ' ' + FirstName + ' ' + MiddleName as FullName, Photo from Members " + "where FileNo='" + txtFileNo.Text.Trim() + "'"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); memberID = Convert.ToInt16(reader["MemberID"].ToString()); lblMemberProfile.Text = reader["FullName"].ToString() + "\n" + reader["FileNo"].ToString(); //display member photo string paths = PhotoPath.getPath(); //MessageBox.Show(reader["Photo"].ToString()); if (reader["Photo"].ToString() != string.Empty) { picMember.Image = Image.FromFile(paths + "\\photos\\" + reader["Photo"].ToString()); } else { picMember.Image = Image.FromFile(paths + "\\photos\\profile_img.png"); } picMember.Visible = true; lblMemberProfile.Visible = true; grpBoxSavingsInfo.Enabled = true; loadDataSetSavingsForward(); loadDataSetMemberSavings(); } else { MessageBox.Show("Sorry, there is no Member with that File No.", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getPreviousWithdrawals() { BuildTempSavingsAcctType.Create(); SqlConnection conn = ConnectDB.GetConnection(); string 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 " + "left join TempSavingsAcctType st on w.SavingsTypeID=st.SavingsTypeID order by w.SavingsWithdrawalID desc"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); datGrdVwSavings.DataSource = null; try { conn.Open(); da.Fill(ds, "Withdrawal"); DataTable dt = ds.Tables["Withdrawal"]; datGrdVwSavings.DataSource = dt; datGrdVwSavings.Columns["ID"].Width = 80; datGrdVwSavings.Columns["Amount"].DefaultCellStyle.Format = "N2"; datGrdVwSavings.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; datGrdVwSavings.Columns["Withdrawal"].DefaultCellStyle.Format = "N2"; datGrdVwSavings.Columns["Withdrawal"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; datGrdVwSavings.Columns["Balance"].DefaultCellStyle.Format = "N2"; datGrdVwSavings.Columns["Balance"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void contributions(string memberID) { BuildTempSavingsAcctType.Create(); SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select c.ContributionID 'ID',p.PaymentMode,c.OtherPayment,b.BankName,s.Amount,s.SavingSource 'Saving Source',mnth.Month,s.Year,c.TellerNo 'Teller No.',c.ReceiptNo 'Receipt No.',c.Comment, " + "t.SavingsName 'Savings Type',s.TransactionID,s.Date from Contributions c " + "left join Savings s on c.SavingsID=s.SavingsID " + "left join Members m on s.MemberID = m.MemberID " + "left join PaymentMode p on p.PaymentModeID=c.PaymentModeID " + "left join Banks b on b.BankID=c.BankID " + "left join MonthByName mnth on mnth.MonthID=s.Month " + "left join TempSavingsAcctType t on c.SavingsAcctID=t.SavingsTypeID " + "where s.MemberID='" + memberID + "' order by c.ContributionID desc"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Contributions"); DataTable dt = ds.Tables["Contributions"]; dtGrdVwContributions.DataSource = dt; dtGrdVwContributions.Columns["ID"].Width = 60; dtGrdVwContributions.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdVwContributions.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } 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(); } }