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