Lines 204-210
sub calculate {
Link Here
|
204 |
my $colfield; |
204 |
my $colfield; |
205 |
my $colorder; |
205 |
my $colorder; |
206 |
if ($column){ |
206 |
if ($column){ |
207 |
$column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); |
207 |
$column = "circulated.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); |
208 |
if($column=~/itemtype/){ |
208 |
if($column=~/itemtype/){ |
209 |
$column = C4::Context->preference('item-level_itypes') ? "items.itype": "biblioitems.itemtype"; |
209 |
$column = C4::Context->preference('item-level_itypes') ? "items.itype": "biblioitems.itemtype"; |
210 |
} |
210 |
} |
Lines 226-242
sub calculate {
Link Here
|
226 |
# loop cols. |
226 |
# loop cols. |
227 |
if ($column eq "Day") { |
227 |
if ($column eq "Day") { |
228 |
#Display by day |
228 |
#Display by day |
229 |
$column = "old_issues.timestamp"; |
229 |
$column = "circulated.timestamp"; |
230 |
$colfield .="dayname($column)"; |
230 |
$colfield .="dayname($column)"; |
231 |
$colorder .="weekday($column)"; |
231 |
$colorder .="weekday($column)"; |
232 |
} elsif ($column eq "Month") { |
232 |
} elsif ($column eq "Month") { |
233 |
#Display by Month |
233 |
#Display by Month |
234 |
$column = "old_issues.timestamp"; |
234 |
$column = "circulated.timestamp"; |
235 |
$colfield .="monthname($column)"; |
235 |
$colfield .="monthname($column)"; |
236 |
$colorder .="month($column)"; |
236 |
$colorder .="month($column)"; |
237 |
} elsif ($column eq "Year") { |
237 |
} elsif ($column eq "Year") { |
238 |
#Display by Year |
238 |
#Display by Year |
239 |
$column = "old_issues.timestamp"; |
239 |
$column = "circulated.timestamp"; |
240 |
$colfield .="Year($column)"; |
240 |
$colfield .="Year($column)"; |
241 |
$colorder .= $column; |
241 |
$colorder .= $column; |
242 |
} else { |
242 |
} else { |
Lines 245-268
sub calculate {
Link Here
|
245 |
} |
245 |
} |
246 |
|
246 |
|
247 |
my $strsth2; |
247 |
my $strsth2; |
248 |
$strsth2 .= "SELECT distinctrow $colfield |
248 |
my $strsth2_filter; |
249 |
FROM `old_issues` |
249 |
$strsth2_filter = ""; |
250 |
LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber |
|
|
251 |
LEFT JOIN items ON old_issues.itemnumber=items.itemnumber |
252 |
LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber |
253 |
WHERE 1"; |
254 |
if (($column=~/issuedate/) or ($column=~/returndate/)){ |
250 |
if (($column=~/issuedate/) or ($column=~/returndate/)){ |
255 |
if ($colfilter[1] and ($colfilter[0])){ |
251 |
if ($colfilter[1] and ($colfilter[0])){ |
256 |
$strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; |
252 |
$strsth2_filter .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; |
257 |
} elsif ($colfilter[1]) { |
253 |
} elsif ($colfilter[1]) { |
258 |
$strsth2 .= " and $column < '$colfilter[1]' " ; |
254 |
$strsth2_filter .= " and $column < '$colfilter[1]' " ; |
259 |
} elsif ($colfilter[0]) { |
255 |
} elsif ($colfilter[0]) { |
260 |
$strsth2 .= " and $column > '$colfilter[0]' " ; |
256 |
$strsth2_filter .= " and $column > '$colfilter[0]' " ; |
261 |
} |
257 |
} |
262 |
} elsif ($colfilter[0]) { |
258 |
} elsif ($colfilter[0]) { |
263 |
$colfilter[0] =~ s/\*/%/g; |
259 |
$colfilter[0] =~ s/\*/%/g; |
264 |
$strsth2 .= " and $column LIKE '$colfilter[0]' " ; |
260 |
$strsth2_filter .= " and $column LIKE '$colfilter[0]' " ; |
265 |
} |
261 |
} |
|
|
262 |
$strsth2 .= "SELECT distinctrow $colfield |
263 |
FROM ( SELECT * FROM `old_issues` WHERE 1 $strsth2_filter |
264 |
UNION ALL |
265 |
SELECT * FROM `issues` WHERE 1 $strsth2_filter ) AS circulated |
266 |
LEFT JOIN borrowers ON borrowers.borrowernumber=circulated.borrowernumber |
267 |
LEFT JOIN items ON circulated.itemnumber=items.itemnumber |
268 |
LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber"; |
266 |
$strsth2 .=" group by $colfield"; |
269 |
$strsth2 .=" group by $colfield"; |
267 |
$strsth2 .=" order by $colorder"; |
270 |
$strsth2 .=" order by $colorder"; |
268 |
|
271 |
|
Lines 302-328
sub calculate {
Link Here
|
302 |
|
305 |
|
303 |
# preparing calculation |
306 |
# preparing calculation |
304 |
my $strcalc ; |
307 |
my $strcalc ; |
|
|
308 |
my $strcalc_filter; |
305 |
|
309 |
|
|
|
310 |
$strcalc_filter = ""; |
311 |
@$filters[0]=~ s/\*/%/g if (@$filters[0]); |
312 |
$strcalc_filter .= " AND issuedate > '" . @$filters[0] ."'" if ( @$filters[0] ); |
313 |
@$filters[1]=~ s/\*/%/g if (@$filters[1]); |
314 |
$strcalc_filter .= " AND issuedate < '" . @$filters[1] ."'" if ( @$filters[1] ); |
315 |
@$filters[2]=~ s/\*/%/g if (@$filters[2]); |
316 |
$strcalc_filter .= " AND returndate > '" . @$filters[2] ."'" if ( @$filters[2] ); |
317 |
@$filters[3]=~ s/\*/%/g if (@$filters[3]); |
318 |
$strcalc_filter .= " AND returndate < '" . @$filters[3] ."'" if ( @$filters[3] ); |
319 |
@$filters[4]=~ s/\*/%/g if (@$filters[4]); |
320 |
$strcalc_filter .= " AND branchcode like '" . @$filters[4] ."'" if ( @$filters[4] ); |
321 |
@$filters[7]=~ s/\*/%/g if (@$filters[7]); |
322 |
$strcalc_filter .= " AND dayname(timestamp) like '" . @$filters[7]."'" if (@$filters[7]); |
323 |
@$filters[8]=~ s/\*/%/g if (@$filters[8]); |
324 |
$strcalc_filter .= " AND monthname(timestamp) like '" . @$filters[8]."'" if (@$filters[8]); |
325 |
@$filters[9]=~ s/\*/%/g if (@$filters[9]); |
326 |
$strcalc_filter .= " AND year(timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] ); |
306 |
# Processing average loanperiods |
327 |
# Processing average loanperiods |
307 |
$strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID"; |
328 |
$strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID"; |
308 |
$strcalc .= " , $colfield " if ($colfield); |
329 |
$strcalc .= " , $colfield " if ($colfield); |
309 |
$strcalc .= " FROM `old_issues` |
330 |
$strcalc .= " FROM ( SELECT * FROM `old_issues` WHERE 1 $strcalc_filter "; |
310 |
LEFT JOIN items USING(itemnumber) |
331 |
$strcalc .= " UNION ALL "; |
|
|
332 |
$strcalc .= " SELECT * FROM `issues` WHERE 1 $strcalc_filter ) AS circulated"; |
333 |
$strcalc .= " LEFT JOIN items USING(itemnumber) |
311 |
LEFT JOIN biblio USING(biblionumber) |
334 |
LEFT JOIN biblio USING(biblionumber) |
312 |
LEFT JOIN biblioitems USING(biblionumber) |
335 |
LEFT JOIN biblioitems USING(biblionumber) |
313 |
LEFT JOIN borrowers USING(borrowernumber) |
336 |
LEFT JOIN borrowers USING(borrowernumber) |
314 |
WHERE 1"; |
337 |
WHERE 1"; |
315 |
|
338 |
|
316 |
@$filters[0]=~ s/\*/%/g if (@$filters[0]); |
|
|
317 |
$strcalc .= " AND old_issues.issuedate > '" . @$filters[0] ."'" if ( @$filters[0] ); |
318 |
@$filters[1]=~ s/\*/%/g if (@$filters[1]); |
319 |
$strcalc .= " AND old_issues.issuedate < '" . @$filters[1] ."'" if ( @$filters[1] ); |
320 |
@$filters[2]=~ s/\*/%/g if (@$filters[2]); |
321 |
$strcalc .= " AND old_issues.returndate > '" . @$filters[2] ."'" if ( @$filters[2] ); |
322 |
@$filters[3]=~ s/\*/%/g if (@$filters[3]); |
323 |
$strcalc .= " AND old_issues.returndate < '" . @$filters[3] ."'" if ( @$filters[3] ); |
324 |
@$filters[4]=~ s/\*/%/g if (@$filters[4]); |
325 |
$strcalc .= " AND old_issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] ); |
326 |
@$filters[5]=~ s/\*/%/g if (@$filters[5]); |
339 |
@$filters[5]=~ s/\*/%/g if (@$filters[5]); |
327 |
if ( @$filters[5] ){ |
340 |
if ( @$filters[5] ){ |
328 |
if(C4::Context->preference('item-level_itypes') ){ |
341 |
if(C4::Context->preference('item-level_itypes') ){ |
Lines 334-345
sub calculate {
Link Here
|
334 |
} |
347 |
} |
335 |
@$filters[6]=~ s/\*/%/g if (@$filters[6]); |
348 |
@$filters[6]=~ s/\*/%/g if (@$filters[6]); |
336 |
$strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] ); |
349 |
$strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] ); |
337 |
@$filters[7]=~ s/\*/%/g if (@$filters[7]); |
|
|
338 |
$strcalc .= " AND dayname(old_issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]); |
339 |
@$filters[8]=~ s/\*/%/g if (@$filters[8]); |
340 |
$strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]); |
341 |
@$filters[9]=~ s/\*/%/g if (@$filters[9]); |
342 |
$strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] ); |
343 |
|
350 |
|
344 |
$strcalc .= " group by biblio.biblionumber"; |
351 |
$strcalc .= " group by biblio.biblionumber"; |
345 |
$strcalc .= ", $colfield" if ($column); |
352 |
$strcalc .= ", $colfield" if ($column); |