#!/usr/bin/perl -wT use strict; use DBI(); use CGI qw(:standard); use CGI::Session; use CGI::Session::Driver::mysql; my $query = new CGI; $query->import_names('R'); my $cmd = ($R::act); my $qs = $ENV{QUERY_STRING}; my $server = "www.ictprovision.com"; my $dbh; my $host = "localhost"; my $db = "supportdb"; my $dbu = "www-data"; my $dpw = "999or666"; my $table1 = "issues"; my $rows = ''; my $stit; my $stxt; my $href; my @ex; my @pri = ('dummy', 'Critical', 'Important', 'Routine', 'Request'); my @status = ('dummy', 'Open', 'Closed', 'In Hand', 'Cancelled'); my $Pprofile; my $Sprofile; $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", "$dbu", "$dpw", {'RaiseError' => 1}); my $session = new CGI::Session("driver:mysql", undef, { Handle => $dbh, TableName => "sessions" } ); if ( $session->param("~logged-in") ) { $Pprofile = $session->param("~Pprofile"); $Sprofile = $session->param("~Sprofile"); print "Content-type: text/html\n\n"; my $qrystr = liststr(); # my $sth = $dbh->prepare("select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=?"); my $sth = $dbh->prepare($qrystr); # $sth->execute($Sprofile->{uid}); $sth->execute(@ex); print < Issues List
END while(my @row = $sth->fetchrow_array()) { if ( length($row[2]) > 32 ) { $stit = substr($row[2], 0, 32) . ".."; } else { $stit = $row[2]; } if ( length($row[3]) > 38 ) { $stxt = substr($row[3], 0, 38) . ".."; } else { $stxt = $row[3]; } $href = qq($row[0]); $rows = "$rows"; } print < END } else { # print $query->redirect( -url => "http://$server/login.cgi?act=$cmd" ); print $query->redirect( -url => "http://$server/login.cgi?$qs" ); } sub liststr { my $lqry; if ( defined ($R::stat) ) { # We're only looking for open issues if ( defined ($R::typ) ) { # We only want open, critical issues $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? and pri=? and status=? order by id desc"; @ex = ($Sprofile->{uid}, $R::typ, $R::stat); } elsif ( defined ($R::mine) ) { # We want all open issues for the specific user $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? and uid=? and (status=1 or status=3) order by id desc"; @ex = ($Sprofile->{uid}, $Pprofile->{uid}); } else { # We want all open issues $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? and (status=1 or status=3) order by id desc"; @ex = ($Sprofile->{uid}); } } elsif ( defined ($R::tot) ) { # We want the last 10 issues $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? order by id desc limit 10"; @ex = ($Sprofile->{uid}); } elsif ( defined ($R::mine) ) { # We want all issues for the specific user $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? and uid=? order by id desc"; @ex = ($Sprofile->{uid}, $Pprofile->{uid}); } else { # List all issues $lqry = "select id, date_format(repdate, '%d/%m/%Y'), itit, itext, status, pri from $table1 where sid=? order by id desc"; @ex = ($Sprofile->{uid}); } return $lqry; } sub dummysub { # The following lines are solely to suppress 'only used once' warnings $R::act = $R::act; $R::stat = $R::stat; $R::tot = $R::tot; $R::typ = $R::typ; }
No. Date Title Description Status Piority
$href $row[1] $stit $stxt $status[$row[4]] $pri[$row[5]]