[Koha] New SQL Report - Permissions Check

[Cab Vinton]

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, at CHECK:=b.flags
AS 'CheckQ',IF(@Check-131072>=0, at Q:="On", at Q:="Off") AS 'Q',
IF(@Check-131072>=0, at CHECK:=@Check-131072, at CHECK) AS
'CheckP',IF(@Check-65536>=0, at P:="On", at 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, at CHECK:=@Check-65536, at CHECK) AS
'CheckO',IF(@Check-32768>=0, at O:="On", at 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, at CHECK:=@Check-32768, at CHECK) AS
'CheckN',IF(@Check-16384>=0, at N:="On", at N:="Off") AS 'N',
IF(@Check-16384>=0, at CHECK:=@Check-16384, at CHECK) AS
'CheckM',IF(@Check-8192>=0, at M:="On", at 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, at CHECK:=@Check-8192, at CHECK) AS
'CheckL',IF(@Check-4096>=0, at L:="On", at L:="Off") AS 'L',
IF(@Check-4096>=0, at CHECK:=@Check-4096, at CHECK) AS
'CheckK',IF(@Check-2048>=0, at K:="On", at 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, at CHECK:=@Check-2048, at CHECK) AS
'CheckJ',IF(@Check-1024>=0, at J:="On", at J:="Off") AS 'J',
IF(@Check-1024>=0, at CHECK:=@Check-1024, at CHECK) AS
'CheckI',IF(@Check-512>=0, at I:="On", at 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, at CHECK:=@Check-512, at CHECK) AS
'CheckH',IF(@Check-256>=0, at H:="On", at H:="Off") AS 'H',
IF(@Check-256>=0, at CHECK:=@Check-256, at CHECK) AS
'CheckG',IF(@Check-128>=0, at G:="On", at G:="Off") AS 'G',
IF(@Check-128>=0, at CHECK:=@Check-128, at CHECK) AS
'CheckF',IF(@Check-64>=0, at F:="On", at 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, at CHECK:=@Check-64, at CHECK) AS
'CheckE',IF(@Check-32>=0, at E:="On", at E:="Off") AS 'E',
IF(@Check-32>=0, at CHECK:=@Check-32, at CHECK) AS
'CheckD',IF(@Check-16>=0, at D:="On", at D:="Off") AS 'D',
IF(@Check-16>=0, at CHECK:=@Check-16, at CHECK) AS
'CheckC',IF(@Check-8>=0, at C:="On", at 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, at CHECK:=@Check-8, at CHECK) AS
'CheckB',IF(@Check-4>=0, at B:="On", at B:="Off") AS 'B',
IF(@Check-4>=0, at CHECK:=@Check-4, at CHECK) AS
'CheckA',IF(@Check-2>=0, at A:="On", at 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, at CHECK:=@Check-2, at 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