Bug 185090: Add revamped whining system

patch by: Erik
r=joel
r=jouni
a=justdave

13 files changed, 1786 insertions, 7 deletions

diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm
index 66087b81c..18ff93392 100644
--- a/Bugzilla/User.pm
+++ b/Bugzilla/User.pm
@@ -172,10 +172,14 @@ sub queries {
     return [] unless $self->id;
 
     my $dbh = Bugzilla->dbh;
-    my $sth = $dbh->prepare(q{  SELECT name, query, linkinfooter
-                                  FROM namedqueries
-                                 WHERE userid=?
-                              ORDER BY UPPER(name)});
+    my $sth = $dbh->prepare(q{ SELECT
+                             DISTINCT name, query, linkinfooter,
+                                      IF(whine_queries.id IS NOT NULL, 1, 0)
+                                 FROM namedqueries
+                            LEFT JOIN whine_queries
+                                   ON whine_queries.query_name = name
+                                WHERE userid=?
+                             ORDER BY UPPER(name)});
     $sth->execute($self->{id});
 
     my @queries;
@@ -184,6 +188,7 @@ sub queries {
                           name         => $row->[0],
                           query        => $row->[1],
                           linkinfooter => $row->[2],
+                          usedinwhine  => $row->[3],
                         });
     }
     $self->{queries} = \@queries;
diff --git a/checksetup.pl b/checksetup.pl
index 2d96032f3..c42c23535 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -1262,7 +1262,8 @@ WriteParams();
 
 # These are the files which need to be marked executable
 my @executable_files = ('whineatnews.pl', 'collectstats.pl',
-   'checksetup.pl', 'importxml.pl', 'runtests.sh', 'testserver.pl');
+   'checksetup.pl', 'importxml.pl', 'runtests.sh', 'testserver.pl',
+   'whine.pl');
 
 # tell me if a file is executable.  All CGI files and those in @executable_files
 # are executable
@@ -1989,6 +1990,37 @@ $table{series_categories} =
      
      unique(name)';
      
+
+
+# whine system
+
+$table{whine_queries} =
+    'id             mediumint       auto_increment primary key,
+     eventid        mediumint       not null,
+     query_name     varchar(64)     not null default \'\',
+     sortkey        smallint        not null default 0,
+     onemailperbug  tinyint         not null default 0,
+     title          varchar(128)    not null,
+     
+     index(eventid)';
+
+$table{whine_schedules} =
+    'id             mediumint       auto_increment primary key,
+     eventid        mediumint       not null,
+     run_day        varchar(32),
+     run_time       varchar(32),
+     run_next       datetime,
+     mailto_userid  mediumint       not null,
+     
+     index(run_next),
+     index(eventid)';
+
+$table{whine_events} =
+    'id             mediumint       auto_increment primary key,
+     owner_userid   mediumint       not null,
+     subject        varchar(128),
+     body           mediumtext';
+
 ###########################################################################
 # Create tables
 ###########################################################################
@@ -4012,6 +4044,18 @@ if (!GroupDoesExist("canconfirm")) {
 
 }
 
+# Create bz_canusewhineatothers and bz_canusewhines
+if (!GroupDoesExist('bz_canusewhines')) {
+    my $whine_group = AddGroup('bz_canusewhines',
+                               'User can configure whine reports for self');
+    my $whineatothers_group = AddGroup('bz_canusewhineatothers',
+                                       'Can configure whine reports for ' .
+                                       'other users');
+    $dbh->do("INSERT IGNORE INTO group_group_map " .
+             "(member_id, grantor_id, grant_type) " .
+             "VALUES (${whine_group}, ${whineatothers_group}, " .
+             GROUP_MEMBERSHIP . ")");
+}
 
 ###########################################################################
 # Create Administrator  --ADMIN--
diff --git a/editwhines.cgi b/editwhines.cgi
new file mode 100755
index 000000000..5610f7eaa
--- /dev/null
+++ b/editwhines.cgi
@@ -0,0 +1,433 @@
+#!/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.
+#
+# The Initial Developer of the Original Code is Netscape Communications
+# Corporation. Portions created by Netscape are
+# Copyright (C) 1998 Netscape Communications Corporation. All
+# Rights Reserved.
+#
+# Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+#
+
+################################################################################
+# Script Initialization
+################################################################################
+
+use strict;
+
+use lib ".";
+require "CGI.pl";
+require "globals.pl";
+
+use vars qw( $vars );
+
+use Bugzilla::Constants;
+use Bugzilla::User;
+# require the user to have logged in
+Bugzilla->login(LOGIN_REQUIRED);
+
+###############################################################################
+# Main Body Execution
+###############################################################################
+
+my $cgi      = Bugzilla->cgi;
+my $template = Bugzilla->template;
+my $dbh      = Bugzilla->dbh;
+
+my $user     = Bugzilla->user;
+my $userid   = $user->id;
+
+my $sth; # database statement handle
+
+# $events is a hash ref, keyed by event id, that stores the active user's
+# events.  It starts off with:
+#  'subject' - the subject line for the email message
+#  'body'    - the text to be sent at the top of the message
+#
+# Eventually, it winds up with:
+#  'queries'  - array ref containing hashes of:
+#       'name'          - the name of the saved query
+#       'title'         - The title line for the search results table
+#       'sort'          - Numeric sort ID
+#       'id'            - row ID for the query entry
+#       'onemailperbug' - whether a single message must be sent for each
+#                         result.
+#  'schedule' - array ref containing hashes of:
+#       'day' - Day or range of days this schedule will be run
+#       'time' - time or interval to run
+#       'mailto' - person who will receive the results
+#       'id' - row ID for the schedule
+my $events = get_events($userid);
+
+# First see if this user may use whines
+ThrowUserError('whine_access_denied') unless (UserInGroup('bz_canusewhines'));
+
+# May this user send mail to other users?
+my $can_mail_others = UserInGroup('bz_canusewhineatothers');
+
+# If the form was submitted, we need to look for what needs to be added or
+# removed, then what was altered.
+
+if ($cgi->param('update')) {
+    if ($cgi->param("add_event")) {
+        # we create a new event
+        $sth = $dbh->prepare("INSERT INTO whine_events " .
+                             "(owner_userid) " .
+                             "VALUES (?)");
+        $sth->execute($userid);
+    }
+    else {
+        for my $eventid (keys %{$events}) {
+            # delete an entire event
+            if ($cgi->param("remove_event_$eventid")) {
+                # We need to make sure these belong to the same user,
+                # otherwise we could simply delete whatever matched that ID.
+                #
+                # schedules
+                $sth = $dbh->prepare("SELECT whine_schedules.id " .
+                                     "FROM whine_schedules " .
+                                     "LEFT JOIN whine_events " .
+                                     "ON whine_events.id = " .
+                                     "whine_schedules.eventid " .
+                                     "WHERE whine_events.id = ? " .
+                                     "AND whine_events.owner_userid = ?");
+                $sth->execute($eventid, $userid);
+                my @ids = @{$sth->fetchall_arrayref};
+                $sth = $dbh->prepare("DELETE FROM whine_schedules "
+                    . "WHERE id=?");
+                for (@ids) {
+                    my $delete_id = $_->[0];
+                    $sth->execute($delete_id);
+                }
+
+                # queries
+                $sth = $dbh->prepare("SELECT whine_queries.id " .
+                                     "FROM whine_queries " .
+                                     "LEFT JOIN whine_events " .
+                                     "ON whine_events.id = " .
+                                     "whine_queries.eventid " .
+                                     "WHERE whine_events.id = ? " .
+                                     "AND whine_events.owner_userid = ?");
+                $sth->execute($eventid, $userid);
+                @ids = @{$sth->fetchall_arrayref};
+                $sth = $dbh->prepare("DELETE FROM whine_queries " .
+                                     "WHERE id=?");
+                for (@ids) {
+                    my $delete_id = $_->[0];
+                    $sth->execute($delete_id);
+                }
+
+                # events
+                $sth = $dbh->prepare("DELETE FROM whine_events " .
+                                     "WHERE id=? AND owner_userid=?");
+                $sth->execute($eventid, $userid);
+            }
+            else {
+                # check the subject and body for changes
+                my $subject = ($cgi->param("event_${eventid}_subject") or '');
+                my $body    = ($cgi->param("event_${eventid}_body")    or '');
+
+                trick_taint($subject) if $subject;
+                trick_taint($body)    if $body;
+
+                if ( ($subject ne $events->{$eventid}->{'subject'})
+                  || ($body    ne $events->{$eventid}->{'body'}) ) {
+
+                    $sth = $dbh->prepare("UPDATE whine_events " .
+                                         "SET subject=?, body=? " .
+                                         "WHERE id=?");
+                    $sth->execute($subject, $body, $eventid);
+                }
+
+                # add a schedule
+                if ($cgi->param("add_schedule_$eventid")) {
+                    # the schedule table must be locked before altering
+                    $sth = $dbh->prepare("INSERT INTO whine_schedules " .
+                                         "(eventid, mailto_userid, " .
+                                         "run_day, run_time) " .
+                                         "VALUES (?, ?, 'Sun', 2)");
+                    $sth->execute($eventid, $userid);
+                }
+                # add a query
+                elsif ($cgi->param("add_query_$eventid")) {
+                    $sth = $dbh->prepare("INSERT INTO whine_queries "
+                        . "(eventid) "
+                        . "VALUES (?)");
+                    $sth->execute($eventid);
+                }
+            }
+
+            # now check all of the schedules and queries to see if they need
+            # to be altered or deleted
+
+            # Check schedules for changes
+            $sth = $dbh->prepare("SELECT id " .
+                                 "FROM whine_schedules " .
+                                 "WHERE eventid=?");
+            $sth->execute($eventid);
+            my @scheduleids = ();
+            for (@{$sth->fetchall_arrayref}) {
+                push @scheduleids, $_->[0];
+            };
+
+            # we need to double-check all of the user IDs in mailto to make
+            # sure they exist
+            my $arglist = {};   # args for match_field
+            for my $sid (@scheduleids) {
+                $arglist->{"mailto_$sid"} = {
+                    'type' => 'single',
+                };
+            }
+            if (scalar %{$arglist}) {
+                &Bugzilla::User::match_field($arglist);
+            }
+
+            for my $sid (@scheduleids) {
+                if ($cgi->param("remove_schedule_$sid")) {
+                    # having the owner id in here is a security failsafe
+                    $sth = $dbh->prepare("SELECT whine_schedules.id " .
+                                         "FROM whine_schedules " .
+                                         "LEFT JOIN whine_events " .
+                                         "ON whine_events.id = " .
+                                         "whine_schedules.eventid " .
+                                         "WHERE whine_events.owner_userid=? " .
+                                         "AND whine_schedules.id =?");
+                    $sth->execute($userid, $sid);
+
+                    my @ids = @{$sth->fetchall_arrayref};
+                    for (@ids) {
+                        $sth = $dbh->prepare("DELETE FROM whine_schedules " .
+                                             "WHERE id=?");
+                        $sth->execute($_->[0]);
+                    }
+                }
+                else {
+                    my $o_day    = $cgi->param("orig_day_$sid");
+                    my $day      = $cgi->param("day_$sid");
+                    my $o_time   = $cgi->param("orig_time_$sid");
+                    my $time     = $cgi->param("time_$sid");
+                    my $o_mailto = $cgi->param("orig_mailto_$sid");
+                    my $mailto   = $cgi->param("mailto_$sid");
+
+                    $o_day    = '' unless length($o_day);
+                    $o_time   = '' unless length($o_time);
+                    $o_mailto = '' unless length($o_mailto);
+                    $day      = '' unless length($day);
+                    $time     = '' unless length($time);
+                    $mailto   = '' unless length($mailto);
+
+                    my $mail_uid = $userid;
+
+                    # get a userid for the mailto address
+                    if ($can_mail_others and $mailto) {
+                        trick_taint($mailto);
+                        $mail_uid = DBname_to_id($mailto);
+                    }
+
+                    if ( ($o_day  ne $day) ||
+                         ($o_time ne $time) ){
+
+                        trick_taint($day) if length($day);
+                        trick_taint($time) if length($time);
+
+                        # the schedule table must be locked
+                        $sth = $dbh->prepare("UPDATE whine_schedules " .
+                                             "SET run_day=?, run_time=?, " .
+                                             "mailto_userid=?, " .
+                                             "run_next=NULL " .
+                                             "WHERE id=?");
+                        $sth->execute($day, $time, $mail_uid, $sid);
+                    }
+                }
+            }
+
+            # Check queries for changes
+            $sth = $dbh->prepare("SELECT id " .
+                                 "FROM whine_queries " .
+                                 "WHERE eventid=?");
+            $sth->execute($eventid);
+            my @queries = ();
+            for (@{$sth->fetchall_arrayref}) {
+                push @queries, $_->[0];
+            };
+
+            for my $qid (@queries) {
+                if ($cgi->param("remove_query_$qid")) {
+
+                    $sth = $dbh->prepare("SELECT whine_queries.id " .
+                                         "FROM whine_queries " .
+                                         "LEFT JOIN whine_events " .
+                                         "ON whine_events.id = " .
+                                         "whine_queries.eventid " .
+                                         "WHERE whine_events.owner_userid=? " .
+                                         "AND whine_queries.id =?");
+                    $sth->execute($userid, $qid);
+
+                    for (@{$sth->fetchall_arrayref}) {
+                        $sth = $dbh->prepare("DELETE FROM whine_queries " .
+                                             "WHERE id=?");
+                        $sth->execute($_->[0]);
+                    }
+                }
+                else {
+                    my $o_sort      = $cgi->param("orig_query_sort_$qid");
+                    my $sort        = $cgi->param("query_sort_$qid");
+                    my $o_queryname = $cgi->param("orig_query_name_$qid");
+                    my $queryname   = $cgi->param("query_name_$qid");
+                    my $o_title     = $cgi->param("orig_query_title_$qid");
+                    my $title       = $cgi->param("query_title_$qid");
+                    my $o_onemailperbug =
+                            $cgi->param("orig_query_onemailperbug_$qid");
+                    my $onemailperbug   =
+                            $cgi->param("query_onemailperbug_$qid");
+
+                    $o_sort          = '' unless length($o_sort);
+                    $o_queryname     = '' unless length($o_queryname);
+                    $o_title         = '' unless length($o_title);
+                    $o_onemailperbug = '' unless length($o_onemailperbug);
+                    $sort            = '' unless length($sort);
+                    $queryname       = '' unless length($queryname);
+                    $title           = '' unless length($title);
+                    $onemailperbug   = '' unless length($onemailperbug);
+
+                    if ($onemailperbug eq 'on') {
+                        $onemailperbug = 1;
+                    }
+                    elsif ($onemailperbug eq 'off') {
+                        $onemailperbug = 0;
+                    }
+
+                    if ( ($o_sort ne $sort) ||
+                         ($o_queryname ne $queryname) ||
+                         ($o_onemailperbug xor $onemailperbug) ||
+                         ($o_title ne $title) ){
+
+                        detaint_natural($sort)      if length $sort;
+                        trick_taint($queryname)     if length $queryname;
+                        trick_taint($title)         if length $title;
+                        trick_taint($onemailperbug) if length $onemailperbug;
+
+                        $sth = $dbh->prepare("UPDATE whine_queries " .
+                                             "SET sortkey=?, " .
+                                             "query_name=?, " .
+                                             "title=?, " .
+                                             "onemailperbug=? " .
+                                             "WHERE id=?");
+                        $sth->execute($sort, $queryname, $title,
+                                      $onemailperbug, $qid);
+                    }
+                }
+            }
+        }
+    }
+}
+
+$vars->{'mail_others'} = $can_mail_others;
+
+# Return the appropriate HTTP response headers.
+print $cgi->header();
+
+# Get events again, to cover any updates that were made
+$events = get_events($userid);
+
+# Here is the data layout as sent to the template:
+#
+#   events
+#       event_id #
+#           schedule
+#               day
+#               time
+#               mailto
+#           queries
+#               name
+#               title
+#               sort
+#
+# build the whine list by event id
+for my $event_id (keys %{$events}) {
+
+    $events->{$event_id}->{'schedule'} = [];
+    $events->{$event_id}->{'queries'} = [];
+
+    # schedules
+    $sth = $dbh->prepare("SELECT run_day, run_time, profiles.login_name, id " .
+                         "FROM whine_schedules " .
+                         "LEFT JOIN profiles " .
+                         "ON whine_schedules.mailto_userid = " .
+                         "profiles.userid " .
+                         "WHERE eventid=?");
+    $sth->execute($event_id);
+    for my $row (@{$sth->fetchall_arrayref}) {
+        my $this_schedule = {
+            'day'    => $row->[0],
+            'time'   => $row->[1],
+            'mailto' => $row->[2],
+            'id'     => $row->[3],
+        };
+        push @{$events->{$event_id}->{'schedule'}}, $this_schedule;
+    }
+
+    # queries
+    $sth = $dbh->prepare("SELECT query_name, title, sortkey, id, " .
+                         "onemailperbug " .
+                         "FROM whine_queries " .
+                         "WHERE eventid=? " .
+                         "ORDER BY sortkey");
+    $sth->execute($event_id);
+    for my $row (@{$sth->fetchall_arrayref}) {
+        my $this_query = {
+            'name'          => $row->[0],
+            'title'         => $row->[1],
+            'sort'          => $row->[2],
+            'id'            => $row->[3],
+            'onemailperbug' => $row->[4],
+        };
+        push @{$events->{$event_id}->{'queries'}}, $this_query;
+    }
+}
+
+$vars->{'events'} = $events;
+
+# get the available queries
+$sth = $dbh->prepare("SELECT name FROM namedqueries WHERE userid=?");
+$sth->execute($userid);
+
+$vars->{'available_queries'} = [];
+while (my $query = $sth->fetch) {
+    push @{$vars->{'available_queries'}}, $query->[0];
+}
+
+$template->process("whine/schedule.html.tmpl", $vars)
+  || ThrowTemplateError($template->error());
+
+# get_events takes a userid and returns a hash, keyed by event ID, containing
+# the subject and body of each event that user owns
+sub get_events {
+    my $userid = shift;
+    my $events = {};
+
+    my $sth = $dbh->prepare("SELECT DISTINCT id, subject, body " .
+                            "FROM whine_events " .
+                            "WHERE owner_userid=?");
+    $sth->execute($userid);
+    for (@{$sth->fetchall_arrayref}) {
+        $events->{$_->[0]} = {
+            'subject' => $_->[1],
+            'body'    => $_->[2],
+        }
+    }
+    return $events;
+}
+
diff --git a/template/en/default/account/prefs/saved-searches.html.tmpl b/template/en/default/account/prefs/saved-searches.html.tmpl
index 5055565e3..cd251d542 100644
--- a/template/en/default/account/prefs/saved-searches.html.tmpl
+++ b/template/en/default/account/prefs/saved-searches.html.tmpl
@@ -70,8 +70,12 @@
           <a href="query.cgi?[% q.query FILTER html %]">Edit</a>
         </td>
         <td>
