KISS your Koha patron categories

A case study of applying the KISS principle to simplify and standardize your Koha patron categories.

KISS, an acronym for “keep it simple, stupid” or “keep it stupid simple”, is a design principle noted by the U.S. Navy in 1960. The KISS principle states that most systems work best if they are kept simple rather than made complicated; therefore, simplicity should be a key goal in design, and unnecessary complexity should be avoided. The phrase has been associated with aircraft engineer Kelly Johnson. Variations on the phrase include: “Keep it simple, silly”, “keep it short and simple”, “keep it simple and straightforward”, “keep it small and simple”, or “keep it stupid simple”. [1]

Earlier today we received a request to add a new SQL report to generate a list of all students with their email ids from the librarian at our client partner Sister Nivedita University. The request prima facie was simple. However, when we looked at the patron categories for students under Administration, the first reaction was “Uh oh!”.

Let me explain. There were 8 patron categories for students (of all types) and all these student patron categories had different mnemonic codes that were as follows:

DIP Diploma – 2 years
GNM-3 General Nursing and Midwifery – 3 years
RS Research scholar
SPG-2 Student Postgraduate – 2 years
SPG-3 Student Postgraduate – 3 years
SUG-3 Student Undergraduate – 3 years
SUG-4 Student Undergraduate – 4 years
SUG-5 Student Undergraduate – 5 years

Still, just to fulfill the service request, all we needed to do was to create an SQL query like

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode IN 
('DIP', 'GNM-3', 'RS', 'SPG-2', 'SPG-3', 'SUG-3', 'SUG-4', 'SUG-5')

The ‘problem’

There was no way to know from the mnemonic values that all these codes designated a student patron category, as there was no uniform standardization of the values used for student categories. To put the problem in a perspective that library professionals will understand easily – e.g. if we saw a DDC class number that started with 004, we’ll immediately know that it belongs to “computer science“, but what if the computer science documents were marked out with numbers that did not denote computer science under DDC???

Further, if in the future, the library added another student patron category, the SQL report would need to be updated in order to give the correct output that will include members belonging to this newly added category.

The ‘solution’

Luckily the solution was both simple and straightforward. We had to standardize and re-code the student patron categories and update the affected patrons. To do that we prefixed every student patron category with “STD_“. And thus, the student patron categories became:

STD_DIP2 Diploma – 2 years
STD_GNM3 General Nursing and Midwifery – 3 years
STD_RS Research scholar
STD_PG2 Student Postgraduate – 2 years
STD_PG3 Student Postgraduate – 3 years
STD_UG3 Student Undergraduate – 3 years
STD_UG4 Student Undergraduate – 4 years
STD_UG5 Student Undergraduate – 5 years

This was followed by updating the categorycode in the borrowers table to reflect the new changes. As a result the SQL query became much simplified:

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode LIKE 'STD_%'

If the library added a new student patron category in the future, all they would need to do is prefix the code with “STD_” and this query would continue to work without any change with this simple and straightforward update.

Legalese

This solution required us to directly access the production database with MySQL’s foreign key check turned off with SET FOREIGN_KEY_CHECKS=0;. If you get inspired to follow this, please ensure you have a complete database backup before you attempt this. And if by chance, you manage to mess it up or damage your database while attempting to do this, we are NOT responsible in any manner.

References [1] https://en.wikipedia.org/wiki/KISS_principle

“Fixing” ascii sorting in SQL Reports

A quick-n-dirty SQL tuturial

The Problem

Recently there was a request from IDSK – they wanted the accession register SQL report for their Rabindranath Tagore Grant collection books to be listed in a natural sort. Documents of this collection carry the prefix “RT” which is fine and dandy. The “problem” started after that. In their earlier legacy system (SOUL v1) when these books had been accessioned, they did not use an fixed uniform length accession no for this collection. For example, instead of RT00001, it was entered RT1, RT2 and so on. Further, their problem was compounded by another glitch. In several of these accession nos, there were spaces inserted between “RT” and the actual numerical part. When they migrated to Koha with L2C2 Technologies, these problems were carried over to the new system as data curation and correction was not part of the deliverables. Now, three years later, when reports were being asked for, these issues had come back to haunt them.

The “Solution”

The RT collection has over 4000+ books and a manual fix was not feasible at this time. So using the ORDER BY keyword in the report’s SQL like this: ORDER BY LPAD(REPLACE(REPLACE(TRIM(items.barcode)," ",""),"RT",""), 10, ' ') ASC we at least managed to hide the problem and also got natural sorting to work. The first REPLACE() took out any space in the accession no, and the second REPLACE() stripped off the “RT” prefix, leaving us with only the number. Finally using LPAD() we zero prefixed the number so that each one was exactly 10 characters long. And we had our naturally sorted list of accession numbers in the report as we can see below.

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.

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
SELECT 
    CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=", 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' 
FROM 
    `accountlines` T1 
     LEFT JOIN borrowers T2 USING (borrowernumber) 
    LEFT JOIN categories T3 USING (categorycode) 
WHERE 
    T1.accounttype="PAY" 
    AND
   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.

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 * FROM 

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

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

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

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

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

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

Cheers!

Koha Quick tip: Removing the dot from salutation like “Ms.” after a bulk patron import

A quick-n-dirty way to remove the trailing dot from salutation field after a bulk patron import into Koha.

Earlier today the deputy librarian at our client partner Gouri Devi Institute of Medical Sciences and Hospital (GIMSH) called us wanting to figure out how to fix a small, but crucial problem. The employee list from GIMSH’s HR department was used to create the patron bulk import CSV [1] file. All 143 records got imported without a glitch and each individual records looked perfectly OK when viewing them. However, when she tried to edit these patron records, she noticed that the salutation (e.g. Mr, Mrs, Ms etc.) would not show up in the salutation drop-down during edit! 🙁

missing salutation
Missing salutation even though salutation was entered into the database

Explanation

About 141 out of the total 143 records had a period.” after the salutation and two didn’t. These two records which did not have the period at the end of salutation were displaying the stored value correctly in the drop-down, rather than showing as being blank. As it happens, Koha uses the BorrowersTitles system preference to store the options to display. The default options are Mr|Mrs|Miss|Ms. As you can see, none of these have a period at their end. So, while the salutation field value was stored in GIMSH’s borrowers table, it was not being displayed during the edit as the value didn’t match with the values stored in BorrowersTitles syspref.

The “Fix”

***WARNING*** The following step requires you to directly execute a SQL on the Koha database. This is usually not recommended, but some times, it is required to quickly fix a problem.

Using the TRIM() string function of MySQL in tandem with the specifier trailing [2] we can take out the “.(period) at the end of the salutation from all the records in the borrowers table in the following manner:

UPDATE `borrowers` SET `title` = TRIM(TRAILING '.' FROM `title`);
salutation drop down is working now
salutation drop down is showing the stored value correctly now

References

[1] See the entry Comma separated values from Wikipedia.

[2] String Functions in MySQL 5.5 – http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_trim

[3] Sample patron data file – patron_import.csv NB. Those of you who wish to study how the HR data was prepared for import into Koha, can look here for a sample CSV file with dummy data (do not worry about privacy as the data is fake).