diff options
Diffstat (limited to 'extensions/RequestNagger/lib/Constants.pm')
-rw-r--r-- | extensions/RequestNagger/lib/Constants.pm | 104 |
1 files changed, 96 insertions, 8 deletions
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; |