package com.ozs.mdm.service.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.ozs.mdm.mapper.StorageManageMapper;
import com.ozs.mdm.service.KpiConfigApi;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;

import com.ozs.mdm.dto.MdmCreateTableDTO;
import com.ozs.mdm.dto.MdmDimColumnType;
import com.ozs.mdm.dto.MdmInfoColumn;
import com.ozs.mdm.dto.MdmInfoDatabase;
import com.ozs.mdm.dto.UpdateColumnDTO;
import com.ozs.mdm.dto.UpdateTableDTO;
import com.ozs.mdm.util.JDBCUtil;
import com.ozs.mdm.util.R;

public class StorageManageServiceUtil {

	private final Logger log = LoggerFactory.getLogger(this.getClass());

	/**
	 * @fieldName: REPORT_CREDITCARD
	 * @fieldType: List<String>
	 * @Description:向导方式建表导出模板字段
	 */
	protected final List<String> REPORT_COLUMNS = new ArrayList<String>() {
		private static final long serialVersionUID = 1L;
		{
			add("columnName");
			add("columnComment");
			add("columnType");
			add("length");
			add("numericScale");
			add("ifPrimary");
			add("ifNull");
		}
	};

	/**
	 * @fieldName: REPORT_COLUMNS_MAP
	 * @fieldType: Map<String,String>
	 * @Description: 数据导入模板-向导方式建表导出模板字段对应中文映射
	 */
	protected final Map<String, String> REPORT_COLUMNS_MAP = new HashMap<String, String>() {
		private static final long serialVersionUID = 1L;
		{
			put("columnName", "字段名称");
			put("columnComment", "字段描述");
			put("columnType", "字段类型");
			put("length", "字段长度");
			put("numericScale", "小数位数");
			put("ifPrimary", "是否主键");
			put("ifNull", "是否可空");
		}
	};

	/**
	 * 保存字段信息表数据
	 * 
	 * @param columns
	 * @param storageManageMapper
	 */
	protected synchronized void saveColums(MdmCreateTableDTO tableDto, StorageManageMapper storageManageMapper) {
		int maxCode = storageManageMapper.getMaxColumnCode();
		List<MdmInfoColumn> columns = tableDto.getColumns();
		for (MdmInfoColumn c : columns) {
			maxCode++;
			c.setUpdateUserId(tableDto.getUpdateUserId());
			c.setTableId(tableDto.getTableId());
			c.setColumnCode(maxCode);
		}
		storageManageMapper.saveColumns(columns);
	}

	/**
	 * 数据库中建表操作
	 * 
	 * @param tableDto
	 * @param jdbcUtil
	 * @param storageManageMapper
	 * @throws Exception
	 */
	public MdmInfoDatabase createTableForDatabase(MdmCreateTableDTO tableDto, JDBCUtil jdbcUtil,
			StorageManageMapper storageManageMapper) throws Exception {
		List<MdmInfoColumn> columns = tableDto.getColumns();
		List<MdmDimColumnType> columnTypes = storageManageMapper.listColumnTypeByIds(columns);
		for (MdmInfoColumn column : columns) {
			for (MdmDimColumnType columnType : columnTypes) {
				if (column.getColumnType() == columnType.getId()) {
					column.setColumnTypeName(columnType.getTypeName());
					// 判断字段是否是时间类型
					if (columnType.getIsTime() == 1) {
						column.setLength(0);
					}
					// 判断字段不是浮点类型，小数点位数为null
					if (columnType.getIsFloatPoint() == 0) {
						column.setNumericScale(null);
					}
				}

			}
		}
		tableDto.setColumns(columns);
		// tableDto.setCharsetName(storageManageMapper.getCharsetNameById(tableDto.getCharsetId()));
		MdmInfoDatabase database = storageManageMapper.getDatabaseById(tableDto.getDatabaseId());
		tableDto.setCharsetName(database.getCharsetName());
		String sql = null;
		switch (database.getResourceTypeName()) {
		case "mysql":
			sql = getMysqlSql(tableDto);
			break;
		case "hive":
			sql = getHiveSql(tableDto);
			break;
		default:
			log.info("未知的数据资源类型");
			throw new RuntimeException("未知的数据资源类型");
		}
		tableDto.setCharsetId(database.getCharset());
		tableDto.setDdl(sql);
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		try {
			jdbcUtil.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("建表语句执行异常！");
		}
		log.info("数据库建表完成");
		return database;
	}

	/**
	 * 获取Mysql拼接sql
	 * 
	 * @param tableDto
	 * @return
	 */
	protected String getMysqlSql(MdmCreateTableDTO tableDto) {
		StringBuffer sb = new StringBuffer();
		StringBuffer cb = new StringBuffer();
		// 主键
		boolean havePriMary = false;
		StringBuffer prib = new StringBuffer();
		prib.append(" primary key (");
		List<MdmInfoColumn> columns = tableDto.getColumns();
		for (MdmInfoColumn c : columns) {
			cb.append(c.getColumnName()).append(" ").append(c.getColumnTypeName());
			if (c.getLength() != null && c.getLength() != 0) {
				cb.append(" (").append(c.getLength());
				if (c.getNumericScale() != null && c.getNumericScale() != 0) {
					cb.append(",").append(c.getNumericScale());
				}
				cb.append(") ");
			}
			if (c.getIfPrimary() == 1) {
				havePriMary = true;
				// cb.append(" primary key ");
				prib.append(c.getColumnName()).append(",");
			}
			if (c.getIfNull() == 0) {
				cb.append(" NOT NULL ");
			}
			cb.append(" COMMENT '").append(c.getColumnComment()).append("',");

		}
		if (!havePriMary) {
			cb.deleteCharAt(cb.length() - 1);
		}
		sb.append("CREATE TABLE ").append(tableDto.getTableName()).append(" (");
		sb.append(cb.toString());
		if (havePriMary) {
			prib.deleteCharAt(prib.length() - 1);
			prib.append(")");
			sb.append(prib);
		}
		sb.append(") ENGINE=InnoDB DEFAULT CHARSET=").append(tableDto.getCharsetName()).append(" ");
		sb.append("COMMENT='").append(tableDto.getTableComment()).append("';");
		return sb.toString();
	}

	/**
	 * 获取getHiveSql拼接sql
	 * 
	 * @param tableDto
	 * @return
	 */
	private String getHiveSql(MdmCreateTableDTO tableDto) {
		StringBuffer sb = new StringBuffer();

		return sb.toString();
	}

	@Value("${log.file.path}")
	protected String filePath;

	/**
	 * 是否主键选项数组
	 */
	private final static String[] IF_PRIMARY_OPTION = { "是", "否" };
	/**
	 * 是否可空选项数组
	 */
	private final static String[] IF_NULL_OPTION = { "可空", "不可空" };

	/**
	 * 
	 * @Title: getExportFileName
	 * @Description: 导出Excel文件并返回文件名
	 * @param paramDTO
	 * @param fileName
	 * @return
	 * @throws Exception
	 * @return: String
	 */
	public String getTemplateColums(String sheetName, StorageManageMapper storageManageMapper) throws Exception {
		List<String> list = REPORT_COLUMNS;
		// 第一步创建workbook
		XSSFWorkbook wb = new XSSFWorkbook();
		// 第二步创建sheet
		XSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步创建行row:添加表头0行
		XSSFRow row = sheet.createRow(0);
		XSSFCellStyle style = wb.createCellStyle();
		XSSFDataFormat format = wb.createDataFormat();
		style.setDataFormat(format.getFormat("@"));
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		XSSFCell cell = null;
		// 第四步创建单元格
		for (int i = 0; i < list.size(); i++) {
			cell = row.createCell(i);
			cell.setCellValue(REPORT_COLUMNS_MAP.get(list.get(i)));
			cell.setCellStyle(style);
		}
		// 第五步插入下拉选项数据
		CellRangeAddressList addressList = null;
		// 数据库资源类型，1 hive 2 mysql
		Integer resourceType = 2;
		String[] typeNameArray = storageManageMapper.listColumnsTypeName(resourceType);
		addressList = new CellRangeAddressList(1, 65536, 2, 2);
		// 超255字节下拉数据解决方法
		addDropDownList(wb, sheet, typeNameArray, addressList);
		addressList = new CellRangeAddressList(1, 65536, 5, 5);
		sheetAddValidation(sheet, IF_PRIMARY_OPTION, addressList);
		addressList = new CellRangeAddressList(1, 65536, 6, 6);
		sheetAddValidation(sheet, IF_NULL_OPTION, addressList);

		// 第六步将生成excel文件保存到指定路径下
		File downloanRootPath = new File(filePath + File.separator + "file");
		if (!downloanRootPath.isDirectory()) {
			downloanRootPath.mkdirs();
		}
		long fileName = System.currentTimeMillis();
		String filePath = downloanRootPath.getAbsolutePath() + File.separator + fileName + ".xlsx";
		FileOutputStream fout = new FileOutputStream(filePath);
		wb.write(fout);
		fout.flush();
		fout.close();
		return filePath;
	}

	private XSSFWorkbook xssfWorkbook = null;

	// 如果是xlsx，做什么操作
	protected List<List<String>> xlsx(File file, List<String> paramList) throws Exception {

		try {
			xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
			return show(xssfWorkbook, paramList);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;

	}

	// 他们两者都有相同的父类，而且都是父类中的方法操作，那我干脆点直接将他们用父类的形式抽取出来，这样解决了代码的冗余问题
	private List<List<String>> show(Workbook workbook, List<String> paramList) throws Exception {
		List<List<String>> resultList = new ArrayList<>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			Sheet xssfSheet = workbook.getSheetAt(numSheet);
			// 这种主要是判断是否含空以免包空指针
			if (xssfSheet == null) {
				continue;
			}
			// 获取展示数据格式，先列，再行
			for (int current = 0; current < paramList.size(); current++) {
				List<String> rowDataList = new ArrayList<>();
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					Row xssfRow = xssfSheet.getRow(rowNum);
					Cell data = xssfRow.getCell(current);
					if (data == null || getValue(data, workbook).equals("")) {
						rowDataList.add("");
						continue;
					}
					rowDataList.add(getValue(data, workbook));
				}
				resultList.add(rowDataList);
			}
			List<Map<String, Object>> dataList = new ArrayList<>();
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				Row xssfRow = xssfSheet.getRow(rowNum);
				Map<String, Object> cell = new HashMap<>();
				for (int current = 0; current < paramList.size(); current++) {
					Cell data = xssfRow.getCell(current);
					if (data == null || getValue(data, workbook).equals("")) {
						continue;
					}
					cell.put(REPORT_COLUMNS.get(current), getValue(data, workbook));
				}
				dataList.add(cell);
			}

		}
		return resultList;
	}

	// 如果是xlsx，做什么操作
	protected List<Map<String, Object>> xlsx2(File file, List<String> paramList) throws Exception {

		try {
			xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
			return show2(xssfWorkbook, paramList);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;

	}

	// 他们两者都有相同的父类，而且都是父类中的方法操作，那我干脆点直接将他们用父类的形式抽取出来，这样解决了代码的冗余问题
	private List<Map<String, Object>> show2(Workbook workbook, List<String> paramList) throws Exception {
		List<Map<String, Object>> dataList = new ArrayList<>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			Sheet xssfSheet = workbook.getSheetAt(numSheet);
			// 如果是隐藏sheet页不读取
			if (workbook.isSheetHidden(numSheet)) {
				continue;
			}
			// 这种主要是判断是否含空以免包空指针
			if (xssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				Row xssfRow = xssfSheet.getRow(rowNum);
				Map<String, Object> cell = new HashMap<>();
				for (int current = 0; current < paramList.size(); current++) {
					Cell data = xssfRow.getCell(current);
					if (data == null || getValue(data, workbook).equals("")) {
						continue;
					}
					cell.put(REPORT_COLUMNS.get(current), getValue(data, workbook));
				}
				dataList.add(cell);
			}

		}
		return dataList;
	}

	// 将他们两者的父类抽取出来，这样减少代码量，同时便于管理
	private String getValue(Cell xssfCell, Workbook workbook) {
		String value = "";
		switch (xssfCell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = String.valueOf(xssfCell.getRichStringCellValue().getString());
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(xssfCell)) {
				value = String.valueOf(String.valueOf(xssfCell.getDateCellValue()));
			} else {
				value = String.valueOf(xssfCell.getNumericCellValue());
				if (value.endsWith(".0")) {
					value = value.replace(".0", "");
				}
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(xssfCell.getBooleanCellValue());
			break;
		// 公式,
		case Cell.CELL_TYPE_FORMULA:
			// 获取Excel中用公式获取到的值,//=SUM(P4-Q4-R4-S4)Excel用这种类似的公式计算出来的值用POI无法获取，要想获取的话，就必须一下操作
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			evaluator.evaluateFormulaCell(xssfCell);
			CellValue cellValue = evaluator.evaluate(xssfCell);
			value = String.valueOf(cellValue.getNumberValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			value = String.valueOf(xssfCell.getErrorCellValue());
			break;
		default:
		}
		return value;
	}

	/**
	 * 准备下拉列表数据
	 * 
	 * @param sheet
	 * @param data
	 * @param addressList
	 */
	private void sheetAddValidation(XSSFSheet sheet, String[] data, CellRangeAddressList addressList) {
		// 绑定
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createExplicitListConstraint(data);
		XSSFDataValidation validation = null;
		validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
		sheet.addValidationData(validation);

	}

	/**
	 * 单元格添加下拉菜单，解决下拉数据255字节限制
	 * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]
	 * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
	 * 
	 * @param workbook
	 * @param tarSheet
	 * @param menuItems
	 * @param addressList
	 * @throws Exception
	 */
	public static void addDropDownList(XSSFWorkbook workbook, XSSFSheet tarSheet, String[] menuItems,
			CellRangeAddressList addressList) throws Exception {
		if (null == workbook) {
			throw new Exception("workbook为null");
		}
		if (null == tarSheet) {
			throw new Exception("待添加菜单的sheet为null");
		}
		// 必须以字母开头，最长为31位
		String hiddenSheetName = "a" + UUID.randomUUID().toString().replace("-", "").substring(1, 31);
		// excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
		String formulaId = "form" + UUID.randomUUID().toString().replace("-", "");
		XSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);
		// 存储下拉菜单项的sheet页不显示
		workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
		XSSFRow row = null;
		XSSFCell cell = null;
		// 隐藏sheet中添加菜单数据
		for (int i = 0; i < menuItems.length; i++) {
			row = hiddenSheet.createRow(i);
			// 隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
			cell = row.createCell(2);
			cell.setCellValue(menuItems[i]);
		}
		XSSFName namedCell = workbook.createName();// 创建"名称"标签，用于链接
		namedCell.setNameName(formulaId);
		namedCell.setRefersToFormula(hiddenSheetName + "!A$1:A$" + menuItems.length);
		XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(ValidationType.LIST, formulaId);
		// 数据有效性对象
		DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) hiddenSheet);
		DataValidation valida = help.createValidation(constraint, addressList);
		tarSheet.addValidationData(valida);
	}

	/**
	 * 保存字段信息表数据
	 * 
	 * @param columns
	 * @param storageManageMapper
	 * @throws Exception
	 */
	protected synchronized void saveColumsByDDL(MdmCreateTableDTO tableDto, MdmInfoDatabase database,
			StorageManageMapper storageManageMapper) throws Exception {
		List<MdmInfoColumn> columns = null;
		switch (database.getResourceTypeName()) {
		case "mysql":
			columns = getMysqlColumns(tableDto, database);
			break;
		case "hive":
			columns = getHiveColumns(tableDto, database);
			break;
		default:
			log.info("未知的数据资源类型");
			throw new Exception("未知的数据资源类型");
		}
		// 批量赋值字段编号
		int maxCode = storageManageMapper.getMaxColumnCode();
		for (MdmInfoColumn c : columns) {
			maxCode++;
			c.setUpdateUserId(tableDto.getUpdateUserId());
			c.setTableId(tableDto.getTableId());
			c.setColumnCode(maxCode);
		}
		// 批量赋值字段类型ID
		List<MdmDimColumnType> types = storageManageMapper.listColumnTypeByNames(columns);
		for (MdmInfoColumn c : columns) {
			for (MdmDimColumnType t : types) {
				if (c.getColumnTypeName().equals(t.getTypeName())) {
					c.setColumnType(t.getId());
				}
			}
		}
		storageManageMapper.saveColumns(columns);
	}

	private List<MdmInfoColumn> getHiveColumns(MdmCreateTableDTO tableDto, MdmInfoDatabase database)
			throws SQLException {
		List<MdmInfoColumn> columns = new ArrayList<>();
		String sql = "SELECT 	column_name column_name,data_type as column_type,column_comment column_comment,NUMERIC_SCALE numeric_scale,if( NUMERIC_SCALE = 0 or NUMERIC_SCALE is null, substring_index(substring_index(COLUMN_TYPE, '(', -1), ')', 1), substring_index(substring_index(COLUMN_TYPE, '(', -1), ',', 1) ) length, if(IS_NULLABLE = 'NO','0','1')	if_null, if(COLUMN_KEY = 'PRI','1','0') if_primary FROM information_schema.COLUMNS "
				+ "WHERE TABLE_SCHEMA = '" + database.getDatabaseName() + "'  and TABLE_NAME = '"
				+ tableDto.getTableName() + "'";
		JDBCUtil jdbcUtil = new JDBCUtil();
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		List<Map<String, Object>> result = jdbcUtil.getData(sql);
		jdbcUtil.closeJDBC();
		for (Map<String, Object> m : result) {
			MdmInfoColumn c = new MdmInfoColumn();
			if (m.get("column_comment") != null) {
				c.setColumnComment(m.get("column_comment").toString());
			}
			if (m.get("column_name") != null) {
				c.setColumnName(m.get("column_name").toString());
			}
			if (m.get("column_type") != null) {
				c.setColumnTypeName(m.get("column_type").toString().toUpperCase());
			}
			if (m.get("numeric_scale") != null) {
				c.setNumericScale(Integer.parseInt((m.get("numeric_scale").toString())));
			}
			if (m.get("length") != null) {
				// 判断返回值是否是数值类型，时间类型系统表length为字符格式 如：time
				if (isNumeric(m.get("length").toString())) {
					c.setLength(Integer.parseInt((m.get("length").toString())));
				} else {
					c.setLength(0);
				}
			}
			if (m.get("if_null") != null) {
				c.setIfNull(Integer.parseInt((m.get("if_null").toString())));
			}
			if (m.get("if_primary") != null) {
				c.setIfPrimary(Integer.parseInt((m.get("if_primary").toString())));
			}
			columns.add(c);
		}
		return columns;
	}

	private List<MdmInfoColumn> getMysqlColumns(MdmCreateTableDTO tableDto, MdmInfoDatabase database)
			throws SQLException {
		List<MdmInfoColumn> columns = new ArrayList<>();
		String sql = "SELECT 	column_name column_name,data_type as column_type,column_comment column_comment,NUMERIC_SCALE numeric_scale,if( NUMERIC_SCALE = 0 or NUMERIC_SCALE is null, substring_index(substring_index(COLUMN_TYPE, '(', -1), ')', 1), substring_index(substring_index(COLUMN_TYPE, '(', -1), ',', 1) ) length, if(IS_NULLABLE = 'NO','0','1')	if_null, if(COLUMN_KEY = 'PRI','1','0') if_primary FROM information_schema.COLUMNS "
				+ "WHERE TABLE_SCHEMA = '" + database.getDatabaseName() + "'  and TABLE_NAME = '"
				+ tableDto.getTableName() + "'";
		JDBCUtil jdbcUtil = new JDBCUtil();
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		List<Map<String, Object>> result = jdbcUtil.getData(sql);
		jdbcUtil.closeJDBC();
		for (Map<String, Object> m : result) {
			MdmInfoColumn c = new MdmInfoColumn();
			if (m.get("column_comment") != null) {
				c.setColumnComment(m.get("column_comment").toString());
			}
			if (m.get("column_name") != null) {
				c.setColumnName(m.get("column_name").toString());
			}
			if (m.get("column_type") != null) {
				c.setColumnTypeName(m.get("column_type").toString().toUpperCase());
			}
			if (m.get("numeric_scale") != null) {
				c.setNumericScale(Integer.parseInt((m.get("numeric_scale").toString())));
			}
			if (m.get("length") != null) {
				// 判断返回值是否是数值类型，时间类型系统表length为字符格式 如：time
				if (isNumeric(m.get("length").toString())) {
					c.setLength(Integer.parseInt((m.get("length").toString())));
				} else {
					c.setLength(0);
				}
			}
			if (m.get("if_null") != null) {
				c.setIfNull(Integer.parseInt((m.get("if_null").toString())));
			}
			if (m.get("if_primary") != null) {
				c.setIfPrimary(Integer.parseInt((m.get("if_primary").toString())));
			}
			columns.add(c);
		}
		return columns;
	}

	/**
	 * 数据库中建表操作
	 * 
	 * @param tableDto
	 * @param jdbcUtil
	 * @param storageManageMapper
	 * @throws Exception
	 */
	public MdmInfoDatabase createTableForDatabaseByDDL(MdmCreateTableDTO tableDto, JDBCUtil jdbcUtil,
			StorageManageMapper storageManageMapper) throws Exception {
		// 获取数据库信息
		MdmInfoDatabase database = storageManageMapper.getDatabaseById(tableDto.getDatabaseId());
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		jdbcUtil.update(tableDto.getDdl());
		jdbcUtil.commit();
		log.info("数据库建表完成");
		jdbcUtil.closeJDBC();
		return database;
	}

	/**
	 * 调用kpi配置服务接口，批量判断字段是否在使用
	 * 
	 * @param storageManageMapper
	 * @param tableId
	 * @return
	 */
	protected boolean ifColumnsInUse(List<String> columnIds, KpiConfigApi kpiConfigApi) {
		if (columnIds == null || columnIds.size() == 0) {
			return true;
		}
		R<Map<String, Object>> result = kpiConfigApi.ifColumnsInUse(columnIds);
		Map<String, Object> m = result.getData();
		return !m.containsValue("true");
	}

	/**
	 * 修改表和字段信息
	 * 
	 * @param uTableDto
	 * @param database
	 * @param jdbcUtil
	 * @param storageManageMapper
	 * @throws Exception
	 */
	protected void updateTableOfDatabase(UpdateTableDTO uTableDto, MdmInfoDatabase database, JDBCUtil jdbcUtil,
			StorageManageMapper storageManageMapper) throws Exception {
		storageManageMapper.updateTableInfo(uTableDto);
		// 修改表注释 alter table test1 comment '修改后的表的注释';
		String commentSql = "alter table " + uTableDto.getTableName() + " comment '" + uTableDto.getTableComment()
				+ "'";
		// 修改表名 alter table test rename test1; --修改表名
		String renameSql = "alter table " + uTableDto.getTableName() + " rename " + uTableDto.getNewTableName();
		jdbcUtil.update(commentSql);
		jdbcUtil.update(renameSql);

	}

	/**
	 * 修改字段信息
	 * 
	 * @param uTableDto
	 * @param database
	 * @param jdbcUtil
	 * @param storageManageMapper
	 * @throws Exception
	 */
	protected void updateColumnsOfDatabase(UpdateTableDTO uTableDto, MdmInfoDatabase database, JDBCUtil jdbcUtil,
			StorageManageMapper storageManageMapper) throws Exception {
		// 表字段删除drop sql
		StringBuffer dSb = new StringBuffer();
		// 字段名称修改 Sql
		StringBuffer cSb = new StringBuffer();
		cSb.append("alter table ").append(uTableDto.getTableName());
		dSb.append("alter table ").append(uTableDto.getTableName());
		List<UpdateColumnDTO> dColumns = new ArrayList<>();
		// 表字段添加add sql
		StringBuffer aSb = new StringBuffer();
		aSb.append("alter table ").append(uTableDto.getTableName());
		List<UpdateColumnDTO> aColumns = new ArrayList<>();

		List<UpdateColumnDTO> mColumns = new ArrayList<>();

		List<UpdateColumnDTO> cColumns = new ArrayList<>();

		List<UpdateColumnDTO> uColumns = uTableDto.getColumns();

		List<MdmInfoColumn> columns = storageManageMapper.listColumsByTableId(uTableDto.getId());

		// 获取替换columnTypeName
		List<MdmDimColumnType> columnTypes = storageManageMapper.listColumnTypeByIds(uColumns);
		for (UpdateColumnDTO column : uColumns) {
			// 判断是否修改字段名称
			for (MdmInfoColumn c : columns) {
				if (column.getId() != null && column.getId().equals(c.getId())
						&& !c.getColumnName().equals(column.getColumnName())) {
					column.setOldName(c.getColumnName());
					column.setColumnUpdateType("chenge");
				}
			}
			for (MdmDimColumnType columnType : columnTypes) {
				if (column.getColumnType() == columnType.getId()) {
					column.setColumnTypeName(columnType.getTypeName());
				}
			}
		}
		List<String> mSqls = new ArrayList<>();
		List<String> cSqls = new ArrayList<>();

		for (UpdateColumnDTO uC : uColumns) {
			uC.setTableId(uTableDto.getId());
			uC.setUpdateUserId(uTableDto.getUpdateUserId());
			switch (uC.getColumnUpdateType()) {
			// 字段修改
			case "modify":
				StringBuffer mSb = new StringBuffer();
				mSb.append("alter table ").append(uTableDto.getTableName()).append(" modify ");
				mSb.append(uC.getColumnName()).append(" ").append(uC.getColumnTypeName());
				if (uC.getLength() != null && uC.getLength() != 0) {
					mSb.append(" (").append(uC.getLength());
					// TODO 小数点文字需要默认
					if (uC.getNumericScale() != null && uC.getNumericScale() != 0) {
						mSb.append(",").append(uC.getNumericScale());
					}
					mSb.append(") ");
				}
				mSb.append(" comment ").append("'").append(uC.getColumnComment()).append("',");
				mSb.deleteCharAt(mSb.length() - 1);
				mSqls.add(mSb.toString());
				mColumns.add(uC);
				break;
			// 字段删除
			case "drop":
				dSb.append(" drop column ").append(uC.getColumnName()).append(",");
				dColumns.add(uC);
				break;
			// 字段名称修改
			case "chenge":
				cSb.append(" change  ").append(uC.getOldName()).append(" ").append(uC.getColumnName());
				cSb.append(" ").append(uC.getColumnTypeName());
				if (uC.getLength() != null && uC.getLength() != 0) {
					cSb.append(" (").append(uC.getLength());
					// TODO 小数点文字需要默认
					if (uC.getNumericScale() != null && uC.getNumericScale() != 0) {
						cSb.append(",").append(uC.getNumericScale());
					}
					cSb.append(") ");
				}
				cSb.append(" comment ").append("'").append(uC.getColumnComment()).append("'");
				cSqls.add(cSb.toString());
				cColumns.add(uC);
				break;
			// 字段增加
			case "add":
				aSb.append(" add ").append(uC.getColumnName()).append(" ").append(uC.getColumnTypeName());
				if (uC.getLength() != null && uC.getLength() != 0) {
					aSb.append(" (").append(uC.getLength());
					if (uC.getNumericScale() != null && uC.getNumericScale() != 0) {
						aSb.append(",").append(uC.getNumericScale());
					}
					aSb.append(") ");
				}
				aSb.append(" comment ").append("'").append(uC.getColumnComment()).append("',");
				aColumns.add(uC);
				break;
			default:
				break;
			}
		}
		// 判断系统库字段信息是否需要增删改

		if (aColumns != null && aColumns.size() != 0) {
			aSb.deleteCharAt(aSb.length() - 1);
			int maxCode = storageManageMapper.getMaxColumnCode();
			for (UpdateColumnDTO c : aColumns) {
				maxCode++;
				c.setTableId(uTableDto.getId());
				c.setColumnCode((maxCode) + "");
			}
			storageManageMapper.saveUpdateColumn(aColumns);
			/**
			 * 执行 add column 操作 例：alter table id_name add age int,add address
			 * varchar(11);
			 */
			jdbcUtil.update(aSb.toString());
		}
		if (dColumns != null && dColumns.size() != 0) {
			dSb.deleteCharAt(dSb.length() - 1);
			storageManageMapper.removeUpdateColumn(dColumns);
			/**
			 * 执行 drop column 操作 例：alter table id_name drop column age,drop
			 * column address;
			 */
			jdbcUtil.update(dSb.toString());
		}
		if (mColumns != null && mColumns.size() != 0) {
			storageManageMapper.updateUpdateColumn(mColumns);
			/**
			 * 执行 modify column 操作 例：alter table user modify new1 VARCHAR(10)
			 * comment '';
			 */
			for (String s : mSqls) {
				jdbcUtil.update(s);
			}
		}
		if (cColumns != null && cColumns.size() != 0) {
			storageManageMapper.updateUpdateColumn(cColumns);
			/**
			 * 执行 change column 操作 例：alter table user change new1 new4
			 * VARCHAR(10) comment '';
			 */
			for (String s : cSqls) {
				jdbcUtil.update(s);
			}
		}
	}

	/**
	 * 利用正则表达式判断字符串是否是数字
	 * 
	 * @param str
	 * @return
	 */
	public boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}

}
