#!/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::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 $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/user_del.tmpl',
            reqPermission   => 'user_delete',
        }
);

#if ($permission && $permission->{'user_delete'}) 
#{
#    my $input = $cgi->Vars();
#    if ($input->{'op'} eq 'delete') 
#    {
#        if (user_delete($dbh, $input)) 
#        {
#            $template->param(success => 1);
#        }
#        else 
#        {
#            $template->param(error => 1);
#        }
#    }
#    $template->param(userDel => 1);
#}
    my $pagesize = 20;
    my $szWhere = '';

    if ( !defined $input->{'importer'} ) { $input->{'importer'} = -1; }

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

    if ( $input->{'importer'} != -1 )
    {
        if ( $szWhere ) { $szWhere .= " && idimport = $input->{'importer'}"; }
        else { $szWhere = " where idimport = $input->{'importer'}"; }
    }
   
    # User status 
    if ( !$input->{'status'} ) { $input->{'status'} = "active"; }
    if ( $input->{'status'} eq "active" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "status=" . USER_ACTIVE;
    }
    elsif ( $input->{'status'} eq "activeblock" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "(status=" . USER_ACTIVE . " || status=" . USER_BLOCK . ")";
    }
    elsif ( $input->{'status'} eq "inactive" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "status=" . USER_INACTIVE;
    }
    
    # User type
    my @brwrtype = user_LoadCategory($dbh);
    my %idxType = {};
    foreach my $rec (@brwrtype)
    {
        $idxType{$rec->{'catid'}} = $rec->{'catname'};
    }

    # Set default type is Student
    if ( !$input->{'brwrtype'} ) 
    { 
        my $typedef = -1;
        foreach my $rec (@brwrtype)
        {
            if ( $rec->{'catname'} eq 'Student' )
            { 
                $typedef = $rec->{'catid'}; 
            }
        }
        $input->{'brwrtype'} = $typedef;
    }

    if ( $input->{'brwrtype'} != -1 )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "categorycode=" . $input->{'brwrtype'};
    }
   
    # Page number
    if ( !$input->{'pNum'} ) { $input->{'pNum'} = 1; }
    $input->{'pNum'} = ceil($input->{'pNum'});
    
    # Do actions
    if ( $input->{'op'} eq 'delete' )
    {
        my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
        
        my ($countNotDel) = DeleteSelected($template, $dbh, $input, $user->{'uid'});
        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;
        }
    }
    
    GetBorrowerList($dbh, $input, $template, \%idxType, $pagesize, $szWhere);
    MakePages ($dbh, $input, $template, $pagesize, $szWhere); 

# Fill up the list of user type
    foreach my $rec (@brwrtype)
    {
        $rec->{'brsel'} = 1 if ( $input->{'brwrtype'} == $rec->{'catid'} );
    }
    $template->param(brwrtypelist => \@brwrtype);
    
    $template->param(filterval => $input->{'filtervalue'});
    $template->param('sel' . $input->{'filtertype'} => 1);
    $template->param('sort' . $input->{'ordervalue'} => 1);
    $template->param($input->{'status'} => 1);

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

# 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 dateImport, i.idimport as idimport, firstname, lastname, dateImport from opl_user as u, opl_userimport as i where u.uid=i.uid order by lastname, firstname, dateImport");
    $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(xxx => $input->{'uidlist'});

#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('del') );

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

#-------------------------------------------------------
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 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 DeleteSelected
{
    my ($template, $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 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 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 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);
#Tue, Oct 26, 2010 @ 11:30:49 EDT
#cancel user hold reserve    
       cancelUerHoldReserve($dbh,$uid);
#/Tue, Oct 26, 2010 @ 11:31:08 EDT
        


=ha comment out   2008/09/22        
        if ( $recLoan || ( $bal != 0 ) || ( $status == 1 ) || ( $idCurrent == $uid ) ) 
            { $count++; $newlist .= $uid . ','; next; }

         # delete all reports of lost or damage or overdue by this user
        $queryIdLoan->execute($uid) || return;
        while ( my ($rec) = $queryIdLoan->fetchrow_array )
        {
            $queryLost->execute($rec) || return;
            my $lostitem = $queryLost->fetchrow_hashref;
            $queryLost->finish;
            $queryLostStatus->execute($lostitem->{'ondate'}, $lostitem->{'barcode'});
            $queryLostStatus->finish;
            
            $queryDamage->execute($rec) || return;
            my $damageditem = $queryDamage->fetchrow_hashref;
            $queryDamage->finish;
            $queryDamageStatus->execute($damageditem->{'ondate'}, $damageditem->{'barcode'});
            $queryDamageStatus->finish;
            
            $queryDelLost->execute($rec) || return;
            $queryDelDamage->execute($rec) || return;
            $queryDelOverdue->execute($rec) || return;
        }
        
        # delete all transactions of user
        $queryDelLoan->execute($uid) || return;
        
        # delete all payments from user
        $queryDelPay->execute($uid) || return;

        # delete all holdings from user
        $queryIdRsv->execute($uid) || return;
        while ( my ($rec) = $queryIdRsv->fetchrow_array )
        {
            $queryDelHold->execute($rec) || return;
        }
        
        # delete all reserved items
        $queryDelRsv->execute($uid) || return;
        
        # delete guardians
        $queryDelGuardian->execute($uid) || return;
=cut   
        
        # 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 < $pageoffset )
    {
        $input->{'pNum'} = ceil($total/$pagesize);
        $pageoffset = ($input->{'pNum'} - 1)*$pagesize;
    }
    $template->param(totaluser => $total);
    
# Get the users in the current page
    $sql = "select * from opl_user"; 
    $sql .= $szWhere;
    if ( $input->{'ordervalue'} )
    { $sql .= " order by " . $input->{'ordervalue'}; }
    else
    { $sql .= " order by lastname"; }
 
    $sql .= " limit $pageoffset, $pagesize";
     
    $query = $dbh->prepare($sql);
    $query->execute();
    #$template->param(xxx => $sql);

    my @BrwrList = ();
    my $IsSelected = 0;
    my $row = 0;
    my $uidList = $input->{'uidlist'};
    my $newlist = '';

    while ( my $rec = $query->fetchrow_hashref )
    {
        if ( $row%2 ) { $rec->{'clrcode'} = 1; }
        else  { $rec->{'clrcode'} = 0; }
        
        $rec->{'row'} = $row++;
        
        #--- Set highlight
         
        if ( $uidList =~ m/$rec->{'uid'}/ ) 
        { 
            $rec->{'colormark'} = 1;
            $newlist .= $rec->{'uid'} . ',';
        }

        $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);

        #--- Get overdue/loans info
        my $loanList = circ_userListLoan($dbh, $rec->{'uid'});
        my $odCount = 0;
        foreach my $loan (@$loanList) 
        {
            if ( $loan->{'overdue'} ) { $odCount++; }
        }
        $rec->{'odloans'} = scalar(@$loanList) . "/" . $odCount;

        push @BrwrList, $rec;
    }
    $query->finish;
    
    $template->param(userlist => \@BrwrList);
    
# Set parameter
    #$template->param(uidlist => $newlist);
}

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

#----------------------------------------------------------
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: