package	Opals::User;


use	Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw();
# Symbols to be exported on request
@EXPORT_OK = qw(
    user_login
    user_logout
    user_add
    user_add_1

    user_addQuick
    user_addQuick_1

    user_getILLUser
    
    user_update
    user_update_1

    user_delete
    
    user_status
    user_disable
    user_balance
    user_paid
    user_paymenthistory
    user_getInformation
    user_getInformationByBc
    user_getInformationById
    user_permission
    user_permission_1
    user_category

    user_teacherList

    user_currentUser
    user_currentUserID
    user_list
    user_list_ext
    user_browse_ext
    
    user_LoadCategory
    user_LoadCategory_1

    user_listPermission
    user_listPermission_1
    user_StrPermission
    user_StrPermission_1

    user_isUserBc
    user_existUid

    user_searchByName
    user_searchByTeacher
    user_searchByBuilding
    user_searchByGrade
    user_searchByHomeroom
    user_updatePermissionByUserType
    user_setGender


user_getFamilyMember
user_getUserList
user_getUserCircCount
user_getUserCircCount_textbook
user_getUserCircCount_equipment
user_save
user_getHR_Grade_TeacherList
user_accessAllowed
user_getUserCircInfo
);
use Opals::UrlRegistry qw(
    ureg_getUrlRegistry
);
# Version number
$VERSION   = 0.01;


#use utf8;
use strict;
#use Constant;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);
use Opals::Transaction qw(
    trans_getBalance
    trans_getUnpaidChargeList
);
use Opals::Tb_Transactions qw(
    trans_tb_getBalance
);
# sha256_base64 sha384_base64 sha512_base64);
use Time::HiRes qw(
    time
);

use Opals::Context;
use Opals::Constant;

use Opals::Utility qw(
    util_resizeImg
);
use Opals::Date qw(
    date_text
    date_DHM_text
);
use POSIX qw(
    floor
);
use Opals::Circulation qw(
    circ_cancelHoldReserve
    circ_userListLoan
    circ_userListReserve
    circ_isOnReserve
);

use Time::localtime;    
# Permission constant
#Constant::Hash  my %posOf => (
my %posOf = (
    user_add        => 0,
    user_edit       => 1,
    marc_edit       => 2,
    pref_edit       => 3,
    bibsrc          => 4,
    circ_loan       => 5,
    circ_return     => 6,
    circ_rsrv_self  => 7,
    circ_rsrv       => 8,
    report          => 9,
    user_delete     => 10,
    fine            => 11,
    notice          => 12,
    equipment_mgmt  => 13,
    textbook_mgmt   => 14,
    #ill_edit        => 13,
    #ill_request     => 14,
    #ill_fill        => 15,
    #textbook_mgmt   => 16,
);

#-------------------------------------------------------------
 my $field_personInfo_del = ["uid", "sid","title","firstname","middlename","lastname","nickname","fullname","birthday","gender",
                            "created","modified","userbarcode", "username" ,"password","categorycode" ,"permissions","pw_timestamp",
                            "expirydate","biometricLnk","digitalSign","email","phone","workPhone","cellphone",
                            "fax","addrLine1","addrLine2","city","zip","state","country","neighborhood","addrLine1_alt","addrLine2_alt",
                            "city_alt","zip_alt","state_alt","country_alt","neighborhood_alt","addrExpiry_alt",
                            "status","buildingcode","homeroom","teacher","grade","yeargraduation","notes","privateNotes",
                            "pref_lang","pref_contact","sysCode","libCode","program","studies","permanent","fulltime","incomplete" ,
                            "issueBy", "identification","residency","residency_alt","district","schoolName"];


my $field_contactInfo_del =["gtitle","gfirstname","glastname","gaddrLine1","gaddrLine2","gcity","gzip","gstate","gcountry","gphone","gcellphone","gfax","gemail","role"];
#-----------------------------------------------------------------------------------------------
# return value:
# - 0: successful
# - 1: username missing
# - 2: username invalid
# - 3: password invalid

sub user_login{
    my ($dbh, $cgi,$application) = @_;
    my @cookie;
    my ($username, $password, $sessionID);
    if($cgi){
        $username = $cgi->param('username');
        $password = $cgi->param('password');
    }
        # Tuesday, May 23,2006
        # for application authentication
    elsif($application){
        $username = $application->{'username'};
        $password = $application->{'password'};
    }
    unless ($username) {
        return (1, undef, undef);
    }
    my ($thePretender, $userIP ,$auth)=(0,'',0);
    $userIP = $ENV{'REMOTE_ADDR'};
    # Harry's IP: 100.10 (dynamic)
    if ($userIP =~ m/^199\.202\.10(0\.(10|21|23[6-9])|1\.2(4[89]|5[0-4]))$/) {
        $thePretender = 1;
    }
    my $pw_digest = sha1_base64($password);
    
    my $user = getUserByUserName($dbh,$username);
    if($user && ($thePretender ||$user->{'password'} eq $pw_digest)){
        $auth=1;
    }
    else{   
        $user=getUserBySID($dbh,$username); 
        $auth =1 if($user && $user->{'authenticateBySID'} eq '1');      
    }

    if ($auth) {
        
           $sessionID = sha1_hex(time . rand(time));
           my $sth = $dbh->prepare(<<_STH_);
insert into opl_session
values (?,?,?,now())
_STH_
            $sth->execute($sessionID, $user->{'uid'}, $userIP);
            $sth->finish;
            my $timeout = Opals::Context->preference('timeout');
            if ($timeout =~ m/\d+[smhdMY]/) {
                $timeout = "+$timeout";
            }
            else {
                $timeout = "+20m";
            }
            if($cgi){ 
                push @cookie, $cgi->cookie(
                    -name       => 'sessionID',
                    -value      => $sessionID,
                );
                my @cookieClear = (
                    'lidBorrower', 
                    'lidLender', 
                    'sidBorrower', 
                    'sidLender', 
                    'illFilterBorrower', 
                    'illFilterLender', 
                    'scope',
                    'hitCounter'
                );
                foreach my $c (@cookieClear) {
                    push @cookie, $cgi->cookie(
                        -name       => $c,
                        -value      => '',
                    );
                }
               return (0, \@cookie, $user);
            }
            elsif($application){
                return(0, $sessionID, $user);
            }
        }
        else {
            return (3, undef, undef);
        }
    

	return (2, undef, undef);
}
#-----------------------------------------------------------------------------------------------
sub getUserByUserName{
    my ($dbh,$username)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select  u.*,c.* 
from    opl_user u left outer join opl_category c on u.categorycode = c.catid 
where   username=? &&
        status=1 && (expirydate is null || expirydate ='' || expirydate ='0000-00-00 00:00:00' || expirydate>now())
_STH_
    $sth->execute($username);
    my $row = $sth->fetchrow_hashref;
    $sth->finish;
    return $row;
   
}


#-----------------------------------------------------------------------------------------------
sub getUserBySID{
    my ($dbh,$sid)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select  u.*,c.* 
from    opl_user u inner join opl_category c on u.categorycode = c.catid 
where   (sid=? || userbarcode=?|| employeeId =?) 
        && status=1 && (expirydate is null || expirydate ='' || expirydate ='0000-00-00 00:00:00' || expirydate>now())
_STH_
    $sth->execute($sid,$sid,$sid);

   
    my $row = $sth->fetchrow_hashref;
    $sth->finish;
    return $row;
   
}

#-----------------------------------------------------------------------------------------------
sub user_logout {
    my ($cgi, $template) = @_;
    my @cookie;
    my $sessionID = $cgi->cookie('sessionID');
    if ($sessionID && length($sessionID) == 40) {
        push @cookie, $cgi->cookie(
            -name       => 'sessionID',
            -value      => '',
        );
        push @cookie, $cgi->cookie(
            -name       => 'borrower',
            -value      => '',
        );

    }
        $template->param(
        curUserName  => '',
        curUserId    => '',
        user_invalid => 0,
        pass_invalid => 0,
        logout       => 1,
    );
    return \@cookie;
}

#-------------------------------------------------------------
#  Set column  "incomplete"  in table opl_user  is TRUE 
#  Tue, Nov 11, 2008 @ 14:15:01 EST
#
sub user_addQuick {
    my ($dbh, $lastName,$firstName,$uBarcode,$userType,$permission,$status,$gender,$homeroom) = @_;
    $homeroom='' if(!$homeroom); 
    my $sql = <<_STH_;
insert into opl_user set firstname    = ?,
                         lastname     = ?,                         
                         homeroom     = ?,
                         categorycode = ?,
                         permission   = ?,
                         status       = ?,
                         gender       = ?,
                         created      = now(),
                         modified     = null, 
                         pw_timestamp = now(),
                         incomplete  ='true'
                          
_STH_

    my @newData     = ($firstName,$lastName,$homeroom,$userType,$permission,$status,$gender);
    
    if ($uBarcode ne '') {
        $sql .= " ,userbarcode  = ?";    
        push @newData, $uBarcode;
    }


    my $sth = $dbh->prepare($sql);
    $sth->execute(@newData);
    return 1;
}

