LP#1695007 All-circulations slim DB VIEW
[working/Evergreen.git] / 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