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(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 alarm_operate_match(self, s_datetime, e_datetime):
pg_inf = pg_inf_46
self.pg = Postgres(pg_inf)
host = pg_inf.get('host')
conn,cr = self.pg.db_conn()
if cr:
cr.execute(sql_alarm_operate_match.format(host,s_datetime,e_datetime,MATCH_LIMITTIME))
conn.commit()
def clear_database():
pg = Postgres()
save_date = (dt.datetime.now() -
dt.timedelta(days=3)).strftime("%Y-%m-%d %H:%M:%S")
pg.execute(SqlText.sql_delete_real_phase.format(save_date))
pg.execute(SqlText.sql_delete_kde_vaue.format(save_date))
pg.db_close()
print("数据库清理完成")
def operate_data_send(operate_data):
send_data = []
for key in operate_data.keys():
describe = operate_data.get(key)
if describe is not None:
datetime = dt.datetime.strftime(dt.datetime.now(), '%Y-%m-%d %H:%M:%S')
oper_num = describe.get('OperNum')
json_describe = json.dumps(describe, ensure_ascii=False)
send_data.append([datetime,json_describe,key,oper_num])
pg = Postgres()
conn, cr = pg.db_conn()
for data in send_data:
# print(data)
cr.execute(sql_insert, data)
conn.commit()
pg.db_close()
def call_pg_function(self,function_name,args):
db = Postgres.get_instance()
result = None
if db.conn is not None:
try:
result = db.cr.callproc(function_name, args)
db.conn.commit()
db.db_close()
except Exception as e:
print('call_pg_function error function_name is %s'% function_name, e)
else:
conn, cr = db.db_conn()
if conn:
try:
assert cr is not None
result = db.cr.callproc(function_name, args)
except psycopg2.IntegrityError as e:
print(e)
finally:
conn.commit()
db.db_close()
else:
logger2.error("数据库连接失败！")
# except Exception as e:
# print('call_pg_function error function_name is %s'%function_name, e)
return result
def subsys_intid_match(self):
db = Postgres.get_instance()
result = db.call_pg_data(SituationOperate.sql_subsys_inf)
grouped = result.groupby(['subsystem_id'])
for k1, group in grouped:
self.subsys_relation[k1] = group['site_id'].tolist()
# print(self.subsys_relation)
return result
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):
current_time = dt.datetime.now()
# current_date = current_time.date()
current_date = str(current_time.date())
if IF_TEST:
current_date = '2018-11-09'
failed_detector = self.parsing_failed_check(current_date)
pg = Postgres(ConnectInf.pg_inf_inter_info)
self.pg.send_pg_data(sql=sql_send_parse_failed_detector, data=failed_detector)
return
def operate_statistic(self):
db = Postgres.get_instance()
if IFTEST:
result = db.call_pg_data(SituationOperate.sql_operate_statistic_test)
else:
result = db.call_pg_data(SituationOperate.sql_operate_statistic)
if len(result)>0:
result['oper_type'] = result['all_type'].apply(lambda x: list(set(x.split(','))))
# self.operator_statistic(result)
self.int_statistic(result)
return result
else:
print("can't get operate data!please check database table and time!")
return None
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 OperateStatistics():
def __init__(self, operate_data=pd.DataFrame({})):
self.operate_data = operate_data
self.count_dict = {}
for key in KEYWORD:
self.count_dict[key] = 0
def operate_type_percent(self):
operate_data = self.operate_data
for i in range(len(operate_data)):
oper_type = operate_data.iloc[i][6]
for type in oper_type:
count = self.count_dict.get(type)
if count is not None:
self.count_dict[type] += 1
return self.count_dict
def user_operate_sum(self):
operate_data = self.operate_data
grouped = operate_data.groupby('userid')
result = {}
# operate_data.drop_duplicates()
for index, group in grouped:
operater = Operater()
sum_num = len(group['opertime'].drop_duplicates().tolist())
result[index] = {'OperNum':sum_num}
for i in range(len(group)):
oper_type = group.iloc[i][6]
for type in oper_type:
count = operater.count_dict.get(type)
if count is not None:
operater.count_dict[type] += 1
result[index]['TypeCount'] = operater.count_dict
return result
def alarm_operate_match(self, s_datetime, e_datetime):
pg_inf = pg_inf_46
self.pg = Postgres(pg_inf)
host = pg_inf.get('host')
conn,cr = self.pg.db_conn()
if cr:
cr.execute(sql_alarm_operate_match.format(host,s_datetime,e_datetime,MATCH_LIMITTIME))
conn.commit()
def call_pg_data(sql,pg_inf=None):
if pg_inf:
db = Postgres(pg_inf)
else:
db = Postgres()
pg_conn, cr = db.db_conn()
if pg_conn:
cr.execute(sql)
index = cr.description
result = cr.fetchall()
db.db_close()
if result:
fresult = tuple2frame(result, index)
return fresult
else:
return pd.DataFrame({})
def so_run(message_quue):
db = Postgres.get_instance()
interval = SO_INTERVAL
local_time = dt.datetime.now()
stime = local_time - dt.timedelta(minutes=10)
etime = local_time
stime = dt.datetime.strftime(stime, '%Y-%m-%d %H:%M:%S')
etime = dt.datetime.strftime(etime, '%Y-%m-%d %H:%M:%S')
# stime = '2018-10-22 00:00:00'
# etime = '2018-10-22 23:00:00'
args = [stime, etime, interval]
S1 = SituationOperate()
try:
S1.call_pg_function(S1.sql_alarm_operate_match, args)
except Exception as e:
logger2.error('so_run', e)
else:
message_quue.put()
logger2.info("完成一轮调控记录匹配")
finally:
db.db_close()
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 MainProcess():
global IntersectIDlist # 路口列表
global IntStrInput # 配置表
# global enjoy_strategicInfo
global last_date
global no_data_node
global resolve_fail_node
no_data_node = {}
resolve_fail_node = []
IntersectInfo = CallOracle() # 从数据库读取路口列表
currenttime = dt.datetime.now()
last_date = currenttime.date()
if len(IntersectInfo) > 0:
IntersectIDlist = IntersectInfo['SITEID']
pg_inf_inter_info = {'database': "inter_info", 'user': "******", 'password': "******",
'host': "192.168.20.46", 'port': "5432"}
try:
pg = Postgres(pg_inf_inter_info)
except Exception as e:
print('MainProcess:连接数据库失败', e)
else:
IntStrInput = pg.call_pg_data(sql_get_scats_input)
print(IntStrInput)
if IntStrInput is not None:
int_id = np.array(IntersectIDlist).tolist()
int_num = len(int_id)
print("请求总路口数：", int_num)
group = round(int_num / CONSTANT.group_interval, 0)+1
int_grouped_data = int_grouped(int_id, group)
thread_creat(int(group), int_grouped_data,IntStrInput)
return
else:
print('获取节点列表失败')
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
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
def __init__(self):
self.pg = Postgres()
self.int_auto = {}
def __init__(self):
self.status = None
self.pg = Postgres(SqlText.pg_inf_arith)
self.ora = Oracle(SqlText.OracleUser)
self.conn, self.cr = self.ora.db_conn()
class InterfaceStatus():
def __init__(self):
self.status = None
self.pg = Postgres(SqlText.pg_inf_arith)
self.ora = Oracle(SqlText.OracleUser)
self.conn, self.cr = self.ora.db_conn()
@timer
def salklist_status(self, date, stime, etime):
try:
self.cr.execute(sql_get_salklist_status, a=date, b=stime, f=etime)
except cx_Oracle.InterfaceError:
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_get_salklist_status, a=date, b=stime, f=etime)
result = self.cr.fetchall()
# print(result)
self.conn.commit()
# self.ora.db_close()
data_num = result[0][0]
return data_num
@timer
def operate_status(self, stime, etime):
# stime = dt.datetime.strptime(stime, '%Y-%m-%d %H:%M:%S')
# etime = dt.datetime.strptime(etime, '%Y-%m-%d %H:%M:%S')
try:
self.cr.execute(sql_get_opetate_status, a=stime, b=etime)
except cx_Oracle.InterfaceError:
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_get_opetate_status, a=stime, b=etime)
result = self.cr.fetchall()
# print(result)
self.conn.commit()
data_num = result[0][0]
return data_num
@timer
def parsing_failed_check(self, date,sdate,edate):
def tuple2frame(result, index):
column_name = []
for i in range(len(index)):
index_name = index[i][0]
column_name.append(index_name)
result = pd.DataFrame(result, columns=column_name)
return result
# stime = dt.datetime.strptime(stime, '%Y-%m-%d %H:%M:%S')
# etime = dt.datetime.strptime(etime, '%Y-%m-%d %H:%M:%S')
try:
self.cr.execute(sql_failed_detector, a=date, b=parse_failed_judge,c=sdate,d=edate)
print(sql_failed_detector)
except cx_Oracle.InterfaceError:
# 如果连接异常，重新连接数据库
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_failed_detector, a=date, b=parse_failed_judge,c=sdate,d=edate)
index = self.cr.description
result = self.cr.fetchall()
fresult = None
if result is not None:
fresult = tuple2frame(result, index)
if IF_TEST: #测试模式
result = list(result)
for i in range(len(result)):
result[i] = list(result[i])
# result[i][3] = str(dt.datetime.now().date())
self.conn.commit()
return fresult
def salk_send(self, delta=15):
current_time = dt.datetime.now()
start_time = current_time - 2*dt.timedelta(minutes=delta)
end_time = current_time - dt.timedelta(minutes=delta)
current_date = str(current_time.date())
if IF_TEST:
current_date = TEST_DATE
test_time = TEST_DATE + ' ' + str(current_time.strftime('%H:%M:%S'))
start_time = dt.datetime.strptime(test_time, "%Y-%m-%d %H:%M:%S") - dt.timedelta(minutes=delta)
# print(current_date, start_time.strftime("%H:%M:%S"), current_time.strftime("%H:%M:%S"))
salk_num = self.salklist_status(current_date, start_time.strftime("%H:%M:%S"),
end_time.strftime("%H:%M:%S"))
if salk_num == 0:
StartTime = (dt.datetime.now() - dt.timedelta(minutes=15)).strftime('%Y-%m-%d %H:%M:%S')
EndTime = dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
SiteID = '1'
payload1 = {'SiteID': SiteID, 'STime': StartTime, 'ETime': EndTime}
# 4.3获取战略运行记录
try:
RunStrInformation = requests.get(r'http://33.83.100.138:8080/getStrategicmonitor.html',
params=payload1, timeout=5) # 4.3
RunStrInformation = RunStrInformation.text
except Exception as e:
print(type(e))
# Log.warning("RunStrInformation: request timeout!")
exception = "request failed,error type: %s" % type(e)
else:
RunStrInformation = json.loads(RunStrInformation)
if len(RunStrInformation['resultList']) > 0:
exception = "request success,data length:%s" % len(RunStrInformation['resultList'])
else:
exception = "request success,but no data"
else:
exception = None
message_salk = [['战略运行记录接口', current_time, salk_num, exception]]
self.pg.send_pg_data(sql=sql_send_message, data=message_salk)
return
def operate_send(self, delta=15):
current_time = dt.datetime.now()
# start_time = current_time - dt.timedelta(minutes=delta)
start_time = current_time - 2 * dt.timedelta(minutes=delta)
end_time = current_time - dt.timedelta(minutes=delta)
if IF_TEST:
test_time = TEST_DATE + ' ' + str(current_time.strftime('%H:%M:%S'))
test_time = dt.datetime.strptime(test_time, "%Y-%m-%d %H:%M:%S")
test_start_time = current_time - dt.timedelta(minutes=delta)
operate_num = self.operate_status(test_start_time.strftime("%Y-%m-%d %H:%M:%S"),
test_time.strftime("%Y-%m-%d %H:%M:%S"))
else:
operate_num = self.operate_status(start_time.strftime("%Y-%m-%d %H:%M:%S"),
end_time.strftime("%Y-%m-%d %H:%M:%S"))
if operate_num == 0:
StartTime = (dt.datetime.now() - dt.timedelta(minutes=15)).strftime('%Y-%m-%d %H:%M:%S')
EndTime = dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
payload1 = {r'STime': StartTime, r'ETime': EndTime}
try:
get_response = requests.get(r'http://33.83.100.138:8080/getOperatorIntervention.html',
params=payload1, timeout=10) # 4.7
GetManoperationRecord = get_response.text
print("操作记录请求成功")
except Exception as e:
print(e)
exception = "request failed,error type: %s" % type(e)
else:
GetManoperationRecord = json.loads(GetManoperationRecord)
if len(GetManoperationRecord['resultList']) > 0:
exception = "request success,data length:%s" % len(GetManoperationRecord['resultList'])
else:
exception = "request success,but no data"
else:
exception = None
message_operate = [['人工操作记录接口', current_time, operate_num, exception]]
self.pg.send_pg_data(sql=sql_send_message, data=message_operate)
return
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
class InterfaceStatus():
OracleUser = '******'
pg_inf = {'database': "arithmetic", 'user': "******", 'password': "******",
'host': "192.168.20.46", 'port': "5432"}
def __init__(self):
self.status = None
self.pg = Postgres(InterfaceStatus.pg_inf)
self.ora = Oracle(InterfaceStatus.OracleUser)
self.conn, self.cr = self.ora.db_conn()
@timer
def salklist_status(self, date, stime, etime):
try:
self.cr.execute(sql_get_salklist_status, a=date, b=stime, f=etime)
except cx_Oracle.InterfaceError:
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_get_salklist_status, a=date, b=stime, f=etime)
result = self.cr.fetchall()
# print(result)
self.conn.commit()
# self.ora.db_close()
data_num = result[0][0]
return data_num
@timer
def operate_status(self, stime, etime):
stime = dt.datetime.strptime(stime, '%Y-%m-%d %H:%M:%S')
etime = dt.datetime.strptime(etime, '%Y-%m-%d %H:%M:%S')
try:
self.cr.execute(sql_get_opetate_status, a=stime, b=etime)
except cx_Oracle.InterfaceError:
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_get_opetate_status, a=stime, b=etime)
result = self.cr.fetchall()
# print(result)
self.conn.commit()
data_num = result[0][0]
return data_num
@timer
def parsing_failed_check(self, date):
# stime = dt.datetime.strptime(stime, '%Y-%m-%d %H:%M:%S')
# etime = dt.datetime.strptime(etime, '%Y-%m-%d %H:%M:%S')
try:
self.cr.execute(sql_failed_detector, a=date, b=parse_failed_judge)
except cx_Oracle.InterfaceError:
self.conn, self.cr = self.ora.db_conn()
self.cr.execute(sql_failed_detector, a=date, b=parse_failed_judge)
result = self.cr.fetchall()
if IF_TEST:
result = list(result)
for i in range(len(result)):
result[i] = list(result[i])
result[i][3] = str(dt.datetime.now().date())
# print(result)
self.conn.commit()
return result
def salk_send(self, delta=15):
current_time = dt.datetime.now()
start_time = current_time - dt.timedelta(minutes=delta)
current_date = str(current_time.date())
if IF_TEST:
current_date = TEST_DATE
test_time = TEST_DATE + ' ' + str(current_time.strftime('%H:%M:%S'))
start_time = dt.datetime.strptime(test_time, "%Y-%m-%d %H:%M:%S") - dt.timedelta(minutes=delta)
# print(current_date, start_time.strftime("%H:%M:%S"), current_time.strftime("%H:%M:%S"))
salk_num = self.salklist_status(current_date, start_time.strftime("%H:%M:%S"),
current_time.strftime("%H:%M:%S"))
if salk_num == 0:
StartTime = (dt.datetime.now() - dt.timedelta(minutes=15)).strftime('%Y-%m-%d %H:%M:%S')
EndTime = dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
SiteID = '1'
payload1 = {'SiteID': SiteID, 'STime': StartTime, 'ETime': EndTime}
# 4.3获取战略运行记录
try:
RunStrInformation = requests.get(r'http://33.83.100.138:8080/getStrategicmonitor.html',
params=payload1, timeout=5) # 4.3
RunStrInformation = RunStrInformation.text
except Exception as e:
print(type(e))
# Log.warning("RunStrInformation: request timeout!")
exception = "request failed,error type: %s" % type(e)
else:
RunStrInformation = json.loads(RunStrInformation)
if len(RunStrInformation['resultList']) > 0:
exception = "request success,data length:%s" % len(RunStrInformation['resultList'])
else:
exception = "request success,but no data"
else:
exception = None
message_salk = [['战略运行记录接口', current_time, salk_num, exception]]
self.pg.send_pg_data(sql=sql_send_message, data=message_salk)
return
def operate_send(self, delta=15):
current_time = dt.datetime.now()
start_time = current_time - dt.timedelta(minutes=delta)
if IF_TEST:
test_time = TEST_DATE + ' ' + str(current_time.strftime('%H:%M:%S'))
test_time = dt.datetime.strptime(test_time, "%Y-%m-%d %H:%M:%S")
test_start_time = current_time - dt.timedelta(minutes=delta)
operate_num = self.operate_status(test_start_time.strftime("%Y-%m-%d %H:%M:%S"),
test_time.strftime("%Y-%m-%d %H:%M:%S"))
else:
operate_num = self.operate_status(start_time.strftime("%Y-%m-%d %H:%M:%S"),
current_time.strftime("%Y-%m-%d %H:%M:%S"))
if operate_num == 0:
StartTime = (dt.datetime.now() - dt.timedelta(minutes=15)).strftime('%Y-%m-%d %H:%M:%S')
EndTime = dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
payload1 = {r'STime': StartTime, r'ETime': EndTime}
try:
get_response = requests.get(r'http://33.83.100.138:8080/getOperatorIntervention.html',
params=payload1, timeout=10) # 4.7
GetManoperationRecord = get_response.text
print("操作记录请求成功")
except Exception as e:
print(e)
exception = "request failed,error type: %s" % type(e)
else:
GetManoperationRecord = json.loads(GetManoperationRecord)
if len(GetManoperationRecord['resultList']) > 0:
exception = "request success,data length:%s" % len(GetManoperationRecord['resultList'])
else:
exception = "request success,but no data"
else:
exception = None
message_operate = [['人工操作记录接口', current_time, operate_num, exception]]
self.pg.send_pg_data(sql=sql_send_message, data=message_operate)
return
def parse_failed_detector_send(self):
current_time = dt.datetime.now()
# current_date = current_time.date()
current_date = str(current_time.date())
if IF_TEST:
current_date = '2018-11-09'
failed_detector = self.parsing_failed_check(current_date)
pg = Postgres(ConnectInf.pg_inf_inter_info)
self.pg.send_pg_data(sql=sql_send_parse_failed_detector, data=failed_detector)
return
def __init__(self):
self.status = None
self.pg = Postgres(InterfaceStatus.pg_inf)
self.ora = Oracle(InterfaceStatus.OracleUser)
self.conn, self.cr = self.ora.db_conn()
def get_operate_data(self):
db = Postgres.get_instance()
result = db.call_pg_data(SituationOperate.sql)
return result
