#!/usr/bin/perl -w

#use utf8;
use strict;
use CGI;
#use Text::CSV::Unicode;
use Text::CSV_XS;
use JSON;

use Opals::Context;

use Digest::SHA qw(
    sha512_hex
);

use Opals::Search qw(
    srch_F852Default_marc21
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

use Opals::Equipment qw(
    eq_getItemTypeList
    eq_defItem_getList
    eq_defRecord_getList
    eq_fieldCodes
    eq_recordFields
    eq_itemFields
);
    
use Opals::Eq_Search qw(
    eq_category_getList
    eq_category_getListById
);

use Opals::Eq_BarcodeMgmt qw(
    eq_bcm_getVendorList
    eq_bcm_getBiggestBcFromDB
    eq_bcm_createNextBiggestBc
    eq_bcm_getMaxBarcode
);

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/importCSV.tmpl',
    }
);
my $sth; 

# Get id of importer
my $userfile = $cgi->param('csvfile');
my $ImportFile = '';

# Important: the order and the name of inputs on screen MUST be the same order as in
# the array DataHeader
# Important: the order of variables in the insert SQL command MUST be the same
# order as in the array DataHeader
 
my @headerMap = ();
my @ImportHeader = ();

#Thu, Jan 07, 2010 @ 13:50:35 EST
my $msgValMap ={};

my $op = $input->{'op'};
my $csvfile = $cgi->param('csvfile');
my $selItemType = $input->{'itemType'};
my $uid = $template->param('curUserId');
my $eqDefRecord = eq_defRecord_getList($dbh);
my $eqDefItem = eq_defItem_getList($dbh);
my $eqDef = eq_fieldCodes($dbh);
my $eqItemTypeList = eq_getItemTypeList($dbh);
my $categoryList    = eq_category_getList($dbh);
my $importedList;
my $mesgDigest;
my $default852 = srch_F852Default_marc21($dbh);
my $sCode =  $default852->subfield('a') || "";
my $lCode =  $default852->subfield('b') || "";
my $chkSCode = $input->{'chkSCode'} || "";
my $chkLCode = $input->{'chkLCode'} || "";
my $mode    = $input->{'mode'};
my $type    = $input->{'type'};
my ($fieldsMapSaved)=$dbh->selectrow_array("select val from opl_preference where var='iEQmap'");
my ($bcVendorList,$lastBc,$nextToLastBc) = ([],"","");

my $defaultHeaderMap = "{ 
    'name'            : 'Equipment Name',
    'category'        : 'Category',
    'manufacturer'    : 'Manufacturer',
    'model'           : 'Model',
    'warrantyNote'    : 'Warranty Note',
    'ManufacturerURL' : 'Manufacturer URL',
    'manualUrl'       : 'Online Manual',
    'supplierUrl'     : 'Supplier URL',
    'generalNote'     : 'General Note',
    'generalAccessoriesNote'  : 'General Accessories Note',
    'barcode'         : 'Barcode',
    'typeId'          : 'Item Type',
    'serialNumber'    : 'Serial Number',
    'price'           : 'Price',
    'districtInventoryNumber' : 'District Inventory Number',
    'fundingSource'   : 'Funding Source/Grant',
    'location'        : 'Location in building',
    'buildingCode'    : 'Building Code',
    'districtCode'    : 'District Code',
    'dateAcquired'    : 'Date Acquired/Assigned/Install',
    'machineName'     : 'Machine Name',
    'statusNote'      : 'Item Status Note',
    'vendorName'      : 'Vendor Name',
    'assignedPersonFirstName' : 'Assigned Person First Name',
    'assignedPersonLastName'  : 'Assigned Person Last Name',
    'purchaseOrder'   : 'P.O. Number',
    'ipAddress'       : 'IP Address',
    'macAddress'      : 'MAC Address',
    'softwareKey'     : 'Software Key',
    'cartNumber'      : 'Cart Number',
    'slotNumber'      : 'Slot Number',
    'accessories'     : 'Accessories',
    'bldCode'         : 'Building Code',
    'bldName'         : 'Building Name',
    'bldAddress'      : 'Building Address',
    'bldCity'         : 'Building City',
    'bldState'        : 'Building State',
    'bldCountry'      : 'Building Country',
    }";

