/**
* @param column
* @param sortingStatus
* @return
*/
private String toJSon(int column, int sortingStatus) {
try {
JSONObject response = new JSONObject();
JSONObject content = new JSONObject();
JSONArray array = new JSONArray();
if (column == -1 || sortingStatus == -2) {
array.put(tableModel.getHeaderVector());
} else if (column > -1 && sortingStatus > -2 && sortingStatus < 2 /*-1,0 or 1 */) {
Vector<String> vectorCopy = (Vector<String>) tableModel.getHeaderVector().clone();
for (int header = 0; header < tableModel.getHeaderVector().size(); header++) {
if (header == column) {
switch (sortingStatus) {
case -1:
vectorCopy.set(header, vectorCopy.elementAt(header) + " ↓");
break;
case 0:
vectorCopy.set(header, vectorCopy.elementAt(header) + " ↑↓");
break;
case 1:
vectorCopy.set(header, vectorCopy.elementAt(header) + " ↑");
break;
}
}
}
array.put(vectorCopy);
}
for (int row = 0; row < tableModel.getDataVector().size(); row++) {
array.put((Vector<Object>) tableModel.getDataVector().get(row));
}
content.put("table", array);
content.put("isAdmin", new Boolean(isAdmin).toString());
response.put("result", content);
return response.toString();
} catch (JSONException e) {
e.printStackTrace();
}
return null;
}
/**
* Use the current sql command with a Count(*) to return the number of results of the current <br>
* sql command
*
* @throws SQLException
*/
private void countResults(HttpSession session, Connection con) throws SQLException {
String currentSqlCommand = (String) session.getAttribute("countSqlCommand");
Vector sqlParams = (Vector) session.getAttribute("countSqlParams");
int fromPosition = currentSqlCommand.toLowerCase().indexOf("from");
String secondPart = currentSqlCommand.substring(fromPosition);
// AMS - MySQL does not like spaces between COUNT and (*)
String SQLCommandCountStar = "SELECT COUNT(*) " + secondPart;
if (logger.isDebugEnabled()) {
logger.debug("GetTableServlet: SQLCommandCountStar: " + SQLCommandCountStar);
}
logger.info("GetTableServlet: SQLCommandCountStar: " + SQLCommandCountStar);
PreparedStatement pstmt = null;
ResultSet resultSet = null;
try {
pstmt = con.prepareStatement(SQLCommandCountStar);
pstmt = TableModel.setPStmtParameters(pstmt, sqlParams);
resultSet = pstmt.executeQuery();
resultSet.next();
int numberOfResults = resultSet.getInt(1);
session.setAttribute("numberOfResults", new Integer(numberOfResults));
} catch (SQLException ex) {
throw ex;
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException ex) {
logger.error(ex);
}
}
}
/**
* <b>sqlCommandProcessing</b><br>
* Create the sql command beginning with the sql command specified in the web.xml file and
* applying all the options <br>
* specified by the user. This command also apply a limit in the results to avoid to overload the
* server
*/
private void sqlCommandProcessing(
HttpSession session,
int optionNumberInt,
String page,
String numberResultsByPage,
String databaseType) {
int parameterNumber = 1;
reInitSqlCommand(session);
String currentSqlCommand = (String) session.getAttribute("currentSqlCommand");
Vector currentSqlParams = new Vector();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if (logger.isDebugEnabled()) {
logger.debug("SQL-BASE: " + currentSqlCommand);
}
// If search criteria was entered, build the WHERE clause to
// include the filter data entered
if (map != null && optionNumberInt > 0) {
String commandTemp = currentSqlCommand;
if (currentSqlCommand.toLowerCase().indexOf("where") > -1) {
commandTemp += " AND ";
} else {
commandTemp += " WHERE ";
}
while (parameterNumber <= optionNumberInt) {
if (map.containsKey("option" + parameterNumber)) {
if (map.containsKey("and-or" + parameterNumber)) {
commandTemp += " " + map.get("and-or" + parameterNumber) + " ";
}
String parameterName = map.get("option" + parameterNumber).toString();
if (parameterName.equals("ip")) {
commandTemp += " main.ip LIKE ?";
currentSqlParams.add(
TableModel.setSqlParam(
TableModel.STRING, "%" + map.get("value" + parameterNumber).toString() + "%"));
} else if (parameterName.equals("pass")) {
commandTemp += " main.pass = ?";
currentSqlParams.add(
TableModel.setSqlParam(
TableModel.BOOLEAN, map.get("value" + parameterNumber).toString()));
} else if (parameterName.equals("test")) {
commandTemp += " main.test = ?";
currentSqlParams.add(
TableModel.setSqlParam(
TableModel.STRING, map.get("value" + parameterNumber).toString()));
} else if (parameterName.equals("company")) {
commandTemp += " ip.company_name = ?";
currentSqlParams.add(
TableModel.setSqlParam(
TableModel.STRING, map.get("value" + parameterNumber).toString()));
} else if (parameterName.equals("date1")) {
String date1 = map.get("value" + parameterNumber).toString().trim();
java.util.Date d1;
GregorianCalendar endday = new GregorianCalendar();
try {
d1 = sdf.parse(date1);
if (map.containsKey("option" + (parameterNumber + 1))
&& map.get("option" + (parameterNumber + 1)).toString().equals("date2")) {
if (map.containsKey("value" + (parameterNumber + 1))) {
String date2 = map.get("value" + (parameterNumber + 1)).toString().trim();
java.util.Date d2 = sdf.parse(date2);
if (d2.getTime() > d1.getTime()) {
endday.setTimeInMillis(d2.getTime());
endday.roll(Calendar.DAY_OF_MONTH, +1);
d2 = endday.getTime();
commandTemp += " timereceived >= ? and timereceived < ?";
} else if (d2.getTime() < d1.getTime()) {
endday.setTimeInMillis(d1.getTime());
endday.roll(Calendar.DAY_OF_MONTH, +1);
d1 = endday.getTime();
commandTemp += " timereceived < ? and timereceived >= ?";
} else {
// From Date same as To Date
endday.setTimeInMillis(d2.getTime());
endday.roll(Calendar.DAY_OF_MONTH, +1);
d2 = endday.getTime();
commandTemp += " timereceived >= ? and timereceived < ?";
}
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1));
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d2));
map.remove("value" + (parameterNumber + 1));
map.remove("option" + (parameterNumber + 1));
if (map.containsKey("and-or" + (parameterNumber + 1))) {
map.remove("and-or" + (parameterNumber + 1));
}
}
} else {
// To Date not provided
commandTemp += " timereceived >= ? ";
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1));
}
} catch (ParseException e) {
logger.error("Date Parsing Error: " + e);
}
} else if (parameterName.equals("date2")) {
// From Date not provided
String date1 = map.get("value" + parameterNumber).toString();
java.util.Date d1 = new java.util.Date();
try {
d1 = sdf.parse(date1);
} catch (ParseException ex) {
logger.error("Date Parsing Error: " + ex);
}
GregorianCalendar endday = new GregorianCalendar();
endday.setTime(d1);
endday.roll(Calendar.DAY_OF_MONTH, +1);
commandTemp += " timereceived < ?";
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime()));
} else if (parameterName.equals("date")) {
String value = map.get("value" + parameterNumber).toString();
GregorianCalendar today = new GregorianCalendar();
GregorianCalendar endday = new GregorianCalendar();
if (value.equals("today")) {
endday.roll(Calendar.DAY_OF_MONTH, +1);
} else if (value.equals("yesterday")) {
today.roll(Calendar.DAY_OF_MONTH, -1);
} else if (value.equals("2days")) {
today.roll(Calendar.DAY_OF_MONTH, -2);
endday.roll(Calendar.DAY_OF_MONTH, -1);
} else if (value.equals("3days")) {
today.roll(Calendar.DAY_OF_MONTH, -3);
endday.roll(Calendar.DAY_OF_MONTH, -2);
}
commandTemp += " timereceived >= ? and timereceived < ?";
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, today.getTime()));
currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime()));
}
}
parameterNumber++;
}
if (logger.isDebugEnabled()) {
logger.debug("SQL-WITH-FILTERS: " + commandTemp);
}
currentSqlCommand = commandTemp;
}
// Save SQL for count(*) command
session.setAttribute("countSqlCommand", currentSqlCommand);
session.setAttribute("countSqlParams", currentSqlParams);
// Add Paging logic to the SQL Statement
if (logger.isDebugEnabled()) {
logger.debug("GetTableServlet: SQLRequest before paging: >" + currentSqlCommand);
}
int nbResByPage = MAX_RESULTS_BY_PAGE;
if (numberResultsByPage != null) {
nbResByPage = new Integer(numberResultsByPage).intValue();
}
int pageNumber = 0;
if (page != null) {
pageNumber = new Integer(page).intValue();
}
// Add database specific paging logic to the SQL command
HashMap sqlMap =
TableModel.getSQLWithPaging(
databaseType, currentSqlCommand, currentSqlParams, pageNumber, nbResByPage);
StringBuffer completeSQL = (StringBuffer) sqlMap.get("completeSQL");
Vector<HashMap> completeSqlParams = (Vector<HashMap>) sqlMap.get("completeSqlParams");
// Save the SQL Command
session.setAttribute("currentSqlCommand", completeSQL.toString());
session.setAttribute("currentSqlParams", completeSqlParams);
if (logger.isInfoEnabled()) {
logger.debug("LogBrowser Page: " + page);
logger.info("LogBrowser SQL (with paging): " + completeSQL);
}
}
