Permissions Check Developer: Christopher Brannon Module: Patrons Purpose: Search for ANY patrons that have some dangerous flags set. Modify the WHERE statement to watch for flags you want to keep an eye on. Status: Complete
SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS 'Card Number',categorycode,userid AS 'UserID',branchcode AS 'Library',Super AS 'superlibrarian',A AS 'circulate',SubA AS 'circulate subs',B AS 'catalogue',C AS 'parameters',SubC AS 'parameters subs',D AS 'borrowers',E AS 'permissions',F AS 'reserveforothers',SubF AS 'reserveforothers subs',G AS 'borrow',I AS 'editcatalogue',SubI AS 'editcatalogue subs',J AS 'updatecharges',K AS 'acquisition',SubK AS 'acquisition subs',L AS 'management',M AS 'tools',SubM1 AS 'tools subs (Pt 1)',SubM2 AS 'tools subs (Pt 2)',N AS 'editauthorities',O AS 'serials',SubO AS 'serials subs',P AS 'reports',SubP AS 'reports subs',Q AS 'staffaccess' FROM (SELECT b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@CHECK:=b.flags AS 'CheckQ',IF(@Check-131072>=0,@Q:="On",@Q:="Off") AS 'Q', IF(@Check-131072>=0,@CHECK:=@Check-131072,@CHECK) AS 'CheckP',IF(@Check-65536>=0,@P:="On",@P:="Off") AS 'P',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) AS SubP, IF(@Check-65536>=0,@CHECK:=@Check-65536,@CHECK) AS 'CheckO',IF(@Check-32768>=0,@O:="On",@O:="Off") AS 'O',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) AS SubO, IF(@Check-32768>=0,@CHECK:=@Check-32768,@CHECK) AS 'CheckN',IF(@Check-16384>=0,@N:="On",@N:="Off") AS 'N', IF(@Check-16384>=0,@CHECK:=@Check-16384,@CHECK) AS 'CheckM',IF(@Check-8192>=0,@M:="On",@M:="Off") AS 'M',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "a%" AND "i%") AS SubM1,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "j%" AND "z%") AS SubM2, IF(@Check-8192>=0,@CHECK:=@Check-8192,@CHECK) AS 'CheckL',IF(@Check-4096>=0,@L:="On",@L:="Off") AS 'L', IF(@Check-4096>=0,@CHECK:=@Check-4096,@CHECK) AS 'CheckK',IF(@Check-2048>=0,@K:="On",@K:="Off") AS 'K',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) AS SubK, IF(@Check-2048>=0,@CHECK:=@Check-2048,@CHECK) AS 'CheckJ',IF(@Check-1024>=0,@J:="On",@J:="Off") AS 'J', IF(@Check-1024>=0,@CHECK:=@Check-1024,@CHECK) AS 'CheckI',IF(@Check-512>=0,@I:="On",@I:="Off") AS 'I',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) AS SubI, IF(@Check-512>=0,@CHECK:=@Check-512,@CHECK) AS 'CheckH',IF(@Check-256>=0,@H:="On",@H:="Off") AS 'H', IF(@Check-256>=0,@CHECK:=@Check-256,@CHECK) AS 'CheckG',IF(@Check-128>=0,@G:="On",@G:="Off") AS 'G', IF(@Check-128>=0,@CHECK:=@Check-128,@CHECK) AS 'CheckF',IF(@Check-64>=0,@F:="On",@F:="Off") AS 'F',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) AS SubF, IF(@Check-64>=0,@CHECK:=@Check-64,@CHECK) AS 'CheckE',IF(@Check-32>=0,@E:="On",@E:="Off") AS 'E', IF(@Check-32>=0,@CHECK:=@Check-32,@CHECK) AS 'CheckD',IF(@Check-16>=0,@D:="On",@D:="Off") AS 'D', IF(@Check-16>=0,@CHECK:=@Check-16,@CHECK) AS 'CheckC',IF(@Check-8>=0,@C:="On",@C:="Off") AS 'C',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) AS SubC, IF(@Check-8>=0,@CHECK:=@Check-8,@CHECK) AS 'CheckB',IF(@Check-4>=0,@B:="On",@B:="Off") AS 'B', IF(@Check-4>=0,@CHECK:=@Check-4,@CHECK) AS 'CheckA',IF(@Check-2>=0,@A:="On",@A:="Off") AS 'A',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) AS SubA, IF(@Check-2>=0,@CHECK:=@Check-2,@CHECK) AS 'CheckSuper',IF(@Check>0,"On","Off") AS "Super" FROM borrowers b LEFT JOIN categories USING (categorycode)) AS MainFlags WHERE MainFlags.Super="On" OR MainFlags.E="On" OR MainFlags.M="On" OR MainFlags.SubM1 LIKE "%delete_anonymize_patrons%" OR MainFlags.SubM1 LIKE "%edit_calendar%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%edit_notice_status_triggers%" OR MainFlags.SubM1 LIKE "%edit_notices%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%items_batchdel%" OR MainFlags.SubM1 LIKE "%schedule_tasks%" OR MainFlags.SubM1 LIKE "%view_system_logs%" OR MainFlags.SubP LIKE "%create_reports%" ORDER BY surname, firstname ASC _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha