def create():
db = get_db()
sensores = db.execute(
'SELECT se.id as id, se.name as name, site_id, si.name as site'
' FROM sensor se JOIN site si ON se.site_id=si.id'
' ORDER BY si.id,se.id ASC'
).fetchall()
if request.method == 'POST':
name = request.form['name']
sensor_id = request.form['sensor_id']
db = get_db()
error = None
if not name:
error = 'El nombre del actuador es requerido.'
elif db.execute(
'SELECT id FROM actuator WHERE name = ?', (name,)
).fetchone() is not None:
error = 'El actuator {} ya existe.'.format(name)
if error is None:
db.execute(
'INSERT INTO actuator (name,status,sensor_id) VALUES (?, 0, ?)',
(name, sensor_id)
)
db.commit()
return redirect(url_for('actuator.index'))
flash(error)
return render_template('actuator/crear.html', sensores=sensores)
def register():
db = get_db()
grupos = db.execute('SELECT id, name, description'
' FROM grupo'
' ORDER BY id DESC').fetchall()
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
grupo = request.form['grupo']
db = get_db()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif not grupo:
error = 'grupo is required.'
elif db.execute('SELECT id FROM user WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
db.execute(
'INSERT INTO user (username, password,grupo_id) VALUES (?, ?, ?)',
(username, generate_password_hash(password), grupo))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html', grupos=grupos)
def create():
if request.method == 'POST':
name = request.form['name']
description = request.form['description']
db = get_db()
error = None
if not name:
error = 'El nombre del grupo es requerido.'
elif not description:
error = 'La descripción es requerida'
elif db.execute(
'SELECT id FROM grupo WHERE name = ?', (name,)
).fetchone() is not None:
error = 'El grupo {} ya existe.'.format(name)
if error is None:
db.execute(
'INSERT INTO grupo (name, description) VALUES (?, ?)',
(name, description)
)
db.commit()
return redirect(url_for('grupo.index'))
flash(error)
return render_template('grupo/crear.html')
def index():
db = get_db()
sensors = db.execute(
'SELECT se.id as id, se.name as name, site_id, si.name as sname, unit, min, max'
' FROM sensor se JOIN site si ON se.site_id = si.id'
' ORDER BY si.id,se.id ASC').fetchall()
return render_template('sensor/index.html', sensors=sensors)
def create():
if request.method == 'POST':
name = request.form['name']
address = request.form['address']
description = request.form['description']
db = get_db()
error = None
if not name:
error = 'El nombre del lugar es requerido.'
elif (' ' in name):
error = 'El nombre no debe llevar espacios'
elif (' ' in address):
error = 'La direccion del lugar es requerido'
elif not description:
error = 'La descripción es requerida'
elif db.execute('SELECT id FROM site WHERE name = ?',
(name.lower(), )).fetchone() is not None:
error = 'El lugar {} ya existe.'.format(name)
if error is None:
db.execute(
'INSERT INTO site (name, address, description, enabled) VALUES (?, ?, ?,0)',
(name.lower(), address, description))
db.execute(
'CREATE TABLE sitetable' + name.lower() +
' ( id INTEGER PRIMARY KEY AUTOINCREMENT, date datetime )')
db.commit()
return redirect(url_for('site.index'))
flash(error)
return render_template('site/crear.html')
def create():
db = get_db()
lugares = db.execute('SELECT id, name, description'
' FROM site'
' ORDER BY id DESC').fetchall()
if request.method == 'POST':
name = request.form['name']
lugar = request.form['lugar']
datatype = request.form['datatype']
unit = request.form['unit']
min = request.form['min']
max = request.form['max']
db = get_db()
error = None
if not name:
error = 'El nombre del sensor es requerido.'
elif not unit:
error = 'La unidad del sensor es requerido'
elif not min:
error = 'El sensor debe tener un valor mínimo'
elif not max:
error = 'El sensor debe tener un valor máximo'
elif (' ' in name):
error = 'El nombre no debe llevar espacios'
elif db.execute('SELECT id FROM sensor WHERE name = ? AND site_id = ?',
(name, lugar)).fetchone() is not None:
error = 'El sensor {} ya existe.'.format(name)
if error is not None:
flash(error)
else:
db.execute(
'INSERT INTO sensor (name, site_id,datatype,unit,min,max) VALUES (?, ?, ?, ?, ?, ?)',
(name, lugar, datatype, unit, min, max))
lugarnombre = db.execute('SELECT name FROM site WHERE id = ? ',
(lugar)).fetchone()
db.execute('ALTER TABLE sitetable' + lugarnombre[0] +
' ADD COLUMN ' + name + ' ' + datatype)
db.commit()
return redirect(url_for('sensor.index'))
return render_template('sensor/crear.html',
lugares=lugares,
datatypes=datatypes)
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def index():
db = get_db()
grupos = db.execute(
'SELECT id, name, description'
' FROM grupo'
' ORDER BY id DESC'
).fetchall()
return render_template('grupo/index.html', grupos=grupos)
def graph(id):
sensor = get_sensor(id)
db = get_db()
lugar = db.execute('SELECT id, name, description'
' FROM site WHERE id=' + str(sensor['site_id']) +
' ORDER BY id DESC').fetchone()
return render_template('sensor/graph.html', lugar=lugar, sensor=sensor)
def get_site(id):
site = get_db().execute('SELECT id, name, address, description', 'enabled'
' FROM site '
' WHERE id = ?', (id, )).fetchone()
if site is None:
abort(404, "El id del lugar {0} no existe.".format(id))
return site
def graph(id):
lugar = get_site(id)
db = get_db()
sensores = db.execute('SELECT id, name, site_id, unit, min, max'
' FROM sensor WHERE site_id=' + str(lugar['id']) +
' ORDER BY id DESC').fetchall()
return render_template('site/graph.html', lugar=lugar, sensores=sensores)
def index():
db = get_db()
actuators = db.execute(
'SELECT ac.id as id, ac.name as name, status, sensor_id, se.name as sensor, si.name as site'
' FROM actuator ac JOIN sensor se ON ac.sensor_id=se.id '
' JOIN site si ON se.site_id=si.id'
' ORDER BY id ASC'
).fetchall()
return render_template('actuator/index.html', actuators=actuators)
def get_sensor(id):
sensor = get_db().execute(
'SELECT id, name, site_id, datatype, unit, min, max'
' FROM sensor '
' WHERE id = ?', (id, )).fetchone()
if sensor is None:
abort(404, "El id del sensor {0} no existe.".format(id))
return sensor
def actual():
db = get_db()
sensores = db.execute('SELECT id, name, site_id'
' FROM sensor '
' ORDER BY id ASC').fetchall()
lugares = db.execute('SELECT id, name'
' FROM site '
' ORDER BY id ASC').fetchall()
return render_template('dashboard/actual.html',
sensores=sensores,
lugares=lugares)
def index():
db = get_db()
sites = db.execute('SELECT id, name, address, description,enabled'
' FROM site'
' ORDER BY id ASC').fetchall()
sensores = db.execute('SELECT id, name, site_id'
' FROM sensor'
' ORDER BY id ASC').fetchall()
return render_template('site/index.html', sites=sites, sensores=sensores)
def gaugejson(id, sensor):
db = get_db()
tabla = db.execute('SELECT name FROM site WHERE id=' + str(id)).fetchone()
namesensor = db.execute('SELECT name FROM sensor WHERE id=' +
str(sensor)).fetchone()
#Falta mandar error cuando no encuentra el sensor
resultado = db.execute('SELECT ' + namesensor['name'] + ' FROM sitetable' +
tabla['name'] +
' ORDER BY date DESC LIMIT 1').fetchone()
#print (resultado[namesensor['name']])
return str(resultado[namesensor['name']])
def update(id):
sensor = get_sensor(id)
db = get_db()
lugares = db.execute('SELECT id, name, description'
' FROM site'
' ORDER BY id DESC').fetchall()
return render_template('sensor/update.html',
sensor=sensor,
lugares=lugares,
datatypes=datatypes)
def get_actuator(id):
actuator = get_db().execute(
'SELECT id, name, status, sensor_id'
' FROM actuator '
' WHERE id = ?',
(id,)
).fetchone()
if actuator is None:
abort(404, "El actuator id {0} no existe.".format(id))
return actuator
def get_grupo(id):
grupo = get_db().execute(
'SELECT id, name, description'
' FROM grupo '
' WHERE id = ?',
(id,)
).fetchone()
if grupo is None:
abort(404, "El grupo id {0} no existe.".format(id))
return grupo
def update(id):
actuator = get_actuator(id)
db = get_db()
sensores = db.execute(
'SELECT se.id as id, se.name as name, site_id, si.name as site'
' FROM sensor se JOIN site si ON se.site_id=si.id'
' ORDER BY si.id,se.id ASC'
).fetchall()
if request.method == 'POST':
name = request.form['name']
sensor_id = request.form['sensor_id']
if request.form.get('status'):
status=1
else:
status=0
error = None
if not name:
error = 'El nombre es requerido.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE actuator SET name = ?, sensor_id = ?, status= ?'
' WHERE id = ?',
(name, sensor_id, status, id)
)
db.commit()
return redirect(url_for('actuator.index'))
return render_template('actuator/update.html', actuator=actuator, sensores=sensores)
def datajson(id, sensor):
db = get_db()
tabla = db.execute('SELECT name FROM site WHERE id=' + str(id)).fetchone()
namesensor = db.execute('SELECT name FROM sensor WHERE id=' +
str(sensor)).fetchone()
#print (namesensor['name'])
#Falta mandar error cuando no encuentra el sensor
resultados = db.execute(
'SELECT strftime("%s",substr(date,0,5)||"-" ||substr(date,5,2)||"-"||substr(date,7,2)||" "||substr(date,9,2)||":"||substr(date,11,2)||":"|| substr(date,13,2),"-6 hour") * 1000 as date,'
+ namesensor['name'] + ' FROM sitetable' + tabla['name'] +
' ORDER BY date ASC').fetchall()
data = []
for resultado in resultados:
data.append(list(resultado)) # or simply data.append(list(row))
return json.dumps(data)
def delete(id):
#Obtenemos los campos de los datos que queremos eliminar
site = get_site(id)
db = get_db()
error = None
if db.execute('SELECT id FROM sensor WHERE site_id = ' +
str(id)).fetchone() is not None:
error = 'El lugar {} aun tiene asociados sensores.'.format(site[1])
if error is None:
#Borramos todos los sensores que pertenezcan al lugar
#db.execute('DELETE FROM sensor WHERE site_id = ?', (id,))
#Borramos el reistro de la tabla site
db.execute('DELETE FROM site WHERE id = ?', (id, ))
#Buscamos la tabla para eliminarla
db.execute('DROP TABLE sitetable' + site[1])
db.commit()
return redirect(url_for('site.index'))
flash(error)
return redirect(url_for('site.index'))
def index():
db = get_db()
sensores = db.execute('SELECT id, name, site_id, unit, min, max'
' FROM sensor '
' ORDER BY id ASC').fetchall()
lugares = db.execute('SELECT id, name'
' FROM site '
' ORDER BY id ASC').fetchall()
actuators = db.execute(
'SELECT ac.id as id, ac.name as name, status, sensor_id, se.name as sensor, si.name as site'
' FROM actuator ac JOIN sensor se ON ac.sensor_id=se.id '
' JOIN site si ON se.site_id=si.id'
' ORDER BY id ASC').fetchall()
#print (*lugares,sep = ", ")
#print (lugares[0]['id'])
return render_template('dashboard/index.html',
sensores=sensores,
lugares=lugares,
actuators=actuators)
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('grupo.index'))
flash(error)
return render_template('auth/login.html')
def actual(id):
db = get_db()
sensor = db.execute(
'SELECT id, name, site_id, datatype, unit, min, max'
' FROM sensor '
' WHERE id = ?', (id, )).fetchone()
if sensor is None:
abort(404, "El id del sensor {0} no existe.".format(id))
else:
tabla = db.execute('SELECT name FROM site WHERE id=' +
str(sensor['site_id'])).fetchone()
resultado = db.execute('SELECT date,' + sensor['name'] +
' FROM sitetable' + tabla['name'] +
' ORDER BY id DESC LIMIT 1').fetchone()
dateActual = time.strftime("%Y%m%d%H%M%S")
print(dateActual)
print(resultado)
print(resultado['date'])
#print (resultado[namesensor['name']])
return dateActual
def update(id):
grupo = get_grupo(id)
if request.method == 'POST':
name = request.form['name']
description = request.form['description']
error = None
if not name:
error = 'El nombre es requerido.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE grupo SET name = ?, description = ?'
' WHERE id = ?',
(name, description, id)
)
db.commit()
return redirect(url_for('grupo.index'))
return render_template('grupo/update.html', grupo=grupo)
def update(id):
site = get_site(id)
if request.method == 'POST':
description = request.form['description']
address = request.form['address']
if request.form.get('enabled'):
enabled = 1
else:
enabled = 0
error = None
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE site SET address=?,description = ?, enabled = ?'
' WHERE id = ?', (address, description, enabled, id))
db.commit()
return redirect(url_for('site.index'))
return render_template('site/update.html', site=site)
def delete(id):
get_actuator(id)
db = get_db()
db.execute('DELETE FROM actuator WHERE id = ?', (id,))
db.commit()
return redirect(url_for('actuator.index'))
def delete(id):
#Obtenemos los campos de los datos que queremos eliminar
sensor = get_sensor(id)
db = get_db()
error = None
if db.execute('SELECT id FROM actuator WHERE sensor_id = ' +
str(id)).fetchone() is not None:
error = 'El sensor {} tiene actuadores asociados. Debe borrar los actuadores asociados antes de borrar.'.format(
sensor['name'])
if error is not None:
flash(error)
else:
#Para borrar el sensor, necesitamos borrar la columna de la tabla sitetable"lugarr"
#Para ello renombramos la tabla, creamos de nuevo la tabla sin la columna a borrar
#pasamos los datos y borramos la tabla temporal y borramos el registro en la tabla sensor
tablatemporal = 'temporal'
tabla = db.execute('SELECT name FROM site WHERE id=' +
str(sensor['site_id'])).fetchone()
columnas = db.execute(
'SELECT name, datatype FROM sensor WHERE site_id=' +
str(sensor['site_id']))
#Renombramos la tabla actual a una temporal
print('ALTER TABLE sitetable' + tabla['name'] + ' RENAME TO ' +
tablatemporal)
db.execute('ALTER TABLE sitetable' + tabla['name'] + ' RENAME TO ' +
tablatemporal)
#Creamos la tabla inicial
print('CREATE TABLE sitetable' + tabla['name'] +
'( id INTEGER PRIMARY KEY AUTOINCREMENT, date datetime )')
db.execute('CREATE TABLE sitetable' + tabla['name'] +
'( id INTEGER PRIMARY KEY AUTOINCREMENT, date datetime )')
campos = ''
for columna in columnas:
if columna['name'] != sensor['name']:
#Recreamos las columnas en la nueva tabla, menos la que se va a borrar
print('ALTER TABLE sitetable' + tabla['name'] +
' ADD COLUMN ' + columna['name'] + ' ' +
columna['datatype'])
db.execute('ALTER TABLE sitetable' + tabla['name'] +
' ADD COLUMN ' + columna['name'] + ' ' +
columna['datatype'])
campos = campos + columna['name'] + ','
#Quitamos la ultima coma de la lista de columnas
campos = campos[:-1]
#insertamos los campos de la tabla anterior a la nueva
if campos != '':
print('INSERT INTO sitetable' + tabla['name'] +
' SELECT id,date,' + campos + ' FROM ' + tablatemporal)
db.execute('INSERT INTO sitetable' + tabla['name'] +
' SELECT id,date,' + campos + ' FROM ' + tablatemporal)
#Borramos el reistro de la tabla sensor
print('DELETE FROM sensor WHERE id = ?', (id, ))
db.execute('DELETE FROM sensor WHERE id = ?', (id, ))
#Borramos la tabla temporal
print('DROP TABLE ' + tablatemporal)
db.execute('DROP TABLE ' + tablatemporal)
db.commit()
return redirect(url_for('sensor.index'))
def delete(id):
get_grupo(id)
db = get_db()
db.execute('DELETE FROM grupo WHERE id = ?', (id,))
db.commit()
return redirect(url_for('grupo.index'))
