private void btnApplyChanges_Click(object sender, EventArgs e)
{
decimal new_LoansAmt = 0;
//decimal new_totalAmt;
lstMonthlyDeductions.Items[0].SubItems[4].Text = lblTotalSavings.Text;
if (txtChangeLoanAmount.Text == string.Empty)
{
lstMonthlyDeductions.Items[0].SubItems[5].Text = lblTotalLoans.Text;
new_LoansAmt = Convert.ToDecimal(lblTotalLoans.Text);
}
else
{
if (decimal.TryParse(txtChangeLoanAmount.Text, out new_LoansAmt))
{
if (Convert.ToDecimal(txtChangeLoanAmount.Text) > 0)
{
lstMonthlyDeductions.Items[0].SubItems[5].Text = CheckForNumber.formatCurrency2(txtChangeLoanAmount.Text);
new_LoansAmt = Convert.ToDecimal(txtChangeLoanAmount.Text);
}
else
{
MessageBox.Show("The new Loan Amount entered is invalid", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
lstMonthlyDeductions.Items[0].SubItems[5].Text = lblTotalLoans.Text;
new_LoansAmt = Convert.ToDecimal(lblTotalLoans.Text);
}
}
}
lstMonthlyDeductions.Items[0].SubItems[6].Text = CheckForNumber.formatCurrency2(Convert.ToString(Convert.ToDecimal(lblTotalSavings.Text) + new_LoansAmt));
}
private void btnChangeLoanAmt_Click(object sender, EventArgs e)
{
decimal new_LoansAmt;
decimal new_totalAmt;
if (lstVwLoans.SelectedItems.Count > 0 && txtChangeLoanAmount.Text != string.Empty)
{
if (decimal.TryParse(txtChangeLoanAmount.Text, out new_LoansAmt))
{
if (Convert.ToDecimal(txtChangeLoanAmount.Text) > 0)
{
lstMonthlyDeductions.Items[0].SubItems[5].Text = CheckForNumber.formatCurrency2(txtChangeLoanAmount.Text);
new_totalAmt = Convert.ToDecimal(lstMonthlyDeductions.Items[0].SubItems[4].Text) + Convert.ToDecimal(lstMonthlyDeductions.Items[0].SubItems[5].Text);
lstMonthlyDeductions.Items[0].SubItems[6].Text = CheckForNumber.formatCurrency2(new_totalAmt.ToString());
}
else
{
MessageBox.Show("The Amount entered is invalid", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("The Amount entered is invalid", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Select a Loan and Supply a new Loan Amount", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void txtChangeLoanAmount_Leave(object sender, EventArgs e)
{
if (!FieldValidator.isBlank(txtChangeLoanAmount.Text) && CheckForNumber.isNumeric(txtChangeLoanAmount.Text))
{
txtChangeLoanAmount.Text = CheckForNumber.formatCurrency2(txtChangeLoanAmount.Text);
}
}
private void btnChangeSavingsAmt_Click(object sender, EventArgs e)
{
decimal new_SavingsAmount;
decimal totalSavingsRecalculated = 0;
if (lstVwSavings.SelectedItems.Count > 0 && txtChangeSavingsAmt.Text != string.Empty)
{
int lstCountItem = lstVwSavings.Items.Count;
if (Decimal.TryParse(txtChangeSavingsAmt.Text, out new_SavingsAmount))
{
lstVwSavings.SelectedItems[0].SubItems[2].Text = CheckForNumber.formatCurrency2(txtChangeSavingsAmt.Text);
for (int i = 0; i < lstCountItem; i++)
{
totalSavingsRecalculated += Convert.ToDecimal(lstVwSavings.Items[i].SubItems[2].Text);
}
lblTotalSavings.Text = CheckForNumber.formatCurrency2(totalSavingsRecalculated.ToString());
}
else
{
MessageBox.Show("The Amount entered is invalid", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Select an a Savings Type and Supply an Amount", "Deduction Info", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void txtWithdrawAmt_TextChanged(object sender, EventArgs e)
{
if (txtWithdrawAmt.Text != string.Empty)
{
if (CheckForNumber.isNumeric(txtWithdrawAmt.Text))
{
decimal withdrawalAmt = Convert.ToDecimal(txtWithdrawAmt.Text);
decimal balance = memberTotalSavingsByType - (withdrawalAmt);
lblAmtBalance.Text = CheckForNumber.formatCurrency2(balance.ToString());
//Enable or disable btn based on balance
if (balance >= 0 && withdrawalAmt > 0)
{
btnPost.Enabled = true;
}
else
{
btnPost.Enabled = false;
}
}
else
{
MessageBox.Show("Invalid Amount has been supplied", "Withdrawal", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
btnPost.Enabled = false;
}
}
private void datGrdVwLoanApplications_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (datGrdVwLoanApplications.SelectedRows.Count > 0)
{
lblAmountPaid.Text = string.Empty;
lblOutstandingAmt.Text = string.Empty;
lblRepaymentStatus.Text = string.Empty;
//MessageBox.Show(e.ColumnIndex.ToString());
string amountPaid = datGrdVwLoanApplications.SelectedRows[0].Cells[14].Value.ToString();
string outstanding = datGrdVwLoanApplications.SelectedRows[0].Cells[15].Value.ToString();
string repaymentStatus = datGrdVwLoanApplications.SelectedRows[0].Cells[16].Value.ToString();
//MessageBox.Show(amountPaid.ToString());
if (amountPaid != string.Empty)
{
lblAmountPaid.Text = CheckForNumber.formatCurrency2(amountPaid);
}
if (outstanding != string.Empty)
{
lblOutstandingAmt.Text = CheckForNumber.formatCurrency2(outstanding);
}
lblRepaymentStatus.Text = repaymentStatus;
if (amountPaid == string.Empty || outstanding == string.Empty)
{
btnViewDetails.Enabled = false;
}
else
{
btnViewDetails.Enabled = true;
}
}
}
private void getSelectedDetailsTotalSavings(string strFilter)
{
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Select SUM(Amount) as Total from Savings " + strFilter;
SqlCommand cmd = new SqlCommand(strQuery, conn);
//MessageBox.Show(strQuery);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
lblSelectedSavingsTotal.Text = "Total Savings: " + CheckForNumber.formatCurrency2(reader["Total"].ToString());
}
}
}
catch (Exception ex)
{
MessageBox.Show("Total Savings: " + ex.Message);
}
finally
{
conn.Close();
}
}
private void txtInterest_Leave(object sender, EventArgs e)
{
decimal totalRepayment;
decimal monthlyRepayment;
int duration = Convert.ToInt16(lblDuration.Text);
if (txtInterest.Text != string.Empty && (CheckForNumber.isNumeric(txtInterest.Text)))
{
if (txtAmount.Text != string.Empty && (CheckForNumber.isNumeric(txtAmount.Text)))
{
totalRepayment = Convert.ToDecimal(txtAmount.Text) + Convert.ToDecimal(txtInterest.Text);
monthlyRepayment = totalRepayment / duration;
txtTotalRepayment.Text = CheckForNumber.formatCurrency2(totalRepayment.ToString());
txtInterest.Text = CheckForNumber.formatCurrency2(txtInterest.Text);
txtMonthlyRepayment.Text = monthlyRepayment.ToString();
}
else
{
MessageBox.Show("Enter a valid Capital to proceed", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Invalid data entry", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtInterest.Text = string.Empty;
}
}
private void getSavingStatus()
{
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Select SUM(Amount) as TotalSavings from Savings where MemberID=" + memberID;
SqlCommand cmd = new SqlCommand(strQuery, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
lblFinancialStatus.Text = "Savings: " + CheckForNumber.formatCurrency2(reader["TotalSavings"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
lblFinancialStatus.Visible = true;
}
private void displayTotalOfDataSet()
{
SqlConnection conn = ConnectDB.GetConnection();
cmdTotal = new SqlCommand(totalContributionOfDataSet, conn);
try
{
conn.Open();
SqlDataReader reader = cmdTotal.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
lblTotalAmountOfDataSet.Text = "Total: " + CheckForNumber.formatCurrency2(reader["TotalAmount"].ToString());
}
else
{
lblTotalAmountOfDataSet.Text = "Total: 0.0";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void addSavingsIntoListView()
{
bool alreadyExist = false;
if (lstVwSavings.Items.Count != 0)
{
for (int i = 0; i < lstVwSavings.Items.Count; i++)
{
if (lstVwSavings.Items[i].SubItems[1].Text.Trim() == cboSavingsAcct.Text)
{
alreadyExist = true;
}
}
}
if (alreadyExist == false)
{
String[] row = { cboSavingsAcct.SelectedValue.ToString(), cboSavingsAcct.Text, txtAmount.Text };
ListViewItem item = new ListViewItem(row);
lstVwSavings.Items.Add(item);
//Add amount to total
totalAmount = Convert.ToDecimal(txtAmount.Text) + totalAmount;
lblTotalSavings.Text = "Total: " + CheckForNumber.formatCurrency2(totalAmount.ToString());
}
else
{
MessageBox.Show("That Savings Account has already been added", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void dtGrdVwSavings_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string savingsAcct;
decimal savingsTotal = 0;
btnUpdate.Enabled = true;
//MessageBox.Show(e.ColumnIndex.ToString());
if (e.ColumnIndex == 5)
{
string transactionID = dtGrdVwSavings.Rows[e.RowIndex].Cells[3].Value.ToString();
grpBoxDetails.Text = "Savings Details [" + transactionID + "]";
lblSavingsID.Text = 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());
}
}
lblSavingsTotal.Text = CheckForNumber.formatCurrency2(savingsTotal.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
}
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 txtTotalRepayment_Leave(object sender, EventArgs e)
{
if (!FieldValidator.isBlank(txtTotalRepayment.Text))
{
txtTotalRepayment.Text = CheckForNumber.formatCurrency2(txtTotalRepayment.Text);
}
else
{
MessageBox.Show("Total Repayment Amount cannot be left blank.", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void txtAmount_Leave(object sender, EventArgs e)
{
if (txtAmount.Text != string.Empty)
{
if (CheckForNumber.isNumeric(txtAmount.Text))
{
txtAmount.Text = CheckForNumber.formatCurrency2(txtAmount.Text);
}
btnPostSavings.Enabled = true;
}
}
private void calculateSavingsTotal()
{
decimal savingsTotal = 0;
if (lstVwSavingDetails.Items.Count > 0)
{
for (int i = 0; i < lstVwSavingDetails.Items.Count; i++)
{
//MessageBox.Show(lstVwSavingDetails.Items[i].SubItems[2].Text.ToString());
savingsTotal = savingsTotal + Convert.ToDecimal(lstVwSavingDetails.Items[i].SubItems[3].Text.ToString());
}
lblSavingsTotal.Text = CheckForNumber.formatCurrency2(savingsTotal.ToString());
}
}
private void btnRemoveSavings_Click(object sender, EventArgs e)
{
if (lstVwSavings.SelectedItems.Count > 0)
{
decimal selAmount = Convert.ToDecimal(lstVwSavings.SelectedItems[0].SubItems[2].Text.ToString());
totalAmount = totalAmount - selAmount;
lblTotalSavings.Text = "Total: " + CheckForNumber.formatCurrency2(totalAmount.ToString());
lstVwSavings.Items.RemoveAt(lstVwSavings.SelectedIndices[0]);
}
else
{
MessageBox.Show("Please select an item to remove from the list", "Savings", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void getLoansInfo(string memberID)
{
//decimal loanMonthlyPayment = 0;
//get loan amount
decimal serviceLoanAmount = Convert.ToDecimal(lstMonthlyDeductions.Items[0].SubItems[5].Text);
if (serviceLoanAmount > 0)
{
SqlConnection conn = ConnectDB.GetConnection();
string sqlQuery = "Select a.MonthlyRepayment,l.RepaymentAmount,l.AmountPaid,l.OutstandingAmount, lt.Type, lt.LoanTypeID from LoanApplication a " +
"inner join Loans l on a.LoanApplicationID = l.LoanApplicationID " +
"inner join LoanType lt on a.LoanTypeID=lt.LoanTypeID " +
"where a.MemberID=@MemberID and l.OutstandingAmount<>0";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
cmd.Parameters.Add("@MemberID", SqlDbType.NVarChar, 20);
cmd.Parameters["@MemberID"].Value = memberID;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
lstVwLoans.Items.Clear();
if (reader.HasRows)
{
reader.Read();
string[] row = { CheckForNumber.formatCurrency2(reader["MonthlyRepayment"].ToString()), CheckForNumber.formatCurrency2(reader["RepaymentAmount"].ToString()), CheckForNumber.formatCurrency2(reader["AmountPaid"].ToString()), CheckForNumber.formatCurrency2(reader["OutstandingAmount"].ToString()), reader["Type"].ToString(), reader["LoanTypeID"].ToString() };
ListViewItem item = new ListViewItem(row);
lstVwLoans.Items.Add(item);
lblTotalLoans.Text = CheckForNumber.formatCurrency2(reader["MonthlyRepayment"].ToString());
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("Get Loans Info: - " + ex.Message);
}
finally
{
conn.Close();
}
}
}
private void getLoanStatus()
{
SqlConnection conn = ConnectDB.GetConnection();
string strQuery = "Select count(*) as counter from Loans l " +
"left join LoanApplication a on l.LoanApplicationID = a.LoanApplicationID " +
"where (l.PaymentFinished is NULL or l.PaymentFinished='No') " +
"and a.MemberID=" + memberID;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = strQuery;
try
{
conn.Open();
int recFound = (int)cmd.ExecuteScalar();
if (recFound > 0)
{
strQuery = "Select SUM(l.OutstandingAmount) as Outstanding from Loans l " +
"left join LoanApplication a on l.LoanApplicationID = a.LoanApplicationID " +
"where (l.PaymentFinished is NULL or l.PaymentFinished='No') " +
"and a.MemberID=" + memberID;
cmd.CommandText = strQuery;
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string outstandingAmount = reader["Outstanding"].ToString();
lblFinancialStatus.Text += "\nOutstanding Loan: " + CheckForNumber.formatCurrency2(outstandingAmount) + " from " + recFound.ToString() + " Loan Applications";
reader.Close();
}
else
{
lblFinancialStatus.Text += "\n" + "No Outstanding Loan to pay.";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void txtWithdrawAmt_Leave(object sender, EventArgs e)
{
if (txtWithdrawAmt.Text != string.Empty)
{
if (CheckForNumber.isNumeric(txtWithdrawAmt.Text))
{
txtWithdrawAmt.Text = CheckForNumber.formatCurrency2(txtWithdrawAmt.Text);
}
else
{
MessageBox.Show("Invalid Amount has been supplied", "Withdrawal", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtWithdrawAmt.Text = string.Empty;
}
}
else
{
MessageBox.Show("Amount has not been supplied", "Withdrawal", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void txtAmountPaid_Leave(object sender, EventArgs e)
{
if (!FieldValidator.isBlank(txtAmountPaid.Text) && CheckForNumber.isNumeric(txtAmountPaid.Text))
{
decimal totalAmountRepayment = Convert.ToDecimal(txtTotalRepayment.Text);
decimal amountPaid = Convert.ToDecimal(txtAmountPaid.Text);
decimal outstandingAmount;
if (amountPaid > totalAmountRepayment)
{
MessageBox.Show("Sorry, that Amount Paid exceeds the Total Repayment Amount.", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtAmountPaid.Text = string.Empty;
}
else
{
txtAmountPaid.Text = CheckForNumber.formatCurrency2(txtAmountPaid.Text);
outstandingAmount = totalAmountRepayment - amountPaid;
txtOutstandingAmt.Text = CheckForNumber.formatCurrency2(outstandingAmount.ToString());
//check outstanding amount and set paymentStatus
if (outstandingAmount == 0)
{
paymentStatus = "PAID";
paymentFinished = "Yes";
dateFinishedPayment = dtPickerPaymentFinished.Value.ToString();
lblPaymentFinishedDate.Visible = true;
dtPickerPaymentFinished.Visible = true;
}
else
{
paymentStatus = "Paying";
paymentFinished = "No";
dateFinishedPayment = string.Empty;
lblPaymentFinishedDate.Visible = false;
dtPickerPaymentFinished.Visible = false;
}
}
}
}
}//end of executeDeduction
private void getSavingsInfo(string memberID)
{
decimal calTotalSavings = 0;
SqlConnection conn = ConnectDB.GetConnection();
string sqlQuery = "Select SavingsTypeID,Remark,Amount from MemberSavingsTypeAcct where MemberID=@MemberID";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
cmd.Parameters.Add("@MemberID", SqlDbType.NVarChar, 20);
cmd.Parameters["@MemberID"].Value = memberID;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
lstVwSavings.Items.Clear();
if (reader.HasRows)
{
while (reader.Read())
{
calTotalSavings += Convert.ToDecimal(reader["Amount"]);
string[] row = { reader["SavingsTypeID"].ToString(), reader["Remark"].ToString(), CheckForNumber.formatCurrency2(reader["Amount"].ToString()) };
ListViewItem item = new ListViewItem(row);
lstVwSavings.Items.Add(item);
}
lblTotalSavings.Text = CheckForNumber.formatCurrency2(calTotalSavings.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Get Savings Info - " + ex.Message);
}
finally
{
conn.Close();
}
}
private void txtAmount_Leave(object sender, EventArgs e)
{
if (txtAmount.Text != string.Empty && (CheckForNumber.isNumeric(txtAmount.Text)))
{
if (Convert.ToDecimal(txtAmount.Text) > 0)
{
txtAmountPaid.Enabled = true;
decimal loanAmount = Convert.ToDecimal(txtAmount.Text);
decimal interestRate = Convert.ToDecimal(lblInterestRate.Text);
string strDuration = lblDuration.Text;
decimal duration = decimal.Parse(strDuration);
decimal totalRepayment;
decimal monthlyRepayment;
decimal interestAmount = loanAmount * (interestRate / 100);
txtInterest.Text = CheckForNumber.formatCurrency2(interestAmount.ToString());
totalRepayment = loanAmount + interestAmount;
monthlyRepayment = totalRepayment / duration;
txtTotalRepayment.Text = CheckForNumber.formatCurrency2(totalRepayment.ToString());
txtAmount.Text = CheckForNumber.formatCurrency2(txtAmount.Text);
txtMonthlyRepayment.Text = CheckForNumber.formatCurrency2(monthlyRepayment.ToString());
}
else
{
txtAmountPaid.Enabled = false;
MessageBox.Show("The Amount is required to proceed", "Loans", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
txtAmountPaid.Text = string.Empty;
txtAmountPaid.Enabled = false;
}
}
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 txtFormFee_Leave(object sender, EventArgs e)
{
txtFormFee.Text = CheckForNumber.formatCurrency2(txtFormFee.Text);
}
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 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 updateMemberSavings()
{
SqlConnection conn = ConnectDB.GetConnection();
string savingsType = lstVSavings.SelectedItems[0].SubItems[1].Text;
string savingsID = lstVSavings.SelectedItems[0].SubItems[0].Text;
string amount = txtAmount.Text.Trim();
//string strUpdateShares = "Update Shares set Shares=@Shares where SharesID=@SharesID";
//SqlCommand cmdUpdateShares = new SqlCommand(strUpdateShares, conn);
#region cmd parameters
//cmdUpdateShares.Parameters.Add("@Shares", System.Data.SqlDbType.Decimal);
//cmdUpdateShares.Parameters["@Shares"].Value = amount;
//cmdUpdateShares.Parameters.Add("@SharesID", System.Data.SqlDbType.Int);
//cmdUpdateShares.Parameters["@SharesID"].Value = savingsID;
DateTime dateModified = DateTime.Now;
string strUpdateSavings = "Update MemberSavingsTypeAcct set Amount=@Amount, DateModified=@dateModified where SavingsAcctID=@SavingsAcctID";
SqlCommand cmdUpdateSavings = new SqlCommand(strUpdateSavings, conn);
cmdUpdateSavings.Parameters.Add("@Amount", System.Data.SqlDbType.Decimal);
cmdUpdateSavings.Parameters["@Amount"].Value = amount;
cmdUpdateSavings.Parameters.Add("@SavingsAcctID", System.Data.SqlDbType.Int);
cmdUpdateSavings.Parameters["@SavingsAcctID"].Value = savingsID;
cmdUpdateSavings.Parameters.Add("@dateModified", System.Data.SqlDbType.DateTime);
cmdUpdateSavings.Parameters["@dateModified"].Value = dateModified;
#endregion cmd parameters
try
{
conn.Open();
int rowAffected = 0;
rowAffected = cmdUpdateSavings.ExecuteNonQuery();
if (rowAffected > 0)
{
MessageBox.Show("Savings Account has been Successfully Updated", "Edit Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Information);
lstVSavings.SelectedItems[0].SubItems[2].Text = txtAmount.Text.Trim();
txtAmount.Clear();
txtComment.Clear();
txtAmount.Enabled = false;
txtComment.Enabled = false;
btnUpdate.Enabled = false;
lstVSavings.SelectedItems.Clear();
//recalculate totalSavings
decimal totalSavings = 0;
for (int i = 0; i < lstVSavings.Items.Count; i++)
{
totalSavings += Convert.ToDecimal(lstVSavings.Items[i].SubItems[2].Text.ToString());
}
lblTotalSavings.Text = "Savings Total: " + CheckForNumber.formatCurrency2(totalSavings.ToString());
}
else
{
MessageBox.Show("An Error has Occurred Updating Savings", "Edit Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(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 void cboSavingsType_SelectedIndexChanged(object sender, EventArgs e)
{
//MessageBox.Show(cboSavingsType.SelectedIndex.ToString());
lstVwSavingsSource.Items.Clear();
lblAcctAmount.Text = string.Empty;
txtWithdrawAmt.Text = string.Empty;
if (cboSavingsType.SelectedIndex != 0)
{
//Enable btnPose
btnPost.Enabled = true;
//Enable txtWithdrawal control
txtWithdrawAmt.Enabled = true;
int savingsTypeID = Convert.ToInt16(cboSavingsType.SelectedValue);
//MessageBox.Show("Member ID: " + memberID + "\nSavings TypeID: " + savingsTypeID);
SavingsByAcctType memberSavings = new SavingsByAcctType();
#region Member Contributions
decimal contributionsByType = memberSavings.getContributionSavings(memberID, savingsTypeID);
//Add Contributions to ListView
string[] row = { "Contributions", CheckForNumber.formatCurrency2(contributionsByType.ToString()) };
ListViewItem item = new ListViewItem(row);
lstVwSavingsSource.Items.Add(item);
#endregion
#region Member Savings Forward
decimal savingsForwardType = memberSavings.getSavingsForward(memberID, savingsTypeID);
//Add SavingsForward to ListView
string[] row2 = { "Savings Forward", CheckForNumber.formatCurrency2(savingsForwardType.ToString()) };
item = new ListViewItem(row2);
lstVwSavingsSource.Items.Add(item);
#endregion
#region Member Deductions Type
decimal deductionsByType = memberSavings.getDeductionSavings(memberID, savingsTypeID);
//Add Deductions to ListView
string[] row3 = { "Deductions", CheckForNumber.formatCurrency2(deductionsByType.ToString()) };
item = new ListViewItem(row3);
lstVwSavingsSource.Items.Add(item);
#endregion
#region Member Withdrawal
decimal withdrawalByType = memberSavings.getWithdrawalSavings(memberID, savingsTypeID);
//Add Withdrawal to ListView
string[] row4 = { "Withdrawal", CheckForNumber.formatCurrency2("-" + withdrawalByType.ToString()) };
item = new ListViewItem(row4);
lstVwSavingsSource.Items.Add(item);
#endregion
memberTotalSavingsByType = (contributionsByType + savingsForwardType + deductionsByType) - (withdrawalByType);
lblAcctAmount.Text = CheckForNumber.formatCurrency2(memberTotalSavingsByType.ToString());
//MessageBox.Show("Contribution : " + contributionsByType.ToString() + "\nSavings Forward: " + savingsForwardType.ToString() + "\nDeduction: " + deductionsByType.ToString());
if (memberTotalSavingsByType == 0)
{
txtWithdrawAmt.Enabled = false;
btnPost.Enabled = false;
}
else
{
txtWithdrawAmt.Enabled = true;
btnPost.Enabled = true;
}
}
else
{
btnPost.Enabled = false;
txtWithdrawAmt.Enabled = false;
}
}