3 INSERT INTO config.upgrade_log (version) VALUES ('0367'); -- 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$
10 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 = record;
12 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
17 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
19 CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END
21 actor.org_unit_descendants(ans.id) d
22 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
23 JOIN asset.copy cp ON (cp.id = av.id)
27 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
36 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$
41 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 = record;
43 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
48 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
52 actor.org_unit_descendants(ans.id) d
53 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
54 JOIN asset.copy cp ON (cp.id = av.id)
58 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
67 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$
72 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 = record;
74 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
79 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
83 actor.org_unit_descendants(ans.id) d
84 JOIN asset.copy cp ON (cp.circ_lib = d.id)
85 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
89 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
98 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$
103 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 = record;
105 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
110 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
114 actor.org_unit_descendants(ans.id) d
115 JOIN asset.copy cp ON (cp.circ_lib = d.id)
116 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
120 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
127 $f$ LANGUAGE PLPGSQL;
129 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$
131 IF staff IS TRUE THEN
133 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record );
135 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record );
139 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record );
141 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record );
147 $f$ LANGUAGE PLPGSQL;
149 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$
154 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 = record;
156 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
161 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
165 actor.org_unit_descendants(ans.id) d
166 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
167 JOIN asset.copy cp ON (cp.id = av.id)
168 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
172 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
179 $f$ LANGUAGE PLPGSQL;
181 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$
186 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 = record;
188 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
193 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
197 actor.org_unit_descendants(ans.id) d
198 JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
199 JOIN asset.copy cp ON (cp.id = av.id)
200 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
204 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
211 $f$ LANGUAGE PLPGSQL;
213 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$
218 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 = record;
220 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
225 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
229 actor.org_unit_descendants(ans.id) d
230 JOIN asset.copy cp ON (cp.circ_lib = d.id)
231 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
232 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
236 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
243 $f$ LANGUAGE PLPGSQL;
245 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$
250 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 = record;
252 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
257 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
261 actor.org_unit_descendants(ans.id) d
262 JOIN asset.copy cp ON (cp.circ_lib = d.id)
263 JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
264 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
268 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
275 $f$ LANGUAGE PLPGSQL;
277 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$
279 IF staff IS TRUE THEN
281 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record );
283 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record );
287 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record );
289 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record );
295 $f$ LANGUAGE PLPGSQL;