1#!/usr/local/bin/perl -T 2# This Source Code Form is subject to the terms of the Mozilla Public 3# License, v. 2.0. If a copy of the MPL was not distributed with this 4# file, You can obtain one at http://mozilla.org/MPL/2.0/. 5# 6# This Source Code Form is "Incompatible With Secondary Licenses", as 7# defined by the Mozilla Public License, v. 2.0. 8 9################################################################################ 10# Script Initialization 11################################################################################ 12 13use 5.10.1; 14use strict; 15use warnings; 16use lib qw(. lib); 17 18use Bugzilla; 19use Bugzilla::Constants; 20use Bugzilla::Search; 21use Bugzilla::User; 22use Bugzilla::Mailer qw(MessageToMTA generate_email); 23use Bugzilla::Util; 24use Bugzilla::Group; 25 26# create some handles that we'll need 27my $template = Bugzilla->template; 28my $dbh = Bugzilla->dbh; 29my $sth; 30 31# @seen_schedules is a list of all of the schedules that have already been 32# touched by reset_timer. If reset_timer sees a schedule more than once, it 33# sets it to NULL so it won't come up again until the next execution of 34# whine.pl 35my @seen_schedules = (); 36 37# These statement handles should live outside of their functions in order to 38# allow the database to keep their SQL compiled. 39my $sth_run_queries = 40 $dbh->prepare("SELECT " . 41 "query_name, title, onemailperbug " . 42 "FROM whine_queries " . 43 "WHERE eventid=? " . 44 "ORDER BY sortkey"); 45my $sth_get_query = 46 $dbh->prepare("SELECT query FROM namedqueries " . 47 "WHERE userid = ? AND name = ?"); 48 49# get the event that's scheduled with the lowest run_next value 50my $sth_next_scheduled_event = $dbh->prepare( 51 "SELECT " . 52 " whine_schedules.eventid, " . 53 " whine_events.owner_userid, " . 54 " whine_events.subject, " . 55 " whine_events.body, " . 56 " whine_events.mailifnobugs " . 57 "FROM whine_schedules " . 58 "LEFT JOIN whine_events " . 59 " ON whine_events.id = whine_schedules.eventid " . 60 "WHERE run_next <= NOW() " . 61 "ORDER BY run_next " . 62 $dbh->sql_limit(1) 63); 64 65# get all pending schedules matching an eventid 66my $sth_schedules_by_event = $dbh->prepare( 67 "SELECT id, mailto_type, mailto " . 68 "FROM whine_schedules " . 69 "WHERE eventid=? AND run_next <= NOW()" 70); 71 72 73################################################################################ 74# Main Body Execution 75################################################################################ 76 77# This script needs to check through the database for schedules that have 78# run_next set to NULL, which means that schedule is new or has been altered. 79# It then sets it to run immediately if the schedule entry has it running at 80# an interval like every hour, otherwise to the appropriate day and time. 81 82# After that, it looks over each user to see if they have schedules that need 83# running, then runs those and generates the email messages. 84 85# Send whines from the address in the 'mailfrom' Parameter so that all 86# Bugzilla-originated mail appears to come from a single address. 87my $fromaddress = Bugzilla->params->{'mailfrom'}; 88 89# get the current date and time 90my ($now_sec, $now_minute, $now_hour, $now_day, $now_month, $now_year, 91 $now_weekday) = localtime; 92# Convert year to two digits 93$now_year = sprintf("%02d", $now_year % 100); 94# Convert the month to January being "1" instead of January being "0". 95$now_month++; 96 97my @daysinmonth = qw(0 31 28 31 30 31 30 31 31 30 31 30 31); 98# Alter February in case of a leap year. This simple way to do it only 99# applies if you won't be looking at February of next year, which whining 100# doesn't need to do. 101if (($now_year % 4 == 0) && 102 (($now_year % 100 != 0) || ($now_year % 400 == 0))) { 103 $daysinmonth[2] = 29; 104} 105 106# run_day can contain either a calendar day (1, 2, 3...), a day of the week 107# (Mon, Tue, Wed...), a range of days (All, MF), or 'last' for the last day of 108# the month. 109# 110# run_time can contain either an hour (0, 1, 2...) or an interval 111# (60min, 30min, 15min). 112# 113# We go over each uninitialized schedule record and use its settings to 114# determine what the next time it runs should be 115my $sched_h = $dbh->prepare("SELECT id, run_day, run_time " . 116 "FROM whine_schedules " . 117 "WHERE run_next IS NULL" ); 118$sched_h->execute(); 119while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { 120 # fill in some defaults in case they're blank 121 $day ||= '0'; 122 $time ||= '0'; 123 124 # If this schedule is supposed to run today, we see if it's supposed to be 125 # run at a particular hour. If so, we set it for that hour, and if not, 126 # it runs at an interval over the course of a day, which means we should 127 # set it to run immediately. 128 if (&check_today($day)) { 129 # Values that are not entirely numeric are intervals, like "30min" 130 if ($time !~ /^\d+$/) { 131 # set it to now 132 $sth = $dbh->prepare( "UPDATE whine_schedules " . 133 "SET run_next=NOW() " . 134 "WHERE id=?"); 135 $sth->execute($schedule_id); 136 } 137 # A time greater than now means it still has to run today 138 elsif ($time >= $now_hour) { 139 # set it to today + number of hours 140 $sth = $dbh->prepare( 141 "UPDATE whine_schedules " . 142 "SET run_next = " . 143 $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'HOUR') . 144 " WHERE id = ?"); 145 $sth->execute($time, $schedule_id); 146 } 147 # the target time is less than the current time 148 else { # set it for the next applicable day 149 $day = &get_next_date($day); 150 my $run_next = $dbh->sql_date_math('(' 151 . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') 152 . ')', '+', '?', 'HOUR'); 153 $sth = $dbh->prepare("UPDATE whine_schedules " . 154 "SET run_next = $run_next 155 WHERE id = ?"); 156 $sth->execute($day, $time, $schedule_id); 157 } 158 159 } 160 # If the schedule is not supposed to run today, we set it to run on the 161 # appropriate date and time 162 else { 163 my $target_date = &get_next_date($day); 164 # If configured for a particular time, set it to that, otherwise 165 # midnight 166 my $target_time = ($time =~ /^\d+$/) ? $time : 0; 167 168 my $run_next = $dbh->sql_date_math('(' 169 . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') 170 . ')', '+', '?', 'HOUR'); 171 $sth = $dbh->prepare("UPDATE whine_schedules " . 172 "SET run_next = $run_next 173 WHERE id = ?"); 174 $sth->execute($target_date, $target_time, $schedule_id); 175 } 176} 177$sched_h->finish(); 178 179# get_next_event 180# 181# This function will: 182# 1. Lock whine_schedules 183# 2. Grab the most overdue pending schedules on the same event that must run 184# 3. Update those schedules' run_next value 185# 4. Unlock the table 186# 5. Return an event hashref 187# 188# The event hashref consists of: 189# eventid - ID of the event 190# author - user object for the event's creator 191# users - array of user objects for recipients 192# subject - Subject line for the email 193# body - the text inserted above the bug lists 194# mailifnobugs - send message even if there are no query or query results 195 196sub get_next_event { 197 my $event = {}; 198 199 # Loop until there's something to return 200 until (scalar keys %{$event}) { 201 202 $dbh->bz_start_transaction(); 203 204 # Get the event ID for the first pending schedule 205 $sth_next_scheduled_event->execute; 206 my $fetched = $sth_next_scheduled_event->fetch; 207 $sth_next_scheduled_event->finish; 208 return undef unless $fetched; 209 my ($eventid, $owner_id, $subject, $body, $mailifnobugs) = @{$fetched}; 210 211 my $owner = Bugzilla::User->new($owner_id); 212 213 my $whineatothers = $owner->in_group('bz_canusewhineatothers'); 214 215 my %user_objects; # Used for keeping track of who has been added 216 217 # Get all schedules that match that event ID and are pending 218 $sth_schedules_by_event->execute($eventid); 219 220 # Add the users from those schedules to the list 221 while (my $row = $sth_schedules_by_event->fetch) { 222 my ($sid, $mailto_type, $mailto) = @{$row}; 223 224 # Only bother doing any work if this user has whine permission 225 if ($owner->in_group('bz_canusewhines')) { 226 227 if ($mailto_type == MAILTO_USER) { 228 if (not defined $user_objects{$mailto}) { 229 if ($mailto == $owner_id) { 230 $user_objects{$mailto} = $owner; 231 } 232 elsif ($whineatothers) { 233 $user_objects{$mailto} = Bugzilla::User->new($mailto); 234 } 235 } 236 } 237 elsif ($mailto_type == MAILTO_GROUP) { 238 my $sth = $dbh->prepare("SELECT name FROM groups " . 239 "WHERE id=?"); 240 $sth->execute($mailto); 241 my $groupname = $sth->fetch->[0]; 242 my $group_id = Bugzilla::Group::ValidateGroupName( 243 $groupname, $owner); 244 if ($group_id) { 245 my $glist = join(',', 246 @{Bugzilla::Group->flatten_group_membership( 247 $group_id)}); 248 $sth = $dbh->prepare("SELECT user_id FROM " . 249 "user_group_map " . 250 "WHERE group_id IN ($glist)"); 251 $sth->execute(); 252 for my $row (@{$sth->fetchall_arrayref}) { 253 if (not defined $user_objects{$row->[0]}) { 254 $user_objects{$row->[0]} = 255 Bugzilla::User->new($row->[0]); 256 } 257 } 258 } 259 } 260 261 } 262 263 reset_timer($sid); 264 } 265 266 $dbh->bz_commit_transaction(); 267 268 # Only set $event if the user is allowed to do whining 269 if ($owner->in_group('bz_canusewhines')) { 270 my @users = values %user_objects; 271 $event = { 272 'eventid' => $eventid, 273 'author' => $owner, 274 'mailto' => \@users, 275 'subject' => $subject, 276 'body' => $body, 277 'mailifnobugs' => $mailifnobugs, 278 }; 279 } 280 } 281 return $event; 282} 283 284# Run the queries for each event 285# 286# $event: 287# eventid (the database ID for this event) 288# author (user object for who created the event) 289# mailto (array of user objects for mail targets) 290# subject (subject line for message) 291# body (text blurb at top of message) 292# mailifnobugs (send message even if there are no query or query results) 293while (my $event = get_next_event) { 294 295 my $eventid = $event->{'eventid'}; 296 297 # We loop for each target user because some of the queries will be using 298 # subjective pronouns 299 $dbh = Bugzilla->switch_to_shadow_db(); 300 for my $target (@{$event->{'mailto'}}) { 301 my $args = { 302 'subject' => $event->{'subject'}, 303 'body' => $event->{'body'}, 304 'eventid' => $event->{'eventid'}, 305 'author' => $event->{'author'}, 306 'recipient' => $target, 307 'from' => $fromaddress, 308 }; 309 310 # run the queries for this schedule 311 my $queries = run_queries($args); 312 313 # If mailifnobugs is false, make sure there is something to output 314 if (!$event->{'mailifnobugs'}) { 315 my $there_are_bugs = 0; 316 for my $query (@{$queries}) { 317 $there_are_bugs = 1 if scalar @{$query->{'bugs'}}; 318 } 319 next unless $there_are_bugs; 320 } 321 322 $args->{'queries'} = $queries; 323 324 mail($args); 325 } 326 $dbh = Bugzilla->switch_to_main_db(); 327} 328 329################################################################################ 330# Functions 331################################################################################ 332 333# The mail and run_queries functions use an anonymous hash ($args) for their 334# arguments, which are then passed to the templates. 335# 336# When run_queries is run, $args contains the following fields: 337# - body Message body defined in event 338# - from Bugzilla system email address 339# - queries array of hashes containing: 340# - bugs: array of hashes mapping fieldnames to values for this bug 341# - title: text title given to this query in the whine event 342# - columnlist: array of fieldnames to display in the mail 343# - name: text name of this query 344# - schedule_id integer id of the schedule being run 345# - subject Subject line for the message 346# - recipient user object for the recipient 347# - author user object of the person who created the whine event 348sub mail { 349 my $args = shift; 350 # Don't send mail to someone whose bugmail notification is disabled. 351 return if $args->{recipient}->email_disabled; 352 353 $args->{to_user} = $args->{recipient}; 354 MessageToMTA(generate_email( 355 $args, 356 { 357 header => 'whine/header.txt.tmpl', 358 text => 'whine/mail.txt.tmpl', 359 html => 'whine/mail.html.tmpl', 360 } 361 )); 362} 363 364# run_queries runs all of the queries associated with a schedule ID, adding 365# the results to $args or mailing off the template if a query wants individual 366# messages for each bug 367sub run_queries { 368 my $args = shift; 369 370 my $return_queries = []; 371 372 $sth_run_queries->execute($args->{'eventid'}); 373 my @queries = (); 374 for (@{$sth_run_queries->fetchall_arrayref}) { 375 push(@queries, 376 { 377 'name' => $_->[0], 378 'title' => $_->[1], 379 'onemailperbug' => $_->[2], 380 'columnlist' => [], 381 'bugs' => [], 382 } 383 ); 384 } 385 386 foreach my $thisquery (@queries) { 387 next unless $thisquery->{'name'}; # named query is blank 388 389 my $savedquery = get_query($thisquery->{'name'}, $args->{'author'}); 390 next unless $savedquery; # silently ignore missing queries 391 392 # Execute the saved query 393 my @searchfields = ('bug_id', DEFAULT_COLUMN_LIST); 394 395 # A new Bugzilla::CGI object needs to be created to allow 396 # Bugzilla::Search to execute a saved query. It's exceedingly weird, 397 # but that's how it works. 398 my $searchparams = new Bugzilla::CGI($savedquery); 399 400 # Use the columnlist for the saved query, if it exists, and make 401 # sure bug_id is always in the list. 402 if (my $columnlist = $searchparams->param('columnlist')) { 403 @searchfields = split(/[\s,]+/, $columnlist); 404 unshift(@searchfields, 'bug_id') unless grep { $_ eq 'bug_id' } @searchfields; 405 } 406 push @{$thisquery->{'columnlist'}}, @searchfields; 407 408 my @orderstrings = split(/,\s*/, $searchparams->param('order') || ''); 409 my $search = new Bugzilla::Search( 410 'fields' => \@searchfields, 411 'params' => scalar $searchparams->Vars, 412 'user' => $args->{'recipient'}, # the search runs as the recipient 413 'order' => \@orderstrings 414 ); 415 # If a query fails for whatever reason, it shouldn't kill the script. 416 my $data = eval { $search->data }; 417 if ($@) { 418 print STDERR get_text('whine_query_failed', { query_name => $thisquery->{'name'}, 419 author => $args->{'author'}, 420 reason => $@ }) . "\n"; 421 next; 422 } 423 424 foreach my $row (@$data) { 425 my $bug = {}; 426 for my $field (@searchfields) { 427 my $fieldname = $field; 428 $fieldname =~ s/^bugs\.//; # No need for bugs.whatever 429 $bug->{$fieldname} = shift @$row; 430 } 431 432 if ($thisquery->{'onemailperbug'}) { 433 $args->{'queries'} = [ 434 { 435 'name' => $thisquery->{'name'}, 436 'title' => $thisquery->{'title'}, 437 'columnlist' => $thisquery->{'columnlist'}, 438 'bugs' => [ $bug ], 439 }, 440 ]; 441 mail($args); 442 delete $args->{'queries'}; 443 } 444 else { # It belongs in one message with any other lists 445 push @{$thisquery->{'bugs'}}, $bug; 446 } 447 } 448 if (!$thisquery->{'onemailperbug'} && @{$thisquery->{'bugs'}}) { 449 push @{$return_queries}, $thisquery; 450 } 451 } 452 453 return $return_queries; 454} 455 456# get_query gets the namedquery. It's similar to LookupNamedQuery (in 457# buglist.cgi), but doesn't care if a query name really exists or not, since 458# individual named queries might go away without the whine_queries that point 459# to them being removed. 460sub get_query { 461 my ($name, $user) = @_; 462 my $qname = $name; 463 $sth_get_query->execute($user->id, $qname); 464 my $fetched = $sth_get_query->fetch; 465 $sth_get_query->finish; 466 return $fetched ? $fetched->[0] : ''; 467} 468 469# check_today gets a run day from the schedule and sees if it matches today 470# a run day value can contain any of: 471# - a three-letter day of the week 472# - a number for a day of the month 473# - 'last' for the last day of the month 474# - 'All' for every day 475# - 'MF' for every weekday 476 477sub check_today { 478 my $run_day = shift; 479 480 if (($run_day eq 'MF') 481 && ($now_weekday > 0) 482 && ($now_weekday < 6)) { 483 return 1; 484 } 485 elsif ( 486 length($run_day) == 3 && 487 index("SunMonTueWedThuFriSat", $run_day)/3 == $now_weekday) { 488 return 1; 489 } 490 elsif (($run_day eq 'All') 491 || (($run_day eq 'last') && 492 ($now_day == $daysinmonth[$now_month] )) 493 || ($run_day eq $now_day)) { 494 return 1; 495 } 496 return 0; 497} 498 499# reset_timer sets the next time a whine is supposed to run, assuming it just 500# ran moments ago. Its only parameter is a schedule ID. 501# 502# reset_timer does not lock the whine_schedules table. Anything that calls it 503# should do that itself. 504sub reset_timer { 505 my $schedule_id = shift; 506 507 # Schedules may not be executed more than once for each invocation of 508 # whine.pl -- there are legitimate circumstances that can cause this, like 509 # a set of whines that take a very long time to execute, so it's done 510 # quietly. 511 if (grep($_ == $schedule_id, @seen_schedules)) { 512 null_schedule($schedule_id); 513 return; 514 } 515 push @seen_schedules, $schedule_id; 516 517 $sth = $dbh->prepare( "SELECT run_day, run_time FROM whine_schedules " . 518 "WHERE id=?" ); 519 $sth->execute($schedule_id); 520 my ($run_day, $run_time) = $sth->fetchrow_array; 521 522 # It may happen that the run_time field is NULL or blank due to 523 # a bug in editwhines.cgi when this field was initially 0. 524 $run_time ||= 0; 525 526 my $run_today = 0; 527 my $minute_offset = 0; 528 529 # If the schedule is to run today, and it runs many times per day, 530 # it shall be set to run immediately. 531 $run_today = &check_today($run_day); 532 if (($run_today) && ($run_time !~ /^\d+$/)) { 533 # The default of 60 catches any bad value 534 my $minute_interval = 60; 535 if ($run_time =~ /^(\d+)min$/i) { 536 $minute_interval = $1; 537 } 538 539 # set the minute offset to the next interval point 540 $minute_offset = $minute_interval - ($now_minute % $minute_interval); 541 } 542 elsif (($run_today) && ($run_time > $now_hour)) { 543 # timed event for later today 544 # (This should only happen if, for example, an 11pm scheduled event 545 # didn't happen until after midnight) 546 $minute_offset = (60 * ($run_time - $now_hour)) - $now_minute; 547 } 548 else { 549 # it's not something that runs later today. 550 $minute_offset = 0; 551 552 # Set the target time if it's a specific hour 553 my $target_time = ($run_time =~ /^\d+$/) ? $run_time : 0; 554 555 my $nextdate = &get_next_date($run_day); 556 my $run_next = $dbh->sql_date_math('(' 557 . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') 558 . ')', '+', '?', 'HOUR'); 559 $sth = $dbh->prepare("UPDATE whine_schedules " . 560 "SET run_next = $run_next 561 WHERE id = ?"); 562 $sth->execute($nextdate, $target_time, $schedule_id); 563 return; 564 } 565 566 if ($minute_offset > 0) { 567 # Scheduling is done in terms of whole minutes. 568 569 my $next_run = $dbh->selectrow_array( 570 'SELECT ' . $dbh->sql_date_math('NOW()', '+', '?', 'MINUTE'), 571 undef, $minute_offset); 572 $next_run = format_time($next_run, "%Y-%m-%d %R"); 573 574 $sth = $dbh->prepare("UPDATE whine_schedules " . 575 "SET run_next = ? WHERE id = ?"); 576 $sth->execute($next_run, $schedule_id); 577 } else { 578 # The minute offset is zero or less, which is not supposed to happen. 579 # complain to STDERR 580 null_schedule($schedule_id); 581 print STDERR "Error: bad minute_offset for schedule ID $schedule_id\n"; 582 } 583} 584 585# null_schedule is used to safeguard against infinite loops. Schedules with 586# run_next set to NULL will not be available to get_next_event until they are 587# rescheduled, which only happens when whine.pl starts. 588sub null_schedule { 589 my $schedule_id = shift; 590 $sth = $dbh->prepare("UPDATE whine_schedules " . 591 "SET run_next = NULL " . 592 "WHERE id=?"); 593 $sth->execute($schedule_id); 594} 595 596# get_next_date determines the difference in days between now and the next 597# time a schedule should run, excluding today 598# 599# It takes a run_day argument (see check_today, above, for an explanation), 600# and returns an integer, representing a number of days. 601sub get_next_date { 602 my $day = shift; 603 604 my $add_days = 0; 605 606 if ($day eq 'All') { 607 $add_days = 1; 608 } 609 elsif ($day eq 'last') { 610 # next_date should contain the last day of this month, or next month 611 # if it's today 612 if ($daysinmonth[$now_month] == $now_day) { 613 my $month = $now_month + 1; 614 $month = 1 if $month > 12; 615 $add_days = $daysinmonth[$month] + 1; 616 } 617 else { 618 $add_days = $daysinmonth[$now_month] - $now_day; 619 } 620 } 621 elsif ($day eq 'MF') { # any day Monday through Friday 622 if ($now_weekday < 5) { # Sun-Thurs 623 $add_days = 1; 624 } 625 elsif ($now_weekday == 5) { # Friday 626 $add_days = 3; 627 } 628 else { # it's 6, Saturday 629 $add_days = 2; 630 } 631 } 632 elsif ($day !~ /^\d+$/) { # A specific day of the week 633 # The default is used if there is a bad value in the database, in 634 # which case we mark it to a less-popular day (Sunday) 635 my $day_num = 0; 636 637 if (length($day) == 3) { 638 $day_num = (index("SunMonTueWedThuFriSat", $day)/3) or 0; 639 } 640 641 $add_days = $day_num - $now_weekday; 642 if ($add_days <= 0) { # it's next week 643 $add_days += 7; 644 } 645 } 646 else { # it's a number, so we set it for that calendar day 647 $add_days = $day - $now_day; 648 # If it's already beyond that day this month, set it to the next one 649 if ($add_days <= 0) { 650 $add_days += $daysinmonth[$now_month]; 651 } 652 } 653 return $add_days; 654} 655