3 -- SELECT evergreen.upgrade_deps_block_check('1XXX', :eg_version);
5 CREATE OR REPLACE VIEW action.all_circulation_slim AS
6 SELECT * FROM action.circulation
41 FROM action.aged_circulation
44 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
45 DROP FUNCTION action.all_circ_chain(INTEGER);
47 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
48 RETURNS SETOF action.all_circulation_slim AS $$
50 tmp_circ action.all_circulation_slim%ROWTYPE;
51 circ_0 action.all_circulation_slim%ROWTYPE;
54 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
56 IF tmp_circ IS NULL THEN
61 -- find the front of the chain
63 SELECT INTO tmp_circ * FROM action.all_circulation_slim
64 WHERE id = tmp_circ.parent_circ;
65 IF tmp_circ IS NULL THEN
71 -- now send the circs to the caller, oldest to newest
74 IF tmp_circ IS NULL THEN
78 SELECT INTO tmp_circ * FROM action.all_circulation_slim
79 WHERE parent_circ = tmp_circ.id;
83 $$ LANGUAGE 'plpgsql';
85 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
86 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
90 -- first circ in the chain
91 circ_0 action.all_circulation_slim%ROWTYPE;
93 -- last circ in the chain
94 circ_n action.all_circulation_slim%ROWTYPE;
96 -- circ chain under construction
97 chain action.circ_chain_summary;
98 tmp_circ action.all_circulation_slim%ROWTYPE;
102 chain.num_circs := 0;
103 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
105 IF chain.num_circs = 0 THEN
109 chain.num_circs := chain.num_circs + 1;
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;
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;
129 $$ LANGUAGE 'plpgsql';
131 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
132 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
134 badge rating.badge_with_orgs%ROWTYPE;
137 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
139 PERFORM rating.precalc_bibs_by_copy(badge_id);
141 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
142 SELECT id FROM precalc_filter_bib_list
144 SELECT id FROM precalc_bibs_by_copy_list
147 ANALYZE precalc_copy_filter_bib_list;
151 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
152 FROM (SELECT cn.record AS bib,
154 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
155 SUM( -- time copy spent circulating
159 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
163 )::NUMERIC AS circ_time
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 (
176 checkin_time IS NULL AND
177 stop_fines = 'MAXFINES'
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)
188 $f$ LANGUAGE PLPGSQL STRICT;