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