package org.jivesoftware.xmpp.workgroup.spi;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.user.UserNotFoundException;
import org.jivesoftware.util.StringUtils;
import org.jivesoftware.xmpp.workgroup.RequestQueue;
import org.jivesoftware.xmpp.workgroup.Workgroup;
import org.jivesoftware.xmpp.workgroup.WorkgroupManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;
/**
* <code>ChatHistoryImpl</code> class is used for statistical reporting of
* the Live Assistant Server.
*
* @author Derek DeMoro
*/
public final class ChatHistoryUtils {
private static final Logger Log = LoggerFactory.getLogger(ChatHistoryUtils.class);
private static final String ALL_SESSION_TIMES =
"SELECT startTime, endTime FROM fpSession";
private static final String ACCEPTED_CHATS_COUNT =
"SELECT count(*) FROM fpSession WHERE state=2 AND workgroupID=? " +
"AND startTime >= ? AND endTime <= ?";
private static final String CHAT_TIMES_FOR_WORKGROUPS =
"SELECT startTime, endTime FROM fpSession WHERE workgroupID=?";
private static final String WORKGROUP_REQUEST_COUNT =
"SELECT count(*) FROM fpSession WHERE workgroupID=? AND startTime >= ? " +
"AND endTime <= ?";
private static final String WORKGROUP_STATE_REQUEST_COUNT =
"SELECT count(*) FROM fpSession where workgroupID=? AND state=? AND startTime >= ? " +
"AND endTime <= ?";
private static final String ALL_SESSIONS =
"SELECT sessionID FROM fpSession";
private static final String ALL_CHATS_COUNT =
"SELECT count(*) FROM fpSession WHERE state=2";
private static final String ALL_REQUESTS_COUNT =
"SELECT count(*) FROM fpSession";
private static final String TOTAL_WAIT_TIME =
"SELECT sum(queueWaitTime) FROM fpSession";
private static final String WORKGROUP_WAIT_TIME =
"SELECT sum(queueWaitTime) FROM fpSession WHERE workgroupID=? AND startTime >= ? " +
"AND endTime <= ?";
/**
* Creates a new ChatHistoryImpl object.
*/
private ChatHistoryUtils() {
}
/**
* Returns the average chat time for all workgroups in the server.
*
* @return the average time of all chats.
*/
public static long getAverageChatLengthForServer() {
int numberOfChats = getTotalChatsInSystem();
long chatLength = getTotalTimeForAllChatsInServer();
if(numberOfChats == 0 ) {
return 0;
}
return chatLength / numberOfChats;
}
/**
* Returns the total amount of time for all the chats in all workgroups.
*
* @return the total length of all chats in the system.
*/
public static long getTotalTimeForAllChatsInServer() {
int totalWorkgroupChatTime = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ALL_SESSION_TIMES);
rs = pstmt.executeQuery();
while (rs.next()) {
try {
String startTimeString = rs.getString(1);
String endTimeString = rs.getString(2);
if ((startTimeString != null) && (startTimeString.trim().length() > 0) &&
(endTimeString != null) && (endTimeString.trim().length() > 0)) {
long startLong = Long.parseLong(startTimeString);
long endLong = Long.parseLong(endTimeString);
totalWorkgroupChatTime += endLong - startLong;
}
}
catch (SQLException e) {
Log.error(e.getMessage(), e);
}
catch (NumberFormatException e) {
Log.error(e.getMessage(), e);
}
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return totalWorkgroupChatTime;
}
/**
* Returns the number of chat requests that were accepted.
*
* @param workgroupName the name of the workgroup where the request(s) were made.
* @param startDate the start date.
* @param endDate the end date.
* @return the number of chats requests accepted by the workgroup.
*/
public static int getNumberOfChatsAccepted(String workgroupName, Date startDate, Date endDate) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
if (workgroup == null) {
return 0;
}
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ACCEPTED_CHATS_COUNT);
pstmt.setLong(1, workgroup.getID());
pstmt.setString(2, StringUtils.dateToMillis(startDate));
pstmt.setString(3, StringUtils.dateToMillis(endDate));
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Returns the total chat length of an individual workgroup.
*
* @param workgroupName the name of the workgroup.
* @return the total length of all chats in the specified workgroup.
*/
public static long getTotalChatTimeForWorkgroup(String workgroupName) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
int totalWorkgroupChatTime = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(CHAT_TIMES_FOR_WORKGROUPS);
pstmt.setLong(1, workgroup.getID());
rs = pstmt.executeQuery();
while (rs.next()) {
String startTimeString = rs.getString(1);
String endTimeString = rs.getString(2);
if ((startTimeString != null) && (startTimeString.trim().length() > 0) &&
(endTimeString != null) && (endTimeString.trim().length() > 0)) {
long startLong = Long.parseLong(startTimeString);
long endLong = Long.parseLong(endTimeString);
totalWorkgroupChatTime += endLong - startLong;
}
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return totalWorkgroupChatTime;
}
/**
* Returns the number of request made to a workgroup between
* specified dates.
*
* @param workgroupName the workgroup to search
* @param startDate the time to begin the search from.
* @param endDate the time to end the search.
* @return the total number of requests
*/
public static int getNumberOfRequestsForWorkgroup(String workgroupName, Date startDate, Date endDate) {
Workgroup workgroup = getWorkgroup(workgroupName);
if (workgroup == null) {
return 0;
}
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(WORKGROUP_REQUEST_COUNT);
pstmt.setLong(1, workgroup.getID());
pstmt.setString(2, StringUtils.dateToMillis(startDate));
pstmt.setString(3, StringUtils.dateToMillis(endDate));
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Returns the number of canceled requests.
*
* @param workgroupName the workgroup to search
* @param startDate the time to begin the search from.
* @param endDate the time to end the search.
* @return the total number of requests
*/
public static int getNumberOfRequestsCancelledByUser(String workgroupName, Date startDate, Date endDate) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
if (workgroup == null) {
return 0;
}
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(WORKGROUP_STATE_REQUEST_COUNT);
pstmt.setLong(1, workgroup.getID());
// Set the state the cancelled requests.
pstmt.setInt(2, 0);
pstmt.setString(3, StringUtils.dateToMillis(startDate));
pstmt.setString(4, StringUtils.dateToMillis(endDate));
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Returns an iterator of all sessionID's in the system.
*
* @return an iterator of sessionID's.
*/
public static Iterator<String> getSessionIDs() {
final List<String> sessionList = new ArrayList<String>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ALL_SESSIONS);
rs = pstmt.executeQuery();
while (rs.next()) {
sessionList.add(rs.getString(1));
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return sessionList.iterator();
}
/**
* Returns the number of canceled requests.
*
* @param workgroupName the workgroup to search
* @param startDate the time to begin the search from.
* @param endDate the time to end the search.
* @return the total number of requests
*/
public static int getNumberOfRequestsNeverPickedUp(String workgroupName, Date startDate, Date endDate) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
if (workgroup == null) {
return 0;
}
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(WORKGROUP_STATE_REQUEST_COUNT);
pstmt.setLong(1, workgroup.getID());
// Set the state the ignored requests.
pstmt.setInt(2, 1);
pstmt.setString(3, StringUtils.dateToMillis(startDate));
pstmt.setString(4, StringUtils.dateToMillis(endDate));
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Returns the total number of chats that have occured within a workgroup.
*
* @param workgroupName the jid of the workgroup.
* @return the total number of chats that have occured within a workgroup.
*/
public static int getNumberOfChatsForWorkgroup(String workgroupName) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
int count = 0;
for (RequestQueue requestQueue : workgroup.getRequestQueues()){
count += requestQueue.getTotalChatCount();
}
return count;
}
/**
* Returns the average wait time in the system.
*
* @return the average wait time.
*/
public static String getAverageWaitTimeForServer() {
int totalRequests = getTotalRequestCountForSystem();
long totalWaitTime = getTotalWaitTimeForServer();
if (totalRequests == 0 ) {
return "0 sec.";
}
long averageWaitTime = totalWaitTime / totalRequests;
return getDateFromLong(averageWaitTime);
}
/**
* Returns the average wait time for a specified workgroup between two dates.
*
* @param workgroupName the Live Assistant Workgroup.
* @param startDate the startDate.
* @param endTime the end date.
* @return the average wait time for this workgroup.
*/
public static long getAverageWaitTimeForWorkgroup(String workgroupName, Date startDate, Date endTime) {
int totalRequests = getNumberOfRequestsForWorkgroup(workgroupName, startDate, endTime);
long waitTime = getTotalWaitTimeForWorkgroup(workgroupName, startDate, endTime);
if (totalRequests == 0) {
return 0;
}
return waitTime / totalRequests;
}
/**
* Returns the total number of chats.
*
* @return the total number of chats.
*/
public static int getTotalChatsInSystem() {
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ALL_CHATS_COUNT);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
}
catch (SQLException e) {
Log.error(e.getMessage(), e);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Retruns the total number of requests.
*
* @return the total number of requests.
*/
public static int getTotalRequestCountForSystem() {
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ALL_REQUESTS_COUNT);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
}
catch (SQLException e) {
Log.error(e.getMessage(), e);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return count;
}
/**
* Returns the total waitTime for all incoming requests.
*
* @return the total wait time.
*/
public static long getTotalWaitTimeForServer() {
int totalWaitTime = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(TOTAL_WAIT_TIME);
rs = pstmt.executeQuery();
rs.next();
totalWaitTime = rs.getInt(1);
}
catch (SQLException e) {
Log.error(e.getMessage(), e);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return totalWaitTime;
}
/**
* Returns the number of canceled requests.
*
* @param workgroupName the workgroup to search
* @param startDate the time to begin the search from.
* @param endDate the time to end the search.
* @return the total number of requests
*/
public static long getTotalWaitTimeForWorkgroup(String workgroupName, Date startDate, Date endDate) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
if (workgroup == null) {
return 0;
}
int waitTime = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(WORKGROUP_WAIT_TIME);
pstmt.setLong(1, workgroup.getID());
// Set the state the ignored requests.
pstmt.setInt(2, 1);
pstmt.setString(2, StringUtils.dateToMillis(startDate));
pstmt.setString(3, StringUtils.dateToMillis(endDate));
rs = pstmt.executeQuery();
rs.next();
waitTime = rs.getInt(1);
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return waitTime;
}
/**
* Returns a formatted string to display minutes and seconds.
*
* @param time the number of milliseconds.
* @return a formatted string.
*/
public static String getDateFromLong(long time) {
int aTime;
int minutes;
int seconds;
String displayString;
if (time > 0) {
aTime = (int) (time) / 1000;
minutes = aTime / 60;
seconds = aTime % 60;
if (minutes != 0) {
displayString = minutes + " min, " + seconds + " sec.";
}
else {
displayString = seconds + " seconds";
}
}
else {
return "0 seconds";
}
return displayString;
}
/**
* Returns a <code>Workgroup</code> based on it's full jid.
* @param workgroupJID the full jid of the workgroup (ex. demo@workgroup.jivesoftware.com)
* @return the Workgroup
*/
public static Workgroup getWorkgroup(String workgroupJID) {
Workgroup workgroup = null;
try {
workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupJID));
}
catch (UserNotFoundException e) {
Log.error("Error retrieving Workgroup", e);
}
return workgroup;
}
}