src/main/java/com/ruoyi/device/init/MaintenanceTaskRegistrar.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,48 @@
package com.ruoyi.device.init;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruoyi.device.mapper.MaintenanceTaskMapper;
import com.ruoyi.device.pojo.MaintenanceTask;
import com.ruoyi.device.service.impl.MaintenanceTaskScheduler;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * å¯åŠ¨æ—¶æŠŠå·²å¯ç”¨çš„保养定时任务注册到调度器，保证历史数据（直接写库、从未经过 service）也能被执行
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class MaintenanceTaskRegistrar implements ApplicationRunner {

    private final MaintenanceTaskMapper maintenanceTaskMapper;
    private final MaintenanceTaskScheduler maintenanceTaskScheduler;

    @Override
    public void run(ApplicationArguments args) {
        try {
            List<MaintenanceTask> tasks = maintenanceTaskMapper.selectList(
                    new QueryWrapper<MaintenanceTask>().eq("deleted", 0).eq("is_active", 1));
            int registered = 0;
            for (MaintenanceTask task : tasks) {
                try {
                    if (maintenanceTaskScheduler.isRegistered(task.getId())) {
                        continue;
                    }
                    maintenanceTaskScheduler.scheduleMaintenanceTask(task);
                    registered++;
                } catch (Exception e) {
                    log.warn("保养定时任务注册失败 id={} name={}", task.getId(), task.getTaskName(), e);
                }
            }
            log.info("保养定时任务注册完成：待注册 {} æ¡ï¼Œæœ¬æ¬¡æ–°æ³¨å†Œ {} æ¡", tasks.size(), registered);
        } catch (Exception e) {
            log.error("保养定时任务启动注册失败", e);
        }
    }
}

 src/main/java/com/ruoyi/device/service/impl/MaintenanceTaskScheduler.java

@@ -6,6 +6,7 @@
import org.quartz.*;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeParseException;
@@ -31,6 +32,10 @@
        try {
            JobDetail jobDetail = buildJobDetail(task);
            Trigger trigger = buildJobTrigger(task, jobDetail);
            if (scheduler.checkExists(jobDetail.getKey())) {
                // æŒä¹…化的Job可能还在（例如历史数据直接写入、上次未清理），先移除旧的Job及其触发器
                scheduler.deleteJob(jobDetail.getKey());
            }
            scheduler.scheduleJob(jobDetail, trigger);
        }catch (SchedulerException e){
            log.error("SchedulerException scheduleMaintenanceTask ERROR",e);
@@ -45,30 +50,49 @@
       try{
           TriggerKey triggerKey = new TriggerKey("triggerMaintenanceTask_" + task.getId());

           // èŽ·å–现有触发器并转换为 CronTrigger
           // èŽ·å–现有触发器
           Trigger oldTrigger = scheduler.getTrigger(triggerKey);
           if (!(oldTrigger instanceof CronTrigger)) {
               throw new SchedulerException("Existing trigger is not a CronTrigger");
               // è§¦å‘器不存在或类型不符（如任务由直接写库创建），重新注册而不是报错
               scheduleMaintenanceTask(task);
               return;
           }

           // 3. æž„建CronTrigger，确保持久化配置
           // æž„建CronTrigger，确保持久化配置
           CronTrigger newTrigger = TriggerBuilder.newTrigger()
                   .withIdentity(triggerKey)                // å”¯ä¸€æ ‡è¯†ï¼Œç”¨äºŽæŒä¹…化存储
                   .withDescription(task.getTaskName() + "_TRIGGER") // è§¦å‘器描述
                   .forJob(oldTrigger.getJobKey())                       // å…³è”对应的Job
                   .withSchedule(CronScheduleBuilder
                           .cronSchedule(convertToCronExpression(task)) // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                           .cronSchedule(convertToCronExpression(task))
                           .withMisfireHandlingInstructionDoNothing() // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                   )
                   // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull则立即生效）
                   .startAt(task.getNextExecutionTime() != null
                           ? Date.from(task.getNextExecutionTime().atZone(ZoneId.systemDefault()).toInstant())
                           : new Date())
                   // è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull或已过期则立即生效）
                   .startAt(resolveStartTime(task))
                   .build();
           scheduler.rescheduleJob(triggerKey, newTrigger);
       }catch (SchedulerException e){
           log.error("SchedulerException rescheduleMaintenanceTask ERROR",e);
           throw new RuntimeException(e);
       }
    }

    /**
     * åˆ¤æ–­ä»»åŠ¡æ˜¯å¦å·²æ³¨å†Œåˆ°è°ƒåº¦å™¨
     */
    public boolean isRegistered(Long taskId) throws SchedulerException {
        return scheduler.checkExists(new TriggerKey("triggerMaintenanceTask_" + taskId));
    }

    /**
     * è§¦å‘器开始时间：为空或已过期时从当前时间起算，避免补跑历史
     */
    private Date resolveStartTime(MaintenanceTask task) {
        LocalDateTime nextExecutionTime = task.getNextExecutionTime();
        if (nextExecutionTime == null || !nextExecutionTime.isAfter(LocalDateTime.now())) {
            return new Date();
        }
        return Date.from(nextExecutionTime.atZone(ZoneId.systemDefault()).toInstant());
    }

    /**
@@ -137,10 +161,8 @@
                        .cronSchedule(cronExpression)
                        .withMisfireHandlingInstructionDoNothing() // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                )
                // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull则立即生效）
                .startAt(task.getNextExecutionTime() != null
                        ? Date.from(task.getNextExecutionTime().atZone(ZoneId.systemDefault()).toInstant())
                        : new Date())
                // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull或已过期则立即生效）
                .startAt(resolveStartTime(task))
                .build();
    }
    private String convertToCronExpression(MaintenanceTask task) {

 src/main/java/com/ruoyi/inspectiontask/init/TimingTaskRegistrar.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,48 @@
package com.ruoyi.inspectiontask.init;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruoyi.inspectiontask.mapper.TimingTaskMapper;
import com.ruoyi.inspectiontask.pojo.TimingTask;
import com.ruoyi.inspectiontask.service.impl.TimingTaskScheduler;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * å¯åŠ¨æ—¶æŠŠå·²å¯ç”¨çš„巡检定时任务注册到调度器，保证历史数据（直接写库、从未经过 service）也能被执行
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class TimingTaskRegistrar implements ApplicationRunner {

    private final TimingTaskMapper timingTaskMapper;
    private final TimingTaskScheduler timingTaskScheduler;

    @Override
    public void run(ApplicationArguments args) {
        try {
            List<TimingTask> tasks = timingTaskMapper.selectList(
                    new QueryWrapper<TimingTask>().eq("deleted", 0).eq("is_enabled", 1));
            int registered = 0;
            for (TimingTask task : tasks) {
                try {
                    if (timingTaskScheduler.isRegistered(task.getId())) {
                        continue;
                    }
                    timingTaskScheduler.scheduleTimingTask(task);
                    registered++;
                } catch (Exception e) {
                    log.warn("巡检定时任务注册失败 id={} name={}", task.getId(), task.getTaskName(), e);
                }
            }
            log.info("巡检定时任务注册完成：待注册 {} æ¡ï¼Œæœ¬æ¬¡æ–°æ³¨å†Œ {} æ¡", tasks.size(), registered);
        } catch (Exception e) {
            log.error("巡检定时任务启动注册失败", e);
        }
    }
}

 src/main/java/com/ruoyi/inspectiontask/service/impl/TimingTaskScheduler.java

@@ -5,6 +5,7 @@
import org.quartz.*;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeParseException;
@@ -24,6 +25,10 @@
    public void scheduleTimingTask(TimingTask task) throws SchedulerException {
        JobDetail jobDetail = buildJobDetail(task);
        Trigger trigger = buildJobTrigger(task, jobDetail);
        if (scheduler.checkExists(jobDetail.getKey())) {
            // æŒä¹…化的Job可能还在（例如历史数据直接写入、上次未清理），先移除旧的Job及其触发器
            scheduler.deleteJob(jobDetail.getKey());
        }
        scheduler.scheduleJob(jobDetail, trigger);
    }

@@ -33,36 +38,46 @@
    public void rescheduleTimingTask(TimingTask task) throws SchedulerException {
        TriggerKey triggerKey = new TriggerKey("trigger_" + task.getId());

        // èŽ·å–现有触发器并转换为 CronTrigger
        // èŽ·å–现有触发器
        Trigger oldTrigger = scheduler.getTrigger(triggerKey);
        if (!(oldTrigger instanceof CronTrigger)) {
            throw new SchedulerException("Existing trigger is not a CronTrigger");
            // è§¦å‘器不存在或类型不符（如任务由直接写库创建），重新注册而不是报错
            scheduleTimingTask(task);
            return;
        }

        // 3. æž„建CronTrigger，确保持久化配置
        // æž„建CronTrigger，确保持久化配置
        CronTrigger newTrigger = TriggerBuilder.newTrigger()
                .withIdentity(triggerKey)                // å”¯ä¸€æ ‡è¯†ï¼Œç”¨äºŽæŒä¹…化存储
                .withDescription(task.getTaskName() + "_TRIGGER") // è§¦å‘器描述
                .forJob(oldTrigger.getJobKey())                       // å…³è”对应的Job
                .withSchedule(CronScheduleBuilder
                        .cronSchedule(convertToCronExpression(task)) // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                        .cronSchedule(convertToCronExpression(task))
                        .withMisfireHandlingInstructionDoNothing() // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                )
                // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull则立即生效）
                .startAt(task.getNextExecutionTime() != null
                        ? Date.from(task.getNextExecutionTime().atZone(ZoneId.systemDefault()).toInstant())
                        : new Date())
                // è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull或已过期则立即生效）
                .startAt(resolveStartTime(task))
                .build();

        // æž„建新触发器
//        Trigger newTrigger = TriggerBuilder.newTrigger()
//                .withIdentity(triggerKey)
//                .withDescription(task.getTaskName())
//                .withSchedule(CronScheduleBuilder.cronSchedule(convertToCronExpression(task)))
//                .startAt(Date.from(task.getNextExecutionTime().atZone(ZoneId.systemDefault()).toInstant()))
//                .forJob(oldTrigger.getJobKey())
//                .build();

        scheduler.rescheduleJob(triggerKey, newTrigger);
    }

    /**
     * åˆ¤æ–­ä»»åŠ¡æ˜¯å¦å·²æ³¨å†Œåˆ°è°ƒåº¦å™¨
     */
    public boolean isRegistered(Long taskId) throws SchedulerException {
        return scheduler.checkExists(new TriggerKey("trigger_" + taskId));
    }

    /**
     * è§¦å‘器开始时间：为空或已过期时从当前时间起算，避免补跑历史
     */
    private Date resolveStartTime(TimingTask task) {
        LocalDateTime nextExecutionTime = task.getNextExecutionTime();
        if (nextExecutionTime == null || !nextExecutionTime.isAfter(LocalDateTime.now())) {
            return new Date();
        }
        return Date.from(nextExecutionTime.atZone(ZoneId.systemDefault()).toInstant());
    }

    /**
@@ -130,10 +145,8 @@
                        .cronSchedule(cronExpression)
                        .withMisfireHandlingInstructionDoNothing() // é”™è¿‡æ‰§è¡Œæ—¶çš„策略（根据业务调整）
                )
                // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull则立即生效）
                .startAt(task.getNextExecutionTime() != null
                        ? Date.from(task.getNextExecutionTime().atZone(ZoneId.systemDefault()).toInstant())
                        : new Date())
                // 4. è®¾ç½®å¼€å§‹æ—¶é—´ï¼ˆè‹¥ä¸ºnull或已过期则立即生效）
                .startAt(resolveStartTime(task))
                .build();
    }
    private String convertToCronExpression(TimingTask task) {

 src/main/resources/mapper/basic/CustomerMapper.xml

@@ -139,15 +139,16 @@
        from (select IFNULL(project_id, -1) as project_id, sum(contract_amount) as contractAmounts from sales_ledger group by IFNULL(project_id, -1)) T1
        left join project_management_info p on T1.project_id = p.id
        left join (
            select
                IFNULL(sl.project_id, -1) as project_id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by IFNULL(sl.project_id, -1)
            select x.project_id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, IFNULL(sl.project_id, -1) as project_id
                from account_sales_collection ascc
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.project_id
        ) T2 on T1.project_id = T2.project_id
        left join (
            select
@@ -160,16 +161,17 @@
            group by IFNULL(sl.project_id, -1)
        ) T4 on T4.project_id = T1.project_id
        left join (
            select 
                IFNULL(sl.project_id, -1) as project_id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by IFNULL(sl.project_id, -1)
            select x.project_id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price, IFNULL(sl.project_id, -1) as project_id
                from account_sales_invoice asi
                left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
                left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                left join sales_ledger sl on s.sales_ledger_id = sl.id
                where asi.status=0
            ) x
            group by x.project_id
        ) T5 on T5.project_id = T1.project_id
        left join (
            select IFNULL(sl.project_id, -1) as project_id,
@@ -178,13 +180,16 @@
                   sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(T1_rcpt.receiptPaymentAmount, 0) &lt; sl.contract_amount then 1 else 0 end) as overdueCount
            from sales_ledger sl
            left join (
                select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
                from account_sales_collection ascc
                left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
                WHERE sor.record_type='13' and sor.approval_status=1
                group by sl_in.id
                select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
                from (
                    select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
                    from account_sales_collection ascc
                    join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                    join shipping_info s on sor.record_id = s.id
                    join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                    where sor.record_type='13' and sor.approval_status=1
                ) x
                group by x.ledger_id
            ) T1_rcpt on T1_rcpt.id = sl.id
            group by IFNULL(sl.project_id, -1)
        ) T6 on T6.project_id = T1.project_id
@@ -232,15 +237,17 @@
            group by customer_id, IFNULL(project_id, -1)
        ) T1
        left join (
            select
                sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by sl.customer_id, IFNULL(sl.project_id, -1)
            select x.customer_id, x.project_id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount,
                       sl.customer_id, IFNULL(sl.project_id, -1) as project_id
                from account_sales_collection ascc
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.customer_id, x.project_id
        ) T2 on T1.customer_id = T2.customer_id and T1.project_id = T2.project_id
        left join (
            select
@@ -253,16 +260,18 @@
            group by sl.customer_id, IFNULL(sl.project_id, -1)
        ) T4 on T4.customer_id = T1.customer_id and T4.project_id = T1.project_id
        left join (
            select 
                sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by sl.customer_id, IFNULL(sl.project_id, -1)
            select x.customer_id, x.project_id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price,
                       sl.customer_id, IFNULL(sl.project_id, -1) as project_id
                from account_sales_invoice asi
                left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
                left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                left join sales_ledger sl on s.sales_ledger_id = sl.id
                where asi.status=0
            ) x
            group by x.customer_id, x.project_id
        ) T5 on T5.customer_id = T1.customer_id and T5.project_id = T1.project_id
        left join (
            select sl.customer_id, IFNULL(sl.project_id, -1) as project_id,
@@ -270,13 +279,16 @@
                   sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(T1_rcpt.receiptPaymentAmount, 0) &lt; sl.contract_amount then 1 else 0 end) as overdueCount
            from sales_ledger sl
            left join (
                select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
                from account_sales_collection ascc
                left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
                WHERE sor.record_type='13' and sor.approval_status=1
                group by sl_in.id
                select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
                from (
                    select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
                    from account_sales_collection ascc
                    join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                    join shipping_info s on sor.record_id = s.id
                    join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                    where sor.record_type='13' and sor.approval_status=1
                ) x
                group by x.ledger_id
            ) T1_rcpt on T1_rcpt.id = sl.id
            group by sl.customer_id, IFNULL(sl.project_id, -1)
        ) T6 on T6.customer_id = T1.customer_id and T6.project_id = T1.project_id
@@ -312,16 +324,16 @@
               END AS isPaymentTimeout
        from sales_ledger sl
        left join (
            select
                sl.id,
                sum(ascc.collection_amount) as receiptPaymentAmount
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
            WHERE sor.record_type='13'
              and sor.approval_status=1
            group by sl.id
            select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, sl.id as ledger_id
                from account_sales_collection ascc
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl on s.sales_ledger_id = sl.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.ledger_id
        )T1 on T1.id = sl.id
        left join (
            select sl.id,
@@ -333,16 +345,17 @@
            group by sl.id
        )T3 on T3.id = sl.id
        left join (
            select 
                sl.id,
                sum(asi.tax_inclusive_price) as invoiceAmount
            from account_sales_invoice asi
            left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
            left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            left join sales_ledger sl on s.sales_ledger_id = sl.id
            where asi.status=0
            group by sl.id
            select x.ledger_id as id, sum(x.tax_inclusive_price) as invoiceAmount
            from (
                select distinct asi.id, asi.tax_inclusive_price, sl.id as ledger_id
                from account_sales_invoice asi
                left join account_invoice_application aia on asi.account_invoice_application_id = aia.id
                left join stock_out_record sor on FIND_IN_SET(sor.id, aia.stock_out_record_ids) > 0
                left join shipping_info s on sor.record_id = s.id
                left join sales_ledger sl on s.sales_ledger_id = sl.id
                where asi.status=0
            ) x
            group by x.ledger_id
        ) T5 on T5.id = sl.id
        where sl.customer_id = #{customerId}
        <if test="projectId!=null">
@@ -362,13 +375,16 @@
               IFNULL(sum(case when DATEDIFF(NOW(), sl.execution_date) > 30 and IFNULL(rcpt.receiptPaymentAmount, 0) &lt; sl.contract_amount then 1 else 0 end), 0) as totalOverdueCount
        from sales_ledger sl
        left join (
            select sl_in.id, sum(ascc.collection_amount) as receiptPaymentAmount
            from account_sales_collection ascc
            left join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
            left join shipping_info s on sor.record_id = s.id
            LEFT JOIN sales_ledger sl_in ON s.sales_ledger_id = sl_in.id
            WHERE sor.record_type='13' and sor.approval_status=1
            group by sl_in.id
            select x.ledger_id as id, sum(x.collection_amount) as receiptPaymentAmount
            from (
                select distinct ascc.id, ascc.collection_amount, sl_in.id as ledger_id
                from account_sales_collection ascc
                join stock_out_record sor on FIND_IN_SET(sor.id, ascc.stock_out_record_ids) > 0
                join shipping_info s on sor.record_id = s.id
                join sales_ledger sl_in on s.sales_ledger_id = sl_in.id
                where sor.record_type='13' and sor.approval_status=1
            ) x
            group by x.ledger_id
        ) rcpt on rcpt.id = sl.id
    </select>
</mapper>