3 SELECT evergreen.upgrade_deps_block_check('1051', :eg_version);
5 CREATE OR REPLACE VIEW action.all_circulation_slim AS
39 FROM action.circulation
74 FROM action.aged_circulation
77 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
78 DROP FUNCTION action.all_circ_chain(INTEGER);
80 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
81 RETURNS SETOF action.all_circulation_slim AS $$
83 tmp_circ action.all_circulation_slim%ROWTYPE;
84 circ_0 action.all_circulation_slim%ROWTYPE;
87 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
89 IF tmp_circ IS NULL THEN
94 -- find the front of the chain
96 SELECT INTO tmp_circ * FROM action.all_circulation_slim
97 WHERE id = tmp_circ.parent_circ;
98 IF tmp_circ IS NULL THEN
104 -- now send the circs to the caller, oldest to newest
107 IF tmp_circ IS NULL THEN
110 RETURN NEXT tmp_circ;
111 SELECT INTO tmp_circ * FROM action.all_circulation_slim
112 WHERE parent_circ = tmp_circ.id;
116 $$ LANGUAGE 'plpgsql';
118 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
119 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
123 -- first circ in the chain
124 circ_0 action.all_circulation_slim%ROWTYPE;
126 -- last circ in the chain
127 circ_n action.all_circulation_slim%ROWTYPE;
129 -- circ chain under construction
130 chain action.circ_chain_summary;
131 tmp_circ action.all_circulation_slim%ROWTYPE;
135 chain.num_circs := 0;
136 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
138 IF chain.num_circs = 0 THEN
142 chain.num_circs := chain.num_circs + 1;
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;
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;
162 $$ LANGUAGE 'plpgsql';
164 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
165 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
167 badge rating.badge_with_orgs%ROWTYPE;
170 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
172 PERFORM rating.precalc_bibs_by_copy(badge_id);
174 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
175 SELECT id FROM precalc_filter_bib_list
177 SELECT id FROM precalc_bibs_by_copy_list
180 ANALYZE precalc_copy_filter_bib_list;
184 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
185 FROM (SELECT cn.record AS bib,
187 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
188 SUM( -- time copy spent circulating
192 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
196 )::NUMERIC AS circ_time
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 (
209 checkin_time IS NULL AND
210 stop_fines = 'MAXFINES'
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)
221 $f$ LANGUAGE PLPGSQL STRICT;