def createCandyReferenceByID( candyID=0, colorID=0, textureID=0, variantID=0, packagingID=0): # Creates a Candy Reference for given composition IDs connection = oracle.connectToOracle() cursor = connection.cursor() ref = getCandyReferenceByCompositionID(candyID, colorID, textureID, variantID, packagingID) if not ref: cursor.execute( """ INSERT INTO CANDYREFERENCES(FK_CANDY_ID, FK_COLOR_ID, FK_TEXTURE_ID, FK_VARIANT_ID, FK_PACKAGING_ID) VALUES(:FK_CANDY_ID, :FK_COLOR_ID, :FK_TEXTURE_ID, :FK_VARIANT_ID, :FK_PACKAGING_ID)""", { "FK_CANDY_ID": candyID, "FK_COLOR_ID": colorID, "FK_TEXTURE_ID": textureID, "FK_VARIANT_ID": variantID, "FK_PACKAGING_ID": packagingID }) connection.commit() cursor.close() ref = getCandyReferenceByCompositionID(candyID, colorID, textureID, variantID, packagingID) return ref
def getCandyReferenceByID( candyReferenceID=0 ): # Returns a candy reference if exists for a given reference ID connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT cr.CANDYSTOCK, cr.READYTOPACK, cr.READYBOX, cr.READYBAG, cr.READYSAMPLE, candy.CANDYNAME, color.COLORNAME, text.TEXTURENAME, var.VARIANTNAME, pack.PACKAGINGNAME FROM CANDYREFERENCES cr LEFT JOIN CANDY candy ON (candy.CANDY_ID = cr.FK_CANDY_ID) LEFT JOIN COLOR color ON (color.COLOR_ID = cr.FK_COLOR_ID) LEFT JOIN TEXTURE text ON (text.TEXTURE_ID = cr.FK_TEXTURE_ID) LEFT JOIN VARIANT var ON (var.VARIANT_ID = cr.FK_VARIANT_ID) LEFT JOIN PACKAGING pack ON (pack.PACKAGING_ID = cr.FK_PACKAGING_ID) WHERE (cr.CANDYREFERENCE_ID = :CANDYREFERENCEID)""", {"CANDYREFERENCEID": candyReferenceID}) results = cursor.fetchall() return results
def createOrder(clientName="UNKNOWN", clientSurname="UNKNOWN", clientMail="UNKNOWN", quantity=0, totalCost=0, orderState="UNKNOWN", trackingNumber=0000, countryID=0, candyReference=0 ): # Creates the order according to the given parameters connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ INSERT INTO ORDERS(ORDERDATE, CLIENTNAME, CLIENTSURNAME, CLIENTMAIL, QUANTITY, TOTALCOST, ORDERSTATE, TRACKINGNUMBER, FK_COUNTRY_ID, FK_CANDYREFERENCE_ID) VALUES(TO_TIMESTAMP(:ORDERDATE, 'DD/MM/YYYY hh24:mi:ss'), :CLIENTNAME, :CLIENTSURNAME, :CLIENTMAIL, :QUANTITY, :TOTALCOST, :ORDERSTATE, :TRACKINGNUMBER, :FK_COUNTRY_ID, :FK_CANDYREFERENCE_ID)""", { "ORDERDATE": time.strftime('%d-%m-%Y %H:%M:%S'), "CLIENTNAME": clientName, "CLIENTSURNAME": clientSurname, "CLIENTMAIL": clientMail, "QUANTITY": quantity, "TOTALCOST": totalCost, "ORDERSTATE": orderState, "TRACKINGNUMBER": trackingNumber, "FK_COUNTRY_ID": countryID, "FK_CANDYREFERENCE_ID": candyReference }) connection.commit() cursor.close() return True
def getOrderByReferenceID( referenceID=0 ): # Returns an order for a given reference ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT o.ORDERDATE, o.CLIENTNAME, o.CLIENTSURNAME, o.CLIENTMAIL, o.FK_CANDYREFERENCE_ID, o.QUANTITY, o.TOTALCOST, o.ORDERSTATE, c.COUNTRY o.TRACKINGNUMBER FROM ORDERS o LEFT JOIN COUNTRY c ON (c.COUNTRY_ID = o.FK_COUNTRY_ID ) WHERE (o.FK_CANDYREFERENCE_ID = :FK_CANDYREFERENCE_ID)""", {"FK_CANDYREFERENCE_ID": referenceID}) result = cursor.fetchall() cursor.close() return result
def getPackagingByName( packagingName="" ): # Returns the packaging according to the packaging Name given as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT p.PACKAGING_ID, p.CONTENT, p.FK_PALETTE_ID FROM PACKAGING p WHERE (p.PACKAGINGNAME = :PACKAGINGNAME)""", {"PACKAGINGNAME": packagingName}) result = cursor.fetchall() if len(result) != 0: if int(result[0][1]) == 1: cursor.execute( """ SELECT p.PACKAGING_ID, p.PACKAGINGNAME as Container, pal.PALETTENAME as Content, p.QUANTITY, pal.PLANEQUANTITY as CardboardsPerPlane, pal.BOATQUANTITY as CardboardsPerBoat, pal.TRUCKQUANTITY as CardboardsPerTruck FROM PACKAGING p LEFT JOIN PALETTE pal ON (p.FK_PALETTE_ID = pal.PALETTE_ID) WHERE (p.PACKAGINGNAME = :PACKAGINGNAME)""", {"PACKAGINGNAME": packagingName}) elif int(result[0][0]) == 1: cursor.execute( """ SELECT p.PACKAGING_ID, p.PACKAGINGNAME as Container, pack.PACKAGINGNAME as Content, p.QUANTITY FROM PACKAGING p LEFT JOIN PACKAGING pack ON (p.CONTENT = pack.PACKAGING_ID) WHERE (p.PACKAGINGNAME = :PACKAGINGNAME)""", {"PACKAGINGNAME": packagingName}) else: cursor.execute( """ SELECT p.PALETTE_ID, p.PALETTENAME as Container, c.CARDBOARDNAME as Content, p.BOATQUANTITY as CardbardsInBoat, p.TRUCKQUANTITY as CardbardsInTruck, p.PLANEQUANTITY as CardbardsInPlane FROM PALETTE p LEFT JOIN CARDBOARD c ON (p.FK_CARDBOARD_ID = c.CARDBOARD_ID) WHERE (p.PALETTENAME = :PALETTENAME)""", {"PALETTENAME": packagingName}) result = cursor.fetchall() if len(result) != 1: cursor.execute( """ SELECT c.CARDBOARD_ID, c.CARDBOARDNAME, c.SAMPLEQUANTITY as SamplesPerCardboard, c.BAGQUANTITY as BagsPerCardboard, c.BOXQUANTITY as BoxesPerCardboard FROM CARDBOARD c WHERE (c.CARDBOARDNAME = :CARDBOARDNAME)""", {"CARDBOARDNAME": packagingName}) result = cursor.fetchall() cursor.close() return result
def getVariantsList(): # Returns the variants list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT v.VARIANTNAME FROM VARIANT v """) result = cursor.fetchall() cursor.close() return result
def getColorsList(): # Returns the color list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT c.COLORNAME FROM COLOR c """) result = cursor.fetchall() cursor.close() return result
def getTexturesList(): # Returns the texture list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT t.TEXTURENAME FROM TEXTURE t """) result = cursor.fetchall() cursor.close() return result
def getCandiesList(): # Returns the Candy List connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT c.CANDYNAME, c.ADDITIVE, c.COATING, c.AROMA, c.GELLING, c.SUGAR FROM CANDY c """) results = cursor.fetchall() cursor.close() return results
def getColorByName(colorName=""): # Returns a Color for a given color Name as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT c.COLOR_ID, c.COLORNAME FROM COLOR c WHERE (c.COLORNAME = :COLORNAME)""", {"COLORNAME": colorName}) result = cursor.fetchall() cursor.close() return result
def getColorByID(colorID=0): # Returns a Color for a given color ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT c.COLORNAME FROM COLOR c WHERE (c.COLOR_ID = :COLOR_ID)""", {"COLOR_ID": colorID}) result = cursor.fetchall() cursor.close() return result
def getTextureByID( textureID=0): # Returns a texture a given texture ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT t.TEXTURENAME FROM TEXTURE t WHERE (t.TEXTURE_ID = :TEXTURE_ID)""", {"TEXTURE_ID": textureID}) result = cursor.fetchall() cursor.close() return result
def getStocksList(): # Returns the stock list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT s.COMPONENT, s.CONDITIONNEMENTKG, s.PALETTEKG FROM STOCK s """) result = cursor.fetchall() cursor.close() return result
def getCountriesList(): # Return the country list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT c.COUNTRYNAME, p.PACKAGINGNAME FROM COUNTRY c LEFT JOIN PACKAGING p ON (c.FK_PACKAGING_ID = p.PACKAGING_ID ) """) result = cursor.fetchall() cursor.close() return result
def getVariantByID( variantID=0): # Returns a variant a given variant ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT v.VARIANTNAME FROM VARIANT v WHERE (v.VARIANT_ID = :VARIANT_ID)""", {"VARIANT_ID": variantID}) result = cursor.fetchall() cursor.close() return result
def getStockByComponent(component="UNKNOWN"): # Returns a component stock for a given component name as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT s.COMPONENT, s.CONDITIONNEMENTKG, s.PALETTEKG FROM STOCK s WHERE (s.COMPONENT = :COMPONENT)""", {"COMPONENT": component}) result = cursor.fetchall() cursor.close() return result
def getVariantByName( variantName="" ): # Returns a variant a given variant name as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT v.VARIANT_ID, v.VARIANTNAME FROM VARIANT v WHERE (v.VARIANTNAME = :VARIANTNAME)""", {"VARIANTNAME": variantName}) result = cursor.fetchall() cursor.close() return result
def getCountryByName(countryname=""): # Returns a Country for a given country name as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT c.COUNTRY_ID, c.COUNTRYNAME, p.PACKAGINGNAME FROM COUNTRY c LEFT JOIN PACKAGING p ON (c.FK_PACKAGING_ID = p.PACKAGING_ID ) WHERE (c.COUNTRYNAME = :COUNTRYNAME)""", {"COUNTRYNAME": countryname}) result = cursor.fetchall() cursor.close() return result
def getCandyByID( candyID=0): # Returns a candy for a candy ID given as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT c.CANDYNAME, c.ADDITIVE, c.COATING, c.AROMA, c.GELLING, c.SUGAR FROM CANDY c WHERE (c.CANDY_ID = :CANDY_ID)""", {"CANDY_ID": candyID}) result = cursor.fetchall() cursor.close() return result
def getTextureByName( textureName="" ): # Returns a texture a given texture name as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT t.TEXTURE_ID, t.TEXTURENAME FROM TEXTURE t WHERE (t.TEXTURENAME = :TEXTURENAME)""", {"TEXTURENAME": textureName}) result = cursor.fetchall() cursor.close() return result
def getStockByID(stockID=0): # Returns a component stock for a given stock ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT s.COMPONENT, s.CONDITIONNEMENTKG, s.PALETTEKG FROM STOCK s WHERE (s.STOCK_ID = :STOCK_ID)""", {"STOCK_ID": stockID}) for COMPONENT, CONDITIONNEMENTKG, PALETTEKG in cursor: print(COMPONENT, CONDITIONNEMENTKG, PALETTEKG) cursor.close() return
def getMachinesList(): # Returns the machine list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT m.MACHINENUMBER, m.CADENCE, m.MACHINEDELAY, v.VARIANTNAME, p.PACKAGINGNAME FROM MACHINE m LEFT JOIN VARIANT v ON (v.VARIANT_ID = m.FK_VARIANT_ID) LEFT JOIN PACKAGING p ON (p.PACKAGING_ID = m.FK_PACKAGING_ID) """) result = cursor.fetchall() cursor.close() return result
def updateStockQuantityByCandyName(candyName="UNKNOWN", totalQuantity=0): # Updates the stock quantity according to a given candy composition * total quantity required given as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() results = candies.getCandyByName(candyName)[0] quantities = [results[2], results[3], results[4], results[5], results[6]] component = ['additive', 'coating', 'aroma', 'gelling', 'sugar'] for x in range(0, len(component)): query = """UPDATE STOCK SET PALETTEG = ( SELECT PALETTEG FROM STOCK WHERE COMPONENT = '{}') - {} WHERE COMPONENT = '{}'""".format(component[x], quantities[x] * totalQuantity, component[x]) cursor.execute(query) connection.commit()
def getCandyCostsList(): # Returns a candy costs List connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT c.CANDYNAME, cost.manufacturePercentage, cost.contioningPercentage, cost.shippingPercentage, cost.generalPercentage, cost.sampleCost, cost.bagCost, cost.boxCost FROM CANDY c LEFT JOIN CANDYCOST cost ON (c.CANDY_ID = cost.CANDYCOST_ID ) """) results = cursor.fetchall() return results
def getOrdersList(): # Returns the order list connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute(""" SELECT o.ORDERDATE, o.CLIENTNAME, o.CLIENTSURNAME, o.CLIENTMAIL, o.FK_CANDYREFERENCE_ID, o.QUANTITY, o.TOTALCOST, o.ORDERSTATE, c.COUNTRY o.TRACKINGNUMBER FROM ORDERS o LEFT JOIN COUNTRY c ON (c.COUNTRY_ID = o.FK_COUNTRY_ID ) """) result = cursor.fetchall() cursor.close() return result
def getMachineByID( machineID=0 ): # Returns a Machine for a given machine ID as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT m.MACHINENUMBER, m.CADENCE, m.MACHINEDELAY, v.VARIANTNAME, p.PACKAGINGNAME FROM MACHINE m LEFT JOIN VARIANT v ON (v.VARIANT_ID = m.FK_VARIANT_ID) LEFT JOIN PACKAGING p ON (p.PACKAGING_ID = m.FK_PACKAGING_ID) WHERE (m.MACHINE_ID = :MACHINE_ID)""", {"MACHINE_ID": machineID}) result = cursor.fetchall() cursor.close() return result
def getCandyCostByID( candyID=0): # Returns a candy cost for a candy ID given as a parameter connection = oracle.connectToOracle() cursor = connection.cursor() cursor.execute( """ SELECT c.CANDY_ID, c.CANDYNAME, cost.manufacturePercentage, cost.contioningPercentage, cost.shippingPercentage, cost.generalPercentage, cost.sampleCost, cost.bagCost, cost.boxCost FROM CANDY c LEFT JOIN CANDYCOST cost ON (c.CANDY_ID = cost.CANDYCOST_ID ) WHERE (c.CANDY_ID = :CANDY_ID)""", {"CANDY_ID": candyID}) result = cursor.fetchall() cursor.close() return result