#!/usr/bin/perl

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

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);
use Opals::Circulation qw(
    circ_GetOnloanNumber
    circ_GetOverdueNumber
    circ_GetReserveNumber
    circ_getRecCircStatus
);
use Opals::Transaction qw(
    trans_getBalance
);
#use Opals::Date qw(
#    date_today
#    date_parse
#);

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }
use Time::localtime;
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
 

my $input   = getInput();

my $cgi = CGI->new;
my $syspref     = tmpl_preference($dbh);
my $CallNumSort  = $syspref->{'classificationSystem'} eq 'LCC'?'callNumSort_lcc':'callNumSort_dewey';

my $pNum    = $input->{'pNum'}||1;
my $pSize   = $input->{'pSize'}||10;
#my $sort    = $sortFieldMap->{"name"}||"id";
my $sortDir = $input->{'sortDir'}||"asc";
my $status  = $input->{'status'}; # waiting|reserve|hold|complete|rejected|cancelled|expired
my $offset=($pNum-1)*$pSize;

my $sField   = $input->{'sField'}||"";
my $sTerm   = $input->{'sTerm'}||"";
my $sFrom    = $input->{'sFrom'}||"";
my $sTo      = $input->{'sTo'}||"";
my $fnMap={
            "waiting"   =>\&getRequest_waiting,
            "reserve"   =>\&getRequest_onreserve,
            "hold"      =>\&getRequest_hold,
            "complete"  =>\&getRequest_complete,
            "rejected"  =>\&getRequest_rejected,
            "cancelled" =>\&getRequest_cancel,
            "expired"   =>\&getRequest_expired,
            "tracking"  =>\&getRequest_tracking,
};

my $rs=[];
my $reqCount=0;

#-------------------------------------------------------------
my $sortFieldMap={
        name=>               "lastname $sortDir,firstname,titleSort,author,dateRequest,dateExpiry",
        title=>              "titleSort $sortDir,author,lastname,firstname,dateRequest,dateExpiry",
        author=>             "author $sortDir,titleSort,lastname,firstname,dateRequest,dateExpiry",
        barcode=>            "barcode $sortDir,titleSort,author,lastname,firstname,dateRequest,dateExpiry",
        status=>             "reqStatus $sortDir,titleSort,author,lastname,firstname,dateRequest,dateExpiry",
        dateRequest=>        "dateRequest $sortDir,lastname,firstname,author,titleSort,dateExpiry",
        dateExpiry=>         "dateExpiry $sortDir,lastname,firstname,author,titleSort,dateRequest",
        dateReserveExpiry=>  "dateReserveExpiry $sortDir,lastname,firstname,author,titleSort,dateRequest",
        dateHoldExpiry=>     "dateHoldExpiry $sortDir,lastname,firstname,author,titleSort,dateRequest",
        dateLoan=>           "dateLoan $sortDir,lastname,firstname,author,titleSort,dateRequest",
        numCopyReserve=>     "numCopyReserve $sortDir,titleSort,author,dateRequest,dateExpiry",
        numCopyRequest=>     "numCopyRequest $sortDir,titleSort,author,dateRequest,dateExpiry",
        callNum=>            "$CallNumSort $sortDir,location,titleSort,lastname,firstname,dateRequest,dateExpiry"
};
#-------------------------------------------------------------
    my $subFields="q.*,
                  u.firstname,u.lastname,u.nickname,u.notes, u.privateNotes,u.noticeByEmail,
                  m.title,titleSort,m.author,
                  i.callNumber,i.callNumSort_dewey,callNumSort_lcc,i.location";
    my $fieldMap={
            "waiting"   =>$subFields,
            "reserve"   =>"r.idReserve,r.rid,r.numCopyReserve ,r.dateReserve datePlaceReserve,
                           r.dateExpiry dateReserveExpiry,r.suspendFrom,r.suspendUntil",
            "hold"      =>"h.dateHold,h.idloan,h.dateExpiry dateHoldExpiry",
            "complete"  =>"h.dateHold,h.idloan,h.dateExpiry dateHoldExpiry,h.dateLoan,h.barcode",
            "rejected"  =>$subFields,
            "cancelled" =>$subFields,
            "expired"   =>$subFields,
            "tracking"  =>$subFields
     };
