#!/usr/bin/perl -w

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

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

use Opals::User qw(
    user_LoadCategory
    user_currentUser
);


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();
#my $op = $input->{'op'};
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'user/tbk_importStuReg.tmpl',
            reqPermission   => 'user_add',
        }
);

my $sth; #need to delete unused data <<<<<<<<

# Get id of importer
my ($errCode, $ck, $currentUser) = user_currentUser($dbh, $cgi);
my $userfile = $cgi->param('userfile');
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 @DataHeader = qw(
    --
    courseCode
    sectionCode
    studentId
    notes
);

my @HeaderMap = ();
my @ImportHeader = ();

# User is required to select a data file to import
# It is when no file to upload or not import user
if (!$input->{'Upload'} && !$input->{'Import'}) {
NeedFile:
    $template->param(SelectUserFile => 1);
    tmpl_write($dbh, $cgi, $cookie, $template);
    goto __END_OF_FILE;
}


if ($input->{'Upload'}) {
    my $userData = '';

    #Copy file from Import file into user file
    my $firstline = <$userfile>;
    if (!$firstline || $firstline eq '') {
        $template->param(wrongfile => 1);
        goto NeedFile;
    }

    $userData = $firstline;

    while (<$userfile>) {
        $userData .= $_;
    }

    $userData = iso2utf8($userData);
    
 $sth = $dbh->prepare(<<_STH_);
insert into opl_userimport
set     uid        = ?,
        dateUpload = now(),
        data       = ?
_STH_
    $sth->execute($currentUser->{'uid'}, $userData);
    $sth->finish;

    $sth = $dbh->prepare(<<_STH_);
select  idimport
from    opl_userimport
where   uid  = ? &&
        data = ?
order by dateUpload desc
_STH_
    $sth->execute($currentUser->{'uid'}, $userData);
    my ($idimport) = $sth->fetchrow_array;
    $sth->finish;
    $template->param(idimport => $idimport);

    # Make header from the first line
    @ImportHeader = LoadHeader($firstline);

    # Load import preference
    my @brwrtype = user_LoadCategory($dbh);
    my @status = (0, 1);
    my @MapPref = LoadImportPref($template, $dbh, $input, \@ImportHeader, \@brwrtype, \@status);

   
    $template->param(
        category        => \@brwrtype,
        ColumnList      => \@ImportHeader,
        MapImportUser   => 1,
    );
}
else {
    my $idimport = $input->{'idimport'};
    $sth = $dbh->prepare(<<_STH_);
select  data
from    opl_userimport
where   idimport = $idimport
_STH_
    $sth->execute();
    my ($data) = $sth->fetchrow_array;
    $sth->finish;

    $dbh->do("update opl_userimport set dateImport = now() where idimport=$idimport");
    
    $data =~ s/\r\n/\n/g;
    my @userData = split(/\n/, $data);

    my $firstline = $userData[0];

    # Make header (data, import) from the first line
    @ImportHeader = LoadHeader($firstline);
#    MatchIndex(\@DataHeader, \@HeaderMap, $input);
    for (my $i = 0; $i < scalar(@DataHeader); $i++) {
        $HeaderMap[$i] = $input->{$DataHeader[$i]} - 1;
    }

    SaveImportPref($template, $dbh, $input, \@DataHeader, \@ImportHeader);
    
    
    # Prepare SQL command
    # The order of variables must be the same as in the DataHeader
    my $insertStr = $dbh->prepare(<<_STH_);
insert into  tbk_studentSchedule
set     courseCode        = ?,
        sectionCode       = ?,
        studentId         = ?,
        notes             = ?,
        created           = now()
_STH_

    # ignore username, password when import/update records
    my $query = <<_QRY_;
update tbk_studentSchedule
set     courseCode        = ?,
        sectionCode       = ?,
        studentId         = ?,
        notes           = concat_ws('\n--\n', notes, ?)

where   sectionCode       = ? && studentId = ?
_QRY_

    

    my $sth_id = $dbh->prepare("select max(id) from tbk_teachers");

    
        
      my $sth_studentId = $dbh->prepare(<<_STH_);
select  studentId
from    tbk_studentSchedule
where   sectionCode = ? && studentId= ?
_STH_
    my $catcode = $input->{'categorycode'};
    my $status = $input->{'status'};
    my $total = 0;

    for (my $i = 1; $i < scalar(@userData); $i++) {
        my $record = $userData[$i];
        my @rec = parseRecord($record, \@HeaderMap);
        if ( ImportData(\@rec,  $sth_id, $sth_studentId, $insertStr, $status, $catcode,$idimport) ) {
            $total++;
        }
    }
    $sth_id->finish;
    $insertStr->finish;
    $overwriteUser->finish;
    $sth_teacherId->finish;

    # Set info for the end page
    $template->param(FinishImport => 1, NumOfImport => $total);

    if (!$total) {
        $dbh->do("delete from opl_userimport where idimport=$idimport");
    }
    else {
        $dbh->do("update opl_userimport set countProcessed = $total where idimport = $idimport");
    }
}

$template->param(importUser => 1);
tmpl_write($dbh, $cgi, $cookie, $template);

#------------------------------------------------------
sub LoadImportPref {
    my ($template, $dbh, $input, $ImportHeaders, $arrCategory, $arrStatus) = @_;

    # Get the import option from dtbs
    my $szSQL       = "select val from opl_preference where var=?";
    my $sqlcomd     = $dbh->prepare($szSQL);
    my $bResult     = $sqlcomd->execute("iuset");
    my @arrFields   = $sqlcomd->fetchrow_array;
    my $bSetPref    = ($arrFields[0] eq '1' ) ? 1 : 0;
    $sqlcomd->finish;
    $template->param(ImportPref => $bSetPref);

    # Load the map string from dtbs, store in arrFields
    $szSQL          = "select val from opl_preference where var=?";
    $sqlcomd        = $dbh->prepare($szSQL);
    $bResult        = $sqlcomd->execute("iumap");
    @arrFields      = $sqlcomd->fetchrow_array;
    my $szFields    = $arrFields[0];
    $sqlcomd->finish;

    @arrFields = ();

    # split all fields in szFields into arrFields
    my @AllFields = split /,/, $szFields;
    my ($i, $j);
    my $dbQuotes = 0;
    for ($i=0, $j=0; $i<@AllFields; $i++, $j++) {
        if ($AllFields[$i] =~ m/^"/) {
            $dbQuotes = 1;
            $AllFields[$i] =~ s/^"//g;
            $arrFields[$j] = $AllFields[$i];
            for ($i++; $i<@AllFields && $dbQuotes==1; $i++) {
                if ($AllFields[$i] =~ m/"$/) {
                    $dbQuotes = 0 ;
                    $AllFields[$i] =~ s/"$//g;
                }
                $arrFields[$j] .= ',' . $AllFields[$i];
            }
            $i--;
        }
        else {
            $arrFields[$j] = $AllFields[$i];
        }
    }

    # Create data for function SetHighlight in Import template by scanning each
    # item of ImportHeader in arrFields. If there is, put its index into 
    # template
    my @arrMap = ();
    my $nNumOfMaps = scalar(@arrFields);
    my $nNumOfHeaders = scalar(@$ImportHeaders);
    my $map;
    for (my $i = 0; $i < $nNumOfMaps; $i += 2) {
        $map->{$arrFields[$i+1]} = $arrFields[$i];
    }

    foreach my $ih (@$ImportHeaders) {
        if ($map->{$ih->{'ColumnHeader'}}) {
            push @arrMap, {
                listName  => $map->{$ih->{'ColumnHeader'}},
                listIndex => $ih->{'Id'},
            };
        }
    }

    $template->param(ListOfIndex => \@arrMap);

    return @arrMap;
}

#------------------------------------------------------
sub SaveImportPref {
    my ($template, $dbh, $input, $arrDataHeaders, $arrImportHeaders) = @_;

    return unless $input->{'ImportPref'};

    # Create mapping string of pairs (data header, import header)
    my $nNumOfFields = scalar(@$arrDataHeaders);
    my ($szPref, $szData, $szImport);
    $szPref = '';
    for (my $i = 1; $i < $nNumOfFields; $i++) {
        # if user did a map
        $szData = $arrDataHeaders->[$i];
        if ($szData && $input->{$szData} > 0 &&
            $szData ne 'status' && $szData ne 'categorycode') {
            $szImport = '';
            
            my $idx = $input->{$szData};
            my $hh  = $arrImportHeaders->[$idx];
            $szImport = $hh->{'ColumnHeader'}; # for general map

            if ( $szData =~ m/,/ ) {
                $szPref .= '"' . $szData . '",';
            }
            else {
                $szPref .= $szData . ',';
            }
            $szPref .= $szImport . ',';
        }
    }
    $szPref =~ s/,$//;

    # Save the string to dtbs
    my $bResult = $dbh->do("update opl_preference set val=1 where var='iuset'");

    $sth = $dbh->prepare("update opl_preference set val=? where var='iumap'");
    $bResult = $sth->execute($szPref);
    $sth->finish;

    $template->param(title => $szPref);
    return $bResult;
}


#------------------------------------------------------
# Split $HeaderLine into pieces by comma,
# then order them from 1, 2, 3
sub LoadHeader {
    my ($HeaderLine) = @_;
    #$HeaderLine =~ s/[\n\r]//g;

    my $csv = Text::CSV_XS->new({ binary => 1 });
    #my $csv = Text::CSV::Unicode->new({ binary => 1 });
    $csv->parse($HeaderLine);
    my @field = $csv->fields();
    foreach my $f (@field) {
        $f =~ s/^\s+//;
	    $f =~ s/\s+$//;
    }

    my @FieldList = ();
    my $i = 1;

    push @FieldList, {
        Id           => 0,
        ColumnHeader => '--',
    };
    foreach my $ColumnHeader (@field) {#split /,/, $HeaderLine) {
        push @FieldList, {
            Id           => $i,
            ColumnHeader => $ColumnHeader,
        };
        $i++;
    }

    return @FieldList;
}

#------------------------------------------------------
# Get the index of listboxes on screen into array Map
#
#sub MatchIndex {
#    my ($OrderInList, $map, $input) = @_;
#
#    for (my $i = 0; $i < @$OrderInList; $i++) {
#        $map->[$i] = $input->{$OrderInList->[$i]} - 1;
#    }
#}

#------------------------------------------------------
# Fields in a record are splitted by comma
# In case there is a comma inside a field, that field will be exported
# with double quote at begining and ending
#
sub parseRecord {
    my ($szRecord, $MapIndex) = @_;

    my $csv = Text::CSV_XS->new({ binary => 1 });
    #my $csv = Text::CSV::Unicode->new({ binary => 1 });
    $csv->parse($szRecord);
    my @field = $csv->fields();
    # Remove
    foreach my $f (@field) {
        $f =~ s/^\s+//;
	    $f =~ s/\s+$//;
    }

    # Fill array data by fields
    my @dataField = ();

    for(my $i=0; $i < scalar(@{$MapIndex}); $i++) {
        if ($MapIndex->[$i] >= 0) {
            push @dataField, $field[$MapIndex->[$i]];
        }
        else {
            push @dataField, "";
        }
    }

    return @dataField;
}

#-----------------------------------------------------
sub ImportData {
    my ($dataRec,  $sth_id, $sth_studentId, $insertStr, $status,  
         $catcode, $idimport) = @_;
 
    my ($dupUsername, $dupStuSec);
    $sth_studentId->execute($dataRec->[2],$dataRec->[3]);
    ($dupStuSec) = $sth_studentId->fetchrow_array;
    

    my $bResult = $sth_id->execute();
    my ($uid) = $sth_id->fetchrow_array;
    $uid++;

     # Teacher ID: set to null if empty
    if ($dataRec->[1] eq '') {
        undef $dataRec->[1];
    }
    
    my $gender = $dataRec->[13];
    if(defined $gender ){
        if($gender =~ m/^0$/ ||
           $gender =~ m/^f$/i ||
           $gender =~ m/^female$/i ){
             $gender=0;
        }
        elsif ($gender =~ m/^1$/ ||
            $gender =~ m/^m$/i ||
            $gender =~ m/^male$/i ){
            $gender=1;
        }
        else{
            $gender = undef;
        }
   
     }

    # ignore username, password when import/update records
    # username: $dataRec->[2]
    if ($dupStuSec) {        
               
       # $gender = (defined $gender && $gender =~ m/^0+$/) ? 0 : 1;
        
        my @updateData = (
            $idimport,
            $dataRec->[1],
            $dataRec->[2],
            $dataRec->[3],
            $dataRec->[4],
         );
            
            push @updateData, $dataRec->[2];
            push @updateData, $dataRec->[3];

           #Ha  $sth_uid_by_sid->execute($dataRec->[1]);
           #Ha  ($uid) = $sth_uid_by_sid->fetchrow_array;
        
        $bResult = $overwriteUser->execute(@updateData);
    }
    else {
        $bResult = $insertStr->execute(
            $idimport,
            $dataRec->[1],
            $dataRec->[2],
            $dataRec->[3],
            $dataRec->[4]
       );
    }


    if (!$bResult) {
        return;
    }

    my @gData;
    my $hasData;
    
    
    return 1;
}

# Written by Marc Liyanage (http://www.entropy.ch)
# http://www.entropy.ch/software/perl/
#

#print iso2utf8(join("", <>));

sub iso2utf8 {

        my ($string) = @_;
                my %cache = ();

                # Match characters with their high bit set (0x80-0xff).
                # Send these to the encode function and replace
                # them with the result of the function.
                #
        $string =~ s/([\x80-\xff])/$cache{$1} ||= encode_iso($1)/eg;
        return $string;

}

sub encode_iso {

        # Take the integer value of the ISO character
        # using the ord() function
        #
        my $iso = ord($_[0]);

        # Take the two high bits (0xc0) of the iso value.
        # Shift them to the right by 6 positions and OR
        # it with the UTF-8 bit pattern for a two-byte sequence
        # (110xxxxx or 0xc0). This gives the leading byte of the
        # 2-byte UTF-8 sequence.
        #
        # Then take the ISO value again, keep the 6 lower bits
        # (0x3f) and OR the resulting value with the bit pattern
        # for an UTF-8 trailing byte (10xxxxxx or 0x80).
        # This is the second byte of the UTF-8 sequence.
        #
        my $a = ((($iso & 0xc0) >> 6) | 0xc0);
        my $b = ($iso & 0x3f) | 0x80;


        # Look up the character for the two bytes, concatenate them
        # and return this two-character sequence
        #
        return chr($a) . chr($b);

}


__END_OF_FILE: