From 34a58ac0692eb1839bd78b3a14927d2598928df5 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 19 May 2009 02:20:19 +0000 Subject: [PATCH] adding invalidating stop_date column to usr_standing_penalty, which is checked by circ/hold stored procs. ML code (circ/hold/renew scripts) will need to do the same. git-svn-id: svn://svn.open-ils.org/ILS/trunk@13206 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 3 ++- Open-ILS/src/sql/Pg/002.schema.config.sql | 11 +++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 1 + Open-ILS/src/sql/Pg/100.circ_matrix.sql | 5 +++++ Open-ILS/src/sql/Pg/110.hold_matrix.sql | 2 ++ 5 files changed, 21 insertions(+), 1 deletion(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 08b2c8dce1..17f4d2a9db 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1897,11 +1897,12 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 91221d856a..926541e07a 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -125,6 +125,17 @@ INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC'); INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW'); + +INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks'); +INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD'); +INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW'); + SELECT SETVAL('config.standing_penalty_id_seq', 100); CREATE TABLE config.xml_transform ( diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index d8eb74d1a2..9880efcddc 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -497,6 +497,7 @@ CREATE TABLE actor.usr_standing_penalty ( standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + stop_date TIMESTAMP WITH TIME ZONE, note TEXT ); COMMENT ON TABLE actor.usr_standing_penalty IS $$ diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 892866f577..33733e4f5e 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -321,6 +321,7 @@ BEGIN JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE penalty_type LOOP result.fail_part := standing_penalty.name; @@ -421,6 +422,7 @@ BEGIN FROM actor.usr_standing_penalty WHERE usr = match_user AND org_unit = max_fines.org_unit + AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 1 LOOP RETURN NEXT existing_sp_row; @@ -485,6 +487,7 @@ BEGIN FROM actor.usr_standing_penalty WHERE usr = match_user AND org_unit = max_overdue.org_unit + AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 2 LOOP RETURN NEXT existing_sp_row; @@ -543,6 +546,7 @@ BEGIN FROM actor.usr_standing_penalty WHERE usr = match_user AND org_unit = max_items_out.org_unit + AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 3 LOOP RETURN NEXT existing_sp_row; @@ -598,6 +602,7 @@ BEGIN FROM actor.usr_standing_penalty WHERE usr = match_user AND org_unit = max_fines.org_unit + AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 4 LOOP RETURN NEXT existing_sp_row; diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index d526830d5c..a966737252 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -261,6 +261,7 @@ BEGIN JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%HOLD%' LOOP result.fail_part := standing_penalty.name; @@ -276,6 +277,7 @@ BEGIN JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%CIRC%' LOOP result.fail_part := standing_penalty.name; -- 2.11.0