web_server/src/main/java/thyyxxk/webserver/utils/ReportUtil.java

package thyyxxk.webserver.utils;

import org.jetbrains.annotations.NotNull;
import thyyxxk.webserver.entity.highreport.HighReportDto;
import thyyxxk.webserver.entity.highreport.ReportBaseInfo;
import thyyxxk.webserver.entity.highreport.TableGroupInfo;
import thyyxxk.webserver.entity.targetmanagement.TargetManagement;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description: 报表相关的工具类
 * @Author: hsh
 * @CreateTime: 2022-11-04  08:34
 * @Version: 1.0
 */
public class ReportUtil {

    private static final String START_TIME = ":startTime";
    private static final String END_TIME = ":endTime";
    /**
     * 险种(下拉选查询使用)
     **/
    private static final String INSUR_NAME = "insurName";
    /**
     * 科室(下拉选查询使用)
     **/
    private static final String DEPT = "dept";
    /**
     * 医生(下拉选查询使用)
     **/
    private static final String DOCTOR = "doctor";
    /**
     * 诊断(模糊查询使用)
     **/
    private static final String DIAGN = "diagn";

    /**
     * 住院号
     **/
    private static final String ZYH = "zyh";

    /**
     * 姓名(模糊查询使用)
     **/
    private static final String XM = "xm";

    /**
     * 号别(下拉选查询使用)
     **/
    private static final String REG_LEVEL = "regLevel";

    private static final String OTHER_PARAM = "其他";

    /**
     * @description: 拼接查询数据的sql（备注：采用了script标签包裹方式拼接sql）
     * @author: hsh
     * @date: 2022/11/7 16:21
     * @param: [dto 查询条件, baseSql 基础sql, gatherSql 聚合sql, groupColumn 分组sql字段, dropSql 钻取条件sql, sortSql 排序sql]
     * @return: String
     **/
    public static @NotNull String callSqlFormat(HighReportDto dto, @NotNull String baseSql, String gatherSql, String groupColumn, String dropSql, String sortSql){
        // 查询必填字段(包括开始时间，结束时间，钻取科室，医生等sql用:拼接的字符)替换
        baseSql = getTimeString(dto, baseSql);
        // 查询非必填字段(一般是下拉选险种，模糊查询科室、诊断等sql用_拼接的字符)
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(gatherSql).append(" from (").append(baseSql).append(") t where 1 = 1 ");
        if(baseSql.contains(DEPT) && StringUtil.notBlank(dto.getDept())){
            sql.append(" and t.dept = '").append(dto.getDept()).append("' ");
        }
        if(baseSql.contains(DOCTOR) && StringUtil.notBlank(dto.getDoctor())){
            sql.append(" and t.doctor = '").append(dto.getDoctor()).append("' ");
        }
        if(baseSql.contains(INSUR_NAME) && StringUtil.notBlank(dto.getInsurName())){
            sql.append(" and t.insurName = '").append(dto.getInsurName()).append("' ");
        }
        if(baseSql.contains(REG_LEVEL) && StringUtil.notBlank(dto.getRegLevel())){
            sql.append(" and t.regLevel = '").append(dto.getRegLevel()).append("' ");
        }
        if(baseSql.contains(DIAGN) && StringUtil.notBlank(dto.getDiagn())){
            sql.append(" and t.diagn like '%").append(dto.getDiagn()).append("%' ");
        }
        if(baseSql.contains(ZYH) && StringUtil.notBlank(dto.getZyh())){
            sql.append(" and t.zyh = '").append(dto.getZyh()).append("' ");
        }
        if(baseSql.contains(XM) && StringUtil.notBlank(dto.getXm())){
            sql.append(" and t.xm like '%").append(dto.getXm()).append("%' ");
        }
        // 存在钻取，需要添加钻取条件
        if(StringUtil.notBlank(dropSql)){
            sql.append(dropSql);
        }
        // 如果存在分组情况，需要添加分组sql字段
        if(StringUtil.notBlank(groupColumn)){
            sql.append(" group by t.").append(groupColumn);
        }
        // 如果存在默认排序，需要添加排序sql
        if(StringUtil.notBlank(sortSql)){
            sql.append(" order by t.").append(sortSql);
        }
        return sql.toString();
    }

    /**
     * @description: 钻取分组查询数据
     * @author: hsh
     * @date: 2022/11/16 14:20
     * @param: [dto, report, info, isPatient] isPatient: 是否是病人这一层查询
     * @return: String
     **/
    public static String callSqlFormatByGroup(HighReportDto dto, ReportBaseInfo report, TableGroupInfo info, boolean isPatient) {
        // 获取分组查询条件
        String groupColumn = info.getFstGroup();
        String gatherMainSql = report.getGatherSql();
        StringBuilder dropSql = new StringBuilder();
        StringBuilder gatherSql = new StringBuilder();
        if (StringUtil.notBlank(info.getFthGroup()) && StringUtil.notBlank(dto.getTrdName())) {
            groupColumn = info.getFthGroup();
            if(OTHER_PARAM.equals(dto.getFstName())){
                callIsNotMatchData(dropSql, info.getFstGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getFstGroup(), dto.getFstName(), false);
            }
            if(OTHER_PARAM.equals(dto.getSndName())){
                callIsNotMatchData(dropSql, info.getSndGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getSndGroup(), dto.getSndName(), false);
            }
            if(OTHER_PARAM.equals(dto.getTrdName())){
                callIsNotMatchData(dropSql, info.getTrdGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getTrdGroup(), dto.getTrdName(), false);
            }
        } else if (StringUtil.notBlank(info.getTrdGroup()) && StringUtil.notBlank(dto.getSndName())) {
            groupColumn = info.getTrdGroup();
            if(OTHER_PARAM.equals(dto.getFstName())){
                callIsNotMatchData(dropSql, info.getFstGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getFstGroup(), dto.getFstName(), false);
            }
            if(OTHER_PARAM.equals(dto.getSndName())){
                callIsNotMatchData(dropSql, info.getSndGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getSndGroup(), dto.getSndName(), false);
            }
        } else if (StringUtil.notBlank(info.getSndGroup()) && StringUtil.notBlank(dto.getFstName())) {
            groupColumn = info.getSndGroup();
            if(OTHER_PARAM.equals(dto.getFstName())){
                callIsNotMatchData(dropSql, info.getFstGroup(), null, true);
            } else {
                callIsNotMatchData(dropSql, info.getFstGroup(), dto.getFstName(), false);
            }
        } else {
            dropSql.setLength(0);
        }
        if(isPatient){
            // 查询病人不需要分组sql，也不需要聚合函数，需要查询显示所有列并带上钻取条件即可
            gatherSql.append(" t.* ");
            return callSqlFormat(dto, report.getBaseSql(), gatherSql.toString(), null, dropSql.toString(), report.getReportSort());
        } else {
            gatherSql.append("isnull(rtrim(").append(groupColumn).append("), '").append(OTHER_PARAM).append("') as x, ").append(gatherMainSql).append(" as y ");
            return callSqlFormat(dto, report.getBaseSql(), gatherSql.toString(), groupColumn, dropSql.toString(), report.getReportSort());
        }
    }

    /**
     * @description: 查询条件中出现参数是空或者是null的情况(前端传来的参数是其他)sql拼接处理
     * @author: hsh
     * @date: 2022/11/17 9:44
     * @param: [sql, column, param, isNotMatchParam]
     * sql: 拼接sql；column: 查询条件字段；param: 查询条件值；isNotMatchParam: true表示前端传来的参数是其他即实际是没有值
     * @return: void
     **/
    private static void callIsNotMatchData(StringBuilder sql, String column, String param, boolean isNotMatchParam) {
        if(isNotMatchParam){
            sql.append(" and (t.").append(column).append(" is null or ").append(" t.").append(column).append(" = '') ");
        } else {
            sql.append(" and t.").append(column).append(" = '").append(param).append("' ");
        }
    }

    /**
     * @description: 指标字典定义的sql条件转换
     * @author: hsh
     * @date: 2023/6/13 10:47
     * @param: [dto]
     * @return: void
     **/
    public static void callSqlFormatTarget(TargetManagement dto){

        String sqlChild = dto.getCalcChild();
        String sqlMom = dto.getCalcMom();

        if(sqlChild.contains(START_TIME)){
            if(StringUtil.notBlank(dto.getStartTime())){
                sqlChild = sqlChild.replaceAll(START_TIME, "'" + dto.getStartTime() + "'");
            } else {
                sqlChild = sqlChild.replaceAll(START_TIME, "'" + DateUtil.getDayStartTime(new Date()) + "'");
            }
        }
        if(sqlChild.contains(END_TIME)){
            if(StringUtil.notBlank(dto.getEndTime())){
                sqlChild = sqlChild.replaceAll(END_TIME, "'" + dto.getEndTime() + "'");
            } else {
                sqlChild = sqlChild.replaceAll(END_TIME, DateUtil.getDayEndTime(new Date()));
            }
        }

        if (StringUtil.notBlank(sqlMom)) {
            if(sqlMom.contains(START_TIME)){
                if(StringUtil.notBlank(dto.getStartTime())){
                    sqlMom = sqlMom.replaceAll(START_TIME, "'" + dto.getStartTime() + "'");
                } else {
                    sqlMom = sqlMom.replaceAll(START_TIME, "'" + DateUtil.getDayStartTime(new Date()) + "'");
                }
            }
            if(sqlMom.contains(END_TIME)){
                if(StringUtil.notBlank(dto.getEndTime())){
                    sqlMom = sqlMom.replaceAll(END_TIME, "'" + dto.getEndTime() + "'");
                } else {
                    sqlMom = sqlMom.replaceAll(END_TIME, DateUtil.getDayEndTime(new Date()));
                }
            }
        }

        dto.setCalcChild(sqlChild);
        dto.setCalcMom(sqlMom);

    }

