#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use JSON;
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::Eq_Record qw(
    eq_record_findByRId
);
use Opals::Equipment qw(
    eq_getItemBuildingList
);

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        => 'eqmnt/report/circStats.tmpl',
            reqPermission   => 'eq_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 $buildingId  = $input->{'building'} || "";

my $tm = localtime;
my $dateToday = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

$buildingId = ($buildingId eq 'all' || $buildingId eq "All")?"":$buildingId;
my ($numOfWorkDay,$numOfMonth);
my $sort  = $input->{'sort'};
    $sort = ' rname' 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 $buildingList = eq_building_getList($dbh);
my $buildingList =  eq_getItemBuildingList($dbh) || [];

foreach my $bld (@$buildingList){
  $bld->{'selected'} = 0;
  if ($buildingId && $bld->{'id'} eq $buildingId){
    $bld->{'selected'} = 1;
  }
}
if ($permission && $permission->{'eq_report'}) {
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "2000-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,$buildingId);
        getCircStatistics($dbh,$template, $dateFrom, $dateTo,$buildingId);
        getTotalCircStatistic($dbh,$template, $dateFrom, $dateTo,$buildingId);
        getExtraCurrentCircStat($dbh,$template, $dateFrom, $dateTo,$buildingId);
        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 ($header , $itemNoCircList ) = getItemNoCircList($dbh,$dateFrom,$dateTo,$sort,$sDirection,$pNum,$pagesize);
        makePages($dbh,$input,$template,$pagesize,$dateFrom,$dateTo);
        my ($header , $itemNoCircList ) = getItemNoCircList1($dbh,$dateFrom,$dateTo,$sort,$sDirection,$pNum,$pagesize,$buildingId);
        makePages1($dbh,$input,$template,$pagesize,$dateFrom,$dateTo,$buildingId);
        $template->param( 
                periodicalType  => $periodicalType,
                groupType       => $groupType,
                groupGender     => $groupGender,
                subDivBy        => $subDivBy,
                itemNoCirc  => 1,
                op          => "itemNoCirc",
                report      => 0,
                Items       => $itemNoCircList, 
                header      => $header,
                pNum        => $pNum,
                sort        => $sort,
                sDirection  => $sDirection,
                eq_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",
       );
    }
  $template->param(
    buildingList    => scalar(@{$buildingList})>0?to_json($buildingList):"[]",
    hasMultiBlds    => scalar(@{$buildingList})>0?1:0,
    dateToday       => $dateToday, 
  );
}
tmpl_write($dbh, $cgi, $cookie, $template);
$dbh->disconnect();
    
#########################################################################################
sub getStatistics{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId) = @_;
    my $SQL = {
            loan    =>build_SQL_loan($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId),
            return  =>build_SQL_return($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId), 
            renewal =>build_SQL_renewal($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId), 
            reserve =>build_SQL_reserve($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId),
            hold    =>build_SQL_hold($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId), 
        };
    my $n = 0;
    foreach my $circType (keys (%$SQL)){
        $n = 0;
        my ($period,$rid,$eqName);
        my $query = $dbh->prepare($SQL->{$circType});
        $query->execute();
        if($subDivBy =~ m/itemtype/i){
            my $i = 1;
            while(my $rec = $query->fetchrow_hashref){
                $n = $rec->{'n'}  ?  $rec->{'n'} : 0 ;
                $period = inPeriodOf($rec->{'date'});
                $rid = $rec->{'rid'};
                $eqName = $rec->{'rname'};
                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,$buildingId) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateLoan as date, t.id as circCode , 1 as n , r.rid, r.rname ";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId=t.id inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid 
            inner join eq_records r on i.rid 
          ) inner join eq_loan as l using(barcode))";
        }
        else{
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId = t.id
            inner join eq_records r on i.rid = r.rid 
          ) inner join eq_loan as l using(barcode))";
        }
    }
    else{
        $queryStr = "select dateLoan as date, 1 as n ";
        if ($buildingId && $buildingId ne ""){
          $joinCond = " ( eq_loan l inner join eq_items i using(barcode) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid )";
        }else{
          $joinCond = " ( eq_loan as l inner join eq_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 ";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfId=18 && f.sfValue='$buildingId' ";
    }
    return $queryStr;
}
#########################################################################################
sub build_SQL_return{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateReturn as date, t.id as circCode , 1 as n , r.rid, r.rname ";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId=t.id inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid 
            inner join eq_records r on i.rid 
          ) inner join eq_loan as l using(barcode))";
        }
        else{
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId = t.id
            inner join eq_records r on i.rid = r.rid 
          ) inner join eq_loan as l using(barcode))";
        }
    }
    else{
        $queryStr = "select dateReturn as date , 1 as n ";
        if ($buildingId && $buildingId ne ""){
          $joinCond = " ( eq_loan l inner join eq_items i using(barcode) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid )";
        }else{
          $joinCond = " ( eq_loan as l inner join eq_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 ";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfId=18 && f.sfValue='$buildingId' ";
    }
    return $queryStr;
}

