#!/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
);

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


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',
        }
);

#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 = MakeFilter($input->{'filtertype'}, $input->{'filtervalue'}, $input->{'status'}, $input->{'brwrtype'});
    my $szOldWhere = MakeFilter($input->{'oldfiltertype'}, $input->{'oldfiltervalue'}, 
                                    $input->{'oldstatus'}, $input->{'oldbrwrtype'});

    my @brwrtype = user_LoadCategory($dbh);
    my %idxType = {};
    foreach my $rec (@brwrtype)
    {
        $idxType{$rec->{'catid'}} = $rec->{'catname'};
    }            

    # Page number
    if ( !$input->{'pNum'} ) { $input->{'pNum'} = 1; }
    $input->{'pNum'} = ceil($input->{'pNum'});
    
    # Do actions
    if ( $input->{'op'} eq 'delete' )
    {
        my ($countNotDel) = DeleteSelected($template, $dbh, $input);
        SetCurPage($dbh, $input, $countNotDel, $pagesize, $szWhere);
        if ( $countNotDel )
        {
            $template->param(countNotDel => $countNotDel);
        }
    }
    elsif ( $input->{'op'} eq 'update' )
    {
        my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
        UpdateSelected($dbh, $input, $template, $szOldWhere, $user->{'uid'});
    }
    
#    elsif ( $input->{'op'} eq 'MoveTo' )
#    {
#        if ( $input->{'whole'} ) 
#        {
#            my $sz = "update opl_user set categorycode=" . $input->{'movebrwrtype'} . $szOldWhere;
#            $dbh->do($sz);
#        }
#        else { MoveSelected($dbh, $input, $template); }
#    }
#    elsif ( $input->{'op'} eq 'ChangeStatus' )
#    {
#        if ( $input->{'whole'} ) { ChangeStatusWhole($dbh, $input, $szOldWhere, $template); }
#        else { ChangeStatus($dbh, $input, $template); }
#    }
    
    GetBorrowerList($dbh, $input, $template, \%idxType, $pagesize, $szWhere);
    MakePages ($dbh, $input, $template, $pagesize, $szWhere); 

    $template->param(movebrwrtype => \@brwrtype);
    @brwrtype = ({catid => -1, catname => 'Any'} , @brwrtype);
    foreach my $rec (@brwrtype)
    {
        $rec->{'brsel'} = 1 if ( $input->{'brwrtype'} == $rec->{'catid'} );
    }
    $template->param(brwrtype => \@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(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'});

    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 
    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 "gradeyear" )
    {
        $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 || $idcurrent == $uid );
                if ( $bCanNotDelete ) { $CountNotChange++; $listNotUpdated .= $uid . ','; next; }
            }
        }
        $queryUpdt->execute($uid);
        $queryUpdt->finish;
    }
    $input->{'uidlist'} = $listNotUpdated;
    $template->param(CountNotChange => $CountNotChange);
}

#-------------------------------------------------------
sub DeleteSelected
{
    my ($template, $dbh, $input) = @_;
    
# Query delete user    
    my $szDelSql = "delete from opl_user where uid = ?";
    my $queryDel = $dbh->prepare($szDelSql);

# Query delete user    
    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 cancel all holdings of a user
    my $szUpdtHoldSql = "update opl_hold set dateCancel=now() where uid=? and dateCancel is null";
    my $queryUpdtHold = $dbh->prepare($szUpdtHoldSql);
               
# Query check if user own a loan record
    my $szLoanSql = "select u.uid, idloan from opl_user as u, opl_loan as l
                where u.uid = l.uid && u.uid = ?";
    my $queryLoan = $dbh->prepare($szLoanSql);
    
    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;
        if ( $recLoan ) { $count++; $newlist .= $uid . ','; next; }
        $queryLoan->finish;
        
        # if this user is holding some items, cancel them
        $queryUpdtHold->execute($uid);
        $queryUpdtHold->finish;

        # delete all reserved items
        $queryDelRsv->execute($uid);
        $queryDelRsv->finish;
        
        # delete guardians
        $queryDelGuardian->execute($uid);
        $queryDelGuardian->finish;
        
        # delete this user
        $queryDel->execute($uid);
        $queryDel->finish;
    }
    $input->{'uidlist'} = $newlist;
    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'} )
    { $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'};
#    if ( $input->{'uidlist'} !~ m/^,/ )
#    {
#        $uidList = ',' . $uidList;
#    }
    $uidList =~ s/^,*/,/;

    while ( my $rec = $query->fetchrow_hashref )
    {
        $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);

        #--- 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);
    $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 = $szSQL . " and status=?";
    $query = $dbh->prepare($szUserSQL);
    
    $query->execute(USER_ACTIVE);
    ($count) = $query->fetchrow_array;
    $query->finish;
    $template->param(activeuser => $count);

    $query->execute(USER_INACTIVE);
    ($count) = $query->fetchrow_array;
    $query->finish;
    $template->param(inactiveuser => $count);

    $query->execute(USER_BLOCK);
    ($count) = $query->fetchrow_array;
    $query->finish;
    $template->param(blockeduser => $count);

    my @uidlist = split /,/, $input->{'uidlist'};
    $template->param(selecteduser => scalar(@uidlist));
}

#----------------------------------------------------------
sub MakeFilter
{
    my ($filtertype, $filtervalue, $status, $brwrtype) = @_;
    my $szWhere = '';

    if ( $filtervalue )
    {
        my $szCond = $filtervalue;
        $szCond =~ s/^\s+//;
        $szCond =~ s/\s+$//;
        if ( $filtertype eq 'name' ) 
            { $szWhere = " where firstname like '$szCond%' || lastname like '$szCond%'"; }
        elsif ( $filtertype eq 'city' ) { $szWhere = " where city like '$szCond%'"; }
        elsif ( $filtertype eq 'zippostal' ) { $szWhere = " where zip = '$szCond'"; }
        elsif ( $filtertype eq 'phone' ) { $szWhere = " where phone = '$szCond'"; }
        elsif ( $filtertype eq 'grade' ) { $szWhere = " where grade = $szCond"; }
        elsif ( $filtertype eq 'teacher' ) { $szWhere = " where teacher like '$szCond%'"; }
        elsif ( $filtertype eq 'homeroom' ) { $szWhere = " where homeroom = '$szCond'"; }
        elsif ( $filtertype eq 'buildingcode' ) { $szWhere = " where buildingcode = '$szCond'"; }
        elsif ( $filtertype eq 'gradeyear' ) { $szWhere = " where yeargraduation = $szCond"; }
    }

    # User status 
    if ( !$status ) { $status = "active"; }
    if ( $status eq "active" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "status=" . USER_ACTIVE;
    }
    elsif ( $status eq "activeblock" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "(status=" . USER_ACTIVE . " || status=" . USER_BLOCK . ")";
    }
    elsif ( $status eq "inactive" )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "status=" . USER_INACTIVE;
    }

    # User type
    if ( !$brwrtype ) { $brwrtype = -1; }
    if ( $brwrtype != -1 )
    {
        if ( $szWhere eq '' ) { $szWhere = " where "; } else { $szWhere .= " && "; }
        $szWhere .= "categorycode=" . $brwrtype;
    }
    return $szWhere;
}

__END_OF_FILE: