Files
Gset/importer.py
treeandClaude Opus 5.5 0e070a64fe P1:闭环时限管控、治理成效评估、站型阈值系数与接口测试
- 闭环状态流转自动记录接单/闭环时间,状态值校验;按断站分级判定响应时限
  (及时/超时/超时未响应/未到期/无记录),新增及时响应率、平均闭环时长、超时未闭环指标与筛选
- 新增治理成效评估 /api/effect:分界日期前后指标对比与问题站点逐站改善判定
- 新增站型阈值系数(实际阈值 = 场景阈值 × 站型系数)及配置接口与页面
- 样例数据新增接单/闭环时间(独立随机数,原字段不变),导入支持并校验
- 新增 tests/test_api.py(权限、导入模板、闭环流程、站型系数、成效评估、无数据边界)
- 修复:新流水无告警匹配时根因分析报错;空筛选时超长站点清单返回 500

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-18 15:14:06 +08:00

447 lines
20 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""数据导入:读取上传文件 → 识别文件类型 → 逐行校验,返回合格数据与问题行,入库由 core.py 完成。
支持四种文件(上传时自动识别,不需要用户选择):
outage 退服流水(本系统模板 / data_outage.csv 口径,支持中文表头)
alarm 告警流(data_alarm.csv 口径,含 alarm_code 列)
nms_alarm 华为网管「当前告警浏览」导出 CSV(Reference-data/参数及告警检查工具)
site 5G 工参表,电信 / 移动两种格式(Reference-data/电信5G工参.xlsx、移动5G工参.xlsx)
每个 validate_xxx 函数都返回 (合格数据 DataFrame, 问题行列表),问题行格式为
{'row': 文件中的行号, 'key': 编号, 'start_time': 时间(没有则为空), 'reason': 不合格原因}。
"""
import io
import numpy as np
import pandas as pd
from core import CAUSES, TIME_FMT, query
# 用来在文件前几行里找“表头行”的关键字:网管导出的文件在表头前有几行说明文字
HEADER_KEYWORDS = ['ne_id', 'alarm_id', 'start_time', '基站编号', '流水号', '告警ID', 'gNodeBID', 'ENBID']
# ================================================================ 1. 读取上传文件
def read_upload(raw, filename):
"""把上传文件读成 DataFrame,所有列按文本读取(避免基站编号被读成小数),自动跳过表头前的说明行。"""
name = filename.lower()
if name.endswith('.csv'):
return read_csv_text(decode(raw))
if name.endswith(('.xlsx', '.xls')):
return read_excel(raw)
raise ValueError('仅支持 Excel / CSV 文件')
def decode(raw):
"""网管导出的 CSV 多为 GBK 编码,本系统模板为 UTF-8,两种都尝试。"""
try:
return raw.decode('utf-8-sig')
except UnicodeDecodeError:
return raw.decode('gbk', errors='replace')
def read_csv_text(text):
# 网管导出文件开头有“保存时间、用户名、筛选条件”等说明,其中还有跨行的引号内容,
# 所以不能按行数跳过,而是找到表头所在行,从那一行的位置截取文本再交给 pandas。
lines = text.splitlines(keepends=True)
header_line = 0
for i, line in enumerate(lines[:50]):
if any(word in line for word in HEADER_KEYWORDS):
header_line = i
break
body = ''.join(lines[header_line:])
return pd.read_csv(io.StringIO(body), dtype=str)
def read_excel(raw):
# 先不指定表头读一遍前几十行,找到表头行,再按该行作为表头正式读取(只读第一个工作表)
preview = pd.read_excel(io.BytesIO(raw), header=None, nrows=50, dtype=str)
header_row = 0
for i in range(len(preview)):
cells = [str(v) for v in preview.iloc[i].tolist()]
if any(word in cell for cell in cells for word in HEADER_KEYWORDS):
header_row = i
break
df = pd.read_excel(io.BytesIO(raw), header=header_row, dtype=str)
df.columns = [str(c).strip() for c in df.columns]
return df
# ================================================================ 2. 识别类型并校验
def validate(df):
"""识别文件类型并调用对应的校验函数,返回 (类型, 类型说明, 合格数据, 问题行)。"""
df = df.reset_index(drop=True)
df.columns = [str(c).strip() for c in df.columns]
columns = set(df.columns)
if '告警ID' in columns and '首次发生时间(NT)' in columns:
good, errors = validate_nms_alarms(df)
return 'alarm', '告警流(华为网管导出)', good, errors
if 'alarm_code' in columns or '告警名称' in columns:
good, errors = validate_alarms(df)
return 'alarm', '告警流', good, errors
if 'gNodeBID' in columns:
good, errors = validate_sites(df, SITE_COLUMNS_TELECOM)
return 'site', '站点工参(电信格式)', good, errors
if 'ENBID' in columns:
good, errors = validate_sites(df, SITE_COLUMNS_MOBILE)
return 'site', '站点工参(移动格式)', good, errors
good, errors = validate_outages(df)
return 'outage', '退服流水', good, errors
def clean_text(series):
"""
统一成去掉首尾空格的文本,空值、'nan'、'None'、'-' 都变成空字符串。
注意要先 fillna 再 astype(str):pandas 3 起 astype(str) 会保留空值,不再变成字符串 'nan'。
"""
return series.fillna('').astype(str).str.strip().replace({'nan': '', 'None': '', '-': ''})
def parse_time(series):
"""把一列文本解析成时间;空值、'-'、格式错误都变成 NaT。网管时间为“月/日/年”格式,pandas 默认按月在前解析。"""
return pd.to_datetime(clean_text(series), errors='coerce', format='mixed')
def rename_columns(df, aliases):
"""按别名表把中文 / 其他写法的表头改成系统字段名。aliases: {字段名: [别名, ...]}"""
mapping = {}
for field, names in aliases.items():
for column in df.columns:
if column == field or column in names:
mapping[column] = field
return df.rename(columns=mapping)
# ---------------------------------------------------------------- 2.1 退服流水
OUTAGE_ALIASES = {
'record_id': ['流水号'], 'ne_id': ['基站编号', '站号'], 'site_name': ['基站名称', '站点名称'],
'cell_id': ['小区', '小区编号'], 'start_time': ['退服开始时间', '开始时间'],
'end_time': ['退服恢复时间', '恢复时间', '结束时间'], 'duration_min': ['退服时长', '退服时长(分钟)'],
'cause_category': ['原因大类', '退服原因'], 'cause_detail': ['原因细项'], 'cause_code': ['原因码'],
'ticket_id': ['工单号'], 'closed': ['是否闭环'],
'handled_at': ['handle_time', '接单时间', '响应时间'], 'closed_at': ['close_time', '闭环时间', '回单时间'],
}
OUTAGE_SAVE_COLUMNS = ['record_id', 'ne_id', 'cell_id', 'start_time', 'end_time', 'duration_min', 'reported_min',
'cause_category', 'cause_detail', 'cause_code', 'ticket_id', 'status', 'handled_at',
'closed_at']
def validate_outages(df):
"""
退服流水校验:必填字段、站号是否在站点库、时间格式、恢复早于开始、超过 30 天、文件内及库内重复、
接单 / 闭环时间格式及是否早于退服开始。
退服时长一律按“恢复时间 − 开始时间”重算;文件里人工上报的时长保存为 reported_min,供核对差错。
闭环状态:是否闭环=是 或有闭环时间 → 已闭环;否则有接单时间 → 处理中;其余 → 待处理。
"""
df = rename_columns(df, OUTAGE_ALIASES)
# 文件只给了站名没给站号时,按站点库把站名换成站号
if 'ne_id' not in df.columns and 'site_name' in df.columns:
name_to_ne = {}
for site in query('SELECT ne_id, site_name FROM sites'):
name_to_ne[site['site_name']] = site['ne_id']
df['ne_id'] = df['site_name'].map(name_to_ne).fillna(df['site_name'])
missing = []
for field in ('ne_id', 'start_time', 'end_time'):
if field not in df.columns:
missing.append(OUTAGE_ALIASES[field][0])
if missing:
raise ValueError('缺少必填字段:' + '、'.join(missing))
for field in OUTAGE_ALIASES:
if field not in df.columns:
df[field] = None
df['ne_id'] = clean_text(df['ne_id'])
start_text = clean_text(df['start_time']) # 问题行里原样显示用户填写的开始时间
start = parse_time(df['start_time'])
end = parse_time(df['end_time'])
handled = parse_time(df['handled_at'])
closed = parse_time(df['closed_at'])
handled_given = clean_text(df['handled_at']) != ''
closed_given = clean_text(df['closed_at']) != ''
known_sites = {row['ne_id'] for row in query('SELECT ne_id FROM sites')}
existing = {(row['ne_id'], row['start_time']) for row in query('SELECT ne_id, start_time FROM outages')}
seen_in_file = set()
errors = []
good_rows = []
for i in range(len(df)):
ne_id = df.at[i, 'ne_id']
reasons = []
if not ne_id:
reasons.append('基站编号缺失')
elif ne_id not in known_sites:
reasons.append('基站不在站点库中')
if pd.isna(start[i]):
reasons.append('开始时间缺失或格式错误')
if pd.isna(end[i]):
reasons.append('恢复时间缺失或格式错误')
# 以下检查依赖时间有效,前面已有问题就不再继续判断
if not reasons:
key = (ne_id, start[i].strftime(TIME_FMT))
if end[i] < start[i]:
reasons.append('恢复时间早于开始时间')
elif (end[i] - start[i]).days > 30:
reasons.append('退服时长超过30天,疑似录入错误')
elif key in seen_in_file or key in existing:
reasons.append('重复记录(同站同开始时间)')
seen_in_file.add(key)
if handled_given[i] and pd.isna(handled[i]):
reasons.append('接单时间格式错误')
elif handled_given[i] and handled[i] < start[i]:
reasons.append('接单时间早于退服开始')
if closed_given[i] and pd.isna(closed[i]):
reasons.append('闭环时间格式错误')
elif closed_given[i] and closed[i] < start[i]:
reasons.append('闭环时间早于退服开始')
if reasons:
errors.append({'row': i + 2, 'key': ne_id, 'start_time': start_text[i], 'reason': ';'.join(reasons)})
else:
good_rows.append(i)
good = df.loc[good_rows].copy()
good_start = start.loc[good_rows]
good_end = end.loc[good_rows]
good['start_time'] = good_start.dt.strftime(TIME_FMT)
good['end_time'] = good_end.dt.strftime(TIME_FMT)
good['reported_min'] = pd.to_numeric(good['duration_min'], errors='coerce').astype(float)
good['duration_min'] = ((good_end - good_start).dt.total_seconds() / 60).round(1)
good['handled_at'] = handled.loc[good_rows].dt.strftime(TIME_FMT)
good['closed_at'] = closed.loc[good_rows].dt.strftime(TIME_FMT)
is_closed = (clean_text(good['closed']) == '是') | good['closed_at'].notna()
good['status'] = np.select([is_closed, good['handled_at'].notna()], ['已闭环', '处理中'], default='待处理')
# 原因大类只接受系统定义的 7 类,其他写法视为未填写
good['cause_category'] = good['cause_category'].where(good['cause_category'].isin(CAUSES), None)
good = good[OUTAGE_SAVE_COLUMNS]
good = good.astype(object).where(good.notna(), None) # NaN 换成 None,入库后是 NULL
return good, errors
# ---------------------------------------------------------------- 2.2 告警流(本系统口径)
ALARM_SAVE_COLUMNS = ['alarm_id', 'ne_id', 'alarm_code', 'alarm_name', 'alarm_level', 'category', 'start_time',
'clear_time', 'duration_s', 'cleared', 'parent_alarm_id']
def validate_alarms(df):
missing = []
for field in ('alarm_id', 'ne_id', 'category', 'start_time'):
if field not in df.columns:
missing.append(field)
if missing:
raise ValueError('告警文件缺少字段:' + '、'.join(missing))
for field in ALARM_SAVE_COLUMNS:
if field not in df.columns:
df[field] = None
start = parse_time(df['start_time'])
bad = df['ne_id'].isna() | start.isna() | df['alarm_id'].isna() | df['alarm_id'].duplicated()
alarm_id_text = clean_text(df['alarm_id'])
start_text = clean_text(df['start_time'])
errors = []
for i in df.index[bad]:
errors.append({'row': i + 2, 'key': alarm_id_text[i], 'start_time': start_text[i],
'reason': '告警编号缺失/重复、基站编号缺失或时间格式错误'})
good = df.loc[~bad, ALARM_SAVE_COLUMNS].copy()
good['start_time'] = start[~bad].dt.strftime(TIME_FMT)
return good, errors
# ---------------------------------------------------------------- 2.3 华为网管告警导出
# 按告警名称关键字归入系统的告警类别。顺序很重要:先判断更具体的类别,
# 例如“射频单元驻波告警”应归天馈而不是设备硬件,“射频单元交流掉电告警”应归电源。
# “小区退服”是退服现象本身,根因分析时用来匹配退服时刻;其余类别对应根因。
NMS_CATEGORY_KEYWORDS = [
('小区退服', ['退服', '小区不可用']),
('割接施工', ['闭塞', '人工']),
('电源故障', ['掉电', '电源', '直流', '交流', '市电', '电池', '欠压', '停电']),
('机房环境', ['温度', '水浸', '烟雾', '空调', '湿度', '门禁']),
('天馈故障', ['驻波', 'VSWR', '天馈', '天线设备']),
('设备硬件', ['射频单元', 'RRU', 'RHUB', 'BBU', 'IR', 'CPRI', '单板', '硬件', '心跳', 'CANBUS',
'时钟', '星卡', 'GPS', '主控', '基带']),
('传输中断', ['SCTP', 'X2', 'S1', '用户面', '传输', '光口', '光模块', '光接口', 'E1/T1', 'IMA',
'SAAL', 'Path', '链路', '连接中断', '断链']),
]
def classify_nms_alarm(name):
for category, keywords in NMS_CATEGORY_KEYWORDS:
for word in keywords:
if word in name:
return category
return '其他'
def validate_nms_alarms(df):
"""
华为网管告警导出 → 系统告警表。字段对应:日志流水号→告警编号,eNodeB ID→基站编号,告警ID→告警码,
首次发生时间→开始时间,清除时间→清除时间;告警类别由告警名称按关键字归类。
没有 eNodeB ID 的是网管系统自身的告警(如账号、License),与基站退服无关,列为问题行不入库。
"""
# 网管导出文件末尾有一行“总计 N 条”,不是告警数据,直接去掉
first_column = df.columns[0]
is_total_row = df[first_column].astype(str).str.startswith('总计')
df = df[~is_total_row].reset_index(drop=True)
ne_id = clean_text(df['eNodeB ID'])
serial = clean_text(df['日志流水号'])
name = clean_text(df['名称'])
start_text = clean_text(df['首次发生时间(NT)'])
start = parse_time(df['首次发生时间(NT)'])
clear = parse_time(df['清除时间(NT)'])
errors = []
good_rows = []
for i in range(len(df)):
reasons = []
if not serial[i]:
reasons.append('日志流水号缺失')
if not ne_id[i]:
reasons.append('无 eNodeB ID(网管系统告警,与基站无关)')
if pd.isna(start[i]):
reasons.append('首次发生时间缺失或格式错误')
if reasons:
errors.append({'row': i + 2, 'key': name[i], 'start_time': start_text[i], 'reason': ';'.join(reasons)})
else:
good_rows.append(i)
good = pd.DataFrame({
'alarm_id': 'NMS-' + serial.loc[good_rows], # 加前缀,避免与本系统告警编号冲突
'ne_id': ne_id.loc[good_rows],
'alarm_code': clean_text(df['告警ID']).loc[good_rows],
'alarm_name': name.loc[good_rows],
'alarm_level': clean_text(df['级别']).loc[good_rows],
'category': name.loc[good_rows].map(classify_nms_alarm),
'start_time': start.loc[good_rows].dt.strftime(TIME_FMT),
'clear_time': clear.loc[good_rows].dt.strftime(TIME_FMT),
'duration_s': (clear.loc[good_rows] - start.loc[good_rows]).dt.total_seconds(),
'parent_alarm_id': None, # 网管导出只标注了“衍生告警”,没有给出父告警编号
})
good['cleared'] = good['clear_time'].notna().map({True: '是', False: '否'})
good = good[ALARM_SAVE_COLUMNS]
good = good.astype(object).where(good.notna(), None)
return good, errors
# ---------------------------------------------------------------- 2.4 5G 工参表 → 站点库
# 工参表一行是一个小区,同一基站有多行;入库时按基站汇总,小区数 = 行数。
SITE_COLUMNS_TELECOM = {
'ne_id': 'gNodeBID', 'site_name': 'gNodeBName', 'district': '区域', 'vendor': None,
'site_type': '站点类型', 'lng': '经度Longitude', 'lat': '纬度Latitude',
}
SITE_COLUMNS_MOBILE = {
'ne_id': 'ENBID', 'site_name': '现网基站名称', 'district': None, 'vendor': '厂商',
'site_type': '覆盖类型(室分、宏站)', 'lng': '经度', 'lat': '纬度',
}
NANNING_DISTRICTS = ['青秀区', '兴宁区', '江南区', '西乡塘区', '良庆区', '邕宁区', '武鸣区']
# 按站名关键字推断通信场景,按顺序取第一个命中;都不命中归为“城中村”(城郊居民区),可在页面上调整。
# 这里的场景名必须是 core.SCENES 中定义的 5 类之一。
SCENE_KEYWORDS = [
('高校', ['大学', '学院', '学校', '校区', '中学', '职校', '师范']),
('交通枢纽', ['火车站', '高铁', '东站', '客运', '机场', '地铁', '汽车站', '收费站', '枢纽', '码头']),
('工业园区', ['工业', '产业园', '园区', '厂', '物流', '开发区', '经开']),
('商圈', ['广场', '商场', '万达', '万象', '购物', '酒店', '大厦', '商务', '市场', '营业厅', '步行街', '中心']),
]
DEFAULT_SCENE = '城中村'
def guess_scene(site_name):
for scene, keywords in SCENE_KEYWORDS:
for word in keywords:
if word in site_name:
return scene
return DEFAULT_SCENE
def guess_site_type(text):
"""工参里的站型写法不统一(室分 / 宏站 / 微站 / 皮站 …),统一成系统的三类。"""
if '室' in text:
return '室分'
if '皮' in text or '微' in text or '分布式' in text:
return '分布式皮基站'
return '宏站'
def guess_district(site_name):
for district in NANNING_DISTRICTS:
if district in site_name:
return district
return '其他'
def column_or_empty(df, column):
"""取一列并清洗;工参格式里没有这一列时返回同长度的空文本列。"""
if column is None or column not in df.columns:
return pd.Series([''] * len(df), index=df.index)
return clean_text(df[column])
def validate_sites(df, columns):
"""
工参表校验并按基站汇总。基站编号缺失或经纬度不在中国范围内的小区行列为问题行;
库中已存在的基站标为“更新”(入库时保留管理员设置过的场景标签),其余为“新增”。
"""
cells = pd.DataFrame({
'ne_id': column_or_empty(df, columns['ne_id']),
'site_name': column_or_empty(df, columns['site_name']),
'district': column_or_empty(df, columns['district']),
'vendor': column_or_empty(df, columns['vendor']),
'site_type': column_or_empty(df, columns['site_type']),
'lng': pd.to_numeric(column_or_empty(df, columns['lng']), errors='coerce'),
'lat': pd.to_numeric(column_or_empty(df, columns['lat']), errors='coerce'),
})
errors = []
good_rows = []
for i in range(len(cells)):
reasons = []
if not cells.at[i, 'ne_id']:
reasons.append('基站编号缺失')
lng = cells.at[i, 'lng']
lat = cells.at[i, 'lat']
if pd.isna(lng) or pd.isna(lat) or not (73 <= lng <= 136) or not (3 <= lat <= 54):
reasons.append('经纬度缺失或不在中国范围内')
if reasons:
errors.append({'row': i + 2, 'key': cells.at[i, 'ne_id'] or cells.at[i, 'site_name'], 'start_time': '',
'reason': ';'.join(reasons)})
else:
good_rows.append(i)
cells = cells.loc[good_rows]
existing = {row['ne_id'] for row in query('SELECT ne_id FROM sites')}
sites = []
for ne_id, group in cells.groupby('ne_id', sort=False):
first = group.iloc[0]
site_name = first['site_name'] or ne_id
district = first['district'] or guess_district(site_name)
if ne_id in existing:
action = '更新'
else:
action = '新增'
sites.append({
'ne_id': ne_id,
'site_name': site_name,
'district': district,
'scene': guess_scene(site_name),
'site_type': guess_site_type(first['site_type']),
'vendor': first['vendor'] or '未知',
'cells': len(group),
'lng': round(float(first['lng']), 6),
'lat': round(float(first['lat']), 6),
'action': action,
})
good = pd.DataFrame(sites, columns=['ne_id', 'site_name', 'district', 'scene', 'site_type', 'vendor',
'cells', 'lng', 'lat', 'action'])
return good, errors