Posted By

stagger on 02/24/08


Tagged

sql textmate python ttm thesis


Versions (?)

SoftPRO Data Collector


 / Published in: Python
 

  1. import sys
  2. import pymssql
  3. import csv
  4.  
  5. """SoftPRO objects"""
  6. class SalesOrder:
  7. """Sales Order containg SO items and order / commit dates"""
  8. def __init__(self, cur, code):
  9. """fetch items from SQL server and construct items"""
  10. self.code = code
  11. self.order_date = None
  12. self.commit_date = None
  13. self.items = []
  14. # get specific sales order from server
  15. soitem_query = "SELECT t1.fsodate AS order_date, " \
  16. "t1.fddate AS commit_date, " \
  17. "t1.fitemno1 AS so_item " \
  18. "FROM od20sord t1 " \
  19. "WHERE t1.fsono LIKE '" + self.code + "' " \
  20. "AND t1.fsodate <= t1.fddate " \
  21. "GROUP BY t1.fsodate, t1.fddate, t1.fitemno1 " \
  22. "ORDER BY t1.fitemno1"
  23. cur.execute(soitem_query)
  24. soitem_results = cur.fetchall()
  25. # process SQL query
  26. for soitem in soitem_results:
  27. self.order_date = soitem[0]
  28. self.commit_date = soitem[1]
  29. # create and bind SOItem objects from SQL query
  30. cur_soitem = SOItem(cur, self.code, soitem[2].strip())
  31. if cur_soitem:
  32. self.items.append(cur_soitem)
  33.  
  34. def get_opstring(self):
  35. "return op code list, one op code string for every SO item"
  36. opstrings = []
  37. for soitem in self.items:
  38. cur_opstring = soitem.get_opstring()
  39. opstrings.append(cur_opstring)
  40. return opstrings
  41.  
  42.  
  43. class SOItem:
  44. """Sales Order Item containing BOM and order quantity"""
  45. def __init__(self, cur, so_code, number):
  46. """fetch data from SQL server and construct BOM"""
  47. self.number = (5-len(number))*" " + number.lstrip()
  48. self.so_code = so_code
  49. self.bom = None
  50. # get product code and order / plan quantity from SQL server
  51. query = "SELECT t1.fpdcode AS product_code, " \
  52. " t1.fqty AS order_qty, " \
  53. " t3.fwoqty AS plan_qty " \
  54. "FROM od20sord t1 " \
  55. "LEFT JOIN pd20woi1 t3 on t1.fsono = t3.forderno " \
  56. "AND t1.fitemno1 = t3.fitemno1 " \
  57. "AND t1.fpdcode = t3.fpdcode " \
  58. "WHERE t1.fsono LIKE '" + self.so_code + "' " \
  59. "AND t1.fitemno1 LIKE '" + self.number + "' " \
  60. "AND t3.fwoqty NOT LIKE 0 AND t3.fwoqty NOT LIKE '' " \
  61. "GROUP BY t1.fpdcode, t1.fqty, t3.fwoqty"
  62. cur.execute(query)
  63. result = cur.fetchall()
  64. if result:
  65. # because fetchall() returns a list with only 1 element:
  66. result = result[0]
  67. # process result (order quantity and BOM)
  68. self.order_qty = result[1]
  69. self.bom = BOM(result[0], result[2])
  70. self.bom.op_fetch(cur)
  71.  
  72. def get_opstring(self):
  73. """return BOM's op code string"""
  74. if self.bom:
  75. return self.bom.get_opstring()
  76. else:
  77. return ""
  78.  
  79.  
  80. class BOM:
  81. """BOM has plan quantity, operations and their components"""
  82. def __init__(self, product_code, quantity):
  83. """fetch operations and their materials from SQL server"""
  84. self.code = product_code
  85. self.quantity = quantity
  86. self.operations = []
  87.  
  88. def op_fetch(self, cur):
  89. # query operations
  90. op_query = "SELECT t7.fopseq AS opseq, " \
  91. "t7.fopcode AS opcode " \
  92. "FROM bd01stb1 t7 " \
  93. "WHERE t7.fpdcode LIKE '" + self.code + "' " \
  94. "GROUP BY t7.fopseq, t7.fopcode " \
  95. "ORDER BY t7.fopseq"
  96. cur.execute(op_query)
  97. op_result = cur.fetchall()
  98. # transfer operations from results to list
  99. for op in op_result:
  100. cur_opcode = op[1]
  101. cur_opcode = cur_opcode.upper()
  102. cur_opcode = cur_opcode.strip()
  103. cur_op = {"opseq":op[0],"opcode":cur_opcode, "components":[]}
  104. # against broken "forever-recursing" BOMs
  105. if (cur_op["opcode"] == self.code):
  106. continue
  107. # query components for current operation
  108. comp_query = "SELECT t8.fopseq AS opseq, " \
  109. "t8.fcompcode AS component, " \
  110. "t8.fqty AS quantity " \
  111. "FROM bd01stb2 t8 " \
  112. "WHERE t8.fpdcode LIKE '" + self.code + "' " \
  113. "AND t8.fopseq LIKE '" + cur_op["opseq"] + "' " \
  114. "GROUP BY t8.fcompcode, t8.fqty, t8.fopseq " \
  115. "ORDER BY t8.fopseq"
  116. #"AND t8.fqty NOT LIKE Null " \
  117. #"AND t8.fqty NOT LIKE 0 " \
  118. cur.execute(comp_query)
  119. comp_result = cur.fetchall()
  120. # build a BOM for each component in results
  121. for comp in comp_result:
  122. cur_bom = BOM(comp[1], comp[2])
  123. cur_bom.op_fetch(cur)
  124. cur_comp = {"opseq":comp[0], "component":cur_bom}
  125. cur_op["components"].append(cur_comp)
  126. # add operation to list
  127. self.operations.append(cur_op)
  128.  
  129. def get_opstring(self):
  130. "return the BOM's opcodes and all of their components' as string"
  131. opstring = ""
  132. for op in self.operations:
  133. comp_opstring = ""
  134. for comp in op["components"]:
  135. comp_opstring += " " + comp["component"].get_opstring()
  136. comp_opstring = comp_opstring.strip()
  137. if comp_opstring:
  138. opstring += " (" + comp_opstring + ")"
  139. opstring += " " + op["opcode"]
  140. return opstring.lstrip()
  141.  
  142.  
  143. class MainApp:
  144. """This is the main application."""
  145. def __init__(self, min_year=2001, max_year=2001, verbose=False, print_so=False, step=100, basename="opcode_qty", segment=""):
  146. self.verbose = verbose
  147. self.print_so = print_so
  148. self.step = step
  149. self.basename = basename
  150. self.segment = segment
  151. self.min_year = min_year
  152. self.max_year = max_year
  153. sql_host = "server ip"
  154. # on Mac we need IP _and_ port number:
  155. if (sys.platform == "darwin"):
  156. sql_host += ":1433"
  157. con = pymssql.connect(host=sql_host,
  158. user='user name',
  159. password='password',
  160. database='TTM')
  161. self.cur = con.cursor()
  162. self.build_years()
  163.  
  164. def build_opdic(self):
  165. """build dictionary of op codes from SOs with accumulated plan quantities"""
  166. opdic = {}
  167. # get all sales orders from SQL server
  168. so_codes_query = "SELECT t1.fsono " \
  169. "FROM od20sord t1 " \
  170. "WHERE t1.fitemno1 LIKE ' 1' " \
  171. "AND t1.fsodate <= t1.fddate " \
  172. "AND DatePart(yyyy, t1.fddate) >= " + str(self.min_year) + " " \
  173. "AND DatePart(yyyy, t1.fddate) <= " + str(self.max_year) + " "
  174. if self.segment:
  175. so_codes_query += "AND t1.fpdcode LIKE '" + self.segment + "%' "
  176. so_codes_query += "GROUP BY t1.fsono " \
  177. "ORDER BY t1.fsono"
  178. self.cur.execute(so_codes_query)
  179. sorder_codes = self.cur.fetchall()
  180. position = 0
  181. if self.verbose:
  182. print "Starting to build a op code dictionary out of %d SQL data rows." % len(sorder_codes)
  183. print "This could take some time..."
  184. # put sales orders in dictionary
  185. for sorder_code in sorder_codes:
  186. position += 1
  187. # for debugging
  188. if self.verbose and (position % self.step == 0):
  189. print "Progress: %d / %d" % (position, len(sorder_codes))
  190. # create sales order object and iterate through items
  191. sorder = SalesOrder(self.cur, sorder_code[0])
  192. for soitem in sorder.items:
  193. bom = soitem.bom
  194. if bom:
  195. opstring = soitem.get_opstring()
  196. qty = int(soitem.bom.quantity)
  197. if opstring:
  198. if not opdic.has_key(opstring):
  199. opdic[opstring] = qty
  200. else:
  201. opdic[opstring] += qty
  202. if self.print_so:
  203. print "%s:%s (%6d) => %s" % (sorder_code[0],
  204. soitem.number.lstrip(),
  205. soitem.bom.quantity,
  206. opstring)
  207. # for debugging
  208. if self.verbose:
  209. print "Finished! The dictionary has %d entries." % len(opdic)
  210. return opdic
  211.  
  212. def opdic2csv(self, opdic, filename):
  213. """write opdic to CSV file"""
  214. writer = csv.writer(file(filename, "w"),
  215. dialect="excel",
  216. delimiter=";")
  217. position = 0
  218. if self.verbose:
  219. print "Writing dictionary to file: " + filename
  220. for op, quantity in opdic.items():
  221. position += 1
  222. writer.writerow([op, quantity])
  223. if self.verbose and (position % self.step == 0):
  224. print "%d of %d rows written" % (position, len(opdic))
  225. if self.verbose:
  226. print "Finished.\n"
  227.  
  228. def build_years(self):
  229. """build and write op code + plan qty dict for given years to CSV"""
  230. if self.verbose:
  231. print "For years %d to %d:" % (self.min_year, self.max_year)
  232. opdic = self.build_opdic()
  233. filename = self.basename + "_" + `self.min_year` + "_" + `self.max_year` + ".csv"
  234. self.opdic2csv(opdic, filename)
  235.  
  236.  
  237. """
  238. cur = pymssql.connect(host="server ip", database="TTM", user="user name", password="password").cursor()
  239. so_test = SalesOrder(cur, "05SO02328")
  240. print so_test.get_opstring()
  241. """
  242. # min_year = 2001
  243. # max_year = 2007
  244. # for year in range(min_year, max_year+1):
  245. # MainApp(verbose=True, min_year=year, max_year=year)
  246. #
  247. # MainApp(verbose=True, min_year=2001, max_year=2007)
  248.  
  249. MainApp(verbose=True, min_year=2007, max_year=2007, basename="opcode_qty_BM", segment="M")

Report this snippet  

You need to login to post a comment.