#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;use POSIX qw(
    floor
    ceil
);

use Date::Calc qw(
    Day_of_Week 
    Week_Number 
    Day_of_Year
    Monday_of_Week 
    Month_to_Text
    English_Ordinal
    );


use Time::localtime;

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

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

my @csvHeader_tb1=qw(
    LOAN
    INLIBRAY
    RESERVE
    RENEW
    RETURN    
);

my @fields_tb1=qw(
    loan
    inlibrary
    reserve
    renewal
    return
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $incExcOpt = $input->{'incExcOpt'};
if(!$incExcOpt || $incExcOpt ne 'inclusion'){
    $incExcOpt='exclusion';
}
#$incExcOpt = "exclusion" if ( !$incExcOpt );
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/circsttc.tmpl' ,
            reqPermission   => 'report',
        }
);

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('circsttc') );
my %sttcsTbl;
my $sttcsTotalTbl=undef;
my $sttcsTotalCSV=undef;

my $dateFrom = $input->{'DateFrom'};
my $dateTo   = $input->{'DateTo'}; 
my $subDivBy = $input->{'type'};
my $groupType = $input->{'user'};
my $groupGender = $input->{'gender'};
my $periodicalType = $input->{'period'}; 
my ($numOfWorkDay,$numOfMonth);
$periodicalType = "monthly" if ( !$periodicalType );
$groupType      = "none" if ( !$groupType );
    my $tm = localtime;
    my $dateToday = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
 
if(!$dateFrom && !$dateTo){
    $dateFrom = $dateToday ;
    $dateTo = $dateToday ;
    $template->param(report => 0);
  
}
else{
    $dateFrom .= " 00:00:00";
    $dateTo  =($dateTo gt $dateToday)?$dateToday:$dateTo;
    $dateTo .= " 23:59:59";
    $numOfWorkDay = date_deltaWorkDay(date_parse($dateFrom),date_parse($dateTo) );
    $numOfWorkDay=1 if ($numOfWorkDay==0);
    $numOfMonth   = date_deltaMonth(date_parse($dateFrom),date_parse($dateTo));
    $numOfMonth   = 1 if ($numOfMonth==0);
    my @reportTbl_total==[] ;
    getStatistics($groupType,$subDivBy,$periodicalType,$dateFrom,$dateTo,$groupGender,$incExcOpt);
   
    if($input->{'op'} eq 'saveCsv'){
       
        GetCirculatedStatisticCSV($dbh, $dateFrom, $dateTo,$incExcOpt);
       
        my $reportCSV =createReportCSV();
        print "Content-Encoding: UTF-8\n";
        print "Content-type: text/csv; charset=UTF-8\n";
        print "Content-Disposition:attachment;filename=circsttc.csv\n\n"; 
        print "\"\",";
        print "\"",  join("\",\"",@csvHeader_tb1), "\"\n";
        foreach my $row(@$reportCSV){
            print "\"",  join("\",\"",@$row), "\"\n";
        }
       
      
    }
    else{
        GetCirculatedStatistic($dbh, $template, $dateFrom, $dateTo,$incExcOpt);
        my @reportTbl =createReportTbl();
    
        if(defined $sttcsTotalTbl){
            foreach my $t(sort keys %$sttcsTotalTbl){
                my $stats ={dewey =>$t};
                foreach my $circType qw(loan inlibrary return renewal reserve){
                    $stats->{$circType} = $sttcsTotalTbl->{$t}->{$circType};
                    $stats->{'total'}  += $sttcsTotalTbl->{$t}->{$circType};
                }
                push @reportTbl_total,$stats;
            }
        }

        
        $template->param(report             => 1);
        $template->param(periodicalType     => $periodicalType);
        $template->param(groupType          => $groupType);
        $template->param(groupGender        => $groupGender);
        $template->param(subDivBy           => $subDivBy);
        $template->param(Items              => \@reportTbl);
        $template->param(Items_tot          => \@reportTbl_total) if(defined $sttcsTotalTbl);
         
    

    if($incExcOpt eq 'exclusion'){
        $template->param(exclusion =>1);
    }
    elsif($incExcOpt eq 'inclusion'){
        $template->param(inclusion =>1);
    }


    if($dateRange eq 'all'){
        $template->param(
            rangeAll   => 1,
            dateRangeOpt=>"all"
       );
    }
    elsif($dateRange eq 'fYear'){
        $template->param(
            dateFrom    => substr($dateFrom, 0, 10),
            dateTo      => substr($dateTo, 0, 10),
            rangeFYear   => 1,
            dateRangeOpt=>"fYear"
       );
    }
    else{
        $template->param(
            dateFrom    => substr($dateFrom, 0, 10),
            dateTo      => substr($dateTo, 0, 10),
            rangeSel   => 1,
            dateRangeOpt=>"rangeSel"
       );

    }

    #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);
    $dbh->disconnect();
    }
} 
#########################################################################################
sub getStatistics{
    my($groupType,$subDivBy,$periodicalType,$dateB,$dateE,$groupGender,$incExcOpt)=@_;
    #my $SQL = makeSQLs($groupType,$dateB,$dateE);
    my $SQL = {loan      => build_SQL_loan($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt),
               inlibrary => build_SQL_inlibrary($groupType,$subDivBy,$dateB,$dateE,$incExcOpt),
               return    => build_SQL_return($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt), 
               renewal   => build_SQL_renewal($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt), 
               reserve   => build_SQL_reserve($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt)
              };
    foreach my $circType (keys (%$SQL)){
        my ($period,$dewyGroup);
        my $query = $dbh->prepare($SQL->{$circType});
        $query->execute();
        if($subDivBy eq 'sub10' || $subDivBy eq 'sub100'){
            while (my $rec = $query->fetchrow_hashref) {
                $period=inPeriodOf($rec->{'date'});
                $dewyGroup=isDeweyGroupOf($rec->{'callNumber'});
                add2sttcsTbl($rec->{'groupType'},$dewyGroup,$period,$circType,1);
                $sttcsTotalTbl->{$dewyGroup}->{$circType} =0 if(!defined $sttcsTotalTbl->{$dewyGroup}->{$circType});
                $sttcsTotalTbl->{$dewyGroup}->{$circType} +=1;
            }
        }
        elsif($subDivBy eq 'itemtype'){
            while (my $rec = $query->fetchrow_hashref) {
                $period=inPeriodOf($rec->{'date'});
                add2sttcsTbl($rec->{'groupType'},$rec->{'circCode'},$period,$circType,1);
                $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType} =0 if(!defined $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType});
                $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType} +=1;
                 
            }
        }
        else{
            while (my $rec = $query->fetchrow_hashref) {
                $period=inPeriodOf($rec->{'date'});
                add2sttcsTbl($rec->{'groupType'},'',$period,$circType,1);
            }
        }

        $query->finish;

    }
}
#########################################################################################
sub build_SQL_loan{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";
   
   if($subDivBy eq 'itemtype'){
       $queryStr="select dateLoan as date, id as circCode";
       $joinCond="((opl_item as i inner join opl_itemType as t on i.typeId=t.id) inner join opl_loan as l on l.barcode=i.barcode) ";
   }
   else{
       $queryStr="select dateLoan as date, i.callNumber";
       $joinCond="( opl_loan as l left join opl_item as i on l.barcode=i.barcode)";
   }
   
   if ($groupType eq "grade" ) {
       $queryStr .= ",concat('Grade: ', grade) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "homeroom" ){ 
       $queryStr .= ", concat('Homeroom: ',lower(homeroom)) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "teacher" ){ 
       $queryStr .= ",  concat('Teacher: ',ucase(teacher))  as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "usertype" ){ 
       $queryStr .= ",  concat('User type: ', catname) as groupType ";
       $joinCond .= " inner join (opl_user as u inner join opl_category as c on  u.categorycode = c.catid) on l.uid=u.uid ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
        if($groupGender eq '0' || $groupGender eq '1'){
            $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
        }
   }
   
   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr =" where dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.uid >0 "; 
   if($incExcOpt eq 'exclusion'){
       #$whereStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $whereStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
   $queryStr .= " from " . $joinCond . $whereStr;
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }
   
   return $queryStr;
}

########################################################################################
sub build_SQL_inlibrary{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";

   if($subDivBy eq 'itemtype'){
       $queryStr="select dateLoan as date, id as circCode";
       $joinCond="((opl_item as i inner join opl_itemType as t on i.typeId=t.id) inner join opl_loan as l on l.barcode=i.barcode) ";
   }
   else{
       $queryStr="select dateLoan as date, i.callNumber";
       $joinCond="(opl_loan as l left join opl_item as i  on l.barcode=i.barcode)";
   }
   if ($groupType eq "grade" ) {
       $queryStr .= ",concat('Grade: ', '') as groupType ";
   }
    elsif ($groupType eq "homeroom" ){ 
       $queryStr .= ", concat('Homeroom: ','') as groupType ";
   }
   elsif ($groupType eq "teacher" ){ 
       $queryStr .= ",  concat('Teacher: ','')  as groupType ";
   }
   elsif ($groupType eq "usertype" ){ 
       $queryStr .= ",  concat('User type: ', '') as groupType ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
   }
   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr =" where dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.uid =0 "; 
   if($incExcOpt eq 'exclusion'){
       #$whereStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $whereStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
    
  # $queryStr .= ", 'none' as groupType ";
   $queryStr .= " from " . $joinCond . $whereStr;
  
   return $queryStr;
}

#########################################################################################
sub build_SQL_return{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";
   if($subDivBy eq 'itemtype'){
       $queryStr="select dateReturn as date, id as circCode";
       $joinCond="((opl_item as i inner join opl_itemType as t on i.typeId=t.id) inner join opl_loan as l on l.barcode=i.barcode) ";
   }
   else{
       $queryStr="select dateReturn as date, i.callNumber";
       $joinCond="(opl_item as i inner join opl_loan as l on l.barcode=i.barcode)";
   }
   if ($groupType eq "grade" ) {
       $queryStr .= ", concat('Grade: ', grade) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "homeroom" ){ 
       $queryStr .= ",  concat('Homeroom: ',lower(homeroom)) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "teacher" ){ 
       $queryStr .= ",  concat('Teacher: ',ucase(teacher))  as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "usertype" ){ 
       $queryStr .= ",  concat('User type: ', catname) as groupType ";
       $joinCond .= " inner join (opl_user as u inner join opl_category as c on  u.categorycode = c.catid) on l.uid=u.uid ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
        if($groupGender eq '0' || $groupGender eq '1'){
            $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
        }
   }

   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr ="  where dateReturn >= '$dateFrom' && dateReturn <= '$dateTo' && l.uid >0  "; 
   if($incExcOpt eq 'exclusion'){
       $whereStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
   
   $queryStr .= " from " . $joinCond . $whereStr;
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }
   return $queryStr;
}
#########################################################################################
sub build_SQL_renewal{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";
   if($subDivBy eq 'itemtype'){
       $queryStr="select dateRenewal as date, id as circCode";
       $joinCond="((opl_item as i inner join opl_itemType as t on i.typeId=t.id) inner join opl_loan as l on l.barcode=i.barcode) ";
   }
   else{
       $queryStr="select dateRenewal as date, i.callNumber";
       $joinCond="( opl_item as i inner join opl_loan as l on l.barcode=i.barcode)";
   }
   if ($groupType eq "grade" ) {
       $queryStr .= ", concat('Grade: ', grade) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "homeroom" ){ 
       $queryStr .= ",  concat('Homeroom: ',lower(homeroom)) as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "teacher" ){ 
       $queryStr .= ",  concat('Teacher: ',ucase(teacher))  as groupType ";
       $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
   }
   elsif ($groupType eq "usertype" ){ 
       $queryStr .= ", concat('User type: ', catname) as groupType ";
       $joinCond .= " inner join (opl_user as u inner join opl_category as c on  u.categorycode = c.catid) on l.uid=u.uid ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
        if($groupGender eq '0' || $groupGender eq '1'){
            $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
        }
   }

   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr =" where dateRenewal >= '$dateFrom' && dateRenewal <= '$dateTo' && l.uid >0  "; 
   if($incExcOpt eq 'exclusion'){
       #$whereStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $whereStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
  
   $queryStr .= " from " . $joinCond . $whereStr;
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }
   return $queryStr;
}
#########################################################################################
sub build_SQL_reserve{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";
   my $groupBy="";
   if($subDivBy eq 'itemtype'){
       $queryStr="select distinct idReserve, dateReserve as date, id as circCode";
       $joinCond="((opl_reserve as r left outer join opl_item as i on i.rid=r.rid) inner join opl_itemType as t on i.typeId=t.id)" ;
       #$groupBy=" group by r.rid,r.dateReserve ";####Thai
       $groupBy=" group by r.idReserve ";####Ha
   }
   else{
       $queryStr="select idReserve,dateReserve as date, i.callNumber ";
       $joinCond="(opl_reserve as r left outer join opl_item as i on i.rid=r.rid)";
       $groupBy=" group by r.idReserve "; ####Ha
   }

   if ($groupType eq "grade" ) {
       $queryStr .= ", concat('Grade: ', grade) as groupType ";
       $joinCond .=" inner join opl_user as u on r.uid= u.uid ";
   }
   elsif ($groupType eq "homeroom" ){ 
       $queryStr .= ",  concat('Homeroom: ',lower(homeroom))  as groupType ";
       $joinCond .=" inner join opl_user as u on r.uid= u.uid ";
   }
   elsif ($groupType eq "teacher" ){ 
       $queryStr .= ",  concat('Teacher: ',ucase(teacher))  as groupType ";
       $joinCond .=" inner join opl_user as u on r.uid= u.uid ";
   }
   elsif ($groupType eq "usertype" ){ 
       $queryStr .= ",  concat('User type: ', catname) as groupType ";
       $joinCond .= " inner join (opl_user as u inner join opl_category as c on  u.categorycode = c.catid) on r.uid=u.uid ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
        if($groupGender eq '0' || $groupGender eq '1'){
            $joinCond .=" inner join opl_user as u on l.uid= u.uid ";
        }
   }
   #$queryStr .= " from " . $joinCond . " where dateReserve >= '$dateFrom' && dateReserve <= '$dateTo'  && l.uid >0 " .  $groupBy; ####Thai
   ## Tue, Jun 30, 2009 @ 14:16:49 EDT 
   
   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr =" where dateReserve >= '$dateFrom' && dateReserve <= '$dateTo'  && r.uid >0  "; 
   if($incExcOpt eq 'exclusion'){
      # $whereStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $whereStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
   
   $queryStr .= " from " . $joinCond . $whereStr .  $groupBy; ####Ha
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }


     
   return $queryStr;
}

#########################################################################################
sub isDeweyGroupOf
{
    my ($callNumber) = @_;
    my ($retval,$dewey)=('','');
    if ($callNumber =~ m/([\d]+\.?[\d]*)/){ 
        $dewey=$1;
        if ( $subDivBy eq 'sub10' )
        {
            $dewey = floor($dewey - ($dewey % 10));
        }
        elsif ( $subDivBy eq 'sub100' )
        {
            $dewey = floor($dewey - ($dewey % 100));
        }
    }
    
    if ($callNumber =~  m/^([^\ ^\d]+)/ ){
        $retval = $1 . " " .$dewey; 
    }
    else{
        $retval=$dewey;
    }
    return $retval;
}
#########################################################################################
sub inPeriodOf{
    my($date)=@_;
    my $retval;
    my ($year,$month,$day)=(substr($date, 0, 4),substr($date, 5, 2),substr($date, 8, 2));
    if($periodicalType eq 'weekly'){
        my $wn=Week_Number($year,$month,$day);
        $wn=$wn%52 +1;
        ($year,$month,$day)=Monday_of_Week($wn,$year);
    }
    elsif($periodicalType eq 'monthly'){
       $day = 1;
    }
    elsif($periodicalType eq 'yearly'){
       $day = 1;
       $month=1;
    }
    $retval= sprintf("%4d-%2d-%2d",$year,$month,$day);
    return $retval;
}
#########################################################################################
sub add2sttcsTbl{
#        
#       cirdType: [loan | inlibrary | return | renewal | reserve ]
#
    
    my($group,$dewey,$period,$circType,$n) =@_;
    if($group eq '' || !defined $group){
        $group='none';
    }
    $group =~ s/ +$//g;
    $group =~ s/:$/: \[blank\]/;
    
    if(exists $sttcsTbl{$group}){
        $sttcsTbl{$group}{$circType} +=$n;
    }
    else{
        $sttcsTbl{$group}={loan=>0, inlibrary=>0,reserve=>0,return=>0,renewal=>0};
        $sttcsTbl{$group}{$circType} =$n;
    }
    if(exists $sttcsTbl{$group}{'periodOf'}{$period}){
        $sttcsTbl{$group}{'periodOf'}{$period}{$circType} +=$n;
      }
    else{
        $sttcsTbl{$group}{'periodOf'}{$period}={loan=>0, inlibrary=>0,reserve=>0,return=>0,renewal=>0};
        $sttcsTbl{$group}{'periodOf'}{$period}{$circType} =$n;
     }
    if($dewey ne ''){
        if(exists $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}){
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{$circType} +=$n;
        }
        else{
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}={loan=>0, inlibrary=>0, reserve=>0,return=>0,renewal=>0};
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{$circType} =$n;
        }
    }
    
}
#########################################################################################

