SQL Tips and Tricks for Koha Reports

by Nick Clemens on Apr 22, 2020

You know how to write reports, now we will show you how to tweak them! In this webinar, Nick will show you how to use CSS, HTML, and javascript to style your reports and a few tips and tricks to modify them!

Report Trips and Tricks from Presentation

Concat
1. CONCAT('') As renew_button, onclick="window.location.href=\'/cgi-bin/koha/members/setstatus.pl?borrowernumber=',borrowernumber,'&destination=&reregistration=y\';" value="Renew patron" />') As renew_button,
Reports with Reports
Reports with Styling
Runtime Parameters

Webinar Information

When: April 29, 2020, 1:30 PM Eastern Time (US and Canada)

Topic: SQL Tips and Tricks for Koha Reports

After registering, you will receive a confirmation email containing information about joining the webinar. This webinar will be recorded and made available on demand.

Report that demonstrates different echniques

SELECT
 CONCAT( firstname, " ", surname) AS borrower,
 dateexpiry,
 CONCAT('<a target="_blank" href="/cgi-bin/koha/members/setstatus.pl?borrowernumber=',borrowernumber,'&destination=&reregistration=y">Renew patron</a>') As renew_link,
 CONCAT('<input type="button" onclick="window.location.href=\'/cgi-bin/koha/members/setstatus.pl?borrowernumber=',borrowernumber,'&destination=&reregistration=y\';" value="Renew patron" />') As renew_button,
 CASE categorycode
 WHEN 'S' THEN CONCAT('<span style="color: blue">',categorycode,'</span>')
 WHEN 'T' THEN CONCAT('<span style="color: pink">',categorycode,'</span>')
 WHEN 'PT' THEN CONCAT('<span style="color: orange">',categorycode,'</span>')
 END AS case_color,
 CONCAT('<span class="category_',categorycode,'">',categorycode,'</span>') AS added_class,
 CONCAT('<a target="_blank" href="/cgi-bin/koha/members/moremember.pl?borrowernumber=',borrowernumber,'">',firstname, " ", surname,'</a>') AS borrower_link,
 GROUP_CONCAT(barcode) AS barcodes_simple,
 GROUP_CONCAT(barcode SEPARATOR " -- ") AS barcodes,
 GROUP_CONCAT(barcode SEPARATOR "</br>") AS barcodes,
 GROUP_CONCAT('<a href="/cgi-bin/koha/reports/guided_reports.pl?reports=3&phase=Run+this+report¶m_name=itemnumber&sql_params=',itemnumber,'">',barcode,'</a>' SEPARATOR "</br>") AS barcodes,
 MIN(date_due),
 IF( MIN(date_due) <= CURDATE(),CONCAT('<span class="overdue">',MIN(date_due),'</span>'),MIN(date_due)) AS styled_due
FROM borrowers
JOIN issues USING (borrowernumber)
JOIN items USING (itemnumber)
WHERE ccode RLIKE <<Collection code|Collections>>
GROUP BY borrowernumber,categorycode,surname, firstname, dateexpiry

JS code to add links to tile and author in reports


if (window.location.href.indexOf("phase=Run") > -1) {
 var title_row = $("#rep_guided_reports_start table th:contains('title')").index();
 var biblionumber_row = $("#rep_guided_reports_start table th:contains('biblionumber')").index();
 var author_row = $("#rep_guided_reports_start table th:contains('author')").index();
 $('tr').each( function(){
 var link_id = $(this).find('td:eq('+biblionumber_row+')').text();
 var link_text;
 var link_col;
 if( biblionumber_row > -1 ){
 if( title_row > -1 ){
 link_text = $(this).find('td:eq('+title_row+')').text();
 link_col = title_row;
 } else {
 link_text = link_id;
 link_col = biblionumber_row;
 }
 $(this).find('td:eq('+link_col+')').html(''+link_text+'');
 }
 if ( author_row > -1 ){
 $(this).find('td:eq('+author_row+')').html(''+$(this).find('td:eq('+author_row+')').text() +'');
 }
 });
}

CSS that applies to report

#rep_guided_reports_start .category_PT { color: blue; }

.category_S { color: green; }

.category_T { color: pink; }

Read more by Nick Clemens

Tags