From 46e7cd9daeb5072eb562317520307a1d60b1446e Mon Sep 17 00:00:00 2001
From: huanghongfa <18228131219@163.com>
Date: 星期四, 25 二月 2021 13:15:47 +0800
Subject: [PATCH] all
---
springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java | 154 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 152 insertions(+), 2 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java b/springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java
index 3090be0..45648be 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/UserDao.java
@@ -5,6 +5,8 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.panzhihua.common.model.dtos.PageDTO;
+import com.panzhihua.common.model.dtos.community.ExportUserDTO;
+import com.panzhihua.common.model.dtos.user.EexcelUserDTO;
import com.panzhihua.common.model.dtos.user.PageUserAppletsBackstageDTO;
import com.panzhihua.common.model.vos.IndexDataKanbanVO;
import com.panzhihua.common.model.vos.IndexDataVO;
@@ -75,8 +77,12 @@
"SELECT\n" +
"u.user_id,\n" +
"u.`name`,\n" +
+ "u.`sex`,\n" +
"u.nick_name,\n" +
"u.phone,\n" +
+ "YEAR (now()) - YEAR (substring(u.id_card, 7, 8)) age,\n"+
+ "1 isRegister,\n"+
+ "v.political_face political_outlook,\n"+
"u.id_card,\n" +
"u.job,\n" +
"c.area_name communityName,\n" +
@@ -84,10 +90,11 @@
"u.create_at \n" +
"FROM\n" +
"sys_user u\n" +
- "<if test='loginUserInfoVO.name != null and loginUserInfoVO.name.trim() != ""'>" +
+ "left join com_mng_volunteer_mng v on u.phone=v.phone \n"+
+ "<if test='loginUserInfoVO.communityName != null and loginUserInfoVO.communityName.trim() != ""'>" +
" JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
" </if> " +
- "<if test='loginUserInfoVO.name == null or loginUserInfoVO.name.trim() == ""'>" +
+ "<if test='loginUserInfoVO.communityName == null or loginUserInfoVO.communityName.trim() == ""'>" +
"LEFT JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
" </if> " +
"<if test='loginUserInfoVO.communityName != null and loginUserInfoVO.communityName.trim() != ""'>" +
@@ -99,6 +106,45 @@
" </if> " +
"<if test='loginUserInfoVO.phone != null and loginUserInfoVO.phone.trim() != ""'>" +
"AND u.phone like concat(#{loginUserInfoVO.phone},'%') " +
+ " </if> " +
+
+ "union all "+
+ "SELECT \n" +
+ "u.id+100000000 user_id,\n" +
+ "u.`name`,\n" +
+ "case if(length(u.id_card)=18, cast(substring(u.id_card,17,1) as UNSIGNED)%2, if(length(u.id_card)=15,cast(substring(u.id_card,15,1) as UNSIGNED)%2,3)) \n" +
+ "when 1 then 1\n" +
+ "when 0 then 2\n" +
+ "else 0\n" +
+ "end as sex,\n" +
+ "'' nick_name,\n" +
+ "u.phone,\n" +
+ "YEAR (now()) - YEAR (substring(u.id_card, 7, 8)) age,\n" +
+ "if(su.user_id is null ,0,1)isRegister,\n" +
+ "u.political_outlook,\n" +
+ "u.id_card,\n" +
+ "'' job,\n" +
+ "c.area_name communityName,\n" +
+ "CONCAT(if(u.soldier=1,'军人,',''),if(u.lowIncome_households=1,'低保户,',''),if(u.lowIncome_people=1,'低收入,',''),if(u.old_people=1,'特服家庭,',''),if(u.key_personnel=1,'重点人员,',''))tags,\n" +
+ "u.create_at\n" +
+ "FROM\n" +
+ "\tsys_user_input u\n" +
+ "\tLEFT JOIN sys_user su on su.id_card=u.id_card"+
+ "<if test='loginUserInfoVO.communityName != null and loginUserInfoVO.communityName.trim() != ""'>" +
+ " JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
+ " </if> " +
+ "<if test='loginUserInfoVO.communityName == null or loginUserInfoVO.communityName.trim() == ""'>" +
+ "LEFT JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
+ " </if> " +
+ "<if test='loginUserInfoVO.communityName != null and loginUserInfoVO.communityName.trim() != ""'>" +
+ "AND c.`area_name` like concat(#{loginUserInfoVO.communityName},'%') \n" +
+ " </if> " +
+ " where u.community_id=#{loginUserInfoVO.communityId} " +
+ "<if test='loginUserInfoVO.phone != null and loginUserInfoVO.phone.trim() != ""'>" +
+ "AND u.phone like concat(#{loginUserInfoVO.phone},'%') " +
+ " </if> " +
+ "<if test='loginUserInfoVO.name != null and loginUserInfoVO.name.trim() != ""'>" +
+ " and u.`name` like concat(#{loginUserInfoVO.name},'%') \n" +
" </if> " +
"</script>")
IPage<LoginUserInfoVO> pageUser(Page page, @Param("loginUserInfoVO") LoginUserInfoVO loginUserInfoVO);
@@ -243,4 +289,108 @@
int updateFaceState(Long userId);
@Select("select name from com_act where community_id=#{communityId}")
ComActVO selectCommunity(Long communityId);
+ @Select("select COUNT(a.id) from com_mng_struct_area a where a.area_name=#{areaName}")
+ Integer selectCountArea(String areaName);
+ @Select("SELECT\n" +
+ "\tCOUNT(h.id)\n" +
+ "FROM\n" +
+ "\tcom_mng_struct_area a \n" +
+ "\tLEFT JOIN com_mng_struct_house h on h.parent_code=a.area_code\n" +
+ "WHERE\n" +
+ "\ta.area_name = #{areaName}")
+ Integer selectCountHouse(String areaName);
+
+ @Select("<script> " +
+ "SELECT\n" +
+ "u.user_id 'order',\n" +
+ "h.house_name doorNumber,\n" +
+ "if(h.state=2,'租住','自主')isRent,\n" +
+ "u.`name`,\n" +
+ "'汉' nation,\n" +
+ "if(u.is_partymember=1,'党员','群众')politicalOutlook,\n" +
+ "'未知' maritalStatus,\n" +
+ "u.phone,\n" +
+ "'未知' education,\n" +
+ "u.id_card,\n" +
+ "'未知' company,\n" +
+ "'未知' residence,\n" +
+ "'未知' isPanZhiHua,\n" +
+ "'未知' situation,\n" +
+ "'未知' isContact,\n" +
+ "'未知' major,\n" +
+ "'未知' soldier,\n" +
+ "'未知' disability,\n" +
+ "'未知' lowIncomeHouseholds,\n" +
+ "'未知' lowIncomePeople,\n" +
+ "'未知' oldPeople,\n" +
+ "'未知' specialServiceFamily,\n" +
+ "'未知' keyPersonnel\n" +
+ "FROM\n" +
+ "sys_user u\n" +
+ "left join com_mng_struct_house_user hu on hu.user_id=u.user_id\n"+
+ "left join com_mng_struct_house h on hu.house_code=h.house_code\n"+
+ "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" +
+ " JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
+ " </if> " +
+ "<if test='exportUserDTO.areaName == null or exportUserDTO.areaName.trim() == ""'>" +
+ "LEFT JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
+ " </if> " +
+ "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" +
+ "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') \n" +
+ " </if> " +
+ " where u.community_id=#{exportUserDTO.communityId} and u.type=1 " +
+ "<if test='exportUserDTO.name != null and exportUserDTO.name.trim() != ""'>" +
+ " and u.`name` like concat(#{exportUserDTO.name},'%') \n" +
+ " </if> " +
+ "<if test='exportUserDTO.phone != null and exportUserDTO.phone.trim() != ""'>" +
+ "AND u.phone like concat(#{exportUserDTO.phone},'%') " +
+ " </if> " +
+ " group by u.user_id "+
+
+ "union all "+
+ "SELECT \n" +
+ "u.id+100000000 'order',\n" +
+ "u.door_number doorNumber,\n" +
+ "if(u.is_rent=1,'租住','自主')isRent,\n" +
+ "u.name,\n" +
+ "u.nation,\n" +
+ "case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook,\n" +
+ "if(u.marital_status=1,'已婚','未婚')maritalStatus,\n" +
+ "u.phone,\n" +
+ "u.education,\n" +
+ "u.id_card,\n" +
+ "u.company,\n" +
+ "u.residence,\n" +
+ "if(u.is_panzhiHua=1,'是','否') isPanZhiHua,\n" +
+ "u.situation,\n" +
+ "if(is_contact=1,'是','否') isContact,\n" +
+ "if(u.major=1,'是','否') major,\n" +
+ "if(u.soldier=1,'是','否') soldier,\n" +
+ "if(u.disability=1,'是','否') disability,\n" +
+ "if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds,\n" +
+ "if(u.lowIncome_people=1,'是','否') lowIncomePeople,\n" +
+ "if(u.old_people=1,'是','否') oldPeople,\n" +
+ "if(u.special_service_family=1,'是','否') specialServiceFamily,\n" +
+ "if(u.key_personnel=1,'是','否') keyPersonnel\n" +
+ "FROM\n" +
+ "\tsys_user_input u\n" +
+ "\tLEFT 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 \n" +
+ " </if> " +
+ "<if test='exportUserDTO.areaName == null or exportUserDTO.areaName.trim() == ""'>" +
+ "LEFT JOIN com_mng_struct_area c ON u.area_id = c.id \n" +
+ " </if> " +
+ "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" +
+ "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') \n" +
+ " </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},'%') \n" +
+ " </if> " +
+ "</script>")
+ List<EexcelUserDTO> selectExport(@Param("exportUserDTO") ExportUserDTO exportUserDTO);
}
--
Gitblit v1.7.1