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

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