#!/usr/bin/perl

use strict;
use CGI;
use JSON;

use Opals::Context;
use Date::Parse;
use POSIX qw(
    floor
    ceil
    strftime
);
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::Equipment qw(
    eq_getItemBuildingList
);

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

my $cgi = CGI->new;
my $input = {};
my $rs;
my %statsTbl;
my ($dateFrom,$dateTo,$subDivBy,$groupType,$groupGender,$periodicalType,$buildingId,$op);
my ($numOfWorkDay,$numOfMonth);

my $buildingList = eq_getItemBuildingList($dbh);
my @reportTbl;
my $stats;
my ($header,$totalNeverLoaned) = ((),0);
$header = ["manufacuturer","model","serialNumber"];
my $defLang= Opals::Context->preference('lang') || 'en';
if ($ENV{'REQUEST_METHOD'} eq "POST") {
  $input = decode_json($cgi->param('POSTDATA'));
  $dateFrom = $input->{'dateFrom'};
  $dateTo   = $input->{'dateTo'}; 
  $subDivBy = $input->{'type'};
  $groupType = $input->{'user'};
  $groupGender = $input->{'gender'};
  $periodicalType = $input->{'period'}; 
  $buildingId  = $input->{'buildingId'} || "";
  $op = $input->{'op'};
  my $pNum = $input->{'pNum'};
  my $pSize = $input->{'pSize'};
  my $sDirection = $input->{'sDirection'}|| 'asc';
  my $sort = $input->{'sort'} || 'name';
  my $bcList = $input->{'bcList'};
  
  foreach my $bld (@$buildingList){
    $bld->{'selected'} = 0;
    if ($buildingId && $bld->{'id'} eq $buildingId){
      $bld->{'selected'} = 1;
    }
  }
  $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 || $op =~ m/circStats/i){
      getStatistics($groupType, $subDivBy, $periodicalType, $dateFrom, $dateTo,$groupGender,$buildingId);
      $stats->{'summary'} = getCircStatistics($dbh,$dateFrom, $dateTo,$buildingId);
      $stats->{'general'} = getTotalCircStatistic($dbh,$dateFrom, $dateTo,$buildingId);
      $stats->{'current_circulation'} = getExtraCurrentCircStat($dbh,$dateFrom, $dateTo,$buildingId);
      @{$stats->{'circulation'}} = createReportTbl();
  }
  else{
    ($header,$stats) = getItemNoCircList($dbh,$dateFrom,$dateTo,$sort,$sDirection,$pNum,$pSize,$buildingId,$bcList);
    $totalNeverLoaned=getTotalNeverLoaned($dbh,$dateFrom,$dateTo,$buildingId);
  }
} 

print "Content-type: text/plain\n\n";
print to_json({'data'=>$stats,'header'=>$header,'total'=>$totalNeverLoaned});
#----------------------------------------------

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'}||$rec->{'name'};
                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 as rname";
              $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 ";
        $joinCond = " (eq_loan l inner join eq_items i using (barcode) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18 )";
    }
    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: ', teacher) 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.sfValue='$buildingId' ";
    }
    else{
       $queryStr .= " && f.sfValue like '%' ";
    }
    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 as rname ";
        $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 ";
        $joinCond = " (eq_loan l inner join eq_items i using(barcode) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18)";
    }
    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: ', teacher) 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.sfValue='$buildingId' ";
    }
    else{
       $queryStr .= " && f.sfValue like '%' ";
    }
    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 as rname ";
        if ($buildingId && $buildingId ne "") {
          $joinCond = " ((eq_items as i inner join opl_itemType as t on i.typeId=t.id
            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 l inner join eq_items i using (barcode) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18)";
    }
    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: ', teacher) 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.sfValue='$buildingId' ";
    }
    else{
       $queryStr .= " && f.sfValue='%' ";
    }
    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 )";
        $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))";
        $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: ', teacher) 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 .= " && i.buildingCode='$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.name as 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 ";
        $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))";
        $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";
    }
    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\]/;
    $group =~ s/:$/: \[-]/;
    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;
            $period =~ s/\s+/0/g;
            push @retTbl, {period=>$periodName, loan=>$loan, return=>$return,reserve=>$reserve, hold=>$hold, renewal=>$renewal, total=>$total,dateStart=>$period,periodType=>$periodicalType};
            #$period=~ s/ //gc;
=item            
            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};
                    }
                }
            }
=cut            
        }
    }
    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, $dateFrom, $dateTo,$buildingId) = @_;
    return _getCircStat_table2($dbh,$dateFrom, $dateTo,$buildingId);
}

