fix : 修改数据同步逻辑

1 files changed, 106 insertions, 8 deletions

diff --git a/src/main/java/com/nis/handler/SqlHandler.java b/src/main/java/com/nis/handler/SqlHandler.java
index 9d7b6ca..ebc973c 100644
--- a/src/main/java/com/nis/handler/SqlHandler.java
+++ b/src/main/java/com/nis/handler/SqlHandler.java
@@ -1,12 +1,32 @@
 package com.nis.handler;
 
+import java.io.BufferedReader;
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.IOException;
+import java.io.InputStream;
+import java.sql.SQLException;
 import java.util.List;
+import java.util.Map;
+import java.util.function.Function;
+import java.util.stream.Collectors;
+import java.util.stream.Stream;
 
+import javax.sql.DataSource;
+
+import org.apache.commons.io.IOUtils;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.beans.factory.annotation.Value;
+import org.springframework.core.io.ClassPathResource;
+import org.springframework.core.io.Resource;
+import org.springframework.jdbc.datasource.init.ScriptException;
+import org.springframework.jdbc.datasource.init.ScriptUtils;
 import org.springframework.stereotype.Service;
+import org.springframework.util.ResourceUtils;
 
 import com.nis.dao.SqlDao;
+import com.nis.entity.SysConfigEntity;
+import com.nis.entity.SysUserEntity;
 import com.nis.service.AlertMessageService;
 import com.nis.service.AssetAssetService;
 import com.nis.service.AssetBrandService;
@@ -14,8 +34,12 @@ import com.nis.service.AssetModelService;
 import com.nis.service.ChartService;
 import com.nis.service.DcService;
 import com.nis.service.EndpointService;
+import com.nis.service.SysConfigService;
+import com.nis.service.SysUserService;
 import com.nis.util.Constant;
+import com.nis.util.ToolUtil;
 
+import cn.hutool.core.io.IoUtil;
 import cn.hutool.core.util.StrUtil;
 import cn.hutool.log.Log;
 
@@ -53,6 +77,15 @@ public class SqlHandler {
 	@Autowired
 	private ChartService chartService;
 	
+	@Autowired
+	private DataSource dataSource;
+	
+	@Autowired
+	private SysUserService sysUserService;
+	
+	@Autowired
+	private SysConfigService sysConfigService;
+	
 	public void removeData() {
 		String assetPing = "delete from asset_ping";
 		sqlDao.execute(assetPing);
@@ -140,7 +173,8 @@ public class SqlHandler {
 		
 		String sysMenu = "ALTER TABLE `sys_menu` \r\n"
 				+ "ADD COLUMN `description` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,\r\n"
-				+ "ADD COLUMN `icon` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'nz-icon nz-alert-add';";
+				+ "ADD COLUMN `icon` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'nz-icon nz-alert-add',"
+				+ "ADD COLUMN `required` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '必要权限，如编辑按钮需要 勾选查看权限 填写内容为 sys_menu.id，多个逗号分隔 1,2';";
 		sqlDao.execute(sysMenu);
 		
 		String alertRule = "ALTER TABLE `alert_rule` \r\n"
@@ -198,11 +232,11 @@ public class SqlHandler {
 		 		+ "  `id` int(10) NOT NULL AUTO_INCREMENT,\r\n"
 		 		+ "  `message_id` int(10) NOT NULL COMMENT '关联 alert_message.id',\r\n"
 		 		+ "  `user_id` int(10) NOT NULL COMMENT '关联 sys_user.id',\r\n"
-		 		+ "  `method` varchar(128) NOT NULL COMMENT '通知方式	VARCHARemail 或 NOTIFICATION_SCRIPT.name',\r\n"
 		 		+ "  `message_state` char(1) NOT NULL COMMENT '消息状态1: active2: expired',\r\n"
 		 		+ "  `state` char(1) NOT NULL COMMENT '通知状态0:失败1:成功',\r\n"
 		 		+ "  `error_msg` varchar(1024) NOT NULL DEFAULT '' COMMENT '发送错误信息',\r\n"
 		 		+ "  `time` datetime NOT NULL COMMENT '发送时间',\r\n"
+		 		+ "  `method_id` int(10) NOT NULL COMMENT '通知方式id 关联ALERT_NOTIFICATION_METHOD.id',\r\n"
 		 		+ "  PRIMARY KEY (`id`) USING BTREE\r\n"
 		 		+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
 		 
@@ -672,9 +706,18 @@ public class SqlHandler {
 	 * asset_type_conf
 	 * monitor_event_log
 	 * sys_timezone
+	 * @throws IOException 
+	 * @throws SQLException 
+	 * @throws ScriptException 
 	 */
-	public void initData() {
-		StringBuilder sb = new StringBuilder();
+	public void initData() throws ScriptException, SQLException {
+		Resource classPathResource = new ClassPathResource("db/init.sql");
+        ScriptUtils.executeSqlScript(dataSource.getConnection(), classPathResource);
+        // asset_brand表数据单独处理
+ 		assetBrandService.handler();
+ 		// asset_model表数据处理 前提先处理好brand表数据信息
+ 		assetModelService.handler();
+		/*StringBuilder sb = new StringBuilder();
 		sb.append("INSERT INTO `asset_field_group`(`id`, `name`, `remark`, `build_in`, `seq`) VALUES (1, 'default', 'Default meta group', '1', '');");
 		sb.append("INSERT INTO `alert_severity_conf`(`id`, `name`, `color`, `weight`, `remark`) VALUES (1, 'P1', '#f2866e', 1, '高级告警');");
 		sb.append("INSERT INTO `alert_severity_conf`(`id`, `name`, `color`, `weight`, `remark`) VALUES (2, 'P2', '#f89984', 2, '中级告警');");
@@ -971,16 +1014,15 @@ public class SqlHandler {
 				+ "INSERT INTO `sys_config`(`param_key`, `param_value`, `status`, `remark`) VALUES ('terminal_telnet_pin_tip', 'assword:', 1, '默认：assword:');\r\n"
 				+ "INSERT INTO `sys_config`(`param_key`, `param_value`, `status`, `remark`) VALUES ('terminal_telnet_user_tip', 'ogin:', 1, '默认：ogin:');\r\n"
 				+ "INSERT INTO `sys_config`(`param_key`, `param_value`, `status`, `remark`) VALUES ('terminal_timeout', '30', 1, '默认：30，单位：minute');\r\n"
+				+ "INSERT INTO `sys_config`(`param_key`, `param_value`, `status`, `remark`) VALUES ('snmp_trap_listen_port', '160', 1, 'snmp trap接收端口');\r\n"
 				+ "update sys_config set param_key =\"default_scrape_interval\" where param_key=\"scrape_interval\";\r\n"
 				+ "update sys_config set param_key =\"default_scrape_timeout\" where param_key=\"scrape_timeout\";\r\n"
+				+ "update sys_config set param_value =\"Asia/Almaty\" where param_key=\"timezone\";\r\n"
 				);
 		// 修改sys_user表数据 初始化name字段内容
 		sb.append("update sys_user set name = username;");
 		sqlDao.execute(sb.toString());
-		// asset_brand表数据单独处理
-		assetBrandService.handler();
-		// asset_model表数据处理 前提先处理好brand表数据信息
-		assetModelService.handler();
+		*/
 	}
 	
 	public void transferData() {
@@ -998,5 +1040,61 @@ public class SqlHandler {
 		// 图表相关数据同步
 		chartService.handler();
 		
+		// 将原来数据库的数据数据还原
+		this.dataRestore();
+	}
+	
+	public void dataRestore() {
+		StringBuilder sb =new StringBuilder();
+		// sys_user表数据还原
+		List<SysUserEntity> users = sysUserService.queryNewUsers();
+		List<SysUserEntity> oldUsers = sysUserService.queryOldUsers();
+		Map<Long, SysUserEntity> userIdAndEntity = users.stream().collect(Collectors.toMap(SysUserEntity::getId, Function.identity()));
+		for(SysUserEntity user : oldUsers) {
+			SysUserEntity sysUserEntity = userIdAndEntity.get(user.getUserId());
+			if(ToolUtil.isNotEmpty(sysUserEntity)) {
+				sb.append("update sys_user set id='"+user.getUserId()+"',username='"+user.getUsername()+"',pin='"
+						+user.getPassword()+"',salt='"+user.getSalt()+"',email='"+user.getEmail()+"',status='"
+						+user.getStatus()+"',lang='"+user.getLang()
+						+"',source='"+user.getSource()+"',name='"+user.getUsername()+"';");
+			}else {
+				sb.append("insert into sys_user (id,username,pin,salt,email,status,"
+						+ "lang,source,name) values('"+user.getUserId()+"','"+user.getUsername()+"','"+user.getPassword()
+						+"','"+user.getSalt()+"','"+user.getEmail()+"','"+user.getStatus()
+						+"','"+user.getLang()+"','"+user.getSource()+"','"+user.getUsername()+"');");
+			}
+		}
+		// 
+		sqlDao.execute(sb.toString());
+		
+		// sys_config表数据
+		List<SysConfigEntity> newDatas = sysConfigService.list();
+		List<SysConfigEntity> oldDatas = sysConfigService.querySysConfigEntitys();
+		Map<String, String> oldDataMap = oldDatas.stream().collect(Collectors.toMap(SysConfigEntity::getParamKey, SysConfigEntity::getParamValue));
+		for(SysConfigEntity data : newDatas) {
+			if(data.getParamKey().equals("default_scrape_interval")) {
+				data.setParamValue(oldDataMap.get("scrape_interval"));
+			}else if(data.getParamKey().equals("default_scrape_timeout")) {
+				data.setParamValue(oldDataMap.get("scrape_timeout"));
+			}else if(data.getParamKey().equals("timezone")) {
+				data.setParamValue("Asia/Shanghai");
+			}else {
+				String value = oldDataMap.get(data.getParamKey());
+				if(value!=null) {
+					data.setParamValue(value);
+				}
+			}
+		}
+		sysConfigService.updateBatchById(newDatas);
+		
+		// prom_server snmp_mib link project_topo表数据同步
+		StringBuilder restoreData = new StringBuilder();
+		restoreData.append("INSERT INTO prom_server (id, dc_id, `host`, `port`, type, `status`, check_time,token ) SELECT id, idc_id,`host`,`port`,type,`status`,check_time,\"\" FROM prom_server_copy;\r\n"
+				+ "		INSERT INTO project_topo (id, project_id, topo,update_at,update_by) SELECT id, project_id, topo,update_at,update_by from project_topo_copy;\r\n"
+				+ "		INSERT INTO project_topo_icon (id,name,bytes,type,unit) select id,name,bytes,type,-1 from project_topo_icon_copy where id > 4;\r\n"
+				+ "		INSERT INTO snmp_mib (id,name,file_name,content,models,remark,update_by,update_at,tree) select id,name,file_name,content,models,remark,update_by,update_at,tree from snmp_mib_copy;\r\n"
+				+ "		INSERT INTO link (id,name,create_by,url,build_in,weight) select id,name,create_by,url,build_in,0 from link_copy;"
+				+ "     INSERT INTO cabinet (id,name,dc_id,u_size,remark,seq,x,y) select id,name,idc_id,u_size,remark,seq,x,y from cabinet_copy;");
+		sqlDao.execute(restoreData.toString());
 	}
 }