#! /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','&nbsp; &nbsp;',
	      'Mozilla','&nbsp;',
	      '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 &lt;pre&gt;</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 &lt;pre&gt;</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 &lt;pre&gt;</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 &lt;pre&gt;</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;