#!/usr/bin/perl # Display an index for retrieving spreadsheet friendly versions of the data # Bruno Wolff III # Last revised October 13, 2012 # While areaid and gameid can't currently contain any uri specials, I want # assume they can here, so I if tweak the database schema, things don't # break here. The problem characters that might eventually be allowed # are '#', '/' and '?'. # Use unbuffered output as there are some long delays while doing queries select(STDOUT); $| = 1; $" = ''; use Pg; # Use this to clean stuff extracted from the database for html output sub clean(@) { local $str = "@_"; $str =~ s/&/&/g; $str =~ s//>/g; $str =~ s/"/"/g; return $str; } # Use this to convert REQUEST_URI to unescaped string sub urldecode(@) { local $str = "@_"; $str =~ s/\%[0-9a-fA-F]{2}/$urldhash{$&}/eg; return $str; } # Use this to make sure urls don't contain url specials sub urlencode(@) { local $str = "@_"; $str =~ s/[^-_A-Za-z0-9.$+!*'(),]/$urlehash{$&}/eg; return $str; } # One time build of data used by url decode sub urlinit() { my $c; my $f; my $i; for ($i=0; $i<=255; $i++) { $c = chr($i); $f = sprintf('%%%.2X', $i); $urldhash{$f} = $c; $urldhash{lc($f)} = $c; $urlehash{$c} = $f; } } urlinit; if ($ENV{REQUEST_METHOD} ne '' && $ENV{REQUEST_METHOD} ne 'GET' && $ENV{REQUEST_METHOD} ne 'HEAD') { $meth = clean($ENV{REQUEST_METHOD}); $uri = clean($ENV{REQUEST_URI}); print < 501 Method Not Implemented

Method Not Implemented

Method '$meth' not implented for $uri. EOF exit; } print <<"EOF"; content-type: text/html; charset=UTF-8 Spreadsheet friendly versions of the data EOF { local *INPUT; if (open(INPUT, "links.html")) { print while (); close INPUT; } } print << "EOF";

Spreadsheet friendly versions of the data

EOF # First try to connect $conn = Pg::connectdb('dbname=area'); if ($conn->status != PGRES_CONNECTION_OK) { print << "EOF"; Unable to connect the AREA database. EOF { local *INPUT; if (open(INPUT, "links.html")) { print while (); close INPUT; } } { local *INPUT; if (open(INPUT, "sig.html")) { print while (); close INPUT; } } print << "EOF"; EOF exit; } $result = $conn->exec("begin"); if ($result->resultStatus != PGRES_COMMAND_OK) { $uri = clean($ENV{REQUEST_URI}); print <<"EOF"; content-type: text/html; charset=UTF-8 status: 404 File Not Found 404 File Not Found

404 File Not Found

'$uri' not found. Unable to start a transaction. EOF exit; } $result = $conn->exec("set transaction isolation level serializable"); if ($result->resultStatus != PGRES_COMMAND_OK) { $uri = clean($ENV{REQUEST_URI}); print <<"EOF"; content-type: text/html; charset=UTF-8 status: 404 File Not Found 404 File Not Found

404 File Not Found

'$uri' not found. Unable to serialize transaction. EOF exit; } print << "EOF";

Caveats

Note that data obtained from here has been "cleaned" by removing some inconsistancies. However it is possible that the wrong choice was made when doing that. Also suppressed names will show up as a last name of "Withheld" and no first name. This data is publicly accessible, so the names have to be suppressed here as well.

Ranking isn't needed so it isn't included. Other interested players are not in the game sheets. A complete list of other interested players is available separately. There is no separate designation for active, inactive, or expired people as that is determined by their active date. People are listed in order by last name so you can find them.

The sheet names used will be the current internal game codes. They may not always match the original sheet name sent in by the maintainer. There are also some games that no one has a sheet for because they are WBC tournament games for which we have never received any results.

Generating an excel file for all of the games can take a couple of minutes. EOF print "

Combined game sheets in excel format

\n"; print "
  1. Any admin\n"; print "
    1. ALL - any game type\n"; $result = $conn->exec("select rtypes.rtype, descr from rtypes, games where rtypes.rtype = games.rtype group by rtypes.rtype, descr having count(*) > 0 order by lower(rtypes.rtype)"); if ($result->resultStatus == PGRES_TUPLES_OK) { while (@row = $result->fetchrow) { $type = clean($row[0]); $descr = clean($row[1]); print "
    2. $type - $descr\n"; } } @contacts = (); $result = $conn->exec("select contacts.contact from contacts, games where contacts.contact = games.contact group by contacts.contact having count(*) > 0 order by lower(contacts.contact)"); if ($result->resultStatus == PGRES_TUPLES_OK) { while (@row = $result->fetchrow) { push @contacts, $row[0]; } } foreach $contact (@contacts) { $cc = clean($contact); $qcontact = $contact; $qcontact =~ s/\\|'/\\$&/g; print "
  2. $cc\n"; print "
    1. ALL - any game type\n"; $result = $conn->exec("select rtypes.rtype, descr from rtypes, games where rtypes.rtype = games.rtype and games.contact = '$qcontact' group by rtypes.rtype, descr having count(*) > 0 order by lower(rtypes.rtype)"); if ($result->resultStatus == PGRES_TUPLES_OK) { while (@row = $result->fetchrow) { $type = clean($row[0]); $descr = clean($row[1]); print "
    2. $type - $descr\n"; } } } print "
\n"; print "

Individual game sheets in tab separated values format

\n"; $result = $conn->exec("select gameid, title from games order by lower(title)"); if ($result->resultStatus != PGRES_TUPLES_OK) { print "Unable to access the games table.\n" } elsif ($result->ntuples <= 0) { print "No games were found.\n"; } else { print "\n\n"; while (@row = $result->fetchrow) { $code = clean(urlencode($row[0])); $title = clean($row[1]); print "
Title
$title\n"; } print "
\n"; } print <); close INPUT; } } { local *INPUT; if (open(INPUT, "sig.html")) { print while (); close INPUT; } } print << "EOF"; EOF