From 5d26efd374f175d5c959166282a225ad6e06c38c Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 20 Oct 2015 10:10:28 -0400 Subject: [PATCH] Initial schema for copy alerts Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 89 +++++++++++++++++++++ .../src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql | 93 ++++++++++++++++++++++ 2 files changed, 182 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 283b476bbd..494b575007 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -905,5 +905,94 @@ BEGIN END; $F$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION asset.copy_state  (cid BIGINT) RETURNS TEXT AS $$ +DECLARE + last_circ_stop TEXT; + the_copy asset.copy%ROWTYPE; +BEGIN + + SELECT * INTO the_copy FROM asset.copy WHERE id = cid; + RETURN NULL WHEN NOT FOUND; + + IF the_copy.status = 3 THEN -- Lost + RETURN 'LOST'; + ELIF the_copy.status = 4 THEN -- Missing + RETURN 'MISSING'; + ELIF the_copy.status = 14 THEN -- Damaged + RETURN 'DAMAGED'; + END; + + SELECT stop_fines INTO last_circ_stop +  FROM action.circulation +  WHERE target_copy = cid +  ORDER BY xact_start DESC LIMIT 1; + + IF FOUND THEN + IF last_circ_stop IN ( + 'CLAIMSNEVERCHECKEDOUT', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) THEN + RETURN last_circ_stop; + END; + END; + + RETURN 'NORMAL'; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE config.copy_alert_type_state AS ENUM ( + 'NORMAL', + 'LOST', + 'MISSING', + 'DAMAGED', + 'CLAIMSRETURNED', + 'LONGOVERDUE', + 'CLAIMSNEVERCHECKEDOUT' +); + +CREATE TYPE config.copy_alert_type_event AS ENUM ( + 'CHECKIN', + 'CHECKOUT' +); + +CREATE TABLE config.copy_alert_type ( + id serial primary key, -- reserve 1-100 for system + scope_org int not null references actor.org_unit (id) on delete cascade, + active bool not null default true, + name text not null unique, + state config.copy_alert_type_state, + event config.copy_alert_type_event, + in_renew bool, + invert_location bool not null default false, + at_circ bool, + at_owing bool, + next_status int[] +); +SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100); + +CREATE TABLE actor.copy_alert_suppress ( + id serial primary key, + org int not null references actor.org_unit (id) on delete cascade, + alert_type int not null references config.copy_alert_type (id) on delete cascade +); + +CREATE TABLE asset.copy_alert ( + id bigserial primary key, + alert_type int not null references config.copy_alert_type (id) on delete cascade, + copy bigint not null references asset.copy (id) on delete cascade, + temp bool not null default false, + create_time timestamptz not null default now(), + create_staff bigint not null references actor.usr (id) on delete set null, + note text, + ack_time timestamptz, + ack_staff bigint references actor.usr (id) on delete set null +); + +CREATE VIEW asset.active_copy_alert AS + SELECT * + FROM asset.copy_alert + WHERE ack_time IS NULL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql new file mode 100644 index 0000000000..bf2cce6da5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql @@ -0,0 +1,93 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION asset.copy_state  (cid BIGINT) RETURNS TEXT AS $$ +DECLARE + last_circ_stop TEXT; + the_copy asset.copy%ROWTYPE; +BEGIN + + SELECT * INTO the_copy FROM asset.copy WHERE id = cid; + RETURN NULL WHEN NOT FOUND; + + IF the_copy.status = 3 THEN -- Lost + RETURN 'LOST'; + ELIF the_copy.status = 4 THEN -- Missing + RETURN 'MISSING'; + ELIF the_copy.status = 14 THEN -- Damaged + RETURN 'DAMAGED'; + END; + + SELECT stop_fines INTO last_circ_stop +  FROM action.circulation +  WHERE target_copy = cid +  ORDER BY xact_start DESC LIMIT 1; + + IF FOUND THEN + IF last_circ_stop IN ( + 'CLAIMSNEVERCHECKEDOUT', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) THEN + RETURN last_circ_stop; + END; + END; + + RETURN 'NORMAL'; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE config.copy_alert_type_state AS ENUM ( + 'NORMAL', + 'LOST', + 'MISSING', + 'DAMAGED', + 'CLAIMSRETURNED', + 'LONGOVERDUE', + 'CLAIMSNEVERCHECKEDOUT' +); + +CREATE TYPE config.copy_alert_type_event AS ENUM ( + 'CHECKIN', + 'CHECKOUT' +); + +CREATE TABLE config.copy_alert_type ( + id serial primary key, -- reserve 1-100 for system + scope_org int not null references actor.org_unit (id) on delete cascade, + active bool not null default true, + name text not null unique, + state config.copy_alert_type_state, + event config.copy_alert_type_event, + in_renew bool, + invert_location bool not null default false, + at_circ bool, + at_owing bool, + next_status int[] +); +SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100); + +CREATE TABLE actor.copy_alert_suppress ( + id serial primary key, + org int not null references actor.org_unit (id) on delete cascade, + alert_type int not null references config.copy_alert_type (id) on delete cascade +); + +CREATE TABLE asset.copy_alert ( + id bigserial primary key, + alert_type int not null references config.copy_alert_type (id) on delete cascade, + copy bigint not null references asset.copy (id) on delete cascade, + temp bool not null default false, + create_time timestamptz not null default now(), + create_staff bigint not null references actor.usr (id) on delete set null, + note text, + ack_time timestamptz, + ack_staff bigint references actor.usr (id) on delete set null +); + +CREATE VIEW asset.active_copy_alert AS + SELECT * + FROM asset.copy_alert + WHERE ack_time IS NULL; + +COMMIT; + -- 2.11.0