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