def loginPage():
error = ""
try:
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
conn,cursor = getDbCursor()
query = 'SELECT * FROM users WHERE username = %s'
cursor.execute(query, (username,))
result = cursor.fetchone()
print(result)
if (result is not None and result[2] == password):
session['id'] = result[0]
session['logged_in'] = True
session['username'] = username
flash("Welcome " + username + " you are now logged in.", "success")
gc.collect()
conn.close()
return redirect(url_for('dashBoardPage'))
#verification failed. falls through to here.
flash("INVALID LOGIN", "error")
error = "Invalid login. Try Again."
gc.collect()
conn.close()
return render_template("login.html", error=error) #fallthrough covers GET aswell.
except Exception as e:
# TODO - this needs to be changed to allow less information.
flash(str(e))
return render_template("login.html", error=error)
def registerPage():
form = RegistrationForm(request.form)
if request.method == "POST" and form.validate():
username = form.username.data
email = form.email.data
password = form.password.data
conn,cursor = getDbCursor()
print("username " + username)
query = 'SELECT * FROM users WHERE username = %s'
cursor.execute(query, (username,))
if cursor.fetchone() is not None:
flash("Sorry that username is already taken.")
gc.collect()
conn.close()
return render_template("register.html", form=form)
else:
query = """INSERT INTO users (username, password, email) VALUES (%s, %s, %s)"""
cursor.execute(query, (username, password, email,))
conn.commit()
session['logged_in'] = True
session['username'] = username
query = 'SELECT * FROM users WHERE username = %s'
cursor.execute(query, (username,))
result = cursor.fetchone()
session['id'] = result[0]
conn.close()
gc.collect()
return redirect(url_for('dashBoardPage'))
if request.method == "POST" and form.validate() != True:
flash_errors(form)
return render_template("register.html", form=form)
return render_template("register.html", form=form)
def addControl():
PORT_MAPPING = { 7:12, 8:16, 9:18, 10:23, 11:24, 12:25 } # maps ports to GPIO on rpi.
# retrieve information
hid = int(session["hid"])
try:
port = int(request.form["port"])
controller_type_name = request.form["controller_type_name"]
controller_type_id = int(request.form["controller_type_id"]) #from hidden form field
except Exception:
flash("You need to select a port", "error")
return redirect(url_for('viewDevice'))
# get the gpio pin associated with the port
gpio = PORT_MAPPING[port]
#add the new controller to the GPIO table.
connection, cursor = getDbCursor()
query = (
"INSERT INTO controller (controller_hub_id,controller_gpio,controller_type_id) "
"VALUES ({}, {}, {}) ".format(hid, gpio, controller_type_id)
)
cursor.execute(query)
connection.commit()
cleanUpDb(connection, cursor)
gc.collect()
flash("You have successfully added a " + controller_type_name + " controller to port " + str(port), "success")
return redirect(url_for('viewDevice'))
def addSensor():
PORT_MAPPING = { 1:5, 2:6, 3:13, 4:17, 5:22, 6:27} # maps ports to GPIO on rpi.
# retrieve information
hid = int(session["hid"])
try:
port = int(request.form["port"])
sensor_type_name = request.form["sensor_type_name"]
sensor_type_id = int(request.form["sensor_type_id"]) #from hidden form field in /selectsensor.html
except Exception:
flash("You need to select a port", "error")
return redirect(url_for('viewDevice'))
# get the gpio pin associated with the port
gpio = PORT_MAPPING[port]
#add the new sensor to the GPIO table.
connection, cursor = getDbCursor()
query = (
"INSERT INTO sensor (sensor_hub_id,sensor_gpio,sensor_type_id)"
"VALUES ({}, {}, {}) ".format(hid, gpio, sensor_type_id)
)
cursor.execute(query)
connection.commit()
cleanUpDb(connection, cursor)
gc.collect()
flash("You have successfully added a " + sensor_type_name + " sensor to port " + str(port), "success")
return redirect(url_for('viewDevice'))
def viewPictures():
#get the user's selected hub
hid = session["hid"]
#retrieve the picture links for all the files.
# -- store these inside of a list.
# remove the sensor from the sensors table
connection, cursor = getDbCursor()
query = (
"SELECT link, description "
"FROM pictures "
"WHERE hub_id = %s"
)
cursor.execute(query, (hid,))
results = []
for link, desc in cursor:
results.append((link,desc))
cleanUpDb(connection, cursor)
gc.collect()
#embed the pictures into the page
# test data (overrides DB call...)
# results = [("https://www.w3schools.com//w3images/lights.jpg", "1"), ("https://www.w3schools.com//w3images/lights.jpg", "2"),("https://www.w3schools.com//w3images/lights.jpg", "3"),("https://www.w3schools.com//w3images/lights.jpg", "4"),("https://www.w3schools.com//w3images/lights.jpg", "5")]
environment_name = session["environment_name"]
is_results = True
if len(results) == 0:
is_results = False
print(is_results)
return render_template("viewpictures.html", environment_name=environment_name, pictures=results, is_results=str(is_results))
def viewEvents():
hid = session["hid"]
environment_name = session["environment_name"]
# query DB for all events with a event_hub_id of hid
query = (
"SELECT event_dtg, event_message "
"FROM event "
"WHERE event_hub_id = %s"
)
# #save the file to the database
connection, cursor = getDbCursor()
cursor.execute(query, (hid,))
results = []
for event_dtg, event_message in cursor:
results.append((event_dtg, event_message))
# Currently first LOGGED TO THE DB will be displayed first.
# we want most recent.
results = list(reversed(results))
cleanUpDb(connection,cursor)
return render_template("events.html", results=results, environment_name=environment_name)
def getHub():
#retrieve arguments
args = request.args.to_dict()
# currently only want the mac adr, could add in more params in future.
mac = args["mac"]
#query database to retrive hub information
connection, cursor = getDbCursor()
query = (
"SELECT hub_id, hub_mac, hub_name, hub_profile_id "
"FROM hub "
"WHERE hub_mac "
"LIKE ('{}')".format(mac)
)
cursor.execute(query)
hub = {}
for hub_id, hub_mac, hub_name, hub_profile_id in cursor:
hub.update({
'hub_id': hub_id,
'hub_mac': hub_mac,
'hub_name': hub_name,
'hub_profile_id': hub_profile_id})
if not any(hub):
hub = {"failed":"1"}
# print("Unregistered MAC (" + mac + ")\nDevice could not be configured.\n")
cleanUpDb(connection,cursor)
return json.dumps(hub)
def viewDevice():
# try:
# Get the id parameter from the GET request
# OR if user has already chosen a hub, just use that one.
args = request.args.to_dict()
if "hid" in args:
hid = args["hid"]
elif "hid" in session:
hid = session["hid"]
#query db here for general info
connection, cursor = getDbCursor()
hub = get_hub_from_id(hid, cursor)
info = {
'hub' : hub,
'controller_type': get_controller_types(cursor),
'controller': get_controllers(hub['hub_id'],cursor),
'sensor_type': get_sensor_types(cursor),
'sensor': get_sensors(hub['hub_id'],cursor),
'profile': get_profile(hub['hub_profile_id'],cursor),
'profile_sensor': get_profile_sensor(hub['hub_profile_id'],cursor),
'lighting_gpio': get_lighting(hub['hub_profile_id'],cursor)
}
cleanUpDb(connection, cursor)
gc.collect()
if hub["hub_owner_id"] != session["id"]:
return redirect(url_for('forbiddenPage'))
# get environment name
environment_name = info["hub"]["hub_name"]
# get plant profile
plant_profile = info["profile"]["profile_name"]
#get sensors
sensors = {}
for sensor in info["sensor"]:
sensors[sensor["sensor_id"]] = (info["sensor_type"][(sensor["sensor_type_id"])]["sensor_type_name"])
#get controllers
control_elements = {}
for ce in info["controller"]:
control_elements[ce["controller_id"]] = (info["controller_type"][(ce["controller_type_id"])]["controller_type_name"])
print ("control elements: " + str(control_elements))
# except Exception:
# # TODO - return a failed device page
# return("Failed to get device.")
# add new session variables
session["hid"] = hid
session["environment_name"] = environment_name
session["current_profile"] = plant_profile
return render_template("viewdevice.html", environment_name=environment_name, plant_profile=plant_profile, sensors=sensors, control_elements=control_elements) #hid is appended to every sub url, it lets the system query the correct hub.
def newDevice():
user_id = session["id"]
if request.form["name"] == "":
flash("You must type in a name!", "error")
return redirect(url_for('dashBoardPage'))
if request.form["uid"] == "":
flash("You must type in a unique id!", "error")
return redirect(url_for('dashBoardPage'))
name = request.form["name"]
try:
unique_id = int(request.form["uid"])
except Exception:
flash("Unique ID must be a number!", "error")
return redirect(url_for('dashBoardPage'))
# check database for hub
connection, cursor = getDbCursor()
query = (
"SELECT hub_id, hub_mac, hub_name, hub_profile_id, hub_owner_id "
"FROM hub "
"WHERE hub_id = {}".format(unique_id)
)
cursor.execute(query)
result = cursor.fetchone()
if result is None:
flash("That unique ID doesn't exist!", "error")
return redirect(url_for('dashBoardPage'))
if result[4] is not None: # hub_owner_id
flash("That hub is already owned", "error")
return redirect(url_for('dashBoardPage'))
# if it makes it here, there is a valid hub with no owner.
# update its name, and set the owner to be this user
# UPDATES DEFAULT PLANT PROFILE TO 1.
query = (
"UPDATE hub "
"SET hub_name = %s, hub_profile_id = 1, hub_owner_id = %s "
"WHERE hub_id = %s"
)
cursor.execute(query, (name, user_id, unique_id))
connection.commit()
cleanUpDb(connection, cursor)
gc.collect()
#redirect back to dashboard for now.
flash("Successfully added in environment " + name, "success")
#TODO -- add Try: catch: here?
return redirect(url_for('dashBoardPage'))
def dashBoardPage():
# get all environments belonging to the user.
connection, cursor = getDbCursor()
query = (
"SELECT hub_id, hub_name "
"FROM hub "
"WHERE hub_owner_id = {}".format(int(session["id"]))
)
cursor.execute(query)
results = {}
for hub_id,hub_name in cursor:
results[hub_id] = hub_name
cleanUpDb(connection,cursor)
gc.collect()
# these environments are rendered on the dashboard.html template.
return render_template("dashboard.html", results=results)
def modifyPlantProfile():
# the user requies a hub to be selected to be here!
# this means use of hid & profile cookie can be used.
connection, cursor = getDbCursor()
query = (
"SELECT profile_id, profile_name "
"FROM profile"
)
cursor.execute(query)
profiles = {}
for profile_id, profile_name in cursor:
profiles[profile_id] = profile_name
cleanUpDb(connection, cursor)
gc.collect()
# return a list of current profiles to the user
return render_template("modifyplantprofile.html", profiles=profiles)
def deleteController():
# retrieve information
hid = session["hid"]
controller_name = request.form["ce"]
controller_id = int(request.form["ce_id"])
# remove the sensor from the sensors table
connection, cursor = getDbCursor()
query = (
"DELETE FROM controller "
"WHERE controller_id = %s"
)
cursor.execute(query, (controller_id,))
connection.commit()
cleanUpDb(connection, cursor)
gc.collect()
flash ('You have successfully removed the ' + controller_name + ' controller.', 'success')
return redirect(url_for('viewDevice'))
def dataLog():
log = request.form['log']
log_dict = json.loads(log)
# event_id, event_dtg, event_hub_id, event_profile_id, event_sensor_type_id, event_state, event_message
# #save the file to the database
connection, cursor = getDbCursor()
query = (
"INSERT INTO event (event_dtg, event_hub_id, event_profile_id, event_sensor_type_id, event_state, event_message) "
"VALUES (%s, %s, %s, %s, %s, %s)"
)
cursor.execute(query, (str(log_dict["event_dtg"]), str(log_dict["event_hub_id"]), str(log_dict["event_profile_id"]), str(log_dict["event_sensor_type_id"]), str(log_dict["event_state"]), str(log_dict["event_message"])))
connection.commit()
cleanUpDb(connection,cursor)
return "success"
def selectPlantProfile():
#get the pid argument. (plant profile id)
args = request.args.to_dict()
profile_id = args["pid"]
hub_id = session["hid"]
#TODO - check pid exists.
#convert to int.
profile_id = int(profile_id)
# set the plant profile for the user to the one from the request URL
connection, cursor = getDbCursor()
query = (
"UPDATE hub "
"SET hub_profile_id = {} "
"WHERE hub_id = {}".format(profile_id, hub_id)
)
cursor.execute(query)
connection.commit()
flash("You have successfully updated your plant profile", "success")
return redirect(url_for('viewDevice'))
def getconfig():
#get arguments
args = request.args.to_dict()
#get hub
mac = args["mac"]
connection, cursor = getDbCursor()
hub = get_hub(mac, cursor)
config = {
'hub': hub,
'controller_type': get_controller_types(cursor),
'controller': get_controllers(hub['hub_id'],cursor),
'sensor_type': get_sensor_types(cursor),
'sensor': get_sensors(hub['hub_id'],cursor),
'profile': get_profile(hub['hub_profile_id'],cursor),
'profile_sensor': get_profile_sensor(hub['hub_profile_id'],cursor),
'lighting_gpio': get_lighting(hub['hub_profile_id'],cursor)
}
#return the config as a JSON object
#TODO -- set content-type to JSON here.
return json.dumps(config)
def selectControl():
flash("NOTE: for the raspberry pi, slot 7 = GPIO 12, 8 = GPIO 16, 9 = GPIO 18, 10 = GPIO 23, 11 = GPIO 24, 12 = GPIO 25")
SENSOR_MAPPING = { 12:7, 16:8, 18:9, 23:10, 24:11, 25:12 }
# retrieve hub id for future queries
hid = session["hid"]
connection, cursor = getDbCursor()
# get possible sensors
query = (
"SELECT controller_type_id, controller_type_name "
"FROM controller_type"
)
cursor.execute(query)
controllers = {}
for controller_type_id, controller_type_name in cursor:
controllers[controller_type_id] = controller_type_name
cleanUpDb(connection, cursor)
gc.collect()
# return a list of current profiles to the user
return render_template("selectcontrol.html", controllers=controllers)
def imageUpload():
# get the hub ID from the params
S3_BUCKET_URL = "https://s3-us-west-2.amazonaws.com/microhort/pictures/"
f = request.files['file']
filename = request.form['filename']
description = request.form['description']
mac = request.form['mac']
#upload the file to s3 bucket
# the file will upload to ../pictures/filename
# this link will be saved in the database, mapping to the hub_id.
botoupload.uploadFileToMicroHortS3(f, filename)
#save the file to the database
connection, cursor = getDbCursor()
# get the owner of the hub && hub_id
query = (
"SELECT hub_id, hub_owner_id "
"FROM hub "
"WHERE hub_mac "
"LIKE ('{}')".format(mac)
)
cursor.execute(query)
hub_id = cursor.fetchone()[1]
link = S3_BUCKET_URL + filename
# save to pictures table.
query = (
"INSERT INTO pictures (hub_id,link,description) "
"VALUES (%s, %s, %s)"
)
cursor.execute(query, (hub_id, link, description))
connection.commit()
cleanUpDb(connection,cursor)
gc.collect()
# return a success message.
return ("success")
def selectSensor():
flash("NOTE: for the raspberry pi, slot 1 = GPIO 5, 2 = GPIO 6, 3 = GPIO 13, 4 = GPIO 17, 5 = GPIO 22, 6 = GPIO 27")
SENSOR_MAPPING = { 5:1, 6:2, 13:3, 17:4, 22:5, 27:6
}
# retrieve hub id for future queries
hid = session["hid"]
connection, cursor = getDbCursor()
# get possible sensors
query = (
"SELECT sensor_type_id, sensor_type_name "
"FROM sensor_type"
)
cursor.execute(query)
sensors = {}
for sensor_type_id, sensor_type_name in cursor:
sensors[sensor_type_id] = sensor_type_name
cleanUpDb(connection, cursor)
gc.collect()
# return a list of current profiles to the user
return render_template("selectsensor.html", sensors=sensors)