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

use Opals::Tb_Record qw(

    tb_record_findByRId_brief

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'txtbk/report/circStats.tmpl',
            reqPermission   => 'tb_report',
        }
);
my %statsTbl;
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);
my $sort  = $input->{'sort'};
    $sort = 'title' if (!$sort || $sort eq "");
    

my $sDirection = $input->{'sDirection'};
    $sDirection='asc' if(!$sDirection || $sDirection eq '');

my $pagesize = 20;
my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

my $op = $input->{'op'};

$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 ($op =~ m/itemCirc/i){

        getStatistics($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender);
        getCircStatistics($dbh,$template, $dateFrom, $dateTo);
        getExtraCurrentCircStat($dbh,$template, $dateFrom, $dateTo);
        my @reportTbl = createReportTbl();
        $template->param(
            periodicalType  => $periodicalType,
            groupType       => $groupType,
            groupGender     => $groupGender,
            subDivBy        => $subDivBy,
            Items           => \@reportTbl,
            itemCirc        => 1,
            op              => "itemCirc",
            report          => 1,
        );
    }
    elsif($op =~ m/itemNoCirc/i){
        my $itemNoCircList  = getItemNoCircList($dbh,$dateFrom,$dateTo,$sort,$sDirection,$pNum,$pagesize);
        makePages($dbh,$input,$template,$pagesize,$dateFrom,$dateTo);
        $template->param( 
                periodicalType  => $periodicalType,
                groupType       => $groupType,
                groupGender     => $groupGender,
                subDivBy        => $subDivBy,
                itemNoCirc      => 1,
                op              => "itemNoCirc",
                report          => 0,
                Items           => $itemNoCircList, 
                pNum            => $pNum,
                sort            => $sort,
                sDirection      => $sDirection,
                tb_itemNoCirc  => 1,
       );
    }
    else{
        $template->param( 
                itemNoCirc  => 0,
                itemCirc    => 0,
                op          => "",
        );
    }

    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",
       );
    }

tmpl_write($dbh, $cgi, $cookie, $template);
$dbh->disconnect();
    
#########################################################################################
sub getStatistics{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $SQL = {
            loan    =>build_SQL_loan($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender),
            return  =>build_SQL_return($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender), 
            renewal =>build_SQL_renewal($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender), 
            reserve =>build_SQL_reserve($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender), 
            hold    =>build_SQL_hold($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender), 
        };

    my $n = 0;
    foreach my $circType (keys (%$SQL)){
        $n = 0;
        my ($period);
        my $query = $dbh->prepare($SQL->{$circType});
        $query->execute();
        if($subDivBy =~ m/itemtype/i){
            while(my $rec = $query->fetchrow_hashref){
                $n = $rec->{'n'}  ?  $rec->{'n'} : 0 ;
                $period = inPeriodOf($rec->{'date'});
                add2StatsTbl($rec->{'groupType'},$rec->{'circCode'}, $period,$circType, $n);
            }
        }
        else{
            while(my $rec = $query->fetchrow_hashref){

                $n = $rec->{'n'} ?  $rec->{'n'} : 0; 
                if ($circType =~ m/reserve|hold/i){
                }
                $period = inPeriodOf($rec->{'date'});
                add2StatsTbl($rec->{'groupType'},'',$period,$circType, $n);
            }
        }
        $query->finish; 
    }
}

#########################################################################################
sub build_SQL_loan{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateLoan as date, t.id as circCode , 1 as n ";
        $joinCond = " ((tb_items as i inner join opl_itemType as t on i.typeId = t.id inner join opl_itemCategory ic on ic.id = t.itemCategory && ic.description regexp 'textbook') 
                        inner join tb_loan as l using(barcode))";
    }
    else{
        $queryStr = "select dateLoan as date, 1 as n ";
        $joinCond = " (tb_loan as l inner join tb_items as i using (barcode))";
    }
    if ($groupType eq "grade"){
        $queryStr .= " ,concat('Grade: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "homeroom"){
         $queryStr .= " ,concat('Homeroom: ', lower(homeroom)) as groupType ";
         $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "teacher"){
        $queryStr .= " ,concat('Teacher: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(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(uid) ";
        }
    }
    my $whereStr = " where dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.uid > 0 ";
    $queryStr .= " from " . $joinCond . $whereStr;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender ";
    }
    return $queryStr;
}
#########################################################################################
sub build_SQL_return{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateReturn as date, t.id as circCode , 1 as n ";
        $joinCond = " ((tb_items as i inner join opl_itemType as t on i.typeId = t.id) inner join tb_loan as l using(barcode))";
    }
    else{
        $queryStr = "select dateReturn as date , 1 as n ";
        $joinCond = " ( tb_loan as l left join tb_items as i using (barcode))";
    }
    if ($groupType eq "grade"){
        $queryStr .= " ,concat('Grade: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "homeroom"){
         $queryStr .= " ,concat('Homeroom: ', lower(homeroom)) as groupType ";
         $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "teacher"){
        $queryStr .= " ,concat('Teacher: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(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(uid) ";
        }
    }
    my $whereStr = " where dateReturn >= '$dateFrom' && dateReturn <= '$dateTo' && l.uid > 0 ";
    $queryStr .= " from " . $joinCond . $whereStr;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender ";
    }
    return $queryStr;
}
#########################################################################################
sub build_SQL_renewal{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateRenewal as date, t.id as circCode ";
        $joinCond = " ((tb_items as i inner join opl_itemType as t on i.typeId = t.id) inner join tb_loan as l using(barcode))";
    }
    else{
        $queryStr = "select dateRenewal as date ";
        $joinCond = " ( tb_loan as l left join tb_items as i using (barcode))";
    }
    if ($groupType eq "grade"){
        $queryStr .= " ,concat('Grade: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "homeroom"){
         $queryStr .= " ,concat('Homeroom: ', lower(homeroom)) as groupType ";
         $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "teacher"){
        $queryStr .= " ,concat('Teacher: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(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(uid) ";
        }
    }
    my $whereStr = " where dateRenewal >= '$dateFrom' && dateRenewal <= '$dateTo' && l.uid > 0 ";
    $queryStr .= " from " . $joinCond . $whereStr;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender ";
    }
    return $queryStr;
}
#########################################################################################
sub build_SQL_reserve{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $queryStr="";
    my $joinCond="";
    my $groupBy = "";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select distinct r.id, dateReserve as date, t.id as circCode, r.numCopyReserve as n ";
        $joinCond = " (tb_reserve as r inner join tb_items as i using(rid) inner join opl_itemType as t on i.typeId = t.id )";
        $groupBy = "  group by r.id, r.id, r.uid";
    }
    else{
        $queryStr = "select distinct r.id, dateReserve as date, i.typeId as circCode, r.numCopyReserve as n ";
        $joinCond = " ( tb_reserve as r inner join tb_items as i using(rid))";
        $groupBy = "  group by r.id, r.rid, r.uid";
    }
    if ($groupType eq "grade"){
        $queryStr .= " ,concat('Grade: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "homeroom"){
         $queryStr .= " ,concat('Homeroom: ', lower(homeroom)) as groupType ";
         $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "teacher"){
        $queryStr .= " ,concat('Teacher: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(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(uid) ";
        }
    }
    my $whereStr = " where dateReserve >= '$dateFrom' && dateReserve <= '$dateTo' ";
    $queryStr .= " from " . $joinCond . $whereStr . $groupBy ;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender group by r.id";
    }
    $queryStr .= " having n > 0 ";
    #$queryStr .= ' UNION ' . build_SQL_hold($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender);
    
    return $queryStr;

}

sub build_SQL_hold{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender) = @_;
    my $queryStr="";
    my $joinCond="";
    my $groupBy = "";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select distinct r.id, dateReserve as date, t.id as circCode,  1 as n ";
        $joinCond = "tb_reserve r inner join tb_hold h on h.idReserve = r.id inner join tb_items i using(rid) inner join opl_itemType t on i.typeId = t.id ";
        $groupBy = "  group by r.id, id, date";
    }
    else{
        $queryStr = "select distinct r.id, dateReserve as date, i.typeId as circCode, 1 as n  ";
        $joinCond = " ( tb_reserve r inner join eq_hold h on h.idReserve = r.id inner join tb_items as i using(rid))";
        $groupBy = "  group by r.id, rid, date";
    }
    if ($groupType eq "grade"){
        $queryStr .= " ,concat('Grade: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "homeroom"){
         $queryStr .= " ,concat('Homeroom: ', lower(homeroom)) as groupType ";
         $joinCond .= " inner join opl_user as u using(uid) ";
    }
    elsif ($groupType eq "teacher"){
        $queryStr .= " ,concat('Teacher: ', grade) as groupType ";
        $joinCond .= " inner join opl_user as u using(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(uid) ";
        }
    }
    my $whereStr = " where dateHold >= '$dateFrom' && dateHold <= '$dateTo' ";
    $queryStr .= " from " . $joinCond . $whereStr . $groupBy ;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender group by r.id";
    }
    return $queryStr;
}

