房屋数据导出字典翻译修改

zhongrj

2023-11-17 17c81b57ec33fc692ed1499cda86bb01f1fe65a0

房屋数据导出字典翻译修改

 src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java

New file
@@ -0,0 +1,49 @@
package org.springblade.common.excel;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

/**
 * 自适应行高
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        Integer maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    if (cell.getStringCellValue().indexOf("\n") != -1) {
                        int length = cell.getStringCellValue().split("\n").length;
                        maxHeight = Math.max(maxHeight, length);
                    }
                    break;
                default:
                    break;
            }
        }

        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }
}

 src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java

New file
@@ -0,0 +1,83 @@
package org.springblade.common.excel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 自适应行款
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 254) {
                    columnWidth = 254;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                            cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

 src/main/java/org/springblade/common/excel/ExcelDictConverter.java

New file
@@ -0,0 +1,65 @@
package org.springblade.common.excel;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.springblade.common.utils.SpringUtils;
import org.springblade.modules.system.entity.DictBiz;
import org.springblade.modules.system.service.IDictBizService;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.List;

/**
 * 字典映射
 *
 * @author zhongrj
 * @date 2023-11-17
 */
@Component
public class ExcelDictConverter implements Converter<String> {

    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    /**
     * java 导出到 excel
     * @param dictValue
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public CellData convertToExcelData(String dictValue, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取字典类型
        Field field = excelContentProperty.getField();
        ExcelDictItem excel = field.getAnnotation(ExcelDictItem.class);
        String dictType = excel.type();
        IDictBizService dictBizService = SpringUtils.getBean(IDictBizService.class);
        List<DictBiz> list = dictBizService.getList(dictType);
        String value = "";
        //解析返回
        for (DictBiz dictBiz : list) {
            if (dictBiz.getDictKey().equals(dictValue)){
                value = dictBiz.getDictValue();
            }
        }
        return new CellData(value);
    }
}

 src/main/java/org/springblade/common/excel/ExcelDictItem.java

New file
@@ -0,0 +1,24 @@
package org.springblade.common.excel;

import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * excel导出字典转换注解
 * <p>
 * 将excel导出的字典code自动转换为字典label
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@JacksonAnnotationsInside
public @interface ExcelDictItem {

    /**
     * 字典type
     */
    String type();

}

 src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java

New file
@@ -0,0 +1,24 @@
package org.springblade.common.excel;

import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * excel导入字典转换注解
 * <p>
 * 将excel导入的字典label自动转换为字典code
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@JacksonAnnotationsInside
public @interface ExcelDictItemLabel {

    /**
     * 字典type
     */
    String type();

}

 src/main/java/org/springblade/common/excel/MySheetWriteHandler.java

New file
@@ -0,0 +1,45 @@
package org.springblade.common.excel;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class MySheetWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 500);
        Cell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("附件2");
        //设置标题
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 800);
        Cell cell1 = row2.createCell(0);
        cell1.setCellValue("存量建筑垃圾堆体治理进度月报表");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 17));
        //设置填表日期,填报人,联系方式
        Row row3 = sheet.createRow(2);
        row3.setHeight((short) 500);
        row3.createCell(1).setCellValue("填表日期");
        row3.createCell(11).setCellValue("填表人");
        row3.createCell(15).setCellValue("联系方式");
    }
}

 src/main/java/org/springblade/common/excel/RowWriteHandler.java

New file
@@ -0,0 +1,59 @@
package org.springblade.common.excel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.springblade.core.tool.utils.Func;

import java.util.List;

/**
 * 修改单元格格式为文本格式
 * @author zhongrj
 * @since 2021-9-26
 */
public class RowWriteHandler  implements CellWriteHandler {

    public static CellStyle cellStyle;

    /**
     * 设置全局变量，防止  cellStyle 创建过多报错 2021-12-16 zrj
     * The maximum number of cell styles was exceeded. You can define up to 64000styles in a .xlsx workbook
     * @param cellStyle
     */
    public static void setCellStyle(CellStyle cellStyle) {
        RowWriteHandler.cellStyle = cellStyle;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //设置单元格格式为文本
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        //自定义样式不为空时,重复利用
        if(Func.isEmpty(cellStyle)){
            cellStyle = workbook.createCellStyle();
            setCellStyle(cellStyle);
        }
        DataFormat dataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        cell.setCellStyle(cellStyle);
    }

    //@Override 加上会报错
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }
}

 src/main/java/org/springblade/common/utils/SpringUtils.java

New file
@@ -0,0 +1,49 @@
package org.springblade.common.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * spring 工具类
 * @author zhongrj
 */
@Component
public class SpringUtils implements ApplicationContextAware {


    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext)throws BeansException {
        if (SpringUtils.applicationContext == null) {
            SpringUtils.applicationContext = applicationContext;
        }
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }


    public static Object getBean(String name) {
        return getApplicationContext().getBean(name);
    }


    public static <T> T getBean(Class<T> clazz) {
        return getApplicationContext().getBean(clazz);
    }

    public static <T> T getBean(String name, Class<T> clazz) {
        return getApplicationContext().getBean(name, clazz);
    }

    /**
     * 获取当前环境
     */
    public static String getActiveProfile() {
        return applicationContext.getEnvironment().getActiveProfiles()[0];
    }
}

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

@@ -166,7 +166,7 @@
    /**
     * 导入用户
     */
    @PostMapping("import-houseHold")
    @PostMapping("import-household")
    @ApiOperationSupport(order = 12)
    @ApiOperation(value = "导入住户", notes = "传入excel")
    public R importUser(MultipartFile file, Integer isCovered) {
@@ -178,7 +178,7 @@
    /**
     * 导出用户
     */
    @GetMapping("export-houseHold")
    @GetMapping("export-household")
    @ApiOperationSupport(order = 13)
    @ApiOperation(value = "导出住户", notes = "传入user")
    public void exportUser(HouseholdVO household, HttpServletResponse response) {

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

@@ -1,12 +1,14 @@
package org.springblade.modules.house.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
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 com.fasterxml.jackson.annotation.JsonFormat;
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;
import java.util.Date;
@@ -24,17 +26,6 @@

    private static final long serialVersionUID = 2L;


    /** 主键id */
    @ColumnWidth(15)
    @ExcelProperty( "主键ID")
    private Long id;

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

    /** 姓名 */
    @ColumnWidth(15)
    @ExcelProperty( "姓名")
@@ -45,40 +36,40 @@
    @ExcelProperty( "手机号")
    private String phoneNumber;

    /** 绑定用户ID */
    /** 小区名称 */
    @ColumnWidth(15)
    @ExcelProperty( "绑定用户ID")
    private Long associatedUserId;
    @ExcelProperty( "小区名称")
    private String aoiName;

    /** 角色  */
    @ColumnWidth(15)
    @ExcelProperty( "角色 ")
    private Integer roleType;

    /** 绑定用户名称 */
    @ColumnWidth(15)
    @ExcelProperty( "绑定用户名称")
    private String associatedUserName;
    @ExcelProperty( value = "角色",converter = ExcelDictConverter.class)
    @ExcelDictItem(type = "roleType")
    private String roleType;

    /** 与角色关系（业主，父子，其他） */
    @ColumnWidth(15)
    @ExcelProperty( "与角色关系（业主，父子，其他）")
    private Integer relationship;
    @ExcelProperty( value = "与角色关系",converter = ExcelDictConverter.class)
    @ExcelDictItem(type = "roleRelation")
    private String relationship;

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

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

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

    /** 生日 */
    @ColumnWidth(15)
@@ -103,13 +94,15 @@

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

    /** 学历 */
    @ColumnWidth(15)
    @ExcelProperty( "学历")
    private Integer education;
    @ExcelProperty( value = "学历",converter = ExcelDictConverter.class)
    @ExcelDictItem(type = "educationType")
    private String education;

    /** 户籍登记地 */
    @ColumnWidth(15)
@@ -118,8 +111,9 @@

    /** 工作状态 */
    @ColumnWidth(15)
    @ExcelProperty( "工作状态")
    private Integer workStatus;
    @ExcelProperty( value = "工作状态",converter = ExcelDictConverter.class)
    @ExcelDictItem(type = "workStatusType")
    private String workStatus;

    /** 工作单位 */
    @ColumnWidth(15)
@@ -128,8 +122,9 @@

    /** 婚姻状态 */
    @ColumnWidth(15)
    @ExcelProperty( "婚姻状态")
    private Integer maritalStatus;
    @ExcelProperty( value = "婚姻状态",converter = ExcelDictConverter.class)
    @ExcelDictItem(type = "marriageStatusType")
    private String maritalStatus;

    /** 车牌号 */
    @ColumnWidth(15)
@@ -153,31 +148,9 @@

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

    /** 创建人 */
    @ColumnWidth(15)
    @ExcelProperty( "创建人")
    private Long createUser;

    /** 创建时间 */
    @ColumnWidth(15)
    @ExcelProperty( "创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    /** 更新人 */
    @ColumnWidth(15)
    @ExcelIgnore
    @ExcelProperty( "更新人")
    private Long updateUser;

    /** 更新时间 */
    @ExcelProperty( "更新时间")
    @ColumnWidth(15)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;

    /** 备注 */
    @ColumnWidth(15)

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

@@ -65,6 +65,11 @@
     */
    HouseholdVO getHouseholdListById(@Param("household") HouseholdEntity household);

    /**
     * 导出
     * @param household
     * @return
     */
    List<HouseHoldExcel> export(HouseholdVO household);

    Integer statistics(Long userId);

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

@@ -150,42 +150,32 @@
        and jh.id = #{household.id}
    </select>

    <!--导出数据-->
    <select id="export" resultType="org.springblade.modules.house.excel.HouseHoldExcel">
        <include refid="selectHousehold"/>
        <where>
            <if test="id != null "> and id = #{id}</if>
            <if test="houseCode != null  and houseCode != ''"> and house_code = #{houseCode}</if>
            <if test="name != null  and name != ''"> and name = #{name}</if>
            <if test="phoneNumber != null  and phoneNumber != ''"> and phone_number = #{phoneNumber}</if>
            <if test="associatedUserId != null "> and associated_user_id = #{associatedUserId}</if>
            <if test="roleType != null "> and role_type = #{roleType}</if>
            <if test="associatedUserName != null  and associatedUserName != ''"> and associated_user_name = #{associatedUserName}</if>
            <if test="relationship != null "> and relationship = #{relationship}</if>
            <if test="isPrimaryContact != null "> and is_primary_contact = #{isPrimaryContact}</if>
            <if test="residentialStatus != null "> and residential_status = #{residentialStatus}</if>
            <if test="gender != null "> and gender = #{gender}</if>
            <if test="birthday != null "> and birthday = #{birthday}</if>
            <if test="idCard != null  and idCard != ''"> and id_card = #{idCard}</if>
            <if test="hkmtPass != null  and hkmtPass != ''"> and hkmt_pass = #{hkmtPass}</if>
            <if test="passport != null  and passport != ''"> and passport = #{passport}</if>
            <if test="ethnicity != null "> and ethnicity = #{ethnicity}</if>
            <if test="education != null "> and education = #{education}</if>
            <if test="hukouRegistration != null  and hukouRegistration != ''"> and hukou_registration = #{hukouRegistration}</if>
            <if test="workStatus != null "> and work_status = #{workStatus}</if>
            <if test="employer != null  and employer != ''"> and employer = #{employer}</if>
            <if test="maritalStatus != null "> and marital_status = #{maritalStatus}</if>
            <if test="cardNumber != null  and cardNumber != ''"> and card_number = #{cardNumber}</if>
            <if test="otherContact != null  and otherContact != ''"> and other_contact = #{otherContact}</if>
            <if test="currentAddress != null  and currentAddress != ''"> and current_address = #{currentAddress}</if>
            <if test="disabilityCert != null  and disabilityCert != ''"> and disability_cert = #{disabilityCert}</if>
            <if test="partyEmber != null "> and party_ember = #{partyEmber}</if>
            <if test="createUser != null "> and create_user = #{createUser}</if>
            <if test="createTime != null "> and create_time = #{createTime}</if>
            <if test="updateUser != null "> and update_user = #{updateUser}</if>
            <if test="updateTime != null "> and update_time = #{updateTime}</if>
            <if test="remark != null  and remark != ''"> and remark = #{remark}</if>
            <if test="isDeleted != null "> and is_deleted = #{isDeleted}</if>
        </where>
        select
        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.hkmt_pass hkmtPass,jh.passport,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.disability_cert disabilityCert,jh.party_ember partyEmber,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
        <if test="household.name!=null and household.name !=''">
            and jh.name like concat('%',#{household.name},'%')
        </if>
        <if test="household.phoneNumber!=null and household.phoneNumber !=''">
            and jh.phone_number like concat('%',#{household.phoneNumber},'%')
        </if>
        <if test="household.idCard!=null and household.idCard !=''">
            and jh.id_card like concat('%',#{household.idCard},'%')
        </if>
        <if test="household.aoiName!=null and household.aoiName !=''">
            and jhs.district_name like concat('%',#{household.aoiName},'%')
        </if>
    </select>