住户导出调整

zhongrj

2024-05-15 db85e305e50167b984004da6f105d19a2c10b5a3

住户导出调整

 src/main/java/org/springblade/modules/house/controller/HouseholdController.java

@@ -34,6 +34,7 @@
import org.springblade.core.tool.jackson.JsonUtil;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.modules.house.entity.HouseholdEntity;
import org.springblade.modules.house.excel.ExportHouseholdExcel;
import org.springblade.modules.house.excel.ImportHouseholdExcel;
import org.springblade.modules.house.excel.HouseHoldImporter;
import org.springblade.modules.house.service.IHouseholdService;
@@ -242,8 +243,8 @@
    @ApiOperationSupport(order = 13)
    @ApiOperation(value = "导出住户", notes = "传入user")
    public void exportUser(HouseholdVO household, HttpServletResponse response) {
        List<ImportHouseholdExcel> list = householdService.export(household);
        ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, ImportHouseholdExcel.class);
        List<ExportHouseholdExcel> list = householdService.export(household);
        ExcelUtil.export(response, "住户户数据" + DateUtil.time(), "住户数据表", list, ExportHouseholdExcel.class);
    }

    /**

 src/main/java/org/springblade/modules/house/excel/ExportHouseholdExcel.java

New file
@@ -0,0 +1,237 @@
package org.springblade.modules.house.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import org.springblade.common.excel.ExcelDictConverter;
import org.springblade.common.excel.ExcelDictItem;
import org.springblade.common.excel.ExcelDictItemLabel;

import java.io.Serializable;

/**
 * ExportHouseholdExcel
 * 导出住户信息
 * @author Chill
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class ExportHouseholdExcel implements Serializable {

    private static final long serialVersionUID = 2L;

//    /**
//     * 门牌地址编码
//     */
//    @ColumnWidth(25)
//    @ExcelProperty( "门牌地址编码")
//    private String houseCode;

    /** 姓名 */
    @ColumnWidth(15)
    @ExcelProperty( "姓名*")
    private String name;

    /** 性别 1: 男 0：女  2: 未知 */
    @ColumnWidth(15)
    @ExcelProperty( value = "性别",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "sex")
    @ExcelDictItem(type = "sex")
    private String gender;


    /** 手机号 */
    @ColumnWidth(15)
    @ExcelProperty( "手机号*")
    private String phoneNumber;

    /** 生日 */
    @ColumnWidth(15)
    @ExcelProperty( "生日")
    private String birthday;

    /** 身份证 */
    @ColumnWidth(15)
    @ExcelProperty( "身份证号码")
    private String idCard;

    /** 其他联系方式 */
    @ColumnWidth(15)
    @ExcelProperty( "其他联系方式")
    private String otherContact;

    /** 所属街道 */
    @ColumnWidth(15)
    @ExcelProperty( "所属街道")
    private String townName;

    /** 所属社区 */
    @ColumnWidth(15)
    @ExcelProperty( "所属社区")
    private String communityName;

    /** 所属网格 */
    @ColumnWidth(15)
    @ExcelProperty( "所属网格")
    private String gridName;

    /** 小区名称 */
    @ColumnWidth(15)
    @ExcelProperty( "小区名称")
    private String aoiName;

    /** 与业主关系 */
    @ColumnWidth(15)
    @ExcelProperty( value = "与业主关系*(业主/妻子/丈夫/女儿/儿子/母亲/父亲/.../租户/其他)",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "roleRelation")
    @ExcelDictItem(type = "roleRelation")
    private String relationship;

    /** 是否主要联系人 1：是  0：否 */
    @ColumnWidth(15)
    @ExcelProperty( value = "是否主要联系人",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "primaryContactType")
    @ExcelDictItem(type = "primaryContactType")
    private String isPrimaryContact;

    /** 居住状态 1: 是  0：否 */
    @ColumnWidth(15)
    @ExcelProperty( value = "居住状态",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "residentialStatusType")
    @ExcelDictItem(type = "residentialStatusType")
    private String residentialStatus;

    /** 民族 */
    @ColumnWidth(15)
    @ExcelProperty( value = "民族",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "nationType")
    @ExcelDictItem(type = "nationType")
    private String ethnicity;


    /** 户籍地址 */
    @ColumnWidth(15)
    @ExcelProperty( "户籍地址")
    private String hukouRegistration;

    /** 现居住地址 */
    @ColumnWidth(15)
    @ExcelProperty( "现居住地址")
    private String currentAddress;

    /** 是否党员  1：党员  2：群众 */
    @ColumnWidth(15)
    @ExcelProperty( value = "是否党员（党员/群众）",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "partyEmberType")
    @ExcelDictItem(type = "partyEmberType")
    private String partyEmber;

    /** 学历 */
    @ColumnWidth(15)
    @ExcelProperty( value = "学历",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "educationType")
    @ExcelDictItem(type = "educationType")
    private String education;
    /**
     * 职业类别
     */
    @ColumnWidth(15)
    @ExcelProperty( "职业类别")
    private String occupation;

    /** 工作单位 */
    @ColumnWidth(15)
    @ExcelProperty( "工作单位")
    private String employer;
    /**
     * 工作单位地址
     */
    @ExcelProperty( "工作单位地址")
    private String cmpyRegAddr;

    /** 工作状态 */
    @ColumnWidth(15)
    @ExcelProperty( value = "工作状态（在职/离职/退休/其他）",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "workStatusType")
    @ExcelDictItem(type = "workStatusType")
    private String workStatus;


    /** 婚姻状态 */
    @ColumnWidth(15)
    @ExcelProperty( value = "婚姻状态（未婚/已婚/离异/丧偶/其他）",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "marriageStatusType")
    @ExcelDictItem(type = "marriageStatusType")
    private String maritalStatus;
    /**
     * 宗教信仰
     */
    @ColumnWidth(15)
    @ExcelProperty( "宗教信仰")
    private String religiousBelief;
    /**
     * 健康状况    业务字典 healthStatus
     */
    @ColumnWidth(15)
    @ExcelProperty( value = "健康状况(健康/良好/较差)",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "healthStatus")
    @ExcelDictItem(type = "healthStatus")
    private String healthStatus;
    /**
     * 疾病名称
     */
    @ColumnWidth(15)
    @ExcelProperty( "疾病名称")
    private String diseaseName;
    /**
     * 外出去向
     */
    @ColumnWidth(15)
    @ExcelProperty( "外出去向")
    private String goOutWhere;
    /**
     * 外出原因
     */
    @ColumnWidth(15)
    @ExcelProperty( "外出原因")
    private String goOutReason;
    /**
     * 外出时间
     */
    @ColumnWidth(15)
    @ExcelProperty( "外出时间")
    private String goOutTime;

    /**
     * 外出详址
     */
    @ExcelProperty( "外出详址")
    private String goOutAddr;

    /** 车牌号 */
    @ColumnWidth(15)
    @ExcelProperty( "车牌号")
    private String cardNumber;
    /**
     * 自愿者组织
     */
    @ColumnWidth(15)
    @ExcelProperty( "自愿者组织")
    private String volunteerOrg;

    /** 备注 */
    @ColumnWidth(15)
    @ExcelProperty( "备注")
    private String remark;

    /** 备注 */
    @ColumnWidth(15)
    @ExcelProperty( "标签")
    private String labelName;

}


 src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java

