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: In Discussion --- QA Contact: Testopia <testopia>
Severity: minor    
Priority: P5 - low CC: aleisha, blawlor, dcook, jonathan.druart
Version: Main   
Hardware: All   
OS: All   
GIT URL: 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'
Comment 2 David Cook 2024-12-12 05:08:02 UTC
You could also use: p.auth_method != 'two-factor'
Comment 3 David Cook 2024-12-12 05:08:22 UTC
Tempted to close this one but will leave In Discussion