def createsession():
if request.method == 'POST':
print("CREATING NEW SESSION!")
print(request.form)
sessionname = request.form['session_name']
print(sessionname)
dev_id = request.form['session_sensor']
print(dev_id)
type = request.form['session_type']
print(type)
color = request.form['color']
print(color)
db = get_db()
error = None
if not sessionname:
error = 'Session name is required.'
elif not dev_id:
error = 'Attatched sensor is required.'
elif not type:
error = 'Define the type of the session.'
sensor_id = db.execute('SELECT id FROM sensor WHERE dev_id = ?',
(dev_id, )).fetchone()[0]
# print("sensor ID", sensor_id)
current_session = db.execute(
'SELECT id FROM session WHERE sensor_id = ?'
'ORDER BY begin_time DESC', (sensor_id, )).fetchone()
if current_session is not None:
timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print("Current session:", current_session[0])
print("Current timestamp", timestamp)
# type_new = 'test'
print("Stopping other session that used the same sensor")
db.execute('UPDATE session SET end_time = ? WHERE id = ?', (
timestamp,
current_session[0],
))
if error is None:
db.execute(
'INSERT INTO session (session_name, type, color, sensor_id) \
VALUES (?, ?, ?, ?)', (sessionname, type, color, sensor_id))
db.commit()
return redirect(url_for('info.index'))
flash(error)
current_app.logger.warning(error)
if request.method == 'GET':
db = get_db()
sensors = db.execute('SELECT dev_id'
' FROM sensor'
' ORDER BY dev_id DESC').fetchall()
return render_template('temperature/session.html', sensors=sensors)
def register():
if request.method == 'POST':
plantname = request.form['plantname']
dev_id = request.form['dev_id']
location = request.form['location']
color = request.form['color']
db = get_db()
error = None
if not plantname:
error = 'Plantname is required.'
elif not dev_id:
error = 'Device_id is required.'
elif not location:
error = 'Location is required.'
elif not color:
error = 'Color is required.'
elif db.execute('SELECT id FROM plant WHERE dev_id = ?',
(dev_id, )).fetchone() is not None:
error = 'Device {} already exists.'.format(dev_id)
elif db.execute('SELECT id FROM plant WHERE color = ?',
(color, )).fetchone() is not None:
error = 'Color {} is already in use.'.format(color)
if error is None:
db.execute(
'INSERT INTO plant (dev_id, plantname, location, color) VALUES (?, ?, ?, ?)',
(dev_id, plantname, location, color))
db.commit()
return redirect(url_for('info.index'))
flash(error)
current_app.logger.warning(error)
return render_template('ttn/register.html')
def submit():
if request.method == 'POST':
current_app.logger.info(request.get_json())
data = request.get_json()
metadata = data["metadata"]
date = pd.to_datetime(metadata['time']).strftime('%Y-%m-%d %H:%M:%S')
dev_id = data['dev_id']
payload = data['payload_fields']
water = payload['water']
voltage = payload['voltage']
db = get_db()
error = None
if not water:
error = 'Water is required.'
elif not voltage:
error = 'Voltage is required.'
plant_id = db.execute('SELECT id FROM plant WHERE dev_id = ?',
(dev_id, )).fetchone()
if plant_id is None:
error = 'Device {} is not yet registered.'.format(dev_id)
if error is None:
db.execute(
'INSERT INTO status (plant_id, water, voltage, date_tx) VALUES (?, ?, ?, ?)',
(plant_id[0], water, voltage, date))
db.commit()
else:
current_app.logger.warning(error)
return request.query_string
def register():
if request.method == 'POST':
sensorname = request.form['sensorname']
dev_id = request.form['dev_id']
db = get_db()
error = None
if not sensorname:
error = 'Sensorname is required.'
elif not dev_id:
error = 'Device_id is required.'
elif db.execute('SELECT id FROM sensor WHERE dev_id = ?',
(dev_id, )).fetchone() is not None:
error = 'Device {} already exists.'.format(dev_id)
if error is None:
db.execute('INSERT INTO sensor (dev_id, sensorname) VALUES (?, ?)',
(dev_id, sensorname))
db.commit()
return redirect(url_for('info.index'))
flash(error)
current_app.logger.warning(error)
return render_template('temperature/register.html')
def get_data():
db = get_db()
c = db.cursor()
plants = c.execute('SELECT plantname, dev_id, color, location'
' FROM plant').fetchall()
plants = [
dict(zip([key[0] for key in c.description], row)) for row in plants
]
today = date.today()
last_n_days = 7
day_offset = today - timedelta(days=last_n_days)
month_ago = today - timedelta(days=31)
water = dict()
for plant in plants:
max_water = c.execute(
'SELECT MAX(water)'
' FROM status s JOIN plant p on s.plant_id = p.id'
' WHERE dev_id = ? AND date_tx >= ?', (
plant['dev_id'],
month_ago,
)).fetchone()
if max_water is None:
max_water = 1
else:
max_water = max_water['MAX(water)']
results = c.execute(
'SELECT water, date_tx'
' FROM status s JOIN plant p on s.plant_id = p.id'
' WHERE dev_id = ?' # ' AND date_tx >= ?'
' ORDER BY date_tx ASC',
(
plant['dev_id'],
day_offset,
)).fetchall()
water[plant['dev_id']] = [
dict([('x',
(datetime.strptime(row['date_tx'], '%Y-%m-%d %H:%M:%S') +
timedelta(hours=2)).strftime('%Y-%m-%d %H:%M:%S')),
('y', round(row['water'] / max_water * 100, 2))])
for row in results
]
if len(water[plant['dev_id']]) > 0 and water[
plant['dev_id']][-1]['y'] < 20:
plant['danger_level'] = 1
else:
plant['danger_level'] = 0
return jsonify(water=water, plants=plants)
def monitor(session):
db = get_db()
sessions = db.execute(
'SELECT id, session_name, sensor_id, color, begin_time, end_time, type'
' FROM session'
' ORDER BY id DESC').fetchall()
selected_session = 0
if session is not None:
for i in range(len(sessions)):
if sessions[i]['id'] == int(session):
selected_session = i
return render_template('info/monitor.html',
sessions=sessions,
selected_session=selected_session)
def submit():
if request.method == 'POST':
current_app.logger.info(request.get_json())
data = request.get_json()
# print(data)
metadata = data["metadata"]
date = pd.to_datetime(metadata['time']).strftime('%Y-%m-%d %H:%M:%S')
dev_id = data['dev_id']
payload = data['payload_fields']
temperature = payload['temperature']
# print(temperature)
voltage = payload['voltage']
db = get_db()
error = None
if not temperature:
error = 'Temperature is required.'
elif not voltage:
error = 'Voltage is required.'
sensor_id = db.execute('SELECT id FROM sensor WHERE dev_id = ?',
(dev_id, )).fetchone()
session_id = None
if sensor_id is None:
error = 'Device {} is not yet registered.'.format(dev_id)
else:
print("sersor ID:", sensor_id[0])
session_id = db.execute(
'SELECT id FROM session WHERE sensor_id = ? ORDER BY begin_time DESC',
(sensor_id[0], )).fetchone()
if session_id is None:
error = 'Device {} is not yet used by any session.'.format(
dev_id)
if error is None:
db.execute(
'INSERT INTO status(sensor_id, session_id, temperature, voltage, date_tx)'
' VALUES(?, ?, ?, ?, ?)',
(sensor_id[0], session_id[0], temperature, voltage, date))
db.commit()
else:
current_app.logger.warning(error)
return request.query_string
def get_data():
db = get_db()
c = db.cursor()
sessions = c.execute('SELECT id, session_name'
' FROM session'
' ORDER BY id DESC').fetchall()
sessions = [
dict(zip([key[0] for key in c.description], row)) for row in sessions
]
today = date.today()
last_n_days = 7
day_offset = today - timedelta(days=last_n_days)
# month_ago = today - timedelta(days=31)
temperature = dict()
for session in sessions:
# max_temperature = c.execute(
# 'SELECT MAX(temperature)'
# ' FROM status s JOIN sensor p on s.sensor_id = p.id'
# ' WHERE dev_id = ? AND date_tx >= ?', (sensor['dev_id'], month_ago,)
# ).fetchone()
# if max_temperature is None:
# max_temperature = 1
# else:
# max_temperature = max_temperature['MAX(temperature)']
results = c.execute(
'SELECT temperature, date_tx'
' FROM status s JOIN session p on s.session_id = p.id'
' WHERE session_id = ? AND date_tx >= ?'
' ORDER BY date_tx ASC', (
session['id'],
day_offset,
)).fetchall()
# print(results)
temperature[session['id']] = [
dict([('x',
(datetime.strptime(row['date_tx'], '%Y-%m-%d %H:%M:%S') +
timedelta(hours=1)).strftime('%Y-%m-%d %H:%M:%S')),
('y', row['temperature'])]) for row in results
]
return jsonify(temperature=temperature, sessions=sessions)
def current_temperature():
db = get_db()
c = db.cursor()
sessions = c.execute('SELECT id, session_name'
' FROM session'
' ORDER BY id DESC').fetchall()
sessions = [
dict(zip([key[0] for key in c.description], row)) for row in sessions
]
# print('GETTING CURRENT TEMP')
current_temp = dict()
for session in sessions:
temp = c.execute(
'SELECT temperature '
'FROM status s JOIN session p on s.session_id = p.id'
' WHERE session_id = ?'
' ORDER BY date_tx DESC', (session['id'], )).fetchone()
current_temp[session['id']] = temp[0]
return jsonify(current_temp=current_temp)