    /**
     * @description: 根据指标字典sql信息组装指标报表信息
     * @author: hsh
     * @date: 2023/7/3 14:44
     * @param: [dto]
     * @return: Map<String,List<ReportBaseInfo>>
     **/
    public static Map<String, ReportBaseInfo> assertReportForTarget(TargetManagement dto){
        Map<String, ReportBaseInfo> map = new HashMap<>();
        ReportBaseInfo report = new ReportBaseInfo();
        if(StringUtil.notBlank(dto.getCalcChild()) && !dto.getCalcChild().contains("/")){
            report.setReportId(dto.getChildId());
            report.setReportName(dto.getChildName());
            report.setBaseSql(dto.getCalcChild());
            report.setGatherSql(dto.getChildGather());
            report.setLevel("group_zb");
            report.setMenuId("777");
            report.setReportType("zb");
            report.setFlag("Y");
            report.setDisplayType("mx");
            map.put("calcChild", report);
        }
        if(StringUtil.notBlank(dto.getCalcMom()) && !dto.getCalcMom().contains("/")){
            report = new ReportBaseInfo();
            report.setReportId(dto.getMomId());
            report.setReportName(dto.getMomName());
            report.setBaseSql(dto.getCalcMom());
            report.setGatherSql(dto.getMomGather());
            report.setLevel("group_zb");
            report.setMenuId("777");
            report.setReportType("zb");
            report.setFlag("Y");
            report.setDisplayType("mx");
            map.put("calcMom", report);
        }
        return map;
    }

    /**
     * @description: 实体查询条件转换（历史条件原因）
     * @author: hsh
     * @date: 2023/7/17 16:43
     * @param: [dto]
     * @return: HighReportDto
     **/
    public static HighReportDto TargetManagementConvertHighReportDto(TargetManagement dto){
        HighReportDto d = new HighReportDto();
        d.setStartTime(dto.getStartTime());
        d.setEndTime(dto.getEndTime());
        return d;
    }

    /**
     * @description: 验证sql是否正确
     * @author: hsh
     * @date: 2023/7/12 10:32
     * @param: [sql, isValid]
     * @return: String
     **/
    public static String sqlCheckAndVerifyByKeyword(String sql){
        if(sql.contains("drop") || sql.contains("truncate") || sql.contains("delete")  || sql.contains("update")
                || sql.contains("DROP") || sql.contains("TRUNCATE") || sql.contains("DELETE")  || sql.contains("UPDATE")){
            return "sql语句存在非查询语句关键字，请检查！";
        }
        return "";
    }

    /**
     * @Description 报表sql中关键字替换
     * @Author hsh
     * @param dto 查询条件
     * @param sql 统计sql
     * @return sql 统计sql
     * @Date 2024/8/19 14:46
     */
    public static String sqlFormat(HighReportDto dto, String sql){
        // 查询必填字段(包括开始时间，结束时间)替换
        sql = getTimeString(dto, sql);
        // 存在dept的查询条件
        if(StringUtil.notBlank(dto.getDept())){
            sql = sql.replaceAll("::ward_code", "and t.ward_code = '" + dto.getDept() + "' ");
            sql = sql.replaceAll("::exec_unit", "and t.exec_unit = '" + dto.getDept() + "' ");
            sql = sql.replaceAll("::dept", "and t.dept = '" + dto.getDept() + "' ");
        } else {
            sql = sql.replaceAll("::ward_code", "");
            sql = sql.replaceAll("::exec_unit", "");
            sql = sql.replaceAll("::dept", "");
        }
        return sql;
    }

    @NotNull
    private static String getTimeString(HighReportDto dto, String sql) {
        if(sql.contains(START_TIME)){
            if(StringUtil.notBlank(dto.getStartTime())){
                sql = sql.replaceAll(START_TIME, "'" + dto.getStartTime() + "'");
            } else {
                sql = sql.replaceAll(START_TIME,"'2012-01-01 00:00:00'");
            }
        }
        if(sql.contains(END_TIME)){
            if(StringUtil.notBlank(dto.getEndTime())){
                sql = sql.replaceAll(END_TIME, "'" + dto.getEndTime() + "'");
            } else {
                sql = sql.replaceAll(END_TIME, DateUtil.getDayEndTime(new Date()));
            }
        }
        return sql;
    }


}