#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

use Opals::Constant;

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);

use Opals::Date qw(
    date_text
);

use Opals::Tb_Record qw(
    
    tb_item_findByBarcode

);
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'txtbk/report/itemLost.tmpl',
                reqPermission   => 'tb_report',
            }
    );

my $pref = tmpl_preference($dbh);
my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sortOrder   = $input->{'sortOrder'};
my $pRange = 5;
my $pSize = 10;
my $curPage = $input->{'pNum'};
my $offset;
    ($curPage && $curPage >= 1) ||($curPage = 1 );
    $offset = ($curPage - 1 ) * $pSize;

    if (!$sort1){
        $sort1 = "title";
    }
    if (!$sortOrder){
        $sortOrder    = '2';
    }
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $todayStr;
    }
    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }
    $template->param(
        dateFrom    => substr($dateFrom,0,10),
        dateTo      => substr($dateTo,0,10),
    );
    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
        );
    }
    my $sortAttr = '245_a';
    if ($sort1 eq 'recordId'){
        $sortAttr = 'recordId';
    }
    elsif ($sort1 eq 'title'){
        $sortAttr = '245_a';
    }
    elsif ($sort1 eq 'callNumber'){
        $sortAttr = 'classNumber';    
    }
    elsif ($sort1 eq 'barcode'){
        $sortAttr = 'barcode';    
    }
    elsif ($sort1 eq 'price'){
        $sortAttr = 'price';    
    }
    elsif ($sort1 eq 'ondate'){
        $sortAttr = 'ondate';    
    }
    my ($resultSize, $itemLostList) = getLostTextbookList($dbh, $offset, $pSize,  $dateFrom,  $dateTo, $sortAttr ,$sortOrder);
    my @rangedPageList = tmpl_rangedPageList($resultSize, $curPage, $pSize, $pRange);
    $template->param(
        tb_itemLost     => 1,
        itemLostList    => $itemLostList,
        resultSize      => $resultSize,
        pNum            =>  $curPage,
        sort1           => $sort1,
        sortOrder       =>  $sortOrder,
        sortOrderReverse => ($sortOrder && $sortOrder == 1)  ? 2 : 1,
        sortRecordId    => ($sortAttr eq 'recordId') ? 1 : 0,
        sortTitle       => ($sortAttr eq '245_a')    ? 1 : 0,
        sortCallNumber  => ($sortAttr eq 'classNumber')? 1 : 0,
        sortBarcode     => ($sortAttr eq 'barcode')  ? 1 : 0,
        sortPrice       => ($sortAttr eq 'price')     ? 1 : 0,
        sortOndate      => ($sortAttr eq 'ondate')     ? 1 : 0,
        sortDown        => ($sortOrder && $sortOrder == 1)  ? 1 : 0,
        rangedPageList  =>  \@rangedPageList,
   );

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

#########################################################################

sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}

sub getLostTextbookList{
    my ($dbh,  $offset, $pSize, $dateFrom,$dateTo,$sortAttr,$sortOrder) = @_;
    my @itemLostList = ();



    my $sqlCount = " select count(*)
            from tb_items i1 inner join
            (select ist1.* from
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist1
                left outer join
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i1.barcode=ist.barcode ";


    my $sql = "
    select i.* from (
        select i1.rid ,i1.barcode,ist.ondate,i1.price,i1.classNumber 
            from tb_items i1 inner join
            (select ist1.* from
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist1
                left outer join
                (select * from tb_itemStatus where ondate between '$dateFrom' and  '$dateTo') as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i1.barcode=ist.barcode ) as i 
        inner join tb_records r on r.rid = i.rid" ;
            
    if ($sortAttr eq '245_a'){
        $sql .= " where r.fId = '$sortAttr' group by barcode order by fVal";
    } 
    elsif ($sortAttr eq 'recordId'){
        $sql .= " group by barcode order by r.rid";
    }
    else{
        $sql .= " group by barcode order by $sortAttr";
    }

    if ($sortOrder && $sortOrder ==2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    my @param = ITEM_LOST;
   my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
   #my $resultSize = 16;
        
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );

    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    $sql .= " LIMIT $offset , $availRange ";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute(ITEM_LOST);
    
    while(my ($rid,$barcode,$ondate) = $sth->fetchrow_array()){
        my $itemInfo =  tb_item_findByBarcode($dbh,$barcode);
        push @itemLostList, {
             rid         => $rid,
             title       => $itemInfo->{'title'},
             author      => $itemInfo->{'author'},
             isbn        => $itemInfo->{'isbn'},
             publisher   => $itemInfo->{'publisher'},
             pubDate     => $itemInfo->{'pubDate'},
             price       => $itemInfo->{'price'},
             callnumber  => $itemInfo->{'classNumber'},
             barcode     => $barcode,
             ondate      => $ondate,
        };
    }         
    $sth->finish;
    return ($resultSize, \@itemLostList);
}