From 25e2f9601aed6d23923f3200ef248036b7787e7c Mon Sep 17 00:00:00 2001 From: pang.jiabao <pang_jiabao@163.com> Date: 星期二, 29 四月 2025 10:51:31 +0800 Subject: [PATCH] 产品合格校验手动excel导入 --- src/main/webapp/static/js/locDetl/locDetl.js | 25 ++++++ src/main/webapp/views/locDetl/locDetl.html | 8 + src/main/java/com/zy/asrs/mapper/LocDetlMapper.java | 7 + src/main/java/com/zy/asrs/importexcle/ProductCalibrationImportListener.java | 103 +++++++++++++++++++++++++ src/main/java/com/zy/asrs/controller/LocDetlController.java | 24 ++++++ src/main/resources/mapper/LocDetlMapper.xml | 3 src/main/java/com/zy/asrs/entity/param/ProductCalibrationExcelParam.java | 21 +++++ 7 files changed, 189 insertions(+), 2 deletions(-) diff --git a/src/main/java/com/zy/asrs/controller/LocDetlController.java b/src/main/java/com/zy/asrs/controller/LocDetlController.java index d478272..9639102 100644 --- a/src/main/java/com/zy/asrs/controller/LocDetlController.java +++ b/src/main/java/com/zy/asrs/controller/LocDetlController.java @@ -14,13 +14,16 @@ import com.core.common.R; import com.zy.asrs.entity.LocDetl; import com.zy.asrs.entity.Mat; +import com.zy.asrs.entity.param.ProductCalibrationExcelParam; import com.zy.asrs.importexcle.LocMat; import com.zy.asrs.importexcle.LocMatListener; +import com.zy.asrs.importexcle.ProductCalibrationImportListener; import com.zy.asrs.mapper.LocDetlMapper; import com.zy.asrs.service.LocDetlService; import com.zy.asrs.service.LocMastService; import com.zy.asrs.service.MatService; import com.zy.common.web.BaseController; +import org.apache.ibatis.session.SqlSessionFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import org.springframework.web.bind.annotation.*; @@ -46,11 +49,26 @@ @Resource private LocMastService locMastService; + @Resource + private SqlSessionFactory sqlSessionFactory; + @PostMapping("/importLocData") @ManagerAuth(memo = "瀵煎叆绔嬪簱宸叉湁搴撳瓨") public R importLocData(@RequestParam("file") MultipartFile multipartFile) { try { importLocData1(multipartFile); + } catch (Exception e) { + e.printStackTrace(); + return R.error(e.getMessage()); + } + return R.ok("瀵煎叆鎴愬姛"); + } + + @PostMapping("/productCalibration") + @ManagerAuth(memo = "浜у搧鏍¢獙瀵煎叆excel鏂瑰紡") + public R productCalibration(@RequestParam("file") MultipartFile multipartFile) { + try { + productCalibrationExcel(multipartFile); } catch (Exception e) { e.printStackTrace(); return R.error(e.getMessage()); @@ -73,6 +91,12 @@ new LocMatListener(locMastService,locDetlMapper,getUserId())).sheet().doReadSync(); } + @Transactional(rollbackFor = Exception.class) + public void productCalibrationExcel(MultipartFile multipartFile) throws IOException { + EasyExcel.read(multipartFile.getInputStream(), ProductCalibrationExcelParam.class, + new ProductCalibrationImportListener(sqlSessionFactory)).sheet().doReadSync(); + } + @RequestMapping(value = "/locDetl/update") public R update1() { if (!locDetlService.updateLocNo("0402805", "0402804")) { diff --git a/src/main/java/com/zy/asrs/entity/param/ProductCalibrationExcelParam.java b/src/main/java/com/zy/asrs/entity/param/ProductCalibrationExcelParam.java new file mode 100644 index 0000000..17dd4d1 --- /dev/null +++ b/src/main/java/com/zy/asrs/entity/param/ProductCalibrationExcelParam.java @@ -0,0 +1,21 @@ +package com.zy.asrs.entity.param; + +import com.alibaba.excel.annotation.ExcelProperty; +import lombok.Data; + +/** + * @author pang.jiabao + * @description 浜у搧鏍¢獙excel瀵煎叆param + * @createDate 2025/4/29 9:19 + */ +@Data +public class ProductCalibrationExcelParam { + + // index浠�0寮�濮� + @ExcelProperty(value = "鍗峰彿",index = 0) + private String rollNo; + + @ExcelProperty(value = "鏍¢獙缁撴灉 鍚堟牸/涓嶅悎鏍�",index = 1) + private String qualified; + +} diff --git a/src/main/java/com/zy/asrs/importexcle/ProductCalibrationImportListener.java b/src/main/java/com/zy/asrs/importexcle/ProductCalibrationImportListener.java new file mode 100644 index 0000000..8ef2889 --- /dev/null +++ b/src/main/java/com/zy/asrs/importexcle/ProductCalibrationImportListener.java @@ -0,0 +1,103 @@ +package com.zy.asrs.importexcle; + +import com.alibaba.excel.context.AnalysisContext; +import com.alibaba.excel.event.AnalysisEventListener; +import com.alibaba.fastjson.JSON; +import com.core.exception.CoolException; +import com.zy.asrs.entity.param.ProductCalibrationExcelParam; +import com.zy.asrs.mapper.LocDetlMapper; +import lombok.SneakyThrows; +import lombok.extern.slf4j.Slf4j; +import org.apache.ibatis.session.ExecutorType; +import org.apache.ibatis.session.SqlSession; +import org.apache.ibatis.session.SqlSessionFactory; + +import java.util.ArrayList; +import java.util.List; + +/** + * @author pang.jiabao + * @description 鐩戝惉绔嬪簱宸叉湁鐗╂枡excel鏁版嵁瀵煎叆 + * @createDate 2023/10/9 11:13 + */ +@Slf4j +public class ProductCalibrationImportListener extends AnalysisEventListener<ProductCalibrationExcelParam> { + + /** + * 姣忛殧1000鏉″瓨鍌ㄦ暟鎹簱锛屽疄闄呬娇鐢ㄤ腑鍙互3000鏉★紝鐒跺悗娓呯悊list 锛屾柟渚垮唴瀛樺洖鏀� + */ + private static final int BATCH_COUNT = 100; + + private int count = 0; + + List<ProductCalibrationExcelParam> list = new ArrayList<>(); + /** + * 鍋囪杩欎釜鏄竴涓狣AO锛屽綋鐒舵湁涓氬姟閫昏緫杩欎釜涔熷彲浠ユ槸涓�涓猻ervice銆傚綋鐒跺鏋滀笉鐢ㄥ瓨鍌ㄨ繖涓璞℃病鐢ㄣ�� + */ + private final SqlSessionFactory sqlSessionFactory; + + /** + * 濡傛灉浣跨敤浜唖pring,璇蜂娇鐢ㄨ繖涓瀯閫犳柟娉曘�傛瘡娆″垱寤篖istener鐨勬椂鍊欓渶瑕佹妸spring绠$悊鐨勭被浼犺繘鏉� + */ + public ProductCalibrationImportListener(SqlSessionFactory sqlSessionFactory) { + this.sqlSessionFactory = sqlSessionFactory; + } + + /** + * 杩欎釜姣忎竴鏉℃暟鎹В鏋愰兘浼氭潵璋冪敤 + */ + @SneakyThrows + @Override + public void invoke(ProductCalibrationExcelParam data, AnalysisContext context) { + log.info("鎴愬搧鏍¢獙瑙f瀽鍒扮 {} 鏉℃暟鎹�:{}", ++count, JSON.toJSONString(data)); + if ("鍚堟牸".equals(data.getQualified()) || "涓嶅悎鏍�".equals(data.getQualified())) { + list.add(data); + } else { + throw new CoolException("鎴愬搧鏍¢獙鏍¢獙缁撴灉寮傚父:" + data); + } + + // 杈惧埌BATCH_COUNT浜嗭紝闇�瑕佸幓瀛樺偍涓�娆℃暟鎹簱锛岄槻姝㈡暟鎹嚑涓囨潯鏁版嵁鍦ㄥ唴瀛橈紝瀹规槗OOM + if (list.size() >= BATCH_COUNT) { + saveData(); + // 瀛樺偍瀹屾垚娓呯悊 list + list.clear(); + } + } + + /** + * 鎵�鏈夋暟鎹В鏋愬畬鎴愪簡 閮戒細鏉ヨ皟鐢� + */ + @Override + public void doAfterAllAnalysed(AnalysisContext context) { + // 杩欓噷涔熻淇濆瓨鏁版嵁锛岀‘淇濇渶鍚庨仐鐣欑殑鏁版嵁涔熷瓨鍌ㄥ埌鏁版嵁搴� + if (list.size() > 0) { + saveData(); + } + log.info("鎴愬搧鏍¢獙鎵�鏈夋暟鎹В鏋愬畬鎴愶紒"); + } + + /** + * 鍔犱笂瀛樺偍鏁版嵁搴� + */ + private void saveData() { + log.info("{}鏉℃暟鎹紝寮�濮嬪瓨鍌ㄦ暟鎹簱锛�", list.size()); + try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) { + LocDetlMapper mapper = sqlSession.getMapper(LocDetlMapper.class); + list.forEach(productCalibrationExcelParam -> { + mapper.updateQualifiedByRollNo(productCalibrationExcelParam.getRollNo(), productCalibrationExcelParam.getQualified()); + }); + sqlSession.commit(); + } + + log.info("鎴愬搧鏍¢獙瀛樺偍鏁版嵁搴撴垚鍔燂紒"); + } + + /** + * 瑙f瀽鍑虹幇閿欒浼氳繘鍏ヨ鏂规硶 鍏蜂綋鐪嬫簮浠g爜鎴栨枃妗� + */ + @Override + public void onException(Exception exception, AnalysisContext context) throws Exception { + log.error("鎴愬搧鏍¢獙澶勭悊寮傚父锛�" + exception.getMessage()); + throw exception; + } +} diff --git a/src/main/java/com/zy/asrs/mapper/LocDetlMapper.java b/src/main/java/com/zy/asrs/mapper/LocDetlMapper.java index 50ba4a3..1c05beb 100644 --- a/src/main/java/com/zy/asrs/mapper/LocDetlMapper.java +++ b/src/main/java/com/zy/asrs/mapper/LocDetlMapper.java @@ -105,4 +105,11 @@ * @param status 鐘舵�� */ void updateLhStsByLocNo(@Param("locNo") String locNo,@Param("status") int status); + + /** + * 鏇存柊鎴愬搧鏍¢獙缁撴灉 + * @param rollNo 鍗峰彿 + * @param qualified 鏍¢獙缁撴灉 鍚堟牸/涓嶅悎鏍� + */ + void updateQualifiedByRollNo(@Param("rollNo") String rollNo, @Param("qualified") String qualified); } diff --git a/src/main/resources/mapper/LocDetlMapper.xml b/src/main/resources/mapper/LocDetlMapper.xml index 20c9784..d48e856 100644 --- a/src/main/resources/mapper/LocDetlMapper.xml +++ b/src/main/resources/mapper/LocDetlMapper.xml @@ -443,6 +443,9 @@ <update id="updateLhStsByLocNo"> update asr_loc_detl set dead_warn = #{status},modi_time = getdate() where loc_no = #{locNo} </update> + <update id="updateQualifiedByRollNo"> + update asr_loc_detl set three_code = #{qualified} where model = #{rollNo} + </update> </mapper> diff --git a/src/main/webapp/static/js/locDetl/locDetl.js b/src/main/webapp/static/js/locDetl/locDetl.js index c94a1c5..beb325d 100644 --- a/src/main/webapp/static/js/locDetl/locDetl.js +++ b/src/main/webapp/static/js/locDetl/locDetl.js @@ -46,12 +46,13 @@ return cols; } -layui.use(['table','laydate', 'form'], function(){ +layui.use(['table','laydate', 'form','upload','layer'], function(){ var table = layui.table; var $ = layui.jquery; var layer = layui.layer; var layDate = layui.laydate; var form = layui.form; + var upload = layui.upload; // 鏁版嵁娓叉煋 tableIns = table.render({ @@ -97,6 +98,28 @@ } }); + upload.render({ + elem: '#uploadExcel', // 缁戝畾鍏冪礌 + url: baseUrl+'/productCalibration', // 涓婁紶鎺ュ彛锛屾浛鎹负浣犵殑鍚庣鎺ュ彛 + headers:{'token': localStorage.getItem('token')}, + accept: 'file', // 鍏佽涓婁紶鐨勬枃浠剁被鍨� + exts: 'xls|xlsx', // 鍏佽涓婁紶鐨勬枃浠跺悗缂� + done: function(res){ + // 涓婁紶瀹屾瘯鍥炶皟 + if(res.code === 200){ + layer.msg(res.msg); + console.log('鏂囦欢鏁版嵁:', res.data); + } else { + layer.msg(res.msg || '涓婁紶澶辫触'); + } + }, + error: function(){ + // 璇锋眰寮傚父鍥炶皟 + layer.msg('涓婁紶澶辫触锛岃閲嶈瘯'); + } + }); + + // 鐩戝惉鎺掑簭浜嬩欢 table.on('sort(locDetl)', function (obj) { var searchData = {}; diff --git a/src/main/webapp/views/locDetl/locDetl.html b/src/main/webapp/views/locDetl/locDetl.html index 0b18e58..04dff36 100644 --- a/src/main/webapp/views/locDetl/locDetl.html +++ b/src/main/webapp/views/locDetl/locDetl.html @@ -9,6 +9,11 @@ <link rel="stylesheet" href="../../static/layui/css/layui.css" media="all"> <link rel="stylesheet" href="../../static/css/cool.css" media="all"> <link rel="stylesheet" href="../../static/css/common.css" media="all"> + <style> + #uploadExcel{ + display: none; + } + </style> </head> <body> @@ -71,7 +76,8 @@ </div> <script type="text/html" id="toolbar"> <div class="layui-btn-container"> - <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" style="margin-top: 10px">瀵煎嚭</button> + <button id="uploadExcel" class="layui-btn layui-btn-primary layui-btn-sm" >浜у搧鏍¢獙</button> + <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" style="margin-top: 1px">瀵煎嚭</button> </div> <!-- <input type="file" id="fileInput" accept=".xlsx, .xls">--> <!-- <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-import" onclick="exportExc()" style="margin-top: 10px">瀵煎叆</button>--> -- Gitblit v1.9.1