Bug 7021

Summary: Add patron category to the statistics table
Product: Koha Reporter: Nicole C. Engard <nengard>
Component: CirculationAssignee: Emmi Takkinen <emmi.takkinen>
Status: CLOSED FIXED QA Contact: Kyle M Hall (khall) <kyle>
Severity: enhancement    
Priority: P5 - low CC: barton, bugzilla, caroline.cyr-la-rose, emmi.takkinen, george, gmcharlt, jonathan.druart, kyle, lucas, martin.renvoize, mathsabypro, olli-antti.kivilahti, sally.healey, wizzyrea
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30928
Change sponsored?: --- Patch complexity: Medium patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00
Circulation function:
Bug Depends on:    
Bug Blocks: 22794    
Attachments: Bug_7021-Add_borrowers_category_to_statistics_usercode
Bug 7021 - patron category in the statistics table
Bug 7021: Reintroduce usercode column
DO NOT PUSH! Schema changes
Bug 7021: patron category in the statistics table
Bug 7021: patron category in the statistics table
Bug 7021: patron category in the statistics table
Bug 7021: Reintroduce usercode column
Bug 7021: patron category in the statistics table
Bug 7021: Reintroduce usercode column
Bug 7021: Patron category in the statistics table
Bug 7021: Terminology - usercode -> categorycode
Bug 7021: DO NOT PUSH! Schema changes
Bug 7021: Remove superflous 'open'
Bug 7021: Catch new introductions of UpdateStats
Bug 7021: Reintroduce usercode column
Bug 7021: Patron category in the statistics table
Bug 7021: Terminology - usercode -> categorycode
Bug 7021: DO NOT PUSH! Schema changes
Bug 7021: Remove superflous 'open'
Bug 7021: Catch new introductions of UpdateStats
Bug 7021: (QA follow-up): Minor code cleanup
Bug 7021: Move to new atomic update structure
Bug 7021: Move to new atomic update structure
Bug 7021: (QA followup) Add comment to new database column
Bug 7021: (QA follow-up) Fix typo

Description Nicole C. Engard 2011-10-13 11:59:01 UTC
The patron category code should be in the statistics table.  We track the item type and item number, but we only track the borrowernumber for the patron and part way through the year the patron's category could change from child to adult - or the patron could be deleted and that will mess with the statistics on circulation/actions based on patron category.

Once that data is added to that table, the circulation stats wizard will need to be updated as well.
Comment 1 Katrin Fischer 2011-10-13 12:00:25 UTC
I like that idea - not only for when patrons are deleted, but also for anonymization. Having the patron category will be great for statistics in those cases.
Comment 2 Liz Rea 2011-11-11 00:58:24 UTC
oh yes, instead of just nuking the borrower out of statistics, we can only null their borrowernumber and leave their category. That would definitely make our librarians a bit happier about anonymization.
Comment 3 Nicole C. Engard 2013-04-15 12:28:42 UTC
I got asked for this again today, so I'm bumping it :)
Comment 4 Mathieu Saby 2013-08-24 11:29:01 UTC
Hello
I have just realized I've created a bug for the same idea, but with other informations to store. Could you take a look? (Bug 10778). If you agree I can write a patch.

M. Saby
Comment 5 Mathieu Saby 2013-09-01 21:52:26 UTC
I copy the content of the bug Bug 10778 and close it, as it is a duplicate
Mathieu

Curently, statistic table contains some information about the item and the borrower (http://schema.koha-community.org/tables/statistics.html):
For the item : 
- itemnumber
- itemtype
- ccode
For the borrower
- borrowernumber
- associatedborrower
- and maybe "usercode" (not documented in schemaspy, so I don't know what it means).

I think we should store more information for the borrower : if the borrower record is deleted, we will not be able to get the useful informations (categorycode...) with a JOIN.

I am not sure of Koha's behavior if the item or biblio is deleted : if the itemnumber and biblionumber is deleted from statistic table, we could also store more information (as homebranch, location, biblio.serial, biblioitems.itemtype)

For the borrowers, I think we need :
- categorycode
- branchcode
- sex
- sort1
- sort2
and optionally the ability to record 3 or 4 custom fields with a syspref (for example, to store the extended attributes).

Do you agree with that ?

M. Saby
Comment 6 Mathieu Saby 2013-09-01 21:52:45 UTC
*** Bug 10778 has been marked as a duplicate of this bug. ***
Comment 7 Katrin Fischer 2013-09-02 07:15:59 UTC
Some of the information proposed by Mathieu is or might be a bit critical - so it would be nice to have a system preference to determine which fields are going to be saved into the statistics table. Maybe we could add the patron category as a first step.
Comment 8 Mathieu Saby 2013-09-08 11:19:14 UTC
(In reply to Katrin Fischer from comment #7)
> Some of the information proposed by Mathieu is or might be a bit critical -
> so it would be nice to have a system preference to determine which fields
> are going to be saved into the statistics table.

I agreee

 Maybe we could add the
> patron category as a first step.

OK for the categorycode in a 1st step. I think the library of the patron could be recorded too. It is not critical.

Do you think we need to store more information about items & biblios too ?

Mathieu
Comment 9 Katrin Fischer 2013-09-08 11:27:11 UTC
I think items and biblio information is stord in the deleted* tables and can be looked up from there - data privacy not a problem here, so the data doesn't need to be deleted regularly.
Comment 10 Mathieu Saby 2013-09-08 13:12:31 UTC
The bug was assigned to Kyle, but he is not in the contacts. I add him.
Kyle, do you still want to work on this?

Mathieu
Comment 11 Mathieu Saby 2013-11-09 15:48:21 UTC
I misread bug's status. It was not assigned. I take it and will propose a patch.
For me some info about items and biblio MUST be recorded in this table. Because item location (like patron category) can change over time.
The case occured in my library network (books moved from one library to one other), and it was complicated to get the original location.

Mathieu
Comment 12 Mathieu Saby 2013-11-09 15:54:21 UTC
I will add
for the item : 
- holdingbranch
- homebranch
- callnumber
- location
- dateaccessioned

for the borrower
- categorycode
- branchcode
- sex
- sort1
- sort2

for the record
- biblioitems.itemtype (as document_type)
- biblio.serial

Other interesting fields could be
items.barcode
items.price
items.replacementprice
items.issues
items.renewal
biblio.title
biblio.author
biblio.publicationyear
biblio.publishercode
and extended attributes of patrons


Mathieu
Comment 13 Mathieu Saby 2014-04-03 16:35:47 UTC
Before working on that, I would like bug 11230 to be tested.

Mathieu
Comment 14 Olli-Antti Kivilahti 2014-07-02 17:08:08 UTC
lol
Comment 15 Olli-Antti Kivilahti 2014-07-02 17:09:43 UTC Comment hidden (obsolete)
Comment 16 Olli-Antti Kivilahti 2014-07-02 17:13:12 UTC
BTW.
I am not very keen on adding lots of content to statistics-table.
Because it already is gettting verrrrry large.
I am not looking forward to it getting larger than absolutely necessary.

We already have old_issues and deleted* to keep as history.

Our SSD for the DB is only 100GB ;)


However I have no data to back up anything. It is possible that suggested modifications won't enlargen the statistics table significantly.
Comment 17 Mathieu Saby 2014-07-03 11:24:14 UTC
(In reply to Olli-Antti Kivilahti from comment #16)
> BTW.
> I am not very keen on adding lots of content to statistics-table.
> Because it already is gettting verrrrry large.
> I am not looking forward to it getting larger than absolutely necessary.
> 
> We already have old_issues and deleted* to keep as history.
> 
> Our SSD for the DB is only 100GB ;)
> 
> 
> However I have no data to back up anything. It is possible that suggested
> modifications won't enlargen the statistics table significantly.

The way Koha is storing statistics is not sufficient at all for a lot of libraries : if you delete some borrower's account, if a borrower's category changes (common in academic libraries), or if you anonymize transactions after a few months (mandatory in some countries), the information stored in statistics table is not sufficient to calculate some figures needed in our annual reports.

However, I'm concerned with the "DB size" issue... Do you know if creating columns in a table but NOT filling them (it could depend on the activation of a syspref) will have a significant effect on the size of the database?

Mathieu
Comment 18 Mathieu Saby 2014-07-03 11:25:27 UTC
By the way, bug 11230 has been signed off.
Comment 19 Olli-Antti Kivilahti 2014-07-04 07:39:15 UTC
Created attachment 29508 [details] [review]
Bug 7021 -  patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!
Comment 20 Olli-Antti Kivilahti 2014-07-04 08:46:49 UTC
Sorry, we are using some ancient version of master by now (but are looking forward to rebasing :)

Simply attached this patch in hopes of it being usefull.

Looking forward to rebasing into Bug 11230!

> However, I'm concerned with the "DB size" issue... Do you know if creating
> columns in a table but NOT filling them (it could depend on the activation of a 
> syspref) will have a significant effect on the size of the database?

I recall from my lessons that if you set a static size for a column, it always uses that much space. That way searches are super fast.
But I am not a DB specialist.
Varchar columns need a separate map to find in which sector/address they reside in.
Comment 21 Olli-Antti Kivilahti 2014-07-04 08:49:08 UTC
BTW. We could possibly have a separate Database inside Koha's MySQL/MariaDB just for statistics. That could be easily separated from the main DB and put somewhere with lots of hard drive space.

Also that wouldn't complicate Koha installation one bit because we can use same credentials for both DB's by default.
Comment 22 Mathieu Saby 2014-07-28 08:40:57 UTC
Hello Olli-Anti
Bug 11230 has been pushed, so if you still want to work on that, could you
- assign 7021 to yourself
- refactor your patch
- change the status to need signoff?

I will have some time to sign it before september.


Mathieu
Comment 23 Mathieu Saby 2014-07-28 08:42:38 UTC
(also, could you provide a test plan for your patch?)
Comment 24 Nicole C. Engard 2015-12-04 16:46:59 UTC
Can someone bring this back to the forefront, it's a big deal if we're anonymizing patron circ history because we lose the category.
Comment 25 Nicole C. Engard 2016-02-03 18:02:22 UTC
I have another example here.  If a child become adult their stats go to adults even though they were children at the time. I'd love to see this patch rebased/fixed for current Koha if possible.

Nicole
Comment 26 Barton Chittenden 2016-03-17 14:57:17 UTC
What's the status on this? The bug report shows 'Assigned', but there's also a patch. Is it ready for sign-off?

Can someone add a test plan?
Comment 27 Nicole C. Engard 2016-05-12 15:54:35 UTC
Checking in here again ... any updates on a test plan?
Comment 28 Emmi Takkinen 2021-07-16 10:10:50 UTC
Created attachment 122881 [details] [review]
Bug 7021: Reintroduce usercode column

This patch reintroduces usercode column to
statistics table.

To test:
1. Apply patch and update database
2. Confirm there's column usercode in statistics
table

Sponsored-by: Koha-Suomi Oy
Comment 29 Emmi Takkinen 2021-07-16 10:11:16 UTC
Created attachment 122882 [details] [review]
DO NOT PUSH! Schema changes
Comment 30 Emmi Takkinen 2021-07-16 10:12:02 UTC
Created attachment 122883 [details] [review]
Bug 7021: patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Comment 31 Chris Cormack 2022-01-06 23:12:35 UTC
Created attachment 129119 [details] [review]
Bug 7021: patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 32 Chris Cormack 2022-01-06 23:13:01 UTC
Created attachment 129120 [details] [review]
Bug 7021: patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 33 Chris Cormack 2022-01-06 23:14:18 UTC
Created attachment 129121 [details] [review]
Bug 7021: Reintroduce usercode column

This patch reintroduces usercode column to
statistics table.

To test:
1. Apply patch and update database
2. Confirm there's column usercode in statistics
table

Sponsored-by: Koha-Suomi Oy
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 34 Chris Cormack 2022-01-06 23:14:23 UTC
Created attachment 129122 [details] [review]
Bug 7021: patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 35 Katrin Fischer 2022-01-08 21:59:41 UTC
Hi Emmi,

thx for taking on this old bug! I still like the idea. Can you explain why you are using usercode instead of categorycode or similar?
Comment 36 Emmi Takkinen 2022-01-10 06:57:17 UTC
(In reply to Katrin Fischer from comment #35)
> Hi Emmi,
> 
> thx for taking on this old bug! I still like the idea. Can you explain why
> you are using usercode instead of categorycode or similar?

I decided to leave it as is, since I thought it would make it more clear we are talking about patrons categorycode who made the checkout/renew/return.
Comment 37 Martin Renvoize (ashimema) 2022-05-23 14:22:50 UTC
Created attachment 135269 [details] [review]
Bug 7021: Reintroduce usercode column

This patch reintroduces usercode column to
statistics table.

To test:
1. Apply patch and update database
2. Confirm there's column usercode in statistics
table

Sponsored-by: Koha-Suomi Oy
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 38 Martin Renvoize (ashimema) 2022-05-23 14:22:55 UTC
Created attachment 135270 [details] [review]
Bug 7021: Patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 39 Martin Renvoize (ashimema) 2022-05-23 14:23:00 UTC
Created attachment 135271 [details] [review]
Bug 7021: Terminology - usercode -> categorycode

Lets stick to standard terminology and use categorycode rather than
usercode here.
Comment 40 Martin Renvoize (ashimema) 2022-05-23 14:23:06 UTC
Created attachment 135272 [details] [review]
Bug 7021: DO NOT PUSH! Schema changes
Comment 41 Martin Renvoize (ashimema) 2022-05-23 14:23:12 UTC
Created attachment 135273 [details] [review]
Bug 7021: Remove superflous 'open'
Comment 42 Martin Renvoize (ashimema) 2022-05-23 14:23:17 UTC
Created attachment 135274 [details] [review]
Bug 7021: Catch new introductions of UpdateStats
Comment 43 Martin Renvoize (ashimema) 2022-05-23 14:38:47 UTC
Think that should resolve all QA issues.. setting back to signed off to bring it back to the QA pile.
Comment 44 Jonathan Druart 2022-05-31 08:26:53 UTC
As said on IRC, IMO we should promote the pseudonymization feature instead.
Comment 45 Sally 2022-07-13 15:50:33 UTC
We'd really love to see this feature in Koha.  We don't use pseudonymization as there are statistics that we pull together where we reference date of birth.

Pseudonymization definitely has a place, but we've developed policies to ensure that we're processing our collected data in line with GDPR and with consent granted by our patrons.

The statistics table is the heart of our performance statistics, and having a permanent record of which category a patron was in would be a major step forward for us.
Comment 46 Katrin Fischer 2022-07-14 20:41:02 UTC
(In reply to Sally from comment #45)
> We'd really love to see this feature in Koha.  We don't use pseudonymization
> as there are statistics that we pull together where we reference date of
> birth.
> 
> Pseudonymization definitely has a place, but we've developed policies to
> ensure that we're processing our collected data in line with GDPR and with
> consent granted by our patrons.
> 
> The statistics table is the heart of our performance statistics, and having
> a permanent record of which category a patron was in would be a major step
> forward for us.

+1
Comment 47 Martin Renvoize (ashimema) 2022-08-12 12:10:14 UTC
Indeed.. I discussed the options with the customer and others and we agreed this covers something not covered by the anonymisation functionality..

It would be great to see some QA action on this one.
Comment 48 Kyle M Hall (khall) 2022-09-02 11:15:08 UTC
Created attachment 140103 [details] [review]
Bug 7021: Reintroduce usercode column

This patch reintroduces usercode column to
statistics table.

To test:
1. Apply patch and update database
2. Confirm there's column usercode in statistics
table

Sponsored-by: Koha-Suomi Oy
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 49 Kyle M Hall (khall) 2022-09-02 11:15:21 UTC
Created attachment 140104 [details] [review]
Bug 7021: Patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!

To test:
1. Add loan for patron.
2. Check statistics table
=> 'usercode' column for this issue should now contain patrons categorycode

To test add_statistics_borrowers_categorycode.pl:
1. Run add_statistics_borrowers_categorycode.pl
2. Check statistics table
=> all statistics which are type 'issue' OR 'localuse' OR 'renew'
should now contain patrons categorycode in 'usercode' column

Also prove that tests in t/db_dependent/Circulation.t still pass.

Rebased-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 50 Kyle M Hall (khall) 2022-09-02 11:15:27 UTC
Created attachment 140105 [details] [review]
Bug 7021: Terminology - usercode -> categorycode

Lets stick to standard terminology and use categorycode rather than
usercode here.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 51 Kyle M Hall (khall) 2022-09-02 11:15:31 UTC
Created attachment 140106 [details] [review]
Bug 7021: DO NOT PUSH! Schema changes

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 52 Kyle M Hall (khall) 2022-09-02 11:15:37 UTC
Created attachment 140107 [details] [review]
Bug 7021: Remove superflous 'open'

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 53 Kyle M Hall (khall) 2022-09-02 11:15:41 UTC
Created attachment 140108 [details] [review]
Bug 7021: Catch new introductions of UpdateStats

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 54 Kyle M Hall (khall) 2022-09-02 11:15:45 UTC
Created attachment 140109 [details] [review]
Bug 7021: (QA follow-up): Minor code cleanup

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 55 Kyle M Hall (khall) 2022-09-02 11:15:49 UTC
Created attachment 140110 [details] [review]
Bug 7021: Move to new atomic update structure

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 56 Kyle M Hall (khall) 2022-09-02 13:19:52 UTC
Created attachment 140116 [details] [review]
Bug 7021: Move to new atomic update structure

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 57 Kyle M Hall (khall) 2022-09-02 13:34:08 UTC
Created attachment 140144 [details] [review]
Bug 7021: (QA followup) Add comment to new database column

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 58 Tomás Cohen Arazi (tcohen) 2022-09-22 12:37:35 UTC
Created attachment 140845 [details] [review]
Bug 7021: (QA follow-up) Fix typo

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 59 Tomás Cohen Arazi (tcohen) 2022-09-22 12:43:03 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 60 Lucas Gass (lukeg) 2022-10-31 23:02:00 UTC
Enhancement will not be backported to 22.05.x
Comment 61 Caroline Cyr La Rose 2023-05-05 18:57:20 UTC
Not sure what needs documenting here. It seems like backend stuff. Closing, but feel free to reopen with details if needed.