From f6c796ad212c7cc62687cd02e3808245a993f6b1 Mon Sep 17 00:00:00 2001 From: "bugreport%peshkin.net" <> Date: Thu, 5 Aug 2004 04:36:23 +0000 Subject: Bug 185090: Add revamped whining system patch by: Erik r=joel r=jouni a=justdave --- editwhines.cgi | 433 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 433 insertions(+) create mode 100755 editwhines.cgi (limited to 'editwhines.cgi') diff --git a/editwhines.cgi b/editwhines.cgi new file mode 100755 index 000000000..5610f7eaa --- /dev/null +++ b/editwhines.cgi @@ -0,0 +1,433 @@ +#!/usr/bin/perl -wT +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Netscape Communications +# Corporation. Portions created by Netscape are +# Copyright (C) 1998 Netscape Communications Corporation. All +# Rights Reserved. +# +# Contributor(s): Erik Stambaugh +# + +################################################################################ +# Script Initialization +################################################################################ + +use strict; + +use lib "."; +require "CGI.pl"; +require "globals.pl"; + +use vars qw( $vars ); + +use Bugzilla::Constants; +use Bugzilla::User; +# require the user to have logged in +Bugzilla->login(LOGIN_REQUIRED); + +############################################################################### +# Main Body Execution +############################################################################### + +my $cgi = Bugzilla->cgi; +my $template = Bugzilla->template; +my $dbh = Bugzilla->dbh; + +my $user = Bugzilla->user; +my $userid = $user->id; + +my $sth; # database statement handle + +# $events is a hash ref, keyed by event id, that stores the active user's +# events. It starts off with: +# 'subject' - the subject line for the email message +# 'body' - the text to be sent at the top of the message +# +# Eventually, it winds up with: +# 'queries' - array ref containing hashes of: +# 'name' - the name of the saved query +# 'title' - The title line for the search results table +# 'sort' - Numeric sort ID +# 'id' - row ID for the query entry +# 'onemailperbug' - whether a single message must be sent for each +# result. +# 'schedule' - array ref containing hashes of: +# 'day' - Day or range of days this schedule will be run +# 'time' - time or interval to run +# 'mailto' - person who will receive the results +# 'id' - row ID for the schedule +my $events = get_events($userid); + +# First see if this user may use whines +ThrowUserError('whine_access_denied') unless (UserInGroup('bz_canusewhines')); + +# May this user send mail to other users? +my $can_mail_others = UserInGroup('bz_canusewhineatothers'); + +# If the form was submitted, we need to look for what needs to be added or +# removed, then what was altered. + +if ($cgi->param('update')) { + if ($cgi->param("add_event")) { + # we create a new event + $sth = $dbh->prepare("INSERT INTO whine_events " . + "(owner_userid) " . + "VALUES (?)"); + $sth->execute($userid); + } + else { + for my $eventid (keys %{$events}) { + # delete an entire event + if ($cgi->param("remove_event_$eventid")) { + # We need to make sure these belong to the same user, + # otherwise we could simply delete whatever matched that ID. + # + # schedules + $sth = $dbh->prepare("SELECT whine_schedules.id " . + "FROM whine_schedules " . + "LEFT JOIN whine_events " . + "ON whine_events.id = " . + "whine_schedules.eventid " . + "WHERE whine_events.id = ? " . + "AND whine_events.owner_userid = ?"); + $sth->execute($eventid, $userid); + my @ids = @{$sth->fetchall_arrayref}; + $sth = $dbh->prepare("DELETE FROM whine_schedules " + . "WHERE id=?"); + for (@ids) { + my $delete_id = $_->[0]; + $sth->execute($delete_id); + } + + # queries + $sth = $dbh->prepare("SELECT whine_queries.id " . + "FROM whine_queries " . + "LEFT JOIN whine_events " . + "ON whine_events.id = " . + "whine_queries.eventid " . + "WHERE whine_events.id = ? " . + "AND whine_events.owner_userid = ?"); + $sth->execute($eventid, $userid); + @ids = @{$sth->fetchall_arrayref}; + $sth = $dbh->prepare("DELETE FROM whine_queries " . + "WHERE id=?"); + for (@ids) { + my $delete_id = $_->[0]; + $sth->execute($delete_id); + } + + # events + $sth = $dbh->prepare("DELETE FROM whine_events " . + "WHERE id=? AND owner_userid=?"); + $sth->execute($eventid, $userid); + } + else { + # check the subject and body for changes + my $subject = ($cgi->param("event_${eventid}_subject") or ''); + my $body = ($cgi->param("event_${eventid}_body") or ''); + + trick_taint($subject) if $subject; + trick_taint($body) if $body; + + if ( ($subject ne $events->{$eventid}->{'subject'}) + || ($body ne $events->{$eventid}->{'body'}) ) { + + $sth = $dbh->prepare("UPDATE whine_events " . + "SET subject=?, body=? " . + "WHERE id=?"); + $sth->execute($subject, $body, $eventid); + } + + # add a schedule + if ($cgi->param("add_schedule_$eventid")) { + # the schedule table must be locked before altering + $sth = $dbh->prepare("INSERT INTO whine_schedules " . + "(eventid, mailto_userid, " . + "run_day, run_time) " . + "VALUES (?, ?, 'Sun', 2)"); + $sth->execute($eventid, $userid); + } + # add a query + elsif ($cgi->param("add_query_$eventid")) { + $sth = $dbh->prepare("INSERT INTO whine_queries " + . "(eventid) " + . "VALUES (?)"); + $sth->execute($eventid); + } + } + + # now check all of the schedules and queries to see if they need + # to be altered or deleted + + # Check schedules for changes + $sth = $dbh->prepare("SELECT id " . + "FROM whine_schedules " . + "WHERE eventid=?"); + $sth->execute($eventid); + my @scheduleids = (); + for (@{$sth->fetchall_arrayref}) { + push @scheduleids, $_->[0]; + }; + + # we need to double-check all of the user IDs in mailto to make + # sure they exist + my $arglist = {}; # args for match_field + for my $sid (@scheduleids) { + $arglist->{"mailto_$sid"} = { + 'type' => 'single', + }; + } + if (scalar %{$arglist}) { + &Bugzilla::User::match_field($arglist); + } + + for my $sid (@scheduleids) { + if ($cgi->param("remove_schedule_$sid")) { + # having the owner id in here is a security failsafe + $sth = $dbh->prepare("SELECT whine_schedules.id " . + "FROM whine_schedules " . + "LEFT JOIN whine_events " . + "ON whine_events.id = " . + "whine_schedules.eventid " . + "WHERE whine_events.owner_userid=? " . + "AND whine_schedules.id =?"); + $sth->execute($userid, $sid); + + my @ids = @{$sth->fetchall_arrayref}; + for (@ids) { + $sth = $dbh->prepare("DELETE FROM whine_schedules " . + "WHERE id=?"); + $sth->execute($_->[0]); + } + } + else { + my $o_day = $cgi->param("orig_day_$sid"); + my $day = $cgi->param("day_$sid"); + my $o_time = $cgi->param("orig_time_$sid"); + my $time = $cgi->param("time_$sid"); + my $o_mailto = $cgi->param("orig_mailto_$sid"); + my $mailto = $cgi->param("mailto_$sid"); + + $o_day = '' unless length($o_day); + $o_time = '' unless length($o_time); + $o_mailto = '' unless length($o_mailto); + $day = '' unless length($day); + $time = '' unless length($time); + $mailto = '' unless length($mailto); + + my $mail_uid = $userid; + + # get a userid for the mailto address + if ($can_mail_others and $mailto) { + trick_taint($mailto); + $mail_uid = DBname_to_id($mailto); + } + + if ( ($o_day ne $day) || + ($o_time ne $time) ){ + + trick_taint($day) if length($day); + trick_taint($time) if length($time); + + # the schedule table must be locked + $sth = $dbh->prepare("UPDATE whine_schedules " . + "SET run_day=?, run_time=?, " . + "mailto_userid=?, " . + "run_next=NULL " . + "WHERE id=?"); + $sth->execute($day, $time, $mail_uid, $sid); + } + } + } + + # Check queries for changes + $sth = $dbh->prepare("SELECT id " . + "FROM whine_queries " . + "WHERE eventid=?"); + $sth->execute($eventid); + my @queries = (); + for (@{$sth->fetchall_arrayref}) { + push @queries, $_->[0]; + }; + + for my $qid (@queries) { + if ($cgi->param("remove_query_$qid")) { + + $sth = $dbh->prepare("SELECT whine_queries.id " . + "FROM whine_queries " . + "LEFT JOIN whine_events " . + "ON whine_events.id = " . + "whine_queries.eventid " . + "WHERE whine_events.owner_userid=? " . + "AND whine_queries.id =?"); + $sth->execute($userid, $qid); + + for (@{$sth->fetchall_arrayref}) { + $sth = $dbh->prepare("DELETE FROM whine_queries " . + "WHERE id=?"); + $sth->execute($_->[0]); + } + } + else { + my $o_sort = $cgi->param("orig_query_sort_$qid"); + my $sort = $cgi->param("query_sort_$qid"); + my $o_queryname = $cgi->param("orig_query_name_$qid"); + my $queryname = $cgi->param("query_name_$qid"); + my $o_title = $cgi->param("orig_query_title_$qid"); + my $title = $cgi->param("query_title_$qid"); + my $o_onemailperbug = + $cgi->param("orig_query_onemailperbug_$qid"); + my $onemailperbug = + $cgi->param("query_onemailperbug_$qid"); + + $o_sort = '' unless length($o_sort); + $o_queryname = '' unless length($o_queryname); + $o_title = '' unless length($o_title); + $o_onemailperbug = '' unless length($o_onemailperbug); + $sort = '' unless length($sort); + $queryname = '' unless length($queryname); + $title = '' unless length($title); + $onemailperbug = '' unless length($onemailperbug); + + if ($onemailperbug eq 'on') { + $onemailperbug = 1; + } + elsif ($onemailperbug eq 'off') { + $onemailperbug = 0; + } + + if ( ($o_sort ne $sort) || + ($o_queryname ne $queryname) || + ($o_onemailperbug xor $onemailperbug) || + ($o_title ne $title) ){ + + detaint_natural($sort) if length $sort; + trick_taint($queryname) if length $queryname; + trick_taint($title) if length $title; + trick_taint($onemailperbug) if length $onemailperbug; + + $sth = $dbh->prepare("UPDATE whine_queries " . + "SET sortkey=?, " . + "query_name=?, " . + "title=?, " . + "onemailperbug=? " . + "WHERE id=?"); + $sth->execute($sort, $queryname, $title, + $onemailperbug, $qid); + } + } + } + } + } +} + +$vars->{'mail_others'} = $can_mail_others; + +# Return the appropriate HTTP response headers. +print $cgi->header(); + +# Get events again, to cover any updates that were made +$events = get_events($userid); + +# Here is the data layout as sent to the template: +# +# events +# event_id # +# schedule +# day +# time +# mailto +# queries +# name +# title +# sort +# +# build the whine list by event id +for my $event_id (keys %{$events}) { + + $events->{$event_id}->{'schedule'} = []; + $events->{$event_id}->{'queries'} = []; + + # schedules + $sth = $dbh->prepare("SELECT run_day, run_time, profiles.login_name, id " . + "FROM whine_schedules " . + "LEFT JOIN profiles " . + "ON whine_schedules.mailto_userid = " . + "profiles.userid " . + "WHERE eventid=?"); + $sth->execute($event_id); + for my $row (@{$sth->fetchall_arrayref}) { + my $this_schedule = { + 'day' => $row->[0], + 'time' => $row->[1], + 'mailto' => $row->[2], + 'id' => $row->[3], + }; + push @{$events->{$event_id}->{'schedule'}}, $this_schedule; + } + + # queries + $sth = $dbh->prepare("SELECT query_name, title, sortkey, id, " . + "onemailperbug " . + "FROM whine_queries " . + "WHERE eventid=? " . + "ORDER BY sortkey"); + $sth->execute($event_id); + for my $row (@{$sth->fetchall_arrayref}) { + my $this_query = { + 'name' => $row->[0], + 'title' => $row->[1], + 'sort' => $row->[2], + 'id' => $row->[3], + 'onemailperbug' => $row->[4], + }; + push @{$events->{$event_id}->{'queries'}}, $this_query; + } +} + +$vars->{'events'} = $events; + +# get the available queries +$sth = $dbh->prepare("SELECT name FROM namedqueries WHERE userid=?"); +$sth->execute($userid); + +$vars->{'available_queries'} = []; +while (my $query = $sth->fetch) { + push @{$vars->{'available_queries'}}, $query->[0]; +} + +$template->process("whine/schedule.html.tmpl", $vars) + || ThrowTemplateError($template->error()); + +# get_events takes a userid and returns a hash, keyed by event ID, containing +# the subject and body of each event that user owns +sub get_events { + my $userid = shift; + my $events = {}; + + my $sth = $dbh->prepare("SELECT DISTINCT id, subject, body " . + "FROM whine_events " . + "WHERE owner_userid=?"); + $sth->execute($userid); + for (@{$sth->fetchall_arrayref}) { + $events->{$_->[0]} = { + 'subject' => $_->[1], + 'body' => $_->[2], + } + } + return $events; +} + -- cgit v1.2.3-24-g4f1b