package Opals::Eq_Import;

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

    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_defItem_getList
    eq_defRecord_getList
    eq_record_add
    eq_item_add
    eq_isBarcodeExist
    eq_item_findByBarcode
    eq_record_idGen
		eq_record_items_deleteAll
		eq_maxBarcodeDup
);

use Opals::Eq_BarcodeMgmt qw(
    eq_bcm_getBiggestBcFromDB
    eq_bcm_createNextBiggestBc
    eq_bcm_getMaxBarcode
    eq_bcm_alignTakenBc

);
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 $recordDef;
my $itemDef;
my $categories ;
my $itemType;

sub eq_csv_import {
	 my ($dbh,$importid) = @_;
	 my $import_info = _get_import_info($dbh,$importid);
	 my $mode = $import_info->{'mode'};
	 my $iid = $import_info->{'iid'};
	 my $itemType = $import_info->{'itemType'};
	 my $i_data = $import_info->{'data'};
   my $importInfo = {
     startBc    => $import_info->{'startBc'},
     vendorBcId => $import_info->{'vendorBcId'},
     itemType   => $import_info->{'itemType'},
     recType    => $import_info->{'recType'},
     mode       => $import_info->{'mode'}
   };
   my $startBc = $import_info->{'startBc'};
   my $vendorBcId = $import_info->{'$vendorBcId'};
   $i_data =~ s/\x0d[\x0a]?/\n/g;
   $i_data =~ s/\r/\n/g;
   $i_data =~ s/\n\n/\n/g;
	 $recordDef = eq_defRecord_getList($dbh);
   $itemDef = eq_defItem_getList($dbh);
   $categories = _getCategoryList($dbh);
	 my @csvData = split(/\n/,$i_data);
   open debug, ">/tmp/II";
   print debug "importid: $importid \n";
   print "importid: $iid \n";
	 return if (!$iid); 
   $dbh->do("update eq_csvImport set status='processed' where iid=$iid");
   print "import info: ", to_json($import_info,{pretty=>1}), "\n";
   print debug "import info: ", to_json($import_info,{pretty=>1}), "\n";
   print debug "itemDef: ", to_json($itemDef,{pretty=>1}), "\n";
#****

   my $curBc="";
	 for (my $i = 1; $i < scalar(@csvData); $i++){
			my $row = $csvData[$i];
			my $record = _mapData($dbh,$row,$import_info->{'mapHeader'});
      print debug "i: $i \t ", to_json($record) , "\n";
      print  "i: $i \t ", to_json($record) , "\n";
      #if ($record->{'barcode'} && $record->{'barcode'} ne "" && $vendorBcId==-2){
      if ($record->{'barcode'} && $record->{'barcode'} ne "" ){
        $record->{'bc'} = $record->{'barcode'};
      }
      elsif ($import_info->{'startBc'} ne ""){
          if ($i<=1){
            $record->{'bc'} = $import_info->{'startBc'};
            $curBc = $import_info->{'startBc'};
          }
          else{
            $curBc = _nextBarcode ($curBc);
            $record->{'bc'} = $curBc;
          }
      }
      else{
        my $maxBc = eq_bcm_getMaxBarcode($dbh);
        print "maxBc: $maxBc \n";
        my $bc = eq_bcm_createNextBiggestBc($dbh,$maxBc);
        $record->{'bc'} = $bc;
      }
      print debug "data $i : $row \n";

			my ($bldCode,$bldName,$bldAddress) = ($record->{'bldCode'},$record->{'bldName'},$record->{'bldAddress'} );
			my ($bldCity,$bldState, $bldCountry) = ($record->{'bldCity'},$record->{'bldState'},$record->{'bldCountry'});
			if ($bldCode && $bldCode ne "" && ($record->{'eqName'} ne "" || $record->{'name'} ne "" || $record->{'eq_name'} ne "")){
				 _updateBuildingInfo($dbh,{code=>$bldCode,name=>$bldName,address=>$bldAddress,city=>$bldCity,state=>$bldState,country=>$bldCountry});
			}
			if ($mode =~ m/new/){
				 _importAsNew($dbh,{data=>$record,iid=>$iid,importInfo=>$importInfo});
			}
			elsif($mode =~ m/replace/){
				 _importAsReplace($dbh,{data=>$record,iid=>$iid});
			}
      elsif($mode =~ m/replaceBc/){
        _importAsUpdateBc($dbh,{data=>$record,iid=>$iid,row=>$row});
      }
			else{ #autoMerge
				_importAsAutoMerge($dbh,{data=>$record,iid=>$iid,row=>$row});
			}
	 }
   close debug;
	 $dbh->do("update eq_csvImport set status='indexing' where iid=$iid&&countProcessed>0&&countImported>0&&((countProcessed>=countTotalHolding)||(countProcessed>=countTotal))&&(status='processed')");

   if ($import_info->{'vendorBcId'}>0 && $import_info->{'startBc'}){  
     eq_bcm_alignTakenBc($dbh);
   } 

}

sub _nextBarcode {
    my ($barcode) = @_;
    my $holdZero="";
    my $flag=1;
    my $char="";
    my $num="";
    my $prefix = "";
    my $len = length($barcode);
   
    for (my $i=0;$i<length($barcode); $i++){
        $char = substr($barcode,length($barcode)-($i+1),1);
        if ( ord($char) > 48 && ord($char)<=57 && $flag){
            $num .= $holdZero . $char;
            $holdZero = "";
        }
        elsif (ord($char) == 48 && $flag){
            $holdZero .= '0';
        }
        else{
            $flag = 0;
            $prefix .= $char;
        }
    }
    $prefix = scalar reverse("$prefix");
    $num    = scalar reverse("$num");
    my @barcodes = ();
    my $tmpBC = $barcode;
    my $n=int($num)+1;
    my $bc = $prefix . $holdZero . $n;
    while ( length($bc) > $len && length($holdZero) > 0){
        $holdZero = substr($holdZero,1,length($holdZero)-1);
        $bc = $prefix . $holdZero . $n;
    }
    return $bc;
}

sub _importAsNew {
	 my ($dbh,$param) = @_;
	 _importRecord($dbh,$param);
}

sub _importAsReplace {
	 my ($dbh,$param) = @_;
   my $itemInfo;
	 my $doReplace = "no";
	 my $iid = $param->{'iid'};
	 my $data = $param->{'data'};
	 if (eq_isBarcodeExist($dbh,$data->{'bc'})){
				$itemInfo = eq_item_findByBarcode($dbh,$data->{'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_record_items_deleteAll($dbh,{rid=>$rid_old});
	 }
	 _importRecord($dbh,{data=>$data,iid=>$iid});
	 $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'");
}

sub _importAsAutoMerge {
	 my ($dbh,$param) = @_;
	 my $iid = $param->{'iid'};
	 my $data = $param->{'data'};
   my $row = $param->{'row'};
	 my $recordExists = _checkRecordExist($dbh,$data);
	 if ($recordExists->{'count'} == 0 && $recordExists->{'rid'} == 0){
			_importRecord($dbh,{data=>$data,iid=>$iid});
	 }
	 elsif($recordExists->{'count'} == 1 && $recordExists->{'rid'} > 0){
			_importRecord($dbh,{data=>$data,rid=>$recordExists->{'rid'},iid=>$iid});
	 }
	 else{
			_saveDuplicateRecord($dbh,{iid=>$iid,row=>$row});
	 }
}
sub _importAsUpdateBc {
   my ($dbh,$param) = @_;
	 my $rid = $param->{'rid'};
	 my $iid = $param->{'iid'};
	 my $data = $param->{'data'};
}

sub _importRecord {
	 my ($dbh,$param) = @_;
	 my $rid = $param->{'rid'};
	 my $iid = $param->{'iid'};
	 my $data = $param->{'data'};
	 my $qty = $data->{'qty'} || 1;
   my $unitDescription = $data->{'unitDescription'};
   my $unitPerItem = $data->{'unitPerItem'};
   my $recType = $param->{'importInfo'}->{'recType'};
   my $consumable =  ($recType eq 'consumable')?"1":"0";
   $data->{'eqName'} = $data->{'eqName'} || $data->{'eq_name'} || $data->{'name'};
   print "consumable : $consumable \trecType:$recType \n";
   print "param in _importRecord:", to_json($param,{pretty=>1}), "\n";
   #return;
	 if (!defined $rid || $rid<=0){
			$rid =  eq_record_idGen($dbh);
			my $category = $data->{'category'};
				 $category =~ s/,+$//;
		  my $catCode =  ($category ne "")?$categories->{$category}:"";		 
			eq_record_add($dbh,{rid=>$rid, fValue=>$data->{'eqName'},category=>$catCode,reqField=>1});
      foreach my $rf (@$recordDef){
				 eq_record_add($dbh,{rid=>$rid,fId=>$rf->{'id'},fValue=>$data->{$rf->{'id'}}});
      }
	 }
   #my $itType = ($data->{'itemType'})? $data->{'itemType'} : $itemType;
   my $itType = ($param->{'importInfo'}->{'itemType'})? $param->{'importInfo'}->{'itemType'} : $data->{'itemType'};
	 #my $bc = $data->{'bc'};
	 my $bc = $data->{'bc'} ;
	 my $itmId = 	$dbh->selectrow_array("select max(iid) from eq_items where rid=$rid") || 0;
	 $itmId++;
	 $bc =  ($bc ne "")?$bc:"TMP_BC_".$rid ."_".$itmId;
   print "bc at 198 : $bc \n";
   if (!$bc || $bc eq "0" || $bc eq ""){
        $bc = eq_bcm_createNextBiggestBc($dbh,$bc);
   }
	 if (eq_isBarcodeExist($dbh,$bc)){
			$bc = eq_maxBarcodeDup($dbh,$bc);
	 }
	 eq_item_add($dbh,{rid=>$rid,barcode=>$bc,typeId=>$itType,available=>1,reqField=>1,iid=>$itmId,consumable=>$consumable,qty=>$qty,unitDescription=>$unitDescription,unitPerItem=>$unitPerItem});
	 foreach my $if (@$itemDef){
			eq_item_add($dbh,{rid=>$rid,iid=>$itmId,sfId=>$if->{'id'},sfValue=>$data->{$if->{'id'}}});
	 }
	 $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'");
}

sub _get_import_info {
	 my ($dbh,$iid) = @_;
	 my $sth; 

	 if (defined $iid){
        $sth = $dbh->prepare(<<_STH_);
select  iid,mapHeader,data,sCode,lCode,itemType,mode,vendorBcId,startBc,recType
from    eq_csvImport
where   iid = ? && status='accepted'
_STH_
    $sth->execute($iid);
    }
    else{
        $sth = $dbh->prepare(<<_STH_);
select  iid,mapHeader,data,sCode,lCode,itemType,mode,vendorBcId,startBc,recType
from    eq_csvImport
where   status = 'accepted' && (countProcessed < countTotalHolding)
order by dateUpload asc
limit 1
_STH_
    $sth->execute();
}
	 my $ret = $sth->fetchrow_hashref;
	 $sth->finish;
	 return $ret;
}

sub _mapData{
    my ($dbh,$rec,$hMap) = @_;
    print  "rec : $rec \n";
    print "hMap : $hMap\n";
    my $csv = Text::CSV_XS->new({ binary => 1 });
    $csv->parse($rec);
    my @field = $csv->fields();
    foreach my $f(@field) {
        $f =~ s/^\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'}];
            }
						$record->{$k} = ($record->{$k} eq "-")?"":$record->{$k};
        }
        else{
            $record->{$k} ="";
        }
				$record->{$k} =~ s/^\s+|\s+$//;
    }
    print "record:" , to_json($record) ," \n";
    return $record;
}

