package Opals::Eq_Import;

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

    eq_csvImport
    eq_csv_import

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

use utf8;
use strict;
use Encode;
use JSON;

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::Equipment qw(
    
    eq_fieldCodes
    mapData2FieldCode
    eq_defItem_getList
    eq_defRecord_getList
    eq_record_add
    eq_item_add
    eq_isBarcodeExist
    eq_item_findByBarcode
    eq_record_idGen

);

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 eq_csv_import {
    my ($dbh, $importid) = @_;
    my $sth;
    if ($importid){
        $sth = $dbh->prepare(<<_STH_);
select  iid, mapHeader,data,sCode,lCode,itemType,mode
from    eq_csvImport
where   iid = ? && status='accepted'
_STH_
    $sth->execute($importid);
    }
    else{
        $sth = $dbh->prepare(<<_STH_);
select  iid, mapHeader,data,sCode,lCode,itemType,mode
from    eq_csvImport
where   status = 'accepted' && (countProcessed < countTotalHolding)
order by dateUpload asc
limit 1
_STH_
    $sth->execute();
    }
    my ($iid, $mapHeader,$data,$sCode,$lCode,$itemType,$mode) = $sth->fetchrow_array;
    return if (!$iid); 
    $dbh->do("update eq_csvImport set status='processed' where iid = $iid");
    my $eqDefRecord = eq_defRecord_getList($dbh);
    my $eqDefItem = eq_defItem_getList($dbh);
    my $dupNum = 1;
    $data =~ s/\x0d[\x0a]?/\n/g;
    $data =~ s/\r/\n/g;
    $data =~ s/\n\n/\n/g;
    my @csvData = split(/\n/,$data);
    #my  @map = split(/,/,$mapHeader);
    my ($eqName,$curEqName,$manufacture,$curManufacture,$model,$curModel) = ("","","","","","");
    my ($duplicate, $mid) = ("","") ;
    my ($rid,$itmId)  =  (0, 1);
    my ($bc, $category,$itType ) = ("","","");
    my ($bldCode,$bldName,$bldAddress,$bldCity,$bldState,$bldCountry);
    my $categories = getCategoryList($dbh);
    open debug, ">/tmp/debugTEST";
    for (my $i = 1; $i < scalar(@csvData); $i++){
        my $rec = $csvData[$i];
        print debug "rec:$rec\n";
        my $record = _mapData2FieldCode($dbh,$rec, $mapHeader);
        print debug "mapHeader: $mapHeader\n";
        print debug "record:$record\n";
        foreach my $k(keys %$record){
            print debug "$k=>$record->{$k}\n";
        }
        $record->{'eqName'} = ($record->{'1'} . " " . $record->{'3'} ) if (!$record->{'eqName'});
        if (!$record->{'eqName'} || $record->{'eqName'} eq ""){
            $record->{'eqName'} = "No Name";
        }
        $eqName = $record->{'eqName'};
        $manufacture = $record->{'1'};
        $model = $record->{'3'};
        $bldCode = $record->{'bldCode'};
        $bldName = $record->{'bldName'};
        $bldAddress = $record->{'bldAddress'};
        $bldCity = $record->{'bldCity'};
        $bldState = $record->{'bldState'};
        $bldCountry = $record->{'bldCountry'};
        my $category = $record->{'category'};
        my $catCode =  ($category ne "")?$categories->{$category}:"";
        $itType = ($record->{'itemType'})? $record->{'itemType'} : $itemType;
        if ($eqName ne ""){
            my $params = {
                    code    => $bldCode,
                    name    => $bldName,
                    address => $bldAddress,
                    city    => $bldCity,
                    state   => $bldState,
                    country => $bldCountry
                };
            if ($bldCode && $bldCode ne ""){
                updateBuildingInfo($dbh,$params);
            }
            if ($mode =~ m/new/) {   #each line is a record
                $dupNum = 1;
                $rid = eq_record_idGen($dbh);
                eq_record_add($dbh,{rid=>$rid, fValue=>$record->{'eqName'},category=>$catCode,reqField=>1});
                foreach my $rf (@$eqDefRecord){
                    eq_record_add($dbh,{rid=>$rid,fId=>$rf->{'id'},fValue=>$record->{$rf->{'id'}}});
                }
                $bc = $record->{'bc'};
                $bc =  ($bc ne "")?$bc:"TMP_BC".$rid ."_".$itmId;
                if (eq_isBarcodeExist($dbh,$bc)){
                    $bc = "DUP_".$bc."_".$dupNum;
                    $dupNum++;
                }
                eq_item_add($dbh, {rid=>$rid,barcode=>$bc,typeId=>$itType,available=>1,reqField=>1,iid=> $itmId});
                foreach my $if (@$eqDefItem){
                    eq_item_add($dbh,{rid=>$rid,iid=>$itmId,sfId=>$if->{'id'},sfValue=>$record->{$if->{'id'}}});
                }
                $dbh->do("update eq_csvImport set countTotal=countTotal+1 where iid=$iid && status='processed'");
                $dbh->do("update eq_csvImport set countImported=countImported+1 where iid=$iid && status='processed'");
                $dbh->do("update eq_csvImport set countProcessed=countProcessed+1 where iid=$iid && status='processed'");
            }
            elsif($mode =~ m/replace/){
                my $itemInfo;
                my $doReplace = "no";
                if (eq_isBarcodeExist($dbh,$record->{'bc'})){
                    $itemInfo = eq_item_findByBarcode($dbh,$record->{'bc'});
                    $doReplace = "yes";
                }
                if ($doReplace eq "no"){
                    #print debug "$i: bc", $record->{'bc'} , " not exists ...\n";
                }
                #next if ($doReplace eq "no");
                ##my $hasLoan = _eq_check_bc_hasLoan($dbh,$record->{'bc'});
                my $rid_old = ($itemInfo)? $itemInfo->{'rid'}:0;
                if ($rid_old && $rid_old > 0 && $doReplace eq "yes"){
                    _eq_archiveRecordItems($dbh,$rid_old);
                }
                $rid = eq_record_idGen($dbh);
                eq_record_add($dbh,{rid=>$rid, fValue=>$record->{'eqName'},category=>$catCode,reqField=>1});
                foreach my $rf (@$eqDefRecord){
                    eq_record_add($dbh,{rid=>$rid,fId=>$rf->{'id'},fValue=>$record->{$rf->{'id'}}});
                }
                my $bc = $record->{'bc'};
                $bc =  ($bc ne "")?$bc:"TMP_BC".$rid ."_".$itmId;
#                if (eq_isBarcodeExist($dbh,$bc)){
#                    $bc = "DUP_".$bc."_".$dupNum;
#                    $dupNum++;
#                }
                eq_item_add($dbh, {rid=>$rid,barcode=>$bc,typeId=>$itType,available=>1,reqField=>1,iid=> $itmId});
                foreach my $if (@$eqDefItem){
                    eq_item_add($dbh,{rid=>$rid,iid=>$itmId,sfId=>$if->{'id'},sfValue=>$record->{$if->{'id'}}});
                }
                $dbh->do("update eq_csvImport set countTotal=countTotal + 1 where iid=$iid  && status='processed'");
                $dbh->do("update eq_csvImport set countImported=countImported + 1 where iid=$iid  && status='processed'");
                $dbh->do("update eq_csvImport set countProcessed=countProcessed + 1 where iid=$iid  && status='processed'");
                #$dbh->do("update eq_csvImport set countTotal=countTotal+1,countImported=countImported+1,countProcessed=countProcessed+1 where iid=$iid && status='processed'");
            }
            else {        
                if (($curEqName ne $eqName)  || ($curManufacture ne $manufacture) ||  ($curModel ne $model)){
                    $duplicate = checkDuplicateRecord($dbh,{eqName=>$eqName,manufacture=>$manufacture,model=>$model,rid=>$rid});
                    if ($duplicate){
                        $sth = $dbh->prepare("insert into eq_csvDuplicate set iid = $iid, content = ?");
                        $rec .= "\n";
                        $sth->execute($rec);
                        $mid = $dbh->{'mysql_insertid'};
                        $dbh->do("update eq_csvImport set status = 'indexing' where   iid = $iid && status <> 'indexing '");
                        next;
                    }
                    $rid = eq_record_idGen($dbh);
                    $category = $record->{'category'};
                    $category =~ s/,+$//;
                    eq_record_add($dbh,{rid=>$rid, fValue=>$record->{'eqName'},category=>$catCode,reqField=>1});
                    foreach my $rf (@$eqDefRecord){
                        eq_record_add($dbh,{rid=>$rid,fId=>$rf->{'id'},fValue=>$record->{$rf->{'id'}}});
                    }
                    $curEqName = $eqName;
                    $curManufacture = $manufacture;
                    $curModel = $model;
                    $itmId = 1;
                    $dbh->do("update eq_csvImport set countTotal = countTotal + 1 where  iid = $iid");
                }
                $bc = $record->{'bc'};
                $bc =  ($bc ne "")?$bc:"TMP_BC".$rid ."_".$itmId;
                if (eq_isBarcodeExist($dbh,$bc)){
                    $bc = "DUP_".$bc."_".$dupNum;
                    $dupNum++;
                }
                eq_item_add($dbh, {rid=>$rid,barcode=>$bc,typeId=>$itType ,available=>1,reqField=>1,iid=> $itmId});
                foreach my $if (@$eqDefItem){
                    eq_item_add($dbh,{rid=>$rid,iid=>$itmId,sfId=>$if->{'id'},sfValue=>$record->{$if->{'id'}}});
                }
                $itmId++;
                $dbh->do("update eq_csvImport set countImported=countImported+1 where iid = $iid && status='processed'");
                $dbh->do("update eq_csvImport set countProcessed=countProcessed+1 where iid = $iid && status='processed'");
                #$dbh->do("update eq_csvImport set countImported=countImported+1,countProcessed=countProcessed+1 where iid=$iid && status='processed'");
            }
        }
    }
    close debug;
    $dbh->do("update eq_csvImport set status='indexing' where iid=$iid && countProcessed>0 && countImported>0 && ((countProcessed=countTotalHolding)||(countProcessed=countTotal)) && (status='processed')");
    #$dbh->do("update eq_csvImport set status = 'done' where iid = $iid && (countImported > 0 && countImported >= countProcessed) && (status='indexing') ");
    $sth->finish;
}
sub _mapData2FieldCode{

    my ($dbh,$rec, $hMap) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });

=item    
    my @tmp = split (/,/, $rec) ;
    my $data = "";
    foreach my $t (@tmp){
        $t =~ s/^["\s]*|["\s]*$//g;
        $data .= $t . ",";
    }
    #$csv->parse($data);
=cut    
    $csv->parse($rec);
    my @field = $csv->fields();
    
    foreach my $f(@field) {
        $f =~ s/^\s+//;
        $f =~ s/\s+$//;
        $f =~ s/[^[:print:]]//g;
    }
    my $record;
    my $map = from_json($hMap);
    foreach my $k (sort keys %$map){
        if (defined $map->{$k}->{'0'} && $map->{$k}->{'0'}>=0){
            if ($map->{$k}->{'1'} && $map->{$k}->{'1'} > 0 ){
                $record->{$k} = $field[$map->{$k}->{'0'}] . "-" . $field[$map->{$k}->{'1'}]; 
            }
            else{
                $record->{$k} = $field[$map->{$k}->{'0'}];
            }
        }
        else{
            $record->{$k} ="";
        }
    }
    return $record;
}

#Duplicate Equipment : has the same name, model and manufacuture.
sub checkDuplicateRecord {
    
    my ($dbh,$params) = @_;
    my $eqName = $params->{'eqName'};
    my $manufacture = $params->{'manufacture'};
    my $model       = $params->{'model'};
    my $rid = $params->{'rid'};
    if (!$eqName || $eqName eq ''){
        return 0;
    }
    my $sql = <<_SQL_;
SELECT r.rid, r.rname, 
    max(case when rf.fId=1 then rf.fValue end) manufacture,
    max(case when rf.fId=3 then rf.fValue end) model 
from eq_records r inner join eq_recordFields rf using(rid)  
where r.rname = ? && r.rid < ? group by r.rid;
_SQL_

    my $count=0;
    my $sth = $dbh->prepare($sql);
    $sth->execute($eqName, $rid);
    while (my $r = $sth->fetchrow_hashref()){
        if ($r->{'manufacture'} eq $manufacture &&  $r->{'model'} eq $model){
            $count++;
        }
    }
    $sth->finish;
    return $count;
}

