优化SQL语句，以提高查询速度

liurunyu

2025-07-29 83c1bbca96beb5461fff56d861214444348e86d0

 pipIrr-platform/pipIrr-web/pipIrr-web-sell/src/main/java/com/dy/pipIrrSell/recharge/RechargeCtrl.java

New file
@@ -0,0 +1,195 @@
package com.dy.pipIrrSell.recharge;

import com.dy.common.aop.SsoAop;
import com.dy.common.webUtil.BaseResponse;
import com.dy.common.webUtil.BaseResponseUtils;
import com.dy.common.webUtil.QueryResultVo;
import com.dy.common.webUtil.ResultCodeMsg;
import com.dy.pipIrrGlobal.pojoSe.SeClientCard;
import com.dy.pipIrrGlobal.pojoSe.SeRecharge;
import com.dy.pipIrrGlobal.pojoSe.SeRechargeHistory;
import com.dy.pipIrrGlobal.voSe.VoRecharge;
import com.dy.pipIrrSell.clientCard.ClientCardSv;
import com.dy.pipIrrSell.clientCard.LastOperateENUM;
import com.dy.pipIrrSell.result.SellResultCode;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.media.Content;
import io.swagger.v3.oas.annotations.media.Schema;
import io.swagger.v3.oas.annotations.responses.ApiResponse;
import io.swagger.v3.oas.annotations.responses.ApiResponses;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.MediaType;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;

import java.util.*;

@Slf4j
@Tag(name = "充值管理", description = "充值操作")
@RestController
@RequestMapping(path="recharge")
@RequiredArgsConstructor
public class RechargeCtrl {
    private final RechargeSv rechargeSv;
    private final ClientCardSv clientCardSv;

    @Operation(summary = "获得一页充值记录", description = "返回一页充值数据")
    @ApiResponses(value = {
            @ApiResponse(
                    responseCode = ResultCodeMsg.RsCode.SUCCESS_CODE,
                    description = "返回一页充值数据（BaseResponse.content:QueryResultVo[{}]）",
                    content = {@Content(mediaType = MediaType.APPLICATION_JSON_VALUE,
                            schema = @Schema(implementation = VoRecharge.class))}
            )
    })
    //@GetMapping(path = "/getRecharges", consumes = MediaType.APPLICATION_JSON_VALUE)
    @GetMapping(path = "/getRecharges")
    @SsoAop()
    public BaseResponse<QueryResultVo<List<VoRecharge>>> get(QueryVo vo){
        try {
            QueryResultVo<List<VoRecharge>> res = rechargeSv.getRecharges(vo);
            return BaseResponseUtils.buildSuccess(res);
        } catch (Exception e) {
            log.error("获取充值记录异常", e);
            return BaseResponseUtils.buildException(e.getMessage()) ;
        }
    }

    @Operation(summary = "充值", description = "充值")
    @ApiResponses(value = {
            @ApiResponse(
                    responseCode = ResultCodeMsg.RsCode.SUCCESS_CODE,
                    description = "操作结果：true：成功，false：失败（BaseResponse.content）",
                    content = {@Content(mediaType = MediaType.APPLICATION_JSON_VALUE,
                            schema = @Schema(implementation = Boolean.class))}
            )
    })
    @PostMapping(path = "add", consumes = MediaType.APPLICATION_JSON_VALUE)
    @Transactional(rollbackFor = Exception.class)
    @SsoAop()
    public BaseResponse<Boolean> add(@RequestBody @Valid DtoRecharge po, BindingResult bindingResult){
        if(bindingResult != null && bindingResult.hasErrors()){
            return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
        }
        return addRecharge(po);
    }

    /**
     * 添加充值记录
     * @param po
     * @return
     */
    public BaseResponse<Boolean> addRecharge(DtoRecharge po){
        /**
         * cardId           水卡编号（插入记录后生成）
         * cardAddr         水卡地址（仅仅写入，无业务）
         * clientNum        农户编号（非传入参数，由cardNum反查，添加充值使用）
         * money            卡片余额
         * amount           充值金额
         * gift             赠送金额
         * afterRecharge    充值后余额
         * paymentId        支付方式编号
         * price            水价
         * remarks          备注
         * operator         操作人编号
         * rechargeTime     充值时间
         */
        Long cardId = 0L;
        Long cardNum = po.getCardNum();
        String clientNum = "";
        //Long clientId = po.getClientId();
        Long clientId = 0L;
        Float money = po.getMoney();
        Float amount = po.getAmount();
        Float gift = po.getGift();
        Float afterRecharge = money + amount + gift;
        Long paymentId = po.getPaymentId();
        Float price = po.getPrice();
        String remarks = po.getRemarks();
        Long operator = po.getOperator();
        Date rechargeTime = new Date();

        /**
         * 依据水卡编号获取水卡表主键及农户编号
         */
        Map map = Optional.ofNullable(clientCardSv.getCardIdAndClientNum(cardNum)).orElse(new HashMap());
        if(map == null || map.size() <= 0) {
            return BaseResponseUtils.buildFail(SellResultCode.CARD_NUMBER_MISTAKE.getMessage());
        }
        cardId = Long.parseLong(map.get("cardId").toString());
        clientNum = map.get("clientNum").toString();
        clientId = Long.parseLong(map.get("clientId").toString());

        /**
         * 修改农户卡信息：
         *      补卡时间
         *      最后操作类型-2
         */
        SeClientCard seClientCard = new SeClientCard();
        seClientCard.setId(cardId);
        seClientCard.setMoney(afterRecharge);
        seClientCard.setRechargedt(rechargeTime);
        seClientCard.setLastoper(LastOperateENUM.RECHARGE.getCode ());
        Integer rec_updateClientCard = Optional.ofNullable(clientCardSv.UpdateClientCard(seClientCard)).orElse(0);
        if(rec_updateClientCard == 0) {
            return BaseResponseUtils.buildFail(SellResultCode.RECHARGE_FAIL_WRITE_CLIENT_CARD_ERROR.getMessage());
        }

        /**
         * 删除充值表该卡的充值记录
         */
        Integer rec_deleteRecharge = Optional.ofNullable(rechargeSv.deleteByCardId(cardId)).orElse(0);
        /**
         * 添加充值记录
         */
        SeRecharge seRecharge = new SeRecharge();
        //seRecharge.setCardnum(cardNum);
        //seRecharge.setClientnum(clientNum);

        seRecharge.setCardid(cardId);
        seRecharge.setClientid(clientId);
        seRecharge.setMoney(money);
        seRecharge.setAmount(amount);
        seRecharge.setGift(gift);
        seRecharge.setAfterrecharge(afterRecharge);
        seRecharge.setPaymentid(paymentId);
        seRecharge.setPrice(price);
        seRecharge.setRemarks(remarks);
        seRecharge.setOperator(operator);
        seRecharge.setOperatedt(rechargeTime);
        Long rec = Optional.ofNullable(rechargeSv.add(seRecharge)).orElse(0L);
        if(rec == 0) {
            return BaseResponseUtils.buildFail(SellResultCode.REPLACE_FAIL_WRITE_RECHARGE_ERROR.getMessage());
        }

        /**
         * 添加补卡历史记录
         */
        SeRechargeHistory seRechargeHistory = new SeRechargeHistory();
        //seRechargeHistory.setCardnum(cardNum);
        //seRechargeHistory.setClientnum(clientNum);
        seRechargeHistory.setCardid(cardId);
        seRechargeHistory.setClientid(clientId);
        seRechargeHistory.setMoney(money);
        seRechargeHistory.setAmount(amount);
        seRechargeHistory.setGift(gift);
        seRechargeHistory.setAfterrecharge(afterRecharge);
        seRechargeHistory.setPaymentid(paymentId);
        seRechargeHistory.setPrice(price);
        seRechargeHistory.setRemarks(remarks);
        seRechargeHistory.setOperator(operator);
        seRechargeHistory.setOperatedt(rechargeTime);

        Long rec_history = Optional.ofNullable(rechargeSv.addHistory(seRechargeHistory)).orElse(0L);
        if(rec_history == 0) {
            return BaseResponseUtils.buildFail(SellResultCode.RECHARGE_FAIL_WRITE_RECHARGE_HISTORY_ERROR.getMessage());
        }
        Map returnMap = new HashMap();
        returnMap.put("orderNumber",rec+"");//返回充值ID
        return BaseResponseUtils.buildSuccess(returnMap) ;
    }
}