#!/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_importStudent.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(
    --
    studentId
    userBarcode
    firstName
    lastName
    addrLine1
    addrLine2
    city
    state
    zip
    phone
    fax
    email
    birthday
    gender
    status
    homeroom
    buildingCode
    teacher
    grade
    yeargraduation
    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;
}

my $overwrittenByBarcode = $input->{'overwrittenByBarcode'};
# If the import file is selected, the column headers is extracted from user 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);
    
    my $barcodeIsKey = ($input->{'overwrittenByBarcode'}) ? 1:0;
    SetOverwriteByBarcode($dbh, $barcodeIsKey);

    # Prepare SQL command
    # The order of variables must be the same as in the DataHeader
    my ($sth_guardianDelSid, $sth_guardianDelBarcode);
    my $insertUser = $dbh->prepare(<<_STH_);
insert into  tbk_students
set     importId        = ?,
        studentId       = ?,
        userBarcode     = ?,
        firstName       = ?,
        lastName        = ?,
        addrLine1       = ?,
        addrLine2       = ?,
        city            = ?,
        state           = ?,
        zip             = ?,
        phone           = ?,
        fax             = ?,
        email           = ?,
        birthday        = ?,
        gender          = ?,
        status          = ?,
        homeroom        = ?,
        building        = ?,
        teacher         = ?,
        grade           = ?,
        yeargraduation  = ?,
        notes           = ?,
        created         = now()
_STH_

    # ignore username, password when import/update records
    my $query = <<_QRY_;
update tbk_students
set     importId        = ?,
        studentId       = ?,
        userBarcode     = ?,
        firstName       = ?,
        lastName        = ?,
        addrLine1       = ?,
        addrLine2       = ?,
        city            = ?,
        state           = ?,
        zip             = ?,
        phone           = ?,
        fax             = ?,
        email           = ?,
        birthday        = ?,
        gender          = ?,
        status          = ?,
        homeroom        = ?,
        building        = ?,
        teacher         = ?,
        grade           = ?,
        yeargraduation  = ?,
        notes           = concat_ws('\n--\n', notes, ?)
_QRY_

    if ($overwrittenByBarcode) {
        $query .= <<_QRY_;
where   userBarcode = ?
_QRY_

    }
    else {
        $query .= <<_QRY_;
where   studentId = ?
_QRY_

    }
    my $overwriteUser = $dbh->prepare($query);

    my $sth_uid = $dbh->prepare("select max(id) from tbk_students");

    
       my $sth_sid = $dbh->prepare(<<_STH_);
select  count(*)
from    tbk_students
where   studentId = ?
_STH_
    my $sth_bc = $dbh->prepare(<<_STH_);
select  count(*)
from    tbk_students
where   userBarcode = ?
_STH_

    my $sth_uid_by_barcode = $dbh->prepare(<<_STH_);
select  studentId
from    tbk_students
where   userbarcode = ?
_STH_
    my $sth_uid_by_sid = $dbh->prepare(<<_STH_);
select  studentId
from    tbk_students
where   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_sid, $sth_bc, $insertUser, $overwriteUser,   $status, $catcode, $sth_uid, $idimport, $overwrittenByBarcode, $sth_uid_by_barcode, $sth_uid_by_sid) ) {
            $total++;
        }
    }
    $sth_sid->finish;
    $sth_bc->finish;
    $insertUser->finish;
    $overwriteUser->finish;
    $sth_uid->finish;
    $sth_uid_by_barcode->finish;
    $sth_uid_by_sid->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;
}

################################################################################
sub SetOverwriteByBarcode {
    my ($dbh, $barcodeIsKey) = @_;
    $dbh->do(<<_QRY_);
update  opl_preference
set     val = $barcodeIsKey
where   var = 'overwrittenByBarcode'
_QRY_
}

