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