#!/usr/bin/perl
# ================================================================================
# $Rev: 170 $ $URL: svn://localhost/PlayChess/trunk/cgi/tourdir-list.cgi $
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Author: $Author: Thomas $
# Modified: $Date: 2005-11-02 21:36:50 +0000 (Mi, 02 Nov 2005) $
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Copyright at playchess.de - all rights reserved
# ================================================================================
use lib "../cgi-bin";
use CGI::Carp qw(fatalsToBrowser);
use CGI qw( :standard );
use PCLeagueUtil;
use PC;
use Util;
use PCSession;
use Template;
use ChessConfig;
use MyDbi;
##--------------------------------------------------------------------------
## External Parameters
##--------------------------------------------------------------------------
local $cgi = CGI->new();
local $sobj = PCSession->new( $cgi );
local $sname = $sobj->getValue( 'name' );
local $config = getConfig();
@envParms = qw( action league tour area class players state min ppp vid nofilter );
foreach $e (@envParms)
{
eval { $$e = $cgi->param($e) || $ENV{$e}; }
}
# Default values
# ~~~~~~~~~~~~~~
$number = 0;
$players = 5 unless $players;
$ppp = 10 unless $ppp;
$min = 0 unless $min;
$state = 'open' if( $action eq 'enroll' );
# Check for plausibility
# ~~~~~~~~~~~~~~~~~~~~~~
die( "League parameter is missing." ) unless $league;
# Connect to database
# ~~~~~~~~~~~~~~~~~~~
$dbh = dbiConnect() or die( "DB-Handle is undefined" );
##--------------------------------------------------------------------------
## Main program
##--------------------------------------------------------------------------
{
# -------------------------
# Find relevant tournaments
# -------------------------
my $pLevel = $sobj->getValue('AccessLevel');
# Optional conditions
# ~~~~~~~~~~~~~~~~~~~
my $where = 'where 1';
$where .= " and t.league = '$league'" if( $league );
$where .= " and t.class = '$class'" if( $class );
$where .= " and t.state = '$state'" if( $state );
$where .= " and t.accesslevel <= $pLevel" if( $pLevel && $action eq 'enroll' );
$where .= " and v.vid = $vid" if( $vid );
my $starttime = time;
# Thematic tournaments
# ~~~~~~~~~~~~~~~~~~~~
my( $thematic, $vFields, $vJoin );
if( $class eq 'T' )
{
$thematic = 1;
$vFields = ', v.name, v.vid';
$vJoin = 'left join tbl_thema v on t.vid=v.vid';
}
# Open swiss tournaments: check queue
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
my $qstmt = "
select count(*), avg(r.rating)
from tbl_queue q, tbl_rating r
where q.tid=?
and q.pid=r.pid
and r.league=?
group by q.tid
";
my $q_sth = $dbh->prepare( $qstmt );
# Total count
# ~~~~~~~~~~~
my $cstmt = "
select count(*)
from tbl_tournament t
$vJoin
$where
";
$number = getValue( $cstmt );
# Read all required data from database
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
my $stmt = "
select
t.tid, t.name, t.type, t.league, t.clockmin, t.clockinc, t.clockmax, t.maxplayers,
t.starttime, t.endtime, t.state, t.accesslevel,
count(pt.pid), avg(pt.ter), t.rounds
$vFields
from
(tbl_tournament t)
left outer join tbl_player_tournament pt on pt.tid=t.tid
$vJoin
$where
group by t.tid
order by t.tid desc
limit $min,$ppp
";
#print "stmt = $stmt
\n";
my $rows = MyDbi::getRows( $stmt );
my $count = 0;
my @list;
## print "rows = $rows
\n";
printf( "%s(%d): action-%s tourprefs='%s' sprefs='%s'
\n", __FILE__, __LINE__, $action, $sobj->getValue('tourprefs'), $sobj->getValue($pref) ) if(Util::isDebug);
# Loop over result set
# ~~~~~~~~~~~~~~~~~~~~
for $r ( 0 .. $#{$rows} )
{
my( $tid, $name, $tourtype, $tourleague, $clockmin, $clockinc, $clockmax, $player_max,
$starttime, $endtime, $tourstate, $accesslevel,
$player_anz, $avg_ter, $rounds, $thema, $vid ) = @{ $rows->[$r] };
my( $pref, @line );
printf( "%s(%d): Tour-%d='%s'
\n", __FILE__, __LINE__, $r, join('|',@{$rows->[$r]}) ) if(Util::isDebug);
# open swiss tours need the queue for some data
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if( $tourtype =~ /Swiss/i && $tourstate =~ /open/i )
{
#my $cols = $q_sth->execute( $tid, $tourleague );
my $cols = MyDbi::getRow( $q_sth, $tid, $tourleague );
$player_anz = 0 + $cols->[0];
$avg_ter = 0 + int( $cols->[1] + 0.5 );
# print "cols=$cols $playeranz=$player_anz pcr=$avg_ter
\n";
}
# tourtype preferences for members
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$pref = ($clockinc==48) ? 'turtle' : 'rabbit';
$pref = "$pref-$player_max";
printf( "%s(%d): Tour-%d pref='%s'
\n", __FILE__, __LINE__, $r, $pref ) if(Util::isDebug);
printf( "%s(%d): Tour-%d sprefs='%s'
\n", __FILE__, __LINE__, $r, $sobj->getValue($pref) ) if(Util::isDebug);
# Create tournament list
# ~~~~~~~~~~~~~~~~~~~~~~
#printf( "$name: action=%s, level=%d, pLevel=%d
\n", $action, $accesslevel, $pLevel );
if( $action ne 'enroll' || $accesslevel <= $pLevel )
{
printf( "%s(%d): Tour-%d alevel='%d'
\n", __FILE__, __LINE__, $r, $accesslevel ) if(Util::isDebug);
if( $action ne 'enroll' || $nofilter || !$sobj->getValue('tourprefs') || $sobj->getValue($pref) )
{
printf( "%s(%d): Tour-%d IN LISTE
\n", __FILE__, __LINE__, $r ) if(Util::isDebug);
# format data for display
# ~~~~~~~~~~~~~~~~~~~~~~~
$avg_ter = ($avg_ter) ? int($avg_ter+0.5) : ' ';
#$starttime = ($starttime) ? Util::getDate( $starttime, 'dd.mm.yyyy' ) : ' ';
#$endtime = ($endtime) ? Util::getDate( $endtime, 'dd.mm.yyyy' ) : ' ';
push @line,
$name, $tourleague, $clockmin.'d', $clockinc.'h',
$starttime, $endtime, $tourstate, $player_max,
$player_anz, $avg_ter, ($count%2), $thema, $vid, $accesslevel, $rounds, ($clockmax ? $clockmax.'d' : '');
push @list, join( '|', @line );
$count++;
}
else
{
printf( "%s(%d): Tour-%d FILTERED OUT
\n", __FILE__, __LINE__, $r ) if(Util::isDebug);
$filtered++;
}
}
# print "count = $count, filtered=$filtered
\n";
}
# Disconnect from database
# ~~~~~~~~~~~~~~~~~~~~~~~~
$dbh->disconnect();
# determine limit parameters for next/previous page
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$prev = ($min<=1) ? 0 : (($min>$ppp) ? $min-$ppp : 1);
$next = $min+$ppp;
#$number = getValue( $cstmt, $gommin, $gommax );
#$number = @table;
$shown = ($number>=$next) ? $ppp : ($number-$min);
$next = 0 if $next>$number;
# Create output
# ~~~~~~~~~~~~~
my $tpl = Template->new( "tourdir-list.tpl" );
$tpl->addBatch( $min, $ppp, scalar @TN, $number );
$tpl->addMakro( "ACTION", $action );
$tpl->addMakro( "THEMATIC", $thematic );
$tpl->addMakro( "LEAGUE", $league );
$tpl->addMakro( "CLASS", $class );
$tpl->addMakro( "TOTAL", scalar @TN );
$tpl->addMakro( "FILTERED", $filtered );
$tpl->addMakro( "STATE", $state );
$tpl->addMakro( "STATE_".uc($state), 1 );
$tpl->addMakro( "COLS", $state ? 8 : 9 );
$tpl->addMakro( "STATEWORD", ucfirst $state );
$tpl->addMakro( uc($state), 1 ) if $state;
$tpl->addMakro( "TOTAL_FOUND", $number );
$tpl->addMakro( "VID", $vid );
$tpl->addMakro( "MIN", $min );
$tpl->addMakro( "PPP", $ppp );
$tpl->addMakro( "PREV", $prev );
$tpl->addMakro( "NEXT", $next );
$tpl->addMakro( "SHOWN", $shown );
$tpl->addMakro( "SORT", '' );
$tpl->addMakro( "ACCESS_LEVEL", $pLevel );
$tpl->addArray( "TABLE", \@list );
for( $m=0, $n=1; $m<$number; $m+=$ppp, $n++ )
{
# $line = join( '|', $n, $m, ($m!=$min) );
$line = join( '|', $n, $m, ($m==$min), $m+1, $m+$ppp );
push @minarray, $line;
}
$tpl->addArray( "MINARRAY", \@minarray );
print $tpl->Expand($sobj);
}