/*
* Copyright (C) 2004-2008 Jive Software. All rights reserved.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.jivesoftware.openfire.fastpath.history;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.EmailService;
import org.jivesoftware.util.StringUtils;
import org.jivesoftware.util.XMPPDateTimeFormat;
import org.jivesoftware.xmpp.workgroup.DbProperties;
import org.jivesoftware.xmpp.workgroup.Workgroup;
import org.jivesoftware.xmpp.workgroup.WorkgroupManager;
import org.jivesoftware.xmpp.workgroup.request.Request;
import org.jivesoftware.xmpp.workgroup.utils.ModelUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;
/**
* Utility class to control the update and retrieval of Chat Transcripts within the
* Fastpath system.
*
* @author Derek DeMoro
*/
public class ChatTranscriptManager {
private static final Logger Log = LoggerFactory.getLogger(ChatTranscriptManager.class);
private static final String GET_WORKGROUP_SESSIONS =
"SELECT sessionID, userID, startTime, endTime, queueWaitTime, state " +
"FROM fpSession WHERE workgroupID=? AND startTime>=? AND endTime<=?";
private static final String GET_SESSION_TRANSCRIPT =
"SELECT transcript FROM fpSession WHERE sessionID=?";
private static final String GET_SESSION =
"SELECT userID, workgroupID, transcript, startTime, endTime, queueWaitTime, state " +
"FROM fpSession WHERE sessionID=?";
private static final String GET_SESSION_AGENTS =
"SELECT agentJID, joinTime, leftTime FROM fpAgentSession WHERE sessionID=?";
private static final String GET_SESSION_META_DATA =
"SELECT metadataName, metadataValue FROM fpSessionMetadata WHERE sessionID=?";
private ChatTranscriptManager() {
}
/**
* Returns a collection of ChatSessions for a particular workgroup.
*
* @param workgroup the workgroup.
* @param start retrieve all ChatSessions at or after this specified date.
* @param end retrieve all ChatSessions before or on this specified date.
* @return a collection of ChatSessions.
*/
public static Collection<ChatSession> getChatSessionsForWorkgroup(Workgroup workgroup,
Date start, Date end)
{
final List<ChatSession> resultList = new ArrayList<ChatSession>();
long wgID = workgroup.getID();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_WORKGROUP_SESSIONS);
pstmt.setLong(1, wgID);
pstmt.setString(2, StringUtils.dateToMillis(start));
pstmt.setString(3, StringUtils.dateToMillis(end));
rs = pstmt.executeQuery();
while (rs.next()) {
String sessionID = rs.getString(1);
String userID = rs.getString(2);
String startTime = rs.getString(3);
String endTime = rs.getString(4);
long queueWaitTime = rs.getLong(5);
int state = rs.getInt(6);
ChatSession session = new ChatSession();
session.setSessionID(sessionID);
session.setUserID(userID);
session.setWorkgroupID(wgID);
if (startTime.trim().length() > 0) {
session.setStartTime(Long.parseLong(startTime));
}
if (endTime.trim().length() > 0) {
session.setEndTime(Long.parseLong(endTime));
}
session.setQueueWaitTime(queueWaitTime);
session.setState(state);
populateSessionWithMetadata(session);
populateSessionWithAgents(session);
resultList.add(session);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
// Sort by date
Collections.sort(resultList, dateComparator);
return resultList;
}
/**
* Return the plain text version of a chat transcript.
*
* @param sessionID the sessionID of the <code>ChatSession</code>
* @return the plain text version of a chat transcript.
*/
public static String getTextTranscriptFromSessionID(String sessionID) {
String transcript = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_TRANSCRIPT);
pstmt.setString(1, sessionID);
rs = pstmt.executeQuery();
if (rs.next()) {
transcript = DbConnectionManager.getLargeTextField(rs, 1);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
if (transcript == null || "".equals(transcript)) {
return "";
}
// Define time zone used in the transcript.
SimpleDateFormat UTC_FORMAT = new SimpleDateFormat(XMPPDateTimeFormat.XMPP_DELAY_DATETIME_FORMAT);
UTC_FORMAT.setTimeZone(TimeZone.getTimeZone("UTC"));
final SimpleDateFormat formatter = new SimpleDateFormat("h:mm a");
Document element = null;
try {
element = DocumentHelper.parseText(transcript);
}
catch (DocumentException e) {
Log.error(e.getMessage(), e);
}
StringBuilder buf = new StringBuilder();
// Add the Messages and Presences contained in the retrieved transcript element
for (Iterator<Element> it = element.getRootElement().elementIterator(); it.hasNext();) {
Element packet = it.next();
String name = packet.getName();
String message = "";
String from = "";
if ("presence".equals(name)) {
String type = packet.attributeValue("type");
from = new JID(packet.attributeValue("from")).getResource();
if (type == null) {
message = from + " has joined the room";
}
else {
message = from + " has left the room";
}
}
else if ("message".equals(name)) {
from = new JID(packet.attributeValue("from")).getResource();
message = packet.elementText("body");
message = StringUtils.escapeHTMLTags(message);
}
List<Element> el = packet.elements("x");
for (Element ele : el) {
if ("jabber:x:delay".equals(ele.getNamespaceURI())) {
String stamp = ele.attributeValue("stamp");
try {
String formattedDate;
synchronized (UTC_FORMAT) {
Date d = UTC_FORMAT.parse(stamp);
formattedDate = formatter.format(d);
}
if ("presence".equals(name)) {
buf.append("[").append(formattedDate).append("] ").append(message)
.append("\n");
}
else {
buf.append("[").append(formattedDate).append("] ").append(from)
.append(": ").append(message).append("\n");
}
}
catch (ParseException e) {
Log.error(e.getMessage(), e);
}
}
}
}
return buf.toString();
}
/**
* Retrieves a <code>ChatSession</code> based on it's session ID.
*
* @param sessionID the session ID.
* @return the ChatSession or null if no ChatSession is found.
*/
public static ChatSession getChatSession(String sessionID) {
final ChatSession session = new ChatSession();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION);
pstmt.setString(1, sessionID);
rs = pstmt.executeQuery();
while (rs.next()) {
String userID = rs.getString(1);
long workgroupID = rs.getLong(2);
String transcript = DbConnectionManager.getLargeTextField(rs, 3);
String startTime = rs.getString(4);
String endTime = rs.getString(5);
long queueWaitTime = rs.getLong(6);
int state = rs.getInt(7);
session.setSessionID(sessionID);
session.setWorkgroupID(workgroupID);
session.setUserID(userID);
session.setTranscript(formatTranscript(transcript));
if (startTime.trim().length() > 0) {
session.setStartTime(Long.parseLong(startTime));
}
if (endTime.trim().length() > 0) {
session.setEndTime(Long.parseLong(endTime));
}
session.setQueueWaitTime(queueWaitTime);
session.setState(state);
if (startTime.trim().length() > 0 && endTime.trim().length() > 0) {
populateSessionWithMetadata(session);
populateSessionWithAgents(session);
}
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return session;
}
/**
* Adds all metadata associated with a <code>ChatSession</code>
*
* @param session the ChatSession.
*/
public static void populateSessionWithMetadata(ChatSession session) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_META_DATA);
pstmt.setString(1, session.getSessionID());
rs = pstmt.executeQuery();
Map<String, List<String>> metadata = new HashMap<String, List<String>>();
while (rs.next()) {
String name = rs.getString(1);
String value = rs.getString(2);
metadata.put(name, Request.decodeMetadataValue(value));
}
session.setMetadata(metadata);
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
}
/**
* Adds all participating agents to a <code>ChatSession</code>
*
* @param session the ChatSession.
*/
public static void populateSessionWithAgents(ChatSession session) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(GET_SESSION_AGENTS);
pstmt.setString(1, session.getSessionID());
rs = pstmt.executeQuery();
while (rs.next()) {
String agentJID = rs.getString(1);
String joinTime = rs.getString(2);
String endTime = rs.getString(3);
long start = -1;
if (joinTime != null && joinTime.trim().length() > 0) {
start = Long.parseLong(joinTime);
}
long end = -1;
if (endTime != null && endTime.trim().length() > 0) {
end = Long.parseLong(endTime);
}
AgentChatSession agentSession = new AgentChatSession(agentJID, start, end);
session.addAgent(agentSession);
}
}
catch (Exception ex) {
Log.error(ex.getMessage(), ex);
}
finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
}
/**
* Formats a given XML Chat Transcript.
*
* @param transcript the XMP ChatTranscript.
* @return the pretty-version of a transcript.
*/
public static String formatTranscript(String transcript) {
if (transcript == null || "".equals(transcript)) {
return "";
}
final SimpleDateFormat UTC_FORMAT = new SimpleDateFormat(XMPPDateTimeFormat.XMPP_DELAY_DATETIME_FORMAT);
UTC_FORMAT.setTimeZone(TimeZone.getTimeZone("UTC"));
final SimpleDateFormat formatter = new SimpleDateFormat("h:mm a");
Document element = null;
try {
element = DocumentHelper.parseText(transcript);
}
catch (DocumentException e) {
Log.error(e.getMessage(), e);
}
StringBuilder buf = new StringBuilder();
String conv1 = null;
// Add the Messages and Presences contained in the retrieved transcript element
for (Iterator<Element> it = element.getRootElement().elementIterator(); it.hasNext();) {
Element packet = it.next();
String name = packet.getName();
String message = "";
String from = "";
if ("presence".equals(name)) {
String type = packet.attributeValue("type");
from = new JID(packet.attributeValue("from")).getResource();
if (type == null) {
message = from + " has joined the room";
}
else {
message = from + " has left the room";
}
}
else if ("message".equals(name)) {
from = new JID(packet.attributeValue("from")).getResource();
message = packet.elementText("body");
message = StringUtils.escapeHTMLTags(message);
if(conv1 == null){
conv1 = from;
}
}
List<Element> el = packet.elements("x");
for (Element ele : el) {
if ("jabber:x:delay".equals(ele.getNamespaceURI())) {
String stamp = ele.attributeValue("stamp");
try {
String formattedDate;
synchronized (UTC_FORMAT) {
Date d = UTC_FORMAT.parse(stamp);
formattedDate = formatter.format(d);
}
if ("presence".equals(name)) {
buf.append(
"<tr valign=\"top\"><td class=\"notification-label\" colspan=2 nowrap>[")
.append(formattedDate).append("] ").append(message)
.append("</td></tr>");
}
else {
String cssClass = conv1.equals(from) ? "conversation-label1" : "conversation-label2";
buf.append(
"<tr valign=\"top\"><td width=1% class=\""+cssClass+"\" nowrap>[")
.append(formattedDate).append("] ").append(from)
.append(":</td><td class=\"conversation-body\">").append(message).append("</td></tr>");
}
}
catch (ParseException e) {
Log.error(e.getMessage(), e);
}
}
}
}
return buf.toString();
}
/**
* Sends a transcript mapped by it's sessionID.
*
* @param sessionID the sessionID of the Chat.
* @param from specify who the email is from.
* @param to specify the email address of the person to receive the email.
* @param body specify header text to use in the email.
* @param subject specify the subject of this email.
*/
public static void sendTranscriptByMail(String sessionID, String from, String to, String body, String subject) {
final ChatSession chatSession = getChatSession(sessionID);
if (chatSession != null) {
final StringBuilder builder = new StringBuilder();
String transcript = chatSession.getTranscript();
if (ModelUtil.hasLength(body)) {
builder.append(body);
}
builder.append("<br/>");
builder.append("<table>").append(transcript).append("</table>");
EmailService emailService = EmailService.getInstance();
emailService.sendMessage(null, to, null, from, subject, null, builder.toString());
}
}
/**
* Sends a transcript mapped by it's sessionID using the transcript settings.
*
* @param sessionID the sessionID of the Chat.
* @param to the email address to send the transcript to.
*/
public static void sendTranscriptByMail(String sessionID, String to) {
final ChatSession chatSession = getChatSession(sessionID);
final WorkgroupManager workgroupManager = WorkgroupManager.getInstance();
Workgroup workgroup = null;
for (Workgroup wgroup : workgroupManager.getWorkgroups()) {
if (wgroup.getID() == chatSession.getWorkgroupID()) {
workgroup = wgroup;
break;
}
}
// If for some reason, the workgroup could not be found.
if (workgroup == null) {
return;
}
DbProperties props = workgroup.getProperties();
String context = "jive.transcript";
String from = props.getProperty(context + ".from");
if (from == null) {
from = workgroup.getJID().toBareJID();
}
String fromEmail = props.getProperty(context + ".fromEmail");
if (fromEmail == null) {
fromEmail = workgroup.getJID().toBareJID();
}
String subject = props.getProperty(context + ".subject");
if (subject == null) {
subject = "Chat transcript";
}
String message = props.getProperty(context + ".message");
if (message == null) {
message = "";
}
if (chatSession != null) {
final StringBuilder builder = new StringBuilder();
String transcript = chatSession.getTranscript();
if (ModelUtil.hasLength(message)) {
builder.append(message);
}
builder.append("<br/>");
builder.append("<table>").append(transcript).append("</table>");
EmailService emailService = EmailService.getInstance();
emailService.sendMessage(null, to, from, fromEmail, subject, null, builder.toString());
}
}
/**
* Sorts ChatSessions by date.
*/
static final Comparator<ChatSession> dateComparator = new Comparator<ChatSession>() {
public int compare(ChatSession item1, ChatSession item2) {
float int1 = item1.getStartTime();
float int2 = item2.getStartTime();
if (int1 == int2) {
return 0;
}
if (int1 > int2) {
return -1;
}
if (int1 < int2) {
return 1;
}
return 0;
}
};
}