#!/usr/bin/perl

#use utf8;
use strict;
use POSIX qw(
    ceil
    floor
);
use CGI;

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

use Opals::User qw(
    user_balance
    user_getInformation
    user_LoadCategory
);
use Opals::Transactions qw(
    trans_getBalance
);
use Opals::Circulation qw(
    circ_userListLoan
    circ_cancelHoldReserve
);

use Opals::Date qw(
    date_parse
    date_today
    date_text
);
 
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::User qw(
    user_currentUser
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

use Time::localtime;

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

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

    my $pagesize = 20;

     my $status= $input->{'status'};
     $status =~ s/^,+//g;
     $status =~ s/,+$//g;
    my @u_status = split(/,/, $status); 
    if (scalar(@u_status)==0) {
       $template->param(all => 1);
    }
    foreach my $val (@u_status){
       if ( $val eq "active" ){
          $template->param(active => 1);
       }
        elsif ( $val eq "block" ){
           $template->param(block => 1);
        }
        elsif ( $val eq "inactive" ){
           $template->param(inactive => 1);
        }
    }

    my $szOldWhere = MakeFilter($input->{'oldfiltertype'}, $input->{'oldfiltervalue'}, 
                                    $input->{'oldstatus'}, $input->{'oldbrwrtype'});
    my $szWhere    = MakeFilter($input->{'filtertype'}, $input->{'filtervalue'}, 
                                    $input->{'status'}, $input->{'brwrtype'});



    my @brwrtype = user_LoadCategory($dbh);
    my %idxType = {};
    my $i=1;
    foreach my $rec (@brwrtype)
    {   
        $idxType{$rec->{'catid'}} = $rec->{'catname'};
        if ($rec->{'catname'} eq "Inter Library Loan"){
             $rec->{'catname'} = "ILL";   
        }
        $rec->{'order'} =$i++;

    }            

    # Page number
    if ( !$input->{'pNum'} ) { $input->{'pNum'} = 1; }
    if ( !$input->{'sortDir'} ) { $input->{'sortDir'} = 'asc'; }
    #$input->{'pNum'} = ceil($input->{'pNum'});
     # Do actions
     my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
    if ( $input->{'op'} eq 'delete' ){
        my $countNotDel =0;    
        $countNotDel = deleteUser($dbh, $input, $user->{'uid'}, $szWhere);
        SetCurPage($dbh, $input, $countNotDel, $pagesize, $szWhere);

        if ( $countNotDel ) {
            GetCircInfo($template, $dbh, $input->{'uidlist'});
            $template->param(countNotDel => $countNotDel);
            $template->param(filtervalue => $input->{'filtervalue'});
            $template->param(filtertype => $input->{'filtertype'});
            $template->param(ordervalue => $input->{'ordervalue'});
            $template->param(status => $input->{'status'});
            $template->param(brwrtype => $input->{'brwrtype'});

            $template->param(importer => $input->{'importer'});            
            ### tmpl_write($dbh, $cgi, $cookie, $template);
            #####  exit 0;
        }
        
    }
    elsif ( $input->{'op'} eq 'update' )    {
        UpdateSelected($dbh, $input, $template, $szOldWhere, $user->{'uid'});
        $input->{'op'}="";
    }
       
    
    GetBorrowerList($dbh, $input, $template, \%idxType, $pagesize, $szWhere);
    MakePages ($dbh, $input, $template, $pagesize, $szWhere); 

    $template->param(movebrwrtype => \@brwrtype);
    @brwrtype = ({catid =>' ', catname => 'All', brsel=>1,order=>0} , @brwrtype);

   

    my $tmpCatList= "," . $input->{'brwrtype'} .",";
    for(my $i=1; $i< scalar(@brwrtype);$i++)
    {   
        if($tmpCatList =~ /\,@brwrtype[$i]->{'catid'}\,/ ){
                @brwrtype[$i]->{'brsel'} = 1;
                @brwrtype[0]->{'brsel'}=0;
        }
    }
    my $brtypeList=[];
    my $j=-1;
    for(my $i=0; $i< scalar(@brwrtype);$i++){
        if($i%6  ==0){
            $j++;
            $brtypeList->[$j]->{'row'}=[];
        }
        push @{$brtypeList->[$j]->{'row'}},@brwrtype[$i];
    }


# Fill up the list of importings
    my @importlist = ({idimport => -1, lastname => 'Any', imsel => ($input->{'importer'} == -1 ? 1 : 0) }, 
        {idimport => 0, lastname => 'Add manually', imsel => ($input->{'importer'} == 0 ? 1 : 0) });
    
    my $qrImport = $dbh->prepare("select i.idimport,i.dateImport,t.lastname,t.firstname  from opl_userimport i left outer join opl_user u using(idimport) inner join opl_user t on t.uid=i.uid where u.uid is not null group by idimport order by dateImport desc,lastname, firstname");
    $qrImport->execute();
    
    while ( my $rec = $qrImport->fetchrow_hashref )
    {
        if ( $rec->{'idimport'} == $input->{'importer'} ) { $rec->{'imsel'} = 1; }
        push @importlist, $rec;
    }
    if ( scalar(@importlist) == 2 ) { @importlist = (); }
    $qrImport->finish;
    $template->param(importlist => \@importlist);
    
    $template->param(brwrtypeList => $brtypeList);
    $template->param(op => $input->{'op'});

    $template->param(filterval => $input->{'filtervalue'});
    $template->param('sel' . $input->{'filtertype'} => 1);
    $template->param('sort' . $input->{'ordervalue'} => 1);
    $template->param($input->{'status'} => 1);
    $template->param(whole => $input->{'whole'});

    $template->param(pNum => $input->{'pNum'});
    $template->param(uidlist => $input->{'uidlist'});
#    $template->param(xxx => $input->{'uidlist'});

#----------- Save old parameters -----------
    $template->param(oldfiltertype => $input->{'filtertype'});
    $template->param(oldfiltervalue => $input->{'filtervalue'});
    $template->param(oldstatus => $input->{'status'});
    $template->param(oldbrwrtype => $input->{'brwrtype'});
    $template->param(oldordervalue => $input->{'ordervalue'});
    ($input->{'sortDir'} eq 'desc')? $template->param(sortDesc => 1): $template->param(sortAsc => 1);
        

#Thu, Jan 07, 2010 @ 13:50:35 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('user/userInfo.msg',$msgValMap);
loc_write($template,$msgMap);
 $template->param(hlpUrl     => Opals::Constant->getHlpUrl('grpedit') );

    tmpl_write($dbh, $cgi, $cookie, $template);

#-------------------------------------------------------
sub SetCurPage
{
    my ($dbh, $input, $countNotDel, $pagesize, $szWhere) = @_;

    my $offsetFirstPage = $input->{'pNum'}*$pagesize + $countNotDel;

    # count all items
    my $szSQL = "select count(*) from opl_user" . $szWhere;
    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($TotalRec) = $query->fetchrow_array;
    $query->finish;

    if ( $offsetFirstPage > $TotalRec )
    {
        $input->{'pNum'} = ceil($TotalRec/$pagesize);
    }
}

#-------------------------------------------------------
sub UpdateSelected
{
    my ($dbh, $input, $template, $szOldWhere, $idcurrent) = @_;

    my $status = -1;
    if ( $input->{'availstatus'} eq 'active' ) { $status=USER_ACTIVE; }
    elsif ( $input->{'availstatus'} eq 'block' ) { $status=USER_BLOCK; }
    elsif ( $input->{'availstatus'} eq 'inactive' ) { $status=USER_INACTIVE; }
        
    # Make query to change user's information 
    $input->{'schlvalue'}=~ s/'/\\'/g;
    my $szSql = "update opl_user set";
    if ( $status != -1 )
    {
        $szSql .= " status=$status,";
    }

    if ( $input->{'movebrwrtype'} != -1 )
    {
        $szSql .= " categorycode=$input->{'movebrwrtype'},";
    }

    if ( $input->{'schlinfo'} eq "hmroom" )
    {
        $szSql .= " homeroom='" . $input->{'schlvalue'} . "'";
    }
    elsif ( $input->{'schlinfo'} eq "blding" )
    {
        $szSql .= " buildingcode='" . $input->{'schlvalue'} . "'";
    }
    elsif ( $input->{'schlinfo'} eq "tcher" )
    {
        $szSql .= " teacher='" . $input->{'schlvalue'} . "'";
    }
    elsif ( $input->{'schlinfo'} eq "grade" )
    {
        $szSql .= " grade='" . $input->{'schlvalue'} ."'";
    }
    elsif ( $input->{'schlinfo'} eq "yeargraduation" )
    {
        $szSql .= " yeargraduation=" . $input->{'schlvalue'};
    }

    $szSql =~ s/,$//;
    $szSql .= " where uid=?";
    #$template->param(xxx => $szSql);

    my $queryUpdt = $dbh->prepare($szSql);

    my $CountNotChange = 0;

    # Collect the list of user uid
    my @arrId = ();
    if ( $input->{'whole'} )
    {
        my $szSQL = "select uid from opl_user" . $szOldWhere;
        my $query = $dbh->prepare($szSQL);
        $query->execute();
        while ( my ($id) = $query->fetchrow_array ) { push @arrId, $id; }
        $query->finish;
    }
    else
    {
        @arrId = split /,/, $input->{'uidlist'};
    }
    # Update each user
    my $listNotUpdated = '';
    foreach my $uid (@arrId)
    {
        if ( $status != -1 )
        {
            my $bCanNotDelete = 0;
            if ( $status == USER_INACTIVE )
            {
                my $loanList = circ_userListLoan($dbh, $uid);

                my $balance = user_balance($dbh, $uid);
                $bCanNotDelete = ( @$loanList || $balance >0 || $idcurrent == $uid );
                if ( $bCanNotDelete ) { $CountNotChange++; $listNotUpdated .= $uid . ','; next; }
            }
        }
        $queryUpdt->execute($uid);
        $queryUpdt->finish;
    }
    $input->{'uidlist'} = $listNotUpdated;
    $template->param(CountNotChange => $CountNotChange);
}
#-------------------------------------------------------
#Mon, Jun 04, 2012 @ 09:18:34 EDT
sub getUdiLis2Del{
    my ($dbh, $input,  $szWhere) = @_;   
# Get  user list to delete
    my $sql = "select uid  from opl_user"; 
    $sql .= $szWhere;
    my @uidList = ();
    my $query = $dbh->prepare($sql);
    $query->execute();
    while ( my ($uid) = $query->fetchrow_array ){
        push @uidList, $uid;
    }
    $query->finish;
    return \@uidList;
}

#-------------------------------------------------------
# Mon, Jun 04, 2012 @ 09:18:34 EDT
sub deleteUser{
    my ($dbh, $input, $idCurrent, $szWhere) = @_;  
    my $numNotDel = 0;
    my $newlist = '';
    my $uidList =[];
    if($input->{'whole'} eq "1"){
        $uidList = getUdiLis2Del($dbh, $input,  $szWhere);
    }
    else{
        @$uidList = split /,/, $input->{'uidlist'};

    }
    ($numNotDel,$newlist) = _onProcessDeleteUser($dbh, $uidList, $idCurrent);
    $input->{'uidlist'} = $newlist;
    $input->{'whole'}="0";
    return  $numNotDel;
}



#-------------------------------------------------------
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 check if user is loanning an item
    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);

    
    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;
        my $bal = user_balance($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 ||  $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 DeleteSelected_bk{
    my ( $dbh, $input, $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 check if user is loanning an item
    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);

# Query delete user's guardian
    my $szDelGuardianSql = "delete from opl_guardian where uid = ?";
    my $queryDelGuardian = $dbh->prepare($szDelGuardianSql);

# Query delete reserve
    my $szDelRsvSql = "delete from opl_reserve where uid = ?";
    my $queryDelRsv = $dbh->prepare($szDelRsvSql);

# Query retrieve reserve of user
    my $szIdRsvSql = "select idReserve from opl_reserve where uid = ?";
    my $queryIdRsv = $dbh->prepare($szIdRsvSql);

# Query delete a holding
    my $szDelHoldSql = "delete from opl_hold where idReserve = ?";
    my $queryDelHold = $dbh->prepare($szDelHoldSql);

# Query delete all closed transactions of a user
    my $szDelLoanSql = "delete from opl_loan where uid=?";
    my $queryDelLoan = $dbh->prepare($szDelLoanSql);

# Query delete all closed transactions of a user
    my $queryIdLoanSql = "select idloan from opl_loan where uid=?";
    my $queryIdLoan = $dbh->prepare($queryIdLoanSql);
    
# Query delete all payments from a user
    my $szDelPaySql = "delete from opl_payment where uid=?";
    my $queryDelPay = $dbh->prepare($szDelPaySql);

# Query delete all reports of lost by a user
    my $szDelLost = "delete from opl_lost where idloan=?";
    my $queryDelLost = $dbh->prepare($szDelLost);

# Query delete all reports of damage by a user
    my $szDelDamage = "delete from opl_damage where idloan=?";
    my $queryDelDamage = $dbh->prepare($szDelDamage);

# Query delete all reports of overdue by a user
    my $szDelOverdue = "delete from opl_overdue where idloan=?";
    my $queryDelOverdue = $dbh->prepare($szDelOverdue);
  

# Query get a lost item
    #Ha my $szLostSql = "select barcode, ondate from opl_lost as lt, opl_loan ln where lt.idloan=? && lt.idloan=ln.idloan";
    my $szLostSql = "select barcode, ondate from opl_odl as lt, opl_loan ln where  lt.type='lost' &&  lt.idloan=? && lt.idloan=ln.idloan";
    my $queryLost = $dbh->prepare($szLostSql);

# Query move a lost item with borrower to one without borrower
    my $szLostStatus = "insert into opl_itemstatus set ondate=?, status=3, barcode=?";
    my $queryLostStatus = $dbh->prepare($szLostStatus);
   
# Query get a damaged item
    #my $szDamageSql = "select barcode, ondate from opl_lost as lt, opl_loan ln where lt.idloan=? && lt.idloan=ln.idloan";
    my $szDamageSql = "select barcode, ondate from opl_odl as lt, opl_loan ln where  lt.type='damaged' &&  lt.idloan=? && lt.idloan=ln.idloan";
    my $queryDamage = $dbh->prepare($szDamageSql);

# Query move a damaged item with borrower to one without borrower
    my $szDamageStatus = "insert into opl_itemstatus set ondate=?, status=2, barcode=?";
    my $queryDamageStatus = $dbh->prepare($szDamageStatus);
        
    my $count = 0;
    my $newlist = '';
    foreach my $uid (split /,/, $input->{'uidlist'})
    {
        # if this user is loaning some items, leave it
        $queryLoan->execute($uid);
        my $recLoan = $queryLoan->fetchrow_hashref;
        my $bal = user_balance($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 ||  $bal != 0 || $idCurrent == $uid ){
            $count++; $newlist .= $uid . ','; next;
        }
        # Move deleted user into opl_deletedUser
        $queryInsert->execute($uid);
       
        cancelUerHoldReserve($dbh,$uid);
      # delete this user
        $queryDel->execute($uid) || return;
    }
  
    $input->{'uidlist'} = $newlist;

    $queryUser->finish;
    $queryLoan->finish;
    $queryDelOverdue->finish;
    $queryDelDamage->finish;
    $queryDelLost->finish;
    $queryDelPay->finish;
    $queryDelLoan->finish;
    $queryDelHold->finish;
    $queryIdLoan->finish;
    $queryIdRsv->finish;
    $queryDelRsv->finish;
    $queryDelGuardian->finish;
    $queryDel->finish;
    return $count;

}

#-------------------------------------------------------
sub GetBorrowerList
{
    my ($dbh, $input, $template, $brwrtype, $pagesize, $szWhere) = @_;   
    
# Get the page offset $szwhere .= " && status=" . USER_ACTIVE;
    my $pageoffset = $input->{'pNum'};
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($input->{'pNum'} - 1)*$pagesize; }
    
# Get the total of users
    my $sql = "select count(*) from opl_user "; 
   
    $sql .= $szWhere;
    my $query = $dbh->prepare($sql);
    $query->execute();
    my @Total = $query->fetchrow_array;
    $query->finish;

    if ( $Total[0] < $pageoffset )
    {
        $input->{'pNum'} = ceil($Total[0]/$pagesize);
        $pageoffset = ($input->{'pNum'} - 1)*$pagesize;
    }
    
# Get the users in the current page
    $sql = "select * from opl_user"; 
    $sql .= $szWhere;
    if ( $input->{'ordervalue'} ){   
        if($input->{'ordervalue'} eq 'grade'){
            $sql .= " order by cast(" . $input->{'ordervalue'} . " as UNSIGNED) "  . $input->{'sortDir'} 
                  . " , " . $input->{'ordervalue'} . " "  . $input->{'sortDir'} ;
        }
        else{
            $sql .= " order by " . $input->{'ordervalue'} . " " . $input->{'sortDir'} ;
        }
       $sql .= " , lastname asc" if($input->{'ordervalue'} ne 'lastname' && $input->{'ordervalue'} ne 'firstname') ; 
    }
    else
    { $sql .= " order by lastname "; }
    #$sql .= " " . $input->{'sortDir'};
    $sql .= "  limit $pageoffset, $pagesize";

    $query = $dbh->prepare($sql);
    #$template->param(debug =>$sql);
    $query->execute();
    my @BrwrList = ();
    my $IsSelected = 0;
    my $row = 0;
    my $uidList = $input->{'uidlist'};
#    if ( $input->{'uidlist'} !~ m/^,/ )
#    {
#        $uidList = ',' . $uidList;
#    }
    $uidList =~ s/^,*/,/;
    while ( my $rec = $query->fetchrow_hashref )
    {   
        if ( $row%2 ) { $rec->{'clrcode'} = 1; }
        else  { $rec->{'clrcode'} = 0; }

        $rec->{'row'} = $row++;
        
        $rec->{'catname'} = $brwrtype->{$rec->{'categorycode'}};
        $rec->{'statusname'} = 'inactive' if ( $rec->{'status'} == USER_INACTIVE);
        $rec->{'statusname'} = 'active' if ( $rec->{'status'} == USER_ACTIVE);
        $rec->{'statusname'} = 'block' if ( $rec->{'status'} == USER_BLOCK);

        $rec->{'block'} = 1 if ( $rec->{'status'} == USER_BLOCK || $rec->{'status'} == USER_INACTIVE);

        $rec->{'notes'} =~ s/--//g;
        $rec->{'notes'} =~ s/^\s+|\s+$//g;
        if($rec->{'notes'} eq ''){
            $rec->{'notes'} = undef;
        }
        #--- Get overdue/loans info
        my $loanList = circ_userListLoan($dbh, $rec->{'uid'});
        my $odCount = 0;
        foreach my $loan (@$loanList) 
        {
            if ( $loan->{'overdue'} ) { $odCount++; }
        }
        $rec->{'odloans'} =  $odCount . "/" . scalar(@$loanList) ;
        if ($rec->{'created'} =~ /^([\d]{4})-([\d]{1,2})-([\d]{1,2})/) {
              $rec->{'created'} = "$1-$2-$3";
        }  
        $rec->{'isAdminUser'} =(int($rec->{'uid'}) == 1)? 1:0;

        my $balance =trans_getBalance($dbh,$rec->{'uid'});
        $balance = floor($balance*100 + 0.50)/100; 
        $balance = sprintf("%.2f",$balance);
        if($balance != 0){
            $rec->{'balance'} = $balance;  
        }
        push @BrwrList, $rec;
    }
    $query->finish;
    $template->param(userlist => \@BrwrList);
    $template->param(NumUser => scalar(@BrwrList));
}

