#!/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 POSIX qw(
    ceil
    floor
);

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
    tmpl_preference
);

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 $syspref          = tmpl_preference($dbh);
my $sessionID = $cgi->cookie('globalSessionID');
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/shelfRange_prt.tmpl',
            reqPermission   => 'rpt_catRec',
        }
);

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';
    }
}

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 $shelvingLoc = $input->{'shelvingLoc'};
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 $t =time; 
    my $r= floor(100*rand());
    my $tmpSuffix= "$t" ."_" . $r ;
    createReportTbl($dbh, $tmpSuffix,$callNoB,$callNoE,$incExcOpt,$shelvingLoc);

    my ($recordList,$countRecord) = GetResultList($dbh,$tmpSuffix,$prtOpt, $mSort,$sDirection);
    my $prtType = $input->{'prtType'};
   
    if($prtType && $prtType eq 'csv'){
        saveShelfListAsCsv($recordList);
    }
    else{
        $template->param(rsList   => $recordList ,
                        countRecord=>$countRecord,
                         );
        if($prtOpt eq 'all'){
            $template->param(
                         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);
    }
    cleanUpTmpTbls($dbh,$tmpSuffix);

################################################################################
sub saveShelfListAsCsv{
   my ($recordList)=@_;
   print "Content-Type:application/x-download\n";
   print "Content-Disposition:attachment;filename=shelflist.csv\n\n";    
   print "\"Title\",\"Author\",\"ISBN\",\"Pub.Date\",\"Total Circ\",\"Location\",\"Barcode\",\"Call Number\",\"Price\",\"status\"\n" ;
   my @field =qw(title author isbn pubDate totalCirc);
   my @holdingField = qw(location barcode callNumber price status);
   my $preRid;
   foreach my $rec(@$recordList){
        my $rowStr="";
        foreach my $f(@field){
            my $str =$rec->{$f};
            $str =~ s/"/""/gi;
            $rowStr .= "\"$str\",";
         }
         $rowStr =~ s/,$//g;
         my @holdingList = @{$rec->{'holdingList'}};
         foreach my $h(@holdingList){
             my $hStr="";
             foreach my $hf(@holdingField){
                my $str =$h->{$hf};
                $str =~ s/"/""/gi;
                $hStr .= ",\"$str\"";
             }
             print "$rowStr $hStr\n";

         }

   }
}
################################################################################
sub GetResultList{
    my ($dbh,$tmpSuffix,$prtOpt, $mSort, $sDirection) =@_;
    my $sql ="select i.* from srReport_$tmpSuffix i";
    if($prtOpt ne  'all' ){
         $sql .= " inner join opl_sessionVar s on s.ssid ='$sessionID'  && i.barcode=s.barcode";
    }
    $sql .=  " order by $mSort " if($mSort ne ""); 


   
    my $query = $dbh->prepare($sql);

    $query->execute();
  
    my $pNum = -1;
    my @recordList =();
    my $holdingList;
    my $preRid=0;
    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;
    my $countRecord = scalar(@recordList);
    return (\@recordList,$countRecord);

}
################################################################################
 sub GetResultList_bk{
    my ($dbh,$prtOpt,$callNoB,$callNoE,$mSort, $sDirection,$incExcOpt,$shelvingLoc) =@_;
    my $sql ='';
    if($prtOpt eq 'all' ){
        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  ."'";
        }
        $sql = "select m.rid,m.title,m.author,m.pubDate,m.isbn,
                i.barcode,i.callNumber,i.dateImport,i.price,i.location,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 ";
       
    }
    else{
        $sql="select m.rid,m.title,m.author,m.pubDate,m.isbn,
                i.barcode,i.callNumber,i.dateImport,i.price,i.location,m.tempIll ,
                count(l.barcode) totalCirc
                from opl_item i inner join opl_sessionVar s on s.ssid ='$sessionID' && i.barcode=s.barcode
                inner join opl_marcRecord m on i.rid=m.rid 
                left outer join opl_loan l on i.barcode=l.barcode ";
    }
    $sql .=" group by i.barcode ";
    $sql .=" order by $mSort  " if($mSort ne "");

# open debug ,">/tmp/sr_p.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;
    my $cc=0;
    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'};
            $rec->{'location'}= $r->{'location'};
            $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->{'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);

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

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 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_

}

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