package Opals::Tb_Import;

require Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(

    tb_m21_import
    tb_csv_import

    tb_m21_getFileName

);
#    mc21_importMarcCollection
# Version number
$VERSION   = 0.01;

use utf8;
use strict;
use Encode;

use Time::localtime;

use  Opals::Date qw(
    date_f005
);
use Opals::Utility qw(
    util_formatBarcode
    util_removeChar
    util_filterMarcXml
);
use Opals::Utf8 qw(
    utf8_fromMarc8
    utf8_normalize
    utf8_encode
);

use Opals::Tb_Record qw(
    tb_record_idGen
    tb_record_add
    tb_item_add
    tb_isBarcodeExist
    tb_subfieldCode

    mapData2SubfieldCode

);

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


sub mc21_formatIsbn {
    my ($marc) = @_;
    my ($f020a_prefix, $isbn, $f020a_suffix);
    
    my $f020 = $marc->field('020');
    if ($f020) {
        my $f020a = $f020->subfield('a');
        if ($f020a =~ m/([\D^x^X]*)([\d][\d \-]{8,}[\dxX])([\D^x^X]*)/) {
            $f020a_prefix = $1;
            $isbn         = $2;
            $f020a_suffix = $3;

            $isbn =~ s/[- ]//g;
        }

        if ($isbn && $isbn =~ m/^[\dxX]{10}$/) {
            $f020a = $f020a_prefix . $isbn . $f020a_suffix;
            $f020->update(a => $f020a);
        }
        else{
            undef $isbn;
        }
    }

    return ($marc, $isbn);
}


sub tb_mc21_getFileName {
    my ($iid) = @_;
    my $db_name = Opals::Context->config('db_name');
    my $imex    = Opals::Context->config('imex');
    return  "$imex/import/$db_name/tb.$iid";

}



sub tb_m21_import {
    my ($dbh) = @_;
    my $config;
    my $import = tb_m21_getImport($dbh);
    my $iid    = $import->{'iid'};
    my $record = $import->{'record'};
    my $impUid = $import->{'uid'};
    my $addToNewItemList=$import->{'addToNewitemList'};
    my $itemType = $import->{'itemType'};
    my $sysCode = $import->{'sCode'};
    my $libCode = $import->{'lCode'};

    return unless $iid;

    my $enc = 'a';
    if ($import->{'encoding'} eq 'MARC-8') {
        $enc = ' ';
    }
    $config->{'zRoot'}      = Opals::Context->config('zRoot');
    $config->{'zPort'}      = Opals::Context->config('zPort');
    $config->{'zDatabase'}  = Opals::Context->config('zDatabase');
    $config->{'type'}       = Opals::Context->config('type');

    my $rid = 0;
    my $pending;
    my $sth_updateCountProcessed = $dbh->prepare(<<_STH_);
update  tb_marcImport
set     countProcessed = countProcessed + 1
where   iid = $iid
_STH_
    my $sth_updateCountImported = $dbh->prepare(<<_STH_);
update  tb_marcImport
set     countImported = countImported + 1,
        dateImport = now()
where   iid = $iid
_STH_
    # Importing records
    foreach my $rec (@$record) {
        $sth_updateCountProcessed->execute();
        substr($rec, 9, 1, $enc);
        my $marc = MARC::File::USMARC->decode($rec);
        my $isbn;
        
        if ($import->{'encoding'} eq 'MARC-8') {
            $marc = utf8_fromMarc8($marc);
        }
        else {
            $marc = utf8_normalize($marc);
        }

        if ($import->{'cMerge'}){
            ($marc,$pending) = tb_m21_addListDuplicate($dbh,$iid,$marc);
            next if $pending;
        }

        $rid = tb_record_idGen($dbh,'textbook');
        ($marc, $isbn) = mc21_formatIsbn($marc);
        tb_record_add($dbh, $rid, '005', $dateToday);
        tb_record_add($dbh, $rid, '010_a', $marc->subfield('010',"a"));
        foreach my $f020 ($marc->field('020')) {
            tb_record_add($dbh, $rid, '020_a', $f020->subfield("a"));
        }
        tb_record_add($dbh, $rid, '022_a', $marc->subfield('022',"a"));
        foreach my $f100 ($marc->field('100')) {
            tb_record_add($dbh, $rid, '100_a', $f100->subfield("a"));
        }
        foreach my $f110 ($marc->field('110')) {
            tb_record_add($dbh, $rid, '110_a', $f110->subfield("a"));
        }
        tb_record_add($dbh, $rid, '245_a', $marc->subfield('245',"a"));
        tb_record_add($dbh, $rid, '245_b', $marc->subfield('245',"b"));
        tb_record_add($dbh, $rid, '250_a', $marc->subfield('250',"a"));
        tb_record_add($dbh, $rid, '260_a', $marc->subfield('260',"a"));
        tb_record_add($dbh, $rid, '260_b', $marc->subfield('260',"b"));
        tb_record_add($dbh, $rid, '260_c', $marc->subfield('260',"c"));
        tb_record_add($dbh, $rid, '300_a', $marc->subfield('300',"a"));
        tb_record_add($dbh, $rid, '490_a', $marc->subfield('490',"a"));
        tb_record_add($dbh, $rid, '490_v', $marc->subfield('490',"v"));
        tb_record_add($dbh, $rid, '500_a', $marc->subfield('500',"a"));
        tb_record_add($dbh, $rid, '505_g', $marc->subfield('505',"g"));
        tb_record_add($dbh, $rid, '520_a', $marc->subfield('520',"a"));
        tb_record_add($dbh, $rid, '521_a', $marc->subfield('521',"a"));
        foreach my $f650 ($marc->field('650')) {
            tb_record_add($dbh, $rid, '650_a', $f650->subfield("a"));
        }
        foreach my $f856 ($marc->field('856')) {
            tb_record_add($dbh, $rid, '856_u', $f856->subfield("u"));
        }
        my $emptyStr = "";
        my ($classno, $data);
        foreach my $f ($marc->field('852')) {
            if (! $f->subfield('p')){
                next;
            }
            $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'),
                typeId      => $itemType ? $itemType : $f->subfield('3'),
                lCode       => $f->subfield('b') ? $f->subfield('b') : "",
                price       => $f->subfield('9') ? $f->subfield('9') : "",
                classno     => $classno,
                acqDate     => $emptyStr,
                PONo        => $emptyStr,
                distributor => $emptyStr,
                regionCode  => $sysCode,
                districtCode=> $emptyStr,
                buildingCode=> $libCode,
                importDate  => $dateToday,
            };
            tb_item_add($dbh,$params);
        }
        $sth_updateCountImported->execute();
    }
    $sth_updateCountProcessed->finish();
    $sth_updateCountImported->finish();
    if (scalar(@$record) + $import->{'countProcessed'} >= $import->{'countTotal'}) {
        $dbh->do("update tb_marcImport set status='done' where iid=$iid");
    }
}

 
sub tb_m21_getImport {
    my ($dbh) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select  *
from    tb_marcImport
where   status = 'accepted' &&
        countProcessed < countTotal
order by dateUpload asc
limit 1
_STH_
    my $rv = $sth->execute;
    my $import = $sth->fetchrow_hashref;
    $sth->finish;
    my $iid = $import->{'iid'};
    if ($iid) {
        my $marcData = '';
        my $db_name = Opals::Context->config('db_name');
        my $imex    = Opals::Context->config('imex');
        my $fileImport = tb_m21_getFileName($iid);
        open FILEIMPORT, $fileImport;
        while (<FILEIMPORT>) {
            $marcData .= $_;
        }
        close FILEIMPORT;
        my @record = split(/\x1D/, $marcData);
        my $countTotal = $import->{'countTotal'};
        if ($countTotal && 
            $countTotal =~ m/^[\d]+$/ && 
            $countTotal == scalar(@record)) {
            my @recordImport;
            my $first = $import->{'countProcessed'};
            my $last  = $first + 1000;
            $last = ($last < $countTotal) ? $last : $countTotal;
            for (my $i = $first; $i < $last; $i++) {
                $record[$i] =~ s/^[\D]*//;
                push @recordImport, "$record[$i]\x1D";
            }
            $sth = $dbh->prepare(<<_STH_);
select  sys.sid, sys.sCode, sys.sName,
        lib.lid, lib.lCode, lib.lName
from    opl_libSystem as sys, opl_library as lib
where   lid = ? &&
        sys.sCode = lib.sysCode
_STH_
            $sth->execute($import->{'lid'});
            $import->{'libInfo'} = $sth->fetchrow_hashref;
            $sth->finish;
            
            $import->{'record'} = \@recordImport;
        }
        return $import;
    }
    return;
}




sub tb_m21_getFileName {
    my ($iid) = @_;
    my $db_name = Opals::Context->config('db_name');
    my $imex    = Opals::Context->config('imex');
    return  "$imex/import/$db_name/tb.$iid";

}


sub tb_m21_addListDuplicate {
    my ($dbh, $iid, $marc) = @_;
    my ($isbn_format, $isbn);
    ($marc, $isbn_format) = mc21_formatIsbn($marc);
    $isbn = $marc->subfield("020", "a");
    my @recordList = (); 
    my $sql = " SELECT distinct (rid) from tb_records where (fVal = ? || fVal = ? ) && fId = ? && deleted <> '1' && fVal <> ''"; 
    my $sth = $dbh->prepare($sql);
    $sth->execute($isbn,$isbn_format , '020_a');
    while (my $rec = $sth->fetchrow_hashref){
        push @recordList, $rec;  
    }
    my $pending;
    if (scalar(@recordList) > 0) {
        my $xml = MARC::File::XML::record($marc);
        $xml = util_filterMarcXml($xml);
        my $sth_1 = $dbh->prepare(<<_STH_);
insert into tb_marcDuplicate
set iid     = ?,
    content = ?
_STH_
        $pending = $sth_1->execute($iid, $xml);
        $sth_1->finish;
    }
    $sth->finish;
    return ($marc, $pending);
}



sub tb_csv_import {
   
    my ($dbh, $importid) = @_;
    my $sth;
    if ($importid){
        $sth = $dbh->prepare(<<_STH_);
select  iid, mapHeader,data,sCode,lCode,itemType,noHolding
from    tb_csvImport
where   iid = ? && status  = 'accepted'
_STH_
    $sth->execute($importid);
    }
    else{
        $sth = $dbh->prepare(<<_STH_);
select  iid, mapHeader,data,sCode,lCode,itemType,noHolding
from    tb_csvImport
where   status = 'accepted' && (countProcessed < countTotal)
order by dateUpload asc
limit 1
_STH_
    $sth->execute();
    }

    my ($iid, $mapHeader,$data,$sCode,$lCode,$itemType,$noHolding) = $sth->fetchrow_array;
    return if (!$iid); 
    $data =~ s/\r\n/\n/g;
    my @csvData = split(/\n/,$data);
    my  @map = split(/,/,$mapHeader);
    my ($duplicate, $mid) = ("","") ;
    my ($curTitle, $curISBN) = ("","") ;
    my @subfieldCode = tb_subfieldCode();
    my $rid;
    for (my $i = 1; $i < scalar(@csvData); $i++){
        my $rec = $csvData[$i];
        my $record = mapData2SubfieldCode($rec, \@map); 
        if ( $record->{'245_a'} ne $curTitle && $record->{'245_a'} ne "") {
            $curTitle = $record->{'245_a'};
            $duplicate = checkDuplicateRecord($dbh,$record);
            if ($duplicate){
                $sth = $dbh->prepare("insert into tb_csvDuplicate set iid = $iid, content = ?");
                $rec .= "\n";
                $sth->execute($rec);
                $mid = $dbh->{'mysql_insertid'};
            }
            else{
                $rid = tb_record_idGen($dbh,'textbook');
                $curTitle = $record->{'245_a'};
                tb_record_add($dbh, $rid, '005', $dateToday);
                foreach my $header (@subfieldCode) {
                    if ( $header ne "barcode" &&  $header ne "price" &&  $header ne "itemtype"){
                        tb_record_add($dbh, $rid, $header, $record->{$header});
                    }
                }
                if (! $noHolding || $noHolding eq '0'){
                    my $params = {
                        rid         => $rid,
                        barcode     => $record->{'barcode'},
                        typeId      => $record->{'itemtype'} ? $record->{'itemtype'}: $itemType,
                        lCode       => "",
                        price       => $record->{'price'},
                        classno     => "",
                        acqDate     => "",
                        PONo        => "",
                        distributor => "",
                        regionCode  => $sCode,
                        districtCode=> "",
                        buildingCode=> $lCode,
                        importDate  => $dateToday,
                    };
                    tb_item_add($dbh,$params);
                }
                $dbh->do(<<_STH_);
update  tb_csvImport
set     countImported = countImported + 1
where   iid = $iid
_STH_
            }
            $dbh->do(<<_STH_);
update  tb_csvImport
set     countProcessed = countProcessed + 1
where   iid = $iid
_STH_
        }
        else{
            if ($duplicate){
                $sth = $dbh->prepare("update tb_csvDuplicate set content = CONCAT(COALESCE(content,''),?) where iid = $iid && mid = $mid ");
                $rec .= "\n";
                $sth->execute($rec);   }
            else{
                 my $params = {
                    rid         => $rid,
                    barcode     => $record->{'barcode'},
                    typeId      => $record->{'itemtype'} ? $record->{'itemtype'}: $itemType,
                    lCode       => "",
                    price       => $record->{'price'},
                    classno     => "",
                    acqDate     => "",
                    PONo        => "",
                    distributor => "",
                    regionCode  => $sCode,
                    districtCode=> "",
                    buildingCode=> $lCode,
                    importDate  => $dateToday,
                };
                tb_item_add($dbh,$params);}
        }
    } 
$dbh->do(<<_STH_);
update  tb_csvImport
set     status = 'done'
where   iid = $iid && (countProcessed = countTotal)
_STH_
    $sth->finish;
    
}

sub checkDuplicateRecord {
    
    my ($dbh,$record) = @_;

    my $isbn = $record->{'020_a'};
    if (!$isbn || $isbn eq ''){
        return 0;
    }
    
    my $sql = " SELECT count(distinct rid) from tb_records where fVal = ?  && fId = ? && deleted <> '1'"; 
    my $sth = $dbh->prepare($sql);
    $sth->execute($isbn,'020_a');
    my ($ret) = $sth->fetchrow_array;
    $sth->finish;
    if(!$ret){
        return 0;
    }
    return $ret; 

}