DBFrames tutorial

Creating your first application

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.

views

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.

More complicated application

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.

Outlets Products Orders

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.

OrderDetails

When you press Order tab again you will see that Amount field is placed with sum of the detail amounts.

OrderAmount

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.

Filter

Adding actions

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.

Appendix

Source code files for this tutorial: firstapp.py config.py start.py actions.py