Magento 2 MCP Server

Instructions

Implementation Reference

• mcp-server.js:1015-1144 (handler)

  Inline async handler function implementing the core logic: parses date range, builds API query, fetches all orders via pagination helper, filters by status/country, processes order items to compute totals (orders, items, quantity, revenue), tracks top products by SKU, formats and returns JSON response.

  async ({ date_range, status, country }) => {
    try {
      // Parse the date range expression
      const dateRange = parseDateExpression(date_range);
      
      // Build the search criteria for the date range
      let searchCriteria = buildDateRangeFilter('created_at', dateRange.startDate, dateRange.endDate);
      
      // Add status filter if provided
      if (status) {
        searchCriteria += `&searchCriteria[filter_groups][2][filters][0][field]=status&` +
                          `searchCriteria[filter_groups][2][filters][0][value]=${encodeURIComponent(status)}&` +
                          `searchCriteria[filter_groups][2][filters][0][condition_type]=eq`;
      }
      
      // Fetch all orders using the helper function
      const allOrders = await fetchAllPages('/orders', searchCriteria);
      
      // Filter orders by country if provided
      let filteredOrders = allOrders;
      if (country) {
        // Normalize country input
        const normalizedCountry = normalizeCountry(country);
        
        // Filter orders by country
        filteredOrders = filteredOrders.filter(order => {
          // Check billing address country
          const billingCountry = order.billing_address?.country_id;
          
          // Check shipping address country
          const shippingCountry = order.extension_attributes?.shipping_assignments?.[0]?.shipping?.address?.country_id;
          
          // Match if either billing or shipping country matches
          return normalizedCountry.includes(billingCountry) || normalizedCountry.includes(shippingCountry);
        });
      }
      
      // Calculate statistics
      let totalOrders = filteredOrders.length;
      let totalOrderItems = 0;
      let totalProductQuantity = 0;
      let totalRevenue = 0;
      let productCounts = {};
      
      // Process each order
      filteredOrders.forEach(order => {
        // Add to total revenue
        totalRevenue += parseFloat(order.grand_total || 0);
        
        // Process order items
        if (order.items && Array.isArray(order.items)) {
          // Count total order items (order lines)
          totalOrderItems += order.items.length;
          
          // Process each item
          order.items.forEach(item => {
            // Add to total product quantity
            const quantity = parseFloat(item.qty_ordered || 0);
            totalProductQuantity += quantity;
            
            // Track product counts by SKU
            const sku = item.sku;
            if (sku) {
              if (!productCounts[sku]) {
                productCounts[sku] = {
                  name: item.name,
                  quantity: 0,
                  revenue: 0
                };
              }
              productCounts[sku].quantity += quantity;
              productCounts[sku].revenue += parseFloat(item.row_total || 0);
            }
          });
        }
      });
      
      // Convert product counts to array and sort by quantity
      const topProducts = Object.entries(productCounts)
        .map(([sku, data]) => ({
          sku,
          name: data.name,
          quantity: data.quantity,
          revenue: data.revenue
        }))
        .sort((a, b) => b.quantity - a.quantity)
        .slice(0, 10); // Top 10 products
      
      // Format the response
      const result = {
        query: {
          date_range: dateRange.description,
          status: status || 'All',
          country: country || 'All',
          period: {
            start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
            end_date: format(dateRange.endDate, 'yyyy-MM-dd')
          }
        },
        result: {
          total_orders: totalOrders,
          total_order_items: totalOrderItems,
          total_product_quantity: totalProductQuantity,
          average_products_per_order: totalOrders > 0 ? parseFloat((totalProductQuantity / totalOrders).toFixed(2)) : 0,
          total_revenue: parseFloat(totalRevenue.toFixed(2)),
          average_revenue_per_product: totalProductQuantity > 0 ? parseFloat((totalRevenue / totalProductQuantity).toFixed(2)) : 0,
          top_products: topProducts
        }
      };
      
      return {
        content: [
          {
            type: "text",
            text: JSON.stringify(result, null, 2)
          }
        ]
      };
    } catch (error) {
      return {
        content: [
          {
            type: "text",
            text: `Error fetching product sales: ${error.message}`
          }
        ],
        isError: true
      };
    }
  }

• mcp-server.js:1010-1014 (schema)

  Zod-based input schema defining parameters: date_range (required string for date expressions), status (optional string for order status filter), country (optional string for country filter).

  {
    date_range: z.string().describe("Date range expression (e.g., 'today', 'yesterday', 'last week', 'this month', 'YTD', or a specific date range like '2023-01-01 to 2023-01-31')"),
    status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
    country: z.string().optional().describe("Filter by country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')")
  },

