Testing - Circ Policy Script

DROP TABLE IF EXISTS rogan.test_items;

CREATE TABLE rogan.test_items AS
        SELECT DISTINCT ON (ac.circ_modifier, paou.shortname) ac.id, ac.circ_lib, paou.id as p_aou, ac.circ_modifier
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON acl.id = ac.LOCATION
        JOIN actor.org_unit aou ON aou.id = ac.circ_lib
        JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        WHERE ac.deleted = FALSE AND ac.status in (0,7) AND ac.circulate = TRUE AND acl.circulate = TRUE AND aou.ou_type = 3
        UNION ALL
        SELECT DISTINCT ON (ac.circ_modifier, paou.shortname) ac.id, ac.circ_lib, paou.id as p_aou, ac.circ_modifier
        FROM asset.COPY ac
        JOIN asset.copy_location acl ON acl.id = ac.LOCATION
        JOIN actor.org_unit aou ON aou.id = ac.circ_lib
        JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        WHERE ac.deleted = FALSE AND ac.status in (0,7) AND ac.circulate = TRUE AND acl.circulate = TRUE AND aou.ou_type = 5
        ;

DROP TABLE IF EXISTS rogan.test_patrons;


CREATE TABLE rogan.test_patrons AS
        SELECT DISTINCT ON (au.profile, paou.shortname) au.id, au.home_ou, paou.id AS p_aou, au.profile
        FROM actor.usr au
        LEFT JOIN actor.usr_standing_penalty pen ON pen.usr = au.id
        JOIN permission.grp_tree pgt ON pgt.id = au.profile
        JOIN actor.org_unit aou ON aou.id = au.home_ou
        JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        -- JOIN money.usr_summary mus on mus.usr = au.id
        WHERE au.deleted = FALSE AND au.barred = FALSE AND au.expire_date > NOW() + INTERVAL '1 day'
        -- AND mus.balance_owed = 0.0
        AND au.active = TRUE AND pen.id IS NULL AND pgt.parent = 2 AND aou.ou_type = 3 AND pgt.NAME NOT ILIKE '%beta%'
                UNION ALL
        SELECT DISTINCT ON (au.profile, paou.shortname) au.id, au.home_ou,paou.id AS p_aou, au.profile
        FROM actor.usr au
        LEFT JOIN actor.usr_standing_penalty pen ON pen.usr = au.id
        JOIN permission.grp_tree pgt ON pgt.id = au.profile
        JOIN actor.org_unit aou ON aou.id = au.home_ou
        JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        -- JOIN money.usr_summary mus on mus.usr = au.id
        WHERE au.deleted = FALSE AND au.barred = FALSE AND au.expire_date > NOW() + INTERVAL '1 day'
        -- AND mus.balance_owed = 0.0
        AND au.active = TRUE AND pen.id IS NULL AND pgt.parent = 2 AND aou.ou_type = 5 AND pgt.NAME NOT ILIKE '%beta%'
        ;


DROP TABLE IF EXISTS rogan.test_staff;

CREATE TABLE rogan.test_staff AS
        SELECT DISTINCT ON (au.profile, au.home_ou) au.id
        FROM actor.usr au
        LEFT JOIN actor.usr_standing_penalty pen ON pen.usr = au.id
        WHERE au.deleted = FALSE AND au.barred = FALSE AND au.expire_date > NOW() + INTERVAL '1 day' AND pen.id IS NULL
        AND au.profile IN (155,156,4,157,158,268,5,179,247,378,12,185,181,3,175)
        ;

DROP TABLE IF EXISTS rogan.test_orgs;

