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 use Data::Dumper;
  24
  25 my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests();
  26
  27 my $dbh   = C4::Context->dbh;
  28 $dbh->do("DELETE FROM tmp_holdsqueue");  # clear the old table for new info
  29 $dbh->do("DELETE FROM hold_fill_targets");
  30
  31 my $total_bibs = 0;
  32 my $total_requests = 0;
  33 my $total_available_items = 0;
  34 my $num_items_mapped = 0;
  35 foreach my $biblionumber (@$bibs_with_pending_requests) {
  36     $total_bibs++;
  37     my $hold_requests =   GetPendingHoldRequestsForBib($biblionumber);
  38     $total_requests += scalar(@$hold_requests);
  39     my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
  40     $total_available_items += scalar(@$available_items);
  41     my $item_map = MapItemsToHoldRequests($hold_requests, $available_items);
  42     if (defined($item_map)) {
  43         $num_items_mapped += scalar(keys %$item_map);
  44         CreatePicklistFromItemMap($item_map);
  45         AddToHoldTargetMap($item_map);
  46         if ((scalar(keys %$item_map) < scalar(@$hold_requests)) and
  47             (scalar(keys %$item_map) < scalar(@$available_items))) {
  48             # DOUBLE CHECK, but this is probably OK - unfilled item-level requests
  49             # FIXME
  50             #warn "unfilled requests for $biblionumber";
  51             #warn Dumper($hold_requests);
  52             #warn Dumper($available_items);
  53             #warn Dumper($item_map);
  54         }
  55     }
  56 }
  57
  58 exit 0;
  59
  60 =head2 GetBibsWithPendingHoldRequests
  61
  62 =over 4
  63
  64 my $biblionumber_aref = GetBibsWithPendingHoldRequests();
  65
  66 =back
  67
  68 Return an arrayref of the biblionumbers of all bibs
  69 that have one or more unfilled hold requests.
  70
  71 =cut
  72
  73 sub GetBibsWithPendingHoldRequests {
  74     my $dbh = C4::Context->dbh;
  75
  76     my $bib_query = "SELECT DISTINCT biblionumber
  77                      FROM reserves
  78                      WHERE found IS NULL
  79                      AND priority > 0";
  80     my $sth = $dbh->prepare($bib_query);
  81
  82     $sth->execute();
  83     my $biblionumbers = $sth->fetchall_arrayref();
  84
  85     return [ map { $_->[0] } @$biblionumbers ];
  86 }
  87
  88 =head2 GetPendingHoldRequestsForBib
  89
  90 =over 4
  91
  92 my $requests = GetPendingHoldRequestsForBib($biblionumber);
  93
  94 =back
  95
  96 Returns an arrayref of hashrefs to pending, unfilled hold requests
  97 on the bib identified by $biblionumber.  The following keys
  98 are present in each hashref:
  99
 100 biblionumber
 101 borrowernumber
 102 itemnumber
 103 priority
 104 branchcode
 105 reservedate
 106 reservenotes
 107
 108 The arrayref is sorted in order of increasing priority.
 109
 110 =cut
 111
 112 sub GetPendingHoldRequestsForBib {
 113     my $biblionumber = shift;
 114
 115     my $dbh = C4::Context->dbh;
 116
 117     my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, branchcode, reservedate, reservenotes
 118                          FROM reserves
 119                          WHERE biblionumber = ?
 120                          AND found IS NULL
 121                          AND priority > 0
 122                          ORDER BY priority";
 123     my $sth = $dbh->prepare($request_query);
 124     $sth->execute($biblionumber);
 125
 126     my $requests = $sth->fetchall_arrayref({});
 127     return $requests;
 128
 129 }
 130
 131 =head2 GetItemsAvailableToFillHoldRequestsForBib
 132
 133 =over 4
 134
 135 my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber);
 136
 137 =back
 138
 139 Returns an arrayref of items available to fill hold requests
 140 for the bib identified by C<$biblionumber>.  An item is available
 141 to fill a hold request if and only if:
 142
 143 * it is not on loan
 144 * it is not withdrawn
 145 * it is not marked notforloan
 146 * it is not currently in transit
 147 * it is not lost
 148 * it is not sitting on the hold shelf
 149
 150 =cut
 151
 152 sub GetItemsAvailableToFillHoldRequestsForBib {
 153     my $biblionumber = shift;
 154
 155     my $dbh = C4::Context->dbh;
 156     my $items_query = "SELECT itemnumber, homebranch, holdingbranch
 157                        FROM items ";
 158
 159     if (C4::Context->preference('item-level_itypes')) {
 160         $items_query .=   "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) ";
 161     } else {
 162         $items_query .=   "JOIN biblioitems USING (biblioitemnumber)
 163                            LEFT JOIN itemtypes USING (itemtype) ";
 164     }
 165     $items_query .=   "WHERE items.notforloan = 0
 166                        AND holdingbranch IS NOT NULL
 167                        AND itemlost = 0
 168                        AND wthdrawn = 0
 169                        AND items.onloan IS NULL
 170                        AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0)
 171                        AND itemnumber NOT IN (
 172                            SELECT itemnumber
 173                            FROM reserves
 174                            WHERE biblionumber = ?
 175                            AND itemnumber IS NOT NULL
 176                            AND (found IS NOT NULL OR priority = 0)
 177                         )
 178                        AND biblionumber = ?";
 179     my $sth = $dbh->prepare($items_query);
 180     $sth->execute($biblionumber, $biblionumber);
 181
 182     my $items = $sth->fetchall_arrayref({});
 183     return [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ];
 184 }
 185
 186 =head2 MapItemsToHoldRequests
 187
 188 =over 4
 189
 190 MapItemsToHoldRequests($hold_requests, $available_items);
 191
 192 =back
 193
 194 =cut
 195
 196 sub MapItemsToHoldRequests {
 197     my $hold_requests = shift;
 198     my $available_items = shift;
 199
 200     # handle trival cases
 201     return unless scalar(@$hold_requests) > 0;
 202     return unless scalar(@$available_items) > 0;
 203
 204     # identify item-level requests
 205     my %specific_items_requested = map { $_->{itemnumber} => 1 }
 206                                    grep { defined($_->{itemnumber}) }
 207                                    @$hold_requests;
 208
 209     # group available items by itemnumber
 210     my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items;
 211
 212     # items already allocated
 213     my %allocated_items = ();
 214
 215     # map of items to hold requests
 216     my %item_map = ();
 217
 218     # figure out which item-level requests can be filled
 219     my $num_items_remaining = scalar(@$available_items);
 220     foreach my $request (@$hold_requests) {
 221         last if $num_items_remaining == 0;
 222
 223         # is this an item-level request?
 224         if (defined($request->{itemnumber})) {
 225             # fill it if possible; if not skip it
 226             if (exists $items_by_itemnumber{$request->{itemnumber}} and
 227                 not exists $allocated_items{$request->{itemnumber}}) {
 228                 $item_map{$request->{itemnumber}} = {
 229                     borrowernumber => $request->{borrowernumber},
 230                     biblionumber => $request->{biblionumber},
 231                     holdingbranch =>  $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch},
 232                     pickup_branch => $request->{branchcode},
 233                     item_level => 1,
 234                     reservedate => $request->{reservedate},
 235                     reservenotes => $request->{reservenotes},
 236                 };
 237                 $allocated_items{$request->{itemnumber}}++;
 238                 $num_items_remaining--;
 239             }
 240         } else {
 241             # it's title-level request that will take up one item
 242             $num_items_remaining--;
 243         }
 244     }
 245
 246     # group available items by branch
 247     my %items_by_branch = ();
 248     foreach my $item (@$available_items) {
 249         push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} };
 250     }
 251
 252     # now handle the title-level requests
 253     $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items);
 254     foreach my $request (@$hold_requests) {
 255         last if $num_items_remaining <= 0;
 256         next if defined($request->{itemnumber}); # already handled these
 257
 258         # look for local match first
 259         my $pickup_branch = $request->{branchcode};
 260         if (exists $items_by_branch{$pickup_branch}) {
 261             my $item = pop @{ $items_by_branch{$pickup_branch} };
 262             delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0;
 263             $item_map{$item->{itemnumber}} = {
 264                                                 borrowernumber => $request->{borrowernumber},
 265                                                 biblionumber => $request->{biblionumber},
 266                                                 holdingbranch => $pickup_branch,
 267                                                 pickup_branch => $pickup_branch,
 268                                                 item_level => 0,
 269                                                 reservedate => $request->{reservedate},
 270                                                 reservenotes => $request->{reservenotes},
 271                                              };
 272             $num_items_remaining--;
 273         } else {
 274             # FIXME implement static, random options
 275             foreach my $branch (sort keys %items_by_branch) {
 276                 my $item = pop @{ $items_by_branch{$branch} };
 277                 delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0;
 278                 $item_map{$item->{itemnumber}} = {
 279                                                     borrowernumber => $request->{borrowernumber},
 280                                                     biblionumber => $request->{biblionumber},
 281                                                     holdingbranch => $branch,
 282                                                     pickup_branch => $pickup_branch,
 283                                                     item_level => 0,
 284                                                     reservedate => $request->{reservedate},
 285                                                     reservenotes => $request->{reservenotes},
 286                                                  };
 287                 $num_items_remaining--;
 288                 last;
 289             }
 290         }
 291     }
 292     return \%item_map;
 293 }
 294
 295 =head2 CreatePickListFromItemMap
 296
 297 =cut
 298
 299 sub CreatePicklistFromItemMap {
 300     my $item_map = shift;
 301
 302     my $dbh = C4::Context->dbh;
 303
 304     my $sth_load=$dbh->prepare("
 305         INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,
 306                                     cardnumber,reservedate,title, itemcallnumber,
 307                                     holdingbranch,pickbranch,notes, item_level_request)
 308         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 309     ");
 310
 311     foreach my $itemnumber  (sort keys %$item_map) {
 312         my $mapped_item = $item_map->{$itemnumber};
 313         my $biblionumber = $mapped_item->{biblionumber};
 314         my $borrowernumber = $mapped_item->{borrowernumber};
 315         my $pickbranch = $mapped_item->{pickup_branch};
 316         my $holdingbranch = $mapped_item->{holdingbranch};
 317         my $reservedate = $mapped_item->{reservedate};
 318         my $reservenotes = $mapped_item->{reservenotes};
 319         my $item_level = $mapped_item->{item_level};
 320
 321         my $item = GetItem($itemnumber);
 322         my $barcode = $item->{barcode};
 323         my $itemcallnumber = $item->{itemcallnumber};
 324
 325         my $borrower = GetMember($borrowernumber);
 326         my $cardnumber = $borrower->{'cardnumber'};
 327         my $surname = $borrower->{'surname'};
 328         my $firstname = $borrower->{'firstname'};
 329         my $phone = $borrower->{'phone'};
 330
 331         my $bib = GetBiblioData($biblionumber);
 332         my $title = $bib->{title};
 333
 334         $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber,
 335                            $cardnumber, $reservedate, $title, $itemcallnumber,
 336                            $holdingbranch, $pickbranch, $reservenotes, $item_level);
 337     }
 338 }
 339
 340 =head2 AddToHoldTargetMap
 341
 342 =cut
 343
 344 sub AddToHoldTargetMap {
 345     my $item_map = shift;
 346
 347     my $dbh = C4::Context->dbh;
 348
 349     my $insert_sql = q(
 350         INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request)
 351                                VALUES (?, ?, ?, ?, ?)
 352     );
 353     my $sth_insert = $dbh->prepare($insert_sql);
 354
 355     foreach my $itemnumber (keys %$item_map) {
 356         my $mapped_item = $item_map->{$itemnumber};
 357         $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber,
 358                              $mapped_item->{holdingbranch}, $mapped_item->{item_level});
 359     }
 360 }