[Koha-patches] [PATCH] bug 2522 [3/3]: populate hold_fill_targets
Galen Charlton
galen.charlton at liblime.com
Fri Aug 15 03:59:51 CEST 2008
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()
---
misc/cronjobs/holds/build_holds_queue.pl | 446 +++++++++++++++++++++---------
1 files changed, 319 insertions(+), 127 deletions(-)
diff --git a/misc/cronjobs/holds/build_holds_queue.pl b/misc/cronjobs/holds/build_holds_queue.pl
index e422248..ae075b7 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
- at 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});
+ }
+}
--
1.5.5.GIT
More information about the Koha-patches
mailing list