From fbaeeeff5704f210bc6347454c0f16d255001822 Mon Sep 17 00:00:00 2001
From: gaoluyang <2820782392@qq.com>
Date: 星期三, 04 十一月 2026 16:32:09 +0800
Subject: [PATCH] refactor(srm): 重构供应商采购域，新增询价比价与保证金管理
---
yudao-module-srm/src/main/resources/sql/srm_init.sql | 378 +++++++++++++++++------------------------------------
1 files changed, 122 insertions(+), 256 deletions(-)
diff --git a/yudao-module-srm/src/main/resources/sql/srm_init.sql b/yudao-module-srm/src/main/resources/sql/srm_init.sql
index faef6eb..a7dcc50 100644
--- a/yudao-module-srm/src/main/resources/sql/srm_init.sql
+++ b/yudao-module-srm/src/main/resources/sql/srm_init.sql
@@ -1,19 +1,11 @@
-- =============================================
-- SRM 渚涘簲鍟嗗叧绯荤鐞嗘ā鍧� - 鏁版嵁搴撳垵濮嬪寲鑴氭湰
-- MySQL 8.4+
+--
+-- 淇濈暀鑼冨洿锛氫緵搴斿晢妗ｆ锛堝惈澶氳仈绯讳汉/澶氶摱琛岃处鎴凤級銆佽浠锋瘮浠枫�佹姇鏍囦繚璇侀噾
+-- 宸茬Щ闄わ細渚涘簲鍟嗗噯鍏ャ�佽祫璐ㄣ�佺哗鏁堛�佹嫑鎶曟爣銆佺粺璁″垎鏋愶紙瀵瑰簲琛ㄥ凡鍦�
+-- sql/mysql/srm_procurement_refactor_20261103.sql 涓� DROP锛�
-- =============================================
-
--- 0. 鎷涙姇鏍囬厤缃〃
-CREATE TABLE IF NOT EXISTS srm_tender_config (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '閰嶇疆ID',
- default_bidding_mode VARCHAR(20) NOT NULL DEFAULT 'FULL' COMMENT '榛樿鎷涙姇鏍囨ā寮�: FULL-瀹屾暣鐗�, SIMPLE-绠�鏄撶増',
- 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)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM鎷涙姇鏍囬厤缃〃';
-- 1. 渚涘簲鍟嗕富琛�
CREATE TABLE IF NOT EXISTS srm_supplier (
@@ -29,11 +21,11 @@
legal_person VARCHAR(50) DEFAULT NULL COMMENT '娉曚汉浠ｈ〃',
address VARCHAR(500) DEFAULT NULL COMMENT '鍏徃鍦板潃',
website VARCHAR(200) DEFAULT NULL COMMENT '鍏徃缃戝潃',
- contact_name VARCHAR(50) DEFAULT NULL COMMENT '鑱旂郴浜�',
- contact_mobile VARCHAR(20) DEFAULT NULL COMMENT '鑱旂郴鐢佃瘽',
- contact_email VARCHAR(100) DEFAULT NULL COMMENT '閭',
- bank_name VARCHAR(255) DEFAULT NULL COMMENT '寮�鎴烽摱琛�',
- bank_account VARCHAR(100) DEFAULT NULL COMMENT '閾惰璐﹀彿',
+ contact_name VARCHAR(50) DEFAULT NULL COMMENT '榛樿鑱旂郴浜猴紙涓昏〃鍐椾綑锛�',
+ contact_mobile VARCHAR(20) DEFAULT NULL COMMENT '榛樿鑱旂郴鐢佃瘽锛堜富琛ㄥ啑浣欙級',
+ contact_email VARCHAR(100) DEFAULT NULL COMMENT '榛樿閭锛堜富琛ㄥ啑浣欙級',
+ bank_name VARCHAR(255) DEFAULT NULL COMMENT '榛樿寮�鎴烽摱琛岋紙涓昏〃鍐椾綑锛�',
+ bank_account VARCHAR(100) DEFAULT NULL COMMENT '榛樿閾惰璐﹀彿锛堜富琛ㄥ啑浣欙級',
status INT NOT NULL DEFAULT 0 COMMENT '鐘舵�侊紙0鍚敤 1绂佺敤锛�',
remark VARCHAR(500) DEFAULT NULL COMMENT '澶囨敞',
erp_supplier_id BIGINT DEFAULT NULL COMMENT '鍏宠仈ERP渚涘簲鍟咺D',
@@ -49,7 +41,7 @@
KEY idx_erp_supplier_id (erp_supplier_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗕富琛�';
--- 2. 渚涘簲鍟嗚仈绯讳汉琛�
+-- 2. 渚涘簲鍟嗚仈绯讳汉琛紙鏀寔澶氫釜鑱旂郴浜猴紝浠呬竴涓粯璁わ級
CREATE TABLE IF NOT EXISTS srm_supplier_contact (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鑱旂郴浜篒D',
supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
@@ -70,7 +62,27 @@
KEY idx_supplier_id (supplier_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗚仈绯讳汉琛�';
--- 3. 渚涘簲鍟嗗垎绫诲叧鑱旇〃
+-- 3. 渚涘簲鍟嗛摱琛岃处鎴疯〃锛堟敮鎸佸涓紑鎴疯锛屼粎涓�涓粯璁わ級
+CREATE TABLE IF NOT EXISTS srm_supplier_bank (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '閾惰璐︽埛ID',
+ supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
+ 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),
+ KEY idx_supplier_id (supplier_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗛摱琛岃处鎴疯〃';
+
+-- 4. 渚涘簲鍟嗗垎绫诲叧鑱旇〃
CREATE TABLE IF NOT EXISTS srm_supplier_category (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鍏宠仈ID',
supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
@@ -78,41 +90,23 @@
category_name VARCHAR(100) 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 INT NOT NULL DEFAULT 0 COMMENT '鏄惁鍒犻櫎 0=鍚� 1=鏄�',
PRIMARY KEY (id),
KEY idx_supplier_id (supplier_id),
KEY idx_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗗垎绫诲叧鑱旇〃';
--- 4. 渚涘簲鍟嗗噯鍏ョ敵璇疯〃
-CREATE TABLE IF NOT EXISTS srm_supplier_apply (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鐢宠ID',
- apply_no VARCHAR(64) NOT NULL COMMENT '鐢宠缂栧彿',
- supplier_id BIGINT DEFAULT NULL COMMENT '渚涘簲鍟咺D锛堟柊寤轰负绌猴紝鍑嗗叆鍚庡洖濉級',
- supplier_name VARCHAR(255) NOT NULL COMMENT '渚涘簲鍟嗗悕绉�',
- supplier_type VARCHAR(50) DEFAULT NULL COMMENT '渚涘簲鍟嗙被鍨�',
- company_nature VARCHAR(50) DEFAULT NULL COMMENT '浼佷笟鎬ц川',
- registered_capital DECIMAL(24,6) DEFAULT NULL COMMENT '娉ㄥ唽璧勯噾锛堜竾鍏冿級',
- business_license_no VARCHAR(100) DEFAULT NULL COMMENT '钀ヤ笟鎵х収缂栧彿',
- tax_no VARCHAR(100) DEFAULT NULL COMMENT '绋庡彿',
- address VARCHAR(500) DEFAULT NULL COMMENT '鍏徃鍦板潃',
- bank_name VARCHAR(255) DEFAULT NULL COMMENT '寮�鎴烽摱琛�',
- bank_account VARCHAR(100) DEFAULT NULL COMMENT '閾惰璐﹀彿',
- contact_name VARCHAR(50) DEFAULT NULL COMMENT '鑱旂郴浜�',
- contact_mobile VARCHAR(20) DEFAULT NULL COMMENT '鑱旂郴鐢佃瘽',
- contact_email VARCHAR(100) DEFAULT NULL COMMENT '閭',
- apply_user_id BIGINT DEFAULT NULL COMMENT '鐢宠浜篒D',
- apply_user_name VARCHAR(50) DEFAULT NULL COMMENT '鐢宠浜哄鍚�',
- apply_time DATETIME DEFAULT NULL COMMENT '鐢宠鏃堕棿',
- apply_status INT NOT NULL DEFAULT 0 COMMENT '瀹℃牳鐘舵�侊紙0寰呮彁浜� 10寰呴噰璐鏍� 20寰呰川閲忓鏍� 30寰呰储鍔″鏍� 40宸查�氳繃 50宸叉嫆缁濓級',
- purchase_reviewer_id BIGINT DEFAULT NULL COMMENT '閲囪喘瀹℃牳浜篒D',
- purchase_review_time DATETIME DEFAULT NULL COMMENT '閲囪喘瀹℃牳鏃堕棿',
- purchase_review_opinion VARCHAR(500) DEFAULT NULL COMMENT '閲囪喘瀹℃牳鎰忚',
- quality_reviewer_id BIGINT DEFAULT NULL COMMENT '璐ㄩ噺瀹℃牳浜篒D',
- quality_review_time DATETIME DEFAULT NULL COMMENT '璐ㄩ噺瀹℃牳鏃堕棿',
- quality_review_opinion VARCHAR(500) DEFAULT NULL COMMENT '璐ㄩ噺瀹℃牳鎰忚',
- finance_reviewer_id BIGINT DEFAULT NULL COMMENT '璐㈠姟瀹℃牳浜篒D',
- finance_review_time DATETIME DEFAULT NULL COMMENT '璐㈠姟瀹℃牳鏃堕棿',
- finance_review_opinion VARCHAR(500) DEFAULT NULL COMMENT '璐㈠姟瀹℃牳鎰忚',
+-- 5. 璇环鍗�
+CREATE TABLE IF NOT EXISTS srm_inquiry (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇环鍗旾D',
+ 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 '閲囪喘鍛樼敤鎴稩D',
remark VARCHAR(500) DEFAULT NULL COMMENT '澶囨敞',
creator VARCHAR(64) DEFAULT '' COMMENT '鍒涘缓鑰�',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
@@ -120,116 +114,27 @@
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),
- UNIQUE KEY uk_apply_no (apply_no),
- KEY idx_supplier_id (supplier_id),
- KEY idx_apply_status (apply_status)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗗噯鍏ョ敵璇疯〃';
+ UNIQUE KEY uk_no (no),
+ KEY idx_status (status),
+ KEY idx_inquiry_date (inquiry_date)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM璇环鍗�';
--- 5. 渚涘簲鍟嗚祫璐ㄨ瘉涔﹁〃
-CREATE TABLE IF NOT EXISTS srm_supplier_certificate (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇佷功ID',
- supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
- certificate_type INT NOT NULL COMMENT '璇佷功绫诲瀷锛�1钀ヤ笟鎵х収 2璁よ瘉璇佷功 3妫�娴嬫姤鍛� 4鍏朵粬锛�',
- certificate_name VARCHAR(255) NOT NULL COMMENT '璇佷功鍚嶇О',
- certificate_no VARCHAR(100) DEFAULT NULL COMMENT '璇佷功缂栧彿',
- file_name VARCHAR(255) DEFAULT NULL COMMENT '鏂囦欢鍚嶇О',
- file_url VARCHAR(500) DEFAULT NULL COMMENT '鏂囦欢鍦板潃',
- issuing_authority VARCHAR(255) DEFAULT NULL COMMENT '棰佸彂鏈烘瀯',
- valid_start_date DATE DEFAULT NULL COMMENT '鏈夋晥寮�濮嬫棩鏈�',
- valid_end_date DATE DEFAULT NULL COMMENT '鏈夋晥缁撴潫鏃ユ湡',
- status INT NOT NULL DEFAULT 0 COMMENT '鐘舵�侊紙0鏈夋晥 1杩囨湡 2鍗冲皢杩囨湡锛�',
- 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),
- KEY idx_supplier_id (supplier_id),
- KEY idx_certificate_type (certificate_type),
- KEY idx_valid_end_date (valid_end_date)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗚祫璐ㄨ瘉涔﹁〃';
-
--- 6. 渚涘簲鍟嗙哗鏁堣瘎浠疯〃
-CREATE TABLE IF NOT EXISTS srm_supplier_evaluation (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇勪环ID',
- supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
- evaluation_period VARCHAR(50) NOT NULL COMMENT '璇勪环鍛ㄦ湡锛堝锛�2026-Q1锛�',
- quality_score DECIMAL(5,2) DEFAULT NULL COMMENT '璐ㄩ噺璇勫垎锛堟弧鍒�100锛�',
- quality_pass_rate DECIMAL(5,2) DEFAULT NULL COMMENT '鏉ユ枡鍚堟牸鐜�(%)',
- quality_defect_count INT DEFAULT 0 COMMENT '涓嶈壇娆℃暟',
- delivery_score DECIMAL(5,2) DEFAULT NULL COMMENT '浜や粯璇勫垎锛堟弧鍒�100锛�',
- delivery_on_time_rate DECIMAL(5,2) DEFAULT NULL COMMENT '鍑嗘椂浜や粯鐜�(%)',
- delivery_delay_count INT DEFAULT 0 COMMENT '寤惰繜娆℃暟',
- price_score DECIMAL(5,2) DEFAULT NULL COMMENT '浠锋牸璇勫垎锛堟弧鍒�100锛�',
- service_score DECIMAL(5,2) DEFAULT NULL COMMENT '鏈嶅姟璇勫垎锛堟弧鍒�100锛�',
- service_response_speed INT DEFAULT NULL COMMENT '鍝嶅簲閫熷害锛堣瘎鍒�1-10锛�',
- composite_score DECIMAL(5,2) DEFAULT NULL COMMENT '缁煎悎璇勫垎锛堣嚜鍔ㄨ绠楋級',
- evaluation_level VARCHAR(20) DEFAULT NULL COMMENT '璇勪环绛夌骇锛圓/B/C/D锛�',
- evaluator_id BIGINT DEFAULT NULL COMMENT '璇勪环浜篒D',
- evaluation_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),
- KEY idx_supplier_id (supplier_id),
- KEY idx_evaluation_period (evaluation_period),
- KEY idx_composite_score (composite_score)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗙哗鏁堣瘎浠疯〃';
-
--- 7. 鎷涙爣椤圭洰琛�
-CREATE TABLE IF NOT EXISTS srm_tender_project (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鎷涙爣椤圭洰ID',
- tender_no VARCHAR(64) NOT NULL COMMENT '鎷涙爣缂栧彿',
- tender_name VARCHAR(255) NOT NULL COMMENT '鎷涙爣鍚嶇О',
- tender_type INT NOT NULL COMMENT '鎷涙爣绫诲瀷锛�1鍏紑鎷涙爣 2閭�璇锋嫑鏍� 3绔炰簤鎬ц皥鍒わ級',
- purchase_org VARCHAR(100) DEFAULT NULL COMMENT '閲囪喘缁勭粐',
- project_leader_id BIGINT DEFAULT NULL COMMENT '椤圭洰璐熻矗浜篒D',
- project_leader_name VARCHAR(50) DEFAULT NULL COMMENT '椤圭洰璐熻矗浜哄鍚�',
- tender_status INT NOT NULL DEFAULT 0 COMMENT '鎷涙爣鐘舵�侊紙0鑽夌 1鍙戝竷涓� 2鎶曟爣涓� 3璇勬爣涓� 4宸插畾鏍� 5宸插叧闂級',
- budget_amount DECIMAL(24,6) DEFAULT NULL COMMENT '棰勭畻閲戦',
- start_time DATETIME DEFAULT NULL COMMENT '寮�濮嬫椂闂�',
- end_time DATETIME DEFAULT NULL COMMENT '鎴鏃堕棿',
- bid_open_time DATETIME DEFAULT NULL COMMENT '棰勮寮�鏍囨椂闂�',
- tender_desc TEXT DEFAULT NULL COMMENT '鎷涙爣璇存槑',
- qualification_requirements TEXT DEFAULT NULL COMMENT '璧勮川瑕佹眰',
- purchase_request_id BIGINT DEFAULT NULL COMMENT '鍏宠仈閲囪喘鐢宠ID',
- 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),
- UNIQUE KEY uk_tender_no (tender_no),
- KEY idx_tender_status (tender_status),
- KEY idx_purchase_request_id (purchase_request_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='鎷涙爣椤圭洰琛�';
-
--- 鎷涙爣椤圭洰琛ㄥ鍔� bidding_mode 瀛楁锛堢畝鏄撴ā寮忔敮鎸侊級
-SET @exists = (SELECT COUNT(*) FROM information_schema.columns
- WHERE table_schema = DATABASE() AND table_name = 'srm_tender_project' AND column_name = 'bidding_mode');
-SET @sql = IF(@exists = 0,
- 'ALTER TABLE srm_tender_project ADD COLUMN bidding_mode VARCHAR(20) NOT NULL DEFAULT ''FULL'' COMMENT ''鎷涙姇鏍囨ā寮�: FULL-瀹屾暣鐗�, SIMPLE-绠�鏄撶増'' AFTER tender_status',
- 'SELECT 1');
-PREPARE stmt FROM @sql;
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-
--- 8. 鎷涙爣鐗╂枡鏄庣粏琛�
-CREATE TABLE IF NOT EXISTS srm_tender_material (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鐗╂枡鏄庣粏ID',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
- product_id BIGINT DEFAULT NULL COMMENT '鐗╂枡ID锛堝叧鑱擬DM鐗╂枡锛�',
- product_code VARCHAR(64) DEFAULT NULL COMMENT '鐗╂枡缂栫爜',
- product_name VARCHAR(255) NOT NULL COMMENT '鐗╂枡鍚嶇О',
+-- 6. 璇环鍗曚骇鍝佽
+CREATE TABLE IF NOT EXISTS srm_inquiry_item (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇环浜у搧琛孖D',
+ inquiry_id BIGINT NOT NULL COMMENT '璇环鍗旾D',
+ 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 '瑙勬牸鍨嬪彿',
- quantity DECIMAL(24,6) NOT NULL COMMENT '鏁伴噺',
- unit VARCHAR(20) NOT NULL COMMENT '鍗曚綅',
- tech_requirement TEXT DEFAULT NULL COMMENT '鎶�鏈姹�',
+ unit_name VARCHAR(50) DEFAULT NULL COMMENT '鍗曚綅鍚嶇О',
+ quantity DECIMAL(24,6) NOT NULL DEFAULT 0 COMMENT '閲囪喘鏁伴噺',
estimated_price DECIMAL(24,6) DEFAULT NULL COMMENT '棰勪及鍗曚环',
+ selected_supplier_id BIGINT DEFAULT NULL COMMENT '涓爣渚涘簲鍟咺D',
+ 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 '鍒涘缓鏃堕棿',
@@ -237,131 +142,92 @@
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),
- KEY idx_tender_project_id (tender_project_id),
- KEY idx_product_code (product_code)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='鎷涙爣鐗╂枡鏄庣粏琛�';
+ KEY idx_inquiry_id (inquiry_id),
+ KEY idx_product_id (product_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM璇环鍗曚骇鍝佽';
--- 9. 鎶曟爣璁板綍琛�
-CREATE TABLE IF NOT EXISTS srm_tender_bid (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鎶曟爣ID',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
+-- 7. 璇环鍙備笌渚涘簲鍟�
+CREATE TABLE IF NOT EXISTS srm_inquiry_supplier (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇环鍙備笌渚涘簲鍟咺D',
+ inquiry_id BIGINT NOT NULL COMMENT '璇环鍗旾D',
supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
- bid_no VARCHAR(64) NOT NULL COMMENT '鎶曟爣缂栧彿',
- bid_status INT NOT NULL DEFAULT 0 COMMENT '鎶曟爣鐘舵�侊紙0鎶ュ悕 10宸叉姇鏍� 20鎾ゆ爣 30涓爣 40鏈腑鏍囷級',
- bid_time DATETIME DEFAULT NULL COMMENT '鎶曟爣鏃堕棿',
- bid_total_amount DECIMAL(24,6) DEFAULT NULL COMMENT '鎶曟爣鎬婚噾棰�',
- bid_file_name VARCHAR(255) DEFAULT NULL COMMENT '鎶曟爣鏂囦欢鍚嶇О',
- bid_file_url VARCHAR(500) DEFAULT NULL COMMENT '鎶曟爣鏂囦欢鍦板潃',
- withdraw_reason VARCHAR(500) DEFAULT NULL COMMENT '鎾ゆ爣鍘熷洜',
+ 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),
- UNIQUE KEY uk_bid_no (bid_no),
- KEY idx_tender_project_id (tender_project_id),
- KEY idx_supplier_id (supplier_id),
- KEY idx_bid_status (bid_status)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='鎶曟爣璁板綍琛�';
+ KEY idx_inquiry_id (inquiry_id),
+ KEY idx_supplier_id (supplier_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM璇环鍙備笌渚涘簲鍟�';
--- 10. 渚涘簲鍟嗘姤浠锋槑缁嗚〃
-CREATE TABLE IF NOT EXISTS srm_supplier_quote (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鎶ヤ环ID',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
+-- 8. 璇环鎶ヤ环鏄庣粏
+CREATE TABLE IF NOT EXISTS srm_inquiry_quote (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇环鎶ヤ环ID',
+ inquiry_id BIGINT NOT NULL COMMENT '璇环鍗旾D',
+ inquiry_item_id BIGINT NOT NULL COMMENT '璇环浜у搧琛孖D',
supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
- bid_id BIGINT DEFAULT NULL COMMENT '鎶曟爣璁板綍ID',
- tender_material_id BIGINT NOT NULL COMMENT '鎷涙爣鐗╂枡ID',
- quote_price DECIMAL(24,6) NOT NULL COMMENT '鎶ヤ环浠锋牸',
- tax_rate DECIMAL(5,2) DEFAULT NULL COMMENT '绋庣巼(%)',
+ price DECIMAL(24,6) NOT NULL COMMENT '鎶ヤ环鍗曚环',
+ tax_rate DECIMAL(24,6) DEFAULT NULL COMMENT '绋庣巼锛�%锛�',
delivery_cycle INT DEFAULT NULL COMMENT '浜よ揣鍛ㄦ湡锛堝ぉ锛�',
- payment_terms VARCHAR(500) DEFAULT NULL COMMENT '浠樻鏉′欢',
- warranty_period 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),
- KEY idx_tender_project_id (tender_project_id),
- KEY idx_supplier_id (supplier_id),
- KEY idx_bid_id (bid_id),
- KEY idx_tender_material_id (tender_material_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渚涘簲鍟嗘姤浠锋槑缁嗚〃';
+ KEY idx_inquiry_id (inquiry_id),
+ KEY idx_inquiry_item_id (inquiry_item_id),
+ KEY idx_supplier_id (supplier_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM璇环鎶ヤ环鏄庣粏';
--- 11. 寮�鏍囪褰曡〃
-CREATE TABLE IF NOT EXISTS srm_bid_open (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '寮�鏍嘔D',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
- open_time DATETIME NOT NULL COMMENT '寮�鏍囨椂闂�',
- open_person_id BIGINT DEFAULT NULL COMMENT '寮�鏍囦汉鍛業D',
- open_person_name VARCHAR(50) DEFAULT NULL COMMENT '寮�鏍囦汉鍛樺鍚�',
- open_record TEXT DEFAULT NULL COMMENT '寮�鏍囪褰�',
- attachment_name VARCHAR(255) DEFAULT NULL COMMENT '闄勪欢鍚嶇О',
- attachment_url 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),
- KEY idx_tender_project_id (tender_project_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='寮�鏍囪褰曡〃';
-
--- 12. 璇勬爣璁板綍琛�
-CREATE TABLE IF NOT EXISTS srm_bid_evaluation (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇勬爣ID',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
- supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
- bid_id BIGINT NOT NULL COMMENT '鎶曟爣璁板綍ID',
- price_score DECIMAL(5,2) DEFAULT NULL COMMENT '浠锋牸璇勫垎锛堟弧鍒�40锛�',
- tech_score DECIMAL(5,2) DEFAULT NULL COMMENT '鎶�鏈瘎鍒嗭紙婊″垎30锛�',
- delivery_score DECIMAL(5,2) DEFAULT NULL COMMENT '浜や粯璇勫垎锛堟弧鍒�20锛�',
- service_score DECIMAL(5,2) DEFAULT NULL COMMENT '鏈嶅姟璇勫垎锛堟弧鍒�10锛�',
- composite_score DECIMAL(5,2) DEFAULT NULL COMMENT '缁煎悎璇勫垎锛堣嚜鍔ㄨ绠�=浠锋牸+鎶�鏈�+浜や粯+鏈嶅姟锛�',
- ranking INT DEFAULT NULL COMMENT '鎺掑悕',
- evaluator_id BIGINT DEFAULT NULL COMMENT '璇勬爣浜篒D',
- evaluator_name VARCHAR(50) DEFAULT NULL COMMENT '璇勬爣浜哄鍚�',
- evaluation_opinion VARCHAR(500) DEFAULT NULL COMMENT '璇勬爣鎰忚',
- evaluation_time DATETIME 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),
- KEY idx_tender_project_id (tender_project_id),
- KEY idx_supplier_id (supplier_id),
- KEY idx_bid_id (bid_id),
- KEY idx_ranking (ranking)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='璇勬爣璁板綍琛�';
-
--- 13. 瀹氭爣璁板綍琛�
-CREATE TABLE IF NOT EXISTS srm_bid_award (
- id BIGINT NOT NULL AUTO_INCREMENT COMMENT '瀹氭爣ID',
- tender_project_id BIGINT NOT NULL COMMENT '鎷涙爣椤圭洰ID',
+-- 9. 璇环杞鍗曡褰�
+CREATE TABLE IF NOT EXISTS srm_inquiry_order (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '璇环杞鍗曡褰旾D',
+ inquiry_id BIGINT NOT NULL COMMENT '璇环鍗旾D',
supplier_id BIGINT NOT NULL COMMENT '涓爣渚涘簲鍟咺D',
- bid_id BIGINT NOT NULL COMMENT '涓爣鎶曟爣ID',
- award_no VARCHAR(64) NOT NULL COMMENT '涓爣閫氱煡涔︾紪鍙�',
- award_status INT NOT NULL DEFAULT 0 COMMENT '瀹氭爣鐘舵�侊紙0寰呭鎵� 10宸插畾鏍� 20宸茬敓鎴愯鍗曪級',
- award_amount DECIMAL(24,6) NOT NULL COMMENT '涓爣閲戦',
- award_time DATETIME DEFAULT NULL COMMENT '瀹氭爣鏃堕棿',
- notice_file_name VARCHAR(255) DEFAULT NULL COMMENT '涓爣閫氱煡涔︽枃浠跺悕',
- notice_file_url VARCHAR(500) DEFAULT NULL COMMENT '涓爣閫氱煡涔︽枃浠跺湴鍧�',
- purchase_order_id BIGINT DEFAULT NULL COMMENT '鍏宠仈鐢熸垚鐨勯噰璐鍗旾D',
- purchase_order_no VARCHAR(64) DEFAULT NULL COMMENT '鍏宠仈鐢熸垚鐨勯噰璐鍗曞彿',
- approver_id BIGINT DEFAULT NULL COMMENT '瀹℃壒浜篒D',
- approval_time DATETIME DEFAULT NULL COMMENT '瀹℃壒鏃堕棿',
- remark VARCHAR(500) DEFAULT NULL COMMENT '澶囨敞',
+ purchase_order_id BIGINT DEFAULT NULL COMMENT '鐢熸垚鐨勯噰璐鍗旾D',
+ 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),
- UNIQUE KEY uk_award_no (award_no),
- KEY idx_tender_project_id (tender_project_id),
+ KEY idx_inquiry_id (inquiry_id),
KEY idx_supplier_id (supplier_id),
KEY idx_purchase_order_id (purchase_order_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='瀹氭爣璁板綍琛�';
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM璇环杞噰璐鍗曡褰�';
+
+-- 10. 鎶曟爣淇濊瘉閲戝彴璐�
+CREATE TABLE IF NOT EXISTS srm_bid_bond (
+ id BIGINT NOT NULL AUTO_INCREMENT COMMENT '鎶曟爣淇濊瘉閲慖D',
+ no VARCHAR(32) NOT NULL COMMENT '淇濊瘉閲戝崟鍙�',
+ supplier_id BIGINT NOT NULL COMMENT '渚涘簲鍟咺D',
+ project_name VARCHAR(255) DEFAULT NULL COMMENT '椤圭洰鍚嶇О',
+ inquiry_id BIGINT DEFAULT NULL COMMENT '鍏宠仈璇环鍗旾D',
+ purchase_order_id BIGINT DEFAULT NULL COMMENT '鍏宠仈閲囪喘璁㈠崟ID',
+ bond_amount DECIMAL(24,6) NOT NULL DEFAULT 0 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),
+ UNIQUE KEY uk_no (no),
+ KEY idx_supplier_id (supplier_id),
+ KEY idx_inquiry_id (inquiry_id),
+ KEY idx_refund_status (refund_status)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SRM鎶曟爣淇濊瘉閲戝彴璐�';
--
Gitblit v1.9.3