def add_status():
try:
accel_id = request.form['input_accel_id']
date_identified = request.form['input_date_identified']
status = request.form['input_status']
remarks = request.form['input_remarks']
flagger = request.form['input_flagger']
# if status_text == "Ok":
# status = 1
# elif status_text == "Use with Caution"
# validate the received values
if accel_id and date_identified and status and flagger and request.method == 'POST':
#do not save password as a plain text
# save edits
sql = ("INSERT INTO accelerometer_status(accel_id, ts_flag, "
"date_identified, flagger, status, remarks) VALUES('{}', "
"NOW(), '{}', '{}', '{}', '{}')".format(
accel_id, date_identified, flagger, status, remarks))
# print(sql)
# data = (accel_id, date_identified, flagger, status, remarks)
# conn = mysql.connect()
# cursor = conn.cursor()
# cursor.execute(sql, data)
# conn.commit()
qdb.execute_query(sql)
flash('Status added successfully!')
return redirect('/')
else:
return 'Error while adding status'
except Exception as e:
print(e)
def update_status():
try:
accel_id = request.form['input_accel_id']
date_identified = request.form['input_date_identified']
status = request.form['input_status']
remarks = request.form['input_remarks']
flagger = request.form['input_flagger']
stat_id = request.form['id']
# validate the received values
if accel_id and date_identified and status and flagger and request.method == 'POST':
#do not save password as a plain text
# _hashed_password = generate_password_hash(_password)
# save edits
# sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
sql = ("UPDATE accelerometer_status SET accel_id = '{}', "
"ts_flag = NOW(), date_identified = '{}', flagger='{}', "
"status='{}', remarks='{}' WHERE stat_id = '{}'".format(
accel_id, date_identified, flagger, status, remarks,
stat_id))
# conn = mysql.connect()
# cursor = conn.cursor()
# cursor.execute(sql, data)
# conn.commit()
qdb.execute_query(sql)
flash('Status updated successfully!')
return redirect('/')
else:
return 'Error while updating status'
except Exception as e:
flash('Status updated ERROR!')
return redirect('/')
print(e)
def update_in_use():
print('verygood')
try:
tsm_id = request.form['input_tsm_id']
node_id = request.form['input_node_id']
accel_inuse = request.form['input_accel_inuse']
# date_identified = request.form['input_date_identified']
# status = request.form['input_status']
# remarks = request.form['input_remarks']
# flagger = request.form['input_flagger']
# stat_id = request.form['id']
# validate the received values
if tsm_id and request.method == 'POST':
query = ("SELECT * FROM accelerometers "
"where tsm_id = '{}' and node_id = '{}'".format(
tsm_id, node_id))
accel = qdb.get_db_dataframe(query)
for i in accel.accel_number:
print(i)
if int(accel_inuse) == i:
in_use = 1
else:
in_use = 0
update_query = (
"UPDATE `accelerometers` "
"SET `in_use`='{}', `ts_updated` = NOW() WHERE `tsm_id`='{}' and "
"`node_id`='{}' and `accel_number` = '{}';".format(
in_use, tsm_id, node_id, i))
qdb.execute_query(update_query)
print(update_query)
# #do not save password as a plain text
## _hashed_password = generate_password_hash(_password)
# # save edits
## sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
# sql = ("UPDATE accelerometer_status SET accel_id = '{}', "
# "ts_flag = NOW(), date_identified = '{}', flagger='{}', "
# "status='{}', remarks='{}' WHERE stat_id = '{}'".format(accel_id, date_identified, flagger, status, remarks, stat_id))
## conn = mysql.connect()
## cursor = conn.cursor()
## cursor.execute(sql, data)
## conn.commit()
# qdb.execute_query(sql)
init.main()
flash('accel switched successfully!')
return redirect('/')
else:
return 'Error while updating status'
except Exception as e:
print(e)
flash('accel switched ERROR!')
return redirect('/')
def delete_status(id):
try:
# conn = mysql.connect()
# cursor = conn.cursor()
sql = "DELETE FROM accelerometer_status WHERE stat_id={}".format(id)
qdb.execute_query(sql)
flash('User deleted successfully!')
return redirect('/')
except Exception as e:
print(e)
def create_piezo_table(logger_name=''):
query = ("CREATE TABLE `piezo_{}` ("
"`data_id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
"`ts` timestamp NULL DEFAULT NULL, "
"`frequency_shift` decimal(6,2) unsigned DEFAULT NULL, "
"`temperature` float DEFAULT NULL, "
"PRIMARY KEY (`data_id`), UNIQUE KEY `unique1` (`ts`) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8;".format(logger_name))
print(query)
qdb.execute_query(query)
def delete_invalid_public_alert_entry(site_id, public_ts_start):
# query = "SELECT "
# query += " * "
query = "DELETE "
query += "FROM public_alerts "
query += "WHERE "
query += " ts = '%s' " %(public_ts_start)
query += " AND site_id = %s " %(site_id)
# query += " AND pub_sym_id = %s " %(pub_sym_id)
qdb.execute_query(query)
def create_soms_table(logger_name=''):
query = ("CREATE TABLE `soms_{}` ( "
"`data_id` int(11) NOT NULL AUTO_INCREMENT, "
"`ts` timestamp NULL DEFAULT NULL, "
"`node_id` int(11) DEFAULT NULL, "
"`type_num` int(11) DEFAULT NULL, "
"`mval1` int(11) DEFAULT NULL, "
"`mval2` int(11) DEFAULT NULL, "
"PRIMARY KEY (`data_id`), "
"UNIQUE KEY `unique1` (`ts`,`node_id`,`type_num`) "
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;".format(
logger_name))
print(query)
qdb.execute_query(query)
def create_rain_table(logger_name=''):
query = ("CREATE TABLE `rain_{}` ("
"`data_id` int(10) unsigned NOT NULL AUTO_INCREMENT, "
"`ts` timestamp NULL DEFAULT NULL, "
"`rain` float DEFAULT NULL, "
"`temperature` float DEFAULT NULL, "
"`humidity` float DEFAULT NULL, "
"`battery1` float DEFAULT NULL, "
"`battery2` float DEFAULT NULL, "
"`csq` tinyint(3) DEFAULT NULL, "
"PRIMARY KEY (`data_id`), "
"UNIQUE KEY `unique1` (`ts`) "
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;".format(
logger_name))
print(query)
qdb.execute_query(query)
def insert_l2_operational_trigger(ts, site_id):
query = "INSERT INTO operational_triggers "
query += "(ts, site_id, trigger_sym_id, ts_updated) "
query += "VALUES ('%s', %s, 8, '%s')" %(ts, site_id, ts)
result = qdb.execute_query(query)
return result
def create_tilt_table(logger_name=''):
query = ("CREATE TABLE `tilt_{}` ( "
"`data_id` int(10) unsigned NOT NULL AUTO_INCREMENT, "
"`ts` timestamp NULL DEFAULT NULL, "
"`node_id` int(11) unsigned DEFAULT NULL, "
"`type_num` int(11) unsigned DEFAULT NULL, "
"`xval` int(6) DEFAULT NULL, "
"`yval` int(6) DEFAULT NULL, "
"`zval` int(6) DEFAULT NULL, "
"`batt` float DEFAULT NULL, "
"`is_live` tinyint(4) DEFAULT '1', "
"PRIMARY KEY (`data_id`), "
"UNIQUE KEY `unique1` (`ts`,`node_id`,`type_num`)"
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;".format(
logger_name))
print(query)
qdb.execute_query(query)
def create_node_alerts():
query = "CREATE TABLE `node_alerts` ("
query += " `na_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,"
query += " `ts` TIMESTAMP NOT NULL,"
query += " `tsm_id` SMALLINT(5) UNSIGNED NOT NULL,"
query += " `node_id` SMALLINT(5) UNSIGNED NOT NULL,"
query += " `disp_alert` TINYINT(1) NOT NULL DEFAULT 0,"
query += " `vel_alert` TINYINT(1) NOT NULL DEFAULT 0,"
query += " PRIMARY KEY (`na_id`),"
query += " UNIQUE INDEX `uq_node_alerts` (`ts` ASC, `tsm_id` ASC, `node_id` ASC),"
query += " INDEX `fk_node_alerts_tsm_sensors1_idx` (`tsm_id` ASC),"
query += " CONSTRAINT `fk_node_alerts_tsm_sensors1`"
query += " FOREIGN KEY (`tsm_id`)"
query += " REFERENCES `tsm_sensors` (`tsm_id`)"
query += " ON DELETE NO ACTION"
query += " ON UPDATE CASCADE)"
qdb.execute_query(query)
def update_ts_last_site_public_alert(site_id, ts_updated):
query = "UPDATE public_alerts "
query += "SET "
query += " ts_updated = '%s' " %(ts_updated)
query += "WHERE "
query += " site_id = %s " %(site_id)
query += "ORDER BY ts_updated DESC "
query += "LIMIT 1"
result = qdb.execute_query(query)
return result
def main():
query = 'SELECT * FROM markers'
markers = db.df_read(query)
query = "SELECT * FROM marker_observations"
mo = db.df_read(query)
query = "SELECT * FROM marker_data"
md = db.df_read(query)
query = "SELECT ma_id, ts, marker_id FROM marker_alerts"
ma = db.df_read(query)
marker_alerts = pd.merge(ma, markers, on='marker_id', validate='m:1')
marker_alerts = pd.merge(marker_alerts, mo, on=['site_id', 'ts'],
validate='m:1')
marker_alerts = pd.merge(marker_alerts, md, on=['mo_id', 'marker_id'],
validate='m:1')
marker_alerts = marker_alerts.drop_duplicates(['ts', 'marker_id'],
keep='last')
# delete marker_alerts not in marker_observations and duplicated marker_alerts
ma_id = set(ma['ma_id']) - set(marker_alerts['ma_id'])
if len(ma_id) != 0:
query = 'DELETE FROM marker_alerts WHERE ma_id in %s' %str(tuple(ma_id))
qdb.execute_query(query)
try:
query = 'ALTER TABLE marker_alerts ADD UNIQUE INDEX uq_marker_alerts (marker_id ASC, ts ASC)'
qdb.execute_query(query)
except:
pass
try:
query = "ALTER TABLE marker_alerts "
query += "ADD UNIQUE INDEX uq_marker_alerts1 (data_id ASC); "
qdb.execute_query(query)
except:
pass
try:
query = "ALTER TABLE marker_alerts "
query += "ADD CONSTRAINT fk_marker_data "
query += " FOREIGN KEY (data_id) "
query += " REFERENCES marker_data (data_id) "
query += " ON DELETE CASCADE "
query += " ON UPDATE CASCADE; "
qdb.execute_query(query)
except:
pass
data_table = sms.DataTable('marker_alerts',
marker_alerts[['ts', 'marker_id', 'data_id']])
db.df_write(data_table)
timeNow = datetime.today()
df['last_data'] = logger_active['max(ts)']
df['last_data'] = pd.to_datetime(df['last_data'])
df['ts_updated'] = timeNow
df['tsm_id'] = gdf.tsm_id
diff = df['ts_updated'] - df['last_data']
tdta = diff
fdta = tdta.astype('timedelta64[D]')
#days = fdta.astype(int)
df['diff_days'] = fdta
df.loc[(df['diff_days'] > -1) & (df['diff_days'] < 3),
'presence'] = 'active'
df['presence'] = df['diff_days'].apply(lambda x: '1' if x <= 3 else '0')
print(df)
# engine=create_engine('mysql+mysqlconnector://root:[email protected]:3306/senslopedb', echo = False)
engine = create_engine('mysql+pymysql://' + sc['db']['user'] + ':' +
sc['db']['password'] + '@' + sc['hosts']['local'] +
':3306/' + sc['db']['name'])
df.to_sql(name='data_presence_tsm',
con=engine,
if_exists='append',
index=False)
return df
query = "DELETE FROM data_presence_tsm"
qdb.execute_query(query, hostdb='local')
dftosql(df)