package com.java110.api.importData.adapt;
|
|
import com.alibaba.fastjson.JSONObject;
|
import com.java110.api.importData.DefaultImportDataAdapt;
|
import com.java110.api.importData.IImportDataCleaningAdapt;
|
import com.java110.dto.PropertyWhiteListFlowDto;
|
import com.java110.dto.community.CommunityDto;
|
import com.java110.dto.importData.ImportRoomFee;
|
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 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.util.ArrayList;
|
import java.util.Calendar;
|
import java.util.List;
|
|
@Service("importPropertyWhiteListFlowDataCleaning")
|
public class ImportPropertyWhiteListfFlowAdapt extends DefaultImportDataAdapt implements IImportDataCleaningAdapt {
|
|
|
@Autowired
|
private IPayFeeBatchV1InnerServiceSMO payFeeBatchV1InnerServiceSMOImpl;
|
|
@Autowired
|
private IUserInnerServiceSMO userInnerServiceSMOImpl;
|
|
@Autowired
|
private ICommunityInnerServiceSMO communityInnerServiceSMOImpl;
|
|
|
@Override
|
public List analysisExcel(Workbook workbook, JSONObject paramIn, ComponentValidateResult result) throws Exception {
|
|
generatorBatch(paramIn);
|
List<PropertyWhiteListFlowDto> whiteLists = new ArrayList<PropertyWhiteListFlowDto>();
|
//获取车辆信息
|
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());
|
}
|
else if (communityDtos.size() > 1) {
|
throw new IllegalArgumentException("查询小区异常:存在多个符合条件的小区：" + d.getCommunityCode() + d.getName());
|
}
|
return communityDtos.get(0).getCommunityId();
|
}
|
|
|
public String defaultValue(Object o) {
|
return o == null ? "" : o.toString();
|
}
|
|
private void getPropertyWhiteListFlowDtos(Workbook workbook, List<PropertyWhiteListFlowDto> whiteLists) {
|
Sheet sheet = null;
|
sheet = ImportExcelUtils.getSheet(workbook, "白单流水 导入新模板");
|
List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
|
PropertyWhiteListFlowDto white = null;
|
for (int osIndex = 0; osIndex < oList.size(); osIndex++) {
|
Object[] os = oList.get(osIndex);
|
if (osIndex == 1) { // 第一行是 头部信息 直接跳过
|
continue;
|
}
|
if (StringUtil.isNullOrNone(os[3])) {
|
continue;
|
}
|
Assert.hasValue(os[3], (osIndex + 1) + "行收费日期不能为空");
|
|
|
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 inputTime = null;
|
if(os[24] != null){
|
inputTime = defaultValueToDate(os[24]);
|
}
|
white.setChargeTime(chargeTime);//收费时间
|
white.setInvoiceReceiptNo(defaultValueToDate(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));
|
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());//收费人
|
white.setBankDepositDate(bankDepositDate);//银行解款日期
|
white.setBankDepositor(os[17] == null ? null : os[17].toString());//银行解款人
|
white.setSplitBankDepositAmount(os[18] == null ? null : os[18].toString());//拆分后银行解款金额
|
white.setBankDepositAmount(os[19] == null ? null : os[19].toString());//银行解款金额
|
white.setCheckAmount(os[20] == null ? null : os[20].toString());//核对金额
|
white.setWhiteListArchiveNo(os[21] == null ? null : os[21].toString());//白单存档编号
|
white.setSheetCount(os[22] == null ? null : os[22].toString());//张数
|
white.setFinancialReceiver(os[23] == null ? null : os[23].toString());//财务收账人
|
white.setInputTime(inputTime);//输入时间
|
white.setInputPerson(os[25] == null ? null : os[25].toString());//输入人员
|
white.setOrderNo(os[28] == null ? null : os[28].toString());//单号
|
white.setLicensePlate(os[29] == null ? null : os[29].toString());//车牌号
|
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.setPendingProblem(os[30] == null ? null : os[30].toString());
|
// if (os.length > 23){
|
// white.setFrequency(os[23] == null ? null : os[23].toString());
|
// }
|
white.setChargeStart(changeStart);
|
white.setChargeEnd(changeEnd);
|
white.setRow(osIndex + 1);
|
whiteLists.add(white);
|
}
|
}
|
|
public static Object[] removeElement(Object[] original, int index) {
|
// 校验原数组是否为空或索引越界
|
if (original == null || index < 0 || index >= original.length) {
|
return original; // 或抛出异常，根据需求处理
|
}
|
|
// 创建新数组（长度 = 原数组长度 - 1）
|
Object[] newArray = new Object[original.length - 1];
|
|
// 复制索引4之前的元素（0~3）
|
System.arraycopy(original, 0, newArray, 0, index);
|
|
// 复制索引4之后的元素（5~末尾），从新数组的index位置开始
|
System.arraycopy(original, index + 1, newArray, index, original.length - index - 1);
|
|
return newArray;
|
}
|
//
|
// private void getPropertyWhiteListFlowDtosV1(Workbook workbook, List<PropertyWhiteListFlowDto> whiteLists) {
|
// Sheet sheet = null;
|
// sheet = ImportExcelUtils.getSheet(workbook, "白单流水（归档数据）");
|
// List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
|
// PropertyWhiteListFlowDto white = null;
|
// for (int osIndex = 0; osIndex < oList.size(); osIndex++) {
|
// Object[] os = oList.get(osIndex);
|
// if (osIndex == 0) { // 第一行是 头部信息 直接跳过
|
// continue;
|
// }
|
// if (StringUtil.isNullOrNone(os[0])) {
|
// continue;
|
// }
|
// Assert.hasValue(os[0], (osIndex + 1) + "行收费日期不能为空");
|
//
|
// white = new PropertyWhiteListFlowDto();
|
// String chargeTime = excelDoubleToDate(os[0].toString());
|
// String changeStart = os[21] == null ? null : excelDoubleToDate(os[21].toString());
|
// String changeEnd = os[22] == null ? null : excelDoubleToDate(os[22].toString());
|
// String bankDepositDate = os[8] == null ? null : excelDoubleToDate(os[8].toString());
|
// String inputTime = null;
|
// if(os[16] != null){
|
// inputTime = excelDoubleToDate(os[16].toString().split("\\.")[0]);
|
// }
|
// white.setChargeTime(chargeTime);
|
// white.setInvoiceReceiptNo(os[1] == null ? null : os[1].toString());
|
// white.setRoomId(os[2] == null ? null : os[2].toString());
|
// white.setPropertyAddress(os[3] == null ? null : os[3].toString());
|
// white.setDoorRoomNum(defaultValueToDate(os[4]));
|
// white.setSecondaryFeeTypeCd(os[5] == null ? null : os[5].toString());
|
// white.setChargeAmount(os[6] == null ? null : os[6].toString());
|
// white.setCharger(os[7] == null ? null : os[7].toString());
|
// white.setBankDepositDate(bankDepositDate);
|
// white.setBankDepositor(os[9] == null ? null : os[9].toString());
|
// white.setSplitBankDepositAmount(os[10] == null ? null : os[10].toString());
|
// white.setBankDepositAmount(os[11] == null ? null : os[11].toString());
|
// white.setCheckAmount(os[12] == null ? null : os[12].toString());
|
// white.setWhiteListArchiveNo(os[13] == null ? null : os[13].toString());
|
// white.setSheetCount(os[14] == null ? null : os[14].toString());
|
// white.setFinancialReceiver(os[15] == null ? null : os[15].toString());
|
// white.setInputTime(inputTime);
|
// white.setInputPerson(os[17] == null ? null : os[17].toString());
|
// white.setOrderNo(os[18] == null ? null : os[18].toString());
|
// white.setLicensePlate(os[19] == null ? null : os[19].toString());
|
// white.setCategory22(os[20] == null ? null : os[20].toString());
|
// if (os.length > 23){
|
// white.setFrequency(os[23] == null ? null : os[23].toString());
|
// }
|
// white.setChargeStart(changeStart);
|
// white.setChargeEnd(changeEnd);
|
// white.setRow(osIndex + 1);
|
// 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();
|
}
|
}
|
}
|
