1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
| -- 客户申报与项目业务升级脚本
| -- 适用库：product-inventory-management-xmgl
| -- 执行前必须先做全库备份；本脚本不删除现有表和数据。
| -- 建议在测试库验证后再执行生产库。
|
| SET NAMES utf8mb4;
| SET FOREIGN_KEY_CHECKS = 0;
|
| CREATE TABLE IF NOT EXISTS `customer_submission` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
| `customer_id` bigint NOT NULL COMMENT '客户ID',
| `report_year` int NOT NULL COMMENT '报告年度',
| `report_name` varchar(255) DEFAULT NULL COMMENT '材料名称',
| `material_files` text COMMENT '客户材料附件，逗号分隔',
| `status` tinyint NOT NULL DEFAULT 0 COMMENT '0待审核 1处理中 2已发布 3已退回',
| `result_text` varchar(2000) DEFAULT NULL COMMENT '发布结果说明',
| `result_files` text COMMENT '结果附件，逗号分隔',
| `review_remark` varchar(1000) DEFAULT NULL COMMENT '审核意见',
| `submit_user_id` bigint DEFAULT NULL COMMENT '提交人ID',
| `submit_user_name` varchar(100) DEFAULT NULL COMMENT '提交人名称',
| `publish_user_id` bigint DEFAULT NULL COMMENT '发布人ID',
| `publish_user_name` varchar(100) DEFAULT NULL COMMENT '发布人名称',
| `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
| `tenant_id` bigint DEFAULT NULL COMMENT '租户ID',
| `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
| `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '删除标记',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_customer_report_year` (`customer_id`,`report_year`,`is_deleted`),
| KEY `idx_submission_status` (`status`),
| KEY `idx_submission_user` (`submit_user_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户年度申报材料与结果';
|
| DROP PROCEDURE IF EXISTS `add_column_if_missing`;
| DELIMITER $$
| CREATE PROCEDURE `add_column_if_missing`(
| IN p_table varchar(64), IN p_column varchar(64), IN p_definition varchar(1000)
| )
| BEGIN
| IF NOT EXISTS (
| SELECT 1 FROM information_schema.COLUMNS
| WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_column
| ) THEN
| SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
| PREPARE stmt FROM @ddl;
| EXECUTE stmt;
| DEALLOCATE PREPARE stmt;
| END IF;
| END$$
| DELIMITER ;
|
| CALL add_column_if_missing('sales_ledger', 'business_approval_status', "tinyint NOT NULL DEFAULT 0 COMMENT '0草稿 1待审批 2已通过 3已退回'");
| CALL add_column_if_missing('sales_ledger', 'business_approval_remark', "varchar(1000) DEFAULT NULL COMMENT '审批意见'");
| CALL add_column_if_missing('sales_ledger', 'business_approved_by', "bigint DEFAULT NULL COMMENT '审批人ID'");
| CALL add_column_if_missing('sales_ledger', 'business_approved_name', "varchar(100) DEFAULT NULL COMMENT '审批人名称'");
| CALL add_column_if_missing('sales_ledger', 'business_approved_time', "datetime DEFAULT NULL COMMENT '审批时间'");
| CALL add_column_if_missing('sales_ledger', 'business_submitted_time', "datetime DEFAULT NULL COMMENT '提交审批时间'");
| CALL add_column_if_missing('sales_ledger', 'project_management_info_id', "bigint DEFAULT NULL COMMENT '审批通过后生成的项目ID'");
| DROP PROCEDURE IF EXISTS `add_column_if_missing`;
|
| -- 固定六阶段模板；重复执行不会重复创建。
| INSERT INTO `project_management_plan` (`name`, `description`, `is_delete`, `create_time`, `update_time`, `create_user_name`)
| SELECT '项目申报六阶段', '业务审批通过后自动生成：立项、团队分工、资料汇报、申报、申报结果、验收', 0, NOW(), NOW(), '升级脚本'
| WHERE NOT EXISTS (
| SELECT 1 FROM `project_management_plan` WHERE `name` = '项目申报六阶段' AND `is_delete` = 0
| );
| SET @plan_id = (SELECT `id` FROM `project_management_plan` WHERE `name` = '项目申报六阶段' AND `is_delete` = 0 ORDER BY `id` LIMIT 1);
|
| INSERT INTO `project_management_plan_node`
| (`project_management_plan_id`, `sort`, `name`, `estimated_duration`, `work_content`, `is_delete`, `create_time`, `update_time`, `create_user_name`)
| SELECT @plan_id, 1, '立项', 1, '建立项目并确认负责人、计划周期和基础资料', 0, NOW(), NOW(), '升级脚本'
| WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='立项' AND `is_delete`=0);
| INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
| SELECT @plan_id,2,'团队分工',1,'确定项目成员与工作分工',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='团队分工' AND `is_delete`=0);
| INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
| SELECT @plan_id,3,'资料汇报',1,'收集、补充并汇总申报资料',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='资料汇报' AND `is_delete`=0);
| INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
| SELECT @plan_id,4,'申报',1,'完成正式申报并登记申报信息',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='申报' AND `is_delete`=0);
| INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
| SELECT @plan_id,5,'申报结果',1,'登记申报结果并形成客户发布材料',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='申报结果' AND `is_delete`=0);
| INSERT INTO `project_management_plan_node` (`project_management_plan_id`,`sort`,`name`,`estimated_duration`,`work_content`,`is_delete`,`create_time`,`update_time`,`create_user_name`)
| SELECT @plan_id,6,'验收',1,'完成交付、验收和项目归档',0,NOW(),NOW(),'升级脚本' WHERE NOT EXISTS (SELECT 1 FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `name`='验收' AND `is_delete`=0);
|
| -- 客户门户菜单。
| INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
| SELECT '客户服务',0,7,'customerPortal',NULL,'CustomerPortal',1,0,'M','0','0','customer:portal','#','admin',NOW(),'客户材料与结果门户'
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `path`='customerPortal' AND `parent_id`=0);
| SET @portal_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `path`='customerPortal' AND `parent_id`=0 ORDER BY `menu_id` LIMIT 1);
|
| INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
| SELECT '我的申报',@portal_menu_id,1,'mySubmission','customerPortal/mySubmission/index','MySubmission',1,0,'C','0','0','customer:submission:mine','upload','admin',NOW(),'客户上传近三年材料并查看结果'
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='mySubmission');
| INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
| SELECT '申报管理',@portal_menu_id,2,'submissionManagement','customerPortal/submissionManagement/index','SubmissionManagement',1,0,'C','0','0','customer:submission:manage','list','admin',NOW(),'内部人员审核并发布客户申报结果'
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='submissionManagement');
| SET @my_submission_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `parent_id`=@portal_menu_id AND `path`='mySubmission' ORDER BY `menu_id` LIMIT 1);
|
| -- 销售台账中的业务审批按钮权限。已有销售台账权限的角色自动获得“提交”，
| -- “审批”不自动授予，由管理员在现有角色管理中分配给管理人员。
| SET @sales_ledger_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `component`='salesManagement/salesLedger/index' ORDER BY `menu_id` LIMIT 1);
| INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
| SELECT '提交业务审批',@sales_ledger_menu_id,20,'#',NULL,'',1,0,'F','0','0','business:approval:submit','#','admin',NOW(),'提交业务进入管理审批'
| WHERE @sales_ledger_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms`='business:approval:submit');
| INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`)
| SELECT '审核业务',@sales_ledger_menu_id,21,'#',NULL,'',1,0,'F','0','0','business:approval:review','#','admin',NOW(),'审批通过后自动生成项目'
| WHERE @sales_ledger_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms`='business:approval:review');
| SET @business_submit_menu_id = (SELECT `menu_id` FROM `sys_menu` WHERE `perms`='business:approval:submit' ORDER BY `menu_id` LIMIT 1);
| INSERT IGNORE INTO `sys_role_menu` (`role_id`,`menu_id`)
| SELECT `role_id`, @business_submit_menu_id FROM `sys_role_menu`
| WHERE `menu_id`=@sales_ledger_menu_id AND @business_submit_menu_id IS NOT NULL;
|
| -- 客户角色只授予客户门户和“我的申报”，账号仍由管理员在现有用户管理中创建。
| INSERT INTO `sys_role` (`role_name`,`role_key`,`role_sort`,`data_scope`,`menu_check_strictly`,`dept_check_strictly`,`status`,`del_flag`,`create_by`,`create_time`,`remark`)
| SELECT '客户','customer',90,'5',1,1,'0','0','admin',NOW(),'外部客户账号；不开放自行注册'
| WHERE NOT EXISTS (SELECT 1 FROM `sys_role` WHERE `role_key`='customer' AND `del_flag`='0');
| SET @customer_role_id = (SELECT `role_id` FROM `sys_role` WHERE `role_key`='customer' AND `del_flag`='0' ORDER BY `role_id` LIMIT 1);
| INSERT IGNORE INTO `sys_role_menu` (`role_id`,`menu_id`) VALUES (@customer_role_id,@portal_menu_id),(@customer_role_id,@my_submission_menu_id);
|
| SET FOREIGN_KEY_CHECKS = 1;
|
| -- 执行后检查
| SELECT 'customer_submission' AS item, COUNT(*) AS count_value FROM `customer_submission`
| UNION ALL SELECT 'six_stage_nodes', COUNT(*) FROM `project_management_plan_node` WHERE `project_management_plan_id`=@plan_id AND `is_delete`=0
| UNION ALL SELECT 'customer_portal_menus', COUNT(*) FROM `sys_menu` WHERE `menu_id`=@portal_menu_id OR `parent_id`=@portal_menu_id;
|
|
