#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    ceil
);

use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::User qw(
    user_getInformation
);
use Opals::MarcXml qw(
    mxml_delete
);
use Opals::Date qw(
    date_f005
    date_DHM_text
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);


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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $ridList= $input->{'ridList'};
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemStats.tmpl',
            reqPermission   => 'rpt_catRec',
        }
);

    $template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemStats'));
    my $incExcOpt = $input->{'incExcOpt'};
    if(!$incExcOpt || ($incExcOpt ne 'inclusion' && $incExcOpt ne 'illTempOnly' )){
        $incExcOpt='exclusion';
    }
    
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    my $dateFrom= $input->{'statFrom'};
    my $dateTo  = $input->{'statTo'};
    my $sort    = $input->{'sort'};
   
    my $field    = $input->{'fieldOpt'};

    my $dir     = $input->{'dir'};
    $dir       = '' if ( !$dir );
    if ( !$sort ){
        $sort = 'totalCirc' ;
        $dir  ="desc";
    }
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all" || $dateFrom eq ''){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

    my $selectedPrefix=$input->{'prefix'};
    my $selectedItemType=$input->{'itemType'};
    my $selectedRecFormat=$input->{'recFormat'};

    $template->param(statFrom => $dateFrom);
    $template->param(statTo   => $dateTo);
    $template->param(field    => $dateTo);
    $template->param(itemStat => 1);
    $template->param(selectedPrefix => $selectedPrefix);
    $template->param(selectedItemType => $selectedItemType);
    $template->param(selectedRecFormat => $selectedRecFormat);


    my $prefixTbl=getPrefixList($dbh,$selectedPrefix);
    my $itemTypeTbl=getItemTypeList($dbh,$selectedItemType);
    my $recFormatTbl=getRecFormatList($dbh,$selectedRecFormat);
 
    my $op= $input->{'op'};
    if(defined $op && $op eq 'csv'){
        my $recordList = GetRecordStatList($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, 0,$incExcOpt,$selectedPrefix,$selectedItemType,$selectedRecFormat);
        getItemCIrcStats2Export($recordList);
    }
    else{ 
        my $recordList = GetRecordStatList($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize,$incExcOpt,$selectedPrefix,$selectedItemType,$selectedRecFormat);
        MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt,$selectedPrefix,$selectedItemType,$selectedRecFormat);
        $template->param(
            recordList => $recordList,
            sort    => $sort,
            dir    => $dir,
            pNum    => $pNum,
            incExcOpt=> $incExcOpt,
            prefixTbl=>$prefixTbl,
            itemTypeTbl=>$itemTypeTbl,
            recFormatTbl=>$recFormatTbl,
        );

        if($incExcOpt eq 'exclusion'){
            $template->param(exclusion =>1);
        }
        elsif($incExcOpt eq 'inclusion'){
            $template->param(inclusion =>1);
        }
        elsif($incExcOpt eq 'illTempOnly'){
            $template->param(illTempOnly =>1);
        }
        if($dateRange eq 'all'){
            $template->param(
                rangeAll   => 1,
                dateRangeOpt=>"all"
           );
        }
        elsif($dateRange eq 'fYear'){
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeFYear   => 1,
                dateRangeOpt=>"fYear"
           );
        }
        else{
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeSel   => 1,
                dateRangeOpt=>"rangeSel"
           );
        }

        #Tue, Jan 12, 2010 @ 10:31:41 EST
        my $msgValMap ={};
        my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
        my $msgMap2           =loc_getMsgFile('circ/userCircList.msg',$msgValMap);
       
        loc_write($template,$msgMap);
        loc_write($template,$msgMap2);

        tmpl_write($dbh, $cgi, $cookie, $template);
        #$dbh->disconnect();
   }

