#!/usr/bin/perl
#----------------------------------------------------------------------------
# $Header: e:/p/w/playchess/rcs/todo.cgi 1.4 2003/01/15 16:54:58Z ts init $
#----------------------------------------------------------------------------
use lib "../cgi-bin";
use CGI qw( :standard );
use CGI::Carp qw(fatalsToBrowser);
use PCSession;
use Template;
use MyDbi;
use Util;
local $cgi = CGI->new();
local $sobj = PCSession->new( $cgi );
local $sid = $sobj->getValue( 'sid' );
local $spid = $sobj->getValue( 'pid' );
local $config = getConfig();
# CGI parameters
# ~~~~~~~~~~~~~~
$action = $cgi->param( 'action' );
$view = $cgi->param( 'view' ) || 's';
$tid = $cgi->param( 'tid' );
$sort = $cgi->param( 'sort' );
$head = $cgi->param( 'head' ) || '';
$shorthead = $cgi->param( 'shorthead' ) || '';
$description = $cgi->param( 'description' ) || '';
$type = $cgi->param( 'type' ) || '';
$state = $cgi->param( 'state' ) || '';
$importance = $cgi->param( 'importance' ) || '';
$schedule_year = $cgi->param( 'schedule_year' ) || '';
$schedule_month = $cgi->param( 'schedule_month' ) || '';
$value = $cgi->param( 'value' ) || 0;
$ppp = $cgi->param('ppp') || 25;
$min = $cgi->param('min');
$max = $cgi->param('max');
# print join( '|',$tid, $head, $description, $supplier, $created,
# $type, $state, $importance, $schedule_year, $schedule_month ), "
\n";
if( !$action )
{
# Connect to database
# ~~~~~~~~~~~~~~~~~~~
$dbh = dbiConnect() or exit(1);
$sort = 'modified' unless( $sort );
$min = 0 unless $min;
#$ppp = 10 if( $ppp<=10 || $ppp>100);
# $order = "order by $sort" if( $sort =~ /(type|importance)/ );
$order = "order by state desc, scheduled_year, scheduled_month, importance" if( $sort =~ /(schedule)/ );
$order = "order by type, state desc, scheduled_year, scheduled_month, importance" if( $sort =~ /(type)/ );
$order = "order by 14 desc" if( $sort =~ /(importance)/ );
#$order = "order by importance, state desc, scheduled_year, scheduled_month" if( $sort =~ /(importance)/ );
$order = "order by modified desc" if( $sort =~ /(modified)/ );
$order = "order by created desc" if( $sort =~ /(created)/ );
$order = "order by p.name" if( $sort =~ /(supplier)/ );
$order = "order by t.head" if( $sort =~ /(title)/ );
my $cond = '';
$cond = "and (t.state='submitted' or t.state='accepted')" if( $view eq 's' );
$cond = "and (t.state='scheduled' or t.state='in progress')" if( $view eq 'w' );
$cond = "and (t.state='done' or t.state='declined')" if( $view eq 'd' );
$tp = MyDbi::getValue( "select count(*) from tbl_task t where 1 $cond" );
$stmt = "
select
t.tid, t.head, t.description, t.spid, t.created,
t.type, t.importance, t.state, t.scheduled_year, t.scheduled_month,
unix_timestamp(t.modified),
p.name, count(v.pid), sum( v.weight*v.value ) / sum( v.weight )
from (tbl_task t, tbl_player p)
left outer join tbl_vote v on t.tid=v.vid -- and v.bid=1
where 1
and t.spid=p.pid
$cond
group by t.tid
$order
limit $min,$ppp
";
# print $stmt, "
\n";
$rows = MyDbi::getRows( $stmt );
print 'stmt=', $stmt, "
\n" if(Util::isDebug);
print 'rows=', $#{$rows}, "
\n" if(Util::isDebug);
# loop over result set
# ~~~~~~~~~~~~~~~~~~~~
for $r ( 0 .. $#{$rows} )
{
my( $tid, $head, $description, $supid, $created,
$type, $importance, $state, $scheduled_year, $scheduled_month,
$modified,
$supplier, $count, $value ) = @{ $rows->[$r] };
print $r, ': ', join('|', @{ $rows->[$r] }), "
\n" if(Util::isDebug);
my $rawdesc = $rows->[$r][2];
$rawdesc =~ s|\s\s+| |g;
$rawdesc = Template::DispShort($rawdesc,100);
$rows->[$r][2] =~ s|\n+|
|g; $rows->[$r][2] = '
' . $rows->[$r][2] . '
'; #$rows->[$r][11] = 0 + $rows->[$r][11]; my $isMine = ($spid==$supid && $count<=1) || $sobj->isAdmin; push @TASKS, join( '|', $min+$r+1, $r%2, @{ $rows->[$r] }, $isMine, $rawdesc ); } # End of DB access #----------------- $dbh->disconnect(); # Create output # ~~~~~~~~~~~~~ my $tplfile = "todo.tpl"; my $tpl = Template->new( $tplfile ); addMakros( $tpl ); $tpl->addArray( "TASKS", \@TASKS ); $tpl->addBatch( $min, $ppp, scalar @TASKS, $tp ); print $tpl->Expand( $sobj ); } elsif( $action =~ /^view$/ ) { $sobj->assertLogged(); # Connect to database # ~~~~~~~~~~~~~~~~~~~ $dbh = dbiConnect() or exit(1); my $myvalue = 0 + MyDbi::getValue( 'select 0+value from tbl_vote where bid=1 and vid=? and pid=?', $tid, $spid ); $stmt = " select t.tid, t.head, t.shorthead, t.description, t.spid, t.created, t.type, t.state, t.scheduled_year, t.scheduled_month, unix_timestamp(t.modified), p.name, count(v.pid), sum( v.weight*v.value ) / sum( v.weight ) from (tbl_task t, tbl_player p) left outer join tbl_vote v on t.tid=v.vid and v.bid=1 where tid=? and t.spid=p.pid group by t.tid "; # print $stmt, "#gsx; $description =~ s#\n\s*\n*#
#gs; #$description = join( '
', split(/\n\s*\n*/, $description) ); $description = '
' . $description . '
'; my $tpl = Template->new( 'todo-view.tpl' ); $tpl->addMakro( "TITLE", 'Vote' ); $tpl->addMakro( "COUNT", $count ); $tpl->addMakro( "IMPORTANCE", $importance ); $tpl->addMakro( "IS_MINE", $isMine ); $tpl->addRadioMakro( "value", $myvalue ); addMakros( $tpl ); print $tpl->Expand( $sobj ); } elsif( $action =~ /(storevote)/ ) { $sobj->assertLogged(); # Connect to database # ~~~~~~~~~~~~~~~~~~~ $dbh = dbiConnect() or exit(1); my $weight = $sobj->getValue('isMember') ? 5 : 1; #$stmt = "replace into tbl_vote set weight=?, bid=1, vid=?, pid=?"; $stmt = "replace into tbl_vote set weight=$weight, value=$value, bid=1, vid=$tid, pid=$spid;"; #print $stmt, "$stmterr='$DBI::errstr'