Bug 253721: Add group-based lists to whining

Patch by Erik Stambaugh <erik@dasbistro.com>
r=joel, r,a=justdave

8 files changed, 208 insertions, 80 deletions

diff --git a/Bugzilla/Constants.pm b/Bugzilla/Constants.pm
index a3e16251c..8837d2276 100644
--- a/Bugzilla/Constants.pm
+++ b/Bugzilla/Constants.pm
@@ -62,6 +62,9 @@ use base qw(Exporter);
     GROUP_BLESS
     GROUP_VISIBLE
 
+    MAILTO_USER
+    MAILTO_GROUP
+
     DEFAULT_COLUMN_LIST
     DEFAULT_QUERY_NAME
 
@@ -206,6 +209,9 @@ use constant GROUP_MEMBERSHIP => 0;
 use constant GROUP_BLESS => 1;
 use constant GROUP_VISIBLE => 2;
 
+use constant MAILTO_USER => 0;
+use constant MAILTO_GROUP => 1;
+
 # The default list of columns for buglist.cgi
 use constant DEFAULT_COLUMN_LIST => (
     "bug_severity", "priority", "rep_platform","assigned_to",
diff --git a/Bugzilla/Group.pm b/Bugzilla/Group.pm
new file mode 100644
index 000000000..2cb8e5342
--- /dev/null
+++ b/Bugzilla/Group.pm
@@ -0,0 +1,51 @@
+# -*- 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): Joel Peshkin <bugreport@peshkin.net>
+#                 Erik Stambaugh <erik@dasbistro.com>
+
+use strict;
+
+package Bugzilla::Group;
+
+use Bugzilla::Config;
+
+# ValidateGroupName checks to see if ANY of the users in the provided list 
+# of user objects can see the named group.  It returns the group id if
+# successful and undef otherwise.
+sub ValidateGroupName {
+    my ($name, @users) = (@_);
+    my $dbh = Bugzilla->dbh;
+    my $query = "SELECT id FROM groups " .
+                "WHERE name = ?";
+    if (Param('usevisibilitygroups')) {
+        my @visible = (-1);
+        foreach my $user (@users) {
+            $user && push @visible, @{$user->visible_groups_direct};
+        }
+        my $visible = join(', ', @visible);
+        $query .= " AND id IN($visible)";
+    }
+    my $sth = $dbh->prepare($query);
+    $sth->execute($name);
+    my ($ret) = $sth->fetchrow_array();
+    return $ret;
+}
+
+1;
diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index 816b4b0a1..e58278f48 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -40,6 +40,7 @@ use Bugzilla::Config;
 use Bugzilla::Error;
 use Bugzilla::Util;
 use Bugzilla::Constants;
+use Bugzilla::Group;
 
 use Date::Format;
 use Date::Parse;
@@ -382,7 +383,7 @@ sub init {
         (
          "^(?:assigned_to|reporter|qa_contact),(?:notequals|equals|anyexact),%group\\.(\\w+)%" => sub {
              my $group = $1;
-             my $groupid = ValidateGroupName( $group, ($user));
+             my $groupid = Bugzilla::Group::ValidateGroupName( $group, ($user));
              $groupid || ThrowUserError('invalid_group_name',{name => $group});
              my @childgroups = @{$user->flatten_group_membership($groupid)};
              my $table = "user_group_map_$chartid";
@@ -419,7 +420,7 @@ sub init {
 
          "^(?:cc),(?:notequals|equals|anyexact),%group\\.(\\w+)%" => sub {
              my $group = $1;
-             my $groupid = ValidateGroupName( $group, ($user));
+             my $groupid = Bugzilla::Group::ValidateGroupName( $group, ($user));
              $groupid || ThrowUserError('invalid_group_name',{name => $group});
              my @childgroups = @{$user->flatten_group_membership($groupid)};
              my $chartseq = $chartid;
@@ -1495,24 +1496,6 @@ sub pronoun {
     return 0;
 }
 
-# ValidateGroupName checks to see if ANY of the users in the provided list 
-# of user objects can see the named group.  It returns the group id if
-# successful and undef otherwise.
-sub ValidateGroupName {
-    my ($name, @users) = (@_);
-    my @visible = (-1);
-    foreach my $user (@users) {
-        $user && push @visible, @{$user->visible_groups_direct};
-    }
-    my $visible = join(', ', @visible);
-    my $dbh = Bugzilla->dbh;
-    my $sth = $dbh->prepare("SELECT id FROM groups " .
-                            "WHERE name = ? AND id IN($visible)");
-    $sth->execute($name);
-    my ($ret) = $sth->fetchrow_array();
-    return $ret;
-}
-
 # Validate that the query type is one we can deal with
 sub IsValidQueryType
 {
diff --git a/checksetup.pl b/checksetup.pl
index 287b0baa6..7404b5912 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -2108,7 +2108,8 @@ $table{whine_schedules} =
      run_day        varchar(32),
      run_time       varchar(32),
      run_next       datetime,
-     mailto_userid  mediumint       not null,
+     mailto         mediumint       not null,
+     mailto_type    smallint        not null default 0,
      
      index(run_next),
      index(eventid)';
@@ -4223,6 +4224,11 @@ AddField("profiles", "extern_id", "varchar(64)");
 AddField('flagtypes', 'grant_group_id', 'mediumint null');
 AddField('flagtypes', 'request_group_id', 'mediumint null');
 
+# 2004-01-03 - bug 253721 erik@dasbistro.com
+# mailto is no longer just userids
+RenameField('whine_schedules', 'mailto_userid', 'mailto');
+AddField('whine_schedules', 'mailto_type', 'smallint not null default 0');
+
 # 2005-01-29 - mkanat@kerio.com
 if (!GetFieldDef('longdescs', 'already_wrapped')) {
     AddField('longdescs', 'already_wrapped', 'tinyint not null default 0');
diff --git a/editgroups.cgi b/editgroups.cgi
index f7362cb5f..abef251f4 100755
--- a/editgroups.cgi
+++ b/editgroups.cgi
@@ -424,6 +424,9 @@ if ($action eq 'delete') {
         SendSQL("DELETE FROM group_group_map WHERE grantor_id = $gid");
         SendSQL("DELETE FROM bug_group_map WHERE group_id = $gid");
         SendSQL("DELETE FROM group_control_map WHERE group_id = $gid");
+        SendSQL("DELETE FROM whine_schedules WHERE " .
+                "mailto_type = " . MAILTO_GROUP . " " .
+                "AND mailto = $gid");
         SendSQL("DELETE FROM groups WHERE id = $gid");
     }
 
diff --git a/editwhines.cgi b/editwhines.cgi
index 8c7c269e8..b9808941a 100755
--- a/editwhines.cgi
+++ b/editwhines.cgi
@@ -35,6 +35,7 @@ use vars qw( $vars );
 
 use Bugzilla::Constants;
 use Bugzilla::User;
+use Bugzilla::Group;
 # require the user to have logged in
 Bugzilla->login(LOGIN_REQUIRED);
 
@@ -67,7 +68,8 @@ my $sth; # database statement handle
 #  '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
+#       'mailto_type' - MAILTO_USER or MAILTO_GROUP
+#       'mailto' - person/group who will receive the results
 #       'id' - row ID for the schedule
 my $events = get_events($userid);
 
@@ -158,10 +160,10 @@ if ($cgi->param('update')) {
                 if ($cgi->param("add_schedule_$eventid")) {
                     # the schedule table must be locked before altering
                     $sth = $dbh->prepare("INSERT INTO whine_schedules " .
-                                         "(eventid, mailto_userid, " .
+                                         "(eventid, mailto_type, mailto, " .
                                          "run_day, run_time) " .
-                                         "VALUES (?, ?, 'Sun', 2)");
-                    $sth->execute($eventid, $userid);
+                                         "VALUES (?, ?, ?, 'Sun', 2)");
+                    $sth->execute($eventid, MAILTO_USER, $userid);
                 }
                 # add a query
                 elsif ($cgi->param("add_query_$eventid")) {
@@ -181,17 +183,19 @@ if ($cgi->param('update')) {
                                  "WHERE eventid=?");
             $sth->execute($eventid);
             my @scheduleids = ();
-            for (@{$sth->fetchall_arrayref}) {
-                push @scheduleids, $_->[0];
-            };
+            while (my ($sid) = $sth->fetchrow_array) {
+                push @scheduleids, $sid;
+            }
 
             # 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 ($cgi->param("mailto_type_$sid") == MAILTO_USER) {
+                    $arglist->{"mailto_$sid"} = {
+                        'type' => 'single',
+                    };
+                }
             }
             if (scalar %{$arglist}) {
                 &Bugzilla::User::match_field($arglist);
@@ -217,30 +221,58 @@ if ($cgi->param('update')) {
                     }
                 }
                 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);
+                    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");
+                    my $o_mailto_type = lc $cgi->param("orig_mailto_type_$sid");
+                    my $mailto_type   = $cgi->param("mailto_type_$sid");
+
+                    $o_day         = '' unless length($o_day);
+                    $o_time        = '' unless length($o_time);
+                    $o_mailto      = '' unless length($o_mailto);
+                    $o_mailto_type = '' unless length($o_mailto_type);
+                    $day           = '' unless length($day);
+                    $time          = '' unless length($time);
+                    $mailto        = '' unless length($mailto);
+                    $mailto_type   = '' unless length($mailto_type);
+
+                    my $mailto_id = $userid;
+
+                    # get an id for the mailto address
+                    if ($can_mail_others && $mailto) {
+                        if ($mailto_type == MAILTO_USER) {
+                            # detaint
+                            my $emailregexp = Param('emailregexp');
+                            $mailto =~ /($emailregexp)/;
+                            $mailto =~ $1;
+                            $mailto_id = DBname_to_id($mailto);
+                        }
+                        elsif ($mailto_type == MAILTO_GROUP) {
+                            # detaint the group parameter
+                            $mailto =~ /^([0-9a-z_\-\.]+)/i;
+                            my $group = $1;
+
+                            $mailto_id = Bugzilla::Group::ValidateGroupName(
+                                $group, ($user));
+                            $mailto_id || ThrowUserError(
+                                'invalid_group_name', {name => $group});
+                        }
+                        else {
+                            # bad value, so it will just mail to the whine
+                            # owner.  $mailto_id was already set above.
+                            $mailto_type = MAILTO_USER;
+                        }
                     }
 
+                    detaint_natural($mailto_type);
+
                     if ( ($o_day  ne $day) ||
-                         ($o_time ne $time) ){
+                         ($o_time ne $time) ||
+                         ($o_mailto != $mailto) ||
+                         ($o_mailto_type != $mailto_type) ){
 
                         trick_taint($day) if length($day);
                         trick_taint($time) if length($time);
@@ -248,10 +280,11 @@ if ($cgi->param('update')) {
                         # the schedule table must be locked
                         $sth = $dbh->prepare("UPDATE whine_schedules " .
                                              "SET run_day=?, run_time=?, " .
-                                             "mailto_userid=?, " .
+                                             "mailto_type=?, mailto=?, " .
                                              "run_next=NULL " .
                                              "WHERE id=?");
-                        $sth->execute($day, $time, $mail_uid, $sid);
+                        $sth->execute($day, $time, $mailto_type,
+                                      $mailto_id, $sid);
                     }
                 }
             }
@@ -262,9 +295,9 @@ if ($cgi->param('update')) {
                                  "WHERE eventid=?");
             $sth->execute($eventid);
             my @queries = ();
-            for (@{$sth->fetchall_arrayref}) {
-                push @queries, $_->[0];
-            };
+            while (my ($qid) = $sth->fetchrow_array) {
+                push @queries, $qid;
+            }
 
             for my $qid (@queries) {
                 if ($cgi->param("remove_query_$qid")) {
@@ -365,19 +398,30 @@ for my $event_id (keys %{$events}) {
     $events->{$event_id}->{'queries'} = [];
 
     # schedules
-    $sth = $dbh->prepare("SELECT run_day, run_time, profiles.login_name, id " .
+    $sth = $dbh->prepare("SELECT run_day, run_time, mailto_type, mailto, 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 $mailto_type = $row->[2];
+        my $mailto = '';
+        if ($mailto_type == MAILTO_USER) {
+            my $mailto_user = new Bugzilla::User($row->[3]);
+            $mailto = $mailto_user->login;
+        }
+        elsif ($mailto_type == MAILTO_GROUP) {
+            $sth = $dbh->prepare("SELECT name FROM groups WHERE id=?");
+            $sth->execute($row->[3]);
+            $mailto = $sth->fetch->[0];
+            $mailto = "" unless Bugzilla::Group::ValidateGroupName(
+                                $mailto, ($user));
+        }
         my $this_schedule = {
-            'day'    => $row->[0],
-            'time'   => $row->[1],
-            'mailto' => $row->[2],
-            'id'     => $row->[3],
+            'day'         => $row->[0],
+            'time'        => $row->[1],
+            'mailto_type' => $mailto_type,
+            'mailto'      => $mailto,
+            'id'          => $row->[4],
         };
         push @{$events->{$event_id}->{'schedule'}}, $this_schedule;
     }
@@ -408,8 +452,8 @@ $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];
+while (my ($query) = $sth->fetchrow_array) {
+    push @{$vars->{'available_queries'}}, $query;
 }
 
 $template->process("whine/schedule.html.tmpl", $vars)
@@ -425,11 +469,11 @@ sub get_events {
                             "FROM whine_events " .
                             "WHERE owner_userid=?");
     $sth->execute($userid);
-    for (@{$sth->fetchall_arrayref}) {
-        $events->{$_->[0]} = {
-            'subject' => $_->[1],
-            'body'    => $_->[2],
-        }
+    while (my ($ev, $sub, $bod) = $sth->fetchrow_array) {
+        $events->{$ev} = {
+            'subject' => $sub,
+            'body' => $bod,
+        };
     }
     return $events;
 }
diff --git a/template/en/default/whine/schedule.html.tmpl b/template/en/default/whine/schedule.html.tmpl
index 60c0f3cd8..f227987d6 100644
--- a/template/en/default/whine/schedule.html.tmpl
+++ b/template/en/default/whine/schedule.html.tmpl
@@ -25,7 +25,8 @@
   #     schedule: array of hashes containing schedule info:
   #         day:    value in day column
   #         time:   value selected in time column
-  #         mailto: recipient's email address
+  #         mailto_type: 0=user 1=group
+  #         mailto: recipient's id (profile or group)
   #     queries:  as with schedule, an anonymous array containing hashes of:
   #         name:  the named query's name
   #         title: title to be displayed on the results
@@ -158,6 +159,16 @@
               </td>
               <td align="left">
                 [% IF mail_others %]
+                  <input type="hidden" name="orig_mailto_type_[% schedule.id %]"
+                         value="[% schedule.mailto_type FILTER html %]">
+                  <select name="mailto_type_[% schedule.id %]">
+                    <option value="0" [% IF schedule.mailto_type == 0 %]
+                                        selected
+                                      [% END %]>User</option>
+                    <option value="1" [% IF schedule.mailto_type == 1 %]
+                                        selected
+                                      [% END %]>Group</option>
+                  </select>
                   <input type="hidden" name="orig_mailto_[% schedule.id %]"
                          value="[% schedule.mailto FILTER html %]">
                   <input type="text" name="mailto_[% schedule.id %]"
diff --git a/whine.pl b/whine.pl
index d3584db68..997e4a58a 100755
--- a/whine.pl
+++ b/whine.pl
@@ -75,7 +75,7 @@ my $sth_next_scheduled_event = $dbh->prepare(
 
 # get all pending schedules matching an eventid
 my $sth_schedules_by_event = $dbh->prepare(
-    "SELECT id, mailto_userid " .
+    "SELECT id, mailto_type, mailto " .
     "FROM whine_schedules " .
     "WHERE eventid=? AND run_next <= NOW()"
 );
@@ -245,18 +245,42 @@ sub get_next_event {
 
         # Add the users from those schedules to the list
         while (my $row = $sth_schedules_by_event->fetch) {
-            my ($sid, $mailto) = @{$row};
+            my ($sid, $mailto_type, $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;
+
+                if ($mailto_type == MAILTO_USER) {
+                    if (not defined $user_objects{$mailto}) {
+                        if ($mailto == $owner_id) {
+                            $user_objects{$mailto} = $owner;
+                        }
+                        elsif ($whineatothers) {
+                            $user_objects{$mailto} = Bugzilla::User->new($mailto);
+                        }
                     }
-                    elsif ($whineatothers) {
-                        $user_objects{$mailto} = Bugzilla::User->new($mailto);
+                }
+                elsif ($mailto_type == MAILTO_GROUP) {
+                    my $sth = $dbh->prepare("SELECT name FROM groups " .
+                                            "WHERE id=?");
+                    $sth->execute($mailto);
+                    my $groupname = $sth->fetch->[0];
+                    my $group_id = Bugzilla::Group::ValidateGroupName(
+                        $groupname, $owner);
+                    if ($group_id) {
+                        $sth = $dbh->prepare("SELECT user_id FROM " .
+                                             "user_group_map " .
+                                             "WHERE group_id=?");
+                        $sth->execute($group_id);
+                        for my $row (@{$sth->fetchall_arrayref}) {
+                            if (not defined $user_objects{$row->[0]}) {
+                                $user_objects{$row->[0]} =
+                                    Bugzilla::User->new($row->[0]);
+                            }
+                        }
                     }
                 }
+
             }
 
             reset_timer($sid);