def update_notes(database_location, new_note, cook_name=None):
if cook_name is None:
# call last row of database, use name column result
dict_variables_template = SQLQueries.variables_last_row_results(
database_location)
cook_name = dict_variables_template["name"]
tuple_cook_name = (cook_name, )
select_name_query = "SELECT cook_notes FROM notes WHERE cook_name = ?"
my_result = SQLHelpers.query_one(database_location, select_name_query,
tuple_cook_name)
print(my_result)
if my_result is None:
print "None"
tuple_new_note = (
cook_name,
new_note,
)
select_name_query = "INSERT INTO notes(cook_name, cook_notes) VALUES(?,?)"
SQLHelpers.execute(database_location, select_name_query,
tuple_new_note)
else:
print "not None"
tuple_new_note = (
new_note,
cook_name,
)
update_notes_query = "UPDATE notes SET cook_notes=? WHERE cook_name=?"
SQLHelpers.execute(database_location, update_notes_query,
tuple_new_note)
print "SQL database updated with note %s, table %s" % (
tuple_new_note[0], tuple_new_note[1])
def delete_cook(database_location, cook_name):
delete_cook_query = "DELETE FROM temps WHERE cook_name=?"
tuple_cook_name = (cook_name, )
SQLHelpers.execute(database_location, delete_cook_query,
tuple_cook_name)
print "deleted the %s cook" % tuple_cook_name
def update_user(database_location, tuple_update_user):
update_user_query = "UPDATE users SET username=?, password=? WHERE role=?"
# tuple_update_user = (user_row["username"], user_row["password"], user_row["role"])
SQLHelpers.execute(database_location, update_user_query,
tuple_update_user)
print "SQL database updated with role %s, user %s, password %s" % \
(tuple_update_user[2], tuple_update_user[0], tuple_update_user[1])
def cook_notes(database_location, cook_name):
cook_name = (cook_name, )
cook_name_query = "SELECT cook_notes FROM notes WHERE cook_name = ?"
result = str(
SQLHelpers.query_scalar(database_location, cook_name_query,
cook_name))
return result
def cook_history(database_location):
list_history = []
cook_history_query = "SELECT DISTINCT cook_name FROM temps ORDER BY timestamp DESC;"
for result in SQLHelpers.query_text(database_location,
cook_history_query):
list_history.append(str(result[0]))
return list_history
def temps_last_row_results(database_location):
last_row_query = "SELECT * FROM temps WHERE cook_name IS NOT NULL ORDER BY rowid DESC limit 1;"
row = SQLHelpers.query_one(database_location, last_row_query)
temps_timestamp = str(row[0])
temps_grill_temp = str(row[1])
temps_meat_temp = str(row[2])
temps_fan = str(row[4])
return temps_timestamp, temps_grill_temp, temps_meat_temp, temps_fan
def user_query(database_location, role):
# type: (object) -> object
role = (role, )
user_query = "SELECT * FROM users WHERE role = ?"
row = SQLHelpers.query_one(database_location, user_query, role)
# for row in all_rows:
role = row[0]
username = row[1]
password = row[2]
list_user = [role, username, password]
return list_user
def cook_rows(database_location, cook_name):
if cook_name != "":
cook_name = (cook_name, )
cook_name_query = "SELECT * FROM temps WHERE cook_name = ?"
for result in SQLHelpers.query(database_location, cook_name_query,
cook_name):
return result
# print "returning %s" % (result)
else:
# return rows([datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 0, 0])
pass
def variables_last_row_results(database_location):
last_row_query = "select cook_name, target_temp, finish_temp, ifnull(status, 0) " \
"from variables ORDER BY rowid DESC LIMIT 1;"
row = SQLHelpers.query_one(database_location, last_row_query)
# if row == None:
# return ##fix
variables_cook_name = str(row[0])
variables_grill_temp = row[1]
variables_finish_temp = row[2]
variables_status = row[3]
dict_variables_template = {
"name": variables_cook_name,
"target": variables_grill_temp,
"finish": variables_finish_temp,
"status": variables_status
}
return dict_variables_template
def dict_temps_last_row_results(database_location):
last_row_query = "SELECT * FROM temps WHERE cook_name IS NOT NULL ORDER BY rowid DESC limit 1;"
row = SQLHelpers.query_one(database_location, last_row_query)
temps_timestamp = str(row[0])
temps_grill_temp = row[1]
temps_meat_temp = row[2]
temps_fan = row[4]
temps_cook_name = row[5]
dict_temps_template = {
"timestamp": temps_timestamp,
"grill_temp": temps_grill_temp,
"meat_temp": temps_meat_temp,
"fan": temps_fan,
"name": temps_cook_name
}
return dict_temps_template
def history(self):
if request.forms.get('historyName'):
# postdata = request.body.read()
# show data received from client for debugging purposes
# print postdata
# graph value equals the user input selecting a cook name to populate a graph
self.graph_value = request.forms.get('historyName')
# get records for the last cook name
self.cook_row_result = SQLQueries().cook_rows(
self.database_location, self.graph_value)
# make chart for last cook name
self.cook_graph = SQLHelpers.chart_list(self.cook_row_result)
self.cook_list = SQLQueries().cook_history(self.database_location)
self.cook_notes = SQLQueries.cook_notes(self.database_location,
self.graph_value)
return template(self.grillcon_history_template,
get_url=url,
rows=self.cook_graph,
list=self.cook_list,
cook_notes=self.cook_notes,
graph_value=self.graph_value)
# if notesSubmit, update the notes table for the specified cook
if request.forms.get('notesSubmit'):
# print 'history notes received'
self.cook_name = request.POST.get("hidden", '').strip()
self.new_notes = request.POST.get("notes", '').strip()
# print 'new_notes are %s' % (new_notes)
# print "the notes cook name is %s" % (cook_name)
SQLQueries().update_notes(self.database_location, self.new_notes,
self.cook_name)
# redundant with above, possible method?
self.graph_value = self.cook_name
# get records for the last cook name
self.cook_row_result = SQLQueries().cook_rows(self.graph_value)
# make chart for last cook name
self.cook_graph = SQLHelpers.chart_list(self.cook_row_result)
self.cook_list = SQLQueries().cook_history()
self.cook_notes = SQLQueries.cook_notes(self.graph_value)
return template(self.grillcon_history_template,
get_url=url,
rows=self.cook_graph,
list=self.cook_list,
cook_notes=self.cook_notes,
graph_value=self.graph_value)
else:
self.dict_temps_row = SQLQueries.dict_temps_last_row_results(
self.database_location)
# print SQLQueries.temps_last_row_results(self.database_location)
# get last row cook name
self.last_cook_name = self.dict_temps_row["name"]
# get records for the last cook name
self.cook_row_result = SQLQueries.cook_rows(
self.database_location, self.last_cook_name)
# make chart for last cook name
self.cook_graph = SQLHelpers.chart_list(self.cook_row_result)
self.cook_list = SQLQueries().cook_history(self.database_location)
self.cook_notes = SQLQueries.cook_notes(self.database_location,
self.last_cook_name)
return template(self.grillcon_history_template,
get_url=url,
rows=self.cook_graph,
list=self.cook_list,
cook_notes=self.cook_notes,
graph_value=self.last_cook_name)
def main(self):
# if user sends post data to start the grill, validate fields and
# set status in temps database to 1, where grillcon is looking
if request.POST.get('Start Grill Controller', '').strip():
self.send_cook_name = request.POST.get('option_cook_name',
'').strip()
self.send_cook_target = request.POST.get('option_target_temp',
'').strip()
self.send_cook_finish = request.POST.get('option_finish_temp',
'').strip()
# send user inputs to validate function
print 'trying validations'
Validations.validate_input(self.database_location,
self.send_cook_name,
self.send_cook_target,
self.send_cook_finish)
time.sleep(7.0)
redirect('/main')
if request.POST.get('Modify Current Cook', '').strip():
self.send_cook_target = request.POST.get('option_target_temp',
'').strip()
self.send_cook_finish = request.POST.get('option_finish_temp',
'').strip()
# send to validate data function
Validations.validate_input_modify(self.database_location,
self.send_cook_target,
self.send_cook_finish)
time.sleep(5.0)
redirect('/main')
# if receive option off, create tuple with None and 0 turning off the controller
if request.POST.get('option_off', '').strip():
self.tuple_send_cook = (None, None, None, 0)
SQLQueries().write_input(self.database_location,
self.tuple_send_cook)
print "Option Off Received!!"
return template(self.grillcon_off_template, get_url=url)
# if notesSubmit, update the notes table for the current cook
if request.forms.get('notesSubmit'):
print 'notes received'
self.new_notes = request.POST.get("notes", '').strip()
SQLQueries().update_notes(self.database_location, new_notes)
redirect('/main')
else:
print "DATABASE LOCATION"
print self.database_location
# call last row results into a dictionary
self.dict_variables_template = SQLQueries.variables_last_row_results(
self.database_location)
# if 'status' == 1 the grill controller is on
if self.dict_variables_template["status"] == 1:
# populate the dictionary to create the web page showing the controller as on
self.dict_variables_row = self.dict_variables_template
# get last row cook name
self.dict_temps_row = SQLQueries.dict_temps_last_row_results(
self.database_location)
self.last_cook_name = self.dict_temps_row["name"]
# get records for last row cook name
self.cook_row_result = SQLQueries.cook_rows(
self.database_location, self.last_cook_name)
# make chart for last row cook name
self.cook_graph = SQLHelpers.chart_list(self.cook_row_result)
# get notes for the last cook
self.cook_notes = SQLQueries.cook_notes(
self.database_location, self.last_cook_name)
return template(self.grillcon_on_template,
get_url=url,
rows=self.cook_graph,
dict_variables_row=self.dict_variables_row,
dict_temps_row=self.dict_temps_row,
cook_notes=self.cook_notes)
else:
# print "file path is %s /n template path is %s /n complete file is %s" %
# (path, grillcon_off_template, template_path)
return template(self.grillcon_off_template, get_url=url)
def write_input(database_location, tuple_send_cook):
write_input_query = "INSERT INTO variables(cook_name, target_temp, finish_temp, status) VALUES(?,?,?,?)"
SQLHelpers.execute(database_location, write_input_query,
tuple_send_cook)