def task_view(task_id):
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT tasks.id, tasks.user_id, tasks.task_id, tasks.status, results.result, tasks.created_at, tasks.modified_at, tasks.function_id, functions.function_name, tasks.endpoint_id, sites.endpoint_name, username "
"FROM tasks, results, sites, functions, users "
"WHERE results.task_id = tasks.task_id AND sites.endpoint_uuid = tasks.endpoint_id AND functions.function_uuid = tasks.function_id AND tasks.task_id = %s AND cast(tasks.user_id as integer) = users.id "
"AND function_id IS NOT NULL;", (task_id, ))
task = cur.fetchone()
if task == None:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
if task['username'] != session.get('username'):
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
name = task['task_id']
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.tasks'))
return render_template('task_view.html',
user=session.get('name'),
title='View Task',
task=task)
def home():
if 'username' not in session:
return redirect(url_for('auth_api.login'))
stats = [0 for i in range(3)]
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT count(functions.id) FROM functions, users WHERE functions.user_id = users.id AND users.username = %s",
(session.get("username"), ))
executions = cur.fetchone()
stats[0] = num_delimiter(executions['count'], "int")
cur.execute(
"SELECT tasks.created_at, tasks.modified_at FROM tasks, users WHERE cast(tasks.user_id as integer) = users.id AND users.username = %s",
(session.get("username"), ))
tasks = cur.fetchall()
stats[1] = num_delimiter(len(tasks), "int")
if len(tasks) != 0:
times = list()
for task in tasks:
times.append(task['modified_at'] - task['created_at'])
count = timedelta(hours=0)
for time in times:
count += time
stats[2] = num_delimiter(
round((count.total_seconds() / 3600.0), 2), "decimal")
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.start'))
return render_template('home.html',
user=session.get('name'),
title='Home',
stats=stats)
def load_op_return(axioma_ids, table_name):
day_counts = [1, 5, 10, 15, 21, 42, 63, 126]
columns = ["date", "axioma_id"]
for n in day_counts:
for ret_type in ["total", "idio", "sector"]:
columns.append(f"{ret_type}_{n}d_fwd")
errors = []
for id in axioma_ids:
try:
print(id)
df = pd.read_sql(
f"""
select axioma_id,date,total_return,factor_return,idio_return,sector_return
from op_raw_return
where axioma_id = '{id}'
""",
utils.get_db_connection(),
)
for n in day_counts:
for ret_type in ["total"]:
col_name = f"{ret_type}_{n}d_fwd"
df[col_name] = (
(1 +
df[[f"{ret_type}_return"]]).pct_change(n).shift(-n))
for ret_type in ["idio", "sector"]:
col_name = f"{ret_type}_{n}d_fwd"
total_col_name = f"total_return"
df[col_name] = (df.shift(-n)[f"{ret_type}_return"] -
df[f"{ret_type}_return"]) / (
1 + df[total_col_name])
df = df.rename(
columns={
"total_return": "total_return_index",
"factor_return": "factor_return_index",
"idio_return": "idio_return_index",
"sector_return": "sector_return_index",
})
df = df.replace([np.inf, -np.inf], np.nan)
df.to_sql(table_name,
utils.get_db_connection(),
if_exists="append",
index=False)
except Exception as e:
errors.append((e, id))
return errors
def endpoint_view(endpoint_uuid):
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT sites.id, sites.user_id, sites.created_at, status, endpoint_name, endpoint_uuid, public, sites.deleted, username "
"FROM sites, users "
"WHERE sites.endpoint_uuid = %s "
"AND users.id = sites.user_id "
"AND endpoint_name IS NOT NULL AND sites.deleted = 'f';",
(endpoint_uuid, ))
endpoint = cur.fetchone()
if endpoint == None:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
if endpoint['username'] != session.get(
'username') and endpoint['public'] is False:
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
name = endpoint['endpoint_name']
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.endpoints'))
delete_form = DeleteForm()
if delete_form.validate_on_submit() and delete_form.delete.data:
json = {'endpoint': endpoint['endpoint_uuid']}
tokens = session.get("tokens")
funcx_tokens = tokens['funcx_service']
access_token = "Bearer " + funcx_tokens['access_token']
response = requests.post("http://dev.funcx.org/api/v1/delete_endpoint",
headers={"Authorization": access_token},
json=json)
result = response.json()['result']
if result == 302:
flash(f'Deleted Endpoint "{name}".', 'success')
return redirect(url_for('guiapi.endpoints'))
elif result == 403:
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
elif result == 404:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
else:
flash('There was an issue handling your request.', 'danger')
# return redirect(url_for('guiapi.endpoints'))
return render_template('endpoint_view.html',
user=session.get('name'),
title=f'View "{name}"',
endpoint=endpoint,
delete_form=delete_form)
def winsorize_op_return(table_name):
columns = [
"total_0d_fwd",
"idio_0d_fwd",
"sector_0d_fwd",
"idio_and_sector_0d_fwd",
"total_1d_fwd",
"idio_1d_fwd",
"sector_1d_fwd",
"idio_and_sector_1d_fwd",
"total_5d_fwd",
"idio_5d_fwd",
"sector_5d_fwd",
"idio_and_sector_5d_fwd",
"total_10d_fwd",
"idio_10d_fwd",
"sector_10d_fwd",
"idio_and_sector_10d_fwd",
"total_15d_fwd",
"idio_15d_fwd",
"sector_15d_fwd",
"idio_and_sector_15d_fwd",
"total_21d_fwd",
"idio_21d_fwd",
"sector_21d_fwd",
"idio_and_sector_21d_fwd",
"total_42d_fwd",
"idio_42d_fwd",
"sector_42d_fwd",
"idio_and_sector_42d_fwd",
"total_63d_fwd",
"idio_63d_fwd",
"sector_63d_fwd",
"idio_and_sector_63d_fwd",
"total_126d_fwd",
"idio_126d_fwd",
"sector_126d_fwd",
"idio_and_sector_126d_fwd",
]
connection = utils.get_db_connection()
connection.execute("truncate table winsorize_return")
connection.execute(
f"insert into winsorize_return (date) select distinct date from {table_name}"
)
for col_name in columns:
print("building winsorize_return ", col_name)
query = get_winsorize_sql(col_name, table_name)
connection.execute(query)
for col_name in columns:
print("winsorizing ", col_name)
connection.execute(get_ceiling(col_name, table_name))
connection.execute(get_floor(col_name, table_name))
def authorize_function(user_id, function_uuid, token):
"""Determine whether or not the user is allowed to access this function.
This is done in two steps: first, check if the user owns the function. If not,
check if there are any groups associated with the function and determine if the user
is a member of any of them.
Parameters
----------
user_id : str
The primary identity of the user
function_uuid : str
The uuid of the function
token : str
The auth token
Returns
-------
bool
Whether or not the user is allowed access to the function
"""
authorized = False
try:
conn, cur = get_db_connection()
# Check if the user owns the endpoint
query = "select * from functions where function_uuid = %s"
cur.execute(query, (function_uuid, ))
row = cur.fetchone()
app.logger.debug(f"Endpoint auth row: {row}")
if len(row) > 0:
# Check if the user owns it
if row['user_id'] == user_id:
authorized = True
elif row['public']:
authorized = True
if not authorized:
# Check if there are any groups associated with this function
query = "select * from function_auth_groups where function_id = %s"
cur.execute(query, (function_uuid, ))
rows = cur.fetchall()
endpoint_groups = []
for row in rows:
endpoint_groups.append(row['group_id'])
if len(endpoint_groups) > 0:
authorized = check_group_membership(token, endpoint_groups)
except Exception as e:
print(e)
app.logger.error(e)
return authorized
def update_op_return(table_name):
command = f"""
drop table if exists #map
drop table if exists #op_return
--Take the max sedol arbitrarily.
select map.axioma_id, max(map.sedol) sedol
into #map
from sedol_map map
group by map.axioma_id
create clustered index map_idx on #map (sedol)
update {table_name}
set idio_and_sector_return_index = (idio_return_index + sector_return_index),
idio_and_sector_0d_fwd = (sector_0d_fwd + idio_0d_fwd),
idio_and_sector_1d_fwd = (sector_1d_fwd + idio_1d_fwd),
idio_and_sector_5d_fwd = (sector_5d_fwd + idio_5d_fwd),
idio_and_sector_10d_fwd = (sector_10d_fwd + idio_10d_fwd),
idio_and_sector_15d_fwd = (sector_15d_fwd + idio_15d_fwd),
idio_and_sector_21d_fwd = (sector_21d_fwd + idio_21d_fwd),
idio_and_sector_42d_fwd = (sector_42d_fwd + idio_42d_fwd),
idio_and_sector_63d_fwd = (sector_63d_fwd + idio_63d_fwd),
idio_and_sector_126d_fwd = (sector_126d_fwd + idio_126d_fwd)
from {table_name}
select ret.date,
ret.axioma_id,
map.sedol,
LAG(total_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) total_0d_fwd,
LAG(idio_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) idio_0d_fwd,
LAG(sector_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) sector_0d_fwd,
LAG(idio_and_sector_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) idio_and_sector_0d_fwd
into #op_return
from {table_name} ret
join #map map on map.axioma_id = ret.axioma_id
update ret
set ret.sedol = temp.sedol,
ret.total_0d_fwd = temp.total_0d_fwd,
ret.idio_0d_fwd = temp.idio_0d_fwd,
ret.sector_0d_fwd = temp.sector_0d_fwd,
ret.idio_and_sector_0d_fwd = temp.idio_and_sector_0d_fwd
from {table_name} ret
join #op_return temp on temp.axioma_id = ret.axioma_id and temp.date = ret.date
"""
connection = utils.get_db_connection()
connection.execute(command)
def endpoints():
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT DISTINCT sites.user_id, endpoint_name, endpoint_uuid, status, sites.created_at, public FROM sites, users WHERE ((sites.user_id = users.id AND users.username = %s AND sites.public = 'f') OR (sites.public = 't')) AND sites.deleted = 'f' AND endpoint_uuid is not null order by created_at desc;",
(session.get("username"), ))
endpoints = cur.fetchall()
endpoints_total = len(endpoints)
private_endpoints = list()
private_endpoints_total = 0
private_endpoints_online_total = 0
public_endpoints = list()
public_endpoints_total = 0
public_endpoints_online_total = 0
for endpoint in endpoints:
if endpoint['public'] is False:
private_endpoints_total += 1
if endpoint['status'] == "ONLINE":
private_endpoints_online_total += 1
else:
public_endpoints_total += 1
if endpoint['status'] == "ONLINE":
public_endpoints_online_total += 1
private_endpoints.append(private_endpoints_total)
private_endpoints.append(private_endpoints_online_total)
private_endpoints.append(private_endpoints_total -
private_endpoints_online_total)
public_endpoints.append(public_endpoints_total)
public_endpoints.append(public_endpoints_online_total)
public_endpoints.append(public_endpoints_total -
public_endpoints_online_total)
numPages = ceil(endpoints_total / 30)
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.home'))
return render_template('endpoints.html',
user=session.get('name'),
title='Endpoints',
endpoints=endpoints,
endpoints_total=endpoints_total,
private_endpoints=private_endpoints,
public_endpoints=public_endpoints,
numPages=numPages)
def function_tasks(uuid):
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT function_uuid, function_name, username FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id",
(uuid, ))
func = cur.fetchone()
if func == None:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
if func['username'] != session.get('username'):
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
func_name = func['function_name']
cur.execute(
"SELECT task_id, cast(tasks.user_id as integer), tasks.function_id, functions.function_name, tasks.status, tasks.created_at, tasks.endpoint_id, sites.endpoint_name "
"FROM tasks, sites, users, functions "
"WHERE tasks.endpoint_id = sites.endpoint_uuid AND cast(tasks.user_id as integer) = users.id AND tasks.function_id = functions.function_uuid "
"AND tasks.function_id = %s"
"ORDER by tasks.task_id desc", (uuid, ))
func_tasks = cur.fetchall()
tasks_total = len(func_tasks)
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.function_view', func=func))
try:
numPages = ceil(tasks_total / 30)
except:
return render_template('function_tasks.html',
user=session.get('name'),
title=f'Tasks of "{func_name}"')
return render_template('function_tasks.html',
user=session.get('name'),
title=f'Tasks of "{func_name}"',
func_tasks=func_tasks,
tasks_total=tasks_total,
func=func,
numPages=numPages)
def load_op_raw_return(axioma_ids, start_date, end_date):
errors = []
for id in axioma_ids:
try:
df_ret = op.get_stock_returns("model_provider_id", [id],
start_date, end_date)
df_ret = df_ret.rename(
columns={
"model_provider_id": "axioma_id",
"specific_return": "idio_return",
})
df_ret.to_sql(
"op_raw_return",
utils.get_db_connection(),
if_exists="append",
index=False,
)
except Exception as e:
errors.append((e, id))
return errors
def functions():
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT function_name, timestamp, modified_at, function_uuid FROM functions, users WHERE functions.user_id = users.id AND users.username = %s AND functions.deleted = False ORDER BY functions.id desc",
(session.get("username"), ))
functions = cur.fetchall()
functions_total = len(functions)
numPages = ceil(functions_total / 30)
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.home'))
return render_template('functions.html',
user=session.get('name'),
title='Your Functions',
functions=functions,
functions_total=functions_total,
numPages=numPages)
def tasks():
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT tasks.id, users.id, cast(tasks.user_id as integer), tasks.task_id, results.result, tasks.status, tasks.function_id, functions.function_name, tasks.endpoint_id, sites.endpoint_name "
"FROM results, tasks, users, functions, sites "
"WHERE results.task_id = tasks.task_id AND users.id = cast(tasks.user_id as integer) AND sites.endpoint_uuid = tasks.endpoint_id AND functions.function_uuid = tasks.function_id "
"AND function_id IS NOT NULL AND users.username = %s "
"ORDER by tasks.id desc", (session.get("username"), ))
tasks = cur.fetchall()
tasks_total = len(tasks)
numPages = ceil(tasks_total / 30)
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.home'))
return render_template('tasks.html',
user=session.get('name'),
title='Tasks',
tasks=tasks,
tasks_total=tasks_total,
numPages=numPages)
def authorize_endpoint(user_id, endpoint_uuid, function_uuid, token):
"""Determine whether or not the user is allowed to access this endpoint.
This is done in two steps: first, check if the user owns the endpoint. If not,
check if there are any groups associated with the endpoint and determine if the user
is a member of any of them.
Parameters
----------
user_id : str
The primary identity of the user
endpoint_uuid : str
The uuid of the endpoint
function_uuid : str
The uuid of the function
token : str
The auth token
Returns
-------
bool
Whether or not the user is allowed access to the endpoint
"""
authorized = False
try:
conn, cur = get_db_connection()
# Check if the user owns the endpoint
query = "select * from sites where endpoint_uuid = %s"
cur.execute(query, (endpoint_uuid, ))
row = cur.fetchone()
app.logger.debug(f"Endpoint auth row: {row}")
# If the endpoint is flagged as protected we need to overwrite
if len(row) > 0:
# If it is restricted we need to check the function is allowed, otherwise nothing else matters
if row['restricted']:
app.logger.debug(
"Restricted endpoint, checking function is allowed.")
query = "select * from restricted_endpoint_functions where endpoint_id = %s and function_id = %s"
cur.execute(query, (endpoint_uuid, function_uuid))
funcs = cur.fetchall()
app.logger.debug(f"Length of query response: {len(funcs)}")
if len(funcs) == 0:
# There is no entry of this function, so reject it.
raise Exception(
f"Function {function_uuid} not permitted on endpoint {endpoint_uuid}"
)
# Check if the user owns it
if row['user_id'] == user_id:
authorized = True
# Otherwise if the row is public
elif row['public']:
authorized = True
if not authorized:
# Check if there are any groups associated with this endpoint
query = "select * from auth_groups where endpoint_id = %s"
cur.execute(query, (endpoint_uuid, ))
rows = cur.fetchall()
endpoint_groups = []
for row in rows:
endpoint_groups.append(row['group_id'])
if len(endpoint_groups) > 0:
authorized = check_group_membership(token, endpoint_groups)
except Exception as e:
print(e)
app.logger.error(e)
return authorized
connection.execute(get_ceiling(col_name, table_name))
connection.execute(get_floor(col_name, table_name))
if __name__ == "main":
# Note to get winsorization to work properly, first run:
# truncate table op_return
# Change this to op_return_test to test.
table_name = "op_return"
missing_ids = pd.read_sql(
"""
select distinct axioma_id from sedol_map map
where not exists (select * from op_raw_return ret
where ret.axioma_id = map.axioma_id)
""",
utils.get_db_connection(),
).axioma_id.values
errors_raw = load_op_raw_return(missing_ids, date(2012, 1, 1),
date(2019, 12, 31))
# errors_raw = load_op_raw_return(['11D7GQAK9'], date(2012,1,1), date(2019,12,31))
print(errors_raw)
missing_op_return_ids = pd.read_sql(
"""
select distinct axioma_id from op_raw_return raw
where not exists (select * from op_return ret
where ret.axioma_id = raw.axioma_id)
""",
utils.get_db_connection(),
).axioma_id.values
errors_op_return = load_op_return(missing_op_return_ids, table_name)
def function_view(uuid):
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT function_name, description, entry_point, users.id, username, timestamp, modified_at, function_uuid, status, function_code FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id AND functions.deleted = False",
(uuid, ))
func = cur.fetchone()
if func == None:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
if func['username'] != session.get('username'):
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
name = func['function_name']
user_id = func['id']
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.functions'))
execute_form = ExecuteForm()
cur.execute(
"SELECT DISTINCT endpoint_name, endpoint_uuid FROM sites WHERE endpoint_uuid IS NOT NULL AND ((user_id = %s AND sites.public = false) OR (sites.public = true));",
(user_id, ))
endpoints = cur.fetchall()
endpoint_uuids = list()
for endpoint in endpoints:
endpoint_uuids.append(
(endpoint['endpoint_uuid'], endpoint['endpoint_name']))
execute_form.endpoint.choices = endpoint_uuids
if execute_form.validate_on_submit() and execute_form.submit.data:
json = {
'func': func['function_uuid'],
'endpoint': execute_form.endpoint.data,
'data': execute_form.data.data
}
tokens = session.get("tokens")
funcx_tokens = tokens['funcx_service']
access_token = "Bearer " + funcx_tokens['access_token']
print("Sending Request")
response = requests.post("http://funcx.org/api/v1/execute",
headers={"Authorization": access_token},
json=json)
task_id = response.json()['task_id']
time.sleep(1)
return redirect(url_for('guiapi.task_view', task_id=task_id))
else:
print(execute_form.validate_on_submit())
print(execute_form.submit.data)
print("nope")
delete_form = DeleteForm()
if delete_form.validate_on_submit() and delete_form.delete.data:
print("Delete:" + str(delete_form.validate_on_submit()))
print("Delete:" + str(delete_form.delete.data))
json = {'func': func['function_uuid']}
tokens = session.get("tokens")
funcx_tokens = tokens['funcx_service']
access_token = "Bearer " + funcx_tokens['access_token']
response = requests.post("http://dev.funcx.org/api/v1/delete_function",
headers={"Authorization": access_token},
json=json)
result = response.json()['result']
if result == 302:
flash(f'Deleted Function "{name}".', 'success')
return redirect(url_for('guiapi.functions'))
elif result == 403:
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
elif result == 404:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
else:
flash('There was an issue handling your request.', 'danger')
# return redirect(url_for('guiapi.functions'))
return render_template('function_view.html',
user=session.get('name'),
title=f'View "{name}"',
func=func,
execute_form=execute_form,
delete_form=delete_form)
def function_edit(uuid):
if 'username' not in session:
return redirect(url_for('auth_api.login'))
try:
conn, cur = get_db_connection()
cur.execute(
"SELECT function_name, description, entry_point, username, timestamp, modified_at, function_uuid, status, function_code FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id",
(uuid, ))
func = cur.fetchone()
if func == None:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
if func['username'] != session.get('username'):
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
except:
flash('There was an issue handling your request.', 'danger')
return redirect(url_for('guiapi.function_view', uuid=uuid))
name = func['function_name']
form = EditForm()
if form.validate_on_submit():
json = {
'func': func['function_uuid'],
'name': form.name.data,
'desc': form.desc.data,
'entry_point': form.entry_point.data,
'code': form.code.data
}
tokens = session.get("tokens")
funcx_tokens = tokens['funcx_service']
access_token = "Bearer " + funcx_tokens['access_token']
response = requests.post("http://dev.funcx.org/api/v1/upd_function",
headers={"Authorization": access_token},
json=json)
result = response.json()['result']
if result == 302:
flash(f'Saved Function "{form.name.data}"!', 'success')
return redirect(url_for('guiapi.function_view', uuid=uuid))
elif result == 403:
return render_template('error.html',
user=session.get('name'),
title='403 Forbidden')
elif result == 404:
return render_template('error.html',
user=session.get('name'),
title='404 Not Found')
else:
flash('There was an issue handling your request.', 'danger')
# return redirect(url_for('guiapi.function_view', uuid=uuid))
form.name.data = func['function_name']
form.desc.data = func['description']
form.entry_point.data = func['entry_point']
form.code.data = func['function_code']
return render_template('function_edit.html',
user=session.get('name'),
title=f'Edit "{name}"',
func=func,
form=form,
cancel_route="view")