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

    
    my $status=$input->{'type'};
    $status=3 if(!$status || $status != 2);
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );
    my $dateFrom= $input->{'lostFrom'};
    my $dateTo  = $input->{'lostTo'};

    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'};
    $sort = 'dateImport' if ( !$sort );
    my @recordList;
    if($bcList){
        @recordList = GetRecordPrint_ridList($dbh, $bcList, $dateFrom, $dateTo,$status,$sort );
     }
     else {
       @recordList = GetRecordPrint_all($dbh, $dateFrom, $dateTo,$status, $sort);
     }
     
     if($input->{'op'} eq 'exportCsv' ){
        #Wed, Jun 06, 2012 @ 14:09:49 EDT
        #
        my $lostList =[];
        foreach my $item(@recordList){
            my $tmp=[];
            foreach my $field(@fields){
                push @$tmp,$item->{$field};
            }
            push @$lostList,$tmp;
        }
        print "Content-Encoding: UTF-8\n";
        print "Content-type: text/csv; charset=UTF-8\n";
        print "Content-Disposition:attachment;filename=LostItemList.csv\n\n"; 
        print "\"",  join("\",\"",@csvHeader), "\"\n";
        foreach my $row(@$lostList){
            print "\"",  join("\",\"",@$row), "\"\n";
        }
     }
     else{     
        $template->param(
            reportType   => ($status eq 3)?3:2,
            lostReport   => ($status eq 3)?1:0,
            damagedReport=> ($status eq 2)?1:0,
            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, $dateFrom, $dateTo,$status, $sort) = @_;
    my @recordList;

my $sql = <<_STH_;
SELECT distinct i.rid,i.callNumber,i.price,r.title, r.author,r.pubDate,i.barcode,s.status,max(s.ondate) as ondate ,  r.isbn as ISBN
FROM (opl_item as i inner join opl_itemstatus as s on i.barcode= s.barcode && s.status=$status)
INNER JOIN opl_marcRecord as r  on i.rid = r.rid  
WHERE i.barcode in ($bcList) && s.ondate  is not null
GROUP BY barcode
ORDER BY $sort  

_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,$status, $sort) = @_;
   my $sql = <<_STH_;
select  m1.rid,m1.title,m1.author,m.callNumber, m.barcode,m.ondate,m.price,m1.pubDate, m1.isbn as ISBN

    from
    (select  t2.rid,t2.callNumber,t6.barcode,t6.ondate,t6.price
    from
        (select rid, callNumber,barcode,price from (select rid,callNumber,barcode ,price from opl_item) as t1 group by rid) as t2
        inner  join
        (select i.rid ,i.price,i.barcode,t5.ondate 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo' && status<>6 && barcode not regexp '^___') as t3
                left outer join
                (select * from opl_itemstatus where ondate between '$dateFrom' and  '$dateTo'  && status<>6 && barcode not regexp '^___') as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status=$status 
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid    
    )as m
    inner join opl_marcRecord m1 on m1.rid=m.rid where (m1.tempIll is NULL ||  m1.tempIll = '')

order by $sort  asc
_STH_
 
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @recordList;
    my @holdingList;
    while (my $rec = $sth->fetchrow_hashref) {
        my $isbn = $rec->{'ISBN'};
	    $isbn =~ s/^\s+|\s+$//gi;
	    $isbn =~ s/ /, /gi;
        $rec->{'ISBN'} = $isbn;

       push @recordList, {rid        =>$rec->{'rid'},
                          title      =>$rec->{'title'},
                          author     =>$rec->{'author'} ,
                          order      =>$rec->{'order'} ,
                          callNumber =>$rec->{'callNumber'},
                          barcode    =>$rec->{'barcode'},
                          ondate     =>$rec->{'ondate'},
                          price      =>$rec->{'price'},
                          pubDate    =>$rec->{'pubDate'},
                          ISBN       =>$rec->{'ISBN'},
                          };
    }
    $sth->finish;

    return @recordList;

 }

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

    my $sth = $dbh->prepare(<<_STH_);
SELECT  s.barcode,callNumber,substring(c,1,19) AS ondate FROM opl_item AS i 
   INNER JOIN
          (
           SELECT barcode, MAX(concat(ondate,status)) as c
           FROM opl_itemstatus as s2 where  substring(s2.barcode, 1, 3) <> '___' 
           group by s2.barcode having c regExp '$status\$'
           )
           AS s ON i.barcode=s.barcode and i.rid=?
_STH_
    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
        push @holdingList, $holding;
    }
    $sth->finish;

    return @holdingList;

}