Notices Reports

From Koha Wiki
Jump to navigation Jump to search

This is the page that relates to notices reports.

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

Notices Sent

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Notices
  • Purpose: Count of overdue notices sent in a specific time frame (by type). Uses the following codes for overdue messages: ODUE, ODUE2, ODUE3. Edit notice names as necessary. See Notices Available below.
  • Status: Complete
SELECT monthname(message_queue.time_queued) AS month, year(message_queue.time_queued) AS year, 
       message_queue.letter_code AS notice, count(message_queue.borrowernumber) AS count 
FROM message_queue 
WHERE message_queue.time_queued between <<Sent between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
      AND message_queue.letter_code in ('ODUE', 'ODUE2', 'ODUE3' )
      AND status = 'sent' 
GROUP BY year(message_queue.time_queued), month(message_queue.time_queued), message_queue.letter_code

Overdue Notices Available

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of letter codes used in trigger table.
  • Status: Complete
select 
    distinct code 
from 
   letter 
   inner join (
             select distinct letter1 as code from overduerules 
       UNION select distinct letter2 as code from overduerules 
       UNION select distinct letter3 as code from overduerules
   ) as overdue_trigger using (code)

Patrons ordered by days overdue

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of patrons with overdue items, ordered by days overdue.
  • Status: Complete

I would like to highlight rows where 'days overdue' matches (delay1, delay2, delay3) from overduerules. 

SELECT
    datediff(CURRENT_DATE, date_due) AS 'days overdue',
    count(*) as 'count',
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN borrowers USING (borrowernumber)
WHERE datediff(CURRENT_DATE, date_due) > 1
GROUP BY datediff(CURRENT_DATE, date_due), borrowernumber
ORDER BY datediff(CURRENT_DATE, date_due) asc, count(*) desc

Patrons with overdue notices triggered today

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of patrons with overdue notices triggered today.
  • Status: Complete
SELECT
    datediff(CURRENT_DATE, date_due) AS 'days overdue',
    count(*) AS 'count',
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN (
              SELECT DISTINCT delay1 AS delay FROM overduerules
        UNION SELECT DISTINCT delay2 AS delay FROM overduerules
        UNION SELECT DISTINCT delay3 AS delay FROM overduerules
    ) AS odr ON ( datediff(CURRENT_DATE, date_due) = odr.delay )
    INNER JOIN borrowers USING (borrowernumber)
WHERE datediff(CURRENT_DATE, date_due) > 1
GROUP BY datediff(CURRENT_DATE, date_due), borrowernumber
ORDER BY datediff(CURRENT_DATE, date_due) ASC, count(*) DESC

Patrons ordered by count of items with third notices

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list patrons with a count of items which would trigger a third notice.
  • Status: Complete
SELECT
    count(*) AS 'count',   
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN borrowers USING (borrowernumber)
WHERE
    date_add( date_due, INTERVAL (select max(delay3) from overduerules) DAY ) < CURRENT_DATE
GROUP BY
    borrowernumber order BY count(*)

Notices and slips, select content

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Find Notices and Slips with selected content.
  • Status: Complete
SELECT
    module, code, branchcode, name, is_html, title, message_transport_type
FROM
    letter
WHERE
    content like '<<Text in notice (use % as wildcard)>>'

Overduerules in a single column

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Show Overduerules in a single column
  • Status: Complete
  • Comments: This query is probably a useful base for more complex queries.
SELECT
    letter_number,
    branchcode,
    categorycode,
    delay,      
    letter_code,
    debarred
FROM
   (
            SELECT
                '1' as letter_number, branchcode, categorycode,
                delay1 as delay, letter1 as letter_code,
                debarred1 as debarred
            FROM overduerules
        UNION
            SELECT
                '2' as letter_number, branchcode, categorycode,
                delay2 as delay, letter2 as letter_code,
                debarred2 as debarred
            FROM overduerules
        UNION
            SELECT
                '3' as letter_number, branchcode, categorycode,
                delay3 as delay, letter3 as letter_code,
                debarred3 as debarred
            FROM overduerules
   ) as odr

Show overdue notices in message queue

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Show counts of overdue notices by letter code, message transport type and status, ordered by date.
  • Status: Complete
SELECT
    count(*),
    date(time_queued),
    letter_code,
    message_transport_type as mtt,
    status
FROM
    message_queue
    INNER JOIN
       (
                SELECT letter1 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
            UNION
                SELECT letter2 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
            UNION
                SELECT letter3 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
       ) AS odr using (letter_code)
GROUP BY
    date(time_queued), letter_code, mtt,status
ORDER BY
    date(time_queued), letter_code, mtt,status

Daily message count by "Sent to" domain

  • Developer: George H. Williams, Northeast Kansas Library System
  • Module: Notices
  • Purpose: Returns a count of messages sent to a specific domain on a specific date during the date range you set
  • Status: Complete.
SELECT
  Date_Format(message_queue.updated_on, '%Y-%m-%d') AS DATE_SENT,
  Coalesce(SubString_Index(message_queue.to_address, '@', -1), '') AS SENT_TO,
  Count(DISTINCT message_queue.message_id) AS COUNT_OF_MESSAGES_SENT
FROM
  message_queue
WHERE
  message_queue.updated_on BETWEEN <<Messages sent between the beginning of the day on date1|date>> AND (<<and the end of the day on date2|date>> + INTERVAL 1
  DAY)
GROUP BY
  Date_Format(message_queue.updated_on, '%Y-%m-%d'),
  Coalesce(SubString_Index(message_queue.to_address, '@', -1), '')
ORDER BY
  DATE_SENT DESC,
  SENT_TO

Daily message count by "Sent to" specific domains

  • Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
  • Module: Notices
  • Purpose: Returns a count of messages sent to specific domains (gmail/yahoo) on a specific date during the date range you set
  • Status: Complete.
SELECT DATE(mq.time_queued) Date,IF(mq.to_address LIKE '%gmail.com','gmail.com','yahoo.com') Carrier, COUNT(*) Notices
FROM message_queue mq
WHERE DATE(mq.time_queued) BETWEEN <<Date in question from|date>> AND <<to|date>> AND mq.status="sent" AND (mq.to_address LIKE '%gmail.com' OR mq.to_address LIKE '%yahoo.com')
GROUP BY Date, Carrier

Primary email domain usage in borrower accounts

  • Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
  • Module: Notices
  • Purpose: Returns a count of uses of each unique domain in borrower primary emails. Use this to scan through results for anomalies.
  • Status: Complete.
SELECT SUBSTRING_INDEX(b.email, '@', -1) domain, count(*)
FROM borrowers b
WHERE b.email IS NOT NULL AND b.email != ''
GROUP BY domain

Find a user with a specific partial domain

  • Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
  • Module: Notices
  • Purpose: Great tool to track down those pesky partial or incomplete domains without having to sift through all the other email addresses that have that partial domain as well.
  • Status: Complete.
SELECT b.cardnumber,SUBSTRING_INDEX(b.email, '@', -1) domain
FROM borrowers b
WHERE SUBSTRING_INDEX(b.email, '@', -1) = <<domain>>
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Notices_Reports&oldid=38526"