[Koha] New SQL Report - Patron Permissions

[Cab Vinton]

+ peanut gallery commentary -- Whoa. Will have to spend some time
learning from this lovely SQL ...

Cheers,

Cab Vinton
Sanbornton PL
Sanbornton, NH

Patron Permissions
Developer: Christopher Brannon
Module: Patrons
Purpose: List patrons by library and type, and show what the status of
all permissions.
Status: Complete

SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS
'Card Number',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',SubM AS 'tools
subs',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) AS SubM,
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(b.flags=1,"On","Off") AS "Super"
FROM borrowers b
LEFT JOIN categories USING (categorycode)
WHERE b.branchcode=<<Accounts FOR|branches>> AND
b.categorycode=<<Account type|categorycode>>) AS MainFlags
ORDER BY surname, firstname ASC