def insert_user_data(new_data, view_run_id, session):
view_run = session.query(ViewRun) \
.filter_by(id=view_run_id) \
.first()
data_view = view_run.data_view
business_keys = split_strip(data_view.business_keys, ",")
information_columns = split_strip(data_view.information_columns, ",")
keys = []
for key in business_keys + information_columns:
sql_filter = f"coalesce(data ->> '{key}', '') = '{new_data.get(key)}'"
keys.append(sql_filter)
where = ' AND '.join(keys)
current_data = session.query(UserData) \
.filter(sql.text(where)) \
.order_by(UserData.created_date.desc()) \
.first()
if not current_data:
user_data = UserData()
session.add(user_data)
user_data.data = new_data
user_data.view_run_id = view_run_id
else:
current_data.view_run_id = view_run_id
def create_view(data_view, session):
sql_text = f"create or replace view {data_view.view_name} as "
business_keys = []
information_columns = []
for col in split_strip(data_view.business_keys, ","):
business_keys.append({"json": "data ->> '" + col + "'", "name": col})
for col in split_strip(data_view.information_columns, ","):
information_columns.append({
"json": "data ->> '" + col + "'",
"name": col
})
business_keys_json_name = " , ".join(
[col["json"] + "as " + col["name"] for col in business_keys])
information_columns_json_name = " , ".join(
[col["json"] + "as " + col["name"] for col in information_columns])
business_keys_order = "row_number() over (partition by " + " , ".join(
col["json"]
for col in business_keys) + ' order by view_run_id desc) as rownum'
all_cols = business_keys_json_name + ', ' + information_columns_json_name + "," + business_keys_order
col_names = " , ".join(col["name"]
for col in business_keys + information_columns)
sql_text = f'''
create or replace view {data_view.view_name} as
with q1 as
(
SELECT
{all_cols}
, user_data.view_run_id
FROM
user_data
inner join view_runs on view_runs.id = user_data.view_run_id
WHERE
data_view_id = {data_view.id}
)
SELECT
{col_names}
, case
when view_run_id = (select max(id) from view_runs where data_view_id = {data_view.id})
then 1
end as in_last_run
FROM
q1
WHERE
rownum = 1;
'''
session.execute(sql_text)
def insert_user_data(new_data, data_view_id):
session = db_session.create_session()
data_view = session.query(DataView) \
.filter_by(id=data_view_id) \
.first()
keys = []
for key in split_strip(data_view.business_keys, ","):
sql_filter = f"data ->> '{key}' = '{new_data[key]}'"
keys.append(sql_filter)
where = ' AND '.join(keys)
current_data = session.query(UserData) \
.filter(sql.text(where)) \
.order_by(UserData.created_date.desc()) \
.first()
cols = []
if current_data:
for col in split_strip(data_view.information_columns, ","):
if current_data.data[col] != new_data[col]:
cols.append(col)
if not current_data or cols:
user_data = UserData()
user_data.data = new_data
user_data.data_view_id = data_view_id
session.add(user_data)
session.commit()
session.close()
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_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)