springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngPopulationCommunityTagsVo.java
New file @@ -0,0 +1,38 @@ package com.panzhihua.common.model.vos.community; import com.fasterxml.jackson.databind.annotation.JsonSerialize; import com.fasterxml.jackson.databind.ser.std.ToStringSerializer; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** * 基础数据》人口社区特殊群体关系表 * @author lyq */ @Data @ApiModel public class ComMngPopulationCommunityTagsVo { /** * 实有人口id */ @ApiModelProperty("实有人口id") @JsonSerialize(using = ToStringSerializer.class) private Long populationId; /** * 社区id */ @ApiModelProperty("社区id") private Long communityId; /** * 社区标签 */ @ApiModelProperty("社区标签") private String label; /** * 社区标签 */ @ApiModelProperty("社区名字") private String communityName; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/LabelUtils.java
New file @@ -0,0 +1,35 @@ package com.panzhihua.common.utlis; import com.panzhihua.common.model.vos.community.ComMngPopulationCommunityTagsVo; import java.util.List; /** * @author lyq * 社区标签工具类 */ public class LabelUtils { /** * 社区标签转换 * @param communityTagsList 社区标签列表 * @return 社区标签 */ public static String assembleLabel(List<ComMngPopulationCommunityTagsVo> communityTagsList){ //创建用户拼接字符串 StringBuilder sb = new StringBuilder(); communityTagsList.forEach(tag -> { if(StringUtils.isNotEmpty(tag.getLabel())){ sb.append(tag.getCommunityName()); sb.append("："); sb.append(tag.getLabel()); sb.append("；"); } }); String result = sb.toString(); if(StringUtils.isNotEmpty(result)){ result = result.substring(0,result.length() - 1); } return result; } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
@@ -7,6 +7,7 @@ import com.panzhihua.common.model.dtos.community.CascadeHouseDTO; import com.panzhihua.common.model.dtos.property.CommonPage; import com.panzhihua.common.model.vos.community.*; import com.panzhihua.service_community.model.dos.ComMngPopulationCommunityTagsDO; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; @@ -307,112 +308,11 @@ + "GROUP BY " + " months " + "ORDER BY " + " count( e.id ) DESC " + " LIMIT 3") List<Integer> getFrequentlyEventMonth(@Param("communityId") Long communityId); @Select("<script> " + "SELECT " + " count( e.id ) AS eventZATotal, " + " IFNULL(( " + " SELECT " + " count( e1.id ) " + " FROM " + " `event` AS e1 " + " LEFT JOIN event_grid_data AS egd1 ON egd1.id = e1.grid_id " + " WHERE " + " egd1.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 1 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e1.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e1.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventZATotal, " + " IFNULL(( " + " SELECT " + " count( e2.id ) " + " FROM " + " `event` AS e2 " + " LEFT JOIN event_grid_data AS egd2 ON egd2.id = e2.grid_id " + " WHERE " + " egd2.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 5 " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e2.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e2.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS eventTFTotal, " + " IFNULL(( " + " SELECT " + " count( e3.id ) " + " FROM " + " `event` AS e3 " + " LEFT JOIN event_grid_data AS egd3 ON egd3.id = e3.grid_id " + " WHERE " + " egd3.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 5 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e3.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e3.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventTFTotal, " + " IFNULL(( " + " SELECT " + " count( e4.id ) " + " FROM " + " `event` AS e4 " + " LEFT JOIN event_grid_data AS egd4 ON egd4.id = e4.grid_id " + " WHERE " + " egd4.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 6 " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e4.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e4.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS eventTSTotal, " + " IFNULL(( " + " SELECT " + " count( e5.id ) " + " FROM " + " `event` AS e5 " + " LEFT JOIN event_grid_data AS egd5 ON egd5.id = e5.grid_id " + " WHERE " + " egd5.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 6 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e5.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e5.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventTSTotal, " + " IFNULL(( " + " SELECT " + " count( e6.id ) " + " FROM " + " `event` AS e6 " + " LEFT JOIN event_grid_data AS egd6 ON egd6.id = e6.grid_id " + " WHERE " + " egd6.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 3 " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e6.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e6.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS eventMDTotal, " + " IFNULL(( " + " SELECT " + " count( e7.id ) " + " FROM " + " `event` AS e7 " + " LEFT JOIN event_grid_data AS egd7 ON egd7.id = e7.grid_id " + " WHERE " + " egd7.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 3 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e7.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e7.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventMDTotal, " + " IFNULL(( " + " SELECT " + " count( e8.id ) " + " FROM " + " `event` AS e8 " + " LEFT JOIN event_grid_data AS egd8 ON egd8.id = e8.grid_id " + " WHERE " + " egd8.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 4 " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e8.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e8.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS eventBWDTotal, " + " IFNULL(( " + " SELECT " + " count( e9.id ) " + " FROM " + " `event` AS e9 " + " LEFT JOIN event_grid_data AS egd9 ON egd9.id = e9.grid_id " + " WHERE " + " egd9.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 4 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e9.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e9.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventBWDTotal, " + " IFNULL(( " + " SELECT " + " count( e10.id ) " + " FROM " + " `event` AS e10 " + " LEFT JOIN event_grid_data AS egd10 ON egd10.id = e10.grid_id " + " WHERE " + " egd10.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 2 " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e10.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e10.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS eventGGTotal, " + " IFNULL(( " + " SELECT " + " count( e11.id ) " + " FROM " + " `event` AS e11 " + " LEFT JOIN event_grid_data AS egd11 ON egd11.id = e11.grid_id " + " WHERE " + " egd11.grid_community_id = #{screenEventDTO.communityId} " + " AND event_category = 1 " + " AND event_type = 2 " + " AND event_deal_status = 4 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e11.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e11.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " ), " + " 0 " + " ) AS yesEventGGTotal, " + " IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND handle_status IS NOT NULL AND community_id = #{screenEventDTO.communityId} " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + "), 0 ) AS eventSSPTotal, " + " IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND status = 4 AND community_id = #{screenEventDTO.communityId} " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + "), 0 ) AS yesEventSSPTotal " + "FROM " + " `event` AS e " + " LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " + "WHERE " + " egd.grid_community_id = #{screenEventDTO.communityId} " + " AND event_status = 2 " + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'>" + " AND e.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> " + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'>" + " AND e.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + " </script>") /** * 综治事件大屏统计数据 * @param screenEventDTO 请求参数 * @return 大屏统计数据 */ EventLeftDownStatisticsVO getEventScreenLeftDown(@Param("screenEventDTO") BigScreenEventDTO screenEventDTO); @Select("SELECT " + " su.nick_name AS userName, " + " su.image_url AS imageUrl, " + " e.happen_time as createAt, " @@ -549,36 +449,26 @@ IPage<PopulationListVO> pagePopulationListApp(Page page, @Param("populationListDTO") PagePopulationListDTO populationListDTO); @Select("SELECT " + " cmp.id, " + " cmp.`name`, " + " cmp.phone, " + " cmp.card_no, " + " cmpct.label, " + " cmp.address, " + " cmp.culture_level, " + " cmp.nation, " + " cmp.political_outlook, " + " cmp.sex, " + " cmp.birthday, " + " cmp.native_place, " + " cmp.healthy, " + " cmp.is_rent, " + " cmv.alley AS road, " + " cmv.house_num AS doorNo, " + " cmp.floor, " + " cmp.unit_no, " + " cmp.house_no, " + " cmp.work_company, " + " cmp.census_register, " + " cmp.out_or_local, " + " cmp.remark, " + " cmp.marriage " + "FROM " + " com_mng_population AS cmp " + " LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id " + " left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id " + "WHERE " + " cmp.id = #{populationId}") /** * 根据人口id查询人口详情 * @param populationId 人口id * @return 人口详情 */ PopulationDetailVO getPopulationDetailApp(@Param("populationId") Long populationId); @Select("<script> " + " SELECT " + " cmp.id, " + " cmp.`name` AS userName, " + " cmpct.label, " + " cmp.card_no, " + " cmp.card_no_str, " + " cmp.sex, " + " cmp.address, " + " cmp.political_outlook, " + " cmp.census_register, " + " cmp.house_id, " + " cmp.phone, " + " IFNULL(cmp.house_id,0) as isHouse, " + " ( SELECT event_status FROM event_visiting_tasks WHERE event_status in (1,2,3,5) AND visiter_id = cmp.id ORDER BY create_at DESC LIMIT 1 ) AS eventStatus, " + " ( SELECT create_at FROM event_visiting_tasks WHERE event_status in (1,2,3,5) AND visiter_id = cmp.id ORDER BY create_at DESC LIMIT 1 ) AS createAt " + " FROM " + " com_mng_population AS cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id where 1=1 " + "<if test='populationListDTO.keyWord != null and populationListDTO.keyWord != ""'>" + "AND (cmp.name like concat (#{populationListDTO.keyWord},'%') or cmp.card_no = #{populationListDTO.cardNo} or cmp.address like concat (#{populationListDTO.keyWord},'%')) " + " </if> " + "<if test='populationListDTO.label != null and populationListDTO.label != ""'>" + "AND cmpct.label like concat ('%',#{populationListDTO.label},'%') " + " </if> " + "<if test='populationListDTO.sex != null'>" + "AND cmp.sex = #{populationListDTO.sex} " + " </if> " + "<if test='populationListDTO.isHouse != null and populationListDTO.isHouse == 1'>" + "AND cmp.house_id is not null " + " </if> " + "<if test='populationListDTO.isHouse != null and populationListDTO.isHouse == 2'>" + "AND cmp.house_id is null " + " </if> " + "<if test='populationListDTO.communityId != null'>" + "AND cmpct.community_id = #{populationListDTO.communityId} " + " </if> " + "<if test='populationListDTO.politicalOutlook != null'>" + "AND cmp.political_outlook = #{populationListDTO.politicalOutlook} " + " </if> " + " </script>") /** * 根据人口id查询人口在各个社区的标签列表 * @param populationId 人口id * @return 人口在各个社区的标签列表 */ List<ComMngPopulationCommunityTagsVo> getCommunityTagList(@Param("populationId") Long populationId); /** * 综治后台-分页查询居民列表 * @param page 分页参数 * @param populationListDTO 请求参数 * @return 居民列表 */ IPage<ComMngPopulationListVO> getGridPopulationAdminList(Page page, @Param("populationListDTO") ComMngPopulationListDTO populationListDTO);
@@ -63,6 +63,12 @@ */ private Long updateBy; /** * 社区名字 */ @TableField(exist = false) private String communityName; @Override public String toString() { return "ComMngPopulationCommunityTagsDO{" + "id=" + id + ", populationId=" + populationId + ", communityId=" springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngPopulationServiceImpl.java
@@ -9,6 +9,7 @@ import com.baomidou.mybatisplus.core.conditions.Wrapper; import com.baomidou.mybatisplus.core.conditions.segments.MergeSegments; import com.panzhihua.common.utlis.*; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; @@ -54,10 +55,6 @@ import com.panzhihua.common.model.vos.screen.ComActPopulationScreenVO; import com.panzhihua.common.model.vos.screen.ComMngPopulationAgeVO; import com.panzhihua.common.model.vos.user.*; import com.panzhihua.common.utlis.AgeUtils; import com.panzhihua.common.utlis.DateUtils; import com.panzhihua.common.utlis.Snowflake; import com.panzhihua.common.utlis.StringUtils; import com.panzhihua.service_community.dao.*; import com.panzhihua.service_community.model.dos.*; import com.panzhihua.service_community.service.*; @@ -7473,6 +7470,10 @@ if (StringUtils.isNotEmpty(detail.getMarriage())) { detail.setMarriage(PopulMarriageEnum.getCnDescByName(Integer.parseInt(detail.getMarriage()))); } //查询该人口的标签 List<ComMngPopulationCommunityTagsVo> communityTagsList = this.baseMapper.getCommunityTagList(populationId); String label = LabelUtils.assembleLabel(communityTagsList); detail.setLabel(label); } return R.ok(detail); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/resources/mapper/ComMngPopulationDOMapper.xml
@@ -423,4 +423,181 @@ select t.*,t2.address as house_address,t2.house_id as house_house_id ,t2.residence as house_residence ,t2.status,t2.relation as s_relation,t2.relation_id as s_relation_id,t2.popul_id from com_mng_population t left join ( select t.address,t1.house_id,t1.popul_id,t1.relation,t1.relation_id,t.status,t1.residence from com_mng_population_house t left join com_mng_population_house_user t1 on t.id = t1.house_id) t2 on t.id = t2.popul_id where t.id =#{id} </select> <select id="getPopulationDetailApp" resultType="com.panzhihua.common.model.vos.grid.PopulationDetailVO"> SELECT cmp.id, cmp.`name`, cmp.phone, cmp.card_no, cmp.address, cmp.culture_level, cmp.nation, cmp.political_outlook, cmp.sex, cmp.birthday, cmp.native_place, cmp.healthy, cmp.is_rent, cmv.alley AS road, cmv.house_num AS doorNo, cmp.floor, cmp.unit_no, cmp.house_no, cmp.work_company, cmp.census_register, cmp.out_or_local, cmp.remark, cmp.marriage FROM com_mng_population AS cmp LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id WHERE cmp.id = #{populationId} </select> <select id="getCommunityTagList" resultType="com.panzhihua.common.model.vos.community.ComMngPopulationCommunityTagsVo"> SELECT cmpct.population_id, cmpct.community_id, cmpct.label, ca.`name` AS communityName FROM com_mng_population_community_tags AS cmpct LEFT JOIN com_act AS ca ON ca.community_id = cmpct.community_id WHERE cmpct.population_id = #{populationId} </select> <select id="getGridPopulationAdminList" resultType="com.panzhihua.common.model.vos.grid.admin.ComMngPopulationListVO"> SELECT cmp.id, cmp.`name` AS userName, cmpct.label, cmp.card_no, cmp.card_no_str, cmp.sex, cmp.address, cmp.political_outlook, cmp.census_register, cmp.house_id, cmp.phone, IFNULL(cmp.house_id,0) as isHouse, ( SELECT event_status FROM event_visiting_tasks WHERE event_status in (1,2,3,5) AND visiter_id = cmp.id ORDER BY create_at DESC LIMIT 1 ) AS eventStatus, ( SELECT create_at FROM event_visiting_tasks WHERE event_status in (1,2,3,5) AND visiter_id = cmp.id ORDER BY create_at DESC LIMIT 1 ) AS createAt FROM com_mng_population AS cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id where 1=1 <if test='populationListDTO.keyWord != null and populationListDTO.keyWord != ""'> AND (cmp.name like concat (#{populationListDTO.keyWord},'%') or cmp.card_no = #{populationListDTO.cardNo} or cmp.address like concat (#{populationListDTO.keyWord},'%')) </if> <if test='populationListDTO.label != null and populationListDTO.label != ""'> AND cmpct.label like concat ('%',#{populationListDTO.label},'%') </if> <if test='populationListDTO.sex != null'> AND cmp.sex = #{populationListDTO.sex} </if> <if test='populationListDTO.isHouse != null and populationListDTO.isHouse == 1'> AND cmp.house_id is not null </if> <if test='populationListDTO.isHouse != null and populationListDTO.isHouse == 2'> AND cmp.house_id is null </if> <if test='populationListDTO.communityId != null'> AND cmpct.community_id = #{populationListDTO.communityId} </if> <if test='populationListDTO.politicalOutlook != null'> AND cmp.political_outlook = #{populationListDTO.politicalOutlook} </if> </select> <select id="getEventScreenLeftDown" resultType="com.panzhihua.common.model.vos.community.screen.event.EventLeftDownStatisticsVO"> SELECT count( e.id ) AS eventZATotal, IFNULL(( SELECT count( e1.id ) FROM `event` AS e1 LEFT JOIN event_grid_data AS egd1 ON egd1.id = e1.grid_id WHERE egd1.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 1 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e1.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e1.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventZATotal, IFNULL(( SELECT count( e2.id ) FROM `event` AS e2 LEFT JOIN event_grid_data AS egd2 ON egd2.id = e2.grid_id WHERE egd2.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 5 AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e2.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e2.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventTFTotal, IFNULL(( SELECT count( e3.id ) FROM `event` AS e3 LEFT JOIN event_grid_data AS egd3 ON egd3.id = e3.grid_id WHERE egd3.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 5 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e3.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e3.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventTFTotal, IFNULL(( SELECT count( e4.id ) FROM `event` AS e4 LEFT JOIN event_grid_data AS egd4 ON egd4.id = e4.grid_id WHERE egd4.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 6 AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e4.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e4.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventTSTotal, IFNULL(( SELECT count( e5.id ) FROM `event` AS e5 LEFT JOIN event_grid_data AS egd5 ON egd5.id = e5.grid_id WHERE egd5.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 6 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e5.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e5.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventTSTotal, IFNULL(( SELECT count( e6.id ) FROM `event` AS e6 LEFT JOIN event_grid_data AS egd6 ON egd6.id = e6.grid_id WHERE egd6.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 3 AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e6.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e6.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventMDTotal, IFNULL(( SELECT count( e7.id ) FROM `event` AS e7 LEFT JOIN event_grid_data AS egd7 ON egd7.id = e7.grid_id WHERE egd7.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 3 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e7.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e7.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventMDTotal, IFNULL(( SELECT count( e8.id ) FROM `event` AS e8 LEFT JOIN event_grid_data AS egd8 ON egd8.id = e8.grid_id WHERE egd8.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 4 AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e8.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e8.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventBWDTotal, IFNULL(( SELECT count( e9.id ) FROM `event` AS e9 LEFT JOIN event_grid_data AS egd9 ON egd9.id = e9.grid_id WHERE egd9.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 4 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e9.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e9.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventBWDTotal, IFNULL(( SELECT count( e10.id ) FROM `event` AS e10 LEFT JOIN event_grid_data AS egd10 ON egd10.id = e10.grid_id WHERE egd10.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 2 AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e10.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e10.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventGGTotal, IFNULL(( SELECT count( e11.id ) FROM `event` AS e11 LEFT JOIN event_grid_data AS egd11 ON egd11.id = e11.grid_id WHERE egd11.grid_community_id = #{screenEventDTO.communityId} AND event_category = 1 AND event_type = 2 AND event_deal_status = 4 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e11.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e11.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventGGTotal, IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND community_id = #{screenEventDTO.communityId} <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS eventSSPTotal, IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND `status` = 4 AND community_id = #{screenEventDTO.communityId} <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> ), 0 ) AS yesEventSSPTotal FROM `event` AS e LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id WHERE egd.grid_community_id = #{screenEventDTO.communityId} AND event_status = 2 <if test='screenEventDTO.startTime != null and screenEventDTO.startTime != ""'> AND e.create_at <![CDATA[>=]]> #{screenEventDTO.startTime} </if> <if test='screenEventDTO.endTime != null and screenEventDTO.endTime != ""'> AND e.create_at <![CDATA[<=]]> #{screenEventDTO.endTime} </if> </select> </mapper> springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/dao/EventVisitingTasksMapper.java
@@ -1,7 +1,9 @@ package com.panzhihua.service_grid.dao; import java.util.List; import java.util.Map; import com.panzhihua.common.model.vos.community.ComMngPopulationCommunityTagsVo; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; @@ -112,4 +114,10 @@ String getPopulationLabel(@Param("populationId") Long populationId); /** * 根据人口id查询人口在各个社区的标签列表 * @param populationId 人口id * @return 人口在各个社区的标签列表 */ List<ComMngPopulationCommunityTagsVo> getCommunityTagList(@Param("populationId") Long populationId); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/service/impl/EventVisitingTasksServiceImpl.java
@@ -4,7 +4,9 @@ import javax.annotation.Resource; import com.panzhihua.common.model.vos.community.ComMngPopulationCommunityTagsVo; import com.panzhihua.common.utlis.DateUtils; import com.panzhihua.common.utlis.LabelUtils; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; @@ -172,6 +174,10 @@ eventVisitingTasksVO .setNation(PopulPoliticalOutlookEnum.getCnDescByName(eventVisitingTasksVO.getNationCode())); } //查询该人口的标签 List<ComMngPopulationCommunityTagsVo> communityTagsList = this.baseMapper.getCommunityTagList(eventVisitingTasksVO.getVisiterId()); String label = LabelUtils.assembleLabel(communityTagsList); eventVisitingTasksVO.setLabel(label); } return R.ok(page); } @@ -186,6 +192,11 @@ eventVisitingTasksVO .setNation(PopulPoliticalOutlookEnum.getCnDescByName(eventVisitingTasksVO.getNationCode())); } //查询该人口的标签 List<ComMngPopulationCommunityTagsVo> communityTagsList = this.baseMapper.getCommunityTagList(eventVisitingTasksVO.getVisiterId()); String label = LabelUtils.assembleLabel(communityTagsList); eventVisitingTasksVO.setLabel(label); } return R.ok(page); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/resources/mapper/EventMapper.xml
@@ -1045,7 +1045,7 @@ egd1.grid_community_id = #{communityId} AND e1.event_category = 1 AND e1.event_process_status = 1 AND e1.event_status AND e1.event_status = 2 AND e1.event_deal_status = 1 ) AS pendingNum, ( @@ -1068,7 +1068,7 @@ WHERE e3.event_status = 2 AND e3.create_at LIKE CONCAT(#{nowDate},'%')) as currentNum, (select count(id) from com_act_easy_photo where community_id = 2 and del_tag = 0 and create_at LIKE CONCAT('2021-07','%')) as sspCurrentNum (select count(id) from com_act_easy_photo where community_id = 2 and del_tag = 0 and create_at LIKE CONCAT(#{nowDate},'%')) as sspCurrentNum FROM `event` AS e @@ -1228,7 +1228,7 @@ AND e5.event_type = 6 AND egd5.grid_community_id = #{communityId} ) AS tsTotal, (select count(id) from com_act_easy_photo as caep where community_id = #{communityId} and del_tag = 0 and handle_status = 2) as sspTotal (select count(id) from com_act_easy_photo as caep where community_id = #{communityId} and del_tag = 0 and status = 4) as sspTotal FROM `event` AS e LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id @@ -1302,7 +1302,7 @@ AND e5.event_type = 6 AND egd5.grid_community_id = #{communityId} ) AS tsTotal, (select count(id) from com_act_easy_photo as caep where community_id = #{communityId} and del_tag = 0 and handle_status = 1) as sspTotal (select count(id) from com_act_easy_photo as caep where community_id = #{communityId} and del_tag = 0 and status in (1,2)) as sspTotal FROM `event` AS e LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/resources/mapper/EventVisitingTasksMapper.xml
@@ -51,16 +51,15 @@ <select id="count" parameterType="java.lang.Long" resultType="java.util.Map"> select count(id) as visit, (select count(id) from event_visiting_tasks where event_status = 3) as visited, (select count(id) from event_visiting_tasks where event_status = 6) as canceled (select count(id) from event_visiting_tasks where event_status = 2) as visited, (select count(id) from event_visiting_tasks where event_status = 5) as canceled from event_visiting_tasks WHERE event_status = 1 </select> <select id="findListByPage" parameterType="com.panzhihua.common.model.query.visit.EventTasksQuery" resultType="com.panzhihua.common.model.vos.visit.EventVisitingTasksVO"> select vt.id,vt.create_at,vt.create_by,vt.visiter_id,vt.visiter_name,vt.visiter_tele,vt.visiter_sex,vt.visiter_address,vt.dell_user_id,vt.dell_user_name,vt.submit_date,vt.cancel_time,vt.event_status, TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS age,p.card_no_str,cmpct.label,u.name as creator,p.nation_code,vt.exception,vt.grid_member_name,vt.grid_menber_tele,egd.grid_name AS gridName TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS age,p.card_no_str,u.name as creator,p.nation_code,vt.exception,vt.grid_member_name,vt.grid_menber_tele,egd.grid_name AS gridName from event_visiting_tasks vt left join com_mng_population p on vt.visiter_id = p.id left join com_mng_population_community_tags AS cmpct on p.id = cmpct.population_id left join sys_user u on vt.create_by = u.user_id left join event_grid_data egd on egd.id = vt.grid_id where 1=1 @@ -89,10 +88,9 @@ resultType="com.panzhihua.common.model.vos.visit.EventVisitingTasksVO"> select vt.id,vt.create_at,vt.create_by,vt.visiter_id,vt.visiter_name,vt.visiter_tele,vt.visiter_sex ,vt.visiter_address,vt.dell_user_id,vt.dell_user_name,vt.submit_date,vt.cancel_time,vt.event_status,p.nation,p.political_outlook, TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS age,cmpct.label,u.name as creator,p.nation_code,p.card_no as cardNoStr TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS age,u.name as creator,p.nation_code,p.card_no as cardNoStr FROM event_visiting_tasks vt LEFT JOIN com_mng_population p ON p.id = vt.visiter_id left join com_mng_population_community_tags AS cmpct on p.id = cmpct.population_id LEFT JOIN sys_user u ON u.user_id = vt.create_by where 1=1 <if test = "query.gridId != null"> @@ -209,5 +207,18 @@ select label from com_mng_population where id = #{populationId} </select> <select id="getCommunityTagList" resultType="com.panzhihua.common.model.vos.community.ComMngPopulationCommunityTagsVo"> SELECT cmpct.population_id, cmpct.community_id, cmpct.label, ca.`name` AS communityName FROM com_mng_population_community_tags AS cmpct LEFT JOIN com_act AS ca ON ca.community_id = cmpct.community_id WHERE cmpct.population_id = #{populationId} </select> </mapper>
