[Bug 7003] New: canned report to show patrons and permissions
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7003 Bug #: 7003 Summary: canned report to show patrons and permissions Classification: Unclassified Change sponsored?: --- Product: Koha Version: master Platform: All OS/Version: All Status: NEW Severity: enhancement Priority: P5 Component: Reports AssignedTo: gmcharlt@gmail.com ReportedBy: nengard@gmail.com QAContact: koha-bugs@lists.koha-community.org Querying permissions in SQL is very difficult. It would be nice to have a canned report that shows you all of your patrons and the permissions they each have (well not all patrons - but patrons with permissions). Something like this: Patron Name (linked) | Permission Levels (listed) A filter to only show patrons with 'superlibrarian' or other specific permissions would be great too. -- Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the QA Contact for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7003 Nicole C. Engard <nengard@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |new feature --- Comment #1 from Nicole C. Engard <nengard@gmail.com> --- I still think this is a good idea :) -- You are receiving this mail because: You are the QA Contact for the bug. You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7003 --- Comment #2 from Nicole C. Engard <nengard@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,@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) As SubM, 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(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.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7003 Nick Clemens <nick@quecheelibrary.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@quecheelibrary.org --- Comment #3 from Nick Clemens <nick@quecheelibrary.org> --- Just throwing this in, Chris and I rewrote the report to make it easier to read: SELECT surname,firstname,cardnumber, categorycode, branchcode, IF(flags MOD 2,'Set','') AS SuperLib, IF(MOD(flags DIV 2,2),'All parameters',GROUP_CONCAT(IF(u_p.module_bit=1,p.code,'') SEPARATOR ' ' ) ) AS "CircPermissions", IF(MOD(flags DIV 4,2),'Set','') AS 'View staff interface', IF(MOD(flags DIV 8,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=3,p.code,'') SEPARATOR ' ' ) ) AS ManParams, IF(MOD(flags DIV 16,2),'Set','') AS 'Add/modify patrons', IF(MOD(flags DIV 32,2),'Set','') AS 'Modify permissions', IF(MOD(flags DIV 64,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=6,p.code,'') SEPARATOR ' ' ) ) AS ReservePermissions, IF(MOD(flags DIV 128,2),'Set','') AS BorrowBooks, IF(MOD(flags DIV 512,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=9,p.code,'') SEPARATOR ' ' ) ) AS EditCatalogue, IF(MOD(flags DIV 1024,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=10,p.code,'') SEPARATOR ' ' ) ) AS UpdateCharges, IF(MOD(flags DIV 2048,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=11,p.code,'') SEPARATOR ' ' ) ) AS Acquisition, IF(MOD(flags DIV 4096,2),'Set','') AS Management, IF(MOD(flags DIV 8192,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=13,p.code,'') SEPARATOR ' ' ) ) AS Tools, IF(MOD(flags DIV 16384,2),'Set','') AS EditAuthories, IF(MOD(flags DIV 32768,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=15,p.code,'') SEPARATOR ' ' ) ) AS Series, IF(MOD(flags DIV 65536,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=16,p.code,'') SEPARATOR ' ' ) ) AS Reports, IF(MOD(flags DIV 131072,2),'Set','') AS StaffAccess, IF(MOD(flags DIV 262144,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=18,p.code,'') SEPARATOR ' ' ) ) AS CourseReserves, IF(MOD(flags DIV 524288,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=19,p.code,'') SEPARATOR ' ' ) ) AS Plugins FROM borrowers b LEFT JOIN user_permissions u_p ON b.borrowernumber=u_p.borrowernumber LEFT JOIN permissions p ON u_p.code=p.code WHERE flags>0 OR u_p.module_bit>0 GROUP BY b.borrowernumber ORDER BY categorycode,branchcode,surname,firstname ASC -- You are receiving this mail because: You are the QA Contact for the bug. You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org