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
