采购订单管理
<p>以下是基于需求设计的采购模块MySQL数据库表结构，遵循示例中的命名规范并包含必要字段、注释和索引：</p>
<pre><code class="language-sql">-- ----------------------------
-- 采购订单主表
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_order;
CREATE TABLE lv_pur_order (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
sales_no VARCHAR(50) COMMENT &#039;销售订单编号&#039;,
pur_order_no VARCHAR(50) NOT NULL COMMENT &#039;采购单号（系统生成）&#039;,
pur_type INT(10) COMMENT &#039;采购类型：10销售采购/20库存采购/30售后采购&#039;,
after_sale_no VARCHAR(50) COMMENT &#039;售后单号&#039;,
supplier_id BIGINT(20) NOT NULL COMMENT &#039;供应商ID&#039;,
sku_code VARCHAR(50) COMMENT &#039;SKU编码&#039;,
pur_quantity INT NOT NULL COMMENT &#039;采购数量&#039;,
unit_price DECIMAL(10,2) COMMENT &#039;供应商单价&#039;,
tax_included TINYINT(1) DEFAULT 1 COMMENT &#039;是否含税（0否/1是）&#039;,
tax_rate DECIMAL(5,2) COMMENT &#039;税率&#039;,
tax_amount DECIMAL(10,2) COMMENT &#039;税额&#039;,
total_amount DECIMAL(10,2) COMMENT &#039;总金额&#039;,
delivery_date DATETIME COMMENT &#039;交货日期&#039;,
delivery_method VARCHAR(50) COMMENT &#039;交货方式&#039;,
settlement_method INT(10) COMMENT &#039;结算方式：10预付/20按单结/30月结&#039;,
settlement_date DATETIME COMMENT &#039;结算日期&#039;,
settlement_amount DECIMAL(10,2) COMMENT &#039;结算金额&#039;,
invoice_type INT(10) COMMENT &#039;发票类型：10开票/20不开票&#039;,
settlement_status INT(10) COMMENT &#039;结算状态：10待结/20部分结/30结清&#039;,
payment_method INT(10) COMMENT &#039;付款方式：10公账/20线下&#039;,
pur_date DATETIME COMMENT &#039;采购日期&#039;,
pur_status INT(10) DEFAULT 10 COMMENT &#039;采购状态：10草稿/20待审核/30审核通过/40审核不通过/50采购中/60采购发货/70采购换货/80采购退货/90采购结束&#039;,
operator_id BIGINT(20) COMMENT &#039;运营负责人&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_pur_order_no (pur_order_no),
KEY idx_po_sales_no (sales_no),
KEY idx_po_supplier_id (supplier_id),
KEY idx_po_pur_status (pur_status)
) COMMENT=&#039;采购订单主表&#039;;
-- ----------------------------
-- 采购费用明细表
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_cost_detail;
CREATE TABLE lv_pur_cost_detail (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
pur_order_id BIGINT(20) NOT NULL COMMENT &#039;采购订单ID&#039;,
cost_type INT(10) COMMENT &#039;费用类型：10预付款/20付尾款/30付全款/40退货款&#039;,
cost_status INT(10) COMMENT &#039;费用状态：10待付/20已付/30退款中/40已退款&#039;,
amount DECIMAL(10,2) COMMENT &#039;费用金额&#039;,
cost_date DATETIME COMMENT &#039;费用日期&#039;,
payer_id BIGINT(20) COMMENT &#039;付款人&#039;,
payee_id BIGINT(20) COMMENT &#039;收款人&#039;,
return_no VARCHAR(50) COMMENT &#039;退/换单号&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_pcd_pur_order_id (pur_order_id),
KEY idx_pcd_cost_type (cost_type)
) COMMENT=&#039;采购费用明细表&#039;;
-- ----------------------------
-- 采购审核表
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_audit;
CREATE TABLE lv_pur_audit (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
pur_order_id BIGINT(20) NOT NULL COMMENT &#039;采购订单ID&#039;,
auditor_id BIGINT(20) NOT NULL COMMENT &#039;审核人&#039;,
audit_opinion TEXT COMMENT &#039;审核意见&#039;,
audit_status INT(10) COMMENT &#039;审核状态：10通过/20不通过&#039;,
audit_time DATETIME COMMENT &#039;审核时间&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_pa_pur_order_id (pur_order_id),
KEY idx_pa_auditor_id (auditor_id)
) COMMENT=&#039;采购审核表&#039;;
-- ----------------------------
-- 采购跟踪表
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_track;
CREATE TABLE lv_pur_track (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
pur_order_id BIGINT(20) NOT NULL COMMENT &#039;采购订单ID&#039;,
operator_id BIGINT(20) COMMENT &#039;业务员&#039;,
track_content TEXT COMMENT &#039;跟踪内容&#039;,
pur_status INT(10) COMMENT &#039;采购状态&#039;,
attachment VARCHAR(500) COMMENT &#039;附件凭证&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
KEY idx_pt_pur_order_id (pur_order_id)
) COMMENT=&#039;采购跟踪表&#039;;
-- ----------------------------
-- 采购退换货表
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_return;
CREATE TABLE lv_pur_return (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
pur_order_id BIGINT(20) NOT NULL COMMENT &#039;采购订单ID&#039;,
return_no VARCHAR(50) COMMENT &#039;退/换单号&#039;,
return_type INT(10) COMMENT &#039;退换类型：10退货/20换货&#039;,
sku_code VARCHAR(50) COMMENT &#039;SKU编码&#039;,
return_quantity INT COMMENT &#039;退换数量&#039;,
unit_price DECIMAL(10,2) COMMENT &#039;商品单价&#039;,
refund_amount DECIMAL(10,2) COMMENT &#039;退款金额&#039;,
actual_amount DECIMAL(10,2) COMMENT &#039;实际金额&#039;,
return_status INT(10) COMMENT &#039;退换状态：10草稿/20进行中/30已取消/40已完成&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
remark VARCHAR(400) COMMENT &#039;备注&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_return_no (return_no),
KEY idx_pr_pur_order_id (pur_order_id)
) COMMENT=&#039;采购退换货表&#039;;
-- ----------------------------
-- 采购统计表（按供应商）
-- ----------------------------
DROP TABLE IF EXISTS lv_pur_statistics;
CREATE TABLE lv_pur_statistics (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &#039;主键ID&#039;,
supplier_id BIGINT(20) NOT NULL COMMENT &#039;供应商ID&#039;,
stat_year INT COMMENT &#039;统计年度&#039;,
stat_month INT COMMENT &#039;统计月份&#039;,
order_count INT COMMENT &#039;采购订单笔数&#039;,
total_amount DECIMAL(16,2) COMMENT &#039;采购金额&#039;,
invoice_amount DECIMAL(16,2) COMMENT &#039;开票金额&#039;,
settled_amount DECIMAL(16,2) COMMENT &#039;已结金额&#039;,
unsettled_amount DECIMAL(16,2) COMMENT &#039;未结金额&#039;,
tenant_id BIGINT(20) DEFAULT 0 COMMENT &#039;租户ID&#039;,
creator BIGINT(20) COMMENT &#039;创建人&#039;,
create_time DATETIME COMMENT &#039;创建时间&#039;,
updater BIGINT(20) COMMENT &#039;更新人&#039;,
update_time DATETIME COMMENT &#039;更新时间&#039;,
deleted BIT(1) DEFAULT 0 COMMENT &#039;删除标记&#039;,
PRIMARY KEY (id),
UNIQUE KEY uk_stat_supplier (supplier_id, stat_year, stat_month),
KEY idx_ps_stat_year (stat_year),
KEY idx_ps_stat_month (stat_month)
) COMMENT=&#039;采购统计表（按供应商）&#039;;</code></pre>
<h3>设计说明：</h3>
<ol>
<li>
<p><strong>表结构特点</strong>：</p>
<ul>
<li>所有表均包含 <code>tenant_id</code>（多租户支持）、<code>deleted</code>（逻辑删除）、审计字段（creator/create_time/updater/update_time）</li>
<li>状态字段统一使用 <strong>INT类型</strong>（示例：<code>pur_status</code>），建议配合字典表使用</li>
<li>金额字段统一使用 <strong>DECIMAL(10,2)</strong>（统计表使用DECIMAL(16,2)）</li>
</ul>
</li>
<li>
<p><strong>关键设计</strong>：</p>
<ul>
<li><code>lv_pur_order</code> 主表包含采购全生命周期字段，通过 <code>pur_status</code> 驱动状态流转</li>
<li><code>lv_pur_cost_detail</code> 支持多次付款/退款场景（如预付款+尾款）</li>
<li><code>lv_pur_statistics</code> 支持按供应商+年月维度快速统计</li>
<li>所有业务单据号（<code>pur_order_no</code>/<code>return_no</code>）使用 <strong>唯一索引</strong></li>
</ul>
</li>
<li><strong>扩展建议</strong>：
<ul>
<li>添加外键约束（如 <code>supplier_id</code> 关联供应商表）</li>
<li>创建视图生成实时统计（替代物理表 <code>lv_pur_statistics</code>）</li>
<li>补充字典表管理状态枚举值（如 <code>pur_type</code>/<code>settlement_method</code>）</li>
</ul></li>
</ol>