package com.product.administration.service;

import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.product.admin.service.CodeService;
import com.product.admin.service.RouterService;
import com.product.admin.service.UpdateLoginUserInfoService;
import com.product.administration.config.CmnConst;
import com.product.administration.config.SystemCode;
import com.product.administration.service.ide.ITargetManagerService;
import com.product.common.excel.EasyExcelUtil;
import com.product.common.lang.DateUtils;
import com.product.common.lang.StringUtils;
import com.product.core.cache.DataPoolCacheImpl;
import com.product.core.cache.DataPoolRefreshCache;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.entity.RequestParameterEntity;
import com.product.core.exception.BaseException;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.service.support.QueryFilterService;
import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.core.transfer.Transactional;
import com.product.core.websocket.service.WebsocketMesssageServiceThread;
import com.product.module.sys.entity.SystemUser;
import com.product.util.BaseUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cheng
 * @Description 指标管理 业务层
 * @date 2021年9月13日16:07:14
 */
@Service
public class TargetManagerService extends AbstractBaseService implements ITargetManagerService {

    @Autowired
    private QueryFilterService queryFilterService;
    @Autowired
    private CodeService codeService;
    @Autowired
    private UpdateLoginUserInfoService updateLoginUserInfoService;
    @Autowired
    private RouterService routerService;

    /**
     * 查询功能字段 field_type = datetime,userid
     *
     * @param tableName
     * @return
     * @throws BaseException
     */
    @Override
    public Map<String, Object> getFieldsByTableName(String tableName) throws BaseException {
        DataPoolCacheImpl instance = DataPoolCacheImpl.getInstance();
        String table_uuid = getBaseDao().getTableUuid(tableName);
        DataTableEntity dt = instance.getCacheData("所有字段信息并按表分组", new String[]{table_uuid});
        if (!BaseUtil.dataTableIsEmpty(dt)) {
            List<Object> user = Lists.newArrayList();
            List<Object> datetime = Lists.newArrayList();
            for (int i = 0; i < dt.getRows(); i++) {
                String field_type = dt.getString(i, CmnConst.FIELD_TYPE);
                if (StringUtils.isEmpty(field_type)) {
                    continue;
                }
                HashMap<Object, Object> values = (HashMap<Object, Object>) dt.getFieldSetEntity(i).getValues();
                values = (HashMap<Object, Object>) values.clone();
                if ("datetime".equals(field_type)) {
                    datetime.add(values);
                } else if ("userid".equals(field_type) || "orgUuid".equals(field_type)) {
                    user.add(values);
                } else {
                    values.clear();
                }
            }
            Map<String, Object> m = Maps.newHashMap();
            m.put("user", user);
            m.put("datetime", datetime);
            return m;
        }
        return null;
    }

    /**
     * 指标分类树
     *
     * @return
     * @throws BaseException
     */
    @Override
    public JSONArray getTargetClassifyTree() throws BaseException {
        SystemUser currentUser = SpringMVCContextHolder.getCurrentUser();
        String org_level_uuid = currentUser.getOrg_level_uuid();
        DataTableEntity dataTableEntity = getBaseDao().listTable(CmnConst.PRODUCT_OA_TARGET_CLASSIFICATION);
        JSONArray result = new JSONArray();
        JSONObject topTree = new JSONObject();
        topTree.put("icon", "fa fa-university");
        topTree.put("classification_name", "指标分类");
        topTree.put("type", 1);
        result.add(topTree);
        if (BaseUtil.dataTableIsEmpty(dataTableEntity)) {
            topTree.put("children", Lists.newArrayList());
        } else {
            topTree.put("children", BaseUtil.dataTableToTreeData(dataTableEntity, CmnConst.UUID, "parent_uuid", (objects) -> {
                JSONObject object = objects[0];
                if (object.get("children") == null) {
                    object.put("children", Lists.newArrayList());
                }
                object.put("icon", "fa fa-folder");
            }, true));
        }
        return result;
    }

    /**
     * 保存指标分类
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    @Override
    public String saveTargetClassify(FieldSetEntity fse) throws BaseException {
        BaseUtil.createCreatorAndCreationTime(fse);
        getBaseDao().saveFieldSetEntity(fse);
        return fse.getUUID();
    }

    /**
     * 删除指标分类
     *
     * @param uuid
     * @throws BaseException
     */
    @Override
    public void deleteTargetClassify(String uuid) throws BaseException {
        FieldSetEntity fs = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_OA_TARGET_BASIC, "target_classification=?", new Object[]{uuid}, false);
        if (fs != null && !StringUtils.isEmpty(fs.getUUID())) {
            throw new BaseException(SystemCode.DELETE_TARGET_CLASSIFY_EXIST_CITE.getValue(), SystemCode.DELETE_TARGET_CLASSIFY_EXIST_CITE.getText());
        }
        getBaseDao().delete(CmnConst.PRODUCT_OA_TARGET_CLASSIFICATION, new Object[]{uuid});
    }

    /**
     * 查询指标库详情
     *
     * @param uuid
     * @return
     * @throws BaseException
     */
    @Override
    public FieldSetEntity findTargetBasicByUuid(String uuid) throws BaseException {
        FieldSetEntity fse = getBaseDao().getFieldSetEntity(CmnConst.PRODUCT_OA_TARGET_BASIC, uuid, false);
        String expression_config = fse.getString("expression_config");
        String filter_config = fse.getString("filter_config");
        if (!StringUtils.isEmpty(expression_config)) {
            JSONArray array = JSONArray.parseArray(expression_config);
            fse.setValue("expression_config", array);
        }
        if (!StringUtils.isEmpty(filter_config)) {
            JSONArray array = JSONArray.parseArray(filter_config);
            fse.setValue("filter_config", array);
        }
        return fse;
    }

    /**
     * 保存指标基础库
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    @Override
    public String saveTargetBasic(FieldSetEntity fse) throws BaseException {
        try {
            String expression = fse.getString(CmnConst.EXPRESSION_STATEMENT);
            StringBuilder sql = new StringBuilder(512);
            sql.append("select ").append(transferExpression(expression)).append(" from ").append(fse.getString(CmnConst.SOURCE_TABLE));
            if (!StringUtils.isEmpty(fse.getString(CmnConst.FILTER_STATEMENT))) {
                sql.append("\nwhere ").append(transferExpression(fse.getString(CmnConst.FILTER_STATEMENT)));
            }
            DataTableEntity dte = getBaseDao().listTable(sql.toString(), new Object[]{});
            if (dte.getRows() != 1) {
                throw new BaseException(SystemCode.UNQUALIFIED_EXPRESSION.getValue(), SystemCode.UNQUALIFIED_EXPRESSION.getText());
            }
        } catch (Exception e) {
            throw new BaseException(SystemCode.UNQUALIFIED_EXPRESSION.getValue(), SystemCode.UNQUALIFIED_EXPRESSION.getText());
        }

        BaseUtil.createCreatorAndCreationTime(fse);
        getBaseDao().saveFieldSetEntity(fse);
        return fse.getUUID();
    }

    /**
     * 删除指标基础库
     *
     * @param uuids
     * @throws BaseException
     */
    @Override
    public void deleteTargetBasic(String[] uuids) throws BaseException {
        getBaseDao().delete(CmnConst.PRODUCT_OA_TARGET_BASIC, uuids);
    }

    /**
     * 指标下达-保存
     *
     * @param fse
     * @return
     */
    @Transactional
    @Override
    public String saveAssignTargetTask(FieldSetEntity fse) {
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        BaseUtil.addOrgLeveLUUID(fse, Collections.singletonList(CmnConst.PRODUCT_OA_TARGET_TASK_SUB), curUser.getOrg_level_uuid(), curUser);
        getBaseDao().saveFieldSetEntity(fse);
        return fse.getUUID();
    }

    /**
     * 指标下达-导入数据
     *
     * @return
     */
    @Override
    public JSONArray importData(RequestParameterEntity rpe) {
        Map<String, File> fileMap = rpe.getFiles();

        String aimSheetName = "import_data";
        Map<String, List<List<String>>> map;
        try {
            FieldSetEntity formData = rpe.getFormData();
            map = EasyExcelUtil.readExcelByStringFromInputStream(new FileInputStream(fileMap.get(formData.getString("file"))), aimSheetName);
        } catch (Exception e) {
            throw new BaseException(SystemCode.IMPORT_FILE_FAIL.getValue(), SystemCode.IMPORT_FILE_FAIL.getText());
        }
        JSONArray resultArr = new JSONArray();
        JSONObject resultObj;
        List<String> paramList = Lists.newArrayList();
        Map<String, String> paramMap = Maps.newHashMap();
        int type = 0;

        List<List<String>> dataList = map.get(aimSheetName);
        List<String> rowList;
        StringBuilder filterSb = new StringBuilder(512);
        for (int i = 1; i < dataList.size(); i++) {
            rowList = dataList.get(i);
            if (filterSb.length() > 0) {
                filterSb.append(" or ");
            }
            if (i == 1) {
                if (!StringUtils.isEmpty(rowList.get(0))) {
                    type = 1;
                    filterSb.append("(corp_name=?");
                    paramList.add(rowList.get(0));
                    paramMap.put(rowList.get(0), rowList.get(3));
                    if (!StringUtils.isEmpty(rowList.get(1))) {
                        type = 2;
                        filterSb.append(" and dept_name=?");
                        paramList.add(rowList.get(1));
                        paramMap.remove(rowList.get(0));
                        paramMap.put(rowList.get(1), rowList.get(3));
                        if (!StringUtils.isEmpty(rowList.get(2))) {
                            type = 4;
                            filterSb.append(" and user_name=?");
                            paramList.add(rowList.get(2));
                            paramMap.remove(rowList.get(1));
                            paramMap.put(rowList.get(2), rowList.get(3));
                        }
                    }
                    filterSb.append(")");
                }
            } else {
                if (type == 1) {
                    if (!StringUtils.isEmpty(rowList.get(0))) {
                        filterSb.append("(corp_name=?)");
                        paramList.add(rowList.get(0));
                        paramMap.put(rowList.get(0), rowList.get(3));
                    }
                } else if (type == 2) {
                    if (!StringUtils.isEmpty(rowList.get(0)) && !StringUtils.isEmpty(rowList.get(1))) {
                        filterSb.append("(corp_name=? and dept_name=?)");
                        paramList.add(rowList.get(0));
                        paramList.add(rowList.get(1));
                        paramMap.put(rowList.get(1), rowList.get(3));
                    }
                } else if (type == 4) {
                    if (!StringUtils.isEmpty(rowList.get(0)) && !StringUtils.isEmpty(rowList.get(1))) {
                        filterSb.append("(corp_name=? and dept_name=? and user_name=?)");
                        paramList.add(rowList.get(0));
                        paramList.add(rowList.get(1));
                        paramList.add(rowList.get(2));
                        paramMap.put(rowList.get(2), rowList.get(3));
                    }
                }
            }
        }
        String fieldName;
        String filterFieldName;
        if (type == 1) {
            fieldName = "corp_uuid";
            filterFieldName = "corp_name";
        } else if (type == 2) {
            fieldName = "dept_uuid";
            filterFieldName = "dept_name";
        } else {
            fieldName = CmnConst.USER_ID;
            filterFieldName = CmnConst.USER_NAME;
        }
        StringBuilder sql = new StringBuilder(512);
        sql.append("\nselect * from (");
        sql.append("\n    select distinct c.org_level_name corp_name,c.uuid corp_uuid");
        if (type > 1) {
            sql.append(",d.org_level_name dept_name,d.uuid dept_uuid");
            if (type > 2) {
                sql.append(",u.user_name,u.user_id");
            }
        }
        sql.append("\n    FROM product_sys_users u");
        sql.append("\n    inner join product_sys_staffs s on u.user_id=s.user_id");
        sql.append("\n    inner join product_sys_org_levels d on s.dept_uuid=d.uuid");
        sql.append("\n    inner join product_sys_org_levels c on s.org_level_uuid=c.uuid");
        sql.append("\n) t");
        sql.append("\nwhere ").append(filterSb);
        DataTableEntity responsibleDte = getBaseDao().listTable(sql.toString(), paramList.toArray());
        FieldSetEntity responsibleFse;
        for (int i = 0; i < responsibleDte.getRows(); i++) {
            responsibleFse = responsibleDte.getFieldSetEntity(i);
            resultObj = new JSONObject();
            resultObj.put("task_responsible", responsibleFse.getString(fieldName));
            resultObj.put("task_responsible_name", responsibleFse.getString(filterFieldName));
            resultObj.put("target_value", paramMap.get(responsibleFse.getString(filterFieldName)));
            resultObj.put("organization_type", type);
            resultArr.add(resultObj);
        }
        return resultArr;
    }

    /**
     * 下达指标-详情
     *
     * @param fse
     * @return
     */
    @Override
    public FieldSetEntity findAssignTargetTask(FieldSetEntity fse) {
        FieldSetEntity fs = getBaseDao().getFieldSetEntity(fse.getTableName(), fse.getUUID(), true);
        if (!StringUtils.isEmpty(fs.getString(CmnConst.DATETIME_RANGE))) {
            fs.setValue(CmnConst.DATETIME_RANGE, fs.getString(CmnConst.DATETIME_RANGE).split(","));
        }
        return fs;
    }

    /**
     * 获取指标名称 (指标下达左侧树）
     *
     * @return
     * @throws BaseException
     */
    @Override
    public DataTableEntity getTargetNameList() throws BaseException {
        return getBaseDao().listTable("SELECT target_name,uuid,'fa fa-users' icon,2 type from product_oa_target_basic", new Object[]{});
    }

    /**
     * 获取指标配置 公司过滤
     * 用于指标下达
     *
     * @return
     * @throws BaseException
     */
    @Override
    public DataTableEntity getTargetBasicTree() throws BaseException {
        DataTableEntity resultDataTable = new DataTableEntity();
        StringBuilder sql = new StringBuilder();
        sql.append(" select uuid,parent_uuid,classification_name `name`,null is_percentage ,null target_cycle,1 type,null datetime_value_field ");
        sql.append(" from ");
        sql.append(" product_oa_target_classification ");
//        sql.append(" where org_level_uuid=? ");
        sql.append(" union all ");
        sql.append(" select uuid,target_classification parent_uuid,target_name `name`,is_percentage,target_cycle,2 type,datetime_value_field ");
        sql.append(" from ");
        sql.append(" product_oa_target_basic ");
//        sql.append(" where org_level_uuid=? ");
        String org_level_uuid = SpringMVCContextHolder.getCurrentUser().getOrg_level_uuid();
        if (StringUtils.isEmpty(org_level_uuid)) {
            return resultDataTable;
        }
        DataTableEntity dt = getBaseDao().listTable(sql.toString(), new Object[]{});
        final String children = "children";
        //分类集合
        Map<String, FieldSetEntity> classifyMap = Maps.newHashMap();
        List<FieldSetEntity> result = Lists.newArrayList();
        if (!BaseUtil.dataTableIsEmpty(dt)) {
            for (int i = 0; i < dt.getRows(); i++) {
                FieldSetEntity fs = dt.getFieldSetEntity(i);
                // type= 1 分类 type= 2 指标库
                String type = fs.getString("type");
                if ("1".equals(type)) {
                    classifyMap.put(fs.getUUID(), fs);
                    continue;
                } else {
                    //上级uuid
                    String parent_uuid = fs.getString("parent_uuid");
                    //初始进入时 取出 指标库的分类
                    FieldSetEntity parentFieldSet = classifyMap.get(parent_uuid);
                    FieldSetEntity value = fs;
                    //循环找上级分类
                    while (!StringUtils.isEmpty(parent_uuid) && value != null && parentFieldSet != null) {
                        if (parentFieldSet != null) {
                            Map<String, DataTableEntity> subData = parentFieldSet.getSubData();
                            if (subData == null) {
                                subData = new TreeMap<>();
                                subData.put(children, new DataTableEntity());
                                parentFieldSet.setSubData(subData);
                            }
                            DataTableEntity dataTableEntity = subData.get(children);
                            List<FieldSetEntity> data = dataTableEntity.getData();
                            if (data == null || !data.contains(value)) {
                                dataTableEntity.addFieldSetEntity(value);
                                //获取上级的上级uuid
                                parent_uuid = parentFieldSet.getString("parent_uuid");
                                value = parentFieldSet;
                                //获取分类的上级
                                parentFieldSet = classifyMap.get(parent_uuid);
                            } else {
                                value = parentFieldSet;
                                parent_uuid = null;
                            }

                        }
                    }
                    if (!result.contains(value)) {
                        result.add(value);
                    }
                }

            }
            resultDataTable.setData(result);
        }

        return resultDataTable;
    }

    /**
     * 我的指标任务-列表
     *
     * @param fse
     * @return
     */
    @Override
    public DataTableEntity listMyTargetTask(FieldSetEntity fse) {
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        BaseDao baseDao = getBaseDao();

        int pageSize = fse.getInteger(CmnConst.PAGESIZE) == null ? Integer.MAX_VALUE : fse.getInteger(CmnConst.PAGESIZE);
        int curPage = fse.getInteger(CmnConst.CPAGE) == null ? 1 : fse.getInteger(CmnConst.CPAGE);
        StringBuilder sql = new StringBuilder(256);
        sql.append("\nselect * from (");
        sql.append("\n    select tm.uuid task_uuid,ts.uuid,tm.target_task_name,ts.target_value,case when ts.user_id is not null then u.user_name else o.org_level_name end organization_value,tm.datetime_range_type,ts.datetime_range,ifnull(ts.updated_utc_datetime,ts.created_utc_datetime) orderby,b.target_classification");
        sql.append("\n    from (");
        sql.append("\n        select uuid,created_utc_datetime,updated_utc_datetime,target_value,task_responsible,datetime_range,task_uuid,organization_type,");
        sql.append("\n            case when organization_type=4 then task_responsible else null end user_id,");
        sql.append("\n            case when organization_type in (1,2) then task_responsible else null end task_responsible_org_level_uuid");
        sql.append("\n        from product_oa_target_task_sub");
        sql.append("\n        where target_value>0");
        sql.append("\n    ) ts");
        sql.append("\n    left join product_sys_org_levels o on ts.task_responsible_org_level_uuid=o.uuid");
        sql.append("\n    left join (");
        sql.append("\n        select org_level_uuid,user_id from product_sys_staffs where is_org_manager=1");
        sql.append("\n        union all");
        sql.append("\n        select dept_uuid,user_id from product_sys_staffs where is_dept_manage=1");
        sql.append("\n    ) s on o.uuid=s.org_level_uuid");
        sql.append("\n    left join product_oa_target_task tm on ts.task_uuid=tm.uuid");
        sql.append("\n    left join product_sys_users u on ts.user_id=u.user_id");
        sql.append("\n    left join product_oa_target_basic b on b.uuid=tm.target_uuid");
        sql.append("\n    where ifnull(ts.user_id,s.user_id)=?");
        sql.append("\n) t");
        String queryFilter = queryFilterService.getQueryFilter(fse);
        if (!StringUtils.isEmpty(fse.getString("filter"))) {
            String filter = fse.getString("filter");
            if (!StringUtils.isEmpty(queryFilter)) {
                queryFilter += " and ";
            }
            queryFilter += " (" + filter + " ) ";
        }
        if (!StringUtils.isEmpty(queryFilter)) {
            queryFilter = queryFilter.replaceAll("\\s+or\\s+progress_value\\s+like\\s+concat\\('\\%','.+','\\%'\\)\\s+", " ");
            sql.append("\nwhere ").append(queryFilter);
        }
        sql.append("\norder by orderby");

        DataTableEntity tempDataDte = baseDao.listTable(sql.toString(), new Object[]{curUser.getUser_id()}, pageSize, curPage);
        if (BaseUtil.dataTableIsEmpty(tempDataDte)) {
            return new DataTableEntity();
        }

        // 特殊处理，时间范围、进度
        spDealDte(tempDataDte);

        return tempDataDte;
    }

    /**
     * 根据指标任务子表数据的uuid提取对应的进度
     *
     * @param uuidArr
     */
    public Map<String, Integer> getProgressByUUID(Object[] uuidArr) {
        StringBuilder sql = new StringBuilder(256);
        sql.append("\nselect ts.uuid,tm.target_task_name,ts.target_value,ts.datetime_range,b.source_table,b.org_value_field,b.datetime_value_field,tm.task_responsible_type,");
        sql.append("\n    ts.organization_type,ts.task_responsible,tm.datetime_range_type,b.expression_statement,b.filter_statement,b.is_percentage");
        sql.append("\nfrom (");
        sql.append("\n    select uuid,target_value,task_responsible,datetime_range,task_uuid,organization_type,");
        sql.append("\n        case when organization_type=4 then task_responsible else null end user_id,");
        sql.append("\n        case when organization_type in (1,2) then task_responsible else null end task_responsible_org_level_uuid");
        sql.append("\n    from product_oa_target_task_sub");
        sql.append("\n    where target_value>0");
        sql.append("\n    and ").append(BaseUtil.buildQuestionMarkFilter("uuid", uuidArr.length, true));
        sql.append("\n) ts");
        sql.append("\nleft join product_oa_target_task tm on ts.task_uuid=tm.uuid");
        sql.append("\nleft join product_oa_target_basic b on tm.target_uuid=b.uuid");
        DataTableEntity tempDte = getBaseDao().listTable(sql.toString(), uuidArr);
        FieldSetEntity tempFse;
        double progressValue;
        Map<String, Integer> resultMap = Maps.newHashMap();
        for (int i = 0; i < tempDte.getRows(); i++) {
            tempFse = tempDte.getFieldSetEntity(i);
            try {
                progressValue = getProgressValue(tempFse);
                if ("1".equals(tempFse.getString(CmnConst.IS_PERCENTAGE))) {
                    progressValue *= 100;
                }
                resultMap.put(tempFse.getString("uuid"), spDealProgressValue(progressValue));
            } catch (Exception e) {
                SpringMVCContextHolder.getSystemLogger().error("error: " + tempFse.getString(CmnConst.TARGET_TASK_NAME));
                SpringMVCContextHolder.getSystemLogger().error(e);
            }
        }
        return resultMap;
    }

    /**
     * 获取进度值
     *
     * @param fse
     * @return
     */
    private double getProgressValue(FieldSetEntity fse) {
        String sourceTable = fse.getString(CmnConst.SOURCE_TABLE);

        // 责任主体
        StringBuilder partSql = getResponsiblePartSql(fse);


        // 时间范围
        String dateTimeRangeField = fse.getString(CmnConst.DATETIME_VALUE_FIELD);
        String dateTimeRangeType = fse.getString(CmnConst.DATETIME_RANGE_TYPE);
        String dateTimeRange = fse.getString(CmnConst.DATETIME_RANGE);
        boolean addTimeProportionFlag = !StringUtils.isEmpty(dateTimeRangeField) && !StringUtils.isEmpty(dateTimeRangeType) && !StringUtils.isEmpty(dateTimeRange);
        FieldSetEntity valueFse = null;
        StringBuilder sql = new StringBuilder(128);
        if (addTimeProportionFlag) {
            if ("1".equals(dateTimeRangeType)) {
                // 任意时间段
                String[] arr = dateTimeRange.split(",");
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(arr[0]).append("','%Y-%m-%d') and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<str_to_date('").append(arr[1]).append("','%Y-%m-%d')");
                sql.append("select value/").append(fse.getString(CmnConst.TARGET_VALUE)).append("/(timestampdiff(HOUR,str_to_date('").append(arr[0]).append("','%Y-%m-%d'),now())/timestampdiff(HOUR,str_to_date('").append(arr[0]).append("','%Y-%m-%d'),str_to_date('").append(arr[1]).append("','%Y-%m-%d'))) progressValue from (");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                sql.append("\n) t");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else if ("2".equals(dateTimeRangeType)) {
                // 分解时间 暂放 todo
            } else if ("3".equals(dateTimeRangeType)) {
                // 动态时间（T+N）
                int deadline = Integer.parseInt(dateTimeRange);
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(" between now() and date_add(now(),INTERVAL ").append(deadline).append(" DAY)");
                sql.append("select value/").append(fse.getString(CmnConst.TARGET_VALUE)).append(" progressValue from (");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                sql.append("\n) t");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else if ("4".equals(dateTimeRangeType)) {
                // 周期时间
                List<String> timeList = getStartTimeAndFinalTime(dateTimeRange);
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d') and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d')");
                sql.append("select value/").append(fse.getString(CmnConst.TARGET_VALUE)).append("/(timestampdiff(HOUR,str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),now())/timestampdiff(HOUR,str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d'))) progressValue from (");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                sql.append("\n) t");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else {
                throw new BaseException(SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getValue(), SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getText());
            }
        } else {
            sql.append("select value/").append(fse.getString(CmnConst.TARGET_VALUE)).append(" progressValue from (");
            sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
            sql.append("\n) t");
            valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
        }

        return valueFse == null ? 0d : (valueFse.getDouble("progressValue") == null ? 0d : valueFse.getDouble("progressValue"));
    }

    /**
     * 获取指标值
     *
     * @param fse
     * @return
     */
    public double getActualTargetValue(FieldSetEntity fse) {
        StringBuilder responsiblePartSql = getResponsiblePartSql(fse);
        StringBuilder partSql = new StringBuilder(responsiblePartSql);

        // 责任主体
        String sourceTable = fse.getString(CmnConst.SOURCE_TABLE);

        // 时间范围
        String dateTimeRangeField = fse.getString(CmnConst.DATETIME_VALUE_FIELD);
        String dateTimeRangeType = fse.getString(CmnConst.DATETIME_RANGE_TYPE);
        boolean addTimeProportionFlag = !StringUtils.isEmpty(dateTimeRangeField) && !StringUtils.isEmpty(dateTimeRangeType);
        StringBuilder compSql = new StringBuilder();//同期或上期
        String dateTimeRange = fse.getString(CmnConst.DATETIME_RANGE);
        FieldSetEntity valueFse = null;
        StringBuilder sql = new StringBuilder(128);

        if (addTimeProportionFlag) {
            String ct = fse.getString(CmnConst.COMPARE_TYPE);//1 同比 2 环比
            if ("1".equals(dateTimeRangeType)) {
                // 任意时间段
                String[] arr = dateTimeRange.split(",");
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(arr[0]).append("','%Y-%m-%d') and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<str_to_date('").append(arr[1]).append("','%Y-%m-%d')");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                if ("1".equals(ct)) {
                    //同比
                    compSql.append(" AND ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=").append("DATE_ADD(").append("str_to_date('").append(arr[0]).append("','%Y-%m-%d'),INTERVAL  -1 YEAR) and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(arr[1]).append("','%Y-%m-%d'),INTERVAL  -1 YEAR) )");
                } else {
                    //环比
                    //获取上个周期时间段
                    String[] linkRelative = getLinkRelative(arr);
                    compSql.append(" AND ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(linkRelative[0]).append("','%Y-%m-%d') and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<=str_to_date('").append(linkRelative[1]).append("','%Y-%m-%d')");
                }
            } else if ("2".equals(dateTimeRangeType)) {
                // 分解时间 暂放 todo
            } else if ("3".equals(dateTimeRangeType)) {
                int deadline = Integer.parseInt(dateTimeRange);
                // 动态时间（T+N）
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(" between now() and date_add(now(),INTERVAL ").append(deadline).append(" DAY)");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                if ("1".equals(ct)) {
                    //同比
                    compSql.append(sourceTable).append(".").append(dateTimeRangeField).append(" between DATE_ADD(now(),INTERVAL -1 YEAR ) and date_add(DATE_ADD(now(),INTERVAL -1 YEAR),INTERVAL ").append(deadline).append(" DAY)");
                } else {
                    //环比
                    //当前时间
                    Date date = new Date();
                    //偏移时间 当前时间 - 偏移小时 dateLine(天） * 24(一天24小时)s
                    DateTime endTime = DateUtil.offset(date, DateField.HOUR_OF_DAY, deadline * 24);
                    String[] linkRelative = getLinkRelative(date, endTime);
                    //获取上个周期时间段
                    compSql.append(sourceTable).append(".").append(dateTimeRangeField).append(" between str_to_date(").append(linkRelative[0]).append(",'%Y-%m-%d %H:%i:%s') and  str_to_date(").append(linkRelative[1]).append(",'%Y-%m-%d %H:%i:%s' )");
                }

            } else if ("4".equals(dateTimeRangeType)) {
                // 周期时间
                List<String> timeList = getStartTimeAndFinalTime(dateTimeRange);
                partSql.append("\nand ").append(sourceTable).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d') and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d')");
                sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
                if ("1".equals(ct)) {
                    //同比
                    compSql.append(sourceTable).append(".").append(dateTimeRangeField).append(">=DATE_ADD(str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),INTERVAL -1 YEAR) and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d'),INTERVAL -1 YEAR)");
                } else {
                    //环比
                    String[] linkRelative = getLinkRelative(timeList.toArray(new String[]{}));
                    compSql.append(sourceTable).append(".").append(dateTimeRangeField).append(">=DATE_ADD(str_to_date('").append(linkRelative[0]).append("','%Y-%m-%d'),INTERVAL -1 YEAR) and ").append(sourceTable).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(linkRelative[1]).append("','%Y-%m-%d'),INTERVAL -1 YEAR)");
                }

            } else {
                throw new BaseException(SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getValue(), SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getText());
            }
            if (",1,2,".contains("," + ct + ",")) {
                //比较
                String fullSql = sql.toString();
                compSql.insert(0, responsiblePartSql).insert(0, " value ").insert(0, transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).insert(0, " SELECT ");
                sql.setLength(0);
                sql.append(" SELECT (ifnull(a.`value`,0)/ifnull(b.`value`,0)) as `value` FROM  ");
                sql.append(" ( ").append(fullSql).append(" ) a, ");
                sql.append(" ( ").append(compSql).append(" ) b ");
            }

            // 分解时间和统计行获取sql不进行sql执行
            if (!"2".equals(dateTimeRangeType) && !"6".equals(fse.getString(CmnConst.ORGANIZATION_TYPE))) {
                DataTableEntity valueDte = getBaseDao().listTable(sql.toString(), new Object[]{});
                if (valueDte.getRows() > 1) {
                    StringBuilder targetContent = new StringBuilder(64);
                    if (!StringUtils.isEmpty(fse.getString(CmnConst.TARGET_NAME))) {
                        targetContent.append("【").append(fse.getString(CmnConst.TARGET_NAME)).append("】 ");
                    }
                    throw new BaseException(SystemCode.MULTI_TARGET_AIM_VALUE.getValue(), targetContent.toString() + SystemCode.MULTI_TARGET_AIM_VALUE.getText());
                }
                valueFse = valueDte.getFieldSetEntity(0);
            }
        } else {
            sql.append("\nselect ").append(transferExpression(fse.getString(CmnConst.EXPRESSION_STATEMENT))).append(" value ").append(partSql);
            valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
        }

        fse.setValue("sql", sql);
        return valueFse == null ? 0d : (valueFse.getDouble(CmnConst.PARAM_VALUE) == null ? 0d : ("1".equals(fse.getString(CmnConst.IS_PERCENTAGE))) ? 100d * valueFse.getDouble(CmnConst.PARAM_VALUE) : valueFse.getDouble(CmnConst.PARAM_VALUE));
    }

    /**
     * 根据指标值获取对应的进度值
     *
     * @param fse
     * @return
     */
    private double getProgressValueByTargetValue(FieldSetEntity fse) {
        String dateTimeRangeType = fse.getString(CmnConst.DATETIME_RANGE_TYPE);
        String dateTimeRange = fse.getString(CmnConst.DATETIME_RANGE);
        boolean addTimeProportionFlag = !StringUtils.isEmpty(dateTimeRangeType) && !StringUtils.isEmpty(dateTimeRange);
        FieldSetEntity valueFse = null;
        StringBuilder sql = new StringBuilder(128);
        if (addTimeProportionFlag) {
            if ("1".equals(dateTimeRangeType)) {
                // 任意时间段
                String[] arr = dateTimeRange.split(",");
                sql.append("select ").append(fse.getString(CmnConst.PARAM_VALUE)).append("/").append(fse.getString(CmnConst.TARGET_VALUE)).append("/(timestampdiff(HOUR,str_to_date('").append(arr[0]).append("','%Y-%m-%d'),now())/timestampdiff(HOUR,str_to_date('").append(arr[0]).append("','%Y-%m-%d'),str_to_date('").append(arr[1]).append("','%Y-%m-%d'))) progressValue from dual");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else if ("2".equals(dateTimeRangeType)) {
                // 分解时间 暂放 todo
            } else if ("3".equals(dateTimeRangeType)) {
                // 动态时间（T+N）
                sql.append("select ").append(fse.getString(CmnConst.PARAM_VALUE)).append("/").append(fse.getString(CmnConst.TARGET_VALUE)).append(" progressValue from dual");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else if ("4".equals(dateTimeRangeType)) {
                // 周期时间
                List<String> timeList = getStartTimeAndFinalTime(dateTimeRange);
                sql.append("select ").append(fse.getString(CmnConst.PARAM_VALUE)).append("/").append(fse.getString(CmnConst.TARGET_VALUE)).append("/(timestampdiff(HOUR,str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),now())/timestampdiff(HOUR,str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d'))) progressValue from dual");
                valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
            } else {
                throw new BaseException(SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getValue(), SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getText());
            }
        } else {
            sql.append("select ").append(fse.getString(CmnConst.PARAM_VALUE)).append("/").append(fse.getString(CmnConst.TARGET_VALUE)).append(" progressValue from dual");
            valueFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
        }

        return valueFse == null ? 0d : (valueFse.getDouble("progressValue") == null ? 0d : valueFse.getDouble("progressValue"));
    }

    /**
     * 指标值-获取责任主体部分sql
     *
     * @param fse
     * @return
     */
    private StringBuilder getResponsiblePartSql(FieldSetEntity fse) {
        StringBuilder partSql = new StringBuilder(128);
        String sourceTable = fse.getString(CmnConst.SOURCE_TABLE);
        partSql.append("from ").append(sourceTable);

        // 责任主体
        String taskResponsibleField = fse.getString(CmnConst.ORG_VALUE_FIELD);
        String[] taskResponsibleFieldArr = taskResponsibleField.split(",");
        List<String> taskResponsibleFieldList = Lists.newArrayList();
        Collections.addAll(taskResponsibleFieldList, taskResponsibleFieldArr);
        DataTableEntity tableCacheDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_ALL_TABLE_INFO, new String[]{sourceTable});
        DataTableEntity fieldCacheDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_ALL_FIELD_GROUP_BY_TABLE, new String[]{tableCacheDte.getFieldSetEntity(0).getUUID()});
        FieldSetEntity fieldCacheFse = null;
        Map<String, String> fieldMap = Maps.newHashMap();
        for (int i = 0; i < fieldCacheDte.getRows(); i++) {
            fieldCacheFse = fieldCacheDte.getFieldSetEntity(i);
            if (taskResponsibleFieldList.contains(fieldCacheFse.getString(CmnConst.FIELD_NAME))) {
                fieldMap.put(fieldCacheFse.getString(CmnConst.FIELD_TYPE), fieldCacheFse.getString(CmnConst.FIELD_NAME));
            }
        }
        if (fieldMap.isEmpty()) {
            throw new BaseException(SystemCode.NO_MATCH_FIELD.getValue(), SystemCode.NO_MATCH_FIELD.getText());
        }
        String organizationType = fse.getString(CmnConst.ORGANIZATION_TYPE);
        String taskResponsible = fse.getString(CmnConst.TASK_RESPONSIBLE);
        if ("1".equals(organizationType) || "2".equals(organizationType)) {
            // 公司、部门
            taskResponsibleField = fieldMap.get(CmnConst.FIELD_TYPE_ORG);
            if (!StringUtils.isEmpty(taskResponsibleField)) {
                partSql.append("\nwhere ").append(taskResponsibleField).append(" in (");
                partSql.append("\n    select distinct d.uuid FROM product_sys_org_levels d");
                partSql.append("\n    inner join product_sys_org_levels m on d.org_level_code like concat(m.org_level_code,'%')");
                partSql.append("\n    where m.uuid='").append(taskResponsible).append("'");
                partSql.append("\n)");
            } else {
                taskResponsibleField = fieldMap.get(CmnConst.USERID);
                if (!CmnConst.USERID.equals(taskResponsibleField)) {
                    partSql.append("\nwhere ").append(taskResponsibleField).append(" in (");
                    partSql.append("\n    select distinct user_id FROM product_sys_staffs s");
                    partSql.append("\n    inner join product_sys_org_levels d on s.dept_uuid=d.uuid");
                    partSql.append("\n    inner join product_sys_org_levels m on d.org_level_code like concat(m.org_level_code,'%')");
                    partSql.append("\n    where m.uuid='").append(taskResponsible).append("'");
                    partSql.append("\n)");
                }
            }
        } else if ("4".equals(organizationType)) {
            // 人员
            taskResponsibleField = fieldMap.get(CmnConst.USERID);
            if (!StringUtils.isEmpty(taskResponsibleField)) {
                partSql.append("\nwhere ").append(taskResponsibleField).append("=").append(taskResponsible);
            } else {
                partSql.append("\nwhere 1=2");
            }
        } else if ("5".equals(organizationType)) {
            // 单位组
            taskResponsibleField = fieldMap.get(CmnConst.USERID);
            if (!StringUtils.isEmpty(taskResponsibleField)) {
                partSql.append("\ninner join product_sys_company_user_group on concat(','.PRODUCT_SYS_company_user_group.user_ids,',') like concat('%,',").append(sourceTable).append(".").append(taskResponsibleField).append(",',%')");
                partSql.append("\nwhere.PRODUCT_SYS_company_user_group.uuid='").append(taskResponsible).append("'");
            } else {
                partSql.append("\nwhere 1=2");
            }
        } else if ("6".equals(organizationType)) {
            // 特殊：获取统计行的过滤筛选sql
            partSql.append("\nwhere (");
            if (!StringUtils.isEmpty(fse.getString(CmnConst.TASK_RESPONSIBLE)) && !StringUtils.isEmpty(fse.getString(CmnConst.TASK_RESPONSIBLE).replace("[", "").replace("]", ""))) {
                if ("1".equals(fse.getString(CmnConst.PARAM_ROW_ORGANIZATION_TYPE)) || "2".equals(fse.getString(CmnConst.PARAM_ROW_ORGANIZATION_TYPE))) {
                    String orgUUIDParam = fse.getString(CmnConst.TASK_RESPONSIBLE).replace("[", "('").replace("]", "')").replace(",", "','");
                    if (!StringUtils.isEmpty(fieldMap.get(CmnConst.USERID))) {
                        partSql.append(fieldMap.get(CmnConst.USERID)).append(" in (");
                        partSql.append("\n    select user_id FROM product_sys_staffs s");
                        partSql.append("\n    inner join product_sys_org_levels o on s.dept_uuid=o.uuid");
                        partSql.append("\n    inner join product_sys_org_levels om on o.org_level_code like concat(om.org_level_code,'%')");
                        partSql.append("\n    where om.uuid in ").append(orgUUIDParam);
                        partSql.append("\n)");
                    }
                    if (!StringUtils.isEmpty(fieldMap.get(CmnConst.FIELD_TYPE_ORG))) {
                        if (!StringUtils.isEmpty(fieldMap.get(CmnConst.USERID))) {
                            partSql.append("\nor ");
                        }
                        partSql.append(fieldMap.get(CmnConst.FIELD_TYPE_ORG)).append(" in (");
                        partSql.append("\n    select o.uuid FROM product_sys_org_levels o");
                        partSql.append("\n    inner join product_sys_org_levels om on o.org_level_code like concat(om.org_level_code,'%')");
                        partSql.append("\n    where om.uuid in ").append(orgUUIDParam);
                        partSql.append("\n)");
                    }
                } else if ("4".equals(fse.getString(CmnConst.PARAM_ROW_ORGANIZATION_TYPE))) {
                    partSql.append(fieldMap.get(CmnConst.USERID)).append(" in ").append(fse.getString(CmnConst.TASK_RESPONSIBLE).replace("[", "(").replace("]", ")"));
                } else {
                    partSql.append("1=2");
                }
            } else {
                partSql.append("1=2");
            }
            partSql.append("\n)");
        } else {
            // 周期、非周期指标，没有固定的责任主体类型
            if ("4".equals(fse.getString(CmnConst.PARAM_ROW_ORGANIZATION_TYPE))) {
                if (taskResponsible.matches("\\d+")) {
                    partSql.append("\nwhere ").append(fieldMap.get(CmnConst.USERID)).append("=").append(taskResponsible);
                } else {
                    partSql.append("\nwhere 1=2");
                }
            } else {
                partSql.append("\nwhere (");
                if (!StringUtils.isEmpty(fieldMap.get(CmnConst.FIELD_TYPE_ORG))) {
                    partSql.append(fieldMap.get(CmnConst.FIELD_TYPE_ORG)).append(" in (");
                    partSql.append("\n    select distinct d.uuid FROM product_sys_org_levels d");
                    partSql.append("\n    inner join product_sys_org_levels m on d.org_level_code like concat(m.org_level_code,'%')");
                    partSql.append("\n    where m.uuid='").append(taskResponsible).append("'");
                    partSql.append("\n)");
                }
                if (!StringUtils.isEmpty(fieldMap.get(CmnConst.USERID))) {
                    if (!StringUtils.isEmpty(fieldMap.get(CmnConst.FIELD_TYPE_ORG))) {
                        partSql.append(" or ");
                    }
                    partSql.append(fieldMap.get(CmnConst.USERID)).append(" in (");
                    partSql.append("\n    select distinct user_id FROM product_sys_staffs s");
                    partSql.append("\n    inner join product_sys_org_levels d on s.dept_uuid=d.uuid");
                    partSql.append("\n    inner join product_sys_org_levels m on d.org_level_code like concat(m.org_level_code,'%')");
                    partSql.append("\n    where m.uuid='").append(taskResponsible).append("'");
                    partSql.append("\n)");
                }
                partSql.append(")");
            }
        }

        // 默认的指标过滤条件
        if (!StringUtils.isEmpty(fse.getString(CmnConst.FILTER_STATEMENT))) {
            partSql.append("\nand ").append(transferExpression(fse.getString(CmnConst.FILTER_STATEMENT)));
        }

        return partSql;
    }

    /**
     * 转化表达式
     *
     * @param expression
     * @return
     */
    private String transferExpression(String expression) {
        List<String> suitList = getSuitContent(expression, "\\{%AVG%\\}\\(.*\\)", null);
        String value;
        String replaceContent;
        Map<String, String> map = Maps.newLinkedHashMap();
        String reusltExpression = expression;
        for (String suitContent : suitList) {
            value = expression.substring(expression.indexOf("(") + 1, expression.indexOf(")"));
            replaceContent = suitContent.replace("{%AVG%}", "{%SUM%}") + "/" + "COUNT(" + value + ")";
            map.put(suitContent, replaceContent);
            reusltExpression = reusltExpression.replace(suitContent, replaceContent);
        }
        return reusltExpression.replace("{%", "").replace("%}", "").replace("{#", "").replace("#}", "");
    }

    /**
     * 根据正则返回符合条件的内容
     *
     * @param str       字符串
     * @param regexp    正则表达式
     * @param exceptKey 排除关键字（含有该关键字的内容不做处理）
     * @return
     */
    public static List<String> getSuitContent(String str, String regexp, String exceptKey) {
        Pattern pattern = Pattern.compile(regexp);
        Matcher matcher = pattern.matcher(str);
        List<String> list = Lists.newArrayList();
        String curContent;
        while (matcher.find()) {
            curContent = matcher.group();
            if (!com.alibaba.excel.util.StringUtils.isEmpty(exceptKey) && curContent.contains(exceptKey)) {
                continue;
            }
            list.add(curContent);
        }
        return list;
    }

    /**
     * 特殊处理进度值
     *
     * @param progressValue
     * @return
     */
    private int spDealProgressValue(double progressValue) {
        return (int) ((progressValue > 1d ? 1d : progressValue) * 100d);
    }

    /**
     * 根据周期时间类型获取开始时间和结束时间，前闭后开
     *
     * @param type
     * @return
     */
    public List<String> getStartTimeAndFinalTime(String type) {
        List<String> resultList = Lists.newArrayList();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        if ("1".equals(type)) {
            // 每年
            int year = c.get(Calendar.YEAR);
            c.clear();
            c.set(Calendar.YEAR, year);
            resultList.add(dateFormat.format(c.getTime()));
            c.add(Calendar.YEAR, 1);
            resultList.add(dateFormat.format(c.getTime()));
        } else if ("2".equals(type)) {
            // 每季
            int year = c.get(Calendar.YEAR);
            int month = c.get(Calendar.MONTH);
            c.clear();
            c.set(Calendar.YEAR, year);
            int startMonth = ((int) Math.floor((month + 1) / 3) - 1) * 3;
            int finalMonth = startMonth + 3;
            c.set(Calendar.MONTH, startMonth);
            resultList.add(dateFormat.format(c.getTime()));
            c.set(Calendar.MONTH, finalMonth);
            resultList.add(dateFormat.format(c.getTime()));
        } else if ("3".equals(type)) {
            // 每月
            int year = c.get(Calendar.YEAR);
            int month = c.get(Calendar.MONTH);
            c.clear();
            c.set(Calendar.YEAR, year);
            c.set(Calendar.MONTH, month);
            resultList.add(dateFormat.format(c.getTime()));
            c.add(Calendar.MONTH, 1);
            resultList.add(dateFormat.format(c.getTime()));
        } else if ("4".equals(type)) {
            // 每周
            int d;
            if (c.get(Calendar.DAY_OF_WEEK) == 1) {
                d = -6;
            } else {
                d = 2 - c.get(Calendar.DAY_OF_WEEK);
            }
            c.add(Calendar.DAY_OF_WEEK, d);
            resultList.add(dateFormat.format(c.getTime()));
            c.add(Calendar.DAY_OF_WEEK, 7);
            resultList.add(dateFormat.format(c.getTime()));
        } else if ("5".equals(type)) {
            // 每日
            int year = c.get(Calendar.YEAR);
            int month = c.get(Calendar.MONTH);
            int day = c.get(Calendar.DATE);
            c.clear();
            c.set(Calendar.YEAR, year);
            c.set(Calendar.MONTH, month);
            c.set(Calendar.DATE, day);
            resultList.add(dateFormat.format(c.getTime()));
            c.add(Calendar.DATE, 1);
            resultList.add(dateFormat.format(c.getTime()));
        } else {
            throw new BaseException(SystemCode.UNKNOWN_CIRCLE_LENGTH_TYPE.getValue(), SystemCode.UNKNOWN_CIRCLE_LENGTH_TYPE.getText());
        }
        return resultList;
    }

    /**
     * 特殊处理，时间范围、进度
     *
     * @param tempDataDte
     */
    private void spDealDte(DataTableEntity tempDataDte) {
        Object[] uuidArr = tempDataDte.getFieldAllValues("uuid");
        Map<String, Integer> progressMap = getProgressByUUID(uuidArr);

        FieldSetEntity tempFse;
        String dateTimeRangeType;
        String dateTimeRange;
        for (int i = 0; i < tempDataDte.getRows(); i++) {
            tempFse = tempDataDte.getFieldSetEntity(i);
            tempFse.setValue("progress_value", progressMap.get(tempFse.getUUID()));
            dateTimeRangeType = tempFse.getString(CmnConst.DATETIME_RANGE_TYPE);
            dateTimeRange = tempFse.getString(CmnConst.DATETIME_RANGE);
            if ("1".equals(dateTimeRangeType)) {
                tempFse.setValue(CmnConst.DATETIME_RANGE, dateTimeRange.replace(" 00:00:00", "").replace(",", "至"));
            }
            if ("3".equals(dateTimeRangeType)) {
                tempFse.setValue(CmnConst.DATETIME_RANGE, "T+" + dateTimeRange);
            }
            if ("4".equals(dateTimeRangeType)) {
                if ("1".equals(dateTimeRange)) {
                    tempFse.setValue(CmnConst.DATETIME_RANGE, "每年");
                } else if ("2".equals(dateTimeRange)) {
                    tempFse.setValue(CmnConst.DATETIME_RANGE, "每季");
                } else if ("3".equals(dateTimeRange)) {
                    tempFse.setValue(CmnConst.DATETIME_RANGE, "每月");
                } else if ("4".equals(dateTimeRange)) {
                    tempFse.setValue(CmnConst.DATETIME_RANGE, "每周");
                } else if ("5".equals(dateTimeRange)) {
                    tempFse.setValue(CmnConst.DATETIME_RANGE, "每日");
                }
            }
        }
    }

    /**
     * 下级指标任务-列表
     *
     * @param fse
     * @return
     */
    @Override
    public DataTableEntity listSubordinateTargetTask(FieldSetEntity fse) {
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        FieldSetEntity staffFse = curUser.getCurrentStaff();

        StringBuilder sql = new StringBuilder(512);
        sql.append("\nselect *");
        sql.append("\nfrom (");
        sql.append("\n    select tm.uuid task_uuid,ts.uuid,tm.target_task_name,ts.target_value,case when length(o.org_level_name)>0 then o.org_level_name else s.show_name end organization_value,tm.datetime_range_type,ts.datetime_range,b.target_classification");
        sql.append("\n    from (");
        sql.append("\n        select");
        sql.append("\n            case when organization_type in (1,2) then task_responsible else null end org_level_uuid,");
        sql.append("\n            case when organization_type=4 then task_responsible else null end user_id,");
        sql.append("\n            target_value,datetime_range,task_uuid,uuid");
        sql.append("\n        from (");
        sql.append("\n            select uuid,task_uuid,organization_type,task_responsible,target_value,datetime_range,1 can_update");
        sql.append("\n            from product_oa_target_task_sub");
        sql.append("\n        ) t");
        sql.append("\n    ) ts");
        sql.append("\n    left join (");
        sql.append("\n        select org_level_name,uuid");
        sql.append("\n        FROM product_sys_org_levels os");
        // 顶层看所有
        List<Object> paramList = Lists.newArrayList();
        if (!"001".equals(curUser.getOrg_level_code())) {
            sql.append("\n        inner join (");
            sql.append("\n            select org_level_code FROM product_sys_org_levels");
            sql.append("\n            where org_level_leader_uuid in (");
            sql.append("\n                select uuid FROM product_sys_staffs where user_id=?");
            sql.append("\n            )");
            sql.append("\n        ) om on os.org_level_code like concat(om.org_level_code,'%')");
            paramList.add(curUser.getUser_id());
        }
        sql.append("\n    ) o on ts.org_level_uuid=o.uuid");
        sql.append("\n    left join (");
        sql.append("\n        select show_name,user_id");
        sql.append("\n        FROM product_sys_staffs");
        sql.append("\n        where leader_tricode like concat(?,'%')");
        sql.append("\n    ) s on s.user_id=ts.user_id");
        sql.append("\n    inner join product_oa_target_task tm on ts.task_uuid=tm.uuid");
        sql.append("\n    inner join product_oa_target_basic b on tm.target_uuid=b.uuid");
        sql.append("\n    where (length(o.org_level_name)>0 or length(s.show_name)>0)");
        sql.append("\n) t");

        String queryFilter = queryFilterService.getQueryFilter(fse);
        if (!StringUtils.isEmpty(fse.getString("filter"))) {
            String filter = fse.getString("filter");
            if (!StringUtils.isEmpty(queryFilter)) {
                queryFilter += " and ";
            }
            queryFilter += " (" + filter + " ) ";
        }
        if (!StringUtils.isEmpty(queryFilter)) {
            queryFilter = queryFilter.replaceAll("\\s+or\\s+progress_value\\s+like\\s+concat\\('\\%','.+','\\%'\\)\\s+", " ");
            sql.append("\nwhere ").append(queryFilter);
        }

        paramList.add(staffFse.getString("leader_tricode"));
        Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);
        if (pageSize == null) {
            pageSize = Integer.MAX_VALUE;
        }
        Integer cpage = fse.getInteger(CmnConst.CPAGE);
        if (cpage == null) {
            cpage = 1;
        }
        DataTableEntity tempDataDte = getBaseDao().listTable(sql.toString(), paramList.toArray(), pageSize, cpage);
        if (BaseUtil.dataTableIsEmpty(tempDataDte)) {
            return new DataTableEntity();
        }
        spDealDte(tempDataDte);

        return tempDataDte;
    }

    /**
     * 进度汇报-查询
     *
     * @param fse
     * @return
     */
    @Override
    public FieldSetEntity findProgressReport(FieldSetEntity fse) {
        String operateType = fse.getString("operate_type");

        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        FieldSetEntity curStaffFse = curUser.getCurrentStaff();
        FieldSetEntity leaderStaffFse = null;
        if (curStaffFse != null) {
            leaderStaffFse = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_STAFFS, "tricode=?", new Object[]{curStaffFse.getString("direct_leader_code")}, false);
        }

        String uuid = fse.getUUID();
        String taskSubUUID;
        String progressUUID;

        FieldSetEntity resultFse;
        StringBuilder sql = new StringBuilder(256);
        if ("2".equals(operateType)) {
            // 评分人查询详情
            progressUUID = uuid;
            sql.append("\nselect b.compare_type,p.uuid,ts.uuid task_sub_uuid,tm.target_task_name task_name,case when b.is_percentage=1 then concat(ts.target_value,'%') else ts.target_value end target_total_value,tm.task_desc,p.accomplish_progress,p.accomplish_value,p.accomplish_date,p.scorer,p.examine_grade,p.progress_desc,p.status");
            sql.append("\nfrom product_oa_target_task_progress p");
            sql.append("\ninner join product_oa_target_task_sub ts on p.task_sub_uuid=ts.uuid");
            sql.append("\ninner join product_oa_target_task tm on ts.task_uuid=tm.uuid");
            sql.append("\ninner join product_oa_target_basic b on tm.target_uuid=b.uuid");
            sql.append("\nwhere p.uuid=?");
            resultFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{progressUUID}, false);
            taskSubUUID = resultFse.getUUID();
        } else {
            sql.append("\nselect b.compare_type,ts.uuid,tm.target_task_name,ts.target_value,ts.datetime_range,b.source_table,b.org_value_field,b.datetime_value_field,tm.task_responsible_type,");
            sql.append("\n    ts.organization_type,ts.task_responsible,tm.datetime_range_type,b.expression_statement,b.target_name,b.filter_statement,b.is_percentage");
            sql.append("\nfrom (");
            sql.append("\n    select uuid,target_value,task_responsible,datetime_range,task_uuid,organization_type,");
            sql.append("\n        case when organization_type=4 then task_responsible else null end user_id,");
            sql.append("\n        case when organization_type in (1,2) then task_responsible else null end task_responsible_org_level_uuid");
            sql.append("\n    from product_oa_target_task_sub");
            sql.append("\n    where target_value>0 and uuid=?");
            sql.append("\n) ts");
            sql.append("\nleft join product_oa_target_task tm on ts.task_uuid=tm.uuid");
            sql.append("\nleft join product_oa_target_basic b on tm.target_uuid=b.uuid");
            FieldSetEntity tempFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{uuid}, false);

            double accomplishValue = getActualTargetValue(tempFse);
            tempFse.setValue(CmnConst.PARAM_VALUE, accomplishValue);
            double progressValue = getProgressValueByTargetValue(tempFse);

            sql.setLength(0);
            sql.append("\nselect b.compare_type,ts.uuid task_sub_uuid,tm.target_task_name task_name,case when b.is_percentage=1 then concat(ts.target_value,'%') else ts.target_value end target_total_value,tm.task_desc");
            sql.append("\nfrom product_oa_target_task_sub ts");
            sql.append("\ninner join product_oa_target_task tm on ts.task_uuid=tm.uuid");
            sql.append("\ninner join product_oa_target_basic b on tm.target_uuid=b.uuid");
            sql.append("\nwhere ts.uuid=?");
            resultFse = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{uuid}, false);

            resultFse.setValue("accomplish_progress", spDealProgressValue(progressValue));
            resultFse.setValue("accomplish_value", (int) accomplishValue);
            resultFse.setValue("accomplish_date", DateUtils.getDate("yyyy-MM-dd HH:mm"));
            resultFse.setValue("scorer", leaderStaffFse == null ? "" : leaderStaffFse.getString(CmnConst.USER_ID));
            resultFse.setValue("operate_type", StringUtils.isEmpty(operateType) ? 0 : 1);

            taskSubUUID = uuid;
        }

        DataTableEntity subDte = getBaseDao().listTable(CmnConst.PRODUCT_OA_TARGET_TASK_PROGRESS, "task_sub_uuid=? and status=2", new Object[]{taskSubUUID}, new Object[]{"accomplish_value", "accomplish_progress", "accomplish_date", "progress_desc"});
        Map<String, DataTableEntity> subMap = Maps.newHashMap();
        subMap.put("history_progress", subDte);
        resultFse.setSubData(subMap);

        return resultFse;
    }

    /**
     * 进度填报-列表
     *
     * @param fse
     * @return
     */
    @Override
    public DataTableEntity listProgressReport(FieldSetEntity fse) {
        int pageSize = fse.getInteger(CmnConst.PAGESIZE) == null ? Integer.MAX_VALUE : fse.getInteger(CmnConst.PAGESIZE);
        int curPage = fse.getInteger(CmnConst.CPAGE) == null ? 1 : fse.getInteger(CmnConst.CPAGE);
        String queryFilter = queryFilterService.getQueryFilter(fse);
        if (!StringUtils.isEmpty(fse.getString("filter"))) {
            String filter = fse.getString("filter");
            if (!StringUtils.isEmpty(queryFilter)) {
                queryFilter += " and ";
            }
            queryFilter += " (" + filter + " ) ";
        }
        if (StringUtils.isEmpty(queryFilter)) {
            queryFilter += "task_sub_uuid=?";
        }
        DataTableEntity dte = getBaseDao().listTable(fse.getTableName(), queryFilter, new Object[]{fse.getUUID()}, null, "created_utc_datetime desc", pageSize, curPage);
        getBaseDao().loadPromptData(dte);
        return dte;
    }

    /**
     * 进度填报-送办-保存（发送给评分人）
     *
     * @param fse
     * @return
     */
    @Override
    public String saveProgressSend(FieldSetEntity fse) {
        String operateType = fse.getString("operate_type");
        if ("1".equals(operateType)) {
            // 消息撤回 todo
        }

        // 保存数据
        BaseUtil.createCreatorAndCreationTime(fse);
        fse.setValue(CmnConst.STATUS, 1);
        getBaseDao().saveFieldSetEntity(fse);

        // 发消息
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        String content = fse.getString("progress_desc");
        String title = "【进度评分】" + fse.getString("task_name");
        WebsocketMesssageServiceThread.getInstance().appendMessage(fse.getString(CmnConst.SCORER), content, title, curUser.getUser_id(), CmnConst.MESSAGE_TYPE_APPROVE,
                CmnConst.MESSAGE_TURN_URL_INFO + "?uuid=" + fse.getUUID() + "&operate_type=2", CmnConst.PRODUCT_OA_TARGET_TASK_PROGRESS, fse.getUUID(), curUser.getUser_id(), 0, 0);
        return fse.getUUID();
    }

    /**
     * 进度填报-评分-保存（发送给填报人）
     *
     * @param fse
     * @return
     */
    @Override
    @Transactional
    public String saveProgressScore(FieldSetEntity fse) {
        // 保存数据
        fse.setValue(CmnConst.STATUS, 2);
        getBaseDao().saveFieldSetEntity(fse);

        // 发消息
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        String content = fse.getString("progress_desc");
        String title = "【进度评分】" + fse.getString("task_name") + "已完成评分";
        WebsocketMesssageServiceThread.getInstance().appendMessage(fse.getString(CmnConst.CREATED_BY), content, title, curUser.getUser_id(), CmnConst.MESSAGE_TYPE_APPROVE,
                CmnConst.MESSAGE_TURN_URL_INFO + "?uuid=" + fse.getUUID(), CmnConst.PRODUCT_OA_TARGET_TASK_PROGRESS, fse.getUUID(), curUser.getUser_id(), 0, 0);
        return fse.getUUID();
    }

    /**
     * 获取指标信息
     *
     * @return
     */
    @Override
    public JSONObject getTargetInfo(FieldSetEntity fse) {
        StringBuilder sql = new StringBuilder(256);
        String filter;
        DataTableEntity resultDte;
        String uuid = fse.getUUID();
        JSONObject resultObj = new JSONObject();
        FieldSetEntity tempFse;
        resultObj.put("warn_target", new JSONArray());
        if (!StringUtils.isEmpty(uuid)) {
            // 预警指标
            resultDte = getBaseDao().listTable(CmnConst.PRODUCT_OA_TARGET_TASK, "target_uuid=?", new Object[]{uuid});
            JSONArray resultArray = resultObj.getJSONArray("warn_target");
            JSONObject jsonObject;
            for (int i = 0; i < resultDte.getRows(); i++) {
                tempFse = resultDte.getFieldSetEntity(i);
                jsonObject = new JSONObject();
                jsonObject.put(CmnConst.TARGET_TASK_NAME, tempFse.getString(CmnConst.TARGET_TASK_NAME));
                jsonObject.put("target_uuid", tempFse.getUUID());
                jsonObject.put("no_date", StringUtils.isEmpty(fse.getString(CmnConst.DATETIME_RANGE)) ? 1 : 0);
                jsonObject.put("target_type", 2);
                resultArray.add(jsonObject);
            }
        } else {
            // 周期、非周期指标
            String type = fse.getString("type");
            if ("0".equals(type)) {
                filter = "b.target_cycle is null";
            } else if ("1".equals(type)) {
                filter = "b.target_cycle is not null";
            } else if ("0,1".equals(type) || "1,0".equals(type)) {
                filter = "";
            } else {
                throw new BaseException(SystemCode.ERROR_TARGET_REPORT_SET_TYPE.getValue(), SystemCode.ERROR_TARGET_REPORT_SET_TYPE.getText());
            }

            sql.append("\nselect case when b.target_cycle is null then 0 else 1 end target_type,b.uuid,t.classification_name,b.target_name,b.datetime_value_field");
            sql.append("\nfrom product_oa_target_basic b");
            sql.append("\ninner join product_oa_target_classification t on b.target_classification=t.uuid");
            if (!StringUtils.isEmpty(filter)) {
                sql.append("\nwhere ").append(filter);
            }
            sql.append("\norder by t.classification_name");

            resultDte = getBaseDao().listTable(sql.toString(), new Object[]{});
            JSONArray jsonArray = new JSONArray();
            resultObj.put("first_target", jsonArray);
            String typeName = "";
            JSONObject jsonObject;
            JSONArray subArray = new JSONArray();
            JSONObject subObject;
            for (int i = 0; i < resultDte.getRows(); i++) {
                tempFse = resultDte.getFieldSetEntity(i);
                if (!typeName.equals(tempFse.getString("classification_name"))) {
                    typeName = tempFse.getString("classification_name");
                    subArray = new JSONArray();
                    jsonObject = new JSONObject();
                    jsonArray.add(jsonObject);
                    jsonObject.put("target_name", typeName);
                    jsonObject.put("children", subArray);
                    jsonObject.put("type", 1);
                }
                subObject = new JSONObject();
                subObject.put("target_uuid", tempFse.getUUID());
                subObject.put("no_date", StringUtils.isEmpty(fse.getString(CmnConst.DATETIME_VALUE_FIELD)) ? 1 : 0);
                subObject.put("target_type", tempFse.getString("target_type"));
                subObject.put("target_name", tempFse.getString("target_name"));
                subArray.add(subObject);
            }
        }
        return resultObj;
    }

    /**
     * 指标报表配置-保存
     *
     * @param fse
     * @return
     */
    @Transactional
    @Override
    public String saveTargetReportConfig(FieldSetEntity fse) {
        BaseUtil.createCreatorAndCreationTime(fse);
        DataTableEntity subDte = fse.getSubDataTable(CmnConst.PRODUCT_OA_TARGET_REPORT_CONFIG_SUB);

        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        BaseUtil.addOrgLeveLUUID(subDte, curUser.getOrg_level_uuid(), curUser);

        getBaseDao().saveFieldSetEntity(fse);

        // 创建对应的mvc
        createReportMvc(fse);

        updateLoginUserInfoService.updateUserInfoByUpdateRole(fse.getString(CmnConst.ROLE_UUIDS).split(","), true);
        return fse.getUUID();
    }

    /**
     * 创建报表mvc
     *
     * @param targetConfigFse
     */
    public void createReportMvc(FieldSetEntity targetConfigFse) {
        BaseDao baseDao = getBaseDao();
        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        String functionUUID = targetConfigFse.getString(CmnConst.FUNCTION_UUID);

        // 1.创建MVC(系统MVC目录下)
        // 生成功能编码
        String functionTricode = codeService.createCode(CmnConst.PRODUCT_SYS_FUNCTIONS, CmnConst.TRICODE, CmnConst.TARGET_TRICODE);

        // 创建MVC保存对象
        FieldSetEntity buttonFse;
        if (StringUtils.isEmpty(functionUUID)) {
            FieldSetEntity functionFse = new FieldSetEntity();
            functionFse.setTableName(CmnConst.PRODUCT_SYS_FUNCTIONS);
            BaseUtil.createCreatorAndCreationTime(curUser, functionFse);
            functionFse.setValue(CmnConst.TRICODE, functionTricode);// 功能编码
            functionFse.setValue(CmnConst.TRICODE_PARENT, CmnConst.TARGET_TRICODE);
            functionFse.setValue(CmnConst.MODULE_UUID, CmnConst.TARGET_MODULE_UUID);// 所属模块UUID
            functionFse.setValue(CmnConst.TABLE_UUID, CmnConst.PRODUCT_OA_TARGET_REPORT_CONFIG);// 功能关联表
            functionFse.setValue(CmnConst.FUNCTION_NAME, targetConfigFse.getString(CmnConst.REPORT_NAME));// 功能名称
            functionFse.setValue(CmnConst.FUNCTION_DESCRIPTION, targetConfigFse.getString(CmnConst.REPORT_NAME));// 功能描述
            functionFse.setValue(CmnConst.STATUS_UUID, 1);    // 是否启用
            functionFse.setValue(CmnConst.FUNCTION_TYPE_UUID, 1);// 功能类型-业务功能
            functionFse.setValue(CmnConst.CLIENT_TYPE_UUID, "Web");// 客户端类型
            functionFse.setValue(CmnConst.VERSION_UUID, "001");
            functionFse.setValue(CmnConst.DATA_TYPE, 1);

            // 创建按钮保存对象
            DataTableEntity buttonDte = new DataTableEntity();
            buttonFse = new FieldSetEntity();
            buttonFse.setTableName(CmnConst.PRODUCT_SYS_FUNCTION_BUTTONS);
            buttonFse.setValue(CmnConst.IS_MAIN, 1);// 是否入口
            buttonFse.setValue(CmnConst.STATUS_UUID, 1);// 是否启用
            buttonFse.setValue(CmnConst.CLIENT_TYPE_UUID, "Web");// 客户端类型
            buttonFse.setValue(CmnConst.BUTTON_NAME, "入口");// 按钮名称
            buttonFse.setValue(CmnConst.BUTTON_TITLE, "entrance");// 按钮标题
            buttonFse.setValue(CmnConst.BUTTON_TYPE, 1);// 按钮类型
            buttonFse.setValue(CmnConst.BUTTON_CATEGORY_UUID, "main");// 按钮分类
            buttonFse.setValue(CmnConst.ROUTE_NAME, BaseUtil.getPageCode());// 路由名称
            buttonFse.setValue(CmnConst.UPLOAD_API_URL, CmnConst.ADDRESS_INIT_INTERFENCE);// 入口接口地址
            JSONObject paramObj = new JSONObject();
            paramObj.put(CmnConst.UUID, targetConfigFse.getUUID());
            paramObj.put(CmnConst.DIMENSION, StringUtils.isEmpty(targetConfigFse.getString(CmnConst.DIMENSION)) ? 4 : targetConfigFse.getInteger(CmnConst.DIMENSION));
            buttonFse.setValue(CmnConst.PARAMS, paramObj.toString());// 接口参数
            buttonFse.setValue(CmnConst.TOP_LOCATION, "100px");
            buttonFse.setValue(CmnConst.LEFT_LOCATION, "100px");
            buttonFse.setValue("terminal_type", 1);
            buttonDte.setMeta(buttonFse.getMeta());
            buttonDte.addFieldSetEntity(buttonFse);

            //创建页面保存对象
            DataTableEntity pageDte = new DataTableEntity();
            FieldSetEntity fsePage = new FieldSetEntity();
            fsePage.setTableName(CmnConst.PRODUCT_SYS_MVC_PAGE);
//            fsePage.setValue(CmnConst.PAGE_NAME, "报表展示");
            fsePage.setValue(CmnConst.PAGE_NAME, targetConfigFse.getString(CmnConst.REPORT_NAME));

            fsePage.setValue(CmnConst.PAGE_TYPE, 2);
            fsePage.setValue(CmnConst.PAGE_URL, CmnConst.ADDRESS_REPORT_SHOW);
            fsePage.setValue(CmnConst.TOP_LOCATION, "300px");
            fsePage.setValue(CmnConst.LEFT_LOCATION, "100px");
            fsePage.setValue(CmnConst.PAGE_OPEN_WITH, 0);
            fsePage.setValue("terminal_type", 1);
            pageDte.addFieldSetEntity(fsePage);

            functionFse.addSubDataTable(buttonDte);
            functionFse.addSubDataTable(pageDte);

            functionUUID = baseDao.add(functionFse);

            // 创建连线保存对象
            FieldSetEntity fseLink = new FieldSetEntity();
            fseLink.setTableName(CmnConst.PRODUCT_SYS_LINK);
            fseLink.setValue(CmnConst.LINK_TYPE, 0);// 连线类型
            fseLink.setValue(CmnConst.FUNCTION_UUID, functionUUID);// 所属功能UUID
            fseLink.setValue(CmnConst.LINE_FROM, buttonFse.getUUID());// 起始端
            fseLink.setValue(CmnConst.FROM_TYPE, 0);// 起始端类型
            fseLink.setValue(CmnConst.LINE_TO, fsePage.getUUID());// 结束端
            fseLink.setValue(CmnConst.TO_TYPE, 2);// 结束端类型
            fseLink.setValue("terminal_type", 1);
            BaseUtil.createCreatorAndCreationTime(SpringMVCContextHolder.getCurrentUser(), fseLink);

            baseDao.add(fseLink);

            // 4.创建菜单(根据前端所选父级菜单)
            FieldSetEntity menuFse = new FieldSetEntity();
            menuFse.setTableName(CmnConst.PRODUCT_SYS_MENUS);
            menuFse.setValue(CmnConst.TRICODE, codeService.createCode(CmnConst.PRODUCT_SYS_MENUS, CmnConst.TRICODE, targetConfigFse.getString(CmnConst.MENU_UUID)));
            menuFse.setValue(CmnConst.TRICODE_PARENT, targetConfigFse.getString(CmnConst.TRICODE_PARENT));
            menuFse.setValue(CmnConst.MENU_NAME, targetConfigFse.getString(CmnConst.REPORT_NAME));
            menuFse.setValue(CmnConst.FUNCTION_UUID, functionUUID);
            menuFse.setValue(CmnConst.SEQUENCE, 0);
            menuFse.setValue(CmnConst.IS_CATALOG, 0);
            menuFse.setValue(CmnConst.IS_SHOW, 1);
            menuFse.setValue(CmnConst.MENU_ICON, CmnConst.VALUE_MENU_ICON);
            menuFse.setValue(CmnConst.TRICODE_PARENT, targetConfigFse.getString(CmnConst.MENU_UUID));
            BaseUtil.createCreatorAndCreationTime(SpringMVCContextHolder.getCurrentUser(), menuFse);
            baseDao.add(menuFse);

            //数据回写
            targetConfigFse.setValue(CmnConst.FUNCTION_UUID, functionUUID);
            baseDao.saveFieldSetEntity(targetConfigFse);
        } else {
            // 更新mvc名称
            FieldSetEntity functionFse = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_FUNCTIONS, functionUUID, false);
            functionFse.setValue(CmnConst.FUNCTION_NAME, targetConfigFse.getString(CmnConst.REPORT_NAME));// 功能名称
            functionFse.setValue(CmnConst.FUNCTION_DESCRIPTION, targetConfigFse.getString(CmnConst.REPORT_NAME));// 功能描述
            baseDao.saveFieldSetEntity(functionFse);

            buttonFse = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_FUNCTION_BUTTONS, "function_uuid=? and is_main=1", new Object[]{functionUUID}, false);

            // 更新菜单名称
            FieldSetEntity menuFse = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_MENUS, "tricode_parent=? and function_uuid=?", new Object[]{targetConfigFse.getString(CmnConst.MENU_UUID), functionUUID}, false);
            menuFse.setValue(CmnConst.MENU_NAME, targetConfigFse.getString(CmnConst.REPORT_NAME));
            BaseUtil.updatedRegeneratorAndUpdateTime(curUser, menuFse);
            baseDao.saveFieldSetEntity(menuFse);
        }


        // 提取发布的角色相关数据
        List<String> roleUUIDList = Lists.newArrayList();
        List<String> clientUUIDList = Lists.newArrayList();
        for (String singleRoleInfo : targetConfigFse.getString(CmnConst.ROLE_UUIDS).split(",")) {
            roleUUIDList.add(singleRoleInfo);
            clientUUIDList.add(singleRoleInfo);
        }

        // 从角色权限中移除
        baseDao.delete(CmnConst.PRODUCT_SYS_FUNCTION_PERMISSION, "function_uuid=?", new Object[]{functionUUID});
        // 2.绑定角色(给某一角色赋予该功能)
        for (String roleUUID : roleUUIDList) {
            FieldSetEntity fseFunctionButton = new FieldSetEntity();
            fseFunctionButton.setTableName(CmnConst.PRODUCT_SYS_FUNCTION_PERMISSION);
            fseFunctionButton.setValue(CmnConst.FUNCTION_UUID, functionUUID);// 所属功能UUID
            fseFunctionButton.setValue(CmnConst.BUTTON_UUID, buttonFse.getUUID());// 按钮UUID
            fseFunctionButton.setValue(CmnConst.ROLE_UUID, roleUUID);// 所属角色UUID
            baseDao.add(fseFunctionButton);
        }

        // 3.绑定客户角色(给某一客户角色赋予该功能)
        for (String clientUUID : clientUUIDList) {
            DataTableEntity clientDte = DataPoolCacheImpl.getInstance().getCacheData("客户信息", new String[]{clientUUID});
            if (BaseUtil.dataTableIsEmpty(clientDte)) {
                continue;
            }
            FieldSetEntity clientFse = clientDte.getFieldSetEntity(0);
            FieldSetEntity fseClientFunctionButton = new FieldSetEntity();
            fseClientFunctionButton.setTableName(CmnConst.PRODUCT_SYS_FUNCTION_PERMISSION);
            fseClientFunctionButton.setValue(CmnConst.FUNCTION_UUID, functionUUID);// 所属功能UUID
            fseClientFunctionButton.setValue(CmnConst.BUTTON_UUID, buttonFse.getUUID());// 按钮UUID
            fseClientFunctionButton.setValue(CmnConst.ROLE_UUID, clientFse.getString(CmnConst.ROLE_UUID));// 所属角色UUID
            baseDao.add(fseClientFunctionButton);
        }
    }

    /**
     * 删除指标报表配置
     *
     * @param fse
     */
    @Override
    @Transactional
    public void delReportConfig(FieldSetEntity fse) {
        BaseDao baseDao = getBaseDao();
        DataTableEntity targetReportConfigDte = baseDao.listTable(CmnConst.PRODUCT_OA_TARGET_REPORT_CONFIG, new Object[]{CmnConst.FUNCTION_UUID}, fse.getUUID().split(","));
        Object[] functionUUIDArr = targetReportConfigDte.getFieldAllValues(CmnConst.FUNCTION_UUID);
        String filter = BaseUtil.buildQuestionMarkFilter("function_uuid", functionUUIDArr.length, true);
        // 从角色权限中移除
        baseDao.delete(CmnConst.PRODUCT_SYS_FUNCTION_PERMISSION, filter, functionUUIDArr);
        // 删除连线
        baseDao.delete(CmnConst.PRODUCT_SYS_LINK, filter, functionUUIDArr);
        // 删除按钮
        baseDao.delete(CmnConst.PRODUCT_SYS_FUNCTION_BUTTONS, filter, functionUUIDArr);
        // 删除页面
        baseDao.delete(CmnConst.PRODUCT_SYS_MVC_PAGE, filter, functionUUIDArr);
        // 删除菜单
        baseDao.delete(CmnConst.PRODUCT_SYS_MENUS, filter, functionUUIDArr);
        // 删除功能
        filter = BaseUtil.buildQuestionMarkFilter("uuid", functionUUIDArr.length, true);
        baseDao.delete(CmnConst.PRODUCT_SYS_FUNCTIONS, filter, functionUUIDArr);
        // 指标报表配置表数据删除
        baseDao.delete(CmnConst.PRODUCT_OA_TARGET_REPORT_CONFIG, fse.getUUID().split(","));

        DataPoolRefreshCache.getInstance().put("product_sys_page_button_v");

        baseDao.update(fse);
    }

    /**
     * 指标报表配置-查询
     *
     * @param fse
     * @return
     * @throws BaseException
     */
    @Override
    public FieldSetEntity findReportConfig(FieldSetEntity fse) throws BaseException {
        FieldSetEntity f = getBaseDao().getFieldSetEntity(fse.getTableName(), fse.getUUID(), false);
        if (f != null && !StringUtils.isEmpty(f.getUUID())) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT ");
            sql.append(" a.target_uuid, ");
            sql.append(" main_uuid, ");
            sql.append(" a.uuid,a.target_type, ");
            sql.append(" ifnull(b.target_task_name,c.target_name) target_name ");
            sql.append(" FROM ");
            sql.append(" product_oa_target_report_config_sub a ");
            sql.append(" LEFT JOIN product_oa_target_task b ON a.target_uuid = b.uuid ");
            sql.append(" LEFT JOIN product_oa_target_basic c ON a.target_uuid =c.uuid ");
            sql.append(" WHERE ");
            sql.append(" main_uuid =? ");

            DataTableEntity subDataTable = getBaseDao().listTable(sql.toString(), new Object[]{f.getUUID()});
            f.addSubDataTable(subDataTable);
        }
        return f;
    }

    /**
     * 指标责任主体-保存
     *
     * @param fse
     * @return
     */
    @Transactional
    @Override
    public String saveTargetResponsible(FieldSetEntity fse) {
        BaseUtil.createCreatorAndCreationTime(fse);
        DataTableEntity subDte = fse.getSubDataTable(CmnConst.PRODUCT_OA_TARGET_RESPONSIBLE_RANGE_SUB);

        SystemUser curUser = SpringMVCContextHolder.getCurrentUser();
        BaseUtil.addOrgLeveLUUID(subDte, curUser.getOrg_level_uuid(), curUser);

        getBaseDao().saveFieldSetEntity(fse);
        return fse.getUUID();
    }

    /**
     * 指标报表展示-获取html
     *
     * @param fse
     * @return
     */
    @Override
    public JSONObject getTargetReportHtml(FieldSetEntity fse) {
        BaseDao baseDao = getBaseDao();
        List<FieldSetEntity> colList = Lists.newArrayList();// 列fse

        // 默认展示的指标行
        FieldSetEntity targetReportConfigFse = baseDao.getFieldSetEntity(CmnConst.PRODUCT_OA_TARGET_REPORT_CONFIG, fse.getUUID(), true);
        StringBuilder sql = new StringBuilder(512);
        sql.append("\nselect *");
        sql.append("\nfrom (");
        sql.append("\n    select t2.type,t2.responsible,ifnull(t2.corp_name,c.org_level_name) corp_name,ifnull(t2.dept_name,d.org_level_name) dept_name,t2.user_name");
        sql.append("\n    from (");
        sql.append("\n        select t1.type,t1.responsible,ifnull(t1.corp_name,c.org_level_name) corp_name,ifnull(t1.dept_name,d.org_level_name) dept_name,t1.user_name,t1.dept_uuid");
        sql.append("\n        from (");
        sql.append("\n            select rs.type,rs.responsible,");
        sql.append("\n                case when type=1 then o.org_level_name else null end corp_name,");
        sql.append("\n                case when type=2 then rs.responsible else null end dept_uuid,case when type=2 then o.org_level_name else null end dept_name,");
        sql.append("\n                case when type=4 then rs.responsible else null end user_id,case when type=4 then u.user_name else null end user_name");
        sql.append("\n            from (");
        sql.append("\n                select responsible,type from product_oa_target_responsible_range_sub");
        sql.append("\n                where main_uuid=?");
        sql.append("\n            ) rs");
        sql.append("\n            left join product_sys_org_levels o on o.uuid=rs.responsible");
        sql.append("\n            left join product_sys_users u on u.user_id=rs.responsible");
        sql.append("\n        ) t1");
        sql.append("\n        left join product_sys_staffs s on s.user_id=t1.user_id");
        sql.append("\n        left join product_sys_org_levels c on c.uuid=s.org_level_uuid");
        sql.append("\n        left join product_sys_org_levels d on d.uuid=s.dept_uuid");
        sql.append("\n    ) t2");
        sql.append("\n    left join product_sys_org_levels d on d.uuid=t2.dept_uuid");
        sql.append("\n    left join product_sys_org_levels c on c.uuid=d.org_level_uuid");
        sql.append("\n) t3");
        sql.append("\norder by corp_name,dept_name,user_name");
        DataTableEntity rowDte = baseDao.listTable(sql.toString(), new Object[]{targetReportConfigFse.getString(CmnConst.RESPONSIBLE_RANGE_UUID)});

        // 列-周期、非周期指标
        sql.setLength(0);
        sql.append("\nselect b.compare_type,b.target_name,b.expression_statement,cs.target_type,b.target_cycle,b.source_table,b.org_value_field,b.datetime_value_field,b.is_percentage,cs.statistics_type,b.function_uuid,b.button_uuid,b.filter_statement");
        sql.append("\nfrom (");
        sql.append("\n    select target_uuid,target_type,statistics_type from product_oa_target_report_config_sub");
        sql.append("\n    where main_uuid=?");
        sql.append("\n    and target_type in (0,1)");
        sql.append("\n) cs");
        sql.append("\ninner join product_oa_target_basic b on cs.target_uuid=b.uuid");
        DataTableEntity tempDte = baseDao.listTable(sql.toString(), new Object[]{fse.getUUID()});
        FieldSetEntity tempFse;
        for (int i = 0; i < tempDte.getRows(); i++) {
            tempFse = tempDte.getFieldSetEntity(i);
            tempFse.setValue(CmnConst.PARAM_TURN, routerService.functionSkipByButtonUuid(tempFse.getString(CmnConst.FUNCTION_UUID), tempFse.getString(CmnConst.BUTTON_UUID)));
            colList.add(tempFse);
        }

        // 列-预警指标
        sql.setLength(0);
        sql.append("\nselect b.compare_type,tm.uuid,tm.target_task_name,cs.target_type,b.is_percentage,cs.statistics_type,b.function_uuid,b.button_uuid,b.source_table,b.org_value_field,b.datetime_value_field,tm.datetime_range_type,tm.datetime_range,b.target_name,b.filter_statement");
        sql.append("\nfrom (");
        sql.append("\n    select target_uuid,target_type,statistics_type from product_oa_target_report_config_sub");
        sql.append("\n    where main_uuid=?");
        sql.append("\n    and target_type=2");
        sql.append("\n) cs");
        sql.append("\ninner join product_oa_target_task tm on cs.target_uuid=tm.uuid");
        sql.append("\ninner join product_oa_target_basic b on tm.target_uuid=b.uuid");
        tempDte = baseDao.listTable(sql.toString(), new Object[]{fse.getUUID()});
        for (int i = 0; i < tempDte.getRows(); i++) {
            tempFse = tempDte.getFieldSetEntity(i);
            tempFse.setValue(CmnConst.PARAM_TURN, routerService.functionSkipByButtonUuid(tempFse.getString(CmnConst.FUNCTION_UUID), tempFse.getString(CmnConst.BUTTON_UUID)));
            colList.add(tempFse);
        }
        DataTableEntity taskSubInfoDte;
        if (!DataTableEntity.isEmpty(tempDte)) {
            sql.setLength(0);
            sql.append("\nselect b.compare_type,tm.uuid,tm.target_task_name,b.expression_statement,ts.organization_type,ts.task_responsible,tm.datetime_range_type,ts.datetime_range,ts.target_value,b.source_table,b.org_value_field,b.datetime_value_field,b.is_percentage,b.target_name,b.filter_statement");
            sql.append("\nfrom product_oa_target_task tm");
            sql.append("\ninner join product_oa_target_task_sub ts on tm.uuid=ts.task_uuid");
            sql.append("\ninner join product_oa_target_basic b on tm.target_uuid=b.uuid");
            sql.append("\nwhere ").append(BaseUtil.buildQuestionMarkFilter("tm.uuid", tempDte.getRows(), true));
            taskSubInfoDte = baseDao.listTable(sql.toString(), tempDte.getFieldAllValues("uuid"));
        } else {
            taskSubInfoDte = new DataTableEntity();
        }
        FieldSetEntity paramFse;
        Map<JSONObject, List<JSONObject>> tableValueContainer = Maps.newLinkedHashMap();// 容器-总表格
        List<JSONObject> rowValueContainer;// 容器-行
        JSONObject rowKeyObj;
        JSONObject valueObj;
        double targetValue;
        double progressValue;
        for (int i = 0; i < rowDte.getRows(); i++) {
            tempFse = rowDte.getFieldSetEntity(i);
            rowKeyObj = new JSONObject();
            rowKeyObj.put(CmnConst.PARAM_CORP_NAME, tempFse.getString(CmnConst.PARAM_CORP_NAME));
            rowKeyObj.put(CmnConst.RESPONSIBLE, tempFse.getString(CmnConst.RESPONSIBLE));
            if (tempFse.getString(CmnConst.PARAM_DEPT_NAME) != null) {
                rowKeyObj.put(CmnConst.PARAM_DEPT_NAME, tempFse.getString(CmnConst.PARAM_DEPT_NAME));
            }
            if (tempFse.getString(CmnConst.PARAM_USER_NAME) != null) {
                rowKeyObj.put(CmnConst.PARAM_USER_NAME, tempFse.getString(CmnConst.PARAM_USER_NAME));
            }

            // 过滤判定
            if (checkNeedResponsibleFilter(fse, rowKeyObj)) {
                rowValueContainer = Lists.newArrayList();
                tableValueContainer.put(rowKeyObj, rowValueContainer);
                for (FieldSetEntity colFse : colList) {
                    valueObj = new JSONObject();
                    valueObj.put(CmnConst.PARAM_ROW, i + 1);
                    valueObj.put(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, tempFse.getString(CmnConst.TYPE));
                    valueObj.put(CmnConst.PARAM_TURN, colFse.getString(CmnConst.PARAM_TURN));
                    if ("0".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                        // 非周期，只有传入过滤条件的时候才需要计算值
                        valueObj.put(CmnConst.PARAM_COL_TYPE, CmnConst.VALUE_COL_TYPE_ACYCLIC);
                        if (!StringUtils.isEmpty(fse.getString(CmnConst.DATETIME_RANGE))) {
                            paramFse = new FieldSetEntity();
                            paramFse.setTableName("temp_table");
                            paramFse.setValue(CmnConst.SOURCE_TABLE, colFse.getString(CmnConst.SOURCE_TABLE));
                            paramFse.setValue(CmnConst.ORG_VALUE_FIELD, colFse.getString(CmnConst.ORG_VALUE_FIELD));
                            paramFse.setValue(CmnConst.TASK_RESPONSIBLE, tempFse.getString(CmnConst.RESPONSIBLE));
                            paramFse.setValue(CmnConst.DATETIME_VALUE_FIELD, colFse.getString(CmnConst.DATETIME_VALUE_FIELD));
                            paramFse.setValue(CmnConst.DATETIME_RANGE_TYPE, 1);
                            paramFse.setValue(CmnConst.DATETIME_RANGE, fse.getString(CmnConst.DATETIME_RANGE));
                            paramFse.setValue(CmnConst.EXPRESSION_STATEMENT, colFse.getString(CmnConst.EXPRESSION_STATEMENT));
                            paramFse.setValue(CmnConst.FILTER_STATEMENT, colFse.getString(CmnConst.FILTER_STATEMENT));
                            paramFse.setValue(CmnConst.TARGET_VALUE, colFse.getString(CmnConst.TARGET_VALUE));
                            paramFse.setValue(CmnConst.IS_PERCENTAGE, colFse.getString(CmnConst.IS_PERCENTAGE));
                            paramFse.setValue(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, tempFse.getString(CmnConst.TYPE));
                            paramFse.setValue(CmnConst.TARGET_NAME, colFse.getString(CmnConst.TARGET_NAME));

                            paramFse.setValue(CmnConst.COMPARE_TYPE, colFse.getString(CmnConst.COMPARE_TYPE));
                            targetValue = getActualTargetValue(paramFse);
                            valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(paramFse.getString(CmnConst.PARAM_SQL)));
                            valueObj.put(CmnConst.PARAM_VALUE, targetValue);
                            valueObj.put(CmnConst.IS_PERCENTAGE, colFse.getString(CmnConst.IS_PERCENTAGE));
                        } else {
                            valueObj.put(CmnConst.PARAM_VALUE, 0);
                        }
                        rowValueContainer.add(valueObj);
                    } else if ("1".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                        // 周期
                        paramFse = new FieldSetEntity();
                        paramFse.setTableName("temp_table");
                        paramFse.setValue(CmnConst.SOURCE_TABLE, colFse.getString(CmnConst.SOURCE_TABLE));
                        paramFse.setValue(CmnConst.ORG_VALUE_FIELD, colFse.getString(CmnConst.ORG_VALUE_FIELD));
                        paramFse.setValue(CmnConst.TASK_RESPONSIBLE, tempFse.getString(CmnConst.RESPONSIBLE));
                        paramFse.setValue(CmnConst.DATETIME_VALUE_FIELD, colFse.getString(CmnConst.DATETIME_VALUE_FIELD));
                        paramFse.setValue(CmnConst.DATETIME_RANGE_TYPE, 4);
                        paramFse.setValue(CmnConst.DATETIME_RANGE, colFse.getString(CmnConst.TARGET_CYCLE));
                        paramFse.setValue(CmnConst.EXPRESSION_STATEMENT, colFse.getString(CmnConst.EXPRESSION_STATEMENT));
                        paramFse.setValue(CmnConst.FILTER_STATEMENT, colFse.getString(CmnConst.FILTER_STATEMENT));
                        paramFse.setValue(CmnConst.TARGET_VALUE, colFse.getString(CmnConst.TARGET_VALUE));
                        paramFse.setValue(CmnConst.IS_PERCENTAGE, colFse.getString(CmnConst.IS_PERCENTAGE));
                        paramFse.setValue(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, tempFse.getString(CmnConst.TYPE));
                        paramFse.setValue(CmnConst.TARGET_NAME, colFse.getString(CmnConst.TARGET_NAME));

                        paramFse.setValue(CmnConst.COMPARE_TYPE, colFse.getString(CmnConst.COMPARE_TYPE));
                        targetValue = getActualTargetValue(paramFse);
                        valueObj.put(CmnConst.IS_PERCENTAGE, colFse.getString(CmnConst.IS_PERCENTAGE));
                        valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(paramFse.getString(CmnConst.PARAM_SQL)));
                        valueObj.put(CmnConst.PARAM_VALUE, targetValue);
                        valueObj.put(CmnConst.PARAM_COL_TYPE, CmnConst.VALUE_COL_TYPE_CYCLE);
                        rowValueContainer.add(valueObj);
                    } else if ("2".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                        // 预警
                        FieldSetEntity taskSubInfoFse = null;
                        for (int k = 0; k < taskSubInfoDte.getRows(); k++) {
                            taskSubInfoFse = taskSubInfoDte.getFieldSetEntity(k);
                            if (taskSubInfoFse.getUUID().equals(colFse.getUUID()) && tempFse.getString(CmnConst.RESPONSIBLE).equals(taskSubInfoFse.getString(CmnConst.TASK_RESPONSIBLE))) {
                                break;
                            }
                            taskSubInfoFse = null;
                        }
                        if (taskSubInfoFse != null) {
                            if (taskSubInfoFse.getString(CmnConst.ORGANIZATION_TYPE).equals(tempFse.getString(CmnConst.TYPE)) && taskSubInfoFse.getString(CmnConst.TASK_RESPONSIBLE).equals(tempFse.getString(CmnConst.RESPONSIBLE))) {
                                targetValue = getActualTargetValue(taskSubInfoFse);
                                taskSubInfoFse.setValue(CmnConst.PARAM_VALUE, targetValue);
                                progressValue = getProgressValueByTargetValue(taskSubInfoFse);

                                if (progressValue < 0.3d) {
                                    valueObj.put(CmnConst.PARAM_CLASS, "warn_1");
                                    valueObj.put(CmnConst.PARAM_COLOR, IndexedColors.RED.getIndex());
                                } else if (progressValue < 0.5d) {
                                    valueObj.put(CmnConst.PARAM_CLASS, "warn_2");
                                    valueObj.put(CmnConst.PARAM_COLOR, IndexedColors.ORANGE.getIndex());
                                } else if (progressValue < 0.7d) {
                                    valueObj.put(CmnConst.PARAM_CLASS, "warn_3");
                                    valueObj.put(CmnConst.PARAM_COLOR, IndexedColors.GOLD.getIndex());
                                } else if (progressValue < 0.9d) {
                                    valueObj.put(CmnConst.PARAM_CLASS, "warn_4");
                                    valueObj.put(CmnConst.PARAM_COLOR, IndexedColors.BLUE.getIndex());
                                } else {
                                    valueObj.put(CmnConst.PARAM_CLASS, "warn_5");
                                    valueObj.put(CmnConst.PARAM_COLOR, IndexedColors.GREEN.getIndex());
                                }
                                valueObj.put(CmnConst.IS_PERCENTAGE, taskSubInfoFse.getString(CmnConst.IS_PERCENTAGE));
                                valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(taskSubInfoFse.getString(CmnConst.PARAM_SQL)));
                                valueObj.put(CmnConst.PARAM_VALUE, targetValue);
                                valueObj.put(CmnConst.PARAM_COL_TYPE, CmnConst.VALUE_COL_TYPE_WARN);
                                valueObj.put(CmnConst.PARAM_PROGRESS_VALUE, progressValue);
                            }
                        }
                        rowValueContainer.add(valueObj);
                    }
                }
            }
        }

        // 根据传入的主体维度特殊处理容器中的值
        tableValueContainer = dealContainerByDimension(fse, tableValueContainer);

        // 添加统计行
        tableValueContainer = addStatisticsRow(tableValueContainer, colList, StringUtils.isEmpty(fse.getString(CmnConst.DIMENSION)) ? 4 : fse.getInteger(CmnConst.DIMENSION), taskSubInfoDte);

        // 添加统计行穿透内容
        addStatisticsRowTurnContent(tableValueContainer, colList, StringUtils.isEmpty(fse.getString(CmnConst.DIMENSION)) ? 4 : fse.getInteger(CmnConst.DIMENSION), fse.getString(CmnConst.DATETIME_RANGE));

        JSONObject resultObj = new JSONObject();
        // 拼凑html
        String reportTitle = targetReportConfigFse.getString(CmnConst.REPORT_NAME);
        resultObj.put("report_title", reportTitle);
        resultObj.put("dimension", fse.getString(CmnConst.DIMENSION) == null ? "4" : fse.getString(CmnConst.DIMENSION));
        if (!"1".equals(fse.getString("export_sign"))) {
            StringBuilder html = getHtml(tableValueContainer, colList, resultObj.getIntValue("dimension"), reportTitle);
            resultObj.put("report_html", html);
            resultObj.put("operate", targetReportConfigFse.getString("operate") == null ? "" : targetReportConfigFse.getString("operate"));
            resultObj.put("responsible", fse.getString(CmnConst.RESPONSIBLE) == null ? "" : fse.getString(CmnConst.RESPONSIBLE));
            resultObj.put("report_empty", tableValueContainer.size() == 0);
            resultObj.put("datetime_range", fse.getString(CmnConst.DATETIME_RANGE) == null ? "" : fse.getString(CmnConst.DATETIME_RANGE));
        } else {
            resultObj.put("colList", colList);
            resultObj.put("result", tableValueContainer);
        }

        return resultObj;
    }

    /**
     * 添加统计行穿透内容
     *
     * @param tableValueContainer
     * @param colList
     * @param dimension
     * @param datetimeRange
     */
    private void addStatisticsRowTurnContent(Map<JSONObject, List<JSONObject>> tableValueContainer, List<FieldSetEntity> colList, int dimension, String datetimeRange) {
        JSONObject keyObj;
        List<JSONObject> valueList;
        JSONObject valueObj;
        FieldSetEntity colFse;
        FieldSetEntity paramFse;
        JSONObject statisticsResponsibleObj;
        List<Object> statisticsResponsibleList;
        JSONObject cloneObj;
        String curName = dimension == 1 ? CmnConst.PARAM_CORP_NAME : (dimension == 2 ? CmnConst.PARAM_DEPT_NAME : CmnConst.PARAM_USER_NAME);
        for (Map.Entry<JSONObject, List<JSONObject>> entry : tableValueContainer.entrySet()) {
            keyObj = entry.getKey();
            statisticsResponsibleObj = keyObj.getJSONObject(CmnConst.PARAM_STATISTICS_RESPONSIBLE);
            if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                valueList = entry.getValue();
                if (valueList == null) {
                    continue;
                }
                for (int i = 0; i < valueList.size(); i++) {
                    valueObj = valueList.get(i);
                    statisticsResponsibleList = Lists.newArrayList();
                    if (!StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_VALUE))) {
                        colFse = colList.get(i);
                        if ("0".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                            // 非周期，只有传入过滤条件的时候才需要计算值
                            if (!StringUtils.isEmpty(datetimeRange)) {
                                if (statisticsResponsibleObj != null) {
                                    for (Map.Entry<String, Object> objEntry : statisticsResponsibleObj.entrySet()) {
                                        statisticsResponsibleList.add(objEntry.getValue());
                                    }
                                }

                                paramFse = new FieldSetEntity();
                                paramFse.setTableName("temp_table");
                                paramFse.setValue(CmnConst.SOURCE_TABLE, colFse.getString(CmnConst.SOURCE_TABLE));
                                paramFse.setValue(CmnConst.ORG_VALUE_FIELD, colFse.getString(CmnConst.ORG_VALUE_FIELD));
                                paramFse.setValue(CmnConst.TASK_RESPONSIBLE, statisticsResponsibleList);
                                paramFse.setValue(CmnConst.DATETIME_VALUE_FIELD, colFse.getString(CmnConst.DATETIME_VALUE_FIELD));
                                paramFse.setValue(CmnConst.DATETIME_RANGE_TYPE, 1);
                                paramFse.setValue(CmnConst.DATETIME_RANGE, datetimeRange);
                                paramFse.setValue(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, dimension);
                                paramFse.setValue(CmnConst.ORGANIZATION_TYPE, 6);
                                paramFse.setValue(CmnConst.EXPRESSION_STATEMENT, "");
                                paramFse.setValue(CmnConst.TARGET_NAME, colFse.getString(CmnConst.TARGET_NAME));
                                paramFse.setValue(CmnConst.FILTER_STATEMENT, colFse.getString(CmnConst.FILTER_STATEMENT));
                                paramFse.setValue(CmnConst.COMPARE_TYPE, colFse.getString(CmnConst.COMPARE_TYPE));

                                getActualTargetValue(paramFse);
                                valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(paramFse.getString(CmnConst.PARAM_SQL)));
                            }
                        } else if ("1".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                            // 周期
                            if (statisticsResponsibleObj != null) {
                                for (Map.Entry<String, Object> objEntry : statisticsResponsibleObj.entrySet()) {
                                    statisticsResponsibleList.add(objEntry.getValue());
                                }
                            }

                            paramFse = new FieldSetEntity();
                            paramFse.setTableName("temp_table");
                            paramFse.setValue(CmnConst.SOURCE_TABLE, colFse.getString(CmnConst.SOURCE_TABLE));
                            paramFse.setValue(CmnConst.ORG_VALUE_FIELD, colFse.getString(CmnConst.ORG_VALUE_FIELD));
                            paramFse.setValue(CmnConst.TASK_RESPONSIBLE, statisticsResponsibleList);
                            paramFse.setValue(CmnConst.DATETIME_VALUE_FIELD, colFse.getString(CmnConst.DATETIME_VALUE_FIELD));
                            paramFse.setValue(CmnConst.DATETIME_RANGE_TYPE, 4);
                            paramFse.setValue(CmnConst.DATETIME_RANGE, colFse.getString(CmnConst.TARGET_CYCLE));
                            paramFse.setValue(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, dimension);
                            paramFse.setValue(CmnConst.ORGANIZATION_TYPE, 6);
                            paramFse.setValue(CmnConst.EXPRESSION_STATEMENT, "");
                            paramFse.setValue(CmnConst.TARGET_NAME, colFse.getString(CmnConst.TARGET_NAME));
                            paramFse.setValue(CmnConst.FILTER_STATEMENT, colFse.getString(CmnConst.FILTER_STATEMENT));
                            paramFse.setValue(CmnConst.COMPARE_TYPE, colFse.getString(CmnConst.COMPARE_TYPE));

                            getActualTargetValue(paramFse);
                            valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(paramFse.getString(CmnConst.PARAM_SQL)));
                        } else if ("2".equals(colFse.getString(CmnConst.TARGET_TYPE))) {
                            // 预警
                            if (statisticsResponsibleObj != null) {
                                for (Map.Entry<String, Object> objEntry : statisticsResponsibleObj.entrySet()) {
                                    cloneObj = (JSONObject) keyObj.clone();
                                    cloneObj.remove(CmnConst.PARAM_STATISTICS_ROW);
                                    cloneObj.remove(CmnConst.PARAM_STATISTICS_RESPONSIBLE);
                                    cloneObj.put(curName, objEntry.getKey());
                                    if (tableValueContainer.get(cloneObj) != null && tableValueContainer.get(cloneObj).get(i) != null && !StringUtils.isEmpty(tableValueContainer.get(cloneObj).get(i).getString(CmnConst.PARAM_VALUE))) {
                                        statisticsResponsibleList.add(objEntry.getValue());
                                    }
                                }
                            }

                            paramFse = new FieldSetEntity();
                            paramFse.setTableName("temp_table");
                            paramFse.setValue(CmnConst.SOURCE_TABLE, colFse.getString(CmnConst.SOURCE_TABLE));
                            paramFse.setValue(CmnConst.ORG_VALUE_FIELD, colFse.getString(CmnConst.ORG_VALUE_FIELD));
                            paramFse.setValue(CmnConst.TASK_RESPONSIBLE, statisticsResponsibleList);
                            paramFse.setValue(CmnConst.DATETIME_VALUE_FIELD, colFse.getString(CmnConst.DATETIME_VALUE_FIELD));
                            paramFse.setValue(CmnConst.DATETIME_RANGE_TYPE, colFse.getString(CmnConst.DATETIME_RANGE_TYPE));
                            paramFse.setValue(CmnConst.DATETIME_RANGE, colFse.getString(CmnConst.DATETIME_RANGE));
                            paramFse.setValue(CmnConst.PARAM_ROW_ORGANIZATION_TYPE, dimension);
                            paramFse.setValue(CmnConst.ORGANIZATION_TYPE, 6);
                            paramFse.setValue(CmnConst.EXPRESSION_STATEMENT, "");
                            paramFse.setValue(CmnConst.TARGET_NAME, colFse.getString(CmnConst.TARGET_NAME));
                            paramFse.setValue(CmnConst.FILTER_STATEMENT, colFse.getString(CmnConst.FILTER_STATEMENT));
                            paramFse.setValue(CmnConst.COMPARE_TYPE, colFse.getString(CmnConst.COMPARE_TYPE));

                            getActualTargetValue(paramFse);
                            valueObj.put(CmnConst.PARAM_FILTER, getSqlFilter(paramFse.getString(CmnConst.PARAM_SQL)));
                        }
                    }
                }
            }
        }
    }

    /**
     * 从sql中提取filter
     *
     * @param sql
     * @return
     */
    private String getSqlFilter(String sql) {
        String keyWordWhere = "where";
        String filter = sql.substring(sql.indexOf(keyWordWhere) + keyWordWhere.length()).replaceAll("\\n", " ").replace("    ", "");
        return filter;
    }

    /**
     * 责任主体过滤判定
     *
     * @param fse
     * @param rowKeyObj
     * @return
     */
    private boolean checkNeedResponsibleFilter(FieldSetEntity fse, JSONObject rowKeyObj) {
        String responsible = fse.getString(CmnConst.RESPONSIBLE);
        if (StringUtils.isEmpty(responsible)) {
            return true;
        }
        String corpName = rowKeyObj.getString(CmnConst.PARAM_CORP_NAME);
        String deptName = rowKeyObj.getString(CmnConst.PARAM_DEPT_NAME);
        String userName = rowKeyObj.getString(CmnConst.PARAM_USER_NAME);
        if ((!StringUtils.isEmpty(corpName) && corpName.contains(responsible))
                || (!StringUtils.isEmpty(deptName) && deptName.contains(responsible))
                || (!StringUtils.isEmpty(userName) && userName.contains(responsible))) {
            return true;
        } else {
            return false;
        }

    }

    /**
     * 根据传入的维度特殊处理容器中的值
     *
     * @param fse
     * @param tableValueContainer
     */
    private Map<JSONObject, List<JSONObject>> dealContainerByDimension(FieldSetEntity fse, Map<JSONObject, List<JSONObject>> tableValueContainer) {
        int dimension = StringUtils.isEmpty(fse.getString(CmnConst.DIMENSION)) ? 4 : fse.getInteger(CmnConst.DIMENSION);
        Map<JSONObject, List<JSONObject>> newTableValueContainer = Maps.newLinkedHashMap();
        JSONObject rowKeyObj;
        JSONObject tempObj;
        List<JSONObject> curList;
        List<JSONObject> spList;
        JSONObject curValueObj;
        JSONObject spValueObj;
        int curRow;
        int spRow;

        for (Map.Entry<JSONObject, List<JSONObject>> entry : tableValueContainer.entrySet()) {
            rowKeyObj = entry.getKey();
            curList = entry.getValue();
            curRow = curList.get(0).getIntValue(CmnConst.PARAM_ROW);
            tempObj = (JSONObject) rowKeyObj.clone();
            if ((dimension == 4 && tempObj.getString(CmnConst.PARAM_USER_NAME) == null)
                    || (dimension == 2 && (tempObj.getString(CmnConst.PARAM_USER_NAME) != null || tempObj.getString(CmnConst.PARAM_DEPT_NAME) == null))
                    || (dimension == 1 && tempObj.getString(CmnConst.PARAM_DEPT_NAME) != null)) {
                continue;
            }
            spList = tableValueContainer.get(tempObj);
            if (spList != null) {
                spRow = spList.get(0).getIntValue(CmnConst.PARAM_ROW);
                if (curRow != spRow) {
                    for (int i = 0; i < curList.size(); i++) {
                        curValueObj = curList.get(i);
                        spValueObj = spList.get(i);
                        if (!StringUtils.isEmpty(curValueObj.getString(CmnConst.PARAM_VALUE))) {
                            if (StringUtils.isEmpty(spValueObj.getString(CmnConst.PARAM_VALUE))) {
                                spValueObj.put(CmnConst.PARAM_VALUE, curValueObj.getString(CmnConst.PARAM_VALUE));
                                spValueObj.put(CmnConst.PARAM_OTHER_ROW, 1);
                            } else {
                                if ("1".equals(spValueObj.getString(CmnConst.PARAM_OTHER_ROW))) {
                                    spValueObj.put(CmnConst.PARAM_VALUE, curValueObj.getDoubleValue(CmnConst.PARAM_VALUE) + spValueObj.getDoubleValue(CmnConst.PARAM_OTHER_ROW));
                                }
                            }
                        }
                    }
                }
            } else {
                spList = curList;
            }
            newTableValueContainer.put(tempObj, spList);
        }
        return newTableValueContainer;
    }

    /**
     * 添加统计行
     *
     * @param tableValueContainer
     */
    private Map<JSONObject, List<JSONObject>> addStatisticsRow(Map<JSONObject, List<JSONObject>> tableValueContainer, List<FieldSetEntity> colList, int dimension, DataTableEntity taskSubInfoDte) {
        Map<JSONObject, List<JSONObject>> resultMap = Maps.newLinkedHashMap();
        JSONObject preKeyObj = new JSONObject();
        JSONObject keyObj;
        JSONObject cloneKeyObj;
        JSONObject statisticsInfoObj;
        Map<JSONObject, List<JSONObject>> statisticsMap = Maps.newLinkedHashMap();
        Map<JSONObject, JSONObject> keyObjMap = Maps.newHashMap();
        List<JSONObject> valueObjList;

        // 总合计
        JSONObject totalKeyObj = new JSONObject();
        totalKeyObj.put(CmnConst.PARAM_STATISTICS_ROW, 1);
        totalKeyObj.put(CmnConst.PARAM_ROW_TYPE, CmnConst.VALUE_ROW_TYPE_TOTAL);
        String curStatisticsName;
        String curStatisticsValue;

        String curName = dimension == 1 ? CmnConst.PARAM_CORP_NAME : (dimension == 2 ? CmnConst.PARAM_DEPT_NAME : CmnConst.PARAM_USER_NAME);
        for (Map.Entry<JSONObject, List<JSONObject>> entry : tableValueContainer.entrySet()) {
            keyObj = entry.getKey();
            valueObjList = entry.getValue();

            if ((dimension == 2 && StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) || (dimension == 4 && StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_USER_NAME)))) {
                continue;
            }

            curStatisticsName = keyObj.getString(curName);
            curStatisticsValue = keyObj.getString(CmnConst.RESPONSIBLE);
            keyObj.remove(CmnConst.RESPONSIBLE);
            // 部门统计
            cloneKeyObj = (JSONObject) keyObj.clone();
            cloneKeyObj.put(CmnConst.PARAM_STATISTICS_ROW, 1);
            if (dimension == 4) {
                cloneKeyObj.remove(CmnConst.PARAM_USER_NAME);
                statisticsMap.put(cloneKeyObj, combineStatisticsValue(statisticsMap.get(cloneKeyObj), valueObjList, colList));
                statisticsInfoObj = keyObjMap.get(cloneKeyObj) == null ? new JSONObject() : keyObjMap.get(cloneKeyObj);
                statisticsInfoObj.put(curStatisticsName, curStatisticsValue);
                keyObjMap.put(cloneKeyObj, statisticsInfoObj);
            }
            // 公司统计
            if (dimension == 2 || dimension == 4) {
                cloneKeyObj = (JSONObject) cloneKeyObj.clone();
                cloneKeyObj.remove(CmnConst.PARAM_DEPT_NAME);
                statisticsMap.put(cloneKeyObj, combineStatisticsValue(statisticsMap.get(cloneKeyObj), valueObjList, colList));
                statisticsInfoObj = keyObjMap.get(cloneKeyObj) == null ? new JSONObject() : keyObjMap.get(cloneKeyObj);
                statisticsInfoObj.put(curStatisticsName, curStatisticsValue);
                keyObjMap.put(cloneKeyObj, statisticsInfoObj);
            }
            // 总统计
            statisticsMap.put(totalKeyObj, combineStatisticsValue(statisticsMap.get(totalKeyObj), valueObjList, colList));
            statisticsInfoObj = keyObjMap.get(cloneKeyObj) == null ? new JSONObject() : keyObjMap.get(cloneKeyObj);
            statisticsInfoObj.put(curStatisticsName, curStatisticsValue);
            keyObjMap.put(cloneKeyObj, statisticsInfoObj);

            if (preKeyObj.size() > 0) {
                if (!StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_CORP_NAME)) && !keyObj.getString(CmnConst.PARAM_CORP_NAME).equals(preKeyObj.getString(CmnConst.PARAM_CORP_NAME))) {
                    // 公司不同
                    cloneKeyObj = (JSONObject) preKeyObj.clone();
                    cloneKeyObj.remove(CmnConst.PARAM_USER_NAME);
                    cloneKeyObj.put(CmnConst.PARAM_STATISTICS_ROW, 1);
                    resultMap.put(cloneKeyObj, statisticsMap.get(cloneKeyObj));

                    cloneKeyObj = (JSONObject) cloneKeyObj.clone();
                    cloneKeyObj.remove(CmnConst.PARAM_DEPT_NAME);
                    resultMap.put(cloneKeyObj, statisticsMap.get(cloneKeyObj));
                }

                if (!StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME)) && !keyObj.getString(CmnConst.PARAM_DEPT_NAME).equals(preKeyObj.getString(CmnConst.PARAM_DEPT_NAME))) {
                    // 部门不同
                    cloneKeyObj = (JSONObject) preKeyObj.clone();
                    cloneKeyObj.remove(CmnConst.PARAM_USER_NAME);
                    cloneKeyObj.put(CmnConst.PARAM_STATISTICS_ROW, 1);
                    resultMap.put(cloneKeyObj, statisticsMap.get(cloneKeyObj));
                }
            }

            resultMap.put(keyObj, entry.getValue());

            preKeyObj = keyObj;
        }

        // 最后统计
        if (resultMap.size() > 0) {
            cloneKeyObj = (JSONObject) preKeyObj.clone();
            cloneKeyObj.put(CmnConst.PARAM_STATISTICS_ROW, 1);
            if (!StringUtils.isEmpty(cloneKeyObj.getString(CmnConst.PARAM_USER_NAME))) {
                cloneKeyObj.remove(CmnConst.PARAM_USER_NAME);
                resultMap.put(cloneKeyObj, statisticsMap.get(cloneKeyObj));
            }

            cloneKeyObj = (JSONObject) cloneKeyObj.clone();
            cloneKeyObj.remove(CmnConst.PARAM_DEPT_NAME);
            if (statisticsMap.get(cloneKeyObj) != null) {
                resultMap.put(cloneKeyObj, statisticsMap.get(cloneKeyObj));
            }

            resultMap.put(totalKeyObj, statisticsMap.get(totalKeyObj));
        }

        for (Map.Entry<JSONObject, List<JSONObject>> entry : resultMap.entrySet()) {
            keyObj = entry.getKey();
            if (!"1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                continue;
            }
            cloneKeyObj = (JSONObject) keyObj.clone();
            keyObj.put(CmnConst.PARAM_STATISTICS_RESPONSIBLE, keyObjMap.get(cloneKeyObj));
        }
        return resultMap;
    }

    /**
     * 统计行数值合并
     *
     * @param aimList
     * @param sourceList
     */
    private List<JSONObject> combineStatisticsValue(List<JSONObject> aimList, List<JSONObject> sourceList, List<FieldSetEntity> colList) {
        List<JSONObject> resultList = Lists.newArrayList();
        JSONObject valueObj;
        double v1;
        double v2;
        int curCnt;
        FieldSetEntity tempFse;
        boolean curAimListFlag;
        for (int i = 0; i < colList.size(); i++) {
            curAimListFlag = aimList == null || aimList.get(i) == null;
            valueObj = curAimListFlag ? new JSONObject() : aimList.get(i);
            tempFse = colList.get(i);
            v1 = curAimListFlag || StringUtils.isEmpty(aimList.get(i).getString(CmnConst.PARAM_VALUE)) ? 0d : string2Double(aimList.get(i).getString(CmnConst.PARAM_VALUE));
            v2 = sourceList == null || sourceList.get(i) == null || StringUtils.isEmpty(sourceList.get(i).getString(CmnConst.PARAM_VALUE)) ? 0d : string2Double(sourceList.get(i).getString(CmnConst.PARAM_VALUE));
            curCnt = sourceList == null || sourceList.get(i) == null || StringUtils.isEmpty(sourceList.get(i).getString(CmnConst.PARAM_VALUE)) ? 0 : 1;
            if ("sum".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                valueObj.put(CmnConst.PARAM_VALUE, String.valueOf(v1 + v2));
            } else if ("max".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                valueObj.put(CmnConst.PARAM_VALUE, String.valueOf(Math.max(v1, v2)));
            } else if ("min".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                if (curAimListFlag) {
                    valueObj.put(CmnConst.PARAM_VALUE, String.valueOf(v2));
                }
            } else if ("cnt".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                valueObj.put(CmnConst.PARAM_VALUE, String.valueOf(valueObj.getIntValue(CmnConst.PARAM_VALUE) + curCnt));
            } else if ("avg".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                double sumValue = valueObj.getDoubleValue(CmnConst.PARAM_STATISTICS_SUM_VALUE) + v2;
                int cntValue = valueObj.getIntValue(CmnConst.PARAM_STATISTICS_CNT_VALUE) + curCnt;
                valueObj.put(CmnConst.PARAM_STATISTICS_SUM_VALUE, String.valueOf(sumValue));
                valueObj.put(CmnConst.PARAM_STATISTICS_CNT_VALUE, cntValue);
                valueObj.put(CmnConst.PARAM_VALUE, String.format("%1$,.1f", sumValue / (cntValue == 0 ? 1 : cntValue)));
            }
            if ("1".equals(tempFse.getString(CmnConst.IS_PERCENTAGE))) {
                valueObj.put(CmnConst.IS_PERCENTAGE, 1);
            }
            if (!StringUtils.isEmpty(tempFse.getString(CmnConst.PARAM_TURN))) {
                valueObj.put(CmnConst.PARAM_TURN, tempFse.getString(CmnConst.PARAM_TURN));
            }
            resultList.add(valueObj);
        }
        return resultList;
    }

    /**
     * 字符串转换为double
     *
     * @param stringValue
     * @return
     */
    private double string2Double(String stringValue) {
        return StringUtils.isEmpty(stringValue) ? 0d : Double.parseDouble(stringValue);
    }

    /**
     * 获取html
     *
     * @param tableValueContainer
     * @return
     */
    private StringBuilder getHtml(Map<JSONObject, List<JSONObject>> tableValueContainer, List<FieldSetEntity> colList, int dimension, String reportTitle) {
        StringBuilder html = new StringBuilder(1024);
        html.append("<table id='target_table_box'>\n");
        html.append(getCssHtml());
        int colSize = (dimension == 1 ? 1 : (dimension == 2 ? 2 : 3)) + colList.size();
        html.append("<thead>\n<tr>\n    <th class='table_title'  colspan=\"").append(colSize).append("\">").append(reportTitle).append("</th>\n</tr>\n");
        html.append("<tr>\n    ");
        int responsibleColSize = 0;
        if (dimension >= 1) {
            html.append("<th>公司</td>");
            responsibleColSize++;
            if (dimension >= 2) {
                html.append("<th>团队</td>");
                responsibleColSize++;
                if (dimension >= 4) {
                    html.append("<th>人员</td>");
                    responsibleColSize++;
                }
            }
        }
        String tableValue;
        for (FieldSetEntity colFse : colList) {
            tableValue = "2".equals(colFse.getString(CmnConst.TARGET_TYPE)) ? colFse.getString(CmnConst.TARGET_TASK_NAME) : colFse.getString(CmnConst.TARGET_NAME);
            html.append("<th>").append(StringUtils.isEmpty(tableValue) ? "" : tableValue).append("</th>");
        }
        html.append("\n</tr>\n</thead>\n");
        JSONObject keyObj;
        List<JSONObject> valueList;
        StringBuilder rowHtml = new StringBuilder(256);
        int statisticsEmptyCount;
        for (Map.Entry<JSONObject, List<JSONObject>> entry : tableValueContainer.entrySet()) {
            statisticsEmptyCount = 0;
            keyObj = entry.getKey();
            valueList = entry.getValue();
            rowHtml.setLength(0);
            rowHtml.append("<tr>\n    ");
            if (CmnConst.VALUE_ROW_TYPE_TOTAL.equals(keyObj.getString(CmnConst.PARAM_ROW_TYPE))) {
                rowHtml.append("<td class=\"statistics_data\" colspan=\"").append(responsibleColSize).append("\">总计");
            } else {
                if (dimension == 1) {
                    rowHtml.append("<td class=\"organization\" colspan=\"").append(responsibleColSize).append("\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                } else if (dimension == 2) {
                    if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) {
                        rowHtml.append("<td class=\"organization\" colspan=\"").append(responsibleColSize).append("\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                    } else {
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_DEPT_NAME));
                    }
                } else if (dimension == 4) {
                    if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) {
                        rowHtml.append("<td class=\"organization\" colspan=\"3\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                    } else if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_USER_NAME))) {
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                        rowHtml.append("<td class=\"organization\" colspan=\"2\">").append(keyObj.get(CmnConst.PARAM_DEPT_NAME));
                    } else {
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_CORP_NAME));
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_DEPT_NAME));
                        rowHtml.append("<td class=\"organization\" colspan=\"1\">").append(keyObj.get(CmnConst.PARAM_USER_NAME));
                    }
                }
                if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                    rowHtml.append("统计");
                } else {
                    if ((dimension == 2 && StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) || (dimension == 4 && StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_USER_NAME)))) {
                        continue;
                    }
                }
            }
            rowHtml.append("</td>");
            if (valueList == null) {
                for (int i = 0; i < colList.size(); i++) {
                    rowHtml.append("<td></td>");
                }
            } else {
                JSONObject valueObj;
                FieldSetEntity tempFse;
                for (int i = 0; i < valueList.size(); i++) {
                    valueObj = valueList.get(i);
                    tempFse = colList.get(i);
                    rowHtml.append("<td");
                    boolean skip = false;
                    // 穿透部分
                    if (!StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_VALUE)) && !StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_TURN))) {
                        valueObj.put(CmnConst.PARAM_CLASS, (StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_CLASS)) ? "" : valueObj.getString(CmnConst.PARAM_CLASS) + " ") + "can_turn");
                        rowHtml.append(" turn=\"").append(valueObj.getString(CmnConst.PARAM_TURN) == null ? "" : valueObj.getString(CmnConst.PARAM_TURN)).append("\"");
                        rowHtml.append(" filter=\"").append(valueObj.getString(CmnConst.PARAM_FILTER) == null ? "" : valueObj.getString(CmnConst.PARAM_FILTER)).append("\"");
                        skip = true;
                    }
                    // 统计行数据
                    if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                        valueObj.put(CmnConst.PARAM_CLASS, (StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_CLASS)) ? "" : valueObj.getString(CmnConst.PARAM_CLASS) + " ") + "statistics_data");
                        if (StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_VALUE))) {
                            statisticsEmptyCount++;
                        }
                    }
                    if (!StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_CLASS))) {
                        rowHtml.append(" class=\"").append(valueObj.getString(CmnConst.PARAM_CLASS)).append("\"");
                    }
                    rowHtml.append(">").append(valueObj.getString(CmnConst.PARAM_VALUE) == null ? "" : ("1".equals(valueObj.getString(CmnConst.IS_PERCENTAGE)) ? String.format("%1$,.1f", valueObj.getDoubleValue(CmnConst.PARAM_VALUE)) + "%" : String.format("%1$,.1f", valueObj.getDoubleValue(CmnConst.PARAM_VALUE))));
                    if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                        if ("sum".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                            rowHtml.append("(求和)");
                        } else if ("max".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                            rowHtml.append("(最大)");
                        } else if ("min".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                            rowHtml.append("(最小)");
                        } else if ("cnt".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                            rowHtml.append("(计数)");
                        } else if ("avg".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                            rowHtml.append("(平均)");
                        }
                    }
                    if (skip) {
                        rowHtml.append("<i class='el-icon-link'><i>");
                    }
                    rowHtml.append("</td>");
                }
                if (statisticsEmptyCount == valueList.size()) {
                    continue;
                }
            }
            rowHtml.append("\n</tr>\n");
            html.append(rowHtml);
        }
        html.append("</table>");
        if (tableValueContainer.size() <= 0) {
            html.append("<div id=\"empty\"></div>");
        }

        return html;
    }

    /**
     * 获取报表css
     *
     * @return
     */
    private StringBuilder getCssHtml() {
        StringBuilder cssHtml = new StringBuilder(512);
        cssHtml.append("<style rel=\"stylesheet\" type=\"text/css\"> ");
        cssHtml.append("\n    table { ");
        cssHtml.append("\n        margin: 0 auto; ");
        cssHtml.append("\n    } ");
        cssHtml.append("\n    .warn_1 {color: #F56C6C !important} ");
        cssHtml.append("\n    .warn_2 {color: #FFA500 !important} ");
        cssHtml.append("\n    .warn_3 {color: #fb9d03 !important} ");
        cssHtml.append("\n    .warn_4 {color: #409DFD !important} ");
        cssHtml.append("\n    .warn_5 {color: #5cb87a !important} ");
        cssHtml.append("\n    .statistics_data,.organization {font-weight:bold} ");
        cssHtml.append("\n</style>\n");
        return cssHtml;
    }

    /**
     * 报表导出
     *
     * @param fse
     * @param response
     */
    @Override
    public void exportTargetReport(FieldSetEntity fse, HttpServletResponse response) {
        try {
            fse.setValue("export_sign", "1");
            JSONObject resultObj = getTargetReportHtml(fse);
            Map<JSONObject, List<JSONObject>> tableValueContainer = (Map<JSONObject, List<JSONObject>>) resultObj.get("result");
            List<FieldSetEntity> colList = (List<FieldSetEntity>) resultObj.get("colList");
            String reportTitle = resultObj.getString("report_title");
            int dimension = resultObj.getIntValue("dimension");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;fileName=" + reportTitle);

            String tempValue;
            int colNo = 0;
            Map<Integer, Integer> colWidthMap = Maps.newHashMap();
            colWidthMap.put(colNo++, 24 * 256);
            // 标题
            String defaultSheetName = "sheet1";
            List<List<String>> headList = Lists.newArrayList();
            Map<String, List<List<String>>> headMap = Maps.newLinkedHashMap();
            headMap.put(defaultSheetName, headList);
            List<String> rowList;
            headList.add(Arrays.asList(reportTitle, "公司"));
            if (dimension == 2 || dimension == 4) {
                headList.add(Arrays.asList(reportTitle, "团队"));
                colWidthMap.put(colNo++, 16 * 256);
            }
            if (dimension == 4) {
                headList.add(Arrays.asList(reportTitle, "人员"));
                colWidthMap.put(colNo++, 8 * 256);
            }
            int responsibleColSize = dimension == 1 ? 1 : (dimension == 2 ? 2 : 3);
            for (FieldSetEntity tempFse : colList) {
                tempValue = StringUtils.isEmpty(tempFse.getString(CmnConst.TARGET_TASK_NAME)) ? tempFse.getString(CmnConst.TARGET_NAME) : tempFse.getString(CmnConst.TARGET_TASK_NAME);
                if (StringUtils.isEmpty(tempValue)) {
                    continue;
                }
                colWidthMap.put(colNo++, tempValue.getBytes().length * 256);
                headList.add(Arrays.asList(reportTitle, tempValue));
            }

            // 合并容器
            JSONArray mergeArr = new JSONArray();

            // 数据
            List<List<String>> dataList = Lists.newArrayList();
            Map<String, List<List<String>>> dataMap = Maps.newLinkedHashMap();
            dataMap.put(defaultSheetName, dataList);
            JSONObject keyObj;
            List<JSONObject> valueList;
            JSONObject valueObj;
            FieldSetEntity tempFse;
            int rowNo = 2;// 行号
            int statisticsEmptyCount;
            for (Map.Entry<JSONObject, List<JSONObject>> entry : tableValueContainer.entrySet()) {
                keyObj = entry.getKey();
                rowList = Lists.newArrayList();
                if (CmnConst.VALUE_ROW_TYPE_TOTAL.equals(keyObj.getString(CmnConst.PARAM_ROW_TYPE))) {
                    addSameValue2List(rowList, responsibleColSize, "总计");
                    mergeArr.add(getMergeInfo(rowNo, rowNo, 0, responsibleColSize - 1));
                } else {
                    if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                        tempValue = "统计";
                    } else {
                        tempValue = "";
                    }
                    if (dimension == 1) {
                        addSameValue2List(rowList, responsibleColSize, keyObj.getString(CmnConst.PARAM_CORP_NAME) + tempValue);
                        mergeArr.add(getMergeInfo(rowNo, rowNo, 0, responsibleColSize - 1));
                    } else if (dimension == 2) {
                        if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) {
                            addSameValue2List(rowList, responsibleColSize, keyObj.getString(CmnConst.PARAM_CORP_NAME) + tempValue);
                            mergeArr.add(getMergeInfo(rowNo, rowNo, 0, responsibleColSize - 1));
                        } else {
                            addSameValue2List(rowList, 1, keyObj.getString(CmnConst.PARAM_CORP_NAME));
                            addSameValue2List(rowList, responsibleColSize - 1, keyObj.getString(CmnConst.PARAM_DEPT_NAME) + tempValue);
                            mergeArr.add(getMergeInfo(rowNo, rowNo, 1, responsibleColSize - 1));
                        }
                    } else if (dimension == 4) {
                        if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_DEPT_NAME))) {
                            addSameValue2List(rowList, responsibleColSize, keyObj.getString(CmnConst.PARAM_CORP_NAME) + tempValue);
                            mergeArr.add(getMergeInfo(rowNo, rowNo, 0, responsibleColSize - 1));
                        } else if (StringUtils.isEmpty(keyObj.getString(CmnConst.PARAM_USER_NAME))) {
                            addSameValue2List(rowList, 1, keyObj.getString(CmnConst.PARAM_CORP_NAME));
                            addSameValue2List(rowList, responsibleColSize - 1, keyObj.getString(CmnConst.PARAM_DEPT_NAME) + tempValue);
                            mergeArr.add(getMergeInfo(rowNo, rowNo, 1, responsibleColSize - 1));
                        } else {
                            addSameValue2List(rowList, 1, keyObj.getString(CmnConst.PARAM_CORP_NAME));
                            addSameValue2List(rowList, 1, keyObj.getString(CmnConst.PARAM_DEPT_NAME));
                            addSameValue2List(rowList, 1, keyObj.getString(CmnConst.PARAM_USER_NAME));
                        }
                    }
                }
                rowNo++;

                statisticsEmptyCount = 0;
                valueList = entry.getValue();
                if (valueList != null) {
                    for (int i = 0; i < valueList.size(); i++) {
                        valueObj = valueList.get(i);
                        tempFse = colList.get(i);
                        // 统计行数据
                        if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                            valueObj.put(CmnConst.PARAM_CLASS, (StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_CLASS)) ? "" : valueObj.getString(CmnConst.PARAM_CLASS) + " ") + "statistics_data");
                            if (StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_VALUE))) {
                                statisticsEmptyCount++;
                            }
                        }
                        tempValue = valueObj.getString(CmnConst.PARAM_VALUE) == null ? "" : ("1".equals(valueObj.getString(CmnConst.IS_PERCENTAGE)) ? String.format("%1$,.1f", valueObj.getDoubleValue(CmnConst.PARAM_VALUE)) + "%" : String.format("%1$,.1f", valueObj.getDoubleValue(CmnConst.PARAM_VALUE)));
                        if ("1".equals(keyObj.getString(CmnConst.PARAM_STATISTICS_ROW))) {
                            if ("sum".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                                tempValue += "(求和)";
                            } else if ("max".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                                tempValue += "(最大)";
                            } else if ("min".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                                tempValue += "(最小)";
                            } else if ("cnt".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                                tempValue += "(计数)";
                            } else if ("avg".equals(tempFse.getString(CmnConst.STATISTICS_TYPE))) {
                                tempValue += "(平均)";
                            }
                        }
                        if (!StringUtils.isEmpty(valueObj.getString(CmnConst.PARAM_COLOR))) {
                            tempValue += "{%" + valueObj.getString(CmnConst.PARAM_COLOR) + "%}";
                        }
                        rowList.add(tempValue);
                    }
                    if (statisticsEmptyCount == valueList.size()) {
                        continue;
                    }
                    dataList.add(rowList);
                }
            }

            /*====================test-start====================*/
//            headList.forEach(r -> {
//                System.out.println(r);
//            });
//            dataList.forEach(r -> {
//                System.out.println(r);
//            });
//            String tempPath = fse.getString("tempPath");
//            File file = new File(tempPath);
//            OutputStream sos = new FileOutputStream(file);
//            EasyExcelUtil.writeExcelByOutputStream(sos, dataMap, headMap, mergeArr, colWidthMap);
//            sos.close();
//            System.out.println(1);
            /*====================test-final====================*/
            EasyExcelUtil.writeExcelByOutputStream(response.getOutputStream(), dataMap, headMap, mergeArr, colWidthMap);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException(SystemCode.EXPORT_FILE_FAIL.getValue(), SystemCode.EXPORT_FILE_FAIL.getText());
        }
    }


    /**
     * 核心指标统计配置
     *
     * @return
     * @throws BaseException
     */
    @Override
    public DataTableEntity getTargetCoreStatisticsConfig() throws BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" cs.target_type, ");
        sql.append(" cs.time_horizon, ");
        sql.append(" cs.backstepping_time, ");
        sql.append(" tt.datetime_range, ");
        sql.append(" tt.datetime_range_type, ");
        sql.append(" ifnull(tt.target_task_name,tb.target_name) target_name, ");
        sql.append(" tb.uuid target_uuid, ");
        sql.append(" tb.org_value_field, ");
        sql.append(" tb.datetime_value_field, ");
        sql.append(" expression_statement, ");
        sql.append(" filter_statement, ");
        sql.append(" tb.is_percentage, ");
        sql.append(" tb.source_table,cs.compare_type, ");
        sql.append(" (select sum(target_value) from product_oa_target_task_sub where task_uuid=tt.uuid ) target_total_value ");
        sql.append(" FROM ");
        sql.append(" product_oa_target_core_statistics cs ");
        sql.append(" LEFT JOIN product_oa_target_task tt ON cs.target_type = 2 ");
        sql.append(" AND cs.target_uuid = tt.uuid ");
        sql.append(" LEFT JOIN product_oa_target_basic tb ON ( cs.target_type != 2 AND tb.uuid = cs.target_uuid ) ");
        sql.append(" OR ( cs.target_type = 2 AND tb.uuid = tt.target_uuid ) ");
        String dataPermissionFilter;
        //核心指标统计配置
        DataTableEntity coreConfigDt = getBaseDao().listTable(sql.toString(), new Object[]{});
        DataTableEntity dt = new DataTableEntity();
        if (!BaseUtil.dataTableIsEmpty(coreConfigDt)) {
            // 同比或环比sql
            StringBuilder sql2 = new StringBuilder();
            for (int i = 0; i < coreConfigDt.getRows(); i++) {
                FieldSetEntity fse = coreConfigDt.getFieldSetEntity(i);
                String filter_statement = fse.getString(CmnConst.FILTER_STATEMENT);
                String expression_statement = fse.getString(CmnConst.EXPRESSION_STATEMENT);
                String sourceTable = fse.getString(CmnConst.SOURCE_TABLE);
                dataPermissionFilter = "";
                if (!StringUtils.isEmpty(filter_statement)) {
                    filter_statement = transferExpression(filter_statement);
                }
                if (!StringUtils.isEmpty(expression_statement)) {
                    expression_statement = transferExpression(expression_statement) + " as `value`";
                } else {
                    continue;
                }
                sql.setLength(0);
                sql2.setLength(0);
                sql.append(" SELECT ").append(expression_statement).append("  FROM ").append(sourceTable);
                sql2.append(" SELECT ").append(expression_statement).append("  FROM ").append(sourceTable);
                boolean isFilter = false;
                boolean isCompare = !StringUtils.isEmpty(fse.getString(CmnConst.COMPARE_TYPE));
                if (!StringUtils.isEmpty(filter_statement)) {
                    sql.append(" WHERE  ( ").append(filter_statement).append(" ) ");
                    isFilter = true;
                }
                if (!StringUtils.isEmpty(dataPermissionFilter)) {
                    if (isFilter) {
                        sql.append(" AND ");
                    } else {
                        sql.append(" WHERE ");
                    }
                    sql.append(" ( ").append(dataPermissionFilter).append(" ) ");
                    isFilter = true;
                }
                String timeFilter[] = StringUtils.isEmpty(fse.getString(CmnConst.DATETIME_VALUE_FIELD)) ? null : getTargetCoreTimeFilter(fse);
                if (timeFilter != null && timeFilter.length > 1) {
                    // 比较sql
                    if (isCompare) {
                        String fullSql = sql.append(isFilter ? " AND " : " WHERE ").toString();
                        String filter1 = timeFilter[0];
                        String filter2 = timeFilter[1];
                        sql.setLength(0);
                        sql.append("SELECT (ifnull(a.`value`,0)/ifnull(b.`value`,0)) as `value` FROM ");
                        sql.append(" ( ").append(fullSql).append(!StringUtils.isEmpty(filter1) ? filter1 : " 1=1 ").append(" ) a ,");
                        sql.append(" ( ").append(fullSql).append(!StringUtils.isEmpty(filter2) ? filter2 : " 1=1 ").append(" ) b ");
                    } else {
                        sql.append(isFilter ? " AND " : " WHERE ").append(timeFilter[0]);
                    }
                }
                //加为同项
                fse.setValue("sql", sql);
                try {
                    boolean is_percentage = fse.getBoolean(CmnConst.IS_PERCENTAGE);
                    FieldSetEntity coreTarget = getBaseDao().getFieldSetEntityBySQL(sql.toString(), new Object[]{}, false);
                    //百分比指标
                    if (StringUtils.isEmpty(coreTarget.getString("value"))) {
                        coreTarget.setValue("value", 0);
                    }
                    Double value = coreTarget.getDouble("value");
                    if (is_percentage || !StringUtils.isEmpty(fse.getString(CmnConst.COMPARE_TYPE))) {
                        coreTarget.setValue("value", NumberUtil.round((value == 0.0 ? 0 : (value * 100.00)), 2).doubleValue() + " %");
                    }
                    FieldSetEntity temp = new FieldSetEntity();
                    temp.setTableName("temp");
                    temp.setValue(CmnConst.TARGET_NAME, fse.getString(CmnConst.TARGET_NAME));
                    temp.setValue("target_value", coreTarget.getObject("value"));
                    dt.addFieldSetEntity(temp);
                } catch (Exception e) {
                    logger.error("统计此项核心指标失败：" + sql, e);
                    // 忽略错误，继续计算下一个指标
                    continue;
                }
            }
        }
        return dt;
    }

    @Override
    public DataTableEntity findTargetCoreStatisticsConfig() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" cs.*, ");
        sql.append(" tb.target_name, ");
        sql.append(" tt.target_task_name ");
        sql.append(" FROM ");
        sql.append(" `product_oa_target_core_statistics` cs ");
        sql.append(" LEFT JOIN product_oa_target_basic tb ON cs.target_type != 2 ");
        sql.append(" AND tb.uuid = cs.target_uuid ");
        sql.append(" LEFT JOIN product_oa_target_task tt ON cs.target_type = 2 ");
        sql.append(" AND cs.target_uuid = tt.uuid ");
        return getBaseDao().listTable(sql.toString(), new Object[]{});
    }


    @Override
    public void saveTargetCoreStatisticsConfig(DataTableEntity dt) throws BaseException {
        System.out.println(dt);

        BaseUtil.createCreatorAndCreationTime(SpringMVCContextHolder.getCurrentUser(), dt);
        getBaseDao().update(dt);
    }


    private String[] getLinkRelative(String[] times) {
        //开始时间
        Date startTime = DateUtils.parseDate(times[0]);
        //结束时间
        Date endTime = DateUtils.parseDate(times[1]);

        return getLinkRelative(startTime, endTime);
    }

    private String[] getLinkRelative(Date startTime, Date endTime) {
        // 相差毫秒数
        long between = DateUtil.between(endTime, startTime, DateUnit.MS);
        //上个周期的开始时间戳
        long newDateLong = startTime.getTime() - between;
        return new String[]{DateUtil.formatDateTime(new Date(newDateLong)), DateUtil.formatDateTime(startTime)};
    }

    private String[] getTargetCoreTimeFilter(FieldSetEntity fse) {
        String target_type = fse.getString(CmnConst.TARGET_TYPE);
        StringBuilder partSql = new StringBuilder();//本期
        StringBuilder compSql = new StringBuilder();//同期或上期
        String source_table = fse.getString(CmnConst.SOURCE_TABLE);
        String dateTimeRangeField = fse.getString(CmnConst.DATETIME_VALUE_FIELD);
        String ct = fse.getString(CmnConst.COMPARE_TYPE);//1 同比 2 环比
        if ("2".equals(target_type)) {
            // 时间范围
            String dateTimeRangeType = fse.getString(CmnConst.DATETIME_RANGE_TYPE);
            String dateTimeRange = fse.getString(CmnConst.DATETIME_RANGE);
            //预警指标
            if ("1".equals(dateTimeRangeType)) {
                // 任意时间段
                String[] arr = dateTimeRange.split(",");
                partSql.append(source_table).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(arr[0]).append("','%Y-%m-%d') and ").append(source_table).append(".").append(dateTimeRangeField).append("<str_to_date('").append(arr[1]).append("','%Y-%m-%d')");
                if ("1".equals(ct)) {
                    //同比
                    compSql.append("  ").append(source_table).append(".").append(dateTimeRangeField).append(">=").append("DATE_ADD(").append("str_to_date('").append(arr[0]).append("','%Y-%m-%d'),INTERVAL  -1 YEAR) and ").append(source_table).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(arr[1]).append("','%Y-%m-%d'),INTERVAL  -1 YEAR)");
                } else {
                    //环比
                    //获取上个周期时间段
                    String[] linkRelative = getLinkRelative(arr);
                    compSql.append("  ").append(source_table).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(linkRelative[0]).append("','%Y-%m-%d') and ").append(source_table).append(".").append(dateTimeRangeField).append("<=str_to_date('").append(linkRelative[1]).append("','%Y-%m-%d')");
                }
            } else if ("2".equals(dateTimeRangeType)) {
                // 分解时间 暂放 todo
            } else if ("3".equals(dateTimeRangeType)) {
                // 动态时间（T+N）
                int deadline = Integer.parseInt(dateTimeRange);
                partSql.append(source_table).append(".").append(dateTimeRangeField).append(" between now() and date_add(now(),INTERVAL ").append(deadline).append(" DAY)");
                if ("1".equals(ct)) {
                    //同比
                    compSql.append(source_table).append(".").append(dateTimeRangeField).append(" between DATE_ADD(now(),INTERVAL -1 YEAR ) and date_add(DATE_ADD(now(),INTERVAL -1 YEAR),INTERVAL ").append(deadline).append(" DAY)");
                } else {
                    //环比
                    //当前时间
                    Date date = new Date();
                    //偏移时间 当前时间 - 偏移小时 dateLine(天） * 24(一天24小时)s
                    DateTime endTime = DateUtil.offset(date, DateField.HOUR_OF_DAY, deadline * 24);
                    String[] linkRelative = getLinkRelative(date, endTime);
                    //获取上个周期时间段
                    compSql.append(source_table).append(".").append(dateTimeRangeField).append(" between str_to_date(").append(linkRelative[0]).append(",'%Y-%m-%d %H:%i:%s') and  str_to_date(").append(linkRelative[1]).append(",'%Y-%m-%d %H:%i:%s' )");

                }
            } else if ("4".equals(dateTimeRangeType)) {
                // 周期时间
                List<String> timeList = getStartTimeAndFinalTime(dateTimeRange);
                partSql.append(source_table).append(".").append(dateTimeRangeField).append(">=str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d') and ").append(source_table).append(".").append(dateTimeRangeField).append("<str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d')");
                if ("1".equals(ct)) {
                    //同比
                    compSql.append(source_table).append(".").append(dateTimeRangeField).append(">=DATE_ADD(str_to_date('").append(timeList.get(0)).append("','%Y-%m-%d'),INTERVAL -1 YEAR) and ").append(source_table).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(timeList.get(1)).append("','%Y-%m-%d'),INTERVAL -1 YEAR)");
                } else {
                    //环比
                    String[] linkRelative = getLinkRelative(timeList.toArray(new String[]{}));
                    compSql.append(source_table).append(".").append(dateTimeRangeField).append(">=DATE_ADD(str_to_date('").append(linkRelative[0]).append("','%Y-%m-%d'),INTERVAL -1 YEAR) and ").append(source_table).append(".").append(dateTimeRangeField).append("<DATE_ADD(str_to_date('").append(linkRelative[1]).append("','%Y-%m-%d'),INTERVAL -1 YEAR)");
                }
            } else {
                throw new BaseException(SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getValue(), SystemCode.UNKNOWN_DATETIME_RANGE_TYPE.getText());
            }
        } else {
            // 获取配置的周期
            int ttp = fse.getInteger("time_horizon").intValue();//统计周期
            //当前时间段算法
            if (ttp == 1) {//年
                partSql.append(" YEAR(").append(source_table).append(".").append(dateTimeRangeField).append(")=YEAR(NOW())");
            } else if (ttp == 2) {//季
                partSql.append(" QUARTER(").append(source_table).append(".").append(dateTimeRangeField).append(")=QUARTER (now()) AND YEAR (").append(source_table).append(".").append(dateTimeRangeField).append(") = YEAR (NOW())");
            } else if (ttp == 3) {//月
                partSql.append(" MONTH(").append(source_table).append(".").append(dateTimeRangeField).append(")= MONTH(now()) AND YEAR (").append(source_table).append(".").append(dateTimeRangeField).append(") = YEAR (NOW())");
            } else if (ttp == 4) {//周
                partSql.append(" YEARWEEK(date_format(").append(source_table).append(".").append(dateTimeRangeField).append(",'%Y-%m-%d')) = YEARWEEK(now())");
            }
            if ("1".equals(ct)) {//1 同比
                if (ttp == 1) {//年
                    compSql.append(" YEAR(").append(source_table).append(".").append(dateTimeRangeField).append(")=YEAR(date_add(NOW(),INTERVAL -1 YEAR) )");
                } else if (ttp == 2) {//季
                    compSql.append(" QUARTER(").append(source_table).append(".").append(dateTimeRangeField).append(")=QUARTER (now()) AND YEAR (").append(source_table).append(".").append(dateTimeRangeField).append(") = YEAR (date_add(NOW(),INTERVAL -1 YEAR))");
                } else if (ttp == 3) {//月
                    compSql.append(" MONTH(").append(source_table).append(".").append(dateTimeRangeField).append(")= MONTH(now()) AND YEAR (").append(source_table).append(".").append(dateTimeRangeField).append(") = YEAR (date_add(NOW(),INTERVAL -1 YEAR))");
                } else if (ttp == 4) {//周
                    compSql.append(" YEARWEEK(").append(source_table).append(".").append(dateTimeRangeField).append(") = YEARWEEK(date_add(NOW(),INTERVAL -1 YEAR))");
                }
            } else if ("2".equals(ct)) {//2 环比
                if (ttp == 1) {//上一年
                    compSql.append(" YEAR(").append(source_table).append(".").append(dateTimeRangeField).append(")=YEAR(date_add(NOW(),INTERVAL -1 YEAR)) ");
                } else if (ttp == 2) {//上一季
                    compSql.append(" QUARTER(").append(source_table).append(".").append(dateTimeRangeField).append(")=QUARTER (date_add(NOW(),INTERVAL -3 MONTH)) ");
                } else if (ttp == 3) {//上一月
                    compSql.append(" MONTH(").append(source_table).append(".").append(dateTimeRangeField).append(")= MONTH(date_add(NOW(),INTERVAL -1 MONTH)) ");
                } else if (ttp == 4) {//上一周
                    compSql.append(" YEARWEEK(").append(source_table).append(".").append(dateTimeRangeField).append(") = YEARWEEK(date_add(NOW(),INTERVAL -7 DAY)) ");
                }
            }

        }
        return new String[]{partSql.toString(), compSql.toString()};
    }

    /**
     * 向list中添加指定个数相同的值
     *
     * @param list
     * @param count
     * @param value
     */
    private void addSameValue2List(List<String> list, int count, String value) {
        for (int i = 0; i < count; i++) {
            list.add(value);
        }
    }

    /**
     * 添加合并信息
     *
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    private JSONObject getMergeInfo(int firstRow, int lastRow, int firstCol, int lastCol) {
        JSONObject mergeObj = new JSONObject();
        mergeObj.put(CmnConst.FIRST_ROW, firstRow);
        mergeObj.put(CmnConst.LAST_ROW, lastRow);
        mergeObj.put(CmnConst.FIRST_COL, firstCol);
        mergeObj.put(CmnConst.LAST_COL, lastCol);
        return mergeObj;
    }
}