Bug 5527 - Reports: Patrons who haven't checked out misses active users from issues table
Summary: Reports: Patrons who haven't checked out misses active users from issues table
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: rel_3_2
Hardware: All All
: PATCH-Sent (DO NOT USE) major (vote)
Target Milestone: ---
Assignee: Katrin Fischer
QA Contact: Bugs List
URL: /cgi-bin/koha/reports/borrowers_out.pl
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-12-20 15:43 UTC by Katrin Fischer
Modified: 2012-10-25 23:04 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
screenshot of my report settings (14.06 KB, image/png)
2010-12-20 15:43 UTC, Katrin Fischer
Details
Proposed patch (2.15 KB, patch)
2010-12-21 07:47 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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