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.

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.

Prequisites

  • 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 patronimage.pl Perl script using an URL like this: http://<your_instance>/cgi-bin/koha/members/patronimage.pl?borrowernumber=<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 get_pix.sh.
    #!/bin/bash
    
    mkdir patronimages
    
    while read LINE
        do  curl -b CGISESSID=$1  -o ./patronimages/$LINE.png http://<your_instance>/cgi-bin/koha/members/patronimage.pl?borrowernumber=$LINE
    done < $2

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

  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'?>
    <response>
      <status>ok</status>
    </response>

    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 get_pix.sh script
    ./get_pix.sh 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 get_pix.sh 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 * 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!

RTFM Series : Memcached and “DBI Connection failed: Access denied for user [..]”

Stumped by the Koha v 18.05’s refusal to access the database after a koha-remove followed by a koha-create using the same instance name? Then read on!

This post applies strictly to Koha 18.05 which was released on May 24, 2018. The new version with its new features has created a lot of excitement among the user. However the version has some major changes and unless you **CLOSELY** read and understand the release notes you will asking for trouble.

In this post we are going to talk about Memcached which is turned on by default from 18.05. If you overlook this fact, you may see yourself wasting hours trying to troubleshoot a problem, which may inexplicably (not quite even though it looks that way) go away after re-starting your system.

Last weekend my young friend Jayanta Nayek spent nearly a day trying to understand why he was getting the error – “DBI Connection failed: Access denied for user” whenever he tried to access the web-installer part of the staff client on his new 18.05 instance. Since he was on a test system, he had followed the old rinse-and-repeat routine. So when his installation did not work out for the first time, he ran sudo koha-remove library and then re-ran sudo koha-create --create-db library to start afresh.

When he re-created the instance i.e. “library”, he was stumped with the following error :

Software error:

DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: Access denied for user 'koha_library'@'localhost' (using password: YES) at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1490. at /usr/share/koha/lib/Koha/Database.pm line 103

Of course, when he tried to access the koha_library database from the mysql command-line client using the user and pass from his /etc/koha/sites/library/koha-conf.xml, it worked perfectly. But if he came back to the browser and tried to access the web-installer, the error would return.

So what was happening here? In one word – memcached! Memcached is an open source, distributed memory object caching system that alleviates database load to speed up dynamic Web applications. The system caches data and objects in memory to minimize the frequency with which an external database or API (application program interface) must be accessed. (Source: What is Memcached?).

In simple terms what this means for Koha is that memcached caches the frequent database queries fired off by Koha. And if an SQL result set has not changed since it was last queried *and* is already stored into memcached, it offers the data from in-memory hashes rather than using a more time-consuming database lookup process. Memcached (along with plack is intended to make Koha work faster under heavier loads).

When Jayanta would run sudo koha-remove library and followed it up with sudo koha-create --create-db library the Memcached server was not restarted, and kept holding on to the *original* database access hashes. Whereas following koha-create command to re-create the instance, the database authentication credentials (user & pass values from koha-conf.xml) were changed. That is why when he tried to directly access via the mysql command line client, it worked as the CLI client did not know about memcached. But when he tried to access the web-installer it failed as the connection query hash offered up from memcached had the old and not longer existing credentials.

So, if any reader of this blog should find themselves facing a problem like this, simple run the command sudo service memcached restart and once memcached has restarted, access the web-installer. It will work this time. Since memcached is an in-memory storage, the restart clears up the hashes and when the web-installer tries to access the database, it leads to what is called a “cache-miss” and thus the queries get run against the actual DB using the access credentials stored in koha-conf.xml.

And for goodness sake READ THE RELEASE NOTES 😉

Generate a single sheet custom MARC21 framework in 2 minutes

For intermediate Koha ILS users who wish to quickly generate a single tab MARC framework.

