package com.product.server.report.service;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
import com.google.common.collect.Lists;
|
import com.google.common.collect.Maps;
|
import com.google.common.collect.Sets;
|
import com.product.core.entity.RequestParameterEntity;
|
import com.product.core.service.support.QueryFilterService;
|
import com.product.util.BaseUtil;
|
import com.product.util.CallBack;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
import com.product.common.lang.StringUtils;
|
import com.product.core.cache.DataPoolCacheImpl;
|
import com.product.core.connection.ConnectionManager;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldMetaEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.core.transfer.Transactional;
|
import com.product.server.report.config.CmnConst;
|
import com.product.server.report.service.idel.IReportDatasourceService;
|
import com.product.util.SystemParamReplace;
|
|
@Component
|
public class ReportDatasourceService extends AbstractBaseService implements IReportDatasourceService {
|
|
@Autowired
|
BaseDao baseDao;
|
|
@Autowired
|
QueryFilterService queryFilterService;
|
/**
|
* 报表数据源列表
|
*
|
* @param fse 分页加高级索引
|
* @return
|
*/
|
public DataTableEntity listDatasource(FieldSetEntity fse) {
|
Integer cpage = fse.getInteger("cpage");
|
Integer pageSize = fse.getInteger("pagesize");
|
DataTableEntity dataTableEntity = null;
|
String queryFilter = "";
|
if (!BaseUtil.dataTableIsEmpty(fse.getSubDataTable("systemSeniorQueryString"))) {
|
queryFilter = queryFilterService.getQueryFilter(fse);
|
}
|
if (!StringUtils.isEmpty(cpage)) {
|
dataTableEntity = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, queryFilter, null, null, null, pageSize, cpage);
|
} else {
|
dataTableEntity = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG);
|
}
|
baseDao.loadPromptData(dataTableEntity);
|
return dataTableEntity;
|
}
|
|
/**
|
* 报表数据源详情
|
*
|
* @param uuid
|
* @return
|
*/
|
public FieldSetEntity findDatasource(String uuid) {
|
return baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, uuid, true);
|
}
|
|
/**
|
* 数据源-保存
|
* @param fse
|
* @return
|
*/
|
@Transactional
|
@Override
|
public String saveDatasource(FieldSetEntity fse) {
|
String uuid;
|
if (StringUtils.isEmpty(fse.getUUID())) {
|
fse.setValue(CmnConst.CREATED_BY, SpringMVCContextHolder.getCurrentUserId());
|
fse.setValue(CmnConst.CREATED_UTC_DATETIME, new Date());
|
uuid = baseDao.add(fse);
|
} else {
|
fse.setValue(CmnConst.UPDATED_BY, SpringMVCContextHolder.getCurrentUserId());
|
fse.setValue(CmnConst.UPDATED_UTC_DATETIME, new Date());
|
DataTableEntity subDte = fse.getSubDataTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD);
|
if (!BaseUtil.dataTableIsEmpty(subDte)) {
|
List<String> list = Lists.newArrayList();
|
FieldSetEntity tempFse;
|
for (int i = 0; i < subDte.getRows(); i++) {
|
tempFse = subDte.getFieldSetEntity(i);
|
uuid = tempFse.getUUID();
|
if (!StringUtils.isEmpty(uuid)) {
|
list.add(uuid);
|
}
|
}
|
String filter = BaseUtil.buildQuestionMarkFilter(CmnConst.UUID, list.size(), false);
|
if (!StringUtils.isEmpty(filter)) {
|
filter += " and ";
|
}
|
filter += "datasource_uuid=?";
|
list.add(fse.getUUID());
|
baseDao.delete(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, filter, list.toArray());
|
}
|
baseDao.update(fse);
|
uuid = fse.getUUID();
|
}
|
return uuid;
|
}
|
|
/**
|
* 报表数据源-复制
|
*
|
* @param
|
* @return
|
*/
|
public FieldSetEntity copyDatasource(FieldSetEntity fse) {
|
FieldSetEntity baseFse = baseDao.getFieldSet(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, fse.getUUID(), true);
|
DataTableEntity baseSubDte = baseFse.getSubDataTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD);
|
FieldSetEntity tempFse;
|
for (int i = 0;i < baseSubDte.getRows();i++) {
|
tempFse = baseSubDte.getFieldSetEntity(i);
|
tempFse.remove(new String[]{CmnConst.UUID, CmnConst.ID});
|
}
|
baseFse.remove(new String[]{CmnConst.UUID, CmnConst.ID, CmnConst.UPDATED_UTC_DATETIME, CmnConst.UPDATED_BY});
|
baseFse.setValue(CmnConst.REPORT_DATASOURCE_NAME, baseFse.getString(CmnConst.REPORT_DATASOURCE_NAME) + "_" + System.currentTimeMillis());
|
return baseFse;
|
}
|
|
/**
|
* 报表数据源删除
|
*
|
* @param uuid
|
* @return
|
*/
|
@Transactional
|
public boolean deleteDatasource(String uuid) {
|
return baseDao.delete(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG, "uuid=?", new Object[]{uuid});
|
}
|
|
/**
|
* sql解析与验证
|
*
|
* @param sqlText
|
* @return
|
*/
|
public DataTableEntity sqlVerify(FieldSetEntity fse) throws SQLException {
|
String sqlText = fse.getString(CmnConst.SQL_TEXT);
|
// String rep = "(\\{\\$.+\\$\\})|(\\{\\^.+\\^\\})|(\\{\\&.+\\&\\})";
|
|
// sqlText = sqlText.replaceAll("\\[\\[.+\\]\\]", "").replaceAll("(\\{\\$.+\\$\\})|(\\{\\^.+\\^\\})|(\\{\\&.+\\&\\})", "null");
|
|
// sqlText = sqlText.replace("\n", " ");//替换为空格
|
// sqlText = sqlText.replaceAll("\\{%((?!(\\{%)).+)%\\}", "");
|
sqlText = SystemParamReplace.systemParamsReplace(sqlText);
|
sqlText+=" limit 1 ";
|
DataTableEntity dataTableEntity = getColumnNames(sqlText);
|
String relatedTable = dataTableEntity.getFieldSetEntity(0).getString(CmnConst.TABLE_NAME);
|
Map<String, String> map = Maps.newHashMap();
|
if (!BaseUtil.strIsNull(relatedTable)) {
|
FieldSetEntity tableInfo = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, "table_name = ?", new Object[]{relatedTable}, true);
|
if (tableInfo != null) {
|
DataTableEntity fieldData = tableInfo.getSubDataTable(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
|
for (int i = 0, length = fieldData.getRows(); i < length; i++) {
|
FieldSetEntity subField = fieldData.getFieldSetEntity(i);
|
String fieldName = subField.getString("field_name");
|
String fieldDescription = subField.getString("field_description");
|
map.put(fieldName, fieldDescription);
|
}
|
}
|
}
|
Map<String, List<String>> fieldTypeMap = Maps.newHashMap();
|
fieldTypeMap.put(CmnConst.ATTR_DATA_TYPE_NUM, Arrays.asList(CmnConst.FIELD_TYPE_NUM_STR.split(",")));
|
fieldTypeMap.put(CmnConst.ATTR_DATA_TYPE_DATE, Arrays.asList(CmnConst.FIELD_TYPE_DATE_STR.split(",")));
|
String fieldType;
|
for (int i = 0; i < dataTableEntity.getRows(); i++) {
|
FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
|
fieldSetEntity.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
|
fieldSetEntity.setValue(CmnConst.FIELD_TEXT, map.get(fieldSetEntity.getString(CmnConst.FIELD_NAME)));
|
fieldType = fieldSetEntity.getString(CmnConst.FIELD_FORMAT);
|
mid:
|
for (Map.Entry<String, List<String>> entry : fieldTypeMap.entrySet()) {
|
for (String singleFieldType : entry.getValue()) {
|
if (singleFieldType.equalsIgnoreCase(fieldType)) {
|
fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, entry.getKey());
|
break mid;
|
}
|
}
|
}
|
fieldType = fieldSetEntity.getString(CmnConst.FIELD_FORMAT);
|
if (!CmnConst.ATTR_DATA_TYPE_NUM.equalsIgnoreCase(fieldType) && !CmnConst.ATTR_DATA_TYPE_DATE.equalsIgnoreCase(fieldType)) {
|
fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, CmnConst.ATTR_DATA_TYPE_STRING);
|
}
|
}
|
//data存放表名
|
dataTableEntity.setMeta(dataTableEntity.getFieldSetEntity(0).getMeta());
|
|
// 获取原有信息 2021年12月24日 10:51:38 6c
|
DataTableEntity subDte = baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, "datasource_uuid=?", new Object[]{fse.getUUID()});
|
FieldSetEntity subFse;
|
FieldSetEntity tempFse;
|
for (int j = 0; j < dataTableEntity.getRows(); j++) {
|
tempFse = dataTableEntity.getFieldSetEntity(j);
|
for (int i = 0; i < subDte.getRows(); i++) {
|
subFse = subDte.getFieldSetEntity(i);
|
if (subFse.getString(CmnConst.FIELD_NAME).equals(tempFse.getString(CmnConst.FIELD_NAME))) {
|
tempFse.setValue(CmnConst.FIELD_TEXT, subFse.getString(CmnConst.FIELD_TEXT));
|
tempFse.setValue(CmnConst.FIELD_PROMPT, subFse.getString(CmnConst.FIELD_PROMPT));
|
tempFse.setValue(CmnConst.TABLE_NAME, subFse.getString(CmnConst.TABLE_NAME));
|
tempFse.setValue(CmnConst.FIELD_FORMAT, subFse.getString(CmnConst.FIELD_FORMAT));
|
tempFse.setValue(CmnConst.SEARCH_TYPE, subFse.getInteger(CmnConst.SEARCH_TYPE));
|
tempFse.setValue(CmnConst.LOGICAL_OPERATOR, subFse.getString(CmnConst.LOGICAL_OPERATOR));
|
tempFse.setValue(CmnConst.LOGICAL_VALUE, subFse.getString(CmnConst.LOGICAL_VALUE));
|
break;
|
}
|
}
|
}
|
|
return dataTableEntity;
|
}
|
|
|
public static Set<String> getCustomColumnName(String sql) {
|
Set<String> result = Sets.newHashSet();
|
CallBack<Matcher> getColumn = param -> {
|
Matcher m = param[0];
|
while (m.find()) {
|
result.add(m.group(2));
|
}
|
};
|
String[] reps = {"(\\{\\^)([\\w]+)(\\^\\})", "(\\{\\$)([\\w]+)(\\$\\})", "(\\{\\&)([\\w]+)(\\&\\})"};
|
for (String rep : reps) {
|
getColumn.method(Pattern.compile(rep).matcher(sql));
|
}
|
return result;
|
}
|
|
/**
|
* 获取表中所有字段名称 不正确-------------------
|
*
|
* @param sql 表名
|
* @return
|
* @throws BaseException show full columns from tableName 通过表名获取数据库元数据
|
*/
|
public DataTableEntity getColumnNames(String sql) throws SQLException {
|
DataTableEntity dataTableEntity = new DataTableEntity();
|
Set<String> customColumnName = getCustomColumnName(sql);
|
sql = sql.replaceAll("(\\{\\$((?!(\\{\\$)).)+\\$\\})|(\\{\\^((?!(\\{\\^)).)+\\^\\})|(\\{\\&((?!(\\{\\&)).)+\\&\\})", "null");
|
List<String> ternaryOperatorSuitList = BaseUtil.getSuitContent(sql, "\\{@((?!(\\{@)|(@\\})|(\\[\\[)|(\\]\\])).)+@\\}");
|
String executeContent;
|
String resultValue;
|
for (String ternaryOperator : ternaryOperatorSuitList) {
|
executeContent = ternaryOperator.substring(2, ternaryOperator.length() - 2);
|
try {
|
resultValue = BaseUtil.executeExpression(executeContent).toString();
|
sql = sql.replace(ternaryOperator, resultValue);
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
sql = sql.replace(ternaryOperator, "");
|
}
|
}
|
sql = sql.replaceAll("\\[\\[((?!\\[).)+\\]\\]", "");
|
//与数据库的连接
|
PreparedStatement ps = null;
|
Connection con = null;
|
con = ConnectionManager.getConnection();
|
sql = sql.replaceAll("and\\s+1\\s*=\\s*1", "");
|
ps = con.prepareStatement(sql);
|
//结果集元数据
|
ResultSetMetaData rsmd = ps.getMetaData();
|
//表列数
|
int size = rsmd.getColumnCount();
|
Set<String> fieldNameSet = Sets.newHashSet();
|
for (int i = 1; i <= size; i++) {
|
FieldSetEntity fieldSetEntity = new FieldSetEntity();
|
FieldMetaEntity f = new FieldMetaEntity();
|
f.setTableName(new Object[]{CmnConst.PRODUCT_SYS_DATAMODEL_FIELD});
|
fieldSetEntity.setMeta(f);
|
fieldSetEntity.setValue(CmnConst.TABLE_NAME, rsmd.getTableName(i));//获取表名
|
//字段名或别名
|
fieldSetEntity.setValue(CmnConst.FIELD_NAME, rsmd.getColumnLabel(i));
|
//字段类型
|
fieldSetEntity.setValue(CmnConst.FIELD_FORMAT, "string");
|
// 查询类型
|
fieldSetEntity.setValue(CmnConst.SEARCH_TYPE, -1);
|
//获取指定列的指定列大小
|
// fieldSetEntity.setValue(CmnConst.FIELD_LENGTH, rsmd.getPrecision(i));
|
dataTableEntity.addFieldSetEntity(fieldSetEntity);
|
fieldNameSet.add(fieldSetEntity.getString(CmnConst.FIELD_NAME));
|
}
|
for (String columnName : customColumnName) {
|
if (fieldNameSet.contains(columnName)) {
|
continue;
|
}
|
FieldSetEntity fs = new FieldSetEntity();
|
fs.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
|
fs.setValue(CmnConst.FIELD_NAME, columnName);
|
fs.setValue(CmnConst.FIELD_FORMAT, "string");
|
fs.setValue(CmnConst.SEARCH_TYPE, -1);
|
dataTableEntity.addFieldSetEntity(fs);
|
}
|
return dataTableEntity;
|
}
|
|
/**
|
* 根据数据源配置，查询出数据
|
*
|
* @param dfs 数据源
|
* @return
|
*/
|
public DataTableEntity getDataSourceData(FieldSetEntity dfs) {
|
if (dfs == null) {
|
return null;
|
}
|
String sql = dfs.getString("sql_text");
|
sql = SystemParamReplace.systemParamsReplace(sql);
|
DataTableEntity dt = null;
|
if (dfs.getBoolean("is_page")) {
|
dt = baseDao.listTable(sql, new String[]{}, dfs.getInteger("page_size"), 1);
|
} else {
|
dt = baseDao.listTable(sql, new String[]{});
|
}
|
return dt;
|
}
|
|
/**
|
* 根据数据源配置，查询出数据
|
*
|
* @param uuid 数据源uuid
|
* @return
|
*/
|
public DataTableEntity getDataSourceData(String uuid) {
|
if (StringUtils.isEmpty(uuid)) {
|
return null;
|
}
|
//获取数据源
|
DataTableEntity datasources = DataPoolCacheImpl.getInstance().getCacheData("数据源配置", new String[]{uuid});
|
if (datasources != null && datasources.getRows() > 0) {
|
return getDataSourceData(datasources.getFieldSetEntity(0));
|
}
|
return null;
|
}
|
|
public DataTableEntity getDictNames() {
|
return baseDao.listTable("SELECT dict_name FROM product_sys_dict GROUP BY dict_name", new Object[]{});
|
}
|
|
/**
|
* 根据数据源获取所有字段信息
|
*
|
* @param uuid
|
* @return
|
*/
|
public DataTableEntity getDataReouceOfField(String uuid) {
|
return baseDao.listTable(CmnConst.PRODUCT_SYS_REPORT_DATASOURCE_CONFIG_FIELD, "datasource_uuid=?", new Object[]{uuid});
|
}
|
|
/**
|
* 获取所有参照
|
*
|
* @return
|
*/
|
public List<String> getDictAndPrompt() {
|
List<String> reference_name = new ArrayList<String>();
|
StringBuffer query_sql = new StringBuffer();
|
query_sql.append(" select dict_name from( ");
|
query_sql.append(" select CONCAT('《',dict_name,'》')dict_name FROM product_sys_dict GROUP BY dict_name ");
|
query_sql.append(" UNION ALL ");
|
query_sql.append(" select prompt_name FROM product_sys_prompt ");
|
query_sql.append(" )a ");
|
DataTableEntity dt = baseDao.listTable(query_sql.toString(), new Object[]{});
|
if (!BaseUtil.dataTableIsEmpty(dt)) {
|
for (int i = 0; i < dt.getRows(); i++) {
|
reference_name.add(dt.getFieldSetEntity(i).getString("dict_name"));
|
}
|
}
|
return reference_name;
|
}
|
}
|
