"""
Author: Vasudev Ram
Copyright 2014 Vasudev Ram - www.dancingbison.com
This program is a demo of how to use the PyDAL and xtopdf Python libraries
together to publish database data to PDF.
PyDAL is at: https://github.com/web2py/pydal/blob/master/README.md
xtopdf is at: https://bitbucket.org/vasudevram/xtopdf
and info about xtopdf is at: http://slides.com/vasudevram/xtopdf or
at: http://slid.es/vasudevram/xtopdf
"""
# imports
from pydal import DAL, Field
from PDFWriter import PDFWriter
SEP = 60
# create the database
db = DAL('sqlite://house_depot.db')
# define the table
db.define_table('furniture', \
Field('id'), Field('name'), Field('quantity'), Field('unit_price')
)
# insert rows into table
items = ( \
(1, 'chair', 40, 50),
(2, 'table', 10, 300),
(3, 'cupboard', 20, 200),
(4, 'bed', 30, 400)
)
for item in items:
db.furniture.insert(id=item[0], name=item[1], quantity=item[2], unit_price=item[3])
# define the query
query = db.furniture
# the above line shows an interesting property of PyDAL; it seems to
# have some flexibility in how queries can be defined; in this case,
# just saying db.table_name tells it to fetch all the rows
# from table_name; there are other variations possible; I have not
# checked out all the options, but the ones I have seem somewhat
# intuitive.
# run the query
rows = db(query).select()
# setup the PDFWriter
pw = PDFWriter('furniture.pdf')
pw.setFont('Courier', 10)
pw.setHeader(' House Depot Stock Report - Furniture Division '.center(60))
pw.setFooter('Generated by xtopdf: http://google.com/search?q=xtopdf')
pw.writeLine('=' * SEP)
field_widths = (5, 10, 10, 12, 10)
# print the header row
pw.writeLine(''.join(header_field.center(field_widths[idx]) for idx, header_field in enumerate(('#', 'Name', 'Quantity', 'Unit price', 'Price'))))
pw.writeLine('-' * SEP)
# print the data rows
for row in rows:
# methinks the writeLine argument gets a little long here ...
# the first version of the program was taller but thinner :)
pw.writeLine(''.join(str(data_field).center(field_widths[idx]) for idx, data_field in enumerate((row['id'], row['name'], row['quantity'], row['unit_price'], int(row['quantity']) * int(row['unit_price'])))))
pw.writeLine('=' * SEP)
pw.close()