• mcp-server.js:1007-1145 (registration)

  MCP server tool registration call: server.tool('get_product_sales', description, schema, handler). Includes comment '// Tool: Get product sales'.

  server.tool(
    "get_product_sales",
    "Get statistics about the quantity of products sold in a given date range",
    {
      date_range: z.string().describe("Date range expression (e.g., 'today', 'yesterday', 'last week', 'this month', 'YTD', or a specific date range like '2023-01-01 to 2023-01-31')"),
      status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
      country: z.string().optional().describe("Filter by country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')")
    },
    async ({ date_range, status, country }) => {
      try {
        // Parse the date range expression
        const dateRange = parseDateExpression(date_range);
        
        // Build the search criteria for the date range
        let searchCriteria = buildDateRangeFilter('created_at', dateRange.startDate, dateRange.endDate);
        
        // Add status filter if provided
        if (status) {
          searchCriteria += `&searchCriteria[filter_groups][2][filters][0][field]=status&` +
                            `searchCriteria[filter_groups][2][filters][0][value]=${encodeURIComponent(status)}&` +
                            `searchCriteria[filter_groups][2][filters][0][condition_type]=eq`;
        }
        
        // Fetch all orders using the helper function
        const allOrders = await fetchAllPages('/orders', searchCriteria);
        
        // Filter orders by country if provided
        let filteredOrders = allOrders;
        if (country) {
          // Normalize country input
          const normalizedCountry = normalizeCountry(country);
          
          // Filter orders by country
          filteredOrders = filteredOrders.filter(order => {
            // Check billing address country
            const billingCountry = order.billing_address?.country_id;
            
            // Check shipping address country
            const shippingCountry = order.extension_attributes?.shipping_assignments?.[0]?.shipping?.address?.country_id;
            
            // Match if either billing or shipping country matches
            return normalizedCountry.includes(billingCountry) || normalizedCountry.includes(shippingCountry);
          });
        }
        
        // Calculate statistics
        let totalOrders = filteredOrders.length;
        let totalOrderItems = 0;
        let totalProductQuantity = 0;
        let totalRevenue = 0;
        let productCounts = {};
        
        // Process each order
        filteredOrders.forEach(order => {
          // Add to total revenue
          totalRevenue += parseFloat(order.grand_total || 0);
          
          // Process order items
          if (order.items && Array.isArray(order.items)) {
            // Count total order items (order lines)
            totalOrderItems += order.items.length;
            
            // Process each item
            order.items.forEach(item => {
              // Add to total product quantity
              const quantity = parseFloat(item.qty_ordered || 0);
              totalProductQuantity += quantity;
              
              // Track product counts by SKU
              const sku = item.sku;
              if (sku) {
                if (!productCounts[sku]) {
                  productCounts[sku] = {
                    name: item.name,
                    quantity: 0,
                    revenue: 0
                  };
                }
                productCounts[sku].quantity += quantity;
                productCounts[sku].revenue += parseFloat(item.row_total || 0);
              }
            });
          }
        });
        
        // Convert product counts to array and sort by quantity
        const topProducts = Object.entries(productCounts)
          .map(([sku, data]) => ({
            sku,
            name: data.name,
            quantity: data.quantity,
            revenue: data.revenue
          }))
          .sort((a, b) => b.quantity - a.quantity)
          .slice(0, 10); // Top 10 products
        
        // Format the response
        const result = {
          query: {
            date_range: dateRange.description,
            status: status || 'All',
            country: country || 'All',
            period: {
              start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
              end_date: format(dateRange.endDate, 'yyyy-MM-dd')
            }
          },
          result: {
            total_orders: totalOrders,
            total_order_items: totalOrderItems,
            total_product_quantity: totalProductQuantity,
            average_products_per_order: totalOrders > 0 ? parseFloat((totalProductQuantity / totalOrders).toFixed(2)) : 0,
            total_revenue: parseFloat(totalRevenue.toFixed(2)),
            average_revenue_per_product: totalProductQuantity > 0 ? parseFloat((totalRevenue / totalProductQuantity).toFixed(2)) : 0,
            top_products: topProducts
          }
        };
        
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(result, null, 2)
            }
          ]
        };
      } catch (error) {
        return {
          content: [
            {
              type: "text",
              text: `Error fetching product sales: ${error.message}`
            }
          ],
          isError: true
        };
      }
    }
  );

• mcp-server.js:23-124 (helper)

  parseDateExpression(dateExpression): Parses relative date strings (e.g., 'today', 'last month', '2023-01-01 to 2023-01-31') into {startDate, endDate, description} using date-fns.

  function parseDateExpression(dateExpression) {
    const now = new Date();
    const currentYear = now.getFullYear();
    const currentMonth = now.getMonth();
    const currentDay = now.getDate();
    
    // Normalize the date expression
    const normalizedExpression = dateExpression.toLowerCase().trim();
    
    // Handle relative date expressions
    switch (normalizedExpression) {
      case 'today':
        return {
          startDate: startOfDay(now),
          endDate: endOfDay(now),
          description: 'Today'
        };
      case 'yesterday':
        const yesterday = subDays(now, 1);
        return {
          startDate: startOfDay(yesterday),
          endDate: endOfDay(yesterday),
          description: 'Yesterday'
        };
      case 'this week':
        return {
          startDate: startOfWeek(now, { weekStartsOn: 1 }), // Week starts on Monday
          endDate: endOfDay(now),
          description: 'This week'
        };
      case 'last week':
        const lastWeekStart = subDays(startOfWeek(now, { weekStartsOn: 1 }), 7);
        const lastWeekEnd = subDays(endOfWeek(now, { weekStartsOn: 1 }), 7);
        return {
          startDate: lastWeekStart,
          endDate: lastWeekEnd,
          description: 'Last week'
        };
      case 'this month':
        return {
          startDate: startOfMonth(now),
          endDate: endOfDay(now),
          description: 'This month'
        };
      case 'last month':
        const lastMonth = new Date(currentYear, currentMonth - 1, 1);
        return {
          startDate: startOfMonth(lastMonth),
          endDate: endOfMonth(lastMonth),
          description: 'Last month'
        };
      case 'ytd':
      case 'this ytd':
      case 'this year to date':
      case 'year to date':
        return {
          startDate: startOfYear(now),
          endDate: endOfDay(now),
          description: 'Year to date'
        };
      case 'last year':
        const lastYear = new Date(currentYear - 1, 0, 1);
        return {
          startDate: startOfYear(lastYear),
          endDate: endOfYear(lastYear),
          description: 'Last year'
        };
      default:
        // Try to parse as ISO date or other common formats
        try {
          // Check if it's a single date (not a range)
          const parsedDate = parseISO(normalizedExpression);
          if (isValid(parsedDate)) {
            return {
              startDate: startOfDay(parsedDate),
              endDate: endOfDay(parsedDate),
              description: format(parsedDate, 'yyyy-MM-dd')
            };
          }
          
          // Check if it's a date range in format "YYYY-MM-DD to YYYY-MM-DD"
          const rangeParts = normalizedExpression.split(' to ');
          if (rangeParts.length === 2) {
            const startDate = parseISO(rangeParts[0]);
            const endDate = parseISO(rangeParts[1]);
            
            if (isValid(startDate) && isValid(endDate)) {
              return {
                startDate: startOfDay(startDate),
                endDate: endOfDay(endDate),
                description: `${format(startDate, 'yyyy-MM-dd')} to ${format(endDate, 'yyyy-MM-dd')}`
              };
            }
          }
          
          // If we can't parse it, throw an error
          throw new Error(`Unable to parse date expression: ${dateExpression}`);
        } catch (error) {
          throw new Error(`Invalid date expression: ${dateExpression}. ${error.message}`);
        }
    }
  }

• mcp-server.js:152-256 (helper)

  normalizeCountry(country): Maps common country names/variations to ISO codes (e.g., 'netherlands' -> 'NL') for use in order filtering by billing/shipping address.

  function normalizeCountry(country) {
    // Normalize the country input (handle both country codes and names)
    const countryInput = country.trim().toLowerCase();
    
    // Map of common country names to ISO country codes
    const countryMap = {
      // Common variations for The Netherlands
      'netherlands': 'NL',
      'the netherlands': 'NL',
      'holland': 'NL',
      'nl': 'NL',
      
      // Common variations for United States
      'united states': 'US',
      'usa': 'US',
      'us': 'US',
      'america': 'US',
      
      // Common variations for United Kingdom
      'united kingdom': 'GB',
      'uk': 'GB',
      'great britain': 'GB',
      'gb': 'GB',
      'england': 'GB',
      
      // Add more countries as needed
      'canada': 'CA',
      'ca': 'CA',
      
      'australia': 'AU',
      'au': 'AU',
      
      'germany': 'DE',
      'de': 'DE',
      
      'france': 'FR',
      'fr': 'FR',
      
      'italy': 'IT',
      'it': 'IT',
      
      'spain': 'ES',
      'es': 'ES',
      
      'belgium': 'BE',
      'be': 'BE',
      
      'sweden': 'SE',
      'se': 'SE',
      
      'norway': 'NO',
      'no': 'NO',
      
      'denmark': 'DK',
      'dk': 'DK',
      
      'finland': 'FI',
      'fi': 'FI',
      
      'ireland': 'IE',
      'ie': 'IE',
      
      'switzerland': 'CH',
      'ch': 'CH',
      
      'austria': 'AT',
      'at': 'AT',
      
      'portugal': 'PT',
      'pt': 'PT',
      
      'greece': 'GR',
      'gr': 'GR',
      
      'poland': 'PL',
      'pl': 'PL',
      
      'japan': 'JP',
      'jp': 'JP',
      
      'china': 'CN',
      'cn': 'CN',
      
      'india': 'IN',
      'in': 'IN',
      
      'brazil': 'BR',
      'br': 'BR',
      
      'mexico': 'MX',
      'mx': 'MX',
      
      'south africa': 'ZA',
      'za': 'ZA'
    };
    
    // Check if the input is in our map
    if (countryMap[countryInput]) {
      return [countryMap[countryInput]];
    }
    
    // If it's not in our map, assume it's a country code or name and return as is
    // For a more robust solution, we would validate against a complete list of country codes
    return [countryInput.toUpperCase()];
  }