#!/usr/bin/perl

use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(

    tmpl_read
    tmpl_write
    tmpl_rangedPageList

);

use Opals::Date qw(
    date_f005
);

use Opals::Tb_Record qw(

    tb_record_findByRId
    tb_record_findByRId_brief
    tb_item_findByRId

);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'txtbk/report/itemAcqst_prt.tmpl',
        reqPermission   => 'tb_report',
    }
);
    my $ridList = $input->{'ridList'};
    my $pSize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    my $dateFrom    =   $input->{'acqstFrom'};
    my $dateTo      =   $input->{'acqstTo'};
    my $dateRange   =   $input->{'dateRageOpt'}; 
    my $sort        =   $input->{'sort'};
    my $sortOrder   =   $input->{'sortOrder'};
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";
    $sort = 'importDate' if (!$sort);
    my $recordList = getRecordAcquisitionList($dbh, $dateFrom, $dateTo, $sort, $sortOrder, $ridList );

    $template->param(
        sort2               => $sort,
        "sort_".$sort       => 1,
        sortOrder           => $sortOrder,
        "sort_".$sortOrder  => 1,
        pNum                => $pNum,
        recordList          => $recordList,
    );

    if ($dateRange eq 'fYear'){
        $template->param(
            dateFrom    =>  substr($dateFrom, 0, 10),
            dateTo      =>  substr($dateTo, 0,10),
            rangeFYear  =>  1,
            dateRangeOpt=>"fYear",

        );
    }
    else{
        $template->param(
            dateFrom    =>  substr($dateFrom, 0, 10),
            dateTo      =>  substr($dateTo, 0,10),
            rangeSel    =>  1,
            dateRangeOpt=>"rangeSel",
        );
    }
#------------------------------------------------------------------------------
sub getRecordAcquisitionList {

    my ($dbh, $dateFrom,$dateTo,$sort, $sortOrder, $ridList) = @_;
    my @recordList;
    my $sql = 
        " select i.rid as rid
          from tb_items i ";
          
    my $sqlWhere = " where  i.barcode not regexp '^\_\_\_' &&
                 i.importDate between '$dateFrom' and '$dateTo' && i.deleted <> '1' && i.available = '1' ";
    my $sqlOrderBy = "";
    if ($sort =~ m/title/i){
        $sql .= "  inner join tb_records r on r.rid = i.rid && r.fId = '245_a' ";
        $sqlOrderBy =  " order by r.fVal, r.rid " ;
    }
    elsif ($sort =~ m/author/i){
        $sql .= "  inner join tb_records r on r.rid = i.rid && r.fId = '100_a' ";
        $sqlOrderBy =  " order by r.fVal, r.rid " ;
    }
    elsif ($sort =~ m/pubDate/i){
        $sql .= "  inner join tb_records r on r.rid = i.rid && r.fId = '260_a' ";
        $sqlOrderBy =  " order by r.fVal, r.rid " ;
    }
    elsif ($sort =~ m/classNumber|barcode|importDate/i){
        $sqlOrderBy =  " order by $sort ";
    }
    else{
        $sqlOrderBy =  " order by $sort ";
    }

    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    $ridList =~ s/,$//;
    if ($ridList){
        $sqlWhere .= " && i.rid in ($ridList) ";
    }

    $sql .= " $sqlWhere group by rid $sqlOrderBy  $sortOrder ";
    my $itemList;
    my $countCopy = 0;
    my ($countOutOfRange, $countTmp) =(0, 0);
    my $recordInfo;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $preRId = -1;

    while (my $rec = $sth->fetchrow_hashref) {
        $countTmp = 0;
        if ($rec->{'rid'} != $preRId){
            ($itemList ,$countTmp) = getItemList($dbh,$rec->{'rid'});
            $recordInfo = tb_record_findByRId_brief($dbh,$rec->{'rid'});
            $preRId = $rec->{'rid'};

            $countOutOfRange += $countTmp;
            $countCopy += scalar(@$itemList);
            push @recordList, {
                rid         =>  $rec->{'rid'},
                title       =>  $recordInfo->{'245_a'},
                author      =>  $recordInfo->{'100_a'},
                pubDate     =>  $recordInfo->{'260_c'},
                copyCount   =>  $countCopy,
                itemList    =>  $itemList,
            }
        }
    }
  
    $sth->finish;
    $template->param(
        totalCopy       =>  $countCopy ,
        totalOutOfRange =>  $countOutOfRange, 
        totalRecord     =>  scalar(@recordList),
    );

    return \@recordList;

}

sub getItemList{
    my ($dbh, $rid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select  barcode, classNumber, importDate
from    tb_items
where   rid = ? && barcode not regexp '^\_\_\_' && deleted <> '1' && available = '1' 
order by classNumber,barcode asc
_STH_
    $sth->execute($rid);

    my @copyList;
    my $countOutOfRange = 0;
    while (my $copy = $sth->fetchrow_hashref) {
       if($copy->{'importDate'} lt $dateFrom || $copy->{'importDate'} gt $dateTo){
            $copy->{'outOfRange'}=1;
            $countOutOfRange++;
        }
        push @copyList, $copy;
    }
    $sth->finish;
    return (\@copyList, $countOutOfRange);
}

tmpl_write($dbh, $cgi, $cookieList, $template);