-- ============================================================= -- 电力数转 - 营销管理(CRM 扩展)阶段十 -- 5 个子项:客户分类 / 信用评级 / 电力报价 / 供用电合同+指令 / 退费+销户 -- 说明:ALTER TABLE ADD COLUMN 不支持 IF NOT EXISTS,重复执行报"字段已存在"可忽略 -- ============================================================= -- 1. 客户档案扩展字段 ALTER TABLE `crm_customer` ADD COLUMN `customer_type` varchar(32) NULL COMMENT '客户分类(政企/工业/居民/商业/农业)' AFTER `remark`; ALTER TABLE `crm_customer` ADD COLUMN `credit_level` varchar(16) NULL COMMENT '当前信用等级' AFTER `customer_type`; ALTER TABLE `crm_customer` ADD COLUMN `credit_score` int NULL COMMENT '当前信用分' AFTER `credit_level`; -- 2. 报价单扩展字段(电力/能源服务报价) ALTER TABLE `crm_sale_quotation` ADD COLUMN `quotation_type` varchar(32) NULL COMMENT '报价类型(general/power/energy)' AFTER `remark`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `capacity` decimal(12,2) NULL COMMENT '合同容量(kVA)' AFTER `quotation_type`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `max_demand` decimal(12,2) NULL COMMENT '最大需量(kW)' AFTER `capacity`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `unit_price` decimal(10,4) NULL COMMENT '电度单价(元/kWh)' AFTER `max_demand`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `tariff_mode` varchar(32) NULL COMMENT '电价模式(single/two-part/tier/tou)' AFTER `unit_price`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `power_factor_fee` decimal(12,2) NULL COMMENT '力调电费(元)' AFTER `tariff_mode`; ALTER TABLE `crm_sale_quotation` ADD COLUMN `billing_cycle` int NULL COMMENT '结算周期(月)' AFTER `power_factor_fee`; -- 3. 合同扩展字段(供用电/能源服务合同) ALTER TABLE `crm_contract` ADD COLUMN `contract_type` varchar(32) NULL COMMENT '合同类型(power/energy/resident/solar)' AFTER `remark`; ALTER TABLE `crm_contract` ADD COLUMN `voltage_level` varchar(32) NULL COMMENT '电压等级' AFTER `contract_type`; ALTER TABLE `crm_contract` ADD COLUMN `electricity_type` varchar(32) NULL COMMENT '用电类型' AFTER `voltage_level`; ALTER TABLE `crm_contract` ADD COLUMN `contract_capacity` decimal(12,2) NULL COMMENT '合同容量(kVA)' AFTER `electricity_type`; ALTER TABLE `crm_contract` ADD COLUMN `tariff_mode` varchar(32) NULL COMMENT '电价模式' AFTER `contract_capacity`; ALTER TABLE `crm_contract` ADD COLUMN `meter_mode` varchar(32) NULL COMMENT '计量方式' AFTER `tariff_mode`; ALTER TABLE `crm_contract` ADD COLUMN `pay_cycle` int NULL COMMENT '缴费周期(月)' AFTER `meter_mode`; -- 4. 信用评级表 CREATE TABLE IF NOT EXISTS `crm_credit_rating` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `customer_id` bigint NOT NULL COMMENT '客户ID', `customer_name` varchar(255) NULL COMMENT '客户名称', `level` varchar(16) NULL COMMENT '信用等级(A+/A/B+/B/C/C-/D)', `score` int NULL COMMENT '信用分(0-100)', `eval_type` varchar(32) NULL COMMENT '评定类型(初评/复评/动态调整)', `eval_reason` varchar(500) NULL COMMENT '评定理由', `operator_id` bigint NULL COMMENT '操作人ID', `operator_name` varchar(64) NULL COMMENT '操作人姓名', `operate_time` datetime NULL COMMENT '操作时间', `valid_until` datetime NULL COMMENT '有效期至', `remark` varchar(500) 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_customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM 客户信用评级'; -- 5. 合同指令表 CREATE TABLE IF NOT EXISTS `crm_contract_command` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `contract_id` bigint NOT NULL COMMENT '合同ID', `contract_no` varchar(64) NULL COMMENT '合同编号', `command_type` varchar(32) NULL COMMENT '指令类型(下发表/抄表任务/服务工单)', `target_module` varchar(64) NULL COMMENT '目标模块', `target_id` bigint NULL COMMENT '目标记录ID', `biz_no` varchar(64) NULL COMMENT '业务单号', `operator_id` bigint NULL COMMENT '操作人ID', `operator_name` varchar(64) NULL COMMENT '操作人姓名', `operate_time` datetime NULL COMMENT '操作时间', `status` int NULL DEFAULT 0 COMMENT '状态(0待执行/10已下发/20已完成/30已取消)', `remark` varchar(500) 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_contract_id` (`contract_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM 合同指令下发'; -- 6. 退费单表 CREATE TABLE IF NOT EXISTS `crm_refund` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `refund_no` varchar(64) NOT NULL COMMENT '退费单号', `customer_id` bigint NOT NULL COMMENT '客户ID', `customer_name` varchar(255) NULL COMMENT '客户名称', `contract_id` bigint NULL COMMENT '合同ID', `contract_no` varchar(64) NULL COMMENT '合同编号', `refund_type` varchar(32) NULL COMMENT '退费类型(电费退费/服务退订/容量退费)', `refund_amount` decimal(12,2) NULL COMMENT '退费金额', `refund_reason` varchar(500) NULL COMMENT '退费原因', `apply_user_id` bigint NULL COMMENT '申请用户ID', `apply_user_name` varchar(64) NULL COMMENT '申请用户姓名', `apply_time` datetime NULL COMMENT '申请时间', `audit_status` int NULL DEFAULT 0 COMMENT '审批状态(0草稿/10待审批/20通过/30驳回)', `process_instance_id` varchar(64) NULL COMMENT '流程实例ID', `remark` varchar(500) 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_refund_no` (`refund_no`), KEY `idx_customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM 退费单'; -- 7. 销户申请表 CREATE TABLE IF NOT EXISTS `crm_cancellation` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `cancellation_no` varchar(64) NOT NULL COMMENT '销户编号', `customer_id` bigint NOT NULL COMMENT '客户ID', `customer_name` varchar(255) NULL COMMENT '客户名称', `contract_id` bigint NULL COMMENT '合同ID', `contract_no` varchar(64) NULL COMMENT '合同编号', `cancellation_type` varchar(32) NULL COMMENT '销户类型(用电销户/服务终止/合同到期)', `cancel_reason` varchar(500) NULL COMMENT '销户原因', `expected_cancel_date` date NULL COMMENT '预计销户日期', `actual_cancel_date` date NULL COMMENT '实际销户日期', `refund_amount` decimal(12,2) NULL COMMENT '退费金额', `apply_user_id` bigint NULL COMMENT '申请用户ID', `apply_user_name` varchar(64) NULL COMMENT '申请用户姓名', `apply_time` datetime NULL COMMENT '申请时间', `audit_status` int NULL DEFAULT 0 COMMENT '审批状态(0草稿/10待审批/20通过/30驳回)', `process_instance_id` varchar(64) NULL COMMENT '流程实例ID', `remark` varchar(500) 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_cancellation_no` (`cancellation_no`), KEY `idx_customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM 销户申请'; -- ==================== 字典 ==================== INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ('客户分类', 'crm_customer_type', 0, '电力客户分类', '1', NOW(), '1', NOW(), b'0', NULL), ('客户信用等级', 'crm_credit_level', 0, '电力客户信用等级', '1', NOW(), '1', NOW(), b'0', NULL), ('报价单类型', 'crm_quotation_type', 0, '通用/电力/能源服务报价', '1', NOW(), '1', NOW(), b'0', NULL), ('电价模式', 'crm_tariff_mode', 0, '单一制/两部制/阶梯/峰谷分时', '1', NOW(), '1', NOW(), b'0', NULL), ('合同类型', 'crm_contract_type', 0, '供用电/能源服务/居配/光伏', '1', NOW(), '1', NOW(), b'0', NULL), ('电压等级', 'crm_voltage_level', 0, '电力电压等级', '1', NOW(), '1', NOW(), b'0', NULL), ('用电类型', 'crm_electricity_type', 0, '大工业/一般工商业/居民/农业', '1', NOW(), '1', NOW(), b'0', NULL), ('计量方式', 'crm_meter_mode', 0, '高供高计/高供低计/低供低计', '1', NOW(), '1', NOW(), b'0', NULL), ('合同指令类型', 'crm_command_type', 0, '下发表/抄表任务/服务工单', '1', NOW(), '1', NOW(), b'0', NULL), ('合同指令状态', 'crm_command_status', 0, '待执行/已下发/已完成/已取消', '1', NOW(), '1', NOW(), b'0', NULL), ('退费类型', 'crm_refund_type', 0, '电费退费/服务退订/容量退费', '1', NOW(), '1', NOW(), b'0', NULL), ('销户类型', 'crm_cancellation_type', 0, '用电销户/服务终止/合同到期', '1', NOW(), '1', NOW(), b'0', NULL) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1, '政企客户', 'government', 'crm_customer_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '工业用户', 'industrial', 'crm_customer_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '居民用户', 'resident', 'crm_customer_type', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (4, '商业用户', 'commercial', 'crm_customer_type', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (5, '农业用电', 'agricultural', 'crm_customer_type', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (1, 'A+', 'A+', 'crm_credit_level', 0, 'success', '', '信用优秀', '1', NOW(), '1', NOW(), b'0'), (2, 'A', 'A', 'crm_credit_level', 0, 'success', '', '信用良好', '1', NOW(), '1', NOW(), b'0'), (3, 'B+', 'B+', 'crm_credit_level', 0, 'primary', '', '信用较好', '1', NOW(), '1', NOW(), b'0'), (4, 'B', 'B', 'crm_credit_level', 0, 'primary', '', '信用一般', '1', NOW(), '1', NOW(), b'0'), (5, 'C', 'C', 'crm_credit_level', 0, 'warning', '', '信用较差', '1', NOW(), '1', NOW(), b'0'), (6, 'C-', 'C-', 'crm_credit_level', 0, 'warning', '', '信用差', '1', NOW(), '1', NOW(), b'0'), (7, 'D', 'D', 'crm_credit_level', 0, 'danger', '', '信用极差', '1', NOW(), '1', NOW(), b'0'), (1, '通用报价', 'general', 'crm_quotation_type', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '电力报价', 'power', 'crm_quotation_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '能源服务报价', 'energy', 'crm_quotation_type', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '单一制', 'single', 'crm_tariff_mode', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '两部制', 'two-part', 'crm_tariff_mode', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '阶梯', 'tier', 'crm_tariff_mode', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (4, '峰谷分时', 'tou', 'crm_tariff_mode', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '供用电合同', 'power', 'crm_contract_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '能源服务合同', 'energy', 'crm_contract_type', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '居配合同', 'resident', 'crm_contract_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (4, '光伏合同', 'solar', 'crm_contract_type', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '10kV', '10kV', 'crm_voltage_level', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '35kV', '35kV', 'crm_voltage_level', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '110kV', '110kV', 'crm_voltage_level', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (4, '220kV', '220kV', 'crm_voltage_level', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (5, '380V', '380V', 'crm_voltage_level', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (6, '220V', '220V', 'crm_voltage_level', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '大工业', 'large-industrial', 'crm_electricity_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '一般工商业', 'general-commercial', 'crm_electricity_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '居民', 'residential', 'crm_electricity_type', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (4, '农业', 'agricultural', 'crm_electricity_type', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '高供高计', 'high-high', 'crm_meter_mode', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '高供低计', 'high-low', 'crm_meter_mode', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '低供低计', 'low-low', 'crm_meter_mode', 0, 'default', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '下发表', 'meter-issuance', 'crm_command_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '抄表任务', 'meter-read', 'crm_command_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '服务工单', 'service-workorder', 'crm_command_type', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (0, '待执行', '0', 'crm_command_status', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '已下发', '10', 'crm_command_status', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '已完成', '20', 'crm_command_status', 0, 'success', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '已取消', '30', 'crm_command_status', 0, 'danger', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '电费退费', 'electricity', 'crm_refund_type', 0, 'primary', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '服务退订', 'service-cancel', 'crm_refund_type', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '容量退费', 'capacity', 'crm_refund_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0'), (1, '用电销户', 'electricity', 'crm_cancellation_type', 0, 'danger', '', '', '1', NOW(), '1', NOW(), b'0'), (2, '服务终止', 'service-terminate', 'crm_cancellation_type', 0, 'warning', '', '', '1', NOW(), '1', NOW(), b'0'), (3, '合同到期', 'contract-expire', 'crm_cancellation_type', 0, 'info', '', '', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- ==================== 菜单(CRM 系统 2397 下) ==================== 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 (1065262, '信用评级', '', 2, 1, 2397, 'credit-rating', 'ep:medal', 'crm/credit-rating/index', 'CrmCreditRating', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065263, '信用评级查询', 'crm:credit-rating:query', 3, 1, 1065262, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065264, '信用评级创建', 'crm:credit-rating:create', 3, 2, 1065262, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065265, '信用评级更新', 'crm:credit-rating:update', 3, 3, 1065262, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065266, '信用评级删除', 'crm:credit-rating:delete', 3, 4, 1065262, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065267, '退费单', '', 2, 2, 2397, 'refund', 'ep:refresh-left', 'crm/refund/index', 'CrmRefund', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065268, '退费单查询', 'crm:refund:query', 3, 1, 1065267, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065269, '退费单创建', 'crm:refund:create', 3, 2, 1065267, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065270, '退费单更新', 'crm:refund:update', 3, 3, 1065267, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065271, '退费单删除', 'crm:refund:delete', 3, 4, 1065267, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065272, '退费单导出', 'crm:refund:export', 3, 5, 1065267, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065273, '销户申请', '', 2, 3, 2397, 'cancellation', 'ep:circle-close', 'crm/cancellation/index', 'CrmCancellation', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065274, '销户申请查询', 'crm:cancellation:query', 3, 1, 1065273, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065275, '销户申请创建', 'crm:cancellation:create', 3, 2, 1065273, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065276, '销户申请更新', 'crm:cancellation:update', 3, 3, 1065273, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065277, '销户申请删除', 'crm:cancellation:delete', 3, 4, 1065273, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065278, '销户申请导出', 'crm:cancellation:export', 3, 5, 1065273, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- 角色授权(普通角色2 / 租户管理员109、111) INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) SELECT r.role_id, m.menu_id, '1', NOW(), '1', NOW(), b'0', 1 FROM (SELECT 2 AS role_id UNION ALL SELECT 109 UNION ALL SELECT 111) r CROSS JOIN (SELECT 1065262 AS menu_id UNION ALL SELECT 1065263 UNION ALL SELECT 1065264 UNION ALL SELECT 1065265 UNION ALL SELECT 1065266 UNION ALL SELECT 1065267 UNION ALL SELECT 1065268 UNION ALL SELECT 1065269 UNION ALL SELECT 1065270 UNION ALL SELECT 1065271 UNION ALL SELECT 1065272 UNION ALL SELECT 1065273 UNION ALL SELECT 1065274 UNION ALL SELECT 1065275 UNION ALL SELECT 1065276 UNION ALL SELECT 1065277 UNION ALL SELECT 1065278) m LEFT JOIN `system_role_menu` existing ON existing.role_id = r.role_id AND existing.menu_id = m.menu_id AND existing.deleted = b'0' WHERE existing.id IS NULL;