任务列表接口优化

guoshilong

2024-02-07 15601bee826ae306649c50d88dfa4f8416c8aa94

任务列表接口优化

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java

@@ -102,4 +102,8 @@
    TaskStatusCount getTaskStatusCountAll(@Param("vo") PatrolTaskVO patrolTaskVO);

    TaskStatus getTaskStatus(@Param("vo") PatrolTaskVO patrolTaskVO);

    List<PatrolTaskVO> selectPatrolTaskPage(@Param("current") long current, @Param("size") long size, @Param("patrolTask") PatrolTaskVO patrolTask);

    long getPatrolTaskTotal(@Param("patrolTask") PatrolTaskVO patrolTask);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml

@@ -50,57 +50,94 @@
        <if test="patrolTask.adCode != null and patrolTask.adCode != '' ">
            and concat(aab."ad_code",aa."ad_code",ab."ad_code",ac."ad_code") like CONCAT('%',#{patrolTask.adCode},'%')
        </if>
    </select>


<!--        select spt.*, spt2.name as patrolTypeName, route.ROUTE_COORDINATES as routeRange, u.real_name as toUserName, u2.real_name as createUserName , arb."res_loc" as regionName-->
<!--        from (-->
<!--        select * from ywxt.sm_patrol_task spt-->
<!--        where spt.is_deleted = 0-->
<!--                <if test="patrolTask.title !=null and patrolTask.title!=''">-->
<!--                    AND spt.title LIKE CONCAT('%',#{patrolTask.title},'%')-->
<!--                </if>-->
<!--                <if test="patrolTask.startTime !=null and patrolTask.startTime !=''">-->
<!--                    AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTask.startTime}-->
<!--                </if>-->
<!--                <if test="patrolTask.endTime !=null and patrolTask.endTime !=''">-->
<!--                    AND DATE_FORMAT(spt.end_time,'%Y-%m-%d') &lt;= #{patrolTask.endTime}-->
<!--                </if>-->
<!--                <if test="patrolTask.projectId !=null and patrolTask.projectId !='' ">-->
<!--                    AND spt.project_id = #{patrolTask.projectId}-->
<!--                </if>-->
<!--                <if test="patrolTask.reservoirName !=null and patrolTask.reservoirName !='' ">-->
<!--                    AND spt.reservoir_name LIKE CONCAT('%',#{patrolTask.reservoirName},'%')-->
<!--                </if>-->
<!--                <if test="patrolTask.toUserId != null and patrolTask.toUserId !=''">-->
<!--                    AND spt.to_user_id = #{patrolTask.toUserId}-->
<!--                </if>-->
<!--                <if test="patrolTask.state != null and patrolTask.state !=''">-->
<!--                    AND spt.state = #{patrolTask.state}-->
<!--                </if>-->
<!--                <if test="patrolTask.taskType != null and patrolTask.taskType !='' ">-->
<!--                    AND spt.task_type = #{patrolTask.taskType}-->
<!--                </if>-->
<!--        order by create_time desc-->
<!--        ) spt-->
<!--        left join ywxt.sm_patrol_type spt2 on spt.task_type = spt2.id-->
<!--        left join ywxt.sm_patrol_route route on route.id = spt.route_id-->
<!--        left join ywxt.blade_user u on u.id = spt.to_user_id-->
<!--        left join ywxt.blade_user u2 on u2.id = spt.create_user-->
<!--        left join SJZT_MD."att_res_base" arb on arb."guid" = spt.project_id-->
<!--        left join SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"-->
<!--        left join SJZT_MD."att_ad_base" aa on aab."p_ad_code" = aa."ad_code"-->
<!--        left join SJZT_MD."att_ad_base" ab on aa."p_ad_code" = ab."ad_code"-->
<!--        left join SJZT_MD."att_ad_base" ac on ab."p_ad_code" = ac."ad_code"-->
<!--        where 1=1-->
<!--                <if test="patrolTask.adCode != null and patrolTask.adCode != '' ">-->
<!--                    and concat(aab."ad_code",aa."ad_code",ab."ad_code",ac."ad_code") like CONCAT('%',#{patrolTask.adCode},'%')-->
<!--                </if>-->




    <select id="selectPatrolTaskPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskVO">
                select spt.*, spt2.name as patrolTypeName, route.ROUTE_COORDINATES as routeRange, u.real_name as toUserName, u2.real_name as createUserName , arb."res_loc" as regionName
                from (
                select * from ywxt.sm_patrol_task spt
                where spt.is_deleted = 0
                        <if test="patrolTask.title !=null and patrolTask.title!=''">
                            AND spt.title LIKE CONCAT('%',#{patrolTask.title},'%')
                        </if>
                        <if test="patrolTask.startTime !=null and patrolTask.startTime !=''">
                            AND spt.start_time &gt;= #{patrolTask.startTime}
                        </if>
                        <if test="patrolTask.endTime !=null and patrolTask.endTime !=''">
                            AND spt.end_time &lt;= #{patrolTask.endTime}
                        </if>
                        <if test="patrolTask.projectId !=null and patrolTask.projectId !='' ">
                            AND spt.project_id = #{patrolTask.projectId}
                        </if>
                        <if test="patrolTask.reservoirName !=null and patrolTask.reservoirName !='' ">
                            AND spt.reservoir_name LIKE CONCAT('%',#{patrolTask.reservoirName},'%')
                        </if>
                        <if test="patrolTask.toUserId != null and patrolTask.toUserId !=''">
                            AND spt.to_user_id = #{patrolTask.toUserId}
                        </if>
                        <if test="patrolTask.state != null and patrolTask.state !=''">
                            AND spt.state = #{patrolTask.state}
                        </if>
                        <if test="patrolTask.taskType != null and patrolTask.taskType !='' ">
                            AND spt.task_type = #{patrolTask.taskType}
                        </if>
                          limit ${current},${size} order by create_time desc
                ) spt
                left join ywxt.sm_patrol_type spt2 on spt.task_type = spt2.id
                left join ywxt.sm_patrol_route route on route.id = spt.route_id
                left join ywxt.blade_user u on u.id = spt.to_user_id
                left join ywxt.blade_user u2 on u2.id = spt.create_user
                left join SJZT_MD."att_res_base" arb on arb."guid" = spt.project_id
                left join SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
                left join SJZT_MD."att_ad_base" aa on aab."p_ad_code" = aa."ad_code"
                left join SJZT_MD."att_ad_base" ab on aa."p_ad_code" = ab."ad_code"
                left join SJZT_MD."att_ad_base" ac on ab."p_ad_code" = ac."ad_code"
                where 1=1
                <if test="patrolTask.adCode != null and patrolTask.adCode != '' ">
                    and concat(aab."ad_code",aa."ad_code",ab."ad_code",ac."ad_code") like CONCAT('%',#{patrolTask.adCode},'%')
                </if>

    </select>

    <select id="getPatrolTaskTotal" resultType="java.lang.Long">

        select count(*) from ywxt.sm_patrol_task spt
        left join SJZT_MD."att_res_base" arb on arb."guid" = spt.project_id
        left join SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        left join SJZT_MD."att_ad_base" aa on aab."p_ad_code" = aa."ad_code"
        left join SJZT_MD."att_ad_base" ab on aa."p_ad_code" = ab."ad_code"
        left join SJZT_MD."att_ad_base" ac on ab."p_ad_code" = ac."ad_code"
        where spt.is_deleted = 0
        <if test="patrolTask.title !=null and patrolTask.title!=''">
            AND spt.title LIKE CONCAT('%',#{patrolTask.title},'%')
        </if>
        <if test="patrolTask.startTime !=null and patrolTask.startTime !=''">
            AND spt.start_time &gt;= #{patrolTask.startTime}
        </if>
        <if test="patrolTask.endTime !=null and patrolTask.endTime !=''">
            AND spt.end_time &lt;= #{patrolTask.endTime}
        </if>
        <if test="patrolTask.projectId !=null and patrolTask.projectId !='' ">
            AND spt.project_id = #{patrolTask.projectId}
        </if>
        <if test="patrolTask.reservoirName !=null and patrolTask.reservoirName !='' ">
            AND spt.reservoir_name LIKE CONCAT('%',#{patrolTask.reservoirName},'%')
        </if>
        <if test="patrolTask.toUserId != null and patrolTask.toUserId !=''">
            AND spt.to_user_id = #{patrolTask.toUserId}
        </if>
        <if test="patrolTask.state != null and patrolTask.state !=''">
            AND spt.state = #{patrolTask.state}
        </if>
        <if test="patrolTask.taskType != null and patrolTask.taskType !='' ">
            AND spt.task_type = #{patrolTask.taskType}
        </if>
        <if test="patrolTask.adCode != null and patrolTask.adCode != '' ">
            and concat(aab."ad_code",aa."ad_code",ab."ad_code",ac."ad_code") like CONCAT('%',#{patrolTask.adCode},'%')
        </if>
    </select>

    <!--获取全部-->
    <select id="getAll" resultType="cn.gistack.sm.patrol.entity.PatrolTask">
        SELECT

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java

@@ -52,8 +52,14 @@
     */
    @Override
    public IPage<PatrolTaskVO> selectPatrolTask(IPage<PatrolTaskVO> page, PatrolTaskVO patrolTask) {
        List<PatrolTaskVO> patrolTaskList = baseMapper.selectPatrolTask(page, patrolTask);
        return page.setRecords(patrolTaskList);
//        List<PatrolTaskVO> patrolTaskList = baseMapper.selectPatrolTask(page, patrolTask);

        List<PatrolTaskVO> patrolTaskList = baseMapper.selectPatrolTaskPage(page.getCurrent()-1,page.getSize(),patrolTask);
        long total = baseMapper.getPatrolTaskTotal(patrolTask);
        page.setRecords(patrolTaskList);
        page.setTotal(total);

        return page;
    }

    /**