-
Notifications
You must be signed in to change notification settings - Fork 2
/
check_grade_book.py
238 lines (189 loc) · 8.24 KB
/
check_grade_book.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
# Standard library imports
import sys
import os
from datetime import datetime
# Third-party imports
import pandas as pd
import gspread
import tkinter as tk
from tkinter import scrolledtext
from tabulate import tabulate
# File names and paths
LINE_INCOMING_MESSAGE_FILENAME = "quiz_response"
LOG_LOCATION = r"txt_files/push_log.txt"
GRADE_BOOK_FILENAME = "quiz_grade_book"
SERVICE_ACCOUNT_PATH = r"./json_files/savvy-temple-381905-6e78e62d4ee5.json"
try:
SERVICE_ACCOUNT = gspread.service_account(
SERVICE_ACCOUNT_PATH) # type: ignore
except FileNotFoundError:
sys.exit(
"Service account file not found. Please download the file from Google Cloud Platform."
)
def format_quiz_times(
quiz_start_time: datetime, now: datetime, quiz_end_time: datetime
) -> str:
"""Format the quiz start time, current time, quiz end time, and quiz duration"""
duration = quiz_end_time - quiz_start_time
days = duration.days
hours, remainder = divmod(duration.seconds, 3600)
minutes, _ = divmod(remainder, 60)
quiz_times_str = (
f"開始時間:{quiz_start_time}\n"
f'現在時間:{now.strftime("%Y-%m-%d %H:%M:%S")}\n'
f"終了時間:{quiz_end_time}\n"
f"クイズ時間:{days}日{hours}時間{minutes}分\n"
)
return quiz_times_str
def get_quiz_answer() -> str:
"""Get the quiz answer from the log file"""
with open(LOG_LOCATION, "r", encoding="utf-8") as f:
lines = f.readlines()
answer = lines[2]
answer = answer.replace("単語意味クイズ解答:", "")
return answer.strip()
def calculate_point(correct: str, given: str) -> int:
"""Calculate the number of correct answers"""
correct = correct.strip().upper()
given = given.strip().upper()
if len(correct) != len(given):
return 0
return sum(c == g for c, g in zip(correct, given))
def process_data(raw_data: str, correct_answer: str) -> pd.Series:
"""Process the raw data from the LINE message file"""
try:
student_id, given_answer = raw_data.split("\n")
except ValueError:
return pd.Series(
["0", "0", "0"], index=["student_id", "given_answer", "points"]
)
points = calculate_point(correct_answer, given_answer)
return pd.Series(
[student_id, given_answer, points],
index=["student_id", "given_answer", "points"],
)
def parse_quiz_end_time(end_time: str) -> datetime:
"""Parse a quiz end time string to a datetime object."""
return datetime.strptime(end_time, "%Y-%m-%d %H:%M")
def get_quiz_start_time() -> tuple[datetime, datetime]:
"""Get the quiz start time from the log file"""
with open(LOG_LOCATION, "r", encoding="utf-8") as f:
quiz_start_time = f.readline().strip("\n").split(".")[0]
quiz_start_time = datetime.strptime(
quiz_start_time, "%Y-%m-%d %H:%M:%S")
now = datetime.now()
return now, quiz_start_time
def update_grade_book(df_result: pd.DataFrame, quiz_end_time: datetime) -> None:
"""Update the grade book with the quiz results"""
grade_book = SERVICE_ACCOUNT.open(GRADE_BOOK_FILENAME)
grade_sheet = grade_book.worksheet("シート1")
quiz_end_time_str = quiz_end_time.date().strftime("%Y/%m/%d")
header_row = grade_sheet.row_values(1)
if quiz_end_time_str not in header_row:
col_num = len(header_row) + 1
grade_sheet.update_cell(1, col_num, quiz_end_time_str)
header_row.append(quiz_end_time_str)
# Find the index of the date column
date_col_idx = header_row.index(quiz_end_time_str) + 1
for _, row in df_result.iterrows():
student_id = row["student_id"]
points = row["points"]
# Try to find the student ID in the sheet
try:
student_id_cell = grade_sheet.find(student_id)
if student_id_cell:
# If the student ID exists, update the points in the date column only if the cell is empty
existing_points = grade_sheet.cell(
student_id_cell.row, date_col_idx
).value
if not existing_points: # If the cell is empty
grade_sheet.update_cell(
student_id_cell.row, date_col_idx, points)
else:
# If the student ID doesn't exist, append a new row with the student ID and points
new_row = [student_id] + [""] * (date_col_idx - 2) + [points]
grade_sheet.append_row(new_row)
except gspread.exceptions.APIError as e:
print(f"Error: {e}")
def pretty_print_dataframe(df: pd.DataFrame) -> None:
"""Print a dataframe in a pretty format"""
data = df.reset_index().values.tolist()
for row in data:
row[0] += 2 # Add 2 to the 'Index' column
header = ["Index"] + df.columns.tolist()
print(tabulate(data, headers=header, tablefmt="grid"))
def display_table_in_popup(df, quiz_info) -> None:
"""Display the quiz results in a popup window"""
def tabulate_dataframe(df_) -> str:
"""Format a dataframe as a table"""
header = ["Index"] + df_.columns.tolist()
data = df_.reset_index().values.tolist()
for row in data:
row[0] += 2
formatted_table = tabulate(data, headers=header, tablefmt="grid")
return formatted_table
table_str = quiz_info + "\n" + tabulate_dataframe(df)
root = tk.Tk()
root.title("Quiz Results")
root.geometry("800x494") # Set the size of the root window
frame = tk.Frame(root)
frame.grid(row=0, column=0, sticky="nsew") # Place the frame using grid
root.grid_rowconfigure(0, weight=1)
root.grid_columnconfigure(0, weight=1)
text_area = scrolledtext.ScrolledText(frame, wrap=tk.WORD)
# Place the text area using grid
text_area.grid(row=0, column=0, sticky="nsew")
frame.grid_rowconfigure(0, weight=1)
frame.grid_columnconfigure(0, weight=1)
text_area.insert(tk.INSERT, table_str)
root.mainloop()
def main(end_time: str) -> None:
"""Main function to process the data and update the grade book"""
# Parse the quiz end time
quiz_end_time = parse_quiz_end_time(end_time)
now, quiz_start_time = get_quiz_start_time()
quiz_times_str = format_quiz_times(quiz_start_time, now, quiz_end_time)
print(quiz_times_str)
correct_answer = get_quiz_answer()
print(f"単語意味クイズ正解:{correct_answer}\n")
# Get the quiz answers from student messages
line_message = SERVICE_ACCOUNT.open(LINE_INCOMING_MESSAGE_FILENAME)
message_sheet = line_message.worksheet("Messages")
message_records = message_sheet.get_all_records()
df_message = pd.DataFrame(message_records)
df_message["Sent Time"] = pd.to_datetime(df_message["Sent Time"])
# Process the data
correct_answer = get_quiz_answer()
df_message = df_message.query(
"@quiz_start_time <= `Sent Time` <= @quiz_end_time")
df_processed = df_message["Message"].apply(
lambda x: process_data(x, correct_answer)
)
# Concatenate the processed data with the original data
df_result = pd.concat([df_message["Sent Time"], df_processed], axis=1)
try:
df_result = df_result.query(
'student_id != "0" or given_answer != "0" or points != "0"'
)
pretty_print_dataframe(df_result)
quiz_info = format_quiz_times(quiz_start_time, now, quiz_end_time)
correct_answer = get_quiz_answer()
quiz_info += f"単語意味クイズ正解:{correct_answer}\n"
display_table_in_popup(df_result, quiz_info)
except pd.errors.UndefinedVariableError:
sys.exit("Error: No data found. Check the quiz start and end times.")
if quiz_end_time > now:
sys.exit(
"Warning: quiz_end_time has not been reached. Data will not be updated."
)
else:
update_grade_book(df_result, quiz_end_time)
if __name__ == "__main__":
# Clearing the terminal
os.system("cls") if sys.platform.startswith(
"win32") else os.system("clear")
# Quiz end time in the format 'YYYY-MM-DD HH:mm'
main(end_time="2023-04-08 22:00")
# TODO: Set up a cron job to run this script every day at 12:00 AM
# TODO: Add a function to send a message to the students who have not submitted their answers
# TODO: Verify the USER ID as well