#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
);

use Opals::User qw(
    user_getUserList
    user_getUserCircCount
    user_getUserCircCount_textbook
    user_getUserCircCount_equipment
);
use Opals::Session qw(
  SessionHdl_getSSID
);

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

my $cgi      = CGI->new;
my $input    = $cgi->Vars();
my $ssChkSel = $input->{'ssChkSel'} || "";
my $result =[];
my $searchArg={pSize=>10,pNum=>1,sortBy=>'lastname,firstname',sortDir=>'ASC'};
   
   foreach my $f(qw(categorycode idimport status sortBy sortDir pNum pSize)){
         if(defined $input->{$f} && $input->{$f} ne ""){
            $searchArg->{$f} = $input->{$f} ;
         }
    } 


    if(defined $input->{'ssName'} && $input->{'ssName'} ne ''){
        $result = getSelectedUsers($dbh,$input->{'ssName'},$searchArg->{'pNum'},$searchArg->{'pSize'});
    }
    else{
        if(defined $input->{'sField'} && $input->{'sField'} ne "" && defined $input->{'term'} ){
            $searchArg->{'sField'}=$input->{'sField'};
            $searchArg->{'term'}=$input->{'term'};
        }
        $result = user_getUserList($dbh,$searchArg);
    }

    if($result->{'numOfUserFound'}>0) {
        foreach my $u(@{$result->{'userList'}}){
            $u->{'balance'}=getAcctBalance($dbh,$u->{'uid'});
        }
        user_getUserCircCount($dbh,$result->{'userList'});
        user_getUserCircCount_textbook($dbh,$result->{'userList'});
        user_getUserCircCount_equipment($dbh,$result->{'userList'});
    
        if($ssChkSel ne ''){
            verifySelected($dbh,$ssChkSel,$result->{'userList'});
        }
    }
    if($ssChkSel ne ''){
        $result->{'totalSelInSS'} =getTotalInSession($dbh,$ssChkSel);
    }

    my $userJSON = to_json($result, {pretty => 1})  ;


    print "Content-type: text/plain\n\n";
    print   $userJSON;

 
#------------------------------------------------------------------------------
sub verifySelected{
    my($dbh,$ssName,$userList)=@_;
    my $ssId = $cgi->cookie('globalSessionID');
    if(defined $ssId && $ssId ne"" ){    
        my @uidList=();
        foreach my $u(@$userList){
            push @uidList,$u->{'uid'};
        }
        my $idList = join(",", @uidList);
        my $sth =$dbh->prepare("select rid from opl_sessionVar where var='$ssName' && ssid='$ssId' && rid in($idList)");
        my $inSession={};
        $sth->execute();
        while(my ($uid)=$sth->fetchrow_array){
            $inSession->{$uid}=1;
        }
        foreach my $u(@$userList){
            if($inSession->{$u->{'uid'}}){
                $u->{'selected'}='true';
            }
        }
    }

}
#------------------------------------------------------------------------------
sub getTotalInSession{
    my($dbh,$ssName)=@_;
    my $ssId = $cgi->cookie('globalSessionID');
    my $sqlCount="select count(*) from opl_sessionVar where ssid=? && var=?";
    my ($c)=$dbh->selectrow_array($sqlCount, undef,$ssId,$ssName);
    return $c;
}
#------------------------------------------------------------------------------
sub getAcctBalance{
    my($dbh,$uid)=@_;
    my ($bal)=$dbh->selectrow_array("select sum(balance) from opl_charge where uid=?",undef,$uid);
    return $bal|| "0.00";
}

#------------------------------------------------------------------------------
sub getSelectedUsers{
    my($dbh,$ssVar,$pNum,$pSize)=@_;
    my $offset=($pNum-1)*$pSize;
    my $ssid   = SessionHdl_getSSID($cgi);
    my @rs=();
    my ($count) =$dbh->selectrow_array("select count(*) from opl_sessionVar where ssid=? && var=?",undef,$ssid,$ssVar);
    my $userStatusGrp={inactive=>0,active=>0,block=>0};
    my  $query_statusGrp =" select  status,count(status) count 
              from opl_user u inner join opl_sessionVar s on s.rid=u.uid && s.var=? && ssid=? 
               left outer join opl_category c on u.categorycode=c.catid group by status";
        my $userStatusGrp={inactive=>0,active=>0,block=>0};
    if($count>0){
        my $sth =$dbh->prepare(<<_STH_);
        select  u.* ,c.catname  
        from   opl_user u inner join opl_sessionVar s on s.rid=u.uid && s.var=? && ssid=? 
               left outer join opl_category c on u.categorycode=c.catid order by s.sOrder
               limit $offset,$pSize 
_STH_

        $sth->execute($ssVar,$ssid);
        my $statusMap= {'0'=>'inactive','1'=>'active','2'=>'block'};
        my ($i,$a,$b) =(0,0,0);
        while(my $u=$sth->fetchrow_hashref){
            $u->{'statusName'}=$statusMap->{$u->{'status'}};
            #$u->{'selected'}='true';
            push @rs,$u;
        }
        my $sth_statusGrp =$dbh->prepare($query_statusGrp);
            $sth_statusGrp->execute($ssVar,$ssid);
            my $total=0;
            while(my ($s,$c)=$sth_statusGrp->fetchrow_array){
                $total +=$c;
                $userStatusGrp->{$statusMap->{$s}}=$c;

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


    }
    return {numOfUserFound=>$count,userList=>\@rs,userStatusGrp=>$userStatusGrp};
   
}
#------------------------------------------------------------------------------