#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $szWhere) = @_;
    my $szSQL = "select count(*) from opl_user " . $szWhere;
    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($count) = $query->fetchrow_array;
    $query->finish;


    my @arrayref = ();
    if ( !$input->{'pNum'} )
        { @arrayref = tmpl_rangedPageList($count, 1, $pagesize, 10); }
    else
        { @arrayref = tmpl_rangedPageList($count, $input->{'pNum'}, $pagesize, 10); }

    $template->param(rangedPageList => \@arrayref);
    $template->param(totaluser => $count);

    
    my $szUserSQL = "select status,count(status) as count from opl_user " . $szWhere . " group by  status";
    $query = $dbh->prepare($szUserSQL);
    
    $query->execute();
    while ( my $rec = $query->fetchrow_hashref ){
        if($rec->{'status'}==USER_ACTIVE){
            $template->param(activeuser => $rec->{'count'});
        }
        elsif($rec->{'status'}==USER_INACTIVE){
            $template->param(inactiveuser => $rec->{'count'});
        }
        elsif($rec->{'status'}==USER_BLOCK){
            $template->param(blockeduser => $rec->{'count'});
        }
    }
    
    my @uidlist = split /,/, $input->{'uidlist'};
    $template->param(selecteduser => scalar(@uidlist));
}

#----------------------------------------------------------
sub MakeFilter
{
    my ($filtertype, $filtervalue, $status, $brwrtype) = @_;
    my $szWhere = '';
    my $condStr="";
    my $tmpCondStr="";
    if ( !defined $input->{'importer'} ) { $input->{'importer'} = -1; }

    if ( $filtervalue ne '' )
    {
        my $szCond = $filtervalue;
        $szCond =~ s/^\s+//;
        $szCond =~ s/\s+$//;
        $szCond =~ s/'/\\'/g;
        if ( $filtertype eq 'name' ){ 
            $szCond =~ s/ +/ /g;
            $szCond =~ s/,/ /g; 
            my @data = split(' ', $szCond);
            $condStr = "  (";
            foreach my $i (@data) {
                if($i =~ m/\w+/g){
                    $condStr  .= " ( lastname like '$i%' or lastname like '% $i%' or 
                                      firstname like '$i%' or firstname like '% $i%'
                                    )  and";                  
                }
             }
            $condStr =~ s/ and$/)/;
            #$condStr = " (firstname like '$szCond%' || lastname like '$szCond%')"; 
        }
        elsif ( $filtertype eq 'city' ) { $condStr= " city like '$szCond%'"; }
        elsif ( $filtertype eq 'zippostal' ) { $condStr = "zip = '$szCond'"; }
        elsif ( $filtertype eq 'phone' ) { $condStr = " phone = '$szCond'"; }
        elsif ( $filtertype eq 'grade' ) { $condStr = " grade = '$szCond'"; }
        elsif ( $filtertype eq 'teacher' ) { $condStr = " teacher like '$szCond%'"; }
        elsif ( $filtertype eq 'homeroom' ) { $condStr = " homeroom =  '$szCond'"; }
        elsif ( $filtertype eq 'buildingcode' ) { $condStr = " buildingcode = '$szCond'"; }
        elsif ( $filtertype eq 'yeargraduation' ) { $condStr = " yeargraduation = $szCond"; }
    }

  # User type
    $brwrtype =~ s/^ +//g;
    $brwrtype =~ s/ +$//g;
    $brwrtype =~ s/^,+//g;
    $brwrtype =~ s/,+$//g;
    my @u_type = split(/,/, $brwrtype);
    
    foreach my $val (@u_type){
        $val =~ s/ +//g;
        if($val ne ''){
            $tmpCondStr .=' || categorycode=' . $val;
        }
    }
    $tmpCondStr =~ s/^ +//g;
    $tmpCondStr =~ s/^\|\| //g;
    if($tmpCondStr ne ''){
        if($condStr ne ''){
            $condStr .= " && (" . $tmpCondStr .")" ;
        }
        else{
            $condStr = " ($tmpCondStr )" ;

        }
    }

    if ( defined $input->{'importer'} && $input->{'importer'} != -1){
        if ( $condStr ) { $condStr .= " && idimport = $input->{'importer'}"; }
        else { $condStr = "  idimport = $input->{'importer'}"; }
    }

    $tmpCondStr="";
    # User status
    $status =~ s/,+$//g;
    my @u_status = split(/,/, $status); 
    
    foreach my $val (@u_status){
       if ( $val eq "active" ){
           $tmpCondStr .=' || status=' . USER_ACTIVE;
       }
        elsif ( $val eq "block" ){
            $tmpCondStr .=' || status=' . USER_BLOCK;
        }
        elsif ( $val eq "inactive" ){
            $tmpCondStr .=' || status=' . USER_INACTIVE;
        }
    }
    $tmpCondStr =~ s/^ +//g;
    $tmpCondStr =~ s/^\|\| //g;
    if($tmpCondStr ne ''){
        if($condStr ne ''){
            $condStr .= " && (" . $tmpCondStr .")";
        }
        else{
            $condStr =  $tmpCondStr ;

        }
    }
      if($condStr ne ''){
        return " where $condStr";
    }
    return "";
}
#-------------------------------------------------------
sub GetCircInfo
{
    my ($template, $dbh, $uidlist) = @_;
    
    my $szLoanSql = "select count(*) from opl_loan where uid = ? and dateReturn is null";
    my $queryLoan = $dbh->prepare($szLoanSql);
    my $szUserSql = "select userbarcode, username, firstname, lastname, status from opl_user where uid=?";
    my $queryUser = $dbh->prepare($szUserSql);

    my @notdellist;
    foreach my $uid (split /,/, $input->{'uidlist'})
    {
        $queryLoan->execute($uid);
        my $rec;
        my ($loan) = $queryLoan->fetchrow_array;
        if ( $loan ) { $rec->{'loan'} = $loan; }
        my $bal = user_balance($dbh, $uid);
        if ( $bal ) { $rec->{'bal'} = $bal; }
        
        $queryUser->execute($uid);
        my $recUser = $queryUser->fetchrow_hashref;
        $rec->{'uid'} = $recUser->{'uid'};
        $rec->{'firstname'} = $recUser->{'firstname'};
        $rec->{'lastname'} = $recUser->{'lastname'};
        $rec->{'active'} = $recUser->{'status'};
        push @notdellist, $rec;
    }
    $queryUser->finish;
    $queryLoan->finish;
    $template->param(notdellist => \@notdellist);
}
#----------------------------------------------------------
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);
    }

}

#-------------------------------------------------------------------------------------------------
__END_OF_FILE: