#!/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=1; $i<2;$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="";
        if(defined $marc->field('245')){
            $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;
	#next if(@$bcList[0] !~ m/39087001106253|39087000531105|39087000531691/gi);
    my $printed=0;
        foreach my $bc(@$bcList){ 
		my $rid =getRid($dbh,$bc);
                if($rid !=$preRid){
			$marc_db=undef;
			$title_db='';
        		my $xml = getRecXml($zdbDir,$rid);
			if($xml ne ''){
		          $marc_db = Opals::Marc::Record::newFromXml($xml);
		          $title_db =$marc_db->field('245')->subfield('a');
		    	  $title_db=~ s/[\W]+$//g;
		    	  $title_db=~ s/^ +| +$//g;
			}
			$preRid=$rid;
		}

		if(defined $marc_db){    
		    if(lc($title) ne lc($title_db)) {
			print "[$bc][$title] <<<<<>>>>>> [$title_db]\n";

			print MISSING $marc->as_usmarc if(!$printed);
            $printed=1;
			saveStats($dbh,$bc,1,$title,$title_db,$count,$content);
		    }
		}
		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 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 isGood{
    my($dbh,$bc)=@_;
    my $sth=$dbh->prepare("select barcode from tmp_notMismatch where barcode = ?");
    $sth->execute($bc);
    if(my ($barcode) = $sth->fetchrow_array){
        return 1;
    }
#already check? avoid duplicate records
    my $sth=$dbh->prepare("select barcode from tmp_importCheck where barcode = ?");
    $sth->execute($bc);
    if(my ($barcode) = $sth->fetchrow_array){
        return 1;
    }

    return 0;

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

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;
}
############################################################


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