Displaying Real-Time Library Statistics on the Koha Staff Interface

Efficient library management thrives on real-time insights into library operations. By customizing Koha, the open-source Integrated Library System (ILS), you can display essential library statistics and daily circulation metrics directly on the staff interface. This blog post walks you through the entire process, including creating SQL reports, configuring the intranetuserjs preference, and integrating the data into the interface.

What Will Be Displayed?

Library Statistics:

• Branch-wise count of unique titles.
• Total number of item records.

Daily Circulation Statistics:

• Issues, renewals, returns, and payments recorded for the current day.

Step 1: Create Two Public SQL Reports

Report 1: Library Statistics

SELECT 

    homebranch, 

    COUNT(DISTINCT biblionumber) AS bibs, 

    COUNT(itemnumber) AS items 

FROM 

    items 

GROUP BY 

    homebranch 

ORDER BY 

    homebranch ASC;

Report 2: Daily Circulation Statistics

SELECT 

    branch,

    SUM(CASE WHEN type = 'issue' THEN 1 ELSE 0 END) AS issue_count,

    SUM(CASE WHEN type = 'return' THEN 1 ELSE 0 END) AS return_count,

    SUM(CASE WHEN type = 'renew' THEN 1 ELSE 0 END) AS renew_count,

    SUM(CASE WHEN type = 'payment' THEN 1 ELSE 0 END) AS payment_count

FROM 

    statistics

WHERE 

    DATE(datetime) = CURDATE()

GROUP BY 

    branch;

Note: Record the report IDs generated by Koha after saving these reports. These will be required in later steps.

Step 2: Update the intranetuserjs Preference

To dynamically fetch and display the data in the "News" section of the Koha staff interface, update the intranetuserjs preference as follows:

• Go to Administration > Global System Preferences.
• Search for the intranetuserjs preference.
• Replace its content with the following JavaScript:

$(document).ready(function() {

    // Check if the main intranet page is loaded

    if ($('#main_intranet-main').length) {

        // Fetch Library Statistics (Replace with your Report ID for Library Statistics)

        $.getJSON("/cgi-bin/koha/svc/report?id=17&annotated=1", function(data) {

            var branches = data[0].homebranch;

            var bibs = data[0].bibs;

            var items = data[0].items;

            // Fetch Daily Circulation Statistics (Replace with your Report ID for Circulation Statistics)

            $.getJSON("/cgi-bin/koha/svc/report?id=39&annotated=1", function(data2) {

                var branch = data2[0].branch;

                var renewCount = data2[0].renew_count;

                var issueCount = data2[0].issue_count;

                var returnCount = data2[0].return_count;

                var paymentCount = data2[0].payment_count;

                // Append statistics to the staff interface

                $('div.newsitem').prepend(`

                    <div class="newsitem">

                        <h3>Library Statistics</h3>

                        <table>

                            <tr><th>Branch</th><th>Unique Titles</th><th>Total Copies</th></tr>

                            <tr><td>${branches}</td><td>${bibs}</td><td>${items}</td></tr>

                        </table>

                    </div>

                    <div class="newsitem">

                        <h3>Today’s Circulation Statistics</h3>

                        <table>

                            <tr><td>Branch</td><td>${branch}</td></tr>

                            <tr><td>Issues</td><td>${issueCount}</td></tr>

                            <tr><td>Renewals</td><td>${renewCount}</td></tr>

                            <tr><td>Returns</td><td>${returnCount}</td></tr>

                            <tr><td>Payments</td><td>${paymentCount}</td></tr>

                        </table>

                    </div>

                `);

            });

        });

    }

});

Note: Replace id=17 and id=39 with the actual report IDs from Step 1. Save the changes.

Step 3: Reload and Verify

Refresh the Koha staff interface.

Verify the new sections titled "Library Statistics" and "Today’s Circulation Statistics" in the "News" section.