#!/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 Opals::CallNumberUtil qw(
    cn_parseLCC
    cn_getLccGrp
    cn_parseDewey
);



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
    tmpl_preference
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

my @csvHeader=qw(
    GROUPTYPE
    PERIOD
    LOAN
    INLIBRAY
    RESERVE
    RENEW
    RETURN
    eBookREAD
    eBookPREVIEW    
);

my @fields=qw(
    loan
    inlibrary
    reserve
    renewal
    return
    ebRead
    ebPreview
);
use JSON;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $pref = tmpl_preference($dbh);

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

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('circsttc') );

my $libType=$pref->{'libraryType'} ;
$template->param(isK_12=>($libType eq 'k-12'));
my %sttcsTbl;
my $sttcsTotalTbl=undef;

my $isLccSystem =$pref->{'classificationSystem'} eq 'LCC';

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 ;
    }
    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);
      }
if($input->{'op'} eq 'saveCsv'){
    if($dateFrom  || $dateTo){
        if($subDivBy eq 'sub10' || $subDivBy eq 'sub100' || $subDivBy eq 'lcc'){ 
            @csvHeader=qw( GROUPTYPE PERIOD DEWEY LOAN INLIBRAY RESERVE RENEW RETURN );
        }
        elsif($subDivBy eq 'itemtype'){ 
            @csvHeader=qw( GROUPTYPE PERIOD ITEMTYPE LOAN INLIBRAY RESERVE RENEW RETURN );
        }
        getStatistics($groupType,$subDivBy,$periodicalType,$dateFrom,$dateTo,$groupGender,$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 "\"",  join("\",\"",@csvHeader), "\"\n";
        foreach my $row(@$reportCSV){
            print "\"",  join("\",\"",@$row), "\"\n";
        }
    }

}
else{
    $template->param(isLccSystem             => $isLccSystem);

    if($dateFrom  eq $dateTo && $dateFrom eq $dateToday){
        $template->param(report             => 0);
    }
    else{
        getStatistics($groupType,$subDivBy,$periodicalType,$dateFrom,$dateTo,$groupGender,$incExcOpt);
        GetCirculatedStatistic($dbh, $template, $dateFrom, $dateTo,$incExcOpt);
        my @reportTbl =createReportTbl();
        my @reportTbl_total==[] ;
        if(defined $sttcsTotalTbl){
            foreach my $t(sort keys %$sttcsTotalTbl){
                my $dewey   = $sttcsTotalTbl->{$t}->{"dewey"};
                my $prefix  = $sttcsTotalTbl->{$t}->{"prefix"};
                my $location= $sttcsTotalTbl->{$t}->{"location"};

                my $stats ={dewey =>$dewey,prefix=>$prefix,location=>$location};
                foreach my $circType qw(loan inlibrary return renewal reserve ebRead ebPreview){
                    $stats->{$circType} = $sttcsTotalTbl->{$t}->{$circType};
                    $stats->{'total'}  += $sttcsTotalTbl->{$t}->{$circType};
                }
                push @reportTbl_total,$stats;
            }
        }
        open debug,">/tmp/cc"; print debug to_json(\@reportTbl,{pretty=>1});close debug;
        $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);
    }
    elsif($incExcOpt eq 'illTempOnly'){
            $template->param(illTempOnly =>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"
       );

    }
   $template->param(incExcOpt =>$incExcOpt );

#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),
               ebRead    => build_SQL_ebRead($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt),
               ebPreview => build_SQL_ebPreview($groupType,$subDivBy,$dateB,$dateE,$groupGender,$incExcOpt)
              };
    foreach my $circType (keys (%$SQL)){
        my ($period,$dewyGroup,$dewey);
        my $query = $dbh->prepare($SQL->{$circType});
        $query->execute();
        if($subDivBy eq 'sub10' || $subDivBy eq 'sub100'|| $subDivBy eq 'lcc'){
            while (my $rec = $query->fetchrow_hashref) {
                $period=inPeriodOf($rec->{'date'});
                my $prefix=$rec->{"prefix"};
                $dewey =$isLccSystem?isLccOf($rec->{'callNumber'}):isDeweyGroupOf($rec->{'callNumber'});
                $dewyGroup= $dewey;
                $dewyGroup .= " $prefix" if ($prefix ne "");

                $sttcsTotalTbl->{$dewyGroup}->{'dewey'}=$dewey;          
                $sttcsTotalTbl->{$dewyGroup}->{'prefix'}=$prefix;          
                add2sttcsTbl($rec->{'groupType'},$dewyGroup,$prefix,$dewey,$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'});
                $sttcsTotalTbl->{$rec->{'circCode'}}->{'dewey'}=$rec->{'circCode'};          
                add2sttcsTbl($rec->{'groupType'},$rec->{'circCode'},'',$rec->{'circCode'},$period,$circType,1);
                $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType} =0 if(!defined $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType});
                $sttcsTotalTbl->{$rec->{'circCode'}}->{$circType} +=1;
            }
        }
        elsif($subDivBy eq 'location'){
            while (my $rec = $query->fetchrow_hashref) {
                $period=inPeriodOf($rec->{'date'});
                $rec->{'location'}="" if(!defined $rec->{'location'});
                $sttcsTotalTbl->{$rec->{'location'}}->{'dewey'}=$rec->{'location'};          
                add2sttcsTbl($rec->{'groupType'},$rec->{'location'},'',$rec->{'location'},$period,$circType,1);
                $sttcsTotalTbl->{$rec->{'location'}}->{$circType} =0 if(!defined $sttcsTotalTbl->{$rec->{'location'}}->{$circType});
                $sttcsTotalTbl->{$rec->{'location'}}->{$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) ";
   }
   elsif($subDivBy eq 'location'){
       $queryStr="select dateLoan as date, trim(i.location) as location ";
       $joinCond="(opl_loan as l inner join opl_item as i on l.barcode=i.barcode )";
   }
   else{
       $queryStr="select dateLoan as date, i.callNumber,f.sf852Data as prefix ";
       $joinCond="( opl_loan as l left join opl_item as i on l.barcode=i.barcode left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' )";
   }
   
   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 = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
   $queryStr .= " from " . $joinCond . $whereStr;
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }
 #open debug ,">/tmp/aa"; print debug "$queryStr\n"; close debug; 
   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) ";
   }
 elsif($subDivBy eq 'location'){
       $queryStr="select dateLoan as date, trim(i.location) as location ";
       $joinCond="(opl_loan as l inner join opl_item as i  on l.barcode=i.barcode )";
   }
 else{
       $queryStr="select dateLoan as date, i.callNumber,f.sf852Data as prefix ";
       $joinCond="(opl_loan as l left join opl_item as i  on l.barcode=i.barcode left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' )";
   }
   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 = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    
  # $queryStr .= ", 'none' as groupType ";
   $queryStr .= " from " . $joinCond . $whereStr;
  
