API tools faq
paste
View difference between Paste ID: pfJEMKUv and 5Ku48YDx
SHOW: | | - or go back to the newest paste.
1
-- really verbose alternative for getting the rules for in-db holds rules
2
-- rather than just select * config.hold_matrix_matchpoint
3
-- almost a necessity for a consortium with varying rules or any library
4
-- with intricate polices because in-db holds policies are much easier to
5
-- handle via SQL than through the staff client
6
-- script has IDs for admins and look ups to names for easy reference
7
-- tested on 2.1 and 2.4
8
-- updated to include the circ limits information on 2.4.4 2013-12-19
9
-- this tracks circ limits information for circ limits only not copy locations or MARC attributes
10
-- (c) Rogan Hamby, 2013, roganhamby.com
11
-- Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License
12
13
14-
 

14+
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,

15-
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",

15+
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,

16-
aou.parent_ou AS "Parent Org Unit", aou2.shortname "Parent Org Unit Short Name", pgt.id AS "Permission Group ID",

16+
cmm.juvenile_flag, cmm.script_test, cmm.hard_due_date, cmm.total_copy_hold_ratio, cmm.available_copy_hold_ratio, cmm.marc_bib_level,

17-
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier", ccm.marc_type AS "MARC Type", ccm.marc_form AS "MARC Form",

17+
cmm_copy_circ.shortname as "cmm copy circ lib", cmm_copy_own.shortname as "cmm copy owning lib", cmm.renewals as "cmm renewals",

18-
ccm.marc_vr_format AS "MARC Video Recording Format", ccm.ref_flag AS "Reference Boolean", ccm.is_renewal AS "Renewable Boolean",

18+
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",

19-
ccm.usr_age_lower_bound AS "Lower Age Bound", ccm.usr_age_upper_bound AS "Upper Age Bound", ccm.juvenile_flag AS "Juvenile",

19+
duration.NAME AS "duration", duration.id AS "duration id", recurring.NAME AS "recurring", recurring.id AS "recurring id",

20-
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",

20+
maxfine.NAME AS "maxfine", maxfine.id AS "maxfine id", lsm.id AS "limit set map id", lsm.fallthrough AS "limit set map fallthrough",

21-
ccm.recurring_fine_rule AS "Recurring Fine Rule", recur.NAME AS "Recurring Fine Name", ccm.total_copy_hold_ratio AS "Total Copy Hold Ratio",

21+
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",

22-
ccm.available_copy_hold_ratio AS "Available Copy Hold Ratio",

22+
cls.depth AS "limit set depth", cls.NAME AS "circ limit name", cls.global AS "circ limit global", cls.description AS "circ limit description",

23-
ccm.hard_due_date AS "Hard Due Date ID", hdd.NAME AS "Hard Due Date Name",  

23+
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",

24-
ccm.copy_circ_lib AS "Copy Circ Lib ID", aou3.shortname AS "Copy Circ Lib Shortname",

24+
cls_groupmap.id AS "cls group map", cls_groupmap.check_only AS "cls group check only",

25-
ccm.copy_owning_lib AS "Copy Owning Lib ID", aou4.shortname AS "Copy Owning Lib Shortname",

25+
cls_group.id AS "clsgroup id", cls_group.NAME AS "clsgroup name", cls_group.description AS "clsgroup description"

26-
ccm.renewals AS "Renewals",

26+
FROM config.circ_matrix_matchpoint cmm

27-
ccm.user_home_ou AS "User Home Lib ID", aou5.shortname AS "User Home Lib Shortname",

27+
JOIN actor.org_unit org ON org.id = cmm.org_unit

28-
ccm.grace_period AS "Grace Period", ccm.marc_bib_level AS "MARC Bib Level",

28+
LEFT JOIN actor.org_unit cmm_copy_circ ON cmm_copy_circ.id = cmm.copy_circ_lib

29-
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", 

29+
LEFT JOIN actor.org_unit cmm_copy_own ON cmm_copy_own.id = cmm.copy_owning_lib

30-
aoucls.shortname AS "Circ Limit Set Owning Lib Name",

30+
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = cmm.id

31-
cls.items_out "Circ Limit Set Items Out", cls.depth AS "Circ Limit Set Depth", cls.GLOBAL AS "Circ Limit Set Global",

31+
LEFT JOIN actor.org_unit cmm_user_home on cmm_user_home.id = cmm.user_home_ou

32-
scmm.circ_mod AS "Circ Limit Set Circ Mod", scmm.id as "Circ Mod Map ID"

32+
33-
FROM config.circ_matrix_matchpoint ccm

33+
LEFT JOIN asset.copy_location aclx on aclx.id = cmm.copy_location

34-
LEFT JOIN actor.org_unit aou ON aou.id = ccm.org_unit

34+
JOIN permission.grp_tree tree ON tree.id = cmm.grp

35-
LEFT JOIN actor.org_unit aou2 ON aou2.id = aou.parent_ou

35+
JOIN config.rule_circ_duration duration ON duration.id = cmm.duration_rule

36-
LEFT JOIN permission.grp_tree pgt ON pgt.id = ccm.grp

36+
JOIN config.rule_max_fine maxfine ON maxfine.id = cmm.max_fine_rule

37-
LEFT JOIN config.rule_circ_duration dur ON dur.id = ccm.duration_rule

37+
JOIN config.rule_recurring_fine recurring ON recurring.id = cmm.recurring_fine_rule

38-
LEFT JOIN config.rule_max_fine maxf ON maxf.id = ccm.max_fine_rule

38+
LEFT JOIN actor.org_unit limsetorg ON limsetorg.id = cls.owning_lib

39-
LEFT JOIN config.rule_recurring_fine recur ON recur.id = ccm.recurring_fine_rule

39+
LEFT JOIN config.circ_limit_set_circ_mod_map cls_circmod ON cls_circmod.limit_set = cls.id

40-
LEFT JOIN config.hard_due_date hdd ON hdd.id = ccm.hard_due_date

40+
LEFT JOIN config.circ_limit_set_copy_loc_map cls_copyloc ON cls_copyloc.limit_set = cls.id

41-
LEFT JOIN actor.org_unit aou3 ON aou3.id = ccm.copy_circ_lib

41+
LEFT JOIN config.circ_limit_set_group_map cls_groupmap ON cls_groupmap.limit_set = cls.id

42-
LEFT JOIN actor.org_unit aou4 ON aou4.id = ccm.copy_owning_lib

42+
LEFT JOIN config.circ_limit_group cls_group ON cls_group.id = cls_groupmap.limit_group

43-
LEFT JOIN actor.org_unit aou5 ON aou5.id = ccm.user_home_ou

43+
LEFT JOIN asset.copy_location acl ON acl.id = cls_copyloc.copy_loc

44-
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = ccm.id

44+
45
46-
LEFT JOIN config.circ_limit_set_circ_mod_map scmm ON scmm.limit_set = cls.id

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

47-
LEFT JOIN actor.org_unit aoucls ON aoucls.id = cls.owning_lib

47+
48
-- with thresholds

49
-- 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

50
51-
-- shorter with circ

51+
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,

52-
 

52+
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,

53-
SELECT ccm.id AS "Circ Matrix Matchpoint ID", ccm.active AS "Active", ccm.circulate as "Circulate", 

53+
cmm.juvenile_flag, cmm.script_test, cmm.hard_due_date, cmm.total_copy_hold_ratio, cmm.available_copy_hold_ratio, cmm.marc_bib_level,

54-
ccm.org_unit AS "Circ Org Unit", aou.shortname AS "Circ Short Org Unit Name",

54+
cmm_copy_circ.shortname AS "cmm copy circ lib", cmm_copy_own.shortname AS "cmm copy owning lib", cmm.renewals AS "cmm renewals",

55-
aou.parent_ou AS "Parent Org Unit", aou2.shortname "Parent Org Unit Short Name", pgt.id AS "Permission Group ID",

55+
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",

56-
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier", 

56+
duration.NAME AS "duration", duration.id AS "duration id", recurring.NAME AS "recurring", recurring.id AS "recurring id",

57-
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",

57+
maxfine.NAME AS "maxfine", maxfine.id AS "maxfine id", lsm.id AS "limit set map id", lsm.fallthrough AS "limit set map fallthrough",

58-
ccm.recurring_fine_rule AS "Recurring Fine Rule", recur.NAME AS "Recurring Fine Name", 

58+
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",

59-
ccm.user_home_ou AS "User Home Lib ID", aou5.shortname AS "User Home Lib Shortname",

59+
cls.depth AS "limit set depth", cls.NAME AS "circ limit name", cls.GLOBAL AS "circ limit global", cls.description AS "circ limit description",

60-
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",

60+
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",

61-
aoucls.shortname AS "Circ Limit Set Owning Lib Name",

61+
cls_groupmap.id AS "cls group map", cls_groupmap.check_only AS "cls group check only",

62-
cls.items_out "Circ Limit Set Items Out", cls.depth AS "Circ Limit Set Depth", cls.GLOBAL AS "Circ Limit Set Global",

62+
cls_group.id AS "clsgroup id", cls_group.NAME AS "clsgroup name", cls_group.description AS "clsgroup description",

63-
scmm.circ_mod AS "Circ Limit Set Circ Mod", scmm.id AS "Circ Mod Map ID"

63+
penalty_org.shortname, pgpt.threshold, csp.NAME AS "penalty name", csp.label AS "penalty description", csp.block_list, csp.org_depth, csp.staff_alert

64-
FROM config.circ_matrix_matchpoint ccm

64+
FROM config.circ_matrix_matchpoint cmm

65-
LEFT JOIN actor.org_unit aou ON aou.id = ccm.org_unit

65+
JOIN actor.org_unit org ON org.id = cmm.org_unit

66-
LEFT JOIN actor.org_unit aou2 ON aou2.id = aou.parent_ou

66+
JOIN actor.org_unit org_parent ON org_parent.id = org.parent_ou

67-
LEFT JOIN permission.grp_tree pgt ON pgt.id = ccm.grp

67+
LEFT JOIN actor.org_unit cmm_copy_circ ON cmm_copy_circ.id = cmm.copy_circ_lib

68-
LEFT JOIN config.rule_circ_duration dur ON dur.id = ccm.duration_rule

68+
LEFT JOIN actor.org_unit cmm_copy_own ON cmm_copy_own.id = cmm.copy_owning_lib

69-
LEFT JOIN config.rule_max_fine maxf ON maxf.id = ccm.max_fine_rule

69+
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = cmm.id

70-
LEFT JOIN config.rule_recurring_fine recur ON recur.id = ccm.recurring_fine_rule

70+
LEFT JOIN actor.org_unit cmm_user_home ON cmm_user_home.id = cmm.user_home_ou

71-
LEFT JOIN config.hard_due_date hdd ON hdd.id = ccm.hard_due_date

71+
72-
LEFT JOIN actor.org_unit aou3 ON aou3.id = ccm.copy_circ_lib

72+
LEFT JOIN asset.copy_location aclx ON aclx.id = cmm.copy_location

73-
LEFT JOIN actor.org_unit aou4 ON aou4.id = ccm.copy_owning_lib

73+
JOIN permission.grp_tree tree ON tree.id = cmm.grp

74-
LEFT JOIN actor.org_unit aou5 ON aou5.id = ccm.user_home_ou

74+
JOIN config.rule_circ_duration duration ON duration.id = cmm.duration_rule

75-
LEFT JOIN config.circ_matrix_limit_set_map lsm ON lsm.matchpoint = ccm.id

75+
JOIN config.rule_max_fine maxfine ON maxfine.id = cmm.max_fine_rule

76
JOIN config.rule_recurring_fine recurring ON recurring.id = cmm.recurring_fine_rule

77-
LEFT JOIN config.circ_limit_set_circ_mod_map scmm ON scmm.limit_set = cls.id

77+
LEFT JOIN actor.org_unit limsetorg ON limsetorg.id = cls.owning_lib

78-
LEFT JOIN actor.org_unit aoucls ON aoucls.id = cls.owning_lib

78+
LEFT JOIN config.circ_limit_set_circ_mod_map cls_circmod ON cls_circmod.limit_set = cls.id

79
LEFT JOIN config.circ_limit_set_copy_loc_map cls_copyloc ON cls_copyloc.limit_set = cls.id

80-
 

80+
LEFT JOIN config.circ_limit_set_group_map cls_groupmap ON cls_groupmap.limit_set = cls.id

81
LEFT JOIN config.circ_limit_group cls_group ON cls_group.id = cls_groupmap.limit_group

82-
-- for troubleshooting :

82+
LEFT JOIN asset.copy_location acl ON acl.id = cls_copyloc.copy_loc

83
LEFT JOIN permission.grp_penalty_threshold pgpt ON pgpt.grp = tree.id

84
LEFT JOIN config.standing_penalty csp ON csp.id = pgpt.penalty

85-
SELECT ccm.id AS "Circ Matrix Matchpoint ID", aou.shortname AS "Circ Short Org Unit Name",

85+
LEFT JOIN actor.org_unit penalty_org ON penalty_org.id = pgpt.org_unit

86-
pgt.NAME AS "Permission Group Name", ccm.circ_modifier AS "Circ Modifier", ccm.circulate as "Circulate Boolean",

86+
-- where (pgpt.org_unit = org.id or pgpt.org_unit = org_parent.id)

87-
ccm.is_renewal AS "Renewable Boolean", lsm.id AS "Circ Limit Set Map ID", cls.id as "Circ Limit Set ID", 

87+
;
        

    


            


                                


        

            



                
    

        Public Pastes
    

    
            

    

                    

        

    





    


    
    
    
    
    
    
    
    




    


    



                

            We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.             OK, I Understand
        

    
                

            

                
                    
                
            

            

                Not a member of Pastebin yet?

                Sign Up, it unlocks many cool features!