优化SQL

czt

2025-06-12 930d29f39d115fe76c305af4320c2acbcb30c445

 igds-inout/src/main/java/com/ld/igds/inout/service/JInoutReportService.java

@@ -142,12 +142,12 @@
        sql = new StringBuffer();
        args = new HashMap<>();
        sql.append("SELECT ");
        sql.append("DEPOT_ID_, ");
        sql.append("SETTLE_WEIGHT_, ");
        sql.append("TYPE_, ");
        sql.append("CUR_STORAGE_, ");
        sql.append("FOOD_VARIETY_, ");
        sql.append("COMPLETE_TIME_ ");
        sql.append("a.DEPOT_ID_, ");
        sql.append("a.SETTLE_WEIGHT_, ");
        sql.append("a.TYPE_, ");
        sql.append("a.CUR_STORAGE_, ");
        sql.append("a.FOOD_VARIETY_, ");
        sql.append("a.COMPLETE_TIME_ ");
        sql.append("FROM ");
        sql.append("( ");
        sql.append("SELECT ");
@@ -192,12 +192,12 @@
        sql = new StringBuffer();
        args = new HashMap<>();
        sql.append("SELECT ");
        sql.append("DEPOT_ID_, ");
        sql.append("SETTLE_WEIGHT_, ");
        sql.append("TYPE_, ");
        sql.append("CUR_STORAGE_, ");
        sql.append("FOOD_VARIETY_, ");
        sql.append("COMPLETE_TIME_ ");
        sql.append("a.DEPOT_ID_, ");
        sql.append("a.SETTLE_WEIGHT_, ");
        sql.append("a.TYPE_, ");
        sql.append("a.CUR_STORAGE_, ");
        sql.append("a.FOOD_VARIETY_, ");
        sql.append("a.COMPLETE_TIME_ ");
        sql.append("FROM ");
        sql.append("( ");
        sql.append("SELECT ");
@@ -252,9 +252,9 @@
            // 设置序号
            d.setRemarks(String.valueOf(index));
            // 当前数据添加初期库存
            d = this.updateDepotReportCqkc(d, list1);
//         d = this.updateDepotReportQmkc(d, list2);

//            d = this.updateDepotReportCqkc(d, list1);
//         d = this.updateDepotReportQmkc(d, list1, list2);
            d.setQmkc(d.getInNum() - d.getOutNum());
            // 合计信息
            sum.setCqkc(sum.getCqkc() + d.getCqkc());
            sum.setInNum(sum.getInNum() + d.getInNum());
@@ -271,40 +271,34 @@
    }

    // 注意：如果期末库存没有临近数据，则表示获取最新的库存
    public ReportInoutDepotData updateDepotReportQmkc(ReportInoutDepotData d,
    public ReportInoutDepotData updateDepotReportQmkc(ReportInoutDepotData d,List<ReportInoutStorageData> list1,
                                                       List<ReportInoutStorageData> list2) {
        ReportInoutStorageData inoutStorageData = null;
        for (ReportInoutStorageData storageData : list2) {
            if (storageData.getDepotId().equals(d.getDepotId())) {
                inoutStorageData = storageData;
                break;
        ReportInoutStorageData inoutStorageData1 = null;
        ReportInoutStorageData inoutStorageData2 = null;
        if(null!= list1 && list1.size() > 0){
            for (ReportInoutStorageData storageData : list1) {
                if (storageData.getDepotId().equals(d.getDepotId())) {
                    inoutStorageData1 = storageData;
                    break;
                }
            }
        }
        if (null == inoutStorageData) {
            Depot depot = depotService.getDepotById(ContextUtil.getCompanyId(),
                    d.getDepotId());
            d.setQmkc(null == depot.getStorageReal() ? 0.0 : depot
                    .getStorageReal());
            return d;
        if(null!= list2 && list2.size() > 0){
            for (ReportInoutStorageData storageData : list2) {
                if (storageData.getDepotId().equals(d.getDepotId())) {
                    inoutStorageData2 = storageData;
                    break;
                }
            }
        }

        double cmkc = inoutStorageData.getCurStorage();
        if (InoutConstant.TYPE_IN.equals(inoutStorageData.getType())) {
            cmkc = inoutStorageData.getCurStorage()
                    + inoutStorageData.getSettleWeight();
        if (null == inoutStorageData1) {
            inoutStorageData1 = new ReportInoutStorageData();
        }
        if (InoutConstant.TYPE_OUT.equals(inoutStorageData.getType())) {
            cmkc = inoutStorageData.getCurStorage()
                    - inoutStorageData.getSettleWeight();
        if (null == inoutStorageData2) {
            inoutStorageData2 = new ReportInoutStorageData();
        }
        if (Constant.LOSS_OVER_OVER.equals(inoutStorageData.getType())) {
            cmkc = inoutStorageData.getCurStorage()
                    + inoutStorageData.getSettleWeight();
        }
        if (Constant.LOSS_OVER_LOSS.equals(inoutStorageData.getType())) {
            cmkc = inoutStorageData.getCurStorage()
                    - inoutStorageData.getSettleWeight();
        }
        double cmkc = inoutStorageData1.getSettleWeight() - inoutStorageData2.getSettleWeight();
        d.setQmkc(cmkc);

        return d;