Automatic Card Renewal

[Dan Brower]

This is something that is on our wishlist. We are currently using 3.1.10 (web-based), and will be updating to 3.2 or possibly 3.3 later this year. Is there a way for a patron's account expiration date to be automatically extended based on their latest activity date? This would be based on a library's privilege rules. For example, when a patron checks out an item on April 8, 2019, their privilege would automatically be extended to April 8, 2020 (based on our privilege rules). This would help us keep our patrons active, and while there would be less direct contact, patrons would not have to worry about receiving another email from the library about their card. By keeping their account active, we can continue to remind them about other services or upcoming initiatives, programs, etc.

This may not be something every library is interested in, but some libraries might be. Each library could make that decision.

[Josh Stompro (u-launchpad-stompro-org)]

Hello Dan, you could do something like this now with a nightly sql script without too much trouble.

Here is an example sql command that will extend the expiration date by one year for all patron accounts that are going to expire in 30 days or less, and have checked out something in the past 30 days. If you use Overdrive or something along those lines, you could also look at the user activity info to look at recent SIP logins to define an active customer, to avoid cutting off active overdrive customers.

--Select query for testing criteria - safe to run to play around with
select au.id, au.expire_date, au.expire_date+'1 year'::interval new_expire
, (select max(ac.xact_start) from action.circulation ac where ac.usr=au.id
     and ac.xact_start >= now()-'30 days'::interval ) latest_checkout

from actor.usr au

where
au.expire_date <= now()+'30 days'::interval -- Accounts that will expire in 30 days

and exists (select 1 from action.circulation ac
             where ac.usr=au.id
                   and ac.xact_start >= now()-'30 days'::interval --Checkout started in past 30 days
            )
;

-- Update query to actually update
begin;

update actor.usr au
set expire_date=expiration_date+'1 year'::interval -- This gives them 1 year + 30 days
where
au.expire_date <= now()+'30 days'::interval -- Accounts that will expire in 30 days
and exists (select 1 from action.circulation ac
             where ac.usr=au.id
                   and ac.xact_start >= now()-'30 days'::interval --Checkout started in past 30 days
            )
-- and au.profile in (1,2,3) --restrict to certain permission groups
-- possibly restrict if patron owes money
-- possibly restrict if standing penalties exist

returning au.id, au.expiration_date --return some info on updated users

rollback; --change to commit when done testing.

In production it would be a good idea to add some logging, such as adding a patron note when they are auto-renewed. To do that I would create a temp table from the initial select, then use that data to perform the updates, and to insert notes into the patron's account. "Patron auto renewed for 1 year on x date because recent checkouts existed."

Josh

[Andrea Neiman (aneiman)]

This is a duplicate of an old wishlist item, bug 1365693.