Circ Matrix Report

-- really verbose alternative for getting the rules for in-db holds rules
-- rather than just select * config.hold_matrix_matchpoint
-- almost a necessity for a consortium with varying rules or any library
-- with intricate polices because in-db holds policies are much easier to
-- handle via SQL than through the staff client
-- script has IDs for admins and look ups to names for easy reference
-- tested on 2.1 and 2.4
-- updated to include the circ limits information on 2.4.4 2013-12-19
-- this tracks circ limits information for circ limits only not copy locations or MARC attributes
-- (c) Rogan Hamby, 2013, roganhamby.com
-- Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License



SELECT ccm.id AS "Circ Matrix Matchpoint ID", ccm.active AS "Active", ccm.org_unit AS "Circ Org Unit", aou.shortname AS "Circ Short Org Unit Name",
aou.parent_ou AS "Parent Org Unit", aou2.shortname "Parent Org Unit Short Name", pgt.id AS "Permission Group ID",
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier", ccm.marc_type AS "MARC Type", ccm.marc_form AS "MARC Form",
ccm.marc_vr_format AS "MARC Video Recording Format", ccm.ref_flag AS "Reference Boolean", ccm.is_renewal AS "Renewable Boolean",
ccm.usr_age_lower_bound AS "Lower Age Bound", ccm.usr_age_upper_bound AS "Upper Age Bound", ccm.juvenile_flag AS "Juvenile",
ccm.duration_rule AS "Duration Rule", dur.NAME AS "Duration Name", ccm.max_fine_rule AS "Max Fine Rule", maxf.NAME AS "Max Fine Name",
ccm.recurring_fine_rule AS "Recurring Fine Rule", recur.NAME AS "Recurring Fine Name", ccm.total_copy_hold_ratio AS "Total Copy Hold Ratio",
ccm.available_copy_hold_ratio AS "Available Copy Hold Ratio",
ccm.hard_due_date AS "Hard Due Date ID", hdd.NAME AS "Hard Due Date Name",
ccm.copy_circ_lib AS "Copy Circ Lib ID", aou3.shortname AS "Copy Circ Lib Shortname",
ccm.copy_owning_lib AS "Copy Owning Lib ID", aou4.shortname AS "Copy Owning Lib Shortname",
ccm.renewals AS "Renewals",
ccm.user_home_ou AS "User Home Lib ID", aou5.shortname AS "User Home Lib Shortname",
ccm.grace_period AS "Grace Period", ccm.marc_bib_level AS "MARC Bib Level",
lsm.id AS "Circ Limit Set Map ID", cls.owning_lib AS "Circ Limit Set Owning Lib", lsm.limit_set as "Circ Limit Set ID",
aoucls.shortname AS "Circ Limit Set Owning Lib Name",
cls.items_out "Circ Limit Set Items Out", cls.depth AS "Circ Limit Set Depth", cls.GLOBAL AS "Circ Limit Set Global",
scmm.circ_mod AS "Circ Limit Set Circ Mod", scmm.id as "Circ Mod Map ID"
FROM config.circ_matrix_matchpoint ccm
LEFT JOIN actor.org_unit aou ON aou.id = ccm.org_unit
LEFT JOIN actor.org_unit aou2 ON aou2.id = aou.parent_ou
LEFT JOIN permission.grp_tree pgt ON pgt.id = ccm.grp
LEFT JOIN config.rule_circ_duration dur ON dur.id = ccm.duration_rule
LEFT JOIN config.rule_max_fine maxf ON maxf.id = ccm.max_fine_rule
LEFT JOIN config.rule_recurring_fine recur ON recur.id = ccm.recurring_fine_rule
LEFT JOIN config.hard_due_date hdd ON hdd.id = ccm.hard_due_date
LEFT JOIN actor.org_unit aou3 ON aou3.id = ccm.copy_circ_lib
LEFT JOIN actor.org_unit aou4 ON aou4.id = ccm.copy_owning_lib
LEFT JOIN actor.org_unit aou5 ON aou5.id = ccm.user_home_ou
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = ccm.id
LEFT JOIN config.circ_limit_set cls ON cls.id = lsm.limit_set
LEFT JOIN config.circ_limit_set_circ_mod_map scmm ON scmm.limit_set = cls.id
LEFT JOIN actor.org_unit aoucls ON aoucls.id = cls.owning_lib
;


-- shorter with circ

