JILS-34: Show time logged on Version Pages

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.

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.

It's a fairly simple change to the query. The issue list is generated with

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 ;

We want to ensure that an association is of a specific type, by default, JIRA doesn't have too many to choose from

mysql> select distinct ASSOCIATION_TYPE from nodeassociation;
+------------------+
| ASSOCIATION_TYPE |
+------------------+
| IssueFixVersion  |
| ProjectScheme    |
| ProjectCategory  |
| IssueComponent   |
| IssueVersion     |
+------------------+

So our selection query should be

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 ;

But, we're only actually interested in the time logged, so that can be reduced down to

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 ;

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).

Re-opening to assign to a version

Assigning to v0.01b

Re-Closing