sub _checkRecordExist {
    my ($dbh,$params) = @_;
    my $eqName = $params->{'eqName'}||$params->{'name'} ||$params->{'eq_name'} ;
    my $manufacture = $params->{'1'};
    my $model       = $params->{'3'};
    my $rid = $params->{'rid'};
    if (!$eqName || $eqName eq ''){
        return 0;
    }
		my $retRid=0;
    my $sql = <<_SQL_;
		select r.rid,r.rname,rf1.fValue as manufacture,rf3.fValue as model
from eq_records r 
  inner join eq_recordFields as rf1 on rf1.rid=r.rid and rf1.fId=1
  inner join eq_recordFields as rf3 on rf3.rid=r.rid and rf3.fId=3
where r.rname = ?
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute($eqName);
		my $count=0;
    while (my $r = $sth->fetchrow_hashref()){
        if ($r->{'manufacture'} eq $manufacture &&  $r->{'model'} eq $model){
						$retRid=$r->{'rid'};
						$count++;
        }
    }
    $sth->finish;
    return {rid=>$retRid,count=>$count};
}

sub _saveDuplicateRecord {
	 my ($dbh,$param) = @_;
	 my $iid = $param->{'iid'};
   my $data = $param->{'row'};
	 my $sth = $dbh->prepare("insert into eq_csvDuplicate set iid = $iid, content = ?");
   $data .= "\n";
   $sth->execute($data);
   my $mid = $dbh->{'mysql_insertid'};
   $dbh->do("update eq_csvImport set status = 'indexing' where iid=$iid && status<>'indexing '");
}

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'};
        }
    }
		$sth->finish;
    return $category;
}

sub _updateBuildingInfo {
    my ($dbh,$params) = @_;
    my $bldCode = $params->{'code'};
    my $bldName = $params->{'name'};
    my $bldAddress = $params->{'address'};
    my $bldCity = $params->{'city'};
    my $bldState = $params->{'state'};
    my $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'");
            }
        }
    }
}


1;
################################################################################