#!/usr/bin/perl -wT

use strict;
use DBI();
use CGI qw(:standard);
use CGI::Session;
use CGI::Session::Driver::mysql;
#use CGIBook::Error;

$ENV{PATH} = "/bin:/usr/bin";
delete @ENV{ 'IFS', 'CDPATH', 'ENV', 'BASH_ENV' };

require 'formdate.pl';

my $query = new CGI;
$query->import_names('R');
my $cmd = ($R::act);
my $DATE_FORMAT = '<d>-<mon>-<year>';
my $DATE_FORMAT_IN = '<year>-<0m>-<0d>';
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 $Pprofile;
my $Sprofile;
my $dte = &format_date(time, $DATE_FORMAT);
my $foot = ''; my $udte = '--/--/--'; my $cdte = '--/--/--';
my $title = '';
my $stat_box = ''; my $pri_box = ''; my $typ_box = ''; my $styp_box = ''; 
my $inum = ''; my $unam = ''; my $itit = ''; my $rtitle = '';
my $loc = ''; my $itext = ''; my $utext = ''; my $ctext = ''; 
my $idrs = "readonly=\"readonly\"";
my $ssbox = ''; my $psbox = ''; my $tsbox = ''; my $stsbox = '';
my $iirs = ''; my $iurs = ''; my $irrs = ''; 

$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");
	setdets($cmd);

	print "Content-type: text/html\n\n";		
	print_html();
} else {
	print $query->redirect( -url => "http://$server/login.cgi?$qs" );
}

sub setdets {
	my ($act) = shift;
	my $msg = '';
	
	if ( $act eq 'add' ) {
		$idrs = ''; $ssbox = 'DISABLED'; $iurs = $irrs = "readonly=\"readonly\""; 
		$unam = "$Pprofile->{forename} $Pprofile->{surname}";
		$rtitle = "Raise A New Issue";
		$cdte = "New Issue";
		gen_boxes();	
		$foot = mkft("insert", "");
		$title = "Add Issue";
	} elsif ( $act eq 'View' ) {
		$rtitle = 'Existing Issue Details';
		$psbox = $tsbox = $stsbox = $ssbox = 'DISABLED';
		$iirs = $iurs = $irrs = "readonly=\"readonly\""; 
		$title = "View Issue";
		my $sth = $dbh->prepare("select id,sid,uid,typ,styp,pri,loc,itit,itext,date_format(repdate, '%d/%m/%Y'),date_format(cldate, '%d/%m/%Y'),cltext,uptext,status,date_format(lupdte, '%d/%m/%Y') from issues where id=?");		
		$sth->execute($R::iss);
#		unless ( $sth->execute($R::iss) ) {
#			error( $query, "Ooops..." );
#		}
		my ($id, $sid, $uid, $typ, $styp, $pri, $wer, $tit, $txt, $rdte, $cldte, $ctxt, $utxt, $statt, $ludte) = $sth->fetchrow_array();
		$inum = $id;
		$unam = get_rep_by($uid);
		$dte = $rdte;
		$loc = $wer;
		$itit = $tit;
		$itext = $txt;
		$ctext = $ctxt if ( defined $ctxt );
		$utext = $utxt if ( defined $utxt );
		$udte = $ludte if ( defined $ludte );
		$cdte = $cldte if ( defined $cldte );
		gen_boxes($statt, $pri, $typ, $styp);
	} elsif ( $act eq 'insert' ) {
		$title = "Insert Issue";
		my $sth = $dbh->prepare("insert into issues (sid,uid,typ,styp,pri,loc,itit,itext,repdate,status) values(?,?,?,?,?,?,?,?,curdate(),1)");
		$sth->execute($Sprofile->{uid},$Pprofile->{uid},$R::type,$R::stype,$R::pri,$R::loc,$R::tit2,$R::details2);
#		unless ( $sth->execute($Sprofile->{uid},$Pprofile->{uid},$R::type,$R::stype,$R::pri,$R::loc,$R::tit2,$R::details2) ) {
#			error( $query, "Ooops..." );
#		}
		inform();
		print $query->redirect( -url => "http://$server/dbases/dashboard.cgi?act=dash" );
	} elsif ( $act eq 'Update' ) {
		$rtitle = "Update Issue Details";
		$title = "Update Issue";
		my $sth = $dbh->prepare("select id,sid,uid,typ,styp,pri,loc,itit,itext,date_format(repdate, '%d/%m/%Y'),cldate,cltext,uptext,status,date_format(lupdte, '%d/%m/%Y') from issues where id=?");		
		$sth->execute($R::iss);
#		unless ( $sth->execute($R::iss) ) {
#			error( $query, "Ooops..." );
#		}
		my ($id, $sid, $uid, $typ, $styp, $pri, $wer, $tit, $txt, $rdte, $cldte, $ctxt, $utxt, $statt, $ludte) = $sth->fetchrow_array();
		if ( ( $Pprofile->{uid} == $uid ) or ( $Pprofile->{role} < 2 ) ) {
			if ( $Pprofile->{role} < 2 ) {
				upstat('admin', $statt);
			} else {
				upstat('user', $statt);
			}
			$inum = $id;
			$unam = get_rep_by($uid);
			$dte = $rdte;
			$loc = $wer;
			$itit = $tit;
			$itext = $txt;
			$ctext = $ctxt if ( defined $ctxt );
			$utext = $utxt if ( defined $utxt );
			$udte = $ludte if ( defined $ludte );
			$cdte = $cldte if ( defined $cldte );
			gen_boxes($statt, $pri, $typ, $styp);
			$foot = mkft("update", qq^onClick="return validate_form(this.form)"^);
		} else {
			print $query->redirect( -url => "http://$server/dbases/addissue.cgi?act=View&iss=$R::iss" );
		}
	} elsif ( $act eq 'update' ) {
		my $udte = &format_date(time, $DATE_FORMAT_IN);
		my $cdte = &format_date(time, $DATE_FORMAT_IN) if ( $R::stat == 2 );
		my $utxt = $R::updates if ( $R::updates ne 'Issue updates will go here ....' );
		my $cltxt = $R::resolv if ( $R::stat == 2 );
		$title = "Update Issue";
		my $sth = $dbh->prepare("update issues set typ=?, styp=?, pri=?, itit=?, loc=?, status=?, itext=?, uptext=?, cltext=?, cldate=?, lupdte=? where id=?");
		$sth->execute($R::type,$R::stype,$R::pri,$R::tit2,$R::loc,$R::stat,$R::details2,$R::updates,$R::resolv,$cdte,$udte,$R::num);
#		unless ( $sth->execute($R::type,$R::stype,$R::pri,$R::tit2,$R::loc,$R::stat,$R::details2,$R::updates,$R::resolv,$cdte,$udte,$R::num) ) {
#		error( $query, "Ooops..." );
#		}
		print $query->redirect( -url => "http://$server/dbases/dashboard.cgi?act=dash" );
	}	
	return;
}

sub upstat {
	my ($usr, $stat) = @_;
	
	if ( $usr eq 'admin' ) {
		$psbox = $tsbox = $stsbox = 'DISABLED';
		if( $stat == 1 ) {
			$iirs = $iurs = $irrs = "readonly=\"readonly\""; 
		} elsif ( $stat == 2 ) {
			$iirs = "readonly=\"readonly\""; 
		} elsif ( ( $stat == 3 ) or ( $stat == 4 ) ) {
			$iirs = $irrs = "readonly=\"readonly\""; 
		}
	} else {
		$ssbox = 'DISABLED';
		if( $stat == 1 ) {
			$iurs = $irrs = "readonly=\"readonly\""; 
		} elsif ( $stat == 3 ) {
			$iirs = "readonly=\"readonly\""; 
		} elsif ( ( $stat == 2 ) or ( $stat == 4 ) ) {
			$irrs = "readonly=\"readonly\""; 
		}
	}
}

sub mkft {

	my ($subm, $val) = @_;
	my $foot;
	my $ msg;
	
	$msg = q^If this form does not redirect on clicking 'Submit', you have made an error. Please scroll up and correct error(s).^;
	$foot = qq^
	<div id="buttons">
	<div id="reset"><input name="reset" id="reset" type="reset" value="Reset" /></div>
	<div id="act"><input name="act" id="act" type="hidden" value="$subm" /></div>
	<div id="submit"><input name="submit" id="submit" $val type="submit" value="Submit" /></div>
	</div>
	<div id="msg">$msg</div>^;
	
	return $foot;
}

sub inform {
	my $to ='issues@ictprovision.com';
	my $from = 'www-data@mailsrv.ictprovision.com';
	my $sbj = "New Issue Added To The Support Database";
	my $msg = "A new issue has been added to $Sprofile->{sname}'s (SID: $Sprofile->{uid}) database by \n\n$Pprofile->{forename} $Pprofile->{surname} (UID: $Pprofile->{uid})\n\nIssue Title: $R::tit2\n\nPriority: $R::pri\n";
	
	open(MAIL, "|/usr/sbin/sendmail -t -f $from");
 
	## Mail Header
	print MAIL "To: $to\n";
	print MAIL "From: $from\n";
	print MAIL "Subject: $sbj\n\n";
	## Mail Body
	print MAIL "$msg\n";
 
	close(MAIL);
}

sub gen_boxes {

	my ($s1, $s2, $s3, $s4) = @_;

	$stat_box = qq(<option value="5">New Issue</option>
			<option value="1">Open</option>
			<option value="2">Closed</option>
			<option value="3">In Hand</option>
			<option value="4">Cancelled</option>);
			
	$pri_box = qq(<option value="-1">Set priority</option>
			<option value="1">Critical</option>
			<option value="2">Important</option>
			<option value="3">Routine</option>
			<option value="4">Request</option>);

	$typ_box = qq(<option value="-1">Select category</option>
			<option value="1">Hardware</option>
			<option value="2">Network</option>
			<option value="3">Software</option>
			<option value="4">Other</option>);
		
	$styp_box = qq(<option value="-1">Select type</option>);
	
	if ( defined $s1 ) {
		my $sth = $dbh->prepare("select typ from istypes where id=?");
		$sth->execute($s4);
#		unless ( $sth->execute($s4) ) {
#			error( $query, "Ooops..." );
#		}
		my ($typ) = $sth->fetchrow_array();
		$stat_box =~ s/\"$s1\"/\"$s1\" selected/;
		$pri_box =~ s/\"$s2\"/\"$s2\" selected/;
		$typ_box =~ s/\"$s3\"/\"$s3\" selected/;
		$styp_box = qq(<option value="$s4" selected>$typ</option>);
	}
}

sub get_rep_by {
	my ($uid) = @_;
	my $nam = '';
	
	if ( $uid eq $Pprofile->{uid} ) {
		$nam = "$Pprofile->{forename} $Pprofile->{surname}";
	} else {
		my $sth = $dbh->prepare("select forename, surname from users where uid=?");
		$sth->execute($uid);
#		unless ( $sth->execute($uid) ) {
#			error( $query, "Ooops..." );
#		}
		my ($fname, $sname) = $sth->fetchrow_array();
		$nam = "$fname $sname";
	}
	return $nam;
}

sub dummysub {
#	The following lines are solely to suppress 'only used once' warnings
	$R::type = $R::type; $R::pri = $R::pri; $R::loc = $R::loc; $R::repdate = $R::repdate; $R::act = $R::act; 
	$R::stype = $R::stype; $R::tit2 = $R::tit2; $R::details2 = $R::details2;
	$R::stat = $R::stat; $R::iss = $R::iss; $R::num = $R::num;
}

sub print_html {

print <<END;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>$title</title>
<script src="../SpryAssets/SpryMenuBar.js" type="text/javascript"></script>
<script src="../SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<script src="../SpryAssets/SpryValidationTextarea.js" type="text/javascript"></script>
<script src="../SpryAssets/SpryValidationSelect.js" type="text/javascript"></script>
<script src="../SpryAssets/My.js" type="text/javascript"></script>
<link href="../SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
<link href="../SpryAssets/SpryValidationTextarea.css" rel="stylesheet" type="text/css" />
<link href="../SpryAssets/SpryValidationSelect.css" rel="stylesheet" type="text/css" />
<link href="../SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<link href="../ict.css" rel="stylesheet" type="text/css" />
</head>

<body>
<!--[if IE]> <div id="IEroot"> <![endif]-->
<div id="container">
	<div id="banner" title="This site is best viewed with Mozilla Firefox or equivalent.">
<ul id="ict_menu" class="MenuBarHorizontal">
			<li><a href="../index.htm">Home</a></li>
			<li><a href="../services.htm">Services</a></li>
			<li><a href="dashboard.cgi?act=dash">Dashboard</a></li>
			<li><a href="issues.cgi?act=issuesl">&nbsp;&nbsp;&nbsp;&nbsp;Issues</a></li>
			<li><a href="../links.htm">&nbsp;&nbsp;&nbsp;Links</a></li>
			<li><a href="../contact.htm">Contact Us</a></li>
		</ul>
	</div>
	
	<div id="title">
		<h3 align="center">$rtitle</h3>
	</div>
	
	<div id="issues">
	  <form id="form1" name="form1" method="post" action="">
			<div id="form_gen1">
				<fieldset>
					<legend>Status Information</legend>
					<label for="num">Issue No.</label>
					<input name="num" id="num" type="text" class="input-box" size=15 value="$inum" readonly="readonly"/><br>
					<label for="stat">Status</label>
					<select name="stat" id="stat" onchange="setbox(this.form)" size="1" class="select" $ssbox>$stat_box</select><br>		
					<span id="spryPri">
					<label for="pri">Priority</label>
					<select name="pri" id="pri" size="1" class="select" $psbox>$pri_box</select><br>
					<span class="selectInvalidMsg">Please select a valid level.</span>
                			</span>
					<label for="udate">Last Updated</label>
					<input  name="udate"  id="udate" type="text" class="input-box" size=15 value="$udte" readonly="readonly"/><br><br>
				</fieldset>
		  </div>
			<div id="form_gen2">
				<fieldset>
					<legend>User Information</legend>
					<label for="sch">School</label>
				  	<input name="sch" id="sch" type="text" class="input-box" size=50 readonly="readonly" value="$Sprofile->{fname}"/><br>
					<label for="user">Reported By</label>
					<input name="user" id="user" type="text" class="input-box" size=50 readonly="readonly" value="$unam"/><br>
					<label for="repdate">Date Reported</label>
					<input  name="repdate"  id="repdate" type="text" class="input-box" size=15 readonly="readonly" value="$dte"/><br>
					<label for="cldate">Date Closed</label>
					<input  name="cldate"  id="cldate" type="text" class="input-box" size=15 readonly="readonly" value="$cdte"/><br><br>
				</fieldset>
		  </div>
			<div id="isshead">
			  <fieldset>
			    <legend>Issue Details</legend>
					<span id="spryIssueTitle">
						<label for="tit2">Title</label>
                		<input type="text" name="tit2" id="tit2" value="$itit" class="input-box" size=50 $idrs/>
               			<span class="textfieldRequiredMsg">An Issue Title Is Required!</span>
					</span>
					<br>
					<span id="spryIssueLoc">
						<label for="loc">Location</label>
                		<input type="text" name="loc" id="loc" value="$loc" class="input-box" size=50 $idrs/>
               			<span class="textfieldRequiredMsg">You must specify a location!</span>
					</span>
					<br>
					<span id="spryCat">
						<label for="type">Category</label>
						<select name="type" id="type" onchange="buildSecond(this.form)" size="1" class="select" $tsbox>$typ_box</select>
						<span class="selectInvalidMsg">Please select a valid category.</span>
					</span>
					<br>
					<span id="sprySCat">
						<label for="stype">Type</label>
						<select name="stype" id="stype" size="1" class="select" $stsbox>$styp_box</select>
					<span class="selectInvalidMsg">Please select a valid type.</span>
					</span>
					<br>
					<br>
		      </fieldset>
		  </div>
          
<div id="issdet">
  <fieldset>
    <legend>Issue Descriptions</legend>
	<span id="spryOrigIssue">
		<label for="details2" class="tlabel">Original Issue</label>
		<textarea name="details2" id="details2" cols="100" rows="5" class="tarea" $iirs>$itext</textarea>
		<span class="textareaRequiredMsg">A value is required.</span>
	</span>
	<span id="spryUpdIssue">
		<label for="updates" class="tlabel">Issue Updates</label>
		<textarea name="updates" id="updates" cols="100" rows="5" class="tarea" $iurs>$utext</textarea>
		<span class="textareaRequiredMsg">A value is required.</span>
	</span>
	<span id="spryResIssue">
		<label for="resolv" class="tlabel">Issue Resolution</label>
		<textarea name="resolv" id="resolv" cols="100" rows="5" class="tarea" $irrs>$ctext</textarea>
		<span class="textareaRequiredMsg">A value is required.</span>
	</span>
	<span id="spryResIssue">
		<label class="tlabel">&nbsp;</label>
	</span>
  </fieldset>
</div>
$foot
	</form>
	</div>
	<div id="footer"></div>
</div>
<script type="text/javascript">
<!--
var MenuBar1 = new Spry.Widget.MenuBar("ict_menu", {imgDown:"../SpryAssets/SpryMenuBarDownHover.gif", imgRight:"../SpryAssets/SpryMenuBarRightHover.gif"});
var sprytextfield1 = new Spry.Widget.ValidationTextField("spryIssueTitle", "none", {hint:"Your issue title goes here ..."});
var sprytextfield2 = new Spry.Widget.ValidationTextField("spryIssueLoc", "none", {hint:"Where in school is the issue?"});
var sprytextarea1 = new Spry.Widget.ValidationTextarea("spryOrigIssue", {hint:"Enter your issue details here ....."});
var sprytextarea2 = new Spry.Widget.ValidationTextarea("spryUpdIssue", {hint:"Issue updates will go here ...."});
var sprytextarea3 = new Spry.Widget.ValidationTextarea("spryResIssue", {hint:"Details of the resolution will go here ..."});
var spryselect1 = new Spry.Widget.ValidationSelect("spryCat", {invalidValue:"-1", isRequired:true});
var spryselect2 = new Spry.Widget.ValidationSelect("sprySCat", {invalidValue:"-1", isRequired:true});
var spryselect3 = new Spry.Widget.ValidationSelect("spryPri", {invalidValue:"-1", isRequired:true});
//-->
</script>
<!--[if IE]> </div> <![endif]-->
</body>
</html>

END

}