From 7febb461fdabc11456586d4ea754e169720ab14c Mon Sep 17 00:00:00 2001
From: 罗元桥 <2376770955@qq.com>
Date: 星期四, 17 六月 2021 11:22:47 +0800
Subject: [PATCH] Merge branch 'test_screen' into 'master'
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java | 112 ++++++++++++++++++++++++++++---------------------------
1 files changed, 57 insertions(+), 55 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 2fa6b03..3c3dcf2 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
@@ -36,7 +36,8 @@
"</if> " +
" </where>" +
" GROUP BY org.id " +
- " ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
+ //" ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
+ " ORDER BY total desc " +
"</script>")
IPage<BigScreenStatisticPartyOrg> partyOrgMembers(Page page, @Param("pageDTO") PageBigScreenStatisticPartyOrg pageBigScreenStatisticPartyOrg);
@@ -92,75 +93,76 @@
BigScreenStatisticAgeGender gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);
@Select("<script> " +
- " SELECT \n" +
- " SUM(CASE TYPE WHEN 1 THEN total ELSE 0 END) as 'orgCount',\n" +
- " SUM(CASE TYPE WHEN 2 THEN total ELSE 0 END) as 'memberCount',\n" +
- " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount',\n" +
- " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount'\n" +
- " FROM (\n" +
- " SELECT 1 AS TYPE , COUNT(id) total\n" +
- " FROM com_pb_org org \n" +
- " WHERE org.community_id = #{communityId} AND STATUS=1\n" +
- " UNION ALL \n" +
- " SELECT 2 AS TYPE , COUNT(id) total\n" +
- " FROM com_pb_member \n" +
- " WHERE community_id = #{communityId} AND audit_result = 1\n" +
- " UNION ALL \n" +
- " SELECT 3 AS TYPE , COUNT(id) total\n" +
- " FROM com_pb_activity\n" +
- " WHERE community_id = #{communityId} AND STATUS IN (2,3,4,5)\n" +
- " UNION ALL \n" +
- " SELECT 4 AS TYPE , COUNT(id) total\n" +
- " FROM com_pb_dyn \n" +
- " WHERE community_id = #{communityId} AND STATUS = 2\n" +
+ " SELECT " +
+ " SUM(CASE TYPE WHEN 1 THEN total ELSE 0 END) as 'orgCount', " +
+ " SUM(CASE TYPE WHEN 2 THEN total ELSE 0 END) as 'memberCount', " +
+ " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount', " +
+ " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount' " +
+ " FROM ( " +
+ " SELECT 1 AS TYPE , COUNT(id) total " +
+ " FROM com_pb_org org " +
+ " WHERE org.community_id = #{communityId} AND STATUS=1 " +
+ " UNION ALL " +
+ " SELECT 2 AS TYPE , COUNT(id) total " +
+ " FROM com_pb_member " +
+ " WHERE community_id = #{communityId} AND audit_result = 1 " +
+ " UNION ALL " +
+ " SELECT 3 AS TYPE , COUNT(id) total " +
+ " FROM com_pb_activity " +
+ " WHERE community_id = #{communityId} AND STATUS IN (2,3,4,5) " +
+ " UNION ALL " +
+ " SELECT 4 AS TYPE , COUNT(id) total " +
+ " FROM com_pb_dyn " +
+ " WHERE community_id = #{communityId} AND TYPE = 1 " +
") t" +
"</script>")
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 \n" +
- " FROM com_pb_activity_member pm LEFT JOIN com_pb_activity pa ON pm.activity_id = pa.id \n" +
- " WHERE pa.community_id = #{communityId} \n" +
- " GROUP BY pm.member_id\n" +
- " ORDER BY total DESC\n" +
+ " 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} " +
+ " GROUP BY pm.member_id " +
+ " ORDER BY total DESC " +
" LIMIT 3 " +
"</script>")
List<PartyBuildingMemberVO> bigscreenPartyactivitytopuser(@Param("communityId")Long communityId);
@Select("<script> " +
- " SELECT id AS member_id, user_id, NAME, photo_path, 0 AS total\n" +
- " FROM com_pb_member\n" +
- " WHERE community_id=#{communityId}\n" +
- " ORDER BY join_time DESC \n" +
+ " SELECT id AS member_id, user_id, NAME, photo_path, 0 AS total " +
+ " FROM com_pb_member " +
+ " WHERE community_id=#{communityId} " +
+ " ORDER BY join_time DESC " +
" LIMIT 3 " +
"</script>")
List<PartyBuildingMemberVO> defaultPartyactivity(@Param("communityId")Long communityId);
@Select("<script> " +
- " SELECT m.idxName, case when d.total IS NOT NULL then d.total ELSE 0 END AS total\n" +
- " FROM \n" +
- " (\n" +
- " SELECT\n" +
- " DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as idxName\n" +
- "FROM\n" +
- " ( \n" +
- " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH )\n" +
- " FROM com_pb_activity LIMIT 12\n" +
- " )d\n" +
- " ORDER BY idxName\n" +
- ")m LEFT JOIN \n" +
- "(\n" +
- "SELECT \n" +
- " date_format(activity_time_begin, '%Y-%m') AS idxName,\n" +
- " COUNT(id) AS total\n" +
- " FROM com_pb_activity \n" +
- " WHERE \n" +
- " community_id=#{queryDTO.communityId} AND STATUS IN ( 3, 4, 5 ) AND \n" +
- " activity_time_begin < DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND\n" +
- " activity_time_begin > DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d') \n" +
- " GROUP BY idxName\n" +
- " ORDER BY idxName ASC \n" +
- " ) d ON m.idxName = d.idxName\n" +
+ " 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 MONTH ),'%Y-%m') as idxName " +
+ "FROM " +
+ " ( " +
+ " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH ) " +
+ " FROM com_pb_activity LIMIT 12 " +
+ " )d " +
+ " ORDER BY idxName " +
+ ")m LEFT JOIN " +
+ "( " +
+ "SELECT " +
+ " date_format(activity_time_begin, '%Y-%m') 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( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND " +
+ " activity_time_begin > DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d') " +
+ " GROUP BY idxName " +
+ " ORDER BY idxName ASC " +
+ " ) d ON m.idxName = d.idxName " +
+ " WHERE m.idxName > '2020-12-31' " +
" ORDER BY m.idxName " +
"</script>")
List<BigScreenStatisticPartyActivity> partyactivity(@Param("queryDTO")BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);
--
Gitblit v1.7.1