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