Monthly Archives: February 2010

Custom printed overdue postcards

My library has a long and complicated history of how to deal with overdue notifications. We’ve tried a few different forms of printed notices, and at one time we even tried to telephone each patron with overdues. After we switched to Koha we got a new option: sending overdue notices via email. Koha didn’t have a built-in notification system at the time, but the open nature of the application meant we could create our own scripted process to query the Koha database and get the information we needed.

The script, written by our director at the time, Stephen Hedges, queried the Koha database for patrons with overdues and did a few things at once with the results of this query:

  1. Send overdue notices via email.
  2. Create a file of overdues data for patrons without email.
  3. Restrict (“debar”) patrons with items overdue for more than 30 days.

In Koha 3, some of this work can be taken over by new built-in notification functionality. With the Overdue notice/status triggers feature we can define when overdue notices should be sent to patrons and when patrons should be restricted for their overdues (taking care of #3 above).

The drawback to Koha 3’s Notices feature is that it doesn’t give you much help when it comes to printed overdue notices. Koha will send an email notice to any patron who has an email address in their patron record (taking care of  #1 above). For patrons without email, Koha collects all of their overdue notices into a single email which is sent to the Koha administrator. Presumably the idea is that the messages can be printed out and mailed by hand.

Unfortunately this won’t work for us: We don’t want to send letters. It’s too much manual labor to stuff envelopes and stick stamps.  We want to send post cards. They’re cheaper. Of course for privacy reasons (not to mention space constraints) we can’t print out a list of a patron’s overdue items on a postcard. We want to send them a generic reminder which includes our web address (for online renewals) and phone numbers to our branches. So even with improvements in Koha 3 we need to do some work to take care of #2 from the list of tasks performed by the old overdues script.

Let’s take a look at how the old script worked. It began by performing this query:

[sourcecode language=’sql’] SELECT issues.borrowernumber, firstname, surname, streetaddress, physstreet, city, zipcode, emailaddress
FROM issues, borrowers
WHERE returndate IS NULL
AND TO_DAYS( NOW( ) ) – TO_DAYS( date_due )
AND issues.borrowernumber = borrowers.borrowernumber
AND gonenoaddress < 1
AND borrowers.categorycode != 'HB' ORDER BY issues.borrowernumber

It limited the query to items which were between 8 and 30 days overdue, where the "gonenoaddress" flag was unset (less than one, meaning in this case zero), and where the patron didn't have the 'HB' categorycode (our "homebound" category for home-delivery patrons).

As far as printed notices are concerned, the purpose of the old script was to create a CSV file containing the name and address of each patron with overdues.  Since we're going to send a non-personalized postcard to each patron, we don't want the output to include any other personal details. Here's what the results looked like:

“name” “address1” “address2” “city” “zipcode”
“John Smith” “1 Main Street” “” “Nelsonville OH” “45764”

In updating this for Koha 3 we can make that query a little bit more portable by having it check whether the patron in question has a category that requires overdue notices:

[sourcecode language=’sql’] SELECT issues.borrowernumber, borrowers.firstname, borrowers.surname, borrowers.address, borrowers.address2,, borrowers.zipcode
FROM issues, borrowers, categories
WHERE issues.returndate IS NULL
AND TO_DAYS( NOW( ) ) – TO_DAYS( issues.date_due )
AND issues.borrowernumber = borrowers.borrowernumber
AND borrowers.gonenoaddress < 1
AND borrowers.categorycode = categories.categorycode AND categories.overduenoticerequired = 1
AND != ''
ORDER BY borrowers.surname,borrowers.firstname

I've added a check that categories.overduenoticerequired is 1, which eliminates the need to hard-code the ‘HB’ patron category. I’ve also added a check to make sure the patron email address field isn’t empty (the old script performed that check elsewhere).

Since I’m more comfortable in PHP than Perl, I use a PHP script to query MySQL and format the results to be saved as a CSV file just like the old script did.

Taking it to the Post Office

We’ve successfully pulled the data we need. What next? We use an online service called Click2Mail, a “trusted and accredited partner to the United States Postal Service.” Click2Mail allows us to define a custom postcard template with our own personalized message. We can then upload our CSV file containing the name and address information for each patron with overdues. The service parses that CSV file, prints one postcard for each patron, and delivers it to the USPS for delivery. Click2Mail even checks your mailing addresses for possible errors and lets you review them.

Their cost estimator puts the cost of sending 100 single-sided postcards at about $30. Considering the time, effort, and cost of stuffing and stamping 100 letters, Click2Mail seems to be a great value.

Taking ownership of Koha

One of the promises of using Koha or any other Open Source ILS is that you’re not tied any one support company. “No vendor lock-in.” But it’s important to understand that this isn’t a promise that libraries can take for granted–in particular, libraries who contract with a support company for hosting of their Koha system. We need to be aware of what that means in practical terms and be prepared to put that promise to the test when the time comes. There are steps that we can take to make sure we’re protecting our own interests.

Insist on access to your database

We as libraries should own our data. That means the database of our patrons, the items in our collection, our authority records, etc. Everything stored by the ILS should belong to us. After all, we put it there. When we contract with another company to host our Koha system, we’re giving them the keys to the vault. We have to put our trust in them, but we can also protect ourselves.

It’s simple: our data is in the database. If we want to retain ownership of that data we need to have access to that database. In my opinion this should be part of any Koha hosting agreement. If you have read access to your database you can run your own queries and leverage your data in ways that Koha may not do out of the box.  Does Koha not include the kind of report you need? Hire a programmer to write a script to pull the data and manipulate it however you want. It doesn’t even matter what kind of scripting language you want to use: Perl, PHP, Ruby–anything that can connect to your database will work.

If you want to be prepared for disaster I also suggest you ask for access to regular database dumps, or ask for privileges to do the data dumps yourself. This should be in addition to whatever data backup plan your host has at their end.

Know what’s going on in the background

There is more to Koha than the database. If you ever decide to change to another Koha host/support company you’ll need to know some things about your Koha installation that aren’t stored in the database.  Your host will have set up cron jobs to run background scripts on a regular schedule. Communicate with your host about what these settings are. Know when things are running and how often.

Insist that any development you sponsor be released to the Koha community

If you’re interested in preventing being locked in to any one vendor this is very important. If you pay your Koha support company to develop a new feature for Koha and they don’t release it as open source, your Koha installation will be unique and potentially incompatible with Koha installations built from the official release. Your host will be able to say to you, “Sure you could switch hosts, but you’ll lose such-and-such feature and the data associated with it.” This is what vendor lock-in is all about.  Insist that your support company/host to be a  part of the Koha open source community. Insist that anything they develop for you gets released to the community as soon as it is complete. Or better yet, ask that they do this development out in the open, using public source repositories which can be accessed by other Koha developers. To insist on these conditions is to protect your organization and make sure you can freely make decisions about Koha support and hosting in the future.

The worst case scenario

In the worst case scenario your hosting and support company vanishes from the face of the earth and takes your data along with it. Because you’ve got a backup and details about other required settings, you can pick right up where you left off with a brand new host. Because the features you sponsored were released as open source, they’re either already in the standard Koha code or can be re-integrated by your new support company.

The best case scenario

In the best case scenario, you’re moving from one hosting and support company to another for reasons other than catastrophe.  Because you’ve taken ownership of your data and taken an active role in understanding your Koha configuration, you’re able to bring everything to the table your new host needs. Migrating from one standard Koha installation to another is so simple compared to ILS migrations of the past that you’ll be amazed.