#########################################################################################
sub build_SQL_renewal{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId) = @_;
    my $queryStr="";
    my $joinCond="";
    if ($subDivBy eq "itemtype"){
        $queryStr = "select dateRenewal as date, t.id as circCode, l.renewalCount as n, r.rid, r.rname ";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId=t.id inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid 
            inner join eq_records r on i.rid 
          ) inner join eq_loan as l using(barcode))";
        }
        else{
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId = t.id
            inner join eq_records r on i.rid = r.rid 
          ) inner join eq_loan as l using(barcode))";
        }
    }
    else{
        $queryStr = "select dateRenewal as date,l.renewalCount as n";
        #$joinCond = " ( eq_loan as l left join eq_items as i using (barcode))";
        if ($buildingId && $buildingId ne ""){
          $joinCond = " ( eq_loan l left join eq_items i using(barcode) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid )";
        }else{
          $joinCond = " ( eq_loan l left join eq_items 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' && dateRenewal>='$dateFrom' && dateRenewal<='$dateTo' && l.uid > 0 ";
    $queryStr .= " from " . $joinCond . $whereStr;
    if ($groupGender eq '0' || $groupGender eq '1'){
        $queryStr .= " && gender=$groupGender ";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfId=18 && f.sfValue='$buildingId' ";
    }
    return $queryStr;
}

#########################################################################################
sub build_SQL_reserve{
    my ($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId) = @_;
    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 = " (eq_reserve as r inner join eq_items as i using(rid) inner join opl_itemType as t on i.typeId = t.id )";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " (eq_reserve as r inner join eq_items as i using(rid) 
          inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
          inner join opl_itemType as t on i.typeId = t.id )";
        }
        else{
          #$joinCond = " (eq_reserve as r inner join eq_items as i using(rid) inner join opl_itemType as t on i.typeId = t.id )";
          if ($buildingId && $buildingId ne "") {
            $joinCond = " (eq_reserve as r inner join eq_items i using(rid) 
            inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
            inner join opl_itemType as t on i.typeId = t.id )";
          }
          else{
            $joinCond = " (eq_reserve as r inner join eq_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 = " ( eq_reserve as r inner join eq_items as i using(rid))";
        if ($buildingId && $buildingId ne ""){
          $joinCond = " ( eq_reserve as r inner join eq_items as i using(rid)) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid";
        }
        else{
          $joinCond = " ( eq_reserve as r inner join eq_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";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfId=18 && f.sfValue='$buildingId' ";
    }
    $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,$buildingId) = @_;
    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 , r1.rid, r1.rname ";
        #$joinCond = "eq_reserve r inner join eq_hold h on h.idReserve = r.id inner join eq_items i using(rid) 
        #    inner join eq_records r1 on i.rid = r1.rid
        #    inner join opl_itemType t on i.typeId = t.id ";
        if ($buildingId && $buildingId ne "") {
            $joinCond = "eq_reserve r inner join eq_hold h on h.idReserve = r.id 
            inner join eq_items i using(rid) 
            inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
            inner join eq_records r1 on i.rid = r1.rid
            inner join opl_itemType t on i.typeId = t.id ";
        }
        else{
            $joinCond = "eq_reserve r inner join eq_hold h on h.idReserve = r.id inner join eq_items i using(rid) 
            inner join eq_records r1 on i.rid = r1.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 = " ( eq_reserve r inner join eq_hold h on h.idReserve = r.id inner join eq_items as i using(rid))";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " ( eq_reserve r inner join eq_hold h on h.idReserve = r.id inner join eq_items as i using(rid) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid)";
        }else{
          $joinCond = " ( eq_reserve r inner join eq_hold h on h.idReserve = r.id inner join eq_items as i using(rid))";
        }
        $groupBy = "  group by r.id, r.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";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfId=18 && f.sfValue='$buildingId' ";
    }
    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,$rid,$eqName) = @_;
    