@@ -23,6 +23,7 @@
import org.springblade.common.node.TreeIntegerNode;
import org.springblade.common.node.TreeStringNode;
import org.springblade.modules.house.entity.HouseholdEntity;
import org.springblade.modules.house.excel.ExportHouseholdExcel;
import org.springblade.modules.house.excel.ImportHouseholdExcel;
import org.springblade.modules.house.vo.HouseholdOtherVO;
import org.springblade.modules.house.vo.HouseholdVO;
@@ -85,12 +86,15 @@
    HouseholdVO getHouseholdListById(@Param("household") HouseholdEntity household);

    /**
     * 导出
     * 导出住户信息
     *
     * @param household
     * @return
     */
    List<ImportHouseholdExcel> export(@Param("household") HouseholdVO household);
    List<ExportHouseholdExcel> export(@Param("household") HouseholdVO household,
                                      @Param("isAdministrator") Integer isAdministrator,
                                      @Param("regionChildCodesList") List<String> regionChildCodesList,
                                      @Param("gridCodeList") List<String> gridCodeList);

    Integer statistics(@Param("household") HouseholdVO household,
                       @Param("isAdministrator") Integer isAdministrator,

 src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml

@@ -557,23 +557,121 @@
    </select>

    <!--导出数据-->
    <select id="export" resultType="org.springblade.modules.house.excel.ImportHouseholdExcel">
        select
        jh.house_code houseCode,
        jh.name,jh.phone_number phoneNumber,jh.role_type roleType,jh.relationship relationship,
        jh.is_primary_contact isPrimaryContact,jh.residential_status residentialStatus,jh.gender,
        jh.birthday,jh.id_card idCard,jh.ethnicity,jh.education,
        jh.hukou_registration hukouRegistration,jh.work_status workStatus,jh.employer,jh.marital_status maritalStatus,
        jh.card_number cardNumber,jh.other_contact otherContact,jh.current_address currentAddress,jh.volunteer_org,
        jh.disability_cert disabilityCert,jh.party_ember partyEmber,jh.remark,
    <select id="export" resultType="org.springblade.modules.house.excel.ExportHouseholdExcel">
        SELECT
        jh.id,
        jh.house_code,
        jh.NAME,
        jh.phone_number,
        jh.relationship,
        jh.is_primary_contact,
        jh.residential_status,
        jh.birthday,
        jh.id_card,
        jh.card_type,
        ifnull( jh.gender, CASE WHEN substring( jh.id_card, 17, 1 )% 2 = 1 THEN 1 ELSE 0 END ) AS gender,
        jh.ethnicity,
        jh.education,
        jh.resident_type,
        jh.hukou_registration,
        jh.resident_adcode,
        jh.native_place_adcode,
        jh.religious_belief,
        jh.health_status,
        jh.disease_name,
        jh.work_status,
        jh.employer,
        jh.occupation,
        jh.cmpy_reg_addr,
        jh.go_out_addr,
        jh.go_out_where,
        jh.go_out_time,
        jh.go_out_reason,
        jh.marital_status,
        jh.card_number,
        jh.other_contact,
        IF
        ( jda.id IS NOT NULL, jda.address_name, jh.current_address ) AS current_address,
        jh.disability_cert,
        jh.party_ember,
        jh.remark,
        jhs.district_name aoiName,
        concat(jhs.building," ",unit," ",room) as address
        from
        jczz_household jh join jczz_house jhs on jh.house_code = jhs.house_code and jhs.is_deleted = 0
        where jh.is_deleted = 0
        jda.town_street_name AS townName,
        jda.nei_name AS communityName,
        jg.grid_name,
        jhs.building,
        jh.volunteer_org,
        case
        when TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) &lt; 18 then 2
        when TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) &gt;= 18 then 1
        end as minors,
        jhs.unit,
        juhl.label_name
        FROM
        jczz_household jh
        LEFT JOIN jczz_house jhs ON jh.house_code = jhs.house_code and jhs.is_deleted = 0
        LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jh.house_code
        LEFT JOIN jczz_grid jg on jg.grid_code = jhs.grid_code and jg.is_deleted = 0
        LEFT JOIN jczz_police_affairs_grid jpag on jhs.jw_grid_code= jpag.jw_grid_code and jpag.is_deleted = 0
        LEFT JOIN (
            select household_id,GROUP_CONCAT(label_name) as label_name from jczz_user_house_label where lable_type = 1 and household_id is not null GROUP BY household_id
        ) juhl on juhl.household_id = jh.id
        <where>
            <if test="household.labelIdList !=null and household.labelIdList.size() >0 ">
                EXISTS (
                SELECT
                *
                FROM
                jczz_user_house_label juhl
                LEFT JOIN jczz_label jl ON juhl.label_id = jl.id
                where  juhl.household_id = jh.id
                and juhl.lable_type = 1
                AND juhl.household_id IS NOT NULL
                AND jl.id in
                <foreach collection="household.labelIdList" item="code" open="(" close=")" separator=",">
                    #{code}
                </foreach>
                )
            </if>
            <if test="household.userId!=null">
                AND jg.grid_code IN ( SELECT DISTINCT jgm.grid_code FROM jczz_gridman jgm WHERE jgm.user_id =
                #{household.userId}
                )
            </if>

            <if test="household.searchKey!=null and household.searchKey!=''">
                and CONCAT(jh.name,jh.phone_number) like CONCAT ('%', #{household.searchKey},'%')
            </if>

        <if test="household.name!=null and household.name !=''">
            and jh.name like concat('%',#{household.name},'%')
        </if>
            <if test="household.volunteerOrg != null  and household.volunteerOrg != ''">
                and jh.volunteer_org  like concat('%',#{household.volunteerOrg},'%')
            </if>
            <if test="household.houseCode!=null and household.houseCode !=''">
                and jh.house_code = #{household.houseCode}
            </if>

            <if test="household.communityCode!=null and household.communityCode !=''">
                and jg.community_code = #{household.communityCode}
            </if>
            <!-- 等于18 就是租户 -->
            <if test="household.relationship!=null and household.relationship == 18">
                and jh.relationship = #{household.relationship}

                <if test="household.minors!=null and household.minors ==2">
                    and TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) &lt; 18
                </if>
                <if test="household.minors!=null and household.minors ==1">
                    and TIMESTAMPDIFF(year, substring(jh.id_card, 7, 8), now()) &gt;= 18
                </if>
            </if>
            <!-- 不等于18 就是非租户 -->
            <if test="household.relationship!=null and household.relationship != 18">
                and jh.relationship != #{household.relationship}
            </if>

        <if test="household.phoneNumber!=null and household.phoneNumber !=''">
            and jh.phone_number like concat('%',#{household.phoneNumber},'%')
        </if>
@@ -583,6 +681,88 @@
        <if test="household.aoiName!=null and household.aoiName !=''">
            and jhs.district_name like concat('%',#{household.aoiName},'%')
        </if>
            <if test="household.confirmFlag != null ">
                and jh.confirm_flag = #{household.confirmFlag}
            </if>
            <if test="household.townStreetName!=null and household.townStreetName!=''">
                and jda.town_street_name like concat('%',#{household.townStreetName},'%')
            </if>
            <if test="household.neiName!=null and household.neiName!=''">
                and jda.nei_name like concat('%',#{household.neiName},'%')
            </if>
            <if test="household.housingRentalId != null ">
                and jh.housing_rental_id = #{household.housingRentalId}
            </if>
            <if test="household.startTime != null and household.startTime != '' and household.endTime != null and household.endTime != '' ">
                AND jh.create_time BETWEEN #{household.startTime} and #{household.endTime}
            </if>
            <if test="isAdministrator==2">
                <choose>
                    <when test="household.roleName != null and household.roleName != ''">
                        <if test="household.roleName=='wgy'">
                            <choose>
                                <when test="gridCodeList !=null and gridCodeList.size()>0">
                                    and jhs.grid_code in
                                    <foreach collection="gridCodeList" item="code" open="(" close=")" separator=",">
                                        #{code}
                                    </foreach>
                                </when>
                                <otherwise>
                                    and jhs.grid_code in ('')
                                </otherwise>
                            </choose>
                        </if>
                        <if test="household.roleName=='mj'">
                            <choose>
                                <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
                                    and jpag.community_code in
                                    <foreach collection="regionChildCodesList" item="code" open="(" close=")"
                                             separator=",">
                                        #{code}
                                    </foreach>
                                </when>
                                <otherwise>
                                    and jpag.community_code in ('')
                                </otherwise>
                            </choose>
                        </if>
                    </when>
                    <otherwise>
                        <choose>
                            <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
                                and
                                (
                                jg.grid_code in
                                <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
                                    #{code}
                                </foreach>
                                or
                                jpag.community_code in
                                <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
                                    #{code}
                                </foreach>
                                )
                            </when>
                            <otherwise>

                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
            <if test="household.building!=null and household.building!=''">
                and jhs.building like concat(#{household.building},'%')
            </if>
            <if test="household.unit!=null and household.unit!=''">
                and jhs.unit like concat(#{household.unit},'%')
            </if>
            <if test="household.aoiCode!=null and household.aoiCode!=''">
                and jhs.district_code = #{household.aoiCode}
            </if>
            and jh.is_deleted = 0
        </where>
        order by jh.create_time desc
        limit 0,10000
    </select>



 src/main/java/org/springblade/modules/house/service/IHouseholdService.java

@@ -21,6 +21,7 @@
import org.springblade.common.node.TreeIntegerNode;
import org.springblade.common.node.TreeStringNode;
import org.springblade.modules.house.entity.HouseholdEntity;
import org.springblade.modules.house.excel.ExportHouseholdExcel;
import org.springblade.modules.house.excel.ImportHouseholdExcel;
import org.springblade.modules.house.vo.HouseholdVO;

@@ -71,7 +72,12 @@
     */
    Object getDetail(HouseholdEntity household);

    List<ImportHouseholdExcel> export(HouseholdVO household);
    /**
     * 导出住户信息
     * @param household
     * @return
     */
    List<ExportHouseholdExcel> export(HouseholdVO household);

    String importUserHouseHold(List<ImportHouseholdExcel> data, Boolean isCovered);


 src/main/java/org/springblade/modules/house/service/impl/HouseholdServiceImpl.java

@@ -35,6 +35,7 @@
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.house.entity.HouseholdEntity;
import org.springblade.modules.house.entity.UserHouseLabelEntity;
import org.springblade.modules.house.excel.ExportHouseholdExcel;
import org.springblade.modules.house.excel.ImportHouseholdExcel;
import org.springblade.modules.house.mapper.HouseholdMapper;
import org.springblade.modules.house.service.IHouseService;
@@ -300,9 +301,23 @@
        return baseMapper.getHouseholdListById(household);
    }

    /**
     * 导出住户信息
     * @param household
     * @return
     */
    @Override
    public List<ImportHouseholdExcel> export(HouseholdVO household) {
        List<ImportHouseholdExcel> userHouseHoldExcels = baseMapper.export(household);
    public List<ExportHouseholdExcel> export(HouseholdVO household) {
        CommonParamSet commonParamSet = new CommonParamSet().invoke(HouseholdVO.class, household);
        // 重点人员与关注人员
        if (Objects.nonNull(household.getLabelType())) {
            extracted(household);
        }
        List<ExportHouseholdExcel> userHouseHoldExcels = baseMapper.export(household,
            commonParamSet.getIsAdministrator(),
            commonParamSet.getRegionChildCodesList(),
            commonParamSet.getGridCodeList()
        );
        return userHouseHoldExcels;
    }