#!/usr/bin/perl 

# foreverstats2.pl
# NDC Code Release 1
#
# Connects to the database holding the statistic data and builds
# a web page from it.
# This is old code. The HTML is inlined in it. Hard to read,
# almost impossible to maintain.
# CGI-Parameters:
#       start:          First entry displayed
#       limit:          Number of entries. Defaults to 20.
#       sort:           Sort mode.
#
#   Copyright (C) 2001 Andreas Ulbrich, Ralf Ertzinger (ndccode@ndc.sh)
#
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; either version 2 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

#use strict;
require 5.002;
use DBI;
use CGI;

my $CGIquery;

### Var def ###
$CGIquery = new CGI;
$sort = ($CGIquery->param("sort")?$CGIquery->param("sort"):'0');
$limit = ($CGIquery->param("limit")?$CGIquery->param("limit"):'20');
$start = ($CGIquery->param("start")?$CGIquery->param("start"):'0');
$back = ($CGIquery->param("back")?$CGIquery->param("back"):'0');

# Rebuild database, if necessary
open (FIRST, "< first.run") || die "Can't open first.run\n";
while (<FIRST>) {
	if ($_ == 1) {
		close (FIRST);
		open (FIRST, "> first.run") || die "Can't open first.run\n";
		print FIRST "0\n";
		close (FIRST);
		{$update = `databasectrl.sh`}
	}
}
close (FIRST);

$database = "stats";
$data_source = "DBI:mysql:$database";
$username = "";
$password = "";
$dbh = DBI->connect( $data_source, $username, $password) or die "Can't connect to $data_source\n"; #: $dbh->errstr\n";

$query = $dbh->prepare("SELECT nick FROM PlayerStats WHERE fe_skill > 0");
$query->execute;
$rows = $query->rows;
$query->finish;


$query = $dbh->prepare("SELECT nick FROM PlayerStats");
$query->execute;
$rowsall = $query->rows;
$query->finish;



$query = $dbh->prepare("SELECT nick ,fe_skill, fe_kills, fe_victims, fe_suicides, fe_mapsplayed, fe_mapswon, fe_time, fe_fph, date_format(LastSeen, '%d.%m.%Y') as date_europe FROM PlayerStats WHERE fe_skill > 0 ORDER by fe_skill DESC LIMIT $start,$limit");
$query->execute;

while ($row_hash = $query->fetchrow_hashref) {
	$name = $row_hash->{nick};
	$nick{$name} = $name;
	$skill{$name} = $row_hash->{fe_skill};
	$kills{$name} = $row_hash->{fe_kills};
	$victims{$name} = $row_hash->{fe_victims};
	$suicides{$name} = $row_hash->{fe_suicides};
	$maps{$name} = $row_hash->{fe_mapsplayed};
	$wonmaps{$name} = $row_hash->{fe_mapswon};
	$time{$name} = $row_hash->{fe_time};
	$fph{$name} = $row_hash->{fe_fph};
	$lastseen{$name} = $row_hash->{date_europe};
	unless(defined($lastseen{$name})) { $lastseen{$name} = '--.--.--'; }
}
$query->finish;
$dbh->disconnect;



print "Content-Type: text/html\n\n\n";


###### Sortieren #####

# By default, sort by rank

if ($sort eq "0") {
  @sortkey = sort { $skill{$b} <=> $skill{$a} } (keys %nick);
  $sortname = "Skill";
  $darstellung = "\= Kills \/ \( 1 \+ Victims \+ Suicides \) \* \( 1 \+ \( Maps-won \/ Maps-played \)";
}



if ($sort eq "1") {
  @sortkey = sort { $nick{$a} cmp $nick{$b} } (keys %nick);
  $sortname = "Nick";
  $darstellung = "";
}

if ($sort eq "2") {
  @sortkey = sort { $maps{$b} <=> $maps{$a} } (keys %maps);
  $sortname = "gespielten Maps";
  $darstellung = "";
}

if ($sort eq "3") {
  @sortkey = sort { $kills{$b} <=> $kills{$a} } (keys %kills);
  $sortname = "Kills";
  $darstellung = "";
}

if ($sort eq "4") {
  @sortkey = sort { $victims{$b} <=> $victims{$a} } (keys %victims);
  $sortname = "Victims";
  $darstellung = "";
}

if ($sort eq "5") {
  @sortkey = sort { $suicides{$b} <=> $suicides{$a} } (keys %suicides);
  $sortname = "Suicides";
  $darstellung = "";
}

if ($sort eq "6") {
  @sortkey = sort { $fph{$b} <=> $fph{$a} } (keys %fph);
  $sortname = "FPH";
  $darstellung = "\= Kills \/ \(Sec \/ 3600 \)";
}

if ($sort eq "7") {
  @sortkey = sort { $time{$b} <=> $time{$a} } (keys %nick);
  $sortname = "Zeit";
  $darstellung = "";
}

if ($sort eq "8") {
  @sortkey = sort { $wonmaps{$b} <=> $wonmaps{$a} } (keys %nick);
  $sortname = "gewonnenen Maps";
  $darstellung = "";
}
## Ausgabe ##
### Header ###
print '<HTML><HEAD><TITLE>Cardassia - Hall of Fame</TITLE>';
print '<META NAME="Andreas Ulbrich \+ Ralf Ertzinger">';
print '</HEAD><BODY BGCOLOR="#FFFFFF">';
print "\n<CENTER><img SRC=\"http\:\/\/www.ndc.sh\/graphics\/halloffame_logo.jpg\" WIDTH=\"500\" HEIGHT=\"90\" BORDER=\"0\"ALT=\"\[SERVER Hall of Fame\]\"><\/CENTER>\n";

print "<CENTER><b>Erfasste Spieler gesamt: $rowsall <BR>Anzahl der Spieler mit mehr als 10 Maps: $rows<\/B><BR>\n";
if ($back == 0) {
	print "<b><a href =\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=0\&limit\=$rows\&back\=1\">$rows Spieler anzeigen<\/A><\/B><\/CENTER><\/BR>\n";
}
else {
	print "<b><a href =\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=0\&limit\=20\&back\=0\">Die ersten 20 anzeigen<\/A><\/B><\/CENTER><\/BR>\n";
}


##### Sortiert nach sortkey ########

print "<P>\&nbsp\;<P><UL><b>Sortiert nach $sortname $darstellung<\/b>\n";
print "<table BORDER=\"0\">\n";
print "<tr>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b>Rang<\/b><\/td>
\n";
print "<td WIDTH=\"120\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=1\&limit=$limit\&back=$back\">Name<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=0\&limit=$limit\&back=$back\">Skill<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=6\&limit=$limit\&back=$back\">FPH<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=2\&limit=$limit\&back=$back\">Maps gespielt<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=8\&limit=$limit\&back=$back\">Maps gewonnen<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=3\&limit=$limit\&back=$back\">Kills<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=4\&limit=$limit\&back=$back\">Victims<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=5\&limit=$limit\&back=$back\">Suicides<\/a><\/b><\/td>\n";
print "<td WIDTH=\"50\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b><a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort\=7\&limit=$limit\&back=$back\">Zeit (Min)<\/a><\/b><\/td>\n";
print "<td WIDTH=\"100\" ALIGN=\"CENTER\" BGCOLOR=\"\#FFFFCC\"><b>Zuletzt gespielt<\/b><\/td>\n";
print "<\/tr>\n";

$count = $start;
foreach $name (@sortkey)
{
	
       $count ++;
       print "<tr>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"+0\">$count<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#CCCCCC\"><font size =\"+0\">";
       print "<a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/playerstats.pl\?nick\=";
       print make_compatible($nick{$name});
       print "\">";
       print "<font color\=\"#000000\">";
       print make_html($nick{$name});
       print "<\/font>";
       print "<\/a><\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$skill{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$fph{$name}<\/font><\/td>\n";

       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$maps{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$wonmaps{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$kills{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$victims{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$suicides{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$time{$name}<\/font><\/td>\n";
       print "<td ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\"><font size =\"-1\">$lastseen{$name}<\/font><\/td>\n";
       print "<\/tr>\n";
}

print "<\/TABLE><\/UL><P>\n";

if ($back == 0) {
  print "<CENTER><TABLE BORDER=\"0\">\n";
  print "<tr>\n";
  print "<td WIDTH=\"200\" ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\">";
  if ($start != 0) {
    $start_prev = $start - $limit;
    if ($start_prev < 0) {$start_prev = 0;}
    print "<a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort=$sort&start=$start_prev&limit=$limit&back=0\">";
    print "Zur&uuml;ck";
    print "<\/a>\n";
  } else {
    print "Zur&uuml;ck";
  }
  print "<\/td>\n";
  print "<td WIDTH=\"200\" ALIGN=\"CENTER\" BGCOLOR=\"\#EEEEEE\">";
  if ($start + $limit < $rows) {
    $start_next = $start + $limit;
    print "<a href=\"http\:\/\/www.ndc.sh\/cgi-bin\/quake\/foreverstats2.pl\?sort=$sort&start=$start_next&limit=$limit&back=0\">";
    print "Weiter";
    print "<\/a>\n";
  } else {
    print "Weiter";
  }
  print "<\/td>\n";
  print "<\/tr>\n";
  print "<\/TABLE><\/CENTER>\n";
}

print "<P><FONT SIZE=\"-2\"><DIV ALIGN=right>Skripting by NDC Code<BR><A href=\"http://www.ndc.sh\" target=\"_top\">www.ndc.sh</A></DIV></FONT><BR>\n";
print '</BODY></HTML>';


############### Subroutines ###########

sub make_html {

  $input = shift;
#  $input =~ s/&/und/g;
  $input =~ s/</&lt\;/g;
  $input =~ s/>/&gt\;/g;
  $input =~ s/"/&quot\;/g;
  $input =~ s/\x1c/&middot;/g;
#  $input =~ s/\|/pipe/g;
  return $input;
}

sub make_compatible {

  $input = shift;
  $input =~ s/%/%25/g;
  $input =~ s/\+/%2B/g;
  $input =~ s/ /+/g;
  $input =~ s/,/%2C/g;
  $input =~ s/</%3C/g;
  $input =~ s/>/%3E/g;
  $input =~ s/#/%23/g;
  $input =~ s/\[/%5B/g;
  $input =~ s/\]/%5D/g;
  $input =~ s/\//%2F/g;
  $input =~ s/\|/%7C/g;
  $input =~ s/\?/%3F/g;
  $input =~ s/\x1c/%1C/g;

  return $input;
}
