SoftPRO Data Collector

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")