#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);
use Time::localtime;

use Opals::Marc21 qw(
    mc21_parseRecord
);

use Opals::Tb_Record qw(
    
    tb_defRecordIndex_getList
    tb_barcode_getMaxValue

    tb_record_setFlagUpdating
    tb_record_findByRId4Merge
    tb_record_delete
    
    tb_itemType_getList
    tb_item_setFlagUpdating
    tb_item_delete

    tb_barcode_getList
    tb_isbn_getList
    tb_title_getList

    tb_record_index_map_010i_900e
    
    tb_record_idGen
    tb_record_add
    tb_item_add

);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();

my ($permission, $cookie, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'txtbk/record/merge.tmpl',
        reqPermission   => 'tb_record_edit',
    }
);

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);
my $merge;
$merge->{'dbh'} = $dbh;
$merge->{'iid'} = $input->{'iid'};
$merge->{'mid'} = $input->{'mid'};
$merge->{'rid'} = $input->{'rid'};
$merge->{'op'}  = $input->{'op'};
$merge->{'pNum'}= $input->{'pNum'};
($merge->{'pNum'}) || ($merge->{'pNum'} = 1);
$merge->{'pSize'} = 20;

    if ($merge->{'iid'}) {
        $merge = getDefaultMergeSet($merge);
    }

    $merge = deleteMarcRecord($merge);
    $merge = loadDuplicate($merge);
    $merge = AddData($merge);

    if (($merge->{'op'} && $merge->{'op'} eq 'list') ||(!$merge->{'op'} && !$merge->{'mid'} )){
        $merge->{'op'} = 'list';
        my $pendingItemCount = getPendingItemCount($dbh, $merge->{'iid'});
        my $listDuplicate = getDuplicateList($dbh,$merge->{'iid'}, $merge->{'pNum'}, $merge->{'pSize'});
        my @rangedPageList = tmpl_rangedPageList($pendingItemCount, $merge->{'pNum'}, $merge->{'pSize'}, 10);
        $template->param(
            listDuplicate   =>  $listDuplicate,
            rangedPageList  => \@rangedPageList,
        );
    }
    else {
        my $marc = Opals::Marc::Record::newFromXml($merge->{'duplicate'}->{'content'});
        if (!$merge->{'iid'}){
            $merge->{'iid'} = $merge->{'duplicate'}->{'iid'};
        }
        my @newRec = ();
        my @newItemList = ();
        my $index_map = tb_record_index_map_010i_900e($dbh);
        
        my $isbn = $marc->subfield("020", "a") ;
        my $isbn_format = $isbn;
        
        if ($isbn_format && $isbn_format =~ m/([\d\-]{9,}[xX]?)/) {
            $isbn_format = $1;
            $isbn_format =~ s/[-]//g;
        }
        $merge = getDupRecordList($dbh,$isbn,$isbn_format, $merge);
        my ($existRec, $existItemList ) =  tb_record_findByRId4Merge($dbh, $merge->{'rid'});
        ($existRec, $existItemList )  = formatExistRecord2Display($index_map, $existRec, $existItemList);
        my ($newRec,$newItemList) = extractNewRecord2Display($index_map, $marc);
        $template->param(
             listMerge      => 1,
             newRec         => $newRec,
             newItemList    => $newItemList,
             existRec       => $existRec,
             existItemList  => $existItemList,
             mid            => $merge->{'mid'},
             rid            => $merge->{'rid'},
        );
        
        if (scalar(@{$merge->{'ridList'}}) > 0) {
            $template->param(
                ridList        => $merge->{'ridList'},
            );
        }
    }
    $template->param(
        iid => $merge->{'iid'},
        pNum => $merge->{'pNum'},
);

tmpl_write($dbh, $cgi, $cookie, $template);
#$dbh->disconnect();

#----------------------------------------------------------------------------


sub getDefaultMergeSet {
    my ($merge) = @_;
    my $iid = $merge->{'iid'};
    my $dbh = $merge->{'dbh'};
    if ($merge->{'iid'}){
        $merge->{'defaultValues'} = $dbh->selectrow_hashref(<<_STH_);
select sCode, lCode, itemType
from tb_marcImport where iid = $merge->{'iid'}
_STH_
    }
    return $merge;
}

sub loadDuplicate {

    my ($merge) = @_;
    if ($merge->{'op'} && $merge->{'op'} =~ m/^(next|prev)$/i)  {
            $merge = changeMerge($merge);    
    }
    if ($merge->{'op'} !~ m/^list$/i) {
        $merge->{'duplicate'} = $dbh->selectrow_hashref(<<_STH_);
select  * 
from    tb_marcDuplicate
where   mid = $merge->{'mid'}
_STH_
    }
    return $merge;

}

