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)