#!/usr/bin/perl -T
#
# pitchfork reviews project:
# this is a CGI that provides statistics for each word,
# various ways to organize and choose the words you're viewing,
# and links to other features for each word.
#
# loren jan wilson, end of 2003
#


use strict;
use CGI qw(:standard -nosticky);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Reviews;

$CGI::POST_MAX=1024 * 15;  # max 20K posts
$CGI::DISABLE_UPLOADS = 1;  # no uploads
$ENV{PATH} = "";

my $q = new CGI;
print $q->header(-expires=>'now');
print $q->start_html();

my $begin_time = time();

my $dbh = make_db_handle;
my $sort = $q->param('sort') || "positivity";
$sort = substr($sort, 0, 30);
$sort =~ s/[^A-Za-z,]//g;
$sort = lc($sort);
$sort =~ s/,/, /g;
$sort =~ s/desc/ desc/g;
my $order = $q->param('order');
if (! $order) {
	if ($sort eq "total") { $order = "tot DESC"; }
	elsif ($sort eq "positivity") { $order = "pwr DESC"; }
	elsif ($sort eq "negativity") { $order = "nwr DESC"; }
	else { $order = $sort }
}

my $limit = $q->param('limit') || 100; #only return 100 at a time
$limit =~ s/[^\d]//g;
$limit = substr($limit, 0, 4);
my $offset = $q->param('offset') || 0;
$offset =~ s/[^\d]//g;
$offset = substr($offset, 0, 7);
# not interested in words that occur less than the "floor"
my $floor = $q->param('floor') || 60; 
$floor =~ s/[^\d]//g;
$floor = substr($floor, 0, 4);
if ($floor < 5) {
	$floor = 5;
}
# also, not interested in words that occur more than the "ceiling"
my $ceiling = $q->param('ceiling') || 10000; 
$ceiling =~ s/[^\d]//g;
$ceiling = substr($ceiling, 0, 10);

my $capitalization = $q->param('capitalization') || "lowercase";
my $cap_string = "";
if ($capitalization eq "lowercase") { 
	$cap_string = qq/AND word REGEXP "^[a-z]"/;
} elsif ($capitalization eq "capitalized") {
	$cap_string = qq/AND word REGEXP "^[A-Z]"/;
}

# we can now take a class (usually these are links from class.pl)
my $class = $q->param('class');
$class = substr($class, 0, 4);
my $class_string = "";
my $class_db = "";
if ($class) {
	$class_string = qq/
		AND word.id = word_class.word_id 
		AND word_class.class_id = $class
	/;
	$class_db = ", word_class";
}

# print a form giving the current settings and offering a way
# to change them.
print(
	$q->start_form(-method=>'GET'),
	#"sort order:", $q->textfield(-name=>'sort', -default=>$order, -override=>1, -size=>20),
	"sort order:", $q->popup_menu(-name=>'sort', -values=>['positivity', 'negativity', 'total']),
	" limit:", $q->textfield(-name=>'limit', -default=>$limit, -size=>5),
	" floor (min tot):", $q->textfield(-name=>'floor', -default=>$floor, -override=>1, -size=>5),
	" ceiling (max tot):", $q->textfield(-name=>'ceiling', -default=>$ceiling, -size=>5),
	" offset:", $q->textfield(-name=>'offset', -default=>$offset, -size=>5),
	" capitalization:", $q->popup_menu(-name=>'capitalization',
		-values=>['lowercase','capitalized', 'all']),
	" ",$q->submit(-value=>'Submit'),
	$q->end_form,
	"\n");
# print the "next" button
my $firstword = $offset+1; 
my $lastword = $limit+$offset;
my $new_url = $q->url(-relative=>1, -query=>1);
if ($new_url =~ /offset=(\d+)/) {
	my $new_offset = $1 + $limit;
	$new_url =~ s/offset=(\d+)/offset=$new_offset/;
} else {
	my $new_offset = $offset + $limit;
	$new_url = $new_url."?offset=$new_offset";
}
print qq%You are viewing words $firstword - $lastword. <a href="$new_url">next $limit >></a>%;
print "<br>", "For an explanation of these statistics, select a word and click on it.<br>(word links are on the right side of the screen)";
print "<pre><ol start=$firstword>\n";

# get words
my $words = dbquery($dbh, qq/
	SELECT word.id, word.word FROM word,statistics $class_db
	WHERE statistics.word_id = word.id
	# AND length(word) > 2
	AND tot BETWEEN $floor AND $ceiling
	$cap_string
	$class_string
	ORDER BY $order
	LIMIT $offset,$limit
/);
# get fields
#my $fields_table = dbquery($dbh, qq/SHOW COLUMNS FROM statistics/);
#my @fields;
#foreach my $row (@$fields_table) {
#	push @fields, $$row[0];
#}
#my $trash = shift @fields;
my @fields = qw(tot pos neu neg pwr nwr avg art aut);

my $i;
foreach my $wordref (@$words) {
	my $word_id = $$wordref[0];
	my $word = $$wordref[1];
	print "<li><b>$word</b> -> ";
	my $selectstring = join(",", @fields);
	my $values = dbquery($dbh, qq/
		SELECT $selectstring
		FROM statistics
		WHERE word_id = $word_id
	/);
	my $i = 0;
	while ($i <= $#fields) {
		print "$fields[$i]:$$values[$i] ";
		$i++;
	}
	#foreach my $field (@fields) {
	#	my $value = dbquery($dbh, qq/
	#		SELECT $field FROM statistics 
	#		WHERE word_id = $word_id
	#	/);
	#	print "$field:$value ";
	#}
	print "-> ";
	print qq(<a href="word.pl?id=$word_id">$word</a> );
	print qq(<a href="concordance.pl?id=$word_id">KWIC</a> );
	print qq(<a href="coexistence.pl?id=$word_id">coex</a> );
	print qq(<a href="close_coex.pl?id=$word_id">close</a> );
	print "\n";
}

my $end_time = time();
my $time_elapsed = $end_time - $begin_time;
print "\n<b>query time: $time_elapsed seconds</b>\n";

print $q->end_html();