LP#1676608: copy alert and suppression matrix
[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 TABLE actor.copy_alert_suppress (
73     id          serial primary key,
74     org         int not null references actor.org_unit (id) on delete cascade,
75     alert_type  int not null references config.copy_alert_type (id) on delete cascade
76 );
77
78 CREATE TABLE asset.copy_alert (
79     id      bigserial   primary key,
80     alert_type  int     not null references config.copy_alert_type (id) on delete cascade,
81     copy        bigint  not null references asset.copy (id) on delete cascade,
82     temp        bool    not null default false,
83     create_time timestamptz not null default now(),
84     create_staff    bigint  not null references actor.usr (id) on delete set null,
85     note        text,
86     ack_time    timestamptz,
87     ack_staff   bigint references actor.usr (id) on delete set null
88 );
89
90 CREATE VIEW asset.active_copy_alert AS
91     SELECT  *
92       FROM  asset.copy_alert
93       WHERE ack_time IS NULL;
94
95 COMMIT;
96