From 1b0cec788ca49a6c641f84c2bf0e616049c22e70 Mon Sep 17 00:00:00 2001
From: lyfO_o <764716047@qq.com>
Date: 星期五, 25 六月 2021 14:22:40 +0800
Subject: [PATCH] 导入申购修改导入字段
---
src/main/java/com/nanometer/smartlab/service/OpeApplyService.java | 3 +
src/main/webapp/resources/template/申购导入模板.xlsx | 0
src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.xml | 17 +++++---
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml | 19 ++++++---
src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.java | 2 +
src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java | 78 ++++++++++++++++++++++----------------
6 files changed, 71 insertions(+), 48 deletions(-)
diff --git a/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.java b/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.java
index 4157c6b..038d8a4 100644
--- a/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.java
+++ b/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.java
@@ -55,4 +55,6 @@
OpeApplyReserve getOpeApplyDetail(String id);
void updateOpeApplyInfo(Map params);
+
+ void insertList(List<OpeApply> opeApplyList);
}
diff --git a/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.xml b/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.xml
index f25e743..e852f9f 100644
--- a/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/OpeApplyDao.xml
@@ -205,14 +205,17 @@
</insert>
<insert id="insertOpeApplyList" parameterType="java.util.List">
- insert into ope_apply
- (id, apply_code, reagent_id, num, deadline, apply_user_id, objective, status, approve_user_id, memo, valid_flag, create_time, update_time,apply_goal,apply_price,first_user)
- values
- <foreach collection="list" item="opeApply" separator=",">
- (#{opeApply.id}, #{opeApply.applyCode}, #{opeApply.reagent.id}, #{opeApply.num}, #{opeApply.deadline},
- #{opeApply.applyUserId}, #{opeApply.objective}, #{opeApply.status}, #{opeApply.approveUserId}, #{opeApply.memo}, 1, now(), now(),#{opeApply.applyGoal},#{opeApply.reagent.price},#{opeApply.approveUserId})
- </foreach>
+
</insert>
+ <insert id="insertList">
+ insert into ope_apply
+ (id, apply_code, reagent_id, num, deadline, apply_user_id, objective, status, approve_user_id, memo, valid_flag, create_time, update_time,apply_goal,apply_price,first_user)
+ values
+ <foreach collection="list" item="opeApply" separator=",">
+ (#{opeApply.id}, #{opeApply.applyCode}, #{opeApply.reagent.id}, #{opeApply.num}, #{opeApply.deadline},
+ #{opeApply.applyUserId}, #{opeApply.objective}, #{opeApply.status}, #{opeApply.approveUserId}, #{opeApply.memo}, 1, #{opeApply.createTime}, #{opeApply.updateTime},#{opeApply.applyGoal},#{opeApply.reagent.price},#{opeApply.approveUserId})
+ </foreach>
+ </insert>
<update id="updateFirst" parameterType="java.util.Map">
update ope_apply set first_user=approve_user_id where id=#{id}
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
index b149844..c576cba 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
@@ -479,23 +479,28 @@
*
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 base_meta bm6 on sr.reagent_format = bm6.id
+ left join base_meta bm7 on sr.reagent_type = bm7.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})
+ sr.valid_flag = 1
+ <if test="params.packing != null and params.packing != ''">
+ and UPPER(concat(sr.main_metering,bm4.meta_value) ) = UPPER(#{params.packing})
</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.reagent_format != null and params.reagent_format != ''">
+ and bm6.meta_value = #{params.reagent_format}
+ </if>
+ <if test="params.reagent_type != null and params.reagent_type != ''">
+ and bm7.meta_value = #{params.reagent_type}
</if>
<if test="params.cas != null and params.cas != ''">
and sr.cas = #{params.cas}
@@ -504,7 +509,7 @@
and sr.product_sn = #{params.product_sn}
</if>
<if test="params.name != null and params.name != ''">
- and sr.name = #{params.name}
+ and sr.name = #{params.name};
</if>
</where>
diff --git a/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java b/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
index dad4c1d..6f29f7c 100644
--- a/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
+++ b/src/main/java/com/nanometer/smartlab/service/OpeApplyService.java
@@ -5,6 +5,7 @@
import com.nanometer.smartlab.exception.BusinessException;
import org.primefaces.event.FileUploadEvent;
+import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.List;
@@ -72,5 +73,5 @@
void btUpdateApplyAndOrder(List<OpeApply> opeList);
- void importApply(FileUploadEvent event, SysUser user);
+ void importApply(FileUploadEvent event, SysUser user) throws IOException;
}
diff --git a/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
index dd9d9fc..a4e9fc5 100644
--- a/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/OpeApplyServiceImpl.java
@@ -15,6 +15,7 @@
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
@@ -35,10 +36,9 @@
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.*;
/**
* Created by johnny on 17/12/12.
@@ -747,9 +747,8 @@
@Override
@Transactional
- public void importApply(FileUploadEvent event,SysUser user) {
+ public void importApply(FileUploadEvent event,SysUser user) throws IOException {
- try {
UploadedFile file = event.getFile();
InputStream is = file.getInputstream();
@@ -772,39 +771,57 @@
List<String> valuesList = new ArrayList<String>();
row = sheet.getRow(i);
- totalCells = row.getPhysicalNumberOfCells();
-
- for (int t = 0; t < totalCells; t++) {
+ totalCells = row.getLastCellNum();
+ //第10列是课题,其他都不能为空
+ for (int index = 0; index < totalCells; index++) {
String cellInfo = "";
+ if (row.getCell(index) != null) {
+ if (row.getCell(index).getCellTypeEnum() == CellType.BLANK && index != 9) {
+ throw new BusinessException(ExceptionEnumCode.PARAM_NULL, "第" + ++i + "行：除了课题组都不能为空");
+ }
- if (row.getCell(t) != null) {
- if (row.getCell(t).getCellTypeEnum().toString().equals("NUMERIC")) {
- cellInfo = String.valueOf(row.getCell(t).getNumericCellValue());
+ if (row.getCell(index).getCellTypeEnum() == CellType.NUMERIC) {
+ cellInfo = String.valueOf(row.getCell(index).getNumericCellValue());
}else{
- cellInfo = row.getCell(t).getStringCellValue();
+ cellInfo = row.getCell(index).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));
+ detail.put("product_sn", valuesList.get(0));
+ detail.put("name", valuesList.get(1));
+ detail.put("reagent_format", valuesList.get(2));
+ detail.put("packing", valuesList.get(3));
+ detail.put("cas", valuesList.get(4));
+ detail.put("product_home", valuesList.get(5));
+ detail.put("reagent_type", 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 applyUserName = valuesList.get(8);
+ SysUser applyUser = sysUserService.getUserByName(applyUserName);
+ if (applyUser == null) throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：申购人不存在或者申购人姓名存在重复，导入失败");
+ //课题组名称
+ String objective = valuesList.get(9);
+ String applyDateStr = valuesList.get(10);
+ String format = "yyyy-MM-dd";
+ SimpleDateFormat dateFormat = new SimpleDateFormat(format);
+ Date applyDate = null;
+ try {
+ applyDate = dateFormat.parse(applyDateStr);
+ } catch (ParseException e) {
+ e.printStackTrace();
+ throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：申购日期不合法，应如2016-10-09，导入失败");
+ }
//选择的审批人姓名
- String approveUserName = valuesList.get(9);
+ String approveUserName = valuesList.get(11);
//根据姓名获取用户信息的id
SysUser approveUser = sysUserService.getUserByName(approveUserName);
if (approveUser == null) throw new BusinessException(ExceptionEnumCode.PARAM_NO_EXIST, i+"行：审批人不存在或者审批人姓名存在重复，导入失败");
@@ -816,6 +833,10 @@
opeApply.setApplyCode(sysSequenceService.getApplyCode());
//设置试剂id
opeApply.setReagent(sysReagent);
+ assert applyDate != null;
+ Timestamp applyTime = new Timestamp(applyDate.getTime());
+ opeApply.setCreateTime(applyTime);
+ opeApply.setUpdateTime(applyTime);
//价格
opeApply.setApplyPrice(sysReagent.getPrice());
//申购数量
@@ -832,17 +853,8 @@
}
if (adds.size() > 0) {
- opeApplyDao.insertOpeApplyList(adds);
+ opeApplyDao.insertList(adds);
}
-
-
-
-
-
-
- } catch (IOException e) {
- e.printStackTrace();
- }
}
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"
index c6ce4ca..8c93710 100644
--- "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"
Binary files differ
--
Gitblit v1.9.2