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