|
Lines 152-294
unless ( $enddate ) {
Link Here
|
| 152 |
$enddate = $today + DateTime::Duration->new( days => C4::Context->preference('ConfirmFutureHolds') || 0 ); |
152 |
$enddate = $today + DateTime::Duration->new( days => C4::Context->preference('ConfirmFutureHolds') || 0 ); |
| 153 |
} |
153 |
} |
| 154 |
|
154 |
|
| 155 |
my @reservedata; |
155 |
# building query parameters |
| 156 |
my $dbh = C4::Context->dbh; |
156 |
my %where = ( |
| 157 |
my $sqldatewhere = ""; |
157 |
'reserve.found' => undef, |
|
|
158 |
'reserve.suspend' => 0, |
| 159 |
'itembib.itemlost' => 0, |
| 160 |
'itembib.withdrawn' => 0, |
| 161 |
'itembib.notforloan' => 0, |
| 162 |
'itembib.itemnumber' => { -not_in => \'SELECT itemnumber FROM branchtransfers WHERE datearrived IS NULL' } |
| 163 |
); |
| 164 |
|
| 165 |
# date boundaries |
| 158 |
my $startdate_iso = output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }); |
166 |
my $startdate_iso = output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }); |
| 159 |
my $enddate_iso = output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); |
167 |
my $enddate_iso = output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); |
| 160 |
|
168 |
if ( $startdate_iso && $enddate_iso ){ |
| 161 |
$debug and warn $startdate_iso. "\n" . $enddate_iso; |
169 |
$where{'reserve.reservedate'} = [ -and => { '>=', $startdate_iso }, { '<=', $enddate_iso } ]; |
| 162 |
|
170 |
} elsif ( $startdate_iso ){ |
| 163 |
my @query_params = (); |
171 |
$where{'reserve.reservedate'} = { '>=', $startdate_iso }; |
| 164 |
|
172 |
} elsif ( $enddate_iso ){ |
| 165 |
if ($startdate_iso) { |
173 |
$where{'reserve.reservedate'} = { '<=', $enddate_iso }; |
| 166 |
$sqldatewhere .= " AND reservedate >= ?"; |
|
|
| 167 |
push @query_params, $startdate_iso; |
| 168 |
} |
| 169 |
if ($enddate_iso) { |
| 170 |
$sqldatewhere .= " AND reservedate <= ?"; |
| 171 |
push @query_params, $enddate_iso; |
| 172 |
} |
174 |
} |
| 173 |
|
175 |
|
| 174 |
my $item_type = C4::Context->preference('item-level_itypes') ? "items.itype" : "biblioitems.itemtype"; |
|
|
| 175 |
|
| 176 |
# Bug 21320 |
176 |
# Bug 21320 |
| 177 |
if ( ! C4::Context->preference('AllowHoldsOnDamagedItems') ) { |
177 |
if ( !C4::Context->preference('AllowHoldsOnDamagedItems') ){ |
| 178 |
$sqldatewhere .= " AND damaged = 0"; |
178 |
$where{'itembib.damaged'} = 0; |
| 179 |
} |
179 |
} |
| 180 |
|
180 |
|
| 181 |
my $strsth = |
181 |
if ( C4::Context->preference('IndependentBranches') ){ |
| 182 |
"SELECT min(reservedate) as l_reservedate, |
182 |
$where{'itembib.holdingbranch'} = C4::Context->userenv->{'branch'}; |
| 183 |
reserves.reserve_id, |
|
|
| 184 |
reserves.borrowernumber as borrowernumber, |
| 185 |
|
| 186 |
GROUP_CONCAT(DISTINCT items.holdingbranch |
| 187 |
ORDER BY items.itemnumber SEPARATOR '|') l_holdingbranch, |
| 188 |
reserves.biblionumber, |
| 189 |
reserves.branchcode as l_branch, |
| 190 |
reserves.itemnumber, |
| 191 |
items.holdingbranch, |
| 192 |
items.homebranch, |
| 193 |
GROUP_CONCAT(DISTINCT $item_type |
| 194 |
ORDER BY items.itemnumber SEPARATOR '|') l_item_type, |
| 195 |
GROUP_CONCAT(DISTINCT items.location |
| 196 |
ORDER BY items.itemnumber SEPARATOR '|') l_location, |
| 197 |
GROUP_CONCAT(DISTINCT items.itemcallnumber |
| 198 |
ORDER BY items.itemnumber SEPARATOR '|') l_itemcallnumber, |
| 199 |
GROUP_CONCAT(DISTINCT items.enumchron |
| 200 |
ORDER BY items.itemnumber SEPARATOR '|') l_enumchron, |
| 201 |
GROUP_CONCAT(DISTINCT items.copynumber |
| 202 |
ORDER BY items.itemnumber SEPARATOR '|') l_copynumber, |
| 203 |
GROUP_CONCAT(DISTINCT items.barcode |
| 204 |
ORDER BY items.itemnumber SEPARATOR '|') l_barcode, |
| 205 |
biblio.title, |
| 206 |
biblio.copyrightdate, |
| 207 |
biblioitems.publicationyear, |
| 208 |
biblio.subtitle, |
| 209 |
biblio.medium, |
| 210 |
biblio.part_number, |
| 211 |
biblio.part_name, |
| 212 |
biblio.author, |
| 213 |
biblioitems.editionstatement, |
| 214 |
count(DISTINCT items.itemnumber) as icount, |
| 215 |
count(DISTINCT reserves.borrowernumber) as rcount, |
| 216 |
borrowers.firstname, |
| 217 |
borrowers.surname |
| 218 |
FROM reserves |
| 219 |
LEFT JOIN items ON items.biblionumber=reserves.biblionumber |
| 220 |
LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber |
| 221 |
LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber |
| 222 |
LEFT JOIN branchtransfers ON items.itemnumber=branchtransfers.itemnumber |
| 223 |
LEFT JOIN issues ON items.itemnumber=issues.itemnumber |
| 224 |
LEFT JOIN borrowers ON reserves.borrowernumber=borrowers.borrowernumber |
| 225 |
LEFT JOIN circulation_rules ON ( items.itype=circulation_rules.itemtype AND rule_name = 'holdallowed' AND circulation_rules.branchcode IS NULL AND circulation_rules.categorycode IS NULL ) |
| 226 |
WHERE |
| 227 |
reserves.found IS NULL |
| 228 |
$sqldatewhere |
| 229 |
AND (reserves.itemnumber IS NULL OR reserves.itemnumber = items.itemnumber) |
| 230 |
AND items.itemnumber NOT IN (SELECT itemnumber FROM branchtransfers where datearrived IS NULL) |
| 231 |
AND items.itemnumber NOT IN (SELECT itemnumber FROM reserves WHERE found IS NOT NULL AND itemnumber IS NOT NULL) |
| 232 |
AND issues.itemnumber IS NULL |
| 233 |
AND reserves.priority <> 0 |
| 234 |
AND reserves.suspend = 0 |
| 235 |
AND notforloan = 0 AND itemlost = 0 AND withdrawn = 0 |
| 236 |
AND ( circulation_rules.rule_value IS NULL OR circulation_rules.rule_value != 0 ) |
| 237 |
"; |
| 238 |
# GROUP BY reserves.biblionumber allows only items that are not checked out, else multiples occur when |
| 239 |
# multiple patrons have a hold on an item |
| 240 |
#FIXME "found IS NOT NULL AND itemnumber IS NOT NULL" is just a workaround: see BZ 25726 |
| 241 |
|
| 242 |
if (C4::Context->preference('IndependentBranches')){ |
| 243 |
$strsth .= " AND items.holdingbranch=? "; |
| 244 |
push @query_params, C4::Context->userenv->{'branch'}; |
| 245 |
} |
183 |
} |
| 246 |
$strsth .= " GROUP BY reserves.biblionumber ORDER BY biblio.title "; |
184 |
|
| 247 |
|
185 |
# get all distinct unfulfilled reserves |
| 248 |
my $sth = $dbh->prepare($strsth); |
186 |
my @distinct_holds = Koha::Holds->search( |
| 249 |
$sth->execute(@query_params); |
187 |
{ %where }, |
| 250 |
|
188 |
{ join => 'itembib', group_by => 'reserve.biblionumber', alias => 'reserve' } |
| 251 |
while ( my $data = $sth->fetchrow_hashref ) { |
189 |
); |
| 252 |
push( |
190 |
|
| 253 |
@reservedata, { |
191 |
# make final reserves hash and fill with info |
| 254 |
reservedate => $data->{l_reservedate}, |
192 |
my $reserves; |
| 255 |
firstname => $data->{firstname} || '', |
193 |
foreach my $dh ( @distinct_holds ){ |
| 256 |
surname => $data->{surname}, |
194 |
|
| 257 |
title => $data->{title}, |
195 |
my $bibnum = $dh->biblionumber; |
| 258 |
editionstatement => $data->{editionstatement}, |
196 |
|
| 259 |
subtitle => $data->{subtitle}, |
197 |
my @items = Koha::Items->search({ biblionumber => $bibnum }); |
| 260 |
medium => $data->{medium}, |
198 |
foreach my $i ( @items ){ |
| 261 |
part_number => $data->{part_number}, |
199 |
if ( $i->checkout ){ |
| 262 |
part_name => $data->{part_name}, |
200 |
next; |
| 263 |
author => $data->{author}, |
201 |
} |
| 264 |
borrowernumber => $data->{borrowernumber}, |
202 |
} |
| 265 |
biblionumber => $data->{biblionumber}, |
203 |
|
| 266 |
holdingbranches => [split('\|', $data->{l_holdingbranch})], |
204 |
my @branchtransfers = map { $_->itemnumber } Koha::Item::Transfers->search({ datearrived => undef }, { columns => [ 'itemnumber' ], collapse => 1 }); |
| 267 |
branch => $data->{l_branch}, |
205 |
my @issues = map { $_->itemnumber } Koha::Checkouts->search({}, { columns => [ 'itemnumber' ], collapse => 1 }); |
| 268 |
itemcallnumber => [split('\|', $data->{l_itemcallnumber})], |
206 |
|
| 269 |
enumchron => [split('\|', $data->{l_enumchron})], |
207 |
# get available item types for each biblio |
| 270 |
copyno => [split('\|', $data->{l_copynumber})], |
208 |
my $res_itemtypes; |
| 271 |
barcode => [split('\|', $data->{l_barcode})], |
209 |
if ( C4::Context->preference('item-level_itypes') ){ |
| 272 |
count => $data->{icount}, |
210 |
$res_itemtypes = Koha::Items->search( |
| 273 |
rcount => $data->{rcount}, |
211 |
{ biblionumber => $bibnum, |
| 274 |
pullcount => $data->{icount} <= $data->{rcount} ? $data->{icount} : $data->{rcount}, |
212 |
itype => { '!=', undef }, |
| 275 |
itemTypes => [split('\|', $data->{l_item_type})], |
213 |
itemlost => 0, |
| 276 |
locations => [split('\|', $data->{l_location})], |
214 |
withdrawn => 0, |
| 277 |
reserve_id => $data->{reserve_id}, |
215 |
notforloan => 0, |
| 278 |
holdingbranch => $data->{holdingbranch}, |
216 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 279 |
homebranch => $data->{homebranch}, |
217 |
}, |
| 280 |
itemnumber => $data->{itemnumber}, |
218 |
{ columns => 'itype', |
| 281 |
publicationyear => C4::Context->preference('marcflavour') eq "MARC21" ? $data->{copyrightdate} : $data->{publicationyear}, |
219 |
distinct => 1, |
|
|
220 |
} |
| 221 |
); |
| 222 |
} else { |
| 223 |
my $res_itemtypes = Koha::Biblioitems->search( |
| 224 |
{ biblionumber => $bibnum, itemtype => { '!=', undef } }, |
| 225 |
{ columns => 'itemtype', |
| 226 |
distinct => 1, |
| 227 |
} |
| 228 |
); |
| 229 |
} |
| 230 |
$reserves->{$bibnum}->{itemtypes} = $res_itemtypes; |
| 231 |
|
| 232 |
# get available locations for each biblio |
| 233 |
my $res_locations = Koha::Items->search( |
| 234 |
{ biblionumber => $bibnum, |
| 235 |
location => { '!=', undef }, |
| 236 |
itemlost => 0, |
| 237 |
withdrawn => 0, |
| 238 |
notforloan => 0, |
| 239 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 240 |
}, |
| 241 |
{ columns => 'location', |
| 242 |
distinct => 1, |
| 282 |
} |
243 |
} |
| 283 |
); |
244 |
); |
|
|
245 |
$reserves->{$bibnum}->{locations} = $res_locations; |
| 246 |
|
| 247 |
# get available callnumbers for each biblio |
| 248 |
my $res_callnumbers = Koha::Items->search( |
| 249 |
{ biblionumber => $bibnum, |
| 250 |
itemcallnumber => { '!=', undef }, |
| 251 |
itemlost => 0, |
| 252 |
withdrawn => 0, |
| 253 |
notforloan => 0, |
| 254 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 255 |
}, |
| 256 |
{ columns => 'itemcallnumber', |
| 257 |
distinct => 1, |
| 258 |
} |
| 259 |
); |
| 260 |
$reserves->{$bibnum}->{callnumbers} = $res_callnumbers; |
| 261 |
|
| 262 |
# get available enumchrons for each biblio |
| 263 |
my $res_enumchrons = Koha::Items->search( |
| 264 |
{ biblionumber => $bibnum, |
| 265 |
enumchron => { '!=', undef }, |
| 266 |
itemlost => 0, |
| 267 |
withdrawn => 0, |
| 268 |
notforloan => 0, |
| 269 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 270 |
}, |
| 271 |
{ columns => 'enumchron', |
| 272 |
distinct => 1, |
| 273 |
} |
| 274 |
); |
| 275 |
$reserves->{$bibnum}->{enumchrons} = $res_enumchrons; |
| 276 |
|
| 277 |
# get available copynumbers for each biblio |
| 278 |
my $res_copynumbers = Koha::Items->search( |
| 279 |
{ biblionumber => $bibnum, |
| 280 |
copynumber => { '!=', undef }, |
| 281 |
itemlost => 0, |
| 282 |
withdrawn => 0, |
| 283 |
notforloan => 0, |
| 284 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 285 |
}, |
| 286 |
{ columns => 'copynumber', |
| 287 |
distinct => 1, |
| 288 |
} |
| 289 |
); |
| 290 |
$reserves->{$bibnum}->{copynumbers} = $res_copynumbers; |
| 291 |
|
| 292 |
# get available barcodes for each biblio |
| 293 |
my $res_barcodes = Koha::Items->search( |
| 294 |
{ biblionumber => $bibnum, |
| 295 |
barcode => { '!=', undef }, |
| 296 |
itemlost => 0, |
| 297 |
withdrawn => 0, |
| 298 |
notforloan => 0, |
| 299 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 300 |
}, |
| 301 |
{ columns => 'barcode', |
| 302 |
distinct => 1, |
| 303 |
} |
| 304 |
); |
| 305 |
$reserves->{$bibnum}->{barcodes} = $res_barcodes; |
| 306 |
|
| 307 |
# get available holding branches for each biblio |
| 308 |
my $res_holdingbranches = Koha::Items->search( |
| 309 |
{ biblionumber => $bibnum, |
| 310 |
holdingbranch => { '!=', undef }, |
| 311 |
itemlost => 0, |
| 312 |
withdrawn => 0, |
| 313 |
notforloan => 0, |
| 314 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 315 |
}, |
| 316 |
{ columns => 'holdingbranch', |
| 317 |
distinct => 1, |
| 318 |
} |
| 319 |
); |
| 320 |
$reserves->{$bibnum}->{holdingbranches} = $res_holdingbranches; |
| 321 |
|
| 322 |
# items available |
| 323 |
my $items_count = Koha::Items->search( |
| 324 |
{ biblionumber => $bibnum, |
| 325 |
itemlost => 0, |
| 326 |
withdrawn => 0, |
| 327 |
notforloan => 0, |
| 328 |
itemnumber => { -not_in => [ @branchtransfers, @issues ] }, |
| 329 |
}, |
| 330 |
{ select => [ { distinct => 'itemnumber' } ] } |
| 331 |
)->count; |
| 332 |
$reserves->{$bibnum}->{items_count} = $items_count; |
| 333 |
|
| 334 |
# patrons with holds |
| 335 |
my $patrons_count = Koha::Holds->search( |
| 336 |
{ biblionumber => $bibnum }, |
| 337 |
{ select => [ { distinct => 'borrowernumber' } ] } |
| 338 |
)->count; |
| 339 |
$reserves->{$bibnum}->{patrons_count} = $patrons_count; |
| 340 |
|
| 341 |
my $pull_count = $items_count <= $patrons_count ? $items_count : $patrons_count; |
| 342 |
if ( $pull_count == 0 ) { |
| 343 |
delete($reserves->{$bibnum}); |
| 344 |
next; |
| 345 |
} |
| 346 |
$reserves->{$bibnum}->{pull_count} = $pull_count; |
| 347 |
|
| 348 |
# get other relevant information |
| 349 |
my $res_info = Koha::Holds->search( |
| 350 |
{ 'reserve.biblionumber' => $bibnum, %where }, |
| 351 |
{ prefetch => [ 'borrowernumber', 'itembib', 'biblio' ], |
| 352 |
order_by => 'reserve.reservedate', |
| 353 |
alias => 'reserve' |
| 354 |
} |
| 355 |
)->next; # get first item in results |
| 356 |
$reserves->{$bibnum}->{borrower} = $res_info->borrower; |
| 357 |
$reserves->{$bibnum}->{item} = $res_info->item; |
| 358 |
$reserves->{$bibnum}->{biblio} = $res_info->biblio; |
| 359 |
$reserves->{$bibnum}->{reserve} = $res_info; |
| 284 |
} |
360 |
} |
| 285 |
$sth->finish; |
|
|
| 286 |
|
361 |
|
| 287 |
$template->param( |
362 |
$template->param( |
| 288 |
todaysdate => $today, |
363 |
todaysdate => $today, |
| 289 |
from => $startdate, |
364 |
from => $startdate, |
| 290 |
to => $enddate, |
365 |
to => $enddate, |
| 291 |
reserveloop => \@reservedata, |
366 |
reserves => $reserves, |
| 292 |
"BiblioDefaultView".C4::Context->preference("BiblioDefaultView") => 1, |
367 |
"BiblioDefaultView".C4::Context->preference("BiblioDefaultView") => 1, |
| 293 |
HoldsToPullStartDate => C4::Context->preference('HoldsToPullStartDate') || PULL_INTERVAL, |
368 |
HoldsToPullStartDate => C4::Context->preference('HoldsToPullStartDate') || PULL_INTERVAL, |
| 294 |
HoldsToPullEndDate => C4::Context->preference('ConfirmFutureHolds') || 0, |
369 |
HoldsToPullEndDate => C4::Context->preference('ConfirmFutureHolds') || 0, |