From eb33c60306f707ec7b30a048388e4de702ca2f15 Mon Sep 17 00:00:00 2001
From: whyclxw <810412026@qq.com>
Date: 星期四, 06 三月 2025 08:24:51 +0800
Subject: [PATCH] 查询日期三天内的所有上传软件
---
src/main/java/com/whyc/service/SoftwareService.java | 353 ++++++++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 329 insertions(+), 24 deletions(-)
diff --git a/src/main/java/com/whyc/service/SoftwareService.java b/src/main/java/com/whyc/service/SoftwareService.java
index ecdcfa9..93eb460 100644
--- a/src/main/java/com/whyc/service/SoftwareService.java
+++ b/src/main/java/com/whyc/service/SoftwareService.java
@@ -1,26 +1,58 @@
package com.whyc.service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
+import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
+import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.github.pagehelper.PageHelper;
+import com.github.pagehelper.PageInfo;
+import com.whyc.constant.UserOperation;
+import com.whyc.dto.FileDirPath;
import com.whyc.dto.Response;
+import com.whyc.dto.SoftDto;
+import com.whyc.mapper.SoftcodeMapper;
import com.whyc.mapper.SoftwareMapper;
+import com.whyc.pojo.Softcode;
import com.whyc.pojo.Software;
+import com.whyc.util.ActionUtil;
+import com.whyc.util.CommonUtil;
+import com.whyc.util.DateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
-import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+import org.springframework.web.multipart.MultipartFile;
-import java.io.IOException;
-import java.io.InputStream;
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+import java.io.*;
+import java.net.URLEncoder;
+import java.text.ParseException;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.time.ZoneId;
+import java.util.Date;
import java.util.LinkedList;
import java.util.List;
+import java.util.stream.Collectors;
@Service
public class SoftwareService {
- @Autowired
+ @Autowired(required = false)
private SoftwareMapper mapper;
- public List<Software> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
+ @Autowired(required = false)
+ private SoftcodeMapper codeMapper;
+
+ @Autowired
+ private DocLogService logService;
+
+ public Response<Object> excelParse(InputStream inputStream) throws IOException, InvalidFormatException, ParseException {
List<Software> softwareList = new LinkedList<>();
Workbook workbook = null;
@@ -29,45 +61,318 @@
//鍙栫涓�涓猻heet琛�
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
+ //鏍″噯lastRowNum
+ for (int i = 8; i <= lastRowNum; i++) {
+ if(sheet.getRow(i).getCell(1).getStringCellValue().equals("鍙戝竷璇存槑")){
+ lastRowNum = i;
+ break;
+ }
+ }
//鍥哄畾5鍒�
short cellNum = 5;
//鍙栧浐瀹氶儴鍒嗗��
Software common = new Software();
common.setFileName(sheet.getRow(2).getCell(2).getStringCellValue());
- String typeStr = sheet.getRow(3).getCell(2).getStringCellValue();
+ Cell cellBoardNumber = sheet.getRow(3).getCell(2);
+ if(cellBoardNumber != null){
+ common.setBoardNumber(cellBoardNumber.getStringCellValue());
+ }
+ String typeStr = sheet.getRow(4).getCell(2).getStringCellValue();
String[] typeArr = typeStr.split(" ");
+ for (int i = 0; i < typeArr.length; i++) {
+ //excel鍗曞厓鏍间腑鐨勨湐瀵瑰簲瀛楃
+ if(typeArr[i].contains("镁")){
+ common.setType(typeArr[i].replace("镁","").trim());
+ break;
+ }
+ }
+ //妫�楠�
+ if(!common.getType().equals("搴旂敤杞欢")){
+ if(common.getBoardNumber() == null){
+ return new Response().set(1,false,"褰撴槸BootLoader 杞欢銆佹搷浣滅郴缁熻蒋浠讹紙鏍稿績鏉匡級杞欢鏃讹紝闇�濉啓PCB鐨勮鏍煎瀷鍙�");
+ }
+ }
+ common.setVersion(sheet.getRow(5).getCell(2).getStringCellValue());
+ common.setBasedVersion(sheet.getRow(5).getCell(4).getStringCellValue());
- //绗�8琛屽紑濮�,鍊掓暟绗�2琛屾埅姝�
- for (int i = 0; i < lastRowNum+1; i++) {
+ common.setOwner(sheet.getRow(6).getCell(2).getStringCellValue());
+ Cell cellFilingDate = sheet.getRow(6).getCell(4);
+ if(cellFilingDate.getCellType() == Cell.CELL_TYPE_NUMERIC){
+ Date dateCellValue = cellFilingDate.getDateCellValue();
+ common.setFilingDate(DateUtil.YYYY_MM_DD.format(dateCellValue));
+ System.out.println(dateCellValue);
+ }else {
+ cellFilingDate.setCellType(Cell.CELL_TYPE_STRING);
+ common.setFilingDate(cellFilingDate.getStringCellValue());
+ }
+ //鏈�鍚庝竴琛�,鍙栧彂甯冭鏄�
+ common.setReleaseNotes(sheet.getRow(lastRowNum).getCell(2).getStringCellValue());
+
+ //绗�9琛屽紑濮�,鍊掓暟绗�2琛屾埅姝�
+ int applyModelNum = lastRowNum + 1 - 9;
+ for (int i = 0; i < applyModelNum; i++) {
Software software = new Software();
+ BeanUtils.copyProperties(common,software);
//鍙栫3鍒�,绗�5鍒�
- for (int j = 1; j < cellNum; j++) {
- Row row = sheet.getRow(i);
- Cell cell = row.getCell(j);
- if(j == 2){
- cell.setCellType(Cell.CELL_TYPE_STRING);
- }
- String cellValue = cell.getStringCellValue();
-
- switch (j){
-
+ Cell cell = sheet.getRow(8 + i).getCell(2);
+ cell.setCellType(Cell.CELL_TYPE_STRING);
+ String stringCellValue = cell.getStringCellValue();
+ if(!stringCellValue.equals("")) {
+ int length = stringCellValue.length();
+ if (length == 9) {
+ stringCellValue = "0" + stringCellValue;
+ } else if (length < 9) {
+ int centerZeroNum = 10 - length - 1;
+ StringBuilder centerZeroStr = new StringBuilder();
+ for (int j = 0; j < centerZeroNum; j++) {
+ centerZeroStr.append("0");
+ }
+ String strFront = stringCellValue.substring(0, 3);
+ String strBehind = stringCellValue.substring(3);
+ stringCellValue = "0" + strFront + centerZeroStr + strBehind;
}
}
+ software.setApplyMaterialCode(stringCellValue);
+ software.setApplyModel(sheet.getRow(8+i).getCell(4).getStringCellValue());
+
softwareList.add(software);
}
- return softwareList;
+ softwareList = softwareList.stream().filter(software -> !software.getApplyMaterialCode().equals("")).collect(Collectors.toList());
+
+ return new Response().setII(1,true,softwareList,"鏂囦欢瑙ｆ瀽鎴愬姛");
}
//鏌ヨ杞欢鍒楄〃鐨勪俊鎭�
- public Response getAllSoftware(String fileName, int pageCurr, int pageSize) {
+ public Response getAllSoftware(Integer lockFlag, String fileName, String applyMaterialCode, String applyModel, String owner, String boardNumber
+ ,String version, int pageCurr, int pageSize) {
PageHelper.startPage(pageCurr,pageSize);
- QueryWrapper wrapper=new QueryWrapper();
- if(fileName!=null&&!fileName.isEmpty()){
- wrapper.like("file_name",fileName);
+ List<SoftDto> list=mapper.getFileUrl(lockFlag,fileName,applyMaterialCode,applyModel,owner,boardNumber,version);
+ //鏍规嵁杞欢鍚嶇О鏌ヨ鏄惁瀛樺湪婧愮爜锛�0锛氬惁锛�1锛氬瓨鍦級
+ for (SoftDto dto:list) {
+ QueryWrapper wrapper=new QueryWrapper();
+ wrapper.eq("file_name",dto.getFileName());
+ wrapper.last("limit 1");
+ Softcode code=codeMapper.selectOne(wrapper);
+ if(code!=null){
+ dto.setCodeFlag(1);
+ dto.setCodeName(code.getCodeName());
+ }else{
+ dto.setCodeFlag(0);
+ dto.setCodeName("");
+ }
}
- wrapper.orderByAsc("id");
+ PageInfo pageInfo=new PageInfo(list);
+ return new Response().setII(1,list.size()>0,pageInfo,"杞欢淇℃伅杩斿洖");
+ }
+ //鏍规嵁subcode鏌ヨ杞欢鍒楄〃
+ public Response getSoftBySubCode(String subCode) {
+ QueryWrapper wrapper=new QueryWrapper();
+ wrapper.eq("apply_material_code",subCode);
+ wrapper.orderByAsc("version");
List list=mapper.selectList(wrapper);
return new Response().setII(1,list.size()>0,list,"杞欢淇℃伅杩斿洖");
}
+
+
+ //鏍规嵁杞欢鍚嶇О瀹炵幇杞欢涓嬭浇
+ public void downLoadSoftware(HttpServletRequest req, HttpServletResponse resp, int id) {
+ String fileDirName = FileDirPath.getFileDirName();
+ QueryWrapper wrapper=new QueryWrapper();
+ wrapper.eq("id",id);
+ wrapper.last("limit 1");
+ Software software=mapper.selectOne(wrapper);
+
+ String filename=software.getFileUrl().substring(software.getFileUrl().lastIndexOf("\\")+1);
+ try {
+ // 杞爜闃叉涔辩爜
+ //resp.addHeader("Content-Disposition", "attachment;filename=" + new String(softwareName.getBytes("UTF-8"), "ISO8859-1"));
+ resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode (filename, "utf-8"));
+ OutputStream out = resp.getOutputStream();
+ FileInputStream in = new FileInputStream(fileDirName+File.separator+software.getFileUrl());
+ int len=0;
+ byte[] buffer =new byte[1024];
+ //7. 灏嗙紦鍐插尯涓殑鏁版嵁杈撳嚭
+ while ((len=in.read(buffer))>0){
+ out.write(buffer,0,len);
+ }
+ in.close();
+ out.close();
+ } catch (FileNotFoundException | UnsupportedEncodingException e) {
+ e.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ //璁板綍鏃ュ織
+ logService.recordOperationLogDownLoad(ActionUtil.getUser().getId(),ActionUtil.getUser().getName(), UserOperation.TYPE_DOWNLOAD_SOFWARE.getType(),new Date(),req.getRemoteAddr()
+ ,filename,fileDirName+File.separator+software.getFileUrl(),"",String.valueOf(id),software.getVersion());
+
+ }
+
+ @Transactional
+ public Response upload(MultipartFile file1, MultipartFile file2, String fontUpdateTime, List<Software> softwareList) throws IOException {
+ String userName = ActionUtil.getUser().getName();
+ String file1Name = file1.getOriginalFilename();
+ String file2Name = file2.getOriginalFilename();
+ Software software = softwareList.get(0);
+ if(!software.getFileName().equals(file1Name.substring(0,file1Name.lastIndexOf(".")))){
+ return new Response().set(1,false,"闄勪欢鐨勬枃浠跺悕涓庤蒋浠跺彂甯冭褰昬xcel鍐呯殑鏂囦欢鍚嶇О涓嶄竴鑷�");
+ }
+ Software softExists = getByFilename(software.getFileName());
+ if(softExists != null){ //鏂囦欢鍚�:瑙勬牸鍨嬪彿_杞欢鐗堟湰,宸插瓨鍦�,涓嶈兘閲嶅涓婁紶;杩欑鎯呭喌灞炰簬杩藉姞鏈哄瀷,鍒鏇存柊
+ return new Response().set(1,false,"鏂囦欢鍚嶇О("+softExists.getFileName()+")宸插瓨鍦�,涓嶈兘閲嶅涓婁紶");
+ }
+ Date date = new Date();
+ String dateUnion = DateUtil.YYYY_MM_DD_HH_MM_SS_UNION.format(date);
+ //鏂囦欢閲嶅懡鍚�(涓ユ牸閬靛畧鍚嶇О瑙勫垯鐨勬儏鍐典笅涓嶄細閲嶅悕)
+ file1Name = file1Name.substring(0,file1Name.lastIndexOf(".")) + "_" + dateUnion +file1Name.substring(file1Name.lastIndexOf("."));
+ file2Name = file2Name.substring(0,file2Name.lastIndexOf(".")) + "_" + dateUnion +file2Name.substring(file2Name.lastIndexOf("."));
+ //瀛樺偍璺緞
+ String rootFile = CommonUtil.getRootFile();
+ String softwareDir = rootFile + "software" + File.separator + software.getOwner()+ File.separator + software.getFileName();
+ String softwareHttpUrl = softwareDir.substring(softwareDir.lastIndexOf("doc_file"+ File.separator + "software"));
+ File softwareDirFile = new File(softwareDir);
+ if(!softwareDirFile.exists()){
+ softwareDirFile.mkdirs();
+ }
+ file1.transferTo(new File(softwareDir+File.separator+file1Name));
+ file2.transferTo(new File(softwareDir+File.separator+file2Name));
+ //璁剧疆璺緞
+ for (Software software1:softwareList){
+ software1.setFontUpdateTime(fontUpdateTime);
+ software1.setFileUrl(softwareHttpUrl+File.separator+file1Name);
+ software1.setExcelUrl(softwareHttpUrl+File.separator+file2Name);
+ software1.setCreateTime(date);
+ software1.setUploadUser(userName);
+ //閿佸畾-涓撲緵娴嬭瘯浜哄憳鎵嶈兘瑙ｉ攣
+ software1.setLockFlag(-1);
+ }
+ /* 鍙栨秷鑷姩閿佸畾鍘熸潵鐗堟湰,鍥犱负鏂扮増鏈渶瑕佽繘琛屾祴璇曟墠鑳戒娇鐢�
+ //閿佸畾鍘熸潵鐨勬墍鏈夌増鏈�:鏍规嵁鐗堝彿鍜岃蒋浠剁被鍨�
+ List<String> materialCodeList = softwareList.stream().map(Software::getApplyMaterialCode).collect(Collectors.toList());
+ lockByBoardNumberAndTypeAndMaterialCode(software.getBoardNumber(),software.getType(),materialCodeList);*/
+ //鍐欏叆鏁版嵁搴�
+ insertBatch(softwareList);
+ return new Response().set(1,true,"涓婁紶瀹屾垚");
+ }
+
+ private void lockByBoardNumberAndTypeAndMaterialCode(String boardNumber, String type, List<String> materialCodeList) {
+ UpdateWrapper<Software> update = Wrappers.update();
+ update.set("lock_flag",1)
+ .set("local_reason","杞欢鐗堟湰鍗囩骇")
+ .eq("board_number",boardNumber)
+ .eq("type",type)
+ .ne("lock_flag",-1)
+ .in("apply_material_code",materialCodeList);
+ mapper.update(null,update);
+ }
+
+ private Software getByFilename(String fileName) {
+ QueryWrapper<Software> query = Wrappers.query();
+ query.eq("file_name",fileName).last(" limit 1");
+ return mapper.selectOne(query);
+ }
+
+ private void insertBatch(List<Software> softwareList){
+ mapper.insertBatchSomeColumn(softwareList);
+ }
+
+ @Transactional
+ public Response updateApplyModel(MultipartFile multipartFile, List<Software> softwareList) throws IOException {
+ String originalFilename = multipartFile.getOriginalFilename();
+ Software software = softwareList.get(0);
+ QueryWrapper<Software> query = Wrappers.query();
+ query.eq("file_name", software.getFileName()).last(" limit 1");
+ Software softwareDB = mapper.selectOne(query);
+ if(softwareDB == null){
+ return new Response().set(1,false,"瀵瑰簲鐨勮蒋浠跺苟鏈笂浼犺繃,鏃犳硶鏇存柊閫傜敤鏈哄瀷");
+ }else{
+ //鍐欏叆鏂板鐨勮蒋浠跺彂甯冭褰昬xcel
+ String rootFile = CommonUtil.getRootFile();
+ String softwareDir = rootFile + "software" + File.separator + software.getOwner()+ File.separator + software.getFileName();
+ String softwareHttpUrl = softwareDir.substring(softwareDir.lastIndexOf("doc_file"+ File.separator + "software"));
+ multipartFile.transferTo(new File(softwareDir + File.separator + originalFilename));
+ //鍏堝垹闄ゅ搴旂殑閫傜敤鏈哄瀷,鍐嶆柊澧為�傜敤鏈哄瀷璁板綍
+ UpdateWrapper<Software> update = Wrappers.update();
+ update.eq("file_name",softwareDB.getFileName());
+ mapper.delete(update);
+ String fontUpdateTime = softwareDB.getFontUpdateTime();
+ softwareList.forEach(software2 -> {
+ software2.setFileUrl(softwareDB.getFileUrl());
+ software2.setExcelUrl(softwareHttpUrl + File.separator + originalFilename);
+ software2.setCreateTime(new Date());
+ software2.setLockFlag(-1);
+ software2.setFontUpdateTime(fontUpdateTime);
+ });
+ mapper.insertBatchSomeColumn(softwareList);
+
+ return new Response().set(1,true,"鏇存柊瀹屾垚");
+ }
+ }
+
+ /**
+ * 鏍规嵁杞欢鍞竴鍊煎瓧娈典慨鏀硅蒋浠堕攣瀹氱姸鎬�,杩欓噷閫夊彇fileUrl
+ *
+ * @param fileUrl
+ * @param lockFlagNow
+ * @param lockFlag
+ * @param localReason
+ * @return
+ */
+ public Response updateSoftwareLock(String fileUrl, int lockFlagNow, int lockFlag, String localReason) {
+ String userName = ActionUtil.getUser().getName();
+ if(lockFlag ==0){
+ //鍙湁鏉庢鍗庢墠鑳借В閿� 寰呮祴璇曢攣瀹氱殑杞欢
+ QueryWrapper<Software> query = Wrappers.query();
+ query.eq("file_url",fileUrl);
+ List<Software> softwareListInDB = mapper.selectList(query);
+ Software softwareInDB = softwareListInDB.get(0);
+ if(softwareInDB.getLockFlag() == -1){
+ //if(!userName.equals("鏉庢鍗�")){
+ // return new Response().set(1,false,"鏃犳潈闄愯В閿� 寰呮祴璇曠殑杞欢");
+ //}else{
+ //閿佸畾鍘熸潵鐨勬墍鏈夌増鏈�:鏍规嵁鐗堝彿鍜岃蒋浠剁被鍨�,鎺掗櫎寰呮祴璇曠増鏈�
+ List<String> materialCodeList = softwareListInDB.stream().map(Software::getApplyMaterialCode).collect(Collectors.toList());
+ lockByBoardNumberAndTypeAndMaterialCode(softwareInDB.getBoardNumber(),softwareInDB.getType(),materialCodeList);
+
+ //}
+ }
+ }
+ UpdateWrapper uwrapper=new UpdateWrapper();
+ uwrapper.set("lock_flag",lockFlag);
+ uwrapper.set("local_reason",localReason);
+ uwrapper.eq("file_url",fileUrl);
+ uwrapper.eq("lock_flag",lockFlagNow);
+ int flag=mapper.update(null,uwrapper);
+ return new Response().set(1,flag>0,"閿佸畾/瑙ｉ攣鎴愬姛");
+ }
+ //鏍规嵁杞欢鍚嶇О瀹炵幇杞欢鍒犻櫎
+ public Response deleteSoftware(String fileName,String version) {
+ UpdateWrapper wrapper=new UpdateWrapper();
+ wrapper.eq("file_name",fileName);
+ wrapper.eq("version",version);
+ int flag=mapper.delete(wrapper);
+ return new Response().set(1,flag>0,"浠秈d瀹炵幇杞欢鍒犻櫎");
+ }
+
+ //鏌ヨ鏃ユ湡涓夊ぉ鍐呯殑鎵�鏈変笂浼犺蒋浠�
+ public Response getFileNameByCreateTime(String createTime) {
+ //鑾峰彇endtime鐨勪笁澶╁墠鐨勬椂闂�
+ LocalDate createDay= LocalDate.parse(createTime);
+ LocalDateTime end = createDay.atTime(LocalTime.MAX);
+ Date endtime=Date.from(end.atZone(ZoneId.systemDefault()).toInstant());
+ //鑾峰彇createTime鐨勪笁澶╁墠鐨勬椂闂�
+ LocalDate threeDaysAgo = createDay.minusDays(3);
+ LocalDateTime startOfDay = threeDaysAgo.atStartOfDay();
+ Date starttime=Date.from(startOfDay.atZone(ZoneId.systemDefault()).toInstant());
+
+ QueryWrapper wrapper=new QueryWrapper();
+ wrapper.select("distinct file_name","type","version","based_version","owner","filing_date","create_time");
+ wrapper.ge("create_time",starttime);
+ wrapper.le("create_time",endtime);
+ List<Software> list=mapper.selectList(wrapper);
+ //鑾峰彇list涓璮ileName鐨勯泦鍚�
+ //List<String> nameList = list.stream().map(Software::getFileName).collect(Collectors.toList());
+ return new Response().setII(1,list!=null,list,"鏌ヨ鏃ユ湡涓夊ぉ鍐呯殑鎵�鏈変笂浼犺蒋浠�");
+ }
}
--
Gitblit v1.9.1