#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    ceil
);

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);
use Opals::Constant;
use Opals::Tb_Record qw(
    tb_record_findByRId
    tb_item_findByRId
);

use Opals::Tb_Circulation qw(
    circ_getLoanListByRid
    circ_getItemStatus

);
use Opals::User qw(
    user_getInformationById
);
use Opals::Date qw(
    date_text
);

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

my ($sfOrder, $kw, $boolop);
my @sInput;

 
my $sortAttr    = $input->{'sortAttr'};
my $curPage     = $input->{'pNum'};
my $field       = $input->{'sf0'};
my $kw          = $input->{'kw0'};
#my $pSize = 1;
my $curPage = $input->{'pNum'};
my $pSize = ($input->{'pSize'}) ? $input->{'pSize'}:1;

($curPage && $curPage >= 1) || ($curPage = 1);
my $offset = ($curPage - 1) * $pSize + $ENV{'Z_INDEX_BASE'};


my ($resultSize, $totalItem ,$resultRecordList) ;
if ($field && $kw){
    ($resultSize, $totalItem,$resultRecordList) =  search_record_byTitle($dbh, $field, $kw, $sortAttr, $offset, $pSize);

    
$template->param(
    recordReturned      => scalar(@$resultRecordList),
    numberOfHits        => $resultSize,
    nitems              => $totalItem,
    curPage             => $curPage,
    pSize               => $pSize,
    sortAttr            => $sortAttr,
    resultRecordList    => $resultRecordList,
);
}

#print "Content-type: text/xml\n\n";
#print $template->output;

print $cgi->header(
        -type    => 'text/xml',
        -charset => 'utf-8',
    ), $template->output;
##########################################################################
sub search_record_byTitle{

    my ($dbh, $sfCode, $kw, $sortField, $offset, $pSize) = @_;
    my ($sql, $sqlCount, $sqlItems, $sqlICount);

    ($offset >= 0) || ($offset = 0);
    ($pSize  > 0) || ($pSize  = 1);


    $sql        = "SELECT distinct (rid) FROM tb_records WHERE MATCH (fVal) AGAINST ('+$kw' in boolean mode)  && fId = '$sfCode' && deleted <> '1' order by rid ";
    $sqlCount   = "SELECT COUNT(distinct rid) FROM tb_records r inner join tb_items i using(rid) 
                   WHERE MATCH (fVal) AGAINST ('+$kw' in boolean mode) && fId = '$sfCode' && r.deleted <> '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;

    if ($resultSize > 0){              
        my $sth_itemCount = $dbh->prepare($sql);
        $sth_itemCount->execute();
        while(my ($rid) = $sth_itemCount->fetchrow_array()){
            my ($tot) = $dbh->selectrow_array("select count(*) from tb_items where rid =$rid && barcode not regexp '^\_\_\_' ");
                $totalItems = $totalItems + $tot;
        }
        $sth_itemCount->finish;
        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 = tb_item_findByRId($dbh, $rid);
            my $circInfo ;
            my @itemCircList;
            my $nItemOnLoan = 0;
            foreach my $item(@$itemList){
                $circInfo = circ_getItemStatus($dbh,$item->{'barcode'}) ;
                if ($circInfo->{'status'} == IT_STAT_ONLOAN){
                    $nItemOnLoan ++;
                }
                my $username ;
                if ($circInfo && $circInfo->{'l_uid'}){
                    my ($userInfo,$guardien) = user_getInformationById($dbh,$circInfo->{'l_uid'});
                    $username = $userInfo->{'firstname'} . " " . $userInfo->{'lastname'} ;
                }
                push @itemCircList, {
                    barcode     => $item->{'barcode'},
                    dueDate     => $circInfo->{'l_duedate'} ? date_text($circInfo->{'l_duedate'},0) : "",
                    uid         => $circInfo->{'l_uid'} ? $circInfo->{'l_uid'}: "",
                    username    => $username,
                };
            }

            push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                publisher   => $publisher,
                pubDate     => $pubDate,
                nItem       => scalar(@itemCircList),
                nItemOnLoan => $nItemOnLoan,
                itemList    => \@itemCircList,
            };
        }
        $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);
}