################################################################################
sub GetRecordStatList {
    my ($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize,$incExcOpt,$selectedPrefix,$selectedItemType,$selectedRecFormat) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

       
    my $sql = "
select    i.rid, i.callNumber,i.typeId ,m.title, m.author,m.recFormat,
          m.pubPlace,m.pubName,m.pubDate,m.pubDateSort,
          count(i.rid) as totalCirc ,m.tempIll,
          l.dateLoan, l.dateDue, l.dateReturn ,f.sf852Data as prefix
from      opl_marcRecord m 
          inner join opl_item i on i.rid=m.rid  
          inner  join opl_loan l on i.barcode=l.barcode  
          left outer join opl_itemInfo f on f.barcode =l.barcode && f.sf852Code='k'
where     l.dateLoan between '$dateFrom' and  '$dateTo' ";
#          && i.barcode not regexp '^\_\_\_' ";

    if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    elsif($incExcOpt eq 'illTempOnly'){
       $sql .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    if(defined $selectedPrefix && $selectedPrefix ne ''){
        $sql .= " &&  f.sf852Data='$selectedPrefix' ";
    }
    if(defined $selectedItemType && $selectedItemType ne ''){
        $sql .= " && i.typeId='$selectedItemType' ";
    }
    if(defined $selectedRecFormat && $selectedRecFormat ne ''){
        $sql .= " && m.recFormat='$selectedRecFormat' ";
    }

    
    $sort =" concat($sort ,'zzzz')" if ( $dir eq ""  ||  $dir eq 'asc' );

    $sql .= " group by i.rid
          order by $sort $dir ";
    
    if($pagesize && $pagesize >0){          
          $sql .= " limit   $pageoffset, $pagesize ";
    }
    my $sth = $dbh->prepare($sql);
#open debug,">/tmp/ss";print debug "[$sql]\n"; close debug;
    $sth->execute();
 
    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $barcode;
    while (my $r = $sth->fetchrow_hashref) {
        my $rec;
        $rec->{'rid'}       = $r->{'rid'};
        $rec->{'typeId'}    = $r->{'typeId'};
        $rec->{'recFormat'} = $r->{'recFormat'};
        $rec->{'title'}     = $r->{'title'};
        $rec->{'author'}    = $r->{'author'};
        $rec->{'pubPlace'}  = $r->{'pubPlace'};
        $rec->{'pubName'}   = $r->{'pubName'};
        $rec->{'pubDate'}   = $r->{'pubDate'};
        $rec->{'pubDateSort'}   = $r->{'pubDateSort'};
        $rec->{'totalCirc'} = $r->{'totalCirc'};
        $rec->{'callNumber'}= $r->{'callNumber'};
        $rec->{'prefix'}    = $r->{'prefix'};
        $pNum++;        
        $rec->{'odd'} = $pNum%2;
        $rec->{'order'} = $pNum;
        if( $incExcOpt eq 'inclusion' && 
           ($r->{'tempIll'} eq 'temporary' || $r->{'tempIll'} eq 'ILL')){
               $rec->{'tempIll'} = 1;
        }
        push @recordList, $rec;
        
    }
    $sth->finish;
    return \@recordList;
}



#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt,$selectedPrefix,$selectedItemType,$selectedRecFormat) = @_;
     my $sql_count = "
select    count(distinct i.rid) 
from      opl_item i inner join opl_marcRecord m on m.rid = i.rid 
                     inner  join opl_loan l on i.barcode=l.barcode
                     left outer join opl_itemInfo f on f.barcode=l.barcode  
where     l.dateLoan between '$dateFrom' and  '$dateTo' ";
          #&& i.barcode not regexp '^\_\_\_' ";

    if($incExcOpt eq 'exclusion'){
        $sql_count .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }          
    elsif($incExcOpt eq 'illTempOnly'){
       $sql_count .= " && (m.tempIll ='temporary' ||  m.tempIll = 'ILL') ";
    }
    if(defined $selectedPrefix && $selectedPrefix ne ''){
        $sql_count .= " && f.sf852Code='k' && f.sf852Data='$selectedPrefix' ";
    }
    if(defined $selectedItemType && $selectedItemType ne ''){
        $sql_count .= " && i.typeId='$selectedItemType' ";
    }
    if(defined $selectedRecFormat && $selectedRecFormat ne ''){
        $sql_count .= " && m.recFormat='$selectedRecFormat' ";
    }
    my $sth = $dbh->prepare($sql_count);
    $sth->execute();
    my ($countRecord) = $sth->fetchrow_array;

    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countRecord, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countRecord     => $countRecord,
        rangedPageList  => \@rangedPageList,
    );
}

#----------------------------------------------------------
# Mon, Nov 26, 2012 @ 11:40:09 EST
#

sub getItemCIrcStats2Export{
    my ($recordList)=@_;
    
    my $msgValMap ={};
    my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
    my $msgMap2           =loc_getMsgFile('circ/userCircList.msg',$msgValMap);

    my $itemFields=[
        {name=>'rid',       header=>uc($msgMap->{'recIdTxt'})},
        {name=>'title',     header=>uc($msgMap->{'titleTxt'})},
        {name=>'author',    header=>uc($msgMap->{'authorTxt'})},
        {name=>'pubName',   header=>uc($msgMap->{'pubNameTxt'})},
        {name=>'pubPlace',  header=>uc($msgMap->{'pubPlaceTxt'})},
        {name=>'pubDate',   header=>uc($msgMap->{'pubDateTxt'})},
        {name=>'callNumber',header=>uc($msgMap->{'callNumTxt'})},
        {name=>'prefix',    header=>uc($msgMap->{'prefixtxt'})},
        {name=>'typeId',    header=>uc($msgMap->{'typeTxt'})},
        {name=>'recFormat', header=>uc($msgMap->{'recFotmatTtx'})},
       # {name=>'dateLoan',header=>uc($msgMap2->{'dateLoanTxt'})},
       # {name=>'dateDue',header=>uc($msgMap2->{'dateDueTxt'})},
       # {name=>'dateReturn',header=>uc($msgMap2->{'dateReturnTxt'})},
        {name=>'totalCirc', header=>uc($msgMap->{'totalCircTxt'})}] ;

    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=itemCircStats.csv\n\n"; 
    my $headers=[];
    foreach  my $r (@$itemFields){
        push @$headers,"\"" . $r->{'header'} . "\"";
    }
    print  join(",",@$headers), "\n";
    foreach  my $row (@$recordList){
       my $arr=[];
       foreach my $f(@$itemFields){  
          $row->{$f->{'name'}} =~ s/"/""/g;
          push @$arr,"\"" . $row->{$f->{'name'}} ."\"";
       }
       print join(",",@$arr) ,"\n";
    }


}
#----------------------------------------------------------
sub getPrefixList{
    my($dbh,$selectedPrefix)=@_;
    my $prefixTbl=[];
    my $sth=$dbh->prepare("select distinct sf852Data prefix from opl_itemInfo where barcode not regexp '^_\_\__' && sf852Code='k' && sf852Data<>''");
    $sth->execute();
    while(my $rec =$sth->fetchrow_hashref){
        if(defined $selectedPrefix && $selectedPrefix eq $rec->{'prefix'}){
            $rec->{'selected'}=1;
        }
        push @$prefixTbl,$rec;
    }
    return $prefixTbl;
}
#----------------------------------------------------------
sub getItemTypeList{
    my($dbh,$selectedItemType)=@_;
    my $itemTypeTbl=[];
    my $sth=$dbh->prepare("select id as itemType from opl_itemType where itemCategory=1 && id<>''");
    $sth->execute();
    while(my $rec =$sth->fetchrow_hashref){
        if(defined $selectedItemType && $selectedItemType eq $rec->{'itemType'}){
            $rec->{'selected'}=1;
        }
        push @$itemTypeTbl,$rec;
    }
    return $itemTypeTbl;
}
#----------------------------------------------------------
sub getRecFormatList{
    my($dbh,$selectedRecFormat)=@_;
    my $recFormatTbl=[];
    my $msgMap           =loc_getMsgFile('search/standard.msg');
    my $sth=$dbh->prepare("select distinct recFormat from opl_marcRecord");
    $sth->execute();
    while(my $rec =$sth->fetchrow_hashref){
        if(defined $selectedRecFormat && $selectedRecFormat eq $rec->{'recFormat'}){
            $rec->{'selected'}=1;
        }
        if(defined $msgMap->{$rec->{'recFormat'}."Desc"}){
            $rec->{'label'}=$msgMap->{$rec->{'recFormat'}."Desc"};
        }
        else{
            $rec->{'label'}=$rec->{'recFormat'};
        }
        push @$recFormatTbl,$rec;
    }
    return $recFormatTbl;
}