YouTrackReport

/***
    USAGE:
    
    YouTrackReport(query, start, end)
        show report for issues returned by the given query
***/

// check that prerequisites are met
if(!__env.youtrack) {
    return "YouTrack extension is not enabled";
}
if(!__env.google) {
    return "Google extension is not enabled";
}

// read and validate parameters
var query = $0 ?? $query;
if(!query) {
    return "Missing query";
}
var start = $1 ?? $start ?? date.today;
var end = $2 ?? $end ?? date.today;
var availability = $team;

// retrieve list of issues and categorize accordingly
var limit = 1000;
var issues = youtrack.issues{ query: query .. ' type:bug', limit: limit }
    .. youtrack.issues{ query: query .. ' type:feature', limit: limit }
    .. youtrack.issues{ query: query .. ' type:task', limit: limit }
    .. youtrack.issues{ query: query .. ' type:-bug,-feature,-task', limit: limit };

var groups = list.groupby(issues, '$state');
var open = [ ] .. groups['Open'] .. groups['Submitted'] .. groups['Reopened'] .. groups['In Progress'] .. groups['To be discussed'];
var resolved = [ ] .. groups['Fixed'] .. groups['Verified'] .. groups['Closed'];
var discarded = groups['Can\'t Reproduce'] .. groups['Duplicate'] .. groups['Incomplete'] .. groups['Obsolete'] .. groups['Won\'t fix'];
var closed = resolved .. discarded;
var default_hours_left = 4;
var today = date.today;

// determine start and end dates
if(!start) {
    let start = today;
    foreach(var issue in issues) {
        let start = date.min(start, issue.created);
    }
}
let start = date.startofday(start);
if(!end) {
    let end = date.addmonths(date.now, -1);
    foreach(var issue in issues) {
        let end = date.max(end, issue.created);
        if(issue.resolved) {
            let end = date.max(end, issue.resolved);
        }
    }
}
let end = date.startofday(date.adddays(end, 1));

// compute # of remaining work days
var daysleft = date.diffdays(end, date.max(start, date.min(today, end)));
var daystotal = date.diffdays(end, start);
foreach(var index in num.series(0, daystotal)) {
    switch(date.dayofweek(date.adddays(start, index))) {
    case 0: // sunday
    case 6: // saturday
        let daystotal -= 1;
        if(!date.isbefore(date.adddays(start, index), today)) {
            let daysleft -= 1;
        }
    }
}
var remaining = daysleft / daystotal;

<div class="youtrack-section-status">

/*
* STATUS REPORT
*/

// determine remaining hours
var hours_left = list.sum([ num.cast(open_issue.fields['Hours Left']) ?? default_hours_left foreach var open_issue in open ]);
var hours_plan = list.sum([ num.cast(open_issue.fields['Hours Planned']) ?? default_hours_left foreach var open_issue in issues ]);

