#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

use Opals::Constant;

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);

use Opals::Date qw(
    date_text
);

use Opals::Equipment qw(
    
    eq_item_findByBarcode 

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

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

my $cgi = CGI->new;
my $input = $cgi->Vars();


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'eqmnt/report/circStatsByEqmnt.tmpl',
                reqPermission   => 'eq_report',
            }
    );

my $pref = tmpl_preference($dbh);
my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sortOrder   = $input->{'sortOrder'};
my $pRange = 5;
my $pSize = 20;
my $curPage = $input->{'pNum'};
my $offset;
    ($curPage && $curPage >= 1) ||($curPage = 1 );
    $offset = ($curPage - 1 ) * $pSize;
 
    if (!$sort1){
        $sort1 = "title";
    }
    if (!$sortOrder){
        $sortOrder    = '2';
    }

    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $todayStr;
    }
    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }
=item    
    $template->param(
        dateFrom    => substr($dateFrom,0,10),
        dateTo      => substr($dateTo,0,10),
    );
=cut    
    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
            dateFrom    => substr($dateFrom,0,10),
            dateTo      => substr($dateTo,0,10),

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

    my $sortAttr = 'name';
    if ($sort1 eq 'recordId'){
        $sortAttr = 'recordId';
    }
    elsif ($sort1 eq 'count'){
        $sortAttr = 'count';    
    }
    my ($resultSize, $resultList) = getList($dbh, $offset, $pSize,  $dateFrom,  $dateTo, $sortAttr ,$sortOrder);
    my @rangedPageList = tmpl_rangedPageList($resultSize, $curPage, $pSize, $pRange);
    $template->param(
        resultList      => $resultList,
        resultSize      => $resultSize,
        pNum            => $curPage,
        sort1           => $sort1,
        sortOrder       => $sortOrder,
        sortOrderReverse => ($sortOrder && $sortOrder == 1)  ? 2 : 1,
        sortRecordId    => ($sortAttr eq 'recordId') ? 1 : 0,
        sortName        => ($sortAttr eq 'name')    ? 1 : 0,
        sortCount       => ($sortAttr eq 'count')  ? 1 : 0,
        sortDown        => ($sortOrder && $sortOrder == 1)  ? 1 : 0,
        rangedPageList  =>  \@rangedPageList,
   );
tmpl_write($dbh, $cgi, $cookie, $template);

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

sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}

sub getList{
    my ($dbh,  $offset, $pSize, $dateFrom,$dateTo,$sortAttr,$sortOrder) = @_;
    my @circList = ();
    my $sqlCount = " select count( distinct i.rid)
            from eq_items i inner join eq_records r using(rid)
            inner join eq_loan l on l.barcode = i.barcode 
            where l.dateLoan between '$dateFrom' and  '$dateTo' && i.barcode not regexp '^\_\_\_' ";

   my $sql = " select i.rid,r.rname,rf.fValue as model,count( i.rid) as count, sum(l.renewalCount) as renewalCount
            from eq_items i inner join eq_records r using(rid)
            inner join eq_loan l on l.barcode = i.barcode
            left outer join ( eq_def d inner join eq_recordFields rf on d.id = rf.fId && d.name regexp 'model\$' ) on rf.rid = r.rid
            where l.dateLoan between '$dateFrom' and  '$dateTo' && i.barcode not regexp '^\_\_\_' 
            group by i.rid ";     

    if ($sortAttr =~ m/^name/i){
        $sql .= " order by r.rname";
    } 
    elsif ($sortAttr =~ m/recordId/i){
        $sql .= " order by r.rid";
    }
    elsif ($sortAttr =~ m/count/i){
        $sql .= " order by count";
    }
    else{
         $sql .= " order by r.rname";
    }

 if ($sortOrder && $sortOrder ==2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    my @param = ();
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef,  @param);
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );
    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    $sql .= " LIMIT $offset , $availRange ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while(my ($rid,$rname,$model,$count,$renewalCount) = $sth->fetchrow_array()){
        push @circList, {
             rid        => $rid,
             rname      => $rname,
             model      => $model,
             count      => $count,
             renewalCnt => $renewalCount,
             totalCnt   => $count + $renewalCount,
        };
    }         
    $sth->finish;
    #$sth_user->finish;
    return ($resultSize, \@circList);
}