SQL Reports Patrons

From Koha Wiki

Jump to: navigation, search
Home > Documentation

Patron Reports

Contents

Patron Statistics

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. Engard, 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 Birthday Report

  • Developer: Nicole C. Engard, 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: Nicole C. Engard, ByWater Solutions
  • Module: patrons
  • Purpose: This report shows patrons between the age of 12 and 13
  • Status: Complete
SELECT cardnumber, firstname, surname, dateofbirth, 
      (YEAR(CURDATE( )) - YEAR(dateofbirth) - IF(RIGHT(CURDATE( ),5) < RIGHT(dateofbirth,5),1,0)) AS 'age in years', 
      categorycode 
FROM borrowers 
WHERE DATEDIFF(now(), dateofbirth) < ((13*365)+4) 
      AND DATEDIFF(now(), dateofbirth) > ((12*365)+4)


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,
   GREATEST ( c.upperagelimit, 999 ) 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 NOT BETWEEN MinAge AND MaxAge


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>>

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. Engard, 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. Engard, 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

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. Engard, 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

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. Engard, 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 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

Expired Patrons

Count of Expired Patrons

  • Developer: Nicole C. Engard, 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. Engard, 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 and Funds Owed

  • Developer: Nicole C. Engard, 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.guarantorid=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

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)



Patrons with All Attribute Values

  • Developer: Nicole C. Engard, 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. Engard, 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. Engard, 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 != ''

Duplicate Patrons

  • Developer: Nicole C. Engard, 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. Engard, 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

Patrons with notes or messages

  • Developer: Nicole C. Engard, 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


Patron search by sort1

  • Developer: Nicole C. Engard, 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
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


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

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.
Personal tools