#!/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::User qw(
    user_getInformation
);
use Opals::MarcXml qw(
    mxml_delete
);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);
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/itemdel.tmpl',
            reqPermission   => 'rpt_notice|rpt_catRec',
        }
);


$template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemdel') );
    my $incExcOpt = $input->{'incExcOpt'};
    if(!$incExcOpt || $incExcOpt ne 'inclusion'){
        $incExcOpt='exclusion';
    }
 
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    my $dateFrom= $input->{'delFrom'};
    my $dateTo  = $input->{'delTo'};
    my $sort    = $input->{'sort'};
    my $sortDir = $input->{'sortDir'};
    if ( !$sort ){
        $sort    = 'ondate';
        $sortDir = 'desc'  ;
    }

    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";

    $template->param(delFrom => $dateFrom);
    $template->param(delTo => $dateTo);
    $template->param(delList => 1);

    my $recordList = GetRecordDelList($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize,$sortDir,$incExcOpt);
    MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt);

$template->param(
    recordList => $recordList,
    sort     => $sort,
    sortDir  => $sortDir,
    pNum     => $pNum,
    incExcOpt=> $incExcOpt,
);

if($incExcOpt eq 'exclusion'){
    $template->param(exclusion =>1);
}
elsif($incExcOpt eq 'inclusion'){
    $template->param(inclusion =>1);
}

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"
   );

}

#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 GetRecordDelList {
    my ($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize,$sortDir,$incExcOpt) = @_;

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

=item       
    my $sql = <<_STH_;
select  m1.rid,m1.title,m1.author,m.callNumber, m.barcode,m.ondate,m1.pubDate,m.price
    from
    (select  t2.rid,t2.callNumber,t6.barcode,t6.ondate,t6.price 
    from
        (select rid, callNumber,barcode ,price from (select rid,callNumber,barcode ,price from opl_item) as t1 group by rid) as t2
        inner  join
        (select i.rid ,i.barcode,t5.ondate,i.price 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6) as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo'   && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (5)
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid    
    )as m
natural join opl_marcRecord m1 
order by $sort $sortDir 
limit   $pageoffset, $pagesize
_STH_

=cut
    my $sql ="
select m.title,m.pubDate,m.author,i.rid,i.callNumber,i.barcode,i.price,s.ondate ,m.tempIll, m.isbn 
from opl_marcRecord m inner join opl_item i on i.rid=m.rid 
                      inner join opl_itemstatus s on s.barcode=i.barcode
                      ";

 $sql .= " where s.ondate between '$dateFrom' and  '$dateTo' && s.status=5";
 if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') && i.barcode not regexp '\_\_\_TMP\_' ";
  }
 
$sql .= " order by $sort $sortDir 
          limit   $pageoffset, $pagesize ";

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

    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $preRid=0;
    my $barcode;
    my $isbn;
    while (my $r = $sth->fetchrow_hashref) {
        if($preRid != $r->{'rid'}){
            my $rec;
            $preRid = $r->{'rid'};
            $rec->{'rid'}= $r->{'rid'};
            $rec->{'title'}= $r->{'title'};
            $rec->{'author'}= $r->{'author'};
            $rec->{'pubDate'}= $r->{'pubDate'};
            $isbn= $r->{'isbn'};
            $isbn =~ s/\s+/ /g;
            $isbn =~ s/^\s+|\s+$//g;
            $isbn =~  s/\s/, /gi;
            $rec->{'isbn'} =$isbn;

            $pNum++;        
            $rec->{'odd'} = $pNum%2;
            $rec->{'order'} = $pNum;
            $rec->{'marcXmlExist'}=isMarcXmlExist($rec->{'rid'});
            $rec->{'holdingList'}=();
            $rec->{'holdingCount'}=0;
            if( $incExcOpt eq 'inclusion' && 
               ($r->{'tempIll'} eq 'temporary' || $r->{'tempIll'} eq 'ILL')){
                   $rec->{'tempIll'} = 1;
            }
            push @recordList, $rec;

        }
        
        if($r->{'barcode'} =~m/^\_\_\_(.*)\_[\d]+$/){
            $barcode=$1;
        }
        else{
           $barcode='n/a';
        }
        my $holding; 
        my $i=@recordList[$pNum]->{'holdingCount'};
        $holding->{'barcode'} = $barcode;
        $holding->{'callNumber'}= $r->{'callNumber'};
        $holding->{'ondate'} = $r->{'ondate'};
        $holding->{'price'} = $r->{'price'};

        @recordList[$pNum]->{'holdingList'}->[$i] =$holding ;
        @recordList[$pNum]->{'holdingCount'} +=1;
          }
    $sth->finish;
    return \@recordList;
}


#----------------------------------------------------------
sub getRecordHolding_bk {
my ($dbh, $rid) = @_;
 
    my $sth = $dbh->prepare(<<_STH_);
select  distinct s.barcode, callNumber, ondate
from    opl_itemstatus as s inner join opl_item as i on s.barcode = i.barcode
where   rid = ? && s.status =5 
order by callNumber,barcode asc
_STH_
    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        if($holding ->{'barcode'} =~m/^\_\_\_(.*)\_[\d]+$/){
            $holding ->{'barcode'}=$1;
        }
        else{
           $holding ->{'barcode'}='n/a';
        }
        push @holdingList, $holding;
    }
    $sth->finish;
 
    return \@holdingList;
}

#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;
=item    
     my $sql_count = <<_STH_;
select count(rid),sum(count) 
    from
    (
    select  t2.rid,count(t6.barcode) as count
    from
        (select rid, callNumber,barcode from (select rid,callNumber,barcode from opl_item) as t1 group by rid) as t2
        inner  join
        (select i.rid ,i.barcode,t5.ondate 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6) as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (5)
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid   group by t2.rid 
)as m

_STH_

=cut
     my $sql_count =" 
select count(rid),sum(count) 
from  (select i.rid,count(s.barcode) as count 
       from opl_item i inner join opl_marcRecord m on m.rid = i.rid 
       inner join opl_itemstatus s on s.barcode=i.barcode 
       left outer join opl_recordindex r on r.rid=m.rid";

    $sql_count .= "  where s.ondate between '$dateFrom' and  '$dateTo' && s.status=5 ";
    if($incExcOpt eq 'exclusion'){
        $sql_count .= " && (m.tempIll is NULL ||  m.tempIll = '') && i.barcode not regexp '\_\_\_TMP\_'  ";
    }
    $sql_count .= " group by i.rid   ) as m";

    my $sth = $dbh->prepare($sql_count);
    $sth->execute();
    my ($countRecord,$countHolding) = $sth->fetchrow_array;

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

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

#-------------------------------------------------------------
sub isMarcXmlExist{
    my ($rid) =@_;
    my $zRoot   = Opals::Context->config('zRoot');
    my $zDatabase = Opals::Context->config('zDatabase');
    my $dir     = "$zRoot/$zDatabase/" . ceil($rid/1000);
    
     
    return (-f "$dir/$rid.xml");

}