Merge branch 'test_screen' into 'master'

罗元桥

2021-06-16 02b5015e989fef8878744d50f21a092efa395231

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java

@@ -6,6 +6,7 @@
import com.panzhihua.common.model.dtos.community.ComMngPopulationDTO;
import com.panzhihua.common.model.dtos.user.PageInputUserDTO;
import com.panzhihua.common.model.vos.area.AreaAddressVO;
import com.panzhihua.common.model.vos.community.ComActMessageVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationTotalVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationVO;
import com.panzhihua.common.model.vos.community.screen.civil.CivilPopulationStatisticsVO;
@@ -14,6 +15,11 @@
import com.panzhihua.common.model.vos.community.screen.index.*;
import com.panzhihua.common.model.vos.grid.EventGridDataVO;
import com.panzhihua.common.model.vos.user.*;
import com.panzhihua.common.model.vos.community.PageComActMessageVO;
import com.panzhihua.common.model.vos.user.ComHouseMemberVo;
import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO;
import com.panzhihua.common.model.vos.user.InputUserInfoVO;
import com.panzhihua.common.model.vos.user.UserElectronicFileVO;
import com.panzhihua.service_community.model.dos.ComMngPopulationDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -21,6 +27,7 @@

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 实有人口Dao
@@ -29,29 +36,29 @@
public interface ComMngPopulationDAO extends BaseMapper<ComMngPopulationDO> {

    @Select("<script> " +
            "SELECT " +
            "cmp.`name`, " +
            "cmp.id, " +
            "cmp.street_id, " +
            "cmp.act_id, " +
            "cmp.village_id, " +
            "cmp.sex, " +
            "cmp.age, " +
            "cmp.card_no, " +
            "cmp.road, " +
            "cmp.door_no, " +
            "cmp.floor, " +
            "cmp.unit_no, " +
            "cmp.house_no, " +
            "cmp.political_outlook, " +
            "cmp.work_company, " +
            "cmp.special_situation, " +
            "cmp.phone, " +
            "cmp.remark, " +
            "cmv.alley, " +
            "cmp.native_place, " +
            "cmp.nation  " +
            "FROM  " +
            "SELECT\n" +
            "cmp.`name`,\n" +
            "cmp.id,\n" +
            "cmp.street_id,\n" +
            "cmp.act_id,\n" +
            "cmp.village_id,\n" +
            "cmp.sex,\n" +
            "cmp.age,\n" +
            "cmp.card_no,\n" +
            "cmp.road,\n" +
            "cmp.door_no,\n" +
            "cmp.floor,\n" +
            "cmp.unit_no,\n" +
            "cmp.house_no,\n" +
            "cmp.political_outlook,\n" +
            "cmp.work_company,\n" +
            "cmp.special_situation,\n" +
            "cmp.phone,\n" +
            "cmp.remark,\n" +
            "cmv.alley,\n" +
            "cmp.native_place,\n" +
            "cmp.nation \n" +
            "FROM \n" +
            "com_mng_population AS cmp " +
            "LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id " +
            "<where>" +
@@ -106,39 +113,39 @@
    List<ComHouseMemberVo> listHouseMermberByUserId(@Param("houseId") Long houseId, @Param("populId") Long populId);

    @Select("<script> " +
            "SELECT " +
            "cmp.`name`, " +
            "cmp.id, " +
            "cmp.street_id, " +
            "cmp.act_id, " +
            "cmp.village_id, " +
            "cmp.sex, " +
            "cmp.age, " +
            "cmp.card_no, " +
            "cmp.road, " +
            "cmp.door_no, " +
            "cmp.floor, " +
            "cmp.unit_no, " +
            "cmp.house_no, " +
            "cmp.political_outlook, " +
            "cmp.work_company, " +
            "cmp.special_situation, " +
            "cmp.phone, " +
            "cmp.remark, " +
            "cmv.alley, " +
            "cmp.native_place, " +
            "cmp.nation,  " +
            "cmp.label,  " +
            "cmp.relation,  " +
            "cmp.marriage,  " +
            "cmp.culture_level,  " +
            "cmp.out_or_local,  " +
            "cmp.census_register,  " +
            "cmp.healthy,  " +
            "cmp.birthday,  " +
            "cmp.update_at,  " +
            "cmp.is_rent  " +
            "FROM  " +
            "SELECT\n" +
            "cmp.`name`,\n" +
            "cmp.id,\n" +
            "cmp.street_id,\n" +
            "cmp.act_id,\n" +
            "cmp.village_id,\n" +
            "cmp.sex,\n" +
            "cmp.age,\n" +
            "cmp.card_no,\n" +
            "cmp.road,\n" +
            "cmp.door_no,\n" +
            "cmp.floor,\n" +
            "cmp.unit_no,\n" +
            "cmp.house_no,\n" +
            "cmp.political_outlook,\n" +
            "cmp.work_company,\n" +
            "cmp.special_situation,\n" +
            "cmp.phone,\n" +
            "cmp.remark,\n" +
            "cmv.alley,\n" +
            "cmp.native_place,\n" +
            "cmp.nation, \n" +
            "cmp.label, \n" +
            "cmp.relation, \n" +
            "cmp.marriage, \n" +
            "cmp.culture_level, \n" +
            "cmp.out_or_local, \n" +
            "cmp.census_register, \n" +
            "cmp.healthy, \n" +
            "cmp.birthday, \n" +
            "cmp.update_at, \n" +
            "cmp.is_rent \n" +
            "FROM \n" +
            "com_mng_population AS cmp " +
            "LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id " +
            "<where>" +
@@ -661,4 +668,38 @@
    List<CivilVillageStatisticsVO> getCivilScreenVillageList(@Param("communityId") Long communityId);


   @Select("SELECT COUNT(id) AS man,(SELECT COUNT(id) FROM com_mng_population WHERE sex = 2 AND act_id = #{communityId}) AS woman FROM com_mng_population WHERE sex = 1 AND act_id = #{communityId}")
    Map<String, Long> countBySex(@Param("communityId") Long communityId);

    @Select(
            "SELECT " +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) as aa where aa.age<= 16) as age16," +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 25) AS age25," +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 25 and aa.age<= 35) AS age35," +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45," +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55," +
                    "(SELECT COUNT(aa.age) FROM " +
                    "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
    Map<String, Long> countByAge(@Param("communityId")Long communityId);

    @Select("SELECT COUNT(id)AS xx," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 2 AND act_id = #{communityId}) as cz," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 3 AND act_id = #{communityId}) as gz," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 4 AND act_id = #{communityId}) as zz," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 5 AND act_id = #{communityId}) as dz," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 6 AND act_id = #{communityId}) as bk," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 7 AND act_id = #{communityId}) as ss," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 8 AND act_id = #{communityId}) as bs," +
            "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 9 AND act_id = #{communityId}) as qt" +
            " FROM com_mng_population WHERE culture_level = 1 AND act_id = #{communityId}")
    Map<String, Long> countByCulture(@Param("communityId")Long communityId);

    @Select("SELECT COUNT(p.id) FROM com_mng_population p INNER JOIN sys_user u ON p.card_no = u.id_card where p.act_id = #{communityId}")
    Long countUsedCommunityPopulation(@Param("communityId")Long communityId);

}