package Opals::Search_mysql;

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_searchRecordARL
    srch_zTab 
    srch_infoLibrary
    srch_recordBrief

);
#    srch_searchField
#    srch_searchFieldIndex
# Version number
$VERSION   = 0.01;      


#use utf8;
use strict;
use POSIX qw(
    ceil
);
use Encode;
#use Constant;
use Net::Z3950;
use MARC::File::USMARC;
use MARC::File::XML;
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
);


# Constant
#Constant::Array my @zSyntax => (
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;
        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 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 getRecord_brief{
    my($dbh,$rid) =@_;
    my $record;
    my $query="select tag,tag_id,subfield,data from opl_fullMarcRecord where rid= ? 
               && (tag='100' ||tag='245' ||tag='260' ||tag='300' ||tag='852')
               order by tag,subfield,tag_id" ;
    
    my $sth = $dbh->prepare($query);
    $record->{'rid'}=$rid;
    my $f852;
    my $f245;
    my $f260;
    my ($curTagId_245,$curTagId_260,$curTagId_852)=('','','');
    $sth->execute($rid);
    while (my ($tag,$tag_id,$subfield,$data) = $sth->fetchrow_array()) {
        if($tag eq '100' && $subfield eq 'a'){
            $record->{'author'} = $data;
        }
        elsif($tag eq '245'){
            $f245->{$subfield} =  $data;
        }
        elsif($tag eq '260' ){
            if($curTagId_852 eq $tag_id || $curTagId_852 eq ''){
                $f260->{$subfield} = $data;
            }
        }

        elsif($tag eq '300' && $subfield eq 'a'){
            $record->{'numOfPages'} = $data;
        }
        elsif($tag eq '852'){
            if($curTagId_852 eq $tag_id || $curTagId_852 eq ''){
                $f852->{$subfield}=$data;
            }
            $curTagId_852=$tag_id;
        }
        
    }
    my $callNum = "$f852->{'k'}  $f852->{'h'} $f852->{'i'}  $f852->{'m'}" ;
    $callNum =~ s/^ +| +$//g;
    $record->{'callNum1st'} =$callNum;
    $record->{'title'} =$f245->{'a'} ;
    $record->{'title'} .=" : $f245->{'b'}" if($f245->{'b'} ne '' && $record->{'title'} ne '');
    $record->{'title'} .=" : $f245->{'p'}" if($f245->{'p'} ne '' && $record->{'title'} ne '');
    $record->{'pubPlace'} =$f260->{'a'};
    $record->{'pubName'}  =$f260->{'b'};
    $record->{'pubDate'}  =$f260->{'c'};
    $sth->finish;
    return $record;
    
}


sub srch_searchRecord {
    my ($dbh,$resultType, $sqlCon, $offset, $pSize) = @_;
    ($offset >= 1) || ($offset = 1);
    ($pSize  >= 1) || ($pSize  = 1);

    my $query="select count(distinct rid) from opl_fullMarcRecord where match data against $sqlCon " ;
    my ($resultSize) = $dbh->selectrow_array($query);
      
    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 = ();
    $offset -=1;
    if ($resultSize > 0) {
        $query="select distinct rid from opl_fullMarcRecord where match data against $sqlCon 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);
                $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);
}

sub srch_searchRecordARL {
    my ($dbh,$resultType, $sqlCond, $offset, $pSize) = @_;

    open debug, ">/tmp/arl";
    print debug "$sqlCond\n";
    close debug;
    ($offset >= 1) || ($offset = 1);
    ($pSize  >= 1) || ($pSize  = 1);

    my $query="select count(distinct r.rid)   $sqlCond " ;
    my ($resultSize) = $dbh->selectrow_array($query);
      
    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 = ();
    $offset -=1;
    if ($resultSize > 0) {
        $query="select distinct r.rid   $sqlCond  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);
                $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);
}


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_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 mxml_recordPath {
    my ($dbh, $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);

    return $dir;
}

sub srch_recordBrief
{
    my ($xml) = @_;
    #$xml = util_ieFix($xml);
    
    my $record;

    if ($xml =~ m/[\s]*<leader>([\w ]{24})<\/leader>/) {
        $record->{'medium'} = substr($1, 6, 1);
        ($record->{'medium'} =~ m/[acegijkmor]/) || ($record->{'medium'} = 'a');
    }
    
    if ($xml =~ m/[\s]*<controlfield tag="001">([\d]+)<\/controlfield>/) {
        $record->{'rid'} = $1;
    }
    
    if ($xml =~ m/([\s]*<datafield tag="100" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        if ($1 =~ m/<subfield code="a">(.*)<\/subfield>/) {
            $record->{'author'} = $1;
            #$record->{'author'} =~ s/&apos;/'/g;
        }
    }
    
    if ($xml =~ m/([\s]*<datafield tag="245" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        my $f245 = $1;
        if ($f245 =~ m/<subfield code="a">(.*)<\/subfield>/) {
            $record->{'title'} = $1;
            $record->{'title'} =~ s/ *[:\/] *$//
        }
        if ($f245 =~ m/<subfield code="b">(.*)<\/subfield>/) {
            $record->{'title'} .= ': '.$1;
        }
        elsif ($f245 =~ m/<subfield code="p">(.*)<\/subfield>/) {
            $record->{'title'} .= ': '.$1;
        }
        #$record->{'title'} =~ s/&apos;/'/g;
        $record->{'title'} =~ s/ +\///g;
    }
    
    if ($xml =~ m/([\s]*<datafield tag="260" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        my $f260 = $1;
        if ($f260 =~ m/<subfield code="a">(.*)<\/subfield>/) {
            $record->{'pubPlace'} = $1;
        }
        if ($f260 =~ m/<subfield code="b">(.*)<\/subfield>/) {
            $record->{'pubName'} = $1;
            #$record->{'pubName'} =~ s/&apos;/'/g;
        }
        if ($f260 =~ m/<subfield code="c">(.*)<\/subfield>/) {
            $record->{'pubDate'} = $1;
        }
    }
    if ($xml =~ m/([\s]*<datafield tag="300" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        my $f300 = $1;
        if ($f300 =~ m/<subfield code="a">(.*)<\/subfield>/) {
            $record->{'numOfPages'} = $1;
        }
    }

    my $catalogueType = Opals::Context->config('type');
    
    my @itemList;
    my $callNum1st = '';
    my $count852a;
    my @list852a;
    while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
        my $f852 = $1;
        my $item;
        my ($f852_a, $f852_b, $barcode, $callnumber, $typeCircCode, $price);
        if ($catalogueType && $f852 =~ m/<subfield code="a">(.*)<\/subfield>/) {
            $f852_a = $1;
            $count852a->{$f852_a} = 
                ($count852a->{$f852_a}) ? ($count852a->{$f852_a}+1) : 1;
        }

        if ($f852 =~ m/<subfield code="b">(.*)<\/subfield>/) {
            $f852_b = $1;
        }

        # Bar code
        if ($f852 =~ m/<subfield code="p">(.*)<\/subfield>/) {
            $barcode = $1;
        }
        
        # Call number
        if ($f852 =~ m/<subfield code="k">(.*)<\/subfield>/) {
            $callnumber = $1;
        }
        if ($f852 =~ m/<subfield code="h">(.*)<\/subfield>/) {
            $callnumber .= ' ' . $1;
        }
        if ($f852 =~ m/<subfield code="i">(.*)<\/subfield>/) {
            $callnumber .= ' ' . $1;
        }
        if ($f852 =~ m/<subfield code="m">(.*)<\/subfield>/) {
            $callnumber .= ' ' . $1;
        }
        $callnumber =~ s/ +/ /g;
        $callnumber =~ s/(^ +| +$)//g;

        # Code of circulation type
        if ($f852 =~ m/<subfield code="3">(.*)<\/subfield>/) {
            $typeCircCode = $1;
        }

        # Price
        if ($f852 =~ m/<subfield code="9">(.*)<\/subfield>/) {
            $price = $1;
        }

        #$item->{$barcode} = $callnumber;
        $item->{'barcode'}      = $barcode;
        $item->{'callnumber'}   = $callnumber;
        $item->{'typeCircCode'} = $typeCircCode;
        $item->{'price'}        = $price;
        $item->{'f852_a'}       = $f852_a;
        $item->{'f852_b'}       = $f852_b;
        push @itemList, $item;
        
        $callNum1st = $callnumber if ($callNum1st eq '');
    }

    if ($catalogueType) {
        foreach my $k (sort keys %$count852a) {
            push @list852a, {
                code852a  => $k,
                count852a => $count852a->{$k}
            };
        }
        $record->{'stat852a'} = \@list852a;
    }
    
    $record->{'callNum1st'} = $callNum1st;
    $record->{'itemList'}   = \@itemList;

    return $record;
}




1;