Changes between Version 1 and Version 2 of PeerReviewPlugin/Reports

Timestamp:

May 27, 2016, 7:29:16 AM (8 years ago)

Author:

Cinc-th

Comment:

Added example reports

PeerReviewPlugin/Reports

@@ -1 +1 @@
 = Reports
-Instead of shipping it's own report module PeerReviewPlugin relies on Tracs reporting features by leveraging custom TracReports using data from PeerReviewPlugin.
-
-These custom reports can be accessed from the regular Trac report page but they are also shown on the report page for reviews.
+Instead of shipping it's own report module PeerReviewPlugin relies on Tracs reporting features by leveraging custom TracReports using data from PeerReviewPlugin. These custom reports can be accessed from the regular Trac report page but they are also shown on the report page for reviews.
 
 Note that when opening a report you are automatically redirected to the Trac report module.
 
+There are no reports created during installation. You may use the ones shown below.
 == Creating Reports
 === Displaying a report on the review report page
@@ -47 +46 @@
 ORDER BY f.path, f.revision
 }}}
+== Example Reports
+=== All reviews grouped by author
+Description:
+{{{
+{{{
+#!comment
+codereview=1
+}}}
+Back to [/peerreviewreport Codereview reports].
+}}}
+
+Query:
+
+{{{#!sql
+SELECT r.owner AS __group__, r.review_id AS id,
+       r.name AS name, r.owner AS owner,
+       r.created AS created, r.project AS project,
+       r.status AS status,
+       (CASE WHEN r.parent_id  > 0 THEN r.parent_id ELSE NULL END) AS followup_from,
+       (CASE r.status WHEN 'approved' THEN 2 
+        WHEN 'disapproved' THEN 1 
+        WHEN 'reviewed' THEN 4 
+        WHEN 'in-review' THEN 5 END) AS __color__,
+       'peerreview' AS _realm
+FROM peerreview as r
+ORDER BY r.owner, r.review_id
+}}}
+
+=== Approved files
+Description:
+{{{
+{{{
+#!comment
+codereview=1
+}}}
+* Show all ''approved'' files sorted by name.
+
+Back to [/peerreviewreport Codereview reports].
+}}}
+
+Query:
+
+{{{#!sql
+SELECT f.file_id AS id, f.review_id AS review_id,
+       f.path AS path, f.hash AS hash,
+       f.revision AS revision, f.status AS status,
+       'peerreviewfile' AS _realm,
+       (SELECT COUNT(*) FROM peerreviewcomment AS c WHERE f.file_id = c.file_id) AS comments 
+FROM peerreviewfile f
+WHERE f.status = 'approved' 
+GROUP BY f.file_id
+ORDER BY f.path, f.revision
+}}}
+
+=== All files with comments
+Description:
+{{{
+{{{
+#!comment
+codereview=1
+}}}
+* Show all files with comments sorted by name.
+
+Back to [/peerreviewreport Codereview reports].
+}}}
+
+Query:
+
+{{{#!sql
+SELECT f.file_id AS id, f.review_id AS review_id,
+       f.path AS path, f.revision AS revision,
+       f.status AS status, COUNT(*) AS Comments,
+       'peerreviewfile' AS _realm,
+       (CASE f.status WHEN 'approved' THEN 2 WHEN 'disapproved' THEN 1 END) AS __color__ 
+FROM peerreviewfile f
+INNER JOIN peerreviewcomment AS c ON f.file_id = c.file_id
+GROUP BY f.file_id
+ORDER BY f.file_id, f.status, f.path, f.revision
+}}}
+
+=== Files with comments - My reviews
+Description:
+{{{
+{{{
+#!comment
+codereview=1
+}}}
+* Show all files with comments from reviews assigned to me sorted by name.
+* Omit '''closed''' reviews
+* Only new files or files which are in-progress. This means no files with status
+ * '''approved'''
+ * '''disapproved'''
+* Mark files with unread comments
+  [[BR]][[BR]]Note: the status ''read/unread'' is set for each user.
+
+Back to [/peerreviewreport Codereview reports].
+}}}
+
+Query:
+
+{{{#!sql
+SELECT f.file_id AS id, f.review_id AS 'review-Id',
+       rev.name AS Reviewname, rev.owner AS reviewauthor,
+       f.path AS path, f.revision AS revision,
+       f.status AS filestatus, COUNT(*) AS comments,
+       (SELECT COUNT(*) FROM peerreviewcomment AS c 
+        WHERE f.file_id = c.file_id) -
+       (SELECT COUNT(*) FROM peerreviewdata AS d 
+        WHERE f.file_id = d.file_id AND owner = $USER) AS unread,
+       'peerreviewfile' as _realm,
+
+       (CASE 
+        WHEN (SELECT COUNT(*) FROM peerreviewcomment AS c 
+        WHERE f.file_id = c.file_id) -
+        (SELECT COUNT(*) FROM peerreviewdata AS d 
+        WHERE f.file_id = d.file_id AND owner = $USER) > 0 THEN 2 END) AS __color__ 
+
+FROM peerreviewfile AS f
+INNER JOIN peerreviewcomment AS c ON f.file_id = c.file_id
+INNER JOIN peerreview AS rev ON f.review_id = rev.review_id
+WHERE (f.status IS NULL OR f.status NOT IN ('approved', 'disapproved')) 
+AND f.review_id in 
+(SELECT a.review_id FROM peerreviewer a JOIN peerreview r 
+ON a.review_id = r.review_id
+WHERE a.reviewer = $USER 
+AND r.status IS NOT 'closed')
+
+GROUP BY f.file_id
+ORDER BY f.file_id, f.status, f.path, f.revision
+}}}