#!/usr/local/bin/perl -w

use DBI;
use CGI qw(:standard);
print header;
print start_html;




$ENV {'ORACLE_HOME'}= '/oracle/u01';
$host="neptune";
$sid="ora1";
$username="tayloja1";
$password="the.best";




$sport = 'basketball';


$dbh = DBI->connect( "dbi:Oracle:host=$host;sid=$sid",$username, $password)|| die "Can't connect to Oracle";
 

$sth = $dbh->prepare("SELECT year from compsize  WHERE sport = \'$sport\'")|| die "couldn't  prepareSQL statement";
$sth->execute || die "can't execute sql statement";




print "<table border=1> \n";
print "<tr> \n"; 
print "<th> List of info </th> \n"; 
print "<tr> \n";


my ($thisyear)= $sth->fetchrow_array();

$sth->finish;


print "<tr> \n";
print "<td>$thisyear</td> \n";
print "</tr> \n";

my @deplist = ('Business School', 'Admin/Estates', 'CEAC', 'Civ Eng', 'Combined Honours', 'CSAM', 'EEAP', 'Mod Lang', 'Mech Eng', 'Pharmacy', 'Vision Sciences');



my @sportlist = ('campus relay', 'archery',  'badminton', 'basketball',  'cricket', 'football','hockey','netball','rugby', 'snooker', 'squash','swimming', 'table tennis','tennis','tug of war', 'volleyball' );



print "<table border=1> \n";
print "<tr> \n"; 
print "<th> List of info </th> \n"; 
print "<tr> \n";

$sth4 = $dbh->prepare("SELECT department from tempres WHERE year = \'$thisyear\' ORDER BY besttwelve DESC ")|| die "couldn't  prepare SQL statement";

$sth4->execute || die "can't execute sql statement";


 my $thisno = 0;
 my @newdeplist;


     while (@row2 = $sth4->fetchrow_array()) {

      $newdeplist[$thisno] = $row2[0];

      $thisno++;

     }

print "<FORM ACTION='/http://www.cs.aston.ac.uk/cgi-bin/cgiwrap/tayloja1/deleteseason.pl/' METHOD=/'POST/'>\n";
print "<table border=1> \n";

for $newdep(0..$#newdeplist){
        print "<tr>\n";
	my $position = $newdep + 1;
print "<td>$position</td> \n";
print "<td>$newdeplist[$newdep]</td> \n";



	for $sport(0..$#sportlist){
   
    $sth5 = $dbh->prepare("SELECT score from results WHERE year = \'$thisyear\' AND sport = \'$sportlist[$sport]\' AND department = \'$newdeplist[$newdep]\'")|| die "couldn't  prepare SQL statement";

    $sth5->execute || die "can't execute sql statement";

    if ($sth5->rows != 0){
          
	 my @row4;
        while (@row4 = $sth5->fetchrow_array()) {

                $thisscore = $row4[0];
                
             print "<td>$thisscore</td> \n";
            
            



	}    
  
     }
    elsif ($sth5->rows == 0) {
      print "<td>NA</td> \n";
  }

}

$sth6 = $dbh->prepare("SELECT total,besttwelve from tempres WHERE year = \'$thisyear\'  AND department = \'$newdeplist[$newdep]\' ")|| die "couldn't  prepare SQL statement";

           $sth6->execute || die "can't execute sql statement";

           my @row3;
            while (@row3 = $sth6->fetchrow_array()) {

                $thistotal = $row3[0];
                $twelve = $row3[1];

             print "<td>$thistotal</td> \n";
             print "<td>$twelve</td> \n";
            }