sub createReportTbl(){
    my @retTbl  = ();
    my $csvList = [];
    my ($loan,$inlibrary,$return,$reserve,$renewal,$total)=(0,0,0,0,0,0);
    my $periodName="";
    foreach my $group (sort keys (%sttcsTbl)){
        if($group ne 'none'){
            $loan   = $sttcsTbl{$group}{'loan'};
            $inlibrary   = $sttcsTbl{$group}{'inlibrary'};
            $return = $sttcsTbl{$group}{'return'};
            $reserve= $sttcsTbl{$group}{'reserve'};
            $renewal= $sttcsTbl{$group}{'renewal'};
            $total  = $loan + $inlibrary + $return + $reserve + $renewal;
            push @retTbl , {UserGroup=>$group, loan=>$loan, inlibrary=>$inlibrary,return=>$return, reserve=>$reserve,renewal=>$renewal,total=>$total};
            
        }
        foreach my $period (sort keys (%{$sttcsTbl{$group}{'periodOf'}})){
            $loan   = $sttcsTbl{$group}{'periodOf'}{$period}{'loan'};
            $inlibrary   = $sttcsTbl{$group}{'periodOf'}{$period}{'inlibrary'};
                           
            $return = $sttcsTbl{$group}{'periodOf'}{$period}{'return'};
            $reserve= $sttcsTbl{$group}{'periodOf'}{$period}{'reserve'};
            $renewal= $sttcsTbl{$group}{'periodOf'}{$period}{'renewal'};
            $total  = $loan + $inlibrary + $return + $reserve + $renewal;
            $periodName=formatPeriod($periodicalType,$period);
            push @retTbl , {period=>$periodName, loan=>$loan,inlibrary=>$inlibrary, return=>$return, reserve=>$reserve,renewal=>$renewal,total=>$total};
            foreach my $deweyGroup (sort keys (%{$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}})){
                
                $loan   = $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'loan'};
                $inlibrary   = $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'inlibrary'};
                $return = $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'return'};
                $reserve= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'reserve'};
                $renewal= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'renewal'};
                $total  = $loan + $inlibrary + $return + $reserve + $renewal;
               push @retTbl , {dewey=>$deweyGroup, loan=>$loan,inlibrary=>$inlibrary, return=>$return, reserve=>$reserve,renewal=>$renewal,total=>$total};

            }
            
        }
    }
    return @retTbl;

}

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

