PSI进销存系统智能报表与经营分析

发布时间：2025-06-23 | 分类：数据分析

引言

进销存系统积累了大量的业务数据，如何从这些数据中提取有价值的信息，支撑企业经营决策，是报表分析模块的核心目标。本文将介绍智能报表与经营分析系统的设计与实现。

报表系统架构

整体报表系统架构：

指标引擎

统一指标定义与计算：

// 指标引擎
class MetricEngine {
  constructor() {
    this.metrics = new Map();
    this.registerDefaultMetrics();
  }

  // 注册指标
  registerMetric(config) {
    const metric = {
      id: config.id,
      name: config.name,
      type: config.type,
      category: config.category,
      formula: config.formula,
      aggregation: config.aggregation,
      filters: config.filters || [],
      timeWindow: config.timeWindow,
      dependencies: config.dependencies || []
    };

    this.metrics.set(config.id, metric);
    return this;
  }

  // 注册默认指标
  registerDefaultMetrics() {
    // 销售指标
    this.registerMetric({
      id: 'sales_amount',
      name: '销售额',
      type: 'basic',
      category: 'sales',
      aggregation: 'sum',
      formula: 'order_items.quantity * order_items.price'
    });

    this.registerMetric({
      id: 'sales_count',
      name: '订单数',
      type: 'basic',
      category: 'sales',
      aggregation: 'count',
      formula: 'orders.id'
    });

    this.registerMetric({
      id: 'sales_avg_order_value',
      name: '客单价',
      type: 'derived',
      category: 'sales',
      formula: 'sales_amount / sales_count'
    });

    // 库存指标
    this.registerMetric({
      id: 'inventory_value',
      name: '库存金额',
      type: 'computed',
      category: 'inventory',
      formula: 'SUM(inventory.quantity * product.cost_price)'
    });

    this.registerMetric({
      id: 'inventory_turnover',
      name: '库存周转率',
      type: 'ratio',
      category: 'inventory',
      formula: 'sales_cost / average_inventory'
    });

    // 利润指标
    this.registerMetric({
      id: 'gross_profit',
      name: '毛利润',
      type: 'derived',
      category: 'profit',
      formula: 'sales_amount - sales_cost'
    });

    this.registerMetric({
      id: 'gross_margin',
      name: '毛利率',
      type: 'ratio',
      category: 'profit',
      formula: 'gross_profit / sales_amount * 100'
    });
  }

  // 计算指标
  async calculate(metricId, params) {
    const metric = this.metrics.get(metricId);
    if (!metric) {
      throw new Error(`Metric not found: ${metricId}`);
    }

    const { startDate, endDate, filters, groupBy } = params;
    const query = this.buildQuery(metric, { startDate, endDate, filters, groupBy });
    const result = await this.executeQuery(query);

    return result;
  }

  // 构建 SQL 查询
  buildQuery(metric, params) {
    let sql = 'SELECT ';

    if (params.groupBy) {
      sql += `${params.groupBy}, `;
    }

    sql += `${metric.aggregation}(${metric.formula}) as value `;
    sql += 'FROM orders JOIN order_items ON orders.id = order_items.order_id ';
    sql += `WHERE orders.created_at BETWEEN '${params.startDate}' AND '${params.endDate}'`;

    if (params.filters) {
      for (const filter of params.filters) {
        sql += ` AND ${filter.field} ${filter.operator} ${filter.value}`;
      }
    }

    if (params.groupBy) {
      sql += ` GROUP BY ${params.groupBy}`;
    }

    return sql;
  }
}

多维分析引擎

支持灵活的维度分析与下钻：

// 多维分析引擎
class OLAPEngine {
  constructor() {
    this.dimensions = this.initDimensions();
    this.measures = this.initMeasures();
  }

  // 初始化维度
  initDimensions() {
    return {
      time: {
        name: '时间',
        hierarchy: ['year', 'quarter', 'month', 'week', 'day'],
        attributes: ['year', 'quarter', 'month', 'week', 'day']
      },
      product: {
        name: '商品',
        hierarchy: ['category', 'subcategory', 'product'],
        attributes: ['category', 'subcategory', 'product', 'brand']
      },
      customer: {
        name: '客户',
        hierarchy: ['customer_group', 'customer'],
        attributes: ['customer', 'customer_group', 'region']
      },
      warehouse: {
        name: '仓库',
        hierarchy: ['warehouse', 'location'],
        attributes: ['warehouse', 'location', 'region']
      }
    };
  }

  // 初始化度量
  initMeasures() {
    return [
      { id: 'sales_amount', name: '销售额', aggregator: 'SUM' },
      { id: 'sales_quantity', name: '销售数量', aggregator: 'SUM' },
      { id: 'order_count', name: '订单数', aggregator: 'COUNT' },
      { id: 'profit_amount', name: '利润', aggregator: 'SUM' }
    ];
  }

  // 多维查询
  async query(config) {
    const { measures, dimensions, filters, orderBy, limit } = config;

    // 验证维度
    for (const dim of dimensions) {
      if (!this.dimensions[dim]) {
        throw new Error(`Invalid dimension: ${dim}`);
      }
    }

    const sql = this.buildMDXQuery({ measures, dimensions, filters, orderBy, limit });
    return await this.executeQuery(sql);
  }

  // 构建查询
  buildMDXQuery({ measures, dimensions, filters, orderBy, limit }) {
    let sql = 'SELECT ';

    const selectParts = dimensions.map(d => d);
    for (const m of measures) {
      selectParts.push(`SUM(${m}) as ${m}`);
    }
    sql += selectParts.join(', ');

    const mainTable = this.detectMainTable(dimensions);
    sql += ` FROM ${mainTable}`;

    const joins = this.buildJoins(dimensions, measures);
    sql += joins;

    if (filters && filters.length > 0) {
      sql += ' WHERE ' + filters.map(f =>
        `${f.dimension}.${f.attribute} ${f.operator} '${f.value}'`
      ).join(' AND ');
    }

    sql += ` GROUP BY ${dimensions.join(', ')}`;

    if (orderBy) {
      sql += ` ORDER BY ${orderBy.field} ${orderBy.direction || 'DESC'}`;
    }

    if (limit) {
      sql += ` LIMIT ${limit}`;
    }

    return sql;
  }
}

报表生成器

动态生成各类报表：

// 报表生成器
class ReportGenerator {
  constructor(metricEngine, olapEngine) {
    this.metricEngine = metricEngine;
    this.olapEngine = olapEngine;
    this.templates = new Map();
    this.registerTemplates();
  }

  // 注册报表模板
  registerTemplates() {
    // 销售日报
    this.templates.set('daily_sales', {
      name: '销售日报',
      type: 'table',
      sections: [
        {
          title: '关键指标',
          metrics: ['sales_amount', 'sales_count', 'sales_avg_order_value']
        },
        {
          title: '销售趋势',
          type: 'chart',
          chartType: 'line',
          metrics: ['sales_amount'],
          dimensions: ['day']
        },
        {
          title: '热销商品',
          type: 'table',
          columns: ['product_name', 'quantity', 'amount']
        }
      ]
    });

    // 库存报表
    this.templates.set('inventory_status', {
      name: '库存状态报表',
      type: 'table',
      sections: [
        { title: '库存概览', metrics: ['inventory_value', 'inventory_quantity'] },
        { title: '库存明细', type: 'table', dimensions: ['product', 'warehouse'] }
      ]
    });
  }

  // 生成报表
  async generate(templateId, params) {
    const template = this.templates.get(templateId);
    if (!template) {
      throw new Error(`Template not found: ${templateId}`);
    }

    const result = {
      template: template.name,
      generatedAt: new Date().toISOString(),
      params,
      sections: []
    };

    for (const section of template.sections) {
      const sectionData = await this.generateSection(section, params);
      result.sections.push(sectionData);
    }

    return result;
  }

  // 生成报表区块
  async generateSection(section, params) {
    if (section.metrics) {
      const metrics = await this.metricEngine.calculateMultiple(
        section.metrics,
        params
      );
      return { title: section.title, type: section.type || 'metrics', data: metrics };
    }

    if (section.dimensions) {
      const data = await this.olapEngine.query({
        measures: section.measures || ['sales_amount'],
        dimensions: section.dimensions,
        filters: section.filters || [],
        limit: section.limit || 100
      });
      return { title: section.title, type: section.type || 'table', data };
    }

    return { title: section.title, type: section.type };
  }

  // 导出报表
  async export(report, format) {
    switch (format) {
      case 'excel': return this.exportToExcel(report);
      case 'pdf': return this.exportToPDF(report);
      case 'csv': return this.exportToCSV(report);
      default: throw new Error(`Unsupported format: ${format}`);
    }
  }
}

实时数据大屏

经营数据实时展示：

// 实时数据大屏
class Dashboard {
  constructor() {
    this.widgets = new Map();
    this.refreshInterval = 30000;
    this.timers = [];
  }

  // 添加组件
  addWidget(config) {
    const widget = {
      id: config.id,
      type: config.type,
      metric: config.metric,
      dimensions: config.dimensions,
      refreshInterval: config.refreshInterval || this.refreshInterval,
      config: config.config || {},
      element: null,
      lastData: null
    };

    this.widgets.set(config.id, widget);
    return widget;
  }

  // 启动实时更新
  start() {
    for (const [id, widget] of this.widgets) {
      this.refreshWidget(id);

      const timer = setInterval(() => {
        this.refreshWidget(id);
      }, widget.refreshInterval);

      this.timers.push(timer);
    }
  }

  // 刷新组件
  async refreshWidget(widgetId) {
    const widget = this.widgets.get(widgetId);
    if (!widget) return;

    try {
      const data = await this.fetchData(widget);
      widget.lastData = data;

      if (widget.element) {
        this.updateWidgetUI(widget, data);
      }
    } catch (error) {
      console.error(`Failed to refresh widget ${widgetId}:`, error);
    }
  }

  // 获取数据
  async fetchData(widget) {
    const params = {
      startDate: this.getRelativeDate(widget.config.period),
      endDate: new Date()
    };

    if (widget.metric) {
      return await metricEngine.calculate(widget.metric, params);
    }

    if (widget.dimensions) {
      return await olapEngine.query({
        ...params,
        measures: widget.config.measures,
        dimensions: widget.dimensions,
        limit: widget.config.limit
      });
    }
  }

  // 更新 UI
  updateWidgetUI(widget, data) {
    switch (widget.type) {
      case 'number': this.updateNumberWidget(widget, data); break;
      case 'chart': this.updateChartWidget(widget, data); break;
      case 'table': this.updateTableWidget(widget, data); break;
    }
  }

  // 更新数字组件
  updateNumberWidget(widget, data) {
    const element = widget.element;
    const value = element.querySelector('.value');
    const change = element.querySelector('.change');

    value.textContent = this.formatNumber(data.value);

    if (data.changePercent !== undefined) {
      change.textContent = `${data.changePercent > 0 ? '+' : ''}${data.changePercent}%`;
      change.className = `change ${data.changePercent >= 0 ? 'positive' : 'negative'}`;
    }
  }

  // 格式化数字
  formatNumber(num) {
    if (num >= 100000000) return (num / 100000000).toFixed(2) + '亿';
    if (num >= 10000) return (num / 10000).toFixed(2) + '万';
    return num.toFixed(2);
  }

  // 初始化大屏
  initialize(containerId) {
    const container = document.getElementById(containerId);
    const grid = document.createElement('div');
    grid.className = 'dashboard-grid';

    for (const [id, widget] of this.widgets) {
      const element = document.createElement('div');
      element.className = `dashboard-widget ${widget.type}`;
      element.innerHTML = this.createWidgetHTML(widget);
      grid.appendChild(element);
      widget.element = element;
    }

    container.appendChild(grid);
    this.start();
  }
}

智能预警

自动检测业务异常并提醒：

// 智能预警系统
class IntelligenceAlert {
  constructor() {
    this.rules = this.loadAlertRules();
    this.checkInterval = 300000; // 5分钟检查一次
  }

  // 加载预警规则
  loadAlertRules() {
    return [
      {
        id: 'low_stock',
        name: '库存不足预警',
        type: 'inventory',
        condition: async () => {
          const lowStock = await db.query(`
            SELECT COUNT(*) as count FROM inventory
            WHERE quantity <= min_stock
          `);
          return lowStock[0].count > 0;
        },
        severity: 'warning',
        channels: [' dashboard', 'sms', 'email']
      },
      {
        id: 'overdue_receivable',
        name: '应收账款逾期',
        type: 'finance',
        condition: async () => {
          const overdue = await db.query(`
            SELECT COUNT(*) as count FROM receivables
            WHERE due_date < NOW() AND status != 'paid'
          `);
          return overdue[0].count > 0;
        },
        severity: 'danger',
        channels: ['dashboard', 'email']
      },
      {
        id: 'sales_anomaly',
        name: '销售异常',
        type: 'sales',
        condition: async () => {
          const today = await this.getDailySales();
          const yesterday = await this.getDailySales(-1);
          const changeRate = ((today - yesterday) / yesterday) * 100;
          return Math.abs(changeRate) > 30;
        },
        severity: 'info',
        channels: ['dashboard']
      }
    ];
  }

  // 检查所有预警
  async checkAlerts() {
    const alerts = [];

    for (const rule of this.rules) {
      try {
        const isTriggered = await rule.condition();

        if (isTriggered) {
          alerts.push({
            ruleId: rule.id,
            name: rule.name,
            type: rule.type,
            severity: rule.severity,
            triggeredAt: new Date().toISOString()
          });
        }
      } catch (error) {
        console.error(`Error checking rule ${rule.id}:`, error);
      }
    }

    return alerts;
  }

  // 发送预警通知
  async sendNotification(alert, channels) {
    for (const channel of channels) {
      switch (channel) {
        case 'dashboard':
          await this.sendToDashboard(alert);
          break;
        case 'email':
          await this.sendEmailAlert(alert);
          break;
        case 'sms':
          await this.sendSMSAlert(alert);
          break;
      }
    }
  }

  // 启动预警监控
  startMonitoring() {
    setInterval(async () => {
      const alerts = await this.checkAlerts();

      for (const alert of alerts) {
        const rule = this.rules.find(r => r.id === alert.ruleId);
        await this.sendNotification(alert, rule.channels);
      }
    }, this.checkInterval);
  }
}

最佳实践建议

• 指标统一：建立统一的指标体系，避免数据二义性
• 分层展示：从宏观到微观，满足不同层级决策需求
• 实时性：关键指标实时更新，辅助快速决策
• 可视化：图表选择合理，清晰传达数据信息
• 导出能力：支持多格式导出，方便汇报使用

总结

智能报表与经营分析是企业数据化运营的核心支撑：

• 指标引擎：统一的指标定义与计算
• 多维分析：灵活的分析维度与下钻
• 报表生成：模板化报表快速生成
• 实时大屏：关键数据实时监控展示
• 智能预警：自动检测业务异常

通过智能报表，企业可以更好地洞察业务现状，支撑科学决策。