package com.ozs.da.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ozs.da.entity.dataquality.ListDataDTO;
import com.ozs.da.entity.dataquality.QuerySqlDTO;
import com.ozs.da.entity.dataquality.UpdateDataDTO;
import com.ozs.da.entity.remotedb.MdmInfoColumn;
import com.ozs.da.service.DataQualityService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.ozs.da.entity.remotedb.MdmCreateTableDTO;
import com.ozs.da.entity.remotedb.MdmInfoDatabase;
import com.ozs.da.entity.remotedb.MdmInfoTable;
import com.ozs.da.feign.DaMdmFeign;
import com.ozs.da.utils.FileTool;
import com.ozs.da.utils.R;

/**
 * 
 * @ClassName: DataQualityService
 * @Description:数据质量管理
 * @author: Zhao You Kai
 * @date: Apr 3, 2019 2:13:36 PM
 */
@Service
public class DataQualityServiceImpl implements DataQualityService {
	@Autowired
	private DaMdmFeign daMdmFeign;

	@Override
	public R<ListDataDTO> listData(String tableId, String sql, Integer page, Integer pageSize) {
		R<ListDataDTO> result = new R<>();
		ListDataDTO resultData = new ListDataDTO();
		Map<String, String> sqlMap = null;
		// 主键字段名称
		List<String> primaryColumns = new ArrayList<>();
		// TODO chectSql() 检验sql是否符合查询规则
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(tableId).getData();
		List<MdmInfoColumn> columns = daMdmFeign.listColumnByTableId(tableId);
		for (MdmInfoColumn column : columns) {
			if (column.getIfPrimary() == 1) {
				primaryColumns.add(column.getColumnName());
			}
		}
		// 获取查询总数量
		sqlMap = new HashMap<>();
		sqlMap.put("sql", "select count(0) totalnumber from (" + sql + ") temp");
		Integer totalNumber = null;
		try {
			totalNumber = (Integer) daMdmFeign.listData(table.getDatabaseId(), sqlMap).get(0).get("totalnumber");
		} catch (Exception e) {
			result.setCode("-1");
			result.setMsg("sql执行异常");
			return result;
		}
		// 总页数
		Integer totalPageCount = (totalNumber - 1) / pageSize + 1;
		// 执行查询sql,获取数据
		sqlMap.put("sql", "select * from (" + sql + ") temp limit " + (pageSize * (page - 1)) + "," + pageSize);
		List<Map<String, Object>> listData = daMdmFeign.listData(table.getDatabaseId(), sqlMap);
		// 获取查询sql字段列表
		resultData.setListColumn(getListColumnBySql(sql));
		resultData.setListData(listData);
		resultData.setPrimaryColumns(primaryColumns);
		result.setData(resultData);
		result.setTotalNumber(totalNumber);
		result.setTotalPageCount(totalPageCount);
		result.setPage(page);
		result.setPageSize(pageSize);
		return result;
	}

	/**
	 * 获取查询sql字段列表
	 * 
	 * @param sql
	 * @return
	 */
	private List<String> getListColumnBySql(String sql) {
		// 去除所有空格
		sql = sql.replace("\t", "");
		sql = sql.replace(" ", "");
		sql = sql.replace("`", "");
		// 转小写
		sql = sql.toLowerCase();
		return Arrays.asList(sql.substring(sql.indexOf("select") + 6, sql.indexOf("from")).split(","));
	}

	@Override
	public R<List<MdmInfoDatabase>> listDatabase(String resourceName) {
		return new R<>(daMdmFeign.listDatabase(resourceName));
	}

	@Override
	public R<List<MdmInfoTable>> listTableByDatabaseId(String databaseId) {
		return new R<>(daMdmFeign.listTableByDatabaseId(databaseId));
	}

	@Override
	public R<QuerySqlDTO> getQuerySql(String tableId) {
		QuerySqlDTO result = new QuerySqlDTO();
		// 主键部分sql，包括 select 主键1，主键2...
		StringBuffer primarySql = new StringBuffer();
		// 查询部分sql，主键部分后
		StringBuffer qureySql = new StringBuffer();
		primarySql.append("select ");
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(tableId).getData();
		List<MdmInfoColumn> columns = daMdmFeign.listColumnByTableId(tableId);
		for (MdmInfoColumn column : columns) {
			if (column.getIfPrimary() == 1) {
				primarySql.append(column.getColumnName()).append(",");
			} else {
				qureySql.append(column.getColumnName()).append(",");
			}
		}
		// 去掉最后一个逗号
		if (qureySql.length() == 0) {
			primarySql.deleteCharAt(primarySql.length() - 1);
		}
		// 如果只有主键字段
		if (qureySql != null && qureySql.length() != 0) {
			qureySql.deleteCharAt(qureySql.length() - 1);
		}
		qureySql.append(" from ").append(table.getTableName()).append(" where 1=1 ");
		result.setPrimarySql(primarySql.toString());
		result.setQureySql(qureySql.toString());
		return new R<>(result);
	}

	@Override
	public R<String> updateData(UpdateDataDTO updateData) {
		R<String> r = new R<>();
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(updateData.getTableId()).getData();

		// 判断修改后主键是否重复
		// StringBuffer checkSql = new StringBuffer();
		// checkSql.append(" select count(0) count from
		// ").append(table.getTableName());

		StringBuffer updateSql = new StringBuffer();
		// 主键部分sql
		StringBuffer whereSql = new StringBuffer();
		// 修改后值
		String updateValue = null;
		List<MdmInfoColumn> columns = daMdmFeign.listColumnByTableId(updateData.getTableId());
		whereSql.append(" where 1=1");
		for (String primaryColumn : updateData.getPrimaryColumns()) {
			for (MdmInfoColumn column : columns) {
				if (column.getColumnName().equals(primaryColumn)) {
					// 判断是否是文本类型
					if (column.getIfChar() == 1 || column.getIsTime() == 1) {
						whereSql.append(" and ").append(primaryColumn).append("=").append("'")
								.append(updateData.getPrimaryData().get(primaryColumn)).append("'");
					} else {
						whereSql.append(" and ").append(primaryColumn).append("=")
								.append(updateData.getPrimaryData().get(primaryColumn));
					}
				}
				// 修改后值判断字段类型
				if (updateData.getUpdateColumn().equals(column.getColumnName())) {
					// 判断是否是文本类型
					if (column.getIfChar() == 1 || column.getIsTime() == 1) {
						updateValue = "'" + (String) updateData.getUpdateData().get(updateData.getUpdateColumn()) + "'";
					} else {
						updateValue = updateData.getUpdateData().get(updateData.getUpdateColumn()).toString();
					}
				}
			}
		}
		Map<String, String> sqlMap = null;
		sqlMap = new HashMap<>();
		updateSql.append("update ").append(table.getTableName()).append(" set ");
		updateSql.append(updateData.getUpdateColumn()).append("=").append(updateValue);
		Integer index = null;

		// 判断update语句是否有where条件，避免全量修改
		if (whereSql.toString().contains("and")) {
			updateSql.append(whereSql.toString());
			// 开始判断修改后主键
			// checkSql.append(whereSql.toString());
			// sqlMap.put("sql", checkSql.toString());
			// Integer count = (Integer)
			// daMdmFeign.listData(table.getDatabaseId(),
			// sqlMap).get(0).get("count");

			sqlMap.put("sql", updateSql.toString());
			index = daMdmFeign.executeSql(table.getDatabaseId(), sqlMap);
		}
		if (index == null) {
			r.setMsg("修改失败");
			r.setCode("-1");
			return r;
		}
		r.setMsg("修改成功");
		r.setData(index.toString());
		return r;
	}

