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