#-------------------------------------------------------------

my $sort    = $sortFieldMap->{$input->{'sortBy'}}||"id";

#-------------------------------------------------------------
if(defined $fnMap->{$status}){
    my $fn=$fnMap->{$status};
    ($reqCount,$rs) =&$fn($dbh,$sort,$sortDir,$offset,$pSize,$sField,$sTerm,$sFrom,$sTo);
    #_fillCircInfo($dbh,$rs);
    foreach my $r (@$rs){
        _fillCircInfo($dbh,$r);
        _fillLocation($dbh,$r);
        _checkEmailSent($dbh,$r);
    }
}


foreach my $r(@$rs){
    $r->{'phone'}=$r->{'phone'}||"";
    $r->{'email'}=$r->{'email'}||"";
    $r->{'notes'}=$r->{'notes'}||"";
    $r->{'privateNotes'}=$r->{'privateNotes'}||"";
}


print "Content-type: text/plain\n\n";
print to_json({count=>$reqCount, rs=>$rs},{pretty=>1});
#-------------------------------------------------------------
#
#
sub _getUserCircCount{
    my($dbh,$uid)=@_;
    my $totalLoan      = circ_GetOnloanNumber($dbh, $uid)||0;
    my $totalOverdue   = circ_GetOverdueNumber($dbh, $uid)||0;
    my $totalReserve   = circ_GetReserveNumber($dbh, $uid)||0;
    my $balance = trans_getBalance($dbh,$uid );
       $balance = sprintf("%.2f",$balance);
    return ($totalLoan,$totalOverdue,$totalReserve,$balance);
}
#-------------------------------------------------------------
sub _fillLocation{
    my($dbh,$r)=@_;
    my $sth_location=$dbh->prepare("select location,count(rid) nLoc  from opl_item 
                   where  barcode not regexp '___' && rid=? group by rid,location");
    $sth_location->execute($r->{'rid'});
    my $locations=undef;
    while(my $l =$sth_location->fetchrow_hashref){
        push @{$locations},$l; 
    }
    $r->{'locations'}=$locations;
}

#-------------------------------------------------------------
sub _fillCircInfo{
    my($dbh,$r)=@_;
    $r->{'circStatus'}= circ_getRecCircStatus($dbh,$r->{'rid'});
    ($r->{'totalLoan'},$r->{'totalOverdue'},$r->{'totalReserve'},$r->{'balance'})=_getUserCircCount($dbh,$r->{"uid"});
}

#-------------------------------------------------------------
sub _checkEmailSent{
    my($dbh,$r)=@_;
    my $emailTypeMap= {'waiting'=>'rejectEmail','rejected'=>'rejectEmail' ,
                       'reserve'=>'cancelReserveEmail', 'cancelled'=>'cancelReserveEmail',
                       'hold' =>'holdReadyEmail'};

    my $sql_countSent="select count(*) countSentMail ,reqId, max(dateLog) dateSentMail from opl_reqReserveHistory 
                       where action =? && reqId=?  group by(reqId) ";
    my $sentEmail = $dbh->selectrow_hashref($sql_countSent, undef,$emailTypeMap->{$status} , $r->{'id'});
    $r->{'countSentMail'}=$sentEmail->{'countSentMail'};
    $r->{'dateSentMail'} =$sentEmail->{'dateSentMail'};

}

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

sub getRequest_waiting{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select  $fieldMap->{'waiting'} 
                 from     opl_requestReserve q inner join opl_marcRecord m using(rid) 
                          left outer join opl_item i using(rid) 
                          inner join opl_user u using(uid))t";
   
    my $sql_cond="  t.status='waiting' && dateExpiry>now()";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }

    my $sql  ="select t.* from $sql_sub where $sql_cond 
               group by t.id order by $sort limit $offset,$size";
   
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    while(my $r=$sth->fetchrow_hashref){
        $r->{'phone'}=$r->{'phone'}||"";
        $r->{'email'}=$r->{'email'}||"";
        $r->{'notes'}=$r->{'notes'}||"";
        $r->{'privateNotes'}=$r->{'privateNotes'}||"";

        push @reqList, $r;
    }
    
    my $sql_count ="select count(distinct t.id) from $sql_sub where $sql_cond";
    #my $sql_count ="select count(distinct t.id) from $sql_sub where $sql_cond group by id";
    #open debug,">/tmp/ss1"; print debug  "$sql\n";close debug;
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}
#-------------------------------------------------------------------------------------------------
sub getRequest_cancel{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select  $fieldMap->{'cancelled'} 
                 from     opl_requestReserve q inner join opl_marcRecord m using(rid) 
                          left outer join opl_item i using(rid) 
                          inner join opl_user u using(uid) group by q.rid ,q.idReserve)t";
    my $sql_cond="  t.status='cancel' ";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }

    my $sql ="select t.* from $sql_sub where $sql_cond
               order by $sort $sortDir limit $offset,$size";
    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;          
  
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    my $sth_sentHistory=$dbh->prepare("select * from opl_reqReserveHistory where action ='cancelReserveEmail' && reqId=? order by id");                       
    while(my $r=$sth->fetchrow_hashref){
        $sth_sentHistory->execute($r->{'id'});
        my @sentEmail=();
        while(my $s = $sth_sentHistory->fetchrow_hashref){
            my $t=decode_json($s->{'log'});
            push @sentEmail,$t;
        }
        $r->{'sentLog'} =\@sentEmail; 
        $r->{'countSentMail'}=scalar(@sentEmail);
        push @reqList, $r;
    }
  
  
    my $sql_count ="select count(distinct t.id) from $sql_sub where $sql_cond";
    #open debug,">/tmp/ss1"; print debug "$sql_count\n\n";close debug;
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}

#-------------------------------------------------------------------------------------------------
sub getRequest_rejected{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select  $fieldMap->{'rejected'} 
                 from     opl_requestReserve q inner join opl_marcRecord m using(rid) 
                          left outer join opl_item i using(rid) 
                          inner join opl_user u using(uid) group by q.rid ,q.idReserve)t";
    my $sql_cond="  t.status='reject' ";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }

    my $sql ="select t.* from $sql_sub where $sql_cond
              group by t.id order by $sort $sortDir limit $offset,$size";
    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;          
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my $sth_sentHistory=$dbh->prepare("select * from opl_reqReserveHistory where action ='rejectEmail' && reqId=? order by id");                       
    my @reqList; 
    while(my $r=$sth->fetchrow_hashref){
        $sth_sentHistory->execute($r->{'id'});
        my @sentEmail=();
        while(my $s = $sth_sentHistory->fetchrow_hashref){
            my $t=decode_json($s->{'log'});
            push @sentEmail,$t;
        }
        $r->{'sentLog'} =\@sentEmail; 
        $r->{'countSentMail'}=scalar(@sentEmail);
        push @reqList, $r;
    }
    
    my $sql_count ="select count(distinct t.id) from $sql_sub where $sql_cond";
    #open debug,">/tmp/ss1"; print debug "$sql_count\n\n";close debug;
 
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}

#-------------------------------------------------------------------------------------------------
sub getRequest_expired{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select  $fieldMap->{'expired'} 
                 from     opl_requestReserve q inner join opl_marcRecord m using(rid) 
                          left outer join opl_item i using(rid) 
                          inner join opl_user u using(uid) group by q.rid ,q.idReserve)t";
    my $sql_cond="  t.dateExpiry<now() && t.idReserve is null ";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }

    my $sql ="select t.* from $sql_sub where $sql_cond
               order by $sort $sortDir limit $offset,$size";
    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;     
  
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    while(my $r=$sth->fetchrow_hashref){
        $r->{'dateRequest'}=substr($r->{'dateRequest'},0,10);
        $r->{'dateExpiry'} =substr($r->{'dateExpiry'},0,10);
        push @reqList, $r;

    }
    
    my $sql_count ="select count(distinct t.id) from $sql_sub where $sql_cond";
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);

    return ($numOfRequestFound,\@reqList);
}

