From fc048034c0a45795057dcea75ad2e876b6d5785a Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 12 Dec 2012 12:12:12 -0500 Subject: [PATCH] Calculated Proximity Adjustments, a new feature Allows customization to the way that Evergreen measures the distance between org units for the purposes of 1) determining what copy at what org unit is best suited for targeting a title-level hold, and 2) determining what hold is best suited for fulfillment by a copy-in-hand at capture (checkin) time. The customization is based on a table 'actor.org_unit_proximity_adjustment', with certain matching criteria that the system compares to properties of the holds and copies in question. This feature is actually side-ported from the FulfILLment project, where it was originally developed by Mike Rylander. Lebbeous Fogle-Weekley was responsible for integration into current Evergreen code, some testing and bug-fixing, and minor refinement of documentation. Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 31 +++++ .../Application/Storage/CDBI/action.pm | 2 +- .../Application/Storage/Publisher/action.pm | 71 ++++++----- .../Application/Storage/Publisher/asset.pm | 32 ++++- Open-ILS/src/sql/Pg/005.schema.actors.sql | 21 ++++ Open-ILS/src/sql/Pg/020.schema.functions.sql | 11 ++ Open-ILS/src/sql/Pg/090.schema.action.sql | 73 +++++++++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 2 + .../upgrade/XXXX.schema.org_prox_adjust.sql | 115 ++++++++++++++++++ .../config/org_unit_proximity_adjustment.tt2 | 85 +++++++++++++ Open-ILS/web/opac/locale/en-US/lang.dtd | 1 + .../staff_client/chrome/content/main/menu.js | 4 + .../chrome/content/main/menu_frame_menus.xul | 2 + .../calculated-proximity-adjustments.txt | 10 ++ .../Circ/calculated-proximity-adjustments.txt | 46 +++++++ 15 files changed, 472 insertions(+), 34 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql create mode 100644 Open-ILS/src/templates/conify/global/config/org_unit_proximity_adjustment.tt2 create mode 100644 docs/RELEASE_NOTES_NEXT/calculated-proximity-adjustments.txt create mode 100644 docs/TechRef/Circ/calculated-proximity-adjustments.txt diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index ce64b5fb9b..771ab0b2a3 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4103,6 +4103,7 @@ SELECT usr, + @@ -4870,6 +4871,36 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm index 48686643f3..c79371057d 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm @@ -114,7 +114,7 @@ package action::hold_copy_map; use base qw/action/; __PACKAGE__->table('action_hold_copy_map'); __PACKAGE__->columns(Primary => 'id'); -__PACKAGE__->columns(Essential => qw/hold target_copy/); +__PACKAGE__->columns(Essential => qw/hold target_copy proximity/); #------------------------------------------------------------------------------- diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm index 4101309eae..60fe6b784c 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm @@ -294,8 +294,8 @@ sub nearest_hold { local $OpenILS::Application::Storage::WRITE = 1; my $holdsort = isTrue($fifo) ? - "pgt.hold_priority, CASE WHEN h.cut_in_line IS TRUE THEN 0 ELSE 1 END, h.request_time, h.selection_depth DESC, p.prox " : - "p.prox, pgt.hold_priority, CASE WHEN h.cut_in_line IS TRUE THEN 0 ELSE 1 END, h.selection_depth DESC, h.request_time "; + "pgt.hold_priority, CASE WHEN h.cut_in_line IS TRUE THEN 0 ELSE 1 END, h.request_time, h.selection_depth DESC, COALESCE(hm.proximity, h.prox) " : + "COALESCE(hm.proximity, h.prox), pgt.hold_priority, CASE WHEN h.cut_in_line IS TRUE THEN 0 ELSE 1 END, h.selection_depth DESC, h.request_time "; my $ids = action::hold_request->db_Main->selectcol_arrayref(<<" SQL", {}, $here, $cp, $age); SELECT h.id @@ -1293,8 +1293,12 @@ sub new_hold_copy_targeter { # map the potentials, so that we can pick up checkins # XXX Loop-based targeting may require that /only/ copies from this loop should be added to # XXX the potentials list. If this is the cased, hold_copy_map creation will move down further. + my $pu_lib = ''.$hold->pickup_lib; + my $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold ); $log->debug( "\tMapping ".scalar(@$all_copies)." potential copies for hold ".$hold->id); - action::hold_copy_map->create( { hold => $hold->id, target_copy => $_->id } ) for (@$all_copies); + for my $prox ( keys %$prox_list ) { + action::hold_copy_map->create( { proximity => $prox, hold => $hold->id, target_copy => $_->id } ) for (@{$$prox_list{$prox}}); + } #$client->status( new OpenSRF::DomainObject::oilsContinueStatus ); @@ -1374,26 +1378,23 @@ sub new_hold_copy_targeter { } } - my $pu_lib = ''.$hold->pickup_lib; + # reset prox list after trimming good copies + $prox_list = create_prox_list( $self, $pu_lib, \@good_copies, $hold ); - my $prox_list = []; - $$prox_list[0] = - [ - grep { - ''.$_->circ_lib eq $pu_lib && - ( $_->status == 0 || $_->status == 7 ) - } @good_copies - ]; - $all_copies = [grep { $_->status == 0 || $_->status == 7 } grep {''.$_->circ_lib ne $pu_lib } @good_copies]; - # $all_copies is now a list of copies not at the pickup library - - my $best; - if ($hold->hold_type eq 'R' || $hold->hold_type eq 'F') { # Recall/Force holds bypass hold rules. - $best = $good_copies[0] if(scalar @good_copies); - } else { - $best = choose_nearest_copy($hold, $prox_list); - } + my $min_prox = [ sort keys %$prox_list ]->[0]; + my $best; + if ($hold->hold_type eq 'R' || $hold->hold_type eq 'F') { # Recall/Force holds bypass hold rules. + $best = $good_copies[0] if(scalar @good_copies); + } else { + $best = choose_nearest_copy($hold, { $min_prox => delete($$prox_list{$min_prox}) }); + } + + $all_copies = []; + for my $prox (keys %$prox_list) { + push @$all_copies, @{$$prox_list{$prox}}; + } + $client->status( new OpenSRF::DomainObject::oilsContinueStatus ); if (!$best) { @@ -1481,11 +1482,12 @@ sub new_hold_copy_targeter { die "OK\n"; } - } - $prox_list = create_prox_list( $self, $pu_lib, $all_copies ); + $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold ); - $client->status( new OpenSRF::DomainObject::oilsContinueStatus ); + $client->status( new OpenSRF::DomainObject::oilsContinueStatus ); + + } $best = choose_nearest_copy($hold, $prox_list); } @@ -1806,6 +1808,10 @@ sub reservation_targeter { $log->debug("\t".scalar(@good_resources)." resources available for targeting..."); + # LFW: note that after the inclusion of hold proximity + # adjustment, this prox_list is the only prox_list + # array in this perl package. Other occurences are + # hashes. my $prox_list = []; $$prox_list[0] = [ @@ -1938,10 +1944,10 @@ sub choose_nearest_copy { my $hold = shift; my $prox_list = shift; - for my $p ( 0 .. int( scalar(@$prox_list) - 1) ) { - next unless (ref $$prox_list[$p]); + for my $p ( sort keys %$prox_list ) { + next unless (ref $$prox_list{$p}); - my @capturable = @{ $$prox_list[$p] }; + my @capturable = @{ $$prox_list{$p} }; next unless (@capturable); my $rand = int(rand(scalar(@capturable))); @@ -1970,12 +1976,13 @@ sub create_prox_list { my $self = shift; my $lib = shift; my $copies = shift; + my $hold = shift; my $actor = OpenSRF::AppSession->create('open-ils.actor'); - my @prox_list; + my %prox_list; for my $cp (@$copies) { - my ($prox) = $self->method_lookup('open-ils.storage.asset.copy.proximity')->run( $cp, $lib ); + my ($prox) = $self->method_lookup('open-ils.storage.asset.copy.proximity')->run( $cp, $lib, $hold ); next unless (defined($prox)); my $copy_circ_lib = ''.$cp->circ_lib; @@ -1986,12 +1993,12 @@ sub create_prox_list { $self->{target_weight}{$copy_circ_lib} = $self->{target_weight}{$copy_circ_lib}{value} if (ref $self->{target_weight}{$copy_circ_lib}); $self->{target_weight}{$copy_circ_lib} ||= 1; - $prox_list[$prox] = [] unless defined($prox_list[$prox]); + $prox_list{$prox} = [] unless defined($prox_list{$prox}); for my $w ( 1 .. $self->{target_weight}{$copy_circ_lib} ) { - push @{$prox_list[$prox]}, $cp; + push @{$prox_list{$prox}}, $cp; } } - return \@prox_list; + return \%prox_list; } sub volume_hold_capture { diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/asset.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/asset.pm index 42fb89161f..90ca204c6b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/asset.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/asset.pm @@ -396,10 +396,40 @@ sub copy_proximity { my $client = shift; my $cp = shift; - my $org = shift; + my $org = shift; # hold pickup lib + my $hold = shift; return unless ($cp && $org); + if ($hold) { + my $row = action::hold_request->db_Main->selectrow_hashref( + 'SELECT proximity AS prox FROM action.hold_copy_map WHERE hold = ? and target_copy = ?', + {}, + "$hold", + "$cp" + ); + return $row->{prox} if $row; + + # There was a bug here before. + # action.hold_copy_calculated_proximity() was called with a + # third argument, $org. Wrong. a.hccp() interprets its third + # argument as an optional override of copy circ lib. $org + # here is hold pickup lib. This had the effect of basically + # measuring the distance between a hold's pickup lib and + # itself, which is always zero, so all proximities landing in + # the hold copy map were zero. + + $log->debug("Calculating copy proximity with: action.hold_copy_calculated_proximity($hold,$cp)", DEBUG); + $row = action::hold_request->db_Main->selectrow_hashref( + 'SELECT action.hold_copy_calculated_proximity(?,?) AS prox', + {}, + "$hold", + "$cp" + ); + + return $row->{prox} if $row; + } + $cp = asset::copy->retrieve($cp) unless (ref($cp)); return unless $cp; diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 86958525ce..176f4654d1 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -380,6 +380,27 @@ default entry. $$; +CREATE TABLE actor.org_unit_proximity_adjustment ( + id SERIAL PRIMARY KEY, + item_circ_lib INT REFERENCES actor.org_unit (id), + item_owning_lib INT REFERENCES actor.org_unit (id), + copy_location INT REFERENCES asset.copy_location (id), + hold_pickup_lib INT REFERENCES actor.org_unit (id), + hold_request_lib INT REFERENCES actor.org_unit (id), + pos INT NOT NULL DEFAULT 0, + absolute_adjustment BOOL NOT NULL DEFAULT FALSE, + prox_adjustment NUMERIC, + circ_mod TEXT, -- REFERENCES config.circ_modifier (code), + CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL) +); +CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod); +CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib); +CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib); +CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location); +CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib); +CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib); +CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod); + CREATE TABLE actor.hours_of_operation ( id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, dow_0_open TIME NOT NULL DEFAULT '09:00', diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index f69bfa4aa0..739b3177db 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -193,6 +193,17 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABL SELECT * FROM org_unit_ancestors_distance; $$ LANGUAGE SQL STABLE ROWS 1; +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT * FROM org_unit_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_ancestors($1) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 833d7bce6a..3f7bb6f6de 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -452,6 +452,7 @@ CREATE TABLE action.hold_copy_map ( id BIGSERIAL PRIMARY KEY, hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance + proximity NUMERIC, CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) ); -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold); @@ -972,5 +973,77 @@ query-based fieldsets. Returns NULL if successful, or an error message if not. $$; +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(ahr_id INT, acp_id BIGINT, context_ou INT DEFAULT NULL) RETURNS NUMERIC AS $f$ +DECLARE + aoupa actor.org_unit_proximity_adjustment%ROWTYPE; + ahr action.hold_request%ROWTYPE; + acp asset.copy%ROWTYPE; + acn asset.call_number%ROWTYPE; + acl asset.copy_location%ROWTYPE; + baseline_prox NUMERIC; + + icl_list INT[]; + iol_list INT[]; + isl_list INT[]; + hpl_list INT[]; + hrl_list INT[]; + +BEGIN + + SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id; + SELECT * INTO acp FROM asset.copy WHERE id = acp_id; + SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number; + SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location; + + IF context_ou IS NULL THEN + context_ou := acp.circ_lib; + END IF; + + -- First, gather the baseline proximity of "here" to pickup lib + SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib; + + -- Find any absolute adjustments, and set the baseline prox to that + SELECT adj.* INTO aoupa + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + ORDER BY + COALESCE(acp_cl.distance,999) + + COALESCE(acn_ol.distance,999) + + COALESCE(acl_ol.distance,999) + + COALESCE(ahr_pl.distance,999) + + COALESCE(ahr_rl.distance,999), + adj.pos + LIMIT 1; + + IF FOUND THEN + baseline_prox := aoupa.prox_adjustment; + END IF; + + -- Now find any relative adjustments, and change the baseline prox based on them + FOR aoupa IN + SELECT adj.* + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + NOT absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + LOOP + baseline_prox := baseline_prox + aoupa.prox_adjustment; + END LOOP; + + RETURN baseline_prox; +END; +$f$ LANGUAGE PLPGSQL; COMMIT; diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 414e5e4614..36e9438482 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -39,6 +39,8 @@ ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_billing_address_fkey FO ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_holds_address_fkey FOREIGN KEY (holds_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_ill_address_fkey FOREIGN KEY (ill_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.org_unit_proximity_adjustment ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE acq.provider ADD CONSTRAINT acq_provider_edi_default_fkey FOREIGN KEY (edi_default) REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE biblio.record_note ADD CONSTRAINT biblio_record_note_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql new file mode 100644 index 0000000000..d9eb082a5b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql @@ -0,0 +1,115 @@ +BEGIN; + +CREATE TABLE actor.org_unit_proximity_adjustment ( + id SERIAL PRIMARY KEY, + item_circ_lib INT REFERENCES actor.org_unit (id), + item_owning_lib INT REFERENCES actor.org_unit (id), + copy_location INT REFERENCES asset.copy_location (id), + hold_pickup_lib INT REFERENCES actor.org_unit (id), + hold_request_lib INT REFERENCES actor.org_unit (id), + pos INT NOT NULL DEFAULT 0, + absolute_adjustment BOOL NOT NULL DEFAULT FALSE, + prox_adjustment NUMERIC, + circ_mod TEXT, -- REFERENCES config.circ_modifier (code), + CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL) +); +CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod); +CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib); +CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib); +CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location); +CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib); +CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib); +CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod); + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT * FROM org_unit_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(ahr_id INT, acp_id BIGINT, context_ou INT DEFAULT NULL) RETURNS NUMERIC AS $f$ +DECLARE + aoupa actor.org_unit_proximity_adjustment%ROWTYPE; + ahr action.hold_request%ROWTYPE; + acp asset.copy%ROWTYPE; + acn asset.call_number%ROWTYPE; + acl asset.copy_location%ROWTYPE; + baseline_prox NUMERIC; + + icl_list INT[]; + iol_list INT[]; + isl_list INT[]; + hpl_list INT[]; + hrl_list INT[]; + +BEGIN + + SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id; + SELECT * INTO acp FROM asset.copy WHERE id = acp_id; + SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number; + SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location; + + IF context_ou IS NULL THEN + context_ou := acp.circ_lib; + END IF; + + -- First, gather the baseline proximity of "here" to pickup lib + SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib; + + -- Find any absolute adjustments, and set the baseline prox to that + SELECT adj.* INTO aoupa + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + ORDER BY + COALESCE(acp_cl.distance,999) + + COALESCE(acn_ol.distance,999) + + COALESCE(acl_ol.distance,999) + + COALESCE(ahr_pl.distance,999) + + COALESCE(ahr_rl.distance,999), + adj.pos + LIMIT 1; + + IF FOUND THEN + baseline_prox := aoupa.prox_adjustment; + END IF; + + -- Now find any relative adjustments, and change the baseline prox based on them + FOR aoupa IN + SELECT adj.* + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + NOT absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + LOOP + baseline_prox := baseline_prox + aoupa.prox_adjustment; + END LOOP; + + RETURN baseline_prox; +END; +$f$ LANGUAGE PLPGSQL; + +ALTER TABLE actor.org_unit_proximity_adjustment + ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey + FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code) + DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC; + +COMMIT; diff --git a/Open-ILS/src/templates/conify/global/config/org_unit_proximity_adjustment.tt2 b/Open-ILS/src/templates/conify/global/config/org_unit_proximity_adjustment.tt2 new file mode 100644 index 0000000000..1c1a2ab8b4 --- /dev/null +++ b/Open-ILS/src/templates/conify/global/config/org_unit_proximity_adjustment.tt2 @@ -0,0 +1,85 @@ +[% WRAPPER base.tt2 %] +[% ctx.page_title = 'Org Unit Proximity Adjustments' %] +
+
+
[% ctx.page_title %]
+
+ + +
+
+
+ Show adjustments involving this branch or deeper: + +
+ + + + + + + + + +
+
+ + +[% END %] diff --git a/Open-ILS/web/opac/locale/en-US/lang.dtd b/Open-ILS/web/opac/locale/en-US/lang.dtd index e0cb546b9a..109c9a6a25 100644 --- a/Open-ILS/web/opac/locale/en-US/lang.dtd +++ b/Open-ILS/web/opac/locale/en-US/lang.dtd @@ -767,6 +767,7 @@ + diff --git a/Open-ILS/xul/staff_client/chrome/content/main/menu.js b/Open-ILS/xul/staff_client/chrome/content/main/menu.js index 29f09010dd..1bb5db98e3 100644 --- a/Open-ILS/xul/staff_client/chrome/content/main/menu.js +++ b/Open-ILS/xul/staff_client/chrome/content/main/menu.js @@ -1021,6 +1021,10 @@ main.menu.prototype = { ['oncommand'], function(event) { open_eg_web_page('conify/global/config/z3950_source', null, event); } ], + 'cmd_server_admin_org_unit_proximity_adjustment' : [ + ['oncommand'], + function(event) { open_eg_web_page('conify/global/config/org_unit_proximity_adjustment', null, event); } + ], 'cmd_server_admin_circ_mod' : [ ['oncommand'], function(event) { open_eg_web_page('conify/global/config/circ_modifier', null, event); } diff --git a/Open-ILS/xul/staff_client/chrome/content/main/menu_frame_menus.xul b/Open-ILS/xul/staff_client/chrome/content/main/menu_frame_menus.xul index 8967f9c2a3..347fac7985 100644 --- a/Open-ILS/xul/staff_client/chrome/content/main/menu_frame_menus.xul +++ b/Open-ILS/xul/staff_client/chrome/content/main/menu_frame_menus.xul @@ -257,6 +257,7 @@ + @@ -599,6 +600,7 @@ + diff --git a/docs/RELEASE_NOTES_NEXT/calculated-proximity-adjustments.txt b/docs/RELEASE_NOTES_NEXT/calculated-proximity-adjustments.txt new file mode 100644 index 0000000000..61a81a709b --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/calculated-proximity-adjustments.txt @@ -0,0 +1,10 @@ +Calculated Proximity Adjustments +================================ + +Allows customization to the way that Evergreen measures the distance between +org units for the purposes of 1) determining what copy at what org unit is best +suited for targeting a title-level hold, and 2) determining what hold is best +suited for fulfillment by a copy-in-hand at capture (checkin) time. The +customization is based on a table 'actor.org_unit_proximity_adjustment', with +certain matching criteria that the system compares to properties of the holds +and copies in question. diff --git a/docs/TechRef/Circ/calculated-proximity-adjustments.txt b/docs/TechRef/Circ/calculated-proximity-adjustments.txt new file mode 100644 index 0000000000..586d6fc31e --- /dev/null +++ b/docs/TechRef/Circ/calculated-proximity-adjustments.txt @@ -0,0 +1,46 @@ +Calculated Proximity Adjustments +================================ + +Summary +------- + +Today in Evergreen, the way in which organizational hierarchy can be taken into account during hold targeting and capture is through the evaluation of Org Unit Proximity.  This is defined as the number of graph edges between Org Units, and for holds, specifically the distance between the capturing library and the pickup library. This value is used to rank sets of potential copies for holds based on their apparent nearness or proximity to the pickup lib at targeting time and to the checkin lib at op-capture time (in certain configurations). + +Evergreen needs a mechanism by which the proximity between libraries can be adjusted for the purpose of effecting hold capture.  This will support several use cases, including, but not limited to: + + * Causing a specific library to be targeted for holds in preference to all others. + * Causing a specific library to be targeted for holds in preference to all others except for the pickup library. + * Allowing transit distance to be more accurately reflected in hold order choice, for instance, causing nearby systems to have lower effective transit distances than widely separated systems. + * Reporting on the true cost of transiting items in a broadly distributed consortium. + +Overview +-------- + +Evergreen can be made to provide a way to specify two types of proximity adjustment: Relative and Absolute. + +Relative proximity adjustment will allow Org Units, and descendants thereof, to be treated as closer or farther from one another than the simple edge distance describes by adding or subtracting full or partial edge distance amounts to the baseline edge distance under configured circumstances. + +Absolute proximity adjustment will allow Org Units, and descendants thereof, to be viewed as having a specific distance from one another that replaces the baseline edge distance under configure circumstances. This will naturally have an impact on how potential copies are evaluated for their 'proximity' when targeting holds and capturing copies for holds. + +Plan +---- + +Create a configuration interface allowing certain item- and hold-level criteria to be evaluated at targeting time.  Among the criteria would be: + + * Item circ library (or ancestor thereof) + * Item owning library (or ancestor thereof) + * Hold pickup library (or ancestor thereof) + * Hold request library (or ancestor thereof) + * Item circ modifier + * Item shelving location + +At least one criterion must be supplied.  These criteria would be ranked by order, and reordering allowed. + +In addition to these criteria, an Absolute or Relative proximity adjustment would be supplied.  For Absolute proximity adjustments, the highest-ranked criteria-matching rule would be used for the copy.  For Relative proximity adjustments, all applicable adjustments would be summed.  In the case that both Absolute and Relative adjustments are found for the currently evaluated item and hold, the Absolute proximity adjustment will replace the baseline edge distance and then be modified by the Relative proximity adjustment calculation. + +To support both targeting-time and capture-time use of this derived proximity information, the calculated value will be stored on the hold-copy map.  In conjunction with the Custom Best-hold Sort Order proposal, this information would then be available for use in choosing the hold to be filled by a particular copy. + + +//// +vim: ft=asciidoc +//// -- 2.43.2