From f6c796ad212c7cc62687cd02e3808245a993f6b1 Mon Sep 17 00:00:00 2001 From: "bugreport%peshkin.net" <> Date: Thu, 5 Aug 2004 04:36:23 +0000 Subject: Bug 185090: Add revamped whining system patch by: Erik r=joel r=jouni a=justdave --- Bugzilla/User.pm | 13 +- checksetup.pl | 46 +- editwhines.cgi | 433 ++++++++++++++ .../default/account/prefs/saved-searches.html.tmpl | 8 +- template/en/default/filterexceptions.pl | 13 + .../en/default/global/site-navigation.html.tmpl | 2 + template/en/default/global/useful-links.html.tmpl | 2 + template/en/default/global/user-error.html.tmpl | 5 + template/en/default/whine/mail.html.tmpl | 96 +++ template/en/default/whine/mail.txt.tmpl | 69 +++ template/en/default/whine/multipart-mime.txt.tmpl | 52 ++ template/en/default/whine/schedule.html.tmpl | 406 +++++++++++++ whine.pl | 648 +++++++++++++++++++++ 13 files changed, 1786 insertions(+), 7 deletions(-) create mode 100755 editwhines.cgi create mode 100644 template/en/default/whine/mail.html.tmpl create mode 100644 template/en/default/whine/mail.txt.tmpl create mode 100644 template/en/default/whine/multipart-mime.txt.tmpl create mode 100644 template/en/default/whine/schedule.html.tmpl create mode 100755 whine.pl 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 +# + +################################################################################ +# 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 @@ Edit - Forget + [% IF q.usedinwhine %] + Remove from whining first + [% ELSE %] + Forget + [% END %] [ + '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 %] [% '' IF user.groups.editkeywords %] + [% '' IF user.groups.bz_canusewhines %] [% '' 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 %] [% ' | Keywords' IF user.groups.editkeywords %] + [% ' | Whining' + IF user.groups.bz_canusewhines %] [% 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 [% field_descs.$field FILTER html %]. + [% 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 + #%] + +[%# 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" %] + + + + + + [[% terms.Bugzilla %]] [% subject FILTER html %] + + + + +

+ [% body FILTER html %] +

+ +

+ [% IF author.login == recipient.login %] + Click + here to edit your whine schedule + [% ELSE %] + This search was scheduled by [% author.login FILTER html %]. + [% END %] +

+ + +[% FOREACH query=queries %] + +

[%+ query.title FILTER html %]

+ + + + + + + + + + + + + + [% FOREACH bug=query.bugs %] + + + + + + + + + + + [% END %] +
IDSevPriPltAssigneeStatusResolutionSummary
[% bug.bug_id %][% bug.bug_severity FILTER html %][% bug.priority FILTER html %][% bug.rep_platform FILTER html %][% bug.$assignee_login_string FILTER html %][% bug.bug_status FILTER html %][% bug.resolution FILTER html %][% bug.short_desc FILTER html %]
+[% END %] + + + + + 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 + #%] + +[%# 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 + #%] + +[%# 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 + #%] + +[%# 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 %] + +

+ "Whining" is when [% terms.Bugzilla %] executes a saved query at a regular interval + and sends the resulting list of [% terms.bugs %] via email. +

+ +

+ 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. +

+ +

+ 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. +

+ +

+ Queries come from saved searches, which are created by executing a search, 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. +

+ +
+[%# This hidden submit button must be here to set default behavior when + the user presses return on a form input field #%] + + + +[% FOREACH event = events %] + + + + + + + + + + + + + + + + + + [% IF event.value.schedule.size == 0 %] + + + + + + + [% ELSE %] + + + + + + + + [% END %] + + [% IF event.value.queries.size == 0 %] + + + + + + + + [% ELSE %] + + + + + + + + [% END %] + +
+ Event: + + +
+ Email subject line: + + +
+ Descriptive text sent within whine message: + + +
+ Schedule: + + Not scheduled to run
+ +
+ Schedule: + + + + + + [% IF mail_others %] + + [% END %] + + [% FOREACH schedule = event.value.schedule %] + + + + + + [% END %] + + + + + +
+ Interval + + Mail to +
+ + [%# these hidden fields allow us to compare old values instead + of reading the database to tell if a field has changed %] + + + + [% PROCESS day_field val=schedule.day %] + [% PROCESS time_field val=schedule.time %] + + [% IF mail_others %] + + + [% END %] + + +
+ +
+ +
+ Queries: + + No queries
+ + 		+ +
+ Queries: + + + + + + + + + + [% FOREACH query = event.value.queries %] + + + + + + + + + + [% END %] + + + + + +
SortSearchTitle
+ + + + + [% PROCESS query_field thisquery=query.name %] + + + + + + + One message per [% terms.bug %] + + +
+ +
+ + 		+ +
+ +[% END %] + +

+ +

+ +
+ +[% PROCESS global/footer.html.tmpl %] + +[% BLOCK query_field +%] + + [% IF available_queries.size > 0 %] + + + + [% ELSE %] + Please visit the Search page and save a query + [% END %] + +[%+ END %] + +[% BLOCK day_field +%] + +[%+ END %] + +[% BLOCK time_field +%] + + +[%+ 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 + +################################################################################ +# 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{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]; +} + -- cgit v1.2.1