--- /dev/null
+#! /usr/bin/perl
+
+# ticket - Infodrom Trouble Ticket System
+# Copyright (c) 1997-8 Martin Schulze <joey@infodrom.north.de>
+
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
+
+use CGI;
+use DBI;
+use Date::Calc qw(Delta_Days);
+
+push (@INC, "/etc/noc");
+push (@INC, "/usr/lib/ticket");
+
+require 'ticket-config.pl';
+require 'ticket-db.pl';
+require 'ticket.pl';
+
+$progname = 'ticket';
+
+$html_title = "<h1>Trouble-Tickets</h1>\n\n";
+
+# Alle moeglichen Gruppen
+@ticket_groups = ();
+
+# Die Gruppen des Users
+@ticket_mygroups = ();
+
+%languages = ('ger', 'German',
+ 'gbr', 'English',
+ 'ita', 'Italian',
+ 'ost', 'Plattdeutsch');
+
+# left, middle, right
+@button_left = ('/','<','\\');
+@button_right = ('/','>','\\');
+
+%delimiter = ('Lynx','',
+ 'Mosaic',' ',
+ 'Mozilla',' ',
+ 'Chimera',' . ');
+
+# -----------------------------------------------------------------------
+
+# Sucht alle moeglichen User der angegebenen Gruppe(n) oder User heraus
+#
+sub get_users
+{
+ my $user = shift;
+ my $all = shift;
+ my $groups = "";
+ my $first = 1;
+ my %user = ();
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return if (!$user);
+ if (substr($user,0,1) ne ":") {
+ $groups = &user_groups($user) . &user_assoc($user);
+ $groups =~ s/::/:/;
+ if (!$groups) {
+ &index_user($user);
+ $groups = &user_groups($user);
+ }
+ } else {
+ $groups = $user;
+ }
+ $groups =~ s/^:(.*):$/$1/;
+
+ # Fetch all possible users and store them in our hashes
+ #
+ $query = "SELECT username,realname,email,groups,admin,maingroup FROM $ticket_table_user";
+ if (!$all) {
+ $query .= " WHERE active = 1 AND (";
+ } else {
+ $query .= " WHERE";
+ }
+ foreach $group (split(/:/,$groups)) {
+ $query .= " OR" if (!$first);
+ $query .= " groups LIKE '%:$group:%'";
+ $first = 0;
+ }
+ $query .= " )" if (!$all);
+ $query .= " ORDER BY realname";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ while (@row = $sth->fetchrow_array) {
+ &index_user_hashes($row[0],$row[1],$row[2],$row[3],$row[4]);
+ if ($row[0] && $row[1]) {
+ $user{$row[0]} = sprintf("%s (%s)", $row[1], $row[5]);
+ }
+ }
+ }
+ }
+ return %user;
+}
+
+# Sucht alle moeglichen Gruppen heraus
+#
+# ACHTUNG: Race-Condition: darf nicht 2x mit und ohne User aufgerufen
+# werden.
+#
+sub get_groups
+{
+ my $groups = "";
+ my $assoc = "";
+ my @groups;
+ my $group;
+ my $where = "";
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return @ticket_groups if ($#ticket_groups > -1);
+
+ if ($_[0]) {
+ $groups = &user_groups($_[0]);
+ $groups =~ s/^:(.+):$/$1/;
+
+ if ( exists($ticket_intern{'user'}) && exists($ticket_intern{'assoc'})
+ && $ticket_intern{'user'} eq $_[0]) {
+ $assoc = $ticket_intern{'assoc'};
+ } else {
+ $query = sprintf("SELECT assoc FROM $ticket_table_user WHERE username = '%s'", $_[0]);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $row[0] =~ s/^:(.+):$/$1/;
+ $assoc = $row[0];
+ }
+ }
+ }
+
+ $assoc =~ s/^:(.+):$/$1/;
+ @groups = split (/:/, $assoc);
+ foreach $group (@groups) {
+ $groups .= ":" . $group if ($groups !~ /:$group:/);
+ }
+ @ticket_groups = split (/:/, $groups);
+ } else {
+ $query = "SELECT name FROM $ticket_table_groups";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ while (@row = $sth->fetchrow_array) {
+ push (@ticket_groups, $row[0]);
+ }
+ }
+ }
+ }
+
+ $query = "SELECT name,description FROM $ticket_table_groups WHERE name = '";
+ $query .= join ("' OR name = '", @ticket_groups);
+ $query .= "'";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ while (@row = $sth->fetchrow_array) {
+ $groupname{$row[0]} = $row[1];
+ }
+ }
+ }
+ return @ticket_groups;
+}
+
+# Print some button, print them differently depending on the
+# browser. This routine could create some gif's on the fly if needed.
+#
+# [0] = 0, 1, 2 meaning top middle bottom
+# [1] = text
+# [1] = link
+sub print_button
+{
+ my $type = $_[0];
+ my $text = $_[1];
+ my $link = $_[2];
+
+ if ($ticket_browser eq 'Lynx') {
+ printf "<a href=\"%s\">%s%s%s</a> \n", $link, $button_left[$type], $text, $button_right[$type];
+ } else {
+ printf "<a href=\"%s\">%s</a> \n", $link, $text;
+ }
+}
+
+sub print_selection
+{
+ my $is_lynx = ($ticket_browser eq 'Lynx');
+
+ print "<hr>\n" if ($is_lynx);
+ print "<center>\n";
+ printf "<form method=post action=\"%s/selection\">", $ticket_html_base;
+ print "\n<input type=hidden name=admin value=\"y\">" if ($_[0] eq "admin");
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/list/open", &gettext("Open Tickets");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/insert", &gettext("New Ticket");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/list/closed", &gettext("Closed Tickets");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\"><br>", "/list/all", &gettext("All Tickets");
+
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/queries", &gettext("Queries");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/admin", &gettext("Admin");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\">", "/info", &gettext("Info");
+ print " ." if ($is_lynx);
+ printf "\n<input type=submit name=\"button%s\" value=\"%s\"><br>\n", "/help", &gettext("Help");
+ print "</form>\n";
+ print "</center>\n";
+}
+
+sub process_selection
+{
+ my $path = $ENV{'PATH_INFO'};
+ $ENV{'PATH_INFO'} = "";
+
+ if (defined $cgi->param('button/list/open')) {
+ $ENV{'PATH_INFO'} = "open";
+ &list();
+ } elsif (defined $cgi->param('button/list/closed')) {
+ $ENV{'PATH_INFO'} = "closed";
+ &list();
+ } elsif (defined $cgi->param('button/list/all')) {
+ $ENV{'PATH_INFO'} = "all";
+ &list();
+ } elsif (defined $cgi->param('button/insert')) {
+ &insert();
+ } elsif (defined $cgi->param('button/queries')) {
+ &queries();
+ } elsif (defined $cgi->param('button/info')) {
+ &info();
+ } elsif (defined $cgi->param('button/help')) {
+ &help();
+ } elsif (defined $cgi->param('button/admin')) {
+ $ENV{'PATH_INFO'} = "edit" if (!$is_admin);
+ &admin();
+ } elsif (defined $cgi->param('button/admin/newuser')) {
+ $ENV{'PATH_INFO'} = "newuser";
+ &admin();
+ } elsif (defined $cgi->param('button/admin/newgroup')) {
+ $ENV{'PATH_INFO'} = "newgroup";
+ &admin();
+ } elsif (defined $cgi->param('button/admin/newclass')) {
+ $ENV{'PATH_INFO'} = "newclass";
+ &admin();
+ }
+}
+
+sub print_select_priority
+{
+ my $prio = shift;
+
+ print "<select name=priority>\n";
+ printf "<option value=0%s>high\n", (" selected","","","")[$prio];
+ printf "<option value=1%s>medium\n", (""," selected","","")[$prio];;
+ printf "<option value=2%s>low\n" ,("",""," selected","")[$prio];;
+ printf "<option value=3%s>remind\n" ,("","",""," selected")[$prio];;
+ print "</select>";
+}
+
+sub print_select_class
+{
+ my $class = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $query = "SELECT name,description FROM $ticket_table_classes ORDER BY description";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ print "<select name=class>\n";
+ printf "<option value=>\n", if ($class eq '');
+ while (@row = $sth->fetchrow_array) {
+ printf "<option value=\"%s\"", &html_quote($row[0]);
+ print " selected" if ($class eq $row[0]);
+ printf ">%s\n", $row[1];
+ }
+ print "</select>";
+ }
+ }
+}
+
+sub print_select_anchor
+{
+ my $groups = shift;
+ my $anchor = shift;
+ my $group;
+ my $first = 1;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $query = "SELECT nr,subject,class FROM $ticket_table WHERE ";
+
+ if ($groups) {
+ $groups =~ s/^:(.*):$/$1/;
+ $query .= " (";
+ foreach $group (split(/:/,$groups)) {
+ $query .= " OR" if (!$first);
+ $query .= " groups LIKE '%:$group:%'";
+ $first = 0;
+ }
+ $query .= " OR" if (!$first);
+ $query .= sprintf(" nr = %d )", $anchor);
+ } else {
+ $groups = &user_groups($ENV{'REMOTE_USER'});
+ if ($groups) {
+ $query .= " (";
+ foreach $group (split(/:/,$groups)) {
+ $query .= " OR" if (!$first);
+ $query .= " groups LIKE '%:$group:%'";
+ $first = 0;
+ }
+ $query .= sprintf(" OR nr = %d )", $anchor);
+ } else {
+ $query .= sprintf (" insertp = '%s'", &user_email($ENV{'REMOTE_USER'}));
+ }
+ }
+
+ $query .= " AND closing = '' ORDER BY subject";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ print "<select name=anchor>\n";
+ print "<option value=\"0\"";
+ print " selected" if ($anchor == 0);
+ print "> \n";
+
+ while (@row = $sth->fetchrow_array) {
+ printf "<option value=\"%d\"", $row[0];
+ print " selected" if ($anchor == $row[0]);
+ printf ">#%d: %s (%s)\n", $row[0], substr($row[1], 0, 30), $row[2];
+ }
+ print "</select>";
+ } else {
+ print "<input name=anchor type=hidden size=1 value=0>";
+ }
+ } else {
+ print "<input name=anchor type=hidden size=1 value=0>";
+ }
+}
+
+sub textarea_size
+{
+ my $size;
+
+ if ($ticket_browser ne 'Lynx') {
+ $size = $_[0] / 70;
+ } else {
+ $size = $_[0] / 50;
+ }
+
+ if ($ticket_browser eq 'Mozilla') {
+ $size = ($size / 2);
+ }
+ return $size;
+}
+sub select_ticketuser
+{
+ my $name = shift;
+ my $selected;
+ my %tockuser;
+ my $user;
+
+ if ($_[0]) {
+ $selected = $_[0];
+ %ticketuser = &get_users($ENV{'REMOTE_USER'}) if (%ticketuser == ());
+ } else {
+ %ticketuser = &get_users($ENV{'REMOTE_USER'},"all") if (%ticketuser == ());
+ }
+
+ foreach $user (keys(%ticketuser)) {
+ $tockuser{$ticketuser{$user}} = $user;
+ }
+
+ printf "<select name=%s>\n", $name;
+ print "<option value=>\n" if ($#_ > -1);
+ foreach $user (sort(keys(%tockuser))) {
+ printf "<option value=\"%s\"", $tockuser{$user};
+ print " selected" if ($tockuser{$user} eq $selected);
+ printf ">%s\n", $user;
+ }
+ print "</select>";
+}
+
+sub select_ticketgroups
+{
+ my $name = $_[0];
+ my $selected = $_[1];
+ my $flag = $_[$#_];
+ my @ticketgroups;
+ my @sortgroups;
+ my $group;
+ my $half;
+ my $i;
+
+ if ($flag =~ /nopublic/) {
+ @ticketgroups = &get_groups();
+ } else {
+ @ticketgroups = &get_groups($ENV{'REMOTE_USER'});
+ }
+ push (@ticketgroups, '') if ($flag =~ /addempty/);
+ push (@ticketgroups, 'Public') if ($flag !~ /nopublic/);
+
+ @sortgroups = sort(@ticketgroups);
+ if (($flag !~ /nomultiple/) && ($ticket_intern{'misc'} =~ /tablegroups/) && $ticket_browser_table) {
+ $half = int(($#sortgroups+2)/2);
+
+ print "</pre><table width=100% border=0>\n";
+ $i=0;while ($i < $half) {
+
+ print " <tr>\n";
+ printf " <td width=50%%><input type=checkbox name=%s value=\"%s\"%s>%s</td>\n",
+ $name, $sortgroups[$i], $selected =~ /:$sortgroups[$i]:/?' checked':'', &groupname($sortgroups[$i]);
+ if ($half+$i <= ($#sortgroups+1)) {
+ printf " <td width=50%%><input type=checkbox name=%s value=\"%s\"%s>%s</td>\n",
+ $name, $sortgroups[$half+$i], $selected =~ /:$sortgroups[$half+$i]:/?' checked':'',
+ &groupname($sortgroups[$half+$i]);
+ } else {
+ printf " <td width=50%%></td>\n";
+ }
+ print " </tr>\n";
+ $i++;
+ }
+ print "</table><pre>\n";
+ } else {
+ printf "<select name=%s%s>\n", $name, $flag =~ /nomultiple/?'':' multiple size=5';
+ foreach $group (@sortgroups) {
+ printf "<option value=\"%s\"", $group;
+ print " selected" if ($selected =~ /:$group:/);
+ printf ">%s\n", &groupname($group);
+ }
+ print "</select>";
+ }
+}
+
+sub select_languages
+{
+ my $name = $_[0];
+ my $selected = $_[1];
+
+ printf "<select name=%s>\n", $name;
+ $lang = "gbr"; # default
+ printf "<option value=\"%s\"", $lang;
+ print " selected" if ($lang eq $selected);
+ printf ">%s\n", $languages{$lang};
+ foreach $lang (keys(%languages)) {
+ if (-r "$ticket_lib/ticket-$lang.pl") {
+ printf "<option value=\"%s\"", $lang;
+ print " selected" if ($lang eq $selected);
+ printf ">%s\n", $languages{$lang};
+ }
+ }
+ print "</select>";
+}
+
+sub select_programs
+{
+ my $name = $_[0];
+ my $selected = $_[1];
+
+ printf "<select name=%s multiple size=5>\n", $name;
+ foreach $prg (keys(%ticket_programs)) {
+ printf "<option value=\"%s\"", $prg;
+ print " selected" if ($selected =~ /:$prg:/);
+ printf ">%s\n", $ticket_programs{$prg};
+ }
+ print "</select>";
+}
+
+# [0] name
+# [1] radio || checkbox
+# [2] value
+# [3] text
+# [4] value= (opt)
+sub print_box
+{
+ my $value = "j";
+ $value = $_[4] if (length($_[4]));
+
+ printf "<input type=\"%s\" name=%s value=\"%s\"", $_[1], $_[0], $value;
+ print " checked" if ($_[2] eq $value);
+ printf ">%s", $_[3];
+}
+
+sub list_staff
+{
+ my $res = "";
+
+ $res = $_[0] if ($_[0]);
+ if ($_[1]) {
+ $res .= " " if ($res);
+ $res .= $_[1]
+ }
+ if ($_[2]) {
+ $res .= " " if ($res);
+ $res .= $_[2]
+ }
+ return $res;
+}
+
+sub print_actions
+{
+ my $nr = shift;
+ my @arr = ();
+ my $acc;
+ my $admin = (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))?1:undef;
+
+ if ($admin) {
+ $acc = 1;
+ } else {
+ $acc = &has_access($nr, $ENV{'REMOTE_USER'});
+ }
+
+ print "<center>\n";
+ printf "<form method=post action=\"%s/action\">\n", $ticket_html_base;
+ printf "<input type=hidden name=nr value=%d>\n", $nr;
+ printf "<input type=hidden name=anchor value=%d>\n", $nr;
+ printf "<input type=hidden name=admin value=y>\n" if ($admin);
+ push (@arr, sprintf("<input type=submit name=\"button%s\" value=\"%s\">", "/edit", sprintf(&gettext("Edit #%d"), $nr)))
+ if ($acc);
+ if ($_[0] ne "delete") {
+ push (@arr,sprintf("<input type=submit name=\"button%s\" value=\"%s\">", "/update",
+ sprintf(&gettext("Followup #%d"), $nr)));
+ push (@arr, sprintf("<input type=submit name=\"button%s\" value=\"%s\">", "/close",
+ sprintf(&gettext("Close #%d"), $nr)))
+ if ($acc && !&has_open_children($nr));
+ } else {
+ push (@arr, sprintf("<input type=submit name=\"button%s\" value=\"%s\">", "/clone",
+ sprintf(&gettext("Reopen #%d"), $nr)));
+ push (@arr, sprintf("<input type=submit name=\"button%s\" value=\"%s\">", "/delete",
+ sprintf(&gettext("Delete #%d"), $nr)))
+ if ($acc);
+
+ }
+ if ($ticket_browser eq 'Lynx') {
+ print join (" .\n", @arr) . "\n";
+ } else {
+ print join ("\n", @arr) . "\n";
+ }
+ print "</form>\n";
+ print "</center>\n";
+}
+
+sub process_actions
+{
+ $ENV{'PATH_INFO'} = "";
+
+ if (defined $cgi->param('button/edit')) {
+ &edit();
+ } elsif (defined $cgi->param('button/update')) {
+ &insert();
+ } elsif (defined $cgi->param('button/close')) {
+ &close();
+ } elsif (defined $cgi->param('button/clone')) {
+ &clone();
+ } elsif (defined $cgi->param('button/delete')) {
+ &delete();
+ } else {
+ &print_first_banner();
+ }
+}
+
+sub list_children
+{
+ my $nr = shift;
+ my $type = shift;
+ my $head = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $query = "SELECT nr,subject,class,staff0 FROM $ticket_table WHERE anchor = $nr";
+ $query .= " AND closing = ''" if ($type eq "open");
+ $query .= " AND closing <> ''" if ($type eq "closed");
+ $query .= " ORDER BY subject";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ printf "%s\n", $head if ($head);
+ print "<blockquote>\n";
+
+ while (@row = $sth->fetchrow_array) {
+ printf "#%d <a href=\"%s/listone?nr=%d\">%s (%s, %s)</a><br>\n",
+ $row[0], $ticket_html_base, $row[0], $row[1], &get_class_descrip($row[2]), $row[3];
+ }
+ print "</blockquote><p>\n";
+ }
+ }
+}
+
+sub print_banner
+{
+ my $typ = 2;
+
+ if ($ticket_browser_table && $ticket_intern{'misc'} =~ /tablelist/) {
+ printf "<!--\n \n", &gettext("The output is optimized for Mozilla like browsers.");
+ } else {
+ printf "<!--\n %s\n", &gettext("The output is optimized for Lynx, Chimera and Mosaic.");
+ }
+ printf " Detected browser: %s\n-->\n\n", $ticket_browser;
+
+ &eval_tickets($dbh,$_[0]);
+ $typ = 1 if ($_[0] =~ /closing <> ''/);
+ $typ = 0 if ($_[0] =~ /closing = ''/);
+ &print_tickets(0,$typ, (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))?"&admin=y":"");
+
+ &print_selection();
+}
+
+sub print_first_banner
+{
+ my $where = "";
+ my $i;
+
+ $where = "WHERE closing = '' AND ( groups LIKE '%:Public:%' ";
+ $i=0; while ($i < $ticket_staff) {
+ $where .= sprintf (" OR staff%d = '%s'", $i++, $ENV{'REMOTE_USER'});
+ }
+ $where .= ") AND priority <> 3";
+ &print_banner($where);
+}
+
+sub list
+{
+ my $tmp;
+ my $i;
+ my $expr;
+
+ if (!length($ENV{'PATH_INFO'})) {
+ &print_first_banner();
+ } elsif ($ENV{'PATH_INFO'} eq "open") {
+ &print_banner("WHERE closing = ''");
+ } elsif ($ENV{'PATH_INFO'} eq "closed") {
+ &print_banner("WHERE closing <> ''");
+ } elsif ($ENV{'PATH_INFO'} eq "all") {
+ &print_banner("");
+ } elsif ($ENV{'PATH_INFO'} eq "priority") {
+ &print_banner(sprintf ("WHERE closing = '' AND priority = %d", $cgi->param('priority')));
+ } elsif ($ENV{'PATH_INFO'} eq "class") {
+ &print_banner(sprintf ("WHERE closing = '' AND class = '%s'", $cgi->param('class')));
+ } elsif ($ENV{'PATH_INFO'} eq "user") {
+ $tmp = sprintf ("WHERE closing = '' AND ( insertp = '%s'", &user_email($cgi->param('user')));
+ $i=0; while ($i < $ticket_staff) {
+ $tmp .= sprintf (" OR staff%d = '%s'", $i++, $cgi->param('user'));
+ }
+ $tmp .= " )";
+ &print_banner ($tmp);
+# &print_banner(sprintf ("WHERE insertp = '%s' OR staff0 = '%s' OR staff1 = '%s' OR staff2 = '%s' AND closing = ''",
+# &user_email($cgi->param('user')),
+# $cgi->param('user'), $cgi->param('user'), $cgi->param('user')));
+ } elsif ($ENV{'PATH_INFO'} eq "group") {
+ &print_banner(sprintf ("WHERE closing = '' AND groups LIKE '%%:%s:%%'", $cgi->param('group')));
+ } elsif ($ENV{'PATH_INFO'} eq "subject") {
+ &print_banner(sprintf ("WHERE subject $ticket_clike '%s'", &sql_clike($cgi->param('keyword'))));
+ } elsif ($ENV{'PATH_INFO'} eq "search") {
+ $expr = &sql_clike($cgi->param('keyword'));
+ &print_banner(sprintf ("WHERE subject $ticket_clike '%s' OR body $ticket_clike '%s' OR closing $ticket_clike '%s'",
+ $expr, $expr, $expr));
+ } elsif ($ENV{'PATH_INFO'} eq "insertrange") {
+ $tmp = "";
+ if (length($cgi->param('insertdfrom'))) {
+ $tmp = sprintf ("insertd >= '%s'", &date_to_string($cgi->param('insertdfrom')));
+ }
+ if (length($cgi->param('insertdto'))) {
+ $tmp .= " AND " if (length($tmp));
+ $tmp .= sprintf ("insertd <= '%s'", &date_to_string($cgi->param('insertdto')));
+ }
+ $tmp = "WHERE " . $tmp if (length($tmp));
+ &print_banner($tmp);
+ } elsif ($ENV{'PATH_INFO'} eq "seireuq") {
+ print "<h3>Combined query</h3>\n<!-- ";
+ if ($cgi->param('open') eq 'y') {
+ print "Open";
+ $tmp = "WHERE closing = ''";
+ } else {
+ print "Closed";
+ $tmp = "WHERE closing <> ''";
+ }
+ if (length($cgi->param('staff'))) {
+ printf ", staff=%s", $cgi->param('staff');
+ $tmp .= sprintf (" AND ( insertp = '%s'", &user_email($cgi->param('staff')));
+ $i=0; while ($i < $ticket_staff) {
+ $tmp .= sprintf (" OR staff%d = '%s'", $i++, $cgi->param('staff'));
+ }
+ $tmp .= " )";
+ }
+ if (length($cgi->param('group'))) {
+ printf ", group=%s", $cgi->param('group');
+ $tmp .= sprintf (" AND groups LIKE '%%:%s:%%'", $cgi->param('group'));
+ }
+ if (length($cgi->param('class'))) {
+ printf ", class=%s", $cgi->param('class');
+ $tmp .= sprintf (" AND class = '%s'", $cgi->param('class'));
+ }
+ if (length($cgi->param('priority'))) {
+ printf ", priority=%d", $cgi->param('priority');
+ $tmp .= sprintf (" AND priority = %d", $cgi->param('priority'));
+ }
+ if (length($cgi->param('subject'))) {
+ printf ", subject=~%s", $cgi->param('subject');
+ $tmp .= sprintf (" AND subject $ticket_clike '%s'", &sql_clike($cgi->param('keyword')));
+ }
+ if (length($cgi->param('substr'))) {
+ printf ", ticket =~ %s", $cgi->param('substr');
+ $expr = &sql_clike($cgi->param('keyword'));
+ $tmp .= sprintf (" AND ( subject $ticket_clike '%s' OR body $ticket_clike '%s' OR closing $ticket_clike '%s' )",
+ $expr, $expr, $expr);
+ }
+ print " -->\n";
+ &print_banner($tmp);
+ } else {
+ &print_banner("WHERE closing = ''");
+ }
+}
+
+sub display_ticket
+{
+ my $nr = shift;
+ my $actions = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my @staff;
+
+ $query = sprintf("SELECT * FROM $ticket_table WHERE nr = %d",$nr);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ if ((defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))
+ || &is_visible($row[4+$ticket_staff], $row[10+$ticket_staff])) {
+ printf "<h1 align=center>Ticket #%d (%s)</h1>\n\n", $row[0], $row[1];
+ print "<pre>\n";
+ printf "<b>Subject </b>: %s\n", &de_msql($row[1]);
+ printf "<b>Class ..</b>: %s\n", &get_class_descrip($row[2]);
+ printf "<b>Priority</b>: %s\n", $ticket_priorities[$row[3]];
+ $i=0; @staff=();
+ while ($i < $ticket_staff) {
+ push (@staff, $row[4+$i]) if ($row[4+$i]);
+ $i++;
+ }
+ printf "<b>Staff ..</b>: %s\n", join (", ", &uniq_user_realname(@staff));
+ $row[4+$ticket_staff] =~ s/^:(.*):$/$1/;
+ $row[4+$ticket_staff] =~ s/:/, /g;
+ printf "<b>Gruppen </b>: %s\n", $row[4+$ticket_staff] if (length($row[4+$ticket_staff]));
+ printf "<b>Auftrag </b>: #%d <a href=\"%s/listone?nr=%d\">%s</a>\n",
+ $row[5+$ticket_staff], $ticket_html_base, $row[5+$ticket_staff],
+ &get_subject($row[5+$ticket_staff]) if ($row[5+$ticket_staff]);
+ printf "<b>Fixdate </b>: %s\n", &string_to_date($row[8+$ticket_staff]) if (length($row[8+$ticket_staff]));
+ printf "<b>Inserted</b>: %s by %s\n", &string_to_date($row[9+$ticket_staff]),
+ &email_realname($row[10+$ticket_staff]);
+ printf "<b>Closed .</b>: %s by %s\n", &string_to_date($row[11+$ticket_staff]),
+ &email_realname($row[12+$ticket_staff]) if (length($row[11+$ticket_staff]));
+ printf "<b>Modified</b>: %s by %s\n", &string_to_date($row[16+$ticket_staff]),
+ &email_realname($row[17+$ticket_staff]);
+ printf "<b>Time ...</b>: %d min\n", $row[13+$ticket_staff]
+ if ($row[13+$ticket_staff] && $row[13+$ticket_staff] > 0);
+ print "</pre>\n";
+ print "<b>Text</b>:\n";
+ if ($row[6+$ticket_staff] eq "j") {
+ print "<pre>\n";
+ } else {
+ print "<blockquote>\n";
+ }
+ printf "%s\n", &de_msql($row[7+$ticket_staff]);
+ if ($row[6+$ticket_staff] eq "j") {
+ print "</pre>\n";
+ } else {
+ print "</blockquote><p>\n";
+ }
+ if ($row[15+$ticket_staff]) {
+ printf "<b>Closing (%s)</b>:\n", &string_to_date($row[11+$ticket_staff]);
+ if ($row[14+$ticket_staff] eq "j") {
+ print "<pre>\n";
+ } else {
+ print "<blockquote>\n";
+ }
+ printf "%s\n", &de_msql($row[15+$ticket_staff]);
+ if ($row[14+$ticket_staff] eq "j") {
+ print "</pre>\n";
+ } else {
+ print "</blockquote><p>\n";
+ }
+ }
+
+ &list_children($row[0], "open", "<b>" . &gettext("Open children") . "</b>:");
+ &list_children($row[0], "closed", "<b>" . &gettext("Closed children") . "</b>:");
+ if ($actions) {
+ if (length($row[15+$ticket_staff])) {
+ &print_actions($row[0], "delete");
+ } else {
+ &print_actions($row[0]);
+ }
+ }
+ } else {
+ print "<h1>" . sprintf (&gettext("Access to ticket #%d denied!"), $nr) . "</h1>\n\n";
+ }
+ } else {
+ print "<h1>" . sprintf (&gettext("The ticket #%d does not exist!"), $nr) . "</h1>\n\n";
+ &print_selection() if ($action);
+ }
+ } else {
+ &error_query($query);
+ }
+}
+
+sub group_in_use
+{
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $query = sprintf("SELECT user FROM $ticket_table_user WHERE groups LIKE ':%s:' OR group = '%s'", $_[0],$_[0]);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return 1;
+ }
+ }
+ return 0;
+}
+
+sub listone
+{
+ my $nr = $cgi->param('nr');
+
+ if ($nr) {
+ &display_ticket($nr, 1);
+ } else {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), &gettext("Nr.")) . "</h3>\n\n";
+ }
+}
+
+sub edit
+{
+ my $nr = $cgi->param('nr');
+ my $i;
+ my $shortdate = &shortdate();
+ my $fieldlength, $lines;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $admin = (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))?1:undef;
+
+ if ($admin || &has_access($nr,$ENV{'REMOTE_USER'})) {
+
+ $query = "SELECT * FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ printf "<h1 align=center>" . sprintf(&gettext("Edit ticket #%d"), $nr) . "</h1>\n\n";
+ printf "<form action=\"%s/update\" method=post>\n", $ticket_html_base;
+ printf "<input name=nr type=hidden size=1 value=\"%d\">\n", $nr;
+ print "<input name=admin type=hidden value=\"y\">\n" if ($admin);
+ print "<pre>\n";
+ printf "<b>Subject </b>: <input name=subject size=55 maxlength=80 value=\"%s\">\n", &html_quote($row[1]);
+ print "<b>Klasse </b>: "; &print_select_class($row[2]);print "\n";
+ print "<b>Priority</b>: "; &print_select_priority($row[3]);print "\n";
+ printf "<b>Staff </b>: ";
+ $i=0;while ($i < $ticket_staff) {
+ $staff = sprintf ("staff%d", $i);
+ &select_ticketuser($staff,$row[4+$i]);
+ print "\n " if ($i % 2);
+ $i++;
+ }
+ print "\n" if ($i % 2);
+ printf "<b>Gruppen </b>: ";
+ &select_ticketgroups("groups",$row[4+$ticket_staff]);
+ print "<b>Auftrag </b>: "; &print_select_anchor($row[4+$ticket_staff],$row[5+$ticket_staff]);print "\n";
+ # 11 - insertd
+ printf "<input name=insertd type=hidden size=1 value=\"%s\">\n", $row[9+$ticket_staff];
+ printf "<input name=insertp type=hidden size=1 value=\"%s\">\n", $row[10+$ticket_staff];
+ printf &gettext("Inserted at %s by %s") . "\n", &string_to_date($row[9+$ticket_staff]),
+ &email_realname($row[10+$ticket_staff]);
+ # 14 - 18: closed, closep, closetime, closingpre, closing
+ if (length($row[15+$ticket_staff])) {
+ printf &gettext("Closed at %s by %s") . "\n", &string_to_date($row[11+$ticket_staff]),
+ &email_realname($row[12+$ticket_staff]);
+ }
+ printf &gettext("Last modified at %s by %s") . "<br>\n", &string_to_date($row[16+$ticket_staff]),
+ &email_realname($row[17+$ticket_staff]);
+
+ printf "<b>Text </b>: (max. %d) <b>Format <pre></b>: ", $ticket_length{'body'};
+ &print_box ("bodypre", "radio", $row[6+$ticket_staff], "ja ", "j");
+ &print_box ("bodypre", "radio", $row[6+$ticket_staff], "nein", "n");
+ print "\n" if ($ticket_browser ne 'Lynx');
+ $text = $row[7+$ticket_staff];
+ $text =~ s/(\r?\n)+$//g;
+ $text =~ s/(<p>\r?\n?)+$//;
+
+ $fieldlength = &textarea_size($ticket_length{'body'});
+ if ($ticket_browser eq 'Lynx') {
+ $lines = $text =~ tr/\n//;
+ if (($lines) + 3 > $fieldlength) {
+ $fieldlength = $ticket_length{'body'} / (length($text) / $lines) + 5;
+ }
+ }
+ printf "<textarea name=body cols=65 rows=%d wrap=physical>%s<hr>\n[%s/%s]</textarea><p>\n",
+ $fieldlength, $text, $shortdate, $ENV{'REMOTE_USER'};
+
+ printf "<b>Fixdate </b>: <input name=fixdate size=13 value=\"%s\">\n", &string_to_date($row[8+$ticket_staff]);
+ # 14 - 18: closed, closep, closetime, closingpre, closing
+ if (length($row[15+$ticket_staff])) {
+ printf "<b>Time </b>: <input name=closetime size=20 maxlength=10 value=\"%d\">min\n\n",
+ $row[13+$ticket_staff];
+ printf "<b>Closing (%s)</b>: (max. %d) <b>Format <pre></b>: ",
+ &string_to_date($row[11+$ticket_staff]), $ticket_length{'closing'};
+ &print_box ("closingpre", "radio", $row[14+$ticket_staff], "ja ", "j");
+ &print_box ("closingpre", "radio", $row[14+$ticket_staff], "nein", "n");
+ print "\n" if ($ticket_browser ne 'Lynx');
+
+ $text = $row[15+$ticket_staff];
+ $text =~ s/(\r?\n)+$//g;
+ $text =~ s/(<p>\r?\n?)+$//;
+
+ $fieldlength = &textarea_size($ticket_length{'closing'});
+ if ($ticket_browser eq 'Lynx') {
+ $lines = $text =~ tr/\n//;
+ if (($lines) + 3 > $fieldlength) {
+ $fieldlength = $ticket_length{'closing'} / (length($text) / $lines) + 5;
+ }
+ }
+
+ printf "<textarea name=closing cols=65 rows=%d wrap=physical>%s<hr>\n[%s/%s]</textarea><p>\n",
+ $fieldlength, $text, $shortdate, $ENV{'REMOTE_USER'};
+ }
+ print "</pre>\n";
+ &list_children($row[0], "open", "<b>" . &gettext("Open children") . "</b>:");
+ &list_children($row[0], "closed", "<b>" . &gettext("Closed children") . "</b>:");
+ printf "<center><input type=submit value=\"%s\">", &gettext("Update");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n";
+ } else {
+ &error_query($query);
+ }
+ } else {
+ &error_query($query);
+ }
+ } else {
+ printf &gettext("Access to ticket #%d denied!"), $nr;
+ }
+}
+
+sub insert
+{
+ my @addrs;
+ my $class = "Misc";
+ my $priority = 2;
+ my @groups;
+ my $groups;
+ my $subject;
+ my $staff;
+ my @staff;
+ my $i;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $val;
+
+ if (!length($cgi->param('subject'))) {
+ if (!$cgi->param('anchor') || $cgi->param('anchor') == 0) {
+ print "<h1 align=center>" . &gettext("Insert new ticket") . "</h1>\n\n";
+ $groups = &user_primary($ENV{'REMOTE_USER'});
+ } else {
+ $query = "SELECT class,priority";
+ $i=0; while ($i < $ticket_staff) {
+ $query .= sprintf (",staff%d", $i);
+ $i++;
+ }
+ $query .= ",groups,subject FROM $ticket_table WHERE nr = "
+ . $cgi->param('anchor');
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $class = $row[0];
+ $priority = $row[1];
+ $i=0; @staff=();while ($i < $ticket_staff) {
+ push (@staff, $row[2+$i++]);
+ }
+ $groups = $row[2+$ticket_staff];
+ $subject = $row[3+$ticket_staff];
+ }
+ }
+
+ printf "<h1 align=center>" . sprintf(&gettext("Followup ticket #%d (%s)"), $cgi->param('anchor'), $subject) . "</h1>\n\n";
+ $subject = substr($subject, 0, 20);
+ }
+
+ printf "<form action=\"%s/insertdata\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<b>Subject </b>: <input name=subject size=55 maxlength=80 value=\"%s\">\n", &html_quote($subject);
+ print "<b>Klasse </b>: "; &print_select_class($class);print "\n";
+ print "<b>Priority</b>: "; &print_select_priority($priority);print "\n";
+ printf "<b>Staff </b>: ";
+ $i=0;while ($i < $ticket_staff) {
+ $staff = sprintf ("staff%d", $i);
+ $val=$row[2+$i];
+ $val='none' if (!$val);
+ &select_ticketuser($staff,$val);
+ print "\n " if ($i % 2);
+ $i++;
+ }
+ print "\n" if ($i % 2);
+ print "<b>Gruppen </b>: ";
+ &select_ticketgroups("groups",":".$groups.":");
+ print "\n";
+ printf "<input name=anchor size=1 type=hidden value=\"%d\">", $cgi->param('anchor');
+ printf "<b>Text </b>: (max. %d) <b>Format <pre></b>: ", $ticket_length{'body'};
+ &print_box ("bodypre", "radio", "n", "ja ", "j");
+ &print_box ("bodypre", "radio", "n", "nein", "n");
+ print "\n" if ($ticket_browser ne 'Lynx');
+ printf "<textarea name=body cols=65 rows=%d wrap=physical></textarea><p>\n", &textarea_size($ticket_length{'body'});
+ print "<b>Fixdate </b>: <input name=fixdate size=13> <p>";
+ print "</pre>\n";
+
+ printf "<center><input type=submit value=\"%s\">", &gettext("Insert");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n\n";
+ } else {
+
+ @groups = $cgi->param('groups');
+ $i=0; @staff=();while ($i < $ticket_staff) {
+ $staff = sprintf ("staff%d", $i);
+ push (@staff, $cgi->param($staff)) if (length($cgi->param($staff)));
+ $i++;
+ }
+ # Args: (user,subject,class,priority,@staff,groups,anchor,bodypre,body,fixdate)) {
+ $nr = &ticket_insert($ENV{'REMOTE_USER'}, $cgi->param('subject'), $cgi->param('class'),
+ $cgi->param('priority'),\@staff,join (" ",@groups),$cgi->param('anchor'),
+ $cgi->param('bodypre'),$cgi->param('body'),$cgi->param('fixdate'));
+ if ($nr) {
+ printf "<h1 align=center>" . sprintf(&gettext("Ticket #%s (%s) opened"),
+ sprintf("<a href=\"%s/listone?nr=%d\">%d</a>",$ticket_html_base,$nr,$nr), $cgi->param('subject')) . "</h1>\n";
+ &print_selection();
+ } else {
+ &print_selection();
+ }
+ }
+}
+
+sub close
+{
+ my $nr = $cgi->param('nr');
+ my @addrs;
+ my @staff;
+ my $user;
+ my $i;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $url;
+ my $admin = (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))?1:undef;
+ my ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst);
+ my ($my_year,$my_mon,$my_day,$ti_year,$ti_mon,$ti_day);
+
+ if ($admin || &has_access($nr,$ENV{'REMOTE_USER'})) {
+
+ if (!length($nr)) {
+ &print_banner("WHERE closing = ''");
+ } elsif (!length($cgi->param('closing'))) {
+ if (ticket_is_closed($nr)) {
+ edit ();
+ return;
+ }
+# print "<h1 align=center>" . sprintf(&gettext("Close ticket #%d (%s) "), $nr, &get_subject($nr)) . "</h1>\n\n";
+
+ if (!&has_open_children($nr)) {
+ &display_ticket($nr,0);
+ printf "<form action=\"%s/close\" method=post>\n", $ticket_html_base;
+ print "<input name=admin type=hidden value=\"y\">\n" if ($admin);
+ printf "<input name=nr type=hidden size=1 value=\"%d\">\n", $nr;
+ print "<pre>\n";
+ print "<b>Time </b>: <input name=closetime size=20 maxlength=10> min\n\n";
+ printf "<b>Closing</b>: (max. %d): <b>Format <pre></b>: ", $ticket_length{'closing'};
+ &print_box ("closingpre", "radio", "n", "ja ", "j");
+ &print_box ("closingpre", "radio", "n", "nein", "n");
+ print "\n" if ($ticket_browser ne 'Lynx');
+ printf "<textarea name=closing cols=65 rows=%d wrap=physical></textarea><p>\n",
+ &textarea_size($ticket_length{'closing'});
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Close");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n";
+ } else {
+ # Dieses Ticket kann nicht geschlossen werden
+ print "<h3>" . &gettext("This ticket cannot be closed!") . "</h3>\n\n";
+ print &gettext("There are still open tickets:") . "<br>\n";
+ &list_children($nr, "open");
+ &print_actions($nr);
+ }
+ } else {
+ if (ticket_is_closed($nr)) {
+ print "<h3>" . sprintf (&gettext("Ticket#%d is already closed!"),$nr) . "<h3>";
+ return;
+ }
+ if (length($cgi->param('closing')) > ($ticket_length{'closing'}-1)) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not contain more than %d characters!"),
+ "Closing", $ticket_length{'closing'}) . "</h3>\n\n";
+ return;
+ }
+
+# printf "<h1 align=center>" . sprintf (&gettext("Close ticket #%d (%s) "), $nr, &get_subject($nr)) . "</h1>\n\n";
+
+ if (&has_open_children($nr)) {
+ print "<h3>" . &gettext("This ticket cannot be closed!") . "</h3>\n\n";
+ print &gettext("There are still open tickets:") . "<br>\n";
+ &list_children($nr, "open");
+ &print_actions($nr);
+ } else {
+
+ $user = &user_email($ENV{'REMOTE_USER'});
+ $date = &stringdate();
+
+ $query = sprintf ("UPDATE $ticket_table SET closetime=%d,closingpre='%s',closing=%s,closed='%s',closep='%s'" .
+ ",changed='%s',changep='%s'",
+ $cgi->param('closetime'),$cgi->param('closingpre'),
+ $dbh->quote(substr(&prepare_text($cgi->param('closing')),0,$ticket_length{'closing'}-1)),
+ $date, $user, $date, $user)
+ . " WHERE nr = $nr";
+
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h1 align=center>" . sprintf(&gettext("Ticket #%s is closed."),
+ sprintf("<a href=\"%s/listone?nr=%d\">%d</a>",$ticket_html_base,$nr,$nr)) . "</h1>\n";
+
+ $query = "SELECT insertp,subject,class,priority,groups,anchor,body,insertd";
+ $i=0;
+ while ($i < $ticket_staff) {
+ $query .= sprintf(",staff%d",$i++);
+ }
+ $query .= " FROM $ticket_table WHERE nr = $nr";
+
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $i=0; @staff=();
+ while ($i < $ticket_staff) {
+ push (@staff, $row[8+$i]) if ($row[8+$i] && length($row[8+$i]));
+ $i++;
+ }
+ }
+
+ # We send the mail from a cloned process in
+ # order to give a quick response to the web
+ # interface.
+ #
+ $| = 1; print ""; $| = 0;
+ if (&forkme() == 0) {
+ # The DBD subsystem reports an error, but all queries from before are finished already.
+ $dbh->disconnect;
+ $dbh = &noc_connect();
+ open (MAIL, "|$ticket_sendmailcmd");
+ push (@staff, $ENV{'REMOTE_USER'});
+ printf MAIL "To: %s\n", join(", ", &uniq_user_email(@staff));
+ pop (@staff);
+ printf MAIL "From: %s\n", $ticket_from;
+ printf MAIL "Bcc: %s\n", $ticket_bcc if ($ticket_bcc);
+ printf MAIL "Subject: [CLOSE] #%d: %s\n", $nr, &de_html($row[1]);
+ print MAIL "Precedence: junk\n";
+ printf MAIL "X-Mailer: ticket %s\n", $ticket_version;
+ $url = $ticket_html_proto . "://" . $ENV{'SERVER_NAME'};
+ $url .= ":" . $ENV{'SERVER_PORT'} if ($ENV{'SERVER_PORT'} != 80);
+ $url .= $ticket_html_base . "/listone?nr=" . $nr;
+ print MAIL "\n";
+ printf MAIL &gettext("%s has closed the following ticket on %s:") . "\n\n",
+ &user_realname($ENV{'REMOTE_USER'}), &string_to_date($date);
+ printf MAIL "Ticket ....: #%d\n", $nr;
+ printf MAIL "Subject ...: %s\n", &de_html($row[1]);
+ printf MAIL "Klasse ....: %s\n", &get_class_descrip($row[2]);
+ printf MAIL "Prioritaet : %s\n", $ticket_priorities[$row[3]];
+ printf MAIL "Mitarbeiter: %s\n", join (", ", &uniq_user_realname(@staff));
+ $row[4] =~ s/^:(.*):$/$1/;
+ $row[4] =~ s/:/, /g;
+ printf MAIL "Gruppen ...: %s\n", $row[4] if (length($row[4]));
+ printf MAIL "Auftrag ...: #%d (%s)\n", $row[5], &get_subject($row[5])
+ if ($row[5]);
+ ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
+ = localtime(time);
+ $my_year = $date_year+1900;
+ $my_mon = $date_mon+1;
+ $my_day = $date_mday;
+ $ti_year = substr($row[7],0,4);
+ $ti_mon = substr($row[7],4,2);
+ $ti_day = substr($row[7],6,2);
+ printf MAIL "Lifetime ..: %d day(s)\n", Delta_Days($ti_year,$ti_mon,$ti_day,$my_year,$my_mon,$my_day);
+ printf MAIL "Time ......: %d min\n", $cgi->param('closetime');
+ printf MAIL "URL .......: %s\n", $url;
+ printf MAIL "\n%s\n", &de_html($row[6]);
+ print MAIL "\n" . &gettext("The following reason was given:") . "\n\n";
+ printf MAIL "%s\n\n", &de_html($cgi->param('closing'));
+ printf MAIL "\n%s,\n\n\t%s\n", &gettext("Best regards"), &gettext("Your Ticket System");
+ print MAIL $ticket_signature;
+ close (MAIL);
+ exit (0);
+ }
+ }
+ } else {
+ &error_query($query);
+ }
+ &print_selection();
+ }
+ }
+ } else {
+ printf &gettext("Access to ticket #%d denied!"), $nr;
+ }
+}
+
+sub update
+{
+ my $nr = $cgi->param('nr');
+ my $user = &user_email($ENV{'REMOTE_USER'});
+ my @groups = $cgi->param('groups');
+ my $groups;
+ my $subject;
+ my $body;
+ my $closing;
+ my @staff;
+ my @staff;
+ my $x;
+ my $i;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $url;
+ my $stamp = undef;
+ my ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst);
+ my ($my_year,$my_mon,$my_day,$ti_year,$ti_mon,$ti_day);
+ my $admin = (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}))?1:undef;
+
+ printf "<h1 align=center>" . sprintf(&gettext("Edit ticket #%d (%s)"), $nr, &get_subject($nr)) . "</h1>\n\n";
+
+ if (!$admin && !&has_access($nr,$ENV{'REMOTE_USER'})) {
+ printf &gettext("Access to ticket #%d denied!"), $nr;
+ return;
+ }
+
+ $subject = $dbh->quote(substr($cgi->param('subject'), 0, $ticket_length{'subject'}-1));
+ $body = $cgi->param('body');
+ $body =~ s/\r//g; # Netscape on Windows inserts ^M
+ if (substr($body, length($body)-1, 1) eq "]") {
+ $stamp = sprintf ("[%s/%s]", &shortdate(), $ENV{'REMOTE_USER'});
+ $body = substr($body,0,length($body)-length($stamp)-5)
+ if (substr($body, length($body)-length($stamp)-5, 4) eq "<hr>");
+ }
+ $sqlbody = $dbh->quote(substr(&prepare_text($body),0,$ticket_length{'body'}-1));
+ $i=0; @staff = ();
+ while ($i < $ticket_staff) {
+ $x = sprintf ("staff%d", $i++);
+ push (@staff, $cgi->param($x)) if ($cgi->param($x) && length($cgi->param($x)));
+ }
+ if (!&ticket_is_valid("html",length($subject)-2,\@staff,join (" ",@groups),length($sqlbody)-2)) {
+ &print_selection();
+ } else {
+ $groups = ":" . join (":", @groups) . ":";
+ if (!length($cgi->param('closing'))) {
+ $query = sprintf ("UPDATE $ticket_table SET subject=%s,class='%s',anchor=%d,priority=%d,"
+ . "groups='%s',bodypre='%s',body=%s,fixdate='%s',changed='%s',changep='%s'",
+ $subject, $cgi->param('class'), $cgi->param('anchor'),
+ $cgi->param('priority'), $groups, $cgi->param('bodypre'),
+ $sqlbody,
+ &date_to_string($cgi->param('fixdate')), &stringdate(), $user);
+ } else {
+ $closing = $cgi->param('closing');
+ $closing =~ s/\r//g; # Netscape on Windows inserts ^M
+ if (substr($closing, length($closing)-1, 1) eq "]") {
+ $stamp = sprintf ("[%s/%s]", &shortdate(), $ENV{'REMOTE_USER'}) if (!$stamp);
+ $closing = substr($closing,0,length($closing)-length($stamp)-5)
+ if (substr($closing, length($closing)-length($stamp)-5, 4) eq "<hr>");
+ }
+ $query = sprintf ("UPDATE $ticket_table SET subject=%s,class='%s',anchor=%d,priority=%d,"
+ . "groups='%s',bodypre='%s',body=%s,"
+ . "closetime=%d,closingpre='%s',closing=%s,"
+ . "fixdate='%s',changed='%s',changep='%s'",
+ $subject, $cgi->param('class'), $cgi->param('anchor'),
+ $cgi->param('priority'), $groups, $cgi->param('bodypre'),
+ $sqlbody,
+ $cgi->param('closetime'), $cgi->param('closingpre'),
+ $dbh->quote(substr(&prepare_text($closing),0,$ticket_length{'closing'}-1)),
+ &date_to_string($cgi->param('fixdate')), &stringdate(), $user);
+ }
+ $i=0;
+ while ($i < $ticket_staff) {
+ $query .= sprintf(",staff%d='%s'", $i, $staff[$i]);
+ $i++;
+ }
+ $query .= " WHERE nr = $nr";
+
+ $sth = $dbh->do($query);
+
+ if ($sth) {
+ printf "<h1 align=center>" . sprintf(&gettext("Ticket #%s (%s) was updated."),
+ sprintf("<a href=\"%s/listone?nr=%d\">%d</a>",$ticket_html_base,$nr,$nr), &get_subject($nr)) . "</h1>\n";
+
+ # We send the mail from a cloned process in
+ # order to give a quick response to the web
+ # interface.
+ #
+ $| = 1; print ""; $| = 0;
+ if (&forkme() == 0) {
+ $dbh->disconnect;
+ $dbh = &noc_connect();
+ open (MAIL, "|$ticket_sendmailcmd");
+ push (@staff, $ENV{'REMOTE_USER'});
+ printf MAIL "To: %s\n", join(", ", &uniq_user_email(@staff));
+ pop (@staff);
+ printf MAIL "From: %s\n", $ticket_from;
+ printf MAIL "Bcc: %s\n", $ticket_bcc if ($ticket_bcc);
+ printf MAIL "Subject: [UPDATE] #%d: %s\n", $nr, &de_html($cgi->param('subject'));
+ print MAIL "Precedence: junk\n";
+ printf MAIL "X-Mailer: ticket %s\n", $ticket_version;
+ print MAIL "\n";
+ printf MAIL &gettext("%s has updated the following ticket on %s:") . "\n\n",
+ &user_realname($ENV{'REMOTE_USER'}), &string_to_date(&stringdate());
+ printf MAIL "Ticket ....: #%d\n", $nr;
+ printf MAIL "Subject ...: %s\n", &de_html($cgi->param('subject'));
+ printf MAIL "Klasse ....: %s\n", &get_class_descrip($cgi->param('class'));
+ printf MAIL "Prioritaet : %s\n", $ticket_priorities[$cgi->param('priority')];
+ printf MAIL "Mitarbeiter: %s\n", join(", ", &uniq_user_realname(@staff));
+ printf MAIL "Gruppen ...: %s\n", join (", ", @groups) if ($#groups > -1);
+ printf MAIL "Auftrag ...: #%d (%s)\n", $cgi->param('anchor'), &get_subject($cgi->param('anchor'))
+ if ($cgi->param('anchor'));
+ ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
+ = localtime(time);
+ $my_year = $date_year+1900;
+ $my_mon = $date_mon+1;
+ $my_day = $date_mday;
+ $ti_year = substr($cgi->param('insertd'),0,4);
+ $ti_mon = substr($cgi->param('insertd'),4,2);
+ $ti_day = substr($cgi->param('insertd'),6,2);
+ printf MAIL "Lifetime ..: %d day(s)\n", Delta_Days($ti_year,$ti_mon,$ti_day,$my_year,$my_mon,$my_day);
+ printf MAIL "Inserted ..: %s by %s\n", string_to_date($cgi->param('insertd')),
+ &email_realname($cgi->param('insertp'));
+ $url = $ticket_html_proto . "://" . $ENV{'SERVER_NAME'};
+ $url .= ":" . $ENV{'SERVER_PORT'} if ($ENV{'SERVER_PORT'} != 80);
+ $url .= $ticket_html_base . "/listone?nr=" . $nr;
+ printf MAIL "URL .......: %s\n", $url;
+ printf MAIL "\n%s\n", &de_html($body);
+ if (length($cgi->param('closing'))) {
+ print MAIL &gettext("This ticket is already closed:") . "\n";
+ printf MAIL "%s\n", &de_html($cgi->param('closing'));
+ } else {
+ print MAIL sprintf ("\n" . &gettext("%s was set as fixdate."), $cgi->param('fixdate'))
+ . "\n\n" if (length($cgi->param('fixdate')));
+ }
+ printf MAIL "\n%s,\n\n\t%s\n", &gettext("Best regards"), &gettext("Your Ticket System");
+ print MAIL $ticket_signature;
+ close (MAIL);
+ exit (0);
+ }
+ &print_selection();
+ } else {
+ &error_query($query);
+ }
+ }
+}
+
+sub delete
+{
+ my $nr = $cgi->param('nr');
+ my $subject = &get_subject($nr);
+ my @staff;
+ my $i;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ print "<h1 align=center>" . sprintf (&gettext("Remove ticket #%d (%s) "), $nr, $subject) . "</h1>\n\n";
+
+ if ((defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'})) || &has_access($nr,$ENV{'REMOTE_USER'})) {
+ $query = "SELECT insertp";
+ $i=0; while ($i < $ticket_staff) {
+ $query .= sprintf(",staff%d",$i++);
+ }
+ $query .= ",closing FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ if (length($row[1+$ticket_staff])) {
+ $query = "DELETE FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->do($query);
+
+ print "<h1 align=center>" . sprintf(&gettext("Ticket #%d has been removed from the database"), $nr, $subject)
+ . "</h1>\n";
+
+ $date = &stringdate();
+ $i=0; @staff=();
+ while ($i < $ticket_staff) {
+ push (@staff, $row[1+$i]) if ($row[1+$i]);
+ $i++;
+ }
+ # We send the mail from a cloned process in
+ # order to give a quick response to the web
+ # interface.
+ #
+ $| = 1; print ""; $| = 0;
+ if (&forkme(1) == 0) {
+ $dbh->disconnect;
+ $dbh = &noc_connect();
+ open (MAIL, "|$ticket_sendmailcmd");
+ push (@staff, $ENV{'REMOTE_USER'});
+ printf MAIL "To: %s\n", join(", ", &uniq_user_email(@staff));
+ pop (@staff);
+ printf MAIL "From: %s\n", $ticket_from;
+ printf MAIL "Bcc: %s\n", $ticket_bcc if ($ticket_bcc);
+ printf MAIL "Subject: [DELETE] #%d: %s\n", $nr, &de_html($subject);
+ print MAIL "Precedence: junk\n";
+ printf MAIL "X-Mailer: ticket %s\n", $ticket_version;
+ printf MAIL &gettext("%s has removed the following ticket from the database on %s:")
+ . "\n\n", &user_realname($ENV{'REMOTE_USER'}), &string_to_date(&stringdate());
+ printf MAIL "Ticket ....: #%d\n", $nr;
+ printf MAIL "Subject ...: %s\n", &de_html($subject);
+ printf MAIL "Inserted ..: %s\n", $row[0];
+ printf MAIL "Mitarbeiter: %s\n", join (", ", &uniq_user_realname(@staff));
+ printf MAIL "\n%s,\n\n\t%s\n", &gettext("Best regards"), &gettext("Your Ticket System");
+ print MAIL $ticket_signature;
+ close (MAIL);
+ exit (0);
+ }
+ &print_selection();
+ } else {
+ printf &gettext("This ticket is not yet closed.") . "\n";
+ }
+ } else {
+ &error_query($query);
+ }
+ } else {
+ &error_query($query);
+ }
+ } else {
+ printf &gettext("Access to ticket #%d denied!"), $nr;
+ }
+}
+
+sub clone
+{
+ my $nr = $cgi->param('nr');
+ my $subject = &get_subject($nr);
+ my @staff;
+ my $i;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $newnr;
+ my $body;
+ my $groups;
+
+ print "<h1 align=center>" . sprintf (&gettext("Reopen ticket #%d (%s) "), $nr, $subject) . "</h1>\n\n";
+
+ $query = "SELECT subject,class,priority,groups,bodypre,body";
+ $i=0; while ($i < $ticket_staff) {
+ $query .= sprintf(",staff%d",$i++);
+ }
+ $query .= " FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $i=0; @staff=();
+ while ($i < $ticket_staff) {
+ push (@staff, $row[6+$i]) if ($row[6+$i]);
+ $i++;
+ }
+ $body = $row[5];
+ $body =~ s:\[([^/\]]*)/([^\]]*)\].*::;
+ $groups = $row[3];
+ $groups =~ s/^:(.*):$/\1/;
+
+ $newnr = &ticket_insert($ENV{'REMOTE_USER'}, $row[0], $row[1], $row[2],
+ \@staff, $groups, 0, $row[4], $body,'');
+ if ($newnr) {
+ $query = "UPDATE $ticket_table SET anchor=$newnr WHERE nr = $nr";
+ $sth = $dbh->do($query);
+
+ printf "<h1 align=center>" . sprintf(&gettext("Ticket #%s (%s) opened"),
+ sprintf("<a href=\"%s/listone?nr=%d\">%d</a>",$ticket_html_base,$newnr,$newnr), $subject)
+ . "</h1>\n";
+
+ &print_selection();
+ } else {
+ &error_query($query);
+ }
+ } else {
+ &error_query($query);
+ }
+ } else {
+ &error_query($query);
+ }
+}
+
+sub admin
+{
+ my $tmp;
+ my @progs;
+ my @assoc;
+ my $grouplist;
+ my $group;
+ my $user;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my @misc;
+
+ if ($cgi->param('user')) {
+ $user = $cgi->param('user');
+ } else {
+ $user = $ENV{'REMOTE_USER'}
+ }
+ if ((!$is_admin) &&
+ !($user eq $ENV{'REMOTE_USER'} && (($ENV{'PATH_INFO'} eq "edit") || ($ENV{'PATH_INFO'} eq "update")))) {
+ print "<h1>" . &gettext("Access to admin pages denied!") . "</h1>\n\n";
+ print "<h3>" . &gettext("Ask the maintainer for permission.") . "</h3>\n\n";
+ &print_selection();
+ return;
+ }
+
+ if (!length($ENV{'PATH_INFO'})) {
+ printf "<form method=post action=\"%s/selection\">\n", $ticket_html_base;
+ $query = "SELECT realname,username,email,groups,active FROM $ticket_table_user ORDER by realname";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ print "\n<center><h1>" . &gettext("List of users") . "</h1></center>\n\n";
+ if ($ticket_browser_table) {
+ print "<table width=100% border=1 cellpadding=2 cellspacing=0>\n";
+ print "<tr bgcolor=\"#c0c0c0\">\n";
+ print " <th>Realname</th>\n";
+ print " <th>User</th>\n";
+ print " <th>Email</th>\n";
+ print " <th>Groups</th>\n";
+ print "</tr>\n";
+ } else {
+ print "<ol>\n";
+ }
+ while (@row = $sth->fetchrow_array) {
+ $row[3] =~ s/^:(.*):$/$1/;
+ $row[3] =~ s/:/ /g;
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ printf " <td align=left><a href=\"%s/admin/edit?user=%s\">%s</a></td>\n",
+ $ticket_html_base,$row[1], $row[0];
+ printf " <td align=center>%s</td>\n", $row[1];
+ printf " <td align=left><a href=\"mailto:%s\">%s</a></td>\n", $row[2], $row[2];
+ printf " <td align=left>%s</td>\n", $row[3];
+ print "</tr>\n";
+ } else {
+ if ($ticket_browser eq 'Lynx') {
+ printf "<li> <a href=\"%s/admin/edit?user=%s\">%s</a> (%s, %s) [%s%s]",
+ $ticket_html_base, $row[1], $row[0], $row[1], $row[2], $row[3], (", disabled","")[$row[4]];
+ } else {
+ printf "<li> <a href=\"%s/admin/edit?user=%s\">%s</a> (%s, <a href=\"mailto:%s\">%s</a>) [%s%s]",
+ $ticket_html_base, $row[1], $row[0], $row[1], $row[2], $row[2], $row[3], (", disabled","")[$row[4]];
+ }
+ print "\n";
+ }
+ }
+ if ($ticket_browser_table) {
+ print "</table>\n\n";
+ } else {
+ print "</ol>\n\n";
+ }
+ }
+ }
+ printf "<p><center><input type=submit name=\"button/admin/newuser\" value=\"%s\"></center>\n", &gettext("New user");
+ $query = "SELECT name,description FROM $ticket_table_groups ORDER by name";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ print "\n<center><h1>" . &gettext("List of groups") . "</h1></center>\n\n";
+ if ($ticket_browser_table) {
+ print "<table width=100% border=1 cellpadding=2 cellspacing=0>\n";
+ print "<tr bgcolor=\"#c0c0c0\">\n";
+ print " <th width=25%>Name</th>\n";
+ print " <th>Description</th>\n";
+ print "</tr>\n";
+ } else {
+ print "<ol>\n";
+ }
+ while (@row = $sth->fetchrow_array) {
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ printf " <td align=left><a href=\"%s/admin/edit?group=%s\">%s</a></td>\n",
+ $ticket_html_base,$row[0], $row[0];
+ printf " <td align=left>%s</td>\n", $row[1];
+ print "</tr>\n";
+ } else {
+ printf "<li> <a href=\"%s/admin/edit?group=%s\">%s</a> (%s)\n",
+ $ticket_html_base, $row[0], $row[0], $row[1];
+ }
+ }
+ if ($ticket_browser_table) {
+ print "</table>\n\n";
+ } else {
+ print "</ol>\n\n";
+ }
+ }
+ }
+ printf "<p><center><input type=submit name=\"button/admin/newgroup\" value=\"%s\"></center>\n", &gettext("New group");
+ $query = "SELECT name,description FROM $ticket_table_classes ORDER by name";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 ) {
+ print "\n<center><h1>" . &gettext("List of classes") . "</h1></center>\n\n";
+ if ($ticket_browser_table) {
+ print "<table width=100% border=1 cellpadding=2 cellspacing=0>\n";
+ print "<tr bgcolor=\"#c0c0c0\">\n";
+ print " <th width=25%>Name</th>\n";
+ print " <th>Description</th>\n";
+ print "</tr>\n";
+ } else {
+ print "<ol>\n";
+ }
+ while (@row = $sth->fetchrow_array) {
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ printf " <td align=left><a href=\"%s/admin/edit?class=%s\">%s</a></td>\n",
+ $ticket_html_base,$row[0], $row[0];
+ printf " <td align=left>%s</td>\n", $row[1];
+ print "</tr>\n";
+ } else {
+ printf "<li> <a href=\"%s/admin/edit?class=%s\">%s</a> (%s)\n",
+ $ticket_html_base, $row[0], $row[0], $row[1];
+ }
+ }
+ if ($ticket_browser_table) {
+ print "</table>\n\n";
+ } else {
+ print "</ol>\n\n";
+ }
+ }
+ }
+ printf "<p><center><input type=submit name=\"button/admin/newclass\" value=\"%s\"></center>\n", &gettext("New class");
+ print "</form>\n";
+ if ($is_admin) {
+ &print_selection("admin");
+ } else {
+ &print_selection("");
+ }
+ } elsif ($ENV{'PATH_INFO'} eq "edit") {
+ if ($cgi->param('group')) {
+ $query = sprintf ("SELECT * FROM $ticket_table_groups WHERE name = '%s'", $cgi->param('group'));
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ print "<h1 align=center>" . sprintf(&gettext("Update group %s (%s)"),
+ $cgi->param('group'), $row[1]). "</h1>\n\n";
+ printf "<form action=\"%s/admin/update\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<input name=group type=hidden size=1 value=\"%s\">\n", $cgi->param('group');
+ printf "<b>Name </b>: <input name=description size=55 maxlength=80 value=\"%s\">\n", $row[1];
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Update");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ printf "<b><a href=\"%s/admin/delete?group=%s\">%s</a></b></center>\n",
+ $ticket_html_base,$row[0], &gettext("Delete");
+ print "</form>\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Unknown group: %s"), $cgi->param('group')) . "</h3>\n\n";
+ &print_selection();
+ }
+ }
+ } elsif ($cgi->param('class')) {
+ $query = sprintf ("SELECT * FROM $ticket_table_classes WHERE name = '%s'", $cgi->param('class'));
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ print "<h1 align=center>" . sprintf(&gettext("Update class %s (%s)"),
+ $cgi->param('class'), $row[1]). "</h1>\n\n";
+ printf "<form action=\"%s/admin/update\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<input name=class type=hidden size=1 value=\"%s\">\n", $cgi->param('class');
+ printf "<b>Name </b>: <input name=description size=55 maxlength=80 value=\"%s\">\n", $row[1];
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Update");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ printf "<b><a href=\"%s/admin/delete?class=%s\">%s</a></b></center>\n",
+ $ticket_html_base,$row[0], &gettext("Delete");
+ print "</form>\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Unknown class: %s"), $cgi->param('class')) . "</h3>\n\n";
+ &print_selection();
+ }
+ }
+ } else {
+ $query = sprintf ("SELECT * FROM $ticket_table_user WHERE username = '%s'", $user);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ print "<h1 align=center>" . sprintf(&gettext("Update user %s (%s)"), $user, $row[1]). "</h1>\n\n";
+ printf "<form action=\"%s/admin/update\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<input name=user type=hidden size=1 value=\"%s\">\n", $user;
+ printf "<b>Name </b>: <input name=realname size=55 maxlength=50 value=\"%s\">\n", $row[1];
+ printf "<b>Email </b>: <input name=email size=55 maxlength=80 value=\"%s\">\n", $row[2];
+ print "<b>Sprache </b>: ";
+ &select_languages("language",$row[3]);
+ if ($is_admin) {
+ printf "\n<b>Gruppe </b>: ";
+ &select_ticketgroups("group",":".$row[4].":", "nopublic nomultiple");
+ printf "\n<b>Gruppen </b>: ";
+ &select_ticketgroups("groups",$row[5], "nopublic");
+ print "\n" if ($ticket_browser ne 'Lynx');
+ printf "<b>Assoziiert</b>: ";
+ &select_ticketgroups("assoc",$row[6], "nopublic");
+ print "\n" if ($ticket_browser ne 'Lynx');
+ printf "<b>Programme </b>: ";
+ &select_programs ("programs",$row[7]);
+ print "\n" if ($ticket_browser ne 'Lynx');
+ print "<b>Misc. </b>: ";
+ printf "<input type=checkbox name=misc value=\"tablegroups\"%s>%s ",
+ $row[9] =~ /:tablegroups:/?" checked": "", gettext("Groups as table");
+ printf "<input type=checkbox name=misc value=\"tablelist\"%s>%s\n",
+ $row[9] =~ /:tablelist:/?" checked": "", gettext("List as table");
+ printf "<b>Active </b>: ";
+ &print_box ("active", "radio", $row[8], "ja ", "1");
+ &print_box ("active", "radio", $row[8], "nein<br>\n", "0");
+ printf "<b>Admin </b>: ";
+ &print_box ("admin", "radio", $row[9], "ja ", "j");
+ &print_box ("admin", "radio", $row[9], "nein<br>\n", "n");
+ } else {
+ print "\n<b>Misc. </b>: ";
+ printf "<input type=checkbox name=misc value=\"tablegroups\">%s ",
+ gettext("Groups as table");
+ printf "<input type=checkbox name=misc value=\"tablelist\">%s\n",
+ gettext("List as table");
+ }
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Update");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ printf "<b><a href=\"%s/admin/delete?user=%s\">%s</a></b></center>\n",
+ $ticket_html_base,$row[0], &gettext("Delete")
+ if ($is_admin);
+ print "</form>\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Unknown user: %s"), $cgi->param('user')) . "</h3>\n\n";
+ &print_selection();
+ }
+ }
+ }
+ } elsif ($ENV{'PATH_INFO'} eq "update") {
+ if ($cgi->param('user')) {
+ if ($is_admin) {
+ @row = $cgi->param('groups');
+ @assoc = $cgi->param('assoc');
+ @progs = $cgi->param('programs');
+ } else {
+ # Just a dummy...
+ @row = ('dummy');
+ }
+ @misc = $cgi->param('misc');
+ if ($#row == -1 || !length($cgi->param('realname')) || !length($cgi->param('email'))) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Realname")
+ . "</h3>\n\n" if (!length($cgi->param('realname')));
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Email")
+ . "</h3>\n\n" if (!length($cgi->param('email')));
+ print "<h3>" . sprintf (&gettext("Primary group %s not in grouplist!"),
+ $cgi->param('group')) . "</h3>\n\n"
+ if ($grouplist !~ /:$group:/);
+ } else {
+ if ($is_admin) {
+ $grouplist = ":" . join(":",@row) . ":";
+ $group = $cgi->param('group');
+ if ($grouplist !~ /:$group:/) {
+ print "<h3>" . sprintf (&gettext("Primary group %s not in grouplist!"),
+ $cgi->param('group')) . "</h3>\n\n";
+ } else {
+ $query = sprintf ("UPDATE $ticket_table_user SET "
+ . "realname=%s,email=%s,language='%s',maingroup='%s',groups='%s',"
+ . "assoc='%s',programs='%s',misc='%s',active=%d,admin='%s' "
+ . "WHERE username = '%s'",
+ $dbh->quote($cgi->param('realname')), $dbh->quote($cgi->param('email')),
+ $cgi->param('language'), $group,
+ $grouplist, ":" . join(":",@assoc) . ":",
+ ":" . join(":",@progs) . ":",
+ ":" . join(":",@misc) . ":", $cgi->param('active'),
+ $cgi->param('admin'), $cgi->param('user'));
+ }
+ } else {
+ $query = sprintf ("UPDATE $ticket_table_user SET realname=%s,email=%s,language='%s',misc='%s'"
+ . "WHERE username = '%s'",
+ $dbh->quote($cgi->param('realname')), $dbh->quote($cgi->param('email')),
+ $cgi->param('language'), ":" . join(":",@misc) . ":", $cgi->param('user'));
+ }
+ $sth = $dbh->do($query);
+
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("User %s (%s) updated."), $cgi->param('user'),
+ $cgi->param('realname')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Update of %s (%s) failed!"), $cgi->param('user'),
+ $cgi->param('realname')) . "</h3>\n\n";
+ }
+ }
+ } elsif ($cgi->param('group')) {
+ if (!length($cgi->param('description'))) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Description") . "</h3>\n\n";
+ } else {
+ $query = sprintf ("UPDATE $ticket_table_groups SET description=%s WHERE name = '%s'",
+ $dbh->quote($cgi->param('description')), $cgi->param('group'));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Group %s (%s) updated."), $cgi->param('group'),
+ $cgi->param('description')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Update of %s (%s) failed!"), $cgi->param('group'),
+ $cgi->param('description')) . "</h3>\n\n";
+ }
+ }
+ } elsif ($cgi->param('class')) {
+ if (!length($cgi->param('description'))) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Description") . "</h3>\n\n";
+ } else {
+ $query = sprintf ("UPDATE $ticket_table_classes SET description=%s WHERE name = '%s'",
+ $dbh->quote($cgi->param('description')), $cgi->param('class'));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Class %s (%s) updated."), $cgi->param('class'),
+ $cgi->param('description')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Update of %s (%s) failed!"), $cgi->param('class'),
+ $cgi->param('description')) . "</h3>\n\n";
+ }
+ }
+ } else {
+ print "<h3>" . &gettext("No user, group or class given!") . "</h3>\n\n";
+ }
+ &print_selection();
+ } elsif ($ENV{'PATH_INFO'} eq "insert") {
+ if ($cgi->param('user')) {
+ @row = $cgi->param('groups');
+ @assoc = $cgi->param('assoc');
+ @progs = $cgi->param('programs');
+ if ($#row == -1 || !$cgi->param('user') || !$cgi->param('realname') || !$cgi->param('email')) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "User")
+ . "</h3>\n\n" if (!length($cgi->param('user')));
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Realname")
+ . "</h3>\n\n" if (!length($cgi->param('realname')));
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Email")
+ . "</h3>\n\n" if (!length($cgi->param('email')));
+ } else {
+ $query = sprintf ("INSERT INTO $ticket_table_user VALUES (%s,%s,%s,'%s','%s','%s','%s','%s',%d,'%s','%s')",
+ $dbh->quote($cgi->param('user')),$dbh->quote($cgi->param('realname')),
+ $dbh->quote($cgi->param('email')), $cgi->param('language'),
+ $cgi->param('group'),":" . join(":",@row) . ":",":" . join(":",@assoc) . ":",
+ ":" . join(":",@progs) . ":", $cgi->param('active'), $cgi->param('admin'),
+ ":" . join(":",@misc) . ":");
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("User %s (%s) added."), $cgi->param('user'),
+ $cgi->param('realname')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Insert of %s (%s) failed!"), $cgi->param('user'),
+ $cgi->param('realname')) . "</h3>\n\n";
+ }
+ }
+ } elsif ($cgi->param('group')) {
+ if (!$cgi->param('description')) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Description") . "</h3>\n\n";
+ } else {
+ $tmp = $cgi->param('group');
+ $tmp =~ s/[\'\\ ]//g;
+ $query = sprintf ("INSERT INTO $ticket_table_groups VALUES ('%s',%s)",
+ $cgi->param('group'),$dbh->quote($cgi->param('description')));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Group %s (%s) added."), $cgi->param('group'),
+ $cgi->param('description')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Insert of %s (%s) failed!"), $cgi->param('group'),
+ $cgi->param('description')) . "</h3>\n\n";
+ }
+ }
+ } elsif ($cgi->param('class')) {
+ if (!$cgi->param('description')) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Description") . "</h3>\n\n";
+ } else {
+ $tmp = $cgi->param('class');
+ $tmp =~ s/[\'\\ ]//g;
+ $query = sprintf ("INSERT INTO $ticket_table_classes VALUES ('%s',%s)",
+ $cgi->param('class'),$dbh->quote($cgi->param('description')));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Class %s (%s) added."), $cgi->param('class'),
+ $cgi->param('description')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Insert of %s (%s) failed!"), $cgi->param('class'),
+ $cgi->param('description')) . "</h3>\n\n";
+ }
+ }
+ } else {
+ print "<h3>" . &gettext("No user, group or class given!") . "</h3>\n\n";
+ }
+ &print_selection();
+ } elsif ($ENV{'PATH_INFO'} eq "newuser") {
+ print "<h1 align=center>" . &gettext("Insert new user") . "</h1>\n\n";
+ printf "<form action=\"%s/admin/insert\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<b>User </b>: <input name=user size=55 maxlength=15>\n";
+ printf "<b>Name </b>: <input name=realname size=55 maxlength=50>\n";
+ printf "<b>Email </b>: <input name=email size=55 maxlength=80>\n";
+ printf "<b>Sprache </b>: ";
+ &select_languages("language", "ger");
+ printf "\n<b>Gruppe </b>: ";
+ &select_ticketgroups("group","nopublic nomultiple");
+ printf "\n<b>Gruppen </b>: ";
+ &select_ticketgroups("groups","nopublic");
+ print "\n" if ($ticket_browser eq 'Lynx');
+ printf "<b>Assoziiert</b>: ";
+ &select_ticketgroups("assoc","nopublic");
+ print "\n" if ($ticket_browser eq 'Lynx');
+ printf "<b>Programme </b>: ";
+ &select_programs ("programs", "-");
+ print "\n" if ($ticket_browser eq 'Lynx');
+ print "<b>Misc. </b>: ";
+ printf "<input type=checkbox name=misc value=\"tablegroups\">%s ",
+ gettext("Groups as table");
+ printf "<input type=checkbox name=misc value=\"tablelist\">%s\n",
+ gettext("List as table");
+ printf "<b>Active </b>: ";
+ &print_box ("active", "radio", "1", "ja ", "1");
+ &print_box ("active", "radio", "1", "nein<br>\n", "0");
+ printf "<b>Admin </b>: ";
+ &print_box ("admin", "radio", "n", "ja ", "j");
+ &print_box ("admin", "radio", "n", "nein<br>\n", "n");
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Insert");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n";
+ } elsif ($ENV{'PATH_INFO'} eq "newgroup") {
+ print "<h1 align=center>" . &gettext("Insert new group") . "</h1>\n\n";
+ printf "<form action=\"%s/admin/insert\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<b>Name </b>: <input name=group size=55 maxlength=15>\n";
+ printf "<b>Text </b>: <input name=description size=55 maxlength=80>\n";
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Insert");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n";
+ } elsif ($ENV{'PATH_INFO'} eq "newclass") {
+ print "<h1 align=center>" . &gettext("Insert new class") . "</h1>\n\n";
+ printf "<form action=\"%s/admin/insert\" method=post>\n", $ticket_html_base;
+ print "<pre>\n";
+ printf "<b>Name </b>: <input name=class size=55 maxlength=20>\n";
+ printf "<b>Text </b>: <input name=description size=55 maxlength=80>\n";
+ print "</pre>\n";
+ printf "<center><input type=submit value=\"%s\">", &gettext("Insert");
+ print " . " if ($ticket_browser eq 'Lynx');
+ printf "<input type=reset value=\"%s\"></center>\n", &gettext("Reset");
+ print "</form>\n";
+ } elsif ($ENV{'PATH_INFO'} eq "delete") {
+ if ($cgi->param('user')) {
+ $query = sprintf ("DELETE FROM $ticket_table_user WHERE username = %s", $dbh->quote($cgi->param('user')));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("User %s removed."), $cgi->param('user')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Removal of user %s failed!"), $cgi->param('user')) . "</h3>\n\n";
+ }
+ } elsif ($cgi->param('group')) {
+ if (!group_in_use( $cgi->param('group') )) {
+ $query = sprintf ("DELETE FROM $ticket_table_groups WHERE name = '%s'", $cgi->param('group'));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Group %s removed."), $cgi->param('group')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Removal of group %s failed!"), $cgi->param('group')) . "</h3>\n\n";
+ }
+ } else {
+ print "<h3>" . sprintf (&gettext("A user is still assigned to group %s!"), $cgi->param('group')) . "</h3>\n\n";
+ }
+ } elsif ($cgi->param('class')) {
+ $query = sprintf ("DELETE FROM $ticket_table_classes WHERE name = '%s'", $cgi->param('class'));
+ $sth = $dbh->do($query);
+ if ($sth) {
+ print "<h3>" . sprintf (&gettext("Class %s removed."), $cgi->param('class')) . "</h3>\n\n";
+ } else {
+ print "<h3>" . sprintf (&gettext("Removal of class %s failed!"), $cgi->param('class')) . "</h3>\n\n";
+ }
+ } else {
+ print "<h3>" . &gettext("No user, group or class given!") . "</h3>\n\n";
+ }
+ &print_selection();
+ }
+}
+
+sub relshow
+{
+ my $table = $_[0];
+ my $cmd;
+
+ return if (!$_[0]);
+
+ if ($ticket_dbhost) {
+ $cmd = "relshow -h $ticket_dbhost $ticket_dbase $table|";
+ } else {
+ $cmd = "relshow $ticket_dbase $table|";
+ }
+ if (open (IN, "$cmd")) {
+ while (<IN>) {
+ next until (/^ [+|]/);
+ print;
+ }
+ close (IN);
+ }
+}
+
+sub queries
+{
+ my $is_admin = undef;
+
+ $is_admin = 1 if (defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}));
+
+ print "<h1 align=center>" . &gettext("Special Queries") . "</h1>\n\n";
+
+ if ($ticket_browser_table) {
+ print "<table width=100% border=1 cellpadding=3 cellspacing=1>\n";
+ print "<tr>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/priority\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Open tickets by priority") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<select name=\"priority\">\n";
+ print "<option value=0>high\n";
+ print "<option value=1>medium\n";
+ print "<option value=2>low\n";
+ print "<option value=3>remind\n";
+ print "</select><br>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/user\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Open tickets by staff") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ &select_ticketuser("user");
+ print "<br>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/class\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Open tickets by classes") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ &print_select_class();
+ print "<br>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print "</tr>\n";
+ }
+
+ # Zweite Tabelle
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/group\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Tickets by groups") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<b>Group: </b> ";
+ &select_ticketgroups("group","nomultiple");
+ print "\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/listone\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Ticket by number") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<b>Nummer: </b> <input name=nr size=20 maxsize=5>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print &gettext("Shows ticket with that number") . "\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/subject\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Tickets by substring") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<b>Subject: </b> <input name=keyword type=body size=20 maxsize=80>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print &gettext("Subject contains keyword") . "\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print "</tr>\n";
+ }
+
+ # Dritte Tabelle
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ print " <td width=33%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/search\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Tickets by substring") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<b>Substring: </b> <input name=keyword type=text size=20 maxsize=80>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print &gettext("Textareas contain keyword, closed tickets as well") . "\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print " <td colspan=2 width=66%>\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<center>\n";
+ printf "\n<form action=\"%s/list/insertrange\" method=post>\n", $ticket_html_base;
+ print "<b>" . &gettext("Tickets by period") . "</b><br>\n";
+ print "<input type=hidden name=admin value=\"y\">\n" if ($is_admin);
+ print "<b>Von: </b> <input name=insertdfrom type=text size=10 maxsize=15>\n";
+ print "<b>Bis: </b> <input name=insertdto type=text size=10 maxsize=15><br>\n";
+ print "<input type=submit value=\"" . &gettext("Show") . "\">\n";
+ print "</form>\n";
+ print "</center>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print "</tr>\n";
+ } else {
+ print "<hr>\n";
+ }
+
+ # Vierte Tabele
+ if ($ticket_browser_table) {
+ print "<tr>\n";
+ print " <td colspan=3 width=100%>\n";
+ }
+ printf "\n<form action=\"%s/list/seireuq\" method=post>\n", $ticket_html_base;
+ print "<center><b>" . &gettext("Combined Query") . "</b></center><br>\n";
+
+ print "<center><table width=75% border=0 cellpadding=0 cellspacing=0>\n";
+ print "<tr>\n";
+ print " <td><center><input type=radio name=open value=y checked> open</center></td>\n";
+ print " <td><center><input type=radio name=open value=n> closed</center></td>\n";
+ print "</tr>\n";
+
+ print "<tr>\n";
+ $ticketuser{''}='';
+ print " <td>Staff<br>\n"; &select_ticketuser("user"); print " <br></td>\n";
+ print " <td>Priority<br>\n";
+ print "<select name=\"priority\">\n";
+ print "<option value=>\n";
+ print "<option value=0>high\n";
+ print "<option value=1>medium\n";
+ print "<option value=2>low\n";
+ print "<option value=3>remind\n";
+ print "</select><br>\n";
+ print " </td>\n";
+ print "</tr>\n";
+
+ print "<tr>\n";
+ print " <td>Group<br>\n"; &select_ticketgroups("group","nomultiple addempty"); print " <br></td>\n";
+ print " <td>Subject<br>\n<input name=subject type=text size=20 maxsize=80></td>\n";
+ print "</tr>\n";
+
+ print "<tr>\n";
+ print " <td>Class<br>\n"; &print_select_class(''); print " <br></td>\n";
+ print " <td>Substring<br>\n<input name=substr type=text size=20 maxsize=80></td>\n";
+ print "</tr>\n";
+
+ print "<tr><td colspan=2><center><input type=submit value=\"" . &gettext("Show") . "\"></center></td></tr>\n";
+
+ print "</table></center>\n";
+ print "</form>\n";
+ if ($ticket_browser_table) {
+ print " </td>\n";
+ print "</tr>\n";
+ print "</table>\n<p>\n<p>\n";
+ } else {
+ print "<hr>\n";
+ }
+
+
+ print &gettext("The lookups are case insensitive.") . "\n";
+ &print_selection();
+}
+
+# FIXME
+sub help
+{
+ my $host;
+
+ if ($ticket_html_base !~ /^http:/) {
+ $host = $ENV{'SERVER_URL'};
+ chop ($host) if ($host =~ /\/$/ && $ticket_html_base =~ /^\//);
+ $host .= $ticket_html_base;
+ } else {
+ $host = $ticket_html_base;
+ }
+
+ printf "\n<h1 align=center>Hilfe zum <a href=\"%s\">Ticket-System</a></h1>\n\n", $ticket_html_base;
+
+ if (open (F, "$ticket_file_help")) {
+ while (<F>) {
+ s/__BASE__/$host/g;
+ print;
+ }
+ close (F);
+ }
+ &print_selection();
+}
+
+sub info
+{
+ printf "\n<h1 align=center>Informationen zum <a href=\"%s\">Ticket-System</a></h1>\n\n", $ticket_html_base;
+
+ if (open (F, "$ticket_file_info")) {
+ print while (<F>);
+ close (F);
+ }
+
+ if ($is_admin) {
+ print "Die Tabellen laufen zur Zeit auf $ticket_dbhost in der Datenbank $ticket_dbase.<p>\n\n";
+
+ if ($ticket_browser_table) {
+ print "<table width=100% border=0 cellpadding=3>\n";
+ print "<tr>\n";
+ print "<td width=50% bgcolor=\"#99ccff\">\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<h3 align=center>Ticket-Tabelle</h3>\n";
+ print "<font size=\"-1\"><pre>\n";
+ &relshow ($ticket_table);
+ print "</pre></font>\n";
+ if ($ticket_browser_table) {
+ print "</td>\n";
+ print "<td width=50% bgcolor=\"#99ccff\">\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<h3 align=center>User-Tabelle</h3>\n";
+ print "<font size=\"-1\"><pre>\n";
+ &relshow ($ticket_table_user);
+ print "</pre></font>\n";
+ if ($ticket_browser_table) {
+ print "</td>\n";
+ print "</tr>\n";
+ print "<tr>\n";
+ print "<td width=50% bgcolor=\"#99ccff\">\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<h3 align=center>Gruppen-Tabelle</h3>\n";
+ print "<font size=\"-1\"><pre>\n";
+ &relshow ($ticket_table_groups);
+ print "</pre></font>\n";
+ if ($ticket_browser_table) {
+ print "</td>\n";
+ print "<td width=50% bgcolor=\"#99ccff\">\n";
+ } else {
+ print "<hr>\n";
+ }
+ print "<h3 align=center>Klassen-Tabelle</h3>\n";
+ print "<font size=\"-1\"><pre>\n";
+ &relshow ($ticket_table_classes);
+ print "</pre></font>\n";
+ if ($ticket_browser_table) {
+ print "</td>\n";
+ print "</tr>\n";
+ print "</table>\n";
+ } else {
+ print "<hr>\n";
+ }
+ }
+
+ &print_selection();
+}
+
+sub print_html_groups()
+{
+ my $groups = &user_groups($ENV{'REMOTE_USER'});
+ my $g;
+
+ $groups =~ s/^:(.*):$/$1/;
+ $groups =~ s/:/ /g;
+ if (length($groups) > 65) {
+ $groups = substr($groups, 0, 62) . "...";
+ }
+
+ print "<table width=100% bgcolor=\"#ffff00\" border=2>\n"
+ . " <tr>\n <th>\n <center>\n";
+ printf " User: <a href=\"%s/list/user?user=%s\">%s</a>",
+ $ticket_html_base, $ENV{'REMOTE_USER'}, $ENV{'REMOTE_USER'};
+ printf " (%s)", &user_realname($ENV{'REMOTE_USER'}) if (length($groups) <= 48);
+# printf " - Groups: %s\n", $groups;
+ printf " - Groups:";
+ foreach $g (split(/ /,$groups)) {
+ if ($g eq "...") {
+ printf "%s", $g;
+ } else {
+ printf " <a href=\"%s/list/group?group=%s\">%s</a>", $ticket_html_base, $g, $g;
+ }
+ }
+ print "\n </th>\n </center>\n </tr>\n</table>\n";
+ print "<hr>\n" if ($ENV{'HTTP_USER_AGENT'} !~ /Mozilla/);
+ print "\n";
+
+}
+
+# -----------------------------------------------------------------------
+
+$cgi = new CGI;
+print $cgi->header();
+print $ticket_html_header;
+
+if (!length($ENV{'REMOTE_USER'})) {
+ print "<h1>No user specified!</h1>\n\n";
+ print $ticket_html_footer;
+ exit (0);
+}
+
+$dbh = &ticket_init($ENV{'REMOTE_USER'});
+
+if (!$dbh) {
+ print "<h1>Cannot connect to database!</h1>";
+ print $ticket_html_footer;
+ exit (0);
+}
+
+print_html_groups();
+
+$is_admin = &is_admin($ENV{'REMOTE_USER'});
+
+if (length ($ENV{'PATH_INFO'})) {
+ $ENV{'PATH_INFO'} =~ s,^/,,;
+
+ if ($ENV{'PATH_INFO'} eq "listone") {
+ &listone();
+ } elsif ($ENV{'PATH_INFO'} =~ /^list/) {
+ $ENV{'PATH_INFO'} =~ s,^list,,;
+ $ENV{'PATH_INFO'} =~ s,^/,,;
+ &list();
+ } elsif ($ENV{'PATH_INFO'} =~ /^admin/) {
+ $ENV{'PATH_INFO'} =~ s,^admin,,;
+ $ENV{'PATH_INFO'} =~ s,^/,,;
+ &admin();
+ } elsif ($ENV{'PATH_INFO'} eq "selection") {
+ &process_selection();
+ } elsif ($ENV{'PATH_INFO'} eq "action") {
+ &process_actions();
+ } elsif ($ENV{'PATH_INFO'} eq "edit") {
+ &edit();
+ } elsif ($ENV{'PATH_INFO'} eq "update") {
+ &update();
+ } elsif ($ENV{'PATH_INFO'} eq "delete") {
+ &delete();
+ } elsif ($ENV{'PATH_INFO'} eq "close") {
+ &close();
+ } elsif ($ENV{'PATH_INFO'} eq "queries") {
+ &queries();
+ } elsif ($ENV{'PATH_INFO'} eq "insert" || $ENV{'PATH_INFO'} eq "insertdata") {
+ &insert();
+ } elsif ($ENV{'PATH_INFO'} =~ /^help/) {
+ &help();
+ } elsif ($ENV{'PATH_INFO'} =~ /^info/) {
+ &info();
+ } else {
+ &print_first_banner();
+ }
+} else {
+ &print_first_banner();
+}
+
+print $ticket_html_footer;
+$dbh->disconnect;
--- /dev/null
+# ticket-db.pl - Bibliothek fuer das Infodrom Trouble Ticket System
+# Copyright (c) 1997,8 Martin Schulze <joey@infodrom.north.de>
+
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
+
+$ticket_version = "0.4.5.10";
+
+# is_visible (groups,insertp)
+# Decides wether a ticket is visibe
+#
+# ticket_is_visible (nr)
+# Decides if the referenced ticket is visible, based on is_visible()
+#
+# has_access (nr, user)
+# Decides wether the referenced ticket is visible by that user.
+#
+# eval_tickets (dbh, where)
+# Queries the database and builds a binary tree, needed for viewing.
+# Modifies %ticket_tree and @tickets.
+#
+# is_second (nr)
+# Evaluates if the referred tickets is a child of some other. Needs
+# %ticket_tree which is generated by eval_tickets()
+#
+# print_tickets (text, open)
+# Prints the whole received tickets, needs %ticket_tree
+#
+# print_table_ticket (nr, bold/$anchor, time)
+# Prints one ticket - and its children
+#
+# print_children (parent, text, open)
+# Prints the list of children
+#
+# has_open_children
+# Evaluates %ticket_tree and returns appropriate if open
+#
+# get_next_nr
+# Gibt die naechste freie Nr. in der Datenbank zurueck. Wenn der
+# INSERT nicht schnell darauf folgt, kann es passieren, dass die
+# Nr. anschliessend bereits wieder vergeben ist.
+#
+# ticket_is_valid
+# Prueft, ob das angegebene Ticket gueltig ist.
+#
+# ticket_is_closed
+# Prueft, ob das Ticket bereits geschlossen ist oder nicht.
+#
+# ticket_insert
+# Prueft, ob das Ticket gueltig ist und fuegt es dann ein. Die
+# INSERT-Mail wird automatisch verschickt. Die Nummer des neuen
+# Tickets wird zurueckgegeben
+
+require 'ticket.pl';
+
+@ticket_priorities = ("high","medium","low","remind");
+
+# [0] Gruppen des Tickets
+# [1] insertp
+# Needs @ticket_mygroups
+sub is_visible
+{
+ my $groups = $_[0];
+ my $insertp = $_[1];
+ my $mygroup;
+ my $mygroups;
+
+ return 1 if ($ENV{'LOGNAME'} eq $ticket_superuser);
+ return 1 if ($groups =~ /:Public:/);
+ return 1 if ($insertp eq &user_email($ENV{'REMOTE_USER'}));
+
+ if ($#ticket_mygroups < 0) {
+ $mygroups = &user_groups($ENV{'REMOTE_USER'});
+ $mygroups =~ s/^:(.*):$/$1/;
+ @ticket_mygroups = split (/:/,$mygroups);
+ }
+ return 1 if (&user_groups($ENV{'REMOTE_USER'}) =~ /:All:/);
+
+ foreach $mygroup (@ticket_mygroups) {
+ return 1 if ($groups =~ /:$mygroup:/);
+ }
+
+ return 0;
+}
+
+# [0] = Nr.
+sub ticket_is_visible
+{
+ my $nr = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return 1 if ($ENV{'LOGNAME'} eq $ticket_superuser);
+
+ $query = sprintf("SELECT groups,insertp FROM $ticket_table WHERE nr = %d", $nr);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return &is_visible($row[0], $row[1]);
+ }
+ }
+ return 0;
+}
+
+# [0] Ticket #
+# [1] User
+sub has_access
+{
+ my $nr = shift;
+ my $user = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $i;
+
+ $query = "SELECT insertp,changep";
+ $i=0;while ($i < $ticket_staff) {
+ $query .= sprintf (",staff%d", $i);
+ $i++;
+ }
+ $query .= sprintf(" FROM $ticket_table WHERE nr = %d", $nr);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return 1 if (&email_user($row[0]) eq $user);
+ return 1 if (&email_user($row[1]) eq $user);
+ $i=0;while ($i < $ticket_staff) {
+ return 1 if ($row[2+$i] eq $user);
+ $i++;
+ }
+ }
+ }
+ return 0;
+}
+
+@tickets = ();
+%ticket_tree = ();
+$ticket_spacing = "";
+sub eval_tickets
+{
+ my $dbh = shift;
+ my $where = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $children;
+ my $nr;
+ my $check;
+
+ my $is_admin = undef;
+
+ $is_admin = 1 if (defined $cgi && defined $cgi->param('admin') && &is_admin($ENV{'REMOTE_USER'}));
+
+ $query = "SELECT nr,anchor,priority,staff0,groups,insertp FROM $ticket_table $where ORDER BY priority,staff0,nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 ) {
+ while (@row = $sth->fetchrow_array) {
+ if (!$is_admin) {
+ next unless (&is_visible($row[4], $row[5]));
+ }
+ push (@tickets, $row[0]);
+ if ($row[1]) {
+ $children = $ticket_tree{$row[1]};
+ $children = ":" if (!length($children)) ;
+ $children .= $row[0] . ":";
+ $ticket_tree{$row[1]} = $children;
+ $children = ":";
+ } else {
+ $ticket_tree{$row[0]} = ":" if (!$ticket_tree{$row[0]});
+ }
+ }
+
+ # Sanity check
+ foreach $check (keys(%ticket_tree)) {
+ if (! grep (/^($check)$/, @tickets)) {
+ $children = $ticket_tree{$check};
+ $children =~ s/^:(.*):$/$1/;
+ if (length($children)) {
+ @row = split(/:/,$children);
+ foreach $nr (@row) {
+ $ticket_tree{$nr} = ":" if (!exists ($ticket_tree{$nr}));
+ }
+ delete $ticket_tree{$check};
+ }
+ }
+ }
+ }
+ }
+}
+
+# [0] Nr
+# Evaluates if the referred tickets is a child of some other. Needs
+# %ticket_tree which is generated by eval_tickets()
+#
+sub is_second
+{
+ return 0 if ($#_ < 0);
+ foreach $key (keys(%ticket_tree)) {
+ return 1 if ($ticket_tree{$key} =~ /:$_[0]:/);
+ }
+ return 0;
+}
+
+# [0] Nr
+# Evaluates if the referred tickets has one or more children.
+# Needs %ticket_tree which is generated by eval_tickets()
+#
+sub has_children
+{
+ return 0 if ($#_ < 0);
+ foreach $key (keys(%ticket_tree)) {
+ return 1 if ($ticket_tree{$key} =~ /^$_[0]:/);
+ }
+ return 0;
+}
+
+sub print_tickets
+{
+ my $text = shift;
+ my $open = shift;
+ my $urladd = shift;
+ my $count = 0;
+ my $do_table = ($ticket_browser_table && $ticket_intern{'misc'} =~ /tablelist/);
+
+ $ticket_spacing = "";
+ if (!$text) {
+ if ($do_table) {
+ print "<table border=1 cellspacing=0 cellpadding=2>\n";
+ print "<tr bgcolor=\"#c0c0c0\">\n";
+ print " <th width=30>Nr.</th>\n";
+ print " <th width=30>Pri</th>\n";
+ print " <th width=100>Staff</th>\n";
+ print " <th width=70%>Subject</th>\n";
+ if ($open) {
+ print " <th>Time</th>\n";
+ } else {
+ print " <th>Modified</th>\n";
+ }
+ print "</tr>\n";
+ } else {
+ print "<ul>\n";
+ }
+ }
+
+ foreach $nr (@tickets) {
+ if (! &is_second ($nr)) {
+ do print_table_ticket ($nr, "bold", $text, $open, $urladd, $do_table);
+ $count++;
+ $count += &print_children($nr, $text, $open, $urladd, $do_table);
+ }
+ }
+ if (!$text) {
+ if ($do_table) {
+ print "<tr>\n";
+ printf " <th colspan=5 align=left>%d Tickets</th>\n", $count;
+ print "</tr>\n";
+ print "</table>\n";
+ } else {
+ print "</ul>\n";
+ printf "<b>%d Tickets</b><p>\n\n", $count;
+ }
+ } else {
+ printf "\n%d Tickets\n\n", $count;
+ }
+}
+
+# [0] Nr.
+# [1] bold/$anchor
+# [2] 1/0 (=time ja/nein)
+# [3] Appendix to URL like "&admin=y"
+#
+sub print_table_ticket
+{
+ my $nr = shift;
+ my $bold = shift;
+ my $text = shift;
+ my $open = shift;
+ my $urladd = shift;
+ my $do_table = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $td;
+ my $subject;
+ my $state;
+
+ if ($bold eq "bold") {
+ $td = "th";
+ $anchor = 0;
+ } else {
+ $anchor = $bold;
+ $td = "td";
+ }
+
+ $query = "SELECT priority,staff0,subject";
+ if ($typ == 1) {
+ $query .= ",closetime";
+ } else {
+ $query .= ",changed";
+ $query .= ",closing" if ($open == 2);
+ }
+ $query .= " FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ if ($text) {
+ if ($open == 1) {
+ printf "%s#%d %s [%s, %s, %d min]\n",
+ $ticket_spacing, $nr, $row[2], $ticket_priorities[$row[0]], $row[1], $row[3];
+ } elsif ($open == 2) {
+ if (length($row[4])) { $state = "closed"; }
+ else { $state = "open"; }
+ printf "%s#%d %s [%s, %s, %s %s]\n",
+ $ticket_spacing, $nr, $row[2], $ticket_priorities[$row[0]],
+ $row[1], &string_to_date($row[3]), $state;
+ } else {
+ printf "%s#%d %s [%s, %s, %s]\n",
+ $ticket_spacing, $nr, $row[2], $ticket_priorities[$row[0]], $row[1], &string_to_date($row[3]);
+ }
+ } else {
+ if ($do_table) {
+ print "<tr>\n";
+ printf " <td align=right>%d</td>\n", $nr;
+ printf " <td align=center>%s</td>\n", ('HI','M','LO')[$row[0]];
+ printf " <td align=center>%s</td>\n", $row[1];
+ $subject = $row[2];
+ $subject = sprintf ("(%d) %s", $anchor, $subject) if ($anchor);
+ printf " <$td align=left><a href=\"%s/listone?nr=%d%s\">%s</a></$td>\n",
+ $ticket_html_base, $nr, $urladd, $subject;
+ if ($open == 1) {
+ printf " <td align=right>%d</td>\n", $row[3];
+ } elsif ($typ == 2) {
+ if (length($row[4])) { $state = "-"; }
+ else { $state = "+"; }
+ printf " <td align=center>%s %s</td>\n", &string_to_date($row[3]), $state;
+ } else {
+ printf " <td align=center>%s</td>\n", &string_to_date($row[3])
+ }
+# printf " <td>%s by %s</td>\n", &string_to_date($row[3]), &email_user($row[4]);
+ print "</tr>\n";
+ } else {
+ if ($open == 1) {
+ printf "<li> <a href=\"%s/listone?nr=%d%s\">%s</a> [%s, %s, %d min]\n",
+ $ticket_html_base, $nr, $urladd, $row[2], $ticket_priorities[$row[0]], $row[1], $row[3];
+ } elsif ($open == 2) {
+ if (length($row[4])) { $state = "closed"; }
+ else { $state = "open"; }
+ printf "<li> <a href=\"%s/listone?nr=%d%s\">%s</a> [%s, %s, %s, %s]\n",
+ $ticket_html_base, $nr, $urladd, $row[2], $ticket_priorities[$row[0]], $row[1],
+ &string_to_date($row[3]), $state;
+ } else {
+ printf "<li> <a href=\"%s/listone?nr=%d%s\">%s</a> [%s, %s, %s]\n",
+ $ticket_html_base, $nr, $urladd, $row[2], $ticket_priorities[$row[0]], $row[1], &string_to_date($row[3]);
+ }
+ }
+ }
+
+ }
+ } else {
+ do error_query($query);
+ }
+}
+
+sub print_children
+{
+ my $parent = shift;
+ my $text = shift;
+ my $open = shift;
+ my $urladd = shift;
+ my $do_table = shift;
+ my @list;
+ my $second;
+ my $count = 0;
+
+ $second = $ticket_tree{$parent};
+ $second =~ s/^:(.*):$/$1/;
+ if (length($second)) {
+ @list = split(/:/,$second);
+ if (@list) {
+ print "<ul>\n" if (!$text && !$do_table);
+ foreach $nr (@list) {
+ $ticket_spacing .= " ";
+ do print_table_ticket ($nr, $parent, $text, $open, $urladd, $do_table);
+ $count++;
+ $count += &print_children ($nr,$text,$open,$urladd,$do_table);
+ chop($ticket_spacing);
+ chop($ticket_spacing);
+ }
+ print "</ul>\n" if (!$text && !$do_table);
+ }
+ }
+ return $count;
+}
+
+sub has_open_children
+{
+ my $nr = shift;
+ my $open = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return 0 if (!$nr);
+
+ $query = "SELECT nr FROM $ticket_table WHERE anchor = $nr AND closing = '' ORDER BY nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0) {
+ return 1;
+ }
+ }
+ return 0;
+}
+
+########### get_class_descrip
+#
+# Gibt die Beschreibung einer Klasse zurueck
+#
+sub get_class_descrip
+{
+ my $class = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return if (!$class);
+
+ $query = "SELECT description FROM $ticket_table_classes WHERE name = '$class'";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return ($row[0]);
+ }
+ } else {
+ do error_query($query);
+ }
+}
+
+########### get_next_nr
+#
+# Gibt die naechste freie Nr. in der Datenbank zurueck. Wenn der
+# INSERT nicht schnell darauf folgt, kann es passieren, dass die
+# Nr. anschliessend bereits wieder vergeben ist.
+#
+sub get_next_nr
+{
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT nr FROM $ticket_table ORDER BY nr DESC";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return $row[0] + 1;
+ }
+ }
+ return 1;
+}
+
+########### ticket_is_valid
+#
+# Prueft, ob das angegebene Ticket gueltig ist.
+#
+# Args: "text/html", length(subject), @staff, groups, length(text)
+# groups ist mit Leerzeichen getrennt
+#
+sub ticket_is_valid
+{
+ my $type = shift;
+ my $lsubject = shift;
+ my $staff = shift;
+ my @staff = @$staff;
+ my $groups = shift;
+ my $lbody = shift;
+ my $res = 1;
+ my @groups;
+ my $group;
+ my $tickgroups;
+ my $ok;
+ my $i;
+
+ if (!$lsubject) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), "Subject") . "</h3>\n\n";
+ $res = 0;
+ } elsif ($lsubject>$ticket_length{'subject'}-1) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not contain more than %d characters!"), "Subject", $ticket_length{'subject'}) . "</h3>\n\n";
+ $res = 0;
+ }
+ if ($#staff == -1) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), &gettext("Staff")) . "</h3>\n\n";
+ $res = 0;
+ }
+ if (!$lbody) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), &gettext("Text")) . "</h3>\n\n";
+ $res = 0;
+ } elsif ($lsubject>$ticket_length{'subject'}-1) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not contain more than %d characters!"), &gettext("Text"), $ticket_length{'body'}) . "</h3>\n\n";
+ $res = 0;
+ }
+ if ($staff[0] ne $ENV{'REMOTE_USER'}) {
+ if (!length($groups)) {
+ print "<h3>" . sprintf (&gettext("The field '%s' must not be empty!"), &gettext("Groups")) . "</h3>\n\n";
+ $res = 0;
+ }
+ }
+
+ if (length($groups)) {
+ @groups = split (/ /,$groups);
+ $tickgroups = ":" . join (":", @groups) . ":";
+ }
+ if ($tickgroups !~ /:Public:/) {
+
+ # Staff0
+ $i = 0;
+ while ($i < $ticket_staff) {
+ if ($staff[$i] && ($staff[$i] ne $ENV{'REMOTE_USER'})) {
+ $groups = &user_groups($staff[$i]);
+ $groups =~ s/^:(.*):$/$1/;
+ @groups = split(/:/,$groups);
+ $ok = 0;
+ foreach $group (@groups) {
+ $ok = 1 if ($tickgroups =~ /:$group:/);
+ }
+ if (!$ok) {
+ print "<h3>" . &gettext("Not enough groups specified!") . "</h3>\n\n";
+ print sprintf (&gettext("%s would not be able to access the ticket."), &user_realname($staff[0])) . "<p>\n\n";
+ $res = 0;
+ }
+ }
+ $i++;
+ }
+ }
+ return $res;
+}
+
+########### ticket_is_closed
+#
+# Prueft, ob das angegebene Ticket bereits geschlossen ist
+#
+# Args: Nummer
+#
+sub ticket_is_closed
+{
+ my $nr = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT closing FROM $ticket_table WHERE nr = $nr";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return 1 if (length($row[0]));
+ }
+ }
+ return 0;
+}
+
+########### ticket_insert
+#
+# Prueft, ob das Ticket gueltig ist und fuegt es dann ein. Die
+# INSERT-Mail wird automatisch verschickt. Die Nummer des neuen
+# Tickets wird zurueckgegeben
+#
+# Args: (user,subject,class,priority,@staff,groups,anchor,bodypre,body,fixdate)
+# groups ist mit Leerzeichen getrennt
+#
+sub ticket_insert
+{
+ my ($nr) = 0;
+ my $user = shift;
+ my $subject = shift;
+ my $class = shift;
+ my $priority = shift;
+ my $staff = shift;
+ my @staff = @$staff;
+ my $groups = shift;
+ my $anchor = shift;
+ my $bodypre = shift;
+ my $body = shift;
+ my $fixdate = shift;
+ my $mygroups = $groups;
+ my $query, $sth, $rc;
+ my $datastring;
+ my $i;
+
+ $ENV{'REMOTE_USER'} = $user if (! scalar $ENV{'REMOTE_USER'});
+
+ $priority = 3 if ($priority < 0 || $priority > 3);
+ # return 0 if (!is_class($class));
+ # return 0 if (!ticket_exists($anhor));
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $subject =~ s/\s*$//;
+ if (&ticket_is_valid("text",length($subject), \@staff, $groups, length($body)) && $dbh) {
+ $date = &stringdate();
+ $mygroups =~ s/ /:/g;
+ $mygroups = ":" . $mygroups . ":";
+
+ $datastring = sprintf ("%s,'%s',%d,",
+ $dbh->quote(substr($subject,0,$ticket_length{'subject'}-1)),
+ $class, $priority);
+ $i=0;
+ while ($i < $ticket_staff) {
+ $datastring .= sprintf("'%s',", $staff[$i]);
+ $i++;
+ }
+ $datastring .= sprintf("'%s',%d,'%s',%s,'%s','%s','%s','','',0,'n','','%s','%s')",
+ $mygroups,$anchor, $bodypre,
+ $dbh->quote(substr(&prepare_text($body),0,
+ $ticket_length{'body'}-1)),
+ &date_to_string($fixdate),
+ $date, &user_email($ENV{'REMOTE_USER'}),
+ $date, &user_email($ENV{'REMOTE_USER'}));
+ do {
+ $nr = &get_next_nr();
+ $query = sprintf ("INSERT INTO tickets VALUES (%d,%s", $nr, $datastring);
+ $rc = $dbh->do($query);
+ $rc = -1 if (!$rc && $dbh->errstr =~ /non unique/i);
+ if (!$rc) {
+ printf STDERR "Query: %s\n", $query;
+ printf STDERR "Error: %s\n", $dbh->errstr;
+ }
+ } until ($rc);
+
+ if ($rc) {
+ open (MAIL, "|/usr/lib/sendmail -t");
+ push (@staff, $user);
+ printf MAIL "To: %s\n", join(", ", &uniq_user_email(@staff));
+ pop (@staff);
+ printf MAIL "From: %s\n", $ticket_from;
+ printf MAIL "Bcc: %s\n", $ticket_bcc if ($ticket_bcc);
+ printf MAIL "Subject: [INSERT] #%d: %s\n", $nr, &de_html($subject);
+ print MAIL "Precedence: junk\n";
+ printf MAIL "X-Mailer: ticket %s\n", $ticket_version;
+ print MAIL "\n";
+ printf MAIL &gettext("%s has opened the following ticket on %s:") . "\n\n",
+ &user_realname($ENV{'REMOTE_USER'}), &string_to_date(&stringdate());
+ printf MAIL "Ticket ....: #%d\n", $nr;
+ printf MAIL "Subject ...: %s\n", &de_html($subject);
+ printf MAIL "Klasse ....: %s\n", &get_class_descrip($class);
+ printf MAIL "Prioritaet : %s\n", $ticket_priorities[$priority];
+ printf MAIL "Mitarbeiter: %s\n", join(", ", &uniq_user_realname(@staff));
+ printf MAIL "Gruppen ...: %s\n", join(", ", split(/:/, $groups)) if (length($groups));
+ printf MAIL "Auftrag ...: #%d (%s)\n", $anchor, &get_subject($anchor)
+ if ($anchor);
+ if (exists $ENV{'SERVER_NAME'} && exists $ENV{'SERVER_PORT'}) {
+ $url = "http://" . $ENV{'SERVER_NAME'};
+ $url .= ":" . $ENV{'SERVER_PORT'} if ($ENV{'SERVER_PORT'} != 80);
+ $url .= $ticket_html_base . "/listone?nr=" . $nr;
+ printf MAIL "URL .......: %s\n", $url;
+ }
+ printf MAIL "\n%s\n\n", &de_html($body);
+ print MAIL sprintf (&gettext("%s was set as fixdate."), $fixdate) . "\n\n" if (length($fixdate));
+ printf MAIL "\n%s,\n\n\t%s\n", &gettext("Best regards"), &gettext("Your Ticket System");
+ print MAIL $ticket_signature;
+ close (MAIL);
+ } else {
+ $nr = 0;
+ }
+ } else {
+ printf "<h3>%s</h3>\n\n", &gettext("The ticket is invalid.");
+ }
+
+ return $nr
+}
+
+# Make perl happy
+#
+1;
--- /dev/null
+# ticket.pl - Bibliothek fuer das Infodrom Trouble Ticket System
+# Copyright (c) 1997,8 Martin Schulze <joey@infodrom.north.de>
+
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
+
+######################################################################
+######################################################################
+
+# noc_connect
+# Stellt die Verbindung zur Datenbank her
+#
+# ticket_init
+# Initialisiert das Ticket-System
+#
+# stringdate
+# Gibt den heutigen Tag als Zeichenkette zurueck.
+#
+# shortdate
+# Gibt den heutigen Tag als Zeichenkette zurueck.
+#
+# gettext
+# Returns a string, possibly in a different language
+#
+# error_query
+# Fehlermeldung, der fehlgeschlagene Query wird angezeigt.
+#
+# string_to_date
+# Wandelt einen Datumsstring wie er in der Datenbank gespeichert wird
+# in eine lesbare Form um.
+#
+# date_to_string
+# Wandelt einen lesbaren Datumsstring in die Form um, in der er in der
+# Datenbank gespeichert werden kann.
+#
+# index_user_hashes
+# Fuellt diverse Cache-Listen mit Userdaten
+# %ticket_list_user_realname Zuordnung User Realname
+# %ticket_list_user_email Zuordnung User Email
+# %ticket_list_email_realname Zuordnung Email Realname
+# %ticket_list_email_user Zuordnung Email User
+# %ticket_list_user_groups Zuordnung User Gruppen
+# %ticket_list_user_admin Zuordnung User Admin
+# %ticket_list_user_primary Zuordnung User Primary Group
+#
+# index_user
+# Fuellt diverse Cache-Listen mit Userdaten. Stuetzt sich auf
+# index_user_hashes ab.
+#
+# get_language
+# Gibt die zu benutzende Sprache zurueck, beinhatet ein index_user()
+#
+# user_email
+# Gibt die Email-Adresses eines Users zurueck, falls unbekannt, wird
+# ein leerer String zurueckgegeben.
+#
+# user_groups
+# Gibt die Gruppen eines Users zurueck, falls unbekannt, wird ein
+# leerer String zurueckgegeben.
+#
+# user_realname
+# Gibt den Realname einers Users zurueck, falls unbekannt, wird der
+# Username zurueckgegeben.
+#
+# user_primary
+# Gibt die primaere Gruppe einers Users zurueck, falls unbekannt, wird der
+# Username zurueckgegeben.
+#
+# email_realname
+# Gibt den Realname eines Users mit der angegebenen Email-Adresse
+# zurueck, falls unbekannt, wird die Email-Adresse zurueckgegeben.
+#
+# email_user
+# Gibt den Benutzer zur angegebenen Email-Adresse zurueck, falls unbekannt,
+# wird die Email-Adresse zurueckgegeben.
+#
+# is_admin
+# Gibt 0 oder 1 zurueck, je nachdem, ob der angegebene User als Admin
+# eingetragen ist oder nicht.
+#
+# is_user
+# Gibt 0 oder 1 zurueck, je nachdem, ob der angegebene User
+# tatsaechlich als User (in der angegebenen Gruppe) existiert.
+#
+# user_assoc
+# Gibt die assoziierten Gruppen eines Users zurueck, falls unbekannt, wird ein
+# leerer String zurueckgegeben.
+#
+# uniq_fields
+# Loescht doppelte Eintraege aus der Liste - zur Vermeidung von
+# doppelten Mails.
+#
+# uniq_user_email
+# Returns a uniqe list of email addresses regarding the given array of
+# users.
+#
+# uniq_user_realname
+# Returns a uniqe list of realnames regarding the given array of
+# users.
+#
+# groupname
+# Gibt die Beschreibung zu einer Gruppe zurueck, ggf. wird ein Query
+# abgesetzt
+#
+# get_subject
+# Gibt das Subject eines Tickets zurueck.
+#
+# prepare_text
+# Tries to do some conversation from normal text to html'ed
+#
+# de_html
+# Tries to remove some tags from texts.
+#
+# de_msql
+# Tries to remove some msql quotes.
+#
+# load_language
+# Loads strings for the given language.
+#
+# start_program
+# Gibt 1 zurueck, wenn der angegebene User das angegebene Programm
+# ausfuehren darf, ansonsten 0.
+#
+# forkme
+# Returns 0 if it's the child, $pid if its the parent and undef if error.
+#
+# sql_clike
+# Converts a string into an expression to be used by the clike expression.
+#
+# html_quote
+# Converts a string into proper HTML text. This is essentially needed
+# if text fields contain regular quotes (") which are used to delimit
+# fields as well.
+
+push (@INC, "/etc/noc") if (!grep (/\/etc\/noc/, @INC));
+push (@INC, "/usr/lib/ticket") if (!grep (/\/usr\/lib\/ticket/, @INC));
+require 'ticket-db.pl';
+require 'ticket-config.pl';
+
+my $dbh;
+
+########### noc_connect
+#
+# Stellt die Verbindung zur Datenbank her
+#
+sub noc_connect
+{
+ my $tick_engine;
+
+ if ($ticket_dbdriver eq 'Pg') {
+ $tick_engine = "dbi:$ticket_dbdriver:dbname=$ticket_dbase";
+ } elsif ($ticket_dbdriver eq 'mSQL') {
+ $tick_engine = "dbi:$ticket_dbdriver:$ticket_dbase";
+ $tick_engine .= "@$ticket_dbhost" if ($ticket_dbhost);
+ } elsif ($ticket_dbdriver eq 'mysql') {
+ $tick_engine = "dbi:$ticket_dbdriver:$ticket_dbase";
+ }
+
+ if (defined $ticket_username) {
+ if (defined $ticket_password) {
+ $dbh = DBI->connect($tick_engine, $ticket_username, $ticket_password);
+ } else {
+ $dbh = DBI->connect($tick_engine, $ticket_username);
+ }
+ } else {
+ $dbh = DBI->connect($tick_engine);
+ }
+ if (!$dbh) {
+ print "<h1>Access to database denied!</h1>\n\n";
+ return 0;
+ }
+ return $dbh
+}
+
+########### ticket_init
+#
+# Initialisiert das Ticket-System
+#
+# [0] REMOTE_USER
+#
+sub ticket_init
+{
+ my $sth;
+
+ $ENV{'REMOTE_USER'} = $ENV{'LOGNAME'} if (!scalar $ENV{'REMOTE_USER'});
+
+ $dbh = &noc_connect if (!$dbh);
+
+ if ($ticket_dbdriver eq 'Pg') {
+ $ticket_clike = "~*";
+ } elsif ($ticket_dbdriver eq 'mysql') {
+ $ticket_clike = "LIKE";
+ } elsif ($ticket_dbdriver eq 'mSQL') {
+ $ticket_clike = "CLIKE";
+ }
+
+# $sth = $dbh->query("SELECT subject,body,closing FROM $ticket_table WHERE nr = 0");
+#
+# ($ticket_length{'subject'},$ticket_length{'body'},$ticket_length{'closing'}) = $sth->length;
+#
+# Moved into ticket-config.pl temporarily since I don't know how to
+# determine the size of field with DBI
+
+ &load_language(&get_language($_[0]));
+
+ if (!&start_program("ticket", $_[0])) {
+ print "<h1>" . &gettext("Access denied.") . "</h1>\n\n";
+ return 0;
+ }
+
+ if ($ENV{'HTTP_USER_AGENT'} =~ /Lynx/i) {
+ $ticket_browser = 'Lynx';
+ $ticket_browser_table = undef;
+ } elsif ($ENV{'HTTP_USER_AGENT'} =~ /Mosaic/i) {
+ $ticket_browser = 'Mosaic';
+ $ticket_browser_table = undef;
+ } elsif ($ENV{'HTTP_USER_AGENT'} =~ /Grail/i) {
+ $ticket_browser = 'Grail';
+ $ticket_browser_table = undef;
+ } elsif ($ENV{'HTTP_USER_AGENT'} =~ /Chimera/i) {
+ $ticket_browser = 'Chimera';
+ $ticket_browser_table = undef;
+ } elsif ($ENV{'HTTP_USER_AGENT'} =~ /Mozilla|MSIE/i) {
+ $ticket_browser = 'Mozilla';
+ $ticket_browser_table = 1;
+ }
+
+ return $dbh;
+}
+
+########### stringdate
+#
+# Gibt den heutigen Tag als Zeichenkette zurueck.
+#
+sub stringdate
+{
+ ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
+ = localtime();
+ return sprintf ("%4d%02d%02d", $date_year+1900, $date_mon+1, $date_mday);
+}
+
+########### shortdate
+#
+# Gibt den heutigen Tag als Zeichenkette zurueck.
+#
+sub shortdate
+{
+ ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
+ = localtime();
+ return sprintf ("%d.%d.%d", $date_mday, $date_mon+1, $date_year);
+}
+
+########### gettext
+#
+# Returns a string, possibly in a different language
+sub gettext
+{
+ my $text = $language{$_[0]};
+ return $text if ($text);
+ return $_[0];
+}
+
+########### error_query
+#
+# Fehlermeldung, der fehlgeschlagene Query wird angezeigt.
+#
+sub error_query
+{
+ my $query = $_[0];
+
+ printf "<h3>%s</h3>\n", &gettext("An SQL-Query failed!");
+ printf "<h3>%s</h3>\n\n", &gettext("Please press 'Reload' or contact the maintainer.");
+ printf "<pre>\n%s\n</pre>\n\n", $query;
+ printf "<blockquote>\n%s\n</blockquote>\n\n", $query;
+}
+
+########### string_to_date
+#
+# Wandelt einen Datumsstring wie er in der Datenbank gespeichert wird
+# in eine lesbare Form um.
+#
+sub string_to_date
+{
+ return "" if (!$_[0]);
+
+ $_[0] =~ /(....)(..)(..)/;
+ return "$3.$2.$1";
+}
+
+########### date_to_string
+#
+# Wandelt einen lesbaren Datumsstring in die Form um, in der er in der
+# Datenbank gespeichert werden kann.
+#
+sub date_to_string
+{
+ return "" if (!$_[0]);
+
+ ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
+ = localtime();
+
+ if ($_[0] eq "heute" || $_[0] eq "sofort" || $_[0] eq "pronto" || $_[0] eq "today" || $_[0] eq "now") {
+ $day = $date_mday;
+ $mon = $date_mon+1;
+ $year = $date_year;
+ } elsif ($_[0] eq "gestern" || $_[0] eq "yesterday") {
+ $day = $date_mday-1 if ($date_mday);
+ $mon = $date_mon+1;
+ $year = $date_year;
+ } elsif ($_[0] eq "morgen" || $_[0] eq "tomorrow") {
+ $day = $date_mday+1;
+ $mon = $date_mon+1;
+ $year = $date_year;
+ } else {
+ ($day,$mon,$year) = split(/\./, $_[0]);
+ if (!$year) {
+ $year = $date_year;
+ }
+ }
+ $year += 1900 if ($year < 1901);
+ return sprintf("%4d%02d%02d", $year,$mon,$day);
+}
+
+########### index_user_hashes
+#
+# Fuellt diverse Cache-Listen mit Userdaten
+#
+# %ticket_list_user_realname Zuordnung User Realname
+# %ticket_list_user_email Zuordnung User Email
+# %ticket_list_email_realname Zuordnung Email Realname
+# %ticket_list_email_user Zuordnung Email User
+# %ticket_list_user_groups Zuordnung User Gruppen
+# %ticket_list_user_admin Zuordnung User Admin
+# %ticket_list_user_primary Zuordnung User Primary Group
+#
+#
+# Args = (realname,email,groups,admin)
+sub index_user_hashes
+{
+ my @row = @_;
+
+ $ticket_list_user_realname{$row[0]} = $row[1];
+ $ticket_list_user_email{$row[0]} = $row[2];
+ $ticket_list_email_realname{$row[2]} = $row[1];
+ $ticket_list_email_user{$row[2]} = $row[0];
+ $ticket_list_user_groups{$row[0]} = $row[3];
+ if ($row[4] eq "j") {
+ $ticket_list_user_admin{$row[0]} = 1;
+ } else {
+ $ticket_list_user_admin{$row[0]} = 0;
+ }
+ $ticket_list_user_primary{$row[0]} = $row[5];
+}
+
+########### index_user
+#
+# Fuellt diverse Cache-Listen mit Userdaten. Stuetzt sich auf
+# index_user_hashes ab.
+#
+sub index_user
+{
+ my $user = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT username,realname,email,groups,admin,maingroup FROM $ticket_table_user";
+ $query .= sprintf (" WHERE username = '%s' OR email = '%s'", $user, $user) if (length($user));
+
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ &index_user_hashes($row[0],$row[1],$row[2],$row[3],$row[4],$row[5]);
+ } else {
+ printf STDERR "Query: %s\n", $query,;
+ }
+ }
+}
+
+########### get_language
+#
+# Gibt die zu benutzende Sprache zurueck, beinhaltet ein index_user()
+#
+sub get_language
+{
+ my $user = shift;
+ my $lang = $ticket_default_language;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT username,realname,email,groups,admin,maingroup,language,programs,assoc,misc FROM $ticket_table_user";
+ $query .= sprintf (" WHERE username = '%s'", $user) if (length($user));
+
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ &index_user_hashes($row[0],$row[1],$row[2],$row[3],$row[4],$row[5]);
+ $lang = $row[6];
+ $ticket_intern{'user'} = $user;
+ $ticket_intern{'programs'} = $row[7];
+ $ticket_intern{'assoc'} = $row[8];
+ $ticket_intern{'misc'} = $row[9];
+ }
+ }
+ return $lang;
+}
+
+########### user_email
+#
+# Gibt die Email-Adresses eines Users zurueck, falls unbekannt, wird
+# ein leerer String zurueckgegeben.
+#
+sub user_email
+{
+ return "" if (!$_[0]);
+ return $ticket_list_user_email{$_[0]} if (exists($ticket_list_user_email{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_user_email{$_[0]} if (exists($ticket_list_user_email{$_[0]}));
+ return "";
+}
+
+########### user_groups
+#
+# Gibt die Gruppen eines Users zurueck, falls unbekannt, wird ein
+# leerer String zurueckgegeben.
+#
+sub user_groups
+{
+ return "" if (!$_[0]);
+ return $ticket_list_user_groups{$_[0]} if (exists($ticket_list_user_groups{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_user_groups{$_[0]} if (exists($ticket_list_user_groups{$_[0]}));
+ return "";
+}
+
+########### user_realname
+#
+# Gibt den Realname einers Users zurueck, falls unbekannt, wird der
+# Username zurueckgegeben.
+#
+sub user_realname
+{
+ return "" if (!$_[0]);
+ return $ticket_list_user_realname{$_[0]} if (exists($ticket_list_user_realname{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_user_realname{$_[0]} if (exists($ticket_list_user_realname{$_[0]}));
+ return $_[0];
+}
+
+########### user_primary
+#
+# Gibt die primaere Gruppe einers Users zurueck, falls unbekannt, wird
+# ein leerer String zurueckgegeben.
+#
+sub user_primary
+{
+ return "" if (!$_[0]);
+ return $ticket_list_user_primary{$_[0]} if (exists($ticket_list_user_primary{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_user_primary{$_[0]} if (exists($ticket_list_user_primary{$_[0]}));
+ return "";
+}
+
+########### email_realname
+#
+# Gibt den Realname eines Users mit der angegebenen Email-Adresse
+# zurueck, falls unbekannt, wird die Email-Adresse zurueckgegeben.
+#
+sub email_realname
+{
+ return if (!$_[0]);
+ return $ticket_list_email_realname{$_[0]} if (exists($ticket_list_email_realname{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_email_realname{$_[0]} if (exists($ticket_list_email_realname{$_[0]}));
+ return $_[0];
+}
+
+########### email_user
+#
+# Gibt den User zur angegebenen Email-Adresse zurueck, falls unbekannt,
+# wird die Email-Adresse zurueckgegeben.
+#
+sub email_user
+{
+ return if (!$_[0]);
+ return $ticket_list_email_user{$_[0]} if (exists($ticket_list_email_user{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_email_user{$_[0]} if (exists($ticket_list_email_user{$_[0]}));
+ return $_[0];
+}
+
+########### is_admin
+#
+# Gibt 0 oder 1 zurueck, je nachdem, ob der angegebene User als Admin
+# eingetragen ist oder nicht.
+#
+sub is_admin
+{
+ return 0 if (!$_[0]);
+ return $ticket_list_user_admin{$_[0]} if (exists($ticket_list_user_admin{$_[0]}));
+ &index_user ($_[0]);
+ return $ticket_list_user_admin{$_[0]} if (exists($ticket_list_user_admin{$_[0]}));
+ return 0;
+}
+
+########### is_user
+#
+# Gibt 0 oder 1 zurueck, je nachdem, ob der angegebene User
+# tatsaechlich als User (in der angegebenen Gruppe) existiert.
+#
+sub is_user
+{
+ my $group = $_[1];
+
+ return 0 if (!$_[0]);
+ return 1 if (exists($ticket_list_user_groups{$_[0]}) && $ticket_list_user_groups{$_[0]} =~ /$group/);
+ &index_user ($_[0]);
+ return 1 if (exists($ticket_list_user_groups{$_[0]}) && $ticket_list_user_groups{$_[0]} =~ /$group/);
+ return 0;
+}
+
+########### user_assoc
+#
+# Gibt die assoziierten Gruppen eines Users zurueck, falls unbekannt, wird ein
+# leerer String zurueckgegeben.
+#
+sub user_assoc
+{
+ my $user = shift;
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return "" if (!$user);
+
+ if (exists($ticket_intern{'user'}) && exists($ticket_intern{'assoc'})
+ && $ticket_intern{'user'} eq $_[0]) {
+ return $ticket_intern{'assoc'};
+ } else {
+ $dbh = &noc_connect if (!$dbh);
+
+ $query .= sprintf ("SELECT assoc FROM %s WHERE username = '%s' OR email = '%s'", $ticket_table_user, $user, $user);
+
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return $row[0];
+ }
+ }
+ }
+ return "";
+}
+
+########### uniq_fields
+#
+# Loescht doppelte Eintraege aus der Liste - zur Vermeidung von
+# doppelten Mails.
+#
+sub uniq_fields
+{
+ my %addr = ();
+ my $user;
+ my @result = ();
+
+ while ($user = shift) {
+ if ($user && length($user)) {
+ if (!exists $addr{$user}) {
+ $addr{$user} = 1;
+ push (@result, $user);
+ }
+ }
+ }
+ return @result
+}
+
+########### uniq_user_email
+#
+# Returns a uniqe list of email addresses regarding the given array of
+# users.
+#
+sub uniq_user_email
+{
+ my @user = uniq_fields(@_);
+ my @fields = ();
+ my $user;
+ my $addr;
+
+ while ($user = shift (@user)) {
+ $addr = &user_email($user);
+ push(@fields, $addr) if ($addr);
+ }
+ return @fields;
+}
+
+########### uniq_user_realname
+#
+# Returns a uniqe list of realnames regarding the given array of
+# users.
+#
+sub uniq_user_realname
+{
+ my @user = uniq_fields(@_);
+ my @fields = ();
+ my $user;
+ my $addr;
+
+ while ($user = shift (@user)) {
+ $addr = &user_realname($user);
+ push(@fields, $addr) if ($addr);
+ }
+ return @fields;
+}
+
+########### groupname
+#
+# Gibt die Beschreibung zu einer Gruppe zurueck, ggf. wird ein Query
+# abgesetzt
+
+#
+sub groupname
+{
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return '' if (!$_[0]);
+ return $groupname{$_[0]} if (exists($groupname{$_[0]}));
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT description FROM $ticket_table_groups";
+ $query .= sprintf (" WHERE name = '%s'", $_[0]);
+
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $groupname{$_[0]} = $row[0];
+ return $row[0];
+ }
+ }
+ return $_[0];
+}
+
+########### get_subject
+#
+# Gibt das Subject eines Tickets zurueck.
+#
+sub get_subject
+{
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+
+ return if (!$_[0]);
+
+ $dbh = &noc_connect if (!$dbh);
+
+ $query = "SELECT subject FROM $ticket_table WHERE nr = $_[0]";
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ return $row[0];
+ } else {
+ do error_query($query);
+ }
+ } else {
+ do error_query($query);
+ }
+}
+
+########### prepare_text
+#
+# Tries to do some conversation from normal text to html'ed
+#
+sub prepare_text
+{
+ my $text = $_[0];
+
+ $text =~ s/\r//g;
+ $text =~ s/\n\n(\n)*/<p>\n/g;
+ return $text;
+}
+
+########### de_html
+#
+# Tries to remove some tags from texts.
+#
+sub de_html
+{
+ my $text = $_[0];
+
+ $text =~ s/<p>/\n/g;
+ $text =~ s/(\r?\n)*<hr>(\r?\n)*/\n--------------------------------------------------------------------\n/g;
+ $text =~ s/<li>/- /g;
+ $text =~ s/<..?.?.?>//g;
+ $text =~ s/Ä/Ae/g;
+ $text =~ s/ä/ae/g;
+ $text =~ s/Ö/Oe/g;
+ $text =~ s/ö/oe/g;
+ $text =~ s/Ü/Ue/g;
+ $text =~ s/ü/ue/g;
+ $text =~ s/ß/ss/g;
+ return $text;
+}
+
+########### de_msql
+#
+# Tries to remove some msql quotes.
+#
+sub de_msql
+{
+ my $text = $_[0];
+
+ $text =~ s/\\'/'/g;
+ $text =~ s/\\\\/\\/g;
+ $text =~ s/<([^<>]*)\@([^<>]*)>/<$1\@$2>/g;
+ $text =~ s:\[([^/\]]*)/([^\]]*)\]:<b>Update from \1 by \2</b><p>:g;
+ # The following regexp is stolen from the Debian package of urlview.
+ $text =~ s!((((http|https|ftp|gopher)|mailto):(//)?[^ <>"\t\r\n]*|www\.[-a-z0-9.]+)[^ .,;\t\r\n<">\):])!<a href=\"$1\">$1</a>!g;
+ return $text;
+}
+
+########### load_language
+#
+# Load strings for the given language
+#
+sub load_language
+{
+ my $lang = $_[0];
+
+ require "ticket-$lang.pl" if ($lang ne "gbr");
+}
+
+########### start_program
+#
+# Gibt 1 zurueck, wenn der angegebene User das angegebene Programm
+# ausfuehren darf, ansonsten 0.
+#
+sub start_program
+{
+ my $query;
+ my $sth;
+ my $rc;
+ my @row;
+ my $programs;
+
+ $dbh = &noc_connect if (!$dbh);
+
+ return 1 if ($ENV{'LOGNAME'} eq $ticket_superuser);
+
+ if ( !exists($ticket_intern{'user'}) || !exists($ticket_intern{'programs'})
+ || $ticket_intern{'user'} ne $_[1]) {
+ $query = sprintf("SELECT programs FROM $ticket_table_user WHERE username = '%s'", $_[1]);
+ $sth = $dbh->prepare($query);
+ if ($sth) {
+ $rc = $sth->execute;
+ if ($rc > 0 && (@row = $sth->fetchrow_array)) {
+ $programs = $row[0];
+ }
+ }
+ } else {
+ $programs = $ticket_intern{'programs'}
+ }
+
+ return 1 if ($programs =~ /:$_[0]:/);
+
+ return 0;
+}
+
+########### forkme
+#
+# Returns 0 if it's the child, $pid if its the parent and undef if
+# error. Possible argument $care, define if the routine needs to be
+# successful with fork, ignore if not.
+#
+sub forkme
+{
+ my $care = shift;
+ my $pid;
+
+ $SIG{'CHLD'} = 'IGNORE';
+ if ($pid = fork) {
+ waitpid($pid,0);
+ return $pid;
+ } elsif (defined $pid) {
+ if (fork) {
+ exit (0);
+ } else {
+ return 0;
+ }
+ }
+ return undef;
+}
+
+########### sql_clike
+#
+# Depending on the backend database the string expression to be used
+# by a CLIKE statement varies. This routine take this into account.
+#
+sub sql_clike
+{
+ my $string = shift;
+
+ return "" if (!$string);
+
+ if ($ticket_dbdriver eq 'Pg') {
+ return $string;
+ } elsif ($ticket_dbdriver eq 'mSQL' || $ticket_dbdriver eq 'mysql') {
+ return "%" . $string . "%";
+ }
+}
+
+########### html_quote
+# Converts a string into proper HTML text. This is essentially needed
+# if text fields contain regular quotes (") which are used to delimit
+# fields as well.
+#
+sub html_quote
+{
+ my $string = shift;
+
+ return "" if (!$string);
+
+ $string =~ s/"/\"/g;
+ return $string;
+}
+
+# Make perl happy
+#
+1;
+
+# Local variables:
+# mode: perl
+# End: