#!/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::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/itemdel_prt.tmpl',
            reqPermission   => 'rpt_notice',
        }
);



    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->{'delFrom'};
    my $dateTo  = $input->{'delTo'};
    my $sort    = $input->{'sort'};
    $sort = 'ondate' if ( !$sort );
    my $sortDir = $input->{'sortDir'};
    if ( !$sort ){
        $sort    = 'ondate';
        $sortDir = 'desc'  ;
    }

    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

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

    my $remainList = '';
    if ( $input->{'op'} && $input->{'op'} eq 'del' )
    {
        $remainList = mxml_delete($dbh, $input->{'ridlist'});
    }

    my $recordList = GetRecordDelList($dbh,$ridList, $dateFrom, $dateTo, $sort,$sortDir,$incExcOpt);
    MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt);

    my $prtType = $input->{'prtType'};
    if($prtType && $prtType eq 'csv'){
        saveDelItemAsCsv($recordList); # //Fri, Feb 11, 2011 @ 12:05:02 ESTFri, Feb 11, 2011 @ 12:05:02 EST
    }
    else{
        $template->param(
            recordList  => $recordList,
            from        => substr($dateFrom, 0, 10),
            to          => substr($dateTo, 0, 10),
            sort        => $sort,
            sortDir     => $sortDir,
            pNum        => $pNum,
            incExcOpt   => $incExcOpt,
        );
    
        if(!$ridList){
            $template->param(
                prtRecDelHeader => 1,
            )
        }
        if($incExcOpt eq 'exclusion'){
            $template->param(exclusion =>1);
        }
        elsif($incExcOpt eq 'inclusion'){
            $template->param(inclusion =>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 GetRecordDelList {
    my ($dbh,$ridList,$dateFrom, $dateTo, $sort,$sortDir,$incExcOpt) = @_;

    
    my $sql ="
select   distinct i.rid, r.title, r.author,r.pubDate,i.callNumber, r.isbn
from    (opl_item as i inner join opl_itemstatus as s on i.barcode=s.barcode && s.status=5)
        inner join opl_marcRecord as r  on i.rid = r.rid 
where   s.ondate >= '$dateFrom' &&
        s.ondate <= '$dateTo'  && s.status=5 ";
 
 if($incExcOpt eq 'exclusion'){
       $sql .= " && (r.tempIll is NULL ||  r.tempIll = '') ";
  }

    $ridList =~ s/,+/,/g;
    $ridList =~ s/(^,|,$)//g;
    $ridList =~ s/,$//;
    if ($ridList){
        $sql .= " && i.rid in ($ridList) ";
    }
    $sql .= " order by $sort $sortDir  ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    #$template->param(debugStr=>$sql);
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my $holdingList;
    my $preRid=0;
    my $isbn;
    while (my $r = $sth->fetchrow_hashref) {
        if($preRid != $r->{'rid'}){
             my $rec;   
             $preRid = $r->{'rid'};
            $rec->{'rid'}= $r->{'rid'};
            $rec->{'title'}= $r->{'title'};
            $rec->{'author'}= $r->{'author'};
            $rec->{'pubDate'}= $r->{'pubDate'};
            $isbn= $r->{'isbn'};
            $isbn =~ s/\s+/ /g;
            $isbn =~ s/^\s+|\s+$//g;
            $isbn =~  s/\s/, /gi;
            $rec->{'isbn'} =$isbn;
 
            #$rec->{'odd'} = $odd ^ 1;
            $pNum++;
            $rec->{'odd'} = $pNum%2;
            $rec->{'order'} = $pNum;
            $holdingList = getRecordHolding($dbh, $rec->{'rid'}, $sort,$sortDir,$incExcOpt,$dateFrom, $dateTo);
            $rec->{'holdingList'} = $holdingList;
            $rec->{'holdingCount'} = scalar(@$holdingList);
            ($rec->{'holdingCount'} > 1) || ($rec->{'holdingCount'} = 0);
        push @recordList, $rec;
        }
    }
    $sth->finish;

    return \@recordList;
}


#----------------------------------------------------------
sub getRecordHolding {
my ($dbh, $rid, $sort,$sortDir,$incExcOpt,$dateFrom, $dateTo) = @_;
 
    my $sql =" 
select  distinct s.barcode, callNumber,price, ondate
from    opl_itemstatus as s inner join opl_item as i on s.barcode = i.barcode
                            inner join opl_marcRecord m on m.rid = i.rid 
where   m.rid = ? && s.status =5 && s.ondate >= '$dateFrom' && s.ondate <= '$dateTo'";

if($incExcOpt eq 'exclusion'){
       $sql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
  }
  $sort ="m.$sort" if($sort eq 'rid');
$sql .= " order by $sort $sortDir "; 
 
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        if($holding ->{'barcode'} =~m/^\_\_\_(.*)\_[\d]+$/){
            $holding ->{'barcode'}=$1;
        }
        else{
           $holding ->{'barcode'}='n/a';
        }
       push @holdingList, $holding;
    }
    $sth->finish;
 
    return \@holdingList;
}

#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;
    
    my $szSQL = "select count(distinct i.rid) from opl_item as i  
                inner join opl_marcRecord m on m.rid = i.rid 
                       inner join opl_itemstatus as s on i.barcode=s.barcode   
                       
                 where s.status=5 && ondate>='$dateFrom' && ondate<='$dateTo'";
    if($incExcOpt eq 'exclusion'){
        $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }


    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my $countRecord = $query->fetchrow_array;
    $query->finish;
    
    $szSQL = "select count(distinct i.barcode) from opl_itemstatus  s
                         inner join opl_item as i on i.barcode=s.barcode
                         inner join opl_marcRecord m on m.rid = i.rid
                  where status=5 && ondate>='$dateFrom' && ondate<='$dateTo'";
    if($incExcOpt eq 'exclusion'){
        $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($countHolding) = $query->fetchrow_array;

    $query->finish;

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

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

################################################################################
# Function add: Fri, Feb 11, 2011 @ 12:06:09 EST
#
sub saveDelItemAsCsv{
   my ($recordList)=@_;
   print "Content-Type:application/x-download\n";
   print "Content-Disposition:attachment;filename=deletedItems.csv\n\n";    
   print "\"Title\",\"Author\",\"ISBN\",\"Pub.Date\",\"Call Number\",\"Price\",\"Barcode\",\"Deleted Date\"\n" ;
   my @field =qw(title author isbn pubDate );
   my @holdingField = qw(callNumber price barcode ondate);
   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";

         }

   }
}