Posts Tagged ‘staff client’

A quick Firebug tip for editing CSS

Friday, September 19th, 2014

I’ve talked before about Firebug’s inspector tool for examining the markup of a page and its associated style. When you inspect a particular element in the markup you can see the CSS rules that have been applied to it. But what if the styles applied to an element change based on user interaction? How do you “catch” the style of a hover interaction, or of an element which has focus?

For instance: In the Koha staff client there is a global style for the color of a link when you hover your cursor over it.

Koha link color on hover

This color is defined in the staff client’s main CSS file, staff-global.css:

a:hover, a:active {
    color : #538200;
    text-decoration: none;
}

However, if you use Firebug’s inspector to examine a link you won’t see that CSS:

Inspecting a link in the staff client

That’s because the Firebug inspector isn’t showing you the “hover” state of the link–at least not unless you’re mouse cursor is hovering over the link at the time:

Inspecting the link on hover

But what if we want to use Firebug’s Style tab to live-edit the CSS of the hover state? As soon as we move our mouse away from the link the hover state disappears from the style pane, so we can’t edit it. Luckily Firebug has an option built in for “activating” three different interaction states: Hover, Active, and Focus:

Firebug Style tab interaction options

By selecting “:hover” we can “pin” the hover state to keep it activated so that we can inspect and change it:

Edit the hover style in Firebug

We can test the “focus” option by inspecting the style of any standard text input in Koha, for example the barcode field on the check-in page:

Inspecting an element's :focus state in Firebug

This lets us see that there is a CSS rule defining a default border style for <input> and <textarea>

input:focus, textarea:focus {
    border-color: #538200;
    border-radius: 4px;
    border-style: solid;
}

And from there we can customize away:

