From 0771f7c6ea249a336ec7e68bd6e1949311ce3856 Mon Sep 17 00:00:00 2001 From: Thomas Berezansky Date: Thu, 21 Jun 2012 09:55:05 -0400 Subject: [PATCH] Make user visible holds based on fill/cancel time Instead of request time. That way holds don't vanish after filling when keeping history was turned on after hold placement. Signed-off-by: Thomas Berezansky Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/090.schema.action.sql | 4 +- .../src/sql/Pg/upgrade/XXXX.purge_holds.sql | 63 +++++++++++++++++++ 2 files changed, 65 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index f309498cb0..639becd626 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -757,8 +757,8 @@ BEGIN FROM action.hold_request WHERE usr = usr_id AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL ) - AND request_time > NOW() - view_age - ORDER BY request_time DESC + AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age + ORDER BY COALESCE(fulfillment_time, cancel_time) DESC LIMIT view_count LOOP RETURN NEXT h; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.purge_holds.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.purge_holds.sql index 7db1607c5e..69ae1dfd32 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.purge_holds.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.purge_holds.sql @@ -98,3 +98,66 @@ BEGIN RETURN purged_holds; END; $func$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$ +DECLARE + h action.hold_request%ROWTYPE; + view_age INTERVAL; + view_count INT; + usr_view_count actor.usr_setting%ROWTYPE; + usr_view_age actor.usr_setting%ROWTYPE; + usr_view_start actor.usr_setting%ROWTYPE; +BEGIN + SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count'; + SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start'; + + FOR h IN + SELECT * + FROM action.hold_request + WHERE usr = usr_id + AND fulfillment_time IS NULL + AND cancel_time IS NULL + ORDER BY request_time DESC + LOOP + RETURN NEXT h; + END LOOP; + + IF usr_view_start.value IS NULL THEN + RETURN; + END IF; + + IF usr_view_age.value IS NOT NULL THEN + -- User opted in and supplied a retention age + IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + END IF; + ELSE + -- User opted in + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + END IF; + + IF usr_view_count.value IS NOT NULL THEN + view_count := oils_json_to_text(usr_view_count.value)::INT; + ELSE + view_count := 1000; + END IF; + + -- show some fulfilled/canceled holds + FOR h IN + SELECT * + FROM action.hold_request + WHERE usr = usr_id + AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL ) + AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age + ORDER BY COALESCE(fulfillment_time, cancel_time) DESC + LIMIT view_count + LOOP + RETURN NEXT h; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; -- 2.43.2