Verbose 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 cmm.id AS "circ matrix match id", cmm.is_renewal, org.shortname AS "org unit", tree.NAME AS "group", cmm.circ_modifier, cmm.circulate,
cmm.active as "circ matrix active", cmm.marc_type, cmm.marc_form, cmm.marc_vr_format, cmm.ref_flag, cmm.usr_age_lower_bound, cmm.usr_age_upper_bound,
cmm.juvenile_flag, cmm.script_test, cmm.hard_due_date, cmm.total_copy_hold_ratio, cmm.available_copy_hold_ratio, cmm.marc_bib_level,
cmm_copy_circ.shortname as "cmm copy circ lib", cmm_copy_own.shortname as "cmm copy owning lib", cmm.renewals as "cmm renewals",
cmm.user_home_ou as "cmm user home org unit", cmm.grace_period as "cmm grace period", cmm.item_age, aclx.name as "cmm copy location",
duration.NAME AS "duration", duration.id AS "duration id", recurring.NAME AS "recurring", recurring.id AS "recurring id",
maxfine.NAME AS "maxfine", maxfine.id AS "maxfine id", lsm.id AS "limit set map id", lsm.fallthrough AS "limit set map fallthrough",
lsm.active AS "limit set map active", cls.id AS "circ limit id", cls.items_out AS "items out", limsetorg.shortname AS "limit set owning lib",
cls.depth AS "limit set depth", cls.NAME AS "circ limit name", cls.global AS "circ limit global", cls.description AS "circ limit description",
cls_circmod.id AS "cls_circmod id", cls_circmod.circ_mod AS "cls_circmod", cls_copyloc.id AS "cls_copyloc id", acl.NAME "copy location",
cls_groupmap.id AS "cls group map", cls_groupmap.check_only AS "cls group check only",
cls_group.id AS "clsgroup id", cls_group.NAME AS "clsgroup name", cls_group.description AS "clsgroup description"
FROM config.circ_matrix_matchpoint cmm
JOIN actor.org_unit org ON org.id = cmm.org_unit
LEFT JOIN actor.org_unit cmm_copy_circ ON cmm_copy_circ.id = cmm.copy_circ_lib
LEFT JOIN actor.org_unit cmm_copy_own ON cmm_copy_own.id = cmm.copy_owning_lib
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = cmm.id
LEFT JOIN actor.org_unit cmm_user_home on cmm_user_home.id = cmm.user_home_ou
LEFT JOIN config.circ_limit_set cls ON cls.id = lsm.limit_set
LEFT JOIN asset.copy_location aclx on aclx.id = cmm.copy_location
JOIN permission.grp_tree tree ON tree.id = cmm.grp
JOIN config.rule_circ_duration duration ON duration.id = cmm.duration_rule
JOIN config.rule_max_fine maxfine ON maxfine.id = cmm.max_fine_rule
JOIN config.rule_recurring_fine recurring ON recurring.id = cmm.recurring_fine_rule
LEFT JOIN actor.org_unit limsetorg ON limsetorg.id = cls.owning_lib
LEFT JOIN config.circ_limit_set_circ_mod_map cls_circmod ON cls_circmod.limit_set = cls.id
LEFT JOIN config.circ_limit_set_copy_loc_map cls_copyloc ON cls_copyloc.limit_set = cls.id
LEFT JOIN config.circ_limit_set_group_map cls_groupmap ON cls_groupmap.limit_set = cls.id
LEFT JOIN config.circ_limit_group cls_group ON cls_group.id = cls_groupmap.limit_group
LEFT JOIN asset.copy_location acl ON acl.id = cls_copyloc.copy_loc
;

-- where org.id in (160,165,164,163,161,162) ;

-- with thresholds
-- could clean it up more with group bys but it gets changed so much for different things it's easier to do in the spreadsheet

