dzhh-backend/.svn/pristine/ad/ade89f4373e5e6cec2e8d32ce560e26af46078e0.svn-base

package org.jeecg.modules.online.cgreport.service.impl;

import cn.hutool.core.util.ReUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.dynamic.db.DataSourceCachePool;
import org.jeecg.common.util.dynamic.db.DynamicDBUtil;
import org.jeecg.common.util.dynamic.db.SqlUtils;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportItem;
import org.jeecg.modules.online.cgreport.model.OnlCgreportModel;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportItemService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportParamService;
import org.jeecg.modules.online.cgreport.util.SqlUtil;
import org.jeecg.common.util.CommonUtils;
import org.jeecg.common.util.oConvertUtils;
//import org.jeecg.modules.online.cgform.util.SqlSymbolUtil;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportHead;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportParam;
import org.jeecg.modules.online.cgreport.mapper.OnlCgreportHeadMapper;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportHeadService;
import org.jeecg.modules.online.config.util.TableUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * TODO
 *
 * @author dousw
 * @version 1.0
 * @date 2020/11/11 14:44
 */
@Service
public class OnlCgreportHeadService  extends ServiceImpl<OnlCgreportHeadMapper, OnlCgreportHead> implements IOnlCgreportHeadService {

    private static final Logger a = LoggerFactory.getLogger(OnlCgreportHeadService.class);
    @Autowired
    private IOnlCgreportParamService onlCgreportParamService;
    @Autowired
    private IOnlCgreportItemService onlCgreportItemService;
    @Autowired
    private OnlCgreportHeadMapper mapper;

    public Map<String, Object> executeSelectSql(String sql, String onlCgreportHeadId, Map<String, Object> params) throws SQLException {
        String databaseType = CommonUtils.getDatabaseType();
        LambdaQueryWrapper<OnlCgreportParam> qw = new LambdaQueryWrapper<>();
        qw.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
        List<OnlCgreportParam> reportParams = this.onlCgreportParamService.list(qw);
        OnlCgreportParam onlCgreportParam;
        String paramValue;
        if (reportParams != null && reportParams.size() > 0) {
            for(Iterator<OnlCgreportParam> iterator = reportParams.iterator(); iterator.hasNext(); sql = sql.replace("${" + onlCgreportParam.getParamName() + "}", paramValue)) {
                onlCgreportParam = iterator.next();
                Object selfParamValue = params.get("self_" + onlCgreportParam.getParamName());
                paramValue = "";
                if (selfParamValue != null) {
                    paramValue = selfParamValue.toString();
                } else if (selfParamValue == null && oConvertUtils.isNotEmpty(onlCgreportParam.getParamValue())) {
                    paramValue = onlCgreportParam.getParamValue();
                }
            }
        }

        HashMap<String, Object> result = new HashMap<>();
        Integer pageSize = oConvertUtils.getInt(params.get("pageSize"), 10);
        Integer pageNo = oConvertUtils.getInt(params.get("pageNo"), 1);
        Page<Map<String, Object>> page = new Page<>(pageNo, pageSize);
        LambdaQueryWrapper<OnlCgreportItem> var11 = new LambdaQueryWrapper<>();
        var11.eq(OnlCgreportItem::getCgrheadId, onlCgreportHeadId);
        var11.eq(OnlCgreportItem::getIsSearch, 1);
        List<OnlCgreportItem> items = this.onlCgreportItemService.list(var11);
        String tempTableName = "jeecg_rp_temp";
        String wherecase = org.jeecg.modules.online.cgreport.util.CgReportQueryParamUtil.a(items, params, tempTableName+".");
        if (ReUtil.contains(" order\\s+by ", sql.toLowerCase()) && "SQLSERVER".equalsIgnoreCase(databaseType)) {
            throw new JeecgBootException("SqlServer不支持SQL内排序!");
        } else {
            String cgSql = "select * from (" + sql + ") "+ tempTableName +"  where 1=1 " + wherecase;
            cgSql = SqlUtil.transSql(cgSql);
            Object column = params.get("column");
            if (column != null) {
                cgSql = cgSql + " order by " + tempTableName + "." + column.toString() + " " + params.get("order").toString();
            }

            a.info("报表查询sql=>\r\n" + cgSql);
            IPage<Map<String, Object>> iPage;
            if (Boolean.valueOf(String.valueOf(params.get("getAll")))) {
                List<Map<String, Object>> records = this.mapper.executeSelect(cgSql);
                iPage = new Page<Map<String, Object>>();
                iPage.setRecords(records);
                iPage.setTotal(records.size());
            } else {
                iPage = this.mapper.selectPageBySql(page, cgSql);
            }

            result.put("total", iPage.getTotal());
            result.put("records",iPage.getRecords());
            return result;
        }
    }

    public Map<String, Object> executeSelectSqlDynamic(String dbKey, String sql, Map<String, Object> params, String onlCgreportHeadId) {
        DynamicDataSourceModel dynamicDataSourceModel = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
        int pageNo = oConvertUtils.getInt(params.get("pageNo"), 1);
        int pageSize = oConvertUtils.getInt(params.get("pageSize"), 10);
        a.info("【Online多数据源逻辑】报表查询参数params: " + JSON.toJSONString(params));
        LambdaQueryWrapper<OnlCgreportParam> qw = new LambdaQueryWrapper<>();
        qw.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
        List<OnlCgreportParam> reportParams = this.onlCgreportParamService.list(qw);
        OnlCgreportParam reportParam;
        String var15;
        if (reportParams != null && reportParams.size() > 0) {
            for(Iterator<OnlCgreportParam> iterator = reportParams.iterator(); iterator.hasNext(); sql = sql.replace("${" + reportParam.getParamName() + "}", var15)) {
                reportParam = iterator.next();
                Object var14 = params.get("self_" + reportParam.getParamName());
                var15 = "";
                if (var14 != null) {
                    var15 = var14.toString();
                } else if (var14 == null && oConvertUtils.isNotEmpty(reportParam.getParamValue())) {
                    var15 = reportParam.getParamValue();
                }
            }
        }

        LambdaQueryWrapper<OnlCgreportItem> var23 = new LambdaQueryWrapper<>();
        var23.eq(OnlCgreportItem::getCgrheadId, onlCgreportHeadId);
        var23.eq(OnlCgreportItem::getIsSearch, 1);
        List<OnlCgreportItem> items = this.onlCgreportItemService.list(var23);
        if (ReUtil.contains(" order\\s+by ", sql.toLowerCase()) && "3".equalsIgnoreCase(dynamicDataSourceModel.getDbType())) {
            throw new JeecgBootException("SqlServer不支持SQL内排序!");
        } else {
            String tempTableName = "jeecg_rp_temp";
            var15 = org.jeecg.modules.online.cgreport.util.CgReportQueryParamUtil.a(items, params, tempTableName + ".");
            String querySql = "select * from (" + sql + ") " + tempTableName + "  where 1=1 " + var15;
            querySql = SqlUtil.transSql(querySql);
            String countSql = SqlUtils.getCountSql(querySql);
            Object column = params.get("column");
            if (column != null) {
                querySql = querySql + " order by " + tempTableName + "." + column.toString() + " " + params.get("order").toString();
            }

            String pageSQL = querySql;
            if (!Boolean.valueOf(String.valueOf(params.get("getAll")))) {
                pageSQL = SqlUtils.createPageSqlByDBType(dynamicDataSourceModel.getDbType(), querySql, pageNo, pageSize);
            }

            a.info("多数据源 报表查询sql=>querySql: " + querySql);
            a.info("多数据源 报表查询sql=>pageSQL: " + pageSQL);
            a.info("多数据源 报表查询sql=>countSql: " + countSql);
            HashMap<String, Object> result = new HashMap<>();
            Map<String, Object> var21 = (Map<String, Object>) DynamicDBUtil.findOne(dbKey, countSql, new Object[0]);
            result.put("total", var21.get("total"));
            List<Map<String, Object>> var22 = DynamicDBUtil.findList(dbKey, pageSQL, new Object[0]);
            result.put("records", var22);
            return result;
        }
    }

    @Transactional(
            rollbackFor = {Exception.class}
    )
    public Result<?> editAll(OnlCgreportModel values) {
        OnlCgreportHead var2 = values.getHead();
        OnlCgreportHead var3 = (OnlCgreportHead)super.getById(var2.getId());
        if (var3 == null) {
            return Result.error("未找到对应实体");
        } else {
            super.updateById(var2);
            LambdaQueryWrapper<OnlCgreportItem> var4 = new LambdaQueryWrapper();
            var4.eq(OnlCgreportItem::getCgrheadId, var2.getId());
            this.onlCgreportItemService.remove(var4);
            LambdaQueryWrapper<OnlCgreportParam> var5 = new LambdaQueryWrapper();
            var5.eq(OnlCgreportParam::getCgrheadId, var2.getId());
            this.onlCgreportParamService.remove(var5);
            Iterator var6 = values.getParams().iterator();

            while(var6.hasNext()) {
                OnlCgreportParam var7 = (OnlCgreportParam)var6.next();
                var7.setCgrheadId(var2.getId());
            }

            var6 = values.getItems().iterator();

            while(var6.hasNext()) {
                OnlCgreportItem var8 = (OnlCgreportItem)var6.next();
                var8.setFieldName(var8.getFieldName().trim().toLowerCase());
                var8.setCgrheadId(var2.getId());
            }

            this.onlCgreportItemService.saveBatch(values.getItems());
            this.onlCgreportParamService.saveBatch(values.getParams());
            return Result.ok("全部修改成功");
        }
    }

    @Transactional(
            rollbackFor = {Exception.class}
    )
    public Result<?> delete(String id) {
        boolean var2 = super.removeById(id);
        if (var2) {
            LambdaQueryWrapper<OnlCgreportItem> var3 = new LambdaQueryWrapper();
            var3.eq(OnlCgreportItem::getCgrheadId, id);
            this.onlCgreportItemService.remove(var3);
            LambdaQueryWrapper<OnlCgreportParam> var4 = new LambdaQueryWrapper();
            var4.eq(OnlCgreportParam::getCgrheadId, id);
            this.onlCgreportParamService.remove(var4);
        }

        return Result.ok("删除成功");
    }

    @Transactional(
            rollbackFor = {Exception.class}
    )
    public Result<?> bathDelete(String[] ids) {
        String[] var2 = ids;
        int var3 = ids.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            String var5 = var2[var4];
            boolean var6 = super.removeById(var5);
            if (var6) {
                LambdaQueryWrapper<OnlCgreportItem> var7 = new LambdaQueryWrapper();
                var7.eq(OnlCgreportItem::getCgrheadId, var5);
                this.onlCgreportItemService.remove(var7);
                LambdaQueryWrapper<OnlCgreportParam> var8 = new LambdaQueryWrapper();
                var8.eq(OnlCgreportParam::getCgrheadId, var5);
                this.onlCgreportParamService.remove(var8);
            }
        }

        return Result.ok("删除成功");
    }

    public List<String> getSqlFields(String sql, String dbKey) throws SQLException {
        List var3 = null;
        if (StringUtils.isNotBlank(dbKey)) {
            var3 = this.a(sql, dbKey);
        } else {
            var3 = this.a(sql, (String)null);
        }

        return var3;
    }

    public List<String> getSqlParams(String sql) {
        if (oConvertUtils.isEmpty(sql)) {
            return null;
        } else {
            ArrayList var2 = new ArrayList();
            String var3 = "\\$\\{\\w+\\}";
            Pattern var4 = Pattern.compile(var3);
            Matcher var5 = var4.matcher(sql);

            while(var5.find()) {
                String var6 = var5.group();
                var2.add(var6.substring(var6.indexOf("{") + 1, var6.indexOf("}")));
            }

            return var2;
        }
    }

    private List<String> a(String var1, String var2) throws SQLException {
        if (oConvertUtils.isEmpty(var1)) {
            return null;
        } else {
            var1 = var1.trim();
            if (var1.endsWith(";")) {
                var1 = var1.substring(0, var1.length() - 1);
            }

            var1 = QueryGenerator.convertSystemVariables(var1);
            var1 = SqlUtil.a(var1);
            Set var3;
            if (StringUtils.isNotBlank(var2)) {
                a.info("parse sql : " + var1);
                DynamicDataSourceModel var4 = DataSourceCachePool.getCacheDynamicDataSourceModel(var2);
                if (ReUtil.contains(" order\\s+by ", var1.toLowerCase()) && "3".equalsIgnoreCase(var4.getDbType())) {
                    throw new JeecgBootException("SqlServer不支持SQL内排序!");
                }

                if ("1".equals(var4.getDbType())) {
                    var1 = "SELECT * FROM (" + var1 + ") temp LIMIT 1";
                } else if ("2".equals(var4.getDbType())) {
                    var1 = "SELECT * FROM (" + var1 + ") temp WHERE ROWNUM <= 1";
                } else if ("3".equals(var4.getDbType())) {
                    var1 = "SELECT TOP 1 * FROM (" + var1 + ") temp";
                }

                a.info("parse sql with page : " + var1);
                Map var5 = (Map)DynamicDBUtil.findOne(var2, var1, new Object[0]);
                if (var5 == null) {
                    throw new JeecgBootException("该报表sql没有数据");
                }

                var3 = var5.keySet();
            } else {
                a.info("parse sql: " + var1);
                if (ReUtil.contains(" order\\s+by ", var1.toLowerCase()) && "SQLSERVER".equalsIgnoreCase(CommonUtils.getDatabaseType())) {
                    throw new JeecgBootException("SqlServer不支持SQL内排序!");
                }

                IPage var6 = this.mapper.selectPageBySql(new Page(1L, 1L), var1);
                List var7 = var6.getRecords();
                if (var7.size() < 1) {
                    throw new JeecgBootException("该报表sql没有数据");
                }

                var3 = ((Map)var7.get(0)).keySet();
            }

            if (var3 != null) {
                var3.remove("ROW_ID");
            }

            return new ArrayList(var3);
        }
    }

    public Map<String, Object> queryCgReportConfig(String reportId) {
        HashMap var2 = new HashMap(0);
        Map var3 = this.mapper.queryCgReportMainConfig(reportId);
        List var4 = this.mapper.queryCgReportItems(reportId);
        List var5 = this.mapper.queryCgReportParams(reportId);
/*        if (TableUtil.isOracle()) {
            var2.put("main", SqlSymbolUtil.getValueType(var3));
            var2.put("items", SqlSymbolUtil.transforRecords(var4));
        } else {*/
        var2.put("main", var3);
        var2.put("items", var4);
        // }

        var2.put("params", var5);
        return var2;
    }

    public List<Map<?, ?>> queryByCgReportSql(String sql, Map params, Map paramData, int pageNo, int pageSize) {
        String var6 = SqlUtil.a(sql, params);
        List var7 = null;
        if (paramData != null && paramData.size() == 0) {
            paramData = null;
        }

        if (pageNo == -1 && pageSize == -1) {
            var7 = this.mapper.executeSelete(var6);
        } else {
            Page var8 = new Page((long)pageNo, (long)pageSize);
            IPage var9 = this.mapper.selectPageBySql(var8, var6);
            if (var9.getRecords() != null && var9.getRecords().size() > 0) {
                var7.addAll(var9.getRecords());
            }
        }

        return var7;
    }
}