package com.panzhihua.service_user.dao;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO;
|
import com.panzhihua.common.model.dtos.community.ExportUserDTO;
|
import com.panzhihua.common.model.dtos.user.EexcelUserDTO;
|
import com.panzhihua.common.model.dtos.user.PageInputUserDTO;
|
import com.panzhihua.common.model.vos.community.ComMngStructAreaVO;
|
import com.panzhihua.common.model.vos.community.ComMngStructHouseVO;
|
import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO;
|
import com.panzhihua.common.model.vos.user.InputUserInfoVO;
|
import com.panzhihua.service_user.model.dos.SysUserInputDO;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.List;
|
|
/**
|
* @program: springcloud_k8s_panzhihuazhihuishequ
|
* @description: 居民导入
|
* @author: huang.hongfa weixin hhf9596 qq 959656820
|
* @create: 2021-02-05 17:40
|
**/
|
@Mapper
|
public interface SysUserInputDAO extends BaseMapper<SysUserInputDO> {
|
// @Select("<script> " +
|
// " <where>" +
|
// "<if test='nameCn != null and nameCn.trim() != ""'>" +
|
// " </if> " +
|
// "<if test='nameCn != null and nameCn.trim() != ""'>" +
|
// " </if> " +
|
// "<if test='nameCn != null and nameCn.trim() != ""'>" +
|
// " </if> " +
|
// " </where>" +
|
// "</script>")
|
@Select("select a.id,a.community_id from com_mng_struct_area a where a.area_name=#{areaName}")
|
ComMngStructAreaVO selectByAreaName(String areaName);
|
@Select("SELECT " +
|
"REPLACE(REPLACE(REPLACE(REPLACE(h.house_name,a.address_detail,''),'栋','-'),'单元','-'),'层','-')houseName, " +
|
"h.house_code " +
|
"FROM " +
|
" com_mng_struct_area a " +
|
" join com_mng_struct_house h on h.house_name like CONCAT(a.address_detail,'%') and h.type=5 " +
|
"WHERE " +
|
" a.area_name = #{areaName}")
|
List<ComMngStructHouseVO> selectHouserByareaName(String areaName);
|
@Select("SELECT " +
|
"p.name, " +
|
"p.id, " +
|
"if(p.is_rent=1,'合租','家人')relationship, " +
|
"p.id_card, " +
|
"p.phone, " +
|
"year(curdate())-if(length(p.id_card)=18,substring(p.id_card,7,4),if(length(p.id_card)=15,concat('19',substring(p.id_card,7,2)),null)) as age, " +
|
"p.create_at " +
|
"FROM " +
|
" sys_user_input p " +
|
"WHERE " +
|
" p.door_number = #{doorNumber} " +
|
" AND p.id !=#{userId}")
|
List<ComMngFamilyInfoVO> selectListFamily(@Param("doorNumber") String doorNumber, @Param("userId")Long userId);
|
|
@Select("<script> " +
|
"SELECT " +
|
" t.id, " +
|
" t.`name`, " +
|
"CASE " +
|
" IF " +
|
" ( " +
|
" length( t.id_card ) = 18, " +
|
" cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " +
|
" IF " +
|
" ( " +
|
" length( t.id_card ) = 15, " +
|
" cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " +
|
" 3 " +
|
" ) " +
|
" ) " +
|
" WHEN 1 THEN " +
|
" 1 " +
|
" WHEN 0 THEN " +
|
" 2 ELSE 0 " +
|
" END AS sex, " +
|
" t.phone, " +
|
" YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
|
" t1.area_name as areaName, " +
|
" t.political_outlook politicalOutlook, " +
|
" CONCAT( " +
|
" IF " +
|
" ( t.soldier = 1, '军人,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_households = 1, '低保户,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_people = 1, '低收入,', '' ), " +
|
" IF " +
|
" ( t.old_people = 1, '特服家庭,', '' ), " +
|
" IF " +
|
" ( t.key_personnel = 1, '重点人员,', '' ) " +
|
" ) tags, " +
|
" t.create_at as createAt " +
|
"FROM " +
|
" sys_user_input t " +
|
" LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " +
|
"WHERE " +
|
" t.community_id = #{pageInputUserDTO.communityId}"+
|
"<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != ""'>" +
|
" and t.`name` like concat(#{pageInputUserDTO.name},'%') " +
|
" </if> " +
|
"<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" +
|
" and t.phone like concat(#{pageInputUserDTO.phone},'%') " +
|
" </if> " +
|
"<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" +
|
"AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " +
|
" </if> " +
|
" group by t.id_card "+
|
"</script>")
|
IPage<InputUserInfoVO> pageInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);
|
|
@Select("select a.id,a.community_id,a.area_name,address_detail from com_mng_struct_area a where a.community_id=#{communityId}")
|
List<ComMngStructAreaVO> selectComMngStructArea(@Param("communityId") Long communityId);
|
|
@Select("<script> " +
|
"SELECT * FROM ( "+
|
" SELECT " +
|
" t.id, " +
|
" t.`name`, " +
|
"CASE " +
|
" IF " +
|
" ( " +
|
" length( t.id_card ) = 18, " +
|
" cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " +
|
" IF " +
|
" ( " +
|
" length( t.id_card ) = 15, " +
|
" cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " +
|
" 3 " +
|
" ) " +
|
" ) " +
|
" WHEN 1 THEN " +
|
" 1 " +
|
" WHEN 0 THEN " +
|
" 2 ELSE 0 " +
|
" END AS sex, " +
|
" t.phone, " +
|
" YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
|
" t1.area_name as areaName, " +
|
" t.political_outlook politicalOutlook, " +
|
" CONCAT( " +
|
" IF " +
|
" ( t.soldier = 1, '军人,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_households = 1, '低保户,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_people = 1, '低收入,', '' ), " +
|
" IF " +
|
" ( t.old_people = 1, '特服家庭,', '' ), " +
|
" IF " +
|
" ( t.key_personnel = 1, '重点人员,', '' ), " +
|
" IFNULL(t.tags,'') " +
|
" ) tags, " +
|
" t2.house_name as address, " +
|
" t.create_at as createAt " +
|
"FROM " +
|
" sys_user_input t " +
|
" LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " +
|
" LEFT JOIN com_mng_struct_house t2 ON t.house_code = t2.house_code " +
|
"WHERE " +
|
" t.community_id = #{pageInputUserDTO.communityId}"+
|
" AND " +
|
"(t.soldier=1 OR t.lowIncome_households=1 OR t.lowIncome_people=1 or t.old_people=1 OR t.special_service_family=1 OR t.key_personnel=1 OR (t.tags IS NOT NULL AND TRIM(t.tags)!=''))"+
|
"<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != ""'>" +
|
" and t.`name` like concat(#{pageInputUserDTO.name},'%') " +
|
" </if> " +
|
"<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" +
|
" and t.phone like concat(#{pageInputUserDTO.phone},'%') " +
|
" </if> " +
|
"<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" +
|
"AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " +
|
" </if> " +
|
" group by t.id_card "+
|
" ) tt WHERE 1=1 "+
|
"<if test='pageInputUserDTO.tags != null and pageInputUserDTO.tags.trim() != ""'>" +
|
" and tt.tags like concat('%',#{pageInputUserDTO.tags},'%') " +
|
" </if> " +
|
"</script>")
|
IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);
|
|
@Select("" +
|
"SELECT " +
|
" t1.house_code houseCode, " +
|
" t1.house_name houseName, " +
|
" t1.square, " +
|
" t1.state " +
|
"FROM " +
|
" sys_user_input t " +
|
" LEFT JOIN com_mng_struct_house t1 ON t.house_code = t1.house_code " +
|
" WHERE" +
|
" t.id_card = #{idCard}")
|
List<ComMngStructHouseVO> selectUserHouseList(@Param("idCard") String idCard);
|
|
@Select("<script> " +
|
"SELECT " +
|
"u.id 'order', " +
|
"c.`area_name` areaName, "+
|
"u.door_number doorNumber, " +
|
"if(u.is_rent=1,'租住','自主')isRent, " +
|
"u.name, " +
|
"u.nation, " +
|
"case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook, " +
|
"if(u.marital_status=1,'已婚','未婚')maritalStatus, " +
|
"u.phone, " +
|
"u.education, " +
|
"u.id_card, " +
|
"u.company, " +
|
"u.residence, " +
|
"if(u.is_panzhiHua=1,'是','否') isPanZhiHua, " +
|
"u.situation, " +
|
"if(is_contact=1,'是','否') isContact, " +
|
"if(u.major=1,'是','否') major, " +
|
"if(u.soldier=1,'是','否') soldier, " +
|
"if(u.disability=1,'是','否') disability, " +
|
"if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds, " +
|
"if(u.lowIncome_people=1,'是','否') lowIncomePeople, " +
|
"if(u.old_people=1,'是','否') oldPeople, " +
|
"if(u.special_service_family=1,'是','否') specialServiceFamily, " +
|
"if(u.key_personnel=1,'是','否') keyPersonnel, " +
|
"u.tags " +
|
"FROM " +
|
" sys_user_input u " +
|
" where u.community_id=#{exportUserDTO.communityId} " +
|
"<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" +
|
" JOIN com_mng_struct_area c ON u.area_id = c.id " +
|
" </if> " +
|
"<if test='exportUserDTO.areaName == null or exportUserDTO.areaName.trim() == ""'>" +
|
"LEFT JOIN com_mng_struct_area c ON u.area_id = c.id " +
|
" </if> " +
|
"<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" +
|
"AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " +
|
" </if> " +
|
"<if test='exportUserDTO.phone != null and exportUserDTO.phone.trim() != ""'>" +
|
"AND u.phone like concat(#{exportUserDTO.phone},'%') " +
|
" </if> " +
|
"<if test='exportUserDTO.name != null and exportUserDTO.name.trim() != ""'>" +
|
" and u.`name` like concat(#{exportUserDTO.name},'%') " +
|
" </if> " +
|
"</script>")
|
List<EexcelUserDTO> selectExport(@Param("exportUserDTO") ExportUserDTO exportUserDTO);
|
|
@Select("<script> " +
|
"SELECT " +
|
"u.id 'order', " +
|
"c.`area_name` areaName, "+
|
"u.door_number doorNumber, " +
|
"if(u.is_rent=1,'租住','自主')isRent, " +
|
"u.name, " +
|
"u.nation, " +
|
"case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook, " +
|
"if(u.marital_status=1,'已婚','未婚')maritalStatus, " +
|
"u.phone, " +
|
"u.education, " +
|
"u.id_card, " +
|
"u.company, " +
|
"u.residence, " +
|
"if(u.is_panzhiHua=1,'是','否') isPanZhiHua, " +
|
"u.situation, " +
|
"if(is_contact=1,'是','否') isContact, " +
|
"if(u.major=1,'是','否') major, " +
|
"if(u.soldier=1,'是','否') soldier, " +
|
"if(u.disability=1,'是','否') disability, " +
|
"if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds, " +
|
"if(u.lowIncome_people=1,'是','否') lowIncomePeople, " +
|
"if(u.old_people=1,'是','否') oldPeople, " +
|
"if(u.special_service_family=1,'是','否') specialServiceFamily, " +
|
"if(u.key_personnel=1,'是','否') keyPersonnel, " +
|
"u.tags " +
|
"FROM " +
|
" sys_user_input u " +
|
" JOIN com_mng_struct_area c ON u.area_id = c.id " +
|
" where u.community_id=#{exportSpecialUserDTO.communityId} " +
|
"<if test='exportSpecialUserDTO.areaName != null and exportSpecialUserDTO.areaName.trim() != ""'>" +
|
"AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " +
|
" </if> " +
|
"<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" +
|
" and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " +
|
" </if> " +
|
"<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" +
|
" and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " +
|
" </if> " +
|
"<if test='exportSpecialUserDTO.tags != null and exportSpecialUserDTO.tags.trim() != ""'>" +
|
" CONCAT( " +
|
" IF " +
|
" ( t.soldier = 1, '军人,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_households = 1, '低保户,', '' ), " +
|
" IF " +
|
" ( t.lowIncome_people = 1, '低收入,', '' ), " +
|
" IF " +
|
" ( t.old_people = 1, '特服家庭,', '' ), " +
|
" IF " +
|
" ( t.key_personnel = 1, '重点人员,', '' ), " +
|
" IFNULL(t.tags,'') " +
|
" ) like concat(#{exportSpecialUserDTO.tags},'%')"+
|
" </if> " +
|
"</script>")
|
List<EexcelUserDTO> specialUserExport(@Param("exportSpecialUserDTO") ExportSpecialUserDTO exportSpecialUserDTO);
|
}
|
