-- =============================================================================
|
-- 采购管理 / 供应商域重构迁移脚本
|
-- 日期：2026-11-03
|
-- 目标库：mom-ntsh（36.213.150.237:10000）
|
--
|
-- 内容：
|
-- 1. 新建供应商银行账户表 srm_supplier_bank
|
-- 2. 新建询价比价相关表 srm_inquiry / srm_inquiry_item / srm_inquiry_supplier
|
-- / srm_inquiry_quote / srm_inquiry_order
|
-- 3. 新建投标保证金台账 srm_bid_bond
|
-- 4. 旧供应商扁平联系人/银行字段迁移为默认联系人与默认银行
|
-- 5. 删除供应商准入/资质/绩效/招投标/统计菜单，新增询价比价/投标保证金/供应商往来菜单
|
-- 6. 同步新菜单授权给已拥有“供应商档案”的角色
|
-- 7. DROP 已废弃的 13 张空表（结构备份见同目录
|
-- srm_legacy_tables_backup_20261103.sql）
|
--
|
-- 说明：
|
-- - 脚本不使用 tenant_id（项目非多租户）。
|
-- - 建表使用 CREATE TABLE IF NOT EXISTS，菜单使用“先删后插”，可重复执行。
|
-- - DROP 的 13 张表在执行前已确认均为 0 行；执行后不可恢复。
|
-- =============================================================================
|
|
-- -----------------------------------------------------------------------------
|
-- 〇、补齐依赖字段（供应商往来查询 erp_purchase_invoice 需要，与
|
-- 20261027_business_requirements.sql 中的定义一致；已存在则跳过）
|
-- -----------------------------------------------------------------------------
|
|
SET @has_arrival_notice_id = (
|
SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_invoice'
|
AND column_name = 'arrival_notice_id'
|
);
|
SET @sql = IF(@has_arrival_notice_id = 0,
|
'ALTER TABLE `erp_purchase_invoice` ADD COLUMN `arrival_notice_id` BIGINT NULL COMMENT ''关联入库单'' AFTER `purchase_order_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- -----------------------------------------------------------------------------
|
-- 一、新建表
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `srm_supplier_bank` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '银行账户ID',
|
`supplier_id` bigint NOT NULL COMMENT '供应商ID',
|
`bank_name` varchar(128) NOT NULL COMMENT '开户行',
|
`bank_account` varchar(64) NOT NULL COMMENT '银行账号',
|
`bank_no` varchar(64) DEFAULT NULL COMMENT '银行行号',
|
`bank_address` varchar(255) DEFAULT NULL COMMENT '开户行地址',
|
`is_default` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否默认银行账户',
|
`sort` int NOT NULL DEFAULT '0' COMMENT '排序',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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_supplier_id` (`supplier_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='供应商银行账户表';
|
|
CREATE TABLE IF NOT EXISTS `srm_inquiry` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '询价单ID',
|
`no` varchar(32) NOT NULL COMMENT '询价单号',
|
`title` varchar(128) NOT NULL COMMENT '询价标题',
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态：0草稿 10询价中 20已转订单 30已取消',
|
`inquiry_date` date DEFAULT NULL COMMENT '询价日期',
|
`deadline` date DEFAULT NULL COMMENT '报价截止日期',
|
`purchaser_user_id` bigint DEFAULT NULL COMMENT '采购员用户ID',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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`) USING BTREE,
|
KEY `idx_status` (`status`) USING BTREE,
|
KEY `idx_inquiry_date` (`inquiry_date`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM询价单';
|
|
CREATE TABLE IF NOT EXISTS `srm_inquiry_item` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '询价产品行ID',
|
`inquiry_id` bigint NOT NULL COMMENT '询价单ID',
|
`product_id` bigint DEFAULT NULL COMMENT '产品ID',
|
`product_unit_id` bigint DEFAULT NULL COMMENT '产品单位ID',
|
`product_code` varchar(64) DEFAULT NULL COMMENT '产品编码',
|
`product_name` varchar(255) DEFAULT NULL COMMENT '产品名称',
|
`product_spec` varchar(200) DEFAULT NULL COMMENT '规格型号',
|
`unit_name` varchar(50) DEFAULT NULL COMMENT '单位名称',
|
`quantity` decimal(24,6) NOT NULL DEFAULT '0.000000' COMMENT '采购数量',
|
`estimated_price` decimal(24,6) DEFAULT NULL COMMENT '预估单价',
|
`selected_supplier_id` bigint DEFAULT NULL COMMENT '中标供应商ID',
|
`selected_quote_id` bigint DEFAULT NULL COMMENT '中标报价ID',
|
`selected_price` decimal(24,6) DEFAULT NULL COMMENT '中标单价',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`sort` int NOT NULL DEFAULT '0' COMMENT '排序',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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_inquiry_id` (`inquiry_id`) USING BTREE,
|
KEY `idx_product_id` (`product_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM询价单产品行';
|
|
CREATE TABLE IF NOT EXISTS `srm_inquiry_supplier` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '询价参与供应商ID',
|
`inquiry_id` bigint NOT NULL COMMENT '询价单ID',
|
`supplier_id` bigint NOT NULL COMMENT '供应商ID',
|
`quote_status` tinyint NOT NULL DEFAULT '0' COMMENT '报价状态：0未报价 10已报价',
|
`quote_time` datetime DEFAULT NULL COMMENT '最近报价时间',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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_inquiry_id` (`inquiry_id`) USING BTREE,
|
KEY `idx_supplier_id` (`supplier_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM询价参与供应商';
|
|
CREATE TABLE IF NOT EXISTS `srm_inquiry_quote` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '询价报价ID',
|
`inquiry_id` bigint NOT NULL COMMENT '询价单ID',
|
`inquiry_item_id` bigint NOT NULL COMMENT '询价产品行ID',
|
`supplier_id` bigint NOT NULL COMMENT '供应商ID',
|
`price` decimal(24,6) NOT NULL COMMENT '报价单价',
|
`tax_rate` decimal(24,6) DEFAULT NULL COMMENT '税率（%）',
|
`delivery_cycle` int DEFAULT NULL COMMENT '交货周期（天）',
|
`payment_terms` varchar(128) DEFAULT NULL COMMENT '付款条件',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`is_selected` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否中标报价',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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_inquiry_id` (`inquiry_id`) USING BTREE,
|
KEY `idx_inquiry_item_id` (`inquiry_item_id`) USING BTREE,
|
KEY `idx_supplier_id` (`supplier_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM询价报价明细';
|
|
CREATE TABLE IF NOT EXISTS `srm_inquiry_order` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '询价转订单记录ID',
|
`inquiry_id` bigint NOT NULL COMMENT '询价单ID',
|
`supplier_id` bigint NOT NULL COMMENT '中标供应商ID',
|
`purchase_order_id` bigint DEFAULT NULL COMMENT '生成的采购订单ID',
|
`purchase_order_no` varchar(64) DEFAULT NULL COMMENT '生成的采购订单号',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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_inquiry_id` (`inquiry_id`) USING BTREE,
|
KEY `idx_supplier_id` (`supplier_id`) USING BTREE,
|
KEY `idx_purchase_order_id` (`purchase_order_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM询价转采购订单记录';
|
|
CREATE TABLE IF NOT EXISTS `srm_bid_bond` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '投标保证金ID',
|
`no` varchar(32) NOT NULL COMMENT '保证金单号',
|
`supplier_id` bigint NOT NULL COMMENT '供应商ID',
|
`project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
|
`inquiry_id` bigint DEFAULT NULL COMMENT '关联询价单ID',
|
`purchase_order_id` bigint DEFAULT NULL COMMENT '关联采购订单ID',
|
`bond_amount` decimal(24,6) NOT NULL DEFAULT '0.000000' COMMENT '保证金金额',
|
`pay_date` date DEFAULT NULL COMMENT '缴纳日期',
|
`won` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否中标',
|
`refund_status` tinyint NOT NULL DEFAULT '0' COMMENT '退回状态：0未退回 1部分退回 2已退回 3无需退回',
|
`refund_amount` decimal(24,6) DEFAULT NULL COMMENT '退回金额',
|
`refund_date` date DEFAULT NULL COMMENT '退回日期',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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`) USING BTREE,
|
KEY `idx_supplier_id` (`supplier_id`) USING BTREE,
|
KEY `idx_inquiry_id` (`inquiry_id`) USING BTREE,
|
KEY `idx_refund_status` (`refund_status`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='SRM投标保证金台账';
|
|
-- -----------------------------------------------------------------------------
|
-- 二、旧供应商扁平字段迁移为默认联系人 / 默认银行
|
-- 仅在对应子表还没有数据时补一条，避免重复执行产生重复行。
|
-- -----------------------------------------------------------------------------
|
|
INSERT INTO `srm_supplier_contact`
|
(`supplier_id`, `name`, `mobile`, `email`, `is_default`, `sort`, `creator`, `updater`)
|
SELECT s.`id`, TRIM(s.`contact_name`), s.`contact_mobile`, s.`contact_email`, b'1', 0, 'system', 'system'
|
FROM `srm_supplier` s
|
WHERE s.`deleted` = b'0'
|
AND s.`contact_name` IS NOT NULL
|
AND TRIM(s.`contact_name`) <> ''
|
AND NOT EXISTS (
|
SELECT 1 FROM `srm_supplier_contact` c
|
WHERE c.`supplier_id` = s.`id` AND c.`deleted` = b'0'
|
);
|
|
INSERT INTO `srm_supplier_bank`
|
(`supplier_id`, `bank_name`, `bank_account`, `is_default`, `sort`, `creator`, `updater`)
|
SELECT s.`id`, TRIM(s.`bank_name`), TRIM(s.`bank_account`), b'1', 0, 'system', 'system'
|
FROM `srm_supplier` s
|
WHERE s.`deleted` = b'0'
|
AND s.`bank_name` IS NOT NULL
|
AND TRIM(s.`bank_name`) <> ''
|
AND s.`bank_account` IS NOT NULL
|
AND TRIM(s.`bank_account`) <> ''
|
AND NOT EXISTS (
|
SELECT 1 FROM `srm_supplier_bank` b
|
WHERE b.`supplier_id` = s.`id` AND b.`deleted` = b'0'
|
);
|
|
-- -----------------------------------------------------------------------------
|
-- 三、菜单调整
|
-- -----------------------------------------------------------------------------
|
|
-- 3.1 删除旧 SRM 子菜单（准入/资质/绩效/招投标/统计）及其按钮，并移除 SRM 目录
|
-- 供应商档案/询价比价/投标保证金 统一挂到采购管理根菜单(2602) 下
|
DELETE FROM `system_role_menu`
|
WHERE `menu_id` IN (
|
SELECT `id` FROM (
|
SELECT `id` FROM `system_menu`
|
WHERE `id` IN (602524, 602530, 602535, 602540, 602545, 602569,
|
602612, 602613, 602614, 1080070, 1080071)
|
OR `parent_id` IN (602530, 602535, 602540, 602545, 602569,
|
602612, 602613, 602614, 1080070, 1080071)
|
) t
|
);
|
|
DELETE FROM `system_menu`
|
WHERE `id` IN (
|
SELECT `id` FROM (
|
SELECT `id` FROM `system_menu`
|
WHERE `id` IN (602524, 602530, 602535, 602540, 602545, 602569,
|
602612, 602613, 602614, 1080070, 1080071)
|
OR `parent_id` IN (602530, 602535, 602540, 602545, 602569,
|
602612, 602613, 602614, 1080070, 1080071)
|
) t
|
);
|
|
-- 3.2 新菜单先删后插，保证可重复执行（602620-602641 为空号段）
|
DELETE FROM `system_role_menu` WHERE `menu_id` BETWEEN 602620 AND 602641;
|
DELETE FROM `system_menu` WHERE `id` BETWEEN 602620 AND 602641;
|
|
INSERT INTO `system_menu`
|
(`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`)
|
VALUES
|
-- 询价比价
|
(602620, '询价比价', '', 2, 2, 2602, 'inquiry', 'ep:search', 'srm/inquiry/index', 'SrmInquiry', 0),
|
(602621, '询价查询', 'srm:inquiry:query', 3, 1, 602620, '', '', '', '', 0),
|
(602622, '询价创建', 'srm:inquiry:create', 3, 2, 602620, '', '', '', '', 0),
|
(602623, '询价更新', 'srm:inquiry:update', 3, 3, 602620, '', '', '', '', 0),
|
(602624, '询价删除', 'srm:inquiry:delete', 3, 4, 602620, '', '', '', '', 0),
|
(602625, '询价确认转订单', 'srm:inquiry:confirm', 3, 5, 602620, '', '', '', '', 0),
|
(602626, '询价录入报价', 'srm:inquiry:quote', 3, 6, 602620, '', '', '', '', 0),
|
-- 投标保证金
|
(602630, '投标保证金', '', 2, 3, 2602, 'bid-bond', 'ep:money', 'srm/bidBond/index', 'SrmBidBond', 0),
|
(602631, '保证金查询', 'srm:bid-bond:query', 3, 1, 602630, '', '', '', '', 0),
|
(602632, '保证金创建', 'srm:bid-bond:create', 3, 2, 602630, '', '', '', '', 0),
|
(602633, '保证金更新', 'srm:bid-bond:update', 3, 3, 602630, '', '', '', '', 0),
|
(602634, '保证金删除', 'srm:bid-bond:delete', 3, 4, 602630, '', '', '', '', 0),
|
-- 供应商往来（挂在采购管理根下）
|
(602640, '供应商往来', '', 2, 8, 2602, 'supplier-statement', 'ep:wallet', 'erp/purchase/statement/index', 'ErpSupplierStatement', 0),
|
(602641, '供应商往来查询', 'erp:supplier-statement:query', 3, 1, 602640, '', '', '', '', 0);
|
|
-- 3.3 统一采购管理菜单排序：档案/寻源/保证金 → 申请 → 订单 → 来票 → 付款 → 往来
|
UPDATE `system_menu` SET `sort` = 1 WHERE `id` = 602525;
|
UPDATE `system_menu` SET `sort` = 2 WHERE `id` = 602620;
|
UPDATE `system_menu` SET `sort` = 3 WHERE `id` = 602630;
|
UPDATE `system_menu` SET `sort` = 4 WHERE `id` = 602498;
|
UPDATE `system_menu` SET `sort` = 5 WHERE `id` = 2666;
|
UPDATE `system_menu` SET `sort` = 6 WHERE `id` = 1063110;
|
UPDATE `system_menu` SET `sort` = 7 WHERE `id` = 1063116;
|
UPDATE `system_menu` SET `sort` = 8 WHERE `id` = 602640;
|
|
-- 3.4 新菜单授权：授予当前已拥有“供应商档案”(602525) 的角色
|
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `updater`)
|
SELECT r.`role_id`, m.`menu_id`, 'system', 'system'
|
FROM (
|
SELECT DISTINCT `role_id` FROM `system_role_menu`
|
WHERE `menu_id` = 602525 AND `deleted` = b'0'
|
) r
|
CROSS JOIN (
|
SELECT 602620 AS `menu_id`
|
UNION ALL SELECT 602621
|
UNION ALL SELECT 602622
|
UNION ALL SELECT 602623
|
UNION ALL SELECT 602624
|
UNION ALL SELECT 602625
|
UNION ALL SELECT 602626
|
UNION ALL SELECT 602630
|
UNION ALL SELECT 602631
|
UNION ALL SELECT 602632
|
UNION ALL SELECT 602633
|
UNION ALL SELECT 602634
|
UNION ALL SELECT 602640
|
UNION ALL SELECT 602641
|
) m
|
WHERE NOT EXISTS (
|
SELECT 1 FROM `system_role_menu` rm
|
WHERE rm.`role_id` = r.`role_id` AND rm.`menu_id` = m.`menu_id` AND rm.`deleted` = b'0'
|
);
|
|
-- -----------------------------------------------------------------------------
|
-- 四、DROP 废弃空表（执行前已确认 0 行，结构备份见 srm_legacy_tables_backup_20261103.sql）
|
-- -----------------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS `srm_supplier_apply`;
|
DROP TABLE IF EXISTS `srm_supplier_certificate`;
|
DROP TABLE IF EXISTS `srm_supplier_evaluation`;
|
DROP TABLE IF EXISTS `srm_tender_project`;
|
DROP TABLE IF EXISTS `srm_tender_material`;
|
DROP TABLE IF EXISTS `srm_tender_bid`;
|
DROP TABLE IF EXISTS `srm_supplier_quote`;
|
DROP TABLE IF EXISTS `srm_bid_open`;
|
DROP TABLE IF EXISTS `srm_bid_evaluation`;
|
DROP TABLE IF EXISTS `srm_bid_award`;
|
DROP TABLE IF EXISTS `srm_tender_config`;
|
DROP TABLE IF EXISTS `srm_delivery_plan`;
|
DROP TABLE IF EXISTS `srm_purchase_collaboration`;
|
