Monthly Archives: March 2009

Getting started with statistics

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

ACPL’s “New and Upcoming” OPAC List

upcoming-titles-screenshot1

The Athens County Public Libraries’ OPAC has a display of selections from our “New and Upcoming” book list. Although it is displayed within a Koha page, the content is brought in from an outside Koha. These are the pieces that put it all together:

  • A list of new titles, maintained in a separate MySQL database via its own web interface
  • A php script to pull out and display a random selection of titles from the list
  • Markup for an iframe added to Koha’s opacmainuserblock system preference

Set up the Database

ACPL originally built the infrastructure for maintaining book lists as part of the library’s public web site. The intention was to enable librarians to build any kind of book list. There are two tables, bookshelf and bookshelfitems:

bookshelfitems
Field Type Null Key Default Extra
shelfitemid int(11) PRI NULL auto_increment
shelfid int(11) YES NULL
adddate date YES NULL
title varchar(255) YES NULL
authorfirst varchar(80) YES NULL
authorlast varchar(80) YES
biblionumber int(11) 0
isbn varchar(20)
sortorder tinyint(4) YES NULL

The bookshelfitems table contains the individual items that appear on a list. The sortorder field is not required. ACPL uses it to define a custom sort order for other book lists created for our site.

bookshelf
Field Type Null Key Default Extra
shelfid int(11) PRI NULL auto_increment
shelftitle varchar(255) YES NULL
description text YES NULL
bannerimage varchar(150) YES NULL
dateadded date YES NULL
displayfrom date YES NULL
displayto date 0000-00-00
category tinyint(4) YES NULL

The bookshelf table contains the names and details of each individual list. This is not strictly necessary if you’re only maintaining one list. The description, bannerimage, category and date-related columns are not required.

In the case of the new book list, a list with shelftitle “New and Upcoming” was created. Then individual items were added to bookshelfitems which specified the shelfid of the “New and Upcoming” list.

Retrieve the Data

The next step is to pull the relevant data from the list for display. Create a new PHP file which does a straightforward query of the database:


Of course your WHERE clause should specify the id of your new book shelf

<table><tr>

<td>
&lt;a target=&quot;_parent&quot;
href=&quot;http://path/to/koha/opac-detail.pl?biblionumber=

"&gt;&lt;img src=&quot;http://images.amazon.com/images/P/.01._TZZZZZZZ_PU_PU-5_.jpg" alt="" /&gt;</a>
&lt;a target=&quot;_parent&quot;
href=&quot;http://path/to/koha/opac-detail.pl?biblionumber=
"&gt;
</a>


By &lt;a target=&quot;_parent&quot; href=&quot;http://path/to/koha/opac-search.pl?q=au:
,%20"&gt;</a>.

</td>

</tr></table>

And of course you should change the URLs to point to your OPAC. As you can see ACPL pulls book cover images from Amazon. That could be modified to point to a different source if your library chooses. When you access your new PHP file directly you should see your titles:

upcoming-titles-screenshot21

Add to your OPAC

Log in to the Administrative interface of Koha, go to System Preferences, and edit the OpacMainUserBlock preference. This is the essence of the markup that ACPL uses:

<h4>Selections From the New and Upcoming Titles List:</h4>

Possible Improvements

  • iframe height is fixed. The 21 em height of the iframe is a compromise, attempting to set a height that will fit all but the longest titles without creating too much white space. This doesn’t always work.
  • Dynamic awareness of overflow in smaller displays. If your browser window is 800 pixels wide or smaller, the third title will be cut off by the overflow:hidden CSS property. A great improvement would be to add Javascript that would check the width of the iframe on page load and remove the last cover completely so that no partial covers were displayed.
  • Gracefully handle missing covers. Not every title we list has a corresponding cover image on Amazon.com. There are many techniques available for handling this. The same javascript method Koha uses (adapted from the link article) could probably be applied here.

Adding a custom header to Koha 3’s OPAC

In the last post I covered customizing the Koha logo in the OPAC. Now let’s look at another way to change the look of your OPAC, the opacheader system preference.

The opacheader system preference lets you embed custom markup within the structure of OPAC pages. It controls the area of the page above the persistent blue search bar and below the menu at the top that contains login information (either “Log in…” or “Logged in as…”):

<div id="members">
<ul><li><a href="/cgi-bin/koha/opac-user.pl">Log in to Your Account</a></li></ul>
</div>

<!-- opacheader markup will be embedded here -->

<div id="opac-main-search" class="yui-g">
<h1 id="libraryname"><a href="/cgi-bin/koha/opac-main.pl">Athens County Public Libraries</a></h1>
<div id="fluid">
<div id="fluid-offset">
<form name="searchform" method="get" action="/cgi-bin/koha/opac-search.pl" id="searchform">

This placement gives us all the freedom we need to add a library name, a larger logo, or even additional menus.  That’s what I’ve done with the Athens County Library System’s OPAC. A custom OPAC logo sits above a version of the persistent navigation menus that appear on every page of the Athens County Library System web site. Visitors to our OPAC see elements which are visually consistent with our web site and can keep oriented with the same navigation scheme.

koha3-opacheader-0

Adding Custom HTML

The simplest use of the opacheader preference would be to add a simple heading:

<h1>Springfield Free Public Library Catalog</h1>

koha3-opacheader-1

How about a menu like we have on ACPL’s OPAC? Let’s pick something from Listamatic, a terrifc source of inspriation when building menus with HTML and CSS. For this example we’ll try the Rollover horizontal list navbar. First we grab the HTML and save it in the opacheader system pref:

<div id="navcontainer">
<ul id="navlist">
<li id="active"><a href="#" id="current">Item one</a></li>
<li><a href="#">Item two</a></li>
<li><a href="#">Item three</a></li>
<li><a href="#">Item four</a></li>
<li><a href="#">Item five</a></li>
</ul>
</div>

Next grab the CSS and add that to the OPACUserCSS system pref:

#navcontainer ul {
padding-left: 0;
margin-left: 0;
background-color: #036;
color: White;
float: left;
width: 100%;
font-family: arial, helvetica, sans-serif;
}

#navcontainer ul li { display: inline; }

#navcontainer ul li a {
padding: 0.2em 1em;
background-color: #036;
color: White;
text-decoration: none;
float: left;
border-right: 1px solid #fff;
}

#navcontainer ul li a:hover {
background-color: #369;
color: #fff;
}

Now let’s check our work:

koha3-opacheader-3

Close, but not quite. The new menu is overlapping the search menu. Floats in the menu CSS are causing the menu not to clear. Luckily there is a float-clearing class in the default OPAC CSS we can apply here: we’ll add class="clearfix" to the menu container’s markup:

<div id="navcontainer" class="clearfix">
<ul id="navlist">
<li id="active"><a href="#" id="current">Item one</a></li>
<li><a href="#">Item two</a></li>
<li><a href="#">Item three</a></li>
<li><a href="#">Item four</a></li>
<li><a href="#">Item five</a></li>
</ul>
</div>

koha3-opacheader-4

Of course that’s a fairly basic menu with minimal styling. Browse the other menus on the Listamatic site for more ideas. To duplicate the rounded-tab look on ACPL’s menus, check out the sliding-doors technique on A List Apart.