def get_user_sheets(user_id):
query = (f"""
SELECT s_id,
s_google_id,
aurs_is_owner,
s_sheet_name,
s_row_count,
COALESCE(views, 0) AS views,
ss_status_name,
s_last_modified
FROM sheet AS s
INNER JOIN (
SELECT aurs_s_id
, aurs_is_owner
FROM app_user_rel_sheet
WHERE aurs_au_id = {user_id}
AND NOT aurs_deleted
) AS aurs
ON aurs_s_id = s_id
INNER JOIN sheet_status
ON s_ss_id = ss_id
LEFT JOIN (
SELECT v_s_id,
COUNT(v_id) AS views
FROM public.view
GROUP BY v_s_id
ORDER BY views DESC
) AS vs
ON s.s_id = vs.v_s_id;
""")
data = db_session.execute(query)
return data
def get_request_sheets():
query = ("""
SELECT s_id,
s_google_id,
FALSE AS aurs_is_owner,
s_sheet_name,
s_row_count,
COALESCE(views, 0) AS views,
ss_status_name,
s_last_modified
FROM sheet AS s
INNER JOIN sheet_status
ON s_ss_id = ss_id
LEFT JOIN (
SELECT v_s_id,
COUNT(v_id) AS views
FROM public.view
GROUP BY v_s_id
ORDER BY views DESC
) AS vs
ON s.s_id = vs.v_s_id
WHERE ss_status_name = 'Review Requested';
""")
data = db_session.execute(query)
return data
def get_user_data():
query = ("""
SELECT au_id,
aur_role_name,
au_email,
CONCAT(au_first_name, ' ', au_last_name) AS au_full_name,
au_created,
au_last_modified
FROM app_user
INNER JOIN app_user_role
ON au_aur_id = aur_id
""")
data = db_session.execute(query)
return data