package com.gs.xky.service.Impl;
|
|
import cn.hutool.core.io.FileUtil;
|
import cn.hutool.poi.excel.ExcelUtil;
|
import cn.hutool.poi.excel.ExcelWriter;
|
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
|
import com.gs.xky.entity.VwCjScSjTsBb;
|
import com.gs.xky.mapper.VwCjScSjTsBbMapper;
|
import com.gs.xky.service.DingtalkInfoService;
|
import com.gs.xky.service.VwCjScSjTsBbService;
|
import lombok.RequiredArgsConstructor;
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.stereotype.Service;
|
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDate;
|
import java.time.format.DateTimeFormatter;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
/**
|
* @author Administrator
|
* @description 针对表【VW_CJ_SC_SJ_TS_BB】的数据库操作Service实现
|
* @createDate 2025-11-12 16:42:06
|
*/
|
@Service
|
@Slf4j
|
@RequiredArgsConstructor
|
public class VwCjScSjTsBbServiceImpl extends ServiceImpl<VwCjScSjTsBbMapper, VwCjScSjTsBb>
|
implements VwCjScSjTsBbService {
|
|
private final DingtalkInfoService dingtalkInfoService;
|
|
@Override
|
public boolean exportAndSendToDingtalk() throws Exception {
|
String exportFilePath = null;
|
try {
|
// 1. 查询所有数据
|
log.info("开始查询生产数据...");
|
List<VwCjScSjTsBb> dataList = list();
|
|
if (dataList == null || dataList.isEmpty()) {
|
log.warn("没有数据需要导出");
|
return false;
|
}
|
|
log.info("查询到 {} 条数据", dataList.size());
|
|
// 2. 准备导出文件路径
|
String timestamp = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
|
String fileName = "生产数据报表_" + timestamp + ".xlsx";
|
exportFilePath = "D:\\BIFile\\" + fileName;
|
|
// 确保目录存在
|
FileUtil.mkdir("D:\\BIFile");
|
|
// 3. 导出到Excel
|
log.info("开始导出Excel文件: {}", exportFilePath);
|
exportToExcel(dataList, exportFilePath);
|
log.info("Excel文件导出成功");
|
|
// 4. 发送钉钉消息
|
log.info("开始发送钉钉文件消息...");
|
boolean sendResult = dingtalkInfoService.sendFileMessage(exportFilePath);
|
|
if (sendResult) {
|
log.info("钉钉文件消息发送成功");
|
} else {
|
log.warn("钉钉文件消息发送失败");
|
}
|
|
return sendResult;
|
|
} catch (Exception e) {
|
log.error("导出并发送失败", e);
|
throw e;
|
} finally {
|
// 可选：发送后删除临时文件
|
// if (exportFilePath != null && FileUtil.exist(exportFilePath)) {
|
// FileUtil.del(exportFilePath);
|
// log.info("临时文件已删除: {}", exportFilePath);
|
// }
|
}
|
}
|
|
/**
|
* 导出数据到Excel（两个sheet页）
|
*
|
* @param dataList 数据列表
|
* @param filePath 文件路径
|
*/
|
private void exportToExcel(List<VwCjScSjTsBb> dataList, String filePath) {
|
// 计算昨天的日期
|
LocalDate yesterday = LocalDate.now().minusDays(1);
|
String yesterdayStr = yesterday.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
|
|
// 计算本月的起止日期
|
LocalDate today = LocalDate.now();
|
LocalDate firstDayOfMonth = today.withDayOfMonth(1);
|
LocalDate lastDayOfMonth = today.withDayOfMonth(today.lengthOfMonth());
|
String firstDayStr = firstDayOfMonth.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
|
String lastDayStr = lastDayOfMonth.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
|
|
log.info("昨天日期: {}", yesterdayStr);
|
log.info("本月范围: {} 至 {}", firstDayStr, lastDayStr);
|
|
// 过滤数据：预计开工在昨天的数据
|
List<VwCjScSjTsBb> yesterdayData = dataList.stream()
|
.filter(item -> item.getYjkg() != null && item.getYjkg().startsWith(yesterdayStr))
|
.collect(Collectors.toList());
|
|
// 过滤数据：预计开工在本月的数据
|
List<VwCjScSjTsBb> thisMonthData = dataList.stream()
|
.filter(item -> {
|
if (item.getYjkg() == null || item.getYjkg().trim().isEmpty()) {
|
return false;
|
}
|
String yjkg = item.getYjkg().substring(0, Math.min(10, item.getYjkg().length()));
|
return yjkg.compareTo(firstDayStr) >= 0 && yjkg.compareTo(lastDayStr) <= 0;
|
})
|
.collect(Collectors.toList());
|
|
log.info("昨天数据: {} 条", yesterdayData.size());
|
log.info("本月数据: {} 条", thisMonthData.size());
|
|
// 创建Excel写入器（第一个sheet）
|
ExcelWriter writer = ExcelUtil.getWriter(filePath, "昨天预计开工");
|
|
// 写入第一个sheet：昨天预计开工的数据
|
writeSheetData(writer, yesterdayData, "昨天预计开工数据（" + yesterdayStr + "）");
|
|
// 创建第二个sheet
|
writer.setSheet("本月预计开工");
|
|
// 写入第二个sheet：本月预计开工的数据
|
writeSheetData(writer, thisMonthData, "本月预计开工数据（" + firstDayStr + " 至 " + lastDayStr + "）");
|
|
// 关闭writer，释放内存
|
writer.close();
|
}
|
|
/**
|
* 写入单个sheet的数据
|
*
|
* @param writer Excel写入器
|
* @param dataList 数据列表
|
* @param title 标题
|
*/
|
private void writeSheetData(ExcelWriter writer, List<VwCjScSjTsBb> dataList, String title) {
|
// 清空之前的别名设置
|
writer.clearHeaderAlias();
|
|
// 设置表头别名（中文列名）
|
writer.addHeaderAlias("daa001", "工单号");
|
writer.addHeaderAlias("itemNo", "物料编码");
|
writer.addHeaderAlias("itemName", "物料名称");
|
writer.addHeaderAlias("departmentname", "车间名称");
|
writer.addHeaderAlias("lineName", "线体名称");
|
writer.addHeaderAlias("departmentcode", "车间编码");
|
writer.addHeaderAlias("lineNo", "线体编码");
|
writer.addHeaderAlias("daa008", "工单数量");
|
writer.addHeaderAlias("yjkg", "预计开工");
|
writer.addHeaderAlias("sjkg", "实际开工");
|
writer.addHeaderAlias("sq", "申请入库数");
|
writer.addHeaderAlias("rk", "入库数");
|
writer.addHeaderAlias("sqwwg", "申请未完工数");
|
writer.addHeaderAlias("rkwwg", "入库未完工");
|
writer.addHeaderAlias("sqwrk", "申请未入库");
|
|
// 合并单元格作为标题行
|
writer.merge(14, title);
|
|
// 写入数据，默认会使用别名作为表头
|
writer.write(dataList, true);
|
|
// 设置列宽自适应
|
writer.autoSizeColumnAll();
|
}
|
}
|
