Before you try using DBFrames on your WinCE device, it's recommended to install Python, ppygui-win32 and DBFrames on your desktop or laptop. If your application will work fine on desktop, you can copy it on the handheld.
Create file firstapp.py and put the following code into it.
import dbframes as D
class Products(D.DBFrame):
""" This class creates tb_products table with fields f_code_prod,
f_name, f_price."""
tablename = "tb_products"
title = "Products"
columns = (
{"name": "f_code_prod", "type": D.INTEGER, "title": "Code"},
{"name": "f_name", "type": D.TEXT, "title": "Name"},
{"name": "f_price", "type": D.REAL, "title": "Price",
"default": lambda self: 0,},
)
# Only one class to initialize and put into the application menu
D.G.initorder = (Products,)
D.G.menuorder = (Products,)
D.start()
Start this by python firstapp.py and you will see the application window.
Each DBFrame object has two view modes - List and Form. But only Form view allows data editing. To add new product entry press + button, enter product code, name, price and pess OK. Also don't forget to press OK to write changes to database after any data editing.
Let's assume you need a mobile application to gather sale orders from your retail outlets. So you have to create four DBFrame classes: Outlets, Products, Orders and OrderDetails.
First of all, create the separate configuration file where you will define your custom classes. Put the following code into the file config.py:
import datetime
import dbframes as D
# Database file name
D.G.dbfilename = "orders.db"
# Define the application title
D.G.lstr["Application Title"] = "Sale Orders"
class Outlets(D.DBFrame):
tablename = "tb_outlets"
title = "Outlets"
columns = (
{"name": "f_code_id", "type": D.INTEGER, "title": "Code",
"default": lambda self: 0,},
{"name": "f_name", "type": D.TEXT, "title": "Name",
"default": lambda self: '',},
)
class Products(D.DBFrame):
tablename = "tb_products"
title = "Products"
columns = (
{"name": "f_code_prod", "type": D.INTEGER, "title": "Code",
"default": lambda self: 0,},
{"name": "f_name", "type": D.TEXT, "title": "Name",
"default": lambda self: '',},
{"name": "f_price", "type": D.REAL, "title": "Price",
"default": lambda self: 0,},
)
class OrderDetails(D.DBFrame):
tablename = "tb_order_details"
title = u"OrderDetails"
columns = (
{"name": "f_code_order", "type": D.INTEGER, "title": u"Code",
"show": False},
{"name": "f_code_prod", "type": D.INTEGER, "title": u"Product",
"default": lambda self: 0,
"foreign":
{
"dbframe": Products,
"key_fld": "f_code_prod",
"str_fld": "f_name",
}
},
{"name": "f_num", "type": D.INTEGER, "title": u"Quantity",
"default": lambda self: 0},
{"name": "f_sum", "type": D.REAL, "title": u"Amount",
"default": lambda self: 0,
"readonly": lambda self: True},
{"name": "f_price", "type": D.REAL, "title": u"Price",
"default": lambda self: 0,
"readonly": lambda self: True},
)
total = "f_sum"
def on_ok(self):
# Save price and amount
self.set_edit_value("f_price",
D.G.objects[Products].get_by_value("f_code_prod",
self.get_edit_value("f_code_prod"), "f_price"))
self.set_edit_value("f_sum",
self.get_edit_value("f_price") * self.get_edit_value("f_num"))
class Orders(D.DBFrame):
tablename = "tb_orders"
title = "Orders"
columns = (
{"name": "f_code_id", "type": D.INTEGER, "title": "No",
"constr": lambda self: "PRIMARY KEY AUTOINCREMENT",
"readonly": lambda self: True},
{"name": "f_date", "type": D.DATE, "title": "Date",
"default": lambda self: datetime.date.today(),
"filter": lambda self: ("=", datetime.date.today())
},
{"name": "f_code_outlet", "type": D.INTEGER, "title": "Outlet",
"default": lambda self: 0,
"foreign":
{
"dbframe": Outlets,
"key_fld": "f_code_id",
"str_fld": "f_name",
},
"filter": lambda self: None},
{"name": "f_sum", "type": D.REAL, "title": u"Amount",
"default": lambda self: 0,
"readonly": lambda self: True,
"filter": lambda self: None},
)
total = "f_sum"
orderby = lambda self: [("f_code_id", "asc"),]
detail = (OrderDetails, "f_code_order")
def on_ok(self):
# Calculate total amount
self.get_edit("f_sum").text = self.details.sum_col("f_sum",False,True)
# Check outlet selection
if self.get_edit_value("f_code_outlet") == 0:
ret = D.Message.ok(u"Warning",
u"Outlet not selected",
"warning", self)
# Application objects in initialization order.
# 'Products' and 'Outlets' are joined as 'foreign' to other objects, so they
# must be initialized first than objects they joined to.
D.G.initorder = (Products, Outlets, Orders, OrderDetails)
# Application objects in menu and notebook position order
D.G.menuorder = (Orders, OrderDetails, Products, Outlets)
Also create the file start.py to use config.py file:
import dbframes
from config import *
dbframes.start()
Run python start.py and add entris to Products and Outlets as described in first application.
To make sale order with multiple detailed entries, add an entry to Order, press OK button, select OrderDetails tab and add one or more order detail entries.
When you press Order tab again you will see that Amount field is placed with sum of the detail amounts.
The number between navigation buttons and filter button on Order tab means total sum of sale orders on chosen date as defined in class Order in line "filter": lambda self: ("=", datetime.date.today()) You can press F button and choose other date for filter.
Paste the following code to the file actions.py.
import ppygui.api as gui
import dbframes as D
class Totals(D.ActionDialog):
def __init__(self, parent, title):
D.ActionDialog.__init__(self, parent, title)
self.dctrl = gui.Date(self)
self.dctrl.value = datetime.date.today()
self.bclean = gui.Button(self, "Calculate totals on date",
action=self.on_click_bclean)
sizer = gui.VBox((2,2,2,2), spacing=2)
sizer.add(self.dctrl)
sizer.add(self.bclean)
self.sizer = sizer
def on_click_bclean(self, event):
totals = (
{"text": "Number of orders: %s\n",
"sql": "select count(*) from tb_orders where f_date=? and f_sum > 0",
"result": 0,
},
{"text": "Total amount of orders: %s\n",
"sql": "select sum(f_sum) from tb_orders where f_date=?",
"result": 0,
},
)
c = self.dbconn.cursor()
messtxtl = []
for t in totals:
c.execute(t["sql"], (self.dctrl.value,))
r = c.fetchone()
if r != None:
if r[0] != None:
t["result"] = r[0]
messtxtl.append(t["text"] % t["result"])
ret = gui.Message.ok("Message", "".join(messtxtl), "info", self)
class RemoveOldOrders(D.ActionDialog):
def __init__(self, parent, title):
D.ActionDialog.__init__(self, parent, title)
self.dctrl = gui.Date(self)
self.dctrl.value = datetime.date.today()
self.bclean = gui.Button(self, "Delete all orders before the date",
action=self.on_click_bclean)
sizer = gui.VBox((2,2,2,2), spacing=2)
sizer.add(self.dctrl)
sizer.add(self.bclean)
self.sizer = sizer
def on_click_bclean(self, event):
from config import Orders, OrderDetails
if self.objects[Orders].crow_edit or self.objects[OrderDetails].crow_edit:
ret = gui.Message.ok("Message",
"There is an unsaved order. Save it before orders deleting",
"warning", self)
return
ret = gui.Message.yesno("Warning",
"ALL orders before %s will be deleted. Continue?" % self.dctrl.value,
"warning", self)
if ret == "yes":
c = self.dbconn.cursor()
sql = "select f_code_id from tb_orders where f_date < ?"
c.execute(sql, (self.dctrl.value,))
ordrs = c.fetchall()
sql = "delete from tb_order_details where f_code_order=?"
c.executemany(sql, ordrs)
sql = "delete from tb_orders where f_code_id=?"
c.executemany(sql, ordrs)
self.dbconn.commit()
rc = c.rowcount
if rc < 0:
rc = 0
ret = gui.Message.ok("Message",
"Deleting completed.\nOrders deleted: %s.\n" % rc,
"info", self)
self.objects[Orders].listform.fill_with_data()
self.objects[OrderDetails].listform.fill_with_data()
And add this code to the end of config.py:
# Actions
import actions
D.G.actions = (
("Totals", actions.Totals),
("Delete old orders", actions.RemoveOldOrders),
)
Start the application and press Action button. You will see pop-up menu with two items: Totals and Delete old orders. Totals calculates number of orders per chosen date and total amount of them. Delete old orders allows batch deleting orders with date earlier than chosen.
Source code files for this tutorial: firstapp.py config.py start.py actions.py