usercomments in reports

Hi,

I've created a new type of usercomment as you recommended http://mango.serotoninsoftware.com/forum/posts/list/120.page

I am including usercomments in reports much the same way you've included events; as a seperate CSV file or an additional table at the bottom.

I'm selecting any usercomments made during the reporting period, such as those made on points and events, as well as my 'chat' type. I see in the reportChart.ftl template there is code there to display usercomments on events. The usercomments are loaded through EventDao.attachRelationalInfo but this isn't called when the events are loaded from the reportInstanceEvents table.

the purpose of the reportInstance tables is so that the data behind the report doesn't disappear if the point or event data is purged right?

Since the template is ready to print out usercomments on events in reports, I was wondering if you are planning on including the usercomments in reports in a future version?

Cheers
C

The reportinstance table does that, yes, but it also allows reports to be generated asynchronously. I.e. when you request a report, you don't have to let your browser wait the potentially long time to have it generated - it's done in the background. Whenever you want to view the report, you get it instantly (or as instantly as possible).

To have your user comments handled similarly, you should create a reportcomments or some such table, and have it populated by the report generation process.

I have added the table:

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

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