Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql

   1 BEGIN;
   2
   3 -- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version);
   4
   5 DROP VIEW money.all_payments;
   6
   7 CREATE OR REPLACE VIEW money.payment_view_extended AS
   8     SELECT p.*,
   9         bnm.accepting_usr,
  10         bnmd.cash_drawer,
  11         maa.billing
  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;
  16
  17 ALTER TABLE money.aged_payment
  18     ADD COLUMN accepting_usr INTEGER,
  19     ADD COLUMN cash_drawer INTEGER,
  20     ADD COLUMN billing BIGINT;
  21
  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);
  25
  26 CREATE OR REPLACE VIEW money.all_payments AS
  27     SELECT * FROM money.payment_view_extended
  28     UNION ALL
  29     SELECT * FROM money.aged_payment;
  30
  31 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
  32 DECLARE
  33 found char := 'N';
  34 BEGIN
  35
  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.
  38
  39     SELECT 'Y' INTO found
  40     FROM action.circulation
  41     WHERE parent_circ = OLD.id
  42     LIMIT 1;
  43
  44     IF found = 'Y' THEN
  45         RETURN NULL;  -- don't delete
  46         END IF;
  47
  48     -- Archive a copy of the old row to action.aged_circulation
  49
  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)
  58       SELECT
  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;
  67
  68     -- Migrate billings and payments to aged tables
  69
  70     INSERT INTO money.aged_billing
  71         SELECT * FROM money.billing WHERE xact = OLD.id;
  72
  73     INSERT INTO money.aged_payment
  74         SELECT * FROM money.payment_view_extended WHERE xact = OLD.id;
  75
  76     DELETE FROM money.payment WHERE xact = OLD.id;
  77     DELETE FROM money.billing WHERE xact = OLD.id;
  78
  79     RETURN OLD;
  80 END;
  81 $$ LANGUAGE 'plpgsql';
  82
  83
  84 COMMIT;