一、客户管理
销售的起点是客户。客户数据的质量直接影响后续所有业务。
1. 客户主数据
CREATE TABLE bd_customer ( id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_code VARCHAR(30) NOT NULL UNIQUE, customer_name VARCHAR(100) NOT NULL, customer_short VARCHAR(50), -- 分类 customer_type VARCHAR(20), -- GENERAL/KEY/STRATEGIC industry_id BIGINT, region_id BIGINT, -- 联系信息 contact_person VARCHAR(50), phone VARCHAR(30), email VARCHAR(100), address VARCHAR(200), -- 商务信息 credit_limit DECIMAL(18,2) DEFAULT 0, credit_used DECIMAL(18,2) DEFAULT 0, payment_terms VARCHAR(20), price_list_id BIGINT, -- 销售归属 sales_rep_id BIGINT, sales_team_id BIGINT, -- 状态 status VARCHAR(10) DEFAULT 'ACTIVE', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_type (customer_type), INDEX idx_sales_rep (sales_rep_id), INDEX idx_region (region_id) );
credit_limit 和 credit_used：信用额度控制。销售下单时自动校验，超额度需要审批。
2. 客户联系人
一个客户可能有多个联系人，特别是大客户。
CREATE TABLE bd_customer_contact ( id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT NOT NULL, contact_name VARCHAR(50) NOT NULL, -- 角色 role VARCHAR(30), -- DECISION_MAKER/INFLUENCER/USER -- 联系方式 phone VARCHAR(30), mobile VARCHAR(30), email VARCHAR(100), wechat VARCHAR(50), -- 决策权重 influence_level VARCHAR(10), -- HIGH/MEDIUM/LOW is_primary BIT DEFAULT 0, INDEX idx_customer (customer_id) );
二、报价管理
1. 报价单
CREATE TABLE sa_quotation ( id BIGINT PRIMARY KEY AUTO_INCREMENT, quotation_no VARCHAR(30) NOT NULL UNIQUE, quotation_date DATE NOT NULL, -- 客户 customer_id BIGINT NOT NULL, contact_id BIGINT, -- 有效期 valid_until DATE, -- 金额 total_amount DECIMAL(18,2) DEFAULT 0, total_tax DECIMAL(18,2) DEFAULT 0, total_with_tax DECIMAL(18,2) DEFAULT 0, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', -- DRAFT/SENT/ACCEPTED/REJECTED/EXPIRED -- 转化 order_id BIGINT, -- 转为销售订单后关联 created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (quotation_date) ); CREATE TABLE sa_quotation_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, quotation_id BIGINT NOT NULL, line_no INT NOT NULL, -- 产品 material_id BIGINT NOT NULL, material_code VARCHAR(30), material_name VARCHAR(100), -- 数量价格 quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, price DECIMAL(18,6) NOT NULL, discount_rate DECIMAL(5,2) DEFAULT 0, tax_rate DECIMAL(5,2) DEFAULT 13.00, -- 金额 amount DECIMAL(18,2), tax_amount DECIMAL(18,2), total_amount DECIMAL(18,2), -- 交货 delivery_date DATE, INDEX idx_quotation (quotation_id) );
2. 报价单转销售订单
报价确认后一键转订单：
CREATE PROCEDURE sp_quotation_to_order(IN p_quotation_id BIGINT) BEGIN DECLARE v_order_id BIGINT; DECLARE v_customer_id BIGINT; -- 获取客户ID SELECT customer_id INTO v_customer_id FROM sa_quotation WHERE id = p_quotation_id; -- 创建销售订单 INSERT INTO sa_order (order_date, customer_id, status) SELECT quotation_date, customer_id, 'DRAFT' FROM sa_quotation WHERE id = p_quotation_id; SET v_order_id = LAST_INSERT_ID(); -- 复制明细 INSERT INTO sa_order_line (order_id, line_no, material_id, quantity, unit_id, price, discount_rate, tax_rate, amount, tax_amount, total_amount, delivery_date) SELECT v_order_id, line_no, material_id, quantity, unit_id, price, discount_rate, tax_rate, amount, tax_amount, total_amount, delivery_date FROM sa_quotation_line WHERE quotation_id = p_quotation_id; -- 更新报价单状态 UPDATE sa_quotation SET status = 'ACCEPTED', order_id = v_order_id WHERE id = p_quotation_id; END;
三、销售订单
1. 订单主表
CREATE TABLE sa_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(30) NOT NULL UNIQUE, order_date DATE NOT NULL, -- 客户 customer_id BIGINT NOT NULL, customer_code VARCHAR(20), customer_name VARCHAR(100), -- 业务信息 sales_rep_id BIGINT, department_id BIGINT, -- 价格条款 currency_code VARCHAR(3) DEFAULT 'CNY', exchange_rate DECIMAL(18,6) DEFAULT 1, payment_terms VARCHAR(20), -- 金额汇总 total_amount DECIMAL(18,2) DEFAULT 0, total_tax DECIMAL(18,2) DEFAULT 0, total_with_tax DECIMAL(18,2) DEFAULT 0, -- 信用校验 credit_check_result VARCHAR(10), -- PASS/WARNING/BLOCK -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', -- DRAFT/APPROVED/SHIPPING/COMPLETED/CLOSED created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (order_date), INDEX idx_status (status) ); CREATE TABLE sa_order_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, line_no INT NOT NULL, -- 产品 material_id BIGINT NOT NULL, material_code VARCHAR(30), material_name VARCHAR(100), -- 数量价格 quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, price DECIMAL(18,6) NOT NULL, discount_rate DECIMAL(5,2) DEFAULT 0, tax_rate DECIMAL(5,2) DEFAULT 13.00, -- 金额 amount DECIMAL(18,2), tax_amount DECIMAL(18,2), total_amount DECIMAL(18,2), -- 交货 delivery_date DATE, warehouse_id BIGINT, -- 出货进度 shipped_quantity DECIMAL(18,6) DEFAULT 0, remaining_quantity DECIMAL(18,6) GENERATED ALWAYS AS (quantity - shipped_quantity) STORED, -- 开票进度 invoiced_quantity DECIMAL(18,6) DEFAULT 0, INDEX idx_order (order_id), INDEX idx_material (material_id) );
2. 信用校验逻辑
销售订单保存时自动校验客户信用：
CREATE PROCEDURE sp_credit_check(IN p_customer_id BIGINT, IN p_order_amount DECIMAL(18,2)) BEGIN DECLARE v_credit_limit DECIMAL(18,2); DECLARE v_credit_used DECIMAL(18,2); DECLARE v_result VARCHAR(10); SELECT credit_limit, credit_used INTO v_credit_limit, v_credit_used FROM bd_customer WHERE id = p_customer_id; IF v_credit_limit = 0 THEN SET v_result = 'PASS'; -- 未设信用额度，不限制 ELSEIF v_credit_used + p_order_amount <= v_credit_limit THEN SET v_result = 'PASS'; ELSEIF v_credit_used + p_order_amount <= v_credit_limit * 1.1 THEN SET v_result = 'WARNING'; -- 超额10%以内，警告 ELSE SET v_result = 'BLOCK'; -- 超额10%以上，阻断 END IF; SELECT v_result AS credit_check_result; END;
四、出货与开票
1. 出库单
CREATE TABLE sa_delivery ( id BIGINT PRIMARY KEY AUTO_INCREMENT, delivery_no VARCHAR(30) NOT NULL UNIQUE, delivery_date DATE NOT NULL, -- 订单关联 order_id BIGINT, order_no VARCHAR(30), -- 客户 customer_id BIGINT NOT NULL, -- 仓库 warehouse_id BIGINT NOT NULL, -- 物流 carrier VARCHAR(50), tracking_no VARCHAR(50), -- 金额 total_quantity DECIMAL(18,6) DEFAULT 0, total_amount DECIMAL(18,2) DEFAULT 0, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_customer (customer_id) );
2. 出库审核扣减库存
CREATE PROCEDURE sp_sa_delivery_approve(IN p_delivery_id BIGINT) BEGIN -- 扣减库存 UPDATE inv_stock s JOIN sa_delivery_line l ON s.material_id = l.material_id AND s.warehouse_id = l.warehouse_id SET s.quantity = s.quantity - l.quantity WHERE l.delivery_id = p_delivery_id; -- 记录流水 INSERT INTO inv_transaction (transaction_type, warehouse_id, material_id, quantity) SELECT 'OUT_SALES', warehouse_id, material_id, -quantity FROM sa_delivery_line WHERE delivery_id = p_delivery_id; -- 更新订单明细出货数量 UPDATE sa_order_line ol JOIN sa_delivery_line dl ON ol.id = dl.order_line_id SET ol.shipped_quantity = ol.shipped_quantity + dl.quantity WHERE dl.delivery_id = p_delivery_id; -- 更新出库单状态 UPDATE sa_delivery SET status = 'APPROVED' WHERE id = p_delivery_id; END;
3. 销售发票
CREATE TABLE sa_invoice ( id BIGINT PRIMARY KEY AUTO_INCREMENT, invoice_no VARCHAR(30) NOT NULL UNIQUE, invoice_date DATE NOT NULL, invoice_type VARCHAR(20), -- SPECIAL/NORMAL/ELECTRONIC -- 客户 customer_id BIGINT NOT NULL, -- 金额 total_amount DECIMAL(18,2), total_tax DECIMAL(18,2), total_with_tax DECIMAL(18,2), -- 应收 ar_invoice_id BIGINT, -- 关联应收发票 status VARCHAR(10) DEFAULT 'DRAFT', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (invoice_date) );
五、销售分析报表
1. 客户毛利分析
SELECT c.customer_name, COUNT(DISTINCT o.id) AS order_count, SUM(ol.quantity * ol.price) AS sales_amount, SUM(ol.quantity * ol.price) - SUM(ol.quantity * COALESCE(ic.avg_cost, 0)) AS gross_profit, CASE WHEN SUM(ol.quantity * ol.price) > 0 THEN ROUND((SUM(ol.quantity * ol.price) - SUM(ol.quantity * COALESCE(ic.avg_cost, 0))) / SUM(ol.quantity * ol.price) * 100, 2) ELSE 0 END AS gross_margin FROM sa_order o JOIN sa_order_line ol ON o.id = ol.order_id JOIN bd_customer c ON o.customer_id = c.id LEFT JOIN ( SELECT material_id, AVG(cost) AS avg_cost FROM inv_stock GROUP BY material_id ) ic ON ol.material_id = ic.material_id WHERE o.order_date BETWEEN ? AND ? AND o.status IN ('APPROVED', 'SHIPPING', 'COMPLETED') GROUP BY c.customer_name ORDER BY gross_profit DESC;2. 销售员业绩排名
SELECT u.user_name AS sales_rep, COUNT(DISTINCT o.id) AS order_count, SUM(o.total_with_tax) AS sales_amount, SUM(CASE WHEN o.status = 'COMPLETED' THEN o.total_with_tax ELSE 0 END) AS completed_amount, ROUND(SUM(CASE WHEN o.status = 'COMPLETED' THEN o.total_with_tax ELSE 0 END) / NULLIF(SUM(o.total_with_tax), 0) * 100, 2) AS completion_rate FROM sa_order o JOIN sys_user u ON o.sales_rep_id = u.id WHERE o.order_date BETWEEN ? AND ? GROUP BY u.user_name ORDER BY sales_amount DESC;
六、销售模块的设计要点
1. 价格体系
价格层级：标准价 → 客户价 → 促销价 → 协议价 → 手工价。
每层覆盖上层的条件不同：客户价按客户分类，促销价按时间段，协议价按合同。
2. 信用控制
三个级别：通过、警告、阻断。警告可以继续但需要主管确认，阻断直接拦截。
超信用处理：临时提额（需审批）、分批出货、预收款冲抵。
3. 订单状态机
DRAFT → APPROVED → SHIPPING → COMPLETED → CLOSED
每个状态转换要有明确的触发条件和权限控制。不能跳过审批直接出货。
成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型
