misc/cronjobs/holds/build_holds_queue.pl

   1 #!/usr/bin/perl
   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
   9
  10 use strict;
  11 use warnings;
  12 BEGIN {
  13     # find Koha's Perl modules
  14     # test carefully before changing this
  15     use FindBin;
  16     eval { require "$FindBin::Bin/../kohalib.pl" };
  17 }
  18
  19 use C4::Context;
  20 use C4::Search;
  21 use C4::Items;
  22 use C4::Branch;
  23 use C4::Circulation;
  24 use C4::Members;
  25 use C4::Biblio;
  26
  27 use List::Util qw(shuffle);
  28
  29 my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests();
  30
  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");
  34
  35 my $total_bibs            = 0;
  36 my $total_requests        = 0;
  37 my $total_available_items = 0;
  38 my $num_items_mapped      = 0;
  39
  40 my @branches_to_use = _get_branches_to_pull_from();
  41
  42 foreach my $biblionumber (@$bibs_with_pending_requests) {
  43     $total_bibs++;
  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);
  49
  50     (defined($item_map)) or next;
  51
  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
  59         # FIXME
  60         #warn "unfilled requests for $biblionumber";
  61         #warn Dumper($hold_requests), Dumper($available_items), Dumper($item_map);
  62     }
  63 }
  64
  65 exit 0;
  66
  67 =head1 FUNCTIONS
  68
  69 =head2 GetBibsWithPendingHoldRequests
  70
  71   my $biblionumber_aref = GetBibsWithPendingHoldRequests();
  72
  73 Return an arrayref of the biblionumbers of all bibs
  74 that have one or more unfilled hold requests.
  75
  76 =cut
  77
  78 sub GetBibsWithPendingHoldRequests {
  79     my $dbh = C4::Context->dbh;
  80
  81     my $bib_query = "SELECT DISTINCT biblionumber
  82                      FROM reserves
  83                      WHERE found IS NULL
  84                      AND priority > 0
  85                      AND reservedate <= CURRENT_DATE()";
  86     my $sth = $dbh->prepare($bib_query);
  87
  88     $sth->execute();
  89     my $biblionumbers = $sth->fetchall_arrayref();
  90
  91     return [ map { $_->[0] } @$biblionumbers ];
  92 }
  93
  94 =head2 GetPendingHoldRequestsForBib
  95
  96   my $requests = GetPendingHoldRequestsForBib($biblionumber);
  97
  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:
 101
 102     biblionumber
 103     borrowernumber
 104     itemnumber
 105     priority
 106     branchcode
 107     reservedate
 108     reservenotes
 109     borrowerbranch
 110
 111 The arrayref is sorted in order of increasing priority.
 112
 113 =cut
 114
 115 sub GetPendingHoldRequestsForBib {
 116     my $biblionumber = shift;
 117
 118     my $dbh = C4::Context->dbh;
 119
 120     my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, reserves.branchcode,
 121                                 reservedate, reservenotes, borrowers.branchcode AS borrowerbranch
 122                          FROM reserves
 123                          JOIN borrowers USING (borrowernumber)
 124                          WHERE biblionumber = ?
 125                          AND found IS NULL
 126                          AND priority > 0
 127                          AND reservedate <= CURRENT_DATE()
 128                          ORDER BY priority";
 129     my $sth = $dbh->prepare($request_query);
 130     $sth->execute($biblionumber);
 131
 132     my $requests = $sth->fetchall_arrayref({});
 133     return $requests;
 134
 135 }
 136
 137 =head2 GetItemsAvailableToFillHoldRequestsForBib
 138
 139   my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
 140
 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:
 144
 145     * it is not on loan
 146     * it is not withdrawn
 147     * it is not marked notforloan
 148     * it is not currently in transit
 149     * it is not lost
 150     * it is not sitting on the hold shelf
 151
 152 =cut
 153
 154 sub GetItemsAvailableToFillHoldRequestsForBib {
 155     my $biblionumber = shift;
 156     my @branches_to_use = @_;
 157
 158     my $dbh = C4::Context->dbh;
 159     my $items_query = "SELECT itemnumber, homebranch, holdingbranch, itemtypes.itemtype AS itype
 160                        FROM items ";
 161
 162     if (C4::Context->preference('item-level_itypes')) {
 163         $items_query .=   "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
 164     } else {
 165         $items_query .=   "JOIN biblioitems USING (biblioitemnumber)
 166                            LEFT JOIN itemtypes USING (itemtype) ";
 167     }
 168     $items_query .=   "WHERE items.notforloan = 0
 169                        AND holdingbranch IS NOT NULL
 170                        AND itemlost = 0
 171                        AND wthdrawn = 0
 172                        AND items.onloan IS NULL
 173                        AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0)
 174                        AND itemnumber NOT IN (
 175                            SELECT itemnumber
 176                            FROM reserves
 177                            WHERE biblionumber = ?
 178                            AND itemnumber IS NOT NULL
 179                            AND (found IS NOT NULL OR priority = 0)
 180                         )
 181                        AND items.biblionumber = ?";
 182     my @params = ($biblionumber, $biblionumber);
 183     if ($#branches_to_use > -1) {
 184         $items_query .= " AND holdingbranch IN (" . join (",", map { "?" } @branches_to_use) . ")";
 185         push @params, @branches_to_use;
 186     }
 187     my $sth = $dbh->prepare($items_query);
 188     $sth->execute(@params);
 189
 190     my $items = $sth->fetchall_arrayref({});
 191     $items = [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ];
 192     map { my $rule = GetBranchItemRule($_->{homebranch}, $_->{itype}); $_->{holdallowed} = $rule->{holdallowed}; $rule->{holdallowed} != 0 } @$items;
 193     return [ grep { $_->{holdallowed} != 0 } @$items ];
 194 }
 195
 196 =head2 MapItemsToHoldRequests
 197
 198   MapItemsToHoldRequests($hold_requests, $available_items);
 199
 200 =cut
 201
 202 sub MapItemsToHoldRequests {
 203     my $hold_requests = shift;
 204     my $available_items = shift;
 205     my @branches_to_use = @_;
 206
 207     # handle trival cases
 208     return unless scalar(@$hold_requests) > 0;
 209     return unless scalar(@$available_items) > 0;
 210
 211     # identify item-level requests
 212     my %specific_items_requested = map { $_->{itemnumber} => 1 }
 213                                    grep { defined($_->{itemnumber}) }
 214                                    @$hold_requests;
 215
 216     # group available items by itemnumber
 217     my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
 218
 219     # items already allocated
 220     my %allocated_items = ();
 221
 222     # map of items to hold requests
 223     my %item_map = ();
 224
 225     # figure out which item-level requests can be filled
 226     my $num_items_remaining = scalar(@$available_items);
 227     foreach my $request (@$hold_requests) {
 228         last if $num_items_remaining == 0;
 229
 230         # is this an item-level request?
 231         if (defined($request->{itemnumber})) {
 232             # fill it if possible; if not skip it
 233             if (exists $items_by_itemnumber{$request->{itemnumber}} and
 234                 not exists $allocated_items{$request->{itemnumber}}) {
 235                 $item_map{$request->{itemnumber}} = {
 236                     borrowernumber => $request->{borrowernumber},
 237                     biblionumber => $request->{biblionumber},
 238                     holdingbranch =>  $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch},
 239                     pickup_branch => $request->{branchcode},
 240                     item_level => 1,
 241                     reservedate => $request->{reservedate},
 242                     reservenotes => $request->{reservenotes},
 243                 };
 244                 $allocated_items{$request->{itemnumber}}++;
 245                 $num_items_remaining--;
 246             }
 247         } else {
 248             # it's title-level request that will take up one item
 249             $num_items_remaining--;
 250         }
 251     }
 252
 253     # group available items by branch
 254     my %items_by_branch = ();
 255     foreach my $item (@$available_items) {
 256         push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} };
 257     }
 258
 259     # now handle the title-level requests
 260     $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items);
 261     foreach my $request (@$hold_requests) {
 262         last if $num_items_remaining <= 0;
 263         next if defined($request->{itemnumber}); # already handled these
 264
 265         # look for local match first
 266         my $pickup_branch = $request->{branchcode};
 267         if (exists $items_by_branch{$pickup_branch} and
 268             not ($items_by_branch{$pickup_branch}->[0]->{holdallowed} == 1 and
 269                  $request->{borrowerbranch} ne $items_by_branch{$pickup_branch}->[0]->{homebranch})
 270            ) {
 271             my $item = pop @{ $items_by_branch{$pickup_branch} };
 272             delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0;
 273             $item_map{$item->{itemnumber}} = {
 274                                                 borrowernumber => $request->{borrowernumber},
 275                                                 biblionumber => $request->{biblionumber},
 276                                                 holdingbranch => $pickup_branch,
 277                                                 pickup_branch => $pickup_branch,
 278                                                 item_level => 0,
 279                                                 reservedate => $request->{reservedate},
 280                                                 reservenotes => $request->{reservenotes},
 281                                              };
 282             $num_items_remaining--;
 283         } else {
 284             my @pull_branches = ();
 285             if ($#branches_to_use > -1) {
 286                 @pull_branches = @branches_to_use;
 287             } else {
 288                 @pull_branches = sort keys %items_by_branch;
 289             }
 290             foreach my $branch (@pull_branches) {
 291                 next unless exists $items_by_branch{$branch} and
 292                             not ($items_by_branch{$branch}->[0]->{holdallowed} == 1 and
 293                                 $request->{borrowerbranch} ne $items_by_branch{$branch}->[0]->{homebranch});
 294                 my $item = pop @{ $items_by_branch{$branch} };
 295                 delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0;
 296                 $item_map{$item->{itemnumber}} = {
 297                                                     borrowernumber => $request->{borrowernumber},
 298                                                     biblionumber => $request->{biblionumber},
 299                                                     holdingbranch => $branch,
 300                                                     pickup_branch => $pickup_branch,
 301                                                     item_level => 0,
 302                                                     reservedate => $request->{reservedate},
 303                                                     reservenotes => $request->{reservenotes},
 304                                                  };
 305                 $num_items_remaining--;
 306                 last;
 307             }
 308         }
 309     }
 310     return \%item_map;
 311 }
 312
 313 =head2 CreatePickListFromItemMap
 314
 315 =cut
 316
 317 sub CreatePicklistFromItemMap {
 318     my $item_map = shift;
 319
 320     my $dbh = C4::Context->dbh;
 321
 322     my $sth_load=$dbh->prepare("
 323         INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,
 324                                     cardnumber,reservedate,title, itemcallnumber,
 325                                     holdingbranch,pickbranch,notes, item_level_request)
 326         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 327     ");
 328
 329     foreach my $itemnumber  (sort keys %$item_map) {
 330         my $mapped_item = $item_map->{$itemnumber};
 331         my $biblionumber = $mapped_item->{biblionumber};
 332         my $borrowernumber = $mapped_item->{borrowernumber};
 333         my $pickbranch = $mapped_item->{pickup_branch};
 334         my $holdingbranch = $mapped_item->{holdingbranch};
 335         my $reservedate = $mapped_item->{reservedate};
 336         my $reservenotes = $mapped_item->{reservenotes};
 337         my $item_level = $mapped_item->{item_level};
 338
 339         my $item = GetItem($itemnumber);
 340         my $barcode = $item->{barcode};
 341         my $itemcallnumber = $item->{itemcallnumber};
 342
 343         my $borrower = GetMember('borrowernumber'=>$borrowernumber);
 344         my $cardnumber = $borrower->{'cardnumber'};
 345         my $surname = $borrower->{'surname'};
 346         my $firstname = $borrower->{'firstname'};
 347         my $phone = $borrower->{'phone'};
 348
 349         my $bib = GetBiblioData($biblionumber);
 350         my $title = $bib->{title};
 351
 352         $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
 353                            $cardnumber, $reservedate, $title, $itemcallnumber,
 354                            $holdingbranch, $pickbranch, $reservenotes, $item_level);
 355     }
 356 }
 357
 358 =head2 AddToHoldTargetMap
 359
 360 =cut
 361
 362 sub AddToHoldTargetMap {
 363     my $item_map = shift;
 364
 365     my $dbh = C4::Context->dbh;
 366
 367     my $insert_sql = q(
 368         INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
 369                                VALUES (?, ?, ?, ?, ?)
 370     );
 371     my $sth_insert = $dbh->prepare($insert_sql);
 372
 373     foreach my $itemnumber (keys %$item_map) {
 374         my $mapped_item = $item_map->{$itemnumber};
 375         $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber,
 376                              $mapped_item->{holdingbranch}, $mapped_item->{item_level});
 377     }
 378 }
 379
 380 =head2 _get_branches_to_pull_from
 381
 382 Query system preferences to get ordered list of
 383 branches to use to fill hold requests.
 384
 385 =cut
 386
 387 sub _get_branches_to_pull_from {
 388     my @branches_to_use = ();
 389
 390     my $static_branch_list = C4::Context->preference("StaticHoldsQueueWeight");
 391     if ($static_branch_list) {
 392         @branches_to_use = map { s/^\s+//; s/\s+$//; $_; } split /,/, $static_branch_list;
 393     }
 394
 395     @branches_to_use = shuffle(@branches_to_use) if  C4::Context->preference("RandomizeHoldsQueueWeight");
 396
 397     return @branches_to_use;
 398 }