java/zhihuishenqu.git

			@@ -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);
			}