# open debug ,">/tmp/aa"; print debug "$queryStr\n"; close debug; 
   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) ";
   }
   elsif($subDivBy eq 'location'){
       $queryStr="select dateReturn as date, trim(i.location) as location ";
       $joinCond="(opl_loan as l inner join opl_item as i  on l.barcode=i.barcode )";
   }
   else{
       $queryStr="select dateReturn as date, i.callNumber,f.sf852Data as prefix ";
       $joinCond="(opl_item as i inner join opl_loan as l on l.barcode=i.barcode left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k')";
   }

   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 = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
   
   $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) ";
   }
   elsif($subDivBy eq 'location'){
       $queryStr="select dateRenewal as date, trim(i.location) as location ";
       $joinCond="(opl_loan as l inner join opl_item as i  on l.barcode=i.barcode )";
   }
   else{
       $queryStr="select dateRenewal as date, i.callNumber,f.sf852Data as prefix ";
       $joinCond="( opl_item as i inner join opl_loan as l on l.barcode=i.barcode left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' )";
   }


   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 = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
  
   $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.idReserve ";
   }
   elsif($subDivBy eq 'location'){
       $queryStr="select distinct idReserve,dateReserve as date, trim(i.location) as location ";
       $joinCond="(opl_reserve as r  left outer join opl_item as i on i.rid=r.rid) ";
       $groupBy=" group by r.idReserve "; 
   }
   else{
       $queryStr="select idReserve,dateReserve as date, i.callNumber ,f.sf852Data as prefix ";
       $joinCond="(opl_reserve as r left outer join opl_item as i on i.rid=r.rid left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' )";
       $groupBy=" group by r.idReserve "; 
   }

   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 = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
   
   $queryStr .= " from " . $joinCond . $whereStr .  $groupBy; ####Ha
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }


     
   return $queryStr;
}
#########################################################################################
sub  _build_SQL_ebookStats{
   my($ebStatsType,$groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   my $queryStr="";
   my $joinCond="";
   
   if($subDivBy eq 'itemtype'){
       $queryStr="select onDate as date, id as circCode";
       $joinCond="((opl_item as i inner join opl_itemType as t on i.typeId=t.id) inner join opl_ebReadingStats as e on e.rid=i.rid) ";
   }
   elsif($subDivBy eq 'location'){
       $queryStr="select onDate as date, trim(i.location) as location ";
       $joinCond="( opl_ebReadingStats as e left join opl_item as i on e.rid=i.rid )";
   }
   else{
       $queryStr="select onDate as date, i.callNumber,f.sf852Data as prefix ";
       $joinCond="( opl_ebReadingStats as e left join opl_item as i on e.rid=i.rid 
                                            left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' )";
   }
   
   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 e.uid=u.uid ";
   }
   else{
        $queryStr .= ", 'none' as groupType ";
        if($groupGender eq '0' || $groupGender eq '1'){
            $joinCond .=" inner join opl_user as u on e.uid= u.uid ";
        }
   }
   
   $joinCond .= " inner join opl_marcRecord m on m.rid= i.rid "; 
   my $whereStr =" where  e.type='$ebStatsType' && onDate >= '$dateFrom' && onDate <= '$dateTo' "; 
   if($incExcOpt eq 'exclusion'){
       #$whereStr .= " && (m.tempIll <> 'ILL' && m.tempIll <> 'temporary') ";
       $whereStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
   }
   elsif($incExcOpt eq 'illTempOnly'){
       $whereStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
   $queryStr .= " from " . $joinCond . $whereStr;
   if ($groupGender eq '0' || $groupGender eq '1'){ 
        $queryStr .= " && gender=$groupGender ";
   }
  # open debug,">/tmp/sql"; print debug "$queryStr\n\n" ;close debug; 
   return $queryStr;

}
#########################################################################################
sub  build_SQL_ebPreview{
    my ($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_;
   _build_SQL_ebookStats('preview',$groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt); 
}

#########################################################################################
sub  build_SQL_ebRead{
   my($groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt)=@_; 
   _build_SQL_ebookStats('read',$groupType,$subDivBy,$dateFrom,$dateTo,$groupGender,$incExcOpt); 
}

#########################################################################################
sub isDeweyGroupOf
{
    my ($callNumber) = @_;
    my $retval='';
    my $dewey =cn_parseDewey($callNumber);

    if ($dewey =~ m/([\d]+\.?[\d]*)/){ 
        if ( $subDivBy eq 'sub10' )
        {
            $retval = floor($dewey - ($dewey % 10));
        }
        elsif ( $subDivBy eq 'sub100' )
        {
            $retval = floor($dewey - ($dewey % 100));
        }
    }
    
    elsif ($dewey ne '' ){
        $retval = $dewey; 
    }
    else{
        $retval=$callNumber;
    }
    return uc($retval);
}
#########################################################################################
sub isLccOf
{
   my ($lcc) = @_;
   return uc cn_getLccGrp($lcc);
}

#########################################################################################
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 ;
        ($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,$deweyGroup,$prefix,$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,ebRead=>0,ebPreview=>0};
        $sttcsTbl{$group}{$circType} =$n;
    }
    $sttcsTbl{$group}{'prefix'}=$prefix;
    $sttcsTbl{$group}{'dewey'}=$dewey;


    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,ebRead=>0,ebPreview=>0};
        $sttcsTbl{$group}{'periodOf'}{$period}{$circType} =$n;
     }
 ##  if($dewey ne ''){
        if(exists $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}){
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{$circType} +=$n;
        }
        else{
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}={loan=>0, inlibrary=>0, reserve=>0,return=>0,renewal=>0,ebRead=>0,ebPreview=>0};
            $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{$circType} =$n;
        }
        $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'prefix'}=$prefix;
        $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'dewey'}=$dewey;
  ##  }
    
}
#########################################################################################
### Fri, Apr 20, 2012 @ 14:22:53 EDT
### 
sub createReportCSV(){
    my $csvList = [];
    my ($loan,$inlibrary,$return,$reserve,$renewal,$ebRead,$ebPreview,$total)=(0,0,0,0,0,0,0,0);
    my $periodName="";
    my $i=-1;
    foreach my $group (sort keys (%sttcsTbl)){
         foreach my $period (sort keys (%{$sttcsTbl{$group}{'periodOf'}})){
            $periodName=formatPeriod($periodicalType,$period);
            foreach my $deweyGroup (sort keys (%{$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}})){
                $i++;
                $csvList->[$i] =[];
                push @{$csvList->[$i]},$group;
                push @{$csvList->[$i]},$periodName;
                push @{$csvList->[$i]},$deweyGroup if($deweyGroup ne '');
                foreach my $f (@fields){
                    push @{$csvList->[$i]},$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{$f};
                }
            }
        }
    }
    return $csvList;
}

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

