#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

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

use Opals::User qw(
    user_getInformation
);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);


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

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

    my $dateFrom= $input->{'acqstFrom'};
    my $dateTo  = $input->{'acqstTo'};
    my $sort    = $input->{'sort'};
    my $sortOrder    = $input->{'sortOrder'};
    $sort = 'dateImport' if ( !$sort );
    $sortOrder = 'ASC' if ( !$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";

    $template->param(acqstFrom => $dateFrom);
    $template->param(acqstTo => $dateTo);
    $template->param(acqList => 1);

    my $recordList =   GetRecordAcquisitionList_all($dbh,$ridList, $dateFrom, $dateTo,$sort,$sortOrder );
    MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo);

$template->param(
    recordList => $recordList,
    from    => substr($dateFrom, 0, 10),
    to      => substr($dateTo, 0, 10),
    sort2   => $sort,
    pNum    => $pNum,
);

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

################################################################################
sub GetRecordAcquisitionList_all {
    my ($dbh, $ridList,$dateFrom, $dateTo, $sort,$sortOrder) = @_;
    
       my $sql = <<_STH_;
select  i.rid, r.title, r.author,i.callNumber,r.pubDate
from    opl_item as i inner join opl_marcRecord as r  on i.rid = r.rid 
where   substring(i.barcode, 1, 3) <> '___' &&
        i.dateImport >= '$dateFrom' &&
        i.dateImport <= '$dateTo'
_STH_
    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    $ridList =~ s/,$//;
    if ($ridList){
        $sql .= " && i.rid in ($ridList) ";
    }
    $sql .= "group by rid order by $sort $sortOrder ";
    

    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    #my $odd = 0;
    my $pNum = -1;
    my $hNum = 0;
    my @recordList;
    my $holdingList;
    while (my $rec = $sth->fetchrow_hashref) {
        #$rec->{'odd'} = $odd ^ 1;
        $pNum++;
        $rec->{'odd'} = $pNum%2;
              $rec->{'order'} = $pNum;
        $holdingList = getRecordHolding($dbh, $rec->{'rid'});
        $rec->{'holdingList'} = $holdingList;
        $rec->{'holdingCount'} = scalar(@$holdingList);
        $hNum +=scalar(@$holdingList);
        ($rec->{'holdingCount'} > 1) || ($rec->{'holdingCount'} = 0);
        push @recordList, $rec;
    }
    $sth->finish;
    $template->param(
        countHolding_p    =>  $hNum,
        countRecord_p     =>  scalar(@recordList),
    );

    return \@recordList;
}
######################################################################################## 
sub getRecordHolding {
    my ($dbh, $rid) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  barcode, callNumber, dateImport as ondate
from    opl_item
where   rid = ? &&
        substring(barcode, 1, 3) <> '___'
order by callNumber,barcode asc
_STH_
    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
       if($holding->{'ondate'} lt $dateFrom || $holding->{'ondate'} gt $dateTo){
            $holding->{'outOfRange'}=1;
        }
        push @holdingList, $holding;
    }
    $sth->finish;

    return \@holdingList;
}

=item
sub GetAcquisitionReport
{
    my ($dbh, $template, $dateFrom, $dateTo, $sort, $pNum, $pagesize, $remainList) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }3389
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
        
    my $szSQL = "select i.rid, dateImport, callNumber, barcode, title, author from opl_item as i, opl_marcRecord as r where i.rid=r.rid && dateImport >= '$dateFrom' && dateImport <= '$dateTo'";
    $szSQL .= " order by " . $sort . " asc";
    $szSQL .= " limit $pageoffset, $pagesize";

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my @itemList = ();
    my $bZebraServerDown = 0;
    my $Num = 0;
    
    my @IdArray = split(/,/, $remainList);
    my %IdHash;
    for (my $i=0; $i < @IdArray; $i++)
    {
        $IdHash{$IdArray[$i]} = 1;
    }
    $template->param(NumNotDel => scalar(@IdArray));
    
    while ( my $rec = $query->fetchrow_hashref() )
    {
        $rec->{'odd'} = $Num%2;
        $rec->{'order'} = $Num;
        if ( $IdHash{$rec->{'rid'}} == 1 )
        {
            $rec->{'hasTransaction'} = 1;
        }
        $Num++;
        push @itemList, $rec
    }
    $query->finish;
    $template->param(itemList => \@itemList);
}


#----------------------------------------------------------
=cut

sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo) = @_;
    
   # my $szSQL = "select count(*) from opl_historyItem where substring(barcode, 1, 3) <> '___' && ondate>='$dateFrom' && ondate<='$dateTo'";
    my $szSQL = "select count(*) from opl_item where substring(barcode, 1, 3) <> '___' && dateImport>='$dateFrom' && dateImport<='$dateTo'";
    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($countHolding) = $query->fetchrow_array;
    $query->finish;
    
   # $szSQL = "select count(distinct rid) from opl_item inner join opl_historyItem on opl_item.barcode=opl_historyItem.barcode where opl_historyItem.status='imported' && ondate>='$dateFrom' && ondate<='$dateTo' ";
    $szSQL = "select count(*) from opl_item where substring(barcode, 1, 3) <> '___' && dateImport>='$dateFrom' && dateImport<='$dateTo' group by rid";
    $query = $dbh->prepare($szSQL);
    $query->execute();
    my $countRecord = 0;
   # my ($countRecord) = $query->fetchrow_array;
   while ($query->fetchrow_array) {
        $countRecord++;
    }
 $query->finish;

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

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