SQL Reports Patrons

SQL Reports Main Library | SQL Reports Holds | SQL Reports Circulation
== Patron Statistics ==
=== General ===
==== Export patrons ====
* Developer: Heinrich Hartl
* Module: Borrowers
* Purpose: To create a sample spreadsheet for patron import or to export patron data from one library for import into another library
* Status: Completed
SELECT cardnumber, surname, 
 firstname, title, othernames, initials, streetnumber, streettype, address, address2, city, state, zipcode, country, email, phone, mobile, fax, 
 emailpro, phonepro, B_streetnumber, B_streettype, B_address, B_address2, B_city, B_state, B_zipcode, B_country, B_email, B_phone, dateofbirth, 
 branchcode, categorycode, dateenrolled, dateexpiry, gonenoaddress, lost, debarred, debarredcomment, contactname, contactfirstname, contacttitle,
 guarantorid, borrowernotes, relationship, sex, password, flags, userid, opacnote, contactnote, sort1, sort2, altcontactfirstname,
 altcontactsurname, altcontactaddress1, altcontactaddress2, altcontactaddress3, altcontactstate, altcontactzipcode, altcontactcountry, altcontactphone,
 smsalertnumber, privacy
 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> 
ORDER BY surname ASC, firstname ASC
==== Count of all patrons by a specific date ====
* Developer: Marie-Luce Laflamme, inLibro
*Module: Patrons
*Purpose: count of all enrolled patrons on specific date
*Status: Complete
SELECT
 count(borrowernumber) AS "Patrons"
FROM 
 borrowers
WHERE
 DATE(dateenrolled)<= <<Enrolled before (yyyy-mm-dd)|date>>
=== Patrons and Issues ===
==== Patrons with Checked Out Items ====
* Developer: Nora Blake 
* Module: Circulation 
* Purpose: List of items checked out to patrons 
* Status: Complete 
 SELECT issues, biblio.title, author, surname, firstname, borrowers.sort1, 
 items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
 FROM issues 
 LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber 
 LEFT JOIN items ON issues.itemnumber=items.itemnumber 
 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
 WHERE issues.branchcode=<<Checked out at|branches>>
 ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title
==== Patrons with No Checkouts ====
* Developer: Nicole C. Baratta, ByWater Solutions, revised Nick Clemens, VOKAL Library System
* Module: Patrons
* Purpose: Patrons who haven't checked out in a specific timeframe
* Status: Completed
SELECT surname,firstname,cardnumber
FROM borrowers b
LEFT OUTER JOIN (SELECT DISTINCT borrowernumber FROM statistics WHERE datetime BETWEEN <<Date1|date>> and <<Date 2|date>> AND type="issue") foo ON b.borrowernumber=foo.borrowernumber
WHERE foo.borrowernumber IS NULL
==== Anonymous Patron Account Report ====
* Developer: Barton Chittenden, ByWater Solutions
* Purpose: A list of items checked out to the Anonymous Patron account
* Status: Completed
SELECT
 itemnumber,
 title, 
 issuedate, 
 returndate
FROM
 old_issues 
 INNER JOIN systempreferences 
 ON ( 
 old_issues.borrowernumber = systempreferences.value 
 AND systempreferences.variable = 'anonymouspatron' 
 )
 LEFT JOIN items USING (itemnumber)
 LEFT JOIN biblio USING (biblionumber)
=== Patrons and Age ===
==== Patron's birthday today. ====
* Developer: Sri. Sourav Nag, Librarian, Yogoda Satsanga Mahavidyalaya
* Module: patrons 
* Purpose: This report aims to identify library patrons who are celebrating their birthday today. By identifying patrons with birthdays on the current date, library staff can offer personalized greetings, special promotions, or birthday-themed services to enhance patron engagement and satisfaction
* Status: Complete 
SELECT 
 borrowers.firstname AS 'First Name', 
 borrowers.surname AS 'Surname', 
 borrowers.address, 
 borrowers.address2, 
 borrowers.city, 
 borrowers.zipcode AS 'Zip Code', 
 borrowers.dateofbirth AS 'Date of Birth', 
 borrowers.email, 
 borrowers.phone,
 borrowers.cardnumber AS 'Card Number'
FROM 
 borrowers 
WHERE 
 MONTH(borrowers.dateofbirth) = MONTH(CURRENT_DATE()) 
 AND DAY(borrowers.dateofbirth) = DAY(CURRENT_DATE())
==== Patron Birthday Report ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: patrons 
* Purpose: Patrons who are under the age of 17 that have a birthday this month
* Status: Complete 
SELECT firstname, surname, address, address2, city, 
 zipcode, dateofbirth 
FROM borrowers 
WHERE MONTH(dateofbirth) = <<Month (mm)>> 
 AND DATEDIFF(<<Last date of month (yyyy-mm-dd)|date>>, dateofbirth) < ((17*365)+4)
==== Patrons of specific age range ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: patrons 
* Purpose: This report shows patrons between the age of 12 and 13
* Status: Complete 
SELECT cardnumber, firstname, surname, dateofbirth, 
 timestampdiff(year,dateofbirth,curdate()) as age 
 categorycode 
FROM borrowers 
WHERE timestampdiff(year,dateofbirth,curdate()) in (12,13)
==== Patrons Whose Age Violates Category Age Limits ====
* Developer: Kyle M Hall, ByWater Solutions
* Module: circ 
* Purpose: Lists all patrons whose age is above or below the maximum and/or minimum age range for his or her category
* Status: Complete 
SELECT
 c.description AS category,
 b.surname,
 b.firstname,
 b.dateofbirth AS DoB,
 c.dateofbirthrequired as MinAge,
 c.upperagelimit AS MaxAge,
 FLOOR(DATEDIFF (NOW(), b.dateofbirth)/365) AS age 
FROM
 borrowers b 
LEFT JOIN
 categories c USING ( categorycode ) 
WHERE
 (
 c.upperagelimit > 0 
 OR c.dateofbirthrequired > 0 
 ) 
HAVING
 age < MinAge or ( age > MaxAge And MaxAge > 0 )
==== Count of discharges, select date range ====
* Developer: Md. Mubassir Ahsan
* Module: Patrons 
* Purpose: Count the discharges monthly, yearly or any defined date range. 
* Status: Complete 
SELECT COUNT(discharges.borrower) AS "Total Discharges"
FROM discharges
WHERE 
validated > <<Beginning of first range (dd-mm-yyyy)|date>>
AND validated < <<End of first range (dd-mm-yyyy)|date>> 
==== Details of patrons who are discharged today ====
* Developer: Sri. Sourav Nag, Librarian, Yogoda Satsanga Mahavidyalaya
* Module: Patrons 
* Purpose: Details of patrons who are discharged today. 
* Status: Complete 
SELECT 
 borrowers.cardnumber,
 borrowers.surname,
 borrowers.firstname,
 borrowers.email,
 borrowers.address,
 borrowers.phone,
 borrowers.dateenrolled,
 discharges.validated AS "Date of Discharge"
FROM 
 discharges
INNER JOIN 
 borrowers ON discharges.borrower = borrowers.borrowernumber
WHERE 
 discharges.validated >= CURRENT_DATE
 AND discharges.validated < CURRENT_DATE + INTERVAL 1 DAY
==== Count of Active Patrons by Age Group in a Specified Date Range ====
* Developer: Spencer Smith
* Module: Patrons 
* Purpose: Returns a count of patrons grouped by age range, with activity in a certain date range restricted by their home branch. 
* Status: Complete 
SELECT
 (CASE
 WHEN timestampdiff(year,dateofbirth,curdate()) < 5 THEN '0 to 4'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 5 and 12 THEN '5 to 12'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 13 and 18 THEN '13 to 18'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 19 and 25 THEN '19 to 25'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 26 and 32 THEN '26 to 32'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 33 and 40 THEN '33 to 40'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 41 and 50 THEN '41 to 50'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 51 and 60 THEN '51 to 60'
 WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 61 and 70 THEN '61 to 70'
 WHEN timestampdiff(year,dateofbirth,curdate()) >= 70 THEN '70 +'
 WHEN timestampdiff(year,dateofbirth,curdate()) IS NULL THEN 'Not Filled In (NULL)'
 END) as age_range,
 COUNT(borrowernumber) AS Patrons
 FROM
 borrowers
WHERE
 borrowernumber IN (
 SELECT
 borrowernumber
 FROM
 statistics
 WHERE
 DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
 ) and borrowers.branchcode=<<branchcode|branches>>
 GROUP BY age_range
 ORDER BY age_range
== New Patrons ==
==== New Patron List (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: 
* Status: Complete 
SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.dateenrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
ORDER BY borrowers.surname asc
==== Newest Patron Detail by Branch and Category ====
* Developer: Siraj Saeed, Assistant Librarian, UIT University, Karachi, Pakistan 
* Module: Patrons
* Purpose: This report provides details of the most recently added patron in Koha, filtered by branch and category without requiring a date range
* Status: Complete 
SELECT borrowers.cardnumber, 
 borrowers.surname, 
 borrowers.firstname, 
 borrowers.email, 
 borrowers.initials, 
 borrowers.dateenrolled, 
 borrowers.branchcode, 
 borrowers.categorycode
FROM borrowers
WHERE borrowers.dateenrolled = (
 SELECT MAX(dateenrolled) 
 FROM borrowers 
 WHERE branchcode = <<Enter patrons library|branches>> 
 AND categorycode = <<Enter Category borrowers|categorycode>>
)
AND branchcode = <<Enter patrons library|branches>>
AND categorycode = <<Enter Category borrowers|categorycode>>
==== New Patrons by Category at Branch ====
* Developer: Sharon Moreland 
* Module: Circulation 
* Purpose: New patrons added 
* Status: Complete 
 SELECT branchcode,categorycode,COUNT(*)
 FROM borrowers WHERE MONTH(dateenrolled) = <<Month enrolled (mm)>> AND YEAR(dateenrolled)= <<Year enrolled (yyyy)>>
 GROUP BY branchcode,categorycode 
 ORDER BY branchcode
==== New Patrons by Category in Date Range ====
* Developer: Nicole C. Baratta, ByWater Solutions 
* Module: Circulation 
* Purpose: New patrons added by category in a date range (even if they have been deleted since)
* Status: Complete 
SELECT categorycode, COUNT(borrowernumber) as 'new patrons'
FROM (select borrowernumber, categorycode, dateenrolled from borrowers 
union all
select borrowernumber, categorycode, dateenrolled from deletedborrowers) as patrons 
WHERE dateenrolled between <<Added between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
GROUP BY categorycode
==== New Patron Count (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: 
* Status: Complete 
SELECT COUNT(*) as 'New Patrons Last Month' 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month)
==== New Patron Count (by Branch/Category) (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: Count of new patrons enrolled in the previous month, by branch and category code 
* Status: Complete 
SELECT branchcode, categorycode, COUNT(branchcode) as NumberEnrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
GROUP BY branchcode, categorycode
==== New Patrons by Branch (year to date) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: 
* Status: Complete 
SELECT branchcode, categorycode, COUNT(branchcode) as NumberEnrolled 
FROM borrowers 
WHERE YEAR(borrowers.dateenrolled) = YEAR(NOW()) 
GROUP BY branchcode, categorycode
==== Count of New Patrons ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Count of patrons added between two dates at a branch
* Status: Complete
SELECT COUNT(borrowernumber) AS 'New Patrons' 
FROM borrowers 
WHERE dateenrolled between <<Added between (yyyy-mm-dd)|date>> 
 and <<and (yyyy-mm-dd)|date>> and branchcode=<<Branch|branches>> 
== Patrons and Branches ==
==== Patron branchcode change counts in a date range ====
* Developer: Jason Robb, SEKLS
* Module: Patrons
* Purpose: Finds how many patrons have switched branchcode to the selected library, grouped by previous library.
* Status: Complete
SELECT JSON_VALUE(info, '$.branchcode.before') AS PreviousHomeLib, JSON_VALUE(info, '$.branchcode.after') AS CurrentHomeLib , COUNT(*) AS count
FROM action_logs
WHERE module = "MEMBERS"
 AND action = "MODIFY"
 AND date(timestamp) BETWEEN <<Users modified between|date>> AND <<and|date>>
 AND JSON_VALUE(info, '$.branchcode.before') NOT LIKE <<Choose Library|branches>>
 AND JSON_VALUE(info, '$.branchcode.after') LIKE <<Choose Library|branches>>
GROUP BY PreviousHomeLib
== Patrons, Categories, and Permissions ==
==== Patrons with Staff Permissions ====
* Developer: Ian Walls, ByWater Solutions
* Module: Patrons
* Purpose: List of patrons/staff with their permission levels
* Status: Complete
SELECT borrowernumber, firstname, surname, categorycode, 
 description, flags 
FROM borrowers 
JOIN user_permissions USING (borrowernumber) 
JOIN permissions USING (code) 
UNION (
 select borrowernumber, firstname, surname, 
 categorycode, 'module-level permissions; 
 1 is superlibrarian' as description, flags 
 FROM borrowers 
 WHERE flags > 0) 
ORDER BY borrowernumber ASC
==== Superlibrarians ====
* Developer: Nicole C. Baratta, Joy Nelson and Elliott Davis, ByWater Solutions
* Module: Patrons
* Purpose: List of patrons/staff with superlibrarian permission
* Status: Complete
SELECT borrowernumber, firstname, surname, categorycode, flags 
FROM borrowers 
WHERE flags%2=1 
ORDER BY borrowernumber ASC
==== Patrons with staff permission, and if they are superlibrarians ====
* Developer: Tomás Cohen Arazi, Universidad Nacional de Córdoba
* Module: Patrons
* Purpose: Patrons with staff permission, and if the are superlibrarians
* Status: Complete
SELECT
 surname AS 'Surname',
 firstname AS 'Firstname',
 Cardnumber,
 categories.description AS 'Patron type',
 Superlibrarian
FROM (
 SELECT
 surname, firstname,
 CONCAT('<a href="http://',(SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
 '/cgi-bin/koha/members/moremember.pl?borrowernumber=',bn.borrowernumber,'">',cardnumber,'</a>') AS 'Cardnumber',
 CASE WHEN flags%2=1 THEN 'yes' WHEN flags%2=0 THEN 'no' END AS Superlibrarian, categorycode
 FROM (
 SELECT borrowernumber
 FROM borrowers
 WHERE flags > 0
 UNION
 SELECT DISTINCT borrowernumber
 FROM user_permissions) bn 
 LEFT JOIN borrowers ON (borrowers.borrowernumber=bn.borrowernumber)
) a
LEFT JOIN categories
ON (a.categorycode=categories.categorycode)
ORDER BY surname ASC
==== Patron Permissions ====
* Developer: Christopher Brannon & Nick Clemens
* Module: Patrons
* Purpose: Lists all patrons with any permissions set and details their permissions.
* Status: Complete
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
==== 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
==== Accounts with permissions ====
* Developer: George H. Williams (Northeast Kansas Library System)
* Module: Patrons
* Purpose: Displays accounts with "Staff" permissions at the library you specify
* Version: Developed on Koha 21.11
* Status: Complete
* Notes: Based on the report "Patron Permissions" by Christopher Brannon and Nick Clemens but updated to include new permissions and layed out in a 6 column table
 SELECT 
 borrowers.borrowernumber, 
 borrowers.userid, 
 borrowers.categorycode, 
 borrowers.branchcode, 
 borrowers.lastseen, 
 If( 
 borrowers.flags MOD 2, 
 'Superlibrarian', 
 CONCAT_WS(, 
 '-----Library staff-----', 
 If( 
 MOD(borrowers.flags DIV 2, 2), 
 'circulate - All parameters', 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 1, 
 CONCAT('---->circulate: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 1, 
 CONCAT('---->circulate: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 4, 2), 
 'catalogue - All parameters', 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 2, 
 CONCAT('---->catalogue: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 2, 
 CONCAT('---->catalogue: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 8, 2), 
 "parameters - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 3, 
 CONCAT('---->parameters: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 3, 
 CONCAT('---->parameters: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 16, 2), 
 "borrowers - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 4, 
 CONCAT('---->borrowers: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 4, 
 CONCAT('---->borrowers: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 32, 2), 
 "permissions - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 5, 
 CONCAT('---->permissions: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 5, 
 CONCAT('---->permissions: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 64, 2), 
 "reserveforothers - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 6, 
 CONCAT('---->reserveforothers: ', permissions.code), 
 
 ) ORDER BY permissions.code SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 6, 
 CONCAT('---->reserveforothers: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 128, 2), 
 'FLAG7 Uh-oh', 
 
 ), 
 If( 
 MOD(borrowers.flags DIV 256, 2), 
 'FLAG8 Uh-oheditcatalogue - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 9, 
 CONCAT('---->editcatalogue: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT If( 
 user_permissions.module_bit = 9, 
 CONCAT('---->editcatalogue: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 1024, 2), 
 "updatecharges - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 10, 
 CONCAT('---->updatecharges: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 10, 
 CONCAT('---->updatecharges: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 2048, 2), 
 "acquisition - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 11, 
 CONCAT('---->acquisition: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 11, 
 CONCAT('---->acquisition: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 4096, 2), 
 "suggestions - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 12, 
 CONCAT('---->suggestions: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 12, 
 CONCAT('---->suggestions: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 8192, 2), 
 "tools - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 13, 
 CONCAT('---->tools: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 13, 
 CONCAT('---->tools: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 16384, 2), 
 "editauthorities - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 14, 
 CONCAT('---->editauthorities: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 14, 
 CONCAT('---->editauthorities: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 32768, 2), 
 "serials - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 15, 
 CONCAT('---->serials: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 15, 
 CONCAT('---->serials: ', 
 permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 65536, 2), 
 "reports - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 16, 
 CONCAT('---->reports: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 16, 
 CONCAT('---->reports: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 131072, 2), 
 "staffaccess - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 17, 
 CONCAT('---->staffaccess: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 17, 
 CONCAT('---->staffaccess: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 262144, 2), 
 "coursereserves - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 18, 
 CONCAT('---->coursereserves: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 18, 
 CONCAT('---->coursereserves: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 524288, 2), 
 "plugins - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 19, 
 CONCAT('---->plugins: ', 
 permissions.code 
 ), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 19, 
 CONCAT('---->plugins: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 1048576, 2), 
 "lists - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 20, 
 CONCAT('---->lists: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 20, 
 CONCAT('---->lists: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 2097152, 2), 
 "clubs - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 21, 
 CONCAT('---->clubs: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 21, 
 CONCAT('---->clubs: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 4194304, 2), 
 "ill - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 22, 
 CONCAT('---->ill: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 22, 
 CONCAT('---->ill: ', permissions.code), 
 
 ) 
 ORDER BY 
 permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 8388608, 2), 
 "self_check - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 23, 
 CONCAT('---->self_check: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 23, 
 CONCAT('---->self_check: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 16777216, 2), 
 "stockrotation - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 24, 
 CONCAT('---->stockrotation: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 24, 
 CONCAT('---->stockrotation: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 33554432, 2), 
 "cash_management - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 25, 
 CONCAT('---->cash_management: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 25, 
 CONCAT('---->cash_management: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ), 
 If( 
 MOD(borrowers.flags DIV 67108864, 2), 
 "problem_reports - All parameters", 
 IF( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 26, 
 CONCAT('---->problem_reports: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ) = , 
 , 
 CONCAT( 
 GROUP_CONCAT( 
 DISTINCT 
 If( 
 user_permissions.module_bit = 26, 
 CONCAT('---->problem_reports: ', permissions.code), 
 
 ) 
 ORDER BY permissions.code 
 SEPARATOR 
 ), 
 '' 
 ) 
 ) 
 ) 
 ) 
 ) AS PERMISSIONS 
 FROM 
 borrowers 
 LEFT JOIN user_permissions 
 ON borrowers.borrowernumber = user_permissions.borrowernumber 
 LEFT JOIN permissions 
 ON user_permissions.code = permissions.code 
 WHERE 
 ((borrowers.flags > 0) OR 
 (user_permissions.module_bit > 0)) AND 
 borrowers.branchcode LIKE <<Choose a library|branches:all>> 
 GROUP BY 
 borrowers.borrowernumber 
 ORDER BY 
 borrowers.flags MOD 2 DESC, 
 borrowers.branchcode, 
 borrowers.userid, 
 borrowers.categorycode 
==== Accounts with permissions (v22.11)====
* Developer: Christopher Brannon (Coeur d'Alene Public Library / Cooperative Information Network)
* Module: Patrons
* Purpose: Displays accounts with "Staff" permissions at the library you specify
* Version: Developed on Koha 22.11
* Status: Complete
* Notes: Updated with newer permissions and formatting for easier viewing. Highlights critical permissions. Technically I think this should be backwards compatible.
SELECT borrowernumber,cardnumber AS 'Card Number',surname AS 'Last Name',firstname AS 'First Name',categorycode AS 'Type',branchcode AS 'Library',
CONCAT(
if(super !=,'SUPERLIBRARIAN (Can do anything!)',),
if(circulate_main !=,'CIRCULATION: - All',),
if(circulate_subs IS NOT NULL,CONCAT('CIRCULATION:</br/> - ',REPLACE(circulate_subs,',',' - '), ''),),
if(catalogue_main !=,'CATALOGUE: (Can log into staff interface!) - All',),
if(catalogue_subs IS NOT NULL,CONCAT('CATALOGUE: (Can log into staff interface!)</br/> - ',REPLACE(catalogue_subs,',',' - '), ''),),
if(parameters_main !=,'PARAMETERS: (Can change system settings!) - All',),
if(parameters_subs IS NOT NULL,CONCAT('PARAMETERS: (Can change system settings!)</br/> - ',REPLACE(parameters_subs,',',' - '), ''),),
if(borrowers_main !=,'BORROWERS: - All',),
if(borrowers_subs IS NOT NULL,CONCAT('BORROWERS:</br/> - ',REPLACE(borrowers_subs,',',' - '), ''),),
if(permissions_main !=,'PERMISSIONS: (Can change user permissions!) - All',),
if(permissions_subs IS NOT NULL,CONCAT('PERMISSIONS: (Can change user permissions!)</br/> - ',REPLACE(permissions_subs,',',' - '), ''),),
if(reserveforothers_main !=,'RESERVEFOROTHERS: - All',),
if(reserveforothers_subs IS NOT NULL,CONCAT('RESERVEFOROTHERS:</br/> - ',REPLACE(reserveforothers_subs,',',' - '), ''),),
if(editcatalogue_main !=,'EDITCATALOGUE: - All',),
if(editcatalogue_subs IS NOT NULL,CONCAT('EDITCATALOGUE:</br/> - ',REPLACE(editcatalogue_subs,',',' - '), ''),),
if(updatecharges_main !=,'UPDATECHARGES: - All',),
if(updatecharges_subs IS NOT NULL,CONCAT('UPDATECHARGES:</br/> - ',REPLACE(updatecharges_subs,',',' - '), ''),),
if(acquisition_main !=,'ACQUISITION: - All',),
if(acquisition_subs IS NOT NULL,CONCAT('ACQUISITION:</br/> - ',REPLACE(acquisition_subs,',',' - '), ''),),
if(management_main !=,'MANAGEMENT: - All',),
if(management_subs IS NOT NULL,CONCAT('MANAGEMENT:</br/> - ',REPLACE(management_subs,',',' - '), ''),),
if(tools_main !=,'TOOLS: - All',),
if(tools_subs IS NOT NULL,CONCAT('TOOLS:</br/> - ',REPLACE(tools_subs,',',' - '), ''),),
if(editauthorities_main !=,'EDITAUTHORITIES: - All',),
if(editauthorities_subs IS NOT NULL,CONCAT('EDITAUTHORITIES:</br/> - ',REPLACE(editauthorities_subs,',',' - '), ''),),
if(serials_main !=,'SERIALS: - All',),
if(serials_subs IS NOT NULL,CONCAT('SERIALS:</br/> - ',REPLACE(serials_subs,',',' - '), ''),),
if(reports_main !=,'REPORTS: - All',),
if(reports_subs IS NOT NULL,CONCAT('REPORTS:</br/> - ',REPLACE(reports_subs,',',' - '), ''),),
if(staffaccess_main !=,'STAFFACCESS: - All',),
if(staffaccess_subs IS NOT NULL,CONCAT('STAFFACCESS:</br/> - ',REPLACE(staffaccess_subs,',',' - '), ''),),
if(coursereserves_main !=,'COURSERESERVES: - All',),
if(coursereserves_subs IS NOT NULL,CONCAT('COURSERESERVES:</br/> - ',REPLACE(coursereserves_subs,',',' - '), ''),),
if(plugins_main !=,'PLUGINS: - All',),
if(plugins_subs IS NOT NULL,CONCAT('PLUGINS:</br/> - ',REPLACE(plugins_subs,',',' - '), ''),),
if(lists_main !=,'LISTS: - All',),
if(lists_subs IS NOT NULL,CONCAT('LISTS:</br/> - ',REPLACE(lists_subs,',',' - '), ''),),
if(clubs_main !=,'CLUBS: - All',),
if(clubs_subs IS NOT NULL,CONCAT('CLUBS:</br/> - ',REPLACE(clubs_subs,',',' - '), ''),),
if(ill_main !=,'ILL: - All',),
if(ill_subs IS NOT NULL,CONCAT('ILL:</br/> - ',REPLACE(ill_subs,',',' - '), ''),),
if(self_check_main !=,'SELF_CHECK: - All',),
if(self_check_subs IS NOT NULL,CONCAT('SELF_CHECK:</br/> - ',REPLACE(self_check_subs,',',' - '), ''),),
if(stockrotation_main !=,'STOCKROTATION: - All',),
if(stockrotation_subs IS NOT NULL,CONCAT('STOCKROTATION:</br/> - ',REPLACE(stockrotation_subs,',',' - '), ''),),
if(cash_management_main !=,'CASH_MANAGEMENT: - All',),
if(cash_management_subs IS NOT NULL,CONCAT('CASH_MANAGEMENT:</br/> - ',REPLACE(cash_management_subs,',',' - '), ''),),
if(problem_reports_main !=,'PROBLEM_REPORTS: - All',),
if(problem_reports_subs IS NOT NULL,CONCAT('PROBLEM_REPORTS:</br/> - ',REPLACE(problem_reports_subs,',',' - '), ''),),
if(recalls_main !=,'RECALLS: - All',),
if(recalls_subs IS NOT NULL,CONCAT('RECALLS:</br/> - ',REPLACE(recalls_subs,',',' - '), ''),),
if(erm_main !=,'ERM: - All',),
if(erm_subs IS NOT NULL,CONCAT('ERM:</br/> - ',REPLACE(erm_subs,',',' - '), ''),)) Permissions,lastseen AS 'Last seen'
FROM (SELECT b.borrowernumber,b.lastseen,b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@Check:=b.flags AS check_erm,
if(@Check-268435456>=0,@erm:="TRUE",@erm:="") AS erm_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='28' AND borrowernumber=b.borrowernumber) AS erm_subs,
if(@Check-268435456>=0,@Check:=@Check-268435456,@Check) AS check_recalls,
if(@Check-134217728>=0,@recalls:="TRUE",@recalls:="") AS recalls_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='27' AND borrowernumber=b.borrowernumber) AS recalls_subs,
if(@Check-134217728>=0,@Check:=@Check-134217728,@Check) AS check_problem_reports,
if(@Check-67108864>=0,@problem_reports:="TRUE",@problem_reports:="") AS problem_reports_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='26' AND borrowernumber=b.borrowernumber) AS problem_reports_subs,
if(@Check-67108864>=0,@Check:=@Check-67108864,@Check) AS check_cash_management,
if(@Check-33554432>=0,@cash_management:="TRUE",@cash_management:="") AS cash_management_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='25' AND borrowernumber=b.borrowernumber) AS cash_management_subs,
if(@Check-33554432>=0,@Check:=@Check-33554432,@Check) AS check_stockrotation,
if(@Check-16777216>=0,@stockrotation:="TRUE",@stockrotation:="") AS stockrotation_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='24' AND borrowernumber=b.borrowernumber) AS stockrotation_subs,
if(@Check-16777216>=0,@Check:=@Check-16777216,@Check) AS check_self_check,
if(@Check-8388608>=0,@self_check:="TRUE",@self_check:="") AS self_check_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='23' AND borrowernumber=b.borrowernumber) AS self_check_subs,
if(@Check-8388608>=0,@Check:=@Check-8388608,@Check) AS check_ill,
if(@Check-4194304>=0,@ill:="TRUE",@ill:="") AS ill_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='22' AND borrowernumber=b.borrowernumber) AS ill_subs,
if(@Check-4194304>=0,@Check:=@Check-4194304,@Check) AS check_clubs,
if(@Check-2097152>=0,@clubs:="TRUE",@clubs:="") AS clubs_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='21' AND borrowernumber=b.borrowernumber) AS clubs_subs,
if(@Check-2097152>=0,@Check:=@Check-2097152,@Check) AS check_lists,
if(@Check-1048576>=0,@lists:="TRUE",@lists:="") AS lists_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='20' AND borrowernumber=b.borrowernumber) AS lists_subs,
if(@Check-1048576>=0,@Check:=@Check-1048576,@Check) AS check_plugins,
if(@Check-524288>=0,@plugins:="TRUE",@plugins:="") AS plugins_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='19' AND borrowernumber=b.borrowernumber) AS plugins_subs,
if(@Check-524288>=0,@Check:=@Check-524288,@Check) AS check_coursereserves,
if(@Check-262144>=0,@coursereserves:="TRUE",@coursereserves:="") AS coursereserves_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='18' AND borrowernumber=b.borrowernumber) AS coursereserves_subs,
if(@Check-262144>=0,@Check:=@Check-262144,@Check) AS check_staffaccess,
if(@Check-131072>=0,@staffaccess:="TRUE",@staffaccess:="") AS staffaccess_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='17' AND borrowernumber=b.borrowernumber) AS staffaccess_subs,
if(@Check-131072>=0,@Check:=@Check-131072,@Check) AS check_reports,
if(@Check-65536>=0,@reports:="TRUE",@reports:="") AS reports_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) AS reports_subs,
if(@Check-65536>=0,@Check:=@Check-65536,@Check) AS check_serials,
if(@Check-32768>=0,@serials:="TRUE",@serials:="") AS serials_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) AS serials_subs,
if(@Check-32768>=0,@Check:=@Check-32768,@Check) AS check_editauthorities,
if(@Check-16384>=0,@editauthorities:="TRUE",@editauthorities:="") AS editauthorities_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='14' AND borrowernumber=b.borrowernumber) AS editauthorities_subs,
if(@Check-16384>=0,@Check:=@Check-16384,@Check) AS check_tools,
if(@Check-8192>=0,@tools:="TRUE",@tools:="") AS tools_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber) AS tools_subs,
if(@Check-8192>=0,@Check:=@Check-8192,@Check) AS check_management,
if(@Check-4096>=0,@management:="TRUE",@management:="") AS management_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='12' AND borrowernumber=b.borrowernumber) AS management_subs,
if(@Check-4096>=0,@Check:=@Check-4096,@Check) AS check_acquisition,
if(@Check-2048>=0,@acquisition:="TRUE",@acquisition:="") AS acquisition_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) AS acquisition_subs,
if(@Check-2048>=0,@Check:=@Check-2048,@Check) AS check_updatecharges,
if(@Check-1024>=0,@updatecharges:="TRUE",@updatecharges:="") AS updatecharges_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='10' AND borrowernumber=b.borrowernumber) AS updatecharges_subs,
if(@Check-1024>=0,@Check:=@Check-1024,@Check) AS check_editcatalogue,
if(@Check-512>=0,@editcatalogue:="TRUE",@editcatalogue:="") AS editcatalogue_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) AS editcatalogue_subs,
if(@Check-512>=0,@Check:=@Check-512,@Check) AS check_reserveforothers,
if(@Check-64>=0,@reserveforothers:="TRUE",@reserveforothers:="") AS reserveforothers_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) AS reserveforothers_subs,
if(@Check-64>=0,@Check:=@Check-64,@Check) AS 'check_permissions',
if(@Check-32>=0,@permissions:="TRUE",@permissions:="") AS permissions_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='5' AND borrowernumber=b.borrowernumber) AS permissions_subs,
if(@Check-32>=0,@Check:=@Check-32,@Check) AS check_borrowers,
if(@Check-16>=0,@borrowers:="TRUE",@borrowers:="") AS borrowers_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='4' AND borrowernumber=b.borrowernumber) AS borrowers_subs,
if(@Check-16>=0,@Check:=@Check-16,@Check) AS check_parameters,
if(@Check-8>=0,@parameters:="TRUE",@parameters:="") AS parameters_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) AS parameters_subs,
if(@Check-8>=0,@Check:=@Check-8,@Check) AS check_catalogue,
if(@Check-4>=0,@catalogue:="TRUE",@catalogue:="") AS catalogue_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='2' AND borrowernumber=b.borrowernumber) AS catalogue_subs,
if(@Check-4>=0,@Check:=@Check-4,@Check) AS check_circulate,
if(@Check-2>=0,@circulate:="TRUE",@circulate:="") AS circulate_main,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) AS circulate_subs,
if(@Check-2>=0,@Check:=@Check-2,@Check) AS 'check_super',
if(@Check=1,"TRUE","") AS "super"
FROM borrowers b
WHERE branchcode=<<Accounts for|branches>> AND categorycode=<<Account type|categorycode>>) AS MainFlags
WHERE MainFlags.check_erm > "0" OR MainFlags.borrowernumber IN (SELECT DISTINCT borrowernumber FROM user_permissions)
ORDER BY surname, firstname ASC
==== Show permission bits and flags with corresponding flag names and flag descriptions ====
* Developer: George H. Williams (Northeast Kansas Library System)
* Module: Patrons
* Purpose: Helps identifying bits and flags when writing reports regarding patron permissions
* Version: Developed on Koha 22.05
* Status: Complete
SELECT
 userflags.bit,
 If(userflags.bit < 7, userflags.bit + 1, userflags.bit) AS HTML_FLAG_NUMBER,
 power(2, userflags.bit) AS FLAGS,
 userflags.flag,
 userflags.flagdesc,
 userflags.defaulton
FROM
 userflags
== Active Patrons ==
==== List Patrons with the SEEN attribute ====
* Developer: Brenda Turnbull, LiveWire CIC
* Module: Statistical (Patrons)
* Purpose: List the patrons who have used eBooks or public computers (recorded by the SEEN attribute) for a specified date. 
* Status: Complete
SELECT a.borrowernumber, a.code, a.attribute as DateSeen, p.surname, p.firstname, p.cardnumber
 from borrower_attributes a
LEFT JOIN borrowers p USING (borrowernumber) 
Where a.code = 'SEEN' and STR_TO_DATE(a.attribute,'%d-%b-%Y') =<< date|date>>
==== List Active Patrons ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Statistical (Circulation, Reports)
* Purpose: List Active Patrons since a specific date
* Status: Complete
SELECT DISTINCT surname, firstname, cardnumber, email, address, 
 address2, city, state, zipcode 
FROM borrowers
WHERE borrowernumber IN 
 (SELECT borrowernumber 
 FROM statistics 
 WHERE borrowernumber = borrowernumber 
 AND datetime >= <<Has activity since (YYYY-MM-dd)|date>>) 
order by surname, firstname 
==== Count Active Patrons ====
* Developer: Mike Hafen
* Module: Patrons
* Purpose: A report for finding patrons who are checking out materials
* Status: Completed
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber)
FROM (
 SELECT issuedate, borrowernumber FROM old_issues
 UNION ALL
 SELECT issuedate, borrowernumber FROM issues
) AS all_issues
LEFT JOIN borrowers USING (borrowernumber)
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
==== Count of active patrons by category, filtered by date range ====
* Developer: Barton Chittenden, ByWater Solutions
* Module: Statistical (Circulation, Reports)
* Purpose: Break down count of active borrowers by category
* Status: Complete
SELECT
 count(*) AS count,
 categorycode
FROM
 borrowers
WHERE
 borrowernumber IN (
 SELECT
 borrowernumber
 FROM
 statistics
 WHERE
 DATE( datetime ) BETWEEN <<Has activity between (YYYY-MM-dd)|date>> AND <<and|date>>
 )
GROUP BY
 categorycode
==== Count Active Patrons by Category for a Specific Month ====
* Developer: Jesse Weaver
* Module: Statistical (Circulation, Reports)
* Purpose: Count Active Patrons by Category for a Specific Month
* Status: Complete
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) 
FROM old_issues
 LEFT JOIN borrowers USING (borrowernumber) 
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
==== List Active Patrons with over $20 in fines ====
* Developer: Nick Clemens, ByWater Solutions
* Module: Statistical (Circulation, Reports)
* Purpose: List Patrons active in 6 months with fines over $20
* Status: Complete
SELECT
COUNT(DISTINCT borrowernumber)
FROM borrowers
LEFT JOIN statistics USING (borrowernumber)
LEFT JOIN
(SELECT borrowernumber, 1 AS highfines
FROM accountlines
GROUP BY borrowernumber HAVING SUM(amountoutstanding) > 20)
finesum USING (borrowernumber)
WHERE
type IN ('issue','renew') AND
DATE(datetime) > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND
highfines=1
==== Count of Renewed Memberships in Date Range ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Action logs
* Purpose: Gives a count of renewed memberships in specified date range
* Status: Complete
SELECT COUNT(timestamp) AS 'Renewed Memberships' 
FROM action_logs
WHERE DATE(timestamp) BETWEEN <<Entre (aaaa-mm-jj)|date>> 
 AND <<et (aaaa-mm-jj)|date>>
AND info LIKE 'Membership renewed'
==== Renewed Memberships in Date Range with Patron Details ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Action logs
* Purpose: Gives a list of renewed memberships in specified date range with patron info (cardnumber, first & last name)
* Status: Complete
SELECT
 CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',borrowernumber,'\">',cardnumber,'</a>') AS "Cardnumber",
 surname AS "Last name",
 firstname AS "First name"
FROM borrowers
RIGHT JOIN action_logs ON borrowers.borrowernumber=action_logs.object
WHERE action_logs.action LIKE 'renew' AND action_logs.module LIKE 'members' AND date(action_logs.timestamp) BETWEEN <<Between|date>> AND <<and|date>>
ORDER BY surname
==== Patrons Who Borrowed More Than X Documents in Date Range ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Borrowers, Circulation
* Purpose: Gives a list of patrons who borrowed more than a specified number of documents in a specified date range
* Status: Complete
SELECT
 b.surname AS 'Last Name',
 b.firstname AS 'First Name',
 CASE b.categorycode --Enter your own patron category codes here
 WHEN 'ADMIN' THEN 'Administration'
 WHEN 'ADULTE' THEN 'Adulte - Résident'
 WHEN 'ADULTE-EXT' THEN 'Adulte Non-Résident'
 WHEN 'CONSULT' THEN 'Consultations sur place'
 WHEN 'EMPMASSIF' THEN 'Emprunts massifs'
 WHEN 'INACTIF' THEN 'Inactif'
 WHEN 'JEUNE-EXT' THEN 'Jeune - Non-Résident'
 WHEN 'JEUNE' THEN 'Jeune - Résident'
 WHEN 'ORGANISME' THEN 'Organismes'
 WHEN 'PROF' THEN 'Professionnel'
 WHEN 'PROVISOIRE' THEN 'Provisoire'
 WHEN 'AVERIFIER' THEN 'À vérifier'
 ELSE 'Error' END AS 'Patron Category',
 b.cardnumber AS "Card number",
 COUNT(i.issue_id) AS 'Loan qty'
FROM
 borrowers b
LEFT JOIN
 (SELECT * from issues UNION SELECT * from old_issues) i
 USING (borrowernumber)
WHERE
 i.issuedate BETWEEN <<Between|date>> AND <<and|date>>
GROUP BY
 b.borrowernumber
 HAVING COUNT(i.issue_id) > <<Minimum qty of loans>>
ORDER BY
 surname, firstname
==== Count of Patrons by Category Who Borrowed More Than X Documents in Date Range ====
* Developer: David Bourgault, inLibro
* Module: Borrowers, Circulation
* Purpose: Gives a count of patrons per patron category who borrowed more than a specified number of documents in a specified date range
* Status: Complete
SELECT
 CASE categorycode --Enter your own patron category codes here
 WHEN 'ADMIN' THEN 'Administration'
 WHEN 'ADULTE' THEN 'Adulte - Résident'
 WHEN 'ADULTE-EXT' THEN 'Adulte Non-Résident'
 WHEN 'CONSULT' THEN 'Consultations sur place'
 WHEN 'EMPMASSIF' THEN 'Emprunts massifs'
 WHEN 'INACTIF' THEN 'Inactif'
 WHEN 'JEUNE-EXT' THEN 'Jeune - Non-Résident'
 WHEN 'JEUNE' THEN 'Jeune - Résident'
 WHEN 'ORGANISME' THEN 'Organismes'
 WHEN 'PROF' THEN 'Professionnel'
 WHEN 'PROVISOIRE' THEN 'Provisoire'
 WHEN 'AVERIFIER' THEN 'À vérifier'
 ELSE 'Erreur' 
 END AS 'Category',
 count(*) AS "User Count" FROM
 (
 SELECT b.categorycode, count(i.issue_id) FROM borrowers b
 LEFT JOIN
 (
 SELECT * FROM issues
 UNION
 SELECT * FROM old_issues
 ) i
 USING (borrowernumber)
 WHERE i.issuedate BETWEEN <<Between|date>> AND <<and|date>>
 GROUP BY b.borrowernumber 
 HAVING COUNT(i.issue_id) > <<Minimum qty of loans>>
 ) c
GROUP BY categorycode
ORDER BY categorycode
==== Patrons renewed within a date range ====
* Developer: Kelly McElligott, ByWater Solutions
* Module: Borrowers
* Purpose: Lists patrons renewed within a given date range
* Status: Complete
SELECT firstname, surname, dateenrolled, date_renewed, cardnumber
FROM borrowers
WHERE date_renewed BETWEEN <<Start date|date>> AND <<End date|date>>
== Expired/ Deleted Patrons ==
==== Count of Expired Patrons ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Count of patrons who's cards have expired before today
* Status: Complete
SELECT COUNT(cardnumber) as count
FROM borrowers 
WHERE dateexpiry > <<Today's Date (yyyy-mm-dd)|date>>
==== Expired Patrons without Checkouts ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons 
* Purpose: List patrons expired in a specific year who do not currently have any checkouts
* Status: Complete 
 SELECT surname, firstname, borrowernumber 
 FROM borrowers 
 WHERE borrowernumber 
 NOT IN (SELECT borrowernumber FROM issues) 
 AND YEAR(dateexpiry) = <<Year>>
==== Expired patrons with Checkouts====
* Developer: David Roberts, PTFS Europe
* Module: Patrons
* Purpose: List of expired patrons with loans
* Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, surname, firstname 
 FROM borrowers 
 WHERE borrowernumber IN (SELECT borrowernumber FROM issues) 
 AND dateexpiry <= NOW()
==== Expired Patrons and Funds Owed ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: A list of expired patrons with the money they owe and their guarantor information
* Status: Complete
SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, 
 p.dateexpiry, IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),) as guarantor, 
 p.relationship, ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') as due
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
left join borrowers g on (p.borrowernumber=g.borrowernumber)
WHERE p.dateexpiry < NOW() 
group by p.borrowernumber
ORDER BY p.dateexpiry asc
==== Count of patrons whose cards will expire after a specific date, group by category ====
* Developer: E. Guertin
* Module: Patrons
* Purpose: Count of patrons with valid card at a specific date (expiration after a specific date), count by patron category. 
* Status: Complete
SELECT categorycode, COUNT(*) 
FROM borrowers
WHERE dateexpiry > <<YYYY-MM-DD>>
GROUP BY categorycode
==== Count of deleted patrons ====
* Developer: George H. Williams (Northeast Kansas Library System)
* Module: Patrons
* Purpose: Count patrons deleted during a specified date range grouped by home branch and category
* Status: Complete
* Version: Should work in any version provided you have the BorrowersLog system preference set to log changes to borrowers
* Notes: If you write the query to say "WHERE action_logs.action = 'DELETE'" Koha will give you an error message saying that "DELETE" is an SQL keyword that you can't use in the Koha reporting module. If you write it, instead, to say "WHERE actionlogs.action LIKE 'DELET%'" you can work around the keyword issue because you're not using the actual keyword.
SELECT
 deletedborrowers.branchcode AS PATRON_HOME_LIBRARY,
 deletedborrowers.categorycode AS PATRON_CATEGORY,
 Count(deletedborrowers.borrowernumber) AS COUNT_OF_PATRONS_DELETED
FROM
 action_logs JOIN
 deletedborrowers
 ON deletedborrowers.borrowernumber = action_logs.object
WHERE
 (action_logs.timestamp BETWEEN <<between the beginning of the day on "START DATE"|date>> AND (<<and the end of the day on "END DATE"|date>>+ INTERVAL 1 DAY)) AND
 action_logs.action LIKE 'DELET%'
GROUP BY
 PATRON_HOME_LIBRARY,
 PATRON_CATEGORY
 WITH ROLLUP
==== List of patrons not using the OPAC or SIP ====
* Developer: Andrew Fuerste-Henry (ByWater Solutions)
* Module: Patrons
* Purpose: List patrons who last logged into the OPAC or connected via SIP more than 12 months ago
* Status: Complete
* Version: 18.11
SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, 
	cardnumber, 
 concat(surname,', ',firstname) as name, 
 lastseen
FROM borrowers
WHERE (date(lastseen)<=date_sub(curdate(), interval 12 month) or lastseen is null)
order by lastseen
==== List of patrons expired in date range ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Patrons
* Purpose: List of patrons whose card expires in a date range (past or future). (Note: I put AS "" in my reports to rename columns since my clients don't necessarily speak English. I left them here, but you can remove them) 
* Status: Complete
* Version: 19.05
SELECT
 cardnumber AS "Card number",
 surname AS "Surname",
 firstname AS "First name",
 dateexpiry AS "Expiry date",
 CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">View patron file</a>' ) AS ""
FROM
 borrowers
WHERE
 dateexpiry BETWEEN <<Expires bewteen|date>> AND <<and|date>>
==== List of patrons expired in date range (just cardnumbers for batch modification) ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Patrons
* Purpose: List of patrons whose card expires in a date range (past or future).
* Status: Complete
* Version: 19.05
SELECT
 cardnumber
FROM
 borrowers
WHERE
 dateexpiry BETWEEN <<Expires bewteen|date>> AND <<and|date>>
== Patron Characteristics ==
==== Patrons with All Attribute Values====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Patron list with the value of all of their custom patron attributes
* Status: Complete
SELECT p.surname, p.firstname, p.cardnumber, a.code, a.attribute 
FROM borrowers p
LEFT JOIN borrower_attributes a using (borrowernumber)
group by a.attribute 
order by p.surname, p.firstname asc
==== Patrons with a Specific Attribute Code ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Patron list with the value of one of their custom patron attributes (student id)
* Status: Complete
SELECT p.surname, p.firstname, 
 p.cardnumber, a.attribute as 'Attribute' 
FROM borrowers p
LEFT JOIN borrower_attributes a using (borrowernumber) 
WHERE a.code = <<Attribute Code>>
==== Patrons with a Specific Attribute Value ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Email list for patrons with a specific attribute value
* Status: Complete
SELECT p.email
FROM borrowers p
LEFT JOIN borrower_attributes a using (borrowernumber)
WHERE a.code = 'NEWSLETTER' and a.attribute='1' and p.email is not null and p.email != ''
==== Patrons with notes or messages ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: Patrons with notes and messages on their accounts
* Status: Completed
SELECT b.cardnumber, b.surname, b.firstname,
b.opacnote, b.borrowernotes, group_concat(distinct m.message separator ', ') as circmesages
FROM borrowers b
left join messages m using (borrowernumber)
WHERE b.branchcode=<<Branch|branches>> AND ((b.opacnote is not
null AND b.opacnote != ) OR (b.borrowernotes is not null AND
b.borrowernotes != ) or (m.message is not null AND
m.message != )) group by b.borrowernumber ORDER BY b.surname asc,
b.firstname asc
==== Patron with messages but no email ====
* Developer: Amy Boisvert, VOKAL
* Module: Patrons
* Purpose: Patrons with email addresses that do not have the patron messaging preference for holds checked.
* Status: Completed
SELECT b.surname, b.firstname, b.cardnumber, b.email
FROM borrowers b
 LEFT JOIN (SELECT p.borrowernumber
 FROM borrower_message_preferences p 
 INNER JOIN borrower_message_transport_preferences t
 ON p.borrower_message_preference_id=t.borrower_message_preference_id
 WHERE p.message_attribute_id=4) e 
 ON b.borrowernumber=e.borrowernumber
WHERE b.branchcode=<<Your branch|branches>>
AND IFNULL(b.email,) <> AND e.borrowernumber IS NULL
==== Search patron messages by keyword, with delete link ====
* Developer: Owen Leonard, Athens County Public Libraries
* Module: Patrons
* Purpose: Returns a list of patrons who have messages with a specific keyword or keyword phrase. Includes a link to delete messages directly.
* Status: Completed
* Notes: The "Remove" link generated by this report will trigger the remove of the corresponding patron message WITHOUT CONFIRMATION.
SELECT CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\">',borrowers.surname,', ',borrowers.firstname,'</a>') AS Name,
	messages.message,
 messages.message_date,
	CONCAT('<a href=\"/cgi-bin/koha/circ/del_message.pl?message_id=',messages.message_id,'&borrowernumber=',borrowers.borrowernumber,'\">Remove</a>') AS `Remove`
FROM borrowers, messages
WHERE borrowers.borrowernumber = messages.borrowernumber
	AND messages.message LIKE CONCAT( '%', <<Keyword phrase>>, '%')
ORDER BY messages.message_date
==== Borrower messaging preferences by branch ====
* Developer: Caroline Cyr La Rose, inLibro (original by Barton Chittenden, ByWater Solutions)
* Module: Patrons
* Purpose: Show messaging preferences for all borrowers at a given branch
* Status: Completed
SELECT 
 borrowers.cardnumber AS "Card number",
 CONCAT(borrowers.surname, ', ', borrowers.firstname) AS "Name",
 borrowers.email AS "Email",
 days_in_advance AS "Days in advance", 
 CASE wants_digest 
 WHEN 0 THEN ""
 WHEN 1 THEN "Digest only"
 ELSE wants_digest END AS "Digest only", 
 message_name AS "Message", 
 group_concat( DISTINCT borrower_message_transport_preferences.message_transport_type SEPARATOR ',') AS "Type"
FROM 
 borrower_message_transport_preferences
 JOIN borrower_message_preferences USING (borrower_message_preference_id)
 JOIN message_attributes USING (message_attribute_id)
 JOIN message_transports USING (message_attribute_id) 
 JOIN borrowers USING (borrowernumber)
WHERE
 borrowers.branchcode = <<Branch|branches>>
GROUP BY borrowernumber, message_name
ORDER BY borrowernumber
==== Patrons with modified messaging preferences ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Patrons
* Purpose: Show patrons whose messaging preferences do not match the defaults for their category
* Status: Completed
SELECT borrowernumber,
	CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', 'click here', '</a>' ) AS link_to_borrower,
 firstname,
 surname,
 categorycode,
 bpg.pref_list as patron_choice,
 cpg.pref_list as category_defaults
FROM (
		SELECT borrowernumber,
			group_concat(bp.prefs ORDER BY left(bp.prefs,1)) as pref_list
		FROM 
			(SELECT borrowernumber,
 	concat(message_attribute_id, ifnull(days_in_advance,' '), wants_digest, group_concat(message_transport_type ORDER BY message_transport_type)) as prefs
			FROM borrower_message_preferences
				LEFT JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
			WHERE borrowernumber is not null
			GROUP BY borrower_message_preference_id) bp
		GROUP BY borrowernumber
	) bpg
	LEFT JOIN borrowers b USING (borrowernumber)
	LEFT JOIN (
				SELECT categorycode,
					group_concat(cp.prefs ORDER BY left(cp.prefs,1)) as pref_list
				FROM 
					(SELECT categorycode,
						concat(message_attribute_id, ifnull(days_in_advance,' '), wants_digest, group_concat(message_transport_type ORDER BY message_transport_type)) as prefs
					FROM borrower_message_preferences
						LEFT JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
					WHERE categorycode is not null
					GROUP BY borrower_message_preference_id) cp
				GROUP BY categorycode
 ) cpg USING (categorycode)
WHERE bpg.pref_list!=cpg.pref_list
==== Patron search by sort1 ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: A search of patrons using the sort1 field that show checkouts and overdues
* Status: Complete
select CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',p.borrowernumber,'\">',p.surname, ', ', p.firstname,'</a>') as patron, p.cardnumber, 
 replace((select count(c.itemnumber) from issues c where p.borrowernumber=c.borrowernumber and c.date_due >= now()),'0',) as checkouts, 
 replace(CONCAT('',(select count(i.itemnumber) from issues i where p.borrowernumber=i.borrowernumber and i.date_due < now()),''),'0',) as overdues 
from borrowers p 
where p.sort1=<<Sort 1 value>> 
order by p.surname, p.firstname
==== Guarantor List ====
* Developer: Bernardo Gonzalez Kriegel 
* Module: Patrons
* Purpose: A list of guarantors and guarantees
* Status: Complete - only works on Koha 19.05 and earlier
SELECT 
 IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),) AS guarantor,
 IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),) AS guarantee
FROM
( 
 SELECT * 
 FROM borrowers
 WHERE guarantorid IS NOT NULL
) AS p
LEFT JOIN borrowers AS g 
 ON p.guarantorid = g.borrowernumber
ORDER BY g.borrowernumber
==== Ex Heavy Borrowers ====
* Developer: Chris Hall for Horowhenua Library Trust, Catalyst
* Module: Borrowers
* Purpose: To show which borrowers, given a certain date range, have transformed from heavy borrowers to light borrowers. The report takes two ranges of dates, calculates the number of issues in each, and according to the thresholds set in the report, shows us borrowers who used to borrow a lot, but now don't borrow as much.
* Status: Completed
SELECT first.borrowernumber, first.firstname, first.surname, first.cardnumber, issues_before, issues_after FROM(
SELECT borrowernumber, firstname, surname, cardnumber, count(old_issues.itemnumber) AS issues_before
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of first range (yyyy-mm-dd)|date>>
AND issuedate < <<End of first range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS first
JOIN (
SELECT borrowernumber, count(old_issues.itemnumber) AS issues_after
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of second range (yyyy-mm-dd)|date>>
AND issuedate < <<End of second range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS second
WHERE first.borrowernumber = second.borrowernumber
AND issues_after < 20 AND issues_before > 60 -- borrowers borrowed fewer than 20 items in the second range, and more than 60 in the first range.
==== Housebound planned deliveries - for choosers ====
* Developer: PTFS Europe
* Module: Circulation
* Purpose: Lists all upcoming housebound deliveries with details of each recipient's preferences so that chooser's can pick relevant material. 
* Status: Completed
SELECT 
 housebound_visit.chooser_brwnumber as "Chooser", 
 housebound_visit.appointment_date as "Delivery date", 
 housebound_visit.day_segment as "Time of day", 
 concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) as "Recipient name", 
 borrowers.cardnumber as "Card number", 
 housebound_profile.fav_itemtypes as "Favourite types", 
 housebound_profile.fav_subjects as "Favourite subjects", 
 housebound_profile.fav_authors as "Favourite authors" 
FROM housebound_visit 
INNER JOIN borrowers on borrowers.borrowernumber=housebound_visit.borrowernumber inner join housebound_profile on housebound_profile.borrowernumber=housebound_visit.borrowernumber 
WHERE appointment_date > CURDATE()
Order BY housebound_visit.chooser_brwnumber ASC
==== Housebound Details for Choosers with Past Checkouts ====
* Developer: ByWater Solutions
* Module: Circulation
* Purpose: Lists all upcoming housebound deliveries with details of each recipient's preferences so that chooser's can pick relevant material as well as past checkouts. 
* Status: Completed
SELECT
housebound_visit.chooser_brwnumber AS "Chooser",
housebound_visit.appointment_date AS "Delivery date",
housebound_visit.day_segment AS "Time of day",
concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) AS "Recipient name",
borrowers.cardnumber AS "Card number",
housebound_profile.fav_itemtypes AS "Favourite types",
housebound_profile.fav_subjects AS "Favourite subjects",
housebound_profile.fav_authors AS "Favourite authors",
group_concat(
concat('',biblio.title, ifnull(biblio.subtitle,),'',' returned:',old_issues.returndate)
separator '') as past_checkouts
FROM housebound_visit
INNER JOIN borrowers ON borrowers.borrowernumber=housebound_visit.borrowernumber INNER JOIN housebound_profile ON housebound_profile.borrowernumber=housebound_visit.borrowernumber
LEFT JOIN old_issues ON (borrowers.borrowernumber=old_issues.borrowernumber and date(old_issues.returndate) > date_sub(curdate(),interval 6 month))
LEFT JOIN items USING (itemnumber)
LEFT JOIN biblio USING (biblionumber)
GROUP BY housebound_visit.borrowernumber
ORDER BY housebound_visit.chooser_brwnumber ASC
==== Housebound planned deliveries - for deliverers ====
* Developer: PTFS Europe
* Module: Circulation 
* Purpose: Lists all upcoming housebound deliveries so that deliverers can plan their visits. 
* Status: Completed
SELECT 
 housebound_visit.deliverer_brwnumber as "Deliverer", 
 housebound_visit.appointment_date as "Delivery date",
 housebound_visit.day_segment as "Time of day", 
 concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) as "Recipient name", 
 borrowers.cardnumber as "Card number", concat(borrowers.address, "\n", borrowers.city, "\n", borrowers.zipcode) as Address 
FROM housebound_visit 
INNER JOIN borrowers on borrowers.borrowernumber=housebound_visit.borrowernumber 
WHERE appointment_date > CURDATE()
ORDER BY housebound_visit.deliverer_brwnumber ASC
==== Enrollment Questions Answers for All Patrons in Club====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Patrons
* Purpose: Shows answers provided at enrollment for all patrons in a given club
* Status: Complete
SELECT borrowernumber, surname, firstname, club_id, clubs.name, club_template_enrollment_fields.name, club_enrollment_fields.value
FROM club_enrollment_fields
LEFT JOIN club_enrollments on (club_enrollment_fields.club_enrollment_id=club_enrollments.id)
LEFT JOIN borrowers using (borrowernumber)
LEFT JOIN clubs on (club_enrollments.club_id=clubs.id)
LEFT JOIN club_template_enrollment_fields on (club_enrollment_fields.club_template_enrollment_field_id=club_template_enrollment_fields.id)
WHERE clubs.id=<<Enter club ID number>> and club_enrollments.date_canceled is null
==== Borrower Relationships (19.11)====
* Developer: Kelly McElligott, ByWater Solutions
* Module: Patrons
* Purpose: Gives Borrower Relationship Information
* Status: Complete
SELECT IFNULL(concat(e.surname, ', ', e.firstname, ' (',e.cardnumber, ')'),) AS Guarantee,IFNULL(concat(r.surname, ', ', r.firstname, ' (',r.cardnumber, ')'),) AS Guarantor, borrower_relationships.relationship
FROM borrower_relationships
LEFT JOIN borrowers e ON borrower_relationships.guarantee_id=e.borrowernumber
LEFT JOIN borrowers r ON borrower_relationships.guarantor_id=r.borrowernumber
ORDER by guarantee_id
==== Show All Guarantors Per Guarantee====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Patrons
* Purpose: List all guarantors for each guarantee
* Status: Complete
SELECT surname, firstname, cardnumber, group_concat(guar_info separator ' / ') as patron_guarantors, concat(b.contactname,', ',b.contactfirstname,', ', b.relationship) as non_patron_guarantor
FROM borrowers b 
left join borrower_relationships br on (b.borrowernumber=br.guarantee_id) 
left join (select borrowernumber, concat(surname,', ', firstname,', ', cardnumber) as guar_info from borrowers) g on (br.guarantor_id=g.borrowernumber)
GROUP BY b.borrowernumber
HAVING (patron_guarantors is not null or non_patron_guarantor is not null)
==== Active Patrons by Sex====
* Developer: Spencer Smith
* Module: Patrons
* Purpose: Returns a count of patrons with activity in a certain date range, grouped by sex
* Status: Complete
SELECT
 count(*) AS count,
 sex
FROM
 borrowers
WHERE
 borrowernumber IN (
 SELECT
 borrowernumber
 FROM
 statistics
 WHERE
 DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
 )
GROUP BY
 sex
== Patrons Gone Wild ==
==== Missing Emails ====
* Developer: Sharon Moreland 
* Module: Patrons 
* Purpose: Missing e-mails 
* Status: Complete 
 SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry 
 FROM borrowers 
 WHERE ' ' IN (email)
==== Duplicate Patrons ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: List of patrons who are potentially duplicates
* Status: Complete
SELECT surname, firstname, GROUP_CONCAT(cardnumber SEPARATOR ', ') AS barcodes,
GROUP_CONCAT(borrowernumber SEPARATOR ', ') AS borrowers 
FROM borrowers 
GROUP BY CONCAT(trim(surname),"/",trim(firstname),"/") 
HAVING COUNT(CONCAT(trim(surname),"/",trim(firstname),"/"))>1
==== Restricted Patrons ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Patrons
* Purpose: List of patrons who have been marked as restricted
* Status: Complete
SELECT cardnumber, surname, firstname,
 debarred, debarredcomment 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> AND debarred is not NULL
ORDER BY surname ASC, firstname ASC
==== Patron without image ====
* Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
* Module: Patrons
* Purpose: To list the patrons whose images have not been uploaded.
* Status: Complete
SELECT cardnumber, borrowernumber, surname, firstname FROM borrowers 
WHERE borrowernumber
NOT IN (SELECT borrowernumber FROM patronimage)
==== Missing or invalid email format ====
* Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
* Module: Patrons
* Purpose: To list the patrons missing or invalid email format.
* Status: Complete
SELECT surname as Surname, firstname as "First Name", cardnumber as "Card Number", email as Email
FROM borrowers
WHERE email NOT LIKE '%_@__%.__%'
ORDER BY email DESC
==== Heavily Indebted Patrons ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Accounts
* Purpose: Shows patron info of patrons owing more than X $
* Status: Completed
SELECT
 borrowers.cardnumber AS 'Card number',
 borrowers.surname AS 'Last name',
 borrowers.firstname AS 'First name',
 borrowers.phone AS 'Phone number',
 CONCAT(borrowers.streetnumber, ' ', borrowers.address, ' ', borrowers.address2, '', borrowers.city, ' (', borrowers.state, ') ', borrowers.zipcode) AS 'Address',
 CONCAT(ROUND(SUM(accountlines.amountoutstanding), 2), ' $') AS 'Amount due',
 CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">See patron file</a>' ) AS ''
FROM accountlines
LEFT JOIN borrowers USING (borrowernumber)
GROUP BY borrowers.borrowernumber
HAVING SUM(accountlines.amountoutstanding) >= <<Owes more than>>
==== Patrons with too many login attempts ====
* Developer: Laura O'Neil, ByWater Solutions
* Module: Patrons
* Purpose: Patrons whose accounts have been locked due to too many FailedLoginAttempts (23.05)
* Status: Completed
SELECT p.borrowernumber, p.cardnumber, p.surname, p.firstname, p.categorycode, p.login_attempts
FROM borrowers p
WHERE p.login_attempts >= (select value from systempreferences sp where sp.variable='FailedLoginAttempts')
ORDER BY p.surname, p.firstname
== Misc Reports ==
==== List of all patron's reviews ====
* Developer: Marie-Luce Laflamme, inLibro
* Module: Patrons
* Purpose: List of all patron reviews published on the opac-showreviews.pl page
* Status: Complete
SELECT
 r.reviewid,
 r.borrowernumber,
 b.surname,
 b.firstname,
 b.email,
 r.biblionumber,
 r.review,
 r.approved,
 r.datereviewed
FROM
 reviews r
 LEFT JOIN borrowers b using (borrowernumber)
Category:Documentation