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