private HashMap<String, ArrayList<Object>> getTableData() {
// get all the information for the query
String useDates = cboxOpenCloseDB.getSelectedItem().toString();
Date date = new Date();
if (datePickerTo.getDate() == null) {
datePickerTo.setDate(date); // set today's date
}
Date dateTo = datePickerTo.getDate();
if (datePickerFrom.getDate() == null) {
date = new Date(-1900, 0, 1);
datePickerFrom.setDate(date);
}
Date dateFrom = datePickerFrom.getDate();
String app = cboxApp.getSelectedItem().toString();
String programmer = cboxProgrammer.getSelectedItem().toString();
// write the query
String query = "SELECT * FROM " + DB_TABLE_NAME + " ";
ArrayList<String> queries = new ArrayList<>();
queries.add(getAppQuery(app));
queries.add(getProgrammerQuery(programmer));
queries.add(getDatesQuery(useDates, dateFrom, dateTo));
queries.add("ORDER BY " + COL_RK + " ASC");
boolean needsWhereClause = true;
for (int i = 0; i < queries.size(); i++) {
if (!queries.get(i).equals("")) {
if (i == queries.size() - 1) { // this is the sorting query
query += queries.get(i) + " ";
} else if (needsWhereClause) {
query += "WHERE " + queries.get(i) + " ";
needsWhereClause = false;
} else {
query += "AND " + queries.get(i) + " ";
}
}
}
// make sure connection is open
DBConnection.close();
DBConnection.open();
// execute query and return data to hash map
sql = new SQL_Commands(DBConnection.getConnection());
HashMap<String, ArrayList<Object>> map;
System.out.println(query); // for debugging
map = sql.getTableData(sql.executeQuery(query));
return map;
}
private void initComboBoxes() {
HashMap<String, ArrayList<Object>> map;
// make sure the connection is open
DBConnection.close(); // close old connection
DBConnection.open(); // open new connection
sql.setConnection(DBConnection.getConnection()); // set connection
sql.createStatement(DBConnection.getConnection()); // create statement
// Opened or Closed Dates
cboxOpenCloseDB.removeAllItems();
cboxOpenCloseDB.addItem(DATES_STILL_OPENED);
cboxOpenCloseDB.addItem(DATES_CLOSED);
cboxOpenCloseDB.addItem(DATES_OPENED);
cboxOpenCloseDB.addItem(DATES_ALL);
datePickerFrom.setEnabled(false);
datePickerTo.setEnabled(false);
Date date = new Date();
datePickerTo.setDate(date);
// App combobox
map = sql.getDistinctColumnValues(DB_TABLE_NAME, COL_APP);
cboxApp.removeAllItems();
cboxApp.addItem(ALL);
for (int i = 0; i < map.get(COL_APP).size(); i++) {
cboxApp.addItem(map.get(COL_APP).get(i).toString());
}
// Programmer combobox
map = sql.getDistinctColumnValues(DB_TABLE_NAME, COL_PROGRAMMER);
cboxProgrammer.removeAllItems();
cboxProgrammer.addItem(ALL);
for (int i = 0; i < map.get(COL_PROGRAMMER).size(); i++) {
cboxProgrammer.addItem(map.get(COL_PROGRAMMER).get(i).toString());
}
}
