#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    ceil
);

use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::Date qw(
   date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

use Opals::Tb_Transactions qw(
    
    trans_getDetailBalance

);


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $ridList= $input->{'ridList'};
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'txtbk/report/fineReport.tmpl',
            reqPermission   => 'tb_report',
        }
);


    
    my $pagesize = 10;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    my $sort     = $input->{'sort'};
    my $sDirection = $input->{'sDirection'};
    if ( !$sort){
      $sDirection='asc';
      $sort     = 'u.lastname, u.firstname ';
    }
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    
    #$template->param(delList => 1);

    my ($tCount,$recordList) = GetFineTransList($dbh,  $sort,$sDirection , $pNum, $pagesize);
    #MakePages($input, $template, $pagesize, $tCount);
    my $pDirection = $sDirection;
    $sDirection=($sDirection eq 'asc' ?'desc':'asc');

$template->param(
    fineReport => 1,
    recordList => $recordList,
    countRecord   => $tCount,
    sort       => $sort,
    sDirection => $sDirection, 
    pDirection => $pDirection, 
    pNum       => $pNum
);

#Tue, Jan 12, 2010 @ 10:31:41 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);

tmpl_write($dbh, $cgi, $cookie, $template);
#$dbh->disconnect();

################################################################################
sub GetFineTransList {
    my ($dbh,  $sort,$sDirection, $pNum, $pagesize) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
=item
   my $sql = <<_STH_;
select  u.lastname ,u.firstname, u.uid, u.sid, u.userbarcode, u.teacher, u.homeroom, u.grade,
        t.tid, t.uid,t.amount,
         d.amount as damount
from tb_transactions t 
      inner join tb_transactiondetail d on t.tid=d.tid
      inner join opl_user u on u.uid = t.uid
group by u.uid
order by $sort $sDirection      
_STH_
=cut

    my $sql = <<_STH_;
select u.uid, u.sid, u.userbarcode, u.firstname, u.lastname, u.teacher, u.homeroom, u.grade,
    t.tid, t.uid, t.amount, t.balance
from tb_transactions t inner join (
        select max(tid) as tid from tb_transactions group by uid ) as r
        on t.tid = r.tid
    inner join opl_user u on u.uid = t.uid
where t.balance <> 0            
order by $sort $sDirection
_STH_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $pNum = -1;
    my @recordList =();
    my $tCount = 0;
    while (my $r = $sth->fetchrow_hashref) {
        my $rec;
        #my ($totBalance, $totalCharge,$totalPaid,$totalFgvn,$totalRefundLost, $totalRefund,$totalCredit,$totalDebit) = trans_getDetailBalance($dbh,$r->{'uid'});
        #if ($totBalance != 0  || ($totalCredit == $totalDebit  )){ }
              $rec->{'tid'}         = $r->{'tid'};
              $rec->{'firstname'}   = $r->{'firstname'};
              $rec->{'lastname'}    = $r->{'lastname'};
              $rec->{'date'}        = $r->{'date'};
              $rec->{'description'} = $r->{'description'};
              $rec->{'balance'}     = sprintf("%.2f", $r->{'balance'});
              $rec->{'uid'}         = $r->{'uid'};
              $rec->{'sid'}         = $r->{'sid'};
              $rec->{'userbarcode'} = $r->{'userbarcode'};
              $rec->{'grade'}       = $r->{'grade'};
              $rec->{'homeroom'}    = $r->{'homeroom'};
              $rec->{'teacher'}     = $r->{'teacher'};
              $pNum++;
              $rec->{'odd'}         = $pNum%2;
              $rec->{'order'}       = $pNum;
              push @recordList, $rec;    
              $tCount++;
    }
    $sth->finish;
    return ($tCount,\@recordList);
}

#----------------------------------------------------------
sub MakePages
{
    my ($input, $template, $pagesize, $countRecord) = @_;

    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);
    my @rangedPageList = tmpl_rangedPageList($countRecord, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countRecord     => $countRecord,
        rangedPageList  => \@rangedPageList,
    );

}