#!/usr/bin/perl
#-------------------------------------------------------------------------------------------------------------------------
# UPDATE:  Tues, Mar 22, 2011 @ 08:31:40 EDT
#    /www/odev/htdocs/theme/opals/search/f852sf.tmpl 
#    /www/odev/bin/search/f852sf 
#        
# UPDATE:  Fri, May 06, 2011 @ 08:44:53 EDT
#  1) /www/odev/bin/search/f852sf
#  2) /www/odev/bin/search/f852sf_prt
#  3) /www/odev/htdocs/theme/opals/search/f852sf.tmpl
#  
#-------------------------------------------------------------------------------------------------------------------------
#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
# $cgi->param('aaa') returns an array of aaa
my $input = $cgi->Vars();
#my $op = $input->{'op'};
my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'search/f852sf.tmpl'
    }
);

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('f852sf'));
# See User.pm for the list of permissions
#if ($permission && $permission->{'marc_edit'}) {
my ($code, $data) = ($input->{'code'}, $input->{'data'});

my $sortField = $input->{'sField'};
($sortField =~ m/^(rid|title|author|callNumber|barcode|pubDateSort)$/i) || ($sortField = 'rid');

my $sField = $sortField ;
if($sortField =~ m/barcode/i){
    $sortField = 'i.' .$sortField;
}
my $sortOrder = $input->{'sOrder'};
($sortOrder =~ m/^(asc|desc)$/i) || ($sortOrder = 'asc');
my $pNum= $input->{'pNum'};
 
if ($code && $data) {
    my $MIN_LENGTH = 2;
    my $pSize = 20;
    $pNum = 1 if ( !$pNum );
    my $pOffset = ($pNum - 1) * $pSize;

    my $sql = <<_SQL_;
select  distinct i.rid, i.callNumber,
        m.title, m.author,m.pubDate,m.pubDateSort 
from    opl_itemInfo as ii inner join  opl_item as i on i.barcode=ii.barcode inner join opl_marcRecord as m on m.rid=i.rid

_SQL_
    
    my @bindVal = ();

    $data =~ s/\*/%/g;
    my $dataCondition = ' i.available =1 && ';
  #  if($code eq 'p'){
  #      $dataCondition .=" ii.barcode like ?";
  #      @bindVal=($data);
  #  }
  #  else{
        @bindVal=($code);
        $dataCondition .=" ii.sf852Code = ? ";
        if ($input->{'sType'} =~ m/^exact$/i) {
            $dataCondition .= " && ii.sf852Data = ?";
            @bindVal = (@bindVal, $data);
        }
        elsif ($data =~ m/[\%]/) {
            $dataCondition .= " && ii.sf852Data like ?";
            @bindVal = (@bindVal, $data);
        }
        elsif ($data =~ m/^".+"$/) {
            $dataCondition .= " && ii.sf852Data like ?";
            $data =~ s/(^"|"$)/%/g;
            @bindVal = (@bindVal, $data);
        }
        else {
            foreach my $word (split(/\s/, $data)) {
                if (length $word < $MIN_LENGTH) {
                    $dataCondition .= " && (ii.sf852Data = ? || ii.sf852Data like ? || ii.sf852Data like ? || ii.sf852Data like ?)";
                    @bindVal = (@bindVal, $word, "$word %", "% $word %", "% $word");
                }
                else {
                    $dataCondition .= " && match(sf852Data) against (?)";
                    @bindVal = (@bindVal, $word);
                }
            }
        }
   # }

    $sql .= <<_SQL_;
        where $dataCondition 
        group by i.rid
        order by $sortField $sortOrder
_SQL_
    my $sth = $dbh->prepare($sql);
#limit $pOffset, $pSize
 
    $sth->execute(@bindVal);
    my @recDisplay;
    my $count = 0;
    my $pMax  = $pOffset + $pSize;
    my $rec_end = $pOffset;
    my $recRsPos =$pOffset;
    while (my $r = $sth->fetchrow_hashref) {
        if ($count >= $pOffset && $count < $pMax) {
            $r->{'holding'} = 
                getHoldingInfo($dbh, $r->{'rid'}, $dataCondition, \@bindVal);
            $r->{'holdingCount'} = scalar @{$r->{'holding'}};
            push @recDisplay, $r;
            $rec_end++;
        }
        $r->{'recRsPos'}=$recRsPos++; 
        $count++;
    }
    $sth->finish;

        my @rangedPageList = tmpl_rangedPageList($count, $pNum, $pSize, 10);
    $template->param(
        recDisplay      => \@recDisplay,
        rec_total       => $count,
        rec_start       => $pOffset+1,
        rec_end         => $rec_end,
        rangedPageList  => \@rangedPageList,
        iCode           => $input->{'code'},
        iData           => $input->{'data'},
        "sort_$sField" => 1,
        exactSearch     => ($input->{'sType'} =~ m/^exact$/i) ? 1 : 0,
        marcEdit        => ($permission && $permission->{'marc_edit'}) ? 1 : 0,
        pNum   => $pNum
    );
}


$template->param(
    sField => $sField,
    sOrder => $sortOrder,
);

#}
#else { # input for login form
#    $template->param(
#        input => [
#            {name => '', value => $,},
#            {name => '', value => $,},
#        ],
#    );
#}


#Tue, Jan 12, 2010 @ 10:31:41 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);


tmpl_write($dbh, $cgi, $cookieList, $template);
#$dbh->disconnect();
################################################################################


sub getHoldingInfo {
    my ($dbh, $rid, $dataCondition, $bindValue) = @_;
    $dataCondition =~ s/^ [\&]{2} //;
    my @bindVal = ($rid, @{$bindValue});

    my $sth = $dbh->prepare(<<_SQL_);
select  distinct *
from    opl_item as i inner join opl_itemInfo as ii on i.barcode =ii.barcode
where   i.rid = ? &&
        $dataCondition
_SQL_

    $sth->execute(@bindVal);
    my @holdingInfo;
    while (my $sf = $sth->fetchrow_hashref) {
        push @holdingInfo, $sf;
    }
    $sth->finish;

    return \@holdingInfo;
}