I ended up having two tables, one table was the main recipe information such as ID, recipe name, creation date, etc... The other table had all of the recipe steps in it with each step having a recipe ID number equal to the ID on the main table.
I was using Opto 22 so I would query this data, create a CSV file, FTP and write to the SD card on the controller. I have done similar with Siemens PLCs as well but then you have a set number of steps and just loop through and write to each tag with the {step number} indirection.
def writeRecipiesToSH(SHIPDict, recipeID="all"):
query = "SELECT ID, RecipeName from SH_Recipe_Table"
if recipeID != "all":
query += " WHERE ID=%s" % (recipeID)
recipes = system.db.runQuery(query)
recipeDict = {}
for recipe in recipes:
recipeDict.update({recipe[0]:recipe[1]})
recipeData = _getRecipeFileData(recipeDict)
import ftplib
import time
SHErrorList = []
DLLogger = system.util.getLogger("SH Download Logger")
if shared.genFunctions.changeDefaultDir("SHRecipes"):
for SH in SHIPDict:
try:
startTime = time.time()
# Log into the Opto units and write the file to the SD Card
ftp = ftplib.FTP(SHIPDict[SH])
ftp.login()
ftp.cwd("/sdcard0")
folderName = "recipe"
# Check to see if the recipe directory exists on the SH SD card.
if folderName in ftp.nlst():
ftp.cwd("/sdcard0/recipe")
# If the recipe directory doesn't exist then we are going to try and create it.
else:
DLLogger.infof("Recipe directory does not exist on Smokehouse %s, directory is being created...", SH)
ftp.mkd("recipe")
ftp.cwd("/sdcard0/recipe")
logTime = time.time() - startTime
DLLogger.infof("Time to FTP into Smokehouse #%s: %s Seconds", SH, str(logTime))
except:
SHErrorList.append(SH)
DLLogger.errorf("Error logging into, changing directory or creating directory on Smokehouse: %s", SH)
else:
for recipe in recipeData:
startTime = time.time()
try:
#Open up a new file
fileName = recipe+".TXT"
file = open(fileName, "wb+")
except:
DLLogger.errorf("Error creating text file for recipe: %s", recipe)
else:
#write recipe data to file
file.write(recipeData[recipe])
logTime = time.time() - startTime
DLLogger.infof("Time to create text file for recipe %s: %s Seconds", recipe, str(logTime))
startTime = time.time()
# Move file pointer to the begging of the file and begin file transfer
file.seek(0,0)
try:
# store the open file to the Opto SD card via FTP
ftp.storbinary("STOR "+fileName, file)
except:
SHErrorList.append(SH)
file.close()
DLLogger.errorf("Error storing recipe on Smokehouse: %s", SH)
else:
file.close()
logTime = time.time() - startTime
DLLogger.infof("Time to write recipe %s to Smokehouse #%s: %s Seconds", recipe, SH, str(logTime))
ftp.quit()
return SHErrorList
else:
DLLogger.errorf("Could not change default directory")
def _getRecipeFileData(recipeDict):
"""This will query the database for all recipe step information and return a dictionary of recipe name and CSV file data
Takes in a dictionary in the form {RecipeID:Recipe Name}
Returns a dictionary in the form {Recipe Name:CSV File Data (with headers)}
"""
query = """SELECT StepNumber, SteamCook, SmokeCook, Temperature, HumidityCook, Humidity, InternalCook, InternalTemp, BlowerHigh, BlowerLow, ExhMotor, Time, ExhDamper, FreshAirDamper,
PositiveClosedDamper, AirForSmoke, LiquidSmoke, Alarm, Shower, Comments
FROM SH_Recipe_Steps
WHERE RecipeID = ?
"""
recipeFiles = {}
for recipe in recipeDict:
recipeName = recipeDict[recipe]
stepData = system.db.runPrepQuery(query, [recipe])
stepData = system.dataset.toDataSet(stepData)
stepsString = system.dataset.toCSV(stepData, showHeaders=True)
recipeFiles.update({recipeName:stepsString})
return recipeFiles
