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();
}
}