如何开发一套门店业绩上报管理系统？（附架构图+流程图+代码参考）

+-----------------------------+
|   前端：React / Vue / 小程序  |
|   - 门店上报页面             |
|   - 审核/报表/看板页面       |
|   - 离线上报缓存（ServiceWorker / localStorage） |
+-------------+---------------+
              |
              v
+-------------+---------------+
|   API 网关 / 认证（Nginx+JWT）|
+-------------+---------------+
              |
    +---------+---------+
    |                   |
    v                   v
+---+---+           +---+---+
| 后端  |           | 报表服务 |
| (Node/Express 或 SpringBoot) |  (ECharts / Grafana) |
+---+---+           +---+---+
    |                   |
    v                   v
+-----------------------------+
|   主库：PostgreSQL / MySQL  |
|   - store, product, sales   |
+-----------------------------+
    |
    v
+-----------------------------+
| 数据仓/ETL (Airflow/Kafka)  |
| OLAP: ClickHouse / Snowflake|
+-----------------------------+

-- 门店表
CREATE TABLE store (
  id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  region VARCHAR(100),
  address TEXT,
  area_sq_m INT, -- 店铺面积
  created_at TIMESTAMP DEFAULT now()
);
-- 商品表
CREATE TABLE product (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  category VARCHAR(100),
  price NUMERIC(12,2),
  cost NUMERIC(12,2),
  created_at TIMESTAMP DEFAULT now()
);
-- 销售日报（门店每日明细，核心数据源）
CREATE TABLE sales_daily (
  id SERIAL PRIMARY KEY,
  store_id INT REFERENCES store(id),
  product_id INT REFERENCES product(id),
  sales_date DATE NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  sales_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
  gross_profit NUMERIC(14,2) DEFAULT 0,
  sales_channel VARCHAR(50), -- POS/外卖/电商等
  created_by VARCHAR(50),
  created_at TIMESTAMP DEFAULT now(),
  audited BOOLEAN DEFAULT FALSE,
  audit_by VARCHAR(50),
  audit_at TIMESTAMP
);
-- 销售目标（计划）
CREATE TABLE sales_plan (
  id SERIAL PRIMARY KEY,
  store_id INT REFERENCES store(id),
  product_id INT REFERENCES product(id),
  period_start DATE,
  period_end DATE,
  target_amount NUMERIC(14,2),
  target_quantity INT,
  created_at TIMESTAMP DEFAULT now()
);
-- 上报记录（审计日志）
CREATE TABLE report_audit_log (
  id SERIAL PRIMARY KEY,
  sales_daily_id INT REFERENCES sales_daily(id),
  action VARCHAR(50), -- create/update/submit/audit/reject
  actor VARCHAR(50),
  message TEXT,
  created_at TIMESTAMP DEFAULT now()
);

// Express 路由示例
const express = require('express');
const router = express.Router();
const { SalesDaily } = require('../models');
router.post('/sales/report', async (req, res) => {
  const payload = req.body; // store_id, sales_date, items: [{sku, qty, amount}]
  // 简化：逐条插入
  const results = [];
  for (const item of payload.items) {
    const pd = await Product.findOne({ where: { sku: item.sku } });
    const row = await SalesDaily.create({
      store_id: payload.store_id,
      product_id: pd.id,
      sales_date: payload.sales_date,
      quantity: item.qty,
      sales_amount: item.amount,
      gross_profit: (item.amount - pd.cost * item.qty),
      created_by: req.user.username
    });
    results.push(row);
  }
  res.json({ success: true, data: results });
});

// React 示例（简化）
function DailyReportForm({storeId}) {
  const [date, setDate] = useState(new Date().toISOString().slice(0,10));
  const [items, setItems] = useState([{sku:'', qty:1, amount:0}]);
  const submit = async () => {
    await axios.post('/api/sales/report', {store_id: storeId, sales_date: date, items});
    alert('提交成功');
  };
  return (<div>...表单...<button onClick={submit}>提交</button></div>);
}

SELECT s.id, s.name,
       SUM(sd.sales_amount) as month_sales,
       SUM(sd.gross_profit) as month_profit
FROM store s
JOIN sales_daily sd ON sd.store_id = s.id
WHERE sd.sales_date BETWEEN '2025-07-01' AND '2025-07-31'
GROUP BY s.id, s.name
ORDER BY month_sales DESC
LIMIT 100;

// 假设后端返回 [{name, month_sales}]
<ECharts option={{
  xAxis: { type: 'category', data: data.map(d=>d.name)},
  yAxis: { type: 'value' },
  series: [{ type: 'bar', data: data.map(d=>d.month_sales) }]
}} />

INSERT INTO sales_plan (store_id, product_id, period_start, period_end, target_amount, target_quantity)
SELECT s.id, p.id, '2025-08-01', '2025-08-31',
       SUM(sd.sales_amount) * 1.08 AS target_amount, SUM(sd.quantity) * 1.05 as target_qty
FROM store s
CROSS JOIN product p
LEFT JOIN sales_daily sd ON sd.store_id = s.id AND sd.product_id = p.id AND sd.sales_date BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY s.id, p.id;

CREATE TABLE sales_hourly (
  id SERIAL PRIMARY KEY,
  sales_daily_id INT REFERENCES sales_daily(id), -- 关联总条目
  hour INT, -- 0-23
  qty INT,
  amount NUMERIC(14,2)
);

// 使用唯一键 (store_id, product_id, sales_date) 做幂等插入/更新
const upsertSales = async ({store_id, product_id, sales_date, qty, amount, user}) => {
  const sql = `
    INSERT INTO sales_daily (store_id, product_id, sales_date, quantity, sales_amount, created_by)
    VALUES ($1,$2,$3,$4,$5,$6)
    ON CONFLICT (store_id, product_id, sales_date)
    DO UPDATE SET quantity = EXCLUDED.quantity, sales_amount = EXCLUDED.sales_amount, created_by = EXCLUDED.created_by, created_at = now()
    RETURNING *;
  `;
  const res = await db.query(sql, [store_id, product_id, sales_date, qty, amount, user]);
  return res.rows[0];
};