堤防普查，白蚁普查列表

guoshilong

2024-03-18 af100201b0e8cbde2194651b12a136e2daba7f18

堤防普查，白蚁普查列表

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbDykeInvestigationController.java

@@ -19,6 +19,7 @@
import org.springframework.web.bind.annotation.*;
import springfox.documentation.annotations.ApiIgnore;

import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;
@@ -102,8 +103,8 @@

    @PostMapping("/remove")
    @ApiOperation(value = "物理删除", notes = "传入id")
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String id) {
        return R.status(tbDykeInvestigationService.removeById(id));
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids) {
        return R.status(tbDykeInvestigationService.removeBatchByIds(Arrays.asList(ids)));
    }

    @PostMapping("/getInvestigationListByStateId")

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbResGeneralInvestigationController.java

@@ -21,6 +21,7 @@
import org.springframework.web.bind.annotation.*;
import springfox.documentation.annotations.ApiIgnore;

import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
@@ -105,8 +106,8 @@

    @PostMapping("/remove")
    @ApiOperation(value = "物理删除", notes = "传入id")
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String id) {
        return R.status(tbResGeneralInvestigationService.removeById(id));
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids) {
        return R.status(tbResGeneralInvestigationService.removeBatchByIds(Arrays.asList(ids)));
    }

    @PostMapping("/getInvestigationListByStateId")

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml

@@ -13,12 +13,46 @@

    <select id="selectAttResBaseGeneralInvestigation"
            resultType="cn.gistack.sm.sjztmd.vo.AttResBaseGeneralInvestigationVO">
        select a.*,b."check_state" AS tb_state,b."tb_year",b."tb_quarter" from sjzt_md."att_res_base" a
        select
            a.*,b."check_state" AS tb_state,
            b."tb_year",
            b."tb_quarter",
            tmp.totalLength,
            tmp.firstLength,
            tmp.secondLength,
            tmp.thirdLength,

            tmp.totalDangerNum,
            tmp.termiteNum,
            tmp.otherNum
        from sjzt_md."att_res_base" a
        left join "SJZT_MD"."att_ad_base" aab on aab."guid" = a."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"
        left join "SJZT_MD"."tb_res_general_investigation_state" b on a."code" = b."res_guid"

        left join

        (
            SELECT TRGI."tb_state_id",TRGI."res_guid",
            SUM(CASE WHEN trgi."hazard_rating" != '无危害' THEN 1 ELSE 0 END) AS totalLength,

            SUM(CASE WHEN trgi."hazard_rating" = 'I' THEN trgi."investigation_length" ELSE 0 END) AS firstLength,
            SUM(CASE WHEN trgi."hazard_rating" = 'II' THEN trgi."investigation_length" ELSE 0 END) AS secondLength,
            SUM(CASE WHEN trgi."hazard_rating" = 'III' THEN trgi."investigation_length" ELSE 0 END) AS thirdLength,

            SUM(CASE WHEN (trgi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS totalDangerNum,
            SUM(CASE WHEN (trgi."investigation_variety" = '白蚁' and trgi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS termiteNum,
            SUM(CASE WHEN (trgi."investigation_variety" != '白蚁' and trgi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS otherNum
            from sjzt_md."tb_res_general_investigation" trgi
            <if test="attResBase.tbYear !=null and attResBase.tbYear !=''">
                AND trgi."tb_year" = #{attResBase.tbYear}
            </if>
            group by TRGI."tb_state_id",TRGI."res_guid"

        ) tmp on tmp."tb_state_id" = b."guid"

        where 1 = 1
        <if test="attResBase.tbYear !=null and attResBase.tbYear !=''">
            AND b."tb_year" = #{attResBase.tbYear}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml

@@ -61,14 +61,55 @@
    <!--  堤防普查列表  -->
    <select id="findTbResDykePcBy"
            resultType="cn.gistack.sm.sjztmd.vo.TbResDykeVO">
        select DISTINCT a.*,b."check_state" AS tb_state,aa."p_ad_code" as pAdCode,
        aa."p_ad_name" as pAdName from sjzt_md."tb_res_dyke" a
        select
        DISTINCT
        a.*,
        b."check_state" AS tb_state,
        aa."p_ad_code" as pAdCode,
        aa."p_ad_name" as pAdName,

        tmp.totalLength,
        tmp.firstLength,
        tmp.secondLength,
        tmp.thirdLength,

        tmp.totalDangerNum,
        tmp.termiteNum,
        tmp.otherNum

        from sjzt_md."tb_res_dyke" a
        left join "SJZT_MD"."tb_dyke_investigation_state" b on a."guid" = b."dyke_guid"
        left join "SJZT_MD"."att_ad_base" aa on aa."guid" = a."ad_code"
        left join "SJZT_MD"."att_ad_base" ab on ab."p_ad_code" = aa."ad_code"
        left join "SJZT_MD"."att_ad_base" ac on ac."p_ad_code" = ab."ad_code"
        left join "SJZT_MD"."att_ad_base" ad on ad."p_ad_code" = ac."ad_code"

        left join

        (
        SELECT tdi."tb_state_id",tdi."dyke_guid",
        SUM(CASE WHEN tdi."hazard_rating" != '无危害' THEN 1 ELSE 0 END) AS totalLength,

        SUM(CASE WHEN tdi."hazard_rating" = 'I' THEN tdi."investigation_length" ELSE 0 END) AS firstLength,
        SUM(CASE WHEN tdi."hazard_rating" = 'II' THEN tdi."investigation_length" ELSE 0 END) AS secondLength,
        SUM(CASE WHEN tdi."hazard_rating" = 'III' THEN tdi."investigation_length" ELSE 0 END) AS thirdLength,

        SUM(CASE WHEN (tdi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS totalDangerNum,
        SUM(CASE WHEN (tdi."investigation_variety" = '白蚁' and tdi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS termiteNum,
        SUM(CASE WHEN (tdi."investigation_variety" != '白蚁' and tdi."hazard_rating" != '无危害') THEN 1 ELSE 0 END) AS otherNum
        from sjzt_md."tb_dyke_investigation" tdi
        <if test="vo.tbYear !=null and vo.tbYear !=''">
            AND tdi."tb_year" = #{vo.tbYear}
        </if>
        group by tdi."tb_state_id",tdi."dyke_guid"

        ) tmp on tmp."tb_state_id" = b."guid"


        where 1 = 1
        <if test="vo.tbYear !=null and vo.tbYear !=''">
            AND tdi."tb_year" = #{vo.tbYear}
        </if>
        <if test="vo.name !=null and vo.name !=''">
            AND a."name" LIKE CONCAT('%',#{vo.name},'%')
        </if>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/AttResBaseGeneralInvestigationVO.java

@@ -21,4 +21,27 @@

    private String tbQuarter;//填报季度

    //危害总普查长度
    private double totalLength;

    //一级危害普查长度
    private double firstLength;

    //二级危害普查长度
    private double secondLength;

    //三级危害普查长度
    private double thirdLength;

    //危害总处数
    private int totalDangerNum;

    //白蚁危害处数
    private int termiteNum;

    //其他危害处数
    private int otherNum;



}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/TbResDykeVO.java

@@ -14,5 +14,28 @@

    private String tbState;//填报状态

    private Integer tbYear;//填报年度

    //危害总普查长度
    private double totalLength;

    //一级危害普查长度
    private double firstLength;

    //二级危害普查长度
    private double secondLength;

    //三级危害普查长度
    private double thirdLength;

    //危害总处数
    private int totalDangerNum;

    //白蚁危害处数
    private int termiteNum;

    //其他危害处数
    private int otherNum;


}