From ff731bb03d87778ae4cb116b8f22d48b4d3949dc Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 15 Sep 2008 18:32:41 +0000 Subject: [PATCH] specify billing/out/etc range by depth, not specific OU (trac ticket #21) git-svn-id: svn://svn.open-ils.org/ILS/trunk@10596 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 26 ++++++++++++-------------- 1 file changed, 12 insertions(+), 14 deletions(-) diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index efffb2e7b4..33eecbb953 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -117,10 +117,10 @@ CREATE TABLE config.circ_matrix_test ( matchpoint INT PRIMARY KEY NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, circulate BOOL NOT NULL DEFAULT TRUE, -- Hard "can't circ" flag requiring an override max_items_out INT, -- Total current active circulations must be less than this, NULL means skip (always pass) - max_overdue INT, -- Total overdue active circulations must be less than this, NULL means skip (always pass) - max_fines NUMERIC(8,2), -- Total fines owed must be less than this, NULL means skip (always pass) - org_unit INT REFERENCES actor.org_unit (id), -- Set to the top OU for the max-out applicability range - script_test TEXT -- filename or javascript source ?? + max_overdue INT, -- Total overdue active circulations must be less than this, NULL means skip (always pass) + max_fines NUMERIC(8,2), -- Total fines owed must be less than this, NULL means skip (always pass) + org_depth INT, -- Set to the top OU for the max-out applicability range + script_test TEXT -- filename or javascript source ?? ); -- Tests for max items out by circ_modifier @@ -298,11 +298,9 @@ BEGIN SELECT INTO circ_test * from config.circ_matrix_test WHERE matchpoint = result.matchpoint; - IF circ_test.org_unit IS NOT NULL THEN - SELECT INTO overdue_orgs ARRAY_ACCUM(id) FROM actor.org_unit_descendants( circ_test.org_unit ); - ELSE - SELECT INTO overdue_orgs ARRAY_ACCUM(id) FROM actor.org_unit; - END IF; + IF circ_test.org_depth IS NOT NULL THEN + SELECT INTO overdue_orgs ARRAY_ACCUM(id) FROM actor.org_unit_descendants( circ_ou, circ_test.org_depth ); + END IF; -- Fail if we couldn't find a set of tests IF result.matchpoint IS NULL THEN @@ -325,7 +323,7 @@ BEGIN SELECT INTO items_out COUNT(*) FROM action.circulation WHERE usr = match_user - AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ) + AND (circ_test.org_depth IS NULL OR (circ_test.org_depth IS NOT NULL AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ))) AND checkin_time IS NULL AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR stop_fines IS NULL); IF items_out >= circ_test.max_items_out THEN @@ -342,7 +340,7 @@ BEGIN FROM action.circulation circ JOIN asset.copy cp ON (cp.id = circ.target_copy) WHERE circ.usr = match_user - AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ) + AND (circ_test.org_depth IS NULL OR (circ_test.org_depth IS NOT NULL AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ))) AND circ.checkin_time IS NULL AND (circ.stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR circ.stop_fines IS NULL) AND cp.circ_modifier = out_by_circ_mod.circ_mod; @@ -359,7 +357,7 @@ BEGIN SELECT INTO items_overdue COUNT(*) FROM action.circulation WHERE usr = match_user - AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ) + AND (circ_test.org_depth IS NULL OR (circ_test.org_depth IS NOT NULL AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ))) AND checkin_time IS NULL AND due_date < NOW() AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE') OR stop_fines IS NULL); @@ -373,14 +371,14 @@ BEGIN -- Fail if the user has a high fine balance IF circ_test.max_fines IS NOT NULL THEN - FOR tmp_groc IN SELECT * FROM money.grocery WHERE usr = match_usr AND xact_finish IS NULL AND billing_location IN ( SELECT * FROM explode_array(overdue_orgs) ) LOOP + FOR tmp_groc IN SELECT * FROM money.grocery WHERE usr = match_usr AND xact_finish IS NULL AND (circ_test.org_depth IS NULL OR (circ_test.org_depth IS NOT NULL AND billing_location IN ( SELECT * FROM explode_array(overdue_orgs) ))) LOOP SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_groc.id AND NOT voided; current_fines = current_fines + COALESCE(tmp_fines, 0.0); SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_groc.id AND NOT voided; current_fines = current_fines - COALESCE(tmp_fines, 0.0); END LOOP; - FOR tmp_circ IN SELECT * FROM action.circulation WHERE usr = match_usr AND xact_finish IS NULL AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ) LOOP + FOR tmp_circ IN SELECT * FROM action.circulation WHERE usr = match_usr AND xact_finish IS NULL AND (circ_test.org_depth IS NULL OR (circ_test.org_depth IS NOT NULL AND circ_lib IN ( SELECT * FROM explode_array(overdue_orgs) ))) LOOP SELECT INTO tmp_fines SUM( amount ) FROM money.billing WHERE xact = tmp_circ.id AND NOT voided; current_fines = current_fines + COALESCE(tmp_fines, 0.0); SELECT INTO tmp_fines SUM( amount ) FROM money.payment WHERE xact = tmp_circ.id AND NOT voided; -- 2.11.0