sub createReportTbl(){
    my @retTbl=();
    my ($loan,$inlibrary,$return,$reserve,$renewal,$ebRead,$ebPreview,$total,$prefix,$dewey)=(0,0,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'};
            $ebPreview= $sttcsTbl{$group}{'ebPreview'};
            $ebRead= $sttcsTbl{$group}{'ebRead'};
            $total  = $loan + $inlibrary + $return + $reserve + $renewal+$ebPreview+$ebRead;
            push @retTbl , {UserGroup=>$group, loan=>$loan, inlibrary=>$inlibrary,return=>$return, reserve=>$reserve,renewal=>$renewal,ebRead=>$ebRead,ebPreview=>$ebPreview,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'};
            $ebPreview= $sttcsTbl{$group}{'periodOf'}{$period}{'ebPreview'};
            $ebRead= $sttcsTbl{$group}{'periodOf'}{$period}{'ebRead'};
            $total  = $loan + $inlibrary + $return + $reserve + $renewal+$ebPreview+$ebRead;
            my ($date,$daily)=(undef,undef);
            if($periodicalType eq 'daily'){
                $date=$period,
                $daily=1;
            }
            $periodName=formatPeriod($periodicalType,$period);
            push @retTbl , {period=>$periodName, loan=>$loan,inlibrary=>$inlibrary, return=>$return, reserve=>$reserve,renewal=>$renewal,ebRead=>$ebRead,ebPreview=>$ebPreview,total=>$total,date=>$date,daily=>$daily};

            foreach my $deweyGroup (sort keys (%{$sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}})){
                next if($deweyGroup eq'') ;
                $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'};
                $ebPreview= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'ebPreview'};
                $ebRead= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'ebRead'};
                $prefix= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'prefix'};
                $dewey= $sttcsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'dewey'};
                $total  = $loan + $inlibrary + $return + $reserve + $renewal +$ebPreview+$ebRead;
               push @retTbl , {prefix=>$prefix,dewey=>$dewey, loan=>$loan,inlibrary=>$inlibrary, return=>$return, reserve=>$reserve,renewal=>$renewal,ebRead=>$ebRead,ebPreview=>$ebPreview,total=>$total};
            }
            
        }
    }
    return @retTbl;

}
#########################################################################################

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 = '') ";
    }
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    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 
    _getCircStat_table2($dbh, $template, $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;
    $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 = '') ";
    }
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    my $query = $dbh->prepare($queryStr); 
    $query->execute($dateFrom, $dateTo);
    my ($rec) = $query->fetchrow_array;
    
    if($isInLibrary && $isInLibrary == 1){
        $template->param(totalInlibrary =>int($rec),
                         avgInlibraryDaily =>int($rec/ $numOfWorkDay), # average loan -- daily
                         avgInlibraryMonthly =>int($rec/ $numOfMonth)  # average loan -- monthly
                        );
    }
    else{
        $template->param(totalLoan =>int($rec),
                         avgLoanDaily =>int($rec/ $numOfWorkDay), # average loan -- daily
                         avgLoanMonthly =>int($rec/ $numOfMonth)  # average loan -- monthly
                        );
    }
}
#########################################################################################
sub  _getCircStat_eBook{  
   my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt,$ebStatsType) =@_; 
   my $queryStr;
   $queryStr = "select count(distinct id) from opl_ebReadingStats e inner join opl_item i on e.rid = i.rid
                inner join opl_marcRecord m on m.rid= i.rid 
                where e.type='$ebStatsType' &&  onDate >= ? && onDate <= ? ";
  
    if($incExcOpt eq 'exclusion'){
       $queryStr .= "  && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    
    my $query = $dbh->prepare($queryStr); 
    $query->execute($dateFrom, $dateTo);
    my ($rec) = $query->fetchrow_array;
    
    if($ebStatsType && $ebStatsType eq 'read'){
        $template->param(totalebRead =>int($rec),
                         avgebReadDaily =>int($rec/ $numOfWorkDay), # average eBook read -- daily
                         avgebReadMonthly =>int($rec/ $numOfMonth)  # average eBook read -- monthly
                        );
    }
    elsif($ebStatsType && $ebStatsType eq 'preview'){
        $template->param(totalebPreview =>int($rec),
                         avgebPreviewDaily =>int($rec/ $numOfWorkDay), # average eBook  preview -- daily
                         avgebPreviewMonthly =>int($rec/ $numOfMonth)  # average eBook  preview -- monthly
                        );
    }
    $query->finish;
}

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

sub  _getCircStat_table2
{  
   my ($dbh, $template, $dateFrom, $dateTo,$incExcOpt) =@_; 
   my ($query,$rec,$queryStr);
    ########## TOTAL loan :  $isInLibrary=0
    _getCircStat_loanOrInLibrary($dbh, $template, $dateFrom, $dateTo,$incExcOpt,0);

    ########## TOTAL inlibrary :  $isInLibrary=1
    _getCircStat_loanOrInLibrary($dbh, $template, $dateFrom, $dateTo,$incExcOpt,1);

    ########## 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 = '') ";
    }
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    $template->param(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;
    $template->param(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 = '') ";
    }
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom, $dateTo);
    ($rec) = $query->fetchrow_array;
    
    $template->param(totalReserve =>int($rec));

    ########## TOTAL eBook Read/Preview
   _getCircStat_eBook($dbh, $template, $dateFrom, $dateTo,$incExcOpt,'read');
    _getCircStat_eBook($dbh, $template, $dateFrom, $dateTo,$incExcOpt,'preview');



    # average reserve -- daily
    $template->param(avgReserveDaily =>int($rec/ $numOfWorkDay));
    
    # average reserve -- monthly 
    $template->param(avgReserveMonthly =>int($rec/ $numOfMonth));
   
}
#########################################################################################
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 = '') ";
    }  
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    $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 = '') ";
    }    
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    $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 = '') ";
    }    
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    $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 = '') ";
    }    
   elsif($incExcOpt eq 'illTempOnly'){
       $queryStr .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    $query = $dbh->prepare($queryStr);
    $query->execute();
    ($rec) = $query->fetchrow_array;
    $template->param(NumOfHolds => $rec);

    $query->finish;

}


__END_OF_FILE: