巡查报表接口修改

guoshilong

2023-07-28 916b76ee90fceff31fe7969db9d9da995d919d52

巡查报表接口修改

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

@@ -58,12 +58,6 @@

    PatrolTaskVO customizeGetById(@Param("id") String id);

    /**
     * 巡查报表，市拿区
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getCountyReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);

    /**
     * 巡查报表，获取水库巡查报表数据
@@ -72,22 +66,6 @@
     */
    List<PatrolTaskReport> getReservoirReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);

    /**
     * 巡查报表，获取市级巡查报表数据
     * @param page
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getCityReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);


    /**
     * 巡查报表，获取乡镇巡查数据
     * @param page
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getTownReportPage(IPage<PatrolTaskReport> page, @Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);

    /**
     * 巡查报表详情 获取行政区下的水库
@@ -102,35 +80,23 @@


    /**
     * 巡查报表，市拿区
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getCountyReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);

    /**
     * 巡查报表，获取水库巡查报表数据
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getReservoirReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);

    /**
     * 巡查报表，获取市级巡查报表数据
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getCityReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);


    /**
     * 巡查报表，获取乡镇巡查数据
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getTownReportList( @Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);

    List<TaskStatusCount> getTaskStatusCount(@Param("vo") PatrolTaskVO patrolTaskVO);

    List<TaskStatusCount> getTaskTypeCount(@Param("vo") PatrolTaskVO patrolTaskVO);

    /**
     * 获取省市区统计报表
     * @param page
     * @param patrolTaskReport
     * @return
     */
    List<PatrolTaskReport> getRegionReportPage(IPage<PatrolTaskReport> page, @Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);

    List<PatrolTaskReport> getRegionReportList(@Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);
}

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

