def get_decay_list():
list_decay_msg = []
decay_msg = {}
now_utc_str = Time_Converter.datetime_to_str(datetime.datetime.utcnow(),
"%Y-%m-%d")
after_week_utc_str = Time_Converter.datetime_to_str(
datetime.datetime.utcnow() + datetime.timedelta(days=7), "%Y-%m-%d")
msg_index = 0
db = DB_Bottom.DB_Bottom()
db.db_init(iface.LOC_DB_DECAY)
msg = db.cur.execute(iface_SQL.sql_query_decay,
(now_utc_str, after_week_utc_str)).fetchall()
for p in msg:
msg_index += 1
decay_msg = {
'norad_id': str(p[0]),
'name': p[4],
'decay_epoch': p[1],
'message_epoch': p[2],
'msg_num': str(msg_index)
}
list_decay_msg.append(decay_msg)
db.db_close()
return list_decay_msg
def insert_result_into_db(self, type, setting, altitude, res_val, target):
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_RESULT.format(self.task_name))
inclination = setting.split(":")[0]
number_sat = setting.split(":")[1].split("/")[0]
number_plane = setting.split(":")[1].split("/")[1]
inter_plane = setting.split(":")[1].split("/")[2]
if type == "area":
query = "insert into {} (setting, altitude, avg_min, avg_max, avg_avg, max_min, max_max, max_avg) values " \
"(?, ?, ?, ?, ?, ?, ?, ?)".format(target)
try:
db.cur.execute(query,
(setting, altitude, res_val[0][0],
res_val[0][1], res_val[0][2], res_val[1][0],
res_val[1][1], res_val[1][2]))
except Exception as e:
print(e)
elif type == "secondary":
query = "insert into {} (setting, altitude, avg, max, min) values (?, ?, ?, ?, ?)"
db.cur.execute(
query, (setting, altitude, res_val[0], res_val[1], res_val[2]))
query_insert_key = "update keys set isdone=1 where altitude = ? and inclination = ? and number_sat = ? and number_plane = ? and inter_plane =? "
# query_insert_key = "insert into keys (altitude,inclination, number_sat, number_plane, inter_plane, isdone) values (?,?, ?, ?, ?, ?)"
db.cur.execute(
query_insert_key,
(altitude, inclination, number_sat, number_plane, inter_plane))
db.conn.commit()
db.db_close()
def get_satcat_count():
satcat_data = {}
db = DB_Bottom.DB_Bottom()
db.db_init(iface.LOC_DB_CDM)
payload_orbit = db.cur.execute(iface_SQL.sql_payload_orbit).fetchone()[0]
payload_decayed = db.cur.execute(
iface_SQL.sql_payload_decayed).fetchone()[0]
debris_orbit = db.cur.execute(iface_SQL.sql_debris_orbit).fetchone()[0]
debris_decayed = db.cur.execute(iface_SQL.sql_debris_decayed).fetchone()[0]
active_sat = db.cur.execute(iface_SQL.sql_active_sat).fetchone()[0]
payload_all = payload_orbit + payload_decayed
debris_all = debris_orbit + debris_decayed
all_orbit = payload_orbit + debris_orbit
all_decayed = payload_decayed + debris_decayed
all_all = all_orbit + all_decayed
satcat_data = {
'p_1': str(payload_orbit),
'p_2': str(payload_decayed),
'p_3': str(payload_all),
'd_1': str(debris_orbit),
'd_2': str(debris_decayed),
'd_3': str(debris_all),
'a_1': str(all_orbit),
'a_2': str(all_decayed),
'a_3': str(all_all),
'active': str(active_sat)
}
db.db_close()
satcat_data.update(get_satcat_country())
return satcat_data
def count_decay_forecast():
count_decay = {'DECAY_week': "", 'DECAY_month': ""}
now_utc = datetime.datetime.utcnow()
now_utc_str = Time_Converter.datetime_to_str(now_utc, "%Y-%m-%d")
db = DB_Bottom.DB_Bottom()
db.db_init(iface.LOC_DB_DECAY)
count_decay['DECAY_month'] = db.cur.execute(
iface_SQL.sql_count_decay,
(now_utc_str,
Time_Converter.datetime_to_str(now_utc + datetime.timedelta(days=30),
"%Y-%m-%d"))).fetchone()[0]
count_decay['DECAY_week'] = db.cur.execute(
iface_SQL.sql_count_decay,
(now_utc_str,
Time_Converter.datetime_to_str(now_utc + datetime.timedelta(days=7),
"%Y-%m-%d"))).fetchone()[0]
db.db_close()
count_decay['DECAY_month'] = str(count_decay['DECAY_month'])
count_decay['DECAY_week'] = str(count_decay['DECAY_week'])
return count_decay
def insert_cdm(self, cdm_data):
loc_db_cdm = iface.LOC_DB_CDM
db_handle = DB_Bottom.DB_Bottom()
self.keys = ""
cdm_data = json.loads(cdm_data)
# print("[INSERT CDM] ", cdm_data)
print("[INSERT CDM] 다운로드 받은 CDM 수: {}".format(len(cdm_data)))
# JSON 형식의 첫 번째 CDM을 이용하여 KEY 값을 저장함
self.keys = cdm_data[0].keys()
query = self.query_decorator()
insert_data = []
for p in cdm_data:
t_data = []
for j in self.keys:
if len(j) >= 4:
if j[-4:] == "UNIT": continue
t_data.append(p[j])
insert_data.append(tuple(t_data))
db_handle.db_init(loc_db_cdm)
cur = db_handle.cur
# cur.executemany(query, insert_data)
for p in insert_data:
try:
cur.execute(query, p)
except sqlite3.IntegrityError as e:
print(e)
db_handle.conn.commit()
db_handle.db_close()
def event_count(self):
db_handle = DB_Bottom.DB_Bottom()
self.event_year_count()
max_event = self.get_max_eventnum()
db_handle.db_init(iface.LOC_DB_CDM)
cur = db_handle.cur
#이매 해당 이벤트에 대한 기록이 존재하는 경우 해당 이벤트 번호를 불러옴
query_exist = "select distinct eventnum from cdm where ? < tca and tca < ? and sat1_object_name = ? and sat2_object_name = ? and eventnum is not null"
update_new = "update cdm set eventnum = ? where ? < tca and tca < ? and sat1_object_name = ? and sat2_object_name = ?"
while (True):
update_eventnum = None
cdm_left = cur.execute(
"select count(*) from cdm where eventnum is null").fetchall(
)[0][0]
if cdm_left == 0: break
query = cur.execute(
"select message_id, tca, sat1_object_name, sat2_object_name, event_year from cdm where eventnum is null limit 1"
).fetchone()
message_id = query[0]
tca = query[1][:19]
sat1_name = query[2]
sat2_name = query[3]
event_year = query[4]
tca_stop = datetime.datetime.strptime(
tca, "%Y-%m-%dT%H:%M:%S") + datetime.timedelta(minutes=15)
tca_start = datetime.datetime.strptime(
tca, "%Y-%m-%dT%H:%M:%S") - datetime.timedelta(minutes=15)
tca_stop = datetime.datetime.strftime(tca_stop,
"%Y-%m-%dT%H:%M:%S")
tca_start = datetime.datetime.strftime(tca_start,
"%Y-%m-%dT%H:%M:%S")
exist_num = cur.execute(
query_exist,
(tca_start, tca_stop, sat1_name, sat2_name)).fetchone()
#해당 이벤트에 대한 기록이 있는 경우
if exist_num != None:
update_eventnum = exist_num[0]
#해당 이벤트에 대한 기록이 없는 경우
else:
max_event[event_year] = max_event[event_year] + 1
update_eventnum = max_event[event_year]
if update_eventnum != None:
#print(update_eventnum, str(tca_start)[:19], str(tca_stop)[:19], sat1_name, sat2_name)
cur.execute(update_new,
(str(update_eventnum), str(tca_start)[:19],
str(tca_stop)[:19], sat1_name, sat2_name))
db_handle.conn.commit()
else:
print("[EventCounter] Event 번호 none 데이터 발생")
db_handle.db_close()
def get_list_remain(self):
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_RESULT.format(self.task_name))
remain_list = db.cur.execute(
"select altitude, inclination, number_sat, number_plane, inter_plane from keys where isdone is null order by altitude asc, inclination asc"
).fetchall()
return remain_list
def insert_metadata(val, time_val=Time_Converter.datetime_to_str(datetime.datetime.utcnow())):
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_METADATA)
db_handle.cur.execute("insert into metadata (updatetime, target) values (?, ?)", (time_val, val))
db_handle.conn.commit()
db_handle.db_close()
def get_last_msg_time():
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_DECAY)
last_msg_time_utc = db_handle.cur.execute(
"select max(msg_epoch) from decay").fetchone()[0]
print(last_msg_time_utc)
db_handle.db_close()
def get_data_top(self):
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_METADATA)
rows = db_handle.cur.execute("select target, max(updatetime) from metadata group by target").fetchall()
for p in rows:
self.dict_metadata[p[0]] = p[1]
db_handle.db_close()
def insert_facility(self, name):
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_META)
val = db.cur.execute(
"select name, latitude, longitude, altitude from facility where name = ?",
(name, )).fetchone()
self.stk.add_obj("Facility", val[0])
self.stk.simple_connect(
int_STK.stk_set_position.format(val[0], val[1], val[2], val[3]))
db.db_close()
def get_cdm_count():
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
val = db_handle.cur.execute("select count(*) from cdm").fetchone()[0]
print("[Metadata] CDM 수: {}".format(val))
db_handle.db_close()
return val
def get_last_creation_date_utc():
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
last_creation_date_utc = db_handle.cur.execute("select max(creation_date) from cdm").fetchone()[0]
db_handle.db_close()
if last_creation_date_utc is None:
last_creation_date_utc = iface.META_DATA_TIME_UTC
return last_creation_date_utc
def screened_cdm(self, last_report_time_utc):
list_screened_cdm = []
dict_screened_data = {}
cdm_no = 0
self.last_report_time = last_report_time_utc
self.cdm_to_notice()
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
screened_leo = db_handle.cur.execute(
self.sql.sql_screen_cdm.format(self.sql.orbit_leo_period_criteria,
self.sql.miss_distance_leo),
(self.last_report_time, )).fetchall()
screened_geo = db_handle.cur.execute(
self.sql.sql_screen_cdm.format(self.sql.orbit_geo_period_criteria,
self.sql.miss_distance_geo),
(self.last_report_time, )).fetchall()
rows = screened_geo + screened_leo
for p in rows:
cdm_no += 1
if p[1] in self.list_to_notice:
met_criteria = "O"
else:
met_criteria = "X"
if p[4] is None:
probability = "-"
else:
probability = p[4]
dict_screened_data = {
'CDM_NO': str(cdm_no),
'CREATION_DATE': p[0].replace("T", "\n"),
'SAT1_NAME': p[8],
'SAT1_NORAD': str(p[9]),
'SAT2_NAME': p[10],
'SAT2_NORAD': str(p[11]),
'TCA': p[5].replace("T", "\n"),
'MISS_DISTANCE': str(p[3]),
'PROBABILITY': str(probability),
'MET_CRITERIA': met_criteria,
'EVENTNUM': str(p[6])
}
list_screened_cdm.append(dict_screened_data)
db_handle.db_close()
return list_screened_cdm
def delete_report_list(self):
db = DB_Bottom.DB_Bottom()
db.db_init(iface.LOC_DB_METADATA)
for p in self.ui.list_report.selectedItems():
db.cur.execute(iface_SQL.sql_delete_report,
(iface.DAILY_REPORT_UTC,
Time_Converter.kst_to_utc_str(p.text())))
db.conn.commit()
db.db_close()
self.update_data()
def new_task(values, task_name=""):
now_utc = datetime.datetime.utcnow()
year = str(now_utc.year)
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_META)
if task_name == "":
last_task = db.cur.execute(
"select task_name from tasks where task_name not like 'test%' order by insert_time desc limit 1"
).fetchone()
if last_task is None:
task_year = year
task_num = "1"
else:
last_task = last_task[0].split("_")
last_year = last_task[0]
if year != last_year:
task_year = year
task_num = "1"
else:
task_year = last_year
task_num = str(int(last_task[1]) + 1)
task_name = "{}_{}".format(task_year, task_num)
# task_name = "task_62"
db.cur.execute("insert into tasks (insert_time, task_name) values (?, ?)",
(Time_Converter.datetime_to_str(now_utc), task_name))
for set_type in [
'altitude', 'inclination', 'sats', 'primary', 'secondary', 'Area',
'sensor_type', 'sensor_set', 'grid', 'inter_plane_space'
]:
for q in values[set_type]:
print("{} value: {}".format(set_type, q))
db.cur.execute(
"insert into settings (task_name, set_type, set_value) values (?, ?, ?)",
(task_name, set_type, q))
db.cur.execute(
"insert into scenario (task_name, scenario_start, scenario_stop) values (?, ?, ?)",
(task_name, values['scenario_start'], values['scenario_stop']))
db.conn.commit()
db.db_close()
values.update({'task_name': task_name})
set_result_file(values)
return task_name
def insert_facility_list(dict_facility):
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_META)
for facility in dict_facility.keys():
try:
db.cur.execute(
"insert into facility (name, latitude, longitude, altitude) values (?, ?, ?, ?)",
(facility, dict_facility[facility][0],
dict_facility[facility][1], dict_facility[facility][2]))
except Exception as e:
print(e)
db.conn.commit()
db.db_close()
def get_list_report_docx():
list_report = []
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_METADATA)
rows = db_handle.cur.execute("select target, updatetime from metadata where target=? order by updatetime desc",
(iface.DAILY_REPORT_UTC,)).fetchall()
for p in rows:
val = Time_Converter.utc_to_kst_str(p[1], time_format="%Y%m%d_%H%M%S")
list_report.append("{}".format(val))
db_handle.db_close()
return list_report
def update_satellite_list(self):
sql = iface_SQL.iface_SQL
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
cur = db_handle.cur
sat_list = cur.execute(sql.sql_sat_list_query).fetchall()
for p in sat_list:
try:
cur.execute(sql.sql_sat_list_update, (p[0], p[1], p[2]))
except:
pass
db_handle.conn.commit()
db_handle.db_close()
def get_last_report_make_utc():
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_METADATA)
now_minus_eight = datetime.datetime.strftime(datetime.datetime.utcnow() - datetime.timedelta(hours=8),
iface.Time_format)
daily_report_time_utc = \
db_handle.cur.execute("select target, max(updatetime) from metadata where target = ? and updatetime < ?",
(iface.DAILY_REPORT_UTC, now_minus_eight)).fetchone()[1]
db_handle.db_close()
if daily_report_time_utc is None:
daily_report_time_utc = iface.META_DATA_TIME_UTC
return daily_report_time_utc
def get_satcat_country():
countries = {}
count_for_all = 0
db = DB_Bottom.DB_Bottom()
db.db_init(iface.LOC_DB_CDM)
for p in iface.list_of_satcat_country:
val = db.cur.execute(iface_SQL.sql_count_country, (p, )).fetchone()[0]
count_for_all += val
countries[p] = str(val)
countries['guitar'] = str(
db.cur.execute(iface_SQL.sql_active_sat).fetchone()[0] - count_for_all)
db.db_close()
return countries
def get_facility_list():
dict_ret = {}
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_META)
rows = db.cur.execute(
"select name, latitude, longitude, altitude from facility").fetchall()
if rows is None:
return None
for p in rows:
print(p)
dict_ret[p[0]] = [p[1], p[2], p[3]]
db.db_close()
return dict_ret
def cdm_to_notice(self):
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
leo_notice = db_handle.cur.execute(
self.sql.sql_notice_query_leo,
(self.last_report_time, )).fetchall()
geo_notice = db_handle.cur.execute(
self.sql.sql_notice_query_geo,
(self.last_report_time, )).fetchall()
# print(geo_notice + leo_notice)
for p in geo_notice + leo_notice:
self.list_to_notice.append(p[0])
print(self.list_to_notice)
db_handle.db_close()
def get_max_eventnum(self):
max_eventnum = {}
query = "select event_year, max(eventnum) from cdm group by event_year"
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
cur = db_handle.cur
rows = cur.execute(query)
data = rows.fetchall()
db_handle.db_close()
for p in data:
if p[1] != None: max_eventnum[p[0]] = p[1]
else: max_eventnum[p[0]] = 0
return max_eventnum
def insert_decay_msg(data_text):
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_DECAY)
decay_list = json.loads(data_text)
for p in decay_list:
try:
db_handle.cur.execute(
iface_SQL.sql_insert_decay,
(p['NORAD_CAT_ID'], p['OBJECT_NUMBER'], p['OBJECT_NAME'],
p['INTLDES'], p['OBJECT_ID'], p['RCS'], p['RCS_SIZE'],
p['COUNTRY'], p['MSG_EPOCH'], p['DECAY_EPOCH'], p['SOURCE'],
p['MSG_TYPE'], p['PRECEDENCE']))
except sqlite3.IntegrityError as e:
print(e)
db_handle.conn.commit()
db_handle.db_close()
def event_year_count(self):
loc_db_cdm = iface.LOC_DB_CDM
query_null_eventyear = "select message_id, tca from cdm where event_year is null"
query_update_year = "update cdm set event_year = ? where message_id = ?"
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(loc_db_cdm)
cur = db_handle.cur
cur.execute(query_null_eventyear)
rows = cur.fetchall()
#print(rows)
for p in rows:
cur.execute(query_update_year, (p[1][:4], p[0]))
db_handle.conn.commit()
db_handle.db_close()
def count_cdm(self, last_report_make_time_utc):
dict_count_cdm = {}
new_cdm_count = 0
sql = iface_SQL.iface_SQL
db_handle = DB_Bottom.DB_Bottom()
db_handle.db_init(iface.LOC_DB_CDM)
# Get dict type frame will deliver cdm count data
dict_count_cdm = self.sat_frame(db_handle.cur)
#새로 다운로드 받은 CDM 수
new_cdm = db_handle.cur.execute(sql.sql_new_cdm, (last_report_make_time_utc,)).fetchall()
#
new_cdm_met_leo = db_handle.cur.execute(
sql.sql_new_cdm_met.format(sql.orbit_leo_period_criteria), (sql.miss_distance_leo, last_report_make_time_utc)
).fetchall()
new_cdm_met_geo = db_handle.cur.execute(
sql.sql_new_cdm_met.format(sql.orbit_geo_period_criteria), (sql.miss_distance_geo, last_report_make_time_utc)
).fetchall()
db_handle.db_close()
# print(new_cdm)
# print(new_cdm_met_leo)
# print(new_cdm_met_geo)
for p in new_cdm:
international_designator = p[1]
count_val = p[3]
new_cdm_count += count_val
dict_count_cdm[international_designator] = str(count_val)
for p in (new_cdm_met_geo + new_cdm_met_leo):
international_designator = p[1]
count_val = p[3]
dict_count_cdm["{}_met".format(international_designator)] = str(count_val)
# Number of updated cdm for all satellites
dict_count_cdm.update({'cdm_total':str(new_cdm_count)})
return dict_count_cdm
def update_satcat():
clear_cat()
update_query = "insert into satcat values (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
db_handle = DB_Bottom.DB_Bottom()
data = Web_Celestrak.get_SATCAT()
db_handle.db_init(iface.LOC_DB_CDM)
cur = db_handle.cur
for p in data.splitlines():
intd = p[0:11].strip()
norad = p[13:18].strip()
multiple_name_flag = p[19:20].strip()
payload_flag = p[20:21].strip()
operational_status_code = p[21:22].strip()
sat_name = p[23:47].strip()
ownership = p[49:54].strip()
launch_date = p[56:66].strip()
launch_site = p[68:73].strip()
decay_date = p[75:85].strip()
orbital_period = p[87:94].strip()
inclination = p[96:101].strip()
apogee = p[103:109].strip()
perigee = p[111:117].strip()
rcs = p[119:127].strip()
orbital_status_code = p[129:132].strip()
try:
cur.execute(
update_query,
(intd, norad, multiple_name_flag, payload_flag,
operational_status_code, sat_name, ownership, launch_date,
launch_site, decay_date, orbital_period, inclination, apogee,
perigee, rcs, orbital_status_code))
except Exception as e:
print(e)
Metadata.insert_metadata(iface.SATCAT_UTC)
db_handle.conn.commit()
db_handle.db_close()
def load_settings(self):
setting = int_STK.shell_setting
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_META)
setting_list = db.cur.execute(
"select set_type, set_value from settings where task_name = ?",
(self.task_name, )).fetchall()
scenario_set = db.cur.execute(
"select scenario_start, scenario_stop from scenario where task_name=?",
(self.task_name, )).fetchone()
for set in setting_list:
set_type = set[0]
setting[set_type].append(set[1])
setting['scenario_interval'] = [scenario_set[0], scenario_set[1]]
# setting['scenario_start'] = scenario_set[0]
# setting['scenario_stop'] = scenario_set[1]
db.db_close()
return setting
def set_result_file(values):
task_name = values['task_name']
db = DB_Bottom.DB_Bottom()
db.db_init(int_STK.LOC_DB_STK_RESULT.format(task_name))
inter_plane_space = values['inter_plane_space'][0]
# Primary Target 재방문주기 계산 요청 시 Primary_target 이름 사용
db.cur.execute(
"create table keys (altitude float, inclination float, number_sat int, number_plane int, inter_plane int, isdone int, primary key (altitude, inclination, number_sat, number_plane))"
)
db.cur.execute(
"create table EEZ (setting string, altitude float, avg_avg float, avg_max float, avg_min float, max_max float, max_min float, max_avg float, primary key(setting, altitude))"
)
for target_area in values['Area']:
db.cur.execute(
"create table {} (setting string, altitude float, avg_avg float, avg_max float, avg_min float, max_max float, max_min float, max_avg float, primary key(setting, altitude))"
.format(target_area))
for target_second in values['secondary']:
db.cur.execute(
"create table {} (setting string, altitude float, avg float, max float, min float, primary key(setting, altitude))"
.format(target_second))
for altitude in values['altitude']:
for inclination in values['inclination']:
for sats in values['sats']:
for plane in get_dividor(sats):
db.cur.execute(
"insert into keys (altitude, inclination, number_sat, number_plane, inter_plane) values (?, ?, ?, ?, ?)",
(altitude, inclination, sats, plane,
inter_plane_space))
# setting = "{}:{}/{}/{}".format(inclination, sats, plane, values['inter_plane_space'])
# for target_area in values['Area']:
# db.cur.execute("insert into {} (setting, altitude) values (?, ?)".format(target_area), (setting, altitude))
# for target_second in values['secondary']:
# db.cur.execute("insert into {} (setting, altitude) values (?, ?)".format(target_second), (setting, altitude))
db.conn.commit()
db.db_close()