采购导入需求

chengf

2026-03-27 1e20f9b6c5bc642f92f77388c5f339f05a3ae9be

 service-api/src/main/java/com/java110/api/importData/adapt/ImportPropertyWhiteListfFlowAdapt.java

@@ -6,24 +6,28 @@
import com.java110.dto.PropertyWhiteListFlowDto;
import com.java110.dto.community.CommunityDto;
import com.java110.dto.importData.ImportRoomFee;
import com.java110.dto.importData.Vtil;
import com.java110.dto.system.ComponentValidateResult;
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.intf.fee.IPayFeeBatchV1InnerServiceSMO;
import com.java110.intf.user.IUserInnerServiceSMO;
import com.java110.po.meter.MeterWaterPo;
import com.java110.utils.util.Assert;
import com.java110.utils.util.DateUtil;
import com.java110.utils.util.ImportExcelUtils;
import com.java110.utils.util.StringUtil;
import com.java110.utils.util.*;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

@Service("importPropertyWhiteListFlowDataCleaning")
public class ImportPropertyWhiteListfFlowAdapt extends DefaultImportDataAdapt implements IImportDataCleaningAdapt {
@@ -47,25 +51,34 @@
        //获取车辆信息
        getPropertyWhiteListFlowDtos(workbook, whiteLists);

        for (PropertyWhiteListFlowDto whiteList : whiteLists){
            whiteList.setCommunityId(paramIn.getString("communityId"));
        }
        return whiteLists;
    }


    public String getCommunityId(Object[] os) {
        CommunityDto d = new CommunityDto();
        d.setCommunityCode(defaultValue(os[1]));
        d.setName(defaultValue(os[2]));
        List<CommunityDto> communityDtos = communityInnerServiceSMOImpl.queryCommunitys(d);
        if (communityDtos.isEmpty()) {
            throw new IllegalArgumentException("未查询到小区：" + d.getCommunityCode() + d.getName());

    public void setCommunityId(PropertyWhiteListFlowDto po, Object[] os, List<Map<String, Object>> maps) {
        for (Map<String, Object> map : maps) {
            if (Vtil.defaultValue(os[1]) == null && Vtil.defaultValue(os[2]) == null){
                throw new IllegalArgumentException("小区名称和小区编号均为空");
            }
            if (Vtil.defaultValue(os[1]) == null) {
                if (Vtil.defaultValue(map.get("name")).contains(Vtil.defaultValue(os[2]))) {
                    po.setCommunityId(map.get("communityId").toString());
                    return;
                }
            } else if (Vtil.defaultValue(os[2]) == null){
                if (Vtil.defaultValue(map.get("communityCode")).equals(Vtil.defaultValue(os[1]))) {
                    po.setCommunityId(map.get("communityId").toString());
                    return;
                }
            } else {
                if (Vtil.defaultValue(map.get("communityCode")).equals(Vtil.defaultValue(os[1])) && Vtil.defaultValue(map.get("name")).contains(Vtil.defaultValue(os[2]))) {
                    po.setCommunityId(map.get("communityId").toString());
                    return;
                }
            }
        }
        else if (communityDtos.size() > 1) {
            throw new IllegalArgumentException("查询小区异常:存在多个符合条件的小区：" + d.getCommunityCode() + d.getName());
        }
        return communityDtos.get(0).getCommunityId();
        throw new IllegalArgumentException("未查询到小区：" + Vtil.appendHyphenToRight(Vtil.defaultValue(os[1])) + Vtil.defaultValue(os[2]));
    }


@@ -75,15 +88,22 @@

    private void getPropertyWhiteListFlowDtos(Workbook workbook, List<PropertyWhiteListFlowDto> whiteLists) {
        Sheet sheet = null;
        sheet = ImportExcelUtils.getSheet(workbook, "白单流水  导入新模板");
        List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
        List<Object[]> oList;
        try {
            sheet = ImportExcelUtils.getSheet(workbook, "白单流水  导入新模板");
            oList = ImportExcelUtils.listFromSheet(sheet);
        } catch (IllegalArgumentException e) {
            sheet = ImportExcelUtils.getSheet(workbook, "Sheet1");
            oList = ImportExcelUtils.listFromSheet(sheet);
        }
        PropertyWhiteListFlowDto white = null;
        List<Map<String, Object>> maps = BeanConvertUtil.beanCovertMapListC(communityInnerServiceSMOImpl.queryCommunitys(new CommunityDto()));
        for (int osIndex = 0; osIndex < oList.size(); osIndex++) {
            Object[] os = oList.get(osIndex);
            if (osIndex == 1) { // 第一行是 头部信息 直接跳过
            if (osIndex == 0) { // 第一行是 头部信息 直接跳过
                continue;
            }
            if (StringUtil.isNullOrNone(os[3])) {
            if (os == null || os.length < 4 || StringUtil.isNullOrNone(os[3])) {
                continue;
            }
            Assert.hasValue(os[3], (osIndex + 1) + "行收费日期不能为空");
@@ -91,22 +111,19 @@

            white = new PropertyWhiteListFlowDto();
            String chargeTime = excelDoubleToDate(os[3].toString());
            String changeStart = defaultValueToDate(os[11]);
            String changeEnd = defaultValueToDate(os[12]);
            String bankDepositDate =  defaultValueToDate(os[16]);
            String changeStart = Vtil.defaultValueToDate(os[11],"start");
            String changeEnd = Vtil.defaultValueToDate(os[12],"end");
            String bankDepositDate =  Vtil.defaultValueToDate(os[16]);
            String inputTime = null;
            if(os[24] != null){
                inputTime = defaultValueToDate(os[24]);
                inputTime = Vtil.defaultValueToDate(os[24]);
            }
            white.setChargeTime(chargeTime);//收费时间
            white.setInvoiceReceiptNo(defaultValueToDate(os[4]));//发票
            white.setInvoiceReceiptNo(Vtil.defaultValue(os[4]));//发票
            white.setRoomId(os[5] == null ? null : os[5].toString());//房号
            white.setPropertyAddress(os[5] == null ? null : os[5].toString());//业主地址
            white.setDoorRoomNum((os[6] == null ? "" : os[6].toString() + "-") + (os[7] == null ? "" : os[7].toString() + "-") + (os[8] == null ? "" : os[8].toString()) );//门市号
            if (white.getDoorRoomNum().isEmpty()){
                white.setDoorRoomNum(null);
            }
            white.setCommunityId(getCommunityId(os));
            setCommunityId(white, os, maps);
            white.setSecondaryFeeTypeCd(os[13] == null ? null : os[13].toString());//收费内容
            white.setChargeAmount(os[14] == null ? null : os[14].toString());//收费金额
            white.setCharger(os[15] == null ? null : os[15].toString());//收费人
@@ -125,7 +142,7 @@
            white.setCategory22(os[31] == null ? null : os[31].toString());//25项收费分类
            white.setRoomNumber(os[9] == null ? null : os[9].toString());
            white.setReceiptYear(os[27] == null ? null : os[27].toString());
            white.setReceiptYearMonth(defaultValueToDate(os[26]));
            white.setReceiptYearMonth(Vtil.defaultValueToDate(os[26]));
            white.setPendingProblem(os[30] == null ? null : os[30].toString());
//            if (os.length > 23){
//                white.setFrequency(os[23] == null ? null : os[23].toString());
@@ -209,31 +226,4 @@
//            whiteLists.add(white);
//        }
//    }

    private String defaultValueToDate(Object o) {
        // 空值直接返回null（原有逻辑）
        if (o == null) {
            return null;
        }

        try {
            // 尝试将对象转换为数字（兼容Excel日期序列号，如44747）
            double excelDateNum = Double.parseDouble(o.toString());

            // Excel 1900日期系统基准（修正闰日bug，实际基准是1899-12-30）
            Calendar calendar = Calendar.getInstance();
            calendar.set(1899, 11, 30, 0, 0, 0); // 月份从0开始，11代表12月
            calendar.set(Calendar.MILLISECOND, 0);

            // 计算对应的实际日期
            calendar.add(Calendar.DAY_OF_MONTH, (int) excelDateNum);

            // 格式化为yyyy-MM-dd的日期字符串
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            return sdf.format(calendar.getTime());
        } catch (NumberFormatException e) {
            // 非数字类型，执行原有逻辑返回toString()
            return o.toString();
        }
    }
}