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

-- 商品类别（树形）
CREATE TABLE product_categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  parent_id INTEGER REFERENCES product_categories(id) ON DELETE SET NULL,
  code VARCHAR(100),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);
-- 商品主表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(100) UNIQUE NOT NULL,
  barcode VARCHAR(64) UNIQUE,
  name VARCHAR(500) NOT NULL,
  category_id INTEGER REFERENCES product_categories(id),
  brand VARCHAR(200),
  spec VARCHAR(200),
  unit VARCHAR(50) DEFAULT '件',
  price NUMERIC(12,2),
  cost NUMERIC(12,2),
  status VARCHAR(20) DEFAULT 'active',
  attributes JSONB,
  main_image_url TEXT,
  created_by INTEGER,
  updated_by INTEGER,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);
-- 商品图片
CREATE TABLE product_images (
  id SERIAL PRIMARY KEY,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  is_main BOOLEAN DEFAULT FALSE,
  sort_order INTEGER DEFAULT 0
);
-- 历史记录（变更审计）
CREATE TABLE product_history (
  id SERIAL PRIMARY KEY,
  product_id INTEGER,
  change_type VARCHAR(50),
  change_by INTEGER,
  change_at TIMESTAMP DEFAULT now(),
  before JSONB,
  after JSONB
);
-- 临时导入表（预检）
CREATE TABLE product_import_jobs (
  id SERIAL PRIMARY KEY,
  filename VARCHAR(255),
  total_rows INTEGER,
  success_rows INTEGER,
  failed_rows INTEGER,
  status VARCHAR(50) DEFAULT 'pending',
  created_by INTEGER,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);
CREATE TABLE product_import_rows (
  id SERIAL PRIMARY KEY,
  job_id INTEGER REFERENCES product_import_jobs(id) ON DELETE CASCADE,
  row_index INTEGER,
  raw_data JSONB,
  status VARCHAR(50), -- pending/ok/error
  error_msg TEXT
);
-- 索引建议
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_name_gin ON products USING gin (to_tsvector('simple', name));

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn } from 'typeorm';
@Entity('products')
export class Product {
  @PrimaryGeneratedColumn()
  id: number;
  @Column({ unique: true })
  sku: string;
  @Column({ unique: true, nullable: true })
  barcode: string;
  @Column()
  name: string;
  @Column({ nullable: true })
  brand: string;
  @Column({ nullable: true })
  spec: string;
  @Column('numeric', { nullable: true })
  price: number;
  @Column('numeric', { nullable: true })
  cost: number;
  @Column({ type: 'jsonb', nullable: true })
  attributes: any;
  @Column({ nullable: true })
  main_image_url: string;
  @CreateDateColumn()
  created_at: Date;
  @UpdateDateColumn()
  updated_at: Date;
}

import { getRepository } from 'typeorm';
import { Product } from './product.entity';
import { publishToMQ } from './mq';
import { saveHistory } from './history.service';
export class ProductService {
  private repo = getRepository(Product);
  async createProduct(payload: any, userId: number) {
    if (!payload.sku && !payload.barcode) {
      throw new Error('SKU 或 条码 必填其一');
    }
    if (payload.barcode) {
      const exists = await this.repo.findOne({ where: { barcode: payload.barcode } });
      if (exists) {
        throw new Error(`条码 ${payload.barcode} 已存在（ID=${exists.id}）`);
      }
    }
    if (payload.sku) {
      const existsSku = await this.repo.findOne({ where: { sku: payload.sku } });
      if (existsSku) {
        throw new Error(`SKU ${payload.sku} 已存在（ID=${existsSku.id}）`);
      }
    }
    const product = this.repo.create(payload);
    const saved = await this.repo.save(product);
    await saveHistory({
      product_id: saved.id,
      change_type: 'create',
      change_by: userId,
      before: null,
      after: saved
    });
    publishToMQ('product.created', { productId: saved.id });
    return saved;
  }
  async updateProduct(id: number, payload: any, userId: number) {
    const existing = await this.repo.findOneOrFail(id);
    const before = { ...existing };
    if (payload.barcode && payload.barcode !== existing.barcode) {
      const e = await this.repo.findOne({ where: { barcode: payload.barcode } });
      if (e) throw new Error('条码冲突');
    }
    Object.assign(existing, payload);
    const saved = await this.repo.save(existing);
    await saveHistory({
      product_id: saved.id,
      change_type: 'update',
      change_by: userId,
      before,
      after: saved
    });
    publishToMQ('product.updated', { productId: saved.id });
    return saved;
  }
  async findByBarcodeOrSku(code: string) {
    const p = await this.repo.findOne({ where: [{ barcode: code }, { sku: code }] });
    return p;
  }
}

import express from 'express';
import { ProductService } from './product.service';
const router = express.Router();
const svc = new ProductService();
router.post('/', async (req, res) => {
  try {
    const userId = req.user?.id || 0;
    const product = await svc.createProduct(req.body, userId);
    res.status(201).json(product);
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
});
router.put('/:id', async (req, res) => {
  try {
    const userId = req.user?.id || 0;
    const product = await svc.updateProduct(Number(req.params.id), req.body, userId);
    res.json(product);
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
});
router.get('/search', async (req, res) => {
  try {
    const q = req.query.q as string;
    // 简单模糊搜索示例（实际用 ES）
    const result = await svc.findByBarcodeOrSku(q);
    res.json(result);
  } catch (err) {
    res.status(500).json({ message: err.message });
  }
});
export default router;

export async function publishToMQ(topic: string, payload: any) {
  // 示例：实际接入 RabbitMQ/Kafka
  // const channel = await mq.getChannel();
  // channel.publish(exchange, routingKey, Buffer.from(JSON.stringify(payload)));
  console.log('MQ Publish ->', topic, payload);
}

import { getManager } from 'typeorm';
export async function saveHistory(entry: {
  product_id: number;
  change_type: string;
  change_by: number;
  before: any;
  after: any;
}) {
  const em = getManager();
  await em.query(
    `INSERT INTO product_history (product_id, change_type, change_by, before, after) VALUES ($1, $2, $3, $4, $5)`,
    [entry.product_id, entry.change_type, entry.change_by, JSON.stringify(entry.before), JSON.stringify(entry.after)]
  );
}

import React from 'react';
import { Form, Input, Button, Upload, TreeSelect, InputNumber } from 'antd';
import { UploadOutlined } from '@ant-design/icons';
import axios from 'axios';
const ProductForm = ({ initialValues = {}, onSaved }) => {
  const [form] = Form.useForm();
  const onFinish = async (values: any) => {
    try {
      // 处理图片 URL 等（这里假设上传返回 url）
      const res = await axios.post('/api/products', values);
      onSaved && onSaved(res.data);
    } catch (e) {
      console.error(e);
    }
  };
  return (
    <Form form={form} initialValues={initialValues} onFinish={onFinish} layout="vertical">
      <Form.Item name="sku" label="SKU" rules={[{ required: true, message: '请输入 SKU' }]}>
        <Input />
      </Form.Item>
      <Form.Item name="barcode" label="条码">
        <Input />
      </Form.Item>
      <Form.Item name="name" label="商品名称" rules={[{ required: true }]}>
        <Input />
      </Form.Item>
      <Form.Item name="category_id" label="类目" rules={[{ required: true }]}>
        <TreeSelect treeData={/* 从接口获取类目树 */[]} />
      </Form.Item>
      <Form.Item name="price" label="价格">
        <InputNumber min={0} />
      </Form.Item>
      <Form.Item name="main_image" label="主图">
        <Upload name="file" action="/api/upload" listType="picture">
          <Button icon={<UploadOutlined />}>上传主图</Button>
        </Upload>
      </Form.Item>
      <Form.Item>
        <Button type="primary" htmlType="submit">保存</Button>
      </Form.Item>
    </Form>
  );
};
export default ProductForm;

// 使用 exceljs 或 xlsx 解析文件，生成 product_import_rows
// 伪代码：
async function handleFileUpload(file, userId) {
  const rows = parseExcel(file);
  const job = await createImportJob({ filename: file.name, total_rows: rows.length, created_by: userId });
  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const errors = validateRow(row); // 检查必填、条码格式、类目是否存在等
    await insertImportRow(job.id, i+1, row, errors.length ? 'error' : 'ok', errors.join(';'));
  }
  // 触发异步任务进行预检（或实时同步处理）
  return job;
}

// 门店扫码或输入条码，前端调用：
axios.get('/api/products/search?q=BARCODE_OR_SKU')
  .then(res => {
    if (res.data) {
      // 使用 res.data.product_id 进行上报
    } else {
      // 提示临时商品流程
    }
  });

import { getRepository } from 'typeorm';
import { Product } from './product.entity';
import { Parser } from 'json2csv';
import fs from 'fs';
export async function exportProductsCSV(filePath: string) {
  const repo = getRepository(Product);
  const products = await repo.find();
  const fields = ['id','sku','barcode','name','brand','spec','price','cost','category_id'];
  const parser = new Parser({ fields });
  const csv = parser.parse(products);
  fs.writeFileSync(filePath, csv);
}