绿洲ERP电商系统


采购订单管理

<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 &amp;#039;主键ID&amp;#039;, sales_no VARCHAR(50) COMMENT &amp;#039;销售订单编号&amp;#039;, pur_order_no VARCHAR(50) NOT NULL COMMENT &amp;#039;采购单号(系统生成)&amp;#039;, pur_type INT(10) COMMENT &amp;#039;采购类型:10销售采购/20库存采购/30售后采购&amp;#039;, after_sale_no VARCHAR(50) COMMENT &amp;#039;售后单号&amp;#039;, supplier_id BIGINT(20) NOT NULL COMMENT &amp;#039;供应商ID&amp;#039;, sku_code VARCHAR(50) COMMENT &amp;#039;SKU编码&amp;#039;, pur_quantity INT NOT NULL COMMENT &amp;#039;采购数量&amp;#039;, unit_price DECIMAL(10,2) COMMENT &amp;#039;供应商单价&amp;#039;, tax_included TINYINT(1) DEFAULT 1 COMMENT &amp;#039;是否含税(0否/1是)&amp;#039;, tax_rate DECIMAL(5,2) COMMENT &amp;#039;税率&amp;#039;, tax_amount DECIMAL(10,2) COMMENT &amp;#039;税额&amp;#039;, total_amount DECIMAL(10,2) COMMENT &amp;#039;总金额&amp;#039;, delivery_date DATETIME COMMENT &amp;#039;交货日期&amp;#039;, delivery_method VARCHAR(50) COMMENT &amp;#039;交货方式&amp;#039;, settlement_method INT(10) COMMENT &amp;#039;结算方式:10预付/20按单结/30月结&amp;#039;, settlement_date DATETIME COMMENT &amp;#039;结算日期&amp;#039;, settlement_amount DECIMAL(10,2) COMMENT &amp;#039;结算金额&amp;#039;, invoice_type INT(10) COMMENT &amp;#039;发票类型:10开票/20不开票&amp;#039;, settlement_status INT(10) COMMENT &amp;#039;结算状态:10待结/20部分结/30结清&amp;#039;, payment_method INT(10) COMMENT &amp;#039;付款方式:10公账/20线下&amp;#039;, pur_date DATETIME COMMENT &amp;#039;采购日期&amp;#039;, pur_status INT(10) DEFAULT 10 COMMENT &amp;#039;采购状态:10草稿/20待审核/30审核通过/40审核不通过/50采购中/60采购发货/70采购换货/80采购退货/90采购结束&amp;#039;, operator_id BIGINT(20) COMMENT &amp;#039;运营负责人&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, remark VARCHAR(400) COMMENT &amp;#039;备注&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#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=&amp;#039;采购订单主表&amp;#039;; -- ---------------------------- -- 采购费用明细表 -- ---------------------------- DROP TABLE IF EXISTS lv_pur_cost_detail; CREATE TABLE lv_pur_cost_detail ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &amp;#039;主键ID&amp;#039;, pur_order_id BIGINT(20) NOT NULL COMMENT &amp;#039;采购订单ID&amp;#039;, cost_type INT(10) COMMENT &amp;#039;费用类型:10预付款/20付尾款/30付全款/40退货款&amp;#039;, cost_status INT(10) COMMENT &amp;#039;费用状态:10待付/20已付/30退款中/40已退款&amp;#039;, amount DECIMAL(10,2) COMMENT &amp;#039;费用金额&amp;#039;, cost_date DATETIME COMMENT &amp;#039;费用日期&amp;#039;, payer_id BIGINT(20) COMMENT &amp;#039;付款人&amp;#039;, payee_id BIGINT(20) COMMENT &amp;#039;收款人&amp;#039;, return_no VARCHAR(50) COMMENT &amp;#039;退/换单号&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, remark VARCHAR(400) COMMENT &amp;#039;备注&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#039;, PRIMARY KEY (id), KEY idx_pcd_pur_order_id (pur_order_id), KEY idx_pcd_cost_type (cost_type) ) COMMENT=&amp;#039;采购费用明细表&amp;#039;; -- ---------------------------- -- 采购审核表 -- ---------------------------- DROP TABLE IF EXISTS lv_pur_audit; CREATE TABLE lv_pur_audit ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &amp;#039;主键ID&amp;#039;, pur_order_id BIGINT(20) NOT NULL COMMENT &amp;#039;采购订单ID&amp;#039;, auditor_id BIGINT(20) NOT NULL COMMENT &amp;#039;审核人&amp;#039;, audit_opinion TEXT COMMENT &amp;#039;审核意见&amp;#039;, audit_status INT(10) COMMENT &amp;#039;审核状态:10通过/20不通过&amp;#039;, audit_time DATETIME COMMENT &amp;#039;审核时间&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#039;, PRIMARY KEY (id), KEY idx_pa_pur_order_id (pur_order_id), KEY idx_pa_auditor_id (auditor_id) ) COMMENT=&amp;#039;采购审核表&amp;#039;; -- ---------------------------- -- 采购跟踪表 -- ---------------------------- DROP TABLE IF EXISTS lv_pur_track; CREATE TABLE lv_pur_track ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &amp;#039;主键ID&amp;#039;, pur_order_id BIGINT(20) NOT NULL COMMENT &amp;#039;采购订单ID&amp;#039;, operator_id BIGINT(20) COMMENT &amp;#039;业务员&amp;#039;, track_content TEXT COMMENT &amp;#039;跟踪内容&amp;#039;, pur_status INT(10) COMMENT &amp;#039;采购状态&amp;#039;, attachment VARCHAR(500) COMMENT &amp;#039;附件凭证&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, remark VARCHAR(400) COMMENT &amp;#039;备注&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#039;, PRIMARY KEY (id), KEY idx_pt_pur_order_id (pur_order_id) ) COMMENT=&amp;#039;采购跟踪表&amp;#039;; -- ---------------------------- -- 采购退换货表 -- ---------------------------- DROP TABLE IF EXISTS lv_pur_return; CREATE TABLE lv_pur_return ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &amp;#039;主键ID&amp;#039;, pur_order_id BIGINT(20) NOT NULL COMMENT &amp;#039;采购订单ID&amp;#039;, return_no VARCHAR(50) COMMENT &amp;#039;退/换单号&amp;#039;, return_type INT(10) COMMENT &amp;#039;退换类型:10退货/20换货&amp;#039;, sku_code VARCHAR(50) COMMENT &amp;#039;SKU编码&amp;#039;, return_quantity INT COMMENT &amp;#039;退换数量&amp;#039;, unit_price DECIMAL(10,2) COMMENT &amp;#039;商品单价&amp;#039;, refund_amount DECIMAL(10,2) COMMENT &amp;#039;退款金额&amp;#039;, actual_amount DECIMAL(10,2) COMMENT &amp;#039;实际金额&amp;#039;, return_status INT(10) COMMENT &amp;#039;退换状态:10草稿/20进行中/30已取消/40已完成&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, remark VARCHAR(400) COMMENT &amp;#039;备注&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#039;, PRIMARY KEY (id), UNIQUE KEY uk_return_no (return_no), KEY idx_pr_pur_order_id (pur_order_id) ) COMMENT=&amp;#039;采购退换货表&amp;#039;; -- ---------------------------- -- 采购统计表(按供应商) -- ---------------------------- DROP TABLE IF EXISTS lv_pur_statistics; CREATE TABLE lv_pur_statistics ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT &amp;#039;主键ID&amp;#039;, supplier_id BIGINT(20) NOT NULL COMMENT &amp;#039;供应商ID&amp;#039;, stat_year INT COMMENT &amp;#039;统计年度&amp;#039;, stat_month INT COMMENT &amp;#039;统计月份&amp;#039;, order_count INT COMMENT &amp;#039;采购订单笔数&amp;#039;, total_amount DECIMAL(16,2) COMMENT &amp;#039;采购金额&amp;#039;, invoice_amount DECIMAL(16,2) COMMENT &amp;#039;开票金额&amp;#039;, settled_amount DECIMAL(16,2) COMMENT &amp;#039;已结金额&amp;#039;, unsettled_amount DECIMAL(16,2) COMMENT &amp;#039;未结金额&amp;#039;, tenant_id BIGINT(20) DEFAULT 0 COMMENT &amp;#039;租户ID&amp;#039;, creator BIGINT(20) COMMENT &amp;#039;创建人&amp;#039;, create_time DATETIME COMMENT &amp;#039;创建时间&amp;#039;, updater BIGINT(20) COMMENT &amp;#039;更新人&amp;#039;, update_time DATETIME COMMENT &amp;#039;更新时间&amp;#039;, deleted BIT(1) DEFAULT 0 COMMENT &amp;#039;删除标记&amp;#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=&amp;#039;采购统计表(按供应商)&amp;#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>

页面列表

ITEM_HTML