2 #-----------------------------------
3 # Script Name: build_holds_queue.pl
4 # Description: builds a holds queue in the tmp_holdsqueue table
5 #-----------------------------------
6 # FIXME: add command-line options for verbosity and summary
7 # FIXME: expand perldoc, explain intended logic
8 # FIXME: refactor all subroutines into C4 for testability
13 # find Koha's Perl modules
14 # test carefully before changing this
16 eval { require "$FindBin::Bin/../kohalib.pl" };
27 use List::Util qw(shuffle);
29 my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests();
31 my $dbh = C4::Context->dbh;
32 $dbh->do("DELETE FROM tmp_holdsqueue"); # clear the old table for new info
33 $dbh->do("DELETE FROM hold_fill_targets");
36 my $total_requests = 0;
37 my $total_available_items = 0;
38 my $num_items_mapped = 0;
40 my @branches_to_use = _get_branches_to_pull_from();
42 foreach my $biblionumber (@$bibs_with_pending_requests) {
44 my $hold_requests = GetPendingHoldRequestsForBib($biblionumber);
45 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber, @branches_to_use);
46 $total_requests += scalar(@$hold_requests);
47 $total_available_items += scalar(@$available_items);
48 my $item_map = MapItemsToHoldRequests($hold_requests, $available_items, @branches_to_use);
50 (defined($item_map)) or next;
52 my $item_map_size = scalar(keys %$item_map);
53 $num_items_mapped += $item_map_size;
54 CreatePicklistFromItemMap($item_map);
55 AddToHoldTargetMap($item_map);
56 if (($item_map_size < scalar(@$hold_requests )) and
57 ($item_map_size < scalar(@$available_items))) {
58 # DOUBLE CHECK, but this is probably OK - unfilled item-level requests
60 #warn "unfilled requests for $biblionumber";
61 #warn Dumper($hold_requests), Dumper($available_items), Dumper($item_map);
69 =head2 GetBibsWithPendingHoldRequests
71 my $biblionumber_aref = GetBibsWithPendingHoldRequests();
73 Return an arrayref of the biblionumbers of all bibs
74 that have one or more unfilled hold requests.
78 sub GetBibsWithPendingHoldRequests {
79 my $dbh = C4::Context->dbh;
81 my $bib_query = "SELECT DISTINCT biblionumber
85 AND reservedate <= CURRENT_DATE()";
86 my $sth = $dbh->prepare($bib_query);
89 my $biblionumbers = $sth->fetchall_arrayref();
91 return [ map { $_->[0] } @$biblionumbers ];
94 =head2 GetPendingHoldRequestsForBib
96 my $requests = GetPendingHoldRequestsForBib($biblionumber);
98 Returns an arrayref of hashrefs to pending, unfilled hold requests
99 on the bib identified by $biblionumber. The following keys
100 are present in each hashref:
111 The arrayref is sorted in order of increasing priority.
115 sub GetPendingHoldRequestsForBib {
116 my $biblionumber = shift;
118 my $dbh = C4::Context->dbh;
120 my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, reserves.branchcode,
121 reservedate, reservenotes, borrowers.branchcode AS borrowerbranch
123 JOIN borrowers USING (borrowernumber)
124 WHERE biblionumber = ?
127 AND reservedate <= CURRENT_DATE()
129 my $sth = $dbh->prepare($request_query);
130 $sth->execute($biblionumber);
132 my $requests = $sth->fetchall_arrayref({});
137 =head2 GetItemsAvailableToFillHoldRequestsForBib
139 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
141 Returns an arrayref of items available to fill hold requests
142 for the bib identified by C<$biblionumber>. An item is available
143 to fill a hold request if and only if:
146 * it is not withdrawn
147 * it is not marked notforloan
148 * it is not currently in transit
150 * it is not sitting on the hold shelf
154 sub GetItemsAvailableToFillHoldRequestsForBib {
155 my $biblionumber = shift;
156 my @branches_to_use = @_;
158 my $dbh = C4::Context->dbh;
159 my $items_query = "SELECT itemnumber, homebranch, holdingbranch, itemtypes.itemtype AS itype
162 if (C4::Context->preference('item-level_itypes')) {
163 $items_query .= "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
165 $items_query .= "JOIN biblioitems USING (biblioitemnumber)
166 LEFT JOIN itemtypes USING (itemtype) ";
168 $items_query .= "WHERE items.notforloan = 0
169 AND holdingbranch IS NOT NULL
172 $items_query .= " AND damaged = 0 " unless C4::Context->preference('AllowHoldsOnDamagedItems');
173 $items_query .= " AND items.onloan IS NULL
174 AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0)
175 AND itemnumber NOT IN (
178 WHERE biblionumber = ?
179 AND itemnumber IS NOT NULL
180 AND (found IS NOT NULL OR priority = 0)
182 AND items.biblionumber = ?";
183 my @params = ($biblionumber, $biblionumber);
184 if ($#branches_to_use > -1) {
185 $items_query .= " AND holdingbranch IN (" . join (",", map { "?" } @branches_to_use) . ")";
186 push @params, @branches_to_use;
188 my $sth = $dbh->prepare($items_query);
189 $sth->execute(@params);
191 my $items = $sth->fetchall_arrayref({});
192 $items = [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ];
193 map { my $rule = GetBranchItemRule($_->{homebranch}, $_->{itype}); $_->{holdallowed} = $rule->{holdallowed}; $rule->{holdallowed} != 0 } @$items;
194 return [ grep { $_->{holdallowed} != 0 } @$items ];
197 =head2 MapItemsToHoldRequests
199 MapItemsToHoldRequests($hold_requests, $available_items);
203 sub MapItemsToHoldRequests {
204 my $hold_requests = shift;
205 my $available_items = shift;
206 my @branches_to_use = @_;
208 # handle trival cases
209 return unless scalar(@$hold_requests) > 0;
210 return unless scalar(@$available_items) > 0;
212 # identify item-level requests
213 my %specific_items_requested = map { $_->{itemnumber} => 1 }
214 grep { defined($_->{itemnumber}) }
217 # group available items by itemnumber
218 my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
220 # items already allocated
221 my %allocated_items = ();
223 # map of items to hold requests
226 # figure out which item-level requests can be filled
227 my $num_items_remaining = scalar(@$available_items);
228 foreach my $request (@$hold_requests) {
229 last if $num_items_remaining == 0;
231 # is this an item-level request?
232 if (defined($request->{itemnumber})) {
233 # fill it if possible; if not skip it
234 if (exists $items_by_itemnumber{$request->{itemnumber}} and
235 not exists $allocated_items{$request->{itemnumber}}) {
236 $item_map{$request->{itemnumber}} = {
237 borrowernumber => $request->{borrowernumber},
238 biblionumber => $request->{biblionumber},
239 holdingbranch => $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch},
240 pickup_branch => $request->{branchcode},
242 reservedate => $request->{reservedate},
243 reservenotes => $request->{reservenotes},
245 $allocated_items{$request->{itemnumber}}++;
246 $num_items_remaining--;
249 # it's title-level request that will take up one item
250 $num_items_remaining--;
254 # group available items by branch
255 my %items_by_branch = ();
256 foreach my $item (@$available_items) {
257 push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} };
260 # now handle the title-level requests
261 $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items);
262 foreach my $request (@$hold_requests) {
263 last if $num_items_remaining <= 0;
264 next if defined($request->{itemnumber}); # already handled these
266 # look for local match first
267 my $pickup_branch = $request->{branchcode};
268 if (exists $items_by_branch{$pickup_branch} and
269 not ($items_by_branch{$pickup_branch}->[0]->{holdallowed} == 1 and
270 $request->{borrowerbranch} ne $items_by_branch{$pickup_branch}->[0]->{homebranch})
272 my $item = pop @{ $items_by_branch{$pickup_branch} };
273 delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0;
274 $item_map{$item->{itemnumber}} = {
275 borrowernumber => $request->{borrowernumber},
276 biblionumber => $request->{biblionumber},
277 holdingbranch => $pickup_branch,
278 pickup_branch => $pickup_branch,
280 reservedate => $request->{reservedate},
281 reservenotes => $request->{reservenotes},
283 $num_items_remaining--;
285 my @pull_branches = ();
286 if ($#branches_to_use > -1) {
287 @pull_branches = @branches_to_use;
289 @pull_branches = sort keys %items_by_branch;
291 foreach my $branch (@pull_branches) {
292 next unless exists $items_by_branch{$branch} and
293 not ($items_by_branch{$branch}->[0]->{holdallowed} == 1 and
294 $request->{borrowerbranch} ne $items_by_branch{$branch}->[0]->{homebranch});
295 my $item = pop @{ $items_by_branch{$branch} };
296 delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0;
297 $item_map{$item->{itemnumber}} = {
298 borrowernumber => $request->{borrowernumber},
299 biblionumber => $request->{biblionumber},
300 holdingbranch => $branch,
301 pickup_branch => $pickup_branch,
303 reservedate => $request->{reservedate},
304 reservenotes => $request->{reservenotes},
306 $num_items_remaining--;
314 =head2 CreatePickListFromItemMap
318 sub CreatePicklistFromItemMap {
319 my $item_map = shift;
321 my $dbh = C4::Context->dbh;
323 my $sth_load=$dbh->prepare("
324 INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,
325 cardnumber,reservedate,title, itemcallnumber,
326 holdingbranch,pickbranch,notes, item_level_request)
327 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
330 foreach my $itemnumber (sort keys %$item_map) {
331 my $mapped_item = $item_map->{$itemnumber};
332 my $biblionumber = $mapped_item->{biblionumber};
333 my $borrowernumber = $mapped_item->{borrowernumber};
334 my $pickbranch = $mapped_item->{pickup_branch};
335 my $holdingbranch = $mapped_item->{holdingbranch};
336 my $reservedate = $mapped_item->{reservedate};
337 my $reservenotes = $mapped_item->{reservenotes};
338 my $item_level = $mapped_item->{item_level};
340 my $item = GetItem($itemnumber);
341 my $barcode = $item->{barcode};
342 my $itemcallnumber = $item->{itemcallnumber};
344 my $borrower = GetMember('borrowernumber'=>$borrowernumber);
345 my $cardnumber = $borrower->{'cardnumber'};
346 my $surname = $borrower->{'surname'};
347 my $firstname = $borrower->{'firstname'};
348 my $phone = $borrower->{'phone'};
350 my $bib = GetBiblioData($biblionumber);
351 my $title = $bib->{title};
353 $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
354 $cardnumber, $reservedate, $title, $itemcallnumber,
355 $holdingbranch, $pickbranch, $reservenotes, $item_level);
359 =head2 AddToHoldTargetMap
363 sub AddToHoldTargetMap {
364 my $item_map = shift;
366 my $dbh = C4::Context->dbh;
369 INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
370 VALUES (?, ?, ?, ?, ?)
372 my $sth_insert = $dbh->prepare($insert_sql);
374 foreach my $itemnumber (keys %$item_map) {
375 my $mapped_item = $item_map->{$itemnumber};
376 $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber,
377 $mapped_item->{holdingbranch}, $mapped_item->{item_level});
381 =head2 _get_branches_to_pull_from
383 Query system preferences to get ordered list of
384 branches to use to fill hold requests.
388 sub _get_branches_to_pull_from {
389 my @branches_to_use = ();
391 my $static_branch_list = C4::Context->preference("StaticHoldsQueueWeight");
392 if ($static_branch_list) {
393 @branches_to_use = map { s/^\s+//; s/\s+$//; $_; } split /,/, $static_branch_list;
396 @branches_to_use = shuffle(@branches_to_use) if C4::Context->preference("RandomizeHoldsQueueWeight");
398 return @branches_to_use;