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