From 7e50025faf4fe32633aec07abe98e6ef36732ef2 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 6 Dec 2011 16:09:16 -0500 Subject: [PATCH] Alert addresses for patron registration : DB/IDL DB and IDL components to support configuration of alert addresses. The target use case for alert addresses is in the patron registration interface. When a patron's address matches an alert address, the staff should be notified. Alert address fields are treated as regular expressions. Signed-off-by: Bill Erickson --- Open-ILS/examples/fm_IDL.xml | 27 ++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 14 ++++++ Open-ILS/src/sql/Pg/999.functions.global.sql | 32 ++++++++++++ .../sql/Pg/upgrade/XXXX.schema.address-alert.sql | 58 ++++++++++++++++++++++ 4 files changed, 131 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index ec1cac6e7a..327f51599f 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3043,6 +3043,33 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 3f8fb920f7..0148bb60f7 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -605,4 +605,18 @@ CREATE TABLE actor.usr_saved_search ( CONSTRAINT name_once_per_user UNIQUE (owner, name) ); +CREATE TABLE actor.address_alert ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + match_all BOOL NOT NULL DEFAULT TRUE, + alert_message TEXT NOT NULL, + street1 TEXT, + street2 TEXT, + city TEXT, + county TEXT, + state TEXT, + country TEXT, + post_code TEXT +); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 4eff147ebf..1ce50e16ec 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -2035,3 +2035,35 @@ for my $key ( keys %map ) { return $default; $f$ LANGUAGE PLPERLU; + +CREATE OR REPLACE FUNCTION actor.address_alert_matches + (org_unit INT, street1 TEXT, street2 TEXT, city TEXT, county TEXT, state TEXT, country TEXT, post_code TEXT) + RETURNS SETOF actor.address_alert AS $$ +SELECT * +FROM actor.address_alert +WHERE + owner IN (SELECT id FROM actor.org_unit_ancestors($1)) AND ( + ( + match_all + AND LOWER(COALESCE($2, '')) ~ LOWER(COALESCE(street1, '.*')) + AND LOWER(COALESCE($3, '')) ~ LOWER(COALESCE(street2, '.*')) + AND LOWER(COALESCE($4, '')) ~ LOWER(COALESCE(city, '.*')) + AND LOWER(COALESCE($5, '')) ~ LOWER(COALESCE(county, '.*')) + AND LOWER(COALESCE($6, '')) ~ LOWER(COALESCE(state, '.*')) + AND LOWER(COALESCE($7, '')) ~ LOWER(COALESCE(country, '.*')) + AND LOWER(COALESCE($8, '')) ~ LOWER(COALESCE(post_code, '.*')) + ) OR ( + NOT match_all AND ( + LOWER($2) ~ LOWER(street1) + OR LOWER($3) ~ LOWER(street2) + OR LOWER($4) ~ LOWER(city) + OR LOWER($5) ~ LOWER(county) + OR LOWER($6) ~ LOWER(state) + OR LOWER($7) ~ LOWER(country) + OR LOWER($8) ~ LOWER(post_code) + ) + ) + ) + ORDER BY actor.org_unit_proximity(owner, $1) +$$ LANGUAGE SQL; + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql new file mode 100644 index 0000000000..6085b3f517 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql @@ -0,0 +1,58 @@ +-- Evergreen DB patch XXXX.schema.address-alert.sql +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE TABLE actor.address_alert ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + match_all BOOL NOT NULL DEFAULT TRUE, + alert_message TEXT NOT NULL, + street1 TEXT, + street2 TEXT, + city TEXT, + county TEXT, + state TEXT, + country TEXT, + post_code TEXT +); + +CREATE OR REPLACE FUNCTION actor.address_alert_matches + (org_unit INT, street1 TEXT, street2 TEXT, city TEXT, county TEXT, state TEXT, country TEXT, post_code TEXT) + RETURNS SETOF actor.address_alert AS $$ +SELECT * +FROM actor.address_alert +WHERE + owner IN (SELECT id FROM actor.org_unit_ancestors($1)) AND ( + ( + match_all + AND LOWER(COALESCE($2, '')) ~ LOWER(COALESCE(street1, '.*')) + AND LOWER(COALESCE($3, '')) ~ LOWER(COALESCE(street2, '.*')) + AND LOWER(COALESCE($4, '')) ~ LOWER(COALESCE(city, '.*')) + AND LOWER(COALESCE($5, '')) ~ LOWER(COALESCE(county, '.*')) + AND LOWER(COALESCE($6, '')) ~ LOWER(COALESCE(state, '.*')) + AND LOWER(COALESCE($7, '')) ~ LOWER(COALESCE(country, '.*')) + AND LOWER(COALESCE($8, '')) ~ LOWER(COALESCE(post_code, '.*')) + ) OR ( + NOT match_all AND ( + LOWER($2) ~ LOWER(street1) + OR LOWER($3) ~ LOWER(street2) + OR LOWER($4) ~ LOWER(city) + OR LOWER($5) ~ LOWER(county) + OR LOWER($6) ~ LOWER(state) + OR LOWER($7) ~ LOWER(country) + OR LOWER($8) ~ LOWER(post_code) + ) + ) + ) + ORDER BY actor.org_unit_proximity(owner, $1) +$$ LANGUAGE SQL; + +COMMIT; + +/* UNDO +DROP FUNCTION actor.address_alert_matches(INT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT); +DROP TABLE actor.address_alert; +*/ -- 2.11.0