#if ($eqName eq '' || ! defined $eqName){
#        $eqName='N/A';
#    }

    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;
=item                
                if(exists $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{'name'}{$eqName}){    
                    $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{'name'}{$eqName}{$circType} +=$n;
            }
            else{
                $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{'name'}{$eqName}={loan=>0, reserve=>0,return=>0,renewal=>0, hold=>0};
                $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$dewey}{'name'}{$eqName}{$circType} =$n;
            }
=cut            
        }
        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;
            push @retTbl, {period=>$periodName, loan=>$loan, return=>$return,reserve=>$reserve, hold=>$hold, renewal=>$renewal, total=>$total};
            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};
                foreach my $name (sort keys (%{$statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}})){
                    if ($name ne ""){
                    $loan   = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}{$name}{'loan'};
                    $return = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}{$name}{'return'};
                    $reserve= $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}{$name}{'reserve'};
                    $hold = $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}{$name}{'hold'};
                    $renewal= $statsTbl{$group}{'periodOf'}{$period}{'deweyGroup'}{$deweyGroup}{'name'}{$name}{'renewal'};
                    $total  = $loan + $return + $reserve + $renewal;
                    push @retTbl , {dewey=>$name, 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,$buildingId) = @_;
    _getCircStat_table2($dbh,$template, $dateFrom, $dateTo,$buildingId);

}

sub _getCircStat_table2 {
    my ($dbh,$template, $dateFrom,$dateTo,$buildingId) = @_;
    my ($query,$rec,$queryStr);
    ###---TOTAL LOAN---###
    if ($buildingId && $buildingId ne "") {
      $queryStr = "select count(distinct l.id) as count from eq_loan l 
                    inner join eq_items i using (barcode) 
                    inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
                    inner join eq_records r on i.rid=r.rid
                 where dateLoan >= ?  && dateLoan <= ? && f.sfId=18 && f.sfValue='$buildingId'";
    }
    else{
      $queryStr = "select count(distinct l.id) as count from eq_loan l 
                    inner join eq_items i using (barcode) 
                    inner join eq_records r using (rid) 
                 where dateLoan >= ?  && dateLoan <= ?";
    }
    $query = $dbh->prepare($queryStr);
    $query->execute($dateFrom,$dateTo);
    $rec = $query->fetchrow_array;
    $template->param(
        totalLoan       => int($rec),
        avgLoanDaily    => int($rec / $numOfWorkDay),
        avgLoanMonthly  => int($rec / $numOfMonth)
    );
    ###---TOTAL OVERDUE---###
    if ($buildingId && $buildingId ne "") {
      $queryStr = "select count(*) from eq_loan 
                inner join eq_items i using (barcode)
                inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
                where dateDue < now() && dateReturn is null && dateLoan>= ? && dateLoan <= ? && f.sfId=18 && f.sfValue='$buildingId'";
    }
    else{
      $queryStr = "select count(*) from eq_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
    if ($buildingId && $buildingId ne "") {
      $queryStr = " select numCopyReserve as count from eq_reserve r inner join eq_items as i on i.rid = r.rid  
        inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
        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 && f.sfId=18 && f.sfValue='$buildingId'";                      
    }else{
      $queryStr = " select numCopyReserve as count from eq_reserve r inner join eq_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---###
    if ($buildingId && $buildingId ne "") {
      $queryStr = "select count(distinct h.id) as count from eq_reserve r inner join eq_hold h on r.id = h.idReserve
                 inner join eq_items as i on i.rid = r.rid    
                 inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
                 inner join opl_itemType t on i.typeId = t.id 
                 where h.dateHold >= '$dateFrom' && h.dateHold <= '$dateTo' && f.sfId=18 && f.sfValue='$buildingId' ";
    }else{
      $queryStr = "select count(distinct h.id) as count from eq_reserve r inner join eq_hold h on r.id = h.idReserve
                 inner join eq_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 getExtraCurrentCircStat {

    my ($db, $template, $dateFrom, $dateTo) = @_;
    my ($query, $ret, $queryStr);
    #most loan
    if ($buildingId && $buildingId ne "") {
      $queryStr = "select count(r.rid) as loans, r.rname from eq_loan l 
                inner join eq_items i using(barcode) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid 
                inner join eq_records r on r.rid=i.rid
                where l.dateLoan >= '$dateFrom' && l.dateLoan <='$dateTo' && f.sfId=18 && f.sfValue='$buildingId' 
                group by r.rid order by loans desc limit 1 ;";
    }
    else{
      $queryStr = "select count(rid) as loans, r.rname from eq_loan l inner join eq_items using(barcode)  inner join eq_records r using(rid) 
                where l.dateLoan >= '$dateFrom' && l.dateLoan <='$dateTo'
                group by r.rid order by loans desc limit 1 ;";
    }
    $query = $dbh->prepare($queryStr);
    $query->execute();
    $ret = $query->fetchrow_hashref;
    $query->finish;
    $template->param(
            maxLoan     => $ret->{'loans'},
            maxName    => $ret->{'rname'},
    );
    #loans overdue
    if ($buildingId && $buildingId ne "") {
      $queryStr = "select count(l.id) as loans from eq_loan l inner join eq_items i using(barcode) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid 
            inner join eq_records r on r.rid=i.rid
            where l.dateReturn is null && f.sfId=18 && f.sfValue='$buildingId'";
    }else{
      $queryStr = "select count(l.id) as loans from eq_loan l inner join eq_items using(barcode)  inner join eq_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
    if ($buildingId && $buildingId ne "") {
      $queryStr = " select   sum(r.numCopyReserve) as reserves from  eq_reserve r 
        			inner join eq_items i on i.rid = r.rid inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
                where r.dateCancel is null and to_days(r.dateExpiry) > to_days(now()) && f.sfId=18 && f.sfValue='$buildingId' ";
    }else{
      $queryStr = " select   sum(r.numCopyReserve) as reserves from  eq_reserve r 
        			inner join eq_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
    if ($buildingId && $buildingId ne "") {
      $queryStr = " select   count(h.idReserve) as holds
            from  eq_hold h inner join eq_reserve r on r.id =h.idReserve 
			      inner join eq_items i on i.rid = r.rid inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
            where h.dateCancel is null and h.dateLoan is null and to_days(h.dateExpiry) > to_days(now()) && f.sfId=18 && f.sfValue='$buildingId'";
    }else{
      $queryStr = " select   count(h.idReserve) as holds
            from  eq_hold h inner join eq_reserve r on r.id =h.idReserve 
			inner join eq_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 $header = getHeader($dbh);
    my $itemFieldSelect = "";
    my $itemFieldTable = "";
    foreach my $c (@{$header}){
            $itemFieldSelect .= "f".$c->{'id'} . ".sfValue as f". $c->{'id'} ." ,";
            $itemFieldTable .=  " left join eq_itemFields "."f".$c->{'id'} . " on (" . "f".$c->{'id'}.".rid = i.rid && i.iid = "."f".$c->{'id'}.".iid && f". $c->{'id'} . ".sfId = " .  $c->{'id'} . ") ";
        }
    $itemFieldSelect =~ s/,$//;  
    my $sql = " select r.rname as rname, i.rid as rid, i.barcode as barcode, $itemFieldSelect , count(l.barcode)
                from eq_records r inner join eq_items i using(rid)";
        $sql .=  $itemFieldTable;
        $sql .= " left outer join eq_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();              
    my $curRId = 0;
    my  $rid;
    my $recordInfo ;
    while( my $rec = $query->fetchrow_hashref){
        foreach my $c (@{$header}) {
            push @{$rec->{'cols'}}, { value => $rec->{'f'.$c->{'id'}}};
        }
        push @Items, $rec;
    }
    return ($header, \@Items);
}
sub getItemNoCircList1 {
    my ($dbh, $dateFrom, $dateTo, $sort, $sDir, $pNum, $pagesize,,$buildingId) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

    my @Items = ();
    my $header = getHeader($dbh);
    my $itemFieldSelect = "";
    my $itemFieldTable = "";
    foreach my $c (@{$header}){
            $itemFieldSelect .= "f".$c->{'id'} . ".sfValue as f". $c->{'id'} ." ,";
            $itemFieldTable .=  " left join eq_itemFields "."f".$c->{'id'} . " on (" . "f".$c->{'id'}.".rid = i.rid && i.iid = "."f".$c->{'id'}.".iid && f". $c->{'id'} . ".sfId = " .  $c->{'id'} . ") ";
        }
    $itemFieldSelect =~ s/,$//;  
    my $sql = "";
    if ($buildingId && $buildingId ne "") {
      $sql = " select r.rname as rname, i.rid as rid, i.barcode as barcode, $itemFieldSelect , count(l.barcode)
                from eq_records r inner join eq_items i using(rid) inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid";
      $sql .=  $itemFieldTable;
      $sql .= " left outer join eq_loan l on l.barcode = i.barcode
                && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
                where i.barcode not regexp '^\_\_\_' && f.sfId=18 && f.sfValue='$buildingId' 
                group by i.barcode
                having count(l.barcode) = 0
                order by $sort $sDir ";
      $sql .= " limit  $pageoffset, $pagesize ";
    }else{
      $sql = " select r.rname as rname, i.rid as rid, i.barcode as barcode, $itemFieldSelect , count(l.barcode)
                from eq_records r inner join eq_items i using(rid)";
        $sql .=  $itemFieldTable;
        $sql .= " left outer join eq_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();              
    my $curRId = 0;
    my  $rid;
    my $recordInfo ;
    while( my $rec = $query->fetchrow_hashref){
        foreach my $c (@{$header}) {
            push @{$rec->{'cols'}}, { value => $rec->{'f'.$c->{'id'}}};
        }
        push @Items, $rec;
    }
    return ($header, \@Items);
}

sub makePages {

    my ($dbh,$input,$template,$pagesize,$dateFrom,$dateTo) = @_;
    my $sql_count_loan = "
        select  count(distinct i.barcode)
        from eq_records r inner join eq_items i on r.rid = i.rid 
        inner join eq_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 eq_records r inner join eq_items i on r.rid = i.rid && i.createdDate <= '$dateTo' 
        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,
    );
}
sub makePages1 {
    my ($dbh,$input,$template,$pagesize,$dateFrom,$dateTo,$buildingId) = @_;
    my $sql_count_loan = "";
    if ($buildingId && $buildingId ne "") {
      $sql_count_loan = "
        select  count(distinct i.barcode)
        from eq_records r inner join eq_items i on r.rid = i.rid inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
        inner join eq_loan l on i.barcode=l.barcode && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
        where i.barcode not regexp '^\_\_\_' && f.sfId=18 && f.sfValue='$buildingId'   ";
    }else{
      $sql_count_loan = "
        select  count(distinct i.barcode)
        from eq_records r inner join eq_items i on r.rid = i.rid 
        inner join eq_loan l on i.barcode=l.barcode && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
        where i.barcode not regexp '^\_\_\_'     ";
    }
    my $sql_count_total = " ";
    if ($buildingId && $buildingId ne "") {
      $sql_count_total = "
        select  count(distinct i.barcode)
        from eq_records r inner join eq_items i on r.rid = i.rid && i.createdDate <= '$dateTo' inner join eq_itemFields f on f.rid=i.rid && f.iid=i.iid
        where i.barcode not regexp '^\_\_\_'  && f.sfId=18 && f.sfValue='$buildingId' ";
    }else{
      $sql_count_total = "
        select  count(distinct i.barcode)
        from eq_records r inner join eq_items i on r.rid = i.rid && i.createdDate <= '$dateTo' 
        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,
    );
}

sub getHeader {
    my ($dbh) = @_;
    my @header = ();
    my $sql = " select id, name from eq_def where defType = 'item' && display = 1" ; 
    my $query = $dbh->prepare($sql);
    $query->execute();
    while (my $rec = $query->fetchrow_hashref){
        push @header, $rec;
    }
    return \@header;
}


#Added 
#Thu, Sep 29, 2011 @ 14:56:45 EDT 
#
#
sub getTotalCircStatistic {
    my ($dbh,$template, $dateFrom, $dateTo) = @_;
    ########## 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);
    
    my $totalItems         = getAddedItemStats($dbh);
    my $numActiveItems     = getItemLoanStats($dbh,$dateFrom, $dateTo);
    my $totalActiveItems   = getItemLoanStats($dbh);
    my $totalInactiveItems = $totalItems - $totalActiveItems ;
    $template->param(totalOfItems       =>$totalItems);
    $template->param(numActiveItems         => $numActiveItems);
    $template->param(totalActiveItems   => $totalActiveItems);
    $template->param(totalInactiveItems =>$totalInactiveItems);
    $template->param(itemAddedPeriodSel =>getAddedItemStats($dbh,$dateFrom, $dateTo));
    my $totalRecords = getTotalRecords($dbh);
    my $numActiveRecords     = getRecordLoanStats($dbh,$dateFrom, $dateTo);
    my $totalActiveRecords   = getRecordLoanStats($dbh);
    my $totalInactiveRecords = $totalRecords - $totalActiveRecords ;
    $template->param(totalRecords =>$totalRecords);
    $template->param(totalActiveRecords =>$totalActiveRecords);
    $template->param(activeRecordSel =>$numActiveRecords);
    $template->param(totalRecords =>$totalRecords);
    $template->param(totalInactiveRecords => $totalInactiveRecords);
}

sub getUserStats{
    my($dbh,$uStatus,$dateFrom,$dateTo)=@_;
    my $queryStr="";
    if($uStatus eq 'active'){ #find active users
        $queryStr ="select count(distinct l.uid) 
                    from eq_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 getAddedItemStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct barcode) from eq_items where substring(barcode, 1, 3) <> '___' ";
    if($dateFrom && $dateTo){
      $queryStr .= " && createdDate >='$dateFrom' && createdDate<='$dateTo'";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
sub getItemLoanStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct barcode) from eq_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 getRecordLoanStats{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct i.rid) from eq_loan l inner join eq_items i using(barcode) ";
    if($dateFrom && $dateTo){
      $queryStr .= " where l.dateLoan >='$dateFrom' && l.dateLoan <='$dateTo'";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
sub getTotalRecords{
    my($dbh,$dateFrom,$dateTo)=@_;
    my $queryStr="select count(distinct r.rid) from eq_records r inner join eq_items i using(rid)";
    if($dateFrom && $dateTo){
      $queryStr .= " && i.createdDate >='$dateFrom' && i.createdDate<='$dateTo'";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}

sub _eq_building_getList {
    
    my ($dbh) = @_;
    my @list =();
    my $sql = 
    my $sql = (<<_SQL_) ;
select count(*) as count, sfValue as id, l.name as name 
from eq_itemFields f inner join eq_locationDirectory l on (BINARY l.code= BINARY f.sfValue) 
where f.sfId=18 group by f.sfValue 
    having count>0 
    order by l.name
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while(my $rec = $sth->fetchrow_hashref) {
        push @list, $rec;
    }
    $sth->finish;
    if (scalar(@list) <=0 ){
      push @list,{count=>99,id=>'ares',name=>'Amanda Rochell Elem.'};
      push @list,{count=>75,id=>'clpe',name=>'Doris Cullins-Lake Pointe Elem.'};
      push @list,{count=>99,id=>'daj',name=>'Doris A. Jones Elem.'};
      push @list,{count=>99,id=>'oses',name=>'Quida Springer Eleme.'};
    }
    return \@list;
}

__END_OF_FILE: