查询焊缝统计表V2列表、修改焊缝统计表V2、刷新焊缝统计表V2数据

hongjli

2025-05-20 5e3d812e9b9ebf21d9373ae40617f2499a9368e2

查询焊缝统计表V2列表、修改焊缝统计表V2、刷新焊缝统计表V2数据

 aps-modules/aps-core/src/main/java/com/aps/core/controller/mainPlan/ApsWeldSeamStatisticsV2Controller.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,81 @@
package com.aps.core.controller.mainPlan;

import com.aps.common.core.utils.poi.ExcelUtil;
import com.aps.common.core.web.controller.BaseController;
import com.aps.common.core.web.domain.AjaxResult;
import com.aps.common.core.web.page.TableDataInfo;
import com.aps.common.log.annotation.Log;
import com.aps.common.log.enums.BusinessType;
import com.aps.common.security.annotation.RequiresPermissions;
import com.aps.common.security.utils.SecurityUtils;
import com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2;
import com.aps.core.service.mainPlan.IApsWeldSeamStatisticsV2Service;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * ç„Šç¼ç»Ÿè®¡v2Controller
 *
 * @author wwj
 * @date 2025-04-09
 */
@RestController
@RequestMapping("/weldSeamStatisticsV2")
public class ApsWeldSeamStatisticsV2Controller extends BaseController {

    @Autowired
    private IApsWeldSeamStatisticsV2Service apsWeldSeamStatisticsV2Service;

    /**
     * æŸ¥è¯¢ç„Šç¼ç»Ÿè®¡è¡¨V2列表
     */
    @RequiresPermissions("weldSeamStatistics:weldSeamStatisticsV2:list")
    @GetMapping("/list")
    public TableDataInfo list(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2)
    {
        // ä¸éœ€è¦åˆ†é¡µ
        // startPage(); 
        List<ApsWeldSeamStatisticsV2> list = apsWeldSeamStatisticsV2Service.selectApsWeldSeamStatisticsV2List(apsWeldSeamStatisticsV2);
        return getDataTable(list);
    }

    /**
     * å¯¼å‡ºç„Šç¼ç»Ÿè®¡è¡¨V2列表
     */
    @RequiresPermissions("weldSeamStatistics:weldSeamStatisticsV2:export")
    @Log(title = "焊缝统计表V2", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2)
    {
        List<ApsWeldSeamStatisticsV2> list = apsWeldSeamStatisticsV2Service.selectApsWeldSeamStatisticsV2List(apsWeldSeamStatisticsV2);
        ExcelUtil<ApsWeldSeamStatisticsV2> util = new ExcelUtil<ApsWeldSeamStatisticsV2>(ApsWeldSeamStatisticsV2.class);
        util.exportExcel(response, list, "焊缝统计表V2数据");
    }

    /**
     * ä¿®æ”¹ç„Šç¼ç»Ÿè®¡è¡¨V2
     */
    @RequiresPermissions("weldSeamStatistics:weldSeamStatisticsV2:edit")
    @Log(title = "焊缝统计表V2", businessType = BusinessType.UPDATE)
    @PostMapping("/update")
    public AjaxResult edit(@RequestBody ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2)
    {
        ApsWeldSeamStatisticsV2 updatedRecord = apsWeldSeamStatisticsV2Service.updateApsWeldSeamStatisticsV2(apsWeldSeamStatisticsV2);
        return AjaxResult.success("修改成功", updatedRecord);
    }

    /**
     * åˆ·æ–°ç„Šç¼ç»Ÿè®¡è¡¨V2数据
     */
    @RequiresPermissions("weldSeamStatistics:weldSeamStatisticsV2:refresh")
    @Log(title = "焊缝统计表V2", businessType = BusinessType.OTHER)
    @PostMapping("/refresh")
    public AjaxResult refreshStatistics() {
        String username = SecurityUtils.getUsername();
        int count = apsWeldSeamStatisticsV2Service.refreshWeldSeamStatistics(username);
        return success("刷新焊缝统计表成功，共更新 " + count + " æ¡è®°å½•");
    }
}

 aps-modules/aps-core/src/main/java/com/aps/core/domain/mainPlan/ApsWeldSeamStatisticsV2.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,74 @@
