#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;use POSIX qw(
    floor
    ceil
);



use Time::localtime;


use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);


my @fields=qw(
    loan
    inlibrary
    reserve
    renewal
    return
);

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

my $pref = tmpl_preference($dbh);

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

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

my $date   = $input->{'date'}; 
my $type   = $input->{'type'};
my $sortField=$input->{'sortField'}||"callNumber";

my $msgMap =loc_getMsgFile('report/reports.msg',{loanListHeaderTxt      =>{date=>$date},
                                                 returnListHeaderTxt    =>{date=>$date},
                                                 renewalListHeaderTxt   =>{date=>$date},
                                                 reserveListHeaderTxt   =>{date=>$date},
                                                 inlibraryUsedListHeaderTxt=>{date=>$date},
                                                 ebReadListHeaderTxt    =>{date=>$date},
                                                 ebPreviewListHeaderTxt =>{date=>$date}
                                                 });
loc_write($template,$msgMap);
my $sortFieldfMap={
    "barcode"   =>"i.barcode, callNumber, author,titleSort",
    "callNumber"=>"callNumber, author,titleSort",
    "title"     =>"titleSort,callNumber, author",
    "author"    =>"author,callNumber, titleSort",
    "pubName"   =>"pubName, callNumber, author,titleSort",
    "pubPlace"  =>"pubPlace, callNumber, author,titleSort",
    "pubDate"   =>"pubDateSort, callNumber, author,titleSort",
    "barcodeTxt"   =>"i.barcode, callNumber, author,titleSort",
    "callNumTxt"   =>"callNumber, author,titleSort",
    "titleTxt"     =>"titleSort,callNumber, author",
    "authorTxt"    =>"author,callNumber, titleSort",
    "pubNameTxt"   =>"pubName, callNumber, author,titleSort",
    "pubPlaceTxt"  =>"pubPlace, callNumber, author,titleSort",
    "pubDateTxt"   =>"pubDateSort, callNumber, author,titleSort"
    
};
my $headerMap={
    "loan"      =>"loanListHeaderTxt",
    "return"    =>"returnListHeaderTxt",
    "renewal"   =>"renewalListHeaderTxt",
    "reserve"   =>"reserveListHeaderTxt",
    "inLib"     =>"inlibraryUsedListHeaderTxt",
    "ebRead"    =>"ebReadListHeaderTxt",
    "ebPreview" =>"ebPreviewListHeaderTxt"
};

if($date){
    my $circStatsTbl =[];
          #$circStatsTbl =$fnMap->{$type}($dbh,$date,$incExcOpt);
       $circStatsTbl =getStats($dbh,$date,$type,$sortField,$incExcOpt);
       my $pageTitle =$msgMap->{$headerMap->{$type}};
       $template->param(date=>$date,type=>$type,sortField=>$sortField,
                        incExcOpt=>$incExcOpt,
                        pageTitle=>$pageTitle, 
                        circStatsTbl=>$circStatsTbl);

       if($incExcOpt eq 'exclusion'){
            $template->param(exclusion =>1);
        }
        elsif($incExcOpt eq 'inclusion'){
            $template->param(inclusion =>1);
        }
        elsif($incExcOpt eq 'illTempOnly'){
            $template->param(illTempOnly =>1);
        }

   
}
tmpl_write($dbh, $cgi, $cookie, $template);

#########################################################################################
sub getStats{
    my($dbh,$date,$type,$sortField,$incExcOpt)=@_;
   $date=~ s/ \d\d:\d\d:\d\d$//g; 
    my $df="$date 00:00:00";
    my $dt="$date 23:59:59";
    my $ret=[];

  
    my @fields_h=qw(barcodeTxt callNumTxt titleTxt authorTxt pubNameTxt pubPlaceTxt pubDateTxt);
    my $row=[];

    foreach my $f(@fields_h){
        push @$row,{item=>$msgMap->{$f},field=>$f};
    }
    push @$ret,{row=>$row};
    my $query ="";
    my @fields=qw(barcode callNumber title author pubName pubPlace pubDate);
    if($type eq 'reserve'){
         $query ="select idReserve,dateReserve,
                i.barcode,i.callNumber,m.title,m.author,m.pubDate,m.pubPlace,m.pubName
                from opl_reserve as r inner join opl_item i using(rid)  
                inner join opl_marcRecord m using(rid)
                where r.dateReserve  between ? and ?";

    }
    elsif($type eq 'ebRead'){
         $query ="select i.barcode,i.callNumber,m.title,m.author,m.pubDate,m.pubPlace,m.pubName 
                  from opl_item i inner join opl_ebReadingStats e on e.rid=i.rid 
                  inner join opl_marcRecord m on m.rid=e.rid 
                  where  e.type='read' && e.onDate  between ? and ? ";

    }
    elsif($type eq 'ebPreview'){
         $query ="select i.barcode,i.callNumber,m.title,m.author,m.pubDate,m.pubPlace,m.pubName 
                  from opl_item i inner join opl_ebReadingStats e on e.rid=i.rid 
                  inner join opl_marcRecord m  on m.rid=e.rid 
                  where  e.type='preview' && e.onDate  between ? and ? ";

    }
    else{
        $query ="select i.barcode,i.callNumber,m.title,m.author,m.pubDate,m.pubPlace,m.pubName
                from opl_item i inner join opl_loan l using(barcode) 
                inner join opl_marcRecord m using(rid) ";
        if($type eq 'loan'){
              $query .=" where l.dateLoan  between ? and ?";
        }
        if($type eq 'return'){
              $query .=" where l.dateReturn  between ? and ?";
        }
        if($type eq 'renewal'){
              $query .=" where l.dateRenewal  between ? and ?";
        }
        if($type eq 'inLib'){
              $query .=" where l.dateLoan between ? and ? && l.uid=0";
        }
    }

    if(defined $incExcOpt && $incExcOpt eq 'exclusion'){
       $query  .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    elsif($incExcOpt eq 'illTempOnly'){
       $query .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
   $query .=" order by " . $sortFieldfMap->{$sortField};

    
    my $sth=$dbh->prepare($query);
    $sth->execute($df,$dt)   ;
    while( my $rec=$sth->fetchrow_hashref){
        my $row=[];
        foreach my $f(@fields){
            if($f eq 'barcode'){
                $rec->{$f} =~ s/^___|_\d\d\d$//g;
            }
            push @$row,{item=>$rec->{$f}};
        }
        push @$ret,{row=>$row};
    }
    $sth->finish;
   return $ret;
    
}

__END_OF_FILE: