########################################################################################## JILS-34: Show time logged on Version Pages ########################################################################################## Issue Type: New Feature ----------------------------------------------------------------------------------------- Issue Information ==================== Priority: Major Status: Closed Resolution: Done (2016-04-29 15:03:20) Project: Jira Issue Listing Script (JILS) Reported By: btasker Assigned To: btasker Affected Versions: - 0.01b Targeted for fix in version: - 0.01b Time Estimate: 5 minutes Time Logged: 35 minutes ----------------------------------------------------------------------------------------- Issue Description ================== Project pages include a line showing the total amount of time logged against a specific project. For larger projects, billing/invoicing may be done on a per-version basis, so it'd be helpful if the version pages also included an indication of how much time had been recorded against that version ----------------------------------------------------------------------------------------- Activity ========== ----------------------------------------------------------------------------------------- 2015-09-23 13:38:40 ----------------------------------------------------------------------------------------- btasker changed status from 'Open' to 'In Progress' ----------------------------------------------------------------------------------------- 2015-09-23 13:40:58 btasker ----------------------------------------------------------------------------------------- Ideally, we need to use the 'FIXFOR' field when selecting issues related to the current version, so that we're only totting up time for issues that being fixed in the version (rather than those marked as affecting this version, but are being fixed in a later version). I _think_ the query used to select the issue listing for these pages already does that, but will need to double check. ----------------------------------------------------------------------------------------- 2015-09-23 13:48:42 btasker ----------------------------------------------------------------------------------------- Nope the listing will show any issues marked as affecting, or 'Fix For'. Probably need to decide whether that behaviour's desirable for the versions page. Whilst it'd be handy to see what issues affect a version, if an issue is marked as Fixed, you'd have to go into the issue to see whether it was actually fixed in the version you're viewing. It might be better to create a 'Known Issues' page (or even just section on the versions page) for each version, listing all issues that are marked as affecting the current version, but haven't been fixed in that version (either because they're unresolved, or because the fix was made in a later version). Have created an issue (JILS-36) to explore doing that. For now, will build the query needed to extract time only from the issues with the currently viewed version in FIXFOR. ----------------------------------------------------------------------------------------- 2015-09-23 13:56:13 btasker ----------------------------------------------------------------------------------------- It's a fairly simple change to the query. The issue list is generated with -- BEGIN SNIPPET -- SELECT DISTINCT a.ID, a.SUMMARY, a.issuenum, a.REPORTER, b.pname, b.pkey, c.pname as status, d.pname as resolution, e.pname as issuetype, f.pname as priority, a.CREATED, a.RESOLUTIONDATE, a.TIMESPENT, f.SEQUENCE as ptysequence, a.ASSIGNEE FROM projectversion AS pv LEFT JOIN nodeassociation as na ON pv.ID = na.SINK_NODE_ID LEFT JOIN jiraissue AS a ON na.SOURCE_NODE_ID = a.ID LEFT JOIN project AS b on a.PROJECT = b.ID LEFT JOIN issuestatus AS c ON a.issuestatus = c.id LEFT JOIN resolution AS d ON a.RESOLUTION = d.ID LEFT JOIN issuetype AS e ON a.issuetype = e.ID LEFT JOIN priority AS f ON a.PRIORITY = f.ID WHERE pv.ID='10603' AND b.pkey='TESTPROJ' ORDER BY a.PROJECT, a.issuenum ASC ; -- END SNIPPET -- We want to ensure that an association is of a specific type, by default, JIRA doesn't have too many to choose from -- BEGIN SNIPPET -- mysql> select distinct ASSOCIATION_TYPE from nodeassociation; +------------------+ | ASSOCIATION_TYPE | +------------------+ | IssueFixVersion | | ProjectScheme | | ProjectCategory | | IssueComponent | | IssueVersion | +------------------+ -- END SNIPPET -- So our selection query should be -- BEGIN SNIPPET -- SELECT DISTINCT a.ID, a.SUMMARY, a.issuenum, a.REPORTER, b.pname, b.pkey, c.pname as status, d.pname as resolution, e.pname as issuetype, f.pname as priority, a.CREATED, a.RESOLUTIONDATE, a.TIMESPENT, f.SEQUENCE as ptysequence, a.ASSIGNEE FROM projectversion AS pv LEFT JOIN nodeassociation as na ON pv.ID = na.SINK_NODE_ID LEFT JOIN jiraissue AS a ON na.SOURCE_NODE_ID = a.ID LEFT JOIN project AS b on a.PROJECT = b.ID LEFT JOIN issuestatus AS c ON a.issuestatus = c.id LEFT JOIN resolution AS d ON a.RESOLUTION = d.ID LEFT JOIN issuetype AS e ON a.issuetype = e.ID LEFT JOIN priority AS f ON a.PRIORITY = f.ID WHERE pv.ID='10604' AND b.pkey='TESTPROJ' AND na.ASSOCIATION_TYPE='IssueFixVersion' ORDER BY a.PROJECT, a.issuenum ASC ; -- END SNIPPET -- But, we're only actually interested in the time logged, so that can be reduced down to -- BEGIN SNIPPET -- SELECT SUM(a.TIMESPENT) as TIMESPENT, SUM(a.TIMEORIGINALESTIMATE) as estimate FROM projectversion AS pv LEFT JOIN nodeassociation as na ON pv.ID = na.SINK_NODE_ID LEFT JOIN jiraissue AS a ON na.SOURCE_NODE_ID = a.ID LEFT JOIN project AS b on a.PROJECT = b.ID WHERE pv.ID='10604' AND b.pkey='TESTPROJ' AND na.ASSOCIATION_TYPE='IssueFixVersion' ORDER BY a.PROJECT, a.issuenum ASC ; -- END SNIPPET -- ----------------------------------------------------------------------------------------- 2015-09-23 14:13:56 btasker ----------------------------------------------------------------------------------------- This is now implemented, though it might be worth adding something to the output that provides an explanation of what that time takes into account (even if it's just a link back to a comment on this issue). ----------------------------------------------------------------------------------------- 2015-09-23 14:14:07 ----------------------------------------------------------------------------------------- btasker changed status from 'In Progress' to 'Open' ----------------------------------------------------------------------------------------- 2015-09-23 14:14:29 ----------------------------------------------------------------------------------------- btasker changed timespent from '0 minutes' to '35 minutes' ----------------------------------------------------------------------------------------- 2015-09-23 14:14:56 git ----------------------------------------------------------------------------------------- -- BEGIN QUOTE -- Repo: Jira-Issue-Listing Commit: a9fd2ae49ed5623576d056049aba7e35fac12cf0 Author: Ben Tasker