package com.java110.job.cmd.importCarInout;
|
|
import com.alibaba.druid.util.StringUtils;
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.read.listener.PageReadListener;
|
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
|
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
|
import com.alibaba.fastjson.JSONObject;
|
import com.aliyun.oss.OSSClient;
|
import com.aliyun.oss.model.ListObjectsRequest;
|
import com.aliyun.oss.model.ObjectListing;
|
import com.aliyun.oss.model.PutObjectRequest;
|
import com.java110.core.annotation.Java110Cmd;
|
import com.java110.core.context.ICmdDataFlowContext;
|
import com.java110.core.event.cmd.Cmd;
|
import com.java110.core.event.cmd.CmdEvent;
|
import com.java110.dto.community.CommunityDto;
|
import com.java110.intf.common.ICarInoutInnerServiceSMO;
|
import com.java110.intf.community.ICommunityInnerServiceSMO;
|
import com.java110.po.car.CarInoutPo;
|
import com.java110.utils.cache.MappingCache;
|
import com.java110.utils.exception.CmdException;
|
import com.java110.utils.util.DateUtil;
|
import com.java110.utils.util.OSSUtil;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
|
import java.io.ByteArrayInputStream;
|
import java.io.ByteArrayOutputStream;
|
import java.io.InputStream;
|
import java.math.BigDecimal;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
@Java110Cmd(serviceCode = "import.CarInout")
|
public class CarInout extends Cmd {
|
private static final Logger log = LoggerFactory.getLogger(CarInout.class);
|
|
public static final String DOMAIN = "OSS";
|
public static final String BUCKET_NAME = "bucketName";
|
public static final String ERROR_FOLDER = "小区车辆进出场导入文件异常地址"; // 错误文件存储目录
|
|
@Autowired
|
private ICommunityInnerServiceSMO communityInnerServiceSMO;
|
|
// 使用你实际的Service接口
|
@Autowired
|
private ICarInoutInnerServiceSMO carInoutInnerServiceSMOImpl;
|
|
@Override
|
public void validate(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
|
// 验证逻辑，如果需要的话
|
}
|
|
@Override
|
public void doCmd(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
|
CommunityDto communityDto = new CommunityDto();
|
communityDto.setStatusCd("0");
|
List<CommunityDto> allCommunities = communityInnerServiceSMO.queryCommunitys(communityDto);
|
if (allCommunities == null || allCommunities.isEmpty()) {
|
log.info("没有查询到任何小区信息，无需处理");
|
return;
|
}
|
|
OSSClient ossClient = null;
|
try {
|
// 获取OSS客户端
|
ossClient = OSSUtil.getOSSClient();
|
String bucketName = MappingCache.getValue(DOMAIN, BUCKET_NAME);
|
|
if (StringUtils.isEmpty(bucketName)) {
|
throw new CmdException("未配置OSS bucket名称");
|
}
|
|
// 遍历每个小区，处理对应的OSS文件
|
for (CommunityDto community : allCommunities) {
|
String communityName = community.getName();
|
if (StringUtils.isEmpty(communityName)) {
|
log.warn("小区ID: {} 名称为空，跳过处理", community.getCommunityId());
|
continue;
|
}
|
|
// 构建小区在OSS中的文件夹路径
|
String communityFolder = MappingCache.getValue("OSS", "小区车辆进出场导入文件地址") + communityName + "/";
|
|
// 检查该小区文件夹是否存在
|
if (!doesFolderExist(ossClient, bucketName, communityFolder)) {
|
log.info("小区: {} 的OSS文件夹: {} 不存在，跳过处理", communityName, communityFolder);
|
continue;
|
}
|
|
// 获取最近11天的日期
|
List<String> recentDates = getRecent11Days();
|
|
// 遍历每一天，处理对应的Excel文件
|
for (String date : recentDates) {
|
// 构建Excel文件名
|
String fileName = "车辆进出场明细_" + communityName + "_" + date + ".xlsx";
|
String fileKey = communityFolder + fileName;
|
|
// 检查文件是否存在
|
if (!ossClient.doesObjectExist(bucketName, fileKey)) {
|
log.info("文件: {} 不存在，跳过处理", fileKey);
|
continue;
|
}
|
|
try {
|
// 从OSS获取文件并解析
|
InputStream inputStream = ossClient.getObject(bucketName, fileKey).getObjectContent();
|
|
// 解析并分离成功和失败的记录
|
RecordResult result = parseAndValidateExcel(inputStream, community.getCommunityId(), date);
|
|
// 将解析成功的数据存入数据库，使用你的Service实现
|
if (result.getSuccessRecords() != null && !result.getSuccessRecords().isEmpty()) {
|
int min = 100000000;
|
int max = 999999999;
|
Random RANDOM = new Random();
|
for (CarInoutPo carInoutPo : result.getSuccessRecords()) {
|
// carInoutPo.setInoutId("937510345");
|
carInoutPo.setInoutId((min + RANDOM.nextInt(max - min + 1))+"");
|
carInoutPo.setCommunityId(community.getCommunityId());
|
}
|
int[] counts = carInoutInnerServiceSMOImpl.batchSaveCarInout(result.getSuccessRecords());
|
|
// 统计成功和失败的总条数
|
int successTotal = 0;
|
int errorTotal = 0;
|
for (int count : counts) {
|
if (count == 1) {
|
successTotal++;
|
} else {
|
errorTotal++;
|
}
|
}
|
|
// 输出总体导入结果日志
|
log.info("小区: {} 的 {} 日期数据导入完成 - 总记录数: {}, 成功: {}, 失败: {}",
|
communityName, date, counts.length, successTotal, errorTotal);
|
|
// 处理失败的记录，生成错误Excel并上传到OSS
|
if (errorTotal > 0) {
|
// 收集具体失败的记录（根据counts数组筛选）
|
List<ErrorExcelRowData> failedRecords = new ArrayList<>();
|
for (int i = 0; i < counts.length; i++) {
|
if (counts[i] == 0) {
|
failedRecords.add(convertPoToErrorRowData(result.getSuccessRecords().get(i)));
|
}
|
}
|
|
handleErrorRecords(ossClient, bucketName, communityName, date, fileName, failedRecords);
|
log.info("小区: {} 的 {} 日期数据中，{} 条失败记录已生成错误文件",
|
communityName, date, errorTotal);
|
}
|
}
|
|
inputStream.close();
|
} catch (Exception e) {
|
// 处理文件读取或解析异常
|
log.error("处理文件 {} 时发生错误", fileKey, e);
|
}
|
}
|
}
|
} finally {
|
// 关闭OSS客户端
|
if (ossClient != null) {
|
ossClient.shutdown();
|
}
|
}
|
}
|
|
/**
|
* 处理错误记录，生成Excel并上传到OSS
|
*/
|
private void handleErrorRecords(OSSClient ossClient, String bucketName, String communityName,
|
String date, String originalFileName, List<ErrorExcelRowData> errorRecords) {
|
try {
|
// 创建错误文件名，在原文件名前加"错误_"
|
String errorFileName = "错误_" + originalFileName;
|
// 错误文件存储路径
|
String errorFileKey = MappingCache.getValue(DOMAIN, ERROR_FOLDER) + communityName + "/" + errorFileName;
|
|
// 生成错误Excel文件
|
ByteArrayOutputStream out = new ByteArrayOutputStream();
|
ExcelWriterBuilder writerBuilder = EasyExcel.write(out, ErrorExcelRowData.class);
|
ExcelWriterSheetBuilder sheetBuilder = writerBuilder.sheet("错误记录");
|
sheetBuilder.doWrite(errorRecords);
|
|
// 将错误文件上传到OSS
|
InputStream inputStream = new ByteArrayInputStream(out.toByteArray());
|
ossClient.putObject(new PutObjectRequest(bucketName, errorFileKey, inputStream));
|
|
out.close();
|
inputStream.close();
|
|
System.out.println("错误记录文件已上传至: "+errorFileKey);
|
} catch (Exception e) {
|
log.error("处理错误记录时发生异常", e);
|
}
|
}
|
|
private ErrorExcelRowData convertPoToErrorRowData(CarInoutPo po) {
|
ErrorExcelRowData errorRow = new ErrorExcelRowData();
|
// 根据实际字段映射关系赋值
|
errorRow.setPlateNumber(po.getCarNum()); // 车牌号
|
// errorRow.setCommunityName(po.getCommunityName()); // 若CarInoutPo有小区名称字段
|
errorRow.setEntryTime(po.getInTime()); // 进场时间
|
errorRow.setExitName(po.getOutTime()); // 离场时间（原代码中ExcelRowData的exitName实际对应离场时间）
|
errorRow.setVehicleType(po.getVehicleType()); // 车辆类型
|
errorRow.setPaidStatus(po.getState()); // 支付状态
|
errorRow.setPaymentAmount(po.getPaymentAmount()); // 支付金额
|
errorRow.setMerchantId(po.getMerchantId()); // 商户ID
|
errorRow.setDirection(po.getDirection()); // 方向
|
// 可在此处设置错误原因（如数据库插入失败的具体原因）
|
errorRow.setErrorReason(po.getErrorTest());
|
return errorRow;
|
}
|
|
/**
|
* 解析Excel文件并验证数据，分离成功和失败的记录
|
*/
|
private RecordResult parseAndValidateExcel(InputStream inputStream, String communityId, String date) {
|
RecordResult result = new RecordResult();
|
List<CarInoutPo> successRecords = new ArrayList<>();
|
List<ErrorExcelRowData> errorRecords = new ArrayList<>();
|
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
|
SimpleDateFormat targetSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
try {
|
EasyExcel.read(inputStream)
|
.head(ExcelRowData.class)
|
.sheet()
|
.registerReadListener(new PageReadListener<ExcelRowData>(dataList -> {
|
for (ExcelRowData rowData : dataList) {
|
// 转换为错误记录对象（初始没有错误信息）
|
ErrorExcelRowData errorRow = convertToErrorRowData(rowData);
|
|
try {
|
// 验证并转换数据
|
CarInoutPo po = convertToCarInoutPo(rowData, communityId, sdf, targetSdf, errorRow);
|
if (po != null) {
|
successRecords.add(po);
|
} else {
|
// 如果转换失败但没有错误信息，添加默认错误信息
|
if (StringUtils.isEmpty(errorRow.getErrorReason())) {
|
errorRow.setErrorReason("数据转换失败，原因未知");
|
}
|
errorRecords.add(errorRow);
|
}
|
} catch (Exception e) {
|
// 捕获转换过程中的异常
|
errorRow.setErrorReason("解析异常: " + e.getMessage());
|
errorRecords.add(errorRow);
|
log.error("解析Excel行数据失败", e);
|
}
|
}
|
}))
|
.doRead();
|
} catch (Exception e) {
|
log.error("解析Excel文件失败", e);
|
}
|
|
result.setSuccessRecords(successRecords);
|
result.setErrorRecords(errorRecords);
|
return result;
|
}
|
|
/**
|
* 将Excel行数据转换为错误记录行数据（用于错误文件导出）
|
*/
|
private ErrorExcelRowData convertToErrorRowData(ExcelRowData rowData) {
|
ErrorExcelRowData errorRow = new ErrorExcelRowData();
|
errorRow.setPlateNumber(rowData.getPlateNumber());
|
errorRow.setCommunityName(rowData.getCommunityName());
|
errorRow.setEntryTime(rowData.getEntryTime());
|
errorRow.setExitName(rowData.getExitName());
|
errorRow.setVehicleType(rowData.getVehicleType());
|
errorRow.setPaidStatus(rowData.getPaidStatus());
|
errorRow.setPaymentAmount(rowData.getPaymentAmount());
|
errorRow.setMerchantId(rowData.getMerchantId());
|
errorRow.setDirection(rowData.getDirection());
|
return errorRow;
|
}
|
|
/**
|
* 将Excel行数据转换为CarInoutPo对象，并收集错误信息
|
*/
|
private CarInoutPo convertToCarInoutPo(ExcelRowData rowData, String communityId,
|
SimpleDateFormat sdf, SimpleDateFormat targetSdf,
|
ErrorExcelRowData errorRow) {
|
List<String> errorReasons = new ArrayList<>();
|
|
// 验证必填字段
|
if (StringUtils.isEmpty(rowData.getPlateNumber())) {
|
errorReasons.add("车牌号为空");
|
}
|
|
if (StringUtils.isEmpty(rowData.getEntryTime())) {
|
errorReasons.add("进场时间为空");
|
}
|
|
// 处理进场时间格式
|
Date entryDate = null;
|
if (!StringUtils.isEmpty(rowData.getEntryTime())) {
|
try {
|
entryDate = sdf.parse(rowData.getEntryTime());
|
} catch (ParseException e) {
|
errorReasons.add("进场时间格式错误，应为yyyy/MM/dd");
|
}
|
}
|
|
// 处理离场时间格式
|
if (!StringUtils.isEmpty(rowData.getExitName())) {
|
try {
|
sdf.parse(rowData.getExitName());
|
} catch (ParseException e) {
|
errorReasons.add("离场时间格式错误，应为yyyy/MM/dd");
|
}
|
}
|
|
// 处理支付金额格式
|
if (!StringUtils.isEmpty(rowData.getPaymentAmount())) {
|
try {
|
new BigDecimal(rowData.getPaymentAmount());
|
} catch (NumberFormatException e) {
|
errorReasons.add("支付金额格式错误，应为数字");
|
}
|
}
|
|
// 如果有错误，记录错误信息并返回null
|
if (!errorReasons.isEmpty()) {
|
errorRow.setErrorReason(String.join("; ", errorReasons));
|
return null;
|
}
|
|
CarInoutPo po = new CarInoutPo();
|
// 设置主键ID
|
po.setInoutId(UUID.randomUUID().toString());
|
// 设置业务ID，默认为-1
|
po.setbId("-1");
|
// 设置小区ID
|
po.setCommunityId(communityId);
|
// 设置车牌号
|
po.setCarNum(rowData.getPlateNumber().trim());
|
// 设置车辆类型，默认为temp
|
po.setVehicleType(StringUtils.isEmpty(rowData.getVehicleType()) ? "temp" : rowData.getVehicleType());
|
|
// 设置支付金额
|
if (!StringUtils.isEmpty(rowData.getPaymentAmount())) {
|
po.setPaymentAmount(String.valueOf(new BigDecimal(rowData.getPaymentAmount())));
|
} else {
|
po.setPaymentAmount(String.valueOf(BigDecimal.ZERO));
|
}
|
|
// 设置商户ID
|
po.setMerchantId(rowData.getMerchantId());
|
// 设置方向
|
po.setDirection(rowData.getDirection());
|
// 设置状态
|
po.setState(StringUtils.isEmpty(rowData.getPaidStatus()) ? "" : rowData.getPaidStatus());
|
|
// 设置进场时间
|
po.setInTime(targetSdf.format(entryDate));
|
|
// 处理离场时间
|
if (!StringUtils.isEmpty(rowData.getExitName())) {
|
try {
|
Date exitDate = sdf.parse(rowData.getExitName());
|
po.setOutTime(targetSdf.format(exitDate));
|
} catch (ParseException e) {
|
// 这里理论上不会发生，因为前面已经验证过格式
|
po.setOutTime(null);
|
}
|
}
|
|
// 设置创建时间
|
po.setCreateTime(DateUtil.getNow(DateUtil.DATE_FORMATE_STRING_A));
|
// 设置数据状态，默认为0（在用）
|
po.setStatusCd("0");
|
|
return po;
|
}
|
|
/**
|
* 检查文件夹是否存在
|
*/
|
private boolean doesFolderExist(OSSClient ossClient, String bucketName, String folderKey) {
|
ListObjectsRequest request = new ListObjectsRequest(bucketName)
|
.withPrefix(folderKey)
|
.withMaxKeys(1);
|
|
ObjectListing listing = ossClient.listObjects(request);
|
return listing.getObjectSummaries().size() > 0 || listing.getCommonPrefixes().size() > 0;
|
}
|
|
/**
|
* 获取最近11天的日期字符串（格式：yyyy-MM-dd）
|
*/
|
private List<String> getRecent11Days() {
|
List<String> dates = new ArrayList<>();
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Calendar calendar = Calendar.getInstance();
|
|
for (int i = 0; i < 11; i++) {
|
dates.add(sdf.format(calendar.getTime()));
|
calendar.add(Calendar.DAY_OF_YEAR, -1); // 前一天
|
}
|
|
return dates;
|
}
|
|
/**
|
* 记录解析结果的内部类
|
*/
|
private static class RecordResult {
|
private List<CarInoutPo> successRecords;
|
private List<ErrorExcelRowData> errorRecords;
|
|
public List<CarInoutPo> getSuccessRecords() {
|
return successRecords;
|
}
|
|
public void setSuccessRecords(List<CarInoutPo> successRecords) {
|
this.successRecords = successRecords;
|
}
|
|
public List<ErrorExcelRowData> getErrorRecords() {
|
return errorRecords;
|
}
|
|
public void setErrorRecords(List<ErrorExcelRowData> errorRecords) {
|
this.errorRecords = errorRecords;
|
}
|
}
|
|
/**
|
* Excel行数据映射类
|
*/
|
public static class ExcelRowData {
|
private String plateNumber; // 对应Excel的plate_number
|
private String communityName; // 对应Excel的community_name
|
private String entryTime; // 对应Excel的entry_time
|
private String exitName; // 对应Excel的exit_name
|
private String vehicleType; // 对应Excel的vehicle_type
|
private String paidStatus; // 对应Excel的paid_status
|
private String paymentAmount; // 对应Excel的payment_amount
|
private String merchantId; // 对应Excel的merchant_id
|
private String direction; // 对应Excel的direction
|
|
// Getter和Setter方法
|
public String getPlateNumber() {
|
return plateNumber;
|
}
|
|
public void setPlateNumber(String plateNumber) {
|
this.plateNumber = plateNumber;
|
}
|
|
public String getCommunityName() {
|
return communityName;
|
}
|
|
public void setCommunityName(String communityName) {
|
this.communityName = communityName;
|
}
|
|
public String getEntryTime() {
|
return entryTime;
|
}
|
|
public void setEntryTime(String entryTime) {
|
this.entryTime = entryTime;
|
}
|
|
public String getExitName() {
|
return exitName;
|
}
|
|
public void setExitName(String exitName) {
|
this.exitName = exitName;
|
}
|
|
public String getVehicleType() {
|
return vehicleType;
|
}
|
|
public void setVehicleType(String vehicleType) {
|
this.vehicleType = vehicleType;
|
}
|
|
public String getPaidStatus() {
|
return paidStatus;
|
}
|
|
public void setPaidStatus(String paidStatus) {
|
this.paidStatus = paidStatus;
|
}
|
|
public String getPaymentAmount() {
|
return paymentAmount;
|
}
|
|
public void setPaymentAmount(String paymentAmount) {
|
this.paymentAmount = paymentAmount;
|
}
|
|
public String getMerchantId() {
|
return merchantId;
|
}
|
|
public void setMerchantId(String merchantId) {
|
this.merchantId = merchantId;
|
}
|
|
public String getDirection() {
|
return direction;
|
}
|
|
public void setDirection(String direction) {
|
this.direction = direction;
|
}
|
}
|
|
/**
|
* 错误记录Excel行数据（包含错误原因）
|
*/
|
public static class ErrorExcelRowData {
|
private String plateNumber; // 对应Excel的plate_number
|
private String communityName; // 对应Excel的community_name
|
private String entryTime; // 对应Excel的entry_time
|
private String exitName; // 对应Excel的exit_name
|
private String vehicleType; // 对应Excel的vehicle_type
|
private String paidStatus; // 对应Excel的paid_status
|
private String paymentAmount; // 对应Excel的payment_amount
|
private String merchantId; // 对应Excel的merchant_id
|
private String direction; // 对应Excel的direction
|
private String errorReason; // 错误原因
|
|
// Getter和Setter方法
|
public String getPlateNumber() {
|
return plateNumber;
|
}
|
|
public void setPlateNumber(String plateNumber) {
|
this.plateNumber = plateNumber;
|
}
|
|
public String getCommunityName() {
|
return communityName;
|
}
|
|
public void setCommunityName(String communityName) {
|
this.communityName = communityName;
|
}
|
|
public String getEntryTime() {
|
return entryTime;
|
}
|
|
public void setEntryTime(String entryTime) {
|
this.entryTime = entryTime;
|
}
|
|
public String getExitName() {
|
return exitName;
|
}
|
|
public void setExitName(String exitName) {
|
this.exitName = exitName;
|
}
|
|
public String getVehicleType() {
|
return vehicleType;
|
}
|
|
public void setVehicleType(String vehicleType) {
|
this.vehicleType = vehicleType;
|
}
|
|
public String getPaidStatus() {
|
return paidStatus;
|
}
|
|
public void setPaidStatus(String paidStatus) {
|
this.paidStatus = paidStatus;
|
}
|
|
public String getPaymentAmount() {
|
return paymentAmount;
|
}
|
|
public void setPaymentAmount(String paymentAmount) {
|
this.paymentAmount = paymentAmount;
|
}
|
|
public String getMerchantId() {
|
return merchantId;
|
}
|
|
public void setMerchantId(String merchantId) {
|
this.merchantId = merchantId;
|
}
|
|
public String getDirection() {
|
return direction;
|
}
|
|
public void setDirection(String direction) {
|
this.direction = direction;
|
}
|
|
public String getErrorReason() {
|
return errorReason;
|
}
|
|
public void setErrorReason(String errorReason) {
|
this.errorReason = errorReason;
|
}
|
}
|
}
|
