From 516ba77b8b9d8cd40453253eedd46ca749d026d1 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Thu, 14 Aug 2008 20:59:51 -0500 Subject: [PATCH] bug 2522 [3/3]: populate hold_fill_targets This batch job now automatically populates the holds request targeting table hold_fill_targets. This patch is essentially a revamp of the job, and includes fixes for the following bugs: 2281 (holds queue report including unavailable items) 2331 (holds queue report not working with item-level holds) 2332 (holds queue script should attempt to fill many requests as possible). Several functions in this batch job are candidates for being moved to C4::Reserves: GetBibsWithPendingHoldRequests() GetPendingHoldRequestsForBib() GetItemsAvailableToFillHoldRequestsForBib() MapItemsToHoldRequests() Signed-off-by: Galen Charlton --- misc/cronjobs/holds/build_holds_queue.pl | 446 ++++++++++++++++------- 1 file changed, 319 insertions(+), 127 deletions(-) diff --git a/misc/cronjobs/holds/build_holds_queue.pl b/misc/cronjobs/holds/build_holds_queue.pl index e422248764..ae075b7fa6 100755 --- a/misc/cronjobs/holds/build_holds_queue.pl +++ b/misc/cronjobs/holds/build_holds_queue.pl @@ -5,6 +5,7 @@ #----------------------------------- use strict; +use warnings; BEGIN { # find Koha's Perl modules # test carefully before changing this @@ -16,149 +17,340 @@ use C4::Context; use C4::Search; use C4::Items; use C4::Branch; +use C4::Circulation; +use C4::Members; +use C4::Biblio; +use Data::Dumper; -# load the branches -my $branches = GetBranches(); +my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests(); -# obtain the ranked list of weights for the case of static weighting -my $syspref = C4::Context->preference("StaticHoldsQueueWeight"); -my @branch_loop; -#@branch_loop = split(/,/, $syspref) if $syspref; +my $dbh = C4::Context->dbh; +$dbh->do("DELETE FROM tmp_holdsqueue"); # clear the old table for new info +$dbh->do("DELETE FROM hold_fill_targets"); + +my $total_bibs = 0; +my $total_requests = 0; +my $total_available_items = 0; +my $num_items_mapped = 0; +foreach my $biblionumber (@$bibs_with_pending_requests) { + $total_bibs++; + my $hold_requests = GetPendingHoldRequestsForBib($biblionumber); + $total_requests += scalar(@$hold_requests); + my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber); + $total_available_items += scalar(@$available_items); + my $item_map = MapItemsToHoldRequests($hold_requests, $available_items); + if (defined($item_map)) { + $num_items_mapped += scalar(keys %$item_map); + CreatePicklistFromItemMap($item_map); + AddToHoldTargetMap($item_map); + if ((scalar(keys %$item_map) < scalar(@$hold_requests)) and + (scalar(keys %$item_map) < scalar(@$available_items))) { + # DOUBLE CHECK, but this is probably OK - unfilled item-level requests + # FIXME + #warn "unfilled requests for $biblionumber"; + #warn Dumper($hold_requests); + #warn Dumper($available_items); + #warn Dumper($item_map); + } + } +} + +exit 0; + +=head2 GetBibsWithPendingHoldRequests + +=over 4 + +my $biblionumber_aref = GetBibsWithPendingHoldRequests(); -# TODO: Add Randomization Option +=back -# If no syspref is set, use system-order to determine priority -unless ($syspref) { - for my $branch_hash (sort keys %$branches) { - push @branch_loop, $branch_hash; - #{value => "$branch_hash" , branchname => $branches->{$branch_hash}->{'branchname'}, }; - } +Return an arrayref of the biblionumbers of all bibs +that have one or more unfilled hold requests. + +=cut + +sub GetBibsWithPendingHoldRequests { + my $dbh = C4::Context->dbh; + + my $bib_query = "SELECT DISTINCT biblionumber + FROM reserves + WHERE found IS NULL + AND priority > 0"; + my $sth = $dbh->prepare($bib_query); + + $sth->execute(); + my $biblionumbers = $sth->fetchall_arrayref(); + + return [ map { $_->[0] } @$biblionumbers ]; } -# if Randomization is enabled, randomize this array -@branch_loop = randarray(@branch_loop) if C4::Context->preference("RandomizeHoldsQueueWeight");; +=head2 GetPendingHoldRequestsForBib -my ($biblionumber,$itemnumber,$barcode,$holdingbranch,$pickbranch,$notes,$cardnumber,$surname,$firstname,$phone,$title,$callno,$rdate,$borrno); +=over 4 -my $dbh = C4::Context->dbh; +my $requests = GetPendingHoldRequestsForBib($biblionumber); -$dbh->do("DELETE FROM tmp_holdsqueue"); # clear the old table for new info +=back -my $sth=$dbh->prepare(" -SELECT biblionumber,itemnumber,reserves.branchcode,reservenotes,borrowers.borrowernumber,cardnumber,surname,firstname,phone,reservedate - FROM reserves,borrowers -WHERE reserves.found IS NULL - AND reserves.borrowernumber=borrowers.borrowernumber - AND priority=1 -GROUP BY biblionumber"); - -my $sth_load=$dbh->prepare(" -INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber,cardnumber,reservedate,title,itemcallnumber,holdingbranch,pickbranch,notes) - VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); - -$sth->execute(); # get the list of biblionumbers for unfilled holds - -GETIT: -while (my $data=$sth->fetchrow_hashref){ - # get the basic hold info - $biblionumber = $data->{'biblionumber'}; - $pickbranch = $data->{'branchcode'}; - $notes = $data->{'reservenotes'}; - $borrno = $data->{'borrowernumber'}; - $cardnumber = $data->{'cardnumber'}; - $surname = $data->{'surname'}; - $firstname = $data->{'firstname'}; - $phone = $data->{'phone'}; - $rdate = $data->{'reservedate'}; - - my @items = GetItemsInfo($biblionumber,''); # get the items for this biblio - my @itemorder; # prepare a new array to hold re-ordered items - - # Make sure someone(else) doesn't already have this item waiting for them - my $found_sth = $dbh->prepare(" - SELECT found FROM reserves WHERE itemnumber=? AND found = ? AND cancellationdate IS NULL"); - - # The following lines take the retrieved items and run them through various - # tests to decide if they are to be used and then put them in the preferred - # 'pick' order. - foreach my $itm (@items) { - - $found_sth->execute($itm->{itemnumber},"W"); - my $found = $found_sth->fetchrow_hashref(); - if ($found) { - $itm->{"found"} = $found->{"found"}; - } - if ($itm->{"notforloan"}) { - # item is on order - next if $itm->{"notforloan"}== -1; - } - if ( ( (!$itm->{"binding"}) || - # Item is at not at bindery, not checked out, and not lost - ($itm->{"binding"}<1)) && (!$itm->{"found"}) && (!$itm->{"datedue"}) && ( (!$itm->{"itemlost"}) || - - # Item is not lost and not notforloan - ($itm->{"itemlost"}==0) ) && ( ($itm->{"notforloan"}==0) || - - # Item is not notforloan - (!$itm->{"notforloan"}) ) ) { - - warn "patron requested pickup at $pickbranch for item in ".$itm->{'holdingbranch'}; - - # This selects items for fulfilment, and weights them based on - # a static list - my $weight=0; - # always prefer a direct match - if ($itm->{'holdingbranch'} eq $pickbranch) { - warn "Found match in pickuplibrary"; - $itemorder[$weight]=$itm; - } - else { - for my $branchcode (@branch_loop) { - $weight++; - if ($itm->{'homebranch'} eq $branchcode) { - warn "Match found with weight $weight in ".$branchcode; - $itemorder[$weight]=$itm; - } - } +Returns an arrayref of hashrefs to pending, unfilled hold requests +on the bib identified by $biblionumber. The following keys +are present in each hashref: + +biblionumber +borrowernumber +itemnumber +priority +branchcode +reservedate +reservenotes + +The arrayref is sorted in order of increasing priority. + +=cut + +sub GetPendingHoldRequestsForBib { + my $biblionumber = shift; + + my $dbh = C4::Context->dbh; + + my $request_query = "SELECT biblionumber, borrowernumber, itemnumber, priority, branchcode, reservedate, reservenotes + FROM reserves + WHERE biblionumber = ? + AND found IS NULL + AND priority > 0 + ORDER BY priority"; + my $sth = $dbh->prepare($request_query); + $sth->execute($biblionumber); + + my $requests = $sth->fetchall_arrayref({}); + return $requests; + +} + +=head2 GetItemsAvailableToFillHoldRequestsForBib + +=over 4 + +my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber); + +=back + +Returns an arrayref of items available to fill hold requests +for the bib identified by C<$biblionumber>. An item is available +to fill a hold request if and only if: + +* it is not on loan +* it is not withdrawn +* it is not marked notforloan +* it is not currently in transit +* it is not lost +* it is not sitting on the hold shelf + +=cut + +sub GetItemsAvailableToFillHoldRequestsForBib { + my $biblionumber = shift; + + my $dbh = C4::Context->dbh; + my $items_query = "SELECT itemnumber, homebranch, holdingbranch + FROM items "; + + if (C4::Context->preference('item-level_itypes')) { + $items_query .= "LEFT JOIN itemtypes ON (itemtypes.itemtype = items.itype) "; + } else { + $items_query .= "JOIN biblioitems USING (biblioitemnumber) + LEFT JOIN itemtypes USING (itemtype) "; + } + $items_query .= "LEFT JOIN reserves USING (itemnumber) + WHERE items.notforloan = 0 + AND holdingbranch IS NOT NULL + AND itemlost = 0 + AND wthdrawn = 0 + AND items.onloan IS NULL + AND (itemtypes.notforloan IS NULL OR itemtypes.notforloan = 0) + AND (priority IS NULL OR priority > 0) + AND found IS NULL + AND biblionumber = ?"; + my $sth = $dbh->prepare($items_query); + $sth->execute($biblionumber); + + my $items = $sth->fetchall_arrayref({}); + return [ grep { my @transfers = GetTransfers($_->{itemnumber}); $#transfers == -1; } @$items ]; +} + +=head2 MapItemsToHoldRequests + +=over 4 + +MapItemsToHoldRequests($hold_requests, $available_items); + +=back + +=cut + +sub MapItemsToHoldRequests { + my $hold_requests = shift; + my $available_items = shift; + + # handle trival cases + return unless scalar(@$hold_requests) > 0; + return unless scalar(@$available_items) > 0; + + # identify item-level requests + my %specific_items_requested = map { $_->{itemnumber} => 1 } + grep { defined($_->{itemnumber}) } + @$hold_requests; + + # group available items by itemnumber + my %items_by_itemnumber = map { $_->{itemnumber} => $_ } @$available_items; + + # items already allocated + my %allocated_items = (); + + # map of items to hold requests + my %item_map = (); + + # figure out which item-level requests can be filled + my $num_items_remaining = scalar(@$available_items); + foreach my $request (@$hold_requests) { + last if $num_items_remaining == 0; + + # is this an item-level request? + if (defined($request->{itemnumber})) { + # fill it if possible; if not skip it + if (exists $items_by_itemnumber{$request->{itemnumber}} and + not exists $allocated_items{$request->{itemnumber}}) { + $item_map{$request->{itemnumber}} = { + borrowernumber => $request->{borrowernumber}, + biblionumber => $request->{biblionumber}, + holdingbranch => $items_by_itemnumber{$request->{itemnumber}}->{holdingbranch}, + pickup_branch => $request->{branchcode}, + item_level => 1, + reservedate => $request->{reservedate}, + reservenotes => $request->{reservenotes}, + }; + $allocated_items{$request->{itemnumber}}++; + $num_items_remaining--; } + } else { + # it's title-level request that will take up one item + $num_items_remaining--; } } - my $count = @itemorder; - warn "Empty array" if $count<1; - next GETIT if $count<1; # if the re-ordered array is empty, skip to next - - PREP: - foreach my $itmlist (@itemorder) { - if ($itmlist) { - $barcode = $itmlist->{'barcode'}; - $itemnumber = $itmlist->{'itemnumber'}; - $holdingbranch = $itmlist->{'holdingbranch'}; - $title = $itmlist->{'title'}; - $callno = $itmlist->{'itemcallnumber'}; - last PREP; # we only want the first def item in the array + + # group available items by branch + my %items_by_branch = (); + foreach my $item (@$available_items) { + push @{ $items_by_branch{ $item->{holdingbranch} } }, $item unless exists $allocated_items{ $item->{itemnumber} }; + } + + # now handle the title-level requests + $num_items_remaining = scalar(@$available_items) - scalar(keys %allocated_items); + foreach my $request (@$hold_requests) { + last if $num_items_remaining <= 0; + next if defined($request->{itemnumber}); # already handled these + + # look for local match first + my $pickup_branch = $request->{branchcode}; + if (exists $items_by_branch{$pickup_branch}) { + my $item = pop @{ $items_by_branch{$pickup_branch} }; + delete $items_by_branch{$pickup_branch} if scalar(@{ $items_by_branch{$pickup_branch} }) == 0; + $item_map{$item->{itemnumber}} = { + borrowernumber => $request->{borrowernumber}, + biblionumber => $request->{biblionumber}, + holdingbranch => $pickup_branch, + pickup_branch => $pickup_branch, + item_level => 0, + reservedate => $request->{reservedate}, + reservenotes => $request->{reservenotes}, + }; + $num_items_remaining--; + } else { + # FIXME implement static, random options + foreach my $branch (sort keys %items_by_branch) { + my $item = pop @{ $items_by_branch{$branch} }; + delete $items_by_branch{$branch} if scalar(@{ $items_by_branch{$branch} }) == 0; + $item_map{$item->{itemnumber}} = { + borrowernumber => $request->{borrowernumber}, + biblionumber => $request->{biblionumber}, + holdingbranch => $branch, + pickup_branch => $pickup_branch, + item_level => 0, + reservedate => $request->{reservedate}, + reservenotes => $request->{reservenotes}, + }; + $num_items_remaining--; + last; + } } } - $sth_load->execute($biblionumber,$itemnumber,$barcode,$surname,$firstname,$phone,$borrno,$cardnumber,$rdate,$title,$callno,$holdingbranch,$pickbranch,$notes); - $sth_load->finish; + return \%item_map; } -$sth->finish; -$dbh->disconnect; - -sub randarray { - my @array = @_; - my @rand = undef; - my $seed = $#array + 1; - my $randnum = int(rand($seed)); - $rand[$randnum] = shift(@array); - while (1) { - my $randnum = int(rand($seed)); - if (!defined($rand[$randnum])) { - $rand[$randnum] = shift(@array); - } - last if ($#array == -1); - } - return @rand; + +=head2 CreatePickListFromItemMap + +=cut + +sub CreatePicklistFromItemMap { + my $item_map = shift; + + my $dbh = C4::Context->dbh; + + my $sth_load=$dbh->prepare(" + INSERT INTO tmp_holdsqueue (biblionumber,itemnumber,barcode,surname,firstname,phone,borrowernumber, + cardnumber,reservedate,title, itemcallnumber, + holdingbranch,pickbranch,notes, item_level_request) + VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) + "); + + foreach my $itemnumber (sort keys %$item_map) { + my $mapped_item = $item_map->{$itemnumber}; + my $biblionumber = $mapped_item->{biblionumber}; + my $borrowernumber = $mapped_item->{borrowernumber}; + my $pickbranch = $mapped_item->{pickup_branch}; + my $holdingbranch = $mapped_item->{holdingbranch}; + my $reservedate = $mapped_item->{reservedate}; + my $reservenotes = $mapped_item->{reservenotes}; + my $item_level = $mapped_item->{item_level}; + + my $item = GetItem($itemnumber); + my $barcode = $item->{barcode}; + my $itemcallnumber = $item->{itemcallnumber}; + + my $borrower = GetMember($borrowernumber); + my $cardnumber = $borrower->{'cardnumber'}; + my $surname = $borrower->{'surname'}; + my $firstname = $borrower->{'firstname'}; + my $phone = $borrower->{'phone'}; + + my $bib = GetBiblioData($biblionumber); + my $title = $bib->{title}; + + $sth_load->execute($biblionumber, $itemnumber, $barcode, $surname, $firstname, $phone, $borrowernumber, + $cardnumber, $reservedate, $title, $itemcallnumber, + $holdingbranch, $pickbranch, $reservenotes, $item_level); + } } +=head2 AddToHoldTargetMap + +=cut + +sub AddToHoldTargetMap { + my $item_map = shift; -print "finished\n"; + my $dbh = C4::Context->dbh; + + my $insert_sql = q( + INSERT INTO hold_fill_targets (borrowernumber, biblionumber, itemnumber, source_branchcode, item_level_request) + VALUES (?, ?, ?, ?, ?) + ); + my $sth_insert = $dbh->prepare($insert_sql); + + foreach my $itemnumber (keys %$item_map) { + my $mapped_item = $item_map->{$itemnumber}; + $sth_insert->execute($mapped_item->{borrowernumber}, $mapped_item->{biblionumber}, $itemnumber, + $mapped_item->{holdingbranch}, $mapped_item->{item_level}); + } +} -- 2.39.5