#-------------------------------------------------------------------------------------------------
sub getRequest_hold{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
     my $sql_sub="(select $subFields
                 from   opl_requestReserve q 
                        inner join opl_user u using(uid) 
                        inner join opl_marcRecord m using(rid) 
                        inner join opl_item i using(rid) group by q.rid ,q.idReserve)t  ";
    my $sql_cond="  h.dateLoan is null  && h.dateExpiry>now() &&  h.dateCancel is null ";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }
    
    my $sql="select  $fieldMap->{'hold'},t.* 
             from    opl_hold h inner join $sql_sub  using(idReserve)
             where   $sql_cond order by $sort  limit $offset,$size";
  
    my $sth =$dbh->prepare($sql);
    $sth->execute();

    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;
    my @reqList; 
    my $sth_sentHistory=$dbh->prepare("select * from opl_reqReserveHistory where action ='holdReadyEmail' && reqId=? order by id");                       
    while(my $r=$sth->fetchrow_hashref){
        $sth_sentHistory->execute($r->{'id'});
        my @sentEmail=();
        while(my $s = $sth_sentHistory->fetchrow_hashref){
            my $t=decode_json($s->{'log'});
            push @sentEmail,$t;
        }
        $r->{'sentLog'} =\@sentEmail; 
        $r->{'countSentMail'}=scalar(@sentEmail);
        push @reqList, $r;

    }
    my $sql_count =" select count(distinct h.idReserve) from  opl_hold h  inner join $sql_sub using(idReserve) where $sql_cond  ";
   # open debug,">/tmp/ss1"; print debug "$sql_count\n\n";close debug;
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}
#-------------------------------------------------------------------------------------------------
sub getRequest_complete{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $sql_sub="(select  $subFields
                 from   opl_requestReserve q 
                        inner join opl_user u using(uid) 
                        inner join opl_marcRecord m using(rid) 
                        inner join opl_item i using(rid) group by q.rid ,q.idReserve)t  ";
    my $sql_cond=" h.idloan>0 && h.barcode not regexp '^\_\_\_' ";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }
    
    my $sql ="select $fieldMap->{'complete'},t.*
              from   opl_hold h inner join $sql_sub  using(idReserve) 
              where  $sql_cond   
                     order by $sort  limit $offset,$size";
    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    while(my $r=$sth->fetchrow_hashref){
        push @reqList, $r;

    }
    
    my $sql_count ="select count(distinct h.idReserve) from  opl_hold h  inner join $sql_sub using(idReserve) where $sql_cond";
    #open debug,">/tmp/ss1"; print debug "$sql_count\n\n";close debug;
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}

#-------------------------------------------------------------------------------------------------
sub getRequest_onreserve{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =20 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select distinct $subFields
                 from   opl_requestReserve q 
                        inner join opl_user u using(uid) 
                        inner join opl_marcRecord m using(rid) 
                        inner join opl_item i using(rid))t  ";
    my $sql_cond="  (r.dateCancel is  null && r.numCopyReserve>0 ) && r.dateExpiry >now()";
    if($cond ne ""){
        $sql_cond .= " AND ( $cond )";
    }

    my $sql  ="select  $fieldMap->{'reserve'},t.*, if(now() between suspendFrom and suspendUntil,'YES','NO') suspended 
               from    opl_reserve r  inner join $sql_sub using(idReserve) 
               where   $sql_cond  group by r.idReserve  
                       order by $sort  limit $offset,$size";

    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    my $sth_sentHistory=$dbh->prepare("select * from opl_reqReserveHistory where action ='holdReadyEmail' && reqId=? order by id");    
    while(my $r=$sth->fetchrow_hashref){
        $r->{'name'} = $r->{'firstname'}  . ' ' . $r->{'lastname'} ;
        $r->{'suspendStat'}=1;
       # my $today = date_today();
       # if(defined $r->{'suspendUntil'} && date_parse($r->{'suspendUntil'}) < $today){
       #    $r->{'suspendStat'} = 0;
       # }
        $sth_sentHistory->execute($r->{'id'});
        my @sentEmail=();
        while(my $s = $sth_sentHistory->fetchrow_hashref){
            my $t=decode_json($s->{'log'});
            push @sentEmail,$t;
        }
        $r->{'sentLog'} =\@sentEmail; 
        $r->{'countSentMail'}=scalar(@sentEmail);
        push @reqList, $r;
    }
    my $rqPriorityTbl={};
    my $sth_pri=$dbh->prepare("select idReserve,priority from opl_reserve where rid=? 
                              && numCopyReserve>0 && dateCancel is null && dateExpiry>now() 
                              order by priority,idReserve");
    foreach my $r(@reqList){
        $r->{'suspendFrom'}=substr($r->{'suspendFrom'},0,10);
        $r->{'suspendUntil'}=substr($r->{'suspendUntil'},0,10);
        if(!defined $rqPriorityTbl->{$r->{'rid'}}){
            $sth_pri->execute($r->{'rid'}); 
            my $priority=1;
            while(my ($id,$p) =$sth_pri->fetchrow_array){
                $rqPriorityTbl->{$r->{'rid'}}->{$id}=$priority++;
            }
            $rqPriorityTbl->{$r->{'rid'}}->{'maxPriority'}=$priority-1;
            $r->{'dateRequest'}=substr($r->{'dateRequest'},0,10);
            $r->{'dateExpiry'} =substr($r->{'dateExpiry'},0,10);
        }
        $r->{'priority'}=$rqPriorityTbl->{$r->{'rid'}}->{$r->{'idReserve'}};
        $r->{'maxPriority'}=$rqPriorityTbl->{$r->{'rid'}}->{'maxPriority'};
        $r->{'logs'} =_getLogByRid($dbh,$r->{'id'});
    }

    my $sql_count=" select count(distinct r.idReserve) from  opl_reserve r  inner join $sql_sub using(idReserve) where $sql_cond  ";   
    #open debug,">/tmp/ss1"; print debug "$sql\n\n";close debug;
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);
}

