#!/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::Transactions qw(
    trans_getUnpaidFineList
    trans_getTaxTable
    trans_getBalance
    trans_getUnrefundLostList
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

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        => 'report/fineReport.tmpl',
            reqPermission   => 'report',
        }
);


    
    my $pagesize = 20;
    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/;
    

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

$template->param(
    fineReport => 1,
    recordList => $recordList,
    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 GetUserFineList {
    my ($dbh,  $sort,$sDirection, $pNum, $pagesize) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

    my $sql = <<_STH_;
select u.* 
from opl_transactions t1 inner join (
               select   max(tid) as tid   
               from     opl_transactions  
               group by uid 
               )as r  
   ON t1.tid=r.tid 
   inner join opl_user u ON u.uid=t1.uid
 where t1.balance > 0   
order by u.uid
_STH_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    

    my $pNum = -1;
    my @recordList =();
    while (my $r = $sth->fetchrow_hashref) {
       my $rec;
          $rec->{'firstname'}   = $r->{'firstname'};
          $rec->{'lastname'}    = $r->{'lastname'};
          $rec->{'uid'}         = $r->{'uid'};
          $rec->{'grade'}       = $r->{'grade'};
          $rec->{'homeroom'}    = $r->{'homeroom'};
          $rec->{'teacher'}     = $r->{'teacher'};
          $rec->{'notes'}       = $r->{'notes'};
          $rec->{'unpaidList'}  = GetUnpaidList($dbh,$r->{'uid'}, $sort ,$sDirection);
          $rec->{'firstname'}   = $r->{'uid'};
          my($totalRefund,$refundList )   = GetRefundList($dbh,$r->{'uid'}, $sort ,$sDirection);
          $rec->{'refundList'}  =  $refundList;
          $rec->{'totalRefund'} =  $totalRefund;
          my $balance = trans_getBalance($dbh,$r->{'uid'});
          $rec->{'balance'}    = $balance;
          $pNum++;
          $rec->{'odd'}         = $pNum%2;
          $rec->{'order'}       = $pNum;
          $rec->{'orderNo'}     = $pNum+1;
          push @recordList, $rec;    
    }
    $sth->finish;
    return \@recordList;
}


################################################################################
sub GetUnpaidList {
    my ($dbh,  $uid, $sort ,$dir) = @_;
        ########  Get unpaid transactions  ############
        #
        #my $dir       =  'asc' ;
        my @transTbl;
            @transTbl =trans_getUnpaidFineList($dbh,$uid,$dir,$sort);
        my $idx=0;
        foreach my $tr(@transTbl){
            $tr->{'idx'} =$idx++;
            $tr->{'barcode'} =~ s/^___//g;
            $tr->{'barcode'} =~ s/_\d\d\d$//g;
        }
        my @taxTbl =trans_getTaxTable($dbh);
        if(scalar(@taxTbl)>0){
            $template->param(taxTbl=>\@taxTbl);
        }
open debug, '>/tmp/debugHa';
    print debug "scalar(@transTbl)\n";
    close debug;
    return \@transTbl;           
}

################################################################################
sub GetRefundList {
    my ($dbh,  $uid, $sort ,$dir) = @_;
    my  ($tidList,$totalRefund) =("",0);              
        my @refundTbl = trans_getUnrefundLostList($dbh,$uid,$dir, $sort);
        foreach my $r(@refundTbl){
            $r->{'tamount'} = abs($r->{'tamount'});
            $totalRefund   += $r->{'tamount'};
        }

    return ($totalRefund,\@refundTbl);           
}



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

 my $sql_count = <<_STH_;
select count(t1.tid)  from opl_transactions t1 
       inner join (
          select max(tid) as tid   
          from opl_transactions  
          group by uid )as r 
  ON t1.tid=r.tid
  inner join opl_user u ON u.uid=t1.uid
 where t1.balance > 0
_STH_
    my $sth = $dbh->prepare($sql_count);
    $sth->execute();
    my ($countRecord) = $sth->fetchrow_array;

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

}