def get_stark_data(sensor_id):
"""
Produces a JSON with Stark readings data for a specified sensor (meter).
Args:
sensor_id - Stark meter ID
Returns:
result - JSON string
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
query = (db.session.query(
ReadingsEnergyClass.sensor_id,
ReadingsEnergyClass.timestamp,
ReadingsEnergyClass.electricity_consumption,
ReadingsEnergyClass.time_created,
ReadingsEnergyClass.time_updated,
).filter(
and_(
ReadingsEnergyClass.sensor_id == sensor_id,
ReadingsEnergyClass.timestamp >= dt_from,
ReadingsEnergyClass.timestamp <= dt_to,
)).order_by(desc(ReadingsEnergyClass.timestamp)))
execute_result = db.session.execute(query).fetchall()
result = jasonify_query_result(execute_result)
return result
def get_advanticsys_data(sensor_id):
"""
Produces a JSON with the Advanticsys sensor data for a specified sensor.
Args:
sensor_id - Advanticsys sensor ID
Returns:
result - JSON string
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
query = (db.session.query(
ReadingsAdvanticsysClass.sensor_id,
ReadingsAdvanticsysClass.timestamp,
ReadingsAdvanticsysClass.temperature,
ReadingsAdvanticsysClass.humidity,
ReadingsAdvanticsysClass.co2,
ReadingsAdvanticsysClass.time_created,
ReadingsAdvanticsysClass.time_updated,
).filter(
and_(
ReadingsAdvanticsysClass.sensor_id == sensor_id,
ReadingsAdvanticsysClass.timestamp >= dt_from,
ReadingsAdvanticsysClass.timestamp <= dt_to,
)).order_by(desc(ReadingsAdvanticsysClass.timestamp)))
execute_result = db.session.execute(query).fetchall()
result = jasonify_query_result(execute_result)
return result
def get_weather():
"""
Produces a JSON with weather data.
Returns:
result - JSON string
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
query = (
db.session.query(
ReadingsWeatherClass.temperature,
ReadingsWeatherClass.relative_humidity,
ReadingsWeatherClass.wind_speed,
ReadingsWeatherClass.wind_direction,
ReadingsWeatherClass.rain,
ReadingsWeatherClass.air_pressure,
ReadingsWeatherClass.timestamp,
ReadingsWeatherClass.icon,
)
.filter(
and_(
ReadingsWeatherClass.timestamp >= dt_from,
ReadingsWeatherClass.timestamp <= dt_to,
)
)
.order_by(desc(ReadingsWeatherClass.timestamp))
)
execute_result = db.session.execute(query).fetchall()
result = jasonify_query_result(execute_result)
return result
def get_aranet_airvelocity_data(sensor_id):
"""
Produces a JSON with the Aranet air velocity
data for a specified sensor.
Args:
sensor_id - sensor ID as stored in CROP db (int).
Returns:
result - JSON string
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
query = (
db.session.query(
ReadingsAranetAirVelocityClass.sensor_id,
ReadingsAranetAirVelocityClass.timestamp,
ReadingsAranetAirVelocityClass.current,
ReadingsAranetAirVelocityClass.air_velocity,
ReadingsAranetAirVelocityClass.time_created,
ReadingsAranetAirVelocityClass.time_updated,
)
.filter(
and_(
ReadingsAranetAirVelocityClass.sensor_id == sensor_id,
ReadingsAranetAirVelocityClass.timestamp >= dt_from,
ReadingsAranetAirVelocityClass.timestamp <= dt_to,
)
)
.order_by(desc(ReadingsAranetAirVelocityClass.timestamp))
)
execute_result = db.session.execute(query).fetchall()
result = jasonify_query_result(execute_result)
return result
def route_template(template):
"""
Main method to render templates.
"""
if request.method == "GET":
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
if template in ["advanticsys", "energy"]:
if template == "advanticsys":
query = (db.session.query(
ReadingsAdvanticsysClass.timestamp,
SensorClass.id,
ReadingsAdvanticsysClass.temperature,
ReadingsAdvanticsysClass.humidity,
ReadingsAdvanticsysClass.co2,
ReadingsAdvanticsysClass.time_created,
ReadingsAdvanticsysClass.time_updated,
).filter(
and_(
ReadingsAdvanticsysClass.sensor_id == SensorClass.id,
ReadingsAdvanticsysClass.timestamp >= dt_from,
ReadingsAdvanticsysClass.timestamp <= dt_to,
)).order_by(desc(
ReadingsAdvanticsysClass.timestamp)).limit(
CONST_MAX_RECORDS))
elif template == "energy":
query = (db.session.query(
ReadingsEnergyClass.timestamp,
SensorClass.id,
ReadingsEnergyClass.electricity_consumption,
ReadingsEnergyClass.time_created,
).filter(
and_(
ReadingsEnergyClass.sensor_id == SensorClass.id,
ReadingsEnergyClass.timestamp >= dt_from,
ReadingsEnergyClass.timestamp <= dt_to,
)).order_by(desc(ReadingsEnergyClass.timestamp)).limit(
CONST_MAX_RECORDS))
readings = db.session.execute(query).fetchall()
results_arr = query_result_to_array(readings, date_iso=False)
return render_template(
template + ".html",
readings=results_arr,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
return None
def aranet_trh_dashboard():
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
num_sensors, temperature_bins_json = aranet_trh_analysis(dt_from, dt_to)
return render_template(
"aranet_trh_dashboard.html",
num_sensors=num_sensors,
temperature_bins_json=temperature_bins_json,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
def energy_dashboard():
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
energy_data = {}
# lights-on analysis
lights_results_df = lights_energy_use(dt_from, dt_to)
# ventilation analysis
ventilation_results_df = ventilation_energy_use(dt_from, dt_to)
# jsonify
energy_data["data"] = ("[" + lights_results_df.to_json(orient="records") +
"," +
ventilation_results_df.to_json(orient="records") +
"]")
return render_template(
"energy_dashboard.html",
energy_data=energy_data,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
def route_template(template):
"""
Main method to render templates.
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
if template in ["advanticsys", "energy", "aranet_trh", "dailyharvest"]:
if template == "advanticsys":
query = (db.session.query(
ReadingsAdvanticsysClass.timestamp,
SensorClass.id,
SensorClass.name,
ReadingsAdvanticsysClass.temperature,
ReadingsAdvanticsysClass.humidity,
ReadingsAdvanticsysClass.co2,
ReadingsAdvanticsysClass.time_created,
ReadingsAdvanticsysClass.time_updated,
ReadingsAdvanticsysClass.sensor_id,
).filter(
and_(
ReadingsAdvanticsysClass.sensor_id == SensorClass.id,
ReadingsAdvanticsysClass.timestamp >= dt_from,
ReadingsAdvanticsysClass.timestamp <= dt_to,
)).order_by(desc(ReadingsAdvanticsysClass.timestamp)).limit(
CONST_MAX_RECORDS))
elif template == "energy":
query = (db.session.query(
ReadingsEnergyClass.timestamp,
SensorClass.id,
SensorClass.name,
TypeClass.sensor_type,
ReadingsEnergyClass.electricity_consumption,
ReadingsEnergyClass.time_created,
).filter(
and_(
SensorClass.type_id == TypeClass.id,
ReadingsEnergyClass.sensor_id == SensorClass.id,
ReadingsEnergyClass.timestamp >= dt_from,
ReadingsEnergyClass.timestamp <= dt_to,
)).order_by(desc(
ReadingsEnergyClass.timestamp)).limit(CONST_MAX_RECORDS))
elif template == "aranet_trh":
query = (db.session.query(
ReadingsAranetTRHClass.timestamp,
SensorClass.name,
ReadingsAranetTRHClass.temperature,
ReadingsAranetTRHClass.humidity,
ReadingsAranetTRHClass.time_created,
ReadingsAranetTRHClass.time_updated,
ReadingsAranetTRHClass.sensor_id,
).filter(
and_(
ReadingsAranetTRHClass.sensor_id == SensorClass.id,
ReadingsAranetTRHClass.timestamp >= dt_from,
ReadingsAranetTRHClass.timestamp <= dt_to,
)).order_by(desc(
ReadingsAranetTRHClass.timestamp)).limit(CONST_MAX_RECORDS)
)
elif template == "dailyharvest":
query = db.session.query(
DailyHarvestClass.crop,
DailyHarvestClass.propagation_date,
DailyHarvestClass.location_id,
DailyHarvestClass.stack,
DailyHarvestClass.total_yield_weight,
DailyHarvestClass.disease_trays,
DailyHarvestClass.defect_trays,
DailyHarvestClass.notes,
DailyHarvestClass.user,
DailyHarvestClass.time_created,
).limit(CONST_MAX_RECORDS)
readings = db.session.execute(query).fetchall()
results_arr = query_result_to_array(readings, date_iso=False)
if request.method == "POST":
return download_csv(readings, template)
else:
return render_template(
template + ".html",
readings=results_arr,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
num_records=CONST_MAX_RECORDS,
)
def route_template(template):
"""
Renders templates
"""
if template == "temperature":
adv_sensors_temp = {}
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
# advanticsys
query = (db.session.query(
ReadingsAdvanticsysClass.timestamp,
ReadingsAdvanticsysClass.sensor_id,
SensorClass.id,
ReadingsAdvanticsysClass.temperature,
ReadingsAdvanticsysClass.humidity,
ReadingsAdvanticsysClass.co2,
).filter(
and_(
ReadingsAdvanticsysClass.sensor_id == SensorClass.id,
ReadingsAdvanticsysClass.timestamp >= dt_from,
ReadingsAdvanticsysClass.timestamp <= dt_to,
)))
df = pd.read_sql(query.statement, query.session.bind)
if not df.empty:
# unique sensors
adv_sensors = df.sensor_id.unique()
adv_sensors_modbus_ids = df.id.unique()
# extracting date from datetime
df['date'] = pd.to_datetime(df['timestamp'].dt.date)
# Reseting index
df.sort_values(by=['timestamp'],
ascending=True).reset_index(inplace=True)
# grouping data by date-hour and sensor id
adv_grp = df.groupby(by=[
df.timestamp.map(lambda x: "%04d-%02d-%02d-%02d" % (
x.year, x.month, x.day, x.hour)), 'sensor_id', 'date'
])
# estimating hourly temperature mean values
adv_grp_temp = adv_grp['temperature'].mean().reset_index()
# binning temperature values
adv_grp_temp['temp_bin'] = pd.cut(adv_grp_temp['temperature'],
TEMP_BINS)
# converting bins to str
adv_grp_temp['temp_bin'] = adv_grp_temp['temp_bin'].astype(str)
# get bin counts for each sensor-day combination
adv_grp_date = adv_grp_temp.groupby(
by=['sensor_id', 'date', 'temp_bin'])
adv_cnt = adv_grp_date['temperature'].count().reset_index()
adv_cnt.rename(columns={'temperature': 'temp_cnt'}, inplace=True)
json_data = []
for adv_sensor_id in adv_sensors:
adv_cnt_sensor = adv_cnt[adv_cnt['sensor_id'] == adv_sensor_id]
del adv_cnt_sensor['sensor_id']
# Adding missing date/temp_bin combos
bins_list, df_list = resample(adv_cnt_sensor, TEMP_BINS,
dt_from, dt_to)
bins_json = []
for i in range(len(bins_list)):
bin_range = bins_list[i]
temp_bin_df = df_list[i]
temp_bin_df['date'] = pd.to_datetime(
temp_bin_df['date'],
format='%Y-%m-%d').dt.strftime('%Y-%m-%d')
bins_json.append('["' + bin_range + '",' +
temp_bin_df.to_json(orient='records') +
']')
json_data.append('[' + ','.join(bins_json) + ']')
adv_sensors_temp['data'] = '[' + ','.join(json_data) + ']'
else:
adv_sensors_modbus_ids = []
return render_template(
template + ".html",
num_adv_sensors=len(adv_sensors_modbus_ids),
adv_sensors=adv_sensors_modbus_ids,
adv_sensors_temp=adv_sensors_temp,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
return render_template(template + ".html")
def route_template(template):
"""
Renders templates
"""
dt_from, dt_to = parse_date_range_argument(request.args.get("range"))
if template == "advanticsys_dashboard":
adv_sensors_temp = {}
# advanticsys
query = db.session.query(
ReadingsAdvanticsysClass.timestamp,
ReadingsAdvanticsysClass.sensor_id,
SensorClass.id,
ReadingsAdvanticsysClass.temperature,
ReadingsAdvanticsysClass.humidity,
ReadingsAdvanticsysClass.co2,
).filter(
and_(
ReadingsAdvanticsysClass.sensor_id == SensorClass.id,
ReadingsAdvanticsysClass.timestamp >= dt_from,
ReadingsAdvanticsysClass.timestamp <= dt_to,
))
df = pd.read_sql(query.statement, query.session.bind)
if not df.empty:
# unique sensors
adv_sensors = df.sensor_id.unique()
adv_sensors_modbus_ids = df.id.unique()
# extracting date from datetime
df["date"] = pd.to_datetime(df["timestamp"].dt.date)
# Reseting index
df.sort_values(by=["timestamp"],
ascending=True).reset_index(inplace=True)
# grouping data by date-hour and sensor id
adv_grp = df.groupby(by=[
df.timestamp.map(lambda x: "%04d-%02d-%02d-%02d" %
(x.year, x.month, x.day, x.hour)),
"sensor_id",
"date",
])
# estimating hourly temperature mean values
adv_grp_temp = adv_grp["temperature"].mean().reset_index()
# binning temperature values
adv_grp_temp["temp_bin"] = pd.cut(adv_grp_temp["temperature"],
TEMP_BINS)
# converting bins to str
adv_grp_temp["temp_bin"] = adv_grp_temp["temp_bin"].astype(str)
# get bin counts for each sensor-day combination
adv_grp_date = adv_grp_temp.groupby(
by=["sensor_id", "date", "temp_bin"])
adv_cnt = adv_grp_date["temperature"].count().reset_index()
adv_cnt.rename(columns={"temperature": "temp_cnt"}, inplace=True)
json_data = []
for adv_sensor_id in adv_sensors:
adv_cnt_sensor = adv_cnt[adv_cnt["sensor_id"] == adv_sensor_id]
del adv_cnt_sensor["sensor_id"]
# Adding missing date/temp_bin combos
bins_list, df_list = resample(adv_cnt_sensor, TEMP_BINS,
dt_from, dt_to)
bins_json = []
for i, bin_range in enumerate(bins_list):
temp_bin_df = df_list[i]
temp_bin_df["date"] = pd.to_datetime(
temp_bin_df["date"],
format="%Y-%m-%d").dt.strftime("%Y-%m-%d")
bins_json.append('["' + bin_range + '",' +
temp_bin_df.to_json(orient="records") +
"]")
json_data.append("[" + ",".join(bins_json) + "]")
adv_sensors_temp["data"] = "[" + ",".join(json_data) + "]"
else:
adv_sensors_modbus_ids = []
return render_template(
template + ".html",
num_adv_sensors=len(adv_sensors_modbus_ids),
adv_sensors=adv_sensors_modbus_ids,
adv_sensors_temp=adv_sensors_temp,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
elif template == "zensie_dashboard":
sensor_names, sensor_temp_ranges = zensie_analysis(dt_from, dt_to)
return render_template(
template + ".html",
num_zensie_sensors=len(sensor_names),
zensie_sensors=sensor_names,
zensie_temp_ranges=sensor_temp_ranges,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
elif template == "energy_dashboard":
energy_data = {}
# lights-on analysis
lights_results_df = lights_energy_use(dt_from, dt_to)
# ventilation analysis
ventilation_results_df = ventilation_energy_use(dt_from, dt_to)
# jsonify
energy_data["data"] = (
"[" + lights_results_df.to_json(orient="records") + "," +
ventilation_results_df.to_json(orient="records") + "]")
return render_template(
template + ".html",
energy_data=energy_data,
dt_from=dt_from.strftime("%B %d, %Y"),
dt_to=dt_to.strftime("%B %d, %Y"),
)
return render_template(template + ".html")
