advance notices / pre-overdues

Once again, apologies for the delay in getting the script available!


At Huddersfield, we’ve recently gone live with a bespoke Perl script for generating pre-overdue emails:

To run the script, use the following command:

perl settings.ini

You can use multiple settings files and then just tell the script which settings to use, e.g.:

perl settings1.ini
perl settings2.ini

To run the script, you’ll need the following:

  1. an installation of Perl (e.g. ActivePerl for Windows)
  2. the following Perl modules:
  3. a working ODBC connection to your Horizon database – if you use ODBC for EasyAsk or ReportSmith, then that should do

The script also expects to be able to create log files and these are stored in a subdirectory called logs – create this subdirectory within the directory where you’ve got the script. So, if you run in the script in C:scripts, then create the subdirectory C:scriptslogs.
To use the script, you’ll need to edit the “settings.ini” file and also make a couple of small changes to the Perl script.

the “settings.ini” file

The “settings.ini” file contains the following settings that the script uses to generate the emails:

  • days_in_advance

    The number of days in advance to check for items due back – e.g. if you set it to 1 and run the script on a Monday, then it will generate emails for items that are due back on the Tuesday.
  • debug

    Set this to either yes or no – if set to yes then all emails will be sent to the address(es) specified in the debug_email setting rather than to the patron. You should leave this set to yes until you’ve finished your testing!
  • debug_email

    If debug is set to yes, then all email notices are sent to the address(es) listed in this setting. To send to multiple addresses, separate each address with a space.
  • summary

    Set this to either yes or no – if set to yes then the script will send a summary email to the email address(es) specified in summary_email
  • summary_email

    If summary is set to yes, then a summary email is sent to the address(es) listed in this setting. To send to multiple addresses, separate each address with a space.
  • smtp_server

    Set this to the IP address (or DNS name?) of your SMTP server.
  • email_replyto

    Set this to the email address that the notices should appear to have come from – this is also used as the “reply to” address.

The remaining settings are used to decide which types of borrowers and items are suitable for sending the notices. If you want to send notices to all patrons for all types of items, then simply leave each of the settings blank.
If you prefer to fine tune the borrowers / items, then you need to set up suitable inclusions or exclusions.
So, if you have a specific list of borrower types that you want to send the notices to, then list them in the inclusion settings.
Alternatively, if you have a specific list of borrower types that you don’t want to send the notices to, then list them as an exclusion.
However, don’t try setting both a borrower type inclusion and exclusion, as that doesn’t make sense!
For example, if you only want to send emails to borrower types STU, FTP and PRE, then you’d use:

btype_include         STU FTP PRE

As well as borrower types, you can also add inclusions or exclusions for collection codes, itype codes and location codes.
In the example “settings.ini” file that I’ve posted are the settings we use at Huddersfield. If you look at it, you’ll see that we don’t send emails to lef borrower types (which stands for “left”) and we don’t send notices for the SL location (which is Short Loan).
If you want to, you can create multiple settings files – for example, you might want to send 1 day in advance for everyone except part-time borrowers, and then have another file that’s set up to send 3 days in advance to only the part-time borrowers.

setting up the script

As well as setting up the “settings.ini” file, you need to make a small chance to the script to set up your ODBC connection information. These changes need to made near line 100:

my $dbh = DBI->connect('dbi:ODBC:REPORTSMITH',
      'report', 'abc123', { RaiseError => 1 });

You’ll need to change:

  1. REPORTSMITH – change this the name of your ODBC connection
  2. report – change this to your ODBC connection username
  3. abc123 – change this to your ODBC connection password

final notes

  • At Huddersfield, the script runs daily on the HIP server as a scheduled task.
  • There’s a few lines of code that are commented out relating to providing a direct secure link to the patron’s “items out” page in HIP. Let me know if providing this kind of functionality in your notices is of interest, and then I’ll send you further details of how to do this.
  • You can probably guess that the script isn’t for the faint hearted! If you’re looking for an easy method of implementing pre-overdues, then you might want wait until Dynix build the functionality into Horizon or maybe take a look Alpha-G Consulting solution.
  • I can only offer limited support for the script (, but I’ll try and make sure that any bugs get fixed.
  • If you spot any errors in these instructions, please let me know!

Finally – have fun and I hope the script comes in useful!

9 thoughts on “advance notices / pre-overdues”

  1. This is a very nice solution! I am trying it out on my WinXP workstation using Perl v5.8.1 from Activestate, and I have run into this message after running your script. Any ideas? ~Aaron
    print() on closed filehandle LOG at line 505.
    print() on closed filehandle LOG at line 506.
    Can’t open the debug file: No such file or directory

  2. Hi Aaron
    Sorry about that – I’ve amended the instructions to say that you need to create a “logs” directory. Alternatively, look for the following line:
    or die “Can’t open the debug file: $!n”;
    …and replace it with:
    or warn “Can’t open the debug file: $!n”;

  3. Ah! figured it out. The directory you run the script from needs to have a subfolder already created named “logs” in order for the script to complete. Once in place, the script runs beautifully!

  4. Hi,
    This is very interesting and something we are considering using. Thank you for the clarity of your instructions, and the tips for finding the various PERL modules…it made this very easy to set up.
    One quick question:
    I have run this script a few times in the afternoon, and it seems to think today is tomorrow. In other words, Horizon thinks the date today is 13314, and the script output identifies today as 13315.
    Run at 2:19PM (EDST) on 6/16/06
    today is 13315
    looking for due date 17/Jun/2006 (13317)
    Of course, June 16, 2006 is 13314, and June 17 is 13316…
    This, in turn, leads to various mis-identification(s) of due dates in the output e-mails, which I will ‘hear about’ if I deploy the script, no doubt.
    I suppose I could modify:
    my $today = int ( time ( ) / (24 * 60 * 60) )
    to read:
    my $today = int ( (time ( ) / (24 * 60 * 60))-1 )
    or something, but it does seem odd…what am I missing? Don’t run the script in the PM?
    We run Horizon on Sybase…for the script I am using ActivePerl 5.8.8 on Windows XP Pro (and the clock on my system is set the to correct date and time 🙂
    Thanks for any insight you might be able to share! And thanks for the script!

  5. Hi Frank
    Thanks for spotting that! Living in the UK I’d forgotten that Perl can do some odd things in other time zones.
    I’ll have a look to see if I can come up with a better way of calculating the Sybase/Horizon date, as I suspect my quick & dirty calculation is only valid for the GMT time zone.

  6. hi;
    i am not able to compile the script; keep getting this error;
    C:scripts>perl settings.ini
    Can’t locate loadable object for module DBI in @INC (@INC conta
    C:/Perl/site/lib .) at line 261
    BEGIN failed–compilation aborted at line 261.
    Compilation failed in require at line 29.
    BEGIN failed–compilation aborted at line 29.
    help pliz

  7. Hi Ashneil
    You’ll need to make sure that you have the three required Perl modules (DBI,
    DBD::ODBC, and Mail::Sender) correctly installed, as well as a working ODBC connection to your Horizon database.

  8. Hi Dave;
    This is really great, it worked. Spend most of the time trying to install DBI and had errors with the make command. after searching for the solution. There is an easy way to install the three required modules. Type at command prompt.
    ppm install DBD-ODBC
    ppm install DBI
    ppm install mail-sender
    and finally got the ODBC conn for Horizon. Now its up and running smoothly.

Comments are closed.