#!/usr/bin/perl
#use utf8;
use strict;
use CGI;
use JSON;
use Opals::Context;
use Opals::Session qw(
SessionHdl_getSSID
);
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 $msgValMap ={};
my $msgMap =loc_getMsgFile('report/reports.msg',$msgValMap);
my $status=$input->{'status'};
my $custRptFieldsJson = Opals::Context->preference('custRptFields');
my $custRptFields=decode_json($custRptFieldsJson);
my $rptFieldList=$custRptFields->{"itemStatusReport" };
if(defined $custRptFields->{"itemStatusReport-$status"}){
$rptFieldList=$custRptFields->{"itemStatusReport-$status"};
}
my $filenameMap={'0' =>'missing_item_report.csv',
'2' =>'damange_item_report.csv',
'3' =>'lost_item_report.csv',
'5' =>'delete_item_report.csv',
'8' =>'on_order_item_report.csv',
'9' =>'processing_item_report.csv',
'10' =>'in_repair_item_report.csv',
'11' =>'claim_return_item_report.csv',
'12' =>'claim_never_loan_item_report.csv',
'13' =>'weed_item_report.csv'
};
my $rptHeaderMap={'0' =>'MISSING ITEM REPORT',
'2' =>'DAMAGED ITEM REPORT',
'3' =>'LOST ITEM REPORT',
'5' =>'DELETE ITEM REPORT',
'8' =>'ON ORDER ITEM REPORT',
'9' =>'PROCESSING ITEM REPORT',
'10' =>'IN REPAIR ITEM REPORT',
'11' =>'CLAIM RETURN ITEM REPORT',
'12' =>'CLAIM NEVER LOAN ITEM REPORT',
'13' =>'WEED ITEM REPORT'
};
my $status_odl= ($status ==2 || $status ==3 ||$status ==11 ||$status ==12 );
my @headers=();
my @fields=();
foreach my $f(@$rptFieldList){
if($f->{'inc'} eq '1' && $f->{'show'} eq '1') {
if(defined $f->{'incRpt'}){
foreach my $s(@{$f->{'incRpt'}}){
if($s eq $status){
push @headers,$msgMap->{$f->{'msgId'}};
push @fields,$f->{'field'};
last;
}
}
}
else{
push @headers,$msgMap->{$f->{'msgId'}};
push @fields,$f->{'field'};
}
}
}
my $fileFormat= $input->{"format"};
my $data =getRptData($dbh);
if($fileFormat eq 'csv'){
export_csv($dbh,\@headers,\@fields,$data);
}
elsif($fileFormat eq 'html'){
export_html($dbh,\@headers,\@fields,$data);
}
#------------------------------------------------------------------------------
sub getRptData{
my ($dbh)=@_;
my $sql = buildSQL();
#open debug,">/tmp/ed.sql"; print debug $sql; close debug;
my $sth=$dbh->prepare($sql);
my @data=();
$sth->execute();
while( my $rec=$sth->fetchrow_hashref){
push @data,$rec;
}
return \@data;
}
#------------------------------------------------------------------------------
sub export_html(){
my ($dbh,$headers,$fields,$data)=@_;
my $rptTitle =$rptHeaderMap->{$input->{'status'}};
print "Content-Encoding: UTF-8\n";
print "Content-type: text/html; charset=UTF-8\n\n";
print "$rptTitle
| ";
print join(" | ",@$headers) . " |
|---|
";
foreach my $rec(@$data){
my @row=();
foreach my $f(@$fields){
if($f eq 'barcode'){
$rec->{$f}=~s/^___(.*)_\d\d\d$/$1/g;
}
push @row,$rec->{$f};
}
print "| " . join(" | ",@row) ." |
";
}
}
#------------------------------------------------------------------------------
sub export_csv(){
my ($dbh,$headers,$fields,$data)=@_;
my $rptTitle =$rptHeaderMap->{$input->{'status'}};
$rptTitle =~ s/ /-/g;
$rptTitle .=".csv";
print "Content-Encoding: UTF-8\n";
print "Content-type: text/csv; charset=UTF-8\n";
print "Content-Disposition:attachment;filename=$rptTitle\n\n";
print join(",",@$headers) . "\n";
foreach my $rec(@$data){
my @row=();
foreach my $f(@$fields){
if($f eq 'barcode'){
$rec->{$f}=~s/^___(.*)_\d\d\d$/$1/g;
$rec->{$f}=~ s/"/""/g;
}
$rec->{$f}="\"$rec->{$f}\"";
push @row,$rec->{$f};
}
print join(",",@row) ."\n";
}
}
#------------------------------------------------------------------------------
sub export_csv_(){
my ($dbh,$fieldMap,$data)=@_;
my $fileName=$filenameMap->{$input->{'status'}};
my @hArr=();
foreach my $i(@$fieldMap){
push @hArr,$i->{'name'};
}
print "Content-Encoding: UTF-8\n";
print "Content-type: text/csv; charset=UTF-8\n";
print "Content-Disposition:attachment;filename=$fileName\n\n";
print join(",",@hArr) . "\n";
my $sql = buildSQL();
my $sth=$dbh->prepare($sql);
$sth->execute();
while( my @aa=$sth->fetchrow_array){
foreach my $c(@aa){
$c =~ s/^___(.*)_\d\d\d$/$1/g;
$c=~ s/"/""/g;
$c="\"$c\"";
}
print join(",",@aa) ."\n";
}
}
#------------------------------------------------------------------------------
sub export_html_(){
my ($dbh,$fieldMap,$data)=@_;
my @hArr=();
my $rptTitle =$rptHeaderMap->{$input->{'status'}};
foreach my $i(@$fieldMap){
push @hArr,$i->{'name'};
}
print "Content-Encoding: UTF-8\n";
print "Content-type: text/html; charset=UTF-8\n\n";
print "$rptTitle
| ";
print join(" | ",@hArr) . " |
|---|
";
my $sql = buildSQL();
my $sth=$dbh->prepare($sql);
$sth->execute();
while( my @aa=$sth->fetchrow_array){
foreach my $c(@aa){
last if($c =~ s/^___(.*)_\d\d\d$/$1/g);
}
print "| " . join(" | ",@aa) ." |
";
}
print "
";
}
#------------------------------------------------------------------------------
sub buildSQL(){
my $varName="itemStatusList_" . $input->{'status'};
my $sort = $input->{'sort'};
my $sortDir = $input->{'sortDir'};
my $incUserInfo = $input->{'incUserInfo'};
my $ssid = SessionHdl_getSSID($cgi);
my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'};
my $status=$itemStatus->{$input->{'status'}};
if($sort =~ m/titleSort|author|pubDateSort|isbn|rid/){
$sort = "m.$sort ";
}
elsif( $sort =~ m/firstname|lastname|userbarcode/ && $status_odl){
$sort = "u.$sort ";
}
elsif($sort =~ m/callNumber|barcode|price/){
$sort = "i.$sort ";
}
elsif($sort =~ m/ondate/){
$sort = "s.note ";
}
else{
$sort = "s.note ";
$sortDir = "DESC ";
}
my $fieldList = "m.rid,m.author,m.title,m.isbn,i.callNumber,m.pubDate,i.price,i.barcode,s.note ondate ";
my $tbls =" opl_sessionVar s inner join opl_item i on s.barcode=i.barcode and s.ssid='$ssid' && var='$varName'
inner join opl_marcRecord m on i.rid=m.rid";
if($status_odl){
$fieldList .=",u.firstname,u.lastname,u.userbarcode";
$tbls .= " left outer join (select l.barcode, u.firstname,u.lastname,u.userbarcode
from opl_loan l inner join opl_odl o on o.idloan=l.idloan && o.type='$status'
left outer join opl_user u on u.uid=l.uid) u on u.barcode=i.barcode ";
}
if( $input->{'status'} eq '0'){
# get latest item status if it's missing report.......
$fieldList .=", if(s1.status=5,'deleted',if(s1.status<>0 && s1.status <>13,'active','missing')) status";
$tbls .= " inner join opl_itemstatus s1 on s.barcode=s1.barcode
inner join (select barcode,max(id) id from opl_itemstatus group by barcode) t on s1.id=t.id and t.barcode=s.barcode"
}
my $sql ="select $fieldList from $tbls order by $sort $sortDir";
# open debug ,">/tmp/dd"; print debug $sql."\n"; close debug;
return $sql;
}
#------------------------------------------------------------------------------