-- ============================================================
|
-- 售后工单模块 DDL
|
-- ============================================================
|
|
-- 1. 售后工单主表
|
DROP TABLE IF EXISTS `after_sale_ticket`;
|
CREATE TABLE `after_sale_ticket` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '工单编号',
|
`no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '工单单号',
|
`name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '工单主题/标题',
|
`customer_id` bigint NOT NULL COMMENT '客户编号，关联 crm_customer.id',
|
`contact_id` bigint DEFAULT NULL COMMENT '联系人编号，关联 crm_contact.id',
|
`sale_order_id` bigint DEFAULT NULL COMMENT '销售订单编号，关联 erp_sale_order.id',
|
`sale_order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '销售订单号(冗余)',
|
`contract_id` bigint DEFAULT NULL COMMENT '合同编号，关联 crm_contract.id',
|
`issue_type` tinyint NOT NULL DEFAULT 0 COMMENT '问题类型: 0-未判定 1-一般问题 2-维修问题 3-退货问题',
|
`issue_category` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '问题分类',
|
`issue_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '问题描述',
|
`severity_level` tinyint DEFAULT 0 COMMENT '严重程度: 0-轻微 1-一般 2-严重 3-致命',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-草稿 10-处理中 30-待退货 40-待退款/换货 50-已完结 60-已关闭 99-已取消',
|
`owner_user_id` bigint NOT NULL COMMENT '负责人用户编号',
|
`handler_user_id` bigint DEFAULT NULL COMMENT '处理人用户编号',
|
`process_instance_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'BPM工作流实例编号',
|
`return_id` bigint DEFAULT NULL COMMENT '关联退货申请编号',
|
`repair_id` bigint DEFAULT NULL COMMENT '关联维修记录编号',
|
`resolved_time` datetime DEFAULT NULL COMMENT '解决时间',
|
`closed_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE KEY `uk_no` (`no`),
|
KEY `idx_customer_id` (`customer_id`),
|
KEY `idx_sale_order_id` (`sale_order_id`),
|
KEY `idx_owner_user_id` (`owner_user_id`),
|
KEY `idx_status` (`status`),
|
KEY `idx_create_time` (`create_time`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后工单主表';
|
|
-- 2. 售后工单明细行表
|
DROP TABLE IF EXISTS `after_sale_ticket_item`;
|
CREATE TABLE `after_sale_ticket_item` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`ticket_id` bigint NOT NULL COMMENT '工单编号，关联 after_sale_ticket.id',
|
`sale_order_item_id` bigint DEFAULT NULL COMMENT '销售订单行编号',
|
`item_id` bigint NOT NULL COMMENT 'MDM物料编号',
|
`item_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料编码(冗余)',
|
`item_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料名称(冗余)',
|
`batch_id` bigint DEFAULT NULL COMMENT '生产批次编号',
|
`batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '生产批次号',
|
`sale_quantity` decimal(10,3) NOT NULL DEFAULT 0.000 COMMENT '销售数量',
|
`return_quantity` decimal(10,3) DEFAULT 0.000 COMMENT '退货数量',
|
`need_repair` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否需要维修',
|
`need_return` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否需要退货',
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
KEY `idx_ticket_id` (`ticket_id`),
|
KEY `idx_item_id` (`item_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后工单明细行表';
|
|
-- 3. 退货申请表
|
DROP TABLE IF EXISTS `after_sale_return`;
|
CREATE TABLE `after_sale_return` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '退货申请编号',
|
`no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货单号',
|
`ticket_id` bigint NOT NULL COMMENT '关联工单编号，关联 after_sale_ticket.id',
|
`customer_id` bigint NOT NULL COMMENT '客户编号',
|
`sale_order_id` bigint DEFAULT NULL COMMENT '关联销售订单编号',
|
`return_type` tinyint NOT NULL COMMENT '退货类型: 1-退款退货 2-换货 3-仅退款',
|
`return_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货原因',
|
`defect_category` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '缺陷分类',
|
`total_return_price` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '应退总金额',
|
`actual_refund_price` decimal(12,2) DEFAULT 0.00 COMMENT '实退金额',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-草稿 10-销售审核中 20-审核通过已同步MES 30-检验中 40-检验完成 50-已入库 60-退款/换货完成 70-已关闭 99-已取消',
|
`audit_user_id` bigint DEFAULT NULL COMMENT '审核人',
|
`audit_time` datetime DEFAULT NULL COMMENT '审核时间',
|
`mes_return_id` bigint DEFAULT NULL COMMENT 'MES退货单编号，关联 mes_wm_return_sales.id',
|
`mes_return_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'MES退货单号(冗余)',
|
`quality_result` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '质检结论: 合格/特采/退货/报废',
|
`quality_report_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '质检报告附件URL',
|
`return_inbound_time` datetime DEFAULT NULL COMMENT '退货入库完成时间',
|
`refund_time` datetime DEFAULT NULL COMMENT '退款/换货完成时间',
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE KEY `uk_no` (`no`),
|
KEY `idx_ticket_id` (`ticket_id`),
|
KEY `idx_mes_return_id` (`mes_return_id`),
|
KEY `idx_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后退货申请表';
|
|
-- 4. 退货明细行表
|
DROP TABLE IF EXISTS `after_sale_return_item`;
|
CREATE TABLE `after_sale_return_item` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`return_id` bigint NOT NULL COMMENT '退货申请编号，关联 after_sale_return.id',
|
`ticket_item_id` bigint NOT NULL COMMENT '工单明细行编号，关联 after_sale_ticket_item.id',
|
`item_id` bigint NOT NULL COMMENT 'MDM物料编号',
|
`item_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料编码(冗余)',
|
`item_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料名称(冗余)',
|
`batch_id` bigint DEFAULT NULL COMMENT '批次编号',
|
`batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '批次号',
|
`return_quantity` decimal(10,3) NOT NULL COMMENT '退货数量',
|
`unit_price` decimal(12,2) NOT NULL COMMENT '销售单价',
|
`return_price` decimal(12,2) NOT NULL COMMENT '退货金额',
|
`mes_return_line_id` bigint DEFAULT NULL COMMENT 'MES退货单行编号',
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
KEY `idx_return_id` (`return_id`),
|
KEY `idx_ticket_item_id` (`ticket_item_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后退货明细行表';
|
|
-- 5. 维修记录表
|
DROP TABLE IF EXISTS `after_sale_repair`;
|
CREATE TABLE `after_sale_repair` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '维修记录编号',
|
`ticket_id` bigint NOT NULL COMMENT '关联工单编号，关联 after_sale_ticket.id',
|
`repair_type` tinyint NOT NULL COMMENT '维修类型: 1-现场维修 2-返厂维修 3-远程指导',
|
`fault_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '故障描述',
|
`repair_action` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '维修措施',
|
`repair_result` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '维修结果: 已修复/无法修复/部分修复',
|
`repair_cost` decimal(10,2) DEFAULT 0.00 COMMENT '维修费用',
|
`repair_user_id` bigint DEFAULT NULL COMMENT '维修人',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-待处理 10-维修中 20-已完成 30-已关闭',
|
`start_time` datetime DEFAULT NULL COMMENT '开始维修时间',
|
`finish_time` datetime DEFAULT NULL COMMENT '完成维修时间',
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
KEY `idx_ticket_id` (`ticket_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后维修记录表';
|
|
-- 6. 售后数据权限表
|
DROP TABLE IF EXISTS `after_sale_permission`;
|
CREATE TABLE `after_sale_permission` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`biz_type` tinyint NOT NULL COMMENT '数据类型: 1-售后工单 2-退货申请 3-维修记录',
|
`biz_id` bigint NOT NULL COMMENT '数据编号',
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
`level` tinyint NOT NULL COMMENT '权限级别: 1-负责人 2-只读 3-读写',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
KEY `idx_biz` (`biz_type`, `biz_id`),
|
KEY `idx_user` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后数据权限表';
|
