<!--人员搜素分页-->
<select id="pageWithCustom" resultType="com.gxhj.safecommunity.person.entity.PersonInfo">
select pi.name as name,pi.age ,pi.person_id as personId,pi.gender_code as genderCode,pi.idcard as idcard
from (select (to_char(sysdate, 'yyyy') - substr(IDCARD, 7, 4)) as age, a.*
from Person_Info a) pi
<!--/*关键字搜索*/-->
<where>
<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} || '%')
</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 >= #{item.minAge} and pi.age <= #{item.maxAge})
</foreach>
</if>
<!-- 实有人口管理类别（SYRKGLLBDM）-人员类型统计 -->
<!-- <if test="personInfo.personManageType != null and personInfo.personManageType!= ''">-->
<!-- and pi.person_manage_type = #{personInfo.personManageType}-->
<!-- </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.attentionTypeCode != null and personInfo.attentionTypeCode!= ''">
and (exists (select * from person_attentionperson pa where pa.attention_type_code=
#{personInfo.attentionTypeCode} and pa.person_id = pi.person_id))
</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.nativeAdminAreaCode != null and personInfo.nativeAdminAreaCode!= ''">-->
<!-- and substr(pi.native_admin_area_code,0,2) = #{personInfo.nativeAdminAreaCode}-->
<!-- </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_house h
where village_id = #{personInfo.villageId}
and h.owner_person_id = pi.person_id)
or 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>
</where>
</select>
<?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="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
from
Person_Info pi where pi.NATIVE_ADMIN_AREA_CODE is not null)
group by code
order by count(1) asc
</select>
<!--统计年零范围-->
<select id="queryCountGroupByAge" resultType="com.gxhj.safecommunity.person.entity.PersonInfo">
select count(1) as PERSON_ID, age as NAME
from (select (to_char(sysdate, 'yyyy') - substr(IDCARD, 7, 4)) as age
from Person_Info)
group by age
order by age asc
</select>
<!--根据关注人员类型统计-->
<select id="queryCountGroupByAttentionType" resultType="java.util.Map">
select count(pa.attention_type_code) as COUNT ,
pa.attention_type_code as NAME
from PERSON_INFO pi
join Person_Attentionperson pa
on pi.person_id = pa.person_id
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 as name,pi.age ,pi.person_id as personId,pi.gender_code as genderCode,pi.idcard as idcard
from (select (to_char(sysdate, 'yyyy') - substr(IDCARD, 7, 4)) as age, a.*
from Person_Info a) pi
<!--/*关键字搜索*/-->
<where>
<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} || '%')
</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 >= #{item.minAge} and pi.age <= #{item.maxAge})
</foreach>
</if>
<!-- 实有人口管理类别（SYRKGLLBDM）-人员类型统计 -->
<!-- <if test="personInfo.personManageType != null and personInfo.personManageType!= ''">-->
<!-- and pi.person_manage_type = #{personInfo.personManageType}-->
<!-- </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.attentionTypeCode != null and personInfo.attentionTypeCode!= ''">
and (exists (select * from person_attentionperson pa where pa.attention_type_code=
#{personInfo.attentionTypeCode} and pa.person_id = pi.person_id))
</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.nativeAdminAreaCode != null and personInfo.nativeAdminAreaCode!= ''">-->
<!-- and substr(pi.native_admin_area_code,0,2) = #{personInfo.nativeAdminAreaCode}-->
<!-- </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_house h
where village_id = #{personInfo.villageId}
and h.owner_person_id = pi.person_id)
or 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>
</where>
</select>
<!--查询人员关联信息-->
<select id="queryAllRelationById" resultType="com.gxhj.safecommunity.person.vo.RelationVo">
<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
from person_info pi right join person_relation pr on pi.person_id = pr.sub_people_id
<where>
and pr.main_people_id = #{id}
and pr.relation_name in
<foreach collection="relationType" item="item" separator="," open="(" close=")" index="">
#{item}
</foreach>
</where>
</if>
<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
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>
<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
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 >= 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>
<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 from vehicle_info v
where v.person_id = #{id}
</if>
<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.ADDRESS_NAME || vh.CODE as name, '' as sex, '' as idcard, '07' as queryType, 2
as dataType
from VILLAGE_HOUSE vh left join village_info v on vh.village_id = v.village_id
where vh.owner_person_id = #{id}
</if>
</select>
</mapper>
网友评论