#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Opals::Context;
use JSON;
use Text::CSV_XS;
use Opals::Date qw(
    date_parse
);

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


my $request = _getRequest();



my $cgi = CGI->new;
=item
my $userTblFields={ "uid"=>1, "sid"=>1,"title"=>1,"firstname"=>1,"middlename"=>1,"lastname"=>1,"nickname"=>1,"fullname"=>1,
                    "birthday"=>1,"gender"=>1,"created"=>1,"modified"=>1,"userbarcode"=>1, "username"=>1,  "categorycode"=>1 ,"permissions"=>1,
                    "expirydate"=>1,"biometricLnk"=>1,"digitalSign"=>1,"email"=>1,"phone"=>1,"workPhone"=>1,"cellPhone"=>1,
                    "fax"=>1,"addrLine1"=>1,"addrLine2"=>1,"city"=>1,"zip"=>1,"state"=>1,"country"=>1,"neighborhood"=>1,"addrLine1_alt"=>1,"addrLine2_alt"=>1,
                    "city_alt"=>1,"zip_alt"=>1,"state_alt"=>1,"country_alt"=>1,"neighborhood_alt"=>1,"addrExpiry_alt"=>1,
                    "status"=>1,"buildingcode"=>1,"homeroom"=>1,"teacher"=>1,"grade"=>1,"yeargraduation"=>1,"notes"=>1,
                    "pref_lang"=>1,"pref_contact"=>1,"sysCode"=>1,"libCode"=>1,"program"=>1,"studies"=>1,"permanent"=>1,"fulltime"=>1,"incomplete"=>1};
=cut                    
my $userTblFields={ "sid"=>1,"title"=>1,"firstname"=>1,"middlename"=>1,"lastname"=>1,"fullname"=>1,
                    "birthday"=>1,"gender"=>1,"userbarcode"=>1, "username"=>1, "district"=>1,"schoolName"=>1, 
                    "expirydate"=>1,"biometricLnk"=>1,"digitalSign"=>1,"email"=>1,"phone"=>1,"workPhone"=>1,"cellPhone"=>1,
                    "fax"=>1,"addrLine1"=>1,"addrLine2"=>1,"city"=>1,"zip"=>1,"state"=>1,"country"=>1,"neighborhood"=>1,"addrLine1_alt"=>1,"addrLine2_alt"=>1,
                    "city_alt"=>1,"zip_alt"=>1,"state_alt"=>1,"country_alt"=>1,"neighborhood_alt"=>1,"addrExpiry_alt"=>1,
                    "buildingcode"=>1,"homeroom"=>1,"teacher"=>1,"grade"=>1,"yeargraduation"=>1,"notes"=>1,"privateNotes"=>1,
                    "pref_lang"=>1,"pref_contact"=>1,"sysCode"=>1,"libCode"=>1,"program"=>1,"incomplete"=>1};


my $guardianTblFields={"gid"=>1,"gtitle"=>1,"gfirstname"=>1,"glastname"=>1,"gaddrLine1"=>1,"gaddrLine2"=>1,"gcity"=>1,
                        "gzip"=>1,"gstate"=>1,"gcountry"=>1,"gphone"=>1,"gcellphone"=>1,"gfax"=>1,"gemail"=>1,"uid"=>1,"role"=>1};


my $fieldMap     =$request->{"fieldMap"};
my $status       =$request->{"status"};
my $categorycode =$request->{"categorycode"};
my $studies      =$request->{"studies"};
my $permanent    =$request->{"permanent"};
my $fulltime     =$request->{"fulltime"};

if($request->{"saveMapping"} eq 'true'){
    saveImportPref($dbh,$fieldMap,$request->{"overwrittenByBarcode"});
}
my $uData       =getImportData($dbh,$request->{"importId"});
my $userFMap    =getImportFieldMap_user($fieldMap);
my $guardianFMap=getImportFieldMap_guardian($fieldMap);

my $idimport=$request->{"importId"};
my $countImport=importUser($dbh,$uData,$userFMap,$guardianFMap,$idimport);
 $dbh->do("update opl_userimport set dateImport = now(),countProcessed=$countImport where idimport=$idimport");



print $cgi->header(
        -type    => 'text/plain',
        -charset => 'utf-8'
    );

