LP#1773479 Show Located URIs in browse
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.function.located_uri_browse_visibility.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
4
5 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
6  RETURNS SETOF metabib.flat_browse_entry_appearance
7 AS $f$
8 DECLARE
9     curs                    REFCURSOR;
10     rec                     RECORD;
11     qpfts_query             TEXT;
12     aqpfts_query            TEXT;
13     afields                 INT[];
14     bfields                 INT[];
15     result_row              metabib.flat_browse_entry_appearance%ROWTYPE;
16     results_skipped         INT := 0;
17     row_counter             INT := 0;
18     row_number              INT;
19     slice_start             INT;
20     slice_end               INT;
21     full_end                INT;
22     all_records             BIGINT[];
23     all_brecords             BIGINT[];
24     all_arecords            BIGINT[];
25     superpage_of_records    BIGINT[];
26     superpage_size          INT;
27     c_tests                 TEXT := '';
28     b_tests                 TEXT := '';
29     c_orgs                  INT[];
30     unauthorized_entry      RECORD;
31 BEGIN
32     IF count_up_from_zero THEN
33         row_number := 0;
34     ELSE
35         row_number := -1;
36     END IF;
37
38     IF NOT staff THEN
39         SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
40     END IF;
41
42     -- b_tests supplies its own query_int operator, c_tests does not
43     IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
44
45     SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
46
47     c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
48                || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
49
50     PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
51     IF FOUND THEN
52         b_tests := b_tests || search.calculate_visibility_attribute_test(
53             'luri_org',
54             (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
55         );
56     ELSE
57         b_tests := b_tests || search.calculate_visibility_attribute_test(
58             'luri_org',
59             (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
60         );
61     END IF;
62
63     IF context_locations THEN
64         IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
65         c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
66     END IF;
67
68     OPEN curs NO SCROLL FOR EXECUTE query;
69
70     LOOP
71         FETCH curs INTO rec;
72         IF NOT FOUND THEN
73             IF result_row.pivot_point IS NOT NULL THEN
74                 RETURN NEXT result_row;
75             END IF;
76             RETURN;
77         END IF;
78
79         --Is unauthorized?
80         SELECT INTO unauthorized_entry *
81         FROM metabib.browse_entry_simple_heading_map mbeshm
82         INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
83         INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
84         JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
85         WHERE mbeshm.entry = rec.id
86         AND   ahf.heading_purpose = 'variant';
87
88         -- Gather aggregate data based on the MBE row we're looking at now, authority axis
89         IF (unauthorized_entry.record IS NOT NULL) THEN
90             --unauthorized term belongs to an auth linked to a bib?
91             SELECT INTO all_arecords, result_row.sees, afields
92                     ARRAY_AGG(DISTINCT abl.bib),
93                     STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
94                     ARRAY_AGG(DISTINCT map.metabib_field)
95             FROM authority.bib_linking abl
96             INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
97                     map.authority_field = unauthorized_entry.atag
98                     AND map.metabib_field = ANY(fields)
99             )
100             WHERE abl.authority = unauthorized_entry.record;
101         ELSE
102             --do usual procedure
103             SELECT INTO all_arecords, result_row.sees, afields
104                     ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
105                     STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
106                     ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
107
108             FROM  metabib.browse_entry_simple_heading_map mbeshm
109                     JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
110                     JOIN authority.authority_linking aal ON ( ash.record = aal.source )
111                     JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
112                     JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
113                         ash.atag = map.authority_field
114                         AND map.metabib_field = ANY(fields)
115                     )
116                     JOIN authority.control_set_authority_field acsaf ON (
117                         map.authority_field = acsaf.id
118                     )
119                     JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
120               WHERE mbeshm.entry = rec.id
121               AND   ahf.heading_purpose = 'variant';
122
123         END IF;
124
125         -- Gather aggregate data based on the MBE row we're looking at now, bib axis
126         SELECT INTO all_brecords, result_row.authorities, bfields
127                 ARRAY_AGG(DISTINCT source),
128                 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
129                 ARRAY_AGG(DISTINCT def)
130           FROM  metabib.browse_entry_def_map
131           WHERE entry = rec.id
132                 AND def = ANY(fields);
133
134         SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
135
136         result_row.sources := 0;
137         result_row.asources := 0;
138
139         -- Bib-linked vis checking
140         IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
141
142             SELECT  INTO result_row.sources COUNT(DISTINCT b.id)
143               FROM  biblio.record_entry b
144                     LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
145               WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
146                     AND (
147                         acvac.vis_attr_vector @@ c_tests::query_int
148                         OR b.vis_attr_vector @@ b_tests::query_int
149                     );
150
151             result_row.accurate := TRUE;
152
153         END IF;
154
155         -- Authority-linked vis checking
156         IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
157
158             SELECT  INTO result_row.asources COUNT(DISTINCT b.id)
159               FROM  biblio.record_entry b
160                     LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
161               WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
162                     AND (
163                         acvac.vis_attr_vector @@ c_tests::query_int
164                         OR b.vis_attr_vector @@ b_tests::query_int
165                     );
166
167             result_row.aaccurate := TRUE;
168
169         END IF;
170
171         IF result_row.sources > 0 OR result_row.asources > 0 THEN
172
173             -- The function that calls this function needs row_number in order
174             -- to correctly order results from two different runs of this
175             -- functions.
176             result_row.row_number := row_number;
177
178             -- Now, if row_counter is still less than limit, return a row.  If
179             -- not, but it is less than next_pivot_pos, continue on without
180             -- returning actual result rows until we find
181             -- that next pivot, and return it.
182
183             IF row_counter < result_limit THEN
184                 result_row.browse_entry := rec.id;
185                 result_row.value := rec.value;
186
187                 RETURN NEXT result_row;
188             ELSE
189                 result_row.browse_entry := NULL;
190                 result_row.authorities := NULL;
191                 result_row.fields := NULL;
192                 result_row.value := NULL;
193                 result_row.sources := NULL;
194                 result_row.sees := NULL;
195                 result_row.accurate := NULL;
196                 result_row.aaccurate := NULL;
197                 result_row.pivot_point := rec.id;
198
199                 IF row_counter >= next_pivot_pos THEN
200                     RETURN NEXT result_row;
201                     RETURN;
202                 END IF;
203             END IF;
204
205             IF count_up_from_zero THEN
206                 row_number := row_number + 1;
207             ELSE
208                 row_number := row_number - 1;
209             END IF;
210
211             -- row_counter is different from row_number.
212             -- It simply counts up from zero so that we know when
213             -- we've reached our limit.
214             row_counter := row_counter + 1;
215         END IF;
216     END LOOP;
217 END;
218 $f$ LANGUAGE plpgsql ROWS 10;
219
220 COMMIT;