I have added the table:
create table reportInstanceUserComments (
reportinstanceId int not null,
typeId int not null,
typeKey int not null,
ts bigint not null,
userid int not null,
commentText varchar(1024) not null)
alter table reportinstanceusercomments add constraint reportInstanceUserCommentsFk1 foreign key (reportInstanceId)
references reportInstances(id) on delete cascade;
And then in ReportDao.java, code to select comments from the userComments table and add to reportInstanceUserComments
+ //Insert the reportInstanceUserComments
+ if (instance.getIncludeUserComments() != ReportVO.USER_COMMENTS_NONE ) {
+ String ucSQL = "insert into reportInstanceUserComments "+
+ " (reportInstanceId,typeId, typeKey, ts, userId, commentText)"+
+ " select "+ instance.getId() +", uc.commentType, uc.typeKey, uc.ts, uc.userId, uc.commentText "+
+ " from userComments uc ";
+
+
+ if (instance.isFromInception() && instance.isToNow())
+ ejt.update(ucSQL, new Object[] {});
+ else if (instance.isFromInception())
+ ejt.update(ucSQL +" where uc.ts<? ",
+ new Object[] {instance.getReportEndTime()});
+ else if (instance.isToNow())
+ ejt.update(ucSQL +" where uc.ts>=? ",
+ new Object[] {instance.getReportStartTime()});
+ else
+ ejt.update(ucSQL +" where uc.ts>=? and uc.ts<? ",
+ new Object[] {instance.getReportStartTime(), instance.getReportEndTime()});
+
+ ejt.update(ucSQL + " join reportInstanceData rid on uc.typeKey=rid.pointValueId where uc.commentType=" + UserComment.TYPE_POINT_VALUE);
+ ejt.update(ucSQL + " join reportInstanceEvents rie on uc.typeKey=rie.eventId where uc.commentType=" + UserComment.TYPE_EVENT);
+
+
+ }
+
// If the report had undefined start or end times, update them with values from the data.
if (instance.isFromInception() || instance.isToNow()) {
and then to select the comments from the reportInstanceUserComments table for inclusion when the report is being displayed:
public List<EventInstance> getReportInstanceEvents(int instanceId) {
- return query(EVENT_SELECT, new Object[] {instanceId}, new EventDao.EventInstanceRowMapper());
- }
+ List<EventInstance> results = query(EVENT_SELECT, new Object[] {instanceId}, new EventDao.EventInstanceRowMapper());
+ attachEventRelationalInfo(results, instanceId);
+ return results;
+ }
+
+ private void attachEventRelationalInfo(List<EventInstance> list, int instanceId) {
+ for (EventInstance e : list)
+ attachEventRelationalInfo(e, instanceId);
+ }
+
+ private void attachEventRelationalInfo(EventInstance event, int instanceId) {
+ event.setEventComments(query(
+ ReportUserCommentRowMapper.USER_COMMENT_SELECT +
+ " and typeId=" + UserComment.TYPE_EVENT +
+ " and typeKey=?", new Object[] {instanceId, event.getId()}, new UserCommentRowMapper()));
+ }
+
+ public List<ReportUserComment> getReportInstanceUserComments(int instanceId) {
+ return query(
+ ReportUserCommentRowMapper.USER_COMMENT_SELECT +
+ " order by ts asc", new Object[] {instanceId}, new ReportUserCommentRowMapper());
+ }
+
+ class ReportUserCommentRowMapper implements GenericRowMapper<ReportUserComment> {
+ public static final String USER_COMMENT_SELECT =
+ "select distinct uc.userId, u.username, uc.ts, uc.commentText, uc.typeId, uc.typeKey "+
+ "from reportInstanceUserComments uc join users u on uc.userId = u.id "+
+ "where reportInstanceId=? ";
+
+ public ReportUserComment mapRow(ResultSet rs, int rowNum) throws SQLException {
+ ReportUserComment c = new ReportUserComment();
+ c.setUserId(rs.getInt(1));
+ c.setUsername(rs.getString(2));
+ c.setTs(rs.getLong(3));
+ c.setComment(rs.getString(4));
+ c.setTypeId(rs.getInt(5));
+ c.setTypeKey(rs.getInt(6));
+
+ if (c.getTypeId() == UserComment.TYPE_POINT) {
+ DataPointDao dpdao = new DataPointDao();
+ DataPointVO dp = dpdao.getDataPoint(c.getTypeKey());
+ c.setTarget(dp.getName());
+ }
+ if (c.getTypeId() == UserComment.TYPE_EVENT) {
+ EventDao edao = new EventDao();
+ EventInstance e = edao.getEventInstance(c.getTypeKey());
+ LocalizableMessage message = e.getMessage();
+ ResourceBundle bundle = Common.getBundle();
+ String eventMessage = message.getLocalizedMessage(bundle);
+ c.setTarget(eventMessage);
+ }
+
+ if (c.getTypeId() == UserComment.TYPE_CHAT) {
+ c.setTarget("User Log");
+ }
+
+ if (c.getTypeId() == UserComment.TYPE_POINT_VALUE) {
+ PointValueDao pvDao = new PointValueDao();
+ PointValueTime pvt = pvDao.getPointValue(c.getTypeKey());
+ DataPointVO pointVO = pvDao.getPointVO(c.getTypeKey());
+ if (pointVO != null) {
+ String pointName = pointVO.getExtendedName();
+ String renderedValue = pointVO.getTextRenderer().getText(pvt, TextRenderer.HINT_FULL);
+ c.setTarget(pointName + ": " + renderedValue + " at " + DateFunctions.getTime(pvt.getTime()));
+ } else {
+ c.setTarget(pvt.getValue().toString() + " at " +DateFunctions.getTime(pvt.getTime()));
+ }
+ }
+ return c;
+ }
+ }
+
and a class to represent comments in the report
+public class ReportUserComment extends UserComment{
+
+
+ private int typeId;
+ private int typeKey;
+ private String target;
+
+ public int getTypeId() {
+ return typeId;
+ }
+
+ public void setTypeId(int typeId) {
+ this.typeId = typeId;
+ }
+
+ public int getTypeKey() {
+ return typeKey;
+ }
+
+ public void setTypeKey(int typeKey) {
+ this.typeKey = typeKey;
+ }
+
+ public String getTarget() {
+ return target;
+ }
+
+ public void setTarget(String target) {
+ this.target = target;
+ }
+
+}
Seems to be working well enough, comments made in the chat, on events, on points, or on pointvalues are included in reports.
The complete patch is http://216.235.5.146/apache2-default/reportscope-2009-09-20.zip