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

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

How to Create Barcode Labels in Koha

-
 Create Barcode Labels in Koha Here we will create the barcode which can be printed on the A4 size ordinary bond paper or A4 size sheet with 65 (5x13) self-adhesive labels. Create a Label Layout First, we need to create a layout for the barcode label. A layout is used to define the fields that you want to print on the label. To create the layout go to:- Koha Home➤ Tools➤Label creater➤New➤ Layout Enter the following details 1. Layout name :- Enter the Name (I have just entered MANC Final) 2. Choose Barcode type :- Code 39 (commonly used) 3. Choose Layout Type :- Bibliographic data proceeds barcode (Library Name on the top and barcode would be below) 4. Bibliographic data :- Here we want to print the library name above the barcode, So I choose order number 1 for the homebranch_description remaining fields left blank. 5. Text Justificatification :- Center 6. Font:-  Times-Roman (You can choose other fonts also) 7. Font Size :- 8 Now save the layout Create a Label Template Koha H...
Read more

Upload files on Koha server

-
   Upload files on Koha server Koha allows you to upload digital files e.g. PDF, text, image, video, etc. on the Koha server. All the uploaded files will be stored in the internal directory of Koha, and files can be retrieved from the staff interface as well as on OPAC.  Configuration First I recommend creating categories to upload the files. If you don't want to create more categories, even you can create only one that will contain all the files.  While uploading the new file you need to select the category otherwise your upload is marked as  temporary  and will be removed when your server restarts.  To start this process, you need to add new authorized values to the  "Upload". Create new Authorized values under the  UPLOAD  category Koha Tools ➤Authorized values 1. Search for  Upload 2. Click on the  Add  button Fill in the detail and Save Upload files Go to  Koha Tools ➤ Upload Browse and Choose the file  Sele...
Read more

Total views