#!/usr/bin/perl -w

#SITE=ztest; INFILE=/tmp/textbook.mrc; \cp -fp /www/opals/script/util/tbk_marc_import /tmp/ttt; perl -pi -e "s/_SITECODE_/$SITE/" /tmp/ttt; /tmp/ttt $INFILE
#
#SITE=ztest
#INFILE=/tmp/textbook.mrc
#\cp -fp script/util/tbk_marc_import /tmp/ttt
#perl -pi -e "s/_SITECODE_/$SITE/" /tmp/ttt
#/tmp/ttt $INFILE

use lib "/www/opals/module";
use Opals::Context("/etc/opals/conf/_SITECODE_");
use Time::localtime;

use strict;
use MARC::Field;
use MARC::File::USMARC;


my $dbh = Opals::Context->dbh();;
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

my $tm = localtime;
my $dateToday = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday, $tm->hour, $tm->min, $tm->sec);

$| = 1;
# Codes start...

print $ARGV[0], "\n";
my $marcfile = MARC::File::USMARC->in($ARGV[0]);
my $i = 0;


while (my $record = $marcfile->next()) {
    my $rid = tb_record_idGen($dbh);
    tb_record_add($dbh, $rid, '005', $dateToday);
    tb_record_add($dbh, $rid, '010_a', $record->subfield('010',"a"));
    foreach my $f020 ($record->field('020')) {
        tb_record_add($dbh, $rid, '020_a', $f020->subfield("a"));
    }
    tb_record_add($dbh, $rid, '022_a', $record->subfield("a"));
    foreach my $f100 ($record->field('100')) {
        tb_record_add($dbh, $rid, '100_a', $f100->subfield("a"));
    }
    foreach my $f110 ($record->field('110')) {
        tb_record_add($dbh, $rid, '110_a', $f110->subfield("a"));
    }
    tb_record_add($dbh, $rid, '245_a', $record->subfield('245',"a"));
    tb_record_add($dbh, $rid, '245_b', $record->subfield('245',"b"));
    tb_record_add($dbh, $rid, '250_a', $record->subfield('250',"a"));
    tb_record_add($dbh, $rid, '260_a', $record->subfield('260',"a"));
    tb_record_add($dbh, $rid, '260_b', $record->subfield('260',"b"));
    tb_record_add($dbh, $rid, '260_c', $record->subfield('260',"c"));
    tb_record_add($dbh, $rid, '300_a', $record->subfield('300',"a"));
    tb_record_add($dbh, $rid, '490_a', $record->subfield('490',"a"));
    tb_record_add($dbh, $rid, '490_v', $record->subfield('490',"v"));
    tb_record_add($dbh, $rid, '500_a', $record->subfield('500',"a"));
    tb_record_add($dbh, $rid, '505_g', $record->subfield('505',"g"));
    tb_record_add($dbh, $rid, '520_a', $record->subfield('520',"a"));
    tb_record_add($dbh, $rid, '521_a', $record->subfield('521',"a"));
    foreach my $f650 ($record->field('650')) {
        tb_record_add($dbh, $rid, '650_a', $f650->subfield("a"));
    }
    foreach my $f856 ($record->field('856')) {
        tb_record_add($dbh, $rid, '856_u', $f856->subfield("u"));
    }
    my $emptyStr = "";
    my ($classno, $data);
    foreach my $f ($record->field('852')) {
       
=item
        if (! $f->subfield('p')){
            
            next;
        }
=cut

        $classno = '';
        foreach my $code ('k', 'h', 'i', 'm') {
            $data = $f->subfield($code);
            $classno .= $data . ' ' if $data;
        }
        $classno =~ s/ +/ /g;
        $classno =~ s/(^ | $)//g;

        my $params = {
            rid         => $rid,
            barcode     => $f->subfield('p')? $f->subfield('p') : tb_maxBarcodeTmp($dbh),
            typeId      => 'TBK',
            lCode       => $f->subfield('b') ? $f->subfield('b') : "",
            price       => $f->subfield('9') ? $f->subfield('9') : "",
            classno     => $classno,
            acqDate     => $emptyStr,
            PONo        => $emptyStr,
            distributor => $emptyStr,
            regionCode  => $emptyStr,
            districtCode=> $emptyStr,
            buildingCode=> $emptyStr,
            importDate  => $dateToday,
        };
=item
        if (! tb_checkdup_bc($dbh,$f->subfield('p'))){
            tb_item_add($dbh,$params);
        }
=cut
        if (tb_checkdup_bc($dbh,$f->subfield('p'))){
            $params->{'barcode'} = tb_maxBarcodeDup($dbh, $f->subfield('p'));
        }
        tb_item_add($dbh,$params);
    }
} #end while
$marcfile->close();
# Codes end.

exit 0;

#add record 
sub tb_record_add{
    my ($dbh, $rid, $fId, $fVal) = @_;
    return -1 if ($rid eq '');
    my($sql, $sth, $id);
    $sql = "insert into tb_records set rid=?, fId=?, fVal=?";    
    $sth = $dbh->prepare($sql);
    my $result = $sth->execute($rid,$fId,$fVal);
    $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;

}
sub tb_item_add{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '');
    my ($sql, $sth, $id);
        $sql = <<_SQL_;
            insert into tb_items set 
                rid=?, 
                barcode=?, 
                available=1,
                typeId=?, 
                locationCode=?, 
                price=?,
                classNumber=?,
                acquisitionDate=?,
                PONumber = ?,
                distributor = ?,
                regionCode = ?,
                districtCode = ?,
                buildingCode = ?,
                importDate=?
_SQL_

        $sth = $dbh->prepare($sql);
        my @param = (
                    $params->{'rid'}, 
                    $params->{'barcode'}, 
                    $params->{'typeId'},
                    $params->{'lCode'},
                    $params->{'price'},
                    $params->{'classno'},
                    $params->{'acqDate'},
                    $params->{'PONo'},
                    $params->{'distributor'},
                    $params->{'regionCode'},
                    $params->{'districtCode'},
                    $params->{'buildingCode'}, 
                    $params->{'importDate'}
        );
        $sth->execute(@param) || die $params->{'rid'}, "\n";
        $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;
}

################################################################################
sub tb_record_idGen {
    
    my ($dbh) = @_;
    my $checkSum = `date +\%Y\%m\%d\%H\%M\%S\%N`;
    chomp $checkSum;

    my $sql = "insert into opl_idGen set type = 'textbook', checkSum=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($checkSum);
    $sql = "select id from opl_idGen where type = ? and checkSum=? ";
    $sth = $dbh->prepare($sql);
    $sth->execute('textbook',$checkSum);
    my ($id) = $sth->fetchrow_array();
    $sth->finish;
    return $id;
}

sub tb_checkdup_bc {
    my ($dbh, $bc) = @_;
    my $sth = $dbh->prepare("select barcode from tb_items where barcode = ?");
    $sth->execute($bc);
    my ($dup) = $sth->fetchrow_array();
    $sth->finish;
    if (!$dup){
        return 0;
    }
    return $dup;
}

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

    my $sth = $dbh->prepare(<<_STH_);
select  max(barcode)
from    tb_items
where   barcode regexp '^TMP_[0-9]+\$'
_STH_
    $sth->execute;
    my ($barcode_tmp) = $sth->fetchrow_array;
    $sth->finish;

    if ($barcode_tmp) {
        $barcode_tmp =~ s/TMP\_([\d]*)/$1/;
        $barcode_tmp =~ s/^0*//g;
    }
    $barcode_tmp = 0 unless ($barcode_tmp);
    $barcode_tmp++;
    $barcode_tmp = 'TMP_' . sprintf("%0.12d", $barcode_tmp);
    return $barcode_tmp;
}

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

    my $sql_bc = $barcode;
    $sql_bc =~ s/([*+?])/\\\\$1/g;
    $sql_bc .= '_';

    my ($maxDupBarcode) = $dbh->selectrow_array(<<_STH_);
select  max(barcode)
from    tb_items
where   barcode regexp '^DUP_$sql_bc'
_STH_

    if ($maxDupBarcode && $maxDupBarcode =~ s/([\d]{3})$//) {
        my $dupCount = $1;
        $dupCount =~ s/^0{1,2}//;
        $dupCount++;
        $dupCount = sprintf("%0.3d", $dupCount);

        $maxDupBarcode .= $dupCount;
    }
    else {
        $maxDupBarcode = 'DUP_'.$barcode.'_000';
    }

    return $maxDupBarcode;
}


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