From ed161bb9d9dbbe5bd7543fcc06d6069aba288bec Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Tue, 27 Jun 2023 14:42:22 -0400 Subject: [PATCH] LP 1904737: Expand copy statuses for the pull list Evergreen limits copies on the pull list to those with hard coded status values of 0 or 7. This commit expands the range of copies available to fill holds to any copy whose status has both the holdable and is_available fields set to true. This modification affords sites more control over what copy statuses can fill holds, including custom ones. The hold targeter and database functions that calculate counts of copies available to fill holds are also modified to use the new logic. In order to maintain timely performance, new indexes are created on the asset copy and serial unit table circ_lib columns the deleted column is false. The previous indexes on these columns where the copy status is 0 or 7 are dropped. Queries that used the old indexes are modified to use the new indexes. A database regression tests are added to check that the new indexes exist and the the old indexes do not. Live tests are added to the live_t/20-hold-targeter.t test suite to exercise the new functionality and verify that it works as expected for the hold targeter and the asset.staff_ou_metarecord_copy_count database function. (If someone could figure out a decent way to live test the pull list and the other database functions, that would be great.) On a default system, only statuses 0 and 7 match the new criteria to fill holds, so testing the pull list would amount to checking that the behavior has not changed after installation of the new code. In order to verify that the change actually works, one could add a new status or modify an existing status so that the holdable and is_available fields are true. After running the hold targeter, a different number of copies should be available to fill some holds. Different copies may appear on the pull list as a result. Signed-off-by: Jason Stephenson Signed-off-by: John Amundson Signed-off-by: Jane Sandberg --- Open-ILS/examples/fm_IDL.xml | 3 +- .../app/staff/share/holds/grid.component.ts | 4 +- .../lib/OpenILS/Application/Circ/Holds.pm | 14 +- .../Application/Storage/Publisher/action.pm | 5 +- .../lib/OpenILS/Utils/HoldTargeter.pm | 22 ++- .../src/perlmods/live_t/20-hold-targeter.t | 147 ++++++++++++++++- Open-ILS/src/sql/Pg/040.schema.asset.sql | 9 +- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 2 +- Open-ILS/src/sql/Pg/210.schema.serials.sql | 1 + .../sql/Pg/t/regress/lp1904737_has_indexes.pg | 13 ++ ...chema-expand-status-for-hold-pull-list.sql | 156 ++++++++++++++++++ .../expansion_of_copy_status_for_holds.adoc | 8 + 12 files changed, 371 insertions(+), 13 deletions(-) mode change 100644 => 100755 Open-ILS/src/perlmods/live_t/20-hold-targeter.t create mode 100644 Open-ILS/src/sql/Pg/t/regress/lp1904737_has_indexes.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema-expand-status-for-hold-pull-list.sql create mode 100644 docs/RELEASE_NOTES_NEXT/Circulation/expansion_of_copy_status_for_holds.adoc diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 6065600fe7..022dfc9dea 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6962,7 +6962,8 @@ SELECT usr, ahr.cancel_time IS NULL AND csp.id IS NULL AND (ahr.expire_time is NULL OR ahr.expire_time > NOW()) AND - acp.status IN (0,7) + acp.deleted IS FALSE AND + acp.status IN (SELECT id FROM config.copy_status WHERE holdable IS TRUE AND is_available IS TRUE) ]]> diff --git a/Open-ILS/src/eg2/src/app/staff/share/holds/grid.component.ts b/Open-ILS/src/eg2/src/app/staff/share/holds/grid.component.ts index 47caf682a7..a223db5070 100644 --- a/Open-ILS/src/eg2/src/app/staff/share/holds/grid.component.ts +++ b/Open-ILS/src/eg2/src/app/staff/share/holds/grid.component.ts @@ -287,8 +287,10 @@ export class HoldsGridComponent implements OnInit { // There are aliases for these (cp_status, cp_circ_lib), // but the API complains when I use them. - filters['cp.status'] = [0, 7]; + filters['cp.status'] = {"in":{"select":{"ccs":["id"]},"from":"ccs","where":{"holdable":'t',"is_available":'t'}}}; filters['cp.circ_lib'] = this.pullListOrg; + // Avoid deleted copies AND this uses a database index on copy circ_lib where deleted is false. + filters['cp.deleted'] = 'f'; return filters; } diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm index d295fa2e72..1ad9713a40 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm @@ -2035,7 +2035,7 @@ sub print_hold_pull_list_stream { "field" => "id", "fkey" => "current_copy", "filter" => { - "circ_lib" => $$params{org_id}, "status" => [0,7] + "circ_lib" => $$params{org_id} }, "join" => { "acn" => { @@ -2059,6 +2059,14 @@ sub print_hold_pull_list_stream { "filter" => { "location" => {"=" => {"+acp" => "location"}} } + }, + "ccs" => { + "field" => "id", + "fkey" => "status", + "filter" => { + "holdable" => "t", + "is_available" => "t" + } } } } @@ -4238,10 +4246,10 @@ sub hold_has_copy_at { filter => { holdable => 't', deleted => 'f' }, fkey => 'location' }, - ccs => {field => 'id', filter => { holdable => 't'}, fkey => 'status' } + ccs => {field => 'id', filter => {holdable => 't', is_available => 't'}, fkey => 'status'} } }, - where => {'+acp' => { circulate => 't', deleted => 'f', holdable => 't', circ_lib => $org_unit, status => [0,7]}}, + where => {'+acp' => { circulate => 't', deleted => 'f', holdable => 't', circ_lib => $org_unit }}, limit => 1 }; 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 07a8bfe41d..af0fb7f5c0 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 @@ -793,7 +793,8 @@ sub hold_pull_list { my $count = 1 if ($self->api_name =~/count$/o); my $status_filter = ''; - $status_filter = 'AND a.status IN (0,7)' if ($self->api_name =~/status_filtered/o); + $status_filter = 'AND a.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available)' + if ($self->api_name =~/status_filtered/); my $select = <<" SQL"; SELECT h.* @@ -801,6 +802,7 @@ sub hold_pull_list { JOIN $a_table a ON (h.current_copy = a.id) LEFT JOIN $ord_table ord ON (a.location = ord.location AND a.circ_lib = ord.org) WHERE a.circ_lib = ? + AND a.deleted IS FALSE AND h.capture_time IS NULL AND h.cancel_time IS NULL AND (h.expire_time IS NULL OR h.expire_time > NOW()) @@ -826,6 +828,7 @@ sub hold_pull_list { FROM $h_table h JOIN $a_table a ON (h.current_copy = a.id) WHERE a.circ_lib = ? + AND a.deleted is FALSE AND h.capture_time IS NULL AND h.cancel_time IS NULL AND (h.expire_time IS NULL OR h.expire_time > NOW()) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm b/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm index 31b8852cb0..093bd3a191 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm @@ -32,6 +32,7 @@ sub new { my $self = { editor => new_editor(), ou_setting_cache => {}, + targetable_statuses => [], %args, }; return bless($self, $class); @@ -255,6 +256,21 @@ sub precache_batch_ou_settings { } } +# Get the list of statuses able to target a hold, i.e. allowed for the +# current_copy. Default to 0 and 7 if there ia a failure. +sub get_targetable_statuses { + my $self = shift; + unless (ref($self->{tagetable_statuses}) eq 'ARRAY' && @{$self->{targetable_statuses}}) { + my $e = $self->{editor}; + $self->{targetable_statuses} = $e->search_config_copy_status({holdable => 't', is_available => 't'}, + {idlist => 1}); + unless (ref($self->{targetable_statuses}) eq 'ARRAY' && @{$self->{targetable_statuses}}) { + $self->{targetable_statuses} = [0,7]; + } + } + return $self->{targetable_statuses}; +} + # ----------------------------------------------------------------------- # Knows how to target a single hold. # ----------------------------------------------------------------------- @@ -843,8 +859,12 @@ sub filter_copies_by_status { # Track checked out copies for later recall $self->recall_copies([grep {$_->{status} == 1} @{$self->copies}]); + my $targetable_statuses = $self->parent->get_targetable_statuses(); $self->copies([ - grep {$_->{status} == 0 || $_->{status} == 7} @{$self->copies} + grep { + my $c = $_; + grep {$c->{status} == $_} @{$targetable_statuses} + } @{$self->copies} ]); return 1; diff --git a/Open-ILS/src/perlmods/live_t/20-hold-targeter.t b/Open-ILS/src/perlmods/live_t/20-hold-targeter.t old mode 100644 new mode 100755 index b06f9a63db..25cd6d5307 --- a/Open-ILS/src/perlmods/live_t/20-hold-targeter.t +++ b/Open-ILS/src/perlmods/live_t/20-hold-targeter.t @@ -2,7 +2,7 @@ use strict; use warnings; -use Test::More tests => 18; +use Test::More tests => 82; diag("General hold targeter tests"); use OpenILS::Const qw/:const/; @@ -185,4 +185,149 @@ $e->update_metabib_metarecord_source_map($mrmap_101) or die $e->die_event; $e->update_metabib_metarecord_source_map($mrmap_42) or die $e->die_event; $e->xact_commit; +# Test Lp 1904737 hold status expansion. +$hold_id = 67; +$hold = $e->retrieve_action_hold_request($hold_id); +my $copy = $e->retrieve_asset_copy($hold->current_copy()); +my $saved_status = $copy->status(); +# change its status to one that won't fill holds +my $cataloging_status = $e->retrieve_config_copy_status(11); +$copy->status($cataloging_status->id()); +$e->xact_begin; +$e->update_asset_copy($copy); +$e->xact_commit(); +$result = target({hold => $hold->id()}); +ok($result->{success}, "Targeting hold $hold_id returned success"); +$hold = $e->retrieve_action_hold_request($hold_id); +isnt($copy->id(), $hold->current_copy(), "Hold $hold_id has new current copy"); +# Check the hold copy map, it should be 1 less than before. +$maps = $e->search_action_hold_copy_map({hold => $hold_id}); +is(scalar(@$maps), 28, "Hold $hold_id has 28 mapped potential copies"); +# Check asset.staff_ou_metarecord_copy_count +$result = $e->json_query({from => ["asset.staff_ou_metarecord_copy_count", 4, 70]}); +is(scalar(@$result), 3, "asset.staff_ou_metarecord_copy_count returns 3 rows"); +for (my $i = 0; $i < 3; $i++) { + my $row = $result->[$i]; + if ($i == 0) { + is($row->{depth}, 0, "Depth of first row is 0"); + is($row->{org_unit}, 1, "Org Unit of first row is 1"); + is($row->{visible}, 31, "Visible of first row is 31"); + is($row->{available}, 25, "Available of first row is 25"); + } elsif ($i == 1) { + is($row->{depth}, 1, "Depth of second row is 1"); + is($row->{org_unit}, 2, "Org Unit of second row is 2"); + is($row->{visible}, 14, "Visible of second row is 14"); + is($row->{available}, 11, "Available of second row is 11"); + } elsif ($i == 2) { + is($row->{depth}, 2, "Depth of third row is 2"); + is($row->{org_unit}, 4, "Org Unit of third row is 4"); + is($row->{visible}, 7, "Visible of third row is 7"); + is($row->{available}, 5, "Available of third row is 5"); + } +} +# Make copy status holdable, retarget the hold and check values again. +$cataloging_status->holdable('t'); +$e->xact_begin; +$e->update_config_copy_status($cataloging_status); +$e->xact_commit; +$cataloging_status = $e->retrieve_config_copy_status(11); +is($cataloging_status->holdable(), 't', "Cataloging status is holdable"); +$result = target({hold => $hold->id()}); +ok($result->{success}, "Targeting hold $hold_id returned success"); +$maps = $e->search_action_hold_copy_map({hold => $hold_id}); +is(scalar(@$maps), 29, "Hold $hold_id has 29 mapped potential copies"); +# Check asset.staff_ou_metarecord_copy_count +$result = $e->json_query({from => ["asset.staff_ou_metarecord_copy_count", 4, 70]}); +is(scalar(@$result), 3, "asset.staff_ou_metarecord_copy_count returns 3 rows"); +for (my $i = 0; $i < 3; $i++) { + my $row = $result->[$i]; + if ($i == 0) { + is($row->{depth}, 0, "Depth of first row is 0"); + is($row->{org_unit}, 1, "Org Unit of first row is 1"); + is($row->{visible}, 31, "Visible of first row is 31"); + is($row->{available}, 25, "Available of first row is 25"); + } elsif ($i == 1) { + is($row->{depth}, 1, "Depth of second row is 1"); + is($row->{org_unit}, 2, "Org Unit of second row is 2"); + is($row->{visible}, 14, "Visible of second row is 14"); + is($row->{available}, 11, "Available of second row is 11"); + } elsif ($i == 2) { + is($row->{depth}, 2, "Depth of third row is 2"); + is($row->{org_unit}, 4, "Org Unit of third row is 4"); + is($row->{visible}, 7, "Visible of third row is 7"); + is($row->{available}, 5, "Available of third row is 5"); + } +} +# Make copy status available, retarget the hold and check values again. +$cataloging_status->is_available('t'); +$e->xact_begin; +$e->update_config_copy_status($cataloging_status); +$e->xact_commit; +$cataloging_status = $e->retrieve_config_copy_status(11); +is($cataloging_status->is_available(), 't', "Cataloging status is available"); +$result = target({hold => $hold->id()}); +ok($result->{success}, "Targeting hold $hold_id returned success"); +$maps = $e->search_action_hold_copy_map({hold => $hold_id}); +is(scalar(@$maps), 29, "Hold $hold_id has 29 mapped potential copies"); +# Check asset.staff_ou_metarecord_copy_count +$result = $e->json_query({from => ["asset.staff_ou_metarecord_copy_count", 4, 70]}); +is(scalar(@$result), 3, "asset.staff_ou_metarecord_copy_count returns 3 rows"); +for (my $i = 0; $i < 3; $i++) { + my $row = $result->[$i]; + if ($i == 0) { + is($row->{depth}, 0, "Depth of first row is 0"); + is($row->{org_unit}, 1, "Org Unit of first row is 1"); + is($row->{visible}, 31, "Visible of first row is 31"); + is($row->{available}, 26, "Available of first row is 26"); + } elsif ($i == 1) { + is($row->{depth}, 1, "Depth of second row is 1"); + is($row->{org_unit}, 2, "Org Unit of second row is 2"); + is($row->{visible}, 14, "Visible of second row is 14"); + is($row->{available}, 12, "Available of second row is 12"); + } elsif ($i == 2) { + is($row->{depth}, 2, "Depth of third row is 2"); + is($row->{org_unit}, 4, "Org Unit of third row is 4"); + is($row->{visible}, 7, "Visible of third row is 7"); + is($row->{available}, 6, "Available of third row is 6"); + } +} +# Reset the copy status, retarget the hold, and check the numbers one last time. +$copy->status($saved_status); +$e->xact_begin; +$e->update_asset_copy($copy); +$e->xact_commit; +$copy = $e->retrieve_asset_copy($copy->id()); +is($copy->status(), $saved_status, "Copy status is $saved_status"); +$result = target({hold => $hold->id()}); +ok($result->{success}, "Targeting hold $hold_id returned success"); +$maps = $e->search_action_hold_copy_map({hold => $hold_id}); +is(scalar(@$maps), 29, "Hold $hold_id has 29 mapped potential copies"); +# Check asset.staff_ou_metarecord_copy_count +$result = $e->json_query({from => ["asset.staff_ou_metarecord_copy_count", 4, 70]}); +is(scalar(@$result), 3, "asset.staff_ou_metarecord_copy_count returns 3 rows"); +for (my $i = 0; $i < 3; $i++) { + my $row = $result->[$i]; + if ($i == 0) { + is($row->{depth}, 0, "Depth of first row is 0"); + is($row->{org_unit}, 1, "Org Unit of first row is 1"); + is($row->{visible}, 31, "Visible of first row is 31"); + is($row->{available}, 26, "Available of first row is 26"); + } elsif ($i == 1) { + is($row->{depth}, 1, "Depth of second row is 1"); + is($row->{org_unit}, 2, "Org Unit of second row is 2"); + is($row->{visible}, 14, "Visible of second row is 14"); + is($row->{available}, 12, "Available of second row is 12"); + } elsif ($i == 2) { + is($row->{depth}, 2, "Depth of third row is 2"); + is($row->{org_unit}, 4, "Org Unit of third row is 4"); + is($row->{visible}, 7, "Visible of third row is 7"); + is($row->{available}, 6, "Available of third row is 6"); + } +} +# Reset cataloging status +$cataloging_status->holdable('f'); +$cataloging_status->is_available('f'); +$e->xact_begin(); +$e->update_config_copy_status($cataloging_status); +$e->commit(); diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 5c9306e1e4..8e9e03ca70 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -110,7 +110,7 @@ CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number); CREATE INDEX cp_creator_idx ON asset.copy ( creator ); CREATE INDEX cp_editor_idx ON asset.copy ( editor ); CREATE INDEX cp_create_date ON asset.copy (create_date); -CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7); +CREATE INDEX cp_extant_by_circ_lib_idx ON asset.copy(circ_lib) WHERE deleted = FALSE OR deleted IS FALSE; CREATE RULE protect_copy_delete AS ON DELETE TO asset.copy DO INSTEAD UPDATE asset.copy SET deleted = TRUE WHERE OLD.id = asset.copy.id; CREATE TABLE asset.copy_part_map ( @@ -708,7 +708,8 @@ BEGIN SELECT -1, ans.id, COUNT( cp.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) + THEN 1 ELSE 0 END ), SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), trans FROM @@ -858,7 +859,7 @@ BEGIN SELECT ans.depth, ans.id, COUNT( cp.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ), COUNT( cp.id ), trans FROM @@ -890,7 +891,7 @@ BEGIN SELECT -1, ans.id, COUNT( cp.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ), COUNT( cp.id ), trans FROM diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 7ef2b3e0bc..7e80c654bb 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -400,7 +400,7 @@ BEGIN WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen LOOP output.copy_count := output.copy_count + 1; - IF hold_map_data.status IN (0,7,12) THEN + IF hold_map_data.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN output.available_count := output.available_count + 1; END IF; END LOOP; diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index eac0097735..f6d7ae2dc7 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -224,6 +224,7 @@ CREATE INDEX unit_cn_idx ON serial.unit (call_number); CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number); CREATE INDEX unit_creator_idx ON serial.unit ( creator ); CREATE INDEX unit_editor_idx ON serial.unit ( editor ); +CREATE INDEX unit_extant_by_circ_lib_idx ON serial.unit(circ_lib) WHERE deleted = FALSE OR deleted IS FALSE; -- must create this rule explicitly; it is not inherited from asset.copy CREATE RULE protect_serial_unit_delete AS ON DELETE TO serial.unit DO INSTEAD UPDATE serial.unit SET deleted = TRUE WHERE OLD.id = serial.unit.id; diff --git a/Open-ILS/src/sql/Pg/t/regress/lp1904737_has_indexes.pg b/Open-ILS/src/sql/Pg/t/regress/lp1904737_has_indexes.pg new file mode 100644 index 0000000000..4f9cdbebb7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/regress/lp1904737_has_indexes.pg @@ -0,0 +1,13 @@ +BEGIN; + +SELECT plan(4); + +SELECT has_index('asset', 'copy', 'cp_extant_by_circ_lib_idx', ARRAY['circ_lib']); + +SELECT has_index('serial', 'unit', 'unit_extant_by_circ_lib_idx', ARRAY['circ_lib']); + +SELECT hasnt_index('asset', 'copy', 'cp_available_by_circ_lib_idx'); + +SELECT hasnt_index('serial', 'unit', 'unit_available_by_circ_lib_idx'); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-expand-status-for-hold-pull-list.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-expand-status-for-hold-pull-list.sql new file mode 100644 index 0000000000..d45da9a606 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-expand-status-for-hold-pull-list.sql @@ -0,0 +1,156 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP INDEX IF EXISTS asset.cp_available_by_circ_lib_idx; + +DROP INDEX IF EXISTS serial.unit_available_by_circ_lib_idx; + +CREATE INDEX cp_extant_by_circ_lib_idx ON asset.copy(circ_lib) WHERE deleted = FALSE OR deleted IS FALSE; + +CREATE INDEX unit_extant_by_circ_lib_idx ON serial.unit(circ_lib) WHERE deleted = FALSE OR deleted IS FALSE; + +CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id BIGINT) RETURNS action.hold_stats AS $func$ +DECLARE + output action.hold_stats%ROWTYPE; + hold_count INT := 0; + copy_count INT := 0; + available_count INT := 0; + hold_map_data RECORD; +BEGIN + + output.hold_count := 0; + output.copy_count := 0; + output.available_count := 0; + + SELECT COUNT( DISTINCT m.hold ) INTO hold_count + FROM action.hold_copy_map m + JOIN action.hold_request h ON (m.hold = h.id) + WHERE m.target_copy = copy_id + AND NOT h.frozen; + + output.hold_count := hold_count; + + IF output.hold_count > 0 THEN + FOR hold_map_data IN + SELECT DISTINCT m.target_copy, + acp.status + FROM action.hold_copy_map m + JOIN asset.copy acp ON (m.target_copy = acp.id) + JOIN action.hold_request h ON (m.hold = h.id) + WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen + LOOP + output.copy_count := output.copy_count + 1; + IF hold_map_data.status IN (SELECT id from config.copy_status where holdable and is_available) THEN + output.available_count := output.available_count + 1; + END IF; + END LOOP; + output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT; + output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT; + + END IF; + + RETURN output; + +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) + THEN 1 ELSE 0 END ), + SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted) + JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted) + JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + + +COMMIT; diff --git a/docs/RELEASE_NOTES_NEXT/Circulation/expansion_of_copy_status_for_holds.adoc b/docs/RELEASE_NOTES_NEXT/Circulation/expansion_of_copy_status_for_holds.adoc new file mode 100644 index 0000000000..bdc269a5c2 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Circulation/expansion_of_copy_status_for_holds.adoc @@ -0,0 +1,8 @@ +== Expansion of Copy Statuses Eligible to Fill Holds == + +Copies with a status that has both the `holdable` and `is_available` +fields set to `true` are now elibible to fill holds. This was +previously limited to copies with a "magical" status of 0 or 7. The +change expands the copies that can fill holds and affords better +control over what copies with what statuses will appear on the holds +pull list or target holds. -- 2.43.2