sub changeMerge {
    
    my ($merge) = @_;
    if ($merge->{'op'} && $merge->{'op'} =~ m/^(prev|next)$/i) {
        delete $merge->{'rid'};
        my $dbh = $merge->{'dbh'};
        my $sth;

        if ($merge->{'op'} =~ m/^prev$/i) {
            $sth = <<_STH_;
select  mid
from    tb_marcDuplicate
where   mid < $merge->{'mid'}
_STH_
            if ($merge->{'iid'}) {
                $sth .= <<_STH_;
&& iid = $merge->{'iid'}
_STH_
            }
            $sth .= <<_STH_;
order by mid desc
limit 1
_STH_
        }
        else {
            $sth = <<_STH_;
select  mid
from    tb_marcDuplicate
where   mid > $merge->{'mid'}
_STH_
            if ($merge->{'iid'}) {
                $sth .= <<_STH_;
&& iid = $merge->{'iid'}
_STH_
            }
            $sth .= <<_STH_;
order by mid asc
limit 1
_STH_
        }
        ($merge->{'mid'}) = $dbh->selectrow_array($sth);
        if (!$merge->{'mid'}) {
            $merge->{'op'} = 'list';
        }
    }
    return $merge;

}

sub deleteMarcRecord {
    my ($merge) = @_;

    if ($merge->{'op'} && $merge->{'op'} =~ m/^del$/i) {
        my $dbh = $merge->{'dbh'};
        $dbh->do(<<_STH_);
delete from tb_marcDuplicate
where mid = $merge->{'mid'}
_STH_
        delete $merge->{'rid'};
        $merge->{'op'} = 'next';
    }
    return $merge;
}

sub AddData {
    my ($merge) = @_;
    
    if ($merge->{'op'} && $merge->{'op'} =~ m/^(new|oton|ntoo)$/i) {
        if ($merge->{'op'} =~ m/^(oton|ntoo)$/i) {
            $merge = mergeRecords($merge);
        }
        else {
            $merge = addMARCRec2DB($merge);
        }

        $merge = deleteMarcRecord($merge);
        $merge = loadDuplicate($merge);
    }
    return $merge;
}

sub mergeRecords {
    my ($merge) = @_;
    my $dbh = $merge->{'dbh'};
    my $rid = $merge->{'rid'};
    my $marc = Opals::Marc::Record::newFromXml($merge->{'duplicate'}->{'content'});
    if ($merge->{'op'} =~ m/^oton$/i) {
        addHoldingO2N($dbh,$rid,$marc,$merge);
    }
    else{
        addHoldingN2O($dbh,$rid,$marc,$merge);
    }
    $dbh->do(<<_STH_);
update  tb_marcImport 
set     countMerged = countMerged + 1 
where   iid = $merge->{'iid'}
_STH_
    
    $merge->{'op'} = 'del';
    return $merge;
}


sub addMARCRec2DB {
    my ($merge) = @_;
    my $dbh = $merge->{'dbh'};
    if ($merge->{'op'} && $merge->{'op'} =~ m/^new$/i) {
        my $marc = Opals::Marc::Record::newFromXml($merge->{'duplicate'}->{'content'});
        my $index_map = tb_record_index_map_010i_900e($dbh);

        my $rid = tb_record_idGen($dbh,'textbook');
        tb_record_add($dbh, $rid, '005', $dateToday);
        foreach my $field (sort {$a <=> $b} keys %$index_map){
            my $tag = $index_map->{$field}->{'tag'};
            my $sf =  $index_map->{$field}->{'subfield'};
            my $fId = $tag . "_" . $sf;
            if ($index_map->{$field}->{'repeatable'} eq '1'){
                foreach my $fr ($marc->field($tag)){
                    tb_record_add($dbh,$rid,$fId,$fr->subfield($sf));
                }
            }
            else{
                tb_record_add($dbh,$rid,$fId,$marc->subfield($tag,$sf));
            }
        }#end for
        
        my $emptyStr = "";
        my ($classno, $data);
        foreach my $f ($marc->field('852')) {
            $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      => $merge->{'defaultValues'}->{'itemType'}? $merge->{'defaultValues'}->{'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  => '',
                districtCode=> $emptyStr,
                buildingCode=> '',
                importDate  => $dateToday,
            };
            tb_item_add($dbh,$params);
        }
    $dbh->do(<<_STH_);
update  tb_marcImport
set     countImported = countImported + 1
where   iid = $merge->{'iid'}
_STH_
    $merge->{'op'} = 'del';
    }
    return $merge;
}

sub addHoldingO2N {

    my ($dbh, $rid, $marc, $merge) = @_;

    my $index_map = tb_record_index_map_010i_900e($dbh);
    my $newrid = tb_record_idGen($dbh,'textbook');
    tb_record_add($dbh, $newrid, '005', $dateToday);
    
    foreach my $field (sort {$a <=> $b} keys %$index_map){
        my $tag = $index_map->{$field}->{'tag'};
        my $sf =  $index_map->{$field}->{'subfield'};
        my $fId = $tag . "_" . $sf;
        if ($index_map->{$field}->{'repeatable'} eq '1'){
            foreach my $fr ($marc->field($tag)){
                tb_record_add($dbh,$newrid,$fId,$fr->subfield($sf));
            }
        }
        else{
            tb_record_add($dbh,$newrid,$fId,$marc->subfield($tag,$sf));
        }
    }#end for
    my $emptyStr = "";
        my ($classno, $data);
        foreach my $f ($marc->field('852')) {
            $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         => $newrid,
                barcode     => $f->subfield('p'),
                typeId      => $merge->{'defaultValues'}->{'itemType'}? $merge->{'defaultValues'}->{'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  => $merge->{'defaultValues'}->{'sCode'} ? $merge->{'defaultValues'}->{'sCode'} : "",
                districtCode=> $emptyStr,
                buildingCode=> $merge->{'defaultValues'}->{'lCode'} ? $merge->{'defaultValues'}->{'lCode'} : "",
                importDate  => $dateToday,
            };
            tb_item_add($dbh,$params);
        }

    updateOldRecord_mergeHolding($dbh,$rid, $newrid);
    $dbh->do(<<_STH_);
update  tb_marcImport
set     countImported = countImported + 1
where   iid = $merge->{'iid'}
_STH_
   
}

sub updateOldRecord_mergeHolding {

    my ($dbh,$rid, $newrid) = @_;
    $dbh->do(<<_STH_);
update  tb_records
set     deleted =  1
where   rid = $rid
_STH_

$dbh->do(<<_STH_);
update  tb_items
set     
    barcode = concat("___", barcode),
    deleted =  1
where   rid = $rid
_STH_

    my $sql = "select * from tb_items where rid = ? && deleted  = 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);

    while( my $item = $sth->fetchrow_hashref){
        $item->{'barcode'} =~ s/^___//;
        my $params = {
            rid         => $newrid,
            barcode     => $item->{'barcode'},
            typeId      => $item->{'typeId'},
            lCode       => $item->{'locationCode'},
            price       => $item->{'price'},
            classno     => $item->{'classNumber'},
            acqDate     => $item->{'acquisitionDate'},
            PONo        => $item->{'PONumber'},
            distributor => $item->{'distributor'},
            regionCode  => $item->{'regionCode'},
            districtCode=> $item->{'districtCode'},
            buildingCode=> $item->{'buildingCode'},
            importDate  => $item->{'importDate'},
        };
        tb_item_add($dbh,$params);
    }
}

sub addHoldingN2O{

    my ($dbh, $rid, $marc,$merge) = @_;
    my $emptyStr = "";
    my ($classno, $data);
    foreach my $f ($marc->field('852')) {
        $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      => $merge->{'defaultValues'}->{'itemType'} ?$merge->{'defaultValues'}->{'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  => $merge->{'defaultValues'}->{'sCode'} ? $merge->{'defaultValues'}->{'sCode'} : "",
            districtCode=> $emptyStr,
            buildingCode=> $merge->{'defaultValues'}->{'lCode'} ? $merge->{'defaultValues'}->{'lCode'} : "",
            importDate  => $dateToday,
        };
        tb_item_add($dbh,$params);
    }
}

sub getDupRecordList {

    my ($dbh, $isbn,$isbn_format, $merge) = @_;
    my @ridList = ();
    return $merge if ($isbn eq '');
    my $sql = "select rid from tb_records where fId = '020_a' && (fVal = ? || fVal = ?) && deleted <> '1'";
    my $sth = $dbh->prepare($sql);
    $sth->execute($isbn, $isbn_format);
    while (my $rid  = $sth->fetchrow_array){
        push @ridList, {
            rid => $rid,
            sel => ($merge->{'rid'} && $merge->{'rid'} == $rid)? 1 :0, 
        };
    }
    if (! $merge->{'rid'} && scalar(@ridList) > 0 && !$merge->{'rid'} ){
        $merge->{'rid'} = $ridList[0]->{'rid'};
    }
    $sth->finish;
    $merge->{'ridList'}= \@ridList;
    return $merge;
}

sub getPendingItemCount {

    my ($dbh, $iid) = @_;
    my $sql = <<_SQL_;
select  count(*)
from tb_marcDuplicate
_SQL_
    if ($iid){
    $sql .= <<_SQL_;
where iid = $iid
_SQL_
    }
    my ($pendingItemCount) = $dbh->selectrow_array($sql);
    return $pendingItemCount;
}

sub getDuplicateList {
    
    my ($dbh,$iid, $pNum, $pSize) = @_;

    ($pNum && $pNum!~m/[\D]/) || ($pNum = 1);
    my $offset = ($pNum - 1) * $pSize;

    my $sql = "select * from tb_marcDuplicate ";
    if ($iid && $iid =~ m/^[\d]+$/ && $iid > 0){
        $sql .= " where iid = $iid";
    }
    $sql .= " order by mid asc limit $offset, $pSize";

    my $sth = $dbh->prepare($sql);
    my @dupList = ();
    $sth->execute();
    while ( my $rec = $sth->fetchrow_hashref) {
        push @dupList, {
            title  => getSubField($rec->{'content'}, "245" , "a"),
            author => getSubField($rec->{'content'}, "100" , "a"),
            isbn   => getSubField($rec->{'content'}, "020" , "a"),
            mid    => $rec->{'mid'},
        };
    }
    $sth->finish;
    return \@dupList;
}

sub getSubField {
    my ($xml, $tag, $sfcode) = @_;
    my $data;
    if ($xml =~ m/([\s]*<datafield tag="$tag" ind1="[\d ]" ind2="[\d ]">([\s]*<subfield code=".">.*<\/subfield>)+[\s]*<\/datafield>)/) {
        if ($1 =~ m/<subfield code="$sfcode">(.*)<\/subfield>/) {
            $data = $1;
        }
    }
    return $data;
}
sub formatExistRecord2Display {
    
    my ($idx_map, $record, $itmList) = @_;
    my $odd = 1;
    my @recField = ();
    my @itemList = ();

    foreach my $field (sort {$a <=> $b} keys %$idx_map){
        my @sfdata = ();
        my $tag = $idx_map->{$field}->{'tag'};
        my $sf =  $idx_map->{$field}->{'subfield'};
        my $label =  $idx_map->{$field}->{'label'};
        if ($idx_map->{$field}->{'repeatable'} eq '1'){
            foreach my $rec ($record->{$tag . '_' . $sf}->{'fVals'}){
                foreach my $r (@{$rec}){
                    push @sfdata , {
                        data => $r->{'fVal'},
                    };
                }
            }
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  =>  \@sfdata,
                odd     => $odd ^= 1,
            };
        }
        else{
            push @sfdata , {
                        data  => $record->{$tag . '_' . $sf}->{'fVal'},
                };
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  =>  \@sfdata,
                odd     => $odd ^= 1,
            };
        }
    }
    $odd = 1;
    foreach my $it(@$itmList){
        my $item = {
            barcode     => $it->{'barcode'},
            typeId      => $it->{'typeId'},
            locationCode=> $it->{'locationCode'},
            price       => $it->{'price'},
            classNo     => $it->{'classNo'},
            odd         => $odd ^= 1,
        };
        push @itemList, $item;
    }

    return (\@recField, \@itemList);

}


sub extractNewRecord2Display{
    
    my ($idx_map, $marc) = @_;
    my @recField = ();
    my @itemList = ();
    my $odd = 1;
    foreach my $field (sort {$a <=> $b} keys %$idx_map){
        my @sfdata = ();
        my $tag = $idx_map->{$field}->{'tag'};
        my $sf =  $idx_map->{$field}->{'subfield'};
        my $label =  $idx_map->{$field}->{'label'};
        if ($idx_map->{$field}->{'repeatable'} eq '1'){
            foreach my $fr ($marc->field($tag)){
                push @sfdata , {
                        data  => $fr->subfield($sf) 
                };
            }
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  =>  \@sfdata,
                odd     => $odd ^= 1,
            };
        }
        else{
            push @sfdata , {
                        data  => $marc->subfield($tag, $sf) 
                };
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  =>  \@sfdata,
                odd     => $odd ^= 1,
            };
        }
    }
    my ($classno, $data);
    $odd = 1;
    foreach my $f($marc->field('852')){
        $classno = '';
        foreach my $code ('k', 'h', 'i', 'm') {
            $data = $f->subfield($code);
            $classno .= $data . ' ' if $data;
        }
        $classno =~ s/ +/ /g;
        $classno =~ s/(^ | $)//g;
        my $item = {
            barcode     => $f->subfield('p'),
            typeId      => $f->subfield('3')? $f->subfield('3') : "",
            lCode       => $f->subfield('b') ? $f->subfield('b') : "",
            price       => $f->subfield('9') ? $f->subfield('9') : "",
            classNo     => $classno,
            odd     => $odd ^= 1,
        };
        push @itemList, $item;
    }
    my @sortedItemList = sort { length($a->{barcode}) <=> length($b->{barcode}) || $a->{barcode} cmp $b->{barcode}  } @itemList;

    return (\@recField, \@sortedItemList);
}