#!/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_findByRId
    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
    tb_subfieldCode 

);

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_csv.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 @sfList = tb_subfieldCode();

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 = deleteCSVRecord($merge);
    $merge = loadDuplicate($merge);
    $merge = addData($merge);
    if (($merge->{'op'} && $merge->{'op'} eq 'list') ||(!$merge->{'op'} && !$merge->{'mid'} ) ){
        my $pendingItemCount = getPendingItemCount($dbh, $merge->{'iid'});
        my $listDuplicate = getDuplicateList($dbh,$merge);
        my @rangedPageList = tmpl_rangedPageList($pendingItemCount, $merge->{'pNum'}, $merge->{'pSize'}, 10);
        $template->param(
            listDuplicate   =>  $listDuplicate,
            rangedPageList  => \@rangedPageList,
        );
    }
    else {
        my @newRec = ();
        my @newItemList = ();
        my $index_map = tb_record_index_map_010i_900e($dbh);
        my $dupRec = getDupRecordInfo($dbh, $merge->{'mid'},$index_map);
        my $isbn =  $dupRec->{'020_a'};
        my $title = $dupRec->{'245_a'};
        $isbn =~ s/(^ | $)//g;
        $merge = getDupRecordList($dbh, $isbn,$title,$merge);
        my ($existRec, $existItemList ) =  tb_record_findByRId4Merge($dbh, $merge->{'rid'});
        ($existRec, $existItemList )  = formatExistRecord2Display($index_map, $existRec, $existItemList);
        my ($newRec,$newItemList) = extractNewRecord2Display($index_map, $dupRec);
        $template->param(
             listMerge      => 1,
             existRec       => $existRec,
             existItemList  => $existItemList,
             newRec         => $newRec,
             newItemList    => $newItemList,
             mid            => $merge->{'mid'},
             rid            => $merge->{'rid'},
        );
        if ($merge->{'ridList'} && 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 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}){
                push @sfdata , {
                        data  => $rec->{$tag . '_' . $sf}->{'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, $record) = @_;
    my @recField = ();
    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'){
            push @sfdata, {
                    data=> $record->{$tag . "_" . $sf},
                };
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  =>  \@sfdata,
                odd     => $odd ^= 1,
            };
        }
        else{
            push @sfdata , {
                    data  => $record->{$tag . "_" . $sf},
                };
            push @recField , {
                label   => $label,
                tag     => $tag,
                subfield=> $sf,
                sfdata  => \@sfdata,
                odd     => $odd ^= 1, 
            };
        }
    }
    my ($classno, $data);
    $odd = 1;

    my $itemList = $record->{'itemList'};
    return (\@recField, $itemList);
}

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, mapHeader
from tb_csvImport 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'} && $merge->{'op'} !~ m/^list$/i) {
        $merge->{'duplicate'} = $dbh->selectrow_hashref(<<_STH_);
select  * 
from    tb_csvDuplicate
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_csvDuplicate
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_csvDuplicate
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 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 = addCSV2SQL($merge);
        }
        $merge = deleteCSVRecord($merge);
        $merge = loadDuplicate($merge);
    }
    return $merge;
}

sub mergeRecords {
    my ($merge) = @_;
    my $dbh = $merge->{'dbh'};
    my $rid = $merge->{'rid'};
    my $index_map = tb_record_index_map_010i_900e($dbh);
    my $rec = getDupRecordInfo($dbh, $merge->{'mid'},$index_map);
    if ($merge->{'op'} =~ m/^oton$/i) {
        addHoldingO2N($dbh,$rid,$rec,$merge);
    }
    else{
        addHoldingN2O($dbh,$rid,$rec,$merge);
    }
    $dbh->do(<<_STH_);
update  tb_csvImport 
set     countMerged = countMerged + 1 
where   iid = $merge->{'iid'}
_STH_
    $merge->{'op'} = 'del';
    return $merge;
}

sub addHoldingO2N{

    my ($dbh, $rid, $rec, $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 ( keys %{$index_map}){
        my $sfield = $index_map->{$field}->{'tag'} . "_" . $index_map->{$field}->{'subfield'};
        tb_record_add($dbh, $newrid, $sfield, $rec->{$sfield});
    }
    my $emptyStr = "";
    foreach my $item (@{$rec->{'itemList'}}){
        my $params = {
            rid         => $newrid,
            barcode     => $item->{'barcode'}? $item->{'barcode'} : tb_maxBarcodeTmp($dbh),
            typeId      => $item->{'typeId'} ? $item->{'typeId'} : "  ",
            lCode       => "",
            price       => "",
            classno     => $emptyStr,
            acqDate     => $emptyStr,
            PONo        => $emptyStr,
            distributor => $emptyStr,
            regionCode  => '',
            districtCode=> $emptyStr,
            buildingCode=> '',
            importDate  => $dateToday,
        };
        tb_item_add($dbh,$params);

    }
    updateOldRecord_mergeHolding($dbh,$rid, $newrid);
}

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'}? $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, $rec,$merge) = @_;
    my $emptyStr = "";
    my ($classno, $data);
    my $emptyStr = "";
    foreach my $item (@{$rec->{'itemList'}}){
        my $params = {
            rid         => $rid,
            barcode     => $item->{'barcode'},
            typeId      => $merge->{'defaultValues'}->{'itemType'} ? $merge->{'defaultValues'}->{'itemType'} : " ",
            lCode       => "",
            price       => "",
            classno     => $emptyStr,
            acqDate     => $emptyStr,
            PONo        => $emptyStr,
            distributor => $emptyStr,
            regionCode  => '',
            districtCode=> $emptyStr,
            buildingCode=> '',
            importDate  => $dateToday,
        };
        tb_item_add($dbh,$params);
    }
}

sub addCSV2SQL {
    my ($merge) =  @_;
    my $dbh = $merge->{'dbh'};
    if ($merge->{'op'} && $merge->{'op'} =~ m/^new$/i){
        my $index_map = tb_record_index_map_010i_900e($dbh);
        my $rec = getDupRecordInfo($dbh, $merge->{'mid'},$index_map);
        my $rid = tb_record_idGen($dbh,'textbook');
        tb_record_add($dbh, $rid, '005', $dateToday);
        foreach my $field ( keys %{$index_map}){
            my $sfield = $index_map->{$field}->{'tag'} . "_" . $index_map->{$field}->{'subfield'};
            tb_record_add($dbh, $rid, $sfield, $rec->{$sfield});
        }
        my $emptyStr = "";
        foreach my $item (@{$rec->{'itemList'}}){
            my $params = {
                rid         => $rid,
                barcode     => $item->{'barcode'},
                typeId      => $merge->{'defaultValues'}->{'itemType'},
                lCode       => "",
                price       => "",
                classno     => $emptyStr,
                acqDate     => $emptyStr,
                PONo        => $emptyStr,
                distributor => $emptyStr,
                regionCode  => '',
                districtCode=> $emptyStr,
                buildingCode=> '',
                importDate  => $dateToday,
            };
            tb_item_add($dbh,$params);
        }
    }
$dbh->do(<<_STH_);
update  tb_csvImport
set     countImported = countImported + 1
where   iid = $merge->{'iid'}
_STH_

    $merge->{'op'} = 'del';
    return $merge;
}


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

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

sub getDupRecordInfo {
 
    my ($dbh, $mid, $idx_map) = @_;
    my $sql = "select d.*, ";
    $sql .= " i.header, i.mapHeader, i.itemType from tb_csvDuplicate d ";
    $sql .= " inner join tb_csvImport i on d.iid = i.iid where mid = ? ";
    my $rec = $dbh->selectrow_hashref($sql, undef,$mid);
    my $data = $rec->{'content'};
    my @data = split(/\n/,$data);
    my @first = split(/,/,@data[0]);
    my $mapHeader = $rec->{'mapHeader'};
    my @mapHeader =  split (/,/,$mapHeader);
    my $record ;
    my $val = "";
    my $odd = 1;
    foreach my $field (sort {$a <=>  $b}  keys %{$idx_map}){
        my $sfield = $idx_map->{$field}->{'tag'} . "_" . $idx_map->{$field}->{'subfield'};
        for (my $i = 0; $i < scalar(@sfList); $i++){
            if ($sfList[$i] eq $sfield){
                 if ($mapHeader[$i] >= 0) {
                    $val = $first[$mapHeader[$i]];
                    $record->{$sfield} = $val;
                }
                else{
                    $record->{$sfield} = "";
                }
                last;
            }
        }
    }
    my @itemList = ();
    foreach my $holding (@data){
        my @holding = split(/,/,$holding);
         for (my $i = 0; $i < scalar(@sfList); $i++){
            if ($sfList[$i] eq 'barcode'){
                 $val = "";
                 if ($mapHeader[$i] >= 0) {
                    $val = $holding[$mapHeader[$i]];
                    push @itemList, {
                        barcode => $val,
                        typeId=> $rec->{'itemType'},
                        price => '',
                    };
                }
                last;
            }
        }
    }
    $record->{'itemList'} = \@itemList;
    return $record;
}

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_csvDuplicate
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_csvDuplicate
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 getDupRecordList {

    my ($dbh, $isbn,$title,$merge) = @_;
    my @ridList = ();
    my $val;
    return $merge if ($isbn eq '' && $title eq '');
    my $sql = "select rid from tb_records "; 
    if ($isbn) {
        $sql .= " where fId = '020_a' && fVal = ? && deleted <> 1";
        $val = $isbn;
        }
    else{
        $sql .= " where fId = '245_a' && fVal = ? && deleted <> 1";
        $val = $title;
    }
    my $sth = $dbh->prepare($sql);

    $sth->execute($val);

    while (my $rid  = $sth->fetchrow_array){
        push @ridList, {
            rid => $rid,
            sel => ($merge->{'rid'} && $merge->{'rid'} == $rid)? 1 :0, 
        };
    }
    if (scalar(@ridList) > 0 ){
        $merge->{'rid'} = $ridList[0]->{'rid'};
    }
    $sth->finish;
    $merge->{'ridList'}= \@ridList;
    return $merge;
}

sub getPendingItemCount {

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

sub getDuplicateList {
    
    my ($dbh,$merge) = @_;
    my $pNum    = $merge->{'pNum'};
    my $offset  = $merge->{'offset'};
    my $iid     = $merge->{'iid'};
    my $mHeader = $merge->{'defaultValues'}->{'mapHeader'};
    my $pSize   = $merge->{'pSize'};

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

    my $sql = "select * from tb_csvDuplicate ";
    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" , $mHeader),
            author => getSubField($rec->{'content'}, "100" , "a" , $mHeader),
            isbn   => getSubField($rec->{'content'}, "020" , "a" , $mHeader),
            mid    => $rec->{'mid'},
        };
    }
    $sth->finish;
    return \@dupList;
}

sub getSubField {
    my ($content, $tag, $sfcode, $mapHeader) = @_;
    my $val = "";
    my $idx = 0;
    my @content = split(/\n/, $content);
    my @data = split(/,/, $content[0]);
    my @mHeader =  split(/,/, $mapHeader);
    my $sfield = $tag . "_" . $sfcode;
    for (my $i = 0; $i < scalar(@sfList); $i++){
         if ($sfList[$i] eq $sfield){
            $idx = $i;
            last;
         }
    }
    if ($mHeader[$idx] >= 0) {
        $val = $data[$mHeader[$idx]];
    }
    return $val;
}