package Opals::MarcXml;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    mxml_recordPath
    mxml_update
    mxml_saveTemplate
    mxml_deleteTemplate
    mxml_updateDbZebra
    mxml_delete
    mxml_newItem
    mxml_updateSubjectsList
    mxml_updateRecordIndex
    mxml_writeSortData
    mxml_del_rec_holding
    mxml_addItemType
    mxml_processReplaceItemType
    mxml_exportRecord
    mxml_exportHolding
    mxml_fixXmlRecord
    mxml_processGE852
    mxml_isLock
    mxml_inventory
    ge852
);
#    mxml_validateRid
# Version number
$VERSION   = 0.01;


#use utf8;
use strict;
use POSIX qw(
    ceil
);
use Opals::Template qw(
    tmpl_preference
);

use Opals::Constant;
use Opals::Date qw(
    date_f005
);
use Opals::Search qw(
    srch_recordBrief
    srch_validateRid
);
#    srch_searchRecord
#    srch_maxRid
use Opals::Utility qw(
    util_formatBarcode
    util_getTimeout_min
);


use Opals::User qw(
    user_currentUser
    user_getInformationById
);

#####################################################
sub mxml_recordPath {
    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);

    return $dir;
}


#####################################################
sub mxml_update {
    my ($dbh, $rid, $marcxml, $barcodeNew, $barcodeDeleted) = @_;

    $rid = srch_validateRid($dbh, $rid);
    $marcxml =~ s/<controlfield tag="001">[\d]*<\/controlfield>/<controlfield tag="001">$rid<\/controlfield>/;
    mxml_updateSubjectsList($dbh, $rid, $marcxml);
    mxml_updateRecordIndex($dbh, $rid, $marcxml);
    if (mxml_updateDbCirculation($dbh, $rid, $marcxml, $barcodeNew, $barcodeDeleted) &&
        mxml_updateDbZebra($dbh, $rid, $marcxml)) {
        return $rid;
    }

    return;
}

#####################################################
# added 2006-11-19
# used for handling subject headings
#####################################################

#sub mxml_updateSubjectsList{
#    my ($dbh, $rid, $newMarcXml) = @_;
#
#    if ($rid && $rid =~ m/^[1-9][\d]*$/) {
#        my ($size, $marcXml) =
#            srch_searchRecord($dbh, 0, 'f', "\@attr 1=12 $rid", $ENV{'Z_INDEX_BASE'}, 1);
#        if ($marcXml->[0] ne '') {
#           my @sbjArr = getSubjectList($marcXml->[0]);
#           foreach  my $s(@sbjArr) {
#               deleteSubject($dbh, $s);
#           }
#        }
#    }
#
#    my @newSbjArr = getSubjectList($newMarcXml);
#    foreach my $s (@newSbjArr) {
#         print debug "$s\n";
#        addSubject($dbh, $s);
#    }
#}

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

    if ($rid && $rid =~ m/^[1-9][\d]*$/) {
        my $zdbDir = mxml_recordPath($rid);
        my $xml = getXmlRecord($zdbDir, $rid);
        if ($xml) {
            my @sbjArr = getSubjectList($xml);
            foreach my $s(@sbjArr) {
                deleteSubject($dbh, $s);
            }
        }
    }
    if($newMarcXml && $newMarcXml ne ""){
        my @newSbjArr = getSubjectList($newMarcXml);
        foreach my $s (@newSbjArr) {
            addSubject($dbh, $s);
        }
    }
}

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

    my @subject;
    my ($subj, $field, $code, $data);
    while ($xml =~ s/([\s]*<datafield tag="6[\d]{2}" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w\-]">.*<\/subfield>)+[\s]*<\/datafield>)//) {
        $field = $1;
        $subj = '';
        while ($field =~ s/<subfield code="([\w\-])">(.*)<\/subfield>//) {
            $code = $1;
            $data = $2;
            $data =~ s/[\.|,|:|;]*$//g;
            if ($code !~ m/^(2|-)$/) {
                $subj = ($subj ne '') ? "$subj -- $data" : $data;
            }
        }
        push @subject, $subj;
    }

    return @subject;

}

sub deleteSubject{
    my($dbh,$subject)=@_;
    my $sth ;
    $sth = $dbh->prepare( <<_SQL_);
update opl_subjects 
set    recCount=recCount-1
where   subject = ?
_SQL_
  $sth->execute($subject); 
}

sub addSubject{
    my($dbh,$subject)=@_;
    my $sth ;
    my $sql;
 $sth = $dbh->prepare(<<_SQL);
select id from opl_subjects 
where   subject = ?
_SQL

     $sth->execute($subject);

    my ($rec) =$sth->fetchrow_hashref;
    if($rec){
$sql = <<_SQL_;
update opl_subjects 
set    recCount=recCount+1
where   id = ?
_SQL_
   my $rv = $dbh->do($sql, undef, $rec->{'id'});
        
    }
    else{
$sql = <<_SQL_;
insert into opl_subjects
set    subject=? ,
       recCount=1
_SQL_
   my $rv = $dbh->do($sql, undef, $subject);

    }
}



sub mxml_updateDbCirculation {
    my ($dbh, $rid, $marcxml, $barcodeNew, $barcodeDeleted) = @_;
    my $sth;

    # Add new holding
    my $barcodeNewErr = '';;
    my $titleSort="";
    my @bcNew = split(/,/, $barcodeNew);
    if (@bcNew) {
        $sth = $dbh->prepare(<<_STH_);
insert into opl_item
(barcode, rid, dateImport)
values
(?, ?, now())
_STH_
        foreach my $bc (@bcNew) {
            $sth->execute($bc, $rid) || ($barcodeNewErr .= $bc . ',');
        }

        $sth->finish;
    }

    # Delete 
    my $sth_itemstatus_del = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=?");
    foreach my $bcDel (split(/,/, $barcodeDeleted)) {
        my $maxBarcodeDel = getMaxBcDelNumber($dbh,$bcDel);
        $maxBarcodeDel++;
        my $delPostFix = sprintf("%0.3d",$maxBarcodeDel);
        my $bcDelNew = '___' .  $bcDel . '_' . $delPostFix;
        $sth_itemstatus_del->execute($bcDelNew, ITEM_DELETED);
        mxml_assignDeletionNumber($dbh,$bcDel,$bcDelNew );
    }
    $sth_itemstatus_del->finish;
    #Tue, Oct 28, 2008 @ 15:50:35 EDT
    #get title sort field        
    my @sfCode = ('a', 'b', 'p');
    if($marcxml =~ m/([\s]*<datafield tag="245" ind1="[\d\ ]" ind2="[\d\ ]">([\s]*<subfield code="[\w\d]">.*<\/subfield>)*[\s]*<\/datafield>)/) {
        $titleSort=createSortData($1,245,\@sfCode);
    }
    # END get title sort field    
    
    # Update record info
    $marcxml =~ s/[\s]*<subfield code="-">.*<\/subfield>//g;
    $marcxml =~ s/[\s]*<subfield code="+">.*<\/subfield>//g;
    my $record = srch_recordBrief($marcxml);
    
    my ($leader, $cf_008)=('','');
    my $xmlTmp=$marcxml;
    # get leader
    if ($xmlTmp =~ s/[\s]*<leader>([\w ]{24})<\/leader>//) {
        $leader=$1;
    }    
    # get control field -- 008
    if($xmlTmp =~ s/[\s]*<controlfield tag="008">(.*)<\/controlfield>//){
        $cf_008=$1;
    }
  
    my ($title, $author, $pubPlace, $pubName, $pubDate);
    $title      = $record->{'title'};
    $author     = $record->{'author'};
    $pubPlace   = $record->{'pubPlace'};
    $pubName    = $record->{'pubName'};
    $pubDate    = $record->{'pubDate'};
    $sth = $dbh->prepare(<<_STH_);
replace into opl_marcRecord
set rid         = ?,
    title       = ?,
    titleSort   = ?,
    author      = ?,
    pubPlace    = ?,
    pubName     = ?,
    pubDate     = ?,
    leader      = ?,
    cf_008      = ?,
    modDate     = now()
_STH_
    $sth->execute($rid, $title, $titleSort,$author, $pubPlace, $pubName, $pubDate,$leader,$cf_008);
    $sth->finish;
    # TODO: error checking

    my $itemList = $record->{'itemList'};
    foreach my $itm (@$itemList) {
        mxml_updateItem(
            $dbh,
            $itm->{'barcode'},
            $itm->{'typeCircCode'},
            $itm->{'callnumber'},
            $itm->{'price'},
            $itm->{'f852_c'}

        );
    }
    
    $sth = $dbh->prepare(<<_STH_);
update opl_marcRecord m inner join (select max(i.modDate) as modDate,rid from opl_item i where i.rid=? group by rid) as t
on m.rid=t.rid && m.modDate <t.modDate 
set m.modDate=t.modDate;   
_STH_

$sth->execute($rid);
    $sth->finish;
        
    mxml_updateItemInfo($dbh, $marcxml);
#    mxml_updateItem($dbh, $rid, $marcxml);
   $sth = $dbh->prepare("select title from opl_marcRecord where rid=?");
   $sth->execute($rid);
   my ($t)= $sth->fetchrow_array;

    return 1;
}


#####################################################
sub mxml_updateItemInfo {
    my ($dbh, $marcxml) = @_;

    my $sth_remove = $dbh->prepare(<<_STH_);
delete from opl_itemInfo
where   barcode = ?
_STH_

    my $sth = $dbh->prepare(<<_STH_);
insert into opl_itemInfo
set barcode    = ?,
    sf852Code  = ?,
    sf852Data  = ?
_STH_

    while ($marcxml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
        my $f852 = $1;
        my @sf852;
        my $barcode;
        while ($f852 =~ s/<subfield code="([\w])">(.*)<\/subfield>//) {
            my ($code, $data) = ($1, $2);
            if ($code eq 'p') {
                $barcode = $data;
                #$barcode =~ s/[^\w]//g;
            }
            else {
                push @sf852, [$code, $data] if ($code && $data);
            }
        }

        $sth_remove->execute($barcode);
        foreach my $sf (@sf852) {
            $sth->execute($barcode, $sf->[0], $sf->[1]);
        }
    }
    $sth_remove->finish;
    $sth->finish;
}


#####################################################
sub mxml_updateItem {
    my ($dbh, $barcode, $typeId, $callNumber, $price,$location) = @_;

    mxml_addItemType($dbh, $typeId);    

    my $sth = $dbh->prepare(<<_STH_);
update  opl_item
set     typeId     = ?,
        callNumber = ?,
        price      = ?,
        location   = ?
where   barcode = ?
_STH_
    $sth->execute($typeId, $callNumber, $price, $location,$barcode);
    $sth->finish;
}


#####################################################
sub mxml_updateDbZebra {
    my ($dbh, $rid, $xml) = @_;

    my $zebraidx = Opals::Context->config('zebraidx');
    return unless (-f $zebraidx);

    my $dir = mxml_recordPath($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);

    unless (-d $dir) {
        mkdir $dir, 0775;
    }

    # Write sort data
    $xml = mxml_writeSortData($xml);
    #/Write sort data

    $xml =~ s/<controlfield tag="001">[\d]*<\/controlfield>/<controlfield tag="001">$rid<\/controlfield>/;
    my $f005 = date_f005();
    $xml =~ s/<controlfield tag="005">[\d]{9,14}\.(0|1)<\/controlfield>/<controlfield tag="005">$f005<\/controlfield>/;
    
#    open  RECORD, ">:utf8", "$dir/$rid.xml";
    open  RECORD, ">$dir/$rid.xml";
    print RECORD $xml;
    close RECORD;

    # Enqueueing record for z-index
    $dbh->do("update opl_marcRecord set zIndexed = 0 where rid = $rid");

    return 1;
}


#####################################################
sub mxml_delete {
    my ($dbh, $ridList) = @_;

    my $zebraidx = Opals::Context->config('zebraidx');
    return unless (-f $zebraidx);
    
    my $zRoot   = Opals::Context->config('zRoot');
    my $zPort   = Opals::Context->config('zPort');
    my $zDatabase = Opals::Context->config('zDatabase');

    my $sth_loan = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_item as i, opl_loan as l
where   i.rid = ? &&
        i.barcode not regexp '^___' &&
        i.barcode = l.barcode &&
        l.dateReturn is null
_STH_
    my $sth_hold = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_item as i, opl_hold as h
where   i.rid = ? &&
        i.barcode = h.barcode &&
        to_days(h.dateExpiry) - to_days(now()) >= 0  &&
        h.dateLoan is null &&
        h.dateCancel is null
_STH_
    my $sth_marcDelete = $dbh->prepare(<<_STH_);
insert into opl_marcDelete
set     content = ?
_STH_

    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    my @ridArray = split(/,/, $ridList);
    
    my %dirList;
    my $remainList = '';
    my ($countLoan, $countHold, $dir, $content);
    foreach my $rid (@ridArray) {
        $sth_loan->execute($rid);
        ($countLoan) = $sth_loan->fetchrow_array;

        $sth_hold->execute($rid);
        ($countHold) = $sth_hold->fetchrow_array;
        
        if ( $countLoan > 0 || $countHold > 0 ) {
            $remainList .= $rid . ",";
        }
        else {
            mxml_updateHoldingToDelete($dbh, $rid);

            $dir = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);

            open REC_DEL, "<$dir/$rid.xml";
            $content = '';
            while (<REC_DEL>) {
                $content .= $_;
            }
            close REC_DEL;

            $sth_marcDelete->execute($content);
            my @sbjArr = getSubjectList($content);
            foreach my $s(@sbjArr) {
                deleteSubject($dbh, $s);
            }

            unlink "$dir/$rid.xml";
            $dirList{$dir} = 1;
        }
        
        # Enqueueing record for z-index
        # Tue, Oct 28, 2008 @ 16:01:51 EDT 
        # add to query to update the delete status field as well

        $dbh->do("update opl_marcRecord set zIndexed = 0 ,deleted=1 where rid = $rid");
    }
    $sth_loan->finish;
    $sth_hold->finish;
    $sth_marcDelete->finish;

    $remainList =~ s/,+/,/g;
    $remainList =~ s/(^,|,$)//g;

#    foreach my $dir (keys %dirList) {
#        system "$zebraidx -c $zRoot/$zPort/zebra.cfg -d $zDatabase update $dir >> $zRoot/$zPort/log/zebraidx.log 2>&1";
#    }
#    system "$zebraidx -c $zRoot/$zPort/zebra.cfg commit >> $zRoot/$zPort/log/zebraidx.log 2>&1";

    return $remainList;
}


#################################################################
sub mxml_updateHoldingToDelete {
    my ($dbh, $rid) = @_;
    #my @barcodeToChange = mxml_getBarcodeToChangeList($dbh, $rid);
    my $sth = $dbh->prepare(<<_STH_);
select barcode  from opl_item
where   rid = ? &&
        substring(barcode, 1, 3) <> '___'
_STH_

    my $sth_itemstatus_del = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=?");
    $sth->execute($rid);
    while (my $holding = $sth->fetchrow_hashref) {
        my $bcDel=$holding->{'barcode'};
        my $maxBarcodeDel = getMaxBcDelNumber($dbh,$bcDel);
        $maxBarcodeDel++;
        my $delPostFix = sprintf("%0.3d",$maxBarcodeDel);
        my $bcDelNew = '___' .  $bcDel . '_' . $delPostFix;
        $sth_itemstatus_del->execute($bcDelNew, ITEM_DELETED);
        mxml_assignDeletionNumber($dbh,$bcDel,$bcDelNew);
    }
   $sth->finish;
     
 }

#################################################################
sub mxml_getBarcodeToChangeList {
    my ($dbh, $rid) = @_;
    my @barcodeToChange;

    # Get barcode on loan
    my $sth = $dbh->prepare(<<_STH_);
select  distinct i.barcode
from    opl_item as i, opl_loan as l
where   i.rid = ? &&
        i.barcode = l.barcode &&
        substring(i.barcode,1,3)<> '___' 
_STH_
    $sth->execute($rid);
    while (my $holding = $sth->fetchrow_hashref) {
        push @barcodeToChange, $holding->{'barcode'};
    }
    $sth->finish;
    
    # Get barcode on hold
    $sth = $dbh->prepare(<<_STH_);
select  distinct i.barcode
from    opl_item as i, opl_hold as h
where   i.rid = ? &&
        i.barcode = h.barcode &&
        substring(i.barcode,1,3)<> '___' 
_STH_
    $sth->execute($rid);
    while (my $holding = $sth->fetchrow_hashref) {
        push @barcodeToChange, $holding->{'barcode'};
    }
    $sth->finish;
    
    # Get barcode found
    $sth = $dbh->prepare(<<_STH_);
select  distinct i.barcode
from    opl_item as i, opl_found as f
where   i.rid = ? &&
        i.barcode = f.barcode &&
        substring(i.barcode,1,3)<> '___' 
_STH_
    $sth->execute($rid);
    while (my $holding = $sth->fetchrow_hashref) {
        push @barcodeToChange, $holding->{'barcode'};
    }
    $sth->finish;
    
    # Get barcode in item status table
    $sth = $dbh->prepare(<<_STH_);
select  distinct i.barcode
from    opl_item as i, opl_itemstatus as s
where   i.rid = ? &&
        i.barcode = s.barcode &&
        substring(i.barcode,1,3)<> '___' 
_STH_
    $sth->execute($rid);
    while (my $holding = $sth->fetchrow_hashref) {
        push @barcodeToChange, $holding->{'barcode'};
    }
    $sth->finish;

    return @barcodeToChange;
}


sub mxml_assignDeletionNumber {
   my ($dbh,$bcDel,$bcDelNew) = @_;

   $dbh->do(<<_STH_);
delete from opl_itemInfo
where   barcode = '$bcDel'
_STH_

   my $sth_item = $dbh->prepare(<<_STH_);
update  opl_item
set     barcode   = ?,
        available = 0
where   barcode   = ?
_STH_
   my $sth_itemStatus = $dbh->prepare(<<_STH_);
update  opl_itemstatus
set     barcode   = ?
where   barcode   = ?
_STH_

    my $sth_loan = $dbh->prepare(<<_STH_);
update  opl_loan
set     barcode = ?
where   barcode = ?
_STH_
    my $sth_hold = $dbh->prepare(<<_STH_);
update  opl_hold
set     barcode = ?
where   barcode = ?
_STH_
    my $sth_found = $dbh->prepare(<<_STH_);
update  opl_found
set     barcode = ?
where   barcode = ?
_STH_

    $sth_item->execute($bcDelNew, $bcDel);
    $sth_itemStatus->execute($bcDelNew, $bcDel);
    $sth_loan->execute($bcDelNew, $bcDel);
    $sth_hold->execute($bcDelNew, $bcDel);
    $sth_found->execute($bcDelNew, $bcDel);
    
    $sth_item->finish;
    $sth_itemStatus->finish;
    $sth_loan->finish;
    $sth_hold->finish;
    $sth_found->finish;
}


#####################################################
sub mxml_saveTemplate {
    my ($in) = @_;
    my ($dbh, $op, $tid, $rank, $name, $content);
    $dbh        = $in->{'dbh'};
    $op         = $in->{'op'};
    $tid        = $in->{'tid'};
    $rank       = $in->{'rank'};
    $name       = $in->{'name'};
    $content    = $in->{'content'};

    my ($sth, $rv);
    if ($op eq 'tmplMarcSaveAs') {
        $sth = $dbh->prepare(<<_STH_);
insert into opl_template
(rank, name, content)
values
(?, ?, ?)
_STH_
        $rv = $sth->execute($rank, $name, $content) || return;
    }
    else {
        $sth = $dbh->prepare(<<_STH_);
update  opl_template
set     rank = ?, name = ?, content = ?
where   tid = ?
_STH_
        $rv = $sth->execute($rank, $name, $content, $tid) || return;
    }
    $sth->finish;

    return $rv;
}


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

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

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

    return $tmplName;
}


#####################################################
sub mxml_writeSortData {
    my ($xml) = @_;
    
    # Remove existing sort data
    $xml =~ s/[\s]*<subfield code="-">.*<\/subfield>//g;
    #/Remove sort data

    # Write sort data
    my $tag = 245;
    my @sfCode = ('a', 'b', 'p');
    $xml = mxml_addSortData($xml, $tag, \@sfCode);

    $tag = 260;
    @sfCode = ('c');
    $xml = mxml_addSortData($xml, $tag, \@sfCode);

    $tag = 852;
    @sfCode = ('k', 'h', 'i');
    $xml = mxml_addSortData($xml, $tag, \@sfCode);
    #/Write sort data

    return $xml;
}


#####################################################
sub mxml_addSortData {
    my ($xml, $tag, $code) = @_;

# OPALS's signature: __OPALS_MARC_XML_f801d6faada54a60faa577bbb60f33__
# Wed, Oct 29, 2008 @ 09:05:48 EDT
# move the create sort data codes to function createSortData
# so it can be used from somewhere else such as mxml_updateDbCirculation

    while ($xml =~ s/([\s]*<datafield tag="$tag" ind1="[\d\ ]" ind2="[\d\ ]">([\s]*<subfield code="[\w\d]">.*<\/subfield>)*[\s]*<\/datafield>)/__OPALS_MARC_XML_f801d6faada54a60faa577bbb60f33__/) {
        my $field = $1;
        my $dataSort = createSortData($field,$tag,$code)  ;
        $field =~ s/<\/datafield>/  <subfield code="-">$dataSort<\/subfield>\n  <\/datafield>/;
        $xml =~ s/__OPALS_MARC_XML_f801d6faada54a60faa577bbb60f33__/$field/;
    }

    return $xml;
}


#####################################################
# Wed, Oct 29, 2008 @ 09:06:19 EDT
# Break from function mxml_addSortData

sub  createSortData{
    my ($fieldXml,$tag,$code)=@_;
    my $dataSort="";
    my $subfield;
    foreach my $c (@$code) {
        if ($fieldXml =~ m/[\s]*<subfield code="$c">(.*)<\/subfield>/) {
            $subfield->{$c} = $1;
        }
    }        
    if ($tag == 245) {
        if ($subfield->{'a'}) {
            $dataSort .= $subfield->{'a'};
            $dataSort =~ s/[\ \:]+$//;

            my $ind2 = 0;
            if ($fieldXml =~ m/[\s]*<datafield tag="245" ind1="[01]" ind2="([\d])">/) {
                $ind2 = $1;
            }

            $dataSort = substr($dataSort, $ind2);
        }
        
        if ($subfield->{'b'}) {
            $dataSort .= ': ' . $subfield->{'b'};
        }
        elsif ($subfield->{'p'}) {
            $dataSort .= ': ' . $subfield->{'p'};
        }
    }
    elsif ($tag == 260 && 
           $subfield->{'c'} && 
           $subfield->{'c'} =~ m/([\d]{4})/) {
            $dataSort .= $1;
    }
    elsif ($tag == 852) {
        $dataSort .= $subfield->{'k'} if $subfield->{'k'};
        $dataSort .= ' ' . $subfield->{'h'} if $subfield->{'h'};
        $dataSort .= ' ' . $subfield->{'i'} if $subfield->{'i'};
        $dataSort =~ s/ +/ /g;
        $dataSort =~ s/(^ | $)//g;
    }

   return $dataSort;

}

#####################################################

sub getMaxBcDelNumber{
    my ($dbh,$bcDel) = @_;
    # Get max number of deletion for a given barcode
    # FORMAT DEL BARCODE: ___ORGINALBARCODE_maxNum such as ___39942000171049_001, ___39942000171049_002,___39942777777_001 ect.
    my $sth;
        
    $sth = $dbh->prepare(<<_STH_);
select  max(barcode)
from    opl_item
where   barcode like '\_\_\_$bcDel\_%'
_STH_
    
    $sth->execute();
    my ($maxBarcodeDel) = $sth->fetchrow_array;
    $sth->finish;
    if($maxBarcodeDel =~ m/\_([\d]+)$/){
        return int($1);
    }
   return  0;
}

#####################################################
sub mxml_del_rec_holding{
    my ($dbh,$rid_bc_list,$opt)=@_;

#  opt: 0|1
#       1: delete the marcXml record if it has 0 holding left.
#       0: leave it as it is.

    my $zebraidx = Opals::Context->config('zebraidx');
    my $zRoot   = Opals::Context->config('zRoot');
    my $zPort   = Opals::Context->config('zPort');
    my $zDatabase = Opals::Context->config('zDatabase');
    my %dirList;
    my $str="";
    foreach my $rid (keys  %$rid_bc_list){
        my $dir = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);
        if (-f "$dir/$rid.xml") {
            $dirList{$dir} = 1;
            del_recHoldings_xml($rid,$rid_bc_list->{$rid});
            del_recHoldings_db($dbh,$rid_bc_list->{$rid});
            
            if($opt==1 && getNumOfRecHolding($dbh,$rid)==0){
                    open REC_DEL, "<$dir/$rid.xml";
                    my $content = '';
                    while (<REC_DEL>) {
                        $content .= $_;
                    }
                    close REC_DEL;
                    my $sth_marcDelete = $dbh->prepare("insert into opl_marcDelete set content = ?");
                    $sth_marcDelete->execute($content);
                    $sth_marcDelete->finish; 
                    unlink "$dir/$rid.xml";
            }
        }
        
        # Enqueueing record for z-index
        $dbh->do("update opl_marcRecord set zIndexed = 0 where rid = $rid");
    }
#    foreach my $dir (keys %dirList) {
#        system "$zebraidx -c $zRoot/$zPort/zebra.cfg -d $zDatabase update $dir >> $zRoot/$zPort/log/zebraidx.log 2>&1";
#    }
#    system "$zebraidx -c $zRoot/$zPort/zebra.cfg commit >> $zRoot/$zPort/log/zebraidx.log 2>&1";
}
#####################################################
sub del_recHoldings_xml{
    my($rid,$bc_list) =@_;
    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);
    unless (-d $dir) {
            mkdir $dir, 0770;
    }        
     
    if (-f "$dir/$rid.xml") {
            my $xml = '';
            open  RECORD, "<$dir/$rid.xml";
            my $line;
            while (<RECORD>) {
                $line = $_;
                if ($line !~ m/<subfield code="-"/) {
                    $xml .= $line;
                }
            }
            my $newSubFields="";
            my $found=0;

            while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)+[\s]*<\/datafield>)//) {
                my $tmp852 =$1;
                $found=0;
                foreach my $bc (keys %$bc_list){
                    $bc=~ s/(\+|\$|\^|\\)/\\$1/g;
                    if ($tmp852 =~ m/<subfield code="p">$bc<\/subfield>/){
                        $found=1;
                        last;
                    }
                 }
                 if(!$found){
                     $newSubFields .= $tmp852;
                 }
            }
            $xml =~ s/[\s]*<\/record>/$newSubFields\n<\/record>/;
            close RECORD;
            open  RECORD, ">$dir/$rid.xml";
            print RECORD $xml;
            close RECORD;
            

    }
}
#####################################################
sub del_recHoldings_db{
    my($dbh,$bc_list) =@_;
    my $sth_itemstatus_del = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=?");
    foreach my $bcDel (keys %$bc_list){
        if(isBcExisted_db($dbh,$bcDel)){
            my $maxBcDelNumber= getMaxBcDelNumber($dbh,$bcDel);
            $maxBcDelNumber++;
            my $delPostFix = sprintf("%0.3d",$maxBcDelNumber);
            my $bcDelNew = '___' .  $bcDel . '_' . $delPostFix;
            $sth_itemstatus_del->execute($bcDelNew, ITEM_DELETED);
            $sth_itemstatus_del->finish;
            mxml_assignDeletionNumber($dbh,$bcDel,$bcDelNew);
        }
    }
}

#####################################################
sub isBcExisted_db{
    my($dbh,$bc) =@_;
    my $sth = $dbh->prepare("select count(*) from opl_item where barcode=?");
    $sth->execute($bc);
    my ($count) = $sth->fetchrow_array;
    $sth->finish;
    return $count;
    
    
}
#####################################################
sub getNumOfRecHolding{
    my ($dbh,$rid) = @_;
    my $sth = $dbh->prepare("select count(*) from opl_item where rid=? && substring(barcode, 1, 3) <> '___'");
    $sth->execute($rid);
    my ($count) = $sth->fetchrow_array;
    $sth->finish;
    return $count;
 
}

#####################################################
sub mxml_newItem {
    my ($dbh, $ridList,$expireDays) = @_;
    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    
    my $dateToday = date_f005();
       $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $noteStr="set new on: " . $dateToday  ;

        my $sql = "insert into opl_itemstatus (barcode,ondate,status,note) 
                                 select distinct barcode, '" .$expireDays . "',6 , '" . $noteStr . "'
                                 from opl_item where rid in (" . $ridList .") && substring(barcode, 1, 3) <> '___'";
        my $sth = $dbh->prepare($sql);
        $sth->execute();
}


#####################################################
sub mxml_addItemType {
    my ($dbh, $itemTypeId) = @_;

    my ($isInDb) = $dbh->selectrow_array('select count(*) from opl_itemType where id=?', undef, $itemTypeId);
    if ($isInDb) {
        return;
    }
     
    if (!$dbh->do("insert into opl_itemType set id = ?", undef, $itemTypeId)) {
        return;
    }
   
    my $sth;
    my $defItemType;

    my ($defaultItemTypeId) = $dbh->selectrow_array(<<_SQL_);
select  id
from    opl_itemType
where   defaultType = 1
_SQL_
    $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_itemTypeParam
where   itemTypeId = ?
_STH_
    $sth->execute($defaultItemTypeId) || return;
    while (my $itemType = $sth->fetchrow_hashref) {
        $defItemType->{$itemType->{'userTypeId'}} = {
            loanPeriod    => $itemType->{'loanPeriod'},
            renewalPeriod => $itemType->{'renewalPeriod'},
            reservePeriod => $itemType->{'reservePeriod'},
            holdPeriod    => $itemType->{'holdPeriod'},
            gracePeriod   => $itemType->{'gracePeriod'},
            maxRenewal    => $itemType->{'maxRenewal'},
        };
    }
    $sth->finish;

    $sth = $dbh->prepare(<<_STH_);
select  catid
from    opl_category
_STH_

    my $sth_itemTypeParam = $dbh->prepare(<<_STH_);
insert into opl_itemTypeParam
set     itemTypeId    = ?,
        userTypeId    = ?,
        loanPeriod    = ?,
        renewalPeriod = ?,
        reservePeriod = ?,
        holdPeriod    = ?,
        gracePeriod   = ?,
        maxRenewal    = ?
_STH_

    $sth->execute() || return;
    while (my ($userTypeId) = $sth->fetchrow_array) {
        $sth_itemTypeParam->execute(
            $itemTypeId,
            $userTypeId,
            $defItemType->{$userTypeId}->{'loanPeriod'},
            $defItemType->{$userTypeId}->{'renewalPeriod'},
            $defItemType->{$userTypeId}->{'reservePeriod'},
            $defItemType->{$userTypeId}->{'holdPeriod'},
            $defItemType->{$userTypeId}->{'gracePeriod'},
            $defItemType->{$userTypeId}->{'maxRenewal'}
        );
    }
    $sth_itemTypeParam->finish;
    $sth->finish;
}



#####################################################
# added 2006-11-20  -- Ha
# 
# Global Edit subfield 852
# 
#####################################################

sub mxml_processGE852 {
    #my $currentTime = `date -R`;#`date +%k`;
    print 'STARTTIME: ', `date -R`, "\n";
    my ($dbh) = @_;
    my $syspref = tmpl_preference($dbh);
    my $timeout = $syspref->{'timeout'}; 
    my $timeout_min =  util_getTimeout_min($timeout);
  
    my $sth = $dbh->prepare(<<_SQL_);
select  *
from    opl_ge852request 
where   status in ('waiting', 'processing')
_SQL_

    my $sth_rid = $dbh->prepare(<<_SQL_);
select  * 
from    opl_ge852record
where   req_id = ?  
order by rid
_SQL_
   
    my $sth_rid_cnt = $dbh->prepare(<<_SQL_);
select  count(rid) as count
from    opl_ge852record
where   req_id = ?  
_SQL_

    my $sql_start = <<_SQL_;
update  opl_ge852request
set     status = 'processing'
where   req_id = ?
_SQL_

    my $sql_finish = <<_SQL_;
update  opl_ge852request
set     status = 'done',
        finishDate = now()
where   req_id = ?
_SQL_


    $sth->execute(); 
    my @bindValue; 
    my $retval;
    my $stop = 0;
    my ($isLock, $last_sessionid);
    while (my $request = $sth->fetchrow_hashref) {
        @bindValue = ($request->{'req_id'});
        $dbh->do($sql_start, undef, @bindValue);
      
        $sth_rid->execute($request->{'req_id'});
        $sth_rid_cnt->execute($request->{'req_id'});
        my $ridNum    = $sth_rid_cnt->fetchrow_array;
        my $ridNumTmp = $ridNum ;
        my $preRid=-1;
        my @bcList=();
        while (my $rec = $sth_rid->fetchrow_hashref) {
            ($isLock, $last_sessionid) = mxml_isLock($dbh, $rec->{'rid'});
            if ($isLock eq 'true'){
                next;
            }
            if($preRid == $rec->{'rid'}){
                if($rec->{'barcode'}){
                    push @bcList, $rec->{'barcode'};
                }
            }
            else{
                if($preRid !=-1){
                    if($request->{'action'} eq 'changeRecType'){
                       geChangeRecType($dbh,
                            $request->{'req_id'},
                            $preRid,
                            $request->{'newRecType'}
                       ); 
                    }
                    elsif($request->{'action'} eq 'appendF'){
                        geInsertDataField($dbh,
                            $request->{'req_id'}, 
                            $preRid, 
                            $request->{'tag'}, 
                            $request->{'n_code'}, 
                            $request->{'n_data'}
                        );
                    }
                    else{
                        geDataField($dbh, 
                            $request->{'req_id'},
                            $preRid,
                            \@bcList,
                            $request->{'action'}, 
                            $request->{'tag'}, 
                            $request->{'o_code'},
                            $request->{'n_code'},
                            $request->{'o_data'},
                            $request->{'n_data'}
                        );
                   }
 
=item  THAI
                    if($request->{'action'} ne 'changeRecType'){
                        geDataField($dbh, 
                            $request->{'req_id'},
                            $preRid,
                            \@bcList,
                            $request->{'action'}, 
                            $request->{'tag'}, 
                            $request->{'o_code'},
                            $request->{'n_code'},
                            $request->{'o_data'},
                            $request->{'n_data'}
                        );
                    }
                    else{
                       geChangeRecType($dbh,
                            $request->{'req_id'},
                            $preRid,
                            $request->{'newRecType'}
                             );
                    } 
=cut                    
               }
                
                undef (@bcList);
                push @bcList,$rec->{'barcode'};
                $preRid = $rec->{'rid'};
            }
            $ridNumTmp -=1;
        } #while (my $rec = $sth_rid->fetchrow_hashref)
        if($preRid !=-1 ){
                if($request->{'action'} eq 'changeRecType'){
                   geChangeRecType($dbh,
                        $request->{'req_id'},
                        $preRid,
                        $request->{'newRecType'}
                   ); 
                }
                elsif($request->{'action'} eq 'appendF'){
                    geInsertDataField($dbh,
                        $request->{'req_id'}, 
                        $preRid, 
                        $request->{'tag'}, 
                        $request->{'n_code'}, 
                        $request->{'n_data'}
                    );
                }
                else{
                    geDataField($dbh, 
                        $request->{'req_id'},
                        $preRid,
                        \@bcList,
                        $request->{'action'}, 
                        $request->{'tag'}, 
                        $request->{'o_code'},
                        $request->{'n_code'},
                        $request->{'o_data'},
                        $request->{'n_data'}
                    );
               }

=item  THAI
             if($request->{'action'} ne 'changeRecType' ){
                    if(scalar(@bcList)>0){
                        geDataField($dbh, 
                            $request->{'req_id'},
                            $preRid,
                            \@bcList,
                            $request->{'action'}, 
                            $request->{'tag'}, 
                            $request->{'o_code'},
                            $request->{'n_code'},
                            $request->{'o_data'},
                            $request->{'n_data'}
                        );
                        }
               }
               else{
                   geChangeRecType($dbh,
                        $request->{'req_id'},
                        $preRid,
                        $request->{'newRecType'}
                        );
               }  
=cut                         
        }       

        if ($ridNumTmp <= 0) {
            $dbh->do($sql_finish, undef, @bindValue);
        }
    }

    $sth_rid->finish;
    $sth_rid_cnt->finish;
    $sth->finish;
}
#####################################################
sub mxml_isLock {
    my ($dbh, $rid, $uid) = @_;
    my ($retval,$sessionid,$lockTime ) = ('false','','false');
    my $sql_MARCrec = $dbh->prepare(<<_SQL_);
select  sessionid, lockExpire,
        editUid,
        time_to_sec(timediff(lockExpire, now())) as deltaLockTime
from    opl_marcRecord
where   rid = ?
_SQL_
    $sql_MARCrec->execute($rid);
    my $sf = $sql_MARCrec->fetchrow_hashref;

    if (   (   !$uid
            &&  $sf->{'deltaLockTime'}
            &&  $sf->{'deltaLockTime'} > 0)
        || (    $uid
            &&  $sf->{'editUid'} != $uid
            &&  $sf->{'deltaLockTime'}
            &&  $sf->{'deltaLockTime'} > 0)
       ) 
    {

        $retval    = 'true';
    }
    if(!$sf->{'lockExpire'} &&  $sf->{'deltaLockTime'}){
      $lockTime = 'true';
    };
    
    $sessionid =$sf->{'sessionid'};
    $sql_MARCrec->finish;
    return ($retval,$sessionid,$lockTime); 
}
#####################################################
sub ge852 {
    my ($dbh,$req_id, $rid, $bcList, $action,$o_code, $n_code,$o_data, $n_data) = @_;

    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 $syspref = tmpl_preference($dbh);
    my $timeout = $syspref->{'timeout'}; 
    my $timeout_min =  util_getTimeout_min($timeout);
    my $sth_status = $dbh->prepare(<<_SQL_);
update  opl_ge852record
set     status = 'done'
where   req_id = ? && rid= ?
_SQL_

    #set lock record in table opl_marcRecord
    lockRecord($dbh,$rid,$timeout_min)|| return;   
    my $content = '';
    my $line;
    
    my $tmp_data;
    # Read XML records
    open RECORD, "<$dir/$rid.xml";
    #Action : Delete | Replace | Add new | Transfer | Copy 
    my @sf852=();
    my $skipLine=0;
    my $isField852 = 0;
    while (<RECORD>) {
        $line = $_;
        if ($line =~ m/<datafield tag="852" /) {
            $isField852 = 1;
        }
        elsif ($line =~ m/<\/datafield>/) {
            if(holdingHasBc($bcList,\@sf852)){
               @sf852 = geSubfields(\@sf852,$action,$o_code, $n_code,$o_data, $n_data);
            }
            $content .= join("",@sf852);
            undef (@sf852);
            $isField852 = 0;
            $skipLine   = 0;
        }
        elsif ($line =~ m/<subfield code="([\-\+])">/) {
            next;
        }
        else{
            if($isField852){
               push @sf852,$line;
               $skipLine=1; 
            }
        }
        if(!$skipLine){
          $content .= $line; 
        }

    }#while (<RECORD>)

    
    close RECORD;
    # call mxml_update...
    my($barcodeNew, $barcodeDeleted);
    $barcodeNew ='';
    $barcodeDeleted='';
    mxml_update($dbh, $rid, $content, $barcodeNew, $barcodeDeleted);
    print "GE852 $rid done.\n";
    unlockRecord($dbh,$rid);
    $sth_status->execute($req_id,$rid);
    $sth_status->finish;
 

}
#####################################################

sub geSubfields{
    my ($sf,$action,$o_code, $n_code,$o_data, $n_data)=@_;
    my @rVal=();
    if($action eq 'delete'){
        @rVal = deleteSf($o_code,$sf);
    }
    elsif($action eq 'append'){
        @rVal = addSf($n_code,$n_data,$sf);
    }
    elsif($action eq 'copy'){
        @rVal = copySf($o_code, $n_code,$sf);
    }
    elsif($action eq 'transfer'){
        @rVal = transferSf($o_code, $n_code,$sf);
    }
    elsif($action eq 'update'){
         @rVal = replaceSf($o_code, $o_data, $n_data,$sf);
    }
    elsif($action eq 'updateAddNew'){
        @rVal = addUpdateSf($o_code,$n_data, $sf);
    }
    else{
        return @$sf;
    }
    return @rVal;
}

#----------------------------------------------------
sub holdingHasBc{
    my ($bcList,$sfArray)=@_;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] =~ m/<subfield code="p">(.+)<\/subfield>/){
            foreach my $bc(@$bcList){
                if($bc eq $1){
                    return 1;        
                }
            }
            return 0;
        }
    }
    return 0;
}

#----------------------------------------------------
sub deleteSf{
    my ($sfCode,$sfArray)=@_;
    my @rVal;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] !~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
           push @rVal, @$sfArray[$i];
        }
    }
    return @rVal;
}
                               
#----------------------------------------------------
sub addSf{
    my ($sfCode,$sfData,$sfArray)=@_;
    my @rVal;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] !~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
           push  @rVal,@$sfArray[$i];
        }
        else{
           return @$sfArray;
        }
    }
    push @rVal,"    <subfield code=\"$sfCode\">$sfData</subfield>\n";
    return @rVal;
}
#----------------------------------------------------

sub replaceSf{
    my ($sfCode,$searchTerm,$replaceTerm,$sfArray)=@_;
    my @rVal;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] !~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
           push  @rVal,@$sfArray[$i];
        }
        else{
           my $sfData=$1;
           $searchTerm  =~ s/\*/\.\*/g;
           $sfData      =~ s/$searchTerm/$replaceTerm/i;
           push @rVal,"    <subfield code=\"$sfCode\">$sfData</subfield>\n";
        }
    }
    return @rVal;
}
#----------------------------------------------------

sub transferSf{
    my ($sfCode,$n_sfCode,$sfArray)=@_;
    my $p=-1;
    my $sfData="";
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] =~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
            $sfData=$1;
            $p=$i;
        }
        elsif(@$sfArray[$i] =~ m/<subfield code="$n_sfCode">(.*)<\/subfield>/){
            return  @$sfArray;
        }
    }
    @$sfArray[$p] = "    <subfield code=\"$n_sfCode\">$sfData</subfield>\n";
    return  @$sfArray;
}

#----------------------------------------------------
sub copySf{
    my ($sfCode,$n_sfCode,$sfArray)=@_;
    my $sfData="";
    my $p=-1;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] =~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
            $sfData=$1;
        }
        elsif(@$sfArray[$i] =~ m/<subfield code="$n_sfCode">(.*)<\/subfield>/){
            $p=$i;
        }
    }
    if($sfData ne ''){
        if($p>=0){
            @$sfArray[$p] = "    <subfield code=\"$n_sfCode\">$sfData</subfield>\n";
        }
        else{
            push @$sfArray,  "    <subfield code=\"$n_sfCode\">$sfData</subfield>\n";            
        }
    }
    return  @$sfArray;
}
#----------------------------------------------------

sub addUpdateSf{
    my ($sfCode,$sfData,$sfArray)=@_;
    my $fieldExist=0;
    for(my $i=0; $i< scalar(@$sfArray); $i++){
        if(@$sfArray[$i] =~ m/<subfield code="$sfCode">(.*)<\/subfield>/){
           $fieldExist=1; 
           @$sfArray[$i] = "    <subfield code=\"$sfCode\">$sfData</subfield>\n";           
        }
    }
    if(!$fieldExist){
        push @$sfArray,  "    <subfield code=\"$sfCode\">$sfData</subfield>\n";            
    }
    return  @$sfArray;
}

#####################################################
sub geChangeRecType {
    my ($dbh,$req_id, $rid, $newRecType) = @_;

    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 $syspref = tmpl_preference($dbh);
    my $timeout = $syspref->{'timeout'}; 
    my $timeout_min =  util_getTimeout_min($timeout);
    my $sth_status = $dbh->prepare(<<_SQL_);
update  opl_ge852record
set     status = 'done'
where   req_id = ? && rid= ?
_SQL_

     #set lock record in table opl_marcRecord
    lockRecord($dbh,$rid,$timeout_min) || return;
       
    my $content = '';
    my $line;
    
    my $tmp_data;
    # Read XML records
    open RECORD, "<$dir/$rid.xml";
    my $skipLine=0;
    my $leader="";
    my @cFields=();
    my $geDone=0;
    while (<RECORD>) {
        $line = $_;
        if($line =~ m/<\?xml (.*) \?>/i ){
            $skipLine=1;
        }
        elsif($line =~ m/<record>/ || $line =~ m/<\/record>/){
            $skipLine=0;
        }
        elsif ($line =~ m/<leader>(.*)<\/leader>/) {
            $leader=$1;
            $skipLine=1;
        }
        elsif($line =~ m/<controlfield tag=\"00\d\">(.*)<\/controlfield>/){
            if($1 && $1 ne ''){
                push @cFields,$line;
            }
            $skipLine=1;
        }
        elsif(!$geDone){
            ($leader,@cFields)=changeRecType($newRecType,$leader,\@cFields);
            $content .="  <leader>$leader</leader>\n";
            $content .=join("",@cFields);
            undef @cFields; 
            $skipLine=0; 
            $geDone=1;
        }
        if(!$skipLine){
          $content .= $line; 
        }
    }#while (<RECORD>)
    #print $content;
    
    close RECORD;
    # call mxml_update...
    my($barcodeNew, $barcodeDeleted);
    $barcodeNew ='';
    $barcodeDeleted='';
    mxml_update($dbh, $rid, $content, $barcodeNew, $barcodeDeleted);
    print "GE852 $rid done.\n";
    unlockRecord($dbh,$rid);
    $sth_status->execute($req_id,$rid);
    $sth_status->finish;


}
#####################################################
sub lockRecord{
    my ($dbh,$rid,$timeout_min)=@_;
    
    my $sql_rec_lock = $dbh->prepare(<<_SQL_);
update  opl_marcRecord
set     lockExpire = now() + interval $timeout_min MINUTE 
where   rid = ?
_SQL_
    
    $sql_rec_lock->execute($rid) || return 0;
    $sql_rec_lock->finish;
    return 1;

}
#----------------------------------------------------
sub unlockRecord{
    my ($dbh,$rid)=@_;
    my $sql_rec_unlock = $dbh->prepare(<<_SQL_);
update  opl_marcRecord
set     lockExpire = now() 
where   rid = ?
_SQL_
    $sql_rec_unlock->execute($rid) || return 0;
    $sql_rec_unlock->finish;
    return 1;
    
}
#----------------------------------------------------
sub replaceStr{
    my ($str,$pos,$c)=@_;    
    $str=substr($str,0,$pos) . $c . substr($str,$pos + length($c));
    return $str;    
}
#----------------------------------------------------

my $recTypes={
'Book'              =>{leader=>[{pos=>6,val=>'a'},{pos=>7,val=>'m'}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Journal'           =>{leader=>[{pos=>6,val=>'a'},{pos=>7,val=>'s'}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Artifact'          =>{leader=>[{pos=>6,val=>'r'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Photos, Posters'    =>{leader=>[{pos=>6,val=>'k'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Music Cassette'    =>{leader=>[{pos=>6,val=>'j'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'CD Music'          =>{leader=>[{pos=>6,val=>' '},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>'s'},{pos=>1,val=>'d'}], cf008=>[{pos=>26,val=>' '}]},
'Electronic Media'  =>{leader=>[{pos=>6,val=>'m'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Movie'             =>{leader=>[{pos=>6,val=>'g'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Videocassette'     =>{leader=>[{pos=>6,val=>' '},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>'v'},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Internet'          =>{leader=>[{pos=>6,val=>'m'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>'j'}]},
'Compact Disc'      =>{leader=>[{pos=>6,val=>' '},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>'c'},{pos=>1,val=>'o'}], cf008=>[{pos=>26,val=>' '}]},
'Book on Tape'      =>{leader=>[{pos=>6,val=>'i'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Libray Kit'        =>{leader=>[{pos=>6,val=>'o'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Map'               =>{leader=>[{pos=>6,val=>'e'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]},
'Sheet Music'       =>{leader=>[{pos=>6,val=>'c'},{pos=>7,val=>' '}], cf007=>[{pos=>0,val=>' '},{pos=>1,val=>' '}], cf008=>[{pos=>26,val=>' '}]}
};

sub changeRecType(){
    my ($type,$leader,$ctrlFields)=@_;
    print "$type -- $leader \n";
    if($recTypes->{$type}){
        if($recTypes->{$type}->{'leader'}){
           my $a = $recTypes->{$type}->{'leader'};
           foreach my $l (@$a){                
               $leader=replaceStr($leader,$l->{'pos'},$l->{'val'});
           }
        }
        # case change needed  in control field 007
        if($recTypes->{$type}->{'cf007'}){
            my $i=0;
           for($i=0; $i<scalar(@$ctrlFields); $i++ ){
               if(@$ctrlFields[$i] =~ m/<controlfield tag=\"007\">(.+)<\/controlfield>/){
                   my $d =$1;
                   my $a = $recTypes->{$type}->{'cf007'};
                   foreach my $l (@$a){
                       $d =replaceStr($d,$l->{'pos'},$l->{'val'});
                   }
                  @$ctrlFields[$i] = "  <controlfield tag=\"007\">$d<\/controlfield>\n"  ;
                  last;
               }
           }
            # there is no control field 007, insert one. e.g. cd rom...
           if($i ==scalar(@$ctrlFields)){
               my $a = $recTypes->{$type}->{'cf007'};
               my $d=@$a[0]->{'val'} . @$a[1]->{'val'};
               for($i=0; $i<scalar(@$ctrlFields); $i++ ){
                    if(@$ctrlFields[$i] =~ m/<controlfield tag=\"(\d+)\">(.+)<\/controlfield>/){
                        if($1>'007'){
                            splice @$ctrlFields,$i,0,"  <controlfield tag=\"007\">$d<\/controlfield>\n";
                            last
                        }
                    }
               }
               # no other control field has tag > '007', append one
               if($i ==scalar(@$ctrlFields)){
                    push @$ctrlFields, "  <controlfield tag=\"007\">$d<\/controlfield>\n";
               }
           }
        }
        # case change needed  in control field 008. e.g. Internet
        if($recTypes->{$type}->{'cf008'}){
           for(my $i=0; $i<scalar($ctrlFields); $i++ ){
               if(@$ctrlFields[$i] =~ m/<controlfield tag=\"008\">(.+)<\/controlfield>/){
                   my $d =$1;
                   my $a = $recTypes->{$type}->{'cf008'};
                   foreach my $l (@$a){
                       $d =replaceStr($d,$l->{'pos'},$l->{'val'});
                   }
                  @$ctrlFields[$i] = "  <controlfield tag=\"008\">$d<\/controlfield>\n"  ;
                  last;
               }
           }
        }
    }
    
    return ($leader,@$ctrlFields);
}
#----------------------------------------------------

sub geDataField {
    my ($dbh,$req_id, $rid, $bcList,$action,$fieldTag, $o_code, $n_code,$o_data, $n_data) = @_;
    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 $syspref = tmpl_preference($dbh);
    my $timeout = $syspref->{'timeout'}; 
    my $timeout_min =  util_getTimeout_min($timeout);
    my $sth_status = $dbh->prepare(<<_SQL_);
update  opl_ge852record
set     status = 'done'
where   req_id = ? && rid= ?
_SQL_

      #set lock record in table opl_marcRecord
    lockRecord($dbh,$rid,$timeout_min) || return;
   
    my $content = '';
    my $line;
    
    
    my $tmp_data;
    # Read XML records
    open RECORD, "<$dir/$rid.xml";

    my $isModifyField = 0;  
    my $sfCode     = '0';
    my $skipLine  = 0;
    my $n_sfData="";
    my @sf=();    
    my $fieldExist = 0;
    while (<RECORD>) {
        $line = $_;
        if ($line =~ m/<datafield tag="$fieldTag" /) {
            $isModifyField = 1;
	        $fieldExist = 1;
        }
        elsif ($line =~ m/<\/datafield>/) {
            if($isModifyField){
                if($fieldTag eq '852'){
                    if(holdingHasBc($bcList,\@sf)){
                        @sf = geSubfields(\@sf,$action,$o_code, $n_code,$o_data, $n_data);
                    }                
                }
                else{
                    @sf = geSubfields(\@sf,$action,$o_code, $n_code,$o_data, $n_data);    
                }
                $content .= join("",@sf);
                undef (@sf);
            }
            $isModifyField = 0;
            $skipLine   = 0;
        }
        elsif ($line =~ m/<subfield code="([\-\+])">/) {
            next;
        }
        else{
            if($isModifyField){
               push @sf,$line;
               $skipLine=1; 
            }
        }
        if(!$skipLine){
          $content .= $line; 
        }
    } #END while (<RECORD>)
    close RECORD;
    
    if($fieldExist){    
         # call mxml_update...
    	my($barcodeNew, $barcodeDeleted);
    	$barcodeNew ='';
   	    $barcodeDeleted='';
        mxml_update($dbh, $rid, $content, $barcodeNew, $barcodeDeleted);
    }
    elsif($action eq 'append'){
	    geInsertDataField($dbh,$req_id, $rid, $fieldTag, $n_code, $n_data);
    }

    unlockRecord($dbh,$rid) || return;
    print "GE $rid done.\n";
    $sth_status->execute($req_id,$rid);
    $sth_status->finish;
   
}

####################################################################################

sub geInsertDataField {
    my ($dbh,$req_id, $rid, $nFieldTag, $n_code, $n_data) = @_;
    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 $syspref = tmpl_preference($dbh);
    my $timeout = $syspref->{'timeout'}; 
   
    my $content = '';
    my $line;
    my $done=0;
    my $field = "";  
    
    my $tmp_data;
    # Read XML records
    open RECORD, "<$dir/$rid.xml";
   
    while (<RECORD>) {
        $line = $_;
        if ($line =~ m/<datafield tag="(.+)" /) {
	        $field=$1;
            if(!$done && $field ge $nFieldTag){
		        $content .="  <datafield tag=\"$nFieldTag\" ind1=\" \" ind2=\" \">\n";
		        $content .="    <subfield code=\"$n_code\">$n_data</subfield>\n";
		        $content .="  </datafield>\n";
                $done=1;
	        }
        }
        $content .= $line; 
 
    } #END while (<RECORD>)

    close RECORD;
   # call mxml_update...
    my($barcodeNew, $barcodeDeleted);
    $barcodeNew ='';
    $barcodeDeleted='';
    mxml_update($dbh, $rid, $content, $barcodeNew, $barcodeDeleted);
   
}

#####################################################
sub mxml_processReplaceItemType {
    my ($dbh) = @_;

    my $sth = $dbh->prepare(<<_SQL_);
select  *
from    opl_itemTypeChange
where   status in ('waiting', 'processing')
_SQL_
    my $sth_rid = $dbh->prepare(<<_SQL_);
select  *
from    opl_item
where   typeId = ? &&
        dateImport < ?
group by rid
_SQL_
    my $sql_start = <<_SQL_;
update  opl_itemTypeChange
set     status = 'processing'
where   cid = ?
_SQL_
    my $sql_finish = <<_SQL_;
update  opl_itemTypeChange
set     status = 'done',
        dateFinish = now()
where   cid = ?
_SQL_

    $sth->execute();
    my @bindValue;
    while (my $request = $sth->fetchrow_hashref) {
        @bindValue = ($request->{'cid'});
        $dbh->do($sql_start, undef, @bindValue);

        $sth_rid->execute($request->{'oldTypeId'}, $request->{'dateRequest'});
        while (my $rec = $sth_rid->fetchrow_hashref) {
            print $rec->{'rid'}, "==" , $request->{'oldTypeId'},"==\n";
            mxml_replaceItemType($dbh, $rec->{'rid'}, $request->{'oldTypeId'}, $request->{'newTypeId'}, $request->{'cid'});
        }

        @bindValue = ($request->{'cid'});
        $dbh->do($sql_finish, undef, @bindValue);
    }
    $sth_rid->finish;
    $sth->finish;
}


#####################################################
sub mxml_replaceItemType {
    my ($dbh, $rid, $oldTypeId, $newTypeId, $cid) = @_;
    
    # Potential bug:
    my $oldTypeId_regex = $oldTypeId;
    $oldTypeId_regex =~ s/([\W])/\\$1/g;
    $oldTypeId_regex =" *" if($oldTypeId_regex eq '');

    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);

    # Read XML records
    open RECORD, "<$dir/$rid.xml";
    my $content = '';
    my $line;
    my $replacement_allowed = 0;
    my $sth = $dbh->prepare(<<_SQL_);
update  opl_itemInfo
set     sf852Data = ?
where   barcode = ? &&
        sf852Code = '3' &&
        sf852Data = ?
_SQL_
    while (<RECORD>) {
        $line = $_;

        if ($line =~ /<datafield tag="852" /) {
            $replacement_allowed = 1;
        }
        if ($line =~ /<\/datafield>/) {
            $replacement_allowed = 0;
        }

        if ($replacement_allowed) {
            $line =~ s/<subfield code="3">$oldTypeId_regex<\/subfield>/<subfield code="3">$newTypeId<\/subfield>/g;

            if ($line =~ m/<subfield code="p">(.+)<\/subfield>/) {
                $sth->execute($newTypeId, $1, $oldTypeId);
            }
        }

        $content .= $line;
    }
    $sth->finish;
    close RECORD;

    # Write XML records
    open  RECORD, ">$dir/$rid.xml";
    print RECORD $content;
    close RECORD;

    # Update record in database
    my $sql = <<_SQL_;
update  opl_item
set     typeId = ?
where   rid = ? &&
        typeId = ? && 
        barcode not regExp '^___'

_SQL_
    my @bindValue = ($newTypeId, $rid, $oldTypeId);
    my $count = $dbh->do($sql, undef, @bindValue);

    # Update process counter
    $sql = <<_SQL_;
update  opl_itemTypeChange
set     cReplaced = cReplaced + ?
where   cid = ?
_SQL_
    @bindValue = (($count)?$count:0, $cid);
    $dbh->do($sql, undef, @bindValue);
}
#####################################################



sub mxml_exportRecord {
    my ($zRoot, $zPort, $zDatabase, $rid) = @_;

    my $dir = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);

    if (! -r "$dir/$rid.xml") {
        return (undef, undef);
    }

    my ($line, $xml, $cFixed) = ('', '', 0);
    open REC, "<$dir/$rid.xml";
    while (<REC>) {
        $line = $_;
        if ($line =~ m/(<subfield code="\-">|<\?xml version=")/) {
            next;
        }

        $xml .= $line;
        if ($xml =~ s/(.|[\s\r\n])*(<record>(.|[\s\r\n])+<\/record>)(.|[\s\r\n])*/$2/) {
            last;
        }
    }
    close REC;

    ($xml, $cFixed) = mxml_fixXmlRecord($xml);
=item
    if ($cFixed) {
        my $dir = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);
        open REC, ">$dir/$rid.xml";
        print REC $xml;
        close REC;
    }
=cut    
    return ($xml, $cFixed);
}
#####################################################


sub mxml_exportHolding {
    my ($xml, $holding) = @_;

    my $selectedHolding = '';
    my $f852;
    while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w]">.*<\/subfield>)*[\s]*<\/datafield>)//) {
        $f852 = $1;
        my $f852_p = '';
        if ($f852 =~ m/<subfield code="p">(.*)<\/subfield>/) {
            $f852_p = $1;
            $f852_p =~ s/[\s]+$//;
        }
        foreach my $bc (keys %{$holding}) {
            #if ($f852 =~ m/<subfield code="p">$bc[\s]*<\/subfield>/) {
            if ($f852_p eq $bc) {
                $selectedHolding .= $f852;
            }
        }
    }

    $xml =~ s/([\s]*<\/record>)/$selectedHolding$1/;

    return $xml;
}
#####################################################


sub mxml_fixXmlRecord {
    my ($xml) = @_;
    my $LEADER = '00000cam  2200241   4500';

    my $cFixed = 0;

    # Fix leader
    $xml =~ m/<leader>(.*)<\/leader>/;
    my $leader = $1;

    if ($leader =~ m/[^a-z0-9 ]/ || length($leader) != 24) {
        $xml =~ s/<leader>.*<\/leader>/<leader>$LEADER<\/leader>/;
        $cFixed++;
    }

    # Replace empty indicator by blank
    if ($xml =~ m/<datafield tag="[\d]{3}" ind1=""/) {
        $xml =~ s/(<datafield tag="[\d]{3}" ind1=)""/$1" "/g;
        $cFixed++;
    }
    if ($xml =~ m/<datafield tag="[\d]{3}" ind1="[\w ]" ind2=""/) {
        $xml =~ s/(<datafield tag="[\d]{3}" ind1="[\w ]" ind2=)""/$1" "/g;
        $cFixed++;
    }
    # Replace empty subfield code by subfield code 'a'
    if ($xml =~ m/<subfield code="">/) {
        $xml =~ s/(<subfield code=)"">/$1"a">/g;
        $cFixed++;
    }

    if ($xml =~ m/<controlfield tag="[\d]{0-2}"/) {
        $xml =~ s/(<controlfield tag=)"[\d]{0-2}"/$1"009"/g;
        $cFixed++;
    }
    if ($xml =~ m/<controlfield tag="(0[1-9][\d]|[1-9][\d]{2})"/) {
        $xml =~ s/(<controlfield tag=)"(0[1-9][\d]|[1-9][\d]{2})"/$1"009"/g;
        $cFixed++;
    }

    if ($xml =~ m/<datafield tag="[\d]{0-2}"/) {
        $xml =~ s/(<datafield tag=)"[\d]{0-2}"/$1"999"/g;
        $cFixed++;
    }
    if ($xml =~ m/<datafield tag="00[\d]"/) {
        $xml =~ s/(<datafield tag=)"00[\d]"/$1"999"/g;
        $cFixed++;
    }

    # Should I fix those errors:
    #  - blank tag/indicator
    #  - wrong tag assigned to control/data field, eg. 002 for data field

    return ($xml, $cFixed);
}
#////////////////////////////////////////////////////////////////////////////
sub loadAttTable{
    my ($dbh) =@_;
    my $attTbl;
    my $sth = $dbh->prepare(<<_SQL_);
select attrval,tag,subfields
from opl_index_map where tag is not null && subfields is not null
_SQL_
    $sth->execute;
    while(my ($attval,$tag,$subfields) = $sth->fetchrow_array){
        push @{$attTbl->{$attval}},{tag=>$tag,subfields=>$subfields};
    }
    return $attTbl;
}
############################################################


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

    my $sth_arl = $dbh->prepare(<<_SQL_);
insert into opl_arl
set     rid = ?,
        tag = ?,
        ind1 = ?,
        code = ?,
        data1 = ?,
        data2 = ?
_SQL_

    return $sth_arl;
}
############################################################


sub get_arl_spec {
    my $arl_spec;
    $arl_spec->{'521'}->{'0'}->{'a'} = 'range data';
    $arl_spec->{'521'}->{'1'}->{'a'} = 'range data';
    $arl_spec->{'521'}->{'2'}->{'a'} = 'range data';
    $arl_spec->{'526'}->{'0'}->{'a'} = 'data';
    $arl_spec->{'526'}->{'0'}->{'b'} = 'range data';
    $arl_spec->{'526'}->{'0'}->{'c'} = 'range data';
    $arl_spec->{'526'}->{'0'}->{'d'} = 'data';

    return $arl_spec;
}
############################################################


sub add_arl {
    my ($sth_arl, $arl_data) = @_;

    my $rv = $sth_arl->execute(
        $arl_data->{'rid'},
        $arl_data->{'tag'},
        $arl_data->{'ind1'},
        $arl_data->{'code'},
        $arl_data->{'data1'},
        $arl_data->{'data2'}
    );

    return $rv;
}
############################################################


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

    $dbh->do("delete from opl_recordindex where rid = $rid");
    $dbh->do("delete from opl_arl where rid = $rid");

    my $rv;
    my ($fXml, $ind1Xml, $ind2Xml, $sfXml,
        $tag, $sflist, $sfCode, $sfdata);

    my $sth_arl = get_sth_arl($dbh);
    my $arl_spec = get_arl_spec();

    my $attTbl = loadAttTable($dbh);
    my $rec;
    my @val;
    push @val, $rid;
    my $read;
    foreach my $att (sort keys %$attTbl) {
        $rec->{$att} = "";
        $fXml = $xml;
        foreach my $tag_sf (@{$attTbl->{$att}}) {
            $tag    = $tag_sf->{'tag'};
            $sflist = $tag_sf->{'subfields'};
            if($tag =~ m/00[\d]/) {
                $fXml =~ s/[\s]*<controlfield tag="$tag">(.*)<\/controlfield>//;
                $rec->{$att} .= "$1\n";   
            }
            else{
                while ($fXml =~ s/[\s]*<datafield tag="$tag" ind1="([\d ])" ind2="([\d ])">(([\s]*<subfield code="[\w-]">.*<\/subfield>)*)[\s]*<\/datafield>//) 
                {   
                    $ind1Xml = $1;
                    $ind2Xml = $2;
                    $sfXml = $3;
                    while ($sfXml =~ s/[\s]*<subfield code="([\w|-])">(.*)<\/subfield>//) {
                        ($sfCode, $sfdata) = ($1, $2);

                        if ($tag eq '020' && $sfCode eq 'a') {
                            if ($sfdata =~ m/([\d\-]{9,}[xX]?)/) {
                                $sfdata = $1;
                                $sfdata =~ s/-//g;

                                if (length($sfdata) != 10 &&
                                    length($sfdata) != 13) {
                                    $sfdata = '';
                                }
                            }
                            else {
                                $sfdata = '';
                            }
                        }

                        if (!$read->{$tag}
                            && $arl_spec->{$tag}->{$ind1Xml}->{$sfCode}) {
                            my $arl_data;
                            $arl_data->{'rid'} = $rid;
                            $arl_data->{'tag'} = $tag;
                            $arl_data->{'ind1'} = $ind1Xml;
                            $arl_data->{'code'} = $sfCode;

                            if ($arl_spec->{$tag}->{$ind1Xml}->{$sfCode} 
                                    eq 'range data'
                                && $sfdata =~ 
                                    m/(K|[\d]+[\.]?[\d]*)-([\d]+[\.]?[\d]*)/) {
                                $arl_data->{'data1'} = $1;
                                $arl_data->{'data2'} = $2;
                            }
                            else {
                                $arl_data->{'data1'} = $sfdata;
                                $arl_data->{'data2'} = $sfdata;
                            }

                            add_arl($sth_arl, $arl_data);
                        }
                        
                        if ($sflist eq '' || $sflist =~ m/$sfCode/) {
                            if ($tag eq '020') {
                                $rec->{$att} .= "$sfdata ";
                            }
                            else {
                                $rec->{$att} .= "$sfdata\n";
                            }
                        }
                    }
                }

            }

            $read->{$tag} = 1;
        }

        push @val,$rec->{$att};        
    }

    $sth_arl->finish;
    
#    my $query_delete = "delete from opl_recordindex where rid= ?";
#    my $sth = $dbh->prepare($query_delete);
#    $sth->execute($rid);

    my $query_insert="insert into opl_recordindex set rid=? ";
    foreach my $attr (sort keys %$attTbl){
        $query_insert .= ", attr_$attr= ? ";
    }
    $rv = $dbh->do($query_insert, undef, @val);

}
############################################################


sub getXmlRecord {
    my ($zdbDir, $rid) = @_;

    my $record = '';
    if (! -f "$zdbDir/$rid.xml") {
        print "ERROR: $zdbDir/$rid.xml: not found.\n";
        return;
    }

    #print "$rid\n";
    #return;

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

    return $record;
}


1;