CREATE TABLE rogan.test_orgs AS
        SELECT DISTINCT ON (paou.shortname) aou.id, aou.parent_ou as p_aou
        FROM actor.org_unit aou JOIN actor.org_unit paou ON paou.id = aou.parent_ou WHERE aou.shortname ilike '%-BK%'
        UNION ALL
        SELECT DISTINCT ON (paou.shortname) aou.id, aou.parent_ou as p_aou
        FROM actor.org_unit aou JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        WHERE aou.shortname not ilike '%-BK%' and aou.shortname not in ('CAL-XP-SR','BCL-BDC')
        UNION ALL
        SELECT aou.id, aou.parent_ou as p_aou
        FROM actor.org_unit aou JOIN actor.org_unit paou ON paou.id = aou.parent_ou
        WHERE aou.shortname in ('CAL-XP-SR','BCL-BDC')
        ;
/*
CREATE TABLE rogan.test_orgs AS
        SELECT aou.id, aou.parent_ou AS p_aou
        FROM actor.org_unit aou where aou.id in
        (154,159,132,139,6,104,111,109,187,178,180,114,172,174,120,
        122,141,143,145,146,192,193,161,164,195,199,200,4,107,167,169,127,123);
*/

DROP TABLE IF EXISTS rogan.testing;

CREATE TABLE rogan.testing AS
SELECT DISTINCT on (o.id, i.circ_modifier, p.profile) o.id AS "org_unit", i.id AS "item", p.id AS "patron"
FROM rogan.test_items i
JOIN rogan.test_patrons p ON p.p_aou = i.p_aou
JOIN rogan.test_orgs o ON o.p_aou = i.p_aou;

ALTER TABLE rogan.testing ADD COLUMN id serial;

ALTER TABLE rogan.testing ADD COLUMN result INTEGER;

DO $$
DECLARE
   min_id       INT;
   max_id       INT;
   this_id      INT;
   item         INT;
   patron       INT;
   org_unit     INT;
   resultx      INT;
BEGIN

SELECT MIN(a.id) INTO min_id FROM rogan.testing a;
SELECT MAX(a.id) INTO max_id FROM rogan.testing a;
this_id := min_id;

WHILE this_id <= max_id
LOOP
      SELECT t.item INTO item FROM rogan.testing t WHERE t.id = this_id;
      SELECT t.org_unit INTO org_unit FROM rogan.testing t WHERE t.id = this_id;
      SELECT t. patron INTO patron FROM rogan.testing t WHERE t.id = this_id;
      SELECT a.find_circ_matrix_matchpoint INTO resultx FROM
        (SELECT * FROM rogan.find_circ_matrix_matchpoint(org_unit,item,patron,FALSE)) a;
          UPDATE rogan.testing SET result = resultx WHERE id = this_id;
          this_id := this_id + 1;
END LOOP;
END$$

SELECT DISTINCT aou.shortname AS "Library", ac.circ_modifier AS "Circ Modifier", pgt.NAME AS "Patron Profile",
coalesce(duration.NAME,'circulation denied') AS "Circ Duration", recure.NAME AS "Recurring Fine Rule", maxfine.NAME AS "Max Fine",
t.result as "circ matchpoint"
FROM rogan.testing t
JOIN actor.org_unit aou ON aou.id = t.org_unit
JOIN asset.COPY ac ON ac.id = t.item
JOIN actor.usr au ON au.id = t.patron
JOIN permission.grp_tree pgt ON pgt.id = au.profile
JOIN config.circ_matrix_matchpoint cmm ON cmm.id = t.result
LEFT JOIN config.rule_recurring_fine recure ON recure.id = cmm.recurring_fine_rule
LEFT JOIN config.rule_max_fine maxfine ON maxfine.id = cmm.max_fine_rule
LEFT JOIN config.rule_circ_duration duration ON duration.id = cmm.duration_rule
WHERE ac.circ_modifier IS NOT NULL AND cmm.active = TRUE
-- and aou.shortname ilike 'acl%' and ac.circ_modifier ilike '%video%'
ORDER BY 1, 3, 2
;




-- ----------------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------
-- ------------------------ let's modify the helper app from 100 ... sql and have it return just the matchpoint
-- ----------------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------


select * from rogan.find_circ_matrix_matchpoint(127,4938102,2024338,FALSE);

