#!/usr/bin/perl

#while read line; do FILE=`echo $line|sed -re 's/^.* in //'`; BC=`echo $line|sed -re "s/^.* by '//" -e "s/' in .*$//"`; grep -i $BC $FILE > /dev/null; if [ "$?" -ne "0" ]; then echo $BC is not in $FILE; fi; done < /tmp/xaaa_xag3.log

use strict;
use DBI;
use Getopt::Std;
use MARC::Field;
use MARC::File::USMARC;
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);
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...

restore_checkChar_fromImportFile($dbh, $config);
restore_checkChar_fromSqlDb($dbh, $config);

# Codes end.

exit 0;
################################################################################


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


sub restore_checkChar_fromImportFile {
    my ($dbh, $conf) = @_;

    my $updateList = getUpdateList($dbh, $conf);
    updateBarcode($dbh, $conf, $updateList);
}
############################################################


sub getUpdateList {
    my ($dbh, $conf) = @_;

    my $sth = $dbh->prepare(<<_SQL_);
select  *
from    opl_marcImport
where   status = 'done'
order by dateUpload asc
_SQL_

    my $sth_bc = $dbh->prepare(<<_SQL_);
select  *
from    opl_item
where   barcode like ?
_SQL_

    $sth->execute() || return;

    my $imDir = $conf->{'imex'}.'/import/'.$conf->{'zDatabase'};

    my ($iid, $di, $du, $status);
    my $updateList;
    while (my $im = $sth->fetchrow_hashref) {
        $iid    = $im->{'iid'};
        $di     = $im->{'dateImport'};
        $du     = $im->{'dateUpload'};
        $status = $im->{'status'};

        if (-f "$imDir/$iid") {
            print "$iid\t$du\t$status\n";
            $updateList = checkFile($dbh, "$imDir/$iid", $sth_bc, $updateList);
        }
        else {
            print "$imDir/$iid: file not found.\n";
        }
    }
    $sth->finish;
    $sth_bc->finish;

    return $updateList;
}
############################################################


sub checkFile {
    my ($dbh, $file, $sth_bc, $updateList) = @_;

    my $bc;

    my $marcfile = MARC::File::USMARC->in($file);
    while (my $rec = $marcfile->next()) {
        foreach my $f852 ($rec->field('852')) {
            $bc = $f852->subfield('p');

            if ($bc) {
                $bc = uc($bc);
                $updateList = check_barcode($dbh, $bc, $sth_bc, $updateList);
            }
        }
    }

    return $updateList;
}
############################################################


sub check_barcode {
    my ($dbh, $barcode, $sth_bc, $updateList) = @_;

    #if ($barcode =~ m/^[\w\-]+[ ]?$/) {
    if ($barcode =~ m/^[\w\-]+$/) {
        return $updateList;
    }

    $sth_bc->execute($barcode) || return;
    my $f852 = $sth_bc->fetchrow_hashref;
    if ($f852) {
        return $updateList;
    }

    $barcode =~ s/(^[\s]+|[\s]+$)//g;
    my $bc = $barcode;
    $bc =~ s/[^\w\-]//g;
    if ($barcode eq $bc) {
        return $updateList;
    }

    $sth_bc->execute($bc) || return;
    $f852 = $sth_bc->fetchrow_hashref;
    if ($f852) {
        my $rid = $f852->{'rid'};

        print "$rid\t'$bc'\t'$barcode'\n";
        if ($updateList->{$rid}->{$bc}->{$barcode}) {
            $updateList->{$rid}->{$bc}->{$barcode}++;
        }
        else {
            $updateList->{$rid}->{$bc}->{$barcode} = 1;
        }
    }

    return $updateList;
}
############################################################


sub updateBarcode {
    my ($dbh, $conf, $updateList) = @_;

    my $log = '/tmp/'.$conf->{'db_name'}.'.log';

    my ($rec, $holding, $file, $replacement, $isUpdated);
    my @barcode;

    open LOG, ">>$log";
    foreach my $rid (sort keys %{$updateList}) {
        $rec = $updateList->{$rid};
        $file = $conf->{'zRoot'} . '/210/record/'
              . $conf->{'zDatabase'} . '/'
              . ceil($rid/1000) . '/'
              . $rid . '.xml';
        print "$rid:\n$file\n";

        foreach my $bc (sort keys %{$rec}) {
            $holding = $rec->{$bc};
            print "\t'$bc'\n";
        
            @barcode = keys %{$holding};
            if (scalar(@barcode) == 1) {
                print "\t\t'",$barcode[0],"' => ", $holding->{$barcode[0]}, "\n";
            }
            else {
                print "There are more than one replacement bar code:\n"
            }
        }

        if (-r $file) {
            $isUpdated = 0;

            open FILE, "<$file";
            my $xml = '';
            my $line;
            my $in852 = 0;
            while (<FILE>) {
                $line = $_;
                if ($line =~ m/<datafield tag="852" ind1="/) {
                    $in852 = 1;
                }
                if ($in852 == 1 && $line =~ m/<\/datafield>/) {
                    $in852 = 0;
                }

                if ($in852 == 1 && $line =~ m/<subfield code="p">/) {
                    foreach my $bc (sort keys %{$rec}) {
                        $holding = $rec->{$bc};
            
                        @barcode = keys %{$holding};
                        if (scalar(@barcode) == 1) {
                            $replacement = $barcode[0];
                            if ($line =~ s/<subfield code="p">$bc<\/subfield>/<subfield code="p">$replacement<\/subfield>/i) {
                                print LOG "Replace '$bc' by '$replacement' in $file\n";
                                $isUpdated = 1;
                                updateBarcode_inSqlDb($dbh, $bc, $replacement);
                            }
                        }
                        else {
                            print "There are more than one replacement bar code:\n"
                        }
                    }
                }

                $xml .= $line;
            }
            close FILE;

            #print "$xml\n";

            if ($isUpdated == 1) {
#                $file .= '.new';
                open FILE, ">$file";
                print FILE $xml;
                close FILE;
            }
        }

    }
    close LOG;
}
############################################################


sub updateBarcode_inSqlDb {
    my ($dbh, $bc, $barcode) = @_;

    foreach my $table qw(found ge852record historyItem hold inventory item itemInfo itemstatus loan sessionVar) {
        #print "Replace '$bc' by '$barcode' in opl_$table\n";
        $dbh->do(<<_SQL_);
update  opl_$table
set     barcode = '$barcode'
where   barcode like '$bc'
_SQL_
    }
}
############################################################


sub restore_checkChar_fromSqlDb {
    my ($dbh, $conf) = @_;

    my $updateList = getUpdateList_fromSqlDb($dbh);
    updateBarcode($dbh, $conf, $updateList);
}
############################################################


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

    my $sth = $dbh->prepare(<<_SQL_);
select  *
from    opl_item
where   barcode not regexp '[[:alnum:]]\$';
_SQL_

    my $sth_check = $dbh->prepare(<<_SQL_);
select  *
from    opl_item
where   barcode like ?
_SQL_

    $sth->execute() || return;

    my ($rid, $bc, $barcode, $duplicate);
    my $updateList;
    while (my $item = $sth->fetchrow_hashref) {
        $rid     = $item->{'rid'};
        $barcode = $item->{'barcode'};

        $bc = substr($barcode, 0, -1);
        $sth_check->execute($bc);
        $duplicate = $sth_check->fetchrow_hashref;
        if (!$duplicate) {
            $updateList->{$rid}->{$bc}->{$barcode} = 1;
        }
    }

    $sth->finish;
    $sth_check->finish;

    return $updateList;
}