Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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)
Ejemplo n.º 3
0
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('/')
Ejemplo n.º 4
0
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)
Ejemplo n.º 5
0
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)
Ejemplo n.º 7
0
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)
Ejemplo n.º 8
0
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
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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
Ejemplo n.º 13
0
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)
Ejemplo n.º 14
0
    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)