From 138b8ed52fe311bfd0ddffc4912ea2201f96a445 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 7 Feb 2020 15:19:15 -0500 Subject: [PATCH] LP1858448 Additional aged_payment fields Adds accepting_usr, cash_drawer, and billing columns to the money.aged_payment table. Modifies the existing "xact" column for aged payments and billings in the IDL to link to "mbt" instead of "acirc", since not all payments/billings are linked to aged circs. Signed-off-by: Bill Erickson Signed-off-by: John Amundson Signed-off-by: Jason Stephenson --- Open-ILS/examples/fm_IDL.xml | 22 +++-- Open-ILS/src/sql/Pg/080.schema.money.sql | 22 ++++- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +- .../upgrade/XXXX.schema.aged-money-fields.sql | 84 +++++++++++++++++++ 4 files changed, 121 insertions(+), 9 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 7622efd0db..3cc362ab73 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8114,11 +8114,17 @@ SELECT usr, - + + + + - + + + + @@ -8140,11 +8146,15 @@ SELECT usr, + + + - + + + + @@ -8444,7 +8454,7 @@ SELECT usr, - + diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 88a724bdf0..631c7a5e0e 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -700,15 +700,33 @@ CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer) LEFT JOIN money.payment_view t ON (p.id = t.id); +-- serves as the basis for the aged payments data. +CREATE OR REPLACE VIEW money.payment_view_extended AS + SELECT p.*, + bnm.accepting_usr, + bnmd.cash_drawer, + maa.billing + FROM money.payment_view p + LEFT JOIN money.bnm_payment bnm ON bnm.id = p.id + LEFT JOIN money.bnm_desk_payment bnmd ON bnmd.id = p.id + LEFT JOIN money.account_adjustment maa ON maa.id = p.id; -- Create 'aged' clones of billing and payment_view tables CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES); -ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL; +ALTER TABLE money.aged_payment + ADD COLUMN payment_type TEXT NOT NULL, + ADD COLUMN accepting_usr INTEGER, + ADD COLUMN cash_drawer INTEGER, + ADD COLUMN billing BIGINT; + +CREATE INDEX aged_payment_accepting_usr_idx ON money.aged_payment(accepting_usr); +CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer); +CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing); CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); CREATE OR REPLACE VIEW money.all_payments AS - SELECT * FROM money.payment_view + SELECT * FROM money.payment_view_extended UNION ALL SELECT * FROM money.aged_payment; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 3fb3b4a782..ae1fa9adfd 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -358,7 +358,7 @@ BEGIN SELECT * FROM money.billing WHERE xact = OLD.id; INSERT INTO money.aged_payment - SELECT * FROM money.payment_view WHERE xact = OLD.id; + SELECT * FROM money.payment_view_extended WHERE xact = OLD.id; DELETE FROM money.payment WHERE xact = OLD.id; DELETE FROM money.billing WHERE xact = OLD.id; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql new file mode 100644 index 0000000000..456ea11b11 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql @@ -0,0 +1,84 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); + +DROP VIEW money.all_payments; + +CREATE OR REPLACE VIEW money.payment_view_extended AS + SELECT p.*, + bnm.accepting_usr, + bnmd.cash_drawer, + maa.billing + FROM money.payment_view p + LEFT JOIN money.bnm_payment bnm ON bnm.id = p.id + LEFT JOIN money.bnm_desk_payment bnmd ON bnmd.id = p.id + LEFT JOIN money.account_adjustment maa ON maa.id = p.id; + +ALTER TABLE money.aged_payment + ADD COLUMN accepting_usr INTEGER, + ADD COLUMN cash_drawer INTEGER, + ADD COLUMN billing BIGINT; + +CREATE INDEX aged_payment_accepting_usr_idx ON money.aged_payment(accepting_usr); +CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer); +CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing); + +CREATE OR REPLACE VIEW money.all_payments AS + SELECT * FROM money.payment_view_extended + UNION ALL + SELECT * FROM money.aged_payment; + +CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ +DECLARE +found char := 'N'; +BEGIN + + -- If there are any renewals for this circulation, don't archive or delete + -- it yet. We'll do so later, when we archive and delete the renewals. + + SELECT 'Y' INTO found + FROM action.circulation + WHERE parent_circ = OLD.id + LIMIT 1; + + IF found = 'Y' THEN + RETURN NULL; -- don't delete + END IF; + + -- Archive a copy of the old row to action.aged_circulation + + INSERT INTO action.aged_circulation + (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, 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, checkin_scan_time, parent_circ, + auto_renewal, auto_renewal_remaining) + SELECT + id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, 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, checkin_scan_time, parent_circ, + auto_renewal, auto_renewal_remaining + FROM action.all_circulation WHERE id = OLD.id; + + -- Migrate billings and payments to aged tables + + INSERT INTO money.aged_billing + SELECT * FROM money.billing WHERE xact = OLD.id; + + INSERT INTO money.aged_payment + SELECT * FROM money.payment_view_extended WHERE xact = OLD.id; + + DELETE FROM money.payment WHERE xact = OLD.id; + DELETE FROM money.billing WHERE xact = OLD.id; + + RETURN OLD; +END; +$$ LANGUAGE 'plpgsql'; + + +COMMIT; -- 2.43.2