#!/usr/bin/perl -w

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

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/import_csv.tmpl',
            reqPermission   => 'eq_record_edit',
        }
);

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 $fileMsgMap            =loc_getMsgFile('user/userInfo.msg',$msgValMap);
#loc_write($template,$fileMsgMap);

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 $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',
    }";


if (!$op || $op eq ""){
    $template->param(
        selectCSVFile   => 1,
    );
}
elsif($op eq 'load' && $csvfile){
    
   #foreach my $i (@$eqItemTypeList){
   #     if ($i->{'id'} eq  $selItemType ){
   #         $i->{'sel'} = 1;
   #         last;
   #     }
   #}
    doImport_load();
}
elsif ($op && ($op eq 'import' || $op eq 'map' ) && $input->{'iid'}){
    doImport_mapImport($dbh);
}
elsif ($op && $op eq 'cancel'){
    doImport_cancel($dbh);
}

NeedFile:
    $op="";
    $importedList = getImportedList ($dbh);
    $template->param(
        eqItemTypeList  => $eqItemTypeList,
        importedList    => $importedList,
        sCode           => $sCode,
        lCode           => $lCode,
        chkSCode        => $chkSCode eq "" ? 0 :1,
        chkLCode        => $chkLCode eq "" ? 0 :1,
        defaultHeaderMap   => $defaultHeaderMap,
    );

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


goto __END_OF_FILE;


sub doImport_load {

    my $tmpFile = `mktemp -t eqmnt_import_csv.XXXXXX`;
    my $csvData = '';
    
    #fixed DOS new line..
    while(<$csvfile>) {
        $csvData .= $_;
    }
#^M => \x0d
#\n => \x0a

    $csvData =~ s/\x0d[\x0a]?/\n/g;
    
#    # save fixed DOS new lines
#    open TMPFILE, ">$tmpFile";
#    print TMPFILE $csvData;
#    close TMPFILE;
#
#    # read fixed DOS new lines
#    open TMPFILE, "<$tmpFile";
#    my $firstline = <TMPFILE>;
#    if (!$firstline || $firstline eq ''){
    if (!$csvData || $csvData eq ''){
        $template->param(
            wrongFile       => 1,
            selectCSVFile   => 1,
        );
        goto NeedFile;
    }
#    $csvData = $firstline;
#    my $countTotal = 0;
#    while(<$csvfile>) {
#        $countTotal++;
#        $csvData .= $_;
#    }
#    close TMPFILE;

    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'){
        $template->param(
            filename => $csvfile,
            unsupportedFile => 1,
            selectCSVFile => 1 ,
        );    
        goto NeedFile;
    } 
    $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    eq_csvImport
where   mesgDigest = ?
_STH_
    $sth->execute($mesgDigest);
    my ($fileDuplicated) = $sth->fetchrow_array;
    $sth->finish();

    if ($fileDuplicated) {
        $template->param(
            filename    =>  $csvfile,
            duplicateFile => 1 , 
            selectCSVFile => 1 ,
        );
        goto NeedFile;
    }
    $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);
    my $buildingInfo = getBuildingColumns();
    $template->param(
        mapImport   => 1,
        columnList  => \@importHeader,
        iid         => $iid,
        mode        => $mode,
        eqDefRecord => $eqDefRecord,
        eqDefItem   => $eqDefItem,
        eqBldInfo   => $buildingInfo,
        categories  => $categoryList,
        defaultHeaderMap   => $defaultHeaderMap,
    );

}


sub loadHeader {
    my ($headerLine) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
    $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,
        columnHeader => '--',
    };
    foreach my $columnHeader (@field) {#split /,/, $HeaderLine) {
        if ($columnHeader ne "") {
            push @headerList, {
                id           => $i,
                columnHeader => $columnHeader,
            };
            $i++;
        }
    }
    return @headerList;
}


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

    my $sth = $dbh->prepare(<<_STH_);
select  c.*, 
        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 $itemType=""; 
    for (my $i =0 ; $i < (scalar(@$eqDef)); $i++){
        $headerMap[$i] = int($input->{'field_'.$eqDef->[$i]->{'id'}})-1;
        if ($input->{'field_itemType'} && $input->{'field_itemType'} =~ m/(\d+)/) {
            $itemType = "";
        }
        else{
            $headerMap[$i] = int($input->{'field_'.$eqDef->[$i]->{'id'}})-1;                   
        }
    }
    my $hMap = join(",", @headerMap);
    $dbh->do("update eq_csvImport set mapHeader='$hMap',itemType='$itemType' where iid=$iid");
 
#note : comment out bc timeout issue so put them in queue for cronjob 
#if ($op eq 'import' ){
        #eq_csv_import($dbh,$iid);
#    }
    $template->param(
        selectCSVFile => 1 , 
    );
}

sub doImport_cancel {

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

sub getBuildingColumns {
    my ($dbh) = @_;
    my @list=(
        {id=>'bldCode',    name=>"Building Code",   value=> "code"},
        {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: