The 5-Minute Series : Downloading your Koha SQL Report

Presenting a fast and flexible option to download your report.

One of the best things about Koha is the complete freedom it provides its users to create a custom SQL report of any level or degree of complexity as long as the query is compliant to MySQL SQL syntax – reports like e.g. accession registers, detailed circulation statistics etc.

Of course, once you have your report, if you are anything like *us*, you would want to grab the data and the do further analysis or use it to make that fancy-schmancy report or use a part of it in a presentation. Of course to do any of that, the data has to be downloaded locally. Koha provides you with three (03) options:

  1. As comma separated value (.CSV) file
  2. As a tab separated (.txt) file
  3. As OpenDocument Spreadsheet (.ODS) file

Now, if you are an OpenOffice.org / LibreOffice user, you may be tempted to go for the third option i.e. download the data as an .ods file. While this may look like the best option. It is *not* always so. The biggest problem is that if your report returns a lot of records, then generating the .ODS file is going to take a bit of time. Also, if your system is low on RAM, this can get *really* slow.

On the other hand, the files in .CSV and the .TXT formats are generated and downloaded almost instantly OR at least faster in comparison by several order of magnitude! Given that, which one among the two are your best bet? Well, in our considered opinion, the tab-separated .TXT file is the best. Here is why: if the single quotes in your data are not properly escaped, then your CSV data may be misinterpreted due to the un-escaped single-quote. leading it to split the data that should have been together into separate columns during an import. In contrast, a “tab separated” file will usually work without any issue unlike the CSV file.

So, if you want the data download to be fast and easy to split into columns correctly, the tab-separated .TXT file provides us the best overall option.

The 5-Minute Series: Setting default messaging preferences for SMS alerts.

Presenting an use-case requiring setting the default messaging preference for SMS alerts sent by Koha.

The problem

Earlier in the day, my professional colleague and friend Joydeep Chanda asked a question. His Koha v3.22.10 installation (at Gurudas College Library) had turned on SMS alert service for its users. The library being a long time Koha user, presented a challenge. He had ~ 6000+ users, so now he and his staff had the unenviable task of having to update the smsalertnumber field in the borrowers table for these 6000+ users. I pointed him to a recent Facebook post by another friend Vimal Kumar Vazaphally. That solved part of his problem, but he still had to choose the default types of SMS messages to be delivered.

Messaging-preferences

So, I pointed him to another post by Vimal – “Use of borrowers-force-messaging-defaults script“. But after a while he complained that while the borrowers-force-messaging-defaults script did set the SMS options, it also enabled *all* of them, he wanted SMS send option to be set only for CHECK-IN and CHECK-OUT.

Description of borrowers-force-messaging-defaults as included in its source:

If the EnhancedMessagingPreferences syspref is enabled after borrowers have been created in the DB, those borrowers won’t have messaging transport preferences default values as defined for their borrower category. So you would have to modify each borrower one by one if you would like to send them ‘Hold Filled’ notice for example.

This script create transport preferences for all existing borrowers and set them to default values defined for the category they belong to.

The solution

The answer lies in correctly assigning advanced messaging preferences by default to a patron category *and then* running the borrowers-force-messaging-defaults script. In order for the patron category edit page display the “Default messaging preferences for this patron category” sub-form, the system preference EnhancedMessagingPreferences needs to be enabled.

The Steps

  1. Check if the EnhancedMessagingPreferences is enabled. If not, then enable it.
  2. Go to Home -> Administration -> Patron categories and select the category to edit.
  3. Scroll down the Modify category form to the “Default messaging preferences for this patron category” section.
  4. Select the checkboxes against the SMSes you want to be sent by default for that category.
  5. Save the changes
  6. Go to a command line terminal and run the borrowers-force-messaging-defaults script. This will ensure that only the defaults are set.

Enjoy!

See also:

[1] “Koha and key lessons of using transactional SMS in India” by L2C2 Technologies

[2] Press release about setting up of SMS alert service at Gurudas College Library.

[3] SMS-Send-IN-Unicel-0.01 at CPAN

Changing languages in the drop-down for Google Transliterate API

Choose the list of languages to transliterate on the Koha OPAC

This tends to be frequent question that we often end up fielding from colleagues and customers. But before we get into this, please remember that this feature is probably at the end of it shelf-life. There are two reasons for this – the Transliterate API that this feature depends on was deprecated many, many years back, and secondly how badly this API was put together – it fetches the necessary JavaScript library from Google’s servers over HTTP! So, if you are using HTTPS on your Koha OPAC, you can kiss this feature good bye right now! Being deprecated there is almost zero chance that Big G will do anything to change that.

Life sucks right?? 😉 But as long as it is still there, here’s “one for the road”! 😀 So, read on!

Turning on Google Transliteration

googleindic

It all starts with setting GoogleIndicTransliteration system preference to “Show” from the default of “Don’t show”.

By default that provides us with this short language drop-down:
googlist

For users from India, a country with 22 official language at the last count, that default list is woefully too little, too less. The good news is that Google’s Transliteration API supports several dozens of languages. Which ones? Well you need to dig around LanguageCode and SupportedDestinationLanguages enumerations in the documentation.

Show me the money!

The code to support the function is loaded via googleindictransliteration.js file that resides under /usr/share/koha/opac/htdocs/opac-tmpl/bootstrap/js/ on a Debian package based installation.

The screen grab below shows exactly that changes we needed to make in order to show on Hindi and Bengali as the list of languages, *and* with Bengali as default language option on the select drop-down.

googchanger

So how did I know that I needed to use “bn” for Bengali? Well, that LanguageCode enum list I’ve linked to above. Simple really!

One more fact about this tip that will bug a few

Change(s) to the LanguagesCode enum like this within the googleindictransliteration.js file would cause *all* instances on a multi-tenanted Koha installation to display the same list of languages. So, if you need different sets of languages lists in different OPACs, you are probably out of luck.

The 5-Minute Series: JavaScript or CSS – what is better at hiding the ‘No cover image available’ markup?

How to efficiently remove the ‘No cover image available’ placeholder using CSS rather than Jquery, when AmazonCoverImages or GoogleJackets can’t retrieve a cover image for your displayed title.

We really like our OPACs to display the cover images of the books and journals which we have often so painstakingly cataloged. Out of the box, Koha allows us to fetch and display book covers from several sources, both local and from data providers over the Internet. The commonest of these being (a) AmazonCoverImages and (b) GoogleJackets. While these two settings (either or both) will work for a large number of books, for users in India, there are also a lot that these two sources do not offer. Especially books in Indian languages or simply books printed without an ISBN (there are a lot of these in India).

When no image is found Koha displays the text “No cover image available” as a placeholder. A lot people would rather not see this. The Koha JQuery Library on the Koha Community wiki offers the following jQuery based approach:

$(document).ready(function(){
    $('.no-image').remove();
});

You simply place this snippet into the OPACUserJS system preference and hey Presto! these pesky “No cover image available” displays are history. Well, yes and no! Yes it works, and “no”, this may not prevent that text to be displayed, at least once. Why? well how about a slow PC and an even slower internet connection? Either one of the two or a combination of both will usually ensure that you get to take a look “No cover image available” before they are “removed” from the displayed page.

The simpler thing IMHO, is to take the Cascading Style Sheet (CSS) route, which as simple as placing the following one-liner into your OPACUserCSS system preference:

.no-image { display: none; }

no-image-removed

Not only will you *never* get to see the “No cover image available” markup anymore, no matter how slow your PC / Internet connection is, this is also more efficient, since rather than use jquery to first select and then .remove() a DOM element with a particular style class attribute, we simply re-define that they shouldn’t be displayed at all. The browser DOM does all the optimization in this approach.