// show status table
<div class="youtrack-status">
    <h2> "Status" </h2>
    <table class="progress" width="600px" border="1">
        <tr>
            <td class="closed" style=("width: $width; background-color: $color" % { color: "rgb(204, 255, 204)", width: web.size(num.min(0.999, #closed / #issues)) }) >
                youtrack.querylink{
                    query: query .. ' #resolved', 
                    text: '$value out of $max issues closed' % { value: #closed, max: #issues },
                    target: '_blank'
                };
            </td>
            <td class="open" style=("width: $width; background-color: $color" % { color: "rgb(255, 255, 255)", width: web.size(num.min(0.999, (#issues - #closed) / #issues)) }) >
                youtrack.querylink{
                    query: query .. ' #unresolved', 
                    text: '$value out of $max issues open' % { value: #issues - #closed, max: #issues },
                    target: '_blank'
                };
            </td>
        </tr>
    </table>
    
    // show status summary
    var sum_bugs_by_dev = [ 
        { 
            dev: dev, 
            bugs: errors,
            tasks: tasks,
            features: features,
            other: other,
            sum: sum
        }
        foreach 
            var dev : bugs in list.groupby(open, '$assignee') where #bugs > 0,
            var errors = list.sum([ num.cast(bug.fields['Hours Left']) ?? default_hours_left foreach var bug in bugs where bug.type == 'Bug' ]),
            var tasks = list.sum([ num.cast(bug.fields['Hours Left']) ?? default_hours_left foreach var bug in bugs where bug.type == 'Task' ]),
            var features = list.sum([ num.cast(bug.fields['Hours Left']) ?? default_hours_left foreach var bug in bugs where bug.type == 'Feature' ]),
            var other = list.sum([ num.cast(bug.fields['Hours Left']) ?? default_hours_left foreach var bug in bugs where bug.type != 'Bug' && bug.type != 'Task' && bug.type != 'Feature' ]),
            var sum = errors + tasks + features + other
    ];
    let sum_bugs_by_dev = list.sort(sum_bugs_by_dev, "dev");
    var eta = list.max([ 0 ] .. [ item.sum foreach var item in sum_bugs_by_dev ]);
    <span>
        "Open issues: "; #open; ", ";
        "Closed issues: "; #closed; ", ";
        "Total issues: "; #issues; " ";
        <strong> "("; num.format(#closed / #issues * 100, "#0"); "% completed; "; hours_left; " of "; hours_plan; " man-hours left, ETA: "; eta; " hours)"; </strong> 
    </span>
</div>

var issue_states = list.groupby(issues, '$state');
var issue_types = list.groupby(issues, '$type');
<div class="youtrack-issue-states">
    <h2> "Issue States"; </h2>
    google.piechart{
        width: 380,
        height: 180,
        values: [ #items foreach var items in issue_states ],
        labels: [ label .. " ($count)" % { count: #items } foreach var label:items in issue_states ],
        threed: false
    };
    <ul>
        foreach(var state:items in issue_states) {
            <li>
                youtrack.querylink{
                    query: query .. ' state: {' .. state .. '}', 
                    text: '$state: $value issues' % { value: #items, state: state },
                    target: '_blank'
                };
            </li>
        }
    </ul>
</div>

<div class="youtrack-issue-types">
    <h2> "Issue Types"; </h2>
    google.piechart{
        width: 380,
        height: 180,
        values: [ #items foreach var items in issue_types ],
        labels: [ label .. " ($count)" % { count: #items } foreach var label:items in issue_types ],
        threed: false
    };
    <ul>
        foreach(var type:items in issue_types) {
            <li>
                youtrack.querylink{
                    query: query .. ' type: {' .. type .. '}', 
                    text: '$type: $value issues' % { value: #items, type: type },
                    target: '_blank'
                };
            </li>
        }
    </ul>
</div>

<div class="youtrack-projects">
    var projects = list.groupby(issues, 'string.substr($id, 0, string.indexof($id, "-"))');
    <h2> "Projects"; </h2>
    google.piechart{
        width: 380,
        height: 180,
        values: [ #items foreach var items in projects ],
        labels: [ label .. " ($count)" % { count: #items } foreach var label:items in projects ],
        threed: false
    };
    <ul>
        foreach(var project:items in projects) {
            <li>
                youtrack.querylink{
                    query: query .. ' project: {' .. project .. '}',
                    text: '$project: $value issues' % { value: #items, project: project },
                    target: '_blank'
                };
            </li>
        }
    </ul>
</div>

</div> // end youtrack-section-status


<div class="youtrack-section-burndown">

/*
*  PROGRESSION REPORT
*/

// create burndown chart data
var tomorrow = date.adddays(date.today, 1);
var burndown = [
    [ issue 
        foreach 
            var issue in issues where 
                date.isbefore(current, tomorrow)
                && !(issue.resolved && date.issameday(issue.created, issue.resolved))
                && (!issue.resolved || date.issameday(issue.resolved, current) || date.isafter(issue.resolved, current))
                && (date.isbefore(issue.created, current) || date.issameday(issue.created, current))
    ]
    foreach
        var day in num.series(0, date.diffdays(end, start)),
        var current = date.adddays(start, __index)
];

<h2> "Progression (#tickets)" </h2>
<br/>
    var burndownvalues = [
        [
            #[ issue foreach var issue in daily where date.issameday(issue.created, current) ],
            #[ issue foreach var issue in daily where !date.issameday(issue.created, current) && !(issue.resolved && date.issameday(issue.resolved, current)) && (!issue.resolved || date.isafter(issue.resolved, current)) ],
            #[ issue foreach var issue in daily where issue.resolved && date.issameday(issue.resolved, current) ]
        ] foreach
            var daily in burndown,
            var current = date.adddays(start, __index)
    ];

    google.barchart{
        width: (#burndown + 1) * 20 + 250, 
        height: 200, 
        values: burndownvalues, 
        colors: [ "ff0000", "ff9900", "333399" ], 
        legends: [ "new", "open", "resolved" ], 
        vertical: true, 
        stacked: true,
        xaxis: [ 
            (switch(__index) {
                case 0: date.format(start, "d");
                case #burndown - 1: date.format(end, "d");
                default: "";
            }) foreach var x in burndown
        ]
    };

</div> // end youtrack-section-burndown

/*
* TEAM REPORT
*/

<h2> "Team (#hours)" </h2>

if(#sum_bugs_by_dev > 0) {
    <br/>
    google.barchart{
        width: 400, 
        height: (#sum_bugs_by_dev + 1) * 27, 
        values: [ [ item.bugs, item.features, item.tasks, item.other ] foreach var item in sum_bugs_by_dev ], 
        legends: [ "bugs", "features", "tasks", "other" ], 
        colors: [ "ff0000", "ff9900", "ffdd00", "000000" ], 
        vertical: false, 
        stacked: true, 
        xaxis: [ 0, eta/2, eta ], 
        yaxis: list.reverse([ item.dev foreach var item in sum_bugs_by_dev ])
    };
}

var groups = list.sort([
    {
        assignee: assignee, 
        open: [ item foreach var item in values where !item.resolved ], 
        closed: [ item foreach var item in values where item.resolved ]
    }
    foreach
        var assignee : values in list.groupby(issues, '$assignee ?? "unassigned"')
], 'assignee');

foreach(var item in groups) {
    var hours = list.sum([ num.cast(bug.fields['Hours Left']) ?? default_hours_left foreach var bug in item.open ]);
    var done = #item.closed;
    var total = #item.open + #item.closed;
    var available = availability[item.assignee] ? num.round(remaining * availability[item.assignee]) : nil;
    <h5>
        <font color=(available < hours ? "red" : nil)> item.assignee </font>
        ": ";
        if(total) {
            if(available) {
                "$percent% done, $hours of $available hrs ($remain available)" % {
                    hours: hours,
                    percent: num.format(done / total * 100, "#0"),
                    available: available,
                    remain: available - hours
                };
            } else {
                "$percent% done, $hours hrs left" % {
                    hours: hours,
                    percent: num.format(done / total * 100, "#0")
                };
            }
        }
    
        // show link to open issues
        if(#item.open) {
            ", ";
            youtrack.querylink{
                query: query .. ' #unresolved assignee: {$assignee}' % item, 
                text: #item.open,
                target: '_blank'
            };
            " open";
        }

        // show link to closed issues
        if(#item.closed) {
            ", ";
            youtrack.querylink{
                query: query .. ' #resolved assignee: {$assignee}' % item, 
                text: #item.closed,
                target: '_blank'
            };
            " closed";
        }
    </h5>
    template('Dev/Controls/YouTrackList', { issues: list.sort(item.open .. item.closed, 'priority', false), options: { showHours: true, showClient: true, showBlocked: true }});
}

.youtrack-section-status {
  overflow: auto;
  margin-bottom: 20px;
}

.youtrack-issue-states {
  float: left;
  width: 400px;
  border: 1px solid #ccc;
  margin-right: 20px;
  padding: 10px;
  min-height: 400px;
  margin-top: 10px;
}

.youtrack-issue-types {
  float: left;
  width: 400px;
  border: 1px solid #ccc;
  margin-right: 20px;
  padding: 10px;
  min-height: 400px;
  margin-top: 10px;
}

.youtrack-projects {
  float: left;
  width: 400px;
  border: 1px solid #ccc;
  margin-right: 20px;
  padding: 10px;
  min-height: 400px;
  margin-top: 10px;
}

.youtrack-section-burndown {
  xborder-top: 1px solid #ccc;
  overflow: auto;
  padding-top: 5px;
}

.youtrack-burndown {
  width: 400px;
  float: left;
}

.youtrack-burndown img {
  xwidth: 400px;
  height: 270px;
}

.youtrack-progression {
  width: 400px;
  border: 1px solid #ccc;
  float: left;
  margin-left: 40px;
  padding: 10px;
  height: 250px;
}