From 58bda816d9f3ea761c64a03db3b831f659529a9b Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 25 Aug 2016 10:18:51 -0400 Subject: [PATCH] JBAS-1419 Aged circ API/SQL cross-port Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/aged-circs-api.sql | 143 +++++++++++++++++++++ KCLS/sql/schema/deploy/patron-co-history-table.sql | 5 + KCLS/sql/schema/revert/aged-circs-api.sql | 56 ++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/aged-circs-api.sql | 7 + 5 files changed, 212 insertions(+) create mode 100644 KCLS/sql/schema/deploy/aged-circs-api.sql create mode 100644 KCLS/sql/schema/revert/aged-circs-api.sql create mode 100644 KCLS/sql/schema/verify/aged-circs-api.sql diff --git a/KCLS/sql/schema/deploy/aged-circs-api.sql b/KCLS/sql/schema/deploy/aged-circs-api.sql new file mode 100644 index 0000000000..8492367480 --- /dev/null +++ b/KCLS/sql/schema/deploy/aged-circs-api.sql @@ -0,0 +1,143 @@ +-- Deploy kcls-evergreen:aged-circs-api to pg +-- requires: patron-co-history-table + +BEGIN; + +\set eg_version '''2.7.8''' +SELECT evergreen.upgrade_deps_block_check('0998', :eg_version); + +DROP VIEW IF EXISTS action.all_circulation; +CREATE VIEW action.all_circulation AS + SELECT aged_circulation.id, aged_circulation.usr_post_code, + aged_circulation.usr_home_ou, aged_circulation.usr_profile, + aged_circulation.usr_birth_year, aged_circulation.copy_call_number, + aged_circulation.copy_location, aged_circulation.copy_owning_lib, + aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record, + aged_circulation.xact_start, aged_circulation.xact_finish, + aged_circulation.target_copy, aged_circulation.circ_lib, + aged_circulation.circ_staff, aged_circulation.checkin_staff, + aged_circulation.checkin_lib, aged_circulation.renewal_remaining, + aged_circulation.grace_period, aged_circulation.due_date, + aged_circulation.stop_fines_time, aged_circulation.checkin_time, + aged_circulation.create_time, aged_circulation.duration, + aged_circulation.fine_interval, aged_circulation.recurring_fine, + aged_circulation.max_fine, aged_circulation.phone_renewal, + aged_circulation.desk_renewal, aged_circulation.opac_renewal, + aged_circulation.duration_rule, + aged_circulation.recurring_fine_rule, + aged_circulation.max_fine_rule, aged_circulation.stop_fines, + aged_circulation.workstation, aged_circulation.checkin_workstation, + aged_circulation.checkin_scan_time, aged_circulation.parent_circ, + NULL AS usr + FROM action.aged_circulation +UNION ALL + SELECT DISTINCT circ.id, + COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + cp.call_number AS copy_call_number, circ.copy_location, + cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, + circ.target_copy, circ.circ_lib, circ.circ_staff, + circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, + circ.grace_period, circ.due_date, circ.stop_fines_time, + circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, + circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, + circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, + circ.stop_fines, circ.workstation, circ.checkin_workstation, + circ.checkin_scan_time, circ.parent_circ, circ.usr + FROM action.circulation circ + JOIN asset.copy cp ON circ.target_copy = cp.id +JOIN asset.call_number cn ON cp.call_number = cn.id +JOIN actor.usr p ON circ.usr = p.id +LEFT JOIN actor.usr_address a ON p.mailing_address = a.id +LEFT JOIN actor.usr_address b ON p.billing_address = b.id; + + +CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) + RETURNS SETOF action.all_circulation AS $$ +DECLARE + tmp_circ action.all_circulation%ROWTYPE; + circ_0 action.all_circulation%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id; + + IF tmp_circ IS NULL THEN + RETURN NEXT tmp_circ; + END IF; + circ_0 := tmp_circ; + + -- find the front of the chain + WHILE TRUE LOOP + SELECT INTO tmp_circ * FROM action.all_circulation + WHERE id = tmp_circ.parent_circ; + IF tmp_circ IS NULL THEN + EXIT; + END IF; + circ_0 := tmp_circ; + END LOOP; + + -- now send the circs to the caller, oldest to newest + tmp_circ := circ_0; + WHILE TRUE LOOP + IF tmp_circ IS NULL THEN + EXIT; + END IF; + RETURN NEXT tmp_circ; + SELECT INTO tmp_circ * FROM action.all_circulation + WHERE parent_circ = tmp_circ.id; + END LOOP; + +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain + (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$ + +DECLARE + + -- first circ in the chain + circ_0 action.all_circulation%ROWTYPE; + + -- last circ in the chain + circ_n action.all_circulation%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.all_circulation%ROWTYPE; + +BEGIN + + chain.num_circs := 0; + FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP + + IF chain.num_circs = 0 THEN + circ_0 := tmp_circ; + END IF; + + chain.num_circs := chain.num_circs + 1; + circ_n := tmp_circ; + END LOOP; + + chain.start_time := circ_0.xact_start; + chain.last_stop_fines := circ_n.stop_fines; + chain.last_stop_fines_time := circ_n.stop_fines_time; + chain.last_checkin_time := circ_n.checkin_time; + chain.last_checkin_scan_time := circ_n.checkin_scan_time; + SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation; + SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation; + + IF chain.num_circs > 1 THEN + chain.last_renewal_time := circ_n.xact_start; + SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation; + END IF; + + RETURN chain; + +END; +$$ LANGUAGE 'plpgsql'; + + +COMMIT; diff --git a/KCLS/sql/schema/deploy/patron-co-history-table.sql b/KCLS/sql/schema/deploy/patron-co-history-table.sql index 0d88c87ab5..c3840e3794 100644 --- a/KCLS/sql/schema/deploy/patron-co-history-table.sql +++ b/KCLS/sql/schema/deploy/patron-co-history-table.sql @@ -3,6 +3,11 @@ BEGIN; +\set eg_version '''2.7.8''' +SELECT evergreen.upgrade_deps_block_check('0960', :eg_version); + +DROP FUNCTION IF EXISTS action.all_circ_chain (INTEGER); + CREATE TABLE action.usr_circ_history ( id BIGSERIAL PRIMARY KEY, usr INTEGER NOT NULL REFERENCES actor.usr(id) diff --git a/KCLS/sql/schema/revert/aged-circs-api.sql b/KCLS/sql/schema/revert/aged-circs-api.sql new file mode 100644 index 0000000000..76945116ac --- /dev/null +++ b/KCLS/sql/schema/revert/aged-circs-api.sql @@ -0,0 +1,56 @@ +-- Revert kcls-evergreen:aged-circs-api from pg + +BEGIN; + +DROP FUNCTION IF EXISTS action.summarize_all_circ_chain (INTEGER); +DROP FUNCTION IF EXISTS action.all_circ_chain (INTEGER); + +DROP VIEW IF EXISTS action.all_circulation; +CREATE VIEW action.all_circulation AS + SELECT aged_circulation.id, aged_circulation.usr_post_code, + aged_circulation.usr_home_ou, aged_circulation.usr_profile, + aged_circulation.usr_birth_year, aged_circulation.copy_call_number, + aged_circulation.copy_location, aged_circulation.copy_owning_lib, + aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record, + aged_circulation.xact_start, aged_circulation.xact_finish, + aged_circulation.target_copy, aged_circulation.circ_lib, + aged_circulation.circ_staff, aged_circulation.checkin_staff, + aged_circulation.checkin_lib, aged_circulation.renewal_remaining, + aged_circulation.grace_period, aged_circulation.due_date, + aged_circulation.stop_fines_time, aged_circulation.checkin_time, + aged_circulation.create_time, aged_circulation.duration, + aged_circulation.fine_interval, aged_circulation.recurring_fine, + aged_circulation.max_fine, aged_circulation.phone_renewal, + aged_circulation.desk_renewal, aged_circulation.opac_renewal, + aged_circulation.duration_rule, + aged_circulation.recurring_fine_rule, + aged_circulation.max_fine_rule, aged_circulation.stop_fines, + aged_circulation.workstation, aged_circulation.checkin_workstation, + aged_circulation.checkin_scan_time, aged_circulation.parent_circ + FROM action.aged_circulation +UNION ALL + SELECT DISTINCT circ.id, + COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + cp.call_number AS copy_call_number, circ.copy_location, + cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, + circ.target_copy, circ.circ_lib, circ.circ_staff, + circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, + circ.grace_period, circ.due_date, circ.stop_fines_time, + circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, + circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, + circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, + circ.stop_fines, circ.workstation, circ.checkin_workstation, + circ.checkin_scan_time, circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON circ.target_copy = cp.id + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN actor.usr p ON circ.usr = p.id + LEFT JOIN actor.usr_address a ON p.mailing_address = a.id + LEFT JOIN actor.usr_address b ON p.billing_address = b.id; + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index ec4dfa6ebb..9ac4047aad 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -46,3 +46,4 @@ patron-co-history-stage-1 [sip-act-type-freegalsip] 2016-09-01T19:36:46Z Bill Er 2.7-to-2.9-upgrade-part-2 [2.7-to-2.9-upgrade] 2016-12-12T15:19:07Z Bill Erickson # 2.7 to 2.9 Upgrade Secondary Updates revert-custom-hold-counts [backstage-exports-continued] 2017-02-06T21:33:32Z Bill Erickson # Remove custom hold count materialized data patron-co-history-table [sip-activity-types] 2016-05-13T18:02:48Z Bill Erickson # Back-porting LP#1527342: Patron reading history +aged-circs-api [patron-co-history-table] 2016-08-25T14:14:36Z Bill Erickson # Aged circs API access diff --git a/KCLS/sql/schema/verify/aged-circs-api.sql b/KCLS/sql/schema/verify/aged-circs-api.sql new file mode 100644 index 0000000000..a065ff079a --- /dev/null +++ b/KCLS/sql/schema/verify/aged-circs-api.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:aged-circs-api on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0