Bug 25090 - Enable library approval of OPAC self registrations before a patron account is created
Summary: Enable library approval of OPAC self registrations before a patron account is...
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Alex Buckley
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-04-09 03:22 UTC by Alex Buckley
Modified: 2020-05-25 04:45 UTC (History)
3 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Allow libraries to verify OPAC self registrations before a patron account is created. This new feature replaces the PatronSelfRegistrationVerifyByEmail syspref with two new sysprefs PatronSelfRegistrationVerifyByEmailToLibrary and PatronSelfRegistrationVerifyByEmailToPatron so now libraries can choose one of the following verification workflows: * Library & patron verification * Library verification only * Patron verification only * No verification
Version(s) released in:


Attachments
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails (39.06 KB, patch)
2020-04-13 14:32 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails (51.41 KB, patch)
2020-04-23 05:47 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails (51.54 KB, patch)
2020-04-23 06:34 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: OPAC Template changes (5.17 KB, patch)
2020-05-05 21:28 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Add 2 new sysprefs allowing libraries to approve OPAC registrations (6.98 KB, patch)
2020-05-05 21:28 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Added 3 new notices (36.15 KB, patch)
2020-05-05 21:28 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Allow libraries to verify OPAC self registrations (20.00 KB, patch)
2020-05-05 21:29 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Allow libraries to verify OPAC self registrations (20.87 KB, patch)
2020-05-05 21:56 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Added 3 new notices (36.76 KB, patch)
2020-05-18 03:29 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Allow libraries to verify OPAC self registrations (20.87 KB, patch)
2020-05-18 03:30 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 25090: Remove references to PatronSelfRegistrationVerifyByEmail syspref (3.98 KB, patch)
2020-05-25 04:45 UTC, Alex Buckley
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Buckley 2020-04-09 03:22:59 UTC
Currently libraries can use the PatronSelfRegistrationVerifyByEmail syspref to restrict self registering users from being able to access the OPAC after registering until they have acknowledged the email sent by Koha. 

If that syspref is disabled then self registering users will be able to login to the OPAC straight after filling in the self registration form. 

It would be good if libraries had further control over what users can login to the OPAC. 
Why? Many online resources (e.g. 3rd party database access) require only OPAC login to be able to be accessed. Special libraries may want to limit OPAC login access only to those users within their organisation as these online resources do charge the libraries on a usage basis. 

How can further control over what users trying to self register can access the OPAC be achieved whilst keeping the default, current Koha behaviour?

1. Ensure 'PatronSelfRegistrationVerifyByEmail' syspref is set to 'Require'

2. Add a new syspref 'PatronSelfRegistrationVerificationRecipient'(This dropdown syspref would allow libraries to choose):

* email address of branch
* KohaAdminEmailAddress
* Requesting user

If email address of branch or KohaAdminEmailAddress then the verification email generated after the user has submitted the registration form will go to the library. The email will contain some details entered by the patron, so the library can confirm if they are happy that the registering user is able to login to the OPAC then they will acknowledge the email (click on the opac-registration-verify.pl link) which will actually make the patron account.
Comment 1 Alex Buckley 2020-04-13 14:32:37 UTC
Created attachment 102845 [details] [review]
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails

Test plan:
To come

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 2 Alex Buckley 2020-04-13 14:33:47 UTC
This is not quite ready for testing I still have to write up test plans and add another followup patch to tidy off some functionality. Will update when ready to test.
Comment 3 Alex Buckley 2020-04-23 05:47:00 UTC
Created attachment 103535 [details] [review]
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails

Online resources such as Ancestry, EBSCO etc. often charge on a usage
basis and for library users to access them they need a Koha OPAC login.

Enabling PatronSelfRegistration syspref allows patrons to immediately
create a Koha patron and have the ability to login to the OPAC.

Enabling PatronSelfRegistrationVerifyByEmail delays when the patron can
login to the OPAC until after they have clicked on the verification
email sent to their entered email address.

Currently there is no way for libraries to moderate self registrations
and prevent certain users (e.g. registrations from outside an
organisation) from having OPAC logins and therefore access to paid
online resources.

This enhancement adds two new system preferences
PatronSelfRegistrationVerificationRecipient and
PatronSelfRegistrationVerificationRecipientAddress.

These new system preferences work in the same way as existing sysprefs
EmailPurchaseSuggestions, EmailAddressForSuggestions, and they keep the
current Koha behaviour as their default.

The former new syspref allows librarians to control where the verification
email (generated after OPAC self registration form is submitted) goes to. The choices are:

A. Self registering patrons selected home library email
B. KohaAdminEmailAddress - Although ReplyToDefault syspref is defaulted
to be used first
C. Email address entered into
PatronSelfRegistrationVerificationRecipientAddress
D. Self registering patron email address - This is the default set for
PatronSelfRegistrationVerificationRecipient.

If A,B,C is selected then a new STAFF_OPAC_REG_VERIFY notice is sent to
the library staff after the OPAC self registration form is submitted.
The library staff can then review the submitted patron information
and if they are happy for that user to have a OPAC login they click the
verification link.

Whereupon a patron account is added to the Koha borrowers table and the 'SELF_REG_APPROVED'
notice containing a password reset link is sent to the patron.

If D is selected then after submitting the OPAC self registration form
is submitted then the patron is emailed the OPAC_REG_VERIFY notice
directly and they can click the verification link whereupon they have a
OPAC login.

Test plan:
1. Enable PatronSelfRegistration and PatronSelfRegistrationVerifyByEmail
sysprefs. Also ensure there are different values in KohaAdminEmailAddress,
ReplyToAddress sysprefs and branch email address (Administration > Libraries)
2. Visit OPAC click registration link
3. Submit form and query the database:
select to_address from message_queue where
letter_code='OPAC_REG_VERIFY';
4. Observe the verification email is sent directly to the email address
entered in step #3
5. Apply patch and restart plack
6. update database:
cd installer/data/mysql
sudo koha-shell <instancename>
./updatedatabase.pl
7. Observe 2 new sysprefs:
* PatronSelfRegistrationVerificationRecipient -> Note this has default
value of 'email address of Self registering patron'
* PatronSelfRegistrationVerificationRecipientAddress

8. Repeat steps 2,3,4 and observe the 'OPAC_REG_VERIFY' notices is still
sent directly to the patron

9. Change PatronSelfRegistrationVerificationRecipient to 'email address
of branch'
10. Revisit the OPAC, resubmit the OPAC self registration (setting the
library to a library with a email set in step #1) and observe
text is displayed telling you that your self registration has been sent
to librarians for approval

11. Query the database:
select to_address, content from message_queue where
letter_code='STAFF_OPAC_REG_VERIFY';

Observe that the STAFF_OPAC_REG_VERIFY notice was sent to the individual
library email address and contains:

* Card number
* Name: title firstname surname
* Physical address: streetnumber, streettype, address, address2, city, state, zipcode, country
* Email
* Phone
* Mobile
* Fax
* Secondary email
* Secondary phone
* Home library
* Temporary patron category

And a verification link

12. Query the database and observe the patron added in step 8 doesn't
yet exist in the borrowers table, e.g:
select * from borrowers where email=<email you entered>;

13. Copy the verification link from step #11 into your browser
14. Observe the OPAC is displaying text telling you a password reset
link email was sent to patron
15. Query the database and observe the patron has been added to the
borrowers table now:
select * from borrowers where email=<email you entered>;

16. Query the message_queue table and observe the password reset email
'SELF_REG_APPROVED' has been sent to the patron:
select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

17. Click on the password reset link in that email and observe the
password reset page in the OPAC loads with the login username and email;
are prefilled.

18. Reset the password and try logging in and observe you are able to.

19. Change PatronSelfRegistrationVerificationRecipient to
'KohaAdminEmailAddress' and repeat steps 10 and 11 and observe the
'STAFF_OPAC_REG_VERIFY' notice has been sent to ReplyToDefault syspref
email
20. Repeat steps 12, 13, 14, 15, 16, 17, 18 and observe they all have
the same outcomes as when PatronSelfRegistrationVerificationRecipient
was set to 'email address of branch'

21. Change PatronSelfRegistrationVerificationRecipient to
'PatronSelfRegistrationVerificationRecipientAddress' and then set a
different email address in
PatronSelfRegistrationVerificationRecipientAddress'

22. Repeat steps 10 and 11 and observe the 'STAFF_OPAC_REG_VERIFY'
notice has been sent to the email you set in
PatronSelfRegistrationVerificationRecipientAddress

23. Repeat steps 12, 13, 14, 15, 16, 17, 18 and observe they all have
the same outcomes as when PatronSelfRegistrationVerificationRecipient
was set to 'email address of branch'

24. Run tests:
sudo koha-shell <instancename>
prove xt
prove t

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 4 Alex Buckley 2020-04-23 06:34:32 UTC
Created attachment 103536 [details] [review]
Bug 25090: Add sysprefs to define destination of OPAC self reg verification emails

Online resources such as Ancestry, EBSCO etc. often charge on a usage
basis and for library users to access them they need a Koha OPAC login.

Enabling PatronSelfRegistration syspref allows patrons to immediately
create a Koha patron and have the ability to login to the OPAC.

Enabling PatronSelfRegistrationVerifyByEmail delays when the patron can
login to the OPAC until after they have clicked on the verification
email sent to their entered email address.

Currently there is no way for libraries to moderate self registrations
and prevent certain users (e.g. registrations from outside an
organisation) from having OPAC logins and therefore access to paid
online resources.

This enhancement adds two new system preferences
PatronSelfRegistrationVerificationRecipient and
PatronSelfRegistrationVerificationRecipientAddress.

These new system preferences work in the same way as existing sysprefs
EmailPurchaseSuggestions, EmailAddressForSuggestions, and they keep the
current Koha behaviour as their default.

The former new syspref allows librarians to control where the verification
email (generated after OPAC self registration form is submitted) goes to. The choices are:

A. Self registering patrons selected home library email
B. KohaAdminEmailAddress - Although ReplyToDefault syspref is defaulted
to be used first
C. Email address entered into
PatronSelfRegistrationVerificationRecipientAddress
D. Self registering patron email address - This is the default set for
PatronSelfRegistrationVerificationRecipient.

If A,B,C is selected then a new STAFF_OPAC_REG_VERIFY notice is sent to
the library staff after the OPAC self registration form is submitted.
The library staff can then review the submitted patron information
and if they are happy for that user to have a OPAC login they click the
verification link.

Whereupon a patron account is added to the Koha borrowers table and the 'SELF_REG_APPROVED'
notice containing a password reset link is sent to the patron.

If D is selected then after submitting the OPAC self registration form
is submitted then the patron is emailed the OPAC_REG_VERIFY notice
directly and they can click the verification link whereupon they have a
OPAC login.

Test plan:
1. Enable PatronSelfRegistration, PatronSelfRegistrationVerifyByEmail,
and OpacResetPassword sysprefs. Also ensure there are different values in KohaAdminEmailAddress,
ReplyToAddress sysprefs and branch email address (Administration > Libraries)
2. Visit OPAC click registration link
3. Submit form and query the database:
select to_address from message_queue where
letter_code='OPAC_REG_VERIFY';
4. Observe the verification email is sent directly to the email address
entered in step #3
5. Apply patch and restart plack
6. update database:
cd installer/data/mysql
sudo koha-shell <instancename>
./updatedatabase.pl
7. Observe 2 new sysprefs:
* PatronSelfRegistrationVerificationRecipient -> Note this has default
value of 'email address of Self registering patron'
* PatronSelfRegistrationVerificationRecipientAddress

8. Repeat steps 2,3,4 and observe the 'OPAC_REG_VERIFY' notices is still
sent directly to the patron

9. Change PatronSelfRegistrationVerificationRecipient to 'email address
of branch'
10. Revisit the OPAC, resubmit the OPAC self registration (setting the
library to a library with a email set in step #1) and observe
text is displayed telling you that your self registration has been sent
to librarians for approval

11. Query the database:
select to_address, content from message_queue where
letter_code='STAFF_OPAC_REG_VERIFY';

Observe that the STAFF_OPAC_REG_VERIFY notice was sent to the individual
library email address and contains:

* Card number
* Name: title firstname surname
* Physical address: streetnumber, streettype, address, address2, city, state, zipcode, country
* Email
* Phone
* Mobile
* Fax
* Secondary email
* Secondary phone
* Home library
* Temporary patron category

And a verification link

12. Query the database and observe the patron added in step 8 doesn't
yet exist in the borrowers table, e.g:
select * from borrowers where email=<email you entered>;

13. Copy the verification link from step #11 into your browser
14. Observe the OPAC is displaying text telling you a password reset
link email was sent to patron
15. Query the database and observe the patron has been added to the
borrowers table now:
select * from borrowers where email=<email you entered>;

16. Query the message_queue table and observe the password reset email
'SELF_REG_APPROVED' has been sent to the patron:
select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

17. Click on the password reset link in that email and observe the
password reset page in the OPAC loads with the login username and email;
are prefilled.

18. Reset the password and try logging in and observe you are able to.

19. Change PatronSelfRegistrationVerificationRecipient to
'KohaAdminEmailAddress' and repeat steps 10 and 11 and observe the
'STAFF_OPAC_REG_VERIFY' notice has been sent to ReplyToDefault syspref
email
20. Repeat steps 12, 13, 14, 15, 16, 17, 18 and observe they all have
the same outcomes as when PatronSelfRegistrationVerificationRecipient
was set to 'email address of branch'

21. Change PatronSelfRegistrationVerificationRecipient to
'PatronSelfRegistrationVerificationRecipientAddress' and then set a
different email address in
PatronSelfRegistrationVerificationRecipientAddress'

22. Repeat steps 10 and 11 and observe the 'STAFF_OPAC_REG_VERIFY'
notice has been sent to the email you set in
PatronSelfRegistrationVerificationRecipientAddress

23. Repeat steps 12, 13, 14, 15, 16, 17, 18 and observe they all have
the same outcomes as when PatronSelfRegistrationVerificationRecipient
was set to 'email address of branch'

24. Run tests:
sudo koha-shell <instancename>
prove xt
prove t

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 5 Katrin Fischer 2020-04-29 06:11:00 UTC
What a test plan!

If I understood correctly, if you turn on the feature to send the verification to the library, you can't use the email verification for the patrons at the same time.

We have new prefs here and a new notice template - would it be a big step to make this a separate feature? Maybe to happen after the patrons have verified their email address?

The email verification also serves as kind of a SPAM protection, it ensures you only have to deal with registrations that have gone through a first step and prevents (to some extend) people from registering under other people's email addresses. I think it might be helpful to have this in addition to further verification by staff as is the use case here.

I guess one of the problems here is that you can't really keep patrons from logging in, once they have been added to the database? Using a restriction would be a logical thing, but I think it would not have the desired effect right now.
Comment 6 Alex Buckley 2020-04-29 07:55:58 UTC
Hi Katrin, 

Yes the test plan was quite something to write up!

Thanks very much for your questions and thoughts, much appreciated :)

Your absolutely correct, if you turn on the feature to send verification to library (in new PatronSelfRegistrationVerificationRecipient syspref) then you can't use email verification for the patrons at the same time.

Yes once a patron is added to the database you can't stop them from logging in. However when the existing PatronSelfRegstrationVerifyByEmail syspref is enabled when a user registers a row is only added into borrower_modifications database table. At this point the user cannot login. 

Only after the patron has clicked the link in the verification email does Koha remove the row from borrower_modification, add a row to borrowers table and give it a borrowers.userid, allowing them to login.

So the verification process does provide a way of delaying when a user can login. So with our patch the registering patron record is in the borrower_modifications table (i.e. can't login) until the library staff member clicks the verification email link. Once they've clicked the link the registering patron is added to borrowers table and so can now login. 

Just to clarify regarding making this a separate feature, would that entail simply changing the workflow to occur like outlined below or would new sysprefs or staff client interfaces be required?

1. User registers in OPAC -> Record added to borrower_modifications table
2. Verification email sent to user
3. If verification email link clicked by user then notice emailed to library to verify 
4. Librarian clicks verification link in library notice -> Record added to borrowers table
5. User is emailed password reset link whereupon they can change their password and login to OPAC

I am more than happy to make this change but would just like to clarify first what you feel would be needed :)

Many thanks,
Alex
Comment 7 Katrin Fischer 2020-05-03 22:45:19 UTC
Hi Alex, 

I think the workflow you describe would work (while hopefully not disrupting your work too much)

A possible thing (for another day and bug report) could be a report/hint on the dashboard for patrons requiring verification. But I think the emails work well as a first implementation. 

About configuration: I am not sure what would be easier, have PatronSelfRegistrationVerifyByEmail with 4 options (none, one, the other, both) or have 2 prefs instead for each that you can turn on/off separately. I am slightly leaning to the latter, but would name them similar so they show up together... or even group them into one line in the editor?
Comment 8 Alex Buckley 2020-05-04 03:21:28 UTC
Hi Katrin, 

Thanks very much for your feedback, yes I agree having a hint or report in the dashboard would be a good later enhancement on a different bug report. 

Yes I think the two prefs on the same line in the editor (replacing PatronSelfRegistrationVerifyByEmail) could be named:

* PatronSelfRegistrationVerifyByEmailToLibrary - Email address of branch / KohaAdminEmailAddress  / Don't require
* PatronSelfRegistrationVerifyByEmailToPatron - Require / Don't require

I think for simplicities sake it would be good to include the options from PatronSelfRegistrationVerificationRecipient into PatronSelfRegistrationVerifyByEmailToLibrary (i.e. have 3 options so the librarian can choose not just to enable but defined where the email should be sent) so we limit the number of new sysprefs added. 

I am working on those changes now. 

Thanks,
Alex
Comment 9 Alex Buckley 2020-05-05 21:28:03 UTC
Created attachment 104380 [details] [review]
Bug 25090: OPAC Template changes

* opac-registration-confirmation.tt
* opac-registration-email-sent.tt

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 10 Alex Buckley 2020-05-05 21:28:21 UTC
Created attachment 104381 [details] [review]
Bug 25090: Add 2 new sysprefs allowing libraries to approve OPAC registrations

Added:
* PatronSelfRegistrationVerifyByEmailToLibrary - Default value = 'None'
* PatronSelfRegistrationVerifyByEmailToPatron - Default value = "Don't
require"

Removed:
* PatronSelfRegistrationVerifyByEmail

For existing Koha installs if removed
PatronSelfRegistrationVerifyByEmail was enabled then
PatronSelfRegistrationVerifyByEmailToPatron is enabled - they have the
same behaviour when enabled.

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 11 Alex Buckley 2020-05-05 21:28:47 UTC
Created attachment 104382 [details] [review]
Bug 25090: Added 3 new notices

* STAFF_VER_OPAC_REG - Sent to library containing registered patron details for library to review
and select verification link if approve patron creation

* PATRON_AND_IB_VER - Sent to patron to inform them they need to verify
their email address via verification link then their registration will
be reviewed by library

* SELF_REG_APPROVED - Sent to patron containing password reset link
after library has approved their registration

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 12 Alex Buckley 2020-05-05 21:29:08 UTC
Created attachment 104383 [details] [review]
Bug 25090: Allow libraries to verify OPAC self registrations

These patchsets add two new sysprefs (to replace
PatronSelfRegistrationVerifyByEmail), they are:

* PatronSelfRegistrationVerifyByEmailToLibrary - Library verifies OPAC
self registrations before a record is added to borrowers database table

* PatronSelfRegistrationVerifyByEmailToPatron - Patron must verify
their email before a record is added to borrowers database table

----------

4 possible email verification workflow scenarios:

1) library verification = disabled & patron verification = disabled :
   1a) Patron submits OPAC self reg form
   1b) Patron account created immediately

2) library verification = enabled & patron verification = disabled :
   2a) Patron submits OPAC self reg form
   2b) Koha sends notice to library to review registration
   2c) Library approves reg by clicking verification link in notice
   2d) Patron account is created
   2e) Password reset link emailed to patron

3) library verification = disabled & patron verification = enabled :
   3a) Patron submits OPAC self reg form
   3b) Koha sends verification email to patron
   3c) Patron clicks verification link in notice
   3d) Patron account is created

4) library verification = enabled & patron verification = enabled :
   4a) Patron submits OPAC self reg form
   4b) Koha sends verification email to patron to verify email
   4c) Patron clicks verification link in notice
   4d) Koha sends notice to library to review registration
   4e) Library approves reg by clicking verification link in notice
   4f) Patron account created
   4g) Password reset link emailed to patron

-----------

Test plan (in each test plan step where you must fill in OPAC self reg form please
use a different email address):

1. Apply patch and restart plack
2. Update database:
cd installer/data/mysql
sudo koha-shell <instancename>
./updatedatabase.pl
3. Observe 2 new sysprefs in same line in editor (and observe
PatronSelfRegistrationVerifyByEmail syspref has been removed):
* PatronSelfRegistrationVerifyByEmailToLibrary - Set to 'None' by
default
* PatronSelfRegistrationVerifyByEmailToPatron - Set to "Don't require"
by default. (Exception: If PatronSelfRegistrationVerifyByEmail
syspref was enabled before applying this patch then
PatronSelfregistrationVerifyByEmailToPatron will now be enabled).

