#!/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 = ' '.$schName.' Teacher Homepage Listing '; if (not $num) { print "

No teachers

"; } 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 .= "
"; $html .= "
"; $html .= ""; $html .= "
"; $html .= "More than one teacher matched $teacher"; $html .= "
NOTE: If teachers have created web pages you will be able to click on their name...
"; $html .= "
    "; 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 = ""; $x = ''; } elsif ( -e "$baseDIR/$_htm" ) { $a = ""; $x = ''; } elsif ( -e "$baseDIR/$def_html" ) { $a = ""; $x = ''; } $html .= "
  1. $a $TEACHERS{$t} $x
    2. "; } $html .= "
"; $html .= "
"; print $html; }