#!/usr/bin/perl -w
#
# htmlizer.pl
#
# J. Uckelman (uckelman@iastate.edu)
# 27 December 2000
#
my $start = time;
require 5.005;
use DBI;
my $version = 0.1;
my $config = '/home/g1/bin/scripts.conf';
# load the config file
eval { require $config; }; die "$config: $@\n" if $@;
$| = 1; # flush after each print, heightens the drama
# are there targets?
defined $ARGV[0] or die "no target\n";
# connect to game database
$dbh = DBI->connect($dsn, $user, $password);
# iterate over target list
while ($ARGV[0]) {
my $then = time; # then is now
print "making $ARGV[0]: ";
my $target = $ARGV[0];
my $result = '';
die "undefined target\n" unless defined($target);
if ($target eq 'all') {
shift @ARGV;
unshift @ARGV, 'skip me', @ALL;
$result = $#ARGV;
}
elsif (! defined($$target)) { die "undefined target type '$target'\n"; }
elsif ($$target eq 'numerical') {
# set variables for target
my $base = $target; $base =~ s/_/./g;
my $type = $target; $type =~ s/^(.*?)_.*/$1/;
my $header_file = ${"${target}_header_file"};
my $hlinks_file = ${"${target}_hlinks_file"};
my $footer_file = ${"${target}_footer_file"};
my $replace_file = ${"${target}_replace_file"};
my $title = ${"${target}_title"};
my $perpage = ${"${target}_perpage"};
my $page_query = ${"${target}_page_query"};
my $body_query = ${"${target}_body_query"};
my $hist_query = ${"${target}_hist_query"};
my $record_fmt = ${"${target}_record_fmt"};
my $table = ${"${target}_table"};
my $links = ${"${target}_links"};
# get templates
my $header = &getfile($header_file);
my ($link, $here) = split /\n/, &getfile($hlinks_file);
my $footer = &getfile($footer_file);
&lock_tables($dbh, &getusedtables($page_query, $body_query, $hist_query));
# make each page
my @pages = &pages($page_query, $perpage);
my $page;
foreach $page (@pages) {
# make page parts
my $first = ($page - 1) * $perpage + 1;
my $last = $page * $perpage;
my $head = &header($header, $title, $page, $perpage, $first, $last);
my $hlinks = ($links) ? &hlinks($page, $perpage, $here, $link, $base, $type, @pages) : '';
my $body = &body($body_query, 1, $hist_query, $table, $record_fmt, $first, $last);
$body = &replace($body, $replace_file);
my $foot = &footer($footer);
# write output
open OUT, ">$WWW/$base.$page.html" or die "$0: $base.$page.html: $!\n";
print OUT "$head$hlinks$body$hlinks$foot";
close OUT;
print '.';
}
$result = 'ok';
}
elsif ($$target eq 'single') {
# set variables for target
my $base = $target; $base =~ s/_/./g;
my $title = ${"${target}_title"};
my $header_file = ${"${target}_header_file"};
my $footer_file = ${"${target}_footer_file"};
my $replace_file = ${"${target}_replace_file"};
my $body_query = ${"${target}_body_query"};
my $hist_query = ${"${target}_hist_query"};
my $record_fmt = ${"${target}_record_fmt"};
my $table = ${"${target}_table"};
# get templates
my $header = &getfile($header_file);
my $footer = &getfile($footer_file);
&lock_tables($dbh, &getusedtables($body_query, $hist_query));
# make page parts
my $head = &header($header, $title, 0, 0, 0, 0);
my $body = &body($body_query, $hist_query eq '' ? 0 : 1, $hist_query, $table, $record_fmt, 0, 0);
$body = &replace($body, $replace_file);
$body .= '
' if $body !~ m/
$/s;
my $foot = &footer($footer);
# write output
open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
print OUT "$head$body$foot";
close OUT;
print '.';
$result = 'ok';
}
elsif ($$target eq 'officers') {
my $base = $target; $base =~ s/_/./g;
my $title = ${"${target}_title"};
my $header_file = ${"${target}_header_file"};
my $footer_file = ${"${target}_footer_file"};
# get templates
my $header = &getfile($header_file);
my $footer = &getfile($footer_file);
&lock_tables($dbh, 'officer', 'name');
# make page parts
my $head = &header($header, $title, 0, 0, 0, 0);
# get offices
my $sth1 = $dbh->prepare("select distinct office from officer order by office");
$sth1->execute;
my $body = '';
for (my ($office) = $sth1->fetchrow_array; defined($office); ($office) = $sth1->fetchrow_array) {
$body = "$body$office";
my $sth2 = $dbh->prepare("select name.name, date_format(officer.start, '%d %b %Y %T'), date_format(officer.end, '%d %b %Y %T'), officer.admin from officer, name where name.id = officer.id and officer.office = '$office' and ((officer.end between name.start and name.end) or (isnull(name.end) and (name.start <= officer.end or isnull(officer.end)))) order by officer.start");
$sth2->execute;
for (my ($name, $start, $end, $admin) = $sth2->fetchrow_array; defined($name); ($name, $start, $end, $admin) = $sth2->fetchrow_array) { $body = "$body- " . ($admin ? 'vacant' : $name) . ", $start - " . (defined($end) ? $end : 'present') . '
'; }
$sth2->finish;
$body = "$body
";
}
$sth1->finish;
my $foot = &footer($footer);
# write output
open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
print OUT "$head$body
$foot";
close OUT;
print '.';
$result = 'ok';
}
elsif ($$target eq 'index') {
# set variables for target
my $base = $target; $base =~ s/_/./g;
my $header_file = ${"${target}_header_file"};
my $footer_file = ${"${target}_footer_file"};
my $title = ${"${target}_title"};
my $lock = ${"${target}_lock"};
my $table = ${"${target}_table"};
my $body_query = ${"${target}_body_query"};
my $index = $target;
$index =~ s/(.*?)_Index$/$1/;
my $index_base = $index;
$index_base =~ s/_/./g;
my $index_perpage = ${"${index}_perpage"};
# get templates
my $header = &getfile($header_file);
my $footer = &getfile($footer_file);
&lock_tables($dbh, &getusedtables($body_query));
# make page parts
my $head = &header($header, $title, 0, 0, 0, 0);
my $body = '';
my $sth = $dbh->prepare($body_query);
$sth->execute;
for (my ($number, $revision, $title) = $sth->fetchrow_array; defined($number); ($number, $revision, $title) = $sth->fetchrow_array) {
my $page = int(($number-1)/$index_perpage) + 1;
$body = "$body\n$number/$revision $title
";
}
$sth->finish;
my $foot = &footer($footer);
# write output
open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
print OUT "$head$body
$foot";
close OUT;
print '.';
$result = 'ok';
}
elsif ($$target eq 'voting') {
# set variables for target
my $base = $target; $base =~ s/_/./g;
my $header_file = ${"${target}_header_file"};
my $footer_file = ${"${target}_footer_file"};
my $title = ${"${target}_title"};
my $table = ${"${target}_table"};
# get templates
my $header = &getfile($header_file);
my $footer = &getfile($footer_file);
&lock_tables($dbh, $table, 'name');
# make page parts
my $head = &header($header, $title, 0, 0, 0, 0);
# get voting dates
my $sth1 = $dbh->prepare("select distinct date from $table order by date");
$sth1->execute;
my $body = '';
for (my ($date) = $sth1->fetchrow_array; defined($date); ($date) = $sth1->fetchrow_array) {
$body = "${body}Voting ending " . `date -d '$date' +'%d %b %Y %T'` . "\n";
my (@office, @number, @revision, $issuelength) = ();
# get issues
$issuelength = 0;
if ($target eq 'Elections_Voting') {
my $sth2 = $dbh->prepare("select distinct office from $table where date = '$date' order by office");
$sth2->execute;
while (my ($tmp) = $sth2->fetchrow_array) { push @office, $tmp; }
$sth2->finish;
for $i (0 .. $#office) { $issuelength = length($office[$i]) if length($office[$i]) > $issuelength; }
}
else {
my $sth2 = $dbh->prepare("select distinct number, revision from $table where date = '$date' order by number, revision");
$sth2->execute;
while (my ($num, $rev) = $sth2->fetchrow_array) { push @number, $num; push @revision, $rev; }
$sth2->finish;
for $i (0 .. $#number) { $issuelength = length("$number[$i]/$revision[$i]") if length("$number[$i]/$revision[$i]") > $issuelength; }
}
# get voters
my $sth2 = $dbh->prepare("select distinct name.name from name, $table where name.id = $table.id and $table.date = '$date' and name.start <= '$date' and (isnull(name.end) or '$date' <= name.end) order by name.name");
$sth2->execute;
my @voter = ();
my ($i, $j);
my $width = 3;
while (my ($tmp) = $sth2->fetchrow_array) { push @voter, $tmp; }
$sth2->finish;
for $i (0 .. 3) {
$body .= ' ' x $issuelength;
for $j (0 .. $#voter) { $body .= ' ' x $width . (length($voter[$j]) <= $i ? ' ' : substr($voter[$j], $i, 1)); }
$body .= "\n";
}
$body .= '-' x (($width+1)*($#voter+1)+$issuelength) . "\n";
if ($target eq 'Elections_Voting') {
# get votes
foreach $i (0 .. $#office) {
$body .= $office[$i] . ' ' x ($issuelength-length($office[$i]));
$sth2 = $dbh->prepare("select $table.vote from name, $table where name.id = $table.id and $table.date = '$date' and name.start <= '$date' and (isnull(name.end) or '$date' <= name.end) and $table.office = '$office[$i]' order by $table.office, name.name");
$sth2->execute;
while (my ($vote) = $sth2->fetchrow_array) { $body .= ' ' x ($width - (length($vote) == 2)) . $vote; }
$sth2->finish;
$body .= "\n";
}
$body .= "\n";
my @candidates = ();
$sth2 = $dbh->prepare("select distinct name.name, $table.vote from $table, name where $table.date = '$date' and $table.vote = name.id and $table.vote <> '-' and name.start <= '$date' and (isnull(name.end) or '$date' <= name.end) order by name.name");
$sth2->execute;
while (my ($name, $number) = $sth2->fetchrow_array) { $body .= "$name, $number\n"; }
$sth2->finish;
}
else {
# get votes
for $i (0 .. $#number) {
$body .= "$number[$i]/$revision[$i]";
$sth2 = $dbh->prepare("select $table.vote from name, $table where name.id = $table.id and $table.date = '$date' and name.start <= '$date' and (isnull(name.end) or '$date' <= name.end) and $table.number = $number[$i] and $table.revision = $revision[$i] order by $table.number, $table.revision, name.name");
$sth2->execute;
while (my ($vote) = $sth2->fetchrow_array) { $body .= ' ' x $width . $vote; }
$sth2->finish;
$body .= "\n";
}
}
$body = "$body\n\n";
}
$sth1->finish;
$body =~ s/\n{2}$//s;
$body = "$body
";
my $foot = &footer($footer);
# write output
open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
print OUT "$head$body$foot";
close OUT;
print '.';
$result = 'ok';
}
else { die "unrecognized target type '$$target'\n"; }
print ".done, ", time - $then, "s\033[60G[ $result ]" unless $target eq 'all';
print "\n";
shift @ARGV;
}
$dbh->disconnect;
print "done, ", time - $start, "s\n";
exit 0;
#
# replace
#
sub replace ($$) {
my ($data, $pairfile) = @_;
my @fr = split /\n/, &getfile($pairfile);
my (@find, @replace);
for (my $i = 0; $i <= ($#fr / 2); $i++) {
$find[$i] = @fr[$i * 2];
$replace[$i] = @fr[$i * 2 + 1];
}
for (my $i = 0; $i <= $#find; $i++) {
eval "\$data =~ s/$find[$i]/sprintf(\"$replace[$i]\")/gesx";
}
return $data;
}
#
# guard
#
sub guard ($) {
my ($str) = @_;
$str =~ s/(["@])/\\$1/g; # guard @'s and "'s
$str =~ s/%/%%/g; # guard %'s from sprintf
return $str;
}
#
# unguard
#
sub unguard ($) {
my ($str) = @_;
$str =~ s/\\\"/\"/g; # unguard "'s
return $str;
}
#
# hlinks
#
sub hlinks ($$$$@) {
my ($curpage, $perpage, $here, $link, $base, $type, @pages) = @_;
my ($tmp, $linkpage);
my $output = '';
foreach $page (@pages) {
my $range = (($page - 1) * $perpage + 1) . '-' . ($page * $perpage);
$linkpage = $page;
$tmp = ($page == $curpage) ? $here : $link;
# interpolate
$tmp = &guard($tmp);
eval "\$tmp = sprintf(\"$tmp\")";
$output = $output . &unguard($tmp);
}
return "" . $output . "\n
";
}
#
# history
#
sub history ($) {
my ($query) = @_;
my ($tmp, $hrev, $date, $event);
my $output = '';
my $sth = $dbh->prepare($query);
$sth->execute;
for (($hrev, $event, $date) = $sth->fetchrow_array; defined($hrev); ($hrev, $event, $date) = $sth->fetchrow_array) { $output = $output . "\n$hrev. $event, $date"; }
$sth->finish;
$output =~ s/^\n//;
return $output;
}
#
# header
#
sub header ($$$$$) {
my ($header, $title, $page, $perpage, $first, $last) = @_;
my $range = "$first-$last";
my $tmp = &guard($header);
eval "\$tmp = sprintf(\"$tmp\")";
return &unguard($tmp);
}
#
# footer
#
sub footer ($) {
my ($footer) = @_;
my $date = `date -u '+%a %d %b %Y %T %Z'`;
my $tmp = &guard($footer);
eval "\$tmp = sprintf(\"$tmp\")";
return &unguard($tmp);
}
#
# body
#
sub body ($$$$$$@) {
my ($body_query, $hist, $hist_query, $table, $record_fmt, $first, $last) = @_;
my ($tmp, @tmp);
my $body = '';
eval "\$tmp = sprintf(\"$body_query\")";
my $sth1 = $dbh->prepare($tmp);
$sth1->execute;
# get column names
my @element = @{$sth1->{NAME}};
# get each rule
for (@tmp = $sth1->fetchrow_array; defined($tmp[0]); @tmp = $sth1->fetchrow_array) {
# use refs to put data into vars named in @element
for (my $i = 0; $i <= $#tmp; $i++) { ${$element[$i]} = $tmp[$i]; }
# make history only if requested and for max revision number
my $history = '';
if ($hist && $revision == &getmaxrev($table, $number)) {
eval "\$tmp = sprintf(\"$hist_query\")";
$history = &history($tmp);
}
# interpolate values into record format
eval "\$body = \$body . $record_fmt";
}
$sth1->finish;
return $body;
}
#
# getmaxrev
#
sub getmaxrev ($$) {
my ($table, $number) = @_;
# get max revision number
my $sth = $dbh->prepare("select max(revision) from $table where number = $number");
$sth->execute;
my ($maxrev) = $sth->fetchrow_array;
$sth->finish;
return $maxrev;
}
#
# pages
#
sub pages ($$) {
my ($page_query, $perpage) = @_;
my ($tmp, @pages);
my @tmp = ();
my $curmaxpage = 0;
my $sth = $dbh->prepare($page_query);
$sth->execute;
while ($tmp = $sth->fetchrow_array) { push @tmp, $tmp; }
$sth->finish;
# add a new page to the list if record is not on current page
foreach (@tmp) { push @pages, $curmaxpage = int(($_ - 1) / $perpage) + 1 if ($_ > $curmaxpage * $perpage); }
return @pages;
}
#
# getusedtables
#
sub getusedtables (@) {
my @table = ();
my ($query, $tmp);
my $clause = '(where|group by|having|order by|limit|procedure)';
# make a list of unique tables queried
foreach $query (@_) {
unless ($query eq '') {
$query =~ m/from ([a-zA-Z0-9 ,]+) $clause/ or die "bad query '$query'";
foreach $tmp (split /, /, $1) { push @table, $tmp unless grep /^$tmp$/, @table; }
}
}
return @table;
}
#
# lock_tables
#
sub lock_tables (@) {
my $dbh = shift @_;
my $tables = join ', ', @_;
# prevent changes to tables while extracting data
$tables =~ s/(,|$)/ write$1/g;
my $SQL = "lock tables $tables";
$dbh->do($SQL) or die "unable to lock tables: $SQL";
return 1;
}
#
# getfile
#
sub getfile ($) {
my ($file) = @_;
open FILE, "<$file" or die "$file: $!\n";
my $data = join '', ;
close FILE;
return $data;
}