Stamping long overdue penalty upgrade script
authorMike Rylander <mrylander@gmail.com>
Mon, 16 Sep 2013 16:47:46 +0000 (12:47 -0400)
committerMike Rylander <mrylander@gmail.com>
Mon, 16 Sep 2013 16:47:46 +0000 (12:47 -0400)
Signed-off-by: Mike Rylander <mrylander@gmail.com>
Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/upgrade/0831.schema.long-overdue-penalty.sql [new file with mode: 0644]
Open-ILS/src/sql/Pg/upgrade/YYYY.schema.long-overdue-penalty.sql [deleted file]

index 577e6d6..41e999a 100644 (file)
@@ -91,7 +91,7 @@ CREATE TRIGGER no_overlapping_deps
     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
 
-INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0830', :eg_version); -- ktomita/dbwells
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0831', :eg_version); -- berick/miker
 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0831.schema.long-overdue-penalty.sql b/Open-ILS/src/sql/Pg/upgrade/0831.schema.long-overdue-penalty.sql
new file mode 100644 (file)
index 0000000..7d45edc
--- /dev/null
@@ -0,0 +1,550 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('0831', :eg_version);
+
+-- TODO: check for penalty ID collision before master merge; affects 
+-- config.standing_penalty and actor.calculate_system_penalties
+
+INSERT INTO config.standing_penalty
+    (id, name, label, block_list, staff_alert)
+VALUES (
+    35,
+    'PATRON_EXCEEDS_LONGOVERDUE_COUNT',
+    oils_i18n_gettext(
+        35,
+        'Patron Exceeds Max Long-Overdue Threshold',
+        'csp',
+        'label'
+    ),
+    'CIRC|FULFILL|HOLD|CAPTURE|RENEW',
+    TRUE
+);
+
+
+CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
+DECLARE
+    user_object         actor.usr%ROWTYPE;
+    new_sp_row          actor.usr_standing_penalty%ROWTYPE;
+    existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
+    collections_fines   permission.grp_penalty_threshold%ROWTYPE;
+    max_fines           permission.grp_penalty_threshold%ROWTYPE;
+    max_overdue         permission.grp_penalty_threshold%ROWTYPE;
+    max_items_out       permission.grp_penalty_threshold%ROWTYPE;
+    max_lost            permission.grp_penalty_threshold%ROWTYPE;
+    max_longoverdue     permission.grp_penalty_threshold%ROWTYPE;
+    tmp_grp             INT;
+    items_overdue       INT;
+    items_out           INT;
+    items_lost          INT;
+    items_longoverdue   INT;
+    context_org_list    INT[];
+    current_fines        NUMERIC(8,2) := 0.0;
+    tmp_fines            NUMERIC(8,2);
+    tmp_groc            RECORD;
+    tmp_circ            RECORD;
+    tmp_org             actor.org_unit%ROWTYPE;
+    tmp_penalty         config.standing_penalty%ROWTYPE;
+    tmp_depth           INTEGER;
+BEGIN
+    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
+
+    -- Max fines
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has a high fine balance
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
+
+            IF max_fines.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_fines.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+              FROM  actor.usr_standing_penalty
+              WHERE usr = match_user
+                    AND org_unit = max_fines.org_unit
+                    AND (stop_date IS NULL or stop_date > NOW())
+                    AND standing_penalty = 1;
+
+        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
+
+        SELECT  SUM(f.balance_owed) INTO current_fines
+          FROM  money.materialized_billable_xact_summary f
+                JOIN (
+                    SELECT  r.id
+                      FROM  booking.reservation r
+                      WHERE r.usr = match_user
+                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND xact_finish IS NULL
+                                UNION ALL
+                    SELECT  g.id
+                      FROM  money.grocery g
+                      WHERE g.usr = match_user
+                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
+                            AND xact_finish IS NULL
+                                UNION ALL
+                    SELECT  circ.id
+                      FROM  action.circulation circ
+                      WHERE circ.usr = match_user
+                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND xact_finish IS NULL ) l USING (id);
+
+        IF current_fines >= max_fines.threshold THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_fines.org_unit;
+            new_sp_row.standing_penalty := 1;
+            RETURN NEXT new_sp_row;
+        END IF;
+    END IF;
+
+    -- Start over for max overdue
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has too many overdue items
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+
+            SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
+
+            IF max_overdue.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_overdue.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+              FROM  actor.usr_standing_penalty
+              WHERE usr = match_user
+                    AND org_unit = max_overdue.org_unit
+                    AND (stop_date IS NULL or stop_date > NOW())
+                    AND standing_penalty = 2;
+
+        SELECT  INTO items_overdue COUNT(*)
+          FROM  action.circulation circ
+                JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
+          WHERE circ.usr = match_user
+            AND circ.checkin_time IS NULL
+            AND circ.due_date < NOW()
+            AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
+
+        IF items_overdue >= max_overdue.threshold::INT THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_overdue.org_unit;
+            new_sp_row.standing_penalty := 2;
+            RETURN NEXT new_sp_row;
+        END IF;
+    END IF;
+
+    -- Start over for max out
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has too many checked out items
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+            SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
+
+            IF max_items_out.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+
+    -- Fail if the user has too many items checked out
+    IF max_items_out.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+              FROM  actor.usr_standing_penalty
+              WHERE usr = match_user
+                    AND org_unit = max_items_out.org_unit
+                    AND (stop_date IS NULL or stop_date > NOW())
+                    AND standing_penalty = 3;
+
+        SELECT  INTO items_out COUNT(*)
+          FROM  action.circulation circ
+                JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
+          WHERE circ.usr = match_user
+                AND circ.checkin_time IS NULL
+                AND (circ.stop_fines IN (
+                    SELECT 'MAXFINES'::TEXT
+                    UNION ALL
+                    SELECT 'LONGOVERDUE'::TEXT
+                    UNION ALL
+                    SELECT 'LOST'::TEXT
+                    WHERE 'true' ILIKE
+                    (
+                        SELECT CASE
+                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
+                            ELSE 'false'
+                        END
+                    )
+                    UNION ALL
+                    SELECT 'CLAIMSRETURNED'::TEXT
+                    WHERE 'false' ILIKE
+                    (
+                        SELECT CASE
+                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
+                            ELSE 'false'
+                        END
+                    )
+                    ) OR circ.stop_fines IS NULL)
+                AND xact_finish IS NULL;
+
+           IF items_out >= max_items_out.threshold::INT THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_items_out.org_unit;
+            new_sp_row.standing_penalty := 3;
+            RETURN NEXT new_sp_row;
+           END IF;
+    END IF;
+
+    -- Start over for max lost
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has too many lost items
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+
+            SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
+
+            IF max_lost.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_lost.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+            FROM  actor.usr_standing_penalty
+            WHERE usr = match_user
+                AND org_unit = max_lost.org_unit
+                AND (stop_date IS NULL or stop_date > NOW())
+                AND standing_penalty = 5;
+
+        SELECT  INTO items_lost COUNT(*)
+        FROM  action.circulation circ
+            JOIN  actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
+        WHERE circ.usr = match_user
+            AND circ.checkin_time IS NULL
+            AND (circ.stop_fines = 'LOST')
+            AND xact_finish IS NULL;
+
+        IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_lost.org_unit;
+            new_sp_row.standing_penalty := 5;
+            RETURN NEXT new_sp_row;
+        END IF;
+    END IF;
+
+    -- Start over for max longoverdue
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has too many longoverdue items
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+
+            SELECT * INTO max_longoverdue 
+                FROM permission.grp_penalty_threshold 
+                WHERE grp = tmp_grp AND 
+                    penalty = 35 AND 
+                    org_unit = tmp_org.id;
+
+            IF max_longoverdue.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp 
+                    FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_longoverdue.threshold IS NOT NULL 
+                OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_longoverdue.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+            FROM  actor.usr_standing_penalty
+            WHERE usr = match_user
+                AND org_unit = max_longoverdue.org_unit
+                AND (stop_date IS NULL or stop_date > NOW())
+                AND standing_penalty = 35;
+
+        SELECT INTO items_longoverdue COUNT(*)
+        FROM action.circulation circ
+            JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp 
+                ON (circ.circ_lib = fp.id)
+        WHERE circ.usr = match_user
+            AND circ.checkin_time IS NULL
+            AND (circ.stop_fines = 'LONGOVERDUE')
+            AND xact_finish IS NULL;
+
+        IF items_longoverdue >= max_longoverdue.threshold::INT 
+                AND 0 < max_longoverdue.threshold::INT THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_longoverdue.org_unit;
+            new_sp_row.standing_penalty := 35;
+            RETURN NEXT new_sp_row;
+        END IF;
+    END IF;
+
+
+    -- Start over for collections warning
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Fail if the user has a collections-level fine balance
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
+
+            IF max_fines.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_fines.threshold IS NOT NULL THEN
+
+        RETURN QUERY
+            SELECT  *
+              FROM  actor.usr_standing_penalty
+              WHERE usr = match_user
+                    AND org_unit = max_fines.org_unit
+                    AND (stop_date IS NULL or stop_date > NOW())
+                    AND standing_penalty = 4;
+
+        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
+
+        SELECT  SUM(f.balance_owed) INTO current_fines
+          FROM  money.materialized_billable_xact_summary f
+                JOIN (
+                    SELECT  r.id
+                      FROM  booking.reservation r
+                      WHERE r.usr = match_user
+                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND r.xact_finish IS NULL
+                                UNION ALL
+                    SELECT  g.id
+                      FROM  money.grocery g
+                      WHERE g.usr = match_user
+                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
+                            AND g.xact_finish IS NULL
+                                UNION ALL
+                    SELECT  circ.id
+                      FROM  action.circulation circ
+                      WHERE circ.usr = match_user
+                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND circ.xact_finish IS NULL ) l USING (id);
+
+        IF current_fines >= max_fines.threshold THEN
+            new_sp_row.usr := match_user;
+            new_sp_row.org_unit := max_fines.org_unit;
+            new_sp_row.standing_penalty := 4;
+            RETURN NEXT new_sp_row;
+        END IF;
+    END IF;
+
+    -- Start over for in collections
+    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+
+    -- Remove the in-collections penalty if the user has paid down enough
+    -- This penalty is different, because this code is not responsible for creating 
+    -- new in-collections penalties, only for removing them
+    LOOP
+        tmp_grp := user_object.profile;
+        LOOP
+            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
+
+            IF max_fines.threshold IS NULL THEN
+                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
+            ELSE
+                EXIT;
+            END IF;
+
+            IF tmp_grp IS NULL THEN
+                EXIT;
+            END IF;
+        END LOOP;
+
+        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
+            EXIT;
+        END IF;
+
+        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+
+    END LOOP;
+
+    IF max_fines.threshold IS NOT NULL THEN
+
+        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
+
+        -- first, see if the user had paid down to the threshold
+        SELECT  SUM(f.balance_owed) INTO current_fines
+          FROM  money.materialized_billable_xact_summary f
+                JOIN (
+                    SELECT  r.id
+                      FROM  booking.reservation r
+                      WHERE r.usr = match_user
+                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND r.xact_finish IS NULL
+                                UNION ALL
+                    SELECT  g.id
+                      FROM  money.grocery g
+                      WHERE g.usr = match_user
+                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
+                            AND g.xact_finish IS NULL
+                                UNION ALL
+                    SELECT  circ.id
+                      FROM  action.circulation circ
+                      WHERE circ.usr = match_user
+                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
+                            AND circ.xact_finish IS NULL ) l USING (id);
+
+        IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
+            -- patron has paid down enough
+
+            SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
+
+            IF tmp_penalty.org_depth IS NOT NULL THEN
+
+                -- since this code is not responsible for applying the penalty, it can't 
+                -- guarantee the current context org will match the org at which the penalty 
+                --- was applied.  search up the org tree until we hit the configured penalty depth
+                SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
+                SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
+
+                WHILE tmp_depth >= tmp_penalty.org_depth LOOP
+
+                    RETURN QUERY
+                        SELECT  *
+                          FROM  actor.usr_standing_penalty
+                          WHERE usr = match_user
+                                AND org_unit = tmp_org.id
+                                AND (stop_date IS NULL or stop_date > NOW())
+                                AND standing_penalty = 30;
+
+                    IF tmp_org.parent_ou IS NULL THEN
+                        EXIT;
+                    END IF;
+
+                    SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
+                    SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
+                END LOOP;
+
+            ELSE
+
+                -- no penalty depth is defined, look for exact matches
+
+                RETURN QUERY
+                    SELECT  *
+                      FROM  actor.usr_standing_penalty
+                      WHERE usr = match_user
+                            AND org_unit = max_fines.org_unit
+                            AND (stop_date IS NULL or stop_date > NOW())
+                            AND standing_penalty = 30;
+            END IF;
+    
+        END IF;
+
+    END IF;
+
+    RETURN;
+END;
+$func$ LANGUAGE plpgsql;
+
+
+COMMIT;
diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.long-overdue-penalty.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.long-overdue-penalty.sql
deleted file mode 100644 (file)
index f7cf9ed..0000000
+++ /dev/null
@@ -1,550 +0,0 @@
-BEGIN;
-
--- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
-
--- TODO: check for penalty ID collision before master merge; affects 
--- config.standing_penalty and actor.calculate_system_penalties
-
-INSERT INTO config.standing_penalty
-    (id, name, label, block_list, staff_alert)
-VALUES (
-    35,
-    'PATRON_EXCEEDS_LONGOVERDUE_COUNT',
-    oils_i18n_gettext(
-        35,
-        'Patron Exceeds Max Long-Overdue Threshold',
-        'csp',
-        'label'
-    ),
-    'CIRC|FULFILL|HOLD|CAPTURE|RENEW',
-    TRUE
-);
-
-
-CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
-DECLARE
-    user_object         actor.usr%ROWTYPE;
-    new_sp_row          actor.usr_standing_penalty%ROWTYPE;
-    existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
-    collections_fines   permission.grp_penalty_threshold%ROWTYPE;
-    max_fines           permission.grp_penalty_threshold%ROWTYPE;
-    max_overdue         permission.grp_penalty_threshold%ROWTYPE;
-    max_items_out       permission.grp_penalty_threshold%ROWTYPE;
-    max_lost            permission.grp_penalty_threshold%ROWTYPE;
-    max_longoverdue     permission.grp_penalty_threshold%ROWTYPE;
-    tmp_grp             INT;
-    items_overdue       INT;
-    items_out           INT;
-    items_lost          INT;
-    items_longoverdue   INT;
-    context_org_list    INT[];
-    current_fines        NUMERIC(8,2) := 0.0;
-    tmp_fines            NUMERIC(8,2);
-    tmp_groc            RECORD;
-    tmp_circ            RECORD;
-    tmp_org             actor.org_unit%ROWTYPE;
-    tmp_penalty         config.standing_penalty%ROWTYPE;
-    tmp_depth           INTEGER;
-BEGIN
-    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
-
-    -- Max fines
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has a high fine balance
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
-
-            IF max_fines.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_fines.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-              FROM  actor.usr_standing_penalty
-              WHERE usr = match_user
-                    AND org_unit = max_fines.org_unit
-                    AND (stop_date IS NULL or stop_date > NOW())
-                    AND standing_penalty = 1;
-
-        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
-
-        SELECT  SUM(f.balance_owed) INTO current_fines
-          FROM  money.materialized_billable_xact_summary f
-                JOIN (
-                    SELECT  r.id
-                      FROM  booking.reservation r
-                      WHERE r.usr = match_user
-                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND xact_finish IS NULL
-                                UNION ALL
-                    SELECT  g.id
-                      FROM  money.grocery g
-                      WHERE g.usr = match_user
-                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
-                            AND xact_finish IS NULL
-                                UNION ALL
-                    SELECT  circ.id
-                      FROM  action.circulation circ
-                      WHERE circ.usr = match_user
-                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND xact_finish IS NULL ) l USING (id);
-
-        IF current_fines >= max_fines.threshold THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_fines.org_unit;
-            new_sp_row.standing_penalty := 1;
-            RETURN NEXT new_sp_row;
-        END IF;
-    END IF;
-
-    -- Start over for max overdue
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has too many overdue items
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-
-            SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
-
-            IF max_overdue.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_overdue.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-              FROM  actor.usr_standing_penalty
-              WHERE usr = match_user
-                    AND org_unit = max_overdue.org_unit
-                    AND (stop_date IS NULL or stop_date > NOW())
-                    AND standing_penalty = 2;
-
-        SELECT  INTO items_overdue COUNT(*)
-          FROM  action.circulation circ
-                JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
-          WHERE circ.usr = match_user
-            AND circ.checkin_time IS NULL
-            AND circ.due_date < NOW()
-            AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
-
-        IF items_overdue >= max_overdue.threshold::INT THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_overdue.org_unit;
-            new_sp_row.standing_penalty := 2;
-            RETURN NEXT new_sp_row;
-        END IF;
-    END IF;
-
-    -- Start over for max out
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has too many checked out items
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-            SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
-
-            IF max_items_out.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-
-    -- Fail if the user has too many items checked out
-    IF max_items_out.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-              FROM  actor.usr_standing_penalty
-              WHERE usr = match_user
-                    AND org_unit = max_items_out.org_unit
-                    AND (stop_date IS NULL or stop_date > NOW())
-                    AND standing_penalty = 3;
-
-        SELECT  INTO items_out COUNT(*)
-          FROM  action.circulation circ
-                JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
-          WHERE circ.usr = match_user
-                AND circ.checkin_time IS NULL
-                AND (circ.stop_fines IN (
-                    SELECT 'MAXFINES'::TEXT
-                    UNION ALL
-                    SELECT 'LONGOVERDUE'::TEXT
-                    UNION ALL
-                    SELECT 'LOST'::TEXT
-                    WHERE 'true' ILIKE
-                    (
-                        SELECT CASE
-                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
-                            ELSE 'false'
-                        END
-                    )
-                    UNION ALL
-                    SELECT 'CLAIMSRETURNED'::TEXT
-                    WHERE 'false' ILIKE
-                    (
-                        SELECT CASE
-                            WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
-                            ELSE 'false'
-                        END
-                    )
-                    ) OR circ.stop_fines IS NULL)
-                AND xact_finish IS NULL;
-
-           IF items_out >= max_items_out.threshold::INT THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_items_out.org_unit;
-            new_sp_row.standing_penalty := 3;
-            RETURN NEXT new_sp_row;
-           END IF;
-    END IF;
-
-    -- Start over for max lost
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has too many lost items
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-
-            SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
-
-            IF max_lost.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_lost.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-            FROM  actor.usr_standing_penalty
-            WHERE usr = match_user
-                AND org_unit = max_lost.org_unit
-                AND (stop_date IS NULL or stop_date > NOW())
-                AND standing_penalty = 5;
-
-        SELECT  INTO items_lost COUNT(*)
-        FROM  action.circulation circ
-            JOIN  actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
-        WHERE circ.usr = match_user
-            AND circ.checkin_time IS NULL
-            AND (circ.stop_fines = 'LOST')
-            AND xact_finish IS NULL;
-
-        IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_lost.org_unit;
-            new_sp_row.standing_penalty := 5;
-            RETURN NEXT new_sp_row;
-        END IF;
-    END IF;
-
-    -- Start over for max longoverdue
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has too many longoverdue items
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-
-            SELECT * INTO max_longoverdue 
-                FROM permission.grp_penalty_threshold 
-                WHERE grp = tmp_grp AND 
-                    penalty = 35 AND 
-                    org_unit = tmp_org.id;
-
-            IF max_longoverdue.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp 
-                    FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_longoverdue.threshold IS NOT NULL 
-                OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_longoverdue.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-            FROM  actor.usr_standing_penalty
-            WHERE usr = match_user
-                AND org_unit = max_longoverdue.org_unit
-                AND (stop_date IS NULL or stop_date > NOW())
-                AND standing_penalty = 35;
-
-        SELECT INTO items_longoverdue COUNT(*)
-        FROM action.circulation circ
-            JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp 
-                ON (circ.circ_lib = fp.id)
-        WHERE circ.usr = match_user
-            AND circ.checkin_time IS NULL
-            AND (circ.stop_fines = 'LONGOVERDUE')
-            AND xact_finish IS NULL;
-
-        IF items_longoverdue >= max_longoverdue.threshold::INT 
-                AND 0 < max_longoverdue.threshold::INT THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_longoverdue.org_unit;
-            new_sp_row.standing_penalty := 35;
-            RETURN NEXT new_sp_row;
-        END IF;
-    END IF;
-
-
-    -- Start over for collections warning
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Fail if the user has a collections-level fine balance
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
-
-            IF max_fines.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_fines.threshold IS NOT NULL THEN
-
-        RETURN QUERY
-            SELECT  *
-              FROM  actor.usr_standing_penalty
-              WHERE usr = match_user
-                    AND org_unit = max_fines.org_unit
-                    AND (stop_date IS NULL or stop_date > NOW())
-                    AND standing_penalty = 4;
-
-        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
-
-        SELECT  SUM(f.balance_owed) INTO current_fines
-          FROM  money.materialized_billable_xact_summary f
-                JOIN (
-                    SELECT  r.id
-                      FROM  booking.reservation r
-                      WHERE r.usr = match_user
-                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND r.xact_finish IS NULL
-                                UNION ALL
-                    SELECT  g.id
-                      FROM  money.grocery g
-                      WHERE g.usr = match_user
-                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
-                            AND g.xact_finish IS NULL
-                                UNION ALL
-                    SELECT  circ.id
-                      FROM  action.circulation circ
-                      WHERE circ.usr = match_user
-                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND circ.xact_finish IS NULL ) l USING (id);
-
-        IF current_fines >= max_fines.threshold THEN
-            new_sp_row.usr := match_user;
-            new_sp_row.org_unit := max_fines.org_unit;
-            new_sp_row.standing_penalty := 4;
-            RETURN NEXT new_sp_row;
-        END IF;
-    END IF;
-
-    -- Start over for in collections
-    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-
-    -- Remove the in-collections penalty if the user has paid down enough
-    -- This penalty is different, because this code is not responsible for creating 
-    -- new in-collections penalties, only for removing them
-    LOOP
-        tmp_grp := user_object.profile;
-        LOOP
-            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
-
-            IF max_fines.threshold IS NULL THEN
-                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
-            ELSE
-                EXIT;
-            END IF;
-
-            IF tmp_grp IS NULL THEN
-                EXIT;
-            END IF;
-        END LOOP;
-
-        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
-            EXIT;
-        END IF;
-
-        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-
-    END LOOP;
-
-    IF max_fines.threshold IS NOT NULL THEN
-
-        SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
-
-        -- first, see if the user had paid down to the threshold
-        SELECT  SUM(f.balance_owed) INTO current_fines
-          FROM  money.materialized_billable_xact_summary f
-                JOIN (
-                    SELECT  r.id
-                      FROM  booking.reservation r
-                      WHERE r.usr = match_user
-                            AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND r.xact_finish IS NULL
-                                UNION ALL
-                    SELECT  g.id
-                      FROM  money.grocery g
-                      WHERE g.usr = match_user
-                            AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
-                            AND g.xact_finish IS NULL
-                                UNION ALL
-                    SELECT  circ.id
-                      FROM  action.circulation circ
-                      WHERE circ.usr = match_user
-                            AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
-                            AND circ.xact_finish IS NULL ) l USING (id);
-
-        IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
-            -- patron has paid down enough
-
-            SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
-
-            IF tmp_penalty.org_depth IS NOT NULL THEN
-
-                -- since this code is not responsible for applying the penalty, it can't 
-                -- guarantee the current context org will match the org at which the penalty 
-                --- was applied.  search up the org tree until we hit the configured penalty depth
-                SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
-                SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
-
-                WHILE tmp_depth >= tmp_penalty.org_depth LOOP
-
-                    RETURN QUERY
-                        SELECT  *
-                          FROM  actor.usr_standing_penalty
-                          WHERE usr = match_user
-                                AND org_unit = tmp_org.id
-                                AND (stop_date IS NULL or stop_date > NOW())
-                                AND standing_penalty = 30;
-
-                    IF tmp_org.parent_ou IS NULL THEN
-                        EXIT;
-                    END IF;
-
-                    SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
-                    SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
-                END LOOP;
-
-            ELSE
-
-                -- no penalty depth is defined, look for exact matches
-
-                RETURN QUERY
-                    SELECT  *
-                      FROM  actor.usr_standing_penalty
-                      WHERE usr = match_user
-                            AND org_unit = max_fines.org_unit
-                            AND (stop_date IS NULL or stop_date > NOW())
-                            AND standing_penalty = 30;
-            END IF;
-    
-        END IF;
-
-    END IF;
-
-    RETURN;
-END;
-$func$ LANGUAGE plpgsql;
-
-
-COMMIT;