数据库
数据
INSERT INTO `test3`.`overdue_record`(`id`,`business_no`,`customer_id`,`customer_name`,`customer_email`,`customer_phone`,`due_date`,`overdue_amount`,`overdue_days`,`reminded_7_days`,`reminded_15_days`,`reminded_30_days`,`status`,`create_time`,`update_time`)VALUES(1,'ORD20240101001',1001,'张三','zhangsan@example.com', '13800138000', '2025-12-20',1000.00,8,0,0,0,0,'2025-12-2816:11:22', '2025-12-2817:27:55');INSERT INTO `test3`.`overdue_record`(`id`,`business_no`,`customer_id`,`customer_name`,`customer_email`,`customer_phone`,`due_date`,`overdue_amount`,`overdue_days`,`reminded_7_days`,`reminded_15_days`,`reminded_30_days`,`status`,`create_time`,`update_time`)VALUES(2,'ORD20240101002',1002,'李四','lisi@example.com', '13800138001', '2025-12-12',2000.00,16,0,0,0,0,'2025-12-2816:11:22', '2025-12-2817:27:55');INSERT INTO `test3`.`overdue_record`(`id`,`business_no`,`customer_id`,`customer_name`,`customer_email`,`customer_phone`,`due_date`,`overdue_amount`,`overdue_days`,`reminded_7_days`,`reminded_15_days`,`reminded_30_days`,`status`,`create_time`,`update_time`)VALUES(3,'ORD20240101003',1003,'王五','wangwu@example.com', '13800138002', '2025-11-27',3000.00,31,0,0,0,0,'2025-12-2816:11:22', '2025-12-2817:27:55');mapper
<update id="updateDueDate">UPDATE overdue_recordSEToverdue_days=DATEDIFF(CURDATE(),due_date),update_time=NOW()WHEREstatus=0</update><select id="overdueSeven"resultType="ho.entity.OverdueSevenRecord">SELECTid,business_no,customer_name,customer_email,customer_phone,due_date,overdue_amount,reminded_seven_days,reminded_fifteen_days,reminded_thirty_days,status FROM overdue_record WHERE//根据due_date查出逾期大于7天小于15天的数据DATEDIFF(CURDATE(),due_date)>=7ANDDATEDIFF(CURDATE(),due_date)<15ANDstatus=0ANDreminded_seven_days=0</select>service
调用接口推送数据
@Service@Slf4jpublicclassOverdueRecordServiceImplextendsServiceImpl<OverdueRecordMapper,OverdueRecord>implementsIOverdueRecordService{@ResourceprivateOverdueRecordMapperoverdueRecordMapper;@OverridepublicList<OverdueRecord>overDueSeven(){// 更新逾期天数overdueRecordMapper.updateDueDate();List<OverdueSevenRecord>overdueSevenRecords=overdueRecordMapper.overdueSeven();List<OverdueRecord>overdueRecords=overdueSevenRecords.stream().map(e->{OverdueRecordoverdueRecord=newOverdueRecord();BeanUtil.copyProperties(e,overdueRecord,false);returnoverdueRecord;}).collect(Collectors.toList());intsuccessCount=0;intfailCount=0;for(OverdueRecordoverdueRecord:overdueRecords){//try{// 发送提醒booleansent=this.sendReminder(overdueRecord,7);if(sent){// 标记已发送overdueRecord.setRemindedSevenDays(true);overdueRecordMapper.updateById(overdueRecord);successCount++;log.info("成功发送7天提醒，业务编号：{}",overdueRecord.getBusinessNo());}else{failCount++;log.warn("发送7天提醒失败，业务编号：{}",overdueRecord.getBusinessNo());}}catch(Exceptione){failCount++;log.error("发送7天提醒异常，业务编号：{}，错误：{}",overdueRecord.getBusinessNo(),e.getMessage(),e);}}returnnull;}privatebooleansendReminder(OverdueRecordoverdueRecord,intdays){//returntrue;}}上门面仅展示了逾期7天的,逾期15、30的也是一样的，根据数据库字段查数据，然后调用推送接口，要自动发送用@XxlJob(“xxx”)即可
逾期15的sql是
DATEDIFF(CURDATE(),due_date)>=15ANDDATEDIFF(CURDATE(),due_date)<30逾期30的sql是
DATEDIFF(CURDATE(),due_date)>=30
)
