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