Example #1
0
        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);
            }
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }