ManyToManyBizobjExample: customers.py

# -*- coding: utf-8 -*-

from base import Base
from productlines import ProdCust_Cust


ACCOUNT_PROXY_STRING = "< automatic >"

DEFAULT_CUSTOMER_NAME = "< New Customer >"
DEFAULT_TERMS = "Net 10"


class Customers(Base):
    def initProperties(self):
        self.super()
        self._module_id = self.Application.constants.modules.MODULE_ID_CUSTOMERS
        self.Caption = "Customers"
        self.DataSource = "customers"
        self.DataStructure = (
                # (field_alias, field_type, pk, table_name, field_name, field_scale)
                ("id", "C", True, "customers", "id"),
                ("account", "C", False, "customers", "account"),
                ("name", "C", False, "customers", "name"),
                ("attention", "C", False, "customers", "attention"),
                ("address1", "C", False, "customers", "address1"),
                ("address2", "C", False, "customers", "address2"),
                ("address3", "C", False, "customers", "address3"),
                ("city", "C", False, "customers", "city"),
                ("state", "C", False, "customers", "state"),
                ("zip", "C", False, "customers", "zip"),
                ("country", "C", False, "customers", "country"),
                ("phone", "C", False, "customers", "phone"),
                ("fax", "C", False, "customers", "fax"),
                ("cell", "C", False, "customers", "cell"),
                ("url", "C", False, "customers", "url"),
                ("email", "C", False, "customers", "email"),
                ("terms", "C", False, "customers", "terms"),
                ("tax_id", "C", False, "customers", "tax_id"),
                ("notes", "M", False, "customers", "notes"),
                )

        self.DefaultValues["account"] = ACCOUNT_PROXY_STRING
        self.DefaultValues["name"] = DEFAULT_CUSTOMER_NAME
        self.DefaultValues["terms"] = DEFAULT_TERMS
        self.KeyField = "id"

        self.bizProdCust = ProdCust_Cust(self.Application.dbConnection)
        self.addChild(self.bizProdCust)

        if self.Application.showCustomerCategories():
            self.bizCustomerCategory = CustomerCategory(self.Application.dbConnection)
            self.addChild(self.bizCustomerCategory)


    def getNextAccountNumber(self):
        return str(self.Application.getNextNumber("customers.account"))


    def setAccount(self, account_number):
        self.Record.account = account_number

    def afterNew(self):
        self.bizProdCust.new()
        self.bizProdCust.Record.prod_id = self.Application.constants.DEFAULT_PRODUCT_LINE_ID
        self.bizProdCust.fillDerivedFields()

    def onSaveNew(self):
        if self.Record.account == ACCOUNT_PROXY_STRING:
            self.Record.account = self.getNextAccountNumber()
            self.save()


    def setBaseSQL(self):
        self.addFrom("customers")
        self.addFieldsFromDataStructure()
        self.addGroupBy("customers.id")
        self.addOrderBy("customers.name")
                

class CustomerCategories(Base):
    def initProperties(self):
        self.super()
        self._module_id = self.Application.constants.modules.MODULE_ID_CUSTOMER_CATEGORIES
        self.Caption = "Customer Categories"
        self.DataSource = "customer_categories"
        self.DataStructure = (
                # (field_alias, field_type, pk, table_name, field_name, field_scale)
                ("id", "C", True, "customer_categories", "id"),
                ("name", "C", False, "customer_categories", "name"),
                ("notes", "M", False, "customer_categories", "notes"),)
        self.KeyField = "id"


    def setBaseSQL(self):
        self.addFrom("customer_categories")
        self.addFieldsFromDataStructure()               
        self.addGroupBy("customer_categories.id")
        self.addOrderBy("customer_categories.name")



class CustomerCategory(Base):
    def initProperties(self):
        self.super()
        self.Caption = "Customer Category"
        self.DataSource = "customer_category"
        self.DataStructure = (
                # (field_alias, field_type, pk, table_name, field_name, field_scale)
                ("id", "C", True, "customer_category", "id"),
                ("customer_id", "C", False, "customer_category", "customer_id"),
                ("category_id", "C", False, "customer_category", "category_id"),
                ("category_name", "C", False, "customer_categories", "name"))
        self.KeyField = "id"
        self.LinkField = "customer_id"
        self.FillLinkFromParent = True


    def setBaseSQL(self):
        self.super()
        self.setFromClause("""customer_category
 left join customer_categories
   on customer_categories.id = customer_category.category_id""")
        self.addFieldsFromDataStructure()
        self.addGroupBy("customer_category.id")


    def addLink(self, category_id):
        ds = self.getDataSet()
        if ds:
            results = ds.execute("""
select count(*) as exist_count
  from dataset
 where customer_id = '%s'
   and category_id = '%s'
""" % (self.Parent.getPK(), category_id))

            if results and results[0]["exist_count"] > 0:
                # already linked: ignore
                return
        results = self.executeSafe("""
select name 
  from customer_categories 
 where id = "%s" """ % category_id)
        try:
            category_name = results.Record.name
        except:
            category_name = category_id
        self.new()
        self.Record.customer_id = self.Parent.getPK()
        self.Record.category_id = category_id
        self.Record.category_name = category_name