[Test scenario 1]

4. Set the following sysprefs:
* PatronSelfRegistration - enabled
* OpacResetPassword - enabled
* PatronSelfRegistrationVerifyByEmailToLibrary - 'None'
* PatronSelfRegistrationVerifyByEmailToPatron - "Don't require"

Also set different values in:
* KohaAdminEmailAddress syspref
* ReplyToAddress syspref
* branch email address (Administration > Libraries)

5. Visit OPAC, fill in, submit OPAC registration form
6. Observe your patron account has been created immediately and you can
login

[Test scenario 2]

6. Set PatronSelfRegistrationVerifyByEmailToLibrary to 'email address of
branch'
7. Repeat step 5 and query the database:
select to_address, content from message_queue where
letter_code='STAFF_VER_OPAC_REG';

8. Observe the verification email's to_address is library branch email
address (i.e. set in Administration > Libraries ) and the notice
contains the following information:

* Card number
* Name: title firstname surname
* Physical address: streetnumber, streettype, address, address2, city, state, zipcode, country
* Email
* Phone
* Mobile
* Fax
* Secondary email
* Secondary phone
* Home library
* Temporary patron category

And a verification link

9. Query database again:
select count(*) from borrowers where email=<email entered in reg form>;

select count(*) from borrower_modifications where email=<email entered in reg form>;

