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/ No module named pip.__main__; ‘pip’ is a package and cannot be directly executed
Traceback (most recent call last):
File “/tmp/tmp.BLzjDMi7yW/”, line 177, in
File “/tmp/tmp.BLzjDMi7yW/”, line 149, in main
pip_version = StrictVersion(check_output([python, ‘-m’, ‘pip’, ‘–version’])
File “/usr/lib/python2.7/”, line 544, in check_output
raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command ‘[‘/opt/’, ‘-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/

2. Download old 0.31 version of certbot-auto so that we can get around the version issueswget

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 wheezy Release.gpg
Hit wheezy Release
Hit wheezy/contrib Translation-en
Hit wheezy/main Translation-en
Hit wheezy/non-free Translation-en
Hit wheezy/main amd64 Packages
Hit wheezy/non-free amd64 Packages
Hit wheezy/contrib amd64 Packages
Hit wheezy/main i386 Packages
Hit wheezy/non-free i386 Packages
Hit 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


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/ 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/ 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/ 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/ 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.

Zara hatke, zara bachke… yeh hai DataTables Meri Jaan!

A short tutorial on identifying and fixing DataTables errors arising from missing data in Koha ILS

The Problem

Recently we fielded support call from Parama (Sarkhel)-di, Librarian at Ramakrishna Sarada Mission Vivekananda Vidyabhavan. Her complaint – for a particular faculty member she was not able to see the member’s checkouts, instead it showed “Loading” and then nothing happened, even though the system was showing there were 9 items checked out to her.

RKSMVV being cloud hosted, we simply punched in the specific member’s cardnumber and then clicked on “Show checkouts” button. And voila! the error was right in front of us. Experience told us that it looked like a typical DataTables error.

What is DataTables

DataTables is a JQuery plugin for displaying information in HTML tables and adding interactions to them. It provides searching, sorting and pagination without any configuration. If you wish to learn more about it, given that Koha makes good use of the plugin, please visit DataTables examples index for a quick start.

Debugging the error

Since we expected the error to a DataTables error, our first step was to check our browser’s JavaScript console. And sure enough there was an error that said that it was triggered when a NULL value was passed to the escapeHtml() function at line number 285 inside Koha’s checkouts.js JavaScript library.

We still needed to know *what* exactly was passed to the escapeHtml() function. For that we clicked on the link to the right which pointed to line number 285 inside the checkouts.js file. As the debugger’s sources tab opened the file around line number 284, it became immediately clear the exact error. One of the checked-out items did not have a barcode assigned.

Now it was just the matter of finding out *which* of the 9 items checked out to the member did not have a barcode. First, we ran a SQL query on the instance’s issues table with the member’s borrowernumber to retrieve the itemnumbers of the checked out items, and then using this list of itemnumber we queried the items table to find out which of the 9 items had a missing barcode. The result was self explanatory.

By cross-referencing the biblionumber attached to the itemnumber we opened the offending item holding record in the edit mode from the staff client and for the time put in “FIX_BARCODE” as the temporary placeholder barcode. Immediately the member’s account showed up the table of checked out documents correctly. The member was requested to temporarily return the book so that the barcode may be fixed.

But why did it happen?

The book was lent out to the member several years back from, what is now a very, very, ancient version of Koha. At that time DataTables plugin was not a norm. About an year back the version was moved to latest version of Koha and the database updated. The error was triggered now, because this was the first time this specific member had come back to the client to borrow a book. Had she tried to issue or return a book before, the error would have been caught much before.

Since we fixed the error, we also checked the entire database of any such other cases. And sure enough there were 3 more books issued to 2 other faculty members like ages back which too did not have any barcode assigned. Parama-di noted the numbers down so that these books could be recalled back and their barcode updated.

Pro-Tip to avoid such errors

For people moving very old versions of Koha to newest versions, please run SQL queries to ensure that your all your items table’s items have homebranch, holdingbranch, itype and barcode are correctly assigned rather than having NULL or whitespace before you move the updated database into production mode.

IEM Trust chooses L2C2 Technologies as their Koha support partner.

To provide pan-India support to its campuses as a single consortium.

We are delighted to extend a very warm welcome to our newest client-partner – the Institute of Engineering & Management (IEM) Trust. We congratulate them on selecting our cloud hosted Koha ILS platform for catering to all their campuses across India. The IEM Trust is the first multi-campus consortium client-partner on L2C2 Technologies’s Koha support network. We are truly proud to be partnered with the institution that setup the first private engineering college in West Bengal and consistently remains a leading top-notch educational institution, attracting some of the best students.

At present L2C2 Technologies will cater to the hosted support for libraries at the following campuses:

1) IEM Gurukul Campus, Kolkata, West Bengal.

2) IEM Ashram Campus, Kolkata, West Bengal.

3) IEM Management House Campus, Kolkata, West Bengal.

4) UEM Kolkata Campus, Kolkata, West Bengal.

5) UEM Jaipur Campus, Jaipur, Rajasthan.

About IEM-UEM Group

The IEM-UEM Group, led by the IEM Trust, has spearheaded engineering education in the state of West Bengal with the setting up of the Institute of Engineering & Management in 1989 as the first self-financed engineering college in the state. Starting from a single campus, the IEM Trust over the years have expanded across the country with multiple campuses. It has setup University of Engineering & Management which is a deemed University under UGC rules, with campuses currently in Rajasthan and West Bengal.

IEM is a NAAC A-rated educational institution and has fared very well in the NIRF (National Institutional Ranking Framework) rankings conducted by Ministry of Human Resource Development (MHRD), Govt of India. In 2016, it was ranked as the 3rd best engineering college from West Bengal, coming after IIT Kharagpur and NIT Durgapur in the NIRF rankings.

At present it operates from 6 campuses around the country.


When the Laws of Library Science comes and knocks on your door

When an online Koha Library OPAC is the sole answer to finding an out-of-print book.

This post is somewhat of a personal nature. My love for libraries came from my original passion for Free & Open Source software. Being able to solve people’s problems using open source technology, to be able to preserve our local languages, our history, our culture and then being able to share and disseminate meant a lot to me in my youth. Now on the wrong side of 40s, it still continues to drive me.

Just a while back Parama di (Smt Parama Sarkhel) the librarian at Ramakrishna Sarada Mission Vivekananda Vidyabhavan college shared the following missive over FB Messenger to me:

মাননীয় গ্রন্থাগারীক সমীপেষু,
রামকৃষ্ণ সারদামিশন বিবেকানন্দ বিদ্যাভবন লাইব্রেরি


আমি শ্রী বিনোদ ঘোষাল, পেশায় একজন সাহিত্যিক, গত তিন বছর ধরে আমি কাজী নজরুল ইসলামের সমগ্র জীবনকে উপজিব্য করে একতি দীর্ঘ উপন্যাস রচনা করছি। যার প্রথম পর্বটি দীর্ঘ একবছর সংবাদ প্রতিদিনের রোববার পত্রিকায় ধারাবাহিকরূপে প্রকাশ পাবার পর মিত্র ও ঘোষ প্রকাশন থেকে ‘কে বাজায় বাঁশি’ নামে বই আকারে প্রকাশিত হয়েছে।

বর্তমানে আমি উক্ত গ্রন্থের দ্বিতীয় খণ্ড রচনা করছি। রিসার্চের কাজে আমার নরেন্দ্রনারায়ণ চক্রবর্তী রচিত ‘নজরুলের সঙ্গে কারাগারে’ গ্রন্থটি বিশেষ প্রয়োজন। বইটি কলেজস্ট্রিটে বা অন্যত্র খোঁজ করে কোথাও পাইনি।

ইন্টারনেটের সূত্রে দেখলাম আপনাদের লাইব্রেরিতে বইটি রয়েছে। আপনার কাছে অনুরোধ আমাকে অনুগ্রহ করে উক্ত বইটি যদি ফটোকপি করার অনুমতি দেন বা নির্দিষ্ট মূল্যের বিনিময়ে বইটির একটি ফটোকপি দেন তাহলে আমি উপন্যাসটি আরও তথ্যসমৃদ্ধ করে তুলতে পারি।

আশা করি অনুরোধটি বিবেচনা করবেন।

আপনার সাহায্যপ্রার্থী,
বিনোদ ঘোষাল

Briefly, this is a request from Sri Binod Ghosal, a young, multi award winning Bengali novelist and short story writer. He needs access to a book – “Nazrul-r songay karagarey” (lit. ‘In the prison with Nazrul’) by Sri Narendra Narayan Chackroborty, as research material for the next volume of his biographical novel “Ke Baajaye BaNshi” on Kazi Nazrul Islam – a Bengali poet, writer, musician, anti-colonial revolutionary and the national poet of Bangladesh. The book is presently out of print, and Sri Ghosal had no luck in locating a copy of it anywhere in College Street’s Boi para or elsewhere for that matter.

Luckily Google’s search engine came to his assistance. Parama di’s college library OPAC, being hosted online is indexed by Google and her library happens to have a copy of it. As a matter of fact it is probably the sole reference to a copy available at a library and that too within the distance of a local train ride from Sri Ghosal’s residence.

The five laws of library science by S.R. Ranganathan are often learnt rote. As practitioners, we often merely amble in the general direction of the Laws. But precisely in moments like this, when everything falls into place, the laws of Library Science manifest themselves in sheer, brilliant clarity.

As a co-founder of L2C2 Technologies, it is in these few and far between moments, that I once again find the reason and strength to carry on.

Antara Psychiatric Hospital & Rehabilitation Centre Library partners with L2C2 Technologies

Antara Psychiatric Hospital & Rehabilitation Centre at Kolkata chooses to automate their library using L2C2 Technologies’s cloud hosted Koha ILS platform

We are pleased to extend a warm welcome to the newest client-partner in Eastern India – Antara Psychiatric Hospital & Rehabilitation Centre, Kolkata. Incidentally Antara happens to be our forth hospital library and the first in psychiatric specialty. The Antara library has gone live on Koha ILS 18.11 series on L2C2 Technologies‘s cloud hosting platform.

About Antara

Established in 1971 and located at Antaragram, Baruipur in the outskirts of Kolkata, Antara today is the largest non-profit, non-governmental organisation in India to extend holistic mental healthcare facilities to people from all walks of life. The foundation stone for the present campus was laid in 1980 by Mother Teresa.

All payments in a date range with quick transaction view

A SQL report for Koha ILS

Yesterday was the last day of the year, when Sri Ashish Kumar Barik the young and enthusiastic librarian at Midnapore City College requested for a new custom SQL report that would allow him:

To see every payment made by every user in a given date range, sorted by MM/YY. It should show every type of payment made e.g. overdue fines, lost book fees, library card replacement. Basically everything that was charged to and paid by a patron

In this blog post we share it with all, so that others looking for something similar can have a ready template for their use. It’s our way to saying thank you and Happy New Year 2019 to all our readers, clients and well-wishers.

Putting it together

The report uses three tables in the Koha database – (a) accountlines; (b) borrowers and (c) categories.

The report adds a quick transaction view link next to each and every payment. For library staff wanting to find out the details of a particular payment, with this report that information is just a single click away.

The report metadata
Report name List all payments made by patrons between two dates
Report group Accounts
Notes List all payments made by patrons between two dates, sorted by date and patron name.
The SQL statement
    CONCAT("<a href=\"/cgi-bin/koha/members/", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view'
    , T2.cardnumber AS 'Card No.'
    , CONCAT(T2.firstname, " ", T2.surname) AS 'Name'
    , T3.description AS 'Category'
    , CONCAT(SUBSTRING(MONTHNAME(T1.timestamp), 1,3), " ", YEAR(T1.timestamp)) AS 'Billing Period'
    , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
    , CONCAT("₹", LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid' 
    `accountlines` T1 
     LEFT JOIN borrowers T2 USING (borrowernumber) 
    LEFT JOIN categories T3 USING (categorycode) 
   DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> 
ORDER BY DATE(T1.timestamp), CONCAT(T2.firstname, " ", T2.surname)
See it in action

Here is a short 2 minute video of it for those who want to see it action.

Wishing everyone a Happy New Year 2019.

Extracting patron images from Koha DB

This is part of the series of blog posts on how to extract your data from a Koha system where you unable to access the DB directly, but where you have “superlibrarian” access. In this part, we talk about extracting the patron images stored inside the patronimage table in your Koha database.


  • You should be comfortable using the GNU/Linux command line
  • Familiarity with SQL syntax
  • The Koha DB schema reference for your version of Koha
  • A Linux system with cURL and LibreOffice installed
  • Superlibrarian userid and password of your Koha instance

Modus operandi

  1. Use the Koha Reports module to download the list of the borrowernumbers of patrons having their image in the db
  2. Remove the header line and save it as a text file, with one borrowernumber per line
  3. Use cURL to get the CGISESSID cookie using the authentication SVC API call
  4. Execute the BASH script, passing the authenticated session id and the file holding the list of borrowernumbers as command line parameters
  5. Use the Koha Reports module to generate the IDLINK.txt batch patron image uploader file

The background

Patron images in Koha are stored in the patronimage table of the database. The table has following schema:

Column Type Size Parents Comments
borrowernumber int 10 borrowers the borrowernumber of the patron this image is attached to (borrowers.borrowernumber)
mimetype varchar 15   the format of the image (png, jpg, etc)
imagefile mediumblob 16777215   the image

A blob is a “binary large object” which means it is not a text field. The contents won’t make any sense if we used the report module to SELECT imagefile FROM patronimage. This brings cURL into the picture.

The patron image is served on the OPAC / staff client using the Perl script using an URL like this: http://<your_instance>/cgi-bin/koha/members/<borrowernumber>.

But before we can run cURL on this URL, we must do one more thing. We need our BASH script to login using our superlibrarian userid and password using Koha authentication web service API call.

We need to do this for two reasons: (a) login into our Koha instance with superlibrarian access and (b) get hold of the CGISESSID cookie.

Putting it all together

  1. A simple SQL report as given below with fetch us the borrowernumber of patrons with their image stored in the DB.

    SELECT borrowernumber FROM patronimage

    We will download the result as Open Document spreadsheet.

    Next we copy and save the list of patron borrowernumbers into a text file (let’s call it list-of-pix.txt), with one borrowernumber per line.

  2. Lets put together our small BASH script. We shall call it
    mkdir patronimages
    while read LINE
        do  curl -b CGISESSID=$1  -o ./patronimages/$LINE.png http://<your_instance>/cgi-bin/koha/members/$LINE
    done < $2

    NOTE: <your_instance> need to be changed with **your** actual server URL.
    Make the script executable by running chmod a+x

  3. From the command-line run the following command:
    $ curl -i http://<your_instance>/cgi-bin/koha/svc/authentication -d 'userid=<yourid>&password=<yourpass>'

    If we had put in the correct information, we shall see something like this:

    HTTP/1.1 200 OK
    Date: Thu, 20 Dec 2018 09:53:02 GMT
    Server: Apache/2.4.18 (Ubuntu)
    Set-Cookie: CGISESSID=4a732ae75d8991a994d7ad0df584f84c; path=/; HttpOnly
    Vary: Accept-Encoding
    Transfer-Encoding: chunked
    Content-Type: text/xml; charset=ISO-8859-1
    <?xml version='1.0' standalone='yes'?>

    This gives us the two things we need for the next step: (a) the CGISESSID and (b) the status as OK. OK signifies that we were successfully logged in.

  4. Now we run the script
    ./ f03a894f1885e891e30f6d40e6e9838c ./list-of-pix.txt

    And we shall see the patron images getting downloaded.

  5. Once the patron images have all been downloaded to patronimages folder created by the script, its time to generate the IDLINK.txt file, which we will place in the aforementioned patronimages folder. Again, we shall turn to Koha's Reports module with this small SQL script:
    SELECT CONCAT(borrowers.cardnumber,", ", patronimage.borrowernumber, ".png") AS 'IDLINK'
      FROM patronimage 
      LEFT JOIN borrowers ON patronimage.borrowernumber=borrowers.borrowernumber

    This will generate our listing for the IDLINK.txt. If you are not sure about what IDLINK.txt does, please read up from here.

  6. Download the report as Open Document Spreadsheet, remove the header line and copy the rest into a text file and save it as IDLINK.txt in the patronimages folder.
  7. Zip up the patronimages folder as mentioned here and we have the patron images ready for a batch upload.

Display the totals for fines & fees, payments made, the outstanding, written off and forgiven amounts between a specified date range

A nifty single line SQL report for accounting using Koha.

Earlier this week, Sri Kalipada Jana, librarian at our client-partner Basanti Devi College, Kolkata, filed a new custom SQL report request. He wanted a report that did the following:

To list the total fines accrued, total paid so far and the total outstanding fines / fee between 02 (two) given dates.

Now, the readers of this blog who are acquinted with ready-made, user contributed SQL report library on the Koha Community wiki will know that there are a quite a few reports available that generate reports similar to Kalipada’s requirement. However these ready-made SQL reports usually generate this data at one data point at a time e.g. one report will provide total fines, another will provide total outstanding fines and so on. Further (and perhaps left as a exercise to the reader) these ready reports usually do not take into account “reversed charges“, “partial payments” entered as credits etc.

The report

The report presents a consolidated, single line of the total fines & Fees (F + FU + N + A + M + L), amount outstanding (F + FU + N + A + M + L), paid (P + C), written off (W) and forgiven (FOR) between a date range. If you wish to learn more about the mnemonics used within the brackets, you should look at the “Hard Coded Values” entry on the Koha Wiki.

By itself, the report is simple, it aggregates the totals presented by 05 (five) SQL sub-queries and shows it together. One interesting thing to note in the report is the use of “runtime variables“. The two run-time variables @FromDate and @ToDate are used to hold the user specified start and end date, instead of asking for the same repeatedly for each sub query.


(SELECT (@FromDate:=<<From date|date>>) AS 'From (y-m-d)', (@ToDate:=<<To date|date>>) AS 'To (y-m-d)') AS T1, 

    IFNULL(ROUND(SUM(accountlines.amount), 2), "0.00") AS 'Total Fines/Fees' FROM accountlines 
    accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND  DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T2, 

    IFNULL(ROUND(SUM(accountlines.amountoutstanding), 2), "0.00") AS 'Total O/S' FROM accountlines 
    accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND  DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T3, 

    IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""), "0.00") AS 'Paid / Credited'  FROM accountlines 
   accounttype IN ('PAY', 'C') AND description NOT LIKE "%Reversed%" AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T4, 

    IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""),"0.00") AS 'Written off'  FROM accountlines 
    accounttype='W' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T5, 

    IFNULL(REPLACE(ROUND(SUM(amount),2), "-", ""), "0.00") AS 'Forgiven'  FROM accountlines 
    accounttype='FOR' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T6