summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--Bugzilla/User.pm13
-rwxr-xr-xchecksetup.pl46
-rwxr-xr-xeditwhines.cgi433
-rw-r--r--template/en/default/account/prefs/saved-searches.html.tmpl8
-rw-r--r--template/en/default/filterexceptions.pl13
-rw-r--r--template/en/default/global/site-navigation.html.tmpl2
-rw-r--r--template/en/default/global/useful-links.html.tmpl2
-rw-r--r--template/en/default/global/user-error.html.tmpl5
-rw-r--r--template/en/default/whine/mail.html.tmpl96
-rw-r--r--template/en/default/whine/mail.txt.tmpl69
-rw-r--r--template/en/default/whine/multipart-mime.txt.tmpl52
-rw-r--r--template/en/default/whine/schedule.html.tmpl406
-rwxr-xr-xwhine.pl648
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];
+}
+