From 1f6e219acf85e7f080c7526e2d377ac2cbe469c9 Mon Sep 17 00:00:00 2001 From: erickson Date: Fri, 28 May 2010 13:25:47 +0000 Subject: [PATCH] repaired misnamed function oils_json_to_string -> oils_json_to_text; updated funcs to use the setting name provided by the seed data retention_start_date -> retention_start git-svn-id: svn://svn.open-ils.org/ILS/trunk@16528 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 24 ++-- ...schema.user-visiable-holds-circs-fixes.sql | 108 ++++++++++++++++++ 3 files changed, 121 insertions(+), 13 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0283.schema.user-visiable-holds-circs-fixes.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 1047326fc7..8ed06cc606 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 ('0282'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0283'); -- berick 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 dd09731f77..26b9898f5a 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -584,18 +584,18 @@ DECLARE usr_view_start actor.usr_setting%ROWTYPE; BEGIN SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; - SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start_date'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; IF usr_view_age.value IS NOT NULL AND usr_view_start.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); + 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_string(usr_view_age.value)::INTERVAL; + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; END IF; ELSIF usr_view_start.value IS NOT NULL THEN -- User opted in - view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ); + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); ELSE -- User did not opt in RETURN; @@ -644,13 +644,13 @@ BEGIN END IF; IF usr_view_age.value IS NOT NULL THEN - view_age := oils_json_to_string(usr_view_age.value)::INTERVAL; + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; ELSE view_age := '2000 years'::INTERVAL; END IF; IF usr_view_count.value IS NOT NULL THEN - view_count := oils_json_to_string(usr_view_count.value)::INT; + view_count := oils_json_to_text(usr_view_count.value)::INT; ELSE view_count := 1000; END IF; @@ -735,16 +735,16 @@ BEGIN SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; usr_keep_start.value := NULL; - SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start_date'; + SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN - IF oils_json_to_string(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ) THEN - keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ); + IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); ELSE - keep_age := oils_json_to_string(usr_keep_age.value)::INTERVAL; + keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; END IF; ELSIF usr_keep_start.value IS NOT NULL THEN - keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ); + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); ELSE keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTEVAL ); END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0283.schema.user-visiable-holds-circs-fixes.sql b/Open-ILS/src/sql/Pg/upgrade/0283.schema.user-visiable-holds-circs-fixes.sql new file mode 100644 index 0000000000..500ca0ca37 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0283.schema.user-visiable-holds-circs-fixes.sql @@ -0,0 +1,108 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0283'); + +CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$ +DECLARE + c action.circulation%ROWTYPE; + view_age INTERVAL; + usr_view_age actor.usr_setting%ROWTYPE; + usr_view_start actor.usr_setting%ROWTYPE; +BEGIN + SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; + + IF usr_view_age.value IS NOT NULL AND usr_view_start.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; + ELSIF usr_view_start.value IS NOT NULL THEN + -- User opted in + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + -- User did not opt in + RETURN; + END IF; + + FOR c IN + SELECT * + FROM action.circulation + WHERE usr = usr_id + AND parent_circ IS NULL + AND xact_start > NOW() - view_age + ORDER BY xact_start + LOOP + RETURN NEXT c; + END LOOP; + + RETURN; +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; +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'; + + 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; + + RETURN; + END IF; + + IF usr_view_age.value IS NOT NULL THEN + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + ELSE + view_age := '2000 years'::INTERVAL; + 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; + + -- Else, show those /and/ 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 + LIMIT view_count + LOOP + RETURN NEXT h; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.43.2