From e9d5fb4b01c383fa5b01bf5e730d4e3da73f266e Mon Sep 17 00:00:00 2001
From: 101captain <237651143@qq.com>
Date: 星期四, 30 十二月 2021 11:27:15 +0800
Subject: [PATCH] 12/30 河门口大屏特殊修改
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java | 24 ++++++++++++------------
1 files changed, 12 insertions(+), 12 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 b636561..ddbc980 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
@@ -27,7 +27,7 @@
@Select("<script> " + " SELECT org.id, org.name, COUNT(pm.id) as total " + " FROM com_pb_org org "
+ " LEFT JOIN com_pb_member pm ON org.id = pm.org_id " + " <where> " + " org.status = 1 "
- + "<if test='pageDTO.communityId != null'>" + " AND org.community_id = #{pageDTO.communityId} " + "</if> "
+ + "<if test='pageDTO.communityId != null'>" + " AND org.community_id = ${pageDTO.communityId} " + "</if> "
+ " </where>" + " GROUP BY org.id " +
// " ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
" ORDER BY total desc " + "</script>")
@@ -42,7 +42,7 @@
+ " when age > 60 then '60以上' " + " ELSE '' " + " END AS lev " + " FROM ( "
+ " 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'> "
- + " AND community_id = #{queryDTO.communityId} " + "</if> " + " </where>" + " ) t " + " )abt "
+ + " AND community_id = ${queryDTO.communityId} " + "</if> " + " </where>" + " ) t " + " )abt "
+ " GROUP BY abt.lev " + " ) t2 " + "</script>")
BigScreenStatisticAgeGender age(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);
@@ -52,7 +52,7 @@
+ " FROM ( "
+ " 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'>"
- + " AND community_id = #{queryDTO.communityId} " + "</if> " + " </where>" + " GROUP BY sxi " + " )t "
+ + " AND community_id = ${queryDTO.communityId} " + "</if> " + " </where>" + " GROUP BY sxi " + " )t "
+ " ) t2 " + "</script>")
BigScreenStatisticAgeGender
gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);
@@ -62,15 +62,15 @@
+ " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount', "
+ " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount',SUM(CASE TYPE WHEN 5 THEN total ELSE 0 END) as 'committeeCount' " + " FROM ( "
+ " SELECT 1 AS TYPE , COUNT(id) total " + " FROM com_pb_org org "
- + " WHERE org.community_id = #{communityId} AND STATUS=1 " + " UNION ALL "
+ + " 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 "
+ + " 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 "
+ + " 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 UNION ALL " +
+ + " WHERE community_id = ${communityId} AND TYPE = 1 UNION ALL " +
"select 5 AS TYPE,count(id) total from com_pb_member_role " +
- "where community_id = #{communityId}" + ") t" + "</script>")
+ "where community_id = ${communityId}" + ") t" + "</script>")
BigScreenStatisticPartyBuild partybuild(@Param("communityId") Long communityId);
@Select("<script> " + " SELECT pm.id, pm.member_id, pm.user_id, pm.name, " + " case "
@@ -80,7 +80,7 @@
+ " ,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 "
+ + " 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);
@@ -88,7 +88,7 @@
+ " 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} "
+ + " 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);
@@ -99,7 +99,7 @@
+ " 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 "
+ + " 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 "
@@ -113,7 +113,7 @@
+ " ( 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} "
+ + " 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' ) "
--
Gitblit v1.7.1