java/zhihuishenqu.git

			@@ -3,6 +3,8 @@
			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.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;
			@@ -35,28 +37,28 @@
			// "</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 \n" +
			"REPLACE(REPLACE(REPLACE(REPLACE(h.house_name,a.address_detail,''),'栋','-'),'单元','-'),'层','-')houseName,\n" +
			"h.house_code\n" +
			"FROM\n" +
			"\tcom_mng_struct_area a \n" +
			"\tjoin com_mng_struct_house h on h.house_name like CONCAT(a.address_detail,'%') and h.type=5\n" +
			"WHERE\n" +
			"\ta.area_name = #{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 \n" +
			"p.name,\n" +
			"p.id,\n" +
			"if(p.is_rent=1,'合租','家人')relationship,\n" +
			"p.id_card,\n" +
			"p.phone,\n" +
			"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,\n" +
			"p.create_at\n" +
			"FROM\n" +
			"\tsys_user_input p \n" +
			"WHERE\n" +
			"\tp.door_number = #{doorNumber} \n" +
			"\tAND p.id !=#{userId}")
			@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> " +
			@@ -81,7 +83,7 @@
			" END AS sex, " +
			" t.phone, " +
			" YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
			" t1.area_name, " +
			" t1.area_name as areaName, " +
			" t.political_outlook politicalOutlook, " +
			" CONCAT( " +
			" IF " +
			@@ -94,7 +96,8 @@
			" ( t.old_people = 1, '特服家庭,', '' ), " +
			" IF " +
			" ( t.key_personnel = 1, '重点人员,', '' ) " +
			" ) tags " +
			" ) tags, " +
			" t.create_at as createAt " +
			"FROM " +
			" sys_user_input t " +
			" LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " +
			@@ -106,8 +109,8 @@
			"<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" +
			" and t.phone like concat(#{pageInputUserDTO.phone},'%') " +
			" </if> " +
			"<if test='pageInputUserDTO.areaId != null and pageInputUserDTO.areaId.trim() != ""'>" +
			" and t.area_id = #{pageInputUserDTO.areaId} " +
			"<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" +
			"AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " +
			" </if> " +
			" group by t.id_card "+
			"</script>")
			@@ -151,7 +154,7 @@
			" END AS sex, " +
			" t.phone, " +
			" YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
			" t1.area_name, " +
			" t1.area_name as areaName, " +
			" t.political_outlook politicalOutlook, " +
			" CONCAT( " +
			" IF " +
			@@ -164,7 +167,8 @@
			" ( t.old_people = 1, '特服家庭,', '' ), " +
			" IF " +
			" ( t.key_personnel = 1, '重点人员,', '' ) " +
			" ) tags " +
			" ) tags, " +
			" t.create_at as createAt " +
			"FROM " +
			" sys_user_input t " +
			" LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " +
			@@ -178,8 +182,8 @@
			"<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" +
			" and t.phone like concat(#{pageInputUserDTO.phone},'%') " +
			" </if> " +
			"<if test='pageInputUserDTO.areaId != null and pageInputUserDTO.areaId.trim() != ""'>" +
			" and t.area_id = #{pageInputUserDTO.areaId} " +
			"<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" +
			"AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " +
			" </if> " +
			"<if test='pageInputUserDTO.tags != null and pageInputUserDTO.tags.trim() != ""'>" +
			" and t.tags like concat('%',#{pageInputUserDTO.tags},'%') " +
			@@ -187,4 +191,51 @@
			" group by t.id_card "+
			"</script>")
			IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);

			@Select("<script> " +
			"SELECT " +
			"u.id 'order', " +
			"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 " +
			"FROM " +
			" sys_user_input u " +
			" LEFT JOIN sys_user su on su.id_card=u.id_card"+
			"<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> " +
			" where u.community_id=#{exportUserDTO.communityId} " +
			"<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);
			}