A Koha ILS mashup to turbo-charge the sort1 and sort2 patron / borrower record fields

This video shares the idea about how to turbo-charge the sort1 and sort2 fields for patron records in Koha by utilizing Koha’s very own reports web service in tandem with a bit of JQuery.

If you are feeling impatient because you already know the background, feel free to jump to 3 minutes 10 seconds into the playtime, to see the actual action in progress.

Runtime: 14 mins 35 sec.

Show me the money…. er.. the barcode?

Fixing the issue of checked out items barcodes’ not showing up for patrons logged into the Koha OPAC.

The Problem

Earlier this morning, Pranab Roy who manages the Library at the Karnavati University‘s UnitedWorld School of Business‘s Kolkata campus popped up on WhatsApp with a question – “Sir, why is Koha not showing the barcode / accession number of a borrowed document when an user logs in into their own account via the OPAC?”

This is what he meant. And this is actually the expected behaviour, with Koha doing exactly what it was asked to do. I could understand his confusion since the OPAC search’s details view showed the barcode quite nicely, then why not for the users themselves?

A bit of backstory

Karnavati University Libraries had shifted to L2C2 Technologies‘ cloud platform from a pre-existing Koha instance maintained by a 3rd party. As such the system had quite a few issues. While we had fixed a larger number of these during the initial on-boarding stage, some of these are getting ironed out only now as the librarians hit these “bumps on the road”. Pranab’s problem was one such.

The Solution

The option to display the barcodes for a logged-in OPAC user’s checkouts (issues) is driven by the SHOW_BARCODE patron attribute. In Koha, patron attributes or more correctly ExtendedPatronAttributes are library-defined custom fields that can be applied to patron records e.g. voter / aadhaar card number, registration number etc.

SHOW_BARCODE is a boolean variable that is defined as either Yes or No. and it is loaded into Koha usually during the web-installer phase of Koha’s installation from the optional SQL dump file: /usr/share/koha/intranet/cgi-bin/installer/data/mysql/en/optional/patron_atributes.sql in Debian package based installations. The tiny file contains a single SQL INSERT statement:

INSERT INTO `borrower_attribute_types` (`code`, `description`, `repeatable`, `unique_id`, `opac_display`, `staff_searchable`, `authorised_value_category`) VALUES (‘SHOW_BCODE’, ‘Show barcode on the summary screen items listings’, 0, 0, 1, 0, ‘YES_NO’);

In the case of Karnavati University this optional patron attribute was not imported during the *original* installation done by the 3rd party support provider at the time. And without “SHOW_BARCODE” being set, Koha had no way of displaying the barcodes of checked out books to patrons logged in via the OPAC.

In the end, the following two lines followed by enabling the ExtendedPatronAttributes system preference cleared off the issue for Karnavati:
$ cd /usr/share/koha/intranet/cgi-bin/installer/data/mysql/en/optional
$ mysql -uroot -p koha_karnavati < patron_atributes.sql.

A memcached restart later (to be safe rather than sorry) the OPAC started showing the barcodes to logged-in patrons.

Using an office suite to generate the sql for Koha’s calendar

An in-house technique we use for fast-track, accurate loading of the long list of Indian holidays into Koha.

LEGAL DISCLAIMAR: This tutorial is strictly meant for educational purpose. We are in no way responsible if you attempt to follow these steps and end up messing up your production Koha installation somehow.

Setting up yearly calendars is an important task in Koha. Especially in a country like India where we also use other calendars including lunar calendars. Since the religious festivals / holidays usually use these other calendars, the holidays often fall on different days in different years in the Gregorian calendar that Koha uses.

As support providers, we are often asked to setup the calendar for our client-partners. While Koha provides a rather useful user-interface to setup calendars under the Tools menu, we have often found that not only setting up the long list of Indian holidays a tedious affair, it is also quite prone to operator error. So for the Unique holidays we usually use a LibreOffice (a Free Software Office Suite) Calc spreadsheet to quickly generate the batch of SQL statements that can be directed imported into the special_holidays table of Koha using the SQL backend.

We ask our client-partners to send us a spreadsheet that lists the holidays and the days they fall on according to the Gregorian calendar (see the columns marked in yellow above). And then we use a couple of simple formulae to generate the necessary SQL. This approach has two advantages : (a) it’s very fast and (b) completely free of errors from our end.

In this present case, it took us less than 2 minute to upload all the 25 holidays for 2020 into this particular Koha instance.

Caveat The only risk here is that we are going to access the database directly from the command-line and that, unless you know what you are doing, can end up badly, *if* you mess things up.

Getting LE certbot-auto to work on an aging Debian 7.x

Stuck with Debian 7 in 2020 and need certbot-auto to work? Here’s how we did it.

Yes, it is 2020 and it is very late in the day to be still using Debian 7.x. But you just may have a piece of critical infrastructure that is still running on that Debian 7 box and moving it may not be an immediate possibility. Your infrastructure component also happens to use LetEncrypt certs for SSL. Your certificate has just expired and you ran certbot to issue a new certificate. And BAMMMM! you hit this!

Replacing certbot-auto…
Creating virtual environment…
Installing Python packages…
/opt/eff.org/certbot/venv/bin/python: No module named pip.__main__; ‘pip’ is a package and cannot be directly executed
Traceback (most recent call last):
File “/tmp/tmp.BLzjDMi7yW/pipstrap.py”, line 177, in
sys.exit(main())
File “/tmp/tmp.BLzjDMi7yW/pipstrap.py”, line 149, in main
pip_version = StrictVersion(check_output([python, ‘-m’, ‘pip’, ‘–version’])
File “/usr/lib/python2.7/subprocess.py”, line 544, in check_output
raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command ‘[‘/opt/eff.org/certbot/venv/bin/python’, ‘-m’, ‘pip’, ‘–version’]’ returned non-zero exit status 1

Well, we did. The problem stems from the fact that with certbot-auto version 0.32 it stopped working with EOLed Linux distributions. This has hit distros like Debian 7.x that EOLed towards the end of 2018 which also dropped official certbot support. Debian 7.x (wheezy) uses an ancient version of pip that cannot be run as a module (python -m pip). And hence the mess.

This is how we got over it for the moment (it is Jan 2020 at the time of writing)

1. rm -rf /opt/eff.org

2. Download old 0.31 version of certbot-auto so that we can get around the version issueswget https://raw.githubusercontent.com/certbot/certbot/75499277be6699fd5a9b884837546391950a3ec9/certbot-auto

3. chmod +x ./certbot-auto

4. Run certbot-auto with the necessary switch ./certbot-auto --no-self-upgrade

And this was the result

Bootstrapping dependencies for Debian-based OSes… (you can skip this with –no-bootstrap)
Hit http://archive.debian.org wheezy Release.gpg
Hit http://archive.debian.org wheezy Release
Hit http://archive.debian.org wheezy/contrib Translation-en
Hit http://archive.debian.org wheezy/main Translation-en
Hit http://archive.debian.org wheezy/non-free Translation-en
Hit http://archive.debian.org wheezy/main amd64 Packages
Hit http://archive.debian.org wheezy/non-free amd64 Packages
Hit http://archive.debian.org wheezy/contrib amd64 Packages
Hit http://archive.debian.org wheezy/main i386 Packages
Hit http://archive.debian.org wheezy/non-free i386 Packages
Hit http://archive.debian.org wheezy/contrib i386 Packages
Reading package lists… Done
Reading package lists… Done
Building dependency tree
Reading state information… Done
gcc is already the newest version.
python is already the newest version.
python-dev is already the newest version.
python-virtualenv is already the newest version.
openssl is already the newest version.
libffi-dev is already the newest version.
libaugeas0 is already the newest version.
libssl-dev is already the newest version.
ca-certificates is already the newest version.
augeas-lenses is already the newest version.
The following packages were automatically installed and are no longer required:
libapache2-mod-fcgid libcarp-assert-more-perl libcarp-assert-perl libcgi-compile-perl libcgi-emulate-psgi-perl libdevel-stacktrace-ashtml-perl
libfcgi-procmanager-perl libfile-pushd-perl libfilesys-notify-simple-perl libfreeradius-client2 libhash-multivalue-perl libhtml-lint-perl libhttp-body-perl
libmodule-refresh-perl libplack-perl libtest-longstring-perl libtest-requires-perl libtest-sharedfork-perl libtest-tcp-perl rt4-apache2 rt4-clients rt4-db-sqlite
Use ‘apt-get autoremove’ to remove them.
0 upgraded, 0 newly installed, 0 to remove and 155 not upgraded.
Creating virtual environment…
Installing Python packages…
Installation succeeded.
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator apache, Installer apache

Which names would you like to activate HTTPS for?
– – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Shared in the hope that it may help someone else in a similar position.

Automating Koha : An Use-Case.

As Koha automates libraries around the world, automating Koha itself offers major benefits for people maintaining these Koha systems.

The background

In the context of the Indian sub-continent, labour being generally cheap, manually doing things is often the norm. Thus even as libraries adopt software like Koha to automate their operations, the management of Koha itself remains largely a manually driven process. While it is no doubt cheaper in the short term to do so, on the longer term, this involves invisible costs. For example, human errors creeping into the Koha configurations; a lack of situational awareness about the running system and thus no pre-emptive maintenance interventions that ultimate lead to higher down-times and disruption of services.

So, as our client-partners look at Koha to automate their libraries, we seek to largely automate the management and maintenance of Koha itself. The open-source nature of Koha lends well towards this. This blogpost aims to do two things : (a) share with our readers an idea of things that are possible with the most minimal of coding and (b) show case how this has benefited both us and our client-partners.

The automation use-case

Some time back we realised that it would be useful if we could let our client-partner users know in real-time exactly how many days their hosted service subscription was still valid for. The information was useful to them to plan their renewals in time. To do this we had initially opted for a small JQuery snippet placed inside IntranetUserJS system preference that took a hard-coded date which was the end date of their active subscription.

Problems encountered


At first, this was seen as a good thing by our client partners. Yet as the subscription ended and was renewed, we were hit by a problem. When they renewed it, we had to manually re-edit the JQuery code and enter the new end date. There were two problems here – (a) sometimes we simply forgot to update it after the payment was made and the system would show that the subscription had expired even though it had been renewed; (b) as anyone dabbling with JQuery knows, its easy to introduce typographical / syntax errors while editing JQuery, if you are careless while doing it.

The solution

The first confused our users and left them with a feeling of dissatisfation. The second was more our headache. So, we wondered can Koha itself help us *automate* this hosted account renewal process? Turns out the answer was “yes” as we looked at *two* basic Koha features – (a) local use system preferences aka system preferences that are generally defined by users of the system rather than by the Koha developers and (b) SQL reports web service API which allows calling on SQL reports via an definitive URL with their results being returned back in JSON format.

From hard-coding to flexibility

We started by defining a local use system preference named SubscriptionEndDate (yes, we are very imaginative 😉 ) which will be used to store the end date in the ISO 8601 format i.e. YYYY-MM-DD HH:MM:SS.

Next, we defined a simple SQL report that extracted the end date value from the systempreferences table in Koha.

The last step was to modify our existing JQuery snippet so that it no longer looked for a hard coded end date value within the snippet itself. Instead it would first call the SQL report web service to get the end date from the SubscriptionEndDate local use syspref as JSON data. Once the data was there, the rest of snippet remained same in terms of how it calculated and displayed the number of days remaining.

Introducing automation into the picture

With the above in place, it was now just a matter of updating the SubscriptionEndDate value when the client-partner renewed by paying up. As the payment got captured in our CRM system, it called an API that automatically triggered an update operation on SubscriptionEndDate in Koha setting it with the new end date. With less than 20 lines of code we had managed to automate the subscription date renewal process. We had managed to remove the twin issues of inconsistent manual updates and the possiblity of introducing typographical errors and breaking our IntranetUserJS configuration.

Revisiting Kerala eSMS service for updated versions of Koha ILS

If you are a Koha user from Kerala using the eSMS send driver written by us and found out that your SMS alerts have stopped after upgrading to 17.05 or later, then you should definitely read this.

For the impatient: Koha users from Kerala using the SMS::Send::IN::eSMS send driver, often find that after upgrading from 16.11 or earlier versions, their SMS alerts from Koha had stopped working. Usually this affects users who had used this previous blog post as the installation guide. This post addresses that and shows how to get SMS working again for eSMS service on 18.11 or later versions.

For the *really* impatient: Jump directly to the section Handling SMS::Send::IN:eSMS on supported versions of Koha

Background

About 3 years back, in early February 2017, we had published the only open-source SMS::Send driver implementation for Kerala Government’s eSMS transactional bulk SMS service for use with Koha ILS. The development was sponsored courtesy the State Librarian, Kerala State Central Library, Thiruvananthapuram, Kerala.

That time the latest stable version of Koha was 16.11. Up until then Koha’s C4/SMS.pm which acts as a wrapper against SMS::Send posed a small problem for Indian users thanks to a the requirement of senderid by Telecom Regulatory Authority of India (TRAI). Koha provided only two fields – the login and password fields. We in India needed three. So, we had to hack C4/SMS.pm as documented here.

The big change

At the end of May 2017, Koha 17.05 was released and with it came Bug id #13029. In the Release Notes, bug 13029 was defined as “Allow to pass additional parameters to SMS::Send drivers”.

This was a game-changer. We no longer needed to hack C4/SMS.pm. Handling extra parameters like SenderIDs, API keys or for that matter any arbitrary parameter(s) specific to a particular bulk transactional message provider could now be handled using a simple YAML file. The path to this YAML file is defined in koha-conf.xml and therefore making it instance-specific and multi-tenant friendly. <sms_send_config>/etc/koha/sites/<your_instance_name>/sms_send/</sms_send_config>

Handling SMS::Send::IN:eSMS on supported versions of Koha

At the time of writing, the supported versions of Koha are 19.11, 19.05 and 18.11. If your version is lower than 18.11, you should really upgrade. There are just three things to keep in mind; (a) there is no change in the SMS::Send::IN::eSMS code, it works out of the box provided you took care of “c” below; (b) no more hand editing C4/SMS.pm to handle the senderid parameter; and (c) you now need to create a YAML file eSMS.yaml at /etc/koha/sites/<your_instance_name>/sms_send/IN/eSMS.yaml with just the following text:

senderid: <put_your_senderid_here>

Well, that’s it! It just works!

P.S. The lawyer says we must add this – L2C2 Technologies or everyone associated with it, disclaims any and all responsibilities in the event of someone facing loss, damages either financial, operational or any problem whatsoever, due to or in course of following this blog post or any other on this blog. The information presented here is on AS-IS basis for personal educational purpose alone. This post is licensed under CC 4.0 BY-SA.