SELECT ccm.id AS "Circ Matrix Matchpoint ID", ccm.active AS "Active", ccm.circulate as "Circulate",
ccm.org_unit AS "Circ Org Unit", aou.shortname AS "Circ Short Org Unit Name",
aou.parent_ou AS "Parent Org Unit", aou2.shortname "Parent Org Unit Short Name", pgt.id AS "Permission Group ID",
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier",
ccm.duration_rule AS "Duration Rule", dur.NAME AS "Duration Name", ccm.max_fine_rule AS "Max Fine Rule", maxf.NAME AS "Max Fine Name",
ccm.recurring_fine_rule AS "Recurring Fine Rule", recur.NAME AS "Recurring Fine Name",
ccm.user_home_ou AS "User Home Lib ID", aou5.shortname AS "User Home Lib Shortname",
lsm.id AS "Circ Limit Set Map ID", cls.owning_lib AS "Circ Limit Set Owning Lib", lsm.limit_set AS "Circ Limit Set ID",
aoucls.shortname AS "Circ Limit Set Owning Lib Name",
cls.items_out "Circ Limit Set Items Out", cls.depth AS "Circ Limit Set Depth", cls.GLOBAL AS "Circ Limit Set Global",
scmm.circ_mod AS "Circ Limit Set Circ Mod", scmm.id AS "Circ Mod Map ID"
FROM config.circ_matrix_matchpoint ccm
LEFT JOIN actor.org_unit aou ON aou.id = ccm.org_unit
LEFT JOIN actor.org_unit aou2 ON aou2.id = aou.parent_ou
LEFT JOIN permission.grp_tree pgt ON pgt.id = ccm.grp
LEFT JOIN config.rule_circ_duration dur ON dur.id = ccm.duration_rule
LEFT JOIN config.rule_max_fine maxf ON maxf.id = ccm.max_fine_rule
LEFT JOIN config.rule_recurring_fine recur ON recur.id = ccm.recurring_fine_rule
LEFT JOIN config.hard_due_date hdd ON hdd.id = ccm.hard_due_date
LEFT JOIN actor.org_unit aou3 ON aou3.id = ccm.copy_circ_lib
LEFT JOIN actor.org_unit aou4 ON aou4.id = ccm.copy_owning_lib
LEFT JOIN actor.org_unit aou5 ON aou5.id = ccm.user_home_ou
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = ccm.id
LEFT JOIN config.circ_limit_set cls ON cls.id = lsm.limit_set
LEFT JOIN config.circ_limit_set_circ_mod_map scmm ON scmm.limit_set = cls.id
LEFT JOIN actor.org_unit aoucls ON aoucls.id = cls.owning_lib
;


-- for troubleshooting :


SELECT ccm.id AS "Circ Matrix Matchpoint ID", aou.shortname AS "Circ Short Org Unit Name",
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier", ccm.circulate as "Circulate Boolean",
ccm.is_renewal AS "Renewable Boolean", lsm.id AS "Circ Limit Set Map ID", cls.id as "Circ Limit Set ID",
aoucls.shortname AS "Circ Limit Set Own", cls.items_out, scmm.id as "Circ Mod Map ID", scmm.circ_mod AS "Limit Set Circ Mod"
FROM config.circ_matrix_matchpoint ccm
LEFT JOIN actor.org_unit aou ON aou.id = ccm.org_unit
LEFT JOIN actor.org_unit aou2 ON aou2.id = aou.parent_ou
LEFT JOIN permission.grp_tree pgt ON pgt.id = ccm.grp
LEFT JOIN config.rule_circ_duration dur ON dur.id = ccm.duration_rule
LEFT JOIN config.rule_max_fine maxf ON maxf.id = ccm.max_fine_rule
LEFT JOIN config.rule_recurring_fine recur ON recur.id = ccm.recurring_fine_rule
LEFT JOIN config.hard_due_date hdd ON hdd.id = ccm.hard_due_date
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = ccm.id
LEFT JOIN config.circ_limit_set cls ON cls.id = lsm.limit_set
LEFT JOIN config.circ_limit_set_circ_mod_map scmm ON scmm.limit_set = cls.id
LEFT JOIN actor.org_unit aoucls ON aoucls.id = cls.owning_lib
where aou.id in (108,111,109,187) and pgt.name = 'Juvenile' and ccm.circ_modifier in ('VIDEO',NULL);