my $defaultHeaderMap1 = "{ 
    'eqName'    : 'Equipment Name',
    'category'  : 'Category',
    '1'         : 'Manufacturer',
    '3'         : 'Model',
    '14'        : 'Warranty Notes',
    '16'        : 'Manufacturer URL',
    '28'        : 'Online Manual',
    '27'        : 'Supplier URL',
    '33'        : 'General Notes',
    '36'        : 'General Accessories Notes',
    'bc'        : 'Barcode',
    'itemType'  : 'Item Type',
    '5'         : 'Serial Number',
    '6'         : 'Price',
    '7'         : 'District Inventory Number',
    '9'         : 'Grant',
    '11'        : 'Location in building',
    '18'        : 'Building Code',
    '26'        : 'District Code',
    '31'        : 'Date Acquired/Assigned/Install',
    '37'        : 'Machine Name',
    '38'        : 'Status',
    '39'        : 'Vendor Name',
    '40'        : 'Assigned Person First Name',
    '41'        : 'Assigned Person Last Name',
    '42'        : 'P.O. Number',
    '43'        : 'IP Address',
    '44'        : 'MAC Address',
    '45'        : 'Software Key',
    '46'        : 'Cart Number',
    '47'        : 'Slot Number',
    '48'        : 'Accessories',
    'bldCode'   : 'Building Code',
    'bldName'   : 'Building Name',
    'bldAddress': 'Building Address',
    'bldCity'   : 'Building City',
    'bldState'  : 'Building State',
    'bldCountry': 'Building Country',
    }";

my $defaultHMap_new = {
    'name'            => 'Equipment Name',
    'category'        => 'Category',
    'manufacturer'    => 'Manufacturer',
    'model'           => 'Model',
    'warrantyNote'    => 'Warranty Note',
    'ManufacturerURL' => 'Manufacturer URL',
    'manualUrl'       => 'Online Manual',
    'supplierUrl'     => 'Supplier URL',
    'generalNote'     => 'General Note',
    'generalAccessoriesNote'  => 'General Accessories Note',
    'barcode'         => 'Barcode',
    'typeId'          => 'Item Type',
    'serialNumber'    => 'Serial Number',
    'price'           => 'Price',
    'districtInventoryNumber' => 'District Inventory Number',
    'fundingSource'   => 'Funding Source/Grant',
    'location'        => 'Location in building',
    'buildingCode'    => 'Building Code',
    'districtCode'    => 'District Code',
    'dateAcquired'    => 'Date Acquired/Assigned/Install',
    'machineName'     => 'Machine Name',
    'statusNote'      => 'Item Status Note',
    'vendorName'      => 'Vendor Name',
    'assignedPersonFirstName' => 'Assigned Person First Name',
    'assignedPersonLastName'  => 'Assigned Person Last Name',
    'purchaseOrder'   => 'P.O. Number',
    'ipAddress'       => 'IP Address',
    'macAddress'      => 'MAC Address',
    'softwareKey'     => 'Software Key',
    'cartNumber'      => 'Cart Number',
    'slotNumber'      => 'Slot Number',
    'accessories'     => 'Accessories',
    'bldCode'         => 'Building Code',
    'bldName'         => 'Building Name',
    'bldAddress'      => 'Building Address',
    'bldCity'         => 'Building City',
    'bldState'        => 'Building State',
    'bldCountry'      => 'Building Country'
};
my $defaultHMap = { 
    'eqName'    => 'Equipment Name',
    'category'  => 'Category',
    '1'         => 'Manufacturer',
    '3'         => 'Model',
    '14'        => 'Warranty Notes',
    '16'        => 'Manufacturer URL',
    '28'        => 'Online Manual',
    '27'        => 'Supplier URL',
    '33'        => 'General Notes',
    '36'        => 'General Accessories Notes',
    'bc'        => 'Barcode',
    'itemType'  => 'Item Type',
    '5'         => 'Serial Number',
    '6'         => 'Price',
    '7'         => 'District Inventory Number',
    '9'         => 'Grant',
    '11'        => 'Location in building',
    '18'        => 'Building Code',
    '26'        => 'District Code',
    '31'        => 'Date Acquired/Assigned/Install',
    '37'        => 'Machine Name',
    '38'        => 'Status',
    '39'        => 'Vendor Name',
    '40'        => 'Assigned Person First Name',
    '41'        => 'Assigned Person Last Name',
    '42'        => 'P.O. Number',
    '43'        => 'IP Address',
    '44'        => 'MAC Address',
    '45'        => 'Software Key',
    '46'        => 'Cart Number',
    '47'        => 'Slot Number',
    '48'        => 'Accessories',
    'bldName'   => 'Building Name',
    'bldAddress'=> 'Building Address',
    'bldCity'   => 'Building City',
    'bldState'  => 'Building State',
    'bldCountry'=> 'Building Country',
};
my $hFieldsMapSaved = (defined $fieldsMapSaved)?from_json(($fieldsMapSaved)) : {};

