#!/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.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->{'dateRangeOpt'}; 
    my $sort        =   $input->{'sort2'};
    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 $itemList = getRecordAcquisitionList($dbh, $dateFrom, $dateTo, $sort, $sortOrder, $pNum, $pSize, $ridList );
    makePages($dbh, $input, $template, $pSize, $dateFrom, $dateTo);

    $template->param(
        sort2               => $sort,
        "sort_".$sort       => 1,
        sortOrder           => $sortOrder,
        "sort_".$sortOrder  => 1,
        pNum                => $pNum,
        itemList            => $itemList,
    );
    my $n = scalar(@$itemList);

    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, $pNum, $pageSize, $ridList) = @_;
    my $pageOffset = $pNum;
    if ( !$pageOffset ) {
        $pageOffset = 0;
    }
    else {
        $pageOffset = ($pNum - 1) * $pageSize;
    }
    my $recBlocked;
    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    foreach my $rid (split /,/, $ridList) {
        $recBlocked->{$rid} = 1;
    }


    my @itemList;

    my $sql = 
        " select i.rid as rid, i.barcode as barcode, i.classNumber as classNumber, i.importDate as importDate
          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 using(rid) ";
        $sqlWhere .= " && r.fId = '245_a' ";
        $sqlOrderBy =  " order by r.fVal, r.rid " ;
    }
    elsif ($sort =~ m/author/i){
        $sql .= "  inner join tb_records r using(rid) ";
        $sqlWhere .= " && r.fId = '100_a' ";
        $sqlOrderBy =  " order by r.fVal, r.rid " ;
    }
    elsif ($sort =~ m/pubDate/i){
        $sql .= "  inner join tb_records r using(rid) ";
        $sqlWhere .= " && 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 ";
    }
    $sql .= " $sqlWhere " . $sqlOrderBy . " $sortOrder limit $pageOffset, $pageSize ";

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $order = -1;
    my $preRId = -1;
    my $recordInfo;
    while (my $rec = $sth->fetchrow_hashref) {
        if ($rec->{'rid'} != $preRId){
            $recordInfo = tb_record_findByRId_brief($dbh,$rec->{'rid'});
            $preRId = $rec->{'rid'};
        }
        $order++;
        $rec->{'order'} = $order;
        $rec->{'hasTransaction'} = $recBlocked->{$rec->{'rid'}};
        $rec->{'title'} = $recordInfo->{'245_a'};
        $rec->{'author'} =  $recordInfo->{'100_a'};
        $rec->{'pubDate'} =  $recordInfo->{'260_c'};
        push @itemList ,$rec;
    }
    return \@itemList;
}


sub makePages {
    my ($dbh, $input, $template, $pSize, $dateFrom, $dateTo) = @_;

    my ($countRecord, $countCopy) = (0,0);
    #my $sql = "select count(distinct r.rid) from tb_records r inner join tb_items i on r.rid = i.rid && r.deleted = '0'  
    #        where   i.importDate between '$dateFrom' and '$dateTo' && i.barcode not regexp '^\_\_\_' && i.available = '1'";

    my $sql = " select count(distinct i.rid) from tb_items i   
            where  i.importDate between '$dateFrom' and '$dateTo' && i.barcode not regexp '^\_\_\_' && i.available = '1'";

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

    $sql = "select count(*) 
            from    tb_items
            where   deleted <> '1' && barcode not regexp '^\_\_\_' && available = '1' &&
                    importDate between '$dateFrom' and '$dateTo' ";

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

    $sth->finish;
    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countCopy, $input->{'pNum'}, $pSize, 10);
    $template->param(
        countRecord => $countRecord,
        countCopy   => $countCopy,
        rangedPageList  =>  \@rangedPageList
    );

}

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