sub _getCircStat_table2 {
    my ($dbh,$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_records r using(rid)
                    left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
                 where dateLoan >= ?  && dateLoan <= ? && 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;
    my $ret;
    $ret = {
        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) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
                where dateDue < now() && dateReturn is null && dateLoan>=? && dateLoan<=? && 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;
    $ret->{'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 opl_itemType t on i.typeId = t.id
        left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
        where dateReserve>='$dateFrom' && dateReserve<='$dateTo' && r.numCopyReserve > 0 group by r.id, r.rid, r.uid && 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;    
    }
    $ret->{'totalReserve'}  = int($rec);
    $ret->{'avgReserveDaily'} = int($rec / $numOfWorkDay);
    $ret->{'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 opl_itemType t on i.typeId = t.id 
                 left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
                 where h.dateHold >= '$dateFrom' && h.dateHold <= '$dateTo' && 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;
    $ret->{'totalHold'}  = int($rec);
    $query->finish;
    return $ret;
}

########## Generate table 3 report
sub getTotalCircStatistic {
    my ($dbh, $dateFrom, $dateTo,$buildingId) = @_;
    my $ret;
    my $activeUsers   = getUserStats($dbh,'active');
    my $activeUsersSel= getUserStats($dbh,'active',$dateFrom, $dateTo);
    my $totalUsers    = getUserStats($dbh,'');
    my $inactiveUsers = $totalUsers - $activeUsers;
    $ret = {
      user_total     => $totalUsers,
      user_active    => $activeUsers,
      user_inactive  => $inactiveUsers,
      user_activeSel => $activeUsersSel,
    };
    my $totalItems         = getAddedItemStats($dbh,$buildingId);
    my $numActiveItems     = getItemLoanStats($dbh,$dateFrom,$dateTo,$buildingId);
    my $totalActiveItems   = getItemLoanStats($dbh,$buildingId);
    my $totalInactiveItems = $totalItems - $totalActiveItems ;
    $ret->{'item_total'} = $totalItems;
    $ret->{'item_activeSel'} = $numActiveItems;
    $ret->{'item_totalActive'} = $totalActiveItems;
    $ret->{'item_totalInactive'} = $totalInactiveItems;
    $ret->{'item_addedPeriodSel'} = getAddedItemStats($dbh,$dateFrom, $dateTo,$buildingId);

    my $totalRecords = getTotalRecords($dbh,'','',$buildingId);
    my $numActiveRecords     = getRecordLoanStats($dbh,$dateFrom,$dateTo,$buildingId);
    my $totalActiveRecords   = getRecordLoanStats($dbh,$dateFrom, $dateTo,$buildingId);
    my $totalInactiveRecords = $totalRecords - $totalActiveRecords ;
    $ret->{'record_total'} = $totalRecords;
    $ret->{'record_totalActive'} = $totalActiveRecords;
    $ret->{'record_activeSel'} = $numActiveRecords;
    $ret->{'record_totalInactive'} = $totalInactiveRecords;
    
    return $ret;
}
sub getAddedItemStats{
    my($dbh,$dateFrom,$dateTo,$buildingId)=@_;
    my $queryStr="select count(distinct barcode) from eq_items i 
      left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
    where substring(barcode, 1, 3) <> '___' ";
    if($dateFrom && $dateTo){
      $queryStr .= " && createdDate >='$dateFrom' && createdDate<='$dateTo'";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " && f.sfValue='$buildingId' ";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    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 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 getItemLoanStats{
    my($dbh,$dateFrom,$dateTo,$buildingId)=@_;
    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 getTotalRecords{
    my($dbh,$dateFrom,$dateTo,$buildingId)=@_;
    my $queryStr="select count(distinct r.rid) from eq_records r inner join eq_items i using(rid) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18 where";
    if($dateFrom && $dateTo){
      $queryStr .= " i.createdDate >='$dateFrom' && i.createdDate<='$dateTo' &&";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " f.sfValue='$buildingId' ";
    }
    else{
      $queryStr .= " f.sfValue like '%' ";
    }
    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}
sub getRecordLoanStats{
    my($dbh,$dateFrom,$dateTo,$buildingId)=@_;
    my $queryStr="select count(distinct i.rid) from eq_loan l inner join eq_items i using(barcode) left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18 where";
    if($dateFrom && $dateTo){
      $queryStr .= " l.dateLoan >='$dateFrom' && l.dateLoan <='$dateTo' && ";
    }
    if ($buildingId && $buildingId ne ""){
      $queryStr .= " f.sfValue='$buildingId' ";
    }
    else{
      $queryStr .= " f.sfValue like '%' ";
    }

    my $query=$dbh->prepare($queryStr);
    $query->execute();
    my ($retval)= $query->fetchrow_array;
    $query->finish;
    return $retval;
}

sub getExtraCurrentCircStat {
    my ($db,$dateFrom, $dateTo) = @_;
    my ($query, $ret, $queryStr);
    my $rs;
    #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_records r on r.rid=i.rid
                left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
                where l.dateLoan >= '$dateFrom' && l.dateLoan <='$dateTo' && 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;
    $rs->{'max_loan'} = $ret->{'loans'};
    $rs->{'max_name'} = $ret->{'rname'} || $ret->{'name'};
 
    #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_records r on r.rid=i.rid
            left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
            where l.dateReturn is null && 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;
    $rs->{'nLoans'} = $ret->{'loans'} ? $ret->{'loans'} : 0;
=item    
    $template->param(
            nLoans    => $ret->{'loans'} ? $ret->{'loans'} : 0 ,
    );
=cut    
    #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 left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18
              where r.dateCancel is null and to_days(r.dateExpiry) > to_days(now()) && 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;
    $rs->{'nReserves'} = $ret->{'reserves'} ?  $ret->{'reserves'} : 0;
=item    
    $template->param(
            nReserves    => $ret->{'reserves'} ?  $ret->{'reserves'} : 0,
    );
=cut
    #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 left outer join eq_itemFields f on i.rid=f.rid and i.iid=f.iid and f.sfId=18 
            where h.dateCancel is null and h.dateLoan is null and to_days(h.dateExpiry)>to_days(now()) && 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;
    $rs->{'nHolds'} = $ret->{'holds'} ? $ret->{'holds'}: 0;
=item    
    $template->param(
            nHolds    => $ret->{'holds'} ? $ret->{'holds'}: 0  ,
    );
=cut    
  return $rs;
}

sub getItemNoCircList {
    my ($dbh, $dateFrom, $dateTo, $sort, $sDir, $pNum, $pagesize,$buildingId,$bcList) = @_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
    my @list = ();
    #my $header =  getHeader($dbh);
    my $rFields = "r.rid,r.rname,r.category,r.container,r.manufacturer,r.model,r.warrantyNote,
      r.manufacturerUrl,r.manualUrl,r.supplierUrl,r.generalNote,r.generalAccessoriesNote";
    my $iFields = "i.rid,i.barcode,i.copyNumber,i.typeId,i.serialNumber,i.purchaseOrder,i.price,i.ipAddress,
      i.macAddress,i.softwareKey,i.cartNumber,i.slotNumber,i.accessories,i.fundingSource,i.districtCode,
      i.buildingCode,i.location,i.dateAcquired,i.districtInventoryNumber,i.statusNote,i.machineName,
      i.vendorName,i.assignedPersonFirstName,i.assignedPersonLastName,i.itemDesignation,i.available,i.note";

    my $itemFieldSelect = "";
    my $sql = "";
    my $bcListStr = "bcListStr";
    if ($buildingId && $buildingId ne "") {
      $sql = " select $rFields,$iFields, count(l.barcode)
                from eq_records r inner join eq_items i using(rid) ";
      $sql .= " left outer join eq_loan l on l.barcode = i.barcode
                && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
                where i.barcode not regexp '^\_\_\_' && i.buildingCode='$buildingId' $bcListStr
                group by i.barcode
                having count(l.barcode) = 0
                order by $sort $sDir ";
      $sql .= " limit  $pageoffset, $pagesize ";
    }else{
      $sql = " select $rFields,$iFields , count(l.barcode)
                from eq_records r inner join eq_items i using(rid)";
        $sql .= " left outer join eq_loan l on l.barcode = i.barcode
                && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
                where i.barcode not regexp '^\_\_\_' $bcListStr
                group by i.barcode
                having count(l.barcode) = 0
                order by $sort $sDir ";
        $sql .= " limit  $pageoffset, $pagesize ";
    }
    my $bcStr;
    if (defined $bcList){
      #foreach my $bc(@{$bcList}){
      #  $bcStr .= "'" . $bc . "'" . ",";
      #}
      #$bcStr =~ s/,\s*//;
		my $bcStr = join ',', map { qq/'$_'/ } @{$bcList};
      $sql =~ s/bcListStr/ && i.barcode in ($bcStr) /gc;
    }
    else{
      $sql =~ s/bcListStr//gc;
    }
    my $query = $dbh->prepare($sql);
    $query->execute();              
    my $curRId = 0;
    my  $rid;
    my $recordInfo ;
    while( my $rec = $query->fetchrow_hashref){
      $rec->{'selected'} = 0;
      push @list, $rec;
    }
    return ($header,\@list);
}


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


    my $sql = "select ( " . $sql_count_total  .") - (" .$sql_count_loan . " )";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}