	@Override
	public R<String> deleteData(UpdateDataDTO updateData) {
		R<String> r = new R<>();
		StringBuffer deleteSql = new StringBuffer();
		// 主键部分sql
		StringBuffer whereSql = new StringBuffer();
		List<MdmInfoColumn> columns = daMdmFeign.listColumnByTableId(updateData.getTableId());
		whereSql.append(" where 1=1");
		for (String primaryColumn : updateData.getPrimaryColumns()) {
			for (MdmInfoColumn column : columns) {
				if (column.getColumnName().equals(primaryColumn)) {
					// 判断是否是文本类型
					if (column.getIfChar() == 1 || column.getIsTime() == 1) {
						whereSql.append(" and ").append(primaryColumn).append("=").append("'")
								.append(updateData.getPrimaryData().get(primaryColumn)).append("'");
					} else {
						whereSql.append(" and ").append(primaryColumn).append("=")
								.append(updateData.getPrimaryData().get(primaryColumn));
					}
				}
			}
		}
		Map<String, String> sqlMap = null;
		sqlMap = new HashMap<>();
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(updateData.getTableId()).getData();
		deleteSql.append("delete from ").append(table.getTableName());
		Integer index = null;
		// 判断update语句是否有where条件，避免全量修改
		if (whereSql.toString().contains("and")) {
			deleteSql.append(whereSql.toString());
			sqlMap.put("sql", deleteSql.toString());
			index = daMdmFeign.executeSql(table.getDatabaseId(), sqlMap);
		}
		if (index == null) {
			r.setMsg("删除失败");
			r.setCode("-1");
			return r;
		}
		r.setMsg("删除成功");
		r.setData(index.toString());
		return r;
	}

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

	@Override
	public String downloadTemplateData(String sheetName, String tableId, String sql) throws Exception {
		//
		List<String> list = getListColumnBySql(sql);
		// 第一步创建workbook
		XSSFWorkbook wb = new XSSFWorkbook();
		// 第二步创建sheet
		XSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步创建行row:添加表头0行
		XSSFCellStyle style = wb.createCellStyle();
		XSSFDataFormat format = wb.createDataFormat();
		style.setDataFormat(format.getFormat("@"));
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		XSSFCell cell = null;
		// 字段ID，标题
		List<String> listColumn = getListColumnBySql(sql);
		XSSFRow row = sheet.createRow(0);
		for (int i = 0; i < list.size(); i++) {
			cell = row.createCell(i);
			cell.setCellValue(listColumn.get(i));
			cell.setCellStyle(style);
		}
		// TODO checkSql() 检验sql是否符合查询规则
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(tableId).getData();
		Map<String, String> sqlMap = null;
		sqlMap = new HashMap<>();
		// 执行查询sql,获取数据
		sqlMap.put("sql", sql);
		List<Map<String, Object>> listData = daMdmFeign.listData(table.getDatabaseId(), sqlMap);
		// 第四步创建单元格
		for (int j = 0; j < listData.size(); j++) {
			row = sheet.createRow(j + 1);
			for (int i = 0; i < list.size(); i++) {
				cell = row.createCell(i);
				if (listData.get(j).get(listColumn.get(i)) == null
						|| listData.get(j).get(listColumn.get(i)).equals("null")) {
					cell.setCellValue("");
				} else {
					cell.setCellValue((String) "" + listData.get(j).get(listColumn.get(i)));
				}
				cell.setCellStyle(style);
			}
		}
		// 第五步将生成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;
	}

	/**
	 * 上传数据
	 * 
	 * @throws Exception
	 * @throws IOException
	 */
	@Override
	public R<String> loadTemplateData(String tableId, MultipartFile multipartFile) throws Exception {
		StringBuffer fileSb = new StringBuffer(multipartFile.getOriginalFilename());
		fileSb.insert(fileSb.indexOf("."), "_" + System.currentTimeMillis());
		FileTool.uploadFile(multipartFile.getBytes(), "", fileSb.toString());
		File file = new File(fileSb.toString());
		// 获取表信息
		MdmCreateTableDTO table = daMdmFeign.getTableInfoById(tableId).getData();
		// 获取字段信息
		List<MdmInfoColumn> columns = daMdmFeign.listColumnByTableId(tableId);
		// 获取文件数据
		List<Map<String, Object>> dataList = xlsx(file);
		StringBuffer sqlSb = new StringBuffer();
		StringBuffer valuesSb = new StringBuffer();

		sqlSb.append("insert into ").append(table.getTableName());
		// 逗号隔开的字段
		StringBuffer columnSb = new StringBuffer();
		StringBuffer updateSb = new StringBuffer();

		for (Map<String, Object> data : dataList) {
			StringBuffer valueSb = new StringBuffer();
			// 第一行，标题行
			if (dataList.indexOf(data) == 0) {
				for (String key : data.keySet()) {
					columnSb.append(data.get(key)).append(",");
					updateSb.append(key).append("=values(").append(key).append("),");
				}
				continue;
			} else {
				for (String key : data.keySet()) {
					for (MdmInfoColumn c : columns) {
						// 全部转为小写做匹配
						if (c.getColumnName().toLowerCase().equals(key.toLowerCase())) {
							// 如果是文本型字段
							if (c.getIfChar() == 1 || c.getIsTime() == 1) {
								valueSb.append("'").append(data.get(key)).append("'").append(",");
							} else {
								if (data.get(key) == null || data.get(key).equals("")) {
									valueSb.append("null").append(",");
								} else {
									valueSb.append(data.get(key)).append(",");
								}
							}
						}
					}
				}
				// 去除最后一位逗号
				valueSb.deleteCharAt(valueSb.length() - 1);
				valuesSb.append(" (").append(valueSb.toString()).append("),");
			}
		}
		columnSb.deleteCharAt(columnSb.length() - 1);
		sqlSb.append(" (").append(columnSb.toString()).append(") ");
		valuesSb.deleteCharAt(valuesSb.length() - 1);
		updateSb.deleteCharAt(updateSb.length() - 1);
		sqlSb.append(" values ").append(valuesSb.toString()).append(" ");
		sqlSb.append(" ON DUPLICATE KEY UPDATE ").append(updateSb.toString());
		// System.out.println("sqlSb:" + sqlSb);
		// Log.info("sqlSb:" + sqlSb);
		Map<String, String> sqlMap = null;
		sqlMap = new HashMap<>();
		// 执行查询sql,获取数据
		sqlMap.put("sql", sqlSb.toString());
		Integer value = daMdmFeign.executeSql(table.getDatabaseId(), sqlMap);
		file.delete();
		R<String> r = new R<>();
		r.setMsg("上传成功");
		r.setData(value.toString());
		return r;
	}

	// 如果是xlsx，做什么操作
	protected List<Map<String, Object>> xlsx(File file) throws Exception {
		try {
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
			return show(xssfWorkbook);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;

	}

	// 他们两者都有相同的父类，而且都是父类中的方法操作，那我干脆点直接将他们用父类的形式抽取出来，这样解决了代码的冗余问题
	private List<Map<String, Object>> show(Workbook workbook) throws Exception {
		Row xssfRow = null;
		Cell data = null;
		// 每列标题
		String key = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			Sheet xssfSheet = workbook.getSheetAt(numSheet);
			// 这种主要是判断是否含空以免包空指针
			if (xssfSheet == null) {
				continue;
			}
			for (int i = 0; i > 0; i++) {
				getValue(xssfSheet.getRow(0).getCell(i), workbook);
			}
			// 获取展示数据格式，先行，再列-防止无限循环，默认1000列循环
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				Map<String, Object> rowDataMap = new HashMap<>();
				for (int current = 0; current < 1000; current++) {
					// 获取每列标题,判断标题为空时，停止文件扫描跳出循环
					if (xssfSheet.getRow(0).getCell(current) == null) {
						break;
					}
					key = getValue(xssfSheet.getRow(0).getCell(current), workbook);

					xssfRow = xssfSheet.getRow(rowNum);
					data = xssfRow.getCell(current);
					if (data == null || getValue(data, workbook).equals("")) {
						rowDataMap.put(key, "");
						continue;
					}
					rowDataMap.put(key, getValue(data, workbook));
				}
				resultList.add(rowDataMap);
			}
		}
		return resultList;
	}

	// 将他们两者的父类抽取出来，这样减少代码量，同时便于管理
	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;
	}

}
