Koha Librarians Workshop

Alex Buckley and Aleisha Amohia

27 July 2021

Agenda

• Introductions
• What's new in Koha 20.11?
• Customising your OPAC/public interface

• Making Koha data useful to your users
• Customising your staff client
• Resources for writing SQL reports

• Writing SQL reports

• What's new in Koha 21.05?
• Contributing to the Koha Community
• Q&A

Introductions

• Who you are
• How long you have been using Koha
• What you hope to get out of this workshop

Alex Buckley

• Koha Developer at Catalyst since 2015
• Based in Nelson
• Data migrations, new implementations, integrations, training

Aleisha Amohia

• Koha Developer at Catalyst since 2014
• Development, mentoring, communications

Test sites for today's training

https://train.koha.catalystdemo.net.nz/

https://train-admin.koha.catalystdemo.net.nz/

Login details:

Username: FirstnameLastname

Password: CatalystTrain1

What's new in Koha 20.11?

Read the blog post

• Group circulation by item type
• Specify a reason when cancelling a hold, send a notice
• Hide chosen fields from the OPAC suggestions form
• Custom destination for failed overdue notices
• Add ability to attach a cover image an item level
• Enhanced high holds
• Default lost item fee refund on return policy
• Holds history for patrons in OPAC
• Accessibility

Customise the OPAC

Tutorial

Customise the OPAC

In the News Tool:

These sections are translatable and you do not need to know HTML to modify them.

Still in the system preferences:

• OpacNav
• OpacNavBottom

OPAC system preferences

• OPACAdvSearchOptions - Hide options from the OPAC advanced search page
• OPACSuggestionsUnwantedFields - Hide fields from OPAC suggestion page
• hidelostitems - Hide lost items from search and detail pages
• HighlightOwnItemsOnOPAC & HighlightOwnItemsOnOPACWhich - Move items to the front and increase font size from patrons home library

OPAC system preferences

• OPACHighlightedWords - Choose if patrons search terms are highlighted in result and detail pages
• NotHighlightedWords - Stopwords that should never be highlighted
• OPACMySummaryHTML - Add a column in the tables on the 'My summary' and 'My checkout history' pages for logged in OPAC users
• OPACNoResultsFound - HTML to be displayed when no results are found for an OPAC search

OPAC system preferences

• OPACUserSummary - Show summary of checkouts, overdues, holds and charges on OPAC home page
• OPACReportProblem - Allow patrons submit problems in OPAC to KohaAdminEmailAddress
• OPACHiddenItems - Rules to hide items from OPAC
• OPACHiddenItemsExceptions - Patron categories that can see otherwise hidden items

Style your OPAC

• Additional stylesheets
• OPACUserCSS

/* Hide the no image found image */
.no-image { display: none; }

• OPACUserJS

$("#opacnav").append("<p>This is a test.</p>");

These system preferences require you to know:

CSS: https://www.w3schools.com/css/default.asp

Javscript: https://www.w3schools.com/js/default.asp

jQuery: https://www.w3schools.com/jquery/default.asp

Make your OPAC accessible

• Use headings well
• Use the News Tool to show custom content in your installed languages
• Avoid including images of text, just use text
• Images that add information must have alt text

Take a break

Making Koha data useful to your users

Read the blog post

• Use Koha reporting in a creative way
• Put Koha data on other webpages that your users are on
• Collect useful data about patrons using Koha extended attributes
• Exhibit your catalogue
• Social media

Book slider plugin

Customise the staff client

Using system preferences!

• Hide fields from forms - CollapseFieldsPatronAddForm
• Hide columns in tables - Admin -> Table settings

Or using the News Tool

Resources for writing SQL reports

Koha reports wiki

• https://wiki.koha-community.org/wiki/SQL_Reports_Library
• https://wiki.koha-community.org/wiki/SQL_Reports_Circulation
• https://wiki.koha-community.org/wiki/SQL_Reports_Patrons
• https://wiki.koha-community.org/wiki/SQL_Reports_Holds

Mana Knowledge Base

Watch the video tutorial

Read the blog post

Take a break

Resources for writing SQL reports

Koha Database Schema

• Shows database tables and columns
• Shows relationships between tables

• Every table (set of data) has columns (attributes)
• Each row in a table represents a record
• Sometimes tables are linked by a shared column

W3 schools

Tutorials point

Writing SQL reports

Important

Always run your report on your test site before trying it in production.

Do not query the action_logs table, use the Log Viewer Tool instead.

Do not refresh the page while running a report, and do not press Run more than once.

You can only view (not modify) data using Koha reports.

Useful tables

• borrowers, deletedborrowers - patron data
• items, deleteditems - holdings data
• biblio, deletedbiblio - bibliographic data
• auth_header - authority data
• reserves, old_reserves - reserves/holds data
• issues, old_issues - circulation data
• branchtransfers - transfer data
• accountlines - fines and fees data
• statistics

SELECT

SELECT column
FROM table

SELECT count(*) 
FROM table

SELECT column1, column2, column3
FROM table

SELECT

SELECT biblionumber
FROM biblio

SELECT count(*) 
FROM items

SELECT firstname, surname, email
FROM borrowers

WHERE

SELECT count(*) 
FROM table 
WHERE column = "value"

SELECT column1, column 2
FROM table
WHERE column IS NULL

SELECT column1
FROM table
WHERE column != "value"

WHERE

SELECT count(*) 
FROM statistics 
WHERE type = "issue"

SELECT count(*) 
FROM items
WHERE barcode IS NULL

SELECT count(*) 
FROM items
WHERE barcode IS NOT NULL

AS

SELECT column AS "Column name" FROM table

SELECT column AS ColumnName FROM table

SELECT firstname AS "First name" FROM borrowers

SELECT surname AS Surname FROM borrowers

LIKE + wildcards

SELECT column FROM table
WHERE column LIKE "%middle%";

SELECT column FROM table
WHERE column NOT LIKE "%value%";

SELECT column FROM table
WHERE column LIKE "%end";

SELECT column FROM table
WHERE column LIKE "start%";

LIKE + wildcards

SELECT surname AS Surname, 
firstname AS "First Name", 
cardnumber AS "Card Number", 
email AS Email
FROM borrowers
WHERE email NOT LIKE '%@%.%'

SELECT title FROM biblio
WHERE title LIKE "%dog%";

DISTINCT

SELECT DISTINCT(column) 
FROM table

DISTINCT

SELECT DISTINCT(itype) 
FROM items

WHERE + AND

SELECT column1, column2
FROM table
WHERE column = 'data'
AND column != 'data'
AND column >= 'number'

WHERE + AND

SELECT itemnumber, barcode 
FROM items 
WHERE homebranch = 'CPL'
AND ccode = 'FIC'

SELECT * FROM items
WHERE ccode = 'FIC'
AND dateaccessioned > '2021';

WHERE + OR

WHERE + OR

WHERE + NOT

SELECT column
FROM table
WHERE NOT column = "data"

WHERE + NOT

SELECT DISTINCT(firstname)
FROM borrowers
WHERE NOT firstname LIKE "A%"

RUNTIME PARAMETERS

SELECT column
FROM table
WHERE column = <<Label for free text input>>

SELECT column
FROM table
WHERE column = <<Label for dropdown|dropdown>>

Koha 20.05: Manual documentation

Koha 21.05: Manual documentation

RUNTIME PARAMETERS

SELECT biblionumber, title
FROM biblio 
WHERE frameworkcode=<<Please enter the framework code to filter on>>

SELECT itemnumber, barcode
FROM items
WHERE homebranch= <<Select the homebranch|branches>> 

ORDER BY

SELECT column
FROM table
WHERE column = "data"
ORDER BY column ASC

SELECT column
FROM table
WHERE column = "data"
ORDER BY column DESC

ORDER BY

SELECT surname, firstname 
FROM borrowers
WHERE branchcode = "CPL"
ORDER BY surname ASC

SELECT surname, firstname 
FROM borrowers
WHERE branchcode = "CPL"
ORDER BY firstname DESC

LIMIT

SELECT column
FROM table
LIMIT rows

LIMIT

SELECT * FROM biblio 
WHERE datecreated > '2020'
LIMIT 5;

SELECT * FROM biblio 
WHERE datecreated > '2020'
ORDER BY title ASC
LIMIT 5;

JOIN

SELECT a.column, b.column
FROM table a
JOIN table b USING (sameColumnName)
WHERE a.column = "value"

SELECT a.column, b.column
FROM table a
JOIN table b ON (a.column = b.column)
WHERE c.column = "value"

JOIN

SELECT b.biblionumber, b.title, i.barcode 
FROM biblio b
JOIN items i USING (biblionumber)
WHERE b.frameworkcode=<<Framework code>>

SELECT DISTINCT(b.title), i.itype, bi.itemtype 
FROM items i 
JOIN biblio b USING (biblionumber) 
JOIN biblioitems bi ON (i.itype=bi.itemtype)

GROUP BY

SELECT COUNT(column), column 
FROM table 
GROUP BY column

SELECT SUM(column), column 
FROM table 
GROUP BY column

GROUP BY

SELECT COUNT(items.itemcallnumber) AS 'Number of Items', items.itemcallnumber 
FROM items 
GROUP BY items.itemcallnumber 

SELECT b.title, SUM(i.issues) 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
WHERE i.issues > 0 
GROUP BY b.title;

HAVING

WHERE - filter results before grouping

HAVING - filter results after grouping

SELECT COUNT(column1), column2
FROM table
GROUP BY column2
HAVING COUNT(column1) > 'num'

HAVING

SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS 'Duplicate biblionumbers', isbn 
FROM biblioitems 
GROUP BY isbn 
HAVING COUNT(isbn)>1

MARC

SELECT 

ExtractValue(metadata,'//datafield[@tag="tag"]/*'),

ExtractValue(metadata,'//datafield[@tag="tag"]/subfield[@code="subfield"]'),

ExtractValue(metadata,'//datafield[@tag="tag"]/subfield[@code>="subfield"]')

FROM table

Table will always be biblio_metadata or auth_header

MARC

SELECT ExtractValue(metadata,
'//datafield[@tag="245"]/*') AS 'Full Title information' 
FROM biblio_metadata 
WHERE biblionumber=14

Charts for displaying data

Take a break

Read the blog post

• Automatically link authorities to biblios while cataloguing
• Default order of MARC subfields
• 'Transfers to send' report
• Shibboleth-only mode for single sign-on
• Easy printing of patron check-in slip
• Automatic checkin at end of loan period
• Default hold expiration date
• Preview SQL from list of saved reports
• Take a list parameter in reports

Contributing to Koha

• Share your usage statistics with Hea
• Share content with the Koha Community with ManaKB
• Contribute directly
  • Test patches
  • Write patches
  • Sponsor bug fixes and enhancements

Any questions?

Thank you for coming!

Slides: https://slides.com/aleisha/koha-librarians-workshop-27-july