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

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-----<br />', 
        If( 
          MOD(borrowers.flags DIV 2, 2), 
          '<br />circulate - All parameters<br />', 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 1, 
                CONCAT('<br />---->circulate: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 1, 
                  CONCAT('<br />---->circulate: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 4, 2), 
          '<br />catalogue - All parameters<br />', 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 2, 
                CONCAT('<br />---->catalogue: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 2, 
                  CONCAT('<br />---->catalogue: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 8, 2), 
          "<br />parameters - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 3, 
                CONCAT('<br />---->parameters: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 3, 
                  CONCAT('<br />---->parameters: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 16, 2), 
          "<br />borrowers - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 4, 
                CONCAT('<br />---->borrowers: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 4, 
                  CONCAT('<br />---->borrowers: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 32, 2), 
          "<br />permissions - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 5, 
                CONCAT('<br />---->permissions: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 5, 
                  CONCAT('<br />---->permissions: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 64, 2), 
          "<br />reserveforothers - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 6, 
                CONCAT('<br />---->reserveforothers: ', permissions.code), 
                '' 
              ) ORDER BY permissions.code SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 6, 
                  CONCAT('<br />---->reserveforothers: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 128, 2), 
          '<br />FLAG7 Uh-oh<br />', 
          '' 
        ), 
        If( 
          MOD(borrowers.flags DIV 256, 2), 
          '<br />FLAG8 Uh-oh<br /', 
          '' 
        ), 
        If( 
          MOD(borrowers.flags DIV 512, 2), 
          "<br />editcatalogue - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 9, 
                CONCAT('<br />---->editcatalogue: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT If( 
                  user_permissions.module_bit = 9, 
                  CONCAT('<br />---->editcatalogue: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 1024, 2), 
          "<br />updatecharges - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 10, 
                CONCAT('<br />---->updatecharges: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 10, 
                  CONCAT('<br />---->updatecharges: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 2048, 2), 
          "<br />acquisition - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 11, 
                CONCAT('<br />---->acquisition: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 11, 
                  CONCAT('<br />---->acquisition: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 4096, 2), 
          "<br />suggestions - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 12, 
                CONCAT('<br />---->suggestions: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 12, 
                  CONCAT('<br />---->suggestions: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 8192, 2), 
          "<br />tools - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 13, 
                CONCAT('<br />---->tools: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 13, 
                  CONCAT('<br />---->tools: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 16384, 2), 
          "<br />editauthorities - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 14, 
                CONCAT('<br />---->editauthorities: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 14, 
                  CONCAT('<br />---->editauthorities: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 32768, 2), 
          "<br />serials - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 15, 
                CONCAT('<br />---->serials: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 15, 
                  CONCAT('<br />---->serials: ', 
                    permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 65536, 2), 
          "<br />reports - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 16, 
                CONCAT('<br />---->reports: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 16, 
                  CONCAT('<br />---->reports: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 131072, 2), 
          "<br />staffaccess - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 17, 
                CONCAT('<br />---->staffaccess: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 17, 
                  CONCAT('<br />---->staffaccess: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 262144, 2), 
          "<br />coursereserves - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 18, 
                CONCAT('<br />---->coursereserves: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 18, 
                  CONCAT('<br />---->coursereserves: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 524288, 2), 
          "<br />plugins - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 19, 
                CONCAT('<br />---->plugins: ', 
                  permissions.code 
                ), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 19, 
                  CONCAT('<br />---->plugins: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 1048576, 2), 
          "<br />lists - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 20, 
                CONCAT('<br />---->lists: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 20, 
                  CONCAT('<br />---->lists: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 2097152, 2), 
          "<br />clubs - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 21, 
                CONCAT('<br />---->clubs: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 21, 
                  CONCAT('<br />---->clubs: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 4194304, 2), 
          "<br />ill - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 22, 
                CONCAT('<br />---->ill: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 22, 
                  CONCAT('<br />---->ill: ', permissions.code), 
                  '' 
                ) 
                ORDER BY 
                permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 8388608, 2), 
          "<br />self_check - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 23, 
                CONCAT('<br />---->self_check: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 23, 
                  CONCAT('<br />---->self_check: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 16777216, 2), 
          "<br />stockrotation - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 24, 
                CONCAT('<br />---->stockrotation: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 24, 
                  CONCAT('<br />---->stockrotation: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR ''), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 33554432, 2), 
          "<br />cash_management - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 25, 
                CONCAT('<br />---->cash_management: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 25, 
                  CONCAT('<br />---->cash_management: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ), 
        If( 
          MOD(borrowers.flags DIV 67108864, 2), 
          "<br />problem_reports - All parameters<br />", 
          IF( 
            GROUP_CONCAT( 
              DISTINCT 
              If( 
                user_permissions.module_bit = 26, 
                CONCAT('<br />---->problem_reports: ', permissions.code), 
                '' 
              ) 
              ORDER BY permissions.code 
              SEPARATOR '' 
            ) = '', 
            '', 
            CONCAT( 
              GROUP_CONCAT( 
                DISTINCT 
                If( 
                  user_permissions.module_bit = 26, 
                  CONCAT('<br />---->problem_reports: ', permissions.code), 
                  '' 
                ) 
                ORDER BY permissions.code 
                SEPARATOR '' 
              ), 
              '<br />' 
            ) 
          ) 
        ) 
      ) 
    ) 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 !='','<span style="color: red;"><strong>SUPERLIBRARIAN (Can do anything!)</strong></span>',''),
if(circulate_main !='','<span><strong>CIRCULATION:</strong></span><br/><em><em> - All</em></em><br/>',''),
if(circulate_subs IS NOT NULL,CONCAT('<span><strong>CIRCULATION:</strong></span></br/><em> - ',REPLACE(circulate_subs,',','<br/> - '), '</em><br/>'),''),
if(catalogue_main !='','<span style="color: red;"><strong>CATALOGUE: (Can log into staff interface!)</strong></span><br/><em> - All</em><br/>',''),
if(catalogue_subs IS NOT NULL,CONCAT('<span style="color: red;"><strong>CATALOGUE: (Can log into staff interface!)</strong></span></br/><em> - ',REPLACE(catalogue_subs,',','<br/> - '), '</em><br/>'),''),
if(parameters_main !='','<span style="color: red;"><strong>PARAMETERS: (Can change system settings!)</strong></span><br/><em> - All</em><br/>',''),
if(parameters_subs IS NOT NULL,CONCAT('<span style="color: red;"><strong>PARAMETERS: (Can change system settings!)</strong></span></br/><em> - ',REPLACE(parameters_subs,',','<br/> - '), '</em><br/>'),''),
if(borrowers_main !='','<span><strong>BORROWERS:</strong></span><br/><em> - All</em><br/>',''),
if(borrowers_subs IS NOT NULL,CONCAT('<span><strong>BORROWERS:</strong></span></br/><em> - ',REPLACE(borrowers_subs,',','<br/> - '), '</em><br/>'),''),
if(permissions_main !='','<span style="color: red;"><strong>PERMISSIONS: (Can change user permissions!)</strong></span><br/><em> - All</em><br/>',''),
if(permissions_subs IS NOT NULL,CONCAT('<span style="color: red;"><strong>PERMISSIONS: (Can change user permissions!)</strong></span></br/><em> - ',REPLACE(permissions_subs,',','<br/> - '), '</em><br/>'),''),
if(reserveforothers_main !='','<span><strong>RESERVEFOROTHERS:</strong></span><br/><em> - All</em><br/>',''),
if(reserveforothers_subs IS NOT NULL,CONCAT('<span><strong>RESERVEFOROTHERS:</strong></span></br/><em> - ',REPLACE(reserveforothers_subs,',','<br/> - '), '</em><br/>'),''),
if(editcatalogue_main !='','<span><strong>EDITCATALOGUE:</strong></span><br/><em> - All</em><br/>',''),
if(editcatalogue_subs IS NOT NULL,CONCAT('<span><strong>EDITCATALOGUE:</strong></span></br/><em> - ',REPLACE(editcatalogue_subs,',','<br/> - '), '</em><br/>'),''),
if(updatecharges_main !='','<span><strong>UPDATECHARGES:</strong></span><br/><em> - All</em><br/>',''),
if(updatecharges_subs IS NOT NULL,CONCAT('<span><strong>UPDATECHARGES:</strong></span></br/><em> - ',REPLACE(updatecharges_subs,',','<br/> - '), '</em><br/>'),''),
if(acquisition_main !='','<span><strong>ACQUISITION:</strong></span><br/><em> - All</em><br/>',''),
if(acquisition_subs IS NOT NULL,CONCAT('<span><strong>ACQUISITION:</strong></span></br/><em> - ',REPLACE(acquisition_subs,',','<br/> - '), '</em><br/>'),''),
if(management_main !='','<span><strong>MANAGEMENT:</strong></span><br/><em> - All</em><br/>',''),
if(management_subs IS NOT NULL,CONCAT('<span><strong>MANAGEMENT:</strong></span></br/><em> - ',REPLACE(management_subs,',','<br/> - '), '</em><br/>'),''),
if(tools_main !='','<span><strong>TOOLS:</strong></span><br/><em> - All</em><br/>',''),
if(tools_subs IS NOT NULL,CONCAT('<span><strong>TOOLS:</strong></span></br/><em> - ',REPLACE(tools_subs,',','<br/> - '), '</em><br/>'),''),
if(editauthorities_main !='','<span><strong>EDITAUTHORITIES:</strong></span><br/><em> - All</em><br/>',''),
if(editauthorities_subs IS NOT NULL,CONCAT('<span><strong>EDITAUTHORITIES:</strong></span></br/><em> - ',REPLACE(editauthorities_subs,',','<br/> - '), '</em><br/>'),''),
if(serials_main !='','<span><strong>SERIALS:</strong></span><br/><em> - All</em><br/>',''),
if(serials_subs IS NOT NULL,CONCAT('<span><strong>SERIALS:</strong></span></br/><em> - ',REPLACE(serials_subs,',','<br/> - '), '</em><br/>'),''),
if(reports_main !='','<span><strong>REPORTS:</strong></span><br/><em> - All</em><br/>',''),
if(reports_subs IS NOT NULL,CONCAT('<span><strong>REPORTS:</strong></span></br/><em> - ',REPLACE(reports_subs,',','<br/> - '), '</em><br/>'),''),
if(staffaccess_main !='','<span><strong>STAFFACCESS:</strong></span><br/><em> - All</em><br/>',''),
if(staffaccess_subs IS NOT NULL,CONCAT('<span><strong>STAFFACCESS:</strong></span></br/><em> - ',REPLACE(staffaccess_subs,',','<br/> - '), '</em><br/>'),''),
if(coursereserves_main !='','<span><strong>COURSERESERVES:</strong></span><br/><em> - All</em><br/>',''),
if(coursereserves_subs IS NOT NULL,CONCAT('<span><strong>COURSERESERVES:</strong></span></br/><em> - ',REPLACE(coursereserves_subs,',','<br/> - '), '</em><br/>'),''),
if(plugins_main !='','<span><strong>PLUGINS:</strong></span><br/><em> - All</em><br/>',''),
if(plugins_subs IS NOT NULL,CONCAT('<span><strong>PLUGINS:</strong></span></br/><em> - ',REPLACE(plugins_subs,',','<br/> - '), '</em><br/>'),''),
if(lists_main !='','<span><strong>LISTS:</strong></span><br/><em> - All</em><br/>',''),
if(lists_subs IS NOT NULL,CONCAT('<span><strong>LISTS:</strong></span></br/><em> - ',REPLACE(lists_subs,',','<br/> - '), '</em><br/>'),''),
if(clubs_main !='','<span><strong>CLUBS:</strong></span><br/><em> - All</em><br/>',''),
if(clubs_subs IS NOT NULL,CONCAT('<span><strong>CLUBS:</strong></span></br/><em> - ',REPLACE(clubs_subs,',','<br/> - '), '</em><br/>'),''),
if(ill_main !='','<span><strong>ILL:</strong></span><br/><em> - All</em><br/>',''),
if(ill_subs IS NOT NULL,CONCAT('<span><strong>ILL:</strong></span></br/><em> - ',REPLACE(ill_subs,',','<br/> - '), '</em><br/>'),''),
if(self_check_main !='','<span><strong>SELF_CHECK:</strong></span><br/><em> - All</em><br/>',''),
if(self_check_subs IS NOT NULL,CONCAT('<span><strong>SELF_CHECK:</strong></span></br/><em> - ',REPLACE(self_check_subs,',','<br/> - '), '</em><br/>'),''),
if(stockrotation_main !='','<span><strong>STOCKROTATION:</strong></span><br/><em> - All</em><br/>',''),
if(stockrotation_subs IS NOT NULL,CONCAT('<span><strong>STOCKROTATION:</strong></span></br/><em> - ',REPLACE(stockrotation_subs,',','<br/> - '), '</em><br/>'),''),
if(cash_management_main !='','<span><strong>CASH_MANAGEMENT:</strong></span><br/><em> - All</em><br/>',''),
if(cash_management_subs IS NOT NULL,CONCAT('<span><strong>CASH_MANAGEMENT:</strong></span></br/><em> - ',REPLACE(cash_management_subs,',','<br/> - '), '</em><br/>'),''),
if(problem_reports_main !='','<span><strong>PROBLEM_REPORTS:</strong></span><br/><em> - All</em><br/>',''),
if(problem_reports_subs IS NOT NULL,CONCAT('<span><strong>PROBLEM_REPORTS:</strong></span></br/><em> - ',REPLACE(problem_reports_subs,',','<br/> - '), '</em><br/>'),''),
if(recalls_main !='','<span><strong>RECALLS:</strong></span><br/><em> - All</em><br/>',''),
if(recalls_subs IS NOT NULL,CONCAT('<span><strong>RECALLS:</strong></span></br/><em> - ',REPLACE(recalls_subs,',','<br/> - '), '</em><br/>'),''),
if(erm_main !='','<span><strong>ERM:</strong></span><br/><em> - All</em><br/>',''),
if(erm_subs IS NOT NULL,CONCAT('<span><strong>ERM:</strong></span></br/><em> - ',REPLACE(erm_subs,',','<br/> - '), '</em><br/>'),'')) 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('<div style=\"color:#f11\">',(select count(i.itemnumber) from issues i where p.borrowernumber=i.borrowernumber and i.date_due < now()),'</div>'),'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('<b>',biblio.title, ifnull(biblio.subtitle,''),'</b>',' returned:',old_issues.returndate)
separator '<br>') 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, '<br>', 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)