input:focus {
    border-top: 5px solid #EF5959;
    border-bottom: 5px solid #FF975F;
    background-image: -webkit-gradient(linear, 0 0, 0 100%, from(#EF5959), to(#FF975F));
    background-image: -webkit-linear-gradient(#EF5959, #FF975F);
    background-image: -moz-linear-gradient(#EF5959, #FF975F),
-moz-linear-gradient(#EF5959, #FF975F);
    background-image: -o-linear-gradient(#EF5959, #FF975F),
-o-linear-gradient(#EF5959, #FF975F);
    background-image: linear-gradient(#EF5959, #FF975F),
linear-gradient(#EF5959, #FF975F);
-moz-background-size:5px 100%;
    background-size:5px 100%;
    background-position:0 0, 100% 0;
    background-repeat:no-repeat;
    border-width: 5px 0;
    padding: 5px;
}

A custom input border

Showing item type counts on the checkout screen

Friday, July 30th, 2010

Does your library limit your patrons to a certain number of checkouts for certain item types? At the Athens County Public Libraries, for instance, we limit patrons to 10 audio books, 10 music CDs, 10 videos, and 5 DVDs at a time. If a patron tries to check out more than 5 DVDs, Koha will show a warning. But what if you want to be able to tell at a glance how many a patron has?

This functionality was available to us in our 2.x installation, but when we upgraded to 3.0 our support company at the time told us it wasn’t a customization they would support. This wasn’t a feature we were willing to give up, so I set out to duplicate it using the tools available to me: system preferences and JavaScript.

Information there for the taking

All the information we need can be found on the patron checkout screen, we just need to figure out how to get it. The page lists all the items checked out to the patron, and it shows the item type for each:

List of items checked out on the checkout screen

With this available to us we can use jQuery to count each instance of each item type. We need to build a count for each item type in our system, so the script isn’t very portable. It looks for table cells (“<td>“) containing the description of each of our item types:


var itypes = {'circ': 0, 'avid': 0, 'avbk': 0, 'avmu': 0, 'advd':0 };
$("#issuest td:contains('Circulating')").each(function(){
itypes["circ"]++;
});
$("#issuest td:contains('Videos')").each(function(){
itypes["avid"]++;
});
$("#issuest td:contains('DVDs')").each(function(){
itypes["advd"]++;
});
$("#issuest td:contains('Audio Books')").each(function(){
itypes["avbk"]++;
});
$("#issuest td:contains('Music CDs')").each(function(){
itypes["avmu"]++;
});

The script starts by setting up an array of all my item types (“circ,” “avid,” etc.) and giving each a value of zero. Then the script looks for instances of each item type description on the page,  “Circulating,” “Videos,” etc., using jQuery’s :contains selector. Each time it finds an instance of one of those text strings the script increments the count for that item type. At the end of the process the script will have the count for each item type.

Displaying the counts on the page

In order to show the item type counts on the page we need to lay some groundwork by adding some markup. I want to add the count information right after the “Checking out to…” heading, so I’ll find that element’s ID using FireBug and jQuery’s after() function:


$("#circ_circulation_issue label[for='barcode']").after( ... );

The HTML I’m going to add is the default state, so it shows a zero count for everything:


<p style="margin-top:1em;">
<span id="avmuout">0</span> Music CDs out, <span id="avmuok">10</span> More Allowed
</p>
<p>
<span id="avbkout">0</span> Audio Books out, <span id="avbkok">10</span> More Allowed
</p>
<p>
<span id="avidout">0</span> Videos out, <span id="avidok">10</span> More Allowed
</p>
<p style="margin-bottom:1em;">
<span id="advdout">0</span> DVDs out, <span id="advdok">5</span> More Allowed
</p>

I’ve  included unique IDs for the “count” spans so that I can easily update them with my script:


$("#avidout").html(String(itypes["avid"]));
$("#advdout").html(String(itypes["advd"]));
$("#avbkout").html(String(itypes["avbk"]));
$("#avmuout").html(String(itypes["avmu"]));

$("#avidok").html( 10 - itypes["avid"] );
$("#advdok").html( 5 - itypes["advd"] );
$("#avbkok").html( 10 - itypes["avbk"] );
$("#avmuok").html( 10 - itypes["avmu"] );

In the first of the two sections above I take the count I got earlier, itypes['avid'] and set the content of the corresponding <span> using the html() function. I also want to show how many more the patron can check out, so I subtract the count from the limits I’ve set in my Koha installation.

If you have patrons who have exceeded their checkout limit you’ll see a problem: The page will tell you they’re allowed to check out a negative number more items. We can correct the script to accommodate:


$("#avidok").html((10-itypes["avid"] > 0) ? 10-itypes["avid"] : 0);
$("#advdok").html((5-itypes["advd"] > 0) ? 5-itypes["advd"] : 0);
$("#avbkok").html((10-itypes["avbk"] > 0) ? 10-itypes["avbk"] : 0);
$("#avmuok").html((10-itypes["avmu"] > 0) ? 10-itypes["avmu"] : 0);

Final version

Here’s what the results look like:

The final version includes proper escaping of the HTML content and wraps the whole process into a function (“itemTypeCount”). This function will be called on page load only if jQuery finds that the table of checkouts, which has an ID “issuest” is being displayed. The whole script goes into Koha’s intranetuserjs system preference.


function itemTypeCount(){
$("#circ_circulation_issue label[for='barcode']").after("<p style=\"margin-top:1em;\" class=\"icount\"><span id=\"avmuout\">0</span> Music CDs out, <span id=\"avmuok\">10</span> More Allowed</p> <p class=\"icount\"><span id=\"avbkout\">0</span> Audio Books out, <span id=\"avbkok\">10</span> More Allowed</p> <p class=\"icount\"><span id=\"avidout\">0</span> Videos out, <span id=\"avidok\">10</span> More Allowed</p> <p style=\"margin-bottom:1em;\" class=\"icount\"><span id=\"advdout\">0</span> DVDs out, <span id=\"advdok\">5</span> More Allowed</p>");

var itypes = {'circ': 0, 'avid': 0, 'avbk': 0, 'avmu': 0, 'advd':0 };
$("#issuest td:contains('Circulating')").each(function(){
itypes["circ"]++;
});
$("#issuest td:contains('Videos')").each(function(){
itypes["avid"]++;
});
$("#issuest td:contains('DVD')").each(function(){
itypes["advd"]++;
});
$("#issuest td:contains('Audio Books')").each(function(){
itypes["avbk"]++;
});
$("#issuest td:contains('Music CDs')").each(function(){
itypes["avmu"]++;
});
$("#avidout").html(String(itypes["avid"]));
$("#advdout").html(String(itypes["advd"]));
$("#avbkout").html(String(itypes["avbk"]));
$("#avmuout").html(String(itypes["avmu"]));
$("#avidok").html((10-itypes["avid"] > 0) ? 10-itypes["avid"] : 0);
$("#advdok").html((5-itypes["advd"] > 0) ? 5-itypes["advd"] : 0);
$("#avbkok").html((10-itypes["avbk"] > 0) ? 10-itypes["avbk"] : 0);
$("#avmuok").html((10-itypes["avmu"] > 0) ? 10-itypes["avmu"] : 0);
}
$(document).ready(function(){
if(document.getElementById("issuest")){
itemTypeCount();
}
});

Caveats

This system works very well for my library, but it comes with a few caveats:

It requires that you hard-code, in the script, handling for each of your Koha item types.

Besides being tedious, it also requires that you modify the script each time you change your item types.

It requires that you hard-code the correct item type limits.

Also tedious, and requires that you modify the script each time you change your circulation rules.

It creates a potential collision with both call numbers and titles.

If my item type description is “DVD” and my call number includes the text “DVD” as well I’ll get an inaccurate count. If my item type description is “Audio Books” and a patron has checked out a print book entitled Audio Books for long trips I’ll get an inaccurate count.

For us the disadvantages are not unwieldy and the collision problem has never caused a problem. The advantage we get is being able to tell at a glance whether the patron is going to be able to check out that stack of DVDs or whether we need to ask them to put some back. Better to ask them to pick their favorites up front rather than after we’ve already checked out some of them.

Customizing the staff client login logo: Addendum

Thursday, July 22nd, 2010

I left something out of my post on Customizing the staff client login logo that I wanted to be sure to add: Once you’ve changed the logo you may want to change where it links to as well. By default it links to the Koha web site (or the deprecated version if your installation is an older one). We can use a little snippet of jQuery to change that link.

Using jQuery to change the URL to which the staff client logo links

$("#login h1 a").attr("href","http://www.myacpl.org");

That looks for an <a> tag inside an <h1> inside <div id="login">, which is specific enough to only catch the login form. This snippet goes inside your intranetuserjs system preference. Assuming the only thing you’ve added to intranetuserjs is the code I covered in the previous post, this would be the revised version:

$(document).ready(function(){
$("#login h1 a").attr("href","http://www.myacpl.org");
});
// ]]>

<!--
/* Custom styles here */
#login h1 a {
height:71px;
}
#login h1 {
background:url("http://www.myacpl.org/sites/all/themes/npl/logo.png") no-repeat scroll center top transparent;
}
-->
<script type="text/javascript">// <![CDATA[
// <![CDATA[

The revised version adds our new JavaScript snippet inside jQuery’s standard “$(document).ready()” to be triggered upon page load.
]]>

Customizing the staff client login logo

Wednesday, July 21st, 2010

A Koha user (from Tanzania!) asked today on the Koha mailing list, “I need some help on how to customize the staff client login page/replace koha logo with a new one.” I haven’t heard this request before, so it’s a good opportunity to investigate.

If this were the OPAC we could use a system preference, opacsmallimage, to change the logo. We don’t have that option in the staff client, so we’ll have to see if we can use the available customization options to accomplish the same thing.

Our first stop, as usual, is FireBug. I opened up the staff client to examine how the default Koha logo is displayed on the login form. Using the Inspect tool I highlight the logo and view the results in the FireBug HTML inspector window:

FireBug details for the login logo anchor tag

The logo is constructed by nesting a link (“anchor”) tag (<a>) inside a top-level heading tag (<h1>). The above screenshot shows the HTML and style details for the <a>, here are the results for the <h1>:

FireBug details for the login logo heading tag

From this we can collect all the CSS information we need to control the display of the login logo:

#login h1 a {
border-bottom:medium none;
display:block;
height:74px;
text-indent:-1000px;
}

#login h1 {
background:url("../../img/koha-logo.gif") no-repeat scroll center top transparent;
margin-bottom:0.5em;
margin-top:0;
}

The staff client logo is displayed using an image-replacement technique similar to the one used to display the Koha logo in the OPAC. The style of the anchor tag sets an explicit height which matches the logo image, and it sets a negative text-indent property which moves the text contents of the tag off the screen. These two properties create a blank space inside the anchor tag in which to display the logo.

The style of the heading tag does the work of displaying the logo. The background property points to the URL of the logo image. In the default case this is an image file on the Koha server’s file system. The URL doesn’t have to be a relative one, though. It can be a full URL pointing to anywhere on the internet.

Customizing the logo CSS

In order to change the image we need to make very simple changes to two CSS properties:

  1. The background-image of the <h1> tag.
  2. The height of the <a> tag.

Let’s use as an example the logo on the Athens County Public Libraries site. I can right-click the image in Firefox and choose “View image” to find the details I need. The location bar shows the URL I need, and the title bar shows the dimensions of the image. I just need to note the height.

Since we only need to change two properties, our custom CSS is simpler than the original. We only need to specify the properties which we need to override:

#login h1 a {
height:71px;
}

#login h1 {
background:url("http://www.myacpl.org/sites/all/themes/npl/logo.png") no-repeat scroll center top transparent;
}

Applying the custom CSS

We’ve got our custom CSS, now what do we do with it? We don’t have an opacsmallimage preference for the staff client, and we don’t even have an option like OPACUserCSS. We do have a couple of options which will work.

intranetcolorstylesheet

If you have access to your Koha server’s file system you can add a stylesheet there and tell Koha to apply it in addition to the default staff client stylesheet. For example, you could create a new CSS file called custom.css and save it alongside staff-global.css and the other staff client CSS files. Then you can specify that filename, custom.css, in the intranetcolorstylesheet system preference.

intranetuserjs

If you don’t have access to your Koha server’s file system, there’s a way to insert custom CSS into your staff client pages, but it’s a bit of a hack. Lacking an “intranetusercss” preference we’re going to hijack an existing preference, intranetuserjs, to do the job for us.

When you insert custom JavaScript into the intranetuserjs preference, Koha detects this and adds it to the header of each staff client page, wrapped in the appropriate <script> tags:

<script type="text/javascript">
// <![CDATA[
/* BEGIN INTRANETUSERJS CONTENTS */
$(document).ready(function(){
alert("Hello world!");     });
/* END INTRANETUSERJS CONTENTS */
// ]]>
</script>

Since Koha automatically wraps the intranetuserjs content in opening and closing <script> tags, we’ll have to do a little bit of roundabout coding to embed a <style> block:

<script type="text/javascript">
//<![CDATA[
/* BEGIN INTRANETUSERJS CONTENTS */
// ]]>
</script>
<style type="text/css">
/* Custom styles here */
</style>
<script type="text/javascript">
// <![CDATA[
/* END INTRANETUSERJS CONTENTS */
// ]]>
</script>

Let’s run down what’s happening here. Lines 1 and 2 have been inserted automatically by Koha in order to properly embed the custom JavaScript it expects from the intranetuserjs preference. Line 3 is the first line of the contents of intranetuserjs. Lines 4 and 5 we have added manually to close the automatically-embedded <script> tag. With the script tag closed we can go on to add just about any HTML we want, really, but this is all inside the <head> of the HTML document, so few things would be valid.

Lines 6, 7, and 8 show the <style> tag we’re adding. This is where we’re going to put our custom CSS. Lines 9, 10, and 11 are the last lines of our custom intranetuserjs. Knowing that Koha wants to close the <script> tag it opened on line 1, we have to re-open a new <script> tag so that when Koha closes it we’ll have valid markup.

Putting it all together, here’s what the contents of our intranetuserjs preference will be:

// ]]>
</script>

<style type="text/css">
/* Custom styles here */
#login h1 a {
height:71px;
}
#login h1 {
background:url("http://www.myacpl.org/sites/all/themes/npl/logo.png") no-repeat scroll center top transparent;
}
</style>
<script type="text/javascript">
// <![CDATA[

If all goes well, this is what you should see when you log out and look at your login screen:

Customized login form

Note: I’ve published a quick addendum to this post which covers using jQuery to change the URL to which the logo links

Quick patron add?

Monday, August 17th, 2009

Someone posted an enhancement request today requesting a change to the way the patron entry form is arranged in the Koha staff client. By default the patron entry form is pretty long, and the request asks that required fields be grouped at the top.

It seems like a reasonable request, but there’s a big disadvantage to trying to solve this problem with a template-only change: Libraries can customize which fields are mandatory when entering patrons. The system preference for that is  BorrowerMandatoryField (“borrower” being the term used in place of “patron” at the time of the preference’s creation).

What alternatives might there be to a template-only solution?

  1. Alter the script which generates the patron entry form so that any required fields (no matter which ones they are) are displayed at the top.
  2. Create a “quick-add” form which shows only required fields.
  3. Add a filter to the full patron entry form to toggle display of non-mandatory fields.

I like option two as a long-term solution, but option three could be implemented now with some custom JavaScript. Here’s a proof-of-concept script to show that using the markup which exists in the form now we can pull only required fields:


$(document).ready(function(){
var list = "<fieldset class=\"rows\"><legend>Quick Add<ol>";
$("label.required").each(function(){
item = $(this).parent().html()
item = "<li>"+item+"</li>";
list += item;
});
list += "</ol></fieldset><fieldset class=\"action\"><input type=\"submit\" value=\"Save\" onclick=\"return check_form_borrowers();\" name=\"save\"/><a href=\"/cgi-bin/koha/members/member.pl\" class=\"cancel\">Cancel</a></fieldset>";
$("#entryform").prepend(list);
});

