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                      AND suspend = 0
  87                      ";
  88     my $sth = $dbh->prepare($bib_query);
  89
  90     $sth->execute();
  91     my $biblionumbers = $sth->fetchall_arrayref();
  92
  93     return [ map { $_->[0] } @$biblionumbers ];
  94 }
  95
  96 =head2 GetPendingHoldRequestsForBib
  97
  98   my $requests = GetPendingHoldRequestsForBib($biblionumber);
  99
 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:
 103
 104     biblionumber
 105     borrowernumber
 106     itemnumber
 107     priority
 108     branchcode
 109     reservedate
 110     reservenotes
 111     borrowerbranch
 112
 113 The arrayref is sorted in order of increasing priority.
 114
 115 =cut
 116
 117 sub GetPendingHoldRequestsForBib {
 118     my $biblionumber = shift;
 119
 120     my $dbh = C4::Context->dbh;
 121
 122     my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, reserves.branchcode,
 123                                 reservedate, reservenotes, borrowers.branchcode AS borrowerbranch
 124                          FROM reserves
 125                          JOIN borrowers USING (borrowernumber)
 126                          WHERE biblionumber = ?
 127                          AND found IS NULL
 128                          AND priority > 0
 129                          AND reservedate <= CURRENT_DATE()
 130                          AND suspend = 0
 131                          ORDER BY priority";
 132     my $sth = $dbh->prepare($request_query);
 133     $sth->execute($biblionumber);
 134
 135     my $requests = $sth->fetchall_arrayref({});
 136     return $requests;
 137
 138 }
 139
 140 =head2 GetItemsAvailableToFillHoldRequestsForBib
 141
 142   my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
 143
 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:
 147
 148     * it is not on loan
 149     * it is not withdrawn
 150     * it is not marked notforloan
 151     * it is not currently in transit
 152     * it is not lost
 153     * it is not sitting on the hold shelf
 154
 155 =cut
 156
 157 sub GetItemsAvailableToFillHoldRequestsForBib {
 158     my $biblionumber = shift;
 159     my @branches_to_use = @_;
 160
 161     my $dbh = C4::Context->dbh;
 162     my $items_query = "SELECT itemnumber, homebranch, holdingbranch, itemtypes.itemtype AS itype
 163                        FROM items ";
 164
 165     if (C4::Context->preference('item-level_itypes')) {
 166         $items_query .=   "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
 167     } else {
 168         $items_query .=   "JOIN biblioitems USING (biblioitemnumber)
 169                            LEFT JOIN itemtypes USING (itemtype) ";
 170     }
 171     $items_query .=   "WHERE items.notforloan = 0
 172                        AND holdingbranch IS NOT NULL
 173                        AND itemlost = 0
 174                        AND wthdrawn = 0";
 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 (
 179                            SELECT itemnumber
 180                            FROM reserves
 181                            WHERE biblionumber = ?
 182                            AND itemnumber IS NOT NULL
 183                            AND (found IS NOT NULL OR priority = 0)
 184                         )
 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;
 190     }
 191     my $sth = $dbh->prepare($items_query);
 192     $sth->execute(@params);
 193
 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 ];
 198 }
 199
 200 =head2 MapItemsToHoldRequests
 201
 202   MapItemsToHoldRequests($hold_requests, $available_items);
 203
 204 =cut
 205
 206 sub MapItemsToHoldRequests {
 207     my $hold_requests = shift;
 208     my $available_items = shift;
 209     my @branches_to_use = @_;
 210
 211     # handle trival cases
 212     return unless scalar(@$hold_requests) > 0;
 213     return unless scalar(@$available_items) > 0;
 214
 215     # identify item-level requests
 216     my %specific_items_requested = map { $_->{itemnumber} => 1 }
 217                                    grep { defined($_->{itemnumber}) }
 218                                    @$hold_requests;
 219
 220     # group available items by itemnumber
 221     my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
 222
 223     # items already allocated
 224     my %allocated_items = ();
 225
 226     # map of items to hold requests
 227     my %item_map = ();
 228
 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;
 233
 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},
 244                     item_level => 1,
 245                     reservedate => $request->{reservedate},
 246                     reservenotes => $request->{reservenotes},
 247                 };
 248                 $allocated_items{$request->{itemnumber}}++;
 249                 $num_items_remaining--;
 250             }
 251         } else {
 252             # it's title-level request that will take up one item
 253             $num_items_remaining--;
 254         }
 255     }
 256
 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} };
 261     }
 262
 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
 268
 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})
 274            ) {
 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,
 282                                                 item_level => 0,
 283                                                 reservedate => $request->{reservedate},
 284                                                 reservenotes => $request->{reservenotes},
 285                                              };
 286             $num_items_remaining--;
 287         } else {
 288             my @pull_branches = ();
 289             if ($#branches_to_use > -1) {
 290                 @pull_branches = @branches_to_use;
 291             } else {
 292                 @pull_branches = sort keys %items_by_branch;
 293             }
 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,
 305                                                     item_level => 0,
 306                                                     reservedate => $request->{reservedate},
 307                                                     reservenotes => $request->{reservenotes},
 308                                                  };
 309                 $num_items_remaining--;
 310                 last;
 311             }
 312         }
 313     }
 314     return \%item_map;
 315 }
 316
 317 =head2 CreatePickListFromItemMap
 318
 319 =cut
 320
 321 sub CreatePicklistFromItemMap {
 322     my $item_map = shift;
 323
 324     my $dbh = C4::Context->dbh;
 325
 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 331     ");
 332
 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};
 342
 343         my $item = GetItem($itemnumber);
 344         my $barcode = $item->{barcode};
 345         my $itemcallnumber = $item->{itemcallnumber};
 346
 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'};
 352
 353         my $bib = GetBiblioData($biblionumber);
 354         my $title = $bib->{title};
 355
 356         $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
 357                            $cardnumber, $reservedate, $title, $itemcallnumber,
 358                            $holdingbranch, $pickbranch, $reservenotes, $item_level);
 359     }
 360 }
 361
 362 =head2 AddToHoldTargetMap
 363
 364 =cut
 365
 366 sub AddToHoldTargetMap {
 367     my $item_map = shift;
 368
 369     my $dbh = C4::Context->dbh;
 370
 371     my $insert_sql = q(
 372         INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
 373                                VALUES (?, ?, ?, ?, ?)
 374     );
 375     my $sth_insert = $dbh->prepare($insert_sql);
 376
 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});
 381     }
 382 }
 383
 384 =head2 _get_branches_to_pull_from
 385
 386 Query system preferences to get ordered list of
 387 branches to use to fill hold requests.
 388
 389 =cut
 390
 391 sub _get_branches_to_pull_from {
 392     my @branches_to_use = ();
 393
 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;
 397     }
 398
 399     @branches_to_use = shuffle(@branches_to_use) if  C4::Context->preference("RandomizeHoldsQueueWeight");
 400
 401     return @branches_to_use;
 402 }