Bakery Data MCP Server

Instructions

Implementation Reference

• src/bakery_data_mcp/server.py:353-459 (handler)

  Handler for the 'sales_summary' tool. Constructs dynamic SQL queries based on the 'group_by' parameter (product, department, payment_method, date, month, or default overall summary), applies date and department filters, executes the query on the transactions table (with joins as needed), and returns aggregated sales statistics as JSON.
  elif name == "sales_summary": group_by = arguments.get("group_by") # Base query if group_by == "product": query = """ SELECT product_code, product_name, COUNT(*) as transaction_count, SUM(quantity) as total_quantity, SUM(amount) as total_revenue FROM transactions WHERE 1=1 """ group_clause = " GROUP BY product_code, product_name ORDER BY total_revenue DESC" elif group_by == "department": query = """ SELECT d.department_id, d.department_name, COUNT(*) as transaction_count, SUM(t.quantity) as total_quantity, SUM(t.amount) as total_revenue FROM transactions t JOIN products p ON t.product_code = p.plu_code JOIN departments d ON p.department_id = d.department_id WHERE 1=1 """ group_clause = " GROUP BY d.department_id, d.department_name ORDER BY total_revenue DESC" elif group_by == "payment_method": query = """ SELECT payment_method, COUNT(*) as transaction_count, SUM(quantity) as total_quantity, SUM(amount) as total_revenue FROM transactions WHERE 1=1 """ group_clause = " GROUP BY payment_method ORDER BY total_revenue DESC" elif group_by == "date": query = """ SELECT DATE(datetime) as date, COUNT(*) as transaction_count, SUM(quantity) as total_quantity, SUM(amount) as total_revenue FROM transactions WHERE 1=1 """ group_clause = " GROUP BY DATE(datetime) ORDER BY date" elif group_by == "month": query = """ SELECT strftime('%Y-%m', datetime) as month, COUNT(*) as transaction_count, SUM(quantity) as total_quantity, SUM(amount) as total_revenue FROM transactions WHERE 1=1 """ group_clause = " GROUP BY strftime('%Y-%m', datetime) ORDER BY month" else: # Overall summary query = """ SELECT COUNT(*) as transaction_count, COUNT(DISTINCT transaction_number) as unique_transactions, SUM(quantity) as total_quantity, SUM(amount) as total_revenue, AVG(amount) as avg_transaction_amount, MIN(datetime) as first_transaction, MAX(datetime) as last_transaction FROM transactions WHERE 1=1 """ group_clause = "" params = [] if "start_date" in arguments: query += " AND datetime >= ?" params.append(arguments["start_date"]) if "end_date" in arguments: query += " AND datetime <= ?" params.append(arguments["end_date"] + " 23:59:59") if "department_id" in arguments and group_by != "department": if "JOIN products" not in query: query = query.replace("FROM transactions", "FROM transactions t JOIN products p ON t.product_code = p.plu_code") query = query.replace("WHERE 1=1", "WHERE 1=1") query += " AND p.department_id = ?" params.append(arguments["department_id"]) query += group_clause if group_by: query += " LIMIT ?" params.append(arguments.get("limit", 100)) cursor.execute(query, params) results = cursor.fetchall() return [TextContent( type="text", text=json.dumps(results, ensure_ascii=False, indent=2) )]
• src/bakery_data_mcp/server.py:147-172 (schema)

  Input schema definition for the 'sales_summary' tool, defining parameters for date range, grouping options, department filter, and result limit.
  inputSchema={ "type": "object", "properties": { "start_date": { "type": "string", "description": "Start date (YYYY-MM-DD format). Optional." }, "end_date": { "type": "string", "description": "End date (YYYY-MM-DD format). Optional." }, "group_by": { "type": "string", "enum": ["product", "department", "payment_method", "date", "month"], "description": "How to group the results. Optional." }, "department_id": { "type": "number", "description": "Filter by department ID. Optional." }, "limit": { "type": "number", "description": "Maximum number of results. Default: 100." } } }
• src/bakery_data_mcp/server.py:144-173 (registration)

  Registration of the 'sales_summary' tool in the list_tools() function, including name, description, and input schema.
  Tool( name="sales_summary", description="Get sales summary statistics. Aggregate sales data by date range, product, department, or payment method.", inputSchema={ "type": "object", "properties": { "start_date": { "type": "string", "description": "Start date (YYYY-MM-DD format). Optional." }, "end_date": { "type": "string", "description": "End date (YYYY-MM-DD format). Optional." }, "group_by": { "type": "string", "enum": ["product", "department", "payment_method", "date", "month"], "description": "How to group the results. Optional." }, "department_id": { "type": "number", "description": "Filter by department ID. Optional." }, "limit": { "type": "number", "description": "Maximum number of results. Default: 100." } } } ),