BugsSite/summarize_time.cgi - WebKit - Git at Google

 #!/usr/bin/perl -wT
 # -*- Mode: perl; indent-tabs-mode: nil -*-
 #
 # The contents of this file are subject to the Mozilla Public
 # License Version 1.1 (the "License"); you may not use this file
 # except in compliance with the License. You may obtain a copy of
 # the License at http://www.mozilla.org/MPL/
 #
 # Software distributed under the License is distributed on an "AS
 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 # implied. See the License for the specific language governing
 # rights and limitations under the License.
 #
 # The Original Code is the Bugzilla Bug Tracking System.
 #
 # Contributor(s): Christian Reis <kiko@async.com.br>
 #                 Shane H. W. Travis <travis@sedsystems.ca>
 #
 use strict;

 use lib qw(.);

 use Date::Parse;         # strptime
 use Date::Format;        # strftime

 use Bugzilla::Bug;       # EmitDependList
 use Bugzilla::Util;      # trim
 use Bugzilla::Constants; # LOGIN_*
 use Bugzilla::User;      # UserInGroup
 require "CGI.pl";

 GetVersionTable();

 # Use global template variables.
 use vars qw($template $vars);

 #
 # Date handling
 #

 sub date_adjust_down {

     my ($year, $month, $day) = @_;

     if ($day == 0) {
         $month -= 1;
         $day = 31;
         # Proper day adjustment is done later.

         if ($month == 0) {
             $year -= 1;
             $month = 12;
         }
     }

     if (($month == 2) && ($day > 28)) {
         if ($year % 4 == 0 && $year % 100 != 0) {
             $day = 29;
         } else {
             $day = 28;
         }
     }

     if (($month == 4 || $month == 6 || $month == 9 || $month == 11) &&
         ($day == 31) )
     {
         $day = 30;
     }
     return ($year, $month, $day);
 }

 sub date_adjust_up {
     my ($year, $month, $day) = @_;

     if ($day > 31) {
         $month += 1;
         $day    = 1;

         if ($month == 13) {
             $month = 1;
             $year += 1;
         }
     }

     if ($month == 2 && $day > 28) {
         if ($year % 4 != 0 || $year % 100 == 0 || $day > 29) {
             $month = 3;
             $day = 1;
         }
     }

     if (($month == 4 || $month == 6 || $month == 9 || $month == 11) &&
         ($day == 31) )
     {
         $month += 1;
         $day    = 1;
     }

     return ($year, $month, $day);
 }

 sub check_dates {
     my ($start_date, $end_date) = @_;
     if ($start_date) {
         if (!str2time($start_date)) {
             ThrowUserError("illegal_date", {'date' => $start_date});
         }
         # This code may strike you as funny. It's actually a workaround
         # for an "issue" in str2time. If you enter the date 2004-06-31,
         # even though it's a bogus date (there *are* only 30 days in
         # June), it will parse and return 2004-07-01. To make this
         # less painful to the end-user, I do the "normalization" here,
         # but it might be "surprising" and warrant a warning in the end.
         $start_date = time2str("%Y-%m-%d", str2time($start_date));
     }
     if ($end_date) {
         if (!str2time($end_date)) {
             ThrowUserError("illegal_date", {'date' => $end_date});
         }
         # see related comment above.
         $end_date = time2str("%Y-%m-%d", str2time($end_date));
     }
     return ($start_date, $end_date);
 }

 sub split_by_month {
     # Takes start and end dates and splits them into a list of
     # monthly-spaced 2-lists of dates.
     my ($start_date, $end_date) = @_;

     # We assume at this point that the dates are provided and sane
     my (undef, undef, undef, $sd, $sm, $sy, undef) = strptime($start_date);
     my (undef, undef, undef, $ed, $em, $ey, undef) = strptime($end_date);

     # Find out how many months fit between the two dates so we know
     # how many times we loop.
     my $yd = $ey - $sy;
     my $md = 12 * $yd + $em - $sm;
     # If the end day is smaller than the start day, last interval is not a whole month.
     if ($sd > $ed) {
         $md -= 1;
     }

     my (@months, $sub_start, $sub_end);
     # This +1 and +1900 are a result of strptime's bizarre semantics
     my $year = $sy + 1900;
     my $month = $sm + 1;

     # Keep the original date, when the date will be changed in the adjust_date.
     my $sd_tmp = $sd;
     my $month_tmp = $month;
     my $year_tmp = $year;

     # This section handles only the whole months.
     for (my $i=0; $i < $md; $i++) {
         # Start of interval is adjusted up: 31.2. -> 1.3.
         ($year_tmp, $month_tmp, $sd_tmp) = date_adjust_up($year, $month, $sd);
         $sub_start = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
         $month += 1;
         if ($month == 13) {
             $month = 1;
             $year += 1;
         }
         # End of interval is adjusted down: 31.2 -> 28.2.
         ($year_tmp, $month_tmp, $sd_tmp) = date_adjust_down($year, $month, $sd - 1);
         $sub_end = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
         push @months, [$sub_start, $sub_end];
     }

     # This section handles the last (unfinished) month.
     $sub_end = sprintf("%04d-%02d-%02d", $ey + 1900, $em + 1, $ed);
     ($year_tmp, $month_tmp, $sd_tmp) = date_adjust_up($year, $month, $sd);
     $sub_start = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
     push @months, [$sub_start, $sub_end];

     return @months;
 }

 sub include_tt_details {
     my ($res, $bugids, $start_date, $end_date) = @_;


     my $dbh = Bugzilla->dbh;
     my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);
     my $buglist = join ", ", @{$bugids};

     my $q = qq{SELECT bugs.bug_id, profiles.login_name, bugs.deadline,
                       bugs.estimated_time, bugs.remaining_time
                FROM   longdescs
                INNER JOIN bugs
                   ON longdescs.bug_id = bugs.bug_id
                INNER JOIN profiles
                   ON longdescs.who = profiles.userid
                WHERE  longdescs.bug_id in ($buglist) $date_bits};

     my %res = %{$res};
     my $sth = $dbh->prepare($q);
     $sth->execute(@{$date_values});
     while (my $row = $sth->fetch) {
         $res{$row->[0]}{"deadline"} = $row->[2];
         $res{$row->[0]}{"estimated_time"} = $row->[3];
         $res{$row->[0]}{"remaining_time"} = $row->[4];
     }
     return \%res;
 }

 sub sqlize_dates {
     my ($start_date, $end_date) = @_;
     my $date_bits;
     my @date_values;
     if ($start_date) {
         # we've checked, trick_taint is fine
         trick_taint($start_date);
         $date_bits = " AND longdescs.bug_when > ?";
         push @date_values, $start_date;
     }
     if ($end_date) {
         # we need to add one day to end_date to catch stuff done today
         # do not forget to adjust date if it was the last day of month
         my (undef, undef, undef, $ed, $em, $ey, undef) = strptime($end_date);
         ($ey, $em, $ed) = date_adjust_up($ey+1900, $em+1, $ed+1);
         $end_date = sprintf("%04d-%02d-%02d", $ey, $em, $ed);

         $date_bits .= " AND longdescs.bug_when < ?";
         push @date_values, $end_date;
     }
     return ($date_bits, \@date_values);
 }

 #
 # Dependencies
 #

 sub get_blocker_ids_unique {
     my $bug_id = shift;
     my @ret = ($bug_id);
     get_blocker_ids_deep($bug_id, \@ret);
     my %unique;
     foreach my $blocker (@ret) {
         $unique{$blocker} = $blocker
     }
     return keys %unique;
 }

 sub get_blocker_ids_deep {
     my ($bug_id, $ret) = @_;
     my $deps = Bugzilla::Bug::EmitDependList("blocked", "dependson", $bug_id);
     push @{$ret}, @$deps;
     foreach $bug_id (@$deps) {
         get_blocker_ids_deep($bug_id, $ret);
     }
 }

 #
 # Queries and data structure assembly
 #

 sub query_work_by_buglist {
     my ($bugids, $start_date, $end_date) = @_;
     my $dbh = Bugzilla->dbh;

     my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);

     # $bugids is guaranteed to be non-empty because at least one bug is
     # always provided to this page.
     my $buglist = join ", ", @{$bugids};

     # Returns the total time worked on each bug *per developer*, with
     # bug descriptions and developer address
     my $q = qq{SELECT sum(longdescs.work_time) as total_time,
                       profiles.login_name,
                       longdescs.bug_id,
                       bugs.short_desc,
                       bugs.bug_status
                FROM   longdescs
                INNER JOIN profiles
                    ON longdescs.who = profiles.userid
                INNER JOIN bugs
                    ON bugs.bug_id = longdescs.bug_id
                WHERE  longdescs.bug_id IN ($buglist)
                       $date_bits } .
             $dbh->sql_group_by('longdescs.bug_id, profiles.login_name',
                 'bugs.short_desc, bugs.bug_status, longdescs.bug_when') . qq{
                ORDER BY longdescs.bug_when};
     my $sth = $dbh->prepare($q);
     $sth->execute(@{$date_values});
     return $sth;
 }

 sub get_work_by_owners {
     my $sth = query_work_by_buglist(@_);
     my %res;
     while (my $row = $sth->fetch) {
         # XXX: Why do we need to check if the total time is positive
         # instead of using SQL to do that?  Simply because MySQL 3.x's
         # GROUP BY doesn't work correctly with aggregates. This is
         # really annoying, but I've spent a long time trying to wrestle
         # with it and it just doesn't seem to work. Should work OK in
         # 4.x, though.
         if ($row->[0] > 0) {
             my $login_name = $row->[1];
             push @{$res{$login_name}}, { total_time => $row->[0],
                                          bug_id     => $row->[2],
                                          short_desc => $row->[3],
                                          bug_status => $row->[4] };
         }
     }
     return \%res;
 }

 sub get_work_by_bugs {
     my $sth = query_work_by_buglist(@_);
     my %res;
     while (my $row = $sth->fetch) {
         # Perl doesn't let me use arrays as keys :-(
         # merge in ID, status and summary
         my $bug = join ";", ($row->[2], $row->[4], $row->[3]);
         # XXX: see comment in get_work_by_owners
         if ($row->[0] > 0) {
             push @{$res{$bug}}, { total_time => $row->[0],
                                   login_name => $row->[1], };
         }
     }
     return \%res;
 }

 sub get_inactive_bugs {
     my ($bugids, $start_date, $end_date) = @_;
     my $dbh = Bugzilla->dbh;
     my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);
     my $buglist = join ", ", @{$bugids};

     my %res;
     # This sucks. I need to make sure that even bugs that *don't* show
     # up in the longdescs query (because no comments were filed during
     # the specified period) but *are* dependent on the parent bug show
     # up in the results if they have no work done; that's why I prefill
     # them in %res here and then remove them below.
     my $q = qq{SELECT DISTINCT bugs.bug_id, bugs.short_desc ,
                                bugs.bug_status
                FROM   longdescs
                INNER JOIN bugs
                     ON longdescs.bug_id = bugs.bug_id
                WHERE  longdescs.bug_id in ($buglist)};
     my $sth = $dbh->prepare($q);
     $sth->execute();
     while (my $row = $sth->fetch) {
         $res{$row->[0]} = [$row->[1], $row->[2]];
     }

     # Returns the total time worked on each bug, with description. This
     # query differs a bit from one in the query_work_by_buglist and I
     # avoided complicating that one just to make it more general.
     $q = qq{SELECT sum(longdescs.work_time) as total_time,
                    longdescs.bug_id,
                    bugs.short_desc,
                    bugs.bug_status
             FROM   longdescs
             INNER JOIN bugs
                 ON bugs.bug_id = longdescs.bug_id
             WHERE  longdescs.bug_id IN ($buglist)
                    $date_bits } .
          $dbh->sql_group_by('longdescs.bug_id',
                             'bugs.short_desc, bugs.bug_status,
                              longdescs.bug_when') . qq{
             ORDER BY longdescs.bug_when};
     $sth = $dbh->prepare($q);
     $sth->execute(@{$date_values});
     while (my $row = $sth->fetch) {
         # XXX: see comment in get_work_by_owners
         if ($row->[0] == 0) {
             $res{$row->[1]} = [$row->[2], $row->[3]];
         } else {
             delete $res{$row->[1]};
         }
     }
     return \%res;
 }

 #
 # Misc
 #

 sub sort_bug_keys {
     # XXX a hack is the mother of all evils. The fact that we store keys
     # joined by semi-colons in the workdata-by-bug structure forces us to
     # write this evil comparison function to ensure we can process the
     # data timely -- just pushing it through a numerical sort makes TT
     # hang while generating output :-(
     my $list = shift;
     my @a;
     my @b;
     return sort { @a = split(";", $a);
                   @b = split(";", $b);
                   $a[0] <=> $b[0] } @{$list};
 }

 #
 # Template code starts here
 #

 Bugzilla->login(LOGIN_REQUIRED);

 my $cgi = Bugzilla->cgi;

 Bugzilla->switch_to_shadow_db();

 UserInGroup(Param("timetrackinggroup"))
     || ThrowUserError("auth_failure", {group  => "time-tracking",
                                        action => "access",
                                        object => "timetracking_summaries"});

 my @ids = split(",", $cgi->param('id'));
 map { ValidateBugID($_) } @ids;
 @ids = map { detaint_natural($_) && $_ } @ids;
 @ids = grep { Bugzilla->user->can_see_bug($_) } @ids;

 my $group_by = $cgi->param('group_by') || "number";
 my $monthly = $cgi->param('monthly');
 my $detailed = $cgi->param('detailed');
 my $do_report = $cgi->param('do_report');
 my $inactive = $cgi->param('inactive');
 my $do_depends = $cgi->param('do_depends');
 my $ctype = scalar($cgi->param("ctype"));

 my ($start_date, $end_date);
 if ($do_report && @ids) {
     my @bugs = @ids;

     # Dependency mode requires a single bug and grabs dependents.
     if ($do_depends) {
         if (scalar(@bugs) != 1) {
             ThrowCodeError("bad_arg", { argument=>"id",
                                         function=>"summarize_time"});
         }
         @bugs = get_blocker_ids_unique($bugs[0]);
         @bugs = grep { Bugzilla->user->can_see_bug($_) } @bugs;
     }

     $start_date = trim $cgi->param('start_date');
     $end_date = trim $cgi->param('end_date');

     # Swap dates in case the user put an end_date before the start_date
     if ($start_date && $end_date &&
         str2time($start_date) > str2time($end_date)) {
         $vars->{'warn_swap_dates'} = 1;
         ($start_date, $end_date) = ($end_date, $start_date);
     }
     ($start_date, $end_date) = check_dates($start_date, $end_date);

     if ($detailed) {
         my %detail_data;
         my $res = include_tt_details(\%detail_data, \@bugs, $start_date, $end_date);

         $vars->{'detail_data'} = $res;
     }

     # Store dates ia session cookie the dates so re-visiting the page
     # for other bugs keeps them around.
     $cgi->send_cookie(-name => 'time-summary-dates',
                       -value => join ";", ($start_date, $end_date));

     my (@parts, $part_data, @part_list);

     # Break dates apart into months if necessary; if not, we use the
     # same @parts list to allow us to use a common codepath.
     if ($monthly) {
         # unfortunately it's not too easy to guess a start date, since
         # it depends on what bugs we're looking at. We risk bothering
         # the user here. XXX: perhaps run a query to see what the
         # earliest activity in longdescs for all bugs and use that as a
         # start date.
         $start_date || ThrowUserError("illegal_date", {'date' => $start_date});
         # we can, however, provide a default end date. Note that this
         # differs in semantics from the open-ended queries we use when
         # start/end_date aren't provided -- and clock skews will make
         # this evident!
         @parts = split_by_month($start_date,
                                 $end_date || time2str("%Y-%m-%d", time()));
     } else {
         @parts = ([$start_date, $end_date]);
     }

     my %empty_hash;
     # For each of the separate divisions, grab the relevant summaries
     foreach my $part (@parts) {
         my ($sub_start, $sub_end) = @{$part};
         if (@bugs) {
             if ($group_by eq "owner") {
                 $part_data = get_work_by_owners(\@bugs, $sub_start, $sub_end);
             } else {
                 $part_data = get_work_by_bugs(\@bugs, $sub_start, $sub_end);
             }
         } else {
             # $part_data must be a reference to a hash
             $part_data = \%empty_hash;
         }
         push @part_list, $part_data;
     }

     if ($inactive && @bugs) {
         $vars->{'null'} = get_inactive_bugs(\@bugs, $start_date, $end_date);
     } else {
         $vars->{'null'} = \%empty_hash;
     }

     $vars->{'part_list'} = \@part_list;
     $vars->{'parts'} = \@parts;

 } elsif ($cgi->cookie("time-summary-dates")) {
     ($start_date, $end_date) = split ";", $cgi->cookie('time-summary-dates');
 }

 $vars->{'ids'} = \@ids;
 $vars->{'start_date'} = $start_date;
 $vars->{'end_date'} = $end_date;
 $vars->{'group_by'} = $group_by;
 $vars->{'monthly'} = $monthly;
 $vars->{'detailed'} = $detailed;
 $vars->{'inactive'} = $inactive;
 $vars->{'do_report'} = $do_report;
 $vars->{'do_depends'} = $do_depends;
 $vars->{'check_time'} = \&check_time;
 $vars->{'sort_bug_keys'} = \&sort_bug_keys;
 $vars->{'GetBugLink'} = \&GetBugLink;

 $ctype = "html" if !$ctype;
 my $format = GetFormat("bug/summarize-time", undef, $ctype);

 # Get the proper content-type
 print $cgi->header(-type=> Bugzilla::Constants::contenttypes->{$ctype});
 $template->process("$format->{'template'}", $vars)
   || ThrowTemplateError($template->error());
	#!/usr/bin/perl -wT
	# -- Mode: perl; indent-tabs-mode: nil --
	#
	# The contents of this file are subject to the Mozilla Public
	# License Version 1.1 (the "License"); you may not use this file
	# except in compliance with the License. You may obtain a copy of
	# the License at http://www.mozilla.org/MPL/
	#
	# Software distributed under the License is distributed on an "AS
	# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
	# implied. See the License for the specific language governing
	# rights and limitations under the License.
	#
	# The Original Code is the Bugzilla Bug Tracking System.
	#
	# Contributor(s): Christian Reis <kiko@async.com.br>
	# Shane H. W. Travis <travis@sedsystems.ca>
	#
	use strict;

	use lib qw(.);

	use Date::Parse; # strptime
	use Date::Format; # strftime

	use Bugzilla::Bug; # EmitDependList
	use Bugzilla::Util; # trim
	use Bugzilla::Constants; # LOGIN_*
	use Bugzilla::User; # UserInGroup
	require "CGI.pl";

	GetVersionTable();

	# Use global template variables.
	use vars qw($template $vars);

	#
	# Date handling
	#

	sub date_adjust_down {

	my ($year, $month, $day) = @_;

	if ($day == 0) {
	$month -= 1;
	$day = 31;
	# Proper day adjustment is done later.

	if ($month == 0) {
	$year -= 1;
	$month = 12;
	}
	}

	if (($month == 2) && ($day > 28)) {
	if ($year % 4 == 0 && $year % 100 != 0) {
	$day = 29;
	} else {
	$day = 28;
	}
	}

	if (($month == 4 \|\| $month == 6 \|\| $month == 9 \|\| $month == 11) &&
	($day == 31) )
	{
	$day = 30;
	}
	return ($year, $month, $day);
	}

	sub date_adjust_up {
	my ($year, $month, $day) = @_;

	if ($day > 31) {
	$month += 1;
	$day = 1;

	if ($month == 13) {
	$month = 1;
	$year += 1;
	}
	}

	if ($month == 2 && $day > 28) {
	if ($year % 4 != 0 \|\| $year % 100 == 0 \|\| $day > 29) {
	$month = 3;
	$day = 1;
	}
	}

	if (($month == 4 \|\| $month == 6 \|\| $month == 9 \|\| $month == 11) &&
	($day == 31) )
	{
	$month += 1;
	$day = 1;
	}

	return ($year, $month, $day);
	}

	sub check_dates {
	my ($start_date, $end_date) = @_;
	if ($start_date) {
	if (!str2time($start_date)) {
	ThrowUserError("illegal_date", {'date' => $start_date});
	}
	# This code may strike you as funny. It's actually a workaround
	# for an "issue" in str2time. If you enter the date 2004-06-31,
	# even though it's a bogus date (there are only 30 days in
	# June), it will parse and return 2004-07-01. To make this
	# less painful to the end-user, I do the "normalization" here,
	# but it might be "surprising" and warrant a warning in the end.
	$start_date = time2str("%Y-%m-%d", str2time($start_date));
	}
	if ($end_date) {
	if (!str2time($end_date)) {
	ThrowUserError("illegal_date", {'date' => $end_date});
	}
	# see related comment above.
	$end_date = time2str("%Y-%m-%d", str2time($end_date));
	}
	return ($start_date, $end_date);
	}

	sub split_by_month {
	# Takes start and end dates and splits them into a list of
	# monthly-spaced 2-lists of dates.
	my ($start_date, $end_date) = @_;

	# We assume at this point that the dates are provided and sane
	my (undef, undef, undef, $sd, $sm, $sy, undef) = strptime($start_date);
	my (undef, undef, undef, $ed, $em, $ey, undef) = strptime($end_date);

	# Find out how many months fit between the two dates so we know
	# how many times we loop.
	my $yd = $ey - $sy;
	my $md = 12 * $yd + $em - $sm;
	# If the end day is smaller than the start day, last interval is not a whole month.
	if ($sd > $ed) {
	$md -= 1;
	}

	my (@months, $sub_start, $sub_end);
	# This +1 and +1900 are a result of strptime's bizarre semantics
	my $year = $sy + 1900;
	my $month = $sm + 1;

	# Keep the original date, when the date will be changed in the adjust_date.
	my $sd_tmp = $sd;
	my $month_tmp = $month;
	my $year_tmp = $year;

	# This section handles only the whole months.
	for (my $i=0; $i < $md; $i++) {
	# Start of interval is adjusted up: 31.2. -> 1.3.
	($year_tmp, $month_tmp, $sd_tmp) = date_adjust_up($year, $month, $sd);
	$sub_start = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
	$month += 1;
	if ($month == 13) {
	$month = 1;
	$year += 1;
	}
	# End of interval is adjusted down: 31.2 -> 28.2.
	($year_tmp, $month_tmp, $sd_tmp) = date_adjust_down($year, $month, $sd - 1);
	$sub_end = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
	push @months, [$sub_start, $sub_end];
	}

	# This section handles the last (unfinished) month.
	$sub_end = sprintf("%04d-%02d-%02d", $ey + 1900, $em + 1, $ed);
	($year_tmp, $month_tmp, $sd_tmp) = date_adjust_up($year, $month, $sd);
	$sub_start = sprintf("%04d-%02d-%02d", $year_tmp, $month_tmp, $sd_tmp);
	push @months, [$sub_start, $sub_end];

	return @months;
	}

	sub include_tt_details {
	my ($res, $bugids, $start_date, $end_date) = @_;


	my $dbh = Bugzilla->dbh;
	my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);
	my $buglist = join ", ", @{$bugids};

	my $q = qq{SELECT bugs.bug_id, profiles.login_name, bugs.deadline,
	bugs.estimated_time, bugs.remaining_time
	FROM longdescs
	INNER JOIN bugs
	ON longdescs.bug_id = bugs.bug_id
	INNER JOIN profiles
	ON longdescs.who = profiles.userid
	WHERE longdescs.bug_id in ($buglist) $date_bits};

	my %res = %{$res};
	my $sth = $dbh->prepare($q);
	$sth->execute(@{$date_values});
	while (my $row = $sth->fetch) {
	$res{$row->[0]}{"deadline"} = $row->[2];
	$res{$row->[0]}{"estimated_time"} = $row->[3];
	$res{$row->[0]}{"remaining_time"} = $row->[4];
	}
	return \%res;
	}

	sub sqlize_dates {
	my ($start_date, $end_date) = @_;
	my $date_bits;
	my @date_values;
	if ($start_date) {
	# we've checked, trick_taint is fine
	trick_taint($start_date);
	$date_bits = " AND longdescs.bug_when > ?";
	push @date_values, $start_date;
	}
	if ($end_date) {
	# we need to add one day to end_date to catch stuff done today
	# do not forget to adjust date if it was the last day of month
	my (undef, undef, undef, $ed, $em, $ey, undef) = strptime($end_date);
	($ey, $em, $ed) = date_adjust_up($ey+1900, $em+1, $ed+1);
	$end_date = sprintf("%04d-%02d-%02d", $ey, $em, $ed);

	$date_bits .= " AND longdescs.bug_when < ?";
	push @date_values, $end_date;
	}
	return ($date_bits, \@date_values);
	}

	#
	# Dependencies
	#

	sub get_blocker_ids_unique {
	my $bug_id = shift;
	my @ret = ($bug_id);
	get_blocker_ids_deep($bug_id, \@ret);
	my %unique;
	foreach my $blocker (@ret) {
	$unique{$blocker} = $blocker
	}
	return keys %unique;
	}

	sub get_blocker_ids_deep {
	my ($bug_id, $ret) = @_;
	my $deps = Bugzilla::Bug::EmitDependList("blocked", "dependson", $bug_id);
	push @{$ret}, @$deps;
	foreach $bug_id (@$deps) {
	get_blocker_ids_deep($bug_id, $ret);
	}
	}

	#
	# Queries and data structure assembly
	#

	sub query_work_by_buglist {
	my ($bugids, $start_date, $end_date) = @_;
	my $dbh = Bugzilla->dbh;

	my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);

	# $bugids is guaranteed to be non-empty because at least one bug is
	# always provided to this page.
	my $buglist = join ", ", @{$bugids};

	# Returns the total time worked on each bug per developer, with
	# bug descriptions and developer address
	my $q = qq{SELECT sum(longdescs.work_time) as total_time,
	profiles.login_name,
	longdescs.bug_id,
	bugs.short_desc,
	bugs.bug_status
	FROM longdescs
	INNER JOIN profiles
	ON longdescs.who = profiles.userid
	INNER JOIN bugs
	ON bugs.bug_id = longdescs.bug_id
	WHERE longdescs.bug_id IN ($buglist)
	$date_bits } .
	$dbh->sql_group_by('longdescs.bug_id, profiles.login_name',
	'bugs.short_desc, bugs.bug_status, longdescs.bug_when') . qq{
	ORDER BY longdescs.bug_when};
	my $sth = $dbh->prepare($q);
	$sth->execute(@{$date_values});
	return $sth;
	}

	sub get_work_by_owners {
	my $sth = query_work_by_buglist(@_);
	my %res;
	while (my $row = $sth->fetch) {
	# XXX: Why do we need to check if the total time is positive
	# instead of using SQL to do that? Simply because MySQL 3.x's
	# GROUP BY doesn't work correctly with aggregates. This is
	# really annoying, but I've spent a long time trying to wrestle
	# with it and it just doesn't seem to work. Should work OK in
	# 4.x, though.
	if ($row->[0] > 0) {
	my $login_name = $row->[1];
	push @{$res{$login_name}}, { total_time => $row->[0],
	bug_id => $row->[2],
	short_desc => $row->[3],
	bug_status => $row->[4] };
	}
	}
	return \%res;
	}

	sub get_work_by_bugs {
	my $sth = query_work_by_buglist(@_);
	my %res;
	while (my $row = $sth->fetch) {
	# Perl doesn't let me use arrays as keys :-(
	# merge in ID, status and summary
	my $bug = join ";", ($row->[2], $row->[4], $row->[3]);
	# XXX: see comment in get_work_by_owners
	if ($row->[0] > 0) {
	push @{$res{$bug}}, { total_time => $row->[0],
	login_name => $row->[1], };
	}
	}
	return \%res;
	}

	sub get_inactive_bugs {
	my ($bugids, $start_date, $end_date) = @_;
	my $dbh = Bugzilla->dbh;
	my ($date_bits, $date_values) = sqlize_dates($start_date, $end_date);
	my $buglist = join ", ", @{$bugids};

	my %res;
	# This sucks. I need to make sure that even bugs that don't show
	# up in the longdescs query (because no comments were filed during
	# the specified period) but are dependent on the parent bug show
	# up in the results if they have no work done; that's why I prefill
	# them in %res here and then remove them below.
	my $q = qq{SELECT DISTINCT bugs.bug_id, bugs.short_desc ,
	bugs.bug_status
	FROM longdescs
	INNER JOIN bugs
	ON longdescs.bug_id = bugs.bug_id
	WHERE longdescs.bug_id in ($buglist)};
	my $sth = $dbh->prepare($q);
	$sth->execute();
	while (my $row = $sth->fetch) {
	$res{$row->[0]} = [$row->[1], $row->[2]];
	}

	# Returns the total time worked on each bug, with description. This
	# query differs a bit from one in the query_work_by_buglist and I
	# avoided complicating that one just to make it more general.
	$q = qq{SELECT sum(longdescs.work_time) as total_time,
	longdescs.bug_id,
	bugs.short_desc,
	bugs.bug_status
	FROM longdescs
	INNER JOIN bugs
	ON bugs.bug_id = longdescs.bug_id
	WHERE longdescs.bug_id IN ($buglist)
	$date_bits } .
	$dbh->sql_group_by('longdescs.bug_id',
	'bugs.short_desc, bugs.bug_status,
	longdescs.bug_when') . qq{
	ORDER BY longdescs.bug_when};
	$sth = $dbh->prepare($q);
	$sth->execute(@{$date_values});
	while (my $row = $sth->fetch) {
	# XXX: see comment in get_work_by_owners
	if ($row->[0] == 0) {
	$res{$row->[1]} = [$row->[2], $row->[3]];
	} else {
	delete $res{$row->[1]};
	}
	}
	return \%res;
	}

	#
	# Misc
	#

	sub sort_bug_keys {
	# XXX a hack is the mother of all evils. The fact that we store keys
	# joined by semi-colons in the workdata-by-bug structure forces us to
	# write this evil comparison function to ensure we can process the
	# data timely -- just pushing it through a numerical sort makes TT
	# hang while generating output :-(
	my $list = shift;
	my @a;
	my @b;
	return sort { @a = split(";", $a);
	@b = split(";", $b);
	$a[0] <=> $b[0] } @{$list};
	}

	#
	# Template code starts here
	#

	Bugzilla->login(LOGIN_REQUIRED);

	my $cgi = Bugzilla->cgi;

	Bugzilla->switch_to_shadow_db();

	UserInGroup(Param("timetrackinggroup"))
	\|\| ThrowUserError("auth_failure", {group => "time-tracking",
	action => "access",
	object => "timetracking_summaries"});

	my @ids = split(",", $cgi->param('id'));
	map { ValidateBugID($_) } @ids;
	@ids = map { detaint_natural($_) && $_ } @ids;
	@ids = grep { Bugzilla->user->can_see_bug($_) } @ids;

	my $group_by = $cgi->param('group_by') \|\| "number";
	my $monthly = $cgi->param('monthly');
	my $detailed = $cgi->param('detailed');
	my $do_report = $cgi->param('do_report');
	my $inactive = $cgi->param('inactive');
	my $do_depends = $cgi->param('do_depends');
	my $ctype = scalar($cgi->param("ctype"));

	my ($start_date, $end_date);
	if ($do_report && @ids) {
	my @bugs = @ids;

	# Dependency mode requires a single bug and grabs dependents.
	if ($do_depends) {
	if (scalar(@bugs) != 1) {
	ThrowCodeError("bad_arg", { argument=>"id",
	function=>"summarize_time"});
	}
	@bugs = get_blocker_ids_unique($bugs[0]);
	@bugs = grep { Bugzilla->user->can_see_bug($_) } @bugs;
	}

	$start_date = trim $cgi->param('start_date');
	$end_date = trim $cgi->param('end_date');

	# Swap dates in case the user put an end_date before the start_date
	if ($start_date && $end_date &&
	str2time($start_date) > str2time($end_date)) {
	$vars->{'warn_swap_dates'} = 1;
	($start_date, $end_date) = ($end_date, $start_date);
	}
	($start_date, $end_date) = check_dates($start_date, $end_date);

	if ($detailed) {
	my %detail_data;
	my $res = include_tt_details(\%detail_data, \@bugs, $start_date, $end_date);

	$vars->{'detail_data'} = $res;
	}

	# Store dates ia session cookie the dates so re-visiting the page
	# for other bugs keeps them around.
	$cgi->send_cookie(-name => 'time-summary-dates',
	-value => join ";", ($start_date, $end_date));

	my (@parts, $part_data, @part_list);

	# Break dates apart into months if necessary; if not, we use the
	# same @parts list to allow us to use a common codepath.
	if ($monthly) {
	# unfortunately it's not too easy to guess a start date, since
	# it depends on what bugs we're looking at. We risk bothering
	# the user here. XXX: perhaps run a query to see what the
	# earliest activity in longdescs for all bugs and use that as a
	# start date.
	$start_date \|\| ThrowUserError("illegal_date", {'date' => $start_date});
	# we can, however, provide a default end date. Note that this
	# differs in semantics from the open-ended queries we use when
	# start/end_date aren't provided -- and clock skews will make
	# this evident!
	@parts = split_by_month($start_date,
	$end_date \|\| time2str("%Y-%m-%d", time()));
	} else {
	@parts = ([$start_date, $end_date]);
	}

	my %empty_hash;
	# For each of the separate divisions, grab the relevant summaries
	foreach my $part (@parts) {
	my ($sub_start, $sub_end) = @{$part};
	if (@bugs) {
	if ($group_by eq "owner") {
	$part_data = get_work_by_owners(\@bugs, $sub_start, $sub_end);
	} else {
	$part_data = get_work_by_bugs(\@bugs, $sub_start, $sub_end);
	}
	} else {
	# $part_data must be a reference to a hash
	$part_data = \%empty_hash;
	}
	push @part_list, $part_data;
	}

	if ($inactive && @bugs) {
	$vars->{'null'} = get_inactive_bugs(\@bugs, $start_date, $end_date);
	} else {
	$vars->{'null'} = \%empty_hash;
	}

	$vars->{'part_list'} = \@part_list;
	$vars->{'parts'} = \@parts;

	} elsif ($cgi->cookie("time-summary-dates")) {
	($start_date, $end_date) = split ";", $cgi->cookie('time-summary-dates');
	}

	$vars->{'ids'} = \@ids;
	$vars->{'start_date'} = $start_date;
	$vars->{'end_date'} = $end_date;
	$vars->{'group_by'} = $group_by;
	$vars->{'monthly'} = $monthly;
	$vars->{'detailed'} = $detailed;
	$vars->{'inactive'} = $inactive;
	$vars->{'do_report'} = $do_report;
	$vars->{'do_depends'} = $do_depends;
	$vars->{'check_time'} = \&check_time;
	$vars->{'sort_bug_keys'} = \&sort_bug_keys;
	$vars->{'GetBugLink'} = \&GetBugLink;

	$ctype = "html" if !$ctype;
	my $format = GetFormat("bug/summarize-time", undef, $ctype);

	# Get the proper content-type
	print $cgi->header(-type=> Bugzilla::Constants::contenttypes->{$ctype});
	$template->process("$format->{'template'}", $vars)
	\|\| ThrowTemplateError($template->error());