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_getNewRid
  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'}) {
        $options->option( user     => $zdb->{'username'} );
        if ($zdb->{'password'} ){
            $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 )if(length($raw)>9);

                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 $isbn = $f020->subfield('a');

        if ( $isbn ne '' ) {
           if($isbn =~ m/([\D^x^X]*)([\dxX][\dxX -]{8,}[\dxX])([\D^x^X]*)/){
            $isbn = $2;
            $isbn =~ s/[- ]//g;
           }
           push @isbnList, $isbn;
        }
    }
    $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->{'pubPlace'} = $marc->subfield( '264', 'a' ) if($record->{'pubPlace'} eq '');
    $record->{'pubName'}  = $marc->subfield( '264', 'b' ) if($record->{'pubName'} eq '');
    $record->{'pubDate'}  = $marc->subfield( '264', 'c' ) if($record->{'pubDate'} eq '');

    $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;
            if($f852 !~ s/<subfield code="$code">.*<\/subfield>/<subfield code="$code">$data<\/subfield>/){
                $f852 =~ s/(  <\/datafield>)/    <subfield code="$code">$data<\/subfield>\n$1\n/g;
            }
        }
    }
    $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_getNewRid {
    my($dbh)=@_;
    my ($sessionID) = $dbh->selectrow_array("select uuid()");
    $dbh->do("insert into opl_marcRecord(rid,sessionid) select  if(max(rid) is null,0,max(rid))+1,'$sessionID' from opl_marcRecord");
    my ($rid)=$dbh->selectrow_array("select rid from opl_marcRecord where sessionid='$sessionID'");
    return $rid;
    
}

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;
        $ridValid = srch_getNewRid($dbh) ;
    }

    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;
        }
        $f852Default =~ s/<subfield code="p">.*?<\/subfield>/<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;
            if($isbn_obj->is_valid()){
                $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 i.rid
from    opl_recordIdentifier i inner join opl_marcRecord m using(rid)
        left outer join opl_item  using(rid)
where   tag = '$tag'
     && code = '$code'
     && data in ($dataList)
     && m.deleted =0 && opl_item.rid is not null
_SQL_
        $ridGroups->{ $tag . $code } =
          $dbh->selectall_arrayref( $sql, { Slice => {} } );
    }

    return $ridGroups;
}

1;