mapper 中将条件整合成引用

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gxhj.safecommunity.person.mapper.PersonMapper">


    <!--=================获取字典===================-->
    <!--人员年龄段字典-->
    <select id="queryDicItemByAge" resultType="java.util.Map">

        select CASE
        WHEN age &lt; 14 THEN
        '1:0-13'
        WHEN age BETWEEN 14 AND 17 THEN
        '2:14-17'
        WHEN age BETWEEN 18 AND 29 THEN
        '3:18-29'
        WHEN age BETWEEN 30 AND 44 THEN
        '4:30-44'
        WHEN age BETWEEN 45 AND 59 THEN
        '5:45-59'
        WHEN age BETWEEN 60 AND 74 THEN
        '6:60-74'
        WHEN age > 74 THEN
        '7:75-999'
        END AS agesope
        from (select age

        from (SELECT nvl(TRUNC(months_between(sysdate, pi.birthday) / 12), 0) AS age
        from Person_Info pi where 1=1
        <include refid="villageIdWhere"></include>)
        group by age
        order by age asc)
        GROUP BY CASE
        WHEN age &lt; 14 THEN
        '1:0-13'
        WHEN age BETWEEN 14 AND 17 THEN
        '2:14-17'
        WHEN age BETWEEN 18 AND 29 THEN
        '3:18-29'
        WHEN age BETWEEN 30 AND 44 THEN
        '4:30-44'
        WHEN age BETWEEN 45 AND 59 THEN
        '5:45-59'
        WHEN age BETWEEN 60 AND 74 THEN
        '6:60-74'
        WHEN age > 74 THEN
        '7:75-999'
        END
        order by agesope

    </select>


    <!--=================获取字典===================-->
    <!--人员省份统计-->
    <select id="queryCountGroupByProvince" resultType="java.util.Map">
        select count(1) as COUNT, code as NAME
        from (select ( substr(pi.NATIVE_ADMIN_AREA_CODE, 0, 2)) as code,pi.NATIVE_ADMIN_AREA_CODE
        <include refid="queryTable"></include>

        <where>
            pi.NATIVE_ADMIN_AREA_CODE is not null
            <include refid="queryWhere"></include>
        </where>
        )
        group by code
        order by count(1) desc
    </select>


    <!--统计年龄范围-->
    <select id="queryCountGroupByAge" resultType="com.gxhj.safecommunity.person.entity.PersonInfo">
        select count(1) as PERSON_ID, age as NAME
        <include refid="queryTable"></include>
        <where>
            1=1
            <include refid="queryWhere"></include>
        </where>
        group by age
        order by age asc
    </select>

    <!--统计人员类型范围-->
    <select id="queryCountGroupByType" resultType="com.gxhj.safecommunity.vo.CountVo">
        select count(1) as count, PERSON_MANAGE_TYPE as name
        <include refid="queryTable"></include>
        <where>
            1=1
            <include refid="queryWhere"></include>
        </where>
        group by PERSON_MANAGE_TYPE
        order by PERSON_MANAGE_TYPE asc
    </select>

    <!--根据关注人员类型统计-->
    <select id="queryCountGroupByAttentionType" resultType="java.util.Map">
        select count(pa.attention_type_code) as COUNT ,
        pa.attention_type_code as NAME
        <include refid="queryTable"></include>
        join Person_Attentionperson pa
        on pi.person_id = pa.person_id
        <where>
            1=1
            <include refid="queryWhere"></include>
        </where>
        group by pa.attention_type_code
        order by count(pa.attention_type_code) desc
    </select>

    <!--人员搜索分页-->
    <select id="pageWithCustom" resultType="com.gxhj.safecommunity.person.entity.PersonInfo">

        select pi.name, pi.age, pi.person_id, pi.gender_code, pi.idcard, pi.contact_number, pi.current_address,
        pi.ETHNICITY_CODE,
        pi.PERSON_MANAGE_TYPE, b.villageNames as village_id, b.police_station_code, b.liability_zone_code,
        b.sub_bureau_code,
        b.bureau_code
        <include refid="queryTable"></include>
        <where>
            1=1
            <include refid="queryWhere"></include>
        </where>
    </select>

    <!-- 分页查询的表字段-->
    <sql id="queryTable">
        from (select nvl(TRUNC(months_between(sysdate, a.birthday) / 12), 0) as age, a.* from Person_Info a) pi
        left join (
            select wm_concat(vi1.police_station_code) as police_station_code, wm_concat(vi1.bureau_code) as bureau_code,
            wm_concat(vi1.sub_bureau_code) as sub_bureau_code, wm_concat(vi1.liability_zone_code) as liability_zone_code,
            wm_concat(vi1.name) as villageNames, vhp.person_id
            from village_info vi1
            left join (
                select village_id, owner_person_id as person_id from village_house union
                select village_id, person_id from Village_Personhouserelation
            ) vhp
            on vi1.village_id = vhp.village_id
            where vhp.person_id is not null
            group by vhp.person_id
         ) b
         on pi.person_id = b.person_id
    </sql>
    <!-- 分页查询条件-->
    <sql id="queryWhere">
        <!--关键词-->
        <if test="personInfo.key != null and personInfo.key!= ''">
            and (pi.name like '%' || #{personInfo.key} || '%' or pi.idcard like '%' || #{personInfo.key} || '%' or
            pi.current_address like '%' || #{personInfo.key} || '%'
            or (exists (select *
            from village_house h
            where exists (select *
            from village_info vi
            where vi.name like '%' || #{personInfo.key} || '%'
            and vi.village_id = h.village_id)
            and h.owner_person_id = pi.person_id)
            or exists
            (select *
            from village_personhouserelation r
            where exists
            (select *
            from village_info vi
            where vi.name like '%' || #{personInfo.key} || '%'
            and vi.village_id = r.village_id)
            and r.person_id = pi.person_id
            and (departure_date is null or departure_date >= sysdate))
            ))
        </if>

        <!--自定义标签-->
        <if test="personInfo.queryCustomTagList != null and personInfo.queryCustomTagList.size() > 0 ">
            and exists
            (select st.main_id
            from sys_tag st
            where pi.person_id = st.main_id
            and
            <foreach collection="personInfo.queryCustomTagList" item="item" open="(" separator=" or " close=")"
                     index="index">
                st.tag_name like '%' || #{item} || '%'
            </foreach>
            )
        </if>
        <!--性别-->
        <if test="personInfo.querySexList != null and personInfo.querySexList.size() > 0 ">
            and pi.gender_code in
            <foreach collection="personInfo.querySexList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--民族-->
        <if test="personInfo.queryNationList != null and personInfo.queryNationList.size() > 0 ">
            and pi.ethnicity_code in
            <foreach collection="personInfo.queryNationList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--文化程度-->
        <if test="personInfo.queryEductionList != null and personInfo.queryEductionList.size() > 0 ">
            and pi.education_code in
            <foreach collection="personInfo.queryEductionList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>

        <!--宗教信仰-->
        <if test="personInfo.queryFaithList != null and personInfo.queryFaithList.size > 0 ">
            and pi.faith_code in
            <foreach collection="personInfo.queryFaithList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--国籍-->
        <if test="personInfo.queryNationalityList != null and personInfo.queryNationalityList.size() > 0 ">
            and pi.nationality_code in
            <foreach collection="personInfo.queryNationalityList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--政治面貌-->
        <if test="personInfo.queryPoliticalStatusList != null and personInfo.queryPoliticalStatusList.size() > 0 ">
            and pi.political_status_code in
            <foreach collection="personInfo.queryPoliticalStatusList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--是否重点人员 0false 1 true-->
        <!--否重点人员 0-->
        <if test="personInfo.stakeholderNum != null  and personInfo.stakeholderNum==0">
            and (not exists (select * from person_attentionperson pa where pa.person_id = pi.person_id))
        </if>
        <!--是重点人员 1-->
        <if test="personInfo.stakeholderNum != null  and personInfo.stakeholderNum==1">
            and (exists (select * from person_attentionperson pa where pa.person_id = pi.person_id))
        </if>
        <!--年龄段-->
        <if test="personInfo.lstAgeScope != null and personInfo.lstAgeScope.size() > 0 ">
            and
            <foreach collection="personInfo.lstAgeScope" item="item" open="(" separator=" or " close=")"
                     index="index">
                ( pi.age &gt;= #{item.minAge} and pi.age &lt;= #{item.maxAge})
            </foreach>
        </if>
        <!--人员类型-->
        <if test="personInfo.queryPersonManageTypeList != null and personInfo.queryPersonManageTypeList.size() > 0 ">
            and pi.person_manage_type in
            <foreach collection="personInfo.queryPersonManageTypeList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
        </if>
        <!--重点人员-->
        <if test="personInfo.queryAttentionTypeCodeList != null and personInfo.queryAttentionTypeCodeList.size()>0 ">
            and (exists (select * from person_attentionperson pa where pa.attention_type_code in
            <foreach collection="personInfo.queryAttentionTypeCodeList" item="item" open="(" separator="," close=")"
                     index="index">
                #{item}
            </foreach>
            and pa.person_id = pi.person_id))
        </if>
        <!--省份-->
        <if test="personInfo.queryNativeAdminAreaCodeList != null and personInfo.queryNativeAdminAreaCodeList.size() > 0 ">
            and substr(pi.native_admin_area_code,0,2) in
            <foreach collection="personInfo.queryNativeAdminAreaCodeList" item="item" open="(" separator=","
                     close=")" index="index">
                #{item}
            </foreach>
        </if>
        <!--小区-->
        <if test="personInfo.villageId != null and personInfo.villageId!= ''">
            and ( exists (select *
            from village_personhouserelation r
            where r.village_id = #{personInfo.villageId}
            and r.person_id = pi.person_id and (departure_date is null or departure_date >= sysdate) ) )
        </if>
        <!--租客-->
        <if test="personInfo.queryTenantList != null and personInfo.queryTenantList!= '' and  personInfo.queryTenantList == 3 ">
            and ( exists (select *
            from Village_Personhouserelation vph
            where (vph.departure_date is null or departure_date >= sysdate)
            and EXISTS (select *
            from VILLAGE_HOUSE vh
            where live_type = '4'
            and vph.person_id != vh.owner_person_id)
            and vph.person_id = pi.person_id))
        </if>
        <!--访客-->
        <if test="personInfo.queryVisitorList  != null and personInfo.queryVisitorList != '' and  personInfo.queryVisitorList  == 6 ">
            and (exists (select pv.visitor_person_id
            from perception_visitor pv
            where pi.person_id = pv.visitor_person_id))
        </if>

        <!--警务责任区 lstBureauCode-->
        <if test="personInfo.getQueryPoliceCodes != null and personInfo.getQueryPoliceCodes.size() > 0 ">
            and(
            <!--市局-->
            <if test="personInfo.lstBureauCode!= null and personInfo.lstBureauCode.size() > 0">
                <foreach collection="personInfo.lstBureauCode" item="item" open="(" separator=" or "
                         close=")" index="index">
                    b.bureau_code like '%' || #{item} || '%'
                </foreach>
            </if>
            <!--县局 lstSubBureauCode-->
            <if test="personInfo.lstSubBureauCode!= null and personInfo.lstSubBureauCode.size() > 0">
                <foreach collection="personInfo.lstSubBureauCode" item="item" open="(" separator=" or "
                         close=")" index="index">
                    b.sub_bureau_code like '%' || #{item} || '%'
                </foreach>
            </if>
            <!--派出所 lstPoliceStationCodes-->
            <if test="personInfo.lstPoliceStationCodes!= null and personInfo.lstPoliceStationCodes.size() > 0">
                <foreach collection="personInfo.lstPoliceStationCodes" item="item" open="(" separator=" or "
                         close=")" index="index">
                    b.police_station_code like '%' || #{item} || '%'
                </foreach>
            </if>

            <!--警务责任区 lstLiabilityZoneCodes-->
            <if test="personInfo.lstLiabilityZoneCodes!= null and personInfo.lstLiabilityZoneCodes.size() > 0">
                <foreach collection="personInfo.lstLiabilityZoneCodes" item="item" open="(" separator=" or "
                         close=")" index="index">
                    b.liability_zone_code like '%' || #{item} || '%'
                </foreach>
            </if>
            <!--            &lt;!&ndash;市局&ndash;&gt;-->
            <!--            <if test="personInfo.lstBureauCode!= null and personInfo.lstBureauCode.size() > 0">-->
            <!--                pi.bureau_code in-->
            <!--                <foreach collection="personInfo.lstBureauCode" item="item" open="(" separator=","-->
            <!--                         close=")" index="index">-->
            <!--                    #{item}-->
            <!--                </foreach>-->
            <!--                <if test="(personInfo.lstSubBureauCode!= null and personInfo.lstSubBureauCode.size() > 0) or (personInfo.lstPoliceStationCodes!= null and personInfo.lstPoliceStationCodes.size() > 0) or (personInfo.lstLiabilityZoneCodes!= null and personInfo.lstLiabilityZoneCodes.size() > 0)">-->
            <!--                    or-->
            <!--                </if>-->
            <!--            </if>-->
            <!--            &lt;!&ndash;县局 lstSubBureauCode&ndash;&gt;-->
            <!--            <if test="personInfo.lstSubBureauCode!= null and personInfo.lstSubBureauCode.size() > 0">-->
            <!--                pi.sub_bureau_code in-->
            <!--                <foreach collection="personInfo.lstSubBureauCode" item="item" open="(" separator=","-->
            <!--                         close=")" index="index">-->
            <!--                    #{item}-->
            <!--                </foreach>-->

            <!--                <if test="(personInfo.lstPoliceStationCodes!= null and personInfo.lstPoliceStationCodes.size() > 0) or (personInfo.lstLiabilityZoneCodes!= null and personInfo.lstLiabilityZoneCodes.size() > 0)">-->
            <!--                    or-->
            <!--                </if>-->
            <!--            </if>-->
            <!--            &lt;!&ndash;派出所 lstPoliceStationCodes&ndash;&gt;-->
            <!--            <if test="personInfo.lstPoliceStationCodes!= null and personInfo.lstPoliceStationCodes.size() > 0">-->
            <!--                pi.police_station_code in-->
            <!--                <foreach collection="personInfo.lstPoliceStationCodes" item="item" open="(" separator=","-->
            <!--                         close=")" index="index">-->
            <!--                    #{item}-->
            <!--                </foreach>-->
            <!--                <if test="(personInfo.lstLiabilityZoneCodes!= null and personInfo.lstLiabilityZoneCodes.size() > 0">-->
            <!--                    or-->
            <!--                </if>-->
            <!--            </if>-->

            <!--            &lt;!&ndash;警务责任区 lstLiabilityZoneCodes&ndash;&gt;-->
            <!--            <if test="personInfo.lstLiabilityZoneCodes!= null and personInfo.lstLiabilityZoneCodes.size() > 0">-->
            <!--                pi.liability_zone_code in-->
            <!--                <foreach collection="personInfo.lstLiabilityZoneCodes" item="item" open="(" separator=","-->
            <!--                         close=")" index="index">-->
            <!--                    #{item}-->
            <!--                </foreach>-->
            <!--            </if>-->
            )
        </if>
    </sql>
    <!-- 小区id查询条件-->
    <sql id="villageIdWhere">
        <if test="villageId != null and villageId != ''">
            and ( exists (select *
            from village_personhouserelation r
            where r.village_id = #{villageId}
            and r.person_id = pi.person_id and (departure_date is null or departure_date >= sysdate) ) )
        </if>
    </sql>

    <!--查询人员关联信息-->
    <select id="queryAllRelationById" resultType="com.gxhj.safecommunity.person.vo.RelationVo">
        <!-- 人与人关联 01 同户，02 配偶 03 同行-->
        <if test="relationType.size > 0">
            select pi.person_id as id, pi.name, pi.gender_code as sex, pi.IDCARD as idcard, pr.relation_name as
            queryType, 0 as dataType , '' as relation
            from person_info pi right join person_relation pr on pi.person_id = pr.sub_people_id
            <where>
                and (pr.main_people_id = #{id} or pr.SUB_PEOPLE_ID = #{id})
                and pr.relation_name in
                <foreach collection="relationType" item="item" separator="," open="(" close=")" index="">
                    #{item}
                </foreach>
            </where>
        </if>

        <!-- 租客或房租 04 同住 -->
        <if test="type.contains('04')">
            <if test="type.contains('04') and relationType.size > 0">
                union all
            </if>
            select v1.person_id as id, p.name, p.GENDER_CODE as sex, p.IDCARD as idcard, '04' as queryType, 0 as
            dataType ,v1.OWNER_RELATION_CODE as relation
            from VILLAGE_PERSONHOUSERELATION v1 left join person_info p on v1.person_id = p.person_id
            where
            exists ( select v2.house_id from VILLAGE_PERSONHOUSERELATION v2
            where v2.person_id = #{id} and v2.house_id = v1.house_id and (v2.departure_date is null or v2.departure_date
            >= sysdate))
            or exists ( select v3.house_id from village_house v3 where v3.owner_person_id = #{id} and v3.house_id =
            v1.house_id)
        </if>

        <!-- 查询访客 05 访客 -->
        <if test="type.contains('05')">
            <if test="type.contains('05') and (relationType.size > 0 or type.contains('04'))">
                union all
            </if>
            select pi5.person_id as id, pi5.name, pi5.GENDER_CODE as sex, pi5.IDCARD as idcard, '05' as queryType, 0
            as dataType , '' as relation
            from PERCEPTION_VISITOR pv5 left join person_Info pi5 on pv5.visitor_person_id = pi5.person_id
            where
            exists ( select v2.house_id from VILLAGE_PERSONHOUSERELATION v2 where v2.person_id = #{id} and v2.house_id =
            pv5.access_house_id and (v2.departure_date is null or v2.departure_date &gt;= sysdate))
            or exists ( select v3.house_id from village_house v3 where v3.owner_person_id = #{id} and v3.house_id =
            pv5.access_house_id)
        </if>

        <!-- 查询车辆 06 车辆 -->
        <if test="type.contains('06')">
            <if test="type.contains('06') and (relationType.size > 0 or type.contains('04') or type.contains('05'))">
                union all
            </if>
            select v.vehicle_id as id, v.license_plate as name, '' as sex, '' as idcard, '06' as queryType, 1 as
            dataType , '' as relation from vehicle_info v
            where v.person_id = #{id}
        </if>

        <!-- 查询房屋 07 房屋 -->
        <if test="type.contains('07')">
            <if test="type.contains('07') and (relationType.size > 0 or type.contains('04') or type.contains('05') or type.contains('06'))">
                union all
            </if>
            select vh.house_id as id, v.NAME || vh.CODE as name, '' as sex, '' as idcard, '07' as queryType, 2
            as dataType , '' as relation
            from VILLAGE_HOUSE vh left join village_info v on vh.village_id = v.village_id
            where vh.owner_person_id = #{id}
        </if>

    </select>

    <!--  通过字段分组  -->
    <select id="queryGroupByField" resultType="java.lang.String">
        select ${key} as name
        from PERSON_INFO pi
        <where>
            1=1
            <include refid="villageIdWhere"></include>
        </where>
        group by ${key}
    </select>

    <!--  通过查询小区中，人员总数和重点人员总数  -->
    <select id="queryAttentionCountByField" resultType="java.util.Map">
        select sum((select count(1) from person_attentionperson p where p.person_id = pi.person_id)) attentionSum, sum(1) as personSum from person_info pi
        <where>
            1=1
            <include refid="villageIdWhere"></include>
        </where>

    </select>

    <!-- 通过小区id，查询所有派出所代码 -->
    <select id="queryPoliceStationCodeByVillageId" resultType="java.lang.String">
        select police_station_code from (
        select a.police_station_code, pi.person_id from person_info pi left join (
        select vh.village_id, vi.police_station_code, vh.owner_person_id as person_id from village_house vh left join
        village_info vi on vh.village_id = vi.village_id
        union
        select vp.village_id, vi.police_station_code, vp.person_id from village_personhouserelation vp left join
        village_info vi on vp.village_id = vi.village_id
        ) a on pi.person_id = a.person_id
        where a.police_station_code is not null
        ) pi
        <where>
            1=1
            <include refid="villageIdWhere"></include>
            <if test="personId != null and personId != ''">
                and pi.person_id = #{personId}
            </if>
        </where>
        group by police_station_code
    </select>


</mapper>