The first query returns 0 (as the patron account has not been added to
borrowers table), the second query has 1 as patron in
borrower_modifications table.

10. Click verification link in notice and query database:
select * from borrowers where email=<email entered in reg form>

11. Observe the patron account has now been added to the borrowers table
12. Query database:
select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

13. Observe password reset link emailed to patron, click on the link and
observe the password reset page loads with the patrons username and email pre-filled

14. Set PatronSelfRegistrationVerifyByEmailToLibrary to
'KohaAdminEmailAddress'

15. Repeat step 7 observing the verification email's to_address is
ReplytoDefault syspref value (if this is not set then
KohaAdminEmailAddress i.e. fallback same as EmailPurchaseSuggestions
syspref).

16. Repeat steps 9, 10, 11, 12, 13 which should all have the same
outcomes

[Test scenario 3]

17. Set PatronSelfRegistrationVerifyByEmailToPatron to "Require"
        PatronSelfRegistrationVerifyByEmailToLibrary to "None"

18. Repeat step 5
19. Query the database with these 4 queries (explanations of what
outputs to expect are hypehnated below each query:

select to_address, content from message_queue
where letter_code='OPAC_REG_VERIFY';

- You'll see 1 new OPAC_REG_VERIFY notice sent to the patrons inputted email

select to_address, content from message_queue
where letter_code='STAFF_VER_OPAC_REG';

- You'll see no new STAFF_VER_OPAC_REG notice generated (because
PatronSelfRegistrationVerifyByEmailToLibrary is set to "None")

select count(*) from borrowers where email=<email entered in reg form>;

- Returns 0 as patron account not added to borrowers table yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- Returns 1 as patron account is in borrower_modifications table

20. Click verification link in OPAC_REG_VERIFY notice and query
database:
select count(*) from borrowers where email=<email entered in reg form>

This returns 1 as patron account now in borrowers table

[Test scenario 4]

21. Set PatronSelfRegistrationVerifyByEmailToLibrary = 'email address of
branch'

22. Repeat steps 5 and 19 observing same query outputs

23. Click verification link in OPAC_REG_VERIFY notice

24. Query the database:
select to_address, content from message_queue where
letter_code='OPAC_REG_VERIFY';

- Returns no new OPAC_REG_VERIFY notice

select to_address, content from message_queue where
letter_code='PATRON_AND_LIB_VER';

- This returns a row result.  Note it contains patron details for librarians
to review and the to_address is the library branch email.

select count(*) from borrowers where email=<email entered in reg form>;

- This returns 0 as library still hasn't verified patron account can be
created yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- This returns a row result as patron account still in
borrower_modifications table

25. Click on verification link in PATRON_AND_LIB_VERIFY notice

26. Query the database:
select count(*) from borrowers where email=<email entered in reg form>;

- This returns a row result as patron account created

select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

- This returns a row result with the to_address being the patrons email.
Click on the password reset link in the notice and observe the password
reset page pre-fills with the patron username and email.

27. Run tests:
sudo koha-shell <instancename>
prove xt
prove t

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 13 Alex Buckley 2020-05-05 21:56:51 UTC
Created attachment 104386 [details] [review]
Bug 25090: Allow libraries to verify OPAC self registrations

These patchsets add two new sysprefs (to replace
PatronSelfRegistrationVerifyByEmail), they are:

* PatronSelfRegistrationVerifyByEmailToLibrary - Library verifies OPAC
self registrations before a record is added to borrowers database table

* PatronSelfRegistrationVerifyByEmailToPatron - Patron must verify
their email before a record is added to borrowers database table

----------

4 possible email verification workflow scenarios:

1) library verification = disabled & patron verification = disabled :
   1a) Patron submits OPAC self reg form
   1b) Patron account created immediately

2) library verification = enabled & patron verification = disabled :
   2a) Patron submits OPAC self reg form
   2b) Koha sends notice to library to review registration
   2c) Library approves reg by clicking verification link in notice
   2d) Patron account is created
   2e) Password reset link emailed to patron

3) library verification = disabled & patron verification = enabled :
   3a) Patron submits OPAC self reg form
   3b) Koha sends verification email to patron
   3c) Patron clicks verification link in notice
   3d) Patron account is created

4) library verification = enabled & patron verification = enabled :
   4a) Patron submits OPAC self reg form
   4b) Koha sends verification email to patron to verify email
   4c) Patron clicks verification link in notice
   4d) Koha sends notice to library to review registration
   4e) Library approves reg by clicking verification link in notice
   4f) Patron account created
   4g) Password reset link emailed to patron

-----------

Test plan (in each test plan step where you must fill in OPAC self reg form please
use a different email address):

1. Apply patch and restart plack
2. Update database:
cd installer/data/mysql
sudo koha-shell <instancename>
./updatedatabase.pl
3. Observe 2 new sysprefs in same line in editor (and observe
PatronSelfRegistrationVerifyByEmail syspref has been removed):
* PatronSelfRegistrationVerifyByEmailToLibrary - Set to 'None' by
default
* PatronSelfRegistrationVerifyByEmailToPatron - Set to "Don't require"
by default. (Exception: If PatronSelfRegistrationVerifyByEmail
syspref was enabled before applying this patch then
PatronSelfregistrationVerifyByEmailToPatron will now be enabled).

[Test scenario 1]

4. Set the following sysprefs:
* PatronSelfRegistration - enabled
* OpacResetPassword - enabled
* PatronSelfRegistrationVerifyByEmailToLibrary - 'None'
* PatronSelfRegistrationVerifyByEmailToPatron - "Don't require"

Also set different values in:
* KohaAdminEmailAddress syspref
* ReplyToAddress syspref
* branch email address (Administration > Libraries)

5. Visit OPAC, fill in, submit OPAC registration form
6. Observe your patron account has been created immediately and you can
login

[Test scenario 2]

6. Set PatronSelfRegistrationVerifyByEmailToLibrary to 'email address of
branch'
7. Repeat step 5 and query the database:
select to_address, content from message_queue where
letter_code='STAFF_VER_OPAC_REG';

8. Observe the verification email's to_address is library branch email
address (i.e. set in Administration > Libraries ) and the notice
contains the following information:

* Card number
* Name: title firstname surname
* Physical address: streetnumber, streettype, address, address2, city, state, zipcode, country
* Email
* Phone
* Mobile
* Fax
* Secondary email
* Secondary phone
* Home library
* Temporary patron category

And a verification link

9. Query database again:
select count(*) from borrowers where email=<email entered in reg form>;

select count(*) from borrower_modifications where email=<email entered in reg form>;

The first query returns 0 (as the patron account has not been added to
borrowers table), the second query has 1 as patron in
borrower_modifications table.

10. Click verification link in notice and query database:
select * from borrowers where email=<email entered in reg form>

11. Observe the patron account has now been added to the borrowers table
12. Query database:
select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

13. Observe password reset link emailed to patron, click on the link and
observe the password reset page loads with the patrons username and email pre-filled

14. Set PatronSelfRegistrationVerifyByEmailToLibrary to
'KohaAdminEmailAddress'

15. Repeat step 7 observing the verification email's to_address is
ReplytoDefault syspref value (if this is not set then
KohaAdminEmailAddress i.e. fallback same as EmailPurchaseSuggestions
syspref).

16. Repeat steps 9, 10, 11, 12, 13 which should all have the same
outcomes

[Test scenario 3]

17. Set PatronSelfRegistrationVerifyByEmailToPatron to "Require"
        PatronSelfRegistrationVerifyByEmailToLibrary to "None"

18. Repeat step 5
19. Query the database with these 4 queries (explanations of what
outputs to expect are hypehnated below each query:

select to_address, content from message_queue
where letter_code='OPAC_REG_VERIFY';

- You'll see 1 new OPAC_REG_VERIFY notice sent to the patrons inputted email

select to_address, content from message_queue
where letter_code='STAFF_VER_OPAC_REG';

- You'll see no new STAFF_VER_OPAC_REG notice generated (because
PatronSelfRegistrationVerifyByEmailToLibrary is set to "None")

select count(*) from borrowers where email=<email entered in reg form>;

- Returns 0 as patron account not added to borrowers table yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- Returns 1 as patron account is in borrower_modifications table

20. Click verification link in OPAC_REG_VERIFY notice and query
database:
select count(*) from borrowers where email=<email entered in reg form>

This returns 1 as patron account now in borrowers table

[Test scenario 4]

21. Set PatronSelfRegistrationVerifyByEmailToLibrary = 'email address of
branch'

22. Repeat steps 5 and query the database:

select to_address, content from message_queue
where letter_code='PATRON_AND_LIB_VER';

- You'll see 1 new PATRON_AND_LIB_VER notice sent to the patrons inputted
email

select to_address, content from message_queue where
letter_code='OPAC_REG_VERIFY';

- You'll see no new OPAC_REG_VERIFY notice as when library & patron
verification are both enabled the PATRON_AND_LIB_VER notice is sent to
the patron instead

select to_address, content from message_queue
where letter_code='STAFF_VER_OPAC_REG';

- You'll see no new STAFF_VER_OPAC_REG notice generated (it is generated
later on in the workflow of scenario 4)

select count(*) from borrowers where email=<email entered in reg form>;

- Returns 0 as patron account not added to borrowers table yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- Returns 1 as patron account is in borrower_modifications table

23. Click verification link in PATRON_AND_LIB_VER notice

24. Query the database:
select to_address, content from message_queue where
letter_code='OPAC_REG_VERIFY';

- Returns no new OPAC_REG_VERIFY notice

select to_address, content from message_queue where
letter_code='STAFF_VER_OPAC_REG';

- This returns a row result.  Note it contains patron details for librarians
to review and the to_address is the library branch email.

select count(*) from borrowers where email=<email entered in reg form>;

- This returns 0 as library still hasn't verified patron account can be
created yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- This returns a row result as patron account still in
borrower_modifications table

25. Click on verification link in STAFF_VER_OPAC_REG notice

26. Query the database:
select count(*) from borrowers where email=<email entered in reg form>;

- This returns a row result as patron account created

select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

- This returns a row result with the to_address being the patrons email.
Click on the password reset link in the notice and observe the password
reset page pre-fills with the patron username and email.