@@ -200,225 +200,230 @@
        WHERE T.ID = #{id}
        AND T.IS_DELETED = 0
    </select>
    <!--获取市级巡查数据-->
    <select id="getCityReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        a."p_ad_code" adCode,
        a."p_ad_name" adName,
        sum(a.need) AS need,
        sum(a.finish) AS finish,
        sum(a.todo) AS todo,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime
        FROM (
        SELECT
        aab."p_ad_code",aab."p_ad_name",
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
        where t.is_deleted = 0 AND aab."p_ad_code" != '420000000000'
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."p_ad_code",aab."p_ad_name")
        ORDER BY aab."p_ad_code"
        ) A
        GROUP BY (a."p_ad_code",a."p_ad_name") order by a."p_ad_code"

    <!--行政区统计分页-->
    <select id="getRegionReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        select * from (
                          select "ad_name"  AS adName ,"guid" AS adCode,"ad_grad","order_id", #{patrolTaskReport.startTime} AS startTime,#{patrolTaskReport.endTime} AS endTime,
                                 (
                                     select count(*) from YWXT.SM_PATROL_TASK spt
                                                              left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
                                                              left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
                                                              left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
                                                              left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
                                     where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
                                       and spt.is_deleted = 0
                                <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
                                    AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
                                </if>
                                <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
                                    AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
                                </if>
                                 ) need ,
                                 (
                                     select count(*) from YWXT.SM_PATROL_TASK spt
                                                              left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
                                                              left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
                                                              left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
                                                              left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
                                     where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
                                       and spt.is_deleted = 0 and spt.state = 3
                                    <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
                                        AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
                                    </if>
                                    <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
                                        AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
                                    </if>
                                 ) finish ,
                                 (
                                     select count(*) from YWXT.SM_PATROL_TASK spt
                                                              left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
                                                              left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
                                                              left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
                                                              left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
                                     where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
                                       and spt.is_deleted = 0 and (spt.state = 1 or spt.state = 2)
                                <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
                                    AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
                                </if>
                                <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
                                    AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
                                </if>
                                 ) todo ,
                                 (
                                     select count(*) from SJZT_MD."att_res_base" a
                                                              left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
                                                              left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
                                                              left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
                                     where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
                                 ) res_count
                          from SJZT_MD."att_ad_base" aab where
                                <choose>
                                    <when test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
                                        "p_ad_code" = #{patrolTaskReport.adCode}
                                    </when>
                                    <otherwise>
                                        "ad_grad" = 2
                                    </otherwise>
                                </choose>
                      )
        where res_count >0 order by "order_id" asc
    </select>
    <!--获取区县巡查数据-->
    <select id="getCountyReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        aab."ad_name" AS adName,
        aab."guid" AS adCode,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
        WHERE t.IS_DELETED = 0
        <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
            AND aab."p_ad_code" = #{patrolTaskReport.padCode}
        </if>

    <!--行政区统计列表-->
    <select id="getRegionReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        select * from (
        select "ad_name","ad_grad","order_id", #{patrolTaskReport.startTime} AS startTime,#{patrolTaskReport.endTime} AS endTime,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
        left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
        left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
        left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
        where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
        and spt.is_deleted = 0
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."ad_name",aab."guid")
        ORDER BY aab."guid"
    </select>
    <!--获取乡镇巡查数据-->
    <select id="getTownReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        aab."ad_name" AS adName,
        aab."guid" AS adCode,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        WHERE t.IS_DELETED = 0
        <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
            AND aab."p_ad_code" = #{patrolTaskReport.padCode}
        </if>
        ) need ,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
        left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
        left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
        left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
        where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
        and spt.is_deleted = 0 and spt.state = 3
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."ad_name",aab."guid")
        ORDER BY aab."guid"
        ) finish ,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
        left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
        left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
        left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
        where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
        and spt.is_deleted = 0 and (spt.state = 1 or spt.state = 2)
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        ) todo ,
        (
        select count(*) from SJZT_MD."att_res_base" a
        left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
        left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
        left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
        where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
        ) res_count
        from SJZT_MD."att_ad_base" aab where
        <choose>
            <when test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
                "p_ad_code" = #{patrolTaskReport.adCode}
            </when>
            <otherwise>
                "ad_grad" = 2
            </otherwise>
        </choose>
        )
        where res_count >0 order by "order_id" asc
    </select>






    <!--获取水库巡查数据-->
    <select id="getReservoirReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        arb."guid" AS reservoirId,
        arb."name" AS reservoirName,
        aab."guid" AS adCode,
        aab."ad_name" AS adName,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        WHERE
        t.IS_DELETED = 0
        <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !=''">
            AND aab."ad_code" = #{patrolTaskReport.adCode}
        </if>
            arb."name" AS reservoirName,
            arb."guid" AS reservoirId,
            arb."interior_ad_guid" AS adCode,
            aab."ad_name" AS adName,
            #{patrolTaskReport.startTime} AS startTime,
            #{patrolTaskReport.endTime} AS endTime,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (arb."guid",arb."name",aab."guid",aab."ad_name")
        ORDER BY arb."guid"
    </select>



    <!--获取市级巡查数据-->
    <select id="getCityReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        a."p_ad_code" adCode,
        a."p_ad_name" adName,
        sum(a.need) AS need,
        sum(a.finish) AS finish,
        sum(a.todo) AS todo,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime
        FROM (
        SELECT
        aab."p_ad_code",aab."p_ad_name",
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
        where t.is_deleted = 0 AND aab."p_ad_code" != '420000000000'
        ) need,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0 and spt.state = 3
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."p_ad_code",aab."p_ad_name")
        ORDER BY aab."p_ad_code"
        ) A
        GROUP BY (a."p_ad_code",a."p_ad_name") order by a."p_ad_code"
        ) finish,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0 and (spt.state = 1 or spt.state =2)
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        ) todo
        FROM SJZT_MD."att_res_base" arb
        LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = arb."interior_ad_guid"
        WHERE "is_deleted" = 0
          <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
              AND arb."interior_ad_guid" = #{patrolTaskReport.adCode}
          </if>

    </select>
    <!--获取区县巡查数据-->
    <select id="getCountyReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        aab."ad_name" AS adName,
        aab."guid" AS adCode,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
        WHERE t.IS_DELETED = 0
        <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
            AND aab."p_ad_code" = #{patrolTaskReport.padCode}
        </if>
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."ad_name",aab."guid")
        ORDER BY aab."guid"
    </select>
    <!--获取乡镇巡查数据-->
    <select id="getTownReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        aab."ad_name" AS adName,
        aab."guid" AS adCode,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        WHERE t.IS_DELETED = 0
        <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
            AND aab."p_ad_code" = #{patrolTaskReport.padCode}
        </if>
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
        </if>
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (aab."ad_name",aab."guid")
        ORDER BY aab."guid"
    </select>

    <!--获取水库巡查数据-->
    <select id="getReservoirReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
        arb."guid" AS reservoirId,
        arb."name" AS reservoirName,
        aab."guid" AS adCode,
        arb."guid" AS reservoirId,
        arb."interior_ad_guid" AS adCode,
        aab."ad_name" AS adName,
        #{patrolTaskReport.startTime} AS startTime,
        #{patrolTaskReport.endTime} AS endTime,
        COUNT(*) AS need,
        SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
        SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
        FROM SM_PATROL_TASK t
        LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
        LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        WHERE
        t.IS_DELETED = 0
        <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !=''">
            AND aab."ad_code" = #{patrolTaskReport.adCode}
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0
        ) need,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0 and spt.state = 3
        ) finish,
        (
        select count(*) from YWXT.SM_PATROL_TASK spt
        where arb."guid" = spt.project_id
        and spt.is_deleted = 0 and (spt.state = 1 or spt.state =2)
        ) todo
        FROM SJZT_MD."att_res_base" arb
        LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = arb."interior_ad_guid"
        WHERE "is_deleted" = 0
        <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
            AND "interior_ad_guid" = #{patrolTaskReport.adCode}
        </if>
        <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &gt;= #{patrolTaskReport.startTime}
@@ -426,8 +431,6 @@
        <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
            AND DATE_FORMAT(t.start_time,'%Y-%m-%d') &lt;= #{patrolTaskReport.endTime}
        </if>
        GROUP BY (arb."guid",arb."name",aab."guid",aab."ad_name")
        ORDER BY arb."guid"
    </select>


@@ -438,27 +441,30 @@
    <!--水库巡查-->
    <select id="getTaskReportDetailRegion" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
        SELECT
            arb."guid" AS reservoirId,
            arb."name" AS reservoirName,
            aab."ad_name" AS adName,
            aab."guid" AS adCode,
            t.start_time,
            u.real_name patrolUserName,
            t.state,
            (
                CASE t.state
                WHEN '1' THEN '未完成'
                WHEN '2' THEN '进行中'
                ELSE '已完成'
                END
            )stateName
        FROM SM_PATROL_TASK T
            LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = t.project_id
            LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = CONCAT(arb."ad_guid",'000000')
            LEFT JOIN BLADE_USER u ON u.id = t.to_user_id
        arb."guid" AS reservoirId,
        arb."name" AS reservoirName,
        aab."ad_name" AS adName,
        aab."guid" AS adCode,
        t.start_time,
        u.real_name patrolUserName,
        t.state,
        (
        CASE t.state
        WHEN '1' THEN '未完成'
        WHEN '2' THEN '进行中'
        ELSE '已完成'
        END
        )stateName
        FROM YWXT.SM_PATROL_TASK T
        LEFT JOIN BLADE_USER u ON u.id = t.to_user_id
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = t.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 T.IS_DELETED = 0
        <if test="patrolTaskReport.adCode !=null and patrolTaskReport.adCode !=''">
            AND aab."guid" LIKE CONCAT('%',#{patrolTaskReport.adCode},'%')
            and (aab."ad_code" =#{patrolTaskReport.adCode} or aa."ad_code" = #{patrolTaskReport.adCode} or ab."ad_code" = #{patrolTaskReport.adCode} or ac."ad_code" = #{patrolTaskReport.adCode})
        </if>
        <if test="patrolTaskReport.reservoirName !=null and patrolTaskReport.reservoirName !=''">
            AND arb."name" LIKE CONCAT('%',#{patrolTaskReport.reservoirName},'%')
@@ -472,12 +478,15 @@
        <if test="patrolTaskReport.reservoirId !=null and patrolTaskReport.reservoirId !=''">
            AND arb."guid" =  #{patrolTaskReport.reservoirId}
        </if>
        <if test="patrolTaskReport.state != null and patrolTaskReport.state !='' ">
            and t.state = #{patrolTaskReport.state}
        </if>
    </select>


    <select id="getTaskStatusCount" resultType="cn.gistack.sm.patrol.vo.TaskStatusCount">

        SELECT '未完成' AS name,'1' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T2
        SELECT '未完成' AS name,'1' AS value , count(*) as taskCount FROM SM_PATROL_TASK T2
                    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T2.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"
@@ -497,7 +506,7 @@
                AND T2.reservoir_name LIKE CONCAT('%',#{vo.reservoirName},'%')
            </if>
        UNION ALL
        SELECT '进行中' AS name,'2' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T3
        SELECT '进行中' AS name,'2' AS value , count(*) as taskCount FROM SM_PATROL_TASK T3
                LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T3.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"
@@ -517,7 +526,7 @@
                    AND T3.reservoir_name LIKE CONCAT('%',#{vo.reservoirName},'%')
                </if>
        UNION ALL
        SELECT '已完成' AS name,'3' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T4
        SELECT '已完成' AS name,'3' AS value , count(*) as taskCount FROM SM_PATROL_TASK T4
                LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T4.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"
@@ -538,7 +547,7 @@
                </if>
        UNION ALL
            SELECT '全部' AS name,'' AS value , count(*) as taskCount
            FROM YWXT.SM_PATROL_TASK T1
            FROM SM_PATROL_TASK T1
            LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T1.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"

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

@@ -215,23 +215,14 @@

    @Override
    public IPage<PatrolTaskReport> getTaskReportPage(IPage<PatrolTaskReport> page,PatrolTaskReport patrolTaskReport) {
        List<PatrolTaskReport> list = new ArrayList();
        List<PatrolTaskReport> list  = new ArrayList<>();

        switch (patrolTaskReport.getRegionFlag()){
            case "1": //省  获取市级数据
                list = baseMapper.getCityReportPage(page,patrolTaskReport);
                break;
            case "2"://市   获取区县级数据
                list = baseMapper.getCountyReportPage(page,patrolTaskReport);
                break;
            case "3"://区县 获取乡镇级数据
                list = baseMapper.getTownReportPage(page,patrolTaskReport);
                break;
            case "4"://乡镇  获取水库数据
                list = baseMapper.getReservoirReportPage(page,patrolTaskReport);
                break;
            default:
                break;
        //获取行政区划统计报表
        if (patrolTaskReport.getRegionFlag().equals("1")){
            list = baseMapper.getRegionReportPage(page,patrolTaskReport);
        }else {
            //获取水库巡查报表
            list = baseMapper.getReservoirReportPage(page,patrolTaskReport);
        }
        return page.setRecords(list);
    }
@@ -240,21 +231,12 @@
    public List<PatrolTaskReport> getTaskReportList(PatrolTaskReport patrolTaskReport) {
        List<PatrolTaskReport> list = new ArrayList();

        switch (patrolTaskReport.getRegionFlag()){
            case "1": //省  获取市级数据
                list = baseMapper.getCityReportList(patrolTaskReport);
                break;
            case "2"://市   获取区县级数据
                list = baseMapper.getCountyReportList(patrolTaskReport);
                break;
            case "3"://区县 获取乡镇级数据
                list = baseMapper.getTownReportList(patrolTaskReport);
                break;
            case "4"://乡镇  获取水库数据
                list = baseMapper.getReservoirReportList(patrolTaskReport);
                break;
            default:
                break;
        //获取行政区划统计报表
        if (patrolTaskReport.getRegionFlag().equals("1")){
            list = baseMapper.getRegionReportList(patrolTaskReport);
        }else {
            //获取水库巡查报表
            list = baseMapper.getReservoirReportList(patrolTaskReport);
        }
        return list;
    }
@@ -273,20 +255,9 @@
    public IPage<PatrolTaskReport> getTaskReportDetail(IPage<PatrolTaskReport> page, PatrolTaskReport patrolTaskReport) {

        List<PatrolTaskReport> list = new ArrayList();
        if (Func.isNotEmpty(patrolTaskReport.getReservoirId())){
            //获取该水库的巡查
            list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);
        }else {
            String code = patrolTaskReport.getAdCode();
            if (code.substring(0,4).indexOf("00") > -1) {
                //前4位包含00 =》省
                patrolTaskReport.setAdCode(code.substring(0,2));
            } else if (code.substring(0,6).indexOf("00") > -1) {
                //前六位包含00 =》市
                patrolTaskReport.setAdCode(code.substring(0,4));
            }
            list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);
        }

        list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);

        return page.setRecords(list);
    }