Bug 37919

Summary: Unable to report on patrons with borrowers.auth_method password
Product: Koha Reporter: David Roberts <david.roberts>
Component: ReportsAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: aleisha, blawlor, jonathan.druart
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 37508    
Bug Blocks:    

Description David Roberts 2024-09-13 14:28:42 UTC
As a result of Bug 37508, it isn't possible to use the reports module to identify which users are MFA enabled using e.g. the following SQL which used to work:

SELECT 
  p.borrowernumber, 
  p.firstname, 
  p.surname, 
  p.cardnumber, 
  p.branchcode, 
  IF(
    power(2, 2) & flags, 
    'yes', 
    'no'
  ) as Staff, 
  IF(
    power(2, 0) & flags, 
    'yes', 
    'no'
  ) as Superlibrarian 
FROM 
  borrowers p 
WHERE 
  p.flags > 0 
  AND p.auth_method = 'password' 
  AND p.dateexpiry > CURDATE() 
HAVING 
  Staff = 'yes' 
  OR Superlibrarian = 'yes' 
ORDER BY 
  p.branchcode
Comment 1 Brendan Lawlor 2024-10-03 19:07:02 UTC
A workaround for this is to change = to LIKE

Change this line:
AND p.auth_method = 'password'

to:
AND p.auth_method LIKE 'passwor'