#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    ceil
);
use JSON;
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_infoRecord

);

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/ajax/search/searchHitList.tmpl',
        }
);
my $sql = "select rTitle.rid as rid, rTitle.fVal as title, 
	    (select GROUP_CONCAT(rAuthor.fVal order by rAuthor.fVal desc separator ';') from tb_records as rAuthor 
		    where  rAuthor.fId = '100_a' && rAuthor.rid = rTitle.rid) as author ,
	    (select GROUP_CONCAT(rISBN.fVal) from tb_records as rISBN 
		    where  rISBN.fId = '020_a' && rISBN.rid = rTitle.rid) as isbn,
	    (select  GROUP_CONCAT(rPubDate.fVal) from tb_records as rPubDate 
		    where  rPubDate.fId = '260_c' && rPubDate.rid = rTitle.rid) as pubDate
        from tb_records  as rTitle 
        where rTitle.fId = '245_a' && rTitle.deleted <> '1' ";


my $sortAttr    = $input->{'sortAttr'};
my $curPage     = $input->{'pNum'};
my $sfCode       = $input->{'searchField'};
my $kw          = $input->{'kw'};
my $curPage = $input->{'pNum'};
my $pSize = $input->{'pSize'} || 1;

($curPage && $curPage >= 1) || ($curPage = 1);
my $offset = ($curPage - 1) * $pSize ;
my $sortAttr    = $input->{'sortAttr'};
$sortAttr = ($sortAttr)? $sortAttr : '245_a';

my $sortOrder   = $input->{'sortOrder'};
$sortOrder = ($sortOrder)?$sortOrder : 0;

my ($resultSize, $recordList);

if ($kw && $sfCode){
    if ($sfCode eq '852_p'){
        ($resultSize, $recordList) = search_record_byBarcode($dbh, $kw, $offset, $pSize, $sortOrder, $sortAttr);
    }
    else{
        ($resultSize, $recordList) = search_record($dbh, $sfCode, $kw, $offset, $pSize, $sortOrder, $sortAttr);

    }
}
foreach my $r( @$recordList){
    my ($numTotal,$numLoan,$numReserve,$numHold,$itemList,$totalCirc,$numLost,$numDamaged,$numMissing)=circ_infoRecord($dbh, $r->{'rid'});
    my $bcList = [];
    if ($sfCode eq '852_p'){
        foreach my $index (0 .. @$itemList-1){
            if ($kw eq $itemList->[$index]->{'barcode'}){
                push @$bcList,$itemList->[$index]; 
                last;
            }
        }
        $r->{'items'} = (scalar(@$bcList) == 1)?$bcList:$itemList;
    }
    else{
        $r->{'items'} = $itemList;
    }
    foreach my $i (@{$r->{'items'}}){
        $i->{'onLoan'} = 0;
        $i->{'onLoanMsg'} = "";
        if (defined $i->{'dateDue'} && $i->{'dateDue'} ne ""){
            $i->{'onLoan'} = 1;
            $i->{'onLoanMsg'} = "currently checked out to " . $i->{'dateDueText'};
        }
    }
}

    $template->param(
        curPage         => $curPage,
        pSize           => $pSize,
        sortAttr        => $sortAttr,
        numberOfHits    => $resultSize,
        recordList      => to_json($recordList,{pretty=>1})
    );

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

sub search_record{
    
    my ($dbh, $sfCode, $kw, $offset, $pSize, $sortOrder, $sortAttr) = @_;
    my $sqlCount = "select COUNT(distinct rid) from tb_records r inner join tb_items i using(rid) WHERE_CLAUSE " ;
    my $sqlRId = "select distinct(rid) from tb_records WHERE_CLAUSE ";
    my @params = ();
    $kw =~ s/ / \+/g;
    if ($sfCode eq '000_a'){
        if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
            $sqlRId     =~ s/WHERE_CLAUSE / where fId = '$sortAttr'/;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            $sqlRId     =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(? IN BOOLEAN MODE) /;
            push @params, '+' . $kw ;
        }
    }
    elsif($sfCode eq '12'){
         if ($kw eq '*'){
            $sqlCount   =~ s/WHERE_CLAUSE /  where rid > 0 /;
            $sqlRId     =~ s/WHERE_CLAUSE /  where rid > 0 /;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE / where rid =  $kw /;
            $sqlRId     =~ s/WHERE_CLAUSE / where rid = $kw /;
        }
    }
    elsif ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
        $sqlRId        =~ s/WHERE_CLAUSE /where fId = '$sfCode'/;
    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(?  IN BOOLEAN MODE) && fId = '$sfCode' /;
        $sqlRId        =~ s/WHERE_CLAUSE / where MATCH(fVal) AGAINST(?  IN BOOLEAN MODE) && fId = '$sfCode' /;
        push @params, '+' . $kw;
    }
    $sqlCount .= " && r.deleted <> '1' ";
    $sqlRId .= " && deleted <> '1' ";
    my $rCount = 0;
        ( $offset >= 0 )|| ( $offset = 0 );
        ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @params);
    my $availRange = ($resultSize < $pSize)? $resultSize: $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 @rIdList = ();
    my $rOrder = $offset;
    my $sortKey = 'title';

    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rids = "";
    if ($resultSize > 0){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute(@params);
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
        if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }
        if ($sortOrder && $sortOrder == 1){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }
        $query .=  " LIMIT $offset , $availRange  ";
        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rOrder      => $rOrder+1,
                rsPos       => $rOrder,
                odd         => ($rOrder % 2)
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}


sub search_record_byBarcode{

    my ($dbh, $barcode ,$offset, $pSize, $sortOrder, $sortAttr) = @_;
    $barcode =~ s/\*/\%/g; 
    my $sqlRId = " SELECT distinct rid from tb_items where barcode like '$barcode' order by rid ";
    my $sqlCount = "select COUNT(distinct (rid)) from tb_items where barcode like '$barcode' order by rid" ;

    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    my $rCount = 0;
        ( $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;    }
    
    my @recordList = ();
    my @rIdList = ();
    my $sortKey = 'title';
    if ($sortAttr eq '100_a')       {
        $sortKey = 'author';
    }
    elsif ($sortAttr eq '020_a'){
        $sortKey = 'isbn';
    }
    elsif($sortAttr eq '260_c'){
        $sortKey = 'pubDate';
    }
    my $rOrder = $offset;
    if ($resultSize > 0 ){
        my $sth = $dbh->prepare($sqlRId);
        $sth->execute();
        while( my ($rid) = $sth->fetchrow_array()){
            push @rIdList,  $rid;
        }
        my $rids = join (',',@rIdList);
        my $query = $sql;
        $query .= ' && rTitle.rid in (' . $rids . ')';
         if ($sortKey eq 'title'){
            $sortKey = " TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FROM TRIM( LEADING 'the ' FROM LOWER( title) ) ) )" ;  
        }

         if ($sortOrder && $sortOrder ==2){
            $query .= " order by $sortKey desc ";
        }
        else{
            $query .= " order by $sortKey asc ";
        }
 
        $query .=  " LIMIT $offset , $availRange  ";
        $sth = $dbh->prepare($query);
        $sth->execute();
        while (my ($recId, $title,$author, $isbn, $pubDate) = $sth->fetchrow_array()){
             push @recordList,  {
                rid         => $recId,
                title       => $title,
                author      => $author,
                isbn        => $isbn,
                pubDate     => $pubDate,
                rsPos       => $rOrder,
                resultOrder => $rOrder,
            };
            $rOrder++;
        }
        $sth->finish;
    }
    return ($resultSize, \@recordList);
}