#!/usr/bin/perl -w
=item
command to update all the sites:

for i in `ls /etc/opals/conf/`; do cp -p /www/opals/script/checkMissingImport /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /tmp/urt; done

COMAND LINE" for i in mo_mhaes; do cp -p /www/opals/script/checkMissingImport /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /tmp/urt; done"

OR 

  COMAND LINE" for i in mo_mhaes; do cp -p /www/opals/script/checkMissingImport /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo perl /tmp/urt; done"

#### STEPS TO DO: #####################################
#    >>>>>  select all " good "   barcodes from tmp_importCheck and insert into tmp_notMismatch
    1/select barcode ,title_sql,title_file from tmp_importCheck;    
        1a/insert into tmp_notMismatch(barcode) values ('bc1'), ('bc2'), ('bc3') ......;
     1/a run again:COMAND LINE:"for i in mo_mhaes; do cp ....
     
    2/COPY   /tmp/missing__MY_SITE_.mrc   INTO   dione 
       This file contains bad records needs to reimport.....
        
    3/update opl_itemInfo i inner join tmp_importCheck t using(barcode) set i.barcode=concat(i.barcode,'_chk');
      update opl_item     i inner join tmp_importCheck t using(barcode) set i.barcode=concat(i.barcode,'_chk');
   
    4/IMPORT FILE: missing__MY_SITE_.mrc  

    5/update opl_item i inner join (select replace(barcode,'_chk','') barcode ,dateImport,modDate from opl_item where barcode regexp '_chk') t using(barcode) set i.dateImport=t.dateImport,i.modDate=t.modDate

    6/ delete from opl_item where barcode regexp '_chk';
       delete from opl_itemInfo where barcode regexp '_chk';

      



=cut
use lib '/www/opals/module';
use Opals::Context("/etc/opals/conf/_MY_SITE_");
#use Opals::Context("/etc/opals/conf/wswhe_qbye");

use strict;
use DBI;

use Opals::Marc::Record;

#use Getopt::Std;
use POSIX qw(
    ceil
);
#my %options = ();
#getopts("c:",\%options);
#my $configFile = $options{c};
#if (!$configFile || ! -f $configFile) {
#    print "Usage: $0 -c CONFIG_FILE\n";
#    exit 1;
#}
#
#my $config = loadConfig($configFile);
#my $dbh = makeConnection($config);
my $dbh = Opals::Context->dbh();
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...
use Opals::Search qw(
    srch_recordBrief
);

#my $zdbDir = $config->{'zRoot'} .'/'
#           . $config->{'zPort'} .'/'
#           . 'record' .'/'
#           . $config->{'zDatabase'};

my $zdbDir = Opals::Context->config('zRoot') .'/'
           . Opals::Context->config('zPort') .'/'
           . 'record' .'/'
           . Opals::Context->config('zDatabase');

my $indexDir;
my $attTbl;
my $query_insert ;

=item
my $sth = $dbh->prepare(<<_SQL_);

select  distinct rid
from    opl_item 
where  barcode not regexp '^\_\_\_' 
order by rid asc  
_SQL_
    $sth->execute;
    my $dir;
    open missingXmlRec, ">/tmp/missingXmlRec.txt";
    while (my ($rid) = $sth->fetchrow_array) {
        my $dir = ceil($rid/1000);
        if (! -f "$zdbDir/$dir/$rid.xml") {
            print missingXmlRec "$rid\n";
        }

   }
   close missingXmlRec;
   $sth->finish;


=cut
# Codes end.
createStatsTbl($dbh);
open MISSING ,">/tmp/missing__MY_SITE_.mrc";
for(my $i=2; $i<3;$i++){
    next if($i==8 || $i==9 || $i==16);
    print "/data/opals/import/_MY_SITE_/$i\n";
checkMissingRecord($dbh,"/data/opals/import/_MY_SITE_/$i");
}


exit 0;

sub createStatsTbl{
my ($dbh)=@_;
$dbh->do("DROP TABLE IF EXISTS tmp_importCheck");
$dbh->do(<<_SQL_);
CREATE TABLE IF NOT EXISTS tmp_notMismatch(
    barcode varchar(50) NOT NULL default '', 
    PRIMARY KEY  (`barcode`)
)ENGINE=MyISAM DEFAULT CHARSET=latin1
_SQL_


$dbh->do(<<_SQL_);
CREATE TABLE `tmp_importCheck` (
  `barcode` varchar(50) default NULL,
  `imported` int(1) unsigned default '1',
  `title_file` varchar(255) default NULL,
  `title_sql` varchar(255) default NULL,
  `recPos` int(6) unsigned default NULL,
  `xml` text,
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `index_1` (`barcode`,`imported`,`recPos`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

_SQL_

}
my $RID=0;
sub checkMissingRecord{
	my($dbh,$marcFile)=@_;
	my $marcData = '';

	open FILEIMPORT,"<$marcFile";
    while (<FILEIMPORT>) {
        $marcData .= $_;
    }
    close FILEIMPORT;
	my @record = split(/\x1D/, $marcData);
    my $count=0;
    my $sth=$dbh->prepare("select ?");
    foreach my $rec(@record){
        my $marc = MARC::File::USMARC->decode($rec, \&mc21_filter);
        my $title=$marc->field('245')->subfield('a');
        my $content = MARC::File::XML::record($marc);
        my $bcList=[];
        foreach my $f852 ($marc->field('852')) {
            my $bc=$f852->subfield('p');
            $bc =~ s/^T //g;
            #next if( isGood($dbh,$bc));
            push @$bcList ,$bc;
            #$f852->delete_subfield(code => "p");
            #$f852->add_subfields('p'=> $bc. "_pending");
        }
        $title=~ s/[\W]+$//g;
        $title=~ s/^ +| +$//g;
        $sth->execute($title);
        ($title)=$sth->fetchrow_array;
     
        my $marc_db=undef;
        my $title_db='';
        my $preRid=0;my $rid =0;
        #next if(@$bcList[0] !~ m/39087001106253|39087000531105|39087000531691/gi);
        my $printed=0;
        my $nHolding_importFile= scalar(@$bcList);
        my $nHolding_DB_MARC   = 0;
        my $nHolding_DB_SQL    = 0;
        foreach my $bc(@$bcList){ 
            $rid =getRid_regexp($dbh,$bc);
            $nHolding_DB_MARC++ if(isDeleted($dbh,$bc));
            if($rid !=$preRid){
                $marc_db=undef;
                $nHolding_DB_MARC=0;
                my $xml = getRecXml($zdbDir,$rid);
                if($xml ne ''){
                    $marc_db = Opals::Marc::Record::newFromXml($xml);
                    while ($xml =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code="[\w\-]">.*<\/subfield>)+[\s]*<\/datafield>)//) {
                        my $tmp852 =$1;
                        if ($tmp852 =~ m/<subfield code="p">(.*)<\/subfield>/){
                            my $barcode =$1;
                            $barcode=~ s/(\+|\$|\^|\\|\*)/\\$1/g;
                            $nHolding_DB_MARC++ if(_isInImportBarcodeList($barcode,$bcList));
                       }
                   }
                }
                #$nHolding_DB_MARC++ if(isDeleted($dbh,$bc));
                $preRid=$rid;
            }

            if(defined $marc_db){    
                if( $nHolding_importFile != $nHolding_DB_MARC) {
                    my $bc_holdings = getBarcodes_db($dbh,$rid);
                    $nHolding_DB_SQL =scalar(@$bc_holdings);
                    #CASE: number of holding in MARC XML not equal  number holdings in SQL DB
                    print "MARC<=>SQL:[$bc][$rid][$nHolding_importFile]<<<>>>[$nHolding_DB_MARC] <<<>>>[$nHolding_DB_SQL]\n" if($nHolding_importFile ne $nHolding_DB_MARC );
                }
            }
            else{
                #if(@$bcList[0] ne '' && !isDeleted($dbh,@$bcList[0]))
                if($bc ne '' && !isDeleted($dbh,$bc)){
                    #missing bc;
                    # print MISSING $marc->as_usmarc;
                    #   saveStats($dbh,$bc,0,$title,'',$count,$content);
                    print "missing $bc\n";
                }
            }
        }
        #last if($count ++ >20);
    }
 close MISSING;

}

#------------------------------------------------------------------------------
sub saveStats{
    my($dbh,$bc,$imported,$title_f,$title_db,$recPos,$recXml)=@_;
    my $sth=$dbh->prepare("insert into tmp_importCheck(barcode,imported,title_file,title_Sql,recPos,xml) values(?,?,?,?,?,?)");
    $sth->execute($bc,$imported,$title_f,$title_db,$recPos,$recXml);
    $sth->finish;

}

#------------------------------------------------------------------------------
sub isDeleted(){
    my($dbh,$bc)=@_;
    my $sth=$dbh->prepare("select rid from opl_item where barcode regexp ?");
    $sth->execute("___$bc" ."_");
    if(my ($rid) = $sth->fetchrow_array){
        return 1;
    }
    return 0;
}
#------------------------------------------------------------------------------

sub getRid{
    my($dbh,$bc)=@_;
    my $sth=$dbh->prepare("select rid from opl_item where barcode =?");
    $sth->execute($bc);
    if(my ($rid) = $sth->fetchrow_array){
	return $rid;
    }
    return 0;
	
}
#------------------------------------------------------------------------------
sub getRid_regexp{
    my($dbh,$bc)=@_;
    my $sth=$dbh->prepare("select min(rid) rid  from opl_item where barcode regexp ?");
    $sth->execute($bc);
    if(my ($rid) = $sth->fetchrow_array){
	    return $rid;
    }
    return 0;
	
}

#------------------------------------------------------------------------------
sub _isInImportBarcodeList{
    my($barcode,$bcList)=@_;
    foreach my $bc(@$bcList){ 
       return 1  if($bc eq $barcode);
    }
    return 0;
	
}

#------------------------------------------------------------------------------
sub getBarcodes_db{
    my($dbh,$rid)=@_;
    my $sth=$dbh->prepare("select distinct barcode from opl_item where rid =? && available=1");
    $sth->execute($rid);
    my $bcList;
    while (my ($bc) = $sth->fetchrow_array) {
        push @$bcList,$bc;
    }
    return $bcList;
	
}
#------------------------------------------------------------------------------
sub countHoldings{
    my($dbh,$bc)=@_;
    my $sth=$dbh->prepare("select count(distinct barcode) nHolding from opl_item where rid = (select rid from opl_item where  barcode regexp ? ) group by rid");
    $sth->execute($bc);
    if(my ($nHolding) = $sth->fetchrow_array){
	    return $nHolding;
    }
    return 0;


}
#------------------------------------------------------------------------------

sub getRecXml{
    my ($zdbDir,$rid) =@_;
    my $fPath=$zdbDir ."/".ceil($rid/1000) ."/$rid.xml";
    my $content="";
    if (-f $fPath){
        open REC_DEL, "<$fPath";
        while (<REC_DEL>) {
            $content .= $_;
        }
        close REC_DEL;
    }
    return $content;
}
#------------------------------------------------------------------------------

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

    return (length($tagno) == 3) &&
           ($tagno !~ m/[\D]/) &&
           ($tagno ne '005') &&
           ($tagno < 900 || $tagno == 940);
#           ($tagno ne '001') &&
}
#------------------------------------------------------------------------------

sub makeConnection {
    my ($config) = @_;
    if (!$config) {
        return;
    }
    my ($db_driver, $db_name, $db_host, $db_port, $db_user, $db_password);

    $db_driver   = $config->{'db_driver'} || 'mysql';
    $db_name     = $config->{'db_name'};
    $db_host     = $config->{'db_host'};
    $db_port     = $config->{'db_port'}   || '3306';
    $db_user     = $config->{'db_user'};
    $db_password = $config->{'db_password'};

    my $dsn = "dbi:$db_driver:$db_name:$db_host:$db_port";

    return DBI->connect($dsn, $db_user, $db_password);
}
############################################################


sub loadConfig {
    my ($configFile) = @_;
#    print "Enter the config filename of Opals: ";
#    $configFile = <STDIN>;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;
    
    return $config;
}
############################################################


#////////////////////////////////////////////////////////////////////////////