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()
88 my $sth = $dbh->prepare($bib_query);
91 my $biblionumbers = $sth->fetchall_arrayref();
93 return [ map { $_->[0] } @$biblionumbers ];
96 =head2 GetPendingHoldRequestsForBib
98 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 = ?
129 AND reservedate <= CURRENT_DATE()
132 my $sth = $dbh->prepare($request_query);
133 $sth->execute($biblionumber);
135 my $requests = $sth->fetchall_arrayref({});
140 =head2 GetItemsAvailableToFillHoldRequestsForBib
142 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
144 Returns an arrayref of items available to fill hold requests
145 for the bib identified by C<$biblionumber>. An item is available
146 to fill a hold request if and only if:
149 * it is not withdrawn
150 * it is not marked notforloan
151 * it is not currently in transit
153 * it is not sitting on the hold shelf
157 sub GetItemsAvailableToFillHoldRequestsForBib {
158 my $biblionumber = shift;
159 my @branches_to_use = @_;
161 my $dbh = C4::Context->dbh;
162 my $items_query = "SELECT itemnumber, homebranch, holdingbranch, itemtypes.itemtype AS itype
165 if (C4::Context->preference('item-level_itypes')) {
166 $items_query .= "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
168 $items_query .= "JOIN biblioitems USING (biblioitemnumber)
169 LEFT JOIN itemtypes USING (itemtype) ";
171 $items_query .= "WHERE items.notforloan = 0
172 AND holdingbranch IS NOT NULL
175 $items_query .= " AND damaged = 0 " unless C4::Context->preference('AllowHoldsOnDamagedItems');
176 $items_query .= " AND items.onloan IS NULL
177 AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0)
178 AND itemnumber NOT IN (
181 WHERE biblionumber = ?
182 AND itemnumber IS NOT NULL
183 AND (found IS NOT NULL OR priority = 0)
185 AND items.biblionumber = ?";
186 my @params = ($biblionumber, $biblionumber);
187 if ($#branches_to_use > -1) {
188 $items_query .= " AND holdingbranch IN (" . join (",", map { "?" } @branches_to_use) . ")";
189 push @params, @branches_to_use;
191 my $sth = $dbh->prepare($items_query);
192 $sth->execute(@params);
194 my $items = $sth->fetchall_arrayref({});
195 $items = [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ];
196 map { my $rule = GetBranchItemRule($_->{homebranch}, $_->{itype}); $_->{holdallowed} = $rule->{holdallowed}; $rule->{holdallowed} != 0 } @$items;
197 return [ grep { $_->{holdallowed} != 0 } @$items ];
200 =head2 MapItemsToHoldRequests
202 MapItemsToHoldRequests($hold_requests, $available_items);
206 sub MapItemsToHoldRequests {
207 my $hold_requests = shift;
208 my $available_items = shift;
209 my @branches_to_use = @_;
211 # handle trival cases
212 return unless scalar(@$hold_requests) > 0;
213 return unless scalar(@$available_items) > 0;
215 # identify item-level requests
216 my %specific_items_requested = map { $_->{itemnumber} => 1 }
217 grep { defined($_->{itemnumber}) }
220 # group available items by itemnumber
221 my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
223 # items already allocated
224 my %allocated_items = ();
226 # map of items to hold requests
229 # figure out which item-level requests can be filled
230 my $num_items_remaining = scalar(@$available_items);
231 foreach my $request (@$hold_requests) {
232 last if $num_items_remaining == 0;
234 # is this an item-level request?
235 if (defined($request->{itemnumber})) {
236 # fill it if possible; if not skip it
237 if (exists $items_by_itemnumber{$request->{itemnumber}} and
238 not exists $allocated_items{$request->{itemnumber}}) {
239 $item_map{$request->{itemnumber}} = {
240 borrowernumber => $request->{borrowernumber},
241 biblionumber => $request->{biblionumber},
242 holdingbranch => $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch},
243 pickup_branch => $request->{branchcode},
245 reservedate => $request->{reservedate},
246 reservenotes => $request->{reservenotes},
248 $allocated_items{$request->{itemnumber}}++;
249 $num_items_remaining--;
252 # it's title-level request that will take up one item
253 $num_items_remaining--;
257 # group available items by branch
258 my %items_by_branch = ();
259 foreach my $item (@$available_items) {
260 push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} };
263 # now handle the title-level requests
264 $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items);
265 foreach my $request (@$hold_requests) {
266 last if $num_items_remaining <= 0;
267 next if defined($request->{itemnumber}); # already handled these
269 # look for local match first
270 my $pickup_branch = $request->{branchcode};
271 if (exists $items_by_branch{$pickup_branch} and
272 not ($items_by_branch{$pickup_branch}->[0]->{holdallowed} == 1 and
273 $request->{borrowerbranch} ne $items_by_branch{$pickup_branch}->[0]->{homebranch})
275 my $item = pop @{ $items_by_branch{$pickup_branch} };
276 delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0;
277 $item_map{$item->{itemnumber}} = {
278 borrowernumber => $request->{borrowernumber},
279 biblionumber => $request->{biblionumber},
280 holdingbranch => $pickup_branch,
281 pickup_branch => $pickup_branch,
283 reservedate => $request->{reservedate},
284 reservenotes => $request->{reservenotes},
286 $num_items_remaining--;
288 my @pull_branches = ();
289 if ($#branches_to_use > -1) {
290 @pull_branches = @branches_to_use;
292 @pull_branches = sort keys %items_by_branch;
294 foreach my $branch (@pull_branches) {
295 next unless exists $items_by_branch{$branch} and
296 not ($items_by_branch{$branch}->[0]->{holdallowed} == 1 and
297 $request->{borrowerbranch} ne $items_by_branch{$branch}->[0]->{homebranch});
298 my $item = pop @{ $items_by_branch{$branch} };
299 delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0;
300 $item_map{$item->{itemnumber}} = {
301 borrowernumber => $request->{borrowernumber},
302 biblionumber => $request->{biblionumber},
303 holdingbranch => $branch,
304 pickup_branch => $pickup_branch,
306 reservedate => $request->{reservedate},
307 reservenotes => $request->{reservenotes},
309 $num_items_remaining--;
317 =head2 CreatePickListFromItemMap
321 sub CreatePicklistFromItemMap {
322 my $item_map = shift;
324 my $dbh = C4::Context->dbh;
326 my $sth_load=$dbh->prepare("
327 INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,
328 cardnumber,reservedate,title, itemcallnumber,
329 holdingbranch,pickbranch,notes, item_level_request)
330 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
333 foreach my $itemnumber (sort keys %$item_map) {
334 my $mapped_item = $item_map->{$itemnumber};
335 my $biblionumber = $mapped_item->{biblionumber};
336 my $borrowernumber = $mapped_item->{borrowernumber};
337 my $pickbranch = $mapped_item->{pickup_branch};
338 my $holdingbranch = $mapped_item->{holdingbranch};
339 my $reservedate = $mapped_item->{reservedate};
340 my $reservenotes = $mapped_item->{reservenotes};
341 my $item_level = $mapped_item->{item_level};
343 my $item = GetItem($itemnumber);
344 my $barcode = $item->{barcode};
345 my $itemcallnumber = $item->{itemcallnumber};
347 my $borrower = GetMember('borrowernumber'=>$borrowernumber);
348 my $cardnumber = $borrower->{'cardnumber'};
349 my $surname = $borrower->{'surname'};
350 my $firstname = $borrower->{'firstname'};
351 my $phone = $borrower->{'phone'};
353 my $bib = GetBiblioData($biblionumber);
354 my $title = $bib->{title};
356 $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
357 $cardnumber, $reservedate, $title, $itemcallnumber,
358 $holdingbranch, $pickbranch, $reservenotes, $item_level);
362 =head2 AddToHoldTargetMap
366 sub AddToHoldTargetMap {
367 my $item_map = shift;
369 my $dbh = C4::Context->dbh;
372 INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
373 VALUES (?, ?, ?, ?, ?)
375 my $sth_insert = $dbh->prepare($insert_sql);
377 foreach my $itemnumber (keys %$item_map) {
378 my $mapped_item = $item_map->{$itemnumber};
379 $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber,
380 $mapped_item->{holdingbranch}, $mapped_item->{item_level});
384 =head2 _get_branches_to_pull_from
386 Query system preferences to get ordered list of
387 branches to use to fill hold requests.
391 sub _get_branches_to_pull_from {
392 my @branches_to_use = ();
394 my $static_branch_list = C4::Context->preference("StaticHoldsQueueWeight");
395 if ($static_branch_list) {
396 @branches_to_use = map { s/^\s+//; s/\s+$//; $_; } split /,/, $static_branch_list;
399 @branches_to_use = shuffle(@branches_to_use) if C4::Context->preference("RandomizeHoldsQueueWeight");
401 return @branches_to_use;