def gamerevent_list(request):
"""Function to build an HTML report of events by gamer"""
if request.method == 'GET':
# Connect to project database
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Query for all events by user
db_cursor.execute("""
SELECT
e.organizer_id,
u.first_name || ' ' || u.last_name AS full_name,
e.id,
e.date,
e.time,
g.title
FROM
levelupapi_event e
JOIN
levelupapi_game g ON e.game_id = g.id
JOIN
auth_user u ON e.organizer_id = u.id
""")
dataset = db_cursor.fetchall()
events_by_user = {}
for row in dataset:
# create an Event instance and set its properties
event = Event()
event.id = row["id"]
event.date = row["date"]
event.time = row["time"]
event.game_name = row["title"]
uid = row["organizer_id"]
if uid in events_by_user:
events_by_user[uid]['events'].append(event)
else:
events_by_user[uid] = {}
events_by_user[uid]["organizer_id"] = uid
events_by_user[uid]["full_name"] = row["full_name"]
events_by_user[uid]["events"] = [event]
list_of_users_with_events = events_by_user.values()
template = 'users/list_with_events.html'
context = {
'eventuser_list': list_of_users_with_events
}
return render(request, template, context)
def userevent_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.date,
e.time,
e.game_id,
g.title,
u.id user_id,
u.first_name || ' ' || u.last_name AS full_name
FROM
levelupapi_event e
JOIN
levelupapi_game g ON e.game_id = g.id
JOIN
levelupapi_gamer gr ON g.gamer_id = gr.id
JOIN
auth_user u ON gr.user_id = u.id
""")
dataset = db_cursor.fetchall()
events_by_user = {}
for row in dataset:
event = Event()
event.date = row["date"]
event.time = row["time"]
event.game_name = row["title"]
uid = row["user_id"]
if uid in events_by_user:
events_by_user[uid]['events'].append(event)
else:
events_by_user[uid] = {}
events_by_user[uid]["id"] = uid
events_by_user[uid]["full_name"] = row["full_name"]
events_by_user[uid]["events"] = [event]
list_of_users_with_events = events_by_user.values()
template = 'users/list_with_events.html'
context = {
'userevent_list': list_of_users_with_events
}
return render(request, template, context)
def event_attendee_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id as event_id,
e.date,
e.time,
g.title as game_name,
u.id AS user_id,
u.first_name || " " || u.last_name as user_full_name
FROM levelupapi_event e
JOIN levelupapi_eventgamer eg ON e.id = eg.event_id
JOIN levelupapi_gamer gr ON eg.gamer_id = gr.id
JOIN auth_user u ON gr.user_id = u.id
JOIN levelupapi_game g ON e.game_id = g.id
""")
dataset = db_cursor.fetchall()
events_dict = {}
"""
{
1: {
date:
time:
game_name:
attendees: [
{
gamer_id: 1
full_name: "steve brownlee"
}
]
}
}
"""
for row in dataset:
event = Event()
event.id = row["event_id"]
event.date = row["date"]
event.time = row["time"]
event.game_name = row["game_name"]
attendee_dict = {}
attendee_dict.user_id = row["user_id"]
attendee_dict.full_name = row["user_full_name"]
if event.id in events_dict:
events_dict[event.id]["attendees"].append(attendee_dict)
else:
events_dict[event.id] = {}
events_dict[event.id]["date"] = event.date
events_dict[event.id]["time"] = event.time
events_dict[event.id]["game_name"] = event.game_name
events_dict[event.id]["attendees"] = [attendee_dict]
event_list = events_dict.values()
template = "events/list_with_attendees.html"
context = {'event_attendee_list': event_list}
return render(request, template, context)
def event_host_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.date,
e.time,
e.id AS event_id,
g.title AS game_name,
u.id AS user_id,
u.first_name || " " || u.last_name AS user_full_name
FROM levelupapi_event e
JOIN levelupapi_gamer gr ON e.organizer_id = gr.id
JOIN auth_user u ON gr.user_id = u.id
JOIN levelupapi_game g ON e.game_id = g.id
""")
dataset = db_cursor.fetchall()
users_dict = {}
"""
{
1: {
organizer_id:
full_name:
events: [
{
date:
time:
id:
game_name:
}
]
}
}
"""
for row in dataset:
event = Event()
event.id = row["event_id"]
event.date = row["date"]
event.time = row["time"]
event.game_name = row["game_name"]
uid = row["user_id"]
if uid in users_dict:
users_dict[uid]['events'].append(event)
else:
users_dict[uid] = {}
users_dict[uid]["organizer_id"] = uid
users_dict[uid]["full_name"] = row["user_full_name"]
users_dict[uid]["events"] = [event]
list_of_users = users_dict.values()
template = "users/list_with_events.html"
context = {'event_host_list': list_of_users}
return render(request, template, context)