sales_summary
Generate sales summary statistics by aggregating bakery transaction data across date ranges, products, departments, or payment methods.
Instructions
Get sales summary statistics. Aggregate sales data by date range, product, department, or payment method.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| start_date | No | Start date (YYYY-MM-DD format). Optional. | |
| end_date | No | End date (YYYY-MM-DD format). Optional. | |
| group_by | No | How to group the results. Optional. | |
| department_id | No | Filter by department ID. Optional. | |
| limit | No | Maximum number of results. Default: 100. |
Input Schema (JSON Schema)
{
"properties": {
"department_id": {
"description": "Filter by department ID. Optional.",
"type": "number"
},
"end_date": {
"description": "End date (YYYY-MM-DD format). Optional.",
"type": "string"
},
"group_by": {
"description": "How to group the results. Optional.",
"enum": [
"product",
"department",
"payment_method",
"date",
"month"
],
"type": "string"
},
"limit": {
"description": "Maximum number of results. Default: 100.",
"type": "number"
},
"start_date": {
"description": "Start date (YYYY-MM-DD format). Optional.",
"type": "string"
}
},
"type": "object"
}
Implementation Reference
- src/bakery_data_mcp/server.py:353-459 (handler)The handler for the 'sales_summary' tool. Constructs SQL queries dynamically based on 'group_by' ('product', 'department', 'payment_method', 'date', 'month') or overall summary, applies filters for date range and department, executes the query using the database connection, fetches results, and returns them as JSON-formatted text content.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) )]
- The input schema for the 'sales_summary' tool, defining optional parameters for date range, grouping method, 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)The registration of the 'sales_summary' tool in the list_tools() function, which returns a list of Tool objects advertised to MCP clients. Includes 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." } } } ),