From 02f18587bd8860b305e2c688e20465be166bb48c Mon Sep 17 00:00:00 2001
From: huanghongfa <huanghongfa123456>
Date: 星期四, 22 七月 2021 15:14:03 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/test' into test
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java | 65 +++++++++++++++++++++++++++++---
1 files changed, 58 insertions(+), 7 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java
index 3c3dcf2..df92c17 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java
@@ -60,8 +60,9 @@
" SELECT id, id_card, ROUND(DATEDIFF(CURDATE(), STR_TO_DATE(SUBSTRING(id_card,7,15), '%Y%m%d'))/365.2422) AS age " +
" FROM com_pb_member " +
" <where> " +
+ " audit_result = 1 " +
"<if test='queryDTO.communityId != null'> " +
- " community_id = #{queryDTO.communityId} " +
+ " AND community_id = #{queryDTO.communityId} " +
"</if> " +
" </where>" +
" ) t " +
@@ -82,8 +83,9 @@
" SELECT CONVERT(SUBSTRING(id_card,'17',1), UNSIGNED INTEGER)%2 AS sxi, COUNT(id_card) AS total " +
" FROM com_pb_member " +
" <where> " +
+ " audit_result = 1 " +
"<if test='queryDTO.communityId != null'>" +
- " community_id = #{queryDTO.communityId} " +
+ " AND community_id = #{queryDTO.communityId} " +
"</if> " +
" </where>" +
" GROUP BY sxi " +
@@ -119,9 +121,15 @@
BigScreenStatisticPartyBuild partybuild(@Param("communityId") Long communityId);
@Select("<script> " +
- " SELECT pm.id, pm.member_id, pm.user_id, pm.name, pm.photo_path, COUNT(pa.id) AS total " +
- " FROM com_pb_activity_member pm LEFT JOIN com_pb_activity pa ON pm.activity_id = pa.id " +
- " WHERE pa.community_id = #{communityId} " +
+ " SELECT pm.id, pm.member_id, pm.user_id, pm.name, " +
+ " case " +
+ " when me.photo_path is NOT NULL then me.photo_path " +
+ " when CAST(SUBSTRING(me.id_card , 17, 1) AS SIGNED)%2=1 then 'https://www.psciio.com/idcard/tst/idcard/a723e5bcbd594a48956ef9354c8b477c.jpg' " +
+ " ELSE 'https://www.psciio.com/idcard/tst/idcard/cfb9b58913074068bba8c7002f91e7ff.jpg' END AS photo_path " +
+ " ,COUNT(pa.id) AS total " +
+ " FROM com_pb_activity_member pm LEFT JOIN com_pb_activity pa ON pm.activity_id = pa.id " +
+ " LEFT JOIN com_pb_member me ON pm.member_id = me.id " +
+ " WHERE pa.community_id = #{communityId} AND me.id IS NOT null " +
" GROUP BY pm.member_id " +
" ORDER BY total DESC " +
" LIMIT 3 " +
@@ -129,10 +137,15 @@
List<PartyBuildingMemberVO> bigscreenPartyactivitytopuser(@Param("communityId")Long communityId);
@Select("<script> " +
- " SELECT id AS member_id, user_id, NAME, photo_path, 0 AS total " +
+ " SELECT id AS member_id, user_id, NAME, " +
+ " case " +
+ " when photo_path IS NOT NULL then photo_path " +
+ " when CAST(SUBSTRING(id_card , 17, 1) AS SIGNED)%2=1 then 'https://www.psciio.com/idcard/tst/idcard/a723e5bcbd594a48956ef9354c8b477c.jpg' " +
+ " ELSE 'https://www.psciio.com/idcard/tst/idcard/cfb9b58913074068bba8c7002f91e7ff.jpg' END AS photo_path , " +
+ " 0 AS total " +
" FROM com_pb_member " +
" WHERE community_id=#{communityId} " +
- " ORDER BY join_time DESC " +
+ " ORDER BY join_time ASC " +
" LIMIT 3 " +
"</script>")
List<PartyBuildingMemberVO> defaultPartyactivity(@Param("communityId")Long communityId);
@@ -166,4 +179,42 @@
" ORDER BY m.idxName " +
"</script>")
List<BigScreenStatisticPartyActivity> partyactivity(@Param("queryDTO")BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);
+
+ @Select("<script> " +
+ "SELECT " +
+ " m.idxName, " +
+ "CASE " +
+ " " +
+ " WHEN d.total IS NOT NULL THEN " +
+ " d.total ELSE 0 " +
+ " END AS total " +
+ "FROM " +
+ " ( " +
+ " SELECT " +
+ " DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 DAY ), '%Y-%m-%d' ) AS idxName " +
+ " FROM " +
+ " ( SELECT @cdate := date_add( #{queryDTO.endTime}, INTERVAL 1 DAY ) FROM com_pb_activity LIMIT 7 ) d " +
+ " ORDER BY " +
+ " idxName " +
+ " ) m " +
+ " LEFT JOIN ( " +
+ " SELECT " +
+ " date_format( activity_time_begin, '%Y-%m-%d' ) AS idxName, " +
+ " COUNT( id ) AS total " +
+ " FROM " +
+ " com_pb_activity " +
+ " WHERE " +
+ " community_id = #{queryDTO.communityId} " +
+ " AND STATUS IN ( 3, 4, 5 ) " +
+ " AND activity_time_begin < DATE_FORMAT( STR_TO_DATE( #{queryDTO.startTime}, '%Y-%m-%d' ), '%Y-%m-%d' ) " +
+ " AND activity_time_begin > DATE_FORMAT( STR_TO_DATE( #{queryDTO.endTime}, '%Y-%m-%d' ), '%Y-%m-%d' ) " +
+ " GROUP BY " +
+ " idxName " +
+ " ORDER BY " +
+ " idxName ASC " +
+ " ) d ON m.idxName = d.idxName " +
+ "ORDER BY " +
+ " m.idxName" +
+ " </script>")
+ List<BigScreenStatisticPartyActivity> partyactivityByWeek(@Param("queryDTO")BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);
}
--
Gitblit v1.7.1