Bug 1196620 - support automatic removal of inactive users from groups

9 files changed, 250 insertions, 0 deletions

diff --git a/Bugzilla/Group.pm b/Bugzilla/Group.pm
index 37d4acf90..51d51b2e8 100644
--- a/Bugzilla/Group.pm
+++ b/Bugzilla/Group.pm
@@ -50,6 +50,7 @@ use constant DB_COLUMNS => qw(
     groups.isactive
     groups.icon_url
     groups.owner_user_id
+    groups.idle_member_removal
 );
 
 use constant DB_TABLE => 'groups';
@@ -64,6 +65,7 @@ use constant VALIDATORS => {
     isbuggroup  => \&_check_is_bug_group,
     icon_url    => \&_check_icon_url,
     owner_user_id => \&_check_owner,
+    idle_member_removal => \&_check_idle_member_removal
 };
 
 use constant UPDATE_COLUMNS => qw(
@@ -73,6 +75,7 @@ use constant UPDATE_COLUMNS => qw(
     isactive
     icon_url
     owner_user_id
+    idle_member_removal
 );
 
 # Parameters that are lists of groups.
@@ -88,6 +91,7 @@ sub is_bug_group { return $_[0]->{'isbuggroup'};   }
 sub user_regexp  { return $_[0]->{'userregexp'};   }
 sub is_active    { return $_[0]->{'isactive'};     }
 sub icon_url     { return $_[0]->{'icon_url'};     }
+sub idle_member_removal { return $_[0]->{'idle_member_removal'}; }
 
 sub bugs {
     my $self = shift;
@@ -240,6 +244,7 @@ sub set_is_active   { $_[0]->set('isactive', $_[1]);    }
 sub set_name        { $_[0]->set('name', $_[1]);        }
 sub set_user_regexp { $_[0]->set('userregexp', $_[1]);  }
 sub set_icon_url    { $_[0]->set('icon_url', $_[1]);    }
+sub set_idle_member_removal { $_[0]->set('idle_member_removal', $_[1]); }
 
 sub set_owner {
     my ($self, $owner_id) = @_;
@@ -550,6 +555,13 @@ sub _check_owner {
     ThrowUserError('group_needs_owner');
 }
 
+sub _check_idle_member_removal {
+    my ($invocant, $value) = @_;
+    detaint_natural($value)
+        || ThrowUserError('invalid_parameter', { name => 'idle member removal', err => 'must be numeric' });
+    return $value <= 0 ? 0 : $value ;
+}
+
 1;
 
 __END__
diff --git a/editgroups.cgi b/editgroups.cgi
index 52fa88b8c..68590457e 100755
--- a/editgroups.cgi
+++ b/editgroups.cgi
@@ -212,6 +212,7 @@ if ($action eq 'new') {
         userregexp  => scalar $cgi->param('regexp'),
         isactive    => scalar $cgi->param('isactive'),
         icon_url    => scalar $cgi->param('icon_url'),
+        idle_member_removal => scalar $cgi->param('idle_member_removal'),
         isbuggroup  => 1,
         owner_user_id => scalar $cgi->param('owner'),
     });
@@ -402,6 +403,10 @@ sub doGroupChanges {
         $group->set_owner($cgi->param('owner'));
     }
 
+    if (defined $cgi->param('idle_member_removal')) {
+        $group->set_idle_member_removal($cgi->param('idle_member_removal'));
+    }
+
     my $changes = $group->update();
 
     my $sth_insert = $dbh->prepare('INSERT INTO group_group_map
diff --git a/scripts/remove_idle_group_members.pl b/scripts/remove_idle_group_members.pl
new file mode 100755
index 000000000..d0eda387a
--- /dev/null
+++ b/scripts/remove_idle_group_members.pl
@@ -0,0 +1,132 @@
+#!/usr/bin/perl
+
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+use strict;
+use warnings;
+
+use FindBin qw($Bin);
+use lib "$Bin/..";
+
+use Bugzilla;
+BEGIN { Bugzilla->extensions() }
+
+use Bugzilla::Constants;
+use Bugzilla::Error;
+use Bugzilla::Field;
+use Bugzilla::Group;
+use Bugzilla::Mailer;
+use Bugzilla::User;
+
+use Sys::Hostname qw(hostname);
+
+Bugzilla->usage_mode(USAGE_MODE_CMDLINE);
+
+my $dbh = Bugzilla->dbh;
+
+# Record any changes as made by the automation user
+my $auto_user = Bugzilla::User->check({ name => 'automation@bmo.tld' });
+
+my $expired = $dbh->selectall_arrayref(
+    "SELECT DISTINCT profiles.userid AS user_id,
+            groups.id AS group_id
+       FROM profiles JOIN user_group_map ON profiles.userid = user_group_map.user_id
+            JOIN groups ON user_group_map.group_id = groups.id
+      WHERE user_group_map.grant_type = ?
+            AND groups.idle_member_removal > 0
+            AND (profiles.last_seen_date IS NULL
+                 OR TO_DAYS(LOCALTIMESTAMP(0)) - TO_DAYS(profiles.last_seen_date) > groups.idle_member_removal)
+      ORDER BY profiles.login_name",
+    { Slice => {} }, GRANT_DIRECT
+);
+
+exit(0) if !@$expired;
+
+my %remove_data = ();
+foreach my $data (@$expired) {
+    $remove_data{$data->{group_id}} ||= [];
+    push(@{ $remove_data{$data->{group_id}} }, $data->{user_id});
+}
+
+# 1. Remove users from the group
+# 2. $user->update will add audit log and profile_activity entries
+# 3. Send email to group owner showing users removed
+foreach my $group_id (keys %remove_data) {
+    my $group = Bugzilla::Group->new({ id => $group_id, cache => 1 });
+
+    $dbh->bz_start_transaction();
+
+    my @users_removed = ();
+    foreach my $user_id (@{ $remove_data{$group->id} }) {
+        my $user = Bugzilla::User->new({ id => $user_id, cache => 1 });
+        Bugzilla->set_user(Bugzilla::User->super_user);
+        $user->set_groups({ remove => [ $group->name ] });
+        $user->set_bless_groups({ remove => [ $group->name ] });
+        Bugzilla->set_user($auto_user);
+        $user->update();
+        push(@users_removed, $user);
+    }
+
+    $dbh->bz_commit_transaction();
+
+    # nobody@mozilla.org cannot recieve email
+    next if $group->owner->login eq 'nobody@mozilla.org';
+
+    _send_email($group, \@users_removed);
+}
+
+sub _send_email {
+    my ($group, $users) = @_;
+
+    my $template = Bugzilla->template_inner($group->owner->setting('lang'));
+    my $vars = { group => $group, users => $users };
+
+    my ($header, $text);
+    $template->process("admin/groups/email/idle-member-removal-header.txt.tmpl", $vars, \$header)
+        || ThrowTemplateError($template->error());
+    $header .= "\n";
+    $template->process("admin/groups/email/idle-member-removal.txt.tmpl", $vars, \$text)
+        || ThrowTemplateError($template->error());
+
+    my @parts = (
+        Email::MIME->create(
+            attributes => {
+                content_type => 'text/plain',
+                charset      => 'UTF-8',
+                encoding     => 'quoted-printable',
+            },
+            body_str => $text,
+        )
+    );
+
+    if ($group->owner->setting('email_format') eq 'html') {
+        my $html;
+        $template->process("admin/groups/email/idle-member-removal.html.tmpl", $vars, \$html)
+            || ThrowTemplateError($template->error());
+        push @parts, Email::MIME->create(
+            attributes => {
+                content_type => 'text/html',
+                charset      => 'UTF-8',
+                encoding     => 'quoted-printable',
+            },
+            body_str => $html,
+        );
+    }
+
+    my $email = Email::MIME->new($header);
+    $email->header_set('X-Generated-By' => hostname());
+    if (scalar(@parts) == 1) {
+        $email->content_type_set($parts[0]->content_type);
+    }
+    else {
+        $email->content_type_set('multipart/alternative');
+    }
+    $email->parts_set(\@parts);
+
+    MessageToMTA($email);
+}
diff --git a/template/en/default/admin/groups/create.html.tmpl b/template/en/default/admin/groups/create.html.tmpl
index e5ffb7819..a2301e75a 100644
--- a/template/en/default/admin/groups/create.html.tmpl
+++ b/template/en/default/admin/groups/create.html.tmpl
@@ -60,6 +60,14 @@
     </td>
   </tr>
 
+  <tr>
+    <th>Idle Member Removal:</th>
+    <td colspan="3">
+      <input type="text" size="5" maxlength="5" id="idle_member_removal" name="idle_member_removal">
+      days (setting to 0 disables this feature)
+    </td>
+  </tr>
+
   [% Hook.process('field') %]
   </table>
 
diff --git a/template/en/default/admin/groups/edit.html.tmpl b/template/en/default/admin/groups/edit.html.tmpl
index fb54c2c0d..f0dce8c85 100644
--- a/template/en/default/admin/groups/edit.html.tmpl
+++ b/template/en/default/admin/groups/edit.html.tmpl
@@ -111,6 +111,17 @@
       </td>
     </tr>
 
+    <tr>
+      <th>
+        Idle Member Removal:
+      </th>
+      <td>
+        <input type="text" size="5" maxlength="5" id="idle_member_removal"
+               name="idle_member_removal" value="[% group.idle_member_removal FILTER html %]">
+        days (setting to 0 disables this feature)
+      </td>
+    </tr>
+
     [% IF group.is_bug_group %]
       <tr>
         <th>Use For [% terms.Bugs %]:</th>
diff --git a/template/en/default/admin/groups/email/idle-member-removal-header.txt.tmpl b/template/en/default/admin/groups/email/idle-member-removal-header.txt.tmpl
new file mode 100644
index 000000000..b15286073
--- /dev/null
+++ b/template/en/default/admin/groups/email/idle-member-removal-header.txt.tmpl
@@ -0,0 +1,12 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+[% PROCESS global/variables.none.tmpl %]
+From: [% Param('mailfrom') %]
+To: [% group.owner.email %]
+Subject: [[% terms.BugzillaTitle %]] Idle group members removed from [% group.name %]
+X-Bugzilla-Type: admin
diff --git a/template/en/default/admin/groups/email/idle-member-removal.html.tmpl b/template/en/default/admin/groups/email/idle-member-removal.html.tmpl
new file mode 100644
index 000000000..1d4f63698
--- /dev/null
+++ b/template/en/default/admin/groups/email/idle-member-removal.html.tmpl
@@ -0,0 +1,42 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+<!doctype html>
+<html>
+
+<head>
+  <title>[[% terms.Bugzilla %]] Ilde Group Member Removal Notification</title>
+</head>
+
+<body bgcolor="#ffffff">
+
+<h3>Idle Group Member Removal Notification</h3>
+
+<p> This email is to notify you, as the group owner for the <strong>[% group.name FILTER html %]</strong>
+group, that the following accounts are no longer members of the group. Accounts who have not logged
+in to [% terms.Bugzilla %] in <strong>[% group.idle_member_removal FILTER html %]</strong> days are
+automatically removed.</p>
+
+<ul>
+[% FOREACH user = users %]
+  <li>[% user.identity FILTER html %]</li>
+[% END %]
+</ul>
+
+<div style="font-size: 90%; color: #666666">
+  <hr style="border: 1px dashed #969696">
+  <b>You are receiving this mail because:</b>
+  <ul>
+    <li>You are a group owner.</li>
+  </ul>
+</div>
+@@body-headers@@
+</body>
+</html>
diff --git a/template/en/default/admin/groups/email/idle-member-removal.txt.tmpl b/template/en/default/admin/groups/email/idle-member-removal.txt.tmpl
new file mode 100644
index 000000000..18fc373fc
--- /dev/null
+++ b/template/en/default/admin/groups/email/idle-member-removal.txt.tmpl
@@ -0,0 +1,26 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+Idle Group Member Removal Notification
+
+This email is to notify you, as the group owner for the
+'[% group.name %]' group, that the following accounts are no
+longer members of the group. Accounts who have not logged in
+to [% terms.BugzillaTitle %] in '[% group.idle_member_removal %]' days
+are automatically removed.
+
+[% FOREACH user = users %]
+* [% user.identity %]
+[% END %]
+
+--
+You are receiving this mail because: you are a group owner.
+
+@@body-headers@@
diff --git a/template/en/default/global/messages.html.tmpl b/template/en/default/global/messages.html.tmpl
index 83b20f4f6..b5c75bcc3 100644
--- a/template/en/default/global/messages.html.tmpl
+++ b/template/en/default/global/messages.html.tmpl
@@ -411,6 +411,8 @@
             <li>The group icon URL has been updated.</li>
           [% CASE 'owner_user_id' %]
             <li>The group owner was updated.</li>
+          [% CASE 'idle_member_removal' %]
+            <li>The idle member removal value has been updated.</li>
           [% CASE 'members_add' %]
             <li>The following groups are now members of this group:
               [%+ changes.members_add.join(', ') FILTER html %]</li>