2 #-----------------------------------
3 # Script Name: build_holds_queue.pl
4 # Description: builds a holds queue in the tmp_holdsqueue table
5 #-----------------------------------
10 # find Koha's Perl modules
11 # test carefully before changing this
13 eval { require "$FindBin::Bin/../kohalib.pl" };
24 use List::Util qw(shuffle);
26 my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests();
28 my $dbh = C4::Context->dbh;
29 $dbh->do("DELETE FROM tmp_holdsqueue"); # clear the old table for new info
30 $dbh->do("DELETE FROM hold_fill_targets");
33 my $total_requests = 0;
34 my $total_available_items = 0;
35 my $num_items_mapped = 0;
37 my @branches_to_use = _get_branches_to_pull_from();
39 foreach my $biblionumber (@$bibs_with_pending_requests) {
41 my $hold_requests = GetPendingHoldRequestsForBib($biblionumber);
42 $total_requests += scalar(@$hold_requests);
43 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber, @branches_to_use);
44 $total_available_items += scalar(@$available_items);
45 my $item_map = MapItemsToHoldRequests($hold_requests, $available_items, @branches_to_use);
46 if (defined($item_map)) {
47 $num_items_mapped += scalar(keys %$item_map);
48 CreatePicklistFromItemMap($item_map);
49 AddToHoldTargetMap($item_map);
50 if ((scalar(keys %$item_map) < scalar(@$hold_requests)) and
51 (scalar(keys %$item_map) < scalar(@$available_items))) {
52 # DOUBLE CHECK, but this is probably OK - unfilled item-level requests
54 #warn "unfilled requests for $biblionumber";
55 #warn Dumper($hold_requests);
56 #warn Dumper($available_items);
57 #warn Dumper($item_map);
64 =head2 GetBibsWithPendingHoldRequests
68 my $biblionumber_aref = GetBibsWithPendingHoldRequests();
72 Return an arrayref of the biblionumbers of all bibs
73 that have one or more unfilled hold requests.
77 sub GetBibsWithPendingHoldRequests {
78 my $dbh = C4::Context->dbh;
80 my $bib_query = "SELECT DISTINCT biblionumber
84 my $sth = $dbh->prepare($bib_query);
87 my $biblionumbers = $sth->fetchall_arrayref();
89 return [ map { $_->[0] } @$biblionumbers ];
92 =head2 GetPendingHoldRequestsForBib
96 my $requests = GetPendingHoldRequestsForBib($biblionumber);
100 Returns an arrayref of hashrefs to pending, unfilled hold requests
101 on the bib identified by $biblionumber. The following keys
102 are present in each hashref:
113 The arrayref is sorted in order of increasing priority.
117 sub GetPendingHoldRequestsForBib {
118 my $biblionumber = shift;
120 my $dbh = C4::Context->dbh;
122 my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, reserves.branchcode,
123 reservedate, reservenotes, borrowers.branchcode AS borrowerbranch
125 JOIN borrowers USING (borrowernumber)
126 WHERE biblionumber = ?
130 my $sth = $dbh->prepare($request_query);
131 $sth->execute($biblionumber);
133 my $requests = $sth->fetchall_arrayref({});
138 =head2 GetItemsAvailableToFillHoldRequestsForBib
142 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
146 Returns an arrayref of items available to fill hold requests
147 for the bib identified by C<$biblionumber>. An item is available
148 to fill a hold request if and only if:
151 * it is not withdrawn
152 * it is not marked notforloan
153 * it is not currently in transit
155 * it is not sitting on the hold shelf
159 sub GetItemsAvailableToFillHoldRequestsForBib {
160 my $biblionumber = shift;
161 my @branches_to_use = @_;
163 my $dbh = C4::Context->dbh;
164 my $items_query = "SELECT itemnumber, homebranch, holdingbranch
167 if (C4::Context->preference('item-level_itypes')) {
168 $items_query .= "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
170 $items_query .= "JOIN biblioitems USING (biblioitemnumber)
171 LEFT JOIN itemtypes USING (itemtype) ";
173 $items_query .= "WHERE items.notforloan = 0
174 AND holdingbranch IS NOT NULL
177 AND items.onloan IS NULL
178 AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0)
179 AND itemnumber NOT IN (
182 WHERE biblionumber = ?
183 AND itemnumber IS NOT NULL
184 AND (found IS NOT NULL OR priority = 0)
186 AND biblionumber = ?";
187 my @params = ($biblionumber, $biblionumber);
188 if ($#branches_to_use > -1) {
189 $items_query .= " AND holdingbranch IN (" . join (",", map { "?" } @branches_to_use) . ")";
190 push @params, @branches_to_use;
192 my $sth = $dbh->prepare($items_query);
193 $sth->execute(@params);
195 my $items = $sth->fetchall_arrayref({});
196 $items = [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ];
197 map { my $rule = GetBranchItemRule($_->{homebranch}, $_->{itype}); $_->{holdallowed} = $rule->{holdallowed}; $rule->{holdallowed} != 0 } @$items;
198 return [ grep { $_->{holdallowed} != 0 } @$items ];
201 =head2 MapItemsToHoldRequests
205 MapItemsToHoldRequests($hold_requests, $available_items);
211 sub MapItemsToHoldRequests {
212 my $hold_requests = shift;
213 my $available_items = shift;
214 my @branches_to_use = @_;
216 # handle trival cases
217 return unless scalar(@$hold_requests) > 0;
218 return unless scalar(@$available_items) > 0;
220 # identify item-level requests
221 my %specific_items_requested = map { $_->{itemnumber} => 1 }
222 grep { defined($_->{itemnumber}) }
225 # group available items by itemnumber
226 my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
228 # items already allocated
229 my %allocated_items = ();
231 # map of items to hold requests
234 # figure out which item-level requests can be filled
235 my $num_items_remaining = scalar(@$available_items);
236 foreach my $request (@$hold_requests) {
237 last if $num_items_remaining == 0;
239 # is this an item-level request?
240 if (defined($request->{itemnumber})) {
241 # fill it if possible; if not skip it
242 if (exists $items_by_itemnumber{$request->{itemnumber}} and
243 not exists $allocated_items{$request->{itemnumber}}) {
244 $item_map{$request->{itemnumber}} = {
245 borrowernumber => $request->{borrowernumber},
246 biblionumber => $request->{biblionumber},
247 holdingbranch => $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch},
248 pickup_branch => $request->{branchcode},
250 reservedate => $request->{reservedate},
251 reservenotes => $request->{reservenotes},
253 $allocated_items{$request->{itemnumber}}++;
254 $num_items_remaining--;
257 # it's title-level request that will take up one item
258 $num_items_remaining--;
262 # group available items by branch
263 my %items_by_branch = ();
264 foreach my $item (@$available_items) {
265 push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} };
268 # now handle the title-level requests
269 $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items);
270 foreach my $request (@$hold_requests) {
271 last if $num_items_remaining <= 0;
272 next if defined($request->{itemnumber}); # already handled these
274 # look for local match first
275 my $pickup_branch = $request->{branchcode};
276 if (exists $items_by_branch{$pickup_branch} and
277 not ($items_by_branch{$pickup_branch}->[0]->{holdallowed} == 1 and
278 $request->{borrowerbranch} ne $items_by_branch{$pickup_branch}->[0]->{homebranch})
280 my $item = pop @{ $items_by_branch{$pickup_branch} };
281 delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0;
282 $item_map{$item->{itemnumber}} = {
283 borrowernumber => $request->{borrowernumber},
284 biblionumber => $request->{biblionumber},
285 holdingbranch => $pickup_branch,
286 pickup_branch => $pickup_branch,
288 reservedate => $request->{reservedate},
289 reservenotes => $request->{reservenotes},
291 $num_items_remaining--;
293 my @pull_branches = ();
294 if ($#branches_to_use > -1) {
295 @pull_branches = @branches_to_use;
297 @pull_branches = sort keys %items_by_branch;
299 foreach my $branch (@pull_branches) {
300 next unless exists $items_by_branch{$branch} and
301 not ($items_by_branch{$branch}->[0]->{holdallowed} == 1 and
302 $request->{borrowerbranch} ne $items_by_branch{$branch}->[0]->{homebranch});
303 my $item = pop @{ $items_by_branch{$branch} };
304 delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0;
305 $item_map{$item->{itemnumber}} = {
306 borrowernumber => $request->{borrowernumber},
307 biblionumber => $request->{biblionumber},
308 holdingbranch => $branch,
309 pickup_branch => $pickup_branch,
311 reservedate => $request->{reservedate},
312 reservenotes => $request->{reservenotes},
314 $num_items_remaining--;
322 =head2 CreatePickListFromItemMap
326 sub CreatePicklistFromItemMap {
327 my $item_map = shift;
329 my $dbh = C4::Context->dbh;
331 my $sth_load=$dbh->prepare("
332 INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,
333 cardnumber,reservedate,title, itemcallnumber,
334 holdingbranch,pickbranch,notes, item_level_request)
335 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
338 foreach my $itemnumber (sort keys %$item_map) {
339 my $mapped_item = $item_map->{$itemnumber};
340 my $biblionumber = $mapped_item->{biblionumber};
341 my $borrowernumber = $mapped_item->{borrowernumber};
342 my $pickbranch = $mapped_item->{pickup_branch};
343 my $holdingbranch = $mapped_item->{holdingbranch};
344 my $reservedate = $mapped_item->{reservedate};
345 my $reservenotes = $mapped_item->{reservenotes};
346 my $item_level = $mapped_item->{item_level};
348 my $item = GetItem($itemnumber);
349 my $barcode = $item->{barcode};
350 my $itemcallnumber = $item->{itemcallnumber};
352 my $borrower = GetMember($borrowernumber);
353 my $cardnumber = $borrower->{'cardnumber'};
354 my $surname = $borrower->{'surname'};
355 my $firstname = $borrower->{'firstname'};
356 my $phone = $borrower->{'phone'};
358 my $bib = GetBiblioData($biblionumber);
359 my $title = $bib->{title};
361 $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
362 $cardnumber, $reservedate, $title, $itemcallnumber,
363 $holdingbranch, $pickbranch, $reservenotes, $item_level);
367 =head2 AddToHoldTargetMap
371 sub AddToHoldTargetMap {
372 my $item_map = shift;
374 my $dbh = C4::Context->dbh;
377 INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
378 VALUES (?, ?, ?, ?, ?)
380 my $sth_insert = $dbh->prepare($insert_sql);
382 foreach my $itemnumber (keys %$item_map) {
383 my $mapped_item = $item_map->{$itemnumber};
384 $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber,
385 $mapped_item->{holdingbranch}, $mapped_item->{item_level});
389 =head2 _get_branches_to_pull_from
391 Query system preferences to get ordered list of
392 branches to use to fill hold requests.
396 sub _get_branches_to_pull_from {
397 my @branches_to_use = ();
399 my $static_branch_list = C4::Context->preference("StaticHoldsQueueWeight");
400 if ($static_branch_list) {
401 @branches_to_use = map { s/^\s+//; s/\s+$//; $_; } split /,/, $static_branch_list;
404 @branches_to_use = shuffle(@branches_to_use) if C4::Context->preference("RandomizeHoldsQueueWeight");
406 return @branches_to_use;