check_missing_reports

Instructions

Implementation Reference

• mcp_server/weekly_report_server.py:68-81 (handler)

  The core handler function for the 'check_missing_reports' tool. Decorated with @mcp.tool() for automatic registration in the FastMCP server. It calls the helper get_report_data() to fetch submission status from Google Sheets, computes missing reports based on NAME_LIST, and returns a formatted string listing missing submitters or '無' if all submitted.

  @mcp.tool()
  def check_missing_reports() -> str:
      """Check who hasn't submitted their weekly reports yet"""
      report_data = get_report_data()
      
      # Get names of people who haven't submitted
      missing_names = [name for name in NAME_LIST if not report_data[name]["submitted"]]
      
      # Format the response
      if missing_names:
          return f"本週未寫週報名單：{', '.join(missing_names)}"
      else:
          return "本週未寫週報名單：無"

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

  Key helper function that retrieves and processes weekly report submission data from a Google Sheet named '週報'. Initializes status for all members in NAME_LIST, parses recent rows for timestamps and names, determines if submissions are recent (within ~6.5 days), and populates detailed status dictionary used by the tool handler.

  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:11-11 (helper)

  Constant list of team member names expected to submit weekly reports, used by get_report_data() and the handler to check for missing submissions.

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

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

  The @mcp.tool() decorator registers the check_missing_reports function as an MCP tool in the FastMCP server instance.

  @mcp.tool()