2009-04-16 Eric Roman <eroman@chromium.org>
[WebKit-https.git] / BugsSite / editwhines.cgi
1 #!/usr/bin/perl -wT
2 # -*- Mode: perl; indent-tabs-mode: nil -*-
3 #
4 # The contents of this file are subject to the Mozilla Public
5 # License Version 1.1 (the "License"); you may not use this file
6 # except in compliance with the License. You may obtain a copy of
7 # the License at http://www.mozilla.org/MPL/
8 #
9 # Software distributed under the License is distributed on an "AS
10 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
11 # implied. See the License for the specific language governing
12 # rights and limitations under the License.
13 #
14 # The Original Code is the Bugzilla Bug Tracking System.
15 #
16 # The Initial Developer of the Original Code is Netscape Communications
17 # Corporation. Portions created by Netscape are
18 # Copyright (C) 1998 Netscape Communications Corporation. All
19 # Rights Reserved.
20 #
21 # Contributor(s): Erik Stambaugh <erik@dasbistro.com>
22 #
23
24 ################################################################################
25 # Script Initialization
26 ################################################################################
27
28 use strict;
29
30 use lib ".";
31 require "CGI.pl";
32 require "globals.pl";
33
34 use vars qw( $vars );
35
36 use Bugzilla::Constants;
37 use Bugzilla::User;
38 use Bugzilla::Group;
39 # require the user to have logged in
40 Bugzilla->login(LOGIN_REQUIRED);
41
42 ###############################################################################
43 # Main Body Execution
44 ###############################################################################
45
46 my $cgi      = Bugzilla->cgi;
47 my $template = Bugzilla->template;
48 my $dbh      = Bugzilla->dbh;
49
50 my $user     = Bugzilla->user;
51 my $userid   = $user->id;
52
53 my $sth; # database statement handle
54
55 # $events is a hash ref, keyed by event id, that stores the active user's
56 # events.  It starts off with:
57 #  'subject' - the subject line for the email message
58 #  'body'    - the text to be sent at the top of the message
59 #
60 # Eventually, it winds up with:
61 #  'queries'  - array ref containing hashes of:
62 #       'name'          - the name of the saved query
63 #       'title'         - The title line for the search results table
64 #       'sort'          - Numeric sort ID
65 #       'id'            - row ID for the query entry
66 #       'onemailperbug' - whether a single message must be sent for each
67 #                         result.
68 #  'schedule' - array ref containing hashes of:
69 #       'day' - Day or range of days this schedule will be run
70 #       'time' - time or interval to run
71 #       'mailto_type' - MAILTO_USER or MAILTO_GROUP
72 #       'mailto' - person/group who will receive the results
73 #       'id' - row ID for the schedule
74 my $events = get_events($userid);
75
76 # First see if this user may use whines
77 UserInGroup("bz_canusewhines")
78   || ThrowUserError("auth_failure", {group  => "bz_canusewhines",
79                                      action => "schedule",
80                                      object => "reports"});
81
82 # May this user send mail to other users?
83 my $can_mail_others = UserInGroup('bz_canusewhineatothers');
84
85 # If the form was submitted, we need to look for what needs to be added or
86 # removed, then what was altered.
87
88 if ($cgi->param('update')) {
89     if ($cgi->param("add_event")) {
90         # we create a new event
91         $sth = $dbh->prepare("INSERT INTO whine_events " .
92                              "(owner_userid) " .
93                              "VALUES (?)");
94         $sth->execute($userid);
95     }
96     else {
97         for my $eventid (keys %{$events}) {
98             # delete an entire event
99             if ($cgi->param("remove_event_$eventid")) {
100                 # We need to make sure these belong to the same user,
101                 # otherwise we could simply delete whatever matched that ID.
102                 #
103                 # schedules
104                 $sth = $dbh->prepare("SELECT whine_schedules.id " .
105                                      "FROM whine_schedules " .
106                                      "LEFT JOIN whine_events " .
107                                      "ON whine_events.id = " .
108                                      "whine_schedules.eventid " .
109                                      "WHERE whine_events.id = ? " .
110                                      "AND whine_events.owner_userid = ?");
111                 $sth->execute($eventid, $userid);
112                 my @ids = @{$sth->fetchall_arrayref};
113                 $sth = $dbh->prepare("DELETE FROM whine_schedules "
114                     . "WHERE id=?");
115                 for (@ids) {
116                     my $delete_id = $_->[0];
117                     $sth->execute($delete_id);
118                 }
119
120                 # queries
121                 $sth = $dbh->prepare("SELECT whine_queries.id " .
122                                      "FROM whine_queries " .
123                                      "LEFT JOIN whine_events " .
124                                      "ON whine_events.id = " .
125                                      "whine_queries.eventid " .
126                                      "WHERE whine_events.id = ? " .
127                                      "AND whine_events.owner_userid = ?");
128                 $sth->execute($eventid, $userid);
129                 @ids = @{$sth->fetchall_arrayref};
130                 $sth = $dbh->prepare("DELETE FROM whine_queries " .
131                                      "WHERE id=?");
132                 for (@ids) {
133                     my $delete_id = $_->[0];
134                     $sth->execute($delete_id);
135                 }
136
137                 # events
138                 $sth = $dbh->prepare("DELETE FROM whine_events " .
139                                      "WHERE id=? AND owner_userid=?");
140                 $sth->execute($eventid, $userid);
141             }
142             else {
143                 # check the subject and body for changes
144                 my $subject = ($cgi->param("event_${eventid}_subject") or '');
145                 my $body    = ($cgi->param("event_${eventid}_body")    or '');
146
147                 trick_taint($subject) if $subject;
148                 trick_taint($body)    if $body;
149
150                 if ( ($subject ne $events->{$eventid}->{'subject'})
151                   || ($body    ne $events->{$eventid}->{'body'}) ) {
152
153                     $sth = $dbh->prepare("UPDATE whine_events " .
154                                          "SET subject=?, body=? " .
155                                          "WHERE id=?");
156                     $sth->execute($subject, $body, $eventid);
157                 }
158
159                 # add a schedule
160                 if ($cgi->param("add_schedule_$eventid")) {
161                     # the schedule table must be locked before altering
162                     $sth = $dbh->prepare("INSERT INTO whine_schedules " .
163                                          "(eventid, mailto_type, mailto, " .
164                                          "run_day, run_time) " .
165                                          "VALUES (?, ?, ?, 'Sun', 2)");
166                     $sth->execute($eventid, MAILTO_USER, $userid);
167                 }
168                 # add a query
169                 elsif ($cgi->param("add_query_$eventid")) {
170                     $sth = $dbh->prepare("INSERT INTO whine_queries "
171                         . "(eventid) "
172                         . "VALUES (?)");
173                     $sth->execute($eventid);
174                 }
175             }
176
177             # now check all of the schedules and queries to see if they need
178             # to be altered or deleted
179
180             # Check schedules for changes
181             $sth = $dbh->prepare("SELECT id " .
182                                  "FROM whine_schedules " .
183                                  "WHERE eventid=?");
184             $sth->execute($eventid);
185             my @scheduleids = ();
186             while (my ($sid) = $sth->fetchrow_array) {
187                 push @scheduleids, $sid;
188             }
189
190             # we need to double-check all of the user IDs in mailto to make
191             # sure they exist
192             my $arglist = {};   # args for match_field
193             for my $sid (@scheduleids) {
194                 if ($cgi->param("mailto_type_$sid") == MAILTO_USER) {
195                     $arglist->{"mailto_$sid"} = {
196                         'type' => 'single',
197                     };
198                 }
199             }
200             if (scalar %{$arglist}) {
201                 &Bugzilla::User::match_field($cgi, $arglist);
202             }
203
204             for my $sid (@scheduleids) {
205                 if ($cgi->param("remove_schedule_$sid")) {
206                     # having the assignee id in here is a security failsafe
207                     $sth = $dbh->prepare("SELECT whine_schedules.id " .
208                                          "FROM whine_schedules " .
209                                          "LEFT JOIN whine_events " .
210                                          "ON whine_events.id = " .
211                                          "whine_schedules.eventid " .
212                                          "WHERE whine_events.owner_userid=? " .
213                                          "AND whine_schedules.id =?");
214                     $sth->execute($userid, $sid);
215
216                     my @ids = @{$sth->fetchall_arrayref};
217                     for (@ids) {
218                         $sth = $dbh->prepare("DELETE FROM whine_schedules " .
219                                              "WHERE id=?");
220                         $sth->execute($_->[0]);
221                     }
222                 }
223                 else {
224                     my $o_day         = $cgi->param("orig_day_$sid") || '';
225                     my $day           = $cgi->param("day_$sid") || '';
226                     my $o_time        = $cgi->param("orig_time_$sid") || 0;
227                     my $time          = $cgi->param("time_$sid") || 0;
228                     my $o_mailto      = $cgi->param("orig_mailto_$sid") || '';
229                     my $mailto        = $cgi->param("mailto_$sid") || '';
230                     my $o_mailto_type = $cgi->param("orig_mailto_type_$sid") || 0;
231                     my $mailto_type   = $cgi->param("mailto_type_$sid") || 0;
232
233                     my $mailto_id = $userid;
234
235                     # get an id for the mailto address
236                     if ($can_mail_others && $mailto) {
237                         if ($mailto_type == MAILTO_USER) {
238                             # detaint
239                             my $emailregexp = Param('emailregexp');
240                             if ($mailto =~ /($emailregexp)/) {
241                                 $mailto_id = login_to_id($1);
242                             }
243                             else {
244                                 ThrowUserError("illegal_email_address", 
245                                                { addr => $mailto });
246                             }
247                         }
248                         elsif ($mailto_type == MAILTO_GROUP) {
249                             # detaint the group parameter
250                             if ($mailto =~ /^([0-9a-z_\-\.]+)$/i) {
251                                 $mailto_id = Bugzilla::Group::ValidateGroupName(
252                                                  $1, ($user)) || 
253                                              ThrowUserError(
254                                                  'invalid_group_name', 
255                                                  { name => $1 });
256                             } else {
257                                 ThrowUserError('invalid_group_name',
258                                                { name => $mailto });
259                             }
260                         }
261                         else {
262                             # bad value, so it will just mail to the whine
263                             # owner.  $mailto_id was already set above.
264                             $mailto_type = MAILTO_USER;
265                         }
266                     }
267
268                     detaint_natural($mailto_type);
269
270                     if ( ($o_day  ne $day) ||
271                          ($o_time ne $time) ||
272                          ($o_mailto ne $mailto) ||
273                          ($o_mailto_type != $mailto_type) ){
274
275                         trick_taint($day);
276                         trick_taint($time);
277
278                         # the schedule table must be locked
279                         $sth = $dbh->prepare("UPDATE whine_schedules " .
280                                              "SET run_day=?, run_time=?, " .
281                                              "mailto_type=?, mailto=?, " .
282                                              "run_next=NULL " .
283                                              "WHERE id=?");
284                         $sth->execute($day, $time, $mailto_type,
285                                       $mailto_id, $sid);
286                     }
287                 }
288             }
289
290             # Check queries for changes
291             $sth = $dbh->prepare("SELECT id " .
292                                  "FROM whine_queries " .
293                                  "WHERE eventid=?");
294             $sth->execute($eventid);
295             my @queries = ();
296             while (my ($qid) = $sth->fetchrow_array) {
297                 push @queries, $qid;
298             }
299
300             for my $qid (@queries) {
301                 if ($cgi->param("remove_query_$qid")) {
302
303                     $sth = $dbh->prepare("SELECT whine_queries.id " .
304                                          "FROM whine_queries " .
305                                          "LEFT JOIN whine_events " .
306                                          "ON whine_events.id = " .
307                                          "whine_queries.eventid " .
308                                          "WHERE whine_events.owner_userid=? " .
309                                          "AND whine_queries.id =?");
310                     $sth->execute($userid, $qid);
311
312                     for (@{$sth->fetchall_arrayref}) {
313                         $sth = $dbh->prepare("DELETE FROM whine_queries " .
314                                              "WHERE id=?");
315                         $sth->execute($_->[0]);
316                     }
317                 }
318                 else {
319                     my $o_sort      = $cgi->param("orig_query_sort_$qid") || 0;
320                     my $sort        = $cgi->param("query_sort_$qid") || 0;
321                     my $o_queryname = $cgi->param("orig_query_name_$qid") || '';
322                     my $queryname   = $cgi->param("query_name_$qid") || '';
323                     my $o_title     = $cgi->param("orig_query_title_$qid") || '';
324                     my $title       = $cgi->param("query_title_$qid") || '';
325                     my $o_onemailperbug =
326                             $cgi->param("orig_query_onemailperbug_$qid") || 0;
327                     my $onemailperbug   =
328                             $cgi->param("query_onemailperbug_$qid") ? 1 : 0;
329
330                     if ( ($o_sort != $sort) ||
331                          ($o_queryname ne $queryname) ||
332                          ($o_onemailperbug != $onemailperbug) ||
333                          ($o_title ne $title) ){
334
335                         detaint_natural($sort);
336                         trick_taint($queryname);
337                         trick_taint($title);
338
339                         $sth = $dbh->prepare("UPDATE whine_queries " .
340                                              "SET sortkey=?, " .
341                                              "query_name=?, " .
342                                              "title=?, " .
343                                              "onemailperbug=? " .
344                                              "WHERE id=?");
345                         $sth->execute($sort, $queryname, $title,
346                                       $onemailperbug, $qid);
347                     }
348                 }
349             }
350         }
351     }
352 }
353
354 $vars->{'mail_others'} = $can_mail_others;
355
356 # Return the appropriate HTTP response headers.
357 print $cgi->header();
358
359 # Get events again, to cover any updates that were made
360 $events = get_events($userid);
361
362 # Here is the data layout as sent to the template:
363 #
364 #   events
365 #       event_id #
366 #           schedule
367 #               day
368 #               time
369 #               mailto
370 #           queries
371 #               name
372 #               title
373 #               sort
374 #
375 # build the whine list by event id
376 for my $event_id (keys %{$events}) {
377
378     $events->{$event_id}->{'schedule'} = [];
379     $events->{$event_id}->{'queries'} = [];
380
381     # schedules
382     $sth = $dbh->prepare("SELECT run_day, run_time, mailto_type, mailto, id " .
383                          "FROM whine_schedules " .
384                          "WHERE eventid=?");
385     $sth->execute($event_id);
386     for my $row (@{$sth->fetchall_arrayref}) {
387         my $mailto_type = $row->[2];
388         my $mailto = '';
389         if ($mailto_type == MAILTO_USER) {
390             my $mailto_user = new Bugzilla::User($row->[3]);
391             $mailto = $mailto_user->login;
392         }
393         elsif ($mailto_type == MAILTO_GROUP) {
394             $sth = $dbh->prepare("SELECT name FROM groups WHERE id=?");
395             $sth->execute($row->[3]);
396             $mailto = $sth->fetch->[0];
397             $mailto = "" unless Bugzilla::Group::ValidateGroupName(
398                                 $mailto, ($user));
399         }
400         my $this_schedule = {
401             'day'         => $row->[0],
402             'time'        => $row->[1],
403             'mailto_type' => $mailto_type,
404             'mailto'      => $mailto,
405             'id'          => $row->[4],
406         };
407         push @{$events->{$event_id}->{'schedule'}}, $this_schedule;
408     }
409
410     # queries
411     $sth = $dbh->prepare("SELECT query_name, title, sortkey, id, " .
412                          "onemailperbug " .
413                          "FROM whine_queries " .
414                          "WHERE eventid=? " .
415                          "ORDER BY sortkey");
416     $sth->execute($event_id);
417     for my $row (@{$sth->fetchall_arrayref}) {
418         my $this_query = {
419             'name'          => $row->[0],
420             'title'         => $row->[1],
421             'sort'          => $row->[2],
422             'id'            => $row->[3],
423             'onemailperbug' => $row->[4],
424         };
425         push @{$events->{$event_id}->{'queries'}}, $this_query;
426     }
427 }
428
429 $vars->{'events'} = $events;
430
431 # get the available queries
432 $sth = $dbh->prepare("SELECT name FROM namedqueries WHERE userid=?");
433 $sth->execute($userid);
434
435 $vars->{'available_queries'} = [];
436 while (my ($query) = $sth->fetchrow_array) {
437     push @{$vars->{'available_queries'}}, $query;
438 }
439
440 $template->process("whine/schedule.html.tmpl", $vars)
441   || ThrowTemplateError($template->error());
442
443 # get_events takes a userid and returns a hash, keyed by event ID, containing
444 # the subject and body of each event that user owns
445 sub get_events {
446     my $userid = shift;
447     my $events = {};
448
449     my $sth = $dbh->prepare("SELECT DISTINCT id, subject, body " .
450                             "FROM whine_events " .
451                             "WHERE owner_userid=?");
452     $sth->execute($userid);
453     while (my ($ev, $sub, $bod) = $sth->fetchrow_array) {
454         $events->{$ev} = {
455             'subject' => $sub || '',
456             'body' => $bod || '',
457         };
458     }
459     return $events;
460 }
461