#-------------------------------------------------------------------------------------------------
sub getRequest_tracking{
    my($dbh,$sort,$sortDir,$offset,$size,$sField,$sTerm,$from,$to)=@_;
    $offset=0 if(!defined $offset);
    $size  =10 if(!defined $size);
    my $cond=_getRequestSQLFilterCond($sField,$sTerm,$from,$to);
    my $sql_sub="(select  $fieldMap->{'expired'} 
                 from     opl_requestReserve q inner join opl_marcRecord m using(rid) 
                          left outer join opl_item i using(rid) 
                          inner join opl_user u using(uid) group by q.rid ,q.idReserve)t";
    my $sql_cond="";
    if($cond ne ""){
        $sql_cond .= " where ( $cond )";
    }
    my $sql ="select t.* from $sql_sub  $sql_cond
               order by $sort $sortDir limit $offset,$size";
    #open debug,">/tmp/ss"; print debug "$sql\n\n";close debug;

    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList;   
    my $sth_tracking=$dbh->prepare("select distinct * from opl_reqReserveHistory where  reqId=? order by id");
   
    my $statusMap= {rejectEmail       => 'Request Reject notice',      
                    holdReadyEmail    => 'Request Ready Notice',
                    cancelReserveEmail=> 'Request Cancellation Notice', 
                    reactivateEmail   =>'Request Re-activate ',
                    loan              =>'Loan',
                    waiting           =>'waiting',
                    cancel            =>'Cancel', 
                    reject            =>'Reject', 
                    reserve           =>'Reserve', 
                    hold              =>'Hold',
                    ready             =>'Ready for pickup',
                    reactivate        =>'Re-activate',
                    're-active'       =>'Re-activate'
                    };
    
     
    while(my $r=$sth->fetchrow_hashref){
        $sth_tracking->execute($r->{'id'});
        my @tracking=();
        while(my $s = $sth_tracking->fetchrow_hashref){
            #if($sField eq 'status' && $sTerm =~ m/hold|reserve/gi){
            my $cnt;
            if( ($sField eq 'status' && $sTerm eq 'hold') || $sField ne 'status'){
                $cnt= _countItemRequestByStatus($dbh,$r->{id},$r->{'idReserve'});
            }
           
          #$s->{'action'} = $statusMap->{$s->{'action'}} . "($cnt->{$s->{'action'}})";

          my $tmpStr = $statusMap->{$s->{'action'}};
            $tmpStr .=  "($cnt->{$s->{'action'}})" if(defined $cnt->{$s->{'action'}});
            $s->{'action'}= $tmpStr ;
            $s->{'log'} =from_json($s->{'log'}); 
            push @tracking,$s;
        }
        $r->{'tracking'} =\@tracking;
        push @reqList, $r;        
    } 
    
    my $sql_count ="select count(distinct t.id) from $sql_sub  $sql_cond";
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);
    return ($numOfRequestFound,\@reqList);            
}
#-------------------------------------------------------------------------------------------------
sub _getLogByRid{
    my ($dbh,$reqId)=@_;
    my $statusMap= {rejectEmail       => 'Request Reject notice',      
                    holdReadyEmail    => 'Request Ready Notice',
                    cancelReserveEmail=> 'Request Cancellation Notice', 
                    reactivateEmail   =>'Request Re-activate ',
                    loan              =>'Loan',
                    waiting           =>'waiting',
                    cancel            =>'Cancel', 
                    reject            =>'Reject', 
                    reserve           =>'Reserve', 
                    hold              =>'Hold',
                    ready             =>'Ready for pickup',
                    reactivate        =>'Re-activate',
                    're-active'       =>'Re-activate'
                    };    
    my $sth=$dbh->prepare("select distinct * from opl_reqReserveHistory where  reqId=? order by id");
    $sth->execute($reqId);
    my @logs=();
    while(my $l = $sth->fetchrow_hashref){
        my $tmpStr = $statusMap->{$l->{'action'}};
        $l->{'action'}= $tmpStr ;

        $l->{'log'} =from_json($l->{'log'}); 
        push @logs,$l;
    }
    return \@logs;
}
#-------------------------------------------------------------------------------------------------
sub _countItemRequestByStatus{
   my ($dbh,$reqId,$idReserve)=@_;
   my $ret;
   $ret->{'hold'} =  1;
   $ret->{'reserve'} =  $dbh->selectrow_array("select numCopyReserve from opl_reserve where idReserve=?" ,undef, $idReserve);
   return $ret;
}

#-------------------------------------------------------------------------------------------------
sub _getRequestSQLFilterCond{
    my ($field,$term,$from,$to)=@_;
    my $cond="";
    if(defined $field){
        if($field  =~ m/dateHoldExpiry|dateReserveExpiry|dateRequest|dateExpiry|dateProcess/gi){
            if(!defined $from || $from !~ /\d{4}-\d{2}-\d{2}/){
                $from ="1970-01-01";
            }
            if(!defined $to || $to !~ /\d{4}-\d{2}-\d{2}/){
                $to =$todayStr;
            }
            $to .=" 23:59:59";
            $cond .= " h.dateExpiry  between '$from' and '$to'" if($field eq 'dateHoldExpiry');
            $cond .= " r.dateExpiry  between '$from' and '$to'" if($field eq 'dateReserveExpiry');
            $cond .= " t.dateRequest between '$from' and '$to'" if($field eq 'dateRequest');
            $cond .= " t.dateExpiry  between '$from' and '$to'" if($field eq 'dateExpiry');
            $cond .= " t.dateProcess between '$from' and '$to'" if($field eq 'dateProcess');

        }
        elsif($field  =~ m/name/gi && defined $term  && $term ne ""){
            $term=~ s/ +/ /g;
            $term=~ s/,/ /g; 
            $term=~ s/'/\\'/g;
            my @data = split(' ', $term);
            my @cArr=();
            foreach my $i (@data) {
                if($i =~ m/\w+/g ){
                     my @a=();
                    foreach my $f qw(firstname lastname nickname) {
                        push(@a,"$f regexp '^$i|[^[:alnum:]]+$i'");
                    }
                    push @cArr, "(" . join(" or ",@a) .")";
                }
                $cond =join(" && ",@cArr);
            }           
        }
        elsif($field  !~ m/name/gi && defined $term  && $term ne ""){
            $cond .= " t.$field regexp '^$term|[^[:alnum:]]+$term'";
        }

    }
    return $cond;
}


#-------------------------------------------------------------
sub _fillCircInfo_bk{
    my($dbh,$reqList)=@_;
    foreach my $r (@$reqList){
        $r->{'circStatus'}= circ_getRecCircStatus($dbh,$r->{'rid'});
        ($r->{'totalLoan'},$r->{'totalOverdue'},$r->{'totalReserve'},$r->{'balance'})=_getUserCircCount($dbh,$r->{"uid"});
    }
}

#-------------------------------------------------------------
sub getInput{
    my $input ={};
    if ($ENV{'REQUEST_METHOD'} eq "POST") {
        my $json ="";
        while (<STDIN>) {
            $json .= $_;
        }
        $input = decode_json($json);
    }
    return $input;
}

__END_OF_FILE: