IterationReportTest

/***
    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 iteration = $3 ?? $iteration;
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;

// create subset of services issues
var services = list.groupby(issues, '$fields["Client Services"] ?? "Yes"');
var services1 = services['Yes'];
var services_groups = list.groupby(services1, '$state');
var services_closed = [ ] .. services_groups['Fixed'] .. services_groups['Verified'] .. services_groups['Closed'];

// set default values
var default_hours_left = 4;
var default_hours_plan = 4;
var default_hours_spent = 0;
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
*/

// create status table hourly values
var hours_plan = list.sum([ num.cast(open_issue.fields['Hours Planned']) ?? default_hours_plan foreach var open_issue in issues ]);
var hours_svc_plan = list.sum([ num.cast(open_issue.fields['Hours Planned']) ?? default_hours_plan foreach var open_issue in services1 ]);
var hours_prod_plan = hours_plan - hours_svc_plan;

var hours_left = list.sum([ num.cast(open_issue.fields['Hours Left']) ?? default_hours_left foreach var open_issue in open ]);
var hours_svc_left = list.sum([ num.cast(open_issue.fields['Hours Left']) ?? default_hours_left foreach var open_issue in services1 ]);
var hours_prod_left = hours_left - hours_svc_left;

var hours_spent = list.sum([ num.cast(open_issue.fields['Hours Spent']) ?? default_hours_spent foreach var open_issue in issues ]);
var hours_svc_spent = list.sum([ num.cast(open_issue.fields['Hours Spent']) ?? default_hours_spent foreach var open_issue in services1 ]);
var hours_prod_spent = hours_spent - hours_svc_spent;

var percent_hours_complete = num.round((hours_plan - hours_left) / hours_plan *100, 0);
var percent_svc_complete = num.round((hours_svc_plan - hours_svc_left) / hours_svc_plan *100, 0);
var percent_prod_complete = num.round((hours_prod_plan - hours_prod_left) / hours_prod_plan *100, 0);

var spent_better_than_plan = hours_plan - hours_left - hours_spent;
var spent_better_than_plan_percent = num.round(spent_better_than_plan / (hours_plan - hours_left) *100, 0);
var spent_better_than_plan_prod = hours_prod_plan - hours_prod_left - hours_prod_spent;
var spent_better_than_plan_prod_percent = num.round(spent_better_than_plan_prod / (hours_prod_plan - hours_prod_left) *100, 0);
var spent_better_than_plan_svc = hours_svc_plan - hours_svc_left - hours_svc_spent;
var spent_better_than_plan_svc_percent = num.round(spent_better_than_plan_svc / (hours_svc_plan - hours_svc_left) *100, 0);

// show status table
<div class="youtrack-status">
    <h2> "Status" </h2>

<table class="progress" width="600px" border="1">
        <tr>
            <td class="open">&nbsp;</td>
            <td class="closed"><b>'Total'</b></td>
            <td class="closed"><b>'Prod'</b></td>
            <td class="closed"><b>'Services'</b></td>
        </tr>
        <tr>
            <td class="closed"><b>'Planned Task Count'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: #issues },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: #issues - #services['Yes']},
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: #services['Yes'] },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Completed Task Count'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: #closed },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: (#closed - #services_closed) },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: #services_closed },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Planned Task Hours'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_plan },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_prod_plan},
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_svc_plan },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Completed Task Hours'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_plan - hours_left },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_prod_plan - hours_prod_left },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_svc_plan - hours_svc_left },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Actual Hours Spent'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_spent },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_prod_spent},
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value' % { value: hours_svc_spent },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Percent Complete'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value%' % { value: percent_hours_complete },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value%' % { value: percent_prod_complete },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value%' % { value: percent_svc_complete },
                    target: '_blank'
                    };
            </td>
        </tr>
        <tr>
            <td class="closed"><b>'Actual Hours better than Plan'</b>
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value hours ($percent%)' % { value: spent_better_than_plan, percent: spent_better_than_plan_percent },
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value hours ($percent%)' % { value: spent_better_than_plan_prod, percent: spent_better_than_plan_prod_percent},
                    target: '_blank'
                    };
            </td>
            <td class="closed">
                youtrack.querylink{
                    query: query .. ' #resolved',
                    text: '$value hours ($percent%)' % { value: spent_better_than_plan_svc, percent: spent_better_than_plan_svc_percent },
                    target: '_blank'
                    };
            </td>
        </tr>
/* Cut line from original table        
        <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>
            <td>'na'</td>
            <td>'na'</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 to go, ETA: "; eta; " hours)"; </strong> 
    </span>
</div>


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

/*
* TEAM REPORT
*/

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

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');

<table border="1">
    <tr>
        <td><b>'Name'</b></td>
        <td><b><center>'Hours Allocated'</center></b></td>
        <td><b><center>'Hours Spent'</center></b></td>
        <td colspan="3"><b><center>'Tasks'</center></b></td>
    </tr>

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

        // show link to closed issues
        <td>
        if(#item.closed) {
            youtrack.querylink{
                query: query .. ' #resolved assignee: {$assignee}' % item, 
                text: #item.closed,
                target: '_blank'
            };
            " closed";
        }
        </td>
        <td>
        if(#item.closed ?? #item.open) {
            youtrack.querylink{
                query: query .. ' assignee: {$assignee}' % item,
                text: total,
                target: '_blank'
            };
            " total";
        }
        </td>
    </tr>
}
</table>

.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;
}