java/zhihuishenqu.git

			@@ -1,5 +1,10 @@
			package com.panzhihua.service_community.dao;

			import java.util.List;

			import org.apache.ibatis.annotations.Mapper;
			import org.apache.ibatis.annotations.Param;
			import org.apache.ibatis.annotations.Select;

			import com.baomidou.mybatisplus.core.mapper.BaseMapper;
			import com.baomidou.mybatisplus.core.metadata.IPage;
			@@ -9,11 +14,6 @@
			import com.panzhihua.common.model.dtos.community.bigscreen.PageBigScreenStatisticPartyOrg;
			import com.panzhihua.common.model.vos.community.bigscreen.*;
			import com.panzhihua.service_community.model.dos.EldersAuthDO;
			import org.apache.ibatis.annotations.Mapper;
			import org.apache.ibatis.annotations.Param;
			import org.apache.ibatis.annotations.Select;

			import java.util.List;

			/**
			* DAO
			@@ -21,200 +21,102 @@
			* @author cedoo email:cedoo(a)qq.com
			* @version 1.0
			* @since 1.0
			* */
			*/
			@Mapper
			public interface BigScreenDAO extends BaseMapper<EldersAuthDO> {

			@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> " +
			" </where>" +
			" GROUP BY org.id " +
			//" ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
			" ORDER BY total desc " +
			"</script>")
			IPage<BigScreenStatisticPartyOrg> partyOrgMembers(Page page, @Param("pageDTO") PageBigScreenStatisticPartyOrg pageBigScreenStatisticPartyOrg);
			@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> "
			+ " </where>" + " GROUP BY org.id " +
			// " ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
			" ORDER BY total desc " + "</script>")
			IPage<BigScreenStatisticPartyOrg> partyOrgMembers(Page page,
			@Param("pageDTO") PageBigScreenStatisticPartyOrg pageBigScreenStatisticPartyOrg);

			@Select("<script> " +
			" SELECT " +
			" SUM(CASE lev WHEN '30以内' THEN total ELSE 0 END) as 'thirtyTotal', " +
			" SUM(CASE lev WHEN '31-60' THEN total ELSE 0 END) as 'sixtyTotal', " +
			" SUM(CASE lev WHEN '60以上' THEN total ELSE 0 END) as 'biggerTotal' " +
			" FROM ( " +
			" SELECT " +
			" abt.lev, COUNT(abt.id) AS total " +
			" FROM ( " +
			" SELECT id_card, id,age, " +
			" case when age <= 30 then '30以内' " +
			" when age > 30 and age <= 60 then '31-60' " +
			" 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 " +
			" GROUP BY abt.lev " +
			" ) t2 " +
			"</script>")
			@Select("<script> " + " SELECT " + " SUM(CASE lev WHEN '30以内' THEN total ELSE 0 END) as 'thirtyTotal', "
			+ " SUM(CASE lev WHEN '31-60' THEN total ELSE 0 END) as 'sixtyTotal', "
			+ " SUM(CASE lev WHEN '60以上' THEN total ELSE 0 END) as 'biggerTotal' " + " FROM ( " + " SELECT "
			+ " abt.lev, COUNT(abt.id) AS total " + " FROM ( " + " SELECT id_card, id,age, "
			+ " case when age <= 30 then '30以内' " + " when age > 30 and age <= 60 then '31-60' "
			+ " 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 "
			+ " GROUP BY abt.lev " + " ) t2 " + "</script>")
			BigScreenStatisticAgeGender age(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);

			@Select("<script> " + " SELECT " + " SUM(CASE gender WHEN '男' THEN total ELSE 0 END) as 'manTotal', "
			+ " SUM(CASE gender WHEN '女' THEN total ELSE 0 END) as 'womeTotal' " + " FROM ( "
			+ " SELECT CASE WHEN t.sxi = 0 THEN '女' WHEN t.sxi = 1 THEN '男' ELSE '未知' END AS gender, t.total "
			+ " 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 "
			+ " ) t2 " + "</script>")
			BigScreenStatisticAgeGender
			gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);

			@Select("<script> " +
			" SELECT " +
			" SUM(CASE gender WHEN '男' THEN total ELSE 0 END) as 'manTotal', " +
			" SUM(CASE gender WHEN '女' THEN total ELSE 0 END) as 'womeTotal' " +
			" FROM ( " +
			" SELECT CASE WHEN t.sxi = 0 THEN '女' WHEN t.sxi = 1 THEN '男' ELSE '未知' END AS gender, t.total " +
			" 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 " +
			" ) t2 " +
			"</script>")
			BigScreenStatisticAgeGender gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);

			@Select("<script> " +
			" 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>")
			@Select("<script> " + " 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, " +
			" 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 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, " +
			" 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 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 " +
			" 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 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);
			@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);
			}