package com.artfess.bpm.persistence.manager.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import com.artfess.base.manager.CommonManager;
import com.artfess.base.util.*;
import com.artfess.bpm.chart.IChart;
import com.artfess.bpm.chart.impl.FlowStatusChart;
import org.springframework.stereotype.Service;

import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.artfess.base.constants.DataSourceConsts;
import com.artfess.base.constants.SQLConst;
import com.artfess.base.datasource.DatabaseContext;
import com.artfess.base.feign.UCFeignService;
import com.artfess.base.manager.impl.BaseManagerImpl;
import com.artfess.base.model.CommonResult;
import com.artfess.base.query.PageList;
import com.artfess.base.query.QueryField;
import com.artfess.base.query.QueryFilter;
import com.artfess.bpm.persistence.dao.BpmReportActDao;
import com.artfess.bpm.persistence.manager.BpmReportActManager;
import com.artfess.bpm.persistence.manager.BpmReportListManager;
import com.artfess.bpm.persistence.model.BpmReportAct;
import com.artfess.bpm.persistence.model.BpmReportActVo;
import com.artfess.bpm.persistence.model.BpmReportList;
import com.artfess.bpm.persistence.model.vo.FlowOrgCountVo;
import com.artfess.bpm.persistence.model.vo.FlowUserCountVo;
import com.artfess.uc.api.impl.util.ContextUtil;
import com.artfess.uc.api.model.IUser;
import org.springframework.transaction.annotation.Transactional;

@Service("bpmReportActManager")
public class BpmReportActManagerImpl extends BaseManagerImpl<BpmReportActDao, BpmReportAct> implements BpmReportActManager{

	@Resource
	UCFeignService ucService;
	@Resource
	BpmReportListManager bpmReportListManager;
	@Resource
	BpmReportActManager bpmReportActManager;
	@Resource
	DatabaseContext databaseContext;
	@Resource
	CommonManager commonManager;

	@Override
    @Transactional
	public CommonResult<String> saveAct(BpmReportActVo bpmReportAct) {
		String resultMsg="";
		String actId=bpmReportAct.getId();
		String reportId=bpmReportAct.getReportId();
		try {
			if(StringUtil.isEmpty(actId)){
				bpmReportAct.setId(UniqueIdUtil.getSuid());
				if(StringUtil.isEmpty(reportId)) {
					if(StringUtil.isEmpty(bpmReportAct.getName())){
						return new CommonResult<>(false, "标题不能为空！");
					}
					BpmReportList reportList = new BpmReportList();
					String id = UniqueIdUtil.getSuid();
					reportList.setId(id);
					reportList.setName(bpmReportAct.getName());
					IUser user = ContextUtil.getCurrentUser();
					String userId = user.getUserId();
					String userName = user.getUsername();
					reportList.setCreateBy(userId);
					reportList.setCreateName(userName);
					ObjectNode org=ucService.getMainGroup(userId);
					if(BeanUtils.isNotEmpty(org)){
						String orgId=org.get("id").asText();
						String orgName=org.get("name").asText();
						reportList.setCreateOrgId(orgId);
						reportList.setOrgName(orgName);
					}
					bpmReportListManager.create(reportList);
					bpmReportAct.setReportId(id);
				}
				bpmReportActManager.create(bpmReportAct);
				resultMsg="添加报表成功";
			}else{
				bpmReportActManager.update(bpmReportAct);
				resultMsg="更新报表成功";
			}
			if(StringUtil.isNotEmpty(reportId)){
				BpmReportList reportList = bpmReportListManager.get(reportId);
				if(BeanUtils.isNotEmpty(reportList) && StringUtil.isNotEmpty(bpmReportAct.getName()) &&
						!bpmReportAct.getName().equals(reportList.getName())){
					reportList.setName(bpmReportAct.getName());
					bpmReportListManager.update(reportList);
				}
			}
			return new CommonResult<>(true, resultMsg, bpmReportAct.getReportId());
		} catch (Exception e) {
			resultMsg="对报表操作失败";
			return new CommonResult<>(false, resultMsg);
		}
	}

	@Override
	public CommonResult<JsonNode> getEchartsData(String reportId) throws Exception{
		ArrayNode array = JsonUtil.getMapper().createArrayNode();
		BpmReportList reportList = bpmReportListManager.get(reportId);
		if(BeanUtils.isNotEmpty(reportList)){
			List<BpmReportAct> acts = baseMapper.queryList(reportId);
			for (BpmReportAct act : acts) {
				ObjectNode node = analysisData(act);
				array.add(node);
			}
		}else{
			return new CommonResult<>(false, "获取失败！id为【"+reportId+"】的统计不存在！");
		}
		return new CommonResult<>(true, "获取成功！", array);
	}

	@Override
	public CommonResult<JsonNode> getSingleEchartsData(String id) throws Exception{
		ObjectNode node;
		BpmReportAct act = get(id);
		if (BeanUtils.isNotEmpty(act)){
			node = analysisData(act);
		}else{
			return new CommonResult<>(false, "获取失败！id为【"+id+"】的统计不存在！");
		}
		return new CommonResult<>(true, "获取成功！", node);
	}
	
	private ObjectNode analysisData(BpmReportAct act) throws Exception{
		ObjectNode node = JsonUtil.getMapper().createObjectNode();
		node.put("name", act.getReportName());
		ObjectNode prop =  (ObjectNode) JsonUtil.toJsonNode(act.getPorp());
		//流程范围类型：“1”流程；“2”流程分类
		String flowType = JsonUtil.getString(prop,"flowType","1");
		//部门/人员范围类型：“1”部门；“2”人员
		String orgType = JsonUtil.getString(prop,"orgType","1");
		//统计维度 org:组织维度，flow:流程或流程分类维度
		String dimension = JsonUtil.getString(prop, "dimension", "org");
		//流程或流程分类ID
		String ids = JsonUtil.getString(prop,"id");
		//流程或流程分类名称
		String names = JsonUtil.getString(prop, "name");

		//HaspMap为无序
		Map<String,String> flowMap = new LinkedHashMap<>();
		if(StringUtil.isNotEmpty(ids)){
			String[] idArray = ids.split(",");
			String[] idNameArray = names.split(",");
			for (int i = 0; i < idArray.length; i++) {
				flowMap.put(idArray[i], idNameArray[i]);
			}
		}
		//部门ID
		String orgIds = JsonUtil.getString(prop, "orgIds");
		//部门名称
		String orgNames = JsonUtil.getString(prop, "orgNames");

		//HashMap为无序
		Map<String,String> orgMap = new LinkedHashMap<String, String>();
		if(StringUtil.isNotEmpty(orgIds)){
			String[] orgIdArray = orgIds.split(",");
			String[] orgNameArray = orgNames.split(",");
			for (int i = 0; i < orgIdArray.length; i++) {
				orgMap.put(orgIdArray[i], orgNameArray[i]);
			}
		}
		//统计类型 year：年度，quarter：季度，monthly：月度，custom：自定义时间段
		String calcuCycle = JsonUtil.getString(prop,"calcuCycle");
		//开始时间
		String calcuStart = JsonUtil.getString(prop,"calcuStart");
		String startTime = getDateTime(calcuCycle, calcuStart, true);
		//结束时间
		String calcuEnd = JsonUtil.getString(prop,"calcuEnd");
		String endTime = getDateTime(calcuCycle, calcuEnd, false);

		//统计类型 start_throughput：启动流程吞吐量，handle_throughput：办件吞吐量，handle_efficiency：办件效率
		String params = act.getParams();
		IChart chart = (IChart) AppUtil.getBean(params);
		ObjectNode rightContentNode = (ObjectNode) JsonUtil.toJsonNode(act.getRightContent());

		ObjectNode analysisNode = getAnalysisNode(chart, flowType, dimension, ids, names, orgIds, orgNames, startTime, endTime, flowMap, orgMap,orgType);
		String ptypeDesc = chart.getDesc();

		String preDesc = "org".equals(dimension)?"部门/人员":"流程/流程分类";
		String calcuCycleDesc = "year".equals(calcuCycle)?"年度":"quarter".equals(calcuCycle)?"季度":"monthly".equals(calcuCycle)?"月度":"自定义时间段";
		String calcuDesc = "（"+calcuStart+"至"+calcuEnd+"）";
		if("quarter".equals(calcuCycle)) {
			calcuDesc="（"+calcuStart+"季度"+"至"+calcuEnd+"季度"+"）";
		}

		analysisNode.put("name", act.getReportName());
		analysisNode.put("histogram", JsonUtil.getBoolean(rightContentNode,"histogram"));
		analysisNode.put("line", JsonUtil.getBoolean(rightContentNode, "line"));
		analysisNode.put("pie", JsonUtil.getBoolean(rightContentNode, "pie"));
		analysisNode.put("dataViews", JsonUtil.getBoolean(rightContentNode, "dataViews"));
		analysisNode.put("id", act.getId());
		analysisNode.put("subtext", "按"+preDesc+"维度"+calcuCycleDesc+calcuDesc+ptypeDesc+"统计图表");
		return analysisNode;
	}
	
	private ObjectNode getAnalysisNode(IChart chart,String flowType,String dimension,String ids,String names, String orgIds, String orgNames, String startTime, String endTime,Map<String,String> flowMap,Map<String,String> orgMap,String orgType) throws Exception{
		ObjectNode startNode = JsonUtil.getMapper().createObjectNode();
		String unionSql = chart.getQuerySql(flowType, dimension, ids, orgIds, startTime, endTime, orgType, getDbType());
		List<String> legend = chart.getLegend();
		//输出拼接的sql语句
//		System.out.println("输出拼接的sql语句:"+unionSql);
		List<Map<String, Object>> selectList = querySql(unionSql);

		ArrayNode series = chart.getSeries(legend, selectList, "org".equals(dimension)?orgMap:flowMap);
		startNode.set("legend", JsonUtil.toJsonNode(legend));
		startNode.set("series", series);
		if (!(chart instanceof FlowStatusChart)){
			List<String> xAxis = Arrays.asList("org".equals(dimension)?orgNames.split(","):names.split(","));
			startNode.set("xAxis", JsonUtil.toJsonNode(xAxis));
		}

		return startNode;
	}

	private String getDateTime(String calcuCycle,String timeStr,boolean isStart){
		String newTimeStr = timeStr;
		switch (calcuCycle) {
		case "year":
			newTimeStr = timeStr + "-01-01 00:00:00";
			if(!isStart){
				newTimeStr = timeStr+"-12-31 23:59:59";
			}
			break;
		case "quarter":
			String[] timeArray = timeStr.split("-");
			String mothDate = getQuarterMothDate(timeArray[1], isStart);
			newTimeStr = timeArray[0]+ "-"+mothDate+" 00:00:00";
			if(isStart){
				newTimeStr = timeArray[0]+ "-" +mothDate+" 23:59:59";
			}
			break;
		case "monthly":
			String[] monthlyArray = timeStr.split("-");
			String monthDayStr = getMonthlyMothDate(monthlyArray[0],monthlyArray[1],isStart);
			
			String laster = isStart?" 00:00:00":" 23:59:59";
			newTimeStr = monthlyArray[0]+ "-" +monthDayStr+laster;
			break;
		}
		return newTimeStr;
	}
	
	private String getQuarterMothDate(String quarter,boolean isStart){
		int quart = Integer.valueOf(quarter);
		int moth = quart*3;
		if(isStart){
			moth -= 2; 
		}
		String mothStr = String.valueOf(moth);
		if(moth<10){
			mothStr =  "0"+moth;
		}
		if(!isStart){
			switch (quart) {
			case 1:
			case 4:
				mothStr = mothStr +"-31";
				break;
			case 2:
			case 3:
				mothStr = mothStr +"-30";
				break;
			default:
				break;
			}
		}else{
			mothStr = mothStr +"-01";
		}
		return mothStr;
	}
	
	private String getMonthlyMothDate(String yearly,String monthly,boolean isStart) {
		int year=Integer.valueOf(yearly);
		int month=Integer.valueOf(monthly);
		if(month<10){
			monthly =  "0"+monthly;
		}
		if(!isStart){
			switch (month) {
			case 1:
			case 3:
			case 5:
			case 7:
			case 8:
			case 10:
			case 12:
				monthly = monthly +"-31";
				break;
			case 4:
			case 6:
			case 9:
			case 11:
				monthly = monthly +"-30";
				break;
			case 2:
				if((year%4==0 && year%100!=0) || year%400==0 ){//判断是闰年还是平年
					monthly = monthly +"-29";
				}else {
					monthly = monthly +"-28";
				}
				break;
			default:
				break;
			}
		}else{
			monthly = monthly +"-01";
		}
		return monthly;
		
	}

	private String getDbType() {
		//达梦语法和oracle一致
		String dbType = databaseContext.getDbTypeByAlias(DataSourceConsts.LOCAL_DATASOURCE);
		return SQLConst.DB_DM.equals(dbType)?SQLConst.DB_ORACLE:dbType;
		//return databaseContext.getDbTypeByAlias(DataSourceConsts.LOCAL_DATASOURCE);
	}
	
	private List<Map<String, Object>> querySql(String sql){
		return commonManager.query(sql);
	}

	@Override
	public PageList<FlowOrgCountVo> flowOrgCountList(QueryFilter queryFilter) {
		List<Map<String, Object>> selectList = this.getFlowOrgSelectList(queryFilter);
		return parseToOrgPageList(selectList);
	}
	
	public List<Map<String, Object>> getFlowOrgSelectList(QueryFilter queryFilter){
		String dbType =  getDbType();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.PROC_DEF_KEY_ as procDefKey,a.PROC_DEF_NAME_ as procDefName,COUNT(1) AS instances,");
		if(SQLConst.DB_ORACLE.equals(dbType)){
			sql.append("ROUND(SUM((TO_DATE (TO_CHAR ((CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE (SELECT SYSDATE FROM dual) END ), 'YYYY-MM-DD HH24-MI-SS' ),'YYYY-MM-DD HH24-MI-SS') - TO_DATE (TO_CHAR (a.CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24),1) AS hourLong,");
		}else if(SQLConst.DB_POSTGRESQL.equals(dbType)) {
			sql.append("ROUND(SUM((TO_DATE (TO_CHAR ((CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE now() END ), 'YYYY-MM-DD HH24-MI-SS' ),'YYYY-MM-DD HH24-MI-SS') - TO_DATE (TO_CHAR (a.CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24),1) AS hourLong,");
		}else if(SQLConst.DB_H2.equals(dbType)){
			sql.append("SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))/60,1)) AS hourLong,");
		}else{
			sql.append("SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,IF (a.END_TIME_ IS NOT NULL,a.END_TIME_,now()))/60,1)) AS hourLong,");
		}
		sql.append("(SELECT COUNT(1) FROM bpm_pro_inst b WHERE b.STATUS_ NOT IN('end','manualend') and b.PROC_DEF_KEY_=a.PROC_DEF_KEY_"+getFlowOrgWhereSql(queryFilter, "b")+") as incomplete");
		sql.append(" FROM bpm_pro_inst a WHERE 1=1 ");
		sql.append(getFlowOrgWhereSql(queryFilter, "a"));
		sql.append(" GROUP BY a.PROC_DEF_KEY_,a.PROC_DEF_NAME_");
		return querySql(sql.toString());
	}
	
	private String getFlowOrgWhereSql(QueryFilter queryFilter,String pre){
		StringBuilder whereSql = new StringBuilder();
		List<QueryField> querys = queryFilter.getQuerys();
		if(BeanUtils.isNotEmpty(queryFilter) && BeanUtils.isNotEmpty(querys)){
			for (QueryField queryField : querys) {
				if("createTime".equals(queryField.getProperty()) && BeanUtils.isNotEmpty(queryField.getValue())){
					List<String> times = (List<String>) queryField.getValue();
					whereSql.append(" AND "+pre+".CREATE_TIME_ >='"+times.get(0)+"' ");
					whereSql.append(" AND "+pre+".CREATE_TIME_ <='"+times.get(1)+"' ");
				}else if("createOrgId".equals(queryField.getProperty()) && BeanUtils.isNotEmpty(queryField.getValue())){
					Map<String,String> map = new HashMap<String, String>();
					String[] orgIds = queryField.getValue().toString().split(",");
					for (String orgId : orgIds) {
						map.put(orgId, orgId);
					}
					Set<String> allOrgIds = new HashSet<String>();
					//获取子部门
					Map<String, Set<String>> mapSet = ucService.getChildrenIds(map);
					for (String key : mapSet.keySet()) { 
						allOrgIds.addAll(mapSet.get(key));
					}
					String orgSql = StringUtil.convertListToSingleQuotesString(allOrgIds);
					whereSql.append(" AND "+pre+".CREATE_ORG_ID_ in("+orgSql+")");
				}
			}
			return whereSql.toString();
		}
		return "";
	}
	
	/**
	 * 转orgPageList
	 * @param selectList
	 * @return
	 */
	private PageList<FlowOrgCountVo> parseToOrgPageList(List<Map<String, Object>> selectList){
		PageList<FlowOrgCountVo> pageList = new PageList<>();
		pageList.setPage(1);
		pageList.setPageSize(1);
		pageList.setTotal(0);
		pageList.setRows(new ArrayList<FlowOrgCountVo>());
		try {
			if(BeanUtils.isNotEmpty(selectList)){
				List<FlowOrgCountVo> rows = new ArrayList<FlowOrgCountVo>();
				if(selectList.get(0).containsKey("PROCDEFKEY")){
					for (Map<String, Object> map : selectList) {
						FlowOrgCountVo vo = new FlowOrgCountVo();
						vo.setProcDefKey(map.get("PROCDEFKEY").toString());
						vo.setProcDefName(map.get("PROCDEFNAME").toString());
						vo.setHourLong(BeanUtils.isNotEmpty(map.get("HOURLONG"))?Float.valueOf(map.get("HOURLONG").toString()):0);
						vo.setIncomplete(BeanUtils.isNotEmpty(map.get("INCOMPLETE"))?Integer.valueOf(map.get("INCOMPLETE").toString()):0);
						vo.setInstances(BeanUtils.isNotEmpty(map.get("INSTANCES"))?Integer.valueOf(map.get("INSTANCES").toString()):0);
						rows.add(vo);
					}
				}else{
					JavaType javaType = JsonUtil.getMapper().getTypeFactory().constructParametricType(ArrayList.class, FlowOrgCountVo.class); 
					rows = JsonUtil.getMapper().readValue(JsonUtil.toJson(selectList), javaType);
				}
				if(BeanUtils.isNotEmpty(rows)){
					pageList.setRows(rows);
					pageList.setPageSize(rows.size());
					pageList.setTotal(rows.size());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pageList;
	}

	@Override
	public PageList<FlowUserCountVo> flowUserCountList(QueryFilter queryFilter) {
		List<Map<String, Object>> selectList = this.getFlowUserSelectList(queryFilter);
		return parseToUserPageList(selectList);
	}
	
	public List<Map<String, Object>> getFlowUserSelectList(QueryFilter queryFilter){
		String dbType =  getDbType();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.PROC_DEF_KEY_ as procDefKey, a.PROC_DEF_NAME_ as procDefName, a.CREATE_BY_ as userId, a.CREATOR_ as userName,");
		if(SQLConst.DB_ORACLE.equals(dbType)){
			sql.append("ROUND(( SELECT SUM((TO_DATE ( TO_CHAR ( ( CASE END_TIME_ WHEN END_TIME_ THEN END_TIME_ ELSE (SELECT SYSDATE FROM dual) END ), 'YYYY-MM-DD HH24-MI-SS' ), 'YYYY-MM-DD HH24-MI-SS' ) - TO_DATE ( TO_CHAR ( CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24) FROM BPM_PRO_INST WHERE PROC_DEF_KEY_=a.PROC_DEF_KEY_)/ COUNT(a.ID_),1) AS avgLong,");
			sql.append("(SELECT count(1) FROM (SELECT PROC_DEF_KEY_,CREATE_BY_,((TO_DATE (TO_CHAR ((CASE END_TIME_ WHEN END_TIME_ THEN END_TIME_ ELSE (SELECT SYSDATE FROM dual) END ), 'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS') - TO_DATE (TO_CHAR (CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24 ) as otime FROM bpm_pro_inst) c where c.otime>24 AND c.PROC_DEF_KEY_=a.PROC_DEF_KEY_ AND c.CREATE_BY_=a.CREATE_BY_) as overtime,");
		}else if(SQLConst.DB_POSTGRESQL.equals(dbType)) {
			sql.append("ROUND(( SELECT SUM((TO_DATE ( TO_CHAR ( ( CASE END_TIME_ WHEN END_TIME_ THEN END_TIME_ ELSE now() END ), 'YYYY-MM-DD HH24-MI-SS' ), 'YYYY-MM-DD HH24-MI-SS' ) - TO_DATE ( TO_CHAR ( CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24) FROM BPM_PRO_INST WHERE PROC_DEF_KEY_=a.PROC_DEF_KEY_)/ COUNT(a.ID_),1) AS avgLong,");
			sql.append("(SELECT count(1) FROM (SELECT PROC_DEF_KEY_,CREATE_BY_,((TO_DATE (TO_CHAR ((CASE END_TIME_ WHEN END_TIME_ THEN END_TIME_ ELSE now() END ), 'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS') - TO_DATE (TO_CHAR (CREATE_TIME_,'YYYY-MM-DD HH24-MI-SS'),'YYYY-MM-DD HH24-MI-SS')) * 24 ) as otime FROM bpm_pro_inst) c where c.otime>24 AND c.PROC_DEF_KEY_=a.PROC_DEF_KEY_ AND c.CREATE_BY_=a.CREATE_BY_) as overtime,");
		}else if(SQLConst.DB_H2.equals(dbType)){
			sql.append("ROUND((ROUND(SUM(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))) / 60,1))/count(a.ID_),1) as avgLong,");
			sql.append("(SELECT count(1) FROM (SELECT PROC_DEF_KEY_,CREATE_BY_,TIMESTAMPDIFF(HOUR,CREATE_TIME_,(CASE WHEN END_TIME_ IS NOT NULL THEN END_TIME_ ELSE now() END)) as otime FROM bpm_pro_inst) c where c.otime>24 AND c.PROC_DEF_KEY_=a.PROC_DEF_KEY_ AND c.CREATE_BY_=a.CREATE_BY_) as overtime,");
		}else{
			sql.append("ROUND((ROUND(SUM(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,IF (a.END_TIME_ IS NOT NULL,a.END_TIME_,now()))) / 60,1))/count(a.ID_),1) as avgLong,");
			sql.append("(SELECT count(1) FROM (SELECT PROC_DEF_KEY_,CREATE_BY_,TIMESTAMPDIFF(HOUR,CREATE_TIME_,IF(END_TIME_ IS NOT NULL,END_TIME_,now())) as otime FROM bpm_pro_inst) c where c.otime>24 AND c.PROC_DEF_KEY_=a.PROC_DEF_KEY_ AND c.CREATE_BY_=a.CREATE_BY_) as overtime,");
		}
		sql.append("ROUND((select count(1) from bpm_pro_inst b where b.STATUS_ in ('end','manualend') AND b.PROC_DEF_KEY_=a.PROC_DEF_KEY_ AND b.CREATE_BY_=a.CREATE_BY_ "+getFlowUserWhereSql(queryFilter, "b")+")/count(1)*100) as closingRate ");
		sql.append(" FROM bpm_pro_inst a WHERE 1=1 ");
		sql.append(getFlowUserWhereSql(queryFilter, "a"));
		sql.append(" GROUP BY a.PROC_DEF_KEY_,a.PROC_DEF_NAME_,a.CREATE_BY_,a.CREATOR_ ");
		return querySql(sql.toString());
	}
	
	private String getFlowUserWhereSql(QueryFilter queryFilter,String pre){
		StringBuilder whereSql = new StringBuilder();
		List<QueryField> querys = queryFilter.getQuerys();
		if(BeanUtils.isNotEmpty(queryFilter) && BeanUtils.isNotEmpty(querys)){
			for (QueryField queryField : querys) {
				if("createTime".equals(queryField.getProperty()) && BeanUtils.isNotEmpty(queryField.getValue())){
					List<String> times = (List<String>) queryField.getValue();
					whereSql.append(" AND "+pre+".CREATE_TIME_ >='"+times.get(0)+"' ");
					whereSql.append(" AND "+pre+".CREATE_TIME_ <='"+times.get(1)+"' ");
				}else if("procDefKey".equals(queryField.getProperty()) && BeanUtils.isNotEmpty(queryField.getValue())){
					String[] defKeys = queryField.getValue().toString().split(",");
					String defKeySql = StringUtil.convertListToSingleQuotesString(new HashSet<String>(Arrays.asList(defKeys)));
					whereSql.append(" AND "+pre+".PROC_DEF_KEY_ in("+defKeySql+")");
				}else if("createBy".equals(queryField.getProperty()) && BeanUtils.isNotEmpty(queryField.getValue())){
					String[] userIds = queryField.getValue().toString().split(",");
					String userSql = StringUtil.convertListToSingleQuotesString(new HashSet<String>(Arrays.asList(userIds)));
					whereSql.append(" AND "+pre+".CREATE_BY_ in("+userSql+")");
				}
			}
			return whereSql.toString();
		}
		return "";
	}
	
	/**
	 * 转userPageList
	 * @param selectList
	 * @return
	 */
	private PageList<FlowUserCountVo> parseToUserPageList(List<Map<String, Object>> selectList){
		PageList<FlowUserCountVo> pageList = new PageList<>();
		pageList.setPage(1);
		pageList.setPageSize(1);
		pageList.setTotal(0);
		pageList.setRows(new ArrayList<FlowUserCountVo>());
		try {
			if(BeanUtils.isNotEmpty(selectList)){
				List<FlowUserCountVo> rows = new ArrayList<FlowUserCountVo>();
				if(selectList.get(0).containsKey("PROCDEFKEY")){
					for (Map<String, Object> map : selectList) {
						FlowUserCountVo vo = new FlowUserCountVo();
						vo.setProcDefKey(map.get("PROCDEFKEY").toString());
						vo.setProcDefName(map.get("PROCDEFNAME").toString());
						vo.setUserId(map.get("USERID").toString());
						vo.setUserName(map.get("USERNAME").toString());
						vo.setAvgLong(BeanUtils.isNotEmpty(map.get("AVGLONG"))?Float.valueOf(map.get("AVGLONG").toString()):0);
						vo.setOvertime(BeanUtils.isNotEmpty(map.get("OVERTIME"))?Integer.valueOf(map.get("OVERTIME").toString()):0);
						vo.setClosingRate(BeanUtils.isNotEmpty(map.get("CLOSINGRATE"))?Integer.valueOf(map.get("CLOSINGRATE").toString()):0);
						rows.add(vo);
					}
				}else{
					JavaType javaType = JsonUtil.getMapper().getTypeFactory().constructParametricType(ArrayList.class, FlowUserCountVo.class); 
					rows = JsonUtil.getMapper().readValue(JsonUtil.toJson(selectList), javaType);
				}
				
				if(BeanUtils.isNotEmpty(rows)){
					pageList.setRows(rows);
					pageList.setPageSize(rows.size());
					pageList.setTotal(rows.size());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pageList;
	}

	@Override
    @Transactional
	public void removeByReportId(String id) {
		baseMapper.removeByReportId(id);
	}
	
}
