My experience in developing an extension to LedgerSMB
Brian Wolf
Activus Technologies
Overview
----------
Adding sales invoices programmatically is an enormously valuable extension to LedgerSMB
and provides a huge boost to productivity. In our particular situation, we've built
an automated process to pull orders from Amazon and add sales invoices to LedgerSMB
along with associated payments. Having navigated Amazon's complex API, we may
offer an Amazon pull as a service in the near future. Meanwhile, let me describe
some of my experience in adding sales invoices in hope of helping someone else
running LedgerSMB.
Technical
----------
Our LedgerSMB extension is built on Python 2.7, a versatile object-oriented programming
language, and SQLAlchemy 0.8, an object-relational mapper (ORM) which simplifies
interactions with a database (PostgreSQL).
Everything in Python is an object. An integer is an object; a string is an object; an
instance of a user-defined class is, well, an object. Let's go over a few useful, oft-used
Python objects and methods.
Attributes or methods of an object created in Python can be addressed with simple dot notation.
For example, suppose a Python object named eca represents a row in the entity_credit_account
table. The pay_to_name column can be addressed like this:
eca.pay_to_name
The following illustrates creating a string and then calling a method on the resulting object:
joe = "J. Jonah Jameson"
joe.upper()
A Python list is an ordered, indexable array of objects.
Python makes use of a non-ordered associative array called a dictionary. A group of customers
could be stored in a dictionary, and a specific customer with 'A783X2' as its key would be
addressed like this:
customers['A783X2']
A query can be built in SQLAlchemy easily with Python objects. The first step is to define
a Python class for each table in the LedgerSMB database. Below is an example of a Python
class representing the country table. The class inherits from the Base class. Each column
is defined on a separate line.
class Country(Base):
__tablename__ = 'country'
id = Column(Integer, primary_key=True)
name = Column(Text)
short_name = Column(Text)
itu = Column(Text)
Let's say you have a session object which provides connectivity to the database. The next step
is to define a query. Here's an example of looking up the United States:
usa = session.query(Country).filter(Country.short_name == 'US').first()
print usa.id, usa.name
The results of the print command:
232, United States
SQLAlchemy let's you daisy-chain query components together, a really useful feature. That's because
most methods return a query object. The query above could have been written in three steps like this:
qry = session.query(Country)
qry = qry.filter(Country.short_name == 'US')
usa = qry.first()
Adding a Sales Order
----------
A sales order involves a few components:
- an order (table: oe)
- items on the order (tables: orderitems, parts)
- customer's credit account (table: entity_credit_account)
One approach is to define two methods: one for the order itself and one for the items
on the order. Your code can iterate through external data, adding orders; plus an inner
loop to add items to each order. Below is an example of adding a sales order.
Note: In Python, None is a keyword which has a rough analogy to null in SQL.
def add_order(eca, order):
oe = session.query(OE).filter(OE.ordnumber == order.external_order_number).first()
if oe is None:
oe = OE()
oe.ordnumber = order.order_id
oe.entity_id = eca.entity_id
oe.amount = order.amount
oe.netamount = order.amount
oe.taxincluded = False
oe.notes = 'some notes go here...'
oe.entity_credit_account = eca.id
oe.oe_class_id = 1
oe.language_code = 'en_US'
session.add(oe)
session.commit()
return oe
Armed with the methods defined above, we can iterate through inbound order data culled from
an external source and create orders (and perhaps customers, too). Something like this:
for order in external_orders:
oe = add_order(eca, order)
external_items = get_items(order)
for item in external_items:
oe_item = add_item_to_order(oe, item)
Brian Wolf
Phone: 410.367.2958
Email: ..hidden..
Try out Activus
Secure Payments™, our recurring payments
application.
Demo at http://demo.activustech.com