Posted By

stagger on 02/24/08


Tagged

sql textmate python ttm thesis


Versions (?)

GUI Collector - SoftPRO Library


 / Published in: Python
 

This is the SoftPRO library needed by the GUI Collector main file.

  1. """SoftPRO objects"""
  2.  
  3. class SalesOrder:
  4. """Sales Order containg SO items and order / commit dates"""
  5. def __init__(self, cur, code):
  6. """fetch items from SQL server and construct items"""
  7. self.code = code
  8. self.order_date = None
  9. self.commit_date = None
  10. self.items = []
  11. # get specific sales order from server
  12. soitem_query = "SELECT t1.fsodate AS order_date, " \
  13. "t1.fddate AS commit_date, " \
  14. "t1.fitemno1 AS so_item " \
  15. "FROM od20sord t1 " \
  16. "WHERE t1.fsono LIKE '" + self.code + "' " \
  17. "AND t1.fsodate <= t1.fddate " \
  18. "GROUP BY t1.fsodate, t1.fddate, t1.fitemno1 " \
  19. "ORDER BY t1.fitemno1"
  20. cur.execute(soitem_query)
  21. soitem_results = cur.fetchall()
  22. # process SQL query
  23. for soitem in soitem_results:
  24. self.order_date = soitem[0]
  25. self.commit_date = soitem[1]
  26. # create and bind SOItem objects from SQL query
  27. cur_soitem = SOItem(cur, self.code, soitem[2].strip())
  28. if cur_soitem:
  29. self.items.append(cur_soitem)
  30.  
  31. def get_opstring(self):
  32. "return op code list, one op code string for every SO item"
  33. opstrings = []
  34. for soitem in self.items:
  35. cur_opstring = soitem.get_opstring()
  36. opstrings.append(cur_opstring)
  37. return opstrings
  38.  
  39.  
  40. class SOItem:
  41. """Sales Order Item containing BOM and order quantity"""
  42. def __init__(self, cur, so_code, number):
  43. """fetch data from SQL server and construct BOM"""
  44. self.number = (5-len(number))*" " + number.lstrip()
  45. self.so_code = so_code
  46. self.bom = None
  47. # get product code and order / plan quantity from SQL server
  48. query = "SELECT t1.fpdcode AS product_code, " \
  49. " t1.fqty AS order_qty, " \
  50. " t3.fwoqty AS plan_qty " \
  51. "FROM od20sord t1 " \
  52. "LEFT JOIN pd20woi1 t3 on t1.fsono = t3.forderno " \
  53. "AND t1.fitemno1 = t3.fitemno1 " \
  54. "AND t1.fpdcode = t3.fpdcode " \
  55. "WHERE t1.fsono LIKE '" + self.so_code + "' " \
  56. "AND t1.fitemno1 LIKE '" + self.number + "' " \
  57. "AND t3.fwoqty NOT LIKE 0 AND t3.fwoqty NOT LIKE '' " \
  58. "GROUP BY t1.fpdcode, t1.fqty, t3.fwoqty"
  59. cur.execute(query)
  60. result = cur.fetchall()
  61. if result:
  62. # because fetchall() returns a list with only 1 element:
  63. result = result[0]
  64. # process result (order quantity and BOM)
  65. self.order_qty = result[1]
  66. self.bom = BOM(result[0], result[2])
  67. self.bom.op_fetch(cur)
  68.  
  69. def get_opstring(self):
  70. """return BOM's op code string"""
  71. if self.bom:
  72. return self.bom.get_opstring()
  73. else:
  74. return ""
  75.  
  76.  
  77. class BOM:
  78. """BOM has plan quantity, operations and their components"""
  79. def __init__(self, product_code, quantity):
  80. """fetch operations and their materials from SQL server"""
  81. self.code = product_code
  82. self.quantity = quantity
  83. self.operations = []
  84.  
  85. def op_fetch(self, cur):
  86. # query operations
  87. op_query = "SELECT t7.fopseq AS opseq, " \
  88. "t7.fopcode AS opcode " \
  89. "FROM bd01stb1 t7 " \
  90. "WHERE t7.fpdcode LIKE '" + self.code + "' " \
  91. "GROUP BY t7.fopseq, t7.fopcode " \
  92. "ORDER BY t7.fopseq"
  93. cur.execute(op_query)
  94. op_result = cur.fetchall()
  95. # transfer operations from results to list
  96. for op in op_result:
  97. cur_opcode = op[1]
  98. cur_opcode = cur_opcode.upper()
  99. cur_opcode = cur_opcode.strip()
  100. cur_op = {"opseq":op[0],"opcode":cur_opcode, "components":[]}
  101. # against broken "forever-recursing" BOMs
  102. if (cur_op["opcode"] == self.code):
  103. continue
  104. # query components for current operation
  105. comp_query = "SELECT t8.fopseq AS opseq, " \
  106. "t8.fcompcode AS component, " \
  107. "t8.fqty AS quantity " \
  108. "FROM bd01stb2 t8 " \
  109. "WHERE t8.fpdcode LIKE '" + self.code + "' " \
  110. "AND t8.fopseq LIKE '" + cur_op["opseq"] + "' " \
  111. "GROUP BY t8.fcompcode, t8.fqty, t8.fopseq " \
  112. "ORDER BY t8.fopseq"
  113. #"AND t8.fqty NOT LIKE Null " \
  114. #"AND t8.fqty NOT LIKE 0 " \
  115. cur.execute(comp_query)
  116. comp_result = cur.fetchall()
  117. # build a BOM for each component in results
  118. for comp in comp_result:
  119. cur_bom = BOM(comp[1], comp[2])
  120. cur_bom.op_fetch(cur)
  121. cur_comp = {"opseq":comp[0], "component":cur_bom}
  122. cur_op["components"].append(cur_comp)
  123. # add operation to list
  124. self.operations.append(cur_op)
  125.  
  126. def get_opstring(self):
  127. "return the BOM's opcodes and all of their components' as string"
  128. opstring = ""
  129. for op in self.operations:
  130. comp_opstring = ""
  131. for comp in op["components"]:
  132. comp_opstring += " " + comp["component"].get_opstring()
  133. comp_opstring = comp_opstring.strip()
  134. if comp_opstring:
  135. opstring += " (" + comp_opstring + ")"
  136. opstring += " " + op["opcode"]
  137. return opstring.lstrip()

Report this snippet  

You need to login to post a comment.