def update_groups(
query
, session
):
if query.notasi_query:
notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records')
for group_category in set(d["group_category"] for d in notasi_query):
category = session.query(GroupCategory).filter_by(name=group_category).first()
if not category:
category = GroupCategory()
session.add(category)
category.name = group_category
for group in set([d["group_name"] for d in notasi_query if d['group_category'] == group_category]):
stored_group = session.query(Group) \
.join(GroupCategory) \
.filter(Group.name==group) \
.filter(GroupCategory.name==group_category) \
.first()
if not stored_group:
stored_group = Group()
session.add(stored_group)
stored_group.name = group
stored_group.group_category_id = category.id
return []
def ldap_select(query):
location = query.location
dc = 'dc=' + ',dc='.join(split_strip(location.database, "."))
if location.subtype.name == 'Active Directory':
auth = NTLM
else:
auth = SIMPLE
server = Server(location.address, get_info=ALL)
conn = Connection(server,
'cn=' + location.username + ',' + dc,
location.password,
auto_bind=True,
authentication=auth)
formatted_queries = []
if query.notasi_query:
notasi_query = pandas.read_sql(
query.notasi_query, db_session.notasi_engine()).to_dict('records')
for row in notasi_query:
key_list = list(row.keys())
formatted_query = query.body
for key in key_list:
formatted_query = formatted_query.replace(
'{' + str(key) + '}', str(row[key]))
formatted_queries.append(formatted_query)
if not formatted_queries:
formatted_queries.append(query.body)
data = []
for formatted_query in formatted_queries:
conn.search(dc, formatted_query, attributes=[ALL_ATTRIBUTES])
for entry in conn.entries:
dict1 = json.loads(entry.entry_to_json())["attributes"]
dict1['dn'] = json.loads(entry.entry_to_json())["dn"]
data.append(dict1)
data = flatten_json(data)
return (data)
def run(category: str, endpoint_location: str):
session = db_session.create_session()
endpoint = session.query(Endpoint) \
.filter_by(endpoint_location=endpoint_location, category=category)\
.first()
session.close()
key = flask.request.headers.get('key')
if not check_password_hash(endpoint.key, key):
return "Unauthorised", 401
data = flask.request.data.decode('utf-8')
args = flask.request.args.to_dict()
data_dict = dict()
if data:
data_dict = json.loads(data)
else:
for key in args:
data_dict[key] = args[key]
notasi_query_string = "select * from (" \
+ endpoint.notasi_query \
+") as tab1"
where_list = []
for key in data_dict:
where_list.append("lower(" + key + ") = lower(:" + key +")")
if where_list:
notasi_query_string = notasi_query_string + " where " + " AND ".join(where_list)
t = text(notasi_query_string)
try:
notasi_query = pandas.read_sql(t, db_session.notasi_engine(), params=data_dict).to_dict('records')
return (json.dumps(notasi_query, default=default))
except Exception as error:
return str(error)
def sql_select(query):
location = query.location
connection_string = location.subtype.dialect + \
"://" + location.username + \
(":" + location.password if location.password else "") + \
"@" + location.address + \
":" + location.port + \
("/" + location.database if location.database else "") + \
("?driver=ODBC+DRIVER+17+for+SQL+Server" if location.subtype.dialect == 'mssql+pyodbc' else "")
print(connection_string)
location_engine = sa.create_engine(connection_string)
location_connection = location_engine.connect()
formatted_queries = []
if query.notasi_query:
notasi_query = pandas.read_sql(
query.notasi_query, db_session.notasi_engine()).to_dict('records')
for row in notasi_query:
key_list = list(row.keys())
formatted_query = query.body
for key in key_list:
formatted_query = formatted_query.replace(
'{' + str(key) + '}', str(row[key]))
formatted_queries.append(formatted_query)
if not formatted_queries:
formatted_queries.append(query.body)
data = []
for formatted_query in formatted_queries:
df = pandas.read_sql_query(formatted_query, location_engine)
data.append(df)
data = json.loads(
json.dumps(pandas.concat(data).to_dict('records'), default=default))
return flatten_json(data)
def update_users(
query
, session
):
if query.notasi_query:
notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records')
for row in notasi_query:
user = session.query(User).filter_by(username=row["username"]).first()
if user:
if user.name !=row["name"]:
user.name =row["name"]
else:
user = User()
session.add(user)
user.username = row["username"]
user.name = row["name"]
response = [{"job":"users added"}]
return flatten_json(response)
def index():
schedules = pandas.read_sql("select * from apscheduler_jobs",
db_session.notasi_engine()).to_dict('records')
print(schedules)
return flask.render_template('schedule/index.html', schedules=schedules)
def run_chart(id, session):
chart = session.query(Chart) \
.filter_by(id=id)\
.first()
chart_type = chart.chart_type.chart_type
user_groups = session.query(UserGroup) \
.filter_by(user_id=current_user.id)\
.all()
####### COLUMN ACCESS ############
groups = []
for user_group in user_groups:
groups.append(user_group.group.group_category.name + '.' +
user_group.group.name)
sql_cols = []
if chart.access_columns.strip():
cols = split_strip(chart.access_columns, ",")
for col in cols:
q = col + " in ('" + "','".join(groups) + "')"
sql_cols.append(q)
sql_cols_concat = " OR ".join(sql_cols)
query = chart.notasi_query
if sql_cols_concat:
query = "select * from (" + query + ") as tab1 where " + sql_cols_concat
####### COLUMN GROUPS ############
if chart.access_groups.strip():
access_groups = split_strip(chart.access_groups, ",")
match = set(groups) & set(access_groups)
if not match and not sql_cols:
abort(401)
###########GET DATA ##############
notasi_query = pandas.read_sql(
query, db_session.notasi_engine()).to_dict('records')
####### COLORS ############
###########SPLIT CHART ON COL ##############
charts = []
result_list = []
if chart.page_column:
result = collections.defaultdict(list)
for d in notasi_query:
result[d[chart.page_column]].append(d)
result_list = list(result.values())
else:
result_list.append(notasi_query)
for l in result_list:
value_sets = []
for col in split_strip(chart.value_columns, ","):
value_sets.append([d[col] for d in l])
color_palettes = []
random_palette = generate_color_palette(len(l))
random.shuffle(random_palette)
if not chart.color_columns:
for value_set in value_sets:
color_palettes.append(random_palette)
else:
for col in split_strip(chart.color_columns, ","):
if col.lower() == 'random':
color_palettes.append(random_palette)
else:
raw_palette = [d[col] for d in l]
rgb_palette = []
for color in raw_palette:
if color.startswith("#"):
rgb_palette.append(",".join([
str(int(color.strip("#")[i:i + 2], 16))
for i in (0, 2, 4)
]))
else:
rgb_palette.append(color)
color_palettes.append(rgb_palette)
charts.append({"page": l[0].get(chart.page_column), \
"chart_type": chart_type, \
"color_palettes": color_palettes, \
"value_sets": value_sets, \
"x_categories": [d[chart.x_categories] for d in l], \
"dataset_legends": split_strip(chart.dataset_legends, ","), \
"chart": chart, \
"options": chart.options, \
"id": chart.id, \
"name": chart.name})
return (charts)
def http_select(query):
url_string = query.location.address + query.endpoint
headers_string = query.head if query.head else ''
data_string = query.body if query.body else ''
request_vars = []
responses = []
if query.notasi_query:
notasi_query = pandas.read_sql(
query.notasi_query, db_session.notasi_engine()).to_dict('records')
for row in notasi_query:
key_list = list(row.keys())
url = url_string
headers = headers_string
data = data_string
for key in key_list:
if url:
url = url.replace('{' + str(key) + '}', str(row[key]))
if headers:
headers = headers.replace('{' + str(key) + '}',
str(row[key]))
if data:
data = data.replace('{' + str(key) + '}', str(row[key]))
if headers:
headers = json.loads(headers)
if data:
data = json.loads(data)
request_vars.append({'url': url, 'headers': headers, 'data': data})
else:
if headers_string:
headers = json.loads(headers_string)
else:
header = []
if data_string:
data = json.loads(data_string)
else:
data = []
request_vars.append({
'url': url_string,
'headers': headers,
'data': data
})
if query.request_method.name == "GET":
for request_var in request_vars:
response = requests.get(url=request_var['url'],
headers=request_var['headers'],
data=request_var['data']).json()
if type(response) == dict:
responses.append(
flatten_json(pandas.DataFrame(response, index=[0])))
else:
responses.append(flatten_json(pandas.DataFrame(response)))
elif query.request_method.name == "POST":
for request_var in request_vars:
response = requests.post(url=request_var['url'],
headers=request_var['headers'],
data=request_var['data']).json()
responses.append(pandas.DataFrame(response, index=[0]))
result = [item for sublist in responses for item in sublist]
return result
def update_user_groups(
query
, session
):
if query.notasi_query:
notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records')
group_categories = []
for row in notasi_query:
if row["group_category"] and row["group_category"] not in group_categories:
group_categories.append(row["group_category"])
existing_row = session.query(UserGroup) \
.join(Group) \
.join(GroupCategory) \
.join(User) \
.filter(Group.name==row["group_name"]) \
.filter(GroupCategory.name==row["group_category"]) \
.filter(User.username==row["username"]) \
.first()
existing_group = session.query(Group).filter(GroupCategory.name==row["group_category"]).filter(Group.name==row["group_name"]).first()
existing_user = session.query(User).filter(User.username==row["username"]).first()
if not existing_row and existing_group and existing_user:
new_row = UserGroup()
session.add(new_row)
new_row.group_id = existing_group.id
new_row.user_id = existing_user.id
session.commit()
for group_category in group_categories:
existing_rows = session.query(UserGroup) \
.join(Group) \
.join(GroupCategory) \
.join(User) \
.filter(GroupCategory.name==row["group_category"]) \
.all()
for existing_row in existing_rows:
if {"username": existing_row.user.username
, "group_category": existing_row.group.group_category.name
, "group_name": existing_row.group.name} not in notasi_query:
session.delete(existing_row)
session.close()
return []
