#feat 修改运营平台统计登录人数和新增人员

yanghui

2022-10-14 c6232cfc36e373f83f45c95d400e09466cc2a7d3

 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/SysUserInputDAO.java

@@ -1,19 +1,22 @@
package com.panzhihua.service_user.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

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.ExportSpecialUserDTO;
import com.panzhihua.common.model.dtos.community.ExportUserDTO;
import com.panzhihua.common.model.dtos.user.EexcelUserDTO;
import com.panzhihua.common.model.dtos.user.PageInputUserDTO;
import com.panzhihua.common.model.vos.community.ComMngStructAreaVO;
import com.panzhihua.common.model.vos.community.ComMngStructHouseVO;
import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO;
import com.panzhihua.common.model.vos.user.InputUserInfoVO;
import com.panzhihua.service_user.model.dos.SysUserInputDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @program: springcloud_k8s_panzhihuazhihuishequ
@@ -23,168 +26,30 @@
 **/
@Mapper
public interface SysUserInputDAO extends BaseMapper<SysUserInputDO> {
    //    @Select("<script> " +
//            " <where>" +
//            "<if test='nameCn != null and nameCn.trim() != &quot;&quot;'>" +
//            " </if> " +
//            "<if test='nameCn != null and nameCn.trim() != &quot;&quot;'>" +
//            " </if> " +
//            "<if test='nameCn != null and nameCn.trim() != &quot;&quot;'>" +
//            " </if> " +
//            " </where>" +
//            "</script>")
    @Select("select a.id,a.community_id from com_mng_struct_area a where a.area_name=#{areaName}")
    ComMngStructAreaVO selectByAreaName(String areaName);
    @Select("SELECT \n" +
            "REPLACE(REPLACE(REPLACE(REPLACE(h.house_name,a.address_detail,''),'栋','-'),'单元','-'),'层','-')houseName,\n" +
            "h.house_code\n" +
            "FROM\n" +
            "\tcom_mng_struct_area a \n" +
            "\tjoin com_mng_struct_house h on h.house_name like CONCAT(a.address_detail,'%') and h.type=5\n" +
            "WHERE\n" +
            "\ta.area_name = #{areaName}")
    List<ComMngStructHouseVO> selectHouserByareaName(String areaName);
    @Select("SELECT \n" +
            "p.name,\n" +
            "p.id,\n" +
            "if(p.is_rent=1,'合租','家人')relationship,\n" +
            "p.id_card,\n" +
            "p.phone,\n" +
            "year(curdate())-if(length(p.id_card)=18,substring(p.id_card,7,4),if(length(p.id_card)=15,concat('19',substring(p.id_card,7,2)),null)) as age,\n" +
            "p.create_at\n" +
            "FROM\n" +
            "\tsys_user_input p \n" +
            "WHERE\n" +
            "\tp.door_number = #{doorNumber} \n" +
            "\tAND p.id !=#{userId}")
    List<ComMngFamilyInfoVO> selectListFamily(@Param("doorNumber") String doorNumber, @Param("userId")Long userId);

    @Select("<script> " +
            "SELECT " +
            " t.`name`, " +
            "CASE " +
            " IF " +
            "  ( " +
            "   length( t.id_card ) = 18, " +
            "   cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " +
            "  IF " +
            "   ( " +
            "    length( t.id_card ) = 15, " +
            "    cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " +
            "    3  " +
            "   )  " +
            "  )  " +
            "  WHEN 1 THEN " +
            "  1  " +
            "  WHEN 0 THEN " +
            "  2 ELSE 0  " +
            " END AS sex, " +
            " t.phone, " +
            " YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
            " t1.area_name, " +
            " t.political_outlook politicalOutlook, " +
            " CONCAT( " +
            " IF " +
            "  ( t.soldier = 1, '军人,', '' ), " +
            " IF " +
            "  ( t.lowIncome_households = 1, '低保户,', '' ), " +
            " IF " +
            "  ( t.lowIncome_people = 1, '低收入,', '' ), " +
            " IF " +
            "  ( t.old_people = 1, '特服家庭,', '' ), " +
            " IF " +
            "  ( t.key_personnel = 1, '重点人员,', '' )  " +
            " ) tags  " +
            "FROM " +
            " sys_user_input t " +
            " LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id  " +
            "WHERE " +
            " t.community_id = #{pageInputUserDTO.communityId}"+
            "<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != &quot;&quot;'>" +
            " and t.`name` like concat(#{pageInputUserDTO.name},'%')  " +
            " </if> " +
            "<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != &quot;&quot;'>" +
            " and t.phone like concat(#{pageInputUserDTO.phone},'%')  " +
            " </if> " +
            "<if test='pageInputUserDTO.areaId != null and pageInputUserDTO.areaId.trim() != &quot;&quot;'>" +
            " and t.area_id = #{pageInputUserDTO.areaId}  " +
            " </if> " +
            " group by t.id_card "+
            "</script>")
    ComMngStructAreaVO selectByAreaName(String areaName);


