#!/usr/bin/perl #use utf8; use strict; use CGI; use JSON; use Opals::Context; use Opals::Session qw( SessionHdl_getSSID ); use Opals::Tb_Record qw( tb_item_findByBarcode ); my $dbh = Opals::Context->dbh(); END { $dbh->disconnect(); } 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 $rptTitleMap={'0' =>'MISSING ITEM REPORT', '2' =>'TEXTBOOK - DAMAGED ITEM REPORT', '3' =>'TEXTBOOK - LOST ITEM REPORT', '5' =>'TEXTBOOK - 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 $cgi = CGI->new; my $input = $cgi->Vars(); my $fieldMap=[ {name=>'RID',field=>'i.rid as rid'}, {name=>'TITLE',field=>'r.fVal as title'}, {name=>'BARCODE',field=>'i.barcode as barcode'}, {name=>'PRICE',field=>'i.price as price'}, {name=>'DATE',field=>'s.note as ondate'} ]; if(defined $input->{'incUserInfo'} && $input->{'incUserInfo'} ==1){ push @$fieldMap,{name=>'FIRSTNAME',field=>'u.firstname as firstname'}; push @$fieldMap,{name=>'LASTNAME',field=>'u.lastname as lastname'}; push @$fieldMap,{name=>'USERBARCODE',field=>'u.userbarcode as userbarcode'}; } my $fileFormat= $input->{"format"}; my $itemList = getItemList(); if($fileFormat eq 'csv'){ export_csv($itemList); } elsif($fileFormat eq 'html'){ export_html($itemList); } #------------------------------------------------------------------------------ sub export_csv(){ my ($list)=@_; 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"; foreach my $i(@$list){ print "\"" . $i->{'rid'} . "\","; print "\"" . $i->{'title'} . "\","; print "\"" . $i->{'barcode'} . "\","; print "\"" . $i->{'price'} . "\","; print "\"" . $i->{'ondate'} . "\","; if(defined $input->{'incUserInfo'} && $input->{'incUserInfo'} ==1){ print "\"" . $i->{'firstname'} . "\","; print "\"" . $i->{'lastname'} . "\","; print "\"" . $i->{'userbarcode'} . "\","; } print "\n"; } } #------------------------------------------------------------------------------ sub export_html(){ my ($list)=@_; my @hArr=(); my $rptTitle =$rptTitleMap->{$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

"; foreach my $i(@$list){ print "" ; print ""; print ""; print ""; print ""; print ""; if(defined $input->{'incUserInfo'} && $input->{'incUserInfo'} ==1){ print ""; print ""; print ""; } print ""; } print "
"; print join("",@hArr) . "
" . $i->{'rid'} . "" . $i->{'title'} . "" . $i->{'barcode'} . "" . $i->{'price'} . "" . $i->{'ondate'} . "" . $i->{'firstname'} . "" . $i->{'lastname'} . "" . $i->{'userbarcode'} . "
"; } #------------------------------------------------------------------------------ sub getItemList(){ my $varName= $input->{'varName'}; my $sort = $input->{'sort'}; my $sortDir = $input->{'sortDir'}; my $ssid = SessionHdl_getSSID($cgi); my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'}; my $status=$itemStatus->{$input->{'status'}}; my $incUserInfo = $input->{'incUserInfo'}; my @list=(); my @fArr=(); foreach my $i(@$fieldMap){ push @fArr,$i->{'field'}; } my $fieldList = join ",",@fArr; my $sql =<<_SQL_; select $fieldList from opl_sessionVar s inner join tb_items i on i.barcode=s.barcode && s.ssid='$ssid' && s.var ='$varName' inner join tb_records r on r.rid=i.rid && r.fId='245_a' _SQL_ if ($incUserInfo && $incUserInfo == 1){ $sql .= " inner join tb_loan l on l.barcode=i.barcode inner join tb_odl o on o.idloan=l.id && o.type='$status' && o.ondate=s.note left outer join opl_user u on u.uid=l.uid " } my @itemList=(); my $sth=$dbh->prepare($sql); $sth->execute(); while (my $i = $sth->fetchrow_hashref){ my $itemInfo = tb_item_findByBarcode($dbh,$i->{'barcode'}); push @itemList, { rid => $i->{'rid'}, title => $itemInfo->{'title'}, author => $itemInfo->{'author'}, price => $itemInfo->{'price'}, callnumber => $itemInfo->{'classNumber'}, barcode => $i->{'barcode'}, ondate => $i->{'ondate'}, firstname => $i->{'firstname'}, lastname => $i->{'lastname'}, uid => $i->{'uid'}, userbarcode => $i->{'userbarcode'}, }; } $sth->finish; my @sorted; if ($sort eq '245_a' || $sort eq 'title'){ @sorted =($sortDir eq "desc")? sort { lc($b->{'title'}) cmp lc($a->{'title'})} @itemList : sort { lc($a->{'title'}) cmp lc($b->{'title'})} @itemList; } elsif ($sort eq 'recordId') { @sorted =($sortDir eq "desc")? sort { lc($b->{'rid'}) <=> $a->{'rid'}} @itemList : sort { $a->{'rid'} <=> $b->{'rid'}} @itemList; } elsif ($sort eq 'barcode') { @sorted =($sortDir eq "desc")? sort { lc($b->{'barcode'}) cmp lc($a->{'barcode'})} @itemList : sort { lc($a->{'barcode'}) cmp lc($b->{'barcode'})} @itemList; } elsif ($sort eq 'price') { @sorted =($sortDir eq "desc")? sort { lc($b->{'price'}) cmp lc($a->{'price'})} @itemList : sort { lc($a->{'price'}) cmp lc($b->{'price'})} @itemList; } elsif ($sort eq 'ondate') { @sorted =($sortDir eq "desc")? sort { lc($b->{'ondate'}) cmp lc($a->{'ondate'})} @itemList : sort { lc($a->{'ondate'}) cmp lc($b->{'ondate'})} @itemList; } else{ @sorted = @itemList; } return \@sorted; } #------------------------------------------------------------------------------