From 68d1f1968a4955db496c46cd57d135d78e85b7ea Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 1 Jun 2010 14:48:11 +0000 Subject: [PATCH] add usr_setting_type of history.hold.retention_start for setting "opt-in" date of holds retention git-svn-id: svn://svn.open-ils.org/ILS/trunk@16540 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 45 ++++++------ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 6 ++ ...tion.usr_visible_holds-retention_start.sql | 72 +++++++++++++++++++ 4 files changed, 102 insertions(+), 23 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0287.schema.action.usr_visible_holds-retention_start.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 6e6ceaf211..382d40ac11 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -65,7 +65,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0286'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0287'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 26b9898f5a..8914d8779e 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -623,30 +623,37 @@ DECLARE 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'; - IF usr_view_count.value IS NULL AND usr_view_age.value IS NULL THEN - -- Only show "open" holds - FOR h IN - SELECT * - FROM action.hold_request - WHERE usr = usr_id - AND fulfillment_time IS NULL - AND cancel_time IS NULL - ORDER BY xact_start - LOOP - RETURN NEXT h; - END LOOP; + 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 - view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + -- User opted in and supplied a retention age + IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_string(usr_view_age.value)::INTERVAL; + END IF; ELSE - view_age := '2000 years'::INTERVAL; + -- User opted in + view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ); END IF; IF usr_view_count.value IS NOT NULL THEN @@ -655,20 +662,14 @@ BEGIN view_count := 1000; END IF; - -- Else, show those /and/ some fulfilled/canceled holds + -- show some fulfilled/canceled holds FOR h IN - SELECT * - FROM action.hold_request - WHERE usr = usr_id - AND fulfillment_time IS NULL - AND cancel_time IS NULL - UNION ALL SELECT * 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 + ORDER BY request_time DESC LIMIT view_count LOOP RETURN NEXT h; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 7d9db2e68e..f285a9dde9 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -5190,6 +5190,12 @@ INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatyp oils_i18n_gettext('history.hold.retention_age','Historical Hold Retention Age','cust','label'), oils_i18n_gettext('history.hold.retention_age','Historical Hold Retention Age','cust','description'), 'interval' + ),( + 'history.hold.retention_start', + TRUE, + oils_i18n_gettext('history.hold.retention_start','Historical Hold Retention Start Date','cust','label'), + oils_i18n_gettext('history.hold.retention_start','Historical Hold Retention Start Date','cust','description'), + 'interval' ),( 'history.hold.retention_count', TRUE, diff --git a/Open-ILS/src/sql/Pg/upgrade/0287.schema.action.usr_visible_holds-retention_start.sql b/Open-ILS/src/sql/Pg/upgrade/0287.schema.action.usr_visible_holds-retention_start.sql new file mode 100644 index 0000000000..2e491e5fc4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0287.schema.action.usr_visible_holds-retention_start.sql @@ -0,0 +1,72 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0287'); --miker + +INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype) + VALUES ('history.hold.retention_start', FALSE, 'Historical Hold Retention Start Date', 'Historical Hold Retention Start Date', 'date'); + +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_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_string(usr_view_age.value)::INTERVAL; + END IF; + ELSE + -- User opted in + view_age := AGE(NOW(), oils_json_to_string(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 request_time > NOW() - view_age + ORDER BY request_time DESC + LIMIT view_count + LOOP + RETURN NEXT h; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.43.2