#!/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

);

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'};
open debug, ">/tmp/debugK";


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

    my $tmpFile = `mktemp`;
    my $csvData = '';
    open TMPFILE, ">$tmpFile";
    my $firstline = <$csvfile>;
    if (!$firstline || $firstline eq ''){
        $template->param(
            wrongFile       => 1,
            selectCSVFile   => 1,
        );
        close TMPFILE;
        goto NeedFile;
    }
    $csvData = $firstline;
    my $countTotal = 0;
    while(<$csvfile>) {
        $countTotal++;
        $csvData .= $_;
    }
    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 = 'accepted',
    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,
        eqDefRecord => $eqDefRecord,
        eqDefItem   => $eqDefItem,
        eqBldInfo   => $buildingInfo,
        categories  => $categoryList
    );
}
elsif ($op && ($op eq 'import' || $op eq 'map' ) && $input->{'iid'}){
=item    
    my $iid = $input->{'iid'};
    my $itemType=""; 
    print debug "op:$op \t iid : $iid \n";
    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+)/) {
            print debug "ABC:",  $input->{'field_itemType'}, "\n";
            $itemType = "from import File";
        }
        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");
    if ($op eq 'import' ){
        print debug "call eq_csv_import $iid \n";
        eq_csv_import($dbh,$iid);
    }
    $template->param(
        selectCSVFile => 1 , 
    );
=cut
    doImport_map-import();

}
elsif ($op && $op eq 'cancel'){
    doImport_cancel($dbh);
}


close debug;
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,
    );

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


goto __END_OF_FILE;


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_map-import {

    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 = "from import File";
        }
        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");
    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
}




__END_OF_FILE: