#!/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/itemStats.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;


my $status = $input->{'type'};
my $type ;

    if ($status eq '3'){
        $status= ITEM_LOST;
        $type = 'Lost';
    }
    elsif ($status eq '2'){
        $status= ITEM_DAMAGED ;
        $type = 'Damaged';
    }
    elsif ($status eq '0'){
        $status= ITEM_INACTIVE ;
        $type = 'Missing';
    }
    else {
        $status= ITEM_LOST;
        $type = 'Lost';

    }

    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";
    }
=item    
    $template->param(
        dateFrom    => substr($dateFrom,0,10),
        dateTo      => substr($dateTo,0,10),
    );
=cut    
    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
            dateFrom    => substr($dateFrom,0,10),
            dateTo      => substr($dateTo,0,10),

        );
    }
    elsif ($dateRange eq 'all'){
        $template->param(
            rangeAll      => 1,
            dateRangeOpt    => "all",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
            dateFrom    => substr($dateFrom,0,10),
            dateTo      => substr($dateTo,0,10),
        );
    }

    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';    
    }
    elsif ($sort1 eq 'userName'){
        $sortAttr = 'userName';
    }

    my ($resultSize, $resultList) = getList($dbh, $offset, $pSize,  $dateFrom,  $dateTo, $sortAttr ,$sortOrder);
    my @rangedPageList = tmpl_rangedPageList($resultSize, $curPage, $pSize, $pRange);
    $template->param(
        tb_itemLost     => ($status == 3)? 1 : 0,
        tb_itemDamaged  => ($status == 2)? 1 : 0,
        type            => $type,
        status          => $status,
        resultList      => $resultList,
        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,
        sortUserName    => ($sortAttr eq 'userName')    ? 1 : 0,
        sortDown        => ($sortOrder && $sortOrder == 1)  ? 1 : 0,
        rangedPageList  =>  \@rangedPageList,
   );
tmpl_write($dbh, $cgi, $cookie, $template);

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

sub getList{
    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.rid,i.barcode,i.ondate, max(l.id), u.lastname, u.firstname, u.userbarcode,u.uid  
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          
        left outer join tb_loan l on l.barcode = i.barcode  
        left outer join tb_odl odl on l.id = odl.idloan
        left outer join opl_user u on u.uid = l.uid";


    if ($sortAttr eq '245_a'){
        $sql .= " where r.fId = '$sortAttr' group by i.barcode order by fVal";
    } 
    elsif ($sortAttr eq 'recordId'){
        $sql .= " group by i.barcode order by r.rid";
    }
    elsif ($sortAttr eq 'userName'){
        $sql .= " group by i.barcode order by concat(u.lastname, u.firstname, fVal)";
    }
    else {
        $sql .= " group by i.barcode order by $sortAttr";
    }

    if ($sortOrder && $sortOrder ==2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    my @param = $status;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
        
    ( $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($status);
    while(my ($rid,$barcode,$ondate,$idloan,$fname,$lname,$ubarcode,$uid) = $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,
             fname       => $fname,
             lname       => $lname,
             uid         => $uid,
             ubarcode    => $ubarcode,
        };
    }         
    $sth->finish;
    
    return ($resultSize, \@itemLostList);
}

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