sub getCategoryList {

    my ($dbh) = @_;
    my $sth = $dbh->prepare("select parentId,id,name from eq_category order by id");
    $sth->execute();
    my $category = {};
    while (my $r = $sth->fetchrow_hashref()){
        if ($r->{'parentId'} && $r->{'parentId'} > 0){
            $category->{$r->{'name'}} = $r->{'parentId'} . "," . $r->{'id'};
        }
        else {
            $category->{$r->{'name'}} = $r->{'id'};
        }
    }
    return $category;
}

sub updateBuildingInfo {

    my ($dbh,$params) = @_;
    my ($bldCode,$bldName,$bldAddress,$bldCity,$bldState,$bldCountry);
    $bldCode = $params->{'code'};
    $bldName = $params->{'name'};
    $bldAddress = $params->{'address'};
    $bldCity = $params->{'city'};
    $bldState = $params->{'state'};
    $bldCountry = $params->{'country'};
    if ($bldCode && $bldCode ne ""){
        my $sql = "select count(*) as count from eq_locationDirectory where code=?";
        my $sth =$dbh->prepare($sql);
        $sth->execute($bldCode);
        my $ret = $sth->fetchrow_array();
        $sth->finish;

        if (!$ret){
            $dbh->do("insert into eq_locationDirectory (code,name,address,city,state,country) values('$bldCode','$bldName','$bldAddress','$bldCity','$bldState','$bldCountry')");
        }
        else{
            if ($bldName ne ""){
                $dbh->do("update eq_locationDirectory set name='$bldName',address='$bldAddress',city='$bldCity',state='$bldState',country='$bldCountry',modDate=now() where code='$bldCode'");
            }
        }
    }
}

sub _eq_archiveRecordItems {
    
    my ($dbh,$rid) = @_;
    $dbh->do("update eq_records set deleted='1',modifiedDate=now() where rid=$rid limit 1");
#set all barcode or just one ???
    $dbh->do("update eq_items set deleted='1',barcode = concat('___', barcode,'_',$rid),modifiedDate=now()  where rid=$rid");
    $dbh->do("insert into eq_recordFieldsArchive (rid,fId, fValue) select rid,fId,fValue from eq_recordFields where rid=$rid");
    $dbh->do("insert into eq_itemFieldsArchive (rid,iid,sfId, sfValue) select rid,iid,sfId,sfValue from eq_itemFields where rid=$rid");
    $dbh->do("delete from eq_recordFields where rid=$rid");
    $dbh->do("delete from eq_itemFields where rid=$rid");
}