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 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_title,
u.id as user_id,
u.first_name || " " || u.last_name as user_full_name
from levelupapi_event e
join levelupapi_gamerevent 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 = {}
for row in dataset:
event = Event()
event.id = row["event_id"]
event.date = row['date']
event.time = row['time']
event.game_title = row['game_title']
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_title'] = event.game_title
events_dict[event.id]['attendees'] = [attendee_dict]
events_list = events_dict.values
template = 'users/list_with_attendees.html'
context = {'event_attendee_list': events_list}
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)
def userevent_list(request):
"""Function to build an HTML report of games by user"""
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, with related user info.
db_cursor.execute("""
SELECT
eg.id,
u.first_name || ' ' || u.last_name AS full_name,
u.id user_id,
eg.event_id,
e.location,
e.time,
e.day,
e.id event_id_number,
g.title
FROM levelupapi_eventgamer eg
JOIN levelupapi_event e ON e.id = eg.event_id
JOIN levelupapi_gamer gr ON gr.id = eg.gamer_id
JOIN levelupapi_game g ON g.id = e.game_id
JOIN auth_user u ON u.id = gr.user_id
""")
dataset = db_cursor.fetchall()
events_by_user = {}
for row in dataset:
# Crete a Event instance and set its properties
event = Event()
event.id = row["event_id_number"]
event.day = row["day"]
event.time = row["time"]
event.location = row["location"]
event.title = row["title"]
# Store the user's id
uid = row["user_id"]
# If the user's id is already a key in the dictionary...
if uid in events_by_user:
# Add the current event to the `events` list for it
events_by_user[uid]['events'].append(event)
else:
# Otherwise, create the key and dictionary value
events_by_user[uid] = {}
events_by_user[uid]["user_id"] = uid
events_by_user[uid]["full_name"] = row["full_name"]
events_by_user[uid]["events"] = [event]
# Get only the values from the dictionary and create a list from them
list_of_users_with_events = events_by_user.values()
# Specify the Django template and provide data context
template = 'users/list_with_events.html'
context = {'userevents_list': list_of_users_with_events}
return render(request, template, context)