Koha Customized SQL Reports

-

Koha Customized SQL Reports

Note: Some SQL queries customized in Koha's backend ensure the Koha supports or not.


 Issue


SELECT

       staff.cardnumber AS 'Staff Card Number',

    CONCAT(staff.firstname, ' ', staff.surname) AS 'Staff Name',

    a.action AS 'Action',

    a.timestamp AS 'Time',

    c.barcode AS 'Acc. Number',

    d.title AS 'Title',

    CONCAT(k.firstname, ' ', k.surname) AS 'User Name',

    k.cardnumber AS 'User ID',

    ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') AS 'Floor',

    issues.date_due AS 'Due Date'

FROM

    action_logs a

LEFT JOIN

    borrowers staff ON a.user = staff.borrowernumber

LEFT JOIN

    items c ON a.info = c.itemnumber

LEFT JOIN

    biblio d ON c.biblionumber = d.biblionumber

LEFT JOIN

    borrowers k ON a.object = k.borrowernumber

LEFT JOIN

    items T5 ON T5.itemnumber = c.itemnumber

LEFT JOIN

    issues ON issues.itemnumber = c.itemnumber

WHERE

    a.timestamp BETWEEN <<Added Date |date>> AND <<and |date>>  -- Replace with actual date range

    AND a.module = 'CIRCULATION'

    AND a.action = 'ISSUE'

ORDER BY

    CASE

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'I FLOOR' THEN 1

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'II FLOOR' THEN 2

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'III FLOOR' THEN 3

        ELSE 4

    END,

    LENGTH(c.barcode) ASC,  -- Sort by length of barcode

    CAST(c.barcode AS UNSIGNED) ASC,  -- Then sort numerically

    a.timestamp;


-----


Return



SELECT

   

    staff.cardnumber AS 'Staff Card Number',

    CONCAT(staff.firstname, ' ', staff.surname) AS 'Staff Name',

    a.action AS 'Action',

    a.timestamp AS 'Time',

    c.barcode AS 'Acc. Number',

    d.title AS 'Title',

    CONCAT(k.firstname, ' ', k.surname) AS 'User Name',

    k.cardnumber AS 'User ID',

    ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') AS 'Floor',

    issues.date_due AS 'Due Date'

FROM

    action_logs a

LEFT JOIN

    borrowers staff ON a.user = staff.borrowernumber

LEFT JOIN

    items c ON a.info = c.itemnumber

LEFT JOIN

    biblio d ON c.biblionumber = d.biblionumber

LEFT JOIN

    borrowers k ON a.object = k.borrowernumber

LEFT JOIN

    items T5 ON T5.itemnumber = c.itemnumber

LEFT JOIN

    issues ON issues.itemnumber = c.itemnumber

WHERE

    a.timestamp BETWEEN <<Added Date |date>> AND <<and |date>>  -- Replace with actual date range

    AND a.module = 'CIRCULATION'

    AND a.action = 'RETURN'

ORDER BY

    CASE

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'I FLOOR' THEN 1

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'II FLOOR' THEN 2

        WHEN ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') = 'III FLOOR' THEN 3

        ELSE 4

    END,

    LENGTH(c.barcode) ASC,  -- Sort by length of barcode

    CAST(c.barcode AS UNSIGNED) ASC,  -- Then sort numerically

    a.timestamp;





---------------


Koha receipt HTML code (Mail Notification)


Return


Dear <br>         <<borrowers.title>>. <<borrowers.surname>><br>


The following items have been<b><font color="green"> Return:</b><br></font color>

<table style="width: auto; height: 45px;" border="2" TABLE BORDERCOLOR="green"   cellspacing="2" cellpadding="1">

  <tr>

    <td><b>Lib No</td></b>

    <td><<borrowers.cardnumber>></td>

  </tr>

  <tr>

    <td><b>Name of the Member</td></b>

    <td><<borrowers.surname>></td>

  </tr>

  <tr>

    <td><b>Barcode / Acc No</td></b>

    <td><<items.barcode>></td>

 

  </tr>

  <tr>

    <td><b>Book Title</td></b>

    <td><<biblio.title>></td>

  </tr>

 <tr>

    <td><b>Author</td></b>

    <td><<biblio.author>></td>


  </tr>

 <tr>

    <td><b>Date of Return</td></b>

    <td><<old_issues.returndate>></td>


  </tr>

</table>

<b><p>Visit your account details. <a href="/cgi-bin/koha/opac-main.pl?logout.x=1" target="_blank">Click here<img src="look-here.gif" alt="Smiley face" width="50" height="42"></img></a></p></b>

<p><b>User Name:</b> <<borrowers.userid>></p></b>

<p><b>Password:</b> <<borrowers.userid>></p></b>

<p><b>This is an Automatically Generated Email.</b>


Thank you for visiting <a href="/cgi-bin/koha/opac-main.pl?logout.x=1" target="_blank"> <<branches.branchname>>.</a></br>



Issue


Dear         <<borrowers.title>>.<<borrowers.surname>><br>

<p>The following items have been<font color="red"> Issue:</b><br></font color><br>

<table style="width: auto; height: 45px;" border="2" TABLE BORDERCOLOR="black"   cellspacing="2" cellpadding="1">

<tr>

    <td><strong>Library Card No</td></strong>

    <td><<borrowers.cardnumber>></td>  

  </tr>

  <tr>

    <td><b>Name of the Member</td>

    <td><<borrowers.surname>></td>

  </tr>

  <tr>  

    <td><b>Barcode / Acc No</td>

    <td><<items.barcode>></td>

  </tr>

  <tr>    

    <td><b>Title</td>

    <td><<biblio.title>></td>

  </tr>

  <tr>    

    <td><b>Author</td>

    <td><<biblio.author>></td>

     </tr>

  <tr>    

    <td><b>Book Issue Date</td>

    <td><<issues.issuedate>></td>

  </tr>

  <tr>    

    <td><b>Book Due Date</td>

    <td><<issues.date_due>></td>

  </tr>

 </h2> </tr>

</table>

<b><p>Visit your account details. <a href="/cgi-bin/koha/opac-main.pl?logout.x=1" target="_blank">Click here<img src="https://look-here.gif" alt="Smiley face" width="50" height="42"></img></a></p></b>

<p><b>User Name:</b> <<borrowers.userid>></p></b>

<p><b>Password:</b> <<borrowers.userid>></p></b>


<br><p><b>This is an Automatically Generated Email.</b></br><br>


Thank you for visiting <a href="/cgi-bin/koha/opac-main.pl?logout.x=1" target="_blank"> <<branches.branchname>>.</a>

------------


Specific Book transaction history


SELECT

    borrowers.borrowernumber,

    borrowers.cardnumber,

    CONCAT(

        borrowers.firstname, ' ', borrowers.surname,

        ' (',

        '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=',

        borrowers.borrowernumber,

        '\">', 'Circulation log' , '</a>',

        ')'

    ) AS Patron,

    items.barcode AS AccNo,

    biblio.title AS Title,

    biblio.author AS Author,

    old_issues.issuedate AS issue_date,

    old_issues.date_due AS issue_date_due,

    categories.Description AS UserType,

    MIN(CASE WHEN statistics.type = 'return' THEN statistics.datetime END) AS return_date

FROM

    borrowers

    INNER JOIN statistics ON borrowers.borrowernumber = statistics.borrowernumber

    INNER JOIN items ON items.itemnumber = statistics.itemnumber

    LEFT JOIN old_issues ON old_issues.borrowernumber = borrowers.borrowernumber

                         AND old_issues.itemnumber = items.itemnumber

    LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber

    LEFT JOIN biblio ON biblio.biblionumber = biblioitems.biblionumber

    LEFT JOIN categories ON borrowers.categorycode = categories.categorycode

WHERE

    items.barcode = <<Item barcode>>

    AND statistics.type IN ('issue', 'return', 'due')

GROUP BY

    borrowers.borrowernumber,

    items.barcode,

    biblio.title,

    biblio.author,

    old_issues.issuedate,

    old_issues.date_due,

    categories.Description;


---------------


Call Number Split


SELECT

    items.barcode AS 'Barcode',

    items.itemcallnumber AS 'Full Call Number',

    SUBSTRING_INDEX(items.itemcallnumber, ' ', 1) AS 'Call Number',

    SUBSTRING_INDEX(SUBSTRING_INDEX(items.itemcallnumber, ' ', 2), ' ', -1) AS 'Year',

    SUBSTRING_INDEX(items.itemcallnumber, ' ', -1) AS 'Author'

FROM

    items

LEFT JOIN

    biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber

LEFT JOIN

    biblio ON biblioitems.biblionumber = biblio.biblionumber  

WHERE

    items.itype = <<Item type|itemtypes>>

    AND barcode BETWEEN <<Start Barcode>> AND <<End Barcode>>

    LIMIT 1000


OR TRY


SELECT

    CONCAT('Barcode: ', items.barcode, '\n',

           'Full Call Number: ', items.itemcallnumber, '\n',

           'Call Number: ', SUBSTRING_INDEX(items.itemcallnumber, ' ', 1), '\n',

           'Year: ', SUBSTRING_INDEX(SUBSTRING_INDEX(items.itemcallnumber, ' ', 2), ' ', -1), '\n',

           'Author: ', SUBSTRING_INDEX(items.itemcallnumber, ' ', -1), '\n'

    ) AS ''

FROM

    items

LEFT JOIN

    biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber

LEFT JOIN

    biblio ON biblioitems.biblionumber = biblio.biblionumber  

WHERE

    items.itype = <<Item type|itemtypes>>

    AND barcode BETWEEN <<Start Barcode>> AND <<End Barcode>>

LIMIT 1000;


-----------------


Due slip (Book front page) (Height 35.56 CM, Width 21.59 CM and Side Ruler 3.1)

Download as .csv and open with Word then print.

SELECT 

    CONCAT(

        '                     ', items.itemcallnumber, '               ', items.barcode, '\n\n',

        '                     ', LEFT(biblio.author, 45), '\n\n',

        '                 ',

        CASE 

            WHEN CHAR_LENGTH(biblio.title) > 45 THEN 

                CONCAT(LEFT(biblio.title, 45), '\n\n', '                ', LEFT(SUBSTRING(biblio.title, 46), 90))

            ELSE

                biblio.title

        END

     '\n') AS''

FROM 

    items

JOIN 

    biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber

JOIN 

    biblio ON biblio.biblionumber = biblioitems.biblionumber

WHERE 

    items.itemcallnumber IS NOT NULL

    AND CAST(items.barcode AS UNSIGNED) BETWEEN <<Start Barcode>> AND <<End Barcode>>

ORDER BY 

    CAST(items.barcode AS UNSIGNED) ASC



Grant and Non-Grant overdue report 


SELECT T2.cardnumber,

       T2.surname AS 'Name',

       T6.description AS 'Category',

       T5.barcode,

       T8.title,

       ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="A"]') AS 'Floor',

       ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="Z"]') AS 'ACCOUNT TYPE',

       T7.cardnumber AS 'Staff_Card',

       T7.surname AS 'Staff_Name',

       DATE_FORMAT(DATE(T4.timestamp), "%d/%m/%Y") AS 'Txn Date',

       LPAD(REPLACE(ROUND(T4.amount, 2), "-", ""), 8, " ") AS 'Paid'

FROM account_offsets T1

LEFT JOIN accountlines T3 ON (T3.accountlines_id = T1.credit_id)

LEFT JOIN accountlines T4 ON (T4.accountlines_id = T1.debit_id)

LEFT JOIN items T5 ON (T5.itemnumber = T4.itemnumber)

LEFT JOIN biblio T8 ON (T8.biblionumber = T5.biblionumber)

LEFT JOIN borrowers T2 ON (T2.borrowernumber = T3.borrowernumber)

LEFT JOIN borrowers T7 ON (T3.manager_id = T7.borrowernumber)

LEFT JOIN categories T6 ON (T2.categorycode = T6.categorycode)

WHERE  

    TRIM(ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="Z"]')) = TRIM(<<Account Head|AHT>>)

    AND T3.credit_type_code = "PAYMENT"

    AND T4.amount > 0

    AND DATE(T3.timestamp) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

UNION ALL

SELECT NULL AS cardnumber,

       'TOTAL' AS 'Name',

       NULL AS 'Category',

       NULL AS 'barcode',

       NULL AS 'title',

       NULL AS 'Floor',

       NULL AS 'ACCOUNT TYPE',

       NULL AS 'Staff_Card',

       NULL AS 'Staff_Name',

       NULL AS 'Txn Date',

       LPAD(REPLACE(ROUND(SUM(T4.amount), 2), "-", ""), 8, " ") AS 'Paid'

FROM account_offsets T1

LEFT JOIN accountlines T3 ON (T3.accountlines_id = T1.credit_id)

LEFT JOIN accountlines T4 ON (T4.accountlines_id = T1.debit_id)

LEFT JOIN items T5 ON (T5.itemnumber = T4.itemnumber)

LEFT JOIN biblio T8 ON (T8.biblionumber = T5.biblionumber)

LEFT JOIN borrowers T2 ON (T2.borrowernumber = T3.borrowernumber)

LEFT JOIN borrowers T7 ON (T3.manager_id = T7.borrowernumber)

LEFT JOIN categories T6 ON (T2.categorycode = T6.categorycode)

WHERE  

    TRIM(ExtractValue(T5.more_subfields_xml, '//datafield[@tag="999"]/subfield[@code="Z"]')) = TRIM(<<Account Head|AHT>>)

    AND T3.credit_type_code = "PAYMENT"

    AND T4.amount > 0

    AND DATE(T3.timestamp) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>



Comments

Post a Comment

Popular posts from this blog

Install Dspace 7.2 on Ubuntu 22.04 LTS

-
Image
  Install Dspace 7.2 on Ubuntu 22.04 LTS Install Dspace 7.2 on Ubuntu 22.04 LTS Prerequisite software : i) Java JDK ii) Apache Maven iii) Apache Ant iv) Apache Tomcat v) PostgreSQL vi) Solr vii) Dspace 7.2 – backend viii) Dspace-angular 7.2 – frontend ix) Node.js x) Node Version Manager (NVM) xi) Yarn xii) gedit (text editor software) Note: It is highly recommended to install Dspace7 on fresh installed Linux-based operating system. It may not be compatible with the Virtual box platform. (Note the red mark lines are the commands that need to be executed on the terminal) Open the terminal by pressing (Ctrl+Alt+T) or go to Applications > Accessories > Terminal and select it for executing the below command. First update and upgrade your package system sudo apt update && sudo apt upgrade -y Install gedit for text editing sudo apt-get install gedit Create a Dspace user with password sudo useradd -m dspace sudo passwd d...
Read more

How to delete library data in koha using the command line

-
 How to delete library data in koha using the command line Sometimes you need to empty the all data (e.g. bibliographic, borrowers, and transaction details)  from your Koha LMS without losing the configuration settings and authorized values, e.g. libraries, item types, patron categories, locations, etc. Let us know how to delete data from the Koha instance/library. Delete Koha's bibliographic data NB: I would recommend taking a backup of the database before applying the following commands. Login as a root user sudo su Log into MySQL database mysql -uroot -p Enter MySQL root password use koha_library; Disable foreign key constraints when you truncate the tables SET FOREIGN_KEY_CHECKS=0; Delete all bibliographic details TRUNCATE items;  TRUNCATE biblioitems;  TRUNCATE biblio;  TRUNCATE deletedbiblioitems; TRUNCATE deletedbiblio;  TRUNCATE biblio_metadata; Delete all transactions  TRUNCATE old_issues;  TRUNCATE issues;  Delete borrowers details ...
Read more

Koha INSTALLATION

-
Image
   Koha INSTALLATION After installation of ubuntu 20.4 (Focal Fossa), right click on the mouse choose open terminal. past the following comments in terminal. Update Ubuntu using apt   sudo apt update sudo apt upgrade -y if the system asks for update make it "Remind me later" it may confuse the process Clear the apt-get packager manager cache sudo apt clean Install MariaDB-server dependence sudo apt install -y mariadb-server Provide MySQL/MariaDB root password (If the password asks during the installation process, enter the password in the window. Apply the following command, if the password window did not appear during the installation secure it) Securing MariaDB Run this command to improve the security of the MariaDB installation:   sudo mysql_secure_installation   (The script will prompt you to set up the root user password, remove the anonymous user, restrict root user access to the local machine and remove the test d...
Read more

Total views