public MiscellaneousDataHolder getSessionData(Connection conn,
java.util.Date start, java.util.Date end, boolean excludeBots)
throws SQLException {
ResultSet sessions = null;
PreparedStatement ps = null;
MiscellaneousDataHolder result = null;
String sql = null;
try {
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy:HH:mm:ss");
String startDate = sdf.format(start);
String endDate = sdf.format(end);
String botLine = "";
if (excludeBots) {
botLine = "AND spider_from is null ";
}
sql = "SELECT stats_id, page, query2, "
+ "to_char(date_time,'DD-MON-YYYY:hh24:mi:ss') as theDate "
+ "FROM stats_requests " + "WHERE stats_id in "
+ "(select stats_id from stats_user_sessions "
+ " where date_time>=TO_DATE(?, 'DD-MON-YYYY:hh24:mi:ss') "
+ "AND date_time<=TO_DATE(?, 'DD-MON-YYYY:hh24:mi:ss') "
+ botLine + ") ORDER BY stats_id, date_time";
ps = conn.prepareStatement(sql);
ps.setString(1, startDate);
ps.setString(2, endDate);
sessions = ps.executeQuery();
ArrayList<Long> visitLengths = new ArrayList<Long>();
int extLinkExits = 0;
int extLinkReturns = 0;
String currentSessionId = "";
Date sessionStart = new Date(), sessionEnd = new Date();
boolean leftSite = false;
float totalDifference = 0F;
int totalDifferenceCount = 0, pageVisitCount = 1, counter = 0;
int ajaxSearchFollowers = 0, ajaxArticleFollowers = 0;
String page, id, query2;
Date date;
int usersLeftSite = 0, usersReturned = 0;
float averageSessionLength = 0F;
float averagePagesVisited = 0F;
while (sessions.next()) {
page = sessions.getString("page");
query2 = sessions.getString("query2");
if ((query2 != null) && (query2.equals("y"))) {
if (page.equals("SearchResult.jsp")) {
ajaxSearchFollowers++;
}
if (page.equals("Article.jsp")) {
ajaxArticleFollowers++;
}
}
id = sessions.getString("stats_id");
date = sdf.parse(sessions.getString("theDate"));
if (!(id.equals(currentSessionId))) {
// if we've been through here before:
if (currentSessionId.length() > 0) {
long timeDifference = sessionEnd.getTime()
- sessionStart.getTime();
visitLengths.add(new Long(timeDifference));
if (timeDifference > 0) {
totalDifferenceCount++;
totalDifference += timeDifference;
}
counter++;
averagePagesVisited = ((averagePagesVisited * (counter - 1)) + pageVisitCount)
/ counter;
}
// init for every time, including fresh start:
sessionStart = date;
sessionEnd = (Date) sessionStart.clone();
currentSessionId = id;
leftSite = false;
pageVisitCount = 1;
} else {
if ((page != null) && (page.indexOf("http://") == -1)) {
pageVisitCount++;
}
sessionEnd = date;
if ((page != null) && (page.indexOf("http://") != -1)) {
extLinkExits++;
leftSite = true;
} else if (leftSite) {
extLinkReturns++;
leftSite = false;
}
}
}
// repeat this little bit from inside the loop to make sure we're
// including the last session
counter++;
averagePagesVisited = ((averagePagesVisited * (counter - 1)) + pageVisitCount)
/ counter;
long timeDifference = sessionEnd.getTime() - sessionStart.getTime();
visitLengths.add(new Long(timeDifference));
if (timeDifference > 0) {
totalDifferenceCount++;
totalDifference += timeDifference;
}
// this part is padding, because for every session in the system
// we don't know the length of visit for the final page selected.
// to remedy that, we average out all of the time differences
// between
// pages as we go, then add it to every visit for a very reasonable
// estimate of what the final visit length might have been.
if (totalDifferenceCount > 0) {
Long toAddTo;
float averageDifference = totalDifference
/ totalDifferenceCount;
ArrayList<Long> newVisitLengths = new ArrayList<Long>();
for (int m = 0; m < visitLengths.size(); m++) {
toAddTo = (Long) visitLengths.get(m);
toAddTo = new Long(toAddTo.longValue()
+ ((long) averageDifference));
newVisitLengths.add(toAddTo);
}
visitLengths = newVisitLengths;
}
usersLeftSite = extLinkExits;
usersReturned = extLinkReturns;
Long length;
float totalLength = 0F;
for (int m = 0; m < visitLengths.size(); m++) {
length = (Long) visitLengths.get(m);
totalLength += length.longValue();
}
averageSessionLength = totalLength / visitLengths.size();
result = new MiscellaneousDataHolder(usersLeftSite, usersReturned,
averageSessionLength, averagePagesVisited, visitLengths
.size(), 0, ajaxSearchFollowers,
ajaxArticleFollowers);
} catch (SQLException sqle) {
Logger.error("StatsDAO getSessionData failure: " + sqle.toString());
sqle.printStackTrace();
throw new SQLException(sqle.getMessage());
} catch (ParseException e) {
Logger.error("StatsDAO getSessionData failure: " + e.toString());
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (sessions != null) {
try {
sessions.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
Configuration.getConnectionPool().returnConnection(conn);
}
result.setReturningUsers(getReturningUsers(start, end, excludeBots));
return result;
}