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