Paste that into your intranetuserjs system preference or into Firebug’s command line to give it a try. Note that this solution assumes two things:

  1. You’ve already specified a patron category (by choosing one from the “new” menu).
  2. You’re adding patrons to the default branch for your logged-in user.

Of course the form could be customized to include these choices, but they’re probably safe assumptions for quick adds. A better implementation of this would probably allow you to toggle between either view: quick or standard.

Add a Koha login form to your web site

Friday, July 17th, 2009

opac-login-on-library-site

This question came up on the Koha mailing list today: How can I add a Koha login form to my library’s web site? Luckily it’s really simple. The image here is of the one we added one to our site.

Everything you need to know about how to do it is right on your Koha OPAC home page. Just view the source for the login box and copy it! Here’s the simple version, adapted right from the source:


<form method="post" action="http://path.to.your.opac/cgi-bin/koha/opac-user.pl">
<input type="hidden" value="opac" name="koha_login_context"/>
<label for="userid">Login:</label><input type="text" name="userid" id="userid"/>
<label for="password">Password:</label><input type="password" name="password" id="password"/>
<input type="submit" value="Log In"/>
</form>

The only thing you’d need to change to make that snippet work for you is the path.to.your.opac part. Replace that with the full URL of your Koha OPAC, e.g. http://acpl.kohalibrary.com.

You could even do the same for the staff client, for instance if you wanted your users to be able to log into the Koha staff client from your library’s intranet:


<form method="post" action="http://path.to.your.staff.client">
<input type="hidden" value="intranet" name="koha_login_context"/>
<label for="userid">Username:</label><input type="text" id="userid" name="userid"/>
<label for="password">Password:</label><input type="password" id="password" name="password"/>
<input type="submit" value="Log in" />
</form>

Of course I wouldn’t recommend putting a login form for your staff client anywhere in public view. Note that using that form will log you in to your default library. If you wanted to give your users the option of logging into a different library you’d have to reproduce the full <select> tag from your staff client’s login page.  View the source to find it.

Interface Test: Editing from the additem table

Thursday, July 16th, 2009

One of the custom reports I’ve been using recently is one that shows me the most expensive items in the catalog. I’m not trying to figure out which book to sneak out under my coat, I’m trying to find data-entry errors which might cause problems for the patrons.

You lost that paperback? That’ll be $4399.00.

The trouble is, once I’m cruising through this report making corrections I’m quickly frustrated by the interface for adding items.additem-interface

The list of items is in its own container with the css property “overflow:auto” so that if it exceeds the constraints of the container it’s in it will display scrollbars. It always displays scrollbars. You have to scroll to the right to see the item’s replacement price, then back to the left to find the Edit link. And it’s hard to be sure you clicked the Edit link in the correct row, because once you’re at the Edit link you can’t see the price anymore!

What if we could access the Edit and Delete functions from anywhere in the row? Inspired by WordPress’s inline edit menu I worked up this example:

additem-inline-edit

Try navigating anywhere in the table of item details and clicking a table cell. You’ll see Edit/Delete links appear for that row. Implementing this change would allow the user to jump to the Edit or Delete functionality from anywhere in the table eliminating the need to scroll back and forth for the static links.

The advantage to the method used in this example is that it’s simple: no generating tooltips or modal dialogs, just a simple append of the Edit|Delete links to the table cell which gets clicked.


$("td").click(function(event){
var $tgt = $(event.target);
if($tgt.is("a")||$tgt.is(":first-child")||$tgt.is(":nth-child(2)")){ return true; } else {
var rowid = $(this).parent().attr("id");
num_rowid = rowid.replace("row","");
$(".linktools").remove();
$(this).append("<span class=\"linktools\"><a href=\"/path/to/additem/script.pl?op=edititem&amp;amp;biblionumber="+biblionumber+"&itemnumber="+num_rowid+"\">Edit</a> | <a href=\"/path/to/additem/script.pl?op=delitem&amp;amp;biblionumber="+biblionumber+"&itemnumber="+num_rowid+"\" onclick=\"confirm_deletion("+biblionumber+","+num_rowid+"); return false;\">Delete</a></span>");
}
});

The disadvantage to the resulting UI is that it could suggest to the user that clicking Edit or Delete will edit or delete just the contents of that cell, which is of course incorrect. It wouldn’t be good for a librarian to expect a click on “Delete” to delete, for instance, only the value contained in the replacementprice field and find that the entire item had been deleted.

I’m not sure how to mitigate that confusion while retaining the script’s simplicity.

Update: Here’s an alternate, slightly more verbose version.

Grabbing eyes with styled messages boxes

Wednesday, July 1st, 2009

I got a question yesterday on the Koha IRC channel about changing the background of message which appear onscreen during check-in operations. Aparently librarians were not noticing the messages, and the questioner wondered if those messages could be styled in a different way.

Koha prompts the user to return an item to its home library with a message box

Koha prompts the user to return an item to its home library with a message box

It’s possible to customize Koha’s staff client just like it’s possible to customize the OPAC. At the moment, though, the staff client lacks one useful option: a means of injecting custom inline CSS as we can with OpacUserCSS. The staff client does have intranetuserjs, and that gives us an opportunity to get around the shortcoming.

PLEASE NOTE: As the manual says, “IMPORTANT: This system preference is an advanced option. If you enter a value with incorrect syntax you can make it very difficult to access the staff interface.”

Here’s how intranetuserjs gets added to the markup. From the template:


<!-- TMPL_IF NAME="intranetuserjs" --><script type="text/javascript">
//<![CDATA[
<!-- TMPL_VAR NAME="intranetuserjs" -->
//]]>
</script><!-- /TMPL_IF -->

IF the intranetuserjs system preference has anything in it, the template will include it, wrapped in the appropriate <script> tags. Here’s how we’ll bend the rules to get our custom CSS in: Add this to the intranetuserjs pref:

//]]>
</script>
<style type="text/css">
/* Insert custom styles here */
</style>
<script type="text/javascript">
//<![CDATA[

When we add that to the intranetuserjs preference, Koha generates this markup in the template:

&lt;script type="text/javascript" &gt;
//&lt; ![CDATA[
//]]>
</script>
<style type="text/css">
/* Insert custom styles here */
</style>
<script type="text/javascript">
//<![CDATA[
//]]&gt;
&lt;/script&gt;

The first thing we do is close the <script> tag which was automatically opened by the template, which expected us to add JavaScript content. We add our custom <style> tag, then re-open the <script> tag. The template is going automatically add a closing <script> tag, so we’ve got to add an opening one or risk breaking things.

With that in place we can add our custom CSS. The message box gets its background from an image. The simplest way to customize its appearance is to remove the image and add a custom color:

div.message { background: #CCFF00 none; }
Remove the background image and add a custom color

Remove the background image and add a custom color

Or use your own background image:

div.message { background:white url(http://www.myacpl.org/koha/wp-content/uploads/2009/07/squidfingers-pattern_141.gif) scroll left 0;
}
Eye-catching but not very readable!

Eye-catching but not very readable!

If you’re going for really eye-catching, you’ll have to go for an animated background image!

Please return Understanding interpersonal communication / to RPL

If you find a background image you like, remember: copy the image to your own server. Don’t hot-link it from someone else’s. Nefarious webmasters are likely to swap out the image for something you don’t want to see!

Of course I don’t really recommend that you use an animated background. As you’re approaching this kind of change, please remember: changing the style of Koha’s default message box will change all instances of that style box. Koha re-uses that style for displaying informational messages throughout the interface. The staff at the circulation desk may like it, but the cataloger doing MARC imports may not!

Getting started with statistics

Tuesday, March 17th, 2009

Koha 3 has always had a selection of built-in reports, and Koha 3 adds additional reports and new a “Guided Reports” system (partially sponsored by this library). The Guided Reports system is still a little rough around the edges, but folks are doing some interesting stuff with them, in particular by using the system’s ability to run custom SQL queries via the Koha interface.

I’d like to take some time to explore how we’re starting to leverage the data that Koha collects to build some reports about how the library is being used. I’ll start with circulation statistics.

The Statistics Table

The health of the library is (for better or worse) judged by its circulation statistics, so that’s first priority. In Koha, every checkout, check-in, and renewal is recorded in one table in the database called statistics. Here’s what it looks like:

Field Type Collation Null Key Default
datetime datetime Yes MUL NULL
branch varchar(10) utf8_general_ci Yes NULL
proccode varchar(4) utf8_general_ci Yes NULL
value double(16,4) Yes NULL
type varchar(16) utf8_general_ci Yes NULL
other mediumtext utf8_general_ci Yes NULL
usercode varchar(10) utf8_general_ci Yes NULL
itemnumber int(11) Yes NULL
itemtype varchar(10) utf8_general_ci Yes NULL
borrowernumber int(11) Yes NULL
associatedborrower int(11) Yes NULL

Some of those columns aren’t even used–I’m not sure if they were in the past, or whether there were plans for them for the future. other, usercode, and associatedborrower don’t seem to be in use.

  • datetime records the time and date of the transaction.
  • branch is the location of the transaction.
  • proccode is related to tracking patron fines, payments etc.
  • value records a currency amount (for fines, payments, etc).
  • type records the type of transaction:  issue [checkout], return [check-in], renew, payment, or writeoff.
  • itemnumber is the id number (defined by items table) of the item that was handled in the transaction.
  • itemtype is a category assigned to the item as defined in Koha’s Item Type management.
  • borrowernumber is the id number (defined by the members table) of the patron involved in the transaction.

When you check something out in Koha, a line is added to this statistics table (SELECTing datetime,branch,type,itemnumber,itemtype, and borrowernumber):

datetime branch type itemnumber itemtype borrowernumber
2008-10-07 14:07:31 CPL issue 40235 CIRC 20351

To get a quick look at how much you’ve circulated today, you could run this:

select count(*) from statistics where year(datetime) = year(curdate()) AND month(datetime) = month(curdate()) AND DAY(datetime) = day(curdate()) and (type='issue' OR type='renew');

The SQL matches the year, month, and day of transactions against today’s date and limits the results to checkouts and renewals. Here’s our count for March 16, 2009:

count(*)
2190

1 row in set (36.76 sec)

Notice how long the query took. The statistics table gets really big: ours has data going back to May 2003, and it has 7,581,248 rows in it. That makes for some slow queries.

Getting a quick count is a great way to put a number to your day’s work. At the end of the month, though, you’ll want to get some good numbers to show your Board about what kind of business you did. Let’s break it down by collection code so we can see what kind of materials our patrons checked out. We’ll look at the Athens branch’s circulation last month:

SELECT items.ccode, COUNT( statistics.itemnumber ) AS count
FROM items, statistics
WHERE statistics.branch = 'APL'
AND statistics.itemnumber = items.itemnumber
AND year( statistics.datetime ) =2009
AND month( statistics.datetime ) =02
AND (
statistics.type = 'issue'
OR statistics.type = 'renew'
)
GROUP BY items.ccode

We get a nice breakdown of how each category (or at least a selection, in this example) fared during the month:

ccode count
AB 427
AF 1355
AV 1002
CDM 681
DVD 1006
EASY 1754
JF 519
JNF 1028
LP 667
MYS 770
NF 1994

You should be able to try out these examples yourself using the Guided Reports system in Koha (or right in MySQL if you have direct access to your database).


Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported.