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 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 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 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 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 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 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 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 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)