]> git.evergreen-ils.org Git - working/Evergreen.git/blob - Open-ILS/src/sql/Pg/upgrade/0451.schema.in-db_hold_test.sql
Stamping upgrade script for revive-qp-fts (LP 1161601).
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0451.schema.in-db_hold_test.sql
1 BEGIN;
2
3 INSERT INTO config.upgrade_log (version) VALUES ('0451'); -- miker
4
5 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$
6 DECLARE
7     current_requestor_group    permission.grp_tree%ROWTYPE;
8     requestor_object    actor.usr%ROWTYPE;
9     user_object        actor.usr%ROWTYPE;
10     item_object        asset.copy%ROWTYPE;
11     item_cn_object        asset.call_number%ROWTYPE;
12     rec_descriptor        metabib.rec_descriptor%ROWTYPE;
13     current_mp_weight    FLOAT;
14     matchpoint_weight    FLOAT;
15     tmp_weight        FLOAT;
16     current_mp        config.hold_matrix_matchpoint%ROWTYPE;
17     matchpoint        config.hold_matrix_matchpoint%ROWTYPE;
18 BEGIN
19     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
20     SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
21     SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
22     SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
23     SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;
24
25     PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;
26
27     IF NOT FOUND THEN
28         SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;
29     ELSE
30         SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = user_object.profile;
31     END IF;
32
33     LOOP 
34         -- for each potential matchpoint for this ou and group ...
35         FOR current_mp IN
36             SELECT    m.*
37               FROM    config.hold_matrix_matchpoint m
38               WHERE    m.requestor_grp = current_requestor_group.id AND m.active
39               ORDER BY    CASE WHEN m.circ_modifier    IS NOT NULL THEN 16 ELSE 0 END +
40                     CASE WHEN m.juvenile_flag    IS NOT NULL THEN 16 ELSE 0 END +
41                     CASE WHEN m.marc_type        IS NOT NULL THEN 8 ELSE 0 END +
42                     CASE WHEN m.marc_form        IS NOT NULL THEN 4 ELSE 0 END +
43                     CASE WHEN m.marc_vr_format    IS NOT NULL THEN 2 ELSE 0 END +
44                     CASE WHEN m.ref_flag        IS NOT NULL THEN 1 ELSE 0 END DESC LOOP
45
46             IF NOT current_mp.strict_ou_match THEN
47                 current_mp_weight := 5.0;
48             ELSE
49                 current_mp_weight := 0.0;
50             END IF;
51
52             IF current_mp.circ_modifier IS NOT NULL THEN
53                 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL;
54             END IF;
55
56             IF current_mp.marc_type IS NOT NULL THEN
57                 IF item_object.circ_as_type IS NOT NULL THEN
58                     CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
59                 ELSE
60                     CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
61                 END IF;
62             END IF;
63
64             IF current_mp.marc_form IS NOT NULL THEN
65                 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
66             END IF;
67
68             IF current_mp.marc_vr_format IS NOT NULL THEN
69                 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
70             END IF;
71
72             IF current_mp.juvenile_flag IS NOT NULL THEN
73                 CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
74             END IF;
75
76             IF current_mp.ref_flag IS NOT NULL THEN
77                 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
78             END IF;
79
80
81             -- caclulate the rule match weight
82             IF current_mp.item_owning_ou IS NOT NULL THEN
83                 CONTINUE WHEN current_mp.item_owning_ou NOT IN (SELECT (actor.org_unit_ancestors(item_cn_object.owning_lib)).id);
84                 IF NOT current_mp.strict_ou_match THEN
85                     SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
86                 ELSE
87                     CONTINUE WHEN current_mp.item_owning_ou <> item_cn_object.owning_lib;
88                     tmp_weight := CASE WHEN current_mp.item_owning_ou = item_cn_object.owning_lib THEN 1.0 ELSE 0.0 END;
89                 END IF;
90                 current_mp_weight := current_mp_weight - tmp_weight;
91             END IF; 
92
93             IF current_mp.item_circ_ou IS NOT NULL THEN
94                 CONTINUE WHEN current_mp.item_circ_ou NOT IN (SELECT (actor.org_unit_ancestors(item_object.circ_lib)).id);
95                 IF NOT current_mp.strict_ou_match THEN
96                     SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
97                 ELSE
98                     CONTINUE WHEN current_mp.item_circ_ou <> item_object.circ_lib;
99                     tmp_weight := CASE WHEN current_mp.item_circ_ou = item_object.circ_lib THEN 1.0 ELSE 0.0 END;
100                 END IF;
101                 current_mp_weight := current_mp_weight - tmp_weight;
102             END IF; 
103
104             IF current_mp.pickup_ou IS NOT NULL THEN
105                 CONTINUE WHEN current_mp.pickup_ou NOT IN (SELECT (actor.org_unit_ancestors(pickup_ou)).id);
106                 IF NOT current_mp.strict_ou_match THEN
107                     SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
108                 ELSE
109                     CONTINUE WHEN current_mp.pickup_ou <> pickup_ou;
110                     tmp_weight := CASE WHEN current_mp.pickup_ou = pickiup_ou THEN 1.0 ELSE 0.0 END;
111                 END IF;
112                 current_mp_weight := current_mp_weight - tmp_weight;
113             END IF; 
114
115             IF current_mp.request_ou IS NOT NULL THEN
116                 CONTINUE WHEN current_mp.request_ou NOT IN (SELECT (actor.org_unit_ancestors(request_ou)).id);
117                 IF NOT current_mp.strict_ou_match THEN
118                     SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
119                 ELSE
120                     CONTINUE WHEN current_mp.request_ou <> request_ou;
121                     tmp_weight := CASE WHEN current_mp.request_ou = request_ou THEN 1.0 ELSE 0.0 END;
122                 END IF;
123                 current_mp_weight := current_mp_weight - tmp_weight;
124             END IF; 
125
126             IF current_mp.user_home_ou IS NOT NULL THEN
127                 CONTINUE WHEN current_mp.user_home_ou NOT IN (SELECT (actor.org_unit_ancestors(user_object.home_ou)).id);
128                 IF NOT current_mp.strict_ou_match THEN
129                     SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
130                 ELSE
131                     CONTINUE WHEN current_mp.user_home_ou <> user_object.home_ou;
132                     tmp_weight := CASE WHEN current_mp.user_home_ou = user_object.home_ou THEN 1.0 ELSE 0.0 END;
133                 END IF;
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 COMMIT;
155