package com.ozs.rpm.service.impl;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.util.HtmlUtils;

import com.ozs.rpm.dto.KpiListColumnDTO;
import com.ozs.rpm.dto.ReportDTO;
import com.ozs.rpm.dto.ReportKPIDTO;
import com.ozs.rpm.dto.ReportKpiRelUserDTO;
import com.ozs.rpm.dto.ReportRelUserDTO;
import com.ozs.rpm.dto.ReportSqlDTO;
import com.ozs.rpm.dto.RpmDimTypeAll;
import com.ozs.rpm.dto.RpmRelReportKpiUserDTO;
import com.ozs.rpm.dto.UserDTO;
import com.ozs.rpm.dto.chart.ChartTypeDTO;
import com.ozs.rpm.dto.chart.RichTextFileR;
import com.ozs.rpm.dto.data.KovDTO;
import com.ozs.rpm.dto.data.ReportDataDTO;
import com.ozs.rpm.dto.data.barOrLine.BarOrLine;
import com.ozs.rpm.dto.data.barOrLine.Series;
import com.ozs.rpm.dto.data.barOrLine.XAxis;
import com.ozs.rpm.dto.data.barOrLine.YAxis;
import com.ozs.rpm.dto.data.pie.Pie;
import com.ozs.rpm.dto.data.pie.SeriesData;
import com.ozs.rpm.dto.data.table.Table;
import com.ozs.rpm.dto.data.table.TableHeader;
import com.ozs.rpm.dto.kpim.KpiConfigDTO;
import com.ozs.rpm.dto.mdm.MdmCreateTableDTO;
import com.ozs.rpm.dto.mdm.MdmInfoColumn;
import com.ozs.rpm.dto.mdm.MdmInfoDatabase;
import com.ozs.rpm.dto.mdm.MdmInfoTable;
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.SaveFiveDTO;
import com.ozs.rpm.dto.save.SaveFourDTO;
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.SaveThreeDTO;
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.service.ReportService;
import com.ozs.rpm.util.CSVUtils;
import com.ozs.rpm.util.DateUtils;
import com.ozs.rpm.util.FileTool;
import com.ozs.rpm.util.PageUtil;
import com.ozs.rpm.util.R;
import com.ozs.rpm.util.StringUtil;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class ReportServiceImpl extends ReportServiceImplUtil implements ReportService {

	@Override
	public R<List<ReportDTO>> listReport(String reportName, Integer reportLevel, Integer ifFinish, Integer page,
			Integer pageSize, String sort, String order) throws Exception {
		R<ReportDTO> r = new R<>();
		r.pageStart(sort, order, page, pageSize);
		List<ReportDTO> list = reportMapper.listReport(reportName, reportLevel, ifFinish);
		return r.page(list);
	}

	@Transactional
	@Override
	public R<String> updateStatus(String id, ReportDTO Status) throws Exception {
		R<String> r = new R<String>();
		if (Status.getStatus() == 1) {
			reportDataService.saveReportSql(id);
		}
		// if (Status.getStatus() == 0) {
		// Status.setStatus(1);
		// }
		reportMapper.updateStatus(id, Status.getStatus());
		r.setData("修改成功");
		return r;
	}

	@Transactional
	@Override
	public R<String> removeReport(String id) throws Exception {
		R<String> r = new R<String>();
		ReportDTO report = reportMapper.getReportById(id);
		// 判断是否启动中
		if (report.getStatus() == 1) {
			r.setCode("-1");
			r.setMsg("请停用报表后删除~");
		}
		// 判断子报表是否在使用
		if (report.getReportLevel() == 1 && reportMapper.countSubReportById(id) > 0) {
			r.setCode("-1");
			r.setMsg("正在使用的子报表，不可删除！");
		}
		// 删除报表kpi信息
		reportMapper.removeReportKpi(id);
		// 删除报表用户关联信息
		reportMapper.removeReportRelUserByReportId(id);
		// 清空菜单报表关联信息
		// reportMapper.updateMenuReportToNull(id);
		// 删除报表分类关联
		reportMapper.removeRpmInfoReportMenuByReportId(id);
		// 删除报表图标信息
		reportMapper.removeReportChart(id);
		reportMapper.removeReport(id);
		r.setData("删除成功");
		return r;
	}

	@Override
	public R<List<RpmDimTypeAll>> listReportType(Integer typeId) throws Exception {
		return new R<List<RpmDimTypeAll>>(reportMapper.listReportType(typeId));
	}

	@Transactional
	@Override
	public R<SaveReportDTO> saveOne(SaveOneDTO saveDto) {

		// 判断修改操作还是新增动作
		if (saveDto.getId() != null && !"".equals(saveDto.getId())) {
			return updateOne(saveDto);
		}
		R<SaveReportDTO> r = new R<>();
		// 名称查重
		ReportDTO report = reportMapper.getReportByName(saveDto.getReportName());
		if (report != null) {
			r.setCode("-1");
			r.setMsg("报表名称已存在");
			return r;
		}
		saveDto.setId(UUID.randomUUID().toString());
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(0);
		// 第一步
		saveDto.setCurrentStep(1);
		// 判断只有主报表可以关联分类
		if (saveDto.getReportLevel() != null && saveDto.getReportLevel() == 1 && saveDto.getMenuCode() != null) {
			// 保存报表和分类关联关系
			reportMapper.saveRpmInfoReportMenu(saveDto.getMenuCode(), saveDto.getId());
		}
		reportMapper.saveOne(saveDto);

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

		return r;
	}

	@Transactional
	@Override
	public R<SaveReportDTO> saveTwo(SaveTwoDTO saveDto) {
		ReportDTO report = reportMapper.getReportById(saveDto.getId());
		// 判断修改操作还是新增动作
		if (report.getCurrentStep() >= 2) {
			return updateTwo(saveDto);
		}
		R<SaveReportDTO> r = new R<>();
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(0);
		// 第二步
		saveDto.setCurrentStep(2);
		// 保存report_kpi
		List<SaveRportKpiDTO> kpis = saveDto.getKpis();
		List<String> kpiName = new ArrayList<String>();
		List<String> kpiIds = new ArrayList<String>();

		// 图表存在没有kpi配置 TODO
		if (kpis != null) {
			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);
						}
					}
				}
			}
		}

		reportMapper.saveReportKpi(kpis);
		reportMapper.updateSaveTwo(saveDto);
		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;
	}

	@Transactional
	@Override
	public R<SaveReportDTO> saveThree(SaveThreeDTO saveDto) {
		R<SaveReportDTO> r = new R<>();
		SaveRportKpiDTO dto = null;
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(0);
		// 第三步
		saveDto.setCurrentStep(3);

		List<SaveRportKpiDTO> updateKpis = new ArrayList<>();
		// 排序KPI)
		List<String> orderKpi = saveDto.getOrderKpis();
		// 聚合KPI
		List<String> groupKpi = saveDto.getGroupKpis();
		// 汇总KPI
		List<String> totalKpi = saveDto.getTotalKpis();
		// 查询条件KPI
		List<SaveRportKpiDTO> queryKpis = saveDto.getQueryKpis();
		// 标记KPI
		List<SaveRportKpiDTO> colorKpis = saveDto.getColorKpis();
		// 使用可操作聚合字段kpiId
		List<String> operableGroupKpis = saveDto.getOperableGroupKpis();
		// 判断整合排序kpi入库数据
		if (orderKpi != null && orderKpi.size() != 0) {
			for (String ok : orderKpi) {

				dto = new SaveRportKpiDTO();
				dto.setKpiId(ok);
				dto.setIfQueryOrderBy(1);
				// 是否是默认排序
				dto.setIfQueryDefaultSort(0);
				if (saveDto.getQueryDefaultSortKpi() != null && ok.equals(saveDto.getQueryDefaultSortKpi())) {
					dto.setIfQueryDefaultSort(1);
					dto.setQueryDefaultOrder(saveDto.getQueryDefaultOrder());
				}
				updateKpis.add(dto);
			}
		}
		// 判断整合聚合kpi入库数据
		if (groupKpi != null && groupKpi.size() != 0) {
			for (String gk : groupKpi) {
				dto = new SaveRportKpiDTO();
				dto.setKpiId(gk);
				dto.setIfQueryGroupBy(1);
				dto.setQueryGroupBySort(groupKpi.indexOf(gk) + 1);
				updateKpis.add(dto);
			}
		}
		// 判断整合聚合kpi入库数据
		if (operableGroupKpis != null && operableGroupKpis.size() != 0) {
			for (String gk : operableGroupKpis) {
				dto = new SaveRportKpiDTO();
				dto.setKpiId(gk);
				dto.setIfOperableGroupBy(1);
				updateKpis.add(dto);
			}
		}
		// 判断整合kpi查询条件入库数据
		if (queryKpis != null && queryKpis.size() != 0) {
			for (SaveRportKpiDTO qkpi : queryKpis) {
				qkpi.setReportId(saveDto.getId());
				qkpi.setQuerySearchSort(queryKpis.indexOf(qkpi) + 1);
			}
			updateKpis.addAll(queryKpis);
		}
		// 判断整合kpi标记颜色条件入库数据
		if (colorKpis != null && colorKpis.size() != 0) {
			for (SaveRportKpiDTO ckpi : colorKpis) {
				ckpi.setIfQueryColor(1);
				ckpi.setReportId(saveDto.getId());
			}
			updateKpis.addAll(colorKpis);
		}
		// 判断整合汇总kpi入库数据,合计数据展示位置 1-数据下方 2 报表上方
		if (saveDto.getIfTotal() == 1 && saveDto.getTotalShowType() == 2 && totalKpi != null && totalKpi.size() != 0) {
			for (String tk : totalKpi) {
				dto = new SaveRportKpiDTO();
				dto.setKpiId(tk);
				dto.setIfTotal(1);
				dto.setTotalShowSort(totalKpi.indexOf(tk) + 1);
				updateKpis.add(dto);
			}
		}
		// 判断修改操作还是新增动作,修改操作先删除第三步kpi配置
		ReportDTO report = reportMapper.getReportById(saveDto.getId());
		if (report.getCurrentStep() >= 3) {
			// 先清空第三步kpi配置，再更新
			reportMapper.cleanKpiConfigThree(saveDto.getId());
			// 修改操作不修改状态信息
			saveDto.setStatus(null);
			saveDto.setIfFinish(null);
			saveDto.setCurrentStep(null);
		}
		// 是否外传参数
		if (saveDto.getIfOutsideParam() != null && saveDto.getIfOutsideParam() == 1) {
			int sort = 1;
			for (RelReportOutsideParamDTO param : saveDto.getReportOutsideParams()) {
				param.setParamSort(sort);
				param.setReportId(saveDto.getId());
				sort++;
			}
			reportMapper.removeRelReportOutsideParam(saveDto.getId());
			reportMapper.saveRelReportOutsideParam(saveDto.getReportOutsideParams());
		}
		// kpi属性入库
		if (updateKpis != null && updateKpis.size() != 0) {
			reportMapper.updateReportKpi(updateKpis, saveDto.getId());
		}
		reportMapper.updateSaveThree(saveDto);
		// 更新报表数据sql
		if (report.getIfFinish() == 1) {
			reportDataService.saveReportSql(saveDto.getId());
		}
		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;
	}

	@Transactional
	@Override
	public R<SaveReportDTO> saveFour(SaveFourDTO saveDto) {
		R<SaveReportDTO> r = new R<>();
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(0);
		// 第四步
		saveDto.setCurrentStep(4);
		// 判断修改操作还是新增动作,修改操作先删除第四步kpi配置
		ReportDTO report = reportMapper.getReportById(saveDto.getId());
		if (report.getCurrentStep() >= 4) {
			// 先清空第四步kpi配置，再更新
			reportMapper.cleanKpiConfigFour(saveDto.getId());
			// 修改操作不修改状态信息
			saveDto.setStatus(null);
			saveDto.setIfFinish(null);
			saveDto.setCurrentStep(null);
		}
		if (saveDto.getDrillDowns() != null && saveDto.getIfDrillDown() == 1) {
			// 修改KPI是否下钻
			reportMapper.updateReportKpiFour(saveDto.getDrillDowns(), saveDto.getId());
			// 下钻参数多参数
			List<RelReportKpiDrillDTO> kpiDrills = new ArrayList<>();
			for (ReportKpiDrillDown drillDown : saveDto.getDrillDowns()) {
				for (RelReportKpiDrillDTO kpiDrill : drillDown.getKpiDrills()) {
					kpiDrill.setKpiId(drillDown.getKpiId());
					kpiDrill.setReportId(saveDto.getId());
					// kpiDrill.setTimeRangeOffset(drillDown.getTimeRangeOffset());
					// kpiDrill.setTimeRelatedSelected(drillDown.getTimeRelatedSelected());
					kpiDrill.setSort(drillDown.getKpiDrills().indexOf(kpiDrill) + 1);
					kpiDrills.add(kpiDrill);
				}
			}
			reportMapper.removeRelReportKpiDrill(saveDto.getId());
			reportMapper.saveRelReportKpiDrill(kpiDrills);
		}

		reportMapper.updateSaveFour(saveDto);
		// 更新报表数据sql
		if (report.getIfFinish() == 1) {
			reportDataService.saveReportSql(saveDto.getId());
		}
		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;
	}

	@Transactional
	@Override
	public R<SaveReportDTO> saveFive(SaveFiveDTO saveDto) {
		R<SaveReportDTO> r = new R<>();
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(0);
		// 第五步
		saveDto.setCurrentStep(5);
		// reportMapper.saveFive(saveDto);
		// 柱折混合图信息列表
		List<RpmInfoReportChartDTO> barAndLineCharts = saveDto.getBarAndLineCharts();
		if (barAndLineCharts != null && barAndLineCharts.size() != 0) {
			for (RpmInfoReportChartDTO chartDto : barAndLineCharts) {
				// 统计图类型：1-柱折混合图，2-饼图
				chartDto.setChartTypeId(1);
				chartDto.setReportId(saveDto.getId());
			}
		}
		// 饼状图kpi
		List<String> pieKpiIds = saveDto.getPieKpiIds();
		// 判断是否勾选 柱折混合图 或者饼图
		if (saveDto.getIfBarAndLine() != null && saveDto.getIfBarAndLine() == 0) {
			barAndLineCharts.clear();
		}
		if (saveDto.getIfPie() != null && saveDto.getIfPie() == 0) {
			pieKpiIds.clear();
		}

		for (String pieKpi : pieKpiIds) {
			RpmInfoReportChartDTO chartDTO = new RpmInfoReportChartDTO();
			// 统计图类型：1-柱折混合图，2-饼图
			chartDTO.setChartTypeId(2);
			chartDTO.setKpiId(pieKpi);
			chartDTO.setReportId(saveDto.getId());
			barAndLineCharts.add(chartDTO);
		}
		// 判断修改操作还是新增动作,修改操作先删除第五步kpi配置和图标配置
		ReportDTO report = reportMapper.getReportById(saveDto.getId());
		if (report.getCurrentStep() >= 5) {
			// 先清空第五步kpi配置，再更新
			// reportMapper.cleanKpiConfigFive(saveDto.getId());
			reportMapper.deleteReportChartByReportId(saveDto.getId());
			// 修改操作不修改状态信息
			saveDto.setStatus(null);
			saveDto.setIfFinish(null);
			saveDto.setCurrentStep(null);
		}
		if (barAndLineCharts != null && barAndLineCharts.size() != 0) {
			// 保存图表信息
			reportMapper.saveReportChart(barAndLineCharts);
		}
		// 分组字段KPI
		String groupKpi = saveDto.getGroupKpi();

		// 图表类型操作
		if (report.getChartType() != null && report.getDisplaySystem() == 2) {
			// 富文本类型
			if (report.getChartType() == 4) {
				// 富文本文件上传检查
				saveDto.setRichText(checkRichTextFile(saveDto.getRichText(), saveDto.getUpdateUserId()));
				// 将富文本文档所有特殊字符转义为对应的数字以十六进制格式引用，用作保存
				saveDto.setRichText(HtmlUtils.htmlEscapeHex(saveDto.getRichText()));
			}
			// 图片和视频类型
			if (report.getChartType() == 5 || report.getChartType() == 6) {
				// 文件检查，文件重命名加_succ后缀，标示入库成功
				saveDto.setFilePath(checkFile(saveDto.getFilePath(), saveDto.getUpdateUserId()));
			}
		}
		reportMapper.updateSaveFive(saveDto);
		if (groupKpi != null) {
			reportMapper.updateGroupKpiReportKpi(groupKpi, saveDto.getId());
		}
		// 更新报表数据sql
		if (report.getIfFinish() == 1) {
			reportDataService.saveReportSql(saveDto.getId());
		}
		// 假接口返回数据
		r.setData(getSaveReportDTO(saveDto.getId()));
		return r;
	}

	@Override
	public R<List<ReportDTO>> listSonReport() {
		return new R<List<ReportDTO>>(reportMapper.listSonReport());
	}

	@Override
	public R<String> saveSix(SaveReportDTO saveDto) {
		R<String> r = new R<>();
		// 未启动
		saveDto.setStatus(0);
		// 未完成
		saveDto.setIfFinish(1);
		// 第六步
		saveDto.setCurrentStep(6);
		reportMapper.updateSaveSix(saveDto);
		reportDataService.saveReportSql(saveDto.getId());
		r.setData("报表保存完成");
		return r;
	}

	@Override
	public R<List<ReportDTO>> listParentReport(String menuCode) {
		return new R<List<ReportDTO>>(reportMapper.listParentReport(menuCode));
	}

	@Override
	public R<List<UserDTO>> listUser() {
		return new R<List<UserDTO>>(reportMapper.listUser());

	}

	@Override
	public R<List<ReportKpiRelUserDTO>> listRelUser(String reportId) {
		List<ReportKpiRelUserDTO> kpiRelUsers = new ArrayList<>();
		List<UserDTO> users = reportMapper.listRelUser(reportId);
		List<ReportKPIDTO> kpis = reportMapper.listKpiByReport(reportId);
		for (UserDTO user : users) {
			ReportKpiRelUserDTO kpiRelUser = new ReportKpiRelUserDTO();
			List<String> kpiIds = new ArrayList<>();
			for (ReportKPIDTO kpi : kpis) {
				if (user.getId().equals(kpi.getUserId())) {
					kpiIds.add(kpi.getKpiId());
				}
			}
			kpiRelUser.setNickname(user.getNickname());
			kpiRelUser.setUserId(user.getId());
			kpiRelUser.setKpiIds(kpiIds);
			kpiRelUsers.add(kpiRelUser);
		}

		return new R<List<ReportKpiRelUserDTO>>(kpiRelUsers);
	}

	@Override
	public R<SaveReportDTO> getReportInfoById(String id) {
		return new R<SaveReportDTO>(getSaveReportDTO(id));
	}

	/**
	 * 单个报表授权用户
	 */
	@Transactional
	@Override
	public R<String> reportRelUsers(ReportRelUserDTO reportRelUserDto) {
		R<String> r = new R<>();
		List<RpmRelReportKpiUserDTO> rkudtos = new ArrayList<>();
		for (ReportKpiRelUserDTO dto : reportRelUserDto.getKpiRelUsers()) {
			for (String kpiId : dto.getKpiIds()) {
				RpmRelReportKpiUserDTO rkudto = new RpmRelReportKpiUserDTO();
				rkudto.setKpiId(kpiId);
				rkudto.setReportId(reportRelUserDto.getReportId());
				rkudto.setUserId(dto.getUserId());
				rkudtos.add(rkudto);
			}

		}
		// 用户报表KPI关联
		reportMapper.removeReportKpiRelUserByReportId(reportRelUserDto.getReportId());
		if (rkudtos != null && rkudtos.size() != 0) {
			reportMapper.saveRpmRelReportKpiUser(rkudtos);
		}
		r.setData("授权完成");
		return r;
	}

	/**
	 * 批量个报表授权用户
	 */
	@Override
	public R<String> reportsRelUsers(ReportRelUserDTO reportRelUserDto) {
		// 获取kpi信息
		List<ReportKPIDTO> kpis = reportMapper.listKpiByReportIds(reportRelUserDto.getReportIds());
		List<RpmRelReportKpiUserDTO> rkudtos = new ArrayList<>();
		for (String userId : reportRelUserDto.getUserIds()) {
			for (ReportKPIDTO kpi : kpis) {
				RpmRelReportKpiUserDTO rkudto = new RpmRelReportKpiUserDTO();
				rkudto.setKpiId(kpi.getKpiId());
				rkudto.setReportId(kpi.getReportId());
				rkudto.setUserId(userId);
				rkudtos.add(rkudto);
			}
		}
		if (rkudtos != null && rkudtos.size() != 0) {
			reportMapper.saveRpmRelReportKpiUser(rkudtos);
		}
		R<String> r = new R<>();
		r.setData("授权完成");
		return r;
	}

	@Override
	public R<ReportDataDTO> getReportData(ReportViewDataParam reportVDP, String userId) throws Exception {
		// 判断用户是管理员用户，展示报表的所有kpi权限
		if (accountApi.getUserType(userId) == 2) {
			userId = null;
		}
		ReportDataDTO reportDataDTO = new ReportDataDTO();
		Table table = new Table();
		BarOrLine barOrLine = new BarOrLine();
		Pie pie = new Pie();
		/**
		 * 替换符号
		 */
		String where = "#where#";
		String page = "#page#";
		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();
		List<Map<String, Object>> tableMap = new ArrayList<>();

		Map<String, String> kpiColumnValue = getKpiColumnValue(reportId);
		/**
		 * 条件语句
		 */
		String pageSql = "";
		String orderSql = " order by ";
		String groupSql = " group by ";

		// 子报表KPI集合
		List<SaveRportKpiDTO> kpiList = reportMapper.listSaveRportKpi(reportId);
		// 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 (kpi.getIfQueryGroupBy() != null && kpi.getIfQueryGroupBy() ==
			// 1) {
			// /**
			// * 自主可选聚合
			// */
			// useGroup = true;
			// groupSql += " " + kpi.getKpiCode() + ",";
			// } else {
			// if (kpi.getIfNumber() != null && kpi.getIfNumber() == 0) {
			// reportSql =
			// reportSql.replace(kpiColumnValue.get(kpi.getKpiCode()), "'——'");
			// } else {
			// reportSql =
			// reportSql.replace(kpiColumnValue.get(kpi.getKpiCode()),
			// " sum(" + kpiColumnValue.get(kpi.getKpiCode()) + ") ");
			//
			// }
			// }
			// if (kpi.getIfQueryOrderBy() != null && kpi.getIfQueryOrderBy() ==
			// 1) {
			// isOrderMap.put(kpi.getKpiId(), kpi.getIfQueryOrderBy());
			// defaultOrder = kpi.getKpiCode() + ",";
			// }
		}
		/**
		 * 传值排序
		 */
		if (reportVDP.getSort() != null && !reportVDP.getSort().equals("")) {
			// 拼接排序，默认只有一个字段排序
			defaultOrder += reportVDP.getSort() + " " + reportVDP.getOrder();
			flag = true;
		}
		if (flag) {
			// defaultOrder = defaultOrder.substring(0, defaultOrder.length() -
			// 1);
			// defaultOrder += " desc";
			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<>();
		// code对应id
		Map<String, String> codeToIdMap = 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());
			codeToIdMap.put(code.toUpperCase(), kpiId);
			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(queryKpi.getKpiCode()).append(" like
							// '%")
							// .append(queryKpi.getValue()).append("%'");
							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.getRelReportKpi(reportId,
										codeToIdMap.get(queryKpi.getKpiCode()));
								// 判断kpi是否为数字类型
								if (kpiDto.getIfNumber() == 1) {
									whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
											.append("=").append(queryKpi.getValue()).append("");
								} else if (kpiDto.getIfTime() == 1) { // 判断kpi是否是时间类型
									/*
									 * 四种情况： 1.以选中时间为基准，偏移0天（等于选中时间）
									 * 2.以选中时间为基准，偏移N天（大于偏移时间，小于选中时间）
									 * 3.以当前时间为基准，偏移0天（等于当前时间）
									 * 4.以当前时间为基准，偏移N天（大于偏移时间，小于当前时间）
									 */

									// 是否以选中时间为基准：1是0否
									int timeRelatedSelected = queryKpi.getTimeRelatedSelected();
									// 偏移天数
									int past = queryKpi.getTimeRangeOffset();
									// 当前时间
									String todayStr = DateUtils.dateToStr(new Date());
									// 选中时间
									String dateSelected = queryKpi.getValue();
									// 偏移时间
									String dateOffset = null;

									if (timeRelatedSelected == 1 && past == 0) { // 1
										whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
												.append("='").append(dateSelected).append("'");
									} else if (timeRelatedSelected == 1 && past != 0) { // 2
										dateOffset = DateUtils.getOffsetDateInFront(dateSelected, past);
										whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
												.append(">='").append(dateOffset).append("'").append(" and ")
												.append(kpiColumnValue.get(queryKpi.getKpiCode())).append("<='")
												.append(dateSelected).append("'");
									} else if (timeRelatedSelected == 0 && past == 0) { // 3
										whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
												.append("='").append(todayStr).append("'");
									} else if (timeRelatedSelected == 0 && past != 0) { // 4
										dateOffset = DateUtils.getOffsetDateInFront(todayStr, past);
										whereSqlSb.append(" and ").append(kpiColumnValue.get(queryKpi.getKpiCode()))
												.append(">='").append(dateOffset).append("'").append(" and ")
												.append(kpiColumnValue.get(queryKpi.getKpiCode())).append("<='")
												.append(todayStr).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("'");
			}
			// whereSql += groupSb;
			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()));
			}
		}
		switch (reportType) {
		case 0:
			// 1.表格
			/**
			 * TODO 是否存在聚合字段
			 */
			if (useGroup) {
				groupSql = groupSql.substring(0, groupSql.length() - 1);
				reportSql = reportSql.replaceAll(group, groupSql);
			} else {
				reportSql = reportSql.replaceAll(group, " ");
			}
			String countSql = "select count(1) from (#table#) a where 1=1 ";
			countSql = countSql.replaceAll("#table#", reportSql);
			String totalSql = sql.getSqlTotal();
			// 拼接分页
			if (reportVDP.getIfPage() != null && reportVDP.getIfPage() == 1) {
				pageSql = " limit " + (reportVDP.getPage() - 1) * reportVDP.getPageSize() + ","
						+ reportVDP.getPageSize();
			}
			// 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(page, "").replaceAll(order,
					orderSql);

			reportSqlFinal = reportSqlFinal.replaceAll("#reportSql#", reportSql);
			reportSqlFinal += pageSql;
			log.info("表格sql：{}", reportSqlFinal);
			// 查询图表数据
			Map<String, String> reportSqlMap = new HashMap<>();
			reportSqlMap.put("sql", reportSqlFinal);
			tableMap = mdmApi.listData(databaseId, reportSqlMap);
			List<Map<String, Object>> casemapList = new ArrayList<>();
			Map<String, Object> casemap = new HashMap<>();

			// 判断用户
			if (userId != null) {
				for (Map<String, Object> map : tableMap) {
					casemap = new HashMap<>();
					for (Map.Entry<String, Object> m : map.entrySet()) {
						if (usedKpiCodeList.contains(m.getKey().toUpperCase())) { // 包含在权限内
							if (m.getValue() != null) {
								casemap.put(m.getKey().toUpperCase(), new StringBuffer().append(m.getValue())
										.append(codeUnitMap.get(m.getKey().toUpperCase())).toString());
							} else {
								casemap.put(m.getKey().toUpperCase(), "");
							}
						}
					}
					casemapList.add(casemap);
				}
			} else {
				for (Map<String, Object> map : tableMap) {
					casemap = new HashMap<>();
					for (Map.Entry<String, Object> m : map.entrySet()) {
						if (m.getValue() != null) {
							casemap.put(m.getKey().toUpperCase(), new StringBuffer().append(m.getValue())
									.append(codeUnitMap.get(m.getKey().toUpperCase())).toString());
						} else {
							casemap.put(m.getKey().toUpperCase(), "");
						}
					}
					casemapList.add(casemap);
				}
			}

			List<Map<String, Object>> countMap = new ArrayList<>();
			List<Map<String, Object>> totalMap = new ArrayList<>();

			totalSql = totalSql.replaceAll(where, whereSqlSb.toString()).replaceAll(page, "").replaceAll(order, "");

			log.info("合计sql：{}", totalSql);
			Map<String, String> totalSqlMap = new HashMap<>();
			totalSqlMap.put("sql", totalSql);
			totalMap = mdmApi.listData(databaseId, totalSqlMap);
			countSql = countSql.replaceAll(where, whereSqlSb.toString()).replaceAll(page, "").replaceAll(order, "");
			// countSql += whereSqlSb;
			log.info("计算总数sql：{}", countSql);
			Map<String, String> countSqlMap = new HashMap<>();
			countSqlMap.put("sql", countSql);
			countMap = mdmApi.listData(databaseId, countSqlMap);
			// 总条数
			int count = 0;
			for (Map<String, Object> map : countMap) {
				for (Map.Entry<String, Object> m : map.entrySet()) {
					count = Integer.parseInt(m.getValue().toString());
				}
			}
			Map<String, Object> total = null;
			// 如果要总数-里边
			Map<String, Object> totalInner = null;
			// 如果要总数-外边
			List<KovDTO> casetotal = null;
			KovDTO totalKov = null;
			if (reportVDP.getIfTotal() == 1 && totalMap.size() != 0) {
				totalInner = new HashMap<>();
				casetotal = new ArrayList<>();
				total = totalMap.get(0);
				List<String> drillsKpiCodes = null;
				// 内部默认所有kpi参与合计
				if (reportVDP.getTotalShowType() != null && reportVDP.getTotalShowType() == 1) {
					drillsKpiCodes = reportMapper.listTotalKpiCodes(reportId, -1);
				} else {
					// 查询合计kpiCode,实现合计展示排序
					drillsKpiCodes = reportMapper.listTotalKpiCodes(reportId, 1);
				}

				// 判断用户
				if (userId != null) {
					for (String kpiCode : drillsKpiCodes) {
						kpiCode = kpiCode.toLowerCase();

						// for (Map.Entry<String, Object> m : .entrySet())
						// {
						// total.get(key);
						if (usedKpiCodeList.contains(kpiCode.toUpperCase())) {
							// total 在数据里边
							if (reportVDP.getTotalShowType() != null && reportVDP.getTotalShowType() == 1) {
								if (total.get(kpiCode) != null) {
									totalInner.put(kpiCode.toUpperCase(), new StringBuffer().append(total.get(kpiCode))
											.append(codeUnitMap.get(kpiCode.toUpperCase())).toString());
								} else {
									totalInner.put(kpiCode.toUpperCase(), "");
								}
							} else if (total.get(kpiCode) != null && total.get(kpiCode).toString().trim().length() != 0
									&& !total.get(kpiCode).toString().contains("——")) { // total
								// 在数据外边
								totalKov = new KovDTO();
								totalKov.setKey(kpiCode.toUpperCase());
								totalKov.setName(codeNameMap.get(kpiCode.toUpperCase()));
								totalKov.setValue(new StringBuffer().append(total.get(kpiCode))
										.append(codeUnitMap.get(kpiCode.toUpperCase())).toString());
								casetotal.add(totalKov);
							}
						}
					}
				} else {
					for (String kpiCode : drillsKpiCodes) {
						kpiCode = kpiCode.toLowerCase();
						// for (Map.Entry<String, Object> m : total.entrySet())
						// {
						// total 在数据里边
						if (reportVDP.getTotalShowType() != null && reportVDP.getTotalShowType() == 1) {
							if (total.get(kpiCode) != null) {
								totalInner.put(kpiCode.toUpperCase(), new StringBuffer().append(total.get(kpiCode))
										.append(codeUnitMap.get(kpiCode.toUpperCase())).toString());
							} else {
								totalInner.put(kpiCode.toUpperCase(), "");
							}
						} else if (total.get(kpiCode) != null && total.get(kpiCode).toString().trim().length() != 0
								&& !total.get(kpiCode).toString().contains("——")) { // total
							// 在数据外边
							totalKov = new KovDTO();
							totalKov.setKey(kpiCode.toUpperCase());
							totalKov.setName(codeNameMap.get(kpiCode.toUpperCase()));
							totalKov.setValue(new StringBuffer().append(total.get(kpiCode))
									.append(codeUnitMap.get(kpiCode.toUpperCase())).toString());
							casetotal.add(totalKov);
						}
					}
				}
			}
			// 如果不要分页
			if (reportVDP.getIfPage() == 0) {
				reportVDP.setPage(0);
				reportVDP.setPageSize(0);
			}
			R<List<Map<String, Object>>> result = PageUtil.getResultAndTotal(reportVDP.getPage(),
					reportVDP.getPageSize(), count, casemapList, totalInner);
			table.setHeader(tableHeader);
			table.setData(result.getData());
			table.setTotal(casetotal);
			// 拼接结果
			reportDataDTO.setTable(table);
			R<ReportDataDTO> r = new R<>();
			r.setPage(result.getPage());
			r.setPageSize(result.getPageSize());
			r.setTotalNumber(result.getTotalNumber());
			r.setTotalPageCount(result.getTotalPageCount());
			r.setData(reportDataDTO);
			return r;
		case 1:
			reportSql = reportSql.replace(group, " ");

			// 因为有且只有一个分组KPI，获取group by和order之间去除空格的kpiCode
			String codeGroupBy = reportSql.substring(reportSql.indexOf("group by") + 8, reportSql.indexOf(order))
					.trim();
			// 暂时去掉排序
			// if(!flag) {
			orderSql = "";
			// }
			// 2.拼接混合图
			// 最后替换下没用的sql替换符
			reportSql = reportSql.replaceAll(where, whereSqlSb.toString()).replaceAll(page, pageSql).replaceAll(order,
					orderSql);
			reportSqlFinal = reportSqlFinal.replaceAll("#reportSql#", reportSql);
			// reportSqlFinal += whereSqlSb;
			log.info("柱折图sql：{}", reportSqlFinal);
			// 查询图表数据
			reportSqlMap = new HashMap<>();
			reportSqlMap.put("sql", reportSqlFinal);
			tableMap = mdmApi.listData(databaseId, reportSqlMap);
			int SeriesNumber = 0;
			List<String> dataCodeList = null;
			if (tableMap.size() > 0) {
				SeriesNumber = tableMap.get(0).size() - 1;
				dataCodeList = new ArrayList<>();
				for (Map.Entry<String, Object> m : tableMap.get(0).entrySet()) {
					if (!m.getKey().equalsIgnoreCase(codeGroupBy)) {
						dataCodeList.add(m.getKey());
					}
				}
			}
			// 获取报表柱折图KPI属性
			List<RpmInfoReportChartDTO> bolInfoList = reportMapper.listBarAndLineCharts(reportId);
			// 生成kpicode对应柱折图对象
			Map<String, RpmInfoReportChartDTO> codeToBolMap = new HashMap<>();
			for (RpmInfoReportChartDTO bolInfo : bolInfoList) {
				codeToBolMap.put(bolInfo.getKpiCode().toUpperCase(), bolInfo);
			}
			// x轴数组
			List<Object> xList = new ArrayList<>();
			// 数据数组
			List<Series> seriesList = new ArrayList<>();
			// 创建
			Series series = null;
			// 当前的柱折图属性
			RpmInfoReportChartDTO bolInfo = null;

			if (userId != null) {
				for (int i = 0; i < SeriesNumber; i++) {
					if (usedKpiCodeList.contains(dataCodeList.get(i).toUpperCase())) { // 包含在权限内
						series = new Series();
						series.setData(new ArrayList<>());
						series.setUnit(codeUnitMap.get(dataCodeList.get(i).toUpperCase()));
						series.setName(codeNameMap.get(dataCodeList.get(i).toUpperCase()));
						bolInfo = codeToBolMap.get(dataCodeList.get(i).toUpperCase());
						if (bolInfo.getGraph() != null && bolInfo.getGraph() == 2) {
							series.setType("line");
						}
						if (bolInfo.getPosition() != null && bolInfo.getPosition() == 2) {
							series.setyAxisIndex(1);
						}
						seriesList.add(series);
					}
				}
				// 循环数据
				for (Map<String, Object> map : tableMap) {
					int num = 0;
					for (Map.Entry<String, Object> m : map.entrySet()) {
						String key = m.getKey();
						if (key.equalsIgnoreCase(codeGroupBy)) {
							xList.add(m.getValue());
						} else if (usedKpiCodeList.contains(m.getKey().toUpperCase())) {
							if (m.getValue() != null) {
								seriesList.get(num).getData().add(m.getValue().toString().replace(",", ""));
							} else {
								seriesList.get(num).getData().add("0");
							}
							num += 1;
						}
					}
				}
			} else {
				for (int i = 0; i < SeriesNumber; i++) {
					series = new Series();
					series.setData(new ArrayList<>());
					series.setUnit(codeUnitMap.get(dataCodeList.get(i).toUpperCase()));
					series.setName(codeNameMap.get(dataCodeList.get(i).toUpperCase()));
					bolInfo = codeToBolMap.get(dataCodeList.get(i).toUpperCase());
					if (bolInfo.getGraph() != null && bolInfo.getGraph() == 2) {
						series.setType("line");
					}
					if (bolInfo.getPosition() != null && bolInfo.getPosition() == 2) {
						series.setyAxisIndex(1);
					}
					seriesList.add(series);
				}
				// 循环数据
				for (Map<String, Object> map : tableMap) {
					int num = 0;
					for (Map.Entry<String, Object> m : map.entrySet()) {
						String key = m.getKey();
						if (key.equalsIgnoreCase(codeGroupBy)) {
							xList.add(m.getValue());
						} else {
							if (m.getValue() != null) {
								seriesList.get(num).getData().add(m.getValue().toString().replace(",", ""));
							} else {
								seriesList.get(num).getData().add("0");
							}
							num += 1;
						}
					}
				}
			}

			XAxis xAxis = new XAxis();
			xAxis.setData(xList);
			barOrLine.setSeries(seriesList);
			barOrLine.setxAxis(xAxis);
			YAxis yAxisL = new YAxis();
			YAxis yAxisR = new YAxis();
			List<YAxis> yAxisList = new ArrayList<>();
			yAxisList.add(yAxisL);
			yAxisList.add(yAxisR);
			barOrLine.setyAxis(yAxisList);
			reportDataDTO.setBarOrLine(barOrLine);
			break;
		case 2:
			reportSql = reportSql.replace(group, " ");

			// 3.拼接饼图
			List<SeriesData> pieList = new ArrayList<>();
			SeriesData seriesDataPie = null;
			// 暂时去掉排序
			// if(!flag) {
			orderSql = "";
			// }
			// 最后替换下没用的sql替换符
			reportSql = reportSql.replaceAll(where, whereSqlSb.toString()).replaceAll(page, pageSql).replaceAll(order,
					orderSql);
			reportSqlFinal = reportSqlFinal.replaceAll("#reportSql#", reportSql);
			// reportSqlFinal += whereSqlSb;
			log.info("饼图sql：{}", reportSqlFinal);
			// 查询图表数据
			reportSqlMap = new HashMap<>();
			reportSqlMap.put("sql", reportSqlFinal);
			tableMap = mdmApi.listData(databaseId, reportSqlMap);
			if (userId != null) {
				for (Map<String, Object> map : tableMap) {
					for (Map.Entry<String, Object> m : map.entrySet()) {
						if (usedKpiCodeList.contains(m.getKey().toUpperCase())) {
							seriesDataPie = new SeriesData();
							seriesDataPie.setName(codeNameMap.get(m.getKey().toUpperCase()));
							if (m.getValue() != null) {
								seriesDataPie.setValue(m.getValue().toString().replace(",", ""));
							}
							pieList.add(seriesDataPie);
						}
					}
				}
			} else {
				for (Map<String, Object> map : tableMap) {
					for (Map.Entry<String, Object> m : map.entrySet()) {
						seriesDataPie = new SeriesData();
						seriesDataPie.setName(codeNameMap.get(m.getKey().toUpperCase()));
						if (m.getValue() != null) {
							seriesDataPie.setValue(m.getValue().toString().replace(",", ""));
						}
						pieList.add(seriesDataPie);
					}
				}
			}

			pie.setSeries(pieList);
			reportDataDTO.setPie(pie);
			break;
		default:
			break;
		}
		R<ReportDataDTO> r = new R<>(reportDataDTO);
		return r;
	}

	@Override
	public R<List<ReportDTO>> listReportByMenu(String menuCode) {
		return new R<List<ReportDTO>>(reportMapper.listReportByMenu(menuCode));
	}

	/**
	 * 查询报表中KPI列表
	 */
	@Override
	public R<List<ReportKPIDTO>> listKpiByReportId(String reportId) {
		return new R<List<ReportKPIDTO>>(reportMapper.listKpiByReportId(reportId));
	}

	@Override
	public R<List<ReportKPIDTO>> listKpiByReportAndUser(String reportId, String userId) {
		return new R<List<ReportKPIDTO>>(reportMapper.listKpiByReportAndUser(reportId, userId));
	}

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

	/**
	 * 下载报表数据文件--修改为下载csv文件导出
	 */
	// @Override
	// public String downloadReportFile(ReportViewDataParam reportViewDataParam,
	// String userId) throws Exception {
	// List<SaveRportKpiDTO> kpiList =
	// reportMapper.listSaveRportKpi(reportViewDataParam.getReportId());
	// ReportDTO report =
	// reportMapper.getReportById(reportViewDataParam.getReportId());
	// // 报表名称做sheet页名称
	// String sheetName = report.getReportName();
	// // 获取所有标题
	// List<String> list = new ArrayList<>();
	// // 字段ID，标题
	// List<String> listCode = new ArrayList<>();
	// for (SaveRportKpiDTO kpi : kpiList) {
	// list.add(kpi.getRname());
	// listCode.add(kpi.getKpiCode().toLowerCase());
	// }
	//
	// // 第一步创建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;
	//
	// XSSFRow row = sheet.createRow(0);
	// for (int i = 0; i < list.size(); i++) {
	// cell = row.createCell(i);
	// cell.setCellValue(list.get(i));
	// cell.setCellStyle(style);
	// }
	// List<Map<String, Object>> listData =
	// getDownloadReportData(reportViewDataParam, userId, kpiList);
	// // 第四步创建单元格
	// 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(listCode.get(i)) == null
	// || listData.get(j).get(listCode.get(i)).equals("null")) {
	// cell.setCellValue("");
	// } else {
	// cell.setCellValue((String) "" + listData.get(j).get(listCode.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;
	// }
	/**
	 * 下载报表数据文件--修改为下载csv文件导出
	 */
	@Override
	public String downloadReportFile(ReportViewDataParam reportViewDataParam, String userId) throws Exception {
		List<SaveRportKpiDTO> kpiList = reportMapper.listSaveRportKpi(reportViewDataParam.getReportId());
		// 获取所有标题
		LinkedHashMap<String, String> map = new LinkedHashMap<>();
		// 字段ID，标题
		for (SaveRportKpiDTO kpi : kpiList) {
			map.put(kpi.getKpiCode().toLowerCase(), kpi.getRname());
		}
		// 获取报表数据
		List<Map<String, Object>> listData = getDownloadReportData(reportViewDataParam, userId, kpiList);
		String fileName = System.currentTimeMillis() + "";
		File file = CSVUtils.createCSVFile(listData, map, filePath + File.separator + "file" + File.separator,
				fileName);
		return file.getPath();
	}
	// ----------------------------添加图表设置---------------------------------------

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

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

	@Override
	public R<List<MdmInfoColumn>> listColumnByTableId(String tableId) {
		return new R<>(mdmApi.listColumnByTableId(tableId));
	}

	@Override
	public R<List<ChartTypeDTO>> listChartType() {
		return new R<>(reportMapper.listChartType());
	}

	@Override
	public R<String> getDML(String tableId, String columnIds) {
		StringBuffer sb = new StringBuffer();
		List<KpiListColumnDTO> columns = null;
		sb.append("SELECT ");
		if (columnIds != null && !"".equals(columnIds)) {
			columns = mdmApi.listColumnByIds(Arrays.asList(columnIds.split(",")));
		}
		// 获取字段信息
		MdmCreateTableDTO tableDto = mdmApi.getTableInfoById(tableId).getData();
		if (columns != null && columns.size() != 0) {
			for (KpiListColumnDTO column : columns) {
				sb.append(column.getColumnName()).append(",");
			}
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(" FROM " + tableDto.getTableName());
		R<String> r = new R<>();
		r.setData(sb.toString());
		return r;
	}

	@Override
	public R<String> loadChartFile(MultipartFile multipartFile, String userId) throws Exception {
		String fileName = multipartFile.getOriginalFilename();
		// 获取文件后缀
		StringBuffer fileSb = new StringBuffer(fileName.substring(fileName.indexOf("."), fileName.length()));
		// 重命名上传文件
		fileSb.insert(fileSb.indexOf("."), System.currentTimeMillis());
		FileTool.uploadFile(multipartFile.getBytes(), importPath + userId + File.separator, fileSb.toString());
		R<String> r = new R<>();
		r.setMsg("上传成功");
		r.setData(fileUrl + userId + File.separator + fileSb.toString());
		return r;
	}

	@Override
	public RichTextFileR uploadRichTextFile(MultipartFile multipartFile, String userId) throws Exception {
		String fileName = multipartFile.getOriginalFilename();
		// 获取文件后缀
		StringBuffer fileSb = new StringBuffer(fileName.substring(fileName.indexOf("."), fileName.length()));
		// 重命名上传文件
		fileSb.insert(fileSb.indexOf("."), System.currentTimeMillis());
		FileTool.uploadFile(multipartFile.getBytes(), importPath + userId + File.separator, fileSb.toString());
		RichTextFileR r = new RichTextFileR();
		r.setError(0);
		r.setUrl(fileUrl + userId + File.separator + fileSb.toString());
		return r;
	}

}
