From 3ac16e523cad1cf05704b3e5fcee07504f7674b1 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 Signed-off-by: Thomas Berezansky --- Open-ILS/examples/fm_IDL.xml | 30 ++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 17 +++++ Open-ILS/src/sql/Pg/999.functions.global.sql | 50 ++++++++++++++ .../sql/Pg/upgrade/XXXX.schema.address-alert.sql | 79 ++++++++++++++++++++++ 4 files changed, 176 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..ae72e81251 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3043,6 +3043,36 @@ 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..3a9f841e9e 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -605,4 +605,21 @@ 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, + active BOOL NOT NULL DEFAULT TRUE, + 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, + mailing_address BOOL NOT NULL DEFAULT FALSE, + billing_address BOOL NOT NULL DEFAULT FALSE +); + 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..9885876136 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -2035,3 +2035,53 @@ 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, + mailing_address BOOL DEFAULT FALSE, + billing_address BOOL DEFAULT FALSE + ) RETURNS SETOF actor.address_alert AS $$ + +SELECT * +FROM actor.address_alert +WHERE + active + AND owner IN (SELECT id FROM actor.org_unit_ancestors($1)) + AND ( + (NOT mailing_address AND NOT billing_address) + OR (mailing_address AND $9) + OR (billing_address AND $10) + ) + AND ( + ( + match_all + AND COALESCE($2, '') ~* COALESCE(street1, '.*') + AND COALESCE($3, '') ~* COALESCE(street2, '.*') + AND COALESCE($4, '') ~* COALESCE(city, '.*') + AND COALESCE($5, '') ~* COALESCE(county, '.*') + AND COALESCE($6, '') ~* COALESCE(state, '.*') + AND COALESCE($7, '') ~* COALESCE(country, '.*') + AND COALESCE($8, '') ~* COALESCE(post_code, '.*') + ) OR ( + NOT match_all + AND ( + $2 ~* street1 + OR $3 ~* street2 + OR $4 ~* city + OR $5 ~* county + OR $6 ~* state + OR $7 ~* country + OR $8 ~* 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..aed3dced53 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql @@ -0,0 +1,79 @@ +-- 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, + active BOOL NOT NULL DEFAULT TRUE, + 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, + mailing_address BOOL NOT NULL DEFAULT FALSE, + billing_address BOOL NOT NULL DEFAULT FALSE +); + +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, + mailing_address BOOL DEFAULT FALSE, + billing_address BOOL DEFAULT FALSE + ) RETURNS SETOF actor.address_alert AS $$ + +SELECT * +FROM actor.address_alert +WHERE + active + AND owner IN (SELECT id FROM actor.org_unit_ancestors($1)) + AND ( + (NOT mailing_address AND NOT billing_address) + OR (mailing_address AND $9) + OR (billing_address AND $10) + ) + AND ( + ( + match_all + AND COALESCE($2, '') ~* COALESCE(street1, '.*') + AND COALESCE($3, '') ~* COALESCE(street2, '.*') + AND COALESCE($4, '') ~* COALESCE(city, '.*') + AND COALESCE($5, '') ~* COALESCE(county, '.*') + AND COALESCE($6, '') ~* COALESCE(state, '.*') + AND COALESCE($7, '') ~* COALESCE(country, '.*') + AND COALESCE($8, '') ~* COALESCE(post_code, '.*') + ) OR ( + NOT match_all + AND ( + $2 ~* street1 + OR $3 ~* street2 + OR $4 ~* city + OR $5 ~* county + OR $6 ~* state + OR $7 ~* country + OR $8 ~* 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, BOOL, BOOL); +DROP TABLE actor.address_alert; +*/ -- 2.11.0