def get_list():
'''Return a list of all games in the database as a list of dictionaries.'''
sql = "SELECT \
g.id, \
g.name, \
g.start_date, \
g.end_date, \
g.location, \
g.price, \
go.person_id AS organiser_id, \
f.published, \
COUNT(r.person_id) AS num_registrations \
FROM Game AS g \
JOIN GameOrganiser AS go \
ON g.id = go.game_id \
LEFT JOIN Form AS f \
ON g.id = f.game_id \
LEFT JOIN Registration as r \
ON g.id = r.game_id \
GROUP BY g.id, g.name, g.start_date, g.end_date, g.location, g.price, go.person_id, f.published \
ORDER BY start_date"
result = db.session.execute(sql)
game_list = utils.to_dict_list(result.fetchall())
return game_list
def get_options(question_id):
'''Get the the options (id and text) for the given question.'''
sql = "SELECT \
id, \
option_text \
FROM Option \
WHERE question_id = :question_id;"
result = db.session.execute(sql, {"question_id": question_id})
return utils.to_dict_list(result.fetchall())
def get_field_types():
'''Return the identifiers and display names of all defined field types as a list of dictionaries.'''
sql = "SELECT \
id, \
display \
FROM FieldType \
ORDER BY display"
result = db.session.execute(sql)
field_types = utils.to_dict_list(result.fetchall())
return field_types
def get_registration_data(game_id):
'''Return the complete data for all the registrations for the game.'''
sql_regs = "SELECT \
id \
FROM Registration \
WHERE game_id = :game_id;"
sql_data = "SELECT \
fq.position AS question_no, \
q.question_text, \
a.answer_text, \
ARRAY_AGG(o.option_text) AS answer_options \
FROM Registration AS r \
JOIN Answer AS a \
ON r.id = a.registration_id \
JOIN FormQuestion AS fq \
ON a.formquestion_id = fq.id \
JOIN Question AS q \
ON fq.question_id = q.id \
LEFT JOIN AnswerOption AS ao \
ON a.id = ao.answer_id \
LEFT JOIN Option AS o \
ON ao.option_id = o.id \
WHERE r.id = :reg_id \
GROUP BY \
fq.position, \
q.question_text, \
a.answer_text \
ORDER BY fq.position ASC;"
result_regs = db.session.execute(sql_regs, {"game_id": game_id})
registration_ids = result_regs.fetchall()
registration_ids = [value for value, in registration_ids]
registrations = []
for id in registration_ids:
print("Registration ID: " + str(id))
result_questions = db.session.execute(sql_data, {"reg_id": id})
questions = utils.to_dict_list(result_questions.fetchall())
registration = {'registration_id': id}
question_list = []
for question in questions:
print(question)
if question['answer_text'] == None:
question['answer'] = list(question['answer_options'])
else:
question['answer'] = question['answer_text']
del question['answer_text']
del question['answer_options']
print(question)
question_list.append(question)
registration['questions'] = question_list
registrations.append(registration)
return registrations
def get_registrations():
'''Get the list of registrations for the currently logged in user as a list of dictionaries.'''
user_id = session.get("user_id",0)
sql_regs = "SELECT \
r.id, \
g.name, \
r.submitted \
FROM Registration AS r \
JOIN Game AS g \
ON r.game_id = g.id \
WHERE r.person_id = :id \
ORDER BY r.submitted ASC"
result_regs = db.session.execute(sql_regs, {"id":user_id})
registrations = utils.to_dict_list(result_regs.fetchall())
return registrations
def get_question_options(formquestion_id):
'''Get the options of a particular question as a list of dictionaries, based on form instance id.'''
sql = "SELECT \
o.id, \
o.option_text AS text \
FROM Option as o \
JOIN Question AS q \
ON o.question_id = q.id \
JOIN FormQuestion AS fq \
ON q.id = fq.question_id \
WHERE fq.id = :formquestion_id;"
result = db.session.execute(sql, {"formquestion_id": formquestion_id})
question_options = utils.to_dict_list(result.fetchall())
return question_options
def get_organisers(game_id):
'''Return the id, name components and email of the organisers for the given game as a list of dictionaries.'''
sql_orgs = "SELECT \
p.id, \
p.first_name, \
p.last_name, \
p.nickname, \
p.email \
FROM Person AS p \
JOIN GameOrganiser AS go \
ON p.id = go.person_id \
WHERE go.game_id = :id \
ORDER BY p.last_name"
result_orgs = db.session.execute(sql_orgs, {"id": game_id})
organisers = utils.to_dict_list(result_orgs.fetchall())
return organisers
def get_registrations(game_id):
'''Return all registrations for the given game as a list of dictionaries.'''
sql_regs = "SELECT \
ROW_NUMBER() OVER (ORDER BY r.submitted ASC) AS number, \
r.id, \
a.answer_text AS name, \
r.submitted \
FROM Registration AS r \
JOIN Answer AS a \
ON r.id = a.registration_id \
JOIN FormQuestion AS fq \
ON a.formquestion_id = fq.id \
JOIN Question AS q \
ON fq.question_id = q.id \
WHERE r.game_id = :id \
AND q.prefill_tag = 'name'\
ORDER BY r.submitted ASC"
result_regs = db.session.execute(sql_regs, {"id": game_id})
registrations = utils.to_dict_list(result_regs.fetchall())
return registrations
def get_form_questions(form_id):
'''Get all the questions of the given registration form as a list of dictionaries.'''
sql = "SELECT \
fq.id, \
ft.name AS field_type, \
q.question_text AS text, \
q.description, \
fq.position, \
q.is_default, \
q.is_optional, \
q.prefill_tag \
FROM Question AS q \
JOIN FieldType AS ft \
ON q.field_type = ft.id \
JOIN FormQuestion AS fq \
ON q.id = fq.question_id \
WHERE fq.form_id = :form_id \
ORDER BY fq.position;"
result = db.session.execute(sql, {"form_id": form_id})
form_questions = utils.to_dict_list(result.fetchall())
return form_questions