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.
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.
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.
I got asked for this again today, so I'm bumping it :)
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
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
*** Bug 10778 has been marked as a duplicate of this bug. ***
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.
(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
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.
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
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
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
Before working on that, I would like bug 11230 to be tested. Mathieu
lol
Created attachment 29456 [details] [review] Bug_7021-Add_borrowers_category_to_statistics_usercode Stumbled upon this issue and thought it would be nice to improve it a bit. Hope it helps.
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.
(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
By the way, bug 11230 has been signed off.
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!
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.
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.
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
(also, could you provide a test plan for your patch?)
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.
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
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?
Checking in here again ... any updates on a test plan?
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
Created attachment 122882 [details] [review] DO NOT PUSH! Schema changes
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>
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>
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>
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>
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>
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?
(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.
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>
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>
Created attachment 135271 [details] [review] Bug 7021: Terminology - usercode -> categorycode Lets stick to standard terminology and use categorycode rather than usercode here.
Created attachment 135272 [details] [review] Bug 7021: DO NOT PUSH! Schema changes
Created attachment 135273 [details] [review] Bug 7021: Remove superflous 'open'
Created attachment 135274 [details] [review] Bug 7021: Catch new introductions of UpdateStats
Think that should resolve all QA issues.. setting back to signed off to bring it back to the QA pile.
As said on IRC, IMO we should promote the pseudonymization feature instead.
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.
(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
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.
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>
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>
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>
Created attachment 140106 [details] [review] Bug 7021: DO NOT PUSH! Schema changes Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140107 [details] [review] Bug 7021: Remove superflous 'open' Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140108 [details] [review] Bug 7021: Catch new introductions of UpdateStats Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140109 [details] [review] Bug 7021: (QA follow-up): Minor code cleanup Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140110 [details] [review] Bug 7021: Move to new atomic update structure Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140116 [details] [review] Bug 7021: Move to new atomic update structure Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140144 [details] [review] Bug 7021: (QA followup) Add comment to new database column Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 140845 [details] [review] Bug 7021: (QA follow-up) Fix typo Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Pushed to master for 22.11. Nice work everyone, thanks!
Enhancement will not be backported to 22.05.x
Not sure what needs documenting here. It seems like backend stuff. Closing, but feel free to reopen with details if needed.