From 23e50e9ef7f7189cb8e26c86e30de2e83486cf62 Mon Sep 17 00:00:00 2001
From: java110 <928255095@qq.com>
Date: 星期五, 05 三月 2021 10:49:49 +0800
Subject: [PATCH] 优化 查询
---
service-front/src/main/java/com/java110/front/smo/assetExport/impl/ExportReportFeeSMOImpl.java | 183 +++++++++++++++++++++++----------------------
1 files changed, 95 insertions(+), 88 deletions(-)
diff --git a/service-front/src/main/java/com/java110/front/smo/assetExport/impl/ExportReportFeeSMOImpl.java b/service-front/src/main/java/com/java110/front/smo/assetExport/impl/ExportReportFeeSMOImpl.java
index 4535c79..47fba4e 100644
--- a/service-front/src/main/java/com/java110/front/smo/assetExport/impl/ExportReportFeeSMOImpl.java
+++ b/service-front/src/main/java/com/java110/front/smo/assetExport/impl/ExportReportFeeSMOImpl.java
@@ -39,13 +39,13 @@
@Service("exportReportFeeSMOImpl")
public class ExportReportFeeSMOImpl extends BaseComponentSMO implements IExportReportFeeSMO {
private final static Logger logger = LoggerFactory.getLogger(ExportReportFeeSMOImpl.class);
-
public static final String REPORT_FEE_SUMMARY = "reportFeeSummary";
public static final String REPORT_FLOOR_UNIT_FEE_SUMMARY = "reportFloorUnitFeeSummary";
public static final String REPORT_FEE_BREAKDOWN = "reportFeeBreakdown";
public static final String REPORT_FEE_DETAIL = "reportFeeDetail";
public static final String REPORT_OWE_FEE_DETAIL = "reportOweFeeDetail";
public static final String REPORT_PAY_FEE_DETAIL = "reportPayFeeDetail";
+ public static final String REPORT_YEAR_COLLECTION = "reportYearCollection";
@Autowired
private RestTemplate restTemplate;
@@ -84,10 +84,11 @@
case REPORT_PAY_FEE_DETAIL:
reportPayFeeDetail(pd, result, workbook);
break;
+ case REPORT_YEAR_COLLECTION:
+ reportYearCollection(pd, result, workbook);
+ break;
}
-
-
ByteArrayOutputStream os = new ByteArrayOutputStream();
MultiValueMap headers = new HttpHeaders();
headers.add("content-type", "application/octet-stream;charset=UTF-8");
@@ -117,16 +118,19 @@
row.createCell(0).setCellValue("璐圭敤缂栧彿");
row.createCell(1).setCellValue("鎴垮彿");
row.createCell(2).setCellValue("璐圭敤椤�");
- row.createCell(3).setCellValue("缂磋垂寮�濮嬫椂闂�");
- row.createCell(4).setCellValue("缂磋垂缁撴潫鏃堕棿");
- row.createCell(5).setCellValue("缂磋垂鏃堕棿");
- row.createCell(6).setCellValue("搴旀敹閲戦");
- row.createCell(7).setCellValue("瀹炴敹閲戦");
-
-
+ row.createCell(3).setCellValue("鏀粯鏂瑰紡");
+ row.createCell(4).setCellValue("缂磋垂寮�濮嬫椂闂�");
+ row.createCell(5).setCellValue("缂磋垂缁撴潫鏃堕棿");
+ row.createCell(6).setCellValue("缂磋垂鏃堕棿");
+ row.createCell(7).setCellValue("搴旀敹閲戦");
+ row.createCell(8).setCellValue("瀹炴敹閲戦");
+ row.createCell(9).setCellValue("浼樻儬閲戦");
+ row.createCell(10).setCellValue("鍑忓厤閲戦");
+ row.createCell(11).setCellValue("婊炵撼閲�");
+ row.createCell(12).setCellValue("绌虹疆鎴挎墦鎶橀噾棰�");
+ row.createCell(13).setCellValue("绌虹疆鎴垮噺鍏嶉噾棰�");
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportPayFeeDetail(pd, result);
-
if (rooms == null) {
return;
}
@@ -134,16 +138,59 @@
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(roomIndex + 1);
row.createCell(1).setCellValue(dataObj.getString("objName"));
row.createCell(2).setCellValue(dataObj.getString("feeName"));
- row.createCell(3).setCellValue(dataObj.getString("startTime"));
- row.createCell(4).setCellValue(dataObj.getString("endTime"));
- row.createCell(5).setCellValue(dataObj.getString("createTime"));
- row.createCell(6).setCellValue(dataObj.getString("receivableAmount"));
- row.createCell(7).setCellValue(dataObj.getString("receivedAmount"));
+ row.createCell(3).setCellValue(dataObj.getString("primeRate"));
+ row.createCell(4).setCellValue(dataObj.getString("startTime"));
+ row.createCell(5).setCellValue(dataObj.getString("endTime"));
+ row.createCell(6).setCellValue(dataObj.getString("createTime"));
+ row.createCell(7).setCellValue(dataObj.getString("receivableAmount"));
+ row.createCell(8).setCellValue(dataObj.getString("receivedAmount"));
+ row.createCell(9).setCellValue(dataObj.getString("preferentialAmount"));
+ row.createCell(10).setCellValue(dataObj.getString("deductionAmount"));
+ row.createCell(11).setCellValue(dataObj.getString("lateFee"));
+ row.createCell(12).setCellValue(dataObj.getString("vacantHousingDiscount"));
+ row.createCell(13).setCellValue(dataObj.getString("vacantHousingReduction"));
+ }
+ }
+ private void reportYearCollection(IPageData pd, ComponentValidateResult result, Workbook workbook) {
+ Sheet sheet = workbook.createSheet("璐圭敤鍙拌处");
+ Row row = sheet.createRow(0);
+ row.createCell(0).setCellValue("濮撳悕");
+ row.createCell(1).setCellValue("鎴垮彿");
+ row.createCell(2).setCellValue("鑱旂郴鐢佃瘽");
+ row.createCell(3).setCellValue("闈㈢Н");
+ row.createCell(4).setCellValue("璐圭敤鍚嶇О");
+ row.createCell(5).setCellValue("搴旀敹閲戦");
+
+ //鏌ヨ妤兼爧淇℃伅
+ JSONArray rooms = this.getReportYearCollection(pd, result);
+ if (rooms == null) {
+ return;
+ }
+ JSONArray reportFeeYearCollectionDetailDtos = rooms.getJSONObject(0).getJSONArray("reportFeeYearCollectionDetailDtos");
+
+ for (int detailIndex = 0; detailIndex < reportFeeYearCollectionDetailDtos.size(); detailIndex++) {
+ row.createCell(6 + detailIndex).setCellValue(reportFeeYearCollectionDetailDtos.getJSONObject(detailIndex).getString("collectionYear") + "骞�");
+ }
+
+ JSONObject dataObj = null;
+ for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
+ row = sheet.createRow(roomIndex + 1);
+ dataObj = rooms.getJSONObject(roomIndex);
+ row.createCell(0).setCellValue(dataObj.getString("ownerName"));
+ row.createCell(1).setCellValue(dataObj.getString("objName"));
+ row.createCell(2).setCellValue(dataObj.getString("ownerLink"));
+ row.createCell(3).setCellValue(dataObj.getString("builtUpArea"));
+ row.createCell(4).setCellValue(dataObj.getString("feeName"));
+ row.createCell(5).setCellValue(dataObj.getString("receivableAmount"));
+
+ reportFeeYearCollectionDetailDtos = dataObj.getJSONArray("reportFeeYearCollectionDetailDtos");
+ for (int detailIndex = 0; detailIndex < reportFeeYearCollectionDetailDtos.size(); detailIndex++) {
+ row.createCell(6 + detailIndex).setCellValue(reportFeeYearCollectionDetailDtos.getJSONObject(detailIndex).getString("receivedAmount"));
+ }
}
}
@@ -155,19 +202,31 @@
reqJson.put("row", 10000);
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryPayFeeDetail" + mapToUrlParam(reqJson);
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
+ return savedRoomInfoResults.getJSONArray("data");
+ }
-
+ private JSONArray getReportYearCollection(IPageData pd, ComponentValidateResult result) {
+ String apiUrl = "";
+ ResponseEntity<String> responseEntity = null;
+ JSONObject reqJson = JSONObject.parseObject(pd.getReqData());
+ reqJson.put("page", 1);
+ reqJson.put("row", 10001);
+ apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeYearCollection/queryReportFeeYear" + mapToUrlParam(reqJson);
+ responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
+ if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
+ return null;
+ }
+ JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
+ if (!savedRoomInfoResults.containsKey("data")) {
+ return null;
+ }
return savedRoomInfoResults.getJSONArray("data");
}
@@ -180,22 +239,18 @@
row.createCell(3).setCellValue("璐圭敤寮�濮嬫椂闂�");
row.createCell(4).setCellValue("娆犺垂鏃堕暱锛堝ぉ锛�");
row.createCell(5).setCellValue("娆犺垂閲戦");
-
-
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportOweFeeDetail(pd, result);
JSONObject dataObj = null;
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(roomIndex + 1);
row.createCell(1).setCellValue(dataObj.getString("objName"));
row.createCell(2).setCellValue(dataObj.getString("feeName"));
row.createCell(3).setCellValue(dataObj.getString("feeCreateTime"));
row.createCell(4).setCellValue(dataObj.getString("oweDay"));
row.createCell(5).setCellValue(dataObj.getString("oweAmount"));
-
}
}
@@ -209,23 +264,19 @@
row.createCell(4).setCellValue("璐圭敤缁撴潫鏃堕棿");
row.createCell(5).setCellValue("搴旀敹閲戦");
row.createCell(6).setCellValue("瀹炴敹閲戦");
-
-
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportFeeDetail(pd, result);
JSONObject dataObj = null;
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(roomIndex + 1);
row.createCell(1).setCellValue(dataObj.getString("objName"));
row.createCell(2).setCellValue(dataObj.getString("feeName"));
row.createCell(3).setCellValue(dataObj.getString("feeCreateTime"));
- row.createCell(4).setCellValue("-");
+ row.createCell(4).setCellValue(dataObj.getString("deadlineTime"));
row.createCell(5).setCellValue(dataObj.getString("receivableAmount"));
row.createCell(6).setCellValue(dataObj.getString("receivedAmount"));
-
}
}
@@ -234,19 +285,13 @@
ResponseEntity<String> responseEntity = null;
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryOweFeeDetail?communityId=" + result.getCommunityId() + "&page=1&row=10000";
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
-
-
return savedRoomInfoResults.getJSONArray("data");
}
@@ -255,19 +300,13 @@
ResponseEntity<String> responseEntity = null;
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryFeeDetail?communityId=" + result.getCommunityId() + "&page=1&row=10000";
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
-
-
return savedRoomInfoResults.getJSONArray("data");
}
@@ -275,49 +314,40 @@
Sheet sheet = workbook.createSheet("璐圭敤鍒嗛」琛�");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("璐圭敤缂栧彿");
- row.createCell(1).setCellValue("璐圭敤椤�");
- row.createCell(2).setCellValue("璐圭敤寮�濮嬫椂闂�");
- row.createCell(3).setCellValue("搴旀敹閲戦");
- row.createCell(4).setCellValue("瀹炴敹閲戦");
- row.createCell(5).setCellValue("娆犺垂閲戦");
-
-
+ row.createCell(1).setCellValue("璐圭敤绫诲瀷");
+ row.createCell(2).setCellValue("璐圭敤椤�");
+ row.createCell(3).setCellValue("璐圭敤寮�濮嬫椂闂�");
+ row.createCell(4).setCellValue("搴旀敹閲戦");
+ row.createCell(5).setCellValue("瀹炴敹閲戦");
+ row.createCell(6).setCellValue("娆犺垂閲戦");
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportFeeBreakdown(pd, result);
JSONObject dataObj = null;
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(roomIndex + 1);
+ row.createCell(1).setCellValue(dataObj.getString("feeTypeCd"));
row.createCell(2).setCellValue(dataObj.getString("feeName"));
row.createCell(3).setCellValue(dataObj.getString("feeCreateTime"));
- row.createCell(1).setCellValue(dataObj.getString("receivableAmount"));
- row.createCell(4).setCellValue(dataObj.getString("receivedAmount"));
- row.createCell(5).setCellValue(dataObj.getString("oweAmount"));
-
+ row.createCell(4).setCellValue(dataObj.getString("receivableAmount"));
+ row.createCell(5).setCellValue(dataObj.getString("receivedAmount"));
+ row.createCell(6).setCellValue(dataObj.getString("oweAmount"));
}
}
private JSONArray getReportFeeBreakdown(IPageData pd, ComponentValidateResult result) {
-
String apiUrl = "";
ResponseEntity<String> responseEntity = null;
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryFeeBreakdown?communityId=" + result.getCommunityId() + "&page=1&row=10000";
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
-
-
return savedRoomInfoResults.getJSONArray("data");
}
@@ -330,22 +360,18 @@
row.createCell(3).setCellValue("搴旀敹閲戦");
row.createCell(4).setCellValue("瀹炴敹閲戦");
row.createCell(5).setCellValue("娆犺垂閲戦");
-
-
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportFloorUnitFeeSummary(pd, result);
JSONObject dataObj = null;
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(dataObj.getString("feeYear") + "骞�" + dataObj.getString("feeMonth") + "鏈�");
- row.createCell(1).setCellValue(dataObj.getString("receivableAmount"));
- row.createCell(2).setCellValue(dataObj.getString("floorNum") + "鍙锋ゼ");
- row.createCell(3).setCellValue(dataObj.getString("unitNum") + "鍗曞厓");
+ row.createCell(1).setCellValue(dataObj.getString("floorNum") + "鍙锋ゼ");
+ row.createCell(2).setCellValue(dataObj.getString("unitNum") + "鍗曞厓");
+ row.createCell(3).setCellValue(dataObj.getString("receivableAmount"));
row.createCell(4).setCellValue(dataObj.getString("receivedAmount"));
row.createCell(5).setCellValue(dataObj.getString("oweAmount"));
-
}
}
@@ -354,23 +380,15 @@
ResponseEntity<String> responseEntity = null;
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryFloorUnitFeeSummary?communityId=" + result.getCommunityId() + "&page=1&row=10000";
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
-
-
return savedRoomInfoResults.getJSONArray("data");
-
}
-
/**
* 鏌ヨ瀛樺湪鐨勬埧灞嬩俊鎭�
@@ -385,21 +403,14 @@
ResponseEntity<String> responseEntity = null;
apiUrl = ServiceConstant.SERVICE_API_URL + "/api/reportFeeMonthStatistics/queryReportFeeSummary?communityId=" + result.getCommunityId() + "&page=1&row=10000";
responseEntity = this.callCenterService(restTemplate, pd, "", apiUrl, HttpMethod.GET);
-
if (responseEntity.getStatusCode() != HttpStatus.OK) { //璺宠繃 淇濆瓨鍗曞厓淇℃伅
return null;
}
-
JSONObject savedRoomInfoResults = JSONObject.parseObject(responseEntity.getBody(), Feature.OrderedField);
-
-
if (!savedRoomInfoResults.containsKey("data")) {
return null;
}
-
-
return savedRoomInfoResults.getJSONArray("data");
-
}
/**
@@ -415,20 +426,16 @@
row.createCell(1).setCellValue("搴旀敹閲戦");
row.createCell(2).setCellValue("瀹炴敹閲戦");
row.createCell(3).setCellValue("娆犺垂閲戦");
-
-
//鏌ヨ妤兼爧淇℃伅
JSONArray rooms = this.getReportFeeSummaryFee(pd, componentValidateResult);
JSONObject dataObj = null;
for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
row = sheet.createRow(roomIndex + 1);
dataObj = rooms.getJSONObject(roomIndex);
-
row.createCell(0).setCellValue(dataObj.getString("feeYear") + "骞�" + dataObj.getString("feeMonth") + "鏈�");
row.createCell(1).setCellValue(dataObj.getString("receivableAmount"));
row.createCell(2).setCellValue(dataObj.getString("receivedAmount"));
row.createCell(3).setCellValue(dataObj.getString("oweAmount"));
-
}
}
--
Gitblit v1.8.0