package com.whyc.service;
|
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
|
import com.baomidou.mybatisplus.core.conditions.Wrapper;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
|
import com.github.pagehelper.PageHelper;
|
import com.github.pagehelper.PageInfo;
|
import com.whyc.dto.Response;
|
import com.whyc.mapper.DeviceSpareMapper;
|
import com.whyc.pojo.web_site.DeviceSpare;
|
import com.whyc.pojo.web_site.DeviceSpareLog;
|
import com.whyc.util.CommonUtil;
|
import com.whyc.util.ThreadLocalUtil;
|
import org.apache.commons.lang.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.File;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.net.URLEncoder;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.*;
|
|
@Service
|
public class DeviceSpareService {
|
|
@Resource
|
private DeviceSpareMapper mapper;
|
|
@Autowired
|
private DeviceSpareLogService deviceSpareLogService;
|
|
public Response<PageInfo<DeviceSpare>> getPage(Integer pageNum, Integer pageSize, String type, String name) {
|
PageHelper.startPage(pageNum, pageSize);
|
QueryWrapper<DeviceSpare> query = Wrappers.query();
|
query.eq(StringUtils.isNotBlank(type), "type", type);
|
query.eq(StringUtils.isNotBlank(name), "name", name);
|
List<DeviceSpare> deviceSpares = mapper.selectList(query);
|
return new Response<PageInfo<DeviceSpare>>().set(1, new PageInfo<>(deviceSpares));
|
}
|
|
public Response<List<DeviceSpare>> getList(String name) {
|
QueryWrapper<DeviceSpare> query = Wrappers.query();
|
query.eq(StringUtils.isNotBlank(name), "name", name);
|
return new Response<List<DeviceSpare>>().set(1, mapper.selectList(query));
|
}
|
|
@Transactional
|
public Response add(DeviceSpare spare, List<MultipartFile> file) throws IOException {
|
//对file进行处理,保存到文件夹中
|
//对存储路径进行定义
|
Date now = new Date();
|
String timeFormat = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM_DD_HH_MM_SS_UNION, now);
|
String dirMonth = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM, now);
|
String fileDirPath = CommonUtil.getRootFile() + "deviceSpare" + File.separator + dirMonth;
|
File fileDir = new File(fileDirPath);
|
//如果文件夹不存在则创建
|
if (!fileDir.exists()) {
|
fileDir.mkdirs();
|
}
|
StringBuilder pictureUrlSb = new StringBuilder();
|
if (file != null && file.size() > 0) {
|
for (int i = 0; i < file.size(); i++) {
|
MultipartFile multipartFile = file.get(i);
|
//String fileName = multipartFile.getOriginalFilename();
|
//将fileName中可能存在的,去掉
|
//fileName = fileName.replace(",","");
|
String filePath = fileDirPath + File.separator + spare.getName()+"_"+spare.getModel()+"_"+spare.getVersion() + "_" + (i+1) + "_"+ timeFormat+".png";
|
|
multipartFile.transferTo(new File(filePath));
|
String split = "pis_file"+File.separator+"deviceSpare";
|
String picUrl = File.separator + filePath.substring(filePath.indexOf(split));
|
if(i == file.size()-1) {
|
pictureUrlSb.append(picUrl);
|
}else {
|
pictureUrlSb.append(picUrl).append(",");
|
}
|
}
|
}
|
spare.setPictureUrl(pictureUrlSb.toString());
|
mapper.insert(spare);
|
|
//变更日志
|
DeviceSpareLog deviceSpareLog = new DeviceSpareLog();
|
deviceSpareLog.setDeviceSpareId(spare.getId());
|
deviceSpareLog.setCreateTime(now);
|
deviceSpareLog.setOperationType(1);
|
|
StringBuilder detailSb = new StringBuilder();
|
detailSb.append("新增物料类型,");
|
detailSb.append("物料名称:").append(spare.getName()).append(",");
|
detailSb.append("物料型号:").append(spare.getModel()).append(",");
|
detailSb.append("物料版本:").append(spare.getVersion()).append(",");
|
detailSb.append("物料品牌:").append(spare.getBrand()).append(",");
|
detailSb.append("物料数量:").append(spare.getQuantity()).append(",");
|
detailSb.append("物料类型:").append(spare.getType()).append(",");
|
detailSb.append("物料供应商:").append(spare.getSupplier());
|
deviceSpareLog.setOperationDetail(detailSb.toString());
|
|
deviceSpareLogService.add(deviceSpareLog);
|
|
|
|
return new Response().setII(1,"新增完成");
|
}
|
|
@Transactional
|
public Response update(DeviceSpare spare) {
|
//根据id查询数据库中的物料
|
DeviceSpare spareInDB = mapper.selectById(spare.getId());
|
//对比每个字段是否一样,不一样的记录下来
|
DeviceSpareLog deviceSpareLog = new DeviceSpareLog();
|
deviceSpareLog.setDeviceSpareId(spare.getId());
|
deviceSpareLog.setCreateTime(new Date());
|
deviceSpareLog.setOperationType(2);
|
|
StringBuilder detailSb = new StringBuilder();
|
detailSb.append("修改物料,");
|
if (!spareInDB.getName().equals(spare.getName())) {
|
detailSb.append("物料名称:").append(spareInDB.getName()).append("->").append(spare.getName()).append(",");
|
}
|
if (!spareInDB.getModel().equals(spare.getModel())) {
|
detailSb.append("物料型号:").append(spareInDB.getModel()).append("->").append(spare.getModel()).append(",");
|
}
|
if (!spareInDB.getVersion().equals(spare.getVersion())) {
|
detailSb.append("物料版本:").append(spareInDB.getVersion()).append("->").append(spare.getVersion()).append(",");
|
}
|
if (!spareInDB.getBrand().equals(spare.getBrand())) {
|
detailSb.append("物料品牌:").append(spareInDB.getBrand()).append("->").append(spare.getBrand()).append(",");
|
}
|
if (!spareInDB.getQuantity().equals(spare.getQuantity())) {
|
detailSb.append("物料数量:").append(spareInDB.getQuantity()).append("->").append(spare.getQuantity()).append(",");
|
}
|
if (!spareInDB.getType().equals(spare.getType())) {
|
detailSb.append("物料类型:").append(spareInDB.getType()).append("->").append(spare.getType()).append(",");
|
}
|
if (!spareInDB.getSupplier().equals(spare.getSupplier())) {
|
detailSb.append("物料供应商:").append(spareInDB.getSupplier()).append("->").append(spare.getSupplier()).append(",");
|
}
|
//detailSb去除最后一个逗号
|
detailSb.deleteCharAt(detailSb.length()-1);
|
deviceSpareLog.setOperationDetail(detailSb.toString());
|
deviceSpareLogService.add(deviceSpareLog);
|
|
mapper.updateById(spare);
|
return new Response().setII(1,"修改完成");
|
}
|
|
public Response delete(Integer id) {
|
mapper.deleteById(id);
|
return new Response().setII(1,"删除完成");
|
}
|
|
public Response<List<DeviceSpare>> getListByIds(String deviceSpareIds) {
|
QueryWrapper<DeviceSpare> query = Wrappers.query();
|
query.in("id",deviceSpareIds.split(","));
|
List<DeviceSpare> deviceSpares = mapper.selectList(query);
|
return new Response<List<DeviceSpare>>().set(1,deviceSpares);
|
}
|
|
public void addBatch(List<DeviceSpare> spareList) {
|
mapper.insertBatchSomeColumn(spareList);
|
}
|
|
public void addOrUpdate(List<DeviceSpare> spareList) {
|
//查询库中是否存在该设备,存在则增加数量.
|
for (DeviceSpare spare : spareList) {
|
QueryWrapper<DeviceSpare> query = Wrappers.query();
|
query.eq("name",spare.getName());
|
query.eq("model",spare.getModel());
|
query.eq("version",spare.getVersion());
|
query.eq("brand",spare.getBrand());
|
query.eq("type",spare.getType());
|
query.eq("supplier",spare.getSupplier());
|
query.last(" limit 1");
|
DeviceSpare spareInDB = mapper.selectOne(query);
|
if(spareInDB != null){
|
spareInDB.setQuantity(spareInDB.getQuantity()+spare.getQuantity());
|
mapper.updateById(spareInDB);
|
|
}else{
|
//不存在则新增
|
mapper.insert(spare);
|
}
|
}
|
|
}
|
|
public void outBound(List<DeviceSpare> spareList) {
|
mapper.outBound(spareList);
|
}
|
|
public Response addByExcel(MultipartFile file) throws IOException {
|
//将文件流转化为workbook
|
Workbook book = WorkbookFactory.create(file.getInputStream());
|
Sheet sheet = book.getSheetAt(0);
|
//获取最后一行
|
int lastRowIndex = sheet.getLastRowNum();
|
List<DeviceSpare> spareList = new ArrayList<>();
|
for (int i = 1; i <= lastRowIndex; i++) {
|
DeviceSpare spare = new DeviceSpare();
|
Row row = sheet.getRow(i);
|
//对每行的前7列进行非空校验
|
for (int j = 0; j < 7; j++) {
|
Cell cell = row.getCell(j);
|
//非图片列进行非空校验.不合规无法上传
|
if (cell == null || cell.getCellType() == CellType.BLANK) {
|
return new Response().set(1, false, "第" + (i + 1) + "行第" + (j + 1) + "列数据为空");
|
}
|
}
|
//获取列的值
|
String name = row.getCell(0).getStringCellValue();
|
String model = row.getCell(1).getStringCellValue();
|
String version = row.getCell(2).getStringCellValue();
|
int quantity = (int) row.getCell(3).getNumericCellValue();
|
String brand = row.getCell(4).getStringCellValue();
|
String type = row.getCell(5).getStringCellValue();
|
String supplier = row.getCell(6).getStringCellValue();
|
spare.setName(name);
|
spare.setModel(model);
|
spare.setVersion(version);
|
spare.setQuantity(quantity);
|
spare.setBrand(brand);
|
spare.setType(type);
|
spare.setSupplier(supplier);
|
|
spareList.add(spare);
|
|
}
|
|
//第七列为图片-浮动式图片
|
//获取绘图对象中的所有图形
|
XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
|
if (drawing == null) {
|
drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
|
}
|
List<XSSFShape> shapes = drawing.getShapes();
|
for (XSSFShape shape : shapes) {
|
if (shape instanceof XSSFPicture) {
|
XSSFPicture pic = (XSSFPicture) shape;
|
XSSFClientAnchor anchor = pic.getClientAnchor();
|
//获取图片所在的起始行
|
int rowIndex = anchor.getRow1();
|
byte[] data = pic.getPictureData().getData();
|
|
DeviceSpare spare = spareList.get(rowIndex - 1);
|
//对存储路径进行定义
|
Date now = new Date();
|
String timeFormat = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM_DD_HH_MM_SS_UNION, now);
|
String dirMonth = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM, now);
|
String fileDirPath = CommonUtil.getRootFile() + "deviceSpare" + File.separator + dirMonth;
|
File fileDir = new File(fileDirPath);
|
//如果文件夹不存在则创建
|
if (!fileDir.exists()) {
|
fileDir.mkdirs();
|
}
|
String filePath = fileDirPath + File.separator + spare.getName()+"_"+spare.getModel()+"_"+spare.getVersion() + "_"+ timeFormat+".png";
|
|
// 保存图片到本地
|
try (FileOutputStream fos = new FileOutputStream(filePath)) {
|
fos.write(data);
|
}
|
String split = "pis_file"+File.separator+"deviceSpare";
|
String picUrl = File.separator + filePath.substring(filePath.indexOf(split));
|
spare.setPictureUrl(picUrl);
|
}
|
}
|
|
//新增列表
|
List<DeviceSpare> spareListNew = new ArrayList<>();
|
//更新列表
|
List<DeviceSpare> spareListUpdate = new ArrayList<>();
|
//查询库中的所有设备器件
|
List<DeviceSpare> deviceSpareListInDB = mapper.selectList((Wrapper<DeviceSpare>) CommonUtil.nullObject);
|
for (DeviceSpare spare : spareList){
|
boolean isExist = false;
|
for (DeviceSpare spareInDB : deviceSpareListInDB){
|
if (spare.getName().equals(spareInDB.getName())
|
&& spare.getModel().equals(spareInDB.getModel())
|
&& spare.getVersion().equals(spareInDB.getVersion())
|
&& spare.getBrand().equals(spareInDB.getBrand())
|
&& spare.getType().equals(spareInDB.getType())
|
&& spare.getSupplier().equals(spareInDB.getSupplier())
|
){
|
isExist = true;
|
spare.setId(spareInDB.getId());
|
spare.setQuantity(spareInDB.getQuantity()+spare.getQuantity());
|
if (spare.getPictureUrl() != null && spareInDB.getPictureUrl()!=null){
|
spare.setPictureUrl(spareInDB.getPictureUrl()+","+spare.getPictureUrl());
|
}
|
spareListUpdate.add(spare);
|
}
|
}
|
if(!isExist){
|
//不存在则新增
|
spareListNew.add(spare);
|
}
|
}
|
//更新
|
if(spareListUpdate.size()>0) {
|
updateQuantityAndPictureBatch(spareListUpdate);
|
}
|
//新增
|
if(spareListNew.size()>0) {
|
addBatch(spareListNew);
|
}
|
|
return new Response().setII(1,"导入完成");
|
}
|
|
private void updateQuantityAndPictureBatch(List<DeviceSpare> spareListUpdate) {
|
mapper.updateQuantityAndPictureBatch(spareListUpdate);
|
}
|
|
public void excelExport(HttpServletResponse response) {
|
//查询所有的设备器件
|
List<DeviceSpare> deviceSpareList = mapper.selectList((Wrapper<DeviceSpare>) CommonUtil.nullObject);
|
//获取导出模板地址
|
ClassPathResource classPathResource = new ClassPathResource("excel_templates/template_device_spare.xlsx");
|
String path = classPathResource.getPath();
|
TemplateExportParams templateExportParams1 = new TemplateExportParams(path,true);
|
// 构建导出数据模型
|
Map<String, Object> map = new HashMap<>();
|
map.put("deviceSpareList", deviceSpareList); // 假设模板中使用 ${deviceSpareList} 作为变量名
|
|
Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
|
try {
|
LocalDateTime now = LocalDateTime.now();
|
String fileName = "维修管理器件库存_"+now.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))+".xlsx";
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
|
response.flushBuffer();
|
wb.write(response.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
}
|
