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;
|
}
|
}
|