27. Run tests:
sudo koha-shell <instancename>
prove xt
prove t

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 14 Alex Buckley 2020-05-05 22:03:51 UTC
Hi Katrin, 

I have made the functionality changes to the patchset as you suggested, and implemented the two new sysprefs PatronSelfRegistrationVerifyByEmailToLibrary and PatronSelfRegistrationVerifyByEmailToPatron on the same line in the editor. 

So now there are 4 possible email verification workflow scenarios:

1) library verification = disabled & patron verification = disabled :
   1a) Patron submits OPAC self reg form
   1b) Patron account created immediately

2) library verification = enabled & patron verification = disabled :
   2a) Patron submits OPAC self reg form
   2b) Koha sends notice to library to review registration
   2c) Library approves reg by clicking verification link in notice
   2d) Patron account is created
   2e) Password reset link emailed to patron

3) library verification = disabled & patron verification = enabled :
   3a) Patron submits OPAC self reg form
   3b) Koha sends verification email to patron
   3c) Patron clicks verification link in notice
   3d) Patron account is created

4) library verification = enabled & patron verification = enabled :
   4a) Patron submits OPAC self reg form
   4b) Koha sends verification email to patron to verify email
   4c) Patron clicks verification link in notice
   4d) Koha sends notice to library to review registration
   4e) Library approves reg by clicking verification link in notice
   4f) Patron account created
   4g) Password reset link emailed to patron

The logic changes I have made have been in opac-memberentry.pl and opac-registration-verify.pl please let me know if you think they should be moved to a Koha object or the C4 namespace. 

Thanks,
Alex
Comment 15 Alex Buckley 2020-05-18 03:29:43 UTC
Created attachment 104994 [details] [review]
Bug 25090: Added 3 new notices

* STAFF_VER_OPAC_REG - Sent to library containing registered patron details for library to review
and select verification link if approve patron creation

* PATRON_AND_IB_VER - Sent to patron to inform them they need to verify
their email address via verification link then their registration will
be reviewed by library

* SELF_REG_APPROVED - Sent to patron containing password reset link
after library has approved their registration

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 16 Alex Buckley 2020-05-18 03:30:18 UTC
Created attachment 104996 [details] [review]
Bug 25090: Allow libraries to verify OPAC self registrations

These patchsets add two new sysprefs (to replace
PatronSelfRegistrationVerifyByEmail), they are:

* PatronSelfRegistrationVerifyByEmailToLibrary - Library verifies OPAC
self registrations before a record is added to borrowers database table

* PatronSelfRegistrationVerifyByEmailToPatron - Patron must verify
their email before a record is added to borrowers database table

----------

4 possible email verification workflow scenarios:

1) library verification = disabled & patron verification = disabled :
   1a) Patron submits OPAC self reg form
   1b) Patron account created immediately

2) library verification = enabled & patron verification = disabled :
   2a) Patron submits OPAC self reg form
   2b) Koha sends notice to library to review registration
   2c) Library approves reg by clicking verification link in notice
   2d) Patron account is created
   2e) Password reset link emailed to patron

3) library verification = disabled & patron verification = enabled :
   3a) Patron submits OPAC self reg form
   3b) Koha sends verification email to patron
   3c) Patron clicks verification link in notice
   3d) Patron account is created

4) library verification = enabled & patron verification = enabled :
   4a) Patron submits OPAC self reg form
   4b) Koha sends verification email to patron to verify email
   4c) Patron clicks verification link in notice
   4d) Koha sends notice to library to review registration
   4e) Library approves reg by clicking verification link in notice
   4f) Patron account created
   4g) Password reset link emailed to patron

-----------

Test plan (in each test plan step where you must fill in OPAC self reg form please
use a different email address):

1. Apply patch and restart plack
2. Update database:
cd installer/data/mysql
sudo koha-shell <instancename>
./updatedatabase.pl
3. Observe 2 new sysprefs in same line in editor (and observe
PatronSelfRegistrationVerifyByEmail syspref has been removed):
* PatronSelfRegistrationVerifyByEmailToLibrary - Set to 'None' by
default
* PatronSelfRegistrationVerifyByEmailToPatron - Set to "Don't require"
by default. (Exception: If PatronSelfRegistrationVerifyByEmail
syspref was enabled before applying this patch then
PatronSelfregistrationVerifyByEmailToPatron will now be enabled).

[Test scenario 1]

4. Set the following sysprefs:
* PatronSelfRegistration - enabled
* OpacResetPassword - enabled
* PatronSelfRegistrationVerifyByEmailToLibrary - 'None'
* PatronSelfRegistrationVerifyByEmailToPatron - "Don't require"

Also set different values in:
* KohaAdminEmailAddress syspref
* ReplyToAddress syspref
* branch email address (Administration > Libraries)

5. Visit OPAC, fill in, submit OPAC registration form
6. Observe your patron account has been created immediately and you can
login

[Test scenario 2]

6. Set PatronSelfRegistrationVerifyByEmailToLibrary to 'email address of
branch'
7. Repeat step 5 and query the database:
select to_address, content from message_queue where
letter_code='STAFF_VER_OPAC_REG';

8. Observe the verification email's to_address is library branch email
address (i.e. set in Administration > Libraries ) and the notice
contains the following information:

* Card number
* Name: title firstname surname
* Physical address: streetnumber, streettype, address, address2, city, state, zipcode, country
* Email
* Phone
* Mobile
* Fax
* Secondary email
* Secondary phone
* Home library
* Temporary patron category

And a verification link

9. Query database again:
select count(*) from borrowers where email=<email entered in reg form>;

select count(*) from borrower_modifications where email=<email entered in reg form>;

The first query returns 0 (as the patron account has not been added to
borrowers table), the second query has 1 as patron in
borrower_modifications table.

10. Click verification link in notice and query database:
select * from borrowers where email=<email entered in reg form>

11. Observe the patron account has now been added to the borrowers table
12. Query database:
select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

13. Observe password reset link emailed to patron, click on the link and
observe the password reset page loads with the patrons username and email pre-filled

14. Set PatronSelfRegistrationVerifyByEmailToLibrary to
'KohaAdminEmailAddress'

15. Repeat step 7 observing the verification email's to_address is
ReplytoDefault syspref value (if this is not set then
KohaAdminEmailAddress i.e. fallback same as EmailPurchaseSuggestions
syspref).

16. Repeat steps 9, 10, 11, 12, 13 which should all have the same
outcomes

[Test scenario 3]

17. Set PatronSelfRegistrationVerifyByEmailToPatron to "Require"
        PatronSelfRegistrationVerifyByEmailToLibrary to "None"

18. Repeat step 5
19. Query the database with these 4 queries (explanations of what
outputs to expect are hypehnated below each query:

select to_address, content from message_queue
where letter_code='OPAC_REG_VERIFY';

- You'll see 1 new OPAC_REG_VERIFY notice sent to the patrons inputted email

select to_address, content from message_queue
where letter_code='STAFF_VER_OPAC_REG';

- You'll see no new STAFF_VER_OPAC_REG notice generated (because
PatronSelfRegistrationVerifyByEmailToLibrary is set to "None")

select count(*) from borrowers where email=<email entered in reg form>;

- Returns 0 as patron account not added to borrowers table yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- Returns 1 as patron account is in borrower_modifications table

20. Click verification link in OPAC_REG_VERIFY notice and query
database:
select count(*) from borrowers where email=<email entered in reg form>

This returns 1 as patron account now in borrowers table

[Test scenario 4]

21. Set PatronSelfRegistrationVerifyByEmailToLibrary = 'email address of
branch'

22. Repeat steps 5 and query the database:

select to_address, content from message_queue
where letter_code='PATRON_AND_LIB_VER';

- You'll see 1 new PATRON_AND_LIB_VER notice sent to the patrons inputted
email

select to_address, content from message_queue where
letter_code='OPAC_REG_VERIFY';

- You'll see no new OPAC_REG_VERIFY notice as when library & patron
verification are both enabled the PATRON_AND_LIB_VER notice is sent to
the patron instead

select to_address, content from message_queue
where letter_code='STAFF_VER_OPAC_REG';

- You'll see no new STAFF_VER_OPAC_REG notice generated (it is generated
later on in the workflow of scenario 4)

select count(*) from borrowers where email=<email entered in reg form>;

- Returns 0 as patron account not added to borrowers table yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- Returns 1 as patron account is in borrower_modifications table

23. Click verification link in PATRON_AND_LIB_VER notice

24. Query the database:
select to_address, content from message_queue where
letter_code='OPAC_REG_VERIFY';

- Returns no new OPAC_REG_VERIFY notice

select to_address, content from message_queue where
letter_code='STAFF_VER_OPAC_REG';

- This returns a row result.  Note it contains patron details for librarians
to review and the to_address is the library branch email.

select count(*) from borrowers where email=<email entered in reg form>;

- This returns 0 as library still hasn't verified patron account can be
created yet

select count(*) from borrower_modifications where email=<email entered
in reg form>;

- This returns a row result as patron account still in
borrower_modifications table

25. Click on verification link in STAFF_VER_OPAC_REG notice

26. Query the database:
select count(*) from borrowers where email=<email entered in reg form>;

- This returns a row result as patron account created

select to_address, content from message_queue where
letter_code='SELF_REG_APPROVED';

- This returns a row result with the to_address being the patrons email.
Click on the password reset link in the notice and observe the password
reset page pre-fills with the patron username and email.

27. Run tests:
sudo koha-shell <instancename>
prove xt
prove t

Sponsored-By: Mental Health Education Resource Centre, NZ
Comment 17 Alex Buckley 2020-05-18 03:31:06 UTC
Rebased against master
Comment 18 Alex Buckley 2020-05-25 04:45:25 UTC
Created attachment 105319 [details] [review]
Bug 25090: Remove references to PatronSelfRegistrationVerifyByEmail syspref

PatronSelfRegistrationVerifyByEmail has been replaced with:

* PatronSelfRegistrationVerifyByEmailToPatron
* PatronSelfRegistrationVerifyByEmailToLibrary

Test plan:
1. Go to Administration > Patron attribute types
2. Add a new patron attribute type and observe the 'Editable in OPAC'
checkbox references PatronSelfRegistrationVerifyByEmail
3. Apply patch
4. Refresh page and observe the said field references/links are replaced
with references/links to PatronSelfRegistrationVerifyByEmailToPatron and PatronSelfRegistrationVerifyByEmailToLibrary
5. Select 'Display in OPAC' and 'Editable in OPAC'
6. Make sure both new PatronSelfRegistration sysprefs are disabled
7. Go to your patron detail page in OPAC and click on 'your personal
details' tab
8. Observe under 'Additional information' the patron attribute is
visible and editable

[Check display logic]
9. Observe when either one of the PatronSelfRegistrationVerify sysprefs is
enabled the attribute is hidden in the OPAC page
10. Observe when both PatronSelfRegistrationVerify sysprefs are enabled the
attribute is hidden in OPAC

Sponsored-By: Mental Health Education Resource Centre, NZ