DROP FUNCTION rogan.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) ;

CREATE OR REPLACE FUNCTION rogan.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
RETURNS INTEGER AS $func$
DECLARE
    item_object asset.COPY%ROWTYPE;
    user_object actor.usr%ROWTYPE;
    cn_object       asset.call_number%ROWTYPE;
    rec_descriptor  metabib.rec_descriptor%ROWTYPE;
    cur_matchpoint  config.circ_matrix_matchpoint%ROWTYPE;
    matchpoint      config.circ_matrix_matchpoint%ROWTYPE;
    weights         config.circ_matrix_weights%ROWTYPE;
    user_age        INTERVAL;
    my_item_age     INTERVAL;
    denominator     NUMERIC(6,2);
    row_list        INT[];
    result          ACTION.found_circ_matrix_matchpoint;
BEGIN
    -- Assume failure
    result.success = FALSE;

    SELECT INTO item_object * FROM asset.COPY   WHERE id = match_item;
    SELECT INTO user_object * FROM actor.usr    WHERE id = match_user;

    -- Fetch useful data
    SELECT INTO cn_object       * FROM asset.call_number        WHERE id = item_object.call_number;
    SELECT INTO rec_descriptor  * FROM metabib.rec_descriptor   WHERE record = cn_object.record;

    -- Pre-generate this so we only calc it once
    IF user_object.dob IS NOT NULL THEN
        SELECT INTO user_age AGE(user_object.dob);
    END IF;

    -- Ditto
    SELECT INTO my_item_age AGE(COALESCE(item_object.active_date, NOW()));

    -- Grab the closest set circ weight setting.
    SELECT INTO weights cw.*
      FROM config.weight_assoc wa
           JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
           JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
      WHERE active
      ORDER BY d.distance
      LIMIT 1;

    -- No weights? Bad admin! Defaults to handle that anyway.
    IF weights.id IS NULL THEN
        weights.grp                 := 11.0;
        weights.org_unit            := 10.0;
        weights.circ_modifier       := 5.0;
        weights.copy_location       := 5.0;
        weights.marc_type           := 4.0;
        weights.marc_form           := 3.0;
        weights.marc_bib_level      := 2.0;
        weights.marc_vr_format      := 2.0;
        weights.copy_circ_lib       := 8.0;
        weights.copy_owning_lib     := 8.0;
        weights.user_home_ou        := 8.0;
        weights.ref_flag            := 1.0;
        weights.juvenile_flag       := 6.0;
        weights.is_renewal          := 7.0;
        weights.usr_age_lower_bound := 0.0;
        weights.usr_age_upper_bound := 0.0;
        weights.item_age            := 0.0;
    END IF;

    -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
    -- If you break your org tree with funky parenting this may be wrong
    -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
    -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
    WITH all_distance(distance) AS (
            SELECT depth AS distance FROM actor.org_unit_type
        UNION
            SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
        )
    SELECT INTO denominator MAX(distance) + 1 FROM all_distance;

    -- Loop over all the potential matchpoints
    FOR cur_matchpoint IN
        SELECT m.*
          FROM  config.circ_matrix_matchpoint m
                /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
                /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
                LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
                LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
                LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou  ) uhoua ON m.user_home_ou = uhoua.id
          WHERE m.active
                -- Permission Groups
             -- AND (m.grp                      IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
                -- Org Units
             -- AND (m.org_unit                 IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
                AND (m.copy_owning_lib          IS NULL OR cnoua.id IS NOT NULL)
                AND (m.copy_circ_lib            IS NULL OR iooua.id IS NOT NULL)
                AND (m.user_home_ou             IS NULL OR uhoua.id IS NOT NULL)
                -- Circ Type
                AND (m.is_renewal               IS NULL OR m.is_renewal = renewal)
                -- Static User Checks
                AND (m.juvenile_flag            IS NULL OR m.juvenile_flag = user_object.juvenile)
                AND (m.usr_age_lower_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
                AND (m.usr_age_upper_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
                -- Static Item Checks
                AND (m.circ_modifier            IS NULL OR m.circ_modifier = item_object.circ_modifier)
                AND (m.copy_location            IS NULL OR m.copy_location = item_object.LOCATION)
                AND (m.marc_type                IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
                AND (m.marc_form                IS NULL OR m.marc_form = rec_descriptor.item_form)
                AND (m.marc_bib_level           IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
                AND (m.marc_vr_format           IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
                AND (m.ref_flag                 IS NULL OR m.ref_flag = item_object.ref)
                AND (m.item_age                 IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
          ORDER BY
                -- Permission Groups
                CASE WHEN upgad.distance        IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
                -- Org Units
                CASE WHEN ctoua.distance        IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN cnoua.distance        IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN iooua.distance        IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
                CASE WHEN uhoua.distance        IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
                -- Circ Type                    -- Note: 4^x is equiv to 2^(2*x)
                CASE WHEN m.is_renewal          IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
                -- Static User Checks
                CASE WHEN m.juvenile_flag       IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
                CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
                CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
                -- Static Item Checks
                CASE WHEN m.circ_modifier       IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
                CASE WHEN m.copy_location       IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
                CASE WHEN m.marc_type           IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
                CASE WHEN m.marc_form           IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
                CASE WHEN m.marc_vr_format      IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
                CASE WHEN m.ref_flag            IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
                -- Item age has a slight adjustment to weight based on value.
                -- This should ensure that a shorter age limit comes first when all else is equal.
                -- NOTE: This assumes that intervals will normally be in days.
                CASE WHEN m.item_age            IS NOT NULL THEN 4^weights.item_age - 1 + 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
                -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
                -- This prevents "we changed the table order by updating a rule, and we started getting different results"
                m.id LOOP

        -- Record the full matching row list
        row_list := row_list || cur_matchpoint.id;

        -- No matchpoint yet?
        IF matchpoint.id IS NULL THEN
            -- Take the entire matchpoint as a starting point
            matchpoint := cur_matchpoint;
            CONTINUE; -- No need to look at this row any more.
        END IF;

        -- Incomplete matchpoint?
        IF matchpoint.circulate IS NULL THEN
            matchpoint.circulate := cur_matchpoint.circulate;
        END IF;
        IF matchpoint.duration_rule IS NULL THEN
            matchpoint.duration_rule := cur_matchpoint.duration_rule;
        END IF;
        IF matchpoint.recurring_fine_rule IS NULL THEN
            matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
        END IF;
        IF matchpoint.max_fine_rule IS NULL THEN
            matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
        END IF;
        IF matchpoint.hard_due_date IS NULL THEN
            matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
        END IF;
        IF matchpoint.total_copy_hold_ratio IS NULL THEN
            matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
        END IF;
        IF matchpoint.available_copy_hold_ratio IS NULL THEN
            matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
        END IF;
        IF matchpoint.renewals IS NULL THEN
            matchpoint.renewals := cur_matchpoint.renewals;
        END IF;
        IF matchpoint.grace_period IS NULL THEN
            matchpoint.grace_period := cur_matchpoint.grace_period;
        END IF;
    END LOOP;

    -- Check required fields
    IF matchpoint.circulate             IS NOT NULL AND
       matchpoint.duration_rule         IS NOT NULL AND
       matchpoint.recurring_fine_rule   IS NOT NULL AND
       matchpoint.max_fine_rule         IS NOT NULL THEN
        -- All there? We have a completed match.
        result.success := TRUE;
    END IF;

    -- Include the assembled matchpoint, even if it isn't complete
    result.matchpoint := matchpoint;

    -- Include (for debugging) the full list of matching rows
    result.buildrows := row_list;

    -- Hand the result back to caller
    -- RETURN result;
    RETURN matchpoint.id;

END;
$func$ LANGUAGE plpgsql;