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


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",
        callNum=>            "$CallNumSort $sortDir,location,titleSort,lastname,firstname,dateRequest,dateExpiry"
};
#-------------------------------------------------------------
    my $subFields="q.id,q.idReserve,q.dateRequest,q.dateProcess,q.dateExpiry,q.numCopyReserve numCopyRequest,q.email,q.phone,q.noticeType,
                  u.uid,u.firstname,u.lastname,u.nickname,u.notes, u.privateNotes,
                  m.title,titleSort,m.author,
                  i.callNumber,i.callNumSort_dewey,i.location";
    my $fieldMap={
            "waiting"   =>$subFields,
            "reserve"   =>"r.idReserve,r.rid,r.numCopyReserve numCopyReserve,r.dateReserve datePlaceReserve,
                           r.dateExpiry dateReserveExpiry",
            "hold"      =>"h.dateHold,h.idloan,h.dateExpiry dateHoldExpiry, h.dateExpiry",
            "complete"  =>"h.dateHold,h.idloan,h.barcode",
            "rejected"  =>"",
            "cancelled" =>"",
            "expired"   =>"",
            "tracking"  =>""
                 };

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 $sql1 ="select r.id,r.numCopyReserve,r.rid,r.email,r.phone,r.dateRequest,r.dateExpiry,r.dateProcess,r.status,r.noticeType,
                     m.title,titleSort,m.author ,i.callNumber,i.callNumSort_dewey,
                     u.uid,u.firstname,u.lastname ,u.notes, u.privateNotes
              from   opl_requestReserve r inner join opl_marcRecord m using(rid) 
                     left outer join opl_item i using(rid) 
                     inner join opl_user u using(uid)
              where  r.status='waiting' && dateExpiry>now()";

    my $sql_sub =" 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
                where  t.status='waiting' && dateExpiry>now()";
              
    my $sql ="select t.* from (select  $fieldMap->{'waiting'},q.status $sql_sub";

    if($cond ne ""){
        $sql .= " AND ( $cond )";
    }
    $sql .=     " group by t.id 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){
        $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 r.id)  
                   from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                        left outer join opl_item i using(rid) 
                        inner join opl_user u using(uid)
              where  r.status='waiting' && dateExpiry>now()";
    if($cond ne ""){
        $sql_count .= " AND ( $cond )";
    }
    
    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 ="select r.id,r.numCopyReserve,r.rid,r.email,r.phone,r.dateRequest,r.dateExpiry,r.dateProcess,r.status,r.noticeType,
                     m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,u.firstname,u.lastname ,u.notes, u.privateNotes,
                     u.uid 
             from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                 left outer join opl_item i using(rid) 
                   inner join opl_user u using(uid)
             where   r.status='cancel' ";
            if($cond ne ""){
                $sql .= " AND ( $cond )";
            }
            $sql .=" group by r.id order by $sort $sortDir limit $offset,$size";
    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 r.id)  
                   from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                        left outer join opl_item i using(rid) 
                        inner join opl_user u using(uid)
             where   r.status='cancel' ";
    if($cond ne ""){
        $sql_count .= " AND ( $cond )";
    }
    
    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 ="select r.id,r.numCopyReserve,r.rid,r.email,r.phone,r.dateRequest,r.dateExpiry,r.dateProcess,r.status,r.noticeType,
                     m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,
                     u.uid,u.firstname,u.lastname,u.notes, u.privateNotes  
             from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                   left outer join opl_item i using(rid)
                   inner join opl_user u using(uid)
             where   r.status='reject'" ;
            if($cond ne ""){
                $sql .= " AND ( $cond )";
            }
            $sql .=" group by r.id order by $sort $sortDir limit $offset,$size";
    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 r.id)  
                   from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                         left outer join opl_item i using(rid) 
                         inner join opl_user u using(uid)
              where  r.status='reject'";
    if($cond ne ""){
        $sql_count .= " AND ( $cond )";
    }
    
    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 ="select r.id,r.numCopyReserve,r.rid,r.email,r.phone,r.dateRequest,r.dateProcess,r.status,r.noticeType,
                     m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,u.firstname,u.lastname ,u.notes, u.privateNotes,
                     u.uid 
              from  opl_requestReserve r 
                    inner join opl_marcRecord m on r.rid=m.rid 
                    left outer join opl_item  i on i.rid=m.rid
                    inner join opl_user u using(uid)
             where  dateExpiry<now()  " ;
            if($cond ne ""){
                $sql .= " AND ( $cond )";
            }
            $sql .=" group by r.id order by $sort  limit $offset,$size";
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my @reqList; 
    while(my $r=$sth->fetchrow_hashref){
        push @reqList, $r;

    }
    
    my $sql_count ="select count(distinct r.id)  
                   from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                        left outer join opl_item i using(rid) 
                     inner join opl_user u using(uid)
              where  dateExpiry<now()";
    if($cond ne ""){
        $sql_count .= " AND ( $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="select  h.dateHold,h.idloan,h.dateExpiry dateHoldExpiry, h.dateExpiry,r.* 
            from opl_hold h inner join 
                  (select distinct q.id,q.idReserve,q.dateRequest,q.dateProcess,q.numCopyReserve,u.nickname, u.uid,u.firstname,u.lastname,u.notes, u.privateNotes, 
                          m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,i.location,
                          q.email,q.phone,q.noticeType
                   from opl_reserve r inner join opl_user u using(uid) 
                        inner join opl_marcRecord m using(rid) 
                        inner join opl_item i using(rid)
                        inner join opl_requestReserve q using(idReserve)
                 )r using(idReserve)
             where h.dateLoan is null  && h.dateExpiry>now() &&  h.dateCancel is null";

   
      if($cond ne ""){
        $sql .= " AND ( $cond )";
       }
       $sql .= "  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(*) 
                   from opl_hold h inner join 
                  (select distinct q.id,q.idReserve,q.dateRequest,q.dateProcess,q.numCopyReserve,u.nickname, u.uid,u.firstname,u.lastname,u.notes, u.privateNotes, 
                          m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,i.location,
                          q.email,q.phone,q.noticeType
                   from opl_reserve r inner join opl_user u using(uid) 
                        inner join opl_marcRecord m using(rid) 
                        inner join opl_item i using(rid)
                        inner join opl_requestReserve q using(idReserve)
                 )r using(idReserve)
             where h.dateLoan is null  && h.dateExpiry>now() &&  h.dateCancel is null";
    if($cond ne ""){
        $sql_count .= " AND ( $cond )";
    }
    
    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 ="select r.id,r.numCopyReserve,s.rid,r.email,r.phone,r.dateRequest,r.dateExpiry,r.dateProcess,r.status,r.noticeType
                     ,m.title,titleSort,m.author,i.callNumber,TRIM(i.callNumSort_dewey),u.firstname,u.lastname ,u.notes, u.privateNotes 
                     ,h.dateHold,h.idloan,h.dateExpiry dateHoldExpiry,h.dateLoan,h.barcode,
                     s.idReserve,
                     u.uid
              from   opl_reserve s 
                     inner join opl_hold h on h.idReserve= s.idReserve
                     left outer join  opl_marcRecord m on s.rid =m.rid 
                     inner  join opl_item i  on s.rid =i.rid 
                     left outer join opl_user u using(uid)
                     inner join opl_requestReserve r on s.idReserve=r.idReserve
              where  h.idloan>0 && h.barcode not regexp '^\_\_\_' ";
              if($cond ne ""){
                $sql .= " AND ( $cond )";
               }

              $sql .="  group by idReserve order by $sort  limit $offset,$size";
    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  where  h.idloan>0 && h.barcode not regexp '^\_\_\_'";
    if($cond ne ""){
        $sql_count .= " AND ( $cond )";
    }
    
    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 $sql1 =" select r.id,s.numCopyReserve numCopyReserve,s.rid,r.email,r.phone,r.dateRequest,r.dateExpiry,r.dateProcess,r.status,r.noticeType,
                      s.idReserve,m.title,titleSort,m.author,i.callNumber,i.callNumSort_dewey,u.firstname,u.lastname,                      
                      u.uid,u.notes, u.privateNotes  ,s.priority,                      
                      s.dateReserve datePlaceReserve ,s.dateExpiry dateReserveExpiry                
            from  opl_reserve s                       
                  inner join opl_marcRecord m on s.rid =m.rid                      
                  left outer join opl_item i  on s.rid =i.rid                     
                  inner join opl_user u on u.uid =s.uid                      
                  inner join opl_requestReserve r on s.idReserve=r.idReserve               
            where (s.dateCancel is  null && s.numCopyReserve>0 ) && s.dateExpiry >now() ";
    my $sql="select $fieldMap->{'reserve'},t.*  
             from  opl_reserve r  inner join 
                (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  using(idReserve)
            where (r.dateCancel is  null && r.numCopyReserve>0 ) && r.dateExpiry >now() ";



        if($cond ne ""){
            $sql .= " AND ( $cond )";
        }
       $sql .=     "  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'} ;
        $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){
        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->{'priority'}=$rqPriorityTbl->{$r->{'rid'}}->{$r->{'idReserve'}};
        $r->{'maxPriority'}=$rqPriorityTbl->{$r->{'rid'}}->{'maxPriority'};
    }

  
    my $sql_count=" select count(distinct s.idReserve)                
            from  opl_reserve s                        
                  inner join opl_marcRecord m on s.rid =m.rid                      
                  left outer join opl_item i  on s.rid =i.rid                     
                  inner join opl_user u on u.uid =s.uid 
                  inner join opl_requestReserve r on s.idReserve=r.idReserve                     
            where (s.dateCancel is  null && s.numCopyReserve>0 ) && s.dateExpiry >now() ";

    if($cond ne ""){
        $sql_count .= " AND ( $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_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_count ="select count(distinct r.id)  
                   from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                        left outer join opl_item i using(rid) 
                        inner join opl_user u using(uid) ";
    if($cond ne ""){
        $sql_count .= " where ( $cond )";
    }
    
    my $numOfRequestFound = $dbh->selectrow_array($sql_count, undef);

    my $sql ="select r.*, r.status as reqStatus,m.title,m.titleSort,m.author,i.callNumber,i.callNumSort_dewey,u.firstname,u.lastname ,u.notes, u.privateNotes 
             from  opl_requestReserve r inner join opl_marcRecord m using(rid) 
                   left outer join opl_item i using(rid) 
                   inner join opl_user u using(uid)";
    if($cond ne ""){
        $sql .= " where ( $cond )";
    }
    $sql .=" group by r.id 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_tracking=$dbh->prepare("select * 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 ',
                    waiting =>'waiting',cancel=>'Cancel', reject=>'Reject', reserve=>'Reserve/Hold', ready=>'Ready for pickup',
                    're-active'=>'Re-activate'};
     
    while(my $r=$sth->fetchrow_hashref){
        $sth_tracking->execute($r->{'id'});
        my @tracking=();
        while(my $s = $sth_tracking->fetchrow_hashref){
           $s->{'action'} = $statusMap->{$s->{'action'}};
            push @tracking,$s;
        }
        $r->{'tracking'} =\@tracking; 
        push @reqList, $r;        
    } 
    return ($numOfRequestFound,\@reqList);            
}
#-------------------------------------------------------------------------------------------------
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 .= " r.$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: