From 82f0f5e82cf0ff88af237ba5929dff7a2bdc4d46 Mon Sep 17 00:00:00 2001
From: lyfO_o <764716047@qq.com>
Date: 星期一, 21 六月 2021 14:52:52 +0800
Subject: [PATCH] 申购导入和导入模板
---
src/main/java/com/nanometer/smartlab/controller/ApplyMngController.java | 18 +++
src/main/java/com/nanometer/smartlab/service/OpeApplyService.java | 8
src/main/webapp/resources/template/申购导入模板.xlsx | 0
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml | 39 +++++++
src/main/java/com/nanometer/smartlab/service/SysUserService.java | 1
src/main/webapp/apply_mng.xhtml | 16 +++
src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java | 123 +++++++++++++++++++++++-
src/main/java/com/nanometer/smartlab/service/SysUserServiceImpl.java | 7 +
src/main/java/com/nanometer/smartlab/dao/SysUserDao.java | 1
src/main/java/com/nanometer/smartlab/service/SysReagentService.java | 6 +
src/main/java/com/nanometer/smartlab/dao/SysUserDao.xml | 4
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java | 7 +
src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java | 14 ++
13 files changed, 234 insertions(+), 10 deletions(-)
diff --git a/src/main/java/com/nanometer/smartlab/controller/ApplyMngController.java b/src/main/java/com/nanometer/smartlab/controller/ApplyMngController.java
index ff14dbe..11315c3 100644
--- a/src/main/java/com/nanometer/smartlab/controller/ApplyMngController.java
+++ b/src/main/java/com/nanometer/smartlab/controller/ApplyMngController.java
@@ -5,6 +5,7 @@
import com.nanometer.smartlab.entity.SysReagent;
import com.nanometer.smartlab.entity.SysUser;
import com.nanometer.smartlab.entity.enumtype.ApplyStatus;
+import com.nanometer.smartlab.exception.BusinessException;
import com.nanometer.smartlab.service.*;
import com.nanometer.smartlab.util.Constants;
import com.nanometer.smartlab.util.FacesUtils;
@@ -15,6 +16,7 @@
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.primefaces.context.RequestContext;
+import org.primefaces.event.FileUploadEvent;
import org.primefaces.event.SelectEvent;
import org.primefaces.event.TabChangeEvent;
import org.primefaces.model.LazyDataModel;
@@ -297,6 +299,22 @@
}
}
+
+
+ public void uploadApply(FileUploadEvent event){
+ try {
+ opeApplyService.importApply(event, getUser());
+ FacesUtils.info("导入成功");
+ } catch (BusinessException e) {
+ FacesUtils.warn(e.getMessage());
+ } catch (Exception e) {
+ e.printStackTrace();
+ FacesUtils.warn("导入失败,请联系管理员");
+ }
+
+ }
+
+
public HSSFWorkbook exportExcelNew(List<String> headerList, List<OpeApply> dataList) {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
index a0ca110..5b569c2 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
@@ -41,4 +41,11 @@
List<SysReagent> selectReagentSafetyNum();
void updateSafetyNum(@Param("id") String id, @Param("safetynum") Integer safetynum);
+
+ /**
+ * @Description: 根据Map里的详细传参 查询试剂个数
+ * @date 2021/6/21 9:07
+ */
+ List<SysReagent> countReagentByDetail(@Param("params") Map detail);
+
}
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
index 9823b84..26e374d 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
@@ -471,4 +471,43 @@
set safetynum = #{safetynum}
where id = #{id}
</update>
+
+ <select id="countReagentByDetail" resultMap="SysReagent">
+ select
+ *
+ from sys_reagent as sr
+ left join base_meta bm2 on sr.reagent_character = bm2.id
+ left join base_meta bm3 on sr.main_metering = bm3.id
+ left join base_meta bm4 on sr.reagent_unit = bm4.id
+ left join base_meta bm5 on sr.product_home = bm5.id
+ left join sys_supplier supply on sr.supplier_id = supply.id
+
+ <where>
+ <if test="params.reagent_unit != null and params.reagent_unit != ''">
+ and UPPER(bm4.meta_value) = UPPER(#{params.reagent_unit})
+ </if>
+ <if test="params.main_metering != null and params.main_metering != ''">
+ and UPPER(bm3.meta_value) = UPPER(#{params.main_metering})
+ </if>
+ <if test="params.product_home != null and params.product_home != ''">
+ and bm5.meta_value = #{params.product_home}
+ </if>
+ <if test="params.reagent_character != null and params.reagent_character != ''">
+ and bm2.meta_value = #{params.reagent_character}
+ </if>
+ <if test="params.cas != null and params.cas != ''">
+ and sr.cas = #{params.cas}
+ </if>
+ <if test="params.product_sn != null and params.product_sn != ''">
+ and sr.product_sn = #{params.product_sn}
+ </if>
+ <if test="params.name != null and params.name != ''">
+ and sr.name = #{params.name}
+ </if>
+ </where>
+
+
+
+
+ </select>
</mapper>
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysUserDao.java b/src/main/java/com/nanometer/smartlab/dao/SysUserDao.java
index c0bd43b..c9591fe 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysUserDao.java
+++ b/src/main/java/com/nanometer/smartlab/dao/SysUserDao.java
@@ -63,4 +63,5 @@
List<String> getLibrarianEmail();
+ List<SysUser> getUserByName(String approveUserName);
}
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysUserDao.xml b/src/main/java/com/nanometer/smartlab/dao/SysUserDao.xml
index 0bfab6b..f5da893 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysUserDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/SysUserDao.xml
@@ -387,6 +387,10 @@
and su.email is not null
and su.email != ''
</select>
+ <select id="getUserByName" resultMap="SysUser">
+ select * from sys_user
+ where name = #{name} and valid_flag = 1
+ </select>
<update id="updateUserPointBySelective" parameterType="java.util.Map">
update sys_user set point=#{point}
<where>
diff --git a/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java b/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
index c2d18cd..dad4c1d 100644
--- a/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
+++ b/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
@@ -1,11 +1,9 @@
package com.nanometer.smartlab.service;
-import com.nanometer.smartlab.entity.OpeApply;
-import com.nanometer.smartlab.entity.OpeApplyReserve;
-import com.nanometer.smartlab.entity.OpeOrder;
-import com.nanometer.smartlab.entity.OpeWarehouseReserve;
+import com.nanometer.smartlab.entity.*;
import com.nanometer.smartlab.entity.enumtype.ApplyStatus;
import com.nanometer.smartlab.exception.BusinessException;
+import org.primefaces.event.FileUploadEvent;
import java.math.BigDecimal;
import java.sql.Timestamp;
@@ -73,4 +71,6 @@
void orderInputWarehouse(List<OpeApplyReserve> opeApplyList,String consigneeId,Timestamp arrivalTime) throws BusinessException,Exception;
void btUpdateApplyAndOrder(List<OpeApply> opeList);
+
+ void importApply(FileUploadEvent event, SysUser user);
}
diff --git a/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
index 4da1d67..dd9d9fc 100644
--- a/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
@@ -1,12 +1,11 @@
package com.nanometer.smartlab.service;
import com.nanometer.smartlab.dao.OpeApplyDao;
-import com.nanometer.smartlab.entity.OpeApply;
-import com.nanometer.smartlab.entity.OpeApplyReserve;
-import com.nanometer.smartlab.entity.OpeOrder;
-import com.nanometer.smartlab.entity.SysUser;
+import com.nanometer.smartlab.dao.SysReagentDao;
+import com.nanometer.smartlab.entity.*;
import com.nanometer.smartlab.entity.enumtype.ApplyStatus;
import com.nanometer.smartlab.entity.enumtype.SeeFlag;
+import com.nanometer.smartlab.entity.enumtype.ValidFlag;
import com.nanometer.smartlab.exception.AlarmCode;
import com.nanometer.smartlab.exception.AlarmException;
import com.nanometer.smartlab.exception.BusinessException;
@@ -15,6 +14,13 @@
import com.nanometer.smartlab.util.MessageUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.primefaces.event.FileUploadEvent;
+import org.primefaces.model.UploadedFile;
import org.springframework.context.annotation.Lazy;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
@@ -25,6 +31,8 @@
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
+import java.io.IOException;
+import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
@@ -51,7 +59,11 @@
@Lazy
@Resource
OpeWarehouseReserveService opeWarehouseReserveService;
-
+ @Lazy
+ @Resource
+ SysReagentService sysReagentService;
+ @Resource
+ SysSequenceService sysSequenceService;
@Resource
private SysUserService sysUserService;
@@ -733,4 +745,105 @@
}
+ @Override
+ @Transactional
+ public void importApply(FileUploadEvent event,SysUser user) {
+
+ try {
+
+ UploadedFile file = event.getFile();
+ InputStream is = file.getInputstream();
+ boolean isExcel2003 = true;
+ if (file.getFileName().matches("^.+\\.(?i)(xlsx)$")) {
+ isExcel2003 = false;
+ }
+ Workbook wb = null;
+ if (isExcel2003) {
+ wb = new HSSFWorkbook(is);
+ } else {
+ wb = new XSSFWorkbook(is);
+ }
+ Sheet sheet = wb.getSheetAt(0);
+ int totalRows = sheet.getPhysicalNumberOfRows();
+ ArrayList<OpeApply> adds = new ArrayList<>();
+ Row row = null;
+ int totalCells = 0;
+ for (int i = 1; i < totalRows; i++) {
+ List<String> valuesList = new ArrayList<String>();
+ row = sheet.getRow(i);
+
+ totalCells = row.getPhysicalNumberOfCells();
+
+ for (int t = 0; t < totalCells; t++) {
+ String cellInfo = "";
+
+ if (row.getCell(t) != null) {
+ if (row.getCell(t).getCellTypeEnum().toString().equals("NUMERIC")) {
+ cellInfo = String.valueOf(row.getCell(t).getNumericCellValue());
+ }else{
+ cellInfo = row.getCell(t).getStringCellValue();
+ }
+ }
+ valuesList.add(cellInfo);
+ }
+
+ Map<String,Object> detail = new HashMap<>();
+ detail.put("name", valuesList.get(0));
+ detail.put("cas", valuesList.get(1));
+ detail.put("product_sn", valuesList.get(2));
+ detail.put("main_metering", valuesList.get(3));
+ detail.put("reagent_unit", valuesList.get(4));
+ detail.put("reagent_character", valuesList.get(5));
+ detail.put("product_home", valuesList.get(6));
+ SysReagent sysReagent = sysReagentService.getReagentByDetail(detail);
+ if (sysReagent == null) throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：试剂不存在或者有多条，导入失败");
+
+ //申购数量
+ Integer num = new Double(valuesList.get(7)).intValue();
+ if (num < 1) throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：试剂申购数量不合法，导入失败");
+ //课题组
+ String objective = valuesList.get(8);
+ //选择的审批人姓名
+ String approveUserName = valuesList.get(9);
+ //根据姓名获取用户信息的id
+ SysUser approveUser = sysUserService.getUserByName(approveUserName);
+ if (approveUser == null) throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：审批人不存在或者审批人姓名存在重复，导入失败");
+
+ OpeApply opeApply = new OpeApply();
+ opeApply.setId(IDUtils.uuid());
+ opeApply.setValidFlag(ValidFlag.VALID);
+ opeApply.setStatus(ApplyStatus.PENDING_APPROVAL);
+ opeApply.setApplyCode(sysSequenceService.getApplyCode());
+ //设置试剂id
+ opeApply.setReagent(sysReagent);
+ //价格
+ opeApply.setApplyPrice(sysReagent.getPrice());
+ //申购数量
+ opeApply.setNum(num);
+ //申购人
+ opeApply.setApplyUserId(user.getId());
+ //选择的审批人
+ opeApply.setApproveUserId(approveUser.getId());
+ //选择的课题组信息
+ opeApply.setObjective(objective);
+
+ adds.add(opeApply);
+
+ }
+
+ if (adds.size() > 0) {
+ opeApplyDao.insertOpeApplyList(adds);
+ }
+
+
+
+
+
+
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
+ }
+
}
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
index b259e92..513147b 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
@@ -43,4 +43,10 @@
void updateSafetyNum(SysReagent sysReagent);
+ /**
+ * @Description: 根据detail的参数获取试剂id
+ * @date 2021/6/21 9:42
+ */
+ SysReagent getReagentByDetail(Map<String, Object> detail);
+
}
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
index 82d0f3d..e69e1d9 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
@@ -169,6 +169,20 @@
}
+ /**
+ * @Description: 根据detail的信息获取试剂的id
+ * @date 2021/6/21 9:45
+ */
+ @Override
+ public SysReagent getReagentByDetail(Map<String, Object> detail) {
+ List<SysReagent> reagentIds = sysReagentDao.countReagentByDetail(detail);
+ if (reagentIds.size() != 1) {
+ return null;
+ }
+ return reagentIds.get(0);
+ }
+
+
@Transactional(propagation = Propagation.REQUIRED)
public int getSysReagentTotalCount(String name, String cas, String supplierId,Integer type) {
try {
diff --git a/src/main/java/com/nanometer/smartlab/service/SysUserService.java b/src/main/java/com/nanometer/smartlab/service/SysUserService.java
index 861ba03..325fd7e 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysUserService.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysUserService.java
@@ -55,4 +55,5 @@
List<String> getLibrarianEmail();
+ SysUser getUserByName(String approveUserName);
}
diff --git a/src/main/java/com/nanometer/smartlab/service/SysUserServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/SysUserServiceImpl.java
index 021fbca..aa11981 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysUserServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysUserServiceImpl.java
@@ -427,5 +427,12 @@
return sysUserDao.getLibrarianEmail();
}
+ @Override
+ public SysUser getUserByName(String approveUserName) {
+ List<SysUser> users = sysUserDao.getUserByName(approveUserName);
+ if (users.size() != 1) return null;
+ return users.get(0);
+ }
+
}
diff --git a/src/main/webapp/apply_mng.xhtml b/src/main/webapp/apply_mng.xhtml
index 813cfd8..9dd4ec6 100644
--- a/src/main/webapp/apply_mng.xhtml
+++ b/src/main/webapp/apply_mng.xhtml
@@ -46,7 +46,7 @@
</p:panelGrid>
</p:panel>
<p:panel styleClass="center-body">
- <p:panelGrid columns="5" styleClass="btn">
+ <p:panelGrid columns="7" styleClass="btn">
<p:commandButton value="申购" styleClass="new-btn"
process="@this"
actionListener="#{applyMngController.onNewBtnClick}"
@@ -69,7 +69,21 @@
</p:commandButton>
<p:commandButton update=":centerRootPanel" actionListener="#{applyMngController.onExportFileBtnClickNew}"
ajax="false" value="导出" styleClass="new-btn" />
+
+ <p:commandButton onclick="importApply()" value="导入" styleClass="import-btn" />
+ <a href="resources/template/申购导入模板.xlsx" style="display: inline-block;"><img src="resources/images/xlsx.png" width="30px;" alt=""/></a>
</p:panelGrid>
+ <script type="text/javascript">
+ function importApply(){
+ $("#applyMngForm\\:importApplyBtn_input").click();
+ }
+ </script>
+ <div style="display: none" id='importDv'>
+ <p:fileUpload fileUploadListener="#{applyMngController.uploadApply}"
+ dragDropSupport="false" id="importApplyBtn" label="导入"
+ update="@form" auto="true" sizeLimit="5000000"
+ mode="advanced"/>
+ </div>
<p:dataTable id="applyMngDataTable" styleClass="data-table" resizableColumns="true"
paginator="true" paginatorAlwaysVisible="false" paginatorPosition="bottom"
diff --git "a/src/main/webapp/resources/template/\347\224\263\350\264\255\345\257\274\345\205\245\346\250\241\346\235\277.xlsx" "b/src/main/webapp/resources/template/\347\224\263\350\264\255\345\257\274\345\205\245\346\250\241\346\235\277.xlsx"
new file mode 100644
index 0000000..c6ce4ca
--- /dev/null
+++ "b/src/main/webapp/resources/template/\347\224\263\350\264\255\345\257\274\345\205\245\346\250\241\346\235\277.xlsx"
Binary files differ
--
Gitblit v1.9.2