From f1876cb0149aefff5b211cd8973adabe05e7be31 Mon Sep 17 00:00:00 2001
From: CeDo <cedoogle@gmail.com>
Date: 星期三, 19 五月 2021 18:10:27 +0800
Subject: [PATCH] update
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthDAO.java | 44 +++++++++++++++++++++++++++++++++++---------
1 files changed, 35 insertions(+), 9 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthDAO.java
index 08d2b24..eee310c 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthDAO.java
@@ -29,9 +29,16 @@
@Select("<script> " +
"SELECT ea.id, ea.create_at, ea.create_by, ea.update_by, ea.update_at," +
" ea.AUTH_USER_NAME , ea.ID_CARD, ea.BIRTH_DAY, ea.FAMILY_USER_ID ," +
- " ea.DOMICILE, ea.SUMIT_USER_ID, u.phone as submitUserAccount ," +
+ " ea.DOMICILE, ea.SUMIT_USER_ID, u.phone as submitUserAccount , ea.VIDEO_URL, " +
" u.name as submitUserName, ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) as age \n" +
- "FROM com_elders_auth ea LEFT JOIN sys_user u ON ea.SUMIT_USER_ID = u.user_id\n" +
+ "FROM " +
+ " (" +
+ " SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth\n" +
+ " FROM com_elders_auth \n" +
+ " GROUP BY id_card, yearMonth\n" +
+ " ) t " +
+ " LEFT JOIN com_elders_auth ea ON t.id = ea.id " +
+ " LEFT JOIN sys_user u ON ea.SUMIT_USER_ID = u.user_id\n" +
" <where> " +
"<if test='pageEldersAuthDTO.sumitUserId != null'>" +
" AND ea.SUMIT_USER_ID = #{pageEldersAuthDTO.sumitUserId} " +
@@ -51,7 +58,7 @@
@Select("<script> " +
"SELECT ea.id, ea.create_at, ea.create_by, ea.update_by, ea.update_at," +
" ea.AUTH_USER_NAME , ea.ID_CARD, ea.BIRTH_DAY, ea.FAMILY_USER_ID, " +
- " ea.DOMICILE, ea.SUMIT_USER_ID, u.phone as submitUserAccount ," +
+ " ea.DOMICILE, ea.SUMIT_USER_ID, u.phone as submitUserAccount , ea.VIDEO_URL , ea.FAMILY_USER_ID, " +
" u.name as submitUserName, ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) as age \n" +
"FROM com_elders_auth ea LEFT JOIN sys_user u ON ea.SUMIT_USER_ID = u.user_id\n" +
" where ea.id = #{id}" +
@@ -59,11 +66,30 @@
EldersAuthDetailsVO selectDetails(@Param("id") Long id);
@Select("<script> " +
- "SELECT ea.id, ea.create_at, ea.create_by, ea.update_by, ea.update_at," +
- " ea.AUTH_USER_NAME , ea.ID_CARD, ea.BIRTH_DAY, ea.FAMILY_USER_ID ," +
- " ea.DOMICILE, ea.SUMIT_USER_ID, u.phone as submitUserAccount ," +
- " u.name as submitUserName, ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) as age \n" +
- "FROM com_elders_auth ea LEFT JOIN sys_user u ON ea.SUMIT_USER_ID = u.user_id\n" +
+ " SELECT \n" +
+ " (@rownum:=@rownum+1) as seq, '高龄补贴' AS subsidy, '' AS batch, ea.AUTH_USER_NAME AS NAME, ea.ID_CARD, \n" +
+ " CASE\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) > 99 THEN \"100周岁（含）以上\"\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) > 89 THEN \"90-99周岁\"\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) >= 80 THEN \"80-89周岁\"\n" +
+ " ELSE \"未达到年龄\"\n" +
+ " END AS TYPE, DATE_FORMAT(ea.create_at, '%Y-%m') AS yearMonth,\n" +
+ " CASE\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) > 99 THEN \"800\"\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) > 89 THEN \"200\"\n" +
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) >= 80 THEN \"50\"\n" +
+ " ELSE \"未达到年龄\"\n" +
+ " END AS payCount,\n" +
+ " ac.name AS communityName, '' AS 'group', '' AS telphone , ROUND(DATEDIFF(CURDATE(), ea.BIRTH_DAY)/365.2422) AS age\n" +
+ " FROM \n" +
+ " (" +
+ " SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth, @rownum:=0 \n" +
+ " FROM com_elders_auth \n" +
+ " GROUP BY id_card, yearMonth\n" +
+ " ) t \n" +
+ " LEFT JOIN com_elders_auth ea ON t.id = ea.id \n" +
+ " LEFT JOIN sys_user u ON ea.SUMIT_USER_ID = u.user_id\n" +
+ " LEFT JOIN com_act ac ON u.community_id = ac.community_id\n" +
" <where> " +
"<if test='pageEldersAuthDTO.sumitUserId != null'>" +
" AND ea.SUMIT_USER_ID = #{pageEldersAuthDTO.sumitUserId} " +
@@ -77,5 +103,5 @@
" </where>" +
" ORDER BY ea.${pageEldersAuthDTO.sortBy} ${pageEldersAuthDTO.order} " +
"</script>")
- List<ExcelElderAuthDTO> selectByParams(PageEldersAuthDTO pageEldersAuthDTO);
+ List<ExcelElderAuthDTO> selectByParams( @Param("pageEldersAuthDTO") PageEldersAuthDTO pageEldersAuthDTO);
}
--
Gitblit v1.7.1