3 -- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version);
5 DROP VIEW money.all_payments;
7 CREATE OR REPLACE VIEW money.payment_view_extended AS
12 FROM money.payment_view p
13 LEFT JOIN money.bnm_payment bnm ON bnm.id = p.id
14 LEFT JOIN money.bnm_desk_payment bnmd ON bnmd.id = p.id
15 LEFT JOIN money.account_adjustment maa ON maa.id = p.id;
17 ALTER TABLE money.aged_payment
18 ADD COLUMN accepting_usr INTEGER,
19 ADD COLUMN cash_drawer INTEGER,
20 ADD COLUMN billing BIGINT;
22 CREATE INDEX aged_payment_accepting_usr_idx ON money.aged_payment(accepting_usr);
23 CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer);
24 CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing);
26 CREATE OR REPLACE VIEW money.all_payments AS
27 SELECT * FROM money.payment_view_extended
29 SELECT * FROM money.aged_payment;
31 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
36 -- If there are any renewals for this circulation, don't archive or delete
37 -- it yet. We'll do so later, when we archive and delete the renewals.
40 FROM action.circulation
41 WHERE parent_circ = OLD.id
45 RETURN NULL; -- don't delete
48 -- Archive a copy of the old row to action.aged_circulation
50 INSERT INTO action.aged_circulation
51 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
52 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
53 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
54 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
55 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
56 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
57 auto_renewal, auto_renewal_remaining)
59 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
60 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
61 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
62 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
63 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
64 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
65 auto_renewal, auto_renewal_remaining
66 FROM action.all_circulation WHERE id = OLD.id;
68 -- Migrate billings and payments to aged tables
70 INSERT INTO money.aged_billing
71 SELECT * FROM money.billing WHERE xact = OLD.id;
73 INSERT INTO money.aged_payment
74 SELECT * FROM money.payment_view_extended WHERE xact = OLD.id;
76 DELETE FROM money.payment WHERE xact = OLD.id;
77 DELETE FROM money.billing WHERE xact = OLD.id;
81 $$ LANGUAGE 'plpgsql';