#!/usr/local/sysmansw/runtime/perl/bin/perl -w

#
## -_- $Name: CallTrack-1_3b $ % $Id: ctfe,v 1.30 2001/09/08 16:46:44 sietse Exp $
#

my $CTDIR;

BEGIN
{
    # Default directory name is ~/calltrack - changeable by defining the variable CTDIR
    $CTDIR = $ENV{'CTDIR'};
    $CTDIR = $ENV{'HOME'} . "/calltrack" if not defined($CTDIR);

    push @INC, $CTDIR;
}

use DBI;
use CGI;
use IO::Handle;
use dbconnect;
use Time::Local;
use strict;

my $dbh;
my %sessions;
my @priority = (1, 2, 3, 4);
my %priority = ( 1 => 'critical', 2 => 'high', 3 => 'medium', 4 => 'low' );
my @status = (1, 2, 3);
my %status = ( 1 => 'open', 2 => 'hold', 3 => 'closed');

my $h_color = '#ff6666';
my $l_color = '#99ffcc';
my $r_color = '#ccccff';
my $r_color_low = '#9999cc';

my $style_top = "
      body { font-family: verdana, arial; font-size: 10pt; background: $h_color; }
      td { font-family: verdana, arial; font-size: 10pt; background: $h_color; }
      a { color: #0000ff; text-decoration: none; }
      a.link { color: #0000ff; text-decoration: none; }
      a.visited { color: #0000ff; text-decoration: none; }
      a.active d{ color: #0000ff; text-decoration: none; }
      h1 { font-size: 18pt; font-weight: bold; }
      h2 { font-size: 16pt; font-weight: bold; }
      h3 { font-size: 14pt; font-weight: bold; }
      h4 { font-size: 12pt; font-weight: bold; }
";
my $style_left = "
      body { font-family: verdana, arial; font-size: 10pt; background: $l_color; }
      td { font-family: verdana, arial; font-size: 10pt; background: $l_color; }
      a { color: #0000ff; text-decoration: none; }
      a.link { color: #0000ff; text-decoration: none; }
      a.visited { color: #0000ff; text-decoration: none; }
      a.active d{ color: #0000ff; text-decoration: none; }
      h1 { font-size: 18pt; font-weight: bold; }
      h2 { font-size: 16pt; font-weight: bold; }
      h3 { font-size: 14pt; font-weight: bold; }
      h4 { font-size: 12pt; font-weight: bold; }
";
my $style_right = "
      body { font-family: verdana, arial; font-size: 10pt; background: $r_color; }
      td { font-family: verdana, arial; font-size: 10pt; background: $r_color; }
      td.low { font-family: verdana, arial; font-size: 10pt; background: $r_color_low; }
      td.white { font-family: verdana, arial; font-size: 10pt; background: white; }
      a { color: #0000ff; text-decoration: none; }
      a.link { color: #0000ff; text-decoration: none; }
      a.visited { color: #0000ff; text-decoration: none; }
      a.active d{ color: #0000ff; text-decoration: none; }
      h1 { font-size: 18pt; font-weight: bold; }
      h2 { font-size: 16pt; font-weight: bold; }
      h3 { font-size: 14pt; font-weight: bold; }
      h4 { font-size: 12pt; font-weight: bold; }
";
my $style_printable = "
      body { font-family: verdana, arial; font-size: 10pt; background: white; }
      td { font-family: verdana, arial; font-size: 10pt; background: white }
      a { color: #0000ff; text-decoration: none; }
      a.link { color: #0000ff; text-decoration: none; }
      a.visited { color: #0000ff; text-decoration: none; }
      a.active d{ color: #0000ff; text-decoration: none; }
      h1 { font-size: 18pt; font-weight: bold; }
      h2 { font-size: 16pt; font-weight: bold; }
      h3 { font-size: 14pt; font-weight: bold; }
      h4 { font-size: 12pt; font-weight: bold; }
";

##########################################################################
##########################################################################
##########################################################################

sub logmsg
{
    my $msg = shift @_;
    my $tm;

    chomp($tm = localtime(time));
    print STDERR "$tm: ". $msg;
}

sub logdie
{
    my $msg = shift @_;
    my $tm;

    chomp($tm = localtime(time));
    die "$tm: ". $msg;
}

##########################################################################
##########################################################################
##########################################################################

sub getconfig
{
    $dbh = dbconnect::connect_ctdb(0);
}

sub mkdate
{
    my $tm = shift;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($tm);
    my $mname = ( 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                  'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )[$mon];

    return sprintf("%02d/%s/%02d %02d:%02d", $mday, $mname, $year - 100, $hour, $min);
}

sub sendgif
{
    my $q = shift;
    my ($nread, $buffer, $GIF);

    my $gif = $q->param('gif');

    $GIF = 100;
    open(GIF, "<$CTDIR/web/$gif") or logmsg "Cannot open gif: $!\n";
    print "Content-type: image/gif\n\n";

    $nread = read GIF, $buffer, 4096;
    syswrite STDOUT, $buffer, $nread;

    close GIF;
}

##################################################################
##################################################################
##################################################################

sub nc_user
{
    my ($q, $user) = @_;
    my @users = ('<select user>');
    my $userlst = $dbh->selectall_arrayref("select * from ct_users");
    my $nc_user = $q->param('nc_user');

    foreach my $u (@$userlst) { push @users, $$u[0]; }

    my $script = '
       function do_cancel()
       {
           document.forms[0].cancelled.value = 1
           return 1
       }
       function set_submit()
       {
           document.forms[0].do_submit.value = 1
           return 1
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - User details',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform,
          $q->hidden( { -name => 'aframe',    -default => 'detail' } ),
          $q->hidden( { -name => 'nc_user',   -default => '0' } ),
          $q->hidden( { -name => 'cancelled', -default => '0' } ),
          $q->hidden( { -name => 'do_submit', -default => '0' } );

    if ($q->param('cancelled'))
    {
        print $q->b('Action cancelled'),
              $q->endform,
              $q->end_html;
        return 1;
    }

    if ($nc_user eq "add")
    {
        ###########################
        # User add
        ###########################
        if ($q->param('do_submit'))
        {
            if ($q->param("username") !~ /^[a-zA-Z0-9_-]+$/)
            {
                print $q->p( { -style => 'color: red' }, $q->b("Username Can only contain a-z, A-Z, - and _"));
                goto REPRINT;
            }
            my $user = $q->param("username");
            if (grep /^$user$/, @users)
            {
                print $q->p( { -style => 'color: red' }, $q->b("User already exists"));
                goto REPRINT;
            }
            if ((not $q->param("password1")) || $q->param("password1") ne $q->param("password2"))
            {
                print $q->p( { -style => 'color: red' }, $q->b("No password, or passwords are not equal"));
                goto REPRINT;
            }
            my $passwd = crypt($q->param('password1'),
                               join('', ('.', '/', 0..9, 'A'..'Z', 'a'..'z')[rand 64, rand 64]));
            my $type = ($q->param("admin")) ? "admin" : "user";

            $dbh->do("insert into ct_users values('$user', '$passwd', '$type')");
            $dbh->commit;

            print $q->b('User added'),
                  $q->endform,
                  $q->end_html;
            return 1;
        }

        REPRINT:
        print $q->big("Add user"),
              $q->table({ -border => '0', -width => '100%' },
                $q->Tr(
                  $q->td('Username'),
                  $q->td($q->textfield(-name => 'username', -default => '', -size => 20, -maxlength => 20))
                ),
                $q->Tr(
                  $q->td('Password'),
                  $q->td($q->password_field(-name => 'password1', -default => '', -size => 20, -maxlength => 20))
                ),
                $q->Tr(
                  $q->td('Repeat password'),
                  $q->td($q->password_field(-name => 'password2', -default => '', -size => 20, -maxlength => 20))
                ),
                $q->Tr(
                  $q->td('User type'),
                  $q->td($q->checkbox(-name    => 'admin',
                                      -checked => '',
                                      -value   => 'admin',
                                      -label   => 'Administrator'))
                )
              ),
              $q->p,
              $q->submit( { -name    => 'Cancel',
                            -value   => 'Cancel',
                            -onClick => 'do_cancel()' } ),
              $q->submit( { -name    => 'Add',
                            -value   => 'Add',
                            -onClick => 'set_submit()' } );
    } elsif ($nc_user eq "change")
    {
        ###########################
        # User change
        ###########################
        if ($q->param('do_submit') && $q->param('username') && $q->param('username') ne '<select user>')
        {
            my $user = $q->param("username");
            if ($q->param("password1") ne $q->param("password2"))
            {
                print $q->p( { -style => 'color: red' }, $q->b("Passwords are not equal"));
                goto REPRINT2;
            }
            my $passwd = '!';
            if ($q->param('password1'))
            {
                $passwd = crypt($q->param('password1'),
                               join('', ('.', '/', 0..9, 'A'..'Z', 'a'..'z')[rand 64, rand 64]));
            }
            my $type = ($q->param("admin")) ? "admin" : "user";
            
            if ($passwd eq '!')
            {
                $dbh->do("update ct_users set role='$type' where userid='$user'");
            }
            else
            {
                $dbh->do("update ct_users set password='$passwd', role='$type' where userid='$user'");
            }
            $dbh->commit;

            print $q->b('User changed'),
                  $q->endform,
                  $q->end_html;
            return 1;
        }

        REPRINT2:
        my $select =  $q->popup_menu(-name     => 'username',
                                     -values   => \@users,
                                     -onChange => 'javascript:submit()');

        my $username = $q->param('username');
        if ($username && $username ne '<select user>')
        {
            my $user = $dbh->selectall_arrayref("select * from ct_users where userid='$username'");
            $user = $$user[0];
            my ($passwd, $type) = @$user[1, 2];

            print $q->big("Edit user"),
                  $q->table({ -border => '0', -width => '100%' },
                    $q->Tr(
                      $q->td('Username'),
                      $q->td($select)
                    ),
                    $q->Tr(
                      $q->td('Password'),
                      $q->td($q->password_field(-name => 'password1', -default => '', -size => 20, -maxlength => 20))
                    ),
                    $q->Tr(
                      $q->td('Repeat password'),
                      $q->td($q->password_field(-name => 'password2', -default => '', -size => 20, -maxlength => 20))
                    ),
                    $q->Tr(
                      $q->td('User type'),
                      $q->td($q->checkbox(-name     => 'admin',
                                          -checked  => ($type eq 'admin') ? 'admin' : '',
                                          -override => 1,
                                          -value    => 'admin',
                                          -label    => 'Administrator'))
                    )
                  ),
                  $q->p,
                  $q->submit( { -name    => 'Cancel',
                                -value   => 'Cancel',
                                -onClick => 'do_cancel()' } ),
                  $q->submit( { -name    => 'Change',
                                -value   => 'Change',
                                -onClick => 'set_submit()' } );
        }
        else
        {
            print $q->big("Edit user"),
                  $q->table({ -border => '0', -width => '100%' },
                    $q->Tr(
                      $q->td('Username'),
                      $q->td($select)
                    )
                  );
        }
    } elsif ($nc_user eq "remove")
    {
        ###########################
        # User remove
        ###########################
        my $username = $q->param('username');
        if ($q->param('do_submit') && $username && $username ne '<select user>')
        {
            $dbh->do("delete from ct_users where userid='$username'");
            $dbh->commit;

            print $q->b('User removed'),
                  $q->endform,
                  $q->end_html;
            return 1;
        }

        my $select =  $q->popup_menu(-name     => 'username',
                                     -values   => \@users,
                                     -onChange => 'javascript:submit()');

        print $q->big("Remove user"),
              $q->table({ -border => '0', -width => '100%' },
                $q->Tr(
                  $q->td('Username'),
                  $q->td($select)
                )
              );
        if ($username && $username ne '<select user>')
        {
            print $q->p,
                  $q->submit( { -name    => 'Cancel',
                                -value   => 'Cancel',
                                -onClick => 'do_cancel()' } ),
                  $q->submit( { -name    => 'Remove',
                                -value   => 'Remove',
                                -onClick => 'set_submit()' } );
        }
    }

    print $q->endform,
          $q->end_html;

    return 1;
}

##################################################################
##################################################################
##################################################################

sub nc_vendor
{
    my ($q, $user) = @_;

    my ($vendor, $vendors);
    my $nc_vendor = $q->param('nc_vendor');
    my $vendorid = $q->param('vendorid');
    my $vendorname = $q->param('vendorname'); $vendorname = '' if not $vendorname;
    my $telno = $q->param('telno'); $telno = '' if not $telno;
    my $faxno = $q->param('faxno'); $faxno = '' if not $faxno;
    my $email = $q->param('email'); $email = '' if not $email;
    my $address = $q->param('address'); $address = '' if not $address;
    my $zipcode = $q->param('zipcode'); $zipcode = '' if not $zipcode;
    my $city = $q->param('city'); $city = '' if not $city;
    my $state = $q->param('state'); $state = '' if not $state;
    my $country = $q->param('country'); $country = '' if not $country;
    my $description = $q->param('description'); $description = '' if not $description;
    my $auto_inc = $q->param('auto_inc'); $auto_inc = '0' if not $auto_inc;
    my $nr_dig = $q->param('nr_dig'); $nr_dig = '1' if not $nr_dig;
    my $next_nr = $q->param('next_nr'); $next_nr = '1' if not $next_nr;
    my $prefix = $q->param('prefix'); $prefix = '' if not $prefix;
    my $ch_by = $q->param('ch_by'); $ch_by = '' if not $ch_by;
    my $ch_at = $q->param('ch_at'); $ch_at = '' if not $ch_at;

    my $script = '
       function do_cancel()
       {
           document.forms[0].cancelled.value = 1
           return 1
       }
       function set_remove()
       {
           document.forms[0].do_remove.value = 1
           return 1
       }
       function set_submit()
       {
           if (document.forms[0].vendorname.value == "")
           {
               alert("Give at least a vendor name");
               return 0;
           }
           if (document.forms[0].auto_inc[1].checked)
           {
               re = /^[0-9]+$/;
               if (! re.test(document.forms[0].nr_dig.value))
               {
                   alert("Number of digits must be numerical");
                   return 0;
               }
               if (! re.test(document.forms[0].next_nr.value))
               {
                   alert("Next number must be numerical");
                   return 0;
               }
           }
           document.forms[0].do_submit.value = 1
           return 1
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Vendor details',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform,
          $q->hidden( { -name => 'aframe',    -default => 'detail' } ),
          $q->hidden( { -name => 'nc_vendor', -default => '0' } ),
          $q->hidden( { -name => 'cancelled', -default => '0' } ),
          $q->hidden( { -name => 'do_submit', -default => '0' } );

    if ($q->param('cancelled'))
    {
        print $q->b('Action cancelled'),
              $q->endform,
              $q->end_html;
        return 1;
    }

    ################
    # Add vendor
    ################
    if ($nc_vendor eq "add")
    {
        if ($q->param('do_submit'))
        {
            $vendorid = $dbh->selectall_arrayref('select vendorid from next_id');
            $vendorid = $$vendorid[0];
            $vendorid = $$vendorid[0];
            $dbh->do("update next_id set vendorid=" . ($vendorid + 1));

            $vendorname =~ s/'/''/g;
            $telno =~ s/'/''/g;
            $faxno =~ s/'/''/g;
            $email =~ s/'/''/g;
            $address =~ s/'/''/g;
            $zipcode =~ s/'/''/g;
            $city =~ s/'/''/g;
            $state =~ s/'/''/g;
            $country =~ s/'/''/g;
            $description =~ s/'/''/g;
            $prefix =~ s/'/''/g;
            $nr_dig = 1 if not $auto_inc;
            $next_nr = 1 if not $auto_inc;
            $dbh->do("insert into vendors values(
                      $vendorid,
                      '$vendorname',
                      '$telno',
                      '$faxno',
                      '$email', 
                      '$address',
                      '$zipcode',
                      '$city',
                      '$state',
                      '$country',
                      '$description',
                      '$user',
                       " . time . ",
                      $auto_inc,
                      $nr_dig,
                      $next_nr,
                      '$prefix'  )");

            $dbh->commit;

            print $q->b('Vendor added'),
                  $q->endform,
                  $q->end_html;

            return 1;
        }

        print $q->h1("New vendor");
    }
    ################
    # Change vendor
    ################
    elsif ($nc_vendor eq "change")
    {
        if ($q->param('do_submit'))
        {
            $vendorname =~ s/'/''/g;
            $telno =~ s/'/''/g;
            $faxno =~ s/'/''/g;
            $email =~ s/'/''/g;
            $address =~ s/'/''/g;
            $zipcode =~ s/'/''/g;
            $city =~ s/'/''/g;
            $state =~ s/'/''/g;
            $country =~ s/'/''/g;
            $description =~ s/'/''/g;
            $prefix =~ s/'/''/g;
            $nr_dig = 1 if not $auto_inc;
            $next_nr = 1 if not $auto_inc;
            $dbh->do("update vendors set
                      vendorname = '$vendorname',
                      telno      = '$telno',
                      faxno      = '$faxno',
                      email      = '$email', 
                      address    = '$address',
                      zipcode    = '$zipcode',
                      city       = '$city',
                      state      = '$state',
                      country    = '$country',
                      descr      = '$description',
                      auto_inc   = $auto_inc,
                      nr_dig     = $nr_dig,
                      next_nr    = $next_nr,
                      prefix     = '$prefix',
                      changed_by = '$user',
                      changed_at = " . time . "
                      where vendorid = $vendorid");

            $dbh->commit;

            print $q->b('Vendor changed'),
                  $q->endform,
                  $q->end_html;
            return 1;
        }

        if (not $vendorid)
        {
            my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
            my (@vendor_ids, %vendor_names);
            @vendor_ids = ('0');
            %vendor_names = ( '0' => '<select vendor>' );
            foreach my $vendor (@$vendors)
            {
                push @vendor_ids, $$vendor[0];
                $vendor_names{$$vendor[0]} =  $$vendor[1];
            }
            my $button = $q->popup_menu(-name     => 'vendorid',
                                        -values   => \@vendor_ids, 
                                        -labels   => \%vendor_names,
                                        -onChange => 'javascript:submit()',
                                        -default  => $vendorid);

            print $q->h1("Edit vendor"),
                  $q->center($button),
                  $q->endform,
                  $q->end_html;

            return 1;
        }
        else
        {
            if (not $q->param('reflow'))
            {
                $vendors = $dbh->selectall_arrayref("select * from vendors where vendorid=$vendorid");
                $vendor = $$vendors[0];

                $vendorname  = $$vendor[1];
                $telno       = $$vendor[2];
                $faxno       = $$vendor[3];
                $email       = $$vendor[4];
                $address     = $$vendor[5];
                $zipcode     = $$vendor[6];
                $city        = $$vendor[7];
                $state       = $$vendor[8];
                $country     = $$vendor[9];
                $description = $$vendor[10];
                $ch_by       = $$vendor[11];
                $ch_at       = $$vendor[12];
                $auto_inc    = $$vendor[13];
                $nr_dig      = $$vendor[14];
                $next_nr     = $$vendor[15];
                $prefix      = $$vendor[16];
            }

            print $q->h1("Edit vendor"),
                  $q->hidden( { -name => 'vendorid', -default => 1 } ),
                  $q->hidden( { -name => 'reflow', -default => $vendorid } );
        }
    }
    ################
    # Remove vendor
    ################
    elsif ($nc_vendor eq "remove")
    {
        if ($q->param('do_remove'))
        {
            # Find all calls for this vendor
            my $callids = $dbh->selectall_arrayref("select callid from callitem where vendorid=$vendorid");
            foreach my $callid (@$callids)
            {
                $dbh->do("delete from callhist where callid=$$callid[0]");
                $dbh->do("delete from calls where callid=$$callid[0]");
            }
            $dbh->do("delete from callitem where vendorid=$vendorid");
            $dbh->do("delete from vendoritems where vendorid=$vendorid");
            $dbh->do("delete from vendors where vendorid=$vendorid");
            $dbh->commit;

            print $q->h1("Remove vendor"),
                  $q->b('Vendor removed.'),
                  $q->endform,
                  $q->end_html;

            return 1;
        }
        else
        {
            if (not $vendorid)
            {
                my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
                my (@vendor_ids, %vendor_names);
                @vendor_ids = ('0');
                %vendor_names = ( '0' => '<select vendor>' );
                foreach my $vendor (@$vendors)
                {
                    push @vendor_ids, $$vendor[0];
                    $vendor_names{$$vendor[0]} =  $$vendor[1];
                }
                my $button = $q->popup_menu(-name     => 'vendorid',
                                            -values   => \@vendor_ids, 
                                            -labels   => \%vendor_names,
                                            -onChange => 'javascript:submit()',
                                            -default  => $vendorid);

                print $q->h1("Remove vendor"),
                      $q->center($button),
                      $q->endform,
                      $q->end_html;

                return 1;
            }
            else
            {
                $vendors = $dbh->selectall_arrayref("select * from vendors where vendorid=$vendorid");
                $vendor = $$vendors[0];

                print $q->h1("Remove vendor '$$vendor[1]'"),
                      $q->p({ -style=>'color: red' }, $q->big('WARNING - this will remove the vendor with all its ' .
                                                              'associated calls!')),
                      $q->p,
                      $q->submit( { -name    => 'Cancel',
                                    -value   => 'Cancel',
                                    -onClick => 'do_cancel()' } ),
                      $q->submit( { -name    => 'Remove',
                                    -value   => 'Remove',
                                    -onClick => 'set_remove()' } ),
                      $q->hidden( { -name => 'vendorid', -default => $vendorid } ),
                      $q->hidden( { -name => 'do_remove', -default => 0 } );
                      $q->endform,
                      $q->end_html;
                return 1;
            }
        }
    }
    else
    {
        printf $q->h1("Huh??"),
               $q->endform,
               $q->end_html;

        return 1;
    }

    print $q->table( { -border => '0', -width => '100%' },
             $q->Tr(
               $q->td('Vendor'),
               $q->td($q->textfield(-name => 'vendorname', -default => $vendorname, -size => 30, -maxlength => 30))
             ),
             $q->Tr(
               $q->td('Telno'),
               $q->td($q->textfield(-name => 'telno', -default => $telno, -size => 30, -maxlength => 30))),
             $q->Tr(
               $q->td('Faxno'),
               $q->td($q->textfield(-name => 'faxno', -default => $faxno, -size => 30, -maxlength => 30))
             ),
             $q->Tr(
               $q->td('Email'),
               $q->td($q->textfield(-name => 'email', -default => $email, -size => 50, -maxlength => 50))
             ),
             $q->Tr(
               $q->td('Address'),
               $q->td($q->textfield(-name => 'address', -default => $address, -size => 50, -maxlength => 50))
             ),
             $q->Tr(
               $q->td('Zipcode'),
               $q->td($q->textfield(-name => 'zipcode', -default => $zipcode, -size => 10, -maxlength => 10))
             ),
             $q->Tr(
               $q->td('City'),
               $q->td($q->textfield(-name => 'city', -default => $city, -size => 30, -maxlength => 30))
             ),
             $q->Tr(
               $q->td('State'),
               $q->td($q->textfield(-name => 'state', -default => $state, -size => 30, -maxlength => 30))
             ),
             $q->Tr(
               $q->td('Country'),
               $q->td($q->textfield(-name => 'country', -default => $country, -size => 25, -maxlength => 25))
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Description'),
               $q->td($q->textarea(-name => 'description', -default => $description, -rows => 5,
                                   -columns => 50, -maxlength => 4000))
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Auto callid'),
               $q->td($q->radio_group(-name      => 'auto_inc',
                                      -values    => [ '0', '1' ],
                                      -default   => $auto_inc,
                                      -linebreak => 'true',
                                      -labels    => { '0' => 'No', '1' => 'Yes' },
                                      -onClick   => 'javascript:submit()' ),
                      $q->br,
                      ($auto_inc) ? ($q->table(
                                       $q->Tr(
                                         $q->td({ -valign => 'top'}, 'Number of digits:'),
                                         $q->td($q->textfield(-name => 'nr_dig', -default => $nr_dig,
                                                              -size => 5, -maxlength => 5))
                                       ),
                                       $q->Tr(
                                         $q->td({ -valign => 'top'}, 'Next call number:'),
                                         $q->td($q->textfield(-name => 'next_nr', -default => $next_nr,
                                                              -size => 5, -maxlength => 5))
                                       ),
                                       $q->Tr(
                                         $q->td({ -valign => 'top'}, 'Callno prefix:'),
                                         $q->td($q->textfield(-name => 'prefix', -default => $prefix,
                                                              -size => 10, -maxlength => 10))
                                       )
                                     )
                                    ) :
                                    (
                                        $q->hidden( { -name => 'nr_dig',  -default => $nr_dig } ),
                                        $q->hidden( { -name => 'next_nr', -default => $next_nr } ),
                                        $q->hidden( { -name => 'prefix',  -default => $prefix } )
                                    )
                     )
             ),
             ($ch_by) ? $q->Tr(
                          $q->td({ -valign => 'top'}, 'Changed by'),
                          $q->td({ -class => 'low' }, $ch_by),
                        ) : "",
             ($ch_at) ? $q->Tr(
                          $q->td({ -valign => 'top'}, 'Changed at'),
                          $q->td({ -class => 'low' }, mkdate($ch_at)),
                        ) : ""
          ),
          $q->p,
          $q->submit( { -name    => 'Cancel',
                        -value   => 'Cancel',
                        -onClick => 'do_cancel()' } ),
          $q->submit( { -name    => 'AddChange',
                        -value   => ($nc_vendor eq "add") ? 'Add' : 'Change',
                        -onClick => 'set_submit()' } ),
          $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub nc_item
{
    my ($q, $user) = @_;

    my $nc_item = $q->param('nc_item');
    my $vendorid = $q->param('vendorid');
    my $vendorname = $q->param('vendorname');
    my $itemid = $q->param('itemid');
    my $itemname = $q->param('itemname'); $itemname = '' if not $itemname;
    my $contract = $q->param('contract'); $contract = '' if not $contract;
    my $license = $q->param('license'); $license = '' if not $license;
    my $description = $q->param('description'); $description = '' if not $description;
    my $ch_by = $q->param('ch_by'); $ch_by = '' if not $ch_by;
    my $ch_at = $q->param('ch_at'); $ch_at = '' if not $ch_at;

    my $script = '
       function do_cancel()
       {
           document.forms[0].cancelled.value = 1
           return 1
       }
       function set_remove()
       {
           document.forms[0].do_remove.value = 1
           return 1
       }
       function set_submit()
       {
           if (document.forms[0].itemname.value == "")
           {
               alert("Give at least a product name");
               return 0;
           }
           document.forms[0].do_submit.value = 1
           return 1
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Vendor details',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform,
          $q->hidden( { -name => 'aframe',    -default => 'detail' } ),
          $q->hidden( { -name => 'nc_item',   -default => '0' } ),
          $q->hidden( { -name => 'cancelled', -default => '0' } ),
          $q->hidden( { -name => 'do_submit', -default => '0' } );

    if ($q->param('cancelled'))
    {
        print $q->b('Action cancelled'),
              $q->endform,
              $q->end_html;
        return 1;
    }

    if (not $vendorid)
    {
        my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
        my (@vendor_ids, %vendor_names);
        @vendor_ids = ('0');
        %vendor_names = ( '0' => '<select vendor>' );
        foreach my $vendor (@$vendors)
        {
            push @vendor_ids, $$vendor[0];
            $vendor_names{$$vendor[0]} =  $$vendor[1];
        }
        my $button = $q->popup_menu(-name     => 'vendorid',
                                    -values   => \@vendor_ids, 
                                    -labels   => \%vendor_names,
                                    -onChange => 'javascript:submit()',
                                    -default  => $vendorid);

        print $q->h1("Choose vendor"),
              $q->center($button),
              $q->endform,
              $q->end_html;

        return 1;
    }
    else
    {
        my $vendor = $dbh->selectall_arrayref("select vendorname from vendors where vendorid=$vendorid");
	$vendor = $$vendor[0];
        $vendorname = $q->escapeHTML($$vendor[0]);
        print $q->hidden( { -name => 'vendorid',  -default => $vendorid } );
    }

    ################
    # Add item
    ################
    if ($nc_item eq "add")
    {
        if ($q->param('do_submit'))
        {
            $itemid = $dbh->selectall_arrayref('select itemid from next_id');
            $itemid = $$itemid[0];
            $itemid = $$itemid[0];
            $dbh->do("update next_id set itemid=" . ($itemid + 1));

            $itemname =~ s/'/''/g;
            $contract =~ s/'/''/g;
            $license =~ s/'/''/g;
            $description =~ s/'/''/g;
            $dbh->do("insert into vendoritems values(
                      $vendorid,
                      $itemid,
                      '$itemname',
                      '$contract',
                      '$license', 
                      '$description',
                      '$user',
                       " . time . ")");

            $dbh->commit;

            print $q->b('Product added'),
                  $q->endform,
                  $q->end_html;

            return 1;
        }

        print $q->h1("New product for $vendorname");
    }
    ################
    # Change item
    ################
    elsif ($nc_item eq "change")
    {
        if ($q->param('do_submit') && $itemid)
        {
            $itemname =~ s/'/''/g;
            $contract =~ s/'/''/g;
            $license =~ s/'/''/g;
            $description =~ s/'/''/g;
            $dbh->do("update vendoritems set
                      item = '$itemname',
                      contractnr = '$contract',
                      licensenr = '$license',
                      descr = '$description',
                      changed_by = '$user',
                      changed_at = " . time . "
                      where itemid = $itemid");

            $dbh->commit;

            print $q->b('Product changed'),
                  $q->endform,
                  $q->end_html;
            return 1;
        }

        if (not $itemid)
        {
            my $items = $dbh->selectall_arrayref("select itemid, item from vendoritems where vendorid=$vendorid");
            my (@item_ids, %item_names);
            @item_ids = ('0');
            %item_names = ( '0' => '<select product>' );
            foreach my $item (@$items)
            {
                push @item_ids, $$item[0];
                $item_names{$$item[0]} =  $$item[1];
            }
            my $button = $q->popup_menu(-name     => 'itemid',
                                        -values   => \@item_ids, 
                                        -labels   => \%item_names,
                                        -onChange => 'javascript:submit()');

            print $q->h1("Edit product for $vendorname"),
                  $q->center($button),
                  $q->endform,
                  $q->end_html;

            return 1;
        }
        else
        {
            my $items = $dbh->selectall_arrayref("select * from vendoritems where itemid=$itemid");
            my $item = $$items[0];

            $itemname = $$item[2];
            $contract = $$item[3];
            $license = $$item[4];
            $description = $$item[5];
            $ch_by = $$item[6];
            $ch_at = $$item[7];

            print $q->h1("Edit product"),
                  $q->hidden( { -name => 'itemid', -default => $itemid } );
        }
    }
    ################
    # Remove item
    ################
    elsif ($nc_item eq "remove")
    {
        if ($q->param('do_remove') && $itemid)
        {
            # Find all calls for this item
            my $callids = $dbh->selectall_arrayref("select callid from callitem where itemid=$itemid");
            foreach my $callid (@$callids)
            {
                my $nr = $dbh->selectall_arrayref("select count(*) from callitem where callid=$$callid[0]");
                $nr = $$nr[0];
                if ($$nr[0] == 1)
                {
                    # The all is only associated with this item
                    $dbh->do("delete from callhist where callid=$$callid[0]");
                    $dbh->do("delete from calls where callid=$$callid[0]");
                }
            }
            $dbh->do("delete from callitem where itemid=$itemid");
            $dbh->do("delete from vendoritems where itemid=$itemid");
            $dbh->commit;

            print $q->h1("Remove product"),
                  $q->b('Product removed.'),
                  $q->endform,
                  $q->end_html;

            return 1;
        }
        else
        {
            if (not $itemid)
            {
                my $items = $dbh->selectall_arrayref("select itemid, item from vendoritems where vendorid=$vendorid");
                my (@item_ids, %item_names);
                @item_ids = ('0');
                %item_names = ( '0' => '<select product>' );
                foreach my $item (@$items)
                {
                    push @item_ids, $$item[0];
                    $item_names{$$item[0]} =  $$item[1];
                }
                my $button = $q->popup_menu(-name     => 'itemid',
                                            -values   => \@item_ids, 
                                            -labels   => \%item_names,
                                            -onChange => 'javascript:submit()',
                                            -default  => $itemid);

                print $q->h1("Remove product"),
                      $q->center($button),
                      $q->endform,
                      $q->end_html;

                return 1;
            }
            else
            {
                my $items = $dbh->selectall_arrayref("select * from vendoritems where itemid=$itemid");
                my $item = $$items[0];

                print $q->h1("Remove product '$$item[2]'"),
                      $q->p({ -style=>'color: red' }, $q->big('WARNING - this will remove the product with all its ' .
                                                              'associated calls!')),
                      $q->p,
                      $q->submit( { -name    => 'Cancel',
                                    -value   => 'Cancel',
                                    -onClick => 'do_cancel()' } ),
                      $q->submit( { -name    => 'Remove',
                                    -value   => 'Remove',
                                    -onClick => 'set_remove()' } ),
                      $q->hidden( { -name => 'itemid', -default => $itemid } ),
                      $q->hidden( { -name => 'do_remove', -default => 0 } );
                      $q->endform,
                      $q->end_html;
                return 1;
            }
        }
    }
    else
    {
        printf $q->h1("Huh??"),
               $q->endform,
               $q->end_html;

        return 1;
    }

    print $q->table({ -border => '0', -width => '100%' },
             $q->Tr(
               $q->td('Product'),
               $q->td( $q->textfield(-name=>'itemname', -default => $itemname, -size => 50, -maxlength => 50))
             ),
             $q->Tr(
               $q->td('Contract nr'),
               $q->td( $q->textfield(-name=>'contract', -default => $contract, -size => 50, -maxlength => 50))
             ),
             $q->Tr(
               $q->td('License nr'),
               $q->td( $q->textfield(-name=>'license', -default => $license, -size => 50, -maxlength => 50))
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Description'),
               $q->td( $q->textarea(-name=>'description', -default => $description, -rows => 5,
                                    -columns => 50, -maxlength => 4000))
             ),
             ($ch_by) ? $q->Tr(
                          $q->td({ -valign => 'top'}, 'Changed by'),
                          $q->td({ -class => 'low' }, $ch_by)
                        ) : "",
             ($ch_at) ? $q->Tr(
                          $q->td({ -valign => 'top'}, 'Changed at'),
                          $q->td({ -class => 'low' }, ($ch_at) ? mkdate($ch_at) : "")
                        ) : ""
          ),
          $q->p,
          $q->submit( { -name    => 'Cancel',
                        -value   => 'Cancel',
                        -onClick => 'do_cancel()' } ),
          $q->submit( { -name    => 'AddChange',
                        -value   => ($nc_item eq "add") ? 'Add' : 'Change',
                        -onClick => 'set_submit()' } ),
          $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub del_call
{
    my ($q, $user) = @_;

    my $do_remove = $q->param('do_remove');
    my @del_calls = $q->param('del_calls'); @del_calls = () if not @del_calls;
    my $searchlist = $q->param('searchlist');
    $searchlist =~ s/~/&/g;
    $searchlist =~ s/\^/=/g;

    my $script = '
       function do_cancel()
       {
           document.forms[0].cancelled.value = 1
           return 1
       }
       function set_remove()
       {
           document.forms[0].do_remove.value = 1
           return 1
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Vendor details',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform,
          $q->hidden(-name => 'aframe',     -default => 'detail'),
          $q->hidden(-name => 'remove',     -default => '1'),
          $q->hidden(-name => 'do_remove',  -default => '0'),
          $q->hidden(-name => 'cancelled',  -default => '0'),
          $q->hidden(-name => 'del_calls',  -default => @del_calls),
          $q->hidden(-name => 'searchlist', -default => $searchlist);

    if (not @del_calls)
    {
        print $q->table( { -border => 0, -width => '100%' },
                $q->Tr,
                  $q->td( { -align => 'right' },
                    $q->a( { -href => $q->url() . "?$searchlist" }, "Back to search list"))),
              $q->b('No calls to remove'),
              $q->endform,
              $q->end_html;
        return 1;
    }

    if ($q->param('cancelled'))
    {
        print $q->table( { -border => 0, -width => '100%' },
                $q->Tr,
                  $q->td( { -align => 'right' },
                    $q->a( { -href => $q->url() . "?$searchlist" }, "Back to search list"))),
              $q->b('Action cancelled'),
              $q->endform,
              $q->end_html;
        return 1;
    }

    if ($do_remove)
    {
        $dbh->do("delete from callhist where callid in (" . join(',', @del_calls) . ")");
        $dbh->do("delete from calls where callid in (" . join(',', @del_calls) . ")");
        $dbh->do("delete from callitem where callid in (" . join(',', @del_calls) . ")");
        $dbh->commit;

        print $q->table( { -border => 0, -width => '100%' },
                $q->Tr,
                  $q->td( { -align => 'right' },
                    $q->a( { -href => $q->url() . "?$searchlist" }, "Back to search list"))),
              $q->p("Call(s) removed"),
              $q->endform,
              $q->end_html;
    }
    else
    {
        my $calls = $dbh->selectall_arrayref("select callnr,problem from calls
                                              where callid in (" . join(',', @del_calls) . ")");
        my $callrows = "";
        foreach my $call (@$calls)
        {
            $callrows .= $q->Tr($q->td( { -class => 'low' },
                                        $q->escapeHTML($$call[0])), $q->td($q->escapeHTML($$call[1])));
        }
        print $q->table( { -border => 0, -width => '100%' },
                $q->Tr,
                  $q->td( { -align => 'right' },
                    $q->a( { -href => $q->url() . "?$searchlist" }, "Back to search list"))),
              $q->b("Really remove the following call(s)?"),
              $q->p,
              $q->table( { -border => 0 }, $callrows),
              $q->p,
              $q->submit( { -name    => 'Cancel',
                            -value   => 'Cancel',
                            -onClick => 'do_cancel()' } ),
              $q->submit( { -name    => 'delcalls',
                            -value   => 'Remove',
                            -onClick => 'set_remove()' } ),
              $q->endform,
              $q->end_html;
    }
}

##################################################################
##################################################################
##################################################################

sub af_detail
{
    my ($q, $user) = @_;

    if ($q->param('nc_user'))
    { 
        nc_user($q, $user);
        return 1;
    }

    if ($q->param('nc_vendor'))
    { 
        nc_vendor($q, $user);
        return 1;
    }

    if ($q->param('nc_item'))
    { 
        nc_item($q, $user);
        return 1;
    }

    if ($q->param('search'))
    { 
        search($q, $user);
        return 1;
    }

    if ($q->param('remove'))
    { 
        del_call($q, $user);
        return 1;
    }

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Product details',
                         -vlink   => '#0000ff',
                         -style   => $style_right),
          $q->end_html;

    return 1;
}

##################################################################
##################################################################
##################################################################

sub af_task
{
    my ($q, $user) = @_;

    my $script = '
       function ch_state(which)
       {
           if (which.value == "open")
               which.value = "closed"
           else
               which.value = "open"
           document.forms[0].submit()
       }
       function do_submit()
       {
           parent.close();
           return 0;
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Administration',
                         -vlink   => '#0000ff',
                         -style   => $style_left,
                         -script  => $script),
          $q->startform ( { -onSubmit => 'do_submit()' } ),
          $q->hidden( { -name => 'aframe', -default => 'task' } ),
          $q->hidden( { -name => 'user_state',  -default => 'closed' } ),
          $q->hidden( { -name => 'vendor_state',  -default => 'closed' } ),
          $q->hidden( { -name => 'item_state',  -default => 'closed' } ),
          $q->hidden( { -name => 'call_state',  -default => 'closed' } ),
          $q->h1('Tasks'),
          $q->br;

    #############################
    # User management
    #############################
    my $user_state = $q->param('user_state');
    $user_state = 'closed' if not $user_state;

    my $usr = $q->ul(
                $q->li(
                  $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_user=add", -target => "detail" },
                                 "Add user"))
                ),
                $q->li(
                  $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_user=change", -target => "detail" },
                                 "Edit user"))
                ),
                $q->li(
                  $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_user=remove", -target => "detail" },
                                 "Remove user"))
                )
              ) . $q->br;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].user_state)' },
                             $q->img( { -src => $q->url() . "?gif=$user_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, '<b><big>User Management</big></b>', 
                     ($user_state eq 'open') ? $usr : '' )
            )
          );

    #############################
    # Vendor management
    #############################
    my $vendor_state = $q->param('vendor_state');
    $vendor_state = 'closed' if not $vendor_state;

    my $vendor= $q->ul(
                  $q->li(
                    $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_vendor=add", -target => "detail" },
                                   "Add vendor"))
                  ),
                  $q->li(
                    $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_vendor=change", -target => "detail" },
                                   "Edit vendor"))
                  ),
                  $q->li(
                    $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_vendor=remove", -target => "detail" },
                                   "Remove vendor"))
                  )
                ) . $q->br;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].vendor_state)' },
                             $q->img( { -src => $q->url() . "?gif=$vendor_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, '<b><big>Vendor Management</b></big>', 
                     ($vendor_state eq 'open') ? $vendor : '' )
            )
          );

    #############################
    # Item management
    #############################
    my $item_state = $q->param('item_state');
    $item_state = 'closed' if not $item_state;

    my $item = $q->ul(
                 $q->li(
                   $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_item=add", -target => "detail" },
                                  "Add product"))
                 ),
                 $q->li(
                   $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_item=change", -target => "detail" },
                                  "Edit product"))
                 ),
                 $q->li(
                   $q->big($q->a( { -href => $q->url() . "?aframe=detail&nc_item=remove", -target => "detail" },
                                  "Remove product"))
                 )
               ) . $q->br;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].item_state)' },
                             $q->img( { -src => $q->url() . "?gif=$item_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, '<b><big>Product Management</b></big>', 
                     ($item_state eq 'open') ? $item : '' )
            )
          );

    #############################
    # Call management
    #############################
    my $call_state = $q->param('call_state');
    $call_state = 'closed' if not $call_state;

    my $call = $q->ul(
                 $q->li(
                   $q->big($q->a( { -href => $q->url() . "?aframe=detail&search=1&remove=1", -target => "detail" },
                                  "Remove calls"))
                 )
               ) . $q->br;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].call_state)' },
                             $q->img( { -src => $q->url() . "?gif=$call_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, '<b><big>Call Management</b></big>', 
                     ($call_state eq 'open') ? $call : '' )
            )
          );

    #############################
    # Close button
    #############################
    print $q->p . $q->submit( { -name => 'Close admin window' } );

    print $q->endform,
          $q->end_html;

    return 1;
}

##################################################################
##################################################################
##################################################################

sub af_header
{
    my ($q, $user) = @_;

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Administration',
                         -vlink   => '#0000ff',
                         -style   => $style_top),
          $q->table( { -width => '100%', -border => '0', -cellspacing => '0', -cellpadding => '0' },
            $q->Tr(
              $q->td( { -align => 'center', -width => '50%' }, $q->big($q->b('CallTrack Administration'))),
              $q->td( { -align => 'center', -width => '50%' }, 
                      $q->a( { -href => 'http://www.sysman.nl', -target => "_top" } , 
                        $q->img( { -src => $q->url() . "?gif=sysman.gif", -border => 0 } )))
            )
          ),
          $q->end_html;

    return 1;
}

##################################################################
##################################################################
##################################################################

sub do_aframe
{
    my $q = shift;
    my $cookie = $q->cookie(-name => 'CallTrack');
    $cookie = "<unknown>" if not $cookie;
    my $user = $sessions{$cookie};
    my $frame_ok = 0;
    my $frame = $q->param('aframe');

    if (! $user)
    {
        &not_logged_in($q);
        return;
    }

    my $usr = $dbh->selectall_arrayref("select role from ct_users where userid='$user'");
    $usr = $$usr[0] if $usr;
    if ((not $usr) || $$usr[0] ne "admin")
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Unknown',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              "You are not an administrator!",
              $q->end_html;

        return;
    }

    (af_header($q, $user)  && ($frame_ok = 1)) if $frame eq "header";
    (af_task($q, $user) && ($frame_ok = 1)) if $frame eq "task";
    (af_detail($q, $user)  && ($frame_ok = 1)) if $frame eq "detail";

    if (! $frame_ok)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Unknown',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              "Unknown frame name?",
              $q->end_html;
    }
}

##################################################################
##################################################################
##################################################################

sub do_aframes
{
    my $q=shift;
    my $af_header  = $q->url()."?aframe=header";
    my $af_task    = $q->url()."?aframe=task";
    my $af_detail  = $q->url()."?aframe=detail";

    print $q->header,
          "<html>\n",
          "<frameset rows='70,*' frameborder='0' marginheigt='0' marginwidth='0'>\n",
              "<frame src='$af_header' name='header'>\n",
              "<frameset cols='37%,*' frameborder=1>\n",
                  "<frame src='$af_task' name='vendors'>\n",
                  "<frame src='$af_detail' name='detail'>\n",
                  "</frameset>\n",
              "</frameset>\n",
          "</frameset>\n",
          "</html>";
}

##################################################################
##################################################################
##################################################################

sub nc_call
{
    my ($q, $user) = @_;
    my $callid = $q->param('nc_call');
    my $vendorid = $q->param('vendorid');

    my @sel_items = ();
    my $priority = (sort(keys(%priority)))[0];
    my $status = (sort(keys(%status)))[0];
    my $callnr = "";
    my $problem = '';
    my $note = '';

    my $slist = $q->param("searchlist");
    my $slist2 = $slist if $slist;
    $slist2 =~ s/~/&/g if $slist;
    $slist2 =~ s/\^/=/g if $slist;

    my $back = "frame=detail&callid=$callid" if $callid && $callid > 0;
    $back .= "&searchlist=$slist" if $back && $slist;

    if ($q->param('call_cancelled'))
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              ($back ? ($q->table( { -border => 0, -width => '100%' },
                          $q->Tr(
                            $q->td( { -align => 'right' },
                               $q->a( { -href => $q->url() . "?$back" }, "Back to call"),
                               $slist2 ? (" | " . $q->a( { -href => $q->url() . "?$slist2" }, "Back to search list"))
                                       : "")))) : ""),
              $q->b('Action cancelled'),
              $q->end_html;

        return 1;
    }

    if ($q->param('call_submit'))
    {
        my $new = 0;

        @sel_items = $q->param('vendoritem');
        $priority = $q->param('priority');
        $status = $q->param('status');
        $callnr = $q->param('callnr');
        $note = $q->param('note');
        $problem = $q->param('problem');

        if ($callid <= 0)
        {
            $new = 1;
            $callid = $dbh->selectall_arrayref('select callid from next_id');
            $callid = $$callid[0];
            $callid = $$callid[0];
            $dbh->do("update next_id set callid=" . ($callid + 1));

            my ($vendor);
            $vendor = $dbh->selectall_arrayref("select auto_inc, nr_dig, next_nr, prefix
                                                from vendors where vendorid=$vendorid");
            $vendor = $$vendor[0];
            if ($$vendor[0])
            {
                my $fmt = "%s%0" . $$vendor[1] . "d";
                $callnr = sprintf($fmt, $$vendor[3], $$vendor[2]);
                $dbh->do("update vendors set next_nr=" . ($$vendor[2] + 1) . " where vendorid=$vendorid");
            }

            $note = "[i]New call.[/i]\n" . $note;
        }

        $callnr = "[unknown]" if not $callnr;
        if ($new)
        {
            foreach my $item (@sel_items)
            {
                $dbh->do("insert into callitem values($vendorid, $item, $callid)");
            }

            $dbh->do("insert into calls values($callid, $priority,$status," . $dbh->quote($callnr) . "," .
                                               $dbh->quote($problem) . "," . time . ",'$user')");
        }
        else
        {
            my @old_items = ();
            my $old_items = $dbh->selectall_arrayref("select itemid from callitem where callid=$callid");
            foreach my $old_item (@$old_items) { push(@old_items, $$old_item[0]); }

            my @rm_list = ();
            my @add_list = ();
            foreach my $item (@sel_items) { push(@add_list, $item) if not grep(/^$item$/, @old_items); }
            foreach my $item (@old_items) { push(@rm_list, $item) if not grep(/^$item$/, @sel_items); }
            if (@rm_list || @add_list)
            {
                my (@rm_itemnames, @add_itemnames);
                if (@rm_list)
                {
                    my $rm_items = $dbh->selectall_arrayref("select item from vendoritems
                                                             where itemid in (" . join(',', @rm_list) . ")");
                    foreach my $rm_item (@$rm_items) { push(@rm_itemnames, $$rm_item[0]); }
                }
                if (@add_list)
                {
                    my $add_items = $dbh->selectall_arrayref("select item from vendoritems
                                                              where itemid in (" . join(',', @add_list) . ")");
                    foreach my $add_item (@$add_items) { push(@add_itemnames, $$add_item[0]); }
                }

                my $n_note = "[i]Product list changed: product(s) '";
                $n_note .= (join('', '', @add_itemnames) . "' added") if @add_list;
                $n_note .= "; product(s) '" if (@rm_list && @add_list);
                $n_note .= (join('', '', @rm_itemnames) . "' removed") if @rm_list;
                $note .= $n_note . ".[/i]" . $note;

                $dbh->do("delete from callitem where callid=$callid");
                foreach my $item (@sel_items)
                {
                    $dbh->do("insert into callitem values($vendorid, $item, $callid)");
                }
            }

            my $old_call = $dbh->selectall_arrayref("select priority,status,callnr,problem
                                                     from   calls
                                                     where  callid=$callid");
            $old_call = $$old_call[0];

            if ($$old_call[0] != $priority)
            {
                $note = "[i]Priority changed from " . $priority{$$old_call[0]} . " to " .
                         $priority{$priority} . ".[/i]\n" . $note;
            }
            if ($$old_call[1] != $status)
            {
                $note = "[i]Status changed from " . $status{$$old_call[1]} . " to " .
                         $status{$status} . ".[/i]\n" . $note;
            }
            if ($$old_call[2] ne $callnr)
            {
                $note = "[i]Callnr changed from '" . $$old_call[2] . "' to '" .
                         $callnr . "'.[/i]\n" . $note;
            }
            if ($$old_call[3] ne $problem)
            {
                $note = "[i]Problem changed from '" . $$old_call[3] . "' to '" .
                         $problem . "'.[/i]\n" . $note;
            }

            if ($note !~ /[\!-~]/)
            {
                print $q->header,
                      $q->start_html(-title   => 'CallTrack - Call',
                                     -vlink   => '#0000ff',
                                     -style   => $style_right),
                      ($back ? ($q->table( { -width => '100%', -border => 0 },
                                  $q->Tr(
                                    $q->td( { -align => 'right' },
                                       $q->a( { -href => $q->url() . "?$back" }, "Back to call"),
                                       $slist2 ? (" | " . $q->a( { -href => $q->url() . "?$slist2" },
                                                                 "Back to search list"))
                                       : "")))) : ""),
                      $q->b('Nothing changed, no record generated.'),
                      $q->end_html;

                return 1;
            }

            $dbh->do("update calls set priority=$priority, status=$status,
                      callnr=" . $dbh->quote($callnr) . ",problem=" . $dbh->quote($problem) . "
                      where callid = $callid");
        }

        $dbh->do("insert into callhist values(" . $dbh->quote($callid) . "," . time . ",'$user', " . 
                                              $dbh->quote($note). ")");
        $dbh->commit;

        my $script = "
           function do_reload()
           {
               parent.frames['vendors'].document.forms[0].submit()
               document.forms[0].callid.value = $callid
               document.forms[0].submit()
           }";

        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right,
                             -script  => $script,
                             -onLoad  => 'do_reload()'),
              
              $q->startform,
              $q->hidden(-name => 'frame',  -default => 'detail'),
              $q->hidden(-name => 'callid',  -default => $callid),
              $q->hidden(-name => 'searchlist',  -default => $slist),
              $q->endform,
              $q->end_html;

        return;
    }

    my ($vendoritems, $vendor);
    if ($vendorid)
    {
        $vendor = $dbh->selectall_arrayref("select * from vendors where vendorid=$vendorid");
        $vendor = $$vendor[0];
        $vendoritems = $dbh->selectall_arrayref("select itemid, item from vendoritems where vendorid = $vendorid");
    }

    if ((not $$vendoritems[0]) || (not $vendorid))
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->b("There is no vendor defined, or there are no products defined for this vendor; " .
                    "an administrator must define a vendor or product first."),
              $q->end_html;
        return;
    }

    my (@items, %items);
    foreach my $item (@$vendoritems)
    {
        push @items, $$item[0];
        $items{$$item[0]} = $$item[1];
    }

    if ($callid > 0)
    {
        my $call = $dbh->selectall_arrayref("select * from calls where callid=$callid");
        $call = $$call[0];
        my $items = $dbh->selectall_arrayref("select itemid from callitem where callid=$callid");

        foreach my $item (@$items) { push @sel_items, $$item[0] } ;
        $priority = $$call[1];
        $status = $$call[2];
        $callnr = $$call[3];
        $problem = $$call[4];
    }

    my $script = '
       function do_nc_call()
       {
           var i, ok, re;

           if (document.forms[0].call_cancelled.value == 1)
               return 1;

           ok = 0;

           if (document.forms[0].vendoritem.checked)
               ok = 1;

           for (i = 0; i < document.forms[0].vendoritem.length; i++)
           {
               if (document.forms[0].vendoritem[i].checked)
                   ok = 1;
           }
           if (ok == 0)
           {
               alert("Choose a product")
               return false
           }
           
           re = /[a-zA-Z0-9]/
           if (! re.test(document.forms[0].problem.value))
           {
               alert("Give a problem description")
               return false
           }

           document.forms[0].call_submit.value = 1
           return 1;
       }

       function cancelled()
       {
           document.forms[0].call_cancelled.value = 1
           return 1
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Call',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform( { -onSubmit => 'do_nc_call()' } ),
          $q->hidden(-name => 'frame',  -default => 'detail'),
          $q->hidden(-name => 'nc_call',  -default => $callid),
          $q->hidden(-name => 'vendorid',  -default => $vendorid),
          $q->hidden(-name => 'call_submit',  -default => '0'),
          $q->hidden(-name => 'call_cancelled',  -default => '0'),
          $q->hidden(-name => 'searchlist',  -default => $slist),
          ($back ? ($q->table( { -width => '100%', -border => 0 },
                      $q->Tr(
                        $q->td( { -align => 'right' },
                           $q->a( { -href => $q->url() . "?$back" }, "Back to call"),
                           $slist2 ? (" | " . $q->a( { -href => $q->url() . "?$slist2" },
                                                     "Back to search list"))
                           : "")))) : ""),
          $q->h1((($callid <= 0) ? "New" : "Change") . " call -- " . $q->escapeHTML($$vendor[1])),
          $q->table(
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Products'),
              $q->td( { -class => 'white' },
                      $q->checkbox_group(-name      => 'vendoritem',
                                         -values    => \@items,
                                         -linebreak => 'true',
                                         -labels    => \%items,
                                         -default   => \@sel_items))
            ),
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Problem'),
              $q->td( $q->textfield(-name      => 'problem',
                                    -default   => $problem,
                                    -size      => 50,
                                    -maxlength => 50))
            ),
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Call no'),
              ($callid <= 0 && $$vendor[13]) ?
                  $q->td( { -class => 'low' }, "Automatically generated")
                  :
                  $q->td( $q->textfield(-name      => 'callnr',
                                        -default   => $callnr,
                                        -size      => 20,
                                        -maxlength => 20))
            ),
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Status'),
              $q->td( { -class => 'white' },
                      $q->radio_group(-name      => 'status',
                                      -values    => \@status,
                                      -default   => $status,
                                      -linebreak => 'true',
                                      -labels    => \%status))
            ),
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Priority'),
              $q->td( { -class => 'white' },
                      $q->radio_group(-name      => 'priority',
                                      -values    => \@priority,
                                      -default   => $priority,
                                      -linebreak => 'true',
                                      -labels    => \%priority))
            ),
            $q->Tr(
              $q->td( { -valign => 'center' }, 'Note'),
              $q->td( $q->textarea(-name=>'note',
                                   -rows      => 5,
                                   -columns   => 50,
                                   -maxlength => 4000))
            )
          ),
          $q->p,
          $q->submit( { -name    => 'Cancel',
                        -value   => 'Cancel',
                        -onClick => 'cancelled()' } ),
          $q->submit( { -name    => 'AddChange',
                        -value   => ($callid <= 0) ? 'Add' : 'Change' } ),
          $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub show_call
{
    my ($q, $user) = @_;

    my $callid = $q->param('callid');
    if (not $callid)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->startform,
              $q->hidden(-name => 'frame',  -default => 'detail'),
              $q->hidden(-name => 'callid', -default => '1'),
              $q->endform,
              $q->end_html;

        return 1;
    }
    if ($q->param('new_problem'))
    {
        $dbh->do("insert into callhist values($callid, " . time . ", '$user'," .
                                              $dbh->quote($q->param('new_problem')) . ")");
        $dbh->commit;
    }
    $q->param( -name => 'new_problem', -value => '' );

    my $calls = $dbh->selectall_arrayref("select * from calls where callid = $callid");
    my $call = $$calls[0]; 
    if (not $call)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->startform,
              $q->hidden(-name => 'frame',  -default => 'detail'),
              $q->hidden(-name => 'callid', -default => '1'),
              $q->b('Call not found in the database, has it been removed?'),
              $q->endform,
              $q->end_html;

        return 1;
    }
    my $callitems = $dbh->selectall_arrayref("select * from callitem where callid = $callid");
    my $callitem = $$callitems[0];
    my $vendorid = $$callitem[0];
    my @itemids;
    foreach $callitem (@$callitems) { push @itemids, $$callitem[1]; }
    my $vendors = $dbh->selectall_arrayref("select vendorname from vendors where vendorid=$vendorid");
    my $vendor = $$vendors[0];
    $vendor =~ s/</&lt;/g;
    my $items = $dbh->selectall_arrayref("select itemid, item
                                          from   vendoritems
                                          where  vendorid = $vendorid
                                          and    itemid in (" . join(",", @itemids). ")");
    my @items;
    foreach my $item (@$items) { push @items, $$item[1]; }

    my $history = $dbh->selectall_arrayref("select * from callhist where callid = $callid order by updtime");
    my $rows;
    foreach my $hist (@$history)
    {
        my $note = $$hist[3];
        $note =~ s/</&lt;/g;
        $note =~ s/\n/<br>/g;
        $note =~ s/\[b\](.*?)\[\/b\]/<b>$1<\/b>/g;
        $note =~ s/\[u\](.*?)\[\/u\]/<u>$1<\/u>/g;
        $note =~ s/\[i\](.*?)\[\/i\]/<i>$1<\/i>/g;
        $note =~ s/\[p\](.*?)\[\/p\]/<pre>$1<\/pre>/g;

        $rows .= $q->Tr(
                   $q->td({ -valign => 'top', -class => 'low' }, mkdate($$hist[1])),
                   $q->td({ -valign => 'top', -class => 'low' }, $$hist[2]),
                   $q->td({ -valign => 'top', -class => 'white', -width => '100%' }, $note)
                 );
    }

    my $script = '
       function add_problem()
       {
           re = /["-~]/
           if (re.test(document.forms[0].new_problem.value))
           {
               document.forms[0].submit()
           }
           else
           {
               alert("There is no text in the note")
           }
       }';

    my $back = $q->param("searchlist");
    my $back2 = $back if $back;
    $back2 =~ s/~/&/g if $back;
    $back2 =~ s/\^/=/g if $back;

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Calls',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->startform,
          $q->hidden(-name => 'frame',  -default => 'items'),
          $q->hidden(-name => 'callid', -default => $callid),
          $q->hidden(-name => 'vendorid', -default => $vendorid),
          ($back ? $q->hidden(-name => 'searchlist', -default => $back) : ""),
          ($back ? ($q->table( { -border => 0, -width => '100%' },
                      $q->Tr($q->td( { -align => 'right' },
                                     $q->a( { -href => $q->url() . "?$back2" }, "Back to search list")))))
                 : ""),
          $q->table( { -width => '100%', -cellspacing => '0', -cellpadding => '0' },
            $q->Tr(
              $q->td( { -width => '100%', -valign => 'center' },
                      $q->h1($q->p( { -style => 'color: red' }, 'Call no: ' . $q->escapeHTML($$call[3])))),
              $q->td( { -align => 'right' },
                      $q->a( { -href => $q->url() . "?frame=detail&nc_call=$callid&vendorid=$vendorid" .
                                                    ($back ? "&searchlist=$back" : "") }, "Edit"),
                      $q->br,
                      $q->a( { -href => $q->url() . "?printable=1&callid=$callid", -target => '_blank' },
                             "Printable"))
            ),
            $q->Tr(
              $q->td( { colspan => 2, -class => 'white', -width => '100%' },
                      $q->big($q->b($q->escapeHTML($$call[4])))),
            )
          ),
          $q->hr,
          $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign  => 'top' }, 'Status'),
              $q->td( { -class => 'white' }, $status{$$call[2]}),
              $q->td( { -valign  => 'top' }, 'Vendor'),
              $q->td( { -class => 'white' }, $vendor)
            ),
            $q->Tr(
              $q->td( { -valign  => 'top' }, 'Priority'),
              $q->td( { -class => 'white' }, $priority{$$call[1]}),
              $q->td( { -valign  => 'top' }, 'Product(s)'),
              $q->td( { -class => 'white' }, join(", ", @items))
            ),
            $q->Tr(
              $q->td( { -valign  => 'top' }, 'Created on'),
              $q->td( { -class => 'white' }, mkdate($$call[5])),
              $q->td( { -valign  => 'top' }, 'Created by'),
              $q->td( { -class => 'white' }, $$call[6])
            )
          ),
          $q->hr,
          $q->table( { -width => '100%', -cellspacing => '0', -cellpadding => '0' },
            $q->Tr( $q->td($q->b('Call history')))
          ),
          $q->table( { -width => '100%' }, $rows),
          $q->hr,
          $q->textarea(-name=>'new_problem',
                       -rows      => 5,
                       -columns   => 50,
                       -maxlength => 4000),
          $q->br,
          $q->a( { -href => "javascript:add_problem()" }, "Add note"),
          $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub show_item
{
    my ($q, $user) = @_;

    my $vendorid = $q->param('vendorid');
    my $itemid = $q->param('show_item');
    my $items = $dbh->selectall_arrayref("select * from vendoritems where vendorid=$vendorid and itemid=$itemid");
    my $item = $$items[0];
    if (not $item)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->startform,
              $q->hidden(-name => 'frame',  -default => 'detail'),
              $q->b('Product not found in the database, has it been removed?'),
              $q->endform,
              $q->end_html;

        return 1;
    }

    my $vendors = $dbh->selectall_arrayref("select vendorname from vendors where vendorid=$vendorid");
    my $vendor = $$vendors[0];

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Product details',
                         -vlink   => '#0000ff',
                         -style   => $style_right),
          $q->h1($$item[2]),
          $q->table({ -border => '0', -width => '100%' },
             $q->Tr(
               $q->td('Vendor'),
               $q->td({ -class => 'white'}, $q->escapeHTML($$vendor[0]))
             ),
             $q->Tr(
               $q->td('Product'),
               $q->td({ -class => 'white'}, $q->escapeHTML($$item[2]))
             ),
             $q->Tr(
               $q->td('Contract nr'),
               $q->td({ -class => 'white'}, ($$item[3]) ? $q->escapeHTML($$item[3]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('License nr'),
               $q->td({ -class => 'white'}, ($$item[4]) ? $q->escapeHTML($$item[4]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Description'),
               $q->td({ -class => 'white'}, ($$item[5]) ? $q->escapeHTML($$item[5]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Changed by'),
               $q->td({ -class => 'white'}, ($$item[6]) ? $$item[6] : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Changed at'),
               $q->td({ -class => 'white'}, ($$item[7]) ? mkdate($$item[7]) : "&nbsp;")
             )
          ),
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub show_vendor
{
    my ($q, $user) = shift;

    my $vendorid = $q->param('show_vendor');
    my $vendors = $dbh->selectall_arrayref("select * from vendors where vendorid=$vendorid");
    my $vendor = $$vendors[0];
    if (not $vendor)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->startform,
              $q->hidden(-name => 'frame',  -default => 'detail'),
              $q->b('Vendor not found in the database, has it been removed?'),
              $q->endform,
              $q->end_html;

        return 1;
    }

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Vendor details',
                         -vlink   => '#0000ff',
                         -style   => $style_right),
          $q->h1($$vendor[1]),
          $q->table({ -border => '0', -width => '100%' },
             $q->Tr(
               $q->td('Vendor'),
               $q->td({ -class => 'white'}, $q->escapeHTML($$vendor[1])),
             ),
             $q->Tr(
               $q->td('Tel'),
               $q->td({ -class => 'white'}, ($$vendor[2]) ? $q->escapeHTML($$vendor[2]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('Fax'),
               $q->td({ -class => 'white'}, ($$vendor[3]) ? $q->escapeHTML($$vendor[3]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('Email'),
               $q->td({ -class => 'white'}, ($$vendor[4]) ? $q->escapeHTML($$vendor[4]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('Address'),
               $q->td({ -class => 'white'}, ($$vendor[5]) ? $q->escapeHTML($$vendor[5]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('Zipcode'),
               $q->td({ -class => 'white'}, ($$vendor[6]) ? $q->escapeHTML($$vendor[6]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('City'),
               $q->td({ -class => 'white'}, ($$vendor[7]) ? $q->escapeHTML($$vendor[7]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('State'),
               $q->td({ -class => 'white'}, ($$vendor[8]) ? $q->escapeHTML($$vendor[8]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td('Country'),
               $q->td({ -class => 'white'}, ($$vendor[9]) ? $q->escapeHTML($$vendor[9]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Description'),
               $q->td({ -class => 'white'}, ($$vendor[10]) ? $q->escapeHTML($$vendor[10]) : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Changed by'),
               $q->td({ -class => 'white'}, ($$vendor[11]) ? $$vendor[11] : "&nbsp;")
             ),
             $q->Tr(
               $q->td({ -valign => 'top'}, 'Changed at'),
               $q->td({ -class => 'white'}, ($$vendor[12]) ? mkdate($$vendor[12]) : "&nbsp;")
             )
          ),
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub search
{
    my ($q, $user) = shift;

    my $vendorid = $q->param('vendorid'); $vendorid = 0 if not $vendorid;
    my $itemid = $q->param('itemid'); $itemid = 0 if not $itemid;
    my $datefrom = $q->param('datefrom'); $datefrom = '' if not $datefrom;
    my $dateto = $q->param('dateto'); $dateto = '' if not $dateto;
    my @c_status = $q->param('c_status');
    my @c_prio = $q->param('c_prio');
    my $callnr = $q->param('callnr');
    $callnr =~ s/__Slinger__/~/g if $callnr;
    $callnr =~ s/__Dakje__/^/g if $callnr;
    my $problem = $q->param('problem');
    $problem =~ s/__Slinger__/~/g if $problem;
    $problem =~ s/__Dakje__/^/g if $problem;
    my $sort1 = $q->param('sort1');
    my $sort2 = $q->param('sort2');
    my $sort3 = $q->param('sort3');
    my $remove = $q->param('remove'); $remove = 0 if not $remove;

    my (@vendor_ids, %vendor_names);
    @vendor_ids = ('0');
    %vendor_names = ( '0' => '<any vendor>' );
    my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
    foreach my $vendor (@$vendors)
    {
        push @vendor_ids, $$vendor[0];
        $vendor_names{$$vendor[0]} =  $$vendor[1];
    }
    my $vbutton = $q->popup_menu(-name     => 'vendorid',
                                 -values   => \@vendor_ids, 
                                 -labels   => \%vendor_names,
                                 -onChange => 'javascript:document.forms[0].submit()',
                                 -default  => $vendorid);

    my (@item_ids, %item_names);
    my $ibutton = "";
    if ($vendorid)
    {
        @item_ids = ('0');
        %item_names = ( '0' => '<any product>' );
        my $items = $dbh->selectall_arrayref("select itemid, item from vendoritems where vendorid = $vendorid");
        foreach my $item (@$items)
        {
            push @item_ids, $$item[0];
            $item_names{$$item[0]} =  $$item[1];
        }
        $ibutton = $q->popup_menu(-name     => 'itemid',
                                  -values   => \@item_ids, 
                                  -labels   => \%item_names,
                                  -default  => $itemid);
    }

    if ($q->param('do_search'))
    {
        my $calls;
        my $check_all = $q->param('check_all');
        my $vendor_and = $vendorid ? "and vendorid = $vendorid" : "";
        my $item_and = $itemid ? "and itemid = $itemid" : "";
        my $esc_callnr = $callnr;
        $esc_callnr =~ s/'/''/g;
        my $callnr_and = $callnr ? ("and callnr like '%$esc_callnr%'") : "";
        my $esc_problem = $problem;
        $esc_problem =~ s/'/''/g;
        my $problem_and = $problem ? ("and problem like '%$esc_problem%'") : "";
        my $prio_and = @c_prio ? ("and priority in (" . join(",", @c_prio) . ")") : "";
        my $status_and = @c_status ? ("and status in (" . join(",", @c_status) . ")") : "";
        my $from_and = "";
        my $to_and = "";
        if ($datefrom && $datefrom =~ /(\d{4})\/(\d{1,2})\/(\d{1,2})/)
        {
            if ($1 >= 2000 && $1 < 2036 && $2 > 0 && $2 <= 12 && $3 > 0 && $3 <= 31)
            {
                $from_and = "and logtime >= " . timegm(0, 0, 0, $3, $2 - 1, $1);
            }
        }
        if ($dateto && $dateto =~ /(\d{4})\/(\d{1,2})\/(\d{1,2})/)
        {
            if ($1 >= 2000 && $1 < 2036 && $2 > 0 && $2 <= 12 && $3 > 0 && $3 <= 31)
            {
                $to_and = "and logtime <= " . timegm(59, 59, 23, $3, $2 - 1, $1);
            }
        }

        my $order = "";
        foreach my $srt ($sort1, $sort2, $sort3)
        {
            $order = ($order ? "$order, $srt" : "order by $srt") if $srt;
        }
        $order = "order by calls.callid" if not $order;

        $calls = $dbh->selectall_arrayref("select distinct calls.callid, callnr, status, priority, problem, logtime,
                                                  vendorid
                                           from   calls,callitem
                                           where  calls.callid = callitem.callid
                                           $vendor_and $item_and
                                           $from_and $to_and
                                           $callnr_and $problem_and
                                           $prio_and $status_and
                                           $order");

        $esc_problem = $problem;
        $esc_problem =~ s/~/__Slinger__/g;
        $esc_problem =~ s/\^/__Dakje__/g;
        $esc_callnr = $callnr;
        $esc_callnr =~ s/~/__Slinger__/g;
        $esc_callnr =~ s/\^/__Dakje__/g;
        my $back = "frame=detail&aframe=detail&search=1" .
                   "&vendorid=$vendorid&itemid=$itemid&datefrom=$datefrom&dateto=$dateto" .
                   (@c_status ? "&c_status=" . join("&c_status=", @c_status) : "") .
                   (@c_prio ? "&c_prio=" . join("&c_prio=", @c_prio) : "") .
                   "&callnr=$esc_callnr&problem=$esc_problem&sort1=$sort1&sort2=$sort2&sort3=$sort3&remove=$remove";

        my $back2 = $back . "&do_search=1";
        $back2 =~ s/&/~/g;
        $back2 =~ s/=/^/g;

        my $scalls = "";
        my @calls = ();
        foreach my $call (@$calls)
        {
            push @calls, $$call[0];
            my $call_link = $remove ? $q->escapeHTML($$call[1]) :
                                      $q->a( { -href => $q->url . "?frame=detail&callid=$$call[0]" .
                                                        "&searchlist=$back2" } , $q->escapeHTML($$call[1]));
            my $del_cbox = $remove ? $q->td($q->checkbox( -name     => 'del_calls',
                                                          -checked  => $check_all ? 'checked' : '',
                                                          -value    => $$call[0],
                                                          -label    => '',
                                                          -override => 1)) : "";
            my $prio = $priority{$$call[3]};
            $prio = $q->u($prio) if $$call[3] == 1;
            $prio = $q->b($prio) if $$call[3] <= 2;
            $prio = $q->u($prio) if $$call[3] == 3;
            my $stat = $status{$$call[2]};
            $stat = $q->b($stat) if $$call[2] == 1;
            $stat = $q->u($stat) if $$call[2] == 2;

            $scalls .= $q->Tr( { -class => 'low' } ,
                               $del_cbox,
                               $q->td($call_link),
                               (!$vendorid ? $q->td($q->escapeHTML($vendor_names{$$call[6]})) : ""),
                               $q->td($stat),
                               $q->td($prio),
                               $q->td($q->escapeHTML($$call[4])),
                               $q->td(mkdate($$call[5])));
        }

        my $script = '
           function set_all()
           {
               document.forms[0].check_all.value = 1
               document.forms[0].search.value = 1
               document.forms[0].do_search.value = 1
               return 1
           }
           function unset_all()
           {
               document.forms[0].check_all.value = 0
               document.forms[0].do_search.value = 1
               document.forms[0].search.value = 1
               return 1
           }';

        print $q->header,
              $q->start_html(-title   => 'CallTrack - Search call',
                             -vlink   => '#0000ff',
                             -style   => $style_right,
                             -script  => $script),
              $q->startform,
              $q->hidden( -name => 'aframe', -value => 'detail'),
              $q->hidden( -name => 'search', -value => '0', -override => 1),
              $q->hidden( -name => 'do_search', -value => '0', -override => 1),
              $q->hidden( -name => 'remove', -value => '1'),
              $q->hidden( -name => 'check_all', -value => 0),
              $q->hidden( -name => 'searchlist', -value => $back2),
              $q->hidden( -name => 'vendorid', -value => $vendorid),
              $q->hidden( -name => 'itemid', -value => $itemid),
              $q->hidden( -name => 'datefrom', -value => $datefrom),
              $q->hidden( -name => 'dateto', -value => $dateto),
              $q->hidden( -name => 'callnr', -value => $callnr),
              $q->hidden( -name => 'problem', -value => $problem),
              @c_prio ? $q->hidden( -name => 'c_prio', -value => @c_prio) : "",
              @c_status ? $q->hidden( -name => 'c_status', -value => @c_status) : "",
              $q->hidden( -name => 'sort1', -value => $sort1),
              $q->hidden( -name => 'sort2', -value => $sort2),
              $q->hidden( -name => 'sort3', -value => $sort3),
              $q->table( { -border => 0, -width => '100%' }, 
                $q->Tr(
                  $q->td($q->big($q->big($q->b('Search results')))),
                  $q->td( { -align => 'right' },
                          $q->a( { -href => $q->url() . "?$back" }, "Back to search"),
                          $q->br,
                          $q->a( { -href => $q->url() . "?printable=1&$back&do_search=1", -target => "_blank" },
                                 "Printable")))),
              $q->p($q->b(($vendorid ? $q->escapeHTML($vendor_names{$vendorid}) : "&nbsp;") . 
                           ($itemid ? ", " . $q->escapeHTML($item_names{$itemid}) : ""))),
              $q->table( { -width => "100%" },
                $q->Tr(($remove ? $q->td( { -width => '1%' }, "&nbsp;") : ""),
                       $q->td($q->b('Callnr')),
                       (!$vendorid ? $q->td($q->b('Vendor')) : "" ),
                       $q->td($q->b('Status')), $q->td($q->b('Priority')),
                       $q->td($q->b('problem')),  $q->td($q->b('logtime'))),
                $scalls ),
              ($remove) ? ($q->submit(-name    => 'Select all',
                                      -onClick => 'javascript:set_all()') . 
                           $q->submit(-name    => 'Unselect all',
                                      -onClick => 'javascript:unset_all()') .
                           $q->submit(-name    => 'Remove')
                          ) : "",
              $q->endform,
              $q->end_html;

        return;
    }

    my $script = '
       function set_search()
       {
           document.forms[0].do_search.value = 1
           return 1
       }';

    my @sortorder = ('', 'logtime', 'status', 'priority', 'callnr', 'problem');
    my %sortorder = ( ''         => '',
                      'logtime'  => 'logtime',
                      'status'   => 'status',
                      'priority' => 'priority',
                      'callnr'   => 'callnr',
                      'problem'  => 'problem' );

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Search call',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  => $script),
          $q->h1('Search call'),
          $q->start_form,
          $q->hidden(-name => 'frame',   -default => 'detail'),
          $q->hidden(-name => 'aframe',  -default => 'detail'),
          $q->hidden(-name => 'search',  -default => '1'),
          $q->hidden(-name => 'remove',  -default => '0'),
          $q->hidden(-name => 'do_search',  -default => '0'),
          $q->table( { -border => '0' },
             $q->Tr(
               $q->td($vbutton),
               $q->td($ibutton)
             ),
             $q->Tr(
               $q->td($q->b('From date (yyyy/mm/dd)')),
               $q->td($q->b('To date (yyyy/mm/dd)'))
             ),
             $q->Tr(
               $q->td($q->textfield(-name      => 'datefrom',
                                    -size      => 12,
                                    -maxlength => 12,
                                    -default   => $datefrom)),
               $q->td($q->textfield(-name      => 'dateto',
                                    -size      => 12,
                                    -maxlength => 12,
                                    -default   => $dateto))
             ),
             $q->Tr(
               $q->td($q->b('Call no contains')),
               $q->td($q->b('Problem contains'))
             ),
             $q->Tr(
               $q->td($q->textfield(-name      => 'callnr',
                                    -size      => 12,
                                    -maxlength => 12,
                                    -default   => $callnr,
                                    -override  => 1)),
               $q->td($q->textfield(-name      => 'problem',
                                    -size      => 12,
                                    -maxlength => 12,
                                    -default   => $problem,
                                    -override  => 1))
             ),
             $q->Tr(
               $q->td($q->b('Status')),
               $q->td($q->b('Priority'))
             ),
             $q->Tr(
               $q->td($q->checkbox_group(-name      => 'c_status',
                                         -values    => \@status,
                                         -linebreak => 'true',
                                         -labels    => \%status,
                                         -default   => \@c_status)),
               $q->td($q->checkbox_group(-name      => 'c_prio',
                                         -values    => \@priority,
                                         -linebreak => 'true',
                                         -labels    => \%priority,
                                         -default   => \@c_prio)),
             ),
             $q->Tr(
               $q->td( $q->b("Sort by ")),
               $q->td( $q->popup_menu(-name     => 'sort1',
                                      -values   => \@sortorder, 
                                      -labels   => \%sortorder))
             ),
             $q->Tr(
               $q->td( $q->b("Then by ")),
               $q->td( $q->popup_menu(-name     => 'sort2',
                                      -values   => \@sortorder, 
                                      -labels   => \%sortorder))
             ),
             $q->Tr(
               $q->td( $q->b("Then by ")),
               $q->td( $q->popup_menu(-name     => 'sort3',
                                      -values   => \@sortorder, 
                                      -labels   => \%sortorder))
             )
          ),
          $q->p,
          $q->submit( { -name    => 'Search',
                        -value   => 'Search',
                        -onClick => 'set_search()' } ),
          $q->end_form,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub f_detail
{
    my ($q, $user) = @_;

    if ($q->param('nc_call'))
    { 
        nc_call($q, $user);
        return 1;
    }

    if ($q->param('show_vendor'))
    { 
        show_vendor($q, $user);
        return 1;
    }

    if ($q->param('show_item'))
    { 
        show_item($q, $user);
        return 1;
    }

    if ($q->param('search'))
    { 
        search($q, $user);
        return 1;
    }

    show_call($q, $user);
    return 1;
}

##################################################################
##################################################################
##################################################################

sub f_vendors
{
    my ($q, $user) = @_;

    my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
    my (@vendor_ids, %vendor_names);
    foreach my $vendor (@$vendors)
    {
        push @vendor_ids, $$vendor[0];
        $vendor_names{$$vendor[0]} =  $$vendor[1];
    }

    my $curr_vendor = $q->param('vendor');
    $curr_vendor = $vendor_ids[0] if not $curr_vendor;
    $curr_vendor = '' if not $curr_vendor;

##################################################################
##### JavaScript
##################################################################

    my $script = '
       function ch_button()
       {
           document.forms[0].vendor_state.value = "closed"
           document.forms[0].item_state.value = "closed"
           document.forms[0].call_state.value = "open"
           document.forms[0].hcall_state.value = "closed"
           document.forms[0].ccall_state.value = "closed"
           document.forms[0].submit()
       }
       function ch_state(which)
       {
           if (which.value == "open")
               which.value = "closed"
           else
               which.value = "open"
           document.forms[0].submit()
       }
       function selectvendor(vendor, itemid)
       {
           document.forms[0].vendor.value = vendor
           document.forms[0].submit()

           parent.frames["calls"].document.forms[0].vendor.value = vendor
           parent.frames["calls"].document.forms[0].item.value = ""
           parent.frames["calls"].document.forms[0].submit()
       }';

##################################################################
##### Header
##################################################################

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Vendors',
                         -vlink   => '#0000ff',
                         -style   => $style_left,
                         -script  => $script),
          $q->startform,
          $q->hidden(-name => 'frame',  -default => 'vendor'),
          $q->hidden(-name => 'vendor_state',  -default => 'closed'),
          $q->hidden(-name => 'item_state',    -default => 'closed'),
          $q->hidden(-name => 'call_state',    -default => 'open'),
          $q->hidden(-name => 'hcall_state',   -default => 'closed'),
          $q->hidden(-name => 'ccall_state',   -default => 'closed');

##################################################################
##### Vendor button
##################################################################

    my $button = $q->popup_menu(-name     => 'vendor',
                                -values   => \@vendor_ids, 
                                -labels   => \%vendor_names,
                                -onChange => 'javascript:ch_button()',
                                -default  => $curr_vendor);

    print $q->center($button),
          $q->br,
          $q->table( { -width => "100%" },
            $q->Tr(
              $q->td( $q->a( { -href => $q->url() . "?frame=detail&nc_call=-1&vendorid=$curr_vendor",
                               -target => "detail" }, 
                             "New call")),
              $q->td( { -align => "right" },
                      $q->a( { -href => $q->url() . "?frame=detail&search=1&vendorid=$curr_vendor",
                               -target => "detail" }, 
                             "Search call"))
            )
          ),
          $q->p;

##################################################################
##### Vendor info
##################################################################

    my $vnd_info = "";
    if ($curr_vendor)
    {
        my $vnd = $dbh->selectall_arrayref("select * from vendors where vendorid=$curr_vendor");
        $vnd = $$vnd[0];
        $vnd_info = $q->a({ -href   => $q->url() . "?frame=detail&show_vendor=" . $$vnd[0],
                            -target => 'detail' }, $q->escapeHTML($$vnd[1])) .
                      $q->table({ -border => '0', -width => '100%' },
                         ($$vnd[2])  ? $q->Tr(
                                         $q->td('Tel'),
                                         $q->td({ -class => 'white', -width => '100%' }, $q->escapeHTML($$vnd[2]))
                                       ) : "",
                         ($$vnd[3])  ? $q->Tr(
                                         $q->td('Fax'),
                                         $q->td({ -class => 'white', -width => '100%' }, $q->escapeHTML($$vnd[3]))
                                       ) : "",
                         ($$vnd[4])  ? $q->Tr(
                                         $q->td('Email'),
                                         $q->td({ -class => 'white', -width => '100%' }, $q->escapeHTML($$vnd[4]))
                                       ) : ""
                      );

    }

    my $vendor_state = $q->param('vendor_state');
    $vendor_state = 'closed' if not $vendor_state;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].vendor_state)' },
                             $q->img( { -src => $q->url() . "?gif=$vendor_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, $q->b('Vendor information'), $q->br, 
                     ($vendor_state eq 'open') ? $vnd_info : '' )
            )
          );

##################################################################
##### Item info
##################################################################

    my $calls = $dbh->selectall_arrayref("
         select calls.callnr,calls.priority,calls.problem,calls.callid,callitem.itemid
         from   callitem, calls
         where  callitem.vendorid = " . ($curr_vendor ? $curr_vendor : 0) . "
         and    callitem.callid = calls.callid
         and    calls.status = 1
         order  by callid,itemid");

    my $item_state = $q->param('item_state');
    $item_state = 'closed' if not $item_state;
    my $item_rows = '';
    my $item_hiddens = '';

    my $vendoritems = $dbh->selectall_arrayref("select itemid, item, contractnr, licensenr from vendoritems
                                                where vendorid = " . ($curr_vendor ? $curr_vendor : 0));

    if ($item_state eq 'open')
    {
        foreach my $item (@$vendoritems)
        {
            $item_hiddens .= $q->hidden ( { -name => "si_" . $$item[0], -default => "closed" } );

            my $call_rows = '';
            foreach my $call (@$calls)
            {
                if ($$call[4] == $$item[0])
                {
                    $call_rows .=
                      $q->Tr(
                        $q->td( { -colspan => 2, -valign => 'top', -class => 'white' },
                          $q->a({ -href => $q->url(). "?frame=detail&callid=" . $$call[3],
                                  -target => 'detail' }, $q->escapeHTML($$call[0]))),
                        $q->td( { -valign => 'top', -align => 'left', -class => 'white', -width => "100%" },
                                $q->escapeHTML($$call[2]))
                      )
                }
            }

            my $state = $q->param('si_' . $$item[0]);
            $state = 'closed' if not $state;
            $item_rows .=
              $q->table( { -width => '100%' },
                $q->Tr(
                  $q->td( { -valign => 'top' },
                            $q->a( { -href => 'javascript:ch_state(document.forms[0].si_' . $$item[0] . ')' },
                              $q->img( { -src => $q->url() . "?gif=$state.gif", -border => '0' } ))),
                  $q->td( { -width => '100%' },
                          $q->a({ -href => $q->url() . "?frame=detail&vendorid=$curr_vendor&show_item=" . $$item[0],
                                  -target => 'detail' }, $q->escapeHTML($$item[1]))),
                ) .
                (($state eq "open") ?
                  $q->Tr(
                    $q->td("&nbsp;"),
                    $q->td(
                      $q->table({ -border => '0', -width => '100%' },
                        (($$item[2]) ? $q->Tr(
                                         $q->td('Contract'),
                                         $q->td({ -class => 'white', -width => '100%' }, $q->escapeHTML($$item[2]))
                                       ) : ""),
                        (($$item[3]) ? $q->Tr(
                                         $q->td('License'),
                                         $q->td({ -class => 'white', -width => '100%' }, $q->escapeHTML($$item[3]))
                                       ) : "")
                      )
                    )
                  ) : "")
              ) . 
              (($state eq "open") ? $q->table( { -width => '100%' }, $call_rows) : '');
        }
    }

    print $item_hiddens,
          $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].item_state)' },
                             $q->img( { -src => $q->url() . "?gif=$item_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, $q->b('Product information'))
            ),
            ($item_state eq 'open') ?  $q->Tr( $q->td, $q->td( { -width=> '100%' }, $item_rows)) : ''
          );

##################################################################
##### Calls
##################################################################

    my ($call_rows, $prev_callid) = ('', 0);
    foreach my $call (@$calls)
    {
        if ($$call[3] != $prev_callid)
        {
            $call_rows .=
              $q->Tr(
                $q->td( { -class => 'white' },
                        $q->a({ -href => $q->url(). "?frame=detail&callid=" . $$call[3],
                                -target => 'detail' }, $q->escapeHTML($$call[0]))),
                $q->td( { -valign => 'top', -align => 'left', -class => 'white', -width => "100%" },
                        $q->escapeHTML($$call[2]))
              );
        }
        $prev_callid = $$call[3];
    }

    my $call_state = $q->param('call_state');
    $call_state = 'open' if not $call_state;

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].call_state)' },
                             $q->img( { -src => $q->url() . "?gif=$call_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, $q->b('Open calls'), $q->br,
                      ($call_state eq 'open') ? $q->table( { -width => "100%", -border => '0' }, $call_rows) : '' )
            )
          );

##################################################################
##### Held Calls
##################################################################

    my ($hcall_rows, $prev_hcallid) = ('', 0);
    my $hcall_state = $q->param('hcall_state');
    $hcall_state = 'closed' if not $hcall_state;

    if ($hcall_state eq 'open')
    {
        my $hcalls = $dbh->selectall_arrayref("
              select calls.callnr,calls.priority,calls.problem,calls.callid,callitem.itemid
              from   callitem, calls
              where  callitem.vendorid = " . ($curr_vendor ? $curr_vendor : 0) . "
              and    callitem.callid = calls.callid
              and    calls.status = 2
              order  by callid,itemid");

        foreach my $call (@$hcalls)
        {
            if ($$call[3] != $prev_hcallid)
            {
                $hcall_rows .=
                  $q->Tr(
                    $q->td( { -class => 'white' },
                            $q->a({ -href => $q->url(). "?frame=detail&callid=" . $$call[3],
                                    -target => 'detail' }, $q->escapeHTML($$call[0]))),
                    $q->td( { -valign => 'top', -align => 'left', -class => 'white', -width => "100%" },
                            $q->escapeHTML($$call[2]))
                  );
            }
            $prev_hcallid = $$call[3];
        }
    }

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].hcall_state)' },
                             $q->img( { -src => $q->url() . "?gif=$hcall_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, $q->b('Calls on hold'), $q->br,
                      ($hcall_state eq 'open') ? $q->table( { -width => "100%", -border => '0' }, $hcall_rows) : '' )
            )
          );

##################################################################
##### Closed Calls
##################################################################

    my $ccall_state = $q->param('ccall_state');
    $ccall_state = 'closed' if not $ccall_state;
    my ($ccall_rows, $prev_ccallid) = ('', 0);

    if ($ccall_state eq "open")
    {
        my $ccalls = $dbh->selectall_arrayref("
              select calls.callnr,calls.priority,calls.problem,calls.callid,callitem.itemid
              from   callitem, calls
              where  callitem.vendorid = " . ($curr_vendor ? $curr_vendor : 0) . "
              and    callitem.callid = calls.callid
              and    calls.status = 3
              order  by callid,itemid");

        my $cnt = 0;
        foreach my $call (@$ccalls)
        {
            if ($$call[3] != $prev_ccallid)
            {
                last if $cnt++ >= 25;

                $ccall_rows .=
                  $q->Tr(
                    $q->td( { -class => 'white' },
                            $q->a({ -href => $q->url(). "?frame=detail&callid=" . $q->escapeHTML($$call[3]),
                                    -target => 'detail' }, $$call[0])),
                    $q->td( { -valign => 'top', -align => 'left', -class => 'white', -width => "100%" },
                            $q->escapeHTML($$call[2]))
                  );
            }
            $prev_ccallid = $$call[3];
        }
    }

    print $q->table( { -width => '100%' },
            $q->Tr(
              $q->td( { -valign => 'top' },
                      $q->a( { -href => 'javascript:ch_state(document.forms[0].ccall_state)' },
                             $q->img( { -src => $q->url() . "?gif=$ccall_state.gif", -border => '0' } ))),
              $q->td( { -width => '100%' }, $q->b('Closed calls'), $q->br,
                      ($ccall_state eq 'open') ? $q->table( { -width => "100%", -border => '0' }, $ccall_rows) : '' )
            )
          );

    print $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub f_header
{
    my ($q, $user) = @_;
    my ($usr, $role);

    $usr = $dbh->selectall_arrayref("select role from ct_users where userid='$user'");
    $usr = $$usr[0] if $usr;
    $role = $$usr[0] if $usr;
    $role = "user" if not $role;
    
    my $script = '
       function open_admin()
       {
           window.open("' . $q->url() . '?aframes=1", "ctadmin");
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Header',
                         -vlink   => '#0000ff',
                         -style   => $style_top,
                         -script  => $script),
          $q->table( { -width => '100%', -border => '0', -cellspacing => '0', -cellpadding => '0' },
            $q->Tr(
              $q->td( { -align => 'center', -width => '33%' }, $q->big($q->b('CallTrack'))),
              $q->td( { -align => 'center', -width => '33%' }, 
                      $q->a( { -href => 'http://www.sysman.nl', -target => "_top" } , 
                        $q->img( { -src => $q->url() . "?gif=sysman.gif", -border => 0 } ))
                      ),
              $q->td( { -align => 'center', -width => '33%' },
                        $q->big($q->b($q->a( { -href => $q->url() . "?state=1", -target => "_top" }, 'Login'))),
                        ($role eq "admin") ? $q->br : "",
                        ($role eq "admin") ? $q->big($q->b($q->a( { -href   => "javascript:open_admin()" },
                                                                  'Admin' ))) : "")
            )
          ),
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub do_frame
{
    my $q = shift;
    my $cookie = $q->cookie(-name => 'CallTrack');
    $cookie = "<unknown>" if not $cookie;
    my $user = $sessions{$cookie};
    my $frame_ok = 0;
    my $frame = $q->param('frame');

    if (! $user)
    {
        &not_logged_in($q);
        return;
    }

    (f_header($q, $user)  && ($frame_ok = 1)) if $frame eq "header";
    (f_vendors($q, $user) && ($frame_ok = 1)) if $frame eq "vendors";
    (f_detail($q, $user)  && ($frame_ok = 1)) if $frame eq "detail";

    if (! $frame_ok)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Unknown',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              "Unknown frame name?",
              $q->end_html;
    }
}

##################################################################
##################################################################
##################################################################

sub frames
{
    my $q=shift;
    my $f_header  = $q->url()."?frame=header";
    my $f_vendors = $q->url()."?frame=vendors";
    my $f_detail  = $q->url()."?frame=detail";

    print $q->header,
          "<html>\n",
          "<frameset rows='70,*' frameborder='0' marginheigt='0' marginwidth='0'>\n",
              "<frame src='$f_header' name='header'>\n",
              "<frameset cols='37%,*' frameborder=1>\n",
                  "<frame src='$f_vendors' name='vendors'>\n",
                  "<frame src='$f_detail' name='detail'>\n",
                  "</frameset>\n",
              "</frameset>\n",
          "</frameset>\n",
          "</html>";
}

##################################################################
##################################################################
##################################################################

sub show_printable_slist
{
    my ($q, $user) = @_;

    my $do_search = $q->param('do_search');
    if (not $do_search)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->b('No search list?'),
              $q->end_html;

        return 1;
    }

    my $vendorid = $q->param('vendorid'); $vendorid = '0' if not $vendorid;
    my $itemid = $q->param('itemid'); $itemid = '0' if not $itemid;
    my $datefrom = $q->param('datefrom'); $datefrom = '<any>' if not $datefrom;
    my $dateto = $q->param('dateto'); $dateto = '<any>' if not $dateto;
    my @c_status = $q->param('c_status');
    my $c_status = '<any>';
    if (@c_status)
    {
        my @c_st;
        foreach my $s (@c_status) { push @c_st, $status{$s}; }
        $c_status = join(", ", @c_st);
    }
    my @c_prio = $q->param('c_prio');
    my $c_prio = '<any>';
    if (@c_prio)
    {
        my @c_pr;
        foreach my $p (@c_prio) { push @c_pr, $priority{$p}; }
        $c_prio = join(", ", @c_pr);
    }
    my $callnr = $q->param('callnr');
    $callnr =~ s/__Slinger__/~/g if $callnr;
    $callnr =~ s/__Dakje__/^/g if $callnr;
    my $problem = $q->param('problem');
    $problem =~ s/__Slinger__/~/g if $problem;
    $problem =~ s/__Dakje__/^/g if $problem;
    my @sort = ();
    my $sort1 = $q->param('sort1'); push(@sort, $sort1) if $sort1;
    my $sort2 = $q->param('sort2'); push(@sort, $sort2) if $sort2;
    my $sort3 = $q->param('sort3'); push(@sort, $sort3) if $sort3;

    my (@vendor_ids, %vendor_names);
    @vendor_ids = ('0');
    %vendor_names = ( '0' => '<any>' );
    my $vendors = $dbh->selectall_arrayref('select vendorid, vendorname from vendors');
    foreach my $vendor (@$vendors)
    {
        push @vendor_ids, $$vendor[0];
        $vendor_names{$$vendor[0]} =  $$vendor[1];
    }

    my (@item_ids, %item_names);
    @item_ids = ('0');
    %item_names = ( '0' => '<any>' );
    if ($vendorid)
    {
        my $items = $dbh->selectall_arrayref("select itemid, item from vendoritems where vendorid = $vendorid");
        foreach my $item (@$items)
        {
            push @item_ids, $$item[0];
            $item_names{$$item[0]} =  $$item[1];
        }
    }

    my $calls;
    my $vendor_and = $vendorid ? "and vendorid = $vendorid" : "";
    my $item_and = $itemid ? "and itemid = $itemid" : "";
    my $esc_callnr = $callnr;
    $esc_callnr =~ s/'/''/g;
    my $callnr_and = $callnr ? ("and callnr like '%$esc_callnr%'") : "";
    my $esc_problem = $problem;
    $esc_problem =~ s/'/''/g;
    my $problem_and = $problem ? ("and problem like '%$esc_problem%'") : "";
    my $prio_and = @c_prio ? ("and priority in (" . join(",", @c_prio) . ")") : "";
    my $status_and = @c_status ? ("and status in (" . join(",", @c_status) . ")") : "";
    my $from_and = "";
    if ($datefrom && $datefrom =~ /(\d{4})\/(\d{1,2})\/(\d{1,2})/)
    {
        if ($1 >= 2000 && $1 < 2036 && $2 > 0 && $2 <= 12 && $3 > 0 && $3 <= 31)
        {
            $from_and = "and logtime >= " . timegm(0, 0, 0, $3, $2 - 1, $1);
        }
    }
    my $to_and = "";
    if ($dateto && $dateto =~ /(\d{4})\/(\d{1,2})\/(\d{1,2})/)
    {
        if ($1 >= 2000 && $1 < 2036 && $2 > 0 && $2 <= 12 && $3 > 0 && $3 <= 31)
        {
            $to_and = "and logtime <= " . timegm(59, 59, 23, $3, $2 - 1, $1);
        }
    }

    my $order = "";
    foreach my $srt ($sort1, $sort2, $sort3)
    {
        $order = ($order ? "$order, $srt" : "order by $srt") if $srt;
    }
    $order = "order by calls.callid" if not $order;

    $calls = $dbh->selectall_arrayref("select distinct calls.callid, callnr, status, priority,
                                                       problem, logtime, vendorid
                                       from   calls,callitem
                                       where  calls.callid = callitem.callid
                                       $vendor_and $item_and
                                       $from_and $to_and
                                       $callnr_and $problem_and
                                       $prio_and $status_and
                                       $order");

    my $scalls = "";
    foreach my $call (@$calls)
    {
        my $prio = $priority{$$call[3]};
        $prio = $q->u($prio) if $$call[3] == 1;
        $prio = $q->b($prio) if $$call[3] <= 2;
        $prio = $q->u($prio) if $$call[3] == 3;
        my $stat = $status{$$call[2]};
        $stat = $q->b($stat) if $$call[2] == 1;
        $stat = $q->u($stat) if $$call[2] == 2;

        $scalls .= $q->Tr( $q->td($q->escapeHTML($$call[1])),
                           (!$vendorid ? $q->td($q->escapeHTML($vendor_names{$$call[6]})) : ""),
                           $q->td($stat),
                           $q->td($prio),
                           $q->td($q->escapeHTML($$call[4])),
                           $q->td(mkdate($$call[5])));
    }

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Search call'),
          $q->table( { -width => '100%', -border => 0 },
                     $q->Tr(
                       $q->td($q->small('Print date: ' . mkdate(time))),
                       $q->td( { -align => 'right' }, $q->small('Calltrack'))
                     )
          ),
          $q->hr,
          $q->h1('Search criteria'),
          $q->p,
          $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5', -width => '100%' },
            $q->Tr(
              $q->td( { -width => '50%' },
                $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5' },
                  $q->Tr(
                    $q->td( { -valign  => 'top', }, 'Vendor:'),
                    $q->td( $q->b($q->escapeHTML($vendor_names{$vendorid})))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Product:'),
                    $q->td( $q->b($q->escapeHTML($item_names{$itemid})))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'From date:'),
                    $q->td( $q->b($q->escapeHTML($datefrom)))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'To date:'),
                    $q->td( $q->b($q->escapeHTML($dateto)))
                  )
                )
              ),
              $q->td(
                $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5' },
                  $q->Tr(
                    $q->td( { -valign  => 'top', }, 'Status:'),
                    $q->td( $q->b($q->escapeHTML($c_status)))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Priority:'),
                    $q->td( $q->b($q->escapeHTML($c_prio)))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Problem contains:'),
                    $q->td( $q->b($q->escapeHTML($problem)))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Callnr contains:'),
                    $q->td( $q->b($q->escapeHTML($callnr)))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Sort order:'),
                    $q->td( $q->b(join(", ", @sort)))
                  )
                )
              )
            )
          ),
          $q->hr,
          $q->h1('Search results'),
          $q->p,
          $q->table( { -width => '100%', -border => '1', -cellspacing => '0', -cellpadding => '5' },
                     $q->Tr($q->td($q->b('Callnr')),
                            (!$vendorid ? $q->td($q->b('Vendor')) : "" ),
                            $q->td($q->b('Status')), $q->td($q->b('Priority')),
                            $q->td($q->b('problem')),  $q->td($q->b('logtime')),
                           ),
                     $scalls ),
          $q->end_html;
}

#
########
#

sub show_printable_call
{
    my ($q, $user) = @_;

    my $callid = $q->param('callid');
    if (not $callid)
    {
        print $q->header,
              $q->start_html(-title   => 'CallTrack - Call',
                             -vlink   => '#0000ff',
                             -style   => $style_right),
              $q->b('No call number?'),
              $q->end_html;

        return 1;
    }

    my $calls = $dbh->selectall_arrayref("select * from calls where callid = $callid");
    my $call = $$calls[0]; 
    my $callitems = $dbh->selectall_arrayref("select * from callitem where callid = $callid");
    my $callitem = $$callitems[0];
    my $vendorid = $$callitem[0];
    my @itemids;
    foreach $callitem (@$callitems) { push @itemids, $$callitem[1]; }
    my $vendors = $dbh->selectall_arrayref("select vendorname from vendors where vendorid=$vendorid");
    my $vendor = $$vendors[0];
    my $items = $dbh->selectall_arrayref("select itemid, item
                                          from   vendoritems
                                          where  vendorid = $vendorid
                                          and    itemid in (" . join(",", @itemids). ")");
    my @items;
    foreach my $item (@$items) { push @items, $$item[1]; }

    my $history = $dbh->selectall_arrayref("select * from callhist where callid = $callid order by updtime");
    my $rows;
    foreach my $hist (@$history)
    {
        my $note = $$hist[3];
        $note =~ s/</&lt;/g;
        $note =~ s/\n/<br>/g;
        $note =~ s/\[b\](.*?)\[\/b\]/<b>$1<\/b>/g;
        $note =~ s/\[u\](.*?)\[\/u\]/<u>$1<\/u>/g;
        $note =~ s/\[i\](.*?)\[\/i\]/<i>$1<\/i>/g;
        $note =~ s/\[p\](.*?)\[\/p\]/<pre>$1<\/pre>/g;

        $rows .= $q->Tr(
                   $q->td({ -valign => 'top' }, mkdate($$hist[1])),
                   $q->td({ -valign => 'top' }, $$hist[2]),
                   $q->td({ -valign => 'top', -class => 'white', -width => '100%' }, $note)
                 );
    }

    print $q->header,
          $q->start_html(-title   => 'CallTrack - Calls',
                         -style   => $style_printable),
          $q->table( { -width => '100%', -border => 0 },
                     $q->Tr(
                       $q->td($q->small('Print date: ' . mkdate(time))),
                       $q->td( { -align => 'right' }, $q->small('Calltrack'))
                     )
          ),
          $q->table( { -width => '100%', -border => 5, -bgcolor => 'black', -cellpadding => 5 },
                     $q->Tr($q->td("<big><big><b>$$call[4]</b></big></big>"))),
          $q->p,
          $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5', -width => '100%' },
            $q->Tr(
              $q->td(
                $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5' },
                  $q->Tr(
                    $q->td($q->big('Call no: ')),
                    $q->td($q->b($q->escapeHTML($$call[3])))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top', }, 'Status:'),
                    $q->td( $q->b($status{$$call[2]}))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Priority:'),
                    $q->td( $q->b($priority{$$call[1]}))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Created on:'),
                    $q->td( $q->b(mkdate($$call[5])))
                  )
                )
              ),
              $q->td(
                $q->table( { -border => '0', -cellspacing => '0', -cellpadding => '5' },
                  $q->Tr(
                    $q->td( { -valign  => 'top', }, 'Vendor:'),
                    $q->td( $q->b($q->escapeHTML($$vendor[0])))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Product(s):'),
                    $q->td( $q->b($q->escapeHTML(join(", ", @items))))
                  ),
                  $q->Tr(
                    $q->td( { -valign  => 'top' }, 'Created by:'),
                    $q->td( $q->b($$call[6]))
                  )
                )
              )
            ),
          ),
          $q->p,
          $q->b('Call history'),
          $q->table( { -width => '100%', -border => '1', -cellspacing => '0', -cellpadding => '5' },
                     $rows),
          $q->end_html;
}

#
########
#

sub do_printable
{
    my $q = shift;
    my $cookie = $q->cookie(-name => 'CallTrack');
    $cookie = "<unknown>" if not $cookie;
    my $user = $sessions{$cookie};
    my $frame_ok = 0;
    my $frame = $q->param('frame');

    if (! $user)
    {
        &not_logged_in($q);
        return;
    }

    if ($q->param('callid'))
    {
        show_printable_call($q, $user);
    }

    if ($q->param('do_search'))
    {
        show_printable_slist($q, $user);
    }
}

##################################################################
##################################################################
##################################################################

sub loginerror
{
    my $q=shift;

    print $q->header,
          $q->start_html(-title   => 'CallTrack Loginfailure',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -target  => "_top"),
          $q->b("Login failed, try again"),
          $q->p,
          $q->a({href => $q->url() . "?status=1"} , "Login"),
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub not_logged_in
{
    my $q=shift;

    print $q->header,
          $q->start_html(-title   => 'CallTrack Not Logged In',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -target  => "_top"),
          $q->b("You are not logged in."),
          $q->p,
          $q->a({href => $q->url() . "?status=1"} , "Login"),
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub login
{
    my $q=shift;
    my $userid = $q->param("userid");
    my $password = $q->param("password");
    my $equal = 1;

    if ($q->param('newpwd'))
    {
        if ($q->param('newpassword1') && ($q->param('newpassword1') eq $q->param('newpassword2')))
        {
            my $pwd = crypt($q->param('newpassword1'),
                            join('', ('.', '/', 0..9, 'A'..'Z', 'a'..'z')[rand 64, rand 64]));
            $dbh->do("update ct_users set password='$pwd' where userid='$userid'");
            $dbh->commit;

            my $cookie = $q->cookie(-name => "CallTrack",
                                    -value => $q->param('ctID'),
                                    -expires => "+10h");

            $sessions{$q->param('ctID')} = $userid;
            print $q->redirect(-location => $q->url()."?state=2", -cookie => $cookie)
        }
        else
        {
            $equal = 0;
        }
    }
 
    if ($q->param('checklogin'))
    {
        my $result = $dbh->selectall_arrayref("select * from ct_users where  userid='$userid'");
        if (scalar(@$result) >= 1)
        {
            my $user = $$result[0];
            my $pwd = $$user[1];
            $pwd = "" if not $pwd;
            if (crypt($password, $pwd) eq $pwd)
            {
                my $cookie = $q->cookie(-name => "CallTrack",
                                        -value => $q->param('ctID'),
                                        -expires => "+10h");

                $sessions{$q->param('ctID')} = $userid;
                print $q->redirect(-location => $q->url()."?state=2", -cookie => $cookie)
                    if not $q->param('changepwd');
            }
            else
            {
                print $q->redirect(-location => $q->url()."?state=3");
            }
        }
        else
        {
            print $q->redirect(-location => $q->url()."?state=3");
        }
    }

    my $ctID=int(rand(10000000));

    my $script = '
       function do_submit()
       {
           if (document.forms[0].changepwd)
           {
               if (document.forms[0].newpassword1 &&
                   document.forms[0].newpassword1.value == "")
               {
                   alert("Empty password is not allowed")
                   return
               }
               if (document.forms[0].newpassword1 &&
                   document.forms[0].newpassword1.value != document.forms[0].newpassword2.value)
               {
                   alert("Passwords are not equal")
                   return
               }
           }
           document.forms[0].submit()
       }';

    print $q->header,
          $q->start_html(-title   => 'CallTrack Login',
                         -vlink   => '#0000ff',
                         -style   => $style_right,
                         -script  =>  $script),
          $q->h1("CallTrack login"),
          $q->startform;

    if ($q->param('changepwd'))
    {
        print($q->p($q->b('New password is empty, or passwords are not equal'))) if not $equal;
        print $q->h1("Change password for $userid"),
              $q->table(
                $q->Tr( $q->td("New password"),
                          $q->td($q->password_field( -name=>'newpassword1',
                                                         -size      => 15,
                                                         -maxlength => 15))),
                $q->Tr( $q->td("Retype new password"),
                          $q->td($q->password_field( -name=>'newpassword2',
                                                         -size      => 15,
                                                         -maxlength => 15))),
                $q->Tr( $q->td(),
                          $q->td($q->a( { -href => "javascript:self.do_submit()" }, "Change")))),
              $q->hidden(-name => 'changepwd', -default => '1'),
              $q->hidden(-name => 'newpwd', -default => '1'),
              $q->hidden(-name => 'userid', -default => $userid),
              $q->p;
    }
    else
    {
        print $q->table(
                $q->Tr( $q->td("Username"),
                          $q->td($q->textfield(-name=>'userid',
                                               -size      => 15,
                                               -maxlength => 15))),
                $q->Tr( $q->td("Password"),
                          $q->td($q->password_field( -name=>'password',
                                                     -size      => 15,
                                                     -maxlength => 15))),
                $q->Tr( $q->td("Change password?"),
                          $q->td($q->checkbox(-name    => 'changepwd',
                                              -checked => '',
                                              -value   => 'change',
                                              -label   => 'Change'))),
                $q->Tr( $q->td(),
                          $q->td($q->a( { -href => "javascript:self.do_submit()" }, "Login")))),
              $q->hidden(-name => 'checklogin', -default => '1'),
              $q->p;
    }

    print $q->img( { -src => $q->url() . "?gif=sysman.gif" } ),
          $q->hidden(-name => 'ctID',       -default =>  $ctID),
          $q->hidden(-name => 'state',      -default => '1'),
          $q->endform,
          $q->end_html;
}

##################################################################
##################################################################
##################################################################

sub do_cgi
{
    CGI::_reset_globals;

    my $q = new CGI;

    if (   $q->param('printable') )       { do_printable($q); return }
    if (   $q->param('aframes') )         { do_aframes($q); return }
    if (   $q->param('aframe') )          { do_aframe($q); return }
    if (   $q->param('frame') )           { do_frame($q); return }
    if (   $q->param('gif') )             { sendgif($q); return }
    if ( ! $q->param('state') )           { print $q->redirect($q->url()."?state=1"); return }
    if (   $q->param('state') eq "1" )    { login($q); return }
    if (   $q->param('state') eq "2" )    { frames($q); return }
    if (   $q->param('state') eq "3" )    { loginerror($q); return }

    print $q->redirect(-location => $q->url()."?state=1");
}

##################################################################
##################################################################
##################################################################

open (STDERR, ">>$CTDIR/logs/ctfe.log");

STDOUT->autoflush(1);
STDERR->autoflush(1);

logmsg "ctfe started\n";
getconfig;

while (1)
{
    my $ln = <STDIN>;
    next if $ln !~ /^helo/i;

    # If reading the data takes more than 30 seconds, timeout
    eval
    {
        local $SIG{ALRM} = sub { die "alarm\n" };
        alarm 30;

        do
        {
            # Create some environment variables as set by the web server
            chomp($ln = <STDIN>);
            if ($ln =~ /^CTENV /)
            {
                my $e = substr($ln, 6);
                $e =~ /(.*?)=(.*)/;
                my ($var, $val) = ($1, $2);
                $ENV{$var} = $val;
            }
        } while ($ln !~ /^CTENV_END/);

        do_cgi();
        print "__END__";

        alarm 0;
    };

    if ($@ eq "alarm\n")
    {
        logdie "Timeout while serving data - restart\n";
    }
    elsif ($@)
    {
        logmsg "Unknown error: $@";
    }
}
