#!c:/perl/bin/perl
use CGI;
use DBI;
my $form=CGI->new;
$d0=$form->param(year);
print "Content-type:text/html\n\n";
print <<"END";
<html>
<head>
<style type="text/css">
<!--
body,td {font-size:15px; line-height:120%; padding:2px;}
.t1 {background-color:#000099; font-weight:bold; color:#ffffff; text-align:center;}
.t2 {background-color:#c9c9ff; font-weight:bold; color:#ffffff; text-align:center;}
.t3 {background-color:#ffffcf;}
-->
</style>
</head>
<body>
<table border="1" style="text-align:center;" align="center">
<caption style="font-size:32px; font-weight:bold;">Drivers Point Ranking</caption>
END
$dbh=DBI->connect("DBI:mysql:database=formula1");
$sql="select max(rnd) from (pack1 inner join race on p1_id=r_event) where p1_year=$d0";
$sth = $dbh->prepare($sql);
$sth->execute;
@a=$sth->fetchrow_array;
$maxrnd=$a[0];
$maxpoint=0;
$sql="
select r_seat,d_name,t_name,e_name,tyre,sum(point) as total,max(point) as pos from
(select * from race inner join
(select * from (((pack2 inner join driver on p2_dri=d_id) inner join const on p2_con=con_id)
inner join team on con_team=t_id) inner join engine on con_en=e_id
) as temp1
on r_seat=p2_id where r_event in (select p1_id from pack1 where p1_year=$d0)
) as temp2
left join (select * from point where pt_year=$d0) as temp3 on result=pt_rslt
group by r_seat order by total desc,pos desc
"; #このSQLが処理１前半
$sth = $dbh->prepare($sql);
$sth->execute;
$rows1= $sth->rows;
for ($i=0; $i<$rows1; $i++) {
@a=$sth->fetchrow_array;
$seat[$i] =$a[0];
$driver[$i]=$a[1];
$team[$i] =$a[2];
$engine[$i]=$a[3];
$tyre[$i] =$a[4];
$total[$i] =$a[5]; if ($total[$i] eq "") {$total[$i] = "0";}
if ($maxpoint<$a[5]) {$maxpoint=$a[5];} #処理２（GD用）
}
@buf=();
@legend=();
for ($i=0; $i<$rows1; $i++) {
$sql="
select point from (select * from pack1 where p1_year=$d0) as temp1
left join
(select * from (((race left join (select * from point where pt_year=$d0) as temp2 on result=pt_rslt)
inner join pack2 on r_seat=p2_id) inner join driver on p2_dri=d_id) where r_seat=$seat[$i]
) as temp3
on p1_id=r_event
order by rnd
"; #このSQLが処理１後半
$sth = $dbh->prepare($sql);
$sth->execute;
$rows2= $sth->rows;
if ($i == 0) {
$temp="";
print "
<tr class=t1><td>Rank</td><td>Driver</td><td>Constractor</td><td>Engine</td><td>Tyre</td>
";
for ($j=0; $j<$rows2; $j++) {
$rnd=$j+1;
$class=""; if ($rnd > $maxrnd) {$class=" class=t2";}
print "<td$class>$rnd</td>";
$temp=$temp.",$rnd";
}
print "<td>Total</td></tr>\n";
$temp=~s/,//;
push(@buf,$temp);
}
$pos=$i+1;
$class=""; if ($pos % 2 == 0) {$class=" class=t3";}
print "
<tr$class><td>$pos</td><td>$driver[$i]</td><td>$team[$i]</td><td>$engine[$i]</td><td>$tyre[$i]</td>
"; #処理２（html用）
push(@legend,$driver[$i]); #処理２（GD用）
$tmp_p="";
$point=0;
for ($j=0; $j<$rows2; $j++) {
@a=$sth->fetchrow_array;
$rnd=$j+1;
$point=$point+$a[0];
if ($point > $maxpoint) {$maxpoint=$point;}
if ($rnd > $maxrnd) {$point="";}
$tmp_p=$tmp_p.",$point";
if ($a[0] eq "") {$a[0]="-";}
print "<td>$a[0]</td>";
}
print "<td>$total[$i]</td></tr>\n";
$tmp_p=~s/,//;
push(@buf,$tmp_p); #処理２（GD用）
}
$sth->finish;
$dbh->disconnect;
print <<"END";
</table>
<br>
<div align="center"><img src="graph.cgi?max=$maxpoint&lgd=@legend&data=@buf"></div> #処理３
</body>
</html>
END
