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)