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.grid.ComMngVillageListAppDTO;
|
import com.panzhihua.common.model.dtos.grid.PageComMngVillageBuildHouseAppDTO;
|
import com.panzhihua.common.model.dtos.grid.admin.ComMngPopulationListDTO;
|
import com.panzhihua.common.model.dtos.grid.admin.ComMngVillageListExportAdminDTO;
|
import com.panzhihua.common.model.vos.community.ComMngVillageTotalVO;
|
import com.panzhihua.common.model.vos.community.ComMngVillageVO;
|
import com.panzhihua.common.model.vos.grid.ComMngVillageBuildingHouseVO;
|
import com.panzhihua.common.model.vos.grid.ComMngVillageBuildingVO;
|
import com.panzhihua.common.model.vos.grid.PageComMngVillageVO;
|
import com.panzhihua.common.model.vos.grid.PopulationDetailVO;
|
import com.panzhihua.common.model.vos.grid.admin.ComMngVillageStatisticsVO;
|
import com.panzhihua.common.model.vos.grid.admin.excel.ComMngVillageExportExcelVO;
|
import com.panzhihua.service_community.model.dos.ComMngVillageDO;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.List;
|
|
|
/**
|
* @author: llming
|
* @description: 街道
|
**/
|
@Mapper
|
public interface ComActVillageDAO extends BaseMapper<ComMngVillageDO> {
|
|
@Select("select count(id) as houseTotal" +
|
",(select count(DISTINCT floor) from com_mng_population_house where village_id = #{villageId}) as floorTotal" +
|
",(select count(id) from com_mng_population where village_id = #{villageId}) as populationTotal" +
|
",(select count(id) from com_mng_car where area_id = #{villageId}) as carTotal" +
|
" from com_mng_population_house where village_id = #{villageId}")
|
ComMngVillageVO getStatisticsCount(@Param("villageId") Long villageId);
|
|
@Select("select count(village_id) as villageTotal" +
|
",(select count(village_id) from com_mng_village where community_id = #{communityId} and type = 1) as townTotal" +
|
",(select count(village_id) from com_mng_village where community_id = #{communityId} and type = 2) as countrysideTotal" +
|
" from com_mng_village where community_id = #{communityId}" )
|
ComMngVillageTotalVO getVillageStatisticsCount(@Param("communityId") Long communityId);
|
|
@Select("select village_id,alley,house_num,group_at,type,address,update_at from com_mng_village where village_id = #{villageId}")
|
ComMngVillageVO getVillageById(@Param("villageId") Long villageId);
|
|
@Select("<script> " +
|
"SELECT " +
|
" village_id, " +
|
" `name` AS userName, " +
|
" build_sum, " +
|
" address, " +
|
" create_at as createAt, " +
|
" lng, " +
|
" lat, " +
|
" ( SELECT count( id ) FROM com_mng_population WHERE village_id = cmv.village_id ) AS userSum, " +
|
" ( SELECT count( id ) FROM com_mng_population_house WHERE village_id = cmv.village_id ) AS houseNum, " +
|
" ( SELECT count( id ) FROM com_mng_population WHERE village_id = cmv.village_id AND out_or_local = 1 ) AS registerNum, " +
|
" ( SELECT count( id ) FROM com_mng_population WHERE village_id = cmv.village_id AND is_rent = 2 ) AS rentNum " +
|
"FROM " +
|
" com_mng_village AS cmv where 1=1 " +
|
"<if test='villageListAppDTO.communityId != null'>" +
|
" and cmv.community_id = #{villageListAppDTO.communityId} "+
|
" </if> " +
|
"<if test='villageListAppDTO.address != null and villageListAppDTO.address != ""'>" +
|
" and cmv.address like concat(#{villageListAppDTO.address},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.alley != null and villageListAppDTO.alley != ""'>" +
|
" and cmv.alley like concat(#{villageListAppDTO.alley},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.houseNum != null and villageListAppDTO.houseNum != ""'>" +
|
" and cmv.house_num like concat(#{villageListAppDTO.houseNum},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.groupAt != null and villageListAppDTO.groupAt != ""'>" +
|
" and cmv.group_at like concat(#{villageListAppDTO.groupAt},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.name != null and villageListAppDTO.name != ""'>" +
|
" and cmv.name like concat(#{villageListAppDTO.name},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.sortColumns!=null'>" +
|
" ORDER BY ${villageListAppDTO.sortColumns} ${villageListAppDTO.sortType} " +
|
" </if>" +
|
" </script>")
|
IPage<PageComMngVillageVO> getGridVillageList(Page page, @Param("villageListAppDTO") ComMngVillageListAppDTO villageListAppDTO);
|
|
@Select("<script> " +
|
"SELECT " +
|
" alley, " +
|
" house_num as doorNum, " +
|
" `name` AS userName, " +
|
" build_sum, " +
|
" address, " +
|
" create_at as createAt, " +
|
" ( SELECT count( id ) FROM com_mng_population WHERE village_id = cmv.village_id ) AS userSum, " +
|
" ( SELECT count( id ) FROM com_mng_population_house WHERE village_id = cmv.village_id ) AS houseNum " +
|
"FROM " +
|
" com_mng_village AS cmv where 1=1 " +
|
"<if test='villageListAppDTO.communityId != null'>" +
|
" and cmv.community_id = #{villageListAppDTO.communityId} "+
|
" </if> " +
|
"<if test='villageListAppDTO.villageIds != null and villageListAppDTO.villageIds.size > 0'>" +
|
" and cmv.village_id in "+
|
"<foreach item='item' collection='villageListAppDTO.villageIds' separator=',' open='(' close=')' index=''> " +
|
"#{item}" +
|
"</foreach>" +
|
" </if> " +
|
"<if test='villageListAppDTO.address != null and villageListAppDTO.address != ""'>" +
|
" and cmv.address like concat(#{villageListAppDTO.address},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.alley != null and villageListAppDTO.alley != ""'>" +
|
" and cmv.alley like concat(#{villageListAppDTO.alley},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.houseNum != null and villageListAppDTO.houseNum != ""'>" +
|
" and cmv.house_num like concat(#{villageListAppDTO.houseNum},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.groupAt != null and villageListAppDTO.groupAt != ""'>" +
|
" and cmv.group_at like concat(#{villageListAppDTO.groupAt},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.name != null and villageListAppDTO.name != ""'>" +
|
" and cmv.name like concat(#{villageListAppDTO.name},'%') "+
|
" </if> " +
|
"<if test='villageListAppDTO.sortColumns!=null'>" +
|
" ORDER BY ${villageListAppDTO.sortColumns} ${villageListAppDTO.sortType} " +
|
" </if>" +
|
" </script>")
|
List<ComMngVillageExportExcelVO> getGridVillageLists(@Param("villageListAppDTO") ComMngVillageListExportAdminDTO villageListAppDTO);
|
|
@Select("SELECT DISTINCT " +
|
" village_id," +
|
" floor, " +
|
" unit_no " +
|
"FROM " +
|
" com_mng_population_house AS cmph " +
|
"WHERE " +
|
" village_id = #{villageId}")
|
List<ComMngVillageBuildingVO> getGridVillageBuildingList(@Param("villageId") Long villageId);
|
|
@Select("<script> " +
|
" select count(village_id) from com_mng_population_house where village_id in " +
|
"<foreach item='item' collection='villageIds' separator=',' open='(' close=')' index=''> " +
|
"#{item} " +
|
"</foreach>" +
|
"</script>")
|
Integer getVillageHouseCount(@Param("villageIds") List<Long> villageIds);
|
|
@Select("SELECT " +
|
" count( village_id ) AS villageNum, " +
|
" ( SELECT count( village_id ) FROM ( SELECT DISTINCT village_id, floor FROM com_mng_population_house ) AS floor ) AS floorNum, " +
|
" ( SELECT count( id ) FROM com_mng_population_house ) AS houseNum, " +
|
" ( SELECT count( id ) FROM com_mng_population ) AS userNum " +
|
"FROM " +
|
" com_mng_village")
|
ComMngVillageStatisticsVO getGridVillageStatistics();
|
}
|
