Bug 1163868: Include requests from others in RequestNagger

2 files changed, 110 insertions, 10 deletions

diff --git a/extensions/RequestNagger/lib/Bug.pm b/extensions/RequestNagger/lib/Bug.pm
index de6d5eae5..bd5bb7fd7 100644
--- a/extensions/RequestNagger/lib/Bug.pm
+++ b/extensions/RequestNagger/lib/Bug.pm
@@ -9,10 +9,22 @@ package Bugzilla::Extension::RequestNagger::Bug;
 
 use strict;
 use parent qw(Bugzilla::Bug);
+use feature 'state';
+
+use Bugzilla::User;
 
 sub short_desc {
     my ($self) = @_;
-    return $self->{secure_bug} ? '(Secure bug)' : $self->SUPER::short_desc;
+    return $self->{sanitise_bug} ? '(Secure bug)' : $self->SUPER::short_desc;
+}
+
+sub is_private {
+    my ($self) = @_;
+    if (!exists $self->{is_private}) {
+        state $default_user //= Bugzilla::User->new();
+        $self->{is_private} = !$default_user->can_see_bug($self);
+    }
+    return $self->{is_private};
 }
 
 sub tooltip {
@@ -21,7 +33,7 @@ sub tooltip {
     if ($self->bug_status eq 'RESOLVED') {
         $tooltip .= '/' . $self->resolution;
     }
-    if (!$self->{secure_bug}) {
+    if (!$self->{sanitise_bug}) {
         $tooltip .= ' ' . $self->product . ' :: ' . $self->component;
     }
     return $tooltip;
diff --git a/extensions/RequestNagger/lib/Constants.pm b/extensions/RequestNagger/lib/Constants.pm
index f61e616a7..899336a72 100644
--- a/extensions/RequestNagger/lib/Constants.pm
+++ b/extensions/RequestNagger/lib/Constants.pm
@@ -13,7 +13,9 @@ use base qw(Exporter);
 our @EXPORT = qw(
     FLAG_TYPES
     REQUESTEE_NAG_SQL
-    WATCHING_NAG_SQL
+    SETTER_NAG_SQL
+    WATCHING_REQUESTEE_NAG_SQL
+    WATCHING_SETTER_NAG_SQL
 );
 
 # the order of this array determines the order used in email
@@ -47,12 +49,14 @@ sub REQUESTEE_NAG_SQL {
         flags.bug_id,
         flags.attach_id,
         flags.modification_date,
-        requester.userid AS requester_id,
-        requestee.userid AS requestee_id
+        setter.userid AS setter_id,
+        requestee.userid AS requestee_id,
+        flags.requestee_id AS recipient_id,
+        flags.requestee_id AS target_id
     FROM
         flags
         INNER JOIN flagtypes ON flagtypes.id = flags.type_id
-        INNER JOIN profiles AS requester ON requester.userid = flags.setter_id
+        INNER JOIN profiles AS setter ON setter.userid = flags.setter_id
         INNER JOIN profiles AS requestee ON requestee.userid = flags.requestee_id
         INNER JOIN bugs ON bugs.bug_id = flags.bug_id
         INNER JOIN products ON products.id = bugs.product_id
@@ -75,7 +79,48 @@ sub REQUESTEE_NAG_SQL {
     ";
 }
 
-sub WATCHING_NAG_SQL {
+sub SETTER_NAG_SQL {
+    my $dbh = Bugzilla->dbh;
+    my @flag_types_sql = map { $dbh->quote($_->{type}) } FLAG_TYPES;
+
+    return "
+    SELECT
+        flagtypes.name AS flag_type,
+        flags.id AS flag_id,
+        flags.bug_id,
+        flags.attach_id,
+        flags.modification_date,
+        setter.userid AS setter_id,
+        requestee.userid AS requestee_id,
+        flags.setter_id AS recipient_id,
+        flags.setter_id AS target_id
+    FROM
+        flags
+        INNER JOIN flagtypes ON flagtypes.id = flags.type_id
+        INNER JOIN profiles AS setter ON setter.userid = flags.setter_id
+        LEFT JOIN profiles AS requestee ON requestee.userid = flags.requestee_id
+        INNER JOIN bugs ON bugs.bug_id = flags.bug_id
+        INNER JOIN products ON products.id = bugs.product_id
+        LEFT JOIN attachments ON attachments.attach_id = flags.attach_id
+        LEFT JOIN profile_setting ON profile_setting.setting_name = 'request_nagging'
+                  AND profile_setting.user_id = flags.setter_id
+        LEFT JOIN nag_defer ON nag_defer.flag_id = flags.id
+    WHERE
+        " . $dbh->sql_in('flagtypes.name', \@flag_types_sql) . "
+        AND flags.status = '?'
+        AND products.nag_interval != 0
+        AND TIMESTAMPDIFF(HOUR, flags.modification_date, CURRENT_DATE()) >= products.nag_interval
+        AND (profile_setting.setting_value IS NULL OR profile_setting.setting_value = 'on')
+        AND setter.disable_mail = 0
+        AND nag_defer.id IS NULL
+    ORDER BY
+        flags.setter_id,
+        flagtypes.name,
+        flags.modification_date
+    ";
+}
+
+sub WATCHING_REQUESTEE_NAG_SQL {
     my $dbh = Bugzilla->dbh;
     my @flag_types_sql = map { $dbh->quote($_->{type}) } FLAG_TYPES;
 
@@ -87,12 +132,14 @@ sub WATCHING_NAG_SQL {
         flags.bug_id,
         flags.attach_id,
         flags.modification_date,
-        requester.userid AS requester_id,
-        requestee.userid AS requestee_id
+        setter.userid AS setter_id,
+        requestee.userid AS requestee_id,
+        nag_watch.watcher_id AS recipient_id,
+        requestee.userid AS target_id
     FROM
         flags
         INNER JOIN flagtypes ON flagtypes.id = flags.type_id
-        INNER JOIN profiles AS requester ON requester.userid = flags.setter_id
+        INNER JOIN profiles AS setter ON setter.userid = flags.setter_id
         INNER JOIN profiles AS requestee ON requestee.userid = flags.requestee_id
         INNER JOIN bugs ON bugs.bug_id = flags.bug_id
         INNER JOIN products ON products.id = bugs.product_id
@@ -113,4 +160,45 @@ sub WATCHING_NAG_SQL {
     ";
 }
 
+sub WATCHING_SETTER_NAG_SQL {
+    my $dbh = Bugzilla->dbh;
+    my @flag_types_sql = map { $dbh->quote($_->{type}) } FLAG_TYPES;
+
+    return "
+    SELECT
+        nag_watch.watcher_id,
+        flagtypes.name AS flag_type,
+        flags.id AS flag_id,
+        flags.bug_id,
+        flags.attach_id,
+        flags.modification_date,
+        setter.userid AS setter_id,
+        requestee.userid AS requestee_id,
+        nag_watch.watcher_id AS recipient_id,
+        setter.userid AS target_id
+    FROM
+        flags
+        INNER JOIN flagtypes ON flagtypes.id = flags.type_id
+        INNER JOIN profiles AS setter ON setter.userid = flags.setter_id
+        LEFT JOIN profiles AS requestee ON requestee.userid = flags.requestee_id
+        INNER JOIN bugs ON bugs.bug_id = flags.bug_id
+        INNER JOIN products ON products.id = bugs.product_id
+        LEFT JOIN attachments ON attachments.attach_id = flags.attach_id
+        LEFT JOIN nag_defer ON nag_defer.flag_id = flags.id
+        INNER JOIN nag_watch ON nag_watch.nagged_id = flags.setter_id
+        INNER JOIN profiles AS watcher ON watcher.userid = nag_watch.watcher_id
+    WHERE
+        " . $dbh->sql_in('flagtypes.name', \@flag_types_sql) . "
+        AND flags.status = '?'
+        AND products.nag_interval != 0
+        AND TIMESTAMPDIFF(HOUR, flags.modification_date, CURRENT_DATE()) >= products.nag_interval
+        AND watcher.disable_mail = 0
+    ORDER BY
+        nag_watch.watcher_id,
+        flags.requestee_id,
+        flags.modification_date
+    ";
+}
+
+
 1;