#!/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::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

my @csvHeader=qw(
    RECID
    TITLE
    AUTHOR
    PUB.DATE
    CALLNUMBER
    BARCODE
    PRICE
    ISBN
    DATE
);
my @fields=qw(
  rid
  title
  author
  pubDate
  callNumber
  barcode
  price
  ISBN
  ondate
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $ridList= $input->{'ridList'};
 $ridList =~ s/(^\[|\]$)//g;
    my @id_bcArray = split(/\]\[/,$ridList);
    my @bcArray ;
    my $bcList = "";
     for (my $i=0; $i < @id_bcArray; $i++)
    {
        @bcArray = split(/:/,@id_bcArray[$i]);
        $bcList  .= "'" . @bcArray[1] . "',";
    }
    $bcList=~ s/,$//;

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

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

   
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );
    my $dateFrom= $input->{'missingFrom'};
    my $dateTo  = $input->{'missingTo'};

    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= "1970-01-01";
        $dateTo  = $dateToday;
    }
      
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

    $template->param(delFrom => $dateFrom);
    $template->param(delTo => $dateTo);

    my $sort    = $input->{'sort'};
    my $sortDir = $input->{'sortDir'};
    if ( !$sort ){
        $sort    = 'ondate';
        $sortDir = 'desc'  ;
    }

 
    my @recordList;
    if($bcList){
        @recordList = GetRecordPrint_ridList($dbh, $bcList,$sort, $sortDir);
     }
     else {
       @recordList = GetRecordPrint_all($dbh, $dateFrom, $dateTo, $sort);
     }
     if($input->{'op'} eq 'exportCsv' ){
        #Wed, Jun 06, 2012 @ 14:09:49 EDT
        #
        my $missingList =[];
        foreach my $item(@recordList){
            my $tmp=[];
            foreach my $field(@fields){
                push @$tmp,$item->{$field};
            }
            push @$missingList,$tmp;
        }
        print "Content-Encoding: UTF-8\n";
        print "Content-type: text/csv; charset=UTF-8\n";
        print "Content-Disposition:attachment;filename=MissingItemList.csv\n\n"; 
        print "\"",  join("\",\"",@csvHeader), "\"\n";
        foreach my $row(@$missingList){
            print "\"",  join("\",\"",@$row), "\"\n";
        }
     }
     else{     
        $template->param(
            recordList => \@recordList,
            from    => substr($dateFrom, 0, 10),
            to      => substr($dateTo, 0, 10),
            countHolding=>scalar(@recordList)
        );
        my $msgValMap ={};
        my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
        loc_write($template,$msgMap);

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

################################################################################
sub GetRecordPrint_ridList {
    my ($dbh,  $bcList, $sort,$sortDir) = @_;
    my @recordList;

my $sql = <<_STH_;
select   a.rid,a.callNumber,a.price,m.title,m.author, m.pubDate,b.barcode, b. status ,b.ondate , m.isbn as ISBN
from     opl_item as a inner join opl_marcRecord as m on m.rid=a.rid         
         inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode        
         left outer join (select * from opl_itemstatus where note='' || note is null)  c on b.barcode=c.barcode && b.id  < c.id 
where    c.id  is null &&
         b.status     = 0 &&
         b.barcode in ($bcList)
ORDER BY $sort  $sortDir
        
_STH_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref) {
        my $isbn = $rec->{'ISBN'};
	    $isbn =~ s/^\s+|\s+$//gi;
	    $isbn =~ s/ /, /gi;
        $rec->{'ISBN'} = $isbn;

        push @recordList, $rec;

    }
    $sth->finish;

    return @recordList;
}

################################################################################
 
sub GetRecordPrint_all {
    my ($dbh, $dateFrom, $dateTo, $sort,$sortDir,$incExcOpt ) = @_;
    
    my $sql = " 
select   a.rid,a.price,m.title,m.author, m.pubDate,b.barcode, b. status ,b.ondate ,m.isbn ISBN 
from     opl_item as a inner join opl_marcRecord as m on m.rid=a.rid         
         inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode        
         left outer join (select * from opl_itemstatus where note='' || note is null)  c on b.barcode=c.barcode && b.id  < c.id  
where    c.id  is null &&
         b.ondate >= ? &&  b.ondate <= ? &&
         b.status     = 0 ";

    if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
       
    $sql .= " group by a.rid  
              order by $sort  $sortDir ";


    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my @holdingList;
    my $hidden=0;
    while (my $rec = $sth->fetchrow_hashref) {
         my $isbn = $rec->{'ISBN'};
	    $isbn =~ s/^\s+|\s+$//gi;
	    $isbn =~ s/ /, /gi;
        $rec->{'ISBN'} = $isbn;

       @holdingList = getRecordHolding($dbh, $rec->{'rid'});
       foreach my $h (@holdingList){
           push @recordList, {rid       =>$rec->{'rid'},
                              title     =>$rec->{'title'},
                              author    =>$rec->{'author'} ,
                              pubDate   =>$rec->{'pubDate'} ,
                              odd       =>$rec->{'odd'},
                              order     =>$rec->{'order'} ,
                              ISBN      =>$rec->{'ISBN'},
                              callNumber=>$h->{'callNumber'},  
                              barcode   =>$h->{'barcode'},
                              price     =>$h->{'price'},
                              ondate    =>$h->{'ondate'},
                              };
           $hidden=1;                   
        }
    }
    $sth->finish;
    return @recordList;

 }


#----------------------------------------------------------
sub getRecordHolding {
   my ($dbh, $rid) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select   a.barcode,a.price,a.callNumber, b. status ,b.ondate 
from     opl_item as a inner join (select * from opl_itemstatus where note='' || note is null)  as b on a.barcode= b.barcode
         left outer join (select * from opl_itemstatus where note='' || note is null)  as c on b.barcode=c.barcode && b.id  < c.id 
where    a.rid=? &&
         c.ondate  is null &&
         b.status     = 0;
_STH_

    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        push @holdingList, $holding;
    }
    $sth->finish;

    return @holdingList;

}