#!/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::Equipment qw(
    
    mapData2FieldCode
    eq_def_getList_brief
    eq_record_findByRId4Merge
    eq_defItem_getList
    eq_defRecord_getList
    eq_isBarcodeExist
    eq_record_add
    eq_item_add
    eq_record_idGen

);

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        => '/eqmnt/record/merge_csv.tmpl',
        reqPermission   => 'eq_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'};
my $selRId  = $input->{'rid'};

($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 $dupRec = getDupRecordInfo($dbh,$merge);
        $merge = getDupRecordList($dbh,$dupRec,$merge,$selRId);
        if (!$merge->{'iid'}) {
            $merge->{'iid'} = $dupRec->{'iid'};
        }
        my ($existRec,$existItems)= eq_record_findByRId4Merge($dbh,$merge->{'rid'});
        ($existRec,$existItems) = formatExistRecord2Display($existRec, $existItems);
        my ($newRec,$newItems) = extractNewRecord2Display($dbh,$dupRec);
        
        $template->param (
            listMerge       => 1,
            existRec        => $existRec,
            existItemList   => $existItems,
            newRec          => $newRec,
            newItemList     => $newItems,
            mid             => $merge->{'mid'} ,
            rid             => $merge->{'rid'},
            iid             => $merge->{'iid'} 
        ); 
        if ($merge->{'ridList'} && scalar(@{$merge->{'ridList'}}) > 0) {
            my $list    = _getDupRecordList($dbh,$merge->{'ridList'});
            $template->param(
                ridList     => $merge->{'ridList'},
                list        => $list,
            );
        }
    }
    $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  itemType, mapHeader from eq_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    eq_csvDuplicate
where   mid = $merge->{'mid'}
_STH_
    }
    return $merge;
}

sub getDuplicateList {

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

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

    my $sql = "select * from eq_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) {
        if ($iid != $rec->{'iid'}){
            $iid = $rec->{'iid'};
            $merge->{'iid'} = $iid;
            getDefaultMergeSet($merge);
            $mHeader = $merge->{'defaultValues'}->{'mapHeader'};
            @mHeader = split(',', $mHeader);
        }
        #my $record = mapData2FieldCode($dbh,$rec->{'content'},\@mHeader); 
        my $record = mapData2FieldCode($dbh,$rec->{'content'},$mHeader); 
        push @dupList, {
            title       => $record->{'eqName'} ,
            manufacture => $record->{'1'},
            model       => $record->{'3'},
            barcode     => $record->{'bc'},
            mid         => $rec->{'mid'},
        };
    }
    $merge->{'iid'} = "";
    $sth->finish;
    return \@dupList;
}

sub _getDupRecordList {
    my ($dbh, $ridList) = @_;
    my @retList = ();

    my $sql = "select r.rid,rname, max(case when f.fId=1 then fValue end) manufacture, max(case when f.fId=3 then fValue end) model 
        from eq_records r inner join eq_recordFields f using(rid) 
    where r.rid =? group by rid";
    my $sth = $dbh->prepare($sql);
    foreach my $rec ( @{$ridList}){
        $sth->execute($rec->{'rid'});
        while ( my $rec = $sth->fetchrow_hashref) {
            push @retList, $rec;
        }
    }
    return \@retList;

}
sub getDupRecordList {

    my ($dbh, $dupRec,$merge,$sRId) = @_;
    my @ridList = ();
    my $val;
    my $title = $dupRec->{'eqName'};
    my $manufacture = $dupRec->{'1'} || "";
    my $model   = $dupRec->{'3'} || "";
    return $merge if ($title eq '');
    #my $sql = "select rid from eq_records where rname = ? && deleted = '0'";
    my $sql = "select rid from eq_records r inner join eq_recordFields f using(rid) 
        inner join eq_recordFields ff using(rid)  
        where rname=? && deleted = '0' && (f.fId = '1' && f.fValue regexp ?) && 
            (ff.fId = '3' && ff.fValue regexp ?)  group by r.rid";
    if ($manufacture eq "" && $model eq ""){
        $sql = "select rid from eq_records r inner join eq_recordFields f using(rid) 
        inner join eq_recordFields ff using(rid)  
        where rname=? && deleted = '0' && (f.fId = '1' && f.fValue = ?) && 
            (ff.fId = '3' && ff.fValue = ?)  group by r.rid"
    }
    elsif ( $manufacture eq ""){
        $sql = "select rid from eq_records r inner join eq_recordFields f using(rid) 
        inner join eq_recordFields ff using(rid)  
        where rname=? && deleted = '0' && (f.fId = '1' && f.fValue = ?) && 
            (ff.fId = '3' && ff.fValue regexp ?)  group by r.rid"
    }
    elsif ($model eq ""){
        $sql = "select rid from eq_records r inner join eq_recordFields f using(rid) 
        inner join eq_recordFields ff using(rid)  
        where rname=? && deleted = '0' && (f.fId = '1' && f.fValue regexp ?) && 
            (ff.fId = '3' && ff.fValue = ?)  group by r.rid"
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute($title,$manufacture,$model);
    
    while (my $rid  = $sth->fetchrow_array){
        push @ridList, {
            rid  => $rid,
            sel => ($merge->{'rid'} && $merge->{'rid'} == $rid)? 1 :0, 
        };
    }

    my @sorted_ridList = sort {$a->{rid} <=> $b->{rid} }  @ridList;

    if (scalar(@sorted_ridList) > 0 && !$merge->{'rid'}  ){
        $merge->{'rid'} = $sorted_ridList[0]->{'rid'};
    }
    $sth->finish;
    $merge->{'ridList'}= \@sorted_ridList;
    return $merge;
}

sub addData {
    my ($merge) = @_;
    if ($merge->{'op'} && $merge->{'op'} =~ m/^(new|oton|ntoo)$/i) {
        getDefaultMergeSet($merge);
        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 $rec = getDupRecordInfo($dbh, $merge);
    if ($merge->{'op'} =~ m/^oton$/i) {
        addHoldingO2N($dbh,$rid,$rec,$merge);
    }
    else{
        addHoldingN2O($dbh,$rid,$rec,$merge);
    }
    $dbh->do(<<_STH_);
update  eq_csvImport 
set     countMerged = countMerged + 1 
where   iid = $merge->{'iid'}
_STH_
    $merge->{'op'} = 'del';

$dbh->do("update eq_csvImport set status = 'done' where iid = $merge->{'iid'} && (countMerged = countTotalHolding)");    

    return $merge;
}

sub addCSV2SQL {
    my ($merge) =  @_;
    my $dbh = $merge->{'dbh'};
    my $rid = $merge->{'rid'};
    my $rec = getDupRecordInfo($dbh, $merge);
    my $eqDefRecord = eq_defRecord_getList($dbh);
    my $eqDefItem = eq_defItem_getList($dbh);
    my $rid = eq_record_idGen($dbh);

    eq_record_add($dbh,{rid=>$rid, fValue=>$rec->{'eqName'},reqField=>1});
    foreach my $rf (@$eqDefRecord){
        eq_record_add($dbh,{rid=>$rid,fId=>$rf->{'id'},fValue=>$rec->{$rf->{'id'}}});
    }
    my $bc = $rec->{'bc'};
    $bc =  ($bc ne "")?$bc:"no_barcode_".$rid ;
    if (eq_isBarcodeExist($dbh,$bc)){
        $bc = $bc . "merge";
    }
    my $itemType = $merge->{'defaultValues'}->{'itemType'} ? $merge->{'defaultValues'}->{'itemType'} : " ";
    my $count = $dbh->selectrow_array("select max(iid)  from eq_items where rid=$rid");
    $count++;
    eq_item_add($dbh, {rid=>$rid,barcode=>$bc,typeId=>$itemType,available=>1,reqField=>1,iid=>$count});
    foreach my $if (@$eqDefItem){
        eq_item_add($dbh,{rid=>$rid,iid=>$count,sfId=>$if->{'id'},sfValue=>$rec->{$if->{'id'}}});
    }

$dbh->do(<<_STH_);
update  eq_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 eq_csvDuplicate
where mid = $merge->{'mid'}
_STH_
        delete $merge->{'rid'};
        $merge->{'op'} = 'next';
    }
    return $merge;
}

sub addHoldingN2O{

    my ($dbh, $rid, $rec,$merge) = @_;
    my $eqDefRecord = eq_defRecord_getList($dbh);
    my $eqDefItem = eq_defItem_getList($dbh);

    my $bc = $rec->{'bc'};
    $bc =  ($bc ne "")?$bc:"no_barcode_".$rid ;
    if (eq_isBarcodeExist($dbh,$bc)){
        $bc = $bc . "_merge";
    }
    #my $itemType = $merge->{'defaultValues'}->{'itemType'} ? $merge->{'defaultValues'}->{'itemType'} : " ";
    my $itemType = $rec->{'itemType'} ? $rec->{'itemType'} : ($merge->{'defaultValues'}->{'itemType'})?$merge->{'defaultValues'}->{'itemType'}:"";
    my $count = $dbh->selectrow_array("select max(iid)  from eq_items where rid=$rid");
    $count++;
    eq_item_add($dbh, {rid=>$rid,barcode=>$bc,typeId=>$itemType,available=>1,reqField=>1,iid=>$count});
    foreach my $if (@$eqDefItem){
        eq_item_add($dbh,{rid=>$rid,iid=>$count,sfId=>$if->{'id'},sfValue=>$rec->{$if->{'id'}}});
    }
    $dbh->do("update eq_records set indexed = '0' where rid=$rid && indexed='1'");
}

sub addHoldingO2N {
    
    my ($dbh, $rid, $rec,$merge) = @_;
    my $eqDefRecord = eq_defRecord_getList($dbh);
    my $eqDefItem = eq_defItem_getList($dbh);
    my $newrid = eq_record_idGen($dbh);
    eq_record_add($dbh,{rid=>$newrid, fValue=>$rec->{'eqName'},reqField=>1});
    foreach my $rf (@$eqDefRecord){
        eq_record_add($dbh,{rid=>$newrid,fId=>$rf->{'id'},fValue=>$rec->{$rf->{'id'}}});
    }
    my $bc = $rec->{'bc'};
    $bc =  ($bc ne "")?$bc:"no_barcode_".$newrid ;
    if (eq_isBarcodeExist($dbh,$bc)){
        $bc = $bc . "_merge";
    }
    my $itemType = $merge->{'defaultValues'}->{'itemType'} ? $merge->{'defaultValues'}->{'itemType'} : " ";
    my $count = $dbh->selectrow_array("select max(iid)  from eq_items where rid=$rid");
    $count++;
    eq_item_add($dbh, {rid=>$newrid,barcode=>$bc,typeId=>$itemType,available=>1,reqField=>1,iid=>$count});
    foreach my $if (@$eqDefItem){
        eq_item_add($dbh,{rid=>$newrid,iid=>$count,sfId=>$if->{'id'},sfValue=>$rec->{$if->{'id'}}});
    }
    updateOldRecord_mergeHolding($dbh,$rid, $newrid);
    $dbh->do("update eq_records set indexed = '0' where rid=$rid && indexed='1'");
}

sub updateOldRecord_mergeHolding {

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

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

my $sql = "select * from eq_items  where rid = ? && deleted  = '1' ";
my $sth = $dbh->prepare($sql);
   $sth->execute($rid);
    while( my $item = $sth->fetchrow_hashref){
        $item->{'barcode'} =~ s/^___//;
        eq_item_add($dbh, { rid=>$newrid,
                        barcode=>$item->{'barcode'},
                        typeId=>$item->{'typeId'},
                        available=>1,
                        reqField=>1,
                        iid=>$item->{'iid'}});
    }
$dbh->do("update eq_records set indexed = '0' where rid=$rid && indexed='1'");

$dbh->do(<<_STH_);
    insert into eq_recordFieldsArchive  select * from eq_recordFields where rid=$rid;
_STH_

$dbh->do(<<_STH_);
    delete * from eq_recordFields where rid=$rid;
_STH_

$dbh->do(<<_STH_);
    insert into eq_itemFieldsArchive select * from eq_itemFields where rid=$rid;
_STH_

$dbh->do(<<_STH_);
    update eq_itemFields set rid = $newrid where rid=$rid;
_STH_

}

sub getDupRecordInfo {

    my ($dbh,$merge)  = @_;
    my $sql = <<_SQL_;
select d.*, i.header, i.mapHeader, i.itemType 
from    eq_csvDuplicate d
    inner join eq_csvImport i using(iid)
where d.mid  =?
_SQL_

    my $rec = $dbh->selectrow_hashref($sql,undef,$merge->{'mid'});
    my $data = $rec->{'content'};
    my @data = split(/\n/,$data);
    my $first = @data[0];
    my $mapHeader = $rec->{'mapHeader'};
    #my @mapHeader = split(/,/,$mapHeader);
    #my $record = mapData2FieldCode($dbh,$first,\@mapHeader);
    my $record = mapData2FieldCode($dbh,$first,$mapHeader);
    if (!$merge->{'iid'}){
        $merge->{'iid'} = $rec->{'iid'};
    }
    return $record;
}

sub formatExistRecord2Display{

    my ($record, $itemList) = @_;
    my @recFields = ();
    push @recFields, {
        fName   => 'Equipment Name',
        fId     => 'eqName',
        fVal    => $record->[0]->{'rname'}
    };
    push @recFields, {
        fName   => 'Category',
        fId     => 'category',
        fVal    => $record->[0]->{'category'}
    };


    foreach my $r (@{$record}){
        push @recFields, {
            fName   => $r->{'fName'},
            fId     => $r->{'fId'},,
            fVal    => $r->{'fValue'}
        }
    }
    @recFields = sort {$$a{'fId'} <=> $$b{'fId'}}  @recFields;
    my @itemFields = sort {$$a{'iid'} <=> $$b{'iid'}}  @{$itemList};
    return (\@recFields,\@itemFields);
}

sub extractNewRecord2Display {

    my ($dbh,$rec) = @_;
    my $eq_defRec = eq_def_getList_brief($dbh,"record");
    my $eq_defItem = eq_def_getList_brief($dbh,"item");
    my @recFields = ();
    push @recFields , {
        fName   => 'Equipment Name',
        fId     => 'eqName',
        fVal    => $rec->{'eqName'}
    };
    push @recFields, {
        fName   => 'Category',
        fId     => 'category',
        fVal    => $rec->{'category'}
    };

    while (my ($k,$v) = each (%{$eq_defRec})){
        push @recFields, {
            fName   => $v,
            fId     => $k,
            fVal    => $rec->{$k}
        }
    }
    my @itemFields = ();
    my @fields=();
    while (my ($k,$v) = each (%{$eq_defItem})){
        push @fields, {
            fName   => $v,
            fId     => $k,
            fVal    => $rec->{$k}
        }
    }
    @fields = sort {$$a{'fId'} <=> $$b{'fId'}} @fields;
    push @itemFields, {
        bc      => $rec->{'bc'},
        typeId  => $rec->{'itemType'},
        fields  => \@fields
    };
    @recFields = sort {$$a{'fId'} <=> $$b{'fId'}}  @recFields;
    return (\@recFields,\@itemFields );
}

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    eq_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    eq_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 getPendingItemCount {

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