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)