Add a support script for importing patrons

[Jane Sandberg]

Academic and school libraries often get their patron data from an external system, such as a student information system (SIS). These libraries would like a standard support script for loading these patron records into Evergreen and updating existing Evergreen records based on changed data from the SIS. Such a support script could be run as a cron job, so library workers wouldn't have to manually upload student information throughout the school year.

There is already an example SQL script for importing patron data from a CSV file here; several libraries use this as the basis for their own local scripts: http://docs.evergreen-ils.org/3.1/_creating_an_sql_script_for_importing_patrons.html

There has been some discussion of this on the mailing list:
* https://markmail.org/message/gy4dxhwf2fdtd4op
* https://markmail.org/message/nfxovop3xxerxs33
* https://markmail.org/message/huwo6rexrytouetg

Here is an older set of requirements: https://wiki.evergreen-ils.org/doku.php?id=student_success_working_group:batch_patron_functions

And here are some discussions of the topic in various meetings:
* https://youtu.be/NXcLPlWNx2w?t=40m18s (Student Success Working Group, 25 June 2018)
* https://evergreen-ils.org/meetings/evergreen/2014/evergreen.2014-11-13-14.00.log.html (Evergreen for Academics)

[Jane Sandberg (sandbergja)]

Chris Sharp, Terran McCanna, and Tiffany Little presented on this topic at the 2020 Evergreen Online Conference. Here is Chris' script: https://git.gapines.org/csharp/student-cards

[Terran McCanna (tmccanna)]

It would be really handy to have a user interface for this so that the burden of setting up each school's info and connection data wasn't solely on the system admin.

[Rogan Hamby (rogan-hamby)]

I've been working with a few of our libraries who need to do periodic bulk loading and updating. I have a generic script in the Equinox migration tools repo that has worked for several libraries already and covers a number of use cases. I want to kick the tires on it more but intend to submit it for consideration for this.

If anyone wants to look at it:

https://github.com/EquinoxOpenLibraryInitiative/migration-tools/blob/master/patron_loader/patron_loader.pl

[Rogan Hamby (rogan-hamby)]

I'm not putting a pull request on this yet because I'd like feedback on use cases, utility, etc... I have used it for three libraries at this point and am pretty happy with it.

user/rogan/lp1786524_patron_loading_script

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=f636f32d18155cf48be7a58bf35d865ad8939142

[Rogan Hamby (rogan-hamby)]

Thinking about this some more as an Evergreen feature there is some reworking to do for a proper 3.6+ patch version. The biggest is the in database logging. The current script assumes no native Evergreen tracking of events and creates a logging table in schema patron_loader to log events such as when a match point can not be found for an account and so on.

Do we want to put a loading log table in the actor schema? That would also allow for field mapper entries for regular reports?

[Rogan Hamby (rogan-hamby)]

This was discussed during the Hack-A-Way and the indication was that people would want to use the reporter to access logs. So, I will be updating the branch soon with new features I've already put into place in migration-tools and making adding the tracking log to the actor schema and appropriate entries to fm_IDL.xml with a (cross fingers) target of inclusion in 3.7

[Rogan Hamby (rogan-hamby)]

patch at :

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=b5ff536901a7b2212e588e530eb0394d9e6a0f3c

user/rogan/lp1786524_patron_loader

patch adds the script, docs, schema changes for supporting mappable data and field mapper additions for the reporter

[Rogan Hamby (rogan-hamby)]

Added a small tweak to add a guardian to the list of columns.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=d8ed2d9b1f7d08b5a15c9c7dff162525dd271813

[Jane Sandberg (sandbergja)]

Thanks for this script, Rogan! Once it is all ready to go, this will be a great tool for the ever-growing number of us who need to do regular patron imports.

I poked at your script this morning. I threw on another commit that:
* installs it into /openils/bin on make install
* and allows us to run the script without specifying db connection configs on the command line (saving us from having to type production db passwords on the command line)

Here it is: https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/sandbergja/lp1786524_patron_loader

However, I still haven't been able to get it working with production data. I'll keep poking at it when I have time.

Also, this script should really use DBI's prepared statement functionality, using the ? placeholders in the SQL queries, rather than just interpolating those strings in there. It would keep us safer from SQL injection, and would also probably help this script to be more flexible in handling unexpected values in those CSV files.

[Rogan Hamby (rogan-hamby)]

Hi Jane,

I added support for using placedholders. Your change to use bootstrap broke the ability to directly specify dbi connections so I added a flag to allow both to work.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=71de8633f8c68e9837cc1cfd31d193a1a036c673

I'm not having any trouble with it working and tested it again after these changes. If it's helpful here is a very short set of CSV data that will work with concerto with no mapping having to be configured:

misnamed_column,profile,usrname,family_name,first_given_name,email,dob,home_library,cardnumber,active,barred,juvenile,passwd,add1_street1,add1_city,add1_state,email,day_phone,statcat_name1,satcat_value2
77,Patrons,77,Jane,Doe,<email address hidden>,07/18/1900,BR1,77,t,false,t,testpasswd,123 Home Ave,Whoville,SE,<email address hidden>,823-555-9082,School,South Who High
99,Patrons,99,Jack,Doe,<email address hidden>,07/18/1901,BR1,99,t,false,t,,Street of the Lifted Lorax,Whoville Lower Metro,SE,<email address hidden>,917-555-9148,School,Thornwell Elementary

[Rogan Hamby (rogan-hamby)]

Based on feedback from Jessica at Bibliomation, a useful feature would be the ability to declare columns that should be loaded into staging tables with actor.usr IDs.

[Rogan Hamby (rogan-hamby)]

Another useful addition would be to be able to supply a list of barcode prefixes and not update or load if an account matches these.

[Blake GH (bmagic)]

Here's mine for reference. I'm reviewing Rogans as well.
https://github.com/mcoia/mobius_evergreen/blob/master/Random/patron_create_ats.pl

[Blake GH (bmagic)]

And Chris's
https://git.gapines.org/csharp/student-cards

[Rogan Hamby (rogan-hamby)]

An application should have an email report option as well.

[Blake GH (bmagic)]

There is a minor merge conflict with master, resolved that. Then there is an issue with creating a new Evergreen database with references to schema "actor" that doesn't exist yet. Fixed that:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=924430606f177818243a45bf32869ff5801fc8b9

This does not fix the master conflict

[Jane Sandberg (sandbergja)]

Thanks for the work on this, Rogan and Blake! I've rebased and signed off on previous commits here: user/sandbergja/lp1786524_patron_loader-2

This also adds a live test for the patron loader script, using the sample CSV data Rogan provided in #10. And fixes a bug that had the potential to overwrite existing patrons' mailing addresses.