Open-ILS/src/sql/Pg/upgrade/0998.schema.aged-circ-chains.sql

   1
   2 BEGIN;
   3
   4 SELECT evergreen.upgrade_deps_block_check('0998', :eg_version);
   5
   6 DROP VIEW IF EXISTS action.all_circulation;
   7 CREATE VIEW action.all_circulation AS
   8      SELECT aged_circulation.id, aged_circulation.usr_post_code,
   9         aged_circulation.usr_home_ou, aged_circulation.usr_profile,
  10         aged_circulation.usr_birth_year, aged_circulation.copy_call_number,
  11         aged_circulation.copy_location, aged_circulation.copy_owning_lib,
  12         aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record,
  13         aged_circulation.xact_start, aged_circulation.xact_finish,
  14         aged_circulation.target_copy, aged_circulation.circ_lib,
  15         aged_circulation.circ_staff, aged_circulation.checkin_staff,
  16         aged_circulation.checkin_lib, aged_circulation.renewal_remaining,
  17         aged_circulation.grace_period, aged_circulation.due_date,
  18         aged_circulation.stop_fines_time, aged_circulation.checkin_time,
  19         aged_circulation.create_time, aged_circulation.duration,
  20         aged_circulation.fine_interval, aged_circulation.recurring_fine,
  21         aged_circulation.max_fine, aged_circulation.phone_renewal,
  22         aged_circulation.desk_renewal, aged_circulation.opac_renewal,
  23         aged_circulation.duration_rule,
  24         aged_circulation.recurring_fine_rule,
  25         aged_circulation.max_fine_rule, aged_circulation.stop_fines,
  26         aged_circulation.workstation, aged_circulation.checkin_workstation,
  27         aged_circulation.checkin_scan_time, aged_circulation.parent_circ,
  28         NULL AS usr
  29        FROM action.aged_circulation
  30 UNION ALL
  31      SELECT DISTINCT circ.id,
  32         COALESCE(a.post_code, b.post_code) AS usr_post_code,
  33         p.home_ou AS usr_home_ou, p.profile AS usr_profile,
  34         date_part('year'::text, p.dob)::integer AS usr_birth_year,
  35         cp.call_number AS copy_call_number, circ.copy_location,
  36         cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
  37         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish,
  38         circ.target_copy, circ.circ_lib, circ.circ_staff,
  39         circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining,
  40         circ.grace_period, circ.due_date, circ.stop_fines_time,
  41         circ.checkin_time, circ.create_time, circ.duration,
  42         circ.fine_interval, circ.recurring_fine, circ.max_fine,
  43         circ.phone_renewal, circ.desk_renewal, circ.opac_renewal,
  44         circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule,
  45         circ.stop_fines, circ.workstation, circ.checkin_workstation,
  46         circ.checkin_scan_time, circ.parent_circ, circ.usr
  47        FROM action.circulation circ
  48   JOIN asset.copy cp ON circ.target_copy = cp.id
  49 JOIN asset.call_number cn ON cp.call_number = cn.id
  50 JOIN actor.usr p ON circ.usr = p.id
  51 LEFT JOIN actor.usr_address a ON p.mailing_address = a.id
  52 LEFT JOIN actor.usr_address b ON p.billing_address = b.id;
  53
  54
  55 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
  56     RETURNS SETOF action.all_circulation AS $$
  57 DECLARE
  58     tmp_circ action.all_circulation%ROWTYPE;
  59     circ_0 action.all_circulation%ROWTYPE;
  60 BEGIN
  61
  62     SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
  63
  64     IF tmp_circ IS NULL THEN
  65         RETURN NEXT tmp_circ;
  66     END IF;
  67     circ_0 := tmp_circ;
  68
  69     -- find the front of the chain
  70     WHILE TRUE LOOP
  71         SELECT INTO tmp_circ * FROM action.all_circulation
  72             WHERE id = tmp_circ.parent_circ;
  73         IF tmp_circ IS NULL THEN
  74             EXIT;
  75         END IF;
  76         circ_0 := tmp_circ;
  77     END LOOP;
  78
  79     -- now send the circs to the caller, oldest to newest
  80     tmp_circ := circ_0;
  81     WHILE TRUE LOOP
  82         IF tmp_circ IS NULL THEN
  83             EXIT;
  84         END IF;
  85         RETURN NEXT tmp_circ;
  86         SELECT INTO tmp_circ * FROM action.all_circulation
  87             WHERE parent_circ = tmp_circ.id;
  88     END LOOP;
  89
  90 END;
  91 $$ LANGUAGE 'plpgsql';
  92
  93 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
  94     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
  95
  96 DECLARE
  97
  98     -- first circ in the chain
  99     circ_0 action.all_circulation%ROWTYPE;
 100
 101     -- last circ in the chain
 102     circ_n action.all_circulation%ROWTYPE;
 103
 104     -- circ chain under construction
 105     chain action.circ_chain_summary;
 106     tmp_circ action.all_circulation%ROWTYPE;
 107
 108 BEGIN
 109
 110     chain.num_circs := 0;
 111     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
 112
 113         IF chain.num_circs = 0 THEN
 114             circ_0 := tmp_circ;
 115         END IF;
 116
 117         chain.num_circs := chain.num_circs + 1;
 118         circ_n := tmp_circ;
 119     END LOOP;
 120
 121     chain.start_time := circ_0.xact_start;
 122     chain.last_stop_fines := circ_n.stop_fines;
 123     chain.last_stop_fines_time := circ_n.stop_fines_time;
 124     chain.last_checkin_time := circ_n.checkin_time;
 125     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 126     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 127     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 128
 129     IF chain.num_circs > 1 THEN
 130         chain.last_renewal_time := circ_n.xact_start;
 131         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 132     END IF;
 133
 134     RETURN chain;
 135
 136 END;
 137 $$ LANGUAGE 'plpgsql';
 138
 139
 140 COMMIT;
 141