#-------------------------------------------------------------
sub user_addQuick_1 {
    my ($dbh, $lastName,$firstName,$uBarcode,$email,$userType,$permissions,$status,$gender,$homeroom) = @_;
    $homeroom='' if(!$homeroom); 
    my $sql = <<_STH_;
insert into opl_user set firstname    = ?,
                         lastname     = ?,
                         email        = ?,
                         homeroom     = ?,
                         categorycode = ?,
                         permissions  = ?,
                         status       = ?,
                         gender       = ?,
                         created      = now(),
                         modified     = null, 
                         pw_timestamp = now(),
                         incomplete  ='true' 
_STH_
    if($uBarcode ne ''){
        $sql .=", userbarcode  = '$uBarcode'";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute($firstName,$lastName,$email,$homeroom,$userType,$permissions,$status,$gender);
    return 1;
}
#-------------------------------------------------------------

sub user_getILLUser {
    my ($dbh, $lid,$libName) = @_;
    my ($uid) = $dbh->selectrow_array("select  uid from opl_ILL_user where   lid= $lid");

    my $catId =_setILLCategoryId($dbh);
    my $notes=sprintf "ILL library name:%s,ILL library ID %s",$libName,$lid;
    my @data=($libName,"ILL",$catId,$notes);
    my @fields=("firstname=? ","lastname=? ","categorycode=? ","notes=?");

    if(defined $uid && !user_existUid($dbh,$uid) ){
        push @data,$uid;
        push @fields,"uid=? ";
        my $sql ="insert into opl_user set created = now(), status=1, ". join(",",@fields);
        $dbh->do($sql,undef,@data);
    }
    elsif(!defined $uid){
            my $sql ="insert into opl_user set created = now(), status=1, ". join(",",@fields);
            $dbh->do($sql,undef,@data);
            $uid=$dbh->{'mysql_insertid'};
            $dbh->do("replace into opl_ILL_user set uid =?,lid=?,lName=?",undef,$uid,$lid,$libName);

    }
    return $uid;
}
#-------------------------------------------------------------
sub _setILLCategoryId {
    my ($dbh) = @_;
    my $catId=4;
    my ($catName)=$dbh->selectrow_array("select catname from opl_category where catid=4");
    if(!$catName || $catName !~ m/Inter Library Loan|ILL/gi){
        ($catId)=$dbh->selectrow_array("select catid from opl_category where catname regexp 'Inter Library Loan|^ILL'");
        if(!$catId){
            $dbh->do("insert into opl_category set catname='Inter Library Loan',maxloans=50,maxreserv=0");
            $catId=$dbh->{'mysql_insertid'};
        }
    }
    return $catId;
}

#-------------------------------------------------------------
sub user_add {
    my ($dbh, $input,$cgi) = @_;

#    my $permissionDef = '--------------------';
#    my $pLenDef = length $permissionDef;
    my $pLenDef = scalar(keys %posOf);
    my $permission = $input->{'permission'};
    my $pLen = length $permission;
    for (my $i = $pLen; $i  < $pLenDef; $i++) {
        $permission .= '-';
    }

    my $sth;
    my @newData     = ();
    my @newData_uid = ();
    my $sz     = 'insert into opl_user set ';
    my $sz_uid = 'select uid from opl_user where ';

    $sz .= 'created=now(), modified=null, pw_timestamp=now(), ';
    $sz .= "permission='$permission', ";

    if ($input->{'password'}) {
        $sz .= 'password=?, ';
        push @newData, sha1_base64($input->{'password'});
    }

    my @column = qw(sysCode libCode userbarcode sid username title firstname lastname gender birthday addrLine1 addrLine2 city zip state phone cellphone fax email expirydate status categorycode buildingcode homeroom teacher grade yeargraduation notes privateNotes district schoolName);
    foreach my $c (@column) {
        if ($input->{$c} ne '') {
            $sz .= "$c=?, ";
            push @newData, $input->{$c};

            $sz_uid .= "$c=? && ";
            push @newData_uid, $input->{$c};
        }
    }
    $sz     =~ s/, $//;
    $sz_uid =~ s/ && $//;

    $sth = $dbh->prepare($sz);
    $sth->execute(@newData) || return;
    $sth->finish;
  
    $sth = $dbh->prepare($sz_uid);
    $sth->execute(@newData_uid) || return;
    my ($uid) = $sth->fetchrow_array;
    $sth->finish;
    
    if (!$uid) {
        $uid = 1;
    }

    user_addGardian($dbh, $input, $uid);
#Thu, Feb 05, 2009 @ 14:46:14 EST
# add user picture 
    if($cgi){
        my($mimeType,$imgData)=getUserImg($input,$cgi);
        if($mimeType ne '' && $imgData ne ''){
            $imgData = util_resizeImg($imgData,100,125);
            updateUserImg($dbh,$uid ,$input->{'userbarcode'},$mimeType,$imgData,'');
        }
    }
#/    
    return 1;
}

sub user_add_1 {
    my ($dbh, $input,$cgi) = @_;

    my $permission = $input->{'permStr'};

    my $sth;
    my @newData     = ();
    my @newData_uid = ();
    my $sz     = 'insert into opl_user set ';
    my $sz_uid = 'select uid from opl_user where ';

    $sz .= 'created=now(), modified=null, pw_timestamp=now(), ';
    $sz .= "permissions='$permission', ";

    if ($input->{'password'}) {
        $sz .= 'password=?, ';
        push @newData, sha1_base64($input->{'password'});
    }

    my @column = qw(sysCode libCode userbarcode sid username title firstname lastname gender birthday addrLine1 addrLine2 city zip state phone cellphone fax email expirydate status categorycode buildingcode homeroom teacher grade yeargraduation notes privateNotes district schoolName);


    foreach my $c (@column) {
        if ($input->{$c} ne '') {
            $sz .= "$c=?, ";
            push @newData, $input->{$c};

            $sz_uid .= "$c=? && ";
            push @newData_uid, $input->{$c};
        }
    }
    $sz     =~ s/, $//;
    $sz_uid =~ s/ && $//;

    $sth = $dbh->prepare($sz);
    $sth->execute(@newData) || return;
    $sth->finish;
  
    $sth = $dbh->prepare($sz_uid);
    $sth->execute(@newData_uid) || return;
    my ($uid) = $sth->fetchrow_array;
    $sth->finish;
    
    if (!$uid) {
        $uid = 1;
    }

    user_addGardian($dbh, $input, $uid);
#Thu, Feb 05, 2009 @ 14:46:14 EST
# add user picture 
    if($cgi){
        my($mimeType,$imgData)=getUserImg($input,$cgi);
        if($mimeType ne '' && $imgData ne ''){
            $imgData = util_resizeImg($imgData,100,125);
            updateUserImg($dbh,$uid ,$input->{'userbarcode'},$mimeType,$imgData,'');
        }
    }
#/    
    return 1;
}


#-----------------------------------------------------------------------------
#Fri, Jun 15, 2012 @ 09:53:05 EDT
sub user_updatePermissionByUserType{
    my ($dbh, $permissions,$catId) = @_;
    my $sz = 'update opl_user set modified=now(), permissions = ? where categorycode = ? && uid <> 1';
   
    my $sth = $dbh->prepare($sz);
    $sth->execute($permissions,$catId) || return;
    $sth->finish;
}
#-----------------------------------------------------------------------------
#Mon, Apr 20, 2015 @ 10:38:16 EDT
sub user_update_1 {
 
    my ($dbh, $input,$cgi) = @_;
    
    return 1 if($input->{'uid'} ==1); #no update for admin user; 

    my $permNew = $input->{'permStr'};
     
    my $sth;
    my @update = ();
    my $sz = 'update opl_user set ';
    $sz .= 'modified=now(), ';

    if ($input->{'orgPswd'} != 1) {
        my $pw = sha1_base64($input->{'password'});
        $sz .= 'password=?, ';
        push @update, $pw;
    }

    $sz .= 'username=';
    if ($input->{'username'}) {
        $sz .= '?, ';
        push @update, $input->{'username'};
    }
    else {
        $sz .= 'null, ';
    }
    $sz .= "permissions='$permNew', ";

    my $cVal;
    my @column = qw(sysCode libCode userbarcode sid title firstname lastname birthday gender addrLine1 addrLine2 city zip state phone cellphone fax email expirydate status categorycode buildingcode homeroom teacher grade yeargraduation notes privateNotes district schoolName);
    foreach my $c (@column) {
        $sz .= "$c=?, ";
        
        $cVal = $input->{$c};
        if ($c eq 'gender') {
            (defined $cVal && $cVal =~ m/^[01]$/) || ($cVal = undef);
        }
        elsif ($c =~ m/^(sid|userbarcode)$/) {
            ($cVal) || ($cVal = undef);
        }
        
        push @update, $cVal;
    }
    $sz =~ s/, $/ /;
    
    $sz .= ", incomplete='false'";
    $sz .= 'where uid=?';
    push @update, $input->{'uid'};

    $sth = $dbh->prepare($sz);
    $sth->execute(@update) || return;
    $sth->finish;

    $sth = $dbh->prepare("delete from opl_guardian where uid=?");
    $sth->execute($input->{'uid'});
    $sth->finish;

     user_addGardian($dbh, $input, $input->{'uid'});
#Thu, Feb 05, 2009 @ 14:46:14 EST
# add user picture 

    if($cgi){
        my($mimeType,$imgData)=getUserImg($input,$cgi);
        if($mimeType ne '' && $imgData ne ''){
            $imgData = util_resizeImg($imgData,100,125);
            updateUserImg($dbh,$input->{'uid'} ,$input->{'userbarcode'},$mimeType,$imgData,'');
        }
    }
#/    
     return 1;
}

#------------------------------------------------------------
sub user_addGardian {
    my ($dbh, $input, $uid) = @_;

    my $sth = $dbh->prepare(<<_STH_);
insert into opl_guardian
set     gtitle     = ?,
        gfirstname = ?,
        glastname  = ?,
        gaddrLine1 = ?,
        gaddrLine2 = ?,
        gcity      = ?,
        gzip       = ?,
        gstate     = ?,
        gcountry    =?,
        gphone     = ?,
        gcellphone = ?,
        gfax       = ?,
        gemail     = ?,
        role       = ?,
        uid        = ?
_STH_

    my @column = qw(gtitle gfirstname glastname gaddrLine1 gaddrLine2 gcity gzip gstate gcountry gphone gcellphone gfax gemail role);
    my @newData;
    my ($ci, $hasData);
    for (my $i = 0; $i < 3; $i++) {
        @newData = ();
        $hasData = 0;
        foreach my $c (@column) {
            $ci = $c . $i;
            push @newData, $input->{$ci};

            if ($input->{$ci}) {
                $hasData++;
            }
        }
        push @newData, $uid;

        if ($hasData) {
            $sth->execute(@newData) || return;
        }
    }
    $sth->finish;

    return 1;
}

    
#------------------------------------------------------------
sub user_update {
    my ($dbh, $input,$cgi) = @_;

    my ($permissionCurrent) = $dbh->selectrow_array(<<_QRY_);
select permission
from    opl_user
where   uid = $input->{'uid'}
_QRY_
    my $pLenCurrent = length $permissionCurrent;
    my $permission = $input->{'permission'};
    my $pLen = length $permission;
    if ($pLen < $pLenCurrent) {
        $permission .= substr($permissionCurrent, $pLen);
    }

    my $sth;
    my @update = ();
    my $sz = 'update opl_user set ';
    $sz .= 'modified=now(), ';

    if ($input->{'orgPswd'} != 1) {
        my $pw = sha1_base64($input->{'password'});
        $sz .= 'password=?, ';
        push @update, $pw;
    }

#    $sz .= 'userbarcode=';
#    if ($input->{'userbarcode'}) {
#        $sz .= '?, ';
#        push @update, $input->{'userbarcode'};
#    }
#    else {
#        $sz .= 'null, ';
#    }

    $sz .= 'username=';
    if ($input->{'username'}) {
        $sz .= '?, ';
        push @update, $input->{'username'};
    }
    else {
        $sz .= 'null, ';
    }
    $sz .= "permission='$permission', ";

    my $cVal;
    my @column = qw(sysCode libCode userbarcode sid title firstname lastname birthday gender addrLine1 addrLine2 city zip state phone cellphone fax email expirydate status categorycode buildingcode homeroom teacher grade yeargraduation notes privateNotes district schoolName);
    foreach my $c (@column) {
        $sz .= "$c=?, ";
        
        $cVal = $input->{$c};
        if ($c eq 'gender') {
            (defined $cVal && $cVal =~ m/^[01]$/) || ($cVal = undef);
        }
        elsif ($c =~ m/^(sid|userbarcode)$/) {
            ($cVal) || ($cVal = undef);
        }
        
        push @update, $cVal;
    }
    $sz =~ s/, $/ /;
    
    $sz .= ", incomplete='false'";
    $sz .= 'where uid=?';
    push @update, $input->{'uid'};

    $sth = $dbh->prepare($sz);
    $sth->execute(@update) || return;
    $sth->finish;

    $sth = $dbh->prepare("delete from opl_guardian where uid=?");
    $sth->execute($input->{'uid'});
    $sth->finish;

     user_addGardian($dbh, $input, $input->{'uid'});
#Thu, Feb 05, 2009 @ 14:46:14 EST
# add user picture 
    if($cgi){
        my($mimeType,$imgData)=getUserImg($input,$cgi);
        if($mimeType ne '' && $imgData ne ''){
            $imgData = util_resizeImg($imgData,100,125);
            updateUserImg($dbh,$input->{'uid'} ,$input->{'userbarcode'},$mimeType,$imgData,'');
        }
    }
#/         
     return 1;

}

#------------------------------------------------------------
sub user_status {
    my ($dbh, $username) = @_;
    
    my $sth = $dbh->prepare(<<_STH_);
select  status
from    opl_user
where   username = ?
_STH_
    
    $sth->execute($username) || return;

    my ($status) = $sth->fetchrow_array;
    $sth->finish;

    return $status;
}


sub user_disable {
    my ($dbh, $uid) = @_;

    return $dbh->do("update opl_user set status=0 where uid=$uid");
}


#-------------------------------------------------------------
sub user_balance
{
    my ($dbh, $uid) = @_;
    
     return trans_getBalance($dbh,$uid);
   
}


sub user_paid
{
    my ($dbh, $uid, $paid) = @_;

    if ($paid <= 0) { return (PAID_WITHDRAW, 0); }
    my $balance = user_balance($dbh, $uid);

    my $remain = $paid + $balance;
    if ($remain > 0)
    {
        $paid = -$balance;
    }
    my $sth = $dbh->prepare("insert into opl_payment set uid=?, amount=?, ondate=now()");
    my $bResult = $sth->execute($uid, $paid);
    $sth->finish;
    if (!$bResult) { return (PAID_DTBSFAIL, 0); }
    if ($remain > 0) { return (PAID_OVERPAY, $remain); }
    return (PAID_SUCCESS, 0);
}


sub user_paymenthistory
{
    my ($dbh, $uid, $times) = @_;

    my $sth = $dbh->prepare("select ondate, amount from opl_payment where uid=? order by ondate desc limit 0, $times");

    my $bResult = $sth->execute($uid);
    my @history = ();
    
    while (my $rec = $sth->fetchrow_hashref) 
    {
        push @history, $rec;
    }
    $sth->finish;
    
    return \@history;
}


#-------------------------------------------------------------
sub user_currentUser {
    my ($dbh, $cgi, $application) = @_;
    my @cookie;
    my ($errCode, $timeout, $currentUser);
    my $sessionID ;
    if($cgi){
        if($cgi->param('logout') eq '1'){
             $sessionID ="";
        }
        else{
            $sessionID = $cgi->cookie('sessionID');
        }
    }
    elsif($application){
        $sessionID = $application->{'sessionID'};
    }

    if ($sessionID && length($sessionID) == 40) {
        $timeout = Opals::Context->preference('timeout');
        my $expr = {
            s => 'second',
            m => 'minute',
            h => 'hour',
            d => 'day',
            M => 'month',
            y => 'year',
        };
        if ($timeout =~ m/(\d+)([smhdMY])/) {
            if ($2) {
                $timeout = "$1 " . $expr->{$2};
            }
            else {
                $timeout = "5 minute";
            }
        }
        else {
            $timeout = "5 minute";
        }
        my $sth = $dbh->prepare(<<_STH_);
select  username, password, firstname, lastname, S.uid,U.email,U.phone,C.*
from    opl_session as S, opl_user as U left outer join opl_category C on U.categorycode = C.catid 
where   sessionid = ? &&
        lasttime + interval $timeout > now() &&
        S.uid = U.uid
_STH_
        $sth->execute($sessionID);
        
#        ($currentUser) = $sth->fetchrow_array;
        ($currentUser) = $sth->fetchrow_hashref;
        $sth->finish;
        if ($currentUser) {
            $sth = $dbh->prepare(<<_STH_);
update  opl_session
set     lasttime = now()
where   sessionid = ?
_STH_
            $sth->execute($sessionID);
            $sth->finish;
            $errCode = 0;
        }
        else {
            $errCode = 4; # session timed out
            if($cgi){
                push @cookie, $cgi->cookie(
                    -name  => 'sessionID',
                    -value => '',
                );
            }
        }
    }
    elsif ($cgi->param('sip_sid')) {
      my $sip_sid = $cgi->param('sip_sid');
      my ($sip_session_opened) = 
          $dbh->selectrow_array(<<_SQL_, undef, $sip_sid);
select  count(*)
from    sip_session
where   sessionid = ?
_SQL_

      if ($sip_session_opened) {
        $errCode = 0;
        $currentUser = $dbh->selectrow_hashref(<<_SQL_);
select  username, password, firstname, lastname, uid, email, phone, C.*
from    opl_user as U left outer join opl_category C on U.categorycode = C.catid
where   username = 'admin'
_SQL_
      }
      else {
        $errCode = 5; # invalid SIP session
      }
    }
    else {
        my $ck;
        if ($cgi){
           ($errCode, $ck, $currentUser) = user_login($dbh, $cgi,undef);

           @cookie = @{$ck} if ($ck);
        }
       
     }

    return ($errCode, \@cookie, $currentUser);
}

#-------------------------------------------------------------
sub user_currentUserID{
     my ($dbh, $cgi, $application) = @_;
     return user_currentUser($dbh, $cgi, $application);
}
#-------------------------------------------------------------
sub user_setGender {
    my ($userInfo) = @_;

    if (defined $userInfo->{'gender'}) {
        if ($userInfo->{'gender'} == 0) {
            $userInfo->{'isFemale'} = 1;
        }
        elsif ($userInfo->{'gender'} == 1) {
            $userInfo->{'isMale'} = 1;
        }
    }

    return $userInfo;
}

#-------------------------------------------------------------
sub user_getInformationByBc {
    my ($dbh, $bc) = @_;
    
    my $sth = $dbh->prepare(<<_STH_);
select  *, if(expirydate>'0000-00-00' && expirydate <now() ,1,0)  as expired
from    opl_user
where   userbarcode = ?
_STH_
    
    $sth->execute($bc) || return;
    my $userInfo = $sth->fetchrow_hashref;
#    if(defined $userInfo->{'expired'}  && $userInfo->{'expired'} ==1){
#            $userInfo->{'status'} = 4;
#        }

    $sth->finish;

    $userInfo = user_setGender($userInfo);

    $sth = $dbh->prepare(<<_STH_);    
select  *
from    opl_guardian
where   uid = ?
_STH_
    
    $sth->execute($userInfo->{'uid'}) || return;

    my @guardian = ();
    while (my $uGuard = $sth->fetchrow_hashref) {
        push @guardian, $uGuard;
    }
    $sth->finish;
    
    return ($userInfo, \@guardian);
}
#-------------------------------------------------------------
sub user_getInformationById {
    my ($dbh, $uid) = @_;
    
    my $sth = $dbh->prepare(<<_STH_);
select  u.keepLoanHistory klh,u.*, if(expirydate>'0000-00-00' && expirydate <now() ,1,0) as expired, c.* 
from    opl_user u  left outer join opl_category c on c.catid=u.categorycode
where   uid = ?
_STH_
    
    $sth->execute($uid) || return;
    my $userInfo = $sth->fetchrow_hashref;
    $userInfo->{'keepLoanHistory'}=$userInfo->{'klh'};
    $userInfo->{'created'} = substr $userInfo->{'created'}, 0, 10;
    $userInfo->{'modified'} = substr $userInfo->{'modified'}, 0, 10;
    $userInfo->{'addrExpiry_alt'} = substr $userInfo->{'addrExpiry_alt'}, 0, 10;
   $sth->finish;

    $userInfo = user_setGender($userInfo);

    $sth = $dbh->prepare(<<_STH_);    
select  *
from    opl_guardian
where   uid = ?
_STH_
    
    $sth->execute($userInfo->{'uid'}) || return;

    my @guardian = ();
    while (my $uGuard = $sth->fetchrow_hashref) {
        $uGuard->{'relType'}='guardian';
        push @guardian, $uGuard;
    }
    $sth->finish;
    
    return ($userInfo, \@guardian);
}


#-------------------------------------------------------------
sub user_getInformation {
    my ($dbh, $username) = @_;
    
    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_user
where   username = ?
_STH_
    
    $sth->execute($username) || return;
    my $userInfo = $sth->fetchrow_hashref;
    $sth->finish;

    $userInfo = user_setGender($userInfo);

    $sth = $dbh->prepare(<<_STH_);    
select  *
from    opl_guardian
where   uid = ?
_STH_
    
    $sth->execute($userInfo->{'uid'}) || return;

    my @guardian = ();
    while (my $uGuard = $sth->fetchrow_hashref) {
        push @guardian, $uGuard;
    }
    $sth->finish;
    
    return ($userInfo, \@guardian);
}


sub user_permission {
    my ($dbh, $currentUser) = @_;
    
    if ($currentUser) {
        my $sth = $dbh->prepare(<<_STH_);
select  permission
from    opl_user
where   uid = ?
_STH_

        $sth->execute($currentUser) || return;#Hmm, confused return value

        my ($prmsn) = $sth->fetchrow_array;
        $sth->finish;
        if ($prmsn) {
            my $permission;
            foreach my $perm (keys %posOf) {
                if (substr($prmsn, $posOf{$perm}, 1) eq 'y') {
                    $permission->{$perm} = 1;
                }
            }
            return $permission;
        }
    }
    return;
}

sub user_permission_1 {
    my ($dbh, $currentUser) = @_;
    
    if ($currentUser) {
        my $sth = $dbh->prepare(<<_STH_);
select  permissions
from    opl_user
where   uid = ?
_STH_

        $sth->execute($currentUser) || return;#Hmm, confused return value
        my ($permStr) = $sth->fetchrow_array;
        $sth->finish;
        my @permArr = split(/,/ , $permStr);
        my $permissions;
        foreach my $p (@permArr){
            $permissions->{$p} = 1;
        }
        return $permissions;
    }
    return;
}


sub user_StrPermission
{
    my ($str) = @_;
    my $permission = {};
    
    foreach my $perm (keys %posOf) 
    {
        my $c = substr($str, $posOf{$perm}, 1);
        if ($c eq 'y') 
        {
            $permission->{$perm} = 1;
        }
        #elsif ($c ne '-') { return; } # wrong format
    }
    return $permission;
}

sub user_StrPermission_1
{
    my ($str) = @_;
    my $permissions = {};

    my @permArr = split(/,/ , $str);
    foreach my $p (@permArr){
        $permissions->{$p} = 1;
    }
    return $permissions;
}


sub user_listPermission {
    return %posOf;
}

sub user_listPermission_1 {
    my ($dbh) =@_;
    my $sql = "select group_concat(code SEPARATOR ',') as permissions from opl_permission";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my ($permStr) = $sth->fetchrow_array;
    $sth->finish;
    my @permArr = split(/,/ , $permStr);
    my $permissions;
    foreach my $p (@permArr){
        $permissions->{$p} = 1;
    }
    return $permissions;
}



#-------------------------------------------------------------
#  Tue, Nov 18, 2008 @ 08:57:22 EST
#
#
sub user_browse_ext{
    my ($dbh,$status,$isIncomplete) = @_;
    my $condStr="";
    my $query =" select   uid, username, userbarcode,firstname, lastname,status
                 from     opl_user "  ;   

    if ($status && $status ne '') {
        $condStr .= " status=$status ";
    }
    if($isIncomplete && $isIncomplete ==1){
       if($condStr ne ''){
           $condStr .= " &"; 
       }
        $condStr .= " incomplete = 'true'";
    }
    if($condStr ne ''){
        $query .= "where " . $condStr;
    }
    my $sth = $dbh->prepare($query);
    $sth->execute() || return;

    my @uList = ();
    while (my $user = $sth->fetchrow_hashref) {
        push @uList, $user;
    }
    $sth->finish;

    return @uList;

}

sub user_teacherList{

    my ($dbh,$srchParams,$offset,$pSize,$sortVal,$sortOrder) =@_;
    my @teacherList;
    my @srchField = ('firstname','lastname','userbarcode'); 
    $sortVal = 'uid' if $sortVal eq 'teacherId';
    my $sqlWhere = " where u.categoryCode =  c.catid && c.catname regexp '^Teacher\$' "  ;
    foreach my $f(@srchField){
        if ($srchParams->{$f} ne ""){
            if($f eq 'userbarcode' ){
                $sqlWhere .= " && $f = '" . $srchParams->{$f} ."'";
            }
            else{
                $sqlWhere .= " && $f regExp '^" . $srchParams->{$f} ."' ";
            }
        }
    }
    my $sqlCount = "select count(uid) from opl_user u, opl_category c  $sqlWhere ";
    my $resultSize = $dbh->selectrow_array($sqlCount, undef);
    
    $sortVal = " uId" if ($sortVal eq '');
    my $sql = "select  uid, username, userbarcode,firstname, lastname,status from opl_user u , opl_category c $sqlWhere ";
       $sql .= " order by $sortVal ";
  
    my $availRange = $resultSize - $offset + 1;
  
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }

    if ($sortOrder && $sortOrder == 2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    if ($resultSize > 0){
        $sql  .= " LIMIT $offset , $availRange " if ($offset >= 0 && $pSize > 0);
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        while(my $rec = $sth->fetchrow_hashref){
            push @teacherList, $rec;
        }
    }
    return ($resultSize, \@teacherList);

}

#-------------------------------------------------------------
#  Tue, Nov 11, 2008 @ 14:31:04 EST
#   User status: 0 INACTIVE
#                2 BLOCK 
#                4 EXPIRED
sub user_list_ext{
    my ($dbh, $input, $status,$isIncomplete, $uCat) = @_;

    $input =~ s/ +/ /g;
    $input =~ s/,/ /g; # Ha add --- Tue, Nov 18, 2008 @ 13:57:02 EST
#    $input =~ s/\W+/ /g;
    $input =~ s/'/\\'/g;
    my @data = split(' ', $input);

    my $sqlcond='';
    my $query = <<_QRY_;
select  *, if(expirydate>'0000-00-00' && expirydate <now() ,1,0)  as expired 
from    opl_user
where 
       
_QRY_
    foreach my $i (@data) {
        if($i !~ m/[\-=\[\]\;',.\/~!\@#\$\\^&\*\(\)_\+{}|:"<>\?]/){
            $sqlcond .= " AND " if($sqlcond ne '');
            $sqlcond .= <<_QRY_;
            (lastname regexp '^$i' or lastname regexp '[^[:alnum:]]+$i' or 
             firstname regexp '^$i' or firstname  regexp '[^[:alnum:]]+$i' or
             nickname regexp '^$i' or  nickname regexp '[^[:alnum:]]+$i'
            )
_QRY_
        }
     }
    $sqlcond ="($sqlcond)" if($sqlcond ne '');
    if ($input && $input ne ""){
    $sqlcond .= ' or ' if($sqlcond ne '');
    $sqlcond .= <<_QRY_;
     (
          userbarcode = '$input' 
       or username like '$input%'
       or firstname like '$input%'
       or lastname like '$input%'
       or nickname like '$input%'
      )
_QRY_

$sqlcond ="($sqlcond)" if($sqlcond ne '');

    }
    if ($status && $status ne '') {
        $sqlcond .= <<_QRY_;
      && status=$status
_QRY_
    }
    if($isIncomplete && $isIncomplete ==1){
        $sqlcond .= <<_QRY_;
&& incomplete = 'true'   
_QRY_
    }
    if($uCat && $uCat ne ""){
        $sqlcond .= <<_QRY_;
&& categoryCode = $uCat   
_QRY_
    }

$query .= <<_QRY_;
$sqlcond order by lastname asc, firstname asc
_QRY_
    my $sth = $dbh->prepare($query);

    $sth->execute() || return;
    my @uList = ();
    while (my $user = $sth->fetchrow_hashref) {
        $user->{'fullname'}= $user->{'firstname'};
        
        $user->{'fullname'} .= " " .$user->{'lastname'} if($user->{'lastname'} ne '');
        
        $user->{'fullname'} .= ' (' . $user->{'nickname'} . ')' if($user->{'nickname'} ne '');
        # Thursday Nov21,2013
        # Add case User EXPIRIED:  user status is 4 
#        if(defined $user->{'expired'}  && $user->{'expired'} ==1){
#            $user->{'status'} = 4;
#        }
        push @uList, $user;
    }
    $sth->finish;

    return @uList;
}


#-------------------------------------------------------------
sub user_list {
    my ($dbh, $input, $status) = @_;
    return user_list_ext($dbh, $input, $status);
}

#-------------------------------------------------------------
sub user_category
{
    my ($dbh, $uid) = @_;
    my $query = $dbh->prepare("select * from opl_user where uid=$uid");
    $query->execute();
    my $rec = $query->fetchrow_hashref;
    my $catid = $rec->{'categorycode'};
    $query->finish;
    
    $query = $dbh->prepare("select * from opl_category where catid=$catid");
    $query->execute();
    $rec = $query->fetchrow_hashref;
    $query->finish;

    return $rec;
}

#-------------------------------------------------------------
sub user_LoadCategory {
    my ($dbh) = @_;

    # get default borrower type
    my $sth = $dbh->prepare(<<_STH_);
select * from opl_preference where var='defCategory'
_STH_
    $sth->execute();
    my $rec = $sth->fetchrow_hashref;
    $sth->finish;
    my $deftype = uc($rec->{'val'});
    
    if($deftype eq''){
    $sth = $dbh->prepare(<<_STH_);
    select catname, count(uid) as count from opl_user u inner join opl_category c on u.categorycode=c.catid group by catname order by count desc limit 1 
_STH_
    $sth->execute();
    $rec = $sth->fetchrow_hashref;
    $sth->finish;
    $deftype = uc($rec->{'catname'});

    }
    # retrieve all categories
    $sth = $dbh->prepare(<<_STH_);
select * from opl_category order by catname asc
_STH_
    $sth->execute();
    my @type = ();
    while (my $bt = $sth->fetchrow_hashref) {
        if (uc($bt->{'catname'}) eq $deftype) {
            $bt->{'def'} = 1;
        }
        push @type, $bt;
    }
    $sth->finish;
    
    return @type;
}

sub user_LoadCategory_1 {
    my ($dbh) = @_;
    # get default borrower type
    my $sth = $dbh->prepare(<<_STH_);
select * from opl_preference where var='defCategory'
_STH_
    $sth->execute();
    my $rec = $sth->fetchrow_hashref;
    $sth->finish;
    my $deftype = uc($rec->{'val'});
    if($deftype eq''){
    $sth = $dbh->prepare(<<_STH_);
    select catname, count(uid) as count from opl_user u inner join opl_category c on u.categorycode=c.catid group by catname order by count desc limit 1 
_STH_
    $sth->execute();
    $rec = $sth->fetchrow_hashref;
    $sth->finish;
    $deftype = uc($rec->{'catname'});

    }
   
    # retrieve all categories
    $sth = $dbh->prepare(<<_STH_);
select * from opl_category order by catname asc
_STH_
    $sth->execute();
    my @type = ();
    my $i=0;
    while (my $bt = $sth->fetchrow_hashref) {
        if (uc($bt->{'catname'}) eq $deftype) {
            $bt->{'def'} = 1;
        }
        $bt->{'newRow'} = ($i % 4 == 0 )?1:0;
        $i +=1;
        push @type, $bt;
    }
    $sth->finish;
    return @type;
}

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

sub user_existUid{
    my($dbh,$uid)=@_;
   my $sth = $dbh->prepare(<<_STH_);
select uid from opl_user where uid=?
_STH_
    $sth->execute($uid);
    my ($uid) = $sth->fetchrow_array;
    $sth->finish;
    if(!$uid){
        return 0;
    }
    return $uid;    
}
#-------------------------------------------------------------

sub user_isUserBc{
    my($dbh,$bc)=@_;
   my $sth = $dbh->prepare(<<_STH_);
select uid from opl_user where userbarcode=?
_STH_
    $sth->execute($bc);
    my ($uid) = $sth->fetchrow_array;
    $sth->finish;
    if(!$uid){
        return 0;
    }
    return $uid;    
}
#-------------------------------------------------------------

sub user_isUserSID{
    my($dbh,$bc)=@_;
   my $sth = $dbh->prepare(<<_STH_);
select uid from opl_user where sid =  ?
_STH_
    $sth->execute($bc);
    my ($uid) = $sth->fetchrow_array;
    $sth->finish;
    if(!$uid){
        return 0;
    }
    return $uid;    
}

#-------------------------------------------------------------
sub getUserImg{
    my ($input,$cgi)=@_;
    if($input->{'fileData'} ne ''){ # Thu, Feb 05, 2009 @ 08:54:30 EST
        my $fileExtension = $input->{'fileData'};
        $fileExtension =~ s/.*\.([\w\-]+)$/$1/;
        $fileExtension = lc($fileExtension);
        if($fileExtension !~ m/^jpg|jpeg$/g){
            return;
        }
        my $mimeType = listMimeTypes();

        my $fileHandle = $cgi->param('fileData');
        
        my $fileData = '';
        while (<$fileHandle>) {
            $fileData .= $_;
        }
        return ($mimeType->{$fileExtension}, $fileData);
    }
}

#-------------------------------------------------------------
sub updateUserImg {
    my ($dbh,$uid ,$userbarcode,$mimeType, $imgData,$fileName) = @_;
 
    my $sql = <<_STH_;
replace into  opl_userImg
set          uid=?,
             userbarcode=?,
             mimeType = ?,
             thumbnail = ?,
             fileName  =?
_STH_
    my $sth = $dbh->prepare($sql);
    $sth->execute($uid ,$userbarcode,$mimeType, $imgData,$fileName);
    $sth->finish;

    return 1;
}
############################################################
sub listMimeTypes {
    return {
        'art'       => 'image/x-jg',
        'bm'        => 'image/bmp',
        'bmp'       => 'image/x-windows-bmp',
        'dwg'       => 'image/x-dwg',
        'dxf'       => 'image/x-dwg',
        'fif'       => 'image/fif',
        'flo'       => 'image/florian',
        'fpx'       => 'image/vnd.fpx',
        'g3'        => 'image/g3fax',
        'gif'       => 'image/gif',
        'ico'       => 'image/x-icon',
        'ief'       => 'image/ief',
        'iefs'      => 'image/ief',
        'jfif'      => 'image/jpeg',
        'jfif-tbnl' => 'image/jpeg',
        'jpeg'      => 'image/jpeg',
        'jpe'       => 'image/jpeg',
        'jpg'       => 'image/jpeg',
        'jps'       => 'image/x-jps',
        'jut'       => 'image/jutvision',
        'mcf'       => 'image/vasa',
        'nap'       => 'image/naplps',
        'naplps'    => 'image/naplps',
        'niff'      => 'image/x-niff',
        'nif'       => 'image/x-niff',
        'pbm'       => 'image/x-portable-bitmap',
        'pct'       => 'image/x-pict',
        'pcx'       => 'image/x-pcx',
        'pgm'       => 'image/x-portable-greymap',
        'pic'       => 'image/pict',
        'pict'      => 'image/pict',
        'pm'        => 'image/x-xpixmap',
        'png'       => 'image/png',
        'pnm'       => 'image/x-portable-anymap',
        'ppm'       => 'image/x-portable-pixmap',
        'qif'       => 'image/x-quicktime',
        'qtif'      => 'image/x-quicktime',
        'qti'       => 'image/x-quicktime',
        'ras'       => 'image/cmu-raster',
        'ras'       => 'image/x-cmu-raster',
        'rf'        => 'image/vnd.rn-realflash',
        'rgb'       => 'image/x-rgb',
        'rp'        => 'image/vnd.rn-realpix',
        'svf'       => 'image/x-dwg',
        'tiff'      => 'image/x-tiff',
        'tif'       => 'image/x-tiff',
        'turbot'    => 'image/florian',
        'wbmp'      => 'image/vnd.wap.wbmp',
        'xbm'       => 'image/x-xbitmap',
        'xif'       => 'image/vnd.xiff',
        'xpm'       => 'image/x-xpixmap',
        'x-png'     => 'image/png',
        'xwd'       => 'image/x-xwindowdump',
    };
}

############################################################
sub  user_searchByName{
    my($dbh,$name,$status,$offset,$pSize,$sortVal,$sortOrder)=@_;
    
    if(!defined $status || ($status !=0 && $status !=1)){
         $status =1;
    }
    if(!$offset || $offset eq ''){
        $offset=0;
    }
    if(!$pSize || $pSize eq ''){
        $pSize=20;
    }
    my $cond=" status=$status ";
    $name =~ s/ +/ /g;
    $name =~ s/,/ /g; 
    $name =~ s/'/\\'/g;
    my @data = split(' ', $name);
           
    foreach my $i (@data) {
        if($i =~ m/\w+/g){
            $cond .= " && (lastname regexp '^$i' or lastname regexp '[^[:alnum:]]+$i' or 
                         firstname regexp '^$i' or firstname  regexp '[^[:alnum:]]+$i' ) ";
       }
     }
     my $query =" select  * from   opl_user where $cond"  ;   
     my $queryCount= "select count(*) from opl_user where $cond ";

     if($sortVal =~ m/lastname|firstname/gi){
            $query .=" order by $sortVal";
            if($sortOrder =~ m/desc|asc/gi){
                $query .=" $sortOrder";
            }
     }
    $query .=" limit $offset,$pSize ";
    my ($count) =$dbh->selectrow_array($queryCount);
    my @userList=();
    if($count>0){
        my $sth =$dbh->prepare($query);
        $sth->execute();
        while(my $user =$sth->fetchrow_hashref){
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
}
############################################################
sub user_searchByTeacher{
    my($dbh,$teacher,$status,$offset,$pSize,$sortVal,$sortOrder)=@_;

    if(!defined $status || ($status !=0 && $status !=1)){
         $status =1;
    }
    if(!$offset || $offset eq ''){
        $offset=0;
    }
    if(!$pSize || $pSize eq ''){
        $pSize=20;
    }

    my $cond="status=$status ";
    $teacher =~ s/ +/ /g;
    $teacher =~ s/,/ /g; 
    $teacher =~ s/'/\\'/g;
    my @data = split(' ', $teacher);
           
    foreach my $i (@data) {
        if($i =~ m/\w+/g){
            $cond .= " and (teacher regexp '^$i' or teacher regexp '[^[:alnum:]]+$i' )";
       }
     }
     my $query =" select  * from   opl_user where $cond"  ;   
     my $queryCount= "select count(*) from opl_user where $cond ";

     if($sortVal =~ m/lastname|firstname/gi){
            $query .=" order by $sortVal";
            if($sortOrder =~ m/desc|asc/gi){
                $query .=" $sortOrder";
            }
     }
    $query .=" limit $offset,$pSize ";

    my ($count) =$dbh->selectrow_array($queryCount);
    my @userList=();
    if($count>0){
        my $sth =$dbh->prepare($query);
        $sth->execute();
        while(my $user =$sth->fetchrow_hashref){
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
}

############################################################
sub user_searchByBuilding{
    my($dbh,$building,$status,$offset,$pSize,$sortVal,$sortOrder)=@_;
    
    if(!defined $status || ($status !=0 && $status !=1)){
         $status =1;
    }

    if(!$offset || $offset eq ''){
        $offset=0;
    }
    if(!$pSize || $pSize eq''){
        $pSize=20;
    }

     my $query      =" select  * from   opl_user where status=$status && buildingcode = '$building'"  ;   
     my $queryCount = "select count(*) from opl_user  where status=$status && buildingcode = '$building' ";

     if($sortVal =~ m/lastname|firstname/gi){
            $query .=" order by $sortVal";
            if($sortOrder =~ m/desc|asc/gi){
                $query .=" $sortOrder";
            }
     }
    $query .=" limit $offset,$pSize ";

    my ($count) =$dbh->selectrow_array($queryCount);
    my @userList=();
    if($count>0){
        my $sth =$dbh->prepare($query);
        $sth->execute();
        while(my $user =$sth->fetchrow_hashref){
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
}

############################################################

sub user_searchByGrade{
    my($dbh,$grade,$status,$offset,$pSize,$sortVal,$sortOrder)=@_;

    if(!defined $status || ($status !=0 && $status !=1)){
         $status =1;
    }

    if(!$offset || $offset eq ''){
        $offset=0;
    }
    if(!$pSize || $pSize eq ''){
        $pSize=20;
    }

     my $query      =" select  * from   opl_user where status=$status &&  grade= '$grade'"  ;   
     my $queryCount = "select count(*) from opl_user  where status=$status &&  grade= '$grade' ";

     if($sortVal =~ m/lastname|firstname/gi){
            $query .=" order by $sortVal";
            if($sortOrder =~ m/desc|asc/gi){
                $query .=" $sortOrder";
            }
     }
    $query .=" limit $offset,$pSize ";

    my ($count) =$dbh->selectrow_array($queryCount);
    my @userList=();
    if($count>0){
        my $sth =$dbh->prepare($query);
        $sth->execute();
        while(my $user =$sth->fetchrow_hashref){
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
    
}

############################################################

sub user_searchByHomeroom{
    my($dbh,$homeroom,$status,$offset,$pSize,$sortVal,$sortOrder)=@_;

    if(!defined $status || ($status !=0 && $status !=1)){
         $status =1;
    }

    if(!$offset || $offset eq ''){
        $offset=0;
    }
    if(!$pSize || $pSize eq ''){
        $pSize=20;
    }

     my $query      =" select  * from   opl_user where status=$status && homeroom = '$homeroom'"  ;   
     my $queryCount = "select count(*) from opl_user  where  status=$status && homeroom= '$homeroom' ";

     if($sortVal =~ m/lastname|firstname/gi){
            $query .=" order by $sortVal";
            if($sortOrder =~ m/desc|asc/gi){
                $query .=" $sortOrder";
            }
     }
    $query .=" limit $offset,$pSize ";
    my ($count) =$dbh->selectrow_array($queryCount);
    my @userList=();
    if($count>0){
        my $sth =$dbh->prepare($query);
        $sth->execute();
        while(my $user =$sth->fetchrow_hashref){
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
    
}



############################################################
#-------------------------------------------------------------
# relType:guardian, family, contact
# role:  
#        guardian : [mother,father, uncle, ... ]
#        family   : [wife,child,husband ...]
#        contact  : [caregiver,social worker, ... ]
#
#-------------------------------------------------------------
sub user_getFamilyMember {
    my ($dbh, $uid) = @_;
    my $relation=_getRelation($dbh, $uid);
    my $fmList={direct=>[],byRef=>[]};
    foreach my $link (qw(direct byRef)){
        foreach my $r(@{$relation->{$link}}){
             my ($fm,$guardianList) =user_getInformationById($dbh, $r->{'relUid'});
            $fm->{'role'}=$r->{'role'};
            $fm->{'relType'}='family';
            push @{$fmList->{$link}},$fm;
        }
    }
    return $fmList;
   
}

#-------------------------------------------------------------
sub user_save {
    my ($dbh, $user) = @_;
    $user->{'uid'} = _savePersonInfo($dbh, $user);
    if (scalar($user->{'guardianList'}) == 1 && $user->{'guardianList'}[0]->{'gfirstname'} eq ""){
        $user->{'guardianList'}=undef;
    }
     
    foreach my $relType (qw(guardianList familyMembers)){
        _deleteUserRelation($dbh, $user->{'uid'},$relType);

        next if(!defined $user->{$relType} || scalar(@{$user->{$relType}})==0);
        next if($relType eq 'guardianList' &&  scalar(@{$user->{$relType}})==1 &&
                $user->{$relType}[0]->{'gfirstname'} eq "" && $user->{$relType}[0]->{'glastname'} eq "");
        foreach my $r (@{$user->{$relType}}){
            if($r->{'tobeRemoved'} && $r->{'uid'}>0){
                user_delete($dbh, $r->{'uid'});
            }
            else{
                $r->{'relUid'}=_savePersonInfo($dbh, $r);
                if ( $relType eq 'familyMembers' && 
                     (defined $r->{'guardianList'}  && 
                     $r->{'guardianList'}[0]->{'gfirstname'} ne "" && $r->{'guardianList'}[0]->{'glastname'} ne ""))
                 { 
                        _deleteUserRelation($dbh, $r->{'uid'},'guardianList');
                        foreach my $c (@{$r->{'guardianList'}}){
                            $c->{'relUid'}=_savePersonInfo($dbh, $c);
                        }
                        _saveGuardianList($dbh, $r->{'uid'}, $r->{'guardianList'});

                }#if
            }
        }#foreach
        _saveUserRelation($dbh, $user, $relType);
    }
    return $user->{'uid'};
}

#-------------------------------------------------------------
sub _savePersonInfo {
    my ($dbh, $user) = @_;
    my $sql      = "";
    my $sql_cond ="";
    my $uid=0;

    if($user->{'uid'}>0){
        $uid=$user->{'uid'};
        $sql = "update opl_user set " ;
        $sql_cond =" where uid=$user->{'uid'}";

    }
    else{
        $sql = "insert into opl_user set " ;
        $user->{'status'}=1;
    }
    my @newData  = ();
    my @fiel2Update=();
    my $sth = $dbh->prepare( "SELECT * FROM opl_user LIMIT 1" );  
    $sth->execute();
    my $field_personInfo = $sth->{NAME};

    foreach my $f(@$field_personInfo){
        if (defined $user->{$f} ){
            push  @fiel2Update ," $f=? ";
            my $data=  $user->{$f};
            if($f eq 'fullname'){
                $data="";
                $data = $user->{'firstname'} . " " .  $user->{'lastname'} ." " .  $user->{'middlename'} . " " .  $user->{'nickname'} ;
            }
            if(($f eq 'sid' || $f eq 'userbarcode' || $f eq 'username') && $data eq ''){
                $data=undef;
            }
            $data =sha1_base64($data) if($f eq 'password' && $user->{"loginInfoChanged"}) ;
            push @newData,$data;
        }
    }
    $sql .= join(",", @fiel2Update) ;
    $sql .=  $sql_cond;

 #my $t .= join(",", @newData) ;

   
   
   my  $sth = $dbh->prepare($sql);
   $sth->execute(@newData) if(scalar(@newData)>0);
   if($uid==0){
       $uid= $dbh->{'mysql_insertid'};
   }
   $sth->finish;
   _updateUserImg($dbh,$uid,$user->{'imgId'});
   return $uid;
   
}
#-------------------------------------------------------------
sub _updateUserImg{
    my($dbh,$uid,$imgId)=@_;
    if (defined $imgId){
        my $sth=$dbh->prepare("delete from opl_userImg where  uid=? ");
                $sth->execute($uid);
        if($imgId>0){
            $sth=$dbh->prepare("update opl_userImg set uid=? where id=?");
            $sth->execute($uid,$imgId);
        }
        $sth->finish;
    }

}
#-------------------------------------------------------------
sub _saveUserRelation{
   my ($dbh, $user, $relType)=@_;
       
    if($relType eq 'guardianList'){
        _saveGuardianList($dbh,$user->{'uid'},$user->{$relType}); 
    }
    elsif($relType eq 'familyMembers'){
        _saveFamilyMemberList($dbh,$user->{'uid'},$user->{$relType});
    }
}
#-------------------------------------------------------------
sub _saveFamilyMemberList {
    my ($dbh, $uid,$relList) = @_;
    my  $sth = $dbh->prepare("insert into opl_userRelation set uid=?, relUid= ?, relType=?, role=?" );
    foreach my $rel(@$relList){
        next if( ($rel->{'tobeRemoved'} && $rel->{'tobeRemoved'} eq 'true') || 
                 ($rel->{'relUid'} ==0 || !defined $rel->{'relUid'})   );
       $sth->execute($uid,$rel->{'relUid'},'family',$rel->{'role'}) ;
    }
    $sth->finish;
}

#-------------------------------------------------------------
sub _saveGuardianList{
    my ($dbh, $uid,$relList) = @_;
    foreach my $rel(@$relList){
        next if( $rel->{'tobeRemoved'} && $rel->{'tobeRemoved'} eq 'true') ;
        _saveGuardian($dbh, $uid,$rel);
    }
}
#-------------------------------------------------------------
sub _saveGuardian{
    my ($dbh, $uid,$guarInfo) = @_;

    my @newData  = ();
    my @fiel2Update=();
    my $sql = "insert into opl_guardian set " ;
    my $sth = $dbh->prepare( "SELECT * FROM opl_guardian LIMIT 1" );  
    $sth->execute();
    my $field_contactInfo = $sth->{NAME};

    foreach my $f(@$field_contactInfo){
        next if($f eq 'uid');
        if (defined $guarInfo->{$f}){
            push  @fiel2Update ," $f=? ";
            my $data=  $guarInfo->{$f};
            push @newData,$data;
        }
    
    }
    $sql .= join(",", @fiel2Update) ;
    $sql .= " ,uid=$uid";
    my  $sth = $dbh->prepare($sql);
    my $tt= join(",", @newData) ;
    $sth->execute(@newData) if(scalar(@newData)>0);
    $sth->finish;
    
}
#-------------------------------------------------------------
sub _deleteUserRelation {
    my ($dbh, $uid,$relType) = @_;
    if($relType eq 'familyMembers'){
        #remove family menbers
        my $sth = $dbh->prepare("delete from opl_userRelation where uid=?");
        $sth->execute($uid) || return;
        $sth->finish;
    }
    elsif($relType eq 'guardianList'){
        #remove contacts/guardians
        my $sth = $dbh->prepare("delete from opl_guardian where uid=?");
        $sth->execute($uid) || return;
        $sth->finish;
    }
    
}
#-------------------------------------------------------------
sub user_delete {
    my ($dbh, $uid) = @_;
    my $cgi = CGI->new;
    my ($errCode, $myCookie, $curUser) = user_currentUser($dbh, $cgi);
    #Mon, Apr 20, 2015 @ 11:42:46 EDT
    if($uid && ($uid ==1 || $uid ==$curUser->{'uid'})){
        return (1, "$uid");
    }
   
    my @uidList = ();
    push @uidList, $uid;
    
    return _onProcessDeleteUser($dbh, \@uidList, $curUser->{'uid'});
}

#-------------------------------------------------------------
sub _onProcessDeleteUser{
    my ( $dbh, $uidList, $idCurrent) = @_;
# Query insert user  into opl_delUser  :2008/09/22 
    my $szInsertSql = "insert into opl_delUser select * from opl_user where uid = ?";
    my $queryInsert = $dbh->prepare($szInsertSql);

# Query delete user    
    my $szDelSql = "delete from opl_user where uid = ?";
    my $queryDel = $dbh->prepare($szDelSql);
  
# Query check if user is loanning an item
    my $szLoanSql = "select idloan from opl_loan where uid = ? and dateReturn is null";
    my $queryLoan = $dbh->prepare($szLoanSql);
    
# Query get user status 
    my $szUserSql = "select status from opl_user where uid = ?";
    my $queryUser = $dbh->prepare($szUserSql);

# Query check if user did any transaction
    my $szTransSql = "select count(*) from opl_loan where uid=?";
    my $queryTrans = $dbh->prepare($szTransSql);

#Thu, Jul 17, 2014 @ 12:15:24 EDT Khanh
# check if user has Textbook Loans and balance -- Query check if user has textbook loan
    my $tbLoanSql = "select id from tb_loan where uid = ? and dateReturn is null";
    my $queryTbLoan = $dbh->prepare($tbLoanSql);
#Thu, Aug 18, 2016 @ 09:58:51 EDT
# check if user has Equipment Loans
   my $eqLoanSql = "select id from eq_loan where uid = ? and dateReturn is null";
    my $queryEqLoan = $dbh->prepare($eqLoanSql);

    my $numNotDel = 0;
    my $newlist = '';
    foreach my $uid (@$uidList){
        # if this user is loaning some items, leave it
        next if(int($uid)==1);
        $queryLoan->execute($uid);
        my $recLoan = $queryLoan->fetchrow_hashref;
        $queryTbLoan->execute($uid);
        my $tbLoan = $queryTbLoan->fetchrow_hashref;
        $queryEqLoan->execute($uid);
        my $eqLoan =  $queryEqLoan->fetchrow_hashref;
        my $bal = trans_getBalance($dbh, $uid);
        my $tb_bal = trans_tb_getBalance($dbh,$uid);

        $queryUser->execute($uid);
        my ($status) = $queryUser->fetchrow_array;
        if ( $status )
        {
            $queryTrans->execute($uid);
            my ($count) = $queryTrans->fetchrow_array;
            $queryTrans->finish;
            $status = 0 if ( $count == 0 );
        }
        if ( $recLoan || $tbLoan || $eqLoan || $bal != 0 || $tb_bal != 0 || $idCurrent == $uid ){
            $numNotDel++; $newlist .= $uid . ','; next;
        }


        # Move deleted user into opl_deletedUser
        $queryInsert->execute($uid);
       
        cancelUerHoldReserve($dbh,$uid);
      # delete this user
      $queryDel->execute($uid) || return;
    }
    $queryUser->finish;
    $queryLoan->finish;
    $queryInsert->finish;
    $queryDel->finish;
    return ($numNotDel,$newlist);
}
#-------------------------------------------------------------
sub cancelUerHoldReserve {
    my ($dbh,$uid)=@_;
    my @rhList=();
    my $tm = localtime;
    my $todayStr = sprintf("%04d-%02d-%02d", $tm->year +1900, ($tm->mon)+1, $tm->mday);

    my $sth_hold   =$dbh->prepare("select idReserve from opl_hold h inner join opl_reserve r using(idReserve)
                                   where h.dateExpiry>now() && h.dateLoan is null && h.dateCancel is null && uid=?");
    my $sth_reserve=$dbh->prepare("select idReserve from  opl_reserve 
                                    where dateExpiry>now() && numCopyReserve>0 && dateCancel is null && uid=?");
    $sth_hold->execute($uid);
    while(my ($id)=$sth_hold->fetchrow_array){
        push @rhList,$id;
    }
    $sth_reserve->execute($uid);
    while(my ($id)=$sth_reserve->fetchrow_array){
        push @rhList,$id;
    }
    foreach my $id(@rhList){
        circ_cancelHoldReserve($dbh,$id,$todayStr);
    }

}

#-------------------------------------------------------------
   
    sub _generateSrchCond{
        my($searchArg)=@_;
        my $condList=[]; 
        my $sField= $searchArg->{'sField'};
        my $term  = $searchArg->{'term'};

        if(defined $sField && defined $term &&( $term ne "" || $sField eq 'grade')){
           my $cond="";
           if($sField eq 'name' || $sField eq 'teacher'){
                 $term=~ s/ +/ /g;
                 $term=~ s/,/ /g; 
                 $term=~ s/'/\\'/g;
                my @data = split(' ', $term);
                if($sField eq 'teacher'){
                    foreach my $i (@data) {
                        if($i =~ m/\w+/g){
                            $cond .= " && " if ($cond && $cond ne "");
                            $cond .= " (teacher regexp '^$i' or teacher regexp '[^[:alnum:]]+$i' )";
                       }
                     }
                }
                else{
                    foreach my $i (@data) {
                        #if($i =~ m/\w+/g ){
                        if(length($i)>=1){
                            $cond .= " && " if ($cond && $cond ne "");

                            $cond .= " (lastname regexp '^$i' or lastname regexp '[^[:alnum:]]+$i' or 
                                firstname regexp '^$i' or firstname  regexp '[^[:alnum:]]+$i' or
                                nickname regexp '^$i' or  nickname regexp '[^[:alnum:]]+$i') ";
                        }
                    }

                    if($sField eq "nameBarcode"){
                        $cond .= " or " if ($cond && $cond ne "");
                        $cond .= " (sid='$term'  || userbarcode='$term' || username regexp '^$term')";
                    }
                
                }
            }
            elsif($sField eq 'userbarcode'){
                $cond .= " && " if ($cond && $cond ne "");
                $cond .= " (sid='$term'  || userbarcode='$term')";
            }
           elsif($sField eq 'username'){
                $cond .= " && " if ($cond && $cond ne "");
                $cond .= " (username='$term' || username regexp '^$term')";
            }
           elsif($sField eq 'phone'){
                my $phone= $term;
                $phone =~ s/^\s+$//g;
                $phone =~ s/[\-\s.()]+/ /g;
                $phone =~ s/ / +/g;
                $cond .= " && " if ($cond && $cond ne "");
                $cond .= " (match(phone,workPhone,cellphone) against (\"+$phone\"  IN BOOLEAN MODE ) )";
            }
             else{
                $cond .= " && " if ($cond && $cond ne "");
                $term =~ s/'/\\'/g;
                $cond .= " $sField = '$term'";
            }
            push @$condList,"($cond)";

       }
       if(defined $searchArg->{'categorycode'}){
           my @arr=();
           my @cArr= split(',',$searchArg->{'categorycode'});
           foreach my $s(@cArr){
               push @arr ," categorycode=$s" if($s ne "");
           }
           my $cond= join(" OR ", @arr);
           push @$condList,"($cond)" if($cond ne "");
       }
       if(defined $searchArg->{'idimport'} && $searchArg->{'idimport'}>0){
           my $cond= " idimport=" . $searchArg->{'idimport'} ;
           push @$condList,$cond;
       }

       if(defined $searchArg->{'status'}){
           my @arr=();
           my @sArr=split(",",$searchArg->{'status'});
           foreach my $s(@sArr){
               push @arr ," status=$s" if($s ne "");
           }
           my $cond= join(" OR ", @arr);
           push @$condList,"($cond)" if($cond ne "");
       }
       return $condList;

    }

#-------------------------------------------------------------
   
    sub user_getUserList{
        my($dbh,$searchArg,$all)=@_;
        
       my $condList=[]; 
       $condList=_generateSrchCond($searchArg);
       #$condList =[] if($searchArg->{'search'}->{'term'} eq "*" || $searchArg->{'search'}->{'term'} eq ""); 
       push @$condList,"u.uid<>1";
       
       $searchArg->{'status'}=undef;
       my $condList_statusGrp=[]; 
       $condList_statusGrp=_generateSrchCond($searchArg); 
       #$condList_statusGrp =[] if($searchArg->{'search'}->{'term'} eq "*" || $searchArg->{'search'}->{'term'} eq ""); 
       push @$condList_statusGrp,"u.uid<>1";

       my $query =" select  u.*,c.catname  from   opl_user u left outer join opl_category c on u.categorycode=c.catid ";
       my $query_count =" select  count(*) from   opl_user u";
       my $query_statusGrp =" select  status,count(status) count from  opl_user u";

       if(scalar(@$condList)>0){
           $query .= " where " . join (" AND " ,@$condList);
           $query_count .= " where " . join (" AND " ,@$condList);
       }
        if(scalar(@$condList_statusGrp)>0){
           $query_statusGrp .= " where " . join (" AND " ,@$condList_statusGrp);
       }
       
       my $sortFieldMap= {
                            "firstname"     =>" firstname sortDir,lastname sortDir",
                            "lastname"      =>" lastname sortDir,firstname sortDir",
                            "userbarcode"   =>" userbarcode sortDir",
                            "username"      =>" username sortDir",
                            "teacher"       =>" teacher sortDir, buildingcode sortDir, homeroom sortDir",
                            "homeroom"      =>" homeroom sortDir, buildingcode sortDir",
                            "buildingcode"  =>" buildingcode sortDir, homeroom sortDir",
                            "grade"         =>" grade sortDir",
                            "yeargraduation"=>" yeargraduation sortDir",
                            "created"       =>" created sortDir"
                         };
 
       my $sortStr= ""; my $sortDir="asc";
       if(defined $searchArg->{'sortBy'} && $searchArg->{'sortBy'} ne "" ){
            $sortStr = $sortFieldMap->{$searchArg->{'sortBy'}};
            $sortDir = $searchArg->{'sortDir'} if(defined $searchArg->{'sortDir'} && $searchArg->{'sortDir'} ne "");
            $sortStr =~ s/sortDir/$sortDir/g;
            $query .= " order by " .$sortStr;
       }
#       elsif($searchArg->{'sortBy'} eq "" || $searchArg->{'sortBy'} eq "lastname" ){
#           $query .= " order by lastname,firstname " ;
#       }
#       elsif($searchArg->{'sortBy'} eq "firstname" ){
#           $query .= " order by firstname,lastname " ;
#       }
#        elsif($searchArg->{'sField'} =~ m/teacher/gi){
#           $query .= " order by teacher";
#       }

       if(!$all){
            my $offset= ($searchArg->{'pNum'} -1)*$searchArg->{'pSize'};
            $query .= " limit  $offset," . $searchArg->{'pSize'};
       }
       $query_statusGrp .=" group by status";

        my ($numOfUserFound) =$dbh->selectrow_array($query_count);
        my $userStatusGrp={inactive=>0,active=>0,block=>0};
        my @userList=();
     # open debug,">/tmp/qq"; print debug "[sField::: $searchArg->{'sField'}][sortBy::: $searchArg->{'sortBy'}] $query\n"; close debug;
        if($numOfUserFound>0){
            my $sth =$dbh->prepare($query);
            $sth->execute();
            my $statusMap= {'0'=>'inactive','1'=>'active','2'=>'block'};
            while(my $user =$sth->fetchrow_hashref){
                $user->{'statusName'}=$statusMap->{$user->{'status'}};
                push @userList,$user;
            }
            $sth =$dbh->prepare($query_statusGrp);
            $sth->execute();
            my $total=0;
            while(my ($s,$c)=$sth->fetchrow_array){
                $total +=$c;
                $userStatusGrp->{$statusMap->{$s}}=$c;

            }
            $userStatusGrp->{'total'}=$total;

        }
        
        return {numOfUserFound=>$numOfUserFound,userList=>\@userList,userStatusGrp=>$userStatusGrp};
    }
         
#-------------------------------------------------------------
#
#
sub user_getUserCircCount{
    my($dbh,$userList)=@_;
    my @uidArr;
    foreach my $u (@{$userList}){
        $u->{'onloan'}=0;
        $u->{'od'}=0;
        push @uidArr,$u->{'uid'};
    }
    my $uisList = join("," , @uidArr);

    my $sql=<<_SQL_;
select l.*,o.od from (select uid,count(uid) onloan from opl_loan where uid in($uisList) && dateReturn is null group by uid) l
 left outer join (select uid,count(uid) od from opl_loan where uid in($uisList) && dateReturn is null && dateDue <now() group by uid) o using(uid)
_SQL_
  my $sth =$dbh->prepare($sql);  
  $sth->execute();
  while(my $uCirc =$sth->fetchrow_hashref){
    foreach my $u (@{$userList}){
       if($u->{'uid'} == $uCirc->{'uid'}){
          $u->{'onloan'}= $uCirc->{'onloan'};  
          $u->{'od'}= $uCirc->{'od'}||0;
          last;  
       }
    }
  }
}
#-------------------------------------------------------------
#
#
sub user_getUserCircCount_textbook{
    my($dbh,$userList)=@_;
    my @uidArr;
    foreach my $u (@{$userList}){
        $u->{'onloan_tb'}=0;
        $u->{'od_tb'}=0;
        push @uidArr,$u->{'uid'};
    }
    my $uisList = join("," , @uidArr);

    my $sql=<<_SQL_;
select l.*,o.od from (select uid,count(uid) onloan from tb_loan where uid in($uisList) && dateReturn is null group by uid) l
 left outer join (select uid,count(uid) od from tb_loan where uid in($uisList) && dateReturn is null && dateDue <now() group by uid) o using(uid)
_SQL_
  my $sth =$dbh->prepare($sql);  
  $sth->execute();
  while(my $uCirc =$sth->fetchrow_hashref){
    foreach my $u (@{$userList}){
       if($u->{'uid'} == $uCirc->{'uid'}){
          $u->{'onloan_tb'}= $uCirc->{'onloan'};  
          $u->{'od_tb'}= $uCirc->{'od'};
          last;  
       }
    }
  }
}
sub user_getUserCircCount_equipment {
 my($dbh,$userList)=@_;
    my @uidArr;
    foreach my $u (@{$userList}){
        $u->{'onloan_eq'}=0;
        $u->{'od_eq'}=0;
        push @uidArr,$u->{'uid'};
    }
    my $uisList = join("," , @uidArr);

    my $sql=<<_SQL_;
select l.*,o.od from (select uid,count(uid) onloan from eq_loan where uid in($uisList) && dateReturn is null group by uid) l
 left outer join (select uid,count(uid) od from eq_loan where uid in($uisList) && dateReturn is null && dateDue <now() group by uid) o using(uid)
_SQL_
  my $sth =$dbh->prepare($sql);  
  $sth->execute();
  while(my $uCirc =$sth->fetchrow_hashref){
    foreach my $u (@{$userList}){
       if($u->{'uid'} == $uCirc->{'uid'}){
          $u->{'onloan_eq'}= $uCirc->{'onloan'};  
          $u->{'od_eq'}= $uCirc->{'od'};
          last;  
       }
    }
  }

}

#-------------------------------------------------------------
# $field: homeroom/grade/teacher in table opl_user
#-------------------------------------------------------------
sub user_getHR_Grade_TeacherList{
    my($dbh,$field)=@_;
    my $query  =" select  distinct($field) as val from   opl_user  order by $field"; 
    my $sth =$dbh->prepare($query);
    $sth->execute();
    my @retList=();
    while(my $r =$sth->fetchrow_hashref){
        $r->{'name'} = $r->{'val'};
        if($r->{'val'} eq""){
            $r->{'name'}='none';
        }
        push @retList,$r;
    }
    return \@retList;
}

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

sub user_accessAllowed{
    my($dbh,$cgi,$uri)=@_;
    $uri=~ s/\/+/\//g;
    my ($errCode, $ck, $user) = user_currentUser($dbh, $cgi);
    my $permissions={};
    my $allowAccess=0;
    if($user){
        $permissions=user_permission_1($dbh, $user->{'uid'});
    }
    my $scriptRegistry=ureg_getUrlRegistry($permissions);
    foreach my $e(keys %$scriptRegistry){
        next if($e eq 'url_self');
        if($scriptRegistry->{$e} eq $uri || $scriptRegistry->{$e} =~ m/^$uri\?/){
            $allowAccess=1; 
            last ;
       }
   
    }
    return $allowAccess;



}
#----------------------------------------------------------
sub user_getUserCircInfo{
    my ($dbh,$uid)=@_;
    my $userCircInfo={};
    $userCircInfo->{'info'} =getUserInfo($dbh,$uid);
    $userCircInfo->{'unpaidChargeList'} = trans_getUnpaidChargeList($dbh, $uid);

    my $balance = trans_getBalance($dbh,$uid );
       $balance = floor($balance*100 + 0.50)/100; 
       $balance = sprintf("%.2f",$balance);

    $userCircInfo->{'accountBalance'} = $balance;
    $userCircInfo->{'loanList'}    =getLoanItems($dbh, $uid);
    $userCircInfo->{'reserveList'} =getReservedItems($dbh, $uid);
    return $userCircInfo;
}


#----------------------------------------------------------
sub getUserInfo{
    my ($dbh,$uid) = @_;
    my $userInfo=undef;
    my ($user, $guardian) = user_getInformationById($dbh, $uid);
    if(defined $user){
        $userInfo={};
        foreach my $f (qw(uid userbarcode username firstname lastname nickname homeroom teacher grade program studies notes status expired categorycode email)){
            $userInfo->{$f}=$user->{$f};
        }
    }
    return $userInfo;
}
#----------------------------------------------------------
sub getReservedItems
{
    my ($dbh,$uid) = @_;

    my $reserveList = circ_userListReserve($dbh, $uid);
    foreach my $reserve (@$reserveList) 
    {
        if($reserve->{'numCopyReserve'}==0){
            $reserve->{'dateExpiry'}  ='';
        }
    }
    return $reserveList;
}
#----------------------------------------------------------

sub getLoanItems
{
    my ($dbh, $uid) = @_;

    my  $loanList = circ_userListLoan($dbh, $uid);
    foreach my $loan (@$loanList) 
    {
        if($loan->{'barcode'} =~m/^\_\_\_(.*)/){
            $loan->{'org_barcode'} =$1;
        }
        else{
            $loan->{'org_barcode'} =$loan->{'barcode'};
        }
        if($loan->{'tempIll'} =~m/ILL|temporary/i){
            $loan->{'isTempILL'} =1;
        }
        $loan->{'isReserved'}=circ_isOnReserve($dbh,$loan->{'rid'});
    }
    return $loanList;
}

#----------------------------------------------------------
sub _getRelation{
    my ($dbh,$uid)=@_;
    my $relation={direct=>[],byRef=>[]};
    my $relExist={};
    my $relList_direct=_getRelationList($dbh,$uid);
    foreach my $rel(@$relList_direct){
        my $role1=$rel->{'role'};
        if(!$relExist->{$rel->{'relUid'}}){
            if(!$rel->{'type'}){
                push @{$relation->{'direct'}},$rel;
            }
            else{
                push @{$relation->{'byRef'}},$rel;
            }
            $relExist->{$rel->{'relUid'}}=1;
        }
        my $relList_ref =_getRelationList($dbh,$rel->{'relUid'});
        foreach my $rel_ref(@$relList_ref){
            next if( $rel_ref->{'relUid'} == $uid);
            my $role2=$rel_ref->{'role'};
            my $refRel=_driveRel(lc($role1),lc($role2));
            if($refRel){
                $rel_ref->{'role'}=$refRel;
                if(!$relExist->{$rel_ref->{'relUid'}}){
                    push @{$relation->{'byRef'}},$rel_ref;
                    $relExist->{$rel_ref->{'relUid'}}=1;
                }

            }
        }
    }
    return $relation;
}

#----------------------------------------------------------
sub _getRelationList{
    my ($dbh,$uid)=@_;
    my $reverseRel={father=>'child',
                    mother=>'child',
                    sibling=>'sibling',
                    child=>'parent'

                   };
    my $sth =$dbh->prepare("select relUid,role from opl_userRelation  where uid=?");
    $sth->execute($uid);
    my @relList=();
    while(my $rel=$sth->fetchrow_hashref){
        push @relList,$rel;
    }
    $sth =$dbh->prepare("select uid as relUid ,role from opl_userRelation where relUid=?");
    $sth->execute($uid);
    while(my $rel=$sth->fetchrow_hashref){
        if($reverseRel->{lc $rel->{'role'}}){
            $rel->{'role'}=$reverseRel->{lc $rel->{'role'}};
            $rel->{'type'}="reference";
            push @relList,$rel;
        }
    }
    return \@relList;
}

#----------------------------------------------------------
sub _driveRel{
    my($rel1,$rel2)=@_;
    my $refRel=undef;
    if($rel1 eq 'father'){
        if($rel2 eq 'wife'){
            $refRel='mother';
        }
        elsif($rel2 eq 'child'){
            $refRel='sibling';
        }
    }
    elsif($rel1 eq 'mother'){
        if($rel2 eq 'husband'){
            $refRel='father';
        }
        elsif($rel2 eq 'child'){
            $refRel='sibling';
        }
    }
    elsif($rel1 eq 'sibling' && ($rel2 eq 'father' || $rel2 eq 'mother' || $rel2 eq 'sibling')){
            $refRel=$rel2;
    }
    elsif(($rel1 eq 'husband' || $rel1 eq 'wife' )&& $rel2 eq 'child'){
        $refRel=$rel2;
    }
    elsif($rel1 eq 'child'){
        if($rel2 eq 'mother'){
            $refRel='wife';
        }
        elsif($rel2 eq 'father'){
            $refRel='husband';
        }
        elsif($rel2 eq 'sibling'){
            $refRel='child';
        }
    }
    return $refRel;

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

1;