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