Revision: 5283
Updated Code
at February 24, 2008 23:05 by stagger
Updated Code
"""SoftPRO objects""" class SalesOrder: """Sales Order containg SO items and order / commit dates""" def __init__(self, cur, code): """fetch items from SQL server and construct items""" self.code = code self.order_date = None self.commit_date = None self.items = [] # get specific sales order from server soitem_query = "SELECT t1.fsodate AS order_date, " \ "t1.fddate AS commit_date, " \ "t1.fitemno1 AS so_item " \ "FROM od20sord t1 " \ "WHERE t1.fsono LIKE '" + self.code + "' " \ "AND t1.fsodate <= t1.fddate " \ "GROUP BY t1.fsodate, t1.fddate, t1.fitemno1 " \ "ORDER BY t1.fitemno1" cur.execute(soitem_query) soitem_results = cur.fetchall() # process SQL query for soitem in soitem_results: self.order_date = soitem[0] self.commit_date = soitem[1] # create and bind SOItem objects from SQL query cur_soitem = SOItem(cur, self.code, soitem[2].strip()) if cur_soitem: self.items.append(cur_soitem) def get_opstring(self): "return op code list, one op code string for every SO item" opstrings = [] for soitem in self.items: cur_opstring = soitem.get_opstring() opstrings.append(cur_opstring) return opstrings class SOItem: """Sales Order Item containing BOM and order quantity""" def __init__(self, cur, so_code, number): """fetch data from SQL server and construct BOM""" self.number = (5-len(number))*" " + number.lstrip() self.so_code = so_code self.bom = None # get product code and order / plan quantity from SQL server query = "SELECT t1.fpdcode AS product_code, " \ " t1.fqty AS order_qty, " \ " t3.fwoqty AS plan_qty " \ "FROM od20sord t1 " \ "LEFT JOIN pd20woi1 t3 on t1.fsono = t3.forderno " \ "AND t1.fitemno1 = t3.fitemno1 " \ "AND t1.fpdcode = t3.fpdcode " \ "WHERE t1.fsono LIKE '" + self.so_code + "' " \ "AND t1.fitemno1 LIKE '" + self.number + "' " \ "AND t3.fwoqty NOT LIKE 0 AND t3.fwoqty NOT LIKE '' " \ "GROUP BY t1.fpdcode, t1.fqty, t3.fwoqty" cur.execute(query) result = cur.fetchall() if result: # because fetchall() returns a list with only 1 element: result = result[0] # process result (order quantity and BOM) self.order_qty = result[1] self.bom = BOM(result[0], result[2]) self.bom.op_fetch(cur) def get_opstring(self): """return BOM's op code string""" if self.bom: return self.bom.get_opstring() else: return "" class BOM: """BOM has plan quantity, operations and their components""" def __init__(self, product_code, quantity): """fetch operations and their materials from SQL server""" self.code = product_code self.quantity = quantity self.operations = [] def op_fetch(self, cur): # query operations op_query = "SELECT t7.fopseq AS opseq, " \ "t7.fopcode AS opcode " \ "FROM bd01stb1 t7 " \ "WHERE t7.fpdcode LIKE '" + self.code + "' " \ "GROUP BY t7.fopseq, t7.fopcode " \ "ORDER BY t7.fopseq" cur.execute(op_query) op_result = cur.fetchall() # transfer operations from results to list for op in op_result: cur_opcode = op[1] cur_opcode = cur_opcode.upper() cur_opcode = cur_opcode.strip() cur_op = {"opseq":op[0],"opcode":cur_opcode, "components":[]} # against broken "forever-recursing" BOMs if (cur_op["opcode"] == self.code): continue # query components for current operation comp_query = "SELECT t8.fopseq AS opseq, " \ "t8.fcompcode AS component, " \ "t8.fqty AS quantity " \ "FROM bd01stb2 t8 " \ "WHERE t8.fpdcode LIKE '" + self.code + "' " \ "AND t8.fopseq LIKE '" + cur_op["opseq"] + "' " \ "GROUP BY t8.fcompcode, t8.fqty, t8.fopseq " \ "ORDER BY t8.fopseq" #"AND t8.fqty NOT LIKE Null " \ #"AND t8.fqty NOT LIKE 0 " \ cur.execute(comp_query) comp_result = cur.fetchall() # build a BOM for each component in results for comp in comp_result: cur_bom = BOM(comp[1], comp[2]) cur_bom.op_fetch(cur) cur_comp = {"opseq":comp[0], "component":cur_bom} cur_op["components"].append(cur_comp) # add operation to list self.operations.append(cur_op) def get_opstring(self): "return the BOM's opcodes and all of their components' as string" opstring = "" for op in self.operations: comp_opstring = "" for comp in op["components"]: comp_opstring += " " + comp["component"].get_opstring() comp_opstring = comp_opstring.strip() if comp_opstring: opstring += " (" + comp_opstring + ")" opstring += " " + op["opcode"] return opstring.lstrip()
Revision: 5282
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 24, 2008 23:02 by stagger
Initial Code
Initial URL
Initial Description
This is the SoftPRO library needed by the GUI Collector main file.
Initial Title
GUI Collector - SoftPRO Library
Initial Tags
sql, textmate, python
Initial Language
Python