#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

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_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Session qw(
    SessionHdl_get
);
use Opals::Circulation qw(
    circ_getItemStatus
);
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 $sessionID = $cgi->cookie('globalSessionID');
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/shelfRange_prt.tmpl',
            reqPermission   => 'report',
        }
);


my $incExcOpt = $input->{'incExcOpt'};
if(!$incExcOpt || $incExcOpt ne 'inclusion'){
    $incExcOpt='exclusion';
}

my $mSort = $input->{'sort'};
my $sDirection = $input->{'sDirection'};
if(!$sDirection || $sDirection eq ''){
    if($mSort eq 'totalCirc' || $mSort eq 'pubDateSort'){
        $sDirection='DESC';
    }
    else{
        $sDirection='ASC';
    }
}


if ( !$mSort || $mSort eq 'callNumber'){ 
    $mSort = "callNumber $sDirection, titleSort, author"; 
}
elsif($mSort eq 'author' ){
    $mSort = "author $sDirection, titleSort, callNumber";
}
elsif($mSort eq 'titleSort' ){
    $mSort = "titleSort $sDirection, author, callNumber"; 
}
else{
    $mSort = "$mSort $sDirection, titleSort, author, callNumber"; 
}


my $callNoB = $input->{'callNoB'};
my $callNoE = $input->{'callNoE'};

    $callNoB =~s/^\s+|\s+$//g;
    $callNoE =~s/^\s+|\s+$//g;
    
    my $countRecord =0;
    my $prtOpt = $input->{'prtOpt'};
    my ($recordList,$countRecord) = GetResultList($dbh,$prtOpt,$callNoB,$callNoE, $mSort, $sDirection,$incExcOpt);
    
$template->param(rsList   => $recordList ,
                countRecord=>$countRecord,
                 callNoB  => $callNoB,
                 callNoE => $callNoE,
                 );
$template->param(sort => $mSort,
                 sDirection => $sDirection,
                ); 

    $template->param(
        prtReportHeader => 1,
    );

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

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

################################################################################
 sub GetResultList{
    my ($dbh,$prtOpt,$callNoB,$callNoE,$mSort, $sDirection,$incExcOpt) =@_;
    my $sql ='';
    if($prtOpt eq 'all' ){
        $sql=" select distinct i.rid, m.title,m.author,m.pubDate,i.barcode,i.callNumber,i.dateImport,
                      i.price ,m.tempIll ,count(l.barcode) as totalCirc 
               from     opl_marcRecord m 
                        inner join opl_item i on i.rid=m.rid 
                        left outer join opl_loan l on i.barcode=l.barcode 
               where i.barcode not regexp '^\_\_\_'";

        if($callNoB ne '' ){
            $sql .=" &&  i.callNumber >= '$callNoB'";
        }
        if($callNoE ne '' ){
            $callNoE =~ s/\*+$/z/gi;
            $sql .=" &&  i.callNumber <= '$callNoE'";
        }
        if($incExcOpt eq 'exclusion'){
            $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
        }
    }
    else{
        $sql=" select distinct m.rid, m.title,m.author,m.pubDate,i.barcode,i.callNumber,i.dateImport,
               i.price,m.tempIll ,count(l.barcode) as totalCirc
               from opl_marcRecord m inner join opl_sessionVar s on m.rid=s.rid  
               inner join opl_item i on i.rid=s.rid 
               left outer join opl_loan l on i.barcode=l.barcode
               where  s.var='shelfRange' && s.ssid ='$sessionID' && i.barcode not regexp '^\_\_\_' ";
    
        if($incExcOpt eq 'exclusion'){
            $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
        }
    }
    $sql .=" group by i.barcode 
             order by $mSort  ";
 
    my $query = $dbh->prepare($sql);
    $query->execute();
  
    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $preRid=0;
    my $barcode;
    my $itemCircStatus ;
    while (my $r = $query->fetchrow_hashref)
    {   
        if($r->{'rid'} != $preRid ){       
            my $rec;
            $preRid = $r->{'rid'};
            $rec->{'rid'}= $r->{'rid'};
            $rec->{'title'}= $r->{'title'};
            $rec->{'author'}= $r->{'author'};
            $rec->{'pubDate'}= $r->{'pubDate'};
            $rec->{'totalCirc'}= $r->{'totalCirc'};
            $pNum++;
            $rec->{'odd'} = $pNum%2;     
            $rec->{'order'} = $pNum;
            $rec->{'holdingList'}=();
            $rec->{'holdingCount'}=0;
            if( $incExcOpt eq 'inclusion' && 
               ($r->{'tempIll'} eq 'temporary' || $r->{'tempIll'} eq 'ILL')){
                   $rec->{'tempIll'} = 1;
            }

            push @recordList, $rec;
            
        }
        my $holding; 
        my $i=@recordList[$pNum]->{'holdingCount'};
        $itemCircStatus = circ_getItemStatus($dbh,$r->{'barcode'});
        my ($itemStatus,$colorStyle,$fontStyle) = ('available','black','normal');
        if($itemCircStatus->{'status'} == 2){
            ($itemStatus,$colorStyle,$fontStyle) = ('reserved','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 3){
            ($itemStatus,$colorStyle,$fontStyle) = ('on loan','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 4){
            ($itemStatus,$colorStyle,$fontStyle) = ('overdue','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 5){
            ($itemStatus,$colorStyle,$fontStyle) = ('on hold','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 7){
            ($itemStatus,$colorStyle,$fontStyle) = ('lost','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 8){
            ($itemStatus,$colorStyle,$fontStyle) = ('damage','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 10){
            ($itemStatus,$colorStyle,$fontStyle) = ('restricted','brown','bold');
        }
        elsif($itemCircStatus->{'status'} == 11){
            ($itemStatus,$colorStyle,$fontStyle) = ('missing','brown','bold');
        }

        $holding->{'status'} = $itemStatus;
        $holding->{'colorStyle'} = $colorStyle;
        $holding->{'fontStyle'} = $fontStyle;
        $holding->{'barcode'} =$r->{'barcode'};
        $holding->{'callNumber'}= $r->{'callNumber'};
        $holding->{'price'} = $r->{'price'};
        @recordList[$pNum]->{'holdingList'}->[$i] =$holding ;
        @recordList[$pNum]->{'holdingCount'} +=1;
        @recordList[$pNum]->{'colorStyle'} =$colorStyle;

         
    }
    $query->finish;
    my $countRecord = scalar(@recordList);
    return (\@recordList,$countRecord);

}

######################################################################
__END_OF_FILE: