#!/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_itemType_getList
    eq_defItem_getList
    eq_defRecord_getList
    eq_fieldCodes

    mapData2FieldCode
    eq_isBarcodeExist
    eq_item_findByBarcode

    eq_record_idGen
    eq_record_add
    eq_item_add
);
    
use Opals::Eq_Search qw(
      
    eq_category_getList
    eq_category_getListById

);

use Opals::Eq_Import qw(

    eq_csv_import

);

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_itemType_getList($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 ($fieldsMapSaved)=$dbh->selectrow_array("select val from opl_preference where var='iEQmap'");

my $defaultHeaderMap = "{ 
    '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 = { 
    '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)) : {};

if (!$op || $op eq ""){
    $importedList = getImportedList ($dbh);
    $template->param(
        step            => "loadFile",
        importedList    => to_json($importedList,{pretty=>1}),
    );
}
elsif($op eq 'load' && $csvfile){
    my ($iid,$header) = 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,
        );
    }
    else{
        $template->param(
            error_loadFile  => 0,
            step            => "mapFields",
            eqItemTypeList  => to_json($eqItemTypeList),
            importedList    => to_json($importedList,{pretty=>1}),
            defaultHeaderMap=> $defaultHeaderMap,
            columnList      => to_json($header),
            eqDefRecord     => to_json($eqDefRecord),
            eqDefItem       => to_json($eqDefItem),
            buildingInfo    => to_json($buildingInfo),
            fieldsMapSaved  => $fieldsMapSaved,
            iid             => $iid,

        );
    }
}
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,

    );
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);
}


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, 
        (countTotal - countImported) 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'");
    #eq_csv_import($dbh,$iid);
}
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;
}

__END_OF_FILE: