3 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
6 the_copy asset.copy%ROWTYPE;
9 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
10 IF NOT FOUND THEN RETURN NULL; END IF;
12 IF the_copy.status = 3 THEN -- Lost
14 ELSIF the_copy.status = 4 THEN -- Missing
16 ELSIF the_copy.status = 14 THEN -- Damaged
18 ELSIF the_copy.status = 17 THEN -- Lost and paid
19 RETURN 'LOST_AND_PAID';
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;
28 IF last_circ_stop IN (
29 'CLAIMSNEVERCHECKEDOUT',
33 RETURN last_circ_stop;
41 CREATE TYPE config.copy_alert_type_state AS ENUM (
49 'CLAIMSNEVERCHECKEDOUT'
52 CREATE TYPE config.copy_alert_type_event AS ENUM (
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,
65 invert_location bool not null default false,
70 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
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
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,
87 ack_staff bigint references actor.usr (id) on delete set null
90 CREATE VIEW asset.active_copy_alert AS
93 WHERE ack_time IS NULL;