From ae674442480bf1126dbfa430b246cd9a706666cc Mon Sep 17 00:00:00 2001
From: Junjie <fallin.jie@qq.com>
Date: 星期五, 28 四月 2023 13:11:06 +0800
Subject: [PATCH] 商品档案数据导出和新商品编号同步
---
src/main/java/com/zy/asrs/utils/MatExcel2Listener.java | 79 +++++++++++++++++++
src/main/webapp/static/js/mat/mat.js | 71 +++++++++++++++++
src/main/java/com/zy/asrs/mapper/MatMapper.java | 2
src/main/resources/mapper/MatMapper.xml | 15 +++
src/main/webapp/views/mat/mat.html | 7 +
src/main/java/com/zy/asrs/entity/Mat.java | 9 ++
src/main/java/com/zy/asrs/controller/MatController.java | 30 +++++++
src/main/java/com/zy/asrs/service/impl/MatServiceImpl.java | 7 +
src/main/java/com/zy/asrs/service/MatService.java | 4 +
9 files changed, 219 insertions(+), 5 deletions(-)
diff --git a/src/main/java/com/zy/asrs/controller/MatController.java b/src/main/java/com/zy/asrs/controller/MatController.java
index 876df56..0a2cf10 100644
--- a/src/main/java/com/zy/asrs/controller/MatController.java
+++ b/src/main/java/com/zy/asrs/controller/MatController.java
@@ -17,6 +17,7 @@
import com.zy.asrs.entity.result.KeyValueVo;
import com.zy.asrs.service.LocDetlService;
import com.zy.asrs.service.MatService;
+import com.zy.asrs.utils.MatExcel2Listener;
import com.zy.asrs.utils.MatExcelListener;
import com.zy.common.CodeRes;
import com.zy.common.config.AdminInterceptor;
@@ -33,6 +34,8 @@
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.IOException;
+import java.io.InputStream;
+import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;
@@ -153,8 +156,8 @@
return R.ok();
}
- @RequestMapping(value = "/mat/update/auth")
- @ManagerAuth
+ @RequestMapping(value = "/mat/update/auth")
+ @ManagerAuth
public R update(Mat mat){
if (Cools.isEmpty(mat) || null==mat.getId()){
return R.error();
@@ -309,6 +312,29 @@
return R.ok("鎴愬姛鍚屾"+listener.getTotal()+"鏉″晢鍝佹暟鎹�");
}
+ // 鏂板晢鍝佺紪鍙峰悓姝�
+ @PostMapping(value = "/mat/excel/importNewMatnr/auth")
+ @ManagerAuth(memo = "鏂板晢鍝佺紪鍙峰悓姝�")
+ @Transactional
+ public R matExcelImportNewMatnr(MultipartFile file) throws IOException {
+ MatExcel2Listener listener = new MatExcel2Listener(getUserId());
+ EasyExcel.read(file.getInputStream(), MatExcel.class, listener).sheet().doRead();
+ return R.ok("鎴愬姛鍚屾"+listener.getTotal()+"鏉″晢鍝佹暟鎹�");
+ }
+
+ @RequestMapping(value = "/mat/excel/export/auth")
+ @ManagerAuth(memo = "鍟嗗搧妗ｆ鏁版嵁瀵煎嚭")
+ @Transactional
+ public void matExcelExport(@RequestParam Map<String, Object> param, HttpServletResponse response) throws IOException {
+ List<Mat> data = matService.exportData(param.get("matnr"), param.get("maktx"), param.get("specs"));
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ // 杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+ String fileName = URLEncoder.encode("娴嬭瘯", "UTF-8").replaceAll("\\+", "%20");
+ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
+ EasyExcel.write(response.getOutputStream(), Mat.class).sheet("妯℃澘").doWrite(data);
+ }
+
/*************************************** xm-select ***********************************************/
// xm-select 鎼滅储鍟嗗搧鍒楄〃
diff --git a/src/main/java/com/zy/asrs/entity/Mat.java b/src/main/java/com/zy/asrs/entity/Mat.java
index 420a5f5..0e6d939 100644
--- a/src/main/java/com/zy/asrs/entity/Mat.java
+++ b/src/main/java/com/zy/asrs/entity/Mat.java
@@ -59,9 +59,16 @@
* 鍟嗗搧缂栧彿
*/
@ApiModelProperty(value= "鍟嗗搧缂栧彿")
- @ExcelProperty(value = "鍟嗗搧缂栫爜")
+ @ExcelProperty(value = "鍟嗗搧缂栧彿")
private String matnr;
+ /**
+ * 鍟嗗搧缂栧彿
+ */
+ @ApiModelProperty(value= "鏂板晢鍝佺紪鍙�")
+ @ExcelProperty(value = "鏂板晢鍝佺紪鍙�")
+ private String matnrNew;
+
/**
* 鍟嗗搧鍚嶇О
diff --git a/src/main/java/com/zy/asrs/mapper/MatMapper.java b/src/main/java/com/zy/asrs/mapper/MatMapper.java
index d37ee63..a8a3a04 100644
--- a/src/main/java/com/zy/asrs/mapper/MatMapper.java
+++ b/src/main/java/com/zy/asrs/mapper/MatMapper.java
@@ -17,4 +17,6 @@
Mat selectByMatnr(@Param("matnr")String matnr);
+ List<Mat> exportData(Object matnr, Object maktx, Object specs);
+
}
diff --git a/src/main/java/com/zy/asrs/service/MatService.java b/src/main/java/com/zy/asrs/service/MatService.java
index 6e5e119..d17d198 100644
--- a/src/main/java/com/zy/asrs/service/MatService.java
+++ b/src/main/java/com/zy/asrs/service/MatService.java
@@ -4,10 +4,14 @@
import com.baomidou.mybatisplus.service.IService;
import com.zy.asrs.entity.Mat;
+import java.util.List;
+
public interface MatService extends IService<Mat> {
Page<Mat> getPage(Page page, String tagId, Object matnr, Object maktx, Object specs);
Mat selectByMatnr(String matnr);
+ List<Mat> exportData(Object matnr, Object maktx, Object specs);
+
}
diff --git a/src/main/java/com/zy/asrs/service/impl/MatServiceImpl.java b/src/main/java/com/zy/asrs/service/impl/MatServiceImpl.java
index e86dce8..f8a5781 100644
--- a/src/main/java/com/zy/asrs/service/impl/MatServiceImpl.java
+++ b/src/main/java/com/zy/asrs/service/impl/MatServiceImpl.java
@@ -7,6 +7,8 @@
import com.zy.asrs.mapper.MatMapper;
import com.zy.asrs.service.MatService;
+import java.util.List;
+
@Service("matService")
public class MatServiceImpl extends ServiceImpl<MatMapper, Mat> implements MatService {
@@ -21,5 +23,8 @@
return this.baseMapper.selectByMatnr(matnr);
}
-
+ @Override
+ public List<Mat> exportData(Object matnr, Object maktx, Object specs) {
+ return this.baseMapper.exportData(matnr, maktx, specs);
+ }
}
diff --git a/src/main/java/com/zy/asrs/utils/MatExcel2Listener.java b/src/main/java/com/zy/asrs/utils/MatExcel2Listener.java
new file mode 100644
index 0000000..79f8f29
--- /dev/null
+++ b/src/main/java/com/zy/asrs/utils/MatExcel2Listener.java
@@ -0,0 +1,79 @@
+package com.zy.asrs.utils;
+
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.event.AnalysisEventListener;
+import com.core.common.Cools;
+import com.core.common.SpringUtils;
+import com.core.exception.CoolException;
+import com.zy.asrs.entity.Mat;
+import com.zy.asrs.entity.Tag;
+import com.zy.asrs.mapper.TagMapper;
+import com.zy.asrs.service.MatService;
+import com.zy.asrs.service.TagService;
+import com.zy.common.entity.MatExcel;
+import com.zy.common.utils.NodeUtils;
+import lombok.extern.slf4j.Slf4j;
+
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+@Slf4j
+public class MatExcel2Listener extends AnalysisEventListener<MatExcel> {
+ private int total = 0;
+ private Long userId;
+
+ public MatExcel2Listener() {
+ }
+
+ public MatExcel2Listener(Long userId) {
+ this.userId = userId;
+ }
+
+ /**
+ * 姣忛殧5鏉″瓨鍌ㄦ暟鎹簱锛屽疄闄呬娇鐢ㄤ腑鍙互3000鏉★紝鐒跺悗娓呯悊list 锛屾柟渚垮唴瀛樺洖鏀�
+ */
+ private static final int BATCH_COUNT = 50;
+
+ private final List<MatExcel> list = new ArrayList<>();
+
+ /**
+ * 杩欓噷浼氫竴琛岃鐨勮繑鍥炲ご
+ */
+ @Override
+ public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
+ }
+
+ /**
+ * 杩欎釜姣忎竴鏉℃暟鎹В鏋愰兘浼氭潵璋冪敤
+ */
+ @Override
+ public void invoke(MatExcel excel, AnalysisContext ctx) {
+ MatService matService = SpringUtils.getBean(MatService.class);
+ Date now = new Date();
+ // 鍟嗗搧
+ Mat mat = matService.selectByMatnr(excel.getMatnr());
+ if (mat != null) {
+ mat.setMatnrNew(excel.getMatnrNew());
+ mat.setUpdateBy(this.userId);
+ mat.setUpdateTime(now);
+ if (matService.updateById(mat)) {
+ total++;
+ }
+ }
+ }
+
+ /**
+ * 鎵�鏈夋暟鎹В鏋愬畬鎴愪簡璋冪敤
+ * 閫傚悎浜嬪姟
+ */
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext ctx) {
+ log.info("鍚屾{}鏉＄墿鏂欎俊鎭紒", total);
+ }
+
+ public int getTotal() {
+ return total;
+ }
+}
diff --git a/src/main/resources/mapper/MatMapper.xml b/src/main/resources/mapper/MatMapper.xml
index 5cffd06..8f6125d 100644
--- a/src/main/resources/mapper/MatMapper.xml
+++ b/src/main/resources/mapper/MatMapper.xml
@@ -8,6 +8,7 @@
<result column="uuid" property="uuid" />
<result column="tag_id" property="tagId" />
<result column="matnr" property="matnr" />
+ <result column="matnr_new" property="matnrNew" />
<result column="maktx" property="maktx" />
<result column="name" property="name" />
<result column="specs" property="specs" />
@@ -80,4 +81,18 @@
<select id="selectByMatnr" resultMap="BaseResultMap">
select top 1 * from man_mat where 1=1 and matnr = #{matnr}
</select>
+
+ <select id="exportData" resultMap="BaseResultMap">
+ select * from man_mat
+ where 1=1
+ <if test="matnr != null and matnr != ''">
+ and matnr like '%' + #{matnr} + '%'
+ </if>
+ <if test="maktx != null and maktx != ''">
+ and maktx like '%' + #{maktx} + '%'
+ </if>
+ <if test="specs != null and specs != ''">
+ and specs like '%' + #{specs} + '%'
+ </if>
+ </select>
</mapper>
diff --git a/src/main/webapp/static/js/mat/mat.js b/src/main/webapp/static/js/mat/mat.js
index ecf879f..7d814a0 100644
--- a/src/main/webapp/static/js/mat/mat.js
+++ b/src/main/webapp/static/js/mat/mat.js
@@ -5,6 +5,7 @@
var cols = [
{type: 'checkbox'}
,{field: 'tagId$', align: 'center',title: '褰掔被', templet: '#tagTpl'}
+ ,{field: 'matnrNew', align: 'center',title: '鏂板晢鍝佺紪鍙�', width: 180}
];
cols.push.apply(cols, matCols);
cols.push(
@@ -433,9 +434,79 @@
// excel瀵煎嚭
function exportExcel() {
+ // window.location.href = baseUrl + "/mat/excel/export/auth"
+ const url = baseUrl + "/mat/excel/export/auth"; //璁板緱鎷兼帴鍙傛暟
+ const xhr = new XMLHttpRequest();
+ var params = '';
+ $.each($('#search-box [name]').serializeArray(), function() {
+ params += this.name + '=' + this.value + '&';
+ });
+ params = params.substring(0, params.length - 1);
+
+ xhr.open('POST', url, true);
+ xhr.setRequestHeader('token', localStorage.getItem('token'));
+ xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
+ xhr.responseType = 'blob'; // 杩斿洖绫诲瀷blob
+ xhr.onload = function(e) {
+ if (this.status === 200) {
+ const blob = this.response;
+ const reader = new FileReader();
+ reader.readAsDataURL(blob); // 杞崲涓篵ase64锛屽彲浠ョ洿鎺ユ斁鍏琛ㄦ儏href
+ reader.onload = function(e) {
+ const a = document.createElement('a');
+ a.download = new Date().getTime() + '_export_data.xlsx';
+ a.href = e.target.result;
+ document.documentElement.appendChild(a);
+ a.click();
+ a.remove(); // 绛変环浜巇ocument.documentElement.removeChild(a);
+ };
+ }
+ };
+ xhr.send(params); // 鍙戦�乤jax璇锋眰
}
+// 鍚屾鏂板晢鍝佺紪鍙�
+function importNewMatnr() {
+ $("#importNewMatnr").trigger("click");
+}
+function uploadNewMatnr(obj){
+ if(!obj.files) {
+ return;
+ }
+ var file = obj.files[0];
+ var filename = file.name
+ var filenameArr = filename.split(".")
+ var filetype = filenameArr[filenameArr.length - 1] //鏂囦欢绫诲瀷
+ if (filetype != "xlsx") {
+ layer.msg("璇蜂笂浼爔lsx鏍煎紡鏂囦欢");
+ return;
+ }
+
+ admin.confirm('纭鍚屾 [' + file.name +'] 鏂囦欢鍚楋紵', function (index) {
+ layer.load(1, {shade: [0.1,'#fff']});
+ var url = baseUrl + "/mat/excel/importNewMatnr/auth";
+ var form = new FormData();
+ form.append("file", file);
+ xhr = new XMLHttpRequest();
+ xhr.open("post", url, true); //post鏂瑰紡锛寀rl涓烘湇鍔″櫒璇锋眰鍦板潃锛宼rue 璇ュ弬鏁拌瀹氳姹傛槸鍚﹀紓姝ュ鐞嗐��
+ xhr.setRequestHeader('token', localStorage.getItem('token'));
+ xhr.onload = uploadComplete; //璇锋眰瀹屾垚
+ xhr.onerror = uploadFailed; //璇锋眰澶辫触
+ xhr.onloadend = function () { // // 涓婁紶瀹屾垚閲嶇疆鏂囦欢娴�
+ layer.closeAll('loading');
+ $("#importExcel").val("");
+ };
+ // xhr.upload.onprogress = progressFunction;//銆愪笂浼犺繘搴﹁皟鐢ㄦ柟娉曞疄鐜般��
+ xhr.upload.onloadstart = function(){//涓婁紶寮�濮嬫墽琛屾柟娉�
+ ot = new Date().getTime(); //璁剧疆涓婁紶寮�濮嬫椂闂�
+ oloaded = 0;//璁剧疆涓婁紶寮�濮嬫椂锛屼互涓婁紶鐨勬枃浠跺ぇ灏忎负0
+ };
+ xhr.send(form);
+ }, function(index){
+ $("#importExcel").val("");
+ });
+}
function tableReload(child) {
var searchData = {};
diff --git a/src/main/webapp/views/mat/mat.html b/src/main/webapp/views/mat/mat.html
index 31b2fda..95a3eb8 100644
--- a/src/main/webapp/views/mat/mat.html
+++ b/src/main/webapp/views/mat/mat.html
@@ -137,9 +137,14 @@
<li><a onclick="excelMouldDownload()" style="font-size: 12px"><i class="layui-icon layui-icon-template-1"></i>妯℃澘涓嬭浇</a></li>
<li><a onclick="importExcel()" style="font-size: 12px"><i class="layui-icon layui-icon-upload"></i>瀵煎叆 Excel</a></li>
<li style="display: none"><input id="importExcel" type="file" onchange="upload(this)" ></li>
+ <li><a onclick="importNewMatnr()" style="font-size: 12px"><i class="layui-icon layui-icon-upload"></i>鍚屾鏂板晢鍝佺紪鍙�</a></li>
+ <li style="display: none"><input id="importNewMatnr" type="file" onchange="uploadNewMatnr(this)" ></li>
<hr>
<li class="title">2nd menu</li>
- <li><a onclick="exportExcel()" style="font-size: 12px"><i class="layui-icon layui-icon-export"></i>瀵煎嚭 Excel</a></li>
+ <li>
+ <a onclick="exportExcel()" style="font-size: 12px"><i class="layui-icon layui-icon-export"></i>瀵煎嚭 Excel</a>
+ <a href="#" id="downloadLink" style="display: none;"></a>
+ </li>
</ul>
</div>
<!-- <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" style="float: right;margin-right: -10px">瀵煎嚭</button>-->
--
Gitblit v1.9.1