package com.panzhihua.service_community.dao;
|
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import com.panzhihua.common.model.dtos.community.bigscreen.BigScreenStatisticAgeGenderDTO;
|
import com.panzhihua.common.model.dtos.community.bigscreen.BigScreenStatisticPartyActivityDTO;
|
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
|
*
|
* @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} " +
|
"</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> " +
|
"<if test='queryDTO.communityId != null'> " +
|
" 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> " +
|
"<if test='queryDTO.communityId != null'>" +
|
" community_id = #{queryDTO.communityId} " +
|
"</if> " +
|
" </where>" +
|
" GROUP BY sxi " +
|
" )t " +
|
" ) t2 " +
|
"</script>")
|
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" +
|
") 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" +
|
" 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" +
|
" 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" +
|
" ORDER BY m.idxName " +
|
"</script>")
|
List<BigScreenStatisticPartyActivity> partyactivity(@Param("queryDTO")BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);
|
}
|
