#!/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::MarcXml qw(
    mxml_del_rec_holding
);
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 ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemmissing.tmpl',
            reqPermission   => 'circ_loan|rpt_notice|rpt_catRec',
        }
);


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

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

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


    if (  $input->{'op'} eq 'del' )
    {
       my %h;
       if($input->{'whole'}==1){
               %h  = getAllMissingItem($dbh,$dateFrom,$dateTo,$incExcOpt);
       }      
       else {
           my $rid_bc_list = $input->{'ridList'};       
              $rid_bc_list =~ s/\]\[/,/g;
              $rid_bc_list =~ s/\[|\]//g;
           my @a= split(/,/,$rid_bc_list);
           for (my $i=0; $ i <scalar(@a);$i++){
              my @b=split(/:/,@a[$i]);
              if(scalar(@b)==2){
                $h{@b[0]}{@b[1]}=1;
              }
          }
        }
        my $delOpt=0;
        if($input->{'delOpt'} && $input->{'delOpt'} eq "1"){
           $delOpt=1;
        }
       mxml_del_rec_holding($dbh,\%h,$delOpt);
    }

    my $recordList = GetRecordMissingList($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,
    missingList => 1,
    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 getAllMissingItem{
    my ($dbh, $dateFrom, $dateTo,$incExcOpt) = @_;
 my $sql = "select a.rid,a.price,b.barcode,b.ondate,m.pubDate
from   opl_item as a inner join opl_marcRecord as m on m.rid=a.rid 
       inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode
       left outer join (select * from opl_itemstatus where note='' || note is null)  c on b.barcode=c.barcode && b.id  < c.id 
where  c.id is null &&
       b.ondate >=? && b.ondate <=? &&
       b.status = 0 ";

    if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
       
    $sql .= " group by a.rid  ";     

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


    my %h;
    while (my $rec = $sth->fetchrow_hashref) {
        $h{$rec->{'rid'}}{$rec->{'barcode'}}=1;
        }
    $sth->finish;
    return %h;
}

################################################################################
sub GetRecordMissingList {
    my ($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize,$sortDir,$incExcOpt) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
=item
 my $sql_bk =" select   a.rid,m.title,m.author, a.price,b.barcode, b. status ,b.ondate ,m.pubDate
from     opl_item as a inner join opl_marcRecord as m on m.rid=a.rid
         inner join opl_itemstatus as b on a.barcode= b.barcode
         left outer join opl_itemstatus c on b.barcode=c.barcode && c.status<>6 && b.status<>6
where    b.id  <= c.id &&  a.barcode  not regexp  '___' &&
           b.ondate >= ? &&  b.ondate <= ? &&
           b.status     = 0 ";
 if($incExcOpt eq 'exclusion'){
       $sql_bk .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
  }

$sql_bk .= " group by a.rid
          order by $sort $sortDir 
          limit   $pageoffset, $pagesize  ";

=cut
 my $sql = "select a.rid,a.price,b.barcode,b.ondate,m.pubDate,m.title,m.author
from   opl_item as a inner join opl_marcRecord as m on m.rid=a.rid 
       inner join  (select * from opl_itemstatus where note='' || note is null) as b on a.barcode= b.barcode
       left outer join  (select * from opl_itemstatus where note='' || note is null) c on b.barcode=c.barcode && b.id  < c.id 
where  c.id is null &&
       b.ondate >=? && b.ondate <=? &&
       b.status = 0 ";


 if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
  }

$sql .= " group by a.rid
          order by $sort $sortDir 
          limit   $pageoffset, $pagesize  ";


    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my @holdingList;
    my $hidden=0;
    while (my $rec = $sth->fetchrow_hashref) {
       # $rec->{'odd'} = $odd ^ 1;

        @holdingList = getRecordHolding($dbh, $rec->{'rid'});
        $hidden=0;
        $pNum++;   
        foreach my $h (@holdingList){
            $rec->{'odd'} = $pNum%2;     
            $rec->{'order'} = $pNum;

           push @recordList, {rid       =>$rec->{'rid'},
                              title     =>$rec->{'title'},
                              author    =>$rec->{'author'} ,
                              pubDate   =>$rec->{'pubDate'} ,
                              odd       =>$rec->{'odd'},
                              order     =>$rec->{'order'} ,
                              callNumber=>$h->{'callNumber'},  
                              barcode   =>$h->{'barcode'},
                              price     =>$h->{'price'},
                              ondate    =>$h->{'ondate'},
                              hidden    =>$hidden
                              };
           $hidden=1;                   
        }
    }
           $sth->finish;
    return \@recordList;

 }


#----------------------------------------------------------
sub getRecordHolding_bk {
   my ($dbh, $rid,$incExcOpt) = @_;
    my $sql = " SELECT  s.barcode,i.price,callNumber,substring(c,1,19) AS ondate FROM opl_item AS i 
   inner join opl_marcRecord m on m.rid = i.rid
   INNER JOIN
          (
           SELECT barcode, MAX(concat(ondate,status)) as c
           FROM opl_itemstatus as s2 where  substring(s2.barcode, 1, 3) <> '___' 
           && s2.status <>6
           group by s2.barcode having c regExp '0\$'
           )
           AS s ON i.barcode=s.barcode and i.rid=? ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

 
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
   
   
   
    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo,$rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        push @holdingList, $holding;
    }
    $sth->finish;

    return @holdingList;

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

    my $sth = $dbh->prepare(<<_STH_);
select   a.barcode,a.price,a.callNumber, b. status ,b.ondate 
from     opl_item as a inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode
         left outer join (select * from opl_itemstatus where note='' || note is null)  as c on b.barcode=c.barcode && b.id  < c.id 
where    a.rid=? &&
         c.ondate  is null &&
         b.status     = 0;
_STH_

    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        push @holdingList, $holding;
    }
    $sth->finish;

    return @holdingList;

}


#----------------------------------------------------------

sub MakePages
{
   my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;  
   my $sql = " select  count(distinct a.rid) 
from     opl_item as a inner join opl_marcRecord as m on m.rid=a.rid
         inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode
         left outer join (select * from opl_itemstatus where note='' || note is null) c on b.barcode=c.barcode && b.id  < c.id 
where  c.ondate  is null &&
           b.ondate >= ? &&  b.ondate <= ? &&
           b.status     = 0 ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    my $query = $dbh->prepare($sql);
    $query->execute($dateFrom,$dateTo );
    my $countRecord = $query->fetchrow_array;
    $query->finish;
    
    $sql =" select  count(a.rid) 
from     opl_item as a inner join opl_marcRecord as m on m.rid=a.rid
         inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode
         left outer join (select * from opl_itemstatus where note='' || note is null)  c on b.barcode=c.barcode && b.id  < c.id 
where  c.ondate  is null &&
           b.ondate >= ? &&  b.ondate <= ? &&
           b.status     = 0 ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }


    $query = $dbh->prepare($sql);   
    $query->execute($dateFrom,$dateTo );
    my ($countHolding) = $query->fetchrow_array;

    $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,
    );
  
 }

#----------------------------------------------------------

sub MakePages_bk
{
   my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;  
   my $sql = " select  count(distinct a.rid) 
from    opl_item as a  inner join opl_marcRecord m on m.rid = a.rid 
        inner join opl_itemstatus as b on a.barcode= b.barcode
        left outer join opl_itemstatus c on b.barcode=c.barcode && b.status<>6 && c.status<>6
where   b.id  <= c.id &&  a.barcode  not regexp  '___' && 
        b.ondate >=? && b.ondate <=? &&
        b.status  = 0 ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    my $query = $dbh->prepare($sql);
    $query->execute($dateFrom,$dateTo );
    my $countRecord = $query->fetchrow_array;
    $query->finish;
    
    $sql =" select  count(a.rid) 
from    opl_item as a inner join opl_marcRecord m on m.rid = a.rid 
        inner join opl_itemstatus as b on a.barcode= b.barcode
        left outer join opl_itemstatus c on b.barcode=c.barcode && b.status<>6 && c.status<>6 
where   b.id  <= c.id && a.barcode not regexp  '___' && 
        b.ondate >=? && b.ondate <=? &&
        b.status     = 0 ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }


    $query = $dbh->prepare($sql);   
    $query->execute($dateFrom,$dateTo );
    my ($countHolding) = $query->fetchrow_array;

    $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,
    );
  
 }