From 80dd44c72c42497080ce04479af76a15689d6195 Mon Sep 17 00:00:00 2001
From: whycq <913841844@qq.com>
Date: 星期日, 14 一月 2024 18:39:14 +0800
Subject: [PATCH] #
---
src/main/java/com/zy/asrs/mapper/MatV2Mapper.java | 10 +
src/main/java/com/zy/asrs/utils/MatV2ExcelListener.java | 117 +++++++++++++++++++
src/main/webapp/static/js/matV2/matV2.js | 57 +++++++++
src/main/java/com/zy/asrs/controller/MatV2Controller.java | 37 ++++++
src/main/java/com/zy/asrs/entity/MatV2.java | 13 +
src/main/resources/mapper/MatV2Mapper.xml | 28 ++++
src/main/java/com/zy/asrs/service/MatV2Service.java | 6 +
src/main/java/com/zy/common/entity/MatV2Excel.java | 20 +++
src/main/java/com/zy/asrs/service/impl/MatV2ServiceImpl.java | 13 ++
src/main/resources/license.lic | 0
src/main/resources/application.yml | 6
11 files changed, 303 insertions(+), 4 deletions(-)
diff --git a/src/main/java/com/zy/asrs/controller/MatV2Controller.java b/src/main/java/com/zy/asrs/controller/MatV2Controller.java
index 9be7c40..cfe323b 100644
--- a/src/main/java/com/zy/asrs/controller/MatV2Controller.java
+++ b/src/main/java/com/zy/asrs/controller/MatV2Controller.java
@@ -1,5 +1,7 @@
package com.zy.asrs.controller;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
@@ -13,10 +15,19 @@
import com.core.common.BaseRes;
import com.core.common.Cools;
import com.core.common.R;
+import com.zy.asrs.utils.MatExcelListener;
+import com.zy.asrs.utils.MatV2ExcelListener;
+import com.zy.common.entity.MatExcel;
+import com.zy.common.entity.MatV2Excel;
import com.zy.common.web.BaseController;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.net.URLEncoder;
import java.util.*;
@RestController
@@ -168,4 +179,30 @@
}
return R.ok().add(matV2s);
}
+
+ /**
+ * excel瀵煎叆妯℃澘涓嬭浇
+ */
+ @RequestMapping(value = "/matV2/excel/import/mould")
+ public void matV2ExcelImportMould(HttpServletResponse response) throws IOException {
+ List<MatV2Excel> excels = new ArrayList<>();
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding("utf-8");
+ String fileName = URLEncoder.encode("缁勪欢妗ｆExcel瀵煎叆妯℃澘", "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+ EasyExcel.write(response.getOutputStream(), MatV2Excel.class)
+ .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
+ .sheet("sheet1")
+ .doWrite(excels);
+ }
+
+ // excel瀵煎叆
+ @PostMapping(value = "/matV2/excel/import/auth")
+ @ManagerAuth(memo = "缁勪欢妗ｆ鏁版嵁瀵煎叆")
+ @Transactional
+ public R matV2ExcelImport(MultipartFile file) throws IOException {
+ MatV2ExcelListener listener = new MatV2ExcelListener(getUserId());
+ EasyExcel.read(file.getInputStream(), MatV2Excel.class, listener).sheet().doRead();
+ return R.ok("鎴愬姛鍚屾"+listener.getTotal()+"鏉″晢鍝佹暟鎹�");
+ }
}
diff --git a/src/main/java/com/zy/asrs/entity/MatV2.java b/src/main/java/com/zy/asrs/entity/MatV2.java
index 6738416..a2f4dd7 100644
--- a/src/main/java/com/zy/asrs/entity/MatV2.java
+++ b/src/main/java/com/zy/asrs/entity/MatV2.java
@@ -1,5 +1,6 @@
package com.zy.asrs.entity;
+import com.alibaba.excel.annotation.ExcelProperty;
import com.core.common.Cools;import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.enums.IdType;
import com.baomidou.mybatisplus.annotations.TableField;
@@ -35,18 +36,22 @@
private String tagId;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "鍟嗗搧缂栫爜")
private String matnr;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "鍟嗗搧鍚嶇О")
private String maktx;
@ApiModelProperty(value= "")
private String name;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "瑙勬牸")
private String specs;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "鍨嬪彿")
private String model;
@ApiModelProperty(value= "")
@@ -56,6 +61,7 @@
private String brand;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "鍗曚綅")
private String unit;
@ApiModelProperty(value= "")
@@ -89,6 +95,7 @@
private Double safeQty;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "閲嶉噺")
private Double weight;
@ApiModelProperty(value= "")
@@ -110,7 +117,8 @@
@ApiModelProperty(value= "")
@TableField("be_batch")
- private Integer beBatch;
+ @ExcelProperty(value = "缁勬垚鐢ㄩ噺")
+ private Float beBatch;
@ApiModelProperty(value= "")
@TableField("dead_time")
@@ -151,11 +159,12 @@
private Date updateTime;
@ApiModelProperty(value= "")
+ @ExcelProperty(value = "澶囨敞")
private String memo;
public MatV2() {}
- public MatV2(String uuid,String tagId,String matnr,String maktx,String name,String specs,String model,String color,String brand,String unit,Double price,String sku,Double units,String barcode,String origin,String manu,String manuDate,String itemNum,Double safeQty,Double weight,Double length,Double volume,String threeCode,String supp,String suppCode,Integer beBatch,String deadTime,Integer deadWarn,Integer source,Integer inspect,Integer danger,Integer status,Long createBy,Date createTime,Long updateBy,Date updateTime,String memo) {
+ public MatV2(String uuid,String tagId,String matnr,String maktx,String name,String specs,String model,String color,String brand,String unit,Double price,String sku,Double units,String barcode,String origin,String manu,String manuDate,String itemNum,Double safeQty,Double weight,Double length,Double volume,String threeCode,String supp,String suppCode,Float beBatch,String deadTime,Integer deadWarn,Integer source,Integer inspect,Integer danger,Integer status,Long createBy,Date createTime,Long updateBy,Date updateTime,String memo) {
this.uuid = uuid;
this.tagId = tagId;
this.matnr = matnr;
diff --git a/src/main/java/com/zy/asrs/mapper/MatV2Mapper.java b/src/main/java/com/zy/asrs/mapper/MatV2Mapper.java
index 808a45c..27b3234 100644
--- a/src/main/java/com/zy/asrs/mapper/MatV2Mapper.java
+++ b/src/main/java/com/zy/asrs/mapper/MatV2Mapper.java
@@ -1,12 +1,22 @@
package com.zy.asrs.mapper;
+import com.baomidou.mybatisplus.plugins.Page;
+import com.zy.asrs.entity.Mat;
import com.zy.asrs.entity.MatV2;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
+import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
+
+import java.util.List;
@Mapper
@Repository
public interface MatV2Mapper extends BaseMapper<MatV2> {
+ List<MatV2> listByPage(Page page, @Param("tagId") String tagId, @Param("matnr") Object matnr, @Param("maktx") Object maktx);
+
+ MatV2 selectByMatnr(@Param("matnr")String matnr);
+
+
}
diff --git a/src/main/java/com/zy/asrs/service/MatV2Service.java b/src/main/java/com/zy/asrs/service/MatV2Service.java
index 9608a34..b525715 100644
--- a/src/main/java/com/zy/asrs/service/MatV2Service.java
+++ b/src/main/java/com/zy/asrs/service/MatV2Service.java
@@ -1,8 +1,14 @@
package com.zy.asrs.service;
+import com.baomidou.mybatisplus.plugins.Page;
+import com.zy.asrs.entity.Mat;
import com.zy.asrs.entity.MatV2;
import com.baomidou.mybatisplus.service.IService;
public interface MatV2Service extends IService<MatV2> {
+ Page<MatV2> getPage(Page page, String tagId, Object matnr, Object maktx);
+
+ MatV2 selectByMatnr(String matnr);
+
}
diff --git a/src/main/java/com/zy/asrs/service/impl/MatV2ServiceImpl.java b/src/main/java/com/zy/asrs/service/impl/MatV2ServiceImpl.java
index 25400a1..d56fa52 100644
--- a/src/main/java/com/zy/asrs/service/impl/MatV2ServiceImpl.java
+++ b/src/main/java/com/zy/asrs/service/impl/MatV2ServiceImpl.java
@@ -1,5 +1,7 @@
package com.zy.asrs.service.impl;
+import com.baomidou.mybatisplus.plugins.Page;
+import com.zy.asrs.entity.Mat;
import com.zy.asrs.mapper.MatV2Mapper;
import com.zy.asrs.entity.MatV2;
import com.zy.asrs.service.MatV2Service;
@@ -9,4 +11,15 @@
@Service("matV2Service")
public class MatV2ServiceImpl extends ServiceImpl<MatV2Mapper, MatV2> implements MatV2Service {
+ @Override
+ public Page<MatV2> getPage(Page page, String tagId, Object matnr, Object maktx) {
+ return page.setRecords(baseMapper.listByPage(page, tagId, matnr, maktx));
+ }
+
+
+ @Override
+ public MatV2 selectByMatnr(String matnr) {
+ return this.baseMapper.selectByMatnr(matnr);
+ }
+
}
diff --git a/src/main/java/com/zy/asrs/utils/MatV2ExcelListener.java b/src/main/java/com/zy/asrs/utils/MatV2ExcelListener.java
new file mode 100644
index 0000000..fa68692
--- /dev/null
+++ b/src/main/java/com/zy/asrs/utils/MatV2ExcelListener.java
@@ -0,0 +1,117 @@
+package com.zy.asrs.utils;
+
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.event.AnalysisEventListener;
+import com.baomidou.mybatisplus.mapper.EntityWrapper;
+import com.core.common.SpringUtils;
+import com.core.exception.CoolException;
+import com.zy.asrs.entity.MatV2;
+import com.zy.asrs.service.MatService;
+import com.zy.asrs.service.MatV2Service;
+import com.zy.common.entity.MatExcel;
+import com.zy.common.entity.MatV2Excel;
+import lombok.extern.slf4j.Slf4j;
+
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Created by vincent on 2019-11-25
+ */
+@Slf4j
+public class MatV2ExcelListener extends AnalysisEventListener<MatV2Excel> {
+
+ private int total = 0;
+ private Long userId;
+
+ public MatV2ExcelListener() {
+ }
+
+ public MatV2ExcelListener(Long userId) {
+ this.userId = userId;
+ }
+
+ /**
+ * 姣忛殧5鏉″瓨鍌ㄦ暟鎹簱锛屽疄闄呬娇鐢ㄤ腑鍙互3000鏉★紝鐒跺悗娓呯悊list 锛屾柟渚垮唴瀛樺洖鏀�
+ */
+ private static final int BATCH_COUNT = 50;
+
+ private final List<MatV2Excel> list = new ArrayList<>();
+
+ /**
+ * 杩欓噷浼氫竴琛岃鐨勮繑鍥炲ご
+ */
+ @Override
+ public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
+ }
+
+ /**
+ * 杩欎釜姣忎竴鏉℃暟鎹В鏋愰兘浼氭潵璋冪敤
+ */
+ @Override
+ public void invoke(MatV2Excel excel, AnalysisContext ctx) {
+ MatV2Service matV2Service = SpringUtils.getBean(MatV2Service.class);
+ Date now = new Date();
+ String number = excel.getNumber();
+ String[] split = number.split("\\.");
+ String uuid;
+ String tagId;
+ if (split.length == 1) {
+ uuid = "1";
+ tagId = split[0];
+ } else {
+ StringBuilder sb = new StringBuilder();
+ StringBuilder sb2 = new StringBuilder();
+ for (int i = 0; i < split.length; i++) {
+ if (i == 0) {
+ sb.append(split[i]);
+ } else {
+ sb.append("." + split[i]);
+ }
+ }
+ tagId = sb.toString();
+ for (int i = 0; i < split.length-1; i++) {
+ if (i == 0) {
+ sb2.append(split[i]);
+ } else {
+ sb2.append("." + split[i]);
+ }
+ }
+ uuid = sb2.toString();
+ }
+ // 鍟嗗搧
+ MatV2 matV2 = matV2Service.selectByMatnr(excel.getMatnr());
+ if (matV2 == null) {
+ matV2 = excel;
+ matV2.setUuid(uuid);
+ matV2.setTagId(tagId);
+ if (!matV2Service.insert(matV2)) {
+ throw new CoolException("淇濆瓨鍟嗗搧淇℃伅澶辫触锛屽晢鍝佺紪鐮侊細" + excel.getMatnr());
+ }
+ total++;
+ } else {
+ matV2 = excel;
+ matV2.setUuid(uuid);
+ matV2.setTagId(tagId);
+ if (!matV2Service.update(matV2,new EntityWrapper<MatV2>().eq("matnr",matV2.getMatnr()))) {
+ throw new CoolException("淇濆瓨鍟嗗搧淇℃伅澶辫触锛屽晢鍝佺紪鐮侊細" + excel.getMatnr());
+ }
+ total++;
+ }
+ }
+
+ /**
+ * 鎵�鏈夋暟鎹В鏋愬畬鎴愪簡璋冪敤
+ * 閫傚悎浜嬪姟
+ */
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext ctx) {
+ log.info("鏂板{}鏉＄墿鏂欎俊鎭紒", total);
+ }
+
+ public int getTotal() {
+ return total;
+ }
+}
diff --git a/src/main/java/com/zy/common/entity/MatV2Excel.java b/src/main/java/com/zy/common/entity/MatV2Excel.java
new file mode 100644
index 0000000..acb0977
--- /dev/null
+++ b/src/main/java/com/zy/common/entity/MatV2Excel.java
@@ -0,0 +1,20 @@
+package com.zy.common.entity;
+
+import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.zy.asrs.entity.MatV2;
+import lombok.Data;
+
+/**
+ * 0.A 1.B 2.C 3.D 4.E 5.F 6.G 7.H 8.I
+ * 9.J 10.K 11.L 12.M 13.N 14.O 15.P 16.Q 17.R 18.S
+ * 19.T 20.U 21.V 22.W 23.X 24.Y 25.Z
+ */
+@Data
+@ExcelIgnoreUnannotated
+public class MatV2Excel extends MatV2 {
+
+ @ExcelProperty(index = 0, value = "搴忓彿")
+ private String number;
+
+}
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index bf29db8..8e1e1c4 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -62,4 +62,8 @@
publicAlias: publicCert
storePass: public_zhongyang_123456789
licensePath: license.lic
- publicKeysStorePath: publicCerts.keystore
\ No newline at end of file
+ publicKeysStorePath: publicCerts.keystore
+
+
+loc-move:
+ move-all: true
\ No newline at end of file
diff --git a/src/main/resources/license.lic b/src/main/resources/license.lic
index 931f723..e3519e6 100644
--- a/src/main/resources/license.lic
+++ b/src/main/resources/license.lic
Binary files differ
diff --git a/src/main/resources/mapper/MatV2Mapper.xml b/src/main/resources/mapper/MatV2Mapper.xml
index df08093..3e7834a 100644
--- a/src/main/resources/mapper/MatV2Mapper.xml
+++ b/src/main/resources/mapper/MatV2Mapper.xml
@@ -45,4 +45,32 @@
</resultMap>
+ <select id="listByPage" resultMap="BaseResultMap">
+ SELECT
+ isnull(mld.amount,0) as stock,
+ mm.*
+ FROM man_mat_v2 mm
+ LEFT JOIN man_tag mt ON mm.tag_id = mt.id
+ LEFT JOIN (
+ select
+ matnr,
+ sum(anfme) as amount
+ from man_loc_detl
+ group by matnr
+ ) as mld on mld.matnr = mm.matnr
+ WHERE 1=1
+ and (mm.memo is null or mm.memo != '鎵撳寘涓婄嚎')
+ AND (CHARINDEX(','+#{tagId}+',', ','+mt.path+',') > 0 OR mt.id = #{tagId})
+ <if test="matnr != null and matnr != ''">
+ and mm.matnr like concat('%',#{matnr},'%')
+ </if>
+ <if test="maktx != null and maktx != ''">
+ and mm.maktx like concat('%',#{maktx},'%')
+ </if>
+ ORDER BY mm.create_time DESC
+ </select>
+
+ <select id="selectByMatnr" resultMap="BaseResultMap">
+ select top 1 * from man_mat_v2 where 1=1 and matnr = #{matnr}
+ </select>
</mapper>
diff --git a/src/main/webapp/static/js/matV2/matV2.js b/src/main/webapp/static/js/matV2/matV2.js
index 07fdeab..e27bf62 100644
--- a/src/main/webapp/static/js/matV2/matV2.js
+++ b/src/main/webapp/static/js/matV2/matV2.js
@@ -1,4 +1,5 @@
var pageCurr;
+var admin;
layui.config({
base: baseUrl + "/static/layui/lay/modules/"
}).extend({
@@ -9,7 +10,7 @@
var layer = layui.layer;
var layDate = layui.laydate;
var form = layui.form;
- var admin = layui.admin;
+ admin = layui.admin;
// 鏁版嵁娓叉煋
tableIns = table.render({
@@ -287,3 +288,57 @@
page: {curr: pageCurr}
});
}
+
+// excel瀵煎叆妯℃澘涓嬭浇
+function excelMouldDownload(){
+ layer.load(1, {shade: [0.1,'#fff']});
+ location.href = baseUrl + "/matV2/excel/import/mould";
+ layer.closeAll('loading');
+}
+
+// excel瀵煎叆
+function importExcel() {
+ $("#importExcel").trigger("click");
+}
+function upload(obj){
+ if(!obj.files) {
+ return;
+ }
+ var file = obj.files[0];
+ admin.confirm('纭鍚屾 [' + file.name +'] 鏂囦欢鍚楋紵', function (index) {
+ layer.load(1, {shade: [0.1,'#fff']});
+ var url = baseUrl + "/matV2/excel/import/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 uploadComplete(evt) {
+ var res = JSON.parse(evt.target.responseText);
+ if(res.code === 200) {
+ layer.msg(res.msg, {icon: 1});
+ loadTree("");
+ } else {
+ layer.msg(res.msg, {icon: 2});
+ }
+}
+function uploadFailed(evt) {
+ var res = JSON.parse(evt.target.responseText);
+ layer.msg(res.msg, {icon: 2});
+}
--
Gitblit v1.9.1