Revision: 5275
Updated Code
at February 24, 2008 23:08 by stagger
Updated Code
import sys import pymssql import csv """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() class MainApp: """This is the main application.""" def __init__(self, min_year=2001, max_year=2001, verbose=False, print_so=False, step=100, basename="opcode_qty", segment=""): self.verbose = verbose self.print_so = print_so self.step = step self.basename = basename self.segment = segment self.min_year = min_year self.max_year = max_year sql_host = "server ip" # on Mac we need IP _and_ port number: if (sys.platform == "darwin"): sql_host += ":1433" con = pymssql.connect(host=sql_host, user='user name', password='password', database='TTM') self.cur = con.cursor() self.build_years() def build_opdic(self): """build dictionary of op codes from SOs with accumulated plan quantities""" opdic = {} # get all sales orders from SQL server so_codes_query = "SELECT t1.fsono " \ "FROM od20sord t1 " \ "WHERE t1.fitemno1 LIKE ' 1' " \ "AND t1.fsodate <= t1.fddate " \ "AND DatePart(yyyy, t1.fddate) >= " + str(self.min_year) + " " \ "AND DatePart(yyyy, t1.fddate) <= " + str(self.max_year) + " " if self.segment: so_codes_query += "AND t1.fpdcode LIKE '" + self.segment + "%' " so_codes_query += "GROUP BY t1.fsono " \ "ORDER BY t1.fsono" self.cur.execute(so_codes_query) sorder_codes = self.cur.fetchall() position = 0 if self.verbose: print "Starting to build a op code dictionary out of %d SQL data rows." % len(sorder_codes) print "This could take some time..." # put sales orders in dictionary for sorder_code in sorder_codes: position += 1 # for debugging if self.verbose and (position % self.step == 0): print "Progress: %d / %d" % (position, len(sorder_codes)) # create sales order object and iterate through items sorder = SalesOrder(self.cur, sorder_code[0]) for soitem in sorder.items: bom = soitem.bom if bom: opstring = soitem.get_opstring() qty = int(soitem.bom.quantity) if opstring: if not opdic.has_key(opstring): opdic[opstring] = qty else: opdic[opstring] += qty if self.print_so: print "%s:%s (%6d) => %s" % (sorder_code[0], soitem.number.lstrip(), soitem.bom.quantity, opstring) # for debugging if self.verbose: print "Finished! The dictionary has %d entries." % len(opdic) return opdic def opdic2csv(self, opdic, filename): """write opdic to CSV file""" writer = csv.writer(file(filename, "w"), dialect="excel", delimiter=";") position = 0 if self.verbose: print "Writing dictionary to file: " + filename for op, quantity in opdic.items(): position += 1 writer.writerow([op, quantity]) if self.verbose and (position % self.step == 0): print "%d of %d rows written" % (position, len(opdic)) if self.verbose: print "Finished.\n" def build_years(self): """build and write op code + plan qty dict for given years to CSV""" if self.verbose: print "For years %d to %d:" % (self.min_year, self.max_year) opdic = self.build_opdic() filename = self.basename + "_" + `self.min_year` + "_" + `self.max_year` + ".csv" self.opdic2csv(opdic, filename) """ cur = pymssql.connect(host="server ip", database="TTM", user="user name", password="password").cursor() so_test = SalesOrder(cur, "05SO02328") print so_test.get_opstring() """ # min_year = 2001 # max_year = 2007 # for year in range(min_year, max_year+1): # MainApp(verbose=True, min_year=year, max_year=year) # # MainApp(verbose=True, min_year=2001, max_year=2007) MainApp(verbose=True, min_year=2007, max_year=2007, basename="opcode_qty_BM", segment="M")
Revision: 5274
Updated Code
at February 24, 2008 22:58 by stagger
Updated Code
import sys import pymssql import csv """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() class MainApp: """This is the main application.""" def __init__(self, min_year=2001, max_year=2001, verbose=False, print_so=False, step=100, basename="opcode_qty", segment=""): self.verbose = verbose self.print_so = print_so self.step = step self.basename = basename self.segment = segment self.min_year = min_year self.max_year = max_year sql_host = "192.168.1.1" # on Mac we need IP _and_ port number: if (sys.platform == "darwin"): sql_host += ":1433" con = pymssql.connect(host=sql_host, user='USER1', password='Project', database='TTM') self.cur = con.cursor() self.build_years() def build_opdic(self): """build dictionary of op codes from SOs with accumulated plan quantities""" opdic = {} # get all sales orders from SQL server so_codes_query = "SELECT t1.fsono " "FROM od20sord t1 " "WHERE t1.fitemno1 LIKE ' 1' " "AND t1.fsodate <= t1.fddate " "AND DatePart(yyyy, t1.fddate) >= " + str(self.min_year) + " " "AND DatePart(yyyy, t1.fddate) <= " + str(self.max_year) + " " if self.segment: so_codes_query += "AND t1.fpdcode LIKE '" + self.segment + "%' " so_codes_query += "GROUP BY t1.fsono " "ORDER BY t1.fsono" self.cur.execute(so_codes_query) sorder_codes = self.cur.fetchall() position = 0 if self.verbose: print "Starting to build a op code dictionary out of %d SQL data rows." % len(sorder_codes) print "This could take some time..." # put sales orders in dictionary for sorder_code in sorder_codes: position += 1 # for debugging if self.verbose and (position % self.step == 0): print "Progress: %d / %d" % (position, len(sorder_codes)) # create sales order object and iterate through items sorder = SalesOrder(self.cur, sorder_code[0]) for soitem in sorder.items: bom = soitem.bom if bom: opstring = soitem.get_opstring() qty = int(soitem.bom.quantity) if opstring: if not opdic.has_key(opstring): opdic[opstring] = qty else: opdic[opstring] += qty if self.print_so: print "%s:%s (%6d) => %s" % (sorder_code[0], soitem.number.lstrip(), soitem.bom.quantity, opstring) # for debugging if self.verbose: print "Finished! The dictionary has %d entries." % len(opdic) return opdic def opdic2csv(self, opdic, filename): """write opdic to CSV file""" writer = csv.writer(file(filename, "w"), dialect="excel", delimiter=";") position = 0 if self.verbose: print "Writing dictionary to file: " + filename for op, quantity in opdic.items(): position += 1 writer.writerow([op, quantity]) if self.verbose and (position % self.step == 0): print "%d of %d rows written" % (position, len(opdic)) if self.verbose: print "Finished.n" def build_years(self): """build and write op code + plan qty dict for given years to CSV""" if self.verbose: print "For years %d to %d:" % (self.min_year, self.max_year) opdic = self.build_opdic() filename = self.basename + "_" + `self.min_year` + "_" + `self.max_year` + ".csv" self.opdic2csv(opdic, filename) """ cur = pymssql.connect(host="server ip", database="TTM", user="user name", password="password").cursor() so_test = SalesOrder(cur, "05SO02328") print so_test.get_opstring() """ # min_year = 2001 # max_year = 2007 # for year in range(min_year, max_year+1): # MainApp(verbose=True, min_year=year, max_year=year) # # MainApp(verbose=True, min_year=2001, max_year=2007) MainApp(verbose=True, min_year=2007, max_year=2007, basename="opcode_qty_BM", segment="M")
Revision: 5273
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 24, 2008 22:52 by stagger
Initial Code
import sys import pymssql import csv """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() class MainApp: """This is the main application.""" def __init__(self, min_year=2001, max_year=2001, verbose=False, print_so=False, step=100, basename="opcode_qty", segment=""): self.verbose = verbose self.print_so = print_so self.step = step self.basename = basename self.segment = segment self.min_year = min_year self.max_year = max_year sql_host = "192.168.1.1" # on Mac we need IP _and_ port number: if (sys.platform == "darwin"): sql_host += ":1433" con = pymssql.connect(host=sql_host, user='USER1', password='Project', database='TTM') self.cur = con.cursor() self.build_years() def build_opdic(self): """build dictionary of op codes from SOs with accumulated plan quantities""" opdic = {} # get all sales orders from SQL server so_codes_query = "SELECT t1.fsono " "FROM od20sord t1 " "WHERE t1.fitemno1 LIKE ' 1' " "AND t1.fsodate <= t1.fddate " "AND DatePart(yyyy, t1.fddate) >= " + str(self.min_year) + " " "AND DatePart(yyyy, t1.fddate) <= " + str(self.max_year) + " " if self.segment: so_codes_query += "AND t1.fpdcode LIKE '" + self.segment + "%' " so_codes_query += "GROUP BY t1.fsono " "ORDER BY t1.fsono" self.cur.execute(so_codes_query) sorder_codes = self.cur.fetchall() position = 0 if self.verbose: print "Starting to build a op code dictionary out of %d SQL data rows." % len(sorder_codes) print "This could take some time..." # put sales orders in dictionary for sorder_code in sorder_codes: position += 1 # for debugging if self.verbose and (position % self.step == 0): print "Progress: %d / %d" % (position, len(sorder_codes)) # create sales order object and iterate through items sorder = SalesOrder(self.cur, sorder_code[0]) for soitem in sorder.items: bom = soitem.bom if bom: opstring = soitem.get_opstring() qty = int(soitem.bom.quantity) if opstring: if not opdic.has_key(opstring): opdic[opstring] = qty else: opdic[opstring] += qty if self.print_so: print "%s:%s (%6d) => %s" % (sorder_code[0], soitem.number.lstrip(), soitem.bom.quantity, opstring) # for debugging if self.verbose: print "Finished! The dictionary has %d entries." % len(opdic) return opdic def opdic2csv(self, opdic, filename): """write opdic to CSV file""" writer = csv.writer(file(filename, "w"), dialect="excel", delimiter=";") position = 0 if self.verbose: print "Writing dictionary to file: " + filename for op, quantity in opdic.items(): position += 1 writer.writerow([op, quantity]) if self.verbose and (position % self.step == 0): print "%d of %d rows written" % (position, len(opdic)) if self.verbose: print "Finished.n" def build_years(self): """build and write op code + plan qty dict for given years to CSV""" if self.verbose: print "For years %d to %d:" % (self.min_year, self.max_year) opdic = self.build_opdic() filename = self.basename + "_" + `self.min_year` + "_" + `self.max_year` + ".csv" self.opdic2csv(opdic, filename) """ cur = pymssql.connect(host="server ip", database="TTM", user="user name", password="password").cursor() so_test = SalesOrder(cur, "05SO02328") print so_test.get_opstring() """ # min_year = 2001 # max_year = 2007 # for year in range(min_year, max_year+1): # MainApp(verbose=True, min_year=year, max_year=year) # # MainApp(verbose=True, min_year=2001, max_year=2007) MainApp(verbose=True, min_year=2007, max_year=2007, basename="opcode_qty_BM", segment="M")
Initial URL
Initial Description
Initial Title
SoftPRO Data Collector
Initial Tags
sql, textmate, python
Initial Language
Python