package com.ozs.rpm.service.impl;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ozs.rpm.dto.KpiListColumnDTO;
import com.ozs.rpm.dto.ReportDTO;
import com.ozs.rpm.dto.ReportSqlDTO;
import com.ozs.rpm.dto.kpim.ComputedTypeInfoDTO;
import com.ozs.rpm.dto.kpim.DefinedTypeInfoDTO;
import com.ozs.rpm.dto.kpim.KpiConfigDTO;
import com.ozs.rpm.dto.kpim.RelColumnConfigDTO;
import com.ozs.rpm.dto.kpim.RuleConfigDTO;
import com.ozs.rpm.dto.save.RelReportKpiDrillDTO;
import com.ozs.rpm.dto.save.ReportKpiDrillDown;
import com.ozs.rpm.dto.save.RpmInfoReportChartDTO;
import com.ozs.rpm.dto.save.SaveRportKpiDTO;
import com.ozs.rpm.dto.view.GroupKpiViewDTO;
import com.ozs.rpm.dto.view.ReportViewDTO;
import com.ozs.rpm.dto.view.ReportViewPieDTO;
import com.ozs.rpm.dto.view.ReportViewQueryDTO;
import com.ozs.rpm.mapper.ReportAutoViewMapper;
import com.ozs.rpm.mapper.ReportMapper;
import com.ozs.rpm.service.ReportAutoViewService;
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;

@Service
public class ReportAutoViewServiceImpl implements ReportAutoViewService {
	@Autowired
	private ReportMapper reportMapper;
	@Autowired
	private ReportAutoViewMapper reportAutoViewMapper;
	@Autowired
	private KpimApi kpimApi;
	@Autowired
	private MdmApi mdmApi;
	@Autowired
	private AccountApi accountApi;

	@Override
	public R<ReportViewDTO> reportView(String reportId, String userId) throws Exception {
		// 是否有聚合
		List<String> queryGroupByCodes = new ArrayList<>();
		// 可操作聚合kpi
		List<SaveRportKpiDTO> operableGroupKpis = new ArrayList<>();

		// 默认聚合kpi
		List<SaveRportKpiDTO> defaultGroupKpis = new ArrayList<>();
		// 判断用户是管理员用户，展示报表的所有kpi权限
		if (accountApi.getUserType(userId) == 2) {
			userId = null;
		}
		R<ReportViewDTO> r = new R<ReportViewDTO>();
		// 获取报表sql对象
		ReportSqlDTO sql = reportMapper.getReportSql(reportId, 0);
		// 查询多选条件下拉
		List<Object> queryOptions = null;
		// 分组kpi参数列表
		List<Object> groupKpiList = null;
		List<String> kpiIds = new ArrayList<>();
		ReportViewDTO view = reportAutoViewMapper.getReportViewById(reportId);
		// KPI 信息获取
		List<SaveRportKpiDTO> kpis = reportAutoViewMapper.listRportKpiByUser(reportId, userId);
		// 若kpi列表为空
		if (kpis == null || kpis.size() == 0) {
			r.setCode("-1");
			r.setMsg("报表kpi权限为空");
			r.setData(null);
			return r;
		}
		//
		List<ReportKpiDrillDown> drillDowns = null;
		List<RelReportKpiDrillDTO> kpiDrills = null;
		if (view.getIfDrillDown() == 1) {
			drillDowns = new ArrayList<>();
			kpiDrills = reportMapper.listRelReportKpiDrill(reportId);
		}
		for (SaveRportKpiDTO kpi : kpis) {
			kpiIds.add(kpi.getKpiId());
			if (kpi.getIfQueryGroupBy() != null && kpi.getIfQueryGroupBy() == 1) {
				queryGroupByCodes.add(kpi.getKpiCode());
			}
			// 默认排序
			if (kpi.getIfQueryDefaultSort() != null && kpi.getIfQueryDefaultSort() == 1) {
				view.setQueryDefaultSortKpi(kpi.getKpiId());
				view.setQueryDefaultSortKpiCode(kpi.getKpiCode());
				view.setQueryDefaultOrder(kpi.getQueryDefaultOrder());
			}
			// 下钻kpi信息
			if (view.getIfDrillDown() == 1 && kpi.getIfRelated() != null && kpi.getIfRelated() == 1) {
				ReportKpiDrillDown drillDown = new ReportKpiDrillDown();
				drillDown.setKpiCode(kpi.getKpiCode());
				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.getIfQueryGroupBy() != null && kpi.getIfQueryGroupBy() == 1) {
				defaultGroupKpis.add(kpi);
			}
			/**
			 * 可操作性聚合kpi
			 */
			if (kpi.getIfOperableGroupBy() != null && kpi.getIfOperableGroupBy() == 1) {
				operableGroupKpis.add(kpi);
			}
		}
		view.setDrillDowns(drillDowns);
		// Map<String, String> kpiMap = getKpiMap(kpiIds);
		// 柱折混合图信息列表获取
		List<RpmInfoReportChartDTO> reportChartDtos = reportAutoViewMapper.listBarAndLineChartByUser(reportId, userId);
		// 饼状图kpi
		List<ReportViewPieDTO> pieViews = reportAutoViewMapper.listViewPieKpiByUser(reportId, userId);
		List<ReportViewQueryDTO> querys = reportAutoViewMapper.listQueryByUser(reportId, userId);

		for (ReportViewQueryDTO que : querys) {
			// 如果是精准多选，则需要返回下拉选项数据
			if (que.getIfQuerySearch() == 1 && que.getSearchTypeId() == 3) {
				queryOptions = getQueryOptions(que.getKpiCode(), sql.getSqlReport(), kpis, sql.getDatabaseId(),
						queryGroupByCodes);
				// 数据去空
				queryOptions.removeAll(Collections.singleton(null));
				que.setQueryOptions(queryOptions);
			}
		}
		GroupKpiViewDTO group = null;
		// 如果是分组展示，需要获取分组展示参数列表
		if (view.getDisplaySystem() == 1 && view.getShowType() == 2) {
			for (SaveRportKpiDTO kpi : kpis) {
				if (kpi.getIfGroupShow() != null && kpi.getIfGroupShow() == 1) {
					group = new GroupKpiViewDTO();
					group.setKpiName(kpi.getRname());
					group.setKpiId(kpi.getKpiId());
					group.setKpiCode(kpi.getKpiCode());
					group.setIfNumber(kpi.getIfNumber());
					break;
				}
			}
			// 如果存在分组kpi字段
			if (group != null) {
				groupKpiList = getQueryOptions(group.getKpiCode(), sql.getSqlReport(), kpis, sql.getDatabaseId(),
						queryGroupByCodes);
				// 数据去空
				groupKpiList.removeAll(Collections.singleton(null));
				group.setGroupKpiList(groupKpiList);
			}
		}
		/**
		 * 可操作性聚合kpi
		 */
		if (operableGroupKpis != null && operableGroupKpis.size() != 0) {
			view.setOperableGroupKpis(operableGroupKpis);
		}
		/**
		 * 默认聚合
		 */
		if (defaultGroupKpis != null && defaultGroupKpis.size() != 0) {
			view.setDefaultGroupKpis(defaultGroupKpis);
		}
		view.setGroupKpi(group);
		view.setKpis(kpis);
		view.setPieViews(pieViews);
		view.setBarAndLineCharts(reportChartDtos);
		view.setQuerys(querys);
		r.setData(view);
		return r;
	}

