add a note about how to reload this portion of the schema, and from whence to retriev...
[Evergreen.git] / Open-ILS / src / sql / Pg / 110.hold_matrix.sql
1 /*
2
3 -- If, for some reason, you need to reload this chunk of the schema
4 -- just use the following two statements to remove the tables before
5 -- running the rest of the file.  See 950.data.seed-values.sql for
6 -- the one default entry to add back to config.hold_matrix_matchpoint.
7
8 DROP TABLE config.hold_matrix_matchpoint CASCADE;
9 DROP TABLE config.hold_matrix_test CASCADE;
10
11 */
12
13 BEGIN;
14
15
16 --
17 --                 ****** Which ruleset and tests to use *******
18 --
19 -- * Most specific range for org_unit and grp wins.
20 --
21 -- * circ_modifier match takes precidence over marc_type match, if circ_modifier is set here
22 --
23 -- * marc_type is first checked against the circ_as_type from the copy, then the item type from the marc record
24 --
25 -- * If neither circ_modifier nor marc_type is set (both are NULLABLE) then the entry defines the default
26 --   ruleset and tests for the OU + group (like BOOK in PINES)
27 --
28
29
30
31 CREATE TABLE config.hold_matrix_matchpoint (
32         id                      SERIAL      PRIMARY KEY,
33         active                  BOOL    NOT NULL DEFAULT TRUE,
34         user_home_ou    INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
35         request_ou              INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
36         pickup_ou               INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
37         item_owning_ou  INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
38         item_circ_ou    INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
39         usr_grp                 INT         REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED,  -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering
40         requestor_grp   INT         NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering
41         circ_modifier   TEXT    REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED,
42         marc_type               TEXT    REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED,
43         marc_form               TEXT    REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED,
44         marc_vr_format  TEXT    REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED,
45         ref_flag                BOOL,
46         CONSTRAINT hous_once_per_grp_loc_mod_marc UNIQUE (user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, requestor_grp, usr_grp, circ_modifier, marc_type, marc_form, marc_vr_format)
47 );
48
49 -- Tests to determine if hold against a specific copy is possible for a user at (and from) a location
50 CREATE TABLE config.hold_matrix_test (
51         matchpoint                      INT     PRIMARY KEY REFERENCES config.hold_matrix_matchpoint (id) DEFERRABLE INITIALLY DEFERRED,
52         holdable                        BOOL    NOT NULL DEFAULT TRUE,                          -- Hard "can't hold" flag requiring an override
53         distance_is_from_owner  BOOL    NOT NULL DEFAULT FALSE,                         -- How to calculate transit_range.  True means owning lib, false means copy circ lib
54         transit_range               INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,              -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here
55         max_holds                       INT,                                                    -- Total hold requests must be less than this, NULL means skip (always pass)
56         include_frozen_holds    BOOL    NOT NULL DEFAULT TRUE,                          -- Include frozen hold requests in the count for max_holds test
57         stop_blocked_user       BOOL    NOT NULL DEFAULT FALSE,                         -- Stop users who cannot check out items from placing holds
58         age_hold_protect_rule   INT     REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED      -- still not sure we want to move this off the copy
59 );
60
61 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$
62 DECLARE
63         current_requestor_group permission.grp_tree%ROWTYPE;
64         root_ou                 actor.org_unit%ROWTYPE;
65         requestor_object        actor.usr%ROWTYPE;
66         user_object             actor.usr%ROWTYPE;
67         item_object             asset.copy%ROWTYPE;
68         item_cn_object          asset.call_number%ROWTYPE;
69         rec_descriptor          metabib.rec_descriptor%ROWTYPE;
70         current_mp_weight       FLOAT;
71         matchpoint_weight       FLOAT;
72         tmp_weight              FLOAT;
73         current_mp              config.hold_matrix_matchpoint%ROWTYPE;
74         matchpoint              config.hold_matrix_matchpoint%ROWTYPE;
75 BEGIN
76         SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL;
77         SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
78         SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
79         SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
80         SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
81         SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;
82         SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;
83
84         LOOP 
85                 -- for each potential matchpoint for this ou and group ...
86                 FOR current_mp IN
87                         SELECT  m.*
88                           FROM  config.hold_matrix_matchpoint m
89                           WHERE m.requestor_grp = current_requestor_group.id AND m.active
90                           ORDER BY      CASE WHEN m.circ_modifier       IS NOT NULL THEN 16 ELSE 0 END +
91                                         CASE WHEN m.marc_type           IS NOT NULL THEN 8 ELSE 0 END +
92                                         CASE WHEN m.marc_form           IS NOT NULL THEN 4 ELSE 0 END +
93                                         CASE WHEN m.marc_vr_format      IS NOT NULL THEN 2 ELSE 0 END +
94                                         CASE WHEN m.ref_flag            IS NOT NULL THEN 1 ELSE 0 END DESC LOOP
95
96                         current_mp_weight := 5.0;
97
98                         IF current_mp.circ_modifier IS NOT NULL THEN
99                                 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier;
100                         END IF;
101
102                         IF current_mp.marc_type IS NOT NULL THEN
103                                 IF item_object.circ_as_type IS NOT NULL THEN
104                                         CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
105                                 ELSE
106                                         CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
107                                 END IF;
108                         END IF;
109
110                         IF current_mp.marc_form IS NOT NULL THEN
111                                 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
112                         END IF;
113
114                         IF current_mp.marc_vr_format IS NOT NULL THEN
115                                 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
116                         END IF;
117
118                         IF current_mp.ref_flag IS NOT NULL THEN
119                                 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
120                         END IF;
121
122
123                         -- caclulate the rule match weight
124                         IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN
125                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
126                                 current_mp_weight := current_mp_weight - tmp_weight;
127                         END IF; 
128
129                         IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN
130                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
131                                 current_mp_weight := current_mp_weight - tmp_weight;
132                         END IF; 
133
134                         IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN
135                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
136                                 current_mp_weight := current_mp_weight - tmp_weight;
137                         END IF; 
138
139                         IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN
140                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
141                                 current_mp_weight := current_mp_weight - tmp_weight;
142                         END IF; 
143
144                         IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN
145                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
146                                 current_mp_weight := current_mp_weight - tmp_weight;
147                         END IF; 
148
149                         -- set the matchpoint if we found the best one
150                         IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN
151                                 matchpoint = current_mp;
152                                 matchpoint_weight = current_mp_weight;
153                         END IF;
154
155                 END LOOP;
156
157                 EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL;
158
159                 SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent;
160         END LOOP;
161
162         RETURN matchpoint.id;
163 END;
164 $func$ LANGUAGE plpgsql;
165
166
167 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$
168 DECLARE
169         matchpoint_id           INT;
170         user_object             actor.usr%ROWTYPE;
171         age_protect_object      config.rule_age_hold_protect%ROWTYPE;
172         transit_range_ou_type   actor.org_unit_type%ROWTYPE;
173         transit_source          actor.org_unit%ROWTYPE;
174         item_object             asset.copy%ROWTYPE;
175         result                  action.matrix_test_result;
176         hold_test               config.hold_matrix_test%ROWTYPE;
177         hold_count              INT;
178         hold_transit_prox       INT;
179         frozen_hold_count       INT;
180         patron_penalties        INT;
181         done                    BOOL := FALSE;
182 BEGIN
183         SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
184
185         -- Fail if we couldn't find a user
186         IF user_object.id IS NULL THEN
187                 result.fail_part := 'no_user';
188                 result.success := FALSE;
189                 done := TRUE;
190                 RETURN NEXT result;
191                 RETURN;
192         END IF;
193
194         -- Fail if user is barred
195         IF user_object.barred IS TRUE THEN
196                 result.fail_part := 'actor.usr.barred';
197                 result.success := FALSE;
198                 done := TRUE;
199                 RETURN NEXT result;
200                 RETURN;
201         END IF;
202
203         SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
204
205         -- Fail if we couldn't find a copy
206         IF item_object.id IS NULL THEN
207                 result.fail_part := 'no_item';
208                 result.success := FALSE;
209                 done := TRUE;
210                 RETURN NEXT result;
211                 RETURN;
212         END IF;
213
214         SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
215
216         -- Fail if we couldn't find any matchpoint (requires a default)
217         IF matchpoint_id IS NULL THEN
218                 result.fail_part := 'no_matchpoint';
219                 result.success := FALSE;
220                 done := TRUE;
221                 RETURN NEXT result;
222                 RETURN;
223         END IF;
224
225         SELECT INTO hold_test * FROM config.hold_matrix_test WHERE matchpoint = matchpoint_id;
226
227         result.matchpoint := matchpoint_id;
228         result.success := TRUE;
229
230         IF hold_test.holdable IS FALSE THEN
231                 result.fail_part := 'config.hold_matrix_test.holdable';
232                 result.success := FALSE;
233                 done := TRUE;
234                 RETURN NEXT result;
235         END IF;
236
237         IF hold_test.transit_range IS NOT NULL THEN
238                 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
239                 IF hold_test.distance_is_from_owner THEN
240                         SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
241                 ELSE
242                         SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
243                 END IF;
244
245                 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
246
247                 IF NOT FOUND THEN
248                         result.fail_part := 'transit_range';
249                         result.success := FALSE;
250                         done := TRUE;
251                         RETURN NEXT result;
252                 END IF;
253         END IF;
254
255         IF hold_test.stop_blocked_user IS TRUE THEN
256                 SELECT  INTO patron_penalties COUNT(*)
257                   FROM  actor.usr_standing_penalty
258                   WHERE usr = match_user;
259
260                 IF items_out > 0 THEN
261                         result.fail_part := 'config.hold_matrix_test.stop_blocked_user';
262                         result.success := FALSE;
263                         done := TRUE;
264                         RETURN NEXT result;
265                 END IF;
266         END IF;
267
268         IF hold_test.max_holds IS NOT NULL THEN
269                 SELECT  INTO hold_count COUNT(*)
270                   FROM  action.hold_request
271                   WHERE usr = match_user
272                         AND fulfillment_time IS NULL
273                         AND cancel_time IS NULL
274                         AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
275
276                 IF items_out >= hold_test.max_holds THEN
277                         result.fail_part := 'config.hold_matrix_test.max_holds';
278                         result.success := FALSE;
279                         done := TRUE;
280                         RETURN NEXT result;
281                 END IF;
282         END IF;
283
284         IF item_object.age_protect IS NOT NULL THEN
285                 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
286
287                 IF item_object.create_date + age_protect_object.age > NOW() THEN
288                         IF hold_test.distance_is_from_owner THEN
289                                 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
290                         ELSE
291                                 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
292                         END IF;
293
294                         IF hold_transit_prox > age_protect_object.prox THEN
295                                 result.fail_part := 'config.rule_age_hold_protect.prox';
296                                 result.success := FALSE;
297                                 done := TRUE;
298                                 RETURN NEXT result;
299                         END IF;
300                 END IF;
301         END IF;
302
303         IF NOT done THEN
304                 RETURN NEXT result;
305         END IF;
306
307         RETURN;
308 END;
309 $func$ LANGUAGE plpgsql;
310
311 COMMIT;
312