package com.ozs.rpm.service.impl;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

import com.ozs.rpm.dto.KpiListColumnDTO;
import com.ozs.rpm.dto.ReportDTO;
import com.ozs.rpm.dto.ReportKPIDTO;
import com.ozs.rpm.dto.ReportSqlDTO;
import com.ozs.rpm.dto.data.table.TableHeader;
import com.ozs.rpm.dto.kpim.KpiConfigDTO;
import com.ozs.rpm.dto.save.RelReportKpiDrillDTO;
import com.ozs.rpm.dto.save.RelReportOutsideParamDTO;
import com.ozs.rpm.dto.save.ReportKpiDrillDown;
import com.ozs.rpm.dto.save.RpmInfoReportChartDTO;
import com.ozs.rpm.dto.save.SaveOneDTO;
import com.ozs.rpm.dto.save.SaveReportDTO;
import com.ozs.rpm.dto.save.SaveRportKpiDTO;
import com.ozs.rpm.dto.save.SaveTwoDTO;
import com.ozs.rpm.dto.view.ReportGroupKpiDTO;
import com.ozs.rpm.dto.view.ReportViewDataParam;
import com.ozs.rpm.dto.view.ReportViewQueryDTO;
import com.ozs.rpm.mapper.ReportMapper;
import com.ozs.rpm.service.ReportDataService;
import com.ozs.rpm.util.AccountApi;
import com.ozs.rpm.util.KpimApi;
import com.ozs.rpm.util.MdmApi;
import com.ozs.rpm.util.R;
import com.ozs.rpm.util.StringUtil;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ReportServiceImplUtil {
	@Autowired
	protected ReportMapper reportMapper;
	@Autowired
	protected KpimApi kpimApi;
	@Autowired
	protected MdmApi mdmApi;
	@Autowired
	protected AccountApi accountApi;

	@Autowired
	protected ReportDataService reportDataService;

	public R<SaveReportDTO> updateOne(SaveOneDTO saveDto) {
		R<SaveReportDTO> r = new R<>();
		// 名称查重
		ReportDTO report = reportMapper.getReportByName(saveDto.getReportName());
		if (report != null && !report.getId().equals(saveDto.getId())) {
			r.setCode("-1");
			r.setMsg("报表名称已存在");
			return r;
		}
		// 清空 分类报表关联
		reportMapper.updateMenuReportToNull(saveDto.getId());
		// reportMapper.updateReportMenuReport(saveDto.getMenuCode(),
		// saveDto.getId());
		// 删除报表分类关联
		reportMapper.removeRpmInfoReportMenuByReportId(saveDto.getId());

		// 判断只有主报表可以关联分类
		if (saveDto.getReportLevel() != null && saveDto.getReportLevel() == 1 && saveDto.getMenuCode() != null) {
			// 保存报表和分类关联关系
			reportMapper.saveRpmInfoReportMenu(saveDto.getMenuCode(), saveDto.getId());
		}
		reportMapper.updateOne(saveDto);
		// 更新报表数据sql
		reportDataService.saveReportSql(saveDto.getId());

		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;

	}

	public R<SaveReportDTO> updateTwo(SaveTwoDTO saveDto) {
		R<SaveReportDTO> r = new R<>();
		// 保存report_kpi
		List<SaveRportKpiDTO> kpis = saveDto.getKpis();
		List<String> kpiName = new ArrayList<String>();
		List<String> kpiIds = new ArrayList<String>();
		for (SaveRportKpiDTO k : kpis) {
			kpiIds.add(k.getKpiId());
			kpiName.add(k.getRname());
			k.setReportId(saveDto.getId());
			k.setSort(kpis.indexOf(k) + 1);
		}
		if (kpiName.size() != new HashSet<String>(kpiName).size()) {
			r.setCode("-1");
			r.setMsg("KPI命名重复");
			return r;
		}
		// 判断kpi是否是时间字段
		List<KpiConfigDTO> kpiDtos = kpimApi.listKpiConfigByKpiIds(kpiIds);
		for (SaveRportKpiDTO k : kpis) {
			for (KpiConfigDTO kpiDto : kpiDtos) {
				if (kpiDto.getId().equals(k.getKpiId())) {
					k.setKpiCode(kpiDto.getKpiCode());
					// kpi是否为时间类型
					if (kpiDto.getRuleType().equals("time")) {
						k.setIfTime(1);
					} else {
						k.setIfTime(0);
					}
					// kpi是否为数字类型
					if (kpiDto.getRuleType().equals("number")) {
						k.setIfNumber(1);
					} else {
						k.setIfNumber(0);
					}
					// kpi是否可编辑
					if (kpiDto.getEditStatus() == 1) {
						k.setEditStatus(1);
					} else {
						k.setEditStatus(0);
					}
				}
			}
		}
		// 修改前删除不存在的kpi
		reportMapper.removeNotKpi(kpiIds, saveDto.getId());
		reportMapper.updateUpReportKpi(kpis);
		reportMapper.updateSaveTwo(saveDto);
		// 删除没选中的kpi,图表配置
		reportMapper.deleteReportChartNotKpi(saveDto.getId(), kpis);

		// 更新报表数据sql
		// reportDataService.saveReportSql(saveDto.getId());

		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;

	}

	/**
	 * 报表编辑返回数据
	 * 
	 * @param reportId
	 * @return
	 */
	public SaveReportDTO getSaveReportDTO(String reportId) {
		// 使用可操作聚合字段kpiId
		List<String> operableGroupKpis = new ArrayList<>();
		SaveReportDTO r = reportMapper.getSaveReportById(reportId);
		// KPI 信息获取
		List<SaveRportKpiDTO> kpis = reportMapper.listSaveRportKpi(reportId);
		List<ReportKpiDrillDown> drillDowns = new ArrayList<>();
		List<RelReportKpiDrillDTO> kpiDrills = null;
		if (r.getIfDrillDown() != null && r.getIfDrillDown() == 1) {
			kpiDrills = reportMapper.listRelReportKpiDrill(reportId);
		}
		// 汇总kpi展示排序
		for (SaveRportKpiDTO kpi : kpis) {
			// 下钻kpi信息
			if (r.getIfDrillDown() == 1 && kpi.getIfRelated() != null && kpi.getIfRelated() == 1) {
				r.setIfRelated(1);
				ReportKpiDrillDown drillDown = new ReportKpiDrillDown();
				drillDown.setKpiId(kpi.getKpiId());
				drillDown.setIfRelated(kpi.getIfRelated());
				drillDown.setSubreportId(kpi.getSubreportId());
				List<RelReportKpiDrillDTO> drills = new ArrayList<>();
				for (RelReportKpiDrillDTO kpiDrill : kpiDrills) {
					if (kpiDrill.getKpiId() != null && kpiDrill.getKpiId().equals(kpi.getKpiId())) {
						drills.add(kpiDrill);
					}
				}
				drillDown.setKpiDrills(drills);
				drillDowns.add(drillDown);
			}
			// 排序默认kpi
			if (kpi.getIfQueryDefaultSort() != null && kpi.getIfQueryDefaultSort() == 1) {
				r.setQueryDefaultOrder(kpi.getQueryDefaultOrder());
				r.setQueryDefaultSortKpi(kpi.getKpiId());
			}
			/**
			 * 可操作性聚合kpi
			 */
			if (kpi.getIfOperableGroupBy() != null && kpi.getIfOperableGroupBy() == 1) {
				operableGroupKpis.add(kpi.getKpiId());
			} else {
				/**
				 * 处理未加可操作聚合功能前历史数据，实际逻辑不允许
				 */
				if (kpi.getIfQueryGroupBy() != null && kpi.getIfQueryGroupBy() == 1) {
					operableGroupKpis.add(kpi.getKpiId());
				}
			}

		}
		r.setDrillDowns(drillDowns);
		// 柱折混合图信息列表获取
		List<RpmInfoReportChartDTO> reportChartDtos = reportMapper.listBarAndLineCharts(reportId);
		// 饼状图kpi
		List<String> pieKpiIds = reportMapper.listChartPieKpiId(reportId);
		r.setIfBarAndLine(0);
		r.setIfPie(0);
		if (reportChartDtos != null && reportChartDtos.size() != 0) {
			r.setBarAndLineCharts(reportChartDtos);
			r.setIfBarAndLine(1);
		}
		if (pieKpiIds != null && pieKpiIds.size() != 0) {
			r.setPieKpiIds(pieKpiIds);
			r.setIfPie(1);
		}
		// 是否外部传参
		if (r.getIfOutsideParam() != null && r.getIfOutsideParam() == 1) {
			r.setReportOutsideParams(reportMapper.listRelReportOutsideParam(reportId));
		}
		// 汇总KPI
		List<String> totalKpis = reportMapper.listTotalKpis(reportId, 1);
		if (totalKpis != null && totalKpis.size() != 0) {
			r.setTotalKpis(totalKpis);
		}
		/**
		 * 可操作性聚合kpi
		 */
		if (operableGroupKpis != null && operableGroupKpis.size() != 0) {
			r.setOperableGroupKpis(operableGroupKpis);
		} else {
			r.setOperableGroupKpis(new ArrayList<>());
		}
		r.setKpis(kpis);
		return r;
	}

	/**
	 * 获取kpicode 和kpi查询时字段显示关联集合
	 * 
	 * @param reportId
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	protected Map<String, String> getKpiColumnValue(String reportId) throws Exception {
		// 获取报表查询sql
		ReportSqlDTO sqlDto = reportMapper.getReportSql(reportId, 0);
		String sql = sqlDto.getSqlReport();
		Map<String, String> kpiColumnValue = new HashMap<>();
		List<SaveRportKpiDTO> kpis = reportMapper.listSaveRportKpi(reportId);
		// 去空格，方便处理sql
		sql = sql.replaceAll(" ", "");
		for (SaveRportKpiDTO kpi : kpis) {
			// 不存在的code，跳出循环
			if (!sql.contains(kpi.getKpiCode())) {
				break;
			}
			String column = sql.substring(sql.indexOf("select") + 6, sql.lastIndexOf(kpi.getKpiCode()));

			// 最后一位查询kpi,与from 之间没有逗号分隔特殊处理
			if (kpis.indexOf(kpi) == kpis.size() - 1) {
				sql = "select" + sql.substring((column + kpi.getKpiCode() + ",").length() + 5, sql.length() - 1);
			} else {
				sql = "select" + sql.substring((column + kpi.getKpiCode() + ",").length() + 6, sql.length() - 1);
			}
			// 去除sql分组函数
			if (column.contains("sum(")) {
				column = column.replace("sum(", " ");
				column = column.replace("),", ",");
			}
			if (column.contains("avg(")) {
				column = column.replace("avg(", " ");
				column = column.replace("),", ",");
			}
			String kpiCode = kpi.getKpiCode();

			/**
			 * TODO 可操作性聚合
			 */
			column = column.replace(kpiCode + "_sumL", "");
			column = column.replace(kpiCode + "_sumR", "");
			kpiColumnValue.put(kpi.getKpiCode(), column);

		}
		return kpiColumnValue;
	}

	/**
	 * 获取报表下载的数据
	 * 
	 * @param reportVDP
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> getDownloadReportData(ReportViewDataParam reportVDP, String userId,
			List<SaveRportKpiDTO> kpiList) throws Exception {
		// 判断用户是管理员用户，展示报表的所有kpi权限
		if (accountApi.getUserType(userId) == 2) {
			userId = null;
		}
		/**
		 * 替换符号
		 */
		String where = "#where#";
		String order = "#order#";
		String group = "#group#";
		/**
		 * 获取图表类型
		 */
		String reportId = reportVDP.getReportId();
		Integer reportType = reportVDP.getReportType();
		/**
		 * 获取报表sql对象
		 */
		ReportSqlDTO sql = reportMapper.getReportSql(reportId, reportType);
		String databaseId = sql.getDatabaseId();
		String reportSql = sql.getSqlReport();
		Map<String, String> kpiColumnValue = getKpiColumnValue(reportId);
		/**
		 * 条件语句
		 */
		String orderSql = " order by ";
		String groupSql = " group by ";

		// KPIID-所有集合
		List<String> ids = new ArrayList<String>();
		// id对应重命名map
		Map<String, String> rnameMap = new HashMap<>();
		// id对应是否排序map
		Map<String, Integer> isOrderMap = new HashMap<>();
		/**
		 * TODO 自主可选聚合
		 */
		Boolean useGroup = false;
		List<String> groupKpiCodes = reportVDP.getGroupKpiCodes();
		if (groupKpiCodes != null && groupKpiCodes.size() != 0 && reportVDP.getReportType() == 0) {
			useGroup = true;
			for (String kpiCode : groupKpiCodes) {
				groupSql += " " + kpiCode + ",";
			}
			for (SaveRportKpiDTO kpi : kpiList) {
				String kpiCode = kpi.getKpiCode();
				// 非聚合非数字类型字段显示为'-'
				if ((kpi.getIfNumber() == null || kpi.getIfNumber() == 0)
						&& !groupKpiCodes.contains(kpi.getKpiCode())) {
					reportSql = reportSql.replace(kpiColumnValue.get(kpi.getKpiCode()), "'——'");
				} else if ((kpi.getIfNumber() == null || kpi.getIfNumber() == 1)
						&& !groupKpiCodes.contains(kpi.getKpiCode())) {
					// 数字类型且非聚合字段，进行sum函数计算
					reportSql = reportSql.replaceAll(kpiCode + "_sumL", "sum(");
					reportSql = reportSql.replaceAll(kpiCode + "_sumR", ")");
				}
			}
		}

		/**
		 * 默认排序
		 */
		String defaultOrder = "";
		Boolean flag = false; // true为使用默认排序
		for (SaveRportKpiDTO kpi : kpiList) {
			String kpiCode = kpi.getKpiCode();
			ids.add(kpi.getKpiId());
			rnameMap.put(kpi.getKpiId(), kpi.getRname());
			reportSql = reportSql.replaceAll(kpiCode + "_sumL", "");
			reportSql = reportSql.replaceAll(kpiCode + "_sumR", "");
		}
		/**
		 * 传值排序
		 */
		if (reportVDP.getSort() != null && !reportVDP.getSort().equals("")) {
			// 拼接排序，默认只有一个字段排序
			defaultOrder += reportVDP.getSort() + " " + reportVDP.getOrder();
			flag = true;
		}
		if (flag) {
			orderSql += " " + defaultOrder;
		} else {
			orderSql = "";
		}
		/**
		 * 报表所有KPI详细信息
		 */
		List<KpiConfigDTO> kpiConfigList = kpimApi.listKpiConfigByKpiIds(ids);
		// code对应名称map
		Map<String, String> codeNameMap = new HashMap<>();
		// code对应单位map
		Map<String, String> codeUnitMap = new HashMap<>();
		// id对应code
		Map<String, String> codeMap = new HashMap<>();
		// kpiid对应字段id
		Map<String, String> kToCMap = new HashMap<>();
		// kpicode对应字段id
		Map<String, String> codeToCMap = new HashMap<>();
		// kpicode对应字段id
		Map<String, Integer> codeToRule = new HashMap<>();
		for (KpiConfigDTO kpiConfig : kpiConfigList) {
			String kpiId = kpiConfig.getId();
			String code = kpiConfig.getKpiCode();
			String unit = kpiConfig.getUnit();
			codeUnitMap.put(code.toUpperCase(), unit);
			codeMap.put(kpiId, code.toUpperCase());
			codeNameMap.put(code, rnameMap.get(kpiId));
			if (kpiConfig.getDefinedTypeInfo() != null) {
				kToCMap.put(kpiId, kpiConfig.getDefinedTypeInfo().getColumnId());
				codeToCMap.put(code, kpiConfig.getDefinedTypeInfo().getColumnId());
			}
			codeToRule.put(code, kpiConfig.getRuleTypeId());
		}

		/**
		 * 拼接条件，3种，1.时间搜索 2.关键字搜索 3分组搜索
		 */
		// 查询条件
		List<ReportViewQueryDTO> queryKpis = reportVDP.getQueryKpis();
		StringBuffer whereSqlSb = new StringBuffer("");

		if (queryKpis != null) {
			List<String> cList = null;
			/**
			 * 时间条件要放到where中拼接
			 */
			StringBuffer timeSb = null;
			for (ReportViewQueryDTO queryKpi : queryKpis) {
				if (queryKpi.getIfQueryTime() != null && queryKpi.getIfQueryTime() == 1) { // 时间
					// 获取时间类型KPI对应字段库和名
					cList = new ArrayList<>();
					cList.add(kToCMap.get(queryKpi.getKpiId()));
					List<KpiListColumnDTO> columnList = mdmApi.listColumnByIds(cList);
					if (columnList != null && columnList.get(0) != null) {
						if (queryKpi.getValueArr() != null && queryKpi.getValueArr().size() != 0) {
							timeSb = new StringBuffer();
							if (StringUtil.isNotEmpty(queryKpi.getValueArr().get(0))) {
								/*
								 * timeSb.append(" and str_to_date(").append(
								 * kpiColumnValue.get(queryKpi.getKpiCode()))
								 * .append(",'%Y-%m-%d')").append(
								 * ">=str_to_date('")
								 * .append(queryKpi.getValueArr().get(0)).append
								 * ("','%Y-%m-%d')");
								 */
								if (codeToRule.get(queryKpi.getKpiCode()) == 8) {
									// yyyy-MM-dd HH:mm:ss
									timeSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append(">='").append(queryKpi.getValueArr().get(0)).append(" 00:00:00'");
								} else {
									timeSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append(">='").append(queryKpi.getValueArr().get(0)).append("'");
								}

							}
							if (StringUtil.isNotEmpty(queryKpi.getValueArr().get(1))) {
								/*
								 * timeSb.append(" and str_to_date(").append(
								 * kpiColumnValue.get(queryKpi.getKpiCode()))
								 * .append(",'%Y-%m-%d')").append(
								 * "<=str_to_date('")
								 * .append(queryKpi.getValueArr().get(1)).append
								 * ("','%Y-%m-%d')");
								 */
								if (codeToRule.get(queryKpi.getKpiCode()) == 8) {
									// yyyy-MM-dd HH:mm:ss
									timeSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append("<='").append(queryKpi.getValueArr().get(1)).append(" 23:23:59'");
								} else {
									timeSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append("<='").append(queryKpi.getValueArr().get(1)).append("'");
								}

							}
							whereSqlSb.append(timeSb);
						}
					}
				}
				if (queryKpi.getIfQuerySearch() != null && queryKpi.getIfQuerySearch() == 1) { // 关键字和分组
					switch (queryKpi.getSearchTypeId()) {
					case 1: // 模糊
						if (queryKpi.getKpiCode() != null && !queryKpi.getKpiCode().trim().equalsIgnoreCase("")
								&& queryKpi.getValue() != null && !queryKpi.getValue().trim().equalsIgnoreCase("")) {
							whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
									.append(" like '%").append(queryKpi.getValue()).append("%'");
						}
						break;
					case 2: // 精准和分组
						if (queryKpi.getKpiCode() != null && !queryKpi.getKpiCode().trim().equalsIgnoreCase("")) {
							// 判断条件内容是否为空
							if (queryKpi.getValue() != null && !"".equals(queryKpi.getValue())) {
								SaveRportKpiDTO kpiDto = reportMapper.getRelReportKpiByKpiCode(reportId,
										queryKpi.getKpiCode());
								// 判断kpi是否为数字类型
								if (kpiDto.getIfNumber() == 1) {
									whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append("=").append(queryKpi.getValue()).append("");
								} else {
									whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append("='").append(queryKpi.getValue()).append("'");
								}
							}

						}
						break;
					case 3: // in搜索
						StringBuffer vsb = new StringBuffer();
						if (queryKpi.getValueArr() != null) {
							SaveRportKpiDTO kpiDto = reportMapper.getRelReportKpi(reportId, queryKpi.getKpiId());
							// 判断kpi是否为数字类型
							if (kpiDto.getIfNumber() == 1) {
								for (String value : queryKpi.getValueArr()) {
									vsb.append(value).append(",");
								}
							} else {
								for (String value : queryKpi.getValueArr()) {
									vsb.append("'").append(value).append("',");
								}
							}
							if (queryKpi.getValueArr() != null && queryKpi.getValueArr().size() > 0) {
								vsb.deleteCharAt(vsb.length() - 1);
							}
						}
						if (vsb.length() != 0) {
							whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode())).append(" in (")
									.append(vsb).append(")");
						}
						break;
					default:
						break;
					}
				}
			}
		}
		/**
		 * 分组条件
		 */
		ReportGroupKpiDTO groupKpi = reportVDP.getGroupKpi();
		if (groupKpi != null && groupKpi.getValue() != "") {
			List<String> cList = null;
			// 获取时间类型KPI对应字段库和名
			cList = new ArrayList<>();
			cList.add(codeToCMap.get(groupKpi.getKpiCode()));
			StringBuffer groupSb = new StringBuffer("");
			SaveRportKpiDTO kpiDto = reportMapper.getRelReportKpiByKpiCode(reportId, groupKpi.getKpiCode());
			// 判断kpi是否为数字类型
			if (kpiDto.getIfNumber() != null && kpiDto.getIfNumber() == 1) {
				groupSb.append(" and ").append(kpiColumnValue.get(kpiDto.getKpiCode())).append("=")
						.append(groupKpi.getValue());
			} else {
				groupSb.append(" and ").append(kpiColumnValue.get(kpiDto.getKpiCode())).append("='")
						.append(groupKpi.getValue()).append("'");
			}
			whereSqlSb.append(groupSb);

		}
		// 是否传递外部参数
		if (reportVDP.getIfOutsideParam() != null && reportVDP.getIfOutsideParam() == 1) {
			List<RelReportOutsideParamDTO> params = reportMapper.listRelReportOutsideParam(reportId);
			String[] paramValues = reportVDP.getIfOutsideParamValue().split(",");
			for (int i = 0; i < paramValues.length; i++) {
				RelReportOutsideParamDTO param = params.get(i);
				// 参数类型1 文本
				if (!paramValues[i].equals("") && param.getParamType() == 1) {
					// 外部传参拼接sql
					whereSqlSb.append(" and ").append(param.getParamKey()).append(" = '").append(paramValues[i])
							.append("'");
				} else if (!paramValues[i].equals("") && param.getParamType() == 2) {
					// 参数类型2 数字
					// 外部传参拼接sql
					whereSqlSb.append(" and ").append(param.getParamKey()).append(" = ").append(paramValues[i]);
				}
			}
		}
		// }
		// 最终的报表查询sql
		// String reportSqlFinal = "select * from (#reportSql#) a where 1=1 ";
		String reportSqlFinal = "#reportSql# ";

		/**
		 * 1.查询当前用户的KPI权限 2.过滤数据，删除没有权限的KPI数据
		 */
		List<ReportKPIDTO> usedKpiInfoList = null;
		List<String> usedKpiCodeList = new ArrayList<>();
		if (StringUtil.isNotEmpty(userId)) {
			usedKpiInfoList = reportMapper.listKpiByReportAndUser(reportId, userId);
			for (ReportKPIDTO kpiInfo : usedKpiInfoList) {
				usedKpiCodeList.add(codeMap.get(kpiInfo.getKpiId()));
			}
		}
		// 1.表格
		/**
		 * TODO 是否存在聚合字段
		 */
		if (useGroup) {
			groupSql = groupSql.substring(0, groupSql.length() - 1);
			reportSql = reportSql.replaceAll(group, groupSql);
		} else {
			reportSql = reportSql.replaceAll(group, " ");
		}

		// 1.拼接table
		List<TableHeader> tableHeader = new ArrayList<>();
		TableHeader header = null;
		for (KpiConfigDTO kpiConfig : kpiConfigList) {
			String kpiId = kpiConfig.getId();
			header = new TableHeader();
			header.setId(kpiConfig.getKpiCode());
			header.setName(rnameMap.get(kpiId));
			if (isOrderMap.get(kpiId) != null) {
				header.setIfOrder(isOrderMap.get(kpiId));
			} else {
				header.setIfOrder(0);
			}
			tableHeader.add(header);
		}
		// 最后替换下没用的sql替换符
		reportSql = reportSql.replaceAll(where, whereSqlSb.toString()).replaceAll(order, orderSql);
		reportSqlFinal = reportSqlFinal.replaceAll("#reportSql#", reportSql);
		// 去掉 #page#
		reportSqlFinal = reportSqlFinal.replaceAll("#page#", "");
		log.info("导出表格sql：{}", reportSqlFinal);
		// 查询图表数据
		Map<String, String> reportSqlMap = new HashMap<>();
		reportSqlMap.put("sql", reportSqlFinal);
		List<Map<String, Object>> tableMap = null;
		tableMap = mdmApi.listData(databaseId, reportSqlMap);

		return tableMap;
	}

	// ----------------------------添加图表设置---------------------------------------
	// 文件导入路径
	@Value("${import.file.path}")
	protected String importPath;
	// 文件导入文件夹

	@Value("${import.file.url}")
	protected String fileUrl;
	/**
	 * 文件名入库成功后缀_succ
	 */
	protected final static String FILE_SUCC = "_succ";

	/**
	 * 检查文件，文件重命名加_succ后缀，标示入库成功。并删除没有成功入库的文件
	 * 
	 * @return
	 */
	protected String checkFile(String filePath, String userId) {
		StringBuffer fpsb = new StringBuffer(filePath);
		// 获取文件存储的绝对路径
		File file = new File(filePath.replaceAll(fileUrl, importPath));
		// 重命名文件，加上_succ 入库成功后缀
		fpsb.insert(fpsb.lastIndexOf("."), FILE_SUCC);
		file.renameTo(new File(fpsb.toString().replaceAll(fileUrl, importPath)));
		// 删除用户文件夹中没有_succ后缀的文件
		File dir = new File(importPath + userId + File.separator);
		File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
		// 删除没有_succ后缀文件
		for (File f : files) {
			if (f.getName().indexOf(FILE_SUCC) == -1) {
				f.delete();
			}
		}
		return fpsb.toString();
	}

	/**
	 * 富文本上传文件，检查用户文件夹，文件重命名加_succ后缀，标示入库成功。并删除没有成功入库的文件
	 * 
	 * @return
	 */
	protected String checkRichTextFile(String richText, String userId) {
		StringBuffer fpsb = null;
		// 获取文件存储的绝对路径
		String path = importPath + userId + File.separator;
		File dir = new File(path);
		File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
		// 删除没有_succ后缀文件
		for (File f : files) {
			String name = f.getName();
			// 判断没有_succ后缀的文件
			if (f.getName().indexOf(FILE_SUCC) == -1) {
				if (!richText.contains(name)) {
					// 删除没有在富文本中包含的文件
					f.delete();
				} else {
					// 在富文本中使用的文件,重命名文件加_succ后缀
					fpsb = new StringBuffer(name);
					// 重命名文件，加上_succ 入库成功后缀
					fpsb.insert(fpsb.lastIndexOf("."), FILE_SUCC);
					f.renameTo(new File(path + fpsb));
					// 替换富文本中文件名称。加_succ后缀
					richText = richText.replaceAll(name, fpsb.toString());
				}
			}
		}
		return richText;
	}
}
