import discord from discord.ext import commands, tasks import os import csv from datetime import datetime, timedelta from utils.deal_with_sql import ReminderDatabaseHandler import asyncio import traceback # 定義輸出 class ReminderOutput(commands.Cog): def __init__(self, bot): self.bot = bot self.data_folder = "data/reminder_files" self.data_file = "data/reminder_data.csv" self.data_dicts = {} # 所有提醒事項 self.wait_for_del = [] # 發送完畢的提醒事項 # test channel ID = 1323119161819271229 # show channel ID = 1345587616996655194 self.channel_id = 1345587616996655194 self.role_id = 1284710000186495191 # 要@的身分組ID(everyone) self.db_handler = ReminderDatabaseHandler() # 檢查檔案並加載提醒資料 self.load_reminders() # 開始檢查提醒的背景任務 self.check_reminders.start() async def delete_from_sql(self, reminder_id): """從SQL資料庫中刪除指定的reminder""" try: self.db_handler.connect() query = "DELETE FROM reminders WHERE reminder_userID_time = %s" self.db_handler.cursor.execute(query, (reminder_id,)) self.db_handler.connection.commit() print(f"Successfully deleted reminder {reminder_id} from SQL database") except Exception as e: print(f"Error deleting from SQL: {str(e)}") finally: self.db_handler.disconnect() async def sync_with_sql(self): """同步CSV和SQL資料庫的資料（雙向同步）""" try: self.db_handler.connect() # 從CSV讀取資料 csv_data = {} with open(self.data_file, mode="r", newline="", encoding="utf-8-sig") as f: csv_reader = csv.DictReader(f) for row in csv_reader: # 確保檔案名稱以 UTF-8 處理 if row["Files"] != "None": # row["Files"] = row["Files"].encode('utf-8').decode('utf-8') csv_data[row["reminder_userID_time"]] = row # 從SQL讀取資料 query = """ SELECT reminder_userID_time, title, content, first_reminder_time, repeat_count, repeat_interval, files FROM reminders """ self.db_handler.cursor.execute(query) sql_data = { row[0]: { 'reminder_userID_time': row[0], 'Title': row[1], 'Content': row[2], 'First Reminder Time': row[3], 'Repeat Count': str(row[4]), 'Interval': str(row[5]), 'Files': row[6] } for row in self.db_handler.cursor.fetchall() } # 找出在CSV中但不在SQL的資料 csv_only = {key: data for key, data in csv_data.items() if key not in sql_data} # 找出在SQL中但不在CSV的資料 sql_only = {key: data for key, data in sql_data.items() if key not in csv_data} # 將CSV獨有的資料新增到SQL if csv_only: insert_query = """ INSERT INTO reminders (reminder_userID_time, title, content, first_reminder_time, repeat_count, repeat_interval, files) VALUES (%s, %s, %s, %s, %s, %s, %s) """ for reminder_id, data in csv_only.items(): values = ( reminder_id, data['Title'], data['Content'], data['First Reminder Time'], data['Repeat Count'], data['Interval'], data['Files'] ) self.db_handler.cursor.execute(insert_query, values) self.db_handler.connection.commit() print(f"已新增 {len(csv_only)} 筆資料至SQL資料庫") # 將SQL獨有的資料新增到CSV if sql_only: # 先讀取現有的CSV標頭 with open(self.data_file, mode="r", newline="", encoding="utf-8-sig") as f: csv_reader = csv.reader(f) headers = next(csv_reader) # 將新資料加入現有CSV with open(self.data_file, mode="a", newline="", encoding="utf-8-sig") as f: csv_writer = csv.DictWriter(f, fieldnames=headers) for data in sql_only.values(): csv_writer.writerow(data) print(f"已新增 {len(sql_only)} 筆資料至CSV檔案") # 更新記憶體中的資料字典 if sql_only: self.data_dicts.update(sql_only) print(f"'data_dicts'中的資料已更新") except Exception as e: print(f"Error syncing with SQL: {str(e)}") traceback.print_exc() finally: self.db_handler.disconnect() def load_reminders(self): """讀取提醒資料(csv)並加載到字典(self.data_dicts)中""" try: with open(self.data_file, mode="r", newline="", encoding="utf-8-sig") as f: csv_reader = csv.DictReader(f) # 把每一行轉換成字典 # 將data_file值存入字典 for row in csv_reader: # 若 self.data_dicts 已經有了就不儲存 if row["reminder_userID_time"] not in self.data_dicts: row["Content"] = row["Content"].replace("\\n", "\n") try: row["Repeat Count"] = int(row["Repeat Count"]) except ValueError: row["Repeat Count"] = 0 try: row["Interval"] = int(row["Interval"]) except ValueError: row["Interval"] = 0 # 確保檔案名稱以 UTF-8 處理 #if row["Files"] != "None": # row["Files"] = row["Files"] #.encode('utf-8').decode('utf-8') key = row["reminder_userID_time"] self.data_dicts[key] = row print(f" - 訊息欄: self.data_dicts 更新 {key}") except FileNotFoundError: print(" - 錯誤訊息: CSV file not found.") def del_reminders(self, del_key_name): """刪除csv, self.data_dicts 的提醒資料""" # 檢查 del_key_name 是否在字典 self.data_dicts 中 if del_key_name in self.data_dicts: del self.data_dicts[del_key_name] # 刪除 data_dicts 指定的鍵 self.wait_for_del.remove(del_key_name) # 刪除 wait_for_del 指定的值 print(f"- 訊息欄: Deleted the item with key: {del_key_name} from 'self.data_dicts' and 'self.wait_for_del'.") # 從SQL刪除 asyncio.create_task(self.delete_from_sql(del_key_name)) # 刪除 csv 中的該筆資料(重新寫入) try: # 讀取原始 CSV 檔案 with open(self.data_file, 'r', newline='', encoding='utf-8') as csvfile: reader = csv.reader(csvfile) rows = list(reader) # 篩選出不是 del_key_name 的行 updated_rows = [row for row in rows if row[0] != del_key_name] if len(updated_rows) != len(rows): try: with open(self.data_file, 'w', newline='', encoding='utf-8') as csvfile: writer = csv.writer(csvfile) writer.writerows(updated_rows) print(f"- 訊息欄: Deleted the row with key '{del_key_name}' from csv file.") except Exception as e: print(f" - 錯誤訊息: {str(e)}") except FileNotFoundError: print(" - 錯誤訊息: CSV file not found.") else: print(f"- 訊息欄: Key '{del_key_name}' not found in 'self.data_dicts'.") # 將所有提醒事項重新寫入 try: with open(self.data_file, mode="r", newline="", encoding="utf-8-sig") as f: csv_reader = csv.DictReader(f) # 把每一行轉換成字典 # 將data_file值存入字典 for row in csv_reader: row["Content"] = row["Content"].replace("\\n", "\n") key = row["reminder_userID_time"] self.data_dicts[key] = row except Exception as e: print(f"錯誤 {str(e)}") def get_current_time(self): """獲取當前時間並格式化為 YYYY-MM-DD HH:MM:SS""" now = datetime.now() formatted_time = now.strftime("%Y-%m-%d %H:%M:%S") # 轉為 str 格式 return formatted_time # 發送提醒事項 async def send_reminder(self, key, check_time, current_time, reminder): # 嘗試發送訊息到指定用戶 try: user_id = key.split('_')[0] # 假設 userID 是從 key 中分割的 user = await self.bot.fetch_user(int(user_id)) # 假設用戶ID是整數 check_time = datetime.strptime(check_time, "%Y-%m-%d %H:%M") # 轉為 datetime 格式 embed = discord.Embed( title = reminder["Title"], description = reminder["Content"], timestamp = check_time, color = discord.Color.blue() ) channel = self.bot.get_channel(self.channel_id) if channel: # 若有檔案 if reminder["Files"] != "None": try: if isinstance(reminder["Files"], str): # 確保檔案名稱以 UTF-8 處理 file_names = reminder["Files"].strip('"').split(', ') # 去掉雙引號並分割檔案名稱 # 轉義檔案名稱中的 Markdown 字符 escaped_file_names = [discord.utils.escape_markdown(name) for name in file_names] file_names_str = "\n".join(escaped_file_names) # 將檔案名稱列表轉為字串 embed.description += f"\n\n**Files**:\n{file_names_str}" # 在內文下方顯示檔案名稱 files_to_send = [] user_folder = os.path.join(self.data_folder, key) # 檢查檔案是否存在於指定的資料夾中 if os.path.exists(user_folder): for file_name in file_names: file_path = os.path.join(user_folder, file_name.strip()) # 去除檔案名多餘的空格 if os.path.exists(file_path): files_to_send.append(discord.File(file_path, filename=file_name)) # 創建 File 對象 else: print(f"File {file_path} does not exist.") else: print(f"找不到 user_folder: {user_folder}") # 發送帶檔案的消息 if files_to_send: await channel.send(embed=embed) await channel.send(files=files_to_send) else: await channel.send(embed=embed) else: print(f"Invalid 'files' value for reminder {key}: {reminder['Files']}") except Exception as e: print(f" - 錯誤訊息: Error processing files for reminder {key}: {e}") await channel.send(embed=embed) else: await channel.send(embed=embed) # 將提醒時間更新 change_time = self.data_dicts[key]["First Reminder Time"] change_time = datetime.strptime(change_time, "%Y-%m-%d %H:%M") append_hour = self.data_dicts[key]["Interval"] change_time = change_time + timedelta(hours=int(append_hour)) self.data_dicts[key]['First Reminder Time'] = change_time.strftime("%Y-%m-%d %H:%M") print(f" - 訊息欄: {current_time} - 已將 {user.name} 填寫的提醒事項發送至 頻道({channel})") # 若重複次數小於0，則加入待刪除名單(self.wait_for_del將在下次檢查時刪除內容) self.data_dicts[key]["Repeat Count"] = int(self.data_dicts[key]["Repeat Count"]) - 1 if int(reminder["Repeat Count"]) <= 0: self.wait_for_del.append(key) else: print(f" - 訊息欄: {channel} 找不到") except discord.NotFound: print(f" - 錯誤訊息: 無法找到用戶 ID: {user_id}") except discord.Forbidden: print(f" - 錯誤訊息: 無法向 {user_id} 發送訊息。") # 任務：每分鐘檢查一次時間是否達到指定的發送時間 @tasks.loop(minutes=1) # 每分鐘檢查一次 async def check_reminders(self): """每分鐘檢查一次是否有時間匹配的提醒""" self.load_reminders() # 重新讀取提醒資料 current_time = self.get_current_time() await self.sync_with_sql() # 雙向同步資料庫 print(f"\nself.wait_for_del: {self.wait_for_del}") if self.wait_for_del: for del_key in self.wait_for_del: self.del_reminders(del_key) self.wait_for_del = [] Log_test = ( f"-----\n" f" - Log_test: 現在時間: {current_time}\n" f" - Log_test: 提醒事項: {self.data_dicts}\n" ) print(Log_test) for key, reminder in self.data_dicts.items(): reminder_time = reminder["First Reminder Time"] check_time = datetime.strptime(current_time, "%Y-%m-%d %H:%M:%S") # 轉為 datetime 格式 check_time = check_time.strftime("%Y-%m-%d %H:%M") # 轉為 str 格式 if check_time == reminder_time: await self.send_reminder(key, check_time, current_time, reminder) @commands.Cog.listener() async def on_ready(self): """當 bot 登入後啟動背景任務""" # print(f"已登入為 {self.bot.user}") # 背景任務會在 on_ready 被觸發後開始運行 if not self.check_reminders.is_running(): self.check_reminders.start() await self.sync_with_sql() async def setup(bot): await bot.add_cog(ReminderOutput(bot))