	/**
	 * 获取KPI查询别名，tableName.columnName
	 * 
	 * @param kpiIds
	 * @return
	 */
	@SuppressWarnings("unused")
	private Map<String, String> getKpiMap(List<String> kpiIds) {
		// 所有相关字段集合
		Set<String> columnIdSets = new HashSet<>();
		List<String> columnIds = null;
		Map<String, String> kpiRelColmnuMap = new HashMap<>();
		// 报表所有KPI详细信息
		List<KpiConfigDTO> kpiConfigList = kpimApi.listKpiConfigByKpiIds(kpiIds);
		for (int i = 0; i < kpiConfigList.size(); i++) {
			// kpi详细信息
			KpiConfigDTO kpi = kpiConfigList.get(i);
			StringBuffer colmnuValue = new StringBuffer();
			/**
			 * 计算类型
			 */
			if (kpi.getType() == 2) {
				// 获取计算类型相关内容
				ComputedTypeInfoDTO computedTypeInfo = kpi.getComputedTypeInfo();
				// KPI字段之间关系
				List<RelColumnConfigDTO> relColumnList = computedTypeInfo.getRelOptions();
				if (relColumnList != null) {
					for (int j = 0; j < relColumnList.size(); j++) {
						// 添加相关表和字段
						columnIdSets.add(relColumnList.get(j).getColumnId());
						columnIdSets.add(relColumnList.get(j).getRelColumnId());
					}
				}
				// KPI计算公式，按数据加载，如(a /b)
				List<RuleConfigDTO> ruleConfigList = computedTypeInfo.getRuleOptions();
				if (ruleConfigList != null) {
					String columnOrConstant = "";
					String isConstant = "";
					for (int j = 0; j < ruleConfigList.size(); j++) {
						columnOrConstant = "";
						isConstant = ruleConfigList.get(j).getIsConstant();
						if (StringUtil.isNotEmpty(isConstant) && isConstant.equals("true")) { // 是常量
							columnOrConstant = ruleConfigList.get(j).getConstant();
						} else { // 是字段
							columnOrConstant = ruleConfigList.get(j).getColumnId();
							// 添加相关表和字段
							columnIdSets.add(ruleConfigList.get(j).getColumnId());
						}
						colmnuValue.append(ruleConfigList.get(j).getLeftSign()).append(columnOrConstant)
								.append(ruleConfigList.get(j).getRightSign());
					}
				}
				kpiRelColmnuMap.put(kpi.getId(), colmnuValue.toString());
			} else if (kpi.getType() == 4) {
				/**
				 * 函数类型
				 */
				List<DefinedTypeInfoDTO> functionKpiColumns = kpi.getFunctionKpiColumns();
				for (DefinedTypeInfoDTO c : functionKpiColumns) {
					columnIdSets.add(c.getColumnId());
					kpi.setFunctionValue(kpi.getFunctionValue().replaceAll("C" + (functionKpiColumns.indexOf(c) + 1),
							c.getColumnId()));
				}
				kpiRelColmnuMap.put(kpi.getId(), kpi.getFunctionValue());
			} else {
				/**
				 * 定义或输入
				 */
				DefinedTypeInfoDTO definedTypeInfo = kpi.getDefinedTypeInfo();
				// 添加相关表和字段
				columnIdSets.add(definedTypeInfo.getColumnId());
				kpiRelColmnuMap.put(kpi.getId(), definedTypeInfo.getColumnId());
			}
		}
		/**
		 * 涉及到的字段
		 */
		columnIds = new ArrayList<>(columnIdSets);
		/**
		 * KPI和字段的统一映射map
		 */
		Map<String, String> columnMap = new HashMap<>();
		List<KpiListColumnDTO> columnlist = mdmApi.listColumnByIds(columnIds);
		for (int i = 0; i < columnlist.size(); i++) {
			columnMap.put(columnlist.get(i).getId(),
					columnlist.get(i).getTableName() + "." + columnlist.get(i).getColumnName());
			columnMap.put(columnlist.get(i).getTableId(), columnlist.get(i).getTableName());
		}
		for (String id : kpiIds) {
			for (int i = 0; i < columnlist.size(); i++) {
				kpiRelColmnuMap.put(id, kpiRelColmnuMap.get(id).replaceAll(columnlist.get(i).getId(),
						columnMap.get(columnlist.get(i).getId())));
			}
		}
		return kpiRelColmnuMap;
	}

	/**
	 * 获取查询多选条件下拉数据
	 * 
	 * @param kpiId
	 * @return
	 */
	private List<Object> getQueryOptions(String kpiCode, String sql, List<SaveRportKpiDTO> kpis, String databaseId,
			List<String> queryGroupByCodes) {
		List<Object> queryOptions = new ArrayList<Object>();
		// 去空格，方便处理sql
		sql = sql.replaceAll(" ", "");
		List<String> columns = new ArrayList<>();
		for (SaveRportKpiDTO kpi : kpis) {
			String column = sql.substring(sql.indexOf("select") + 6, sql.indexOf(kpi.getKpiCode()));
			columns.add(column + " " + 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);
			}
		}
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		for (String c : columns) {
			if (c.contains(kpiCode)) {
				sb.append(c).append(",");
			}
			// 如果报表存在聚合分组kpi，查询必须存在聚合kpi避免sql查询报错
			if (queryGroupByCodes != null && queryGroupByCodes.size() != 0) {
				for (String code : queryGroupByCodes) {
					if (c.contains(code)) {
						sb.append(c).append(",");
					}
				}
			}
		}
		sb.deleteCharAt(sb.length() - 1);
		// 去除sql #where
		sql = sql.replaceAll("#where", "");
		// sql 的 from 和where 前后加空格，保证sql正常执行
		sql = sql.replaceAll("where", " where ");
		sql = sql.replaceAll("from", " from ");
		sb.append(sql.substring(sql.indexOf(" from "), sql.lastIndexOf(" where ")));
		if (queryGroupByCodes != null && queryGroupByCodes.size() != 0) {
			sb.append(" group by ");
			for (String c : queryGroupByCodes) {
				sb.append(c).append(",");
			}
			sb.deleteCharAt(sb.length() - 1);
		}
		// 查询图表数据
		Map<String, String> reportSqlMap = new HashMap<>();
		reportSqlMap.put("sql", sb.toString());
		List<Map<String, Object>> data = mdmApi.listData(databaseId, reportSqlMap);

		for (Map<String, Object> m : data) {
			// 结果集中不包含key时，key转为小写获取数据
			if (!m.containsKey(kpiCode)) {
				kpiCode = kpiCode.toLowerCase();
			}
			// if (m.get(kpiCode) != null && !"".equals(m.get(kpiCode))) {
			if (m.get(kpiCode) != null) {
				if (!queryOptions.contains(m.get(kpiCode).toString())) {
					queryOptions.add(m.get(kpiCode).toString());

				}
			}
		}

		return queryOptions;
	}

	@Override
	public R<List<ReportDTO>> listReportByUserAndMenu(String menuCode, String userId) throws Exception {

		return new R<List<ReportDTO>>(reportAutoViewMapper.listReportByUserAndMenu(menuCode, userId));
	}

}
