package Opals::Search;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    srch_parseSearchTerm
    srch_searchRecord
    srch_exclusiveResultSize
    srch_zTab
    srch_zList
    srch_zDatabaseLocal
    srch_barcodeDuplicated
    srch_barcodeUsed
    srch_barcodeLocked
    srch_templateMarcRecord
    srch_templateMarcList
    srch_getMarcImport
    srch_recordBrief
    srch_systemList
    srch_infoSystem
    srch_infoLibrary
    srch_maxRid
    srch_validateRid
    srch_F852Default_marc21
    srch_F852Default_marcxml
    srch_briefRecordById
    srch_getRecType
    srch_searchRecordSQL
    srch_buildArlSQL
    srch_buildSchTitleStartWithSQLs
    srch_buildPQF
    srch_buildPQF_owl
    srch_buildNewItemSQLs
    srch_searchSubjectStartWithSQLs
    srch_searchSubjectContainWithSQLs
    srch_searchAuthorSQLs
    
    srch_zDatabase
    srch_getIdenticalRIDs
);
#    srch_searchField
#    srch_searchFieldIndex
# Version number
$VERSION   = 0.01;      


use strict;
use Business::ISBN;
use Encode;
use ZOOM;
use MARC::File::USMARC;
use MARC::File::XML;
use POSIX qw(
    ceil
);
use Time::HiRes qw(
    usleep
);

use Opals::Context;
use Opals::Date qw(
    date_f005
);
use Opals::Marc::Record;
use Opals::Utf8 qw(
    utf8_fromMarc8
);
use Opals::Utility qw(
    util_filterMarcXml
    util_ieFix
    util_getXmlRecord
);


# Constant
use vars qw(
    @RECORD_IDENTIFIERS
);

@RECORD_IDENTIFIERS = (
    {
        tag =>'035',
        code=> 'a',
    },
    {
        tag => '020',
        code=> 'a',
    }
);
my @zSyntax = (
    {val =>  '0', name => 'XML',},
    {val =>  '1', name => 'USMARC',},
);



sub srch_parseSearchTerm {
    my ($term) = @_;
   $term =~ s/[\s]+/ /g;
   $term =~ s/(^| )((AND|OR|NOT) )*/$1$2/g;

    my @andTerm = ();
    my @orTerm  = ();
    my @notTerm = ();
    my $sTerm;
    # Process exact phrase first
    while ($term =~ s/((^| )(AND|OR|NOT) )*("[^\"]+")(.*)/$5/g) {
        push @andTerm, $4 if ($3 eq 'AND' || $3 eq '');
        push @orTerm,  $4 if ($3 eq 'OR');
        push @notTerm, $4 if ($3 eq 'NOT');
    }
    while ($term =~ s/((^| )(AND|OR|NOT) )*(\S+)(.*)/$5/g) {
        $sTerm =$4;
        #$sTerm =~ s/\W/\\*/g;
        $sTerm =~ s/[,|.|;|!]+$/ /g;
        if($sTerm ne ''){ 
            push @andTerm, $sTerm if ($3 eq 'AND' || $3 eq '');
            push @orTerm,  $sTerm if ($3 eq 'OR');
            push @notTerm, $sTerm if ($3 eq 'NOT');
            }
    }
    my $pqfTerm = '';
    foreach $sTerm (@andTerm) {
        if ($sTerm =~ m/^"(.*)"$/g) {
            $sTerm ="\@attr 4=1 $sTerm ";
        }
        else{
            $sTerm ="\@attr 4=2 $sTerm ";
        }

        #if ($sTerm =~ s/\*/\.\*/g) {
        if ($sTerm =~ s/\*[.]*$//g) {
            $sTerm = "\@attr 5=1 $sTerm";
        }
        #if ($sTerm =~ s/\?/\./g) {
        #    $sTerm = "\@attr 5=1 $sTerm";
        #}
        if ($pqfTerm eq '') {
            $pqfTerm = $sTerm;
        }
        else {
            $pqfTerm = "\@and $pqfTerm $sTerm";
        }
        
    }
    foreach $sTerm (@notTerm) {
        if ($sTerm =~ m/^"(.*)"$/g) {
            $sTerm ="\@attr 4=1 $sTerm ";
        }
        else{
            $sTerm ="\@attr 4=2 $sTerm ";
        }

        #if ($sTerm =~ s/\*/\.\*/g) {
        if ($sTerm =~ s/\*[.]*$//g) {
            $sTerm = "\@attr 5=1 $sTerm";
        }
        if ($sTerm =~ s/\?/\./g) {
            $sTerm = "\@attr 5=1 $sTerm";
        }
        if ($pqfTerm eq '') {
            $pqfTerm = $sTerm;
        }
        else {
            $pqfTerm = "\@not $pqfTerm $sTerm";
        }
    }
    foreach $sTerm (@orTerm) {
        if ($sTerm =~ m/^"(.*)"$/g) {
            $sTerm ="\@attr 4=1 $sTerm ";
        }
        else{
            $sTerm ="\@attr 4=2 $sTerm ";
        }

        #if ($sTerm =~ s/\*/\.\*/g) {
        if ($sTerm =~ s/\*[.]*$//g) {
            $sTerm = "\@attr 5=1 $sTerm";
        }
        if ($sTerm =~ s/\?/\./g) {
            $sTerm = "\@attr 5=1 $sTerm";
        }
        if ($pqfTerm eq '') {
            $pqfTerm = $sTerm;
        }
        else {
            $pqfTerm = "\@or $pqfTerm $sTerm";
        }
    }

    return $pqfTerm;
}


sub srch_zList {
    my ($dbh, $type, $zidActive) = @_;
    my @zdbList = ();

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zDatabase
where   $type=1
order by zid
_STH_
    $sth->execute() || return;
    while (my $zdb = $sth->fetchrow_hashref) {
        $zdb->{'active'} = 1 if ($zdb->{'zid'} == $zidActive);
        push @zdbList, $zdb;
    }
    $sth->finish;
    
    return @zdbList;
}


sub srch_zTab {
    my ($dbh, $zidActive) = @_;
    my @zdbList = ();

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zDatabase
where   state=1
order by zid
_STH_
    $sth->execute() || return;
    while (my $zdb = $sth->fetchrow_hashref) {
        $zdb->{'active'} = 1 if ($zdb->{'zid'} == $zidActive);
        push @zdbList, $zdb;
    }
    $sth->finish;
    
    return @zdbList;
}


sub srch_zDatabase {
    my ($dbh, $zid) = @_;
    my $zdb;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zDatabase
where   zid = ?
_STH_
    $sth->execute($zid) || return;
    $zdb = $sth->fetchrow_hashref;
    $sth->finish;

    return $zdb;
}


sub srch_zDatabaseLocal {
    my ($dbh) = @_;
    my $zdb;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zDatabase
where   zid = 0
_STH_
    $sth->execute || return;
    $zdb = $sth->fetchrow_hashref;
    $sth->finish;

    return $zdb;
}


