Extracts a burndown report from a local bugzilla installation.

burndown.pl

#!/usr/bin/perl

# Bugzilla burndown data extraction script.
# Author: Michael Zedeler.
# License: this script is licensed under the same license as perl itself.
# Version: 1.1
# Source: https://gist.github.com/879278

use strict;
use warnings;

use lib '/usr/share/bugzilla3';

use Bugzilla;
use Bugzilla::DB;
use List::Util qw{ maxstr sum };
use Getopt::Long;

sub usage {
  return <<"END_USAGE";
Usage: $0 --milestone=<milestone> --start=<YYYY>-<MM>-<DD> [--matrix] [--create_view]
END_USAGE
}

sub get_options {
  my($milestone, $start, $matrix, $create_view);

  my $options = GetOptions(
    "milestone=s" => \$milestone,
    "start=s"     => \$start,
    "matrix"      => \$matrix,
    "create_view" => \$create_view
  );

  die usage unless $milestone and $start and $start =~ /^\d{4}-\d{2}-\d{2}$/;
  
  return $milestone, $start, $matrix, $create_view;
}

sub create_view {
  shift->do(<<'END_SQL') or die "Can't create burndown view in database.";
  CREATE OR REPLACE VIEW cs_burndown AS
  SELECT bugs.bug_id,
         fielddefs.name,
         target_milestone AS milestone,
         bug_when,
         IF(added='VERIFIED', '0', added) AS estimate
    FROM bugs_activity
    JOIN bugs ON bugs_activity.bug_id = bugs.bug_id
    JOIN fielddefs ON fielddefs.id = bugs_activity.fieldid
   WHERE fielddefs.name IN ('estimated_time', 'remaining_time')
      OR fielddefs.name='bug_status' AND added='VERIFIED';
END_SQL
}

sub get_burndown {
  my($dbh, $milestone, $start, $matrix) = @_;

  # First gather daily estimates
  my $estimates = {};
  my $bugs = {};

  my $sth = $dbh->prepare(<<'END_SQL');
  SELECT bug_id, DATE_FORMAT(creation_ts, '%Y-%m-%d') AS bug_when,
         COALESCE(remaining_time, estimated_time) AS estimate,
         target_milestone AS milestone
    FROM bugs
   WHERE target_milestone = ?
END_SQL
  $sth->execute($milestone);
  
  while(my $row = $sth->fetchrow_hashref) {
    my $reg_date = maxstr($row->{bug_when}, $start);
    $estimates->{$reg_date}{$row->{bug_id}} = $row->{estimate};
    $bugs->{$row->{bug_id}} = 1;
  }

  $sth = $dbh->prepare(<<'END_SQL');
    SELECT bug_id,
           DATE_FORMAT(bug_when, '%Y-%m-%d') AS bug_when,
           estimate
      FROM cs_burndown
     WHERE milestone = ?
END_SQL
  $sth->execute($milestone);
  
  # Lazy scripting here. The two loops below could be folded into one by
  # making the database order by bug_when.

  while(my $row = $sth->fetchrow_hashref) {
    my $reg_date = maxstr($row->{bug_when}, $start);
    $estimates->{$reg_date}{$row->{bug_id}} = $row->{estimate};
    $bugs->{$row->{bug_id}} = 1;
  }

  print "date\t";
  print join("\t", sort keys %$bugs) if $matrix;
  print "\tsum\n";
  my $remaining = $estimates->{$start}; # Initial vector with all bug estimates
  foreach my $date (sort {$a cmp $b} keys %$estimates) {
    # Update vector
    $remaining->{$_} = $estimates->{$date}{$_} for keys %{$estimates->{$date}};
    my $today = sum( values %{$remaining} );
    
    # The rest is just output
    print $date, "\t";
    if($matrix) {
      print join("\t", map {$remaining->{$_} || 0} (sort keys %$bugs));
    }
    print "\t$today\n";
  }
}

sub main {
  my($milestone, $start, $matrix, $create_view) = get_options;
  my $dbh = Bugzilla::DB->connect_main
    or die "Can't connect to bugzilla database";

  create_view($dbh) if $create_view;
  get_burndown($dbh, $milestone, $start, $matrix);
}

main;