3 INSERT INTO config.upgrade_log (version) VALUES ('0484'); -- miker
5 DROP FUNCTION asset.metarecord_copy_count ( INT, BIGINT, BOOL );
6 DROP FUNCTION asset.record_copy_count ( INT, BIGINT, BOOL );
8 DROP FUNCTION asset.opac_ou_record_copy_count (INT, BIGINT);
9 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
14 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
16 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
21 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
25 actor.org_unit_descendants(ans.id) d
26 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
27 JOIN asset.copy cp ON (cp.id = av.id)
31 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
40 DROP FUNCTION asset.opac_lasso_record_copy_count (INT, BIGINT);
41 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
46 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
48 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
53 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
57 actor.org_unit_descendants(ans.id) d
58 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
59 JOIN asset.copy cp ON (cp.id = av.id)
63 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
72 DROP FUNCTION asset.staff_ou_record_copy_count (INT, BIGINT);
73 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
78 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
80 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
85 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
89 actor.org_unit_descendants(ans.id) d
90 JOIN asset.copy cp ON (cp.circ_lib = d.id)
91 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
95 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
102 $f$ LANGUAGE PLPGSQL;
104 DROP FUNCTION asset.staff_lasso_record_copy_count (INT, BIGINT);
105 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
110 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
112 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
117 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
121 actor.org_unit_descendants(ans.id) d
122 JOIN asset.copy cp ON (cp.circ_lib = d.id)
123 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
127 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
134 $f$ LANGUAGE PLPGSQL;
136 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
138 IF staff IS TRUE THEN
140 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
142 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
146 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
148 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
154 $f$ LANGUAGE PLPGSQL;
156 DROP FUNCTION asset.opac_ou_metarecord_copy_count (INT, BIGINT);
157 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
162 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
164 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
169 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
173 actor.org_unit_descendants(ans.id) d
174 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
175 JOIN asset.copy cp ON (cp.id = av.id)
176 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
180 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
187 $f$ LANGUAGE PLPGSQL;
189 DROP FUNCTION asset.opac_lasso_metarecord_copy_count (INT, BIGINT);
190 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
195 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
197 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
202 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
206 actor.org_unit_descendants(ans.id) d
207 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
208 JOIN asset.copy cp ON (cp.id = av.id)
209 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
213 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
220 $f$ LANGUAGE PLPGSQL;
222 DROP FUNCTION asset.staff_ou_metarecord_copy_count (INT, BIGINT);
223 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
228 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
230 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
235 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
239 actor.org_unit_descendants(ans.id) d
240 JOIN asset.copy cp ON (cp.circ_lib = d.id)
241 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
242 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
246 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
253 $f$ LANGUAGE PLPGSQL;
255 DROP FUNCTION asset.staff_lasso_metarecord_copy_count (INT, BIGINT);
256 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
261 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
263 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
268 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
272 actor.org_unit_descendants(ans.id) d
273 JOIN asset.copy cp ON (cp.circ_lib = d.id)
274 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
275 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
279 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
286 $f$ LANGUAGE PLPGSQL;
288 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
290 IF staff IS TRUE THEN
292 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
294 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
298 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
300 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
306 $f$ LANGUAGE PLPGSQL;