sub srch_searchRecord {
    my ($dbh, $zid, $resultType, $pqf, $offset, $pSize, $uid, $direction) = @_;


    ($offset >= $ENV{'Z_INDEX_BASE'}) || ($offset = $ENV{'Z_INDEX_BASE'});
    ($pSize  >= 1) || ($pSize  = 1);
    ($direction) || ($direction = 1);

    my $zdb = srch_zDatabase($dbh, $zid);
    return unless $zdb;

    my $enc = 'a';
    if ($zdb->{'encoding'} eq 'marc8') {
        $enc = ' ';
    }

    my $syntax = $zSyntax[$zdb->{'syntax'}]->{'name'};

    my $options = new ZOOM::Options();
    if ($zdb->{'username'} && $zdb->{'password'}) {
        $options->option(user => $zdb->{'username'});
        $options->option(password => $zdb->{'password'});
    }
    $options->option(databaseName => $zdb->{'name'});
    $options->option(elementSetName => 'F');
    $options->option(preferredRecordSyntax => lc($syntax));

    my $conn = create ZOOM::Connection($options);
    eval {
        $conn->connect($zdb->{'host'}, $zdb->{'port'});
        1;
    }
    or do {
        return (-1, [{
                err_code    => $@->code(),
                err_mesg    => $@->message(),
            }]);
    };

    my $rs;
    eval {
        $rs = $conn->search_pqf($pqf);
        1;
    }
    or do {
        return (-1, [{
                err_code    => $@->code(),
                err_mesg    => $@->message(),
            }]);
    };

    # return 0 if search failed
    unless (defined $rs) {
        return (0, undef);
    }

    my $resultSize  = $rs->size();
    my $result_limit = $resultSize + $ENV{'Z_INDEX_BASE'};

    my $maxCount = $offset + $pSize;# + $ENV{'Z_INDEX_BASE'};
    if ($maxCount > $result_limit) {
        $maxCount = $result_limit;
        $offset = $maxCount - $resultSize % $pSize;
    }
    
    my @result = ();
    if ($result_limit > $ENV{'Z_INDEX_BASE'}) {
        if ($zid && $uid) {
            srch_deleteResult($dbh, $uid);
        }

        my $odd_row = 0;
        for (my $i = $offset;
                ($i >= $ENV{'Z_INDEX_BASE'} && $i < $maxCount) || (scalar(@result)==0 && $i < $result_limit);
                $i += $direction) {
            my $rec;
#            for (my $failure = 0; $failure < 5 && !$rec; $failure++) {
                $rec = $rs->record($i);
#                if (!$rec) {
#                    usleep 100_000;
#                }
#            }

            if (!$rec) {
                if ($resultType =~ m/^b$/i) {
                    push @result, undef;
                }
                next;
            }

            my $raw = $rec->raw();
            my $xml;

            if (uc($syntax) eq 'XML') {
                $xml = $raw;
#                $xml =~ s/[\s]*<subfield code="-">.*<\/subfield>//g;
#                $xml =~ s/[\s]*<subfield code="\+">.*<\/subfield>//g;
                $xml =~ s/[\s]*<idzebra.+>([\s]*<.+>.+<\/.+>)+[\s]*<\/idzebra>//g;
            }
            elsif (uc($syntax) eq 'USMARC') {
                substr($raw, 9, 1, $enc);

                my $marc = MARC::File::USMARC->decode($raw);

                if ($zdb->{'encoding'} eq 'marc8') {
                    $marc = utf8_fromMarc8($marc);
                }
                $xml = MARC::File::XML::record($marc);
            }
            else {
                return;
            }

                if ($zid > 0 && $uid) {
                    $xml = srch_saveResult($dbh, $uid, $i, $xml);
#                    $xml = srch_saveResult($dbh, $uid, $i, $xml, 
#                            'XML', $zdb->{'encoding'});
                }

                #$xml = util_ieFix($xml);

                if ($resultType =~ m/^f$/i) {
                    push @result, $xml;
                }
                elsif ($resultType =~ m/^f852$/i) {
                    my $f852 = '';
                    while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
                        $f852 .= $1;
                    }
                    push @result, $f852;
                }
                elsif ($resultType =~ m/^subjectHeading$/i) {
                    foreach my $res (record_subjectHeading($xml)) {
                        push @result, $res;
                    }
                }
                else {
                    my $recordBrief = srch_recordBrief($xml, $zdb->{'filter'});
                    $recordBrief->{'resultOrder'} = $i;
                    $recordBrief->{'odd'} = $i%2;
                    if ($zid) {
                        $recordBrief->{'xml'} = $xml;
                    }

                    push @result, $recordBrief;
                }
# Thu, Feb 25, 2010 @ 09:21:53 EST
# All MARC record processing will be based on MARC XML. After a while, obsolete codes should be removed.
#            }
#            elsif (uc($syntax) eq 'USMARC') {
#                substr($raw, 9, 1, $enc);
#
#                my $marc = MARC::File::USMARC->decode($raw);
#
#                if ($zid > 0 && $uid) {
#                    $marc = srch_saveResult($dbh, $uid, $i, $marc, 'USMARC', $zdb->{'encoding'});
#                }
#
#                if ($zdb->{'encoding'} eq 'marc8') {
#                    $marc = utf8_fromMarc8($marc);
#                }
#                if ($resultType =~ m/^b$/i) {
#                    my $record =getRecordBrief($marc);
#
#                    if ($zdb->{'encoding'} eq 'marc8') {
#                        foreach my $k ('author', 'title', 'pubName', 'pubDate', 'numOfPages') {
#                            $record->{$k} = encode_utf8($record->{$k});
#                        }
#                    }
#
#                    if ($zid > 0) {
#                        $record->{'xml'} = MARC::File::XML::record($marc);
#                    }
#
#                    push @result, $record;
#                }
#            }
        }
    }

    # Delete record set: it takes a century to delete a record set when
    # connected to z3950.loc.gov:7090/voyager. However, it's OK to delete a
    # record set when connected to a Zebra server.
    # TODO: delete record only when connected to a Zebra server.
    #$rs->delete() if ($zdb->{'host'} ne 'z3950.loc.gov');
    #$rs->delete() if (!$zid);
    $rs->destroy() if (!$zdb->{'syntax'});
    $conn->destroy();

    return ($resultSize, \@result);
}

#///////////////////////////////////////////////////////////////////////////////
sub srch_getRecType{
    my ($leader,$cf006,$cf007,$cf008) = @_;

    my($l_06,$l_07,$cf006_00,$cf007_00,$cf007_01,$cf008_23,$cf008_26)=('','','','','');


    $l_06=substr($leader,6,1);
    $l_07=substr($leader,7,1);
    $cf006_00=lc(substr($cf006,0,1)) if(length($cf006)>0);
    $cf007_00=lc(substr($cf007,0,1)) if(length($cf007)>0);
    $cf007_01=lc(substr($cf007,1,1)) if(length($cf007)>1);

    $cf008_23=lc(substr($cf008,23,1));
    $cf008_26=lc(substr($cf008,26,1));

    my $recType="book";

# Journal ,serial
    if($l_06 =~ m/[a]/ && $l_07 =~ m/[bis]/){ 
        $recType="journal";
    }
# Book|eBook
    elsif($l_06  =~ m/[t]/ || ($l_06 =~ m/[a]/)) {
        if($cf006_00 eq 'm' && $cf007_00 eq 'c' && $cf007_01 eq 'r' &&
            $cf008_23 eq 's'){
             $recType ="ebook";
        }
        elsif($l_07 =~ m/[acdm]/){
             $recType="book";
        }
    }
=item    
# Book|eBook
   elsif($l_06  =~ m/[t]/ || ($l_06 =~ m/[a]/ && $l_07 =~ m/[acdm]/) ){
        if($cf006_00 eq 'm' && $cf007_00 eq 'c' && $cf007_01 eq 'r' &&  $cf008_23 eq 's'){
            $recType="ebook";
        }
        else{
            $recType="book";
        }
    }
=cut    
# Artifact 
    elsif($l_06 =~ m/[r]/ ){
        $recType="artifact";
    }
# Photos, Posters 
    elsif($l_06 =~ m/[k]/ ){
        $recType="photoPoster";
    }
# Music Cassette 
    elsif($l_06 =~ m/[j]/ ){
        $recType="musicCassette";
    }
# Book on Tape
    elsif($l_06 =~ m/[i]/ ){
        $recType="bookOnTape";
    }
    
# Videocassette
#    elsif($cf007_00 =~ m/[v]/){
#        $recType="Videocassette";
#    }

# Movie
    elsif($l_06 =~ m/[g]/ ){
        $recType="movie";
    }
    
# Library Kit
    elsif($l_06 =~ m/[o]/){
        $recType="libraryKit";
    }
    
# Mixed Material
    elsif($l_06 =~ m/[p]/){
        $recType="mixedMaterial";
    }
# Map
    elsif($l_06 =~ m/[ef]/){
        $recType="map";
    }
# Sheet Music
    elsif($l_06 =~ m/[cd]/){
        $recType="sheetMusic";
    }
    elsif($l_06 =~ m/[m]/){
# Internet        
        if($cf008_26 =~ m/[j]/){
            $recType="internet";
        }
# Electronic Media
        else{
            $recType="electronicMedia";
        }
    }
# CD Music
    elsif($cf007_00 =~ m/[s]/ && $cf007_01 =~ m/[d]/){
        $recType="CDMusic";
    }
# Compact Disc
    elsif($cf007_00 =~ m/[c]/ && $cf007_01 =~ m/[o]/){
        $recType="compactDisc";
    }

    return $recType;
    
}
#/////////////////////////////////////////////////////////////////////////////////
sub srch_getRecType_bk{
    my($leader,$cf007,$cf008)=@_;
    my($l_06,$l_07,$cf007_00,$cf007_01,$cf008_26)=('','','','','');
    $l_06=substr($leader,6,1);
    $l_07=substr($leader,7,1);
    my $type="";
    if($cf007 && length($cf007)>0){
        $cf007_00=substr($cf007,0,1);
    }
    if($cf007 && length($cf007)>1){
        $cf007_01=substr($cf007,1,1);
    }
    if($cf008 && length($cf008)>26){
        $cf008_26=substr($cf008,26,1);
    }
# Journal ,serial
    if($l_06 =~ m/[a]/ && $l_07 =~ m/[sb]/){ 
        $type='as';
    }
# Book
    elsif($l_06 =~ m/[at]/ ){
        $type='a';
    }
# Artifact 
    elsif($l_06 =~ m/[r]/ ){
        $type=$l_06;
    }
# Photos, Posters 
    elsif($l_06 =~ m/[k]/ ){
        $type=$l_06;
    }
# Music Cassette 
    elsif($l_06 =~ m/[j]/ ){
        $type=$l_06;
    }
# Book on Tape
    elsif($l_06 =~ m/[i]/ ){
        $type=$l_06;
    }
    
# Videocassette
#    elsif($cf007_00 =~ m/[v]/){
#        $type='vr';
#    }

# Movie
    elsif($l_06 =~ m/[g]/ ){
        $type=$l_06;
    }
    
    
# Libray Kit
    elsif($l_06 =~ m/[op]/){
        $type='o';
    }
# Map
    elsif($l_06 =~ m/[ef]/){
        $type='e';
    }
# Sheet Music
    elsif($l_06 =~ m/[cd]/){
        $type='c';
    }
    elsif($l_06 =~ m/[m]/){
# Internet        
        if($cf008_26 =~ m/[j]/){
            $type='mj';
        }
# Electronic Media
        else{
            $type='m';
        }
    }
# CD Music
    elsif($cf007_00 =~ m/[s]/ && $cf007_01 =~ m/[d]/){
        $type='sd';
    }
# Compact Disc
    elsif($cf007_00 =~ m/[c]/ && $cf007_01 =~ m/[o]/){
        $type='co';
    }
# default set to book
    else{
        $type='a';
    }
     
}
#//////////////////////////////////////////////////////////////////////////
#
sub srch_recordBrief {
    my ($xml, $filter) = @_;
    my $marc = Opals::Marc::Record::newFromXml($xml);
    if ($filter) {
        foreach my $f852 ($marc->field('852')) {
            if ($f852->subfield('+') !~ m/^$filter/) {
                $marc->delete_fields($f852);
            }
        }
    }

    my $recBrief = getRecordBrief($marc);
    my $ARinfo=getAR_info($xml);
    $recBrief->{'ARinfo'}=$ARinfo;
    return $recBrief;
}
sub getRecordBrief{
    my($marc)=@_;        
    my $record;
    my ($leader,$c006,$c007,$c008)=("","","");
    $leader = $marc->leader();
    
    my $tmp_field = $marc->field('006');
    if ($tmp_field) {
        $c006 = $tmp_field->data();
    }
    $tmp_field = $marc->field('007');
    if ($tmp_field) {
        $c007 = $tmp_field->data();
    }
    
    $tmp_field = $marc->field('008');
    if ($tmp_field) {
        $c008 = $tmp_field->data();
    }
    
    $record->{'medium'} = srch_getRecType($leader,$c006,$c007,$c008);

    $tmp_field = $marc->field('001');
    if ($tmp_field) {
        $record->{'rid'} = $tmp_field->data();
    }
#     Thai
#     added     2008-02-13
#       - add isbn property ;for book cover lookup
    my @isbnList=();
    foreach my $f020 ($marc->field('020')){
        my $tmp ="";
        my $isbn=$f020->subfield('a');

        if( $isbn ne ''){
             $isbn =~ m/([\D^x^X]*)([\dxX][\dxX -]{8,}[\dxX])([\D^x^X]*)/;
             $tmp = $2;
             $tmp =~ s/[- ]//g;
            push @isbnList, $tmp;
        }
    }
    $record->{'isbn'}=\@isbnList;
=item    
    my $f020a =$marc->subfield('020','a');
    if ($f020a &&
        $f020a =~ m/([\D^x^X]*)([\dxX][\dxX -]{8,}[\dxX])([\D^x^X]*)/) {
        $record->{'isbn'} = $2;       
        $record->{'isbn'} =~ s/[- ]//g;
    }
=cut    
#  end 2008-02-13
      
    $record->{'author'} = $marc->subfield('100', 'a');

    $record->{'title'} = $marc->subfield('245', 'a');
    $record->{'title'} =~ s/ *[:\.\/] *$//;
    $record->{'titleBrief'} = $record->{'title'};
    my $str = $marc->subfield('245', 'b');
    if ($str) {
        $record->{'title'} .= ': ' . $str;
    }
    $str = $marc->subfield('245', 'p');
    if ($str ) {
           $record->{'title'} .=  ': ' . $str;
    }
    $str = $marc->subfield('245', 'n');
    if ($str) {
            $record->{'title'} .= ': ' . $str;
    }
    
    $record->{'title'} =~ s/ +\///g;
    
    $record->{'pubPlace'} = $marc->subfield('260', 'a');
    $record->{'pubName'} = $marc->subfield('260', 'b');
    $record->{'pubDate'} = $marc->subfield('260', 'c');

    $record->{'numOfPages'} = $marc->subfield('300', 'a');

    my $catalogueType = Opals::Context->config('type');
    
    my @itemList;
    my $callNum1st = '';
    my $count852a;
    my $count852b;
    my @list852a=();
    my $list852c;
    foreach my $f852 ($marc->field('852')) {
        my ($f852_a, $f852_b,$f852_c, $barcode, $callnumber, $typeCircCode, $price);

        $f852_a = $f852->subfield('a');
        $f852_b = $f852->subfield('b');
        $f852_c = $f852->subfield('c');

        $barcode = $f852->subfield('p');
        $callnumber = 
                $f852->subfield('k') . ' ' .
                $f852->subfield('h') . ' ' .
                $f852->subfield('i') . ' ' .
                $f852->subfield('m');
        $callnumber =~ s/ +/ /g;
        $callnumber =~ s/(^ +| +$)//g;
        $typeCircCode = $f852->subfield('3');
        $price = $f852->subfield('9');

        my $item;
        $item->{'barcode'}      = $barcode;
        $item->{'callnumber'}   = $callnumber;
        $item->{'typeCircCode'} = $typeCircCode;
        $item->{'price'}        = $price;
        $item->{'f852_a'}       =  $f852_a;
        $item->{'f852_b'}       =  $f852_b;
        $item->{'f852_c'}    =  $f852_c;

        $list852c->{$f852_c} +=1   if($f852_c ne '');

        $count852a->{$f852_a}->{$f852_b}->{'count'} =  
            $count852a->{$f852_a}->{$f852_b} ? ($count852a->{$f852_a}->{$f852_b}->{'count'} + 1) : 1;  
        push @itemList, $item;
        
        $callNum1st = $callnumber if ($callNum1st eq '');

        $count852a->{$f852_a}->{$f852_b}->{'location'} = $f852->subfield('+');
 


    }
    my $rowspan=0;
    foreach my $k (sort keys %$count852a) {
        my  @list852b=();
        my $tmp=$count852a->{$k};
        foreach my $b(sort keys %$tmp){
            push @list852b,{
                code852b => $b,
                count852b =>$count852a->{$k}->{$b}->{'count'},
                location    => $count852a->{$k}->{$b}->{'location'},
            }
        }
        push @list852a, {
            code852a  => $k,
            list852b=>\@list852b,
            num852ba =>scalar(@list852b),
            count852a => $count852a->{$k}
        };
        $rowspan += 1;
    }
    $rowspan=$rowspan==0?1:$rowspan;

    $record->{'stat852a'} = \@list852a;
    $record->{'count852a'} = scalar(@list852a);
    
    $record->{'callNum1st'} = $callNum1st;
    $record->{'itemList'}   = \@itemList;
    $record->{'rowspan'}    = $rowspan;
    my @locations;
    foreach my $loc(keys %$list852c){
        push @locations,"$loc($list852c->{$loc})";
    }
    $record->{'location'}   = join(",<br>", @locations);

    return $record;
}

sub srch_filterFieldIdentical {
    my ($tagno, $tagdata) = @_;

    return ($tagno eq '020') || ($tagno eq '035');
}


sub record_subjectHeading {
    my ($xml) = @_;

    my @f6xx = ();

    while ($xml =~ s/([\s]*<datafield tag="6[\d]{2}" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
        my $f6xx = $1;
        my $subject;
        my @subfield6xx = ();
        while ($f6xx =~ s/<subfield code="[\w]">(.*)<\/subfield>//) {
            my $sf6xx = $1;
            $sf6xx =~ s/[ ,:\/\.]+$//;
            push @subfield6xx, {sf6xx => $sf6xx};
        }
#        @subfield6xx = sort { $a->{'sf6xx'} cmp $b->{'sf6xx'} ||
#                              $a->{'sf6xx'} <=> $b->{'sf6xx'} } @subfield6xx;
        push @f6xx, \@subfield6xx;
    }

    return @f6xx;
}


sub srch_exclusiveResultSize {
    my ($dbh, $zid, $pqf) = @_;
    my @exclusiveResultSize = ();
    
    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zDatabase
where   zid != ? &&
        state = 1
order by zid
_STH_
    $sth->execute($zid) || return;
    while (my $zdb = $sth->fetchrow_hashref) {
        my $syntax = $zSyntax[$zdb->{'syntax'}]->{'name'};

        my $options = new ZOOM::Options();
        if ($zdb->{'username'} && $zdb->{'password'}) {
            $options->option(user => $zdb->{'username'});
            $options->option(password => $zdb->{'password'});
        }
        $options->option(databaseName => $zdb->{'name'});
        $options->option(elementSetName => 'F');
        $options->option(preferredRecordSyntax => lc($syntax));

        my $conn = create ZOOM::Connection($options);
        # return undef if connection failed
        unless (defined $conn) {
            next;
        }

        $conn->connect($zdb->{'host'}, $zdb->{'port'});

        my $rs = $conn->search_pqf($pqf);
        unless (defined $rs) {
            next;
        }
        
        $zdb->{'rsSize'} = $rs->size();
        
        # Delete record set: it takes a century to delete a record set when
        # connected to z3950.loc.gov:7090/voyager. However, it's OK to delete a
        # record set when connected to a Zebra server.
        # TODO: delete record ser only when connected to a Zebra server.
        #$rs->delete() if ($zdb->{'host'} ne 'z3950.loc.gov');
        $rs->destroy();
        $conn->destroy();

        push @exclusiveResultSize, $zdb;
    }
    $sth->finish;
    
    return @exclusiveResultSize;
}


sub srch_barcodeDuplicated {
    my ($dbh, $barcodeList) = @_;

    my @bc = split /,/, $barcodeList;
    return if (@bc == 0);

    my $query = <<_STH_;
select  barcode
from    opl_item
where   barcode = ?
_STH_
    for (my $i = 1; $i < @bc; $i++) {
        $query .= ' || barcode = ?';
    }
    
    my $sth = $dbh->prepare($query);
    $sth->execute(@bc) || return;

    my $barcodeDuplicated = '';
    while (my $holding = $sth->fetchrow_hashref()) {
        $barcodeDuplicated .= $holding->{'barcode'} . ',';
    }
    $sth->finish;

    # remove last comma
#    $barcodeDuplicated =~ s/,$//;

    return $barcodeDuplicated;
}


sub srch_barcodeUsed {
    my ($dbh, $rid) = @_;

    return '' if ($rid && $rid == 0);

    my $sth = $dbh->prepare(<<_STH_);
select  barcode
from    opl_item
where   rid = ? &&
        substring(barcode, 1, 3) <> '___'
_STH_
#        available = 1
    $sth->execute($rid) || return;

    my $barcodeUsed = '';
    while (my $holding = $sth->fetchrow_hashref()) {
        $barcodeUsed .= $holding->{'barcode'} . ',';
    }
    $sth->finish;
    # remove last comma
#    $barcodeUsed =~ s/,$//;

    return $barcodeUsed;
}


sub srch_barcodeLocked {
    my ($dbh, $rid) = @_;

    return '' if ($rid && $rid == 0);
    
    my $barcodeLocked = '';

    # Holding on loan
    my $sth = $dbh->prepare(<<_STH_);
select  L.barcode
from    opl_item as I, opl_loan as L
where   I.rid = ? &&
        I.barcode = L.barcode &&
        L.dateReturn is null
_STH_
    $sth->execute($rid) || return;

    while (my $holding = $sth->fetchrow_hashref()) {
        $barcodeLocked .= $holding->{'barcode'} . ',';
    }
    $sth->finish;

    # Holding on hold
    $sth = $dbh->prepare(<<_STH_);
select  H.barcode
from    opl_item as I, opl_hold as H
where   I.rid = ? &&
        I.barcode = H.barcode &&
        H.dateLoan is null &&
        H.dateCancel is null &&
        to_days(now()) - to_days(H.dateExpiry) < 0
_STH_
    $sth->execute($rid) || return;

    while (my $holding = $sth->fetchrow_hashref()) {
        $barcodeLocked .= $holding->{'barcode'} . ',';
    }
    $sth->finish;

    # remove last comma
#    $barcodeLocked =~ s/,$//;

    return $barcodeLocked;
}


sub srch_templateMarcRecord {
    my ($dbh, $tmplMarcId) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_template
where   tid = ?
_STH_
    $sth->execute($tmplMarcId) || return;

    my $tmplMarc = $sth->fetchrow_hashref() || return;
    $sth->finish;

    return $tmplMarc;
}


sub srch_templateMarcList {
    my ($dbh) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_template
order by rank, name, tid
_STH_
    $sth->execute() || return;

    my @tmplMarcList;
    while (my $tmplMarc = $sth->fetchrow_hashref()) {
        push @tmplMarcList, $tmplMarc;
    }
    $sth->finish;

    return \@tmplMarcList;
}


sub srch_deleteResult {
    my ($dbh, $uid) = @_;

    my $sth = $dbh->prepare(<<_STH_);
delete from opl_zResult
where       uid = ?
_STH_
    $sth->execute($uid) || return;
    $sth->finish;

    return 1;
}


sub srch_saveResult {
    my ($dbh, $uid, $resultId, $record) = @_;
#    my ($dbh, $uid, $resultId, $record, $syntax, $encoding) = @_;

    my ($content, $holdingInfo, $f852h);
    $holdingInfo = '';
#    if ($syntax eq 'XML') {
        $record =~ s/[\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">(([\s]*<subfield code="[\w\-\+]">.*<\/subfield>)*)[\s]*<\/datafield>//g;
        $holdingInfo = $1;
        $holdingInfo =~ s/[\s]*<subfield code="[^khi9]">.*<\/subfield>//g;
        $holdingInfo =~ s/[\s]*<subfield code="//g;
        $holdingInfo =~ s/">/=/g;
        $holdingInfo =~ s/<\/subfield>[\s]*/\t/g;
        if ($holdingInfo !~ m/h=[^\t]*\t/) {
            my $field;
            foreach my $tag ('082', '090') {
                if ($record =~ m/(<datafield tag="$tag" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
                    $field = $1;
                    if ($field =~ m/<subfield code="a">(.*)<\/subfield>/) {
                        $f852h = $1;
                        $f852h =~ s/[\[\]]//g;
                    }
                }
                if ($f852h) {
                    $holdingInfo .= "h=$f852h";
                    last;
                }
            }
        }
        my $f9xx;
        my $f940 = '';
        while ($record =~ s/([\s]*<datafield tag="9[\d]{2}" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
            $f9xx = $1;
            if ($f9xx =~ m/datafield tag="940"/) {
                $f940 .= $f9xx;
            }
        }
        $record =~ s/<\/record>/$f940<\/record>/ if ($f940 ne '');
        $content = $record;
#    }
#    elsif ($syntax eq 'USMARC') {
#        my $fieldFirst;
#        foreach my $field ($record->field('852')) {
#            $record->delete_field($field);
#            $fieldFirst = $field if (!$fieldFirst);
#        }
#        if ($fieldFirst) {
#            foreach my $sf ($fieldFirst->subfields) {
#                if ($sf->[0] =~ m/^(k|h|i|9)$/) {
#                    $holdingInfo .= "$sf->[0]=$sf->[1]\t";
#                }
#            }
#        }
#        if ($holdingInfo !~ m/h=[^\t]*\t/) {
#            my $f852h = $record->subfield('082', 'a');
#            $f852h = $record->subfield('090', 'a') unless $f852h;
#            $holdingInfo .= "h=$f852h" if $f852h;
#        }
#
#        foreach my $field ($record->field('9..')) {
#            $record->delete_field($field) if ($field->tag != 940);
#        }
#        $content = $record->as_usmarc;
#    }
    $holdingInfo =~ s/[abp]=[^\t]*\t//g;
    $holdingInfo =~ s/\t$//g;

    my $sth = $dbh->prepare(<<_STH_);
insert into opl_zResult
(uid, resultId, content, holding)
values (?, ?, ?, ?)
_STH_
#(uid, resultId, content, holding, syntax, encoding)
#values (?, ?, ?, ?, ?, ?)
#    $sth->execute($uid, $resultId, $content, $holdingInfo, $syntax, $encoding) || return;
    $sth->execute($uid, $resultId, $content, $holdingInfo) || return;
    $sth->finish;

    return $record;
}


sub srch_getMarcImport {
    my ($dbh, $uid, $resultId) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_zResult
where   uid = ? &&
        resultId = ?
_STH_
    $sth->execute($uid, $resultId) || return;

    my $record = $sth->fetchrow_hashref();
    $sth->finish;
    
    my $xml;# = $record->{'content'};
    my $marc;
#    if ($record->{'syntax'} eq 'USMARC') {
#        $marc = MARC::File::USMARC->decode($record->{'content'});
#        if ($record->{'encoding'} eq 'marc8') {
#            $marc = utf8_fromMarc8($marc);
#        }
#    }
#    else {
        $record->{'content'} =~ s/[\r\n\s]*<subfield code="[\-\+]">.+$//mg;
        $marc = Opals::Marc::Record::newFromXml($record->{'content'});
        $marc = Opals::Marc::Record::convert_OCoLC_number($marc);
#    }

    my $fRequired;
    # Delete all existing fields 001 and 005
    $marc = Opals::Marc::Record::delete_fields($marc, '001');
    $marc = Opals::Marc::Record::delete_fields($marc, '005');
=item
    while ($fRequired = $marc->field('001')) {
        $marc->delete_field($fRequired) if ($fRequired);
    }
    while ($fRequired = $marc->field('005')) {
        $marc->delete_field($fRequired) if ($fRequired);
    }
=cut
    # /Delete all existing fields 001 and 005

    my @fields = $marc->fields();
    $fRequired = MARC::Field->new('001', 0);
    $marc->insert_fields_before($fields[0], $fRequired);

    # Locate field after which field 005 should be inserted. It should be
    # the last field 003. If there is not any field 003, then it should be
    # field 001.
    my @f003 = $marc->field('003');
    my $fPrevious = ($#f003 > -1) ? $f003[$#f003] : $marc->field('001');
    
    my $f005 = date_f005();
    $fRequired = MARC::Field->new('005', $f005);
    $marc->insert_fields_after($fPrevious, $fRequired);
    
    $xml = MARC::File::XML::record($marc);

    # Add default holding from the 1st record template
    my $f852 = srch_F852Default_marcxml($dbh, 1);
    my ($code, $data);
    foreach my $sf (split(/\t/, $record->{'holding'})) {
        if ($sf =~ m/([\w\d])=(.*)/) {
            $code = $1;
            $data = $2;
            $f852 =~ s/<subfield code="$code">.*<\/subfield>/<subfield code="$code">$data<\/subfield>/;
        }
    }
    $xml =~ s/[\s]*<\/record>/$f852\n<\/record>/ if $f852 ne '';
    # /Add default holding from the 1st record template

#    if ($record->{'encoding'} eq 'marc8') {
#        $xml = encode_utf8($xml);
#    }

    $xml = util_filterMarcXml($xml);

    return $xml;
}


sub srch_systemList {
    my ($dbh) = @_;
#    my ($dbh, $list) = @_;
    
    my @system;
    
    my $sth_system = $dbh->prepare(<<_STH_);
select  *
from    opl_libSystem
where   sid > 2
_STH_
    
    my $sth_library = $dbh->prepare(<<_STH_);
select  l.*, s.*
from    opl_library as l, opl_libSystem as s
where   l.sysCode = ? &&
        l.sysCode = s.sCode
_STH_

    $sth_system->execute();
    while (my $sys = $sth_system->fetchrow_hashref) {
        $sys->{'sName'} =~ s/'/\\'/g;
        $sth_library->execute($sys->{'sCode'});
        my @library;
        while (my $lib = $sth_library->fetchrow_hashref) {
            $lib->{'lName'} =~ s/'/\\'/g;
            push @library, $lib;
        }
        $sys->{'libList'} = \@library;
        push @system, $sys;
    }
    $sth_system->finish;
    $sth_library->finish;

    return \@system;
}


sub srch_infoSystem {
    my ($dbh, $input) = @_;

    my $infoSys;
    
    my $sth;
    if ($input->{'sCode'}) {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_libSystem
where   sCode = ?
_STH_
        $sth->execute($input->{'sCode'});
        $infoSys = $sth->fetchrow_hashref;
        $sth->finish;
    }
    elsif ($input->{'sid'}) {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_libSystem
where   sid = ?
_STH_
        $sth->execute($input->{'sid'});
        $infoSys = $sth->fetchrow_hashref;
        $sth->finish;
    }

    return $infoSys;
}


sub srch_infoLibrary {
    my ($dbh, $input) = @_;

    my $infoLib;
    
    my $sth;
    if ($input->{'sCode'} && $input->{'lCode'}) {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_libSystem as sys, opl_library as lib
where   sys.sCode = ? &&
        lib.lCode = ? &&
        lib.sysCode = sys.sCode
_STH_
        $sth->execute($input->{'sCode'}, $input->{'lCode'});
        $infoLib = $sth->fetchrow_hashref;
        $sth->finish;
    }
    elsif ($input->{'sid'} && $input->{'lid'}) {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_libSystem as sys, opl_library as lib
where   sys.sid = ? &&
        lib.lid = ? &&
        lib.sysCode = sys.sCode
_STH_
        $sth->execute($input->{'sid'}, $input->{'lid'});
        $infoLib = $sth->fetchrow_hashref;
        $sth->finish;
    }

    return $infoLib;
}


sub srch_maxRid {
    my ($dbh) = @_;
    my ($maxRid) = $dbh->selectrow_array(<<_STH_);
select  max(rid)
from    opl_marcRecord
_STH_

#    my $zRoot = Opals::Context->config('zRoot');
#    my $zdb   = Opals::Context->config('zDatabase');
#    return unless (-d $zRoot && -d "$zRoot/$zdb");
#    
#    my $zDbDir = "$zRoot/$zdb";
#    my $maxDir = 0;
#    my ($dir, $rid);
#    
#    open DIR, "ls $zDbDir | ";
#    while (<DIR>) {
#        chomp $_;
#        $dir = $_;
#        $maxDir = $dir if ($maxDir < $dir);
#    }
#    close DIR;
#
#    open RID, "ls $zDbDir/$maxDir | ";
#    while (<RID>) {
#        m/([\d]+)\.xml/;
#        $rid = $1;
#        $maxRid = $rid if ($maxRid < $rid);
#    }
#    close RID;

    ($maxRid) || ($maxRid = 0);

    return $maxRid;
}


sub srch_validateRid {
    my ($dbh, $rid) = @_;

    my $ridValid;
    if ($rid && $rid =~ m/^[\d]+$/ && $rid > 0) {
        ($ridValid) = $dbh->selectrow_array(<<_STH_);
select  rid
from    opl_marcRecord
where   rid = $rid
_STH_
    }

    if (!$ridValid) {
        $ridValid = srch_maxRid($dbh) + 1;
    }

    return $ridValid;
}


sub srch_F852Default_marc21 {
    my ($dbh, $f852p) = @_;

    my ($xml) = $dbh->selectrow_array(<<_STH_);
select  content
from    opl_template
where   tid = 1
_STH_
    my $marc = Opals::Marc::Record::newFromXml($xml);

    my $f852 = $marc->field('852');
    return unless $f852;
    
    my $f852Default;
    foreach my $sf ($f852->subfields()) {
        if ($f852p || $sf->[0] ne 'p') {
            if ($f852Default) {
                $f852Default->add_subfields($sf->[0] => $sf->[1]);
            }
            else {
                $f852Default = MARC::Field->new(
                    '852', $f852->indicator(1), $f852->indicator(2),
                    $sf->[0] => $sf->[1]
                );
            }
        }
    }
    
    return $f852Default;
}


sub srch_F852Default_marcxml {
    my ($dbh, $f852p) = @_;

    my ($xml) = $dbh->selectrow_array(<<_STH_);
select  content
from    opl_template
order by rank
limit 1
_STH_
    
    $xml =~ s/
//g;
    
    my $f852Default;
    if ($xml =~ m/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        $f852Default = $1;
        if (!$f852p) {
            $f852Default =~ s/[\s]*<subfield code="p">.*<\/subfield>//g;
        }
    }

    return $f852Default;
}


sub srch_briefRecordById {
    my ($dbh, $rid) = @_;

    my $record = $dbh->selectrow_hashref(<<_STH_);
select  *
from    opl_marcRecord
where   rid = $rid
_STH_

    return $record;
}

#===========================================
# Thu, Jun 12, 2008 @ 09:04:28 EDT
# for Accelerated reader/Reading Counts/Lexile search

sub srch_parseSearchTerm_mysql {
    my ($term) = @_;
   $term =~ s/[\s]+/ /g;
   $term =~ s/(^| )((AND|OR|NOT) )*/$1$2/g;


    my @andTerm = ();
    my @orTerm  = ();
    my @notTerm = ();
    my $sTerm;
    # Process exact phrase first
    while ($term =~ s/((^| )(AND|OR|NOT) )*("[^\"]+")(.*)/$5/g) {
        push @andTerm, $4 if ($3 eq 'AND' || $3 eq '');
        push @orTerm,  $4 if ($3 eq 'OR');
        push @notTerm, $4 if ($3 eq 'NOT');
    }
    while ($term =~ s/((^| )(AND|OR|NOT) )*(\S+)(.*)/$5/g) {
        $sTerm =$4;
        #$sTerm =~ s/\W/\\*/g;
        if($sTerm ne ''){ 
            push @andTerm, $sTerm if ($3 eq 'AND' || $3 eq '');
            push @orTerm,  $sTerm if ($3 eq 'OR');
            push @notTerm, $sTerm if ($3 eq 'NOT');
            }
    }
    my $sqlTerm = '';
    foreach $sTerm (@andTerm) {
       $sqlTerm .= " +$sTerm";     
    }
    foreach $sTerm (@notTerm) {
       $sqlTerm .= " -$sTerm";     
    }
    foreach $sTerm (@orTerm) {
        $sqlTerm .= " $sTerm";     
    }
    $sqlTerm ="('$sqlTerm' IN BOOLEAN MODE)"; 
    return $sqlTerm;
}

sub getMarcXML {
    my ($rid) = @_;
    my $zRoot   = Opals::Context->config('zRoot');
    my $zPort   = Opals::Context->config('zPort');
    my $zDatabase = Opals::Context->config('zDatabase');
    my $dir     = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);
    my $record = '';
    if (! -f "$dir/$rid.xml") {
        print "ERROR: $dir/$rid.xml: not found.\n";
        return;
    }

    open MARCXML, "<$dir/$rid.xml";
    while (<MARCXML>) {
        $record .= $_;
    }
    close MARCXML;
    return $record;
}
#---------------------------------------------------------------------------------------

sub srch_searchRecordSQL {
    my ($dbh,$resultType, $sql,$sqlCount,  $offset, $pSize) = @_;
  
    ($offset >= 0) || ($offset = 0);
    ($pSize  > 0) || ($pSize  = 1);

    my ($resultSize) = $dbh->selectrow_array($sqlCount);
   
    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 @result = ();
    if ($resultSize > 0) {
        my $query="$sql  LIMIT $offset ,$availRange " ;
        my $sth = $dbh->prepare($query);
        $sth->execute();
        my $odd_row = 0;
        my $i=$offset ;

        while (my ($rid) = $sth->fetchrow_array()) {
                my $xml = getMarcXML($rid);
                if($xml eq ""){
                    next;
                }
                $xml =~ s/[\s]*<subfield code="-">.*<\/subfield>//g;
                $xml =~ s/[\s]*<idzebra.+>([\s]*<.+>.+<\/.+>)+[\s]*<\/idzebra>//g;
                
               
                $xml = util_ieFix($xml);

                if ($resultType =~ m/^f$/i) {
                    push @result, $xml;
                }
                elsif ($resultType =~ m/^f852$/i) {
                    my $f852 = '';
                    while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
                        $f852 .= $1;
                    }
                    push @result, $f852;
                }
                elsif ($resultType =~ m/^subjectHeading$/i) {
                    foreach my $res (record_subjectHeading($xml)) {
                        push @result, $res;
                    }
                }
                else {
                    my $recordBrief = srch_recordBrief($xml);
                    $recordBrief->{'resultOrder'} = $i++;
                    push @result, $recordBrief;
                }
            
        }

        if (@result > 1 && $resultType =~ m/^b$/i) {
            foreach my $rec (@result) {
                $rec->{'odd'} = ($odd_row ^= 1);
            }
        }

    $sth->finish;
        
    } 
    return ($resultSize, \@result);
}
#=======================================================
# Fri, Oct 24, 2008 @ 09:16:19 EDT

sub srch_buildPQF{
    my ($input,$cgi,$zid) =@_;
    my $pqf="";
    my $iCount = 0;
    my ($sfOrder, $kw, $boolop,$sTerm);
    foreach my $cgiInput (keys %$input) {
        $iCount++ if ($cgiInput =~ m/^sf/);
    }
    for (my $i = 0; $i < $iCount; $i++) {
        $sfOrder= 'sf' . $i;
        $kw     = 'kw' . $i;
        $boolop = 'boolop' . $i;
            
     if ($input->{$kw}) {
            $sTerm='';
            $sTerm = srch_parseSearchTerm($input->{$kw});
            if ($sTerm) {
                if ($pqf) {
                    $pqf = "\@$input->{$boolop} $pqf \@attr 1=$input->{$sfOrder} $sTerm";
                }
                else {
                    $pqf = "\@attr 1=$input->{$sfOrder} $sTerm";
                }                
            }
        }
    }

    if ($pqf) {
        # Scoping
#        my $scopeSys = $cgi->cookie('scopeSysCode');
#        my $scopeLib = $cgi->cookie('scopeLibCode');
#        if ($scopeSys || $scopeLib) {
#            if ($scopeSys && $scopeLib) {
#                $pqf = "\@and $pqf \@and \@attr 1=5003 $scopeSys \@attr 1=5004 $scopeLib";
#            }
#            elsif ($scopeSys) {
#                $pqf = "\@and $pqf \@attr 1=5003 $scopeSys";
#            }
#            else {
#                $pqf = "\@and $pqf \@attr 1=5004 $scopeLib";
#            }
#        }
        my $searchScope = $input->{'searchScope'};
        if ($searchScope) {
            $pqf = "\@and $pqf \@attr 1=5005 $searchScope";
        }
        
        # Sort
        my $sortAttr = $input->{'sortAttr'};
        my $sortOrder = $input->{'sortOrder'};
        if(!$sortAttr || $sortAttr eq ''){
            $sortAttr=31;
            $sortOrder =2;
        }
        $sortAttr=undef if ($zid != 0);

        if ($sortAttr && ($sortAttr == 4    ||  # Title
                          $sortAttr == 31   ||  # Date-publication
                          $sortAttr == 1003 ||  # Author-name
                          $sortAttr == 5001 ||  # Call-number-local
                          $sortAttr == 8008     # Location
                          )) {
            ($sortOrder && $sortOrder == 2) || ($sortOrder = 1);
            $pqf = "\@or $pqf \@attr 7=$sortOrder \@attr 1=$sortAttr 0";
        }
    }
    return $pqf;
}

sub srch_buildPQF_owl{
    my($input,$cgi,$zid,$owlLocation)=@_;
    my $pqf=srch_buildPQF($input,$cgi,$zid);
    if($owlLocation && $owlLocation ne ''){
        $pqf = "\@and $pqf \@attr 1=8008 \"$owlLocation\"";
    }
    return $pqf;

}
#=======================================================
# Mon, Oct 27, 2008 @ 10:34:05 EDT
sub srch_searchAuthorSQLs{
    my ($dbh, $term, $offset, $pSize) = @_;
    my ($sql,$sqlCount,$sqlCond) = ("","","");
    ($offset >= 0) || ($offset = 0);
    ($pSize  > 0) || ($pSize  = 1);
    $term =~ s/[\s]+/ /g;
    $term =~ s/(^ | $)//g;
    $term =~ s/\'/\\\'/g;
    if ($term) {
        $term= " $term";
        $term =~ s/ / \+/g;
        $sqlCond .= "match(author) against('$term' in boolean mode) && deleted = 0";
    }

    $sqlCount  = "SELECT count(distinct author)  FROM opl_marcRecord WHERE $sqlCond";
    $sql       = "SELECT author as strVal,count(rid) as recCount  FROM opl_marcRecord WHERE $sqlCond GROUP BY author ORDER BY author";

    my ($resultSize) = $dbh->selectrow_array($sqlCount);
    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 @result = ();
    if ($resultSize > 0) {
        my $query="$sql  LIMIT $offset ,$availRange " ;
        my $sth = $dbh->prepare($query);
        $sth->execute();
        while (my $record = $sth->fetchrow_hashref()) {
            push @result, $record;
        } 
    }

    return ($resultSize, \@result);
    
}


#=======================================================
# Mon, Oct 27, 2008 @ 10:34:05 EDT
# Search SUBJECT start with 'keyword' 
sub srch_searchSubjectStartWithSQLs{
    my ($dbh, $term, $offset, $pSize) = @_;
    my ($sql,$sqlCount) = ("","");
    ($offset >= 0) || ($offset = 0);
    ($pSize  > 0) || ($pSize  = 1);
    
    $sqlCount  = "SELECT count(*)  FROM opl_subjects WHERE subject like '" . $term ."%' && recCount > 0";
    $sql       = "SELECT id,subject as strVal ,recCount FROM opl_subjects WHERE subject like '" . $term ."%' && recCount > 0 ORDER BY subject";
    
    my ($resultSize) = $dbh->selectrow_array($sqlCount);
    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 @result = ();
    if ($resultSize > 0) {
        my $query="$sql  LIMIT $offset ,$availRange " ;
        my $sth = $dbh->prepare($query);
        $sth->execute();
        while (my $rec = $sth->fetchrow_hashref()) {
            push @result, $rec;
        } 
    }

    return ($resultSize, \@result);
}
#=======================================================
# Mon, Oct 27, 2008 @ 10:34:05 EDT
sub srch_searchSubjectContainWithSQLs{
    my ($dbh,$term, $offset, $pSize) = @_;
    my ($sql,$sqlCount) = ("","");
    ($offset >= 0) || ($offset = 0);
    ($pSize  > 0) || ($pSize  = 1);
    my ($sqlFr,$sqlCond) =("","");
    $sqlFr = " opl_subjects ";
    
    $term =~ s/[\s]+/ /g;
    $term =~ s/(^ | $)//g;
    $term =~ s/\'/\\\'/g;
    if ($term) {
        $term= " $term";
        $term =~ s/ / \+/g;
        $sqlCond .= " match subject against('$term' in boolean mode) && recCount > 0 ";
    }
    
    $sqlCount = "SELECT count(*) FROM opl_subjects WHERE $sqlCond" ;
    $sql      = "SELECT id,subject as strVal,recCount FROM opl_subjects WHERE $sqlCond ORDER BY subject ";

    my ($resultSize) = $dbh->selectrow_array($sqlCount);
    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 @result = ();
    if ($resultSize > 0) {
        my $query="$sql  LIMIT $offset ,$availRange " ;
        my $sth = $dbh->prepare($query);
        $sth->execute();
        while (my $rec = $sth->fetchrow_hashref()) {
            push @result, $rec;
        } 
    }

    return ($resultSize, \@result);
   
}

#=======================================================
# Fri, Oct 24, 2008 @ 09:16:19 EDT
# Search TITLE start with 'keyword' 
sub srch_buildSchTitleStartWithSQLs{
    my ($input) =@_;
    my ($sql,$sqlCount,$sqlFrom,$sqlCond,$sqlOrder)=("","","","","");
    if($input->{'kw0'} ne ''){
        my $kw = $input->{'kw0'};
        $kw =~ s/^\s+|\s+$//g;
        $kw =~ s/\*$//g;
        $kw =~ s/\'/\\\'/g;
        my $rid = $input->{'rid'};

        $sqlFrom = " opl_marcRecord  r inner join opl_item i on i.rid=r.rid ";
        $sqlCond = "deleted =0 && i.barcode not regexp '^\_\_\_' && (titleSort like  '" .$kw . "%' OR title like  '" .$kw . "%')" ;
        
        # Sort
        my $sortAttr = $input->{'sortAttr'};
        my $sortOrder = $input->{'sortOrder'};
        ($sortAttr) || ($sortAttr =4);

        if( $sortAttr == 31 ) {  # Date-publication
            $sqlOrder =" r.pubDate ";
        }
        elsif($sortAttr == 1003 ){ # Author-name
            $sqlOrder =" r.author ";
        }
        elsif($sortAttr == 5001){  # Call-number-local
            $sqlOrder =" i.callNumber ";
            #$sqlFrom .=" inner join opl_item i on i.rid=r.rid ";

        }
        elsif($sortAttr == 8008 ){    # Location
            $sqlOrder =" i.location ";
            #$sqlFrom .=" inner join opl_item i on i.rid=r.rid ";
        }
        else{ #default sort by title field
            $sqlOrder =" r.titleSort ";
        }
        if($sortOrder && $sortOrder == 2){
            $sqlOrder .= " DESC ";
        }

        if($sqlFrom ne ""){
            $sql      ="SELECT DISTINCT(r.rid) FROM $sqlFrom WHERE $sqlCond ORDER BY $sqlOrder ";
            $sqlCount ="SELECT COUNT(DISTINCT r.rid) FROM $sqlFrom WHERE $sqlCond ";
        }
    }
    return ($sql,$sqlCount);
}

#---------------------------------------------------------------------------------------

sub srch_buildNewItemSQLs{
    my ($input) =@_;
    my ($sortAttr,$sortOrder, $periodDays);
    $sortAttr = $input->{'sortAttr'};
    $sortOrder = $input->{'sortOrder'};
 
    $periodDays = 30;
    if (scalar ($input->{'range'}>0)){
      $periodDays =$input->{'range'};   
    }

    my $sqlCount ="select  count(distinct i.rid) as count
        from    (opl_item as i inner join opl_itemstatus as s on i.barcode=s.barcode && s.status=6)
            inner join opl_marcRecord as r  on i.rid = r.rid 
        where   substring(i.barcode, 1, 3) <> '___'&&
            s.ondate >= now()   
            && DATE_ADD(i.dateImport,INTERVAL $periodDays DAY)>=now()   
        ";
   
    my $sql ="select  i.rid
        from    (opl_item as i inner join opl_itemstatus as s on i.barcode=s.barcode && s.status=6)
            inner join opl_marcRecord as r  on i.rid = r.rid 
        where   substring(i.barcode, 1, 3) <> '___'  &&
            s.ondate >= now()   
            && DATE_ADD(i.dateImport,INTERVAL $periodDays DAY)>=now() 
        group by i.rid ";

    if($sortAttr){
        my $sortDir=$sortOrder==2?" DESC ":" ASC ";
        if ($sortAttr == 4) {    # Title
            $sql .=" ORDER BY r.titleSort $sortDir"; 
        }
        elsif($sortAttr == 31){  # Date-publication
            $sql .=" ORDER BY r.pubDateSort $sortDir"; 
        }
        elsif($sortAttr == 1003){  # Author-name
            $sql .=" ORDER BY r.author $sortDir"; 
        }
        elsif($sortAttr == 5001) { # Call-number-local
            $sql .=" ORDER BY i.callNumber $sortDir"; 
        }
    }
    else{
        $sql .=" ORDER BY i.dateImport DESC, r.titleSort ASC ";
    }
    
    return ($sql,$sqlCount);



}


#---------------------------------------------------------------------------------------
sub srch_buildArlSQL_bk{
    my ($input) =@_;
    my ($sql,$sqlCount,$sqlArlFrom,$sqlArlCond,$sqlMatchTermCond, $sqlCond,$sqlOrder)=("","","","","","","",);

  
    if($input->{'kw0'} ne ''){
        $sqlMatchTermCond = " match attr_4 against  " . srch_parseSearchTerm_mysql($input->{'kw0'}) ;
    }
    if($input->{'kw1'} ne ''){
        $sqlMatchTermCond .= " && " if ($sqlMatchTermCond ne "");
        my $sqlMatchTermCond .= "match attr_1003 against " . srch_parseSearchTerm_mysql($input->{'kw1'});
     }
   
    # Accelerated Reader
    if($input->{'program'} eq 'ar'){
        ($sqlArlFrom,$sqlArlCond)=buildQueryAR($input);
          
    }
    # Reading Counts
    elsif($input->{'program'} eq 'rc'){
        ($sqlArlFrom,$sqlArlCond)=buildQueryRC($input);
    }
    #lexile
    else{ #lexile
        ($sqlArlFrom,$sqlArlCond)=builSQLLexile($input);
     }
     if($sqlArlFrom ne "" && $sqlArlCond ne ""){            
            $sqlArlCond = " $sqlMatchTermCond  && $sqlArlCond " if($sqlMatchTermCond ne "");
            $sqlArlFrom = " opl_recordindex r $sqlArlFrom ";
            ###$sqlCond =" from opl_recordindex r $sqlArlFrom where $sqlMatchTermCond $sqlArlCond";
     }
    
    if( $sqlArlFrom ne ""){ 
     $sql      ="SELECT DISTINCT(r.rid) FROM $sqlArlFrom WHERE $sqlArlCond   ";
      $sql    .=" ORDER BY $sqlOrder " if($sqlOrder ne "");
     $sqlCount ="SELECT COUNT(DISTINCT r.rid) FROM $sqlArlFrom WHERE $sqlArlCond ";
    }
     
     return ($sql,$sqlCount);
    
}#---------------------------------------------------------------------------------------
sub srch_buildArlSQL{
    my ($input) =@_;
    my ($sql,$sqlCount,$sqlCond, $sqlOrder)=("","","","");
    return("","") if($input->{'program'} eq "");
    
    $sql        ="SELECT DISTINCT(r.rid) FROM opl_marcRecord m 
                    inner join opl_item i on i.rid=m.rid
                    inner join opl_recordindex r on r.rid=m.rid
                    inner join opl_arl a on a.rid=r.rid ";

    $sqlCount   ="SELECT COUNT(DISTINCT r.rid) FROM  opl_recordindex r inner join opl_arl a on a.rid=r.rid";
    
    $sqlCond =" program ='" . $input->{'program'} ."'";
    
    if($input->{'kw0'} ne ''){
        $sqlCond .= " && match attr_4,attr_5,attr_6,attr_21,attr_31,attr_59,attr_63,attr_1003 against  " . srch_parseSearchTerm_mysql($input->{'kw0'}) ;
    }
  
    if($input->{'kw1'} ne ''){
        $sqlCond .= " && match attr_4 against  " . srch_parseSearchTerm_mysql($input->{'kw1'})  ;
    }
    if($input->{'kw2'} ne ''){
        $sqlCond .= " && match attr_1003 against " . srch_parseSearchTerm_mysql($input->{'kw2'}) ;
    }
   

    if($input->{'readingLevelFrom'} ne ''){
        $sqlCond .= " && CAST(readingLevel As DECIMAL(5,2)) >=CAST('" . $input->{'readingLevelFrom'} . "' As DECIMAL(5,2))";
    }
    if($input->{'readingLevelTo'} ne ''){
        $sqlCond .= " && CAST(readingLevel As DECIMAL(5,2)) <=CAST('" . $input->{'readingLevelTo'} . "' As DECIMAL(5,2))";
    }

    if($input->{'pointvalueFrom'} ne ''){
        $sqlCond .= " && CAST(pointValue As DECIMAL(5,2)) >=CAST('" . $input->{'pointvalueFrom'} . "' As DECIMAL(5,2))";
    }
    if($input->{'pointvalueTo'} ne ''){
        $sqlCond .= " && CAST(pointValue As DECIMAL(5,2)) <=CAST('" . $input->{'pointvalueTo'} . "' As DECIMAL(5,2))";
    }
    if($input->{'interestLevel'} ne ''){
        $sqlCond .= " && interestLevel='" . $input->{'interestLevel'} . "'";
    }
  
    my $sortAttr = $input->{'sortAttr'};     
    my $sortOrder = $input->{'sortOrder'};


    if($sortAttr){
        my $sortDir=$sortOrder==2?" DESC ":" ASC ";
        if ($sortAttr == 4) {    # Title
            $sqlOrder =" ORDER BY m.titleSort $sortDir"; 
        }
        elsif($sortAttr == 31){  # Date-publication
            $sqlOrder =" ORDER BY m.pubDateSort $sortDir"; 
        }
        elsif($sortAttr == 1003){  # Author-name
            $sqlOrder =" ORDER BY m.author $sortDir"; 
        }
        elsif($sortAttr == 5001) { # Call-number-local
            $sqlOrder =" ORDER BY i.callNumber $sortDir"; 
        }
    }
    else{
        $sqlOrder =" ORDER BY m.pubDateSort DESC, m.titleSort ASC ";
    }
     
    $sql      .=" WHERE $sqlCond  $sqlOrder " ;
    $sqlCount .=" WHERE $sqlCond  ";
   
     
   return ($sql,$sqlCount);
    
}
#---------------------------------------------------------------------------------------
sub buildQueryAR_bk
{
     my ($input) =@_;
     my ($sqlFrom,$sqlCond)=("","");
     $sqlFrom .= " inner join opl_arl t0 on r.rid=t0.rid ";
     $sqlCond =$sqlCond . " && " if($sqlCond ne "");
     $sqlCond .= " t0.tag='526' &&  t0.ind1='0' && t0.code='a' && (t0.data1 like '%accelerated reader%'  or t0.data1 like '%AR%' )";



     my $readingLevelFrom=$input->{'arReadingLevelFrom'};
     my $readingLevelTo  =$input->{'arReadingLevelTo'};

     if($readingLevelFrom ne "" && $readingLevelTo eq ""){
         $readingLevelTo=$readingLevelFrom;
     }
     if($readingLevelFrom eq "" && $readingLevelTo ne ""){
         $readingLevelFrom=$readingLevelTo;
     }

     if($readingLevelFrom ne "" && $readingLevelTo ne ""){
            $sqlFrom .= " inner join opl_arl t1 on r.rid=t1.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  " t1.tag='526' &&  t1.ind1='0' && t1.code='c' 
                         && cast(t1.data1 as decimal)  between cast('$readingLevelFrom' as decimal)  AND  cast('$readingLevelTo' as decimal)
                         && cast(t1.data2 as decimal)  between cast('$readingLevelFrom' as decimal)  AND  cast('$readingLevelTo' as decimal) ";                                                               
     } 
     my $poinValueFrom   =$input->{'arPointvalueFrom'};
     my $poinValueTo     =$input->{'arPointvalueTo'};
    
     if($poinValueFrom ne "" && $poinValueTo eq ""){
         $poinValueTo=$poinValueFrom;
     }
     if($poinValueFrom eq "" && $poinValueTo ne ""){
         $poinValueFrom=$poinValueTo;
     }


     if($poinValueFrom ne ""){
            $sqlFrom .= " inner join opl_arl t2 on r.rid=t2.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  " t2.tag='526' &&  t2.ind1='0' && t2.code='d' 
                           && cast(t2.data1 as decimal) between cast('$poinValueFrom'  as decimal) AND cast('$poinValueFrom'  as decimal) ";
     }  
     
 
        if($input->{'arInterestLevel'} eq 'lg'){
            $sqlFrom .= " inner join opl_arl t3 on r.rid=t3.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  " t3.tag='526' &&  t3.ind1='0' &&  t3.code='b' 
                           && (match (t3.data1,t3.data2) against('Lower grade' in boolean mode)
                           or  match (t3.data1,t3.data2) against('lg'))";


        }
        elsif($input->{'arInterestLevel'} eq 'mg'){
           $sqlFrom .= " inner join opl_arl t3 on r.rid=t3.rid ";
           $sqlCond =$sqlCond . " && " if($sqlCond ne "");
           $sqlCond .=  " t3.tag='526' &&  t3.ind1='0' &&  t3.code='b' 
                           && (match (t3.data1,t3.data2) against( '+middle +grade' in boolean mode)
                           or  match (t3.data1,t3.data2) against( 'mg'))";

        }
        elsif($input->{'arInterestLevel'} eq 'hg'){
           $sqlFrom .= " inner join opl_arl t3 on r.rid=t3.rid ";
           $sqlCond =$sqlCond . " && " if($sqlCond ne "");
           $sqlCond .=  "  t3.tag='526' &&  t3.ind1='0' &&  t3.code='b' 
                           && (match (t3.data11,t3.data2) against( '+upper +grade' in boolean mode)
                           or  match (t3.data11,t3.data2) against( 'ug'))";

        }
        return ($sqlFrom ,$sqlCond);

}
sub buildQueryRC
{
     my ($input) =@_;
     my ($sqlFrom,$sqlCond)=("","");
     $sqlFrom .= " inner join opl_arl t0 on r.rid=t0.rid ";
     $sqlCond =$sqlCond . " && " if($sqlCond ne "");
     $sqlCond .= " t0.tag='526' &&  t0.ind1='0' && t0.code='a' && (t0.data1 like '%reading counts%'  or t0.data1 like '%RC%' )";

     my $readingLevelFrom=$input->{'rcReadingLevelFrom'};
     my $readingLevelTo  =$input->{'rcReadingLevelTo'};
    
     if($readingLevelFrom ne "" && $readingLevelTo eq ""){
         $readingLevelTo=$readingLevelFrom;
     }
     if($readingLevelFrom eq "" && $readingLevelTo ne ""){
         $readingLevelFrom=$readingLevelTo;
     }

     if($readingLevelFrom ne "" && $readingLevelTo ne ""){
            $sqlFrom .= " inner join opl_arl t4 on r.rid=t4.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .= " t4.tag='526' &&  t4.ind1='0' && t4.code='c' 
                         && cast(t4.data1 as decimal)  between cast('$readingLevelFrom' as decimal)  AND  cast('$readingLevelTo' as decimal)
                         && cast(t4.data2 as decimal)  between cast('$readingLevelFrom' as decimal)  AND  cast('$readingLevelTo' as decimal) ";                                                               
     } 
     my $poinValueFrom   =$input->{'rcPointvalueFrom'};
     my $poinValueTo     =$input->{'rcPointvalueTo'};
    
     if($poinValueFrom ne "" && $poinValueTo eq ""){
         $poinValueTo=$poinValueFrom;
     }
     if($poinValueFrom eq "" && $poinValueTo ne ""){
         $poinValueFrom=$poinValueTo;
     }


     if($poinValueFrom ne ""){
            $sqlFrom .= " inner join opl_arl t5 on r.rid=t5.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t5.tag='526' &&  t5.ind1='0' && t5.code='d' 
                           && cast(t5.data1 as decimal) between cast('$poinValueFrom'  as decimal) AND cast('$poinValueTo'  as decimal) ";
     }  
     
       
     if($input->{'rcInterestLevel'} eq 'k-3'){
            $sqlFrom .= " inner join opl_arl t6 on r.rid=t6.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t6.tag='526' &&  t6.ind1='0' &&  t6.code='b' 
                           && cast(t6.data1 as decimal) <=3
                           && cast(t6.data2 as decimal) <= 3 ";


        }
        elsif($input->{'rcInterestLevel'} eq '4-6'){
         $sqlFrom .= " inner join opl_arl t6 on r.rid=t6.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t6.tag='526' &&  t6.ind1='0' &&  t6.code='b' 
                           && cast(t6.data1 as decimal) between 4 AND 6
                           && cast(t6.data2 as decimal) between 4 AND 6";

        }
        elsif($input->{'rcInterestLevel'} eq 'ms'){
         $sqlFrom .= " inner join opl_arl t6 on r.rid=t6.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t6.tag='526' &&  t6.ind1='0' &&  t6.code='b' 
                           && (match (t6.data1,t6.data2) against('+middle +school' in boolean mode) 
                           or  match (t6.data1,t6.data2) against('mh')) ";

        }

        elsif($input->{'rcInterestLevel'} eq 'hs'){
           $sqlFrom .= " inner join opl_arl t6 on r.rid=t6.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond  ne "");
           $sqlCond .=  "  t6.tag='526' &&  t6.ind1='0' &&  t6.code='b' 
                           && (match (t6.data1,t6.data2) against('+high +school' in boolean mode)
                           or match (t6.data1,t6.data2) against('mh'))";

        }
        return ($sqlFrom,$sqlCond);

}
#---------------------------------------------------------------------------------------
sub builSQLLexile{
     my ($input) =@_;
     my ($sqlFrom,$sqlCond)=("","");
     my $readingLevelFrom=$input->{'lexileReadingLevelFrom'};
     my $readingLevelTo  =$input->{'lexileReadingLevelTo'};

    
     if($readingLevelFrom ne "" && $readingLevelTo eq ""){
         $readingLevelTo=$readingLevelFrom;
     }
     if($readingLevelFrom eq "" && $readingLevelTo ne ""){
         $readingLevelFrom=$readingLevelTo;
     }

     if($readingLevelFrom ne "" && $readingLevelTo ne ""){

            $sqlFrom .= " inner join opl_arl t7 on r.rid=t7.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  " t7.tag='521' &&  t7.ind1='0' && t7.code='a' 
                         && cast(t7.data1 as decimal)  between cast('$readingLevelFrom' as decimal)  AND  cast('$readingLevelTo' as decimal)
                         && cast(t7.data2 as decimal)  between cast('$readingLevelTo' as decimal)  AND  cast('$readingLevelTo' as decimal) ";                                                               
     } 





      my $interestLevel=$input->{'lexileInterestLevel'};

      if($interestLevel eq 'k-3'){
            $sqlFrom .= " inner join opl_arl t8 on r.rid=t8.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t8.tag='526' &&  t8.ind1='0' &&  t8.code='b' 
                           && cast(t8.data1 as decimal) <= 3
                           && cast(t8.data2 as decimal) <= 3 ";


        }
        elsif($interestLevel eq '4-5'){
         $sqlFrom .= " inner join opl_arl t8 on r.rid=t8.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t8.tag='526' &&  t8.ind1='0' &&  t8.code='b' 
                           && cast(t8.data1 as decimal) between 4 && 5
                           && cast(t8.data2 as decimal) between 4 && 5";

        }
        elsif($interestLevel eq '6-8'){
         $sqlFrom .= " inner join opl_arl t8 on r.rid=t8.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t8.tag='526' &&  t8.ind1='0' &&  t8.code='b' 
                           && cast(t8.data1 as decimal) between 6 && 8
                           && cast(t8.data2 as decimal) between 6 && 8";

        }
        elsif($interestLevel  eq 'ya'){
         $sqlFrom .= " inner join opl_arl t8 on r.rid=t8.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
            $sqlCond .=  "  t8.tag='526' &&  t8.ind1='0' &&  t8.code='b' 
                           && match (t8.data1,t8.data2) against('+Young +Adult' in boolean mode) ";

        }

        elsif($interestLevel  eq 'a'){
           $sqlFrom .= " inner join opl_arl t8 on r.rid=t8.rid ";
            $sqlCond =$sqlCond . " && " if($sqlCond ne "");
           $sqlCond .=  "  t8.tag='526' &&  t8.ind1='0' &&  t8.code='b' 
                           && match t8.data1 ='Adult'";

        }
        return ($sqlFrom , $sqlCond);



}


#####################################################
sub getAR_info{
    my ($xml) = @_;
    my @ARinfo=();
    while ($xml =~ s/[\s]*<datafield tag="526" ind1="([\d ])" ind2="([\d ])">(([\s]*<subfield code="[\w-]">.*<\/subfield>)*)[\s]*<\/datafield>//) {
        if($1 eq '0'){
            my $sfXml = $3;
            my($program,$readingLevel,$interestLevel,$gradeLevel_b,$gradeLevel_e,$pointValue,$quizNum)=('','','','','','','');
            while ($sfXml =~ s/[\s]*<subfield code="([\w|-])">(.*)<\/subfield>//) {
               my ($sfCode, $sfdata) = ($1, $2);
               if($sfCode eq 'a' && $sfdata =~ m/reading count|RC/gi){
                   $program='Reading Counts';
               }
               if($sfCode eq 'a' && $sfdata =~ m/accelerated reader|AR/gi){
                   $program='Accelerated Reader';
               }
               if($sfCode eq 'b'){
                   $interestLevel=$sfdata;
               }
               if($sfCode eq 'c'){
                   $readingLevel=$sfdata;
               }
               if($sfCode eq 'd'){
                   $pointValue=$sfdata;
               }
               if($sfCode eq 'z'){
                   $quizNum =$sfdata;
               }
            }
            if($program =~ m/Accelerated Reader|Reading Counts/gi){
               push @ARinfo, {program=>$program,
                              interestLevel=>$interestLevel,
                              readingLevel=>$readingLevel,
                              pointValue=>$pointValue,
                              quizNum=>$quizNum
                             };
            }

        }
    }
    return \@ARinfo;

}


#####################################################
sub getIdentifiers {
    my ($marc, $tag, $code) = @_;

    my ($sfData, $data, $isbn_obj, $isbn13);
    my @fields = $marc->field($tag);
    foreach my $f (@fields) {
        $data = $f->subfield($code);
        if (!$data) {
            next;
        }

        if ($tag eq '020' && $code eq 'a') {
            $data =~ s/[\- ]+//g;
            if ($data =~ m/(^|[\D])((\d{3})?\d{9}[0-9xX])([\D]|$)/) {
                $data = $2;
                #print "ISBN: $data\n";
            }
            else {
                next;
            }

            $isbn_obj = Business::ISBN->new($data) || next;
            $isbn_obj->fix_checksum;
            $isbn13 = $isbn_obj->as_isbn13;
            $data = $isbn13->as_string([]);
        }
        elsif ($tag eq '035' && $code eq 'a' && 
                $data !~ m/^\(OCoLC\)[1-9]/) {
            next;
        }

        $sfData->{$data} = 1;
    }
    if (scalar(keys %{$sfData}) == 0) {
        return;
    }

    return $sfData;
}


#####################################################
sub srch_getIdenticalRIDs {
    my ($dbh, $marc) = @_;

    my $ridGroups;
    my ($tag, $code);
    foreach my $id (@RECORD_IDENTIFIERS) {
        $tag = $id->{'tag'};
        $code = $id->{'code'};

        my $sf_data = getIdentifiers($marc, $tag, $code) || next;

        my $dataList = join(',', keys %{$sf_data});
        $dataList =~ s/,/','/g;
        $dataList =~ s/(^|$)/'/g;
        my $sql = <<_SQL_;
select  distinct rid
from    opl_recordIdentifier
where   tag = '$tag'
     && code = '$code'
     && data in ($dataList)
_SQL_
        $ridGroups->{$tag . $code} = 
                $dbh->selectall_arrayref($sql, {Slice => {}});
    }

    return $ridGroups;
}


1;