private void txtAmount_Leave(object sender, EventArgs e) { if (CheckForNumber.isNumeric(txtAmount.Text.Trim())) { txtAmount.Text = CheckForNumber.formatCurrency(txtAmount.Text.Trim()); } }
private void txtAmount_TextChanged(object sender, EventArgs e) { if (txtAmount.Text != string.Empty) { if (CheckForNumber.isNumeric(txtAmount.Text.Trim()) == true) { decimal interestAmount = (Convert.ToDecimal(txtAmount.Text)) * ((Convert.ToDecimal(txtLoanInterestRate.Text)) / 100); txtInterestAmount.Text = CheckForNumber.formatCurrency(interestAmount.ToString()); decimal totalRepaymentAmount = Convert.ToDecimal(txtAmount.Text) + interestAmount; txtTotalRepaymentAmount.Text = CheckForNumber.formatCurrency(totalRepaymentAmount.ToString()); decimal monthRepayment = totalRepaymentAmount / Convert.ToInt16(txtLoanDuration.Text); txtMonthRepayment.Text = CheckForNumber.formatCurrency(monthRepayment.ToString()); //enable btnSaveApplication btnSaveApplication.Enabled = true; } else { MessageBox.Show("Sorry, Invalid value has been entered for Amount", "Loan Application", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { btnSaveApplication.Enabled = false; } }
private void loadDataSetMemberSavings() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select m.Month, sf.Year, sf.Amount, sf.SavingSource, sf.TransactionID, sf.Date from Savings sf " + "left join MonthByName m on sf.Month=m.MonthID " + "where MemberID=" + memberID + " order by savingsID desc"; string strQuery2 = "Select Sum(Amount) as TotalSavings from Savings where MemberID=" + memberID; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Savings"); DataTable dt = ds.Tables["Savings"]; dtGrdVwSavings.DataSource = dt; int rowFound = dtGrdVwSavings.Rows.Count; dtGrdVwSavings.Columns["Amount"].DefaultCellStyle.Format = "N2"; dtGrdVwSavings.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dtGrdVwSavings.Columns["Year"].Width = 60; dtGrdVwSavings.Columns["Month"].Width = 80; if (rowFound > 0) { cmd.CommandText = strQuery2; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); string totalSavings = CheckForNumber.formatCurrency(reader["TotalSavings"].ToString()); lblMemberTotalSavings.Text = "Total: " + totalSavings; reader.Close(); } else { lblMemberTotalSavings.Text = "Total: 0.00"; } } else { lblMemberTotalSavings.Text = "Total: 0.00"; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void txtSavingsAmount_Leave(object sender, EventArgs e) { bool isNumeric = CheckForNumber.isNumeric(txtSavingsAmount.Text.Trim()); if (isNumeric) { txtSavingsAmount.Text = CheckForNumber.formatCurrency(txtSavingsAmount.Text.Trim()); } else { MessageBox.Show("The amount is invalid. Please enter numeric values.", "SetUp Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void getSelectedLoanDetail(string transactionID) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select l.LoansID as [Id], l.TransactionID as [Transact. ID], " + "a.TotalRepayment, l.AmountPaid, l.OutstandingAmount, a.ApprovalStatus, " + "a.StartRepaymentMonth, a.StartRepaymentYear, t.Type, t.Duration, t.InterestRate, a.LoanAmount, a.InterestAmount, a.MonthlyRepayment " + "from Loans l inner join LoanApplication a on l.TransactionID=a.TransactionID " + "left join LoanType t on a.LoanTypeID=t.LoanTypeID " + "where a.ApprovalStatus='Yes' " + "and a.TransactionID='" + transactionID + "' order by l.LoansID desc"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { if (reader.Read()) { //MessageBox.Show(reader["StartRepaymentMonth"].ToString()); txtStartRepayment.Text = DateFunction.getMonthByName(Convert.ToInt32(reader["StartRepaymentMonth"])) + " " + reader["StartRepaymentYear"].ToString(); txtLoanType.Text = reader["Type"].ToString(); txtDuration.Text = reader["Duration"].ToString(); txtInterestRate.Text = reader["InterestRate"].ToString(); txtLoanAmount.Text = CheckForNumber.formatCurrency(reader["LoanAmount"].ToString()); txtRepaymentAmount.Text = CheckForNumber.formatCurrency(reader["TotalRepayment"].ToString()); txtInterestAmount.Text = CheckForNumber.formatCurrency(reader["InterestAmount"].ToString()); txtMonthlyRepayment.Text = CheckForNumber.formatCurrency(reader["MonthlyRepayment"].ToString()); txtAmountPaid.Text = CheckForNumber.formatCurrency(reader["AmountPaid"].ToString()); txtOutstanding.Text = CheckForNumber.formatCurrency(reader["OutstandingAmount"].ToString()); //alignment of controls txtLoanAmount.TextAlign = HorizontalAlignment.Right; txtRepaymentAmount.TextAlign = HorizontalAlignment.Right; txtInterestAmount.TextAlign = HorizontalAlignment.Right; txtMonthlyRepayment.TextAlign = HorizontalAlignment.Right; txtAmountPaid.TextAlign = HorizontalAlignment.Right; txtOutstanding.TextAlign = HorizontalAlignment.Right; } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void txtEntryFee_Leave(object sender, EventArgs e) { string strValue = txtEntryFee.Text.Trim(); if (CheckForNumber.isNumeric(strValue)) { txtEntryFee.Text = CheckForNumber.formatCurrency(txtEntryFee.Text); } else { MessageBox.Show("Value entered for Entrance Fee is Invalid", "Invalid Data", MessageBoxButtons.OK, MessageBoxIcon.Error); txtEntryFee.Clear(); } }
private void txtAmount_Leave(object sender, EventArgs e) { if (CheckForNumber.isNumeric(txtAmount.Text.Trim())) { decimal amount = Convert.ToDecimal(txtAmount.Text); if (amount > 0) { txtAmount.Text = CheckForNumber.formatCurrency(txtAmount.Text); } else { MessageBox.Show("Amount entered is Invalid.\nAmount should not be zero or less than.", "Make Contributions", MessageBoxButtons.OK, MessageBoxIcon.Error); txtAmount.Text = string.Empty; } } else { MessageBox.Show("Amount entered is Invalid.\nAmount should be numeric.", "Make Contributions", MessageBoxButtons.OK, MessageBoxIcon.Error); txtAmount.Text = string.Empty; } }
private void getLoanApplicationDetails(string transactionID, string loanApplicationID) { txtApplicationID.Text = loanApplicationID; SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select mn.Month as [AppMonth],a.AppYear as [AppYear],m.FileNo,m.LastName as [LastName],m.FirstName as [FirstName],m.MiddleName as [MiddleName], m.Photo," + "c.Name as [LoanCategory],t.Type as [LoanType], a.LoanAmount as [Amount],a.TransactionID as [TransactionID],a.DatePosted as [Date], " + "a.StartRepaymentMonth, a.StartRepaymentYear,a.SuretyMemberID1,a.SuretyMemberID2," + "a.WitnessMemberID, a.NonMemberSurety1, a.NonMemberSurety2, a.NonMemberWitness, " + "a.LoanDuration, a.InterestRate, a.InterestAmount, a.TotalRepayment, a.MonthlyRepayment, a.ApprovalStatus from LoanApplication a " + "left join Members m on a.MemberID=m.MemberID " + "left join MonthByName mn on a.AppMonth = mn.MonthID " + "left join LoanCategory c on a.LoanCategoryID = c.LoanCategoryID " + "left join LoanType t on a.LoanTypeID=t.LoanTypeID " + "where TransactionID='" + transactionID + "' order by LoanApplicationID desc"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); txtApplicationDate.Text = reader["AppMonth"].ToString() + " " + reader["AppYear"].ToString(); txtCategory.Text = reader["LoanCategory"].ToString(); txtType.Text = reader["LoanType"].ToString(); txtAmount.Text = CheckForNumber.formatCurrency(reader["Amount"].ToString()); txtRepaymentDate.Text = DateFunction.getMonthByName(Convert.ToInt16(reader["StartRepaymentMonth"].ToString())) + " " + reader["StartRepaymentYear"].ToString(); txtTransactionID.Text = reader["TransactionID"].ToString(); txtDuration.Text = reader["LoanDuration"].ToString(); txtInterestRate.Text = CheckForNumber.formatCurrency(reader["InterestRate"].ToString()); txtInterestAmount.Text = CheckForNumber.formatCurrency(reader["InterestAmount"].ToString()); txtTotalRepayment.Text = CheckForNumber.formatCurrency(reader["TotalRepayment"].ToString()); txtMonthRepayment.Text = CheckForNumber.formatCurrency(reader["MonthlyRepayment"].ToString()); txtMemberProfile.Text = reader["LastName"] + " " + reader["FirstName"] + " " + reader["MiddleName"]; lblMemberFileNo.Text = reader["FileNo"].ToString(); if (reader["Photo"].ToString() == null || reader["Photo"].ToString() == string.Empty) { picMember.Image = Image.FromFile(paths + "//photos//" + reader["Photo"].ToString()); } if ((reader["SuretyMemberID1"].ToString() != string.Empty)) { getSuretyDetails(reader["SuretyMemberID1"].ToString(), "1"); } else { lblSurety1.Text = "Surety 1\n" + reader["NonMemberSurety1"].ToString(); picSurety1.Image = Image.FromFile(paths + "//photos//profile_img.png"); } if ((reader["SuretyMemberID2"].ToString() != string.Empty)) { getSuretyDetails(reader["SuretyMemberID2"].ToString(), "2"); } else { lblSurety2.Text = "Surety 2\n" + reader["NonMemberSurety2"].ToString(); picSurety2.Image = Image.FromFile(paths + "//photos//profile_img.png"); } if ((reader["WitnessMemberID"].ToString() != string.Empty)) { getSuretyDetails(reader["WitnessMemberID"].ToString(), "3"); } else { lblWitness.Text = "Witness\n" + reader["NonMemberWitness"].ToString(); picWitness.Image = Image.FromFile(paths + "//photos//profile_img.png"); } //MessageBox.Show(reader["ApprovalStatus"].ToString()); switch (reader["ApprovalStatus"].ToString().Trim()) { case "": lblApprovalStatus.Text = "Pending"; lblApprovalStatus.ForeColor = System.Drawing.Color.Black; radYes.Checked = false; radNo.Checked = false; break; case "Yes": lblApprovalStatus.Text = "Loan Approved"; lblApprovalStatus.ForeColor = System.Drawing.Color.Green; radYes.Checked = true; break; case "No": lblApprovalStatus.Text = "Loan Not Approved"; lblApprovalStatus.ForeColor = System.Drawing.Color.Red; radNo.Checked = true; break; } //check/effect whether to enable or disable approval/disapproval box DateTime today = DateTime.Now; DateTime repaymentDate = DateTime.Parse(txtRepaymentDate.Text); TimeSpan diffBeforeRepayment = today.Subtract(repaymentDate); int daysBeforeRepayment = (int)diffBeforeRepayment.TotalDays; if (daysBeforeRepayment >= 0) { grpApproval.Enabled = false; } else { grpApproval.Enabled = true; } //check the Application Details Section this.Height = 591; } catch (Exception ex) { MessageBox.Show("getLoanApplicationDetails " + ex.Message); } finally { conn.Close(); } }
private void btnAddDeduction_Click(object sender, EventArgs e) { //check if the fileno is genuine and is not already in the list //get number of all records int numOfRecords = lstDeductions.Items.Count - 1; bool alreadyInList = false; bool memberExist = false; string addFileNoDeduction; decimal monthlyLoanRepayment; decimal totalDeduction; int memberID = 0; string lastRecId; int nextRecId; SqlConnection conn = null; SqlCommand cmd = null; conn = ConnectDB.GetConnection(); cmd = conn.CreateCommand(); conn.Open(); addFileNoDeduction = txtAddFileNo.Text.Trim().ToUpper(); #region check if member is genuine and exist string strQuery = "Select MemberID,FileNo from Members where FileNo='" + addFileNoDeduction + "'"; cmd.CommandText = strQuery; try { SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); memberID = Convert.ToInt16(reader["MemberID"]); memberExist = true; reader.Close(); } else { memberExist = false; MessageBox.Show("Sorry, No member with that File No", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message); } #endregion #region Check if the FileNo is already in the List if it is a genuine member FileNo if (memberExist) { for (int i = 0; i < numOfRecords; i++) { if (addFileNoDeduction == lstDeductions.Items[i].SubItems[2].Text.ToString().ToUpper()) { alreadyInList = true; DialogResult res = MessageBox.Show("That Record is Already in the List", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Information); break; } } } #endregion #region get loan amount for the entered member if it does not exist in the list if ((memberExist == true) && (alreadyInList == false)) { //Get member's Savings using memberID strQuery = "Select m.MemberID, m.FileNo, m.Title + ' ' + m.LastName + ' ' + m.FirstName + ' ' + m.MiddleName as Name, " + "SUM(s.Amount) as Amount from Members m inner join MemberSavingsTypeAcct s on m.MemberID=s.MemberID where m.MemberID=" + memberID + " group by m.MemberID,m.FileNo,m.Title,m.FirstName,m.LastName,m.MiddleName"; cmd.CommandText = strQuery; try { SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { //get the serial id of the last record lastRecId = lstDeductions.Items[numOfRecords].SubItems[0].Text; //Add 1 to the last record Id to get the next record id nextRecId = (Int16.Parse(lastRecId)) + 1; //check for loan records monthlyLoanRepayment = getMonthlyLoan(memberID); //calculate monthly total deduction using the total savings plus monthly loan repayment totalDeduction = monthlyLoanRepayment + Convert.ToDecimal(reader["Amount"]); //add data to the lstDeductions String[] row = { nextRecId.ToString(), reader["MemberID"].ToString(), reader["FileNo"].ToString(), reader["Name"].ToString(), CheckForNumber.formatCurrency(reader["Amount"].ToString()), CheckForNumber.formatCurrency2(monthlyLoanRepayment.ToString()), CheckForNumber.formatCurrency(totalDeduction.ToString()) }; ListViewItem item = new ListViewItem(row); lstDeductions.Items.Add(item); txtAddFileNo.Text = string.Empty; btnAddDeduction.Enabled = false; } reader.Close(); lblRecordNo.Text = "No. of Records: " + lstDeductions.Items.Count.ToString(); } } catch (Exception ex) { MessageBox.Show("Last Phase: " + ex.Message); } } #endregion end of if statement }
private void PostDeductions_Load(object sender, EventArgs e) { decimal loanMonthlyRepayment; int memberID; int counter; decimal totalDeduction; SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select m.MemberID, m.FileNo, m.Title + ' ' + m.LastName + ' ' + m.FirstName + ' ' + m.MiddleName as Name, " + "SUM(s.Amount) as Amount from Members m inner join MemberSavingsTypeAcct s on m.MemberID=s.MemberID group by m.MemberID,m.FileNo,m.Title,m.FirstName,m.LastName,m.MiddleName"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { counter = 0; while (reader.Read()) { counter++; memberID = Convert.ToInt32(reader["MemberID"].ToString()); //Total Monthly Savings totalSavings = Convert.ToDecimal(reader["Amount"]); //check for loan records loanMonthlyRepayment = getMonthlyLoan(memberID); //calculate monthly total deduction using the total savings plus monthly loan repayment totalDeduction = loanMonthlyRepayment + totalSavings; //add data to the lstDeductions String[] row = { counter.ToString(), reader["MemberID"].ToString(), reader["FileNo"].ToString(), reader["Name"].ToString(), CheckForNumber.formatCurrency2(totalSavings.ToString()), CheckForNumber.formatCurrency2(loanMonthlyRepayment.ToString()), CheckForNumber.formatCurrency(totalDeduction.ToString()) }; ListViewItem item = new ListViewItem(row); lstDeductions.Items.Add(item); } reader.Close(); lblRecordNo.Text = "No. of Records: " + counter.ToString(); } } catch (Exception ex) { MessageBox.Show("Post Deductions: - " + ex.Message); } finally { conn.Close(); } }
private void loadMemberRegisteredSavingsType() { decimal totalSavings = 0; SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select m.MemberID, m.FileNo, m.Title + ' ' + m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as [FullName], " + "m.Photo, m.DateCreated as RegDateCreated, t.SavingsName, s.SavingsAcctID, s.Amount, s.Remark, " + "s.DateCreated as SavingsDateCreated from Members m left join MemberSavingsTypeAcct s on m.MemberID=s.MemberID " + "left join SavingsType t on s.SavingsTypeID=t.SavingsTypeID where m.FileNo='" + txtFileNo.Text.Trim() + "'"; /** * string strSharesQuery = "Select m.MemberID, m.FileNo, s.Shares from Members m " + * "inner join Shares s on m.MemberID=s.MemberID where m.FileNo='" + txtFileNo.Text.Trim() + "'"; **/ SqlCommand cmd = new SqlCommand(strQuery, conn); //SqlCommand cmdShares = new SqlCommand(strSharesQuery, conn); try { conn.Open(); /****Execute and read shares --- Modification after first Observation meeting with Client * SqlDataReader reader = cmdShares.ExecuteReader(); * if (reader.HasRows) * { * if (reader.Read()) * { * txtShares.Text = CheckForNumber.formatCurrency(reader["Shares"].ToString()); * } * } * reader.Close(); * * ***/ //Execute and read Regular Savings and other savings SqlDataReader reader = cmd.ExecuteReader(); int recordCount = 0; lstVSavings.Items.Clear(); //Check if record is found and start performing operation if (reader.HasRows) { string paths = PhotoPath.getPath(); picMember.Visible = true; lblMemberProfileInfo.Visible = true; lblRegistrationDate.Visible = true; #region Read Data from Reader while (reader.Read()) { recordCount++; if (recordCount == 1) { //MessageBox.Show("Record Count: " + recordCount.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"); } lblMemberProfileInfo.Text = reader["FullName"].ToString() + "\n" + reader["FileNo"].ToString(); lblRegistrationDate.Text = "Member since " + string.Format("{0:ddd, MMM d, yyyy}", reader["RegDateCreated"]); myselectedmemberID = (int)reader["MemberID"]; } if (reader["Remark"].ToString() == "Shares Savings") { txtRegularSavings.Text = CheckForNumber.formatCurrency(reader["Amount"].ToString()); totalSavings += Convert.ToDecimal(reader["Amount"]); continue; } string[] row = { reader["SavingsAcctID"].ToString(), reader["Remark"].ToString(), CheckForNumber.formatCurrency(reader["Amount"].ToString()), string.Format("{0:ddd, MMM d, yyyy}", reader["SavingsDateCreated"]) }; ListViewItem item = new ListViewItem(row); lstVSavings.Items.Add(item); totalSavings += Convert.ToDecimal(reader["Amount"]); } #endregion } else { MessageBox.Show("Sorry, Record with that FileNo is not found", "Members Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } lblRecordNo.Text = "No. of Records : " + recordCount; lblTotalSavings.Text = "Savings Total: " + CheckForNumber.formatCurrency2(totalSavings.ToString()); lblTotalSavings.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message + "-- loadMemberRegisteredSavingsType -- "); } finally { conn.Close(); } }
private void getContributionDetails(string transactionID) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select c.ContributionID,c.SavingsID,c.PaymentModeID,c.OtherPayment,c.BankID,c.TellerNo,c.Comment,c.SavingsAcctID,c.TransactionID," + "s.SavingsID,s.MemberID,s.SavingSource,s.Amount,s.Month,s.Year,s.TransactionID " + "from Savings s inner join Contributions c on s.TransactionID=c.TransactionID and s.SavingSource='Contribution' and s.TransactionID='" + transactionID + "'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { if (reader.Read()) { if (reader["BankID"].ToString() != string.Empty) { cboBank.SelectedIndex = Convert.ToInt16(reader["BankID"]); } //MessageBox.Show(reader["BankID"].ToString()); cboPaymentMode.SelectedIndex = (int)reader["PaymentModeID"]; txtAmount.Text = CheckForNumber.formatCurrency(reader["Amount"].ToString()); cboMemberSavingsType.SelectedValue = reader["SavingsAcctID"]; txtTellerNo.Text = reader["TellerNo"].ToString(); txtOtherPayments.Text = reader["OtherPayment"].ToString(); txtComment.Text = reader["Comment"].ToString(); cboYear.Text = reader["Year"].ToString(); int specifiedMonth = Convert.ToInt16(reader["Month"]); string monthByName = null; switch (specifiedMonth) { case 1: monthByName = "January"; break; case 2: monthByName = "February"; break; case 3: monthByName = "March"; break; case 4: monthByName = "April"; break; case 5: monthByName = "May"; break; case 6: monthByName = "June"; break; case 7: monthByName = "July"; break; case 8: monthByName = "August"; break; case 9: monthByName = "September"; break; case 10: monthByName = "October"; break; case 11: monthByName = "November"; break; case 12: monthByName = "December"; break; } cboMonth.Text = monthByName; btnEditRecord.Enabled = true; lblSelTransactionalID.Text = reader["TransactionID"].ToString(); lblSavingsID.Text = reader["SavingsID"].ToString(); } } } catch (Exception ex) { MessageBox.Show("getContributionDetails : - " + ex.Message); } finally { conn.Close(); } }
private void btnFindMember_Click(object sender, EventArgs e) { clearAndSetControls(); decimal totalSavings = 0; SqlConnection conn = ConnectDB.GetConnection(); /** - Modified about First Meeting * string strSharesQuery = "Select m.MemberID, m.FileNo, s.SharesID, s.Shares, s.DateCreated from Members m " + * "inner join Shares s on m.MemberID=s.MemberID where m.FileNo='" + txtFileNo.Text.Trim() + "'"; * * **/ string strQuery = "Select m.MemberID, m.FileNo, m.Title + ' ' + m.LastName + ' ' + m.MiddleName + ' ' + m.FirstName as [FullName], m.Photo, m.DateCreated as RegDateCreated," + "t.SavingsName, s.SavingsAcctID, s.Amount, s.Remark, s.DateCreated as SavingsDateCreated from Members m left outer join MemberSavingsTypeAcct s on m.MemberID=s.MemberID " + "left outer join SavingsType t on s.SavingsTypeID=t.SavingsTypeID where m.FileNo='" + txtFileNo.Text.Trim() + "'"; //SqlCommand cmdShares = new SqlCommand(strSharesQuery, conn); SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); lstVSavings.Items.Clear(); /** Modified after first meeting....Shares is type of Savings * SqlDataReader reader = cmdShares.ExecuteReader(); * if (reader.HasRows) * { * if (reader.Read()) * { * string[] row = { reader["SharesID"].ToString(), "Shares", CheckForNumber.formatCurrency(reader["Shares"].ToString()), string.Format("{0:ddd, MMM d, yyyy}", reader["DateCreated"]) }; * ListViewItem item = new ListViewItem(row); * lstVSavings.Items.Add(item); * } * } * reader.Close(); * */ int counter = 0; string paths = PhotoPath.getPath(); //MessageBox.Show(Application.StartupPath.ToString()); //MessageBox.Show(paths.ToString()); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { picMember.Visible = true; lblMemberProfileInfo.Visible = true; lblRegistrationDate.Visible = true; while (reader.Read()) { string[] row = { reader["SavingsAcctID"].ToString(), reader["Remark"].ToString(), CheckForNumber.formatCurrency(reader["Amount"].ToString()), string.Format("{0:ddd, MMM d, yyyy}", reader["RegDateCreated"]) }; ListViewItem item = new ListViewItem(row); lstVSavings.Items.Add(item); counter++; totalSavings += Convert.ToDecimal(reader["Amount"]); if (counter == 1) { 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"); } lblMemberProfileInfo.Text = reader["FullName"].ToString() + "\n" + reader["FileNo"].ToString(); lblRegistrationDate.Text = "Member since " + string.Format("{0:ddd, MMM d, yyyy}", reader["RegDateCreated"]); myselectedmemberID = (int)reader["MemberID"]; } } } else { clearAndSetControls(); MessageBox.Show("Sorry, Record with File No. [" + txtFileNo.Text + "] is not Found!", "Edit Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Information); } lblRecordNo.Text = "No. of Records: " + counter.ToString(); lblTotalSavings.Text = "Savings Total: " + CheckForNumber.formatCurrency2(totalSavings.ToString()); lblTotalSavings.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
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 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 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(); } }