#!/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 .= '<hr>' if $body !~ m/<hr>$/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<b>$office</b><ol>";
			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<li>" . ($admin ? 'vacant' : $name) . ", $start - " . (defined($end) ? $end : 'present') . '</li>'; }

			$sth2->finish;
			$body = "$body</ol>";
		}

		$sth1->finish;

		my $foot = &footer($footer);

		# write output
		open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
		print OUT "$head$body<hr>$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<a href=\"$index_base.$page.html#$number.$revision\">$number/$revision</a> $title<br>";
		}	

		$sth->finish;

		my $foot = &footer($footer);
		
		# write output
		open OUT, ">$WWW/$base.html" or die "$0: $base.html: $!\n";
		print OUT "$head$body<hr>$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 = "<pre>$body</pre><hr>";

		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 "<center>" . $output . "</center>\n<hr>";
}


#
# 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 '', <FILE>;
	close FILE;
	return $data;
}
