From 601940ec9fc44c41a965c51b2cdcac728a05d61e Mon Sep 17 00:00:00 2001
From: liurunyu <lry9898@163.com>
Date: 星期二, 29 七月 2025 16:35:26 +0800
Subject: [PATCH] 优化SQL语句，以提高查询速度
---
pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java | 287 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 283 insertions(+), 4 deletions(-)
diff --git a/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java b/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
index e780db2..489e3e6 100644
--- a/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
+++ b/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
@@ -4,8 +4,12 @@
import com.dy.common.webUtil.BaseResponse;
import com.dy.common.webUtil.BaseResponseUtils;
import com.dy.common.webUtil.QueryResultVo;
-import com.dy.pipIrrGlobal.voSt.VoClient;
-import com.dy.pipIrrStatistics.client.qo.OpenCountQO;
+import com.dy.pipIrrGlobal.voSt.*;
+import com.dy.pipIrrStatistics.card.IcCardqo.CommonQO;
+import com.dy.pipIrrStatistics.card.qo.CardUsageQO;
+import com.dy.pipIrrStatistics.client.qo.*;
+import com.dy.pipIrrStatistics.intake.qo.ClientAmountQO;
+import com.dy.pipIrrStatistics.result.StatisticlResultCode;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
@@ -14,6 +18,8 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
+import java.text.SimpleDateFormat;
+import java.util.Date;
import java.util.List;
import java.util.Objects;
@@ -42,8 +48,10 @@
if(bindingResult != null && bindingResult.hasErrors()){
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
-
try {
+ if(qo.getOpenCount() == null){
+ qo.setOpenCount(0);
+ }
return BaseResponseUtils.buildSuccess(clientSv.getLargeOpenCountClients(qo));
} catch (Exception e) {
log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
@@ -63,9 +71,280 @@
if(bindingResult != null && bindingResult.hasErrors()){
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
+ try {
+ if(qo.getOpenCount() == null){
+ qo.setOpenCount(0);
+ }
+ return BaseResponseUtils.buildSuccess(clientSv.getSmallOpenCountClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按閲忚秴杩囨寚瀹氬�肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getLargeWaterConsumptionClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getLargeWaterConsumptionClients(@Valid WaterConsumptionQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ if(qo.getWaterConsumption() == null){
+ qo.setWaterConsumption(0.0D);
+ }
+ return BaseResponseUtils.buildSuccess(clientSv.getLargeWaterConsumptionClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按閲忎綆浜庢寚瀹氬�肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getSmallWaterConsumptionClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getSmallWaterConsumptionClients(@Valid WaterConsumptionQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ if(qo.getWaterConsumption() == null){
+ qo.setWaterConsumption(0.0D);
+ }
+ return BaseResponseUtils.buildSuccess(clientSv.getSmallWaterConsumptionClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦瓒呰繃鎸囧畾鍊肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getLargeAmountSpentClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getLargeAmountSpentClients(@Valid AmountSpentQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getLargeAmountSpentClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦浣庝簬鎸囧畾鍊肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getSmallAmountSpentClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getSmallAmountSpentClients(@Valid AmountSpentQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getSmallAmountSpentClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱瓒呰繃鎸囧畾鍊肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getLargeWaterDurationClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getLargeWaterDurationClients(@Valid WaterDurationQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getLargeWaterDurationClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱浣庝簬鎸囧畾鍊肩殑鍐滄埛
+ * @param qo
+ * @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/getSmallWaterDurationClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getSmallWaterDurationClients(@Valid WaterDurationQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getSmallWaterDurationClients(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 缁熻鎸囧畾鏈堜唤鍚勫啘鎴峰悇澶╃敤姘撮噺鍙婃秷璐归噾棰�
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getDayAmountAndMoney")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoDayClient>>> getDayAmountAndMoney(ClientAmountQO qo) {
+ SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
+ String yearMonth = qo.getYearMonth();
+ if(yearMonth == null) {
+ return BaseResponseUtils.buildErrorMsg(StatisticlResultCode.YEAR_AND_MONTH_CANNOT_BE_NULL.getMessage());
+ }else {
+ yearMonth = yearMonth + "-01";
+ try {
+ Date date = dateFormat.parse(yearMonth);
+ } catch (Exception e) {
+ return BaseResponseUtils.buildErrorMsg(StatisticlResultCode.YEAR_OR_MONTH_IS_ERROR.getMessage());
+ }
+ }
try {
- return BaseResponseUtils.buildSuccess(clientSv.getSmallOpenCountClients(qo));
+ return BaseResponseUtils.buildSuccess(clientSv.getDayAmountAndMoney(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 缁熻鎸囧畾骞翠唤鍚勫啘鎴峰悇鏈堢敤姘撮噺鍙婃秷璐归噾棰�
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getMonthAmountAndMoney")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoMonthClient>>> getMonthAmountAndMoney(ClientAmountQO qo) {
+ if(qo.getYear() == null) {
+ return BaseResponseUtils.buildErrorMsg(StatisticlResultCode.YEAR_CANNOT_BE_NULL.getMessage());
+ }
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getMonthAmountAndMoney(qo));
+ } catch (Exception e) {
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage()) ;
+ }
+ }
+
+ /**
+ * 鎸囧畾鏃堕棿娈靛唴寮�鐗╃悊鍗″啘鎴�
+ *
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getOpenIcCardClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClientOpenCardCount>>> getOpenIcCardClients(CommonQO qo) {
+ try {
+ QueryResultVo<List<VoClientOpenCardCount>> res = clientSv.getOpenIcCardClients(qo);
+ return BaseResponseUtils.buildSuccess(res);
+ }catch (Exception e){
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage());
+ }
+ }
+
+ /**
+ * 鎸囧畾鏃堕棿娈靛唴姣忎釜鍐滄埛鍏呭�煎悎璁★紙鐗╃悊鍗★級
+ *
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getRechargeTotalClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClientRechargeTotal>>> getRechargeTotalClients(CommonQO qo) {
+ try {
+ QueryResultVo<List<VoClientRechargeTotal>> res = clientSv.getRechargeTotalClients(qo);
+ return BaseResponseUtils.buildSuccess(res);
+ }catch (Exception e){
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage());
+ }
+ }
+
+
+ /**
+ * 鎸囧畾鏃堕棿娈靛唴鐗╃悊鍗″厖鍊奸噾棰濊秴杩囨寚瀹氬�肩殑鍐滄埛
+ *
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getRechargeTotalGtValueClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClientRechargeTotal>>> getRechargeTotalGtValueClients(RechargeTotalGtValueQo qo) {
+ try {
+ QueryResultVo<List<VoClientRechargeTotal>> res = clientSv.getRechargeTotalGtValueClients(qo);
+ return BaseResponseUtils.buildSuccess(res);
+ }catch (Exception e){
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage());
+ }
+ }
+
+
+ /**
+ * 鏈�杩戞湭鍏呭�肩殑鍐滄埛
+ *
+ * @param qo
+ * @return
+ */
+ @GetMapping(path = "/getNotRechargeLastClients")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoClient>>> getNotRechargeLastClients(CommonQO qo) {
+ try {
+ QueryResultVo<List<VoClient>> res = clientSv.getNotRechargeLastClients(qo);
+ return BaseResponseUtils.buildSuccess(res);
+ }catch (Exception e){
+ log.error("鑾峰彇璁板綍寮傚父", e);
+ return BaseResponseUtils.buildException(e.getMessage());
+ }
+ }
+
+ /**
+ * 鑾峰彇鎸囧畾鏃堕棿娈靛啘鎴凤細鍏呭�煎悎璁°�佹秷璐瑰悎璁°�佷綑棰�
+ * @param qo
+ //* @param bindingResult
+ * @return
+ */
+ @GetMapping(path = "/client_usage")
+ @SsoAop()
+ public BaseResponse<QueryResultVo<List<VoCardUsage>>> getClientUsages(@Valid CardUsageQO qo, BindingResult bindingResult) {
+ if(bindingResult != null && bindingResult.hasErrors()){
+ return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
+ }
+
+ try {
+ return BaseResponseUtils.buildSuccess(clientSv.getClientUsages(qo));
} catch (Exception e) {
log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
return BaseResponseUtils.buildException(e.getMessage()) ;
--
Gitblit v1.8.0