-          <a href="buglist.cgi?cmdtype=dorem&amp;remaction=forget&amp;namedcmd=
-                   [% q.name FILTER html %]">Forget</a>
+          [% IF q.usedinwhine %]
+            Remove from <a href="editwhines.cgi">whining</a> first
+          [% ELSE %]
+            <a href="buglist.cgi?cmdtype=dorem&amp;remaction=forget&amp;namedcmd=
+                     [% q.name FILTER html %]">Forget</a>
+          [% END %]
         </td>
         <td align="center">
           <input type="checkbox" 
diff --git a/template/en/default/filterexceptions.pl b/template/en/default/filterexceptions.pl
index f183461ce..8d25e2536 100644
--- a/template/en/default/filterexceptions.pl
+++ b/template/en/default/filterexceptions.pl
@@ -37,6 +37,19 @@
 
 %::safe = (
 
+'whine/schedule.html.tmpl' => [
+  'event.key',
+  'query.id',
+  'query.sort',
+  'schedule.id',
+  'option.0',
+  'option.1',
+],
+
+'whine/mail.html.tmpl' => [
+  'bug.bug_id',
+],
+
 'sidebar.xul.tmpl' => [
   'template_version', 
 ],
diff --git a/template/en/default/global/site-navigation.html.tmpl b/template/en/default/global/site-navigation.html.tmpl
index 189d596fc..95a7ef423 100644
--- a/template/en/default/global/site-navigation.html.tmpl
+++ b/template/en/default/global/site-navigation.html.tmpl
@@ -104,6 +104,8 @@
               href="editgroups.cgi">' IF user.groups.creategroups %]
     [% '<link rel="Administration" title="Keywords"      
               href="editkeywords.cgi">' IF user.groups.editkeywords %]
+    [% '<link rel="Administration" title="Whining"       
+              href="editwhines.cgi">' IF user.groups.bz_canusewhines %]
     [% '<link rel="Administration" title="Sanity Check"  
               href="sanitycheck.cgi">' IF user.groups.tweakparams %]
   [% END %]  
diff --git a/template/en/default/global/useful-links.html.tmpl b/template/en/default/global/useful-links.html.tmpl
index f148d7d2f..c04b60dd6 100644
--- a/template/en/default/global/useful-links.html.tmpl
+++ b/template/en/default/global/useful-links.html.tmpl
@@ -81,6 +81,8 @@
                                                  IF user.groups.creategroups %]
         [% ' | <a href="editkeywords.cgi">Keywords</a>' 
                                                  IF user.groups.editkeywords %]
+        [% ' | <a href="editwhines.cgi">Whining</a>' 
+                                              IF user.groups.bz_canusewhines %]
     </div>
   </div>
     [% END %]
diff --git a/template/en/default/global/user-error.html.tmpl b/template/en/default/global/user-error.html.tmpl
index 28002581a..964832db6 100644
--- a/template/en/default/global/user-error.html.tmpl
+++ b/template/en/default/global/user-error.html.tmpl
@@ -846,6 +846,11 @@
     Value is out of range for field 
     <em>[% field_descs.$field FILTER html %]</em>.
 
+  [% ELSIF error == "whine_access_denied" %]
+    [% title = "Access Denied" %]
+    Sorry, you aren't a member of the 'bz_canusewhines' group, and so
+    you aren't allowed to schedule whine reports.
+
   [% ELSIF error == "zero_length_file" %]
     [% title = "File Is Empty" %]
     The file you are trying to attach is empty!
diff --git a/template/en/default/whine/mail.html.tmpl b/template/en/default/whine/mail.html.tmpl
new file mode 100644
index 000000000..9d85c0962
--- /dev/null
+++ b/template/en/default/whine/mail.html.tmpl
@@ -0,0 +1,96 @@
+[%# 1.0@bugzilla.org %]
+[%# 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.
+  #
+  # The Initial Developer of the Original Code is Netscape Communications
+  # Corporation. Portions created by Netscape are
+  # Copyright (C) 1998 Netscape Communications Corporation. All
+  # Rights Reserved.
+  #
+  # Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+  #%]
+
+[%# INTERFACE:
+  # subject: subject line of message
+  # body: message body, shown before the query tables
+  # queries: array of hashes containing:
+  #     bugs: array containing hashes of fieldnames->values for each bug
+  #     title: the title given in the whine scheduling mechanism
+  # author: user object for the person who scheduled this whine
+  # recipient: user object for the intended recipient of the message
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+[%# assignee_login_string is a literal string used for getting the 
+  # assignee's name out of the bug data %]
+[% SET assignee_login_string="map_assigned_to.login_name" %]
+
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
+<html>
+  <head>
+    <title>
+      [[% terms.Bugzilla %]] [% subject FILTER html %]
+    </title>
+  </head>
+  <body bgcolor="#FFFFFF">
+
+    <p align="left">
+      [% body FILTER html %]
+    </p>
+
+    <p align="left">
+      [% IF author.login == recipient.login %]
+        <a href="[%+ Param('urlbase') FILTER html %]editwhines.cgi">Click
+            here to edit your whine schedule</a>
+      [% ELSE %]
+        This search was scheduled by [% author.login FILTER html %].
+      [% END %]
+    </p>
+
+
+[% FOREACH query=queries %]
+
+  <h2>[%+ query.title FILTER html %]</h2>
+
+  <table width="100%">
+    <tr>
+      <th align="left">ID</th>
+      <th align="left">Sev</th>
+      <th align="left">Pri</th>
+      <th align="left">Plt</th>
+      <th align="left">Assignee</th>
+      <th align="left">Status</th>
+      <th align="left">Resolution</th>
+      <th align="left">Summary</th>
+    </tr>
+
+    [% FOREACH bug=query.bugs %]
+      <tr>
+        <td align="left"><a href="[%+ Param('urlbase') FILTER html %]show_bug.cgi?id=
+            [%- bug.bug_id %]">[% bug.bug_id %]</a></td>
+        <td align="left">[% bug.bug_severity FILTER html %]</td>
+        <td align="left">[% bug.priority FILTER html %]</td>
+        <td align="left">[% bug.rep_platform FILTER html %]</td>
+        <td align="left">[% bug.$assignee_login_string FILTER html %]</td>
+        <td align="left">[% bug.bug_status FILTER html %]</td>
+        <td align="left">[% bug.resolution FILTER html %]</td>
+        <td align="left">[% bug.short_desc FILTER html %]</td>
+      </tr>
+    [% END %]
+  </table>
+[% END %]
+
+  </body>
+</html>
+
+
diff --git a/template/en/default/whine/mail.txt.tmpl b/template/en/default/whine/mail.txt.tmpl
new file mode 100644
index 000000000..1694203c1
--- /dev/null
+++ b/template/en/default/whine/mail.txt.tmpl
@@ -0,0 +1,69 @@
+[%# 1.0@bugzilla.org %]
+[%# 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.
+  #
+  # The Initial Developer of the Original Code is Netscape Communications
+  # Corporation. Portions created by Netscape are
+  # Copyright (C) 1998 Netscape Communications Corporation. All
+  # Rights Reserved.
+  #
+  # Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+  #%]
+
+[%# INTERFACE:
+  # subject: subject line of message
+  # body: message body, shown before the query tables
+  # queries: array of hashes containing:
+  #     bugs: array containing hashes of fieldnames->values for each bug
+  #     title: the title given in the whine scheduling mechanism
+  # author: user object for the person who scheduled this whine
+  # recipient: user object for the intended recipient of the message
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+[%# assignee_login_string is a literal string used for getting the 
+  # assignee's name out of the bug data %]
+[% SET assignee_login_string="map_assigned_to.login_name" %]
+
+[% body %]
+
+[% IF author.login == recipient.login %]
+  To edit your whine schedule, visit the following URL:
+  [%+ Param('urlbase') %]editwhines.cgi
+[% ELSE %]
+  This search was scheduled by [% author.login %].
+[% END %]
+
+
+[% FOREACH query=queries %]
+
+[%+ query.title +%]
+[%+ "-" FILTER repeat(query.title.length) %]
+
+ [% FOREACH bug=query.bugs %]
+  [% terms.Bug +%] [%+ bug.bug_id %]:
+  [%+ Param('urlbase') %]show_bug.cgi?id=[% bug.bug_id +%]
+  Priority: [%+ bug.priority -%]
+  Severity: [%+ bug.bug_severity -%]
+  Platform: [%+ bug.rep_platform %]
+  Assignee: [%+ bug.$assignee_login_string %]
+    Status: [%+ bug.bug_status %]
+            [%- IF bug.resolution -%] Resolution: [% bug.resolution -%]
+                                [%- END %]
+   Summary: [% bug.short_desc %]
+
+ [% END %]
+
+[% END %]
+
+
diff --git a/template/en/default/whine/multipart-mime.txt.tmpl b/template/en/default/whine/multipart-mime.txt.tmpl
new file mode 100644
index 000000000..7d5334d04
--- /dev/null
+++ b/template/en/default/whine/multipart-mime.txt.tmpl
@@ -0,0 +1,52 @@
+[%# 1.0@bugzilla.org %]
+[%# 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.
+  #
+  # The Initial Developer of the Original Code is Netscape Communications
+  # Corporation. Portions created by Netscape are
+  # Copyright (C) 1998 Netscape Communications Corporation. All
+  # Rights Reserved.
+  #
+  # Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+  #%]
+
+[%# INTERFACE:
+  # subject: subject line of message
+  # alternatives: array of hashes containing:
+  #     type: MIME type
+  #     content: verbatim content
+  # boundary: a string that has been generated to be a unique boundary
+  # recipient: user object for the intended recipient of the message
+  # from: Bugzilla system email address
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+From: [% from %]
+To: [% recipient.login %]
+Subject: [[% terms.Bugzilla %]] [% subject %]
+MIME-Version: 1.0
+Content-Type: multipart/alternative; boundary="[% boundary %]"
+
+
+This is a MIME multipart message.  It is possible that your mail program
+doesn't quite handle these properly.  Some or all of the information in this
+message may be unreadable.
+
+
+[% FOREACH part=alternatives %]
+
+--[% boundary %]
+Content-type: [% part.type +%]
+
+[%+ part.content %]
+[%+ END %]
diff --git a/template/en/default/whine/schedule.html.tmpl b/template/en/default/whine/schedule.html.tmpl
new file mode 100644
index 000000000..60c0f3cd8
--- /dev/null
+++ b/template/en/default/whine/schedule.html.tmpl
@@ -0,0 +1,406 @@
+[%# 1.0@bugzilla.org %]
+[%# -*- mode: html -*- %]
+[%# 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.
+  #
+  # The Initial Developer of the Original Code is Netscape Communications
+  # Corporation. Portions created by Netscape are
+  # Copyright (C) 1998 Netscape Communications Corporation. All
+  # Rights Reserved.
+  #
+  # Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+  #%]
+
+[%# INTERFACE:
+  # events: hash, keyed by event_id number.  Values are anonymous hashes of:
+  #     schedule: array of hashes containing schedule info:
+  #         day:    value in day column
+  #         time:   value selected in time column
+  #         mailto: recipient's email address
+  #     queries:  as with schedule, an anonymous array containing hashes of:
+  #         name:  the named query's name
+  #         title: title to be displayed on the results
+  #         sort:  integer that sets execution order on named queries
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+[% title = "Set up whining" %]
+[% PROCESS global/header.html.tmpl %]
+
+<p>
+  "Whining" is when [% terms.Bugzilla %] executes a saved query at a regular interval
+  and sends the resulting list of [% terms.bugs %] via email.
+</p>
+
+<p>
+  To set up a new whine event, click "Add a new event."  Enter a subject line
+  for the message that will be sent, along with a block of text that will
+  accompany the [% terms.bug %] list in the body of the message.
+</p>
+
+<p>
+  Schedules are added to an event by clicking on "Add a new schedule."  A schedule
+  consists of a day, a time of day or interval of times
+  (e.g., every 15 minutes), and a target email address that may or may not be
+  alterable, depending on your privileges.  Events may have more than one schedule
+  in order to run at multiple times or for different users.
+</p>
+
+<p>
+  Queries come from saved searches, which are created by executing a <a
+  href="query.cgi">search</a>, then telling [% terms.Bugzilla %] to remember
+  the search under a particular name.  Add a query by clicking "Add a new
+  query", and select the desired saved search name under "Search" and add a
+  title for the [% terms.bug %] table.  The optional number entered under
+  "Sort" will determine the execution order (lowest to highest) if multiple
+  queries are listed.  If you check "One message per [% terms.bug %]," each [%
+  terms.bug %] that matches the search will be sent in its own email message.
+</p>
+
+<form method="post" action="editwhines.cgi">
+[%# This hidden submit button must be here to set default behavior when
+    the user presses return on a form input field #%]
+<input type="submit" value="Update / Commit" name="commit"
+       style="visibility: hidden">
+<input type="hidden" name="update" value="1">
+
+[% FOREACH event = events %]
+
+<table cellspacing="2px" cellpadding="2px" border="0" width="100%"
+       style="border: 1px solid;">
+  <tr>
+    <th align="left" bgcolor="#FFFFFF" colspan="2">
+      Event: 
+    </th>
+    <td align="right">
+      <input type="submit" value="Remove Event"
+             name="remove_event_[% event.key %]">
+    </td>
+  </tr>
+
+  <tr>
+    <td valign="top" align="right">
+      Email subject line:
+    </td>
+    <td>
+      <input type="text" name="event_[% event.key %]_subject"
+             size="60" maxlength="128" value="
+             [%- event.value.subject FILTER html %]">
+    </td>
+  </tr>
+
+  <tr>
+    <td valign="top" align="right">
+      Descriptive text sent within whine message:
+    </td>
+    <td>
+      <textarea name="event_[% event.key %]_body"
+                rows="5" cols="80">
+          [% event.value.body FILTER html %]</textarea>
+    </td>
+  </tr>
+
+  [% IF event.value.schedule.size == 0 %]
+
+    <tr>
+      <td valign="top" align="right">
+        Schedule:
+      </td>
+      <td align="left" bgcolor="#FFEEEE">
+        Not scheduled to run<br>
+        <input type="submit" value="Add a new schedule"
+               name="add_schedule_[% event.key %]">
+      </td>
+    </tr>
+
+  [% ELSE %]
+
+    <tr>
+      <td valign="top" align="right">
+        Schedule:
+      </td>
+      <td align="left" bgcolor="#EEFFEE">
+
+        <table>
+          <tr>
+            <th>
+              Interval
+            </th>
+            [% IF mail_others %]
+              <th>
+                Mail to
+              </th>
+            [% END %]
+          </tr>
+          [% FOREACH schedule = event.value.schedule %]
+            <tr>
+              <td align="left">
+
+                [%# these hidden fields allow us to compare old values instead
+                    of reading the database to tell if a field has changed %]
+
+                <input type="hidden" value="[% schedule.day FILTER html %]"
+                       name="orig_day_[% schedule.id %]">
+                <input type="hidden" value="[% schedule.time FILTER html %]"
+                       name="orig_time_[% schedule.id %]">
+                [% PROCESS day_field val=schedule.day %]
+                [% PROCESS time_field val=schedule.time %]
+              </td>
+              <td align="left">
+                [% IF mail_others %]
+                  <input type="hidden" name="orig_mailto_[% schedule.id %]"
+                         value="[% schedule.mailto FILTER html %]">
+                  <input type="text" name="mailto_[% schedule.id %]"
+                         value="[% schedule.mailto FILTER html %]" size="30">
+                [% END %]
+              </td>
+              <td align="left">
+                <input type="submit" value="Remove"
+                       name="remove_schedule_[% schedule.id %]">
+              </td>
+            </tr>
+          [% END %]
+
+        <tr>
+          <td>
+            <input type="submit" value="Add a new schedule"
+                   name="add_schedule_[% event.key %]">
+          </td>
+        </tr>
+
+        </table>
+
+      </td>
+
+    </tr>
+
+  [% END %]
+
+  [% IF event.value.queries.size == 0 %]
+
+    <tr>
+      <td valign="top" align="right">
+        Queries:
+      </td>
+      <td align="left" colspan="1">
+        No queries <br>
+        <input type="submit" value="Add a new query" name="add_query_[% event.key %]">
+      </td>
+      <td align="right" valign="bottom">
+        <input type="submit" value="Update / Commit" name="commit">
+      </td>
+    </tr>
+
+  [% ELSE %]
+
+    <tr>
+      <td valign="top" align="right">
+        Queries:
+      </td>
+      <td align="left">
+
+        <table>
+          <tr>
+            <th>Sort</th>
+            <th>Search</th>
+            <th>Title</th>
+          </tr>
+
+          [% FOREACH query = event.value.queries %]
+
+            <tr>
+              <td align="left">
+                <input type="text" name="query_sort_[% query.id %]"
+                       size="3" value="[% query.sort %]">
+                <input type="hidden" value="[% query.sort %]"
+                       name="orig_query_sort_[% query.id %]">
+              </td>
+              <td align="left">
+                <input type="hidden" value="[% query.name FILTER html %]"
+                       name="orig_query_name_[% query.id %]">
+                [% PROCESS query_field thisquery=query.name %]
+              </td>
+              <td align="left">
+                <input type="hidden" value="[% query.title FILTER html %]"
+                       name="orig_query_title_[% query.id %]">
+                <input type="text" name="query_title_[% query.id %]"
+                       size="50" value="[% query.title FILTER html %]"
+                       maxlength="64">
+              </td>
+              <td align="left">
+                <input type="hidden" value="[% query.onemailperbug FILTER html %]"
+                       name="orig_query_onemailperbug_[% query.id %]">
+                <input type="checkbox" [% IF query.onemailperbug == 1 %]
+                       checked [% END %]name="query_onemailperbug_
+                       [% query.id %]">
+                One message per [% terms.bug %]
+              </td>
+              <td align="right">
+                <input type="submit" value="Remove"
+                       name="remove_query_[% query.id %]">
+              </td>
+            </tr>
+
+          [% END %]
+
+          <tr>
+            <td colspan="3">
+              <input type="submit" value="Add a new query"
+                     name="add_query_[% event.key %]">
+            </td>
+          </tr>
+
+        </table>
+
+      </td>
+      <td align="right" valign="bottom">
+        <input type="submit" value="Update / Commit" name="commit">
+      </td>
+    </tr>
+
+  [% END %]
+
+</table>
+
+[% END %]
+
+<p align="left">
+  <input type="submit" value="Add a new event" name="add_event">
+</p>
+
+</form>
+
+[% PROCESS global/footer.html.tmpl %]
+
+[% BLOCK query_field +%]
+
+  [% IF available_queries.size > 0 %]
+
+    <select name="query_name_[% query.id %]">
+      [% FOREACH q = available_queries %]
+        <option [% "selected" IF q == thisquery %] value="[% q FILTER html %]">
+            [% q FILTER html %]
+        </option>
+      [% END %]
+    </select>
+
+  [% ELSE %]
+    Please visit the <a href="query.cgi">Search</a> page and save a query
+  [% END %]
+
+[%+ END %]
+
+[% BLOCK day_field +%]
+  <select name="day_[% schedule.id %]">
+      [%
+        options = [
+            ['All',  'Each day',                 ],
+            ['MF',   'Monday through Friday',    ],
+            ['Sun',  'Sunday',                   ],
+            ['Mon',  'Monday',                   ],
+            ['Tue',  'Tuesday',                  ],
+            ['Wed',  'Wednesday',                ],
+            ['Thu',  'Thursday',                 ],
+            ['Fri',  'Friday',                   ],
+            ['Sat',  'Saturday',                 ],
+            ['1',    'On the 1st of the month',  ],
+            ['2',    'On the 2nd of the month',  ],
+            ['3',    'On the 3rd of the month',  ],
+            ['4',    'On the 4th of the month',  ],
+            ['5',    'On the 5th of the month',  ],
+            ['6',    'On the 6th of the month',  ],
+            ['7',    'On the 7th of the month',  ],
+            ['8',    'On the 8th of the month',  ],
+            ['9',    'On the 9th of the month',  ],
+            ['10',   'On the 10th of the month', ],
+            ['11',   'On the 11th of the month', ],
+            ['12',   'On the 12th of the month', ],
+            ['13',   'On the 13th of the month', ],
+            ['14',   'On the 14th of the month', ],
+            ['15',   'On the 15th of the month', ],
+            ['16',   'On the 16th of the month', ],
+            ['17',   'On the 17th of the month', ],
+            ['18',   'On the 18th of the month', ],
+            ['19',   'On the 19th of the month', ],
+            ['20',   'On the 20th of the month', ],
+            ['21',   'On the 21st of the month', ],
+            ['22',   'On the 22nd of the month', ],
+            ['23',   'On the 23rd of the month', ],
+            ['24',   'On the 24th of the month', ],
+            ['25',   'On the 25th of the month', ],
+            ['26',   'On the 26th of the month', ],
+            ['27',   'On the 27th of the month', ],
+            ['28',   'On the 28th of the month', ],
+            ['29',   'On the 29th of the month', ],
+            ['30',   'On the 30th of the month', ],
+            ['31',   'On the 31st of the month', ],
+            ['last', 'Last day of the month',    ],
+        ]
+      %]
+
+      [% FOREACH option = options %]
+        <option value="[% option.0 %]" 
+                [%- IF val == option.0 +%] selected[% END %]>
+                [%- option.1 -%]
+        </option>
+      [% END %]
+
+  </select>
+[%+ END %]
+
+[% BLOCK time_field +%]
+<select name="time_[% schedule.id %]">
+
+  [%
+    options = [
+        [ '0',     'at midnight',      ],
+        [ '1',     'at 01:00',         ],
+        [ '2',     'at 02:00',         ],
+        [ '3',     'at 03:00',         ],
+        [ '4',     'at 04:00',         ],
+        [ '5',     'at 05:00',         ],
+        [ '6',     'at 06:00',         ],
+        [ '7',     'at 07:00',         ],
+        [ '8',     'at 08:00',         ],
+        [ '9',     'at 09:00',         ],
+        [ '10',    'at 10:00',         ],
+        [ '11',    'at 11:00',         ],
+        [ '12',    'at 12:00',         ],
+        [ '13',    'at 13:00',         ],
+        [ '14',    'at 14:00',         ],
+        [ '15',    'at 15:00',         ],
+        [ '16',    'at 16:00',         ],
+        [ '17',    'at 17:00',         ],
+        [ '18',    'at 18:00',         ],
+        [ '19',    'at 19:00',         ],
+        [ '20',    'at 20:00',         ],
+        [ '21',    'at 21:00',         ],
+        [ '22',    'at 22:00',         ],
+        [ '23',    'at 23:00',         ],
+        [ '60min', 'every hour',       ],
+        [ '30min', 'every 30 minutes', ],
+        [ '15min', 'every 15 minutes', ],
+    ]
+  %]
+
+      [% FOREACH option = options %]
+        <option value="[% option.0 %]" 
+                [%- IF val == option.0 +%] selected[% END %]>
+                [%- option.1 -%]
+        </option>
+      [% END %]
+
+</select>
+
+[%+ END %]
+
diff --git a/whine.pl b/whine.pl
new file mode 100755
index 000000000..38cd84894
--- /dev/null
+++ b/whine.pl
@@ -0,0 +1,648 @@
+#!/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.
+#
+# The Initial Developer of the Original Code is Netscape Communications
+# Corporation. Portions created by Netscape are
+# Copyright (C) 1998 Netscape Communications Corporation. All
+# Rights Reserved.
+#
+# Contributor(s): Erik Stambaugh <erik@dasbistro.com>
+
+################################################################################
+# Script Initialization
+################################################################################
+
+use strict;
+
+use lib ".";
+require "globals.pl";
+
+use Bugzilla::Config qw(:DEFAULT $datadir);
+use Bugzilla::Constants;
+use Bugzilla::Search;
+use Bugzilla::User;
+
+# create some handles that we'll need
+my $template = Bugzilla->template;
+my $dbh      = Bugzilla->dbh;
+my $sth;
+
+# These statement handles should live outside of their functions in order to
+# allow the database to keep their SQL compiled.
+my $sth_run_queries =
+    $dbh->prepare("SELECT " .
+                  "id, query_name, title, onemailperbug " .
+                  "FROM whine_queries " .
+                  "WHERE eventid=? " .
+                  "ORDER BY sortkey");
+my $sth_get_query =
+    $dbh->prepare("SELECT query FROM namedqueries " .
+                  "WHERE userid = ? AND name = ?");
+
+# get the event that's scheduled with the lowest run_next value
+my $sth_next_scheduled_event = $dbh->prepare(
+    "SELECT " .
+    " whine_schedules.eventid, " .
+    " whine_events.owner_userid, " .
+    " whine_events.subject, " .
+    " whine_events.body " .
+    "FROM whine_schedules " .
+    "LEFT JOIN whine_events " .
+    " ON whine_events.id = whine_schedules.eventid " .
+    "WHERE run_next <= NOW() " .
+    "ORDER BY run_next LIMIT 1"
+);
+
+# get all pending schedules matching an eventid
+my $sth_schedules_by_event = $dbh->prepare(
+    "SELECT id, mailto_userid " .
+    "FROM whine_schedules " .
+    "WHERE eventid=? AND run_next <= NOW()"
+);
+
+
+################################################################################
+# Main Body Execution
+################################################################################
+
+# This script needs to check through the database for schedules that have
+# run_next set to NULL, which means that schedule is new or has been altered.
+# It then sets it to run immediately if the schedule entry has it running at
+# an interval like every hour, otherwise to the appropriate day and time.
+
+# After that, it looks over each user to see if they have schedules that need
+# running, then runs those and generates the email messages.
+
+# exit quietly if the system is shut down
+if (Param('shutdownhtml')) {
+    exit;
+}
+
+
+# Send whines from the maintainer address. It's not a good idea to use
+# the whine creator address because the admin can make more use of bounces and
+# other replies.
+my $fromaddress = Param('maintainer');
+
+if ($fromaddress !~ Param('emailregexp')) {
+    die "Cannot run.  " .
+        "The maintainer email address has not been properly set!\n";
+}
+
+# Check the nomail file for users who should not receive mail
+my %nomail;
+if (open(NOMAIL, '<', "$datadir/nomail")) {
+    while (<NOMAIL>) {
+        $nomail{trim($_)} = 1;
+    }
+}
+
+# get the current date and time from the database
+$sth = $dbh->prepare( 'SELECT DATE_FORMAT( NOW(), "%y,%m,%e,%w,%k,%i")');
+$sth->execute;
+my ($now_year, $now_month, $now_day, $now_weekday, $now_hour, $now_minute) =
+        split(',', $sth->fetchrow_array);
+$sth->finish;
+
+my @daysinmonth = qw(0 31 28 31 30 31 30 31 31 30 31 30 31);
+# Alter February in case of a leap year.  This simple way to do it only
+# applies if you won't be looking at February of next year, which whining
+# doesn't need to do.
+if (($now_year % 4 == 0) &&
+    (($now_year % 100 != 0) || ($now_year % 400 == 0))) {
+    $daysinmonth[2] = 29;
+}
+
+# run_day can contain either a calendar day (1, 2, 3...), a day of the week
+# (Mon, Tue, Wed...), a range of days (All, MF), or 'last' for the last day of
+# the month.
+#
+# run_time can contain either an hour (0, 1, 2...) or an interval
+# (60min, 30min, 15min).
+#
+# We go over each uninitialized schedule record and use its settings to
+# determine what the next time it runs should be
+my $sched_h = $dbh->prepare("SELECT id, run_day, run_time " .
+                            "FROM whine_schedules " .
+                            "WHERE run_next IS NULL" );
+$sched_h->execute();
+while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) {
+    # fill in some defaults in case they're blank
+    $day  ||= '0';
+    $time ||= '0';
+
+    # If this schedule is supposed to run today, we see if it's supposed to be
+    # run at a particular hour.  If so, we set it for that hour, and if not,
+    # it runs at an interval over the course of a day, which means we should
+    # set it to run immediately.
+    if (&check_today($day)) {
+        # Values that are not entirely numeric are intervals, like "30min"
+        if ($time !~ /^\d+$/) {
+            # set it to now
+            $sth = $dbh->prepare( "UPDATE whine_schedules " .
+                                  "SET run_next=NOW() " .
+                                  "WHERE id=?");
+            $sth->execute($schedule_id);
+        }
+        # A time greater than now means it still has to run today
+        elsif ($time >= $now_hour) {
+            # set it to today + number of hours
+            $sth = $dbh->prepare( "UPDATE whine_schedules " .
+                   "SET run_next=DATE_ADD(CURRENT_DATE(), INTERVAL ? HOUR) " .
+                   "WHERE id=?");
+            $sth->execute($time, $schedule_id);
+        }
+        # the target time is less than the current time
+        else { # set it for the next applicable day
+            my $nextdate = &get_next_date($day);
+            $sth = $dbh->prepare( "UPDATE whine_schedules " .
+                   "SET run_next=" .
+                   "DATE_ADD(?, INTERVAL ? HOUR) " .
+                   "WHERE id=?");
+            $sth->execute($nextdate, $time, $schedule_id);
+        }
+
+    }
+    # If the schedule is not supposed to run today, we set it to run on the
+    # appropriate date and time
+    else {
+        my $target_date = &get_next_date($day);
+        # If configured for a particular time, set it to that, otherwise
+        # midnight
+        my $target_time = ($time =~ /^\d+$/) ? $time : 0;
+
+        $sth = $dbh->prepare( "UPDATE whine_schedules " .
+               "SET run_next=DATE_ADD(?, INTERVAL ? HOUR) " .
+               "WHERE id=?");
+        $sth->execute($target_date, $target_time, $schedule_id);
+    }
+}
+$sched_h->finish();
+
+# get_next_event
+#
+# This function will:
+#   1. Lock whine_schedules
+#   2. Grab the most overdue pending schedules on the same event that must run
+#   3. Update those schedules' run_next value
+#   4. Unlock the table
+#   5. Return an event hashref
+#
+# The event hashref consists of:
+#   eventid - ID of the event 
+#   author  - user object for the event's creator
+#   users   - array of user objects for recipients
+#   subject - Subject line for the email
+#   body    - the text inserted above the bug lists
+
+sub get_next_event {
+    my $event = {};
+
+    # Loop until there's something to return
+    until (scalar keys %{$event}) {
+
+        $dbh->do("LOCK TABLE " .
+                 "whine_schedules WRITE, " .
+                 "whine_events READ, " .
+                 "profiles READ, " .
+                 "groups READ, " .
+                 "user_group_map READ");
+
+        # Get the event ID for the first pending schedule
+        $sth_next_scheduled_event->execute;
+        my $fetched = $sth_next_scheduled_event->fetch;
+        $sth_next_scheduled_event->finish;
+        return undef unless $fetched;
+        my ($eventid, $owner_id, $subject, $body) = @{$fetched};
+
+        my $owner = Bugzilla::User->new($owner_id);
+
+        my $whineatothers = $owner->in_group('bz_canusewhineatothers');
+
+        my %user_objects;   # Used for keeping track of who has been added
+
+        # Get all schedules that match that event ID and are pending
+        $sth_schedules_by_event->execute($eventid);
+
+        # Add the users from those schedules to the list
+        while (my $row = $sth_schedules_by_event->fetch) {
+            my ($sid, $mailto) = @{$row};
+
+            # Only bother doing any work if this user has whine permission
+            if ($owner->in_group('bz_canusewhines')) {
+                if (not defined $user_objects{$mailto}) {
+                    if ($mailto == $owner_id) {
+                        $user_objects{$mailto} = $owner;
+                    }
+                    elsif ($whineatothers) {
+                        $user_objects{$mailto} = Bugzilla::User->new($mailto);
+                    }
+                }
+            }
+
+            reset_timer($sid);
+        }
+
+        $dbh->do("UNLOCK TABLES");
+
+        # Only set $event if the user is allowed to do whining
+        if ($owner->in_group('bz_canusewhines')) {
+            my @users = values %user_objects;
+            $event = {
+                    'eventid' => $eventid,
+                    'author'  => $owner,
+                    'mailto'  => \@users,
+                    'subject' => $subject,
+                    'body'    => $body,
+            };
+        }
+    }
+    return $event;
+}
+
+# Run the queries for each event
+#
+# $event:
+#   eventid (the database ID for this event)
+#   author  (user object for who created the event)
+#   mailto  (array of user objects for mail targets)
+#   subject (subject line for message)
+#   body    (text blurb at top of message)
+while (my $event = get_next_event) {
+
+    my $eventid = $event->{'eventid'};
+
+    # We loop for each target user because some of the queries will be using
+    # subjective pronouns
+    Bugzilla->switch_to_shadow_db();
+    for my $target (@{$event->{'mailto'}}) {
+        my $args = {
+            'subject'     => $event->{'subject'},
+            'body'        => $event->{'body'},
+            'eventid'     => $event->{'eventid'},
+            'author'      => $event->{'author'},
+            'recipient'   => $target,
+            'from'        => $fromaddress,
+        };
+
+        # run the queries for this schedule
+        my $queries = run_queries($args);
+
+        # check to make sure there is something to output
+        my $there_are_bugs = 0;
+        for my $query (@{$queries}) {
+            $there_are_bugs = 1 if scalar @{$query->{'bugs'}};
+        }
+        next unless $there_are_bugs;
+
+        $args->{'queries'} = $queries;
+
+        mail($args);
+    }
+    Bugzilla->switch_to_main_db();
+}
+
+################################################################################
+# Functions
+################################################################################
+
+# The mail and run_queries functions use an anonymous hash ($args) for their
+# arguments, which are then passed to the templates.
+#
+# When run_queries is run, $args contains the following fields:
+#  - body           Message body defined in event
+#  - from           Bugzilla system email address
+#  - queries        array of hashes containing:
+#          - bugs:  array of hashes mapping fieldnames to values for this bug
+#          - title: text title given to this query in the whine event
+#  - schedule_id    integer id of the schedule being run
+#  - subject        Subject line for the message
+#  - recipient      user object for the recipient
+#  - author         user object of the person who created the whine event
+#
+# In addition, mail adds two more fields to $args:
+#  - alternatives   array of hashes defining mime multipart types and contents
+#  - boundary       a MIME boundary generated using the process id and time
+#
+sub mail {
+    my $args = shift;
+
+    # Don't send mail to someone on the nomail list.
+    return if $nomail{$args->{'recipient'}->{'login'}};
+
+    my $msg = ''; # it's a temporary variable to hold the template output
+    $args->{'alternatives'} ||= [];
+
+    # put together the different multipart mime segments
+
+    $template->process("whine/mail.txt.tmpl", $args, \$msg)
+        or die($template->error());
+    push @{$args->{'alternatives'}},
+        {
+            'content' => $msg,
+            'type'    => 'text/plain',
+        };
+    $msg = '';
+
+    $template->process("whine/mail.html.tmpl", $args, \$msg)
+        or die($template->error());
+    push @{$args->{'alternatives'}},
+        {
+            'content' => $msg,
+            'type'    => 'text/html',
+        };
+    $msg = '';
+
+    # now produce a ready-to-mail mime-encoded message
+
+    $args->{'boundary'} = "-----=====-----" . $$ . "--" . time() . "-----";
+
+    $template->process("whine/multipart-mime.txt.tmpl", $args, \$msg)
+        or die($template->error());
+
+    my $sendmailparam =
+        Param('sendmailnow') ? '' : "-ODeliveryMode=deferred";
+    open SENDMAIL, "|/usr/lib/sendmail $sendmailparam -t -i"
+        or die "Can't open sendmail";
+    print SENDMAIL $msg;
+    close SENDMAIL;
+
+    delete $args->{'boundary'};
+    delete $args->{'alternatives'};
+
+}
+
+# run_queries runs all of the queries associated with a schedule ID, adding
+# the results to $args or mailing off the template if a query wants individual
+# messages for each bug
+sub run_queries {
+    my $args = shift;
+
+    my $return_queries = [];
+
+    $sth_run_queries->execute($args->{'eventid'});
+    my $queries = {};
+    for (@{$sth_run_queries->fetchall_arrayref}) {
+        $queries->{$_->[0]} = {
+            'name'          => $_->[1],
+            'title'         => $_->[2],
+            'onemailperbug' => $_->[3],
+            'bugs'          => [],
+        };
+    }
+
+    for my $query_id (keys %{$queries}) {
+        my $thisquery = $queries->{$query_id};
+        next unless $thisquery->{'name'};   # named query is blank
+
+        my $savedquery = get_query($thisquery->{'name'}, $args->{'author'});
+        next unless $savedquery;    # silently ignore missing queries
+
+        # Execute the saved query
+        my @searchfields = (
+            'bugs.bug_id',
+            'bugs.bug_severity',
+            'bugs.priority',
+            'bugs.rep_platform',
+            'bugs.assigned_to',
+            'bugs.bug_status',
+            'bugs.resolution',
+            'bugs.short_desc',
+            'map_assigned_to.login_name',
+        );
+        # A new Bugzilla::CGI object needs to be created to allow
+        # Bugzilla::Search to execute a saved query.  It's exceedingly weird,
+        # but that's how it works.
+        my $searchparams = new Bugzilla::CGI($savedquery);
+        my $search = new Bugzilla::Search(
+            'fields' => \@searchfields,
+            'params' => $searchparams,
+            'user'   => $args->{'recipient'}, # the search runs as the recipient
+        );
+        my $sqlquery = $search->getSQL();
+        $sth = $dbh->prepare($sqlquery);
+        $sth->execute;
+
+        while (my @row = $sth->fetchrow_array) {
+            my $bug = {};
+            for my $field (@searchfields) {
+                my $fieldname = $field;
+                $fieldname =~ s/^bugs\.//;  # No need for bugs.whatever
+                $bug->{$fieldname} = shift @row;
+            }
+
+            if ($thisquery->{'onemailperbug'}) {
+                $args->{'queries'} = [
+                    {
+                        'name' => $thisquery->{'name'},
+                        'title' => $thisquery->{'title'},
+                        'bugs' => [ $bug ],
+                    },
+                ];
+                mail($args);
+                delete $args->{'queries'};
+            }
+            else {  # It belongs in one message with any other lists
+                push @{$thisquery->{'bugs'}}, $bug;
+            }
+        }
+        unless ($thisquery->{'onemailperbug'}) {
+            push @{$return_queries}, $thisquery;
+        }
+    }
+
+    return $return_queries;
+}
+
+# get_query gets the namedquery.  It's similar to LookupNamedQuery (in
+# buglist.cgi), but doesn't care if a query name really exists or not, since
+# individual named queries might go away without the whine_queries that point
+# to them being removed.
+sub get_query {
+    my ($name, $user) = @_;
+    my $qname = $name;
+    $sth_get_query->execute($user->{'id'}, $qname);
+    my $fetched = $sth_get_query->fetch;
+    $sth_get_query->finish;
+    return $fetched ? $fetched->[0] : '';
+}
+
+# check_today gets a run day from the schedule and sees if it matches today
+# a run day value can contain any of:
+#   - a three-letter day of the week
+#   - a number for a day of the month
+#   - 'last' for the last day of the month
+#   - 'All' for every day
+#   - 'MF' for every weekday
+
+sub check_today {
+    my $run_day  = shift;
+
+    if (($run_day eq 'MF')
+     && ($now_weekday > 0)
+     && ($now_weekday < 6)) {
+        return 1;
+    }
+    elsif (
+         length($run_day) == 3 &&
+         index("SunMonTueWedThuFriSat", $run_day)/3 == $now_weekday) {
+        return 1;
+    }
+    elsif  (($run_day eq 'All')
+         || (($run_day eq 'last')  &&
+             ($now_day == $daysinmonth[$now_month] ))
+         || ($run_day eq $now_day)) {
+        return 1;
+    }
+    return 0;
+}
+
+# reset_timer sets the next time a whine is supposed to run, assuming it just
+# ran moments ago.  Its only parameter is a schedule ID.
+#
+# reset_timer does not lock the whine_schedules table.  Anything that calls it
+# should do that itself.
+sub reset_timer {
+    my $schedule_id = shift;
+
+    $sth = $dbh->prepare( "SELECT run_day, run_time FROM whine_schedules " .
+                          "WHERE id=?" );
+    $sth->execute($schedule_id);
+    my ($run_day, $run_time) = $sth->fetchrow_array;
+
+    my $run_today = 0;
+    my $minute_offset = 0;
+
+    # If the schedule is to run today, and it runs many times per day,
+    # it shall be set to run immediately.
+    $run_today = &check_today($run_day);
+    if (($run_today) && ($run_time !~ /^\d+$/)) {
+        # The default of 60 catches any bad value
+        my $minute_interval = 60;
+        if ($run_time =~ /^(\d+)min$/i) {
+            $minute_interval = $1;
+        }
+
+        # set the minute offset to the next interval point
+        $minute_offset = $minute_interval - ($now_minute % $minute_interval);
+    }
+    elsif (($run_today) && ($run_time > $now_hour)) {
+        # timed event for later today
+        # (This should only happen if, for example, an 11pm scheduled event
+        #  didn't happen until after midnight)
+        $minute_offset = (60 * ($run_time - $now_hour)) - $now_minute;
+    }
+    else {
+        # it's not something that runs later today.
+        $minute_offset = 0;
+
+        # Set the target time if it's a specific hour
+        my $target_time = ($run_time =~ /^\d+$/) ? $run_time : 0;
+
+        my $nextdate = &get_next_date($run_day);
+
+        $sth = $dbh->prepare( "UPDATE whine_schedules " .
+                              "SET run_next=DATE_ADD(?, INTERVAL ? HOUR) " .
+                              "WHERE id=?");
+        $sth->execute($nextdate, $target_time, $schedule_id);
+        return;
+    }
+
+    # Scheduling is done in terms of whole minutes, so we use DATE_SUB() to
+    # drop the seconds from the time.
+    if ($minute_offset > 0) {
+        $sth = $dbh->prepare("UPDATE whine_schedules " .
+                             "SET run_next = " .
+                             "DATE_SUB(DATE_ADD(NOW(), INTERVAL ? MINUTE), " .
+                             "INTERVAL SECOND(NOW()) SECOND) " .
+                             "WHERE id=?");
+        $sth->execute($minute_offset, $schedule_id);
+    } else {
+        # The minute offset is zero or less, which is not supposed to happen.
+        # This is a kind of safeguard against infinite loops.  NULL schedules
+        # will not be available to get_next_event until they are rescheduled.
+        $sth = $dbh->prepare("UPDATE whine_schedules " .
+                             "SET run_next = NULL " .
+                             "WHERE id=?");
+        $sth->execute($schedule_id);
+        # complain to STDERR 
+        print STDERR "Bad minute_offset for schedule ID $schedule_id\n";
+    }
+}
+
+# get_next_date determines the difference in days between now and the next
+# time a schedule should run, excluding today
+#
+# It takes a run_day argument (see check_today, above, for an explanation),
+# and returns an SQL date
+sub get_next_date {
+    my $day = shift;
+
+    my $add_days = 0;
+
+    if ($day eq 'All') {
+        $add_days = 1;
+    }
+    elsif ($day eq 'last') {
+        # next_date should contain the last day of this month, or next month
+        # if it's today
+        if ($daysinmonth[$now_month] == $now_day) {
+            my $month = $now_month + 1;
+            $month = 1 if $month > 12;
+            $add_days = $daysinmonth[$month] + 1;
+        }
+        else {
+            $add_days = $daysinmonth[$now_month] - $now_day;
+        }
+    }
+    elsif ($day eq 'MF') { # any day Monday through Friday
+        if ($now_weekday < 5) { # Sun-Thurs
+            $add_days = 1;
+        }
+        elsif ($now_weekday == 5) { # Friday
+            $add_days = 3;
+        }
+        else { # it's 6, Saturday
+            $add_days = 2;
+        }
+    }
+    elsif ($day !~ /^\d+$/) { # A specific day of the week
+        # The default is used if there is a bad value in the database, in
+        # which case we mark it to a less-popular day (Sunday)
+        my $day_num = 0;
+
+        if (length($day) == 3) {
+            $day_num = (index("SunMonTueWedThuFriSat", $day)/3) or 0;
+        }
+
+        $add_days = $day_num - $now_weekday;
+        if ($add_days < 0) { # it's next week
+            $add_days += 7;
+        }
+    }
+    else { # it's a number, so we set it for that calendar day
+        $add_days = $day - $now_day;
+        # If it's already beyond that day this month, set it to the next one
+        if ($add_days < 0) {
+            $add_days += $daysinmonth[$now_month];
+        }
+    }
+
+    # Get a date in whatever format the database will accept
+    $sth = $dbh->prepare("SELECT DATE_ADD(CURRENT_DATE(), INTERVAL ? DAY)");
+    $sth->execute($add_days);
+    return $sth->fetch->[0];
+}
+