Stamping upgrade scripts for new copy alerts
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 1095.schema.copy_alerts.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('1095', :eg_version);
4
5 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
6 DECLARE
7     last_circ_stop      TEXT;
8     the_copy        asset.copy%ROWTYPE;
9 BEGIN
10
11     SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
12     IF NOT FOUND THEN RETURN NULL; END IF;
13
14     IF the_copy.status = 3 THEN -- Lost
15         RETURN 'LOST';
16     ELSIF the_copy.status = 4 THEN -- Missing
17         RETURN 'MISSING';
18     ELSIF the_copy.status = 14 THEN -- Damaged
19         RETURN 'DAMAGED';
20     ELSIF the_copy.status = 17 THEN -- Lost and paid
21         RETURN 'LOST_AND_PAID';
22     END IF;
23
24     SELECT stop_fines INTO last_circ_stop
25       FROM  action.circulation
26       WHERE target_copy = cid
27       ORDER BY xact_start DESC LIMIT 1;
28
29     IF FOUND THEN
30         IF last_circ_stop IN (
31             'CLAIMSNEVERCHECKEDOUT',
32             'CLAIMSRETURNED',
33             'LONGOVERDUE'
34         ) THEN
35             RETURN last_circ_stop;
36         END IF;
37     END IF;
38
39     RETURN 'NORMAL';
40 END;
41 $$ LANGUAGE PLPGSQL;
42
43 CREATE TYPE config.copy_alert_type_state AS ENUM (
44     'NORMAL',
45     'LOST',
46     'LOST_AND_PAID',
47     'MISSING',
48     'DAMAGED',
49     'CLAIMSRETURNED',
50     'LONGOVERDUE',
51     'CLAIMSNEVERCHECKEDOUT'
52 );
53
54 CREATE TYPE config.copy_alert_type_event AS ENUM (
55     'CHECKIN',
56     'CHECKOUT'
57 );
58
59 CREATE TABLE config.copy_alert_type (
60     id          serial  primary key, -- reserve 1-100 for system
61     scope_org   int not null references actor.org_unit (id) on delete cascade,
62     active      bool    not null default true,
63     name        text    not null unique,
64     state       config.copy_alert_type_state,
65     event       config.copy_alert_type_event,
66     in_renew    bool,
67     invert_location bool    not null default false,
68     at_circ     bool,
69     at_owning   bool,
70     next_status int[]
71 );
72 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
73
74 CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$
75 BEGIN
76         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
77         IF NOT FOUND THEN
78                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
79                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
80                 );
81         END IF;
82         RETURN NEW;
83 END;
84 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
85
86 CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey
87         AFTER UPDATE OR INSERT ON asset.copy_alert
88         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey();
89
90 CREATE TABLE actor.copy_alert_suppress (
91     id          serial primary key,
92     org         int not null references actor.org_unit (id) on delete cascade,
93     alert_type  int not null references config.copy_alert_type (id) on delete cascade
94 );
95
96 CREATE TABLE asset.copy_alert (
97     id      bigserial   primary key,
98     alert_type  int     not null references config.copy_alert_type (id) on delete cascade,
99     copy        bigint  not null,
100     temp        bool    not null default false,
101     create_time timestamptz not null default now(),
102     create_staff    bigint  not null references actor.usr (id) on delete set null,
103     note        text,
104     ack_time    timestamptz,
105     ack_staff   bigint references actor.usr (id) on delete set null
106 );
107
108 CREATE VIEW asset.active_copy_alert AS
109     SELECT  *
110       FROM  asset.copy_alert
111       WHERE ack_time IS NULL;
112
113 COMMIT;
114