#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use POSIX;

use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::Date qw(
    date_parse
    date_today
    date_text
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

use Opals::Circulation qw(
    circ_adjustHoldReserve
);

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        => 'report/listreserve.tmpl',
            reqPermission   => 'rpt_circ|circ_loan',
        }
);

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('listreserve') );
my $mSort = $input->{'sort'};
my $sDirection = $input->{'sDirection'};

if ( !$mSort ){ 
    $mSort = 'dateExpiry'; 
    $sDirection='asc';
}
circ_adjustHoldReserve($dbh);
my @Items = ();
my ($totalAvail, $totalUnavail);

    my $sql = "select distinct r.rid,count(distinct i.barcode) as numOfCopyReserve,m.title,m.titleSort,i.callNumber, u.uid as reserveUserUid, u.email,
               concat(u.lastname,', ', u.firstname) as borrower, r.dateReserve, r.dateExpiry as dateExpiry,r.priority,r.idReserve, 
               h.dateExpiry as H_expDate,i.barcode 
    from (opl_item as i inner join opl_marcRecord as m on i.rid=m.rid) 
    inner join ((opl_reserve as r left outer join opl_hold as h on r.idReserve=h.idReserve && h.dateCancel is null) inner join opl_user as u on r.uid=u.uid) on i.rid=r.rid
    where i.barcode not regexp '^\_\_\_' && r.datecancel is null &&  h.dateLoan is null && ((r.dateExpiry>now() && r.numCopyReserve>0 && (h.dateCancel is not null ||h.idreserve is null)) || h.dateExpiry >now()) group by r.rid ,r.uid order by r.rid,r.priority,r.idReserve  
   ";

    my $query = $dbh->prepare($sql);
    $query->execute();
    my ($preRid,$preBorrower)=(-1,"");
    while (my $rec = $query->fetchrow_hashref)
    {
        if($preRid  != $rec->{'rid'} || $preBorrower ne $rec->{'borrower'}){
             push @Items, $rec; 
             $preRid=$rec->{'rid'};
             $preBorrower=$rec->{'borrower'};
        }
        
    }
    $query->finish;


# set priority ,expiry date and reservation status (res or hold)
my $priority=1;
my $preRid=-1;
foreach my $rec (@Items)
{
    # set priority 
    if ( $rec->{'rid'} != $preRid ) {
        $priority=1;
        $preRid=$rec->{'rid'};
    }
    if(!defined $rec->{'H_expDate'} || $rec->{'H_expDate'} eq''){
        $rec->{'priority'} = $priority++; 
    }
    else{
        $rec->{'priority'} ="";
    }
   # set expiry date and reservation status (res or hold)
   if($rec->{'H_expDate'}){
       $rec->{'dateExpiry'} = $rec->{'H_expDate'};
       $rec->{'status'} = 'Hold';
       $rec->{'printSlip'}=1; 
       $rec->{'barcode'}=$rec->{'barcode'}; 
       $rec->{'email'}=$rec->{'email'}; 
   }
   else{
       $rec->{'status'} = 'Reserve';
   }
}

=item
if($mSort ne 'dateExpiry'){
    sortResultTbl('dateExpiry','asc',\@Items);
}
=cut


sortResultTbl($mSort,$sDirection,\@Items);

$sDirection=($sDirection eq 'asc' ?'desc':'asc');


#format Date
foreach my $rec (@Items)
{
    $rec->{'dateExpiry'} = date_text($rec->{'dateExpiry'}, 0);
}

$template->param(reserveList => \@Items); 

$template->param(sort => $mSort); 
$template->param(sDirection => $sDirection); 
#$template->param(debug => $sql); 

#Tue, Jan 12, 2010 @ 10:31:41 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);

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

######################################################################

sub sortResultTbl{
   my ($fName,$direction,$aTbl) =@_;
   @$aTbl = sort {$a->{'priority'} cmp $b->{'priority'}} @$aTbl;
   if($direction eq 'desc'){
       @$aTbl = sort {$b->{$fName} cmp $a->{$fName}  } @$aTbl;
   }
   else{
       @$aTbl = sort {$a->{$fName} cmp $b->{$fName}} @$aTbl;
   }

   @$aTbl = sort {
        if ($fName eq 'title'){
             if ($direction eq 'desc'){
                  $b->{'titleSort'} <=> $a->{'titleSort'}
               || lc($b->{'titleSort'}) cmp lc($a->{'titleSort'})
             }
             else{
                  $a->{'titleSort'} <=> $b->{'titleSort'}
               || lc($a->{'titleSort'}) cmp lc($b->{'titleSort'})
            }
        }
    } @$aTbl;

}
#---------------------------------------------------------------
__END_OF_FILE: