This recipe shows how to build a linked records form app. It is a step-to-step guide to reproduce a working online invoice app prototype available for download at http://code.google.com/p/invoice2py/downloads/list. For installation in production, please read the LICENSE file that ships with the app installation.
invoice2py: an invoice web interface app for web2py
Project page: http://code.google.com/p/ivoice2py
Copyright (C) 2013 Alan Etkin <spametki@gmail.com>
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Affero General Public License for more details.
You should have received a copy of the GNU Affero General Public License
along with this program. If not, see http://www.gnu.org/licenses/
Developed with web2py, by Massimo Di Pierro
A common issue in the web2py users mail list is the apparent lack of a core feature for handling forms with linked records.
When developing apps with forms that imply a little extra complexity such as invoices or other data structures that need to link one record (an invoice) to different child records (details/items), the core web2py tools need to be combined instead of being used separately. This is by all means a different situation than the absence of support for handling linked records. invoice2py (project page http://code.google.com/p/invoice2py) is an example of getting web2py tools together to build such a feature.
Anyway, note that a multi record form implementation in the web2py core would probably apply only for a given subset of possible use cases. For example, a multi-page synchronous one-to-many record forms feature would not solve the need for one-page ajax linked record forms and vice-versa.
The way around this problem is to support both (or the complete subset) of use cases in the core, adding a lot of extra code, but considering the fast paced growth of web2py built-in features between releases, linked record forms will be probably supported in further versions of the framework.
Following Andy Harris STAIR approach, we have a problem already stated, so we can now move to the next step, that is to
Gather the tools
This step is really simple, so I guess I over-emphasized the title. We only need the following built-in web2py features:
- We are going to store the current invoice selected as a session variable.
- response.menu to give quick acces the invoice commands like creating a new invoice, showing the header, details, close an ivoice etc.
- SQLFORM for the main record forms (invoice) CRUD
- SQLFORM.grid for handling item/detail CRUD linked to a given main record.
Here we do not create product/customer CRUD forms because they can be replaced with the default appadmin CRUD api (see the web2py manual for more information), however, you can add custom forms for those tables using the same tools for invoice and items.
Now, we need an
Algorithm
This is also simple, so I'm describing it in plain English (no pseudocode or flowcharts here):
- We expose a grid of created invoices to be selected. There's a general menu which will allow to create a new invoice or update a selected invoice.
- New invoices get an "open" status flag for allowing edition trough different sessions.
- The menu has a link to a details grid, that will allow to do detail/item CRUD while keeping the created or selected invoice as reference.
- When a user modifies an invoice detail/item, the app automatically updates totals for individual items and the invoice total amount as well.
- Completed (closed) or cancelled invoices are not updated.
Our next step is the actual
Implementation
The code is implemented in the scaffolding app models/db.py, models/menu.py and controllers/default.py files.
You can check the complete app code with comments at http://code.google.com/p/invoice2py/source/browse/
This is the model definition
STATUSES = {"open": T("Open"), "locked": T("Locked"),
"cancelled": T("Cancelled")}
PAYMENTS = ["cash", "credit card", "other"]
UNITS = ["units", "Kg", "g", "l"]
# The bill's recipient
db.define_table("customer",
Field("name"),
Field("taxpayer"),
format="%(name)s (%(taxpayer)s)")
# concepts can be articles, discounts or anything that affects the
# amount of the invoice.
db.define_table("concept",
Field("name"),
Field("description"),
Field("amount", "double", default=0.00),
Field("measure", requires=IS_IN_SET(UNITS),
default="units"),
format="%(name)s: $%(amount)s (%(measure)s)")
db.define_table("invoice",
Field("created_on", "datetime", default=request.now),
Field("created_by", "reference auth_user",
default=auth.user_id, writable=False),
Field("customer_id", "reference customer"),
Field("description"),
Field("total", "double",
writable=False, default=0.00),
Field("payment", requires=IS_IN_SET(PAYMENTS)),
Field("status", requires=IS_IN_SET(STATUSES),
default="open", writable=False),
format="%(id)s")
# Each invoice item will be recorded individually. This allows a more
# easy control over the invoice contents, but also requires an extra
# set of records (and a new table) in the database backend.
db.define_table("item",
Field("invoice_id", "reference invoice",
default=session.invoice_id,
writable=False),
Field("concept_id", "reference concept"),
Field("amount", "double", default=0.00),
Field("total", "double", default=0.00,
writable=False),
format=lambda record: record.concept_id.name)
And an app-wide function to update item and invoice total amounts:
def invoice_total(invoice):
if invoice.status in ("locked", "cancelled"): return
items = db(db.item.invoice_id==invoice.id).select()
total = 0.00
for item in items:
item_total = item.amount * item.concept_id.amount
item.update_record(total=item_total)
total += item_total
invoice.update_record(total=total)
We build a simple menu to manage basic invoice commands; first we need to retrieve the current invoice selected to change the menu dinamically.
response.menu = [
(T('My invoices'), False, URL('default', 'index'), [])
]
DEVELOPMENT_MENU = False
INVOICE_ID = session.invoice_id
if request.args(0) == "invoice":
INVOICE_ID = request.args(1)
# Invoice menu. It gives acces to:
# - header CRUD (shows the current invoice id)
# - details (items CRUD)
# - close (set as complete)/cancel/clear invoice actions
response.menu += [(T("Invoice No. %(number)s") % \
dict(number=INVOICE_ID or
"(%s)" % T("Create")), True,
URL(c="default", f="invoice")),
(T("Invoice details"), True,
URL(c="default", f="details"))]
if INVOICE_ID: response.menu += [(T("Clear"), True, URL(c="default",
f="status", args=["invoice", "none", "status", "clear"])),]
The business code is about four actions. First, expose and handle the invoice selection interface:
@auth.requires_login()
def index():
"""
First page. Show the list (grid) of available invoices
"""
return dict(table=SQLFORM.grid(db.invoice,
create=False, editable=False, deletable=False))
Second, an invoice header form:
@auth.requires_login()
def invoice():
""" The invoice header action
It allows to create or modify an invoice header.
Locked or cancelled operations are not editable.
"""
invoice = customer = readonly = None
# INVOICE_ID stores the currently selected invoice
if INVOICE_ID:
if request.args(1):
session.invoice_id = request.args(1)
# get the invoice db record
invoice = db.invoice[session.invoice_id]
# compute/update totals for each item and
# the invoice's total amount
if invoice: invoice_total(invoice)
# was this invoice closed? then disable editing
readonly = invoice.status in ("locked", "cancelled")
form = SQLFORM(db.invoice, session.invoice_id,
readonly=readonly)
# on invoice update disable editing too (you can keep
# updating by accessing the invoice header trough the menu)
if form.process().accepted:
response.flash = T("Done!")
form = SQLFORM(db.invoice, session.invoice_id,
readonly=True)
else:
# here we add the cancel and close (lock) actions to
# the menu.
if not readonly:
response.menu += [
(T("Close"), True, URL(f="status",
args=["invoice", session.invoice_id,
"status", "locked"])),
(T("Cancel"), True, URL(f="status",
args=["invoice", session.invoice_id,
"status", "cancelled"]))]
else:
# when there's no invoice informed (by session variable or
# action argument) we expose a create form
form = SQLFORM(db.invoice)
if form.process().accepted:
session.invoice_id = form.vars.id
session.flash = T("New invoice created")
# on sucess, self-redirect to allow editing
redirect(URL(f="invoice"))
return dict(form=form)
Third, the details grid:
@auth.requires_login()
def details():
"""
The invoice details grid.
It exposes an invoice item CRUD interface and updates totals for
each submission. It requires a session invoice variable which
is set by user input (via the menu), so you cannot edit details
unless you selected an invoice previously.
"""
if not session.invoice_id:
# return an empty page if there's no invoice selected
response.flash = T("No invoice selected!")
return dict(header=None, details=None)
else:
# get the invoice database record
invoice = db.invoice[session.invoice_id]
# update item and invoice totals (only open invoices)
invoice_total(invoice)
# is the invoice editable? then allow item CRUD.
editable = not invoice.status in ("locked", "cancelled")
details = SQLFORM.grid(db.item.invoice_id==session.invoice_id,
create=editable, editable=editable, deletable=editable)
# we add the invoice header to the action data for reference
header = SQLFORM(db.invoice, session.invoice_id, readonly=True)
return dict(header=header, details=details)
And an action for menu selection events (menu invoice commands):
@auth.requires_login()
def status():
""" This is command action to update the invoice status.
It is triggered by the other menu actions.
commands:
- clear: resets the invoice selection
(allows to create a new one)
- lock: close the invoice avoiding further modifications
(for supporting billing)
- cancel: abort the current invoice
"""
if not request.args[3] == "clear":
invoice = db.invoice[request.args[1]]
if invoice:
invoice.update_record(status=request.args[3])
session.flash = \
T("Invoice %(invoice_id)s is %(status)s") % \
dict(status=request.args[3],
invoice_id=request.args[1])
session.invoice_id = None
redirect(URL(f="index"))
Finally, since this is a simple prototype, we could add a lot of
Refinement
- There are no views coded so far. For a production app you should add views so you could control what information is exposed and it's structure. Also consider using a custom stylesheet or colors and changing the default images. If you need to create PDF invoices, there's a web2py contrib tool for that too caled FPDF.
- The model definition could be expanded or modified to support more invoice attributes like local taxpayer fields or special currency formats. It could be possible also to add more child record categories by cloning the details action for each category and linking them to different child tables.
- The business logic could be also easily enhanced by a lot of web2py and Python built-in features like email notification or remote webservice calls.
Comments (0)