SELECT cmm.id AS "circ matrix match id", cmm.is_renewal, org.shortname AS "org unit", tree.NAME AS "group", cmm.circ_modifier, cmm.circulate,
cmm.active AS "circ matrix active", cmm.marc_type, cmm.marc_form, cmm.marc_vr_format, cmm.ref_flag, cmm.usr_age_lower_bound, cmm.usr_age_upper_bound,
cmm.juvenile_flag, cmm.script_test, cmm.hard_due_date, cmm.total_copy_hold_ratio, cmm.available_copy_hold_ratio, cmm.marc_bib_level,
cmm_copy_circ.shortname AS "cmm copy circ lib", cmm_copy_own.shortname AS "cmm copy owning lib", cmm.renewals AS "cmm renewals",
cmm.user_home_ou AS "cmm user home org unit", cmm.grace_period AS "cmm grace period", cmm.item_age, aclx.NAME AS "cmm copy location",
duration.NAME AS "duration", duration.id AS "duration id", recurring.NAME AS "recurring", recurring.id AS "recurring id",
maxfine.NAME AS "maxfine", maxfine.id AS "maxfine id", lsm.id AS "limit set map id", lsm.fallthrough AS "limit set map fallthrough",
lsm.active AS "limit set map active", cls.id AS "circ limit id", cls.items_out AS "items out", limsetorg.shortname AS "limit set owning lib",
cls.depth AS "limit set depth", cls.NAME AS "circ limit name", cls.GLOBAL AS "circ limit global", cls.description AS "circ limit description",
cls_circmod.id AS "cls_circmod id", cls_circmod.circ_mod AS "cls_circmod", cls_copyloc.id AS "cls_copyloc id", acl.NAME "copy location",
cls_groupmap.id AS "cls group map", cls_groupmap.check_only AS "cls group check only",
cls_group.id AS "clsgroup id", cls_group.NAME AS "clsgroup name", cls_group.description AS "clsgroup description",
penalty_org.shortname, pgpt.threshold, csp.NAME AS "penalty name", csp.label AS "penalty description", csp.block_list, csp.org_depth, csp.staff_alert
FROM config.circ_matrix_matchpoint cmm
JOIN actor.org_unit org ON org.id = cmm.org_unit
JOIN actor.org_unit org_parent ON org_parent.id = org.parent_ou
LEFT JOIN actor.org_unit cmm_copy_circ ON cmm_copy_circ.id = cmm.copy_circ_lib
LEFT JOIN actor.org_unit cmm_copy_own ON cmm_copy_own.id = cmm.copy_owning_lib
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = cmm.id
LEFT JOIN actor.org_unit cmm_user_home ON cmm_user_home.id = cmm.user_home_ou
LEFT JOIN config.circ_limit_set cls ON cls.id = lsm.limit_set
LEFT JOIN asset.copy_location aclx ON aclx.id = cmm.copy_location
JOIN permission.grp_tree tree ON tree.id = cmm.grp
JOIN config.rule_circ_duration duration ON duration.id = cmm.duration_rule
JOIN config.rule_max_fine maxfine ON maxfine.id = cmm.max_fine_rule
JOIN config.rule_recurring_fine recurring ON recurring.id = cmm.recurring_fine_rule
LEFT JOIN actor.org_unit limsetorg ON limsetorg.id = cls.owning_lib
LEFT JOIN config.circ_limit_set_circ_mod_map cls_circmod ON cls_circmod.limit_set = cls.id
LEFT JOIN config.circ_limit_set_copy_loc_map cls_copyloc ON cls_copyloc.limit_set = cls.id
LEFT JOIN config.circ_limit_set_group_map cls_groupmap ON cls_groupmap.limit_set = cls.id
LEFT JOIN config.circ_limit_group cls_group ON cls_group.id = cls_groupmap.limit_group
LEFT JOIN asset.copy_location acl ON acl.id = cls_copyloc.copy_loc
LEFT JOIN permission.grp_penalty_threshold pgpt ON pgpt.grp = tree.id
LEFT JOIN config.standing_penalty csp ON csp.id = pgpt.penalty
LEFT JOIN actor.org_unit penalty_org ON penalty_org.id = pgpt.org_unit
-- where (pgpt.org_unit = org.id or pgpt.org_unit = org_parent.id)
;