Last Thursday, Ashish Kumar Barik, librarian at our new client-partner Midnapore City College filed a support ticket asking for a custom single sheet MARC21 framework or what is more commonly referred to by LIS professionals as a “worksheet“. He wrote that he wanted the following tags 000, 003, 005, 008, 020, 040, 041, 044, 082, 100, 245, 250, 260, 300, 440, 490, 500, 504, 650, 700, 942 and that the sub-tags/fields should be set as in the default marc framework shipped with Koha. We promised him his new framework. Being new to this side of Koha, he of course had missed out two key fields without which his system would be rendered practically useless i.e. the two local use tags952 and 999. Koha uses 952 to handle holdings (item) information and 999 is purely an internal tag used to track the bibliographic records.

Now anyone who has ever setup a new MARC framework knows that it can be a laborious and time consuming task. Further, there are chances of introducing inadvertent human errors that may lead to error or bad data when used as a part of the framework. As a result, at L2C2 Technologies we have developed several well defined strategies to manage custom marc frameworks for our clients. In today’s blog, we are going to share the simplest of the techniques we use in cases like this. The outcome of this exercise is a 100% error free marc framework generated in less than 2 minutes.

LEGAL DISCLAIMER: The next steps involve directly accessing and making changes in the the Koha database. So use these instructions at your own risk, if you face any data loss, corruption or system errors we are not responsible.

The Steps

  1. We used a regex capable editor like Notepad++ to quote the fields mentioned by Ashish, so that 000, 003, 005, 008, 020, 040, 041, 044, 082, 100, 245, 250, 260, 300, 440, 490, 500, 504, 650, 700, 942 became ‘000’, ‘003’, ‘005’, ‘008’, ‘020’, ‘040’, ‘041’, ‘044’, ‘082’, ‘100’, ‘245’, ‘250’, ‘260’, ‘300’, ‘440’, ‘490’, ‘500’, ‘504’, ‘650’, ‘700’, ‘942’. And while we did that, we also added the following fields missing in his list i.e. ‘952’, ‘999’.
  2. Next we defined a new framework MCC1 (MCC Framework) by visiting Home -> Administration -> MARC bibliographic framework -> New Framework
  3. Next we copied the default framework into MCC1 as its base, since that is what Ashish had wanted. At this point, the MCC1 framework is exactly same as the default framework of Koha.
  4. Next we fired up the MySQL console and logged in with the user id and passwd from MCC’s koha-conf.xml, and chose Ashish’s database in this case koha_mcc for the next steps.
  5. Fired the following SQL query :
    UPDATE
       `marc_subfield_structure` 
    SET
       tab=0
    WHERE 
       `frameworkcode`='MCC1' 
    AND 
       `tagfield` IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942')
    AND
       `tab`!=0;

    MySQL client told us 152 rows were affected.

    EXPLANATION: This moved all 1XX to 9XX (except 952 and 999) marc fields into Tab 0. The images below help illustrate the condition after this step:

  6. The next step was to set the rest of the fields outside the list supplied by Ashish *plus* 952 and 999 to be ‘ignored’ by Koha when using the MCC1 framework. And thus the following SQL query:
    UPDATE 
        `marc_subfield_structure` 
    SET 
        `tab`='-1' 
    WHERE
        `frameworkcode`='MCC1'
    AND 
        `tagfield` NOT IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942', '952', '999') 
    AND
        `tab`!=0;

    This time MySQL reported that 3416 rows were updated.

  7. Our last step at the MySQL command line was the following query that removed the unwanted 0XX fields from Tab 0 :
    UPDATE 
        `marc_subfield_structure` 
    SET 
        `tab`='-1' 
    WHERE
        `frameworkcode`='MCC1'
    AND 
        `tagfield` NOT IN ('000', '003', '005', '008', '020', '040', '041', '044', '082', '100', '245', '250', '260', '300', '440', '490', '500', '504', '650', '700', '942', '952', '999');

    MySQL reported 341 rows were affected.

  8. Coming back to MCC’s Koha staff client, we did the most important thing i.e. running MARC Bibliographic framework test. The test came out clean without any error.
  9. That’s it! MCC’s custom MARC framework is ready for use. Click on the image below and then zoom in to see the details up close.

Displaying unique title and volume count on the Koha staff client – Part 2

In this followup to our previous post on upgrade-friendly way of displaying unique title and copies’ count on the staff client, we explore a few variant use-cases reported by our readers.

Last December when we posted about the nifty little trick of displaying unique title and volume count on the Koha staff client, we didn’t realize that so many people would find something to be so useful. We received quite a few comments from all over the world. In this post, we are going to try and look at some of these use-cases that people thought could use something like this. So, if you are reading this and haven’t read the original post, it may be helpful to read Part 1 first.

The questions we received were:

(a) what if we have multiple branches?
(b) How to show the stats for the user’s logged in branch only?
(c) How do we show the overall totals if we have multiple branches?

Recently, the library at Our Lady Queen of the Mission School, Salt Lake had started to catalog both their junior library as well as the senior library. So, they presented the perfect opportunity to showcase these 3 use-cases.

1. What if we have multiple branches?

This was a question that came from Hussein Al-Nasri from Egypt. Hussein had tried the code snippet, but since it was hard-coded to handle only one single branch (the first on in the list of the JSON data returned), it wasn’t showing him what he wanted – the data for each of his branches.

The solution
$(document).ready(function() {
  if ( $('#main_intranet-main').length ) {
  $.getJSON("https://qmsl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=1&annotated=1.", function(data) {
    if ( data.length ) {
      $('#news1').prepend('<div class="newsitem" id="mystats"><table class="table table-striped" style="width: 100%; background: none;"><thead><th colspan="3" style="text-align: center; font-weight: bold; padding: 8px; line-height: 1.42857143; vertical-align: middle; text-transform: uppercase;">Library Statistics</thead><tbody><tr id="mystatstb"><td><strong>Branch</strong></td><td><strong>Unique titles</strong></td><td><strong>Total Copies</strong></td></tr></tbody></table></div>');
      for ( var key in data ) {
        $('<tr id=\"tr'+ key + '"><td class="text-center">' + data[key].homebranch + '</td><td class="text-center">' + data[key].bibs + '</td><td class="text-center">' + data[key].items + '</td></tr>').insertAfter( $( '#mystatstb' ) );
      }
    }
  });
  }
});

2. How to show stats only for the logged in branch?

This came from Freddy Enrique Pelayo from Peru, South America. He left a comment on the blog post asking:

if the system were to have more than 2 branches, will the screen show information according to the branch the user logged in?

The solution
$(document).ready(function() {
  if ( $('#main_intranet-main').length ) {
  $.getJSON("https://qmsl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=1&annotated=1.", function(data) {
    if ( data.length ) {
      $('#news1').prepend('<div class="newsitem" id="mystats"><table class="table table-striped" style="width: 100%; background: none;"><thead><th colspan="3" style="text-align: center; font-weight: bold; padding: 8px; line-height: 1.42857143; vertical-align: middle; text-transform: uppercase;">Library Statistics</thead><tbody><tr id="mystatstb"><td><strong>Branch</strong></td><td><strong>Unique titles</strong></td><td><strong>Total Copies</strong></td></tr></tbody></table></div>');
      for ( var key in data ) {
        if ( $('#logged-in-branch-code').html() == data[key].homebranch ) {
          $('<tr id=\"tr'+ key + '"><td class="text-center">' + data[key].homebranch + '</td><td class="text-center">' + data[key].bibs + '</td><td class="text-center">' + data[key].items + '</td></tr>').insertAfter( $( '#mystatstb' ) );
        }
      }
    }
  });
  }
});

Explanation

The key line here is $('#logged-in-branch-code').html() == data[key].homebranch. Whenever an user logs into the Koha staff client, the hidden <span> element with the id logged-in-branch-code holds the code for the user’s logged in branch. In the above snippet, we simply introduce a check to see if the code matches the homebranch code in the JSON array. If it does, we show the value for that branch and not for the other branches.

3. How to show the total for all branches?

This scenario was pointed to by Dr. Apurba Jyoti Mazumdar, Assistant Librarian, Assam Univerity, Silchar, India.

The solution

$(document).ready(function() {
    if ( $('#main_intranet-main').length ) {
    $.getJSON("https://qmsl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=1&annotated=1.", function(data) {
        if ( data.length ) {
            $('#news1').prepend('<div class="newsitem" id="mystats"><table class="table table-striped" style="width: 100%; background: none;"><thead><th colspan="3" style="text-align: center; font-weight: bold; padding: 8px; line-height: 1.42857143; vertical-align: middle; text-transform: uppercase;">Library Statistics</thead><tbody><tr id="mystatstb"><td><strong>Branch</strong></td><td><strong>Unique titles</strong></td><td><strong>Total Copies</strong></td></tr></tbody></table></div>');
            var totalbibs = 0;
            var totalitems = 0;
            for ( var key in data ) {
                $('<tr id=\"tr'+ key + '"><td class="text-center">' + data[key].homebranch + '</td><td class="text-center">' + data[key].bibs + '</td><td class="text-center">' + data[key].items + '</td></tr>').insertAfter( $( '#mystatstb' ) );
                 totalbibs = totalbibs + parseInt(data[key].bibs);
                 totalitems = totalitems +  parseInt(data[key].items); 
            }
            $( '<tr><td><strong>TOTAL</strong></td><td class="text-center"><strong>' + totalbibs + '</strong></td><td  class="text-center"><strong>' + totalitems + '</strong></td></tr>' ).insertAfter(  $('#mystats tr:last')  ); 
        }
    });
    }
});

Conclusion

As you may have noticed, in this version, we added an extra check which ensured that we only display this grid of data *only if* any data is returned. We hope that by answering a few of your questions, this post is of some use to some of you.

Quicktip – Ordering the display of your custom patron fields

An easy-peasy way to order the display of Koha’s ExtendedPatronAttributes fields.

This is going to be a brief tutorial, ExtendedPatronAttributes provide the capability to add custom fields to Koha’s patron management e.g. if we wish to capture say Program enrolled or Roll No. or other demographic details that we may be required to maintain under (a) law of the land or (b) governing rules of our library, this feature of Koha is friend we need to take help of.

If you have just landed on this blog and have no idea what we are talking about, we suggest that you may like to first read this earlier post – Harnessing Koha’s ExtendedPatronAttributes (aka patron custom fields). And while you are here, you may also like to visit this article.

Ok! Enough backstory, now back on topic! 🙂 When we define new custom fields to Koha using Home › Administration › Patron attribute types, the key thing to remember here is that these are ordered and displayed using alphabetic ascending sorting order of the defined Patron attribute type code.

Which means, when we defined a list of custom fields to capture student registration details e.g. as given in the screenshot below, we want them to be displayed in the order of the codes are numbered in the blue circles i.e. Student code, Registration No. and Roll No. in that order respectively.

Instead what we get is this:

The Solution

We simply need to number our codes. The way to do this is simply to prefix them with a serial number. Here we chose to use 00_, 01_, 02_”. Of course, this also meant that we had to dump off our old codes and put in these as replacement.

Hint: Do this before you start using the defined fields while entering your patron records. While it can be done later, you need to know what you are doing if you don’t want to mess up your patron data.

You can see what we did below:

And as you can see below, we have the fields displayed exactly in the order we had wanted in the first place.

Quick Tip – What to avoid when adding news items to your Koha OPAC

Avoiding common errors while using Koha – setting the library name correctly for news.

The News tool available in the Home -> Tools -> Additional tools section of the Koha staff client allows us to publish news and updates across – (a) OPACs (b) staff client and (c) printed slips generated by the library.

Being a handy way to publish library related updates, librarians like to use it. However, there are a few things that we need to keep in mind when doing this. For starters, we should not be logged in into the staff client as Koha’s database administrator; we must be logged in either as a superlibrarian OR as a library staff user with access rights to the News tool. The next thing to understand is even more important – the “Display location” and “Library” drop-downs.

Earlier today, we received a ticket on our online helpdesk –

“We have written a new notice but nothing is displayed on the opac.”

When we cross-checked, we found that to be true. So we checked the new notice to find out what may be the problem. And sure enough it was exactly what we had expected had happened. The notice / news was set to display on the OPAC of client-partner’s defined library branch, instead of being set to “All libraries”.

Now you may ask how can this be wrong??? Well, up until an user logs in via the OPAC, the “library (branch)” is *NOT* set as Koha has no way of knowing which branch (since a Koha instance can cater to multiple library branches of an organisation) of the library the user is looking at at the moment. The notices / news would have showed up *if* the user had logged in into *that* defined branch in this case.

So, the logic to keep in mind while publishing a news item on the OPAC – select a specific branch for display *ONLY* if you want the notice to be branch specific, otherwise, keep it generic as “All libraries”.

Once this was corrected, the news defined showed up just fine as can be seen below.

Displaying unique title and volume count on the Koha staff client

An upgrade-friendly way to show unique title and copies’ count using Koha’s SQL reports in tandem with Koha’s Reports web service API, JSON formatted data and some Javascript.

Kia ora!

Last week my good friend Dr. Gautam Sarma, Asst Librarian, K.K. Handique State Open University in Assam posed an question on the Koha Users forum on Facebook. He wanted to know what was the easiest way to view the total count of unique titles as well as total number of copies (item holdings) in his Koha catalog.

Given that he comes to us from a SOUL user’s background, this is a reasonable demand. SOUL developed and marketed by Inflibnet does show this information at a glance, especially when using the cataloging module. Similarly, so does e-Granthalaya, another LMS developed by NIC, Govt of India. The following screenshot snippets show how this information is displayed for SOUL v2.0 and e-Granthalaya 4.0 respectively.

soul

egrant

In Gautam’s own words he was looking for something that would allow him to:

hurriedly say that how much data we have by entering our ILS

While Koha’s SQL reporting facility makes finding this information a breeze, thanks to this ready-made SQL report by Nicole C. Baratta, it does not immediately allow the users to have the sort of heads-up view that SOUL and e-Granthalaya allows. A library staffer have to go to Reports -> Guided Reports -> Use Saved and then finally manually select the actual report and run it to see the unique title and copy count statistics.

At L2C2 Technologies we have been doing some things like these for our client-partners. So I promised Gautam that I will soon post about it. And as it would happen, yesterday Mr. Kalipada Jana, Librarian, Basanti Devi College and a client partner wanted the same facility for his hosted instance’s staff client. We decided to use the Basanti Devi College library as a live use-case (since they are currently doing retro-conversion) and document the same so that other users may also be able to benefit from the same.

Concepts – or what makes it all work

Koha since long have provided a set of HTTP based web service API. According to the World Wide Web Consortium (W3C) – the global standards body that defines anything and everything related to the web, a web service is “a software system designed to support inter-operable machine-to-machine interaction over a network”.

Among the various things Koha’s web services API allow includes the Reports web service, which allows an SQL report’s data to be made available in JSON format, as long as the report is marked as “public“. A public report is accessible via a URL that looks like this: http://MYOPAC/cgi-bin/koha/svc/report?id=REPORTID.

basanti-stats1

In this case, we called the report as a public report using the reports web service via https://bdcl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=6&annotated=1.

The SQL query being executed by the call was :

SELECT homebranch, count(DISTINCT biblionumber) AS bibs, 
       count(itemnumber) AS items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch ASC

The JSON data (which thanks to the annotated=1 querystring parameter) was returned as the following key-value pairs : [{"bibs":"1374","homebranch":"BDCL","items":"2359"}].

The solution

With the report (in this case report id #6) returning the branch-wise unique title and copies information in JSON format, it was now only a small matter of putting together some JavaScript so that the information could be displayed in a more human-friendly format on the staff client. The following JQuery snippet was all that was required to be placed into BDCL’s IntranetUserJS system preference to get our desired result.

$(document).ready(function() {
    if ( $('#main_intranet-main').length ) {
    $.getJSON("https://bdcl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=6&annotated=1", function(data) {
        var branches = data[0].homebranch;
        var bibs = data[0].bibs;
        var items = data[0].items;
        $('div.newsitem').prepend('<div class="newsitem" id="mystats"><table class="table table-striped" style="width: 100%; background: none;"><thead><th colspan="3" style="text-align: center; font-weight: bold; padding: 8px; line-height: 1.42857143; vertical-align: middle; text-transform: uppercase;">Library Statistics</thead><tbody><tr><td><strong>Branch</strong></td><td><strong>Unique titles</strong></td><td><strong>Total Copies</strong></td></tr><tr><td class="text-center">'+branches+'</td><td class="text-center">'+bibs+'</td><td class="text-center">'+items+'</td></tr></tbody></table></div>');
    });
    }
});

And voila! Houston! We have touchdown! 🙂

koha-count

Hope this post helps you at your library. Happy hacking!

Koha and the “magic” of XSLT – Part 2 : Show accession no. in OPAC results page

How to add 952$p (typically the accession no.) to your OPAC’s Results page display.

About 6 months back, we had posted about “Koha and the “magic” of XSLT : displaying new MARC fields on the OPAC“. This post can be thought of as its Part 2 as it introduces a couple of new concepts – (a) looping through a list of repeatable values and (b) punctuating these values for correct display. If XSLT or Koha with XSLT sound like something you are hearing for the first time, we strongly suggest that you first read the Part 1 first (see above).

The Backstory

Our tutorial style blog posts are usually the result of addressing some sort of user demand. In this case, this post came about because of Mr. Kalipada Jana, Librarian at Basanti Devi College, Kolkata. Yesterday he had filed a ticket on our helpdesk saying that he would like accession number(s) attached to each bibliographic record to be displayed on the OPAC results page. This is something that Koha does not do by default. But having seen such a display elsewhere he wanted to have the same.

The default Results page

chrome_2017-11-28_22-58-45

What the user wanted

chrome_2017-11-28_22-57-56

The Process

Koha stores it holdings item identification in MARC21 tag 952$p. The user here was using this field to store the individually unique accession numbers of their items in holdings. Now a bibliographic record may quite easily have multiple copies with separate accession numbers. So the XSLT snipped we needed must do the following:

  1. Handle looping over to display repeated 952$p (when there were multiple copies of the same book.
  2. Separate the accn nos. with “commas” if there where multiple copies of a book and after the final accession number terminate the line with a period instead of a comma. And if there was only a single copy, then instead of comma use a period.
  3. .

  4. Suppress this accession no. display for bibliographic records that do not have any holdings.

The code

<!-- L2C2 - 2017-11-28 adding accn no to results page 952$p -->
<xsl:if test="marc:datafield[@tag=952]/marc:subfield[@code='p']">
<span class="results_summary accn_no">
<span class="label">Accession number(s): </span>
    <xsl:for-each select="marc:datafield[@tag=952]">
        <xsl:value-of select="marc:subfield[@code='p']"/>
        <xsl:choose>
            <xsl:when test="position()=last()">
                <xsl:text>.</xsl:text>
            </xsl:when>
            <xsl:otherwise>
                <xsl:text>, </xsl:text>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:for-each>
</span>
</xsl:if>

In the first line (not the comment) we check if the MARC record has a 952$p field and sub-tag. If it does only then the rest of the code is executed. This is what helps us to suppress this new display for records without any accession numbers in its 952$p. The next couple of lines push out the necessary HTML code since the record has at least one accession number i.e. 952$p. In the subsequent two lines immediately after, we set up two loops. The first one loops through all the 952 (holdings) field in the bib record, while the inner loop looks up the $p sub-field. The inner most bit i.e. <xsl:choose><xsl:when test="position()=last()"> handled the punctuation. First if it checks if the currect MARCXML 952$p sub-node is the last one, if so it places a period and terminates the line. Otherwise, it places a comma as punctuation between the multiple accession numbers.

This code was added into the custom file which we named as MARC21slim2OPACResults-bdcl.xsl. This file is same as the default MARC21slim2OPACResults.xsl file, the only addition being the snippet given above. In order to get Koha to start using our file instead of the default, we placed the full path of our new file into the OPAC system preference – OPACXSLTResultsDisplay.

See it in action

If you want to see this XSLT in action, click here.