#!/usr/bin/perl

#use utf8;
use strict;
use CGI;


use Opals::Context;
use Opals::Constant;

use Date::Calc::Object qw(
    :all
);
use POSIX qw(
    floor
);
use Time::localtime;

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);

use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);


use Opals::Transactions qw(
    trans_getPaymentList
);

################################################################################

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/paidRefundWaiveList.tmpl',
            reqPermission   => 'circ_loan',
        }
);
    my @cookieList = (@$cookie);
    my $syspref      = tmpl_preference($dbh);
    my $tm = localtime;
    my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
    my $dFirst = Opals::Context->preference('dateFirst');
    my $dLast  = Opals::Context->preference('dateLast');

    my $dateFrom= $input->{'statFrom'};
    my $dateTo  = $input->{'statTo'};
   
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

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

    my $sort    = $input->{'sort'};
    if ( !$sort ){
        $sort = 'uid' ;
    }

    $template->param(statFrom => $dateFrom,
                     statTo   => $dateTo,
                     sort     => $sort,
                     dir      => $dir,
                     pNum     => $pNum);


    my $pwrList = getPaidWaiveRefundList($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize);
    MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo);

    $template->param( pwrList  => $pwrList);

    if($dateRange eq 'all'){
        $template->param(
            rangeAll   => 1,
            dateRangeOpt=>"all"
       );
    }
    elsif($dateRange eq 'fYear'){
        $template->param(
            from    => substr($dateFrom, 0, 10),
            to      => substr($dateTo, 0, 10),
            rangeFYear   => 1,
            dateRangeOpt=>"fYear"
       );
    }
    else{
        $template->param(
            from    => substr($dateFrom, 0, 10),
            to      => substr($dateTo, 0, 10),
            rangeSel   => 1,
            dateRangeOpt=>"rangeSel"
       );
    }



tmpl_write($dbh, $cgi, \@cookieList, $template);
#---------------------------------------------------------
sub getPaidWaiveRefundList {
    my ($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

       
    my $sql = <<_STH_;
select   u.lastname,u.firstname, u.userbarcode, t.tid,t.uid,abs(t.amount) as tamount ,
         t.balance ,t.date,t.description, t.code,d.odl_id
from     opl_transactions t left outer join opl_transactiondetail d on t.tid=d.tid 
         inner join opl_user u using (uid) 
where    t.date >= ? && t.date <=? && 
         (code=4  or code=6 or code=5) 
order by $sort $dir 
limit   $pageoffset, $pagesize

_STH_
    my $sth = $dbh->prepare($sql);
    $sth->execute( $dateFrom, $dateTo);
    
    my @pwrList =();  #  pwr:   PaidWaiveRefund 
    my $pNum =0;
    while (my $rec = $sth->fetchrow_hashref) {
        $rec->{'odd'} = $pNum%2;
        $pNum++;        
        push @pwrList,$rec;
    }
    $sth->finish;
    return \@pwrList;

}
#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo) = @_;
     my $sql_count = <<_STH_;
select   count(distinct t.tid) as count
from     opl_transactions t left outer join opl_transactiondetail d on t.tid=d.tid 
         inner join opl_user u using (uid) 
where    t.date >= ? && t.date <=? && 
         (code=4  or code=6 or code=5) 
_STH_
    my $sth = $dbh->prepare($sql_count);
    $sth->execute($dateFrom, $dateTo);
    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,
    );
}