#------------------------------------------------------
# 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 parseRecord
#{
#    my ($szRecord, $MapIndex) = @_;
#    my @recordParsed = ();#    return @recordParsed;
#
#    # Split fields in record
#    my @AllFields = split /,/, $szRecord;
#
#    # Remove
#    foreach my $f (@AllFields) {
#        $f =~ s/^\s+//;
#	    $f =~ s/\s+$//;
#        $f =~ s/^"([^\"]*)"$/$1/;
#    }
#
#    my ($i, $j);
#    my $dbQuotes = 0;
#    my $NumOfFields = @AllFields;
#    for ($i=0, $j=0; $i<$NumOfFields; $i++, $j++)
#    {
#        if ($AllFields[$i] =~ m/^"/)
#        {
#            $dbQuotes = 1;
#            $AllFields[$i] =~ s/^"//g;
#            $AllFields[$i] =~ s/^\s+//;
#            $recordParsed[$j] = $AllFields[$i];
#            for ($i++; $i<$NumOfFields && $dbQuotes==1; $i++)
#            {
#	            if ($AllFields[$i] =~ m/"$/)
#                {
#	                $dbQuotes = 0 ;
#	                $AllFields[$i] =~ s/"$//g;
#	                $AllFields[$i] =~ s/\s+$//;
#	            }
#	            $recordParsed[$j] .= ',' . $AllFields[$i];
#            }
#            $i--;
#        }
#        else
#        {
#            $recordParsed[$j] = $AllFields[$i];
#        }
#    }
#
#    # Fill array data by fields
#    my @dataField = ();
#
#    for(my $i=0; $i < @$MapIndex; $i++)
#    {
#        if ( $MapIndex->[$i] >= 0 )
#        {
#            push @dataField, $recordParsed[$MapIndex->[$i]];
#        }
#        else
#        {
#            push @dataField, "";
#        }
#    }
#
#    return @dataField;
#}

#-----------------------------------------------------
sub ImportData {
    my ($dataRec,  $sth_sid, $sth_bc, $insertUser, $overwriteUser,  $status, $catcode, $sth_uid,  $idimport, $overwrittenByBarcode, $sth_uid_by_barcode, $sth_uid_by_sid) = @_;

    my ($dupUsername, $dupSid, $dupBarcode);
    #Ha $sth_un->execute($dataRec->[1]);
    $sth_sid->execute($dataRec->[1]);
    $sth_bc->execute($dataRec->[2]);
    #Ha ($dupUsername) = $sth_un->fetchrow_array;
    if ($overwrittenByBarcode) {
        ($dupBarcode) = $sth_bc->fetchrow_array;
    }
    else {
        ($dupSid) = $sth_sid->fetchrow_array;
    }

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

     # Student ID: set to null if empty
    if ($dataRec->[1] eq '') {
        undef $dataRec->[1];
    }
    # User barcode: set to null if empty
    if ($dataRec->[2] eq '') {
        undef $dataRec->[2];
    }
    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 ($dupSid || $dupBarcode) {        
               
       # $gender = (defined $gender && $gender =~ m/^0+$/) ? 0 : 1;
        
        my @updateData = (
            $idimport,
            $dataRec->[1],
            $dataRec->[2],
            $dataRec->[3],
            $dataRec->[4],
            $dataRec->[5],
            $dataRec->[6],
            $dataRec->[7],
            $dataRec->[8],
            $dataRec->[9],
            $dataRec->[10],
            $dataRec->[11],
            $dataRec->[12],
            $dataRec->[13],
            $gender,
            $status,
            $dataRec->[16],
            $dataRec->[17],
            $dataRec->[18],
            $dataRec->[19],
            $dataRec->[20],
            $dataRec->[21],
        );
        if ($overwrittenByBarcode) {
            push @updateData, $dataRec->[2];
            #Ha $sth_guardianDelBarcode->execute($dataRec->[3]);
            $sth_uid_by_barcode->execute($dataRec->[2]);
            ($uid) = $sth_uid_by_barcode->fetchrow_array;
        }
        else {
            push @updateData, $dataRec->[1];
            #Ha $sth_guardianDelSid->execute($dataRec->[2]);
            $sth_uid_by_sid->execute($dataRec->[1]);
            ($uid) = $sth_uid_by_sid->fetchrow_array;
        }
        $bResult = $overwriteUser->execute(@updateData);
    }
    else {
        $bResult = $insertUser->execute(
            $idimport,
            $dataRec->[1],
            $dataRec->[2],
            $dataRec->[3],
            $dataRec->[4],
            $dataRec->[5],
            $dataRec->[6],
            $dataRec->[7],
            $dataRec->[8],
            $dataRec->[9],
            $dataRec->[10],
            $dataRec->[11],
            $dataRec->[12],
            $dataRec->[13],
            $gender,
            $status,
            $dataRec->[16],
            $dataRec->[17],
            $dataRec->[18],
            $dataRec->[19],
            $dataRec->[20],
            $dataRec->[21]
        );
    }



    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: