def getInstrumentIds(self, selected_datetime):
"""
Function for getting a list of sensor ids with selected_datetime
"""
if selected_datetime is None:
return [s.instruments[0].i_id for s in self.sensors]
db_conn = log2nordb()
cur = db_conn.cursor()
query = ("""
SELECT
DISTINCT(instrument_id)
FROM
sensor
WHERE
(
(sensor.time <= %(s_datetime)s AND
sensor.endtime >= %(s_datetime)s)
OR
(sensor.time <= %(s_datetime)s AND
sensor.endtime = 9999999999.999)
)
""")
cur.execute(query,
{'s_datetime': time.mktime(selected_datetime.timetuple())})
return_ids = [a[0] for a in cur.fetchall()]
db_conn.close()
return return_ids
def getSitechanIds(self, selected_datetime):
"""
Function for getting a list of sitechan ids with selected_datetime
"""
if selected_datetime is None:
return [s.s_id for s in self.sitechans]
db_conn = log2nordb()
cur = db_conn.cursor()
query = ("""
SELECT
DISTINCT(id)
FROM
sitechan
WHERE
(
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date >= %(s_datetime)s)
OR
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date IS NULL)
)
""")
cur.execute(query, {'s_datetime': selected_datetime})
return_ids = [a[0] for a in cur.fetchall()]
db_conn.close()
return return_ids
def getSensorIdsFromStations(self, station_ids, selected_datetime):
"""
Function for getting a list of sensor_ids from station_ids
"""
if not station_ids:
return []
db_conn = log2nordb()
cur = db_conn.cursor()
if selected_datetime is None:
query = ("""
SELECT
DISTINCT(sensor.id)
FROM
sensor, sitechan
WHERE
station_id IN %s
AND
sensor.sitechan_id = sitechan.id
""")
cur.execute(query, (tuple(station_ids), ))
else:
query = ("""
SELECT
DISTINCT(sensor.id)
FROM
sensor, sitechan
WHERE
station_id IN %(station_ids)s
AND
sensor.sitechan_id = sitechan.id
AND
(
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date >= %(s_datetime)s)
OR
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date IS NULL)
)
""")
cur.execute(
query, {
'station_ids': tuple(station_ids),
's_datetime': time.mktime(selected_datetime.timetuple())
})
return_ids = [a[0] for a in cur.fetchall()]
db_conn.close()
return return_ids
def getStationIdsFromInstruments(self, instrument_ids, selected_datetime):
"""
Function for getting a list of station_ids from instrument_ids
"""
if not instrument_ids:
return []
db_conn = log2nordb()
cur = db_conn.cursor()
if selected_datetime is None:
query = ("""
SELECT
DISTINCT(station_id)
FROM
sitechan, sensor
WHERE
sensor.sitechan_id = sitechan.id
AND
sensor.instrument_id IN %s
""")
cur.execute(query, (tuple(instrument_ids), ))
else:
query = ("""
SELECT
DISTINCT(station_id)
FROM
sitechan, sensor
WHERE
sensor.sitechan_id = sitechan.id
AND
sensor.instrument_id IN %(instrument_ids)s
AND
(
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date >= %(s_datetime)s)
OR
(sitechan.on_date <= %(s_datetime)s AND
sitechan.off_date IS NULL)
)
""")
cur.execute(
query, {
'instrument_ids': tuple(instrument_ids),
's_datetime': selected_datetime
})
return_ids = [a[0] for a in cur.fetchall()]
db_conn.close()
return return_ids
def getSitechanIdsFromInstruments(self, instrument_ids, selected_datetime):
"""
Function for getting a list of sitechan_ids from instrument_ids
"""
if not instrument_ids:
return []
db_conn = log2nordb()
cur = db_conn.cursor()
if selected_datetime is None:
query = ("""
SELECT
DISTINCT(sitechan_id)
FROM
sensor
WHERE
instrument_id IN %s
""")
cur.execute(query, (tuple(instrument_ids), ))
else:
query = ("""
SELECT
DISTINCT(sitechan_id)
FROM
sensor
WHERE
instrument_id IN %(instrument_ids)s
AND
(
(sensor.time <= %(s_datetime)s AND
sensor.endtime >= %(s_datetime)s)
OR
(sensor.time <= %(s_datetime)s AND
sensor.endtime = 9999999999.999)
)
""")
cur.execute(
query, {
'instrument_ids': tuple(instrument_ids),
's_datetime': time.mktime(selected_datetime.timetuple())
})
return_ids = [a[0] for a in cur.fetchall()]
db_conn.close()
return return_ids
def readDetectionFile(file_date = datetime.now()):
"""
read a detection file for a certain date and push it to the database
"""
conn = log2nordb()
cur = conn.cursor()
try:
cur.execute(DOES_DAILY_LIST_EXIST, {'new_date':file_date.date()})
existing_id = cur.fetchone()
if existing_id is not None:
daily_list_id = existing_id[0]
else:
cur.execute(CREATE_NEW_DAILY_LIST, {'author_lock':None, 'daily_list_date':file_date.date()})
daily_list_id = cur.fetchone()[0]
conn.commit()
except Exception as e:
conn.close()
print('Failed to create a new daily list item into the postgresql database: {0}'.format(e))
sys.exit(1)
try:
entries = []
filename = 'AutomLysti.{0}{1:03d}'.format(file_date.timetuple().tm_year, file_date.timetuple().tm_yday)
type_number = 1
detection_file = open("{0}/{1}".format(DETECTION_FILE_PATH, filename), 'r')
for line in detection_file:
if line[:2] == '{0})'.format(type_number+1): #See if the type of the detection changes
type_number += 1
if type_number == 4:
event_time = datetime.strptime(line[11:21] + '00', '%H.%M.%S.%f').time()
cur.execute(SEARCH_FINA_EVENT, {'event_date':file_date.date(), 'event_time':event_time})
ans = cur.fetchone()
if ans is None:
raise Exception('No event found for line: {0}'.format(line))
event_id = ans[0]
wfdisc_name = detection_file.readline().strip()
cur.execute(INSERT_WFDISC_TO_FINA_EVENT, {'event_id': event_id, 'waveform_info':wfdisc_name.upper()})
cur.execute(CREATE_NEW_EVENT_CLASSIFICATION,
{
'daily_id':daily_list_id,
'event_id':event_id,
'classification':type_number,
'priority':-1,
'eqex':None,
'certainty':None,
'username':'',
'analysis_id':-1
})
if len(line) > 80 and line[79] == '1':
search_event = NordicEvent()
search_event.main_h.append(createStringMainHeader(line, False))
found_events = searchSameEvents(search_event)
if not found_events:
raise Exception('No event found for line: {0}'.format(line))
event_id = -1
for event in found_events:
if event.solution_type == AUTOMATIC_SOLUTION_TYPE:
event_id = found_events[0].event_id
if event_id == -1:
raise Exception('No event with correct solution_type found. Required type: {0}'.format(AUTOMATIC_SOLUTION_TYPE))
detection_file.readline()
class_vals = readClassificationLine(detection_file.readline())
if not class_vals:
cur.execute(CREATE_NEW_EVENT_CLASSIFICATION,
{
'daily_id':daily_list_id,
'event_id':event_id,
'classification':type_number,
'priority':-1,
'eqex':None,
'certainty':None,
'username':'',
'analysis_id':-1
})
else:
cur.execute(CREATE_NEW_EVENT_CLASSIFICATION,
{
'daily_id':daily_list_id,
'event_id':event_id,
'classification':type_number,
'priority':-1,
'eqex':class_vals[1],
'certainty':class_vals[1],
'username':'',
'analysis_id':-1
})
except Exception as e:
print(e)
conn.rollback()
cur.execute(CREATE_NEW_ERROR_LOG,
{
'daily_list_id': daily_list_id,
'error_log': str(e)
})
conn.commit()
conn.close()
def __init__(self):
self.__conn = log2nordb()
