def switches():
form = SwitchSearchForm()
query = NodeSwitch.query.outerjoin(Area, NodeSwitch.area_id==Area.id)
query_dict = dict([(key, request.args.get(key))for key in form.data.keys()])
if query_dict.get("keyword"):
query=query.filter(or_(
NodeSwitch.name.like('%'+query_dict["keyword"]+'%'),
NodeSwitch.alias.like('%'+query_dict["keyword"]+'%'),
NodeSwitch.addr.like('%'+query_dict["keyword"]+'%')
))
if query_dict.get("area"):
netloc = request.args.get('area_netloc')
if 'or' in netloc: netloc = '('+netloc+')'
query = query.filter(netloc)
if query_dict.get("vendor_id"): query=query.filter(NodeSwitch.vendor_id == query_dict["vendor_id"]) # ==
if query_dict.get("model_id"): query=query.filter(NodeSwitch.model_id == query_dict["model_id"]) # ==
if query_dict.get("status"): query=query.filter(NodeSwitch.status == query_dict["status"])
if not current_user.is_province_user: query = query.filter(current_user.domain.clause_permit)
form.process(**query_dict)
table = make_table(query, SwitchTable)
status_statistcs = []
for status in NODE_STATUS_DICT.keys():
num = NodeSwitch.query.filter(NodeSwitch.status == status)
if not current_user.is_province_user: num = num.outerjoin(Area, NodeSwitch.area_id==Area.id).filter(current_user.domain.clause_permit)
num = num.count()
status_statistcs.append({"status": status, "number": num, "name": NODE_STATUS_DICT.get(status)})
if request.base_url.endswith(".xls/"):
csv = XlsExport('switches',columns=NodeSwitch.export_columns())
return send_file(csv.export(query,format={'status': lambda value: NODE_STATUS_DICT.get(value)}),as_attachment=True,attachment_filename='switches.xls')
else:
return render_template('/nodes/switches/index.html', table = table, form=form, status_statistcs=status_statistcs)
def intftraffic(name):
CONFIG = {
'all' : ('intftraffic', u'端口流量'),
'oltup' : ('intftraffic_oltup', u'上联口流量流速'),
'oltpon' : ('intftraffic_oltpon', u'PON口流量流速'),
'onupon' : ('intftraffic_onupon', u'PON口流量流速'),
'eocup' : ('intftraffic_eocup', u'上联口流量流速'),
'cpe' : ('intftraffic_cpe', u'CPE流量流速')
}
menuid, title = CONFIG[name]
form_cls, table_cls = (NodePerfFilterForm, IntfTrafficTable) if name == 'all' \
else (PerfFilterForm, IntfTrafficOctetsTable)
form = form_cls(formdata=request.args)
form.refresh_choices(request.args)
query = form.filter(IntfTrafficPerf)
table = make_table(query, table_cls)
kwargs = {
'menuid' : menuid,
'name' : name,
'title' : title,
'table' : table,
'filterForm' : form
}
if ".csv/" in request.base_url:
csv = CsvExport('intftraffic_'+name,columns=[str(column.accessor) for column in table.columns])
return send_file(csv.export(query),as_attachment=True,attachment_filename='intftraffic_'+name+'.csv')
else:
return render_template('/perf/index.html', **kwargs)
def oplogs():
query = OperationLog.query
filterForm = OplogFilterForm(formdata=request.args)
keyword = filterForm.keyword.data
if keyword and keyword != '':
keyword = keyword.strip()
query = query.filter(db.or_(
OperationLog.terminal_ip.ilike('%'+keyword+'%'),
OperationLog.summary.ilike('%'+keyword+'%')))
user = filterForm.uid.data
if user :
query = query.filter(OperationLog.uid == user.id)
ip = filterForm.ip.data
if ip:
query = query.filter(OperationLog.terminal_ip == ip)
start_date = filterForm.start_date.data
if start_date:
query = query.filter(OperationLog.created_at >= start_date)
end_date = filterForm.end_date.data
if end_date:
query = query.filter(OperationLog.created_at <= end_date)
table = make_table(query, OperationLogTable)
return render_template('/system/oplogs.html',
table=table, filterForm=filterForm)
def eocs():
form = EocSearchForm()
query = NodeEoc.query.outerjoin(Area, NodeEoc.area_id==Area.id)
query_dict = dict([(key, request.args.get(key))for key in form.data.keys()])
if query_dict.get("keyword"):
query=query.filter(or_(
NodeEoc.name.like('%'+query_dict["keyword"]+'%'),
NodeEoc.alias.like('%'+query_dict["keyword"]+'%'),
NodeEoc.addr.like('%'+query_dict["keyword"]+'%')
))
if query_dict.get("area"):
netloc = request.args.get('area_netloc')
print netloc
if 'or' in netloc: netloc = '('+netloc+')'
query = query.filter(netloc)
if query_dict.get("vendor_id"): query=query.filter(NodeEoc.vendor_id == query_dict["vendor_id"]) # ==
if query_dict.get("model_id"): query=query.filter(NodeEoc.model_id == query_dict["model_id"]) # ==
if query_dict.get("status"): query=query.filter(NodeEoc.status == query_dict["status"])
if not current_user.is_province_user: query = query.filter(current_user.domain.clause_permit)
form.process(**query_dict)
table = make_table(query, EocTable)
status_statistcs = []
for status in NODE_STATUS_DICT.keys():
num = NodeEoc.query.filter(NodeEoc.status == status)
if not current_user.is_province_user: num = num.outerjoin(Area, NodeEoc.area_id==Area.id).filter(current_user.domain.clause_permit)
num = num.count()
status_statistcs.append({"status": status, "number": num, "name": NODE_STATUS_DICT.get(status)})
if request.base_url.endswith(".xls/"):
csv = XlsExport('eocs',columns=NodeEoc.export_columns())
return send_file(csv.export(query,format={'status': lambda value: NODE_STATUS_DICT.get(value)}),as_attachment=True,attachment_filename='eocs.xls')
else:
return render_template('/nodes/eocs/index.html', table = table, form=form, status_statistcs=status_statistcs)
def entrances():
netloc = request.args.get('area_netloc') # 区域过滤条件
# 构造各个统计的子查询
sub_query_list = []
categories = Category.query.filter(Category.is_valid == 1).filter(
Category.obj == 'node')
for index, category in [(0, 'total')] + [(category.id, category.name)
for category in categories]:
sub_query = db.session.query(
Area.entrance,
func.count(
Node.id).label(category +
"_count")).select_from(Node).outerjoin(
Area, Node.area_id == Area.id).filter(
current_user.domain.clause_permit)
if index == 0: # 统计总节点数的子查询
sub_query = sub_query.group_by(Area.entrance).subquery()
else:
sub_query = sub_query.filter(Node.category_id == index).group_by(
Area.entrance).subquery()
sub_query_list.append(sub_query)
# 连接各个子查询
export_columns = [
'name', 'alias', 'parent_id', 'longitude', 'latitude', 'remark'
]
query = 'db.session.query(Area.id,Area.name,Area.area_type,Area.alias,Area.longitude,Area.latitude,Area.branch_name.label("parent_id"),'
for index, category in enumerate(
['total'] + [category.name for category in categories]):
if category in ["host", "olt", "eoc"]: continue
query += 'func.coalesce(sub_query_list[%(index)s].c.%(category)s_count,0).label("%(category)s_count"),' % {
'index': index,
'category': category
}
#export_columns.append(category+"_count")
query += ')'
query = eval(query)
for index, sub_query in enumerate(sub_query_list):
query = query.outerjoin(sub_query, sub_query.c.entrance == Area.id)
query = query.filter(Area.area_type == 4)
if not current_user.is_province_user:
query = query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '(' + netloc + ')'
query = query.filter(netloc)
# 隐藏is_valid = 0 的分类
hiddens = u','.join([
category.name + '_count' for category in Category.query.filter(
Category.obj == 'node').filter(Category.is_valid != 1)
])
profile = {"table.areas.hiddens": hiddens}
table = make_table(query, EntranceTable, profile)
if request.base_url.endswith(".xls/"):
csv = XlsExport('entrances', columns=export_columns)
return send_file(csv.export(query),
as_attachment=True,
attachment_filename='entrances.xls')
else:
return render_template('nodes/areas/entrances.html', table=table)
def roles():
query = Role.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(Role.name.ilike('%'+keyword+'%'))
table = make_table(query, RoleTable)
return render_template('users/roles/index.html', table=table, form=form)
def domains():
query = Domain.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(Domain.name.ilike('%'+keyword+'%'))
table = make_table(query, DomainTable)
return render_template('users/domains/index.html', table=table, form=form)
def roles():
query = Role.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(Role.name.ilike('%' + keyword + '%'))
table = make_table(query, RoleTable)
return render_template('users/roles/index.html', table=table, form=form)
def domains():
query = Domain.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(Domain.name.ilike('%' + keyword + '%'))
table = make_table(query, DomainTable)
return render_template('users/domains/index.html', table=table, form=form)
def nodes():
form = NodeSearchForm()
# 节点检索
query = Node.query.outerjoin(Area, Node.area_id == Area.id)
query_dict = dict([(key, request.args.get(key))
for key in form.data.keys()])
if query_dict.get("keyword"):
query = query.filter(
or_(Node.name.like('%' + query_dict["keyword"] + '%'),
Node.alias.like('%' + query_dict["keyword"] + '%'),
Node.addr.like('%' + query_dict["keyword"] + '%')))
if query_dict.get("area"):
# 区域树查询，是直接用的前台传过来的值作为where条件，如果包含or，需加括号
# 注意：值如（areas.cityid=1001 or areas.town=1006），areas 应与实际上生成的sql语句一致
netloc = request.args.get('area_netloc')
if 'or' in netloc: netloc = '(' + netloc + ')'
query = query.filter(netloc)
if query_dict.get("vendor_id"):
query = query.filter(Node.vendor_id == query_dict["vendor_id"]) # ==
if query_dict.get("model_id"):
query = query.filter(Node.model_id == query_dict["model_id"]) # ==
if query_dict.get("category_id"):
query = query.filter(Node.category_id == query_dict["category_id"])
if query_dict.get("status"):
query = query.filter(Node.status == query_dict["status"])
if not current_user.is_province_user:
query = query.filter(
current_user.domain.clause_permit) # 过滤不在当前用户管理域的节点
form.process(**query_dict)
table = make_table(query, NodeTable)
# 节点状态统计
status_statistcs = []
for status in NODE_STATUS_DICT.keys():
num = Node.query.filter(Node.status == status)
if not current_user.is_province_user:
num = num.outerjoin(Area, Node.area_id == Area.id).filter(
current_user.domain.clause_permit)
num = num.count()
status_statistcs.append({
"status": status,
"number": num,
"name": NODE_STATUS_DICT.get(status)
})
if request.base_url.endswith(".xls/"):
csv = XlsExport('nodes', columns=Node.export_columns())
return send_file(csv.export(
query,
format={'status': lambda value: NODE_STATUS_DICT.get(value)}),
as_attachment=True,
attachment_filename='nodes.xls')
else:
return render_template('nodes/index.html',
table=table,
form=form,
status_statistcs=status_statistcs)
def knowledges():
query = AlarmKnowledge.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(AlarmKnowledge.alarm_class.has(
AlarmClass.alias.ilike('%'+keyword+'%')))
table = make_table(query, AlarmKnowledgeTable)
return render_template('/alarms/knowledges/index.html',
table=table, filterForm=form)
def classes():
query = AlarmClass.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
query = query.filter(db.or_(AlarmClass.name.ilike('%'+keyword+'%'),
AlarmClass.alias.ilike('%'+keyword+'%')))
table = make_table(query, AlarmClassTable)
return render_template("/alarms/classes/index.html",
table=table, filterForm=form)
def dict_codes():
form = DictCodeFilterForm(formdata=request.args)
query = DictCode.query
if form.type.data:
query = query.filter_by(type_id=form.type.data.id)
if form.is_valid.data:
query = query.filter_by(is_valid=form.is_valid.data)
table = make_table(query, DictCodeTable)
return render_template('/system/dict-codes/index.html', table=table, form=form)
def histories():
filterForm = AlarmFilterForm(formdata=request.args)
history_query = History.query
if not current_user.is_province_user:
history_query = history_query.outerjoin(Node, Node.id == History.node_id)
history_query = history_query.outerjoin(Area, Node.area_id==Area.id).filter(current_user.domain.clause_permit)
query = alarm_filter(History, history_query, filterForm)
table = make_table(query, HistoryTable)
return render_template("/alarms/histories.html",
table=table, filterForm=filterForm)
def modules():
form = SearchForm(formdata=request.args)
cls, table_cls = Module, ModuleTable
query = cls.query
if form.keyword.data:
ikeyword = '%' + form.keyword.data + '%'
query = query.filter(db.or_(cls.name.ilike(ikeyword),
cls.alias.ilike(ikeyword)))
table = make_table(query, table_cls)
return render_template('admin/modules/index.html',
table = table, form=form)
def timeperiods():
query = TimePeriod.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
ikeyword = '%' + keyword + '%'
query = query.filter(db.or_(TimePeriod.name.ilike(ikeyword),
TimePeriod.alias.ilike(ikeyword)))
table = make_table(query, TimePeriodTable)
return render_template('/system/timeperiods/index.html',
filterForm = form, table=table)
def users():
query = User.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
kw = '%' + keyword + '%'
query = query.filter(
db.or_(User.name.ilike(kw), User.email.ilike(kw),
User.role.has(Role.name.ilike(kw))))
table = make_table(query, UserTable)
return render_template('users/index.html', table=table, form=form)
def users():
query = User.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
kw = '%' + keyword + '%'
query = query.filter(db.or_(User.name.ilike(kw),
User.email.ilike(kw),
User.role.has(Role.name.ilike(kw))))
table = make_table(query, UserTable)
return render_template('users/index.html', table=table, form=form)
def metrics():
query = Metric.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
ikeyword = '%' + keyword + '%'
query = query.filter(db.or_(Metric.name.ilike(ikeyword),
Metric.grp.ilike(ikeyword),
Metric.alias.ilike(ikeyword)))
table = make_table(query, MetricTable)
return render_template('system/metrics/index.html',
filterForm=form, table=table)
def permissions():
form = SearchForm(formdata=request.args)
cls, table_cls = Permission, PermissionTable
query = cls.query
if form.keyword.data:
ikeyword = '%' + form.keyword.data + '%'
query = query.filter(db.or_(cls.endpoint.ilike(ikeyword),
cls.name.ilike(ikeyword),
cls.operation.ilike(ikeyword)))
table = make_table(query, table_cls)
return render_template('admin/permissions/index.html',
table = table, form=form)
def seclogs():
query = SecurityLog.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
keyword = keyword.strip()
query = query.filter(db.or_(
SecurityLog.terminal_ip.ilike('%'+keyword+'%'),
SecurityLog.user.has(User.username.ilike('%'+keyword+'%'))))
table = make_table(query, SecurityLogTable)
return render_template('/system/seclogs.html',
filterForm=form, table=table)
def monitors():
form = SearchForm(formdata=request.args)
cls, table_cls = Monitor, MonitorTable
query = cls.query
if form.keyword.data:
ikeyword = '%' + form.keyword.data + '%'
query = query.filter(db.or_(cls.category.ilike(ikeyword),
cls.vendor.ilike(ikeyword),
cls.remark.ilike(ikeyword)))
table = make_table(query, table_cls)
return render_template('admin/monitors/index.html',
table = table, form=form)
def towns():
netloc = request.args.get('area_netloc') # 区域过滤条件
# 构造各个统计的子查询
sub_query_list = []
categories = Category.query.filter(Category.is_valid == 1).filter(Category.obj=='node')
for index,category in [(0,'total')]+[(category.id,category.name) for category in categories]:
sub_query = db.session.query(
Area.town,func.count(Node.id).label(category+"_count")
).select_from(Node).outerjoin(
Area, Node.area_id==Area.id
).filter(current_user.domain.clause_permit)
if index == 0: # 统计总节点数的子查询
sub_query = sub_query.group_by(Area.town).subquery()
else:
sub_query = sub_query.filter(Node.category_id==index).group_by(Area.town).subquery()
sub_query_list.append(sub_query)
for index,gran in enumerate(['branch','entrance']):
sub_query = db.session.query(
Area.town, func.count(Area.id).label(gran+"_count")
).filter(
Area.area_type==(index+3)
).group_by(Area.town).subquery()
sub_query_list.append(sub_query)
# 连接各个子查询
export_columns = ['name','alias','parent_id','longitude','latitude','remark']
query = 'db.session.query(Area.id,Area.name,Area.area_type,Area.alias,Area.longitude,Area.latitude,Area.city_name.label("parent_id"),'
for index,category in enumerate(['total']+[category.name for category in categories]):
if category == "host": continue
query += 'func.coalesce(sub_query_list[%(index)s].c.%(category)s_count,0).label("%(category)s_count"),' % {'index':index,'category': category}
#export_columns.append(category+"_count")
query += 'func.coalesce(sub_query_list[-2].c.branch_count,0).label("branch_count"),'
query += 'func.coalesce(sub_query_list[-1].c.entrance_count,0).label("entrance_count"),'
query += ')'
query = eval(query)
for index,sub_query in enumerate(sub_query_list):
query = query.outerjoin(sub_query, sub_query.c.town==Area.id)
query = query.filter(Area.area_type==2)
if not current_user.is_province_user: query = query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '('+netloc+')'
query = query.filter(netloc)
# 隐藏is_valid = 0 的分类
hiddens = u','.join([category.name+'_count' for category in Category.query.filter(Category.obj=='node').filter(Category.is_valid!=1)])
profile = {"table.areas.hiddens":hiddens}
table = make_table(query, TownTable,profile)
if request.base_url.endswith(".xls/"):
csv = XlsExport('towns',columns=export_columns)
return send_file(csv.export(query),as_attachment=True,attachment_filename='towns.xls')
else:
return render_template('nodes/areas/towns.html', table = table)
def thresholds():
query = Threshold.query
form = SearchForm(formdata=request.args)
keyword = form.keyword.data
if keyword and keyword != '':
ikeyword = '%' + keyword + '%'
query = query.filter(db.or_(Threshold.name.ilike(ikeyword),
Threshold.alias.ilike(ikeyword),
Threshold.category.has(Category.alias.ilike(ikeyword)),
Threshold.summary.ilike(ikeyword)))
table = make_table(query, ThresholdTable)
return render_template("system/thresholds/index.html",
filterForm = form, table=table)
def sysoids():
form = SearchForm(formdata=request.args)
cls, table_cls = SysOid, SysOidTable
query = cls.query
if form.keyword.data:
ikeyword = '%' + form.keyword.data + '%'
query = query.filter(db.or_(cls.sysoid.ilike(ikeyword),
cls.disco.ilike(ikeyword),
cls.mib.ilike(ikeyword),
cls.remark.ilike(ikeyword)))
table = make_table(query, table_cls)
return render_template('admin/sysoids/index.html',
table = table, form=form)
def areas():
form = AreaStatisticsForm()
netloc = request.args.get('area_netloc') # 区域过滤条件
query_gran = request.args.get('query_gran',1) # 统计粒度
group_type = AREA_TYPE_DICT.get(int(query_gran)) # 分组group_by类型，下面的子查询语句的字段将会根据它动态构造
# 构造各个分类统计的子查询
sub_query_list = []
categories = Category.query.filter(Category.is_valid == 1).filter(Category.obj=='node')
for index,category in [(0,'total')]+[(category.id,category.name) for category in categories]:
sub_query = db.session.query(
getattr(Area,group_type),func.count(Node.id).label(category+"_count")
).select_from(Node).outerjoin(
Area, Node.area_id==Area.id
)
if not current_user.is_province_user: sub_query = sub_query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '('+netloc+')'
sub_query = sub_query.filter(netloc)
if index == 0: # 统计总节点数的子查询
sub_query = sub_query.group_by(getattr(Area,group_type)).subquery()
else:
sub_query = sub_query.filter(Node.category_id==index).group_by(getattr(Area,group_type)).subquery()
sub_query_list.append(sub_query)
# 连接各个子查询
query = 'db.session.query(Area.id,Area.alias,Area.area_type,'
for index,category in enumerate(['total']+[category.name for category in categories]):
query += 'func.coalesce(sub_query_list[%(index)s].c.%(category)s_count,0).label("%(category)s_count"),' % {'index':index,'category': category}
query += ')'
query = eval(query)
for index,sub_query in enumerate(sub_query_list):
query = query.outerjoin(sub_query, getattr(sub_query.c,group_type)==getattr(Area, group_type))
query = query.filter(Area.area_type==query_gran)
if not current_user.is_province_user: query = query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '('+netloc+')'
query = query.filter(netloc)
# 隐藏is_valid = 0 的分类
hiddens = u','.join([category.name+'_count' for category in Category.query.filter(Category.obj=='node').filter(Category.is_valid!=1)])
profile = {"table.nodes.hiddens":hiddens}
table = make_table(query, AreaStatisticsTable,profile)
form.process(query_gran=query_gran)
if request.args.get("dashboard"):
return table.as_html()
else:
return render_template('nodes/statistics/area_statistics.html', table = table, form = form)
def index():
filterForm = AlarmFilterForm(formdata=request.args)
alarm_query = Alarm.query
if not current_user.is_province_user:
alarm_query = alarm_query.outerjoin(Node, Node.id == Alarm.node_id)
alarm_query = alarm_query.outerjoin(Area, Node.area_id==Area.id).filter(current_user.domain.clause_permit)
query = alarm_filter(Alarm, alarm_query, filterForm)
severity = request.args.get('severity')
if severity:
query = query.filter(Alarm.severity == AlarmSeverity.name2id(severity))
severities = query_severities()
total = sum([severity.count for severity in severities])
table = make_table(query, AlarmTable)
return render_template("/alarms/index.html",
table = table, filterForm = filterForm,
severities = severities, total = total)
def ponpower():
form = PerfFilterForm(formdata=request.args)
form.refresh_choices(request.args)
query = form.filter(PonPowerPerf)
table = make_table(query, PonPowerTable)
kwargs = {
'menuid' : 'ponpower',
'name' : 'ponpower',
'title' : u'PON口光功率',
'table' : table,
'filterForm' : form
}
if request.base_url.endswith(".csv/"):
csv = CsvExport('ponpower',columns=[str(column.accessor) for column in table.columns])
return send_file(csv.export(query),as_attachment=True,attachment_filename='ponpower.csv')
else:
return render_template('/perf/index.html', **kwargs)
def cpumem():
form = NodePerfFilterForm(formdata=request.args)
form.refresh_choices(request.args)
query = form.filter(CpuMemPerf)
table = make_table(query, CpuMemTable)
kwargs = {
'menuid' : 'cpumem',
'name' : 'cpumem',
'title' : u'CPU/内存',
'table' : table,
'filterForm' : form
}
if request.base_url.endswith(".csv/"):
csv = CsvExport('cpumem',columns=[str(column.accessor) for column in table.columns])
return send_file(csv.export(query),as_attachment=True,attachment_filename='cpumem.csv')
else:
return render_template('/perf/index.html', **kwargs)
def ping():
form = NodePerfFilterForm(formdata=request.args)
form.refresh_choices(request.args)
query = form.filter(PingPerf)
if form.keyword.data:
query = query.filter(PingPerf.node.has(Node.alias=='%'+ form.keyword.data +'%'))
table = make_table(query, PingTable)
kwargs = {
'menuid' : 'ping',
'name' : 'ping',
'title' : u'PING延时',
'table' : table,
'filterForm' : form
}
if request.base_url.endswith(".csv/"):
csv = CsvExport('ping',columns=[str(column.accessor) for column in table.columns])
return send_file(csv.export(query),as_attachment=True,attachment_filename='ping.csv')
else:
return render_template('/perf/index.html', **kwargs)
def miboids(mib=None):
form = SearchForm(formdata=request.args)
cls, table_cls = Miboid, MiboidTable
mib = cls.query.first().mib if not mib else mib
if not mib:
abort(404)
query = cls.query.filter_by(mib=mib)
if form.keyword.data:
ikeyword = '%' + form.keyword.data + '%'
query = query.filter(db.or_(cls.name.ilike(ikeyword),
cls.alias.ilike(ikeyword)))
table = make_table(query, table_cls)
kwargs = {
'menuid' : mib,
'form' : form,
'table' : table,
'mib' : mib,
}
return render_template("admin/miboids/index.html", **kwargs)
def nodes():
form = NodeSearchForm()
# 节点检索
query = Node.query.outerjoin(Area, Node.area_id==Area.id)
query_dict = dict([(key, request.args.get(key))for key in form.data.keys()])
if query_dict.get("keyword"):
query=query.filter(or_(
Node.name.like('%'+query_dict["keyword"]+'%'),
Node.alias.like('%'+query_dict["keyword"]+'%'),
Node.addr.like('%'+query_dict["keyword"]+'%')
))
if query_dict.get("area"):
# 区域树查询，是直接用的前台传过来的值作为where条件，如果包含or，需加括号
# 注意：值如（areas.cityid=1001 or areas.town=1006），areas 应与实际上生成的sql语句一致
netloc = request.args.get('area_netloc')
if 'or' in netloc: netloc = '('+netloc+')'
query = query.filter(netloc)
if query_dict.get("vendor_id"): query=query.filter(Node.vendor_id == query_dict["vendor_id"]) # ==
if query_dict.get("model_id"): query=query.filter(Node.model_id == query_dict["model_id"]) # ==
if query_dict.get("category_id"): query=query.filter(Node.category_id == query_dict["category_id"])
if query_dict.get("status"): query=query.filter(Node.status == query_dict["status"])
if not current_user.is_province_user: query = query.filter(current_user.domain.clause_permit) # 过滤不在当前用户管理域的节点
form.process(**query_dict)
table = make_table(query, NodeTable)
# 节点状态统计
status_statistcs = []
for status in NODE_STATUS_DICT.keys():
num = Node.query.filter(Node.status == status)
if not current_user.is_province_user: num = num.outerjoin(Area, Node.area_id==Area.id).filter(current_user.domain.clause_permit)
num = num.count()
status_statistcs.append({"status": status, "number": num, "name": NODE_STATUS_DICT.get(status)})
if request.base_url.endswith(".xls/"):
csv = XlsExport('nodes',columns=Node.export_columns())
return send_file(csv.export(query,format={'status': lambda value: NODE_STATUS_DICT.get(value)}),as_attachment=True,attachment_filename='nodes.xls')
else:
return render_template('nodes/index.html', table = table, form=form, status_statistcs=status_statistcs)
def intfusage(name):
CONFIG = {
'all' : ('intfusage', u'端口流量', NodePerfFilterForm ,IntfUsageTable),
'onu' : ('intfusage_onu', u'用户口占用率', PerfFilterForm, IntfUsageTable),
}
menuid, title, form_cls, table_cls = CONFIG[name]
form = form_cls(formdata=request.args)
form.refresh_choices(request.args)
query = form.filter(IntfUsagePerf)
table = make_table(query, table_cls)
kwargs = {
'menuid' : menuid,
'name' : name,
'title' : title,
'table' : table,
'filterForm' : form
}
if ".csv/" in request.base_url:
csv = CsvExport('intfusage_'+name,columns=[str(column.accessor) for column in table.columns])
return send_file(csv.export(query),as_attachment=True,attachment_filename='intfusage_'+name+'.csv')
else:
return render_template('/perf/index.html', **kwargs)
def subsystems():
table = make_table(SubSystem.query, SubSystemTable)
return render_template('/system/subsystems.html', table=table)
def settings():
table = make_table(Setting.query, SettingTable)
return render_template('/system/settings/index.html', table=table)
def cities():
# 构造各个统计的子查询
sub_query_list = []
categories = Category.query.filter(Category.is_valid == 1).filter(
Category.obj == 'node')
for index, category in [(0, 'total')] + [(category.id, category.name)
for category in categories]:
sub_query = db.session.query(
Area.cityid,
func.count(
Node.id).label(category +
"_count")).select_from(Node).outerjoin(
Area, Node.area_id == Area.id).filter(
current_user.domain.clause_permit)
if index == 0: # 统计总节点数的子查询
sub_query = sub_query.group_by(Area.cityid).subquery()
else:
sub_query = sub_query.filter(Node.category_id == index).group_by(
Area.cityid).subquery()
sub_query_list.append(sub_query)
for index, gran in enumerate(['town', 'branch', 'entrance']):
sub_query = db.session.query(
Area.cityid,
func.count(Area.id).label(gran + "_count")).filter(
Area.area_type == (index + 2)).group_by(
Area.cityid).subquery()
sub_query_list.append(sub_query)
# 连接各个子查询
export_columns = ['name', 'alias', 'longitude', 'latitude', 'remark']
query = 'db.session.query(Area.id,Area.name,Area.area_type,Area.alias,Area.longitude,Area.latitude,Area.parent_id,'
for index, category in enumerate(
['total'] + [category.name for category in categories]):
if category == "host": continue
query += 'func.coalesce(sub_query_list[%(index)s].c.%(category)s_count,0).label("%(category)s_count"),' % {
'index': index,
'category': category
}
#export_columns.append(category+"_count")
query += 'func.coalesce(sub_query_list[-3].c.town_count,0).label("town_count"),'
query += 'func.coalesce(sub_query_list[-2].c.branch_count,0).label("branch_count"),'
query += 'func.coalesce(sub_query_list[-1].c.entrance_count,0).label("entrance_count"),'
query += ')'
query = eval(query)
for index, sub_query in enumerate(sub_query_list):
query = query.outerjoin(sub_query, sub_query.c.cityid == Area.id)
query = query.filter(Area.area_type == 1)
if not current_user.is_province_user:
query = query.filter(current_user.domain.clause_permit)
# 隐藏is_valid = 0 的分类
hiddens = u','.join([
category.name + '_count' for category in Category.query.filter(
Category.obj == 'node').filter(Category.is_valid != 1)
])
profile = {"table.areas.hiddens": hiddens}
table = make_table(query, CityTable, profile)
if request.base_url.endswith(".xls/"):
csv = XlsExport('cities', columns=export_columns)
return send_file(csv.export(query,
format={
'parent_id':
lambda value: Area.query.filter(
Area.area_type == 0).first().name
}),
as_attachment=True,
attachment_filename='cities')
else:
return render_template('nodes/areas/cities.html', table=table)
def areas():
form = AreaStatisticsForm()
netloc = request.args.get('area_netloc') # 区域过滤条件
query_gran = request.args.get('query_gran', 1) # 统计粒度
group_type = AREA_TYPE_DICT.get(
int(query_gran)) # 分组group_by类型，下面的子查询语句的字段将会根据它动态构造
# 构造各个分类统计的子查询
sub_query_list = []
categories = Category.query.filter(Category.is_valid == 1).filter(
Category.obj == 'node')
for index, category in [(0, 'total')] + [(category.id, category.name)
for category in categories]:
sub_query = db.session.query(
getattr(Area, group_type),
func.count(Node.id).label(category +
"_count")).select_from(Node).outerjoin(
Area, Node.area_id == Area.id)
if not current_user.is_province_user:
sub_query = sub_query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '(' + netloc + ')'
sub_query = sub_query.filter(netloc)
if index == 0: # 统计总节点数的子查询
sub_query = sub_query.group_by(getattr(Area,
group_type)).subquery()
else:
sub_query = sub_query.filter(Node.category_id == index).group_by(
getattr(Area, group_type)).subquery()
sub_query_list.append(sub_query)
# 连接各个子查询
query = 'db.session.query(Area.id,Area.alias,Area.area_type,'
for index, category in enumerate(
['total'] + [category.name for category in categories]):
query += 'func.coalesce(sub_query_list[%(index)s].c.%(category)s_count,0).label("%(category)s_count"),' % {
'index': index,
'category': category
}
query += ')'
query = eval(query)
for index, sub_query in enumerate(sub_query_list):
query = query.outerjoin(
sub_query,
getattr(sub_query.c, group_type) == getattr(Area, group_type))
query = query.filter(Area.area_type == query_gran)
if not current_user.is_province_user:
query = query.filter(current_user.domain.clause_permit)
if netloc:
if 'or' in netloc: netloc = '(' + netloc + ')'
query = query.filter(netloc)
# 隐藏is_valid = 0 的分类
hiddens = u','.join([
category.name + '_count' for category in Category.query.filter(
Category.obj == 'node').filter(Category.is_valid != 1)
])
profile = {"table.nodes.hiddens": hiddens}
table = make_table(query, AreaStatisticsTable, profile)
form.process(query_gran=query_gran)
if request.args.get("dashboard"):
return table.as_html()
else:
return render_template('nodes/statistics/area_statistics.html',
table=table,
form=form)
def settings():
table = make_table(Setting.query.filter(Setting.mod == 'alarms'), SettingTable)
return render_template('/alarms/settings/index.html', table=table)
