3 INSERT INTO config.upgrade_log (version) VALUES ('0366'); -- miker
5 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
9 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
14 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
16 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
18 actor.org_unit_descendants(ans.id) d
19 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
20 JOIN asset.copy cp ON (cp.id = av.id)
21 JOIN biblio.record_entry b ON (b.id = av.record)
22 LEFT JOIN config.bib_source src ON (b.source = src.id)
30 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
34 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
39 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
41 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
43 actor.org_unit_descendants(ans.id) d
44 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
45 JOIN asset.copy cp ON (cp.id = av.id)
46 JOIN biblio.record_entry b ON (b.id = av.record)
47 LEFT JOIN config.bib_source src ON (b.source = src.id)
55 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
59 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
64 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
66 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
68 actor.org_unit_descendants(ans.id) d
69 JOIN asset.copy cp ON (cp.circ_lib = d.id)
70 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
71 JOIN biblio.record_entry b ON (b.id = cn.record)
72 LEFT JOIN config.bib_source src ON (b.source = src.id)
80 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
84 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
89 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
91 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
93 actor.org_unit_descendants(ans.id) d
94 JOIN asset.copy cp ON (cp.circ_lib = d.id)
95 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
96 JOIN biblio.record_entry b ON (b.id = cn.record)
97 LEFT JOIN config.bib_source src ON (b.source = src.id)
103 $f$ LANGUAGE PLPGSQL;
105 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
107 IF staff IS TRUE THEN
109 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record );
111 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record );
115 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record );
117 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record );
123 $f$ LANGUAGE PLPGSQL;
125 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
129 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
134 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
136 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
138 actor.org_unit_descendants(ans.id) d
139 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
140 JOIN asset.copy cp ON (cp.id = av.id)
141 JOIN biblio.record_entry b ON (b.id = av.record)
142 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
143 LEFT JOIN config.bib_source src ON (b.source = src.id)
149 $f$ LANGUAGE PLPGSQL;
151 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
155 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
160 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
162 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
164 actor.org_unit_descendants(ans.id) d
165 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
166 JOIN asset.copy cp ON (cp.id = av.id)
167 JOIN biblio.record_entry b ON (b.id = av.record)
168 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
169 LEFT JOIN config.bib_source src ON (b.source = src.id)
175 $f$ LANGUAGE PLPGSQL;
177 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
181 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
186 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
188 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
190 actor.org_unit_descendants(ans.id) d
191 JOIN asset.copy cp ON (cp.circ_lib = d.id)
192 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
193 JOIN biblio.record_entry b ON (b.id = cn.record)
194 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
195 LEFT JOIN config.bib_source src ON (b.source = src.id)
201 $f$ LANGUAGE PLPGSQL;
203 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
207 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
212 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
214 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
216 actor.org_unit_descendants(ans.id) d
217 JOIN asset.copy cp ON (cp.circ_lib = d.id)
218 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
219 JOIN biblio.record_entry b ON (b.id = cn.record)
220 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
221 LEFT JOIN config.bib_source src ON (b.source = src.id)
227 $f$ LANGUAGE PLPGSQL;
229 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
231 IF staff IS TRUE THEN
233 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record );
235 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record );
239 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record );
241 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record );
247 $f$ LANGUAGE PLPGSQL;