sub createReportCSV(){
    my $csvList = [];
    my ($loan,$inlibrary,$return,$reserve,$renewal,$total)=(0,0,0,0,0,0);
    my $periodName="";
    my $i=-1;
    foreach my $group (sort keys (%sttcsTbl)){
        if($group ne 'none'){
            $i++;
            $csvList->[$i] =[];
            push @{$csvList->[$i]},$group;
            foreach my $f (@fields_tb1){
                push @{$csvList->[$i]},$sttcsTbl{$group}{$f};
            }
        }
        foreach my $period (sort keys (%{$sttcsTbl{$group}{'periodOf'}})){
            $periodName=formatPeriod($periodicalType,$period);

            $i++;
            $csvList->[$i] =[];
            push @{$csvList->[$i]},$periodName;
            foreach my $f (@fields_tb1){
                push @{$csvList->[$i]},$sttcsTbl{$group}{'periodOf'}{$period}{$f};
            }

            foreach my $deweyGroup (sort keys (%{$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}})){
                $i++;
                $csvList->[$i] =[];
                push @{$csvList->[$i]},$deweyGroup;
                foreach my $f (@fields_tb1){
                    push @{$csvList->[$i]},$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{$f};
                }

            }
            
        }
    }
    return $csvList;

}
#----------------------------------------------------------------------
sub  GetCirculatedStatisticCSV{
    my ($dbh, $dateFrom, $dateTo,$incExcOpt) = @_;
    
     
   ########## Generate table 3 report
    my $activeUsers   = getUserStats($dbh,'active');
    my $activeUsersSel= getUserStats($dbh,'active',$dateFrom, $dateTo);
    my $totalUsers    = getUserStats($dbh,'');
    my $inactiveUsers = $totalUsers - $activeUsers;
=item
    $template->param(totalUsers     => $totalUsers);
    $template->param(activeUsers    => $activeUsers);
    $template->param(activeUsersSel => $activeUsersSel);
    $template->param(inactiveUsers  => $inactiveUsers);
    $template->param(totalItemDel =>getDeletionStats($dbh));
   
=cut    
    my $totalItems         = getAddedItemStats($dbh);
    my $numActiveItems     = getLoanStats($dbh,$dateFrom, $dateTo);
    my $totalActiveItems   = getLoanStats($dbh);
    my $totalInactiveItems = $totalItems - $totalActiveItems ;
=item
    $template->param(totalOfItems       =>$totalItems);
    $template->param(numOfItems         => $numActiveItems);
    $template->param(totalActiveItems   => $totalActiveItems);
    $template->param(totalInactiveItems =>$totalInactiveItems);
    $template->param(itemAddedPeriodSel =>getAddedItemStats($dbh,$dateFrom, $dateTo));
    $template->param(numActiveItems     =>$numActiveItems);
    
=cut    
   
    my ($query, $rec);     
    ########## TOTAL of lost items
    
#    $query = $dbh->prepare("    select count(distinct barcode) 
#            from  opl_itemstatus where status=3");
    
    $query = $dbh->prepare("select count(i.barcode) as count
from opl_item i inner  join 
    (select t3.* from
        (select * from opl_itemstatus where   ondate >= ? && ondate < ? && status<>6) as t3
        left outer join 
        (select * from opl_itemstatus where  ondate >= ? && ondate < ? && status<>6) as t4
    on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status =3
    )as t5 on i.barcode=t5.barcode ");
    $query->execute($dateFrom, $dateTo,$dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
###    $template->param(totalItemLost => $rec);

    $query->finish;

    # Number of lost items-- selected period
    $query = $dbh->prepare("select count(distinct barcode) 
             from  opl_itemstatus where status=3 && ondate >= ? && ondate < ?");
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    
###    $template->param(itemLostSelPeriod => $rec);

    $query->finish;
    ########## TOTAL of found items
    $query = $dbh->prepare("select count(barcode) 
            from opl_found ");
    $query->execute();
    ($rec) = $query->fetchrow_array;
###    $template->param(totalItemFound => $rec);

    $query->finish;

    # Number of found items-- selected period
    $query = $dbh->prepare("select count(barcode) 
            from opl_found
            where ondate >= ? && ondate < ?");
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
###    $template->param(itemFoundSelPeriod => $rec);

    $query->finish;

    ########## TOTAL of damaged items
    $query = $dbh->prepare("select count(barcode) 
            from opl_loan inner join opl_damage on opl_loan.idloan=opl_damage.idloan ");
    $query->execute();
    ($rec) = $query->fetchrow_array;
###    $template->param(totalItemDamaged => $rec);

    $query->finish;

    # Number of damaged items-- selected period
###    _getNumOfDamaged($dbh,$template,$dateFrom,$dateTo,$incExcOpt);
    
    ########## TOTAL of titles
    $query = $dbh->prepare("select count(distinct rid) from opl_item");
    $query->execute();
    ($rec) = $query->fetchrow_array;
    my $totalTitles=$rec;
###    $template->param(totalTitles => $totalTitles);

    $query->finish;
    
   ########## TOTAL active titles 
    $query=$dbh->prepare("select count(distinct rid) from opl_item inner join opl_loan on opl_item.barcode=opl_loan.barcode ");

    $query->execute();
    ($rec) = $query->fetchrow_array;
    my $totalActiveTitles= $rec;
    my $totalInactiveTitles = $totalTitles - $totalActiveTitles;
###    $template->param(totalActiveTitles => $totalActiveTitles);
###    $template->param(totalInactiveTitles => $totalInactiveTitles);
    $query->finish;

    ########## TOTAL active titles --  selected period

    $query=$dbh->prepare("select count(distinct rid) 
                          from opl_item inner join opl_loan on opl_item.barcode=opl_loan.barcode 
                          where dateLoan >=? && dateLoan <? ");

    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    my $activeTitlesSel= $rec;
###    $template->param(activeTitlesSel => $activeTitlesSel);
    $query->finish;

    ########### Generate the Most Current Circulation Statistic report  (table4)
###    _genMostCurrentCircStat_table4($dbh, $template, $dateFrom, $dateTo,$incExcOpt);
}
#########################################################################################

sub formatPeriod{
    my ($format,$date)=@_;
    my $retval="";
    my ($year,$month,$day)=(substr($date, 0, 4),substr($date, 5, 2),substr($date, 8, 2));
    my $monthName=Month_to_Text(int($month));
    if($format eq 'yearly'){
        $retval=$year;
    }
    elsif($format eq 'monthly'){
        $retval=sprintf("Month of %.3s %4d",$monthName,$year);
    }
    elsif($format eq 'weekly'){
        $retval=sprintf("Week of %.3s %d, %4d ",$monthName,$day,$year);
    }
    else{
        $retval=sprintf("%.3s %d, %4d ",$monthName,$day,$year);
    }
    return $retval;
}

#########################################################################################
sub getUserStats{
    my($dbh,$uStatus,$dateFrom,$dateTo)=@_;
    my $queryStr="";
    if($uStatus eq 'active'){ #find active users
        $queryStr ="select count(distinct l.uid) 
                    from opl_loan as l inner join opl_user as u on u.uid=l.uid where u.status=1 ";
        if($dateFrom && $dateTo){
            $queryStr .= " && dateLoan >='$dateFrom' && dateLoan <='$dateTo'";
        }
    }
    else{ #find total users
	    $queryStr ="select count(uid) from opl_user where status=1 ";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}

#########################################################################################
sub getDeletionStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct barcode) from opl_item where substring(barcode, 1, 3) = '___' ";
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
#########################################################################################
sub getLoanStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct barcode) from opl_loan ";
    if($dateFrom && $dateTo){
      $queryStr .= " where dateLoan >='$dateFrom' && dateLoan <='$dateTo'";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
#########################################################################################
sub getAddedItemStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct barcode) from opl_item where substring(barcode, 1, 3) <> '___' ";
    if($dateFrom && $dateTo){
      $queryStr .= " && dateImport >='$dateFrom' && dateImport<='$dateTo'";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
#########################################################################################
sub _getNumOfDamaged{
    my($dbh,$template,$dateFrom,$dateTo,$incExcOpt)=@_;
    my $queryStr="select count(s.id)
                    from opl_itemstatus s inner join opl_item i on i.barcode= s.barcode
                         inner join opl_marcRecord m on m.rid =i.rid
                    where s.ondate >= ? && s.ondate < ? && s.status=2";
    if($incExcOpt eq 'exclusion'){
        #$queryStr .= "  && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
        $queryStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute($dateFrom, $dateTo);
    my ($rec)= $query->fetchrow_array;
    $template->param(itemDamagedSelPeriod => $rec);
    $query->finish;

}

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

sub  GetCirculatedStatistic
{
    my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt) = @_;
    
    ########## Generate table 2 report 
    my ($totalLoan,$avgLoanDaily,$avgLoanMonthly,
        $totalInlibrary,$avgInlibraryDaily,$avgInlibraryMonthly,$totalHold,$totalOverdue,$totalReserve,$avgReserveDaily,$avgReserveMonthly) =_getCircStat_table2($dbh, $template, $dateFrom, $dateTo,$incExcOpt);
    $template->param(totalLoan          => $totalLoan);
    $template->param(avgLoanDaily       => $avgLoanDaily);
    $template->param(avgLoanMonthly     => $avgLoanMonthly);
    $template->param(totalInlibrary     => $totalInlibrary);
    $template->param(avgInlibraryDaily  => $avgInlibraryDaily);
    $template->param(avgInlibraryMonthly=> $avgInlibraryMonthly);
    $template->param(totalHold          => $totalHold);
    $template->param(totalOverdue       => $totalOverdue);
    $template->param(totalReserve       => $totalReserve);
    $template->param(avgReserveDaily    => $avgReserveDaily);
    $template->param(avgReserveMonthly  => $avgReserveMonthly);
   
   ########## Generate table 3 report
    my $activeUsers   = getUserStats($dbh,'active');
    my $activeUsersSel= getUserStats($dbh,'active',$dateFrom, $dateTo);
    my $totalUsers    = getUserStats($dbh,'');
    my $inactiveUsers = $totalUsers - $activeUsers;
    $template->param(totalUsers     => $totalUsers);
    $template->param(activeUsers    => $activeUsers);
    $template->param(activeUsersSel => $activeUsersSel);
    $template->param(inactiveUsers  => $inactiveUsers);
    
    $template->param(totalItemDel =>getDeletionStats($dbh));
   
    my $totalItems         = getAddedItemStats($dbh);
    my $numActiveItems     = getLoanStats($dbh,$dateFrom, $dateTo);
    my $totalActiveItems   = getLoanStats($dbh);
    my $totalInactiveItems = $totalItems - $totalActiveItems ;
    $template->param(totalOfItems       =>$totalItems);
    $template->param(numOfItems         => $numActiveItems);
    $template->param(totalActiveItems   => $totalActiveItems);
    $template->param(totalInactiveItems =>$totalInactiveItems);
    $template->param(itemAddedPeriodSel =>getAddedItemStats($dbh,$dateFrom, $dateTo));
    $template->param(numActiveItems     =>$numActiveItems);
    
   
    my ($query, $rec);     
    ########## TOTAL of lost items
    
#    $query = $dbh->prepare("    select count(distinct barcode) 
#            from  opl_itemstatus where status=3");
    
    $query = $dbh->prepare("select count(i.barcode) as count
from opl_item i inner  join 
    (select t3.* from
        (select * from opl_itemstatus where   ondate >= ? && ondate < ? && status<>6) as t3
        left outer join 
        (select * from opl_itemstatus where  ondate >= ? && ondate < ? && status<>6) as t4
    on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status =3
    )as t5 on i.barcode=t5.barcode ");
    $query->execute($dateFrom, $dateTo,$dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    $template->param(totalItemLost => $rec);

    $query->finish;

    # Number of lost items-- selected period
    $query = $dbh->prepare("select count(distinct barcode) 
             from  opl_itemstatus where status=3 && ondate >= ? && ondate < ?");
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    
    $template->param(itemLostSelPeriod => $rec);

    $query->finish;
    ########## TOTAL of found items
    $query = $dbh->prepare("select count(barcode) 
            from opl_found ");
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(totalItemFound => $rec);

    $query->finish;

    # Number of found items-- selected period
    $query = $dbh->prepare("select count(barcode) 
            from opl_found
            where ondate >= ? && ondate < ?");
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    $template->param(itemFoundSelPeriod => $rec);

    $query->finish;

    ########## TOTAL of damaged items
    $query = $dbh->prepare("select count(barcode) 
            from opl_loan inner join opl_damage on opl_loan.idloan=opl_damage.idloan ");
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(totalItemDamaged => $rec);

    $query->finish;

    # Number of damaged items-- selected period
    _getNumOfDamaged($dbh,$template,$dateFrom,$dateTo,$incExcOpt);
    
    ########## TOTAL of titles
    $query = $dbh->prepare("select count(distinct rid) from opl_item");
    $query->execute();
    ($rec) = $query->fetchrow_array;
    my $totalTitles=$rec;
    $template->param(totalTitles => $totalTitles);

    $query->finish;
    
   ########## TOTAL active titles 
    $query=$dbh->prepare("select count(distinct rid) from opl_item inner join opl_loan on opl_item.barcode=opl_loan.barcode ");

    $query->execute();
    ($rec) = $query->fetchrow_array;
    my $totalActiveTitles= $rec;
    my $totalInactiveTitles = $totalTitles - $totalActiveTitles;
    $template->param(totalActiveTitles => $totalActiveTitles);
    $template->param(totalInactiveTitles => $totalInactiveTitles);
    $query->finish;

    ########## TOTAL active titles --  selected period

    $query=$dbh->prepare("select count(distinct rid) 
                          from opl_item inner join opl_loan on opl_item.barcode=opl_loan.barcode 
                          where dateLoan >=? && dateLoan <? ");

    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    my $activeTitlesSel= $rec;
    $template->param(activeTitlesSel => $activeTitlesSel);
    $query->finish;

    ########### Generate the Most Current Circulation Statistic report  (table4)
    _genMostCurrentCircStat_table4($dbh, $template, $dateFrom, $dateTo,$incExcOpt);
}
#########################################################################################
sub  _getCircStat_loanOrInLibrary
{  
   my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt,$isInLibrary) =@_; 
   my $queryStr;
   # my $query = $dbh->prepare("select count(*) from opl_loan
   #          where dateLoan >= ? && dateLoan <= ? && uid>0");   
   $queryStr = "select count(distinct idloan) from opl_loan l inner join opl_item i on i.barcode = l.barcode
                inner join opl_marcRecord m on m.rid= i.rid 
                where dateLoan >= ? && dateLoan <= ? ";
    if($isInLibrary && $isInLibrary == 1){
         $queryStr .= "  && l.uid=0";  
    }
    else{
        $queryStr .= "  && l.uid>0"; 
    }

    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    my $query = $dbh->prepare($queryStr); 
    $query->execute($dateFrom, $dateTo);
    my ($rec) = $query->fetchrow_array;
    
    if($isInLibrary && $isInLibrary == 1){
        return (int($rec),int($rec/ $numOfWorkDay),int($rec/ $numOfMonth));
    }
    else{
        return (int($rec),int($rec/ $numOfWorkDay),int($rec/ $numOfMonth));
    }
}

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

sub  _getCircStat_table2
{  
   my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt) =@_; 
   my ($query,$rec,$queryStr);
   my ($totalHold,$totalOverdue,$totalReserve,$avgReserveDaily,$avgReserveMonthly)  = (0,0,0,0,0);
   my ($totalLoan,$avgLoanDaily,$avgLoanMonthly) = (0,0,0);
   my ($totalInlibrary,$avgInlibraryDaily,$avgInlibraryMonthly) = (0,0,0);
    ########## TOTAL loan :  $isInLibrary=0
   ($totalLoan,$avgLoanDaily,$avgLoanMonthly) = _getCircStat_loanOrInLibrary($dbh, $template, $dateFrom, $dateTo,$incExcOpt,0);
        $template->param(totalLoan      => $totalLoan,
                         avgLoanDaily   => $avgLoanDaily,   # average loan -- daily
                         avgLoanMonthly => $avgLoanMonthly  # average loan -- monthly
                        );

    ########## TOTAL inlibrary :  $isInLibrary=1
   ($totalInlibrary,$avgInlibraryDaily,$avgInlibraryMonthly) = _getCircStat_loanOrInLibrary($dbh, $template, $dateFrom, $dateTo,$incExcOpt,1);
        $template->param(totalInlibrary      => $totalInlibrary,
                         avgInlibraryDaily   => $avgInlibraryDaily,   # average loan -- daily
                         avgInlibraryMonthly => $avgInlibraryMonthly  # average loan -- monthly
                        );

    ########## TOTAL hold 
    $queryStr = "select count(*) from opl_hold h inner join opl_reserve r on r.idReserve =h.idReserve 
                 inner join opl_marcRecord m on m.rid=r.rid 
                 where dateHold >= ? && dateHold <= ? ";
    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    $totalHold = int($rec);
    
    ########## TOTAL overdue
    $query = $dbh->prepare("select count(*) from opl_odl 
            where type ='overdue' &&  ondate >= ? && ondate <= ? ");
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    $totalOverdue =int($rec);

    ########## TOTAL reserve
    $queryStr ="select count(*) from opl_reserve r  inner join opl_marcRecord m on m.rid=r.rid
            where dateReserve >= ? && dateReserve <= ? ";
    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    
    $totalReserve =int($rec);
    # average reserve -- daily
    $avgReserveDaily = int($rec/ $numOfWorkDay);
    
    # average reserve -- monthly 
    $avgReserveMonthly = int($rec/ $numOfMonth);
    return ($totalLoan,$avgLoanDaily,$avgLoanMonthly,
            $totalInlibrary,$avgInlibraryDaily,$avgInlibraryMonthly,
            $totalHold,$totalOverdue,$totalReserve,$avgReserveDaily,$avgReserveMonthly);
}
#########################################################################################
sub _genMostCurrentCircStat_table4
{
   my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt) =@_; 
   my ($query, $rec,$queryStr);
   
   # The best loan
    $queryStr = "select count(idloan) as loans, m.rid,m.title  
                 from opl_loan as l inner join  opl_item as i on l.barcode =i.barcode
            inner join opl_marcRecord m on m.rid = i.rid     
            where l.barcode=i.barcode && dateLoan >= ? && dateLoan < ? ";
    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }  
    $queryStr .= " group by rid order by loans desc limit 1";
      
    $query = $dbh->prepare($queryStr); 
    $query->execute($dateFrom, $dateTo);
    $rec = $query->fetchrow_hashref;
    $query->finish;
    $template->param(TopTime  => $rec->{'loans'},
                     TopTitle => $rec->{'title'});

    # Number of loans
    $queryStr = "select count(idloan) from opl_loan l 
                        inner join  opl_item as i on l.barcode =i.barcode
                        inner join opl_marcRecord m on m.rid = i.rid
                  where dateReturn is null";
    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }    
    $query = $dbh->prepare($queryStr);
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(NumOfLoans => $rec);

    $query->finish;

    # Number of Reserves
    $queryStr = "select sum(numCopyReserve) 
                            from opl_reserve r inner join opl_marcRecord m on m.rid=r.rid
                            where dateCancel is null && to_days(dateExpiry) > to_days(now())";
    if($incExcOpt eq 'exclusion'){
      #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }    
    $query = $dbh->prepare($queryStr);
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(NumOfReserves => $rec);

    $query->finish;

    # Number of Holds
    $queryStr = "select   count(h.idReserve) 
                 from  opl_hold h inner join opl_reserve r on r.idReserve =h.idReserve 
                       inner join opl_marcRecord m on m.rid=r.rid 
                 where h.dateCancel is null and h.dateLoan is null and to_days(h.dateExpiry) > to_days(now())";
    if($incExcOpt eq 'exclusion'){
       #$queryStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
        $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }    
    $query = $dbh->prepare($queryStr);
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(NumOfHolds => $rec);

    $query->finish;

}


__END_OF_FILE: