def add_manager_to_monitoring_program(program_id = None):
if program_id == None:
return "Not found", 404
user = get_user()
if not permitted(user, 'mangage_managers', 'program', program_id):
return "Not authorized", 401
body = request.json
email = body["email"]
if not re.match(email_regex, email):
return jsonify({ 'error' : '"%s" is not a valid email address' % email }), 400
manager = db_session.query(User).filter(User.email == email).one_or_none()
if not manager:
manager = User(email=email)
db_session.add(manager)
db_session.flush()
db_session.execute("""REPLACE INTO user_program_manager (user_id, monitoring_program_id) VALUES (:user_id, :program_id)""",
{ 'user_id': manager.id, 'program_id': program_id })
db_session.commit()
return "OK", 201
def get_source_processing_notes(source_id=None):
user = get_user()
if not permitted(user, 'get', 'source', source_id):
return "Not authorized", 401
source = db_session.query(Source).get(source_id) if source_id else None
if source == None:
return "Not found", 404
db_session.execute("SET time_zone = '+00:00'")
rows = db_session.execute("""SELECT
notes.id,
user.first_name,
user.last_name,
user.email,
notes.source_id,
notes.time_created,
notes.notes,
user.id = :user_id AS editable
FROM data_processing_notes notes
JOIN user ON notes.user_id = user.id
WHERE notes.source_id = :source_id
""", { 'source_id': source_id, 'user_id': user.id })
return jsonify_rows(rows)
def get_source_imports(source_id=None):
user = get_user()
if not permitted(user, 'get', 'source', source_id):
return "Not authorized", 401
source = db_session.query(Source).get(source_id) if source_id else None
if source == None:
return "Not found", 404
db_session.execute("SET time_zone = '+00:00'")
rows = db_session.execute("""SELECT
data_import.id,
data_import.filename,
data_import.data_type,
data_import_status.code AS status,
data_import.time_created,
data_import.upload_uuid
FROM data_import
LEFT JOIN data_import_status ON data_import_status.id = data_import.status_id
WHERE data_import.source_id = :source_id
""", { 'source_id': source_id })
return jsonify_rows(rows)
def create_or_update_program(program_id=None):
action = 'update' if program_id else 'create'
user = get_user()
if not permitted(user, action, 'program', program_id):
return "Not authorized", 401
if program_id:
program = db_session.query(MonitoringProgram).get(program_id)
else:
program = MonitoringProgram()
if not program:
return "Not found", 400
body = request.json
errors = validate_fields(program_fields, body)
if len(errors):
return jsonify(errors), 400
update_program_from_json(program, body)
db_session.add(program)
db_session.flush()
if action == 'create':
db_session.execute("""INSERT INTO user_program_manager (user_id, monitoring_program_id) VALUES (:user_id, :program_id)""",
{ 'program_id': program.id, 'user_id': user.id })
db_session.commit()
return jsonify(program_to_json(program)), 200 if program_id else 201
def update_import_statuses_after_restart():
db_session.execute("""UPDATE data_import
SET status_id = (SELECT id FROM data_import_status WHERE code = 'checked_error')
WHERE status_id = (SELECT id FROM data_import_status WHERE code = 'checking')""")
db_session.execute("""UPDATE data_import
SET status_id = (SELECT id FROM data_import_status WHERE code = 'import_error')
WHERE status_id = (SELECT id FROM data_import_status WHERE code = 'importing')""")
db_session.commit()
def get_monitoring_program_id(description):
if not description:
return None
for (program_id,) in db_session.execute("""SELECT id FROM monitoring_program WHERE description = :description""", { "description": description}):
return program_id
return db_session.execute("""
INSERT INTO monitoring_program (description)
VALUES (:description)""",
{ "description": description}).lastrowid
def delete_source_custodian(source_id=None, user_id=None):
user = get_user()
if not permitted(user, 'manage_custodians', 'source', source_id):
return "Not authorized", 401
db_session.execute("""DELETE FROM user_source
WHERE user_id = :user_id
AND source_id = :source_id""", { 'source_id': source_id, 'user_id': user_id })
db_session.commit()
return "OK", 200
def delete_monitoring_program(program_id = None):
if program_id == None:
return "Not found", 404
user = get_user()
if not permitted(user, 'delete', 'program', program_id):
return "Not authorized", 401
db_session.execute("""DELETE FROM monitoring_program WHERE id = :program_id""", { 'program_id': program_id })
db_session.commit()
return "OK", 200
def remove_source_from_monitoring_program(program_id = None, source_id = None):
if program_id == None or source_id == None:
return "Not found", 404
user = get_user()
if not permitted(user, 'update', 'source', program_id):
return "Not authorized", 401
db_session.execute("""UPDATE source SET monitoring_program_id = NULL WHERE id = :source_id AND monitoring_program_id = :program_id""",
{ 'program_id': program_id, 'source_id': source_id })
db_session.commit()
return "OK", 200
def remove_manager_from_monitoring_program(program_id = None, user_id = None):
if program_id == None or user_id == None:
return "Not found", 404
user = get_user()
if not permitted(user, 'mangage_managers', 'program', program_id):
return "Not authorized", 401
db_session.execute("""DELETE FROM user_program_manager WHERE user_id = :user_id AND monitoring_program_id = :program_id""",
{ 'program_id': program_id, 'user_id': user_id })
db_session.commit()
return "OK", 200
def create_user():
body = request.json
fields = [
Field(name='email', title='Email address', validators=[validate_required, validate_email]),
Field(name='first_name', title='First name', validators=[validate_required, validate_max_chars(255)]),
Field(name='last_name', title='Last name', validators=[validate_required, validate_max_chars(255)]),
Field(name='phone_number', title='Phone number', validators=[validate_max_chars(32)]),
Field(name='password', title='Password', validators=[validate_required, validate_min_chars(8)])
]
errors = validate_fields(fields, body)
if len(errors):
return jsonify(errors), 400
user = db_session.query(User).filter(User.email == body['email']).one_or_none()
if user:
if user.password_hash:
# User already has an ccount
return jsonify({ 'email': "An account with this email address already exists" }), 400
else:
user = User(email=body['email'].strip())
user.first_name=body['first_name'].strip()
user.last_name=body['last_name'].strip()
user.phone_number=body['phone_number'].strip()
user.password_hash=pwd_context.hash(body['password'])
try:
db_session.add(user)
db_session.flush()
except exc.IntegrityError:
# User already exists
pass
db_session.execute("""INSERT INTO user_role (user_id, role_id)
VALUES (:user_id, (SELECT id FROM role WHERE description = 'Custodian'))""",
{'user_id': user.id})
db_session.commit()
try:
email_body = new_account_body.substitute(name=user.first_name)
send_email(user.email, 'TSX Account Created', email_body)
except Exception as e:
print('Error sending email to %s' % user.email)
print('Error: %s' % e)
return "OK", 204 # Success
def get_processed_data(source_id=None):
(import_id,) = db_session.execute("SELECT MAX(id) FROM data_import WHERE source_id = :source_id", { 'source_id': source_id }).fetchone()
if import_id == None:
return "Not found", 404
with lock:
pinfo = processing_info.get(import_id)
if pinfo is None:
# Maybe processing has completed?
path = processed_data_dir(source_id, import_id)
if os.path.exists(path):
items = []
agg_path = os.path.join(path, 'aggregated.csv')
if os.path.exists(agg_path):
items.append({ 'name': 'Aggregated time series (CSV format)', 'id': 'aggregated.csv' })
trend_path = os.path.join(path, 'trend.csv')
if os.path.exists(trend_path):
items.append({ 'name': 'Population trend (CSV format)', 'id': 'trend.csv' })
return jsonify({
'processing_status': 'ready',
'items': items
})
else:
process_data(source_id, import_id)
return({ 'processing_status': 'pending' })
else:
return jsonify(pinfo)
def is_program_manager_of_program(user_id, monitoring_program_id):
return len(
db_session.execute(
"""SELECT 1 FROM user_program_manager
WHERE user_id = :user_id
AND monitoring_program_id = :monitoring_program_id""", {
'monitoring_program_id': monitoring_program_id,
'user_id': user_id
}).fetchall()) > 0
def is_custodian_of_source(user_id, source_id):
return len(
db_session.execute(
"""SELECT 1 FROM user_source
WHERE user_id = :user_id
AND source_id = :source_id""", {
'source_id': source_id,
'user_id': user_id
}).fetchall()) > 0
def get_processed_data_item(source_id=None, item_id=None):
(import_id,) = db_session.execute("SELECT MAX(id) FROM data_import WHERE source_id = :source_id", { 'source_id': source_id }).fetchone()
if import_id == None:
return "Not found", 404
path = processed_data_dir(source_id, import_id)
item_path = os.path.join(path, item_id)
return send_file(item_path, mimetype = 'text/csv', cache_timeout = 5)
def is_program_manager_of_source(user_id, source_id):
return len(
db_session.execute(
"""SELECT 1 FROM user_program_manager, source
WHERE user_id = :user_id
AND source.monitoring_program_id = user_program_manager.monitoring_program_id
AND source.id = :source_id""", {
'source_id': source_id,
'user_id': user_id
}).fetchall()) > 0
def update_user_role(user_id):
user = get_user()
if 'Administrator' not in get_roles(user):
return "Forbidden", 403
body = request.json
try:
new_role = body['role']
except KeyError:
return "Missing role", 400
db_session.execute("DELETE FROM user_role WHERE user_id = :user_id", { 'user_id': user_id })
print(new_role)
print(user_id)
db_session.execute("INSERT INTO user_role (user_id, role_id) SELECT :user_id, (SELECT id FROM role WHERE description = :role)", { 'user_id': user_id, 'role': new_role })
db_session.commit()
return "OK", 200
def source_to_json(source):
(has_t1_data,) = db_session.execute("""SELECT EXISTS (SELECT 1 FROM t1_survey WHERE source_id = :source_id)""", {"source_id": source.id}).fetchone()
json = {
'id': source.id,
'has_t1_data': has_t1_data
}
for field in source_fields:
if field.name == 'monitoring_program' and source.monitoring_program:
json['monitoring_program'] = source.monitoring_program.description
else:
json[field.name] = getattr(source, field.name)
return json
def get_sources():
user = get_user()
if not permitted(user, 'list', 'source'):
return "Not authorized", 401
db_session.execute("SET time_zone = '+00:00'")
program_id = request.args.get('program_id')
print(user.id)
rows = db_session.execute(
"""SELECT
source.id,
source.description,
data_import_status.code AS status,
source.time_created
FROM source
LEFT JOIN (SELECT source_id, max(data_import.id) AS data_import_id FROM data_import GROUP BY source_id) AS latest_import
ON latest_import.source_id = source.id
LEFT JOIN data_import ON latest_import.data_import_id = data_import.id
LEFT JOIN data_import_status ON data_import_status.id = data_import.status_id
WHERE
(
EXISTS (SELECT 1 FROM user_role WHERE user_id = :user_id AND role_id = 1) OR
(
EXISTS (SELECT 1 FROM user_role WHERE user_id = :user_id AND role_id = 2) AND
source.id IN (SELECT source_id FROM user_source WHERE user_id = :user_id)
) OR
(
EXISTS (SELECT 1 FROM user_role WHERE user_id = :user_id AND role_id = 3) AND
source.monitoring_program_id IN (SELECT monitoring_program_id FROM user_program_manager WHERE user_id = :user_id)
)
)
AND (:program_id IS NULL OR monitoring_program_id = :program_id)
""",
{ 'user_id': user.id, 'program_id': program_id })
return jsonify_rows(rows)
def create_or_update_source(source_id=None):
action = 'update' if source_id else 'create'
user = get_user()
if not permitted(user, action, 'source', source_id):
return "Not authorized", 401
if source_id:
source = db_session.query(Source).get(source_id)
else:
source = Source()
if not source:
return "Not found", 400
body = request.json
errors = validate_fields(source_fields, body)
if len(errors):
return jsonify(errors), 400
update_source_from_json(source, body)
db_session.add(source)
db_session.flush()
if action == 'create':
db_session.execute("""INSERT INTO user_source (user_id, source_id) VALUES (:user_id, :source_id)""",
{ 'source_id': source.id, 'user_id': user.id })
else:
remove_orphaned_monitoring_programs()
db_session.commit()
return jsonify(source_to_json(source)), 200 if source_id else 201
def create_source_custodian(source_id=None):
user = get_user()
if not permitted(user, 'manage_custodians', 'source', source_id):
return "Not authorized", 401
body = request.json
email = body["email"]
if not re.match(email_regex, email):
return jsonify({ 'error' : '"%s" is not a valid email address' % email }), 400
custodian = db_session.query(User).filter(User.email == email).one_or_none()
if not custodian:
if auto_create_custodians:
custodian = User(email=email)
db_session.add(custodian)
db_session.flush()
else:
error_message = 'No user found with the email address "%s". (Note: custodians must first create an account before they can be added)' % email
return jsonify({ 'error': error_message }), 400
rows = db_session.execute("""SELECT 1
FROM user_source
WHERE user_id = :user_id
AND source_id = :source_id
""", { 'source_id': source_id, 'user_id': custodian.id })
if len(list(rows)) == 0:
db_session.execute("""INSERT INTO user_source (user_id, source_id) VALUES (:user_id, :source_id)""",
{ 'source_id': source_id, 'user_id': custodian.id })
db_session.commit()
return "OK", 201
def update_programs(user_id=None):
user = get_user()
if not permitted(user, 'update_programs', 'user', user_id):
return "Not authorized", 401
if user_id == None:
return "Not found", 404
body = request.json
db_session.execute(
"""DELETE FROM user_program_manager WHERE user_id = :user_id""",
{"user_id": user_id})
for program_id in body:
db_session.execute(
"""INSERT INTO user_program_manager (user_id, monitoring_program_id) VALUES (:user_id, :program_id)""",
{
"user_id": user_id,
"program_id": program_id
})
db_session.commit()
return "OK", 201
def approve_import(import_id=None):
data_import = load_import(import_id)
if not data_import:
return "Not found", 404
old_status = status_codes[data_import.status_id]
if old_status != 'imported':
return "Cannot approve import with status '%s'" % old_status, 400
user = get_user()
if not permitted(user, 'approve', 'import', import_id):
return 'Not authorized', 401
db_session.execute("UPDATE data_import SET status_id = :status_id WHERE id = :import_id", {
'status_id': status_ids['approved'],
'import_id': import_id
})
db_session.commit()
data_import.status_id = status_ids['approved']
return jsonify(data_import_json(data_import))
def get_programs(user_id=None):
user = get_user()
if not permitted(user, 'list_programs', 'user', user_id):
return "Not authorized", 401
if user_id == None:
return "Not found", 404
rows = db_session.execute(
"""
SELECT monitoring_program.id, description
FROM monitoring_program, user_program_manager
WHERE monitoring_program.id = monitoring_program_id
AND user_id = :user_id""", {"user_id": user_id})
return jsonify_rows(rows)
def get_program_managers(program_id=None):
user = get_user()
if not permitted(user, 'list_managers', 'program', program_id):
return "Not authorized", 401
if program_id == None:
return "Not found", 404
rows = db_session.execute(
"""
SELECT user.id, email, first_name, last_name
FROM user, user_program_manager
WHERE user.id = user_id
AND monitoring_program_id = :program_id""", {"program_id": program_id})
return jsonify_rows(rows)
def delete_source(source_id=None):
user = get_user()
if not permitted(user, 'delete', 'source', source_id):
return "Not authorized", 401
db_session.execute("""DELETE FROM user_source WHERE source_id = :source_id""", { 'source_id': source_id })
db_session.execute("""DELETE FROM data_import WHERE source_id = :source_id""", { 'source_id': source_id })
db_session.execute("""DELETE FROM source WHERE id = :source_id""", { 'source_id': source_id })
remove_orphaned_monitoring_programs()
db_session.commit()
return "OK", 200
def get_source_custodians(source_id=None):
user = get_user()
if not permitted(user, 'manage_custodians', 'source', source_id):
return "Not authorized", 401
source = db_session.query(Source).get(source_id) if source_id else None
if source == None:
return "Not found", 404
rows = db_session.execute("""SELECT
user.first_name,
user.last_name,
user.email,
user.id
FROM user_source
JOIN user ON user_source.user_id = user.id
WHERE user_source.source_id = :source_id
""", { 'source_id': source_id })
return jsonify_rows(rows)
def users(): user = get_user() if 'Administrator' not in get_roles(user): return "Forbidden", 403 sql = """SELECT user.id, user.email, user.first_name, user.last_name, user.phone_number, MAX(role.description = 'Administrator') AS is_admin, COALESCE(MAX(role.description), 'Custodian') AS role FROM user LEFT JOIN user_role ON user.id = user_role.user_id LEFT JOIN role ON user_role.role_id = role.id GROUP BY user.id""" rows = db_session.execute(sql) return jsonify([dict(row) for row in rows])
def query_to_json(query, params={}):
return jsonify([dict(row) for row in db_session.execute(query, params)])
def process_unprocessed():
for (source_id,import_id) in db_session.execute("SELECT source_id, max(id) FROM data_import WHERE source_id IS NOT NULL GROUP BY source_id"):
process_data(source_id, import_id)
