WITH

sprintparam AS (select  null sprint ::integer AS id),

defaultsprint AS (select "ID" AS id from "AO_60DB71_SPRINT" ORDER BY "ID" DESC LIMIT 1),

sprintid AS (SELECT COALESCE(sprintparam.id, defaultsprint.id) AS id from sprintparam CROSS JOIN defaultsprint),

props AS (select propertyvalue AS baseurl from propertyentry pe JOIN propertystring ps ON pe.id=ps.id where pe.property_key='jira.baseurl'),

jiraissue_compat AS (SELECT (project.pkey::text || '-'::text) || ji.issuenum AS pkey, ji.id, ji.issuetype, ji.summary, ji.timespent, ji.timeestimate, ji.timeoriginalestimate, ji.resolution, ji.assignee FROM jiraissue ji JOIN project ON ji.project=project.id),

parentissuenames AS (

select encodehtml(ji.pkey) as key

from jiraissue_compat ji

JOIN (select * from issuetype  where coalesce(pstyle, '')!='jira_subtask') AS issuetype ON ji.issuetype=issuetype.id

JOIN customfieldvalue cfv on cfv.issue = ji.id and cfv.customfield = (select id from customfield where cfname = 'Sprint')

JOIN sprintid on cfv.stringvalue = cast(sprintid.id as varchar)

),

parentissues AS (

SELECT

null::varchar AS parentkey,

null::varchar AS parentsummary,

null::varchar AS parentassignee,

jiraissue_compat.*

FROM jiraissue_compat

JOIN parentissuenames ON jiraissue_compat.pkey = parentissuenames.key

),

subissues AS (

SELECT parentissues.pkey AS parentkey,

parentissues.summary AS parentsummary,

parentissues.assignee AS parentassignee,

subs.*

FROM parentissues, jiraissue_compat subs, issuelink l, issuelinktype lt

WHERE parentissues.id = l.source AND subs.id = l.destination AND l.linktype=lt.id AND lt.linkname = 'jira_subtask_link'

),

issues AS (

SELECT i.*, issuetype.pname AS itype, issuetype.iconurl AS issuetypeurl,  props.baseurl,

round((CASE i.timespent WHEN 0 THEN NULL::numeric ELSE i.timespent END)/60.0/60, 2) || 'h' AS worklogged,

round((CASE i.timeestimate WHEN 0 THEN NULL::numeric ELSE i.timeestimate END)/60.0/60, 2) || 'h' AS timeest,

round((CASE i.timeoriginalestimate WHEN 0 THEN NULL::numeric ELSE i.timeoriginalestimate END)/60.0/60, 2) || 'h' AS timeorigest

FROM

((SELECT * from parentissues) UNION (select * from subissues)) AS i

JOIN

issuetype ON i.issuetype=issuetype.id

CROSS JOIN props

ORDER BY coalesce(assignee, '') || coalesce(parentkey, '') || pkey

),

renderedissues AS (

select *,

CASE WHEN parentkey is null THEN '' ELSE '^{<a title="' || encodehtml(coalesce(parentsummary,'')) || coalesce(CASE WHEN parentassignee != assignee THEN ' assigned to ' || coalesce(parentassignee, 'nobody') END,'') || '" href="' || baseurl || '/browse/' || parentkey || '">' || regexp_replace(parentkey, '.*-', '''') || '</a>}↳' END ||

CASE WHEN resolution is not null THEN '<strike>' ELSE ''  END ||

'<a href="' || baseurl || '/browse/' || pkey || '" target="_blank"><ac:image><ri:url ri:value="' || baseurl || encodehtml(issuetypeurl) || '"/></ac:image>' || pkey || ' - ' || encodehtml(summary) || '</a>' ||

CASE WHEN resolution is not null THEN '</strike>' ELSE '' END ||

coalesce('<sup title="Time Spent vs. Original Estimate"> ' || coalesce(CASE WHEN timespent > timeoriginalestimate THEN '<font color="red">' ELSE '<font>' END || worklogged || ' logged of ' || '</font>' , '<font color="lightgrey">0h logged of</font> ') || timeorigest || '</sup>', '') as issuelink

FROM issues

),

users AS (

SELECT DISTINCT app_user.user_key, cwd_user.*

FROM cwd_user JOIN cwd_membership ON cwd_user.id = cwd_membership.child_id

JOIN app_user ON app_user.lower_user_name = cwd_user.lower_user_name

WHERE EXISTS (select * from cwd_membership where child_name=cwd_user.user_name AND parent_name in ('jira-users'))

),

capacitykey AS (

SELECT ('capacity for sprint id ' || sprintid.id)::text AS key from sprintid

),

capacityprops AS (

        SELECT users.user_key,

        propertyentry.property_key AS key,

        propertystring.propertyvalue AS capacity

        FROM users

      JOIN app_user ON users.user_key = app_user.user_key

   JOIN propertyentry ON app_user.id = propertyentry.entity_id

   JOIN propertystring ON propertyentry.id = propertystring.id

  WHERE propertyentry.entity_name = 'ApplicationUser' AND propertyentry.property_key like 'jira.meta.%capacity%' AND propertyentry.propertytype = 5::numeric

),

usercapacities AS (

SELECT users.user_key, substring(cp1.capacity, '[\d\.]+') AS defaultcapacity, substring(cp2.capacity, '[\d\.]+') AS capacity

FROM users

LEFT OUTER JOIN capacityprops cp1 ON (users.user_key=cp1.user_key AND cp1.key='jira.meta.default capacity')

LEFT OUTER JOIN capacityprops cp2 ON (users.user_key=cp2.user_key AND cp2.key = 'jira.meta.capacity for sprint id ' || (select id from sprintid))

),

capacitylink AS (

select users.user_key, capacity,

COALESCE(

capacity || 'h ^{<a href="' || props.baseurl || '/secure/admin/user/EditUserProperty.jspa?name=' || users.user_name || '&key=' || capacitykey.key || '" target="_blank">(edit)</a>}',

'<a href="' || props.baseurl || '/secure/admin/user/EditUserProperties.jspa?name=' || users.user_name || coalesce('&value='||defaultcapacity,'') || '&key=' || capacitykey.key || '" target="_blank">(set capacity)</a>'

) AS capacitylink

FROM users LEFT JOIN usercapacities ON users.user_key=usercapacities.user_key,

capacitykey CROSS JOIN props

),

totals AS (

select display_name,

capacitylink.capacitylink,

string_agg(issuelink, '<br/>') AS assignedissues,

round( (sum(timeoriginalestimate))/60.0/60, 2) || 'h' as sumoriginalestimates,

round( (sum(timeestimate))/60.0/60, 2) || 'h' as sumremainingestimates,

round( (sum(timespent))/60.0/60, 2) || 'h' as sumtimespent,

'Work logged: ' || string_agg(round(timespent/60.0/60, 2) || 'h on ' || pkey, ', ') as sumtimespentexp,

CASE WHEN capacitylink.capacity::numeric != 0 THEN round(sum(timeoriginalestimate) / 60::numeric / 60::numeric / capacitylink.capacity::numeric * 100::numeric, 2) ELSE null END AS loadingratio

FROM renderedissues

LEFT JOIN users ON renderedissues.assignee=users.user_key

LEFT JOIN capacitylink ON capacitylink.user_key=users.user_key

GROUP BY display_name, lower_last_name, lower_first_name, capacitylink.capacity, capacitylink.capacitylink

ORDER BY 1

),

final AS (

select display_name AS "User",

capacitylink AS "Capacity",

assignedissues AS "Assigned Issues",

'<span title="Sum of Original Estimates">' || sumoriginalestimates || '</span>' AS "∑ Original Estimates",

'<span title="Sum of Remaining Estimates">' || sumremainingestimates || '</span>' AS "∑ Remaining Estimates",

'<span title="Sum of logged hours">' || sumtimespent || '</span>' AS "∑ Work Logged",

'<span title="Original Estimate divided by User Capacity"><font color=" ' || CASE WHEN loadingratio > 100 THEN 'red' ELSE 'green' END || '">' || loadingratio || '%</font><br/>' || '<progress value="' || loadingratio || '" max="100"></progress></span>' AS "Loading Ratio"

  from totals

)

SELECT * FROM final;