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;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.panzhihua.common.model.dtos.grid.ComMngVillageListAppDTO;
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.ComMngVillageBuildingVO;
import com.panzhihua.common.model.vos.grid.PageComMngVillageVO;
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;

/**
 * @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 road = (select alley from com_mng_village where village_id = #{villageId})"
        + " and door_no = (select house_num from com_mng_village 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(distinct `name`) as villageTotal"
        + ",(select count(distinct `name`) from com_mng_village where community_id = ${communityId} and type = 1) as townTotal"
        + ",(select count(distinct `name`) 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, " + " IFNULL((select count(id) from com_mng_building where village_id = cmv.village_id),0) as buildSum, " + " 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 != &quot;&quot;'>"
        + " and cmv.address like concat(#{villageListAppDTO.address},'%') " + " </if> "
        + "<if test='villageListAppDTO.alley != null and villageListAppDTO.alley != &quot;&quot;'>"
        + " and cmv.alley like concat(#{villageListAppDTO.alley},'%') " + " </if> "
        + "<if test='villageListAppDTO.houseNum != null and villageListAppDTO.houseNum != &quot;&quot;'>"
        + " and cmv.house_num like concat(#{villageListAppDTO.houseNum},'%') " + " </if> "
        + "<if test='villageListAppDTO.groupAt != null and villageListAppDTO.groupAt != &quot;&quot;'>"
        + " and cmv.group_at like concat(#{villageListAppDTO.groupAt},'%') " + " </if> "
        + "<if test='villageListAppDTO.name != null and villageListAppDTO.name != &quot;&quot;'>"
        + " 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);

    IPage<PageComMngVillageVO> getGridVillageListApp(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 != &quot;&quot;'>"
        + " and cmv.address like concat(#{villageListAppDTO.address},'%') " + " </if> "
        + "<if test='villageListAppDTO.alley != null and villageListAppDTO.alley != &quot;&quot;'>"
        + " and cmv.alley like concat(#{villageListAppDTO.alley},'%') " + " </if> "
        + "<if test='villageListAppDTO.houseNum != null and villageListAppDTO.houseNum != &quot;&quot;'>"
        + " and cmv.house_num like concat(#{villageListAppDTO.houseNum},'%') " + " </if> "
        + "<if test='villageListAppDTO.groupAt != null and villageListAppDTO.groupAt != &quot;&quot;'>"
        + " and cmv.group_at like concat(#{villageListAppDTO.groupAt},'%') " + " </if> "
        + "<if test='villageListAppDTO.name != null and villageListAppDTO.name != &quot;&quot;'>"
        + " 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();
}