def call_postgresql(logger):
IntInfor = pd.DataFrame({})
try:
# conn = psycopg2.connect(database="signal_specialist", user="******", password="******",
# host="192.168.20.46",
# port="5432")
pg = Postgres()
# conn = pg.db_conn()
# print('postgresql connect succeed.')
# print('reaching datas...')
try:
sql = "select a.sys_code FSTR_INTERSECTID, b.node_name from pe_tobj_node_info a left join pe_tobj_node b " \
"on a.node_id = b.node_id where length(a.sys_code) < 5"
# cr = conn.cursor()
IntInfor = pg.call_pg_data(sql, fram=True)
# rs1 = cr.fetchall()
# IntInfor = pd.DataFrame(rs1)
# IntInfor.columns = ['FSTR_INTERSECTID', 'node_name']
# print(IntInfor)
# input()
# conn.close()
except Exception as e:
logger.error('when reaching interinfo, ' + str(e))
finally:
pg.db_close()
except Exception as e:
logger.error('when calling postgresql, ' + str(e))
return IntInfor
def loss_data_period_check(self, interface_name, stime, etime):
"""
缺失数据时段检测
:return:
"""
pg = Postgres(SqlText.pg_inf_arith)
result = pg.call_pg_data(sql_loss_data_period.format(
interface_name, stime, etime),
fram=True)
if result is not None and not result.empty:
result_loss = result[result['data_num'] == 0]
result = result[result['data_num'] != 0]
data_num = result['data_num'].values
avg_num = np.mean(data_num)
dev = np.std(data_num)
print("%s今日各时段平均请求量:%s;标准差：%s" % (interface_name, avg_num, dev))
if avg_num > 0:
result_loss.append(
result[result['data_num'] < avg_num - 2 * dev])
else:
result_loss.append(result)
print(result_loss)
loss_time = result_loss['record_time'].apply(
lambda x: str(x)).values.tolist()
return loss_time
def call_postgresql(cur_date_str, scatsid, logger):
laneInfor = pd.DataFrame({})
geom_detector = pd.DataFrame({})
try:
# conn = psycopg2.connect(database="signal_specialist", user="******", password="******",
# host="192.168.20.46",
# port="5432")
try:
pg = Postgres(pg_inf={'database': "inter_info", 'user': "******", 'password': "******",
'host': "192.168.20.46", 'port': "5432"})
sql = "select x.coil_code, x.dir_name, x.function_name from( " \
"select n.node_id, cast(m.coil_code as numeric), n.dir_name, " \
"case when m.function_name = 'icon-func-straight' then '直行' " \
"when m.function_name = 'icon-func-left-straight' then '左直' " \
"when m.function_name = 'icon-func-straight-right' then '直右' " \
"when m.function_name = 'icon-func-left' then '左转' " \
"when m.function_name = 'icon-func-right' then '右转' " \
"when m.function_name = 'icon-func-round' then '掉头' " \
"when m.function_name = 'icon-func-round-left' then '左转掉头' " \
"when m.function_name = 'icon-func-round-straight' then '直行掉头' " \
"when m.function_name = 'icon-func-left-right' then '左右转' " \
"when m.function_name = 'icon-func-three' then '直左右' else '出口' end function_name " \
"from(select a.sys_code, b.trancet_id, b.function_name, b.coil_code " \
"from pe_tobj_node_info a left join pe_tobj_node_lane b on a.node_id = b.node_id " \
" ) m left join pe_tobj_node_round n on m.trancet_id = n.roadsect_id " \
"where m.coil_code is not null and m.coil_code != '-' ) x left join pe_tobj_node_info y on " \
"x.node_id = y.node_id where y.sys_code = '{0}'".format(scatsid)
laneInfor = pg.call_pg_data(sql, fram=True)
pg.db_close()
pg = Postgres(pg_inf={'database': "detector", 'user': "******", 'password': "******",
'host': "192.168.20.56", 'port': "5432"})
sql1 = "select scats_lane, error_name, error_detail from detector_test_results " \
"where datetime = '{0}' and devc_id = '{1}'".format('2018-08-08', scatsid)
geom_detector = pg.call_pg_data(sql1, fram=True)
pg.db_close()
# cr = conn.cursor()
# cr.execute(sql)
# rs1 = cr.fetchall()
# IntInfor = pd.DataFrame(rs1)
# IntInfor.columns = ['FSTR_INTERSECTID', 'node_name']
# cr.close()
except Exception as e:
logger.error('when reaching lanerinfo, ' + str(e))
except Exception as e:
logger.error('when calling postgresql, ' + str(e))
return laneInfor, geom_detector
def parse_failed_detector_send(self, delta=15):
current_time = dt.datetime.now()
# current_date = current_time.date()
start_time = (current_time - dt.timedelta(minutes=delta)).strftime('%H:%M:%S')
end_time = (current_time).strftime('%H:%M:%S')
current_date = str(current_time.date())
if IF_TEST:
current_date = TEST_DATE
failed_detector = self.parsing_failed_check(current_date, start_time, end_time)
if failed_detector is not None:
try:
pg = Postgres(ConnectInf.pg_inf_inter_info)
pg.execute(sql_delete_channel_data)
node_inf = pg.call_pg_data(sql_get_node_inf, fram=True)
node_inf.columns = ['node_id','FSTR_INTERSECTID']
merge_result = pd.merge(failed_detector,node_inf,how='left', on=['FSTR_INTERSECTID'])
return_message = merge_result.values.tolist()
pg.send_pg_data(sql=sql_send_parse_failed_detector, data=return_message)
except Exception as e:
print(e)
return
def call_postgresql(scatsid, logger):
laneInfor = pd.DataFrame({})
try:
# conn = psycopg2.connect(database="signal_specialist", user="******", password="******",
# host="192.168.20.46",
# port="5432")
pg = Postgres()
try:
sql = "select cast(m.coil_code as numeric), n.dir_name, " \
"case when m.function_name = 'icon-func-straight' then '直行' " \
"when m.function_name = 'icon-func-left-straight' then '左直' " \
"when m.function_name = 'icon-func-straight-right' then '直右' " \
"when m.function_name = 'icon-func-left' then '左转' " \
"when m.function_name = 'icon-func-right' then '右转' " \
"when m.function_name = 'icon-func-round' then '掉头' " \
"when m.function_name = 'icon-func-round-left' then '左转掉头' " \
"when m.function_name = 'icon-func-round-straight' then '直行掉头' " \
"when m.function_name = 'icon-func-left-right' then '左右转' " \
"when m.function_name = 'icon-func-three' then '直左右' else '出口' end function_name " \
"from(select a.sys_code, b.trancet_id, b.function_name, b.coil_code " \
"from pe_tobj_node_info a left join pe_tobj_node_lane b on a.node_id = b.node_id " \
"where a.sys_code = '{0}' ) m left join pe_tobj_node_round n on m.trancet_id = n.roadsect_id " \
"where m.coil_code is not null and m.coil_code != '-' ".format(scatsid)
laneInfor = pg.call_pg_data(sql, fram=True)
# cr = conn.cursor()
# cr.execute(sql)
# rs1 = cr.fetchall()
# IntInfor = pd.DataFrame(rs1)
# IntInfor.columns = ['FSTR_INTERSECTID', 'node_name']
# cr.close()
except Exception as e:
logger.error('when reaching lanerinfo, ' + str(e))
finally:
pg.db_close()
except Exception as e:
logger.error('when calling postgresql, ' + str(e))
return laneInfor
def call_postgresql(int_list, logger):
IntInfor = pd.DataFrame({})
try:
# conn = psycopg2.connect(database="signal_specialist", user="******", password="******",
# host="192.168.20.46",
# port="5432")
pg = Postgres(
pg_inf={
'database': "inter_info",
'user': "******",
'password': "******",
'host': "33.83.100.145",
'port': "5432"
})
# conn = pg.db_conn()
# print('postgresql connect succeed.')
# print('reaching datas...')
try:
sql = "select sys_code from pe_tobj_node_info where node_id in {} ".format(
tuple(int_list))
# cr = conn.cursor()
IntInfor = pg.call_pg_data(sql, fram=True)
# print(IntInfor)
# rs1 = cr.fetchall()
# IntInfor = pd.DataFrame(rs1)
# IntInfor.columns = ['FSTR_INTERSECTID', 'node_name']
# print(IntInfor)
# input()
# conn.close()
except Exception as e:
logger.error('when reaching interinfo, ' + str(e))
finally:
pg.db_close()
except Exception as e:
logger.error('when calling postgresql, ' + str(e))
return IntInfor['sys_code'].tolist()
class OperateAutoDis():
# pg_inf = {'database': "research", 'user': "******", 'password': "******",
# 'host': "192.168.20.45", 'port': "5432"}
# pg_inf = {'database': "signal_specialist", 'user': "******", 'password': "******",
# 'host': "192.168.20.46", 'port': "5432"}
intid_list = ['14KC7097AL0', '14KOB0981K0']
def __init__(self):
self.pg = Postgres()
self.int_auto = {}
def get_int_type(self, intid_list=None):
if IF_TEST:
intid_list = OperateAutoDis.intid_list
str_intid_list = ['\'' + int + '\'' for int in intid_list]
pram = ','.join(str_intid_list)
# print(pram)
result = self.pg.call_pg_data(SqlContent.sql_get_int_type.format(pram))
print('get_int_type', result)
return result
def get_alarm_type(self, intid_list=None):
if IF_TEST:
intid_list = OperateAutoDis.intid_list
result = new_kde_cal()
print(result)
return result
def get_alarm_operate_type(self, intid_list=None):
if IF_TEST:
intid_list = OperateAutoDis.intid_list
str_intid_list = ['\'' + int + '\'' for int in intid_list]
pram = ','.join(str_intid_list)
current_date = dt.datetime.now().date()
stime = str(current_date) + ' 00:00:00'
# print(pram)
result = self.pg.call_pg_data(
SqlContent.sql_get_operate_dispose.format(pram, stime), fram=True)
print('operate', result)
return result
def alarm_auto_set(self, alarm_dispose_data):
auto = None
# TD = 30
# TA = 16
# TW = 16
# TRD = 10
print('alarm_dispose_data', alarm_dispose_data)
for i in alarm_dispose_data:
(int_id, time_point, auto_dis, alarm_id, dis_type, int_name) = i
current_time = dt.datetime.now()
time_delta = (current_time - dt.datetime.strptime(
str(time_point), '%Y-%m-%d %H:%M:%S')).seconds
current_time = dt.datetime.now()
# 关注
if dis_type == 1 and time_delta < 60 * TA:
end_time = dt.datetime.strptime(
str(time_point),
'%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TA)
self.int_auto[int_id] = {
'lastDis': '1',
'auto': 1,
'endTime': end_time
}
auto = True
# 调控,调控完以后，30分钟路口不自动处置
elif dis_type == 2 and time_delta < 60 * TD:
end_time = dt.datetime.strptime(
str(time_point),
'%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TD)
self.int_auto[int_id] = {
'lastDis': '2',
'auto': 0,
'endTime': end_time
}
auto = False
# 误报
elif dis_type == 3 and time_delta < 60 * TW:
end_time = dt.datetime.strptime(
str(time_point),
'%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TW)
self.int_auto[int_id] = {
'lastDis': '3',
'auto': 1,
'endTime': end_time
}
auto = True
else:
# 无处置记录
end_time = current_time + dt.timedelta(seconds=60 * TRD)
self.int_auto[int_id] = {
'lastDis': '5',
'auto': 0,
'endTime': end_time
}
auto = False
return auto
def alarm_auto_judge(self, alarm_int_list):
"""
:param alarm_int_list: 报警路口列表
:return: 报警是否自动处置结果
lastDis：上一次路口处置状态【1：关注；2：调控；3：误报；4：快处；5：推送人工】
"""
reponse_all = []
# alarm_type 报警常发偶发判断结果
# self.frame_int_state = self.get_alarm_operate_type(alarm_int_list)
# int_type = self.get_int_type(alarm_int_list)
# alarm_type = self.get_alarm_type()
try:
self.frame_int_state = self.get_alarm_operate_type(alarm_int_list)
int_type = self.get_int_type(alarm_int_list)
alarm_type = self.get_alarm_type()
except Exception as e:
print("获取路口报警类型失败 或 计算报警强度失败")
print(e)
else:
int_type = dict(int_type)
if alarm_type:
fre_alarm_list = [
i[0] for i in alarm_type if i[4] == '0' or i[4] == '0.0'
]
else:
fre_alarm_list = []
for int in alarm_int_list:
int_alarm_type = int_type.get(int)
# 报警较少路口,检测连续报警
if int_alarm_type == '0':
reponse_all = self.continue_alarm_judge(int, reponse_all)
# 报警较多路口，常发报警人工处置，偶发报警检测连续报警
elif int_alarm_type == '1':
# 常发报警
if int in fre_alarm_list:
reponse_all = self.man_disposal_judge(int, reponse_all)
# 偶发报警
else:
reponse_all = self.continue_alarm_judge(
int, reponse_all)
return reponse_all
def auto_disposal_delta(self, int, reponse_all):
int_key = self.int_auto.keys()
int_check_state = []
current_time = dt.datetime.now()
if int not in int_key:
new_end_time = current_time + dt.timedelta(seconds=TRD * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
else:
int_auto_data = self.int_auto.get(int)
last_dis = int_auto_data.get('lastDis')
auto = int_auto_data.get('auto')
end_time = int_auto_data.get('endTime')
if current_time > end_time:
# 路口超时后，自动处置第一次报警！同时将路口状态设置为自动处置
new_end_time = current_time + dt.timedelta(seconds=TRD * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
elif (last_dis == '4' or last_dis
== '5') and auto == 0 and current_time <= end_time:
# 规定时间间隔内，上一次处置为自动处置
# 1、判断路口类型；2、判断路口报警类型
int_check_state.append(int)
pass
elif auto == 1 and current_time <= end_time:
# 路口被设置为自动处置，且未超时
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
def man_disposal_judge(self, int, reponse_all):
"""
:param int: 需要查询的路口
:param reponse_all: 判断结果列表
:return: 判断结果列表
根据人工处置记录判断是否快速处置
"""
frame_int_state = self.frame_int_state
try:
match_dis_type = frame_int_state[frame_int_state['inter_id'] ==
int].values
except Exception as e:
print(e)
else:
# 若路口匹配到了调控记录
auto = self.alarm_auto_set(match_dis_type)
if auto is True:
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
elif auto is False:
reponse = {'alarmInt': int, 'autoDis': False}
reponse_all.append(reponse)
else:
# 匹配不到操作记录，则推送人工
reponse = {'alarmInt': int, 'autoDis': False}
reponse_all.append(reponse)
return reponse_all
def continue_alarm_judge(self, int, reponse_all):
int_key = self.int_auto.keys()
current_time = dt.datetime.now()
if int not in int_key:
new_end_time = current_time + dt.timedelta(seconds=TRD * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
else:
int_auto_data = self.int_auto.get(int)
last_dis = int_auto_data.get('lastDis')
auto_signal = int_auto_data.get('auto')
end_time = int_auto_data.get('endTime')
if current_time > end_time:
# 路口超时后，自动处置第一次报警！同时将路口状态设置为自动处置
new_end_time = current_time + dt.timedelta(seconds=TRD * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
elif (last_dis == '4' or last_dis
== '5') and auto_signal == 0 and current_time <= end_time:
# 规定时间间隔内，上一次处置为自动处置
# 1、判断路口类型；2、判断路口报警类型
reponse_all = self.man_disposal_judge(int, reponse_all)
pass
elif auto_signal == 1 and current_time <= end_time:
# 路口被设置为自动处置，且未超时
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
return reponse_all
class OperateAutoDis():
# pg_inf = {'database': "research", 'user': "******", 'password': "******",
# 'host': "192.168.20.45", 'port': "5432"}
# pg_inf = {'database': "signal_specialist", 'user': "******", 'password': "******",
# 'host': "192.168.20.46", 'port': "5432"}
intid_list = ['14KC7097AL0', '14KOB0981K0']
def __init__(self):
self.pg = Postgres()
self.int_auto = {}
def get_int_type(self, intid_list=None):
if TEST:
intid_list = OperateAutoDis.intid_list
str_intid_list = ['\'' + int + '\'' for int in intid_list]
pram = ','.join(str_intid_list)
# print(pram)
result = self.pg.call_pg_data(SqlContent.sql_get_int_type.format(pram))
print('get_int_type', result)
return result
def get_alarm_type(self, intid_list=None):
if TEST:
intid_list = OperateAutoDis.intid_list
result = new_kde_cal()
print(result)
return result
def get_alarm_operate_type(self, intid_list=None):
if TEST:
intid_list = OperateAutoDis.intid_list
str_intid_list = ['\'' + int + '\'' for int in intid_list]
pram = ','.join(str_intid_list)
current_date = dt.datetime.now().date()
stime = str(current_date) + ' 00:00:00'
print(pram)
result = self.pg.call_pg_data(
SqlContent.sql_get_operate_dispose.format(pram, stime), fram=True)
print('operate', result)
return result
def alarm_auto_set(self, alarm_dispose_data):
auto = None
for i in alarm_dispose_data:
(int_id, time_point, auto_dis, alarm_id, dis_type, int_name) = i
current_time = dt.datetime.now()
time_delta = (current_time - dt.datetime.strptime(
str(time_point), '%Y-%m-%d %H:%M:%S')).seconds
current_time = dt.datetime.now()
# 关注
if dis_type == 1 and time_delta < 60 * TIME_DELAY_N:
end_time = dt.datetime.strptime(
str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(
seconds=60 * TIME_DELAY_N)
self.int_auto[int_id] = {
'lastDis': '1',
'auto': 1,
'endTime': end_time
}
auto = True
# 调控
elif dis_type == 2 and time_delta < 60 * TIME_DELAY_N:
end_time = dt.datetime.strptime(
str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(
seconds=60 * TIME_DELAY_N)
self.int_auto[int_id] = {
'lastDis': '2',
'auto': 1,
'endTime': end_time
}
auto = True
# 误报
elif dis_type == 3 and time_delta < 60 * TIME_DELAY_W:
end_time = dt.datetime.strptime(
str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(
seconds=60 * TIME_DELAY_W)
self.int_auto[int_id] = {
'lastDis': '3',
'auto': 1,
'endTime': end_time
}
auto = True
else:
# 无处置记录
end_time = current_time + dt.timedelta(seconds=60 *
TIME_DELAY_N)
self.int_auto[int_id] = {
'lastDis': '5',
'auto': 0,
'endTime': end_time
}
auto = False
return auto
def alarm_auto_judge(self, alarm_int_list):
"""
:param alarm_int_list: 报警路口列表
:return: 报警是否自动处置结果
lastDis：上一次路口处置状态【1：关注；2：调控；3：误报；4：快处；5：推送人工】
"""
reponse_all = []
reponse = {'alarmInt': None, 'autoDis': None}
int_key = self.int_auto.keys()
int_check_state = []
current_time = dt.datetime.now()
for int in alarm_int_list:
if int not in int_key:
new_end_time = current_time + dt.timedelta(
seconds=TIME_DELAY_N * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
else:
int_auto_data = self.int_auto.get(int)
last_dis = int_auto_data.get('lastDis')
auto = int_auto_data.get('auto')
end_time = int_auto_data.get('endTime')
if current_time > end_time:
# 路口超时后，自动处置第一次报警！同时将路口状态设置为自动处置
new_end_time = current_time + dt.timedelta(
seconds=TIME_DELAY_N * 60)
self.int_auto[int] = {
'lastDis': '4',
'auto': 0,
'endTime': new_end_time
}
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
elif (last_dis == '4' or last_dis
== '5') and auto == 0 and current_time <= end_time:
# 规定时间间隔内，上一次处置为自动处置
# 1、判断路口类型；2、判断路口报警类型
int_check_state.append(int)
pass
elif auto == 1 and current_time <= end_time:
# 路口被设置为自动处置，且未超时
reponse = {'alarmInt': int, 'autoDis': True}
reponse_all.append(reponse)
if len(int_check_state) > 0:
alarm_type = self.get_alarm_type()
print('alarm_type', alarm_type)
if alarm_type:
fre_alarm_list = [
i[0] for i in alarm_type if i[4] == '0' or i[4] == '0.0'
]
print("常发报警路口", fre_alarm_list)
int_type = self.get_int_type(int_check_state)
frame_int_state = self.get_alarm_operate_type(int_check_state)
for i in int_type:
int_id = i[0]
try:
match_alarm_type = frame_int_state[
frame_int_state['inter_id'] == int_id].values
except Exception as e:
print(e)
else:
# 若路口匹配到了调控记录
auto = self.alarm_auto_set(match_alarm_type)
if auto is True:
reponse = {'alarmInt': int_id, 'autoDis': True}
reponse_all.append(reponse)
else:
# 报警较少路口
if i[1] == '0':
reponse = {
'alarmInt': int_id,
'autoDis': False
}
reponse_all.append(reponse)
# 报警较多路口
elif i[1] == '1':
if int_id in fre_alarm_list:
reponse = {
'alarmInt': int_id,
'autoDis': True
}
reponse_all.append(reponse)
else:
reponse = {
'alarmInt': int_id,
'autoDis': False
}
reponse_all.append(reponse)
else:
logger.warning("无法计算报警强度")
for int in int_check_state:
reponse = {'alarmInt': int, 'autoDis': False}
reponse_all.append(reponse)
else:
pass
return reponse_all
