-- ===================================================== -- 报价与投标闭环 -- 1. crm_bid_bond 增加关联报价单字段(投标绑定报价) -- 2. crm_sale_quotation 增加是否需要投标、投标关联、报价轮次、作废字段 -- 3. 新建 crm_sale_quotation_revision(每轮报价流水快照) -- 4. 菜单:销售报价-作废、投标保证金-导出 -- 幂等:可重复执行;不含 tenant_id -- ===================================================== -- ---------- 1. crm_bid_bond ---------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_bid_bond' AND column_name = 'quotation_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_bid_bond` ADD COLUMN `quotation_id` BIGINT NULL COMMENT ''关联报价单ID'' AFTER `tender_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_bid_bond' AND column_name = 'quotation_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_bid_bond` ADD COLUMN `quotation_no` VARCHAR(32) NULL COMMENT ''关联报价单号快照'' AFTER `quotation_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_bid_bond' AND column_name = 'quotation_round'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_bid_bond` ADD COLUMN `quotation_round` INT NOT NULL DEFAULT 0 COMMENT ''当前报价轮次'' AFTER `quotation_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'crm_bid_bond' AND index_name = 'idx_quotation_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_bid_bond` ADD INDEX `idx_quotation_id` (`quotation_id`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 2. crm_sale_quotation ---------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'need_bid'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `need_bid` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否需要投标:1是 0否'' AFTER `contract_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'bid_bond_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `bid_bond_id` BIGINT NULL COMMENT ''关联投标(保证金)ID'' AFTER `contract_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'bid_bond_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `bid_bond_no` VARCHAR(32) NULL COMMENT ''投标单号快照'' AFTER `bid_bond_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'revision_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `revision_no` INT NOT NULL DEFAULT 0 COMMENT ''已提交报价轮次'' AFTER `bid_bond_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'void_type'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `void_type` TINYINT NULL COMMENT ''作废类型:1手动作废 2超期作废'' AFTER `revision_no`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'void_reason'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `void_reason` VARCHAR(500) NULL COMMENT ''作废原因'' AFTER `void_type`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'void_time'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `void_time` DATETIME NULL COMMENT ''作废时间'' AFTER `void_reason`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND column_name = 'void_by'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD COLUMN `void_by` BIGINT NULL COMMENT ''作废人用户ID'' AFTER `void_time`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'crm_sale_quotation' AND index_name = 'idx_bid_bond_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `crm_sale_quotation` ADD INDEX `idx_bid_bond_id` (`bid_bond_id`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 3. crm_sale_quotation_revision ---------- CREATE TABLE IF NOT EXISTS `crm_sale_quotation_revision` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号', `quotation_id` BIGINT NOT NULL COMMENT '报价单ID', `quotation_no` VARCHAR(32) NOT NULL COMMENT '报价单号快照', `revision_no` INT NOT NULL COMMENT '报价轮次', `bid_bond_id` BIGINT DEFAULT NULL COMMENT '当时绑定的投标ID', `bid_bond_no` VARCHAR(32) DEFAULT NULL COMMENT '当时绑定的投标单号', `customer_id` BIGINT DEFAULT NULL COMMENT '客户ID', `name` VARCHAR(255) DEFAULT NULL COMMENT '报价单名称快照', `quotation_time` DATETIME DEFAULT NULL COMMENT '报价日期', `valid_until` DATETIME DEFAULT NULL COMMENT '有效期至', `total_product_price` DECIMAL(24,6) DEFAULT NULL COMMENT '产品总金额', `discount_percent` DECIMAL(24,6) DEFAULT NULL COMMENT '整单折扣', `discount_price` DECIMAL(24,6) DEFAULT NULL COMMENT '优惠金额', `total_price` DECIMAL(24,6) DEFAULT NULL COMMENT '报价总金额', `tax_rate` DECIMAL(24,6) DEFAULT NULL COMMENT '税率', `tax_price` DECIMAL(24,6) DEFAULT NULL COMMENT '税额', `items_snapshot` LONGTEXT COMMENT '明细JSON快照', `status` TINYINT DEFAULT NULL COMMENT '提交时状态', `process_instance_id` VARCHAR(64) DEFAULT NULL COMMENT '流程实例编号', `submit_user_id` BIGINT DEFAULT NULL COMMENT '提交人用户ID', `submit_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`), UNIQUE KEY `uk_quotation_revision` (`quotation_id`, `revision_no`), KEY `idx_quotation_id` (`quotation_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='CRM销售报价轮次流水'; -- ---------- 4. 菜单 ---------- INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (602650, '销售报价-作废', 'crm:sale-quotation:void', 3, 10, 602512, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (602651, '投标保证金-导出', 'crm:bid-bond:export', 3, 9, 602630, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `permission` = VALUES(`permission`), `parent_id` = VALUES(`parent_id`), `sort` = VALUES(`sort`), `status` = VALUES(`status`), `deleted` = b'0';