package com.aps.core.domain.mainPlan;

import com.aps.common.core.annotation.Excel;
import com.aps.common.core.web.domain.BaseEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.math.BigDecimal;

/**
 * ç„Šç¼ç»Ÿè®¡è¡¨V2对象 aps_weld_seam_statistics_v2
 *
 * @date 2023-08-01
 */
@Data
@EqualsAndHashCode(callSuper = true)
public class ApsWeldSeamStatisticsV2 extends BaseEntity {
    private static final long serialVersionUID = 1L;

    /** ID */
    private String id;

    /** å¹´ä»½ */
    @Excel(name = "年份")
    private Integer year;

    /** æœˆä»½ */
    @Excel(name = "月份")
    private Integer month;

    /** ç”Ÿäº§åŸºåœ° */
    @Excel(name = "生产基地")
    private String productionBase;

    /** ç®¡è·¯è®¢å•éœ€æ±‚ */
    @Excel(name = "管路订单需求")
    private BigDecimal pipingOrderRequirement;

    /** æ°”柜订单需求 */
    @Excel(name = "气柜订单需求")
    private BigDecimal gasOrderRequirement;

    /** ç®¡è·¯é¢„测需求 */
    @Excel(name = "管路预测需求")
    private BigDecimal pipingPredictionRequirement;

    /** æ°”柜预测需求 */
    @Excel(name = "气柜预测需求")
    private BigDecimal gasPredictionRequirement;

    /** é¢„留紧急订单产出 */
    @Excel(name = "预留紧急订单产出")
    private BigDecimal reserveEmergencyOrderOutput;

    /** åˆè®¡ */
    @Excel(name = "合计")
    private BigDecimal total;

    /** å¤©æ•° */
    @Excel(name = "天数")
    private BigDecimal days;

    /** éœ€æ±‚日焊缝 */
    @Excel(name = "需求日焊缝")
    private BigDecimal requirementDayWeldSeam;

    /** ç”Ÿäº§æ—¥ç„Šç¼ */
    @Excel(name = "生产日焊缝")
    private BigDecimal productionDayWeldSeam;

    /** æ˜¯å¦æ»¡è¶³ */
    @Excel(name = "是否满足")
    private String isSatisfy;
} 

 aps-modules/aps-core/src/main/java/com/aps/core/mapper/mainPlan/ApsWeldSeamStatisticsV2Mapper.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,84 @@
package com.aps.core.mapper.mainPlan;

import com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * ç„Šç¼ç»Ÿè®¡è¡¨V2Mapper接口
 *
 * @date 2023-08-01
 */
@Mapper
public interface ApsWeldSeamStatisticsV2Mapper {
    
    /**
     * æŸ¥è¯¢ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param id ç„Šç¼ç»Ÿè®¡è¡¨V2主键
     * @return ç„Šç¼ç»Ÿè®¡è¡¨V2
     */
    public ApsWeldSeamStatisticsV2 selectApsWeldSeamStatisticsV2ById(String id);

    /**
     * æŸ¥è¯¢ç„Šç¼ç»Ÿè®¡è¡¨V2列表
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return ç„Šç¼ç»Ÿè®¡è¡¨V2集合
     */
    public List<ApsWeldSeamStatisticsV2> selectApsWeldSeamStatisticsV2List(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2);
    
    /**
     * æ–°å¢žç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return ç»“æžœ
     */
    public int insertApsWeldSeamStatisticsV2(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2);
    
    /**
     * ä¿®æ”¹ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return ç»“æžœ
     */
    public int updateApsWeldSeamStatisticsV2(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2);
    
    /**
     * åˆ é™¤ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param id ç„Šç¼ç»Ÿè®¡è¡¨V2主键
     * @return ç»“æžœ
     */
    public int deleteApsWeldSeamStatisticsV2ById(String id);
    
    /**
     * æ‰¹é‡åˆ é™¤ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param ids éœ€è¦åˆ é™¤çš„数据主键集合
     * @return ç»“æžœ
     */
    public int deleteApsWeldSeamStatisticsV2ByIds(String[] ids);
    
    /**
     * æ¸…空统计表
     */
    public void truncateTable();
    
    /**
     * æ‰¹é‡æ’入数据
     *
     * @param list æ•°æ®åˆ—表
     * @return ç»“æžœ
     */
    public int batchInsert(@Param("list") List<ApsWeldSeamStatisticsV2> list);
    
    /**
     * æŸ¥è¯¢ç»Ÿè®¡æ•°æ®
     *
     * @return ç»Ÿè®¡æ•°æ®åˆ—表
     */
    public List<ApsWeldSeamStatisticsV2> queryWeldSeamStatistics();
} 

 aps-modules/aps-core/src/main/java/com/aps/core/service/mainPlan/IApsWeldSeamStatisticsV2Service.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,35 @@
package com.aps.core.service.mainPlan;

import com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2;
import java.util.List;

/**
 * ç„Šç¼ç»Ÿè®¡è¡¨V2服务接口
 *
 * @date 2023-08-01
 */
public interface IApsWeldSeamStatisticsV2Service {
    /**
     * æŸ¥è¯¢ç„Šç¼ç»Ÿè®¡è¡¨V2列表
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return ç„Šç¼ç»Ÿè®¡è¡¨V2集合
     */
    public List<ApsWeldSeamStatisticsV2> selectApsWeldSeamStatisticsV2List(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2);

    /**
     * ä¿®æ”¹ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return æ›´æ–°åŽçš„记录
     */
    public ApsWeldSeamStatisticsV2 updateApsWeldSeamStatisticsV2(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2);

    /**
     * åˆ·æ–°ç„Šç¼ç»Ÿè®¡è¡¨æ•°æ®
     *
     * @param username æ“ä½œç”¨æˆ·
     * @return å—影响的记录数
     */
    public int refreshWeldSeamStatistics(String username);
} 

 aps-modules/aps-core/src/main/java/com/aps/core/service/mainPlan/impl/ApsWeldSeamStatisticsV2ServiceImpl.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,206 @@
package com.aps.core.service.mainPlan.impl;

import com.aps.common.core.utils.DateUtils;
import com.aps.common.core.utils.uuid.IdUtils;
import com.aps.common.core.web.page.TableDataInfo;
import com.aps.common.security.utils.SecurityUtils;
import com.aps.core.controller.basicData.ApsGasPipelineCapacityPlanController;
import com.aps.core.domain.ApsGasPipelineCapacityPlan;
import com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2;
import com.aps.core.mapper.mainPlan.ApsWeldSeamStatisticsV2Mapper;
import com.aps.core.service.IApsGasPipelineCapacityPlanService;
import com.aps.core.service.mainPlan.IApsWeldSeamStatisticsV2Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * ç„Šç¼ç»Ÿè®¡è¡¨V2Service业务层处理
 *
 * @date 2023-08-01
 */
@Service
public class ApsWeldSeamStatisticsV2ServiceImpl implements IApsWeldSeamStatisticsV2Service {
    @Autowired
    private ApsWeldSeamStatisticsV2Mapper apsWeldSeamStatisticsV2Mapper;
    
    @Autowired
    private ApsGasPipelineCapacityPlanController apsGasPipelineCapacityPlanController;

    /**
     * æŸ¥è¯¢ç„Šç¼ç»Ÿè®¡è¡¨V2列表
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return ç„Šç¼ç»Ÿè®¡è¡¨V2
     */
    @Override
    public List<ApsWeldSeamStatisticsV2> selectApsWeldSeamStatisticsV2List(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2) {
        return apsWeldSeamStatisticsV2Mapper.selectApsWeldSeamStatisticsV2List(apsWeldSeamStatisticsV2);
    }

    /**
     * ä¿®æ”¹ç„Šç¼ç»Ÿè®¡è¡¨V2
     *
     * @param apsWeldSeamStatisticsV2 ç„Šç¼ç»Ÿè®¡è¡¨V2
     * @return æ›´æ–°åŽçš„记录
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ApsWeldSeamStatisticsV2 updateApsWeldSeamStatisticsV2(ApsWeldSeamStatisticsV2 apsWeldSeamStatisticsV2)
    {
        // è®¾ç½®æ›´æ–°æ—¶é—´
        apsWeldSeamStatisticsV2.setUpdateTime(DateUtils.getNowDate());
        apsWeldSeamStatisticsV2.setUpdateBy(SecurityUtils.getUsername());

        // 1. ä»Žæ•°æ®åº“获取原始记录，以保留其他字段不变
        ApsWeldSeamStatisticsV2 originalData = apsWeldSeamStatisticsV2Mapper.selectApsWeldSeamStatisticsV2ById(apsWeldSeamStatisticsV2.getId());
        if (originalData == null) {
            throw new RuntimeException("记录不存在，ID: " + apsWeldSeamStatisticsV2.getId());
        }

        // 2. åªæ›´æ–°reserveEmergencyOrderOutput和days两个字段
        originalData.setReserveEmergencyOrderOutput(apsWeldSeamStatisticsV2.getReserveEmergencyOrderOutput());
        originalData.setDays(apsWeldSeamStatisticsV2.getDays());
        originalData.setUpdateTime(apsWeldSeamStatisticsV2.getUpdateTime());
        originalData.setUpdateBy(apsWeldSeamStatisticsV2.getUpdateBy());
        
        // 3. ç¡®ä¿æ‰€æœ‰ç”¨äºŽè®¡ç®—çš„BigDecimal字段不为null
        java.math.BigDecimal pipingOrderReq = (originalData.getPipingOrderRequirement() != null) 
                ? originalData.getPipingOrderRequirement() : java.math.BigDecimal.ZERO;
        
        java.math.BigDecimal pipingPredictionReq = (originalData.getPipingPredictionRequirement() != null) 
                ? originalData.getPipingPredictionRequirement() : java.math.BigDecimal.ZERO;
        
        java.math.BigDecimal gasOrderReq = (originalData.getGasOrderRequirement() != null) 
                ? originalData.getGasOrderRequirement() : java.math.BigDecimal.ZERO;
        
        java.math.BigDecimal gasPredictionReq = (originalData.getGasPredictionRequirement() != null) 
                ? originalData.getGasPredictionRequirement() : java.math.BigDecimal.ZERO;
        
        java.math.BigDecimal reserveEmergencyOutput = (originalData.getReserveEmergencyOrderOutput() != null) 
                ? originalData.getReserveEmergencyOrderOutput() : java.math.BigDecimal.ZERO;
        
        // 4. å±žæ€§1：重新计算total（合计）
        java.math.BigDecimal total = pipingOrderReq
                .add(pipingPredictionReq)
                .add(gasOrderReq)
                .add(gasPredictionReq)
                .add(reserveEmergencyOutput);
        
        originalData.setTotal(total);
        
        // 5. å¦‚æžœdays不为空且大于0，重新计算requirementDayWeldSeam和isSatisfy
        if(originalData.getDays() != null && originalData.getDays().doubleValue() > 0) {
            // å±žæ€§2：计算需求日焊缝 = åˆè®¡/天数，四舍五入
            double result = total.doubleValue() / originalData.getDays().doubleValue();
            java.math.BigDecimal requirementDayWeldSeam = new java.math.BigDecimal(result).setScale(0, java.math.RoundingMode.HALF_UP);
            originalData.setRequirementDayWeldSeam(requirementDayWeldSeam);
            
            // å±žæ€§3：判断是否满足
            if(originalData.getProductionDayWeldSeam() != null) {
                if(originalData.getProductionDayWeldSeam().compareTo(requirementDayWeldSeam) >= 0) {
                    originalData.setIsSatisfy("1"); // æ»¡è¶³
                } else {
                    originalData.setIsSatisfy("0"); // ä¸æ»¡è¶³
                }
            } else {
                originalData.setIsSatisfy("0"); // é»˜è®¤ä¸æ»¡è¶³
            }
        } else {
            // å¦‚果天数为空或为0，设置为不满足
            originalData.setIsSatisfy("0");
        }
        
        // 6. æ›´æ–°æ•°æ®åº“
        apsWeldSeamStatisticsV2Mapper.updateApsWeldSeamStatisticsV2(originalData);
        
        // 7. è¿”回更新后的完整记录
        return originalData;
    }

    /**
     * åˆ·æ–°ç„Šç¼ç»Ÿè®¡è¡¨æ•°æ®
     *
     * @param username æ“ä½œç”¨æˆ·
     * @return å—影响的记录数
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int refreshWeldSeamStatistics(String username) {
        // 1. æ¸…空统计表
        apsWeldSeamStatisticsV2Mapper.truncateTable();
        
        // 2. èŽ·å–最新的统计数据
        List<ApsWeldSeamStatisticsV2> statisticsList = apsWeldSeamStatisticsV2Mapper.queryWeldSeamStatistics();
        
        // 3. è®¾ç½®åˆ›å»ºä¿¡æ¯å’ŒID，并获取生产日焊缝数据
        Date now = new Date();
        for (ApsWeldSeamStatisticsV2 statistics : statisticsList) {
            // ä½¿ç”¨UUID作为ID
            statistics.setId(IdUtils.fastUUID());
            statistics.setCreateBy(username);
            statistics.setCreateTime(now);
            
            // å…ˆè®¡ç®—需求日焊缝(requirementDayWeldSeam)
            if(statistics.getDays() != null && statistics.getDays().doubleValue() > 0 && statistics.getTotal() != null) {
                // è®¡ç®—需求日焊缝 = åˆè®¡/天数，四舍五入
                double result = statistics.getTotal().doubleValue() / statistics.getDays().doubleValue();
                BigDecimal requirementDayWeldSeam = new BigDecimal(result).setScale(0, java.math.RoundingMode.HALF_UP);
                statistics.setRequirementDayWeldSeam(requirementDayWeldSeam);
            } else if (statistics.getRequirementDayWeldSeam() == null) {
                // å¦‚果无法计算且之前没有值，设置默认值
                statistics.setRequirementDayWeldSeam(BigDecimal.ZERO);
            }
            
            // è®¾ç½®ç”Ÿäº§æ—¥ç„Šç¼(productionDayWeldSeam)
            // æ ¹æ®year、month、productionBase查询对应的生产日焊缝数据
            if (statistics.getYear() != null && statistics.getMonth() != null && statistics.getProductionBase() != null) {
                // åˆ›å»ºæŸ¥è¯¢æ¡ä»¶
                ApsGasPipelineCapacityPlan queryParam = new ApsGasPipelineCapacityPlan();
                queryParam.setYear(statistics.getYear().toString());
                queryParam.setMonth(statistics.getMonth().toString());
                queryParam.setOrgCode(statistics.getProductionBase());
                
                // ç›´æŽ¥è°ƒç”¨Controller的list方法获取数据
                TableDataInfo tableDataInfo = apsGasPipelineCapacityPlanController.list(queryParam);
                List<ApsGasPipelineCapacityPlan> capacityPlans = (List<ApsGasPipelineCapacityPlan>) tableDataInfo.getRows();
                
                // éåŽ†æ‰¾åˆ°processName为"气体管路轨道焊接组"的记录
                for (ApsGasPipelineCapacityPlan plan : capacityPlans) {
                    if ("气体管路轨道焊接组".equals(plan.getProcessName()) && plan.getDayProduceAllNum() != null) {
                        // è®¾ç½®ç”Ÿäº§æ—¥ç„Šç¼å€¼
                        statistics.setProductionDayWeldSeam(plan.getDayProduceAllNum());
                        break; // æ‰¾åˆ°åŒ¹é…è®°å½•åŽé€€å‡ºå¾ªçŽ¯
                    }
                }
            }
            
            // å¦‚果没有找到匹配的产能规划数据，设置默认状态
            if (statistics.getProductionDayWeldSeam() == null) {
                statistics.setProductionDayWeldSeam(BigDecimal.ZERO);
            }
            
            // æœ€åŽç»Ÿä¸€è®¡ç®—isSatisfy（是否满足）
            // å½“生产日焊缝 >= éœ€æ±‚日焊缝时为满足，否则为不满足
            if (statistics.getRequirementDayWeldSeam() != null && 
                statistics.getProductionDayWeldSeam() != null &&
                statistics.getProductionDayWeldSeam().compareTo(statistics.getRequirementDayWeldSeam()) >= 0) {
                statistics.setIsSatisfy("1"); // æ»¡è¶³
            } else {
                statistics.setIsSatisfy("0"); // ä¸æ»¡è¶³
            }
        }
        
        // 4. æ‰¹é‡æ’入数据
        int count = 0;
        if (!statisticsList.isEmpty()) {
            count = apsWeldSeamStatisticsV2Mapper.batchInsert(statisticsList);
        }
        
        return count;
    }
} 

 aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,247 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aps.core.mapper.mainPlan.ApsWeldSeamStatisticsV2Mapper">

    <resultMap type="com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2" id="ApsWeldSeamStatisticsV2Result">
        <id property="id" column="id"/>
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="productionBase" column="production_base"/>
        <result property="pipingOrderRequirement" column="piping_order_requirement"/>
        <result property="gasOrderRequirement" column="gas_order_requirement"/>
        <result property="pipingPredictionRequirement" column="piping_prediction_requirement"/>
        <result property="gasPredictionRequirement" column="gas_prediction_requirement"/>
        <result property="reserveEmergencyOrderOutput" column="reserve_emergency_order_output"/>
        <result property="total" column="total"/>
        <result property="days" column="days"/>
        <result property="requirementDayWeldSeam" column="requirement_day_weld_seam"/>
        <result property="productionDayWeldSeam" column="production_day_weld_seam"/>
        <result property="isSatisfy" column="is_satisfy"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectApsWeldSeamStatisticsV2Vo">
        select id, year, month, production_base, piping_order_requirement, gas_order_requirement, 
               piping_prediction_requirement, gas_prediction_requirement, reserve_emergency_order_output, 
               total, days, requirement_day_weld_seam, production_day_weld_seam, is_satisfy, 
               create_by, create_time, update_by, update_time
        from aps_weld_seam_statistics_v2
    </sql>

    <select id="selectApsWeldSeamStatisticsV2ById" parameterType="String" resultMap="ApsWeldSeamStatisticsV2Result">
        <include refid="selectApsWeldSeamStatisticsV2Vo"/>
        where id = #{id}
    </select>

    <select id="selectApsWeldSeamStatisticsV2List" parameterType="com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2" resultMap="ApsWeldSeamStatisticsV2Result">
        <include refid="selectApsWeldSeamStatisticsV2Vo"/>
        <where>  
            <if test="params.beginYear != null and params.beginYear != '' and params.endYear != null and params.endYear != ''"> and year between #{params.beginYear} and #{params.endYear}</if>
            <if test="params.beginMonth != null and params.beginMonth != '' and params.endMonth != null and params.endMonth != ''"> and month between #{params.beginMonth} and #{params.endMonth}</if>
            <if test="productionBase != null and productionBase != ''"> and production_base like '%'|| #{productionBase}|| '%'</if>
        </where>
        order by year, month, production_base
    </select>

    <insert id="insertApsWeldSeamStatisticsV2" parameterType="com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2">
        insert into aps_weld_seam_statistics_v2
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="year != null">year,</if>
            <if test="month != null">month,</if>
            <if test="productionBase != null">production_base,</if>
            <if test="pipingOrderRequirement != null">piping_order_requirement,</if>
            <if test="gasOrderRequirement != null">gas_order_requirement,</if>
            <if test="pipingPredictionRequirement != null">piping_prediction_requirement,</if>
            <if test="gasPredictionRequirement != null">gas_prediction_requirement,</if>
            <if test="reserveEmergencyOrderOutput != null">reserve_emergency_order_output,</if>
            <if test="total != null">total,</if>
            <if test="days != null">days,</if>
            <if test="requirementDayWeldSeam != null">requirement_day_weld_seam,</if>
            <if test="productionDayWeldSeam != null">production_day_weld_seam,</if>
            <if test="isSatisfy != null">is_satisfy,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="year != null">#{year},</if>
            <if test="month != null">#{month},</if>
            <if test="productionBase != null">#{productionBase},</if>
            <if test="pipingOrderRequirement != null">#{pipingOrderRequirement},</if>
            <if test="gasOrderRequirement != null">#{gasOrderRequirement},</if>
            <if test="pipingPredictionRequirement != null">#{pipingPredictionRequirement},</if>
            <if test="gasPredictionRequirement != null">#{gasPredictionRequirement},</if>
            <if test="reserveEmergencyOrderOutput != null">#{reserveEmergencyOrderOutput},</if>
            <if test="total != null">#{total},</if>
            <if test="days != null">#{days},</if>
            <if test="requirementDayWeldSeam != null">#{requirementDayWeldSeam},</if>
            <if test="productionDayWeldSeam != null">#{productionDayWeldSeam},</if>
            <if test="isSatisfy != null">#{isSatisfy},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateApsWeldSeamStatisticsV2" parameterType="com.aps.core.domain.mainPlan.ApsWeldSeamStatisticsV2">
        update aps_weld_seam_statistics_v2
        <trim prefix="SET" suffixOverrides=",">
            <if test="year != null">year = #{year},</if>
            <if test="month != null">month = #{month},</if>
            <if test="productionBase != null">production_base = #{productionBase},</if>
            <if test="pipingOrderRequirement != null">piping_order_requirement = #{pipingOrderRequirement},</if>
            <if test="gasOrderRequirement != null">gas_order_requirement = #{gasOrderRequirement},</if>
            <if test="pipingPredictionRequirement != null">piping_prediction_requirement = #{pipingPredictionRequirement},</if>
            <if test="gasPredictionRequirement != null">gas_prediction_requirement = #{gasPredictionRequirement},</if>
            <if test="reserveEmergencyOrderOutput != null">reserve_emergency_order_output = #{reserveEmergencyOrderOutput},</if>
            <if test="total != null">total = #{total},</if>
            <if test="days != null">days = #{days},</if>
            <if test="requirementDayWeldSeam != null">requirement_day_weld_seam = #{requirementDayWeldSeam},</if>
            <if test="productionDayWeldSeam != null">production_day_weld_seam = #{productionDayWeldSeam},</if>
            <if test="isSatisfy != null">is_satisfy = #{isSatisfy},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteApsWeldSeamStatisticsV2ById" parameterType="String">
        delete from aps_weld_seam_statistics_v2 where id = #{id}
    </delete>

    <delete id="deleteApsWeldSeamStatisticsV2ByIds" parameterType="String">
        delete from aps_weld_seam_statistics_v2 where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <update id="truncateTable">
        TRUNCATE TABLE aps_weld_seam_statistics_v2
    </update>

    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO aps_weld_seam_statistics_v2 (
            id, year, month, production_base, 
            piping_order_requirement, gas_order_requirement, 
            piping_prediction_requirement, gas_prediction_requirement,
            reserve_emergency_order_output, total, days, 
            requirement_day_weld_seam, production_day_weld_seam, 
            is_satisfy, create_by, create_time
        ) VALUES 
        <foreach collection="list" item="item" separator=",">
            (
                #{item.id}, #{item.year}, #{item.month}, #{item.productionBase},
                #{item.pipingOrderRequirement}, #{item.gasOrderRequirement},
                #{item.pipingPredictionRequirement}, #{item.gasPredictionRequirement},
                #{item.reserveEmergencyOrderOutput}, #{item.total}, #{item.days},
                #{item.requirementDayWeldSeam}, #{item.productionDayWeldSeam},
                #{item.isSatisfy}, #{item.createBy}, #{item.createTime}
            )
        </foreach>
    </insert>

    <select id="queryWeldSeamStatistics" resultMap="ApsWeldSeamStatisticsV2Result">
        WITH å·¥å•ç„Šç¼è®¡ç®— AS (
            -- è®¡ç®—工单的焊缝数量
            SELECT 
                mo.factory as ç”Ÿäº§åŸºåœ°,
                mo.material_code as æ–™å·,
                EXTRACT(YEAR FROM mo.plan_end) as å¹´ä»½,
                EXTRACT(MONTH FROM mo.plan_end) as æœˆä»½,
                mo.quantity * COALESCE(ws.hup_qty, 0) as å·¥å•ç„Šç¼æ•°,
                mgm.domain as ä¸“业
            FROM aps_gas_pipeline_mo mo
            LEFT JOIN aps_weld_seam_standard ws ON mo.material_code = ws.item_code AND ws.del_flag = 0
            LEFT JOIN aps_material_product_group_management mgm ON mo.material_code = mgm.material_code
        ),
        é¢„测焊缝计算 AS (
            -- è®¡ç®—预测的焊缝数量
            SELECT 
                gp.factory as ç”Ÿäº§åŸºåœ°,
                gp.material_code as æ–™å·,
                EXTRACT(YEAR FROM gp.predict_date) as å¹´ä»½,
                EXTRACT(MONTH FROM gp.predict_date) as æœˆä»½,
                gp.predict_quantity * COALESCE(ws.hup_qty, 0) as é¢„测焊缝数,
                mgm.domain as ä¸“业
            FROM aps_gas_pipeline_prediction gp
            LEFT JOIN aps_weld_seam_standard ws ON gp.material_code = ws.item_code AND ws.del_flag = 0
            LEFT JOIN aps_material_product_group_management mgm ON gp.material_code = mgm.material_code
        ),
        æ±‡æ€»æ•°æ® AS (
            SELECT 
                å¹´ä»½,
                æœˆä»½,
                ç”Ÿäº§åŸºåœ°,
                -- ç®¡è·¯è®¢å•éœ€æ±‚
                COALESCE(SUM(CASE 
                    WHEN ä¸“业 = 'GL' AND ç±»åž‹ = '工单' 
                    THEN ç„Šç¼æ•° ELSE 0 
                END), 0) as ç®¡è·¯è®¢å•éœ€æ±‚,
                -- æ°”柜订单需求
                COALESCE(SUM(CASE 
                    WHEN ä¸“业 = 'QG' AND ç±»åž‹ = '工单' 
                    THEN ç„Šç¼æ•° ELSE 0 
                END), 0) as æ°”柜订单需求,
                -- ç®¡è·¯é¢„测需求
                COALESCE(SUM(CASE 
                    WHEN ä¸“业 = 'GL' AND ç±»åž‹ = '预测' 
                    THEN ç„Šç¼æ•° ELSE 0 
                END), 0) as ç®¡è·¯é¢„测需求,
                -- æ°”柜预测需求
                COALESCE(SUM(CASE 
                    WHEN ä¸“业 = 'QG' AND ç±»åž‹ = '预测' 
                    THEN ç„Šç¼æ•° ELSE 0 
                END), 0) as æ°”柜预测需求
            FROM (
                SELECT 
                    å¹´ä»½,
                    æœˆä»½,
                    ç”Ÿäº§åŸºåœ°,
                    å·¥å•ç„Šç¼æ•° as ç„Šç¼æ•°,
                    '工单' as ç±»åž‹,
                    ä¸“业
                FROM å·¥å•ç„Šç¼è®¡ç®—
                UNION ALL
                SELECT 
                    å¹´ä»½,
                    æœˆä»½,
                    ç”Ÿäº§åŸºåœ°,
                    é¢„测焊缝数 as ç„Šç¼æ•°,
                    '预测' as ç±»åž‹,
                    ä¸“业
                FROM é¢„测焊缝计算
            ) åˆå¹¶æ•°æ®
            GROUP BY å¹´ä»½, æœˆä»½, ç”Ÿäº§åŸºåœ°
        )
        SELECT 
            s.年份 as "year",
            s.月份 as "month",
            s.生产基地 as "production_base",
            ROUND(s.管路订单需求, 4) as "piping_order_requirement",
            ROUND(s.气柜订单需求, 4) as "gas_order_requirement",
            ROUND(s.管路预测需求, 4) as "piping_prediction_requirement",
            ROUND(s.气柜预测需求, 4) as "gas_prediction_requirement",
            0 as "reserve_emergency_order_output",
            ROUND(s.管路订单需求 + s.气柜订单需求 + s.管路预测需求 + s.气柜预测需求, 4) as "total",
            0 as "days",
            0 as "requirement_day_weld_seam",
            0 as "production_day_weld_seam",
            '0' as "is_satisfy",
            'system' as "create_by",
            CURRENT_TIMESTAMP as "create_time"
        FROM æ±‡æ€»æ•°æ® s
        ORDER BY s.年份, s.月份, s.生产基地
    </select>
</mapper>