Misc Reports

From Koha Wiki
Jump to navigation Jump to search

This is a page for miscellaneous SQL reports that don't fit on any of the other pages created for the SQL Reports Library.

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Plugin configuration page links

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Koha Administration
  • Purpose: Quick links to plugin configuration pages.
  • Status: Complete
SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/plugins/run.pl?class=', plugin_class, '&method=configure' '\">',
        SUBSTRING_INDEX( plugin_data.plugin_class, ':', -1 ), '</a>'
    ) AS plugin
FROM
    plugin_data

Most Popular Searches

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Search
  • Purpose: Find popular search terms
  • Status: Complete
SELECT COUNT(*) as Count,
       RIGHT(
              query_desc, 
              Length(query_desc) - Instr(query_desc, ':')
       ) AS 'Search string'
FROM   search_history
GROUP  BY RIGHT(query_desc, Length(query_desc) - Instr(query_desc, ':'))
ORDER  BY COUNT(*) DESC

Most Popular Searches II

  • Developer: Steffi (with minor help)
  • Module: Search
  • Purpose: Find popular search terms
  • Status: Complete
SELECT search_history.sessionid,
       search_history.query_desc,
       search_history.type,
       search_history.total,
       search_history.time,
       borrowers.branchcode,
       borrowers.categorycode
FROM search_history
JOIN borrowers ON (search_history.userid=borrowers.borrowernumber)
JOIN branches ON (borrowers.branchcode=branches.branchcode)
WHERE borrowers.branchcode=<<
  SELECT Library|branches>>
  AND search_history.time BETWEEN <<SEARCH history TIMESTAMP BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>>

Show number of items available

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Show counts of available items
  • Status: Incomplete
  • Notes: This is roughly the same logic used in search results.
  • is the item in transit? => not available
  • is it lost? => not available
  • is it damaged? => not available
  • is it withdrawn? => not available
  • is it on hold?
    • Is the item waiting?
      • yes => not avaliable
      • no
        • Is the syspref AllowItemsOnHoldCheckout set?
        • no => not available
  • is it set as 'not for loan'?
    • is the syspref AllowNotForLoanOverride set?
      • no => not available

The AllowItemsOnHoldCheckout logic is too complicated to capture in SQL. The AllowNotForLoanOverride should be fairly straightforward, that still needs to be added. 

SELECT
    title,
    SUM(
        IF(
            (
                onloan is not null
                OR itemlost > 0
                OR damaged > 0
                OR withdrawn > 0
                OR EXISTS (
                    SELECT *
                    FROM branchtransfers
                    WHERE
                        items.itemnumber = branchtransfers.itemnumber
                        AND branchtransfers.datearrived IS NULL
                )
                OR EXISTS (
                    SELECT *
                    FROM reserves
                    WHERE
                        items.itemnumber = reserves.itemnumber
                        AND reserves.found IN ( 'W', 'T' )
                )
            ),
            0,
            1
          )
    ) as 'Copies available',
    count(*) as 'Total copies'
FROM
    items
    INNER JOIN biblio USING (biblionumber)
    LEFT JOIN reserves USING (itemnumber)
    LEFT JOIN branchtransfers USING (itemnumber)
GROUP BY biblio.biblionumber

Circulation statistics about serial items based on the title of biblio record

  • Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
  • Module: Circulation
  • Purpose: List circulation statistics for all items of a serial biblio record. This report accepts up to 10 biblio serial titles at a time.
  • Status: Complete
SELECT biblio.title AS Title, 
  items.holdingbranch AS Current_Location, 
  items.location AS Location, 
  items.ccode AS Collection, 
  items.itemnumber, 
  items.enumchron AS Item_Info, 
  (SELECT count(*)
    FROM statistics
    WHERE type="issue" AND
    statistics.itemnumber=items.itemnumber) AS Number_of_issues
FROM biblio 
LEFT JOIN items ON biblio.biblionumber=items.biblionumber
WHERE items.itype="SERIAL" AND
  biblio.title IN (<<Enter first title>>,
    <<Enter second title>>,
    <<Enter third title>>,
    <<Enter fourth title>>,
    <<Enter fifth title>>,
    <<Enter sixth title>>,
    <<Enter seventh title>>,
    <<Enter eigth title>>,
    <<Enter ninth title>>,
    <<Enter tenth title>>)  
ORDER BY title ASC,
  Current_Location ASC

List of Lists

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Lists
  • Purpose: Show all lists with their creator
  • Status: Complete
select b.borrowernumber, b.surname, b.firstname, 
       s.shelfname 
from virtualshelves s 
left join borrowers b 
on (b.borrowernumber=s.owner)

List's content

  • Developer: Abdullrahman Hegazy
  • Module: Lists
  • Purpose: Show the content of One list, 'You can find the shelf number in the address bar after viewshelf='NUM'.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS Title,author
FROM biblio,virtualshelfcontents
Where biblio.biblionumber=virtualshelfcontents.biblionumber
AND shelfnumber=<<Enter Shelf number>>

Backup/Share OPAC Layout from system preferences

  • Developer: Liz Rea, NEKLS
  • Module: Administration
  • Purpose: Dump the contents of all of the OPAC Interface user input customization preferences, for backup or sharing of layout/CSS
  • Status: Complete
SELECT variable, value 
FROM systempreferences 
WHERE variable IN (
     'OPACUserCSS', 'opacuserjs', 'OPACResultsSidebar', 'OPACNoResultsFound',
     'OpacNav', 'opaccredits','opacheader', 'OpacMainUserBlock'
)

OCLC Number Lookup

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: Look up OCLC Number by barcode.
  • Status: Complete

This report assumes that OCLC Numbers are stored in MARC 001 field. 

select
    biblionumber
  , ExtractValue( marcxml, '//controlfield[@tag=\"001\"]' ) as 'OCLC Number'
  , title 
from biblioitems 
inner join biblio using (biblionumber) 
inner join items using (biblionumber) 
where barcode=<<barcode>>

Get list of OCLC numbers in catalog

  • Developer: Aleisha Amohia, Catalyst IT
  • Module: Cataloging
  • Purpose: Get list of OCLC numbers to upload holdings. Note: this query excludes items that have been checked out in the past or have a reference itemtype.
  • Status: Complete
SELECT
DISTINCT(
SUBSTRING_INDEX(
SUBSTRING_INDEX(ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]'), '(OCoLC)', -1)
,' ', 1)
) 'OCLC Number'
FROM biblio b  
LEFT JOIN items i USING (biblionumber)  
LEFT JOIN biblioitems m USING (biblionumber)
WHERE (i.issues IS NOT NULL
OR i.itype = 'WG_REF')
AND ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') LIKE '%(OCoLC)%';

Troubleshoot accidental checkout when action logs are disabled

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Notices
  • Purpose: Troubleshoot accidental checkouts
  • Status: Complete

This report takes a date range of notice queue times, and prints CHECKIN, CHECKOUT and RENEWAL notices ordered by borrowernumber and time queued. If you see cclusters of CHECKOUT, RENEWAL and CHECKIN notices within the same minute for the same borrower, this is an indication of accidental check-out.

Circulation action logs are a better way to monitor this, but they are often disabled for performance reasons. 

select 
   borrowernumber, 
   letter_code,
   time_queued,
   content 
from message_queue 
where
date(time_queued) BETWEEN <<Notice Queued BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
and letter_code in ('CHECKIN', 'RENEWAL', 'CHECKOUT') 
order by borrowernumber, time_queued

Find all character encodings in MARCXML records

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: List all character encodings
  • Status: Complete
select 
    count(*),
    CASE Mid( 
        ExtractValue(marcxml, '//leader'), 
        9, 
        1
    ) 
    WHEN ' ' THEN 'MARC8'
    WHEN 'a' THEN 'UTF8'
    ELSE CONCAT( 
        'Unknown encoding type: ', 
        Mid(
            ExtractValue(marcxml, '//leader'),
            9,
            1
        )
    )
    END AS encoding
from biblioitems 
where Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        ) is not NULL
group by Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        )

According to the MARC21 standard, the 'Leader' character position 9 determines character encoding: '#' for MARC8, 'a' for UTF8. Koha uses ' ' instead of '#' for MARC8.

OAI-PMH Test URL

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show URLs for OAI-PMH testing
  • Status: Complete
SELECT
    concat(
        opacbaseurl.value,
        '/cgi-bin/koha/oai.pl?verb=GetRecord&identifier=',
        archiveID.value,
        ':',
        biblionumber,
        '&metadataPrefix=marcxml'
    ) as 'OAI-PMH URL',
    biblionumber,
    title
FROM
    biblio, systempreferences as opacbaseurl, systempreferences as archiveID
WHERE
    opacbaseurl.variable = 'opacbaseurl'
    AND archiveID.variable = 'OAI-PMH:archiveID'

Titles with changes still pending in zebraqueue

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging, Circulation
  • Purpose: Show which titles have changes that have not yet been picked up by zebra
  • Status: Complete
select 
    biblio_auth_number as biblionumber,
    title,
    time as 'time queued'
from
    zebraqueue
    inner join biblio on (zebraqueue.biblio_auth_number = biblio.biblionumber)
where
    done=0
    and server='biblioserver'
    and operation = CONCAT( 'specialU', 'pdate' )
order by
    time

In the where clause, operation = CONCAT( 'specialU', 'pdate' ) is necessary because the keyword 'Update' is rejected in reports.

Circulation logs by borrower, select item barcode

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Circulation
  • Purpose: Scan a barcode, and get links to the circulation logs for all borrowers who have checked that item out.
  • Status: Complete
SELECT 
    borrowernumber, 
    cardnumber,
    CONCAT(
        firstname, ' ', surname, 
        ' (', 
        '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=', 
        borrowernumber,
        '\">', 'Circulation log' , '</a>', 
        ')'
    ) AS Patron
FROM
    borrowers
    INNER JOIN statistics USING (borrowernumber)
    INNER JOIN items USING (itemnumber)
WHERE
    barcode = <<Item barcode>>
    and statistics.type='issue'
GROUP BY
    borrowernumber

Show how borrowers were deleted (select date range)

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Find out who deleted borrowers, and when.
  • Status: Complete
SELECT
    timestamp,
    CONCAT( staff.firstname, ' ', staff.surname ) as 'Staff', 
    CONCAT( db.firstname, ' ', db.surname ) as 'Patron', 
    db.cardnumber
FROM
    deletedborrowers db 
    INNER JOIN action_logs on db.borrowernumber = action_logs.object 
    LEFT JOIN borrowers as staff on action_logs.user = staff.borrowernumber
WHERE
    DATE( action_logs.timestamp ) BETWEEN <<patron deleted between|date>> AND <<and|date>>
    AND action_logs.module = 'MEMBERS'
    AND action_logs.action = concat( 'DEL', 'ETE' )
ORDER BY action_logs.timestamp

Workaround for Bug 18611

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Find valid barcodes in import records batch
  • Status: Complete
SELECT
    barcode
FROM
    import_batches
    INNER JOIN import_records using (import_batch_id) 
    INNER JOIN import_items using (import_record_id) 
    INNER JOIN items using (itemnumber)
WHERE
    import_batch_id = <<Batch number>>

Show thumbnails of local cover images

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show thumbnails of local cover images
  • Status: Complete
SELECT 
    b.title AS Title,
    CONCAT(
        '<img src=\"',
        IF(
            CHAR_LENGTH(systempreferences.value),
            CONCAT(systempreferences.value), 
            ''
        ),
        '/cgi-bin/koha/opac-image.pl?thumbnail=1&biblionumber=',
        b.biblionumber, '/">'
    ) AS Image
FROM systempreferences, biblio AS b inner join biblioimages using (biblionumber)
WHERE systempreferences.variable='OPACBaseURL'

Show thumbnails of all titles with local cover images

  • Developer: Andrew Fuerste-Henry, Dubuque County Library District
  • Module: Misc
  • Purpose: Show thumbnails of local cover images
  • Status: Complete
SELECT title, 
concat('<img src="/cgi-bin/koha/catalogue/image.pl?thumbnail=',c.imagenumber,'&amp;imagenumber=',c.imagenumber,'"') as cover
FROM biblio b 
LEFT JOIN cover_images c USING (biblionumber)
WHERE c.imagenumber is not null

Show action logs for holds

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show action logs for holds, select borrower cardnumber
  • Status: Complete
SELECT
    log.timestamp,
    concat( librarian.firstname, ' ', librarian.surname, ' (', librarian.cardnumber, ')' ) AS Librarian,
    log.action,
    log.object,
    log.info,
    log.interface
FROM
    borrowers patron
    INNER JOIN (
        SELECT borrowernumber, reserve_id FROM reserves
    UNION
        SELECT borrowernumber, reserve_id  FROM old_reserves
    ) AS hold using (borrowernumber)
    INNER JOIN action_logs log on log.object = hold.reserve_id AND log.module='HOLDS'
    LEFT JOIN borrowers librarian on log.user = librarian.borrowernumber
WHERE
    patron.cardnumber = <<borrower cardnumber>>
ORDER BY log.timestamp

Dump label creator templates

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Dump label creator templates (ie. page formats)
  • Status: Complete
SELECT
    template_id as 'Template ID',
    template_code as 'Template code',
    template_desc as 'Template description',
    units as 'Units',
    page_height as 'Page height',
    page_width as 'Page width',
    label_width as 'Label width',
    label_height as 'Label height',
    top_margin as 'Top page margin',
    left_margin as 'Left page margin',
    top_text_margin as 'Top text margin',
    left_text_margin as 'Left text margin',
    cols as 'Number of columns',
    rows as 'Number of rows',
    col_gap as 'Gap between columns',
    row_gap as 'Gap between rows'
FROM
    creator_templates

Library Books Investment by Collection Code

  • Developer: Satisha MV, Govt.Engineering College, Hassan
  • Module: Misc
  • Purpose: To know No. of Titles(Records), No. of Volumes(Items) and Total investment by Collection Code
  • Status: Complete
SELECT  
ccode AS 'Collection Code', 
COUNT(DISTINCT items.biblionumber) AS 'No of Titles',
COUNT(itemnumber) AS 'No of Volumes',
SUM(price) AS 'Investment or Total Expenditure'
FROM items 
GROUP BY ccode

Items Added/Accessioned between date range and its investment by Collection Code

  • Developer: Satisha MV, Govt.Engineering College, Hassan
  • Module: Misc
  • Purpose: To know No. of Titles(Records), No. of Volumes(Items) added/accessioned between date range and its investment by Collection Code. Here collection code refers to

departments or Subjects or Branches or Programmes

  • Status: Complete
SELECT  
ccode AS 'Collection Code', 
COUNT(DISTINCT items.biblionumber) AS 'No of Titles',
COUNT(itemnumber) AS 'No of Volumes',
SUM(price) AS 'Investment or Total Expenditure'
FROM items
WHERE date(dateaccessioned) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY ccode

Zero Results Searches

  • Developer: Spencer Smith
  • Module: Search
  • Purpose: This returns a list of search queries in a selected date range that returned ZERO results at the time they were run. This can help us spot blind spots in our collection development practices.
  • Status: Completed
SELECT COUNT(*) AS Count,
       RIGHT(
              query_desc, 
              Length(query_desc) - Instr(query_desc, ':')
       ) AS 'Search string', total
FROM   search_history
WHERE Total= 0
AND time BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP  BY RIGHT(query_desc, Length(query_desc) - Instr(query_desc, ':'))
ORDER  BY COUNT(*) DESC

PayPal Transactions (revised for 19.11)

  • Developer: Kelly McElligott, ByWater Solutions
  • Module: Circulation
  • Purpose: Tracking Paypal accounts
  • Status: Completed
  • Works with: 19.11
SELECT
CONCAT(
'<a target="_blank" href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',
borrowers.borrowernumber,
'\">',
borrowers.borrowernumber,
'</a>'
) AS Link,
borrowers.surname,
borrowers.firstname,
borrowers.cardnumber,
accountlines.credit_type_code, accountlines.status,
accountlines.date,
accountlines.amount,
accountlines.note
FROM
accountlines
LEFT JOIN borrowers using (borrowernumber)
WHERE accountlines.credit_type_code is not null AND
accountlines.note = 'PayPal' AND
date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-dd)|date>>
ORDER BY
date,
borrowers.surname

Closures in the Next Week

  • Developer: Andrew Fuerste-Henry, Dubuque County Library District
  • Module: Tools
  • Purpose: Produce a list of all closures from the Koha calendar in the next week
  • Status: Completed
  • Works with: 21.11
SELECT str_to_date(date,'%Y-%m-%d') as date, title, group_concat(branchcode separator ', ')
FROM
(
SELECT date, title, branchcode
FROM (
		SELECT concat(year,'-',month,'-',day) as date, title, branchcode
		FROM special_holidays
        WHERE isexception=0
     ) tinkywinky

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate()),'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
	 ) dipsy

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate())+1,'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
	 ) lala

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 1 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 6 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 5 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 4 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 3 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 2 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 1 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
) tubbies
WHERE date between curdate() and date_add(curdate(),interval 1 week)
GROUP BY date
ORDER BY date

Closures in the Next 4 Weeks for Specific Library

  • Developer: Andrew Fuerste-Henry, Dubuque County Library District / Modified by Christopher Brannon
  • Module: Tools
  • Purpose: Produce a list of all closures from the Koha calendar in the next 4 weeks
  • Status: Completed
  • Works with: 21.11
SELECT date, title
FROM
(
SELECT date, title, branchcode
FROM (
		SELECT concat(year,'-',month,'-',day) as date, title, branchcode
		FROM special_holidays
        WHERE isexception=0 AND branchcode = <<Library|branches>>
     ) tinkywinky

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate()),'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
         AND branchcode = <<Library|branches>>
	 ) dipsy

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate())+1,'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
         AND branchcode = <<Library|branches>>
	 ) lala

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 1 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 6 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 5 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 4 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 3 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 2 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 1 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_1
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 2 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 13 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 12 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 11 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 10 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 9 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 8 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_2
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 3 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 20 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 19 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 18 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 17 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 16 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 15 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_3
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 4 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 27 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 26 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 25 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 24 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 23 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 22 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_4
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>
) tubbies
WHERE date between curdate() and date_add(curdate(),interval 4 week)
GROUP BY date

Get list of deleted items from background job

  • Developer: Jason Robb (SEKLS)
  • Module: Admin/Tools
  • Purpose: Pulls some info about deleted items within a batch deletion job
  • Status: Completed
  • Works with: 22.05
  • Note: This will only give as many lines as the dummy joined indexes -- I have mine set to 1000 which is my batch delete cap
SELECT
  idx,
  json_extract(data,CONCAT('$.report.deleted_itemnumbers[', idx, ']')) AS del_inum,
  barcode,
  COALESCE(b.title, db.title) AS Title,
  COALESCE(b.author, db.author) AS Author,
  a1.lib AS Loc,
  a2.lib AS Ccode
FROM background_jobs
JOIN (
SELECT 0 AS idx UNION 
SELECT 1 AS idx UNION
SELECT 2 AS idx UNION
SELECT 3 AS idx UNION
SELECT 4 AS idx UNION
/* can add more lines as needed */
SELECT 5 AS idx
  ) AS indexes
LEFT JOIN deleteditems di ON (json_extract(data,CONCAT('$.report.deleted_itemnumbers[', idx, ']')) = di.itemnumber)
LEFT JOIN biblio b ON (di.biblionumber=b.biblionumber)
LEFT JOIN deletedbiblio db ON (di.biblionumber=db.biblionumber)
LEFT JOIN authorised_values a1 ON (a1.authorised_value = di.permanent_location AND a1.category = 'LOC')
LEFT JOIN authorised_values a2 ON (a2.authorised_value = di.ccode AND a2.category = 'CCODE')
WHERE background_jobs.id=<<Deleted item job ID>> AND barcode <> ''
LIMIT 1000

Example of dynamic email content in report

  • Developer: Christopher Brannon (Coeur d'Alene Public Library / Cooperative Information Network)
  • Purpose: Demonstrate how to incorporate links in reports that create dynamic emails with mailto, subject, and body information.
  • Status: Completed
  • Works with: 23.05
SELECT CONCAT('<div style="border:1px solid black;"><b>Due: </b>',DATE(i.date_due),'<br/><b>Barcode: </b><a href="/cgi-bin/koha/catalogue/moredetail.pl?type=&itemnumber=',i2.itemnumber,'" target="_blank">',i2.barcode,'</a><br/><b>Title: </b>',b.title,'<br/><b>Call #: </b>',i2.itemcallnumber,'<br/><b>Loaned to: </b>#',b2.cardnumber,' - ',b2.firstname,' ',b2.surname,' (',IF(b2.email!='',CONCAT('<a href="mailto:',b2.email,'?subject=',b.title,' Overdue&body=',b2.firstname,',%0D%0AWe are writing to let you know that ',b.title,' is overdue.  Please return it as soon as possible so others may also enjoy using it.%0D%0A%0D%0AIf you have already returned this item, you may disregard this notice.%0D%0A%0D%0ASincerely,%0D%0A',b3.branchname,'">',b2.email,'</a>'),IF(b2.phone,b2.phone,'NO CONTACT INFO')),') [',b2.categorycode,']</div>') AS 'OVERDUE REPORT'
FROM issues i
LEFT JOIN items i2 USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN borrowers b2 USING (borrowernumber)
LEFT JOIN branches b3 ON (i2.homebranch = b3.branchcode)
WHERE i.date_due < NOW() - INTERVAL 2 DAY AND i2.damaged='0'

Conditional searching with REGEXP

  • Developer: Joseph Alway
  • Purpose: Search for 1st Regular Expression excluding matches on 2nd Regular Expression.
  • Status: Completed
  • Works with: 23.05
SELECT
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumber,
  biblio.title,
  biblio.author 
FROM
  biblio_metadata 
  JOIN
    biblio 
    ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
  ExtractValue(metadata,"//datafield[@tag =<<1st Compare Field (XXX)>>]/subfield[@code =<<1st Compare Subfield(Y)>>]") REGEXP <<FIND REGEX 1>>
AND NOT
  ExtractValue(metadata,"//datafield[@tag =<<2nd Compare Field (XXX)>>]/subfield[@code =<<2nd Compare Subfield(Y)>>]") REGEXP <<EXCLUDE REGEX 2>>
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Misc_Reports&oldid=38525"