Bug 5527

Summary: Reports: Patrons who haven't checked out misses active users from issues table
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: ReportsAssignee: Katrin Fischer <katrin.fischer>
Status: CLOSED FIXED QA Contact: Bugs List <koha-bugs>
Severity: major    
Priority: PATCH-Sent (DO NOT USE) CC: chris, julian.maurice, nengard
Version: rel_3_2   
Hardware: All   
OS: All   
URL: /cgi-bin/koha/reports/borrowers_out.pl
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: screenshot of my report settings
Proposed patch

Description Katrin Fischer 2010-12-20 15:43:02 UTC
I compared some of my sql statements for statistics with the results from Koha's built in reports in the reports module.

Currently the 'Patrons who haven't checked out' report does not consider patrons from issues.

If you have a newly registered user, that has checked out his first books in the library his borrowernumber will only appear in issues. This is an active user.

At the moment Koha gets a list of active borrowernumbers only from old_issues and compares that (not in) to all borrowers to get the inactive borrowers.

It should also get the borrowers from issues.

Currently koha asks:

SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber) 
FROM borrowers 
WHERE 1  
AND borrowers.borrowernumber not in 
(SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> 01/01/2010 ) 
group by borrowers.borrowernumber


I think it needs to consider issues too, perhaps something like:

SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber)
FROM borrowers
WHERE 1
AND borrowers.borrowernumber not in
(SELECT DISTINCT borrowernumber FROM (
select borrowernumber from old_issues where old_issues.timestamp> 01/01/2010
union
select borrowernumber from issues where issues.timestamp> 01/01/2010) active_b
)
group by borrowers.borrowernumber


There was some discussion about that on #koha too: 
http://stats.workbuffer.org/irclog/koha/2010-12-20#i_567711
Comment 1 Katrin Fischer 2010-12-20 15:43:37 UTC
Created attachment 2892 [details]
screenshot of my report settings
Comment 2 Katrin Fischer 2010-12-20 16:35:04 UTC
Still fighting with this report:

old_issues.timestamp> 01/01/2010

is not right and brings up a lot of 2009-* dates. :(

Should be:

old_issues.timestamp> "2010-01-01"
Comment 3 Katrin Fischer 2010-12-20 22:23:19 UTC
I was looking at an older version of this script earlier today, but didn't realize. 

I still see some problems:
- the date is not showing up in the query
- the issues table is still not considered

SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber) 
FROM borrowers 
WHERE 1 AND borrowers.borrowernumber not in 
  (SELECT DISTINCT borrowernumber 
  FROM old_issues 
  WHERE borrowernumber IS NOT NULL ) 
  group by borrowers.borrowernumber
Comment 4 Katrin Fischer 2010-12-21 07:47:24 UTC
Created attachment 2897 [details] [review]
Proposed patch
Comment 5 MJ Ray (software.coop) 2011-01-11 12:57:39 UTC
This bug is mentioned in:
Bug 5527: Fix some problems in inactive	borrowers report http://lists.koha-community.org/pipermail/koha-patches/2010-December/013449.html
Comment 6 Chris Cormack 2011-03-11 19:44:00 UTC
Pushed, please test and mark resolved
Comment 7 MJ Ray (software.coop) 2011-04-04 08:10:11 UTC
This bug is mentioned in:
Bug 5527: Fix some problems in	inactive borrowers report http://lists.koha-community.org/pipermail/koha-patches/2011-March/014004.html