#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use POSIX;

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_Search qw(
    
    search_record
    search_record_byBarcode
    
);

use Opals::Tb_Circulation qw(

    circ_infoRecord
    circ_getItemStatus

);
use Opals::Tb_Record qw(
    
    tb_record_findByRId
    tb_item_findByRId

);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

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        => 'txtbk/report/searchWtBc.tmpl',
            #reqPermission   => 'report',
        }
);

my $pRange = 5;
my $pSize = 10;
my $curPage = $input->{'pNum'};

my $kw          = $input->{'kw'};
my $sfCode      = $input->{'sfCode'};
my $sortAttr    = $input->{'sortAttr'};
    if (!$sortAttr ){
        $sortAttr = '245_a';
    }
my $sortOrder   = $input->{'sortOrder'};
    ($sortOrder && $sortOrder >=1 ) || ($sortOrder = 1);
   

my $offset;
    ($curPage && $curPage >= 1) ||($curPage = 1 );
    $offset = ($curPage - 1 ) * $pSize;

my $sortOrder   = $input->{'sortOrder'};
    ($sortOrder && $sortOrder >=1 ) || ($sortOrder = 1);

my ($resultSize, $totalItems, $recordList);

 ($resultSize,$totalItems,$recordList) = search_record_item($dbh, $sfCode, $kw, $offset, $pSize, $sortOrder, $sortAttr);

my @rangedPageList = tmpl_rangedPageList($resultSize, $curPage, $pSize, $pRange);

$template->param(
    sKw         =>  $kw,
    pNum        =>  $curPage,
    sfCode      =>  $sfCode?$sfCode:'245_a',
    sfIndex     =>  ($input->{'sfIndex'})? $input->{'sfIndex'}:0,
    sortAttr    =>  $sortAttr,
    sortOrder   =>  $sortOrder,
    sortOrderReverse    => ($sortOrder && $sortOrder == 1)  ? 2 : 1,
    sortTitle           => ($sortAttr eq '245_a')           ? 1 : 0,
    sortAuthor          => ($sortAttr eq '100_a')           ? 1 : 0,
    sortPublisher       => ($sortAttr eq '260_a')           ? 1 : 0,
    sortPubDate         => ($sortAttr eq '260_c')           ? 1 : 0,
    sortBarcode         => ($sortAttr eq '852_p')           ? 1 : 0,
    sortDown            => ($sortOrder && $sortOrder == 1)  ? 1 : 0,
);

$template->param(
    searchWtBc      => 1,
    resultSize      =>  $resultSize,
    totalItems      =>  $totalItems,
    recordList      =>  \@$recordList,
    rec_start       =>  $offset + 1,
    rec_end         =>  $offset + scalar(@{$recordList}),
    rangedPageList  =>  \@rangedPageList,
);

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

sub search_record_item{

    my ($dbh, $sfCode, $kw, $offset, $pSize,$sortOrder, $sortAttr) = @_;
    my ($sql, $sqlCount, $sqlItems, $sqlICount);
    if ($sfCode eq '852_p' || $sortAttr eq '852_p'){
        $sql        = "SELECT distinct(r1.rid) FROM tb_records r1 LEFT OUTER JOIN tb_items i ON r1.rid = i.rid WHERE_CLAUSE ";
        $sqlCount   = "SELECT COUNT(distinct r1.rid) FROM tb_records r1 LEFT OUTER JOIN tb_items i ON r1.rid = i.rid WHERE_CLAUSE ";
    }
    else{
        $sql        = "SELECT distinct(r1.rid) FROM tb_records r1 LEFT OUTER JOIN tb_records r2 ON r1.rid = r2.rid && r2.fId = '$sortAttr' WHERE_CLAUSE ";
        $sqlCount   = "SELECT COUNT(distinct r1.rid) FROM tb_records r1 LEFT OUTER JOIN tb_records r2 ON r1.rid = r2.rid && r2.fId = '$sortAttr' WHERE_CLAUSE ";
    }

    $kw =~ s/ / \+/g;
    if ($sfCode eq '852_p'){
        $kw =~ s/\*/\%/g;
        $sqlCount   =~ s/WHERE_CLAUSE / where i.barcode like  '$kw'  /;
        $sql        =~ s/WHERE_CLAUSE / where i.barcode like  '$kw'  /;
    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(r1.fVal) AGAINST('+$kw' IN BOOLEAN MODE) && r1.fId = '$sfCode' /;
        $sql        =~ s/WHERE_CLAUSE / where MATCH(r1.fVal) AGAINST('+$kw' IN BOOLEAN MODE) && r1.fId = '$sfCode' /;
    }
   
    if ($sfCode eq '852_p' || $sortAttr eq '852_p'){
         if ($sortOrder && $sortOrder ==2){
            $sql .= " order by length(i.barcode) desc, i.barcode desc ";
        }
        else{
            $sql .= " order by length(i.barcode) asc, i.barcode asc ";
        }
    }
    else{
        if ($sortOrder && $sortOrder ==2){
            $sql .= " order by r2.fVal desc ";
        }
        else{
            $sql .= " order by r2.fVal asc ";
        }
    }
    my $rCount = 0;
        ( $offset >= 0 )|| ( $offset = 0 );
        ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;
    }
    my @recordList = ();
    my $totalItems;
    my $rOrder = -1;
    if ($resultSize > 0){              
        my $query = "$sql LIMIT $offset, $availRange";
        my $sth = $dbh->prepare($query);
        $sth->execute();                
        while(my ($rid) = $sth->fetchrow_array()){
            my ($recId, $recInfo) = tb_record_findByRId($dbh, $rid);
            my ($title,$subTitle, $author, $isbn, $publisher, $pubDate)   = formatRecord(@$recInfo);
            my $itemList;
            if ($sortOrder && $sortOrder ==2){
                $itemList = tb_item_findByRId($dbh, $rid, 'desc');
            }
            else{
                $itemList = tb_item_findByRId($dbh, $rid, 'asc');
            }

            $totalItems += scalar(@$itemList);
            my @itemList_brief =();
            if ($totalItems > 2){
                @itemList_brief = @$itemList[0,1];
            }
            foreach my $item(@$itemList){
                my $itemCircStatus =  circ_getItemStatus($dbh,$item->{'barcode'});
                my ($itemStatus,$colorStyle,$fontStyle)  =  getStyleByStatus($itemCircStatus->{'status'});
                $item->{'status'} = $itemStatus; 
                $item->{'colorStyle'} = $colorStyle;
                $item->{'fontStyle'} = $fontStyle;
            }
            #my $sortBarcode = @$itemList[0]->{'barcode'};
            my $lastIndex =   scalar(@$itemList) - 1;
            my $rowspan = 1;
            if (scalar(@$itemList)  > 2){
                $rowspan = scalar(@$itemList)+1;
                }
            elsif(scalar(@$itemList)  == 2){
                $rowspan = 2;
            }
            $rOrder++;
            push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                publisher   => $publisher,
                pubDate     => $pubDate,
                itemList    => $itemList,
                iList_brief => \@itemList_brief,
                iList_briefShow => scalar(@$itemList)>2?1:0,
                odd         => $rOrder%2,
                rOrder      => $rOrder,
                itemCount   => scalar(@$itemList),
                rwspan    => $rowspan,
            };
        }
        $sth->finish;
    }
    return ($resultSize, $totalItems, \@recordList);
}


sub formatRecord {
    my (@record) = @_;
    my ($title, $subTitle, $author, $isbn, $publisher, $pubDate);
    foreach my $f(@record){
        if ($f->{'fId'} eq '245_a'){
            $title = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '245_b'){
            $subTitle = $f->{'fVal'};
        }
        elsif ($f->{'fId'} eq '100_a'){
            foreach my $a(@{$f->{'fVals'}}){
                $author .= $a->{'fVal'} . " " ;
            }
        }
        elsif($f->{'fId'} eq '020_a'){
            $isbn = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '260_b'){
            $publisher = $f->{'fVal'};
        }
        elsif($f->{'fId'} eq '260_c'){
            $pubDate = $f->{'fVal'};
        }
    }
    return ($title, $subTitle, $author, $isbn, $publisher, $pubDate);
}


sub getStyleByStatus {
     
    my $statusCode = @_;
    my ($itemStatus,$colorStyle,$fontStyle) = ('available','green','normal');

    if($statusCode == 2){
        ($itemStatus,$colorStyle,$fontStyle) = ('reserved','brown','bold');
    }
    elsif($statusCode == 3){
        ($itemStatus,$colorStyle,$fontStyle) = ('on loan','brown','bold');
    }
    elsif($statusCode == 4){
        ($itemStatus,$colorStyle,$fontStyle) = ('overdue','brown','bold');
    }
    elsif($statusCode == 5){
        ($itemStatus,$colorStyle,$fontStyle) = ('on hold','brown','bold');
    }
    elsif($statusCode == 7){
        ($itemStatus,$colorStyle,$fontStyle) = ('lost','brown','bold');
    }
    elsif($statusCode == 8){
        ($itemStatus,$colorStyle,$fontStyle) = ('damage','brown','bold');
    }
    elsif($statusCode == 10){
        ($itemStatus,$colorStyle,$fontStyle) = ('restricted','brown','bold');
    }
    elsif($statusCode == 11){
        ($itemStatus,$colorStyle,$fontStyle) = ('missing','brown','bold');
    }
   
   return ($itemStatus,$colorStyle,$fontStyle) ;
}


__END_OF_FILE: