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     $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 (
 176                            SELECT itemnumber
 177                            FROM reserves
 178                            WHERE biblionumber = ?
 179                            AND itemnumber IS NOT NULL
 180                            AND (found IS NOT NULL OR priority = 0)
 181                         )
 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;
 187     }
 188     my $sth = $dbh->prepare($items_query);
 189     $sth->execute(@params);
 190
 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 ];
 195 }
 196
 197 =head2 MapItemsToHoldRequests
 198
 199   MapItemsToHoldRequests($hold_requests, $available_items);
 200
 201 =cut
 202
 203 sub MapItemsToHoldRequests {
 204     my $hold_requests = shift;
 205     my $available_items = shift;
 206     my @branches_to_use = @_;
 207
 208     # handle trival cases
 209     return unless scalar(@$hold_requests) > 0;
 210     return unless scalar(@$available_items) > 0;
 211
 212     # identify item-level requests
 213     my %specific_items_requested = map { $_->{itemnumber} => 1 }
 214                                    grep { defined($_->{itemnumber}) }
 215                                    @$hold_requests;
 216
 217     # group available items by itemnumber
 218     my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
 219
 220     # items already allocated
 221     my %allocated_items = ();
 222
 223     # map of items to hold requests
 224     my %item_map = ();
 225
 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;
 230
 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},
 241                     item_level => 1,
 242                     reservedate => $request->{reservedate},
 243                     reservenotes => $request->{reservenotes},
 244                 };
 245                 $allocated_items{$request->{itemnumber}}++;
 246                 $num_items_remaining--;
 247             }
 248         } else {
 249             # it's title-level request that will take up one item
 250             $num_items_remaining--;
 251         }
 252     }
 253
 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} };
 258     }
 259
 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
 265
 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})
 271            ) {
 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,
 279                                                 item_level => 0,
 280                                                 reservedate => $request->{reservedate},
 281                                                 reservenotes => $request->{reservenotes},
 282                                              };
 283             $num_items_remaining--;
 284         } else {
 285             my @pull_branches = ();
 286             if ($#branches_to_use > -1) {
 287                 @pull_branches = @branches_to_use;
 288             } else {
 289                 @pull_branches = sort keys %items_by_branch;
 290             }
 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,
 302                                                     item_level => 0,
 303                                                     reservedate => $request->{reservedate},
 304                                                     reservenotes => $request->{reservenotes},
 305                                                  };
 306                 $num_items_remaining--;
 307                 last;
 308             }
 309         }
 310     }
 311     return \%item_map;
 312 }
 313
 314 =head2 CreatePickListFromItemMap
 315
 316 =cut
 317
 318 sub CreatePicklistFromItemMap {
 319     my $item_map = shift;
 320
 321     my $dbh = C4::Context->dbh;
 322
 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 328     ");
 329
 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};
 339
 340         my $item = GetItem($itemnumber);
 341         my $barcode = $item->{barcode};
 342         my $itemcallnumber = $item->{itemcallnumber};
 343
 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'};
 349
 350         my $bib = GetBiblioData($biblionumber);
 351         my $title = $bib->{title};
 352
 353         $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
 354                            $cardnumber, $reservedate, $title, $itemcallnumber,
 355                            $holdingbranch, $pickbranch, $reservenotes, $item_level);
 356     }
 357 }
 358
 359 =head2 AddToHoldTargetMap
 360
 361 =cut
 362
 363 sub AddToHoldTargetMap {
 364     my $item_map = shift;
 365
 366     my $dbh = C4::Context->dbh;
 367
 368     my $insert_sql = q(
 369         INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
 370                                VALUES (?, ?, ?, ?, ?)
 371     );
 372     my $sth_insert = $dbh->prepare($insert_sql);
 373
 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});
 378     }
 379 }
 380
 381 =head2 _get_branches_to_pull_from
 382
 383 Query system preferences to get ordered list of
 384 branches to use to fill hold requests.
 385
 386 =cut
 387
 388 sub _get_branches_to_pull_from {
 389     my @branches_to_use = ();
 390
 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;
 394     }
 395
 396     @branches_to_use = shuffle(@branches_to_use) if  C4::Context->preference("RandomizeHoldsQueueWeight");
 397
 398     return @branches_to_use;
 399 }