#!/usr/bin/perl

use CGI qw(:standard);
use DBD::Oracle;

$|=1;

my $user = 'psnavigator';
my $pass = 'psnavigate';

my $conn = "dbi:Oracle:HOST=10.5.7.20;SID=PSPRODDB;port=1521";
my $dbh = DBI->connect ( $conn, $user, $pass ) || die "ERROR: $DBI::errstr\n";

$baseDIR = '/var/www/html/teacherPages/DATA';

$teacher = param('teacher');
$sch = uc(param('sch'));

$lastName = $teacher;
($lastName,$firstName) = split(/,/,$teacher) if ($teacher =~ /,/);
($firstName,$lastName) = split(/\./,$teacher) if ($teacher =~ /[a-z]\.[a-z]/i);
$lastName =~ s/ //g;
$firstName =~ s/ //g;

$LN = $lastName . '%';
$FN = $firstName . '%';

$q = "select TEACHERLOGINID, LASTFIRST, s.NAME as SCHNAME from Teachers t
INNER JOIN Schools s on s.school_number = t.schoolid
where upper(s.Abbreviation) = upper(?)
and t.status = 1
and upper(t.Last_Name) like upper(?)
and upper(t.First_Name) like upper(?)
and t.TEACHERLOGINID is not null
";

$qz = $dbh->prepare("ALTER SESSION SET CURSOR_SHARING=exact");
$qz->execute();
$qh = $dbh->prepare($q);
$qh->execute($sch,$LN,$FN);

while ($r = $qh->fetchrow_hashref()) {
	($ID = $r->{'TEACHERLOGINID'}) =~ s/^f//;
	$TEACHERS{$ID} = $r->{'LASTFIRST'};
	$schName = $r->{'SCHNAME'} if not $schName;
}

$num = scalar keys %TEACHERS;

if (not $num) {
	($firstName = $lastName) =~ s/^(.).*/$1/;
	$lastName =~ s/^.//; 

	$LN = $lastName . '%';
	$FN = $firstName . '%';

	$q = "select TEACHERLOGINID, LASTFIRST, s.NAME as SCHNAME from Teachers t
		INNER JOIN Schools s on s.school_number = t.schoolid
		where upper(s.Abbreviation) = upper(?)
		and t.status = 1
		and upper(t.Last_Name) like upper(?)
		and upper(t.First_Name) like upper(?)
		and t.TEACHERLOGINID is not null
		";

	$qh = $dbh->prepare($q);
	$qh->execute($sch,$LN,$FN);

	while ($r = $qh->fetchrow_hashref()) {
		($ID = $r->{'TEACHERLOGINID'}) =~ s/^f//;
		$TEACHERS{$ID} = $r->{'LASTFIRST'};
		$schName = $r->{'SCHNAME'} if not $schName;
	}

	$num = scalar keys %TEACHERS;

}

$myHeaders = '
<head>
	<title>'.$schName.' Teacher Homepage Listing</title>
	<link href="/default.css" rel="stylesheet">
	<link href="/'.$sch.'/default.css" rel="stylesheet">
</head>
<body>
';


if (not $num) {
	print "<p>No teachers</p>";
} elsif( $num == 1) {
	$_htm = "$sch/$ID/index.htm";
	$_html = "$sch/$ID/index.html";
	$def_html = "$sch/$ID/default.html";
	if ( -f "$baseDIR/$_html" ) {
		print redirect "/$_html";
	} elsif ( -f "$baseDIR/$_htm" ) {
		print redirect "/$_htm";
	} elsif ( -f "$baseDIR/$def_html" ) {
		print redirect "/$def_html";
	} else {
		print redirect "http://$sch.k12northstar.org";
	}
} else {
	print header;
	$html = $myHeaders;
	$html .= "<div id='outer_canvas'>";
	$html .= "<div id='canvas'>";
	$html .= "<div id='header'> <h1 class='site-name'><a href='http://$sch.k12northstar.org'> $schName </a></h1> </div>";
	$html .= "<div id='main'>";
	$html .= "<span class='sub_header'>More than one teacher matched <span class='teachername'>$teacher</span></span>";
	$html .= "<div id='note'>NOTE: If teachers have created web pages you will be able to click on their name...</div>";
	$html .= "<div id='list'><ol>";
	foreach $t (sort {$TEACHERS{$a} cmp $TEACHERS{$b}} keys %TEACHERS) {
		$a = $x = '';
		$_htm = "$sch/$t/index.htm";
		$_html = "$sch/$t/index.html";
		$def_html = "$sch/$t/default.html";
		if ( -e "$baseDIR/$_html" ) {
			$a = "<a href='/$_html'>";
			$x = '</a>';
		} elsif ( -e "$baseDIR/$_htm" ) {
			$a = "<a href='/$_htm'>";
			$x = '</a>';
		} elsif ( -e "$baseDIR/$def_html" ) {
			$a = "<a href='/$def_html'>";
			$x = '</a>';
		}
		$html .= "<li>$a $TEACHERS{$t} $x</li>";
	}
	$html .= "</ol>";
	$html .= "</div></div></div></body></html>";
	print $html;
}