    List<ComMngStructHouseVO> selectHouserByareaName(String areaName);


    List<ComMngFamilyInfoVO> selectListFamily(@Param("doorNumber") String doorNumber, @Param("userId") Long userId);


    IPage<InputUserInfoVO> pageInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);

    @Select("select a.id,a.community_id,a.area_name,address_detail from com_mng_struct_area a where a.community_id=#{communityId}")

    List<ComMngStructAreaVO> selectComMngStructArea(@Param("communityId") Long communityId);

    @Select("" +
            "SELECT " +
            " t1.house_code houseCode, " +
            " t1.house_name houseName, " +
            " t1.square, " +
            " t1.state " +
            "FROM " +
            " sys_user_input t  " +
            " LEFT JOIN com_mng_struct_house t1 ON t.house_code = t1.house_code " +
            " WHERE" +
            " t.id_card = #{idCard}")

    IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);


    List<ComMngStructHouseVO> selectUserHouseList(@Param("idCard") String idCard);

    @Select("<script> " +
            "SELECT " +
            " t.`name`, " +
            "CASE " +
            " IF " +
            "  ( " +
            "   length( t.id_card ) = 18, " +
            "   cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " +
            "  IF " +
            "   ( " +
            "    length( t.id_card ) = 15, " +
            "    cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " +
            "    3  " +
            "   )  " +
            "  )  " +
            "  WHEN 1 THEN " +
            "  1  " +
            "  WHEN 0 THEN " +
            "  2 ELSE 0  " +
            " END AS sex, " +
            " t.phone, " +
            " YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " +
            " t1.area_name, " +
            " t.political_outlook politicalOutlook, " +
            " CONCAT( " +
            " IF " +
            "  ( t.soldier = 1, '军人,', '' ), " +
            " IF " +
            "  ( t.lowIncome_households = 1, '低保户,', '' ), " +
            " IF " +
            "  ( t.lowIncome_people = 1, '低收入,', '' ), " +
            " IF " +
            "  ( t.old_people = 1, '特服家庭,', '' ), " +
            " IF " +
            "  ( t.key_personnel = 1, '重点人员,', '' )  " +
            " ) tags  " +
            "FROM " +
            " sys_user_input t " +
            " LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id  " +
            "WHERE " +
            " t.community_id = #{pageInputUserDTO.communityId}"+
            " AND " +
            "(t.soldier=1 OR t.lowIncome_households=1 OR t.lowIncome_people=1 or t.old_people=1 OR t.special_service_family=1 OR t.key_personnel=1 OR (t.tags IS NOT NULL AND TRIM(t.tags)!=''))"+
            "<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != &quot;&quot;'>" +
            " and t.`name` like concat(#{pageInputUserDTO.name},'%')  " +
            " </if> " +
            "<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != &quot;&quot;'>" +
            " and t.phone like concat(#{pageInputUserDTO.phone},'%')  " +
            " </if> " +
            "<if test='pageInputUserDTO.areaId != null and pageInputUserDTO.areaId.trim() != &quot;&quot;'>" +
            " and t.area_id = #{pageInputUserDTO.areaId}  " +
            " </if> " +
            "<if test='pageInputUserDTO.tags != null and pageInputUserDTO.tags.trim() != &quot;&quot;'>" +
            " and t.tags like concat('%',#{pageInputUserDTO.tags},'%')  " +
            " </if> " +
            " group by t.id_card "+
            "</script>")
    IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);

    List<EexcelUserDTO> selectExport(@Param("exportUserDTO") ExportUserDTO exportUserDTO);


    List<EexcelUserDTO> specialUserExport(@Param("exportSpecialUserDTO") ExportSpecialUserDTO exportSpecialUserDTO);
}