【丽水】获取随访统计比例 优化

陈昶聿

2 天以前 7303db54c1a2b672e4623cd022939b56882685ca

【丽水】获取随访统计比例 优化

 smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java

@@ -101,7 +101,7 @@

    public List<ServiceSubtask> getSfStatistics(ServiceSubtaskCountReq serviceSubtaskCountReq);

    public List<ServiceSubtask> getSfStatistics1(ServiceSubtaskCountReq serviceSubtaskCountReq);
    public List<ServiceSubtask> getSfStatisticsOld(ServiceSubtaskCountReq serviceSubtaskCountReq);

    public List<ServiceSubtaskCount> getSfStatisticsJoy(ServiceSubtaskCountReq serviceSubtaskCountReq);


 smartor/src/main/java/com/smartor/service/impl/ServiceSubtaskServiceImpl.java

@@ -2198,7 +2198,7 @@
    }

    /**
     * 获取随访统计比例
     * 获取随访统计比例（优化版本）
     *
     * @param serviceSubtaskCountReq
     * @return
@@ -2208,22 +2208,28 @@
    public List<ServiceSubtaskStatistic> getSfStatistics(ServiceSubtaskCountReq serviceSubtaskCountReq) {
        log.info("getSfStatistics的入参为：{}", serviceSubtaskCountReq);
        List<ServiceSubtaskStatistic> serviceSubtaskStatistics = new ArrayList<>();
//        List<ServiceSubtask> rawData = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq);

        // 优化：只查询一次数据库，避免重复查询
        List<ServiceSubtask> rawData = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq);

        if (CollectionUtils.isEmpty(rawData)) {
            return serviceSubtaskStatistics;
        }

        // 根据条件进行分组
        Map<String, List<ServiceSubtask>> collect = new HashMap<>();

        if (serviceSubtaskCountReq.getLeavehospitaldistrictcodes() != null && serviceSubtaskCountReq.getLeavehospitaldistrictcodes().size() > 0) {
            if (serviceSubtaskCountReq.getDrcode() != null && !serviceSubtaskCountReq.getDrcode().isEmpty()) {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
            } else {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getLeavehospitaldistrictname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getLeavehospitaldistrictname()).orElse("Unknown")));
            }
        } else if (serviceSubtaskCountReq.getDeptcodes() != null && serviceSubtaskCountReq.getDeptcodes().size() > 0) {
            if (serviceSubtaskCountReq.getDrcode() != null && !serviceSubtaskCountReq.getDrcode().isEmpty()) {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
            } else {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDeptname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDeptname()).orElse("Unknown")));
            }
        }

@@ -2278,7 +2284,7 @@
    }

    /**
     * 获取随访统计比例
     * 获取随访统计比例（优化版本）
     *
     * @param serviceSubtaskCountReq
     * @return
@@ -2289,22 +2295,28 @@
        log.error("getSfStatistics的入参为：{}", serviceSubtaskCountReq);
        List<ServiceSubtaskStatistic> serviceSubtaskStatistics = new ArrayList<>();

        // 优化：只查询一次数据库，避免重复查询
        List<ServiceSubtask> rawData = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq);

        if (CollectionUtils.isEmpty(rawData)) {
            return serviceSubtaskStatistics;
        }

        // 根据条件进行分组
        Map<String, List<ServiceSubtask>> collect = new HashMap<>();
        String orgid = null;

        if (serviceSubtaskCountReq.getLeavehospitaldistrictcodes() != null && serviceSubtaskCountReq.getLeavehospitaldistrictcodes().size() > 0) {
            if (serviceSubtaskCountReq.getDrcode() != null && !serviceSubtaskCountReq.getDrcode().isEmpty()) {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));

                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
            } else {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getLeavehospitaldistrictname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getLeavehospitaldistrictname()).orElse("Unknown")));
            }
        } else if (serviceSubtaskCountReq.getDeptcodes() != null && serviceSubtaskCountReq.getDeptcodes().size() > 0) {
            if (serviceSubtaskCountReq.getDrcode() != null && !serviceSubtaskCountReq.getDrcode().isEmpty()) {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDrname()).orElse("Unknown")));
            } else {
                collect = serviceSubtaskMapper.getSfStatistics(serviceSubtaskCountReq).stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDeptname()).orElse("Unknown")));
                collect = rawData.stream().collect(Collectors.groupingBy(subtask -> Optional.ofNullable(subtask.getDeptname()).orElse("Unknown")));
            }
        }
        List<ServiceSubtaskCount> joySubCount = serviceSubtaskMapper.getSfStatisticsJoy(serviceSubtaskCountReq);

 smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml

@@ -490,7 +490,8 @@
        </where>
    </select>

    <select id="selectTimelyRate" parameterType="com.smartor.domain.ServiceSubtask" resultType="double">
    <!-- 原有查询，性能差 -->
    <select id="selectTimelyRateOld" parameterType="com.smartor.domain.ServiceSubtask" resultType="double">
        SELECT sum( a.finished )/ sum( 1 ) FROM
        (SELECT id, visit_time, finishtime,
        (CASE WHEN ( DATE_FORMAT( visit_time, '%y%m%d' )&lt; DATE_FORMAT( IF ( ISNULL( finishtime ), NOW(), finishtime
@@ -517,6 +518,35 @@
        <if test="managementDoctorCode != null">and management_doctor_code = #{managementDoctorCode}</if>
        <if test="managementDoctor != null">and management_doctor like concat('%',#{managementDoctor}, '%')</if>
        ) a
    </select>

    <!-- 优化后的及时率查询：直接在 WHERE 中计算，避免子查询 -->
    <select id="selectTimelyRate" parameterType="com.smartor.domain.ServiceSubtask" resultType="double">
        SELECT
            SUM(CASE WHEN DATE(visit_time) &lt; DATE(COALESCE(finishtime, NOW())) THEN 1 ELSE 0 END) / COUNT(*)
        FROM service_subtask
        WHERE del_flag=0
        <if test="starttime == null and endtime==null">
            AND visit_time &lt; NOW()
        </if>
        <if test="starttime != null and endtime!=null">
            <!-- 优化：使用日期范围查询替代 date_format -->
            AND visit_time >= #{starttime}
            AND visit_time &lt; DATE_ADD(#{endtime}, INTERVAL 1 DAY)
        </if>
        <if test="orgid != null  and orgid != ''">AND orgid = #{orgid}</if>
        <if test="deptcode != null  and deptcode != ''">AND deptcode = #{deptcode}</if>
        <if test="drcode != null  and drcode != ''">AND drcode = #{drcode}</if>
        <if test="deptname != null  and deptname != ''">AND deptname = #{deptname}</if>
        <if test="isabnormal != null">AND isabnormal = #{isabnormal}</if>
        <if test="leavehospitaldistrictcode != null  and leavehospitaldistrictcode != ''">
            AND leavehospitaldistrictcode = #{leavehospitaldistrictcode}
        </if>
        <if test="leavehospitaldistrictname != null  and leavehospitaldistrictname != ''">
            AND leavehospitaldistrictname = #{leavehospitaldistrictname}
        </if>
        <if test="managementDoctorCode != null">AND management_doctor_code = #{managementDoctorCode}</if>
        <if test="managementDoctor != null">AND management_doctor LIKE CONCAT('%',#{managementDoctor}, '%')</if>
    </select>

    <select id="selectTimelyRateDetail" parameterType="com.smartor.domain.ServiceSubtask"
@@ -1135,7 +1165,7 @@
    </update>


    <select id="getSfStatistics" parameterType="com.smartor.domain.ServiceSubtaskCountReq"
    <select id="getSfStatisticsOld" parameterType="com.smartor.domain.ServiceSubtaskCountReq"
            resultMap="ServiceSubtaskResult">
        <include refid="selectServiceSubtaskVo"/>
        <where>
@@ -1172,7 +1202,7 @@
    </select>

    <!-- 优化后的查询：减少返回字段，优化日期查询，避免 date_format -->
    <select id="getSfStatistics1" parameterType="com.smartor.domain.ServiceSubtaskCountReq"
    <select id="getSfStatistics" parameterType="com.smartor.domain.ServiceSubtaskCountReq"
            resultMap="ServiceSubtaskResult">
        SELECT
            id,