def update_actuator_state(actuator_id, out_table, state): boolean = (str(state).lower() == "true") string = "on" if state else "off" sql_statement = "UPDATE TABLENAME SET state=%s WHERE actuatorId=%s;" sql_statement = sql_statement.replace("TABLENAME", out_table) #scream(sql_statement) helper_functions.execute_SQL_oneoff(sql_statement, (string, actuator_id))
def solenoid_last_on_time(location_id): actuator_id = helper_functions.execute_SQL_oneoff( "SELECT actuatorId FROM LocationMapping WHERE locationId=%s;", (location_id, ))[0][0] last_on_time = helper_functions.execute_SQL_oneoff( "SELECT lastOnTime FROM SolenoidOut WHERE actuatorId=%s", (actuator_id, ))[0][0] print last_on_time last_on_time = int(last_on_time) return last_on_time
def actuate_relay(actuator_id, out_table, state): state = (str(state).lower() == "true") if actuator_state_match(actuator_id, out_table, state): print "actuator %s already %s" % (actuator_id, state) return else: print "actuator %s needs to be switched" % actuator_id sql_statement = "SELECT channelId, relayId FROM TABLENAME WHERE actuatorId=%s" sql_statement = sql_statement.replace("TABLENAME", out_table) relay, relay_id = helper_functions.execute_SQL_oneoff(sql_statement, (actuator_id,))[0] port = helper_functions.execute_SQL_oneoff("SELECT portNumber FROM RelayOut WHERE relayId=%s;", (relay_id,))[0][0] scream("ATTEMPTING TO ACTUATE (%s) WITH ACTUATORID %s to %s" % (str(out_table).replace("Out", ""), actuator_id, state)) relay_actuator.state_set(port, relay, state) update_actuator_state(actuator_id, out_table, state)
def retrieve(greenhouse_id): try: sensor_id = helper_functions.execute_SQL_oneoff("SELECT MAX(sensorId * 1) FROM SoilSensors;")[0][0] sensor_id = str(int(sensor_id) + 1) node_id = str((int(sensor_id) % 254)) node_family = str(int(sensor_id) // 255) location_id = str(int(helper_functions.execute_SQL_oneoff("SELECT MAX(locationId * 1) FROM SoilSensors;")[0][0]) + 1) helper_functions.execute_SQL_oneoff("INSERT INTO SoilSensors VALUES(%s, %s, %s);", (sensor_id, location_id, greenhouse_id)) return (node_id, node_family) except Exception as e: print "Exception: %s" % str(e)
def greenhouse_on_auto(greenhouse_id): bool_str = helper_functions.execute_SQL_oneoff( "SELECT automaticControl FROM GreenhouseStatus WHERE greenhouseId=%s;", (greenhouse_id, )) if bool_str == () or bool_str == None or bool_str == "": return True bool_str = bool_str[0][0] return (bool_str.lower() == "true")
def actuator_state_match(actuator_id, out_table, state): sql_statement = "SELECT state FROM TABLENAME WHERE actuatorId=%s" sql_statement = sql_statement.replace("TABLENAME", out_table) result = helper_functions.execute_SQL_oneoff(sql_statement, (actuator_id,))[0][0] scream("state : " + str(state)) scream("state : " + str(state)) return (result == state)
def insert_global_reading(reading, greenhouse_id, data_table_name): unix_timestamp = int(1000 * time.time()) #in ms sensor_table_name = data_table_name.replace("Data", "Sensors") #get sensor id sql_statement = "SELECT sensorId FROM TABLENAME WHERE greenhouseId=%s;" sql_statement = sql_statement.replace("TABLENAME", sensor_table_name) sensor_id = helper_functions.execute_SQL_oneoff(sql_statement, (str(greenhouse_id), )) if sensor_id == () or sensor_id == None: return sensor_id = sensor_id[0][0] #store db entry sql_statement = "INSERT INTO TABLENAME VALUES(%s, %s, %s, %s);" sql_statement = sql_statement.replace("TABLENAME", data_table_name) helper_functions.execute_SQL_oneoff( sql_statement, (sensor_id, reading, str(unix_timestamp), greenhouse_id))
def monitor_loop(): while True: time_now = str(datetime.now().strftime('%H:%M:%S')) print "=================STARTING MONITOR CYCLE (%s)=============================" % time_now #airtemp sample_period = 15 #seconds preferred_airtemp = 25 #in celsius readings = get_recent_readings("AirTempData") for row in readings: try: if not greenhouse_on_auto(row[3]): continue reading = int(row[1]) if reading > preferred_airtemp: print "HERE IS WHERE I WILL TURN ON THE FANS FOR GREENHOUSE %s" % row[ 3] else: print "HERE IS WHERE I WILL TURN OFF THE FANS FOR GREENHOUSE %s" % row[ 3] actuate_global_relay(row[3], "FAN", "VentilationOut", (reading > preferred_airtemp)) except Exception as e: scream("airtemp monitoring exception caught\n%s" % str(e)) traceback.print_exc() #light level minimum_lightlevel = 100 #reading from light sensor, ranges from 0 to 1024 dusk_hour = 20 #8:00 PM dawn_hour = 5 #5:00 AM readings = get_recent_readings("LightData") try: if not greenhouse_on_auto('3'): continue #reading = int(row[1]) current_hour = datetime.now().hour if (current_hour > dusk_hour or current_hour < dawn_hour): print "HERE IS WHERE I WILL TURN ON THE LIGHTS FOR GREENHOUSE %s" % '3' else: print "HERE IS WHERE I WILL TURN OFF THE LIGHTS FOR GREENHOUSE %s" % '3' actuate_global_relay( '3', "LIGHT", "LightOut", (current_hour > dusk_hour or current_hour < dawn_hour)) except Exception as e: scream("lightlevel monitoring exception caught\n%s" % str(e)) traceback.print_exc() #soil moisture maximum_dryness = 900 #reading from soil moisture sensor, ranges from 0 to 1024 (1024 == DRYER THAN YOUR MOM) readings = get_recent_readings("SoilData") for row in readings: unix_timestamp = int(1000 * time.time()) #in ms try: if not greenhouse_on_auto(get_greenhouse(row[1])): continue #if location was watered in the last 10 mins, dont water if (int(unix_timestamp) - int(solenoid_last_on_time(row[1])) < 1000 * 60 * 10): print "location %s was watered in the last 10 mins, dont water" % row[ 1] continue reading = int(row[2]) if reading > maximum_dryness: print "HERE IS WHERE I WILL TURN ON THE WATER FOR SOIL LOCATION %s for 20 seconds" % row[ 1] if (reading > maximum_dryness): actuate_local_relay(row[1], "SolenoidOut", True) time.sleep(20) actuate_local_relay(row[1], "SolenoidOut", False) helper_functions.execute_SQL_oneoff( "UPDATE SolenoidOut SET lastOnTime=%s WHERE actuatorId=%s", (unix_timestamp, get_actuator_id(row[1]))) except Exception as e: scream("soilmoisture monitoring exception caught\n%s" % str(e)) traceback.print_exc() #water level maximum_dryness = 800 #reading from soil moisture sensor, ranges from 0 to 1024 (1024 == DRY -> water is low) readings = get_recent_readings("WaterLevelData") for row in readings: unix_timestamp = int(1000 * time.time()) #in ms try: if not greenhouse_on_auto(get_greenhouse(row[1])): continue #if location was watered in the last 10 mins, dont water if (int(unix_timestamp) - int(solenoid_last_on_time(row[1])) < 1000 * 60 * 10): print "location %s was watered in the last 10 mins, dont water" % row[ 1] continue reading = int(row[2]) if reading > maximum_dryness: print "HERE IS WHERE I WILL TURN ON THE WATER FOR HYDROPONIC LOCATION %s" % row[ 1] else: print "HERE IS WHERE I WILL TURN OFF THE WATER FOR HYDROPONIC LOCATION %s" % row[ 1] #actuate_local_relay(row[1], "SolenoidOut", (reading > maximum_dryness)) if (reading > maximum_dryness): actuate_local_relay(row[1], "SolenoidOut", True) time.sleep(15) actuate_local_relay(row[1], "SolenoidOut", False) except Exception as e: scream("waterlevel monitoring exception caught\n%s" % str(e)) traceback.print_exc() print "=================ENDING MONITOR CYCLE (ON STANDBY)==================" time.sleep(sample_period) helper_functions.scream("ENDED MONITOR LOOP, NOOOOOOO!!!!")
def get_actuator_id(location_id): return helper_functions.execute_SQL_oneoff( "SELECT actuatorId FROM LocationMapping WHERE locationId=%s;", (location_id, ))[0][0]
def get_recent_readings(table_name): sql_statement = "SELECT * FROM TABLENAME AS s1 WHERE unixTimestamp = (SELECT MAX(unixTimestamp * 1) FROM TABLENAME AS s2 WHERE s1.sensorId = s2.sensorId);" sql_statement = sql_statement.replace("TABLENAME", table_name) #scream(helper_functions.execute_SQL_oneoff(sql_statement, ())) return helper_functions.execute_SQL_oneoff(sql_statement, ())
def actuate_local_relay(location_id, out_table, state): actuator_id = helper_functions.execute_SQL_oneoff( "SELECT actuatorId FROM LocationMapping WHERE locationId=%s;", (location_id, ))[0][0] actuate_relay(actuator_id, out_table, state)
def actuate_global_relay(greenhouse_id, actuator_type, out_table, state): actuator_ids = helper_functions.execute_SQL_oneoff( "SELECT actuatorId FROM GlobalMapping WHERE greenhouseId=%s AND actuatorType=%s;", (greenhouse_id, actuator_type)) for actuator_id in actuator_ids: actuate_relay(actuator_id[0], out_table, state)
def get_greenhouse(location_id): return helper_functions.execute_SQL_oneoff( "SELECT greenhouseId FROM LocationMapping WHERE locationId=%s;", (location_id, ))[0][0]
def mahapatras_greenhouse(): helper_functions.scream("now initializing mahapatra's greenhouse") #username, firstname, lastname, email, password execute_SQL_oneoff( "INSERT INTO Users VALUES('mahapatra', 'Rabi', 'Mahapatra', 'default', 'password')" ) #greenhouseId, username, housenumber, street, city, zip, state execute_SQL_oneoff( "INSERT INTO Greenhouses VALUES('3', 'mahapatra', '3504', 'Graz Dr.', 'College Station', '77840', 'TX')" ) execute_SQL_oneoff("INSERT INTO GreenhouseStatus VALUES('3', 'true')") #type, locationId, greenhouseId execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '301', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '302', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '303', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '304', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '305', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '306', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '307', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '308', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '309', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '310', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '311', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '312', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('pot', '313', '3')") execute_SQL_oneoff("INSERT INTO Location VALUES('hydroponic', '314', '3')") #relayId, piIp, portNumber, numberOfChannels, greenhouseId #realy 31 is the solenoid controller #relay 32 is the lights controller and ventilation execute_SQL_oneoff( "INSERT INTO RelayOut VALUES('31', '192.168.0.101', '11001', '8', '3')" ) execute_SQL_oneoff( "INSERT INTO RelayOut VALUES('32', '192.168.0.108', '11002', '8', '3')" ) #actuatorId, solenoidType, relayId, channelId, greenhouseId, state #solenoids 10-15 are for soil pots #solenoid 16 is for the hydroponic tower #solenoid 17 is the main one execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('10', 'maindrip', '31', '1', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('11', 'drip', '31', '2', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('12', 'drip', '31', '3', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('13', 'drip', '31', '4', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('14', 'drip', '31', '5', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('15', 'drip', '31', '6', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('16', 'drip', '31', '7', '3', 'off', '0')" ) execute_SQL_oneoff( "INSERT INTO SolenoidOut VALUES('17', 'hydroponic', '31', '8', '3', 'off', '0')" ) #locationId, actuatorId, greenhouseId #mapping for the solenoids #most solenoids support 2 pots (except solenoid 15 which does 3) execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('301', '16', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('302', '16', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('303', '11', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('304', '11', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('305', '12', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('306', '12', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('307', '13', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('308', '15', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('309', '14', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('310', '14', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('311', '15', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('312', '15', '3')") execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('313', '16', '3')") #hydroponic tower location execute_SQL_oneoff("INSERT INTO LocationMapping VALUES('314', '17', '3')") #actuatorId, relayId, channelId, greenhouseId, state execute_SQL_oneoff( "INSERT INTO VentilationOut VALUES('18', '32', '1', '3', 'off')") execute_SQL_oneoff( "INSERT INTO VentilationOut VALUES('19', '32', '2', '3', 'off')") execute_SQL_oneoff( "INSERT INTO VentilationOut VALUES('20', '32', '3', '3', 'off')") execute_SQL_oneoff( "INSERT INTO VentilationOut VALUES('21', '32', '4', '3', 'off')") #actuatorId, relayId, channelId, greenhouseId, state execute_SQL_oneoff( "INSERT INTO LightOut VALUES('22', '32', '5', '3', 'off')") execute_SQL_oneoff( "INSERT INTO LightOut VALUES('23', '32', '6', '3', 'off')") execute_SQL_oneoff( "INSERT INTO LightOut VALUES('24', '32', '7', '3', 'off')") #greenhouseId, actuatorType, actuatorId execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'FAN', '18')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'FAN', '19')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'FAN', '20')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'FAN', '21')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'LIGHT', '22')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'LIGHT', '23')") execute_SQL_oneoff("INSERT INTO GlobalMapping VALUES('3', 'LIGHT', '24')") #sensorId, greenhouseId execute_SQL_oneoff("INSERT INTO AirTempSensors VALUES('31', '3')") execute_SQL_oneoff("INSERT INTO HumiditySensors VALUES('31', '3')") execute_SQL_oneoff("INSERT INTO LightSensors VALUES('31', '3')") #sensorId, locationId, greenhouseId execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('34', '301', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('35', '302', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('36', '303', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('37', '304', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('38', '305', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('39', '306', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('40', '307', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('41', '308', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('42', '309', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('43', '310', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('44', '311', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('45', '312', '3')") execute_SQL_oneoff("INSERT INTO SoilSensors VALUES('46', '313', '3')") #sensorId, locationId, greenhouseId execute_SQL_oneoff( "INSERT INTO pHSensors VALUES('47', '314', '3')") #sensorId, locationId, greenhouseId execute_SQL_oneoff( "INSERT INTO WaterLevelSensors VALUES('47', '314', '3')") #sensorId, locationId, greenhouseId execute_SQL_oneoff( "INSERT INTO WaterTempSensors VALUES('47', '314', '3')")