3 # Copyright 2010 BibLibre
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
31 use Koha
::AuthorisedValues
;
34 use Koha
::Patron
::Categories
;
35 use List
::MoreUtils qw
/any/;
40 reports/reserve_stats.pl
44 Plugin that shows reserve stats
48 # my $debug = 1; # override for now.
50 my $fullreportname = "reports/reserves_stats.tt";
51 my $do_it = $input->param('do_it');
52 my $line = $input->param("Line");
53 my $column = $input->param("Column");
54 my $calc = $input->param("Cellvalue");
55 my $output = $input->param("output");
56 my $basename = $input->param("basename");
57 my $hash_params = $input->Vars;
59 foreach my $filter (grep {$_ =~/^filter/} keys %$hash_params){
60 my $filterstring=$filter;
61 $filterstring=~s/^filter_//g;
62 $$filter_hashref{$filterstring}=$$hash_params{$filter} if (defined $$hash_params{$filter} && $$hash_params{$filter} ne "");
64 my ($template, $borrowernumber, $cookie) = get_template_and_user
({
65 template_name
=> $fullreportname,
69 flagsrequired
=> {reports
=> '*'},
72 our $sep = $input->param("sep") || '';
73 $sep = "\t" if ($sep eq 'tabulation');
74 $template->param(do_it
=> $do_it,
77 my $itemtypes = GetItemTypes
();
78 my @patron_categories = Koha
::Patron
::Categories
->search_limited({}, {order_by
=> ['description']});
80 my $locations = { map { ( $_->{authorised_value
} => $_->{lib
} ) } Koha
::AuthorisedValues
->get_descriptions_by_koha_field( { frameworkcode
=> '', kohafield
=> 'items.location' }, { order_by
=> ['description'] } ) };
81 my $ccodes = { map { ( $_->{authorised_value
} => $_->{lib
} ) } Koha
::AuthorisedValues
->get_descriptions_by_koha_field( { frameworkcode
=> '', kohafield
=> 'items.ccode' }, { order_by
=> ['description'] } ) };
83 my $Bsort1 = GetAuthorisedValues
("Bsort1");
84 my $Bsort2 = GetAuthorisedValues
("Bsort2");
85 my ($hassort1,$hassort2);
86 $hassort1=1 if $Bsort1;
87 $hassort2=1 if $Bsort2;
92 my $results = calculate
($line, $column, $calc, $filter_hashref);
93 if ($output eq "screen"){
94 # Printing results to screen
95 $template->param(mainloop
=> $results);
96 output_html_with_http_headers
$input, $cookie, $template->output;
98 # Printing to a csv file
99 print $input->header(-type
=> 'application/vnd.sun.xml.calc',
100 -encoding
=> 'utf-8',
101 -attachment
=>"$basename.csv",
102 -filename
=>"$basename.csv" );
103 my $cols = @
$results[0]->{loopcol
};
104 my $lines = @
$results[0]->{looprow
};
106 print @
$results[0]->{line
} ."/". @
$results[0]->{column
} .$sep;
108 foreach my $col ( @
$cols ) {
109 print $col->{coltitle
}.$sep;
113 foreach my $line ( @
$lines ) {
114 my $x = $line->{loopcell
};
115 print $line->{rowtitle
}.$sep;
116 print map {$_->{value
}.$sep} @
$x;
117 print $line->{totalrow
}, "\n";
121 $cols = @
$results[0]->{loopfooter
};
122 print map {$sep.$_->{totalcol
}} @
$cols;
123 print $sep.@
$results[0]->{total
};
125 exit; # exit either way after $do_it
128 my $dbh = C4
::Context
->dbh;
133 # create itemtype arrayref for <select>.
135 for my $itype ( sort {$itemtypes->{$a}->{translated_description
} cmp $itemtypes->{$b}->{translated_description
}} keys(%$itemtypes)) {
136 push @itemtypeloop, { code
=> $itype , description
=> $itemtypes->{$itype}->{translated_description
} } ;
141 foreach (sort keys %$locations) {
142 push @locations, { code
=> $_, description
=> "$_ - " . $locations->{$_} };
146 foreach (sort {$ccodes->{$a} cmp $ccodes->{$b}} keys %$ccodes) {
147 push @ccodes, { code
=> $_, description
=> $ccodes->{$_} };
151 my $CGIextChoice = ( 'CSV' ); # FIXME translation
152 my $CGIsepChoice=GetDelimiterChoices
;
155 categoryloop
=> \
@patron_categories,
156 itemtypeloop
=> \
@itemtypeloop,
157 locationloop
=> \
@locations,
158 ccodeloop
=> \
@ccodes,
159 hassort1
=> $hassort1,
160 hassort2
=> $hassort2,
163 CGIextChoice
=> $CGIextChoice,
164 CGIsepChoice
=> $CGIsepChoice,
166 output_html_with_http_headers
$input, $cookie, $template->output;
169 my ($linefield, $colfield, $process, $filters_hashref) = @_;
177 my $dbh = C4
::Context
->dbh;
183 foreach my $filter ( keys %$filters_hashref ) {
184 $filters_hashref->{$filter} =~ s/\*/%/;
185 if ( $filter =~ /date/ ) {
186 $filters_hashref->{$filter} =
187 eval { output_pref
( { dt
=> dt_from_string
( $filters_hashref->{$filter} ), dateonly
=> 1, dateformat
=> 'iso' }); };
197 ?
eval { output_pref
( { dt
=> dt_from_string
( $filters_hashref->{$_} ), dateonly
=> 1 }); }
198 : $filters_hashref->{$_}
201 } sort keys %$filters_hashref;
206 my $linesql=changeifreservestatus
($linefield);
207 my $colsql=changeifreservestatus
($colfield);
208 #Initialization of cell values.....
210 # preparing calculation
211 my $strcalc = "(SELECT $linesql line, $colsql col, ";
212 $strcalc .= ($process == 1) ?
" COUNT(*) calculation" :
213 ($process == 2) ?
"(COUNT(DISTINCT reserves.borrowernumber)) calculation" :
214 ($process == 3) ?
"(COUNT(DISTINCT reserves.itemnumber)) calculation" :
215 ($process == 4) ?
"(COUNT(DISTINCT reserves.biblionumber)) calculation" : '*';
217 FROM (select * from reserves union select * from old_reserves) reserves
218 LEFT JOIN borrowers USING (borrowernumber)
220 $strcalc .= "LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber "
221 if ($linefield =~ /^biblio\./ or $colfield =~ /^biblio\./ or any
{$_=~/biblio/}keys %$filters_hashref);
222 $strcalc .= "LEFT JOIN items ON reserves.itemnumber=items.itemnumber "
223 if ($linefield =~ /^items\./ or $colfield =~ /^items\./ or any
{$_=~/items/}keys %$filters_hashref);
229 ($debug) and print STDERR Dump
($filters_hashref);
230 foreach my $filter (keys %$filters_hashref){
232 my $stringfield=$filter;
233 $stringfield=~s/\_[a-z_]+$//;
235 $string=$stringfield;
237 elsif ($filter=~/_or/){
238 push @sqlor, qq{( }.changeifreservestatus
($filter)." = ? ) ";
239 push @sqlorparams, $$filters_hashref{$filter};
241 elsif ($filter=~/_endex$/){
242 $string = " $stringfield < ? ";
244 elsif ($filter=~/_end$/){
245 $string = " $stringfield <= ? ";
247 elsif ($filter=~/_begin$/){
248 $string = " $stringfield >= ? ";
251 $string = " $stringfield LIKE ? ";
254 push @sqlwhere, $string;
255 push @sqlparams, $$filters_hashref{$filter};
259 $strcalc .= " WHERE ".join(" AND ",@sqlwhere) if (@sqlwhere);
260 $strcalc .= " AND (".join(" OR ",@sqlor).")" if (@sqlor);
261 $strcalc .= " GROUP BY line, col )";
262 ($debug) and print STDERR
$strcalc;
263 my $dbcalc = $dbh->prepare($strcalc);
264 push @loopfilter, {crit
=>'SQL =', sql
=>1, filter
=>$strcalc};
265 @sqlparams=(@sqlparams,@sqlorparams);
266 $dbcalc->execute(@sqlparams);
267 my ($emptycol,$emptyrow);
268 my $data = $dbcalc->fetchall_hashref([qw(line col)]);
270 foreach my $row (keys %$data){
271 push @loopline, $row;
272 foreach my $col (keys %{$$data{$row}}){
273 $$data{$row}{totalrow
}+=$$data{$row}{$col}{calculation
};
274 $grantotal+=$$data{$row}{$col}{calculation
};
278 my $urlbase="do_it=1&".join("&",map{"filter_$_=$$filters_hashref{$_}"} keys %$filters_hashref);
279 foreach my $row (sort @loopline) {
281 #@loopcol ensures the order for columns is common with column titles
282 # and the number matches the number of columns
283 foreach my $col (sort keys %cols_hash) {
284 push @loopcell, {value
=>( $$data{$row}{$col}{calculation
} or ""),
285 # url_complement=>($urlbase=~/&$/?$urlbase."&":$urlbase)."filter_$linefield=$row&filter_$colfield=$col"
289 'rowtitle_display' => display_value
($linefield,$row),
291 'loopcell' => \
@loopcell,
292 'totalrow' => $$data{$row}{totalrow
}
295 for my $col ( sort keys %cols_hash ) {
297 foreach my $row (@loopline) {
298 $total += $data->{$row}{$col}{calculation
} if $data->{$row}{$col}{calculation
};
299 $debug and warn "value added ".$$data{$row}{$col}{calculation
}. "for line ".$row;
301 push @loopfooter, {'totalcol' => $total};
302 push @loopcol, {'coltitle' => $col,
303 coltitle_display
=>display_value
($colfield,$col)};
305 # the header of the table
306 $globalline{loopfilter
}=\
@loopfilter;
307 # the core of the table
308 $globalline{looprow
} = \
@looprow;
309 $globalline{loopcol
} = \
@loopcol;
310 # # the foot (totals by borrower type)
311 $globalline{loopfooter
} = \
@loopfooter;
312 $globalline{total
} = $grantotal;
313 $globalline{line
} = $linefield;
314 $globalline{column
} = $colfield;
315 return [(\
%globalline)];
319 my ( $crit, $value ) = @_;
320 my $locations = { map { ( $_->{authorised_value
} => $_->{lib
} ) } Koha
::AuthorisedValues
->get_descriptions_by_koha_field( { frameworkcode
=> '', kohafield
=> 'items.location' }, { order_by
=> ['description'] } ) };
321 my $ccodes = { map { ( $_->{authorised_value
} => $_->{lib
} ) } Koha
::AuthorisedValues
->get_descriptions_by_koha_field( { frameworkcode
=> '', kohafield
=> 'items.ccode' }, { order_by
=> ['description'] } ) };
322 my $itemtypes = GetItemTypes
();
323 my $Bsort1 = GetAuthorisedValues
("Bsort1");
324 my $Bsort2 = GetAuthorisedValues
("Bsort2");
326 ( $crit =~ /ccode/ ) ?
$ccodes->{$value}
327 : ( $crit =~ /location/ ) ?
$locations->{$value}
328 : ( $crit =~ /itemtype/ ) ?
$itemtypes->{$value}->{description
}
329 : ( $crit =~ /branch/ ) ? Koha
::Libraries
->find($value)->branchname
330 : ( $crit =~ /reservestatus/ ) ? reservestatushuman
($value)
331 : $value; # default fallback
332 if ($crit =~ /sort1/) {
334 ($value eq $_->{authorised_value
}) or next;
335 $display_value = $_->{lib
} and last;
338 elsif ($crit =~ /sort2/) {
340 ($value eq $_->{authorised_value
}) or next;
341 $display_value = $_->{lib
} and last;
344 elsif ( $crit =~ /category/ ) {
345 my @patron_categories = Koha
::Patron
::Categories
->search_limited({}, {order_by
=> ['description']});
346 foreach my $patron_category ( @patron_categories ) {
347 ( $value eq $patron_category->categorycode ) or next;
348 $display_value = $patron_category->description and last;
351 return $display_value;
354 sub reservestatushuman
{
367 sub changeifreservestatus
{
369 ($val=~/reservestatus/
371 when priority
>0 then
1
374 when found
='f' then
4
377 when cancellationdate is null then
3