4 INSERT INTO config.upgrade_log (version) VALUES ('0348'); --miker
6 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT ep_once_per_grp_loc_mod_marc;
8 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN copy_circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED;
9 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN copy_owning_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED;
11 ALTER TABLE config.circ_matrix_matchpoint ADD CONSTRAINT ep_once_per_grp_loc_mod_marc UNIQUE (
12 grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag,
13 juvenile_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal, copy_circ_lib,
18 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS config.circ_matrix_matchpoint AS $func$
20 current_group permission.grp_tree%ROWTYPE;
21 user_object actor.usr%ROWTYPE;
22 item_object asset.copy%ROWTYPE;
23 cn_object asset.call_number%ROWTYPE;
24 rec_descriptor metabib.rec_descriptor%ROWTYPE;
25 current_mp config.circ_matrix_matchpoint%ROWTYPE;
26 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
28 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
29 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
30 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
31 SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r JOIN asset.call_number c USING (record) WHERE c.id = item_object.call_number;
32 SELECT INTO current_group * FROM permission.grp_tree WHERE id = user_object.profile;
35 -- for each potential matchpoint for this ou and group ...
38 FROM config.circ_matrix_matchpoint m
39 JOIN actor.org_unit_ancestors( context_ou ) d ON (m.org_unit = d.id)
40 LEFT JOIN actor.org_unit_proximity p ON (p.from_org = context_ou AND p.to_org = d.id)
41 WHERE m.grp = current_group.id
43 AND (m.copy_owning_lib IS NULL OR cn_object.owning_lib IN ( SELECT id FROM actor.org_unit_descendants(m.copy_owning_lib) ))
44 AND (m.copy_circ_lib IS NULL OR item_object.circ_lib IN ( SELECT id FROM actor.org_unit_descendants(m.copy_circ_lib) ))
45 ORDER BY CASE WHEN p.prox IS NULL THEN 999 ELSE p.prox END,
46 CASE WHEN m.copy_owning_lib IS NOT NULL
47 THEN 256 / ( SELECT COALESCE(prox, 255) + 1 FROM actor.org_unit_proximity WHERE to_org = cn_object.owning_lib AND from_org = m.copy_owning_lib LIMIT 1 )
50 CASE WHEN m.copy_circ_lib IS NOT NULL
51 THEN 256 / ( SELECT COALESCE(prox, 255) + 1 FROM actor.org_unit_proximity WHERE to_org = item_object.circ_lib AND from_org = m.copy_circ_lib LIMIT 1 )
54 CASE WHEN m.is_renewal = renewal THEN 128 ELSE 0 END +
55 CASE WHEN m.juvenile_flag IS NOT NULL THEN 64 ELSE 0 END +
56 CASE WHEN m.circ_modifier IS NOT NULL THEN 32 ELSE 0 END +
57 CASE WHEN m.marc_type IS NOT NULL THEN 16 ELSE 0 END +
58 CASE WHEN m.marc_form IS NOT NULL THEN 8 ELSE 0 END +
59 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4 ELSE 0 END +
60 CASE WHEN m.ref_flag IS NOT NULL THEN 2 ELSE 0 END +
61 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 0.5 ELSE 0 END +
62 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 0.5 ELSE 0 END DESC LOOP
64 IF current_mp.circ_modifier IS NOT NULL THEN
65 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL;
68 IF current_mp.marc_type IS NOT NULL THEN
69 IF item_object.circ_as_type IS NOT NULL THEN
70 CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
72 CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
76 IF current_mp.marc_form IS NOT NULL THEN
77 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
80 IF current_mp.marc_vr_format IS NOT NULL THEN
81 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
84 IF current_mp.ref_flag IS NOT NULL THEN
85 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
88 IF current_mp.juvenile_flag IS NOT NULL THEN
89 CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
92 IF current_mp.usr_age_lower_bound IS NOT NULL THEN
93 CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_lower_bound < age(user_object.dob);
96 IF current_mp.usr_age_upper_bound IS NOT NULL THEN
97 CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_upper_bound > age(user_object.dob);
101 -- everything was undefined or matched
102 matchpoint = current_mp;
104 EXIT WHEN matchpoint.id IS NOT NULL;
107 EXIT WHEN current_group.parent IS NULL OR matchpoint.id IS NOT NULL;
109 SELECT INTO current_group * FROM permission.grp_tree WHERE id = current_group.parent;
114 $func$ LANGUAGE plpgsql;