Open-ILS/src/sql/Pg/upgrade/0657.schema.address-alert.sql

   1 -- Evergreen DB patch 0657.schema.address-alert.sql
   2 --
   3 BEGIN;
   4
   5 -- check whether patch can be applied
   6 SELECT evergreen.upgrade_deps_block_check('0657', :eg_version);
   7
   8 CREATE TABLE actor.address_alert (
   9     id              SERIAL  PRIMARY KEY,
  10     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  11     active          BOOL    NOT NULL DEFAULT TRUE,
  12     match_all       BOOL    NOT NULL DEFAULT TRUE,
  13     alert_message   TEXT    NOT NULL,
  14     street1         TEXT,
  15     street2         TEXT,
  16     city            TEXT,
  17     county          TEXT,
  18     state           TEXT,
  19     country         TEXT,
  20     post_code       TEXT,
  21     mailing_address BOOL    NOT NULL DEFAULT FALSE,
  22     billing_address BOOL    NOT NULL DEFAULT FALSE
  23 );
  24
  25 CREATE OR REPLACE FUNCTION actor.address_alert_matches (
  26         org_unit INT,
  27         street1 TEXT,
  28         street2 TEXT,
  29         city TEXT,
  30         county TEXT,
  31         state TEXT,
  32         country TEXT,
  33         post_code TEXT,
  34         mailing_address BOOL DEFAULT FALSE,
  35         billing_address BOOL DEFAULT FALSE
  36     ) RETURNS SETOF actor.address_alert AS $$
  37
  38 SELECT *
  39 FROM actor.address_alert
  40 WHERE
  41     active
  42     AND owner IN (SELECT id FROM actor.org_unit_ancestors($1))
  43     AND (
  44         (NOT mailing_address AND NOT billing_address)
  45         OR (mailing_address AND $9)
  46         OR (billing_address AND $10)
  47     )
  48     AND (
  49             (
  50                 match_all
  51                 AND COALESCE($2, '') ~* COALESCE(street1,   '.*')
  52                 AND COALESCE($3, '') ~* COALESCE(street2,   '.*')
  53                 AND COALESCE($4, '') ~* COALESCE(city,      '.*')
  54                 AND COALESCE($5, '') ~* COALESCE(county,    '.*')
  55                 AND COALESCE($6, '') ~* COALESCE(state,     '.*')
  56                 AND COALESCE($7, '') ~* COALESCE(country,   '.*')
  57                 AND COALESCE($8, '') ~* COALESCE(post_code, '.*')
  58             ) OR (
  59                 NOT match_all
  60                 AND (
  61                        $2 ~* street1
  62                     OR $3 ~* street2
  63                     OR $4 ~* city
  64                     OR $5 ~* county
  65                     OR $6 ~* state
  66                     OR $7 ~* country
  67                     OR $8 ~* post_code
  68                 )
  69             )
  70         )
  71     ORDER BY actor.org_unit_proximity(owner, $1)
  72 $$ LANGUAGE SQL;
  73
  74 COMMIT;
  75
  76 /* UNDO
  77 DROP FUNCTION actor.address_alert_matches(INT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BOOL, BOOL);
  78 DROP TABLE actor.address_alert;
  79 */