From 2e64c232ab6b51b2cecf1ee96e1e9b709234f326 Mon Sep 17 00:00:00 2001
From: huanghongfa <huanghongfa123456>
Date: 星期六, 21 八月 2021 16:35:14 +0800
Subject: [PATCH] 随手拍改版接口开发
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java | 167 ++++++++++++++++++++++++++++++++++++-------------------
1 files changed, 110 insertions(+), 57 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..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
@@ -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);
@@ -59,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 " +
@@ -81,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 " +
@@ -92,76 +95,126 @@
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, " +
+ " 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 " +
"</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, " +
+ " 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 ASC " +
" 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);
+
+ @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