From 7095333ae2087f5ca1cff0a73c1189684eb6dc16 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 8 Feb 2018 10:13:39 -0500 Subject: [PATCH] JBAS-1306 SQL backport for all circs slim DB view Use the slim view in the refundable xact summary view. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/all-circs-slim-backport.sql | 225 +++++++++++++++++++++ KCLS/sql/schema/deploy/lost-paid-receipts.sql | 2 +- KCLS/sql/schema/revert/all-circs-slim-backport.sql | 95 +++++++++ KCLS/sql/schema/sqitch.plan | 3 +- KCLS/sql/schema/verify/all-circs-slim-backport.sql | 7 + 5 files changed, 330 insertions(+), 2 deletions(-) create mode 100644 KCLS/sql/schema/deploy/all-circs-slim-backport.sql create mode 100644 KCLS/sql/schema/revert/all-circs-slim-backport.sql create mode 100644 KCLS/sql/schema/verify/all-circs-slim-backport.sql diff --git a/KCLS/sql/schema/deploy/all-circs-slim-backport.sql b/KCLS/sql/schema/deploy/all-circs-slim-backport.sql new file mode 100644 index 0000000000..b0646513e9 --- /dev/null +++ b/KCLS/sql/schema/deploy/all-circs-slim-backport.sql @@ -0,0 +1,225 @@ +-- Deploy kcls-evergreen:all-circs-slim-backport to pg +-- requires: at-purge-interval-data + +BEGIN; + +CREATE OR REPLACE VIEW action.all_circulation_slim AS + SELECT + id, + usr, + xact_start, + xact_finish, + unrecovered, + target_copy, + circ_lib, + circ_staff, + checkin_staff, + checkin_lib, + renewal_remaining, + grace_period, + due_date, + stop_fines_time, + checkin_time, + create_time, + duration, + fine_interval, + recurring_fine, + max_fine, + phone_renewal, + desk_renewal, + opac_renewal, + duration_rule, + recurring_fine_rule, + max_fine_rule, + stop_fines, + workstation, + checkin_workstation, + copy_location, + checkin_scan_time, + parent_circ + FROM action.circulation +UNION ALL + SELECT + id, + NULL AS usr, + xact_start, + xact_finish, + unrecovered, + target_copy, + circ_lib, + circ_staff, + checkin_staff, + checkin_lib, + renewal_remaining, + grace_period, + due_date, + stop_fines_time, + checkin_time, + create_time, + duration, + fine_interval, + recurring_fine, + max_fine, + phone_renewal, + desk_renewal, + opac_renewal, + duration_rule, + recurring_fine_rule, + max_fine_rule, + stop_fines, + workstation, + checkin_workstation, + copy_location, + checkin_scan_time, + parent_circ + FROM action.aged_circulation +; + +DROP FUNCTION action.summarize_all_circ_chain(INTEGER); +DROP FUNCTION action.all_circ_chain(INTEGER); + +CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) + RETURNS SETOF action.all_circulation_slim AS $$ +DECLARE + tmp_circ action.all_circulation_slim%ROWTYPE; + circ_0 action.all_circulation_slim%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.all_circulation_slim 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_slim + 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_slim + 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_slim%ROWTYPE; + + -- last circ in the chain + circ_n action.all_circulation_slim%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.all_circulation_slim%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'; + +CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT bib, + SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC + FROM (SELECT cn.record AS bib, + cp.id, + EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age, + SUM( -- time copy spent circulating + EXTRACT( + EPOCH FROM + AGE( + COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()), + circ.xact_start + ) + ) + )::NUMERIC AS circ_time + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + LEFT JOIN action.all_circulation_slim circ ON ( + circ.target_copy = cp.id + AND stop_fines NOT IN ( + 'LOST', + 'LONGOVERDUE', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) + AND NOT ( + checkin_time IS NULL AND + stop_fines = 'MAXFINES' + ) + ) + WHERE cn.owning_lib = ANY (badge.orgs) + AND cp.active_date IS NOT NULL + -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted + AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL) + GROUP BY 1,2,3 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + + +COMMIT; diff --git a/KCLS/sql/schema/deploy/lost-paid-receipts.sql b/KCLS/sql/schema/deploy/lost-paid-receipts.sql index 96df20ae17..a70181f920 100644 --- a/KCLS/sql/schema/deploy/lost-paid-receipts.sql +++ b/KCLS/sql/schema/deploy/lost-paid-receipts.sql @@ -91,7 +91,7 @@ CREATE OR REPLACE VIEW money.refundable_xact_summary AS total_refunded.amount::NUMERIC(8,2) AS total_refunded, refundable_payment_count.count AS num_refundable_payments FROM money.refundable_xact xact - JOIN action.all_circulation circ ON (circ.id = xact.xact) + JOIN action.all_circulation_slim circ ON (circ.id = xact.xact) JOIN asset.copy acp ON (acp.id = circ.target_copy) JOIN asset.call_number acn ON (acn.id = acp.call_number) JOIN reporter.materialized_simple_record rsr ON (rsr.id = acn.record) diff --git a/KCLS/sql/schema/revert/all-circs-slim-backport.sql b/KCLS/sql/schema/revert/all-circs-slim-backport.sql new file mode 100644 index 0000000000..e6241f9a57 --- /dev/null +++ b/KCLS/sql/schema/revert/all-circs-slim-backport.sql @@ -0,0 +1,95 @@ +-- Revert kcls-evergreen:all-circs-slim-backport from pg + +BEGIN; + +-- XXX Add DDLs here. + +DROP FUNCTION action.summarize_all_circ_chain(INTEGER); +DROP FUNCTION action.all_circ_chain(INTEGER); + +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/sqitch.plan b/KCLS/sql/schema/sqitch.plan index aa87fe5f49..1755823b92 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -73,7 +73,8 @@ ecard-data [2.10-to-2.12-upgrade] 2018-01-03T21:55:03Z Bill Erickson,,, # Remove gender field/data ecard-notice-validator [remove-gender] 2018-07-26T14:33:57Z Bill Erickson,,, # eCard UMS notice validator aged-billings-payments [ecard-notice-validator] 2018-09-24T18:00:57Z Bill Erickson,,, # Aged money/billing schema and data -lost-paid-receipts [2.9-to-2.10-upgrade-reingest] 2017-07-03T20:10:59Z Bill Erickson,,, # Lost/Paid tracking and receipts +all-circs-slim-backport [aged-billings-payments] 2018-02-08T15:09:53Z Bill Erickson,,, # Backport slim all circs view +lost-paid-receipts [all-circs-slim-backport] 2017-07-03T20:10:59Z Bill Erickson,,, # Lost/Paid tracking and receipts lost-paid-receipts-data [lost-paid-receipts] 2017-08-02T15:28:08Z Bill Erickson,,, # Lost/Paid permissions and receipts stock-browse-schema [ecard-notice-validator] 2018-08-31T15:22:58Z Bill Erickson,,, # Recover stock browse data tables, etc. stock-browse-headings-report [stock-browse-schema] 2018-10-04T15:56:18Z Bill Erickson,,, # New heading report updates for stock browse diff --git a/KCLS/sql/schema/verify/all-circs-slim-backport.sql b/KCLS/sql/schema/verify/all-circs-slim-backport.sql new file mode 100644 index 0000000000..86a046dff6 --- /dev/null +++ b/KCLS/sql/schema/verify/all-circs-slim-backport.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:all-circs-slim-backport on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0