package com.java110.fee.cmd.fee;
|
|
import com.alibaba.fastjson.JSONObject;
|
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.dto.fee.FeeConfigDto;
|
import com.java110.dto.room.RoomDto;
|
import com.java110.intf.community.ICommunityInnerServiceSMO;
|
import com.java110.intf.community.IRoomInnerServiceSMO;
|
import com.java110.intf.fee.IFeeConfigInnerServiceSMO;
|
import com.java110.intf.fee.IReportFeeInnerServiceSMO;
|
import com.java110.utils.exception.CmdException;
|
import com.java110.utils.util.Assert;
|
import com.java110.utils.util.BeanConvertUtil;
|
import com.java110.utils.util.DateUtil;
|
import com.java110.vo.ResultVo;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.http.HttpHeaders;
|
import org.springframework.http.HttpStatus;
|
import org.springframework.http.ResponseEntity;
|
|
import java.io.ByteArrayOutputStream;
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.text.ParseException;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Java110Cmd(serviceCode = "fee.exportPropertyFeeExcelCmd")
|
public class ExportPropertyFeeExcelCmd extends Cmd {
|
|
@Autowired
|
private IReportFeeInnerServiceSMO reportFeeInnerServiceSMOImpl;
|
|
@Autowired
|
private ICommunityInnerServiceSMO communityInnerServiceSMOImpl;
|
|
@Autowired
|
private IRoomInnerServiceSMO roomInnerServiceSMOImpl;
|
|
@Autowired
|
private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMOImpl;
|
|
@Override
|
public void validate(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
|
Assert.hasKey(reqJson, "communityId", "请求报文中未包含小区编号");
|
}
|
|
@Override
|
public void doCmd(CmdEvent event, ICmdDataFlowContext context, JSONObject reqJson) throws CmdException, ParseException {
|
// 设置年份范围：2020年至当前年份
|
int startYear = 2020;
|
int currentYear = DateUtil.getYear();
|
int endYear = currentYear;
|
|
String communityId = reqJson.getString("communityId");
|
|
// 获取小区信息
|
CommunityDto communityDto = new CommunityDto();
|
communityDto.setCommunityId(communityId);
|
List<CommunityDto> communityDtos = communityInnerServiceSMOImpl.queryCommunitys(communityDto);
|
Assert.notEmpty(communityDtos, "未查询到小区信息");
|
|
// 查询房屋数据（不分页，获取全部）
|
RoomDto roomDto = new RoomDto();
|
roomDto.setCommunityId(communityId);
|
roomDto.setFloorNum(reqJson.containsKey("floorId") && !reqJson.getString("floorId").equals("") ? reqJson.getString("floorId") : null);
|
roomDto.setOwnerName(reqJson.containsKey("ownerName") && !reqJson.getString("ownerName").equals("") ? reqJson.getString("ownerName") : null);
|
// 取消分页限制
|
roomDto.setRow(Integer.MAX_VALUE);
|
roomDto.setPage(1);
|
List<Map> rooms = roomInnerServiceSMOImpl.queryRoomsAsReport(roomDto);
|
|
if (rooms.isEmpty()) {
|
ResultVo resultVo = new ResultVo("未查询到数据");
|
ResponseEntity<String> responseEntity = new ResponseEntity<>(resultVo.toString(), HttpStatus.OK);
|
context.setResponseEntity(responseEntity);
|
return;
|
}
|
|
// 获取费用配置
|
FeeConfigDto feeConfigDto = new FeeConfigDto();
|
feeConfigDto.setCommunityId(communityId);
|
if (reqJson.containsKey("feeTypeCd") && !reqJson.getString("feeTypeCd").equals("")) {
|
feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
|
} else {
|
feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
|
}
|
if (reqJson.containsKey("importFee") && !reqJson.getString("importFee").equals("")) {
|
feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
|
} else {
|
feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
|
}
|
List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMOImpl.queryFeeConfigs(feeConfigDto);
|
|
// 处理费用配置：去重、排序
|
List<FeeConfigDto> feeConfigDtos = originalDtos.stream()
|
.sorted(Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
|
.thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder())))
|
.collect(Collectors.groupingBy(dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(), Collectors.toList()))
|
.values().stream()
|
.map(group -> group.get(0))
|
.collect(Collectors.toList());
|
|
// 创建Excel
|
try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 内存中保留100行，其余写入临时文件
|
ByteArrayOutputStream out = new ByteArrayOutputStream()) {
|
|
// 创建工作表
|
Sheet sheet = workbook.createSheet("物业费报表_" + communityDtos.get(0).getName());
|
|
// 创建表头样式
|
CellStyle headerStyle = createHeaderStyle(workbook);
|
// 创建内容样式
|
CellStyle contentStyle = createContentStyle(workbook);
|
|
// 生成表头
|
int headerRowIndex = createExcelHeader(sheet, headerStyle, feeConfigDtos, startYear, endYear);
|
|
// 填充数据
|
fillExcelData(sheet, contentStyle, rooms, feeConfigDtos, startYear, endYear, headerRowIndex);
|
|
// 调整列宽
|
autoSizeColumns(sheet, 0, 15 + 2 * feeConfigDtos.size() + 3 * (endYear - startYear + 1));
|
|
// 写入输出流
|
workbook.write(out);
|
workbook.dispose(); // 清理临时文件
|
|
// 构建响应
|
byte[] bytes = out.toByteArray();
|
HttpHeaders headers = createExcelHeaders("物业费报表_" + DateUtil.getNow("yyyy-MM-dd") + ".xlsx");
|
ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(bytes, headers, HttpStatus.OK);
|
context.setResponseEntity(responseEntity);
|
|
} catch (IOException e) {
|
throw new CmdException("导出Excel失败：" + e.getMessage());
|
}
|
}
|
|
/**
|
* 创建表头样式
|
*/
|
private CellStyle createHeaderStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
// 设置边框
|
style.setBorderTop(BorderStyle.THIN);
|
style.setBorderBottom(BorderStyle.THIN);
|
style.setBorderLeft(BorderStyle.THIN);
|
style.setBorderRight(BorderStyle.THIN);
|
// 设置居中
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
// 设置背景色
|
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
|
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
// 设置字体
|
Font font = workbook.createFont();
|
font.setBold(true);
|
style.setFont(font);
|
return style;
|
}
|
|
/**
|
* 创建内容样式
|
*/
|
private CellStyle createContentStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
// 设置边框
|
style.setBorderTop(BorderStyle.THIN);
|
style.setBorderBottom(BorderStyle.THIN);
|
style.setBorderLeft(BorderStyle.THIN);
|
style.setBorderRight(BorderStyle.THIN);
|
// 设置居中
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
return style;
|
}
|
|
/**
|
* 创建Excel表头
|
*/
|
private int createExcelHeader(Sheet sheet, CellStyle headerStyle, List<FeeConfigDto> feeConfigDtos, int startYear, int endYear) {
|
int rowIndex = 0;
|
int feeCount = feeConfigDtos.size();
|
int yearCount = endYear - startYear + 1;
|
|
// 第一行表头
|
Row row1 = sheet.createRow(rowIndex++);
|
int colIndex = 0;
|
|
// 基础信息列
|
String[] baseHeaders = {"序号", "物业类型", "楼栋号/弄", "门号", "室号", "门室号", "产证地址", "收费面积（m²）", "购房人姓名"};
|
for (String header : baseHeaders) {
|
createCell(row1, colIndex++, header, headerStyle);
|
}
|
|
// 费用配置列
|
for (int i = 0; i < feeCount; i++) {
|
createCell(row1, colIndex++, feeConfigDtos.get(i).getFeeName(), headerStyle);
|
}
|
|
// 其他固定列
|
createCell(row1, colIndex++, "设备运作费", headerStyle);
|
for (int i = 0; i < feeCount; i++) {
|
createCell(row1, colIndex++, feeConfigDtos.get(i).getFeeName(), headerStyle);
|
}
|
createCell(row1, colIndex++, "年应收款", headerStyle);
|
createCell(row1, colIndex++, "合计(" + startYear + "年1月-至今)", headerStyle);
|
createCell(row1, colIndex++, "历年实收", headerStyle);
|
createCell(row1, colIndex++, "操作", headerStyle);
|
|
// 第二行表头
|
Row row2 = sheet.createRow(rowIndex++);
|
colIndex = 0;
|
|
// 跳过基础信息列
|
colIndex += baseHeaders.length + feeCount + 1 + feeCount + 1;
|
|
// 统计信息列
|
String[] statsHeaders = {"应收", "实收", "代收", startYear + "年", (startYear + 1) + "年", "待收月数", "待收金额", "代收区间"};
|
for (String header : statsHeaders) {
|
createCell(row2, colIndex++, header, headerStyle);
|
}
|
|
// 年份列
|
for (int i = startYear; i <= endYear; i++) {
|
createCell(row2, colIndex++, String.valueOf(i), headerStyle);
|
}
|
|
createCell(row2, colIndex++, "打折金额汇总", headerStyle);
|
|
// 第三行表头
|
Row row3 = sheet.createRow(rowIndex++);
|
colIndex = 0;
|
|
// 跳过前面的列
|
colIndex += baseHeaders.length + feeCount + 1 + feeCount + 1 + statsHeaders.length;
|
|
// 年份详情列
|
for (int i = startYear; i <= endYear; i++) {
|
createCell(row3, colIndex++, "已收月数", headerStyle);
|
createCell(row3, colIndex++, "已收区间", headerStyle);
|
createCell(row3, colIndex++, "未收区间", headerStyle);
|
}
|
|
// 合并单元格
|
// 这里可以根据需要添加单元格合并逻辑
|
|
return rowIndex;
|
}
|
|
/**
|
* 填充Excel数据
|
*/
|
private void fillExcelData(Sheet sheet, CellStyle contentStyle, List<Map> rooms,
|
List<FeeConfigDto> feeConfigDtos, int startYear, int endYear, int startRowIndex) {
|
int rowIndex = startRowIndex;
|
int feeCount = feeConfigDtos.size();
|
int yearCount = endYear - startYear + 1;
|
|
for (int i = 0; i < rooms.size(); i++) {
|
Map room = rooms.get(i);
|
Row row = sheet.createRow(rowIndex++);
|
int colIndex = 0;
|
|
// 基础信息
|
createCell(row, colIndex++, String.valueOf(i + 1), contentStyle); // 序号
|
createCell(row, colIndex++, room.get("property_type") != null ? room.get("property_type").toString() : "", contentStyle);
|
createCell(row, colIndex++, room.get("floor_num") != null ? room.get("floor_num").toString() : "", contentStyle);
|
createCell(row, colIndex++, room.get("unit_num") != null ? room.get("unit_num").toString() : "", contentStyle);
|
createCell(row, colIndex++, room.get("room_num") != null ? room.get("room_num").toString() : "", contentStyle);
|
createCell(row, colIndex++, (room.get("unit_num") != null ? room.get("unit_num").toString() : "") + "-" +
|
(room.get("room_num") != null ? room.get("room_num").toString() : ""), contentStyle);
|
createCell(row, colIndex++, room.get("property_address") != null ? room.get("property_address").toString() : "", contentStyle);
|
createCell(row, colIndex++, room.get("room_area") != null ? room.get("room_area").toString() : "", contentStyle);
|
createCell(row, colIndex++, room.get("name") != null ? room.get("name").toString() : "", contentStyle);
|
|
// 费用配置信息
|
for (FeeConfigDto config : feeConfigDtos) {
|
createCell(row, colIndex++, config.getSquarePrice() != null ? config.getSquarePrice() : "", contentStyle);
|
}
|
|
// 设备运作费
|
createCell(row, colIndex++, "", contentStyle);
|
|
// 费用计算
|
BigDecimal roomArea = room.get("room_area") != null ? (BigDecimal) room.get("room_area") : BigDecimal.ZERO;
|
for (FeeConfigDto config : feeConfigDtos) {
|
double price = config.getSquarePrice() != null ? Double.parseDouble(config.getSquarePrice()) : 0;
|
double total = price * roomArea.doubleValue();
|
createCell(row, colIndex++, String.valueOf(doublequ2(total)), contentStyle);
|
}
|
|
// 年应收款
|
double year应收 = feeConfigDtos.stream()
|
.mapToDouble(config -> {
|
double price = config.getSquarePrice() != null ? Double.parseDouble(config.getSquarePrice()) : 0;
|
return price * roomArea.doubleValue() * 12;
|
})
|
.sum();
|
createCell(row, colIndex++, String.valueOf(doublequ2(year应收)), contentStyle);
|
|
// 获取费用数据
|
HashMap<Object, Object> params = new HashMap<>();
|
params.put("payObjId", room.get("room_id"));
|
List<Map> feeData = reportFeeInnerServiceSMOImpl.onceRoomFee(params);
|
|
// 计算合计信息
|
double allMoney = 0;
|
double allPayMoney = 0;
|
int monthCount = 0;
|
int noPayMoney = 0;
|
double discountMoney = 0;
|
|
for (Map fee : feeData) {
|
if (fee.containsKey("统计维度") && !fee.get("统计维度").toString().contains("总计")) {
|
allMoney += ((BigDecimal) fee.get("应收金额")).doubleValue();
|
allPayMoney += ((BigDecimal) fee.get("实收金额")).doubleValue();
|
monthCount += ((BigDecimal) fee.get("未收月数")).intValue();
|
noPayMoney += ((BigDecimal) fee.get("未收金额")).intValue();
|
discountMoney += ((BigDecimal) fee.get("折扣金额")).doubleValue();
|
}
|
}
|
|
// 填充合计信息
|
createCell(row, colIndex++, String.valueOf(doublequ2(allMoney)), contentStyle); // 合计
|
createCell(row, colIndex++, String.valueOf(doublequ2(allPayMoney)), contentStyle); // 历年实收
|
createCell(row, colIndex++, "", contentStyle); // 操作
|
|
// 填充统计信息
|
createCell(row, colIndex++, String.valueOf(doublequ2(allMoney)), contentStyle); // 应收
|
createCell(row, colIndex++, String.valueOf(doublequ2(allPayMoney)), contentStyle); // 实收
|
createCell(row, colIndex++, "", contentStyle); // 代收
|
createCell(row, colIndex++, "", contentStyle); // 2020年
|
createCell(row, colIndex++, "", contentStyle); // 2021年
|
createCell(row, colIndex++, String.valueOf(monthCount), contentStyle); // 待收月数
|
createCell(row, colIndex++, String.valueOf(noPayMoney), contentStyle); // 待收金额
|
createCell(row, colIndex++, "", contentStyle); // 代收区间
|
|
// 填充年份数据
|
for (int year = startYear; year <= endYear; year++) {
|
// 年份总数据（占位）
|
createCell(row, colIndex++, "", contentStyle);
|
|
// 已收月数、已收区间、未收区间
|
String receivedMonths = "0";
|
String receivedRange = "";
|
String unpaidRange = "";
|
|
for (Map fee : feeData) {
|
if (fee.get("统计维度").equals(year + "年")) {
|
receivedMonths = ((BigDecimal) fee.get("已收月数")).intValue() + "";
|
receivedRange = fee.get("已收区间") != null ? fee.get("已收区间").toString() : "";
|
unpaidRange = fee.get("未收区间") != null ? fee.get("未收区间").toString() : "";
|
break;
|
}
|
}
|
|
createCell(row, colIndex++, receivedMonths, contentStyle);
|
createCell(row, colIndex++, receivedRange, contentStyle);
|
createCell(row, colIndex++, unpaidRange, contentStyle);
|
}
|
|
// 打折金额汇总
|
createCell(row, colIndex++, String.valueOf(doublequ2(discountMoney)), contentStyle);
|
}
|
}
|
|
/**
|
* 创建单元格并设置值和样式
|
*/
|
private void createCell(Row row, int column, String value, CellStyle style) {
|
Cell cell = row.createCell(column);
|
cell.setCellValue(value);
|
cell.setCellStyle(style);
|
}
|
|
/**
|
* 自动调整列宽
|
*/
|
private void autoSizeColumns(Sheet sheet, int startCol, int endCol) {
|
for (int i = startCol; i <= endCol; i++) {
|
sheet.autoSizeColumn(i);
|
// 适当调整自动列宽，避免过窄
|
int width = sheet.getColumnWidth(i) + 256;
|
sheet.setColumnWidth(i, width > 65535 ? 65535 : width);
|
}
|
}
|
|
/**
|
* 创建Excel响应头
|
*/
|
private HttpHeaders createExcelHeaders(String fileName) {
|
HttpHeaders headers = new HttpHeaders();
|
try {
|
// 设置文件名编码
|
String encodedFileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
|
headers.setContentDispositionFormData("attachment", encodedFileName);
|
} catch (Exception e) {
|
headers.setContentDispositionFormData("attachment", fileName);
|
}
|
headers.setContentType(org.springframework.http.MediaType.APPLICATION_OCTET_STREAM);
|
return headers;
|
}
|
|
/**
|
* 保留两位小数
|
*/
|
public double doublequ2(double num) {
|
return (int) (num * 100) / 100.0;
|
}
|
}
|
