#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;

use Digest::SHA qw(
    sha512_hex
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference

);
use Text::CSV_XS;

use Date::Calc qw(Today Add_Delta_Days Day_of_Week Week_Number Day_of_Year);
use Time::localtime;
use Opals::Tb_Record qw(
    
    tb_itemType_getList
    tb_subfieldCode
    mapData2SubfieldCode

);

use Opals::Tb_Import qw(
    tb_csv_import
);

use Opals::Search qw(
 
    srch_F852Default_marc21

);

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

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 $sth;
my $maxImport = 100;
my @subfieldCode = tb_subfieldCode();

my @headerMap =();
my $op = $input->{'op'};

my $csvfile = $cgi->param('csvfile');
my $default852 = srch_F852Default_marc21($dbh);
my $sCode =  $default852->subfield('a');
my $lCode =  $default852->subfield('b');
my $selItemType = $input->{'itemType'};
    ($selItemType || $selItemType eq "");
my $chkSCode = $input->{'chkSCode'};
my $chkLCode = $input->{'chkLCode'};
    ($chkSCode || $chkSCode eq "");
    ($chkLCode || $chkLCode eq "");
my $uid = $template->param('curUserId');
my $noHolding = $input->{'noHolding'};
    ($noHolding || $noHolding eq "0");

my $tbItemTypeList = tb_itemType_getList($dbh);
my $importedList;    
my $mesgDigest;


if ( !$op || $op eq "" ){
    $template->param(
        selectCSVFile => 1 , 
    );
}
elsif ($op eq 'load' && $csvfile){

    foreach my $i (@$tbItemTypeList){
        if ($i->{'id'} eq  $selItemType ){
            $i->{'sel'} = 1;
            last;
        }
    }
    my $tmpFile=`mktemp`;
    my $csvData = '';
    open TMPFILE, ">$tmpFile";
    #Copy file from Import file into user file
    my $firstline = <$csvfile>;
    if (!$firstline || $firstline eq '') {
        $template->param(
            wrongFile => 1,
            selectCSVFile => 1 ,
            );
        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/$tmpFile: //'`;
    unlink($tmpFile);
    if ($fileType  !~ 'text') {
        $template->param(
            filename    =>  $csvfile,
            unsupportedFile => 1,
            selectCSVFile => 1 ,
            );
        goto NeedFile;
    }
    $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    tb_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 tb_csvImport
set uid = $uid,
    sCode       = ?,
    lCode       = ?,
    itemType    = ?,
    filename    = ?,
    header      = ?,
    countTotalHolding  = ?,
    dateUpload = now(),
    status = 'accepted',
    mesgDigest  = ?,
    data    =?
_STH_
    $sth->execute($chkSCode,$chkLCode,$selItemType,$csvfile, $firstline, $countTotal, $mesgDigest, $csvData);
    my $iid = $dbh->{'mysql_insertid'};
    $sth->finish();
    my @importHeader = loadHeader($firstline);
    $template->param(
        columnList      => \@importHeader,
        iid             => $iid,
        mapImportRecCSV => 1,
        mapOnly         => $countTotal > $maxImport ? 1 :0,
    );
}
elsif ($op && ($op eq 'import' || $op eq 'map' ) && $input->{'iid'}){

    my $iid = $input->{'iid'};  
    for (my $i=0; $i < scalar(@subfieldCode); $i++){
        $headerMap[$i] = $input->{'sf'.$subfieldCode[$i]} - 1 ;
    }
    my $itemType = $input->{'sfitemtype'};
    my $chkSCode = $input->{'chkSCode'};
    my $chkLCode = $input->{'chkLCode'};

    my $hMap = join( ",", @headerMap);
    my $nRec  = countRecord($dbh,$iid,$hMap);
    if ($noHolding eq '1'){
        $dbh->do("update tb_csvImport set mapHeader ='$hMap',countTotal ='$nRec',itemType ='$itemType',sCode ='$sCode',lCode ='$lCode', noHolding='$noHolding', countTotalHolding = 0 where iid = $iid ");
    }
    else{
        $dbh->do("update tb_csvImport set mapHeader ='$hMap',countTotal ='$nRec',itemType ='$itemType',sCode ='$sCode',lCode ='$lCode', noHolding='$noHolding' where iid = $iid ");
    }


    if ($op eq 'import' ){
        tb_csv_import($dbh,$iid);
    }
    $template->param(
        selectCSVFile => 1 , 
    );
}
elsif ($op && $op eq 'cancel'){
    
    $dbh->do("delete from tb_csvImport where iid = $input->{'iid'} limit 1");
    $template->param(
        selectCSVFile => 1 , 
    );
}
elsif ($op && $op eq 'refresh'){

    $template->param(
        selectCSVFile => 1 , 
    );

}
NeedFile:
    $op = "";
    $importedList = getImportedList ($dbh);
    
    $template->param(
        tbItemTypeList  => $tbItemTypeList,
        importedList    => $importedList, 
        sCode           => $sCode,
        lCode           => $lCode,    
        chkSCode        => $chkSCode eq "" ? 0 :1,
        chkLCode        => $chkLCode eq "" ? 0 :1,
    );
    
tmpl_write($dbh, $cgi, $cookie, $template);
#$dbh->disconnect();
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 countRecord {
    my ($dbh, $iid, $mapHeader) = @_;
    $sth = $dbh->prepare(<<_STH_);
select  data 
from    tb_csvImport 
where   iid = ? 
_STH_
    $sth->execute($iid);
    my ($curTitle, $curISBN) =("","") ;
    my ($data) = $sth->fetchrow_array;
    $data =~ s/\r\n/\n/g;
    my @csvData = split(/\n/, $data);
    my @map = split(/,/,$mapHeader);
    my $count = 0;
    for ( my $i = 1; $i < scalar(@csvData); $i++) {
        my $rec = $csvData[$i];
        my $record = mapData2SubfieldCode($rec, \@map);
        if ( $record->{'245_a'} ne $curTitle && $record->{'245_a'} ne "") {
            $curTitle = $record->{'245_a'};
            $count++;
        }
    }
    $sth->finish();

    return $count;
}

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

    my $sth = $dbh->prepare(<<_STH_);
select  c.*, 
        u.uid, u.firstname, u.lastname, 
        (countTotal - countImported) as countImporting
from    tb_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    tb_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;
}


__END_OF_FILE: