Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql

   1 BEGIN;
   2
   3 -- SELECT evergreen.upgrade_deps_block_check('1XXX', :eg_version);
   4
   5 CREATE OR REPLACE VIEW action.all_circulation_slim AS
   6     SELECT * FROM action.circulation
   7 UNION ALL
   8     SELECT
   9         id,
  10         NULL AS usr,
  11         xact_start,
  12         xact_finish,
  13         unrecovered,
  14         target_copy,
  15         circ_lib,
  16         circ_staff,
  17         checkin_staff,
  18         checkin_lib,
  19         renewal_remaining,
  20         grace_period,
  21         due_date,
  22         stop_fines_time,
  23         checkin_time,
  24         create_time,
  25         duration,
  26         fine_interval,
  27         recurring_fine,
  28         max_fine,
  29         phone_renewal,
  30         desk_renewal,
  31         opac_renewal,
  32         duration_rule,
  33         recurring_fine_rule,
  34         max_fine_rule,
  35         stop_fines,
  36         workstation,
  37         checkin_workstation,
  38         copy_location,
  39         checkin_scan_time,
  40         parent_circ
  41     FROM action.aged_circulation
  42 ;
  43
  44 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
  45 DROP FUNCTION action.all_circ_chain(INTEGER);
  46
  47 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
  48     RETURNS SETOF action.all_circulation_slim AS $$
  49 DECLARE
  50     tmp_circ action.all_circulation_slim%ROWTYPE;
  51     circ_0 action.all_circulation_slim%ROWTYPE;
  52 BEGIN
  53
  54     SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
  55
  56     IF tmp_circ IS NULL THEN
  57         RETURN NEXT tmp_circ;
  58     END IF;
  59     circ_0 := tmp_circ;
  60
  61     -- find the front of the chain
  62     WHILE TRUE LOOP
  63         SELECT INTO tmp_circ * FROM action.all_circulation_slim
  64             WHERE id = tmp_circ.parent_circ;
  65         IF tmp_circ IS NULL THEN
  66             EXIT;
  67         END IF;
  68         circ_0 := tmp_circ;
  69     END LOOP;
  70
  71     -- now send the circs to the caller, oldest to newest
  72     tmp_circ := circ_0;
  73     WHILE TRUE LOOP
  74         IF tmp_circ IS NULL THEN
  75             EXIT;
  76         END IF;
  77         RETURN NEXT tmp_circ;
  78         SELECT INTO tmp_circ * FROM action.all_circulation_slim
  79             WHERE parent_circ = tmp_circ.id;
  80     END LOOP;
  81
  82 END;
  83 $$ LANGUAGE 'plpgsql';
  84
  85 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
  86     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
  87
  88 DECLARE
  89
  90     -- first circ in the chain
  91     circ_0 action.all_circulation_slim%ROWTYPE;
  92
  93     -- last circ in the chain
  94     circ_n action.all_circulation_slim%ROWTYPE;
  95
  96     -- circ chain under construction
  97     chain action.circ_chain_summary;
  98     tmp_circ action.all_circulation_slim%ROWTYPE;
  99
 100 BEGIN
 101
 102     chain.num_circs := 0;
 103     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
 104
 105         IF chain.num_circs = 0 THEN
 106             circ_0 := tmp_circ;
 107         END IF;
 108
 109         chain.num_circs := chain.num_circs + 1;
 110         circ_n := tmp_circ;
 111     END LOOP;
 112
 113     chain.start_time := circ_0.xact_start;
 114     chain.last_stop_fines := circ_n.stop_fines;
 115     chain.last_stop_fines_time := circ_n.stop_fines_time;
 116     chain.last_checkin_time := circ_n.checkin_time;
 117     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 118     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 119     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 120
 121     IF chain.num_circs > 1 THEN
 122         chain.last_renewal_time := circ_n.xact_start;
 123         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 124     END IF;
 125
 126     RETURN chain;
 127
 128 END;
 129 $$ LANGUAGE 'plpgsql';
 130
 131 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
 132     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 133 DECLARE
 134     badge   rating.badge_with_orgs%ROWTYPE;
 135 BEGIN
 136
 137     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 138
 139     PERFORM rating.precalc_bibs_by_copy(badge_id);
 140
 141     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 142         SELECT id FROM precalc_filter_bib_list
 143             INTERSECT
 144         SELECT id FROM precalc_bibs_by_copy_list
 145     );
 146
 147     ANALYZE precalc_copy_filter_bib_list;
 148
 149     RETURN QUERY
 150      SELECT bib,
 151             SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
 152       FROM  (SELECT cn.record AS bib,
 153                     cp.id,
 154                     EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
 155                     SUM(  -- time copy spent circulating
 156                         EXTRACT(
 157                             EPOCH FROM
 158                             AGE(
 159                                 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
 160                                 circ.xact_start
 161                             )
 162                         )
 163                     )::NUMERIC AS circ_time
 164               FROM  asset.copy cp
 165                     JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 166                     JOIN asset.call_number cn ON (cn.id = cp.call_number)
 167                     LEFT JOIN action.all_circulation_slim circ ON (
 168                         circ.target_copy = cp.id
 169                         AND stop_fines NOT IN (
 170                             'LOST',
 171                             'LONGOVERDUE',
 172                             'CLAIMSRETURNED',
 173                             'LONGOVERDUE'
 174                         )
 175                         AND NOT (
 176                             checkin_time IS NULL AND
 177                             stop_fines = 'MAXFINES'
 178                         )
 179                     )
 180               WHERE cn.owning_lib = ANY (badge.orgs)
 181                     AND cp.active_date IS NOT NULL
 182                     -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
 183                     AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
 184               GROUP BY 1,2,3
 185             ) x
 186       GROUP BY 1;
 187 END;
 188 $f$ LANGUAGE PLPGSQL STRICT;
 189
 190
 191 -- ROLLBACK;
 192 COMMIT;
 193