#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Constant;

use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::Date qw(
    date_parse
    date_today
    date_text
);


use Opals::Tb_Record qw(

    tb_item_findByBarcode

);

use Opals::Template qw(
    tmpl_read
    tmpl_write
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $sessionID = $cgi->cookie('globalSessionID');

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


my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $sort1       = $input->{'sort1'};
my $sortOrder   = $input->{'sortOrder'};
    ($sortOrder && $sortOrder >=1 ) || ($sortOrder = 1);
my $prtOpt = $input->{'prtOpt'};   
if (!$sort1){
        $sort1 = "title";
    }
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 ,$recordList);
    if ( $prtOpt eq 'all'){
        ($resultSize, $recordList) = getAllLostTextbookList($dbh,$dateFrom, $dateTo, $sortAttr, $sortOrder);
    }
    else{
        $recordList = getSelectedTextBookList($dbh,$sessionID, "tb_itemLost");
    }
    $template->param(
        recordList    => $recordList,
        );

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


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

sub getSelectedTextBookList{
    my ($dbh, $sessionID, $ssVar) = @_;
    my @itemLostList = ();
    my $sql = "select v.rid , v.barcode, ondate from tb_itemStatus s inner join opl_sessionVar v on s.barcode = v.barcode && s.status = ?
                where v.ssid = '$sessionID' && v.var = '$ssVar' order by v.sOrder ";
    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  \@itemLostList;
}

sub getAllLostTextbookList{
    my ($dbh, $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 $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);
}


__END_OF_FILE: