Generating monthly reports for OpenBiblio

One of the supervisors asked for a monthly report of everybody that has borrowed devices and haven’t returned them yet. You can do that manualy by going to the Reports tab and clicking on Bibliography Checkout Listing.

My plan was to figure out how to:

  • query the database so i can get the desired output
  • display/export the data into a readable format (.csv)
  • send an e-mail with an attachement
  • everything above, automated and from the command line

Quering the OpenBiblio database for checkout listing

Luckly i have found files where the report queries are being stored. These are .rpt files and can be found in OpenBiblio/reports/defs . The file of interest is checkouts.rpt :

select c.bibid, c.copyid, m.mbrid, c.barcode_nmbr,
b.title,, c.status_begin_dt,
c.due_back_dt, m.barcode_nmbr member_bcode,
concat(m.last_name, ‘, ‘, m.first_name) name
from biblio b, biblio_copy c, member m
where b.bibid = c.bibid
and c.mbrid = m.mbrid
and c.status_cd = ‘out’
.    if_set due_before
and c.due_back_dt <= %due_before%
.    end if_set
.    if_set out_since
and c.status_begin_dt >= %out_since%
.    end if_set
.    order_by_expr
.end sql

Mysql will complain if i use the above query, so i’ll modify it a bit :

SELECT c.bibid, c.copyid, m.mbrid, c.barcode_nmbr, b.title,, c.status_begin_dt, c.due_back_dt, m.barcode_nmbr member_bcode, concat( m.last_name, ‘, ‘, m.first_name ) name
FROM biblio b, biblio_copy c, member m
WHERE b.bibid = c.bibid
AND c.mbrid = m.mbrid
AND c.status_cd = ‘out’
ORDER BY c.due_back_dt DESC;

Query Mysql from the command-line without password prompt

If you remember from a previous post, you could use mysqldump without having to being prompted for the password. Same goes for Mysql – a new entry must be added in the ~/.my.cnf file :

user = openbiblio_db_username
password = openbiblio_db_password
host = localhost

Now, i have to make sure that the query actually works and displays what i want. I’ve added the above query into a .sql file and queried the database like so :

mysql < getreport.sql


Pipeing the above query to sed and outputing the result to a .csv file

mysql < getreport.sql | sed “s/’/\’/;s/\t/\”,\”/g;s/^/\”/;s/$/\”/;s/\n//g” > file.csv

Sending an email and attachement with Python

The server on which Openbiblio is production so the IT departament didn’t grant me too many permisions – that means i can’t install something that would do the job for me. I settled for short Python script that will just send an email with the subject “MITLibrary Monthly Report” and with the .csv file attached.

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email import Encoders
SUBJECT = “MITLibrary Monthly Report”
msg = MIMEMultipart()
msg[‘Subject’] = SUBJECT
msg[‘From’] = “[email protected]
msg[‘To’] = “[email protected]

part = MIMEBase(‘application’, “octet-stream”)
part.set_payload(open(“file.csv”, “rb”).read())

part.add_header(‘Content-Disposition’, ‘attachment; filename=”file.csv”‘)
server = smtplib.SMTP(“localhost”)
server.sendmail(“[email protected]”, “[email protected]”, msg.as_string())

Automating and scripting

Now that i know it’s working, i’ll return with python/bash script that will make things happen regularly.