def data():
"""Return server side data."""
# defining columns
columns = [
ColumnDT(User.id),
ColumnDT(User.name, sort_method=func.public.naturalsort(User.name)),
ColumnDT(Address.description),
ColumnDT(User.created_at)
]
# defining the initial query depending on your purpose
query = db.session.query().select_from(User).join(Address).filter(
Address.id > 14)
# GET parameters
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def data_yadcf(request):
"""Return server side data."""
# defining columns
columns = [
ColumnDT(User.id, search_method='yadcf_range_number'),
ColumnDT(User.name, search_method='yadcf_multi_select'),
ColumnDT(Address.description, search_method='yadcf_autocomplete'),
ColumnDT(User.birthday, search_method='yadcf_range_date'),
ColumnDT(User.age, search_method='yadcf_range_number_slider')
]
# defining the initial query depending on your purpose
query = DBSession.query().\
select_from(User).\
join(Address).\
filter(Address.id > 4)
# instantiating a DataTable for the query and table needed
rowTable = DataTables(request.GET, query, columns)
# returns what is needed by DataTable
return rowTable.output_result()
def test_set_mdata(self):
"""Test if result's data have mData set."""
self.populate(5)
columns = []
columns.append(ColumnDT('id', mData='ID'))
columns.append(ColumnDT('name', mData='Username'))
columns.append(ColumnDT('address.description', mData='Address'))
columns.append(ColumnDT('created_at', mData='Created at'))
req = self.create_dt_params()
rowTable = DataTables(req, User,
self.session.query(User).join(Address), columns)
res = rowTable.output_result()
assert len(res['data']) == 5
assert 'ID' in res['data'][0]
assert 'Username' in res['data'][0]
assert 'Address' in res['data'][0]
assert 'Created at' in res['data'][0]
def get_supported_vendor_cm_file_format_for_datatable():
"""
Get support file format for each vendor and technology
"""
metadata = MetaData()
table = Table('vw_vendor_cm_file_formats',
metadata,
autoload=True,
autoload_with=db.engine)
columns = []
for c in table.columns:
columns.append(ColumnDT(c, column_name=c.name, mData=c.name))
query = db.session.query(table)
# GET request parameters
params = request.args.to_dict()
row_table = DataTables(params, query, columns)
return jsonify(row_table.output_result())
def data():
"""Return server side data."""
# defining columns
columns = [
ColumnDT(ModuleBlob.id),
ColumnDT(ModuleBlob.transcription_text_id),
ColumnDT(ModuleBlob.created_at),
ColumnDT(TranscriptionText.text)
]
# defining the initial query depending on your purpose
query = db.session.query().select_from(ModuleBlob).join(TranscriptionText)
# .filter(Address.id > 14)
# GET parameters
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def profile_webauthn_list_json_route():
"""get registered credentials list for current user"""
columns = [
ColumnDT(WebauthnCredential.id,
mData='id',
search_method='none',
global_search=False),
ColumnDT(WebauthnCredential.registered, mData='registered'),
ColumnDT(WebauthnCredential.name, mData='name'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(WebauthnCredential) \
.filter(WebauthnCredential.user_id == current_user.id) \
.order_by(WebauthnCredential.registered.asc())
creds = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(creds, cls=SnerJSONEncoder),
mimetype='application/json')
def data():
"""Return server side data."""
# defining columns
columns = [
ColumnDT(contacts.business_name),
ColumnDT(contacts.city),
ColumnDT(contacts.contact_one),
ColumnDT(contacts.contact_two),
ColumnDT(contacts.address),
]
# defining the initial query depending on your purpose
query = db.session.query().select_from(contacts)
# GET parameters
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def test_null_field_filtering(self):
"""Test if a None field is not filtered."""
self.populate(5)
user6, addr6 = self.create_user('Empty', None)
self.session.add(user6)
self.session.commit()
columns = self.create_columns(['id', 'name', 'address.description',
'created_at'])
req = self.create_dt_params()
rowTable = DataTables(
req, User, self.session.query(User).join(Address), columns)
res = rowTable.output_result()
assert len(res['data']) == 6
assert res['recordsTotal'] == '6'
assert res['recordsFiltered'] == '6'
def urusan_act(request):
ses = request.session
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act'] == 'grid':
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
query = DBSession.query(Urusan)
rowTable = DataTables(req, Urusan, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'headofnama':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Urusan.id, Urusan.nama).filter(
Urusan.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'headofkode':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Urusan.id, Urusan.kode).filter(
Urusan.kode.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
r.append(d)
return r
def get_relations_dt_data():
"""Get relations in jQuery datatable data format"""
metadata = MetaData()
relations_table = Table('vw_relations',
metadata,
autoload=True,
autoload_with=db.engine,
schema='live_network')
columns = []
for c in relations_table.columns:
columns.append(ColumnDT(c, column_name=c.name, mData=c.name))
query = db.session.query(relations_table)
# GET request parameters
params = request.args.to_dict()
row_table = DataTables(params, query, columns)
return jsonify(row_table.output_result())
def service_grouped_json_route():
"""view grouped services, data endpoint"""
info_column = service_info_column(request.args.get('crop'))
columns = [
ColumnDT(info_column, mData='info'),
ColumnDT(func.count(Service.id),
mData='cnt_services',
global_search=False),
]
# join allows filter over host attrs
query = db.session.query().select_from(Service).join(Host).group_by(
info_column)
if 'filter' in request.values:
query = apply_filters(query,
FILTER_PARSER.parse(
request.values.get('filter')),
do_auto_join=False)
services = DataTables(request.values.to_dict(), query,
columns).output_result()
return jsonify(services)
def vuln_grouped_json_route():
"""view grouped vulns, data endpoint"""
columns = [
ColumnDT(Vuln.name, mData='name'),
ColumnDT(Vuln.severity, mData='severity'),
ColumnDT(Vuln.tags, mData='tags'),
ColumnDT(func.count(Vuln.id), mData='cnt_vulns', global_search=False),
]
# join allows filter over host attrs
query = db.session.query().select_from(Vuln).join(Host).group_by(
Vuln.name, Vuln.severity, Vuln.tags)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
vulns = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(vulns, cls=SnerJSONEncoder),
mimetype='application/json')
def test_outerjoin(self):
"""Test if outerjoin works."""
self.populate(5)
f = faker.Faker()
a = Address(description=f.address())
self.session.add(a)
columns = self.create_columns(['id', 'description', 'user.name'])
req = self.create_dt_params()
rowTable = DataTables(req, Address,
self.session.query(Address).outerjoin(User),
columns)
res = rowTable.output_result()
assert len(res['aaData']) == 6
assert res['iTotalRecords'] == '6'
assert res['iTotalDisplayRecords'] == '6'
assert res['aaData'][5]['1'] == a.description
assert res['aaData'][5]['2'] == 'None'
def list_companies_data():
"""Return server side data."""
# defining columns
columns = [
ColumnDT(models.Company.id),
ColumnDT(models.Company.symbol),
ColumnDT(models.Company.name + "|" + models.Company.name_ar),
ColumnDT(models.Company.phone),
ColumnDT(models.Company.website),
ColumnDT(
"<a class=\"fa fa-edit\" href=\"{{url_for('website.edit_com', tn="
+ models.Company.symbol + ")}}\"></a>")
]
# defining the initial query depending on your purpose
query = db.session.query().select_from(models.Company).filter(1 == 1)
# GET parameters
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def data_advanced(request):
"""Return server side data."""
# defining columns
columns = [
ColumnDT(User.id, search_method='numeric'),
ColumnDT(User.name),
ColumnDT(Address.description),
ColumnDT(User.birthday, search_method='date'),
ColumnDT(User.age, search_method='numeric')
]
# defining the initial query depending on your purpose
query = DBSession.query().\
select_from(User).\
join(Address).\
filter(Address.id > 4)
# instantiating a DataTable for the query and table needed
rowTable = DataTables(request.GET, query, columns)
# returns what is needed by DataTable
return rowTable.output_result()
def test_global_search_filtering_with_regex(self):
"""Test if result's are filtered from global search field."""
self.populate(5)
user6, addr6 = self.create_user('Run To', 'The Hills')
user7, addr7 = self.create_user('Fear Of', 'The Dark')
user8, addr8 = self.create_user('More fear of', 'The Daaaaark')
self.session.add(user6)
self.session.add(user7)
self.session.add(user8)
self.session.commit()
columns = [
ColumnDT(User.id, ),
ColumnDT(User.name),
ColumnDT(Address.description),
ColumnDT(User.created_at),
]
req = self.create_dt_params(search='Da*rk')
req['search[regex]'] = 'true'
rowTable = DataTables(
req,
self.session.query().select_from(User).join(Address),
columns,
allow_regex_searches=True)
res = rowTable.output_result()
if 'error' in res:
# unfortunately sqlite doesn't support regexp out of the box'
assert 'no such function: REGEXP' in res['error']
else:
assert len(res['data']) == 1
assert res['recordsTotal'] == '8'
assert res['recordsFiltered'] == '8'
assert res['data'][0]['2'] == 'The Dark'
assert res['data'][1]['2'] == 'The Daaaaark'
def vuln_list_json_route():
"""list vulns, data endpoint"""
columns = [
ColumnDT(literal_column('1'),
mData='_select',
search_method='none',
global_search=False),
ColumnDT(Vuln.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
ColumnDT(Vuln.name, mData='name'),
ColumnDT(Vuln.xtype, mData='xtype'),
ColumnDT(Vuln.severity, mData='severity'),
ColumnDT(Vuln.refs, mData='refs'),
ColumnDT(Vuln.tags, mData='tags'),
ColumnDT(Vuln.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Vuln).outerjoin(
Host, Vuln.host_id == Host.id).outerjoin(Service,
Vuln.service_id == Service.id)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
vulns = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(vulns, cls=SnerJSONEncoder),
mimetype='application/json')
def data():
from db_models import UnitModel
"""Return server side data."""
# defining columns
columns = [
ColumnDT(UnitModel.id),
ColumnDT(UnitModel.name),
ColumnDT(UnitModel.value_per_kg),
ColumnDT(UnitModel.is_deleted),
ColumnDT(UnitModel.created_at),
]
# defining the initial query depending on your purpose
query = db.session.query().select_from(UnitModel).order_by(UnitModel.id)
# GET parameters
params = request.args.to_dict()
# print(params)
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def team_data_table1():
columns = [
ColumnDT(Team.team_name),
ColumnDT(Team.team_city),
ColumnDT(Team.team_year)
]
# print(request.args) # GET方法从args获取参数
# print(request.form) # POST方法从form获取参数
# request.values # 获取所有参数
if request.method == 'POST':
team_name = request.form.get('team_name')
# season = request.form.get('season')
# team_name = request.args.get('team_name')
query = db.session.query().select_from(Team).filter(Team.team_name == team_name)
# params = request.args.to_dict() # GET方法
params = request.form.to_dict() # POST方法
rowTable = DataTables(params, query, columns)
return jsonify(rowTable.output_result())
def service_list_json_route():
"""list services, data endpoint"""
columns = [
ColumnDT(Service.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(Service.proto, mData='proto'),
ColumnDT(Service.port, mData='port'),
ColumnDT(Service.name, mData='name'),
ColumnDT(Service.state, mData='state'),
ColumnDT(Service.info, mData='info'),
ColumnDT(Service.tags, mData='tags'),
ColumnDT(Service.comment, mData='comment'),
ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False)
]
query = db.session.query().select_from(Service).outerjoin(Host)
if 'filter' in request.values:
query = apply_filters(query, filter_parser.parse(request.values.get('filter')), do_auto_join=False)
services = DataTables(request.values.to_dict(), query, columns).output_result()
return jsonify(services)
def test_fields_global_search_filtering_with_regex(self):
"""Test if result's are filtered from global search field."""
columns = [ColumnDT(User.id, ), ColumnDT(User.name)]
query = self.session.query().select_from(User)
params = self.create_dt_params(columns, search='Fe*ar')
params['search[regex]'] = 'true'
rowTable = DataTables(params,
query,
columns,
allow_regex_searches=True)
res = rowTable.output_result()
if 'error' in res:
# unfortunately sqlite doesn't support regexp out of the box'
assert 'no such function: REGEXP' in res['error']
else:
assert len(res['data']) == 1
assert res['recordsTotal'] == '1'
assert res['recordsFiltered'] == '1'
assert res['data'][0]['1'] == 'Feeeeear Of'
def get_dt_data():
"""Get baseline values in jQuery datatables format"""
# Define columns
columns = [
ColumnDT(NetworkBaselineView.vendor,
column_name="vendor",
mData="vendor"),
ColumnDT(NetworkBaselineView.technology,
column_name="technology",
mData="technology"),
ColumnDT(NetworkBaselineView.mo, column_name="mo", mData="mo"),
ColumnDT(NetworkBaselineView.parameter,
column_name="parameter",
mData="parameter"),
ColumnDT(NetworkBaselineView.value, column_name="value",
mData="value"),
ColumnDT(NetworkBaselineView.date_added,
column_name="date_added",
mData="date_added"),
ColumnDT(NetworkBaselineView.date_modified,
column_name="date_modified",
mData="date_modified")
]
query = db.session.query(
NetworkBaselineView.vendor, NetworkBaselineView.technology,
NetworkBaselineView.mo, NetworkBaselineView.parameter,
NetworkBaselineView.value, NetworkBaselineView.date_added,
NetworkBaselineView.date_modified)
# GET request parameters
params = request.args.to_dict()
row_table = DataTables(params, query, columns)
return jsonify(row_table.output_result())
def userTable():
"""Return server side data."""
print('stoff')
print('creating user table')
# defining columns
columns = [
ColumnDT(User.id),
ColumnDT(User.email),
ColumnDT(User.first_name),
ColumnDT(User.last_name),
ColumnDT(User.lastLoginDate)
]
print('params')
params = request.args.to_dict()
# defining the initial query depending on your purpose
query = db.session.query(User.id, User.email, User.first_name,
User.last_name,
User.lastLoginDate).filter(User.is_admin == False)
print('query: ', query)
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
print('params: ', params)
print('cols: ', columns)
print('rowTable: ', rowTable.output_result())
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def get_rule_data(audit_id):
"""Get fields in audit data table"""
rule = AuditRule.query.filter_by(pk=audit_id).first()
metadata = MetaData()
rule_table = Table(rule.table_name,
metadata,
autoload=True,
autoload_with=db.engine,
schema='network_audit')
query = db.session.query(rule_table)
columns = []
for c in rule_table.columns:
columns.append(ColumnDT(c, column_name=c.name, mData=c.name))
# GET request parameters
params = request.args.to_dict()
row_table = DataTables(params, query, columns)
return jsonify(row_table.output_result())
def get_relations():
"""Get a list of all network relations"""
params = request.args.to_dict()
params['draw'] = 1
params['length'] = request.args.get("length", 10)
params['start'] = request.args.get("start", 0)
metadata = MetaData()
relations_table = Table('vw_relations',
metadata,
autoload=True,
autoload_with=db.engine,
schema='live_network')
columns = []
column_index = 0
for c in relations_table.columns:
search_method = 'string_contains'
if request.args.get(
"columns[{}][search][regex]".format(column_index)) == 'true':
search_method = 'regex'
columns.append(
ColumnDT(c,
column_name=c.name,
mData=c.name,
search_method=search_method))
column_index += 1
query = db.session.query(relations_table)
row_table = DataTables(params, query, columns)
app.logger.info(params)
return jsonify(row_table.output_result())
def excl_list_json_route():
"""list target exclusions, data endpoint"""
columns = [
ColumnDT(Excl.id, mData='id'),
ColumnDT(Excl.family, mData='family'),
ColumnDT(Excl.value, mData='value'),
ColumnDT(Excl.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Excl)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
excls = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(excls, cls=SnerJSONEncoder),
mimetype='application/json')
def data():
"""Return server side data"""
# defining columns
columns = [
ColumnDT(User.id),
ColumnDT(User.first_name),
ColumnDT(User.last_name),
ColumnDT(User.email),
ColumnDT(User.username),
ColumnDT(User.admin),
ColumnDT(User.disabled)
]
# defining initial query
query = database.session.query().select_from(User)
# GET parameters
params = request.args.to_dict()
# instantiating a DataTable for the query and table needed
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def test_set_mdata(self):
"""Test if result's data have mData set."""
self.populate(5)
columns = [
ColumnDT(User.id, mData='ID'),
ColumnDT(User.name, mData='Username'),
ColumnDT(Address.description, mData='Address'),
ColumnDT(User.created_at, mData='Created at'),
]
req = self.create_dt_params()
rowTable = DataTables(
req,
self.session.query().select_from(User).join(Address), columns)
res = rowTable.output_result()
assert len(res['data']) == 5
assert 'ID' in res['data'][0]
assert 'Username' in res['data'][0]
assert 'Address' in res['data'][0]
assert 'Created at' in res['data'][0]
def api_User_Info():
columns = [
ColumnDT(Users.id),
ColumnDT(Users.name),
ColumnDT(Users.role_id)
]
query = db.session.query().select_from(Users).filter(Users.role_id == 1)
query2 = db.session.query(Users).filter_by(role_id=1)
# query = db.session.query().select_from(Team).filter(Team.team_name == 'OKC')
# query = Users.query.filter_by(Users.role_id == 1).order_by(Users.id)
# 需要draw, length, start三个参数，来正常运行DataTable包
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
# print(query.scalar())
# print(query2)
# print(query2.all())
# print(query2.all()[3].name)
# print(rowTable.output_result()['data'])
# print((Users.query.filter(Users.name == 'tuige')).all())
return jsonify(rowTable.output_result())
def get_supported_vendor_technologies_for_datatable():
"""
Get supported technologies and vendors in the network
"""
metadata = MetaData()
supported_vendor_tech = Table('vw_supported_vendor_tech',
metadata,
autoload=True,
autoload_with=db.engine,
schema='public')
columns = []
for c in supported_vendor_tech.columns:
columns.append(ColumnDT(c, column_name=c.name, mData=c.name))
query = db.session.query(supported_vendor_tech)
# GET request parameters
params = request.args.to_dict()
row_table = DataTables(params, query, columns)
return jsonify(row_table.output_result())
