wjl-0.26/changelog

v0.26
- Tiny Tweaks here and there..
- $wjl->day_stats() now takes a type as the first arguement.

v0.25
- Rewrite of the Include mechanism. Use WJLCore now instead of eval files.
- Resolve namespace conflict redirect now meta_redirect.
- Resolve namespace conflict button now form_button.
- Cleanup a couple of undeclared variables.
- Improve filter to handle ` chars.
- Fix job_valid so that jobids are clean to numerics with s/([^0-9])//g.
- Asorted filtering fixes to prevent bombing out on invalud user input.
- Code tested with mod_perl, speed improvement is enormous.
- Various minor clean ups which mod_perl was pointing at.

v0.24
- Various DHTML changes.
- Fix/Remove Text Block Formatting Code fmt().
- Add alert() function using javascript to main library.
- job_list.pl uses alert($jobid) for new jobs.
- stats.pl allows filtering based on status before user seletion.

v0.23
- Fix some internal filter bugs.
- Filter time inputs on gen_elapsed and gen_date to integers.
- Order Status is now actualy connected to something in job_list.pl.
- Fix edit_user.pl, so the title is Edit User not New User.
- Add a whole bunch of bounding checks with new cut() function.
- Add -f to rm line in src/inst script.
- Fix silly $job->due() bug.
- Fix nasty bug, where two jobs can appear to have the same jobid.
- Fix db install script bug.

v0.22
- Fix formatting to do with \n\r DHTML bug.
- Add the ability to edit User and Time of comments.
- Movement from Job List now carries Filter and Order parameters when return.
- Fix Bugs in workingseconds().
- Fix $job->due(), so that it is static from initial start of a job.
- Fix departments.pl so you can't add blank departments.
- Allow Filtering to unallocated Jobs.
- Add a $wjl->list_technicians(), so that Allocation, is only to techs.
- Add a Pseudo Live status on job list, which basically means status < 64.

v0.21
- General Clean Up.
- Install Script added.
- Alter WJL.pm to allow a user to have a 0 level.
- Add $job->due(), which give estimate due date.
- Alter Job Report to provide estimated completetion via $job->due().
- Installation Documentation.
- Fix an Atomic Bug in edit_job.pl.
- Fix Bug in find_knowledge() where it doesn't match on Job Description.

v0.20
- Fix Bug where stat_graph.pl header was for gif, but output is png.
- Fix Bug where IE file names are not handled right in knowledge_add.pl.
- Make Buttons use POST, instead of GET, for security...

v0.20-PRE
- Added Statistics (uses Graphs, need GD and GD::Graph modules).
- Added Working Time, so only working hours are counted when selected.
- Added Knowledge Based, this changes the DB structure, see db_patch.
- Knowledge base needs MIME::Base64.
- Feature Freeze now in effect.

v0.12 - Internal Release
- Added Path to Cookies, they now work... Finaly. I hope.

v0.11
- Add DHTML for IE users.
- Fix remove some unintentionaly global variable.
- Add "use strict".
- Cookies/Low security now works :).
- Add Changelog.
- Add a $job->comments() function.

v0.1
- Fix Joblist Filter Bug.
- Fix Edit Job not doing atomic properly for first time users.

v0.1-PRE
- Prelimary Release.

wjl-0.26/install

#!/usr/bin/perl

sub ok {
        print "OK\n";
}

sub md5sum($) {
        use Digest::MD5;
        my $string = $_[0];
        my $md5 = Digest::MD5->new;
        $md5->add($string);
        return($md5->hexdigest);
}

sub check_dependencies {
        print "\nChecking Dependencies\n";
        print "Digest::MD5\t";
        use Digest::MD5;
        ok();
        print "Mysql\t\t";
        use Mysql;
        ok();
        print "Time::Local\t";
        use Time::Local;
        ok();
        print "MIME::Base64\t";
        use MIME::Base64;
        ok();
        print "GD\t\t";
        use GD;
        ok();
        print "GD::Graph\t";
        use GD::Graph;
        ok();
        print "Dependency Check Complete\n";
}

sub ask($$) {
        my ($question,$dpath) = @_;
        print "$question [$dpath]:";
        my $answer = <STDIN>;
        chomp($answer);
        if($answer) {
                return $answer;
        } else {
                return $dpath;
        }
}

sub where($$) {
        my ($item,$dpath) = @_;
        my $question = "Where is your $item";
        return ask($question,$dpath);
}

sub filter_replace($$$$) {
        my ($infile,$outfile,$st,$et) = @_;
        my ($buffer,$data);
        #print "$infile -> $outfile, s/$st/$et/g\n";
        open(IN,$infile) || die $!;
        while($buffer = <IN>) {
                $data .= $buffer;
        }
        close(IN);
        $data =~ s/$st/$et/g;
        open(OUT,">$outfile") || die $!;
        print OUT $data;
        close(OUT);
}

sub clean {
        print "\nCleaning Old Files, please ignore errors.\n";
        unlink('db/sql_db');
        unlink('src/modules/WJLCore.pm');
}


print "Web Job Logs Installer\n";
if($ARGV[0] =~ /clean/) {
        clean();
        exit(0);
} else {
        check_dependencies();
        clean();
}
print "\n";
my $cgibin      = where('cgi-bin','/var/www/cgi-bin/wjl');
my $www         = where('www-root','/var/www/html');
my $perllib     = where('Perl modules directory','/usr/lib/perl5/5.6.0');
my $confdb      = ask('Configure Database','Yes');
my ($sqlroot,$adminuser,$adminpass);
if($confdb =~ /^Y/i) {
        #$sqlroot       = ask('What is your MySQL Root Password','');
        $adminuser      = ask('What username do you want your WJL admin to have','admin');
        $adminpass      = ask('What password should the WJL admin user have','changeme');
        $adminpass      = md5sum($adminpass);
} else {
        $confdb = 0;
}
my $sqluser     = ask('What name should the Database user have','wjl');
my $sqlpass     = ask('What password should the Database user have','changeme');
my $organisation= ask('What is the name of your organisation','noorg');

print "\nMangling Files\n";
if($confdb) {
        filter_replace('db/sql_db.in','db/sql_db','your_user',$sqluser);
        filter_replace('db/sql_db','db/sql_db','your_password',$sqlpass);
        filter_replace('db/sql_db','db/sql_db','admin_user',$adminuser);
        filter_replace('db/sql_db','db/sql_db','admin_password',$adminpass);
}
filter_replace('src/modules/WJLCore.pm.in','src/modules/WJLCore.pm','your_user',$sqluser);
filter_replace('src/modules/WJLCore.pm','src/modules/WJLCore.pm','your_password',$sqlpass);
filter_replace('src/modules/WJLCore.pm','src/modules/WJLCore.pm','your_organisation',$organisation);
if($ARGV[0] !~ /config/i) {
        if($confdb) {
                print "\nSetting Up MySQL Database\n";
                print "You maybe asked for your root password.\n";
                system('db/inst');
                print "If that worked you won't see any messages\n";
        }
        print "\nInstalling Files\n";
        system('src/inst',$cgibin,$www,$perllib);
}
print "Ok I think we are done\n";
exit(0);

wjl-0.26/db/sql_db.in

create database joblogs;
use joblogs;
create table job (
        jobid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        started BIGINT UNSIGNED NOT NULL,
        problem_started BIGINT UNSIGNED,
        caller CHAR(32) BINARY NOT NULL,
        dept CHAR(32) BINARY,
        priority SMALLINT UNSIGNED,
        username CHAR(32) BINARY NOT NULL,
        allocated_to CHAR(32) BINARY NOT NULL,
        PRIMARY KEY (jobid),
        UNIQUE jobid (jobid),
        INDEX (allocated_to)
);
create table event (
        eventid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        jobid BIGINT UNSIGNED NOT NULL,
        date BIGINT UNSIGNED NOT NULL,
        username CHAR(32) BINARY NOT NULL,
        type SMALLINT,
        status SMALLINT,
        data MEDIUMTEXT,
        PRIMARY KEY (eventid),
        UNIQUE eventid (eventid)
);
create table dept (
        dept CHAR(32) BINARY NOT NULL,
        dept_name VARCHAR(250),
        hod VARCHAR(250),
        phone CHAR(32) BINARY,
        PRIMARY KEY (dept),
        UNIQUE dept (dept)
);
create table user (
        username CHAR(32) BINARY NOT NULL,
        sectoken CHAR(32) BINARY NOT NULL,
        initials CHAR(4) BINARY,
        full_name VARCHAR(250) BINARY,
        level INT NOT NULL,
        PRIMARY KEY (username),
        UNIQUE username (username),
        INDEX (initials)
);
create table knowledge (
        jobid BIGINT UNSIGNED NOT NULL,
        name MEDIUMTEXT,
        description MEDIUMTEXT,
        data LONGBLOB
);
create table atomic (
        username CHAR(32) BINARY NOT NULL,
        time BIGINT UNSIGNED NOT NULL
);

use mysql;
INSERT INTO user VALUES(
        'localhost',
        'your_user',
        Password('your_password'),
        'Y','Y','Y','Y','Y','Y','Y',
        'Y','Y','Y','Y','Y','Y','Y'
);

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON joblogs.job TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.event TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.dept TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.user TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.knowledge TO csd@localhost IDENTIFIED BY 'your_password';

FLUSH PRIVILEGES;

use joblogs;
INSERT INTO user (username,sectoken,initials,full_name,level) VALUES ('admin_user','admin_password','AD','Admin User',255);

wjl-0.26/db/inst

#!/bin/sh

cat db/sql_db | /usr/bin/mysql -u root -p

wjl-0.26/src/www/wjl-style.css

BODY, TD {
        background: #3664A9;
        color : silver;
        font-family : "Tahoma";
        font-size: 14px;
        text-align: left;
}

H2 {
        font-size: 14pt;
}

H3 {
        font-size: 15pt;
        font-style: italic;
        font-family: sans-serif;
}

H4 {
    font-family: sans-serif;
    font-size: small;
}

A , A:Visited, A:Active{
        color: white;
        text-decoration : none;
        font-weight : bold;
}

A:Hover {
        color: #DAA520;
        text-decoration : none;
        font-weight : bold;
}

HR {
        height: 1pt;
}

wjl-0.26/src/modules/WJLCore.pm.in

# *-* Perl *-*

# Core Functions Include File for Web Logs Software
# WJL V0.26, Version 0.3
# Carl Ritson - 2002

# Define This Package
package WJLCore;

# Library Includes
require Digest::MD5;
require MIME::Base64;
require Time::Local;
require CGI;
require strict;

use vars qw($VERSION);
$VERSION = '0.3';

# Export Functions for pages to use.
require Exporter;
@ISA = qw(Exporter);
@EXPORT = (
        'organisation',
        'md5sum',
        'meta_redirect',
        'get_user_info',
        'user_valid',
        'user_level',
        'start_page',
        'db_error',
        'db_login',
        'form_button',
        'six_page',
        'frame6_page',
        'has_right',
        'set_right',
        'hidden_fields',
        'text_field',
        'n2month',
        'gen_date',
        'date2timestamp',
        'gen_elapsed',
        'select_field',
        'text_area',
        'submit_button',
        'hash2get',
        'status',
        'statusl',
        'priol',
        'priof',
        'filter',
        'radio_fields',
        'fmt',
        'popup_redirect',
        'job_valid',
        'event_type',
        'auth_fail_page',
        'dhtml_header',
        'dhtml_main',
        'dhtml_wrap',
        'isie',
        'mime',
        'carrier_encode',
        'carrier_decode',
        'cut',
        'alert',
        'set_sectoken'  
);

# Start of Subs

sub organisation {
        my $organisation = 'your_organisation';
        return $organisation;
}

# md5sum, takes string, returns Hex Digest of it, uses Digest::MD5.
sub md5sum($) {
        my $string = $_[0];
        my $md5 = Digest::MD5->new;
        $md5->add($string);
        return($md5->hexdigest);
}

# meta_redirect, takes URL , returns Generates Meta Redirect.
#               Use instead of using meta_redirect header.
sub meta_redirect($) {
        my $url = $_[0];
        return ('<meta http-equiv="refresh" content="0;URL='.$url.'">');
}

# center, returns html with in center tags.
sub center {
        my ($html) = join('',@_);
        return '<center>'.$html.'</center>';
}

# gets_user_info, requires a CGI object, returns a Hash.  Sends Page Header.
sub get_user_info {
        my ($page,$header_type) = @_;
        my %userinfo;
        if(!$header_type) {
                $header_type = 'text/html';
        }
        if ($page->param('security') =~ /high/) {
                $userinfo{username} = $page->param('username');
                $userinfo{sectoken} = $page->param('sectoken');
                $userinfo{security} = 'high';
                print CGI::header($header_type);
        } else {
                %userinfo = $page->cookie('wjl');
                #print header(-cookie=>$cookie);
                #print STDERR "raw: ".$page->raw_cookie()."\n";
                #print STDERR "cookie: $cookie\n";
                #%userinfo = split(/:/,$cookie);
                print CGI::header($header_type);
        }
        return %userinfo;
}

# user_valid, requires DBH, Username, Sectoken. Returns 1 If The User is Valid.
sub user_valid($$$) {
        my ($dbh, $username, $sectoken) = @_;
        my $query = "SELECT username FROM user WHERE username=\'$username\' AND sectoken=\'$sectoken\'";
        my $result = $dbh->query($query);
        if ($result->fetchrow()) {
                return 1;
        } else {
                return 0;
        }
}

# user_level, requires DBH, Username. Returns Users Level.
sub user_level($$) {
        my ($dbh, $username) = @_;
        my $query = "SELECT level FROM user WHERE username='$username'";
        my $result = $dbh->query($query);
        my @row = $result->fetchrow();
        return $row[0];
}       

# start_page, require CGI object and DBH, returns $result and %userinfo.
#               $result is 1 if the user was authenticated, etc.
sub start_page($$) {
        my ($page, $dbh) = @_;  
        my $result = 0;
        my %userinfo = get_user_info($page);
        if (user_valid($dbh,$userinfo{username},$userinfo{sectoken})) {
                $result = 1;
        }
        return ($result, %userinfo);
}

# db_error, take no arguments, returns void. Print DB Error Page.
sub db_error {
        print   CGI::header(),
                CGI::start_html("Database Error :("),
                CGI::h2("Sorry a Database Error has occured, please try again later"),
                CGI::end_html();
}

# db_login, take no arguments, returns an array for use with the Mysql->connect.
sub db_login {
        my @login_info;
        my $db_name     = "joblogs";
        my $db_host     = "localhost";
        my $db_user     = "your_user";
        my $db_password = "your_password";      
        push(@login_info,$db_host,$db_name,$db_user,$db_password);
        return(@login_info);
}

# form_button, takes name, target, %parm, returns form based html button
sub form_button($$%) {
        my ($name, $target, %parm) = @_;
        my $html = "";
        $html .= "<form action=\"$target\" METHOD=\"POST\">\n";
        foreach(keys(%parm)) {
                my $key = $_;
                $html .= "\t".'<input type="hidden" name="'.$key.'" value="'.$parm{$key}.'">'."\n";
        }
        $html .= submit_button($name);
        $html .= "</form>";
        return $html;
}

# six_page, takes 6 strings, returns them as tabled html 3x2.
sub six_page($$$$$$) {
        my ($tl,$tm,$tr,$bl,$bm,$br) = @_;
        my $table = '<table BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">';
        $table .= CGI::Tr(
                        '<td WIDTH="5%"></td>',
                        '<td WIDTH="90%"></td>',
                        '<td WIDTH="5%"></td>'
        );
        
        return CGI::start_html(
                        -title => "Web Job Logs - ".organisation(),
                        -style => {'src' => '/wjl-style.css'},
                        -head  => dhtml_header()
                        ),
                $table,
                        CGI::Tr(CGI::td(center($tl)),CGI::td(center($tm)),CGI::td(center($tr))),
                        CGI::Tr(CGI::td($bl),CGI::td(center($bm)),CGI::td($br)),
                '</table>',
                CGI::end_html();
}

# frame6_page, takes page, returns it framed using a mostly blank six_page.
sub frame6_page($) {
        my $page = $_[0];
        return six_page('','','','',$page,'');
}

# Two functions has_right and set_right, edit of level tokens.
# This is done to keep all the magic in one place.

# has_right, takes level, rights question.  returns 1 if user has that right.
sub has_right($$) {
        my ($level, $right) = @_;
        # The magic..
        my $new_job     = 0x01;
        my $edit_jobs   = 0x02;
        my $alloc_and_prio = 0x04;
        my $setup       = 0x08;
        my $password    = 0x10;
        my $delete      = 0x20;
        my $stats       = 0x40;
        my $knowledge   = 0x80;
        # Use RegExps, so queries can be more fluid.
        if($right =~ /job/) {
                if($right =~ /new/) {
                        if($level & $new_job) {
                                return 1;
                        }       
                } elsif($right =~ /edit/) {
                        if($level & $edit_jobs) {
                                return 1;
                        }
                }
        } elsif($right =~ /password/) {
                if($level & $password) {
                        return 1;
                }
        } elsif($right =~ /setup/) {
                if($level & $setup) {
                        return 1;
                }
        } elsif($right =~ /allocate/ || $right =~ /set.*priority/) {
                if($level & $alloc_and_prio) {
                        return 1;
                }
        } elsif($right =~ /delete/) {
                if($level & $delete) {
                        return 1;
                }
        } elsif($right =~ /stat/) {
                if($level & $stats) {
                        return 1;
                }
        } elsif($right =~ /know/) {
                if($level & $knowledge) {
                        return 1;
                }
        }
        return 0;
}

# set_right, takes level, right, boolean.  returns the altered level token.
sub set_right($$$) {
        my ($level, $right, $boolean) = @_;
        my $mask = 0;
        # The magic..
        my $new_job     = 0x01;
        my $edit_jobs   = 0x02;
        my $alloc_and_prio = 0x04;
        my $setup       = 0x08;
        my $password    = 0x10;
        my $delete      = 0x20;
        my $stats       = 0x40;
        my $knowledge   = 0x80;
        # Use RegExps, so queries can be more fluid.
        if($right =~ /job/) {
                if($right =~ /new/) {
                        $mask = $new_job;
                } elsif($right =~ /edit/) {
                        $mask = $edit_jobs;
                }
        } elsif($right =~ /password/) {
                $mask = $password;
        } elsif($right =~ /setup/) {
                $mask = $setup;
        } elsif($right =~ /allocate/ || $right =~ /set.*priority/) {
                $mask = $alloc_and_prio;
        } elsif($right =~ /delete/) {
                $mask = $delete;
        } elsif($right =~ /stat/) {
                $mask = $stats;
        } elsif($right =~ /know/) {
                $mask = $knowledge;
        }
        if($boolean) {
                $level |= $mask;
        } else {
                $level &= ~$mask;
        }
        return $level;
}

# hidden_fields, takes %data, returns a set of html hidden fields.
sub hidden_fields {
        my %data = @_;
        my $html = '';
        foreach(keys(%data)) {
                my $key = $_;
                $html .= '<input type="hidden" name="'.$key.'" value="'.$data{$key}.'">'."\n";
        }
        return $html;
}

# text_field, takes name, value. returns html text input field.
sub text_field($$) {
        my ($name, $value) = @_;
        my $size = length($value) > 20 ? length($value) : 20;
        my $html = "<input type=\"text\" name=\"$name\" size=\"$size\" value=\"$value\">\n";
        return $html;
}

# n2month, takes unix month number, returns 3 letter month name.
sub n2month($) {
        return (qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec))[$_[0]];
}

# gen_date, takes unix timestamp, returns date+time in form "11-Oct-2002 10:20"
sub gen_date($) {
        my $time = int($_[0]);
        my @temp = gmtime($time);
        my $month = n2month($temp[4]);
        my $day = $temp[3];
        my $year = $temp[5] + 1900;
        my $time = $temp[2].':'.($temp[1] < 10?'0'.$temp[1]:$temp[1]);
        my $text = "$day-$month-$year $time";
        return $text;
}

# date2timestamp, takes a text string something like "11-Oct-2002 10:20".
# returns unix timestamp. (intelligent e.g. "20:10 12/02/2000" is also valid)
sub date2timestamp($) {
        my $text = $_[0];
        my @months = qw(ja fe mar ap may jun jul au se oc no de);
        my ($a,$b,undef) = split(/ /,$text,3);
        my ($date,$time);
        # Which part is time and which is date?
        if($a =~ /:/) {
                $time = $a;
                $date = $b;
        } else {
                $time = $b;
                $date = $a;
        }
        # Clean rubbish out of time
        $time =~ s/([^0-9:])//g;
        # Split Time
        my ($hour,$minute,undef) = split(/:/,$time);
        
        # Split Date
        $date =~ s/([^0-9A-Za-z])/ /g;
        my ($day,$month,$year,undef) = split(/ /,$date);
        # See if month is tex tof number.
        if ($month =~ /([A-Za-z])/) {
                my $count = 0;
                foreach(@months) {
                        my $m = $_;
                        if($month =~ /^$m/i) {
                                $month = $count;
                        }
                        ++$count;
                }
        } else {
                --$month;
        }
        if($year >= 100) {$year -= 1900;}
        #print STDERR "timelocal(0,$minute,$hour,$day,$month,$year)\n";
        return Time::Local::timelocal(0,$minute,$hour,$day,$month,$year);
}

# gen_elapsed, takes seconds and returns the time elapsed as a text string.
sub gen_elapsed($) {
        my $sec = int($_[0]);
        my $ret = '';
        my ($min,$hour,$day);
        #print STDERR "gen_elapsed; in:$sec\n";
        if($sec >= (60*60*24)) {
                $day = int($sec / (60*60*24));
                $sec -= 60*60*24*$day;
        }
        if($sec >= (60*60)) {
                $hour = int($sec / (60*60));
                $sec -= 60*60*$hour;
        }
        if($sec >= 60) {
                $min = int($sec / 60);
                $sec -= 60*$min;
        }
        if($day) {
                $ret .= "$day Days, ";
        }
        if($day || $hour) {
                if($hour == 0) {
                        $hour = '0';
                }
                $ret .= "$hour Hours, ";
        }
        if($day || $hour || $min) {
                if($min == 0) {
                        $min = '0';
                }
                $ret .= "$min Minutes, ";
        }
        if($sec == 0) {
                $sec = '0';
        }
        $ret .= "$sec Seconds";
        #print STDERR "gen_elapsed; out:$ret\n";
        return $ret;
}
        

# select_field, takes name, currently selected option, list of options.
#       returns html select input type.
sub select_field($$@) {
        my ($name, $value, @options) = @_;
        my $html = '';
        $html .= "<SELECT name=\"$name\">\n";
        foreach(@options) {
                my $option = $_; 
                if($value && $option =~ /^$value/) {
                        $html .= "\t<OPTION SELECTED>$option</OPTION>\n";
                } else {
                        $html .= "\t<OPTION>$option</OPTION>\n";
                }
        }
        $html .= "</SELECT>\n";
        return $html;
}

# text_area, takes name, rows, cols and value. returns html textarea.
sub text_area($$$$) {
        my ($name, $rows, $cols, $value) = @_;
        my $html = '';
        $html .= "<TEXTAREA NAME=\"$name\"";
        if($rows) {
                $html .= " ROWS=$rows";
        }
        if($cols) {
                $html .= " COLS=$cols";
        }
        $html .= ">\n";
        $html .= $value;
        $html .= "</TEXTAREA>\n";
        return $html;
}

# submit_button, takes label for button, returns html submit button.
sub submit_button($) {
        my $label = $_[0];
        return "<input type=\"submit\" name=\"submit_type\" value=\"$label\">\n";
}

# hash2get, takes a hash of arguments, returns them in a get style url.
sub hash2get(%) {
        my %parm = @_;
        my @keys = keys(%parm);
        my $url = '';
        if(@keys > 0) {
                $url .= '?';
                foreach(@keys) {
                        my $key = $_;
                        $url .= $key.'='.$parm{$key}.'&';
                }
                chop($url);
        }
        return $url;
}

# status, convert status number to Text and vice versa.
sub status($) {
        my $status = $_[0];
        # print STDERR "status: in '$status'\n";
        my $out;
        # Text to int, or int to Text?
        if($status =~ /([A-Za-z])/) {
                # Text to int..
                if($status =~ /New/i) {
                        $out = 1;
                } elsif($status =~ /Active/i) {
                        $out = 2;
                } elsif($status =~ /Ready/i && $status =~ /User Test/i) {
                        $out = 4;
                } elsif($status =~ /Suspend/i) {
                        $out = 64;
                } elsif($status =~ /Closed/i) {
                        $out = 128;
                }
        } else {
                # int to Text..
                if($status == 1) {
                        $out = 'New';
                } elsif($status == 2) {
                        $out = 'Active';
                } elsif($status == 4) {
                        $out = 'Ready for user testing';
                } elsif($status == 64) {
                        $out = 'Suspended';
                } elsif($status == 128) {
                        $out = 'Closed';
                }
        }
        # print STDERR "status: return '$out'\n";
        return $out;
}

# statusl, returns a list of valid status texts.
sub statusl {
        my @list;
        # List of Status Codes.
        my @sti = (1,2,4,64,128);
        foreach(@sti) {
                push(@list,status($_));
        }
        return @list;
}

# priol, returns a list of valid priorities.
sub priol {
        return ('NA',1,2,3,4,5);
}

# priof, priority filter, converts 0 to NA, NA to 0.
sub priof($) {
        my $p = $_[0];
        if($p =~ /NA/) {
                return 0;
        } elsif($p == 0) {
                return 'NA';
        } else {
                return $p;
        }
}

# filter, takes string filters and dereferences stuff.
sub filter($) {
        my $text = shift;
        if($text) {
                $text =~ s/"/'/g;
                $text =~ s/`/'/g;
                $text =~ s/'/\\'/g;
                $text =~ s/([<>])/\\'/g;
        }
        return $text;
}

# radio_fields, takes name, currently checked and hash of value => option. 
#       returns html radio field.
sub radio_fields($$%) {
        my ($name, $checked, %parm) = @_;
        my $tab = '';
        my $html = '';
        foreach my $key (keys(%parm)) {
                if($key =~ /$checked/ && $checked) {
                        $tab = ' CHECKED';
                } else {
                        $tab = '';
                }
                $html .= '<br><input TYPE="radio" NAME="'.$name.'" VALUE="'.$key.'"'.$tab.'>'.$parm{$key}."\n";
        }
        return $html;
}

# fmt, takes columns, rows, string. 
#       returns the string html formated to those dimensions
sub fmt($$$) {
        my ($cols, $rows, $data) = @_;
        #my $chars = $cols * $rows;
        #my @data = split(//,$data,($chars+1));
        #my ($i,$j);
        #if(length($data) > $cols) {
        #       $data = '';
        #       for ($i = 0; $i < $rows; ++$i) {
        #               for ($j = 0; $j < $cols; ++$j) {
        #                       $data .= $data[($i * $cols) + $j];
        #               }
        #               $data .= "<br>\n";
        #       }
        #}
        return $data;
}

# popup_redirect, takes url, returns javascript.
#       which spawns a new window and redirects it to url.
sub popup_redirect($) {
        my $url = $_[0];
        my $html = '';
        $html .= '<script language="JavaScript">'."\n";
        $html .= 'pop = window.open("","pop","");'."\n";
        $html .= "pop.document.write('<head>');\n";
        $html .= "pop.document.write('".meta_redirect($url)."'>);\n";
        $html .= "pop.document.write('<title> </title>');\n";
        $html .= "pop.document.write('</head>');\n";
        $html .= "</script>\n";
        return $html;
}

# job_valid, takes jobid and dbh. returns 1 if job exists.
sub job_valid($$) {
        my ($jobid, $dbh) = @_;
        $jobid =~ s/([^0-9])//g;
        # Quite simple, if either is blank fail..
        if($dbh && $jobid) {
                my $query = "SELECT jobid FROM job WHERE jobid='$jobid'";
                my $result = $dbh->query($query);
                my @row = $result->fetchrow();
                if($row[0] == $jobid) {
                        return 1;
                } else {
                        return 0;
                }
        } else {
                return 0;
        }
}

# event_type, takes text type and event code. returns 1 if that code is set.
sub event_type($$) {
        my ($text, $code) = @_;
        # Magic..
        my $comment             = 0x01;
        my $status_change       = 0x02;
        my $set_priority        = 0x04;
        my $allocate            = 0x08;
        # Vague Checking..
        if($text =~ /comment/i && ($code & $comment)) {
                return 1;
        } elsif($text =~ /status.change/i && ($code & $status_change)) {
                return 1;
        } elsif($text =~ /set.priority/i && ($code & $set_priority)) {
                return 1;
        } elsif($text =~/allocate/i && ($code & $allocate)) {
                return 1;
        }
        # Nope
        return 0;
}

# auth_fail_page, takes username, returns redirect to the index login page, with the username.
sub auth_fail_page($) {
        my $user = $_[0];
        my $html = '';
        $html .= meta_redirect('index.pl'.hash2get(username => $user, fail => 1));
        $html .= CGI::end_html();
        return $html;
}

# dhtml_header, css for dhtml, needs to be in html <head>
sub dhtml_header {
        return 
'
<STYLE TYPE="text/css">
<!--

.DEK {POSITION:absolute;VISIBILITY:hidden;Z-INDEX:200;}
//-->
</STYLE>
';
}

# dhtml_main, spits out the script to do DHTML message boxes.
sub dhtml_main {
        return
'
<DIV ID="dek" CLASS="dek"></DIV>
<SCRIPT TYPE="text/javascript">
<!--
Width=screen.availWidth/2;
Xoffset=-(Width/2);
Yoffset= 20;
var active=0;

var nav,old,iex=(document.all);
if(navigator.appName=="Netscape"){(document.layers)?nav=true:old=true;}

if(!old){
var skn=(nav)?document.dek:dek.style;
if(nav)document.captureEvents(Event.MOUSEMOVE);
document.onmousemove=get_mouse;
}

function popup(msg,bak){
 active=1;
var content="<TABLE WIDTH="+Width+" BORDER=1 BORDERCOLOR=black CELLPADDING=2 CELLSPACING=0 "+
"BGCOLOR="+bak+"><TD ALIGN=center><FONT COLOR=white face=verdana SIZE=1><b>"+msg+"</b></FONT></TD></TABLE>";
if(old){return;} 
else{
 if(nav){skn.document.write(content);skn.document.close();skn.visibility="visible";}
 if(iex){document.all("dek").innerHTML=content;skn.visibility="visible";}
 }
}

function get_mouse(e){
 if(active) {
  var x=(nav)?e.pageX:event.x+document.body.scrollLeft;skn.left=x+Xoffset;
  var y=(nav)?e.pageY:event.y+document.body.scrollTop;skn.top=y+Yoffset;
 }
}

function kill(){
if(!old){active=0;skn.visibility="hidden";}
}

//-->
</SCRIPT>';
}

# dhtml_wrap, wraps $text in DHTML so that $other pops up on mouseover.
sub dhtml_wrap($$) {
        my ($text, $other) = @_;
        my $html;
        #$text =~ s/(["'\n\r])//g;
        $other =~ s/(["'])//g;
        $other =~ s/([\r\n])+/\n/g;
        $other =~ s/([\n])/<br>/g;
        $html .= "<a ONMOUSEOVER=\"popup('$other','#6699ff')\"; ONMOUSEOUT=\"kill()\">$text</a>\n";
        return $html;
}

# isie, returns 1 if client browser is Internet Exploiter.
sub isie {
        if(CGI::user_agent() =~ /MSIE/) {
                return 1;
        } else {
                return 0;
        }
}

# mime, takes a filename, returns a guess at its mime type.
sub mime($) {
        my $type = shift;
        my $mimetypes = '/etc/mime.types';
        open(MIME,$mimetypes);
        my @mime = readline *MIME;
        close(MIME);
        foreach my $line (@mime) {
                if ($line =~ /[#].*/) {
                        $line='';
                }
                if ($line =~ /[\t\n ]$type[\t\n ]/i) {
                        my @buffer = split(/[\t ]/,$line);
                        return($buffer[0]);
                }
        }
        return('application/octet-steam');
}

sub carrier_encode {
        my @fields = @_;
        my $buffer;
        $buffer = join("\t",@fields);
        $buffer = MIME::Base64::encode($buffer);
        $buffer =~ s/\n//g;
        return $buffer;
}

sub carrier_decode {
        my $buffer = $_[0];
        my @temp = split(//,$buffer);
        my ($i,$j);
        $buffer = '';
        for($i = 0; $i*76 < @temp; ++$i) {
                for($j = 0; $j < 76; ++$j) {
                        $buffer .= $temp[$i*80 + $j];
                }
                $buffer .= "\n";
        }
        return split(/\t/,MIME::Base64::decode($buffer));
}

# cut, returns $string to $len long
sub cut($$) {
        my ($len, $string) = @_;
        if(length($string) > $len) {
                my @temp = split(//,$string,($len+1));
                pop(@temp);
                return join('',@temp);
        } else {
                return $string;
        }
}

sub alert($) {
        my $msg = $_[0];
        return 
'
<SCRIPT TYPE="text/javascript">
<!--
alert('.filter($msg).'.);
//-->
</SCRIPT>';
}

sub set_sectoken($$$) {
        my ($dbh, $user, $token) = @_;
        my $query = "UPDATE user SET sectoken='$token' WHERE username='$user'";
        $dbh->query($query);
}

1;

wjl-0.26/src/modules/WJL.pm

# *-* Perl *-*

# WJL - Web Job Logs Perl Module
# WJL V0.26, Module V0.21
# Carl Ritson - 2002

package WJL;

require Mysql;
require MIME::Base64;

use vars qw($VERSION);
$VERSION = '0.21';

# Hash Definitions use for Reference.
%job_info_hash = (
        'jobid'                 => 1,
        'started'               => 1, 
        'problem_started'       => 1,
        'caller'                => 1,
        'dept'                  => 1,
        'username'              => 1,
        'priority'              => 1,
        'allocated_to'          => 1
);

%event_info_hash = (
        'username'      => 1,
        'type'          => 1,
        'status'        => 1,
        'data'          => 1,
        'date'          => 1
);

# The WJL new Function, spawns a new WJL Object.
sub New {
        my ($dbh) = @_;
        #my $dbh = Mysql->connect($db_host, $db_name, $db_user, $db_password);
        my $self = {
                'dbh' => $dbh
        };
        bless $self;
        #print STDERR "Class:$class\n";
        return $self;
}

# Query Function 
# saves other functions from having to reference the dbh directly.
sub query {
        my $self = shift;
        my $query = shift;
        #print STDERR "QUERY:\"$query\"\n";
        my $dbh = $self->{'dbh'};
        my $result = $dbh->query($query);
        return $result;
}

sub no_active_jobs {
        my $self = shift;
        my $username = shift;
        my $jobs = 0;
        my $result = $self->query("SELECT jobid FROM job WHERE allocated_to='$username'");
        my @row;
        while(@row = $result->fetchrow()) {
                my $jobid = $row[0];
                my $result = $self->query("SELECT status FROM event WHERE jobid=$jobid ORDER BY date DESC LIMIT 1");
                my @dat = $result->fetchrow();
                if($dat[0] < 64) {
                        ++$jobs;
                }
        }
        return $jobs;
}

sub user_exists {
        my $self = shift;
        my $username = shift;
        my $query = "SELECT username FROM user WHERE username=\'$username\'";
        my $result = $self->query($query);
        if ($result->fetchrow()) {
                return 1;
        } else {
                return 0;
        }
}       

sub del_user {
        my $self = shift;
        my $username = shift;
        my $query = "DELETE FROM user WHERE username=\'$username\'";
        $self->query($query);
        #print STDERR "DELETE $username\n";
}

sub _filter($) {
        my $text = shift;
        if($text) {
                $text =~ s/"/'/g;
                $text =~ s/`/'/g;
                $text =~ s/'/\\'/g;
                $text =~ s/([<>])/\\'/g;
        }
        return $text;
}

# Returns the Number of Seconds that are actualy worked between, 
#       two timestamps, based on 9-16.30 days, 5 day weeks...
sub workingseconds($$) {
        # These two constants alter the worked day, edit these only
        my $daystart = 9;       # Day Start
        my $dayfinish = 16.5;  # Day Finish
        # End Constants
        my ($from, $to) = @_;
        my @mdays  = (31,28,31,30,31,30,31,31,30,31,30,31);
        my @stat_s = localtime($from);
        my @stat_e = localtime($to);
        my $perday = ($dayfinish - $daystart)*60*60;
        my $daysdiff    = 0;
        my $daysspare   = 0;
        my $monsdiff    = 0;
        my $wks         = 0;
        my $sec         = 0;
        
        if($stat_s[5] != $stat_e[5]) {
                $monsdiff = (11 - $stat_s[4]) + $stat_e[4];
        } else {
                $monsdiff = $stat_e[4] - $stat_s[4];
        }
        if($monsdiff) {
                my $j = $stat_s[4];
                my $i = 0;
                for($i = 0; $i < $monsdiff; ++$i) {
                        $daysdiff += $mdays[$j];
                        ++$j;
                        if($j > 11) {
                                $j = 0;
                        }
                }
        }
        $daysdiff -= $stat_s[3];
        $daysdiff += $stat_e[3];
        
        $wks = int($daysdiff/7);
        $daysspare = $daysdiff - $wks*7;
        $daysdiff = $wks * 5; # 5 Days worked each week.
        
        if($daysspare) {
                my $days = $daysspare;
                if($stat_s[6] == 0) {
                        --$days;
                }
                if(($stat_s[6] + $daysspare) > 5) {
                        --$days;
                }
                if(($stat_s[6] + $daysspare) > 6) {
                        --$days;
                }
                $daysdiff += $days;
        }
        
        $sec = $daysdiff * $perday;
        if($stat_s[6] == 0 || $stat_s[6] == 6) {
                $stat_s[0] = 0;
                $stat_s[1] = 0;
                $stat_s[2] = $daystart;
        }
        if($stat_e[6] == 0 || $stat_e[6] == 6) {
                $stat_e[0] = 0;
                $stat_e[1] = 0;
                $stat_e[2] = $daystart;
        }

        if($stat_s[2] >= $dayfinish) {
                $stat_s[2] = $dayfinish;
                $stat_s[1] = 0;
                $stat_s[0] = 0;
        }
        if($stat_e[2] >= $dayfinish) {
                $stat_e[2] = $dayfinish;
                $stat_e[1] = 0;
                $stat_e[0] = 0;
        }
        if($stat_e[2] < $daystart) {
                $stat_e[2] = $daystart;
                $stat_e[1] = 0;
                $stat_e[0] = 0;
        }

        $sec += ((($stat_e[2] - $daystart) - ($stat_s[2] - $daystart)))*60*60;
        $sec += ($stat_e[1] - $stat_s[1])*60;
        $sec += $stat_e[0] - $stat_s[0];
        return $sec > 0 ? $sec : 0;
}

sub timediff {
        my ($start,$finish,$real) = @_;
        if($real) {
                return ($finish - $start);
        } else {
                return workingseconds($start,$finish);
        }
}

sub user {
        my ($self, %u) = @_;
        my ($username,$sectoken,$initials,$full_name,$level);
        $username       = _filter($u{username});
        $full_name      = _filter($u{fullname});
        $initials       = _filter($u{initials});
        $level          = $u{level};
        $sectoken       = $u{sectoken};
        if(!$full_name && !$initials && !$sectoken) {
                if($self->user_exists($username)) {
                        my $query = "SELECT full_name,initials,sectoken,level FROM user WHERE username='$username'";
                        my $result = $self->query($query);
                        my @row = $result->fetchrow();
                        if(@row) {
                                $u{fullname} = $row[0];
                                $u{initials} = $row[1];
                                $u{sectoken} = $row[2];
                                $u{level}    = $row[3];
                                return %u;
                        } else {
                                return 0;
                        }
                }
        } elsif($self->user_exists($username)) {
                if($full_name) {
                        $self->query("UPDATE user SET full_name='$full_name' WHERE username='$username'");
                }
                if($initials) {
                        $self->query("UPDATE user SET initials='$initials' WHERE username='$username'");
                }
                $self->query("UPDATE user SET level=$level WHERE username='$username'");
                if($sectoken) {
                        $self->query("UPDATE user SET sectoken='$sectoken' WHERE username='$username'");
                }
        } elsif($username) {
                my $query = "INSERT INTO user (username,sectoken,initials,full_name,level) VALUES('$username','$sectoken','$initials','$full_name',$level)";
                $self->query($query);
        }
}

sub list_depts {
        my $self = shift;
        my $result = $self->query("SELECT dept FROM dept ORDER BY dept ASC");
        my @list;
        my @row;
        while(@row = $result->fetchrow()) {
                push(@list,$row[0]);
        }
        return @list;
}

sub list_users {
        my $self = shift;
        my $result = $self->query("SELECT initials FROM user ORDER BY initials ASC");
        my @list;
        my @row;
        while(@row = $result->fetchrow()) {
                push(@list,$row[0]);
        }
        return @list;
}

sub list_usernames {
        my $self = shift;
        my $result = $self->query("SELECT username FROM user ORDER BY initials ASC");
        my @list;
        my @row;
        while(@row = $result->fetchrow()) {
                push(@list,$row[0]);
        }
        return @list;
}

sub list_technicians {
        my $self = shift;
        my @list = $self->list_usernames();
        my @techs;
        foreach my $uid (@list) {
                my %user = $self->user(username => $uid);
                if(($user{level} & 0x02) || ($user{level} & 0x01)) {
                        push(@techs,$user{initials});
                }
        }
        return @techs;
}

sub job {
        my ($self, $jobid) = @_;
        my $dbh = $self->{'dbh'};
        my ($started,$problem_started,$caller,$dept,$priority,$username,$allocated_to);
        my $new_job = 1;
        if ($jobid) {
                my $query = "SELECT started,problem_started,caller,dept,priority,username,allocated_to FROM job WHERE jobid='$jobid'";
                my $result = $self->query($query);
                ($started,$problem_started,$caller,$dept,$priority,$username,$allocated_to) = $result->fetchrow();
                if (!$started) {
                        return 0;
                }
                $new_job = 0;
        } else {
                $jobid = 0;
        }
        my $newself = {
                "dbh"                   => $dbh,
                "jobid"                 => $jobid,
                "started"               => $started,
                "problem_started"       => $problem_started,
                "caller"                => $caller,
                "dept"                  => $dept,
                "username"              => $username,
                "priority"              => $priority,
                "allocated_to"          => $allocated_to,
                "new_job"               => $new_job,
                "time"                  => 0,
                "changed"               => 0
        };
        bless $newself;
        return $newself;
}

sub info {
        my ($self, %info) = @_;
        my %ret;
        if(keys(%info) == 0) {
                foreach(keys(%job_info_hash)) {
                        #print STDERR "INFO \$ret{$_} = ".$self->{$_}."\n";
                        $ret{$_} = $self->{$_};
                }
                return %ret;
        } else {
                # Delete these Keys from the hash 
                # to prevent them overwritting those in the object.
                delete($info{jobid});
                delete($info{dbh});
                foreach(keys(%info)) {
                        #print STDERR "INFO:\$self->{$_} = ".$info{$_}."\n";
                        $self->{$_} = $info{$_};
                }
                $self->{'changed'} = 1;
        }
}

# Deletes the Job and all attached events.
sub del {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        if($jobid > 0) {
                $self->query("DELETE FROM event WHERE jobid=$jobid");
                $self->query("DELETE FROM job WHERE jobid=$jobid");
                # Make the jobid void to prevent future work on this job.
                $self->{'jobid'} = -1;
        }
}

sub commit {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        # The && jobid > -1, is a sanity check to prevent calls on deleted jobs.
        if(($self->{'changed'} || $self->{'new_job'}) && $jobid > -1) {
                my $started             = $self->{'started'};
                my $problem_started     = $self->{'problem_started'};
                my $caller              = $self->{'caller'};
                my $dept                = $self->{'dept'};
                my $priority            = $self->{'priority'};
                my $username            = $self->{'username'};
                my $allocated_to        = $self->{'allocated_to'};
                # Note the Lack of Error Handling
                # I am assuming that the insert/update are 'assured' ;)
                if($self->{'new_job'}) {
                        $self->query("INSERT INTO job (started,problem_started,caller,dept,priority,username,allocated_to) VALUES ($started,$problem_started,'$caller','$dept',$priority,'$username','$allocated_to')");
                        # Get the jobid of the new job.
                        my $result = $self->query("SELECT jobid FROM job WHERE started=$started AND dept='$dept' AND username='$username' ORDER BY jobid DESC LIMIT 1");
                        my @temp = $result->fetchrow();
                        $self->{'jobid'} = $temp[0];
                        $self->{'new_job'} = 0;
                } else {
                        $self->query("UPDATE job SET started=$started, problem_started=$problem_started, caller='$caller', dept='$dept', priority=$priority, username='$username', allocated_to='$allocated_to' WHERE jobid=$jobid");
                }
                $self->{'changed'} = 0;
        }
}

sub event {
        my ($self, $eventid) = @_;
        my $jobid = $self->{'jobid'};
        # Sanity Check: Only work on Real Jobs.
        if($jobid > 0) {
                my $result = $self->query("SELECT username,type,status,data,date FROM event WHERE jobid=$jobid AND eventid=$eventid");
                if($result) {
                        my @temp = $result->fetchrow();
                        my %info = (
                                eventid => $eventid,
                                username => $temp[0],
                                type    => $temp[1],
                                status  => $temp[2],
                                data    => $temp[3],
                                date    => $temp[4]
                        );
                        return %info;
                }       
        }
        return 0;
}

sub new_event {
        my ($self, %event) = @_;
        my $jobid = $self->{'jobid'};
        my $eventid = 0;
        # More Sanity Checking.. 4 Elements as we don't expect date. 
        if($jobid > -1 && keys(%event) >= 4) {
                if(!defined($event{date})) {
                        $event{date} = time();
                }
                # Make sure this Job is in the DB
                $self->commit();
                # If this was a new Job we now know its ID.
                $jobid = $self->{'jobid'};
                
                $self->query("INSERT INTO event (username,type,status,data,date,jobid) VALUES('".$event{username}."',".$event{type}.",".$event{status}.",'".$event{data}."',".$event{date}.",$jobid)");
                # Whats the chances of the same user adding 2 events at once..
                # 0% Touch Wood ;)
                my $result = $self->query("SELECT eventid FROM event WHERE jobid=$jobid AND username='".$event{username}."' ORDER BY date DESC LIMIT 1");
                my @temp = $result->fetchrow();
                $eventid = $temp[0];
        }
        return $eventid;
}

sub status {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        if($jobid > 0) {
                my $result = $self->query("SELECT status FROM event WHERE jobid=$jobid ORDER BY date DESC LIMIT 1");
                my @temp = $result->fetchrow();
                return $temp[0];
        }
        return -1;
}

sub last_event {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        if($jobid > 0) {
                my $result = $self->query("SELECT eventid FROM event WHERE jobid=$jobid ORDER BY date DESC LIMIT 1");
                my @temp = $result->fetchrow();
                my $eventid = $temp[0];
                return $self->event($eventid);
        }
}

sub first_event {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        if($jobid > 0) {
                my $result = $self->query("SELECT eventid FROM event WHERE jobid=$jobid ORDER BY date ASC LIMIT 1");
                my @temp = $result->fetchrow();
                my $eventid = $temp[0];
                return $self->event($eventid);
        }
}

sub update_event {
        my ($self,$eventid,$data) = @_;
        my $jobid = $self->{'jobid'};
        if($jobid > 0 && $eventid > 0) {
                $self->query("UPDATE event SET data='$data' WHERE eventid=$eventid AND jobid=$jobid");
        }
}

sub delete_event {
        my ($self,$eventid) = @_;
        my $jobid = $self->{'jobid'};
        if($jobid > 0 && $eventid > 0) {
                $self->query("DELETE FROM event WHERE eventid=$eventid AND jobid=$jobid");
        }
}

sub mangle_event {
        my ($self,%event) = @_;
        $self->query("UPDATE event SET username='".$event{username}."', type=".$event{type}.", status=".$event{status}.", data='".$event{data}."', date=".$event{date}." WHERE eventid=".$event{eventid});
}

sub description {
        my ($self, $description) = @_;
        my %ev = $self->first_event();
        $self->update_event($ev{eventid},$description);
}

sub list_events {
        my $self = shift;
        my $jobid = $self->{'jobid'};
        my @ids;
        if($jobid > 0) {
                my @row;
                my $result = $self->query("SELECT eventid FROM event WHERE jobid=$jobid ORDER BY date ASC");
                while(@row = $result->fetchrow()) {
                        push(@ids,$row[0]);
                }
        }
        return @ids;
}

sub user2initials {
        my ($self, $user) = @_;
        my $result = $self->query("SELECT initials FROM user WHERE username='$user'");
        my @temp = $result->fetchrow();
        return $temp[0];
}

sub initials2user {
        my ($self, $initials) = @_;
        my $result = $self->query("SELECT username FROM user WHERE initials='$initials'");
        my @temp = $result->fetchrow();
        return $temp[0];
}

sub fullname {
        my ($self, $user) = @_;
        my $result = $self->query("SELECT full_name FROM user WHERE username='$user'");
        my @temp = $result->fetchrow();
        return $temp[0];
}

sub set_realtime {
        my ($job,$set) = @_;
        $job->{'time'} = ~($set);
}

sub get_realtime {
        my ($job) = shift;
        return ~($job->{'time'});
}       

sub response_time {
        #print STDERR "Response Time\n";
        my $job = shift;
        my (%evf,%evl);
        my ($i,$fc);
        my @events = $job->list_events();
        %evf = $job->event($events[0]);
        for($i = 1; $i < @events && !$fc; ++$i) {
                %evl = $job->event($events[$i]);
                if($evl{type} & 0x01) { # Time of First Comment.
                        $fc = $evl{date};
                }
        }
        if($fc) {
                return timediff($evf{date},$fc,$job->get_realtime());
        } else {
                return 0;
        }
}

sub elapsed_time {
        my $job = shift;
        my %ev;
        my ($active,$i,$prev,$total);
        my @events = $job->list_events();
        %ev = $job->event($events[0]);
        $prev = $ev{date};
        if($ev{status} < 64) {
                $active = 1;
        }
        for($i = 1; $i < @events; ++$i) {
                %ev = $job->event($events[$i]);
                if($ev{status} >= 64) {
                        if($active) {
                                $active = 0;
                                $total += timediff($prev,$ev{date},$job->get_realtime()); 
                        }
                } else {
                        if(!$active) {
                                $active = 1;
                                $prev = $ev{date};
                        }
                }       
        }
        if($active) {
                $total += timediff($prev,time(),$job->get_realtime());
        }
        return $total;
}

sub comments {
        my $job = shift;
        my @events = $job->list_events();
        my @comments;
        foreach my $event (@events) {
                my %ev = $job->event($event);
                if($ev{type} & 0x01) {
                        push(@comments,$ev{data});
                }
        }
        return @comments;
}

# Used to prevent multiple submissions of the same job.
sub get_job_atomic {
        my ($self, $user, $time) = @_;
        my $result = $self->query("SELECT time FROM atomic WHERE username='$user'");
        my @row = $result->fetchrow();
        if($row[0] == $time) {
                return 0;
        } else {
                return 1;
        }
}

# Used to prevent multiple submissions of the same job.
sub set_job_atomic {
        my ($self, $user, $time) = @_;
        my $result = $self->query("SELECT time FROM atomic WHERE username='$user'");
        my @row = $result->fetchrow();
        if($row[0]) {
                $self->query("UPDATE atomic SET time=$time WHERE username='$user'");
        } else {
                $self->query("INSERT INTO atomic (username,time) VALUES('$user',$time)");
        }
}

# list jobs routine.
sub list_jobs {
        my ($self, %parm) = @_;
        my $query = "SELECT jobid FROM job";
        my $filter = '';
        my $order = '';
        foreach(keys(%parm)) {
                my $key = $_;
                if($key =~ /order/) {
                        my $char = chop($parm{$key});
                        if($char =~ /([-+])/) {
                                if($char =~ /-/) {
                                        $char = ' DESC';
                                } else {
                                        $char = ' ASC';
                                }
                        } else {
                                $parm{$key} .= $char;
                                $char = '';
                        }
                        $order = ' ORDER BY '.$parm{$key}.$char;
                } else {
                        $filter .= " $key='".$parm{$key}."'";
                }
        }
        if($filter) {
                $filter = ' WHERE'.$filter;
        }
        $query .= $filter.$order;
        my $result = $self->query($query);
        my @ids;
        @ids = $result->fetchcol();
        return @ids;
}

# add_knowledge, Base64 encode data into database
sub add_knowledge {
        my ($self,%file) = @_;
        $file{data} = MIME::Base64::encode($file{data});
        #print STDERR "add:".length($file{data})."\n";
        my $query = "INSERT INTO knowledge (jobid,name,description,data) VALUES(".$self->{'jobid'}.",'".$file{name}."','".$file{description}."','".$file{data}."')";
        $self->query($query);
}

# get_knowledge, Base64 decode data out of database
sub get_knowledge {
        my ($self,$name) = @_;
        my $query = "SELECT data FROM knowledge WHERE name='$name'";
        my $result = $self->query($query);
        my @temp = $result->fetchrow();
        return MIME::Base64::decode($temp[0]);
}

# list_knowledge, list the knowledge of a job, either by self reference or parm
sub list_knowledge {
        my ($self,$jid) = @_;
        my $jobid;
        if($jid) {
                $jobid = $jid;
        } else {
                $jobid = $self->{'jobid'};
        }
        my $query = "SELECT name FROM knowledge WHERE jobid=$jobid";
        my $result = $self->query($query);
        my (@names,@temp);
        while(@temp = $result->fetchrow()) {
                push(@names,$temp[0]);
        }
        return @names;
}

# find_knowledge, based on words..
sub find_knowledge {
        my ($self, @words) = @_;
        my $equery = "SELECT jobid FROM event WHERE type <= 3";
        my $kquery = "SELECT jobid FROM knowledge WHERE jobid > 0";
        foreach my $word (@words) {
                $word = _filter($word);
                $word =~ s/%/\%/g;
                $word = "\%$word%";
                $equery .= " AND data LIKE \"$word\"";
                $kquery .= " AND description LIKE \"$word\"";
        }
        my $eresult = $self->query($equery);
        my $kresult = $self->query($kquery);
        
        my %jobids;
        my @temp;
        my @names;
        while(@temp = $eresult->fetchrow()) {
                $jobids{$temp[0]} = 1;
        }
        while(@temp = $kresult->fetchrow()) {
                $jobids{$temp[0]} = 1;
        }
        foreach my $jobid (keys(%jobids)) {
                push(@names,$self->list_knowledge($jobid));
        }
        return @names;
}

sub describe_knowledge {
        my ($self, $name) = @_;
        my $query = "SELECT description FROM knowledge WHERE name='$name'";
        my $result = $self->query($query);
        my @temp = $result->fetchrow();
        return $temp[0];
}

sub delete_knowledge {
        my ($self, $name) = @_;
        if($name) {
                my $query = "DELETE FROM knowledge WHERE name='$name'";
                $self->query($query);
        }
}

# day_stats, Returns statistics for the day beginning time
sub day_stats {
        my ($self,$type,$time) = @_;
        my $i;
        my $st = $time;
        my $et = $st + 24*60*60;
        my ($query,$result);
        if($type =~ /^opened$/) {
                $query = "SELECT jobid FROM job WHERE started > $st AND started < $et";
                $result = $self->query($query);
                while($result->fetchrow()) {
                        ++$i;
                }
        } elsif($type =~ /^closed$/) {
                $query = "SELECT eventid FROM event WHERE date > $st AND date < $et AND status = 128";
                $result = $self->query($query);
                while($result->fetchrow()) {
                        ++$i;
                }
        } elsif($type =~ /^comments$/) {
                $query = "SELECT eventid FROM event WHERE date > $st AND date < $et AND type = 1";
                $result = $self->query($query);
                while($result->fetchrow()) {
                        ++$i;
                }
        }
        return $i;
}

# downtime, returns the downtime involved in a job
sub downtime {
        my ($self) = @_;
        my $prio = $self->{'priority'};
        my $r = $self->get_realtime();
        $self->set_realtime(0);
        my $duration = $self->elapsed_time();
        $self->set_realtime($r);
        if($prio > 0 && $prio < 5) {
                return int($duration / ($prio*$prio));
        } else {
                return 0;
        }
}

# due, returns the timestamp of when this job is estimated to finish.
sub due {
        my $job = shift;
        my $now = time;
        my ($query,$result);
        my @temp;
        # If job is suspended, or closed don't bother :)
        if($job->status() >= 64) {
                return -1;
        }
        # Don't Estimate for unallocated Jobs
        if(!($job->{'allocated_to'})) {
                return -1;
        }
        # Don't Act for Jobs with no priority
        if($job->{'priority'} == 0) {
                return -1;
        }
        
        # Figure out who this job was initialy allocated to
        $query = "SELECT data FROM event WHERE jobid=".$job->{'jobid'}." AND type=8 ORDER BY date ASC LIMIT 1";
        $result = $job->query($query);
        @temp = $result->fetchrow();
        my %user;
        if($temp[0]) {
                %user = $job->user(username => $temp[0]);
                #print STDERR "picking db user:".$user{username}."\n";
        } else {
                %user = $job->user(username => $job->{'allocated_to'});
                #print STDERR "picking job user:".$user{username}."\n";
        }

        my $date = $job->{'started'};
        $query = "SELECT jobid FROM job WHERE allocated_to='".$user{username}."' AND started < $date";
        $result = $job->query($query);
        my %jobs;
        while(@temp = $result->fetchrow()) {
                my $jobid = $temp[0];
                my $query = "SELECT status FROM event WHERE date < $date AND jobid=$jobid ORDER BY date DESC LIMIT 1";
                my $result = $job->query($query);
                my ($status,@rubbish) = $result->fetchrow();
                if($status < 64) {
                        my $j = $job->job($jobid);
                        ++$jobs{$j->{'priority'}};
                        #print STDERR "++jobs{".$j->{'priority'}."}\n";
                }
        }
        # Time alloted to different priorities of jobs.
        my %prio_map = (
                1 => 60*60*6,
                2 => 60*60*12,
                3 => 60*60*24,
                4 => 60*60*48,
                5 => 60*60*96
        );
        my $time_alloted = 0;
        my $i;
        for($i = 1; $i <= $job->{'priority'}; ++$i) {
                $time_alloted += $prio_map{$i} * $jobs{$i};
        }
        #print STDERR "time_alloted:$time_alloted\n";
        
        $time_alloted += $prio_map{$job->{'priority'}};
        $i = $job->get_realtime();      # Store The Present Realtime
        $job->set_realtime(0);          # Set Realtime Off
        my $remaining = $time_alloted - $job->elapsed_time();
        $job->set_realtime($i);         # Restore Previous Realtime
        
        #print STDERR "time_alloted:$time_alloted\n";
        #print STDERR "remaining:$remaining\n";
        
        # Ok this is abit of hack as I can't be bothered to write a
        #       reverse workingseconds at the moment...
        my $due = $now;
        $i = 0;
        while($i <= $remaining) {
                my @due = localtime($due);
                $due += 60*60 - $due[0] - $due[1]*60;
                $i = workingseconds($now,$due);
                #print STDERR "due: $due, i: $i\n";
        }
        return ($due != $now ? $due : 0);
}

wjl-0.26/src/inst

#!/bin/sh

mkdir $1 &> /dev/null
mkdir $2 &> /dev/null

cp -a src/cgi-bin/* $1
cp -a src/www/* $2
cp -a src/modules/* $3

rm -f $3/WJLCore.pm.in

wjl-0.26/src/cgi-bin/stat_graph.pl

#!/usr/bin/perl

# Statistical Graph Generator for Web Logs Software
# WJL V0.26, Page V0.11
# Carl Ritson - 2002

# Library Includes
use GD::Graph::bars;
use GD::Graph::colour qw(:colours);
use Mysql;
use CGI qw/:standard/;
use strict;
use WJL;
use WJLCore;

# Main Program Body

# CGI & DBH
my $page = new CGI;
my $dbh = Mysql->connect(db_login()) || db_error;

# Slightly different structure, as this doesn't send back HTML

if($dbh) {
        my %userinfo = get_user_info($page,'image/png');
        if (user_valid($dbh,$userinfo{username},$userinfo{sectoken})) {
                my $level = user_level($dbh,$userinfo{username});
                if(has_right($level,'stat')) {
                        my $wjl         = WJL::New($dbh);
                        my $start       = $page->param('start');
                        my $stop        = $page->param('stop');
                        my $type        = $page->param('type');
                        my $days        = int(($stop - $start)/(24*60*60));
                        my $i;
                        my (@data,@days);
                        for($i = 0; $i < $days; ++$i) {
                                my @data = $wjl->day_stats($type,$start + $i*24*60*60);
                                my @temp = localtime($start + $i*24*60*60);
                                push(@days,$temp[3].' '.n2month($temp[4]));
                        }
                        @data = (
                                [@days],
                                [@data]
                        );
                        my $graph = GD::Graph::bars->new(400,200);
                        $graph->set(
                                #x_label        => 'Days',
                                x_label_skip    => int($days/9),
                                transparent     => 0
                                #bgclr          => _rgb('black')
                        );
                        binmode(STDOUT);
                        print STDOUT $graph->plot(\@data)->png();
                }
        }
}

wjl-0.26/src/cgi-bin/menu.pl

#!/usr/bin/perl

# Main Menu Page for Web Logs Software
# WJL V0.26, Page V0.2
# Carl Ritson - 2002

# Library Include
use Mysql;
use CGI qw/:standard/, 'shortcuts', 'center';
use strict;
use WJL;
use WJLCore;

# Main Program Body

# CGI & DBH
my $page = new CGI;
my $dbh = Mysql->connect(db_login()) || db_error();

# Predefine some Variables
my $user_valid = 0;
my %userinfo;

# Call Start Page Function, only if we have a DBH.
if ($dbh) {
        ($user_valid, %userinfo) = start_page($page,$dbh);
}

if ($user_valid) {
        my $wjl         = WJL::New($dbh);
        my $user        = $userinfo{username};
        my $no_jobs     = $wjl->no_active_jobs($user);
        my $title       = h3("Main Menu");
        my $level       = user_level($dbh,$user);
        my %parm;
        if($userinfo{security} =~ /high/) {
                %parm = %userinfo;
        }
        $parm{src} = 'menu.pl';
        my $menu = '';
        # New Job, Edit Jobs, Job List, Job Report, (Setup/Password)
        if(has_right($level,'new_job')) {
                $menu .= form_button('New Job','new_job.pl',%parm).br();
        }
        if(has_right($level,'edit_jobs')) {
                $menu .= form_button('Edit Job','edit_job.pl',%parm).br();
        }
        $menu .= form_button('Job List','job_list.pl',%parm).br();
        $menu .= form_button('Job Report','job_report.pl',%parm).br();
        if(has_right($level,'stat')) {
                $menu .= form_button('Statistics','stats.pl',%parm).br();
        }
        if(has_right($level,'knowledge')) {
                $menu .= form_button('Knowledge','knowledge_find.pl',%parm).br();
        }
        # Users get one or the other
        if(has_right($level,'setup')) {
                $menu .= form_button('Setup','setup_menu.pl',%parm).br();
        } elsif(has_right($level,'password')) {
                $menu .= form_button('Change Password','change_passwd.pl',%parm).br();
        }
        $menu .= form_button('Log Out','index.pl',logout => 1,security => $userinfo{security}).br();
        print   start_html(
                        -title=>"Web Job Logs - ".organisation(),
                        -style=>{'src'=>'/wjl-style.css'}
                ),
                "<table border=\"0\" width=\"100%\">\n",
                        Tr(
                                '<td WIDTH="10%"></td>',
                                '<td WIDTH="80%"></td>',
                                '<td WIDTH="10%"></td>'
                        ),
                        Tr(
                                td(center("<b>Jobs:</b> $no_jobs"),),
                                td(''),
                                td(center("<b>User:</b><br>".$wjl->fullname($user)))
                        ),
                        Tr(
                                td(''),
                                td(center($title)),
                                td('')
                        ),
                        Tr(
                                td(''),
                                td(center($menu)),
                                td('')
                        ),
                "</table>\n",
                end_html();
} else {
        # If we don't have a DBH an error page has already been printed.
        if($dbh) {
                print auth_fail_page($userinfo{username});
        }
}

wjl-0.26/src/cgi-bin/knowledge_find.pl

#!/usr/bin/perl

# Find Knowledge Page for Web Logs Software
# WJL V0.26, Page V0.1
# Carl Ritson - 2002

# Library Include
use Mysql;
use CGI qw/:standard/, 'shortcuts', 'center';
use strict;
use WJL;
use WJLCore;

# Main Program Body

# CGI & DBH
my $page = new CGI;
my $dbh = Mysql->connect(db_login()) || db_error;

# Predefine some Variables
my $user_valid = 0;
my $level      = 0;
my %userinfo;

my $src         = $page->param('src'); # Where did we come from.

# Call Start Page Function, only if we have a DBH.
if ($dbh) {
        ($user_valid, %userinfo) = start_page($page,$dbh);
        $level = user_level($dbh,$userinfo{username});
}

if ($user_valid && has_right($level,'knowledge')) {
        my $wjl         = WJL::New($dbh);
        my $title       = h3("Find Knowledge");
        my %parm;
        if($userinfo{security} =~ /high/) {
                %parm = %userinfo;
        }
        $parm{src} = $src;
        my $html = '';
        my $phrase = $page->param('phrase');
        my $message;
        # Delete Knowledge, before looking for it.
        if($page->param('delete')) {
                my $file = filter($page->param('file'));
                if($file) {
                        $wjl->delete_knowledge($file);
                }
        }
        # If your looking for $html .= $title, look at the bottom
        $html .= "<form>\n<table>\n";
        $html .= hidden_fields(%parm);
        $html .= Tr(td('Phrase:'),
                        td(text_field('phrase',$phrase)));
        $html .= "</table>\n";
        $html .= "<p>\n";
        $html .= submit_button('Find Knowledge');
        $html .= "</form>\n";
        $html .= form_button('Cancel',$src,%parm);
        $html .= "</p>\n";
        if($phrase) {
                $phrase = filter($phrase);
                my @words = split(/ /,$phrase);
                my @names = $wjl->find_knowledge(@words);
                if(@names) {
                        $html .= '<p><table WIDTH="400" BORDER="1" CELLPADDING="2" CELLSPACING="1">';
                        $html .= Tr(
                                        td(b(center('Filename'))),
                                        td(b(center('Description'))),
                                        td(),td()
                        );
                        foreach my $file (@names) {
                                my $desc = $wjl->describe_knowledge($file);
                                $html .= Tr(
                                        td(center(b($file))),
                                        td(center(fmt(40,2,$desc))),
                                        td(center(form_button('Get',"knowledge_get.pl/$file",%parm))),
                                        td(center(form_button('Delete','knowledge_find.pl',%parm,delete=>1,file=>$file,phrase=>$phrase)))
                                );
                        }
                        $html .= '</table></p>';
                } else {
                        $message = p(b("Sorry No Knowledge Relating To '$phrase' was found."));
                }
        }
        # Hacky Magic
        $html = $title . $message . $html;
        print frame6_page($html);
} else {
        # If we don't have a DBH an error page has already been printed.
        if($dbh) {
                print auth_fail_page($userinfo{username});
        }
}

wjl-0.26/src/cgi-bin/departments.pl

#!/usr/bin/perl

# Department Setup Menu Page for Web Logs Software
# WJL V0.26, Page V0.1
# Carl Ritson - 2002

# Library Include
use Mysql;
use CGI qw/:standard/, 'shortcuts', 'center';
use strict;
use WJL;
use WJLCore;

# Main Program Body

# CGI & DBH
my $page = new CGI;
my $dbh = Mysql->connect(db_login()) || db_error();

# Predefine some Variables
my $user_valid = 0;
my %userinfo;
my $level = 0;

my $src = $page->param('src'); # Where did we come from?

# Call Start Page Function, only if we have a DBH.
if ($dbh) {
        ($user_valid, %userinfo) = start_page($page,$dbh);
        $level  = user_level($dbh,$userinfo{username});
}

if ($user_valid && has_right($level,'setup')) {
        my $wjl         = WJL::New($dbh);
        my $active      = $page->param('active');
        my $type        = $page->param('submit_type');
        my $title       = h3("Departments");
        my %parm;
        if($userinfo{security} =~ /high/) {
                %parm = %userinfo;
        }
        $parm{src} = 'setup_menu.pl';
        # Quite a bit of database exposed in this module.
        my $html = '';
        # Perform any changes before displaying the page.
        if($type =~ /add/i || $type =~ /update/i) {
                my $dept        = cut(32,filter($page->param('dept')));
                my $dept_name   = filter($page->param('dept_name'));
                my $hod         = filter($page->param('hod'));
                my $phone       = cut(32,filter($page->param('phone')));
                if($type =~ /add/i && $dept) {
                        my $query = "INSERT INTO dept (dept,dept_name,hod,phone) VALUES('$dept','$dept_name','$hod','$phone')";
                        $dbh->query($query);
                } elsif($dept) {
                        my $query = "UPDATE dept SET dept_name='$dept_name',hod='$hod',phone='$phone' WHERE dept='$dept'";
                        $dbh->query($query);
                }
        } elsif($type =~ /delete/i) {
                my $dept        = filter($page->param('dept'));
                my $query       = "DELETE FROM dept WHERE dept='$dept'";
                $dbh->query($query);
        }
        $html .= $title;
        $html .= hr();
        $html .= "<table BORDER=\"1\">\n";
        $html .= Tr(
                        td(center(b('Dept'))),
                        td(center(b('Name'))),
                        td(center(b('HOD'))),
                        td(center(b('Phone'))),
                        td(),td()
        );              
        $html .= Tr(
                        "<form>\n",
                        hidden_fields(%parm),
                        hidden_fields(active => 1),
                        td(center(text_field('dept',''))),
                        td(center(text_field('dept_name',''))),
                        td(center(text_field('hod',''))),
                        td(center(text_field('phone',''))),
                        td(center(submit_button('Add'))),
                        "</form>\n"
        );
        my @depts = $wjl->list_depts();
        foreach my $dept (@depts) {
                my $query = "SELECT dept_name,hod,phone FROM dept WHERE dept='$dept'";
                my $result = $dbh->query($query);
                my ($dept_name, $hod, $phone, $rubbish) = $result->fetchrow();
                $html .= Tr(
                        "<form>\n",
                        hidden_fields(%parm),
                        hidden_fields(active => 1, dept => $dept),
                        td(center(b($dept))),
                        td(center(text_field('dept_name',$dept_name))),
                        td(center(text_field('hod',$hod))),
                        td(center(text_field('phone',$phone))),
                        td(center(submit_button('Update'))),
                        td(center(submit_button('Delete'))),
                        "</form>\n"
                );
        }
        $html .= "</table>\n";
        $html .= hr();
        $html .= form_button('Return',$src,%parm);
        print frame6_page($html);
} else {
        # If we don't have a DBH an error page has already been printed.
        if($dbh) {
                print auth_fail_page($userinfo{username});
        }
}