#print to_json({countImport=>$countImport});
print to_json({countImport=>$countImport});
#------------------------------------------------------------------------------
sub importUser{
    my ($dbh,$data,$userFMap, $guardianFMap,$importId)=@_;
    my $csv = Text::CSV_XS->new({ binary => 1 });

    my @uFieldNameArr =();
    my @uFieldIndexArr=();
    foreach my $f(keys %$userFMap){
        if($f eq 'notes' ||$f eq 'privateNotes'){
            if($userFMap->{$f} >=0){
                push @uFieldNameArr,"$f=if($f is null,?,concat($f,'\\n-- ',?)) ";
                push @uFieldIndexArr,$userFMap->{$f};
                push @uFieldIndexArr,$userFMap->{$f};
            }
        }
        else{
            push @uFieldNameArr,"$f=? ";
            push @uFieldIndexArr,$userFMap->{$f};
        }
    }

    #TABLE opl_user
    my $sthInsert_u= $dbh->prepare("insert into opl_user set " .  join(",",@uFieldNameArr) . ", status=?,categorycode =?, 
                                                         studies= ?, permanent=?,fulltime=?,
                                                         idimport=?,created=now(),modified=now()" );
    my $sthUpdate_u= $dbh->prepare("update  opl_user set " .  join(",",@uFieldNameArr) . ", status=?,categorycode =?, 
                                                         studies= ?, permanent=?,fulltime=?,
                                                         idimport=?,modified=now() where uid=?" );
    my $sthdelet_g= $dbh->prepare("delete from   opl_guardian  where uid=?" );

    my @sthInsert_g= ();
    my @fieldIndex_g=();
    foreach my $r(@$guardianFMap){
        my $fNameArr=[];
        my $fIndxArr=[];
        foreach my $f(keys %$r){
            push @$fNameArr,"$f=? ";
            push @$fIndxArr,$r->{$f};
        }
        my $sth =$dbh->prepare("insert into opl_guardian set " .  join(",", @$fNameArr) .", uid=? ");
        push @sthInsert_g,$sth;
        push @fieldIndex_g,$fIndxArr;
    }


    my $sth_uid=undef;
    my $sidUbcInx=-1;
    my $genderIdx=-1;
    my $countImport=0;
    if(defined $userFMap->{"gender"}){
        $genderIdx=$userFMap->{"gender"};
    }
    if($request->{"overwrittenByBarcode"} && defined $userFMap->{"userbarcode"}){
        $sth_uid=$dbh->prepare("select uid from opl_user where userbarcode=?");
        $sidUbcInx=$userFMap->{"userbarcode"};
    }
    elsif(defined $userFMap->{"sid"}){
        $sth_uid=$dbh->prepare("select uid from opl_user where sid=?");
        $sidUbcInx=$userFMap->{"sid"};
    }
    splice @$data,0,1;
    foreach my $row (@$data){
        $csv->parse($row);
        my @fields = $csv->fields();

        my $uData=getDataFields(\@fields,\@uFieldIndexArr,$userFMap);

        my @gData=();
        my $uid = undef;
        if(defined $sth_uid){
            $sth_uid->execute($fields[$sidUbcInx]);
            ($uid)=$sth_uid->fetchrow_array;
        }
        if(defined $uid){
            $sthUpdate_u->execute(@$uData,$status,$categorycode,$studies,$permanent,$fulltime,$importId,$uid);
        }
        else{
            $sthInsert_u->execute(@$uData,$status,$categorycode,$studies,$permanent,$fulltime,$importId);
            $uid=$dbh->{'mysql_insertid'};
        }
        # insert guardians
        $sthdelet_g->execute($uid);
        for(my $i=0;$i<scalar(@sthInsert_g);$i++){
            my $gData =getDataFields(\@fields,$fieldIndex_g[$i],$userFMap);
            my $sth=$sthInsert_g[$i];
            $sth->execute(@$gData,$uid);
        }
        $countImport++;

    }
   return $countImport;
}

#------------------------------------------------------------------------------
sub getDataFields{
    my($fields,$fIndex,$userFMap)=@_;
    my @uData=();
    my($genderIdx,$expiryDateIdx,$birthdayIdx,$addrExpiry_altIdx)=(undef,undef,undef,undef);
    if(defined $userFMap->{"gender"}){
        $genderIdx=$userFMap->{"gender"};
    }
    if(defined $userFMap->{"expirydate"}){
        $expiryDateIdx=$userFMap->{"expirydate"};
    }
    if(defined $userFMap->{"birthday"}){
        $birthdayIdx=$userFMap->{"birthday"};
    }
    if(defined $userFMap->{"addrExpiry_alt"}){
        $addrExpiry_altIdx=$userFMap->{"addrExpiry_alt"};
    }

    foreach my $i (@$fIndex){
        my $val=undef;
        if($i>=0){
            $val=@$fields[$i];
            $val=~ s/<NEWLINE>/\r/g;
            if(defined $genderIdx && $genderIdx == $i){
                $val=getGenderVal($val);
            }
            elsif((defined $expiryDateIdx && $expiryDateIdx==$i)
                   ||(defined $birthdayIdx && $birthdayIdx==$i)
                   ||(defined $addrExpiry_altIdx && $addrExpiry_altIdx==$i)
                   ){
                $val=date_parse($val);
            }
        }
        push @uData,$val;
   }
   return \@uData;

}


#------------------------------------------------------------------------------
sub getImportData{
    my($dbh,$importId)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select  data
from    opl_userimport
where   idimport = $importId

_STH_

    $sth->execute();
    my ($data) = $sth->fetchrow_array;
    $sth->finish;
    my @userData = split(/\r\n/, $data);
       return \@userData;
     
}

#------------------------------------------------------------------------------
sub getImportFieldMap_user{
    my($fieldMap)=@_;
    my $ret={};

    foreach my $f(keys %$userTblFields){
        if(defined $fieldMap->{$f}){
            $ret->{$f}=$fieldMap->{$f}->{"index"};
        }
        else{
            $ret->{$f}=-1;
        }
    }
    return $ret;
}
#------------------------------------------------------------------------------
sub getImportFieldMap_user_bk{
    my($fieldMap)=@_;
    my $ret={};
    foreach my $f(keys %$fieldMap){
        if(defined $userTblFields->{$f}){
            $ret->{$f}=$fieldMap->{$f}->{"index"};
        }
        else{
            $ret->{$f}=-1;
        }
    }
    return $ret;
}

#------------------------------------------------------------------------------
sub getImportFieldMap_guardian{
    my($fieldMap)=@_;
    my $ret=[];
    foreach my $gIdx qw(g0 g1 g2){
        next if(!defined $fieldMap->{$gIdx});
        my $fm={};
        foreach my $f(keys %{$fieldMap->{$gIdx}}){
            if(defined $guardianTblFields->{$f}){
                $fm->{$f}=$fieldMap->{$gIdx}->{$f}->{"index"};
            }
        }
        push @$ret,$fm;
    }
    return $ret;
}


#------------------------------------------------------------------------------
sub getGenderVal{
    my($genderStr)=@_;
    my $val=undef;
    if($genderStr =~ m/^0$/ ||
           $genderStr =~ m/^f$/i ||
           $genderStr =~ m/^female$/i ){
           $val=0;
        }
    elsif ($genderStr =~ m/^1$/ ||
            $genderStr =~ m/^m$/i ||
            $genderStr =~ m/^male$/i ){
            $val=1;
    }
    
    return $val;

}
#------------------------------------------------------------------------------


sub _getRequest{
    my $request =undef;
    if ($ENV{'REQUEST_METHOD'} eq "POST") {
        my $json ="";
        while (<STDIN>) {
            $json .= $_;
        }
         $request = decode_json($json) if($json ne ""); 
    }
    return $request;
}
#------------------------------------------------------------------------------
sub saveImportPref {
    my($dbh,$fieldMap,$overwrittenByBarcode)=@_;
    my $fMap={};
    foreach my $f(keys %$fieldMap){
        if($f =~ m/g0|g1|g3/g){
            foreach my $g(keys %{$fieldMap->{$f}}){
                $fMap->{$f}->{$g}= $fieldMap->{$f}->{$g}->{"srcField"};
            }
        }
        else{
            $fMap->{$f}=$fieldMap->{$f}->{"srcField"};
        }

    }

    my $jsonStr = to_json($fMap);
    $dbh->do("update opl_preference set val=1 where var='iuset'");
    $dbh->do("update  opl_preference set val = $overwrittenByBarcode where  var='overwrittenByBarcode'");

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

#------------------------------------------------------------------------------