private void searchDB(String SearchBy)
{
if (SearchBy == "First Name")
{
String[] colArray = { "First_Name", "Last_Name", "NIC_", "Current_Debt", "BorrowedDate", "LastPayment" };
String query = "SELECT debtors.FName,debtors.LName,debtors.NIC,(loan.Amount - Sum(installment.amount)),loan.BorrowedDate,installment.amount FROM debtors,loan,installment WHERE debtors.DebtorID = loan.DebtorsID AND installment.RefNo = loan.RefNo AND debtors.FName LIKE '%" + txtBoxSearch.Text + "%' GROUP By loan.RefNo";
fillTable fillTableObj = new fillTable();
fillTableObj.DrawTable(dataGridView1, colArray, query);
}
if (SearchBy == "Last Name")
{
String[] colArray = { "First_Name", "Last_Name", "NIC_", "Current_Debt", "BorrowedDate", "LastPayment" };
String query = "SELECT debtors.FName,debtors.LName,debtors.NIC,(loan.Amount - Sum(installment.amount)),loan.BorrowedDate,installment.amount FROM debtors,loan,installment WHERE debtors.DebtorID = loan.DebtorsID AND installment.RefNo = loan.RefNo AND debtors.LName LIKE '%" + txtBoxSearch.Text + "%' GROUP By loan.RefNo";
fillTable fillTableObj = new fillTable();
fillTableObj.DrawTable(dataGridView1, colArray, query);
}
if (SearchBy == "NIC")
{
String[] colArray = { "First_Name", "Last_Name", "NIC_", "Current_Debt", "BorrowedDate", "LastPayment" };
String query = "SELECT debtors.FName,debtors.LName,debtors.NIC,(loan.Amount - Sum(installment.amount)),loan.BorrowedDate,installment.amount FROM debtors,loan,installment WHERE debtors.DebtorID = loan.DebtorsID AND installment.RefNo = loan.RefNo AND debtors.NIC LIKE '%" + txtBoxSearch.Text + "%' GROUP By loan.RefNo";
fillTable fillTableObj = new fillTable();
fillTableObj.DrawTable(dataGridView1, colArray, query);
}
if (SearchBy == "Date")
{
String[] colArray = { "First_Name", "Last_Name", "NIC_", "Current_Debt", "BorrowedDate", "LastPayment" };
String query = "SELECT debtors.FName,debtors.LName,debtors.NIC,(loan.Amount - Sum(installment.amount)),loan.BorrowedDate,installment.amount FROM debtors,loan,installment WHERE debtors.DebtorID = loan.DebtorsID AND installment.RefNo = loan.RefNo AND loan.BorrowedDate LIKE '%" + txtBoxSearch.Text + "%' GROUP By loan.RefNo";
fillTable fillTableObj = new fillTable();
fillTableObj.DrawTable(dataGridView1, colArray, query);
}
}
private void updateTable()
{
fillTable fillTable = new fillTable();
//String query = "SELECT loan.RefNo,loan.Amount AS Amount,installment.amount AS LastPayment,installment.Date,(loan.Amount - (SELECT SUM(installment.amount) FROM installment WHERE installment.RefNo = loan.RefNo GROUP BY RefNo)) AS'toBePaid',loan.Status FROM loan,installment WHERE loan.RefNo = installment.RefNo AND installment.Date = (SELECT installment.Date FROM installment WHERE installment.RefNo = loan.RefNo ORDER BY installment.Date DESC LIMIT 1) AND loan.DebtorsID ="+debtorsID;
String query = "SELECT loan.RefNo,loan.Amount AS Amount,installment.amount AS LastPayment,installment.Date,'toBePaid',loan.Status FROM loan,installment WHERE loan.RefNo = installment.RefNo AND installment.Date = (SELECT installment.Date FROM installment WHERE installment.RefNo = loan.RefNo ORDER BY installment.Date DESC LIMIT 1) AND loan.DebtorsID =" + debtorsID;
String[] colNames = { "RefNo", "Amount", "LastPayment", "lastPaymentDate", "toBePaid", "Status" };
fillTable.DrawTable(dataGridView1, colNames, query);
}
private void btnDebtorList_Click(object sender, EventArgs e)
{
hideAllPanels();
panelDebtorsList.Visible = true;
DebtorListLbl.Show();
btnDebtorList.BackColor = Color.DarkCyan;
btnDebtorList.ForeColor = Color.White;
cmbBoxSearchBy.SelectedIndex = 0;
String[] colArray = { "First_Name", "Last_Name", "NIC_", "Current_Debt", "BorrowedDate", "LastPayment" };
String query = "SELECT debtors.FName,debtors.LName,debtors.NIC,(loan.Amount - Sum(installment.amount)),loan.BorrowedDate,installment.amount FROM debtors,loan,installment WHERE debtors.DebtorID = loan.DebtorsID AND installment.RefNo = loan.RefNo GROUP By loan.RefNo";
fillTable fillTableObj = new fillTable();
fillTableObj.DrawTable(dataGridView1, colArray, query);
}