[Koha-bugs] [Bug 7003] canned report to show patrons and permissions

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7003

--- Comment #2 from Nicole C. Engard <nengard at gmail.com> ---
Okay, a partner of ours came up with this report - I'm not sure how to wrap it
in Perl to make it a canned report - but maybe someone else can take this as a
start.  If you do please credit Christopher Brannon for his amazing report:


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

-- 
You are receiving this mail because:
You are the QA Contact for the bug.
You are watching all bug changes.