From 665bd1dcab3ad87b42f94ff000078d9cb545fe91 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Thu, 21 Jun 2018 07:17:48 -0400 Subject: [PATCH] LP1773452: Repeating copy alerts Without considering the checkin_time on the most recent circ for an item, checking in a lost, claims returned, or claims never checked out item would cause copy alerts on checkin repeatedly until it was checked out again. Staff may be confused by this thinking that there is a continuing problem with the item until the alerts go away. Signed-off-by: Jason Boyer Signed-off-by: Jennifer Pringle Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 2 +- .../t/regress/lp1773452_copy_state_post_checkin.pg | 20 ++++++++++ .../XXXX.function.asset.copy_state-update.sql | 44 ++++++++++++++++++++++ 3 files changed, 65 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/t/regress/lp1773452_copy_state_post_checkin.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.asset.copy_state-update.sql diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 24dda3c41b..a7870aa30e 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -1008,7 +1008,7 @@ BEGIN SELECT stop_fines INTO last_circ_stop FROM action.circulation - WHERE target_copy = cid + WHERE target_copy = cid AND checkin_time IS NULL ORDER BY xact_start DESC LIMIT 1; IF FOUND THEN diff --git a/Open-ILS/src/sql/Pg/t/regress/lp1773452_copy_state_post_checkin.pg b/Open-ILS/src/sql/Pg/t/regress/lp1773452_copy_state_post_checkin.pg new file mode 100644 index 0000000000..6a6f2e8284 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/regress/lp1773452_copy_state_post_checkin.pg @@ -0,0 +1,20 @@ +BEGIN; + +SELECT plan(2); + +INSERT INTO asset.copy (id,circ_lib,creator,loan_duration,fine_level,call_number,editor,barcode,dummy_title,dummy_author) + VALUES (8765309,1,1,2,2,-1,1,'8765309','Spooky Dance','Steve Bachman'); + +INSERT INTO action.circulation (id,usr,target_copy,circ_lib,circ_staff,renewal_remaining, + duration_rule,recurring_fine_rule,max_fine_rule,stop_fines,grace_period) + VALUES (8765309,1,8765309,1,1,0, + 1,1,1,'LONGOVERDUE','0 hours'); + +SELECT is(asset.copy_state(8765309),'LONGOVERDUE', 'Copy state is LONGOVERDUE when stop_fines='LONGOVERDUE' and checkin_time is null'); + +-- Our long overdue item has returned! +UPDATE action.circulation SET checkin_time=now() WHERE id=8765309; + +SELECT is(asset.copy_state(8765309),'NORMAL', 'Copy state is NORMAL when stop_fines='LONGOVERDUE' and checkin_time is not null'); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.asset.copy_state-update.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.asset.copy_state-update.sql new file mode 100644 index 0000000000..8ddf6cb24a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.asset.copy_state-update.sql @@ -0,0 +1,44 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$ +DECLARE + last_circ_stop TEXT; + the_copy asset.copy%ROWTYPE; +BEGIN + + SELECT * INTO the_copy FROM asset.copy WHERE id = cid; + IF NOT FOUND THEN RETURN NULL; END IF; + + IF the_copy.status = 3 THEN -- Lost + RETURN 'LOST'; + ELSIF the_copy.status = 4 THEN -- Missing + RETURN 'MISSING'; + ELSIF the_copy.status = 14 THEN -- Damaged + RETURN 'DAMAGED'; + ELSIF the_copy.status = 17 THEN -- Lost and paid + RETURN 'LOST_AND_PAID'; + END IF; + + SELECT stop_fines INTO last_circ_stop + FROM action.circulation + WHERE target_copy = cid AND checkin_time IS NULL + ORDER BY xact_start DESC LIMIT 1; + + IF FOUND THEN + IF last_circ_stop IN ( + 'CLAIMSNEVERCHECKEDOUT', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) THEN + RETURN last_circ_stop; + END IF; + END IF; + + RETURN 'NORMAL'; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.11.0