#########################################################################################
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);
        ($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 add2StatsTbl {

    my ($group,$dewey, $period,$circType,$n) = @_;
    if ($group eq '' || ! defined $group){
        $group='none';
    }
    $group =~ s/ +$//g;
    $group =~ s/:$/: \[blank\]/;
    if (exists $statsTbl{$group}){
        $statsTbl{$group}{$circType} += $n;
    }
    else{
        $statsTbl{$group} = {loan=>0, return=>0,reserve=>0,renewal=>0,hold=>0 };
        $statsTbl{$group}{$circType} = $n;
    }
    if (exists $statsTbl{$group}{'periodOf'}{$period}){
        $statsTbl{$group}{'periodOf'}{$period}{$circType} += $n;
    }
    else{
        $statsTbl{$group}{'periodOf'}{$period} = {loan=>0, return=>0,reserve=>0,renewal=>0, hold=>0 };
        $statsTbl{$group}{'periodOf'}{$period}{$circType} = $n;
    }
    if($dewey ne ''){
        if(exists $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}){
            $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{$circType} +=$n;
        }
        else{
            $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}={loan=>0, reserve=>0,return=>0,renewal=>0, hold=>0};
            $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{$circType} =$n;
        }
    }

}

sub createReportTbl {
    
    my @retTbl=();
    my ($loan, $return, $reserve, $renewal,$hold, $total) = (0,0,0,0,0,0);
    my $periodName = "";
    foreach my $group (sort keys (%statsTbl)){
        if ($group ne 'none'){
            $loan   = $statsTbl{$group}{'loan'};
            $return = $statsTbl{$group}{'return'};
            $renewal = $statsTbl{$group}{'renewal'};
            $reserve = $statsTbl{$group}{'reserve'};
            $hold   = $statsTbl{$group}{'hold'};
            $total = $loan + $return + $renewal + $reserve ;
            push @retTbl, {UserGroup=>$group,loan=>$loan,return=>$return,reserve=>$reserve,hold=>$hold, renewal=>$renewal, total=>$total};
        }
        foreach my $period(sort keys(%{$statsTbl{$group}{'periodOf'}})){
            $loan   = $statsTbl{$group}{'periodOf'}{$period}{'loan'};
            $return = $statsTbl{$group}{'periodOf'}{$period}{'return'};
            $renewal= $statsTbl{$group}{'periodOf'}{$period}{'renewal'};
            $reserve = $statsTbl{$group}{'periodOf'}{$period}{'reserve'};    
            $hold = $statsTbl{$group}{'periodOf'}{$period}{'hold'};
            $periodName = formatPeriod($periodicalType, $period);
            $total = $loan + $return + $renewal + $reserve;
            my ($date,$daily) = (undef,undef);
            if ($periodicalType eq 'daily'){
                $date = $period;
                $daily = 1;
            }
            push @retTbl, {period=>$periodName,loan=>$loan,return=>$return,reserve=>$reserve,hold=>$hold,renewal=>$renewal,total=>$total,date=>$date};
            
            foreach my $deweyGroup (sort keys (%{$statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}})){
                $loan   = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'loan'};
                $return = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'return'};
                $reserve= $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'reserve'};
                $hold = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'hold'};
                $renewal= $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'renewal'};
                $total  = $loan + $return + $reserve + $renewal;
                push @retTbl , {dewey=>$deweyGroup, loan=>$loan, return=>$return, reserve=>$reserve,hold=>$hold,renewal=>$renewal,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 getCircStatistics {
    my ($dbh,$template, $dateFrom, $dateTo) = @_;
    _getCircStat_table2($dbh,$template, $dateFrom, $dateTo);
    _getCircStat_table3($dbh,$template, $dateFrom, $dateTo);


}
sub _getCircStat_table2 {

    my ($dbh,$template, $dateFrom,$dateTo) = @_;
    my ($query,$rec,$queryStr);
    ###---TOTAL LOAN---###
    $queryStr = "select count(distinct l.id) as count from tb_loan l 
                    inner join tb_items i using (barcode) 
                 where dateLoan >= '$dateFrom'  && dateLoan <= '$dateTo'";
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $rec = $query->fetchrow_array;
    $template->param(
        totalLoan       => int($rec),
        avgLoanDaily    => int($rec / $numOfWorkDay),
        avgLoanMonthly  => int($rec / $numOfMonth)
    );
    ###---TOTAL OVERDUE---###
    $queryStr = "select count(*) from tb_loan 
                where dateDue < now() && dateReturn is null && dateLoan>= ? && dateLoan <= ?";
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom,$dateTo);
    $rec  = $query->fetchrow_array;
    $template->param(
        totalOverDue    => int($rec),
    );
    ###---TOTAL RESERVE---###
=item    
    $queryStr = "select (t1.count + t2.count) as total from 
                    (select sum(numCopyReserve) as count from eq_reserve 
                        where dateReserve >= '$dateFrom'  && dateReserve <= '$dateTo' ) as t1 ,
                     (select count(*) as count from eq_reserve r inner join eq_hold h on r.id = h.idReserve
                        where r.dateReserve >= '$dateFrom' && r.dateReserve <= '$dateTo') as t2  ";
=cut

    $queryStr = " select numCopyReserve as count from tb_reserve r inner join tb_items as i on i.rid = r.rid  inner join opl_itemType t on i.typeId = t.id
                   where dateReserve >= '$dateFrom'  && dateReserve <= '$dateTo'  && r.numCopyReserve > 0 group by r.id, r.rid, r.uid ";                      
    $query = $dbh->prepare($queryStr);
    $rec = 0;
    $query->execute();
    while ( my $count  = $query->fetchrow_array()) {
        $rec = $rec + $count;    
    }
    $template->param(
        totalReserve    => int($rec),
        avgReserveDaily => int($rec / $numOfWorkDay),
        avgReserveMonthly=>int($rec / $numOfMonth)
    );
     ###---TOTAL HOLD---###
    $queryStr = "select count(distinct h.id) as count from tb_reserve r inner join tb_hold h on r.id = h.idReserve
                 inner join tb_items as i on i.rid = r.rid  inner join opl_itemType t on i.typeId = t.id 
                 where h.dateHold >= '$dateFrom' && h.dateHold <= '$dateTo' ";
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $rec  = $query->fetchrow_array;
    $template->param(
        totalHold    => int($rec),
    );
    $query->finish;
}

sub _getCircStat_table3 {
    my ($dbh,$template,$dateFrom,$dateTo) = @_;
    
    ### USER status
    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);

    ### Items Status

    my $totalItems         = getAddedItemStats($dbh);
    my $numActiveItems     = getLoanStats($dbh,$dateFrom, $dateTo);
    my $totalActiveItems   = getLoanStats($dbh);
    my $totalInactiveItems = $totalItems - $totalActiveItems ;
    my $itemAddedPeriodSel =  getAddedItemStats($dbh, $dateFrom,$dateTo);
    $template->param(totalOfItems       =>$totalItems);
    $template->param(numActiveItems         =>$numActiveItems);
    $template->param(totalActiveItems   =>$totalActiveItems);
    $template->param(totalInactiveItems =>$totalInactiveItems);
    $template->param(itemAddedPeriodSel =>$itemAddedPeriodSel);
    $template->param(numActiveItems     =>$numActiveItems);

    ### Title Status
    my $totalTitles = getTitleStats($dbh);
    my $totalActiveTitles = getTitleStats($dbh, 'active');
    my $activeTitlesSel = getTitleStats($dbh, 'active',$dateFrom, $dateTo );
    my $totalInactiveTitles =  $totalTitles - $totalActiveTitles;
    $template->param(totalTitles => $totalTitles);
    $template->param(totalActiveTitles => $totalActiveTitles);
    $template->param(totalInactiveTitles => $totalInactiveTitles);
    $template->param(activeTitlesSel => $activeTitlesSel);

    my $totalDeletions = getDeletionStats($dbh);
    $template->param(totalDeletions => $totalDeletions);

    my $totalAdditions = getAdditionStats($dbh);
    my $additionsSel = getAdditionStats($dbh ,$dateFrom, $dateTo);
    $template->param(totalAdditions => $totalAdditions);
    $template->param(additionsSel => $additionsSel);
    
    my $lostSel = getLostDamagedStats($dbh, 3, ,$dateFrom, $dateTo);
    my $totalLost = getLostDamagedStats($dbh, 3);
    $template->param(lostSel=> $lostSel);
    $template->param(totalLost => $totalLost);

    my $damagedSel = getLostDamagedStats($dbh, 2, ,$dateFrom, $dateTo);
    my $totalDamaged = getLostDamagedStats($dbh, 2);
    $template->param(damagedSel=> $damagedSel);
    $template->param(totalDamaged => $totalDamaged);

}

sub getUserStats {

    my ($dbh,$status,$dateFrom,$dateTo) = @_;
    my $sql="";
    if ($status =~ m/^active/i){
        $sql = "select count(distinct l.uid) from tb_loan l inner join opl_user u using(uid) where u.status = 1";
        if ($dateFrom && $dateTo){
            $sql .= " && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'";
        }
    }
    else{
        $sql = "select count(uid) from opl_user where status = 1";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;


}

sub getAddedItemStats {

     my ($dbh,$dateFrom,$dateTo) = @_;
     my $sql="select count(distinct barcode) from tb_items where barcode not regexp '^\_\_\_' ";
     if ($dateFrom && $dateTo){
            $sql .= " && importDate >= '$dateFrom' && importDate <= '$dateTo'";
     }
   
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;


}

sub getLoanStats {

    my ($dbh,$dateFrom,$dateTo) = @_;
    my $sql = "select count(distinct barcode) from tb_loan ";
    if ($dateFrom && $dateTo){
        $sql .= " where dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;



}
sub getTitleStats {
    my ($dbh,$status,$dateFrom,$dateTo) = @_;
    my $sql = "select count(distinct r.rid) from tb_items i inner join tb_records r on r.rid = i.rid && r.fId = '005' && r.deleted = '0' ";
    if ($status && $status =~ m/^active/i) {
        $sql .= "inner join tb_loan l on i.barcode = l.barcode ";
    }
    if ($status && $dateFrom && $dateTo){
        $sql .= " where l.dateLoan >= '$dateFrom' && l.dateLoan <= '$dateTo'";
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;

}

sub getDeletionStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $sql="select count(distinct barcode) from tb_items where barcode  regexp  '^\_\_\_' ";
    my $sth=$dbh->prepare($sql);
    $sth->execute();
    my ($ret)= $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getAdditionStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $sql="select count(distinct barcode) from tb_items where not barcode regexp  '^\_\_\_' ";
    if ($dateFrom && $dateTo){
        $sql .= " && importDate >= '$dateFrom' && importDate <= '$dateTo'";
    }
    my $sth=$dbh->prepare($sql);
    $sth->execute();
    my ($ret)= $sth->fetchrow_array;
    $sth->finish;
    return $ret;


}
sub getLostDamagedStats{
    my($dbh, $status,$dateFrom,$dateTo)=@_;

    my $sql="select count(distinct barcode) from tb_itemStatus where status = ? ";
    if ($dateFrom && $dateTo){
        $sql .= " && ondate >= '$dateFrom' && onDate <= '$dateTo'";
    }
    my $sth=$dbh->prepare($sql);
    $sth->execute($status);
    my ($ret)= $sth->fetchrow_array;
    $sth->finish;
    return $ret;


}

sub getExtraCurrentCircStat {

    my ($db, $template, $dateFrom, $dateTo) = @_;
    my ($query, $ret, $queryStr);
    #most loan
    $queryStr = "select count(i.rid) as loans, i.rid as rid from tb_loan l inner join tb_items i using(barcode)   
                where l.dateLoan >= '$dateFrom' && l.dateLoan <='$dateTo'
                group by i.rid order by loans desc limit 1 ;";
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $ret = $query->fetchrow_hashref;
    my $rec = tb_record_findByRId_brief($dbh, $ret->{'rid'});
    my $title ;
    if ($ret && $rec){
        $title = $rec->{'245_a'};
    }
    $query->finish;
    $template->param(
            maxLoan     => $ret->{'loans'},
            maxName    => $title,
    );
    #loans overdue

    $queryStr = "select count(distinct l.id) as loans from tb_loan l inner join tb_items using(barcode)  inner join tb_records r using(rid) 
                where l.dateReturn is null ";
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $ret = $query->fetchrow_hashref;
    $query->finish;
    $template->param(
            nLoans    => $ret->{'loans'} ? $ret->{'loans'} : 0 ,
    );

    #nReserves
    $queryStr = " select   sum(r.numCopyReserve) as reserves from  tb_reserve r 
        			inner join tb_items i on i.rid = r.rid
                where r.dateCancel is null and to_days(r.dateExpiry) > to_days(now())";
    
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $ret = $query->fetchrow_hashref;
    $query->finish;
    $template->param(
            nReserves    => $ret->{'reserves'} ?  $ret->{'reserves'} : 0,
    );


    #holds
    $queryStr = " select   count(h.idReserve) as holds
            from  tb_hold h inner join tb_reserve r on r.id =h.idReserve 
			inner join tb_items i on i.rid = r.rid
            where h.dateCancel is null and h.dateLoan is null and to_days(h.dateExpiry) > to_days(now())   ";

    $query = $dbh->prepare($queryStr);
    $query->execute();
    $ret = $query->fetchrow_hashref;
    $query->finish;
    $template->param(
            nHolds    => $ret->{'holds'} ? $ret->{'holds'}: 0  ,
    );
   
}
sub getItemNoCircList {

    my ($dbh, $dateFrom, $dateTo, $sort, $sDir, $pNum, $pagesize ) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

    my @Items = ();
    my $sql = " select i.rid as rid, 
                r1.fVal as title,
                group_concat(r2.fVal order by r2.fVal SEPARATOR '; ') as author,
                group_concat(r3.fVal order by r3.fVal SEPARATOR '; ') as ISBN,
                i.barcode as barcode,  
                i.classNumber as callNumber,
                count(l.barcode)
                from tb_items i
                inner join tb_records r1 on r1.rid = i.rid && r1.fId = '245_a' 
                inner join tb_records r2 on r2.rid = i.rid && r2.fId = '100_a' 
                inner join tb_records r3 on r3.rid = i.rid && r3.fId = '020_a' 
                left outer join tb_loan l on l.barcode = i.barcode
                && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
                where i.barcode not regexp '^\_\_\_'
                group by i.barcode
                having count(l.barcode) = 0
                order by $sort $sDir ";
    $sql .= " limit  $pageoffset, $pagesize ";
    my $query = $dbh->prepare($sql);
    $query->execute();              
    while( my $rec = $query->fetchrow_hashref){
        push @Items, $rec;
    }
    return ( \@Items);
}

sub makePages {

    my ($dbh,$input,$template,$pagesize,$dateFrom,$dateTo) = @_;
    my $sql_count_loan = "
        select  count(distinct i.barcode)
        from tb_records r inner join tb_items i on r.rid = i.rid && r.fId = '005'
        inner join tb_loan l on i.barcode=l.barcode && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
        where i.barcode not regexp '^\_\_\_' 
    ";

    my $sql_count_total = " 
        select  count(distinct i.barcode)
        from tb_records r inner join tb_items i on r.rid = i.rid && i.importDate <= '$dateTo' && r.fId = '005'
        where i.barcode not regexp '^\_\_\_' 
    ";
    my $sql = "select ( " . $sql_count_total  .") - (" .$sql_count_loan . " )";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $countNoLoan = $sth->fetchrow_array;
    my $pNum = $input->{'pNum'};
        $pNum = 1 if (!$pNum);
    my @rangedPageList = tmpl_rangedPageList($countNoLoan, $pNum, $pagesize, 10);
    $template->param(
        countNoLoan     => $countNoLoan,
        rangedPageList  => \@rangedPageList,
    );

}

__END_OF_FILE: