#!/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/itemlost.tmpl',
            reqPermission   => 'circ_loan|rpt_notice|rpt_catRec',
        }
);


    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 $status=$input->{'type'};
    $status=3 if(!$status || $status ne 2);
        $template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemlost') );
    if($status eq 2){
        $template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemdamaged') );
    }
    my $dateFrom= $input->{'lostFrom'};
    my $dateTo  = $input->{'lostTo'};
     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  = getAllLostItem($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 = GetRecordLostList($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize,$sortDir,$incExcOpt );
    MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt);

$template->param(
    reportType      => ($status eq 3)?3:2,
    lostReport      => ($status eq 3)?1:0,
    damagedReport   => ($status eq 2)?1:0,
    recordList      => $recordList,
    sort            => $sort,
    sortDir         => $sortDir,
    pNum            => $pNum,
    lostList        => 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 getAllLostItem{
    my ($dbh, $dateFrom, $dateTo,$incExcOpt) = @_;
    
 my $sql =" 
select i.rid,i.price, s.barcode,s.ondate ,m.pubDate, m.isbn as ISBN
from opl_marcRecord as m inner join
opl_item as i on m.rid =i.rid inner join
    (select i.rid ,i.barcode,i.price,t5.ondate 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6 && barcode not regexp '^___') as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6 && barcode not regexp '^___') as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in ($status)
            )as t5 on i.barcode=t5.barcode 
    ) as s on i.barcode=s.barcode   ";

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

$sql .= " group by rid";


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

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

################################################################################
sub GetRecordLostList {
    my ($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize,$sortDir,$incExcOpt ) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

 my $sql ="
select  m1.rid,m1.title,m1.author,m.callNumber, m.barcode,m.ondate,m.price ,m1.tempIll ,m1.pubDate, m1.isbn as ISBN
    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.price,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 && barcode not regexp '^___') as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6 && barcode not regexp '^___') as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in ($status)
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid    
    )as m
inner join opl_marcRecord m1 on m1.rid=m.rid";

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

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


     my $sth = $dbh->prepare($sql);
    $sth->execute();
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my $preRid=0;
    while (my $rec = $sth->fetchrow_hashref) {
       # $rec->{'odd'} = $odd ^ 1;
         if($preRid != $rec->{'rid'}){
            $preRid = $rec->{'rid'};
            $pNum++;   
            $rec->{'hidden'}=0;
            $rec->{'odd'} = $pNum%2;     
            $rec->{'order'} = $pNum;
            
        }
        else{
            $rec->{'hidden'}=1;
        }
        my $isbn = $rec->{'ISBN'};
        $isbn =~ s/^\s+|\s+$//gi;
        $isbn =~ s/ /,/gi;
        $rec->{'ISBN'} = $isbn;
        if( $incExcOpt eq 'inclusion' && 
           ($rec->{'tempIll'} eq 'temporary' || $rec->{'tempIll'} eq 'ILL')){
           $rec->{'tempIll'} = 1;
        }

       push @recordList, $rec;
    }
    $sth->finish;

    return \@recordList;

 }



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

sub MakePages
{
   my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;  
     my $sql_count ="
select count(m1.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 && status<>6 && barcode not regexp '^___') as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6 && status<>6 && barcode not regexp '^___') as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in ($status)
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid   group by t2.rid 
)as m  inner join opl_marcRecord m1 on m1.rid=m.rid  ";


  if($incExcOpt eq 'exclusion'){
       $sql_count .= " where (m1.tempIll is NULL ||  m1.tempIll = '') ";
  }
  

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