open debug, ">>/tmp/C";
print debug "input: ", to_json($input), "\n";

if (!$op || $op eq ""){
    $importedList = getImportedList ($dbh);
    my $eqItemFields = eq_itemFields();
    my $bcVendorList =  eq_bcm_getVendorList($dbh);
    #$lastBc = eq_bcm_getBiggestBcFromDB($dbh);
    $lastBc = eq_bcm_getMaxBarcode($dbh);
    $nextToLastBc = eq_bcm_createNextBiggestBc($dbh,$lastBc);
    
    foreach (my $i=0;$i<scalar(@$eqItemFields);$i++){
        if (@$eqItemFields[$i]->{'id'} =~ m/barcode|typeId|itemType/){
          splice (@$eqItemFields,$i,1);
        }
    }
    $template->param(
        step            => "loadFile",
        importedList    => to_json($importedList,{pretty=>1}),
        eqRecordFields  => to_json(eq_recordFields(),{pretty=>1}),
        eqItemFields    => to_json($eqItemFields,{pretty=>1}),
        eqItemTypeList  => to_json($eqItemTypeList,{pretty=>1}),
        bcVendorList    => to_json($bcVendorList,{pretty=>1}),
        eqItemConsumableFields=>to_json(get_eqItemConsumableFields(),{pretty=>1}),
        lastBc          => $lastBc ,
        nextAvailBc    => $nextToLastBc
    );
}
elsif($op eq 'load' && $csvfile){
    my ($iid,$header,$totalBarcodes) = doImport_load();
    $importedList = getImportedList ($dbh);
    my $buildingInfo = getBuildingColumns();
 
    if ($iid < 0){
        $template->param(
            error_loadFile  => $iid,
            step            => "loadFile",
            importedList    => to_json($importedList,{pretty=>1}),
            iid             => $iid,
            totalBarcodes   => $totalBarcodes,
        );
    }
    else{
      $bcVendorList = eq_bcm_getVendorList($dbh);
      $lastBc = eq_bcm_getBiggestBcFromDB($dbh);
      $nextToLastBc = eq_bcm_createNextBiggestBc($dbh,$lastBc);
      my $eqItemFields = eq_itemFields();
      print debug "eqItemFields 1: ", to_json($eqItemFields,{pretty=>1}) ,"\n";
      foreach (my $i=0;$i<scalar(@$eqItemFields);$i++){
        if (@$eqItemFields[$i]->{'id'} =~ m/barcode|typeId|itemType/){
          splice (@$eqItemFields,$i,1);
        }
      }
      print debug "eqItemFields: 2 ", to_json($eqItemFields,{pretty=>1}), "\n";
      $template->param(
            error_loadFile  => 0,
            step            => "mapFields",
            type            => $type,
            eqItemTypeList  => to_json($eqItemTypeList),
            importedList    => to_json($importedList,{pretty=>1}),
            defaultHeaderMap=> $defaultHeaderMap,
            columnList      => to_json($header),
            eqDefRecord     => to_json($eqDefRecord,{pretty=>1}),
            eqDefItem       => to_json($eqDefItem,{pretty=>1}),
            buildingInfo    => to_json($buildingInfo),
            fieldsMapSaved  => $fieldsMapSaved,
            iid             => $iid,
            eqRecordFields  => to_json(eq_recordFields(),{pretty=>1}),
            eqItemFields    => to_json($eqItemFields,{pretty=>1}),
            eqItemConsumableFields=>to_json(get_eqItemConsumableFields(),{pretty=>1}),
            bcVendorList    => to_json($bcVendorList,{pretty=>1}),
            lastBc          => $lastBc,
            nextToLastBc    => $nextToLastBc    
        );
    }
}
elsif ($op && $op eq 'map' && $input->{'iid'}){
    doImport_mapImport($dbh);
}
elsif ($op eq 'accept' && $input->{'iid'}){
     doImport_acceptImport($dbh);
}
elsif ($op eq 'reject' && $input->{'iid'}){
    doImport_rejectImport($dbh);
}
elsif ($op && $op eq 'cancel'){
    doImport_cancel($dbh);
}
    $op="";
    $importedList = getImportedList ($dbh);
    $template->param(
        importedList    => to_json($importedList,{pretty=>1}),
        sCode           => $sCode,
        lCode           => $lCode,

    );
close debug;    
tmpl_write($dbh, $cgi, $cookie, $template);

# doImport_load
# return 
#   -1 : file invalid
#   -2 : file unsupport
#   -3 : file duplicate
sub doImport_load {

    my $tmpFile = `mktemp -t eqmnt_import_csv.XXXXXX`;
    my $csvData = '';
    my $ret = 0; 
    #fixed DOS new line..
    while(<$csvfile>) {
        $csvData .= $_;
    }
#^M => \x0d
#\n => \x0a
    $csvData =~ s/\x0d[\x0a]?/\n/g;
    
    $csvData =~ s/\r/\n/g;
    $csvData =~ s/\n\n/\n/g;

    if (!$csvData || $csvData eq ''){
        return -1;
    }
    my @d = split(/\n/, $csvData);
    my $firstline = $d[0];
    my $countTotal = scalar(@d) - 1;

    open TMPFILE, ">$tmpFile";
    print TMPFILE $csvData;
    close TMPFILE;
    $mesgDigest = sha512_hex($csvData);
    my $fileType = `file $tmpFile | sed -re 's/^.+: //'`;
    unlink($tmpFile);
    if ($fileType !~ 'text'){
        return -2;
    } 
    $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    eq_csvImport
where   mesgDigest = ?
_STH_
    $sth->execute($mesgDigest);
    my ($fileDuplicated) = $sth->fetchrow_array;
    $sth->finish();

    if ($fileDuplicated) {
        return -3;
    }
    $sth = $dbh->prepare(<<_STH_);
insert into eq_csvImport
set uid = $uid,
    itemType    = ?,
    filename    = ?,
    header      = ?,
    mode       = ?,
    countTotalHolding  = ?,
    dateUpload = now(),
    status = 'uploaded',
    mesgDigest  = ?,
    data    =?
_STH_
    $sth->execute($selItemType,$csvfile,$firstline,$mode,$countTotal,$mesgDigest,$csvData);
    my $iid = $dbh->{'mysql_insertid'};
    $sth->finish();
    my @importHeader = loadHeader($firstline);
    return ($iid, \@importHeader,$countTotal);
}


sub loadHeader {
    my ($headerLine) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
    #$headerLine =~ s/"//g;
    $csv->parse($headerLine);
    my @field = $csv->fields();
    foreach my $f (@field) {
        $f =~ s/^\s+//;
	    $f =~ s/\s+$//;
    }
    my @headerList = ();
    my $i = 1;
    push @headerList, {
        id      => 0,
        header  => '--',
        mapId   => "",    
    };
    foreach my $columnHeader (@field) {#split /,/, $HeaderLine) {
        if ($columnHeader ne "") {
            push @headerList, {
                id      => $i,
                header  => $columnHeader,
                mapId   => findMapId($columnHeader),
            };
            $i++;
        }
    }
    return @headerList;
}

sub findMapId {
    my ($header) = @_;
    $header =~ s/^\s+|\s+$//g;
    foreach my $key (keys %{$defaultHMap}){
        if (lc($header) eq lc($defaultHMap->{$key})){
            return $key;
        }
    }

    foreach my $key (keys %{$defaultHMap}){
        if ($header =~ /$defaultHMap->{$key}/i){
            return $key;
        }
    }
    return 0;
}

sub getImportedList {
    
    my ($dbh) = @_;
    my @importedList = ();

    my $sth = $dbh->prepare(<<_STH_);
select  
        c.iid,c.itemType,c.filename,c.mode,c.countImported,c.countMerged,c.countProcessed,
        c.countTotal,c.countTotalHolding,c.dateImport,c.dateUpload,c.status,
        u.uid, u.firstname, u.lastname, 
        (CAST(countTotal as SIGNED) - CAST(countImported as SIGNED)) as countImporting
from    eq_csvImport as c left outer join opl_user as u on c.uid = u.uid
order by iid desc
_STH_
    $sth->execute();
my $sth_mergeCount = $dbh->prepare(<<_STH_);    
select  count(*) 
from    eq_csvDuplicate
where iid = ?
_STH_

    while(my $impInfo = $sth->fetchrow_hashref){
        $sth_mergeCount->execute($impInfo->{'iid'});
        ($impInfo->{'countPending'}) = $sth_mergeCount->fetchrow_array;
        if ($impInfo->{'countTotal'} == $impInfo->{'countProcessed'}){
            $impInfo->{'countIgnored'} = 
                $impInfo->{'countTotal'} 
                - $impInfo->{'countMerged'}
                - $impInfo->{'countPending'} 
                - $impInfo->{'countImported'};
        }
        else{
            $impInfo->{'countIgnored'} = 0;
        }
        $impInfo->{'recInStatus_'.$impInfo->{'status'}} = 1;
        push @importedList, $impInfo;
    }
    return \@importedList;
}

sub doImport_mapImport {
    
    my ($dbh) = @_;
    my $iid = $input->{'iid'} ;
    my $map = $input->{'map'} || {};
    my $itemType = $input->{'itemType'};
    my $saveMap = $input->{'saveMap'};
    my $hmap = from_json($map);
    foreach my $k (sort keys %{$hmap}){
        if ($hmap->{$k}->{'0'} && $hmap->{$k}->{'0'} > 0){
            $hmap->{$k}->{'0'} = int($hmap->{$k}->{'0'}) -1;
        }
        else{
             $hmap->{$k}->{'0'} = -1;
        }
        if ($hmap->{$k}->{'1'} && $hmap->{$k}->{'1'} > 0){
            $hmap->{$k}->{'1'} = int($hmap->{$k}->{'1'}) -1;
        }
        else{
            $hmap->{$k}->{'1'} = -1;
        }
    }
    if ($itemType &&  $itemType =~ m/(\d+)/){
        $itemType = "";
    }
    $map = to_json($hmap);
    $dbh->do("update eq_csvImport set mapHeader='$map',itemType='$itemType' where iid=$iid");
    if ($saveMap){
        $dbh->do("insert into opl_preference (var,val) values ('iEQmap','$map') on duplicate KEY update val='$map'");
    }
}

sub doImport_acceptImport{
    my ($dbh) = @_;
    my $iid = $input->{'iid'} ;
    return if (! defined $iid);
    $dbh->do("update eq_csvImport set status='accepted' where iid=$iid && status='uploaded'");
}
sub doImport_rejectImport {
    my ($dbh) = @_;
    my $iid = $input->{'iid'} ;
    return if (! defined $iid);
    $dbh->do("update eq_csvImport set status='rejected', mesgDigest='' where iid=$iid && status='uploaded'");
}

sub doImport_cancel {

    my ($dbh) = @_;
    $dbh->do("delete from eq_csvImport where iid = $input->{'iid'} limit 1");
}

sub getBuildingColumns {
    my ($dbh) = @_;
    my @list=(
        {id=>"bldName",    name=>"Building Name",   value=>"name"},
        {id=>"bldAddress", name=>"Address",         value=>"address"},
        {id=>"bldCity",    name=>"City",            value=>"city"},
        {id=>"bldState",   name=>"State",           value=>"state"},
        {id=>"bldCountry", name=>"Country",         value=>"country"},
    );
    return \@list
}

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 get_eqItemConsumableFields {
  my $fields = [
    {"id"=>"qty","name"=>"Quantity","description"=>"","dataType"=>"number","authCtrl"=>"0","size"=>10 },
    {"id"=>"unitDescription","name"=>"Unit Description","description"=>"Box,Bottle,Pack,etc....","dataType"=>"text","authCtrl"=>"0","size"=>50, },
    {"id"=>"unitPerItem","name"=>"Unit Per Item","description"=>"# of unit(s) per Box,Pack","dataType"=>"number","authCtrl"=>"0","size"=>50 },
  ];
  return $fields;
}

__END_OF_FILE: