Weekly Report Checker

Instructions

Implementation Reference

• mcp_server/weekly_report_server.py:82-94 (handler)

  The core handler function for the 'check_person_report' tool. It fetches report data from Google Sheets via get_report_data() and checks the submission status for the specified person, returning a formatted status message.

  @mcp.tool()
  def check_person_report(name: str) -> str:
      """Check if a specific person has submitted their weekly report"""
      report_data = get_report_data()
      
      if name not in report_data:
          return f"找不到 {name} 的資料。請確認名字是否正確。"
      
      if report_data[name]["submitted"]:
          return f"{name} 已於 {report_data[name]['timestamp']} 提交週報（{report_data[name]['days_ago']} 天前）。\n內容摘要：{report_data[name]['content'][:100]}..."
      else:
          return f"{name} 尚未提交本週週報。"

• mcp_server/weekly_report_server.py:17-66 (helper)

  Key helper function that connects to Google Sheets, fetches weekly report data for predefined list of names (NAME_LIST), processes timestamps and content, and determines submission status within the last ~6-7 days.

  def get_report_data() -> Dict[str, Dict]:
      """Helper function to get report data from Google Sheets"""
      # Connect to Google Sheets
      sa = gspread.service_account(filename=SERVICE_ACCOUNT_FILE)
      sh = sa.open("週報")
      wks = sh.worksheet("週報")
      
      # Get current time
      current_time = datetime.datetime.now()
      
      # Dictionary to store report data for each person
      report_data = {name: {
          "submitted": False,
          "timestamp": None,
          "content": None,
          "days_ago": None
      } for name in NAME_LIST}
      
      # Check each row in the sheet
      for i in range(2, 15):  # Assuming data starts from row 2 and goes to row 14
          try:
              row = wks.get(f"A{i}:F{i}")
              if not row or not row[0][0]:  # Skip empty rows
                  continue
                  
              # Parse the timestamp from the sheet
              item_time = datetime.datetime.strptime(row[0][0], '%m/%d/%Y %H:%M:%S')
              name = row[0][2]  # Assuming name is in column C
              
              # Skip if the name is not in our list
              if name not in report_data:
                  continue
                  
              # Calculate days ago
              delta_sec = (current_time - item_time).total_seconds()
              days_ago = delta_sec / 86400  # Convert seconds to days
              
              # Check if the report was submitted within the last 6 days (518400 seconds + 12 hours buffer)
              if delta_sec < (518400 + 43200):
                  report_data[name] = {
                      "submitted": True,
                      "timestamp": item_time.strftime('%Y-%m-%d %H:%M:%S'),
                      "content": row[0][3] if len(row[0]) > 3 else "No content",  # Assuming content is in column D
                      "days_ago": round(days_ago, 1)
                  }
          except Exception:
              continue
      
      return report_data

• mcp_server/weekly_report_server.py:82-82 (registration)

  The @mcp.tool() decorator registers the check_person_report function as an MCP tool.

  @mcp.tool()

• mcp_server/weekly_report_server.py:10-12 (helper)

  Predefined list of team members whose reports are checked.

  # Define the name lists
  NAME_LIST = ["陳冠宇", "林柏志", "潘班", "董屹煊", "王宇軒", "許圃瑄", "陳冠言", "黃祈緯", "黃渝凌"]