如何开发门店业绩上报管理系统中的销售计划板块？（附架构图+流程图+代码参考）

-- A1. plan 主表
CREATE TABLE `plan` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `period_type` ENUM('year','month','week','day') NOT NULL,
  `period_start` DATE NOT NULL,
  `period_end` DATE NOT NULL,
  `created_by` BIGINT NOT NULL,
  `status` ENUM('draft','pending','approved','active','closed') DEFAULT 'draft',
  `meta` JSON NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- A2. plan_target 明细表
CREATE TABLE `plan_target` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `plan_id` BIGINT NOT NULL,
  `org_id` BIGINT NOT NULL,
  `sku_id` BIGINT NULL,
  `target_amount` DECIMAL(14,2) DEFAULT 0,
  `target_qty` INT DEFAULT 0,
  `commit_amount` DECIMAL(14,2) DEFAULT NULL,
  `status` ENUM('pending','committed','confirmed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`plan_id`),
  INDEX (`org_id`)
);
-- A3. plan_approval 审批记录
CREATE TABLE `plan_approval` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `plan_id` BIGINT NOT NULL,
  `approver_id` BIGINT NOT NULL,
  `action` ENUM('approve','reject','request_change') NOT NULL,
  `comment` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`plan_id`)
);
-- A4. sales_snapshot 每日销售快照
CREATE TABLE `sales_snapshot` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `org_id` BIGINT NOT NULL,
  `sku_id` BIGINT NULL,
  `date` DATE NOT NULL,
  `amount` DECIMAL(14,2) DEFAULT 0,
  `qty` INT DEFAULT 0,
  INDEX (`org_id`,`date`)
);
-- A5. plan_log 变更日志
CREATE TABLE `plan_log` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `plan_id` BIGINT NOT NULL,
  `operator_id` BIGINT NOT NULL,
  `field` VARCHAR(255),
  `old_value` TEXT,
  `new_value` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`plan_id`)
);

// B1. 简单 Express app（app.js）
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql2/promise');
const app = express();
app.use(bodyParser.json());
// 连接池（请替换配置）
const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'password',
  database: 'retail',
  waitForConnections: true,
  connectionLimit: 10
});
// 创建计划
app.post('/api/plans', async (req, res) => {
  const { title, period_type, period_start, period_end, created_by, meta } = req.body;
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    const [result] = await conn.query(
      'INSERT INTO `plan` (title, period_type, period_start, period_end, created_by, meta, status) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [title, period_type, period_start, period_end, created_by, JSON.stringify(meta || {}), 'draft']
    );
    const planId = result.insertId;
    // 可根据规则自动拆解到 plan_target（此处示例略）
    await conn.commit();
    res.json({ success: true, planId });
  } catch (err) {
    await conn.rollback();
    console.error(err);
    res.status(500).json({ success: false, error: err.message });
  } finally {
    conn.release();
  }
});
// 门店提交承诺值
app.post('/api/plans/:planId/commit', async (req, res) => {
  const planId = req.params.planId;
  const { orgId, commitAmount } = req.body;
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    // 更新目标承诺值，若不存在则插入
    const [rows] = await conn.query('SELECT id FROM plan_target WHERE plan_id=? AND org_id=?', [planId, orgId]);
    if (rows.length) {
      await conn.query('UPDATE plan_target SET commit_amount=?, status=? WHERE id=?', [commitAmount, 'committed', rows[0].id]);
    } else {
      await conn.query('INSERT INTO plan_target (plan_id, org_id, commit_amount, status) VALUES (?, ?, ?, ?)', [planId, orgId, commitAmount, 'committed']);
    }
    // 写日志
    await conn.query('INSERT INTO plan_log (plan_id, operator_id, field, old_value, new_value) VALUES (?, ?, ?, ?, ?)', [planId, /*operator=*/0, 'commit_amount', null, commitAmount.toString()]);
    await conn.commit();
    res.json({ success: true });
  } catch (err) {
    await conn.rollback();
    console.error(err);
    res.status(500).json({ success: false, error: err.message });
  } finally {
    conn.release();
  }
});
// 获取门店当前完成率（示例接口，口径为当天累计/承诺值）
app.get('/api/plans/:planId/orgs/:orgId/progress', async (req, res) => {
  const { planId, orgId } = req.params;
  const conn = await pool.getConnection();
  try {
    // 读取承诺值
    const [trows] = await conn.query('SELECT commit_amount FROM plan_target WHERE plan_id=? AND org_id=?', [planId, orgId]);
    const commitAmount = trows.length ? parseFloat(trows[0].commit_amount || 0) : 0;
    // 读取本期内的销售累计（示例以 plan.period 为月度）
    const [planRows] = await conn.query('SELECT period_start, period_end FROM `plan` WHERE id=?', [planId]);
    if (!planRows.length) return res.status(404).json({ error: 'plan not found' });
    const { period_start, period_end } = planRows[0];
    const [srows] = await conn.query('SELECT SUM(amount) as total FROM sales_snapshot WHERE org_id=? AND date BETWEEN ? AND ?', [orgId, period_start, period_end]);
    const total = srows.length ? parseFloat(srows[0].total || 0) : 0;
    const progress = commitAmount > 0 ? total / commitAmount : null;
    res.json({ commitAmount, total, progress });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  } finally {
    conn.release();
  }
});
app.listen(3000, () => console.log('Plan service listening on 3000'));

// C1. CommitForm.jsx
import React, { useState, useEffect } from 'react';
import axios from 'axios';
export default function CommitForm({ planId, orgId }) {
  const [commit, setCommit] = useState('');
  const [reference, setReference] = useState(null);
  useEffect(() => {
    // 获取参考值（例如历史占比自动填充）
    axios.get(`/api/plans/${planId}/orgs/${orgId}/progress`).then(res => {
      setReference(res.data.commitAmount || 0);
      setCommit(res.data.commitAmount ? res.data.commitAmount.toString() : '');
    }).catch(()=>{});
  }, [planId, orgId]);
  const submit = async () => {
    await axios.post(`/api/plans/${planId}/commit`, { orgId, commitAmount: parseFloat(commit) });
    alert('提交成功');
  };
  return (
    <div style={{maxWidth:500}}>
      <h3>提交月度承诺</h3>
      <div>参考值（历史/下发）：{reference ?? '-'}</div>
      <div>
        <label>承诺金额（元）</label>
        <input type="number" value={commit} onChange={e=>setCommit(e.target.value)} />
      </div>
      <button onClick={submit}>提交承诺</button>
    </div>
  );
}

// D1. cron-job.js
const cron = require('node-cron');
const axios = require('axios');
// 每天晚上 22:00 检查当天达成率低于阈值的门店并发出预警
cron.schedule('0 22 * * *', async () => {
  console.log('running daily check');
  // 获取需要检查的 plans（示例）
  const plans = await axios.get('http://localhost:3000/api/plans?active=true').then(r=>r.data).catch(()=>[]);
  for (const plan of plans) {
    // 假设我们有 API 可以返回本计划下未达标门店列表
    const resp = await axios.get(`http://localhost:3000/api/plans/${plan.id}/underdelivery?threshold=0.8`);
    const under = resp.data || [];
    for (const o of under) {
      // 发通知（示例：调用企业微信/消息服务）
      await axios.post('http://notification-service/send', { to: o.managerId, title: '目标告警', body: `门店 ${o.orgName} 本期达成率 ${o.progress*100}%` });
    }
  }
});

flowchart LR
  A[总部计划中心] -->|下发| B[计划服务（Plan Service）]
  B --> C[审批服务（Approval）]
  C --> D[消息服务（Notification）]
  B --> E[数据仓库/OLAP]
  F[门店端APP/PC] -->|承诺/提交| B
  G[销售系统/POS] -->|日销售快照| H[Sales Snapshot Collector]
  H --> E
  E -->|报表| I[BI/复盘报表]
  D --> F