3 SELECT evergreen.upgrade_deps_block_check('1095', :eg_version);
5 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
8 the_copy asset.copy%ROWTYPE;
11 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
12 IF NOT FOUND THEN RETURN NULL; END IF;
14 IF the_copy.status = 3 THEN -- Lost
16 ELSIF the_copy.status = 4 THEN -- Missing
18 ELSIF the_copy.status = 14 THEN -- Damaged
20 ELSIF the_copy.status = 17 THEN -- Lost and paid
21 RETURN 'LOST_AND_PAID';
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;
30 IF last_circ_stop IN (
31 'CLAIMSNEVERCHECKEDOUT',
35 RETURN last_circ_stop;
43 CREATE TYPE config.copy_alert_type_state AS ENUM (
51 'CLAIMSNEVERCHECKEDOUT'
54 CREATE TYPE config.copy_alert_type_event AS ENUM (
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,
67 invert_location bool not null default false,
72 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
74 CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$
76 PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
78 RAISE foreign_key_violation USING MESSAGE = FORMAT(
79 $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
84 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
86 CREATE TABLE actor.copy_alert_suppress (
87 id serial primary key,
88 org int not null references actor.org_unit (id) on delete cascade,
89 alert_type int not null references config.copy_alert_type (id) on delete cascade
92 CREATE TABLE asset.copy_alert (
93 id bigserial primary key,
94 alert_type int not null references config.copy_alert_type (id) on delete cascade,
96 temp bool not null default false,
97 create_time timestamptz not null default now(),
98 create_staff bigint not null references actor.usr (id) on delete set null,
100 ack_time timestamptz,
101 ack_staff bigint references actor.usr (id) on delete set null
104 CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey
105 AFTER UPDATE OR INSERT ON asset.copy_alert
106 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey();
108 CREATE VIEW asset.active_copy_alert AS
110 FROM asset.copy_alert
111 WHERE ack_time IS NULL;