#!/usr/bin/perl
#--------------------------------------------------------------------------------------
#   ON:     Thu, Mar 24, 2011 @ 09:03:27 EDT
#   Add:    Location to report
#   FILES: 
#           /www/odev/bin/report/shelfRangeList
#           /www/odev/htdocs/theme/opals/report/shelfRangeList.tmpl
#           /www/odev/bin/report/shelfRange_prt
#           /www/odev/htdocs/theme/opals/report/shelfRange_prt.tmpl
#           /www/odev/htdocs/theme/opals/locale/fr/report/reports.msg
#           /www/odev/htdocs/theme/opals/locale/en/report/reports.msg
#---------------------------------------------------------------------------------------
#use utf8;
use strict;
use CGI;

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


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

use Opals::Date qw(
    date_parse
    date_today
    date_text
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
    tmpl_preference
);
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 $syspref          = tmpl_preference($dbh);

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

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('shelfRangeList') );
my $shelvingLoc = $input->{'shelvingLoc'};
my $incExcOpt = $input->{'incExcOpt'};
if(!$incExcOpt || $incExcOpt ne 'inclusion'){
    $incExcOpt='exclusion';
}

my $callNoRangeOpt = $input->{'callNoRangeOpt'};
if(!$callNoRangeOpt || $callNoRangeOpt eq ''){
    $callNoRangeOpt = 'range';
}

my $mSort = $input->{'sort'};
my $sDirection = $input->{'sDirection'};
if(!$sDirection || $sDirection eq ''){
    if($mSort eq 'totalCirc' || $mSort eq 'pubDateSort'){
        $sDirection='DESC';
    }
    else{
        $sDirection='ASC';
    }
}
my $cnSys =$syspref->{'classificationSystem'};
#my $cnSortField=($cnSys eq 'dewey')?"callNumSort_dewey":"callNumSort_lcc";
my $cnSortField=($cnSys eq 'dewey')?"callNumber":"callNumSort_lcc";

if ( !$mSort || $mSort eq 'callNumber'){ 
    $mSort = "$cnSortField $sDirection,author, titleSort "; 
}
elsif($mSort eq 'author' ){
    $mSort = "author $sDirection, titleSort, $cnSortField";
}
elsif($mSort eq 'titleSort' ){
    $mSort = "titleSort $sDirection, author, $cnSortField"; 
}
else{
    $mSort = "$mSort $sDirection, author, titleSort, $cnSortField"; 
}
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );


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

    $callNoB =~s/^\s+|\s+$//g;
    $callNoE =~s/^\s+|\s+$//g;
    my $recordList=[] ;
    if(($callNoRangeOpt == 'range' && $callNoB ne "" && $callNoE ne "") || $callNoRangeOpt == 'all'){
        my $t =time; 
        my $r= floor(100*rand());
        my $tmpSuffix= "$t" ."_" . $r ;
        createReportTbl($dbh, $tmpSuffix,$callNoB,$callNoE,$incExcOpt,$shelvingLoc);
        trimNonDigit($dbh,"srReport_$tmpSuffix");
        #MakePages($dbh, $input, $template, $pagesize, $callNoB,$callNoE,$incExcOpt,$shelvingLoc);
        #$recordList = GetResultList($dbh, $callNoB,$callNoE, $mSort, $sDirection, $pNum, $pagesize,$incExcOpt,$shelvingLoc);
        MakePages($dbh,$tmpSuffix, $input, $template, $pagesize);
        $recordList = GetResultList($dbh,$tmpSuffix, $mSort, $sDirection, $pNum, $pagesize);
        cleanUpTmpTbls($dbh,$tmpSuffix);
    }

    #$sDirection=($sDirection eq 'asc' ?'desc':'asc');
    $template->param(shelfRange =>1,
                     rsList     => $recordList ,
                     callNoB    => $callNoB,
                     callNoE    => $callNoE,
                     incExcOpt  => $incExcOpt,
                     "$callNoRangeOpt"=>1
                     );
    $template->param(sort => $input->{'sort'},
                     sDirection => $sDirection,
                     pNum => $pNum); 
    

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

my $sLocList = getShelvingLocationList($dbh);
$template->param( sLocList      => $sLocList,
                  shelvingLoc   => $shelvingLoc);


#Wed, Jan 13, 2010 @ 10:13:11 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);

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

######################################################################
sub getShelvingLocationList{
    my ($dbh) =@_;
    my @retList = ();
     
my $sql = "select    distinct ii.sf852Data from opl_itemInfo ii inner join opl_item i using(barcode) 
           where     i.barcode not regexp '^\_\_\_' && ii.sf852Code='c' && ii.sf852Data <>''";
my $query = $dbh->prepare($sql);
    $query->execute();

    while (my $r = $query->fetchrow_hashref){   
        push @retList ,$r;
    }

    return \@retList;

}
######################################################################
sub GetResultList_bk{
    my ($dbh, $callNoB,$callNoE,$mSort, $sDirection, $pNum, $pagesize ,$incExcOpt,$shelvingLoc) =@_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
    my $joinCond =" i.barcode not regexp '^___' ";
    if($callNoB ne ''){
        $joinCond .=" && i.callNumber >= '$callNoB'";
    }
    if($callNoE ne ''){
        $callNoE =~ s/\*+$/z/gi;
        $joinCond .=" && i.callNumber <= '$callNoE'";
    }
                   
    if($incExcOpt eq 'exclusion'){
       $joinCond .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    if($shelvingLoc && $shelvingLoc ne ''){         
       $joinCond .= " && location ='" . $shelvingLoc  ."'";
    }

my $sql = "select m.rid,m.title,m.author,m.pubDate,m.isbn,
        i.barcode,i.callNumber,i.dateImport,i.price,m.tempIll ,
        count(l.barcode) totalCirc
        from opl_marcRecord m inner join opl_item i on i.rid=m.rid &&  $joinCond 
        left outer join opl_loan l on i.barcode=l.barcode 
        group by i.barcode ";

     $sql .=  " order by $mSort " if($mSort ne ""); 
     $sql .=  " limit  $pageoffset,$pagesize ";
#open debug ,">/tmp/sr.sql";print debug $sql;close debug;
    my $query = $dbh->prepare($sql);
    $query->execute();
    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $preRid=0;
    my $barcode;
    my $itemCircStatus ;
    my $isbn;
    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'};
            $isbn= $r->{'isbn'};
            $isbn =~ s/\s+/ /g;
            $isbn =~ s/^\s+|\s+$//g;
            $isbn =~  s/\s/, /gi;
            $rec->{'isbn'} =$isbn;
            $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->{'location'}      = $r->{'location'};
        $holding->{'barcode'}       = $r->{'barcode'};
        $holding->{'callNumber'}    = $r->{'callNumber'};
        $holding->{'ondate'}        = $r->{'ondate'};
        $holding->{'price'}         = $r->{'price'};   
          
        @recordList[$pNum]->{'holdingList'}->[$i] =$holding ;
        @recordList[$pNum]->{'bcList'} .="," if(@recordList[$pNum]->{'bcList'} ne "");
        @recordList[$pNum]->{'bcList'} .= $r->{'barcode'};
        @recordList[$pNum]->{'holdingCount'} +=1;
        @recordList[$pNum]->{'colorStyle'} =$colorStyle;

    }
    $query->finish;
    return \@recordList;

}
######################################################################
sub MakePages_bk
{
    my ($dbh, $input, $template, $pagesize, $callNoB,$callNoE,$incExcOpt,$shelvingLoc) = @_;  

    my $joinCond =" on m.rid=i.rid && i.barcode not regexp '^___' ";
    if($callNoB ne ''){
        $joinCond .=" && i.callNumber >= '$callNoB'";
    }
    if($callNoE ne ''){
        $callNoE =~ s/\*+$/z/gi;
        $joinCond .=" && i.callNumber <= '$callNoE'";
    }
                   
    if($incExcOpt eq 'exclusion'){
       $joinCond .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    
    if($shelvingLoc && $shelvingLoc ne ''){         
       $joinCond .= "   &&  i.location= '" . $shelvingLoc  ."'";
    }


    my $sql_title ="select  count(distinct i.rid) from opl_item i inner join opl_marcRecord m  $joinCond ";
    my $sql_holding ="select  count(distinct i.barcode) from opl_item i inner join opl_marcRecord m $joinCond ";
    my $sql_sum =" select  sum(price) from opl_item i inner join opl_marcRecord m  $joinCond ";

     
    my @Items = ();
    my $query_title = $dbh->prepare($sql_title);
    $query_title->execute();
    my ($countRecord ) = $query_title->fetchrow_array;

    my $query_holding = $dbh->prepare($sql_holding);
    $query_holding->execute();
    my ($countHolding ) = $query_holding->fetchrow_array;

    #Wed, Jan 16, 2013 @ 10:48:32 EST
    my $query_sum = $dbh->prepare($sql_sum);
    $query_sum->execute();
    my ($totalPrice ) = $query_sum->fetchrow_array;
    my $avgPrice = 0.00;
    $avgPrice =  $totalPrice/$countHolding if($countHolding >0);
     
    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countHolding, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countRecord     => $countRecord,
        countHolding    => $countHolding ,
        rangedPageList  => \@rangedPageList,
        avgPrice        => sprintf("%.2f",$avgPrice),
        totalPrice      => sprintf("%.2f",$totalPrice)
    );

}

######################################################################
sub MakePages
{
    my ($dbh, $tmpSuffix,$input, $template, $pagesize) = @_;  

    my $sql_title ="select  count(distinct rid) from srReport_$tmpSuffix ";
    my $sql_holding ="select  count(barcode) from srReport_$tmpSuffix  ";
    my $sql_sum =" select  sum(price) from  srReport_$tmpSuffix ";

     
    my @Items = ();
    my $query_title = $dbh->prepare($sql_title);
    $query_title->execute();
    my ($countRecord ) = $query_title->fetchrow_array;

    my $query_holding = $dbh->prepare($sql_holding);
    $query_holding->execute();
    my ($countHolding ) = $query_holding->fetchrow_array;

    #Wed, Jan 16, 2013 @ 10:48:32 EST
    my $query_sum = $dbh->prepare($sql_sum);
    $query_sum->execute();
    my ($totalPrice ) = $query_sum->fetchrow_array;
    my $avgPrice = 0.00;
    $avgPrice =  $totalPrice/$countHolding if($countHolding >0);
     
    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countHolding, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countRecord     => $countRecord,
        countHolding    => $countHolding ,
        rangedPageList  => \@rangedPageList,
        avgPrice        => sprintf("%.2f",$avgPrice),
        totalPrice      => sprintf("%.2f",$totalPrice)
    );

}
######################################################################
sub GetResultList{
    my ($dbh,$tmpSuffix, $mSort, $sDirection, $pNum, $pagesize ) =@_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
   
    my $sql = "select * from srReport_$tmpSuffix";

     $sql .=  " order by $mSort " if($mSort ne ""); 
     $sql .=  " limit  $pageoffset,$pagesize ";
    # open debug ,">/tmp/sr.sql";print debug $sql;close debug;
    my $query = $dbh->prepare($sql);
    $query->execute();
    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $preRid=0;
    my $barcode;
    my $itemCircStatus ;
    my $isbn;
    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'};
            $isbn= $r->{'isbn'};
            $isbn =~ s/\s+/ /g;
            $isbn =~ s/^\s+|\s+$//g;
            $isbn =~  s/\s/, /gi;
            $rec->{'isbn'} =$isbn;
            $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 $circStatus = circ_getItemStatus($dbh,$r->{'barcode'});
        $r->{'status'}=$circStatus->{'status'} ;

        my $i=@recordList[$pNum]->{'holdingCount'};
        my ($itemStatus,$colorStyle,$fontStyle) = ('available','black','normal');

        if($r->{'status'} == 2){
            ($itemStatus,$colorStyle,$fontStyle) = ('reserved','brown','bold');
        }
        elsif($r->{'status'} == 3){
            ($itemStatus,$colorStyle,$fontStyle) = ('on loan','brown','bold');
        }
        elsif($r->{'status'} == 4){
            ($itemStatus,$colorStyle,$fontStyle) = ('overdue','brown','bold');
        }
        elsif($r->{'status'} == 5){
            ($itemStatus,$colorStyle,$fontStyle) = ('on hold','brown','bold');
        }
        elsif($r->{'status'} == 7){
            ($itemStatus,$colorStyle,$fontStyle) = ('lost','brown','bold');
        }
        elsif($r->{'status'} == 8){
            ($itemStatus,$colorStyle,$fontStyle) = ('damage','brown','bold');
        }
        elsif($r->{'status'} == 10){
            ($itemStatus,$colorStyle,$fontStyle) = ('restricted','brown','bold');
        }
        elsif($r->{'status'} == 11){
            ($itemStatus,$colorStyle,$fontStyle) = ('missing','brown','bold');
        }

        $r->{'status'}         = $itemStatus;
        $r->{'colorStyle'}    = $colorStyle;
        $r->{'fontStyle'}     = $fontStyle;
         
        @recordList[$pNum]->{'holdingList'}->[$i] =$r; ;
        @recordList[$pNum]->{'bcList'} .="," if(@recordList[$pNum]->{'bcList'} ne "");
        @recordList[$pNum]->{'bcList'} .= $r->{'barcode'};
        @recordList[$pNum]->{'holdingCount'} +=1;

    }
    $query->finish;
    return \@recordList;

}
######################################################################

sub cleanUpTmpTbls{
    my ($dbh,$t)=@_;
  $dbh->do("drop table srReport_$t");
  $dbh->do("drop table tmpCirc_$t");
}
######################################################################
sub createReportTbl{
    my ($dbh,$t,$callNoB,$callNoE,$incExcOpt,$shelvingLoc)=@_;
    
    my $joinCond =" on  i.rid=m.rid && i.barcode not regexp '^___' ";
    if($callNoB ne ''){
        $joinCond .=" && i.callNumber >= '$callNoB'";
    }
    if($callNoE ne ''){
        $callNoE =~ s/\*+$/z/gi;
        $joinCond .=" && i.callNumber <= '$callNoE'";
    }
                   
    if($incExcOpt eq 'exclusion'){
       $joinCond .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    if($shelvingLoc && $shelvingLoc ne ''){         
       $joinCond .= " && location ='" . $shelvingLoc  ."'";
    }



   my $sql=<<_SQL_;
CREATE TABLE if not exists srReport_$t (
  `rid` int(10) unsigned NOT NULL DEFAULT '1',
  `title` varchar(255) CHARACTER SET utf8,
  `titleSort` varchar(255) DEFAULT NULL,
  `author` varchar(255) CHARACTER SET utf8,
  `pubDate` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `pubDateSort` varchar(100) DEFAULT NULL,
  `isbn` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `barcode` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `location` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `callNumber` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `callNumSort_dewey` varchar(100) DEFAULT '',
  `callNumSort_lcc` varchar(100) DEFAULT '',
  `price` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `totalCirc` int(10) unsigned DEFAULT '0',
  `status` tinyint(3) DEFAULT '1',
  KEY `ind_1` (`barcode`,`rid`,`callNumber`,`status`,`totalCirc`),
  KEY `ind_t`(titleSort),
  KEY `ind_p`(pubDateSort)
) ENGINE=MyISAM DEFAULT CHARSET=utf8    
_SQL_


 $dbh->do($sql);

    $sql=<<_SQL_;

    insert into srReport_$t 
        select m.rid,m.title,m.titleSort,m.author,pubDate,m.pubDateSort,isbn,i.barcode,i.location,
        i.callNumber,i.callNumSort_dewey,i.callNumSort_lcc,i.price,0,i.available 
        from opl_item i inner join opl_marcRecord m  $joinCond
        
_SQL_
  $dbh->do($sql);

    $dbh->do(<<_SQL_);
CREATE TABLE if not exists tmpCirc_$t (
  `barcode` varchar(50) NOT NULL DEFAULT '',
  `totalCirc` bigint(21) NOT NULL DEFAULT '0',
   KEY `ind_1` (`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
_SQL_

$dbh->do("truncate table tmpCirc_$t");     
$dbh->do(<<_SQL_);        
    insert into tmpCirc_$t select barcode, count(barcode) from opl_loan group by barcode
_SQL_

$dbh->do(<<_SQL_);        
   update srReport_$t s inner join tmpCirc_$t t using(barcode) set s.totalCirc=t.totalCirc;
_SQL_


$dbh->do(<<_SQL_);
update srReport_$t t inner join opl_loan l using(barcode) set t.status=3 where l.dateReturn is null;
_SQL_

$dbh->do(<<_SQL_);
update srReport_$t t inner join opl_reserve r using(rid) inner join opl_hold h using(idReserve) 
set t.status=5 where h.dateCancel is null && h.dateExpiry >now() && dateLoan is null;
_SQL_

$dbh->do(<<_SQL_);
update srReport_$t t inner join opl_reserve r using(rid) set t.status=2 where numCopyReserve>0 && r.dateCancel is null && r.dateExpiry >now() ;
_SQL_

$dbh->do(<<_SQL_);
create table if not exists tmp_status like opl_itemstatus; 
_SQL_
$dbh->do("truncate table tmp_status"); 
$dbh->do(<<_SQL_);
insert into tmp_status(id,barcode) select max(id),barcode from opl_itemstatus where status=13 || status between 1 and 5 group by barcode;
_SQL_

$dbh->do(<<_SQL_);
update tmp_status t inner join opl_itemstatus s using(id) set t.status=s.status;
_SQL_



$dbh->do(<<_SQL_);
update srReport_$t  t inner join tmp_status s  on t.barcode=s.barcode && (s.status=2||s.status=3) set t.status=if(s.status=2,8,7);
_SQL_

$dbh->do(<<_SQL_);
delete t.* from srReport_$t  t inner join tmp_status s using(barcode) where s.status=13 ;
_SQL_
}

######################################################################
sub trimNonDigit{
    my($dbh,$srTbl)=@_;
    my $sth_update=$dbh->prepare("update $srTbl set price=? where barcode =? ");
    
    my $sth=$dbh->prepare("select barcode,price from $srTbl where price regexp '[^.0-9]'");
    $sth->execute();
    while(my ($bc,$price) = $sth->fetchrow_array){
        $price =~ s/[^.0-9]//gi;
        $sth_update->execute($price,$bc);
    }
    $sth->finish;

}
######################################################################

__END_OF_FILE: